Database Design

Schema design, data modeling, migration safety, and ORM patterns. This skill covers structural decisions — what tables exist, how they relate, how schemas evolve. For tuning queries on an existing schema, use data-sql-optimization.

Quick Reference

Task	Default Picks	Notes
Relational database	PostgreSQL (a supported stable major)	Transactions, complex queries, JSON support; verify the supported major at https://www.postgresql.org/support/versioning/
Relational (MySQL family)	A supported MySQL LTS series	Use an LTS series for production, not an Innovation release; Innovation releases use calendar versioning, so the version number alone does not tell you the track. Check which series are LTS and their support end dates at https://dev.mysql.com/doc/mysql-lts-innovation-release-model/en/ before naming a version
Embedded / edge relational	SQLite	Release train moves fast (monthly point releases); pin a version, don't chase latest blindly
Flexible schema	MongoDB (a supported major)	Rapid iteration, embedded relationships; verify the major at https://www.mongodb.com/docs/manual/release-notes/
Caching / sessions	Redis	Ephemeral data, counters, pub/sub
Graph traversals	Neo4j	Relationship-heavy queries (social, fraud)
Time-series	TimescaleDB, InfluxDB	Metrics, IoT, event streams
Managed app backend	../software-baas-platforms/SKILL.md	Use when auth, realtime, storage, and functions are part of the platform choice
Schema migrations	expand-contract pattern	Zero-downtime changes
ORM	Prisma, Drizzle, SQLAlchemy, EF Core	Match stack; review generated SQL
Vector similarity	pgvector (co-located with PostgreSQL)	Start with exact search; add HNSW when measured latency requires ANN, then test recall under filters. See storage selection.
Full-text search as the product	Elasticsearch / OpenSearch	For relevance and facets beyond the database's text search; see software-search.

When to Use This Skill

• Design table/collection schemas and normalization strategies
• Model relationships (1:N, M:N, polymorphic associations)
• Plan zero-downtime migrations (expand-contract)
• Define indexing strategies based on access patterns
• Configure ORMs and avoid common anti-patterns
• Choose between relational, document, key-value, and graph databases

When NOT to Use This Skill

Problem	Go here
Query optimization on existing schemas	data-sql-optimization
Backend service implementation	software-backend
Managed app backend platform (Supabase, Convex, Firebase, Appwrite, PocketBase)	software-baas-platforms
SwiftData/Core Data schemas mirrored to CloudKit	software-ios-native
On-device iOS semantic/vector retrieval	software-ios-ai-engine
Data lake or warehouse architecture	data-lake-platform
Streaming or real-time pipelines	data-streaming
System-level data architecture decisions	software-architecture-design

Workflow

1. Define the entities, access patterns, integrity constraints, and migration constraints first.
2. Route query tuning, backend implementation, or lakehouse design to the adjacent skill when schema design is not the real problem.
3. Choose the data model and normalization stance from the decision tree.
4. Apply the migration, indexing, and ORM guidance needed for the target stack.
5. Validate current engine-specific behavior with the navigation references before final recommendations.

Decision Tree

1) Identify entities and their relationships
2) Choose data model:
   - Relational (PostgreSQL, MySQL) for structured data with complex joins
   - Document (MongoDB) for flexible schemas with embedded relationships
   - Key-value (Redis) for caching, sessions, counters
   - Graph (Neo4j) when variable-depth traversals or path queries dominate
3) Normalize to 3NF by default; denormalize with justification
4) Define primary keys, foreign keys, and constraints
5) Plan indexing strategy based on access patterns
6) Design migration path (can it be applied with zero downtime?)

For the full relational vs. graph vs. vector decision matrix, see references/storage-paradigm-selection.md.

Normalization Decision Table

Situation	Approach	Rationale
Transactional data (orders, users)	Normalize to 3NF	Data integrity, reduce anomalies
Read-heavy dashboards	Denormalize or materialized views	Query performance
Audit logs	Append-only, denormalized	Immutability, query speed
User preferences/settings	JSON column or document	Flexible schema, rarely joined
Hierarchical data (categories, org charts)	Adjacency list or materialized path	Query pattern determines choice
Many-to-many with attributes	Junction table with columns	Clean modeling

When to stop normalizing. Keep independently managed entities separate. If measured queries repeatedly rejoin fields with no independent lifecycle, consider a combined table or a cached field with an explicit invalidation owner. Treat denormalization as an operational decision because copies can drift.

