Units, buckets, and pillars

This doc asks for one decision.

The migration engine imports data as semantic units (firm-level units, matter buckets, and per-matter pillars) rather than loading staged tables entity by entity. It is deliberately schema-free: which staged tables exist and what columns they carry is separate work that can evolve without reopening this decision. Here we only care about how data lands, and why.

Companion docs: data provenance (the ImportedRecord registry this model leans on), import runs (the run record and the firm-exists-first rule), and the matter boundary schema (the staged-table contract for the first slice).

TL;DR. The atom is a unit: the smallest set of staged rows that must land atomically for v3 to be valid at every commit point. Units form a hierarchy that mirrors meaning, not table layout: one unit per person, team, and contact at firm level, then one bucket per matter holding a core unit (the matter plus its participants) and independent per-kind pillars (its files, its notes, its tasks). Any pillar can fail without touching its siblings, the run report is a grid of matters × pillars, and runs are scoped, incremental, and idempotent against a firm that always exists before any import.

Two facts about the setup are all the context needed:

Staged data is flat tables in BigQuery, one per kind of record, shaped by what v3 needs rather than by any source system, and carrying no v3 ids. Rows reference each other by externalRef, the source system's identifier.
The ImportedRecord registry is a table in each receiving service's database mapping (sourceSystem, entityType, externalRef) to the v3 id, per firm, written in the same transaction as the record it describes. It is how anything imported earlier gets found later, how re-runs stay idempotent (a unique constraint makes them no-ops), and how every imported v3 row traces back to a run.

The firm itself is created by hand before any import and no run ever creates or mutates it; run config carries its id. That rule is what makes everything below incremental.

The problem the model has to solve

A firm migration writes tens of thousands of rows across half a dozen v3 services: people, matters, participants, contacts, files with real blobs behind them, notes, tasks. Three properties of that workload shape everything:

Validity is multi-row. A matter without its participants is not a smaller matter; it is an invalid one, because access control breaks the moment it exists. Some sets of rows are only meaningful together.
Failure is certain. At this volume something always goes wrong: a malformed date, a missing person, a blob that fails its checksum. The design question is what one bad record costs, and what state the platform is in afterwards.
There is no big transaction to hide behind. The writes span services and databases, so the engine chooses its own commit points, and every commit point is a state the platform might be left in.

So the question is: what is the right size of atom? Too big, and one bad row poisons everything committed around it. Too small, and validity shatters across commits while the run dissolves into tens of thousands of transactions nobody can reason about.

The model

The atom is a unit: the smallest set of staged rows that must land atomically for v3 to be valid at every commit point. One unit, one transaction, one independent success or failure. Units are grouped into a hierarchy that mirrors what the data means, not which table it sits in:

flowchart TD
    firm["Firm: created by hand, before any run.<br/>Run config holds its id; no run ever creates or mutates it"]

    subgraph run["an import run: any scoped subset of this structure"]
        subgraph firmLevel["firm-level units, one per real-world record"]
            personU["person unit × N<br/>identity + memberships"]
            teamU["team unit × N"]
            contactU["contact unit × N<br/>contact + its methods + addresses"]
        end

        subgraph matterBucket["matter bucket × N, one per matter"]
            coreU["matter core unit<br/>the matter + its participants"]
            p1["files pillar"]
            p2["notes pillar"]
            p3["tasks pillar"]
            p4["comms pillar"]
            p5["…more pillars as<br/>scope grows"]
        end
    end

    firm --> firmLevel
    firmLevel --> coreU
    coreU --> p1
    coreU --> p2
    coreU --> p3
    coreU --> p4
    coreU --> p5

Three kinds of node:

Firm-level units, one per record. A person unit is the identity plus its firm membership plus its team memberships; splitting those apart would create half-registered people. Teams and address-book contacts work the same way. These are the records everything downstream points at, so they land first.
The matter core unit: the matter plus all of its participants. That grouping is the validity invariant, not taste. Rules like "one lead per side" can only be checked on the assembled unit, and a matter committed without its participants is broken from its first instant.
Pillars: per matter, per kind of content. All of matter A's notes are one unit, its files another, its tasks a third. A pillar is content the matter can validly exist without (organic matters have no notes on day one), so a pillar failure must not take the matter down. Pillars of the same matter are fully independent.

