Audit Log Design for Monoliths: Who Changed What Across Modules

Audit log, change history, or entity versioning: what you actually need

When a team says it needs "audit," it usually means one of three different things. Each answers a different question and costs a different amount to build.

An audit log records actions: who did what, when, through which entry point, and ideally why. An example is "Support agent 42 refunded order 9917, reason: duplicate charge." Compliance reviewers and security teams rely on it because it ties a change to an accountable person.

A change history records field-level before and after values: status went from pending to refunded. Support engineers use it to debug "how did this record end up like this?" Finance and operations teams use it to settle disputes about what a customer or colleague actually changed.

Entity versioning keeps complete snapshots, so you can rebuild a record as it was at any past moment. You need it when the past state itself matters, such as pricing in effect on the invoice date or the policy terms a customer accepted.

Many systems need the first two and only a few entities need the third. Decide this per entity, not for the whole system.

A full audit system can be overkill. Low-risk internal tools, or data with no accountability or regulatory requirement, may need only structured application logs that include the actor and the entity ID. Those logs can be rotated away without anyone missing them.

How application-level auditing works in a monolith

When audit data does carry accountability, the design starts at the request edge. Middleware, an RPC interceptor, or a job runner's entry point resolves the actor once. The actor can be an authenticated user, a service account, or a scheduled job. The edge also assigns a correlation ID and stores both in a request-scoped context object. Every module receives that context explicitly or through a framework-scoped holder. No module re-derives identity on its own.

This has to happen in the application because the database cannot see the actor. Postgres sees a pooled connection role, not the person who clicked "Approve". It sees an UPDATE on invoices, not the intent "refund issued after dispute". It also cannot know that the billing, inventory, and notification changes in this transaction belong to one business operation. Only the code handling the request has the actor, the intent, and the cross-module scope.

Each module writes its audit rows to a shared change history table inside the same database transaction as the business change. This placement is what makes the log trustworthy. If the transaction rolls back, the audit row rolls back with it, so the log never records a change that didn't happen. If the transaction commits, the audit row commits too, so no change is missing from the log.

Postgres triggers sit underneath as a backstop. They catch writes that bypass the application path, such as migrations, manual fixes, or a module that forgot to record its change.

Publishing audit events to a queue after commit breaks this guarantee. A crash between the commit and the publish loses the record. A retry after an unacknowledged publish duplicates it. To close that gap, write the event to an outbox table in the same transaction, and have a separate process relay it from there.

Copy            HTTP request / RPC call / scheduled job
                              |
                              v
+------------------------------------------------------------+
| Request edge: middleware / RPC interceptor / job entry     |
| resolve actor once, assign correlation ID                  |
+------------------------------------------------------------+
                              |  context {actor, correlation_id}
      +-----------------------+---------------------+
      v                       v                     v
+-----------+          +-------------+      +----------------+
|  Billing  |          |  Inventory  |      |  Notification  |
|  module   |          |   module    |      |     module     |
+-----------+          +-------------+      +----------------+
      |                       |                     |  business write +
      v                       v                     v  audit row, same tx
+------------------------------------------------------------+
| Postgres: one transaction -- BEGIN ... COMMIT or ROLLBACK  |
| +----------------+    +----------------+    +----------+   |
| | business       |    | change_history |    | outbox   |   |
| | tables         |    | (shared audit) |    | (events) |   |
| +----------------+    +----------------+    +----------+   |
|          +-- trigger (backstop) --^              |         |
|   catches migrations + manual fixes              |         |
+--------------------------------------------------|---------+
                                                   v
                                         outbox relay --> queue

The request edge resolves the actor and correlation ID once, modules write business changes and audit rows in one Postgres transaction, triggers catch writes that bypass the application, and an outbox relay is the only path to a queue.

Designing the change history table

Whichever delivery path you choose, the row it carries needs a fixed shape. The shape below is one table, audit_event, whose columns answer the questions investigators actually ask:

