The problem
A provider data model — 322 tables and roughly 2,500 business-relevant columns across 16 domains — was under active development, changing several times per week. That created four compounding problems.
No source of truth for what the data meant. Engineers, analysts, and customer-facing product staff had no field-level reference. Tribal knowledge and stale spreadsheets filled the gap — the kind with a date in the filename.
Documentation decayed the moment it was written. A hand-maintained dictionary was obsolete within a week. One refresh alone picked up 145 new columns and 13 dropped ones.
Nobody could see what the UI captured but the database could not store. Screens collected fields with no backing column, so data was silently lost at submit time. There was no systematic way to find these.
Data products could not be sold reliably. The business sold field bundles to customers on subscription. Without immutable, versioned field membership, a customer's contracted bundle could silently change shape underneath them.
Approach
Generate, never hand-maintain
Every deliverable is regenerated from live sources on each run. Curated human knowledge — business descriptions, domain assignment, PII classification — lives in a single curation layer that is merged onto the live schema rather than stored alongside a copy of it.
That separation is the whole design. Schema drift cannot corrupt the curation, and curation cannot mask schema drift.
Reconciling five sources, each authoritative for something different
Rather than trusting any single system, the pipeline cross-references five, each treated as authoritative only for what it genuinely knows:
| Source | Authoritative for |
|---|---|
| Warehouse catalog | Tables, columns, data types |
| SQL Server, direct | NOT NULL constraints → required flags |
| Front-end codebase | Field labels, validation rules, screen grouping |
| Eight backend API repos | API field → database column renames |
| Design files | Gap detection only — screen fields with no database backing |
The reconciliation is deliberately conservative. Required flags use OR logic across sources. Descriptions are never fabricated — a field with no mapping publishes with a blank description rather than an invented one. Where two sources disagree, the pipeline emits a data integrity discrepancy report: columns the database enforces but the UI treats as optional are real latent bugs, handed back to the engineering team rather than smoothed over in the documentation.
Gap detection as a first-class output
Screen fields with no database column are tracked as numbered gap items with proposed remediation — table, column, type. Forty gaps were catalogued, and the tracker records both new gaps opened by schema drops and gaps closed by schema additions. When one table was dropped, three previously-closed gaps re-opened the same day, unhousing 27 claims-history fields.
That turned an invisible failure mode into a prioritized engineering backlog.
Contractual layer, separated from the descriptive one
Data products were modeled properly: a master record, then an immutable-once-published version, then version-to-field membership, then customer subscriptions. Field membership freezes at publish; new fields require a new draft version. Customers subscribe to a version, not a bundle — so a contracted deliverable can never silently change shape.
Critically, the daily documentation sync deliberately does not touch product assignments. The descriptive layer and the contractual layer move independently, because a documentation refresh must never alter what a customer is owed.
Documentation as machine context
Two of the five deliverables are context bundles for AI-assisted development — generated markdown giving any developer or coding agent full schema awareness from the start of a session. Documentation that serves humans and machines from the same generated source, rather than a wiki nobody updates and a prompt nobody maintains.
Outcome
Running in production, unattended, since May 2026. A representative run pulled two repositories, parsed 451 UI fields, queried 840 NOT NULL constraints, regenerated six artifacts, merged 2,451 rows to the warehouse, and published five documentation pages — end to end in 3 minutes 38 seconds.
| Fields documented | 2,451 curated / 2,583 published |
| Tables covered | 322 core + 21 enrichment |
| Domains | 16 |
| PII fields classified | 188 |
| Data gaps identified | 213 screen-field entries → 40 tracked gaps, 9 resolved |
| Data products modeled | 72 bundles, ~1,100 field assignments |
| Governed deliverables | 5, auto-published |
| Unattended daily runs | 65+ consecutive |
The production load was later handed off to a dedicated ETL team via a self-contained migration bundle — reference implementations, an explicit data contract, and a build spec — with the local step disabled to prevent double-writes.
Notable decisions
The no-fabrication rule. Roughly 49% of fields carry descriptions. That is deliberate. A blank cell is honest; a plausible-sounding invented description in a governed healthcare data dictionary is a liability. The rule is enforced in code, not in policy.
Read-only by construction. Every source connection is read-only. The only writes go to project-owned targets.
Idempotent merge with lineage. Creation dates are preserved for existing rows; removed columns are marked inactive with a removal date rather than deleted. The field catalog is an audit trail, not a snapshot.
Change detection before publish. Content-hashed comparison means unchanged pages are skipped rather than republished, so version history stays meaningful and "what changed on the 23rd?" is an answerable question.
What transfers
- Separate curation from extraction. Human knowledge in one layer, live schema in another, merged at generation time. Neither can corrupt the other.
- Never fabricate a description. In governed data, an honest blank beats a confident guess.
- Treat source disagreement as a finding, not noise to reconcile away. The conflicts were real bugs.
- Make the contractual layer immovable and let the descriptive layer refresh freely.
- Detect change before publishing so version history remains a usable record.