WFDASH-WEBAPP/scripts/migrations/2026-08-09-add-ai-system-prompt.ts

/**
 * Migration: add `system_prompt` text field to `ai_settings`.
 *
 * Background
 * ──────────
 * The quotes AI processor (`src/lib/server/quotes-ai-processor.ts`)
 * ships a hardcoded SYSTEM_PROMPT constant. Operators have wanted to
 * inspect and tweak the prompt without redeploying since the first
 * extraction went sideways. This migration makes it editable from
 * `/settings/ai`.
 *
 * Schema
 * ──────
 *   system_prompt   text   The prompt sent as `system:` to the Anthropic
 *                          API. Empty string means "use the hardcoded
 *                          default in the source" — that's the safe
 *                          behaviour because the field is added AFTER
 *                          the row already exists (so no operator
 *                          action is required and the processor keeps
 *                          working through the migration).
 *
 *                          options.max is generous (50 000 chars) so
 *                          multi-rule prompts fit comfortably; the
 *                          current hardcoded default is ~3 KB.
 *
 * viewRule
 * ────────
 * The ai_settings viewRule already strips api_key from non-superuser
 * reads at the service layer; system_prompt is intentionally NOT
 * gated — anyone who can see /settings/ai should be able to read and
 * edit the prompt.
 *
 * Idempotent — safe to re-run.
 *
 * Run with:
 *   npx tsx scripts/migrations/2026-08-09-add-ai-system-prompt.ts
 */
import PocketBase from "pocketbase";
import { readFileSync } from "node:fs";
import { resolve } from "node:path";

function loadEnvFile(name: string) {
  try {
    const raw = readFileSync(resolve(process.cwd(), name), "utf8");
    for (const line of raw.split("\n")) {
      const trimmed = line.trim();
      if (!trimmed || trimmed.startsWith("#")) continue;
      const m = trimmed.match(/^([A-Z0-9_]+)\s*=\s*(.+)$/i);
      if (m && !process.env[m[1]]) {
        process.env[m[1]] = m[2].replace(/^["']|["']$/g, "").trim();
      }
    }
  } catch {
    /* missing file — fine */
  }
}
loadEnvFile(".env");
loadEnvFile(".env.local");

async function main() {
  const url = process.env.NEXT_PUBLIC_POCKETBASE_URL;
  const email = process.env.PB_ADMIN_EMAIL;
  const password = process.env.PB_ADMIN_PASSWORD;
  if (!url || !email || !password) {
    throw new Error(
      "Set NEXT_PUBLIC_POCKETBASE_URL, PB_ADMIN_EMAIL and PB_ADMIN_PASSWORD.",
    );
  }

  const pb = new PocketBase(url);
  await pb.admins.authWithPassword(email, password);

  const existing = await pb.collections.getOne("ai_settings").catch(() => null);
  if (!existing) {
    throw new Error(
      "ai_settings collection missing — run scripts/migrations/2026-08-09-add-ai-settings.ts first.",
    );
  }

  const existingFields = (existing.fields ?? []) as Array<{
    name?: string;
  }>;
  if (existingFields.some((f) => f.name === "system_prompt")) {
    console.log("system_prompt field already present on ai_settings, skipping.");
    return;
  }

  const allFields = [
    ...(existingFields as Array<Record<string, unknown>>),
    {
      name: "system_prompt",
      type: "text",
      required: false,
      presentable: false,
      system: false,
      hidden: false,
      help: "System prompt sent to the Anthropic API. Empty = use the hardcoded default in the source. Max 50000 chars.",
      options: { max: 50000, min: 0, pattern: "" },
    },
  ];

  await pb.collections.update(existing.id, { fields: allFields });
  console.log("Added system_prompt field to ai_settings.");
}

main().catch((err) => {
  console.error(err);
  process.exit(1);
});