audit — Phase 10

Schema locked 2026-06-10. 7 tables, 118 cols: audit_log (22), data_subject_request (17), data_breach_incident (20), compliance_task (15), dpa_agreement (15), subprocessor (13), audit_export_job (16).

Audit has two clusters. Cluster A: the cross-cutting integrity-chained event log (audit_log). Cluster B: the compliance-workflow platform (data_subject_request, data_breach_incident, compliance_task, dpa_agreement, subprocessor, audit_export_job).

Design principles (document at every session touching this schema):

• Reference, don't copy: audit_log captures cross-cutting change events that have NO existing trail — entity edits, config changes, permission changes, access events not already recorded elsewhere. For domains with authoritative append-only trails already built (crm.customer_consent, identity.identity_access_event, inventory.stock_movement, billing.ar_payment_application, payments.stripe_event_log, pos.sale_line_tax), Audit does NOT re-log — it POINTS at them via source_table + source_ref. Single source of truth per trail.
• Integrity chain (per-tenant, async): audit_log rows carry sequence_number + row_hash + prev_hash, chained per tenant (not global — tenants do not serialize against each other). Hash = SHA-256(prev_hash || canonical_row_payload). The row is written immediately; the hash is linked in a tight async step. row_hash / prev_hash are null-on-insert and set once (null → value, never mutate). Daily verification job walks the chain per tenant. Unchained-row contract: row_hash IS NULL is acceptable ONLY for the tail of a tenant's sequence — rows awaiting the async chain step within the expected SLA. A row where row_hash IS NULL and any later-sequenced row for the same tenant has row_hash set is a chain break — the verification job must alert. The (tenant_id, sequence_number) unique guarantees ordering even before the hash links, so a gap is always detectable.
• Store full, mask at read: before_state / after_state are stored complete for forensic integrity. PII masking is a read-time access-control layer, not a write-time redaction. granularity_level controls what is logged (all / sensitive-only / minimal per tenant config in admin.tenant_setting), not whether it is masked.
• Audit owns compliance reporting: GDPR/SAR responses, breach notifications, and regulator-bound exports live in Audit. The Reporting module (Module 6) owns operational business-intelligence reports only. These are different audiences (compliance officers vs. operations staff) and different data shapes.
• Event isolation: audit.* events are not subscribed by operational modules — this prevents event cycles. audit.log_entry_created and similar events are internal to the Audit module.
• No audit-side config tables: AuditService reads retention period, granularity, and access-level config from admin.tenant_setting (the catch-all key-value store already built). Audit owns no config tables.

Deferred items:

• AI anomaly / suspicious-activity detection (spec 8.5, 11.1) — dependency on the AI module (not built); add when AI module exists.
• SOC 2 Type II readiness report — year-2 per spec.
• subprocessor: index on dpa_id — add when "subprocessors by DPA" query is needed.
• audit_export_job: (tenant_id, status) composite index — add when export-job dashboard query is defined.
• data_subject_request / compliance_task / data_breach_incident: (tenant_id, due_date/detected_at) composite indexes — add when per-tenant time-window queries are profiled.

Cut (not deferred — wrong for Vrida):

• HR / labor audit — HR is permanently out of scope.
• Nursery pesticide / USDA compliance records — nursery-specific; generic-first; a compliance_document category code at most.
• Tax-rate-change audit — Stripe Tax owns calculation; no rate tables exist.
• Tenant compliance docs — already admin.compliance_document.
• Vrida's own certification display — Platform concern, not tenant-audit tables.

Cross-Phase FK seams:

Column	References	Status
*.tenant_id	platform.tenant	Locked — enforced
audit_log.actor_user_id	identity.identity_user	Locked — enforced (nullable; system/automated events have no actor)
data_subject_request.data_subject_customer_id	crm.customer	Locked — enforced (nullable; subject may not be a linked customer)
data_subject_request.handled_by_user_id	identity.identity_user	Locked — enforced (nullable)
data_breach_incident.discovered_by_user_id	identity.identity_user	Locked — enforced (nullable)
compliance_task.assigned_to_user_id	identity.identity_user	Locked — enforced (nullable)
dpa_agreement.supersedes_dpa_id	audit.dpa_agreement	Intra-schema — enforced (nullable)
subprocessor.dpa_id	audit.dpa_agreement	Intra-schema — enforced (nullable)
audit_export_job.requested_by_user_id	identity.identity_user	Locked — enforced (nullable)
audit_log.source_table / source_ref	Any module's authoritative trail (e.g. crm.customer_consent, inventory.stock_movement)	Polymorphic pointer — NOT enforced FK; source_table is the discriminator (e.g. 'crm.customer_consent'); loose by design
audit_export_job.export_ref	Files / R2 (file key text)	NOT CLOSED — Files module not built; text seam only
data_subject_request.response_artifact_ref	Files / R2 (file key text)	NOT CLOSED — Files module not built; text seam only
dpa_agreement.document_ref	Files / R2 (file key text)	NOT CLOSED — Files module not built; text seam only

