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

The first question a security reviewer at an enterprise prospect asks isn't about your encryption keys. Not uptime. They ask: "Can I see your audit log?" — and in many cases, it comes before the pricing conversation starts.

B2B SaaS products that skip audit logs early aren't just cutting a compliance corner. They're losing deals.

Why Do Audit Logs Close Enterprise Deals?

Enterprise security teams request audit log access before they ask about pricing, and a missing audit log has ended deals the product would otherwise have won.

The SSOJet April 2026 analysis of enterprise B2B procurement documents one company that cut its sales cycle from four months to six weeks by building and documenting its audit log — not by adding features, not by changing pricing, but by being able to show reviewers "who did what, when" on demand.

Hashorn's 2026 enterprise-readiness guide recommends building audit logs as the second feature after RBAC — before SSO, before SCIM. Every enterprise customer asks for them. Skip them and you fail SOC 2 review. You also can't answer the 11pm question: "Something changed. Who did this?"

This reframing matters more than the implementation detail. You're not building audit logs because compliance said so. You're building them because they close deals.

What Does an Audit Log Schema Actually Need?

A production audit log entry answers six questions: who acted, what they did, what changed, what it changed on, when, and from where.

| Field | Type | Purpose | |---|---|---| | id | UUID | Primary key | | tenant_id | UUID | Multi-tenant isolation — not optional | | actor_id | UUID nullable | User or system identity | | actor_email | text | Readable identity that survives user deletion | | actor_type | enum | user, system, api_key | | action | text | Structured event: subscription.plan_changed | | resource_type | text | subscription, user, appointment | | resource_id | text | The affected row's ID | | changes | jsonb | {"from": "pro", "to": "enterprise"} | | metadata | jsonb | IP address, user agent, request ID | | source | enum | ui, admin_panel, api, background | | created_at | timestamptz | UTC, millisecond precision |

The actor_email field matters more than it appears. When a user gets deleted, their UUID goes cold — orphaned foreign key, no name attached. actor_email keeps a readable trace of everything they did, even after the account is gone. Auditors ask for exactly this.

Yaro Labs makes the case for structured diffs: store before/after state as a JSON diff, not a description. {"from": "pro", "to": "enterprise"} is filterable. "User changed plan" is a log message. The difference shows up when a compliance request lands at 5pm Friday: "Show me every subscription downgrade in March."

One field most schemas omit: source. Distinguishing ui from api from background is the fastest way to answer "was this a user action or a system job?" during an incident reconstruction. Twenty seconds to add. Can save an hour later.

Enforcing Immutability: The Part Everyone Skips

An audit log isn't an audit log if someone can delete the row.

The application role should INSERT and SELECT. Never UPDATE or DELETE. In Postgres:

-- Revoke write access on the app role
REVOKE UPDATE, DELETE ON audit_events FROM app_role;

-- Add RLS for defense-in-depth
ALTER TABLE audit_events ENABLE ROW LEVEL SECURITY;

CREATE POLICY audit_insert_only ON audit_events
  AS RESTRICTIVE FOR ALL
  USING (false) WITH CHECK (true);

CREATE POLICY audit_insert ON audit_events
  FOR INSERT WITH CHECK (true);

The ownership problem is where most teams stop reading. If your application role is also the table owner, REVOKE does nothing — owners bypass permissions. The fix: create a separate audit_writer role that owns the table, grant INSERT to that role only, grant SELECT to the app role, and keep audit_writer credentials out of your application server's environment entirely.

Actually — even that isn't sufficient if your admin panel shares a superuser connection, since superusers bypass RLS by default. The read path and the write path need to be separate roles, full stop.

For products serving regulated healthcare or financial services, AppMaster's guide on tamper-evident PostgreSQL audit trails describes hash chaining as the next level: each row contains a hash computed from its own data plus the previous row's hash, making retroactive tampering detectable because the chain breaks. For most multi-tenant SaaS products, RLS plus a separate owner role is sufficient. Hash chaining is for scenarios where the audit log itself has to be forensically defensible.

On Callidus, the clinic SaaS I built on React and Firebase, the first real test came on a Wednesday evening: a consent form had been modified, and the clinic owner needed to know who. The system answered in under twenty seconds — actor ID, before value, after value, source field showing admin_panel. That separation between app-write and admin-read paths was designed from day one, not added after the fact.

What Query Patterns Cover 90% of Compliance Asks?

