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

21.4 KB
Raw Blame
/**
 * Catalog Federation Sync Service
 *
 * Peer-to-peer sync for the Creative Ingredients Catalog. Mirrors
 * server/services/memorySync.js but the envelope carries seven kinds
 * (scraps, ingredients, sources, refs, relations, tags, media) because the
 * catalog is a multi-table relational store, not a single flat row set. The
 * `media` rows carry a `media_key` REFERENCE into the receiver's own library
 * (never the bytes); an unresolved key surfaces via the metadata-missing
 * integrity endpoint rather than failing the apply.
 *
 * Pull protocol:
 *   GET /api/catalog/sync?since[scraps]=A&since[ingredients]=B&...&since[media]=G&limit=100
 *   → { scraps[], ingredients[], sources[], refs[], relations[], tags[], media[], maxSequences, hasMore }
 *
 * Each kind is its own feed stream with its own commit-ordered positions
 * (`sync_feed`, #8315 — drawn at COMMIT, so a late-committing transaction can
 * never land below a cursor a peer already advanced past). Positions of
 * different kinds are INDEPENDENT — sources position P isn't comparable to
 * ingredients position P. The receiver therefore tracks one cursor per kind
 * and `since` is `{ scraps, ingredients, sources, refs, relations }`. A scalar `?since=N` is still accepted for
 * back-compat / one-shot pulls and is applied uniformly to all four kinds.
 * `hasMore` is true when ANY of the four tables had more than `limit` rows
 * past its respective cursor — drain by re-pulling with the maxSequences from
 * the previous response.
 *
 * Apply: syncOrchestrator applies pulled envelopes in-process via
 * applyRemoteChanges; sharing/peerSyncReceive uses the same function for
 * incoming catalog bundles. Conflict
 * resolution is LWW on `updated_at` for ingredient/scrap rows; source/ref
 * rows are tuple-unique and idempotent on conflict. Each row is wrapped in
 * its own try/catch so one malformed row can't poison the whole batch.
 */

import {
  getScrapChangesSince,
  getIngredientChangesSince,
  getSourceChangesSince,
  getRefChangesSince,
  getRelationChangesSince,
  getTagChangesSince,
  getMediaChangesSince,
  getMaxSequences,
  upsertScrapFromPeer,
  upsertIngredientFromPeer,
  upsertSourceFromPeer,
  upsertRefFromPeer,
  upsertRelationFromPeer,
  upsertTagFromPeer,
  upsertMediaFromPeer,
  drainPendingCatalogApplies,
  normalizeTags,
} from './catalogDB.js';
import { compareSchemaVersions, scopeVersionDiff, formatVersionGap, catalogEnvelopeHasLiveRows, PORTOS_SCHEMA_VERSIONS } from '../lib/schemaVersions.js';
import { friendlifyUniverseTags, LEGACY_UNIVERSE_MARKER_TAG } from '../lib/catalogUniverseTags.js';
import { canonicalTagKey, setUserCatalogTypes, INGREDIENT_TYPE_IDS } from '../lib/catalogTypes.js';
import { readUserTypes as readUserTypeSlice, updateUserTypes } from './catalogUserTypes/store.js';

const CURSOR_KEYS = ['scraps', 'ingredients', 'sources', 'refs', 'relations', 'tags', 'media'];

// Normalize `since` (scalar string OR per-kind object) into a `{ scraps,
// ingredients, sources, refs }` cursor map. Scalar form is uniform across
// kinds — fine for the first pull (everyone starts at '0'); subsequent pulls
// MUST use the per-kind form returned in `maxSequences` or rows on a less-
// active table get silently filtered out forever.
function normalizeCursors(since) {
  if (since && typeof since === 'object' && !Array.isArray(since)) {
    return Object.fromEntries(CURSOR_KEYS.map((k) => {
      const v = since[k];
      return [k, typeof v === 'string' && /^\d+$/.test(v) ? v : '0'];
    }));
  }
  const scalar = typeof since === 'string' && /^\d+$/.test(since) ? since : '0';
  return Object.fromEntries(CURSOR_KEYS.map((k) => [k, scalar]));
}

