Iron Law
NO SCHEMA MIGRATION WITHOUT A DOWN (ROLLBACK) SCRIPT AND AN INDEX ON EVERY FOREIGN KEY — unindexed FKs cause full table scans; irreversible migrations cause production incidents
Database Schema Designer
Design production-ready database schemas with best practices built-in.
Triggers
| Trigger |
Example |
design schema |
"design a schema for user authentication" |
database design |
"database design for multi-tenant SaaS" |
create tables |
"create tables for a blog system" |
schema for |
"schema for inventory management" |
model data |
"model data for real-time analytics" |
I need a database |
"I need a database for tracking orders" |
design NoSQL |
"design NoSQL schema for product catalog" |
Quick Reference
| Task |
Approach |
Key Consideration |
| New schema |
Normalize to 3NF first |
Domain modeling over UI |
| SQL vs NoSQL |
Access patterns decide |
Read/write ratio matters |
| Primary keys |
INT or UUID |
UUID for distributed systems |
| Foreign keys |
Always constrain |
ON DELETE strategy critical |
| Indexes |
FKs + WHERE columns |
Column order matters |
| Migrations |
Always reversible |
Backward compatible first |
Process
Phase 1: Analyze
- Identify entities and relationships
- Determine access patterns (read-heavy vs write-heavy)
- Choose SQL or NoSQL based on requirements
Phase 2: Design
- Normalize to 3NF (SQL) or determine embed/reference strategy (NoSQL)
- Define primary keys and foreign keys
- Choose appropriate data types -- read
reference/data-types-reference.md for type guides
- Add constraints -- read
reference/constraints-and-relationships.md for patterns
Read reference/normalization-guide.md for 1NF/2NF/3NF rules and examples.
Phase 3: Optimize
- Plan indexing strategy -- read
reference/indexing-strategy.md for when to index and composite index rules
- Consider denormalization for read-heavy queries
- Add timestamps (created_at, updated_at)
Phase 4: Migrate
- Generate migration scripts (up + down)
- Ensure backward compatibility
- Plan zero-downtime deployment
Read reference/migration-patterns.md for zero-downtime patterns and rollback strategies.
Read reference/expand-contract-migrations.md for zero-downtime Expand-Contract strategy, CONCURRENTLY index operations, and batched SKIP LOCKED backfills.
NoSQL Design
For MongoDB, Firestore, and other document databases, read reference/nosql-design-patterns.md for embedding vs referencing patterns and Firestore-specific design rules.
Commands
| Command |
When to Use |
design schema for {domain} |
Start fresh -- full schema generation |
normalize {table} |
Fix existing table -- apply normalization rules |
add indexes for {table} |
Performance issues -- generate index strategy |
migration for {change} |
Schema evolution -- create reversible migration |
review schema |
Code review -- audit existing schema |
Anti-Patterns
PostgreSQL Type Forbidden List
NEVER use these types — they have silent, hard-to-debug failure modes:
| Forbidden type |
Problem |
Use instead |
timestamp (no tz) |
Stores local time, breaks across timezones |
timestamptz |
timetz |
Doesn't handle DST — reports wrong time after clock changes |
timestamptz |
char(n) / varchar(n) |
char pads with spaces, silently breaks equality checks |
text |
money |
Locale-dependent formatting, rounding errors across servers |
numeric |
serial |
Sequence ownership breaks on pg_dump/restore |
generated always as identity |
float for money |
Binary floating-point rounding errors |
numeric(10,2) |
Schema Anti-Patterns
| Avoid |
Why |
Instead |
| VARCHAR(255) everywhere |
Wastes storage, hides intent |
Size appropriately per field |
| Missing FK constraints |
Orphaned data |
Always define foreign keys |
| No indexes on FKs |
Slow JOINs |
Index every foreign key |
| Storing dates as strings |
Cannot compare/sort |
DATE, TIMESTAMPTZ types |
| Non-reversible migrations |
Cannot rollback |
Always write DOWN migration |
Verification
After designing a schema, run through reference/schema-design-checklist.md to verify completeness.
Documentation Sources
Before generating schemas or queries, consult these sources:
| Source |
URL / Tool |
Purpose |
| PostgreSQL |
PostgreSQL MCP server |
Schema-aware SQL, introspection, admin-safe workflows |
| Firebase Firestore |
Firebase MCP server |
Document design, rules, indexes for NoSQL schemas |
Reference Files
| File |
Contents |
reference/schema-design-checklist.md |
Pre-design, table design, and deployment checklist |
reference/normalization-guide.md |
1NF/2NF/3NF explanations, examples, denormalization guide |
reference/data-types-reference.md |
String, numeric, date/time, JSON type guides |
reference/indexing-strategy.md |
When to index, composite indexes, B-tree vs hash, EXPLAIN |
reference/constraints-and-relationships.md |
PKs, FKs, CHECK, UNIQUE, relationship patterns |
reference/nosql-design-patterns.md |
MongoDB/Firestore embedding vs referencing |
reference/migration-patterns.md |
Zero-downtime migrations, rollback strategies |
reference/expand-contract-migrations.md |
Expand-Contract pattern, CONCURRENTLY index ops, SKIP LOCKED backfills |
assets/templates/migration-template.sql |
SQL migration file template |
reference/postgresql-review-checklist.md |
PostgreSQL review checklist (used by postgresql-database-reviewer agent) |
Error Handling
Migration conflicts: When migrations fail, check for column type mismatches or missing dependent migrations. Never modify an applied migration — create a new corrective one.
Index creation failures: Verify the column exists and data types support the index type. For large tables, use CREATE INDEX CONCURRENTLY.
Hard Prohibitions
- No
DROP TABLE or DROP COLUMN without explicit human approval
- Use plural table names (
users, orders, payments)
- Database credentials rotated every 90 days (see
security-review-checklist.md §8)
Post-Code Review
After writing SQL/migration code, dispatch this reviewer agent:
postgresql-database-reviewer — query optimization, schema correctness, index coverage, security
1---2name: database-schema-designer3description: This skill should be used when designing database schemas for SQL or NoSQL databases. It provides normalization guidelines, indexing strategies, migration patterns, and performance optimization.4---56## Iron Law78**NO SCHEMA MIGRATION WITHOUT A DOWN (ROLLBACK) SCRIPT AND AN INDEX ON EVERY FOREIGN KEY — unindexed FKs cause full table scans; irreversible migrations cause production incidents**910# Database Schema Designer1112Design production-ready database schemas with best practices built-in.1314## Triggers1516| Trigger | Example |17|---------|---------|18| `design schema` | "design a schema for user authentication" |19| `database design` | "database design for multi-tenant SaaS" |20| `create tables` | "create tables for a blog system" |21| `schema for` | "schema for inventory management" |22| `model data` | "model data for real-time analytics" |23| `I need a database` | "I need a database for tracking orders" |24| `design NoSQL` | "design NoSQL schema for product catalog" |2526## Quick Reference2728| Task | Approach | Key Consideration |29|------|----------|-------------------|30| New schema | Normalize to 3NF first | Domain modeling over UI |31| SQL vs NoSQL | Access patterns decide | Read/write ratio matters |32| Primary keys | INT or UUID | UUID for distributed systems |33| Foreign keys | Always constrain | ON DELETE strategy critical |34| Indexes | FKs + WHERE columns | Column order matters |35| Migrations | Always reversible | Backward compatible first |3637## Process3839### Phase 1: Analyze4041- Identify entities and relationships42- Determine access patterns (read-heavy vs write-heavy)43- Choose SQL or NoSQL based on requirements4445### Phase 2: Design4647- Normalize to 3NF (SQL) or determine embed/reference strategy (NoSQL)48- Define primary keys and foreign keys49- Choose appropriate data types -- read `reference/data-types-reference.md` for type guides50- Add constraints -- read `reference/constraints-and-relationships.md` for patterns5152Read `reference/normalization-guide.md` for 1NF/2NF/3NF rules and examples.5354### Phase 3: Optimize5556- Plan indexing strategy -- read `reference/indexing-strategy.md` for when to index and composite index rules57- Consider denormalization for read-heavy queries58- Add timestamps (created_at, updated_at)5960### Phase 4: Migrate6162- Generate migration scripts (up + down)63- Ensure backward compatibility64- Plan zero-downtime deployment6566Read `reference/migration-patterns.md` for zero-downtime patterns and rollback strategies.6768Read `reference/expand-contract-migrations.md` for zero-downtime Expand-Contract strategy, CONCURRENTLY index operations, and batched SKIP LOCKED backfills.6970### NoSQL Design7172For MongoDB, Firestore, and other document databases, read `reference/nosql-design-patterns.md` for embedding vs referencing patterns and Firestore-specific design rules.7374## Commands7576| Command | When to Use |77|---------|-------------|78| `design schema for {domain}` | Start fresh -- full schema generation |79| `normalize {table}` | Fix existing table -- apply normalization rules |80| `add indexes for {table}` | Performance issues -- generate index strategy |81| `migration for {change}` | Schema evolution -- create reversible migration |82| `review schema` | Code review -- audit existing schema |8384## Anti-Patterns8586### PostgreSQL Type Forbidden List8788NEVER use these types — they have silent, hard-to-debug failure modes:8990| Forbidden type | Problem | Use instead |91|----------------|---------|-------------|92| `timestamp` (no tz) | Stores local time, breaks across timezones | `timestamptz` |93| `timetz` | Doesn't handle DST — reports wrong time after clock changes | `timestamptz` |94| `char(n)` / `varchar(n)` | `char` pads with spaces, silently breaks equality checks | `text` |95| `money` | Locale-dependent formatting, rounding errors across servers | `numeric` |96| `serial` | Sequence ownership breaks on pg_dump/restore | `generated always as identity` |97| `float` for money | Binary floating-point rounding errors | `numeric(10,2)` |9899### Schema Anti-Patterns100101| Avoid | Why | Instead |102|-------|-----|---------|103| VARCHAR(255) everywhere | Wastes storage, hides intent | Size appropriately per field |104| Missing FK constraints | Orphaned data | Always define foreign keys |105| No indexes on FKs | Slow JOINs | Index every foreign key |106| Storing dates as strings | Cannot compare/sort | DATE, TIMESTAMPTZ types |107| Non-reversible migrations | Cannot rollback | Always write DOWN migration |108109## Verification110111After designing a schema, run through `reference/schema-design-checklist.md` to verify completeness.112113## Documentation Sources114115Before generating schemas or queries, consult these sources:116117| Source | URL / Tool | Purpose |118|--------|-----------|---------|119| PostgreSQL | `PostgreSQL MCP server` | Schema-aware SQL, introspection, admin-safe workflows |120| Firebase Firestore | `Firebase MCP server` | Document design, rules, indexes for NoSQL schemas |121122## Reference Files123124| File | Contents |125|------|----------|126| `reference/schema-design-checklist.md` | Pre-design, table design, and deployment checklist |127| `reference/normalization-guide.md` | 1NF/2NF/3NF explanations, examples, denormalization guide |128| `reference/data-types-reference.md` | String, numeric, date/time, JSON type guides |129| `reference/indexing-strategy.md` | When to index, composite indexes, B-tree vs hash, EXPLAIN |130| `reference/constraints-and-relationships.md` | PKs, FKs, CHECK, UNIQUE, relationship patterns |131| `reference/nosql-design-patterns.md` | MongoDB/Firestore embedding vs referencing |132| `reference/migration-patterns.md` | Zero-downtime migrations, rollback strategies |133| `reference/expand-contract-migrations.md` | Expand-Contract pattern, CONCURRENTLY index ops, SKIP LOCKED backfills |134| `assets/templates/migration-template.sql` | SQL migration file template |135| `reference/postgresql-review-checklist.md` | PostgreSQL review checklist (used by `postgresql-database-reviewer` agent) |136137## Error Handling138139**Migration conflicts**: When migrations fail, check for column type mismatches or missing dependent migrations. Never modify an applied migration — create a new corrective one.140141**Index creation failures**: Verify the column exists and data types support the index type. For large tables, use `CREATE INDEX CONCURRENTLY`.142143## Hard Prohibitions144145- No `DROP TABLE` or `DROP COLUMN` without explicit human approval146- Use plural table names (`users`, `orders`, `payments`)147- Database credentials rotated every 90 days (see `security-review-checklist.md` §8)148149## Post-Code Review150151After writing SQL/migration code, dispatch this reviewer agent:152- `postgresql-database-reviewer` — query optimization, schema correctness, index coverage, security
kumaran-is