Thinh Lam
39 lines
1.3 KiB
SQL
39 lines
1.3 KiB
SQL
-- Unified append-only audit trail (see assets/docs/audit-log-implementation.md).
|
|
-- Application role should be granted INSERT, SELECT only (configure per deployment).
|
|
|
|
DO $$
|
|
BEGIN
|
|
CREATE TYPE audit_action AS ENUM (
|
|
'create',
|
|
'read',
|
|
'update',
|
|
'delete',
|
|
'login',
|
|
'logout',
|
|
'login_failed'
|
|
);
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN NULL;
|
|
END
|
|
$$;
|
|
|
|
CREATE TABLE IF NOT EXISTS audit_events (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
actor_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
|
|
actor_email TEXT NOT NULL,
|
|
actor_role TEXT NOT NULL,
|
|
action audit_action NOT NULL,
|
|
entity_type TEXT NOT NULL,
|
|
entity_id TEXT,
|
|
before JSONB,
|
|
after JSONB,
|
|
metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
request_id UUID
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_audit_actor_time ON audit_events (actor_user_id, occurred_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_entity ON audit_events (entity_type, entity_id, occurred_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_action_time ON audit_events (action, occurred_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_metadata_gin ON audit_events USING gin (metadata);
|