# ER diagram from database migrations
> Keep a database schema diagram current by parsing migration files (Prisma, Drizzle, Alembic) and applying incremental Zindex operations on every PR.
## Metadata
- **Diagram family**: `entityRelationship`
- **Source**: Database migrations (Prisma / Drizzle / Alembic)
- **Workflow types**: generate, update, validate, diff, render, ci-cd
- **Audience**: developer, platform, agent
- **MCP tools**: `dsp_get_scene`, `dsp_apply_ops`, `dsp_validate_scene`, `dsp_render_scene`, `dsp_diff_scene`
- **HTTP endpoints**:
  - `GET /v1/scenes/:id`
  - `POST /v1/scenes/:id/applyOps`
  - `POST /v1/scenes/:id/render`
  - `GET /v1/scenes/:id/diff`
---
---

## What this example shows

A classic blog schema rendered as a Crow's Foot ER diagram with PK / FK badges, column-row anchored edges, and auto-inferred cardinality glyphs. The canonical scene is the same shape an agent would produce after parsing migration files for a real codebase - the agent's job is to keep this diagram current as the schema evolves, not to redraw it from scratch on every PR.

## When to use it

Reach for this pattern whenever your team's schema lives in migration files (Prisma, Drizzle, Alembic, Rails, SQLAlchemy, raw SQL). The diagram becomes a derived artifact: agents read migrations, apply typed operations to the persisted scene, and post a visual diff in the PR. New engineers see the schema as a picture; reviewers see exactly what each migration adds, removes, or changes.

## What the agent does

The agent watches migration files in your repo, parses them into table / column / key / foreign-key changes, fetches the current persisted scene, and applies `createNode`, `updateNode`, `createEdge`, `deleteElement` operations to bring the scene up to date. It validates structurally (no orphan FKs, no duplicate IDs), renders the updated diagram, diffs the previous revision against the new one, and posts the visual diff as a PR comment.

The flow is incremental - the persisted scene preserves stable IDs across migrations, so adding one column doesn't reshuffle the whole diagram. Engineers see "what changed in this PR" rather than "here's the entire schema again."

## What the output includes

- A compartmented ER diagram with one entity per table, columns shown as labelled rows, PK / FK badges per column, and Crow's Foot glyphs at every relationship endpoint.
- Auto-inferred cardinality from `pk` / `fk` / `unique` / `nullable` column metadata - agents don't need to hand-set cardinality on most edges.
- FK edges that anchor at the named column row (the dbdiagram.io / DBeaver convention) so the relationship is visible at a glance.
- A revision diff showing exactly which entities, columns, or relationships changed since the last PR.
## Agent workflow
Keep an ER diagram in sync with database migrations on every PR. Parse the migration files changed in the PR, derive entity / column / FK changes, patch the persisted Zindex scene with typed operations, validate, render, and post the visual diff back to the PR.
**Inputs**

- List of migration files changed in the PR (Prisma / Drizzle / Alembic / Rails)
- Existing Zindex scene id (stored as a repo secret on first run)
- Zindex API key with scene-write scope
**Outputs**

- Updated persisted scene with a new immutable revision
- Rendered SVG of the new schema
- Revision diff (JSON) describing what entities / columns / FKs changed
- PR comment with the rendered diagram and summary of schema changes
**Steps**

1. **Create or fetch the persisted scene** - On first run, create a scene with diagramFamily=entityRelationship and an LR hierarchical layoutStrategy. On subsequent runs, fetch the existing scene by id to read the current revision.
   _MCP: `dsp_create_scene` · HTTP: `POST /v1/scenes`_

