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

17.8 KB
Raw Blame
/**
 * Creative Ingredients Catalog — source links, external refs & relations.
 *
 * Three edge tables around ingredients:
 *   - catalog_ingredient_sources: ingredient ↔ scrap provenance links.
 *   - catalog_ingredient_refs:    ingredient ↔ external record (universe /
 *     series / creative-director / issue / work), each with a role.
 *   - catalog_ingredient_relations: directed ingredient ↔ ingredient edges.
 *
 * Unlink is a soft-delete so the tombstone propagates to peers.
 */

import { query } from '../../lib/db.js';
import { resolveRefs } from '../catalogRefResolver.js';
import { assertCatalogIdentityWritable, assertCatalogAssociationRemovable, assertCatalogRelationRemovable } from './identityAccess.js';
import { rowToRef, rowToSource, rowToIngredient } from './shared.js';
import { toPgJson } from '../../lib/pgJson.js';

// Canonical detail views omit local-merge originals; original-ID user exports
// retain those raw rows alongside the bilateral receipt-backed evidence.
const exportRefRows = `(SELECT * FROM catalog_identity_read_refs UNION ALL
  SELECT r.*, false AS identity_retained FROM catalog_ingredient_refs r
   WHERE r.ingredient_id <> catalog_resolve_ingredient_id(r.ingredient_id))`;


// `{ client }` is optional — see the createIngredient comment. Passing the same
// client used to insert the ingredient row keeps the source-link row in the
// same transaction so a mid-batch failure rolls back both halves.
export async function linkIngredientToSource(ingredientId, scrapId, span = null, { client, preserveExisting = false } = {}) {
  ingredientId = await assertCatalogIdentityWritable(ingredientId, { client });
  const exec = client ? client.query.bind(client) : query;
  await exec(
    `INSERT INTO catalog_ingredient_sources (ingredient_id, scrap_id, span)
     VALUES (catalog_resolve_ingredient_id($1), $2, $3::jsonb)
     ON CONFLICT (ingredient_id, scrap_id) ${preserveExisting ? 'DO NOTHING' : 'DO UPDATE SET span = EXCLUDED.span'}`,
    [ingredientId, scrapId, span ? toPgJson(span) : null],
  );
}

// Joins the scrap's `title` onto each source link (#7617) so the detail page
// can render "extracted from <title>" instead of the bare `cat-scrap-<uuid>`.
// `LEFT JOIN` (not `JOIN`) so a source row survives a hard-deleted scrap —
// `scrapTitle` just comes back null, same posture as an orphaned ref.
export async function listSourcesForIngredient(ingredientId) {
  const result = await query(
    `SELECT s.*, sc.title AS scrap_title
       FROM catalog_identity_read_sources s
       LEFT JOIN catalog_scraps sc ON sc.id = s.scrap_id
      WHERE catalog_canonical_ingredient_id(s.ingredient_id) = catalog_canonical_ingredient_id($1)`,
    [ingredientId],
  );
  return result.rows.map((row) => ({ ...rowToSource(row), scrapTitle: row.scrap_title ?? null }));
}

export async function listSourcesForScrap(scrapId) {
  const result = await query(
    `SELECT * FROM catalog_ingredient_sources WHERE scrap_id = $1`,
    [scrapId],
  );
  return result.rows.map(rowToSource);
}

