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
JavaScript ai-agentsdashboarddeveloper-toolsdigital-twinquantified-selfsecond-brainself-hostedtailscale
/**
* 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)),
};
}