• event_id (UUID): lets consumers deduplicate retried publishes.
• occurred_at (timestamptz): the time of the change.
• actor_type and actor_id: actor_type is constrained to user, service_account, or system_job. A nightly reconciliation job should never appear as a NULL user.
• on_behalf_of: records the impersonated user when support staff act as a customer, so both identities survive.
• module, entity_type, entity_id: locate the record. Store entity_id as text because modules disagree on key types.
• action: a domain verb such as invoice.approved, not the generic UPDATE.
• before and after (JSONB).
• operation_id, reason, source: tie rows together and explain the change.
• schema_version: lets readers interpret payloads whose shape has changed over time.

The operation_id column turns scattered rows into one logical action. Approving an invoice updates the invoice, inserts ledger lines, and queues a notification. All three rows share one operation_id, minted when the request enters the system. Filtering on that value reconstructs the whole approval.

Full snapshots cost more storage, but each row can be read on its own. Diffs are smaller, but rebuilding a past state means replaying the history in order. A practical compromise is to store full after snapshots for each entity and compute diffs at read time.

Start with one shared table. Split it only when retention or volume differs sharply between modules.

Copy-- PostgreSQL 13+ (gen_random_uuid() is built in from 13)
CREATE TABLE audit_event (
  event_id       uuid PRIMARY KEY,                  -- set by producer so retries dedupe
  occurred_at    timestamptz NOT NULL DEFAULT now(),
  actor_type     text NOT NULL CHECK (actor_type IN ('user', 'service_account', 'system_job')),
  actor_id       text NOT NULL,                     -- jobs get an id too, never NULL
  on_behalf_of   text,                              -- impersonated user, if any
  module         text NOT NULL,                     -- bounded context, e.g. 'billing'
  entity_type    text NOT NULL,
  entity_id      text NOT NULL,                     -- text: modules use different key types
  action         text NOT NULL,                     -- domain verb, not 'UPDATE'
  before         jsonb,                             -- NULL on create
  after          jsonb,                             -- full snapshot; diff computed at read time
  operation_id   uuid NOT NULL,                     -- minted once per incoming request
  reason         text,
  source         text NOT NULL,                     -- 'web', 'api', 'cron', ...
  schema_version smallint NOT NULL
);
CREATE INDEX audit_event_operation_idx ON audit_event (operation_id);
CREATE INDEX audit_event_entity_idx ON audit_event (entity_type, entity_id, occurred_at);

-- 'Approve invoice' touches three modules; every row carries the same operation_id.
INSERT INTO audit_event (event_id, actor_type, actor_id, module, entity_type, entity_id,
                         action, before, after, operation_id, reason, source, schema_version)
VALUES
  (gen_random_uuid(), 'user', 'u-1042', 'billing', 'invoice', 'INV-2024-0117', 'invoice.approved',
   '{"status": "pending"}', '{"status": "approved", "total": "1250.00"}',
   '7c9e6679-7425-40de-944b-e07fc1f90ae7', 'Matches PO-889', 'web', 1),
  (gen_random_uuid(), 'user', 'u-1042', 'ledger', 'ledger_line', '55031', 'ledger_line.posted',
   NULL, '{"account": "2000", "credit": "1250.00"}',
   '7c9e6679-7425-40de-944b-e07fc1f90ae7', NULL, 'web', 1),
  (gen_random_uuid(), 'user', 'u-1042', 'notifications', 'notification', 'n-88213', 'notification.queued',
   NULL, '{"template": "invoice_approved", "to": "ap@example.com"}',
   '7c9e6679-7425-40de-944b-e07fc1f90ae7', NULL, 'web', 1);

-- Reconstruct the whole logical action with one filter.
SELECT occurred_at, module, entity_type, entity_id, action
FROM audit_event
WHERE operation_id = '7c9e6679-7425-40de-944b-e07fc1f90ae7'
ORDER BY occurred_at;

A PostgreSQL audit_event table with a constrained actor type and full JSONB snapshots, plus three rows from one invoice approval that share an operation_id and are retrieved together with a single query.

Implementing it: actor context, transactional writes, and trigger backstops in Postgres

Start with an application helper, such as audit.record(entity, action, before, after), that inserts the audit row through the same connection and transaction as the business write. If the transaction rolls back, the audit row rolls back with it. This rules out "logged but never happened" and "happened but never logged." The helper is also the only place that knows intent: the domain operation name, the reason the user gave, and which fields actually matter.

