# Audit Log PR — Triple-Test Aggregate

Per `rules/common/development-workflow.md` §5.1, every schema-changing commit
in this PR exercises the Idempotency / Concurrency / Multi-state triple-test
against a dedicated TiDB Cloud branch (never against shared `new-prod-essential`).

This document is a digest across all per-commit triple-test reports. For full
run logs + raw `time -p` output + `SHOW CREATE TABLE` evidence, follow the
per-commit report links below.

## Summary

| Commit | SHA       | TiDB branch / user                                                      | Idempotency | Concurrency | Multi-state (incl. empty-DB) |
|--------|-----------|-------------------------------------------------------------------------|-------------|-------------|------------------------------|
| C2     | 95991908  | 3w25gmpkZ5QKsza.root                                                    | PASS (see C2.2 rerun) | PASS | PASS |
| C2.1   | c78fea5f  | 3w25gmpkZ5QKsza.root                                                    | PASS (R1 46.94s / R2 20.05s / R3 20.85s) | PASS (~16s lock spacing × 3) | PASS (substrate-only) |
| C2.2   | 08852b8d  | 3w25gmpkZ5QKsza.root                                                    | PASS (R1 70.97s / R2 18.88s / R3 19.44s) | PASS (~15s lock spacing × 3) | PASS (empty-DB + substrate-only) |
| C3     | 091e7cc6  | 4Qbn3F3nyaU9DLk.root (branch `bran-gq763k5k5vbgrikh2y5hyb72si`)         | PASS (R1 230.39s / R2 15.93s / R3 15.91s; R2 @ 6.9%) | PASS (rc=[1,1,1] / 31s) | PASS (empty-DB rebuild 68s) |
| PRE-C5 | 3fdd53cd  | 3w25gmpkZ5QKsza.root                                                    | PASS (R1 71.17s / R2 15.91s / R3 15.98s) | PASS (3-way, ~8s stagger) | PASS (empty-DB via run #1 fresh CREATE) |
| C13    | fa0ad1c5  | 2JuXyXHYxHrLAUr.root                                                    | PASS (R1 130.40s / R2 16.50s / R3 16.29s; R2 @ 12.65%) | PASS (rc=[1,1,1] / 33s) | PASS (empty-DB rebuild 67.30s) |
| C15.1  | 4ba06bd0  | 2JuXyXHYxHrLAUr.root                                                    | PASS (R1b 80.85s / R2 15.46s / R3 15.98s; R2 @ 19.1%) | PASS (3-way, uniform resume) | PASS (fresh-state + resumed-state) |

All seven schema commits pass every dimension. Lock-serialization is observed
on every concurrency run; idempotency fast-paths ratios cluster at 6.9%–27%
of first-run wall time, well under the §5.1 "<30%" target; and every tested
commit either exercised a fresh empty-DB CREATE or a drop-and-rebuild empty-DB
path.

## Methodology per commit

Every commit was triple-tested via:

1. A fresh `/tmp/setup-<tag>` binary rebuilt from that commit's HEAD.
2. Three sequential `apply` / `apply-metadata` invocations against the same
   dedicated TiDB branch (no shared-branch contamination).
3. Three parallel invocations against the same branch, to measure advisory-lock
   serialization.
4. An empty-DB rebuild: `DROP TABLE <changed>`; `DELETE FROM _System_BootstrapState`;
   re-apply; assert `SHOW CREATE TABLE` matches the canonical DDL.

Per-commit reports:

- C2 + C2.1 + C2.2 reruns: `~/Documents/nexus/loop-state/audit-log-impl/C2-tripletest.md`
- C3: `~/Documents/nexus/loop-state/audit-log-impl/C3-tripletest.md`
- PRE-C5: `~/Documents/nexus/loop-state/audit-log-impl/PRE-C5-tripletest.md`
- C13: `~/Documents/nexus/loop-state/audit-log-impl/C13-tripletest.md`
- C15.1: `~/Documents/nexus/loop-state/audit-log-impl/C15.1-tripletest.md`

### What each commit tested

- **C2 (+C2.1, C2.2)** — `_System_AuditOutbox` new table. C2.1 widened the
  DDL validator (`CHAR(N)` / `VARCHAR(45)`) after C1 introduced `TraceId CHAR(32)`
  + `ClientIp VARCHAR(45)`. C2.2 upgraded `EnqueuedAt` / `ProcessedAt` /
  `LeaseExpiresAt` to `DATETIME(6)` per §2.4. Triple-test verifies all 9 columns
  materialize with correct types/defaults/indices; parallel applies serialize
  on the advisory lock; empty-DB rebuild regenerates DATETIME(6) fractional
  precision.
- **C3** — `_System_LoginHistory` +4 additive columns (`SessionId`,
  `LogoutTime`, `LogoutReason`, `AuthenticationMethod`). Triple-test verifies
  ALTERs land exactly once each under repeat + parallel apply; drop-and-rebuild
  regenerates the full 15-column shape; FK to `User.Id` is preserved.
- **PRE-C5** — `_System_AuditLog` reconcile: rename `ActionType`→`Action`,
  `SessionKey`→`SessionId`; add `OutboxRowId BIGINT NULL`; add
  `UNIQUE KEY uk_outbox (OutboxRowId, FieldName, ChangedAt)`. Triple-test
  verifies renames+additions on fresh CREATE and confirms `uk_outbox` is the
  load-bearing primitive for §14.3 INSERT IGNORE dedup in the absence of the
  deferred composite PK.
- **C13** — `_System_RequestCapture` new table (request-capture for §14
  replay harness). Triple-test verifies all 16 columns + 3 indices materialize;
  empty-DB rebuild regenerates the canonical DDL; PII scrubber test suite
  (9 tests) passes.
- **C15.1** — `_System_AuditLog` +7 additive NULL columns
  (`EntrypointDetail`, `RequestId`, `ParentRequestId`, `ActualUserId`,
  `UserAgent`, `Outcome`, `ErrorCode`) via `phase0_columns_reconcile`.
  Triple-test exercises fresh-state ALTER path + resumed-state short-circuit
  + 3-way concurrent resume; confirms no duplicate-column errors, final count
  24.

## Pre-existing orthogonal environment quirk

Every per-commit triple-test observed `Error 1406: Data too long for column
'Label' at row 37` during phase2 `_System_Object` registration. This is a
pre-existing row-width issue unrelated to audit-log changes (first seen
before this PR; documented in `~/.claude/projects/-Users-amber-claude-MetaDrivenPlatform4AI/memory/project_system_tables_varchar.md`).
It is deterministic (same row, same byte overflow every run), surfaces on
every setup phase, and was confirmed NOT regressed by any commit in this PR.
A follow-up issue should be filed after merge.

Because the error is deterministic and surfaces **after** the DDL phase of
interest in every run, the triple-test metrics (wall-time ratios, column
counts, lock-serialization spacing) remain valid measurements of the audit-log
schema work.

## Net schema additions in this PR

- `_System_AuditLog`: +13 NULL columns (C1 + PRE-C5 + C15.1), +`uk_outbox`,
  renames `ActionType`→`Action` and `SessionKey`→`SessionId` (PRE-C5).
- `_System_AuditOutbox`: new table (C2 + C2.2) — 9 cols, 2 indices, PK `Id`.
- `_System_LoginHistory`: +4 NULL columns (C3) — `SessionId`, `LogoutTime`,
  `LogoutReason`, `AuthenticationMethod`.
- `_System_RequestCapture`: new table (C13) — 16 cols, 3 indices, PK `RequestId`.

All column additions are nullable → backward compatible. Renames
(`ActionType`→`Action`, `SessionKey`→`SessionId`) are safe because no code
consumer existed at rename time (C5 emitter was written post-rename against
the new names).

## What is NOT triple-tested (and why)

- **`_System_AuditLog` PK migration `(Id)` → `(Id, ChangedAt)`** — deferred
  to a separate infra PR. Requires new `TableDefinition.PrimaryKeyColumns`
  plumbing through the schema-builder. `uk_outbox` alone already satisfies
  the C5 `INSERT IGNORE` dedup contract; PK extension is only a
  range-partitioning prerequisite for a later capacity phase.
- **Cross-table referential integrity (`_System_AuditLog.OutboxRowId` →
  `_System_AuditOutbox.Id`)** — no FK. Kept loose per design to avoid coupling
  the worker's write path to FK enforcement overhead on TiDB; the uniqueness
  guarantee comes from `uk_outbox`, and orphan tolerance is explicit.
- **Partitioning on `_System_RequestCapture(ReceivedAt)`** — deferred to Phase 2B
  (see C13 deviation ledger). Current schema-builder emits inline per-column
  `PRIMARY KEY`; composite PK + `PARTITION BY RANGE` requires the same
  plumbing as the AuditLog PK migration above.

## Verify-gate behavior (§5.1 "BLOCKING, not warning-only" check)

Every triple-test run observed `cmd/setup apply` / `apply-metadata` exiting
`rc=1` via `log.Fatal` when any phase (including the orthogonal `Label` quirk
above) fails. `verify` on a substrate-only post-apply state also exits `rc=1`
on drift. The apply + verify gates are both blocking, not warning-only —
§5.1 requirement satisfied.

---

*This document is the authoritative aggregate. Per-commit reports remain
under `~/Documents/nexus/loop-state/audit-log-impl/` for reviewer drilldown.*