// Sibling ingredients extracted from the same source scrap(s) as `ingredientId`
// (#7617) — "what else came out of this piece?" in the ONE `/details` round-trip
// the detail page already makes, rather than a second fetch per scrap. Batches
// across every scrap the ingredient sources from in one query (mirrors the
// `listRefsForIngredients` batching shape). Returns `Map<scrapId, Array<{ id,
// name, type }>>`; excludes the caller's own row and soft-deleted siblings.
export async function listSiblingIngredientsBySource(ingredientId, scrapIds) {
  const ids = [...new Set(scrapIds)].filter(Boolean);
  if (ids.length === 0) return new Map();
  const result = await query(
    `SELECT DISTINCT s.scrap_id, i.id, i.name, i.type
       FROM catalog_identity_read_sources s
       JOIN catalog_ingredients i ON i.id = catalog_canonical_ingredient_id(s.ingredient_id)
      WHERE s.scrap_id = ANY($1) AND i.id != catalog_canonical_ingredient_id($2) AND i.deleted = false
      ORDER BY i.name ASC`,
    [ids, ingredientId],
  );
  const grouped = new Map(ids.map((id) => [id, []]));
  for (const row of result.rows) {
    grouped.get(row.scrap_id)?.push({ id: row.id, name: row.name, type: row.type });
  }
  return grouped;
}

// `{ client }` is optional — see linkIngredientToSource. Passing the same
// client used to insert the ingredient row keeps the ref link in the same
// transaction so a mid-batch failure rolls back both halves (#7615).
export async function linkIngredientToRef(ingredientId, refKind, refId, role, { client } = {}) {
  ingredientId = await assertCatalogIdentityWritable(ingredientId, { client });
  // ON CONFLICT DO UPDATE revives a soft-deleted row instead of leaving it
  // tombstoned. The trigger only bumps sync_sequence when `deleted` or
  // `deleted_at` actually change, so a link-on-active-row stays a no-op for
  // peers (no spurious sync event).
  const exec = client ? client.query.bind(client) : query;
  await exec(
    `INSERT INTO catalog_ingredient_refs (ingredient_id, ref_kind, ref_id, role)
     VALUES (catalog_resolve_ingredient_id($1), $2, $3, $4)
     ON CONFLICT (ingredient_id, ref_kind, ref_id, role) DO UPDATE
       SET deleted = false, deleted_at = NULL`,
    [ingredientId, refKind, refId, role],
  );
}

// Catalog ingredient `type` → series ref role (#1761). Anything outside this
// map (idea/scene/user-defined types) links as a generic 'mentioned' ref.
// Lives here, next to the link primitive, so every remix target that attaches
// ingredients to a series shares one role vocabulary instead of inlining its own.
const SERIES_REF_ROLE_BY_TYPE = Object.freeze({
  character: 'cast',
  place: 'canon-place',
  object: 'canon-object',
});
export const seriesRefRoleForType = (type) => SERIES_REF_ROLE_BY_TYPE[type] || 'mentioned';

// Link a batch of already-resolved catalog ingredients to a series via
// catalog_ingredient_refs (#1761) — the convergence contract's single data
// model. The inserts are mutually independent, so they fan out. Returns the
// ingredients actually linked (those with an id).
export async function linkIngredientsToSeries(seriesId, ingredients = []) {
  const list = Array.isArray(ingredients) ? ingredients.filter((ing) => ing && ing.id) : [];
  if (list.length === 0) return [];
  await Promise.all(list.map((ing) => linkIngredientToRef(ing.id, 'series', seriesId, seriesRefRoleForType(ing.type))));
  return list;
}

// Catalog ingredient `type` → Creative Director ref role (#1808). CD projects
// reuse the same convergence data model (catalog_ingredient_refs) as series; the
// role vocabulary is CD-flavored (location/prop) so the Catalog "Appears in"
// panel and any future casting UI can label a CD link meaningfully. Anything
// outside this map (idea/concept/user-defined types) links as a generic
// 'reference'. Lives next to seriesRefRoleForType so every remix target shares
// one role-vocabulary home.
const CD_REF_ROLE_BY_TYPE = Object.freeze({
  character: 'cast',
  place: 'location',
  object: 'prop',
  scene: 'scene',
});
export const cdRefRoleForType = (type) => CD_REF_ROLE_BY_TYPE[type] || 'reference';

