Mod Logs: Save every change, thank yourself laterPostsMod Logs: Save every change, thank yourself later<br>By Scott Robinson·June 20, 2026

After over twelve years of building web and desktop apps, I finally added a modification log to one. One table. Only a few columns. It's some of the simplest infrastructure I've ever built, and the one I wish I'd had from day one.

At its core, a modification log is just a table that tracks all changes to your data. Although far from novel, it's truly a simple idea, and it's incredibly powerful.

There are a ton of use cases for this, to name a few:

Audit trail

Display historical trends

Track user activity

Replay data to replicate bugs

See the state of your database at any point in time

The nice thing about modification logs is that all of these use-cases can come later. You don't need to build them on day one. All you need to do is log changes and then at some point in the future you can extract the data you need from the modification log table.

They're also surprisingly simple to implement in any app. It's just a single table with a few fields:

parent_model

parent_id

field_name

old_value

new_value

user_id

created_at

This allows the modification log to be flexible enough to track changes to any model in the app. To start, I'd recommend only tracking changes to your core application models - users, orders, or whatever drives the business - and expanding from there. Any time a tracked model is updated, a new record is created in the modification log table for each changed field.

Once you have the table, you need to populate it whenever data changes. Two common approaches:

Explicit logging in API routes : After each create, update, or delete, write one row per changed field to the modification log.

Model hooks : Use your ORM's callbacks (before_save, after_update, etc.) to log changes automatically, so you don't have to touch every route.

Write-Ahead Logging (WAL) : If your database has one, inspect the WAL to log the changes to the modification log table.

Pick whichever fits your codebase. The important part is consistency - every change to a tracked model should land in the log.

The harder part here is actually reading the data back. Normal tables store current state: one row per user, one row per post, one row per order. A modification log stores history: one row per field change. To answer questions like "what did this record look like last Tuesday?" you need to replay those changes up to a point in time.

An Example

Let's say we have a table called line_items that tracks the items in an order. These line items can change over time, with quantities increasing, decreasing, or even being deleted.

CREATE TABLE line_items (<br>id INT PRIMARY KEY,<br>product_id INT,<br>customer_id INT,<br>quantity_ordered INT,<br>year INT,<br>is_deleted BOOLEAN DEFAULT FALSE<br>);

We then want to answer the question: As of end of day on January 10th, what was total quantity_ordered for 2026 orders?

You'll quickly realize these queries can be expensive at scale, so they're usually a better fit for scheduled reports than live dashboards. Here's one approach that worked for us in Postgres:

WITH latest_modifications AS (<br>-- Step 1: For each line item + field, keep only the most recent<br>-- change that happened on or before the cutoff date.<br>SELECT<br>ml.parent_id AS line_item_id,<br>ml.field_name,<br>ml.new_value,<br>ROW_NUMBER() OVER (<br>PARTITION BY ml.parent_id, ml.field_name<br>ORDER BY ml.created_at DESC<br>) AS row_num<br>FROM modification_logs ml<br>WHERE ml.parent_model = 'line_items'<br>AND ml.created_at 1768024800000 -- Jan 10 cutoff<br>),

line_items_snapshot AS (<br>-- Step 2: Pivot row-oriented log entries into column-oriented state.<br>-- One row per line item, with the latest known value for each field.<br>SELECT<br>lm.line_item_id,<br>COALESCE(<br>MAX(CASE<br>WHEN lm.field_name = 'quantity_ordered'<br>AND TRIM(lm.new_value) != ''<br>THEN lm.new_value::DOUBLE PRECISION<br>END), 0<br>) AS quantity_ordered,<br>MAX(CASE<br>WHEN lm.field_name = 'year'<br>THEN lm.new_value::INT<br>END) AS year,<br>COALESCE(<br>MAX(CASE<br>WHEN lm.field_name = 'is_deleted'<br>AND TRIM(lm.new_value) = '1'<br>THEN 1 ELSE 0<br>END), 0<br>)::BOOLEAN AS is_deleted<br>FROM latest_modifications lm<br>WHERE lm.row_num = 1 -- latest change per field only<br>GROUP BY lm.line_item_id<br>),

line_items_with_details AS (<br>-- Step 3: Join to the live table for fields that were set at creation<br>-- and never modified — and therefore never logged.<br>SELECT<br>lis.line_item_id,<br>lis.quantity_ordered,<br>lis.year,<br>lis.is_deleted,<br>li.product_id<br>FROM line_items_snapshot lis<br>LEFT JOIN line_items li<br>ON lis.line_item_id = li.id::TEXT

-- Step 4: Aggregate with query filters<br>SELECT<br>COALESCE(SUM(lit.quantity_ordered), 0) AS total_quantity_ordered<br>FROM line_items_with_details lit<br>WHERE lit.year = 2026<br>AND lit.is_deleted = FALSE;

From a high level, here is how it works:

Rank changes : For each line item and field, find the most recent log entry on or before the cutoff date.

Pivot to columns : Turn one-row-per-change into one-row-per-line-item,...