HIPAA Compliance Guide

-- Enable encryption extension
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Patient table with encrypted PHI
CREATE TABLE patient (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Encrypted PHI fields
  ssn_encrypted       BYTEA,                           -- Encrypted SSN
  mrn_encrypted       BYTEA,                           -- Encrypted MRN
  first_name_enc      BYTEA,                           -- Encrypted first name
  last_name_enc       BYTEA,                           -- Encrypted last name
  dob_encrypted       BYTEA,                           -- Encrypted date of birth
  phone_encrypted     BYTEA,                           -- Encrypted phone
  email_encrypted     BYTEA,                           -- Encrypted email
  address_encrypted   BYTEA,                           -- Encrypted address
  
  -- Non-PHI fields (can be unencrypted)
  gender              VARCHAR(20),
  status              VARCHAR(20),                     -- active, inactive, deceased
  
  -- Audit fields
  created_at          TIMESTAMP NOT NULL DEFAULT NOW(),
  created_by          UUID NOT NULL,
  updated_at          TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_by          UUID NOT NULL,
  
  -- Soft delete (required for audit trail)
  deleted_at          TIMESTAMP,
  deleted_by          UUID
);

-- Audit log table (REQUIRED for HIPAA)
CREATE TABLE audit_log (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Who
  user_id             UUID NOT NULL,
  user_email          VARCHAR(255) NOT NULL,
  user_role           VARCHAR(100),
  
  -- What
  action              VARCHAR(50) NOT NULL,            -- CREATE, READ, UPDATE, DELETE
  table_name          VARCHAR(100) NOT NULL,
  record_id           UUID,
  
  -- Details
  old_values          JSONB,                           -- Before state
  new_values          JSONB,                           -- After state
  
  -- When
  timestamp           TIMESTAMP NOT NULL DEFAULT NOW(),
  
  -- Where (network info)
  ip_address          INET,
  user_agent          TEXT,
  
  -- Why (optional)
  reason              TEXT
);

CREATE INDEX idx_audit_user ON audit_log(user_id, timestamp DESC);
CREATE INDEX idx_audit_table ON audit_log(table_name, record_id);
CREATE INDEX idx_audit_timestamp ON audit_log(timestamp DESC);

-- Access control table
CREATE TABLE user_access_log (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id             UUID NOT NULL,
  patient_id          UUID NOT NULL,
  access_type         VARCHAR(50),                     -- VIEW, EDIT, EXPORT
  access_reason       TEXT,                            -- Break-glass reason
  accessed_at         TIMESTAMP NOT NULL DEFAULT NOW(),
  session_id          UUID,
  ip_address          INET
);

CREATE INDEX idx_access_patient ON user_access_log(patient_id, accessed_at DESC);
CREATE INDEX idx_access_user ON user_access_log(user_id, accessed_at DESC);

-- Encryption/Decryption functions
CREATE OR REPLACE FUNCTION encrypt_phi(data TEXT, key TEXT)
RETURNS BYTEA AS $$
BEGIN
  RETURN pgp_sym_encrypt(data, key);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION decrypt_phi(data BYTEA, key TEXT)
RETURNS TEXT AS $$
BEGIN
  RETURN pgp_sym_decrypt(data, key);
END;
$$ LANGUAGE plpgsql;

-- Audit trigger function
CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO audit_log (user_id, user_email, action, table_name, record_id, new_values)
    VALUES (
      current_setting('app.user_id')::UUID,
      current_setting('app.user_email'),
      'CREATE',
      TG_TABLE_NAME,
      NEW.id,
      row_to_json(NEW)::JSONB
    );
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit_log (user_id, user_email, action, table_name, record_id, old_values, new_values)
    VALUES (
      current_setting('app.user_id')::UUID,
      current_setting('app.user_email'),
      'UPDATE',
      TG_TABLE_NAME,
      NEW.id,
      row_to_json(OLD)::JSONB,
      row_to_json(NEW)::JSONB
    );
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO audit_log (user_id, user_email, action, table_name, record_id, old_values)
    VALUES (
      current_setting('app.user_id')::UUID,
      current_setting('app.user_email'),
      'DELETE',
      TG_TABLE_NAME,
      OLD.id,
      row_to_json(OLD)::JSONB
    );
    RETURN OLD;
  END IF;
END;
$$ LANGUAGE plpgsql;

-- Apply audit trigger to patient table
CREATE TRIGGER patient_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON patient
FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();