PHI Access Audit Log: Record Who Viewed Which Patient Record

-- records/migrations/1_records.up.sql
CREATE TABLE patients (
    id        BIGSERIAL PRIMARY KEY,
    full_name TEXT NOT NULL,
    dob       DATE NOT NULL
);

-- Which staff members treat which patients.
CREATE TABLE care_team (
    patient_id BIGINT NOT NULL REFERENCES patients (id),
    user_id    TEXT NOT NULL,
    PRIMARY KEY (patient_id, user_id)
);

CREATE TABLE phi_access_log (
    id         BIGSERIAL PRIMARY KEY,
    at         TIMESTAMPTZ NOT NULL DEFAULT now(),
    actor_id   TEXT NOT NULL,
    actor_role TEXT NOT NULL,
    patient_id BIGINT NOT NULL,
    action     TEXT NOT NULL,  -- view_record, view_access_log, ...
    outcome    TEXT NOT NULL CHECK (outcome IN ('allowed', 'denied', 'break_glass')),
    reason     TEXT,
    request_id TEXT NOT NULL
);
CREATE INDEX phi_access_log_patient ON phi_access_log (patient_id, at);
CREATE INDEX phi_access_log_actor ON phi_access_log (actor_id, at);

-- Append-only: no updates, no truncation, and deletes only from the
-- retention job, for rows past the retention period.
CREATE FUNCTION phi_access_log_guard() RETURNS trigger AS $$
BEGIN
    IF TG_OP = 'DELETE'
       AND current_setting('audit.retention_purge', true) = 'on'
       AND OLD.at < now() - interval '6 years' THEN
        RETURN OLD;
    END IF;
    RAISE EXCEPTION 'phi_access_log is append-only (%)', TG_OP;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER phi_access_log_no_change
    BEFORE UPDATE OR DELETE ON phi_access_log
    FOR EACH ROW EXECUTE FUNCTION phi_access_log_guard();

CREATE TRIGGER phi_access_log_no_truncate
    BEFORE TRUNCATE ON phi_access_log
    FOR EACH STATEMENT EXECUTE FUNCTION phi_access_log_guard();

-- The application role can insert and select, but cannot delete.
-- Purge runs as a dedicated role through a definer function, so
-- SET LOCAL from an application connection cannot authorize a delete.
CREATE ROLE phi_audit_purge NOLOGIN;
REVOKE DELETE, UPDATE, TRUNCATE ON phi_access_log FROM PUBLIC;
GRANT SELECT, INSERT ON phi_access_log TO CURRENT_USER;
GRANT DELETE ON phi_access_log TO phi_audit_purge;

CREATE FUNCTION purge_expired_phi_access_log() RETURNS integer
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
    n integer;
BEGIN
    PERFORM set_config('audit.retention_purge', 'on', true);
    DELETE FROM phi_access_log WHERE at < now() - interval '6 years';
    GET DIAGNOSTICS n = ROW_COUNT;
    RETURN n;
END;
$$;

ALTER FUNCTION purge_expired_phi_access_log() OWNER TO phi_audit_purge;
REVOKE ALL ON FUNCTION purge_expired_phi_access_log() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION purge_expired_phi_access_log() TO CURRENT_USER;

// records/records.go
package records

import (
	"context"
	"errors"
	"time"

	"encore.app/auth"
	"encore.dev"
	encauth "encore.dev/beta/auth"
	"encore.dev/beta/errs"
	"encore.dev/storage/sqldb"
)

var db = sqldb.NewDatabase("records", sqldb.DatabaseConfig{
	Migrations: "./migrations",
})

type GetParams struct {
	// Set only for emergency access outside the care team.
	BreakGlassReason string `query:"break_glass_reason" encore:"sensitive"`
}

type Patient struct {
	ID       int64     `json:"id"`
	FullName string    `json:"full_name" encore:"sensitive"`
	DOB      time.Time `json:"dob" encore:"sensitive"`
}

