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.

23.7 KB
Raw Blame
import { describe, expect, test } from "bun:test";
import { readdir, readFile } from "node:fs/promises";

/**
 * The migration journal, which decides what actually runs.
 *
 * Drizzle applies a migration when its journal `when` is later than the newest one the database has
 * recorded, so the ordering that matters is the timestamps, not the file names. A migration stamped
 * ahead of real time therefore does not merely sort oddly: it silently swallows every migration
 * authored after it until the clock catches up, and `drizzle-kit migrate` reports success while
 * doing it.
 *
 * That happened here. `0012` was hand-written with `when = previous + 86_400_000`, a day into the
 * future, and the next migration to arrive carried a real timestamp, so it was older by comparison.
 * `migrate` said "migrations applied successfully!", the table it should have created did not exist,
 * and the only symptom was an integration test failing with `relation "skill_tools" does not exist`.
 *
 * Nothing else in the build would have caught that, which is why this is a test rather than a note.
 *
 * WHAT THIS FILE CANNOT SEE, so that nobody reads it as covering more than it does: every check
 * here reads ONE journal and asks whether it agrees with itself. The same swallowing happens
 * between journals — a merge that renumbers a branch's migrations gives the migrations it merged in
 * a `when` older than the stamp a pre-merge database has already recorded, and those are skipped on
 * that database while this file, and a fresh database, and CI, all stay green. That half lives in
 * `migration-reach.test.ts` and in the guard it tests, which runs against the database rather than
 * against the folder, because it is the only place the evidence exists.
 */

type Entry = { idx: number; tag: string; when: number };

/**
 * A column as a snapshot describes it, which is more than its name.
 *
 * `type` and `notNull` are here because they are what `generate` DIFFS: a snapshot that names the
 * right column under the wrong type has the next migration emit an `ALTER COLUMN` nobody wrote, or
 * emit nothing where one was needed, and `notNull` decides whether the statement it emits can run
 * against rows that already exist. Both were read by nobody until the check below.
 */
type SnapshotColumn = { name: string; type?: string; notNull?: boolean };

type Snapshot = {
  tables?: Record<
    string,
    {
      name: string;
      columns?: Record<string, SnapshotColumn>;
      indexes?: Record<string, unknown>;
      /** The predicate, not merely the name: see {@link checkDisagreements}. */
      checkConstraints?: Record<string, { name: string; value: string }>;
    }
  >;
};

const directory = new URL("../drizzle/", import.meta.url);

const readJournal = async (): Promise<Entry[]> => {
  const journal = JSON.parse(
    await readFile(new URL("meta/_journal.json", directory), "utf8"),
  ) as { entries: Entry[] };
  return journal.entries;
};

/** The snapshot `generate` writes for an entry is named after its index, never after its tag. */
const snapshotName = (idx: number) =>
  `${String(idx).padStart(4, "0")}_snapshot.json`;

const readSnapshot = async (idx: number): Promise<Snapshot> =>
  JSON.parse(
    await readFile(new URL(`meta/${snapshotName(idx)}`, directory), "utf8"),
  ) as Snapshot;

/*
 * A minute, not a day. The mistake this file exists for is `when = previous + 86_400_000`, so a
 * day of slack is exactly the amount that lets it through: at the moment it is authored such a
 * stamp sits a day ahead of the clock and a hair under `now + 86_400_000`, and the guard that was
 * written to name it never once could. What the slack is actually for is a migration generated on
 * a developer's machine whose clock runs a little fast and pushed straight into CI, which is
 * seconds of disagreement, not hours.
 */
const CLOCK_SKEW_ALLOWANCE = 60_000;