The "one big table + JSON" failure mode. JSON is useful for variable, rarely joined attributes. Repeated filters on an inner field may need an expression or GIN index, but JSON keys still lack ordinary foreign-key integrity; if an attribute becomes central to joins or constraints, promote it to a typed column. Validate the real predicates with EXPLAIN before changing the model.

Migration Safety Checklist

• Migration runs while the application serves traffic
• Lock level and duration checked for each DDL operation; PostgreSQL ALTER TABLE subcommands do not all take the same lock (see migration strategies)
• Every DDL statement runs with SET lock_timeout (a few seconds) and SET statement_timeout, plus a retry loop — lock requests queue in arrival order, so even a fast ACCESS EXCLUSIVE request queues behind, and then blocks, every later reader/writer if it waits behind a long-running transaction. Check pg_stat_activity for long-running transactions on the target table before running DDL.
• New columns are nullable, or have a constant default (metadata-only since Postgres 11 — no table rewrite), or a volatile/expression default (this one does rewrite the table and holds ACCESS EXCLUSIVE for the duration — treat as high-risk)
• Schema change is backward-compatible with current application code
• Rollback plan exists (reverse migration or expand-contract)
• No already-applied migration edited (fix forward); concurrent index builds outside a transaction; no imports of current application models (rules)
• Data backfill runs as a separate step, not inside the migration
• Migration tested against production-sized dataset
• Foreign key constraints added after data is consistent
• Migration SQL linted with a tool suited to the stack; generated SQL and online-DDL limitations reviewed (tool choices)

Rule: rules/sql/safety.md loads this invariant when Claude edits a matching file.

Zero-Downtime Migration Pattern (Expand-Contract)

Compatibility Invariant

Write the compatibility matrix before the change: old code on old schema, old code on expanded schema, new code on expanded schema, and rollback code after partial backfill. The expand phase must preserve old reads and writes; backfills must be restartable and observable; the contract phase begins only after production evidence shows no caller depends on the old shape. Treat destructive DDL as final cleanup, not the migration itself.

Phase 1: EXPAND
  - Add new column/table (nullable, no constraints yet)
  - Deploy app code that writes to both old and new
  - Backfill existing data into new structure

Phase 2: MIGRATE
  - Deploy app code that reads from new structure
  - Verify data consistency between old and new
  - Add constraints and indexes on new structure

Phase 3: CONTRACT
  - Deploy app code that only uses new structure
  - Remove old column/table in a follow-up migration
  - Each phase is a separate deployment — never combine

Indexing Strategy

Choose index types from measured access patterns: B-tree by default, GIN/GiST/BRIN only for the query shapes they serve. In composite indexes put equality columns first, then sort, then range (Equality → Sort → Range); do not order by cardinality. Full access-pattern table and indexing rules (partial, covering, BRIN, fillfactor/HOT): references/indexing-strategy.md.

ORM Patterns

Use eager loading to prevent N+1, keep domain logic out of persistence models, review generated SQL, and drop to raw SQL for bulk work and reports. Pattern and anti-pattern tables: references/orm-framework-guide.md.

Partitioning and Connection Pooling

Partition only on measured signals (maintenance time, scheduled retention drops, consistent filtering on the partition key), not because a table "feels big". Transaction-mode poolers break session-scoped state (search_path, advisory locks, session SET), so run migrations over a direct connection. Decision gates and pooler traps: references/partitioning-and-pooling.md.

Known Traps

• Adding NOT NULL, uniqueness, or foreign-key constraints before a data cleanup and backfill plan exists.
• Treating a nullable "temporary" column or JSON blob as a harmless stopgap — it becomes permanent schema debt.
• Planning partitioning, sharding, or exotic indexes before real access patterns and retention rules are measured — see Partitioning Decision Gates.
• Rolling out schema changes in an order that breaks mixed-version application deployments during blue/green or rolling releases.
• Assuming ORM migration generators understand operational rollout safety without a manual expand-contract review.
• For MongoDB retrieval and graph-model traps, load MongoDB Atlas context or NoSQL modeling. For vector-index consistency, load storage selection.

Anti-Patterns

