database-schema-design · diff
v2.0.0 to v2.1.0
164 added, 201 removed. Audit A to A.
---
name: database-schema-design
description: >
- Design or refactor application data models and schema evolution plans for relational
- and document databases. Use when the user needs entity boundaries, table/collection
- shape, cardinality, constraints, indexes, naming, multi-tenant or audit patterns,
- migration sequencing, or rollout-safe schema changes before or alongside backend
- implementation. Not for API contract design, auth setup, published docs, or backend
- test planning.
+ Design storage-model and migration-safety packets for relational, document-heavy,
+ and hybrid data systems. Use when the user needs entity ownership, constraints,
+ indexes, multi-tenant or audit boundaries, staged schema changes, or queryable-vs-
+ flexible field decisions across backend/fullstack products, internal ops tools,
+ marketing/customer-data workflows, or game/live-ops systems. Route API contracts
+ to `api-design`, auth-owned identity/session modeling to `authentication-setup`,
+ verification to `backend-testing`, and reporting/telemetry follow-through to
+ `looker-studio-bigquery` or `monitoring-observability`.
allowed-tools: Bash Read Write Edit Glob Grep
compatibility: >
- Best for backend, product, and fullstack work in web apps and APIs where schema
- choices must balance developer ergonomics, query patterns, integrity rules, and
- production-safe migrations across PostgreSQL, MySQL, SQLite, MongoDB, and similar
- stacks.
+ Best for backend and fullstack systems where schema choices must balance integrity,
+ query shape, lifecycle rules, and rollout safety across PostgreSQL, MySQL, SQLite,
+ MongoDB, Firestore, and similar stacks.
license: MIT
metadata:
- version: "2.0.0"
+ version: "2.1.0"
modernization: 2026-04-14
+ hardening: 2026-04-19
tags: database, schema-design, migrations, indexing, constraints, relational, nosql, backend
platforms: Claude, ChatGPT, Gemini
---
# Database Schema Design
- Use this skill when the main job is **choosing and evolving the storage model for a real product**, not dumping generic SQL examples.
+ Use this skill when the main job is **choosing and evolving the storage model**, not dumping generic SQL or ORM snippets.
- `database-schema-design` owns the storage-design layer for:
- - choosing relational vs document-heavy vs hybrid data shapes
- - turning domain entities into tables, collections, join models, and ownership boundaries
- - deciding keys, uniqueness, cardinality, nullability, lifecycle state, and deletion/audit rules
- - planning indexes, partitioning, and read-vs-write tradeoffs
- - sequencing safe schema evolution so migrations, backfills, and cleanup do not get hand-waved
- - producing a design packet that implementation, auth, API, and testing work can share
+ `database-schema-design` is the backend storage-design anchor for:
+ - choosing between relational-first, document-heavy, and hybrid models
+ - turning domain entities into tables, collections, ownership boundaries, and lifecycle rules
+ - justifying constraints, indexes, tenant scope, history/audit structures, and deletion/retention behavior
+ - planning staged schema evolution so migrations, backfills, and cleanup are believable
+ - handing downstream teams one compact storage-design packet before implementation, verification, reporting, or observability work branches out
Read these support docs before handling larger or riskier work:
- [references/storage-decision-matrix.md](references/storage-decision-matrix.md)
- [references/schema-review-checklist.md](references/schema-review-checklist.md)
+ - [references/intake-packets-and-route-outs.md](references/intake-packets-and-route-outs.md)
## When to use this skill
- - Design a new database schema for a product feature, service, admin app, marketplace, or internal tool
- - Refactor an existing schema that has naming drift, weak constraints, poor cardinality modeling, or missing indexes
- - Decide between normalized relational tables, document/JSON fields, event-style append models, or a deliberate hybrid
- - Plan multi-tenant, soft-delete, audit-log, status-history, or entitlement-related storage patterns
- - Review whether a migration is safe, reversible enough, and staged realistically for production traffic
- - Choose indexes and data-access shape based on actual query patterns instead of guesswork
- - Produce a storage-model packet before backend implementation starts or while a risky backend change is being designed
+ - Design a new schema for a product feature, internal tool, admin workflow, customer-data surface, or live-ops/game backend system.
+ - Refactor an existing storage model with weak constraints, naming drift, poor cardinality modeling, or untrusted indexing.
+ - Decide which fields must be first-class columns or indexed document fields versus flexible metadata payloads.
+ - Plan multi-tenant, audit-log, entitlement, status-history, retention, or soft-delete boundaries.
+ - Review whether a migration is safe, staged realistically, and honest about backfills, compatibility windows, and cleanup.
+ - Produce one bounded storage packet before implementation or while a risky backend change is being shaped.
## When not to use this skill
- - **The main job is REST/GraphQL interface shape, endpoint naming, versioning, or error semantics** → use `api-design`
- - **The main job is product-auth provider choice, sessions/JWTs, login methods, or auth-owned user/org boundaries** → use `authentication-setup`
- - **The main job is developer-facing docs, quickstarts, or example-heavy API/database documentation** → use `api-documentation`
- - **The main job is backend regression coverage, contract tests, migration-test strategy, or CI-vs-local verification** → use `backend-testing`
- - **The main job is broader security hardening such as secret handling, CSRF, cookie flags, or OWASP controls** → use `security-best-practices`
- - The request has no concrete domain, access pattern, or lifecycle context yet; in that case define the open questions instead of pretending the schema is settled
+ - **The main job is REST/GraphQL contract shape, endpoint behavior, webhook semantics, or versioning** → `api-design`.
+ - **The main job is identity/session/provider setup or auth-owned user/org boundaries** → `authentication-setup`.
+ - **The main job is migration verification, repository coverage, or contract/regression tests** → `backend-testing`.
+ - **The main job is published docs, quickstarts, or developer-facing schema/API explanations** → `api-documentation`.
+ - **The main job is broad hardening beyond data integrity, like secret handling, CSRF, cookies, or abuse controls** → `security-best-practices`.
+ - **The main job is dashboard/reporting presentation or telemetry/alert coverage on top of already-modeled data** → `looker-studio-bigquery` or `monitoring-observability`.
+ - The request has no real domain, access pattern, or lifecycle context yet; in that case return the missing questions instead of pretending the schema is settled.
## Instructions
- ### Step 1: Classify the storage-design job
- Normalize the request before naming tables.
+ ### Step 1: Classify one primary storage-design packet
+ Use one primary lane and one smallest useful artifact.
```yaml
- schema_design_profile:
- domain: unknown
- workload_shape: oltp | analytics | event-log | content-heavy | mixed | unknown
- data_model_lane: relational | document-heavy | hybrid | unknown
- tenancy: single-tenant | shared-tenant | isolated-tenant | mixed | unknown
+ schema_packet:
+ workload_shape: oltp | analytics-adjacent | event-log | content-heavy | mixed | unknown
+ data_lane: relational-first | document-heavy | hybrid | unknown
change_type: greenfield | incremental | migration | cleanup | scale-fix
+ ownership_focus: product-core | internal-ops | marketing-customer-data | game-live-ops | mixed
durability_needs: basic | transactional | audit-heavy | compliance-sensitive | unknown
- traffic_shape: read-heavy | write-heavy | mixed | bursty | unknown
- scale_notes: small | moderate | large | unknown
- main_risks: integrity | performance | migration-safety | data-lifecycle | unclear
+ hottest_risk: integrity | queryability | migration-safety | lifecycle-drift | unclear
+ output_packet: design-memo | schema-review | migration-rollout | erd-plus-decisions | unknown
```
- Ask or infer:
- 1. What product/domain entities exist, and which ones truly own the workflow?
- 2. What are the highest-value reads, writes, filters, joins, aggregates, and retention rules?
- 3. Is the task greenfield design, incremental change, or repairing drift in a live system?
- 4. Which constraints are real business rules versus implementation convenience?
- 5. What databases, ORMs, or platform constraints already exist?
-
- ### Step 2: Choose the data-model lane deliberately
- Do not default to a database style because it is fashionable.
+ Normalize first:
+ 1. What are the real business entities or aggregates?
+ 2. Which reads, writes, filters, joins, or reports are highest value?
+ 3. Is this greenfield design, live-system change, or schema cleanup?
+ 4. Which rules are true business invariants versus temporary implementation convenience?
+ 5. Which platform constraints already exist (database engine, ORM, hosted service, compliance, scale)?
- #### Prefer relational-first when
- - data integrity, transactions, and clear entity relationships matter most
- - the product has strong cardinality rules and shared business invariants
- - reporting, joining, and consistency matter more than free-form document flexibility
- - the team needs constraints and indexes to carry real business meaning
+ ### Step 2: Gather the minimum credible evidence
+ Do not design storage from vibes alone. Pull the smallest packet that supports real decisions:
+ - product/domain objective
+ - current schema, models, or representative records if they exist
+ - known reads/writes, filters, joins, search/reporting needs, and retention rules
+ - tenant/ownership, audit/history, and deletion expectations
+ - rollout constraints: traffic, migration windows, lock risk, compatibility concerns, downstream consumers
+ - open questions that would make the design fake-ready
- #### Prefer document-heavy when
- - entity shape varies a lot across records
- - nesting is natural and most reads happen around one aggregate document
- - the team accepts weaker cross-document integrity and more application-owned invariants
- - the product values ingestion flexibility more than relational rigor
+ If the evidence is thin, say so explicitly and keep the packet at review/memo level instead of pretending it is implementation-ready.
- #### Prefer hybrid when
- - transactional core data is relational but some secondary metadata is naturally JSON/document-shaped
- - the team can explain which fields are query-critical and which are flexible payloads
- - there is a clear reason not to force every attribute into first-class columns
+ ### Step 3: Choose the data lane deliberately
+ Use [references/storage-decision-matrix.md](references/storage-decision-matrix.md).
- State the reason for the chosen lane. “Because the stack already has it” is useful context, not a full design rationale.
+ - **Relational-first** when integrity, transactions, shared invariants, joins, or reporting matter most.
+ - **Document-heavy** when one aggregate is usually read/written together and the shape varies enough that strict relational modeling would be fake precision.
+ - **Hybrid** when the transactional core is stable but some metadata/content payloads are legitimately flexible.
- ### Step 3: Model entities, ownership, and lifecycle
- Turn the domain into explicit storage boundaries.
+ State the reason in one or two sentences. “Because the stack already uses it” is useful context, not the whole rationale.
- For each core entity or collection, define:
+ ### Step 4: Model ownership, lifecycle, and query-critical fields
+ For each core entity/collection/aggregate, define:
- purpose and ownership boundary
- - stable identifier strategy
+ - identifier strategy
- required vs optional attributes
- lifecycle states and timestamps
- - who references it and at what cardinality
- - whether deletion should be hard, soft, archived, or evented
- - whether tenant/org ownership, audit fields, or history tables are required
-
- Watch for classic modeling traps:
- - mixing lookup/reference data with user-generated mutable state
- - hiding many-to-many relationships inside ad hoc arrays or comma-separated fields
- - collapsing status history into a single overwritten status when history matters
- - storing every variable field as JSON without a query/access plan
- - letting auth/profile/org membership data blur into unrelated product entities
-
- ### Step 4: Design integrity rules and access patterns together
- A schema is only as good as the invariants it enforces.
-
- For relational lanes, define:
- - primary keys and foreign keys
- - uniqueness rules
- - nullability and defaults
- - check constraints and enum/state rules
- - join tables and ownership direction
+ - relationships or reference direction
+ - tenant/org ownership if relevant
+ - deletion, archival, retention, and history rules
+ - which fields must stay queryable, unique, or reportable
+ - which fields can remain flexible metadata without harming search/reporting/ops
- For document-heavy lanes, define:
- - aggregate/document boundary
- - embedded vs referenced substructures
- - per-document uniqueness assumptions
- - fields that still need app-level invariants or background reconciliation
+ Watch for these traps:
+ - mirroring UI objects instead of business concepts
+ - hiding many-to-many or history in JSON blobs or ad hoc arrays
+ - collapsing mutable state, audit history, and derived/cache data into one table/document
+ - letting auth/profile/session ownership blur into unrelated product entities
+ - storing analytics, telemetry, or campaign attributes in opaque payloads when they already drive filtering, reporting, or live-ops decisions
- For either lane, explicitly list:
- - hottest reads and filters
- - write amplification risks
- - query patterns that justify indexes
- - fields that need full-text, partial, composite, or time-based indexing
- - places where denormalization is intentional rather than accidental
+ ### Step 5: Design integrity and access rules together
+ A schema is only as good as the invariants it can defend.
- If you cannot name the main reads/writes, the indexing advice is probably fake.
+ Name:
+ - keys and ownership rules
+ - uniqueness / nullability / defaults / state constraints
+ - hottest reads, writes, filters, joins, or aggregate lookups
+ - indexes and why each one exists
+ - intentional denormalization or flexible fields and the reason they stay flexible
- ### Step 5: Plan schema evolution, not just the end state
- The design is incomplete if rollout safety is missing.
+ If you cannot name the main query shapes, the indexing guidance is probably fake.
+ ### Step 6: Plan rollout and route-outs
For incremental or live-system changes, define:
- - what changes are additive, backfill-driven, destructive, or compatibility-sensitive
- - whether the migration should be expand-and-contract
- - when constraints/indexes are added relative to application deploys
- - whether data backfill, dual write, or shadow read phases are needed
- - what the rollback or stop condition looks like
- - which old fields/tables can only be removed after traffic and job cleanup
-
- Good defaults:
- - add new columns/tables before switching reads/writes
- - backfill before enforcing new non-null or uniqueness rules when possible
- - treat destructive renames/drops as staged work, not one-step bravery
- - call out long-running backfills, lock risk, and index build cost explicitly
-
- ### Step 6: Produce the storage design packet
- Pick the lightest artifact that still gives downstream work a trustworthy map.
-
- Recommended artifacts:
- - **entity/table design memo** for early architecture work
- - **schema review packet** for PRD/spec/ADR-driven changes
- - **migration rollout plan** for live-system evolution
- - **ERD + decisions table** when collaboration and review are the main need
+ - additive vs destructive changes
+ - expand-and-contract, backfill, dual-read/write, or shadow-read phases if needed
+ - when indexes/constraints become safe to enforce
+ - rollback or stop conditions
+ - cleanup conditions for old columns/tables/doc fields
- Minimum packet:
- - workload and data-model lane with rationale
- - entity / collection map
- - key integrity rules and lifecycle decisions
- - index/access-pattern notes
- - migration or rollout plan if the system is live
- - open questions and risk notes
- - handoffs to adjacent skills
+ Then route adjacent work clearly using [references/intake-packets-and-route-outs.md](references/intake-packets-and-route-outs.md):
+ - `api-design` for interface or contract changes that depend on the model
+ - `authentication-setup` for identity/session/provider ownership
+ - `backend-testing` for migration verification and regression coverage
+ - `looker-studio-bigquery` when the main job is stakeholder dashboards/reporting over curated data
+ - `monitoring-observability` when the main job is telemetry freshness, alert coverage, or runtime visibility
+ - `security-best-practices` when the concern goes beyond data integrity into broader app/web hardening
- ### Step 7: Review for boundary quality and future pain
- Before finalizing, check:
- - does the model encode real business rules or just mirror current code objects?
- - are lifecycle, retention, audit, and deletion rules explicit enough?
- - are indexes justified by named queries, not superstition?
- - did you accidentally turn API shape, auth setup, or testing strategy into part of this skill?
- - is the migration path believable for a live system?
- - will another engineer understand what is normalized, denormalized, or intentionally flexible?
+ ### Step 7: Run the boundary check
+ Use [references/schema-review-checklist.md](references/schema-review-checklist.md) before finalizing.
- Route next steps clearly:
- - `api-design` for interface/contract shape that depends on the data model
- - `authentication-setup` for user/org/session/provider ownership decisions
- - `backend-testing` for migration verification, repository coverage, or contract/regression checks
- - `api-documentation` for published docs/examples once the model is stable enough to explain
- - `security-best-practices` for hardening that goes beyond data integrity modeling
+ Verify:
+ 1. One data lane and one output packet were chosen.
+ 2. Ownership, lifecycle, and query-critical fields are explicit.
+ 3. Flexible metadata is justified instead of acting as deferred modeling debt.
+ 4. Migration safety is believable for a live system.
+ 5. API/auth/testing/reporting/observability work was routed out instead of silently absorbed.
+ 6. The packet ends with the next concrete move.
## Output format
```markdown
## Storage Design Packet: [System or Feature]
- ### Design framing
- - Domain: [...]
- - Workload shape: [OLTP / analytics / content-heavy / mixed]
- - Chosen lane: [relational / document-heavy / hybrid]
- - Reason: [...]
+ ### Packet framing
+ - Workload shape:
+ - Chosen data lane:
+ - Change type:
+ - Ownership focus:
+ - Why this lane fits:
+ ### Evidence used
+ - Current artifacts:
+ - Query/reporting needs:
+ - Lifecycle or retention constraints:
+ - Assumptions / gaps:
+
### Entity / collection map
| Entity | Purpose | Key fields | Relationships / ownership | Lifecycle notes |
|--------|---------|------------|----------------------------|-----------------|
| ... | ... | ... | ... | ... |
### Integrity and access rules
- - Required constraints: [...]
- - Uniqueness rules: [...]
- - Indexes and why: [...]
- - Intentional denormalization / JSON usage: [...]
+ - Required constraints:
+ - Uniqueness / nullability notes:
+ - Indexes and why:
+ - Flexible metadata that stays flexible:
- ### Migration / rollout plan
- - Change type: [greenfield / additive / staged migration / cleanup]
- - Sequence: [...]
- - Backfill / compatibility notes: [...]
- - Removal / cleanup conditions: [...]
+ ### Rollout / migration plan
+ - Sequence:
+ - Backfill / compatibility notes:
+ - Cleanup conditions:
+ - Stop / rollback signals:
- ### Risks / open questions
- - [...]
+ ### Route-outs
+ - API / contract:
+ - Auth / identity:
+ - Verification:
+ - Reporting / observability:
- ### Handoffs
- - API contract: [...]
- - Auth/data ownership: [...]
- - Verification/testing: [...]
- - Docs: [...]
+ ### Recommended next move
+ - draft migration plan | review with owners | hand off to API/auth/testing/reporting | defer until missing evidence is gathered
```
## Examples
- ### Example 1: SaaS billing and organization model
- **Input:** “We need schema help for a B2B SaaS app with users, orgs, memberships, subscriptions, invoices, and audit logs. We use Postgres and need something implementation-ready, not just an ERD.”
+ ### Example 1: SaaS core model
+ **Input:** "We need schema help for a B2B SaaS app with users, organizations, memberships, subscriptions, invoices, and audit logs. We use Postgres and need something implementation-ready."
- **Good response shape:**
- - chooses relational-first because org membership, billing, and audit rules carry real integrity constraints
- - separates `users`, `organizations`, `memberships`, `subscriptions`, and immutable invoice records clearly
- - defines uniqueness and lifecycle rules for invites, membership role changes, and subscription history
- - calls out audit/event tables separately from mutable entity state
- - routes auth-provider/session details to `authentication-setup`
- - includes index suggestions tied to named reads such as org membership lookup, invoice history, and subscription status review
+ **Good output direction**
+ - chooses `relational-first`
+ - separates users, organizations, memberships, subscriptions, and immutable invoice records clearly
+ - treats audit/history separately from mutable entity state
+ - routes session/provider details to `authentication-setup`
- ### Example 2: Live migration from flexible JSON to structured fields
- **Input:** “Our marketplace has a `products.metadata` JSON blob. Search, filtering, and moderation now depend on stable fields. We need a safe migration plan without breaking production.”
+ ### Example 2: Flexible metadata migration
+ **Input:** "Our marketplace stores product metadata in one JSON column, but search, moderation, and filters now depend on stable fields. Plan a safe migration."
- **Good response shape:**
- - keeps the design focused on storage boundaries, not API docs
- - identifies which metadata fields deserve first-class columns and which can remain JSON
- - proposes expand-and-contract: add columns, backfill, dual read/write, then clean up
- - notes index timing and backfill risk explicitly
- - routes interface changes to `api-design` and migration verification to `backend-testing`
+ **Good output direction**
+ - chooses `hybrid`
+ - identifies which fields must graduate from JSON into first-class columns or indexed fields
+ - proposes staged rollout, backfill, and cleanup conditions
+ - routes verification to `backend-testing`
- ### Example 3: Document-heavy content workflow
- **Input:** “We ingest user-authored page blocks with highly variable nested content, but still need account ownership and search.”
+ ### Example 3: Game live-ops boundary
+ **Input:** "We need to model player inventory, seasonal event progress, and telemetry for a live game without burying reporting needs in opaque blobs."
- **Good response shape:**
- - justifies a hybrid or document-heavy lane instead of forcing over-normalization
- - separates stable ownership/account structures from flexible content payloads
- - names which fields must stay queryable and therefore deserve indexes or extracted columns
- - records lifecycle/retention choices for revisions and drafts
+ **Good output direction**
+ - separates player-owned transactional state from telemetry/reporting concerns
+ - keeps one storage packet focused on state integrity and lifecycle
+ - routes dashboard/telemetry follow-through to `looker-studio-bigquery` or `monitoring-observability`
## Best practices
1. Start from business invariants and access patterns, not table aesthetics.
- 2. Treat migration safety as part of schema design for live systems.
- 3. Use JSON/document flexibility intentionally, not as a way to postpone modeling forever.
- 4. Name the reads and writes that justify every important index.
- 5. Separate mutable entity state, history/audit data, and derived/cache data.
- 6. Keep adjacent handoffs explicit so this skill does not absorb API, auth, docs, or test work.
- 7. Prefer a durable design packet over giant vendor-specific SQL dumps.
+ 2. Treat migration safety as part of schema design, not a later ops chore.
+ 3. Keep flexible metadata honest: useful when justified, dangerous when it hides query-critical fields.
+ 4. Separate mutable state, history/audit, and derived/reporting data.
+ 5. Route adjacent API/auth/testing/reporting work outward instead of turning this into a generic backend mega-skill.
+ 6. Prefer a durable packet over giant vendor-specific example dumps.
## References
- - [Evolutionary Database Design — Martin Fowler](https://martinfowler.com/articles/evodb.html)
- - [Prisma Migrate mental model](https://www.prisma.io/docs/orm/prisma-migrate/understanding-prisma-migrate/mental-model)
- [PostgreSQL Data Definition docs](https://www.postgresql.org/docs/current/ddl.html)
- - [DBML docs](https://dbml.dbdiagram.io/docs)
+ - [Prisma Data Guide — Making Connections](https://www.prisma.io/dataguide/datamodeling/making-connections)
+ - [MongoDB data modeling docs](https://www.mongodb.com/docs/manual/data-modeling/)
+ - [Firestore structure-data guide](https://firebase.google.com/docs/firestore/manage-data/structure-data)