Skip to content

Large-database schema payload#

Status: deferred by decision, 2026-09-20. Written now so the shape is agreed before anyone is under pressure to ship it; implement when the trigger metrics below are actually crossed, not before. Nothing in this document is built.

Why this is deferred#

GET /api/analytics/schema/{stage} returns every table, view, procedure and function of a customer stage, with columns, parameters and foreign keys, in one payload. The editors hold it in memory as a SchemaIndex and never ask again until the watermark moves.

That is the right trade for the databases the feature was built against. The development stage BenefitManager returns 99 objects and 151 foreign keys in roughly 96 KB — small enough that a second request per object would cost more than it saved, and small enough that the client-side index builds in a few milliseconds.

It stops being the right trade at some size. The question is what size, and the honest answer today is that nobody has measured a customer database large enough to find out. Building the two-level payload now would add a second endpoint, a loading state in the tree, an async columnsOf, and a cache with two levels of granularity — all to fix a problem no operator has reported.

The trigger#

Implement when any of these holds for a real customer stage, not a synthetic one:

Signal Threshold Where to read it
Payload size Content-Length over ~2 MB browser devtools, or an App Insights dependencies query on the schema request
Object count over ~1 500 objects the ObjectCount on StageSchemaRefreshed (EventId 3090)
Catalogue read ElapsedMs over ~3 000 consistently same log entry
Index build buildSchemaIndex over ~150 ms a performance.mark around the useMemo in either page

The first two are the ones that matter. Read time is a property of the stage's server and may be better fixed there; index build time has never been close.

A useful App Insights query for the first three:

traces
| where customDimensions.EventId == 3090
| extend objects = toint(customDimensions.ObjectCount),
         ms      = toint(customDimensions.ElapsedMs),
         stage   = tostring(customDimensions.StageIdentifier)
| summarize maxObjects = max(objects), p95ms = percentile(ms, 95) by stage
| order by maxObjects desc

The shape, when it is time#

Two levels, chosen by the server#

GET /schema/{stage} gains depth in the payload:

{
  "version": "W/\"...\"",
  "depth": "objects",        // or "full"
  "objects": [
    { "schema": "dbo", "name": "TblAddress", "kind": "table" }   // no columns, parameters or foreignKeys
  ]
}

The server decides, from the object count it already knows, and reports what it decided. The client must not infer depth from whether columns happens to be present — see the compatibility rule below.

A new endpoint serves one object:

GET /schema/{stage}/objects/{schema}/{name}

with the same weak ETag as the parent payload, because the same watermark governs both. Details are cached inside the stage's existing IMemoryCache entry rather than as separate entries, so one watermark change still invalidates everything at once and the per-key SemaphoreSlim still prevents a thundering herd.

What changes on the SPA#

  • columnsOf(object) may return a promise. Monaco's completion provider already accepts a Promise<CompletionList>, so the completion path needs no new machinery — but every synchronous caller does. parametersOf follows.
  • The tree loads children on first expand, with a spinner row. build-tree.ts currently materialises every column of every object it renders; that becomes a per-object fetch.
  • Hover degrades to the object summary until details arrive, rather than showing nothing.
  • relations needs foreign keys, which are per-object detail. Either the join suggestion is unavailable at depth: "objects" until both tables are loaded, or foreign keys stay in the shallow payload. Prefer the second: they are small (151 keys ≈ 18 KB for the reference database) and they are the one part of the payload whose value depends on having all of it.

Compatibility#

columns is already optional in SchemaObject, so the shallow payload does not break the current client at the type level. The rule that matters is semantic and must be written down before the first shallow payload ships:

Absent means "not loaded", never "none". A table with no columns is impossible; a procedure with no parameters is not, and is reported as [].

Any client that currently reads object.columns?.length ?? 0 as a count is wrong under two-level payloads and has to be found and fixed first.

Virtualisation#

Not part of this. The tree keeps children in the DOM only while their parent is open, which holds mounted nodes in the low hundreds regardless of database size. Revisit only if a profile shows more than ~1 000 mounted nodes, and note that a virtual list breaks the DOM-order roving tabindex, browser find, and scrollIntoView — all of which the tree's keyboard support currently relies on.

What not to do instead#

  • Do not paginate the object list. The completion provider needs the whole namespace to answer "what tables are there"; a page of it produces confidently wrong suggestions.
  • Do not drop the watermark. It is what makes the payload cheap to revalidate. A two-level payload makes it more valuable, not less.
  • Do not cache in localStorage. A megabyte-scale schema is customer database structure; it belongs in memory for the session, not on disk.