Bulk Export: SQLite and PostgreSQL skip the _since filter for Patient resources in fetch_patient_compartment_batch

[smunini]

Summary

In fetch_patient_compartment_batch, the SQLite and PostgreSQL backends apply the _since filter to every resource type except Patient. The resource_type == "Patient" branch builds its query without any last_updated bound, so a patient-level or group-level export with _since emits Patient resources that were never modified inside the requested window.

MongoDB and S3 both apply _since in that branch. This is a two-backend divergence from behavior the other two already establish as correct.

Current behavior

GET /Group/cohort-7/$export?_since=2026-06-01T00:00:00Z
Prefer: respond-async

On SQLite or PostgreSQL, the Patient.ndjson output contains every member of the group, including patients whose Patient resource has not changed since 2026-06-01. Every other type in the same export — Observation, Encounter, and so on — is correctly filtered.

The same happens for a patient-level export that names patients explicitly (POST /Patient/$export with patient parameters).

Expected behavior

The Patient branch applies _since exactly as the other branches do, matching MongoDB and S3. A _since export contains only Patient resources modified at or after the bound.

Evidence

SQLite — crates/persistence/src/backends/sqlite/bulk_export.rs:1266-1292, the resource_type == "Patient" branch. The query is built with a tenant, type, and id-list predicate, then a cursor clause, then ORDER BY — no request.since anywhere:

if resource_type == "Patient" {
    let mut query = format!(
        "SELECT id, data, last_updated FROM resources
         WHERE tenant_id = ?1 AND resource_type = ?2 AND id IN ({}) AND is_deleted = 0",
        placeholders.join(",")
    );
    ...
    if let Some(cursor) = cursor { ... query.push_str(" AND (last_updated, id) > (?, ?)"); ... }
    query.push_str(" ORDER BY last_updated, id");

The non-Patient branch immediately below it does apply the filter (sqlite/bulk_export.rs:1360):

if let Some(since) = request.since {
    query.push_str(" AND last_updated >= ?");
    params_vec.push(Box::new(since.to_rfc3339()));
}

PostgreSQL — crates/persistence/src/backends/postgres/bulk_export.rs:1192-1222, the same shape and the same omission:

if resource_type == "Patient" {
    let mut sql = "SELECT id, data, last_updated FROM resources
         WHERE tenant_id = $1 AND resource_type = $2 AND id = ANY($3::text[]) AND is_deleted = FALSE".to_string();
    let param_idx = 4;
    if let Some(cursor) = cursor { ... }
    sql.push_str(&format!(" ORDER BY last_updated, id LIMIT {}", batch_size + 1));

MongoDB gets it right — crates/persistence/src/backends/mongodb/bulk_export.rs:291-297:

if resource_type == "Patient" {
    let mut filter = doc! { "tenant_id": tenant_id, "resource_type": "Patient",
                            "is_deleted": false, "id": { "$in": patient_ids.to_vec() } };
    if let Some(since) = request.since {
        filter.insert("last_updated", doc! { "$gte": chrono_to_bson(since) });
    }

S3 gets it right — crates/persistence/src/backends/s3/bulk_export.rs:273-287 applies since (and until) uniformly, before the compartment test, so the Patient case is covered by the same code path as every other type:

if let Some(since) = request.since && resource.last_modified() < since { continue; }
if let Some(until) = request.until && resource.last_modified() > until { continue; }
let in_compartment = if resource_type == "Patient" {
    patient_id_set.contains(resource.id())
} else {
    resource_in_patient_compartment(resource.content(), &patient_ref_set)
};

The incoming patient_ids are not pre-filtered, so the omission is not compensated for upstream. crates/persistence/src/core/bulk_export_worker.rs:493-527 reaches this method by two routes where the id list has no _since applied to it:

• Group-level — group_patient_ids comes from group-membership resolution.
• Patient-level with an explicit patient parameter — the ids come straight off request.patient_refs.

(list_patient_ids does apply _since — sqlite/bulk_export.rs:1211, postgres/bulk_export.rs:1136 — but the worker uses that path only for the unfiltered patient-level export, which routes to fetch_export_batch instead.)

No test covers _since against the Patient branch.

Suggested approach

Add the request.since clause to the resource_type == "Patient" branch in both backends, matching the non-Patient branch directly below it in each file.

• SQLite (sqlite/bulk_export.rs:1266) — the branch uses positional ?N placeholders (?1, ?2, then ?3.. for the id list), so the new bind has to be numbered after the id list rather than appended blindly. The non-Patient branch uses anonymous ?; do not copy its clause verbatim into the numbered query.
• PostgreSQL (postgres/bulk_export.rs:1192) — param_idx is already tracked and starts at 4; add the bound and advance it before the cursor clause consumes $4/$5. Bind the DateTime<Utc> directly — tokio-postgres will not bind String/&str to TIMESTAMPTZ, and a ::timestamptz cast does not change that.
• Keep the bound inclusive (>=), matching every other site.

Acceptance criteria

• SQLite and PostgreSQL apply _since in the Patient branch of fetch_patient_compartment_batch.
• A test per backend: a group of two patients, one modified after the bound and one not, exported with _since — only the modified one appears in Patient.ndjson, while the compartment types stay correctly filtered.
• Equivalent coverage for the patient-level path that names patients via the patient parameter.
• All four backends agree on _since behavior for the Patient type.
• Pagination still works: the cursor clause and the new bound coexist without dropping or repeating rows across batches.

Scope / out of scope

Out of scope: _until, which is missing from these backends entirely — #657. That fix touches the same two branches, so the two are worth landing together; whichever lands first should leave the other's site obvious.

Related: #656 (the UI _until control).