P1: SQLite cache schema lacks ref_name — public+private notes for one commit silently collapse

[mattfogel]

Summary

The cache schema is keyed on commit_sha alone — no ref_name dimension. Any commit that ever holds notes on both refs/notes/prompts and refs/notes/prompts-private will have one silently overwritten in the SQLite cache. prov log / prov search lose the public-side prompt for those commits.

Affects:

• The post-commit dual-write path when a session has both public and private edits matched to the same diff.
• The reindex_from(public) → overlay_from(private) sequence used by prov reindex and prov install.

Severity

P1 — silent data loss in the read pipeline. Confidence 90.

Evidence

• crates/prov-core/src/storage/sqlite.rs (cache schema definitions).
• crates/prov-cli/src/commands/hook.rs:577 (post-commit dual-write path).

Suggested fix

Add ref_name TEXT to the notes and edits tables and to all primary/unique constraints. Bump cache schema version (schema_version table) and write a migration. Update read paths to merge across refs when the resolver / search needs both.

Acceptance criteria

• Schema migration lands and tests pass on both fresh and migrated caches.
• Test: a commit with one public edit and one private edit returns BOTH from prov log <file> (subject to whatever visibility filtering ships alongside).
• Test: prov reindex against a notes layout with overlapping commit_sha entries on both refs preserves both.

Reference

/tmp/compound-engineering/ce-code-review/20260502-075634-3af3d047/correctness.json (#1).