/**
 * Entries stamped later than the moment we are asking, which is the only moment such a stamp can
 * be caught at. This is deliberately a function of `now` rather than of `Date.now()` directly:
 * "ahead of real time" is a claim about when the entry was authored, so the test below asks it
 * about the present, and the test after that asks it about the day the documented mistake was made.
 *
 * Its reach is narrower than it looks, and that is not a hole. A stamp a day into the future is
 * only harmful while it is the newest entry, because the migration that lands next is the one it
 * swallows — and that migration carries a real timestamp, so it sorts *before* the bad one and the
 * ordering test above fails on it. Once the clock has passed the bad stamp nothing can be swallowed
 * any more. Between them the two tests say `max(when) <= now`, with no window in between.
 */
const stampedAhead = (entries: Entry[], now: number) =>
  entries
    .filter((entry) => entry.when > now + CLOCK_SKEW_ALLOWANCE)
    .map(
      (entry) =>
        `${entry.tag} is stamped ${entry.when}, ${entry.when - now}ms ahead of the clock`,
    );

const quoted = (sql: string, pattern: RegExp) =>
  [...sql.matchAll(pattern)].map((match) => match.slice(1) as string[]);

const tableNames = (snapshot: Snapshot) =>
  new Set(Object.values(snapshot.tables ?? {}).map((table) => table.name));

const tableIn = (snapshot: Snapshot, name: string) =>
  Object.values(snapshot.tables ?? {}).find((table) => table.name === name);

const columnNames = (snapshot: Snapshot, name: string) =>
  new Set(Object.keys(tableIn(snapshot, name)?.columns ?? {}));

const indexNames = (snapshot: Snapshot, name: string) =>
  new Set(Object.keys(tableIn(snapshot, name)?.indexes ?? {}));

const columnIn = (snapshot: Snapshot, table: string, column: string) =>
  tableIn(snapshot, table)?.columns?.[column];

/**
 * What an `ADD COLUMN` actually declares, out of everything it says after the column's name.
 *
 * THE NAME IS NOT THE COLUMN. Matching only names let the snapshot call a column anything it liked
 * — `probe_action` as a `boolean`, and `NOT NULL` where the statement adds it nullable — and every
 * check here went on agreeing. Both halves are what `generate` diffs the next schema against, so a
 * snapshot wrong about either has the next migration emit a statement nobody wrote: an `ALTER
 * COLUMN ... TYPE` correcting a type no database ever had, or a `SET NOT NULL` against a table full
 * of the nulls the real column has been collecting since.
 *
 * AND `NOT NULL` IS THE HALF THAT FAILS ON PRODUCTION AND NOT IN REVIEW. A column added nullable
 * and snapshotted `notNull` makes the next `generate` emit the constraint, which passes on an empty
 * development database and stops dead on the first deployment holding a row that never filled it in.
 *
 * The default is dropped rather than compared: drizzle records it in its own spelling — `false` for
 * the SQL `false`, `"now()"` for `now()`, a quoted string for a quoted string — and a comparison
 * against the SQL literal would be a comparison of two notations rather than of two schemas.
 */
const declared = (tail: string): { type: string; notNull: boolean } => ({
  type: tail
    .replace(/\bNOT NULL\b/gi, " ")
    .replace(
      /\bDEFAULT\s+(?:'(?:[^']|'')*'(?:\s*::\s*[\w.]+(?:\[\])?)*|[^\s;]+)/gi,
      " ",
    )
    .trim()
    .replace(/\s+/g, " "),
  notNull: /\bNOT NULL\b/i.test(tail),
});

/**
 * The text between a `(` and the `)` that closes it, with nested parentheses and quoted strings
 * kept whole.
 *
 * A CHECK body cannot be matched by a regular expression, because every interesting one ends in
 * `IN ('a', 'b')` and the paren that closes the list is not the paren that closes the check. A
 * lazy `\(.*?\)` therefore truncates exactly the predicates whose roster is the thing worth
 * guarding. `open` is the index OF the opening paren.
 */
