# NexusCRM User Operation Audit Log — System Design

**Version:** v2.7 (English translation; content equivalent to v2.6)
**Date:** 2026-04-23
**Status:** Ready for Phase 2A implementation
**Changelog:**
- v1→v2: Absorbed round-2 challenger (11 findings: 3 P0 / 5 P1 / 3 P2).
- v2→v2.1: Absorbed round-3 challenger (1 new P0 + 3 P1). Core: outbox merged into Phase 2A, OwnerId normalization, dedup unique key, poison classification.
- v2.1→v2.2: Absorbed Phase 1b measurements + markdown review. Core: TiDB range partition requires partition key in PK → PK upgraded to `(Id, ChangedAt)`; FlowInterview accounts for 90% of writes; stale text corrected.
- v2.2→v2.3: Absorbed round-5 challenger. Core: `uk_outbox` had the same TiDB partition-key issue; `INSERT IGNORE` silently swallows FK violations → add `SHOW WARNINGS` check; `_System_FieldHistoryTracking` default semantics made explicit.
- v2.3→v2.4: Added §14 Replay capability (driven by Flow refactor behavior-equivalence validation). Core: new relation table `_System_RequestCapture` captures REST traffic + hardcoded PII scrub + Tolerant-mode AuditLog-equivalence assertion; replay tooling at `scripts/audit-replay/`.
- v2.4→v2.5: Absorbed round-6 challenger (1 P0 + 4 P1 + 2 P2). Core: **closed §14.3 binary Content-Type scrub bypass**; §14.6 txn semantics changed to post-commit best-effort; §14.5 added explicit scope caveat (AuditLog equivalence ≠ external side-effect equivalence; Flow refactor needs additional outbound mock recording); §14.8 added the RecordId-rewrite / re-auth / idempotency trio; §14.7 GDPR erasure; §14.10 capture **default opt-in via env gate**.
- v2.5→v2.6: Copilot review disposition (7 actionable items fixed, 3 already superseded by prior versions).
- v2.6→v2.7: Translated to English; content unchanged. Matches team artifact-language convention (docs/issues/PRs/commits all English).

---

## 0. Design Goals and Non-Goals

**Goals — answer the six dimensions:**

| Dimension | Column | Example |
|---|---|---|
| Who | `ChangedById` (+ `ActualUserId` for Login-As) | `005xxx` |
| When | `ChangedAt` (UTC, `DATETIME`; second precision in production today) | `2026-04-23T09:15:03Z` |
| Which | `ObjectApiName` + `RecordId` | `Lead` / `00Q...` |
| What | `Action` + `FieldName` + `OldValue`/`NewValue` | `UPDATE` / `Status` / `Open` / `Converted` |
| Via | `Entrypoint` + `RequestId` + `EntrypointDetail` | `REST_UI` / `req_abc` / `Flow:Lead_Convert_APAC` |
| Result | `Outcome` + `ErrorCode` | `SUCCESS` / `VALIDATION_FAILED` |

**Non-Goals (explicit):**
- No APM / tracing / metrics (those live in zerolog + OTEL).
- No SIEM (audit = factual record, not policy evaluation).
- No business activity timeline (Task/Event — L3 user data, not audit).
- **No read auditing (SELECT-event logging).** SOX/HIPAA "who read this" is out of scope for this iteration; tracked as OQ6 in §11, future standalone RFC.
- No `EventLogFile` / `FieldHistoryArchive` BigObject — no frontend consumer.

**Alignment with Salesforce (Axiom 1):** The frontend already consumes SOQL `<Object>History` (Phase 1b anchor: `frontend/src/features/admin/hooks/useAuditLogs.ts` — flat `SELECT Id, Field, OldValue, NewValue, CreatedDate, CreatedBy.Name FROM {Object}History WHERE ParentId=...`), **and that is the contract.** Backend storage is free to differ, but the read path must stay compatible.

**Multi-tenant model (clarified — v1 was wrong):** This project's `_System_*` tables **do not carry a per-row tenant column**. `grep -c tenant backend/internal/bootstrap/system_tables.json` = 0. Multi-tenancy is per-DB isolation. This design **assumes single DB = single tenant** and does not introduce a `TenantId` column.

---

## 1. Current State (Phase 1 brief)

Already in place (table names below are stable anchors via `tableName` in `backend/internal/bootstrap/system_tables.json`; line numbers drift with the json):
- `_System_AuditLog` — generic table, synchronously written by `PersistenceService.generateAuditLogs()`, main-path active.
- `_System_LoginHistory` — auth boundary, no UI reader.
- `_System_SetupAuditTrail` — async, no UI reader.
- Middleware carries `RequestId` / W3C `traceparent` / `UserId`; **missing** `clientIp` / `userAgent` / `Entrypoint` propagation.

Critical gaps:
1. `<Object>History` SF contract is not routed to `_System_AuditLog` (frontend SOQL landing point unknown — listed as Phase 1b task).
2. No Entrypoint dimension.
3. Synchronous field-level writes → a 100-field update = 100 INSERTs inside one txn.
4. Transient Flow (`WithTransientFlowInterviews`) field changes get attributed to the REST caller; Flow identity is lost.
5. No pseudo-field semantics for CREATE / DELETE / OWNER_CHANGE.

---

## 2. Data Model

**Decision:** Keep using a **single physical table** `_System_AuditLog`; synthesize the SF `<Object>History` view at read time (pending Phase 1b CH-5 verification).

### 2.1 `_System_AuditLog` extension (compatible with existing schema)

**Keep existing columns untouched**, append new columns, all NULL to stay compatible with existing rows:

```
Existing columns (unchanged):
  Id                  VARCHAR(255) PK
  ObjectApiName       VARCHAR(255) NOT NULL
  RecordId            VARCHAR(255) NOT NULL
  FieldName           VARCHAR(255) NOT NULL     -- width preserved + NOT NULL preserved
  OldValue            LONGTEXT NULL
  NewValue            LONGTEXT NULL
  ChangedById         VARCHAR(255) NOT NULL
  ChangedAt           DATETIME NOT NULL
  CreatedDate         DATETIME NOT NULL
  LastModifiedDate    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

New columns (all NULL, may be ALTERed in batches):
  Action              VARCHAR(32)  NULL   -- CREATE|UPDATE|DELETE|UNDELETE|OWNER_CHANGE|LOCK|UNLOCK|LOGIN_AS|CONVERT|APPROVE|REJECT|SUBMIT
  Entrypoint          VARCHAR(32)  NULL   -- REST_API|REST_UI|FLOW|TRIGGER|BULK_API|CONVERT|APPROVAL|SCHEDULER|SETUP|AUTH
  EntrypointDetail    VARCHAR(255) NULL   -- Flow DeveloperName / Bulk JobId / ApprovalProcess DeveloperName
  RequestId           VARCHAR(64)  NULL
  TraceId             CHAR(32)     NULL
  SessionId           VARCHAR(64)  NULL
  ParentRequestId     VARCHAR(64)  NULL   -- links Flow→Trigger chains
  ActualUserId        VARCHAR(255) NULL   -- real human behind a Login-As session
  ClientIp            VARCHAR(45)  NULL
  UserAgent           VARCHAR(512) NULL
  Outcome             VARCHAR(16)  NULL   -- SUCCESS|FAILURE
  ErrorCode           VARCHAR(64)  NULL
  SchemaVersion       TINYINT      NULL DEFAULT 2
```

**Key tradeoffs (responding to CH-2):**
- `FieldName` stays **VARCHAR(255) NOT NULL**. For CREATE / DELETE / OWNER_CHANGE / LOCK / UNLOCK we use **reserved pseudo-values** (`__created__`, `__deleted__`, `__undeleted__`, `Owner`, `__locked__`, `__unlocked__`); NULL is not required. SF itself implements pseudo-fields as strings (`Field='created'` / `Field='Owner'`), so semantics align.
- No Hash column (responding to CH-7 "compliance theater").

