sqlserver-er-knowledgebase

SQL Server ER Diagram Knowledge Base Builder

Purpose

Scan the project repository for committed ER diagram files, parse their content, and build a structured knowledge base that other skills can reference when generating or reviewing T-SQL, migrations, EF Core entities, and Dapper queries.

Supported ER Diagram Formats

Format	Extensions / Patterns	Parser Strategy
DBML (dbdiagram.io)	*.dbml	Parse Table, Ref, Enum blocks
Mermaid erDiagram	*.mmd, *.mermaid, fenced blocks in *.md	Parse erDiagram section
PlantUML entity	*.puml, *.plantuml, *.pu	Parse entity and relationship lines
SQL DDL	*.sql (CREATE TABLE/ALTER TABLE)	Extract tables, columns, PKs, FKs, constraints
SSDT project	*.sqlproj + dbo/Tables/*.sql	Walk SSDT folder tree, extract DDL per file
EF Core models	*.cs with IEntityTypeConfiguration	Extract entity properties, relationships, keys
JSON schema	er-diagram.json, schema.json	Direct structured parse
Draw.io / diagrams.net	*.drawio, *.drawio.xml	Extract entity nodes and connector edges

Discovery

Scan the repo for ER diagram sources in priority order:

1. Config-specified path — check .db-agent.json for engines.sqlserver.erDiagram path
2. Docs folder — docs/**/*.dbml, docs/**/*.mmd, docs/**/*.puml, docs/**/*.drawio
3. Database project — database/**/*.sql, **/*.sqlproj
4. Markdown embedded — docs/**/*.md files containing erDiagram fenced blocks
5. EF Core models — **/Entities/*.cs, **/Models/*.cs, **/Configurations/*.cs
6. Root level — er-diagram.*, schema.* at repo root

Report which sources were found and which were used.

Knowledge Base Schema

Build a structured representation with the following shape:

{
  "metadata": {
    "sources": ["docs/er-diagram.dbml", "database/dbo/Tables/*.sql"],
    "generatedAt": "2026-04-09T12:00:00Z",
    "entityCount": 12,
    "relationshipCount": 15
  },
  "entities": [
    {
      "name": "Orders",
      "schema": "dbo",
      "description": "Customer purchase orders",
      "columns": [
        {
          "name": "Id",
          "type": "INT",
          "nullable": false,
          "isPrimaryKey": true,
          "isIdentity": true
        },
        {
          "name": "CustomerId",
          "type": "INT",
          "nullable": false,
          "isForeignKey": true,
          "references": { "entity": "Customers", "column": "Id" }
        }
      ],
      "indexes": [
        {
          "name": "IX_Orders_CustomerId",
          "columns": ["CustomerId"],
          "isUnique": false,
          "includes": ["CustomerName", "Total"]
        }
      ],
      "constraints": [
        {
          "name": "CK_Orders_Total",
          "type": "CHECK",
          "expression": "[Total] >= 0"
        }
      ]
    }
  ],
  "relationships": [
    {
      "name": "FK_OrderItems_Orders",
      "from": { "entity": "OrderItems", "column": "OrderId" },
      "to": { "entity": "Orders", "column": "Id" },
      "type": "many-to-one",
      "onDelete": "CASCADE",
      "onUpdate": "NO ACTION"
    }
  ],
  "enums": [
    {
      "name": "OrderStatus",
      "values": ["Pending", "Confirmed", "Shipped", "Delivered", "Cancelled"]
    }
  ]
}

Parsing Rules

DBML

Table Orders {
  Id int [pk, increment]
  CustomerId int [ref: > Customers.Id]
  Total decimal(18,2) [not null, default: 0]
  Status OrderStatus [not null]
  CreatedAt datetime2 [default: `SYSUTCDATETIME()`]
}

Enum OrderStatus {
  Pending
  Confirmed
  Shipped
}

Ref: OrderItems.OrderId > Orders.Id [delete: cascade]

• Map [pk] to isPrimaryKey: true
• Map [increment] to isIdentity: true
• Map [ref: > Table.Col] to inline FK relationship (many-to-one)
• Map [ref: - Table.Col] to one-to-one
• Map [ref: <> Table.Col] to many-to-many
• Parse standalone Ref: lines for explicit relationship definitions

Mermaid erDiagram

erDiagram
    Orders ||--o{ OrderItems : contains
    Customers ||--o{ Orders : places
    Orders {
        int Id PK
        int CustomerId FK
        decimal Total
        string Status
    }

• Map ||--o{ to one-to-many
• Map ||--|| to one-to-one
• Map }o--o{ to many-to-many
• Extract column definitions from entity blocks
• Map PK/FK markers to key flags

SQL DDL

• Parse CREATE TABLE for entity + columns
• Parse ALTER TABLE ... ADD CONSTRAINT for FKs, CHECKs, UNIQUEs
• Parse CREATE INDEX / CREATE UNIQUE INDEX for index definitions
• Detect schema from bracket notation: [dbo].[Orders]
• Handle IDENTITY(seed, increment) for identity columns

EF Core Models

• Parse Property(e => e.Column) for column config
• Parse HasOne/HasMany/WithOne/WithMany for relationships
• Parse HasKey / HasAlternateKey for keys
• Parse HasIndex for indexes
• Parse ToTable("name", "schema") for entity-to-table mapping

Output

Knowledge Base File

Write the parsed knowledge base to .er-knowledgebase.json in the project root (or path configured in .db-agent.json under engines.sqlserver.erKnowledgebasePath).

Summary Report

After building the knowledge base, output a summary:

ER Knowledge Base Built
=======================
Sources:     docs/er-diagram.dbml, database/dbo/Tables/*.sql
Entities:    12
Columns:     87
Relationships: 15 (10 many-to-one, 3 one-to-many, 2 one-to-one)
Indexes:     8
Constraints: 5
Enums:       3

Warnings:
- OrderItems.ProductId references Products.Id but Products entity not found
- Customers.Email has no unique constraint (consider adding one)

Cross-Referencing

When the knowledge base is available, other skills should use it to:

• sqlserver-coding: Validate column names, types, and FK references in new T-SQL
• sqlserver-frameworks: Verify EF Core entity configs match the ER diagram
• sqlserver-testing: Generate test data that respects FK constraints and CHECK constraints
• sqlserver-project-structure: Verify all entities have corresponding .sql files in the SSDT layout

Conflict Resolution

When multiple ER sources exist and disagree:

1. SQL DDL (actual schema) takes precedence over diagram files
2. SSDT project files take precedence over standalone SQL files
3. EF Core models are treated as secondary — flag mismatches as warnings
4. Diagram files (DBML, Mermaid, PlantUML, Draw.io) are treated as design intent — flag drift from DDL

Refresh

The knowledge base should be rebuilt when:

• The agent session starts and .er-knowledgebase.json is missing or stale
• Any ER source file has been modified since last build (compare file timestamps)
• The user explicitly asks to refresh the knowledge base
• A new migration or DDL file is created during the session