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
/**
 * Creative Ingredients Catalog — export bundles, stats & facets.
 *
 * The user-facing export slice (`exportSliceForRef`, embeddings stripped) and
 * the sync bundle (`getCatalogBundleForRef`, embeddings + tombstones kept) for
 * one external ref, plus the aggregate stats + faceted counts that drive the
 * Catalog filter dropdowns and album headers.
 */

import { query } from '../../lib/db.js';
import { rowToRef, rowToSource, rowToIngredient, liveHomingTargetSql, HOMING_REF_KINDS_SQL, groupRowsByIngredient } from './shared.js';
import { listIngredientsForRef, listRefsForIngredients } from './refs.js';
import { listMediaForIngredients } from './media.js';

const rowToExportScrap = (row) => ({
  id: row.id,
  title: row.title,
  rawText: row.raw_text,
  sourceKind: row.source_kind,
  metadata: row.metadata || {},
  createdAt: row.created_at.toISOString(),
  updatedAt: row.updated_at.toISOString(),
});

/**
 * Hydrate one ingredient with its live scraps for the export bundle, oldest
 * first. Returns `[]` when an ingredient has no sources.
 */
export async function listScrapsForIngredient(ingredientId) {
  const byIngredient = await listScrapsForIngredients([ingredientId]);
  return byIngredient.get(ingredientId);
}

// Batched `listScrapsForIngredient` for bulk hydration paths (#3940) — ONE
// query for N ingredients instead of N. `src.ingredient_id` comes back on each
// row purely to group by; the outer sort key doesn't disturb the per-ingredient
// `created_at ASC` order. Returns `Map<ingredientId, scraps[]>` with an entry
// (possibly `[]`) for every id.
export async function listScrapsForIngredients(ingredientIds, { includeRetained = false } = {}) {
  const ids = [...new Set(ingredientIds)];
  if (ids.length === 0) return new Map();
  const result = await query(
    `SELECT src.ingredient_id, s.id, s.title, s.raw_text, s.source_kind, s.metadata,
            s.created_at, s.updated_at
       FROM catalog_scraps s
       JOIN ${includeRetained ? 'catalog_identity_read_sources' : 'catalog_ingredient_sources'} src ON src.scrap_id = s.id
      WHERE src.ingredient_id = ANY($1)
        AND s.deleted = false
      ORDER BY src.ingredient_id, s.created_at ASC`,
    [ids],
  );
  return groupRowsByIngredient(ids, result.rows, rowToExportScrap);
}

// Source provenance survives even when a foreign scrap's body was never copied.
async function listExportSourcesForIngredients(ingredientIds) {
  const ids = [...new Set(ingredientIds)];
  if (ids.length === 0) return new Map();
  const result = await query(
    `SELECT * FROM catalog_identity_read_sources
      WHERE ingredient_id = ANY($1) ORDER BY ingredient_id, extracted_at ASC`,
    [ids],
  );
  return groupRowsByIngredient(ids, result.rows, row => {
    const { syncSequence: _syncSequence, ...source } = rowToSource(row);
    return source;
  });
}

/**
 * Build an export bundle for one ref (universe/series/issue/work).
 * Hydrates each ingredient + its scraps + ref links + media attachments. The
 * media bundle carries `media_key` REFERENCES (not bytes) — a receiving peer
 * matches each key against its own library and surfaces unresolved ones via
 * the metadata-missing integrity surface. Relations are still omitted here
 * (see `[catalog-ingredient-relations]`); when they land, extend this helper.
 */
export async function exportSliceForRef(refKind, refId) {
  const rows = await listIngredientsForRef(refKind, refId, { preserveIdentity: true, includeRetained: true });
  // Hydrate the whole slice in batches (#3940), including source links whose
  // scrap bodies are unavailable locally. Five queries total, regardless of N.
  const ingredientIds = rows.map(({ ingredient }) => ingredient.id);
  const [scrapsById, refsById, mediaById, sourcesById] = await Promise.all([
    listScrapsForIngredients(ingredientIds, { includeRetained: true }),
    listRefsForIngredients(ingredientIds, { preserveIdentity: true, includeRetained: true }),
    listMediaForIngredients(ingredientIds, { preserveIdentity: true, includeRetained: true }),
    listExportSourcesForIngredients(ingredientIds),
  ]);
  const ingredients = rows.map(({ ingredient, role }) => {
    const { embedding: _embedding, ...rest } = ingredient;
    return {
      ...rest,
      // The role this ingredient plays for the queried ref — handy for
      // round-trip re-imports that want to preserve roleness without
      // re-deriving it from the full refs list.
      roleForExportedRef: role,
      refs: refsById.get(ingredient.id) || [],
      scraps: scrapsById.get(ingredient.id) || [],
      sources: sourcesById.get(ingredient.id) || [],
      media: mediaById.get(ingredient.id) || [],
    };
  });
  return {
    version: 1,
    ref: { kind: refKind, id: refId },
    exportedAt: new Date().toISOString(),
    ingredients,
  };
}

