Why every entity gets an audit log on day one.

On every LogsOfN build, the second migration after the schema bootstrap is an audit_log table. Not the third. Not the fourth. Second.

That's a deliberate choice and it costs us a non-trivial amount of plumbing before the first feature ships. Here's why we do it.

What goes in

For every status-bearing entity — invoices, orders, deliveries, tasks, expenses, payroll runs, vehicles — there's a generic trigger that fires on insert / update / delete. The trigger writes a row to audit_log with:

• The entity type and id
• The before-state and after-state, as JSONB
• Status from / to (when status actually changed)
• The actor — resolved through auth.uid() → employees.id
• The actor's role at the moment of the event
• The source — app, engine, or cron
• An occurred_at timestamp

The same trigger function attaches to every entity. Generic, security definer, idempotent.

Why we do it

Trust gets built once. The first time a director asks "why was this invoice marked paid on the 12th?" and we can show a row that says "on the 12th at 14:32, status went from sent to paid, actor was the finance lead, source was app" — that's the moment the system becomes trustworthy. If we'd waited until month six to add auditing, we'd have a six-month gap in the record. We don't.

Compliance is free if it's already there. SARS audits, bank reconciliations, dispute resolution, internal investigations — every one of these is a query against a table that already exists. No retrofit. No "we don't have logs for that period."

Debugging is faster. When a customer says "this order disappeared yesterday", the audit log tells us exactly what happened. We don't need to read application logs or git-blame the deployment. The state transition is in one row.

Trigger discipline forces good schema decisions. Designing the audit log on day one forces us to decide what "status-bearing" means. Every entity gets a status enum with a defined state machine. That single discipline pushes the schema toward legibility.

What it costs

About 80 lines of SQL for the trigger function and one create trigger statement per entity. The storage cost is meaningful — JSONB before/after on every change adds up — but it's cheaper than the alternative of trying to reconstruct history from application logs.

Performance: the trigger fires on commit, not in the hot path. We've never measured a write-latency hit that mattered.

The view

On top of the table we ship a single view: v_audit_trail_by_entity. It joins to employees to resolve actor names. It's the one query the UI ever runs against the audit log — no direct table reads, no joins spread through the codebase.

The rule

We don't ship a status-bearing entity without it. There is no system in production at LogsOfN where you can change a record's state and not be able to find out who changed it, when, and from what. Day one. Every time.

That's the smallest reasonable infrastructure that makes a system trustworthy. Everything else — RBAC, RLS, monitoring, backups — accumulates on top of it.