# Audit & Compliance

**Date:** 2026-05-30  
**Status:** Draft  
**Depends on:** `foundation-auth-rbac`, `foundation-monorepo`, `white-label-api`  
**Referenced by:** none (terminal spec)

---

## Overview

Cross-cutting compliance layer: (1) **Audit log** — immutable per-tenant event trail for all write operations; (2) **GDPR & data portability** — tenant export, user data erasure; (3) **Row-Level Security consideration** — whether DB-level RLS is needed given application-level `tenantQuery` enforcement; (4) **Observability** — error tracking and rate monitoring. This spec adds no UI of its own except admin surfaces; it constrains how all other specs implement writes.

---

## Audit Log

### Scope

All state-changing operations emit an audit event. This is not optional — every `INSERT`/`UPDATE`/`DELETE` on business entities must create an audit record.

**Entities audited:**
- invoices, invoice_lines (create, update, status change, delete)
- expenses, expense_corrections (create, update, approve/reject)
- leads, payout_bills (create, stage change)
- tickets, ticket_messages (create, reply, status change)
- users (invite, role change, freeze/unfreeze, delete)
- tenant settings, adapter_credentials (any change)
- time_entries (create, update, delete)
- kb_articles (create, publish, delete)
- payment_methods, payments (create, update)
- webhook_endpoints, tenant_api_keys (create, revoke)

### Data Model

```sql
audit_log (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  actor_id UUID,                         -- user who made the change; NULL for system/cron actions
  actor_type TEXT DEFAULT 'user',        -- 'user' | 'system' | 'api_key' | 'portal_customer'
  api_key_id UUID,                       -- set when action via API key
  entity_type TEXT NOT NULL,             -- e.g. 'invoice', 'lead', 'user'
  entity_id UUID NOT NULL,
  action TEXT NOT NULL,                  -- 'created' | 'updated' | 'deleted' | 'status_changed' | ...
  changes JSONB,                         -- { field: [old, new] } for updates; null for creates/deletes
  request_id TEXT,                       -- CF request ID for correlation
  ip TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
)

-- Partition by created_at month (PG declarative partitioning):
-- Keeps query performance stable as log grows.
-- Retention: 7 years (IL tax document retention requirement).
CREATE TABLE audit_log_2026_05 PARTITION OF audit_log
  FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
-- Cron creates next month's partition on 1st of each month.
```

**Index:** `(tenant_id, entity_type, entity_id, created_at DESC)` — the primary query pattern.

### Write Pattern

Audit writes happen in the **same DB transaction** as the business operation:

```ts
// in every write route handler:
await db.transaction(async (tx) => {
  // business operation
  await tx.update(invoices).set({ status: 'TAX_ISSUED' }).where(...)
  // audit record in same transaction
  await tx.insert(auditLog).values({
    tenantId, actorId: ctx.userId, actorType: 'user',
    entityType: 'invoice', entityId: invoice.id,
    action: 'status_changed',
    changes: { status: ['APPROVED', 'TAX_ISSUED'] },
    requestId: ctx.requestId, ip: ctx.ip
  })
})
```

A route that updates but fails to write audit = not compliant. ESLint rule `require-audit-in-transaction` enforces this on write handlers.

### Audit Log View

Available in `admin-dashboard.md` at `/admin/audit`. Also per-entity in each module (e.g., invoice detail shows its audit trail in the status timeline).

Filters: tenant, actor, entity type, entity ID, date range. Exported as CSV.

**Immutability:** audit records are INSERT-only. No UPDATE or DELETE permitted. Enforced by Postgres triggers that RAISE an error (not silent NOTHING — silent swallow hides bugs):

```sql
CREATE OR REPLACE FUNCTION audit_log_immutable() RETURNS trigger AS $$
BEGIN
  RAISE EXCEPTION 'audit_log is immutable: % not permitted', TG_OP;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_log_no_update
  BEFORE UPDATE ON audit_log FOR EACH ROW EXECUTE FUNCTION audit_log_immutable();

CREATE TRIGGER audit_log_no_delete
  BEFORE DELETE ON audit_log FOR EACH ROW EXECUTE FUNCTION audit_log_immutable();
```

---

## GDPR & Data Portability

### Tenant Export ("Single-click Export")

`POST /api/admin/export` (system admin only) or `POST /api/tenant/export` (tenant owner).

Generates an encrypted ZIP containing:
- All DB tables for the tenant (JSON format, one file per table)
- All R2 objects under `{tenantId}/` prefix
- `manifest.json`: export timestamp, table counts, R2 object count

Process:
1. Enqueue `export.generate` job
2. Consumer: stream DB table data to R2 temp file, collect R2 objects, zip, encrypt with AES-256-GCM (key derived from `DATA_EXPORT_KEY` + tenant ID)
3. On complete: store ZIP in R2 at `exports/{tenantId}/{exportId}.zip`; notify tenant owner via email with signed download URL (24h TTL)
4. Auto-delete ZIP after 7 days

### User Data Erasure (GDPR Right to Erasure)

For individual user erasure requests:

1. `POST /api/admin/users/:userId/erase` (system admin only)
2. Pseudonymize: replace user PII with `[ERASED_{date}]` placeholder in all tables referencing `user_id` (name, email, phone columns)
3. Delete: authentication records, sessions, profile photo (R2)
4. Retain: audit log records (pseudonymized actor name, keep action + entity for compliance), invoices (IL law: 7yr retention), expense records
5. Log the erasure itself in audit_log with actor_type = 'system', action = 'user_erased'

