Skip to content

Self-hosted, Self-Improving, Federated Everything App — dev management, agent orchestration, persistent mind, digital twin, second brain, and creative pipeline from a single dashboard over Tailscale to port the user into the OS

34.6 KB
Raw Blame
/**
 * Database Connection Pool
 *
 * PostgreSQL connection management for the memory system.
 * Uses pg (node-postgres) with a connection pool for efficient query execution.
 */

import pg from 'pg';
import { AsyncLocalStorage } from 'node:async_hooks';
import { isTestRunner } from './runtimeEnv.js';
import { assertDatabaseAdmission, createDatabaseMaintenanceJournal } from './databaseMaintenanceJournal.js';
import { assertDatabasePoolAuthority } from './databaseAuthority.js';
import { createDatabaseRestoreRecovery, databaseRestoreRecoveryError } from './databaseRestoreRecovery.js';
import { normalizePgParams } from './pgJson.js';

const { Pool } = pg;

// DATE (OID 1082) ships from node-pg as a JS `Date` at LOCAL midnight, so a caller
// that reads it back with `toISOString().slice(0, 10)` (the ubiquitous `isoDate()`
// pattern — see tribe.js) gets the PREVIOUS day on every server east of UTC. Return
// the raw `YYYY-MM-DD` string Postgres sends instead: every consumer of a DATE
// column in this codebase already accepts (or, per `asDay()` in postRunDb.js,
// specifically tolerates) a plain day-key string (#8451).
pg.types.setTypeParser(1082, (value) => value);

if (!process.env.PGPASSWORD) {
  console.warn('⚠️ PGPASSWORD not set — using default. Set PGPASSWORD env var for production.');
}

// Connection config from environment or defaults. Exported so callers that
// need a SECOND, independent connection to the same database (a raw `pg`
// client outside the pool) don't have to re-derive these defaults and risk
// drifting from them — see server/lib/db.test.js.
// Capture node-postgres's supported TLS environment once, alongside the endpoint.
const poolTls = new pg.Client().connectionParameters;
export const POOL_CONFIG = Object.freeze({
  host: process.env.PGHOST || 'localhost',
  port: parseInt(process.env.PGPORT || '5432', 10),
  database: process.env.PGDATABASE || 'portos',
  user: process.env.PGUSER || 'portos',
  password: process.env.PGPASSWORD || 'portos',
  ssl: poolTls.ssl && typeof poolTls.ssl === 'object' ? Object.freeze({ ...poolTls.ssl }) : poolTls.ssl,
  sslnegotiation: poolTls.sslnegotiation || 'postgres',
});

const pool = new Pool({
  ...POOL_CONFIG,
  max: 20,
  idleTimeoutMillis: 30000,
  // 10s (was 2s) — a single-user box periodically runs heavy local workloads
  // (Ollama model pulls, CoS agents) that can briefly delay establishing a
  // fresh loopback connection past a 2s window, causing spurious "timeout
  // exceeded when trying to connect" pool errors against a perfectly healthy
  // Postgres. 10s absorbs the busy moments while still failing fast on a real outage.
  connectionTimeoutMillis: 10000
});

pool.on('error', (err) => {
  console.error(`🗄️ Database pool error: ${err.message}`);
});

// pg-pool only installs its error handler while a client is idle. Every
// checked-out client needs a temporary listener of its own because pg emits
// unexpected socket errors while a transaction or other operation is active.
// Keep one stable callback and remove it before release so client reuse cannot
// accumulate listeners.
function logCheckedOutClientError(err) {
  console.error(`🗄️ Checked-out database client error: ${err.message}`);
}

/**
 * Is the configured database a DESIGNATED test database?
 *
 * The test runner must NEVER touch a real (production) Postgres — there is only
 * ONE database per install (`PGDATABASE || 'portos'`), shared by every git
 * worktree, so a DB-backed `*.db.test.js` suite that does `DELETE FROM universes`
 * runs against the user's real authored content. (This is exactly how a CoS
 * agent running the suite in its worktree wiped every universe/series on
 * 2026-06-13.) A database is "safe for destructive tests" only when its name is
 * explicitly a test database (ends in `_test`, or the canonical `portos_test`)
 * or the operator sets `TEST_DB_OK=1` to opt a non-standard name in.
 *
 * Consumed by `checkHealth()` (skips DB suites on a non-test DB) and by the
 * destructive-statement guard in `query()` (a hard backstop).
 *
 * @returns {boolean}
 */
