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.2 KB
Raw Blame
/**
 * Creative Ingredients Catalog — shared leaf module.
 *
 * Row→object mappers, id-minters, the bible-payload sanitizer, revision
 * constants, and the cross-seam HOMING SQL fragments used by both the
 * ingredient list filters and the facets bucket counts. This is the acyclic
 * leaf: every other `catalogDB/*` seam imports from here, nothing here imports
 * from a sibling seam. See the `catalogDB.js` barrel for the full layout.
 */

import { randomUUID } from 'crypto';
import { pgvectorToArray } from '../../lib/db.js';
import { ingredientIdPrefix } from '../../lib/catalogTypes.js';
import { sanitizeCharacter, sanitizePlace, sanitizeObject } from '../../lib/storyBible.js';

// Story-bible sanitizers keyed by catalog type, for the registry
// `extractionShape: 'bible'` types ONLY (character/place/object). The catalog
// payload is the bible-entry shape MINUS the columns the row owns (id / name /
// timestamps) — so we sanitize a synthetic entry `{ name, ...payload }`,
// preserve timestamps, and strip the control fields back out before persist.
// Scoped strictly to bible types: idea/scene/concept AND user-defined types
// pass through untouched (running a user type's payload through a storyBible
// sanitizer would silently drop every field it doesn't recognize).
export const BIBLE_SANITIZERS = Object.freeze({
  character: sanitizeCharacter,
  place: sanitizePlace,
  object: sanitizeObject,
});

// Control fields the sanitizer stamps onto an entry that are NOT part of the
// catalog `payload` (the row owns id/name/timestamps as columns). Stripped from
// the sanitized entry so the stored payload stays shape-stable across the
// catalog↔canon projection round-trip.
const SANITIZED_NON_PAYLOAD_KEYS = ['id', 'name', 'createdAt', 'updatedAt'];

/**
 * Run a bible-type ingredient payload through its storyBible sanitizer so the
 * structured array editors (color palette / stats / aliases) can't land
 * malformed rows and the projection round-trip is shape-stable. Returns the
 * sanitized payload (control fields stripped). Non-bible types (idea/scene/
 * concept, user-defined) return their payload UNCHANGED. `preserveTimestamps`
 * keeps the sanitizer from minting fresh ids/timestamps on an existing row.
 */
export function sanitizeBiblePayload(type, name, payload) {
  const sanitizer = BIBLE_SANITIZERS[type];
  if (!sanitizer) return payload && typeof payload === 'object' ? payload : {};
  const entry = { ...(payload && typeof payload === 'object' ? payload : {}), name: String(name || '').trim() };
  const sane = sanitizer(entry, { preserveTimestamps: true });
  if (!sane) return payload && typeof payload === 'object' ? payload : {};
  const out = { ...sane };
  for (const k of SANITIZED_NON_PAYLOAD_KEYS) delete out[k];
  // The storyBible sanitizer only emits known bible fields, so it drops
  // `schemaVersion`. Preserve the incoming marker (createIngredient re-stamps
  // it anyway; updateIngredient writes payload verbatim, and the restore route
  // relies on the marker round-tripping) so a bible payload keeps its shape
  // version through the sanitize pass.
  if (payload && typeof payload === 'object' && payload.schemaVersion !== undefined) {
    out.schemaVersion = payload.schemaVersion;
  }
  return out;
}

export function newIngredientId(type) {
  return `cat-${ingredientIdPrefix(type)}-${randomUUID()}`;
}

export function newScrapId() {
  return `cat-scrap-${randomUUID()}`;
}

export function newRevisionId() {
  return `cat-rev-${randomUUID()}`;
}

// Cap on how many revision rows we keep per ingredient. Configurable via
// CATALOG_REVISION_RETENTION (env) so an install that wants a deeper audit
// trail can raise it; default 50 bounds unbounded growth from AI refine loops
// or rapid manual edits. A non-positive / non-numeric value falls back to 50.
export const CATALOG_REVISION_RETENTION = (() => {
  const raw = parseInt(process.env.CATALOG_REVISION_RETENTION, 10);
  return Number.isInteger(raw) && raw > 0 ? raw : 50;
})();

// The four revision sources, mirrored from the DB CHECK constraint. 'user' is
// the default (manual detail-page edit); 'extract' for ingest commits, 'refine'
// for AI refinement passes, 'sync' for peer-apply changes.
export const REVISION_SOURCES = new Set(['user', 'extract', 'refine', 'sync']);


