canonical(state): knowledge_facts dedup keys are advisory — no uniqueness on content_hash or unique_key #15673

Summary

knowledge_facts (added in #15663) carries content_hash and unique_key as plain indexes, not unique constraints, and fact_store.persist_fact reads before it inserts. Two concurrent creates of the same content can therefore commit two rows with different fact ids, and an idempotent ingest keyed on unique_key is idempotent only by convention.

Raised in review of #15668 and deliberately not fixed there. Both changes alter what the system accepts, which is a data decision rather than a refactor.

Why it was deferred

unique_key uniqueness needs the pre-existing duplicates reconciled first. The Redis index it replaces is unique_key:man_page:<k> -> fact_id, last-write-wins — so two facts sharing a unique_key is a state the current system reaches without complaint. adopt_legacy_facts() walks every fact:* hash and writes a row for each; adding the constraint first would make that adoption fail partway on any deployment carrying such a pair, which trades a soft inconsistency for hard data loss. The adoption has to run, and be observed, before the constraint can.

content_hash uniqueness is not obviously correct at all. The fingerprint is SHA-256 truncated to 16 hex characters (64 bits), matching the Redis key width. A unique constraint would make a truncated-hash collision reject a legitimately different fact rather than merely mis-deduplicate it, which is the worse failure. Either the column widens to the full digest first, or dedup stays advisory and the race is closed with an application-level lock.

Neither race is introduced by #15668 — the Redis path had exactly the same read-then-write shape.

Acceptance criteria

1. unique_key is unique (partial, allowing many NULLs), with a migration that reconciles or reports pre-existing duplicates rather than failing on them, and adopt_legacy_facts() verified against a deployment carrying at least one duplicate.
2. content_hash either widens to the full SHA-256 and gains a uniqueness guarantee, or the model states in its docstring that it is advisory and the create path takes an application-level lock — a decision recorded either way.
3. fact_store.persist_fact uses a conflict-aware insert that returns the surviving fact id, so a losing writer projects the winner rather than its own discarded row.
4. autobot_shared/store_authority.py's knowledge_facts entry states which of these keys is an enforced invariant, since a projection rebuilt from a non-unique key is not deterministic.

Open decision for the owner

Whether two facts with identical content are ever legitimate — a fact ingested by two connectors with different provenance is the case that argues yes. That answer decides criterion 2.