# Operational Data Audit Trail — Implementation Plan

**Spec:** docs/specs/2026-05-31-operational-audit-trail.md  ·  **Slug:** operational-audit-trail  ·  **Wave:** 12
**Depends on:** audit-compliance, foundation-auth-rbac, tenant-audit-log

## Goal
Add field-level before/after change tracking to all writes on core operational entities (invoices, invoice lines, customers, contacts, projects, contracts, expenses, time entries). Each tracked write records a JSONB diff (changed fields only) into the existing `tenant_audit_log` table inside the same DB transaction as the business operation. Tenants view this trail through a new "History" tab on entity detail views and a `GET /api/{entity}/{id}/history` endpoint, gated by role and tier.

## Architecture
This spec is additive to the upstream `tenant_audit_log` table owned by `tenant-audit-log` (the cycle-cut authority builds that table first). It does NOT create a new audit pipeline or a new table — it adds two JSONB columns (`before_state`, `after_state`) to `tenant_audit_log` and a same-transaction capture helper.

Data flow per tracked write:
1. Route handler opens a Drizzle transaction (`db.transaction` / `tenantQuery`).
2. Reads the `before` row, applies the update with `.returning()`, computes the changed-field diff.
3. Inserts a `tenant_audit_log` row with `event_type = '{entity}.{action}'`, `before_state`, `after_state` — in the SAME transaction (audit-compliance cross-cutting rule, enforced by ESLint `require-audit-in-transaction`).

The capture helper writes directly into `tenant_audit_log` within the caller's `tx` (spec 83 "Same-transaction write | Not post-commit event"). This is deliberately distinct from `tenant-audit-log`'s fire-and-forget `logAuditEvent` queue path; spec 83 requires the diff to be transactionally consistent with the mutation, so the diff capture is a synchronous in-transaction insert.

Upstream interfaces consumed:
- Table `tenant_audit_log` (columns: `id`, `tenant_id`, `user_id`, `actor_name`, `actor_email`, `event_type`, `entity_type`, `entity_id`, `entity_label`, `metadata`, `ip_address`, `created_at`) — from `tenant-audit-log`. NOTE: actor column is `user_id` (NOT `actor_id`); spec 83's pseudocode `actor_id` maps to `user_id`.
- `tenantQuery`, `systemQuery` — tenant-scoped Drizzle wrappers (foundation).
- `authMiddleware`, `requirePermission`, `requireTier`, `SessionPayload` (`UserId`, `TenantId`, role) — from `foundation-auth-rbac`.
- `buildPaginated`, `encodeCursor`, `decodeCursor`, `clampLimit`, `PaginatedResponse`, `PaginationParams` — pagination helpers (foundation).
- Entity tables: `customers`, `customer_contacts` (the `contacts` of the spec scope), plus `invoices`, `invoice_lines`, `projects`, `contracts`, `expenses`, `time_entries` (owned by their respective modules; this plan only reads/instruments their write routes, never alters their schema).
- UI primitives `Tabs`, `Table`, `DataTable`, `EmptyState`, `Avatar`, `Badge`, `Skeleton` — from `@zync/ui`.
- `useDirection`, `LocaleProvider`, locale date formatting — from `system-i18n` (transitively available).

## Tech Stack
- **packages/db** (Drizzle schema + migration): the `ALTER TABLE tenant_audit_log` delta and the regenerated Drizzle table object.
- **packages/audit** (new helper module `@zync/audit` — or extend the existing audit util package created by `tenant-audit-log`): `captureEntityChange`, `computeDiff`, `TRACKED_ENTITIES`, `EntityChangeEvent`.
- **apps/zync-api** (Hono on Cloudflare Workers): the `GET /api/{entity}/{id}/history` route + serializer; instrumentation of existing write handlers.
- **apps/zync-app** (Vite + React): `<EntityHistoryTab>` component mounted into invoice/customer/project/expense/contract detail views.
- Bindings: Neon Postgres via Hyperdrive (`DB`/`Db`), no new bindings, no new queue, no new cron.

## Wave Plan
| Sub-wave | Tasks | Files touched | Parallelizable? |
|----------|-------|---------------|-----------------|
| 12a | Task 1 | packages/db schema + migration | No (blocks all) |
| 12b | Task 2, Task 3 | packages/audit helper, types | Task 3 after Task 2 |
| 12c | Task 4 | apps/zync-api write-route instrumentation | After Task 2 |
| 12d | Task 5 | apps/zync-api history route + serializer | After Task 1, 2 |
| 12e | Task 6 | apps/zync-app History tab + hook | After Task 5 |
| 12f | Task 7 | tests | After Task 4, 5, 6 |

