Audit trail

Every finance write is captured in the AuditLog table. Two layers cooperate: semantic logAudit(...) calls in the API for business-level events (approve, reject, reverse, issue-invoice) plus DB triggers that guarantee a row for every INSERT/UPDATE/DELETE on core finance tables.

Audience: auditors tracing a specific voucher back to its actor; finance leads reviewing change history; developers adding a new write path that needs audit coverage.

1. Schema

Source: supabase/migrations/20260113130121_remote_schema.sql:79-87 plus column adds in supabase/migrations/20260403150000_audit_trail_columns.sql.

Column	Type	Purpose
`id`	text PK	UUID.
`actorId`	text NOT NULL	User UUID who performed the action, or `'system'` if the write came from a trigger/service-role path without a resolvable JWT.
`action`	text NOT NULL	Semantic verb (`finance.voucher.approve`, `issued`) or table-op label (`booking_updated`).
`entity`	text NOT NULL	Logical entity (`journal_entry`, `booking`, `payment`, `group_invoice`). Mixed case in practice — triggers emit table name as-is (`Booking`); API calls lowercase it.
`entityId`	text NOT NULL	PK of the target row.
`metadata`	jsonb	Free-form: changed fields, voucher numbers, previous statuses, reversal links, `bulk: true`, etc.
`userName`	text	Optional denormalised display name, resolved at write time if possible (`src/lib/api.ts:1814-1815`).
`ipAddress`	text	Column added in `20260403150000_audit_trail_columns.sql`. Not populated by the current API layer.
`createdAt`	timestamp	Default `CURRENT_TIMESTAMP`.

No RLS changes are documented on AuditLog — it inherits the permissive policies from the initial schema dump (grant ... on table "AuditLog" at src/lib/.../20260113130121_remote_schema.sql:1364+).

2. Two writer layers

2.1 API-level `logAudit()`

Helper at src/lib/api.ts:1811-1830:

async function logAudit(action: string, entityType: string, entityId: string, details?: any) {
  try {
    const userId = await getCurrentUserId();
    const userName = userId
      ? (await supabase.from('User').select('fullName').eq('id', userId).maybeSingle())?.data?.fullName ?? null
      : null;
    await supabase.from('AuditLog').insert({
      id: uuid(),
      actorId: userId ?? 'system',
      action,
      entity: entityType,
      entityId,
      metadata: details ?? null,
      userName,
      createdAt: new Date().toISOString(),
    });
  } catch (err) {
    console.warn('Audit log failed (non-blocking):', err);
  }
}

Key properties:

Non-blocking. A failed audit INSERT never bubbles up — the surrounding write still succeeds. Intentional: audit coverage is important but must never be the reason a legitimate business write fails.
Resolves actor + name at write time. The userName snapshot is denormalised so renaming a user later does not rewrite history.

2.2 DB-level trigger

supabase/migrations/20260418090000_audit_triggers_transactions.sql attaches one AFTER trigger on each of:

Table	Trigger name
`Booking`	`audit_booking`
`Payment`	`audit_payment`
`JournalEntry`	`audit_journalentry`
`SupplierTransaction`	`audit_suppliertransaction`
`GroupInvoice`	`audit_groupinvoice`

Each fires on INSERT/UPDATE/DELETE and writes one row:

action = <tablename>_created | _updated | _deleted.
actorId = auth.uid()::text or 'system' when the write came from service-role or another trigger.
metadata:
INSERT → {"created": true}
UPDATE → {"changes": {"col": [oldValue, newValue], ...}} — diff excludes createdAt / updatedAt.
DELETE → {"deleted": true, "row": <full OLD snapshot as jsonb>}.

The trigger function audit_table_change() is SECURITY DEFINER and swallows INSERT errors with a RAISE WARNING so audit failures cannot block the original write.

Two rows per semantic write is intentional

A single journal approval writes:

The trigger row — journalentry_updated with a changes diff.
The explicit call — finance.voucher.approve with previousStatus: 'pending' metadata.