export async function getChangesSince(since = '0', limit = 100) {
  const cursors = normalizeCursors(since);
  const [scraps, ingredients, sources, refs, relations, tags, media] = await Promise.all([
    getScrapChangesSince(cursors.scraps, limit),
    getIngredientChangesSince(cursors.ingredients, limit),
    getSourceChangesSince(cursors.sources, limit),
    getRefChangesSince(cursors.refs, limit),
    getRelationChangesSince(cursors.relations, limit),
    getTagChangesSince(cursors.tags, limit),
    getMediaChangesSince(cursors.media, limit),
  ]);

  const hasMore =
    scraps.hasMore || ingredients.hasMore || sources.hasMore || refs.hasMore ||
    relations.hasMore || tags.hasMore || media.hasMore;

  // Per-kind cursor advance — fall back to the inbound cursor so the next pull
  // doesn't move backward on a quiet kind.
  const maxOf = (items, fallback) =>
    items.length > 0 ? items[items.length - 1].syncSequence : fallback;

  // True per-table maxima, INDEPENDENT of the inbound cursor. `maxSequences`
  // (below) falls back to the inbound cursor on a quiet kind, so it can't be
  // used to detect a peer rebuild/restore — it would just echo the caller's
  // cursor. `tableMaxSequences` reports each stream's real max feed position so
  // the receiver can spot `savedCursor > tableMax` and rewind. One cheap MAX query.
  const tableMaxSequences = await getMaxSequences();

  // User-defined type definitions ride EVERY envelope (not sequence-tracked —
  // there are at most 64 and they're small). The receiver LWW-merges them into
  // its own user-type store. Absent → empty array. (The wire shape is storage-
  // independent: #1001 moved the local store from settings.json to Postgres
  // without changing this `catalogTypes` block, so peers interoperate across
  // the migration with no schema-version bump.)
  const catalogTypesRaw = await readUserTypeSlice();
  const catalogTypes = Array.isArray(catalogTypesRaw) ? catalogTypesRaw : [];

  return {
    catalogTypes,
    scraps: scraps.items,
    ingredients: ingredients.items,
    sources: sources.items,
    refs: refs.items,
    relations: relations.items,
    tags: tags.items,
    media: media.items,
    maxSequences: {
      scraps:      maxOf(scraps.items,      cursors.scraps),
      ingredients: maxOf(ingredients.items, cursors.ingredients),
      sources:     maxOf(sources.items,     cursors.sources),
      refs:        maxOf(refs.items,        cursors.refs),
      relations:   maxOf(relations.items,   cursors.relations),
      tags:        maxOf(tags.items,        cursors.tags),
      media:       maxOf(media.items,       cursors.media),
    },
    tableMaxSequences,
    hasMore,
  };
}

export class CatalogSyncVersionMismatchError extends Error {
  constructor(diff) {
    super(`catalog sync rejected: ${formatVersionGap(diff)}`);
    this.name = 'CatalogSyncVersionMismatchError';
    // Keep the established wire code so existing callers recognize the gap.
    this.code = 'CATALOG_SCHEMA_VERSION_AHEAD';
    this.status = 412;
    this.diff = diff;
  }
}