## Tasks

### Task 1: Schema delta — add before/after diff columns to `tenant_audit_log`
**Blocks:** 2, 4, 5  ·  **Blocked by:** —
**Files:**
- Modify: `packages/db/src/schema/tenant-audit-log.ts`
- Create: `packages/db/migrations/00XX_operational_audit_trail.sql`
**Steps:**
- [ ] Add `beforeState` and `afterState` `jsonb` columns to the existing `tenantAuditLog` Drizzle table object (do NOT redefine other columns; this table is owned upstream).
- [ ] Write the forward migration as an `ALTER TABLE ... ADD COLUMN` (both nullable — `before_state` NULL for create events, `after_state` NULL for delete events).
- [ ] Add a partial index to keep History-tab queries (filter by entity + ordered by time, only rows that carry a diff) fast.
- [ ] Regenerate Drizzle types; confirm no change to the immutability guarantee (no UPDATE/DELETE on this table from app code).
**Schema / Interfaces:**
```sql
-- Forward migration: 00XX_operational_audit_trail.sql
-- Additive delta on the upstream tenant_audit_log table (owned by tenant-audit-log).
ALTER TABLE tenant_audit_log
  ADD COLUMN before_state JSONB,  -- changed fields' prior values; NULL for create events
  ADD COLUMN after_state  JSONB;  -- changed fields' new values;   NULL for delete events

-- Speed the per-entity History query (entity scope, newest first, diff-bearing rows only).
CREATE INDEX idx_tal_entity_history
  ON tenant_audit_log (tenant_id, entity_type, entity_id, created_at DESC)
  WHERE before_state IS NOT NULL OR after_state IS NOT NULL;
```
```ts
// packages/db/src/schema/tenant-audit-log.ts — added to existing pgTable definition
beforeState: jsonb('before_state').$type<Record<string, unknown> | null>(),
afterState:  jsonb('after_state').$type<Record<string, unknown> | null>(),
```
**Acceptance:**
- [ ] Migration applies cleanly on a Neon branch; `before_state`/`after_state` are nullable JSONB.
- [ ] `idx_tal_entity_history` exists and is used by the history query plan (verify via EXPLAIN).
- [ ] Drizzle `tenantAuditLog` exposes `beforeState`/`afterState` typed as `Record<string, unknown> | null`.

### Task 2: Diff-capture helper `captureEntityChange` + `computeDiff`
**Blocks:** 4, 5  ·  **Blocked by:** 1
**Files:**
- Create: `packages/audit/src/operational/capture.ts`
- Create: `packages/audit/src/operational/entities.ts`
- Modify: `packages/audit/src/index.ts` (export the new symbols)
**Steps:**
- [ ] Define `TRACKED_ENTITIES` whitelist and `EntityChangeEvent` type (see interfaces below). Scope follows spec 83 verbatim — it INCLUDES `time_entries` (duration/reassign/billable), even though `tenant-audit-log` excludes time entries from its async log; this synchronous diff trail is spec-83-scoped and overrides.
- [ ] Implement `computeDiff(before, after, changedKeys)`: returns `{ beforeDiff, afterDiff }` containing ONLY keys whose value actually changed (diff, not full row snapshot — spec 83 architecture decision). Compare with strict inequality after normalizing dates to ISO strings; skip keys whose before/after are equal.
- [ ] Implement `captureEntityChange(tx, event)`: inserts ONE `tenant_audit_log` row using the caller's transaction handle `tx` — synchronous, same transaction (never enqueue). Maps `event.actorId` → column `user_id`, denormalizes `actorName`/`actorEmail`/`entityLabel`, stamps `ipAddress`. `event_type` is `'{entity}.{action}'`.
- [ ] For create events pass `beforeState: null`; for delete events pass `afterState: null`.
- [ ] Mask sensitive fields (`smtp_password_encrypted`, `api_key_secret`, `webhook_secret`, `oauth_token`, `dkim_private_key`) to `'[redacted]'` in both diff sides before insert (reuse the upstream blocklist constant if exported; otherwise define `SENSITIVE_FIELD_BLOCKLIST` here).
**Schema / Interfaces:**
```ts
// packages/audit/src/operational/entities.ts
export type TrackedEntity =
  | 'invoice' | 'invoice_line' | 'customer' | 'contact'
  | 'project' | 'contract' | 'expense' | 'time_entry';

export type TrackedAction =
  | 'created' | 'updated' | 'deleted' | 'status_changed'
  | 'field_updated';

// Whitelist used by the history route to validate the :entity path param.
export const TRACKED_ENTITIES = {
  invoices:     'invoice',
  invoice_lines:'invoice_line',
  customers:    'customer',
  customers_contacts: 'contact',
  projects:     'project',
  contracts:    'contract',
  expenses:     'expense',
  time_entries: 'time_entry',
} as const satisfies Record<string, TrackedEntity>;

export const HISTORY_API_ENTITIES = [
  'invoices', 'customers', 'projects', 'expenses', 'contracts',
] as const; // entities that expose GET /api/{entity}/{id}/history per spec 83 API section

export const SENSITIVE_FIELD_BLOCKLIST = [
  'smtp_password_encrypted', 'api_key_secret', 'webhook_secret',
  'oauth_token', 'dkim_private_key',
] as const;
```
```ts
// packages/audit/src/operational/capture.ts
export interface EntityChangeEvent {
  tenantId: string;
  actorId?: string;        // -> tenant_audit_log.user_id (NULL for system)
  actorName?: string;
  actorEmail?: string;
  entityType: TrackedEntity;
  entityId: string;
  entityLabel?: string;
  action: TrackedAction;
  beforeState: Record<string, unknown> | null;
  afterState:  Record<string, unknown> | null;
  ipAddress?: string;
}

export function computeDiff(
  before: Record<string, unknown>,
  after: Record<string, unknown>,
  changedKeys: string[],
): { beforeDiff: Record<string, unknown>; afterDiff: Record<string, unknown> };

// Inserts directly into tenant_audit_log inside the caller's transaction.
export async function captureEntityChange(
  tx: Parameters<Parameters<typeof db.transaction>[0]>[0],
  event: EntityChangeEvent,
): Promise<void>;
```
**Acceptance:**
- [ ] `computeDiff` returns only changed keys; equal-valued keys are omitted; dates normalized before comparison.
- [ ] `captureEntityChange` writes exactly one `tenant_audit_log` row with `event_type = '{entity}.{action}'`, populating `user_id` (not `actor_id`), `before_state`, `after_state`.
- [ ] Sensitive field values are `'[redacted]'` in both diff sides.
- [ ] Helper accepts a transaction handle and performs no queue/network call.

