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

6.8 KB
Raw Blame
/**
 * Catalog ref resolver + dangling-ref integrity (#1018).
 *
 * `catalog_ingredient_refs` records which app-native record (universe / series /
 * issue / work / creative-director / music-video) consumes a catalog ingredient, by a
 * `(ref_kind, ref_id)` STRING tuple with NO database foreign key — deliberately
 * (design #999, decision D3): a ref can federate to a peer BEFORE its target
 * arrives, and a hard FK would make catalog sync FAIL on out-of-order apply.
 *
 * Now that every target kind is DB-native (universes #1014, pipeline
 * series/issues #1015, writers_room_works #1017, creative_director_projects
 * #997, music_video_projects), this module delivers the integrity #999 asked
 * for WITHOUT a constraint:
 *
 *   - resolveRefs(tuples)          → which tuples point at a LIVE target
 *   - listDanglingRefs()           → live refs whose target no longer resolves
 *                                    (mirrors the media `metadata-missing` check)
 *
 * Read-only + local — it never touches the wire. The existing
 * `idx_catalog_ing_refs_target (ref_kind, ref_id)` index already makes the
 * reverse lookup cheap, so no schema change is required.
 */

import { query } from '../lib/db.js';

// ref_kind → the DB table its ref_id points at, plus the predicate that makes a
// row a LIVE target. Every target kind soft-deletes (a `deleted` column;
// creative_director_projects gained one in #1564 so its deletion federates), so
// a live target is `deleted = FALSE` in every case. This map is the single
// source of truth for the resolver — adding a new referenceable record kind
// means adding one row here.
export const REF_TARGET_TABLES = Object.freeze({
  universe: { table: 'universes', liveClause: 'deleted = FALSE' },
  series: { table: 'pipeline_series', liveClause: 'deleted = FALSE' },
  issue: { table: 'pipeline_issues', liveClause: 'deleted = FALSE' },
  work: { table: 'writers_room_works', liveClause: 'deleted = FALSE' },
  'creative-director': { table: 'creative_director_projects', liveClause: 'deleted = FALSE' },
  'music-video': { table: 'music_video_projects', liveClause: 'deleted = FALSE' },
});

/** The ref kinds this resolver can resolve (those with a known target table). */
export const RESOLVABLE_REF_KINDS = Object.freeze(Object.keys(REF_TARGET_TABLES));

// SELECT 1 … existence probe for one target, honoring its live predicate.
function targetExistsSql(refKind) {
  const target = REF_TARGET_TABLES[refKind];
  if (!target) return null;
  const where = target.liveClause ? `id = $1 AND ${target.liveClause}` : 'id = $1';
  return `SELECT EXISTS(SELECT 1 FROM ${target.table} WHERE ${where}) AS ok`;
}

// Cache key for a (refKind, refId) pair. JSON-stringify so the two fields are
// joined by an unambiguous, printable delimiter — no in-band separator a refId
// could itself contain, and no non-printable byte that would make this source
// read as binary to git/grep/diff.
function cacheKey(refKind, refId) {
  return JSON.stringify([refKind, refId]);
}

/**
 * For a batch of `{ refKind, refId }` tuples, return each annotated with
 * `resolved` (true = a live target row exists). An unknown ref_kind resolves to
 * `resolved: false` with `reason: 'unknown-kind'` rather than throwing — a
 * peer-sourced ref could name a kind this build doesn't know.
 *
 * Distinct (refKind, refId) pairs are probed once each; duplicates in the input
 * reuse the result. Small N (a record's ref set), so per-tuple probes are fine.
 */
export async function resolveRefs(tuples) {
  if (!Array.isArray(tuples) || tuples.length === 0) return [];
  const cache = new Map();
  const out = [];
  for (const t of tuples) {
    const refKind = t?.refKind;
    const refId = t?.refId;
    const key = cacheKey(refKind, refId);
    if (!cache.has(key)) {
      if (!REF_TARGET_TABLES[refKind]) {
        cache.set(key, { resolved: false, reason: 'unknown-kind' });
      } else if (typeof refId !== 'string' || !refId) {
        cache.set(key, { resolved: false, reason: 'missing-ref-id' });
      } else {
        const { rows } = await query(targetExistsSql(refKind), [refId]);
        const resolved = rows[0]?.ok === true;
        // Tag the unresolved-but-known case with the same `missing-target` reason
        // listDanglingRefs uses, so both read paths describe the state identically.
        cache.set(key, resolved ? { resolved: true } : { resolved: false, reason: 'missing-target' });
      }
    }
    out.push({ refKind, refId, ...cache.get(key) });
  }
  return out;
}

/**
 * Every LIVE ref whose `ref_id` no longer resolves to a live target — the
 * dangling-ref integrity report. One grouped query per known kind (over the
 * distinct targets, not every ref row) keeps it cheap; refs whose kind has no
 * target table are reported as `unknown-kind` so nothing is silently dropped.
 *
 * Only refs that are themselves live AND whose ingredient is live count — a
 * soft-deleted ingredient leaves its ref rows in place (deleted = FALSE), but
 * those have no live "Appears in" consumer, so reporting them would be a false
 * positive (matches the `JOIN … i.deleted = false` filter every user-facing ref
 * path uses).
 *
 * Returns one entry per dangling (ref_kind, ref_id) target, with the count of
 * live ingredient links pointing at it: `{ refKind, refId, reason, linkCount }`.
 */
export async function listDanglingRefs() {
  // Distinct live targets actually referenced by a LIVE ingredient, with how
  // many such links each has.
  const { rows } = await query(
    `SELECT r.ref_kind, r.ref_id, COUNT(*)::int AS link_count
       FROM catalog_ingredient_refs r
       JOIN catalog_ingredients i ON i.id = r.ingredient_id AND i.deleted = FALSE
      WHERE r.deleted = FALSE
      GROUP BY r.ref_kind, r.ref_id`,
  );
  const dangling = [];
  // Probe each distinct target. Group by kind so we can batch the resolvable
  // ones with a single ANY() query per table instead of one probe per target.
  const byKind = new Map();
  for (const r of rows) {
    if (!byKind.has(r.ref_kind)) byKind.set(r.ref_kind, []);
    byKind.get(r.ref_kind).push({ refId: r.ref_id, linkCount: r.link_count });
  }
  for (const [refKind, targets] of byKind) {
    const meta = REF_TARGET_TABLES[refKind];
    if (!meta) {
      // Unknown kind — every target of it is unresolvable on this build.
      for (const t of targets) dangling.push({ refKind, refId: t.refId, reason: 'unknown-kind', linkCount: t.linkCount });
      continue;
    }
    const ids = targets.map((t) => t.refId);
    const where = meta.liveClause ? `${meta.liveClause} AND id = ANY($1)` : 'id = ANY($1)';
    const live = await query(`SELECT id FROM ${meta.table} WHERE ${where}`, [ids]);
    const liveSet = new Set(live.rows.map((row) => row.id));
    for (const t of targets) {
      if (!liveSet.has(t.refId)) {
        dangling.push({ refKind, refId: t.refId, reason: 'missing-target', linkCount: t.linkCount });
      }
    }
  }
  return dangling;
}