Audit and History Tables

Audit Summaries

Count Changes by Actor

Audit logs can feed operational reports that summarize what one actor changed.

Program

Play the script to choose an actor and count their audit events by action.

wanted_actor
audit_summary_by_actor.sql
Replay: real traced execution (multi-file project)
CREATE TABLE audit_choice AS WITH params(wanted_actor) AS (VALUES ('admin')) SELECT wanted_actor FROM params;
CREATE TABLE audit_events (event_id INTEGER, actor TEXT, action TEXT, target_table TEXT);
INSERT INTO audit_events VALUES (1, 'admin', 'insert', 'orders'), (2, 'worker', 'update', 'orders'), (3, 'admin', 'update', 'products'), (4, 'worker', 'update', 'products'), (5, 'admin', 'delete', 'sessions');
SELECT action, COUNT(*) AS event_count FROM audit_events WHERE actor = (SELECT wanted_actor FROM audit_choice) GROUP BY action ORDER BY action;

CREATE TABLE audit_choice AS WITH params(wanted_actor) AS (VALUES ('worker')) SELECT wanted_actor FROM params;
CREATE TABLE audit_events (event_id INTEGER, actor TEXT, action TEXT, target_table TEXT);
INSERT INTO audit_events VALUES (1, 'admin', 'insert', 'orders'), (2, 'worker', 'update', 'orders'), (3, 'admin', 'update', 'products'), (4, 'worker', 'update', 'products'), (5, 'admin', 'delete', 'sessions');
SELECT action, COUNT(*) AS event_count FROM audit_events WHERE actor = (SELECT wanted_actor FROM audit_choice) GROUP BY action ORDER BY action;

  1. choice ← 1 row

    1CREATE TABLE audit_choice AS WITH params(wanted_actor) AS (VALUES ('admin')) SELECT wanted_actor FROM params;2CREATE TABLE audit_events (event_id INTEGER, actor TEXT, action TEXT, target_table TEXT);
    values this step1 rowchoice

  2. audit_events ← 5 rows

    2CREATE TABLE audit_events (event_id INTEGER, actor TEXT, action TEXT, target_table TEXT);3INSERT INTO audit_events VALUES (1, 'admin', 'insert', 'orders'), (2, 'worker', 'update', 'orders'), (3, 'admin', 'update', 'products'), (4, 'worker', 'update', 'products'), (5, 'admin', 'delete', 'sessions');4SELECT action, COUNT(*) AS event_count FROM audit_events WHERE actor = (SELECT wanted_actor FROM audit_choice) GROUP BY action ORDER BY action;
    values this step5 rowsaudit_events

  3. result ← 3 rows

    3INSERT INTO audit_events VALUES (1, 'admin', 'insert', 'orders'), (2, 'worker', 'update', 'orders'), (3, 'admin', 'update', 'products'), (4, 'worker', 'update', 'products'), (5, 'admin', 'delete', 'sessions');4SELECT action, COUNT(*) AS event_count FROM audit_events WHERE actor = (SELECT wanted_actor FROM audit_choice) GROUP BY action ORDER BY action;
    values this step3 rowsresult

  1. choice ← 1 row

    1CREATE TABLE audit_choice AS WITH params(wanted_actor) AS (VALUES ('worker')) SELECT wanted_actor FROM params;2CREATE TABLE audit_events (event_id INTEGER, actor TEXT, action TEXT, target_table TEXT);
    values this step1 rowchoice

  2. audit_events ← 5 rows

    2CREATE TABLE audit_events (event_id INTEGER, actor TEXT, action TEXT, target_table TEXT);3INSERT INTO audit_events VALUES (1, 'admin', 'insert', 'orders'), (2, 'worker', 'update', 'orders'), (3, 'admin', 'update', 'products'), (4, 'worker', 'update', 'products'), (5, 'admin', 'delete', 'sessions');4SELECT action, COUNT(*) AS event_count FROM audit_events WHERE actor = (SELECT wanted_actor FROM audit_choice) GROUP BY action ORDER BY action;
    values this step5 rowsaudit_events

  3. result ← 1 row

    3INSERT INTO audit_events VALUES (1, 'admin', 'insert', 'orders'), (2, 'worker', 'update', 'orders'), (3, 'admin', 'update', 'products'), (4, 'worker', 'update', 'products'), (5, 'admin', 'delete', 'sessions');4SELECT action, COUNT(*) AS event_count FROM audit_events WHERE actor = (SELECT wanted_actor FROM audit_choice) GROUP BY action ORDER BY action;
    values this step1 rowresult
event log Each audit event records who acted, what they did, and which table was affected.
GROUP BY `GROUP BY action` turns detailed audit rows into a compact report.
actor filter The selector changes whose audit activity is summarized.