Same-KB provenance references: choosing the enforcement mechanism

[ansonnmm]

Problem

Every reference an export can resolve is protected today only by a single-column foreign key. A column FK proves the target row exists — it does not prove the target lives in the same knowledge base as the row pointing at it. Two failure shapes follow:

• The exporter mints a local IRI naming a foreign row (urn:utopia:kb:A:fact:{a fact in kb B}): a file that looks complete but points at nothing.
• Vocabulary references (predicates, entity types, parent classes, domain/range) that resolve to another KB are silently dropped at export: a slice of semantics disappears without an error.

There is a second, subtler problem: some edges are same-table self-references (facts.supersedes, facts.from_statement_id, relation_types.inverse_of, relation_types.sub_property_of). A row-level BEFORE check cannot see a sibling inserted later in the same transaction, so valid same-transaction forward references cannot be judged at row time at all.

Desired invariant

A persisted provenance/reference edge resolves to a semantically valid same-KB target at the appropriate transaction boundary — immediately where the target must already exist, at commit where forward references are legitimate.

Current proposal (PR #832)

• §0 precondition scan: refuse the migration on a ledger that already holds cross-KB rows (fail closed, report which edges and how many rows).
• Row-level BEFORE triggers on every provenance edge, column-scoped so they also catch kb_id reassignment attempts.
• DEFERRABLE INITIALLY DEFERRED constraint triggers on the same-table self-references, so COPY and multi-row inserts are judged at commit when the whole batch is visible.
• kb_id ownership immutability on every owned table: moving a referenced row to another KB would invalidate every row pointing at it.
• All function bodies pin search_path and qualify public.* so restore/empty-search_path sessions behave identically.

Design question for maintainers

Three mechanisms can express the invariant:

1. Composite foreign keys — FOREIGN KEY (kb_id, target_id) REFERENCES target (kb_id, id), replacing the existing single-column FKs. Declarative, enforced by internal RI triggers (also under session_replication_role = replica), needs no PL/pgSQL on the write path. Costs: a UNIQUE (kb_id, id) index per referenced table; only available where the source row carries its own kb_id.
2. Triggers + deferred constraint triggers — the current patch. One uniform mechanism across all edges, including edges whose KB authority is a parent row (fact_evidence follows its fact's KB, fact_qualifiers its fact's, relation_type_domains/_ranges/_qualifiers the relation's, entity_type_parents the child's, fact_derivations the derived fact's, attribute_rule_conditions the rule's, typed_fact_sources/statement_qualifiers the fact's). Those rows have no kb_id column, so a composite FK cannot name the authority without adding and synchronizing a denormalized column.
3. Hybrid — composite FKs wherever the schema can express them (roughly two-thirds of the edges; same-table self-refs become DEFERRABLE INITIALLY DEFERRED FKs, which also replaces the deferred constraint triggers), and triggers only for the owner-KB-derived edges where FKs are insufficient.

Do you prefer the uniform trigger approach, or the hybrid (declarative wherever schema-expressible, triggers only where owner-KB derivation / missing kb_id / transaction-final validation makes FKs insufficient)? Either way the §0 precondition scan and kb_id immutability stay; the reasoning will be recorded in a docs/decisions/ record alongside the write-path measurements requested in #832.

Cross-reference: PR #832.

[ansonnmm]

Resolved by #832. The hybrid mechanism landed on dev: composite (kb_id, ref) foreign keys where the source row carries its own kb_id, owner-derived triggers for the remaining edges, deferred same-table references, and the catalog completeness guard. ADR 0048 records the decision and trade-offs.