Dependency order falls out of the hierarchy: firm-level units before matter cores, cores before their own pillars, and nothing else ordered at all. Matter B's pillars never wait for matter A's.

Why pillar granularity, precisely

The pillar is the deliberate middle of a spectrum:

One unit per row maximises isolation but protects nothing, because notes have no cross-row invariants to protect. The extra transactions buy a transaction flood and a report with fifty thousand lines nobody reads.
One unit per matter, content included reads nicely ("the whole case lands or it doesn't") but one corrupt note, or one blob failing its checksum mid-copy, rolls back an entire case including everything that was fine. Blob copies inside a transaction also stretch it to minutes.
Pillar-per-matter is where failure economics and reporting meet: a failure costs one kind of content on one matter, and the run report becomes a grid of matters × pillars, every cell independently done, failed, or retryable.

	unit = row	unit = pillar (chosen)	unit = whole matter
Blast radius of one bad note	one note	that matter's notes	the whole matter
Transactions for a 1,000-matter firm	~100k	~7k	~1k
Multi-row invariants	nowhere to check them	checked at the unit where they live	possible but entangled with content
Report legibility	noise	a matters × pillars grid	"failed" hides what failed
Retry cost	trivial but endless	one cell	re-copy every blob on the matter

What failure looks like, by design

Every unit failure is contained, counted, and reported. The run always finishes and always produces the grid. Blast radius, level by level:

Failing unit	Blast radius
person unit	that person, plus any matter core whose participants reference them. A matter without its lead lawyer fails its own validation, before anything is written
team / contact unit	that record, plus the specific downstream cells that reference it
matter core unit	that matter's bucket; its pillars never run
a pillar	that pillar of that matter; sibling pillars and the core stand

Nothing in that table fails the run. The run is an orchestration, not a transaction: it lands everything that can land and says precisely what could not.

run report (illustrative)

matter        core   files   notes   tasks   comms
SMITH-2019     ✓       ✓       ✓       ✓      ✓
JONES-2021     ✓       ✗       ✓       ✓      ✓     ← blob checksum, 1 file
KHAN-2023      ✗       ·       ·       ·      ·     ← unresolvable person ref
1,204 others   ✓       ✓       ✓       ✓      ✓

Healing is the same operation as importing. The registry's unique constraint makes every unit idempotent, so "fix the staged data and run again" is always safe: units that already landed no-op, and the failed cells run for real. Healing JONES-2021's files never touches SMITH-2019, never re-copies landed blobs, and never asks for a fresh firm.

One failure mode is invisible by construction and needs its own guard (raised by Jaime in review): a staged row that never assembles into any unit. Assembly must be provably exhaustive, so the run reconciles at the end — every imported v3 record tracks which canonical rows fed it (model names plus row hashes), and staged rows consumed by no unit are reported as dangling instead of silently skipped.

Runs are incremental

A run executes any scoped subset of the hierarchy against the firm. This works because every unit resolves its references through the registry rather than through in-memory state, so it makes no difference whether the thing referenced landed thirty seconds ago or three weeks ago.

flowchart LR
    provision["firm created<br/>by hand"]
    run1["run 1<br/>all people + teams"]
    run2["run 2<br/>matters, batch 1"]
    run3["run 3<br/>matters, batch 2"]
    run4["run 4<br/>re-run failed cells<br/>from runs 2 and 3"]
    run5["run 5<br/>comms pillars only,<br/>all matters"]

    provision --> run1 --> run2 --> run3 --> run4 --> run5

This buys the operational shapes a real migration needs, none of which an everything-or-nothing engine can do:

Rehearse cheaply. Import the people, check them, and only then commit to the matters.
Batch by risk. Closed matters first, live matters in a careful window.
Add scope later. When the comms pillar ships as a capability, run it across a firm that migrated months ago. The registry still resolves every matter reference.
Heal surgically. Run 4 above is nothing special, just a run whose scope is the failed cells.

A run's precondition is only that its units can resolve their references. A matters-only run against a firm with no imported people fails loudly at validation, before any writes.

Approaches considered and rejected

A. Entity-by-entity table loading

The default shape everyone reaches for first: load the matters table, then participants, then notes, then files, each table across the whole breadth of the migration, like a warehouse backfill.

