Database Schema Design

Database Schema Design

Use this skill when the main job is choosing and evolving the storage model, not dumping generic SQL or ORM snippets.

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/schema-review-checklist.md
• references/intake-packets-and-route-outs.md

When to use this skill

• 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 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 one primary storage-design packet

Use one primary lane and one smallest useful artifact.

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
  hottest_risk: integrity | queryability | migration-safety | lifecycle-drift | unclear
  output_packet: design-memo | schema-review | migration-rollout | erd-plus-decisions | unknown

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)?

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

If the evidence is thin, say so explicitly and keep the packet at review/memo level instead of pretending it is implementation-ready.

Step 3: Choose the data lane deliberately

Use references/storage-decision-matrix.md.

• 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.

State the reason in one or two sentences. “Because the stack already uses it” is useful context, not the whole rationale.

Step 4: Model ownership, lifecycle, and query-critical fields

For each core entity/collection/aggregate, define:

• purpose and ownership boundary
• identifier strategy
• required vs optional attributes
• lifecycle states and timestamps
• 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

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

Step 5: Design integrity and access rules together

A schema is only as good as the invariants it can defend.

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

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:

• 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

Then route adjacent work clearly using 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: Run the boundary check

Use references/schema-review-checklist.md before finalizing.

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

## Storage Design Packet: [System or Feature]

### 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 / nullability notes:
- Indexes and why:
- Flexible metadata that stays flexible:

### Rollout / migration plan
- Sequence:
- Backfill / compatibility notes:
- Cleanup conditions:
- Stop / rollback signals:

### Route-outs
- API / contract:
- Auth / identity:
- Verification:
- Reporting / observability:

### 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 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 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: 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 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: 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 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, 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

• PostgreSQL Data Definition docs
• Prisma Data Guide — Making Connections
• MongoDB data modeling docs
• Firestore structure-data guide

Intake Packets and Route-outs

Use this note when the request is real but the next artifact is not obvious yet.

1. Pick the smallest honest packet

A. design-memo

Use when the domain is still being shaped and the team needs a clear storage direction, not a migration script.

Best for:

• new product features
• internal tools and admin workflows
• early customer-data / live-ops modeling

Include:

• chosen data lane
• core entities / aggregates
• query-critical fields
• biggest unknowns

B. schema-review

Use when a PRD, ADR, issue, or technical design already exists and the main job is reviewing the storage model.

Best for:

• refactors
• risky index/constraint additions
• cross-team architecture review
• marketing/customer-data systems where reporting needs are already known

Include:

• evidence used
• integrity rules
• index rationale
• lifecycle / retention notes
• explicit route-outs

C. migration-rollout

Use when the schema is live and rollout safety is the actual risk.

Best for:

• JSON-to-column promotion
• table or collection splits
• rename / backfill / cleanup work
• large tenant-scoped data changes

Include:

• expand-and-contract sequence
• backfill plan
• compatibility window
• cleanup conditions
• rollback / stop signals

D. erd-plus-decisions

Use when collaboration and alignment matter more than migration detail.

Best for:

• multi-entity system design reviews
• vendor / service selection conversations
• game live-ops state design that needs a quick shared model

Include:

• entity map
• relationship direction
• lifecycle notes
• explicit decisions and deferred questions

2. Quick domain packets

Backend / SaaS core

• Stable account, billing, entitlement, and audit structures usually lean relational-first.
• Route auth/session/provider ownership to authentication-setup.
• Route API contract follow-through to api-design.

Product / ops systems

• Admin workflows often need clear status history, approvals, and audit trails.
• Watch for UI-shaped tables and nullable fields that actually hide missing product decisions.

Marketing / customer-data workflows

• Keep customer/account/event identity queryable.
• Treat campaign or attribution metadata as flexible only if reporting does not depend on it yet.
• Route dashboard and stakeholder reporting work to looker-studio-bigquery once the model is stable enough.

Game / live-ops systems

