Audit and History Tables

History Rows

Query an As-Of Status

A history table can store validity ranges so a query can ask what status was active on a chosen date.

Program

Play the script to choose an as-of date and find the matching account statuses.

status_history_asof.sql 
CREATE TABLE history_choice AS WITH params(as_of_day) AS (VALUES ()) SELECT as_of_day FROM params;
CREATE TABLE account_status_history (account_id INTEGER, status TEXT, valid_from TEXT, valid_to TEXT);
INSERT INTO account_status_history VALUES (1, 'trial', '2026-01-01', '2026-02-01'), (1, 'active', '2026-02-01', NULL), (2, 'active', '2026-01-10', '2026-03-01'), (2, 'paused', '2026-03-01', NULL);
SELECT account_id, status, valid_from, valid_to FROM account_status_history WHERE valid_from <= (SELECT as_of_day FROM history_choice) AND (valid_to IS NULL OR valid_to > (SELECT as_of_day FROM history_choice)) ORDER BY account_id;

CREATE TABLE history_choice AS WITH params(as_of_day) AS (VALUES ()) SELECT as_of_day FROM params;
CREATE TABLE account_status_history (account_id INTEGER, status TEXT, valid_from TEXT, valid_to TEXT);
INSERT INTO account_status_history VALUES (1, 'trial', '2026-01-01', '2026-02-01'), (1, 'active', '2026-02-01', NULL), (2, 'active', '2026-01-10', '2026-03-01'), (2, 'paused', '2026-03-01', NULL);
SELECT account_id, status, valid_from, valid_to FROM account_status_history WHERE valid_from <= (SELECT as_of_day FROM history_choice) AND (valid_to IS NULL OR valid_to > (SELECT as_of_day FROM history_choice)) ORDER BY account_id;

CREATE TABLE history_choice AS WITH params(as_of_day) AS (VALUES ()) SELECT as_of_day FROM params;
CREATE TABLE account_status_history (account_id INTEGER, status TEXT, valid_from TEXT, valid_to TEXT);
INSERT INTO account_status_history VALUES (1, 'trial', '2026-01-01', '2026-02-01'), (1, 'active', '2026-02-01', NULL), (2, 'active', '2026-01-10', '2026-03-01'), (2, 'paused', '2026-03-01', NULL);
SELECT account_id, status, valid_from, valid_to FROM account_status_history WHERE valid_from <= (SELECT as_of_day FROM history_choice) AND (valid_to IS NULL OR valid_to > (SELECT as_of_day FROM history_choice)) ORDER BY account_id;
validity range `valid_from` and `valid_to` describe when each history row applies.
open-ended row `valid_to IS NULL` means the status is still current.
as-of query The selector changes the date used to read the history table.