Audit Logs for B2B SaaS: Schema, Query Patterns, Compliance

An enterprise prospect's security reviewer has a questionnaire open, and one line on it asks whether customers can see who did what inside your product. Before you build anything, write down the six things an audit entry must answer: who acted, what they did, what changed, what it changed on, when, and from where. If your current logs can't answer all six for a single admin action, you have found your first task.

B2B SaaS teams often treat audit logs as a compliance chore. I'd treat them as a product feature with two jobs: give your customers' admins an answer to "who changed this?", and give you the record you need when something goes wrong.

Why do B2B SaaS buyers ask for audit logs?

Buyers ask for audit logs because the log is the record of who did what after something goes wrong, and regulated buyers may be required to keep audit controls on systems that hold sensitive data.

Two primary sources frame the stakes. IBM's Cost of a Data Breach Report 2026 puts the global average cost of a breach at $4.99 million, a 12% increase over the prior year and a record high. A breach investigation starts with the question an audit log answers. And for healthcare customers in the US, the HIPAA Security Rule at 45 CFR 164.312(b) requires covered entities to "implement hardware, software, and/or procedural mechanisms that record and examine activity in information systems that contain or use electronic protected health information."

This suggests a practical reading: if you sell into regulated customers, they have their own reasons to ask for the capability, and a missing audit log is a gap you'll be asked to explain. I can't cite a primary study on how early in the sales cycle reviewers ask, so I won't claim a timing.

What should an audit log schema include?

A production audit log schema records who acted, what they did, what changed, which resource it changed, when it happened and from where, with tenant isolation built into every row.

Why store actor_email next to actor_id?

When a user is deleted, their UUID goes cold: an orphaned reference with no name attached. actor_email keeps a readable trace of what they did after the account is gone. If you handle personal data, decide how long that copy may live, because it is personal data too (see the retention section below).

Why store structured diffs instead of descriptions?

{"from": "pro", "to": "enterprise"} is filterable, while "user changed plan" is a log line. The difference shows up when a compliance request asks for every subscription downgrade in a given month. Store before and after values as structured data.

Why add a source field?

Distinguishing ui from api from background is the fastest way to answer "was this a user action or a system job?" while reconstructing an incident. It takes minutes to add and is painful to backfill.

How do you make a PostgreSQL audit trail immutable?

To make a PostgreSQL audit trail immutable, give the application role INSERT and SELECT only, keep table ownership with a separate role the application never uses, and add row-level security so each tenant reads only its own rows.

The ownership detail is where many setups fail. The PostgreSQL docs on privileges state that "the right to modify or destroy an object is inherent in being the object's owner, and cannot be granted or revoked in itself," and that an owner can revoke their own ordinary privileges but can always re-grant them. If your application connects as the table owner, no REVOKE protects the table. Separate the roles.

-- A separate owner role holds the table; the app never connects as it
CREATE ROLE audit_owner NOLOGIN;
ALTER TABLE audit_events OWNER TO audit_owner;

REVOKE ALL ON audit_events FROM PUBLIC;
GRANT INSERT, SELECT ON audit_events TO app_role;

-- Row-level security scopes reads and writes to the current tenant
ALTER TABLE audit_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_events FORCE ROW LEVEL SECURITY;

CREATE POLICY audit_select_tenant ON audit_events
  FOR SELECT TO app_role
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY audit_insert_tenant ON audit_events
  FOR INSERT TO app_role
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

Because app_role has no UPDATE or DELETE privilege and no policy for them, those statements fail. The script runs on PostgreSQL 16, where UPDATE and DELETE return permission errors and an INSERT for another tenant is rejected by the policy. Row-level security has its own bypass rules. The PostgreSQL docs on row security policies say superusers and roles with the BYPASSRLS attribute always bypass it, and table owners normally do too unless the table uses FORCE ROW LEVEL SECURITY. So your admin panel and migration tooling must not share a superuser connection with the read path. Adapt the tenant predicate to however your stack identifies the tenant; the React + Supabase RLS stack page covers one approach.

Some teams also need tamper evidence, meaning a way to prove rows weren't altered by someone with privileged access. Hash chaining is one technique: each row stores a hash of its own data plus the previous row's hash, so a retroactive edit breaks the chain. For most multi-tenant SaaS products, separate roles plus RLS is a reasonable starting point, and hash chaining is worth the extra work when the log itself must stand up to forensic scrutiny. That is my judgment, not a standard requirement.

