Plan: Data Breach Detection & Notification

CREATE TABLE detection_rules (
    id              UUID PRIMARY KEY DEFAULT uuidv7(),
    rule_name       TEXT NOT NULL UNIQUE,
    description     TEXT NOT NULL,
    rule_type       TEXT NOT NULL CHECK (rule_type IN (
        'failed_auth', 'bulk_access', 'after_hours_access',
        'privilege_escalation', 'data_export', 'account_lockout'
    )),
    threshold       INTEGER NOT NULL,       -- e.g., 5 failures
    window_minutes  INTEGER NOT NULL,       -- e.g., within 10 minutes
    severity        TEXT NOT NULL CHECK (severity IN ('info', 'warning', 'critical')),
    enabled         BOOLEAN NOT NULL DEFAULT true,
    notify_webhook  TEXT,                   -- URL to POST alert JSON
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    active          BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE security_alerts (
    id              UUID PRIMARY KEY DEFAULT uuidv7(),
    rule_id         UUID REFERENCES detection_rules(id),
    rule_name       TEXT NOT NULL,
    severity        TEXT NOT NULL CHECK (severity IN ('info', 'warning', 'critical')),
    description     TEXT NOT NULL,
    details         JSONB,                  -- audit entries that triggered the alert
    acknowledged    BOOLEAN NOT NULL DEFAULT false,
    acknowledged_by TEXT,
    acknowledged_at TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_alerts_severity ON security_alerts(severity) WHERE NOT acknowledged;
CREATE INDEX idx_alerts_created ON security_alerts(created_at DESC);

-- Example: failed_auth_burst rule (threshold=5, window=10min)
SELECT user_id, COUNT(*) as count
FROM audit_log
WHERE action = 'auth_failed'
  AND timestamp > now() - interval '10 minutes'
  AND success = false
GROUP BY user_id
HAVING COUNT(*) >= 5