54 lines
2.7 KiB
Markdown
54 lines
2.7 KiB
Markdown
# RB-03 CRITICAL: transcript provenance can reference deleted profiles
|
|
|
|
**Severity:** CRITICAL
|
|
**Path:** `crates/storage/src/migrations.rs:208-216`, `crates/storage/src/database.rs:61-89`, `:697-708`
|
|
**Source:** [2026-04-22 code review](../code-review-2026-04-22.md#c4--transcript-provenance-can-reference-deleted-profiles)
|
|
**Labels:** release-blocker, critical, data-integrity, storage
|
|
**Status:** RESOLVED (2026-04-22)
|
|
|
|
## Resolution
|
|
|
|
Chose the strict provenance path:
|
|
|
|
- Migration v9 rebuilds `transcripts` with
|
|
`profile_id REFERENCES profiles(id) ON DELETE RESTRICT`.
|
|
- Existing orphaned transcript `profile_id` values are reconciled onto
|
|
`DEFAULT_PROFILE_ID` during the copy into the rebuilt table.
|
|
- Because SQLite table renames rewrite dependent references, the
|
|
migration also rebuilds `segments`, recreates the transcript FTS
|
|
virtual table/triggers, and repopulates FTS from the rebuilt
|
|
transcript rows inside the same transaction.
|
|
|
|
Application-layer behaviour now matches the schema:
|
|
|
|
- `insert_transcript` rejects unknown `profile_id` values with a clear
|
|
storage error before attempting the insert.
|
|
- `delete_profile` returns a human-readable reassign-first error when
|
|
transcripts still reference that profile.
|
|
|
|
Regression tests:
|
|
|
|
- `migration_v9_reconciles_orphaned_transcript_profiles_and_adds_fk`
|
|
- `insert_transcript_rejects_unknown_profile_id`
|
|
- `delete_profile_rejects_when_transcripts_reference_it`
|
|
|
|
## Problem
|
|
|
|
v8 migration adds `transcripts.profile_id` but without a `FOREIGN KEY` constraint. `insert_transcript` accepts any `profile_id` string without validation. `delete_profile` doesn't guard against existing transcript references. The combined result: persisted transcripts can keep orphaned profile IDs indefinitely, breaking provenance integrity.
|
|
|
|
## Acceptance
|
|
|
|
- A v9 migration adds `FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE RESTRICT` (or `ON DELETE SET NULL` if soft-orphaning is preferred — decide during the fix).
|
|
- The migration reconciles existing orphans: either backfill with `DEFAULT_PROFILE_ID`, or null them, per the chosen FK semantic.
|
|
- `insert_transcript` passes the FK check — no behaviour change on the happy path.
|
|
- `delete_profile` returns a meaningful error when transcripts reference the profile being deleted (or cascades to null, matching the FK semantic).
|
|
- Regression tests: (a) delete_profile with transcript references behaves per the chosen semantic; (b) insert_transcript with a non-existent profile_id errors; (c) existing orphans are reconciled on first migration to v9.
|
|
|
|
## Fix scope
|
|
|
|
Large. FK constraint design decision + migration + reconciliation + `database.rs` updates + tests.
|
|
|
|
## Dependencies
|
|
|
|
- **Blocked by:** RB-02 (migrations atomicity — the v9 migration must be transactional).
|