• Separate player-owned transactional state from telemetry/reporting streams.
• Keep inventory, progression, and entitlement integrity distinct from analytics/event ingestion.
• Route alerting, runtime visibility, or telemetry freshness work to monitoring-observability.

3. Route-out ladder

• api-design → interface shape, endpoint/webhook contracts, payload semantics
• authentication-setup → users/orgs/sessions/providers/identity ownership
• backend-testing → migration verification, repository coverage, contract/regression tests
• api-documentation → published docs once the model is stable enough to explain
• looker-studio-bigquery → stakeholder dashboards and KPI presentation over curated data
• monitoring-observability → telemetry freshness, alerts, dashboards, and runtime visibility
• security-best-practices → hardening beyond storage integrity

4. Common anti-patterns

• Treating the ORM model as the product truth without checking query or lifecycle reality
• Hiding many-to-many or history in JSON because the relationship felt inconvenient
• Pushing dashboard or telemetry design into the schema packet when the storage boundary is already good enough
• Pretending a destructive migration is safe because the final schema looks clean

Schema Review Checklist

Use this before finalizing a storage design packet or approving a risky schema change.

Domain and boundaries

• Are the core entities/collections named after business concepts rather than current UI code objects?
• Are ownership and tenant boundaries explicit?
• Are mutable state, history/audit data, and derived/cache data separated clearly?

Integrity rules

• Which fields must be unique?
• Which relationships need database-enforced foreign keys or equivalent ownership guarantees?
• Are nullability and defaults honest, or are they placeholders for missing product decisions?
• Are soft-delete, archival, and retention rules explicit?

Access patterns and indexes

• Can the team name the primary reads, writes, joins, and filters?
• Does every major index map to a real query pattern?
• Are any indexes missing for foreign keys, status/timestamp filters, or tenant scoping?
• Is denormalization or JSON usage justified by access patterns rather than convenience?

Migration safety

• Is the change additive, staged, backfill-heavy, or destructive?
• Can the rollout use expand-and-contract instead of a risky one-shot cutover?
• Are index build cost, lock risk, and backfill duration called out?
• Are cleanup/removal conditions explicit instead of assumed?

Adjacency and handoffs

• Should API contract updates be handed to api-design?
• Should auth-owned users/orgs/session decisions be handed to authentication-setup?
• Should migration/repository verification be handed to backend-testing?
• Should hardening beyond integrity rules be handed to security-best-practices?

Storage Decision Matrix

Use this matrix when a request blurs relational, document, and hybrid design.

1. Relational-first is usually the better default when

• the domain has strong integrity rules
• joins and cross-entity reporting matter
• many records reference each other over time
• uniqueness, lifecycle state, or money/entitlements need explicit constraints
• downstream teams need stable fields, not loosely shaped payloads

Typical examples:

• organizations / memberships / roles
• billing and invoice history
• orders / payments / fulfillment
• inventory, pricing, and moderation state

2. Document-heavy is usually better when

• each record is mostly read/written as one aggregate
• record shape varies significantly by customer/content type
• cross-record joins are rare or non-critical
• ingestion speed and flexibility matter more than strict cross-entity constraints

Typical examples:

• rich content blocks
• template definitions
• import payload archives
• AI generation traces or loosely structured annotations

3. Hybrid is usually the best answer when

• ownership and billing/account state are relational
• content or metadata needs flexible nested structure
• some fields are query-critical while others are best left flexible
• the team can explain which JSON fields must eventually graduate into columns

Typical examples:

• a marketplace listing with structured pricing/status plus flexible specs/attributes
• B2B account tables plus configurable workflow JSON
• transactional entities plus append-only event or audit payloads

Review prompts

• Which fields participate in joins, filters, or uniqueness checks?
• Which invariants should the database enforce directly?
• Which fields can change shape without breaking reporting, search, or moderation?
• Is JSON being used because it is right, or because the team has not modeled the domain yet?
• If the workload grows 10x, which fields would we wish were first-class columns?