**What is NOT erased:** invoices, expense receipts, audit log records — these are required by IL tax law (7-year retention).

### Data Retention Policy

| Data | Retention | Reason |
|------|-----------|--------|
| Invoices + tax documents | 7 years from issue date | IL tax authority requirement (פקודת מס הכנסה) |
| Expense receipts (R2) | 7 years | Same |
| Audit log | 7 years | Compliance + dispute resolution |
| Time entries | 7 years | Labor law audit requirements |
| User PII (non-financial) | Until erasure request or 3 years after account close | GDPR / IL Privacy Protection Law |
| Session tokens | 30 days (refresh token TTL) | Already in auth spec |
| Export ZIPs (R2) | 7 days | Temporary download window |

Cron `data-retention-purge` — monthly. Purges expired sessions, expired export ZIPs, and any non-financial PII past retention window.

---

## Row-Level Security Consideration

**Decision: Application-level enforcement via `tenantQuery`, not DB-level RLS.**

Reasons:
- Neon Postgres supports RLS, but requires `SET app.current_tenant_id = ?` on each connection
- Hyperdrive pools connections — setting a session variable in a pooled connection leaks state across requests if connection is reused before being reset
- `tenantQuery(db, tenantId)` wraps every Drizzle query with `WHERE tenant_id = ?` at the application layer; ESLint `no-raw-drizzle-from-routes` enforces this
- Penetration test scope: verify no routes accept `tenantId` from client input; it must always come from the verified JWT

**If RLS is reconsidered:** the safe path is `pgbouncer` in `session mode` (not transaction mode) so that `SET LOCAL app.tenant = ?` is scoped to the transaction. Hyperdrive does not support session mode. A dedicated connection pool bypassing Hyperdrive would be needed. Revisit if a security audit demands DB-level enforcement.

---

## Observability

### Error Tracking

Worker unhandled exceptions → Cloudflare Workers Logpush or Sentry (Workers DSN).

For each unhandled error: capture `{ requestId, tenantId, path, error.message, error.stack }` and emit to error tracking. No PII in error payloads.

### Rate Limit Monitoring

CF native RateLimiter bindings (`RATE_LIMITER_AUTH`, `RATE_LIMITER_WEBHOOK`, etc.) emit metrics automatically to Cloudflare dashboard. Alert threshold: if auth rate limiter trips >100×/hour on same tenant → possible credential stuffing → auto-freeze tenant (optional, configurable).

### Slow Query Log

Neon slow query log: `pg_stat_statements` + Neon console. Track p99 query time per route. Baseline: <50ms p50, <200ms p99 for all API endpoints.

---

## Permissions

| Action | Required permission |
|--------|-------------------|
| View audit log (own tenant) | `audit:read` |
| View audit log (all tenants) | `SYSTEM_ADMIN` |
| Trigger tenant export | `owner` or `SYSTEM_ADMIN` |
| User erasure | `SYSTEM_ADMIN` |

---

## API Endpoints

```
GET    /api/audit                          → audit log for tenant (?entity_type=&entity_id=&from=&to=)
GET    /api/audit/export                   → CSV export of audit log

POST   /api/tenant/export                  → request full tenant data export (ZIP)
GET    /api/tenant/export/:exportId        → check export status + download URL

POST   /api/admin/users/:userId/erase      → GDPR erasure (system admin)
```

---

## Foundation Deltas

**New crons:**
- `data-retention-purge` — monthly, purges expired PII + export ZIPs
- `audit-partition-create` — 1st of each month, creates next month's `audit_log` partition

**New table:** `audit_log` (partitioned by created_at month).

**New table:** `tenant_export_jobs` — tracks async export status:
```sql
tenant_export_jobs (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  status TEXT DEFAULT 'PENDING',   -- 'PENDING' | 'PROCESSING' | 'DONE' | 'FAILED'
  r2_key TEXT,
  created_by UUID,
  created_at TIMESTAMPTZ DEFAULT now(),
  completed_at TIMESTAMPTZ
)
```

**New queue:** `export.generate` — async tenant export worker.

**New secret:** `DATA_EXPORT_KEY` — AES-256-GCM key for encrypting tenant export ZIPs.

---

## Architecture Decisions

| Decision | Choice | Reason |
|----------|--------|--------|
| Audit writes in same DB transaction | Not async | Audit record is part of the business operation; async write risks silent failure on rollback |
| Immutable audit via Postgres rules | Not application-level | DB rules prevent bypass even if application code has a bug |
| No DB-level RLS | `tenantQuery` + ESLint rule | Hyperdrive connection pooling incompatible with session-level SET; application enforcement + code review is the control |
| 7-year retention | Not GDPR minimum | IL tax law (7yr) is more stringent than GDPR default; IL law governs financial records |
| Export via queue | Not sync HTTP | Full export can take minutes; HTTP timeout would kill it; queue + email link = reliable delivery |
| Pseudonymization not hard-delete | PII replaced with placeholder | Financial records reference user IDs; hard delete breaks invoice/audit integrity; pseudonymization satisfies GDPR erasure while preserving record structure |