**PK adjustment (v2.2 new constraint):**
Current PK is `(Id)`. **TiDB range partition requires the partition key to appear in a PK or UK** (Phase 1b measured conclusion). Phase 2A must first `ALTER TABLE _System_AuditLog DROP PRIMARY KEY, ADD PRIMARY KEY (Id, ChangedAt)` before `PARTITION BY`. `Id` is already globally unique (UUID/generated), so the composite PK does not change semantics.

**Indexes (no TenantId):**
```
INDEX idx_record      (ObjectApiName, RecordId, ChangedAt DESC)
INDEX idx_user_time   (ChangedById, ChangedAt DESC)
INDEX idx_request     (RequestId)
INDEX idx_entrypoint  (Entrypoint, ChangedAt DESC)
INDEX idx_time        (ChangedAt)
```
Staging reality: only PK(Id) + `(ObjectApiName, RecordId)` + `(ChangedAt)`. Phase 2A must add the five indexes above and perform the PK upgrade.

**Partition (TiDB):**
```
ALTER TABLE _System_AuditLog PARTITION BY RANGE (TO_DAYS(ChangedAt)) INTERVAL (30 DAY)
```
Monthly rolling; drop partitions outside the window with `DROP PARTITION`. Prerequisite: PK already extended to `(Id, ChangedAt)` (see above).

**Landing path (added per Copilot review):**
- The DDL above is **TiDB physical-table dialect**, not part of the `system_tables.json` bootstrap metadata format.
- Phase 2A's `cmd/setup apply` continues to create base tables / columns / indexes in the current format; **partitioning lands via a separate migration / DDL tool** (e.g. `cmd/setup apply-partition` or an ops script), which is also responsible for partition health checks, creating future partitions, and dropping out-of-window partitions.
- Do not extend the bootstrap format with a partition field — keeps the control plane scope bounded and avoids cross-DB dialect coupling.
- **Equivalent strategy for non-TiDB MySQL deployments:** no partitioning by default, same columns and indexes, lifecycle management done at the application layer via time-range archiving / bulk delete by `ChangedAt`; introduce manual partitioning via a standalone migration only when pressure warrants it — it is not a default requirement.

**Audit-object allowlist (added in v2.2):**
Phase 1b staging measurement: `FlowInterview` accounts for ~90% of total writes (67K over 7 days). FlowInterview's `ContextData` is already skipped (§3.6), but `InterviewStatus` / `CurrentElement` field changes still generate noise. Introduce L2 admin metadata `_System_FieldHistoryTracking` (modeled after SF's "Enable Field History Tracking" per-object toggle):
```
_System_FieldHistoryTracking
  ObjectApiName   VARCHAR(255) PK
  FieldName       VARCHAR(255) PK   -- '*' means the entire object
  Enabled         BOOLEAN NOT NULL DEFAULT TRUE
```
**Semantics (made explicit in v2.3):**
- Empty table / no matching row ⇒ emitter treats as `Enabled=TRUE` (default on, preserves current behavior, no bulk-seed needed at Phase 2A migration).
- Admin explicitly `INSERT ... Enabled=FALSE` to disable a specific object/field.
- Emitter lookups use a 60s TTL in-memory cache; admin opt-out takes effect within 60s with no active invalidation channel. Accepted lag (not a hot path).
- Any admin write to `_System_FieldHistoryTracking` is recorded by `_System_SetupAuditTrail` (meta-audit, reuses existing mechanism).
- Write API and admin UI deferred to Phase 2C; Phase 2A relies on `cmd/setup apply` seeds + manual SQL.

Large tenants can selectively disable noisy objects (e.g. `FlowInterview.*`), cutting field-level writes ~10x.

### 2.2 `_System_LoginHistory` extension

Existing columns unchanged. Additions:
- `SessionId VARCHAR(64) NULL` — links with AuditLog's SessionId
- `LogoutTime DATETIME NULL`
- `LogoutReason VARCHAR(32) NULL` — `USER_LOGOUT|SESSION_EXPIRED|ADMIN_REVOKED|PASSWORD_CHANGED`
- `AuthenticationMethod VARCHAR(32) NULL` — `PASSWORD|OAUTH|SAML|MFA|API_KEY`
- `Application VARCHAR(80) NULL` — `Browser|Salesforce APIs|...` (SF-compatible)

New index `(UserId, LoginTime DESC)` (not a partition key).

### 2.3 `_System_SetupAuditTrail` (unchanged)

Only improves persistence guarantees of async writes — see §3.4.

### 2.4 New `_System_AuditOutbox`

```
_System_AuditOutbox
  Id             BIGINT AUTO_INCREMENT PK
  Payload        JSON NOT NULL           -- serialized AuditLog row
  EnqueuedAt     DATETIME(6) NOT NULL
  ProcessedAt    DATETIME(6) NULL
  Attempts       INT NOT NULL DEFAULT 0
  LeaseHolderId  VARCHAR(64) NULL         -- worker heartbeat marker, prevents competitive consumption
  LeaseExpiresAt DATETIME(6) NULL
  LastError      VARCHAR(1024) NULL
  PoisonClass    VARCHAR(32) NULL         -- NULL | PARSE_FAIL | FK_VIOLATION | RETRY_EXHAUSTED | UNKNOWN
INDEX idx_unprocessed (ProcessedAt, PoisonClass, EnqueuedAt)
INDEX idx_lease       (LeaseHolderId, LeaseExpiresAt)

-- Dedup guarantee (F3): unique key on AuditLog side, prevents duplicate consumption after lease expiry
_System_AuditLog: add OutboxRowId BIGINT NULL, UNIQUE KEY uk_outbox (OutboxRowId, FieldName, ChangedAt)
-- (v2.3 correction: TiDB partitioned tables require every UK to include the partition expression column,
--  consistent with the PK constraint. Dedup semantics remain idempotent — when a worker processes an
--  outbox row the original ChangedAt is passed through in the payload; racing workers expand to rows
--  with the same ChangedAt and are still deduplicated by uk_outbox.)

-- Worker write strategy: INSERT IGNORE alone is not sufficient
-- (v2.1's ON DUPLICATE KEY UPDATE Id=Id was also semantically wrong.)
-- v2.3 approach:
--   1. INSERT IGNORE INTO _System_AuditLog ...   -- swallows 1062 (Duplicate Entry)
--   2. SHOW WARNINGS immediately                  -- inspects every other error IGNORE silenced
--   3. If a code != 1062 appears (e.g. FK violation 1452 / data truncation 1265):
--         worker marks the outbox row with PoisonClass, does NOT set ProcessedAt, falls into §3.3 poison flow.
-- Reason: plain INSERT IGNORE also silences FK violations, lock-wait, etc. as "succeeded 0 rows"; the
-- worker would incorrectly treat it as processed → outbox.ProcessedAt gets set → a real FK violation
-- event is lost forever.
```

**Must add this table to `system_tables.json` (L1 seed change, goes through control plane).** This is L1 platform infrastructure, not L2 tenant metadata — compliant with red line #5.

---

## 3. Write Architecture

### 3.1 Unified entry point: `AuditEmitter`

```go
type AuditEmitter interface {
    Emit(ctx context.Context, event AuditEvent) error
    EmitBatch(ctx context.Context, events []AuditEvent) error
}

type AuditEvent struct {
    // REQUIRED — emitter rejects zero values (responds to CH-4 ctx fragility)
    ObjectApiName    string
    RecordId         string
    Action           AuditAction
    ChangedById      string
    Entrypoint       AuditEntrypoint   // required; NOT implicitly sourced from ctx
    Outcome          Outcome

    // Optional
    FieldDiffs       []FieldDiff        // for UPDATE
    EntrypointDetail string
    ActualUserId     string
    ErrorCode        string

    // Auto-populated from ctx (falls back to default with debug log if missing)
    RequestId        string
    TraceId          string
    SessionId        string
    ClientIp         string
    UserAgent        string
    ParentRequestId  string
}
```

**CH-4 mitigation:** `Entrypoint` is a required struct field, no longer implicitly propagated via `context.Context`. A goroutine losing its ctx cannot silently degrade to `REST_API`. CI adds a grep: every call to `Emit()` must explicitly pass `Entrypoint:`.