2. **Parse changed migration files** - Read the migration files added or modified in the PR. Extract the table-level changes: new tables, dropped tables, added columns, renamed columns, dropped columns, new foreign keys, and new indexes. Most ORMs leave a structured trail (Prisma's create/alter blocks, Alembic op.create_table / op.add_column, Rails CreateTable / AddColumn) - parse those rather than free-text SQL.

3. **Diff parsed schema against the persisted scene** - For each entity in the persisted scene, check whether it still exists in the parsed schema. For each column inside each entity, check whether it was added, removed, renamed, or had its type changed. Emit a list of intended ops (createNode / updateNode / deleteElement / createEdge / updateEdge) - do not yet apply them.
   _MCP: `dsp_get_scene` · HTTP: `GET /v1/scenes/${SCENE_ID}`_

4. **Apply the typed-operation batch** - Send the diff as a single applyOps batch with errorPolicy=allOrNothing so the revision either commits cleanly or fails atomically. Always reuse stable ids (`user`, `post`, etc.) so renames produce a true updateNode rather than a delete+create. The element id is the schema's stable handle; do not regenerate it on every run.
   _MCP: `dsp_apply_ops` · HTTP: `POST /v1/scenes/${SCENE_ID}/ops`_

5. **Validate the new revision** - Confirm the scene still validates against ER semantics: every FK column referenced by an edge resolves, every entity has a primary key, no duplicate column names within an entity. The platform emits structured diagnostic codes (EDGE_COLUMN_NOT_FOUND, LABEL_DUPLICATION_DETECTED, …) - fix EDGE_COLUMN_NOT_FOUND and LABEL_DUPLICATION_DETECTED before continuing; CANVAS_AUTO_EXTENDED and EDGE_LABEL_SUPPRESSED_REDUNDANT are informational and can ship.
   _MCP: `dsp_validate_scene` · HTTP: `POST /v1/scenes/validate`_

6. **Render the updated scene** - Render the new revision to SVG. The watermark stamps scene id + revision + date so the PR artifact is traceable back to the persisted scene without metadata sidecar files.
   _MCP: `dsp_render_scene` · HTTP: `POST /v1/scenes/${SCENE_ID}/render`_

7. **Diff against the previous revision** - Ask the platform for the structural diff between the prior revision (recorded on the previous CI run) and the new one. The response lists added / removed / changed elements - perfect for a humanly-readable PR comment, and it does not require the agent to re-derive the diff on its own.
   _MCP: `dsp_diff_scene` · HTTP: `GET /v1/scenes/${SCENE_ID}/diff?from=${PREV_REVISION}&to=${NEW_REVISION}`_

8. **Post the rendered diagram + diff to the PR** - Comment on the PR with the rendered SVG (or a link to the .svg artifact) and the human-readable diff summary. Re-comment idempotently on subsequent commits to keep the latest snapshot pinned.
## Agent prompt

Drop this into a system prompt for an MCP-connected agent.

```
You are an automated database-documentation agent. Your job is to keep an entity-relationship diagram of the application schema in sync with the actual schema definition, on every pull request, without ever drawing the diagram by hand.

The persisted Zindex scene id is `${SCENE_ID}`; treat it as the canonical, mutable, immutable-revisioned source of truth for the ER diagram. The scene already exists - do not create a new one. Each PR potentially introduces a new revision; your job is to compute the smallest valid set of typed operations that move the scene from its current revision to one that matches the schema after this PR.

Workflow on every run:

1. Read the migration files changed in this PR - Prisma `schema.prisma` and `migrations/**`, Drizzle `drizzle/meta/**`, Alembic `alembic/versions/**`, or Rails `db/migrate/**`. Use the migrations rather than free-text SQL: ORMs leave a structured trail you can parse reliably (Prisma's create/alter blocks, Alembic op.create_table / op.add_column, Rails CreateTable / AddColumn). Extract the table-level shape: tables, columns with types, primary keys, foreign keys.

2. Call `dsp_get_scene({ sceneId: "${SCENE_ID}" })` to read the current revision and elements. Diff what you parsed against what is persisted: which entities are new, which were dropped, which had columns added / removed / renamed / retyped, which FK relationships are new.

3. Call `dsp_apply_ops` with one batch of typed operations. Set `errorPolicy: "allOrNothing"` so the revision either commits cleanly or fails atomically. Always reuse stable ids (`user`, `post`, `comment`, …) - element ids are the schema's stable handle; renames must produce `updateNode`, not `deleteElement` + `createNode`, or you will lose the FK edges that anchor on those ids. Pass a meaningful `revisionMessage` like "add Comments table from migration 20260415_add_comments" so the revision history reads like a git log.

4. Call `dsp_validate_scene({ sceneId: "${SCENE_ID}" })`. Resolve any `EDGE_COLUMN_NOT_FOUND` (the FK references a column that does not exist - usually a typo or stale migration); resolve any `LABEL_DUPLICATION_DETECTED` (rename one of the duplicated edge labels). `CANVAS_AUTO_EXTENDED` and `EDGE_LABEL_SUPPRESSED_REDUNDANT` are informational and require no action.

5. Call `dsp_render_scene({ sceneId: "${SCENE_ID}", format: "svg", theme: "clean" })` to render the new revision. The watermark stamps scene-id + revision + date so the PR artifact is traceable.

6. Call `dsp_diff_scene({ sceneId: "${SCENE_ID}", from: PREV_REVISION, to: NEW_REVISION })` to get the structural diff (added / removed / changed elements). Use the diff in the PR comment so reviewers can read the schema change at a glance without re-deriving it from the migration.

7. Post a single PR comment containing the rendered SVG (or a link to the .svg artifact) and the diff summary. Re-comment idempotently on subsequent commits - overwrite the last comment rather than appending - so reviewers always see the latest snapshot pinned to the top of the conversation.

Hard rules: never hand-edit the rendered SVG; always edit the scene with `dsp_apply_ops` and re-render. Never regenerate the scene from scratch; always patch with stable ids. Never skip validation; an unvalidated scene risks shipping a diagram that does not match the schema. If validation surfaces an unrecoverable error, comment on the PR with the structured diagnostic and ask a human to resolve.
```
## Validation

Captured `POST /v1/scenes/validate` response: **valid** (4 diagnostics)

- `warning` `FAMILY_ER_MISSING_CARDINALITY` (/elements/e-writes): ER edge 'e-writes' is missing cardinality on one or both endpoints.
- `warning` `FAMILY_ER_MISSING_CARDINALITY` (/elements/e-comments): ER edge 'e-comments' is missing cardinality on one or both endpoints.
- `warning` `FAMILY_ER_MISSING_CARDINALITY` (/elements/e-has-comments): ER edge 'e-has-comments' is missing cardinality on one or both endpoints.
- `warning` `FAMILY_ER_MISSING_CARDINALITY` (/elements/e-tagged): ER edge 'e-tagged' is missing cardinality on one or both endpoints.
## Resources
- **Canonical scene**: [/examples/er-diagram-from-migrations.scene.json](/examples/er-diagram-from-migrations.scene.json)
- **Operations envelope**: [/examples/er-diagram-from-migrations.ops.json](/examples/er-diagram-from-migrations.ops.json)
- **Workflow recipe**: [/examples/er-diagram-from-migrations.workflow.json](/examples/er-diagram-from-migrations.workflow.json)
- **Revision diff**: [/examples/er-diagram-from-migrations.diff.json](/examples/er-diagram-from-migrations.diff.json)
- **GitHub Actions workflow**: [/examples/er-diagram-from-migrations.github-actions.yml](/examples/er-diagram-from-migrations.github-actions.yml)
- **Rendered SVG**: [/examples/er-diagram-from-migrations.svg](/examples/er-diagram-from-migrations.svg)
- **Human page**: [/examples/er-diagram-from-migrations](/examples/er-diagram-from-migrations)
- **Manifest**: [/examples/index.json](/examples/index.json)
## Related examples

- [/examples/compliance-pii-flow](/examples/compliance-pii-flow)
- [/examples/api-dependency-map](/examples/api-dependency-map)
- [/examples/pr-architecture-diff](/examples/pr-architecture-diff)