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.7 KB
Raw Blame
/**
 * Creative Ingredients Catalog — peer-sync change feeds & upserts.
 *
 * The `get*ChangesSince` readers page rows by their commit-ordered feed
 * position (`sync_feed`, #8315 — see server/lib/db/schema/syncFeed.js) for
 * outbound pulls; the `upsert*FromPeer` writers apply an inbound peer's rows. Mixed-
 * version federation is handled per-table: "tombstone keys absent" is treated
 * as "peer has no opinion" so a pre-tombstone peer can't revive a local delete,
 * and FK-lagged children are durably deferred until their parents arrive.
 *
 * The three tuple-unique kinds (refs, relations, media) additionally gate
 * their tombstone/revival apply on an `updated_at` change-clock (#8347): a
 * peer's `deleted`/`deleted_at` only lands when its `updated_at` is strictly
 * newer than the local row's, so a stale re-send of a still-live row (a
 * reset rewind, a role/data resend, or the #8315 upgrade replay that resends
 * every catalog row once) can't revive a tombstone that happened after the
 * peer's own clock. See `upsertRefFromPeer` for the full rationale.
 */

import { normalizeLibrary } from '../../lib/catalogMediaLibrary.js';
import { query, withTransaction, arrayToPgvector } from '../../lib/db.js';
import {
  rowToScrap,
  rowToIngredient,
  rowToRef,
  rowToSource,
  rowToRelation,
  rowToMedia,
  rowToTag,
} from './shared.js';
import { toPgJson } from '../../lib/pgJson.js';

// One page of a table's feed: rows whose committed feed position is past the
// peer's cursor, in position order. `syncSequence` on each item carries the
// FEED POSITION — the cursor unit peers store — not the row's write-time
// `sync_sequence`, which a late-committing transaction can land below a
// cursor a peer already advanced past. `stream` is one of the table-name
// literals the exported readers below pass, never caller input.
async function getFeedChangesSince(stream, mapRow, since, limit) {
  const result = await query(
    `SELECT t.*, f.position::text AS feed_position
     FROM sync_feed f
     JOIN ${stream} t ON t.sync_sequence = f.row_sequence
     WHERE f.stream = $1 AND f.position > $2
     ORDER BY f.position ASC
     LIMIT $3`,
    [stream, since, limit + 1],
  );
  const hasMore = result.rows.length > limit;
  const rows = hasMore ? result.rows.slice(0, limit) : result.rows;
  return { items: rows.map((row) => ({ ...mapRow(row), syncSequence: row.feed_position })), hasMore };
}

export const getRelationChangesSince = (since = '0', limit = 100) =>
  getFeedChangesSince('catalog_ingredient_relations', rowToRelation, since, limit);

export async function upsertRelationFromPeer(rel) {
  return withPeerIdentityWrite([rel.fromId, rel.toId], async (query) => {
  // Mirrors upsertRefFromPeer's mixed-version handling: a peer that predates
  // the relations feature never emits these rows, so there's no v1-without-
  // tombstone shape to defend against here. But we still treat "key absent"
  // as "no opinion" symmetrically in case a forked peer omits the tombstone
  // fields — preserve local state on conflict rather than coercing to false.
  const hasTombstoneFields =
    Object.prototype.hasOwnProperty.call(rel, 'deleted') ||
    Object.prototype.hasOwnProperty.call(rel, 'deletedAt');
  if (hasTombstoneFields) {
    // #8347 revival guard: `updated_at` is the tombstone/revival change-clock
    // (bumped by the trg_catalog_relation_sync_seq trigger on every
    // deleted/deleted_at flip — see catalog.js). Gate the apply on it being
    // strictly newer than the local row's, the same LWW shape as
    // upsertTagFromPeer, so a stale re-send of a still-live edge (a reset
    // rewind, a role/data resend, or the #8315 upgrade replay) can't revive a
    // local unlink that already happened. A peer that predates this field
    // (`rel.updatedAt` absent) falls back to its `deletedAt`/`createdAt`,
    // which is always older than a genuine later local tombstone.
    const updatedAtClock = rel.updatedAt || rel.deletedAt || rel.createdAt;
    await query(
      `INSERT INTO catalog_ingredient_relations
         (from_id, to_id, kind, created_at, deleted, deleted_at, updated_at)
       VALUES ($1, $2, $3, $4, $5, $6, $7)
       ON CONFLICT (from_id, to_id, kind) DO UPDATE
         SET deleted = EXCLUDED.deleted,
             deleted_at = EXCLUDED.deleted_at,
             updated_at = EXCLUDED.updated_at
       WHERE EXCLUDED.updated_at > catalog_ingredient_relations.updated_at`,
      [rel.fromId, rel.toId, rel.kind, rel.createdAt, !!rel.deleted, rel.deletedAt || null, updatedAtClock],
    );
  } else {
    await query(
      `INSERT INTO catalog_ingredient_relations (from_id, to_id, kind, created_at)
       VALUES ($1, $2, $3, $4)
       ON CONFLICT (from_id, to_id, kind) DO NOTHING`,
      [rel.fromId, rel.toId, rel.kind, rel.createdAt],
    );
  }
  });
}

export const getMediaChangesSince = (since = '0', limit = 100) =>
  getFeedChangesSince('catalog_ingredient_media', rowToMedia, since, limit);

export async function upsertMediaFromPeer(media) {
  return withPeerIdentityWrite([media.ingredientId], async (query) => {
  // Mirrors upsertRefFromPeer's mixed-version handling: a peer that predates
  // the media feature never emits these rows, so there's no pre-tombstone
  // shape to defend against. We still treat "tombstone keys absent" as "peer
  // has no opinion" so a forked peer that omits them preserves local state on
  // conflict. On INSERT a tombstone-less row defaults to deleted=false, which
  // is correct (brand-new locally, peer believes it active). role/caption and
  // metadata are adopted when the peer includes them (LWW is implicit — last
  // writer's envelope wins for these tuple-unique rows, same as refs); an older
  // peer's absent metadata is no opinion.
  const hasTombstoneFields =
    Object.prototype.hasOwnProperty.call(media, 'deleted') ||
    Object.prototype.hasOwnProperty.call(media, 'deletedAt');
  // A peer that predates media provenance has no `metadata` key. Treat that as
  // no opinion on conflict, just like the tombstone-less compatibility path,
  // so an older peer cannot erase a prompt already stored locally.
  const hasMetadata = Object.prototype.hasOwnProperty.call(media, 'metadata')
    && media.metadata && typeof media.metadata === 'object'
    && !Array.isArray(media.metadata) && Object.keys(media.metadata).length > 0;
  const metadataColumn = hasMetadata ? ', metadata' : '';
  const metadataPlaceholder = hasMetadata ? `, $${hasTombstoneFields ? 10 : 7}` : '';
  const metadataUpdate = hasMetadata ? ', metadata = EXCLUDED.metadata' : '';
  const metadataParam = hasMetadata ? [toPgJson(media.metadata || {})] : [];
  // Casting reference library (catalog v12): absent = no opinion, same as metadata.
  const hasLibrary = Object.prototype.hasOwnProperty.call(media, 'library')
    && media.library && typeof media.library === 'object' && !Array.isArray(media.library);
  const libraryColumn = hasLibrary ? ', library' : '';
  const libraryPlaceholder = hasLibrary ? `, $${(hasTombstoneFields ? 10 : 7) + (hasMetadata ? 1 : 0)}` : '';
  const libraryUpdate = hasLibrary ? ', library = EXCLUDED.library' : '';
  const libraryParam = hasLibrary ? [toPgJson(normalizeLibrary(media.library))] : [];
  if (hasTombstoneFields) {
    // #8347 revival guard — see upsertRelationFromPeer for the rationale.
    // `updated_at` also moves on a role/caption/metadata-only edit (the
    // media trigger watches those too), so the same guard additionally
    // protects a live edit from a stale peer resend, not just a tombstone.
    const updatedAtClock = media.updatedAt || media.deletedAt || media.createdAt;
    await query(
      `INSERT INTO catalog_ingredient_media
         (ingredient_id, media_key, kind, role, caption, created_at, deleted, deleted_at, updated_at${metadataColumn}${libraryColumn})
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9${metadataPlaceholder}${libraryPlaceholder})
       ON CONFLICT (ingredient_id, media_key, kind) DO UPDATE
         SET role = EXCLUDED.role,
             caption = EXCLUDED.caption,
             deleted = EXCLUDED.deleted,
             deleted_at = EXCLUDED.deleted_at,
             updated_at = EXCLUDED.updated_at${metadataUpdate}${libraryUpdate}
       WHERE EXCLUDED.updated_at > catalog_ingredient_media.updated_at`,
      [
        media.ingredientId, media.mediaKey, media.kind,
        media.role ?? null, media.caption ?? null, media.createdAt,
        !!media.deleted, media.deletedAt || null, updatedAtClock,
        ...metadataParam, ...libraryParam,
      ],
    );
  } else {
    await query(
      `INSERT INTO catalog_ingredient_media
         (ingredient_id, media_key, kind, role, caption, created_at${metadataColumn}${libraryColumn})
       VALUES ($1, $2, $3, $4, $5, $6${metadataPlaceholder}${libraryPlaceholder})
       ON CONFLICT (ingredient_id, media_key, kind) DO UPDATE
         SET role = EXCLUDED.role, caption = EXCLUDED.caption${metadataUpdate}${libraryUpdate}`,
      [media.ingredientId, media.mediaKey, media.kind, media.role ?? null, media.caption ?? null, media.createdAt, ...metadataParam, ...libraryParam],
    );
  }
  });
}