export function isTestDatabase() {
  if (process.env.TEST_DB_OK === '1') return true;
  const db = process.env.PGDATABASE || 'portos';
  return /_test$/.test(db) || db === 'portos_test';
}

// Guard ALL row writes — not just deletions. The original guard only blocked
// DELETE/TRUNCATE, which let a mis-pointed suite INSERT fixtures into prod
// (their cleanup DELETE then threw, *stranding* the rows) — exactly how test
// series/issues/story-builder fixtures leaked into the real `portos` DB and
// federated to peers. INSERT/UPDATE are now blocked too. Schema DDL
// (CREATE/ALTER/DROP) is still allowed: it is idempotent, carries no row-data,
// and ensureSchema() needs it to stand up portos_test.
//
// The first cut was `^`-anchored on a single leading verb, which let four
// less-obvious write forms slip past the "absolute backstop": data-modifying
// CTEs (`WITH … DELETE …`), `COPY … FROM` imports, `MERGE INTO`, and a write
// hiding after a leading read in a multi-statement batch (`SELECT 1; DELETE …`).
// No current store issues those forms, so this was latent rather than
// exploitable — but the guard is billed as the last line of defense, so it now
// matches a write verb ANYWHERE, after stripping comments so a keyword named in
// a comment can't trip it (and a write after a comment can't slip past).
//
// Normalize the SQL before keyword-matching in a SINGLE left-to-right pass that
// alternates over comments AND string literals, so whichever delimiter appears
// FIRST consumes its own span. Order matters and a two-pass "mask strings, then
// strip comments" is WRONG: an apostrophe inside a comment (`-- don't touch …`)
// would open a spurious string mask that swallows a real write on the next line
// (a false-negative — the exact failure this guard exists to prevent). Processing
// left-to-right keeps a comment's apostrophe part of the comment, and keeps a
// `/*` or `--` inside a string literal from starting a comment. Comments collapse
// to a space; string literals collapse to `''` (Postgres's escaped-quote form),
// so a write verb appearing only inside a literal can't trip the guard, and a
// quote-embedded comment delimiter can't hide a write between two literals.
// Dollar-quoted bodies (`$$…$$`, used in function definitions) are not unwrapped —
// stores don't send them, so this test-only backstop accepts that edge.
function normalizeSqlForMatch(text) {
  return text.replace(
    /--[^\n]*|\/\*[\s\S]*?\*\/|'(?:''|[^'])*'/g,
    (m) => (m[0] === "'" ? "''" : ' '),
  );
}

