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.

audit_summary_by_actor.sql 
CREATE TABLE audit_choice AS WITH params(wanted_actor) AS (VALUES ()) 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 ()) 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;
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.