Best practices for database schema design, migration strategies, query optimization, ORM patterns, and data validation across relational, document, and graph databases.
Capabilities
Design relational schemas with proper normalization, naming conventions, and constraint strategies
Model document database schemas (MongoDB, DynamoDB, Firestore) with embed-vs-reference decisions
Design graph database models (Neo4j, Neptune) for relationship-heavy domains
Plan and execute safe database migrations using Prisma, Drizzle, Alembic, Knex, or Atlas
Optimize queries through indexing strategies, EXPLAIN analysis, and pagination patterns
Implement event sourcing and CQRS patterns for audit-heavy or event-driven systems
Set up boundary validation with Zod (TypeScript), Pydantic (Python), or JSON Schema
Advise on database paradigm selection (relational vs document vs graph) for a given domain
Schema is the contract -- The database schema is the most durable API in any system. Design it deliberately; everything else adapts to it.
Validate at every boundary -- Never trust input from any source. Validate at the API boundary (Zod/Pydantic), enforce with application logic, and backstop with database constraints.
Normalize by default, denormalize by measurement -- Start with 3NF for transactional tables. Only denormalize when profiling proves joins are a bottleneck, and use materialized views or read replicas rather than corrupting the source schema.
Migrations are deployments -- Treat schema changes with the same rigor as application deployments: version them, review them, test them on production-sized data, and ensure rollback paths.
Index with intent -- Every index has a write-cost. Create indexes to serve specific query patterns, verify with EXPLAIN, and remove unused ones.
Choose the right paradigm -- Relational for structured transactional data, document for flexible semi-structured data, graph for relationship-heavy traversal queries. Hybrid is often the answer.
Design for agents and automation -- Use consistent naming, explicit types, descriptive constraints, and machine-readable schema definitions. Agents rely on predictable, well-documented schemas.
Apply normalization -- Target 3NF for transactional schemas; document rationale for any intentional denormalization.
Add constraints -- NOT NULL, UNIQUE, CHECK, foreign keys. These are your safety net.
Plan indexes -- Index foreign keys, unique fields, and columns in frequent WHERE/ORDER BY clauses.
Set up validation -- Define Zod/Pydantic schemas that mirror the database schema at the API boundary.
Write migration -- Use the project's ORM migration tool to generate and review the migration SQL.
Review and test -- Review generated SQL, test on production-sized data, verify rollback.
Schema Change (Existing System)
Assess impact -- Which tables, queries, and application code are affected?
Choose strategy -- Additive change (safe), or expand-and-contract for breaking changes.
Write migration -- Generate with ORM tool, customize if needed (data migration, backfill).
Test migration -- Run on a copy of production data; measure duration and lock behavior.
Deploy -- Apply migration before deploying new application code (expand phase).
Verify -- Confirm data integrity, query performance, and application behavior.
Clean up -- Remove old columns/tables after verification period (contract phase).
Quick Reference
Database Paradigm Selection
Signal
Recommended Paradigm
Structured data, ACID transactions, complex joins
Relational (PostgreSQL, MySQL)
Variable schema, nested objects, key-value access
Document (MongoDB, DynamoDB, Firestore)
Deep relationship traversal, social graphs, recommendations
Graph (Neo4j, Neptune)
Time-series, high-volume append
Time-series (TimescaleDB, InfluxDB) or document buckets
Full-text search as primary access
Search engine (Elasticsearch, Typesense)
Mixed requirements
Hybrid: relational core + specialized stores for specific patterns
Normalization Quick Guide
Form
Rule
Target?
1NF
Atomic values, no repeating groups
Always
2NF
No partial key dependencies
Always
3NF
No transitive dependencies
Default target for OLTP
BCNF
Every determinant is a candidate key
When complex keys exist
ORM Migration Commands
Tool
Generate
Apply (Dev)
Apply (Prod)
Rollback
Prisma
prisma migrate dev
prisma migrate dev
prisma migrate deploy
Manual
Drizzle
drizzle-kit generate
drizzle-kit push
drizzle-kit migrate
Manual
Alembic
alembic revision --autogenerate
alembic upgrade head
alembic upgrade head
alembic downgrade -1
Knex
knex migrate:make
knex migrate:latest
knex migrate:latest
knex migrate:rollback
Atlas
atlas migrate diff
atlas schema apply
atlas migrate apply
atlas schema apply --to
Index Decision Quick Check
Column appears in WHERE, JOIN, or ORDER BY?
No → Do not index
Yes → Table > 1000 rows?
No → Skip (seq scan is fine)
Yes → High cardinality?
Yes → B-tree index
No → Partial index or skip
Text search? → GIN + tsvector
JSONB? → GIN + jsonb_path_ops
Composite? → Composite B-tree (most selective column first)
Validation Tool Selection
Language
Tool
Key Feature
TypeScript
Zod
Runtime validation with z.infer<> for type inference
Python
Pydantic v2
Rust-powered validation, ORM integration via from_attributes
Any / Cross-service
JSON Schema
Language-agnostic contract, OpenAPI compatible
Database
CHECK + UNIQUE + FK
Last-resort integrity enforcement
Common Schema Patterns
Pattern
When to Use
Surrogate keys (identity/UUID)
Almost always; UUIDs for distributed, identity for single-DB
Soft delete (deleted_at)
When records must be recoverable; add partial index on active rows
Audit trail / history table
Compliance, debugging; consider event sourcing for complex cases
Polymorphic association
Comments, attachments that relate to multiple entity types
Many-to-many with metadata
Join tables needing extra fields (granted_at, expires_at)
Tree / hierarchy (ltree)
Categories, org charts; prefer ltree on PostgreSQL
Expand-and-contract
Zero-downtime breaking schema changes in production
Agentic Workflow Considerations
When an AI agent is designing or modifying schemas:
Explain reasoning -- Always state why a particular normalization level, index, or paradigm was chosen, not just what was chosen.
Generate validation schemas alongside migrations -- When creating a new table, also generate the corresponding Zod/Pydantic schema. Keep them in sync.
Prefer additive changes -- When modifying existing schemas, prefer adding new columns/tables over altering or dropping existing ones. This is safer for zero-downtime deployments.
Include rollback plans -- Every migration should have a documented rollback path, even if it is "drop the new column."
Use descriptive naming -- Agents and humans both benefit from user_account_id over uaid. Self-documenting names reduce errors.
Annotate schemas for discoverability -- Use Zod .describe(), Pydantic Field(description=...), and SQL COMMENT ON to make schemas machine-readable.
Validate AI-generated data -- When an agent produces data to insert, always run it through the validation schema first. Use strict mode to reject unexpected fields.
Check existing patterns -- Before creating a new table or migration, check the existing codebase for naming conventions, ORM choice, and migration patterns. Follow established conventions.
Checklist
New Schema
Entities and relationships identified from requirements
Database paradigm chosen with documented rationale
Naming conventions consistent with project standards
Normalization level appropriate (3NF default for OLTP)
All foreign keys have indexes
NOT NULL applied to all required columns
CHECK constraints for value ranges and enums
UNIQUE constraints where business rules require them
Migration strategy chosen (additive or expand-and-contract)
Migration backward-compatible with current application code
No table locks on large tables during peak hours
Rollback migration written and tested
Data backfill tested on production-sized dataset
Validation schemas updated to match new schema
Indexes added CONCURRENTLY where supported
Query Performance
EXPLAIN ANALYZE run on slow queries
Indexes serve the identified query patterns
No N+1 query patterns in application code
Pagination uses cursor/keyset (not OFFSET for large datasets)
No functions wrapping indexed columns in WHERE clauses
Unused indexes identified and removed
Statistics up to date (ANALYZE run recently)
When to Escalate
Cross-database distributed transactions -- If the design requires ACID transactions spanning multiple database systems (e.g., PostgreSQL + MongoDB), escalate to a senior architect. This requires saga patterns or two-phase commit, which are beyond routine schema work.
Sharding or horizontal partitioning -- When a single database instance cannot handle the data volume or throughput, sharding decisions have deep architectural implications. Escalate to infrastructure/platform team.
Data sovereignty and compliance -- If schema design must comply with GDPR, HIPAA, SOC2, or data residency requirements, involve legal/compliance stakeholders before finalizing the schema.
Event sourcing adoption -- If the team has no prior event sourcing experience, adopting it requires significant training and infrastructure changes. Propose it but escalate the adoption decision.
Production data migration over 1 hour -- Large data migrations that require extended maintenance windows or complex rollback procedures should involve a DBA or platform engineer.
Performance issues after optimization -- If query performance remains poor after applying indexing and optimization strategies from this skill, escalate to a DBA for deep analysis (lock contention, connection pooling, hardware constraints).