### Task 3: Shared serializer + response types for history entries
**Blocks:** 5, 6  ·  **Blocked by:** 2
**Files:**
- Create: `packages/audit/src/operational/serialize.ts`
- Modify: `packages/types/src/audit.ts` (export `EntityChangeRecord`)
**Steps:**
- [ ] Define `EntityChangeRecord` response shape (camelCase, matching spec 83 API `returns`).
- [ ] Implement `serializeEntityChange(row)` mapping a `tenant_audit_log` row → `EntityChangeRecord`: `eventType`, `actorId` (from `user_id`), `actorName`, `changedAt` (from `created_at`), `before` (from `before_state`), `after` (from `after_state`).
- [ ] Export both from `@zync/types` / `@zync/audit` so the History tab and route share one shape.
**Schema / Interfaces:**
```ts
export interface EntityChangeRecord {
  id: string;
  eventType: string;        // e.g. 'invoice.field_updated'
  actorId: string | null;   // sourced from tenant_audit_log.user_id
  actorName: string | null;
  changedAt: string;        // ISO 8601, from created_at
  before: Record<string, unknown> | null;
  after: Record<string, unknown> | null;
}
export function serializeEntityChange(row: TenantAuditLogRow): EntityChangeRecord;
```
**Acceptance:**
- [ ] `serializeEntityChange` maps `user_id`→`actorId`, `created_at`→`changedAt` (ISO), `before_state`→`before`, `after_state`→`after`.
- [ ] `EntityChangeRecord` is importable from `@zync/types`.

