security_audit_log

Overview

The security_audit_log table records security-relevant events such as data exports, data deletions, retention policy changes, and other privacy-sensitive operations. This is separate from the admin_audit_log and focuses specifically on data security and privacy compliance.

Relevant Invariants

• Invariant #14: "Privacy Is Infrastructure" -- Security events are tracked for accountability

Schema

-- From 20260205_data_retention.sql
CREATE TABLE security_audit_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id),
  event_type TEXT NOT NULL,
  event_details JSONB DEFAULT '{}',
  ip_address INET,
  user_agent TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Columns

Column	Type	Nullable	Default	Description
id	uuid	No	gen_random_uuid()	Primary key
user_id	uuid	Yes	--	User associated with the event
event_type	text	No	--	Event type (e.g., 'data_export', 'retention_change')
event_details	jsonb	No	'{}'	Event-specific details
ip_address	inet	Yes	--	Client IP address
user_agent	text	Yes	--	Client user agent string
created_at	timestamptz	No	NOW()	Event timestamp

RLS Policies

-- SELECT: Users can view their own security events; admins can view all
CREATE POLICY "Users can view own security events"
  ON security_audit_log FOR SELECT
  USING (
    auth.uid() = user_id OR
    EXISTS (SELECT 1 FROM admin_roles WHERE admin_roles.user_id = auth.uid())
  );

-- INSERT: System can log security events
CREATE POLICY "System can log security events"
  ON security_audit_log FOR INSERT
  WITH CHECK (true);

Related

• profiles -- User associated with the event
• admin_audit_log -- General admin audit trail

---

Last updated: 2026-02-07