export function rowToScrap(row) {
  if (!row) return null;
  return {
    id: row.id,
    title: row.title,
    rawText: row.raw_text,
    sourceKind: row.source_kind,
    metadata: row.metadata || {},
    embedding: row.embedding ? pgvectorToArray(row.embedding) : null,
    embeddingModel: row.embedding_model,
    originInstanceId: row.origin_instance_id,
    chunkIndex: row.chunk_index ?? 0,
    parentScrapId: row.parent_scrap_id ?? null,
    createdAt: row.created_at.toISOString(),
    updatedAt: row.updated_at.toISOString(),
    deleted: !!row.deleted,
    deletedAt: row.deleted_at?.toISOString() ?? null,
    syncSequence: String(row.sync_sequence),
  };
}

export function rowToIngredient(row) {
  if (!row) return null;
  return {
    id: row.id,
    type: row.type,
    name: row.name,
    payload: row.payload || {},
    tags: row.tags || [],
    embedding: row.embedding ? pgvectorToArray(row.embedding) : null,
    embeddingModel: row.embedding_model,
    originInstanceId: row.origin_instance_id,
    createdAt: row.created_at.toISOString(),
    updatedAt: row.updated_at.toISOString(),
    deleted: !!row.deleted,
    deletedAt: row.deleted_at?.toISOString() ?? null,
    syncSequence: String(row.sync_sequence),
  };
}

// #8347: `updatedAt` is the tombstone/revival change-clock (see
// catalog.js's ref/relation/media trigger comments) — falls back to
// `created_at` for a row whose `updated_at` column predates the backfill
// migration having run (should not happen post-boot, but keeps the mapper
// total rather than emitting an invalid date string).
export function rowToRef(row) {
  if (!row) return null;
  return {
    ingredientId: row.ingredient_id,
    refKind: row.ref_kind,
    refId: row.ref_id,
    role: row.role,
    createdAt: row.created_at.toISOString(),
    deleted: !!row.deleted,
    deletedAt: row.deleted_at?.toISOString() ?? null,
    updatedAt: (row.updated_at ?? row.created_at).toISOString(),
    syncSequence: String(row.sync_sequence),
  };
}

export function rowToRelation(row) {
  if (!row) return null;
  return {
    fromId: row.from_id,
    toId: row.to_id,
    kind: row.kind,
    createdAt: row.created_at.toISOString(),
    deleted: !!row.deleted,
    deletedAt: row.deleted_at?.toISOString() ?? null,
    updatedAt: (row.updated_at ?? row.created_at).toISOString(),
    syncSequence: String(row.sync_sequence),
  };
}

export function rowToMedia(row) {
  if (!row) return null;
  return {
    ingredientId: row.ingredient_id,
    ...(row.identity_retained ? { retained: true } : {}),
    mediaKey: row.media_key,
    kind: row.kind,
    role: row.role ?? null,
    caption: row.caption ?? null,
    metadata: row.metadata && typeof row.metadata === 'object' && !Array.isArray(row.metadata) ? row.metadata : {},
    library: row.library && typeof row.library === 'object' && !Array.isArray(row.library) ? row.library : {},
    createdAt: row.created_at.toISOString(),
    deleted: !!row.deleted,
    deletedAt: row.deleted_at?.toISOString() ?? null,
    updatedAt: (row.updated_at ?? row.created_at).toISOString(),
    syncSequence: String(row.sync_sequence),
  };
}

export function rowToSource(row) {
  if (!row) return null;
  return {
    ingredientId: row.ingredient_id,
    scrapId: row.scrap_id,
    span: row.span,
    extractedAt: row.extracted_at.toISOString(),
    syncSequence: String(row.sync_sequence),
  };
}

export function rowToTag(row) {
  if (!row) return null;
  return {
    id: row.id,
    label: row.label,
    description: row.description ?? null,
    color: row.color ?? null,
    parentId: row.parent_id ?? null,
    createdAt: row.created_at.toISOString(),
    updatedAt: row.updated_at.toISOString(),
    syncSequence: String(row.sync_sequence),
  };
}

export function rowToRevision(row) {
  if (!row) return null;
  return {
    id: row.id,
    ingredientId: row.ingredient_id,
    name: row.name,
    payload: row.payload || {},
    tags: row.tags || [],
    source: row.source,
    actor: row.actor ?? null,
    createdAt: row.created_at.toISOString(),
  };
}