Right after BEGIN, call set_config('app.actor', ..., true) and set_config('app.correlation_id', ..., true). The true makes each setting transaction-local. This is what keeps connection poolers safe. A session-level SET survives on the pooled connection and attaches one user's identity to the next client's queries. In transaction-pooling mode, session state is not reliably preserved anyway, so only transaction-local settings behave predictably.

Next, add a generic AFTER INSERT OR UPDATE OR DELETE ... FOR EACH ROW trigger that writes to_jsonb(OLD) and to_jsonb(NEW). It catches raw SQL, migrations, and code paths that bypass the helper. It cannot know why a change happened. It also cannot know who made it unless context was set.

Context is often missing. Background jobs and scripts frequently never call set_config. Read the actor with current_setting('app.actor', true) wrapped in NULLIF(..., ''), because after a transaction-local setting ends, the value can read as an empty string rather than null. Fall back to a sentinel such as 'system:unknown', and alert whenever that sentinel shows up.

Overlap between the two sources needs handling. Have the helper also set app.audited = 'on' in the same transaction. The trigger then skips the write, or writes a row flagged as redundant, which you can reconcile later on transaction id, table, and primary key.

Copy-- Generic backstop trigger: catches raw SQL, migrations and jobs that skip the app helper.
CREATE OR REPLACE FUNCTION audit.row_change() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  -- missing_ok = true returns NULL if never set; NULLIF covers the empty-string case
  v_actor text := COALESCE(NULLIF(current_setting('app.actor', true), ''), 'system:unknown');
  v_corr  text := NULLIF(current_setting('app.correlation_id', true), '');
  v_dup   boolean := COALESCE(current_setting('app.audited', true), '') = 'on';
BEGIN
  -- Keep the row but flag it redundant when the helper already wrote one with intent
  INSERT INTO audit.log (source, table_name, op, actor, correlation_id, old_row, new_row, redundant)
  VALUES ('trigger', TG_TABLE_NAME, TG_OP, v_actor, v_corr,
          CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END,
          CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END,
          v_dup);
  RETURN NULL;  -- return value is ignored for AFTER row triggers
END $$;

CREATE TRIGGER orders_audit
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW EXECUTE FUNCTION audit.row_change();

-- Application path: context is transaction-local (third arg true), so it is safe behind a pooler.
BEGIN;
SELECT set_config('app.actor', 'user:4821', true),
       set_config('app.correlation_id', 'req-7f3a9c', true),
       set_config('app.audited', 'on', true);
UPDATE orders SET status = 'refunded' WHERE id = 1001;
INSERT INTO audit.log (source, table_name, op, actor, correlation_id, reason, redundant)
VALUES ('app', 'orders', 'order.refund', 'user:4821', 'req-7f3a9c', 'customer request', false);
COMMIT;

-- Alerting query: any sentinel actor means a code path never set context.
SELECT table_name, count(*) FROM audit.log
WHERE actor = 'system:unknown' AND created_at > now() - interval '15 minutes'
GROUP BY table_name;

A Postgres backstop trigger reading transaction-local actor context with a sentinel fallback, the app helper's transaction that sets it, and a query to alert on unknown actors.

Entity versioning: versions tables versus temporal ranges

Auditing tells you who changed what. Versioning answers a different question: what did this record look like on a given date? There are two common shapes for that.

Versions table. Each write appends a row of (entity_id, version, payload, recorded_at), with version incrementing by one per entity and (entity_id, version) as the primary key. To reconstruct state at date X, select the row with the highest version where recorded_at <= X. The version number also works as an optimistic locking token. Clients read version 7, then update WHERE id = $1 AND version = 7. If zero rows are affected, someone else wrote first, and you return a conflict instead of silently overwriting their change.

Temporal ranges. Each row carries valid_from and valid_to, with valid_to left open on the current row. The point-in-time query becomes a range containment check. Writes must close the previous row and open the next one atomically. In Postgres, an exclusion constraint on the range stops two history rows for the same entity from overlapping.

Both approaches have the same weakness. Stored JSONB snapshots outlive the code that wrote them. After a column is renamed or dropped, old payloads no longer match current structs. Store a payload_version with every row. Then write readers that upgrade older shapes on load and tolerate missing or unknown fields, so they don't fail when the shape has drifted.

