Db Schema

Steps

1. Gather requirements — extract entities, attributes, and relationships from the user's description; clarify cardinality (one-to-one, one-to-many, many-to-many) and any unclear business rules before proceeding

2. Identify entities and attributes — list every entity with its attributes; mark each attribute as: required / optional, unique, PII (Personally Identifiable Information), or sensitive (password, token, card number)

3. Define relationships — map every relationship between entities with direction and cardinality; identify junction tables for many-to-many relationships

4. Apply normalization — check and enforce up to 3NF:

   • 1NF: eliminate repeating groups; each cell holds one atomic value
   • 2NF: remove partial dependencies — every non-key attribute depends on the full primary key
   • 3NF: remove transitive dependencies — non-key attributes depend only on the primary key
   • Note justified denormalization with explicit reason (e.g. query performance, audit log)

5. Produce ER diagram — generate a Mermaid erDiagram covering all entities, attributes (with type), and relationships with cardinality labels

6. Generate DDL SQL — write CREATE TABLE statements for the target engine with: primary keys, foreign keys, unique constraints, check constraints, NOT NULL, default values, and standard audit columns (created_at, updated_at)

7. Write migration files — produce one migration file per logical change with both up (apply) and down (rollback) sections; name files with a timestamp prefix: YYYYMMDDHHMMSS_<description>.sql

8. Recommend indexes — propose indexes for: all foreign key columns, frequently filtered columns, unique constraints, and composite indexes for common query patterns; explain each index's purpose

9. Flag design decisions — document every non-obvious decision: chosen normalization trade-offs, nullable fields, enum vs lookup table, soft delete strategy, UUID vs auto-increment primary key

10. Confirm before writing files — show the full output and wait for user approval; after approval, write DDL and migration files to the paths specified by the user

Normalization Reference

Standard Columns

Include these in every table unless there is an explicit reason not to:

id          BIGSERIAL PRIMARY KEY,           -- or UUID if distributed
created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()

For soft delete, add:

deleted_at  TIMESTAMPTZ

Output Format

File path: DDL at <user-specified path>/schema.sql, migrations at <path>/migrations/YYYYMMDDHHMMSS_<name>.sql

## Database Schema: <name>

### Entities & Attributes
| Entity | Attribute | Type | Constraints | Notes |
|--------|-----------|------|-------------|-------|

### Relationships
| From | Relationship | To | Via |
|------|--------------|----|-----|

### ER Diagram
```mermaid
erDiagram
  ...

DDL SQL

CREATE TABLE ...;

Migration File: YYYYMMDDHHMMSS_<name>.sql

-- up / down

Index Recommendations

| Table | Columns | Reason |

Design Decisions

• <decision>: <rationale>

Full annotated e-commerce example: [ref/schema-examples.md](ref/schema-examples.md)

## Rules

### Must
- Normalize to at least 3NF; document any intentional deviation with a reason
- Mark every PII and sensitive attribute in the Entities table
- Store monetary values as integers (cents), never as FLOAT or DECIMAL without justification
- Include `created_at` and `updated_at` in every table
- Write both `up` and `down` in every migration file
- Name migration files with timestamp prefix `YYYYMMDDHHMMSS_`
- Show the full output and wait for user approval before writing any file
- Use `ON DELETE RESTRICT` as the default for FK constraints; justify any CASCADE or SET NULL
- Validate every user-supplied identifier (table name, column name, constraint name) against `^[a-z0-9_]+$` before incorporating it into any DDL; reject and ask the user to rename any identifier that fails this check

### Never
- Store passwords, tokens, or secrets in plaintext — flag and comment in DDL
- Use FLOAT for monetary or measurement values — precision loss is a bug
- Create a migration with only `up` and no `down`
- Skip the ER diagram — it is the primary communication artifact
- Use reserved SQL keywords as column or table names without quoting
- Interpolate user-supplied names directly into DDL — all user-provided table names, column names, and identifiers must contain only `[a-z0-9_]`; reject any name with spaces, hyphens, or special characters before generating SQL

## Examples

### Good Example

See Output Format section above — the e-commerce schema demonstrates all required sections with complete DDL, migration, indexes, and design decision documentation.

### Bad Example

Here's a schema for your app: