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

4.1 KB
Raw Blame
// Read-only views retain owner-reviewed evidence without rewriting foreign rows.
// Source snapshots remain authoritative. A survivor's live rows (including
// tombstones) take precedence over its retained pre-consolidation evidence.
const ASSOCIATIONS = {
  sources: ['catalog_ingredient_sources', ['ingredient_id', 'scrap_id']],
  refs: ['catalog_ingredient_refs', ['ingredient_id', 'ref_kind', 'ref_id', 'role']],
  media: ['catalog_ingredient_media', ['ingredient_id', 'media_key', 'kind']],
  relations: ['catalog_ingredient_relations', ['from_id', 'to_id', 'kind']],
  revisions: ['catalog_ingredient_revisions', ['id']],
};

// The views project `(r).*` of their base table, so a column added to that
// table (e.g. catalog_ingredient_media.library, #12762) shifts the view's
// column list; CREATE OR REPLACE VIEW can only APPEND columns, and boot failed
// with "cannot change name of view column". Drop a view whose base table has a
// column the view lacks so the CREATE below rebuilds it. Nothing depends on these
// views (read paths query them directly), and a current view is left untouched,
// so this is idempotent on every boot and a no-op on a fresh database.
const viewDriftDropSql = (kind, table) => `DO $$ BEGIN
    IF to_regclass('catalog_identity_read_${kind}') IS NOT NULL AND EXISTS (
      SELECT column_name FROM information_schema.columns WHERE table_schema = current_schema() AND table_name = '${table}'
      EXCEPT
      SELECT column_name FROM information_schema.columns WHERE table_schema = current_schema() AND table_name = 'catalog_identity_read_${kind}'
    ) THEN
      DROP VIEW catalog_identity_read_${kind};
    END IF;
  END $$`;

export const buildCatalogIdentityViewsPreludeDdl = () => Object.entries(ASSOCIATIONS).map(([kind, [table]]) => viewDriftDropSql(kind, table));

export const buildCatalogIdentityViewsDdl = () => Object.entries(ASSOCIATIONS).map(([kind, [table, keys]]) => {
  const sourceMatch = kind === 'relations'
    ? '(live.from_id = o.source_id OR live.to_id = o.source_id)'
    : 'live.ingredient_id = o.source_id';
  const remoteSurvivorMatch = kind === 'relations'
    ? '(live.from_id = o.survivor_id OR live.to_id = o.survivor_id)'
    : 'live.ingredient_id = o.survivor_id';
  const keySql = keys.map(key => `(r).${key}`).join(', ');
  const localAliasFilter = kind === 'relations'
    ? 'AND live.from_id = catalog_resolve_ingredient_id(live.from_id) AND live.to_id = catalog_resolve_ingredient_id(live.to_id)'
    : ['refs', 'media'].includes(kind) ? 'AND live.ingredient_id = catalog_resolve_ingredient_id(live.ingredient_id)' : '';
  return `CREATE OR REPLACE VIEW catalog_identity_read_${kind} AS
    WITH active AS (SELECT * FROM catalog_identity_operations WHERE state IN ('committed', 'recovery_prepared')),
    candidates AS (
      SELECT jsonb_populate_record(NULL::${table}, item) AS r, 0 AS priority
        FROM active o CROSS JOIN LATERAL jsonb_array_elements(COALESCE(o.plan->'source'->'${kind}', '[]'::jsonb)) item
      UNION ALL
      SELECT live AS r, 1 AS priority FROM ${table} live
        WHERE NOT EXISTS (SELECT 1 FROM active o WHERE ${sourceMatch}
          OR (o.local_owner <> o.survivor_owner AND ${remoteSurvivorMatch}))
        ${localAliasFilter}
      UNION ALL
      SELECT jsonb_populate_record(NULL::${table}, item) AS r, 2 AS priority
        FROM active o CROSS JOIN LATERAL jsonb_array_elements(COALESCE(o.current_snapshot->'${kind}', o.plan->'survivor'->'${kind}', '[]'::jsonb)) item
        ${kind === 'revisions' ? '' : 'WHERE o.local_owner <> o.survivor_owner'}
      ${kind === 'revisions' ? `UNION ALL
      SELECT jsonb_populate_record(NULL::${table}, item) AS r, 3 AS priority
        FROM active o CROSS JOIN LATERAL jsonb_array_elements(COALESCE(o.plan->'survivor'->'revisions', '[]'::jsonb)) item` : ''}
      ${kind === 'media' ? `UNION ALL
      SELECT jsonb_populate_record(NULL::${table}, item) AS r, 4 AS priority
        FROM active o CROSS JOIN LATERAL jsonb_array_elements(COALESCE(o.plan->'archivedMedia', '[]'::jsonb)) item` : ''}
    ) SELECT DISTINCT ON (${keySql}) (r).*, priority IN (0, 3, 4) AS identity_retained
      FROM candidates ORDER BY ${keySql}, priority`;
});