ERD Builder

---
name: erd-builder
description: Use when generating a Mermaid ERD (entity-relationship diagram) for a database schema. Triggers on "ERD", "entity-relationship diagram", "schema diagram", "database diagram", or pasted SQL DDL.
---

# ERD Builder

Generate a Mermaid `erDiagram` that documents the logical schema. The goal: a new engineer can read it and know how the data fits together without reading migrations.

## Required inputs

1. **The entities** — table names, ideally singular nouns (`User`, not `users`)
2. **Key attributes per entity** — primary key, foreign keys, and the 3-5 most important fields. Don't list every column.
3. **Relationships** — which entity references which, and the cardinality (one-to-one, one-to-many, many-to-many)
4. **Scope** — if the full schema has 30+ tables, ask which subset matters for THIS conversation

If the user dumps a 50-table schema and asks for "the ERD," push back: "Which area should the diagram focus on (auth, billing, content)? A single ERD with 50 entities is unreadable."

## Mermaid syntax to use

`erDiagram` uses crow's foot notation. Entities are blocks; relationships connect them with cardinality marks at each end:

- `||--||` one-to-one
- `||--o{` one-to-many
- `}o--o{` many-to-many
- `||--o|` one-to-zero-or-one (optional)

Inside an entity, list `type name [PK|FK] "comment"`.

\`\`\`mermaid
erDiagram
    USER ||--o{ MEMBERSHIP : has
    ORGANIZATION ||--o{ MEMBERSHIP : has
    ORGANIZATION ||--o{ PROJECT : owns
    PROJECT ||--o{ TASK : contains
    USER ||--o{ TASK : "assigned to"

    USER {
        uuid id PK
        string email UK
        string name
        timestamp created_at
    }
    ORGANIZATION {
        uuid id PK
        string slug UK
        string name
    }
    MEMBERSHIP {
        uuid id PK
        uuid user_id FK
        uuid org_id FK
        string role
    }
    PROJECT {
        uuid id PK
        uuid org_id FK
        string name
    }
    TASK {
        uuid id PK
        uuid project_id FK
        uuid assignee_id FK
        string title
        string status
    }
\`\`\`

Use `UK` for unique keys, `PK` for primary, `FK` for foreign. Quoted comments work for clarifying ambiguous relationships ("assigned to" vs "created by").

## Layout principles

- **Singular entity names, UPPER CASE.** Mermaid convention; reads cleanly.
- **List PK first, then FKs, then 2-3 most important attributes.** The diagram is not a column dictionary.
- **Verb on the relationship.** "USER has MEMBERSHIP" is good. "USER -- MEMBERSHIP" is meaningless.
- **Cardinality is mandatory.** A relationship without crow's-foot notation is half a relationship.
- **Many-to-many always uses a join entity.** Don't draw direct `}o--o{` if a real join table exists — show the join table.

## Anti-patterns

- **Listing every column** — diagram becomes a wall of text. Show keys + 3-5 important attributes per entity. The full schema lives in DDL.
- **No cardinality** — readers can't tell if it's 1-1 or 1-many. Always specify both ends.
- **Implicit join tables** — many-to-many through an unshown table hides the truth. Draw the join.
- **Mixing logical and physical** — don't show indexes, partitions, or foreign-key constraint names. That's the DDL's job.
- **All entities, no relationships** — if your ERD has 10 boxes and 0 lines, you've drawn a list, not a diagram.

## Rendering hints

- GitHub renders Mermaid ERDs in markdown — keep the source in the repo next to migrations.
- Mermaid Live Editor for SVG export when you need to embed in a design doc PDF.
- For schema with 20+ entities, render in chunks (auth subgraph, billing subgraph) and link between docs.

## Output

The ```mermaid block plus a short summary: entity count, relationship count, and any cardinality you had to guess (flag for the user to verify against the actual DDL).