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`.
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
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 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.
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.
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
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.
