Muhammad Ali Mughal

Posted on Nov 10

Immutable Audit Trails in Postgres for SaaS

#architecture #database #laravel #postgres

Auditors do not care that your admin UI shows a change history. They care whether anyone with database access—or a compromised application role—can rewrite what happened last Tuesday. Mutable updated_at columns and soft-deleted “history” rows fail that test. An immutable audit trail in Postgres means append-only facts: who did what, to which tenant row, with which before/after payload, at a clock the database itself stamped.

I build Loop Origin, where we design Laravel/Postgres SaaS for teams that need SOC2-ready change evidence without bolting on a separate log product on day one. I wrote this with help from AI tools and checked every command and claim myself. The patterns below are what I ship when compliance, support, and incident response all need the same source of truth.

How do you make Postgres audit trails immutable for SaaS?

Direct answer: Store audit events in an append-only table with no UPDATE/DELETE grants for the app role, write rows in the same transaction as the business change (or via a security definer trigger), partition by month for retention, and treat corrections as new compensating events—never edits to history. Immutability is a privilege and process design, not a UI feature.

Why UPDATE and DELETE on audit rows break compliance

If the same role that mutates orders can also UPDATE audit_events SET … or DELETE FROM audit_events, the trail is theatre. Common failures I see in reviews:

Soft-delete “history” tables that still allow hard deletes by migrations or ops scripts.
Triggers that fire only on application UPDATEs, while bulk SQL and admin consoles bypass them.
Application-layer logging after the commit—process crash, dual-write gap, silent loss.
Shared superuser credentials for the app pool, so least privilege never existed.
“Fixing” a bad audit row in place instead of appending a correction event.

The design goal: the application role can INSERT audit rows (or call a function that does) and SELECT them. It cannot UPDATE or DELETE them. Retention jobs run as a separate privileged role with a documented runbook, not as the web user.

Append-only schema that survives multi-tenant SaaS

Keep the audit table in the same database as the business data so you can write both in one transaction when the app owns the write path:

CREATE TABLE audit_events (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id       uuid NOT NULL,
  actor_user_id   uuid NULL,
  actor_type      text NOT NULL CHECK (actor_type IN ('user','system','support','api_key')),
  action          text NOT NULL,          -- order.updated, role.granted
  entity_type     text NOT NULL,          -- Order, Membership
  entity_id       uuid NOT NULL,
  request_id      uuid NULL,              -- correlate with APM / logs
  ip_inet         inet NULL,
  before_state    jsonb NULL,
  after_state     jsonb NULL,
  metadata        jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at      timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

CREATE TABLE audit_events_2026_11 PARTITION OF audit_events
  FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

CREATE INDEX audit_events_tenant_created_idx
  ON audit_events (tenant_id, created_at DESC);
CREATE INDEX audit_events_entity_idx
  ON audit_events (tenant_id, entity_type, entity_id, created_at DESC);

Notes that matter in production:

created_at DEFAULT now() uses the database clock inside the statement. Do not trust the app server’s wall clock as the legal timestamp.
Store diffs or snapshots deliberately. Full before_state/after_state is simplest for support; large blobs belong in object storage with a pointer in metadata.
Partitioning keeps indexes manageable and makes retention a partition detach, not a million-row DELETE.

Triggers vs application-layer writes

Approach	Atomic with business write?	Covers raw SQL / admin?	App complexity	Typical failure
App INSERT in same DB transaction	Yes	No	Low–medium	Forgot one code path
AFTER trigger on base tables	Yes (same txn)	Yes for those tables	Medium (payload shape)	Trigger recursion, noisy columns
Security definer function only	Yes if called in txn	Depends who can call	Medium	Bypass by direct table write
Async queue after commit	No	No	Low until incidents	Lost events after crash
External SIEM only	No	Partial	Ops-heavy	Gap between DB and SIEM

My default for Laravel SaaS: application write in the same transaction for domain actions you control, plus triggers on a small set of high-risk tables (memberships, api_keys, billing_accounts) so console and migration mistakes still leave a trail. Do not trigger-log every updated_at touch on high-churn tables—you will drown in noise and cost.

Laravel write path:

public function updateOrderStatus(Order $order, string $status, User $actor): Order
{
    return DB::transaction(function () use ($order, $status, $actor) {
        $before = $order->only(['status', 'total_cents', 'currency']);

        $order->forceFill(['status' => $status])->save();

        AuditEvent::query()->create([
            'id'            => (string) Str::uuid(),
            'tenant_id'     => $order->tenant_id,
            'actor_user_id' => $actor->id,
            'actor_type'    => 'user',
            'action'        => 'order.status_changed',
            'entity_type'   => 'Order',
            'entity_id'     => $order->id,
            'request_id'    => request()->attributes->get('request_id'),
            'ip_inet'       => request()->ip(),
            'before_state'  => $before,
            'after_state'   => $order->only(['status', 'total_cents', 'currency']),
            'metadata'      => ['channel' => 'admin_ui'],
        ]);

        return $order->fresh();
    });
}

Trigger sketch for a sensitive table (runs even if someone updates via psql):

CREATE OR REPLACE FUNCTION audit_memberships() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  INSERT INTO audit_events (
    tenant_id, actor_type, action, entity_type, entity_id,
    before_state, after_state, metadata
  ) VALUES (
    NEW.tenant_id,
    'system',
    TG_OP || '.membership',
    'Membership',
    NEW.id,
    CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE to_jsonb(OLD) END,
    to_jsonb(NEW),
    jsonb_build_object('table', TG_TABLE_NAME)
  );
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_audit_memberships
AFTER INSERT OR UPDATE ON memberships
FOR EACH ROW EXECUTE FUNCTION audit_memberships();

Revoke UPDATE/DELETE on audit_events from the app role; grant INSERT/SELECT only. The trigger function owner must be a role that can insert, while the app role still cannot mutate history.

AI-generated image.

Privilege model, retention, and cost

REVOKE UPDATE, DELETE ON audit_events FROM app_runtime;
GRANT INSERT, SELECT ON audit_events TO app_runtime;
-- partitions inherit; verify with \dp after each new partition

Retention is a compliance decision expressed as ops:

Detach partitions older than the policy window into cold storage or an archive schema.
Never DELETE FROM audit_events WHERE created_at < … with the app role.
Document who can run detach/drop and require dual control for destruction.

Cost trade-offs: JSONB snapshots grow with column width and churn. Cap logged columns, sample low-risk fields, or store hashes of large text. Indexes on (tenant_id, created_at) and entity lookups are enough for support; avoid indexing every JSONB path “just in case.”

Related reliability themes show up when inventory and billing also need durable facts: see multi-warehouse inventory: a stock ledger that balances and idempotent jobs and exactly-once effects. Zero-downtime schema work that creates partitions safely pairs with expand/contract migrations.

Failure modes I design for explicitly

App role is also table owner. Ownership bypasses REVOKE. Own tables as a migration role; runtime connects as app_runtime.

Correction culture. Support wants to “fix a typo” in an audit row. Refuse. Append audit.correction with a pointer to the prior event id in metadata.

PII in snapshots. Before/after may contain emails or phone numbers. Align retention with privacy policy; redact or tokenize sensitive keys at write time.

Clock skew across replicas. Use DEFAULT now() in Postgres, not Carbon::now() from three app nodes, as the authoritative audit timestamp.

Partition not created for the new month. Inserts fail at month boundary. Automate next-partition creation in migrations or a scheduled job days ahead.

Trigger recursion. Auditing the audit table, or cascading updates that re-fire, can lock the system. Keep triggers narrow and never audit audit_events itself with the same function.

Implementation checklist

[ ] audit_events append-only: app role has INSERT/SELECT only
[ ] Business write and audit INSERT share one transaction (or AFTER trigger)
[ ] tenant_id, actor, action, entity, before/after, created_at present
[ ] Partitions by month (or week at high volume) with ahead-of-time creation
[ ] High-risk tables also covered by triggers
[ ] Corrections are new events, never UPDATE history
[ ] Retention = detach/archive with dual control, not app DELETEs
[ ] Alert when insert error rate spikes (missing partition, privilege drift)
[ ] Support UI reads audit by tenant + entity; no “edit history” button
[ ] Documented server/ops runbook for privilege and partition jobs

Frequently asked questions

Is soft delete enough for an audit trail?

No. Soft delete still allows privileged DELETE, UPDATE of the row, and silent privilege escalation. Append-only privileges plus compensating events are the durable model.

Should every column change create an audit row?

No. Audit security- and money-relevant actions and fields. Logging every updated_at on a high-churn table burns storage and buries signal.

Can I put audit data in a separate database?

You can for analytics copies, but the authoritative insert should share a transaction with the business write when possible. Cross-database dual writes reintroduce lost-event failures.

How do I handle GDPR erasure requests?

Plan erasure as a controlled process: redact PII fields in archived partitions under legal review, or replace payloads with tombstone markers while preserving non-PII action metadata—never a casual DELETE from the app.

Do ORMs make this automatic?

No. Eloquent observers help only for paths that use the model. Raw queries, jobs, and psql bypass observers unless you also use triggers or a shared write service.

What about event sourcing instead of an audit table?

Event sourcing can subsume audit if every state change is an immutable event. Most SaaS teams need a pragmatic audit table first; full event sourcing is a larger domain redesign.

When we take on Laravel and Postgres builds at Loop Origin, immutable audit is one of the first architecture checks after tenancy and backups. Customers and auditors both ask the same question: can anyone rewrite yesterday? Your privileges and schema should make the honest answer “no.”

DEV Community