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 DDL — scraps, ingredients, sources/refs/relations, tags, revisions,
// ingredient media, and the user-defined ingredient types. Extracted verbatim
// from ensureSchemaImpl() in server/lib/db.js (#2832) with zero behavior change.
// Parity-locked against server/scripts/init-db.sql by db.ddlParity.test.js.
//
// catalog_user_types is a separate export because in the original array it sits
// AFTER the media block, not adjacent to the other catalog tables — the composer
// (index.js) inserts it at that same position so the DDL order is byte-identical.
export const catalogPendingAppliesDdl = [
`CREATE TABLE IF NOT EXISTS catalog_pending_applies (
kind TEXT NOT NULL CHECK (kind IN ('tags', 'scraps')),
id TEXT NOT NULL,
parent_id TEXT NOT NULL,
source_updated_at TIMESTAMPTZ NOT NULL,
payload JSONB NOT NULL,
PRIMARY KEY (kind, id)
)`,
];
// Machine-local retry receipts: retained with the database backup, never federated.
export const catalogCommitReceiptsDdl = [
`CREATE TABLE IF NOT EXISTS catalog_commit_receipts (
operation_key UUID PRIMARY KEY,
fingerprint TEXT NOT NULL,
ingredients JSONB,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
)`,
];
export const catalogDdl = [
...catalogCommitReceiptsDdl,
...catalogPendingAppliesDdl,
`CREATE TABLE IF NOT EXISTS catalog_scraps (
id TEXT PRIMARY KEY,
title TEXT,
raw_text TEXT NOT NULL,
source_kind VARCHAR(32) DEFAULT 'paste',
metadata JSONB DEFAULT '{}'::jsonb,
embedding vector(768),
embedding_model VARCHAR(100),
origin_instance_id VARCHAR(36),
chunk_index INT NOT NULL DEFAULT 0,
parent_scrap_id TEXT REFERENCES catalog_scraps(id) ON DELETE CASCADE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
deleted BOOLEAN DEFAULT FALSE,
deleted_at TIMESTAMPTZ,
sync_sequence BIGSERIAL
)`,
`CREATE INDEX IF NOT EXISTS idx_catalog_scraps_embedding
ON catalog_scraps USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64)`,
`CREATE INDEX IF NOT EXISTS idx_catalog_scraps_fts
ON catalog_scraps USING gin (
to_tsvector('english', coalesce(title, '') || ' ' || coalesce(raw_text, ''))
)`,
`CREATE INDEX IF NOT EXISTS idx_catalog_scraps_sync_seq ON catalog_scraps (sync_sequence)`,
`CREATE INDEX IF NOT EXISTS idx_catalog_scraps_created_at ON catalog_scraps (created_at)`,
`CREATE INDEX IF NOT EXISTS idx_catalog_scraps_origin_instance ON catalog_scraps (origin_instance_id)`,
// Scrap chunking (catalog v7): a long paste is split into a parent row
// (chunk_index 0, raw_text = the FULL original text so the FTS index stays
// populated) plus N child rows (parent_scrap_id → parent, chunk_index 1..N,
// raw_text = the chunk slice). The extractor processes each child and unions
// results. The columns are declared inline in the CREATE above (fresh
// installs) AND re-added idempotently here for EXISTING installs (CREATE IF
// NOT EXISTS won't add columns to a pre-existing table). Existing rows
// default to chunk_index 0 / parent_scrap_id NULL — a plain non-chunked
// scrap, unchanged behavior. We do NOT retro-chunk existing rows.
`ALTER TABLE catalog_scraps ADD COLUMN IF NOT EXISTS chunk_index INT NOT NULL DEFAULT 0`,
`ALTER TABLE catalog_scraps ADD COLUMN IF NOT EXISTS parent_scrap_id TEXT REFERENCES catalog_scraps(id) ON DELETE CASCADE`,
`CREATE INDEX IF NOT EXISTS idx_catalog_scraps_parent ON catalog_scraps (parent_scrap_id)`,
`CREATE TABLE IF NOT EXISTS catalog_ingredients (
id TEXT PRIMARY KEY,
-- No DB CHECK on \`type\`: valid types are gated at the app layer via the
-- INGREDIENT_TYPES registry (catalogTypes.js / catalogValidation.js Zod
-- enum), so a new system or user-defined type needs no constraint migration.
-- VARCHAR(32) leaves headroom for longer type ids. The DROP CONSTRAINT +
-- widen for existing installs runs in the idempotent ALTER block below.
type VARCHAR(32) NOT NULL,
name TEXT NOT NULL,
payload JSONB NOT NULL DEFAULT '{}'::jsonb,
tags TEXT[] DEFAULT '{}',
embedding vector(768),
embedding_model VARCHAR(100),
origin_instance_id VARCHAR(36),
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
deleted BOOLEAN DEFAULT FALSE,
deleted_at TIMESTAMPTZ,
sync_sequence BIGSERIAL
)`,
// Relax the legacy `type` CHECK on existing installs: types are now gated at
// the app layer (INGREDIENT_TYPES registry + Zod enum), so a new system or
// user-defined type doesn't need a DROP/RE-ADD constraint migration. Postgres
// auto-named the inline CHECK `catalog_ingredients_type_check`. Both statements
// are idempotent — DROP IF EXISTS no-ops once gone; the column-type widen
// no-ops when already VARCHAR(32).
`ALTER TABLE catalog_ingredients DROP CONSTRAINT IF EXISTS catalog_ingredients_type_check`,
`ALTER TABLE catalog_ingredients ALTER COLUMN type TYPE VARCHAR(32)`,
// Postgres can't ALTER the expression of a STORED generated column, so when
// the v2 expansion needs to land we DROP and re-ADD `search_tsv`. The
// conditional below (executed after the table CREATE, before the
// ADD-only fallback) inspects pg_attrdef and rewrites the column ONLY
// when the current generation expression is missing a v2-only field
// (`physicalDescription`). That keeps boot O(1) on already-v2 installs —
// an unconditional DROP+ADD would AccessExclusive-lock the table, rewrite
// every row, and rebuild the GIN index on every server start. The one v1 ->
// v2 rewrite deliberately accepts that boot-time lock: Postgres cannot alter
// a STORED generation expression in place, and the catalog schema must be
// complete before the server becomes ready. Operators with unusually large
// catalogs should schedule that upgrade as maintenance; see docs/STORAGE.md
// ("Boot schema upgrades & lock windows") for the decision and runbook.
// Fresh installs (no column yet) fall through to the ADD IF NOT EXISTS
// below and skip the DROP entirely.
`DO $$
DECLARE
expr TEXT;
BEGIN
SELECT pg_get_expr(d.adbin, d.adrelid)
INTO expr
FROM pg_attribute a
JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum
WHERE a.attrelid = 'catalog_ingredients'::regclass
AND a.attname = 'search_tsv'
AND a.attgenerated = 's';
IF expr IS NOT NULL AND position('physicalDescription' in expr) = 0 THEN
EXECUTE 'ALTER TABLE catalog_ingredients DROP COLUMN search_tsv';
END IF;
END$$`,
`ALTER TABLE catalog_ingredients ADD COLUMN IF NOT EXISTS search_tsv tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(name, '')), 'A') ||
setweight(to_tsvector('english',
coalesce(payload->>'description', '') || ' ' ||
coalesce(payload->>'physicalDescription', '') || ' ' ||
coalesce(payload->>'personality', '') || ' ' ||
coalesce(payload->>'background', '') || ' ' ||
coalesce(payload->>'summary', '') || ' ' ||
coalesce(payload->>'notes', '') || ' ' ||
coalesce(payload->>'role', '') || ' ' ||
coalesce(payload->>'motivations', '') || ' ' ||
coalesce(payload->>'significance', '')
), 'B')
) STORED`,
`CREATE INDEX IF NOT EXISTS idx_catalog_ing_embedding
ON catalog_ingredients USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64)`,
`CREATE INDEX IF NOT EXISTS idx_catalog_ing_fts ON catalog_ingredients USING gin (search_tsv)`,
`CREATE INDEX IF NOT EXISTS idx_catalog_ing_type ON catalog_ingredients (type)`,
`CREATE INDEX IF NOT EXISTS idx_catalog_ing_tags ON catalog_ingredients USING gin (tags)`,
`CREATE INDEX IF NOT EXISTS idx_catalog_ing_sync_seq ON catalog_ingredients (sync_sequence)`,
`CREATE INDEX IF NOT EXISTS idx_catalog_ing_created_at ON catalog_ingredients (created_at)`,
`CREATE INDEX IF NOT EXISTS idx_catalog_ing_origin_instance ON catalog_ingredients (origin_instance_id)`,
`CREATE TABLE IF NOT EXISTS catalog_ingredient_sources (
ingredient_id TEXT NOT NULL REFERENCES catalog_ingredients(id) ON DELETE CASCADE,
scrap_id TEXT NOT NULL REFERENCES catalog_scraps(id) ON DELETE CASCADE,
span JSONB,
extracted_at TIMESTAMPTZ DEFAULT NOW(),
sync_sequence BIGSERIAL,
PRIMARY KEY (ingredient_id, scrap_id)
)`,
`CREATE INDEX IF NOT EXISTS idx_catalog_ing_sources_scrap ON catalog_ingredient_sources (scrap_id)`,
`CREATE INDEX IF NOT EXISTS idx_catalog_ing_sources_sync_seq ON catalog_ingredient_sources (sync_sequence)`,
`CREATE TABLE IF NOT EXISTS catalog_ingredient_refs (
ingredient_id TEXT NOT NULL REFERENCES catalog_ingredients(id) ON DELETE CASCADE,
ref_kind VARCHAR(32) NOT NULL,
ref_id TEXT NOT NULL,
role VARCHAR(64) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
deleted BOOLEAN DEFAULT FALSE,
deleted_at TIMESTAMPTZ,
updated_at TIMESTAMPTZ DEFAULT NOW(),
sync_sequence BIGSERIAL,
PRIMARY KEY (ingredient_id, ref_kind, ref_id, role)
)`,
// Idempotent upgrade path for existing installs predating the soft-delete
// columns. Without these, an old install boots the new code and silently
// hard-DELETEs on unlink (no tombstone, no sync_sequence bump) — peers
// never learn the ref was removed.
`ALTER TABLE catalog_ingredient_refs ADD COLUMN IF NOT EXISTS deleted BOOLEAN DEFAULT FALSE`,
`ALTER TABLE catalog_ingredient_refs ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ`,
// #8347: a tombstone/revival change-clock, independent of `deleted_at`
// (which is NULL again on a revival, so it can't order two conflicting
// apply attempts). Backfilled for existing rows to `deleted_at` when the
// row is already tombstoned (the most accurate change-time we have for
// it) or `created_at` otherwise, so a pre-upgrade row's clock isn't
// misdated younger than its own last known change.
`ALTER TABLE catalog_ingredient_refs ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ`,
`UPDATE catalog_ingredient_refs SET updated_at = COALESCE(deleted_at, created_at) WHERE updated_at IS NULL`,
`ALTER TABLE catalog_ingredient_refs ALTER COLUMN updated_at SET DEFAULT NOW()`,
`CREATE INDEX IF NOT EXISTS idx_catalog_ing_refs_target ON catalog_ingredient_refs (ref_kind, ref_id)`,
`CREATE INDEX IF NOT EXISTS idx_catalog_ing_refs_sync_seq ON catalog_ingredient_refs (sync_sequence)`,
// Ingredient↔ingredient edges (the catalog graph seam). `kind` is an
// app-layer enum (RELATION_KINDS in catalogTypes.js), not a DB CHECK.
// Both ids CASCADE on ingredient hard-delete; soft-delete columns from day
// one so unlinks tombstone + propagate to peers.
`CREATE TABLE IF NOT EXISTS catalog_ingredient_relations (
from_id TEXT NOT NULL REFERENCES catalog_ingredients(id) ON DELETE CASCADE,
to_id TEXT NOT NULL REFERENCES catalog_ingredients(id) ON DELETE CASCADE,
kind VARCHAR(32) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
deleted BOOLEAN DEFAULT FALSE,
deleted_at TIMESTAMPTZ,
updated_at TIMESTAMPTZ DEFAULT NOW(),
sync_sequence BIGSERIAL,
PRIMARY KEY (from_id, to_id, kind)
)`,
// #8347: same tombstone/revival change-clock as catalog_ingredient_refs —
// see that block's comment for the backfill rationale.
`ALTER TABLE catalog_ingredient_relations ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ`,
`UPDATE catalog_ingredient_relations SET updated_at = COALESCE(deleted_at, created_at) WHERE updated_at IS NULL`,
`ALTER TABLE catalog_ingredient_relations ALTER COLUMN updated_at SET DEFAULT NOW()`,
`CREATE INDEX IF NOT EXISTS idx_catalog_ing_relations_to ON catalog_ingredient_relations (to_id)`,
`CREATE INDEX IF NOT EXISTS idx_catalog_ing_relations_sync_seq ON catalog_ingredient_relations (sync_sequence)`,
// First-class canonical tag table. Additive index over the freeform
// `catalog_ingredients.tags TEXT[]` column (which stays as-is). `id` is
// deterministic (`cat-tag-<canonical-key>`) so the same tag has the same id
// on every install; `parent_id` self-FK (ON DELETE SET NULL) enables tag
// hierarchies without cascading a subtree away.
`CREATE TABLE IF NOT EXISTS catalog_tags (
id TEXT PRIMARY KEY,
label TEXT NOT NULL,
description TEXT,
color VARCHAR(32),
parent_id TEXT REFERENCES catalog_tags(id) ON DELETE SET NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
sync_sequence BIGSERIAL
)`,
`CREATE INDEX IF NOT EXISTS idx_catalog_tags_label ON catalog_tags (label)`,
`CREATE INDEX IF NOT EXISTS idx_catalog_tags_parent ON catalog_tags (parent_id)`,
`CREATE INDEX IF NOT EXISTS idx_catalog_tags_sync_seq ON catalog_tags (sync_sequence)`,
`CREATE TABLE IF NOT EXISTS catalog_ingredient_merges (
id TEXT PRIMARY KEY,
source_id TEXT NOT NULL REFERENCES catalog_ingredients(id),
survivor_id TEXT NOT NULL REFERENCES catalog_ingredients(id),
before_state JSONB NOT NULL,
after_fingerprint TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
restored_at TIMESTAMPTZ,
CHECK (source_id <> survivor_id)
)`,
`CREATE UNIQUE INDEX IF NOT EXISTS idx_catalog_merge_source_active
ON catalog_ingredient_merges (source_id) WHERE restored_at IS NULL`,
`CREATE OR REPLACE FUNCTION catalog_resolve_ingredient_id(requested_id TEXT)
RETURNS TEXT LANGUAGE SQL STABLE AS $$
SELECT COALESCE((SELECT survivor_id FROM catalog_ingredient_merges
WHERE source_id = requested_id AND restored_at IS NULL), requested_id)
$$`,
`CREATE OR REPLACE FUNCTION guard_catalog_merged_ingredient()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
IF EXISTS (SELECT 1 FROM catalog_ingredient_merges WHERE restored_at IS NULL
AND (source_id = OLD.id OR (survivor_id = OLD.id AND
(TG_OP = 'DELETE' OR NEW.deleted = true)))) THEN
RAISE EXCEPTION 'Merged ingredient is protected; recover its merge first';
END IF;
IF TG_OP = 'DELETE' THEN RETURN OLD; END IF;
RETURN NEW;
END $$`,
`DROP TRIGGER IF EXISTS trg_catalog_merged_ingredient ON catalog_ingredients`,
`CREATE TRIGGER trg_catalog_merged_ingredient BEFORE UPDATE OR DELETE ON catalog_ingredients
FOR EACH ROW EXECUTE FUNCTION guard_catalog_merged_ingredient()`,
// Append-only revision history for catalog_ingredients (local audit, not
// federated). Written by catalogDB.updateIngredient on every content change
// + a seed row on create; pruned to the last CATALOG_REVISION_RETENTION per
// ingredient at the app layer. No sync_sequence — revisions stay local; the
// synced ingredient row already LWW-merges the latest state across peers.
// Mirrors the catalog_ingredient_revisions block in init-db.sql (parity is
// asserted by db.ddlParity.test.js).
`CREATE TABLE IF NOT EXISTS catalog_ingredient_revisions (
id TEXT PRIMARY KEY,
ingredient_id TEXT NOT NULL REFERENCES catalog_ingredients(id) ON DELETE CASCADE,
name TEXT NOT NULL,
payload JSONB NOT NULL DEFAULT '{}'::jsonb,
tags TEXT[] DEFAULT '{}',
source VARCHAR(16) NOT NULL DEFAULT 'user'
CHECK (source IN ('user', 'extract', 'refine', 'sync')),
actor VARCHAR(120),
created_at TIMESTAMPTZ DEFAULT NOW()
)`,
`CREATE INDEX IF NOT EXISTS idx_catalog_ing_revisions_ingredient
ON catalog_ingredient_revisions (ingredient_id, created_at DESC)`,
// Typed media attachments — `media_key` REFERENCES the media library
// (data/images + history.jsonl sidecar) by key; the bytes are never copied
// here, so federation ships the key and the receiver matches its own
// library (missing → metadata-missing integrity surface). `kind` is an
// app-layer enum (MEDIA_KINDS in catalogTypes.js), not a DB CHECK. Soft-
// delete from day one so detaches tombstone + propagate. `metadata` stores
// bounded generation provenance (prompt, negative prompt, sampler, etc.)
// when an image carries it. Mirrors the
// catalog_ingredient_media block in init-db.sql (parity is asserted by
// db.ddlParity.test.js).
`CREATE TABLE IF NOT EXISTS catalog_ingredient_media (
ingredient_id TEXT NOT NULL REFERENCES catalog_ingredients(id) ON DELETE CASCADE,
media_key TEXT NOT NULL,
kind VARCHAR(32) NOT NULL,
role VARCHAR(64),
caption TEXT,
metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
created_at TIMESTAMPTZ DEFAULT NOW(),
deleted BOOLEAN DEFAULT FALSE,
deleted_at TIMESTAMPTZ,
updated_at TIMESTAMPTZ DEFAULT NOW(),
sync_sequence BIGSERIAL,
PRIMARY KEY (ingredient_id, media_key, kind)
)`,
// CREATE IF NOT EXISTS does not add columns to existing installs, so keep
// the provenance upgrade idempotent for rows created before this field.
`ALTER TABLE catalog_ingredient_media ADD COLUMN IF NOT EXISTS metadata JSONB NOT NULL DEFAULT '{}'::jsonb`,
// #8347: same tombstone/revival change-clock as catalog_ingredient_refs —
// see that block's comment for the backfill rationale.
`ALTER TABLE catalog_ingredient_media ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ`,
`UPDATE catalog_ingredient_media SET updated_at = COALESCE(deleted_at, created_at) WHERE updated_at IS NULL`,
`ALTER TABLE catalog_ingredient_media ALTER COLUMN updated_at SET DEFAULT NOW()`,
// Casting reference library (variant/tags/guide). Added nullable so the
// one-time seed below can tell pre-existing rows (NULL) from rows a user
// already edited; once backfilled to '{}' the seed is a no-op forever.
`ALTER TABLE catalog_ingredient_media ADD COLUMN IF NOT EXISTS library JSONB`,
`UPDATE catalog_ingredient_media SET library = '{"guide": true}'::jsonb WHERE library IS NULL AND kind = 'portrait' AND deleted = false`,
`UPDATE catalog_ingredient_media SET library = jsonb_build_object('variant', jsonb_build_object('wardrobeId', left(substring(role from 6), 59), 'label', left(role, 120))) WHERE library IS NULL AND role LIKE 'look:_%'`,
`UPDATE catalog_ingredient_media SET library = jsonb_build_object('variant', jsonb_build_object('label', left(role, 120))) WHERE library IS NULL AND kind = 'reference' AND role ILIKE '%sheet%'`,
`UPDATE catalog_ingredient_media SET library = '{}'::jsonb WHERE library IS NULL`,
`ALTER TABLE catalog_ingredient_media ALTER COLUMN library SET DEFAULT '{}'::jsonb`,
`ALTER TABLE catalog_ingredient_media ALTER COLUMN library SET NOT NULL`,
`CREATE INDEX IF NOT EXISTS idx_catalog_ing_media_ingredient ON catalog_ingredient_media (ingredient_id)`,
`CREATE INDEX IF NOT EXISTS idx_catalog_ing_media_key ON catalog_ingredient_media (media_key)`,
`CREATE INDEX IF NOT EXISTS idx_catalog_ing_media_sync_seq ON catalog_ingredient_media (sync_sequence)`,
// #12500: the LWW clock every catalog trigger stamps on a local change.
// Transaction-start NOW() let a transaction that began before a
// concurrent, already-committed edit stamp an OLDER clock than the row it
// overwrote, and peers' strict `EXCLUDED.updated_at >` guard then dropped
// it for good. The wire carries milliseconds (Date#toISOString), so the
// clock is millisecond-representable and strictly above the previous one
// at that precision; a future (skewed peer) clock is never moved backward.
`CREATE OR REPLACE FUNCTION catalog_next_change_clock(previous TIMESTAMPTZ)
RETURNS TIMESTAMPTZ AS $$
SELECT GREATEST(
date_trunc('milliseconds', clock_timestamp()),
date_trunc('milliseconds', previous) + INTERVAL '1 millisecond'
)
$$ LANGUAGE sql VOLATILE`,
`CREATE OR REPLACE FUNCTION update_catalog_ingredient_timestamp()
RETURNS TRIGGER AS $$
DECLARE
content_changed BOOLEAN;
BEGIN
content_changed := (
NEW.type IS DISTINCT FROM OLD.type OR
NEW.name IS DISTINCT FROM OLD.name OR
NEW.payload IS DISTINCT FROM OLD.payload OR
NEW.tags IS DISTINCT FROM OLD.tags OR
NEW.embedding IS DISTINCT FROM OLD.embedding OR
NEW.embedding_model IS DISTINCT FROM OLD.embedding_model OR
NEW.deleted IS DISTINCT FROM OLD.deleted OR
NEW.updated_at IS DISTINCT FROM OLD.updated_at
);
IF NOT content_changed THEN RETURN NEW; END IF;
IF NEW.updated_at IS NULL OR NEW.updated_at = OLD.updated_at THEN
NEW.updated_at := catalog_next_change_clock(OLD.updated_at);
END IF;
NEW.sync_sequence := nextval(pg_get_serial_sequence('catalog_ingredients', 'sync_sequence'));
RETURN NEW;
END;
$$ LANGUAGE plpgsql`,
`DROP TRIGGER IF EXISTS trg_catalog_ingredient_updated_at ON catalog_ingredients`,
`CREATE TRIGGER trg_catalog_ingredient_updated_at
BEFORE UPDATE ON catalog_ingredients
FOR EACH ROW
EXECUTE FUNCTION update_catalog_ingredient_timestamp()`,
`CREATE OR REPLACE FUNCTION update_catalog_scrap_timestamp()
RETURNS TRIGGER AS $$
DECLARE
content_changed BOOLEAN;
BEGIN
content_changed := (
NEW.title IS DISTINCT FROM OLD.title OR
NEW.raw_text IS DISTINCT FROM OLD.raw_text OR
NEW.source_kind IS DISTINCT FROM OLD.source_kind OR
NEW.metadata IS DISTINCT FROM OLD.metadata OR
NEW.embedding IS DISTINCT FROM OLD.embedding OR
NEW.embedding_model IS DISTINCT FROM OLD.embedding_model OR
NEW.deleted IS DISTINCT FROM OLD.deleted OR
NEW.updated_at IS DISTINCT FROM OLD.updated_at
);
IF NOT content_changed THEN RETURN NEW; END IF;
IF NEW.updated_at IS NULL OR NEW.updated_at = OLD.updated_at THEN
NEW.updated_at := catalog_next_change_clock(OLD.updated_at);
END IF;
NEW.sync_sequence := nextval(pg_get_serial_sequence('catalog_scraps', 'sync_sequence'));
RETURN NEW;
END;
$$ LANGUAGE plpgsql`,
`DROP TRIGGER IF EXISTS trg_catalog_scrap_updated_at ON catalog_scraps`,
`CREATE TRIGGER trg_catalog_scrap_updated_at
BEFORE UPDATE ON catalog_scraps
FOR EACH ROW
EXECUTE FUNCTION update_catalog_scrap_timestamp()`,
// Source-link UPDATE bumps sync_sequence so a span change (via
// `upsertSourceFromPeer` → ON CONFLICT DO UPDATE SET span = ...) doesn't
// stay invisible to peers (whose cursor would skip past the unchanged seq).
`CREATE OR REPLACE FUNCTION update_catalog_source_sync_seq()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.span IS DISTINCT FROM OLD.span THEN
NEW.sync_sequence := nextval(pg_get_serial_sequence('catalog_ingredient_sources', 'sync_sequence'));
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql`,
`DROP TRIGGER IF EXISTS trg_catalog_source_sync_seq ON catalog_ingredient_sources`,
`CREATE TRIGGER trg_catalog_source_sync_seq
BEFORE UPDATE ON catalog_ingredient_sources
FOR EACH ROW
EXECUTE FUNCTION update_catalog_source_sync_seq()`,
// Ref-link UPDATE bumps sync_sequence so a soft-delete or revival of a
// ref row ships as a normal sync event. Without this, the soft-delete
// path would update `deleted`/`deleted_at` but leave sync_sequence at
// the original INSERT value — peers past that cursor would never see
// the tombstone and their "Appears in" panels would stay stale.
// #8347: also stamps `updated_at` (unless the caller already set an
// explicit value — a peer apply carrying the sender's own clock) so the
// revival guard in `upsertRefFromPeer` has a change-clock that moves on
// BOTH a delete and a revival, unlike `deleted_at` which resets to NULL.
`CREATE OR REPLACE FUNCTION update_catalog_ref_sync_seq()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.deleted IS DISTINCT FROM OLD.deleted
OR NEW.deleted_at IS DISTINCT FROM OLD.deleted_at THEN
IF NEW.updated_at IS NULL OR NEW.updated_at = OLD.updated_at THEN
NEW.updated_at := catalog_next_change_clock(OLD.updated_at);
END IF;
NEW.sync_sequence := nextval(pg_get_serial_sequence('catalog_ingredient_refs', 'sync_sequence'));
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql`,
`DROP TRIGGER IF EXISTS trg_catalog_ref_sync_seq ON catalog_ingredient_refs`,
`CREATE TRIGGER trg_catalog_ref_sync_seq
BEFORE UPDATE ON catalog_ingredient_refs
FOR EACH ROW
EXECUTE FUNCTION update_catalog_ref_sync_seq()`,
// Relation UPDATE bumps sync_sequence on soft-delete / revival so a peer
// sees the tombstone (or un-delete) on its next pull — mirrors the ref
// trigger above, including the #8347 `updated_at` change-clock stamp.
`CREATE OR REPLACE FUNCTION update_catalog_relation_sync_seq()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.deleted IS DISTINCT FROM OLD.deleted
OR NEW.deleted_at IS DISTINCT FROM OLD.deleted_at THEN
IF NEW.updated_at IS NULL OR NEW.updated_at = OLD.updated_at THEN
NEW.updated_at := catalog_next_change_clock(OLD.updated_at);
END IF;
NEW.sync_sequence := nextval(pg_get_serial_sequence('catalog_ingredient_relations', 'sync_sequence'));
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql`,
`DROP TRIGGER IF EXISTS trg_catalog_relation_sync_seq ON catalog_ingredient_relations`,
`CREATE TRIGGER trg_catalog_relation_sync_seq
BEFORE UPDATE ON catalog_ingredient_relations
FOR EACH ROW
EXECUTE FUNCTION update_catalog_relation_sync_seq()`,
// Media UPDATE bumps sync_sequence on soft-delete/revival OR a mutable
// field (role/caption/metadata) change so a peer sees the edit/tombstone
// next pull. Mirrors the relation trigger but also watches the editable
// metadata, and stamps the same #8347 `updated_at` change-clock.
`CREATE OR REPLACE FUNCTION update_catalog_media_sync_seq()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.deleted IS DISTINCT FROM OLD.deleted
OR NEW.deleted_at IS DISTINCT FROM OLD.deleted_at
OR NEW.role IS DISTINCT FROM OLD.role
OR NEW.caption IS DISTINCT FROM OLD.caption
OR NEW.metadata IS DISTINCT FROM OLD.metadata
OR NEW.library IS DISTINCT FROM OLD.library THEN
IF NEW.updated_at IS NULL OR NEW.updated_at = OLD.updated_at THEN
NEW.updated_at := catalog_next_change_clock(OLD.updated_at);
END IF;
NEW.sync_sequence := nextval(pg_get_serial_sequence('catalog_ingredient_media', 'sync_sequence'));
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql`,
`DROP TRIGGER IF EXISTS trg_catalog_media_sync_seq ON catalog_ingredient_media`,
`CREATE TRIGGER trg_catalog_media_sync_seq
BEFORE UPDATE ON catalog_ingredient_media
FOR EACH ROW
EXECUTE FUNCTION update_catalog_media_sync_seq()`,
// Tag UPDATE bumps sync_sequence + updated_at on a mutable-field change
// (label/description/color/parent_id) so a peer sees the edit on its next
// pull. Mirrors the scrap timestamp trigger.
`CREATE OR REPLACE FUNCTION update_catalog_tag_timestamp()
RETURNS TRIGGER AS $$
DECLARE
content_changed BOOLEAN;
BEGIN
content_changed := (
NEW.label IS DISTINCT FROM OLD.label OR
NEW.description IS DISTINCT FROM OLD.description OR
NEW.color IS DISTINCT FROM OLD.color OR
NEW.parent_id IS DISTINCT FROM OLD.parent_id OR
NEW.updated_at IS DISTINCT FROM OLD.updated_at
);
IF NOT content_changed THEN RETURN NEW; END IF;
IF NEW.updated_at IS NULL OR NEW.updated_at = OLD.updated_at THEN
NEW.updated_at := catalog_next_change_clock(OLD.updated_at);
END IF;
NEW.sync_sequence := nextval(pg_get_serial_sequence('catalog_tags', 'sync_sequence'));
RETURN NEW;
END;
$$ LANGUAGE plpgsql`,
`DROP TRIGGER IF EXISTS trg_catalog_tag_updated_at ON catalog_tags`,
`CREATE TRIGGER trg_catalog_tag_updated_at
BEFORE UPDATE ON catalog_tags
FOR EACH ROW
EXECUTE FUNCTION update_catalog_tag_timestamp()`,
];
export const catalogUserTypesDdl = [
// Catalog user-defined types (Phase 4 lead-in, issue #1001). One row per
// user-defined ingredient type — the registry that defines catalog row
// semantics, moved out of data/settings.json (`catalogUserTypes`) so type
// evolution versions/syncs alongside the catalog data it governs. `id` is
// the type discriminator (the `type` column on catalog_ingredients + the
// `cat-<prefix>-<uuid>` mint seed); the full definition lives in `data`
// JSONB. updated_at / deleted_at mirror the federation LWW clock + tombstone
// (a soft-deleted type is KEPT as a tombstone row so the deletion federates
// — setUserCatalogTypes filters tombstones out of the active registry).
// ≤64 rows, read whole on every warm/sync, so no secondary index (an unused
// index is just write amplification). Mirrors the catalog_user_types block
// in init-db.sql.
`CREATE TABLE IF NOT EXISTS catalog_user_types (
id TEXT PRIMARY KEY,
data JSONB NOT NULL DEFAULT '{}'::jsonb,
updated_at TIMESTAMPTZ DEFAULT NOW(),
deleted_at TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW()
)`,
];