Skip to content

Open-source AI coworkers that each get a computer of their own: a browser, files and tools, with every action decided before it happens and recorded after. Bring any AG-UI agent.

12.6 KB
Raw Blame
/**
 * Whether every migration in the folder can still be reached by the migrator, on THIS database.
 *
 * WHAT THE MIGRATOR ACTUALLY DOES. `PgDialect.migrate` reads one row of its journal table (see
 * `MigrationsJournal`) — `order by created_at desc limit 1` — and then runs a migration
 * if, and only if, its journal `when` is greater than `Number(created_at)` of that row. That row is
 * not always the newest stamp: Postgres sorts NULLs FIRST in a descending order, so a single row
 * with a NULL `created_at` is the row it reads, and `Number(null)` is 0. See `migratorHighWaterMark`. It never looks at hashes, never looks at tags, and never notices that a
 * migration it walked past was one it has never run. A migration whose `when` is at or below the
 * high-water mark is therefore not "pending": it is unreachable, forever, and the migrator will
 * report success without it.
 *
 * WHY THAT IS NOT HYPOTHETICAL. A branch that ships migrations and is then merged with `main` has
 * its own migrations renumbered and restamped to sit after main's. Every database that ran the
 * branch BEFORE the merge has already recorded the branch's old, higher stamp. Main's migrations
 * now carry a `when` older than that mark, so they are skipped — silently — while the renumbered
 * branch migrations, stamped later still, run perfectly. The database ends up missing tables that
 * the migrator just said it had applied. A fresh database is unaffected, which is exactly why CI,
 * which always migrates from empty, cannot see it.
 *
 * WHY THIS REFUSES RATHER THAN REPAIRS. Running the unreachable SQL and backfilling its row is a
 * two-line change and the wrong one. The migrations that are out of reach are out of reach because
 * some OTHER journal already ran against this database, and nothing here knows what that journal
 * did — whether its statements created the same objects under other names, left half a schema
 * behind, or were the hand-repair of an earlier failure. The honest answer to "this database cannot
 * be brought up to date by running the folder forward" is to say so, loudly, with the names in
 * hand, and to stop. An operator can then drop a disposable database, or repair a durable one
 * deliberately. What must never happen again is `{"type":"migrations-applied","status":"ok"}` over
 * tables that do not exist.
 */
import { createHash } from "node:crypto";
import { readFile } from "node:fs/promises";
import { join } from "node:path";

/** One migration in the folder, as the migrator reads it. */
export type FolderMigration = {
  tag: string;
  /** The journal's `when`: the only thing the migrator compares. */
  when: number;
  /**
   * sha256 of the whole `.sql` file, hex.
   *
   * Computed exactly as `drizzle-orm/migrator`'s `readMigrationFiles` computes it — the whole file
   * as read, before any statement splitting — because this is matched against the hashes that the
   * migrator itself wrote into its journal table. A different recipe here would make
   * every applied migration look unapplied.
   */
  hash: string;
};

/**
 * One row of the migrator's journal table (see `MigrationsJournal`).
 *
 * `createdAt` is nullable because the column is: drizzle creates it as a bare `bigint`, and a NULL
 * there is not noise to be defaulted away — it is the row the migrator compares against. A reader
 * that turns NULL into a number before it gets here hides exactly the case that matters.
 */
export type AppliedMigration = { hash: string; createdAt: number | null };

/**
 * Where the migrator keeps its bookkeeping: the table it reads its high-water mark from and records
 * each applied migration in.
 *
 * The SHAPE lives here, in the module with no database in it, and the VALUE does not:
 * `MIGRATIONS_JOURNAL` in `./apply-migrations` is the one place the name is chosen, and it is handed
 * to everything that needs it — the reach check's read, `migrate()`, and `outOfReachRefusal`'s repair
 * instructions. The value cannot move here without this module deciding deployment configuration,
 * and it cannot be imported from there without a cycle (`./apply-migrations` imports this module),
 * so it is passed in. Nothing in this file may spell the table's name.
 */
