What is an audit log?
An audit log (or audit trail) is an immutable, chronologically ordered record of system activities, transactions, and user interactions. It provides documentary evidence of the sequence of activities that have affected a specific operation, procedure, or event.
Unlike ephemeral application debugging logs, an audit log answers definitive forensic questions for security teams, compliance auditors, and system administrators: Who performed the action? What resource was modified? Exactly when did it happen? From which IP address or interface? And what was the outcome?
Audit log vs application log
Software systems generate multiple classes of telemetry, and conflating operational application logs with security audit logs is a common engineering mistake:
- Application Logs (Telemetry & Debugging): Designed for software engineers to diagnose stack traces, unhandled exceptions, latency, and memory spikes. They often contain high volumes of transient data and are typically retained for only 14 to 30 days before being purged.
- Audit Logs (Security & Compliance): Designed for compliance officers, security auditors, and legal teams. They record business-critical state mutations (account creation, privilege escalation, invoice deletion, data export) and require write-once, append-only immutability with strict multi-year retention guarantees.
What to record (who, what, when, where, result)
A compliant audit logging architecture captures the foundational "5 Ws" of forensic accounting:
- When (occurred_at): A high-precision timestamp recorded strictly in UTC (e.g.
TIMESTAMPTZor ISO-8601). Never log local server timezones. - Who (actor_type & actor_id): The authenticated identity responsible for the action. Distinguish clearly between interactive end users, administrative operators, automated cron workers, and third-party API keys.
- What (action & entity): The standardized mutation verb (e.g.
create,update,delete,permission_change) and the affected entity domain (e.g.booking,invoice,user). - Where (ip_address & request_id): The originator's network location and the unique distributed tracing request ID to correlate log events across microservices.
- Result (outcome & failure_reason): Whether the requested action succeeded or was rejected (e.g. 403 Forbidden, invalid authorization token, or business logic constraint violation).
Designing the table: relational schema & indexes
Audit log tables experience extraordinarily heavy write traffic combined with highly targeted lookup queries during forensic investigations. Efficient indexing is mandatory:
(occurred_at): Essential for time-slice filtering during quarterly compliance audits and incident investigation timelines.(entity_type, entity_id): Enables sub-millisecond retrieval of the complete historical change log for a single customer, invoice, or booking record.(actor_id, occurred_at): Powers user activity dashboards and security investigations into compromised credentials.(tenant_id, occurred_at): Critical for B2B SaaS multi-tenant isolation, ensuring customer data export requests do not scan global table partitions.
Making it append-only
The integrity of an audit trail depends on proof that logs cannot be altered or deleted after creation—even by malicious actors who gain access to application database credentials:
- PostgreSQL / MySQL Role Revocation: Execute
REVOKE UPDATE, DELETE ON audit_log FROM app_role;. The application user should possess onlySELECTandINSERTprivileges. - SQLite / Cloudflare D1 Triggers: In serverless and embedded environments, implement
BEFORE UPDATEandBEFORE DELETEtriggers that invokeRAISE(ABORT, 'audit_log is append-only'). - Cryptographic Tamper-Evidence: Storing a SHA-256 hash of the row content combined with the previous record's hash creates an immutable hash chain, making retroactive tampering mathematically detectable.
Storing before/after changes safely
Logging state changes using JSON columns (JSONB in Postgres, JSON in MySQL, or TEXT in SQLite) provides complete auditability. However, engineers must implement strict automated payload sanitization before writing to the database:
- Never store plaintext passwords, authentication tokens, salt hashes, credit card CVVs, or unencrypted government identifiers (SSN).
- Implement automated masking middleware that strips keys matching your sensitive field denylist prior to JSON serialization.
Retention basics and legal advisory note
Audit log retention schedules should reflect statutory mandates rather than arbitrary storage limits. For example, tax and accounting frameworks frequently require 7 years of financial transaction history, while privacy regulations (such as GDPR) mandate that IP addresses and personal identifiers in access logs be truncated or anonymized after operational necessity expires.
Frequently Asked Questions
At a minimum, every audit log must capture the "5 Ws": occurred_at (UTC timestamp), actor_type & actor_id (who performed the action), action (what took place), entity_type & entity_id (what was affected), outcome (success or failure), and where relevant, ip_address, request_id, and delta changes (before/after states).
Retention requirements depend heavily on industry regulations, commercial agreements, and regional data protection laws. Standard commercial practices often retain operational logs for 90 to 365 days, while financial, tax, and healthcare governance mandates can require 5 to 7 years of immutable archiving.
For early-stage startups and small systems, storing audit logs in the primary database with strict append-only role permissions is practical and cost-effective. However, high-scale enterprises typically stream audit logs to an isolated, dedicated database, write-once object store (S3 Object Lock), or external SIEM to prevent tampering by database administrators.
Enforce append-only constraints at the database level by revoking UPDATE and DELETE permissions from application user roles. In SQLite and Cloudflare D1, implement BEFORE UPDATE and BEFORE DELETE triggers that abort modifications. For advanced tamper evidence, incorporate cryptographic hash chaining (prev_hash and row_hash).
Generally, routine read actions are recorded in web server access logs rather than the database audit log to avoid overwhelming storage capacity. However, sensitive read actions—such as viewing unmasked credit card details, medical records, or bulk exporting customer databases—must be explicitly recorded in the audit trail.
Yes. When selecting the SQLite (D1) target, the generator outputs SQLite-compatible DDL using ISO-8601 UTC TEXT timestamps, TEXT-encoded JSON columns, and database triggers that enforce strict append-only immutability within Cloudflare D1 serverless environments.