Avoid	Do Instead
EAV (Entity-Attribute-Value) tables	JSON columns or document store
Storing money as floats	Use DECIMAL / NUMERIC or integer cents
Soft deletes everywhere	Use only when audit trail required; otherwise hard delete
UUID v4 as clustered primary key	UUID v7 (RFC 9562; time-ordered, uuidv7() built in since PostgreSQL 18) or BIGINT GENERATED ALWAYS AS IDENTITY
Storing files in the database	Store in object storage; keep metadata/URL in DB
No foreign keys "for performance"	FK constraints prevent data corruption; index the FK column
One migration per PR with schema + data	Separate schema migration from data backfill
Graph: indexing every property on every label	Index only properties used in WHERE/MATCH lookup positions
Graph: generic relationship types (CONNECTED_TO, RELATED)	Use specific typed edges (FOLLOWS, OWNS, REPORTS_TO)

Primary-key choice. Default to BIGINT GENERATED ALWAYS AS IDENTITY when one database generates IDs. Use UUIDv7 when clients or multiple services must generate IDs before insertion; its time ordering improves index locality compared with UUIDv4, but exposes approximate creation time. InnoDB's clustered-primary-key trade-off differs from PostgreSQL; see MySQL schema notes.

Scenarios

Worked scenarios S1-S5 (multi-tenant shared vs schema-per-tenant, Postgres expand-contract with online backfill and version-specific NOT NULL steps, high-cardinality index design, soft-delete vs archival, JSONB versioning): references/schema-design-scenarios.md.

S6 — MongoDB Atlas as context layer for AI agents

Full pattern (collection topology, vector index, memory schema, operational checklist): see references/mongodb-atlas-ai-context.md.

Navigation

References

• schema-design-patterns.md — Common schema patterns, PostgreSQL 18 schema boundaries, MySQL InnoDB design, type-choice forensics, hierarchy models
• migration-strategies.md — Expand-contract, migration tooling, online DDL, major-version upgrade regressions and support dates
• sharding-decision-framework.md — When to shard (after vertical/read-pool/queuing options), ER-diagram method for choosing a partitioning key, what cross-shard joins and transactions cost
• orm-framework-guide.md — EF Core, SQLAlchemy, Prisma, Drizzle, Mongoose patterns; ORM pattern and anti-pattern tables
• indexing-strategy.md — Access-pattern to index-type table and indexing rules (ESR, partial, covering, BRIN, HOT)
• partitioning-and-pooling.md — Partitioning decision gates and connection-pooler effects on schema and migrations
• schema-design-scenarios.md — Worked scenarios S1-S5
• nosql-modeling.md — Document, key-value, and graph modeling patterns
• storage-paradigm-selection.md — Relational vs. graph vs. vector decision matrix and common polyglot combinations
• transactions-and-storage-engines.md — Isolation anomaly taxonomy (write skew, phantoms), serializability implementations, LSM vs B-tree tradeoffs, LSM compaction strategies and tombstone rules, RUM conjecture, Bw-trees
• mongodb-atlas-ai-context.md — MongoDB Atlas as an AI agent context layer (RAG, memory, hybrid search)

Related Skills

Skill	Relationship
data-sql-optimization	Query tuning on existing schemas
software-backend	Data access patterns in backend services
software-baas-platforms	Managed app-backend platform choice and migration boundaries
software-csharp-backend	EF Core data access and migration patterns
software-architecture-design	System-level data architecture decisions
data-lake-platform	Analytical storage and lakehouse design
software-ios-native	SwiftData/Core Data + CloudKit persistence in native iOS apps
software-ios-ai-engine	On-device iOS semantic search and local vector retrieval

---

Primary sources live in data/sources.json.

Verification Gate

Before delivering output:

• Every referenced table, collection, index, and migration step is internally consistent with the proposed schema.
• If the repo already contains migration tooling, name the exact validation command; otherwise mark migration validation as unverified.
• Expand-contract guidance includes rollout order and rollback notes for breaking schema changes.
• Every referenced schema file, migration path, or ORM config path exists in the repo or is explicitly marked as proposed.

Learnings Loop

When prior decisions or pitfalls are relevant, consult learnings.consolidated.md if present; use learnings.md only for needed history or as the available fallback. Otherwise skip both.

After applying it, if you encountered a pattern worth remembering, a mistake worth preventing, or a domain fact that surprised you, append one dated bullet to learnings.md via agents-skills-feedback-loop/scripts/append_learning.py. Do not modify SKILL.md itself.