| name | database-design |
| description | Use when designing a new schema, writing a migration, adding or auditing indexes, reviewing query performance, choosing between hard delete and soft delete, or establishing database conventions for a project. |
Database Design
Overview
Define the project's schema conventions and migration safety policy before migrations land in production. Outputs .forge/database-design.md (naming, FK rules, audit columns, soft-delete policy, index strategy, partition strategy, RLS/tenant-scoping rules) and .forge/migrations-policy.md (reversibility, idempotency, locking-aware patterns, the query-review checklist). Consumed by architecture-and-contracts and incremental-implementation.
When to Use
- A new project starts and the schema is being designed
- A migration is being written that touches a table >1M rows
- An ORM is generating N+1 queries in a hot path
- An audit reveals tables with no FK constraints or missing indexes
- The team is debating soft delete vs hard delete and there's no policy
- A production query is timing out and there's no EXPLAIN process
When NOT to Use
- Adding a column to a tiny dev-only table where conventions already apply
- Schema-less stores (Mongo, DynamoDB) — see
architecture-and-contracts data-model section instead
- A migration on an existing project that has documented conventions and the change is local
Common Rationalizations
| Thought | Reality |
|---|
| "We can add indexes later" | Missing indexes in production cause outages, not slowdowns. A full table scan on a 50M-row table locks the DB. |
| "Soft delete is always better" | Soft delete without a cleanup job becomes data hoarding. GDPR forces real deletion eventually. |
| "ORMs handle it" | ORMs generate N+1 queries by default. Lazy loading + a loop = production incident. |
| "One big migration is fine" | Irreversible migrations are deployment landmines. Forward-only with a long expand-contract window is the safe path. |
| "We don't need FK constraints, the app enforces it" | The app has bugs. The DB doesn't. FK constraints are the last line of defense against orphan rows. |
| "Let's just denormalize for speed" | Denormalization without a sync job creates two sources of truth that diverge. |
Red Flags
ALTER TABLE large_table ADD COLUMN ... NOT NULL with no locking review- Foreign key columns without indexes
- A migration that drops or renames a column without a deprecation window
- Soft delete columns (
deleted_at) with no cleanup or RLS policy
- ORM
findAll() followed by .map(item => item.related) (N+1)
- A query that scans rows without an EXPLAIN run
- No
created_at / updated_at audit columns
- Multi-tenant tables without RLS or
tenant_id indexes
Core Process
Step 1: Define naming conventions
Write these to .forge/database-design.md:
- Tables:
snake_case, plural (users, task_assignments)
- Columns:
snake_case, no abbreviations (created_at not cr_at)
- Primary keys:
id (UUIDv7 preferred — sortable, no hot shards)
- Foreign keys:
<table_singular>_id (user_id, task_id)
- Indexes:
idx_<table>_<columns>
- Constraints:
chk_<table>_<rule>, unq_<table>_<columns>
- Audit columns required on every business table:
created_at, updated_at (timestamptz, server-set)
Step 2: Audit existing schema (if applicable)
Run a check: every FK has an index? every business table has audit columns? every soft-delete table has cleanup? Record violations in .forge/database-design.md with an owner and a deadline.
Step 3: Set migration guardrails
In .forge/migrations-policy.md:
- Reversibility: every migration has an explicit
down. Forward-only migrations require an ADR.
- Idempotency:
CREATE TABLE IF NOT EXISTS, ADD COLUMN IF NOT EXISTS, CREATE INDEX IF NOT EXISTS.
- Locking-aware:
CREATE INDEX CONCURRENTLY on Postgres. Online schema change (gh-ost, pt-osc) for MySQL on large tables. Never ALTER TABLE with ACCESS EXCLUSIVE LOCK during business hours.
- Expand-contract pattern: add new column → backfill → switch reads → drop old column over multiple deploys.
- Backup before destructive: any
DROP COLUMN, DROP TABLE, TRUNCATE, or destructive UPDATE requires a snapshot reference in the PR.
Step 4: Write the query-review checklist
Append to .forge/database-design.md:
- Every hot-path query has been EXPLAINed (Postgres) or
EXPLAIN ANALYZEd
- No sequential scan on a table >100k rows
- No N+1 — verify with query logs in a test that simulates the loop
- Index used for every JOIN and WHERE condition
- LIMIT used on any user-facing list query
- Pagination uses cursor (not OFFSET) for large tables
Step 5: Identify partition candidates
Tables likely to exceed 100M rows (events, logs, audit, time-series): plan partitioning before they grow. Document the partition key (usually created_at range or tenant_id hash) and the retention/cleanup job.
Step 6: Document RLS / tenant-scoping rules
For multi-tenant systems:
- Every business table has
tenant_id and a Row-Level Security policy
- Application-side defense (every query filters by
tenant_id) + DB-side defense (RLS) — both, not either-or
- The contract from
architecture-and-contracts MUST specify which tables are tenant-scoped
Step 7: Headers
Prepend a forge:meta header on both .forge/database-design.md and .forge/migrations-policy.md (co-output — both share the same depends_on + generated_from): generated_by: database-design, generated_at: <ISO 8601 UTC with Z>, depends_on: [.forge/architecture.md] — paths only, never hashes, generated_from: {.forge/architecture.md: <upstream content_hash AT generation time>}, content_hash: <sha256 first 8 of THIS file's body> (each file computes its own). See forge-dependency-graph.
Verification
