-- ============================================
-- Prompt Audit Log
-- ============================================
-- Tracks changes to user custom prompts for security auditing
-- and rate limiting purposes.
-- Date: 2026-02-01
-- Audit log table for custom prompt changes
CREATE TABLE IF NOT EXISTS prompt_audit_log (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
-- What happened
action VARCHAR(20) NOT NULL CHECK (action IN ('create', 'update', 'delete')),
-- Before/after values (for tracking changes)
old_value TEXT,
new_value TEXT,
-- Request metadata for security analysis
ip_address INET,
user_agent TEXT,
-- Timestamp
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Index for rate limiting queries (recent changes by user)
CREATE INDEX idx_prompt_audit_log_user_recent
ON prompt_audit_log(user_id, created_at DESC);
-- Index for security analysis (by IP)
CREATE INDEX idx_prompt_audit_log_ip
ON prompt_audit_log(ip_address, created_at DESC)
WHERE ip_address IS NOT NULL;
-- Enable Row Level Security
ALTER TABLE prompt_audit_log ENABLE ROW LEVEL SECURITY;
-- Users can only see their own audit log entries
CREATE POLICY "Users can view own audit log"
ON prompt_audit_log
FOR SELECT
USING (auth.uid() = user_id);
-- Only service role can insert (via API)
-- No direct user inserts allowed
CREATE POLICY "Service role can insert audit log"
ON prompt_audit_log
FOR INSERT
WITH CHECK (true);
-- No updates or deletes allowed (append-only audit log)
-- This is enforced by not creating UPDATE or DELETE policies
-- Grant permissions
GRANT SELECT ON prompt_audit_log TO authenticated;
GRANT INSERT ON prompt_audit_log TO service_role;
-- Comments for documentation
COMMENT ON TABLE prompt_audit_log IS 'Audit log for custom prompt changes - tracks all modifications for security analysis';
COMMENT ON COLUMN prompt_audit_log.action IS 'Type of change: create, update, or delete';
COMMENT ON COLUMN prompt_audit_log.old_value IS 'Previous prompt value (null for create)';
COMMENT ON COLUMN prompt_audit_log.new_value IS 'New prompt value (null for delete)';
COMMENT ON COLUMN prompt_audit_log.ip_address IS 'Client IP address for security analysis';
COMMENT ON COLUMN prompt_audit_log.user_agent IS 'Client user agent string';
-- ============================================
-- Cleanup old audit logs (optional job)
-- ============================================
-- Run periodically to remove logs older than 90 days
-- Example: SELECT cleanup_old_prompt_audit_logs();
CREATE OR REPLACE FUNCTION cleanup_old_prompt_audit_logs()
RETURNS INTEGER AS $$
DECLARE
deleted_count INTEGER;
BEGIN
DELETE FROM prompt_audit_log
WHERE created_at < NOW() - INTERVAL '90 days';
GET DIAGNOSTICS deleted_count = ROW_COUNT;
RETURN deleted_count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
COMMENT ON FUNCTION cleanup_old_prompt_audit_logs IS 'Removes audit log entries older than 90 days. Run periodically via cron.';
