Audit Logging in a Monolith: Triggers vs App Events vs CDC

What question must your audit trail answer?

An audit trail in a monolith answers six questions about every change: who made it, what they did, to which record, when, why, and what the values were before and after. Most designs fail on "why," because a column diff alone cannot capture it. Before choosing triggers, application events or CDC, decide which of three consumers you are building for. They impose different requirements.

The rows pull in different directions. A compliance trail cannot miss a change made through psql, so it needs capture below the application. An activity feed needs intent, such as "Alice approved invoice 42," which only the application knows. Merging them into one log usually produces a table too noisy for users and too incomplete for auditors.

You need a full audit trail when an outside party can ask you to prove who changed a record. That covers regulated data, financial ledgers and access-control changes. For an internal CRUD tool with no such obligation, a full trail is overkill. updated_at/updated_by columns plus application logs usually answer the real questions at a fraction of the storage and operational cost.

Designing the audit table

If you do need a trail, design the table before choosing how rows get into it. Triggers, application events and CDC can all write the same shape. Settling the schema first keeps the later comparison fair.

Columns that earn their place

Each row should record:

• Actor: a user or service id, not a display name, since names change.
• Action: insert, update, delete, or a domain verb.
• Entity type and id: what was touched.
• Database transaction id: groups rows written together.
• Timestamp: from the database clock.
• Changed columns: stored as a JSONB diff of only the fields that changed, not full before/after row images.
• Request or correlation id: ties the change back to an HTTP request or job.

Full row images look convenient but multiply storage on wide tables. They also copy sensitive columns you may later be required to purge.

Partitioning and indexes

Audit data only grows, and old rows are rarely read. Range-partition by time, usually monthly. Retention then becomes detaching or dropping a partition instead of a long DELETE that bloats the table and generates heavy WAL.

The dominant query is "show me this record's history." A composite index on entity type, entity id and timestamp serves it, and it is created on each partition. Add an index on actor only if you actually query by user.

Making it append-only

Revoke UPDATE, DELETE and TRUNCATE on the audit table from the application role. A bug or compromised app then cannot rewrite history.

This does not stop a superuser or the table owner. If tamper resistance matters, ship records to external write-once storage that the database administrators cannot alter.

Copy-- Append-only audit table, range-partitioned by month (PostgreSQL 11+).
CREATE TABLE audit_log (
  id             bigint GENERATED ALWAYS AS IDENTITY,
  actor_id       text        NOT NULL,  -- stable user/service id, not a display name
  action         text        NOT NULL,  -- insert | update | delete | domain verb
  entity_type    text        NOT NULL,
  entity_id      text        NOT NULL,
  tx_id          bigint      NOT NULL DEFAULT txid_current(),  -- groups rows from one transaction
  occurred_at    timestamptz NOT NULL DEFAULT now(),          -- database clock, not app clock
  changed        jsonb       NOT NULL,  -- only changed fields, e.g. {"status": ["open", "closed"]}
  correlation_id text,                  -- HTTP request or job id
  PRIMARY KEY (id, occurred_at)         -- PK on a partitioned table must include the partition key
) PARTITION BY RANGE (occurred_at);

-- One partition per month; create ahead of time (cron job or migration).
CREATE TABLE audit_log_2026_10 PARTITION OF audit_log
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE audit_log_2026_11 PARTITION OF audit_log
  FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

-- Serves "show me this record's history"; created on the parent, cascades to every partition.
CREATE INDEX audit_log_entity_idx
  ON audit_log (entity_type, entity_id, occurred_at);

-- Application role may write and read, never rewrite or erase.
REVOKE ALL ON audit_log FROM app_rw;
GRANT INSERT, SELECT ON audit_log TO app_rw;
-- Partitions carry their own privileges; make sure app_rw has none on them directly
-- (check ALTER DEFAULT PRIVILEGES if it is set for this schema).
REVOKE ALL ON audit_log_2026_10, audit_log_2026_11 FROM app_rw;

-- Retention: run as the table owner. Detach and drop instead of a bulk DELETE.
-- ALTER TABLE audit_log DETACH PARTITION audit_log_2025_10;
-- DROP TABLE audit_log_2025_10;

-- Note: the table owner and superusers can still alter or drop these rows.
-- For tamper resistance, ship rows to external write-once storage.

A PostgreSQL audit table partitioned by month, with an entity-history index, a JSONB diff column and an application role limited to INSERT and SELECT.

How Postgres audit triggers work

A row-level AFTER INSERT OR UPDATE OR DELETE trigger, written once and attached to every audited table, is the classic starting point. The function branches on TG_OP, serializes OLD and NEW with to_jsonb(), records TG_TABLE_NAME (and ideally TG_TABLE_SCHEMA), and stores txid_current(). The transaction ID lets you group every row a single business operation touched, even across tables.

The database does not know which application user made the change, because the connection usually authenticates as a shared role. The application has to pass that identity in, either with SET LOCAL app.user_id = '42' or with set_config('app.user_id', '42', true) at the start of each transaction. The trigger reads it with current_setting('app.user_id', true), where the second argument makes it return NULL instead of raising an error when the setting is missing. The transaction-local scope matters. A plain session-level SET survives COMMIT, so with a connection pool the next request that borrows that connection inherits the previous user's identity and writes it into your audit trail.

Row triggers have blind spots:

• TRUNCATE does not fire them. Statement-level TRUNCATE triggers exist, but they have no OLD rows to record.
• DDL changes are not captured at all.

Triggers also have costs:

• Every write gains a second insert in the same transaction, which adds latency and WAL volume.
• On hot tables, locks are held longer while the audit row is written.
• If the trigger raises an error, for example because the audit table is full or has a constraint violation, the business write rolls back with it.

Finally, a row diff tells you that status went from active to suspended. It cannot tell you whether that was a fraud hold, a billing failure or an admin action.

Copy-- One shared audit table for every audited table
CREATE TABLE audit_log (
  id           bigserial   PRIMARY KEY,
  table_schema text        NOT NULL,
  table_name   text        NOT NULL,
  op           text        NOT NULL,   -- INSERT / UPDATE / DELETE
  txid         bigint      NOT NULL,   -- groups all rows from one transaction
  app_user     text,                   -- NULL if the app forgot to set it
  old_row      jsonb,
  new_row      jsonb,
  changed_at   timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION audit_row() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit_log (table_schema, table_name, op, txid, app_user, old_row, new_row)
  VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_OP, txid_current(),
          -- missing_ok=true returns NULL instead of raising; NULLIF covers the
          -- empty string left behind after a transaction-local value expires
          NULLIF(current_setting('app.user_id', true), ''),
          CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) END,
          CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) END);
  RETURN NULL;  -- ignored for AFTER row triggers
END;
$$;

-- Attach the same function to each audited table (TRUNCATE will not fire this)
CREATE TRIGGER accounts_audit
  AFTER INSERT OR UPDATE OR DELETE ON accounts
  FOR EACH ROW EXECUTE FUNCTION audit_row();

-- Application side: set identity per transaction, never per session,
-- so a pooled connection cannot carry it into the next request
BEGIN;
SELECT set_config('app.user_id', '42', true);  -- same as SET LOCAL app.user_id = '42'
UPDATE accounts SET status = 'suspended' WHERE id = 1001;
COMMIT;

A generic row-level AFTER trigger that writes OLD/NEW as JSONB with the operation, table, transaction ID and a transaction-local application user ID.

Application events: capturing intent in the service layer

The service layer is the only place that knows why a change happened. When a support agent approves a refund, the code path that handles it knows the actor, the reason code, the ticket it came from and the policy that allowed it. An application event records that directly: refund.approved, with the refund ID, the approver, the amount and the justification. It does not just record that refunds.status became approved. Auditors and incident responders ask questions in these terms, so a semantic record answers them without anyone having to reconstruct intent from column diffs.

Where you write the event decides whether you can trust it. If you insert the audit row in the same database transaction as the business change, the two commit or roll back together, so you never record a refund that didn't happen or miss one that did. If you emit the event after commit, or publish straight to a message broker, a crash between the write and the emit loses the record. A transactional outbox restores atomicity: you write the event to an outbox table in the same transaction, then a relay forwards it.

Each option has a cost. With synchronous in-transaction writes, a failing audit insert blocks the refund. With an outbox relay, delivery is at-least-once, so consumers must deduplicate by event ID.

The bigger weakness is coverage. Events only exist when the change goes through your service code. These paths skip it:

• Raw SQL from scripts or psql sessions
• Schema and data migrations
• Admin consoles that write to tables directly
• Manual incident fixes
• Bulk ORM updates that bypass model hooks and callbacks

Copyimport uuid
from psycopg2.extras import Json


def approve_refund(conn, refund_id, approver_id, reason_code, ticket_id):
    """Approve a refund and record the business event atomically."""
    event_id = str(uuid.uuid4())  # consumers deduplicate on this ID

    # `with conn` commits on success and rolls back on any exception,
    # so the state change and the audit event succeed or fail together.
    with conn:
        with conn.cursor() as cur:
            cur.execute(
                "UPDATE refunds SET status = 'approved' "
                "WHERE id = %s AND status = 'pending' RETURNING amount_cents",
                (refund_id,),
            )
            row = cur.fetchone()
            if row is None:
                raise ValueError("refund not found or not pending")

            # Record intent, not a column diff: who, why, and from which ticket.
            # A relay process later reads unpublished rows and forwards them,
            # which gives at-least-once delivery (duplicates are possible).
            cur.execute(
                "INSERT INTO audit_outbox (event_id, event_type, aggregate_id, payload) "
                "VALUES (%s, %s, %s, %s)",
                (
                    event_id,
                    "refund.approved",
                    refund_id,
                    Json({
                        "approver_id": approver_id,
                        "amount_cents": row[0],
                        "reason_code": reason_code,
                        "ticket_id": ticket_id,
                    }),
                ),
            )
    # Do NOT publish to a broker here: a crash at this point would lose the event.
    return event_id

A service-layer function that updates a refund and inserts a semantic refund.approved outbox row in the same database transaction. If either write fails, both roll back, and a separate relay forwards committed events at-least-once.

Change data capture as an audit source

Change data capture closes that gap from the other side. Postgres logical decoding reads committed changes from the write-ahead log and streams them, in commit order, to a consumer attached through a replication slot. Every row change appears whether it came from the ORM, a migration or a psql session, and the write path is untouched: no trigger runs inside the transaction and no application code has to remember to emit anything.

The weakness is that the WAL records data, not people. It knows a row changed, but not who changed it or why. To recover actor and intent, you either add columns such as updated_by and change_reason that every writer must populate, or have the application call pg_logical_emit_message inside the transaction so the context travels in the same stream. Either way, you are back to relying on application discipline, and the paths listed above will still arrive without an actor.

The consumer has real work to do. After a restart it may receive changes it already processed, so writes to the audit store must be idempotent, typically keyed on the change's log position. It also has to cope with columns being added, renamed or dropped while it is running.

For a monolith, the operational cost is significant. You must run and monitor another process. If that consumer stalls, the slot pins WAL on the primary and disk usage grows until someone notices.

CDC is worth it when you already run a decoding pipeline or need guaranteed coverage of out-of-band writes. If neither applies, avoid it.

Comparing the approaches and the hybrid pattern

No single column wins because each approach answers a different question. Events explain why something changed but miss writes that bypass the service layer. Triggers and CDC record what changed but cannot see who asked or why. Most monoliths need both answers, so the practical design is a hybrid. The service writes a semantic event, a trigger or CDC stream records row changes, and both carry the same transaction id or correlation id.

Copyrequest ──► service layer ──► BEGIN
                                ├─ UPDATE orders ──► trigger ──► audit_row_changes (txid)
                                ├─ INSERT audit_events (txid, actor, action)
                              COMMIT
                                └─ WAL ──► optional CDC consumer ──► archive

A row change whose transaction id has no matching event points to an out-of-band write. That mismatch is the main value of the safety net.

A single approach is enough in two cases. If every write goes through one service and no one runs ad hoc SQL, events alone are sufficient. If you only need to reconstruct prior row state, triggers alone are sufficient.

Copy ┌─────────┐     ┌───────────────────┐
 │ request │────►│   service layer   │  knows actor, reason, corr_id
 └─────────┘     └─────────┬─────────┘
                           │ BEGIN
 ┌─────────────────────────▼──────────────────────────────────┐
 │ single database transaction, shared txid                   │
 │                                                            │
 │  UPDATE orders ──► trigger ──► audit_row_changes           │
 │                                (txid, table, old, new)     │
 │                                                            │
 │  service INSERT ─────────────► audit_events                │
 │                                (txid, actor, action)       │
 └─────────────────────────┬──────────────────────────────────┘
                           │ COMMIT
                           ▼
                     ┌───────────┐      ┌──────────────────────────┐
                     │    WAL    │─────►│ CDC consumer (optional)  │
                     └───────────┘      └────────────┬─────────────┘
                                                     │
                                                     ▼
                                        ┌──────────────────────────┐
                                        │ row-change log (txid)    │
                                        └──────────────────────────┘

   audit_events ◄──── join on txid / correlation id ────► row changes

Hybrid audit design: the service writes a semantic audit event in the same transaction as the data change, while a trigger or an optional CDC consumer reading the WAL records row-level changes, and both are joined by transaction id or correlation id.

Operating, testing and the cost of an audit trail

Growth, retention and PII

Audit tables grow with every write, never shrink on their own, and get queried mostly by time range. Partition by month on the event timestamp. Retention then becomes detaching and archiving whole partitions, which avoids the vacuum churn of large DELETEs.

Full row images copy every column, including emails, addresses and free text. Store only the columns the trail needs, and redact or hash sensitive fields when you capture them. When a deletion request arrives, you must be able to find and scrub that person's data in both live and archived partitions. Keying audit rows by subject ID makes that a targeted update instead of a full scan.

Verification

In integration tests, assert that each write path produces the expected audit row, with the right actor, action and before/after values. Missing rows are the failure you won't otherwise notice. On a schedule, replay audit history for a sample of entities and compare the result to current state. If they disagree, something is writing around the trail. Alert on per-table sequence gaps and on CDC consumer lag.

Cost

Illustrative estimate (assumed numbers, not a benchmark): effort = tables × hours per table + fixed setup. Assume 30 tables. Triggers: 30 × 1h + 16h = 46h. App events: 30 × 4h + 24h = 144h. CDC: 30 × 0.5h + 80h = 95h, plus ongoing connector operations.

These costs come back during incidents and compliance requests. "Who changed this, and when?" becomes one query, not a forensic search through application logs.

Alternatives to consider

Each of the three approaches answers a narrower question than these alternatives do. Check that before you adopt one.

Event sourcing

If the event log is the system of record, the audit trail comes with it. Current state is a projection of the log, so the log can't drift from it. The cost is high. Every read model, migration and bug fix has to deal with replaying history. Converting a working monolith to get an audit trail is rarely worth it. Consider it only when the domain already reasons in events, such as ledgers, order lifecycles or approvals.

Temporal (history) tables

You keep a validity range on each row and move superseded versions into a history table. This answers "what did this record look like on a given date?" well, and point-in-time joins become simple queries. It does not record who made a change or why unless you add those columns. In practice, temporal tables solve the same problem as a trigger-based audit with a different query shape.

Statement-level database logging

Extensions such as pgAudit write executed statements to the server log. This fits compliance regimes that require proof of access, including reads and DDL. It is weak for reconstructing data. You get SQL text, not before/after values, and parameterized queries make attribution harder.

Point-in-time recovery

WAL archiving lets you restore the database to any moment. That works as a last-resort forensic tool, but it is not an audit trail. Answering a single question means restoring a whole cluster.

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 logging monolith, 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.