export type MigrationsJournal = {
  migrationsSchema: string;
  migrationsTable: string;
};

/**
 * The journal table as an operator would type it into `psql`: schema-qualified, each part quoted.
 *
 * Quoted always rather than when needed, because "when needed" is a reserved-word list this file
 * has no business keeping; a quoted lower-case identifier means exactly what the bare one does.
 */
function journalTable({
  migrationsSchema,
  migrationsTable,
}: MigrationsJournal): string {
  const quote = (identifier: string) => `"${identifier.replaceAll('"', '""')}"`;
  return `${quote(migrationsSchema)}.${quote(migrationsTable)}`;
}

type Journal = { entries: { idx: number; tag: string; when: number }[] };

/**
 * The folder, read the way the migrator reads it: the journal decides, not the directory listing.
 *
 * A `.sql` file with no journal entry never runs and is not this function's business; an entry with
 * no file stops the migrator dead, and `readFile` rejecting here says so in the same breath.
 */
export async function readMigrationFolder(
  folder: string,
): Promise<FolderMigration[]> {
  const journal = JSON.parse(
    await readFile(join(folder, "meta", "_journal.json"), "utf8"),
  ) as Journal;

  return Promise.all(
    journal.entries.map(async (entry) => ({
      tag: entry.tag,
      when: entry.when,
      hash: createHash("sha256")
        .update(await readFile(join(folder, `${entry.tag}.sql`), "utf8"))
        .digest("hex"),
    })),
  );
}

/**
 * The number the migrator will compare every journal `when` against, chosen the way it chooses it.
 *
 * `PgDialect.migrate` (drizzle-orm 0.45.2) runs `select … order by created_at desc limit 1` and then
 * `Number(lastDbMigration.created_at) < migration.folderMillis`. Two things follow, and this
 * function must reproduce both, because a guard that picks a different row than the migrator is a
 * guard about some other migrator:
 *
 *  - Postgres puts NULLs FIRST in a descending sort, so one NULL `created_at` is the row it reads,
 *    however many real stamps sit below it. `Math.max` over the stamps picks the largest real one
 *    and disagrees.
 *  - `Number(null)` is 0, so that row's mark is 0 and every migration in the folder is "after" it:
 *    the migrator re-runs the whole history and dies on the first `CREATE TYPE` that already exists.
 *
 * `undefined` for an empty table: the migrator then has no row and runs everything, correctly.
 */
export function migratorHighWaterMark(
  applied: readonly AppliedMigration[],
): number | undefined {
  if (applied.length === 0) return undefined;
  if (applied.some((row) => row.createdAt === null)) return 0;
  return Math.max(...applied.map((row) => row.createdAt ?? 0));
}

/**
 * The migrations the migrator would get wrong on this database, judged against the row it reads.
 *
 * Two ways, both of which end with the migrator's word and the database disagreeing:
 *
 *  - OUT OF REACH: never applied, and stamped at or below the mark — walked past forever, while the
 *    run reports success.
 *  - TO BE RE-RUN: already applied (its hash is recorded), yet stamped ABOVE the mark — run a second
 *    time. With honest stamps the mark is at least every applied migration's `when`, so this is
 *    empty; it fills when the row the migrator reads is not the newest stamp, which a NULL
 *    `created_at` guarantees.
 *
 * An empty `applied` is a database the migrator has never touched, where everything is reachable by
 * definition — a fresh database can never be in this trouble, and saying so explicitly keeps the
 * check from having an opinion about the one case it has no evidence for.
 */
export function migrationsOutOfReach(
  folder: readonly FolderMigration[],
  applied: readonly AppliedMigration[],
): FolderMigration[] {
  const highWaterMark = migratorHighWaterMark(applied);
  if (highWaterMark === undefined) return [];

  const appliedHashes = new Set(applied.map((row) => row.hash));

  return folder.filter((migration) =>
    migration.when <= highWaterMark
      ? !appliedHashes.has(migration.hash)
      : appliedHashes.has(migration.hash),
  );
}

