The pattern most clinical apps get wrong

Most multi-tenant healthcare apps I’ve audited handle tenant isolation in the application:

// Pseudo-code from a typical clinical SaaS
func GetPatients(tenantID string) []Patient {
    rows := db.Query("SELECT * FROM patients WHERE tenant_id = ?", tenantID)
    return scanPatients(rows)
}

That works until one of three things happens:

1. A new query path forgets the WHERE tenant_id = ?. Now it’s a cross-tenant data leak in production.
2. An ORM generates a query without the filter. Same outcome.
3. A reporting tool, BI dashboard, or ad-hoc analyst connects to the DB. They have no concept of “tenant” — they see everything.

Application-level tenant isolation is fail-open. A single bug, a single new code path, or a single legacy tool means everyone’s data is everyone else’s data.

The fix is to push tenant isolation down to the database: PostgreSQL row-level security (RLS). I just shipped this for Bodh, the open-source medical multi-agent platform I’ve been building in Go. The pattern is worth writing up — it’s the difference between “we should be tenant-isolated” and “we are, demonstrably, tenant-isolated.”

The architecture

Three tables, all with a tenant_id column:

CREATE TABLE interaction_records (
    id           UUID PRIMARY KEY,
    tenant_id    TEXT NOT NULL,
    case_id      TEXT NOT NULL,
    patient_id   TEXT,                  -- opaque, NEVER an MRN
    payload_ct   BYTEA NOT NULL,        -- ciphertext-ready (plaintext today)
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE patient_records (
    tenant_id    TEXT NOT NULL,
    patient_id   TEXT NOT NULL,
    payload_ct   BYTEA NOT NULL,        -- ciphertext-ready
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (tenant_id, patient_id)
);

CREATE TABLE audit_events (
    id           UUID PRIMARY KEY,
    tenant_id    TEXT NOT NULL,
    at           TIMESTAMPTZ NOT NULL,
    kind         TEXT NOT NULL,
    -- field-narrow schema: enough to reconstruct, never enough to leak
    agent_id, message_id, from_id, to_id, type,
    patient_id, case_id, trace_id,
    decision, reason, reviewer_id, error_text TEXT
);

A dedicated database role with NOBYPASSRLS:

CREATE ROLE bodh_app LOGIN PASSWORD '<from secrets manager>' NOBYPASSRLS;

-- Minimum necessary grants (HIPAA Privacy Rule §164.502(b) applied to DB privileges)
GRANT SELECT, INSERT, UPDATE, DELETE ON interaction_records TO bodh_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON patient_records TO bodh_app;
GRANT SELECT, INSERT                  ON audit_events TO bodh_app;  -- append-only at DB

RLS enabled and forced:

ALTER TABLE interaction_records ENABLE ROW LEVEL SECURITY;
ALTER TABLE interaction_records FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON interaction_records
    USING       (tenant_id = current_setting('app.tenant_id', true))
    WITH CHECK  (tenant_id = current_setting('app.tenant_id', true));

(FORCE ROW LEVEL SECURITY makes the policy apply even to the table owner — important when the owner role might be used for migrations and you don’t want the migrator role bypassing policies during data loads.)

The application side reads its current tenant from context and sets it per query:

func (s *Store) withTenant(ctx context.Context, tenantID string,
    fn func(pgx.Tx) error) error {

    return s.pool.BeginTx(ctx, pgx.TxOptions{}, func(tx pgx.Tx) error {
        // SET LOCAL — scoped to the transaction; cleared at commit/rollback
        _, err := tx.Exec(ctx,
            "SELECT set_config('app.tenant_id', $1, true)", tenantID)
        if err != nil {
            return err
        }
        return fn(tx)
    })
}

That’s the whole pattern. Every query goes through withTenant; the application cannot forget the filter because there’s no way to construct a query that bypasses the policy.

Five things RLS gives you that app-layer filtering does not

1. The policy is the spec

Read the migration file. That’s the security model. Not the application code, not a Confluence page, not “trust me, every query has WHERE tenant_id.”

When an auditor asks “how do you enforce tenant isolation?”, the answer is one SQL statement they can read in 30 seconds.

2. Defence against future code

The new junior engineer six months from now who adds a new query path doesn’t have to remember to add the tenant filter. The database enforces it. They literally cannot write a query that leaks cross-tenant data while connected as bodh_app.

This is the difference between a guideline (“always filter by tenant”) and an invariant (“tenant filter is impossible to bypass”). HIPAA risk-management programs love invariants.

3. Defence against introspection tools

The BI tool connects as bodh_app. The reporting query connects as bodh_app. The ad-hoc analyst’s psql shell connects as bodh_app. None of them sees cross-tenant data unless they explicitly SET app.tenant_id = '...' to the tenant they’re meant to see.

The NOBYPASSRLS on the role means even a SECURITY DEFINER function written by the analyst can’t bypass — only BYPASSRLS roles (postgres superuser, dedicated DBA accounts) can.

4. Tests can verify the invariant

func TestTenantIsolation(t *testing.T) {
    store := setupPostgresWithTestcontainers(t)

    // Insert under tenant-a
    store.UpsertPatient(ctx, "tenant-a", PatientRecord{
        PatientID: "shared-id", Age: 30,
    })
    // Insert with same patient_id under tenant-b
    store.UpsertPatient(ctx, "tenant-b", PatientRecord{
        PatientID: "shared-id", Age: 40,
    })

    // Each tenant sees only its own row
    aPatients := store.ListPatients(ctx, "tenant-a")
    if aPatients[0].Age != 30 { t.Fatal("tenant-a sees wrong row") }

    bPatients := store.ListPatients(ctx, "tenant-b")
    if bPatients[0].Age != 40 { t.Fatal("tenant-b sees wrong row") }

    // BYPASSRLS sanity check — fails loudly if RLS isn't being enforced
    rows := superuserPool.Query("SELECT count(*) FROM patient_records")
    if count(rows) != 2 { t.Fatal("expected 2 rows under BYPASSRLS") }
}

You can integration-test the invariant. CI fails if RLS ever regresses. That’s load-bearing.

5. The audit table goes one level further

GRANT SELECT, INSERT ON audit_events TO bodh_app;
-- Notably absent: UPDATE, DELETE

The application role cannot modify or delete audit events. Append-only is enforced by the database GRANT, not by the application’s intentions.

HIPAA §164.312(b) (“Audit controls — Implement hardware, software, and procedural mechanisms that record and examine activity in information systems that contain or use electronic protected health information”) doesn’t say “append-only”, but every audit framework operationalising HIPAA does: HITRUST, SOC 2, NIST SP 800-66, 21 CFR Part 11. Append-only at the database is the cleanest place to enforce it.

A compromised application credential can write garbage events but cannot rewrite history. The audit log remains a credible forensic record.

What about the PHI itself?

The schema types PHI columns as BYTEA with a _ct suffix:

payload_ct BYTEA NOT NULL,  -- ciphertext-ready (plaintext today)

Today they’re plaintext JSON. The next PR adds column-level encryption via pgcrypto or KMS envelope encryption — without a schema migration, because the column is already typed for it.

The envelope-encryption pattern:

1. KMS holds the Customer Master Key (CMK). It never leaves the KMS.
2. The application asks the KMS to generate a Data Encryption Key (DEK) — a 256-bit AES key wrapped (encrypted) under the CMK.
3. The application uses the DEK to encrypt the PHI payload with AES-256-GCM.
4. The wrapped DEK is stored alongside the ciphertext.
5. To read: ask the KMS to unwrap the DEK, then decrypt the payload locally.

The CMK never appears in process memory. The DEK is rotated per record. Auditors love this pattern — the audit log shows “asked KMS to unwrap DEK for record X at time T by principal P” without any plaintext key material ever existing outside the HSM.

Why this matters for HIPAA breach notification

The HIPAA Breach Notification Rule (45 CFR Part 164 Subpart D) has a Safe Harbor exclusion: a breach of properly encrypted data per HHS Guidance (FIPS 140-2/3 cryptographic modules, NIST-approved algorithms) does not count as a breach.

The notification timeline that would otherwise apply:

Properly encrypted data lets you skip all three. That’s why TLS 1.3 in transit + KMS-backed AES-256-GCM at rest isn’t optional — it’s the difference between an incident response and a regulator-facing notification.

The _ct column suffix is a load-bearing convention: it tells every future maintainer “this column is ciphertext (or about to be).” It’s documentation as data.

What about hard-delete?

GDPR Article 17 (right to erasure) and several US state privacy laws give patients the right to have their data deleted. HIPAA, by contrast, has retention requirements (typically 6 years).

These can conflict. The architecture has to support both. Bodh’s pattern:

func (s *Store) DeletePatient(ctx context.Context, tenantID, patientID string) error {
    return s.withTenant(ctx, tenantID, func(tx pgx.Tx) error {
        // 1. Hard-delete from patient_records and interaction_records
        if _, err := tx.Exec(ctx,
            "DELETE FROM patient_records WHERE tenant_id = $1 AND patient_id = $2",
            tenantID, patientID); err != nil { return err }

        if _, err := tx.Exec(ctx,
            "DELETE FROM interaction_records WHERE tenant_id = $1 AND patient_id = $2",
            tenantID, patientID); err != nil { return err }

        // 2. Audit events: governed by HIPAA retention, NOT deleted by this call.
        //    Operator-only SQL (via DBA account) performs retention-window deletes.
        //    The Reason field documents the deletion request itself, creating a
        //    record of the deletion without preserving the deleted PHI.
        _, err := tx.Exec(ctx, `
            INSERT INTO audit_events (id, tenant_id, at, kind, patient_id, reason)
            VALUES ($1, $2, $3, 'message', $4, 'GDPR Article 17 erasure request executed')`,
            uuid.NewString(), tenantID, time.Now().UTC(), patientID)
        return err
    })
}

Two records of the deletion remain: an audit event saying “this deletion happened” with no PHI, and the retention-policy-governed audit history of what that patient’s activity was while in the system. The operator can argue both HIPAA compliance (audit retention) and GDPR compliance (PHI erasure) with a clean separation.

This is the kind of tension you discover when you try to satisfy both regulations in the same database. The pattern documented in pkg/persistence/postgres/README.md is one way to resolve it.

What it costs

Two real trade-offs:

1. Performance

Every query carries the set_config round-trip in a transaction. PostgreSQL processes ~10-50µs per set_config call. For high-QPS read-heavy workloads, you might benchmark with and without RLS and decide. For clinical workloads (~10-100 QPS per app instance is typical), the overhead is invisible.

For batch jobs (panel rule evaluation across 10k patients), SET LOCAL is a one-time cost per transaction. Run the whole batch in one transaction with the tenant set once.

2. Connection pool implications

SET LOCAL is scoped to the transaction. Connection pools that hand the same connection to different requests across tenants need to either:

• Use SET LOCAL (transaction-scoped — the safe pattern Bodh uses), OR
• Use RESET ROLE / RESET app.tenant_id between leases (extra round-trip)

The pattern I’ve been using with pgx works. Don’t try to use session-scoped SET with a shared pool — that’s how cross-tenant leaks happen via a misconfigured connection lease.

What’s not RLS’s job

RLS doesn’t protect against:

• A compromised BYPASSRLS superuser account
• A malicious DBA with direct DB access
• A backup file leaked to a third party (if columns aren’t encrypted)
• An application that legitimately needs to switch tenants mid-request (multi-tenant admin tooling)

Each of these needs a separate control:

• Privileged access management (PAM, jit-elevation, time-limited credentials) for the superuser problem.
• Vault / sealed-secrets for credential rotation.
• Column-level encryption for the backup problem.
• Carefully audited admin endpoints for the multi-tenant case, with BreakGlassPolicy-style audit on every cross-tenant access.

RLS is the foundation. The other controls layer on top.

Try it

The Postgres backend ships in pkg/persistence/postgres. Migrations live in db/migrations/. The integration tests in pkg/persistence/postgres/postgres_test.go use testcontainers-go to spin up a real Postgres and prove the RLS invariant. Skip the suite cleanly with t.Skip when Docker isn’t available — green CI without Docker.

git clone https://github.com/PratikDhanave/bodh.git
cd bodh

# Run the integration tests against a real Postgres (needs Docker)
go test ./pkg/persistence/postgres/... -v

# Point cmd/care at a real Postgres
docker run -d --name bodh-pg -p 5432:5432 -e POSTGRES_PASSWORD=postgres postgres:16
psql -h localhost -U postgres -c "CREATE DATABASE bodh;"
BODH_DB_DSN="postgres://bodh_app:postgres@localhost:5432/bodh?sslmode=disable" \
BODH_TENANT_ID=tenant-pilot \
go run ./cmd/care -addr=:8088

Schema, RLS policies, and the GDPR Article 17 path are documented in pkg/persistence/postgres/README.md. Full deployment guide in docs/deployment.md.

Repo: github.com/PratikDhanave/bodh

If you’re building HIPAA-aligned multi-tenant infrastructure and want to compare the RLS pattern, the BAA chain considerations, or the GDPR vs HIPAA retention tension — open issues, PRs, or DMs welcome.

Bodh is a research and engineering reference. Not under a Business Associate Agreement. Not certified for clinical deployment. The architecture described above is the target for a production HIPAA-aligned system; the codebase is not audited.

HIPAA #PostgreSQL #DatabaseSecurity #RLS #Compliance #HealthcareIT #PrivacyEngineering #Go #BackendEngineering #OpenSource