Skip to main content

Offline and sync design study (M13-012)

Feeds M30 (bidirectional sync). Written before M30 rather than after, because two of the findings below change what M30 has to build, and one of them changes a table's primary key.

Everything here is measured against main on 2026-09-01, not inferred from the plans.

1. What the existing model already gives us​

M30's premise is that this can be replication rather than a merge engine. That premise holds, and it holds for a reason worth stating precisely rather than trusting:

  • Nothing mutates in place. Rows carry voided, and entities are hidden by @SQLRestriction("voided = false") rather than deleted. A correction is a void plus a new row.
  • Every row carries audit columns (created_at, last_modified_at, version) from AuditTrail.
  • Encounters are per-provider and freeze one hour after submission (M29-001), so two clinicians never edit one encounter.

⚠ The consequence M30 should rely on: a delete never has to propagate as a delete. It propagates as an update that sets voided. A sync protocol that has to replicate deletions has to solve tombstones and resurrection; this one does not.

2. ⚠ Finding: nine tables allocate primary keys from a database sequence, and all nine have a natural key​

669 id columns are UUIDs, safe to generate on both sides. Nine are not, allocating from a database sequence: concept, and eight facility reference tables (facility_type, facility_level, facility_level_option, facility_ownership, facility_ownership_type, facility_operational_status, facility_registration_status, facility_license_status).

⚠ Corrected 2026-09-01, after the PO asked whether concepts are mapped to standards. An earlier draft of this section called the natural-key option expensive and treated concept as the worst case. That was wrong in both directions, and the correction matters because it changes what M30 builds.

Every one of the nine already has a natural key, and for concept the database already enforces it:

uq_concept_source_external_code
  UNIQUE (source_system, external_code) WHERE voided = false AND external_code IS NOT NULL

And the key is total, not partial. Measured: of 55,089 live concepts, 51,283 CIEL and 3,806 local, exactly zero have a null external_code. So (source_system, external_code) identifies every concept in the dictionary, including the locally minted ones, which carry ZHENUS_UHP plus their own code. The eight facility tables each carry code, name and uuid.

So concept is the best-protected of the nine, not the worst, and reconciliation is the cheap option rather than the expensive one:

  • Two cells that both hold CIEL 1234 are holding the same concept by construction, whatever local id each allocated.
  • The seed pins ids explicitly (concept.csv starts concept_id,code,source_system,… with 1,CIEL-1,CIEL,…), so two deployments loading the same seed already agree on ids without any reconciliation at all.
  • A genuine offline collision on the natural key surfaces as a unique-index violation, which is loud. Two cells inventing the same local code for different meanings is caught; two cells inventing different codes is not a conflict.

⚠⚠ The residual hazard is narrower and sharper than an id collision. concept_id appears in 15 columns across the schema, including observations. An observation is only meaningful in a database whose dictionary assigns the same id to the same concept. So:

M30 must not sync a concept_id as an integer. It must carry (source_system, external_code) on the wire and resolve to the local id on ingest. Sending the integer works right up until one side has a concept the other seeded differently, and then it silently records the wrong clinical finding, which is the worst class of failure this platform has.

That is a protocol requirement for M30-003, not a migration, and it costs nothing to honour if it is decided now. No change to concept's primary key is needed or recommended.

3. The real conflict surface is a person merge, not a demographic edit​

M30 identifies "person demographics and the one-hour window" as the conflict surface. Measured against the merge implementation, the sharp case is narrower and worse than a field-level conflict:

  • A merge voids the loser and sets merged_into_person_id; engines hold logical person and patient references with no foreign key, and resolve a stale one through that column.
  • A merge re-points 28 patient-keyed and a further set of person-keyed tables in core, and hands imaging, lab and pharmacy their own re-point through a durable obligation ledger (patient_merge_module_repoint) because that part is not atomic and cannot be.

⚠ So the interesting offline case is: the facility merges A into B while the cloud records a new encounter against A. On reconnect, neither side is wrong and no field conflicts. The encounter is simply attached to a person who no longer exists as a separate identity.

Recommendation for M30-003: replicate the merge as a fact (person_merge plus the merged_into_person_id update), and have the receiving side resolve inbound references through merged_into_person_id at ingest time, exactly as the engines already do at read time. Do not replay the re-point orchestration across the link: it is non-atomic by design, it already has its own resumable ledger, and running it twice from two directions is how rows end up pointing at both records.

4. ⚠ The blind index must agree on a key id, or search diverges silently​

Searchable PII is stored as a keyed blind index whose envelope carries the key id (bi:<blindIndexKeyId>:<hash>, M33-005B). Two consequences for sync:

  • If the facility and the cloud cell hold different blind-index keys, a row synced from one is unfindable on the other. Not an error: a search simply returns nothing, which is indistinguishable from "no such patient".
  • A rehash is not atomic, and its progress is checkpointed per database. Two sides mid-rehash at different points hold different envelopes for the same value.

Recommendation: M30-001's per-facility sync flag should refuse to enable while a rehash is in flight on either side, and the cell's key id should be part of the sync handshake rather than assumed. ⚠ This is a silent failure mode, so it needs an assertion in M30-005's coverage reporting, not a runbook note.

5. Offline duration is bounded by the refresh token, not by storage​

⚠ Measured, and easy to miss: the refresh cookie's Max-Age is 604,800 seconds — seven days. A facility offline for longer than that has every session expire, so "offline for a week" is the practical ceiling regardless of what the sync queue can hold. M30-004's catch-up story should state that ceiling rather than implying an unbounded one.

6. What this study does not settle​

  • Volume. No measurement here of how much a facility generates per day, so the queue's storage and the catch-up window are unquantified. M30-004 needs that number before it picks a batch size.
  • The one-hour mutable window. Two edits inside it, from both sides, remain a genuine conflict. The surface is small, but M30-003 still has to say what happens.
  • Cloud-authored encounters. The cloud is read and write, so an encounter can originate there. Which side is authoritative for an encounter that exists on both is M30-002's question, and this study does not pre-empt it.