// CREATE TABLE … AS SELECT (CTAS) and the broader DDL family (CREATE/ALTER/DROP)
// are knowingly NOT matched: ensureSchema() needs DDL to stand up portos_test, and
// distinguishing a row-copying CTAS from a plain `CREATE TABLE … (col … GENERATED
// ALWAYS AS (…))` reliably needs paren-aware parsing this regex set deliberately
// avoids. No store issues CTAS, and the worst case on a mis-pointed run is a stray
// new table (schema pollution) rather than corruption of existing rows.
const ROW_WRITE_PATTERNS = [
  /\bINSERT\s+INTO\b/i,
  /\bDELETE\s+FROM\b/i,
  /\bMERGE\s+INTO\b/i,
  /\bTRUNCATE\b/i,
  // SELECT … INTO <table> creates and populates a new table (a real row write, not
  // DDL). Reads never carry a standalone INTO, so requiring SELECT before it keeps
  // this off ordinary queries; INSERT/MERGE INTO are caught by their own patterns.
  /\bSELECT\b[\s\S]+?\bINTO\b/i,
  // UPDATE … SET — the SET clause is what makes it a write, and distinguishes it
  // from a `SELECT … FOR UPDATE` / `FOR NO KEY UPDATE` row-lock read. `[^;]+?`
  // keeps the match inside one statement so `… FOR UPDATE; SET search_path …`
  // (a read followed by a session command) doesn't read as an UPDATE write.
  /\bUPDATE\b\s+[^;]+?\bSET\b/i,
  // COPY <table> FROM — an import writes rows. `COPY (query) TO` / `COPY <table>
  // TO` is an export (read): requiring the first non-blank token after COPY to be
  // a non-`(` char (`[^\s(]`) rejects the subquery-export form whose inner FROM
  // would otherwise match, and requiring FROM before the next `;` leaves `COPY
  // <table> TO …` (no FROM) alone. `[^\s(]` (not a `(?!\()` lookahead) is load-
  // bearing: a lookahead lets `\s+` backtrack and pass the assertion mid-whitespace
  // on `COPY   (SELECT … FROM …) TO`, so the export would false-positive.
  /\bCOPY\s+[^\s(][^;]*?\bFROM\b/i,
];

// True when the SQL performs a row write in any of the recognized forms.
function isRowWriteSql(text) {
  const sql = normalizeSqlForMatch(text);
  return ROW_WRITE_PATTERNS.some((re) => re.test(sql));
}

/**
 * Hard backstop: under the test runner, refuse to MUTATE a non-test database.
 * Throws (fail loudly) on any row write — INSERT/UPDATE/DELETE/TRUNCATE/MERGE,
 * COPY … FROM imports, data-modifying CTEs, and a write hidden in a multi-
 * statement batch — instead of silently writing (or stranding) real data. Reads
 * (SELECT, including COPY … TO exports) and schema DDL are left alone so
 * health/version probes and ensureSchema() still work.
 *
 * Shared by BOTH the pool `query()` wrapper and the per-transaction client in
 * `withTransaction()`. The transaction path is critical: nearly every store
 * mutation (updateAuthor, deleteAuthor, mergeAuthorsFromSync, universe runs,
 * catalog, writers-room) runs its writes through `client.query()` inside a
 * transaction — which talks to the raw pg client, NOT this module's `query()`.
 * Guarding only `query()` left that path wide open: a suite under VITEST that
 * reached any transaction write path wrote straight into the real `portos` DB.
 *
 * @param {string} text - SQL query text
 */
export function assertWriteAllowed(text) {
  if (
    isTestRunner() &&
    !isTestDatabase() &&
    typeof text === 'string' &&
    isRowWriteSql(text)
  ) {
    throw new Error(
      `🛑 Refusing to mutate non-test database '${process.env.PGDATABASE || 'portos'}' under the test runner. ` +
        `Point PGDATABASE at a *_test database (e.g. portos_test), gate the suite on requireDb(), or set TEST_DB_OK=1. Query: ${text.slice(0, 80)}`,
    );
  }
}

// Proxy handler that runs a pg client's row writes through assertWriteAllowed.
// Defined once at module scope (not per-transaction): the `get` trap receives
// the client as `target`, so a single shared handler guards every client
// withTransaction() wraps — no per-call handler/closure allocation. A Proxy
// (rather than an object-spread copy) is required because pg's client methods
// live on the prototype, which a spread would not carry.
const GUARDED_CLIENT_HANDLER = {
  get(target, prop, receiver) {
    if (prop === 'query') {
      return (config, ...rest) => {
        const sql = typeof config === 'string' ? config : config?.text;
        assertWriteAllowed(sql);
        // A Submittable (pg-cursor) also carries `values`; only a plain config is rewritten.
        if (typeof config === 'string' && Array.isArray(rest[0])) return target.query(config, normalizePgParams(rest[0]), ...rest.slice(1));
        if (Array.isArray(config?.values) && Object.getPrototypeOf(config) === Object.prototype) {
          return target.query({ ...config, values: normalizePgParams(config.values) }, ...rest);
        }
        return target.query(config, ...rest);
      };
    }
    const value = Reflect.get(target, prop, receiver);
    return typeof value === 'function' ? value.bind(target) : value;
  },
};

// Admission is synchronous, before any pool checkout. A maintenance operation
// closes admission and drains existing operations (including whole transactions)
// before touching the schema. Only its async context can run reconciliation.
const databaseContext = new AsyncLocalStorage();
const activeOperations = new Set();
let maintenanceActive = false;
// Durable fence for a committed snapshot restore whose repair is pending
// (#9725). It outlives this process: while the journal exists, only the
// maintenance context that adopted the matching operation may use the pool.
// Exported so the restore service writes the SAME journal this gate reads.
const restoreRecovery = createDatabaseRestoreRecovery();
export const databaseRestoreRecovery = restoreRecovery;

function maintenanceError() {
  return Object.assign(new Error('Database restore in progress; retry after it finishes.'), {
    status: 503, code: 'DATABASE_MAINTENANCE',
  });
}

// The restore operation this async context may act for, if any. Only a live
// maintenance context (or work it started while live) carries one.
const restoreRecoveryContext = (store) => (store?.active ? store.restoreRecoveryId : undefined);

async function databaseOperation(fn) {
  // A process-local restore context cannot bypass a durable cutover fence,
  // and a pool still naming a cutover's retired backend never reaches it.
  assertDatabaseAdmission();
  assertDatabasePoolAuthority(POOL_CONFIG);
  const parent = databaseContext.getStore();
  if (maintenanceActive && !parent?.active) throw maintenanceError();
  const restoreRecoveryId = restoreRecoveryContext(parent);
  if (!restoreRecoveryId) restoreRecovery.assertAdmission();
  const context = { active: true, restoreRecoveryId };
  const pending = databaseContext.run(context, async () => fn());
  activeOperations.add(pending);
  try {
    return await pending;
  } finally {
    context.active = false;
    activeOperations.delete(pending);
  }
}

/**
 * Drain admitted database work and reject new work until restore completes.
 *
 * A pending restore recovery journal refuses entry unless `restoreRecoveryId`
 * names that same operation. `fn` receives `{ adoptRestoreRecovery(id) }`, which
 * lets the restore that just published a journal keep using the database for
 * that operation only; admission for everyone else stays closed until the
 * journal is released.
 */
export async function withDatabaseMaintenance(fn, { restoreRecoveryId } = {}) {
  assertDatabaseAdmission();
  assertDatabasePoolAuthority(POOL_CONFIG);
  if (maintenanceActive || databaseContext.getStore()?.active) throw maintenanceError();
  if (restoreRecoveryId === undefined) restoreRecovery.assertAdmission();
  else if (restoreRecovery.read()?.id !== restoreRecoveryId) throw databaseRestoreRecoveryError();
  maintenanceActive = true;
  const context = { active: true, restoreRecoveryId };
  const adoptRestoreRecovery = (id) => {
    if (!context.active || restoreRecovery.read()?.id !== id) throw databaseRestoreRecoveryError();
    context.restoreRecoveryId = id;
  };
  try {
    // An admitted transaction can start nested work while draining. Track it
    // separately and repeat until that work also settles, even when its parent
    // did not await it. Async descendants lose admission when their operation ends.
    while (activeOperations.size) await Promise.allSettled([...activeOperations]);
    return await databaseContext.run(context, () => fn({ adoptRestoreRecovery }));
  } finally {
    context.active = false;
    context.restoreRecoveryId = undefined;
    maintenanceActive = false;
  }
}

/**
 * Execute a query against the connection pool.
 * @param {string} text - SQL query text with $1, $2, etc. placeholders
 * @param {Array} params - Parameter values; strings are repaired for PostgreSQL (see pgJson.js) and plain objects serialized, here and on the withTransaction client
 * @returns {Promise<pg.QueryResult>}
 */
export async function query(text, params) {
  // Hard backstop: refuse to mutate a non-test DB under the runner even if a
  // suite reaches query() without gating on checkHealth() first (a new test
  // author calls query('INSERT …') directly, or a backend selector mis-chose
  // Postgres because NODE_ENV wasn't 'test').
  assertWriteAllowed(text);
  return databaseOperation(() => pool.query(text, normalizePgParams(params)));
}

/**
 * Read the connected server's PostgreSQL major version (e.g. 17 for 17.10).
 *
 * `server_version_num` is an integer like 170010 → major = floor(n / 10000).
 * Used by the backup service to select a `pg_dump` whose major version is
 * >= the server's: pg_dump aborts with "server version mismatch" when it is
 * older than the server it dumps, which is the common Homebrew footgun where
 * an older `postgresql@NN` keg shadows the running server in PATH.
 *
 * @returns {Promise<number|null>} major version, or null if unreachable/unparseable
 */
export async function getServerMajorVersion() {
  const result = await query('SHOW server_version_num').catch(() => null);
  const num = parseInt(result?.rows?.[0]?.server_version_num, 10);
  if (!Number.isFinite(num)) return null;
  return Math.floor(num / 10000);
}

/**
 * Run a function inside a database transaction.
 * Auto-commits on success, rolls back on error.
 * @param {function(pg.PoolClient): Promise<T>} fn
 * @returns {Promise<T>}
 */
export async function withTransaction(fn) {
  return databaseOperation(() => runTransaction(fn));
}

async function runTransaction(fn) {
  const client = await pool.connect();
  client.on('error', logCheckedOutClientError);
  // Guard the client's row writes with the same backstop as query(). The raw
  // pg client bypasses query() entirely, so without this wrapper a test-runner
  // process pointed at the real `portos` DB writes through every store's
  // transaction path unguarded (see assertWriteAllowed). BEGIN/COMMIT/ROLLBACK
  // below call the raw client directly (they are not row writes). pg's
  // client.query accepts either a SQL string or a { text, values } config —
  // GUARDED_CLIENT_HANDLER reads the SQL out of both.
  const guardedClient = new Proxy(client, GUARDED_CLIENT_HANDLER);
  let began = false;
  let releaseErr;
  try {
    await client.query('BEGIN');
    began = true;
    const result = await fn(guardedClient);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    // Swallow a secondary ROLLBACK failure (closed socket, statement_timeout
    // abort, backend termination) so the caller sees the original fn/COMMIT
    // error instead of a masking "query on closed connection" from ROLLBACK.
    // Pass that original error to release() so pg destroys a damaged client
    // rather than returning it to the idle pool for the next checkout to inherit.
    releaseErr = err;
    if (began) await client.query('ROLLBACK').catch(() => {});
    throw err;
  } finally {
    client.removeListener('error', logCheckedOutClientError);
    client.release(releaseErr);
  }
}

/**
 * Check if the database is reachable and the schema is initialized.
 * @returns {Promise<{connected: boolean, hasSchema: boolean, error?: string}>}
 */
export async function checkHealth() {
  // Test-runner safety gate. Under the test runner the only database we permit
  // is a designated test database (see isTestDatabase). Reporting "disconnected"
  // here makes every DB-backed `*.db.test.js` suite skip via its existing
  // `if (!health.connected)` branch — instead of running DELETE FROM against the
  // developer's real universes/series/writing. Keyed on isTestRunner() (not bare
  // NODE_ENV) so a worktree run that left NODE_ENV unset is still gated. The
  // mocked checkHealth in memoryBackend.test.js / backup.test.js is unaffected
  // (it replaces this fn).
  if (isTestRunner() && !isTestDatabase()) {
    return {
      connected: false,
      hasSchema: false,
      hasCatalogSchema: false,
      error: `test runner blocked from non-test database '${process.env.PGDATABASE || 'portos'}' — point PGDATABASE at a *_test database (e.g. portos_test) or set TEST_DB_OK=1`,
    };
  }
  try {
    const result = await databaseOperation(() => pool.query(`
      SELECT
        EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'memories') AS has_memories,
        EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'memory_links') AS has_links,
        EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name = 'memories' AND column_name = 'sync_sequence') AS has_sync,
        EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'catalog_ingredients') AS has_catalog,
        EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'catalog_scraps') AS has_catalog_scraps
    `));
    const { has_memories, has_links, has_sync, has_catalog, has_catalog_scraps } = result.rows?.[0] ?? {};
    return {
      connected: true,
      hasSchema: has_memories && has_links && has_sync,
      hasCatalogSchema: has_catalog && has_catalog_scraps,
    };
  } catch (err) {
    console.error(`🗄️ Database health check failed: ${err.message}`);
    return { connected: false, hasSchema: false, hasCatalogSchema: false, error: err.message };
  }
}

/**
 * Fixed, read-only startup diagnostic through THIS process's actual pool.
 * This does not lift admission, execute caller SQL, advance the journal, or
 * establish that a later ordinary restart used this connection. Only the
 * verification-only entrypoint calls it; all ordinary operations stay fenced.
 */
export async function verifyDatabaseMaintenanceTarget(id) {
  const journal = createDatabaseMaintenanceJournal();
  const refuse = () => Object.assign(new Error('Database target verification refused.'), {
    code: 'DATABASE_TARGET_UNVERIFIED',
  });
  const readTarget = () => {
    const operation = journal.read();
    if (!operation || operation.id !== id || !['verifying', 'verified'].includes(operation.stage)) throw refuse();
    const target = operation.target;
    // Exact saved identity, including the pool's captured host/port; do not
    // infer an endpoint from current mode, mutable env, or a reachable peer.
    if (['host', 'port', 'database', 'user'].some(key => target[key] !== POOL_CONFIG[key])) throw refuse();
    return operation;
  };
  const operation = readTarget();
  if (isTestRunner() && !/_test$/.test(POOL_CONFIG.database) && process.env.TEST_DB_OK !== '1') throw refuse();
  const client = await pool.connect();
  client.on('error', logCheckedOutClientError);
  let discard = false;
  try {
    await client.query({ text: 'BEGIN READ ONLY', query_timeout: 10_000 });
    const { rows } = await client.query({ text: `
      SELECT current_database() AS database, current_user AS "user",
        EXISTS(SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'memories') AS has_memories,
        EXISTS(SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'memory_links') AS has_links,
        EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'memories' AND column_name = 'sync_sequence') AS has_sync,
        EXISTS(SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'catalog_ingredients') AS has_catalog,
        EXISTS(SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'catalog_scraps') AS has_catalog_scraps
    `, query_timeout: 10_000 });
    const health = rows?.[0];
    if (health?.database !== operation.target.database || health?.user !== operation.target.user
      || !['has_memories', 'has_links', 'has_sync', 'has_catalog', 'has_catalog_scraps'].every(key => health[key] === true)) throw refuse();
    // A query completing after journal damage/removal/replacement is not proof
    // for that new state. Preserve exactly the operation and stage we read.
    if (JSON.stringify(readTarget()) !== JSON.stringify(operation)) throw refuse();
    return { id, stage: operation.stage, target: operation.target.mode, targetHealthy: true, fenced: true };
  } finally {
    await client.query({ text: 'ROLLBACK', query_timeout: 10_000 }).catch(() => { discard = true; });
    client.removeListener('error', logCheckedOutClientError);
    client.release(discard);
  }
}

// In-flight dedup for ensureSchema(). At boot, two independent fire-and-forget
// callers can race: the Creative Director recovery scan (which lazily selects
// the PG backend and calls ensureSchema) and the boot DB gate. Running the
// idempotent DDL list concurrently can intermittently error or deadlock on
// Postgres system catalogs (concurrent CREATE TABLE/INDEX IF NOT EXISTS contend
// on pg_type / pg_class). Sharing one in-flight promise serializes them; it's
// retained as successful readiness after it settles so sequential store warms
// do not repeat the full DDL block.
//
// That dedup only covers callers IN THIS PROCESS. There is one Postgres per
// install shared by every process that opens it — the server, `portos-cos`,
// and every CoS agent worktree — so a restart during an update routinely
// overlaps an outgoing server (still finishing its shutdown) with an
// incoming one, both calling ensureSchema() at once. Each `DROP TRIGGER IF
// EXISTS` / `CREATE TRIGGER` pair (server/scripts/init-db.sql) is idempotent
// WITHIN one session but not atomic ACROSS sessions: A drops, B's drop is a
// no-op, A creates, B's create then throws "already exists" (#5977). A
// session-level Postgres advisory lock serializes the whole DDL block
// cluster-wide — a dropped connection releases it automatically, so a hard
// kill mid-boot can never strand a later boot waiting forever.
export const SCHEMA_DDL_ADVISORY_LOCK_KEY = 5977001;
let ensureSchemaInFlight = null;
let schemaEnsured = false;
// Every DB-backed store self-runs ensureSchema() when it warms its backend at
// boot (memory, creative-director, media index, catalog, universe/story/writers
// stores, pipeline series/issues, plus the boot DB gate). Those warm
// sequentially, so in-flight dedup alone cannot collapse them. Successful
// readiness lasts for this process; a restart still applies that version's
// complete upgrade list. Failed attempts remain retryable.
let schemaUpgradeLogged = false;

/**
 * Apply idempotent schema upgrades to an existing database.
 * Each statement uses IF NOT EXISTS so it's safe to run on every startup.
 * Add new ALTER TABLE statements here when the schema evolves.
 *
 * Concurrent calls share an execution; later calls reuse successful readiness.
 * `force` explicitly reapplies the current process's DDL for repair tooling.
 */
export async function ensureSchema(options = {}) {
  return databaseOperation(() => ensureSchemaReady(options));
}

/**
 * Owned function signatures declared by the schema DDL: name -> argument type
 * lists. Argument names, modes and defaults are stripped so each list can be
 * resolved through `to_regprocedure`.
 */
function ownedFunctionSignatures(statements) {
  const owned = new Map();
  const pattern = /CREATE\s+(?:OR\s+REPLACE\s+)?FUNCTION\s+(?:public\.)?([a-z_][a-z0-9_]*)\s*\(([^)]*)\)/gi;
  for (const sql of statements) {
    for (const [, name, rawArgs] of sql.matchAll(pattern)) {
      const types = rawArgs.split(',').map((arg) => arg.trim()).filter(Boolean).map((arg) => {
        const words = arg.replace(/\s+(?:DEFAULT|=)\s.*$/i, '').replace(/^(?:IN|INOUT|VARIADIC)\s+/i, '').split(/\s+/);
        return words.length > 1 ? words.slice(1).join(' ') : words[0];
      });
      if (!owned.has(name)) owned.set(name, []);
      owned.get(name).push(types);
    }
  }
  return owned;
}