Six callers:
1. `PersistenceService` — CREATE / UPDATE / DELETE / UNDELETE
2. `AuthService` — LOGIN / LOGOUT / PASSWORD_CHANGE / LOGIN_AS
3. `PermissionService` — PERMISSION_GRANT / REVOKE
4. `ExportService` — EXPORT
5. `ApprovalService` — SUBMIT / APPROVE / REJECT / RECALL (business action layer)
6. `ConvertService` — outer CONVERT wrapper

### 3.2 Sync vs async

**Synchronous write (in-txn):**
- **Audit header row** (single summary row for `Action ∈ {CREATE, UPDATE, DELETE, OWNER_CHANGE, LOCK, UNLOCK}`, `FieldName=__record__`)
- `Outcome + ObjectApiName + RecordId + Action + ChangedById + ChangedAt + RequestId + Entrypoint`
- Guarantees "business change succeeded ⇔ at least a header row exists".

**Async write (out-of-txn, via outbox):**
- Field-level diff.
- At the tail of the txn, write 1 row to `_System_AuditOutbox`.
- Background worker (250ms poll, batch 500, lease lock prevents races, poison isolation) expands that row into N field-level AuditLog rows.

### 3.3 Atomicity semantics (CH-3 correction)

**v1 was wrong:** claimed "business ⇔ field-level audit atomically consistent".
**v2 correct:**
- **Header strongly consistent:** transactional guarantee. "Who changed what at what time" is never lost.
- **Field-level eventually consistent:** SLA target P99 < 5s, P99.9 < 60s. When the worker falls behind, the UI History tab shows a `PendingBacklog` marker (derived from the unprocessed outbox row count).
- **Never silently inconsistent:** what the user sees is either complete History or an explicit "processing" marker.

**Operational thresholds:**
- Outbox backlog > 1000 rows → P1 alert
- Outbox backlog > 10000 rows → UI shows "audit system busy; field-level history may lag"
- Worker failure > 5 min → PagerDuty

**Poison classification (F4):** the worker does not blanket-retry; errors are bucketed by type:

| Error type | PoisonClass | Handling |
|---|---|---|
| JSON parse failure (payload format bug) | `PARSE_FAIL` | Mark poison immediately + PagerDuty; do not count toward Attempts |
| FK violation (ChangedById references a deleted user) | `FK_VIOLATION` | Mark poison immediately + P1 alert; do not retry |
| TiDB deadlock / timeout / connection blip | NULL | Count toward Attempts, exponential backoff, after 5 → `RETRY_EXHAUSTED` |
| Other uncategorized | `UNKNOWN` | After 2 → PagerDuty human intervention |

Poison rows do not block the queue (`idx_unprocessed` excludes via `PoisonClass IS NULL`) but are retained for compliance traceability.

### 3.4 Failure policy

| Failure | Behavior |
|---|---|
| Audit header write fails | **Business txn rolls back** |
| Outbox INSERT fails | Business txn rolls back (same txn) |
| Worker keeps failing | Backlog alerts; UI shows notice; data not lost (outbox is durable); poison rows isolated, do not block others |
| Login audit fails | Login itself fails (auth-boundary audit must not be lost) |
| Setup/Permission async audit fails | Alert, does not block admins (existing behavior preserved) |

### 3.5 Entrypoint identification

```go
type AuditContext struct {
    Entrypoint       AuditEntrypoint
    EntrypointDetail string
    ParentRequestId  string
}
```

Injection points:
- REST middleware: an `X-Client-Source` header distinguishes UI vs API; defaults to `REST_API`.
- FlowExecutor (including transient): wraps ctx before flow entry, overwriting Entrypoint (stack push).
- TriggerEngine: same.
- BulkInsertService / ConvertService / ApprovalService / SchedulerService: each wraps itself.

**ctx-loss defense (CH-4):**
- `Emit()` accepts an explicit `Entrypoint` parameter (not implicitly read from ctx).
- If caller omits it → emitter uses `UNKNOWN` + WARN log + Prometheus counter. Does not silently fall back to `REST_API`.
- CI static check: every `_ = emitter.Emit(ctx, AuditEvent{...})` must assign `Entrypoint:` explicitly (golangci-lint custom rule).

**Recursive Flows (CH-9 tradeoff):** Use **stack-top recording + ParentRequestId chain**. Accept the information loss when parallel Flows write to the same field (the last writer wins); documented as a known gap in §11 OQ5; future work may add `EntrypointStack VARCHAR(512)`.

### 3.6 Diff capture

- **LongText / RichText / Encrypted / MultiPicklist / Blob:** SF semantics — both sides `NULL`. **No** Hash (CH-7 withdrawn). Organizations that need the plaintext for compliance should use the future Field Audit Trail BigObject archive.
- **System fields** (`CreatedDate`, `SystemModstamp`, `LastModifiedDate`): skipped.
- **ContextData** (FlowInterview blob): skipped.
- **PII blacklist** (`Password, SessionKey, ApiToken, MfaSecret`): the emitter layer drops both values; `FieldName` is still recorded.
- **OwnerId normalization (F2):** any write to the `OwnerId` column is **forced** by the emitter to `Action=OWNER_CHANGE, FieldName=Owner`, aligned with SF's pseudo-field semantics. Even if a business caller hits the generic `PATCH /sobjects/Account/001` and modifies `OwnerId`, the audit side still emits an OWNER_CHANGE row, avoiding the double form (`Action=UPDATE, FieldName=OwnerId` vs `Action=OWNER_CHANGE, FieldName=Owner`).

### 3.7 Bulk / Convert / Approval special paths

- Bulk: `Entrypoint=BULK_API, EntrypointDetail=<job_id>`.
- Convert: outer `Entrypoint=CONVERT, EntrypointDetail=<convertRequestId>`; the four sub-mutations share ParentRequestId.
- Approval: business-action events (SUBMIT / APPROVE / REJECT) are emitted explicitly, distinct from the field-change dual-write.

---

## 4. Permission Model

**Three platform permission keys (L1, mirrored on SF `ViewAllData` / `ViewSetup`):**

| Permission Key | Grants |
|---|---|
| `ViewSelfAuditTrail` | All authenticated users; only `ChangedById = me` or records where `RecordId` is visible to me via FLS Read |
| `ViewAllAuditTrail` | Compliance / admin PermissionSet |
| `ViewSetupAuditTrail` | Admins |

### 4.1 FLS redaction — query-plan layer (CH-6 correction)

**v1 was wrong:** replacing `OldValue` with `***REDACTED***` at response row-format time. Leaks information via `ORDER BY OldValue` / `WHERE OldValue LIKE ...` side channels.