/**
 * Wire-shaped catalog bundle for one external ref (universe/series/issue/work),
 * for piggy-backing on a peer RECORD push (e.g. a universe push carries the
 * catalog rows referenced by its embedded canon). Unlike `exportSliceForRef`
 * (a user-facing export that strips embeddings + tombstones) this is a SYNC
 * payload:
 *
 *   - `ingredients` carry their `embedding` + tombstone fields, so the
 *     receiver gets the full enriched row (tags, embedding, payload.summary)
 *     rather than re-deriving a strictly-lossy view from the embedded canon.
 *   - `refs` include TOMBSTONED rows (deleted = true) so an unlink propagates
 *     with the push — the "Appears in" panel converges across peers.
 *
 * Shapes match `catalogSyncIngredientSchema` / `catalogSyncRefSchema`, so the
 * receiver applies them straight through `catalogSync.applyRemoteChanges`.
 */
export async function getCatalogBundleForRef(refKind, refId) {
  // Every ref row for this target — live AND tombstoned (no `deleted = false`
  // filter, unlike listRefsForIngredient) so unlinks ride the bundle.
  const refResult = await query(
    `SELECT * FROM catalog_ingredient_refs WHERE ref_kind = $1 AND ref_id = $2`,
    [refKind, refId],
  );
  const refs = refResult.rows.map(rowToRef);

  // Hydrate each referenced ingredient WITH embedding + tombstone state. A
  // tombstoned ref still names a (possibly live) ingredient — include it so
  // the receiver has the enriched row even if its own ref is being removed.
  const ingredientIds = [...new Set(refs.map((r) => r.ingredientId))];
  let ingredients = [];
  if (ingredientIds.length > 0) {
    const ingResult = await query(
      `SELECT * FROM catalog_ingredients WHERE id = ANY($1)`,
      [ingredientIds],
    );
    ingredients = ingResult.rows.map(rowToIngredient);
  }
  return { ingredients, refs };
}

/**
 * Which of `ingredientIds` already have a local row (live or tombstoned), and
 * which of those already carry a local ref (live or tombstoned) to
 * `refKind`/`refId`. A peer-push receiver uses it to refuse a bundled
 * ingredient that belongs to another record (#12466).
 */
export async function getLocalIngredientRefOwnership(ingredientIds, refKind, refId) {
  const ids = [...new Set(ingredientIds)];
  if (ids.length === 0) return { existing: new Set(), linked: new Set() };
  const [ingResult, refResult] = await Promise.all([
    query(`SELECT id FROM catalog_ingredients WHERE id = ANY($1)`, [ids]),
    query(
      `SELECT DISTINCT ingredient_id FROM catalog_ingredient_refs
        WHERE ingredient_id = ANY($1) AND ref_kind = $2 AND ref_id = $3`,
      [ids, refKind, refId],
    ),
  ]);
  return {
    existing: new Set(ingResult.rows.map((row) => row.id)),
    linked: new Set(refResult.rows.map((row) => row.ingredient_id)),
  };
}

export async function getCatalogStats() {
  const [byTypeResult, scrapResult, withEmb] = await Promise.all([
    query(`SELECT type, COUNT(*) AS count FROM catalog_ingredients WHERE deleted = false AND id = catalog_canonical_ingredient_id(id) GROUP BY type`),
    // Count only parent/standalone scraps — child chunk rows are an internal
    // extraction detail and would inflate the user-facing scrap count.
    query(`SELECT COUNT(*) AS count FROM catalog_scraps WHERE deleted = false AND parent_scrap_id IS NULL`),
    query(`SELECT COUNT(*) AS count FROM catalog_ingredients WHERE deleted = false AND id = catalog_canonical_ingredient_id(id) AND embedding IS NOT NULL`),
  ]);
  const byType = {};
  let total = 0;
  for (const r of byTypeResult.rows) {
    byType[r.type] = parseInt(r.count, 10);
    total += parseInt(r.count, 10);
  }
  return {
    total,
    byType,
    scraps: parseInt(scrapResult.rows[0].count, 10),
    withEmbeddings: parseInt(withEmb.rows[0].count, 10),
  };
}