---

audit.audit_log — 22 cols

The universal cross-cutting change-event log. Captures entity edits, config changes, permission changes, access events, and business-event records that have no authoritative trail in another module. For events that DO have an authoritative trail in another module (consent, stock movements, payment events, billing applications), the row POINTS at that trail via source_table + source_ref rather than duplicating the data.

Append-only — no updated_at, no deleted_at. Rows are never modified or deleted. The only post-insert write is the async hash-chain step: row_hash and prev_hash transition NULL → value exactly once after insert. They never mutate after that.

RLS: tenant-isolated on tenant_id. Read access gated to compliance-owner roles; before_state / after_state PII masking is enforced at the read layer by AuditService, not at the table level.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
sequence_number	bigint	NOT NULL	—	Per-tenant monotonic sequence. The chain order for this tenant. Combined with tenant_id forms the uniqueness anchor.
actor_user_id	UUID	nullable	—	FK → identity.identity_user; NULL for system-generated or automated events
actor_type	text	NOT NULL	'user'	CHECK IN ('user','system','api','integration')
action	text	NOT NULL	—	CHECK IN ('create','update','delete','view','export','login','logout','permission_change','config_change','other')
entity_type	text	NOT NULL	—	The kind of entity changed (e.g. 'customer', 'price_rule', 'tenant_setting', 'role'). Free text — generic across modules.
entity_ref	UUID	nullable	—	The changed entity's id. NULL for events without a single target entity.
entity_module	text	nullable	—	Which schema/module owns the entity (e.g. 'crm', 'inventory').
source_table	text	nullable	—	When this entry POINTS at an existing authoritative trail rather than duplicating it — the fully-qualified table name (e.g. 'crm.customer_consent'). NULL for events logged directly.
source_ref	UUID	nullable	—	The id of the row in source_table. Polymorphic; not enforced FK (cross-schema by design).
before_state	JSONB	nullable	—	Full prior entity state. Stored complete — PII masking is a read-time layer, not applied here. NULL for creates and events with no prior state. Example: {"id":"<uuid>","name":"Acme Landscaping","credit_limit_cents":50000}. Must be NULL when source_table is set (pointer mode).
after_state	JSONB	nullable	—	Full new entity state. NULL for deletes and events with no subsequent state. Example: {"id":"<uuid>","name":"Acme Landscaping","credit_limit_cents":75000}. Must be NULL when source_table is set (pointer mode).
change_summary	text	nullable	—	Human-readable description of what changed (e.g. 'credit_limit_cents changed 50000 → 75000').
granularity_level	text	NOT NULL	'full'	CHECK IN ('full','sensitive','minimal') — records what granularity was active when this entry was written, per tenant config in admin.tenant_setting.
ip_address	text	nullable	—	Source IP address at time of event.
user_agent	text	nullable	—	Browser / client user-agent string.
session_ref	text	nullable	—	Identity session reference (Supabase session ID or API key ref) for correlated-event queries.
row_hash	text	nullable	—	SHA-256(prev_hash || canonical_row_payload). NULL on insert; set once by the async chain step. Never mutates after linking.
prev_hash	text	nullable	—	row_hash of the preceding row in this tenant's sequence. NULL for the first row of a tenant's chain. Set alongside row_hash in the async chain step.
occurred_at	timestamptz	NOT NULL	—	When the event happened (business time). May differ from created_at for buffered or replayed events.
created_at	timestamptz	NOT NULL	now()	When this row was inserted into the log.

Table-level CHECKs:

• CHECK ((source_table IS NULL) = (source_ref IS NULL)) — pointer-pair: source_table and source_ref must be both set or both NULL; one without the other is silently malformed
• CHECK ((source_table IS NULL) OR (before_state IS NULL AND after_state IS NULL)) — pointer mode (source_table IS NOT NULL) and copy mode (before_state/after_state set) are mutually exclusive; when audit_log points at an existing authoritative trail it does NOT also copy state — DB-level guard against Audit duplicating existing consent/access/stock trails

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (tenant_id, sequence_number) — per-tenant chain integrity anchor; one row per sequence position per tenant
• on (tenant_id, occurred_at) — primary compliance/SAR/export query: tenant + date range
• on (actor_user_id)
• on (entity_type, entity_ref)
• on (action)
• on (occurred_at) — cross-tenant ops jobs
• on (source_table, source_ref) WHERE source_table IS NOT NULL — cross-reference lookups from authoritative trails

---

audit.data_subject_request — 17 cols