// Get returns a patient record and logs the access in the same
// transaction. If the log row cannot be written, no PHI is returned.
//
//encore:api auth method=GET path=/patients/:id
func Get(ctx context.Context, id int64, p *GetParams) (*Patient, error) {
	actor, _ := encauth.UserID()
	role := encauth.Data().(*auth.Data).Role

	tx, err := db.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()

	var onTeam bool
	if err := tx.QueryRow(ctx, `
		SELECT EXISTS (SELECT 1 FROM care_team WHERE patient_id = $1 AND user_id = $2)
	`, id, string(actor)).Scan(&onTeam); err != nil {
		return nil, err
	}

	outcome := "allowed"
	switch {
	case role != "clinician":
		outcome = "denied"
	case !onTeam && p.BreakGlassReason == "":
		outcome = "denied"
	case !onTeam:
		outcome = "break_glass"
	}
	if err := logAccess(ctx, tx, string(actor), role, id, "view_record", outcome, p.BreakGlassReason); err != nil {
		return nil, err
	}

	if outcome == "denied" {
		// Commit so the denied attempt stays in the log.
		if err := tx.Commit(); err != nil {
			return nil, err
		}
		return nil, &errs.Error{Code: errs.PermissionDenied, Message: "not on this patient's care team"}
	}

	var pt Patient
	err = tx.QueryRow(ctx, `
		SELECT id, full_name, dob FROM patients WHERE id = $1
	`, id).Scan(&pt.ID, &pt.FullName, &pt.DOB)
	if errors.Is(err, sqldb.ErrNoRows) {
		if err := tx.Commit(); err != nil {
			return nil, err
		}
		return nil, &errs.Error{Code: errs.NotFound, Message: "patient not found"}
	} else if err != nil {
		return nil, err
	}
	return &pt, tx.Commit()
}

func logAccess(ctx context.Context, tx *sqldb.Tx, actor, role string, patientID int64, action, outcome, reason string) error {
	requestID := ""
	if t := encore.CurrentRequest().Trace; t != nil {
		requestID = t.TraceID
	}
	_, err := tx.Exec(ctx, `
		INSERT INTO phi_access_log
		    (actor_id, actor_role, patient_id, action, outcome, reason, request_id)
		VALUES ($1, $2, $3, $4, $5, NULLIF($6, ''), $7)
	`, actor, role, patientID, action, outcome, reason, requestID)
	return err
}

// records/accesslog.go
package records

import (
	"context"
	"time"

	"encore.app/auth"
	encauth "encore.dev/beta/auth"
	"encore.dev/beta/errs"
	"encore.dev/cron"
)

type Entry struct {
	At      time.Time `json:"at"`
	ActorID string    `json:"actor_id"`
	Action  string    `json:"action"`
	Outcome string    `json:"outcome"`
	Reason  *string   `json:"reason" encore:"sensitive"`
}

type History struct {
	Entries []Entry `json:"entries"`
}

// AccessHistory answers "who looked at this patient's record". Reading
// the log is itself logged.
//
//encore:api auth method=GET path=/patients/:id/access-log
func AccessHistory(ctx context.Context, id int64) (*History, error) {
	actor, _ := encauth.UserID()
	role := encauth.Data().(*auth.Data).Role

	tx, err := db.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()

	outcome := "allowed"
	if role != "privacy_officer" {
		outcome = "denied"
	}
	if err := logAccess(ctx, tx, string(actor), role, id, "view_access_log", outcome, ""); err != nil {
		return nil, err
	}
	if outcome == "denied" {
		if err := tx.Commit(); err != nil {
			return nil, err
		}
		return nil, &errs.Error{Code: errs.PermissionDenied, Message: "privacy officers only"}
	}

	rows, err := tx.Query(ctx, `
		SELECT at, actor_id, action, outcome, reason
		FROM phi_access_log
		WHERE patient_id = $1
		ORDER BY at DESC
		LIMIT 500
	`, id)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	h := &History{}
	for rows.Next() {
		var e Entry
		if err := rows.Scan(&e.At, &e.ActorID, &e.Action, &e.Outcome, &e.Reason); err != nil {
			return nil, err
		}
		h.Entries = append(h.Entries, e)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	rows.Close()
	return h, tx.Commit()
}

var _ = cron.NewJob("phi-access-log-retention", cron.JobConfig{
	Title:    "Delete access log rows past the retention period",
	Every:    24 * cron.Hour,
	Endpoint: PurgeExpired,
})

// PurgeExpired is the only application entry point that can delete rows.
// The definer function, not this connection, sets the session flag.
//
//encore:api private
func PurgeExpired(ctx context.Context) error {
	_, err := db.Exec(ctx, `SELECT purge_expired_phi_access_log()`)
	return err
}