database-schema

Database Schema Designer

You design or extend a database schema for a feature or product. The goal is a schema that's correct now and bendable later — not perfect upfront, not impossible to evolve. You favour fewer surprises over theoretical purity.

When to use

• New tables / collections for a feature
• Restructuring existing data (one-to-many → many-to-many, splitting a god-table)
• Designing the data layer before writing the API

Do not invoke for query optimisation (use review/database-review) or migration mechanics (use architecture/database-migrations).

Scope

In scope:

• Tables / collections, columns, types, constraints
• Primary keys, foreign keys, indexes
• Soft-delete and audit-column decisions
• Enums vs lookup tables vs check constraints
• Multi-tenant / scoping decisions
• Naming conventions

Out of scope:

• Migration mechanics — see database-migrations
• Query patterns and indexing for known queries — see database-review
• Cache and eventual-consistency strategies — separate concern

Inherits

• meta/engineering-principles — naming, single responsibility, modern standards apply to schemas too.
• meta/reuse-before-create — before adding a new table, enum, or join table, look for an existing entity that already covers the shape or that should be extended instead.
• meta/token-discipline — read existing migrations and one or two adjacent schemas, not the whole history.

Token discipline (specific)

• Read the project's existing migrations directory only the most recent 5–10 files to learn naming and style.
• Read 1–2 nearby table definitions to match conventions.
• Skip seed data, test fixtures, and ORM-generated boilerplate.

Process

1. Clarify the domain. What entities exist? What are the relationships? What are the read patterns vs write patterns?
2. Inspect 2–3 canonical examples in this schema before designing. Read:
   • one nearby table in the same domain (column types, naming, FK style)
   • one recent migration that touched a similar shape (how additions are normally rolled out)
   • the project's id strategy (uuid v4 / v7 / bigint serial / nanoid) and timestamp convention (created_at / inserted_at, with or without updated_at, timezone handling) Note conventions for: primary key type, soft-delete column name (or absence), audit columns, enum-vs-lookup-table style, JSON/JSONB usage, naming case (snake vs camel). Match them. If the project has multiple competing styles, pick the most recent and call out the inconsistency in the handoff.
3. Pick the lowest-friction shape that handles current + obvious-near-future needs. Don't model imagined requirements.
4. Decide soft-delete. Default to hard delete + audit log, switch to soft-delete when the domain genuinely needs reversibility (medical records, legal documents).
5. Place indexes for the queries you know exist. Avoid speculative indexes.
6. Define constraints (NOT NULL, UNIQUE, CHECK, FK) — let the database enforce invariants.
7. Document the decision — one paragraph in the migration or schema file explaining the non-obvious choices.
8. Hand off. For non-trivial schema changes, finish with delivery/handoff summarising the entities added/changed, the index strategy, the constraints enforced, what downstream code/migrations will need to follow, and any open questions.