GDPR / CCPA rights workflow. One row per data subject request (access, erasure, rectification, restriction, objection, portability, opt-out). Tracks the full lifecycle from receipt through verification, handling, and response within the statutory deadline.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
request_type	text	NOT NULL	—	CHECK IN ('access','erasure','rectification','restriction','objection','portability','opt_out')
data_subject_customer_id	UUID	nullable	—	FK → crm.customer; NULL if the subject is not a linked customer (e.g. anonymous inquiry)
data_subject_identifier	text	nullable	—	Email, name, or other identifier when the subject is not linked to a customer record
status	text	NOT NULL	'received'	CHECK IN ('received','verifying','in_progress','completed','rejected','withdrawn')
received_at	timestamptz	NOT NULL	—	When the request was received (starts the statutory clock)
verification_method	text	nullable	—	How the requester's identity was verified (e.g. 'email_link', 'id_document')
due_date	date	NOT NULL	—	Statutory deadline — GDPR 30 days; CCPA 45 days. Computed at receive time.
completed_at	timestamptz	nullable	—	When the request was fulfilled or rejected
response_summary	text	nullable	—	Description of what was provided or why rejected
response_artifact_ref	text	nullable	—	Files / R2 key for the data package delivered to the subject. NOT CLOSED — Files module not built; text seam.
rejection_reason	text	nullable	—	Reason for rejection (e.g. 'unverified_identity', 'no_data_found')
handled_by_user_id	UUID	nullable	—	FK → identity.identity_user — staff member managing this request

Indexes:

• PK on id
• on (tenant_id)
• on (status) WHERE status IN ('received','verifying','in_progress') — active queue
• on (due_date) — deadline sweep
• on (data_subject_customer_id)
• on (request_type)

---

audit.data_breach_incident — 20 cols

Data breach detection, classification, containment, and regulator / subject notification workflow. Tracks the incident from detection through resolution with GDPR's 72-hour regulator notification clock.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
title	text	NOT NULL	—	Short incident title
description	text	nullable	—	Narrative description of what happened
severity	text	NOT NULL	—	CHECK IN ('low','medium','high','critical')
status	text	NOT NULL	'detected'	CHECK IN ('detected','investigating','contained','notifying','resolved','closed')
detected_at	timestamptz	NOT NULL	—	When the breach was first detected (starts the 72h GDPR clock)
discovered_by_user_id	UUID	nullable	—	FK → identity.identity_user — who detected it; NULL for automated detection
affected_data_categories	JSONB	nullable	—	Array of data category labels (e.g. ["customer_pii","payment_data"])
affected_subject_count	integer	nullable	—	Estimated number of affected data subjects; may be unknown initially
regulator_notification_required	boolean	NOT NULL	false	Whether GDPR Article 33 / CCPA notification to regulator is required
regulator_notification_due_at	timestamptz	nullable	—	72 hours after detected_at when notification is required (GDPR standard). Must be set when regulator_notification_required = true — enforced by table-level CHECK.
regulator_notified_at	timestamptz	nullable	—	When regulator was notified
subjects_notified_at	timestamptz	nullable	—	When affected subjects were notified
containment_summary	text	nullable	—	What was done to contain the breach
resolution_summary	text	nullable	—	Root cause and remediation steps
resolved_at	timestamptz	nullable	—	When the incident reached a terminal state

Table-level CHECKs:

• CHECK ((regulator_notification_required = false) OR (regulator_notification_due_at IS NOT NULL)) — when regulator notification is required, the statutory 72-hour GDPR clock deadline must be set at the same time; it cannot be silently NULL

Indexes:

• PK on id
• on (tenant_id)
• on (status) WHERE status NOT IN ('resolved','closed') — open incidents
• on (severity)
• on (regulator_notification_due_at) WHERE regulator_notification_due_at IS NOT NULL — deadline sweep
• on (detected_at)

---

audit.compliance_task — 15 cols

Deadline-tracked compliance obligations: SAR response deadlines, breach notification windows, DPA review cycles, retention-policy reviews, and general compliance items. Links to the source record driving the obligation.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
title	text	NOT NULL	—	Short task title
description	text	nullable	—	Optional detail
task_type	text	NOT NULL	—	CHECK IN ('sar_deadline','breach_notification','dpa_review','retention_review','certification','general')
related_module	text	nullable	—	Which Audit sub-table this task tracks (e.g. 'data_subject_request', 'data_breach_incident')
related_ref	UUID	nullable	—	The id of the related record (polymorphic; not enforced FK — intra-module loose ref)
status	text	NOT NULL	'open'	CHECK IN ('open','in_progress','completed','cancelled') — 'overdue' is not stored; computed at query: status IN ('open','in_progress') AND due_date < now(). Stored derived state silently lies when the batch job lags.
due_date	date	NOT NULL	—	Task deadline
priority	text	NOT NULL	'medium'	CHECK IN ('low','medium','high')
assigned_to_user_id	UUID	nullable	—	FK → identity.identity_user
completed_at	timestamptz	nullable	—	When the task was completed