// Catalog ingredient `type` → universe ref role (#7615). The `canon-<kind>`
// convention is what catalogCanonProjection.js and catalogUniverseTags.js
// already expect for character/place/object; everything else (idea/scene/
// concept and user-defined types) links as a generic 'reference'. Lives next
// to seriesRefRoleForType/cdRefRoleForType so every remix target shares one
// role-vocabulary home.
const UNIVERSE_REF_ROLE_BY_TYPE = Object.freeze({
  character: 'canon-character',
  place: 'canon-place',
  object: 'canon-object',
});
export const universeRefRoleForType = (type) => UNIVERSE_REF_ROLE_BY_TYPE[type] || 'reference';

// Music video subject kind → ref role. A music video casts characters and
// films in sets with props; the subject kind is the catalog type.
const MUSIC_VIDEO_REF_ROLE_BY_TYPE = Object.freeze({
  character: 'cast',
  place: 'set',
  object: 'prop',
});
export const musicVideoRefRoleForType = (type) => MUSIC_VIDEO_REF_ROLE_BY_TYPE[type] || 'reference';


// Link a batch of already-resolved catalog ingredients to a Creative Director
// project via catalog_ingredient_refs with ref_kind='creative-director' (#1808)
// — the reserved-but-previously-unwritten ref kind. Mirrors
// linkIngredientsToSeries; the inserts are independent so they fan out. Returns
// the ingredients actually linked (those with an id).
export async function linkIngredientsToCreativeDirector(projectId, ingredients = []) {
  const list = Array.isArray(ingredients) ? ingredients.filter((ing) => ing && ing.id) : [];
  if (list.length === 0) return [];
  await Promise.all(list.map((ing) => linkIngredientToRef(ing.id, 'creative-director', projectId, cdRefRoleForType(ing.type))));
  return list;
}

export async function unlinkIngredientFromRef(ingredientId, refKind, refId, role) {
  ingredientId = await assertCatalogIdentityWritable(ingredientId);
  await assertCatalogAssociationRemovable(ingredientId, 'refs', [refKind, refId, role]);
  // Soft-delete via UPDATE so the row stays around as a tombstone and the
  // trg_catalog_ref_sync_seq trigger bumps sync_sequence — peers pick the
  // unlink up on their next pull. The `AND deleted = false` filter keeps
  // re-unlinks from re-bumping sync_sequence unnecessarily.
  await query(
    `UPDATE catalog_ingredient_refs
        SET deleted = true, deleted_at = NOW()
      WHERE ingredient_id = catalog_resolve_ingredient_id($1) AND ref_kind = $2 AND ref_id = $3 AND role = $4
        AND deleted = false`,
    [ingredientId, refKind, refId, role],
  );
}

export async function listRefsForIngredient(ingredientId) {
  // Filter `deleted = false` so the "Appears in" panel doesn't surface
  // tombstoned unlinks. Tombstones are read-only state for sync purposes;
  // user-facing list paths only show live links.
  const byIngredient = await listRefsForIngredients([ingredientId]);
  return byIngredient.get(ingredientId);
}

// Batched `listRefsForIngredient` for bulk hydration paths (#3940) — ONE query
// for N ingredients instead of N, with the same `deleted = false` live filter.
// Returns `Map<ingredientId, refs[]>` with an entry (possibly `[]`) for every id.
export async function listRefsForIngredients(ingredientIds, { preserveIdentity = false, includeRetained = false } = {}) {
  const ids = [...new Set(ingredientIds)];
  if (ids.length === 0) return new Map();
  const result = await query(
    preserveIdentity ? `SELECT * FROM ${includeRetained ? `${exportRefRows} exported_refs` : 'catalog_ingredient_refs'}
      WHERE ingredient_id = ANY($1) AND deleted = false ORDER BY ingredient_id` : `SELECT r.*, requested.id AS requested_id FROM unnest($1::text[]) requested(id)
      JOIN catalog_identity_read_refs r ON r.ingredient_id = ANY(catalog_identity_member_ids(requested.id))
      WHERE r.deleted = false
      ORDER BY ingredient_id`,
    [ids],
  );
  const grouped = new Map(ids.map(id => [id, []]));
  for (const row of result.rows) grouped.get(row.requested_id ?? row.ingredient_id)?.push(rowToRef(row));
  return grouped;
}

