# Recording-link unique-index decision ## Decision **Decision: defer the unique-index operation. No DDL is authorized.** - Decision owner: Shawn - Approved: 2026-08-23 MYT - Deadline and next review: 2026-09-06 MYT - Scope: the proposed nullable, single-column unique index on `agent_call_events.call_recording_id` The defer is time-bounded because there is currently no independent staging environment and the user has no production SSH or database access. The normal manual test path is the local `dev-chen` worktree served by Herd, using the local database `petav3_test_production_20260814`. That path is useful for UAT, but it is not staging or production evidence and cannot authorize DDL on a production or primary database. No target primary has been audited for this decision. No database operator, execution window, DDL command, or existing target unique index is claimed or approved here. The audit command and rollout procedure remain defined in [`RECORDING-LINK-UNIQUE-INDEX-RUNBOOK.md`](RECORDING-LINK-UNIQUE-INDEX-RUNBOOK.md). ## Risk accepted during defer Until a nullable single-column unique index is verified on the actual target database, the database layer cannot ultimately prevent a future bypass or an unknown writer from assigning one non-null recording to multiple call events. The reviewed application paths serialize their writes, but application locking is not a substitute for database enforcement. A missed write path and the audit-to-DDL TOCTOU window therefore remain explicit risks during this defer. ## Compensating write-path proof The application source inventory currently identifies these authorized roles that write the target `agent_call_events.call_recording_id` column: 1. Legacy matcher link: [`AgentCallEventRepository::linkRecording`](../../src/Call/Repositories/AgentCallEventRepository.php), called by [`ClickEventRecordingMatcher`](../../src/Call/Services/ClickEventRecordingMatcher.php). 2. Flutter interaction link: [`AgentCallEventRepository::linkLockedRecording`](../../src/Call/Repositories/AgentCallEventRepository.php), called by [`AgentInteractionRecordingLinker`](../../src/Call/Services/AgentInteractionRecordingLinker.php). 3. Suggestion-rejection unlink: [`AgentCallEventRepository::unlinkLockedRecording`](../../src/Call/Repositories/AgentCallEventRepository.php), called by [`CallRecordingRepository::rejectSuggestion`](../../src/Call/Repositories/CallRecordingRepository.php). `CallRecordingRepository::recordBrief` also contains a field named `call_recording_id`, but it writes `call_recording_briefs`, not `agent_call_events`, and is classified separately as an other-table writer. The legacy link path locks the event, then performs a locking current read of the complete owner set (and indexed equality gap), then locks the recording. The suggestion-rejection unlink similarly locks its expected event, the current owner set/equality gap, and then the recording. These owner reads use `AgentCallEvent::withTrashed()`, ordered by event ID, with `FOR UPDATE`. On MySQL InnoDB under REPEATABLE READ, this current locking read bypasses an older consistent-read snapshot; the `call_recording_id` secondary index makes the equality read take the required next-key/gap lock before the recording lock. The Flutter path has two distinct cases after locking the admin, badge, and event. For an existing recording, it resolves the dedupe-key hint, locks the current owner set/equality gap for that recording ID, and then locks the recording. For a first upload whose dedupe key is not yet stored, there is no recording ID whose owner set can be read: it instead performs the dedupe-key locking lookup/gap, creates the recording, and links the new ID within the same transaction. This application serialization reduces known-path races but does not provide the final database enforcement that the deferred unique index would provide. [`AuditAgentCallRecordingLinksTest`](../../tests/Feature/Call/AuditAgentCallRecordingLinksTest.php) token-scans runtime PHP under `app`, `src`, and `routes`. Its reviewed, fingerprinted occurrence inventory fails if a writer is added, removed, reclassified, or materially changed without review. It recognizes the common Eloquent, builder, property, array, and executed raw-SQL write forms covered by the regression. [`LegacyRecordingLinkInvariantTest`](../../tests/Feature/Call/LegacyRecordingLinkInvariantTest.php) uses real MySQL transactions and `pcntl` competitors to exercise link-versus- link contention, outer REPEATABLE READ snapshots, reject races, owner swaps, and active-owner-to-tombstone changes. These tests are compensating application evidence only; they do not prove the state of a remote or production database. ## Next review requirements By 2026-09-06 MYT, Shawn must reconvene the decision with a genuine owner of the exact target environment and an authorized database operator. The review must: 1. identify the exact writable primary and obtain explicit authorization for read-only inspection; 2. run `php artisan calls:audit-recording-links` against that exact primary and archive its complete output, exit status, database identity, engine/version, table size, duplicate result, and index metadata; 3. inventory every live writer, including workers, scheduled jobs, legacy clients, and manual tools; and 4. choose and approve an effective write fence plus the exact DDL or online schema-change strategy, operators, window, monitoring/abort thresholds, retry procedure, rollback procedure, and evidence location described by the runbook. If those requirements are not satisfied at the next review, the decision must remain deferred with a newly named deadline and owner. No DDL may be run merely because local `dev-chen`/Herd UAT or local automated tests pass.