The problem
A healthcare provider-data platform had a governance layer that existed only as spreadsheets and hand-run SQL.
Business analysts needed to define domains (top-level groupings such as credentialing, directory, sanctions), data chunks (reusable bundles of related fields), use cases (consumer-facing packages composed of chunks), and field mappings — which of roughly 2,500 physical warehouse columns belong to each chunk.
Every relationship is many-to-many. The mappings change over time and need an auditable history. And the only people qualified to maintain them are analysts, not engineers.
Meanwhile downstream consumers wanted a JSON extract per provider pulling actual data for a selected set of chunks, from multiple independent source systems, so the golden record could be compared against what the provider had self-attested.
Concretely: no UI, no versioning, no audit trail, no repeatable extract — over a data model spanning four catalogs with three different naming conventions and no shared join key.
Approach
A declarative spec as the single source of truth
Rather than hand-writing a page and a set of endpoints per table, a single specification declares each table's columns, types, primary key, foreign-key references, and UI flags. That specification is served verbatim by the API, and the front end renders itself from it — navigation and routes from the spec keys, the grid from the column definitions, and the correct input widget per column type.
Adding a managed table is a spec entry plus a grant. Zero front-end code, zero new endpoints.
This paid off concretely. When the warehouse's column-naming convention was changed out from under the application, the fix was a configuration edit — the front end needed no changes and no rebuild, because no column name is hardcoded anywhere in the UI source.
A generic many-to-many assignment engine
Three of the hardest screens are the same interaction: pick a parent, manage its children through a junction table. Rather than building three screens, they are declared as configuration — parent, child, junction, optional sources — and rendered by one component: a two-pane picker with search, bulk select, badge counts, and a drawer that distinguishes unassigned children from ones already assigned elsewhere.
Per-parent versioning with draft and active semantics
Field mappings needed history. The design that shipped keys versions on the individual parent row, so each data chunk has its own v1/v2 timeline independent of every other chunk. Creating a version seed-copies the prior version's rows. Active versions are enforced read-only server-side, so only drafts can be edited. Downstream reads resolve "latest active, falling back to latest" through a window function.
I shipped a per-page versioning model first. The user corrected the requirement, and it was reworked to per-parent-row the same day.
The extract
One record per provider, and for each selected chunk a golden-versus-attested pair grouped by source table. That meant solving a real join problem across catalogs: 20 of 21 golden tables join on one key while one has no ID column and joins on a national identifier; attested values anchor on a personal-info table, rank by attestation date to take each provider's latest, then join child tables through the resulting key.
Batched deliberately — one query per source table for the whole provider batch, with columns unioned across chunks, rather than N+1 per provider.
Notable engineering decisions
Killed a working application to fix the UX. Version one was a dashboard framework with an editable grid. Analysts found inline editing undiscoverable, and it would not scale to 2,500-row field lists. I diagnosed the latency root cause — the data layer opened a new warehouse connection per query and re-fetched the entire table after every save — then rewrote it as a proper SPA and API, keeping the proven SQL shapes. Two fixes did the work: one warm reused connection with a reconnect-once fallback, and writes that return the written row instead of triggering a re-fetch. On a warehouse with a hard ~1.5–2 second per-statement floor, optimistic updates hide the remainder — the drawer closes instantly and rolls back on error.
Injection-safe by construction, not by escaping. Every SQL identifier comes from the specification, never from a request. User values are bound parameters. Where table and column names genuinely do come from data — in the extract — an allowlist is built from live schema metadata and anything that does not exist is dropped.
Real-world data hygiene. Source tables carry row-version binary that is not valid UTF-8. The serializer decodes or falls back to hex rather than returning a 500. Audit columns are server-set from the authenticated identity header and never user-editable.
A permissions trap worth knowing. Schema metadata views are privilege-filtered, so a missing grant made the application silently return empty arrays with no error — and testing the SQL as myself did not reproduce it, because the app runs as a service principal. Fixed with schema-level grants and documented as a production prerequisite.
Outcome
A live internal application behind SSO, access-controlled to an admin group, that analysts use directly — no engineer in the loop for mapping changes.
- 8 governed tables, 3 many-to-many assignment surfaces, 3 field-source catalogs, and a versioned audit trail across all of them
- One-click JSON extract joining four catalogs, emitting golden versus provider-attested values side by side
- Adding a table is a ~10-line spec entry. Adding a whole new assignment surface is a configuration block.
Built solo in roughly four weeks, ~2,100 lines of first-party source across 10 files.
What transfers
- Declare the schema once and render from it. The alternative — a page per table — is where admin tools go to die.
- Version at the grain the business reasons about. Per-parent, not per-page, because that is how the mappings are actually owned.
- Make published versions immutable server-side. A read-only flag in the UI is a suggestion.
- Build identifier safety in structurally. Allowlists from live metadata beat escaping.
- Rewriting a working tool can be the right call when the interaction model is wrong. The SQL was fine; the surface was not.