ER diagram from database migrations

# Zindex - keep an ER diagram in sync with database migrations on every PR.
# Drop into .github/workflows/zindex-er.yml. Set the listed secrets, customise
# the parse step for your ORM, and the agent runs unattended on every PR that
# touches your migrations directory.
#
# Trigger: PR opened/synchronised that touches prisma/migrations/** (or your
# ORM's equivalent).
# Secrets: ZINDEX_API_KEY, ZINDEX_SCENE_ID.
# Outputs: a workflow artifact containing the rendered SVG + a PR comment with
# diff summary and rendered diagram.

name: Zindex - ER diagram from migrations

on:
  pull_request:
    paths:
      - "prisma/migrations/**"
      - "drizzle/meta/**"
      - "alembic/versions/**"
      - "db/migrate/**"
  workflow_dispatch:

permissions:
  contents: read
  pull-requests: write

concurrency:
  group: ${{ github.workflow }}-${{ github.event.pull_request.number || github.ref }}
  cancel-in-progress: ${{ github.event_name == 'pull_request' }}

env:
  ZINDEX_API_BASE: https://api.zindex.ai

jobs:
  sync-er-diagram:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      # 1. Parse the changed migration files. This step is project-specific:
      #    write a small parser for your ORM that emits the table → columns →
      #    foreign keys structure as JSON to ./out/schema.json. The shape
      #    expected downstream is { entities: [{ id, label, columns: [...] }],
      #    relationships: [{ id, from: { entity, column }, to: { entity } }] }.
      - name: Parse migrations to schema.json
        run: |
          mkdir -p out
          node scripts/parse-migrations.mjs > out/schema.json
          echo "::notice::Parsed $(jq '.entities | length' out/schema.json) entities, $(jq '.relationships | length' out/schema.json) relationships"

      # 2. Read the persisted scene's current revision (so we can diff later).
      - name: Capture current revision
        id: prev_rev
        env:
          ZINDEX_API_KEY: ${{ secrets.ZINDEX_API_KEY }}
          ZINDEX_SCENE_ID: ${{ secrets.ZINDEX_SCENE_ID }}
        run: |
          REV=$(curl -fsSL \
            -H "Authorization: Bearer $ZINDEX_API_KEY" \
            "$ZINDEX_API_BASE/v1/scenes/$ZINDEX_SCENE_ID" | jq -r '.revision')
          echo "rev=$REV" >> "$GITHUB_OUTPUT"
          echo "::notice::Persisted scene at revision $REV"

      # 3. Compute the typed-operation batch from the parsed schema. This is
      #    where your agent / script does the real work - turn schema diffs
      #    into createNode / updateNode / deleteElement / createEdge ops with
      #    stable element ids (table name → element id) so renames produce
      #    `updateNode` rather than delete-and-create.
      - name: Compute applyOps batch
        run: node scripts/schema-to-ops.mjs out/schema.json > out/ops.json

      # 4. Apply the batch - atomic via errorPolicy=allOrNothing.
      - name: Apply ops to persisted scene
        id: apply
        env:
          ZINDEX_API_KEY: ${{ secrets.ZINDEX_API_KEY }}
          ZINDEX_SCENE_ID: ${{ secrets.ZINDEX_SCENE_ID }}
        run: |
          RESP=$(curl -fsSL -X POST \
            -H "Authorization: Bearer $ZINDEX_API_KEY" \
            -H "Content-Type: application/json" \
            --data-binary @out/ops.json \
            "$ZINDEX_API_BASE/v1/scenes/$ZINDEX_SCENE_ID/ops")
          NEW_REV=$(echo "$RESP" | jq -r '.revision')
          echo "rev=$NEW_REV" >> "$GITHUB_OUTPUT"
          echo "applied=$(echo "$RESP" | jq -r '.applied')" >> "$GITHUB_OUTPUT"

      # 5. Render the new revision. Watermark stamps scene-id + revision + date.
      - name: Render updated scene to SVG
        env:
          ZINDEX_API_KEY: ${{ secrets.ZINDEX_API_KEY }}
          ZINDEX_SCENE_ID: ${{ secrets.ZINDEX_SCENE_ID }}
        run: |
          curl -fsSL -X POST \
            -H "Authorization: Bearer $ZINDEX_API_KEY" \
            -H "Content-Type: application/json" \
            -d '{"format":"svg","theme":"clean"}' \
            "$ZINDEX_API_BASE/v1/scenes/$ZINDEX_SCENE_ID/render" \
            | jq -r '.output.content' > out/diagram.svg

      # 6. Diff the new revision against the prior one - this is the
      #    PR-comment payload.
      - name: Diff revisions
        id: diff
        env:
          ZINDEX_API_KEY: ${{ secrets.ZINDEX_API_KEY }}
          ZINDEX_SCENE_ID: ${{ secrets.ZINDEX_SCENE_ID }}
          PREV: ${{ steps.prev_rev.outputs.rev }}
          NEW: ${{ steps.apply.outputs.rev }}
        run: |
          curl -fsSL \
            -H "Authorization: Bearer $ZINDEX_API_KEY" \
            "$ZINDEX_API_BASE/v1/scenes/$ZINDEX_SCENE_ID/diff?from=$PREV&to=$NEW" \
            > out/diff.json
          ADDED=$(jq -r '.summary.added' out/diff.json)
          REMOVED=$(jq -r '.summary.removed' out/diff.json)
          MODIFIED=$(jq -r '.summary.modified' out/diff.json)
          echo "added=$ADDED" >> "$GITHUB_OUTPUT"
          echo "removed=$REMOVED" >> "$GITHUB_OUTPUT"
          echo "modified=$MODIFIED" >> "$GITHUB_OUTPUT"

      # 7. Upload the rendered SVG as a workflow artifact (30-day retention).
      #    The PR comment links to this artifact.
      - uses: actions/upload-artifact@v4
        id: upload
        with:
          name: er-diagram-svg
          path: out/diagram.svg
          retention-days: 30

      # 8. Find the previous bot comment so we can replace it (idempotent).
      - uses: peter-evans/find-comment@v3
        id: find_comment
        with:
          issue-number: ${{ github.event.pull_request.number }}
          comment-author: "github-actions[bot]"
          body-includes: "<!-- zindex-bot:er-diagram-from-migrations -->"

      # 9. Post (or replace) the PR comment with the diff summary + diagram link.
      - uses: peter-evans/create-or-update-comment@v4
        with:
          comment-id: ${{ steps.find_comment.outputs.comment-id }}
          issue-number: ${{ github.event.pull_request.number }}
          edit-mode: replace
          body: |
            <!-- zindex-bot:er-diagram-from-migrations -->
            ### ER diagram updated · revision ${{ steps.apply.outputs.rev }}

            | | |
            |---|---|
            | Schema changes | +${{ steps.diff.outputs.added }} / -${{ steps.diff.outputs.removed }} / ~${{ steps.diff.outputs.modified }} |
            | Ops applied | ${{ steps.apply.outputs.applied }} |
            | Revision | ${{ steps.prev_rev.outputs.rev }} → ${{ steps.apply.outputs.rev }} |

            <details><summary>Download rendered SVG</summary>

            ${{ steps.upload.outputs.artifact-url }}

            </details>

            <sub>Rendered by Zindex · scene `${{ secrets.ZINDEX_SCENE_ID }}`</sub>