export const getTagChangesSince = (since = '0', limit = 100) =>
  getFeedChangesSince('catalog_tags', rowToTag, since, limit);

export async function upsertTagFromPeer(tag) {
  // Never materialize a child with a different parent: an equal-clock replay
  // cannot repair that under LWW. Persist the complete row on missing-parent FK.
  const apply = async (parentId) => query(
    `INSERT INTO catalog_tags
       (id, label, description, color, parent_id, created_at, updated_at)
     VALUES ($1, $2, $3, $4, $5, $6, $7)
     ON CONFLICT (id) DO UPDATE SET
       label = EXCLUDED.label,
       description = EXCLUDED.description,
       color = EXCLUDED.color,
       parent_id = EXCLUDED.parent_id,
       updated_at = EXCLUDED.updated_at
     WHERE EXCLUDED.updated_at > catalog_tags.updated_at
     RETURNING (xmax = 0) AS is_insert`,
    [
      tag.id,
      tag.label,
      tag.description ?? null,
      tag.color ?? null,
      parentId ?? null,
      tag.createdAt,
      tag.updatedAt || tag.createdAt,
    ],
  );
  let result;
  try {
    result = await apply(tag.parentId ?? null);
  } catch (err) {
    // A durable inbox write must succeed before this row is cursor-safe.
    if (err?.code === '23503' && err.constraint === 'catalog_tags_parent_id_fkey' && (tag.parentId ?? null) !== null) {
      await deferParentApply('tags', tag, tag.parentId, tag.updatedAt || tag.createdAt);
      return { applied: false, isInsert: false, deferred: true };
    } else {
      throw err;
    }
  }
  return { applied: result.rows.length > 0, isInsert: result.rows[0]?.is_insert ?? false };
}

// Per-stream maximum feed position. Each subquery is a backward scan of the
// (stream, position) index.
export async function getMaxSequences() {
  const maxOf = (stream) =>
    `COALESCE((SELECT MAX(position) FROM sync_feed WHERE stream = '${stream}'), 0)::text`;
  const result = await query(`
    SELECT
      ${maxOf('catalog_ingredients')} AS ingredients,
      ${maxOf('catalog_scraps')} AS scraps,
      ${maxOf('catalog_ingredient_sources')} AS sources,
      ${maxOf('catalog_ingredient_refs')} AS refs,
      ${maxOf('catalog_ingredient_relations')} AS relations,
      ${maxOf('catalog_tags')} AS tags,
      ${maxOf('catalog_ingredient_media')} AS media
  `);
  return result.rows[0];
}

