Data provenance for imported records

Ask: approve or veto one decision

Import provenance lives in a sidecar ImportedRecord table, one per service database, written in the same transaction as the rows it describes. No provenance columns on any domain model. Fast path: read the TL;DR and the options table.

The design is worked through on matter data because that is the first slice the migration engine builds, but nothing in it is matter-specific. Companion docs: human-readable ids and import runs.

TL;DR. Every row the migration engine writes gets a companion ImportedRecord row in the same database and the same transaction: which import run wrote it, which table, which row, which source record. That one table is four things at once: the engine's idempotency key, its teardown manifest, its source-to-v3 id mapping, and the answer to "was this row imported?". Domain schemas stay untouched.

What provenance has to do

Four consumers, all needed:

The engine. Per-row idempotency on re-runs, teardown for the rebuild cycle (delete exactly what a run wrote), and the source-to-v3 id mapping the transform stage needs anyway. A v2 note references a v2 file id; writing the v3 note requires knowing what that file became. This mapping must exist for the engine to function at all, and a mapping keyed by run is provenance. That observation drives the whole design.
Support. Given a v3 row, which source record produced it, and the reverse.
The product. An occasional "is this imported?" check. No current feature reads provenance on a render path.
Verification. After a run, reconcile what the engine believes it wrote against what each database actually holds.

The design

One identical table per service database:

/// One row per domain row the migration engine wrote in this database.
/// Owned by the migration engine; organic write paths never touch it.
/// prisma-lint-ignore-model require-field deletedAt
model ImportedRecord {
  id          String   @id @default(dbgenerated("concat('imp_', nanoid())"))
  /// Insertion-order witness; timestamps cannot order concurrent inserts.
  seq         BigInt   @default(autoincrement())
  importRunId String   // opaque engine run id, encodes source system + firm + run
  firmId      String   // denormalised from the run, so firm-wide queries need no run-id list
  entityType  String   // Prisma model name, e.g. "MatterNote"
  entityId    String   @unique // the v3 row's primary key; one imported entity, one record, ever
  externalRef String?  // the source system's id, e.g. a v2 `cno_` id
  createdAt   DateTime @default(now())

  @@unique([firmId, entityType, externalRef])
  @@index([importRunId])
  @@index([firmId])
  @@index([externalRef])
}

How it serves each consumer:

Idempotency: (firmId, entityType, externalRef) unique is the per-row check, the same chunked, dry-run, per-row shape as backfillMatterTeamAccess.ts.
Teardown: select a run's entries, delete domain rows in reverse seq order, delete the entries. The registry is a manifest of exactly what a run wrote; nothing is reconstructed from circumstantial evidence.
Id mapping: (entityType, externalRef) → entityId.
Provenance checks: a unique-index lookup, or an in-database join for set queries ("all imported notes").

Ordering. Insert order is a topological sort of the FK dependency graph, including the cross-service refs no database enforces. Delete order comes free: seq records the actual insertion order, and a child can only be inserted after its parent, so deleting in reverse seq never breaks an FK. (If a cyclic ref is ever broken by inserting null and patching after, teardown nulls those refs first.)

Re-runs. The (firmId, entityType, externalRef) uniqueness is not scoped to the run, so a later run cannot re-insert the same source entities as new rows. This is safe because re-runs are teardown-and-rebuild. Additive re-runs are a future iteration.

Covering a new entity type costs zero schema change, just registry entries. Covering a new service costs one identical migration. Same-transaction writes mean a crash cannot leave a domain row the engine has no record of.

The importRunId is minted by the engine and is self-describing (imprun_<sourceSystem>_<firmId>_<seq>_<startedAt>). The run's own record, its status, timestamps, dry-run flag, report, and metadata such as the import method and pipeline version, lives with the engine, not on ImportedRecord; see the import runs doc. firmId is denormalised onto the registry so "everything imported for this firm, across every run" is one indexed query.

Options considered