const balanced = (sql: string, open: number): string => {
  let depth = 0;
  for (let at = open; at < sql.length; at += 1) {
    const character = sql[at];
    if (character === "'") {
      // A postgres string literal, in which parens mean nothing. '' inside one is an escaped quote,
      // and falls out of this loop for free: it closes and immediately reopens.
      at += 1;
      while (at < sql.length && sql[at] !== "'") at += 1;
      continue;
    }
    if (character === "(") depth += 1;
    else if (character === ")") {
      depth -= 1;
      if (depth === 0) return sql.slice(open + 1, at);
    }
  }
  return sql.slice(open + 1);
};

/** Two spellings of one predicate are one predicate; only the whitespace is free to differ. */
const normalised = (expression: string) =>
  expression.trim().replace(/\s+/g, " ");

type Check = { table: string; name: string; value: string };

/**
 * Every CHECK the migration declares, in both spellings postgres allows.
 *
 * `generate` writes them inline inside `CREATE TABLE` for a new table and as `ALTER TABLE ... ADD
 * CONSTRAINT` for an existing one, so a reader of only one spelling is blind to half the
 * migrations in the folder — and `0057` is in the half that would have been missed.
 */
const checksIn = (sql: string): Check[] => {
  const found: Check[] = [];

  for (const match of sql.matchAll(
    /ALTER TABLE "([^"]+)" ADD CONSTRAINT "([^"]+)" CHECK\s*\(/gi,
  )) {
    const open = match.index + match[0].length - 1;
    found.push({
      table: match[1]!,
      name: match[2]!,
      value: normalised(balanced(sql, open)),
    });
  }

  for (const table of sql.matchAll(
    /CREATE TABLE (?:IF NOT EXISTS )?"([^"]+)"\s*\(/gi,
  )) {
    const body = balanced(sql, table.index + table[0].length - 1);
    for (const match of body.matchAll(/CONSTRAINT "([^"]+)" CHECK\s*\(/gi)) {
      const open = match.index + match[0].length - 1;
      found.push({
        table: table[1]!,
        name: match[1]!,
        value: normalised(balanced(body, open)),
      });
    }
  }

  return found;
};