Callidus, the clinic SaaS I built on React, Firebase and Stripe Connect, runs on Firestore rather than Postgres, so its audit logging is not this SQL. Its case study records audit logging with three retention tiers: 90 days for routine events, one year for financial events and two years for GDPR-sensitive ones, with PATIENT_DATA_ERASED events kept without expiry.

Which queries, indexes and retention rules does an audit log need?

Four queries cover most compliance asks: the workspace timeline, an actor's history, an entity's history and event-type sampling. Four matching indexes serve them, and retention is set by the rules that apply to your customers.

What are the four core queries and their indexes?

-- "Show me what happened in this workspace"
CREATE INDEX ON audit_events (tenant_id, created_at DESC);

-- "What did this user do?"
CREATE INDEX ON audit_events (actor_id, created_at DESC);

-- "Who changed this subscription?"
CREATE INDEX ON audit_events (resource_type, resource_id, created_at DESC);

-- Compliance sampling by event type
CREATE INDEX ON audit_events (action, created_at DESC);

The (tenant_id, created_at DESC) index backs the customer-facing audit view, so it is the one to get right first. I haven't benchmarked these indexes against a specific row count, so measure them with EXPLAIN ANALYZE on your own data instead of trusting a latency figure from a blog.

For text search, when a customer types a term instead of picking from a dropdown, the PostgreSQL pg_trgm module provides GIN operator classes that support trigram-based searches for LIKE and ILIKE.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ON audit_events USING gin (action gin_trgm_ops);

When the table grows very large, consider a BRIN index on created_at. The PostgreSQL BRIN documentation says BRIN is designed for very large tables whose columns correlate with physical location, and that such an index is very small. An append-only audit table usually fits that pattern, though you should confirm the correlation on your own data.

How long should you retain audit logs?

Retain audit logs for as long as your strictest applicable rule requires and no longer than you can justify. The table lists what I verified; I couldn't confirm PCI DSS or SOC 2 retention numbers on a primary page this run, so they are left out. Check your framework's current text and ask your auditor.

A storage layout that works: keep recent events in a hot, fully indexed table, move older events to monthly partitions, and export the oldest to cold object storage with a pointer row in Postgres. Monthly partitions help retention because the PostgreSQL docs on partitioning say dropping or detaching a partition is far faster than a bulk operation and avoids the VACUUM overhead of a bulk DELETE.

Never let application code run DELETE on audit records. Run retention roll-off as a scheduled database job or an admin-only migration. An audit trail with an application-layer delete is not an audit trail.

Which ten events should you log first?

This list is my recommendation, built from what a reviewer would want to reconstruct after an incident, not a published standard:

1. Login success, with method, IP address and MFA status.
2. Login failure, with reason and lockout state.
3. SSO configuration changes, including what changed.
4. Admin role changes: who granted what to whom, and when.
5. SCIM provisioning: user creation, update and deprovisioning.
6. API key creation and rotation, with scope, expiry and creator.
7. Data exports: record count, format and actor.
8. User impersonation: agent identity and session duration.
9. Session revocation, with reason and actor.
10. MFA enrollment and reset.

Many early products capture items 1 and 2 and feel covered. Items 3, 4 and 7 are the ones I'd expect a careful reviewer to probe, because they change who can access data. You don't need all ten on day one, but you do need a read path: customers want to query the log, so plan a tenant-scoped API or a JSON export, and treat a stream to their SIEM as a later step.

In what order should you build it?

1. Create the audit_events table, the separate owner role and the INSERT and SELECT grants.
2. Write one logAuditEvent(tenantId, actorId, action, resourceType, resourceId, changes) helper that every mutation path calls, with no direct table writes in business logic.
3. Add the four indexes.
4. Expose a tenant-scoped read endpoint filterable by actor, action, resource type and date range.
5. Ship a customer-facing view in your settings area.

Shipping structured application logs to an observability tool is ops telemetry. The audit trail is a customer-facing feature, and the read endpoint is what your enterprise buyers will use.

Which of the ten events is your product not capturing today? Pick the one that changes who can see data, and add it this week.

---

Part of the SaaS Security and Compliance: SOC 2, GDPR, and Audit Trails guide.