**v2 correct:** FLS sinks into **query-plan rewriting**:
1. Parse the user's SOQL/REST query, enumerate the `FieldName` set involved.
2. For each FieldName, look up the caller's FLS Read permission.
3. For fields lacking permission — **precisely distinguish predicate position** (F6, correcting v2's blanket rule):
   - Appearing in `SELECT` → response returns `OldValue=null, NewValue=null, Redacted=true` flag.
   - Predicate on the `OldValue` / `NewValue` value (e.g. `WHERE OldValue LIKE '250%'` on a denied FieldName) → **reject with 400**.
   - Predicate on metadata columns such as `FieldName` / `ChangedAt` / `ChangedById` → **allow**, only redact the corresponding value at output time.
   - `ORDER BY ChangedAt`, `ORDER BY CreatedDate`, etc. row-level metadata sort → allow (common for compliance/admin queries).
4. `FieldName` itself is visible (SF semantics).
5. `ChangedById` is not subject to FLS but is subject to user-visibility rules.

### 4.2 PII / security-sensitive fields

Emitter layer has a built-in blacklist (`Password/SessionKey/ApiToken/MfaSecret`); both sides' values written as NULL. This falls under CLAUDE.md's "Permitted hardcoded strings — security-sensitive field allowlist, gated before FLS", same as §4.2.

### 4.3 Audit-the-audit (CH-8 correction)

The `EXPORT_AUDIT` event is emitted **only once at the API boundary** (in `AuditQueryService.Query()` before returning), **not** on each DB read. Prevents:
- `AuditQueryService` emitting internally → reading `_System_AuditLog` → emitting again → infinite loop.
- Isolation: when emitter sees `Action == EXPORT_AUDIT`, it routes through a dedicated "meta emit" function that bypasses the query layer.

---

## 5. Query API

### 5.1 SF contract-compatible read path (pending Phase 1b verification — see §9)

The SOQL parser recognizes `FROM <ObjectName>History` or `FROM <ObjectName>__History`:
1. Maps to `SELECT ... FROM _System_AuditLog WHERE ObjectApiName='<ObjectName>' AND RecordId=<bind>`.
2. Column mapping: `Field`→`FieldName`, `OldValue`/`NewValue`/`CreatedDate`→`ChangedAt`, `CreatedById`→`ChangedById`.
3. Pseudo-fields: rows with `Action=CREATE, FieldName=__created__` translate back to SF's `Field='created'`; other pseudos similarly.
4. FK relationship `CreatedBy.Name`: keep support (ChangedById is a User FK).

**Required in Phase 1b:** enumerate the SOQL shapes the frontend actually uses (CH-5):
- Flat: `SELECT Id,Field,OldValue FROM AccountHistory WHERE ParentId=...` ✓ supported
- Subquery: `SELECT Id,(SELECT Field FROM Histories) FROM Account` — needs confirmation
- Aggregate: `SELECT COUNT() FROM LeadHistory` — needs confirmation
- Relationship FK: `SELECT CreatedBy.Name FROM AccountHistory` — needs confirmation

A list of unsupported shapes must be produced before the end of Phase 1b, determining whether to fall back to physical per-object tables.

### 5.2 Native admin API

- `GET /services/data/v63.0/audit/trail?user=&from=&to=&entrypoint=` — requires `ViewAllAuditTrail`
- `GET /services/data/v63.0/audit/request/{requestId}` — every change from a single request
- `GET /services/data/v63.0/audit/login-history?user=`
- `GET /services/data/v63.0/audit/setup-trail?section=`

No write endpoints.

---

## 6. Performance and Capacity Estimation (CH-10 correction)

**Recalibrated assumptions (medium-to-high activity org):**
- 2000 DAU, 30 record updates / user / day (incl. Flow-triggered)
- Average 3 fields changed per update
- **Flow automation amplification** 3× (each UI update may trigger 2–3 downstream cascading automated updates) — missed in v1
- 18 months retention

**Estimate:**
- Header rows: 2000 × 30 × 3 = 180K/day
- Field-level: 180K × 3 = 540K/day
- 18-month total: (180K + 540K) × 30 × 18 ≈ **~390M rows**
- Avg row size 1.5KB → ~600GB raw
- TiDB column-compressed ≈ 200GB
- + 4 indexes ≈ +150GB

**v2 is still conservative (within the 3× range flagged by CH-10); for high-concurrency large tenants (10×) that exceed, fallback strategies:**
- Switch monthly partitions → weekly.
- Archive cold partitions to object storage (TiDB TiFlash hot/cold tiering).
- Field-level diff only for fields marked "Field History Enabled" (SF semantics; admin-configured L2 metadata).

**TiDB partition pruning notes:**
- All queries' `WHERE` should include a `ChangedAt` range to trigger pruning.
- `idx_record (ObjectApiName, RecordId, ChangedAt)` is a local index, effective within a partition; cross-partition scans follow PK paths — per TiDB docs this is O(partition count), needs Phase 2A measurement.
- Write latency: 4 indexes × 16 writes/s peak → TiDB P99 well under 10ms, not a bottleneck.

---

## 7. Integration with Flow / Trigger / Approval

| Scenario | Row count | Entrypoint | EntrypointDetail | ParentRequestId |
|---|---|---|---|---|
| UI user edits Lead.Status | 1 head + 1 field | `REST_UI` | null | null |
| REST API PATCH /sobjects/Lead/001 | 1+1 | `REST_API` | `<client_id>` | null |
| Flow "Update Related Account" triggered | 1+N | `FLOW` | `Update_Related_Account` | `<upstream REST requestId>` |
| Trigger BeforeUpdate | 1+N | `TRIGGER` | `LeadBeforeUpdateTrigger` | `<upstream Flow requestId>` |
| Approval Submit | 1 APPROVAL_SUBMIT + 1 UPDATE(Locked) | `APPROVAL` | `<ProcessDeveloperName>` | null |
| Lead Convert | 1 CONVERT + 4 × sub-mutations | `CONVERT` | `<convertReqId>` | sub-mutations share this |
| Bulk 10K | 10K heads + fields | `BULK_API` | `<jobId>` | null |
| Scheduled Flow | 1+N | `SCHEDULER` | `<CronName>` | null |

---

## 8. Metadata-Driven Red Line Self-Check

| # | Check | v2 status |
|---|---|---|
| 1 | `__c` field name literal | ✗ none |
| 2 | ProfileId branching | ✗ none (permission goes through PermissionSet keys) |
| 3 | Default / RecordType hardcoded | ✗ none (Action / Entrypoint enums are L0 platform constants) |
| 4 | Business-enum switch | ✗ none |
| 5 | Tenant-specific table in SQL | ✗ none. Only reads/writes `_System_AuditLog` / `_System_LoginHistory` / `_System_SetupAuditTrail` / `_System_AuditOutbox`. `_System_AuditOutbox` is a **new L1 seed**, added via `system_tables.json` + the control-plane channel — compliant. |
| 6 | Field write permission hardcoded | ✗ none (FLS is a runtime lookup; the PII blacklist is in the permitted range) |
| 7 | SObject-name switch | ✗ none (all SObjects treated uniformly) |
| 8 | Status literal switch | ✗ none |

**Two-tenant test:** cross-tenant / new-org deployments do not depend on tenant-specific schema or metadata, and we do not introduce a per-row TenantId; this design is directly reusable under the current TiDB prerequisite (this design **depends on** TiDB partition semantics; MySQL deployments fall back to the §2.1 equivalent strategy). ✓
**Admin-configure test:** if an admin wants to add an Entrypoint (e.g. `MOBILE_APP`), that requires a code PR. This is a platform-capability extension, not a tenant business-rule change — same semantics as SF's `LoginHistory.Application` constant, compliant. ✓

---

## 9. Implementation Roadmap

### Phase 1b — research (coding gate)

**Phase 1b execution result (completed 2026-04-23):**
1. ✅ **Frontend SOQL shapes:** the sole active pattern is in `useAuditLogs.ts`, flat `WHERE ParentId=?` + `CreatedBy.Name` FK traversal. Verdict: **virtual view is sufficient**, no need to fall back to per-object physical tables.
2. ⚠️ **Staging write-rate measurement:** median 10.7K rows/day (staging traffic ≈ 1/70 of prod). The 400M-row 18-month estimate cannot be falsified from staging alone, but single-row avg **270B** is measured; 400M × 270B ≈ 108GB data + similar indexes ≈ ~200GB total, consistent with §6. **New finding:** `FlowInterview` is 90%+ of staging writes, motivating the §2.1 allowlist design.
3. ⚠️ **TiDB partition pruning:** the current table is not partitioned. EXPLAIN showed `WHERE ChangedById=... AND ChangedAt>...` performing a TableFullScan (confirms `idx_user_time` is required). **New constraint:** TiDB range partition requires the partition key in PK/UK → must upgrade PK to `(Id, ChangedAt)` first (folded into §2.1).
4. ✅ **Audit write-path inventory:** single-point `PersistenceService.Update() → generateAuditLogs()` (line 697 of persistence_service.go). No bypass — Collections / Folders / Standard Actions / Bulk Upsert all flow through. Insert/Delete does not produce field-level diff (aligned with SF). The `AuditEmitter` abstraction in Phase 2A has zero blind spots.
5. ✅ **`cmd/setup apply` supports new `_System_*` tables**: fully declarative — add an entry to `system_tables.json` (example: `_System_AuditLog`). `cmd/setup apply` → `ApplyMetadata()` → `InitializeSchema()` uses `CREATE IF NOT EXISTS` + column reconcile, held under `GET_LOCK('nexuscrm-bootstrap', 300)` for concurrency safety; idempotent. `_System_AuditOutbox` requires zero manual migration.

Complete Phase 1b evidence: `phase1b-code.md` + `phase1b-db.md` (local-only per project convention storing research/test output under `~/Documents/nexus/<area>/`).

**Phase 1b → Phase 2A decision matrix (F5):**

| Phase 1b finding | Phase 2A strategy |
|---|---|
| Frontend uses only flat `WHERE ParentId=?` queries | Virtual-view approach works directly; §5.1 translator is sufficient |
| Frontend uses `SELECT (SELECT FROM Histories) FROM Account` subqueries | Extend SOQL parser to support correlated subqueries; if effort >2 weeks → **fall back to per-object physical tables** (at least 10 core SObjects) |
| Frontend uses `SELECT CreatedBy.Name FROM LeadHistory` FK traversal | Keep FK translation layer's `ChangedById → User` semantics; low complexity, keep virtual view |
| Frontend uses `SELECT COUNT() FROM LeadHistory WHERE ...` aggregate | Virtual view supports — aggregates push down to the physical `_System_AuditLog` |
| Prod write rate > 1M/day | Enable SF's per-field-enable metadata switch, only write for flagged fields (L2 admin config, deferred to Phase 2C) |
| TiDB pruning fails | Reduce partition granularity to weekly; or split into `_System_AuditLog_YYYYMM` physical shards |

Decision recorded in `phase1b-decision.md`, kicks off Phase 2A.

### Phase 2A — contract + context propagation + outbox infra (merged, F1)

**v2 originally** deferred outbox to 2B, but the §3.3 atomicity SLA (strong header + eventually consistent field-level P99<5s) depends on outbox existing; without it, a 100-field UPDATE is 100 INSERTs in the txn. v2.1 merged, avoiding a 2A that knowingly misses the SLA.

1. `ALTER TABLE _System_AuditLog` to append the new columns + `OutboxRowId` + `UNIQUE KEY uk_outbox` (online DDL, batched).
2. New `_System_AuditOutbox` (via `system_tables.json` + control-plane apply).
3. Extract `AuditEmitter` interface; migrate `generateAuditLogs` → `emitter.Emit`.
4. Inject Entrypoint for REST / Flow / Trigger / Bulk.
5. SOQL `<Object>History` → `_System_AuditLog` view (translator complexity driven by Phase 1b's actual-shape list).
6. **Synchronous header + async field-level (outbox)** in one go. Minimum viable worker: lease + poison classification + dedup unique key.
7. Prometheus metrics + UI "History pending" indicator.

**Why this is not scope creep:** outbox itself is a flat table + single worker + 250ms polling, ~500 LOC, far less than the hidden cost of "deploy twice, maintain two different atomicity contracts, migrate data Phase 2A→2B".

### Phase 2B — stability + horizontal scaling

1. Multi-instance worker load balancing (lease-based, already built).
2. TiFlash hot/cold tiering (>6 months of partitions move to column storage).
3. Backpressure / rate-limit (outbox backlog triggers upstream write throttling).

### Phase 2C — coverage expansion

1. Login-As (`ActualUserId`) — auth-layer rework.
2. Export / Report download auditing.
3. Session linking (`SessionId`).
4. `EXPORT_AUDIT` meta-audit.

---

## 10. Compatibility and Migration (CH-2 correction)

**v1 migration error is corrected:**
- ❌ v1: `ALTER FieldName VARCHAR(80) NULL` — narrowing AND nullifying, dangerous.
- ✅ v2: Keep `FieldName VARCHAR(255) NOT NULL` unchanged; use reserved pseudo-values (`__record__`, `__created__`, `__deleted__`, `Owner`, `__locked__`, `__unlocked__`, `__undeleted__`) for record-level actions.

**New column ALTER:**
- All `NULL DEFAULT NULL` → TiDB online DDL supports this, no lock.
- Execute in 3 batches (≤5 cols each) to reduce risk.
- Reading existing rows: queries filter `IS NULL` for compatibility when Entrypoint / RequestId are missing.

**SOQL translator fallback:** old rows without Action column → default-output `Action=UPDATE`, doesn't crash.

---

## 11. Risks and Open Questions

| # | Risk | Impact | Mitigation | Status |
|---|---|---|---|---|
| R1 | Single table ~400M rows (recalibrated) | Query slowdown | idx_record is partition-local + Phase 1b measurement | Pending verification |
| R2 | Outbox backlog delays UI History | UX | Lease worker + poison isolation + UI pending indicator | Mitigated |
| R3 | Transient Flow Entrypoint injection broad | Implementation cost | Phase 2A core path (REST / Flow / Trigger / Bulk all injected); multi-instance extension Phase 2B | Merged into 2A |
| R4 | Single physical table vs per-object History | Long-term contract drift | Phase 1b verifies SOQL shapes; evolvable | Accepted |
| R5 | Recursive Flow races lose parallel Flow identity | Audit info loss | Stack-top + ParentRequestId; future EntrypointStack | Accepted (CH-9) |
| R6 | Encrypted/LongText both sides null | Compliance value not verifiable | SF semantics + future FieldAuditTrail BigObject | Accepted this phase (CH-7) |
| R7 | ctx loss causes Entrypoint=UNKNOWN | Silent error | `Entrypoint` struct-required + CI lint | Mitigated (CH-4) |
| R8 | FLS side channel (WHERE / ORDER BY) | Information leak | Query-plan-layer reject, not row-layer mask | Mitigated (CH-6) |

**Open Questions:**
- **OQ1:** 18-month retention vs 7-year? Compliance industries may require longer. Default 18 months; extended retention needs FieldHistoryArchive (future BigObject, not this phase).
- **OQ2:** Is `EXPORT_AUDIT` meta-audit required by default? Suggest a PermissionSet toggle, default off.
- **OQ3:** `ClientIp` plaintext vs hash (GDPR)? Suggest plaintext storage, query-layer desensitization by permission.
- **OQ4:** Does the frontend `<Object>History` SOQL have routing yet? Required Phase 1b research.
- **OQ5:** Entrypoint semantics when parallel Flows race on the same field — accept "stack-top wins"; add EntrypointStack in the future? (CH-9)
- **OQ6:** **Read-auditing (SELECT events) not implemented.** SOX/HIPAA compliance customers will ask; needs a standalone RFC evaluating perf cost (each SObject read +1 audit write at least 2× write amp). (CH-11)
- **OQ7:** Should the selective per-field Field History for 10×-over-estimate large tenants follow SF's per-field enable pattern? (CH-10 fallback)

---

## 12. Code Structure

```
backend/internal/features/audit/
├── emitter.go           // AuditEmitter interface + default impl
├── context.go           // AuditContext + ctx propagation helpers
├── outbox_writer.go     // write the outbox single row (in-txn)
├── outbox_worker.go     // background expansion + lease + poison
├── field_diff.go        // field-level diff (extracted from persistence_service.go)
├── soql_translator.go   // <Object>History → _System_AuditLog
├── fls_planner.go       // query-plan layer FLS rewriting (CH-6)
├── pii_blacklist.go     // security-sensitive field blacklist
└── query_service.go     // admin API + EXPORT_AUDIT meta-audit

backend/internal/interfaces/rest/
├── middleware/audit_context.go   // REST Entrypoint injection
└── audit_handlers.go              // /audit/* endpoints

backend/internal/features/records/persistence_service.go  // change: call AuditEmitter
backend/internal/features/automation/flow_executor.go     // change: inject AuditContext
backend/internal/features/automation/trigger_engine.go    // change: same

backend/internal/bootstrap/system_tables.json             // add _System_AuditOutbox + _System_RequestCapture
```

---

## 13. Acceptance Criteria (Phase 2A Done Definition)

1. ✓ The frontend's existing `<Object>History` SOQL queries return correct data (E2E: UI Record Detail → History tab, agent-browser evidence).
2. ✓ REST → Flow → Trigger three-layer changes share the same RequestId, with Entrypoint = `REST_UI/FLOW/TRIGGER` respectively, and the ParentRequestId chain is intact.
3. ✓ Crash test: business txn commits ⇒ at least the header row exists; after restart worker resumes from outbox backlog.
4. ✓ 100-field UPDATE: in-txn INSERT ≤ 2 rows (1 header + 1 outbox), header commit P99 < 200ms; worker expands field-level diff with P99 < 5s.
5. ✓ FLS: a user without Read on `Salary__c` — `SELECT OldValue FROM LeadHistory` → response is redacted; `WHERE OldValue LIKE '250%' ON Salary__c` → 400 reject (CH-6).
6. ✓ Red-line grep tests pass (§8).
7. ✓ Unit + integration coverage ≥80%; `tests/e2e-api/cases/audit-trail/` has three cases: lead convert, flow trigger, bulk (mandatory per CLAUDE.md e2e-test-policy).
8. ✓ ctx-loss defense: unit test confirms `Emit()` returns `UNKNOWN` + WARN log when `Entrypoint` is omitted, no silent fallback.
9. ✓ **Replay capability** (§14): once a REST PATCH is recorded, it can be replayed on a new backend using `_System_RequestCapture` + `_System_AuditLog`, with the diff assertion passing at AuditLog field-level equivalence (external side effects go through a mock endpoint set at backend startup, out of scope for this capability).

---

## 14. Replay Capability (added in v2.4 — for Flow-refactor and other "behavior equivalence validation" scenarios)

### 14.1 Goals and non-goals

**Goals:**
- Record real user API traffic (REST layer) and replay against a new backend after a refactor / upgrade.
- Assertion basis: pre-vs-post `_System_AuditLog` field-level equivalence (§14.5).
- **Long-term capability:** every REST request leaves enough context for replay; this is not a one-off script.

**Non-goals:**
- Do not record downstream Flow / Trigger / Scheduler calls (they are cascaded from REST; replaying the top REST strings them together; separate flow-level capture deferred to Phase 2C).
- Do not mock external side effects — backend startup sets `<SERVICE>_API_URL=http://localhost:9998` to point HubSpot / SES / notifications at a mock server, which is an ops config concern, not an audit capability concern (currently these external integrations are not yet fully wired on main, so MVP needs no mock).
- No automatic cross-version schema migration (new and old backend schemas must be compatible, otherwise replay is meaningless).

### 14.2 Data model — `_System_RequestCapture` (independent relation table)

**Decision: do NOT** squeeze RequestBody into every `_System_AuditLog` row:
- One HTTP request → N AuditLog rows (CREATE + N field diffs). Body repeated N times = 200× storage amplification.
- Body may exceed 10KB; not suitable for cold FLS row-format processing.
- Separate table joined by `RequestId`; most AuditLog queries don't need the body.

```
_System_RequestCapture
  RequestId        VARCHAR(64)  NOT NULL   -- corresponds to _System_AuditLog.RequestId
  ReceivedAt       DATETIME(6)  NOT NULL
  Method           VARCHAR(10)  NOT NULL   -- GET/POST/PATCH/DELETE/...
  Path             VARCHAR(1024) NOT NULL  -- /services/data/v63.0/sobjects/Lead/001...
  Query            VARCHAR(2048) NULL
  Headers          JSON         NULL       -- after scrub (§14.3)
  Body             LONGBLOB     NULL       -- after scrub; BLOB (not TEXT) for binary-upload compatibility
  BodySize         INT          NOT NULL   -- raw size before scrub (for monitoring)
  BodyTruncated    BOOLEAN      NOT NULL DEFAULT FALSE  -- marker if > 1MB
  ResponseStatus   SMALLINT     NOT NULL
  ResponseBody     LONGBLOB     NULL       -- default NULL (§14.4 opt-in)
  UserId           VARCHAR(255) NULL
  SessionId        VARCHAR(64)  NULL
  ClientIp         VARCHAR(45)  NULL
  Entrypoint       VARCHAR(32)  NOT NULL   -- REST_API|REST_UI|BULK_API (Flow/Trigger/Scheduler not captured here)
  LatencyMs        INT          NOT NULL   -- backend processing time (replay may pace by this)

PRIMARY KEY (RequestId, ReceivedAt)         -- satisfies TiDB partition-key constraint
PARTITION BY RANGE (TO_DAYS(ReceivedAt)) INTERVAL (30 DAY)

INDEX idx_time  (ReceivedAt)
INDEX idx_user  (UserId, ReceivedAt DESC)
INDEX idx_path  (Path(64), ReceivedAt DESC)   -- prefix index, replay script filters by endpoint
```

### 14.3 PII scrub (hardcoded blacklist, L0 emitter-builtin)

**Header blacklist** (entire value removed):
```
Authorization, Cookie, Set-Cookie, X-Api-Key, X-Session-Token,
Proxy-Authorization, X-Csrf-Token, X-Salesforce-Session
```

**Body blacklist** (deep-walk, including nested JSON objects + arrays; matched values replaced with `"***SCRUBBED***"`):
```
password, currentPassword, newPassword, oldPassword, previousPassword, passwordConfirm,
token, accessToken, refreshToken, apiToken, sessionKey, securityToken, clientSecret,
mfaSecret, totpCode, creditCard, cvv, ssn, taxId, bankAccount
```

**Matching rule:** trailing key segment, case-insensitive full equality (no regex/suffix match — avoids false positives like `passwordHashPolicy`), **recursive array-element walk** (e.g. `{"items":[{"password":"x"}]}` — each element of items is walked).

**Content-type handling (P0 bypass mitigation — round-6 fix):**
- `application/json` / `application/*+json` / `text/json` → JSON parse + deep-walk scrub
- `application/x-www-form-urlencoded` / `multipart/form-data` → scrub by key name
- `application/xml` / `application/*+xml` / `text/xml` → scrub by element name
- **Any `Content-Type` first tries JSON / form-url parsing** (Content-Type is not a trusted source); if parse succeeds → run the corresponding scrubber. Closes the "attacker sets `Content-Type: application/octet-stream` and posts a JSON payload to bypass scrub" side channel.
- True binary body (all parses fail):
  - If the source endpoint is on an L1 **attachment-upload allowlist** (explicitly allowed upload paths such as `POST /sobjects/*/attachments`) → stored as-is, accepting the PII risk (attachment payloads intrinsically contain PII)
  - Otherwise → entire body replaced with `"***BINARY_UNPARSEABLE_SCRUBBED***"` + WARN log (default-deny; do not trust Content-Type)

**Compliance (red line #6):** this is a "security-sensitive-field allowlist, gated before FLS", same as §4.2, within CLAUDE.md's permitted range.

### 14.4 Response body default-off

- Most replay assertions rely on `_System_AuditLog`'s field-level diff, not HTTP response. Body is redundant and 2× bigger.
- Opt-in via L2 metadata `_System_RequestCaptureRules` (admin API in Phase 2C; MVP uses hardcoded seeds).
- Default stores only `ResponseStatus`, enough to assert 2xx/4xx/5xx distribution.

### 14.5 Assertion semantics — field-level AuditLog equivalence

Given the same `RequestId` run on old vs new backends, assert the resulting `_System_AuditLog` row sets (keyed by `ObjectApiName, RecordId, FieldName, Action`) are equivalent:

- **Strict mode:** exact match on every field; `OldValue`/`NewValue` byte-identical.
- **Tolerant mode** (default, for refactor validation):
  - Ignore system-timestamp diffs (`CreatedDate`/`SystemModstamp` second-level skew).
  - Ignore `Entrypoint` inner-detail changes (e.g. `FLOW` vs `TRIGGER` classification mismatch; case-by-case).
  - Do not ignore: `RecordId` / `FieldName` / `OldValue→NewValue` value / `Action` class.
- Diff output: `audit-diff-<replay_id>.json`, per-request equality/inequality markers + three-way comparison of unequal entries.

**Assertion scope caveat (round-6 P1 fix):**
AuditLog equivalence **only covers DB-side behavior**. It does **not** cover:
- External HTTP calls (HubSpot outbound, SES emails, third-party notifications) — not in `_System_AuditLog`
- Email content, notification payload, external webhook body
- Frontend cache / UI-only side effects

**For Flow refactor scenarios (the user's primary use case) in particular:**
Flows may fire external callouts. AuditLog equivalence **cannot** guarantee external behavior equivalence. Complete validation requires:
- Both backends started with external URLs pointing to the same mock-server
- The mock-server records inbound requests (VCR-style); the diff tool additionally compares "outbound HTTP from new vs old backend" for equivalence
- This mock-recording toolchain is ops work, not in the §14 / this design's scope

For this iteration (main has no full Flow/HubSpot integration yet) it can be ignored; **must be added before production** — otherwise behavior changes will be mis-classified as PASS.

### 14.6 Write path

REST middleware (new `backend/internal/interfaces/rest/middleware/request_capture.go`), after auth + audit_context:
1. `io.ReadAll(req.Body)` → reset `req.Body = io.NopCloser(bytes.NewReader(raw))` so the handler can read.
2. Scrub header + body → buffer in `ctx.RequestCaptureBuffer`.
3. After the handler runs (business txn committed, response written), the middleware tail:
4. **Post-commit async** INSERT of `_System_RequestCapture` (goroutine; logical join with `_System_AuditLog` via `RequestId`; **does NOT share the txn**).

**Txn semantics (round-6 correction of v2.4 error):**
- v2.4 said "same txn as AuditLog head row" — **physically impossible**. The business txn commits inside the handler; by the time middleware sees the response, the txn is closed.
- v2.5: `_System_RequestCapture` uses an **independent post-commit best-effort write** (goroutine with bounded retries). Logical join with AuditLog via `RequestId` column; no txn coupling needed.
- Impact: extreme failures (crash in post-commit window) leave "AuditLog head exists but no RequestCapture" orphan rows. Acceptable — capture is best-effort by design; the replay tool `export.py` encounters orphan rows → skip + report.

**Failure policy:**
- `_System_RequestCapture` write failure → WARN + Prometheus metric + **business request unaffected** (already returned).

### 14.7 Compliance and permission

- New permission `ViewRequestCapture` (PermissionSet key): even after scrub, URL + timing + user still leaks behavior patterns (side channel); read permission defaults to `ViewAllAuditTrail` holders only.
- Retention: default 18 months same as AuditLog; L2 tenant metadata can shorten it (`_System_RequestCaptureRetention`, Phase 2C).
- Capacity estimate: 180K requests/day × 2KB (scrub-median) × 540 days ≈ 190GB. After TiFlash tiering (Phase 2B) cold data moves to column store.
- **GDPR right to erasure (round-6 P2 added):** user-erasure runs via `cmd/setup erase-user --user-id=X`, executing `UPDATE _System_RequestCapture SET Body=NULL, Headers=NULL, Query=NULL WHERE UserId=X`; row headers (RequestId/timestamp/path/status) remain for audit traceability. The operation itself writes to `_System_SetupAuditTrail` (meta-audit). Corresponding disposition of `_System_AuditLog` itself is the domain of a broader "audit data erasure" RFC (involves legal-vs-compliance tradeoffs), out of this design's scope.

### 14.8 Replay toolchain (separate PR, `scripts/audit-replay/`)

Scripts don't ship in the backend binary; they're an ops tool:

```
scripts/audit-replay/
├── export.py    # pull a RequestId window from one backend DB → tape.jsonl
├── replay.py    # read tape → POST to target backend in order → record new RequestIds
├── diff.py      # connect both DBs → align AuditLog by old/new RequestId map → produce diff report
├── scrub_local.py  # local-side secondary scrub (used when exporting from staging to a dev box; additional redaction)
└── README.md
```

MVP flow (tomorrow's Flow refactor validation use case):
1. Old backend + staging DB: run a traffic sample (manual / e2e) producing RequestCapture + AuditLog.
2. `export.py` exports the last 2h RequestId window.
3. New backend (refactor version) boots on a separate TiDB branch (`tidb-branch copy`).
4. `replay.py` replays request-by-request; the new backend produces new AuditLog rows.
5. `diff.py` runs in Tolerant mode and outputs the behavior-equivalence report.

**Three required mechanisms for replay.py (round-6 P1 fix — multi-step tapes are broken without these):**

1. **RecordId rewrite map:** every POST that creates a record has a different old-vs-new RecordId (`00Q_old_abc` vs `00Q_new_xyz`). Replay maintains an `{old_id → new_id}` map and does deep-walk substitution over subsequent tape entries' `path` and body JSON. Without this, the second PATCH returns 404.
2. **Re-auth hook:** JWT/session in the tape has expired. `replay.py` starts by logging into the new backend using a configured test account (`--as admin@test.com`), obtains a fresh `Authorization`, and overrides each request's header with the new value.
3. **Idempotency/duplicate policy:** re-POSTing old tape POSTs would create duplicate records. Default **`--dedup-mode=skip-identical-create`**: for requests `POST /sobjects/<Obj>` whose body hash has been seen, skip + log; strict replay users can opt into `--dedup-mode=allow-duplicate`.

Auxiliary: the TiDB branch must be "clean shell + seed data synced by `export.py` before replay" (so PATCH targets exist); do not copy prod directly.

### 14.9 Relation to §3 / §4

- `RequestCapture.RequestId` = `AuditLog.RequestId`, one-to-many join.
- Entrypoint is restricted to `REST_API|REST_UI|BULK_API` — Flow/Trigger/Scheduler field changes trace upward through the corresponding REST request's `ParentRequestId` chain; not captured separately (§14.1 non-goal).
- FLS (§4.1): `RequestCapture.Body` queries go through the same redaction rules (can't bypass FLS by reading the body).

### 14.10 Implementation phase placement

- Phase 2A: `_System_RequestCapture` creation + middleware + hardcoded scrub blacklist + replay script MVP (export/replay/diff).
- Phase 2B: TiFlash tiering, ResponseBody opt-in rules table, customizable scrub rules (`_System_RequestCaptureScrubRule` L2 metadata — admins can add custom `__c` fields to the scrub list).
- Phase 2C: admin UI / per-tenant retention policy / flow-level capture.

### 14.11 Deployment gate — default opt-in (round-6 P2 fix)

Feature is **off by default**. Enabling requires explicit env:
```
NEXUSCRM_REQUEST_CAPTURE=on          # default off — middleware skips, zero RequestCapture writes
NEXUSCRM_REQUEST_CAPTURE_RESPONSE=off  # default off — even with capture on, response body is not recorded (§14.4)
```

Rationale:
- `_System_RequestCapture` doubles audit storage (~190GB / 18 months); medium-small tenants don't need it always on.
- Flow-refactor validation is an "enable-use-disable" pattern, not an always-on need.
- Security/compliance principle of "minimum collection by default" — avoids unintentional PII retention.
- Enabling itself writes to `_System_SetupAuditTrail` (deploy timestamp + who turned it on) for auditability.

---

## Appendix A: Contract Differences Quick Reference

| SF concept | This design | Difference |
|---|---|---|
| `<Object>History` child SObject | Single physical table + SOQL view | Read contract compatible, write implementation simplified; Phase 1b verifies SOQL completeness |
| `Field='created'` pseudo-field | `Action=CREATE, FieldName=__created__` | Translated at read side |
| `Field='Owner'` | `Action=OWNER_CHANGE, FieldName=Owner` | Translated at read side |
| `Field='locked'` | `Action=LOCK, FieldName=__locked__` | Translated at read side |
| `SetupAuditTrail` | `_System_SetupAuditTrail` | Naming only |
| `LoginHistory` | `_System_LoginHistory` | Superset (adds SessionId) |
| `EventLogFile` | Not implemented | Deferred |
| `FieldHistoryArchive` BigObject | Not implemented | Long-retention deferred |

---

## Appendix B: Challenger Findings Disposition (v1 → v2)

| # | Severity | Topic | v2 disposition |
|---|---|---|---|
| CH-1 | P0 | TenantId assumption wrong | ✅ removed entirely; rewrote §2.1 / §6 to per-DB tenancy |
| CH-2 | P0 | FieldName migration collision | ✅ kept NOT NULL VARCHAR(255), use reserved pseudo-values |
| CH-3 | P0 | Atomicity over-claim | ✅ §3.3 downgraded to "header strong + field-level eventual", added SLA + UI pending |
| CH-4 | P1 | ctx-propagation fragility | ✅ `Entrypoint` struct-required + CI lint |
| CH-5 | P1 | SOQL translator missing subquery/FK/aggregate | ⚠️ listed as Phase 1b required research, Phase 2A gate |
| CH-6 | P1 | FLS row-format side channel | ✅ sunk into query-plan layer, predicates on redacted value → 400 |
| CH-7 | P1 | Hash column is theater | ✅ Hash column removed |
| CH-8 | P1 | EXPORT_AUDIT recursion risk | ✅ §4.3 API-boundary single-emit isolation |
| CH-9 | P2 | Recursive Flow stack info loss | ⚠️ accepted, OQ5 records; future EntrypointStack |
| CH-10 | P2 | Capacity underestimate | ✅ §6 added 3× Flow amplification = ~400M; added fallback strategy |
| CH-11 | P2 | Read-audit blank | ✅ §0 explicitly lists as non-goal, OQ6 records |

---

## Appendix C: v2 → v2.1 Disposition (round-3 challenger)

| Severity | Topic | v2.1 disposition |
|---|---|---|
| P0 | Phase 2A atomicity internal contradiction (outbox deferred to 2B but SLA depends on outbox) | ✅ **merged outbox into Phase 2A**, eliminated sync-interim state |
| P1 | `FieldName='Owner'` double-form ambiguity | ✅ §3.6 added OwnerId normalization: emitter forces OWNER_CHANGE/Owner, does not emit UPDATE/OwnerId |
| P1 | Lease expiry → repeated consumption no dedup | ✅ `_System_AuditLog` added `OutboxRowId + UNIQUE KEY uk_outbox`; worker uses `INSERT ... ON DUPLICATE KEY UPDATE` |
| P1 | Poison undifferentiated, governance unclear | ✅ §3.3 added classification table: PARSE_FAIL / FK_VIOLATION / RETRY_EXHAUSTED / UNKNOWN, each with its own SLA |
| P1 | Phase 1b no fallback matrix | ✅ §9 Phase 1b added decision matrix, covering "subquery too complex → fall back to physical shards" / "rate overrun → per-field enable" |
| P2 | FLS query-plan one-size-fits-all | ✅ §4.1 refined: `WHERE` on redacted value → 400; `ORDER BY ChangedAt` etc. metadata columns → allow |
| P2 | TiDB compression 200GB not verified | ⚠️ §6 annotated "estimate pending Phase 1b measurement" |
| P2 | `_System_AuditOutbox` deploy path unclear | ⚠️ Phase 2A step 2 notes `system_tables.json` + control-plane apply; Phase 1b task 5 added: inventory `cmd/setup apply` handling of new tables |

---

## Appendix D: v2.1 → v2.2 Disposition (Phase 1b measurement + markdown review)

| Source | Severity | Topic | v2.2 disposition |
|---|---|---|---|
| Reviewer | BLOCK | §13 AC4 had stale "sync field write" text | ✅ changed to "txn internal ≤ 2 rows + worker expands P99 < 5s" |
| Reviewer | WARN | §11 R3 "listener Phase 2B" stale | ✅ changed to "Phase 2A core path; multi-instance extension Phase 2B" |
| Reviewer | WARN | Dedup upsert semantics wrong | ✅ switched to `INSERT IGNORE`; noted v2.1 ON DUP UPDATE semantics were wrong |
| Reviewer | WARN | §0 "SF contract" missing frontend reference | ✅ anchored to `frontend/src/**/useAuditLogs.ts` |
| Reviewer | NIT | Phase 1b task 5 missing | ✅ §9 Phase 1b added task 5 (cmd/setup apply inventory) |
| Reviewer | NIT | §12 outbox_writer comment / §13 AC3 phase label / bilingual consistency | Left to next editorial pass |
| DB lane | **P0** | TiDB partition requires partition key in PK | ✅ §2.1 added "PK adjustment": `(Id)` → `(Id, ChangedAt)`, Phase 2A mandatory |
| DB lane | **P1** | FlowInterview is 90% of staging writes | ✅ §2.1 added allowlist design (`_System_FieldHistoryTracking` L2 admin switch) |
| DB lane | INFO | Staging traffic only 1/70 of prod, 400M estimate cannot be falsified | ⚠️ kept §6 estimate; verify at prod rollout |
| DB lane | INFO | Staging currently has only 3 indexes, missing 4 | Rolled into §2.1 Phase 2A index list |

---

## Appendix E: v2.2 → v2.3 Disposition (round-5 challenger)

| Severity | Topic | v2.3 disposition |
|---|---|---|
| **P0** | `UNIQUE KEY uk_outbox(OutboxRowId, FieldName)` also violates TiDB partition-key rule (v2.2 only fixed PK; missed UK applying same rule) | ✅ §2.1 → `uk_outbox (OutboxRowId, FieldName, ChangedAt)`; dedup semantics unchanged |
| **P1** | `INSERT IGNORE` silently swallows FK_VIOLATION / data truncation; worker misreads as "successfully processed" | ✅ §2.1 upgraded to "INSERT IGNORE + SHOW WARNINGS + non-1062 error → poison" |
| **P1** | `_System_FieldHistoryTracking` default semantics (row absence = enabled?) + write API / invalidation unspecified | ✅ §2.1 made explicit: empty table = on, 60s cache, meta-audit via `_System_SetupAuditTrail`, write API → Phase 2C |
| P2 | Insert/Delete no-field-level-diff phrasing unclear | Left to next editorial pass (not a correctness issue) |
| P2 | PARSE_FAIL poison reachable only across version skew | Left to next editorial pass |
| P2 | §6 capacity >800M fallback trigger number missing | Left to next editorial pass (post-deploy revisit) |

---

## Appendix F: v2.4 → v2.5 Disposition (round-6 challenger on §14)

| Severity | Topic | v2.5 disposition |
|---|---|---|
| **P0** | §14.3 binary Content-Type scrub bypass: attacker sets `Content-Type: application/octet-stream` with JSON body to evade scrub | ✅ §14.3 rewritten: Content-Type is no longer trusted; always attempt JSON/form-url/xml parse first, treat as binary only on failure, and binary allowed only on attachment-upload allowlist paths |
| **P1** | §14.6 claimed RequestCapture same txn as AuditLog head — physically infeasible | ✅ §14.6 changed to post-commit best-effort; RequestId is a logical-only join, no txn share; orphan rows explicitly tolerated |
| **P1** | §14.3 scrub coverage gaps (oldPassword / refreshToken missing, array walk undefined, form-urlencoded / XML undefined) | ✅ §14.3 added 8 keys + explicit array walk + three content-type handlers |
| **P1** | §14.5 "AuditLog equivalence = behavior equivalence" over-promises for Flow refactor (external callouts not in AuditLog) | ✅ §14.5 added explicit caveat: AuditLog covers DB-side only; external side effects need separate mock-recording; required before production |
| **P1** | §14.8 replay.py silent on RecordId drift / expired JWT / POST duplicates | ✅ §14.8 added the trio: RecordId rewrite map / re-auth hook / dedup policy |
| **P2** | §14 default-on = double audit storage (~190GB/18mo) pressed onto all orgs | ✅ §14.11 explicit default opt-in: `NEXUSCRM_REQUEST_CAPTURE=off` default, explicit opt-in required |
| **P2** | GDPR erasure path unspecified | ✅ §14.7 added `cmd/setup erase-user`: Body/Headers/Query → NULL, row headers retained for audit |

---

**END v2.7 — ready for Phase 2A**