/** Constraints the migration drops by name, whatever kind they are; only checks are asked about. */
const constraintsDropped = (sql: string) =>
  quoted(
    sql,
    /ALTER TABLE "([^"]+)" DROP CONSTRAINT (?:IF EXISTS )?"([^"]+)"/gi,
  ).map(([table, name]) => `${table}.${name}`);

const checksOf = (snapshot: Snapshot, table: string) =>
  new Map(
    Object.values(tableIn(snapshot, table)?.checkConstraints ?? {}).map(
      (check) => [check.name, normalised(check.value)],
    ),
  );

/**
 * What the migration does to the CHECK constraints, against what the snapshots say happened.
 *
 * THE NAME IS NOT THE PREDICATE, and a CHECK is nothing but its predicate. Widening a roster —
 * `IN ('composio', 'arcade')` grown one more member — leaves the constraint present, named the
 * same, and refusing every value anybody wrote a test about, so a test that inserts a junk row and
 * reads back the name of what refused it stays green through the mutation. The snapshot beside the
 * migration is the only artifact that records the predicate itself, and until this check existed
 * nothing compared the two: the pair could disagree about what the database is allowed to hold and
 * every test in this file went on agreeing.
 *
 * Read in both directions, like the tables above. A snapshot that records a predicate no statement
 * writes leaves `generate` diffing against a constraint no database has; a statement no snapshot
 * records has the next `generate` re-emit it, and `ADD CONSTRAINT` is not conditional.
 */
const checkDisagreements = (
  sql: string,
  before: Snapshot,
  after: Snapshot,
  droppedTables: string[],
) => {
  const added = checksIn(sql);
  const dropped = new Set(constraintsDropped(sql));
  const problems: string[] = [];
  const tables = new Set([
    ...tableNames(before),
    ...tableNames(after),
    ...added.map((check) => check.table),
  ]);

  for (const table of tables) {
    const was = checksOf(before, table);
    const is = checksOf(after, table);
    const writes = new Map(
      added
        .filter((check) => check.table === table)
        .map((check) => [check.name, check.value]),
    );

    for (const [name, value] of is)
      if (was.get(name) !== value && writes.get(name) !== value)
        problems.push(
          was.has(name)
            ? `the snapshot changes CHECK "${name}" on "${table}", the migration never writes that predicate`
            : `the snapshot adds CHECK "${name}" on "${table}", the migration never adds that predicate`,
        );

    for (const [name, value] of writes)
      if (is.get(name) !== value)
        problems.push(
          `the migration adds CHECK "${name}" on "${table}", the snapshot does not record that predicate`,
        );

    for (const [name, value] of was)
      if (
        is.get(name) !== value &&
        !dropped.has(`${table}.${name}`) &&
        !droppedTables.includes(table)
      )
        problems.push(
          `the snapshot ${is.has(name) ? "changes" : "drops"} CHECK "${name}" on "${table}", the migration never drops it`,
        );

    for (const name of was.keys())
      if (dropped.has(`${table}.${name}`) && is.get(name) === was.get(name))
        problems.push(
          `the migration drops CHECK "${name}" on "${table}", the snapshot still has it unchanged`,
        );
  }

  return problems;
};

const everyIndexName = (snapshot: Snapshot) =>
  new Set(
    Object.values(snapshot.tables ?? {}).flatMap((table) =>
      Object.keys(table.indexes ?? {}),
    ),
  );

/**
 * What the SQL says it does, against what the two snapshots say happened.
 *
 * Read in both directions on purpose. A snapshot that describes a different schema than the
 * migration beside it satisfies "a snapshot exists" perfectly well, and then `generate` diffs the
 * next migration against a schema the database was never in. So every table, column and index the
 * snapshot pair moves has to be named by a statement in the file, and every statement in the file
 * has to show up in the snapshot pair.
 */
const disagreements = (sql: string, before: Snapshot, after: Snapshot) => {
  const created = quoted(
    sql,
    /CREATE TABLE (?:IF NOT EXISTS )?"([^"]+)"/gi,
  ).map(([table]) => table!);
  const dropped = quoted(sql, /DROP TABLE (?:IF EXISTS )?"([^"]+)"/gi).map(
    ([table]) => table!,
  );
  /*
   * The third capture is everything the statement says after the name — the type, and whatever
   * `DEFAULT` and `NOT NULL` follow it. See {@link declared}: the name alone says a column arrived
   * and nothing about what arrived.
   */
  const columnsAdded = quoted(
    sql,
    /ALTER TABLE "([^"]+)" ADD COLUMN (?:IF NOT EXISTS )?"([^"]+)"([^;]*)/gi,
  );
  const columnsDropped = quoted(
    sql,
    /ALTER TABLE "([^"]+)" DROP COLUMN (?:IF EXISTS )?"([^"]+)"/gi,
  );
  const indexesCreated = quoted(
    sql,
    /CREATE (?:UNIQUE )?INDEX (?:IF NOT EXISTS )?"([^"]+)"/gi,
  ).map(([index]) => index!);
  const indexesDropped = quoted(
    sql,
    /DROP INDEX (?:IF EXISTS )?"([^"]+)"/gi,
  ).map(([index]) => index!);

  const was = tableNames(before);
  const is = tableNames(after);
  const problems: string[] = [];

  for (const table of is)
    if (!was.has(table) && !created.includes(table))
      problems.push(
        `the snapshot adds "${table}", the migration never creates it`,
      );
  for (const table of created)
    if (!is.has(table) || was.has(table))
      problems.push(
        `the migration creates "${table}", the snapshot does not add it`,
      );
  for (const table of was)
    if (!is.has(table) && !dropped.includes(table))
      problems.push(
        `the snapshot drops "${table}", the migration never drops it`,
      );
  for (const table of dropped)
    if (is.has(table))
      problems.push(
        `the migration drops "${table}", the snapshot still has it`,
      );

  /*
   * Only tables that survive the migration. Dropping a table takes its columns and indexes with it
   * without a statement naming any of them, and demanding one would be demanding SQL nobody writes.
   */
  for (const table of [...is].filter((name) => was.has(name))) {
    const columnsWas = columnNames(before, table);
    const columnsIs = columnNames(after, table);
    const named = (list: string[][], column: string) =>
      list.some(([on, which]) => on === table && which === column);

    for (const column of columnsIs)
      if (!columnsWas.has(column) && !named(columnsAdded, column))
        problems.push(
          `the snapshot adds "${table}"."${column}", the migration never adds it`,
        );
    for (const column of columnsWas)
      if (!columnsIs.has(column) && !named(columnsDropped, column))
        problems.push(
          `the snapshot drops "${table}"."${column}", the migration never drops it`,
        );
    for (const [on, column] of columnsAdded)
      if (on === table && !(columnsIs.has(column!) && !columnsWas.has(column!)))
        problems.push(
          `the migration adds "${table}"."${column}", the snapshot does not add it`,
        );
    for (const [on, column] of columnsDropped)
      if (on === table && !(columnsWas.has(column!) && !columnsIs.has(column!)))
        problems.push(
          `the migration drops "${table}"."${column}", the snapshot does not drop it`,
        );

    /*
     * AND WHAT THE ADDED COLUMN IS, not merely that it is there. See {@link declared}. Only columns
     * the snapshot really holds are asked — one it does not is already reported above, and asking
     * twice would name a single mistake in two sentences.
     */
    for (const [on, column, tail] of columnsAdded) {
      if (on !== table) continue;
      const added = columnIn(after, table, column!);
      if (!added) continue;
      const statement = declared(tail ?? "");
      if (added.type !== statement.type)
        problems.push(
          `the migration adds "${table}"."${column}" as ${statement.type}, the snapshot calls it ${added.type}`,
        );
      if ((added.notNull ?? false) !== statement.notNull)
        problems.push(
          `the migration adds "${table}"."${column}" ${statement.notNull ? "NOT NULL" : "nullable"}, the snapshot says the opposite`,
        );
    }

    const indexesWas = indexNames(before, table);
    const indexesIs = indexNames(after, table);
    for (const index of indexesIs)
      if (!indexesWas.has(index) && !indexesCreated.includes(index))
        problems.push(
          `the snapshot adds index "${index}" on "${table}", the migration never creates it`,
        );
    for (const index of indexesWas)
      if (!indexesIs.has(index) && !indexesDropped.includes(index))
        problems.push(
          `the snapshot drops index "${index}" on "${table}", the migration never drops it`,
        );
  }

  const indexes = everyIndexName(after);
  for (const index of indexesCreated)
    if (!indexes.has(index))
      problems.push(
        `the migration creates index "${index}", the snapshot lacks it`,
      );
  for (const index of indexesDropped)
    if (indexes.has(index))
      problems.push(
        `the migration drops index "${index}", the snapshot still has it`,
      );

  problems.push(...checkDisagreements(sql, before, after, dropped));

  return problems;
};