Four queries cover the vast majority of compliance asks: account activity timeline, actor history, event-type filter, and affected-entity lookup.

The index design follows directly from those four patterns:

-- "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);

For a B2B SaaS with a few million rows per tenant, those four indexes answer almost every support or audit question under 100ms. The (tenant_id, created_at DESC) index is load-bearing — it's what the customer-facing audit log view runs on.

For text search — when a customer types a term rather than picking from a dropdown — PostgreSQL's pg_trgm extension handles this without Elasticsearch or a separate search service:

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

That covers "find all events matching 'stripe'" without knowing the exact action string in advance. The Supabase Postgres audit implementation uses a BRIN index on the timestamp column rather than a B-tree — because audit tables are append-only and naturally ordered by time, a BRIN index runs hundreds of times smaller than an equivalent B-tree. Worth adopting once your table grows into the tens of millions of rows.

How Long Do You Need to Retain Audit Logs?

Minimum 12 months. Longer if you're pursuing SOC 2 Type II or serving regulated industries.

| Framework | Minimum retention | Notes | |---|---|---| | SOC 2 Type I | 12 months | Auditors sample 6–12 months back | | SOC 2 Type II | 24–36 months | Pattern evidence over time | | PCI DSS | 12 months | Last 3 months must be immediately queryable | | HIPAA | 6 years | Access log retention | | GDPR | Proportional | Must be documented |

The tiered storage architecture that works in production:

1. 0–90 days: hot Postgres table, fully indexed, queryable directly by your app.
2. 90 days – 2 years: partitioned table or archival schema. Partition by month — audit_events_2026_06 rolls off cleanly, keeps the hot table lean.
3. 2+ years: scheduled export to S3 or cold object storage; retain a metadata pointer in Postgres for lookup.

One constraint that catches teams late: never let application code run DELETE on audit records. Retention rolloff should be a scheduled database job or admin-only migration — not a query accessible from the app server. An audit trail with an application-layer delete is not an audit trail.

The 10 Events Enterprise Buyers Actually Check

Not all events carry equal weight to a security reviewer. The SSOJet April 2026 enterprise procurement analysis identifies the 10 events IT teams open first:

1. Login success — authentication method, IP address, MFA status
2. Login failure — reason, attempt count, lockout state
3. SSO configuration changes — who modified, exactly what changed
4. Admin role changes — who elevated whom, with timestamp. Enterprise security treats this as the highest escalation-risk event in your log; a missing record here fails the review regardless of what else you capture.
5. SCIM provisioning — user creation, update, deprovisioning
6. API key creation and rotation — scope, expiry, creator identity
7. Data exports — record count, format, destination, actor
8. User impersonation — agent identity, ticket linkage, session duration
9. Session revocation — reason and actor
10. MFA enrollment and reset — approval chain, ticket reference

Most early-stage products capture items 1 and 2 and feel covered. Security reviewers open items 3, 4, and 7 first. Those are the highest escalation-risk events. A product that logs login events but misses admin role changes and data exports fails the review even when the coverage looks substantial from the inside.

You don't need all ten from day one. What you do need: a read API. Hashorn's guide identifies this as the most commonly forgotten audit log requirement — customers want to query the audit log, not just generate it. A JSON export endpoint or a webhook stream to their SIEM is often the actual ask, not the ops panel you built for your own support team.

Getting to Production in Four to Six Weeks

A compliant audit system for a product of moderate complexity takes four to six weeks to build properly. Not four days, not a quarter. The trap is treating it as a logging problem and shipping structured app logs to Datadog. That's ops telemetry. The audit trail is a customer-facing product feature.

Build order that works:

1. Create the audit_events table with the schema above. Set the app role to INSERT + SELECT only, separate owner role.
2. Write a single logAuditEvent(tenantId, actorId, action, resourceType, resourceId, changes) helper that every mutation path calls — no direct table writes in business logic.
3. Add the four indexes.
4. Expose a read endpoint: tenant-scoped, filterable by actor, action, resource type, and date range. This is the feature your enterprise buyers actually use.
5. Ship a customer-facing view in your settings area.

If you're building on React + Supabase with RLS, the audit table needs its own RLS policy scoping reads on tenant ID — keep it separate from your business data policies, but follow the same pattern. The (tenant_id, created_at DESC) index handles the performance; RLS handles the isolation.

---

Which of the 10 event types is your product currently not capturing? That gap is probably the one the next enterprise prospect will ask about first.