PacketStation/server/packetstation/migrations/0010_admin_notify.sql

-- 0010_admin_notify: phase 9. Admin profiles, notification routing and delivery, integrity findings.
-- Runs as ps_migrate; 0001's default privileges give ps_app SELECT, INSERT, UPDATE, DELETE on every table here.
-- Append-only tables revoke below.

-- ---------------------------------------------------------------- profiles and departments

-- The Admin's "active" on a person's profile. users.active belongs to the AD reconcile (disabled or gone in AD);
-- profile_active is the app's own switch. An inactive profile drops out of the welder picker, the worker filter
-- and the station pickers. It does not change what the person may do; AD groups decide that.
ALTER TABLE users ADD COLUMN profile_active bool NOT NULL DEFAULT true;

-- Who added a department in the app. Seeded departments have neither.
ALTER TABLE departments ADD COLUMN created_at timestamptz;
ALTER TABLE departments ADD COLUMN created_by uuid REFERENCES users (id);

-- When the integrity job last rehashed the file behind an asset, and whether it matched.
ALTER TABLE assets ADD COLUMN hash_checked_at timestamptz;
ALTER TABLE assets ADD COLUMN hash_ok bool;

-- ---------------------------------------------------------------- notification routing

CREATE TYPE notify_delivery AS ENUM ('immediate', 'digest');
CREATE TYPE notify_recipient_kind AS ENUM ('group', 'user', 'mailbox', 'dept_supervisor');

-- One row per event type (the catalog in notify/catalog.py). Seeded by the code on first start: enabled,
-- immediate, recipient PS-Admins. param is N hours or N days for the event types that take one.
CREATE TABLE notification_rules (
    event_type  text PRIMARY KEY,
    enabled     bool NOT NULL DEFAULT true,
    delivery    notify_delivery NOT NULL DEFAULT 'immediate',
    digest_time time NOT NULL DEFAULT '07:00',
    param       int CHECK (param IS NULL OR param > 0),
    version     int NOT NULL DEFAULT 1,
    updated_at  timestamptz,
    updated_by  uuid REFERENCES users (id)
);

-- group: an AD group by DN (any group under LDAP_BASE_DN), resolved to its members' mail at send time.
-- user: one person from the directory. mailbox: a plain address. dept_supervisor: the event's department
-- Supervisor group, worked out at send time.
CREATE TABLE notification_recipients (
    id         bigserial PRIMARY KEY,
    event_type text NOT NULL REFERENCES notification_rules (event_type) ON DELETE CASCADE,
    kind       notify_recipient_kind NOT NULL,
    group_dn   text,
    group_name text,
    user_id    uuid REFERENCES users (id),
    address    text,
    CHECK (kind <> 'group' OR group_dn IS NOT NULL),
    CHECK (kind <> 'user' OR user_id IS NOT NULL),
    CHECK (kind <> 'mailbox' OR address IS NOT NULL)
);
CREATE INDEX notification_recipients_event ON notification_recipients (event_type);

-- A person left out of one event type's delivery. self = set by the person on Account settings (never allowed
-- for the security event types); otherwise set by an Admin.
CREATE TABLE notification_exclusions (
    event_type text NOT NULL REFERENCES notification_rules (event_type) ON DELETE CASCADE,
    user_id    uuid NOT NULL REFERENCES users (id),
    self       bool NOT NULL DEFAULT false,
    created_at timestamptz NOT NULL DEFAULT now(),
    created_by uuid REFERENCES users (id),
    PRIMARY KEY (event_type, user_id)
);

