spb/skills-spboucher-ai

1import { createHash } from "node:crypto";2import { readdirSync, readFileSync } from "node:fs";3import { dirname, join } from "node:path";4import { fileURLToPath } from "node:url";5import type { Sql } from "./index";67export const MIGRATIONS_DIR = join(dirname(fileURLToPath(import.meta.url)), "..", "migrations");89/** Forward-only, checksummed SQL migrations. A changed, already-applied file aborts the run. */10export async function migrate(sql: Sql, dir: string = MIGRATIONS_DIR): Promise<string[]> {11  await sql`CREATE TABLE IF NOT EXISTS schema_migrations (name text PRIMARY KEY, checksum text NOT NULL, applied_at timestamptz NOT NULL DEFAULT now())`;12  const applied = new Map((await sql<{ name: string; checksum: string }[]>`SELECT name, checksum FROM schema_migrations`).map((r) => [r.name, r.checksum]));13  const done: string[] = [];14  for (const name of readdirSync(dir).filter((f) => /^\d{4}_.+\.sql$/.test(f)).sort()) {15    const body = readFileSync(join(dir, name), "utf8");16    const checksum = createHash("sha256").update(body).digest("hex");17    const prev = applied.get(name);18    if (prev) {19      if (prev !== checksum) throw new Error(`migration ${name} was modified after being applied; write a new migration instead`);20      continue;21    }22    await sql.begin(async (tx) => {23      await tx.unsafe(body);24      await tx`INSERT INTO schema_migrations (name, checksum) VALUES (${name}, ${checksum})`;25    });26    done.push(name);27  }28  return done;29}30