describe("the migration journal", () => {
  test("stamps every migration later than the one before it", async () => {
    const entries = await readJournal();

    const outOfOrder = entries
      .map((entry, index) => ({ entry, previous: entries[index - 1] }))
      .filter(({ entry, previous }) => previous && entry.when <= previous.when)
      .map(
        ({ entry, previous }) =>
          `${entry.tag} (${entry.when}) is not after ${previous?.tag} (${previous?.when})`,
      );

    expect(outOfOrder).toEqual([]);
  });

  test("stamps nothing later than the moment this test runs", async () => {
    expect(stampedAhead(await readJournal(), Date.now())).toEqual([]);
  });

  test("has an entry for every migration file, and a file for every entry", async () => {
    // A journal and a directory that disagree is the other way this goes wrong quietly: a file with
    // no entry never runs, and an entry with no file stops `migrate` dead.
    const files = (await readdir(directory))
      .filter((name) => name.endsWith(".sql"))
      .map((name) => name.replace(/\.sql$/, ""))
      .sort();

    const entries = await readJournal();

    expect(entries.map((entry) => entry.tag).sort()).toEqual(files);
  });

  test("names every entry after its own index", async () => {
    /*
     * `idx` is what the snapshot is named after and what orders the entries; the tag prefix is only
     * a label. A hand-written migration that disagrees with itself — `idx: 30` under the tag
     * `0031_...` — leaves every check that reads the prefix looking at the wrong file while
     * reporting success, so pin the two together before anything else trusts either.
     */
    const entries = await readJournal();

    const mislabelled = entries
      .filter(
        (entry) =>
          entry.tag.split("_")[0] !== String(entry.idx).padStart(4, "0"),
      )
      .map((entry) => `${entry.tag} is entry idx ${entry.idx}`);

    expect(mislabelled).toEqual([]);
  });

  test("has a snapshot for every migration, and a migration for every snapshot", async () => {
    /*
     * A third way this goes wrong quietly, and the one that bites the NEXT person rather than this
     * one. `generate` diffs the schema against the newest snapshot in `meta/`, so a migration that
     * ships without one leaves the previous snapshot as the newest: the columns it added are absent
     * from what `generate` compares against, and the next migration re-emits them. That migration
     * then fails on every database the first one already ran on, because `ADD COLUMN` is not
     * conditional. A hand-written migration needs a hand-written snapshot for the same reason a
     * generated one gets one for free.
     *
     * Both directions. A snapshot nothing claims is the same accident seen from the other side —
     * a migration deleted or renumbered without its snapshot, leaving `generate` to diff against a
     * schema no entry in the journal produces.
     */
    const snapshots = new Set(
      (await readdir(new URL("meta/", directory))).filter((name) =>
        name.endsWith("_snapshot.json"),
      ),
    );

    const entries = await readJournal();

    const missing = entries
      .map((entry) => ({ entry, snapshot: snapshotName(entry.idx) }))
      .filter(({ snapshot }) => !snapshots.has(snapshot))
      .map(({ entry, snapshot }) => `${entry.tag} has no meta/${snapshot}`);

    const claimed = new Set(entries.map((entry) => snapshotName(entry.idx)));
    const orphaned = [...snapshots]
      .filter((snapshot) => !claimed.has(snapshot))
      .map((snapshot) => `meta/${snapshot} belongs to no entry`)
      .sort();

    expect([...missing, ...orphaned]).toEqual([]);
  });

  test("has a snapshot that describes what its migration actually does", async () => {
    /*
     * Existing is not corresponding. The check above is satisfied by any file of the right name,
     * including last migration's snapshot copied forward and including one generated from a schema
     * this migration never produces — and a snapshot that lies is worse than one that is missing,
     * because a missing one at least re-emits statements that fail loudly, while a wrong one lets
     * `generate` emit a diff against a schema no database has ever been in.
     *
     * So read each migration against the pair of snapshots that bracket it and require the two to
     * say the same thing. Pure data migrations get held to this too, and get held to it hardest: a
     * backfill that moves no DDL must leave a snapshot with nothing changed in it.
     */
    const entries = await readJournal();

    const wrong: string[] = [];
    for (const entry of entries) {
      const sql = await readFile(
        new URL(`${entry.tag}.sql`, directory),
        "utf8",
      );
      const after = await readSnapshot(entry.idx);
      const before = entry.idx === 0 ? {} : await readSnapshot(entry.idx - 1);
      wrong.push(
        ...disagreements(sql, before, after).map(
          (problem) => `${entry.tag}: ${problem}`,
        ),
      );
    }

    expect(wrong).toEqual([]);
  });
});