petrowsky/extract-erd

Extract ERD

This skill analyzes a FileMaker solution's relationship graph and produces a true ERD as a Mermaid diagram — collapsing utility table occurrences down to base tables and letting the developer classify which relationships represent genuine entity connections.

Purpose: Give a developer who is new to a solution a quick, accurate, high-level understanding of its data model.

Output: A Mermaid erDiagram file written to agent/sandbox/.

Performance contract

Metric: Minimize tool calls from invocation to ERD ready for developer review.

Key rules:

• Parallel reads: All independent data sources must be read in a single message with multiple tool calls — never sequentially.
• Batch operations: Combine related operations that don't depend on each other into the same parallel batch.
• No unnecessary reads: Do not re-read files already in context.

Data sources

The skill uses two possible data sources, in order of preference:

1. Exploded XML index files (preferred)

When agent/context/{solution}/ exists with index files:

• table_occurrences.index — TO-to-base-table mapping (columns: TOName|TOID|BaseTableName|BaseTableID)
• relationships.index — all relationships between TOs (columns: LeftTO|LeftTOID|RightTO|RightTOID|JoinType|JoinFields|CascadeCreate|CascadeDelete)
• fields.index — all fields per base table (columns: TableName|TableID|FieldName|FieldID|DataType|FieldType|AutoEnter|Flags)

2. OData $metadata endpoint (fallback)

When index files are not available but agent/config/automation.json has an odata block for the solution:

• Fetch {base_url}/{database}/$metadata with Basic auth
• Parse EntityType elements for fields and types
• Parse NavigationProperty and NavigationPropertyBinding for relationships
• Infer base tables by grouping EntityTypes with identical field sets (same field names in same order = same base table). OData exposes TOs, not base tables — this heuristic resolves the mapping.

If neither source is available, instruct the developer to either run Explode XML or configure OData access.

Workflow

Step 1 — Determine the solution and gather raw data

List subdirectories under agent/context/ using Bash ls.

• If one subfolder exists, use it automatically and proceed directly to reading index files.
• If multiple exist, ask the developer which solution, then proceed to reading index files.
• If none exist, check automation.json for OData config and fall back to the OData path.

Index file path — read all three files in parallel:

Once the solution name is known, read all three index files simultaneously in a single parallel batch:

• agent/context/{solution}/table_occurrences.index
• agent/context/{solution}/relationships.index
• agent/context/{solution}/fields.index

These files have zero dependencies between them. Never read them sequentially.

OData path:

1. Fetch $metadata from the OData endpoint (single request contains everything)
2. From the single response, extract in one analytical pass:
   • All EntityType elements — field names, types, and annotations
   • All NavigationProperty elements — TO-level relationships
   • Group EntityTypes by identical field name lists to infer base table groups
   • Map relationships to inferred base tables

Step 2 — Collapse to base tables and classify (single analytical pass)

Process all three data sources together in one pass. Do not separate collapse, table classification, and relationship pre-classification into distinct sequential steps — they use the same data and can be computed together.

Collapse: For each relationship, resolve both sides from TO names to base table names. Group multiple TO-level relationships between the same two base tables.

For each base-table pair, collect:

• All TO pairs that connect them
• The join field(s) for each TO pair
• Join type (Equal, >, <, etc.)
• Cascade settings

Classify tables into one of three categories (suggestions only — the developer makes the final call):

ENTITY — a first-class business object that exists independently:

• Has a PrimaryKey field
• Has descriptive/attribute fields beyond just keys and timestamps (e.g., Name, Status, Address)
• Other tables reference it via foreign keys
• Examples: Clients, Products, Staff, Invoices

JOIN — exists primarily to connect two entities (associative/junction table):

• Has a PrimaryKey field
• Has two or more ForeignKey* fields pointing to other tables
• Has few or no descriptive fields beyond the keys, timestamps, and fields derived from the parent entities
• Often has cascade delete from a parent
• May carry payload fields (Qty, Amount) but its primary purpose is connecting entities
• Examples: Line Items (connects Invoices and Products), Enrollments (connects Students and Courses)

UTILITY — a table that supports the solution but is not part of the core data model:

• Settings/preferences tables (Admin, Config) — single-record, global fields, UI flags
• Navigation/selector tables — used for UI state, global search, or card windows
• Log/audit tables — append-only records for tracking activity
• Temporary/staging tables — used for imports, exports, or intermediate processing
• Has no foreign keys pointing to it from other tables (or very few)
• Not part of the entity model — suggest excluding from the ERD

Heuristics to auto-classify:

| Signal | Points toward | |--------|--------------| | 2+ ForeignKey fields | JOIN | | Cascade delete enabled on a parent relationship | JOIN | | Few non-key, non-timestamp fields | JOIN | | Multiple global fields | UTILITY | | No inbound FK references from other tables | UTILITY | | Rich descriptive fields (Name, Address, Phone, etc.) | ENTITY | | Other tables reference it via FK | ENTITY |

Pre-classify relationships — apply heuristics to suggest a classification for each base-table relationship (suggestions only):

Likely TRUE ERD:

• Join uses PrimaryKey on one side and a ForeignKey* field on the other
• Join type is Equal
• Cascade delete is enabled (strong signal of parent-child)
• Field naming follows FK convention (ForeignKey{TableName}, FK{TableName}, {TableName}ID)

Likely UTILITY:

• Join fields are not PK/FK (e.g., ForeignKeyStaff = CreationTimestamp)
• Join type is not Equal (cartesian, inequality joins)
• Self-join where both sides are the same base table and join fields are not PK = PK
• Multiple TO pairs exist for the same base-table pair and this particular pair uses non-standard join fields

UNCERTAIN:

• Mixed signals — present the evidence and let the developer decide

Step 3 — Present the relationship report

Display a structured report with three sections:

Base Tables

List each base table with its classification, TO count, and total field count:

Base Tables (N total):
  1. Clients        ENTITY    — 2 TOs (Clients Primary, Clients Secondary) — 26 fields
  2. Invoices       ENTITY    — 3 TOs (Invoices, Invoices 2, Invoices 3)   — 23 fields
  3. Line Items     JOIN      — 2 TOs (Line Items, Line Items 2)           — 13 fields
  4. Products       ENTITY    — 1 TO  (Products)                           — 13 fields
  5. Staff          ENTITY    — 1 TO  (Staff)                              — 16 fields
  6. Admin          UTILITY   — 1 TO  (Admin)                              — 25 fields

Relationships

For each base-table-level relationship, show:

Relationship N: Clients → Invoices
  Classification: TRUE ERD
  Reasoning: PrimaryKey = ForeignKeyClient (PK→FK pattern, Equal join)
  TO pairs:
    • Clients Primary → Invoices  |  PrimaryKey = ForeignKeyClient  |  Equal  |  no cascade
  Suggested cardinality: one-to-many (1 Client has many Invoices)

Relationship N: Invoices → Invoices (self-join)
  Classification: UTILITY
  Reasoning: ForeignKeyStaff = CreationTimestamp — non-PK/FK join fields, likely a filtered portal relationship
  TO pairs:
    • Invoices → Invoices 2  |  ForeignKeyStaff = CreationTimestamp  |  Equal  |  no cascade
    • Invoices → Invoices 3  |  ForeignKeyStaff = ForeignKeyClient   |  Equal  |  no cascade

Step 4 — Developer confirmation (two questions in one prompt)

Use a single AskUserQuestion call to present the full report and ask both questions at once:

Prompt: Present the report above, then ask:

Review the table and relationship classifications above. Reply with any changes (e.g., "Line Items is an ENTITY not a JOIN", "Relationship 3 should be TRUE ERD") or confirm with "looks good".

Also, how much field detail in the diagram?

1. Full — all fields per table (best for small solutions, can be large for 100+ field tables)
2. Keys only — PrimaryKey and ForeignKey fields only (compact, focuses on relationships)
3. Keys + business fields — PKs, FKs, and non-system fields (excludes CreationTimestamp, CreatedBy, ModificationTimestamp, ModifiedBy, FoundCount, and similar audit/system fields)

Default is option 3 if you don't have a preference.

For solutions with any table exceeding 50 fields, mention the field counts and recommend option 2 or 3.

This batches two developer interactions into one round-trip. If the developer adjusts any classifications, update accordingly before proceeding.

Step 5 — Determine cardinality and generate the Mermaid diagram

These two operations are tightly coupled — cardinality feeds directly into diagram generation. Perform them in a single pass.

For each confirmed TRUE ERD relationship, infer cardinality:

| Pattern | Cardinality | Mermaid syntax | |---------|-------------|----------------| | PK on left, FK on right | One-to-many | \|\|--o{ | | FK on left, PK on right | Many-to-one | }o--\|\| | | PK on both sides | One-to-one | \|\|--\|\| | | FK on both sides | Many-to-many (rare in FM) | }o--o{ |

When the pattern is ambiguous, default to one-to-many and add a comment.

Build an erDiagram with:

• ENTITY and JOIN tables (exclude UTILITY tables unless the developer overrode the classification)
• Fields filtered according to the developer's choice in Step 4
• Only TRUE ERD relationships (confirmed in Step 4)
• Cardinality notation from the table above
• Relationship labels derived from the FK field name (e.g., ForeignKeyClient → "has" or "belongs to")

Add a Mermaid comment (%%) above each table indicating its classification (ENTITY or JOIN) so the diagram is self-documenting.

Field inclusion by mode

| Mode | Include | Exclude | |------|---------|---------| | Full | All fields | Nothing | | Keys only | PrimaryKey, ForeignKey*, FK* fields | Everything else | | Keys + business | All fields except system/audit fields | CreationTimestamp, CreatedBy, ModificationTimestamp, ModifiedBy, FoundCount, and fields with global flag |

Field type mapping

Map FileMaker field types to concise Mermaid-compatible labels:

| FileMaker type | Mermaid label | |----------------|---------------| | Text | text | | Number | number | | Date | date | | Timestamp | timestamp | | Time | time | | Binary (Container) | container |

Field annotations

| Condition | Annotation | |-----------|------------| | Field named PrimaryKey or has notEmpty,unique flags | PK | | Field name starts with ForeignKey or FK | FK | | FieldType is Calculated | (append type from calc, keep annotation if PK/FK) | | FieldType is Summary | (label as summary) |

Mermaid template

erDiagram
    %% ENTITY
    Clients {
        text PrimaryKey PK
        text Name
        text ContactFirstName
        text ContactLastName
        number InvoiceCount
    }
    %% ENTITY
    Invoices {
        text PrimaryKey PK
        text ForeignKeyClient FK
        text ForeignKeyStaff FK
        date Date
        number Total
    }
    %% JOIN
    LineItems {
        text PrimaryKey PK
        text ForeignKeyInvoice FK
        text ForeignKeyProduct FK
        number Qty
        number Amount
        number LineSubtotal
    }

    Clients ||--o{ Invoices : "has"
    Staff ||--o{ Invoices : "assigned"
    Invoices ||--o{ LineItems : "contains"
    LineItems }o--|| Products : "references"

Step 6 — Write to sandbox

Write the Mermaid file to agent/sandbox/{solution-name}-erd.md with a brief header:

# {Solution Name} — Entity Relationship Diagram

Generated: {date}
Source: {index files | OData $metadata}
Relationships: {N true ERD} confirmed, {N utility} excluded

{mermaid diagram}

Report the file path to the developer.

Key considerations

• Utility tables: Many FM solutions have tables that support the application but aren't part of the core data model — settings/preferences (Admin, Config), navigation/selector tables, log/audit tables, staging/temp tables. Surface them in the base table list as UTILITY but suggest excluding from the ERD unless the developer says otherwise.
• Self-joins: FM commonly uses self-join TOs for filtered portals (e.g., showing only active invoices). These are almost always utility, not true ERD. Flag them clearly.
• Multi-predicate joins: Some relationships have compound join fields (;-separated in the index). Show all predicates — compound joins are more likely to be utility relationships.
• External data sources: TOs marked as external (type != "Local" in the relationship XML) should be noted — they reference tables in other FM files.
• Large solutions: For solutions with 50+ base tables or 100+ relationships, consider grouping the report by domain or presenting in batches to avoid overwhelming the developer.

Examples

Example 1 — Standard solution with index files

User: "I'm new to this solution — can you map out the database for me?"

1. ls agent/context/ → Invoice Solution/
2. Read all three index files in parallel (single message, 3 simultaneous Read calls)
3. Single analytical pass: collapse 11 TOs to 6 base tables, classify tables, pre-classify ~4 unique base-table pair relationships (4 TRUE ERD, 2 UTILITY, 1 UNCERTAIN)
4. Present report with both questions (classifications + field detail) in one prompt
5. Developer confirms → generate Mermaid erDiagram with cardinality in one pass
6. Write to agent/sandbox/Invoice Solution-erd.md

Tool call sequence: ls (1) → 3 parallel Reads (1) → analysis + present report (0) → ask developer (1) → generate + write (1) = 4 tool call rounds

Example 2 — No index files, OData available

User: "Extract the ERD for this solution"

1. ls agent/context/ → empty
2. Check automation.json → OData config exists for the solution
3. Fetch $metadata (single request contains all data)
4. Single analytical pass: parse EntityTypes + NavigationProperties, infer base tables, classify, pre-classify relationships
5. Continue from Step 3 as normal

Example 3 — Neither source available

User: "Show me the ERD"

1. No index files, no OData config
2. Report: "I need either exploded XML or OData access to extract the ERD. Would you like to run Explode XML to generate the index files, or configure OData in automation.json?"