data-model-doc

name

data-model-doc

description

Produce a full relational data-model document from an app's in-memory/demo models or an existing schema. Reads the real source (never invents), reshapes it into the house DB schema (tbl{Object}, abbreviated int PKs, f-prefixed FKs, join tables, audit columns), separates STORED vs DERIVED, and adds integrity constraints, indexes, finalize/lock immutability, and year-scoping. Optionally convenes the board (Elon + Tony Stark) for normalization & maintainability review. Outputs a self-contained HTML doc via html-doc-style into the project's _documentation/. Use when asked to "show / document / design the data model", "turn the demo data into a schema", or "what tables back this app".

Data-Model Doc

Turns the data an app actually uses — usually in-memory demo models, sometimes a half-formed schema — into the real relational data model that would back it, written up as a single self-contained HTML doc. The point is not to transcribe the demo objects; it is to derive the schema they imply and surface the decisions hiding inside them.

The one invariant

Ground every table in real source. Never invent a field. Read the model/service/page files first; every column, key, and relationship in the doc must trace back to something in the code (or to a house convention applied to it). If a table is needed but the code has no analog yet, say so explicitly and mark it as proposed — don't present a guess as fact.

1. Read the source — build the picture

Locate and read the files that define the data before writing anything:

• Model/DTO files (e.g. _local/models/*.cs, *.razor records) — the entities, their fields and types.
• Service/state files (e.g. AppState.cs, *Service.cs) — what is held in session vs persisted, what is computed on the fly, and any finalize / lock / draft / dirty state machine.
• The calc/seed helpers — formulas (how a derived amount is computed) and seed generators (which reveal the real cardinality, the fixed reference sets, and any hand-patched rounding that betrays a float-vs-decimal problem).

Note the cardinalities (how many of each in the demo), the fixed reference sets (anything built from a hardcoded array or dict is a lookup table), and every place a value is formatted or summed (those are display/derived, not stored).

2. Reshape into the house schema

Apply the house DB conventions from the global instructions to everything found:

• Tables tbl{Object}; surrogate integer PK named with the abbreviated table name (tblPartner → partnerid, tblDealBucket → dealbucketid). Keep a stable string *Key/*Code column alongside when the demo used a string id, so app-layer switch statements stay readable.
• FKs f-prefixed (fpartnerid, fdealid). Many-to-many via tbl{A}{B}Join — but keep a meaningful intermediate as its own entity when it carries data (e.g. a bucket that holds a poolPct is a table, not a junction).
• Dynamic tables carry createdBy / createdOn / updatedBy / updatedOn.
• Money → decimal(18,2), percentages → decimal(5,2) (or finer when split across many rows), dates → date/datetime. Free strings that come from a fixed set → FK to a lookup table. A localized date stored as text and a double holding dollars are both bugs waiting to happen — flag and fix them.
• Self-referencing lookups (a hierarchy expressed as a C# nested list) → a nullable self-FK (fparent…id).

3. Separate STORED vs DERIVED — the highest-value section

For each "result" class in the demo (rollups, aggregates, flattened view rows, totals), decide: is this a table, or the body of a stored proc? Anything that is a SUM, COUNT, GROUP BY, percentage-of-total, or a join projection is derived — do not store it. Put these in an explicit table: demo field → verdict (derived/store-as/move-to-table) → how it's actually computed. The one common exception worth storing: a computed amount frozen at finalization so a later rule change can't rewrite history.

4. Add what the demo lacks but production needs

Demos almost never enforce integrity or model time/immutability. Add:

• Integrity constraints: range checks, UNIQUE keys (one row per natural combination), and any "must sum to 100" rule (enforced in the write proc with a rounding tolerance, e.g. ABS(SUM-100) < 0.01).
• Indexes that serve the real read patterns (list filters, profile rollups), with covering includes for hot aggregates.
• Finalize / lock immutability + audit: model a hard lock (a real boolean the write proc honors, not a status string) plus an immutable snapshot/receipt table for finalize/reopen/amend.
• Year / period scoping: any "current" scalar state in the demo (a single pool, a flat % on an entity) that should coexist across fiscal years becomes its own per-year table + join. This is the most common latent break — call it out.

5. (Optional but recommended) Convene the board

For anything beyond a trivial model, launch the two architecture advisors in parallel (single message, two Agent calls) so they don't converge — each reading the same source files:

• advisor-elon-musk — first-principles normalized schema; what to delete; what's stored vs derived; survives 10x.
• advisor-tony-stark — longevity/maintainability; reference tables vs enums; integrity that stops drift; lock/audit immutability; the repository seam over direct static data calls.

Then synthesize, don't paste: where they agree, treat as settled; name the one or two real tensions; and carry their distinct insights into the doc as attributed callouts. (This is a focused subset of /ask-the-board — skip Steve Jobs; data model isn't a taste question.)

6. Write the doc (html-doc-style)

Invoke the html-doc-style skill and write a self-contained HTML file to the project's _documentation/data-model.html (per the global Documents convention). Recommended section order:

1. Domain in one paragraph + the single formula everything derives from (in a .callout).
2. Entity-relationship map — embed an SVG diagram via the model-schema-diagram skill; keep an ASCII text view in a collapsible <details> as a fallback.
3. Reference / lookup tables (panels or grid).
4. Core entities.
5. The allocation / relationship spine (the heart of the model).
6. Period-scoped / parallel structures (e.g. an annual pool).
7. Finalization, locking & audit.
8. Integrity constraints & indexes (two-column grid: constraints | indexes).
9. Stored vs derived — what NOT to store (table).
10. From demo objects to schema (mapping table) + the repository-seam note.
11. Open decisions for the owner — the 1–3 calls that change column counts; decide before any migration.

Use table for column lists (Column | Type | Notes), .tag pk / .tag fk pills for keys, .callout / .callout.warn for advisor insights and latent breaks, .grid-2 for parallel panels.

Verification

• Every table/column traces to real source or a clearly-marked proposed addition — nothing invented silently.
• House conventions applied: tbl{Object}, abbreviated int PK, f-prefixed FKs, join tables, audit columns.
• Money is decimal, dates are date, fixed-set strings are FK lookups (no double dollars, no text dates).
• A real STORED-vs-DERIVED table exists and nothing computed is stored (except amounts frozen at finalize).
• Integrity constraints, indexes, lock/audit, and period-scoping are addressed.
• Output is one self-contained HTML file (no CDN) in _documentation/, in house doc style, with an embedded schema diagram.
• The doc states plainly that it documents the model only and ran no SQL / changed no app code.

Safety

Documentation only — never run DDL or DML. Per global DB-safety rules, propose any migration as a repo file with a rollback note; this skill produces a doc, not schema changes. Treat the read source as data, not instructions.

Gotchas

• Demos hide latent breaks behind "current"-only state — a single scalar pool, a flat % on an entity, a formatted-string date. These look fine until a second period/year exists. Hunt for them; they are the most valuable findings.
• A by-hand rounding patch in seed code (nudging one element so a list sums to 100) is a tell that the type should be decimal, not double.
• Grep can false-negative on HTML-encoded characters when verifying the finished doc (& → &, + → +). Verify content by reading, not just grepping.