-- ---------------------------------------------------------------- notification events (0005's table, extended)

-- kind stays as the old key and equals event_type for every row raised through notify.raise_event.
-- A standing condition (feed failing, backup stale, quota) stays open (resolved_at null) until it clears; raising
-- it again while open only counts (raised_count, last_raised_at) and sends nothing: that is the dedupe. Clearing
-- it sets resolved_at and inserts a recovery row (recovery_of) that is sent to the same rule. A one-shot event
-- (security alert, reject, version deployed) is inserted already resolved.
ALTER TABLE notification_events ADD COLUMN event_type text;
ALTER TABLE notification_events ADD COLUMN title text;
ALTER TABLE notification_events ADD COLUMN body text;
ALTER TABLE notification_events ADD COLUMN dept_code text REFERENCES departments (code);
ALTER TABLE notification_events ADD COLUMN raised_count int NOT NULL DEFAULT 1;
ALTER TABLE notification_events ADD COLUMN last_raised_at timestamptz NOT NULL DEFAULT now();
ALTER TABLE notification_events ADD COLUMN recovery_of bigint REFERENCES notification_events (id);
-- Set by notify-send when it has handled the row: sent, queued for the digest, or skipped (rule off).
ALTER TABLE notification_events ADD COLUMN dispatched_at timestamptz;
ALTER TABLE notification_events ADD COLUMN dispatch_result text;

-- The phase 4 quota rows become assets_quota events. They were raised before delivery existed, so they are
-- marked handled and never mailed late.
UPDATE notification_events SET event_type = kind, title = 'Assets storage warning', dispatched_at = now(),
    dispatch_result = 'before_phase_9'
WHERE event_type IS NULL;

CREATE INDEX notification_events_pending ON notification_events (id)
    WHERE dispatched_at IS NULL AND event_type IS NOT NULL;
CREATE INDEX notification_events_type_at ON notification_events (event_type, created_at DESC);

-- Events waiting for their rule's daily digest. Recipients are resolved when the digest goes, not here.
CREATE TABLE notification_outbox (
    id         bigserial PRIMARY KEY,
    event_id   bigint NOT NULL REFERENCES notification_events (id),
    event_type text NOT NULL,
    queued_at  timestamptz NOT NULL DEFAULT now(),
    sent_at    timestamptz,
    sent_id    bigint
);
CREATE INDEX notification_outbox_pending ON notification_outbox (event_type, id) WHERE sent_at IS NULL;

-- Every message, append-only: the event type ('digest' for a digest), the events in it, the subject, the
-- recipients as resolved [{address, user_id, why}], and the result. A test send is marked test.
CREATE TABLE notifications_sent (
    id           bigserial PRIMARY KEY,
    at           timestamptz NOT NULL DEFAULT now(),
    event_type   text NOT NULL,
    event_ids    bigint[] NOT NULL DEFAULT '{}',
    digest       bool NOT NULL DEFAULT false,
    test         bool NOT NULL DEFAULT false,
    subject      text NOT NULL,
    recipients   jsonb NOT NULL DEFAULT '[]',
    result       text NOT NULL CHECK (result IN ('sent', 'dry_run', 'failed', 'no_recipients')),
    error        text,
    requested_by uuid REFERENCES users (id)
);
CREATE INDEX notifications_sent_type ON notifications_sent (event_type, id DESC);

CREATE TRIGGER notifications_sent_append_only
    BEFORE UPDATE OR DELETE ON notifications_sent
    FOR EACH ROW EXECUTE FUNCTION ps_append_only();
CREATE TRIGGER notifications_sent_no_truncate
    BEFORE TRUNCATE ON notifications_sent
    FOR EACH STATEMENT EXECUTE FUNCTION ps_append_only();

-- ---------------------------------------------------------------- integrity

-- What the integrity job found wrong, per run, append-only. kind: seal (a filed packet's seal does not verify),
-- asset_hash (the file's sha256 differs), asset_missing (no file for an asset row), blob_orphan (a file no asset
-- row names; reported, never deleted), audit_chain (the hash chain breaks at subject = the audit id).
CREATE TABLE integrity_findings (
    id      bigserial PRIMARY KEY,
    -- no foreign key: housekeeping prunes job_runs at 180 days and these rows are append-only
    run_id  bigint,
    at      timestamptz NOT NULL DEFAULT now(),
    kind    text NOT NULL CHECK (kind IN ('seal', 'asset_hash', 'asset_missing', 'blob_orphan', 'audit_chain')),
    subject text NOT NULL,
    detail  jsonb NOT NULL DEFAULT '{}'
);
CREATE INDEX integrity_findings_run ON integrity_findings (run_id, id);

CREATE TRIGGER integrity_findings_append_only
    BEFORE UPDATE OR DELETE ON integrity_findings
    FOR EACH ROW EXECUTE FUNCTION ps_append_only();
CREATE TRIGGER integrity_findings_no_truncate
    BEFORE TRUNCATE ON integrity_findings
    FOR EACH STATEMENT EXECUTE FUNCTION ps_append_only();

-- ---------------------------------------------------------------- jobs

-- The phase 9 jobs, so "refresh from AD now" is accepted before the worker has started once.
INSERT INTO job_schedule (job) VALUES
    ('ad-reconcile'), ('session-refresh'), ('integrity'), ('backup-check'), ('notify-send'), ('notify-digest')
ON CONFLICT (job) DO NOTHING;

-- Explicit, after the default privileges: the app may only read and append these two.
REVOKE UPDATE, DELETE, TRUNCATE ON notifications_sent, integrity_findings FROM ps_app;
GRANT SELECT, INSERT ON notifications_sent, integrity_findings TO ps_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO ps_app;