### Task 4: Instrument tracked-entity write handlers with diff capture
**Blocks:** 7  ·  **Blocked by:** 2
**Files:**
- Modify: `apps/zync-api/src/routes/invoices.ts` (status change, line-item edit, field update)
- Modify: `apps/zync-api/src/routes/invoice-lines.ts` (create, update, delete)
- Modify: `apps/zync-api/src/routes/customers.ts` (field update, archive/status change)
- Modify: `apps/zync-api/src/routes/contacts.ts` (create, update, delete)
- Modify: `apps/zync-api/src/routes/projects.ts` (field update, status change)
- Modify: `apps/zync-api/src/routes/contracts.ts` (status change)
- Modify: `apps/zync-api/src/routes/expenses.ts` (amount/category/date update, status change)
- Modify: `apps/zync-api/src/routes/time-entries.ts` (duration change, project/task reassign, billable toggle)
**Steps:**
- [ ] In each tracked write handler, wrap the mutation in a transaction (or extend the existing audit transaction already mandated by audit-compliance). Read the `before` row, perform the update with `.returning()` for `after`.
- [ ] Compute changed keys (`Object.keys(changes)` filtered to genuinely-changed values via `computeDiff`), then call `captureEntityChange(tx, …)` in the SAME transaction. Use the correct `event_type` per the spec patterns: `invoice.status_changed`, `invoice.field_updated`, `invoice_line.created|updated|deleted`, `customer.field_updated`, `customer.status_changed`, `contact.created|updated|deleted`, `project.field_updated`, `project.status_changed`, `contract.status_changed`, `expense.field_updated`, `expense.status_changed`, `time_entry.field_updated`.
- [ ] Populate `actorId` from `ctx.session.userId`, `actorName`/`actorEmail` from the session, `entityLabel` from the entity's display field (invoice number, customer name, project name, etc.), `ipAddress` from request context.
- [ ] For create handlers: `beforeState: null`. For delete handlers: `afterState: null`.
- [ ] Ensure ESLint `require-audit-in-transaction` passes on every modified handler (capture is inside the tx).
- [ ] Capture is ALL-TIERS, always on (no tier gate on capture — compliance requirement, spec 83 Tier Gate). Do not gate writes by tier.
**Acceptance:**
- [ ] Every tracked write produces a `tenant_audit_log` row with a non-empty diff (or null sides for create/delete) in the same transaction as the mutation; a rolled-back mutation rolls back its audit row.
- [ ] No-op updates (no fields changed) produce no spurious diff entries (empty diff → still recordable as `field_updated` only if at least one field changed; otherwise skip).
- [ ] `require-audit-in-transaction` ESLint rule passes for all eight route files.
- [ ] Capture runs on all tiers (Freelancer included).

### Task 5: `GET /api/{entity}/{id}/history` route
**Blocks:** 6, 7  ·  **Blocked by:** 1, 2, 3
**Files:**
- Create: `apps/zync-api/src/routes/entity-history.ts`
- Modify: `apps/zync-api/src/router.ts` (mount route)
**Steps:**
- [ ] Mount `GET /api/:entity/:id/history` behind `authMiddleware`. Validate `:entity` against `HISTORY_API_ENTITIES` (`invoices | customers | projects | expenses | contracts`); 404 on anything else.
- [ ] Resolve the entity to its `entity_type` via `TRACKED_ENTITIES`; verify the entity belongs to the caller's tenant via `tenantQuery` (tenant id from verified JWT, never from client input).
- [ ] Query `tenant_audit_log` filtered by `tenant_id = session.tenantId AND entity_type = ? AND entity_id = ?`, ordered `created_at DESC`, restricted to diff-bearing rows (`before_state IS NOT NULL OR after_state IS NOT NULL OR event_type LIKE '%.created'`).
- [ ] Validate query params with Zod (`require-zod-validation-in-routes`): `from?` (ISO/unix), `to?` (ISO/unix), `cursor?` (opaque base64), `limit` default 50, clamp via `clampLimit` (max 50 per spec 83; tenant-audit-log list caps at 200 but spec 83 specifies `limit=50`).
- [ ] **Role gate:** OWNER and ADMIN see all changes for the entity. MEMBER sees only their own changes — append `AND user_id = session.userId`. CONTRACTOR/CLIENT_PORTAL: 403.
- [ ] **Tier gate (display window):** Freelancer tier → restrict to last 90 days (`created_at >= now() - INTERVAL '90 days'`). Business+ → 1-year window for OWNER/ADMIN. Capture is unaffected; this only narrows the read. Use `requireTier`/tier from session to pick the window.
- [ ] Paginate with `encodeCursor`/`decodeCursor` (`{id, created_at}`), return `buildPaginated`-shaped `{ changes: EntityChangeRecord[], nextCursor, hasMore }` via `serializeEntityChange`. Resolve `actorName` from the denormalized `actor_name` column (no live user join).
**Schema / Interfaces:**
```ts
// GET /api/:entity/:id/history
// entity ∈ HISTORY_API_ENTITIES; query: from?, to?, cursor?, limit=50
// 200 -> { changes: EntityChangeRecord[]; nextCursor: string | null; hasMore: boolean }
// 403 if role lacks access; 404 if entity not in whitelist or not in tenant.
const historyQuerySchema = z.object({
  from: z.string().optional(),
  to: z.string().optional(),
  cursor: z.string().optional(),
  limit: z.coerce.number().int().min(1).max(50).default(50),
});
```
**Acceptance:**
- [ ] Endpoint returns `{ changes, nextCursor, hasMore }`; `changes` use `EntityChangeRecord` (eventType, actorId, actorName, changedAt, before, after).
- [ ] OWNER/ADMIN see all entity changes; MEMBER sees only `user_id = self`; CONTRACTOR/CLIENT_PORTAL get 403.
- [ ] Freelancer tier results are bounded to the last 90 days; Business+ to 1 year.
- [ ] Tenant scoping comes from JWT; a cross-tenant `id` yields 404, not data.
- [ ] Invalid `:entity` (e.g. `time_entries`, not in `HISTORY_API_ENTITIES`) → 404.
- [ ] Zod validation rejects malformed `limit`/`cursor`.