export const getScrapChangesSince = (since = '0', limit = 100) =>
  getFeedChangesSince('catalog_scraps', rowToScrap, since, limit);

export const getIngredientChangesSince = (since = '0', limit = 100) =>
  getFeedChangesSince('catalog_ingredients', rowToIngredient, since, limit);

export const getSourceChangesSince = (since = '0', limit = 100) =>
  getFeedChangesSince('catalog_ingredient_sources', rowToSource, since, limit);

export const getRefChangesSince = (since = '0', limit = 100) =>
  getFeedChangesSince('catalog_ingredient_refs', rowToRef, since, limit);

export async function upsertScrapFromPeer(scrap) {
  // Cross-page children remain in the durable inbox, not the top-level list.
  const apply = async (parentScrapId) => query(
    `INSERT INTO catalog_scraps
       (id, title, raw_text, source_kind, metadata, embedding, embedding_model,
        origin_instance_id, chunk_index, parent_scrap_id,
        created_at, updated_at, deleted, deleted_at)
     VALUES ($1, $2, $3, $4, $5::jsonb, $6, $7, $8, $9, $10, $11, $12, $13, $14)
     ON CONFLICT (id) DO UPDATE SET
       title = EXCLUDED.title,
       raw_text = EXCLUDED.raw_text,
       source_kind = EXCLUDED.source_kind,
       metadata = EXCLUDED.metadata,
       embedding = EXCLUDED.embedding,
       embedding_model = EXCLUDED.embedding_model,
       chunk_index = EXCLUDED.chunk_index,
       parent_scrap_id = EXCLUDED.parent_scrap_id,
       updated_at = EXCLUDED.updated_at,
       deleted = EXCLUDED.deleted,
       deleted_at = EXCLUDED.deleted_at
     WHERE EXCLUDED.updated_at > catalog_scraps.updated_at
     RETURNING (xmax = 0) AS is_insert`,
    [
      scrap.id,
      scrap.title || null,
      scrap.rawText,
      scrap.sourceKind || 'paste',
      toPgJson(scrap.metadata || {}),
      scrap.embedding ? arrayToPgvector(scrap.embedding) : null,
      scrap.embeddingModel || null,
      scrap.originInstanceId || null,
      Number.isInteger(scrap.chunkIndex) ? scrap.chunkIndex : 0,
      parentScrapId,
      scrap.createdAt,
      scrap.updatedAt,
      !!scrap.deleted,
      scrap.deletedAt || null,
    ],
  );
  const parentId = scrap.parentScrapId ?? null;
  let result;
  try {
    result = await apply(parentId);
  } catch (err) {
    // A durable inbox write must succeed before this row is cursor-safe.
    if (err?.code === '23503' && err.constraint === 'catalog_scraps_parent_scrap_id_fkey' && parentId !== null) {
      await deferParentApply('scraps', scrap, parentId, scrap.updatedAt);
      return { applied: false, isInsert: false, deferred: true };
    } else {
      throw err;
    }
  }
  return { applied: result.rows.length > 0, isInsert: result.rows[0]?.is_insert ?? false };
}

