Setting up HIPAA-compliant pharmacy databases

Most pharmacy inventory databases fail their first audit not because the application logic is wrong, but because compliance was bolted on at the application layer instead of being encoded into the schema. If a quantity_on_hand can drop below zero, if an UPDATE can rewrite a controlled-substance row without leaving a trace, or if a dispensing pharmacist’s login can read raw patient identifiers it has no need for, then the database itself is the violation — no amount of careful Python above it can cure a structurally non-compliant store. This page solves exactly that: how to stand up a PostgreSQL database whose schema, roles, and encryption enforce 21 CFR § 1304.21 recordkeeping and the HIPAA Security Rule technical safeguards at the DDL level, so that a non-compliant write is impossible rather than merely discouraged.

This setup is one concrete implementation inside the Pharmacy Security Framework Architecture, which defines the secure ingestion, sync, and audit spine for controlled-substance inventory, and it operates within the broader Core Architecture & DEA Compliance Frameworks. The goal here is narrow and practical: a database a DEA inspector and a HIPAA auditor can both sign off on, built from statements you can paste into psql.

Prerequisites & environment

• PostgreSQL 14+ — the schema uses gen_random_uuid() (built into pgcrypto/core since 13), GENERATED ALWAYS AS ... STORED columns, and row-level CHECK constraints.
• pgcrypto extension for column-level encryption of any field that may carry protected health information (PHI), plus transparent data encryption (TDE) at the storage or filesystem layer (pgcrypto does not encrypt the whole cluster).
• TLS 1.2+ enforced on every connection (sslmode=verify-full on the client, ssl = on with a real CA in postgresql.conf) to satisfy transmission-security obligations.
• Python 3.11+ with psycopg[binary] (psycopg 3) and pydantic>=2 for the typed bootstrap and constraint-verification block.
• Regulatory context you should already hold: 21 CFR § 1304.21 (every controlled-substance receipt and disposition must be exact, contemporaneous, and retrievable), 21 CFR § 1304.04 (record retention), and the HIPAA Security Rule technical safeguards — access control 45 CFR § 164.312(a)(1), audit controls 45 CFR § 164.312(b), integrity 45 CFR § 164.312(c)(1), and transmission security 45 CFR § 164.312(e)(1).

A design rule underpins everything below: PHI and controlled-substance inventory are two data domains, not one table with extra columns. Dispensing records (which patient received which drug) carry PHI; inventory records (how many units of an NDC are on hand) carry DEA recordkeeping obligations. They share an audit log but never share an access grant. The schema makes that separation physical.

Implementation: a compliance-enforcing schema

The DDL below creates the inventory table with constraints that make an out-of-range or unscheduled write fail in the engine, an append-only audit table, and the indexes a DEA report and a HIPAA audit retrieval actually use. Every controlled item is keyed on its canonical NDC-11; the normalization that produces that value is owned by NDC-11 vs NDC-10 Parsing Standards and must run at ingestion, before any row reaches this table.

-- One row per stock-keeping unit of a controlled substance.
-- Constraints enforce DEA schedule validity and non-negative custody at the DDL level.
CREATE TABLE pharmacy_inventory (
    inventory_id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ndc_11           CHAR(11) NOT NULL,                       -- canonical 5-4-2, padded upstream
    dea_schedule     SMALLINT NOT NULL CHECK (dea_schedule IN (2, 3, 4, 5)),
    quantity_on_hand NUMERIC(12, 3) NOT NULL DEFAULT 0
                         CHECK (quantity_on_hand >= 0),       -- custody can never go negative
    lot_number       VARCHAR(32),
    last_reconciled  TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT ndc_11_is_numeric CHECK (ndc_11 ~ '^[0-9]{11}$'),
    CONSTRAINT uq_ndc_lot UNIQUE (ndc_11, lot_number)
);

