Skip to content

Transition Audit Store Decision

  • Status: Accepted
  • Date: 2026-07-15
  • Decision owner: WP01-08 / GitHub Issue #1185
  • Implementation owner: WP01-09
  • Supersedes: none
  • Current source evidence: app/platform/process/TransitionAuditStore.php, app/systems/TMS/transload/loader_transition_evidence.php, scripts/migrations/2026-07-15_task-1190_transition_audit_store_forward.sql
  • Verification limit: source presence was rechecked 2026-07-30; current DB schema/rows and Production activation were not checked

Context

WP01-07 introduced schema-independent ProcessContext and AuditEnvelope objects. CTC still has no durable, tenant-scoped business-transition ledger that can answer process, correlation, entity, evidence, recovery, and retention questions without reading raw request payloads.

The existing persistence surfaces have different ownership:

  • KJDatabase request JSONL is diagnostic evidence. It can contain SQL, before-row snapshots, request data, and exception details and is rotated as a log rather than managed as an append-only business record.
  • T_OPERATION_EVENT is a notification ledger. Its payload is display-oriented JSON and ACKED_AT / ACKED_BY mean that a notification was read. They do not mean business approval or completion.
  • staff events, mail items, files, external-attempt logs, and business rows are authoritative in their own domain stores and have independent lifecycles.

Read-only local inspection used MySQL 8.4.8 and CLIENT_ID = 1. At inspection time T_OPERATION_EVENT had 43 rows and T_STAFF_TIME_EVENT had approximately 3.7K rows. These counts are sizing evidence only, not capacity forecasts.

Decision Drivers

  1. Every lookup and join must be tenant-scoped and sargable.
  2. The store must preserve the bounded, reference-only WP01-07 contract.
  3. Notification acknowledgement must never become business approval.
  4. Process timelines and entity/evidence lookup must not require JSON scans.
  5. Duplicate append attempts must be deterministic and conflict-detectable.
  6. Retention and rollback must not require deleting source evidence or changing an active business writer.
  7. Migration, writer activation, cleanup activation, and destructive removal must remain separately approved steps.

Considered Options

Option Benefits Blocking problems Decision
Extend KJ mutation JSONL Already records request and DML evidence Raw/high-volume content, file rotation, no durable relational indexes, diagnostic failure semantics, unsuitable retention boundary Rejected
Extend T_OPERATION_EVENT Existing tenant ledger and UUID Notification/display ownership, JSON payload, ACK semantic collision, unread/recent indexes do not serve process/entity audit Rejected
One generic transition JSON table Small initial schema Recreates an unbounded payload bag, weak field constraints, JSON-path indexes, opaque retention/redaction, expensive entity/evidence lookup Rejected
Dedicated bounded transition table plus typed entity/evidence child tables Clear ownership, scalar indexes, reference-only payload, independent retention, additive rollout Three-table transaction and cleanup ordering Accepted

Decision

Create a new additive persistence family in WP01-09:

  1. T_PROCESS_TRANSITION_AUDIT owns one immutable business-transition envelope.
  2. T_PROCESS_TRANSITION_ENTITY owns the envelope's 1-25 typed entity references.
  3. T_PROCESS_TRANSITION_EVIDENCE owns the envelope's 1-50 typed evidence references.

The main row contains bounded scalar columns. The child rows contain only type, relation, and namespaced reference values. No table has a generic JSON, text, blob, raw payload, row snapshot, SQL, document body, provider response, or exception-message column.

CLIENT_ID is the first filter for every runtime query and the first column of every runtime lookup index. Audit and process UUIDs use BINARY(16). The canonical safe envelope has a SHA-256 hash used only for duplicate-conflict detection; no raw or low-entropy secret/PII value is hashed into this store.

The detailed physical contract, index/query pairing, retention policy, and migration/rollback sequence are defined in

Compatibility Boundary

  • KJDatabase JSONL remains unchanged and may be referenced by request ID.
  • T_OPERATION_EVENT remains unchanged and may be referenced only as OPERATION_EVENT / NOTIFICATION_ONLY evidence. ACK is never approval.
  • Source staff, mail, file, external-attempt, and business records remain in their current stores. Audit deletion never cascades to a source record, and source deletion never deletes an audit row.
  • No active ProcessContext or AuditEnvelope caller is added by this decision.
  • DB_SCHEMA.md remains current-state documentation until WP01-09 actually applies and verifies an approved migration.

Retention Decision

The initial engineering baseline is:

  • STANDARD: 400 days for R0-R3 transitions.
  • EXTENDED: 2,555 days for R4, R5, physical, external-committed, or compensation transitions.
  • an explicit legal-hold reference prevents cleanup regardless of purge date.

These are engineering defaults, not a statutory or customer contractual claim. A stricter approved tenant, customer, legal, or regulatory policy overrides the default. Cleanup is disabled at initial rollout and requires the separate gate in the physical contract.

Consequences

Positive

  • Process, correlation, parent, contract, entity, and evidence timelines use ordinary composite indexes.
  • Audit retention is independent from notification visibility and log rotation.
  • A raw payload cannot be added without an explicit future schema decision.
  • Writer disable is enough to stop rollout without altering business tables.

Costs And Risks

  • One append spans three tables and therefore requires a single transaction.
  • Child-row integrity and bounded cardinality must be enforced by both foreign keys and writer validation.
  • Source references can become tombstones when source retention is shorter; resolvability is not guaranteed and must not change the historical audit row.
  • The additional indexes trade write amplification for deterministic read and cleanup paths. WP01-09 must measure fixture inserts and EXPLAIN each query.

Rollback

Before WP01-09 implementation, revert this ADR and its contract. After tables exist, disable the writer first and retain all rows. Dropping tables or deleting retained audit data is a separate destructive action requiring explicit DB approval, a retention/hold check, an export or documented discard decision, and post-action verification.

---

Source: docs/decisions/0002-transition-audit-store.md — ctc repository · This page is an English translation of the source document. Identifiers, file paths and code symbols are unchanged.