c3f43d8b61
- Add 60 new agents across all 10 categories (75 -> 135) - Add 95 new plugins with command files (25 -> 120) - Update all agents to use model: opus - Update README with complete plugin/agent tables - Update marketplace.json with all 120 plugins
44 lines
2.5 KiB
Markdown
44 lines
2.5 KiB
Markdown
# Database
|
|
|
|
## Query Patterns
|
|
- Use parameterized queries for all database operations. Never interpolate user input into SQL.
|
|
- Select only needed columns. Avoid `SELECT *` in production queries.
|
|
- Use CTEs (Common Table Expressions) for complex queries instead of nested subqueries.
|
|
- Batch inserts and updates using `INSERT INTO ... VALUES (...), (...)` or `unnest` patterns.
|
|
- Use database-level constraints (NOT NULL, UNIQUE, CHECK, FK) as the source of truth for data integrity.
|
|
|
|
## N+1 Prevention
|
|
- Detect N+1 queries by enabling query logging in development.
|
|
- Use eager loading or `JOIN` when fetching parent-child relationships.
|
|
- In ORMs, use `include`/`with`/`joinedload` rather than lazy loading in loops.
|
|
- For GraphQL, use DataLoader or equivalent batching to collapse repeated queries.
|
|
- Add automated N+1 detection in tests using query counting assertions.
|
|
|
|
## Indexing
|
|
- Create indexes on all foreign key columns.
|
|
- Create indexes on columns used in `WHERE`, `ORDER BY`, and `JOIN` clauses.
|
|
- Use composite indexes for queries that filter on multiple columns. Column order matters.
|
|
- Use partial indexes for queries that filter on a fixed condition (e.g., `WHERE deleted_at IS NULL`).
|
|
- Monitor slow query logs and add indexes for queries exceeding 100ms.
|
|
- Remove unused indexes. They slow down writes and waste storage.
|
|
|
|
## Migrations
|
|
- Migrations are forward-only in production. Never edit a deployed migration.
|
|
- Each migration must be reversible with a corresponding down migration.
|
|
- Use non-locking migration strategies for large tables (add column, backfill, then add constraint).
|
|
- Test migrations against a copy of production data before deploying.
|
|
- Name migrations descriptively: `20260115_add_orders_status_index.sql`.
|
|
|
|
## Schema Design
|
|
- Use UUIDs (v7 for sortability) as primary keys for public-facing entities.
|
|
- Add `created_at` and `updated_at` timestamps to all tables with database-level defaults.
|
|
- Use soft deletes (`deleted_at` timestamp) for user-facing data. Hard delete only for system data.
|
|
- Normalize to third normal form by default. Denormalize intentionally with a documented reason.
|
|
- Use `ENUM` types or reference tables for fixed value sets, not free-text columns.
|
|
|
|
## Connection Management
|
|
- Use connection pooling (PgBouncer, HikariCP, or ORM-level pooling).
|
|
- Set pool size to `(2 * CPU cores) + number of disks` as a starting point.
|
|
- Set query timeouts: 5 seconds for web requests, 30 seconds for background jobs.
|
|
- Handle connection failures with retry logic and exponential backoff.
|