scripts/db/migrations.ts

Linked repository file from this build.

import { readFileSync } from "node:fs";
import { resolve } from "node:path";
import { readMigrationFiles } from "drizzle-orm/migrator";
import { getTableConfig } from "drizzle-orm/pg-core";
import type { Client } from "pg";
import {
  reviewSnapshots,
  reviewIndex,
  reviewTouches,
  inboxEvents,
  inboxVisits,
  snapPolicyTables,
} from "../../src/lib/db/schema";

export const migrationsFolder = resolve(import.meta.dirname, "../../drizzle");
export function migrationFiles(folder = migrationsFolder) {
  const journal = JSON.parse(readFileSync(resolve(folder, "meta/_journal.json"), "utf8")) as {
    entries: { tag: string; when: number }[];
  };
  return readMigrationFiles({ migrationsFolder: folder }).map((migration, i) => ({
    ...migration,
    tag: journal.entries[i]!.tag,
  }));
}
export type Migration = ReturnType<typeof migrationFiles>[number];
export type AppliedMigration = { hash: string; created_at: string };
export function migrationPlan(files: Migration[], applied: AppliedMigration[]) {
  const issues: string[] = [];
  const warnings: string[] = [];
  const latest = Math.max(0, ...applied.map((row) => Number(row.created_at)));
  for (const row of applied) {
    const file = files.find((entry) => entry.folderMillis === Number(row.created_at));
    if (!file) issues.push(`Database records an unknown migration timestamp ${row.created_at}.`);
    else if (file.hash !== row.hash)
      warnings.push(
        `${file.tag}.sql differs from the applied migration hash; Drizzle will not rerun it.`,
      );
  }
  for (const file of files)
    if (
      file.folderMillis <= latest &&
      !applied.some((row) => Number(row.created_at) === file.folderMillis)
    )
      issues.push(
        `${file.tag}.sql is missing from history but would be skipped by Drizzle's timestamp cursor.`,
      );
  return {
    issues,
    warnings,
    pending: files.filter((file) => file.folderMillis > latest).map((file) => file.tag),
  };
}

export async function databaseStatus(client: Client, files: Migration[]) {
  const ledger = await client.query<{ present: string | null }>(
    "select to_regclass('drizzle.__drizzle_migrations')::text as present",
  );
  const applied = ledger.rows[0]!.present
    ? (
        await client.query<AppliedMigration>(
          "select hash, created_at from drizzle.__drizzle_migrations order by created_at",
        )
      ).rows
    : [];
  const plan = migrationPlan(files, applied);
  const columns = (
    await client.query<{ table_name: string; column_name: string }>(
      "select table_name, column_name from information_schema.columns where table_schema = 'public'",
    )
  ).rows;
  const expected = [
    reviewSnapshots,
    reviewIndex,
    reviewTouches,
    inboxEvents,
    inboxVisits,
    snapPolicyTables,
  ].map(getTableConfig);
  const missingColumns = expected.flatMap((table) =>
    table.columns
      .filter(
        (column) =>
          !columns.some((row) => row.table_name === table.name && row.column_name === column.name),
      )
      .map((column) => `${table.name}.${column.name}`),
  );
  if (
    !applied.length &&
    expected.some((table) => columns.some((row) => row.table_name === table.name))
  )
    plan.issues.push(
      "Application tables exist without migration history. Reconcile the baseline before applying migrations; no history will be fabricated.",
    );
  return { applied: applied.length, ...plan, missingColumns };
}