Proving a Postgres-to-Turso cutover: counts, checksums, and FK spot-checks

We migrated Storyie's production database from Supabase Postgres to a single shared Turso (libSQL) database — one ETL run, one maintenance window, one shot at getting every row across correctly. The ETL script finished, printed a summary, and exited 0.

That told us the script didn't throw. It didn't tell us the data was actually there, unchanged, and still pointing at the right users. A script that silently drops rows, mangles a JSON column, or migrates users out of order can exit 0 just as cleanly as one that migrated everything perfectly. "The script finished" and "the migration is correct" are different claims, and only one of them is worth staking a production cutover on.

So we wrote a second script whose only job is to check the first one's work: verify-turso-migration.ts. It runs after the ETL and before the web app's traffic is switched to Turso, and it is read-only — it never writes to either database, and it never connects to anything just by being imported.

TL;DR

• An ETL exiting 0 proves the script didn't crash, not that the data is correct. We run a separate verification step to close that gap before switching traffic.
• The verification script is read-only end to end — it never writes, and a guard at the bottom means it never connects on import, only when invoked directly from the CLI.
• Three checks per table, in increasing cost and decreasing coverage:

• JSON columns (Lexical content) get their own hashing path — the value can arrive as a string from one driver and a parsed object from the other, and key order isn't guaranteed to match, so we normalize before hashing.
• The runbook is explicit: don't proceed to the env/DNS switch until this script exits 0.
• We are honest about what this doesn't prove: sampling is sampling, and a checksum error is recorded as skip, not fail.

"Completion" is not proof

The ETL script (migrate-to-turso.ts) is the workhorse: it reads from Supabase Postgres and writes into the shared Turso DB, phase by phase (auth, shared, server-only, user-data), and it's idempotent, so a partial run can safely be resumed. But its exit code answers one question — did anything throw? — and that's a much weaker guarantee than "the data on Turso matches the data we started with."

verify-turso-migration.ts exists to answer the stronger question, and it does so by design as a completely separate script with a completely separate contract:

// packages/database/scripts/verify-turso-migration.ts (excerpt)
/**
 * Post-cutover verification for issue #554 ([turso] 08: data ETL + cutover).
 * Compares the legacy Supabase Postgres source against the single shared
 * Turso/libSQL target and produces a structured report covering:
 *
 *   1. Row-count parity — Postgres count vs Turso count per table.
 *   2. Sampling checksums — hash a deterministic projection of N sampled rows
 *      on both sides for content-sensitive tables. For Lexical jsonb columns,
 *      the canonical JSON string is hashed to prove byte-equivalence.
 *   3. FK integrity spot-checks — e.g. every diary.user_id has a matching user.
 *   ...
 * Usage (read-only — never writes):
 *   pnpm tsx packages/database/scripts/verify-turso-migration.ts
 *   ...
 * SAFETY: This script is READ-ONLY. It NEVER writes to any database.
 *         It NEVER connects on import — guarded by isMain check at bottom.
 */

That last guarantee — never connects on import — is enforced with a plain guard at the bottom of the file, not a convention we're trusting ourselves to remember:

// packages/database/scripts/verify-turso-migration.ts (excerpt)
const isMain =
  process.argv[1] != null &&
  (process.argv[1].endsWith("verify-turso-migration.ts") ||
    process.argv[1].endsWith("verify-turso-migration.js"));

if (isMain) {
  main().catch((err) => {
    console.error("Verification failed:", err);
    process.exit(1);
  });
}

In our cutover runbook this script sits in a specific place in the sequence: maintenance mode on, run the ETL, run verification, only then deploy the new web build pointed at Turso, then freeze the Supabase source read-only. The runbook says plainly not to proceed past verification until it exits 0 — a failed or skipped verification step is a rollback trigger, not a warning to note and move past.

Counting rows on both sides

The cheapest, least glamorous check is also the most complete one: for every table, count the rows on both sides and compare.

// packages/database/scripts/verify-turso-migration.ts (excerpt)
async function pgCount(sql: postgres.Sql, table: string, where = ""): Promise<number> {
  const whereClause = where ? `WHERE ${where}` : "";
  const rows = await sql.unsafe(`SELECT COUNT(*)::int AS c FROM ${table} ${whereClause}`);
  return (rows[0] as unknown as { c: number }).c;
}

