Audit logs are the boring infrastructure that saves your company during a breach, a compliance audit, or when a key table starts leaking data and nobody knows why. Most apps treat them as an afterthought: sprinkle some logging middleware, hope the data is consistent, pray nobody asks hard questions. Velocity X does the opposite. Audit logs are a first-class citizen. Every INSERT, UPDATE, DELETE on sensitive tables automatically writes to an immutable append-only table via Postgres triggers. No application code needed. No exceptions. The database enforces immutability—you literally cannot UPDATE or DELETE audit records without superuser access.
Here's why it matters: when compliance calls and asks "who changed the refund policy on June 10 at 2:15pm?", you query your audit_log table and get a name, timestamp, old value, new value, and IP address. 30 seconds. When an insider accidentally exports customer data, you have the exact moment, the actor, the export size. When you need to prove to a court that you didn't tamper with forensic evidence, you show them the RLS policy preventing even admins from touching the log.
Why Immutable > Soft-Delete for Compliance
Soft-delete is a pattern where you never actually delete rows; instead you flag them with a deleted_at timestamp. It's good for data recovery, bad for forensics. Someone can still UPDATE a soft-deleted record. An admin can run a migration that bulk-updates the deleted_at column. The log becomes plastic—useful for reconstruction, worthless for proof.
Immutability is the opposite: the table has zero UPDATE/DELETE permissions, even for authenticated users or service roles. Inserts only. The database itself enforces the contract. In a legal dispute or compliance investigation, that's bulletproof. You're not saying "we follow a soft-delete policy"—you're showing bytecode-level enforcement. Auditors and lawyers love that.
Architecture: Trigger Function + INSERT-Only RLS
The core is simple: a trigger function that fires AFTER INSERT/UPDATE/DELETE on every sensitive table, and an RLS policy that allows SELECT and INSERT but forbids UPDATE/DELETE.
{`-- Immutable audit log table
create table audit_log (
id bigserial primary key,
actor_id uuid not null, -- who did it
action text not null, -- 'insert', 'update', 'delete'
table_name text not null,
row_id uuid not null, -- which row
before_values jsonb, -- old state (UPDATE/DELETE only)
after_values jsonb, -- new state (INSERT/UPDATE only)
ip_address inet,
user_agent text,
created_at timestamp default now()
);
-- Never allow updates or deletes
create policy audit_immutable on audit_log
for select using (true)
for insert with check (true);
alter table audit_log enable row level security;
-- Remove all update/delete permissions
revoke update, delete on audit_log from authenticated, anon, service_role;`}Now attach the trigger. Every time a row changes on the deals table, the function fires and logs the before/after snapshot as JSONB:
{`create or replace function audit_trigger()
returns trigger as $$
declare
actor_id uuid := (auth.jwt() ->> 'sub')::uuid;
ip text := current_setting('request.headers', true)::json->>'cf-connecting-ip';
begin
insert into audit_log (actor_id, action, table_name, row_id, before_values, after_values, ip_address)
values (
actor_id,
tg_op::text,
tg_table_name,
case when tg_op = 'DELETE' then old.id else new.id end,
case when tg_op = 'DELETE' or tg_op = 'UPDATE' then to_jsonb(old) else null end,
case when tg_op = 'INSERT' or tg_op = 'UPDATE' then to_jsonb(new) else null end,
ip::inet
);
return coalesce(new, old);
end;
$$ language plpgsql security definer;
-- Attach to all sensitive tables
create trigger audit_deals after insert, update, delete on deals
for each row execute function audit_trigger();
create trigger audit_profiles after insert, update, delete on profiles
for each row execute function audit_trigger();
create trigger audit_settings after insert, update, delete on settings
for each row execute function audit_trigger();`}That's it. Every change to deals, profiles, or settings is logged. The log itself is locked down. You've built a forensic trail with 40 lines of SQL.
Querying the Audit Trail
The real power is in the queries. Find all changes made by a user on a specific date:
{`select
created_at,
action,
table_name,
row_id,
before_values -> 'status' as old_status,
after_values -> 'status' as new_status
from audit_log
where actor_id = 'user-uuid'
and created_at > '2026-06-10'::date
and created_at < '2026-06-11'::date
order by created_at;`}Find which user deleted a specific row, and what was in it:
{`select
actor_id,
created_at,
before_values
from audit_log
where table_name = 'deals' and row_id = 'deal-uuid' and action = 'delete';`}Spot who's exporting large volumes of customer data:
{`select
actor_id,
created_at,
count(*) as rows_accessed
from audit_log
where table_name = 'customers' and action = 'select'
group by actor_id, date_trunc('hour', created_at)
having count(*) > 1000
order by created_at desc;`}Frequently Asked Questions
Won't this log everything and bloat the database?
Yes. Audit tables grow fast. Velocity X partitions audit_log by month and archives or deletes partitions older than 2 years. A busy SaaS with 10M queries/day generates ~10M audit rows/day. At 1KB per row, that's 10GB/day. Partition by month, and each partition is ~300GB—manageable with cold storage archival. The cost is worth it.
Can I log sensitive fields without storing them?
Yes. Before inserting, mask or hash sensitive fields in the audit record. E.g., instead of logging the full SSN, log the last 4 digits. In the trigger, do:
{`new.ssn := '****' || right(new.ssn, 4);
after_values := jsonb_set(to_jsonb(new), '{ssn}', '"[REDACTED]"'::jsonb);`}You still have the audit trail; you just don't store plaintext PII in the log.
What if an attacker gains superuser access?
They can run alter policy audit_immutable on audit_log disable; and then update the log. But disabling RLS creates an audit trail itself—Postgres logs all DDL. You'll see when the policy was disabled. Not a silver bullet, but another layer.
Can I export the audit log for compliance?
Absolutely. select * from audit_log where created_at between X and Y order by created_at; → JSON → give to your auditor. HIPAA, SOC 2, GDPR audits all ask for this. It's your smoking gun.
Do I need a separate audit service?
No. Postgres handles it. Services like LogRocket or Datadog are great for frontend telemetry; for backend forensics, a trigger-based log is simpler, faster, and less operational overhead. It's baked into your database schema.
How do I know the audit table itself isn't corrupted?
You don't, completely. But if an attacker has enough access to corrupt audit_log, they likely have access to corrupt anything. Audit logs buy you protection against accidental changes, insider mistakes, and mid-layer compromises—not against full database compromise. If you need full forensic immutability against root, use write-once cloud storage (AWS S3 Object Lock, Azure Immutable Blobs).
The Bottom Line
Immutable audit logs shift the security model. Instead of trusting an application to log correctly, you shift the burden to the database, which is already transactional and reliable. Postgres triggers + RLS give you forensic-grade trails with zero application code. When compliance asks "who changed what and when?", you don't scramble. You query. You export. You move on. That's the Velocity X way.