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

12.8 KB
Raw Blame
/**
 * Universe Builder — PostgreSQL leaf I/O (#1014).
 *
 * One row per universe in `universes`: the full sanitized record (canon bibles,
 * categories, compositeSheets, locks, influences) in `data` JSONB, with
 * name / schema_version / ephemeral / updated_at / deleted / deleted_at mirrored
 * into columns for the queries the service + federation actually run (rename
 * cascade, delete-guard, list sort, snapshot ephemeral-filter, LWW staleness).
 * Render-history runs live one-row-per-run in `universe_runs` (local-only,
 * capped at 200, never federated). Non-federation is a deliberate decision, not
 * an oversight: runs are a regenerable render cache under a 200-row GLOBAL cap
 * (trimmed in `appendRun`) that two peers would mutually evict, and the durable
 * universe record already federates. See ADR
 * docs/decisions/2026-06-26-tribe-and-universe-runs-local.md (#1724).
 *
 * This module is PURE leaf I/O — no in-process serialization, no sanitizing.
 * The store facade (store.js) owns the per-id write queue + the mutation epoch
 * and applies `sanitizeTemplate` on read, so reads here return `data` verbatim
 * (the columns are a queryable mirror, never read back). The facade serializes
 * the run RMWs, so the run ops here are single atomic statements/transactions.
 */

import { query, withTransaction } from '../../lib/db.js';
import { LOGLINE_MAX } from '../../lib/universeBibleLimits.js';
import { mirrorTimestamp } from '../../lib/pgTimestamp.js';
import { toPgJson } from '../../lib/pgJson.js';

// Keep the runs[] log bounded to the most-recent 200 — matches the file
// backend's cap (universeBuilder.recordRun) so behavior is identical.
const RUN_CAP = 200;

// --- Universe records ---

/** Raw on-disk-equivalent record (the `data` JSONB), or null. No sanitize. */
export async function readRaw(id) {
  const { rows } = await query(`SELECT data FROM universes WHERE id = $1`, [id]);
  return rows[0]?.data ?? null;
}

/** Every universe id (live, ephemeral, AND tombstones) — the service filters. */
export async function listIds() {
  const { rows } = await query(`SELECT id FROM universes`);
  return rows.map((r) => r.id);
}

/**
 * Live (non-deleted) universe ids only — id-only projection hitting
 * idx_universes_live. Backs tombstoneGc's LIVE_ID_LISTERS (the base-hash
 * orphan sweep), so a record stops protecting its base hash the moment it's
 * tombstoned without the sweep ever reading a universe's JSONB body.
 */
export async function listLiveIds() {
  const { rows } = await query(`SELECT id FROM universes WHERE deleted = FALSE`);
  return rows.map((r) => r.id);
}

/**
 * Tombstoned universe ids whose deletedAt is older than `beforeMs` (epoch ms) —
 * the GC candidate scan for `pruneTombstonedUniverses`. An id-only projection:
 * the sweep almost never finds a tombstone, so the common case must not pay
 * for a single universe's JSONB body, let alone every one of them.
 */
export async function listTombstoneIdsBefore(beforeMs) {
  const cutoffIso = new Date(beforeMs).toISOString();
  const { rows } = await query(
    `SELECT id FROM universes WHERE deleted = TRUE AND deleted_at IS NOT NULL AND deleted_at < $1`,
    [cutoffIso],
  );
  return rows.map((r) => r.id);
}

/**
 * Every record's raw `data` JSONB in one query (live/ephemeral/tombstones).
 * The bulk read behind listUniverses — one SELECT instead of N per-id reads.
 */
export async function listRaw() {
  const { rows } = await query(`SELECT data FROM universes`);
  return rows.map((r) => r.data);
}

/**
 * Live universes projected down to `{ id, name }`, unordered — the rows behind
 * `listUniverseNames`, for pickers that need a label and nothing else.
 *
 * Same reasoning as `listStyles` below and `countUniverses` further down: both
 * columns are exact mirrors of the JSONB (every `writeRaw` binds them from the
 * same record), and `deleted = FALSE` hits the `idx_universes_live` partial
 * index. Skips the bibles, the composite sheets, the whole `universe_runs`
 * table, and the per-record sanitize that make `listUniverses()` a
 * multi-megabyte read.
 */
export async function listNames() {
  const { rows } = await query(
    `SELECT id, name, to_char(created_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"') AS "createdAt" FROM universes WHERE deleted = FALSE`,
  );
  return rows;
}

