Imported from akillness/jeo-skills (.agent-skills/database-schema-design/SKILL.md) via skills.sh. Install upstream with npx skills add akillness/jeo-skills --skill database-schema-design. Copyright stays with the author (MIT).

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