export async function applyRemoteChanges(envelope = {}) {
  // Whole-row LWW from an older sanitizer can erase new payload fields. Live
  // catalog rows require equal versions; tombstones and empty pages are safe
  // at any version. Scan all row blocks, including future envelope kinds.
  const senderVersions = envelope?.portosMeta?.schemaVersions || {};
  const diff = scopeVersionDiff(compareSchemaVersions(senderVersions, PORTOS_SCHEMA_VERSIONS), ['catalog']);
  if (catalogEnvelopeHasLiveRows(envelope) && !diff.compatible) {
    throw new CatalogSyncVersionMismatchError(diff);
  }

  const stats = {
    scraps: { inserted: 0, updated: 0, skipped: 0, failed: 0 },
    ingredients: { inserted: 0, updated: 0, skipped: 0, failed: 0 },
    sources: { applied: 0, failed: 0 },
    refs: { applied: 0, failed: 0 },
    relations: { applied: 0, failed: 0 },
    tags: { inserted: 0, updated: 0, skipped: 0, failed: 0 },
    media: { applied: 0, failed: 0 },
    catalogTypes: { applied: 0, skipped: 0, failed: 0 },
    errors: [],
  };

  const recordFailure = (kind, id, err) => {
    stats.errors.push({ kind, id: id || null, message: err?.message || String(err) });
    console.error(`❌ catalog sync ${kind} ${id || '?'} failed: ${err?.message || err}`);
  };

  // Apply one envelope kind. Every row runs in its own try/catch so a single
  // malformed row can't abort the batch. Two stats shapes: LWW kinds
  // (tags/scraps/ingredients) read `{ applied, isInsert }` from the upsert into
  // inserted/updated/skipped; tuple-unique kinds (sources/refs/relations/media)
  // have no result and tally `applied`. `prepare(item)` may return a REPLACEMENT
  // row to upsert (used to clean legacy tags in-place so the row lands in its
  // final shape in ONE write); `onApplied(item, res)` runs after a successful
  // upsert. Both run in their OWN guard, so a hook failure can't be miscounted
  // as an upsert failure — a failed `prepare` falls back to the raw row rather
  // than dropping it.
  const applyKind = async ({ items, upsertFn, tally, lww, errLabel, idFor, prepare, onApplied }) => {
    for (const raw of items || []) {
      let item = raw;
      if (prepare) {
        try { item = (await prepare(raw)) || raw; }
        catch (err) { recordFailure(`${errLabel}-prepare`, idFor(raw), err); }
      }
      let res;
      try {
        res = await upsertFn(item);
        if (res?.identityPending) tally.identityPending = (tally.identityPending || 0) + 1;
        if (lww) {
          if (res.deferred) tally.deferred = (tally.deferred || 0) + 1;
          else if (!res.applied) tally.skipped++;
          else if (res.isInsert) tally.inserted++;
          else tally.updated++;
        } else if (res?.protectedIdentity) {
          tally.skipped = (tally.skipped || 0) + 1;
        } else {
          tally.applied++;
        }
      } catch (err) {
        tally.failed++;
        recordFailure(errLabel, idFor(item), err);
        continue;
      }
      if (onApplied && !res?.protectedIdentity) {
        try { await onApplied(item, res); }
        catch (err) { recordFailure(`${errLabel}-postapply`, idFor(item), err); }
      }
    }
  };

  // Legacy universe tags (`from-universe` + `universe:<id>`) friendlify on
  // inbound sync: an older peer that syncs an ingredient AFTER our one-time boot
  // repair (repairUniverseTags.js) already ran would otherwise reintroduce the
  // raw machine tags via LWW, and the boot repair's marker means it never runs
  // again to clean them up. Rewriting them to the friendly universe NAME here
  // re-applies the repair whenever an ingredient is (re)synced and its universe
  // is resolvable locally — rather than only at boot. When the ingredient
  // arrives BEFORE its universe, the row keeps its machine tags (changed=false)
  // and is friendlified on the next sync once the universe is known, or by the
  // boot repair, whichever fires first. The universe name map is built lazily —
  // and only when a row actually carries the marker — so an envelope with no
  // legacy rows never issues the universe query.
  //
  // The rewrite happens BEFORE the upsert (not as a follow-up UPDATE) so the row
  // is written once, in its cleaned state: an envelope of N legacy rows costs N
  // queries instead of 2N and leaves no duplicate `catalog_ingredient_revisions`
  // entry per synced row. An LWW skip needs no special case — the local row is
  // newer, so the (rewritten) peer row is discarded by the upsert's WHERE guard
  // exactly as the raw one would have been.
  let universeNameMap = null;
  const ensureUniverseNameMap = async () => {
    if (universeNameMap) return universeNameMap;
    const { listUniverses } = await import('./universeBuilder.js');
    const universes = await listUniverses({ includeDeleted: true });
    universeNameMap = new Map();
    for (const u of universes) {
      if (u?.id && typeof u.name === 'string' && u.name.trim()) {
        universeNameMap.set(u.id, u.name.trim());
      }
    }
    return universeNameMap;
  };
  // The friendly universe NAME is minted locally — it isn't one of the peer's
  // `envelope.tags` rows — so nothing else in the apply registers it in the
  // canonical `catalog_tags` taxonomy, and tag autocomplete would never suggest
  // it. The old follow-up `updateIngredient` got that for free (it runs every
  // patched tag through `normalizeTags`); register the minted labels explicitly
  // instead. Cached per apply and scoped to the labels this rewrite ADDS, so a
  // 100-row envelope registers one label once rather than re-normalizing every
  // row's whole tag array. The stored canonical label wins on casing, matching
  // what `normalizeTags` would have written.
  const universeTagLabels = new Map(); // canonical key → stored canonical label
  const registerUniverseTagLabel = async (label) => {
    const key = canonicalTagKey(label);
    if (!key) return label;
    if (universeTagLabels.has(key)) return universeTagLabels.get(key);
    const [stored] = await normalizeTags([label]);
    const canonical = stored || label;
    universeTagLabels.set(key, canonical);
    return canonical;
  };
  const friendlifyIngredientTagsBeforeApply = async (ing) => {
    const hasMarker = Array.isArray(ing?.tags) && ing.tags.some(
      (t) => typeof t === 'string' && t.trim().toLowerCase() === LEGACY_UNIVERSE_MARKER_TAG);
    if (!hasMarker) return ing;
    const map = await ensureUniverseNameMap();
    const { tags, changed } = friendlifyUniverseTags(ing.tags, (id) => map.get(id) || null, canonicalTagKey);
    if (!changed) return ing; // nothing resolvable yet — keep the machine tags for a later pass
    const inboundKeys = new Set(ing.tags.map((t) => canonicalTagKey(t)).filter(Boolean));
    const registered = [];
    for (const tag of tags) {
      // Tags the peer already sent ride through untouched (same as every other
      // peer-upserted row); only the newly minted universe names are registered.
      registered.push(inboundKeys.has(canonicalTagKey(tag)) ? tag : await registerUniverseTagLabel(tag));
    }
    return { ...ing, tags: registered };
  };

  // Tags first — they carry a `parent_id` self-FK (durably deferred by
  // upsertTagFromPeer) and are referenced by the freeform tag arrays
  // on ingredients, so we want the canonical rows present before the ingredient
  // rows land. They have no FK to scraps/ingredients, so ordering is otherwise
  // free.
  await applyKind({
    items: envelope.tags, upsertFn: upsertTagFromPeer, tally: stats.tags, lww: true,
    errLabel: 'tag', idFor: (t) => t?.id,
  });

  // Scraps next (sources FK to BOTH so we want the parents present before the
  // join rows land). Chunked scraps self-FK: a child carries
  // `parentScrapId` → another scrap row, so order PARENT rows (no
  // parentScrapId) before CHILD rows within this envelope to avoid an FK
  // violation on the child insert. (`upsertScrapFromPeer` durably defers
  // children whose parent is on a later page.)
  const orderedScraps = Array.isArray(envelope.scraps)
    ? [...envelope.scraps].sort((a, b) => {
        const aChild = a?.parentScrapId ? 1 : 0;
        const bChild = b?.parentScrapId ? 1 : 0;
        return aChild - bChild;
      })
    : envelope.scraps;
  await applyKind({
    items: orderedScraps, upsertFn: upsertScrapFromPeer, tally: stats.scraps, lww: true,
    errLabel: 'scrap', idFor: (s) => s?.id,
  });

  // Also runs on empty envelopes: persisted dependencies survive restarts.
  // Complete scraps before source joins attempt their FK-dependent writes.
  await drainPendingCatalogApplies();

  await applyKind({
    items: envelope.ingredients, upsertFn: upsertIngredientFromPeer, tally: stats.ingredients, lww: true,
    errLabel: 'ingredient', idFor: (i) => i?.id, prepare: friendlifyIngredientTagsBeforeApply,
  });

  await applyKind({
    items: envelope.sources, upsertFn: upsertSourceFromPeer, tally: stats.sources, lww: false,
    errLabel: 'source', idFor: (s) => `${s?.ingredientId}↔${s?.scrapId}`,
  });

  await applyKind({
    items: envelope.refs, upsertFn: upsertRefFromPeer, tally: stats.refs, lww: false,
    errLabel: 'ref', idFor: (r) => `${r?.ingredientId}/${r?.refKind}/${r?.refId}`,
  });

  // Relations FK BOTH ids to catalog_ingredients, so they land after the
  // ingredient upserts above (same envelope ordering rationale as sources).
  await applyKind({
    items: envelope.relations, upsertFn: upsertRelationFromPeer, tally: stats.relations, lww: false,
    errLabel: 'relation', idFor: (r) => `${r?.fromId}/${r?.kind}/${r?.toId}`,
  });

  // Media rows FK ingredient_id to catalog_ingredients, so they land after the
  // ingredient upserts. The `media_key` is a REFERENCE into the receiver's own
  // library — we store the key regardless of whether the asset is present
  // locally; a missing asset surfaces later via the metadata-missing integrity
  // endpoint, NOT as an apply failure (otherwise a slow asset transfer would
  // drop the attachment metadata entirely).
  await applyKind({
    items: envelope.media, upsertFn: upsertMediaFromPeer, tally: stats.media, lww: false,
    errLabel: 'media', idFor: (m) => `${m?.ingredientId}/${m?.kind}/${m?.mediaKey}`,
  });

  // User-defined type definitions (catalog v8). LWW-merge the incoming
  // definitions into the local settings slice: a peer's type whose `updatedAt`
  // is strictly newer than the local copy wins (or the local copy is absent —
  // first-seen); an equal clock is a no-op skip so the merge converges; a type
  // colliding with a built-in system id is skipped. The merge writes the
  // settings slice ONCE (not per-row) and refreshes the in-process registry so
  // the synced types resolve immediately. Wrapped so a settings write failure
  // tallies as a single failure rather than aborting the whole apply.
  if (Array.isArray(envelope.catalogTypes) && envelope.catalogTypes.length > 0) {
    try {
      stats.catalogTypes = await applyUserTypesFromPeer(envelope.catalogTypes);
    } catch (err) {
      stats.catalogTypes = { applied: 0, skipped: 0, failed: envelope.catalogTypes.length };
      recordFailure('catalogTypes', null, err);
    }
  }

  return stats;
}