flowchart TD
    subgraph tableWise["table-by-table: failure mid-run"]
        t1["matters: 1,207 / 1,207 ✓"]
        t2["participants: 1,207 / 1,207 ✓"]
        t3["notes: 604 / 8,000 ✗ crashed"]
        t4["files: 0, never ran"]
        t1 --> t2 --> t3 --> t4
    end

    subgraph unitWise["unit-based: same failure"]
        u1["512 matters: fully live ✓"]
        u2["1 matter: notes pillar failed ✗"]
        u3["694 matters: not started"]
    end

Why it loses:

Its partial states are the worst available. A crash mid-notes leaves every matter incomplete in the same way: nothing finished, nothing cleanly absent, every case in a state no organic matter has ever been in. The unit model's partial state after the same crash is "512 matters fully live, the rest untouched".
Multi-row validity has nowhere to live. "One lead per side" is a rule about a matter and its participants together. Table-wise loading commits matters before their participants exist, so either the rule goes unenforced at write time, or the platform has to learn a temporary "invalid but pending" limbo.
It reports the wrong thing. "Row 604 of the notes table failed" answers no operational question. "Matter JONES-2021's notes failed" is a sentence a support person can act on.
It needs the same machinery anyway. References must still resolve, retries must still be idempotent, reports must still exist. Table-wise loading pays for the registry and gets none of the semantic payoff.

Its one genuine advantage, bulk-insert throughput per table, is not the bottleneck. Migrations are constrained by correctness and operability, not insert speed.

B. One transaction per firm

Atomicity at the migration level: the whole firm lands or nothing does. Rejected on three grounds:

It is physically unavailable. The writes span services and databases; no such transaction exists to run.
Its cost curve is wrong. One malformed record discovered at hour six discards hours one through five.
It forbids incremental operation. No people-first rehearsal, no batching, no adding scope later.

Even simulated with compensating teardown, it turns every small failure into the maximum event.

C. Matter mega-units (no pillars)

The tempting one. It keeps the semantic story ("a case lands whole") but couples the durable, cheap parts of a matter to its riskiest part, blob transfer, and makes the most common failure (one bad file) cost the most valuable thing (the whole case). The pillar split exists so the risky parts can fail without touching the sound parts.

D. Row-level units everywhere

Maximum granularity, minimum semantics. Where multi-row invariants exist (matter plus participants) it is simply incorrect, because the invariant has no unit to be checked on. Where they don't (notes), it buys nothing that pillars don't already give, at over ten times the transaction count.

Costs accepted

The model is not free:

The engine owns assembling. Staged data is flat; units are assembled at read time by grouping rows on their references — assembling, in the loader's vocabulary (renamed from "grouping" per review). That is real engine code, per unit type, that table-wise loading would not need. The canonical layer stays flat on purpose: nesting children into staged rows would make constructing a valid unit synonymous with assembly, and assembly is centralised in the import service alone.
Dependency ordering exists. Firm-level before cores, cores before pillars. A shallow DAG, but orchestration state the engine must track per run.
Cross-service resolution must be designed. Registry tables live per service, so a matter unit resolving a person reference needs a mapping that lives in the identity service's database. Owning-service lookups versus a central read copy must be settled before the run loop is built.
Pillar sizing is a judgement call, and revisitable. If a matter arrives with 40,000 filed emails, one comms pillar may prove too coarse and need internal chunking. The model tolerates that: a pillar can split into smaller units later without changing anything above it.

Ask: approve or veto one decision

That the migration engine adopts, as settled design:

Unit = smallest atomically-valid set of rows; one unit, one transaction. Unit boundaries are defined by v3's validity invariants, not by table layout.
The hierarchy: firm-level units, then per-matter buckets of a core plus independent pillars, with dependency order firm-level → core → pillars and no ordering between siblings.
Runs are scoped and incremental: any subset of the hierarchy, any number of runs per firm, resolution across runs through the registry, and the firm itself always created by hand outside the engine.
Failure is contained and reported at cell granularity (matter × pillar), and healing is re-running failed cells, idempotent through the registry.

Everything schema-shaped, meaning which staged tables exist and what each pillar contains at each stage of delivery, is defined separately and evolves without touching this decision.