/**
 * Index projection. Only legacy canon layouts need the full sanitizer to fold
 * category variations into canon; ordinary records return counts, not arrays.
 */
export async function listSummaries() {
  const { rows } = await query(
    `SELECT id, name,
            data->>'logline' AS logline,
            left(btrim(data->>'starterPrompt'), $1) AS "starterPrompt",
            data->'origin' AS origin,
            (data->>'factual')::boolean AS factual,
            data->>'createdAt' AS "createdAt",
            data->>'updatedAt' AS "updatedAt",
            data->'styleImageRefs'->>-1 AS "styleImageRef",
            (CASE WHEN jsonb_typeof(data->'characters') = 'array' THEN jsonb_array_length(data->'characters') ELSE 0 END
             + CASE WHEN jsonb_typeof(data->'places') = 'array' THEN jsonb_array_length(data->'places') ELSE 0 END
             + CASE WHEN jsonb_typeof(data->'objects') = 'array' THEN jsonb_array_length(data->'objects') ELSE 0 END) AS "canonCount",
            CASE WHEN (CASE WHEN jsonb_typeof(data->'schemaVersion') = 'number'
                            THEN (data->>'schemaVersion')::numeric < 4 ELSE TRUE END)
                      OR EXISTS (
                        SELECT 1 FROM jsonb_object_keys(
                          CASE WHEN jsonb_typeof(data->'categories') = 'object' THEN data->'categories' ELSE '{}'::jsonb END
                        ) AS category(key)
                        WHERE key ~* '^[^a-z0-9]*characters?([^a-z0-9]|$)'
                      )
                 THEN jsonb_set(data, '{starterPrompt}', to_jsonb(left(btrim(COALESCE(data->>'starterPrompt', '')), $1)))
                 ELSE NULL END AS "legacyRecord"
       FROM universes WHERE deleted = FALSE`,
    [LOGLINE_MAX],
  );
  return rows;
}

/**
 * Live universes projected down to the style-bearing fields, unordered — the
 * raw input `listUniverseStyles` derives its token lists from. See that
 * function for what the projection is for and which fields feed it.
 *
 * `deleted = FALSE` on the mirror column, for the same reasons spelled out on
 * `countUniverses` below (exact mirror of the JSONB flag, and the form
 * `idx_universes_live` can serve). `name` reads the mirror column too — every
 * `writeRaw` binds it from the same `record.name` that lands in `data`.
 * Ordering is the service's job so both backends can't drift on it.
 */
export async function listStyles() {
  const { rows } = await query(
    `SELECT id, name,
            data->'influences' AS influences,
            data->>'stylePrompt' AS "stylePrompt",
            data->>'negativePrompt' AS "negativePrompt",
            data->>'createdAt' AS "createdAt"
       FROM universes
      WHERE deleted = FALSE`,
  );
  return rows;
}

/**
 * Cheap live-universe tally (#2729) — the count listUniverses().length used to
 * pay for by materializing every JSONB record AND the whole run history.
 *
 * Reads the `deleted` MIRROR COLUMN, not the JSONB flag listUniverses filters
 * on. Those cannot disagree: every write path derives both from the same
 * `record` in the same statement with the same predicate — `writeRaw` above and
 * the one-time file import (`server/scripts/migrateUniversesToDB.js`) each bind
 * `record.deleted === true` into the column, and the sanitizer the service
 * filters on (`sanitizeSoftDeleteFields`, lib/syncWire.js) computes
 * `raw?.deleted === true` off the same field. There is no third writer, and no
 * UPDATE that touches `deleted` without rewriting `data`. So the column is an
 * exact mirror, and `WHERE deleted = FALSE` also hits the `idx_universes_live`
 * partial index that exists for precisely this "live set" scan.
 *
 * Deliberately does NOT filter `ephemeral` — listUniverses() counts ephemeral
 * universes too (it filters ONLY on `deleted`), so filtering here would
 * undercount.
 *
 * `deleted = FALSE` (not `IS NOT TRUE`) is intentional on both counts: it is the
 * form the partial index can serve, and it matches every sibling live-set query
 * (tribe.js, catalogRefResolver.js, catalogDB/shared.js). The column is nullable
 * (`BOOLEAN DEFAULT FALSE`), so the two forms differ on a NULL — but neither
 * writer can produce one (both always bind a boolean; a column-omitting INSERT
 * takes the FALSE default), so the distinction is unreachable.
 *
 * Known boundary: a row whose `data` can't survive `sanitizeTemplate` (blank
 * `name`, non-string `id`) is dropped by listUniverses's `.filter(Boolean)` but
 * still counted here. Unreachable through the service — create/update/sync all
 * sanitize before writing — and only a corrupt legacy record imported verbatim
 * could hit it. Replicating the sanitizer's drop rules in SQL would couple this
 * count to sanitizeTemplate's internals and silently drift when they change;
 * a ±1 on an already-corrupt record is not worth that. The file backend's
 * counterpart (store.js) has the same semantics, so the two agree.
 */