/**
 * LWW-merge a peer's user-defined type definitions into the local settings
 * `catalogUserTypes` slice. A peer type wins when its `updatedAt` is newer than
 * the local copy's (or the local copy has none); a first-seen type is adopted.
 * Types colliding with a built-in system id are skipped. Writes the settings
 * slice once and refreshes the in-process registry. Returns `{ applied,
 * skipped, failed }`.
 */
export async function applyUserTypesFromPeer(incoming = []) {
  let applied = 0;
  let skipped = 0;
  // The LWW clock is the LATER of {updatedAt, deletedAt} — a deletion is a
  // mutation too, so a delete newer than a peer's edit wins (no resurrection)
  // and an edit newer than the delete revives. A tombstone the peer sends is
  // stored verbatim (kept in the slice, filtered out of the active registry by
  // setUserCatalogTypes) so the deletion keeps federating onward.
  const clockOf = (t) => {
    const u = typeof t?.updatedAt === 'string' ? t.updatedAt : '';
    const d = typeof t?.deletedAt === 'string' ? t.deletedAt : '';
    return d > u ? d : u;
  };
  // The read-merge-write runs inside the serialized updateUserTypes so a
  // concurrent Settings edit can't be erased by (or erase) this merge. The
  // mutator can be re-entered by a backend retry, so counters reset per run.
  await updateUserTypes((localRaw) => {
    applied = 0;
    skipped = 0;
    const local = Array.isArray(localRaw) ? localRaw : [];
    const byId = new Map(local.map((t) => [t.id, t]));
    for (const peer of incoming) {
      const id = typeof peer?.id === 'string' ? peer.id.trim() : '';
      // Skip a malformed entry or one colliding with a built-in system id —
      // system types always win and are never represented in this slice.
      if (!id || INGREDIENT_TYPE_IDS.includes(id)) { skipped++; continue; }
      const existing = byId.get(id);
      // LWW: adopt when no local copy, or the peer's clock is STRICTLY newer.
      // Must be `>`, not `>=` — an equal clock means the peer is echoing a type
      // we already hold, so re-adopting it re-writes the slice and re-counts it
      // as an applied change every cycle, and the merge never converges (the peer
      // keeps re-sending it on the next envelope). Strict `>` mirrors the SQL LWW
      // guard every other catalog kind uses (`EXCLUDED.updated_at > … .updated_at`
      // in upsertScrapFromPeer / upsertTagFromPeer): equal clock = no-op = skip.
      if (!existing || clockOf(peer) > clockOf(existing)) {
        byId.set(id, { ...peer, id });
        applied++;
      } else {
        skipped++;
      }
    }
    return applied > 0 ? [...byId.values()] : null;
  }, setUserCatalogTypes);
  if (applied > 0) console.log(`🧩 Catalog sync: merged ${applied} user type(s) from peer`);
  return { applied, skipped, failed: 0 };
}

// Total rows actually applied (inserted + updated for LWW kinds, applied for
// tuple-unique kinds) across an `applyRemoteChanges` stats object. Lives here —
// not in the federation orchestrator — so the per-kind stats SHAPE has a single
// owner; a future envelope kind only needs adding here, not in every caller.
export function countAppliedFromStats(stats = {}) {
  return (
    (stats.scraps?.inserted || 0) + (stats.scraps?.updated || 0) +
    (stats.ingredients?.inserted || 0) + (stats.ingredients?.updated || 0) +
    (stats.sources?.applied || 0) + (stats.refs?.applied || 0) +
    (stats.relations?.applied || 0) +
    (stats.tags?.inserted || 0) + (stats.tags?.updated || 0) +
    (stats.media?.applied || 0) +
    (stats.catalogTypes?.applied || 0)
  );
}

// Per-kind cursor view for the federation orchestrator. The previous scalar
// `getMaxSequence` collapsed the independent per-kind cursors into one max —
// that lied about the protocol (one cursor can't represent four independent sequences).
export { getMaxSequences };