async function tursoCount(client: LibSQLClient, table: string, where = ""): Promise<number> {
  const whereClause = where ? `WHERE ${where}` : "";
  const result = await client.execute(`SELECT COUNT(*) AS c FROM ${table} ${whereClause}`);
  const val = result.rows[0]?.[0];
  return typeof val === "bigint" ? Number(val) : typeof val === "number" ? val : 0;
}

This is the only one of the three checks that covers every row in every table, which is exactly why it runs first and why a mismatch here is the most serious kind of failure. It can't tell you a row's content is wrong, but it can tell you a row is missing (or duplicated) with certainty, no sampling involved. Per the runbook, a count mismatch means re-running the ETL scoped to just that table (--tables=<table> --confirm-write) — the ETL's idempotent writes make that a safe, cheap fix rather than a full re-run.

Checksumming a sample of rows

Row counts can't catch a row that exists on both sides but whose content silently changed in transit — a truncated string, a dropped field, a type coercion that Postgres and libSQL disagree about. For that, the script pulls a bounded sample from each side, in the same deterministic order, and hashes it:

// packages/database/scripts/verify-turso-migration.ts (excerpt)
async function pgSample(
  sql: postgres.Sql,
  table: string,
  columns: string,
  sampleSize: number,
  where = ""
): Promise<unknown[]> {
  const whereClause = where ? `WHERE ${where}` : "";
  return sql.unsafe(
    `SELECT ${columns} FROM ${table} ${whereClause} ORDER BY id ASC LIMIT ${sampleSize}`
  );
}

tursoSample runs the same ORDER BY id ASC LIMIT ${sampleSize} query against libSQL. sampleSize defaults to 20 (DEFAULT_SAMPLE_SIZE) and is overridable with --sample-size=N on the CLI. Ordering by id and taking the same limit on both sides is what makes "the same sample" a meaningful phrase here — without it, comparing a hash of row 1–20 on one side against an arbitrary 20 rows on the other would be comparing nothing at all.

// packages/database/scripts/verify-turso-migration.ts (excerpt)
function hashRows(rows: unknown[]): string {
  const canonical = rows
    .map((row) => {
      if (row === null || row === undefined) return "null";
      if (typeof row === "object") {
        const sorted = Object.fromEntries(
          Object.entries(row as Record<string, unknown>).sort(([a], [b]) => a.localeCompare(b))
        );
        return JSON.stringify(sorted);
      }
      return String(row);
    })
    .join("\n");
  return createHash("sha256").update(canonical).digest("hex").slice(0, 16);
}

If the hashes match on both sides, the sampled rows are byte-identical projections of the same data. If they don't, the runbook's read is that this is a transform bug — something in a mapXxx() function in the ETL script produced different content than the source held, not a missing-row problem. The fix is to find that mapping function, correct it, and re-verify.

Canonical JSON for Lexical content

The row-hashing approach above has one instability built into it, which is exactly why the sorted step exists: a Postgres client and a libSQL client can return the same logical row's keys in different orders. JSON.stringify is order-sensitive, so two identical objects can stringify to different bytes purely because of which driver produced them — a false mismatch that has nothing to do with the migration being wrong. Sorting entries by key before stringifying removes that source of noise from every row this script hashes.

Lexical content columns get an extra layer on top of that, because the value itself can arrive as either a string or an already-parsed object depending on the DB and driver:

// packages/database/scripts/verify-turso-migration.ts (excerpt)
function hashLexicalContent(value: unknown): string {
  let parsed: unknown;
  if (typeof value === "string") {
    try {
      parsed = JSON.parse(value);
    } catch {
      parsed = value;
    }
  } else {
    parsed = value;
  }
  return createHash("sha256")
    .update(JSON.stringify(parsed, Object.keys(parsed as object).sort()))
    .digest("hex")
    .slice(0, 16);
}

The function parses the value if it's a string, then re-serializes it with sorted keys before hashing. That means a Lexical document stored as a JSON string on one side and as a native JSON value on the other still produce the same hash, as long as the actual content is the same — the check is for byte-equivalence of meaning, not of the specific encoding a given driver happened to hand back. diaries, diary_entries, notes, and diary_letters all route their content column through this path instead of the plain hashRows comparison.

