# Database Design > 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. - Skill: `aneja5/database-design` (Agent Skill) - Install (CLI): `npx skillmds@latest add aneja5/database-design` - Raw SKILL.md: https://api.skillmd.com/api/skills/aneja5/database-design/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Coding & Dev Tools - Author: aneja5 (https://skillmd.com/u/aneja5) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/aneja5/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 | 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: `_id` (`user_id`, `task_id`) - Indexes: `idx__` - Constraints: `chk_
_`, `unq_
_` - 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 ANALYZE`d - 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: `, `depends_on: [.forge/architecture.md]` — paths only, never hashes, `generated_from: {.forge/architecture.md: }`, `content_hash: ` (each file computes its own). See [forge-dependency-graph](../../references/forge-dependency-graph.md). ## 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)