`@octabits-io/backfill`

SQL migrations change shapes; backfills move data into them. This is that second half, made repeatable: a named, idempotent unit of work, a marker table that records a fully clean run, and a chain runner that owns the skip / mark / partial-retry protocol so a deploy pipeline can call the whole list on every deploy and pay one primary-key lookup per finished backfill. Pure functions with no dependency at all; the marker table ships as a plain-SQL store over a structural executor and as a Drizzle adapter.

npm i @octabits-io/backfill            # nothing else — bring the executor or the ORM you already have

import { runBackfills } from '@octabits-io/backfill';
import { createPostgresBackfillMarkerStore } from '@octabits-io/backfill/postgres';

// after your schema migrations, on every deploy:
await runBackfills(createPostgresBackfillMarkerStore({ db: pool }), [
  {
    name: 'backfill-invoice-totals-v2',
    run: async () => {
      const { processed, failures } = await transformPendingRows();   // WHERE new_col IS NULL AND legacy_col IS NOT NULL
      const pending = await countRowsStillNeedingWork();
      return { processed, failures, pending };
    },
  },
]);

Subpath	Deps	Contents
`.`	none	the `BackfillMarkerStore` contract, `runBackfills` and its protocol, `createInMemoryBackfillMarkerStore()`, the `BackfillDef` / `BackfillOutcome` / `BackfillRunSummary` / `DataMigrationRun` / `RunBackfillsOptions` / `BackfillLogger` types
`./postgres`	none (structural executor)	`createPostgresBackfillMarkerStore({ db, table? })` and `dataMigrationRunsDdl({ table? })` over any `query(text, params) → { rows }` — a `pg` Pool or Client, PGlite, a postgres.js wrapper
`.` (migrations)	none	`scanMigration(sql, { name })`, `splitMigration(sql)`, `ACKNOWLEDGEABLE_RISKS`, the `MigrationFinding` / `MigrationRisk` types — reading a migration for what breaks existing rows
`./postgres` (migrations)	none (structural executor, one connection)	`checkMigrationsOnRows({ db, migrations })` on a throwaway database, `dryRunMigrations({ db, migrations })` in a rolled-back transaction
`./drizzle`	`drizzle-orm` (optional peer)	`dataMigrationRunsColumns` to spread into your schema, `createDrizzleBackfillMarkerStore(db)`, the marker helpers `ensureDataMigrationRunsTable` / `isDataMigrationCompleted` / `markDataMigrationCompleted`, and `runBackfills(db, …)` — the root’s runner with a db as its first argument

A boundary lint keeps the root and ./postgres importing nothing at all and drizzle-orm confined to ./drizzle, and the whole package type-checks with no Node typings.

The protocol

A backfill is a named, idempotent unit of work whose run() selects only the rows that still need it — re-running on a clean database must do nothing — and reports { processed, failures, pending }. runBackfills decides what that means:

a marked backfill is skipped, so a finished one costs one primary-key lookup per deploy;
a clean run (failures === 0 && pending === 0, no skipMarking) is marked once, and never re-marked — the first marker’s timestamp and notes win;
a run with pending > 0 is left unmarked and retried on the next deploy, automatically;
skipMarking: true says the invocation covered only part of the data, so a partial-scope run never claims global completion;
failures > 0 throws and aborts the chain, so the deploy fails loudly instead of quietly marking bad state.

force: true re-runs a marked backfill without re-marking it.

The marker table

Markers live in an append-only data_migration_runs table — the same category as an ORM’s own migrations table, created on demand with CREATE TABLE IF NOT EXISTS, no migration file and no snapshot footprint. dataMigrationRunsDdl({ table? }) emits it for schemas that would rather declare it. Name a different table with table on either store.

It has no scope column, on purpose: “done” is a global state, because a backfill targets a legacy shape that disappears everywhere at once. Scope fan-out — per organisation, per workspace, per account — lives inside your run(); encode a scope in the name if a deployment genuinely needs per-scope marking, and return skipMarking: true from a filtered invocation.

On Drizzle

import { runBackfills, ensureDataMigrationRunsTable } from '@octabits-io/backfill/drizzle';

await runBackfills(db, backfills);      // the same protocol, over the Drizzle marker table