Option	Why it was attractive	Why it lost
Inline columns on every table the engine writes (`importRunId`/`importExternalRef`)	Row-level truth readable off the row, no joins ever	A schema read of all 17 services put this at ~45 tables, growing with every data type the import takes on. A migration in nearly every service, a convention with permanent enforcement burden, and it duplicates a mapping the engine keeps anyway
Columns on `Matter` only, children inferred via their matter link	Matters anchor almost everything; smallest possible footprint	Unsound twice over. Imported matters accumulate on-platform children the day after cutover, so "parent imported" says nothing about a given child row. And most matter links are not real links: nullable soft strings across database boundaries, a serialised scope string in content-service, an unenforced id convention in messaging-service
Columns on `Matter` plus timestamp inference (children created before the run's completion are imported)	No child columns; timestamps already exist	Couples correctness to the still-open "preserve source timestamps" transform decision; engine-written side-effect rows (`MatterTeamAccess`) have no source timestamp and sit exactly on the boundary; and it is an inference recomputed at query time by every consumer, not a recorded fact. Kept as a verification cross-check only
Registry in the engine's own database (central, not per-service)	One place to look; zero footprint in v3	No transactional atomicity with the writes it describes: a crash can leave rows the engine has no record of. Provenance should also outlive the engine; the target databases stay self-describing
Extend existing `source` enums / the ledger provenance pair	Merged precedent exists (`MatterFileSource.IMPORT`; `(provenanceType, provenanceId)` on ledger transactions)	Different axes. Business origin ("how did this entity come about, domain-wise") and write-path provenance ("which producer wrote this row") both stay meaningful for imported rows, so overloading either loses information. Where they exist the engine participates (`MatterFile.source = IMPORT`; a `MIGRATION_IMPORT` provenance type for ledger transactions), but neither generalises

One follow-up the design allows but does not need: if a feature ever wants provenance on a hot path (an "imported" badge on every matter list row, say), denormalising importRunId onto that one model is a purely additive change backfilled from the registry.

Worked example: importing one matter

One v2 case with one note and one attached file. Notes involve two databases because note bodies live in content-service (createMatterNote.ts creates a Content row and stores its id on the MatterNote); the engine mirrors that shape.

Ids in this example: case_1, casenote_1 and casefile_1 are the v2 rows' primary keys (the source ids the registry stores as externalRef). mat_1, mnote_1, file_1 and content_1 are the new v3 rows.

flowchart TD
    subgraph engine["Migration engine (per chunk)"]
        T["Transform v2 rows,<br/>resolve refs via registry lookups"]
    end
    subgraph ms["matter-service DB (one tx)"]
        M["Matter, MatterNote, File"]
        R1["ImportedRecord entries"]
    end
    subgraph cs["content-service DB (one tx)"]
        C["Content (note body)"]
        R2["ImportedRecord entries"]
    end
    T --> ms
    T --> cs
    M --- R1
    C --- R2

matter-service writes, registry rows in the same transaction:

Write	Registry entry
`Matter` `mat_1` from v2 case `case_1`	`("Matter", mat_1, case_1)`
`MatterNote` `mnote_1` from v2 note `casenote_1`, its `contentId` pointing at `content_1` below	`("MatterNote", mnote_1, casenote_1)`
`File` + `MatterFile` from v2 file `casefile_1`, with `source = IMPORT`	one entry each, `externalRef = casefile_1`

content-service writes:

Write	Registry entry
`Content` `content_1` holding the note's body	`("Content", content_1, casenote_1)`

Both databases store casenote_1 as the externalRef for their half of the note, which is what ties the two halves together. It is also why external-ref uniqueness is keyed per entityType: File and MatterFile above share casefile_1 in the same database. Inserts follow FK order: content_1 before the MatterNote that points at it, mat_1 before everything under it. Teardown reads each registry in reverse seq order. Verification confirms every registry entry's row exists, and flags rows inside imported matters whose timestamps disagree with the registry.

Appendix

The table's shape borrows the @lawhive/framework infra-table conventions, since they already answer the small design questions well: the entityType/entityId naming (as on AuditLog), the prefixed DB-generated id, the seq insertion-order column, and the usual infra-table exemptions (no soft delete, no self-audit).

One invariant, stated independently of how the engine will actually write to the databases: a domain row and its registry entry commit in the same transaction, whatever the write path turns out to be. Whether that write path is application-level (migration endpoints) or SQL-level is deliberately out of scope here: this design is agnostic to it, and the mechanism is worked through in the import service doc.

Related engine-design problems this doc surfaces and does not solve: synthetic external-id namespacing (Nylas, Twilio, Sendbird), the per-firm migration-system identity for non-null createdBy columns, and malware-scan state for imported files.