export async function upsertIngredientFromPeer(ing) {
  return withPeerIdentityWrite([ing.id], async (query) => {
  const result = await query(
    `INSERT INTO catalog_ingredients
       (id, type, name, payload, tags, embedding, embedding_model,
        origin_instance_id, created_at, updated_at, deleted, deleted_at)
     VALUES ($1, $2, $3, $4::jsonb, $5, $6, $7, $8, $9, $10, $11, $12)
     ON CONFLICT (id) DO UPDATE SET
       type = EXCLUDED.type,
       name = EXCLUDED.name,
       payload = EXCLUDED.payload,
       tags = EXCLUDED.tags,
       embedding = EXCLUDED.embedding,
       embedding_model = EXCLUDED.embedding_model,
       updated_at = EXCLUDED.updated_at,
       deleted = EXCLUDED.deleted,
       deleted_at = EXCLUDED.deleted_at
     WHERE EXCLUDED.updated_at > catalog_ingredients.updated_at
       AND NOT EXISTS (SELECT 1 FROM catalog_ingredient_merges WHERE source_id = EXCLUDED.id AND restored_at IS NULL)
     RETURNING (xmax = 0) AS is_insert`,
    [
      ing.id,
      ing.type,
      ing.name,
      toPgJson(ing.payload || {}),
      ing.tags || [],
      ing.embedding ? arrayToPgvector(ing.embedding) : null,
      ing.embeddingModel || null,
      ing.originInstanceId || null,
      ing.createdAt,
      ing.updatedAt,
      !!ing.deleted,
      ing.deletedAt || null,
    ],
  );
  return { applied: result.rows.length > 0, isInsert: result.rows[0]?.is_insert ?? false };
  });
}

export async function upsertSourceFromPeer(src) {
  return withPeerIdentityWrite([src.ingredientId], async (query) => {
  await query(
    `INSERT INTO catalog_ingredient_sources (ingredient_id, scrap_id, span, extracted_at)
     VALUES ($1, $2, $3::jsonb, $4)
     ON CONFLICT (ingredient_id, scrap_id) DO UPDATE SET span = EXCLUDED.span`,
    [src.ingredientId, src.scrapId, src.span ? toPgJson(src.span) : null, src.extractedAt],
  );
  });
}

export async function upsertRefFromPeer(ref) {
  return withPeerIdentityWrite([ref.ingredientId], async (query) => {
  // ON CONFLICT DO UPDATE so a peer's soft-delete (or revival) of a ref row
  // is mirrored locally. Refs are tuple-unique (no editable content fields),
  // so `updated_at` exists ONLY as a tombstone/revival change-clock (#8347),
  // bumped by trg_catalog_ref_sync_seq whenever deleted/deleted_at flips —
  // never by a content edit, since there is no other mutable field. The
  // WHERE guard below rejects a stale apply whose clock isn't strictly newer
  // than the local row's, so a stale re-send of a still-live ref (a reset
  // rewind or the #8315 upgrade replay resending every row once) can't
  // silently revive a tombstone that happened after the peer's clock. A
  // no-op replay (peer already matches local) still bumps nothing new, so
  // it stays silent on the next outbound pull.
  //
  // Mixed-version federation: a v1 peer (pre-tombstone) emits ref rows with
  // NO `deleted`/`deletedAt` keys. Treat "key absent" as "peer has no opinion"
  // and preserve the local state on conflict — otherwise the v1 payload would
  // coerce missing-to-false and ON CONFLICT DO UPDATE would silently revive
  // a locally tombstoned ref. The `hasTombstoneFields` flag distinguishes this
  // from an explicit v2 revival (`deleted: false` present). On INSERT a v1
  // peer's row defaults to `deleted=false`, which is correct — the row is
  // brand-new locally and the peer believes it's active. A peer that carries
  // tombstone fields but predates the `updatedAt` wire field (a v2-but-not-v3
  // sender, which cannot occur once every peer ships this fix) falls back to
  // its `deletedAt`/`createdAt`, which is always older than a genuine later
  // local tombstone.
  const hasTombstoneFields =
    Object.prototype.hasOwnProperty.call(ref, 'deleted') ||
    Object.prototype.hasOwnProperty.call(ref, 'deletedAt');
  if (hasTombstoneFields) {
    const updatedAtClock = ref.updatedAt || ref.deletedAt || ref.createdAt;
    await query(
      `INSERT INTO catalog_ingredient_refs
         (ingredient_id, ref_kind, ref_id, role, created_at, deleted, deleted_at, updated_at)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
       ON CONFLICT (ingredient_id, ref_kind, ref_id, role) DO UPDATE
         SET deleted = EXCLUDED.deleted,
             deleted_at = EXCLUDED.deleted_at,
             updated_at = EXCLUDED.updated_at
       WHERE EXCLUDED.updated_at > catalog_ingredient_refs.updated_at`,
      [
        ref.ingredientId,
        ref.refKind,
        ref.refId,
        ref.role,
        ref.createdAt,
        !!ref.deleted,
        ref.deletedAt || null,
        updatedAtClock,
      ],
    );
  } else {
    // v1-shape payload: insert when missing, leave local tombstone state alone
    // on conflict. Matches the original v1 `ON CONFLICT DO NOTHING` semantics.
    await query(
      `INSERT INTO catalog_ingredient_refs
         (ingredient_id, ref_kind, ref_id, role, created_at)
       VALUES ($1, $2, $3, $4, $5)
       ON CONFLICT (ingredient_id, ref_kind, ref_id, role) DO NOTHING`,
      [ref.ingredientId, ref.refKind, ref.refId, ref.role, ref.createdAt],
    );
  }
  });
}

