database/postgresql/001_schema.sql

-- PHI Audit Log Schema for HIPAA Compliance
-- This table logs all PHI scrubbing operations for audit trails

CREATE TABLE IF NOT EXISTS phi_audit_log (
    id SERIAL PRIMARY KEY,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    user_id VARCHAR(255), -- If applicable, for user tracking
    session_id VARCHAR(255), -- For session-based tracking
    operation VARCHAR(50) NOT NULL, -- e.g., 'scrub', 'redact', 'anonymize'
    input_hash VARCHAR(64) NOT NULL, -- SHA-256 hash of input text
    output_hash VARCHAR(64), -- SHA-256 hash of output text
    phi_types_found TEXT[], -- Array of PHI types detected
    redaction_count INTEGER DEFAULT 0, -- Number of redactions performed
    processing_time_ms INTEGER, -- Time taken for operation
    ip_address INET, -- Client IP for audit
    user_agent TEXT, -- Client user agent
    success BOOLEAN DEFAULT TRUE, -- Whether operation succeeded
    error_message TEXT -- Error details if failed
);

-- Index for efficient querying by timestamp
CREATE INDEX IF NOT EXISTS idx_phi_audit_log_timestamp ON phi_audit_log (timestamp);

-- Index for input hash lookups
CREATE INDEX IF NOT EXISTS idx_phi_audit_log_input_hash ON phi_audit_log (input_hash);

-- Index for session tracking
CREATE INDEX IF NOT EXISTS idx_phi_audit_log_session_id ON phi_audit_log (session_id);

-- Partitioning by month for large-scale deployments (optional)
-- This can be enabled if audit logs grow very large
-- CREATE TABLE phi_audit_log_y2024m01 PARTITION OF phi_audit_log FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- Grant permissions (adjust as needed for your deployment)
-- GRANT SELECT, INSERT ON phi_audit_log TO your_app_user;
-- GRANT USAGE ON SEQUENCE phi_audit_log_id_seq TO your_app_user;