// Like listRefsForIngredient, but drops refs whose TARGET no longer resolves to
// a live record (#1812). A ref row stays live (`deleted = false`) when its
// universe/series/creative-director/issue/work target is soft-deleted — that's
// intentional (the orphan is recoverable via the Orphaned album) — but the
// detail page's "Appears in" panel must not render a chip that deep-links to a
// 404'd target. Resolution goes through the shared resolver (REF_TARGET_TABLES),
// so every ref kind is filtered uniformly. resolveRefs preserves input order and
// de-dupes its target probes, so the filter is a positional zip.
export async function listLiveRefsForIngredient(ingredientId) {
  const refs = await listRefsForIngredient(ingredientId);
  if (refs.length === 0) return refs;
  const resolved = await resolveRefs(refs);
  return refs.filter((_, i) => resolved[i]?.resolved === true);
}

// Only human-requested exports use original owner snapshots. Federation feeds
// and bundles retain their existing raw-table contract. No permanent view is
// needed for the ingredient row type (which must remain freely migratable).
const exportIngredientCte = `WITH active AS (
  SELECT * FROM catalog_identity_operations WHERE state IN ('committed', 'recovery_prepared')
), candidates AS (
  SELECT jsonb_populate_record(NULL::catalog_ingredients, item) AS r, 0 AS priority
    FROM active o CROSS JOIN LATERAL jsonb_array_elements(COALESCE(o.plan->'source'->'ingredients', '[]'::jsonb)) item
  UNION ALL
  SELECT live AS r, 1 AS priority FROM catalog_ingredients live
    WHERE NOT EXISTS (SELECT 1 FROM active o WHERE live.id = o.source_id
      OR (o.local_owner <> o.survivor_owner AND live.id = o.survivor_id))
  UNION ALL
  SELECT jsonb_populate_record(NULL::catalog_ingredients, item) AS r, 2 AS priority
    FROM active o CROSS JOIN LATERAL jsonb_array_elements(COALESCE(o.current_snapshot->'ingredients', o.plan->'survivor'->'ingredients', '[]'::jsonb)) item
    WHERE o.local_owner <> o.survivor_owner
), export_ingredients AS (
  SELECT DISTINCT ON ((r).id) (r).* FROM candidates ORDER BY (r).id, priority
) `;

export async function listIngredientsForRef(refKind, refId, { preserveIdentity = false, includeRetained = false } = {}) {
  const result = await query(
    `${includeRetained ? exportIngredientCte : ''}SELECT DISTINCT i.*, r.role
       FROM ${includeRetained ? 'export_ingredients' : 'catalog_ingredients'} i
       JOIN ${includeRetained ? exportRefRows : preserveIdentity ? 'catalog_ingredient_refs' : 'catalog_identity_read_refs'} r ON ${preserveIdentity ? 'r.ingredient_id' : 'catalog_canonical_ingredient_id(r.ingredient_id)'} = i.id
       WHERE r.ref_kind = $1 AND r.ref_id = $2
         AND r.deleted = false AND i.deleted = false${preserveIdentity ? '' : ' AND i.id = catalog_canonical_ingredient_id(i.id)'}`,
    [refKind, refId],
  );
  return result.rows.map((row) => ({ ingredient: rowToIngredient(row), role: row.role }));
}


// --- Ingredient↔ingredient relations -----------------------------------
// Directed edges (from_id → to_id, kind). Soft-deleted on unlink so peers
// receive the tombstone. ON CONFLICT DO UPDATE revives a soft-deleted edge
// (the trg_catalog_relation_sync_seq trigger bumps sync_sequence only when
// deleted/deleted_at actually change, so a link-on-active-row stays a no-op).

