Audit Trails and History That Excel Cannot Give You

The question arrives after the fact. Who changed the price of product 7? What was it before? Why was a customer's credit limit raised in March? An auditor asks, a customer disputes an invoice, a manager wants to know why a figure moved. In a workbook the honest answer is usually that nobody knows. Excel was designed for calculation, and its record of change is thin. This post lists what Excel can and cannot tell you, what a database can do instead, and how to build an audit trail that stands up to the questions. It is a spoke of when a spreadsheet has become a database.

What Excel gives you

Version history. For a file in OneDrive or SharePoint, you can open earlier versions of the file, restore one, and see who saved each. This is whole-file history. To find what changed between two versions you compare them yourself, and Excel's Spreadsheet Compare (an Office tool in some editions) can help. It tells you that Ana saved at 14:02, not that she changed cell D7 from 10 to 12.

Show Changes. Newer Excel versions have a Show Changes pane for workbooks stored in the cloud, which lists recent cell edits with the person and time. It is useful for a recent look. The retention is limited and it records cell edits, not reasons, and it is not designed as a compliance record.

Track Changes (legacy). The older feature that highlighted changes in a shared workbook. It is deprecated, has limits on what it records, keeps history for a number of days that is configurable, and is not available with modern co-authoring.

Comments and notes. People can write why they changed something. It depends on discipline and sits in a cell, not in a log.

File properties. The author and the last modified by, which say who last saved and not who changed what.

None of these gives you a complete, tamper-resistant, queryable history of changes. For many personal and small team uses that is acceptable. When the question has legal, financial or safety weight, it is not.

What an audit trail should record

A useful audit record answers who, what, when, and where possible why:

• Who: the person or service account.

• When: a timestamp with time zone.

• What: the table and row (by key), which columns, old and new values, and the kind of change (insert, update, delete).

• Why: a reason or a reference (a ticket number), supplied by the application where it matters.

• From where: the application or host, if needed.

It should be written at the moment of the change, in the same transaction, so that the change and its record succeed or fail together. And it should be append-only for the people whose actions it records.

An audit table in PostgreSQL

A generic audit table that stores each change as JSON:

CREATE TABLE audit_log (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    changed_at timestamptz NOT NULL DEFAULT now(),
    changed_by text        NOT NULL DEFAULT current_user,
    table_name text        NOT NULL,
    row_key    text        NOT NULL,
    action     text        NOT NULL CHECK (action IN ('INSERT','UPDATE','DELETE')),
    old_values jsonb,
    new_values jsonb
);

A trigger function fills it for any table that has an id column:

CREATE FUNCTION audit_row() RETURNS trigger AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log(table_name, row_key, action, new_values)
        VALUES (TG_TABLE_NAME, NEW.id::text, 'INSERT', to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        IF to_jsonb(OLD) IS DISTINCT FROM to_jsonb(NEW) THEN
            INSERT INTO audit_log(table_name, row_key, action, old_values, new_values)
            VALUES (TG_TABLE_NAME, NEW.id::text, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
        END IF;
        RETURN NEW;
    ELSE
        INSERT INTO audit_log(table_name, row_key, action, old_values)
        VALUES (TG_TABLE_NAME, OLD.id::text, 'DELETE', to_jsonb(OLD));
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

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

Now every change to products leaves a row. The IS DISTINCT FROM check skips updates that changed nothing. A query answers the question that began this post:

SELECT changed_at, changed_by,
       old_values->>'price' AS old_price,
       new_values->>'price' AS new_price
FROM audit_log
WHERE table_name = 'products' AND row_key = '7' AND action = 'UPDATE'
  AND old_values->>'price' IS DISTINCT FROM new_values->>'price'
ORDER BY changed_at;

         changed_at         | changed_by | old_price | new_price
----------------------------+------------+-----------+-----------
 2025-03-03 14:02:11+00     | ana        | 10.00     | 12.00

Three caveats. current_user is the database role, so if every application connection uses one shared account, the audit says "app" for everyone. Pass the real user to the session (SET LOCAL app.user = 'ana' and read it with current_setting) or give people their own roles. Second, JSON of the whole row grows fast on wide tables; log only changed columns for those. Third, triggers fire for changes made through the database, which is the point, including a careless manual UPDATE by an administrator, which is something you want recorded.

Temporal tables in SQL Server

SQL Server 2016 and later have system-versioned temporal tables: the database keeps the full history of a table automatically in a history table.

CREATE TABLE products (
    id        int PRIMARY KEY,
    name      nvarchar(100) NOT NULL,
    price     decimal(10,2) NOT NULL,
    valid_from datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    valid_to   datetime2 GENERATED ALWAYS AS ROW END   NOT NULL,
    PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.products_history));

Each update moves the old row to the history table with its validity period. You can query the table as of a past moment with FOR SYSTEM_TIME AS OF '2025-03-10', which is excellent for "what was the price on that day". It records what the data was, but not who changed it; add a changed_by column set by the application if you need that. PostgreSQL has no built-in equivalent, though extensions and the trigger approach above cover it. MySQL has neither in the core server, so triggers or application logging are used.

Make the audit trail trustworthy

A log that the people it records can edit is not an audit trail.

• Permissions. Applications and users get INSERT on the audit table through the trigger, and nobody gets UPDATE or DELETE. Only a separate administrator role can manage it, and that role's actions are logged elsewhere.

• Tamper evidence. For strong requirements, chain records with a hash of the previous one, or ship the log to a separate system (a log store, write-once storage) in near real time.

• Time source. Use the database clock with time zone, not a value supplied by a client.

• Retention. Decide how long to keep it, and archive old partitions. Audit tables grow with every change; partition by month for large systems.

• Completeness. Check that every table with sensitive data has a trigger. A table added later without one is a hole. A query against the catalog lists tables without audit triggers.

• Read access. The log contains the data it records, including sensitive values. Restrict who can read it.

Add the reason

The mechanical record says what changed. Reasons are written by people. Where they matter, have the application ask for one and store it with the change. A simple way is a session variable the trigger reads:

SET LOCAL app.change_reason = 'ticket 4412: supplier price rise';
UPDATE products SET price = 12 WHERE id = 7;

and the trigger adds current_setting('app.change_reason', true) to the audit row. A column for it in the audit table makes it searchable. A reason that is optional will be left blank; for regulated changes, make it required in the application.

From the spreadsheet to the trail

Moving to a database does not by itself create history; it makes it possible. When migrating, load the workbook as the first state, and note in the audit table that the baseline came from the file on a given date. From then on, every change is recorded. If the workbook had change information worth keeping (comments, version history), export it to an archive alongside the migration notes (moving from Excel to PostgreSQL step by step).

Concurrent edits are a separate hazard that history does not solve; see shared Excel files and the lost update problem. For the front end that feeds the audit, read keeping Excel as the front end to a database.

Lighter options

Not everything needs triggers. Alternatives:

• An append-only table of events, where changes are inserted as new rows and the current state is a query. This is the event-sourced style, and it suits data that is naturally a ledger.

• **A updated_at and updated_by pair** on each row. It records the last change only, but it answers most casual questions at low cost.

• Soft deletes (deleted_at) instead of removing rows, so nothing vanishes.

• Application-level logging of the commands that were run, which is easier when all writes go through one service.

Choose by the question you must answer later. "What was this value on a given date" needs history; "who touched this last" needs only the pair of columns.

When an audit trail is not worth building

The data is a scratch workspace. Nobody will ask who changed it.

The data can be rebuilt from a source. If a table is derived nightly from another system, audit the source.

The cost exceeds the risk. Audit triggers add write overhead and storage. For a small internal list, version history on the file may be enough.

A different control fits better. If the concern is approval, add a workflow. If it is accuracy, add validation.

PostgreSQL documents triggers in its chapter on trigger functions, and Microsoft documents temporal tables. To convert a small extract to JSON, use the CSV to JSON converter.