-- Append-only custody ledger. No UPDATE or DELETE grant is ever issued on this table;
-- the hash_chain column makes any out-of-band tampering detectable.
CREATE TABLE audit_log (
    log_id      BIGSERIAL PRIMARY KEY,
    table_name  VARCHAR(64) NOT NULL,
    record_id   UUID NOT NULL,
    action      VARCHAR(16) NOT NULL
                    CHECK (action IN ('INSERT', 'UPDATE', 'DELETE', 'RECONCILE', 'ADJUST')),
    old_value   JSONB,
    new_value   JSONB,
    operator_id UUID NOT NULL,                -- a role/actor id, NEVER a patient identifier
    ip_address  INET,
    hash_chain  CHAR(64) NOT NULL,            -- SHA-256 of (prev_hash || canonical payload)
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Indexes that DEA reconciliation and HIPAA audit retrieval actually query.
CREATE INDEX idx_inventory_schedule ON pharmacy_inventory (dea_schedule, ndc_11);
CREATE INDEX idx_audit_record       ON audit_log (table_name, record_id, log_id);
CREATE INDEX idx_audit_created      ON audit_log (created_at);

The audit_log table is the integrity control required by 45 CFR § 164.312(c)(1). It is append-only by grant, not just by convention: no role below is given UPDATE or DELETE on it. The hash_chain column chains each entry to the previous one so retroactive edits are detectable; the full SHA-256 chaining routine and its verification logic are specified in Audit Boundary Definition & Scope, which also draws the line between mutations that must be logged and reads that need not be.

Next, the roles. HIPAA’s access-control standard and its “minimum necessary” principle are implemented as least-privilege GRANTs, not application if statements:

-- Role 1: dispensing pharmacist — read/update inventory, but NO access to the ledger
-- and NO direct read of PHI columns (those live in a separate dispensing schema).
CREATE ROLE rx_dispenser NOLOGIN;
GRANT SELECT, UPDATE (quantity_on_hand, lot_number, last_reconciled)
    ON pharmacy_inventory TO rx_dispenser;

-- Role 2: inventory auditor — read-only on everything, write nothing.
CREATE ROLE inventory_auditor NOLOGIN;
GRANT SELECT ON pharmacy_inventory, audit_log TO inventory_auditor;

-- Role 3: audit writer — the only principal that may append to the ledger.
-- It can INSERT but cannot UPDATE or DELETE, so the log stays append-only by grant.
CREATE ROLE audit_writer NOLOGIN;
GRANT SELECT, INSERT ON audit_log TO audit_writer;
GRANT USAGE, SELECT ON SEQUENCE audit_log_log_id_seq TO audit_writer;

-- Revoke the public default so nothing is reachable without an explicit grant.
REVOKE ALL ON pharmacy_inventory, audit_log FROM PUBLIC;

Note the column-scoped UPDATE on rx_dispenser: a dispensing login can change quantity and lot, but cannot rewrite dea_schedule or created_at. That is the database refusing, structurally, to let a routine actor alter the classification that drives 21 CFR § 1304.21 audit cadence — the same schedule mapping enforced by the DEA Schedule II-V Classification Mapping engine.

Finally, a typed Python bootstrap that applies the migration and verifies the constraints actually took effect, so the build fails loudly if a hand-edit weakened the schema:

from __future__ import annotations

import logging
from dataclasses import dataclass

import psycopg  # psycopg 3

logger = logging.getLogger("pharmacy.db.bootstrap")


@dataclass(frozen=True)
class DbConfig:
    """Connection settings. Secrets come from a vault, never source control."""
    host: str
    dbname: str
    user: str
    password: str
    sslmode: str = "verify-full"          # enforce TLS + cert verification (164.312(e)(1))
    sslrootcert: str = "/etc/ssl/certs/pharmacy-ca.pem"

    def dsn(self) -> str:
        return (
            f"host={self.host} dbname={self.dbname} user={self.user} "
            f"password={self.password} sslmode={self.sslmode} "
            f"sslrootcert={self.sslrootcert}"
        )


# Assertions a compliant database MUST satisfy, checked after migration.
_INVARIANTS: tuple[tuple[str, str], ...] = (
    ("non_negative_custody",
     "SELECT count(*) FROM information_schema.check_constraints "
     "WHERE constraint_name = 'pharmacy_inventory_quantity_on_hand_check'"),
    ("schedule_constrained",
     "SELECT count(*) FROM information_schema.check_constraints "
     "WHERE constraint_name = 'pharmacy_inventory_dea_schedule_check'"),
    ("ledger_no_update_grant",
     "SELECT count(*) FROM information_schema.role_table_grants "
     "WHERE table_name = 'audit_log' AND privilege_type IN ('UPDATE', 'DELETE')"),
)


def verify_compliance_invariants(cfg: DbConfig) -> None:
    """Fail closed: raise if any structural compliance guarantee is missing."""
    expected = {
        "non_negative_custody": 1,
        "schedule_constrained": 1,
        "ledger_no_update_grant": 0,   # the ledger must have ZERO update/delete grants
    }
    with psycopg.connect(cfg.dsn()) as conn, conn.cursor() as cur:
        for name, query in _INVARIANTS:
            cur.execute(query)
            got = cur.fetchone()[0]
            if got != expected[name]:
                raise RuntimeError(
                    f"compliance invariant '{name}' failed: expected "
                    f"{expected[name]}, got {got}"
                )
            logger.info("invariant_ok", extra={"invariant": name})

Because the bootstrap fails closed, a CI pipeline that runs verify_compliance_invariants on every migration turns “we think the schema is compliant” into “the deploy cannot proceed unless it is.”

Verification & testing

Two layers of verification matter: that the constraints reject bad data, and that the access grants reject the wrong principal. Both are testable with plain SQL assertions.

import psycopg
import pytest


def test_negative_custody_is_rejected(cfg):
    """quantity_on_hand >= 0 must be enforced by the engine, not the app."""
    with psycopg.connect(cfg.dsn()) as conn, conn.cursor() as cur:
        with pytest.raises(psycopg.errors.CheckViolation):
            cur.execute(
                "INSERT INTO pharmacy_inventory (ndc_11, dea_schedule, quantity_on_hand) "
                "VALUES ('00071015523', 2, -1)"
            )


def test_unscheduled_drug_is_rejected(cfg):
    """A schedule outside II-V (here 6) must violate the CHECK constraint."""
    with psycopg.connect(cfg.dsn()) as conn, conn.cursor() as cur:
        with pytest.raises(psycopg.errors.CheckViolation):
            cur.execute(
                "INSERT INTO pharmacy_inventory (ndc_11, dea_schedule, quantity_on_hand) "
                "VALUES ('00071015523', 6, 10)"
            )


def test_dispenser_cannot_write_ledger(dispenser_cfg):
    """rx_dispenser has no INSERT on audit_log; the grant layer must refuse it."""
    with psycopg.connect(dispenser_cfg.dsn()) as conn, conn.cursor() as cur:
        with pytest.raises(psycopg.errors.InsufficientPrivilege):
            cur.execute(
                "INSERT INTO audit_log (table_name, record_id, action, "
                "operator_id, hash_chain) VALUES ('pharmacy_inventory', "
                "gen_random_uuid(), 'ADJUST', gen_random_uuid(), repeat('0', 64))"
            )

To prove the compliance posture rather than just the logic, capture the structured log emitted by verify_compliance_invariants and confirm it reports each invariant as satisfied — and confirm it contains no PHI, because the audit and bootstrap paths must never touch patient identifiers:

INFO pharmacy.db.bootstrap invariant_ok invariant=non_negative_custody
INFO pharmacy.db.bootstrap invariant_ok invariant=schedule_constrained
INFO pharmacy.db.bootstrap invariant_ok invariant=ledger_no_update_grant

A clean run of all three invariants plus the four rejection tests is the artifact you hand an auditor: the database demonstrably refuses the writes that 21 CFR § 1304.21 and 45 CFR § 164.312 prohibit.

Gotchas & compliance pitfalls

• Superuser bypasses every constraint and grant. CHECK constraints and REVOKEs mean nothing if your application connects as the database cluster owner. Run the app as rx_dispenser/audit_writer; reserve superuser for migrations only.
• pgcrypto is not full-database encryption. Column functions like pgp_sym_encrypt protect specific PHI fields, but the database cluster files and WAL are still plaintext on disk without filesystem/TDE encryption. You need both to satisfy 45 CFR § 164.312(a)(2)(iv); one is not a substitute for the other.
• sslmode=require is not verify-full. require encrypts but does not verify the server certificate, leaving you open to a man-in-the-middle that defeats transmission security. Always pin the CA with verify-full.
• updated_at does not update itself. A bare column default only fires on INSERT. Without a BEFORE UPDATE trigger (or application discipline), last_reconciled and updated_at silently go stale, undermining the “contemporaneous” requirement of DEA recordkeeping.
• An operator id is not a patient id. It is tempting to log “who” with a name pulled from the dispensing record. Keep operator_id a role/actor UUID; putting a patient identifier into audit_log pulls PHI into a table read by inventory auditors who have no minimum-necessary basis for it.
• Offline writes still need the same constraints on replay. Mutations queued during a network partition by the Fallback Routing for Offline Sync subsystem must pass the identical CHECKs and grants when they flush — never open a “trusted” replay path that bypasses the schema.
• NUMERIC, not FLOAT, for custody. Floating-point quantities accumulate rounding error; a Schedule II count that drifts by a fraction is an audit discrepancy. Use fixed-precision NUMERIC for every dispensable quantity.

Frequently Asked Questions

Related

• Pharmacy Security Framework Architecture — parent topic: the secure ingestion, sync, and audit spine this database plugs into.
• Core Architecture & DEA Compliance Frameworks — the architectural foundation for the immutable ledger and audit boundaries.
• Audit Boundary Definition & Scope — the SHA-256 hash-chaining routine and the rules for which mutations the audit_log must capture.
• NDC-11 vs NDC-10 Parsing Standards — the normalization that produces the canonical ndc_11 key before any row reaches this schema.
• DEA Schedule II-V Classification Mapping — how dea_schedule drives reconciliation cadence and retention.