// Faceted counts driving the Catalog filter dropdowns + album headers (#1762)
// in one round-trip. Distinguishes three mutually-exclusive per-ingredient
// buckets: LINKED (≥1 live universe/series ref — rolls up into the universes/
// series facet arrays), UNLINKED (no universe/series ref at all → "Raw" album),
// and ORPHANED (has a universe/series ref but none resolve to a live target →
// "Orphaned" album). Only live universes/series appear in the facet arrays; the
// joins on `deleted = false` enforce the same live predicate the resolver uses.
export async function getCatalogFacets() {
  const [typeRows, uniRows, serRows, tagRows, bucketRows] = await Promise.all([
    query(`SELECT type, COUNT(*)::int AS count
             FROM catalog_ingredients WHERE deleted = false AND id = catalog_canonical_ingredient_id(id)
            GROUP BY type ORDER BY count DESC, type`),
    // Universe membership rolls up its series' members (decision #1), so the
    // album header count matches what the universe album/filter actually lists:
    // distinct ingredients linked to the universe OR to a live series under it.
    query(`SELECT u.id AS ref_id, u.name, COUNT(DISTINCT catalog_canonical_ingredient_id(r.ingredient_id))::int AS count
             FROM universes u
             JOIN catalog_identity_read_refs r ON r.deleted = false AND (
                  (r.ref_kind = 'universe' AND r.ref_id = u.id)
                  OR (r.ref_kind = 'series' AND r.ref_id IN (
                        SELECT s.id FROM pipeline_series s WHERE s.universe_id = u.id AND s.deleted = false)))
             JOIN catalog_ingredients i ON i.id = catalog_canonical_ingredient_id(r.ingredient_id) AND i.deleted = false AND i.id = catalog_canonical_ingredient_id(i.id)
            WHERE u.deleted = false
            GROUP BY u.id, u.name ORDER BY count DESC, u.name`),
    query(`SELECT s.id AS ref_id, s.name, s.universe_id, COUNT(DISTINCT catalog_canonical_ingredient_id(r.ingredient_id))::int AS count
             FROM catalog_identity_read_refs r
             JOIN catalog_ingredients i ON i.id = catalog_canonical_ingredient_id(r.ingredient_id) AND i.deleted = false AND i.id = catalog_canonical_ingredient_id(i.id)
             JOIN pipeline_series s ON s.id = r.ref_id AND s.deleted = false
            WHERE r.deleted = false AND r.ref_kind = 'series'
            GROUP BY s.id, s.name, s.universe_id ORDER BY count DESC, s.name`),
    query(`SELECT tag, COUNT(*)::int AS count
             FROM (SELECT unnest(tags) AS tag FROM catalog_ingredients WHERE deleted = false AND id = catalog_canonical_ingredient_id(id)) t
            GROUP BY tag ORDER BY count DESC, tag`),
    // Per-ingredient bucket classification. ref_count = live homing ref ROWS
    // (universe/series/creative-director); live_count = those whose target still
    // resolves. unlinked = ref_count 0; orphaned = ref_count > 0 but live_count 0;
    // linked = live_count > 0. Same homing-ref set + live predicate as the
    // listIngredients orphaned/unlinked filters, so the bucket counts and the
    // album listings can't drift.
    query(`SELECT
              COUNT(*) FILTER (WHERE ref_count = 0)::int AS unlinked,
              COUNT(*) FILTER (WHERE ref_count > 0 AND live_count = 0)::int AS orphaned,
              COUNT(*) FILTER (WHERE live_count > 0)::int AS linked
            FROM (
              SELECT i.id,
                COUNT(r.ref_id) AS ref_count,
                COUNT(r.ref_id) FILTER (WHERE ${liveHomingTargetSql('r')}) AS live_count
              FROM catalog_ingredients i
              LEFT JOIN catalog_identity_read_refs r
                ON catalog_canonical_ingredient_id(r.ingredient_id) = i.id AND r.deleted = false AND r.ref_kind IN (${HOMING_REF_KINDS_SQL})
              WHERE i.deleted = false AND i.id = catalog_canonical_ingredient_id(i.id)
              GROUP BY i.id
            ) sub`),
  ]);
  const types = typeRows.rows.map((r) => ({ type: r.type, count: r.count }));
  const total = types.reduce((sum, t) => sum + t.count, 0);
  const buckets = bucketRows.rows[0] || {};
  return {
    types,
    universes: uniRows.rows.map((r) => ({ refId: r.ref_id, name: r.name, count: r.count })),
    series: serRows.rows.map((r) => ({ refId: r.ref_id, name: r.name, universeId: r.universe_id, count: r.count })),
    tags: tagRows.rows.map((r) => ({ tag: r.tag, count: r.count })),
    unlinkedCount: buckets.unlinked ?? 0,
    orphanedCount: buckets.orphaned ?? 0,
    total,
  };
}