Companion docs: data provenance (the per-service ImportedRecord registry this service writes), import runs (the ImportRun record, which lives in this service's database) and human-readable ids.
Ask: approve or veto one decision
The final stage of the migration pipeline is a new internal service inside platform-v3: the firm import service. It pulls cleaned data from BigQuery, validates it at a zod boundary built from the services' own schemas, and writes directly into the service databases via Prisma in dependency order, bypassing the services' APIs. Fast path: read the TL;DR and the options table.
TL;DR. Stages 1 to 3 of the pipeline turn an arbitrary source dump into two clean halves: entity data in BigQuery conforming to an agreed target schema, and physical files in a staging S3 bucket, malware-scanned by GuardDuty. Stage 4 is the only stage that has to know what platform-v3 actually requires, and that knowledge already lives in the monorepo: every service publishes its zod schemas, its Prisma client and its branded id types, and the platform already has code that writes across all the service databases in a known dependency order. So the importer lives where the schemas live. Given a firm id and a staging snapshot, it pulls the BigQuery rows, gates them through the boundary schema, and inserts them into the service databases in FK order, writing
ImportedRecord rows in the same transactions. The boundary schema is the same contract the BigQuery target schema is built from. One contract, enforced at the gate.The pipeline this is the last stage of
For context; only stage 4 is being decided here.
flowchart LR
subgraph s1["Stage 1 · Source dump"]
D["Everything the firm's old system<br/>can export, in one archive"]
end
subgraph s2["Stage 2 · Split"]
SD["Database exports<br/>SQL / JSON / CSV"]
SF["Physical files<br/>PDFs, docx, emails"]
end
subgraph s3["Stage 3 · Transform & stage"]
BQ["BigQuery<br/>cleaned data in the<br/>target schema, via dbt"]
ST["Staging S3 bucket<br/>every object<br/>GuardDuty-scanned"]
end
subgraph s4["Stage 4 · Import · this doc"]
FIS["Firm import service<br/>validate against the contract,<br/>write in dependency order"]
end
DB[("Service databases<br/>+ ImportedRecord registry")]
S3["Platform S3 buckets<br/>+ file rows"]
D --> SD
D --> SF
SD -- "dbt transforms" --> BQ
SF -- "upload + scan" --> ST
BQ -- "pull rows" --> FIS
ST -- "check scan tag,<br/>server-side copy" --> FIS
FIS --> DB
FIS --> S3
The two halves travel separately from stage 2 on and meet again at the import service: entity data down the top lane, physical files down the bottom, both gated before anything touches the platform.
- Source dump. Whatever the firm's outgoing system produces: database exports (SQL, JSON, CSV) plus an archive of the physical files (PDFs, docx, emails). Platform-v2 first, other systems later.
- Split. The dump separates into structured data exports and physical files.
- Transform and stage. dbt transforms the structured data into a cleaned dataset in BigQuery that conforms to the target schema: the agreed representation of the entities we support. Files go to a per-firm staging S3 bucket with GuardDuty malware protection on, so every object gets a scan-status tag on arrival. Stages 1 to 3 sit with the Data team.
- Import. This doc. The staged data and files become real rows and objects inside platform-v3.
After stage 3 we hold clean data and clean files. Stage 4 is where they cross into the platform, and it is the last place a bad record can be rejected before it lives in a production database. So this is where we gate hard.
What the service is
A services/-shaped package in platform-v3, cloned from an existing service like every other one. Three properties define it:
Fully internal. No gateway route, no public surface; reachable only with the internal service JWT, the same way our existing internal-only services work. Its trigger endpoint starts a run for one firm: source system, snapshot reference, dry-run flag, optional teardown-first. The firm must already exist; a run never creates one (see the import runs doc).
A worker, not a request handler, and deliberately not on Inngest. A firm import is many steps, and the shared Inngest system is sized for the platform's organic workload. Running the import there either strains what production depends on or gets throttled until the import crawls. So the service runs its own loop:
- Pull records from BigQuery one unit at a time.
- Write each unit in a transaction together with its
ImportedRecordrows. - Persist a cursor in its own database, so a crashed or stopped run resumes where it left off.
- Retry freely: the registry's unique keys make every write idempotent, so re-processing a unit that already landed is a no-op.
The platform's existing backfill jobs already established the operational shape (dry runs, run labels, a structured report, resumable job state in the service's own database). This keeps all of that, without Inngest underneath.
Owner of the engine's runtime state. The ImportRun table from the import runs doc lives in this service's database, next to the cursors.
The contract, enforced at the gate
The boundary between "data we were given" and "data in the platform" is a zod schema per entity type, composed from pieces the repo already maintains:
- Model shapes from each service's published schema package (every service exports its generated zod model schemas).
- Branded id types from the shared id-schemas package, so a matter id is structurally a
mat_id, not a string that happens to look like one. - Semantic refinements Prisma cannot express: an email column parses as an email, an enum value is one v3 accepts, a referenced file resolves to a staged object whose scan came back clean.
This contract is also what the BigQuery target schema is derived from. The Data team transforms into it, the import service validates against it, and adding a new entity type to the migration is one contract addition that both sides build to.
That is why this stage does not belong in an upstream dbt or DAG job. The contract's source of truth is TypeScript in platform-v3, and only code living in the monorepo can import it rather than re-transcribe it.
One trap to close first: the generated zod schemas are committed and have drifted from the Prisma schemas in places (identity-service's firm schema is missing columns that exist in the database). Regenerating them, ideally in CI, is a prerequisite; validating against a stale schema silently drops columns.
Writing directly to the databases
The importer connects to each service's Postgres through that service's own Prisma client and inserts in dependency order. Not through tRPC, not through the framework's event effects. This is the most contentious part of the design, so here is the reasoning.
The precedent exists and works. The seeding package (packages/db-seeding) already opens a Prisma client per service and writes across ~14 service databases in an explicit dependency order, with a matching child-first delete order. The importer is that mechanism made production-grade: same clients, same ordering knowledge, plus transactions, idempotency and provenance.
The APIs are shaped for organic writes, not bulk load. Service procedures validate one request, authorise one actor, and fan out side effects per write: notifications, events, search hooks. An import is many rows that must not notify anyone. Pushing them through request-shaped endpoints means per-row overhead plus suppression flags threaded through every organic write path.
The bypass has to be inventoried, not ignored. Skipping the services' write paths skips notifications (correct for an import), skips the Inngest event fan-out, and skips event-store rows where a service keeps them. Search stays consistent for free, because CDC picks rows up from Postgres regardless of who wrote them. The event-store gap needs a position per service: either the importer writes synthetic events alongside rows, or imported aggregates are documented as having no pre-import event history. Either way, the ImportedRecord registry marks every row the importer wrote.
Ids are minted app-side. The databases normally generate ids on insert, but the importer must know ids up front to wire cross-service references (a note's content id, a matter's firm id: plain strings across database boundaries). There is already service code that mints ids app-side with the same alphabet; the importer does the same. The id map it builds is exactly the registry's (entityType, externalRef) → entityId mapping.
Ordering is domain knowledge, and this service is its home. Content before the notes that point at it, matters before everything under them, identity before all of it. The seeding package's write order is the starting spelling; the importer owns the production version and extends it per entity type.
The standing rule that production database access is read-only for humans stays true. The importer is not a human with a psql prompt. It is a deployed service whose write credentials exist for this purpose, scoped per target database, and every row it writes is registered.
Files
The staging bucket gives us scanned objects. The importer turns them into platform files without re-taking the upload path.
Gate on the scan. Before importing any record that references a file, check the staged object's scan tag. Anything not clean blocks that record and lands in the run report. Nothing unscanned or dirty crosses over.
Copy, then create rows in the final state. S3 server-side copy from staging into the target bucket, then create the file rows directly in their terminal, ready state, rather than replaying the upload / notification / re-scan choreography for an object already scanned in staging. If we decide every object in a platform bucket must have been scanned in that bucket, the fallback is letting the normal bucket notifications drive file status. That is a per-bucket wiring question, not a design change.
Two file models exist, and each slice has to pick knowingly: the content-service file model with its own bucket, and the older matter-service file model with its own. For matter documents the importer writes whichever the domain model says a matter file is at import time. This is one place the document domain reshape and the migration touch, and the files-slice contract should be agreed against the target model, not the legacy one.
Options considered
| Option | Why it was attractive | Why it lost |
|---|---|---|
| Import from the data side (a dbt/DAG job upstream pushes into the service databases) | The pipeline already lives there; no new v3 service | The contract's source of truth is TypeScript in the monorepo: zod schemas, branded ids, Prisma clients, FK-order knowledge. An upstream job re-transcribes all of it and drifts from day one. It also cannot write ImportedRecord rows through the services' own clients or share code with teardown |
| Go through the services' APIs (tRPC, per-record) | Every invariant the services enforce comes for free; no direct DB coupling | Request-shaped endpoints for bulk load: per-row overhead, per-row auth context, and side effects (notifications, event fan-out) that a migration must suppress, which means import flags threaded through every organic write path |
| Per-service import endpoints (each service exposes an internal bulk-import procedure) | Each team owns its own import logic; no cross-database clients | Ordering across services still needs a central conductor, so the engine exists anyway. This option just splits its body across 17 codebases, and idempotency, teardown, provenance and reporting get rebuilt per service instead of once |
| A new internal v3 service with direct Prisma writes (chosen) | One home for the contract, the ordering, the registry writes and the run state; reuses the seeding precedent, the backfill patterns and the published service packages | Direct writes bypass the framework's effects, so the side-effect inventory must be maintained deliberately; the service holds write credentials to many databases and its blast radius must be respected |
What this costs, honestly
A new deployable. The usual new-service registration: deploy config, secrets, terraform, container repos in the infra repo. Real but one-off. The consolidation plan's push for fewer deployables is about the organic platform; a migration engine with credentials to every database arguably should be its own isolated deployable rather than folded into a domain service.
The repo's first BigQuery client. Today the warehouse link is one-way: Postgres to BigQuery via CDC. This service reads the other way. One new dependency, worth flagging to whoever owns the data-access story.
Write credentials to every service database. Scoped per environment, used only by this service, and every write it makes is registered and tear-down-able. The registry is the audit trail.
Schema drift becomes a runtime concern. When a service migrates its schema, the importer's contract can drift. The importer pins its dependency on each service package, so drift surfaces as type errors in the monorepo at PR time. That is the argument for living in the monorepo.
Why this direction
Adding a new entity type becomes a fixed, small recipe: extend the contract (one zod schema both sides build to), map the source fields in dbt, write one ordered insert routine. The registry, idempotency, teardown and reporting come from the shared machinery. The firm contract between source data and platform is defined once, in the place that has to honour it.