export async function countUniverses({ includeDeleted = false } = {}) {
  const { rows } = includeDeleted
    ? await query(`SELECT COUNT(*) AS count FROM universes`)
    : await query(`SELECT COUNT(*) AS count FROM universes WHERE deleted = FALSE`);
  return parseInt(rows[0].count, 10);
}

/**
 * Upsert one record. `data` is written verbatim (lossless); the typed mirror
 * columns are bind-sanitized so a hand-edited/legacy record with a malformed
 * timestamp or missing field can't make the write throw (which, during the boot
 * warm/import, would block the whole backend). `created_at` is preserved on
 * conflict — only the first INSERT sets it.
 */
export async function writeRaw(id, record) {
  const now = new Date().toISOString();
  const createdAt = mirrorTimestamp(record?.createdAt, now);
  const schemaVersion = Number.isInteger(record?.schemaVersion) ? record.schemaVersion : 5;
  await query(
    `INSERT INTO universes (id, name, data, schema_version, ephemeral, created_at, updated_at, deleted, deleted_at)
     VALUES ($1, $2, $3::jsonb, $4, $5, $6, $7, $8, $9)
     ON CONFLICT (id) DO UPDATE SET
       name = EXCLUDED.name,
       data = EXCLUDED.data,
       schema_version = EXCLUDED.schema_version,
       ephemeral = EXCLUDED.ephemeral,
       updated_at = EXCLUDED.updated_at,
       deleted = EXCLUDED.deleted,
       deleted_at = EXCLUDED.deleted_at`,
    [
      id,
      typeof record?.name === 'string' ? record.name : '',
      toPgJson(record),
      schemaVersion,
      record?.ephemeral === true,
      createdAt,
      mirrorTimestamp(record?.updatedAt, createdAt),
      record?.deleted === true,
      mirrorTimestamp(record?.deletedAt, null),
    ],
  );
  return record;
}

/** Hard-delete a record (tombstone GC). Idempotent — missing row is a no-op. */
export async function deleteRaw(id) {
  await query(`DELETE FROM universes WHERE id = $1`, [id]);
}

// --- Render-history runs (local-only) ---

/** Runs (newest first), optionally scoped to one universe. Returns `data` verbatim. */
export async function loadRuns(universeId = null) {
  const { rows } = universeId
    ? await query(
        `SELECT data FROM universe_runs WHERE universe_id = $1 ORDER BY created_at DESC, id DESC`,
        [universeId],
      )
    : await query(`SELECT data FROM universe_runs ORDER BY created_at DESC, id DESC`);
  return rows.map((r) => r.data);
}

/**
 * Append one run + trim the global log back to the most-recent RUN_CAP. Runs
 * in a transaction so a concurrent read never sees the over-cap intermediate.
 */
export async function appendRun(run) {
  await withTransaction(async (client) => {
    const exec = client.query.bind(client);
    await exec(
      `INSERT INTO universe_runs (id, universe_id, collection_id, data, created_at)
       VALUES ($1, $2, $3, $4::jsonb, $5)
       ON CONFLICT (id) DO NOTHING`,
      [
        run.id,
        run.universeId,
        typeof run.collectionId === 'string' ? run.collectionId : null,
        toPgJson(run),
        mirrorTimestamp(run.createdAt, new Date().toISOString()),
      ],
    );
    // Trim to the newest RUN_CAP across ALL universes (matches the file
    // backend's whole-array cap, not a per-universe cap).
    await exec(
      `DELETE FROM universe_runs WHERE id IN (
         SELECT id FROM universe_runs ORDER BY created_at DESC, id DESC OFFSET $1
       )`,
      [RUN_CAP],
    );
  });
}

/** Drop every run referencing any of `universeIds` (cascade on universe delete). */
export async function removeRunsForUniverses(universeIds) {
  const ids = Array.isArray(universeIds) ? universeIds.filter((x) => typeof x === 'string' && x) : [];
  if (ids.length === 0) return;
  await query(`DELETE FROM universe_runs WHERE universe_id = ANY($1::text[])`, [ids]);
}