Summary

The AOI three-layer hierarchical memory architecture (PR #2092) is non-functional. The tier promotion sweep fires correctly but every promotion attempt fails with a FOREIGN KEY constraint error, resulting in promoted=0 indefinitely.

Evidence (CI-62, live session)

tier promotion: cluster merge failed, skipping cluster_size=2 error=database error: error returned from database: (code: 787) FOREIGN KEY constraint failed
tier promotion: sweep complete candidates=10 clusters=1 promoted=0 merge_failures=1 elapsed_ms=3126

DB state: 50+ messages with session_count >= 2 (up to 15), all remain tier='episodic' — none ever promoted.

Root Cause

crates/zeph-memory/src/tiers.rs:182:

// Use a sentinel conversation_id for cross-session semantic facts.
// ConversationId(0) is used as the "no specific session" sentinel.
let sentinel_conv_id = ConversationId(0);

The messages table has:

conversation_id INTEGER NOT NULL REFERENCES conversations(id) ON DELETE CASCADE

SQLite autoincrement starts at 1. There is never a conversation with id = 0. The INSERT in promote_to_semantic() binds ConversationId(0) → FK violation → rollback.

Reproduction

Run the agent with sweep_interval_secs = 30 and any messages with session_count >= promotion_min_sessions. Wait 30s. Observe FOREIGN KEY constraint failed in logs.

Fix Options

1. Insert a sentinel conversation during DB initialization: INSERT OR IGNORE INTO conversations(id) VALUES (0) in migration. Schema allows explicit 0 since it is INTEGER PRIMARY KEY AUTOINCREMENT (SQLite allows explicit 0 inserts, though autoincrement won't reuse it).
2. Use the source conversation's ID: pick original_ids's conversation_id from the DB instead of a sentinel. Cross-session facts could use the most-recent source conversation.
3. Allow NULL conversation_id: alter schema to conversation_id INTEGER REFERENCES conversations(id) ON DELETE SET NULL for semantic tier messages. Requires a migration.

Option 1 is the simplest. Option 2 is semantically correct for cross-session promotion.

Severity

HIGH — The entire AOI promotion subsystem is silently broken. No episodic messages will ever be promoted to semantic tier. The feature is fully non-functional post-merge.

Config (testing.toml)

[memory.tiers]
enabled = true
promotion_min_sessions = 2
similarity_threshold = 0.85
sweep_interval_secs = 30
sweep_batch_size = 10