/**
 * The refusal, written for whoever is reading a failed deploy at an unreasonable hour.
 *
 * It names the mark and what set it, every migration that cannot be reached, and the two ways out,
 * because the one thing an operator cannot work out from a stack trace is that the database is
 * *ahead* of the folder rather than behind it.
 *
 * `journal` is required, not defaulted: the repair instructions are SQL an operator will paste, and
 * they must name the table the check actually read. A default here would be a second spelling of the
 * name, and the first configuration that moved the journal would make this message send someone to
 * repair a table that is not the one refusing them.
 */
export function outOfReachRefusal(
  outOfReach: readonly FolderMigration[],
  folder: readonly FolderMigration[],
  applied: readonly AppliedMigration[],
  journal: MigrationsJournal,
): string {
  const table = journalTable(journal);
  const highWaterMark = migratorHighWaterMark(applied) ?? 0;
  const appliedHashes = new Set(applied.map((row) => row.hash));
  const skipped = outOfReach.filter(
    (migration) => !appliedHashes.has(migration.hash),
  );
  const rerun = outOfReach.filter((migration) =>
    appliedHashes.has(migration.hash),
  );
  const nulls = applied.filter((row) => row.createdAt === null).length;
  const inFolder = new Set(folder.map((migration) => migration.hash));
  /*
   * Rows this folder cannot account for. Not a failure on its own — a squashed or deleted migration
   * leaves them too — but when migrations are out of reach these rows are almost always the reason,
   * so the count is the fastest thing an operator can act on.
   */
  const unknown = applied.filter((row) => !inFolder.has(row.hash)).length;
  const mark =
    nulls > 0
      ? "a row with no created_at, which the migrator reads first and counts as 0"
      : (folder.find((migration) => migration.when === highWaterMark)?.tag ??
        "a migration this folder does not contain");
  const list = (migrations: readonly FolderMigration[]) =>
    migrations.map(
      (migration) => `  - ${migration.tag} (stamped ${migration.when})`,
    );

  return [
    `This database's migration journal, ${table}, has already recorded a migration stamped ${highWaterMark} (${mark}), and the migrator only runs migrations stamped after the row it reads: the first one ordered by created_at descending.`,
    ...(nulls === 0
      ? []
      : [
          `${nulls === 1 ? "1 applied row has" : `${nulls} applied rows have`} a NULL created_at. Postgres sorts NULLs first in a descending order, so that row, not the newest stamp, is the mark.`,
        ]),
    ...(skipped.length === 0
      ? []
      : [
          `${skipped.length === 1 ? "This migration is" : `These ${skipped.length} migrations are`} stamped at or below that mark and ${skipped.length === 1 ? "has" : "have"} never been applied here, so running the migrator would skip ${skipped.length === 1 ? "it" : "them"} and report success over the schema ${skipped.length === 1 ? "it" : "they"} should have created:`,
          ...list(skipped),
        ]),
    ...(rerun.length === 0
      ? []
      : [
          `${rerun.length === 1 ? "This migration has" : `These ${rerun.length} migrations have`} already been applied here but ${rerun.length === 1 ? "is" : "are"} stamped above that mark, so running the migrator would run ${rerun.length === 1 ? "it" : "them"} a second time:`,
          ...list(rerun),
        ]),
    unknown === 0
      ? "Every applied migration is still present in this folder."
      : `${unknown} applied ${unknown === 1 ? "migration is" : "migrations are"} not in this folder at all, which is what a renumbered or rewritten migration history looks like from here: this database ran a journal that no longer exists.`,
    nulls > 0
      ? `Nothing has been applied. Give every row with a NULL created_at the journal \`when\` of the migration whose hash it records (\`update ${table} set created_at = <its journal when> where hash = <sha256 of the file>\`), or delete a row that matches no migration, and run again.`
      : `Nothing has been applied. Recreate the database from empty if it is disposable (a development, preview or CI database usually is); otherwise apply the SQL above by hand and record each one with \`insert into ${table} (hash, created_at) values (<sha256 of the file>, <its journal when>)\`, which leaves the high-water mark where it is.`,
  ].join("\n");
}