Same name as the root’s runner, different first argument: this one takes the db, the root’s takes a marker store. The db is a structural view (execute / select / insert), so a Drizzle instance and a transaction context both satisfy it and a second copy of drizzle-orm in the tree cannot break it. The marker helpers are exported for standalone CLIs that own the protocol themselves — individually invocable backfill commands with a --force flag, say.

A host that declares every platform table in its own schema and lets drizzle-kit own the migration spreads dataMigrationRunsColumns — the marker table column for column, held equal to dataMigrationRunsDdl() by a test — and ensureDataMigrationRunsTable runs that same DDL, so the two adapters can never create two tables:

import { dataMigrationRunsColumns } from '@octabits-io/backfill/drizzle';

export const dataMigrationRuns = pgTable('data_migration_runs', dataMigrationRunsColumns);

Migrations that keep the rows

A backfill is the middle step of every change to a table that holds data: expand (add the new shape next to the old), fill (move the data), contract (constrain, drop what is gone). The two checks below fail a migration that skips a step, before it runs anywhere that matters.

scanMigration reads a migration’s SQL — what drizzle-kit, Prisma or a person writes — and names what needs a second look:

Risk	Statement	Cleared by
`not_null_without_default`	`ADD COLUMN … NOT NULL` with no default, on a table the migration did not create	the safe form only: nullable or with a default, `NOT NULL` later
`set_not_null_without_fill`	`ALTER COLUMN … SET NOT NULL` with no `UPDATE` of that column before it in the same migration	the safe form only: fill first, in the same migration or by a finished backfill plus that `UPDATE` as the net
`drop_table`, `drop_column`, `rename`, `change_type`, `deletes_rows`	they pass every dry run and still lose data, or break the version still running during the deploy	the sequence, or a `-- reviewed: <reason>` comment directly above the statement — the finding comes back `acknowledged` with the reason, for the reviewer

import { scanMigration } from '@octabits-io/backfill';

const findings = scanMigration(sql, { name: '0007_contact_phone.sql' });
const blocking = findings.filter((f) => !f.acknowledged);   // fail CI on these
const reviewed = findings.filter((f) => f.acknowledged);    // print these for the reviewer

checkMigrationsOnRows runs them. On a throwaway database it applies the migrations in order, and before each one puts a row into every table that is still empty — nullable columns left NULL, every other column a plain value of its type, a foreign key pointing at its parent’s row — then applies the migration in a transaction. A migration that only works on empty tables fails with the statement and Postgres’s own message, and the check stops there, as a deploy would. Tables it cannot fill (a type with no plain value, a check the synthetic row breaks) come back as notes.

import { PGlite } from '@electric-sql/pglite';
import { checkMigrationsOnRows } from '@octabits-io/backfill/postgres';

const report = await checkMigrationsOnRows({ db: await PGlite.create(), migrations });
// report.failures: [{ migration, statement, error, message }] — empty when every migration keeps the rows

dryRunMigrations is the same idea on real data: the pending migrations inside one transaction, rolled back whatever happens — for a restored copy of production before a deploy. Both take one connection (a pg Client, PGlite), not a pool, and both rely on Postgres running DDL in a transaction. Which migrations are pending is your migration tool’s knowledge, not this package’s.

The octabits starter runs both: the guard applies every migration over a row per table and reads each one, and pnpm db:dry-run tries the pending ones on the developer’s data or DATABASE_URL.

When not to use it

To change a schema. This is the layer above your migrations. It does not create columns, and it does not replace drizzle-kit, Prisma Migrate, Atlas or psql — it checks what they wrote.
For recurring work. A backfill is one-shot: it runs until the legacy shape is gone, then it is a lookup forever. Anything on a schedule is a job for a queue.
For long streaming work inside a request. The runner is a deploy-time chain. Batch inside run() and report pending so a big backfill finishes over several deploys, or drive it from a job if it must run for hours.
As a lock. Two deploys running the chain concurrently may both execute a backfill; the marker insert is ON CONFLICT DO NOTHING, so the marking is safe, but the work is not serialised for you.

Conformance

One scenario suite runs against all three marker stores — the in-memory reference, the plain-SQL store and the Drizzle adapter — on an embedded PGlite (real Postgres, no Docker). A new adapter passes the suite or it is not an adapter.

Stability

Pre-1.0: patches never break, minors say so in the changelog. The DDL helper is part of the surface — a column it emits is a column a store will read — and so are the summary status values (completed, already_completed, pending, unmarked), which a deploy log is read against.