Audit Trails and Change History Tables in SQL

1. What an audit trail is and why application logs are not enough

An audit trail is a complete, immutable record of every change made to a piece of data: who changed which value from what to what and when. Unlike an ordinary application log, which usually ends up as a text line in a file or a log aggregator, an audit trail is structured, queryable, and typically anchored in the same database as the data itself, so it enjoys the same transactional and integrity guarantees.

Application logs alone fall short for several reasons: they capture only changes that pass through the logged application layer, while direct database access, batch jobs, or manual admin scripts often go unnoticed. They are rarely structured enough to precisely filter for "all changes to field X in the last 30 days." And they are frequently deleted after a rotation period, while an audit trail often needs to be retained for years for legal or business reasons.

Typical use cases for an audit trail include financial systems where every change to an account balance must be traceable, HR systems with salary changes, or e-commerce platforms where price changes must be explainable after the fact. The following sections show three patterns that map this requirement directly into the database.

2. Pattern 1: shadow table per table with a trigger

The first pattern creates, for every table under audit, a structurally near identical shadow table that stores each version of a row as its own history row. A trigger on INSERT, UPDATE, and DELETE automatically writes a copy of the old or new state into this shadow table on every change, fully transparent to the application.

-- Shadow table for the products table
CREATE TABLE products_history (
  history_id   SERIAL PRIMARY KEY,
  product_id   INTEGER NOT NULL,
  price        NUMERIC(10,2) NOT NULL,
  name         VARCHAR(255) NOT NULL,
  operation    CHAR(1) NOT NULL,          -- 'I', 'U', 'D'
  changed_by   INTEGER NOT NULL,
  changed_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Trigger function (PostgreSQL, plpgsql)
CREATE OR REPLACE FUNCTION log_products_change() RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO products_history (product_id, price, name, operation, changed_by)
    VALUES (OLD.product_id, OLD.price, OLD.name, 'D', current_setting('app.user_id')::int);
    RETURN OLD;
  ELSE
    INSERT INTO products_history (product_id, price, name, operation, changed_by)
    VALUES (NEW.product_id, NEW.price, NEW.name,
            CASE WHEN TG_OP = 'INSERT' THEN 'I' ELSE 'U' END,
            current_setting('app.user_id')::int);
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER products_audit
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW EXECUTE FUNCTION log_products_change();

The advantage of this pattern: every shadow table has exactly the same structure as its source table, which makes queries intuitive and type safe. The downside shows up during schema changes: every new column in products requires a parallel change in products_history and an adjustment of the trigger function, which quickly becomes a maintenance burden with many audited tables.

3. Pattern 2: central audit table with JSON payload

The second pattern skips a shadow table per source table and instead collects all changes from all audited tables in a single central audit trail table. Old and new values are stored not as typed columns but as a JSON or JSONB document, which keeps the structure independent of the source table's schema.

-- Central audit table for all audited tables
CREATE TABLE audit_log (
  audit_id     BIGSERIAL PRIMARY KEY,
  table_name   VARCHAR(100) NOT NULL,
  record_id    INTEGER NOT NULL,
  operation    CHAR(1) NOT NULL,          -- 'I', 'U', 'D'
  old_values   JSONB,
  new_values   JSONB,
  changed_by   INTEGER NOT NULL,
  changed_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Generic trigger function, works for any table
CREATE OR REPLACE FUNCTION audit_generic() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, record_id, operation, old_values, new_values, changed_by)
  VALUES (
    TG_TABLE_NAME,
    COALESCE(NEW.id, OLD.id),
    LEFT(TG_OP, 1),
    CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) ELSE NULL END,
    CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) ELSE NULL END,
    current_setting('app.user_id')::int
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Only one line of trigger definition needed per new table
CREATE TRIGGER orders_audit AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_generic();

This pattern scales much better to many tables, because the same trigger function is reused universally and schema changes on the source table automatically appear in the JSON document without touching the audit table itself. The price is query complexity: filtering by a single field value requires JSON operators like old_values->>'price' instead of a plain column comparison, which can be slow without suitable GIN indexes on the JSONB columns.

4. Pattern 3: temporal tables per SQL standard

The third pattern uses native support for system versioned tables as specified by the SQL:2011 standard and directly implemented by SQL Server and MariaDB. The database automatically maintains a second table with all historical row versions, with no manually written trigger at all.

-- SQL Server: system versioned table, completely without own trigger code
CREATE TABLE contracts (
  contract_id   INT PRIMARY KEY,
  customer_id   INT NOT NULL,
  amount        DECIMAL(10,2) NOT NULL,
  valid_from    DATETIME2 GENERATED ALWAYS AS ROW START,
  valid_to      DATETIME2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.contracts_history));