// `CREATE OR REPLACE FUNCTION` only replaces the same argument list, so an
// overload another branch left behind survives a forced reset and can later
// collide with a restore. Drops public overloads of schema-owned names that no
// owned definition declares. Test database only: on a real install an unknown
// overload may belong to the operator.
async function dropStrayOwnedFunctionOverloads(client, statements) {
  for (const [name, signatures] of ownedFunctionSignatures(statements)) {
    const ownedOids = [];
    for (const types of signatures) {
      const { rows: [row] } = await client.query('SELECT to_regprocedure($1)::oid AS oid', [`public.${name}(${types.join(',')})`]);
      if (!row?.oid) { ownedOids.length = 0; break; }
      ownedOids.push(row.oid);
    }
    if (!ownedOids.length) continue;
    const { rows } = await client.query(
      `SELECT p.oid::regprocedure::text AS signature FROM pg_proc p
         JOIN pg_namespace n ON n.oid = p.pronamespace
        WHERE n.nspname = 'public' AND p.proname = $1 AND p.prokind = 'f' AND NOT (p.oid = ANY($2::oid[]))`,
      [name, ownedOids],
    );
    for (const { signature } of rows) {
      await client.query(`DROP FUNCTION ${signature}`);
      console.warn(`🧹 Dropped stray test-database function overload ${signature}`);
    }
  }
}