Table-level CHECKs:

• CHECK ((related_module IS NULL) = (related_ref IS NULL)) — relation pair: related_module and related_ref must be both set or both NULL; a task pointing at a module with no ref (or vice versa) is silently malformed

Indexes:

• PK on id
• on (tenant_id)
• on (status) WHERE status IN ('open','in_progress') — active task queue ('overdue' is computed at query, not stored)
• on (due_date)
• on (assigned_to_user_id)
• on (task_type)

---

audit.dpa_agreement — 15 cols

Data Processing Agreement (DPA) management and version history. One row per DPA version with a supersedes_dpa_id chain. Covers Vrida–tenant DPA, tenant–customer DPAs, and tenant–subprocessor DPAs.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
counterparty_name	text	NOT NULL	—	Name of the other party to the DPA
counterparty_type	text	NOT NULL	—	CHECK IN ('customer','vendor','subprocessor','other')
version	text	NOT NULL	—	Version identifier (e.g. 'v1.0', '2026-06')
status	text	NOT NULL	'draft'	CHECK IN ('draft','active','superseded','terminated')
effective_date	date	nullable	—	Date the DPA came into force
expires_at	date	nullable	—	Expiry date if time-limited
document_ref	text	nullable	—	Files / R2 key for the signed DPA document. NOT CLOSED — Files module not built; text seam.
supersedes_dpa_id	UUID	nullable	—	FK → audit.dpa_agreement — the prior version this replaces; forms a version chain
signed_at	timestamptz	nullable	—	When signatures were completed
notes	text	nullable	—	Internal notes

Indexes:

• PK on id
• on (tenant_id)
• on (counterparty_type)
• on (status) WHERE status = 'active'
• on (expires_at)
• UNIQUE on (tenant_id, counterparty_name, version) WHERE deleted_at IS NULL

---

audit.subprocessor — 13 cols

GDPR subprocessor list — the maintained register of third-party processors the tenant uses. GDPR Article 28 requires tenants to maintain and disclose this list. Regulators ask for it explicitly; it is a real maintained list with its own lifecycle, not a JSONB column on dpa_agreement.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
name	text	NOT NULL	—	Subprocessor name (e.g. 'Stripe', 'Resend', 'AWS')
purpose	text	NOT NULL	—	What data processing they perform (e.g. 'payment processing', 'transactional email delivery')
data_categories	JSONB	nullable	—	Array of data category labels they process (e.g. ["payment_data","customer_pii"])
location	text	nullable	—	Country or region of data processing (data residency context)
dpa_id	UUID	nullable	—	FK → audit.dpa_agreement — the DPA governing this subprocessor relationship
status	text	NOT NULL	'active'	CHECK IN ('active','pending','removed')
added_at	timestamptz	NOT NULL	—	When this subprocessor was added to the register
removed_at	timestamptz	nullable	—	When removed (status = 'removed')

Indexes:

• PK on id
• on (tenant_id)
• on (status) WHERE status = 'active'
• UNIQUE on (tenant_id, name) WHERE deleted_at IS NULL

---

audit.audit_export_job — 16 cols

Tracks tamper-evident signed exports of audit log data — scheduled weekly exports, on-demand exports, SAR data packages, and regulator-bound export bundles. The signed hash (feature 1.8 note) provides tamper-evidence for the exported file.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
export_type	text	NOT NULL	—	CHECK IN ('scheduled','on_demand','sar_response','regulator')
scope	text	nullable	—	Human-readable description of what was exported (date range, filters applied)
format	text	NOT NULL	'csv'	CHECK IN ('csv','pdf','json')
status	text	NOT NULL	'pending'	CHECK IN ('pending','running','completed','failed')
export_ref	text	nullable	—	Files / R2 key for the completed export file. NOT CLOSED — Files module not built; text seam.
signature	text	nullable	—	SHA-256 signed hash of the export content — provides tamper-evidence for the exported file
row_count	integer	nullable	—	Number of audit log rows included in the export
requested_by_user_id	UUID	nullable	—	FK → identity.identity_user; NULL for scheduled/automated exports
started_at	timestamptz	nullable	—	When export generation began
completed_at	timestamptz	nullable	—	When the export file was finalized
failure_reason	text	nullable	—	Error message if status = 'failed'

Indexes:

• PK on id
• on (tenant_id)
• on (status)
• on (export_type)
• on (completed_at)

---

Column counts: audit_log(22) + data_subject_request(17) + data_breach_incident(20) + compliance_task(15) + dpa_agreement(15) + subprocessor(13) + audit_export_job(16) = 118

---