Versioned Data

A legal tech company stored contracts in a database. A sales rep edited a contract's pricing terms after the customer had already signed it. The customer's copy showed the original terms. The company's copy showed the updated terms. No audit trail. No history. No way to prove what the original terms were.

The lawsuit was expensive.

If the system had versioned data, every edit would have created a new version while preserving the original. Version 1: the terms the customer signed. Version 2: the edited terms. A complete, tamper-evident history with timestamps and user attribution.

Data versioning isn't just about undo buttons, it's about accountability. In regulated industries, it's a legal requirement. In collaborative applications, it's a product feature. And in any system where data matters, it's insurance against "someone changed this and nobody knows when or why."

The core idea: instead of updating a row in place, you create a new row for each change. The old versions remain untouched. You always know what the data looked like at any point in time.

There are two main approaches:

Approach 1: Version column on the same table.

CREATE TABLE contracts (
 id UUID NOT NULL,
 version INTEGER NOT NULL DEFAULT 1,
 title TEXT NOT NULL,
 terms JSONB NOT NULL,
 modified_by UUID REFERENCES users(id),
 created_at TIMESTAMP DEFAULT NOW(),
 PRIMARY KEY (id, version)
);

-- Original contract
INSERT INTO contracts (id, version, title, terms, modified_by)
VALUES ('c-001', 1, 'Enterprise License', '{"price": 10000}', 'user-1');

-- Edited contract (new version, original preserved)
INSERT INTO contracts (id, version, title, terms, modified_by)
VALUES ('c-001', 2, 'Enterprise License', '{"price": 12000}', 'user-2');

-- Get the latest version
SELECT * FROM contracts WHERE id = 'c-001' ORDER BY version DESC LIMIT 1;

-- Get the version history
SELECT * FROM contracts WHERE id = 'c-001' ORDER BY version;

-- Get the state at a specific version
SELECT * FROM contracts WHERE id = 'c-001' AND version = 1;

Approach 2: Separate history table.

-- Main table (always the current version)
CREATE TABLE lessons (
 id UUID PRIMARY KEY,
 title TEXT NOT NULL,
 content_path TEXT,
 updated_at TIMESTAMP DEFAULT NOW()
);

-- History table (all previous versions)
CREATE TABLE lesson_history (
 id UUID DEFAULT gen_random_uuid(),
 lesson_id UUID REFERENCES lessons(id),
 version INTEGER NOT NULL,
 title TEXT NOT NULL,
 content_path TEXT,
 changed_by UUID,
 changed_at TIMESTAMP DEFAULT NOW(),
 PRIMARY KEY (lesson_id, version)
);

The main table always holds the current state, fast reads, no filtering needed. The history table stores every previous version for auditing and rollback. A trigger on the main table can automatically copy the old row to the history table before each update.