The matter boundary schema

It also aligns with two sibling proposals: Cesar's V2 to V3 Data Shaping in BigQuery and Daniele's DM-6 zod-to-dbt proposal. All three forks with those proposals are decided, including the BigQuery-to-importer handoff (see the last section).

Assumed already landed: Matter.humanReadableId is a per-firm-unique string (the human-readable ids doc), and the ImportedRecord registry exists in each service database, tracking (entityType, externalRef) → entityId per firm and source system — shared across runs, so later runs resolve what earlier runs imported (the data provenance doc). Unfamiliar entities: see the v2 and v3 dashboards.

One companion decision is asked for elsewhere, and this doc leans on it: the unit model (how the engine writes: units, buckets, pillars, incremental runs, failure containment). What is decided here is only the staged contract for the matter slice: the two tables, their fields, and the rules that shaped them.

The proposed schema

Two BigQuery tables, matters and matter_participants. The zod contract is the source of truth; the BQ tables are the same schema rendered as dbt models (snake_case names, string→STRING, date→TIMESTAMP, boolean→BOOL).

// the source system's id for a record; branded per source system
const externalRef = z.string().min(1);

// One staged row per matter to create
export const matterImport = z.object({
  // unique; becomes ImportedRecord.externalRef.
  // The v3 Matter gets a fresh mat_ id
  externalRef,
  // the firm's own matter reference (e.g. "25/16095");
  // lands directly on Matter.humanReadableId (per-firm unique)
  humanReadableId: z.string().min(1),
  title: z.string().min(1),
  // generated: OPENING | ACTIVE | COMPLETED | CANCELLED
  // every source's status vocabulary collapses to these (decided)
  matterStatus: MatterStatusSchema,
  // the source's own origin/type classification, kept as data;
  // null when the source has no such concept
  externalSource: z.string().nullable(),
  // no firm column: the run targets one firm, whose id lives in
  // run config; the importer stamps it on every row it writes
  // generated: US | GB; staged per matter (decided) — realistically
  // it always matches the firm's, but the state can technically
  // diverge, so the field stays for flexibility
  market: MarketSchema,
  // pre-populated at transform time (decided): GB matters get
  // "gb-england-wales", the only GB jurisdiction in use. Jurisdiction
  // ids are static reference slugs, not run-minted v3 ids, so the
  // no-v3-ids rule stands
  primaryJurisdictionId: JurisdictionIdSchema,
  // original source-system timestamps throughout, never import time;
  // always UTC (decided) — local-time values resolve in the transform
  createdAt: z.coerce.date(),
  lastActivityAt: z.coerce.date(),
  completedAt: z.coerce.date().nullable(),
});

// One staged row per person on the matter
export const matterParticipantImport = z.object({
  // unique; synthetic refs allowed where the source has no row,
  // namespaced so they stay stable across re-runs (case:<id>:owner)
  externalRef,
  // joins to the matter's externalRef
  matterExternalRef: externalRef,
  // resolves to a v3 Identity through the ImportedRecord registry —
  // identities are imported first, by the engine's own people units;
  // unresolvable → the unit fails
  personExternalRef: externalRef,
  // generated: CLIENT | FIRM; must agree with participantType
  side: MatterSideSchema,
  // generated: LAWYER | TEAM_MEMBER | CLIENT
  participantType: MatterParticipantTypeSchema,
  isLead: z.boolean(),
  // no firm column: the importer stamps the run's firm on
  // firm-side participants (LAWYER, TEAM_MEMBER)
  createdAt: z.coerce.date(),
});

Four rules shaped this, for every source and every future slice:

1. Target-shaped, not source-shaped. Enum columns hold v3 values. Every lossy collapse happens in the transform, where it is reviewable. A new source means a new transform, never a new contract.
2. One kind of reference. Staged rows never carry a v3 id — not a person's, not the firm's. Every reference is an externalRef, resolved through the ImportedRecord registry, because everything referenced was itself imported — by an earlier unit of the same run, or by an earlier run against the same firm (people land first, as their own foundation units). The only real id a run knows is its target firm's, held in run config: the firm is created by hand before any import happens, and the importer stamps its id onto every row it writes.
3. Timestamps are source history, in UTC. When the import happened is the ImportedRecord's business. Every staged timestamp is UTC; if a source holds local-time values, the transform resolves them before the canonical schema ever sees them.
4. Derivable means derived. Staged data carries nothing the importer can compute: v3 ids, firmOfRecordId, MatterTeamAccess rows, ImportedRecord rows, Matter.source. (One deliberate exception: side is staged even though it usually follows from participantType, as a cheap cross-check.)

One prerequisite, owned by an earlier stage — of the same run or of an earlier run against the same firm: people. Identities, firm members, and teams are imported through their own boundary tables, whose contracts follow the same rules as this one and get their own doc, so by the time matter units run, ImportedRecord already resolves every personExternalRef. A matter unit fails loudly on an unresolvable person; it never creates one.

