SQL Database Agent Rules
Project Context
You are designing schemas and writing queries for a PostgreSQL production database. Schema design decisions have long-lasting consequences — every table structure, index choice, and constraint should be deliberate and documented.
Code Style & Naming
- Write SQL keywords in UPPERCASE: `SELECT`, `FROM`, `WHERE`, `JOIN`, `ON`.
- Use `snake_case` for all identifiers: tables, columns, indexes, constraints, functions, schemas.
- Name tables as plural nouns: `users`, `orders`, `order_items`, `audit_logs`.
- Prefix boolean columns: `is_active`, `has_verified_email`, `can_edit`.
- Name indexes: `idx_{table}_{column(s)}` — `idx_orders_user_id`, `idx_events_created_at`.
- Name constraints with prefixes: `pk_users`, `uq_users_email`, `fk_orders_user_id`, `chk_orders_positive_amount`.
- Use `created_at`, `updated_at`, and `deleted_at` (for soft deletes) consistently across all tables.
Schema Design
- Every table must have a primary key: `BIGINT GENERATED ALWAYS AS IDENTITY` for internal sequential IDs, `UUID` for public-facing or distributed systems.
- Use `TIMESTAMPTZ` for all timestamps — never `TIMESTAMP WITHOUT TIME ZONE`, which silently drops timezone information.
- Use `NUMERIC(10, 2)` for monetary amounts — never `FLOAT` or `DOUBLE PRECISION` due to floating-point rounding.
- Add `NOT NULL` on every column unless `NULL` has deliberate semantic meaning for that field.
- Define `ON DELETE` behavior on every foreign key: `CASCADE` for owned child records, `SET NULL` for optional references, `RESTRICT` to prevent orphaned data.
- Normalize to 3NF by default. Denormalize only with explicit documentation and benchmarks showing measurable query improvement.
- Add `CHECK` constraints for domain rules inline: `CONSTRAINT chk_positive_amount CHECK (amount > 0)`.
- Include `created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()` and `updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()` on every table.
- Use partial indexes to enforce conditional uniqueness: `CREATE UNIQUE INDEX uq_active_users_email ON users(email) WHERE deleted_at IS NULL`.
Query Patterns
- Use explicit `JOIN` syntax with `ON` conditions — never comma-separated table lists with implicit joins.
- Use CTEs with `WITH` to decompose complex queries into named, readable steps. Name each CTE after what it computes.
- Use window functions instead of self-joins for ranking, running totals, and period comparisons.
- Always specify a column list in `SELECT` — never `SELECT *` in application code.
- Use `EXISTS` instead of `IN` with subqueries when checking for row membership in large result sets.
- Use keyset (cursor) pagination: `WHERE id > $last_id ORDER BY id LIMIT 20` — offset pagination degrades at high page numbers.
- Use `COALESCE(column, fallback)` for nullable columns in output rather than application-side null handling.
- Rewrite `NOT IN (subquery)` as `NOT EXISTS` — `NOT IN` returns no rows if the subquery contains any `NULL`.
Indexing
- Run `EXPLAIN (ANALYZE, BUFFERS)` on every hot-path query before deploying. Compare estimated vs actual row counts.
- Create indexes on columns in `WHERE`, `JOIN ON`, `ORDER BY`, and `GROUP BY` clauses.
- Use composite indexes with the highest-selectivity column first for multi-column filter queries.
- Create partial indexes for queries that always include a fixed condition: `CREATE INDEX idx_active_users ON users(email) WHERE is_active = TRUE`.
- Use `GIN` indexes for `JSONB`, `tsvector`, and array columns. Use `GIST` for geometric and range types.
- Detect unused indexes with `pg_stat_user_indexes WHERE idx_scan = 0`. Drop them — each index slows writes.
- Use `CREATE INDEX CONCURRENTLY` to add indexes without locking the table.
Transactions & Concurrency
- Wrap multi-statement mutations in `BEGIN` / `COMMIT` for atomicity.
- Set isolation level explicitly when `READ COMMITTED` default is insufficient: `SET TRANSACTION ISOLATION LEVEL REPEATABLE READ`.
- Use `SELECT ... FOR UPDATE` to lock rows before modification in concurrent update scenarios.
- Use `SELECT ... FOR UPDATE SKIP LOCKED` for task queue patterns to avoid lock contention.
- Keep transactions short — long-running transactions hold locks, block autovacuum, and cause replication lag.
Migrations
- Use a migration tool (Flyway, Liquibase, Alembic, Drizzle Kit) — never apply DDL manually in production.
- Name migration files with timestamps: `20240115_add_orders_user_id_index.sql`.
- Write reversible migrations: include both `up` and `down` SQL.
- Add new `NOT NULL` columns safely: add as nullable → backfill in batches → add `NOT NULL` constraint in a subsequent migration.
- Use `ALTER TABLE ... ADD COLUMN` with a `DEFAULT` expression, not a static value, to avoid a full table rewrite on PostgreSQL 11+.
- Test migrations against a production-size data clone before deploying.
Security
- Use parameterized queries exclusively. Never concatenate user input into SQL strings.
- Grant the minimum permissions: `GRANT SELECT, INSERT, UPDATE ON orders TO app_user` — no `SUPERUSER`, no blanket `ALL`.
- Create separate database roles: one for the application (DML only), one for read-only analytics, one for schema migrations.
- Enable TLS for all client connections. Never allow unencrypted connections from application servers.
- Encrypt PII columns at the application level or with `pgcrypto`. Do not rely solely on database-level access control.
Monitoring & Maintenance
- Configure `autovacuum` aggressively for high-write tables to prevent dead tuple accumulation and table bloat.
- Monitor `pg_stat_statements` for slow queries. Tune the top 10 by total time first.
- Set `statement_timeout` per application role to prevent runaway queries from blocking the database.
- Use PgBouncer or RDS Proxy for connection pooling. Direct connections from serverless functions exhaust `max_connections` quickly.
- Configure automated backups with point-in-time recovery. Test restore procedures at least quarterly.
SQL
Standard