The trigger row proves the column-level change; the semantic row tells the auditor what business event triggered it. Queries should filter on one or the other based on the question being asked.

3. Semantic finance actions (ground truth)

The behavioural truth for which finance actions emit an audit row is the test suite src/lib/api.finance.audit.test.ts. Every writer below has a dedicated test asserting the audit row's action, actor, entity, and entityId.

Endpoint	Action label	`entity`	`metadata` highlights
`POST /finance/journals`	`finance.voucher.create`	`journal_entry`	`referenceType`, `referenceId`. Src: `src/lib/api.ts:22118-22121`.
`POST /finance/journals/:id/approve`	`finance.voucher.approve`	`journal_entry`	`previousStatus`. Src: `src/lib/api.ts:22059`.
`POST /finance/journals/:id/reject`	`finance.voucher.reject`	`journal_entry`	`previousStatus`. Src: `src/lib/api.ts:22103`.
`POST /finance/journals/:id/reverse`	`finance.voucher.reverse`	`journal_entry`	`reversalEntryId`, `voucherNo`, `referenceType`, `referenceId`. Src: `src/lib/api.ts:21977-21982`.
`POST /finance/journals/approve-bulk`	`finance.voucher.approve` (one per id)	`journal_entry`	`bulk: true`, `count`. Src: `src/lib/api.ts:21721`.
`POST /finance/vouchers/customer-on-account-receipt`	`recorded`	`customer_on_account_receipt`	amount, customer, receipt no. Src: `src/lib/api.ts:11410`.
`POST /finance/vouchers/agent-on-account-receipt`	`recorded`	`agent_on_account_receipt`	amount, agent, receipt no. Src: `src/lib/api.ts:11811`.
`POST /finance/vouchers/agent-receipt-allocate`	`allocated`	`agent_on_account_receipt`	allocations list. Src: `src/lib/api.ts:11942`.
`POST /finance/vouchers/supplier-refund-receipt`	`recorded`	`supplier_refund_receipt`	supplier, amount. Src: `src/lib/api.ts:11529`.
`POST /finance/vouchers/credit-note` / `.../debit-note`	`issued_credit_note` / `issued_debit_note`	varies by referenceType	amount, reason. Src: `src/lib/api.ts:11695`.
`POST /payments` (record / verify)	`recorded` / `verified`	`payment`	bookingId, amount, method, receiptNo. Src: `src/lib/api.ts:12212, 12235`.
`POST /group-invoices/:id/issue`	`issued`	`group_invoice`	invoiceNumber, total. Src: `src/lib/api.ts:9318`.
`POST /group-invoices/:id/cancel`	`cancelled`	`group_invoice`	invoiceNumber. Src: `src/lib/api.ts:9367`.
`POST /finance/cross-ledger-settlement`	`posted_cross_ledger_settlement`	`settlement_cross_ledger`	details. Src: `src/lib/api.ts:16926`.

Plus the trigger-emitted rows on JournalEntry, Payment, Booking, SupplierTransaction, GroupInvoice for every underlying row mutation.

Never post a journal via direct SQL in production

The API ensures a semantic audit row. The trigger will catch the raw INSERT and log it as journalentry_created, but the semantic finance.voucher.create label (and its referenceType metadata) is only written by the API. A direct INSERT from psql breaks the compliance posture because the auditor can no longer tell "manually posted by ops" apart from "system-generated revenue posting".

4. Querying the audit log

4.1 API endpoint

GET /audit?entityType=<entity>&entityId=<id> at src/lib/api.ts:12457-12520.

No explicit permission check on the GET — falls back to the route's default auth gate. The /admin/audit route in the UI is gated at the page level by admin.view; admin.audit.view exists in the permission catalog (docs/PERMISSIONS.md §4.4) but is not yet wired at this endpoint.
Returns the latest 500 rows ordered by createdAt DESC.
Accepts entityType and entityId query filters.
Resolves missing userName values by looking up the User table.

