Audit Log System Design: Architecture, Schema, and Retention

An audit log records who attempted an action, what it affected, and whether it succeeded. A useful design also answers a harder question: can you trust that the record is complete and unchanged?

For a typical SaaS application, start with an audit event written in the same database transaction as the business change. Publish it through a transactional outbox, make consumers idempotent, and keep the searchable copy separate from an independently protected archive. Choose retention and failure behavior before choosing a storage product.

Audit log architecture at a glance#

Authenticated request
        |
        v
Business database transaction
  - update the business record
  - insert a bounded audit event into an outbox
        |
        v
Outbox publisher -- retry --> queue
                                |
                                v
                       Idempotent audit writer
                         /                \
                        v                  v
                Searchable audit DB   Protected archive
                        |
                        v
                Authorized query API

The application acknowledges success after the business change and outbox event commit together. Publishing, indexing, and archiving can happen asynchronously. A stopped publisher delays visibility; it does not silently discard a committed event.

This is a proposed architecture, not a guarantee of completeness. An action that bypasses the instrumented application can still bypass the outbox. Include administrator changes, background jobs, and direct database access in the threat model.

What an audit event should contain#

Use stable, opaque identifiers and an allowlist of fields. Keep debugging traces and arbitrary request bodies out of the audit record.

For an update, record which approved fields changed. Store old and new values only when there is a defined need and those values are safe to retain. Full object snapshots can duplicate secrets, personal data, and deleted information into a much longer-lived store.

Never log passwords, API keys, authorization headers, or session tokens. Consider whether an IP address, email address, or document excerpt is necessary before collecting it. Validate event size and escape untrusted text when displaying records. The OWASP Logging Cheat Sheet covers sensitive data exclusions, injection risks, access controls, and testing logging failures.

Capture the business change atomically#

A database write followed by a separate queue publish has a gap: the write can succeed and the publish can fail. Reversing the order can describe a change that never committed.

A transactional outbox closes that gap for changes in the same database:

BEGIN;

UPDATE invoices
SET status = 'approved'
WHERE tenant_id = :tenant_id AND id = :invoice_id;

-- Insert only after verifying the intended row changed.
INSERT INTO audit_outbox (tenant_id, event_id, payload)
VALUES (:tenant_id, :event_id, :allowlisted_audit_event);

COMMIT;

The publisher retries undelivered outbox rows. It marks a row delivered only after a broker acknowledgement. A crash between publishing and marking can produce a duplicate, so the audit writer must deduplicate using the original event ID. If the same ID arrives with different content, alert rather than quietly replacing the first record.

Denied attempts need their own capture path: an audit row inside a rolled-back business transaction disappears too. Record the denial separately, without claiming that the protected operation succeeded. For external side effects such as sending an export to another service, distinguish requested, accepted, and confirmed delivery events.

A searchable PostgreSQL schema#

This small schema is a starting point for the queryable copy. It does not make the database immutable.

CREATE TABLE audit_events (
  tenant_id text NOT NULL,
  event_id text NOT NULL,
  actor_id text,
  actor_type text NOT NULL,
  action text NOT NULL,
  resource_type text NOT NULL,
  resource_id text NOT NULL,
  outcome text NOT NULL CHECK (outcome IN ('success', 'denied', 'failed')),
  occurred_at timestamptz NOT NULL,
  recorded_at timestamptz NOT NULL DEFAULT now(),
  schema_version integer NOT NULL,
  metadata jsonb NOT NULL DEFAULT '{}',
  PRIMARY KEY (tenant_id, event_id)
);

CREATE INDEX audit_actor_time
  ON audit_events (tenant_id, actor_id, occurred_at DESC, event_id);
CREATE INDEX audit_resource_time
  ON audit_events (tenant_id, resource_type, resource_id, occurred_at DESC, event_id);

Give the application writer insert permission and the query service read permission. Use a separate, reviewed retention process for deletion. Resolve the tenant from the authenticated caller rather than trusting a supplied tenant ID. Require authorization for exports as well as reads, bound the time range, and paginate with a stable tie-breaker.

Row-level security adds isolation, but it is not tamper protection. PostgreSQL documents that superusers and roles with BYPASSRLS bypass policies, and table owners normally do too. An application role that cannot update records says nothing about a database administrator's ability to change them. See the PostgreSQL row-security documentation.

Append-only, tamper-evident, and immutable are different#

Append-only application access prevents ordinary application code from editing history. It depends on database roles, ownership, migrations, and administrative access.

Tamper-evident records help detect changes. A hash chain can link canonical event bytes to the previous hash, but a writer who can rewrite the events and all hashes can create a new consistent chain. Preserve signed checkpoints or digests in a separately controlled system. Define ordering per stream or partition; timestamps from several services are not a global sequence.

Protected retention restricts deletion of an archived object version for a stated period. S3 Object Lock provides retention modes and legal holds. Governance mode allows authorized bypass; compliance mode has stronger restrictions during retention. The protection applies to object versions, so test versioning, archive completeness, access separation, and retrieval. Read the S3 Object Lock documentation before selecting a mode.

Search indexes are useful for investigations, but rebuilding or deleting an index should not destroy the authoritative archive. Encrypt sensitive records, restrict the archive's administrators, and audit access to the audit system itself.

Retention is a policy, not a magic number#

Define retention by event category, contractual obligation, applicable requirement, and investigation needs. Include legal holds, deletion authority, export access, and a documented recovery process. Verify that retention covers backups, replicas, and derived search indexes.

Avoid universal claims such as “SOC 2 requires every log for one year” or “HIPAA requires all audit logs for six years.” HHS describes audit controls and six-year retention for required Security Rule documentation; that is not a blanket six-year rule for every raw event. The applicable policy needs to be established for your system. The HHS Security Rule summary explains that distinction.

Do not start a new design around Amazon QLDB. AWS ended its support on July 31, 2025, as noted in the QLDB documentation.

Design the failure policy explicitly#

For a high-risk operation, an unavailable durable audit write may need to stop the operation. For a lower-risk action, the approved policy may allow a bounded degraded mode with an alert. “Always log asynchronously” is not enough to decide this.

Estimate capacity from event rate and payload size. For example, 1,000 events per second at 700 bytes each is about 60 GB per day before indexes, replicas, or archive overhead. This is a sizing example, not a throughput benchmark. Test the actual schema, peak burst, tenant distribution, query mix, and retention jobs.

Review checklist#

• Can each committed business operation be reconciled to an event?
• Do retries keep the same ID, and can conflicting duplicates be detected?
• Are every query and export tenant-scoped and authorized?
• Can an application writer, database owner, or archive administrator alter history?
• Does the payload allowlist exclude secrets and unnecessary personal data?
• Are denials, external delivery, and rolled-back operations represented accurately?
• Can the team restore an archive and verify a checkpoint after an outage?
• Are retention, legal holds, and deletion authority documented and tested?

A good audit-log design makes these boundaries visible. The diagram helps explain the pipeline; the failure tests and access controls establish whether it works.