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
/**
* 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 };