POST /audit at src/lib/api.ts:12458-12466 lets the UI write arbitrary external audit entries; gated by finance.create.

4.2 Direct SQL

For auditor drill-downs that need more than 500 rows or the trigger diffs:

-- Every mutation on a specific journal, chronological:
SELECT "createdAt", "actorId", "userName", action, metadata
  FROM public."AuditLog"
 WHERE entity IN ('journal_entry', 'JournalEntry')
   AND "entityId" = '<journal-id>'
 ORDER BY "createdAt";

-- Every approve/reject in a date range, grouped by approver:
SELECT "actorId", COUNT(*) AS approvals
  FROM public."AuditLog"
 WHERE action = 'finance.voucher.approve'
   AND "createdAt" BETWEEN '2026-04-01' AND '2026-04-30'
 GROUP BY "actorId"
 ORDER BY approvals DESC;

4.3 UI access

/admin — Audit logs tab. Route gated by admin.view (see docs/PERMISSIONS.md §6.16).
Booking, group, and voucher detail dialogs each surface their own filtered slice via the entity-scoped /audit?entityType=<>&entityId=<> lookup.

5. Retention

There is no automated retention job. The table grows monotonically.

Retention is an open TODO

For now, plan on keeping everything. If size becomes a concern, partition AuditLog by createdAt month and vacuum old partitions out to archive storage — do not DELETE rows referenced by an active audit engagement.

6. What the statutory auditor will ask for

The trail is designed to answer these questions without a developer in the room:

Who approved this journal, and when? Filter: action = 'finance.voucher.approve' AND entityId = '<je_id>'. The row shows actorId, userName, createdAt, and previousStatus.
Was the approver different from the creator? Join AuditLog (approve action) with JournalEntry.createdBy. Our maker-checker guard enforces this at write time (src/lib/api.ts:22030-22039), so a same-actor pair means either (a) the approver held finance.journals.approve_own (super-admin break-glass) or (b) the row was written outside the API path — itself a red flag.
What changed between two timestamps on a booking? Filter trigger rows: entity = 'Booking' AND entityId = '<b_id>' AND "createdAt" BETWEEN .... The metadata.changes JSON spells out every column diff.
Show me the full trail on this invoice (create → edit → issue → cancel). entity = 'group_invoice' AND entityId = '<gi_id>' — surfaces the semantic sequence created → issued → cancelled. Plus entity = 'GroupInvoice' for the trigger row diff on each mutation.
Show me every reversal posted in Q4. action = 'finance.voucher.reverse' AND "createdAt" BETWEEN '2026-10-01' AND '2026-12-31'.
Any period-lock bypass attempts? Not captured in AuditLog directly — the DB trigger enforce_accounting_period_lock() raises check_violation and aborts the INSERT, so no row lands. If a bypass attempt needs to be observable, add explicit logAudit('period_lock_violation', ...) to the API pre-check.

7. Adding audit coverage to a new write path

At the end of the happy-path block, call await logAudit('<domain>.<action>', '<entity>', rowId, { ...metadata });. Match the naming convention: lowercase, dotted (e.g. finance.voucher.reverse).
If the entity is not on the trigger list (Booking/Payment/JournalEntry/SupplierTransaction/GroupInvoice) and you want automatic column-level diffs, add it to the trigger array in a new migration modelled on 20260418090000_audit_triggers_transactions.sql:97.
Add a test in src/lib/api.finance.audit.test.ts (or the sibling api.*.test.ts) asserting the row's action, entity, entityId, actorId, and key metadata fields.
If the action is regulatory-sensitive (approve, reject, reverse, issue, cancel), consider promoting from the try/catch pattern in logAudit() to a blocking write — the current default is non-blocking (see §2.1).

8. Cross-references

Maker-checker — the approval trail the audit log captures.
Double-entry engine — what journal_entry entity-level audit rows refer to.
src/lib/api.finance.audit.test.ts — behavioural truth for every semantic audit action.
supabase/migrations/20260418090000_audit_triggers_transactions.sql — DB-level trigger source.