Admin
3ccea3e9f6
Record created/joined/triggered/delivered/destroyed with SHA-256 hashes of the alert and user UUIDs only, and a reason on destruction. The earlier analytics and stdout-mirror tables were superseded by the user ruling, so 009 creates alarm.event_log, migrates what still applies and drops both, leaving exactly one durable table with no network identifiers, token, body or query string. Retention is age-based via the sweeper.
84 lines
3.5 KiB
SQL
84 lines
3.5 KiB
SQL
\c dcntrlized;
|
|
|
|
-- 009_consolidate_event_log.sql — one event table, not two.
|
|
--
|
|
-- History: 007 created alarm.alarm_analytics (lifecycle rows with a reason) and
|
|
-- 008 created alarm.log_events (a durable mirror of stdout with hashed
|
|
-- ip/ua). The user ruling of 2026-10-18 superseded both: ONE durable table for
|
|
-- important lifecycle events only, with NO network identifiers, NO level, NO
|
|
-- message body, NO request metadata. stdout keeps the request log; this table is
|
|
-- not a mirror of it.
|
|
--
|
|
-- Final table: alarm.event_log
|
|
-- id BIGSERIAL primary key
|
|
-- occurred_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
-- event TEXT NOT NULL -- created | joined | triggered | delivered | destroyed
|
|
-- alert_hash TEXT NOT NULL -- SHA-256 hex of the alert UUID
|
|
-- user_hash TEXT -- SHA-256 hex of the actor/subscriber UUID
|
|
-- reason TEXT -- on 'destroyed' only: delivered|expired|disarmed|deleted
|
|
--
|
|
-- Identities are plain SHA-256 of the UUID string (UUIDs carry ~122 random bits,
|
|
-- so a digest is one-way). There is deliberately no column for an IP, a user
|
|
-- agent, a token, a body or a query string, and adding one later would be a
|
|
-- privacy regression, not a feature.
|
|
--
|
|
-- This migration:
|
|
-- 1. creates alarm.event_log (if missing);
|
|
-- 2. copies any rows from the two superseded tables, mapping their vocabularies;
|
|
-- 3. drops both superseded tables.
|
|
--
|
|
-- It is IDEMPOTENT IN EFFECT for a full 001→009 re-apply: re-applying 007/008
|
|
-- recreates the old tables empty, this file copies nothing and drops them again,
|
|
-- so the sequence always ENDS with exactly one table (alarm.event_log).
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = 'alarm') THEN
|
|
RETURN;
|
|
END IF;
|
|
|
|
CREATE TABLE IF NOT EXISTS alarm.event_log (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
event TEXT NOT NULL,
|
|
alert_hash TEXT NOT NULL,
|
|
user_hash TEXT,
|
|
reason TEXT,
|
|
CONSTRAINT event_log_event_check
|
|
CHECK (event IN ('created', 'joined', 'triggered', 'delivered', 'destroyed'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS event_log_alert_idx ON alarm.event_log (alert_hash, occurred_at);
|
|
CREATE INDEX IF NOT EXISTS event_log_occurred_idx ON alarm.event_log (occurred_at);
|
|
|
|
IF to_regclass('alarm.alarm_analytics') IS NOT NULL THEN
|
|
INSERT INTO alarm.event_log (occurred_at, event, alert_hash, user_hash, reason)
|
|
SELECT created_at,
|
|
CASE action
|
|
WHEN 'created' THEN 'created'
|
|
WHEN 'subscribed' THEN 'joined'
|
|
WHEN 'triggered' THEN 'triggered'
|
|
WHEN 'delivered' THEN 'delivered'
|
|
ELSE 'destroyed'
|
|
END,
|
|
alert_hash,
|
|
user_hash,
|
|
reason
|
|
FROM alarm.alarm_analytics
|
|
ORDER BY id;
|
|
DROP TABLE alarm.alarm_analytics;
|
|
END IF;
|
|
|
|
-- log_events held hashed network identifiers and stdout mirrors; the user
|
|
-- ruling removed both, so its rows are intentionally discarded.
|
|
IF to_regclass('alarm.log_events') IS NOT NULL THEN
|
|
DROP TABLE alarm.log_events;
|
|
END IF;
|
|
|
|
IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'alarm_user') THEN
|
|
GRANT SELECT, INSERT, DELETE ON alarm.event_log TO alarm_user;
|
|
GRANT USAGE, SELECT ON SEQUENCE alarm.event_log_id_seq TO alarm_user;
|
|
END IF;
|
|
END
|
|
$$;
|