Copy-- Approach 1: append-only versions table; (entity_id, version) is the key
CREATE TABLE order_versions (
  entity_id       bigint      NOT NULL,
  version         int         NOT NULL,  -- +1 per entity on every write
  payload_version int         NOT NULL,  -- shape of payload, for tolerant readers
  payload         jsonb       NOT NULL,
  recorded_at     timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (entity_id, version)
);

-- State on date X: the highest version recorded at or before X
SELECT version, payload_version, payload FROM order_versions
WHERE entity_id = 42 AND recorded_at <= '2026-03-01T00:00:00Z'
ORDER BY version DESC LIMIT 1;

-- Same number as an optimistic lock: client read version 7.
-- Zero rows updated means someone else wrote first, so return a conflict.
UPDATE orders SET status = 'shipped', version = version + 1
WHERE id = 42 AND version = 7;

-- Approach 2: temporal ranges; btree_gist allows '=' on bigint in the constraint
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE order_history (
  entity_id       bigint    NOT NULL,
  payload_version int       NOT NULL,
  payload         jsonb     NOT NULL,
  valid           tstzrange NOT NULL,  -- upper bound open on the current row
  EXCLUDE USING gist (entity_id WITH =, valid WITH &&)  -- no overlaps per entity
);

-- State on date X: the row whose range contains X
SELECT payload_version, payload FROM order_history
WHERE entity_id = 42 AND valid @> '2026-03-01T00:00:00Z'::timestamptz;

-- A write closes the current row and opens the next one in a single transaction
BEGIN;
UPDATE order_history SET valid = tstzrange(lower(valid), now())
WHERE entity_id = 42 AND upper_inf(valid);
INSERT INTO order_history VALUES (42, 2, '{"status": "shipped"}', tstzrange(now(), NULL));
COMMIT;

Postgres schemas and point-in-time queries for a versions table and for a tstzrange history table: the version column doubles as an optimistic lock, an exclusion constraint prevents overlapping ranges, and every row stores a payload_version so readers can upgrade older JSONB shapes.

Tamper resistance, sensitive data, and operating at scale

Make the trail append-only

Put audit tables in their own schema, owned by a dedicated role that the application never uses. Grant the application role only INSERT and SELECT, and revoke UPDATE and DELETE. A bug or a compromised service can then add misleading rows, but it cannot quietly rewrite history. For stronger guarantees, hash-chain the rows: each row stores a hash of its own contents plus the previous row's hash. A verification job recomputes the chain, and any edited or missing row breaks it. Chaining serializes writes, so scope the chain per partition or per entity rather than globally. When an entry is wrong, never edit it. Insert a compensating record that references the original row's ID and explains the correction.

Keep sensitive data out of diffs

Redact secrets and tokens before computing a diff, or replace them with a keyed hash so you can still see that a value changed. Store personal data by reference, such as a subject ID that points to a separate table. An erasure request then deletes or crypto-shreds that table and leaves the append-only log intact.

Operate at scale

Index (entity_type, entity_id, occurred_at) for entity history and (actor_id, occurred_at) for "what did this user do." Partition by time, monthly for example, so an expired partition can be detached, archived, and dropped as a single cheap operation. Set retention per data class. In a single unpartitioned table, indexes outgrow memory and vacuum runs longer. Retention also turns into mass DELETEs that bloat the table, and backup and restore times keep growing with history nobody queries.

Alternatives, when to avoid this design, and impact on cost and engineering time

The application-level design writes one extra row inside every audited transaction. That extra row increases write amplification, latency, and WAL volume.

Illustrative estimate (assumed numbers, not a benchmark). These inputs are assumed: 200 audited writes per second on a hot table, a 2 KB JSONB before/after payload, and 0.3 KB of index entries per row.

Daily growth = writes/s × 86,400 × (payload + index bytes) = 200 × 86,400 × 2.3 KB ≈ 40 GB/day

WAL traffic adds roughly that volume again before replication. Storing diffs instead of full snapshots shrinks the payload term the most.

The payback is in engineering time. Incident and support investigations start from one query instead of grepping logs across modules. Modules no longer hand user ids to each other, because the actor context is already set. A cross-module timeline for one customer is a single indexed lookup. Compliance requests get answered from data rather than reconstructed from old deploys and Slack threads.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to audit log design, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.