Database Design

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

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

• .forge/database-design.md written with naming conventions, soft-delete policy, partition strategy
• .forge/migrations-policy.md written with reversibility, idempotency, locking-aware rules
• Every migration in the codebase is idempotent (IF NOT EXISTS or equivalent)
• Every FK has an index (verified via schema query)
• Every business table has created_at and updated_at
• Every hot-path query has an EXPLAIN attached to the PR (or linked in .forge/database-design.md)
• No migration drops user-visible data without an ADR
• Multi-tenant tables have RLS policies and tenant_id indexes
• N+1 queries are gone from the codebase (verified by query-count test in CI)