Audit Log Trigger Function

-- Audit log table
CREATE TABLE audit_log (
  id         bigserial PRIMARY KEY,
  table_name text NOT NULL,
  operation  text NOT NULL,
  row_id     text,
  old_data   jsonb,
  new_data   jsonb,
  changed_by text DEFAULT current_user,
  changed_at timestamptz DEFAULT NOW()
);

CREATE INDEX idx_audit_table ON audit_log (table_name, changed_at);

-- Generic audit trigger function
CREATE FUNCTION audit_trigger_fn() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO audit_log (table_name, operation, row_id, new_data)
    VALUES (TG_TABLE_NAME, 'INSERT', NEW.id::text, to_jsonb(NEW));
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit_log (table_name, operation, row_id, old_data, new_data)
    VALUES (TG_TABLE_NAME, 'UPDATE', NEW.id::text, to_jsonb(OLD), to_jsonb(NEW));
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO audit_log (table_name, operation, row_id, old_data)
    VALUES (TG_TABLE_NAME, 'DELETE', OLD.id::text, to_jsonb(OLD));
    RETURN OLD;
  END IF;
END;
$$ LANGUAGE plpgsql;

-- Attach to any table
CREATE TRIGGER audit_users
  AFTER INSERT OR UPDATE OR DELETE ON users
  FOR EACH ROW EXECUTE FUNCTION audit_trigger_fn();

-- Query audit history for a specific record
SELECT operation, old_data, new_data, changed_at
FROM audit_log
WHERE table_name = 'users' AND row_id = '42'
ORDER BY changed_at DESC;