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:
- What are the real business entities or aggregates?
- Which reads, writes, filters, joins, or reports are highest value?
- Is this greenfield design, live-system change, or schema cleanup?
- Which rules are true business invariants versus temporary implementation convenience?
- 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:
- One data lane and one output packet were chosen.
- Ownership, lifecycle, and query-critical fields are explicit.
- Flexible metadata is justified instead of acting as deferred modeling debt.
- Migration safety is believable for a live system.
- API/auth/testing/reporting/observability work was routed out instead of silently absorbed.
- 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
- Start from business invariants and access patterns, not table aesthetics.
- Treat migration safety as part of schema design, not a later ops chore.
- Keep flexible metadata honest: useful when justified, dangerous when it hides query-critical fields.
- Separate mutable state, history/audit, and derived/reporting data.
- Route adjacent API/auth/testing/reporting work outward instead of turning this into a generic backend mega-skill.
- Prefer a durable packet over giant vendor-specific example dumps.
References
1---2name: database-schema-design3description: >-4 Design storage-model and migration-safety packets for relational, document-heavy, and hybrid5 data systems. Use when the user needs entity ownership, constraints, indexes, staged schema6 changes, or queryable-vs-flexible field decisions across backend/fullstack products, internal7 ops tools, marketing/customer-data workflows, or game/live-ops systems. Route API contracts to8 api-design, auth-owned identity/session modeling to authentication-setup, verification to9 backend-testing, and reporting/telemetry follow-through to looker-studio-bigquery or10 monitoring-observability.11license: MIT12allowed-tools: Bash Read Write Edit Glob Grep13metadata:14 source: https://skillmd.com/skills/akillness/database-schema-design15 version: 2.1.016 modernization: 2026-04-1417 hardening: 2026-04-1918 tags: database, schema-design, migrations, indexing, constraints, relational, nosql, backend19 platforms: Claude, ChatGPT, Gemini20---21222324252627# Database Schema Design2829Use this skill when the main job is **choosing and evolving the storage model**, not dumping generic SQL or ORM snippets.3031`database-schema-design` is the backend storage-design anchor for:32- choosing between relational-first, document-heavy, and hybrid models33- turning domain entities into tables, collections, ownership boundaries, and lifecycle rules34- justifying constraints, indexes, tenant scope, history/audit structures, and deletion/retention behavior35- planning staged schema evolution so migrations, backfills, and cleanup are believable36- handing downstream teams one compact storage-design packet before implementation, verification, reporting, or observability work branches out3738Read these support docs before handling larger or riskier work:39- [references/storage-decision-matrix.md](references/storage-decision-matrix.md)40- [references/schema-review-checklist.md](references/schema-review-checklist.md)41- [references/intake-packets-and-route-outs.md](references/intake-packets-and-route-outs.md)4243## When to use this skill44- Design a new schema for a product feature, internal tool, admin workflow, customer-data surface, or live-ops/game backend system.45- Refactor an existing storage model with weak constraints, naming drift, poor cardinality modeling, or untrusted indexing.46- Decide which fields must be first-class columns or indexed document fields versus flexible metadata payloads.47- Plan multi-tenant, audit-log, entitlement, status-history, retention, or soft-delete boundaries.48- Review whether a migration is safe, staged realistically, and honest about backfills, compatibility windows, and cleanup.49- Produce one bounded storage packet before implementation or while a risky backend change is being shaped.5051## When not to use this skill52- **The main job is REST/GraphQL contract shape, endpoint behavior, webhook semantics, or versioning** → `api-design`.53- **The main job is identity/session/provider setup or auth-owned user/org boundaries** → `authentication-setup`.54- **The main job is migration verification, repository coverage, or contract/regression tests** → `backend-testing`.55- **The main job is published docs, quickstarts, or developer-facing schema/API explanations** → `api-documentation`.56- **The main job is broad hardening beyond data integrity, like secret handling, CSRF, cookies, or abuse controls** → `security-best-practices`.57- **The main job is dashboard/reporting presentation or telemetry/alert coverage on top of already-modeled data** → `looker-studio-bigquery` or `monitoring-observability`.58- 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.5960## Instructions6162### Step 1: Classify one primary storage-design packet63Use one primary lane and one smallest useful artifact.6465```yaml66schema_packet:67 workload_shape: oltp | analytics-adjacent | event-log | content-heavy | mixed | unknown68 data_lane: relational-first | document-heavy | hybrid | unknown69 change_type: greenfield | incremental | migration | cleanup | scale-fix70 ownership_focus: product-core | internal-ops | marketing-customer-data | game-live-ops | mixed71 durability_needs: basic | transactional | audit-heavy | compliance-sensitive | unknown72 hottest_risk: integrity | queryability | migration-safety | lifecycle-drift | unclear73 output_packet: design-memo | schema-review | migration-rollout | erd-plus-decisions | unknown74```7576Normalize first:771. What are the real business entities or aggregates?782. Which reads, writes, filters, joins, or reports are highest value?793. Is this greenfield design, live-system change, or schema cleanup?804. Which rules are true business invariants versus temporary implementation convenience?815. Which platform constraints already exist (database engine, ORM, hosted service, compliance, scale)?8283### Step 2: Gather the minimum credible evidence84Do not design storage from vibes alone. Pull the smallest packet that supports real decisions:85- product/domain objective86- current schema, models, or representative records if they exist87- known reads/writes, filters, joins, search/reporting needs, and retention rules88- tenant/ownership, audit/history, and deletion expectations89- rollout constraints: traffic, migration windows, lock risk, compatibility concerns, downstream consumers90- open questions that would make the design fake-ready9192If the evidence is thin, say so explicitly and keep the packet at review/memo level instead of pretending it is implementation-ready.9394### Step 3: Choose the data lane deliberately95Use [references/storage-decision-matrix.md](references/storage-decision-matrix.md).9697- **Relational-first** when integrity, transactions, shared invariants, joins, or reporting matter most.98- **Document-heavy** when one aggregate is usually read/written together and the shape varies enough that strict relational modeling would be fake precision.99- **Hybrid** when the transactional core is stable but some metadata/content payloads are legitimately flexible.100101State the reason in one or two sentences. “Because the stack already uses it” is useful context, not the whole rationale.102103### Step 4: Model ownership, lifecycle, and query-critical fields104For each core entity/collection/aggregate, define:105- purpose and ownership boundary106- identifier strategy107- required vs optional attributes108- lifecycle states and timestamps109- relationships or reference direction110- tenant/org ownership if relevant111- deletion, archival, retention, and history rules112- which fields must stay queryable, unique, or reportable113- which fields can remain flexible metadata without harming search/reporting/ops114115Watch for these traps:116- mirroring UI objects instead of business concepts117- hiding many-to-many or history in JSON blobs or ad hoc arrays118- collapsing mutable state, audit history, and derived/cache data into one table/document119- letting auth/profile/session ownership blur into unrelated product entities120- storing analytics, telemetry, or campaign attributes in opaque payloads when they already drive filtering, reporting, or live-ops decisions121122### Step 5: Design integrity and access rules together123A schema is only as good as the invariants it can defend.124125Name:126- keys and ownership rules127- uniqueness / nullability / defaults / state constraints128- hottest reads, writes, filters, joins, or aggregate lookups129- indexes and why each one exists130- intentional denormalization or flexible fields and the reason they stay flexible131132If you cannot name the main query shapes, the indexing guidance is probably fake.133134### Step 6: Plan rollout and route-outs135For incremental or live-system changes, define:136- additive vs destructive changes137- expand-and-contract, backfill, dual-read/write, or shadow-read phases if needed138- when indexes/constraints become safe to enforce139- rollback or stop conditions140- cleanup conditions for old columns/tables/doc fields141142Then route adjacent work clearly using [references/intake-packets-and-route-outs.md](references/intake-packets-and-route-outs.md):143- `api-design` for interface or contract changes that depend on the model144- `authentication-setup` for identity/session/provider ownership145- `backend-testing` for migration verification and regression coverage146- `looker-studio-bigquery` when the main job is stakeholder dashboards/reporting over curated data147- `monitoring-observability` when the main job is telemetry freshness, alert coverage, or runtime visibility148- `security-best-practices` when the concern goes beyond data integrity into broader app/web hardening149150### Step 7: Run the boundary check151Use [references/schema-review-checklist.md](references/schema-review-checklist.md) before finalizing.152153Verify:1541. One data lane and one output packet were chosen.1552. Ownership, lifecycle, and query-critical fields are explicit.1563. Flexible metadata is justified instead of acting as deferred modeling debt.1574. Migration safety is believable for a live system.1585. API/auth/testing/reporting/observability work was routed out instead of silently absorbed.1596. The packet ends with the next concrete move.160161## Output format162163```markdown164## Storage Design Packet: [System or Feature]165166### Packet framing167- Workload shape:168- Chosen data lane:169- Change type:170- Ownership focus:171- Why this lane fits:172173### Evidence used174- Current artifacts:175- Query/reporting needs:176- Lifecycle or retention constraints:177- Assumptions / gaps:178179### Entity / collection map180| Entity | Purpose | Key fields | Relationships / ownership | Lifecycle notes |181|--------|---------|------------|----------------------------|-----------------|182| ... | ... | ... | ... | ... |183184### Integrity and access rules185- Required constraints:186- Uniqueness / nullability notes:187- Indexes and why:188- Flexible metadata that stays flexible:189190### Rollout / migration plan191- Sequence:192- Backfill / compatibility notes:193- Cleanup conditions:194- Stop / rollback signals:195196### Route-outs197- API / contract:198- Auth / identity:199- Verification:200- Reporting / observability:201202### Recommended next move203- draft migration plan | review with owners | hand off to API/auth/testing/reporting | defer until missing evidence is gathered204```205206## Examples207208### Example 1: SaaS core model209**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."210211**Good output direction**212- chooses `relational-first`213- separates users, organizations, memberships, subscriptions, and immutable invoice records clearly214- treats audit/history separately from mutable entity state215- routes session/provider details to `authentication-setup`216217### Example 2: Flexible metadata migration218**Input:** "Our marketplace stores product metadata in one JSON column, but search, moderation, and filters now depend on stable fields. Plan a safe migration."219220**Good output direction**221- chooses `hybrid`222- identifies which fields must graduate from JSON into first-class columns or indexed fields223- proposes staged rollout, backfill, and cleanup conditions224- routes verification to `backend-testing`225226### Example 3: Game live-ops boundary227**Input:** "We need to model player inventory, seasonal event progress, and telemetry for a live game without burying reporting needs in opaque blobs."228229**Good output direction**230- separates player-owned transactional state from telemetry/reporting concerns231- keeps one storage packet focused on state integrity and lifecycle232- routes dashboard/telemetry follow-through to `looker-studio-bigquery` or `monitoring-observability`233234## Best practices2351. Start from business invariants and access patterns, not table aesthetics.2362. Treat migration safety as part of schema design, not a later ops chore.2373. Keep flexible metadata honest: useful when justified, dangerous when it hides query-critical fields.2384. Separate mutable state, history/audit, and derived/reporting data.2395. Route adjacent API/auth/testing/reporting work outward instead of turning this into a generic backend mega-skill.2406. Prefer a durable packet over giant vendor-specific example dumps.241242## References243- [PostgreSQL Data Definition docs](https://www.postgresql.org/docs/current/ddl.html)244- [Prisma Data Guide — Making Connections](https://www.prisma.io/dataguide/datamodeling/making-connections)245- [MongoDB data modeling docs](https://www.mongodb.com/docs/manual/data-modeling/)246- [Firestore structure-data guide](https://firebase.google.com/docs/firestore/manage-data/structure-data)