Foreign keys the database won't check for you

Row counts and checksums both operate table by table — neither one notices if a row's user_id points at a user that doesn't exist. That's a real risk in a migration that moves auth data and per-user data in separate phases: if the phases run out of order, or a user row fails to migrate for some reason, its dependent rows land on Turso pointing at nothing.

The FK spot-check runs against the Postgres source, before the target even enters the picture, and looks for exactly that gap:

// packages/database/scripts/verify-turso-migration.ts (excerpt)
async function spotCheckUserFk(
  sql: postgres.Sql,
  tableName: string,
  userIdCol: string,
  sampleSize: number
): Promise<{ status: "pass" | "fail"; note: string }> {
  const rows = await sql.unsafe(
    `SELECT DISTINCT ${userIdCol} FROM ${tableName} ORDER BY ${userIdCol} ASC LIMIT ${sampleSize}`
  );
  const ids = (rows as Array<Record<string, string>>).map((r) => r[userIdCol]);
  if (ids.length === 0) return { status: "pass", note: "no rows to check" };

  const missingRows = await sql<Array<{ id: string }>>`
    SELECT unnest(${ids}::uuid[]) AS id
    EXCEPT
    SELECT id FROM auth.users
  `;
  // ... build pass/fail result from missingRows
}

The EXCEPT query is the whole idea in one line: take a sampled batch of user_id values, subtract every id that actually exists in auth.users, and whatever's left over is orphaned. We run this against diaries, diary_entries, and notes — the tables where an orphaned row would mean a user's content silently attached to nobody. It's a Postgres-side check on purpose: the source of truth for "which users exist" during the migration window is still Postgres, and running it there also means we catch a bad reference before it ever gets copied to Turso, not after.

This matters more on libSQL than it might on a stricter relational engine, because it doesn't enforce foreign-key constraints the way Postgres does by default. A migration bug that drops or mis-maps a parent row won't necessarily surface as a database-level error on the target — the orphaned child rows will simply sit there, silent, until a user hits one. Per the runbook, a failure here means re-running the auth phase of the ETL (--resume-from=auth --confirm-write) before re-checking.

What this does not prove

We don't want to oversell what a passing report means, so here's the honest boundary. A table passes only when its count matches, its checksum status is pass, n/a, or skip, and its FK status is pass, skip, or absent — and the whole run passes only when every table does:

// packages/database/scripts/verify-turso-migration.ts (excerpt)
const overallPass = results.every(
  (r) =>
    r.countMatch &&
    (r.checksumStatus === "pass" || r.checksumStatus === "n/a" || r.checksumStatus === "skip") &&
    (r.fkStatus == null || r.fkStatus === "pass" || r.fkStatus === "skip")
);

Two things worth sitting with in that condition. First, skip counts as passing for both checksum and FK status — if the checksum query itself throws (a transient connection error, say), the run doesn't hard-fail on that table; it's recorded as skipped and the report says so, but it doesn't block the cutover on its own. Second, and more fundamentally: the checksum and FK checks are sampled, not exhaustive. The checksum compares the first sampleSize rows per table ordered by id (20 by default) — it's a spot-check on the rows we happened to look at, not a hash of the whole table. The FK spot-check samples a batch of distinct user ids the same way. Row-count parity is the only one of the three that covers every row.

That's a deliberate trade-off, not an oversight, and the honest way to describe it is: this catches the classes of bugs a migration is realistically likely to produce — an entire table dropped, a transform function that mangles content, users migrated out of order — without claiming to be a full-table diff. A convenient side effect is that sampling keeps the check fast, which matters inside a maintenance window where every extra minute is a minute the app isn't accepting writes.

Closing / takeaways

The lesson we'd repeat for any one-time cutover: separate "the migration ran" from "the migration is correct," and write the second check as its own read-only script with its own exit code. Ours checks three things — exhaustive row counts, sampled content checksums with driver-order-safe JSON canonicalization, and source-side FK spot-checks — and we don't let the cutover proceed past it until it exits 0.

For the schema/DDL side of running Drizzle against a production database — not the one-time data cutover, but the ongoing story of shipping migrations safely — see Drizzle migrate and custom migrations on Supabase. For how we think about the schema itself, see Database schema design.