// The inbox is db-primary, receiver-local, and covered by the normal DB backup.
// Its key coalesces duplicate delivery; source clocks prevent an older replay
// from replacing a newer deferred edit. No success is returned before commit.
async function deferParentApply(kind, row, parentId, updatedAt) {
  await query(
    `INSERT INTO catalog_pending_applies (kind, id, parent_id, source_updated_at, payload)
     VALUES ($1, $2, $3, $4, $5::jsonb)
     ON CONFLICT (kind, id) DO UPDATE SET
       parent_id = EXCLUDED.parent_id,
       source_updated_at = EXCLUDED.source_updated_at,
       payload = EXCLUDED.payload
     WHERE EXCLUDED.source_updated_at > catalog_pending_applies.source_updated_at`,
    [kind, row.id, parentId, updatedAt, toPgJson(row)],
  );
}

export async function drainPendingCatalogApplies() {
  // No in-memory ownership: every apply (even an empty page after restart)
  // recovers ready rows. Iterate so a multi-level hierarchy drains completely.
  for (const [kind, table, upsert] of [
    ['tags', 'catalog_tags', upsertTagFromPeer],
    ['scraps', 'catalog_scraps', upsertScrapFromPeer],
  ]) {
    let progressed;
    do {
      progressed = false;
      const { rows } = await query(
        `SELECT p.id, p.payload FROM catalog_pending_applies p
         WHERE p.kind = $1 AND (
           EXISTS (SELECT 1 FROM ${table} parent WHERE parent.id = p.parent_id)
           OR EXISTS (SELECT 1 FROM ${table} current
                      WHERE current.id = p.id AND current.updated_at >= p.source_updated_at))
         ORDER BY p.id LIMIT 100`,
        [kind],
      );
      for (const pending of rows) {
        const result = await upsert(pending.payload);
        if (result.deferred) continue; // parent deleted since the readiness query
        // Crash after apply is safe (LWW replay); compare payload so concurrent
        // delivery of a newer deferred edit cannot be deleted by this drain.
        const removed = await query(
          `DELETE FROM catalog_pending_applies
           WHERE kind = $1 AND id = $2 AND payload = $3::jsonb`,
          [kind, pending.id, toPgJson(pending.payload)],
        );
        progressed ||= removed.rowCount > 0;
      }
    } while (progressed);
  }
}

async function withPeerIdentityWrite(ids, write) {
  return withTransaction(async client => {
    const exec = client.query.bind(client);
    await exec("SELECT set_config('portos.catalog_identity_peer_apply', 'on', true)");
    const { rows } = await exec(`SELECT id, state FROM catalog_identity_operations
      WHERE state IN ('prepared', 'committed', 'recovery_prepared')
        AND (source_id = ANY($1::text[]) OR survivor_id = ANY($1::text[]))`, [ids]);
    if (rows.length) return { applied: false, isInsert: false, protectedIdentity: true,
      identityPending: rows.some(row => row.state !== 'committed') };
    return write(exec);
  });
}