// `{ client }` is optional — see linkIngredientToSource. Passing the same
// client used to insert the ingredient rows keeps the relation edge in the
// same transaction so a mid-batch failure rolls back both halves (#7615).
export async function linkIngredientRelation(fromId, toId, kind, { client } = {}) {
  fromId = await assertCatalogIdentityWritable(fromId, { client });
  toId = await assertCatalogIdentityWritable(toId, { client });
  if (fromId === toId) throw new Error('cannot relate an ingredient to itself');
  const exec = client ? client.query.bind(client) : query;
  const result = await exec(
    `INSERT INTO catalog_ingredient_relations (from_id, to_id, kind)
     SELECT catalog_resolve_ingredient_id($1), catalog_resolve_ingredient_id($2), $3
      WHERE catalog_resolve_ingredient_id($1) <> catalog_resolve_ingredient_id($2)
     ON CONFLICT (from_id, to_id, kind) DO UPDATE
       SET deleted = false, deleted_at = NULL RETURNING from_id`,
    [fromId, toId, kind],
  );
  if (result.rowCount === 0) throw new Error('cannot relate an ingredient identity to itself');
}

export async function unlinkIngredientRelation(fromId, toId, kind) {
  fromId = await assertCatalogIdentityWritable(fromId);
  toId = await assertCatalogIdentityWritable(toId);
  await assertCatalogRelationRemovable(fromId, toId, kind);
  // Soft-delete (mirrors unlinkIngredientFromRef): keep the row as a tombstone
  // so the sync_sequence bump propagates the unlink to peers. `AND deleted =
  // false` keeps a re-unlink from re-bumping the sequence needlessly.
  await query(
    `UPDATE catalog_ingredient_relations
        SET deleted = true, deleted_at = NOW()
      WHERE from_id = catalog_resolve_ingredient_id($1) AND to_id = catalog_resolve_ingredient_id($2) AND kind = $3
        AND deleted = false`,
    [fromId, toId, kind],
  );
}

// Both directions for one ingredient's detail "Relations" panel. Outbound
// (from_id = id) and inbound (to_id = id) are returned separately so the UI
// can render each with the correct directional label. Joins the OTHER end's
// ingredient name/type so the chip reads without a second fetch. Live edges
// only (deleted = false on both the edge and the joined ingredient).
export async function listRelationsForIngredient(id) {
  const [outbound, inbound] = await Promise.all([
    query(
      `SELECT r.from_id, r.to_id, r.kind, r.created_at,
              i.name AS other_name, i.type AS other_type
         FROM catalog_identity_read_relations r
         JOIN catalog_ingredients i ON i.id = catalog_canonical_ingredient_id(r.to_id)
        WHERE r.from_id = ANY(catalog_identity_member_ids($1)) AND r.deleted = false AND i.deleted = false AND i.id = catalog_canonical_ingredient_id(i.id)
        ORDER BY r.created_at ASC`,
      [id],
    ),
    query(
      `SELECT r.from_id, r.to_id, r.kind, r.created_at,
              i.name AS other_name, i.type AS other_type
         FROM catalog_identity_read_relations r
         JOIN catalog_ingredients i ON i.id = catalog_canonical_ingredient_id(r.from_id)
        WHERE r.to_id = ANY(catalog_identity_member_ids($1)) AND r.deleted = false AND i.deleted = false AND i.id = catalog_canonical_ingredient_id(i.id)
        ORDER BY r.created_at ASC`,
      [id],
    ),
  ]);
  const mapRow = (row, otherId) => ({
    fromId: row.from_id,
    toId: row.to_id,
    kind: row.kind,
    createdAt: row.created_at.toISOString(),
    other: { id: otherId, name: row.other_name, type: row.other_type },
  });
  return {
    outbound: outbound.rows.map((row) => mapRow(row, row.to_id)),
    inbound: inbound.rows.map((row) => mapRow(row, row.from_id)),
  };
}