-- Changes run as usual, history is created automatically
UPDATE contracts SET amount = 4200.00 WHERE contract_id = 501;

-- Query the state at a specific point in time, without any custom logic
SELECT * FROM contracts
FOR SYSTEM_TIME AS OF '2026-05-01T00:00:00'
WHERE contract_id = 501;

-- MariaDB: very similar syntax with "WITH SYSTEM VERSIONING"
-- CREATE TABLE contracts (...) WITH SYSTEM VERSIONING;

The big advantage of temporal tables is that the database takes over the entire versioning logic, including automatic creation of the history table and time travel queries via FOR SYSTEM_TIME AS OF. The downside is limited portability, because this feature depends heavily on the database system used, and PostgreSQL for example offers no native implementation, relying instead on extensions or manual patterns like shadow tables.

5. What every audit entry must contain

Regardless of the chosen pattern, a complete audit trail entry needs at least five pieces of information: who triggered the change, when it happened, what type of operation it was, the state before the change, and the state after. If any of these fields is missing, the audit trail loses much of its value for traceability and forensics.

An often forgotten detail is the context of the change: was it a direct application call, a batch job, or a manual admin intervention? An additional field source or context with values like 'app', 'batch', or 'admin_console' answers this question without complicating the core structure, and it is often decisive for classification during security incidents or support requests.

6. Performance impact of triggers on write load

Every trigger based audit trail increases the latency of every single write operation, because in addition to the actual change, an insert into the history or audit table happens within the same transaction. On tables with very high write load, say several thousand updates per second, this overhead can become noticeable, especially when the audit table has to maintain its own indexes.

-- Measuring trigger overhead (PostgreSQL)
EXPLAIN ANALYZE
UPDATE products SET price = price * 1.05 WHERE product_id = 42;

-- With an active audit trigger the plan contains the extra INSERT
-- into the audit_log table as part of the same execution

-- Reducing the overhead through selective auditing:
-- only check actually changed columns instead of every UPDATE statement
CREATE OR REPLACE FUNCTION audit_if_changed() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.price IS DISTINCT FROM OLD.price THEN
    INSERT INTO audit_log (table_name, record_id, operation, old_values, new_values, changed_by)
    VALUES ('products', NEW.product_id, 'U',
            jsonb_build_object('price', OLD.price),
            jsonb_build_object('price', NEW.price),
            current_setting('app.user_id')::int);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

One strategy for reducing this overhead is selective auditing: instead of capturing every column change, the trigger only records actually business relevant columns, such as price or status, while purely technical fields like updated_at are excluded. Asynchronous auditing via change data capture, where the change is read from the transaction log instead of via a trigger, further reduces latency in the critical write path but increases architectural complexity.

7. Retention, partitioning, and archiving of history data

An audit trail grows monotonically with no natural upper bound as long as data keeps changing. Without a partitioning strategy, the audit table becomes the largest table in the entire system after a few years, slowing down both backups and queries against recent entries. Partitioning by month or quarter, based on the changed_at timestamp, keeps individual partitions small and allows old partitions to be archived independently or moved to cheaper storage.

A common strategy combines partitioning with a retention policy: the most recent twelve months stay in performant partitions on fast storage, older partitions are monthly moved to a cheaper cold storage system or exported in compressed form. Important here: an audit trail, unlike operational data, must practically never be deleted while legal retention periods apply, which is why archiving should always come before deletion.

8. Audit trails and compliance requirements

Regulations such as GDPR in Europe or industry specific requirements in finance and healthcare frequently demand an explicit, traceable audit trail for changes to personal or financially relevant data. At the same time, that same GDPR demands a "right to erasure," which can conflict with an immutable audit trail when a user requests full removal of their data.

A practical compromise is pseudonymization instead of deletion within the audit context: personal fields in the old_values/new_values JSON are replaced with a hash or placeholder upon a deletion request, while the fact and timing of the original change remain traceable. This decision should be coordinated early with data protection officers, because it has a direct impact on the audit table's schema and is hard to change later.

9. The three patterns compared

The following table compares shadow table, central JSON audit table, and temporal tables by the most important decision criteria for an audit trail.

10. Summary

An audit trail reliably answers who changed which value and when, a task application logs alone rarely fulfill completely and durably. Shadow tables with triggers offer typed, comfortable queries at the cost of maintenance effort on schema changes, a central audit table with JSON payload scales better across many tables, and native temporal tables reduce custom code to zero but couple the project more tightly to a specific database system.

Regardless of the pattern: a complete audit trail entry needs the user, the timestamp, the operation, the old and new state, and ideally the change context. Time based partitioning keeps the table performant, and compliance requirements such as the GDPR right to erasure should shape the schema decision from the start rather than being retrofitted later.

11. FAQ: Audit Trails and Change History Tables