# Tenant Audit Log — Implementation Plan **Spec:** docs/specs/2026-05-31-tenant-audit-log.md · **Slug:** tenant-audit-log · **Wave:** 3 **Depends on:** foundation-auth-rbac, foundation-monorepo, system-communications-notifications ## Goal Deliver a tenant-scoped, immutable, chronological audit log of all write-level actions within a workspace. Mutation handlers fire-and-forget an event via the `audit-log-queue` Cloudflare Queue; a dedicated consumer Worker inserts the row into Neon Postgres so audit writes never add latency to API responses. OWNER/ADMIN view, filter, and CSV-export the log from `/settings/audit-log` and `/reports/audit`; MEMBER sees own actions only on the reports view. A nightly Cron purges rows past each tenant's tier-based retention window. ## Architecture - **New table `tenant_audit_log`** lives in `packages/db` (Drizzle). FK `tenant_id → tenants(id)` (ON DELETE CASCADE) and `user_id → users(id)` (ON DELETE SET NULL), both UUID→UUID against the upstream `tenants` and `users` tables. Denormalized `actor_name`, `actor_email`, `entity_label` keep history accurate after renames/deletes. `metadata` is `JSONB`. - **Producer** `logAuditEvent(ctx, event)` in `packages/db` (queries layer) enqueues an `AuditEvent` message onto the new `AUDIT_QUEUE` binding (`audit-log-queue`). It does NOT write to Postgres inline. It applies the sensitive-field masking blocklist before enqueue. Mutation routes import and call it after their business op; they do **not** wrap audit in the business transaction (this is the async log, distinct from the in-transaction `audit_log` owned by `operational-audit-trail`). - **Consumer Worker** `audit-log-consumer` drains `audit-log-queue`, builds the Drizzle row, and inserts into `tenant_audit_log` via `createDb(env)`. Failed batches throw to trigger Queue retry (≤3, exponential backoff). - **Read API** `GET /api/audit-log` and `GET /api/audit-log/export` in `apps/zync-api`, tenant-scoped via `tenantQuery`, cursor pagination via upstream `encodeCursor`/`decodeCursor`/`buildPaginated`, role-branched access. - **UI** shared `AuditLogTable` component in `apps/zync-app` mounted at `/settings/audit-log` (7-day default range) and `/reports/audit` (30-day default + extra text/full-text search, MEMBER-own-data branch). - **Retention** Cron `audit-log-retention` fires nightly 03:00 UTC, hits a `CRON_SECRET`-guarded `/api/cron/audit-log-retention` endpoint that purges per-tier. Upstream consumed by exact name: `tenants` (`tenants.tier` enum), `users`, `createDb`, `tenantQuery`, `encodeCursor`, `decodeCursor`, `buildPaginated`, `PaginatedResponse`, `Env`, `Session`/`SessionPayload`, `authMiddleware`, `RoleId`, `UserId`, `TenantId`, `QUEUE`/queue infra, `serialize* ` conventions, Hono `AppContext`. ## Tech Stack - **packages/db** — Drizzle schema file `audit.ts`, queries file `audit.ts`, `AUDIT_EVENT_TYPES` + group map + masking blocklist constants. - **packages/types** — `AuditEvent`, `AuditLogItem`, `AuditEventGroup` DTOs. - **apps/zync-api** (Hono / Cloudflare Worker) — read + export + cron routes; queue consumer handler; `AUDIT_QUEUE` producer binding on `Env`. - **apps/zync-app** (Vite + React, react-router v7, TanStack Query v5) — `AuditLogTable`, both route mounts, TanStack hook `useAuditLog`. - **Cloudflare bindings:** `AUDIT_QUEUE` (producer, queue `audit-log-queue`); consumer config in `wrangler.toml`; `DB` (Hyperdrive→Neon); `CRON_SECRET`. ## Wave Plan | Sub-wave | Tasks | Files touched | Parallelizable? | |----------|-------|---------------|-----------------| | A | 1, 2 | packages/db schema + migration, packages/types | Task 2 after Task 1 | | B | 3, 4 | wrangler.toml, packages/db queries (`logAuditEvent`), zync-api consumer | 3 and 4 parallel after A | | C | 5, 6 | zync-api read + export routes | parallel after B | | D | 7 | zync-api cron retention | after A | | E | 8, 9 | zync-app component + route mounts | after C | ## Tasks ### Task 1: `tenant_audit_log` schema + migration **Blocks:** 2, 3, 4, 5, 6, 7 · **Blocked by:** — **Files:** - Create: `packages/db/src/schema/audit.ts` - Create: `packages/db/migrations/_create_tenant_audit_log.sql` - Modify: `packages/db/src/schema/index.ts` (export `tenantAuditLog`) - Modify: `packages/db/src/index.ts` (re-export `tenantAuditLog`) **Steps:** - [ ] Define the Drizzle `pgTable` `tenant_audit_log` exactly matching the DDL below. - [ ] Add the four B-tree indexes; do NOT add a GIN index on `metadata` (filters never touch metadata content — SELECT-only JSONB per foundation rule). - [ ] Do NOT add a `CHECK` constraint on `event_type` (deliberately open per spec — future event types must insert). - [ ] Do NOT add `before_state`/`after_state` columns — those belong to `operational-audit-trail` (spec 50). - [ ] Generate the migration with drizzle-kit; verify FK targets are `tenants(id)` and `users(id)` (UUID→UUID). **Schema / Interfaces:** ```sql CREATE TABLE tenant_audit_log ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE, user_id UUID REFERENCES users(id) ON DELETE SET NULL, -- NULL for system/webhook events actor_name TEXT, -- denormalized display name at time of event actor_email TEXT, -- denormalized event_type TEXT NOT NULL, -- e.g. 'customer.created', 'invoice.status_changed' entity_type TEXT, -- 'customer', 'invoice', 'user', 'settings', etc. entity_id UUID, -- NULL for non-row events (e.g. 'settings') entity_label TEXT, -- denormalized display name at time of event (e.g. customer company name) metadata JSONB, -- { old_value, new_value, ip_address, user_agent, ... } ip_address TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX idx_tal_tenant_time ON tenant_audit_log(tenant_id, created_at DESC); CREATE INDEX idx_tal_entity ON tenant_audit_log(tenant_id, entity_type, entity_id); CREATE INDEX idx_tal_user ON tenant_audit_log(tenant_id, user_id); CREATE INDEX idx_tal_event_type ON tenant_audit_log(tenant_id, event_type); ``` **Acceptance:** - [ ] Migration applies cleanly against a Neon branch; table + 4 indexes exist. - [ ] `tenantAuditLog` is exported from `@zync/db`. - [ ] No GIN index, no `event_type` CHECK, no `before_state`/`after_state` columns present. ### Task 2: Audit types, event-type catalog, group map, masking blocklist **Blocks:** 3, 5, 6, 8 · **Blocked by:** 1 **Files:** - Create: `packages/types/src/audit.ts` - Modify: `packages/types/src/index.ts` (export new symbols) - Create: `packages/db/src/constants/audit.ts` **Steps:** - [ ] Define `AuditEvent` interface (producer input) and `AuditLogItem` DTO (API response item) per signatures below. - [ ] Transcribe all event types into `AUDIT_EVENT_TYPES` (union/const) — NO CHECK constraint, this is the app-side catalog only. - [ ] Define `AUDIT_EVENT_GROUPS` mapping each event type to one of `User | Data | Settings | Auth | Integration | Bulk | API Keys` for the filter dropdown grouping. - [ ] Define `SENSITIVE_FIELD_BLOCKLIST` verbatim for masking. **Schema / Interfaces:** ```typescript // packages/types/src/audit.ts export type AuditEventGroup = | 'User' | 'Data' | 'Settings' | 'Auth' | 'Integration' | 'Bulk' | 'API Keys'; export interface AuditEvent { tenantId: string; userId?: string; actorName?: string; actorEmail?: string; eventType: string; entityType?: string; entityId?: string; entityLabel?: string; metadata?: Record; ipAddress?: string; } export interface AuditLogItem { id: string; event_type: string; entity_type: string | null; entity_id: string | null; entity_label: string | null; actor_name: string | null; actor_email: string | null; metadata: Record | null; ip_address: string | null; created_at: number; // Unix seconds (column is TIMESTAMPTZ; serialized to epoch in response) } export const AUDIT_EVENT_TYPES = [ // User Management 'user.invited', 'user.role_changed', 'user.removed', 'user.2fa_enforced', 'user.frozen', 'user.unfrozen', // Data Changes 'customer.created', 'customer.updated', 'customer.deleted', 'invoice.created', 'invoice.updated', 'invoice.status_changed', 'invoice.sent', 'invoice.paid', 'invoice.voided', 'project.created', 'project.updated', 'project.deleted', 'expense.created', 'expense.updated', 'expense.deleted', 'contract.signed', 'contract.voided', // Settings 'settings.updated', // Auth 'auth.login_success', 'auth.login_failed', 'auth.logout', 'auth.password_reset_requested', 'auth.password_reset_completed', 'auth.2fa_enabled', 'auth.2fa_disabled', 'auth.2fa_backup_code_used', // Integration 'webhook.delivered', 'webhook.failed', 'payment_gateway.configured', 'payment_gateway.removed', 'smtp.configured', 'smtp.verified', // Bulk 'import.completed', 'bulk.status_changed', // API Keys 'api_key.created', 'api_key.revoked', 'api_key.used_from_new_ip', ] as const; export type AuditEventType = typeof AUDIT_EVENT_TYPES[number]; export const AUDIT_EVENT_GROUPS: Record = { 'user.invited': 'User', 'user.role_changed': 'User', 'user.removed': 'User', 'user.2fa_enforced': 'User', 'user.frozen': 'User', 'user.unfrozen': 'User', 'customer.created': 'Data', 'customer.updated': 'Data', 'customer.deleted': 'Data', 'invoice.created': 'Data', 'invoice.updated': 'Data', 'invoice.status_changed': 'Data', 'invoice.sent': 'Data', 'invoice.paid': 'Data', 'invoice.voided': 'Data', 'project.created': 'Data', 'project.updated': 'Data', 'project.deleted': 'Data', 'expense.created': 'Data', 'expense.updated': 'Data', 'expense.deleted': 'Data', 'contract.signed': 'Data', 'contract.voided': 'Data', 'settings.updated': 'Settings', 'auth.login_success': 'Auth', 'auth.login_failed': 'Auth', 'auth.logout': 'Auth', 'auth.password_reset_requested': 'Auth', 'auth.password_reset_completed': 'Auth', 'auth.2fa_enabled': 'Auth', 'auth.2fa_disabled': 'Auth', 'auth.2fa_backup_code_used': 'Auth', 'webhook.delivered': 'Integration', 'webhook.failed': 'Integration', 'payment_gateway.configured': 'Integration', 'payment_gateway.removed': 'Integration', 'smtp.configured': 'Integration', 'smtp.verified': 'Integration', 'import.completed': 'Bulk', 'bulk.status_changed': 'Bulk', 'api_key.created': 'API Keys', 'api_key.revoked': 'API Keys', 'api_key.used_from_new_ip': 'API Keys', }; // packages/db/src/constants/audit.ts export const SENSITIVE_FIELD_BLOCKLIST = [ 'smtp_password_encrypted', 'api_key_secret', 'webhook_secret', 'oauth_token', 'dkim_private_key', ] as const; export const REDACTED = '[redacted]'; ``` **Acceptance:** - [ ] `AuditEvent`, `AuditLogItem`, `AuditEventType`, `AUDIT_EVENT_TYPES`, `AUDIT_EVENT_GROUPS`, `AuditEventGroup` exported from `@zync/types`. - [ ] `SENSITIVE_FIELD_BLOCKLIST` exported from `@zync/db`. ### Task 3: `logAuditEvent` producer + queue binding **Blocks:** 4 (shares queue), 5, 6 (routes call it) · **Blocked by:** 1, 2 **Files:** - Create: `packages/db/src/queries/audit.ts` - Modify: `packages/db/src/index.ts` (re-export `logAuditEvent`) - Modify: `apps/zync-api/wrangler.toml` (add `AUDIT_QUEUE` producer binding) - Modify: `apps/zync-api/src/types/env.ts` (add `AUDIT_QUEUE: Queue` to `Env`) **Steps:** - [ ] Implement `logAuditEvent(ctx, event)` that masks then enqueues — never inserts inline. - [ ] Masking: when `event.eventType === 'settings.updated'`, walk `metadata`; if a `field` value (or any key) is in `SENSITIVE_FIELD_BLOCKLIST`, replace its `old_value`/`new_value` with `REDACTED` before enqueue. - [ ] Read producer binding off the Hono context env (`ctx.env.AUDIT_QUEUE`); use existing `AppContext`/`Env` — do not invent a new context type. - [ ] Add `AUDIT_QUEUE` producer binding to wrangler.toml targeting queue `audit-log-queue`. Do NOT reuse the generic `QUEUE` or `webhook.deliver` queue. - [ ] Swallow/observe enqueue failures (fire-and-forget): a failed enqueue must not throw into the mutation handler; log via `console.error`. **Schema / Interfaces:** ```typescript // packages/db/src/queries/audit.ts import type { Context } from 'hono'; import type { AuditEvent } from '@zync/types'; import { SENSITIVE_FIELD_BLOCKLIST, REDACTED } from '../constants/audit'; type AppContext = Context<{ Bindings: Env }>; export async function logAuditEvent(ctx: AppContext, event: AuditEvent): Promise { const masked = maskSensitive(event); try { await ctx.env.AUDIT_QUEUE.send(masked); } catch (err) { console.error('audit enqueue failed', { eventType: event.eventType, err }); } } function maskSensitive(event: AuditEvent): AuditEvent { /* redact blocklisted fields in metadata when settings.updated */ } ``` ```toml # apps/zync-api/wrangler.toml — producer binding [[queues.producers]] queue = "audit-log-queue" binding = "AUDIT_QUEUE" ``` **Acceptance:** - [ ] `logAuditEvent` exported from `@zync/db`; calling it enqueues one message and never throws. - [ ] A `settings.updated` event carrying `smtp_password_encrypted` enqueues `[redacted]`, never the raw value. - [ ] `Env` type includes `AUDIT_QUEUE: Queue`. ### Task 4: `audit-log-consumer` Queue Worker **Blocks:** — · **Blocked by:** 1, 3 **Files:** - Create: `apps/zync-api/src/queue/audit-log-consumer.ts` - Modify: `apps/zync-api/src/index.ts` (export `queue` handler dispatching `audit-log-queue` batches) - Modify: `apps/zync-api/wrangler.toml` (add consumer config: `max_retries = 3`) **Steps:** - [ ] Implement consumer that for each message builds a `tenantAuditLog` insert row (`metadata` stored as JSONB object directly — no JSON.stringify) and inserts via `createDb(env)`. - [ ] On insert error, throw so the Queue retries the batch (≤3, exponential backoff); after final failure the message is dropped (acceptable per spec trade-off). - [ ] Route the batch in the Worker `queue(batch, env)` handler by `batch.queue === 'audit-log-queue'`. - [ ] Insert-only — the consumer never UPDATEs or DELETEs. **Schema / Interfaces:** ```typescript // apps/zync-api/src/queue/audit-log-consumer.ts import { createDb, tenantAuditLog } from '@zync/db'; import type { AuditEvent } from '@zync/types'; export async function handleAuditLogBatch(batch: MessageBatch, env: Env): Promise { const db = createDb(env); for (const msg of batch.messages) { const e = msg.body; await db.insert(tenantAuditLog).values({ tenantId: e.tenantId, userId: e.userId ?? null, actorName: e.actorName ?? null, actorEmail: e.actorEmail ?? null, eventType: e.eventType, entityType: e.entityType ?? null, entityId: e.entityId ?? null, entityLabel: e.entityLabel ?? null, metadata: e.metadata ?? null, ipAddress: e.ipAddress ?? null, }); msg.ack(); } } ``` ```toml # apps/zync-api/wrangler.toml — consumer [[queues.consumers]] queue = "audit-log-queue" max_retries = 3 max_batch_size = 100 ``` **Acceptance:** - [ ] Enqueued event lands as one `tenant_audit_log` row with `metadata` queryable as JSONB. - [ ] A forced insert error retries the message (verified via dead-letter/retry behavior) up to 3 times. ### Task 5: `GET /api/audit-log` (filtered, cursor-paginated) **Blocks:** 8 · **Blocked by:** 1, 2, 3 **Files:** - Create: `apps/zync-api/src/routes/audit-log.ts` - Create: `packages/db/src/queries/audit-list.ts` (the `tenantQuery` list helper) - Modify: `apps/zync-api/src/index.ts` (mount route under `/api/audit-log`) **Steps:** - [ ] Add `authMiddleware`; gate access by role: `/api/audit-log` (settings view) is OWNER/ADMIN only → others 403. The `?scope=reports` caller (reports view) allows MEMBER but the query must add `WHERE user_id = session.sub`; CONTRACTOR/CLIENT_PORTAL always 403. - [ ] Validate query params through a Zod schema (`AuditLogQuerySchema`): `from`, `to` (ISO date or Unix ts), `user_id`, `event_type`, `entity_type`, `cursor`, `limit` (default 50, max 200), plus reports-only `q` (free text) and `entity_q`. - [ ] Build query via `tenantQuery(db, tenantId)`; apply filters; default order `created_at DESC, id DESC`. - [ ] Cursor: decode with `decodeCursor` → `{id, created_at}`; apply keyset predicate `(created_at, id) < (cursor.created_at, cursor.id)`. Emit `next_cursor` with `encodeCursor`. Use `buildPaginated`/`PaginatedResponse` shape; serialize `created_at` to Unix seconds. - [ ] Reports full-text `q`: ILIKE across `actor_name`, `actor_email`, `entity_label`, `event_type` (applied after tenant_id + date narrow the set — acceptable at audit scale, no FTS index). `entity_q`: ILIKE on `entity_label`. **Schema / Interfaces:** ```typescript const AuditLogQuerySchema = z.object({ from: z.string().optional(), to: z.string().optional(), user_id: z.string().uuid().optional(), event_type: z.string().optional(), entity_type: z.string().optional(), cursor: z.string().optional(), limit: z.coerce.number().int().min(1).max(200).default(50), q: z.string().optional(), entity_q: z.string().optional(), }); // Response: { items: AuditLogItem[], next_cursor: string | null, has_more: boolean } ``` **Acceptance:** - [ ] OWNER/ADMIN get full tenant log; MEMBER on reports scope gets only own rows; CONTRACTOR/CLIENT_PORTAL get 403. - [ ] Cursor pagination returns disjoint pages of ≤50 by default (max 200), newest-first; `next_cursor` round-trips. - [ ] Cross-tenant rows never returned (enforced by `tenantQuery`). ### Task 6: `GET /api/audit-log/export` (streamed CSV) **Blocks:** 8 · **Blocked by:** 1, 2, 5 **Files:** - Modify: `apps/zync-api/src/routes/audit-log.ts` (add `/export` handler) **Steps:** - [ ] Same `authMiddleware` + role gate + Zod-validated filters as Task 5 (reuse `AuditLogQuerySchema` minus `cursor`/`limit`). - [ ] Stream CSV via a `ReadableStream`; header row: `Date,Actor,Email,Event,Entity Type,Entity,Details,IP Address`. Format `created_at` per tenant locale `DD/MM/YYYY HH:mm`. - [ ] Set `Content-Type: text/csv; charset=utf-8` and `Content-Disposition: attachment; filename="audit-log--.csv"`. - [ ] Cap at 10,000 rows; if the filtered set would exceed, stop at 10k and (for larger needs) direct caller to the `data-export-gdpr` async export flow. - [ ] CSV-escape fields (quote + double-quote embedded quotes) to prevent injection/format breakage. **Acceptance:** - [ ] Response streams as an attachment CSV with correct headers. - [ ] Export never exceeds 10,000 rows. - [ ] Same role gating as the list endpoint. ### Task 7: Nightly retention Cron `audit-log-retention` **Blocks:** — · **Blocked by:** 1 **Files:** - Create: `apps/zync-api/src/routes/cron/audit-log-retention.ts` - Modify: `apps/zync-api/src/index.ts` (mount `/api/cron/audit-log-retention`, `CRON_SECRET`-guarded) - Modify: `apps/zync-api/wrangler.toml` (add Cron Trigger `0 3 * * *`) **Steps:** - [ ] Guard endpoint with `CRON_SECRET` (timing-safe compare via `timingSafeEqual` from `@zync/auth` — never `===`). - [ ] Run the two tier-scoped deletes below. Use `tenants.tier` (canonical column; there is no `plan` column) with values `business` and `enterprise`. - [ ] Register Cron Trigger at 03:00 UTC; name it `audit-log-retention` so it does not collide with foundation's `data-retention-purge` or `audit-partition-create`. **Schema / Interfaces:** ```sql DELETE FROM tenant_audit_log WHERE tenant_id IN (SELECT id FROM tenants WHERE tier = 'business') AND created_at < now() - INTERVAL '90 days'; DELETE FROM tenant_audit_log WHERE tenant_id IN (SELECT id FROM tenants WHERE tier = 'enterprise') AND created_at < now() - INTERVAL '365 days'; ``` ```toml # apps/zync-api/wrangler.toml [triggers] crons = ["0 3 * * *"] # audit-log-retention (merge with existing crons array) ``` **Acceptance:** - [ ] Business-tier rows older than 90 days and enterprise-tier rows older than 365 days are purged nightly. - [ ] Endpoint rejects requests without the correct `CRON_SECRET` (timing-safe). - [ ] Retention is the ONLY DELETE path against `tenant_audit_log`; no app route or consumer ever issues UPDATE/DELETE. ### Task 8: `AuditLogTable` shared component + `useAuditLog` hook **Blocks:** 9 · **Blocked by:** 2, 5, 6 **Files:** - Create: `apps/zync-app/src/modules/audit/AuditLogTable.tsx` - Create: `apps/zync-app/src/modules/audit/useAuditLog.ts` - Create: `apps/zync-app/src/modules/audit/AuditRowDetail.tsx` **Steps:** - [ ] `useAuditLog(params)` — TanStack Query v5 infinite query hitting `GET /api/audit-log`, consuming `next_cursor`/`has_more`; exposes `loadMore`. - [ ] `AuditLogTable` props: `{ scope: 'settings' | 'reports' }` — drives default date range (settings = last 7 days, reports = last 30 days) and whether the extra `q`/`entity_q` search inputs render. - [ ] Filter bar (collapsible on mobile): date range (from/to), Actor dropdown (team members + "All actors"), Event type dropdown grouped via `AUDIT_EVENT_GROUPS` (User/Data/Settings/Auth/Integration/Bulk/API Keys), Entity type dropdown. Reports scope adds Entity text search + free-text search. - [ ] Columns: Date/Time (tenant locale `DD/MM/YYYY HH:mm`, default newest-first) | Actor (avatar + name; "System" when `actor_name` null) | Event (human-readable label from event type) | Entity (`entity_type — entity_label`) | Details (truncated summary, e.g. "Draft → Sent", "Role: Member → Admin"). - [ ] Row expand → `AuditRowDetail`: full metadata as key/value pairs; for `settings.updated` a diff view (old/new) with blocklisted fields shown as `[redacted]`; IP + User-Agent collapsed under "Technical details"; raw-JSON toggle. - [ ] "Export CSV" button (top-right) → `GET /api/audit-log/export` with current filters. - [ ] Pagination: "Load more" button (cursor-based, 50/page) — no offset paging. - [ ] A11y: table uses semantic `` roles; expand control is a `