---
name: database-schema
description: >-
  Designs normalized, performant, evolvable database schemas with proper constraints,
  indexes, and migration paths. Use when creating schemas, adding tables or columns,
  or user asks to "design a schema for X".
  NOT for writing queries, query optimization, or running migrations.
allowed-tools:
  - Read
  - Glob
  - Grep
  - Bash
---

## Identity & Scope

**Executor:** either
**Model tier:** sonnet
**Scope:** Schema files (SQL, ORM models), migration files, ERD documents
**Off-limits:** Executing DDL against production, dropping tables, modifying live data
**Hard rules:** No credentials in output. No deleting files. No spending money. Report via structured output.

## Essential Principles

1. **Normalize first, denormalize deliberately** — Start at 3NF, then denormalize for measured performance needs. Guards against: premature optimization causing data inconsistency.
2. **Every table gets timestamps** — id, created_at, updated_at on every table from day one. Guards against: missing audit trail impossible to retrofit.
3. **Constrain at the database level** — NOT NULL, UNIQUE, CHECK, FK constraints — never rely on app code alone. Guards against: data corruption from any code path bypassing validation.
4. **Plan for migration** — Every change achievable without downtime on live systems. Guards against: designs requiring table locks to evolve.

## When to Use
- Designing a database schema for a new project or feature
- Adding tables, columns, or relationships to existing schema
- User asks to "design a schema for X" or "model this data"
- Reviewing schema for normalization, constraints, or indexing
- Planning migration strategy for a breaking change

## When NOT to Use
- Writing SQL queries — use query-writing skill
- Optimizing slow queries — use profiling skill
- Running migrations — use devops skill
- API response shapes — use api-design skill

## Rationalizations to Reject

| Shortcut | Why it's wrong |
|----------|----------------|
| "JSON column for flexibility" | Fine for metadata, terrible for queryable data — no constraints |
| "Add indexes later" | Missing indexes cause production slowdowns that are urgent to fix |
| "FLOAT for currency" | Floating-point rounding errors in financial calculations — use INTEGER cents |
| "No FKs, app enforces it" | Apps have bugs; FK constraints are last defense against orphans |

## Workflow

### Phase 1: Entity Modeling
**Entry:** Feature requirements or domain description available
**Actions:**
1. Identify entities (nouns) in the domain
2. Map relationships: one-to-one, one-to-many, many-to-many
3. Identify required vs optional attributes per entity
**Exit:** Entity-relationship list with attributes and cardinality

### Phase 2: Schema Design
**Entry:** Entity model complete
**Actions:**
1. Create tables with snake_case plural names
2. Add id (PK), created_at, updated_at to every table
3. Define column types with appropriate precision
4. Add NOT NULL, UNIQUE, CHECK constraints
5. Add foreign keys for all relationships
6. Create junction tables for many-to-many
**Exit:** Complete DDL with all tables, columns, constraints

### Phase 3: Indexing
**Entry:** Schema designed
**Actions:**
1. Index every foreign key column
2. Index columns in WHERE clauses and JOINs
3. Add composite indexes for common multi-column queries
4. Consider partial indexes for status/active filters
**Exit:** All indexes defined with rationale

### Phase 4: Migration Planning
**Entry:** Schema and indexes complete
**Actions:**
1. Determine if new schema or alteration to existing
2. For alterations: plan non-locking steps (add column, backfill, add constraint)
3. Flag any steps requiring downtime
4. Plan rollback strategy per step
**Exit:** Migration plan with rollback notes

### Phase 5: Verify
**Entry:** All phases complete
**Actions:**
1. Confirm every table has id, created_at, updated_at
2. Confirm all FKs indexed, no FLOAT for money, all required cols NOT NULL
3. Confirm migration plan avoids downtime (or flags it)
4. Confirm output matches required JSON format
**Exit:** All checks pass — report results

## Output Format

```json
{
  "skill": "database-schema",
  "status": "completed",
  "worker": "worker-id",
  "summary": "Designed 4-table schema (users, teams, team_members, invitations) with 7 indexes, zero-downtime migration",
  "findings": [
    {"item": "users — 8 cols, 2 unique, 1 check", "severity": "info", "detail": "id, email(UNIQUE), display_name, password_hash, status CHECK('active','suspended','deleted'), created_at, updated_at, deleted_at"},
    {"item": "Migration: backfill for status default", "severity": "medium", "detail": "ALTER ADD status DEFAULT 'active', backfill NULLs. No downtime."}
  ],
  "errors": [],
  "elapsed_seconds": 31.6
}
```

**Bad example:** `{"summary": "Created tables", "findings": [{"item": "schema designed"}]}` — no table names, no columns, no constraints, unimplementable.

## Escalation Triggers

STOP and report to boss when:
- Change requires dropping a production column or table
- Migration cannot avoid downtime unexpectedly
- Domain has compliance requirements (PII, HIPAA, PCI)
- Existing data doesn't conform to proposed constraints
- Data corruption or orphaned records discovered

## Success Criteria

- [ ] Every table has id, created_at, updated_at
- [ ] All foreign keys indexed
- [ ] All required columns NOT NULL
- [ ] No FLOAT for financial data
- [ ] Constraints enforce business rules at DB level
- [ ] Migration plan with rollback strategy exists
- [ ] Output matches JSON format above
- [ ] No unresolved escalation triggers