async function ensureSchemaReady({ force = false } = {}) {
  if (ensureSchemaInFlight) return ensureSchemaInFlight;
  if (schemaEnsured && !force) return;
  schemaEnsured = false;
  ensureSchemaInFlight = ensureSchemaImpl({ force }).then(() => { schemaEnsured = true; })
    .finally(() => { ensureSchemaInFlight = null; });
  return ensureSchemaInFlight;
}

async function ensureSchemaImpl({ force = false } = {}) {
  // The DDL composer is imported lazily, not at module load. `db.js` is reached
  // by ~400 server test files through pgFileFacade.js, and eagerly importing
  // ./db/schema/index.js pulled its 17 per-domain DDL modules into every one of
  // those import graphs — ~7k module instantiations per suite run, for
  // statement arrays only this function ever reads (#6009). Resolved BEFORE the
  // advisory lock below, so no other booting process waits on a module load,
  // and once per boot inside a path already awaiting Postgres, so it costs
  // nothing at runtime. vi.mock('./db/schema/index.js') still intercepts it —
  // the mock registry covers dynamic imports too, which is what
  // lib/db.test.js's bad-DDL injection relies on.
  const { buildUpgradeDdl, buildCatalogDdl } = await import('./db/schema/index.js');

  // ⚠️ Boot CREATE INDEX lock window. Every `CREATE INDEX IF NOT EXISTS` below
  // (and in the catalogDDL block, incl. the HNSW vector index on catalog_scraps)
  // runs as a plain, non-CONCURRENT build. The FIRST time an index materializes
  // on a table that ALREADY holds many rows, Postgres takes a SHARE lock that
  // blocks writes (INSERT/UPDATE/DELETE) to that table until the build finishes —
  // so an existing install upgrading into a new index sees a one-time write stall
  // at boot proportional to that table's row count (HNSW builds are the slowest).
  //   Why this is left as-is rather than switched to CONCURRENTLY:
  //   - Fresh installs (the common case) build every index on an EMPTY table, so
  //     the lock is effectively instant — there is nothing to block.
  //   - CREATE INDEX CONCURRENTLY cannot run inside a transaction block, needs its
  //     own retry/cleanup path (a failed CONCURRENT build leaves an INVALID index
  //     that must be dropped by hand), and roughly doubles build time — fragile to
  //     run unattended on every boot for a stall that only bites large-table upgrades.
  //   If a future index must land on a table known to already carry a large row
  //   count on existing installs, note that the standard db-migration runner
  //   (server/scripts/run-db-migrations.js) wraps every migration in a
  //   withTransaction() block, so CREATE INDEX CONCURRENTLY CANNOT run there
  //   either — it must be issued from a dedicated non-transactional path (a
  //   standalone maintenance script / manual step run outside any transaction).
  //   See docs/STORAGE.md ("Boot schema upgrades & lock windows").
  // Serialize the whole DDL block cluster-wide with a session-level advisory
  // lock — see SCHEMA_DDL_ADVISORY_LOCK_KEY above. Held on a single dedicated
  // client (NOT the pool) for the entire block, released in `finally` on both
  // the success and throw path. Session-level, not transaction-scoped: none of
  // this runs inside a transaction (the non-CONCURRENT index builds above are
  // deliberately not wrapped in one either — see the comment block above), so
  // nothing here may be wrapped in withTransaction()/BEGIN.
  const client = await pool.connect();
  client.on('error', logCheckedOutClientError);
  try {
    const { rows: [{ locked }] } = await client.query('SELECT pg_try_advisory_lock($1) AS locked', [SCHEMA_DDL_ADVISORY_LOCK_KEY]);
    if (!locked) {
      // Another process is already running this block — normal during a boot
      // overlap, but pg_advisory_lock's blocking wait is otherwise silent (no
      // lock_timeout applies to advisory locks), so a peer stalled mid-DDL
      // (e.g. the large-table index build documented above) would look like a
      // hung boot with zero log output.
      console.log('🗄️ Database schema DDL lock held by another process — waiting…');
      await client.query('SELECT pg_advisory_lock($1)', [SCHEMA_DDL_ADVISORY_LOCK_KEY]);
    }

    const upgrades = buildUpgradeDdl();
    for (const sql of upgrades) {
      await client.query(sql);
    }

    // Catalog block: every statement below is idempotent (CREATE IF NOT EXISTS
    // / CREATE OR REPLACE FUNCTION / DROP TRIGGER IF EXISTS + CREATE TRIGGER),
    // so we run the whole list once per process rather than gating on table
    // presence. A previous probe that early-returned on "all four tables exist"
    // would skip the indexes / functions / triggers if the prior boot crashed
    // between the table CREATEs and the artifact CREATEs — leaving the schema
    // marked ready while update triggers and HNSW indexes were never installed.
    // Cost on a fully-applied install is ~30 Postgres no-op parses (<10ms).

    const catalogDDL = buildCatalogDdl();

    for (const sql of catalogDDL) {
      await client.query(sql);
    }
    if (force && isTestDatabase()) await dropStrayOwnedFunctionOverloads(client, [...upgrades, ...catalogDDL]);
    if (!schemaUpgradeLogged) {
      console.log('🗄️ Database schema upgrades applied');
      schemaUpgradeLogged = true;
    }
  } finally {
    // A session-level advisory lock is tied to the CONNECTION, not the
    // checkout — release(false) alone would return a still-locked connection
    // to the pool, and every later ensureSchema() call (in this process AND
    // every other process, since the lock is cluster-wide) would then block
    // forever waiting on a lock nobody will ever release. If the unlock
    // itself fails, destroy the connection instead: closing it releases the
    // lock automatically, same as a hard kill mid-boot.
    const unlocked = await client.query('SELECT pg_advisory_unlock($1)', [SCHEMA_DDL_ADVISORY_LOCK_KEY]).then(() => true, () => false);
    client.removeListener('error', logCheckedOutClientError);
    client.release(!unlocked);
  }
}

/**
 * Gracefully shut down the pool.
 */
export async function close() {
  await pool.end();
}

/**
 * Convert pgvector string representation to float array.
 * pgvector returns vectors as '[0.1,0.2,...]' strings.
 */
export function pgvectorToArray(vec) {
  if (Array.isArray(vec)) return vec;
  if (typeof vec === 'string') {
    return vec.replace(/^\[|\]$/g, '').split(',').map(Number);
  }
  return null;
}

/**
 * Format a float array (or pgvector string) as pgvector literal '[0.1,0.2,...]'
 */
export function arrayToPgvector(arr) {
  if (!arr) return null;
  if (typeof arr === 'string') return arr;
  return `[${arr.join(',')}]`;
}