A bad row should cost one row, not the whole import.

A decision that could have gone the other way

Nothing obliged me to write down that row-level security does not hold against a superuser connection. The README says it anyway, beside the access control it qualifies. Someone deploying this needs to know which boundary is real and which is a convenience, and a platform handling patient records is the last place to let a security claim stand wider than the mechanism behind it.

Domain problem

Research data arrives as CSV exports and FHIR Bundles that are never uniformly clean. Rejecting a whole batch for one malformed entry wastes the good records; accepting everything corrupts the study. A later question — where did this observation come from, and who was allowed to read it — has to be answerable months after the import ran.

Key engineering decisions

The API writes the import job down before dispatching it, so an upload never holds a request open while records are processed. Redis hands the job to a Celery worker that owns parsing and persistence. Invalid entries become import errors while valid entries persist, and the job settles as completed, partial or failed. The idempotency key combines the payload, the target study and the namespace, so a retried upload is recognised rather than duplicated.

Provenance

Patients, encounters and observations are identified by a namespaced source identity rather than a bare source ID, so two systems can use the same local number without collision. Source rows are kept as append-only records: re-importing a resource already on file records a re-assertion instead of overwriting what was there, which keeps the history of an assertion intact. ResearchSubject sits between a patient and a study rather than inside the patient record, so enrolment is a relationship and not a property.

Access control

Keycloak issues the tokens and the API validates them locally against cached JWKS keys, requiring exactly one of admin, researcher or auditor. Study access is checked in the service layer for every clinical and provenance read. PostgreSQL row-level security is then a second boundary for runtime connections, with the request principal set as transaction-local context after authentication. The limit is stated rather than hidden: RLS does not protect a connection made as a superuser or as the table owner, which is why the API and the worker are meant to connect as a non-owner role.

Testing and delivery

The fast suite runs on SQLite with eager Celery execution and covers imports, validation, authorization, job state transitions and FHIR parsing. The integration suite starts PostgreSQL, Redis and a Celery worker through Testcontainers to exercise migrations, database constraints and JSONB, broker-backed imports, and whether row-level security actually hides what it should. GitHub Actions runs linting, both suites, migration checks, image builds, SBOM generation and an image vulnerability scan.

What this is not

The data is synthetic and the project makes no clinical-use or regulatory-compliance claim. The importer covers Patient, Encounter, Observation and ResearchStudy, not the full FHIR resource model, and there is no FHIR REST or search surface, no bulk export, and no UI. Import payloads stay in PostgreSQL; object storage, retention workflows and malware scanning are not implemented. The committed Compose stack is a local development configuration — TLS, managed secrets, a deployed Keycloak, a non-owner database role, backups and monitoring are operator work.

Design rationale

Writing the job down before the work starts means an upload never has to hold a request open, and a failed batch is still a row someone can inspect afterwards.

Implementation status