### Task 6: `<EntityHistoryTab>` UI on entity detail views
**Blocks:** 7  ·  **Blocked by:** 5
**Files:**
- Create: `apps/zync-app/src/components/audit/EntityHistoryTab.tsx`
- Create: `apps/zync-app/src/hooks/useEntityHistory.ts`
- Modify: `apps/zync-app/src/pages/invoices/InvoiceDetail.tsx` (add History tab)
- Modify: `apps/zync-app/src/pages/customers/CustomerDetail.tsx`
- Modify: `apps/zync-app/src/pages/projects/ProjectDetail.tsx`
- Modify: `apps/zync-app/src/pages/expenses/ExpenseDetail.tsx`
- Modify: `apps/zync-app/src/pages/contracts/ContractDetail.tsx`
**Steps:**
- [ ] Implement `useEntityHistory(entity, id, params)` — react-query hook calling `GET /api/{entity}/{id}/history`, cursor pagination with a "Load more" affordance.
- [ ] Implement `<EntityHistoryTab entity id>` rendering a "Field Changes" list: each row shows `changedAt` (locale-formatted `DD/MM/YYYY HH:mm`), `actorName`, and a per-field line `field: <before> → <after>`. Create events render `[created]` with no diff arrow; delete events render `[deleted]`.
- [ ] Mount the tab as a new `[History]` entry in each detail view's `Tabs`, alongside Details/Activity/Payments.
- [ ] Empty state via `<EmptyState>` ("No changes recorded yet"); loading via `<Skeleton>`.
- [ ] **a11y:** use the `@zync/ui` `Tabs` (correct `role="tab"`/`role="tabpanel"`/`aria-selected`); the change list is a semantic `Table`/list with header cells; each before/after pair is announced (no color-only signaling — pair the `→` arrow with text).
- [ ] **RTL/Hebrew:** layout via `useDirection`; the `before → after` arrow flips for RTL (use logical CSS / direction-aware arrow); dates use the tenant locale formatter.
- [ ] **prefers-reduced-motion:** any expand/collapse or row-enter animation is disabled under `@media (prefers-reduced-motion: reduce)`.
- [ ] MEMBER role: tab still renders but the API already returns only their own changes; if the response is empty for a MEMBER, show the empty state (do not hide the tab).
**Acceptance:**
- [ ] History tab appears on invoice, customer, project, expense, contract detail views.
- [ ] Each change shows actor, locale-formatted timestamp, and `field: before → after`; create shows `[created]`.
- [ ] Tab/tabpanel expose correct ARIA roles; before/after differences are not conveyed by color alone.
- [ ] RTL renders correctly (arrow direction flips); reduced-motion disables animations.
- [ ] "Load more" fetches the next cursor page.

### Task 7: Tests
**Blocks:** —  ·  **Blocked by:** 4, 5, 6
**Files:**
- Create: `packages/audit/test/compute-diff.test.ts`
- Create: `apps/zync-api/test/entity-history.test.ts`
- Create: `apps/zync-api/test/diff-capture.test.ts`
**Steps:**
- [ ] Unit-test `computeDiff`: changed-only keys, equal keys omitted, date normalization, sensitive-field redaction.
- [ ] Integration-test diff capture: a tracked update writes a `tenant_audit_log` row with the right `event_type`, `user_id`, `before_state`, `after_state`, in the same tx; a rolled-back tx leaves no audit row.
- [ ] Route tests: OWNER/ADMIN see all; MEMBER sees only own; CONTRACTOR → 403; Freelancer 90-day window vs Business+ 1-year; cross-tenant id → 404; non-whitelisted entity → 404; cursor pagination + `hasMore`.
**Acceptance:**
- [ ] All three test files pass.
- [ ] Role-gate, tier-window, tenant-isolation, and same-transaction guarantees are each covered by an assertion.