Why should you care?

Healthcare PostgresVerse must prove who viewed patient record — audit columns plus session user logging.

See it live — copy this example

Run in pgAdmin or psql.

CREATE TABLE patients_audit (
  audit_id bigserial PRIMARY KEY,
  patient_id bigint,
  action text,
  changed_by text DEFAULT current_user,
  changed_at timestamptz DEFAULT now(),
  row_data jsonb
);

CREATE OR REPLACE FUNCTION audit_patient_changes()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO patients_audit (patient_id, action, row_data)
  VALUES (COALESCE(NEW.patient_id, OLD.patient_id), TG_OP, to_jsonb(COALESCE(NEW, OLD)));
  RETURN COALESCE(NEW, OLD);
END;
$$;

What happened?

• Trigger captures INSERT/UPDATE/DELETE into patients_audit with full row json and current_user.
• Append-only audit supports forensic review.

Practice next

1. Create patients table and audit table.
2. Attach trigger FOR EACH ROW on patients.
3. INSERT/UPDATE/DELETE sample patient; query audit.
4. Add action filter query for DELETE only last 7 days.
5. Compare trigger audit vs logical replication change feed.

Remember

Triggers give row-level audit trail. current_user and inet_client_addr identify session. pgaudit adds statement-level enterprise logging.

PostgresVerse HIPAA review

Auditor requests proof of access to patient 9912; patients_audit answers with timestamps.

Outcome: Hospital passes compliance; redaction policy added for sensitive fields.