artifacts/api-server/src/lib/capability/migrations/apply.ts

import { promises as fs } from "node:fs";
import path from "node:path";
import { fileURLToPath } from "node:url";
import { db } from "@workspace/db";
import { sql } from "drizzle-orm";
import { logger } from "../../logger.js";

/**
 * Task #242 (B1) — Idempotent SQL migration runner for capability schema.
 *
 * 读 `migrations/*.sql`(按文件名字典序),按 statement 拆分后用 raw SQL 执行。
 * 每个文件里的 DDL 都用 IF NOT EXISTS / CREATE OR REPLACE,所以重跑安全。
 *
 * 这是 architect review 要求的 "source-controlled DDL artifact" — 取代
 * 之前临时通过 lib/db tmp script 直接 raw SQL 入库的不可重现路径。
 */

const APPLIED = new Set<string>();

export async function applyCapabilityMigrations(): Promise<{
  applied: string[];
  skipped: string[];
}> {
  const here = path.dirname(fileURLToPath(import.meta.url));
  let entries: string[] = [];
  try {
    entries = await fs.readdir(here);
  } catch (err) {
    logger.warn({ err, here }, "capability migrations dir missing, skipping");
    return { applied: [], skipped: [] };
  }
  const sqlFiles = entries.filter((f) => f.endsWith(".sql")).sort();
  const applied: string[] = [];
  const skipped: string[] = [];
  for (const fname of sqlFiles) {
    if (APPLIED.has(fname)) {
      skipped.push(fname);
      continue;
    }
    const full = path.join(here, fname);
    const text = await fs.readFile(full, "utf8");
    // Strip line comments and split on `;` at end of statement. We do NOT
    // try to be a real SQL parser — every statement in our migrations is
    // a top-level DDL with no embedded semicolons.
    const statements = text
      .split(/\n/)
      .filter((line) => !line.trim().startsWith("--"))
      .join("\n")
      .split(";")
      .map((s) => s.trim())
      .filter((s) => s.length > 0);
    for (const stmt of statements) {
      try {
        await db.execute(sql.raw(stmt));
      } catch (err) {
        logger.error(
          { err, fname, stmtPreview: stmt.slice(0, 120) },
          "capability migration statement failed",
        );
        throw err;
      }
    }
    APPLIED.add(fname);
    applied.push(fname);
    logger.info({ fname, statementCount: statements.length }, "capability migration applied");
  }
  return { applied, skipped };
}