database-schema-designer

---
name: database-schema-designer
description: 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.
license: MIT
---

## 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