// Group the rows of a batched `WHERE ingredient_id = ANY($1)` fetch into a
// `Map<ingredientId, mapped[]>`, preserving the SQL result order within each
// group (so a per-ingredient ORDER BY carries through the batching unchanged).
// `ids` seeds the map so every requested id has an entry — a caller can read
// `map.get(id)` without a `?? []` fallback and an ingredient with no rows still
// gets `[]` rather than `undefined`.
export function groupRowsByIngredient(ids, rows, mapRow) {
  const grouped = new Map(ids.map((id) => [id, []]));
  for (const row of rows) {
    const bucket = grouped.get(row.ingredient_id);
    // Defensive: a row for an id we didn't ask for can't come back from
    // `= ANY($1)`, but don't drop it on the floor if the query ever widens.
    if (bucket) bucket.push(mapRow(row));
    else grouped.set(row.ingredient_id, [mapRow(row)]);
  }
  return grouped;
}

// Ref kinds that "home" an ingredient — i.e. drive the linked / unlinked (Raw) /
// orphaned bucket classification + the Orphaned album. universe/series define
// album membership; creative-director joined this set in #1812 so a CD project's
// soft-delete surfaces its ex-cast as orphaned (recoverable) rather than leaving
// dangling chips. issue/work refs CONSUME ingredients but don't home them, so
// they stay out. Each kind soft-deletes its target (`deleted = FALSE` =
// live in REF_TARGET_TABLES, catalogRefResolver.js).
export const HOMING_REF_KINDS = ['universe', 'series', 'creative-director'];
export const HOMING_REF_KINDS_SQL = HOMING_REF_KINDS.map((k) => `'${k}'`).join(', ');

// EXISTS predicate (over the `r` alias) that the named ref points at a LIVE
// target of its kind. One source of truth for both HAS_LIVE_HOMING_REF and the
// facets bucket's live_count FILTER — keep the two call sites in sync by reusing
// this rather than re-inlining the per-kind clauses.
export const liveHomingTargetSql = (r) => `(
       (${r}.ref_kind = 'universe'          AND EXISTS (SELECT 1 FROM universes u                  WHERE u.id = ${r}.ref_id AND u.deleted = false))
    OR (${r}.ref_kind = 'series'            AND EXISTS (SELECT 1 FROM pipeline_series s            WHERE s.id = ${r}.ref_id AND s.deleted = false))
    OR (${r}.ref_kind = 'creative-director' AND EXISTS (SELECT 1 FROM creative_director_projects p WHERE p.id = ${r}.ref_id AND p.deleted = false)))`;

// The identity read views are built from CTEs, UNION ALL and plan JSON, so a
// predicate correlated on `catalog_canonical_ingredient_id(r.ingredient_id) =
// catalog_ingredients.id` cannot use an index and re-evaluates the whole view
// once per outer row (#12989: 35 s for the Orphaned album). A statement that
// filters on refs adds this CTE so the view is evaluated once.
export const CANONICAL_REFS_CTE = `canonical_refs AS MATERIALIZED (
    SELECT catalog_canonical_ingredient_id(ingredient_id) AS cid, ref_kind, ref_id
      FROM catalog_identity_read_refs WHERE deleted = false)`;

// `<expr> IN (<uncorrelated subquery>)` evaluated as ONE hashed subplan. A bare
// top-level IN is pulled up into a semi-join, and the read views' tiny row
// estimates make the planner pick a nested loop that rescans the subquery once
// per outer row — quadratic again (#12989). Wrapping it in IS TRUE keeps it
// a hashed subplan with the old EXISTS semantics: a NULL from IN (a NULL in the
// set) reads as false, so NOT (inSet(…)) also matches the old NOT EXISTS.
export const inSet = (expr, subquery) => `(${expr} IN (${subquery})) IS TRUE`;

// Predicates over CANONICAL_REFS_CTE (the statement must include it).
// HAS_LIVE_HOMING_REF: at least one homing ref whose target still resolves.
export const HAS_LIVE_HOMING_REF = inSet('id', `SELECT r.cid FROM canonical_refs r WHERE ${liveHomingTargetSql('r')}`);

// At least one homing ref row exists (live OR dangling) — distinguishes
// "orphaned" (has a ref, none resolve) from "unlinked" (no ref at all).
export const HAS_ANY_HOMING_REF = inSet('id', `SELECT r.cid FROM canonical_refs r WHERE r.ref_kind IN (${HOMING_REF_KINDS_SQL})`);