The matter core unit

How the engine writes — unit sizing, pillar independence, failure containment, ordering, incremental runs, healing — is the unit model's decision, and this doc deliberately repeats none of it. All that belongs here is this slice's unit definition. Nothing nested is ever staged; the importer assembles the unit at read time by grouping participant rows onto their matter by the matter reference:

// Importer-internal: assembled by grouping staged rows,
// never staged itself. One unit, one transaction.
export const matterUnitImport = z
  .object({
    matter: matterImport,
    participants: z.array(matterParticipantImport).min(1),
  })
  .refine(
    hasExactlyOneLeadPerSide,
    "one lead firm-side and one lead client participant required"
  );

Participants are in because a matter with no lead lawyer breaks access rules the moment it exists; notes and files arrive later as this matter's pillars. Cross-row rules run on the assembled unit, in the refinement above: one lead per side, every participant pointing at the unit's matter, completedAt present exactly when status is COMPLETED. Duplicate identities on one matter are not the unit's problem: the source-specific dbt transform resolves them before staging (decided; see the Decided table).

One schema, enforced end to end

The contract lives in the import service because the services already know what can go in their fields; dbt can't see them. Constraints flow one way:

• Enums and id types are imported from matter-service's published zod package. When the service adds a status, the contract picks it up on a package bump. Nobody re-types an enum.
• Format rules that today live only inside service procedures (say, a string the write path always normalises) get lifted into that published package as refinements. The service's write path and this contract then import the same rule: one definition, both writers.
• The dbt surface is generated from the contract (columns, BQ types, nullability, enum values, unique keys), so every contract change shows up in dbt as a reviewable PR instead of surfacing mid-run. Decided: direct codegen with zod-to-dbt (Daniele's DM-6 experiment, PR #13454) — it walks the zod schemas and upserts the zod-owned parts of schema.yml, a scheduled action opens a PR on drift, and a stale drift PR is replaced by a fresh one. No versioned intermediate artifact.
• dbt models declare contract: {enforced: true} plus not_null, unique, and accepted_values tests. Every source's transform builds into the same contracted models, so a new source inherits the checks for free.
• A run refuses to start unless the staged tables' tests are green and they were built against the importer's contract version. The importer zod-validates every row anyway; CI catches drift at PR time, the gate catches version skew between deploys.

The first source: platform v2

Not carried from v2: ledger and fee-plan fields (money slice, later), Case.enabledModules (deprecated), companyId/teamMemberId/invitationId (no v3 equivalent; counted in the run report as unmapped).

Here is one Case making the full trip.

Stage 1: what v2 holds

// Case
{
  "id": "case_71",
  "humanReadableId": 16095,
  "title": "Purchase of 12 Acacia Avenue",
  "status": "SUBMITTED_FOR_CLOSING",
  "source": "byoc",
  "ownerId": "user_anna",
  "leadCustomerId": "person_john",
  "createdAt": "2024-03-02T09:14:00Z",
  "lastActivity": "2026-07-29T16:02:00Z",
  "completedAt": null
}

// CaseParty rows (caseId "case_71" on all three)
[
  {
    "id": "cp_301",
    "personId": "person_anna",
    "type": "SOLICITOR",
    "isLead": true,
    "status": "ASSIGNED"
  },
  {
    "id": "cp_302",
    "personId": "person_john",
    "type": "CUSTOMER",
    "isLead": true,
    "status": "ASSIGNED"
  },
  {
    "id": "cp_303",
    "personId": "person_kate",
    "type": "CUSTOMER",
    "isLead": false,
    "status": "INVITED"
  }
]

Stage 2: what lands in BigQuery

This is where every v2-ism dies:

// matters: one row
{
  "external_ref": "case_71",
  // the firm's reference, now a string
  "human_readable_id": "16095",
  "title": "Purchase of 12 Acacia Avenue",
  // SUBMITTED_FOR_CLOSING collapses to ACTIVE (decided)
  "matter_status": "ACTIVE",
  // v2's classification, kept as data
  "external_source": "byoc",
  // no firm_id: the target firm lives in run config
  // staged per matter (decided)
  "market": "GB",
  // pre-populated at transform (decided): the only GB jurisdiction
  "primary_jurisdiction_id": "gb-england-wales",
  // original v2 history
  "created_at": "2024-03-02T09:14:00Z",
  "last_activity_at": "2026-07-29T16:02:00Z",
  "completed_at": null
}

// matter_participants: two rows (matter_external_ref "case_71" and
// the original v2 created_at on both).
// cp_303 (INVITED) never becomes a participant (decided): the importer
// records it as unsupported in the ImportRun report, and a later run
// backfills it if the invitation gets accepted.
// The owner already appears as cp_301, so no synthetic lead row;
// dbt only synthesises one ("case:case_71:owner") when the owner
// has no CaseParty row.
[
  {
    "external_ref": "cp_301",
    "person_external_ref": "person_anna",
    "side": "FIRM",
    "participant_type": "LAWYER",
    "is_lead": true
  },
  {
    "external_ref": "cp_302",
    "person_external_ref": "person_john",
    "side": "CLIENT",
    "participant_type": "CLIENT",
    "is_lead": true
  }
]

Stage 3: what the importer writes

One transaction in matter-service:

// Matter
{
  // freshly minted
  "id": "mat_k4p",
  // the firm keeps the reference it has been citing for years
  "humanReadableId": "16095",
  "title": "Purchase of 12 Acacia Avenue",
  "status": "ACTIVE",
  "source": "IMPORT",
  "firmId": "firm_9y2",
  // derived: the importing firm (decided)
  "firmOfRecordId": "firm_9y2",
  "market": "GB",
  // from the staged row (decided)
  "primaryJurisdictionId": "gb-england-wales",
  "createdAt": "2024-03-02T09:14:00Z",
  "lastActivityAt": "2026-07-29T16:02:00Z",
  "completedAt": null
}

// MatterParticipant rows (matterId "mat_k4p", original created_at).
// identityId resolved through ImportedRecord: the identities were
// imported by the foundation units of the same run. firmId on the
// firm-side row is stamped from run config
[
  {
    "id": "matpar_c2",
    "identityId": "idn_anna",
    "side": "FIRM",
    "participantType": "LAWYER",
    "isLead": true,
    "firmId": "firm_9y2"
  },
  {
    "id": "matpar_c3",
    "identityId": "idn_john",
    "side": "CLIENT",
    "participantType": "CLIENT",
    "isLead": true,
    "firmId": null
  }
]

// MatterTeamAccess: derived from the lead lawyer, replicating what
// createMatter.ts does on the organic path
{ "id": "mta_88", "matterId": "mat_k4p", "teamId": "team_5rw" }

// ImportedRecord: one per domain row, same transaction.
// importRunId is an opaque, self-describing string; the run table
// lives in the engine's own database, so there is no FK
[
  {
    "entityType": "Matter",
    "entityId": "mat_k4p",
    "externalRef": "case_71",
    "importRunId": "imprun_platform-v2_firm_9y2_1_20260815"
  },
  {
    "entityType": "MatterParticipant",
    "entityId": "matpar_c2",
    "externalRef": "cp_301",
    "importRunId": "imprun_platform-v2_firm_9y2_1_20260815"
  },
  {
    "entityType": "MatterParticipant",
    "entityId": "matpar_c3",
    "externalRef": "cp_302",
    "importRunId": "imprun_platform-v2_firm_9y2_1_20260815"
  }
]

That importRunId points back to the run record in the engine's database (see the import runs doc). The report object below is illustrative only: the report format needs its own design pass off the back of the validation discussion (agreed in review, not blocking this contract):

// ImportRun (engine database)
{
  "id": "imprun_platform-v2_firm_9y2_1_20260815",
  "sourceSystem": "platform-v2",
  "firmId": "firm_9y2",
  "snapshotRef": "migration_firm_9y2_20260815",
  "dryRun": false,
  "status": "COMPLETED",
  "report": {
    "matter-service": {
      "matters": 1,
      "matterParticipants": 2,
      "unmapped": { "invitedParties": 1 }
    }
  },
  "startedAt": "2026-08-15T07:00:00Z",
  "completedAt": "2026-08-15T07:41:12Z"
}

Decided

One running log to keep, from review: primaryJurisdictionId is the first of the marketplace-shaped domain concepts an import has to work around. Each one we meet gets collected as a candidate for future domain cleanup rather than silently absorbed.

Decided: handoff from BigQuery to the importer

The last fork went to file exports. The staged tables export from BigQuery as one sorted UTF-8 JSONL file per entity on S3 (matters.jsonl, matter_participants.jsonl, …) — roots sorted by primary key, children by (parent ref, primary key) — and the import service streams them from there; it never queries BigQuery directly. Daniele confirmed the choice in review, and infra provides an S3 regulated bucket.

The two mechanics that had to be worked out before deciding are worked out, with the reasoning in two companion walkthroughs:

• Daniele's hierarchical unit load walk — the dump format and the loading algorithm: one file per entity (not per unit), sorted so the loader opens one S3 stream per entity and assembles each unit — a matter plus its participants — with a single forward scan per file, resuming broken streams via byte-range reads.
• The unit load runtime walkthrough — a simulator of that loading walk at runtime, which is what showed the approach holds up.

Zod validates each JSONL line as it streams, so the contract above is unchanged; the files are just the transport.