# Recording-link unique-index rollout runbook

## Purpose and safety boundary

This runbook prepares a nullable unique index on
`agent_call_events.call_recording_id`. MySQL permits multiple `NULL` values in
such an index while preventing one non-null recording from being linked to
more than one event.

Task 5 already enforces the Flutter write path transactionally by locking the
interaction before the recording and rejecting a recording linked elsewhere.
That application invariant remains the protection until a separately approved
schema operation succeeds. It does not make a legacy duplicate safe, and it
does not close the audit/DDL race by itself.

`php artisan calls:audit-recording-links` is read-only. It never repairs data
or creates/drops an index. There is intentionally no Laravel migration that
audits and alters in one deploy: the facts can change after the audit, and an
unreviewed `ALTER TABLE` can block a large production table.

Staging and Production require separate Task 6A decisions and explicit
environment-owner authorization. Without that authorization, do not run DDL.

## 1. Preflight

Run these steps against the exact environment and database that would receive
the index. Do not treat a local SQLite result or a lagging replica as evidence
for a MySQL primary.

1. Choose a low-traffic time for the read-only scan. The exact row count and
   duplicate aggregation can still consume database resources on a large
   table.
2. Run and archive the complete output and exit status:

   ```bash
   php artisan calls:audit-recording-links
   ```

   Exit `0` means there are no duplicate non-null links at that instant. A
   non-zero exit means stop. The output records exact rows and linked rows,
   duplicate counts, storage engine, server version, estimated data/index/row
   sizes, and any equivalent single-column unique index already present.
3. Confirm the reported database, driver, table, engine, server version, and
   size belong to the intended primary. Have the DBA determine whether that
   exact engine/version supports the proposed online algorithm and lock level.
   A version number alone is not approval.
4. Inspect current metadata read-only if additional evidence is required:

   ```sql
   SHOW CREATE TABLE agent_call_events;
   SHOW INDEX FROM agent_call_events;
   ```

5. If the audit reports an existing unique single-column index on
   `call_recording_id`, verify it instead of adding a second index. Detection is
   based on uniqueness and indexed columns, not only the index name.
6. If duplicates exist, stop. Create a reviewed data-repair plan that traces
   every affected event and recording to authoritative business evidence.
   Never select the oldest, newest, lowest ID, or most complete row as a winner
   merely because it is convenient. Repair and re-audit are separate approved
   operations outside this command.

For reviewed investigation, this read-only query returns every duplicate
group without customer names, phones, or recording content:

```sql
SELECT call_recording_id,
       COUNT(*) AS link_count,
       MIN(id) AS first_event_id,
       MAX(id) AS last_event_id
FROM agent_call_events
WHERE call_recording_id IS NOT NULL
GROUP BY call_recording_id
HAVING COUNT(*) > 1
ORDER BY call_recording_id;
```

## 2. Authorization and write window

The Task 6A decision record must name the environment owner, database operator,
approved index name, execution method, scheduled window, abort thresholds,
retry owner, rollback owner, and evidence location.

Before execution, choose one reviewed strategy based on the reported facts:

- native MySQL online DDL only when the exact server/engine supports the
  required algorithm and lock level;
- an owner-approved online-schema-change tool with its own tested pause,
  cleanup, and rollback procedure; or
- a maintenance window with application writes quiesced when a safe online
  operation is unavailable.

Identify every current writer to `agent_call_events.call_recording_id` in the
Task 6A write-path regression. Quiesce those writes, or use another explicitly
approved deployment fence, from the final audit through index verification.
The final audit and DDL are otherwise subject to TOCTOU: a duplicate can arrive
between them. Confirm queue workers, scheduled jobs, legacy clients, and manual
tools rather than assuming HTTP maintenance mode stops all writers.

If write quiescence or an approved equivalent cannot be demonstrated, defer
with a named owner, concrete risk, and deadline. An unowned or indefinite defer
is blocked.

## 3. Execute the separately approved operation

Keep writes fenced and run the audit again immediately before DDL. Abort on a
non-zero exit or if metadata differs from the approved preflight.

The following is an example shape, not blanket authorization:

```sql
ALTER TABLE agent_call_events
    ADD UNIQUE INDEX ux_agent_call_recording_id (call_recording_id),
    ALGORITHM=INPLACE,
    LOCK=NONE;
```

The DBA must approve the exact syntax for the reported server. Do not silently
remove `ALGORITHM=INPLACE` or `LOCK=NONE` after an unsupported-operation error;
doing so can turn a rejected online operation into a blocking table rebuild.
For an external online-schema-change tool, use its reviewed command instead of
the example above and retain its logs.

Monitor metadata-lock waits, replication lag, database CPU/I/O, application
latency/errors, and the DDL session until completion. Trigger the recorded abort
procedure when a threshold is crossed.

## 4. Retry and failure handling

- Duplicate-key failure: keep the rollout stopped, verify whether a concurrent
  write bypassed the fence, run the audit, and return to reviewed data repair.
  Do not delete either link to make a retry pass.
- Metadata-lock timeout or unacceptable application impact: cancel using the
  database operator's procedure, confirm whether the index exists, restore
  service if safe, and schedule a new preflight/window. Never assume a timed-out
  client means the server stopped the operation.
- Unsupported algorithm/lock: return to strategy review. Do not retry with a
  weaker lock mode without fresh owner authorization.
- Interrupted external-tool operation: follow that tool's reviewed cleanup
  procedure and confirm no triggers, shadow tables, or partial artifacts remain
  before retrying.
- Index already exists: inspect its uniqueness and exact ordered columns. If it
  is equivalent, proceed to verification; if it conflicts, stop for DBA review.

Every retry starts again at preflight and requires a fresh final audit. A prior
clean result is not reusable.

## 5. Post-DDL verification

Keep writes fenced until all checks pass:

1. Run `php artisan calls:audit-recording-links` and archive its `0` exit and
   `Unique call_recording_id index: present (...)` result.
2. Run `SHOW INDEX FROM agent_call_events;` and verify exactly one approved
   unique index whose ordered column list is only `call_recording_id`.
3. Re-run the duplicate-group query and verify zero rows.
4. Verify table row count and representative existing event/recording links
   did not change. Creating the index must not repair or rewrite linkage.
5. Run the approved Task 5 interaction-recording upload regression in the
   staging/test environment and verify first-link, same-link retry, and
   conflicting-link behavior.
6. Release the write fence, monitor link conflicts and database health, and
   attach timing, monitoring, command output, and index metadata to the Task 6A
   decision record.

## 6. Rollback

Index removal is another production DDL operation and requires the recorded
rollback owner to authorize it. First diagnose whether the problem is the
index, application behavior, or operational load; dropping enforcement does
not restore changed data and re-opens the legacy duplicate risk.

When the approved MySQL strategy supports the required online behavior, the
rollback shape is:

```sql
ALTER TABLE agent_call_events
    DROP INDEX ux_agent_call_recording_id,
    ALGORITHM=INPLACE,
    LOCK=NONE;
```

Use the actual verified index name. Do not weaken the algorithm/lock clauses
without review. After rollback, confirm the index is absent, run the read-only
audit, verify application health, and record why rollback occurred. The Task 5
transactional invariant must remain deployed while a replacement rollout or a
new time-bounded defer is reviewed.

## Required Task 6A evidence

Task 6A is complete only when the owner records either:

- an authorized execution with preflight, exact DDL/tool command, timing,
  monitoring, index verification, rollback evidence, and post-DDL audit; or
- a time-bounded defer naming the owner, risk, compensating write-path proof,
  deadline, and next review.

This runbook and its audit command do not themselves authorize either choice.