decompose: load all env/node dumps, introspect and assign layout, emit layered schema (shared base + env patches)

[orian]

Problem

Turning live cluster schemas into a layered HCL decomposition is entirely manual today, and it is the dominant cost of the whole declarative-schema effort.
The inputs exist (per-node dumps for prod-us, prod-eu, dev, plus the local dev stack), and the target shape is well understood (a shared base layer per object, plus per-env patch layers), but every step between them is hand archaeology:

• Cross-env inventory and diffing is done object-by-object with ad-hoc scripts over dump files (<env>/<node>-<role>.hcl).
• Deciding what is env-uniform vs per-env vs a runtime-only overlay (materialized-column tails) is a per-block manual classification; a recent pass over one role's overrides classified ~23k lines by hand.
• The emitted layers are hand-authored. The largest single overlay patch (a ~5.7k-line materialized-column tail) was written by hand; there is no emitter that turns "this diff" into "these patch blocks with correct after anchors".
• Mistakes are silent: a patch emitted without position anchors reorders the composed table; interleaved column runs (migration-managed columns physically positioned between runtime columns) make a clean split impossible and are only discovered when goldens mismatch.
• Nothing verifies the decomposition round-trips until the very end (compose == dump), so errors surface late.

Proposal: a decompose workflow (load → list → diff → assign → emit)

A new hclexp mode (subcommand set, or a long-running session exposed as an API/MCP server so a human or agent can drive it interactively) that owns the whole pipeline:

1. Load.
Ingest all node dumps for selected envs (the dump-repo layout: per-node files carrying hostClusterRole macros) and, optionally, an existing layer tree.
Build an inventory keyed by (env, cluster/role, node, object).
Respect -exclude semantics on load (#85 is the same need for diff).

2. List / inspect.
Query the inventory: all objects on all nodes, one env, one role, one object across everything.
Show a presence matrix (which envs/nodes have the object) and intra-env agreement (do replica pairs match?).

3. Diff.
Object-level diff between any selected (env, node) pairs, at column/index/setting/engine granularity, with each difference classified: column set, column order, codec/type, engine args, ZK path, settings, query text.

4. Assign (the decomposition decision, kept in workspace state).
Default heuristic: content identical everywhere → the object's base goes to a shared layer; per-env deltas → per-env patch layers.
The user (or agent, via the API/MCP surface) can then override assignments before anything is written:

• move an object between layers;
• split one object's columns across layers (e.g. a runtime materialized-column tail into a separate overlay layer — name prefix is NOT a reliable classifier, so this must be a user decision, not a heuristic);
• route layers to different output roots (we maintain a public tree and a private overlay tree in separate repos — the assignment must express "this layer emits into root A, that one into root B");
• mark objects as excluded/ignored.
  The workspace state (inventory + assignments) persists across the session so large decompositions can be done incrementally.

5. Emit.
Generate the layer files (tables.hcl / patches.hcl per layer), a manifest skeleton (env blocks composing the layers in order), and goldens.
Requirements learned from doing this by hand:

• Round-trip gate built in: after emit, compose(layers) == dump per (env, role) must be checked by the tool itself, including physical column order (semantic diff is order-blind; goldens are not).
• Correct after anchors on emitted patch columns, anchored only on columns already composed at that point in the stack.
• Detect impossible splits: when an assignment requires repositioning (interleaved runs where base columns sit between overlay columns), fail loudly with the exact columns, instead of emitting a reordering patch (relates to sqlgen: ADD COLUMN never emits FIRST/AFTER — declared column position ignored #88 — declared position is ignored in sqlgen too).
• Duplicate-free by construction: the emitted decomposition must satisfy the defined-once counting rules (base declared once; everything else patches/extends).
• Deterministic and idempotent: re-running against fresh dumps regenerates only the layers whose content changed, so the tool replaces today's reseed-from-dump shell logic.
• (Later / optional) abstract + extend emission for structure-sharing families, e.g. an events base abstract with per-cluster refinements.

Why API/MCP and not only CLI

The assignment step is inherently interactive: hundreds of objects, a handful of genuinely ambiguous ones, and the person deciding is usually working through an agent session.
An MCP/API surface (list/diff/assign/emit as operations over a workspace) lets the human introspect and manipulate the layout incrementally and lets agents automate the mechanical 95% while surfacing only the ambiguous cases.
A batch CLI over an assignment file would work as a first cut; the workspace/API is the target.

Non-goals

Applying anything to live clusters — that stays with plan/chschema.
This tool only reads dumps and writes layer files.

Acceptance sketch

• Given the current dump repo (3 cloud envs + local dumps), decompose produces a layered tree whose per-(env, role) composition is byte-order-identical to each dump (modulo excludes), with shared-base/env-patch assignment matching the default heuristic, in one run.
• Splitting a table's runtime column tail into a second output root reproduces our existing public/private split for the events family, byte-identically.
• An assignment that would require column repositioning is rejected with a precise error naming the interleaved runs.