Introduction

This document details the Sync V7 specification.

The audience is

• For public consumption by opensource contributors

• Inducting new staff

• A technical reference for current and future work on sync.

Conventions

Draft: to indicate work in progress.

Discuss is something that needs deciding/resolving

References

OMS readme sync (not up to date): https://github.com/msupply-foundation/open-msupply/blob/faed9cf52c467d8c04caa8df28e7c64e292aa0c5/server/service/src/sync/README.md

Non-technical Overview

Terminology

This terminology is used by us. It’s not perfect. We don’t waste time arguing about it, but we do spend time trying to remain consistent.

The Central server is the server (usually in the cloud) that coordinates the system, and also has a copy of every site’s data.

A Remote site is a facility (warehouse, hospital, health centre) that is using Open mSupply.

A Store is an entity that manages its own stock and transactions. So a remote site might have one or more than one store. For example, a hospital might have a mini warehouse and a dispensary, each managing their own stock, with their own staff using the system.

Offline vs Online

We often talk about Open mSupply as being “offline first,” as opposed to an “online” system.

This means that the system will work without an always-on internet connection to the cloud server that stores the country’s data (be that in-country, or in a cloud data centre in another country). Intermittent internet is still required for the offline-first site to send and receive orders from other sites, and to update the cloud server with data to enable central reporting.¹

The sync (short for synchronisation) system is how Open mSupply implements offline-first functionality.

Sync in simple terms

Sync is a Hub and Spokes model, or Star Network:

Principles

• One way data movement. Almost all data is conceived of as being “owned” by either the central or a remote site.

  • Data that is owned by the central server is usually called Central Data (The supply chain industry talks about Master Data - same thing). It can only be edited centrally, and when edited (or created), changes flow from the central server to remote sites.The simplest example is items (products). If you want a reporting system for all sites, they all need to be using the same items.

  • Remote data is primarily transactions (invoices and their invoice lines) that each site creates, along with changes in stock levels that happen as the transactions are made. Each remote site has its own remote data.

• All connections are initiated by remote sites. They connect to the central server on a regular schedule and do two things

  • Push: the remote site sends all the changes it has made since the last sync to the central server.

  • Pull: our term for the remote site saying “hey, have you got any data updates for me?”²

• Transfers are the sending and receiving of invoices between stores on different Remote Sites. All transfers go via the central server. Because each remote site “owns” its own data, you can not have the same invoice on two sites. Instead, a copy of the invoice is made and sent to the remote site, and the copy is adjusted to reflect the change in perspective from sender to receiver. The types of transfers are:

  • If goods are being shipped, the sending store makes an outbound shipment invoice, and the receiving store receives an inbound shipment invoice.

  • If an order is being made, the store requisition stock makes a requisition whose status is “request” and the store receiving the stock also makes a copy of the requisition with a status of “response”

Central server notes

• All data from all remote sites is present on the Central Server. This acts as an effective backup for all remote sites as in the event of hardware/data loss; a new device/installation can synchronise all data from the Central Server, retrieving all data in the state it was when the remote site last synchronised. We call this initialisation

• Because the central server has a copy of a store from a remote site, but also can have stores that were created on the central server (see footnote 1), we distinguish between active stores and inactive stores. Remote sites only have active stores. The central server’s copy of each store from a remote site is “inactive”

Exceptions to the One Direction rule

• Patients turn up at remote sites, and although they are master data (we don’t want the same patient in the system twice if they visit 2 remote sites), we need to let remote sites add a new patient.

• Name Properties

• File Sync

• Assets can be edited on both the central server and the remote site

  • Explanation of what the mode is: is it “latest wins”?

Here ends the non-technical section. The Sync Types tab has more detail for developers.

Technical requirements

• Enable offline first
• Ensure data consistency
• Enable sufficient central server performance for the following max site (or a plan to enable it in the future):
  • At the high end, Nigeria has 40,000 sites and 220 million people. Let’s say each person is dispensed 10 items per year = 2.2B invoice_lines per year. This would be 55,000 lines per facility per year
    = 220 lines per facility per day.
    Let’s say they do that mainly in 8 hours = 28 lines per hour. At 5 min sync, ~ 2 records per sync.
    At 5 min sync, 40,000/5/60 = 133 new connections a second, each pushing 2 records. So integrating 270 records per second average- say peaks because some sites busier, and not even through the day of 500 records per second ingestion, while also handling lots of queries for outgoing records at the same time.

V7 Sync

V7 sync system is composed of the following components and mechanisms that drive synchronisation of records (database entity) between sites

• the changelog: A way to record that a record has changed and needs to be routed. It is similar to a feed.
• the changelog filter: A routing system that determines where a record needs to be sent,
• the sync buffer: Buffering that ensures transactional integrity,
• Endpoints that allow communication between sites, the central api and sync record
• A process that facilitates this communication, synchroniser.
• Translate and integration

The Changelog

V7 implements a changelog table for both the central and the remote sites. A changelog record has:

1. A link to the record it’s referencing, record_id and table_name field

2. A sequential marker for tracking position in the changelog, a cursor field, auto increment serial

3. A row_action, of two types UPSERT and DELETE

4. A way to mark the origin of the record, the source_site_id (including central data: a change from OG where central data isn’t marked with the origin)

5. Helper fields used by changelog filter that help with query speed during routing, store_id and transfer_store_id, patient_id (reasons explained further down)

Changelog Logic

Every time a record is mutated (insert, update or delete), in our application logic we append a changelog with either

• A reference to the record id and the table (for inserts and updates)
• For deletes, a reference to the record id and the table, and the value “delete” in the row_action field.

Notes on backend handling:

• The code has distinct layers, and database mutation must only happen in a repository layer, Database operations are partitioned by record type, so this is a convenient place for changelog inserts. (The alternative of using triggers was rejected)

• record_id, table_name and row_action, are easily deduced in the context of mutation.

• source_site_id is either provided by the context calling the operation or set to current site id:

• When receiving a record via sync: the origin site id (the site that is sending the record) will be passed on to the changelog insert

• When records are being mutated by the site itself (graphql mutation, side effect, etc..), current site id is queried from key_value_store (cached)

• store_id, transfer_store_id and patient_id are set based on record sync type, these fields are not always available on the record itself and will need to be queried based on related record when they are updated during normal operations. However when records are integrated via sync, these fields will be populated from sync_buffer which in turn is populated from sync_record, and sync_record will have these fields populated from changelog (full circle)

(TODO: here a link to all sync types and explanation about how/when they sync, what actual tables relate to what type should really be in code).

store_id justification for inclusion

• During initialisation, we need to know which remote records belong to which site, this is done by looking up store_id that are active on remote site for changelog records that are of type ‘remote’

transfer_store_id justification for inclusion

• Records need to be forwarded (routed) from site to site when a shipment or requisitions are sent between stores active on different sites.

patient_id justification for inclusion

• Patient related records are routed to all sites where active store has visibility of the patient

Considerations

Instead of de-normalising transfer_store_id and store_id, grab them from related records. We want to reduce performance hit during inserts to changelog, and thus accessing those fields straight from the records would be much faster. We have to make sure those fields are on invoice_line etc.. are not indexed, this can be done with naming convention or a test (i.e. a postgres test that examines postgres schema after database migrations and checks for indexes on those fields).

We also discussed just using name_id as we did previously for transfer_store_id and patient_id. The former is harder to reason about and slower to query and potentially slower to insert into changelog because:

• Partial indexes (that are based on nullability) are more performant on insertion for optional fields when there are more null values, when this field is shared for transfers and patient related records there would be less null values.
• When querying changelog we would need to join name_store_join and store to name_id field, with transfer_store_id we don’t need to join store field at all and can join directly name_store_join to patient_id field

Other uses of changelog

Changelog is also used by processors, either for core logic like creating transfers, or for plugins which have a generic processor interface. Integrations will also make use of the changelog.

No database mutations allowed outside of the application logic

Manual database manipulations will bypass application logic and mess with changelog, they should not be allowed and the risk needs to be communicated to support

TODO: Outline strategy for data fixes, if they need to happen, with equivalant effect to footrunner code and manual database fixes that were done in OG.

Performance considerations

We want to reduce the effect of changelog insert on operations that require it, changelog will be the biggest table in the database and adding to a huge index will become an issue. This will be a problem for a central site, vs remote site (validate this), since the central site will use postgres, we can use partitioning to deal with this.

We ran an insert-rate benchmark on Postgres to confirm this and to test partitioning as a fix. Summary:

• With only the primary key, inserts stay flat at ~80K rows/s across 100M rows.
• With the v7 secondary indexes (source_site_id, store_id, transfer_store_id, patient_id), insert rate collapses more than 10× before reaching 100M rows, because each insert writes to random spots in indexes that no longer fit in cache.
• Range-partitioning by cursor (5M rows per partition) keeps each partition's indexes small enough to stay in cache. Insert rate oscillates in a sawtooth but does not trend downward — performance at 100M rows matches performance at 10M rows.
• Partitions will be maintained by a scheduled task in the Rust server (the same task that runs changelog deduplication), not by pg_partman.

Full test method, results on two servers, and proposed partitioning/migration strategy: changelog bench summary.

We will also have duplicates in the changelog, we are deliberately not dealing with those when changelogs are being inserted, to keep the operation lean. These will need to be removed on a schedule

Conflict considerations

When changelogs are inserted inside a large transaction (e.g. sync integration) while another consumer (a different sync, or a processor) is reading the changelog, a cursor-based reader must not skip entries that are still inside an uncommitted transaction. Under PostgreSQL's default Read Committed isolation the changelog.cursor sequence is allocated outside the transaction but the row only becomes visible on commit, so a reader can see a non-contiguous set of cursors (e.g. [1, 3] while 2 is still in flight) and advance past the gap — permanently missing cursor 2 once it commits. SQLite is unaffected (Serializable isolation, no concurrent writers).

The implemented solution is a Rust-side in-flight cursor tracker rather than a table lock. A manager-owned map records, per connection per transaction, a lower bound on the cursors that transaction will produce (registered on its first changelog insert, removed on the outermost commit/rollback). Readers clamp query() and max_cursor() to min(in-flight bounds) - 1, so they only ever read cursors guaranteed to be gap-free. Unlike the earlier table-lock approach, writers are never blocked by readers; a reader near an unrelated long-running transaction simply returns fewer rows and catches up on its next cycle.

The full investigation — the five approaches explored (table lock, lower lock levels, Postgres-internals gap detection, the Rust tracker, cursor rollback) and why the tracker was chosen — is in the changelog locking investigation.

What this solution might not be suitable for is sync cursors, as it may result in sending records that are already sent. E.g. sync is running from 900, it’s progressing to 1000. Concurrently an invoice is being updated in a transaction occupying 940-950. If we set the sync cursor back to 940, we’d potentially be resyncing 950-1000.

Alternative discussed (Claude: record locking) that needs feasibility determined:

Check for the lowest changelog.cursor in uncommitted transactions. Don’t let readers of changelog exceed this cursor.

• Note claude example code not validated, it seems to rely on querying changelog table still, attempting to find uncommitted records
• We could manage this in rust land - a shared variable where changelog writers update it if their earliest cursor is lower than the current value. Possibly need to be a vec of all the earliest cursors currently held in transactions
• Writers should clear their earliest cursor from the shared variable after committing or rolling back their transaction. Would have to have guarantee that they clear it if say the process terminates prematurely.

Implementation details

Draft: Here we talk about the abstractions for V7 and implementation details

changelog filter

There are two sync directions, from remote site to central server (push) and from central server to remote site (pull), in both cases we need to determine how many total records need to be sent (for progress indication) and what records to sync in the current batch. Database level filter on changelog is used for this.

The routing filter, cursor windowing, de-duplication and the full generated SQL of the pull query are documented on a dedicated page: Changelog filter, windowing & de-duplication. The sections below summarise the conceptual structure.

Cursor

Cursors are used by the site that requests records to keep track of which records have already been synced, these persist in a database and will increment after each batch has been synced. This is a common condition for the changelog filter. Two primary cursors are stored on a remote site,

• SyncPushCursor - a cursor for the remote site’s own changelog - which records have been pushed.
• SyncPullCursor - a cursor for the central server’s changelog

source_site_id

To avoid circular sync we use source_site_id

• When pushing records to the central server, only send records that have source_site_id equal to this_site_id. Only records that had the latest edit on this site will have source_site_id set to this_site_id.
• When pulling records from the central server, it will filter remote records to only send those where the source_site_id equals to the id of the site doing the pulling.
  • This is true for all cases apart from initialisation, where this filter is ignored. When records are integrated during a sync operation, source_site_id will always be set to the site from which records have arrived.

Sync Type Specific Filters

Additional filters are separated by an OR condition and are based on a sync type of the records, they are structured as:

• Matching any table_names for the given sync type
• AND matching site specific criteria for the sync type

Sync rules per sync style

The push and pull behaviour for each sync style is summarised below. The push side is the same for every style that pushes at all: the site sends records where source_site_id equals its own site id. This is set by application logic at the point of mutation and is not trusted from the client. The pull side varies by style and is the OR of the per-style conditions in the Example Pull query.

Tables that have more than one sync style (for example Invoice, which is Remote + Transfer + Patient; Name, which is Central + Patient) are pulled if any of the applicable conditions match. This is the OR in the Example Pull query.

For the authoritative per-table classification — which sync style(s) each table has, what changelog metadata each populates, the exact outgoing filters, and the translation directions per transport — see the Sync styles, changelog generation & outgoing filters reference. It is generated from the code and is the source of truth for the table-by-table detail this section summarises.

Limits and Order

Sync requests are batched, and the limit is set based on sync request batch_size. We need to always order by cursor in ascending order

Example Pull

When pulling records from central, requesting site will provide SyncPullCursor and is_initialising in the request and authentication data which will be resolved to site_id, by central server, a query will be built resembling this structure.

WHERE (is_initialising OR source_site_id != site_id) AND (cursor > SyncPullCursor) AND (

(table_name in {list of central data table names})

OR

(table_name in {list of remote data table names} AND store_id in {list of stores for site_id})

OR

(table_name in {list of transfer data table names} AND transfer_store_id in {list of stores for site_id})

OR

(table_name in {list of patient data table names} AND patient_id is visible in {list of stores for site_id})

)

Example Push

When pushing records to central, the remote site will know its own site_id, and current SyncPushCursor. The query will be simple, as source_site_id will only be set if record was legitimately edited by this site.

WHERE (source_site_id == site_id) AND (cursor < SyncPushCursor)

Sanity check and record validation

We want to deal with these use cases when considering validation and sanity check

1. A bug in our code where sync record are sent to the wrong site
2. A bad actor that has acquired sync site credentials

note: at the time of this writing, we are not intending to support bespoke sync clients, thus a mechanism for making sure data is consistent with our business rules is not planned

For 1, there is a basic sanity check to examine sync_buffer record and make sure it can be integrated based on its sync style, store_id, transfer_store_id and patient_id. The rules mirror the pull side: a record is accepted on the receiving site if at least one of its sync styles' checks passes, the same OR semantics as the pull filter.

A record can have more than one sync style (e.g. Invoice is Remote + Transfer + Patient). A row is accepted if at least one of its styles' checks passes, mirroring the OR semantics of the pull filter.

When a sanity check rejects a record, the rejection reason is written to the sync buffer row's integration_error, the row is marked integrated so it is not retried, and sync continues to the next record. A partial inconsistency is preferred over total sync failure (see Consideration errors).

For 2, it would be a hard and would impact performance to

• To check all incoming records for consistency
• To differentiate between a valid data state and something malicious (for example price updates)
• To stop sensitive data being accessed, pulled.

Therefore an approach to reduce a chance of impersonation of remote site is taken, using a token based authentication, after initial authentication.

• Initial login is done via a separate endpoint using configured site credentials
• During initial login, a token is allocated and shared with sync client
• Further communication is done using the token
• Site credentials cannot be used to authenticate if token is already allocated
• Token can be cleared by admin user, if site needs to be re-initialised
• Hardware id check remains as is, to prevent accidental sync of dev clones of sync client

de-duplication

Changelog records are generally append only to minimise overhead to updating records in general, thus there will be multiple changelogs for the same record.

Previous reasons for de-duplication:

1. To avoid syncing or processing the record multiple times by only considering the latest action for any particular record.
2. De-duplication can also help reduce conditions when processing changelog, for example when a record is deleted it would have at least two entries in changelog, upsert followed by delete. If changelog is not deduped then when processing it, we would need to deal with a condition that a record may not exist at all, even though it has upsert operation.

Changelog table will grow over time to be very big as it has a row for every record in every other table in the database. As it's the core mechanism for selecting what records to pull from COMS we need to minimise overhead of querying the changelog to maintain high performance of COMS. Although various optimisations can be done to indexing and queries, the best solution of the problem is sometimes the absence of the problem. We shouldn't try to de-duplicate on the go, only do it as a scheduled task, allow for duplicates and deal with those in application logic. This would mean:

1. If records that are reference by changelog are not found in database, skip them rather then surfacing errors
2. We will not have reliable 'count' of changelogs based on a particular filter, thus the progress indications will need to be changed from 'counts' to percentages of current cursor progress to max cursor
3. When a batch of particular size is being prepared (say for sync), and there we have a scenario from 1, then application logic will re-query changelog until batch is full
4. Together with sync_buffer being append only, after ROMS pulling from COMS we may have multiple operations in sync_buffer for the same record, in order to deal with this, we would de-duplicate in application logic (see the sync_buffer investigation for the append-only design and de-duplication rationale)

Filter overrides

It is possible for a requesting site to provide its own filter, which will be added on top of the existing central server filter. This will be essential, in the absence of a sync queue, when needing to re-sync particular store data (when it’s added to a site) or re-syncing patient data (after central lookup). For example a new cursor is created and set at 0, a filter is added on top of current filters that replaces ‘list of stores for site_id’ with ‘store_id equal new store’ and both of those are sent in the request to central.

See re-syncing data for more detail

Implementation details

The filter is a serialisable AST (ChangelogCondition::Inner) built per request from the parameters in the Pull call. It serialises to JSON for transport in the optional filter field, and on the central server it deserialises and compiles to a boxed Diesel WHERE expression applied to a four-join changelog query.

We left-join store three times so the filter can reach site_id via any of the three routing paths in a single query — directly via store_id, via transfer_store_id (aliased as transfer_stores), and via name_store_join then store (aliased as patient_stores):

flowchart LR
    cl[changelog]
    s[store]
    ts["transfer_stores<br/>(alias of store)"]
    nsj[name_store_join]
    ps["patient_stores<br/>(alias of store)"]
    cl -- "store_id = store.id" --> s
    cl -- "transfer_store_id = id" --> ts
    cl -- "patient_id = name_id" --> nsj
    nsj -- "store_id = id" --> ps

All edges are LEFT JOINs, so changelog rows with none of those keys set (e.g. central data) still appear. Filter fields map onto the diagram as follows:

Temporary cursors for store and patient re-syncs (see Store re-syncs) reuse the same machinery — a new filter is built with the relevant field constrained to the specific store or patient, serialised, and sent in the filter parameter alongside a temporary cursor starting at 0.

For the macro that generates the filter AST and the recipe for declaring the joined Source type, see Conditional Filters.

Sync_buffer and atomic batch sync

One of the promises of the synchronization system is the completeness of sync operations. This includes:

• Transactional integrity is maintained
  • Stock lines updates and all corresponding invoice+lines are kept in valid state, so ledger checks are reliable
  • Transfers can rely on all necessary records being integrated before generating transfer records.. Without this lines might be missing in the corresponding transfer, or in a stale state
• All foreign constraints are met when records is being integrated

Due to the nature of changelog and limits on the batch size of requests due to bandwidth constraints in our domain of operation, there are many use cases where the above can break if records are integrated as soon as they are received.

We have a solution and it’s called atomic batch sync, and it goes like this:

• Records are synced in batches
• They are saved in sync buffer
• When there are no more records to receive or send, pending sync buffer records are integrated in a transaction

Above, together with a promise that database operations and their side effects always happen in transaction, should satisfy the stated promise.

Shape of the Sync_Buffer

sync_buffer is essentially a copy of sync_record, it contains all of the information required to save the record in a database, it’s basically a changelog, including the data of the records itself in a json form. For v7, we alter the existing sync_buffer table to be compatible with both v7 and older versions of sync

• table_name and record_id identify a record, it’s unique in sync_buffer by record_id
• row_action determines if it’s UPSERT or DELETE for v7. MERGE variant is maintained for sync v5 and v6
• source_site_id records the origin of the record
• transfer_store_id, store_id, patient_id to populate changelog record
• data is used to store a json version of the record
• received_datetime, integration_datetime and error fields keep track of integration status

Append-only, cursor as primary key

sync_buffer is append-only. Every sync record received is inserted as a new row; record_id is not unique. The primary key is cursor (BIGINT auto-increment). The only in-place mutations after insert are setting integrated_datetime and error.

This was validated by benchmark — upserts by record_id under partitioning required a CTE delete-then-insert to keep uniqueness across partitions, which costs ~20–30% on writes. Append-only avoids that entirely, and as a bonus preserves full sync history for free (see Historic records).

Queries that need "latest version of record X" use a DISTINCT ON (record_id) ... ORDER BY cursor DESC pattern. The hot integration path iterates by cursor ascending and naturally sees the latest version last.

Logic of Sync_buffer

sync_record can be deserialised directly into sync_buffer, and sync_buffer is saved in the database upon receival as a new row (append-only — no conflict handling on record_id).

When records are integrated they are integrated based on source_site_id. The central server may be in the process of receiving data from multiple sites, and should only integrate data for a site that’s finished its push entirely.

integration_datetime allows for records to be marked as integrated and filtered to be excluded in future integrations.

error is populated if there is error in integration.

Performance and partitioning

We ran a scaling benchmark on the sync_buffer design (single table + partial index vs several LIST-partitioned variants) up to ~75M rows. Summary:

• Indexes for the queries we care about don't grow unbounded — they scale with the number of pending rows, which is stable in steady state, not with total table size.
• A partial index on pending rows looks equivalent to partitioning but query latency still creeps up as the table grows, because pending-row heap fetches get scattered across a bigger and bigger table. At ~60M rows, basic filtered queries crept to ~76–96 ms.
• LIST-partitioning on (is_integrated) keeps pending rows co-located in one small partition and holds query latency flat (~40–50 ms at 75M rows). Bulk mark-integrated is also ~2–3× faster.
• Further sub-partitioning the done side by cursor range wasn't measurably better for the workloads tested — revisit only if we add operations that scan the done partition.
• Ordering queries by cursor (monotonic PK) rather than received_datetime is preferred — it uses the PK directly and is not sensitive to clock movement.

Recommended strategy: append-only (above), with LIST partitioning by is_integrated BOOLEAN on the central server. Remote sites can run unpartitioned (pending set is small).

Full test method, results, and schema proposal: sync_buffer investigation.

Historic records

sync_buffer gives us a diagnostic tool to diagnose synchronization errors, as well as keeping historic state of synced records; the latter can be used to re-integrate a record or its fields when upgrading remote sites.

For example, a new field was added to the records, the central server was upgraded but the remote site was not. New record was synced with the new field in data, it’s stored in sync_buffer, remote site was upgraded, and can now, in migration, either mark sync_buffer record as not been integrated (it will be re-integrated on next sync, good for central data updates), or manually re-integrate the new field from the sync_buffer (for remote data, to not overwrite current state of the record).

Consideration to keep all states of sync_buffer

Draft: To allow for historic diagnosis of synchronization, we can archive previous versions of sync_buffer row during upsert operations, rather than overwriting, this would give more tools for diagnosis and data restoration.

Consideration errors

Even though the promise of transaction integrity is built into the sync_buffer system, there will potentially be cases where a record involved in a transaction will not integrated due to an error, we allow this to happen, since the effect of full breakage of sync is deemed less desirable then a partial inconsistency in data (especially during deployment, under time and internet availability constraints).

Draft: There needs to be a mechanism to notify the support and dev team of any errors like this.

Implementation details

Draft: About translations from sync_buffer to sync_record to changelog. About archiving of sync_buffer and using common table_name and row_action.

Central api and sync_record

The central server exposes two endpoints that allow the remote site to initiate sync action.

• push is when a remote site in sending records to central server
• pull is when remote site is requesting records from central server

And additional endpoint to query for status of sync site, site_status.

All three are HTTP POST requests. Error handling is done through the response body; every response returns HTTP 200 with the result encoded in the body, with one exception: 401 is reserved as a signal a proxy can use for rate limiting. This keeps error handling and general implementation simple.

Authentication is sent via HTTP headers; endpoint-specific parameters are sent in the JSON body.

Common headers

These headers are sent on every authenticated request (push, pull, site_status):

• Authorization: Bearer <token> — token allocated during initial login (see Sanity check and record validation)
• hardware-id — unique hardware identifier
• app-version — site sync version, used to validate compatibility

For all authenticated endpoints, the first step is to look up the site by token, then check that the supplied HardwareId matches the one bound to the site at login. Followed by a version check and a site status check.

Authentication is performed at the application level inside each handler, not as middleware; this lets the handler return the resolved site_id and version to downstream sync_buffer / changelog logic.

Successful authentication results in site_id being passed to the endpoint logic.

Token format is a UUID for the V7 MVP. JWT may be adopted later.

Initialisation (get_site_info)

Initialisation is a separate endpoint that allocates the token. It does not use the headers above; instead it takes site name + hashed password (Basic-auth-style credentials) plus the hardwareId and version in the JSON body. On success the response contains the allocated token, siteId, and centralSiteId. See Sanity check and record validation for the full token lifecycle (allocation, single-use, admin clear).

Site Status

This endpoint can be used to query for a number of records to be pulled, but mainly used to see if the site is ready for the next operation. Since there is already a check for site status in the common functionality, which will result in an early return. Successful response would have siteId and centralSiteId

Pull

Additional parameters

• batchSize how many records should there be in response
• cursor changelog cursor from which to start looking at changelog
• isInitialised is the site initialising
• filter optional changelog filter, to apply on top of base changelog filter
• previousTotal optional previous total, to determine when to re-query for total in changelog

Central server will create a changelog filter based on passed parameters, then for every changelog will query for related records, serialise it into sync_record and return a batch result in the same shape as the request in push, see below.

Querying for a total count from a large changelog on each request is not necessary and would be quite demanding, so that is why previousTotal is used and the resulting total is either deduced by looking at previousTotal and current number of pull records, or queried from the same changelog filter if current number of pull records is less then batchSize.

Once the number of records to pull is 0, the remote site will begin integration of the sync buffer.

Response: SyncBatch

Consideration

Draft: During initialization we may want to sync ‘everything’ apart from changelogs, and sync changelogs after. This is possible with the same mechanism of temporary cursor + special filter (I think needs a separate section just about how that works)

Push

Additional parameters: SyncBatch

Central server will deserialize SyncBatch in the response and save it in sync_buffer with source_site_id as site_id.

When there are no more records remaining an integration process will be spawned, which will lock the sync API for the site until integration is finished.

SyncBatch

A common shape in the request of the push and response of the pull

• remaining how many records remaining to be sent/received
• records an array of sync_records

Sync_record

A cursor of the current record, coming from the change log, and a serialized version of sync_buffer row (can be deserialized straight into sync_buffer). This sync record can be produced by a common function, built from a changelog and a query for the related record

Implementation details

Draft: Site status etc.. sharing types etc.

Store Merge

Draft: Would it be similar to the time we've added 'link' tables ? I am wondering if it's coupled to v7 work or it's something separate (not sure about all of the considerations). Adding as placeholder to look at use cases

The Synchroniser

Sync uses GraphQL (Graphql takes a bit of work to build and parse the query, also if error handling is built into the shape of graphql itself there is a bit of work constructing it. Since rust endpoint already has rust types, and it’s easy to use them in actix_web, we can be type safe out of the box as long as we use them in the right endpoint.)

. The Remote site is responsible for driving its own synchronization, to both send and receive records, to and from the central server. Synchroniser is composed of two parts.

• Sync driver, which schedules sync and calls manual sync if requested
• Synchroniser which separates sync into separate steps, recording each step in the sync_log

These steps are

• push records to central server.
• wait_for_integration of pushed records on central server
• pull records from central server
• integrate records pulled from central server

It’s important to note that push needs to be done first, to avoid override of shared sync type data

Sync Driver

A process that waits for sync interval or can be interrupted by a manual sync request, either will result in Synchroniser to be called, this process is initiated at the start, with a trigger to start on startup if settings are provided on startup.

Push synchronisation

Check changelog if there are any records to be pushed to central server, since the last push, using SyncPushCursor, if so call push endpoint with those records serialized into sync_batch and on successful response increment SyncPushCursor, repeat until there is no more records to be pushed, and remaining 0 is sent to the central server.

Consideration of push before pull

Draft: Is that still true, we will have protection against integration of records that are owned on this site? I think it is, because there could be an old record for a patient waiting to be synced way back ?

wait_for_integration

This operation assumes that push happened, and will use sync_status endpoint to poll central server until site is unlocked

Pull synchronisation

Will call pull endpoint on central server, saving result of each response in sync_buffer, until there is no more records returned by central server, using SyncPullCursor to increment cursor on for each sync_record saved.

Integrate

Described in the next section.

sync_log

The result of each sync iteration is stored in sync_log. It can be used to:

• Determine if initialisation has happened, is there at least one fully completed sync_log
• Expose progress and error via api
• Historic analysis of sync operations

For each step it has the following fields (prefixed by the step name)

• started_datetime
• finished_datetime
• progress_total optional, wait_for_integration does not have this
• progress_done optional, wait_for_integration does not have this

As well as overall started_datetime and finished_datetime along with an error field to record any error in any of the steps (unless deliberately skipped, like in the wait_for_integration step).

This is implemented as the sync_request mechanism: an auxiliary sync run can override the push filter, pull filter and cursor, and stamps the records it buffers with its own reference_id so they integrate separately from the main sync. It is used for store transfers and post-migration table re-syncs. See Store re-syncs for the full mechanism.

Implementation details

Draft: Sync api abstraction, for calling and translating API result to actually doing the step logic. Sync log abstraction, for progress tracking, starting and ending steps

Translation and Integration of sync records

The last step that completes synchronization is integration:

• Query for sync buffer rows in foreign key constraint order
• Do sanity check on the record, making sure it can be integrated on current site
• Translate from sync_buffer to actual record
• Save the record

If there is any error in any of the steps, it will be recorded on sync_buffer, and in both success or error integration_datetime is updated.

Foreign key constraint order

Within a definition of each record, there is a dependency array, it is used to create a dependency graph from which we determine which table_name needs to be integrated. This order is used in integration

Sanity Check

Before a record is translated and integrated, the sanity check confirms it is consistent with its sync style on this site. The full per-style rules are described in Sanity check and record validation. On rejection, the reason is written to integration_error, the sync buffer row is marked integrated, and processing continues with the next record.

OG sync v5 has rules to prevent changes of record ownership or modification of unowned records during sync buffer integration; the v7 sanity check above plays the same role.

Translation

This should always be one to one mapping between json data and the record. There is an abstraction that helps with this via serde and trait definitions (implementation details)

Transaction

Integration should ensure that transactional integrity is maintained, for this it itself happens within a transaction, there are some caveats:

• For postgres, the whole transaction is discarded if there is an error, therefore an inner transaction should be created, however this becomes very very slow if there are lots of errors, but we don’t envision this.
• For sqlite, we don’t need an inner transaction, but a transaction is needed because it’s much faster than starting one on every record upsert. In fact inner transactions are very slow.

During initialisation, only initialisation graphql endpoints are available, so it’s safe to reduce transactions (no transactions at all for postgres).

Transaction performance

Draft: There was an example that I should look up, about transactions and performance, remember doing a test like this when we were discussing this with Clemens.

Also do we agree that we don’t envision tons of errors (in v5 this is definitely a problem, i was trying to sync CIV, and it’s a drag, lots of payment invoice cause errors/slow down)

Central Site push Integration

When we push record to central server, integration is spawned, this is using exactly the same mechanism and the same implementation, only difference is the filtering by source_site_id, for the site that has pushed the record.

Use case of the same record pushed by multiple sites

Draft: Investigate what would happen if more than one site is pushing the same record (asset or patient), sync_buffer would be updated to be for another site, so when integration starts this record won’t be integrated, but what if other records rely on it ?

Progress tracking

Similar to sync steps in Synchroniser, sync_log is used to keep track of progress of integration, however since integration happens in a transaction, these updates are not visible until the transaction is finalised. Therefore we use some memory caching on the last sync_log in the repository layer (saving and querying for last record in memory as well as database)

Implementation details

Draft: Talk about traits and abstraction we use for integration and translation, including visitor pattern

Initialisation of new Sites

Before a remote site can be used it needs to be populated with base data, this is called initialization. Also if for some reason data for the site was lost, it can be re-initialised. Essentially initialisation is the process of synchronizing all of the site's data from the central server to the remote site.

App initial state

When the application first starts up, there is a check to see if there is at least one successful sync_log, if there is then data has been initialised and we are in operational mode, otherwise the app will be in initialisation mode.

In operational mode full graphql schema is mounted, and most of the endpoints are protected by authentication and authorisation.

In initialisation mode only initialisation specific schema is mounted, with no user authentication required.

Initialisation mode

In this state, only synchronization specific functionality is exposed via API, which allows users to enter url, site name and password. At which point the site_status endpoint is called to validate credentials and if this is successful, these settings are stored in the database and Synchroniser is manually triggered. Upon successful Synchronisation, successful sync_status will exist causing graphql schema to change to operational mode.

Synchroniser

The same synchroniser as operation mode will be used, the only difference is that is_initialising flag is passed on in pull which in turn is sent to the central server.

Synchroniser: Implementation details

Draft: We’ve previously had a complex system, with messaging and channels to switch schema, this avoided needing a constant requery for successful sync_status in the database. I suggest we use a memory cache on the repository for this, which would be just as quick as global mutex, and we don’t need to explicitly set it, when sync_status is upserted it can be updated, if not already set.

Draft: Front end will know the status, if initialised or not, and can change sync steps to reflect that we are doing pull only.

Store and patient re-sync

Store re-syncs (and auxiliary sync via sync_request)

When a store is moved to a remote site, we need to re-sync all of its data. This poses a challenge since we don’t want to re-sync data for other stores we have active on the site, so full re-initialisation is out of the question. Instead we ask the central server to filter by this store’s id (rather than all stores active on the site), pull those records, and integrate them — using a temporary cursor that starts at 0 so we get the store’s full history, not just changes since the main cursor.

This is generalised into the sync_request mechanism: an auxiliary (a.k.a. special) sync run, parameterised by its own pull/push filter and cursor, that runs after the normal main sync each tick. Anything that needs to backfill a subset of records — a transferred store, a re-synced patient, or a newly-added table after a v7 migration (see Re-sync after a v7 migration) — is expressed as a sync_request row.

Mechanism

• A row is inserted into the sync_request table (see Tables) with a pull_filter (a serialised changelog filter, e.g. all data for store X), an optional push_filter, and a human-readable description.
• After the main sync completes on each tick, the sync_request_runner forms one group of pending (unfinished) requests and runs it as a single auxiliary SyncRequest:
  • If any active rows already carry a reference_id, the rows sharing the first such reference_id form the group — this is an in-flight retry resuming.
  • Otherwise all active rows are grouped together and assigned a new uuid reference_id, which is persisted on the rows before the run so a crash mid-run is recoverable.
  • Per-direction filters across the group are OR’d together.
• The group runs with dynamic temporary cursors derived from the group reference_id: pull_<reference_id> and push_<reference_id>. These live in key_value_store, start at 0, advance as batches are pulled, and are deleted when the group finishes successfully so the KV blob doesn’t grow unbounded.
• Auxiliary requests always run with is_initialising = true (the central pull filter and the integration transaction wrapping behave like initialisation, since we’re backfilling full history for the filtered subset).
• On success every member row is stamped with finished_datetime and drops out of the active set. On failure the rows keep their reference_id, so the next tick re-picks them as the in-flight group and retries from where the cursor left off.

Keeping auxiliary records isolated from the main sync — important

The critical correctness property is that an auxiliary run’s buffered records must never be integrated by the main sync (and vice-versa). This is enforced through reference_id:

• Every sync_buffer row carries a nullable reference_id (a logical FK to sync_request.reference_id). The main sync inserts rows with reference_id = NULL; an auxiliary run stamps every row it inserts with its group reference_id.
• The integrate step filters pending sync_buffer rows by the run’s reference_id: the main sync only integrates NULL-reference rows, and an auxiliary run only integrates rows matching its own reference_id. They are integrated as separate, independent batches.
• This isolation is what makes partial-failure recovery safe. Without it, an auxiliary (special) sync that pulled records into the buffer but failed before finishing integration could be partially integrated by the next normal sync run — integrating an incomplete, inconsistent subset of the store’s data. Scoping by reference_id guarantees each run only ever sees and integrates its own rows, and a failed run resumes its own leftovers on the next tick.

Orphan note: removing a reference_id (e.g. a request abandoned) without finishing its integrate leaves its rows in sync_buffer. Sweeping such orphans is currently out of scope.

Store-move trigger (translate_store)

The store-move case is detected during the v7 store translation, not via a separate message. When a store row arrives on a remote and (a) the site already has that store locally, (b) with a different site_id, and (c) the new site_id is this site’s id — that’s a transfer (not the store’s first arrival). The store translation emits a sync_request row alongside the store upsert, with pull_filter = ChangelogFilter::data_for_store(store_id). The sync_request_runner consumes it on the next tick.

Sanity-check counterpart: central and remote both refuse to integrate records for a store that is no longer active for the pushing site (i.e. the store was moved away). This protects against two sites having the same store active and the resulting ledger issues.

Re-sync after a v7 migration

sync_request is also how we backfill records that a site would otherwise miss when it migrates to v7.

When a remote site transitions from v5/v6 to v7, it does one last v5/v6 sync and then switches transport. Its v7 pull cursor is seeded from the v6 cursor position — i.e. it is already “up to date” with central as of the moment v7 started. The consequence:

• Any records that became v7-eligible after v7 started on central but before this site migrated sit behind the seeded cursor. A normal v7 pull starts from that cursor and will never re-pull them — they’re silently skipped.
• This bites hardest for tables that are new in v7 (had no v5/v6 translator, so were never pulled on the old transport) and for tables whose v7 coverage was switched on after central’s v7 cutover.

The fix is a migration that seeds a sync_request per affected table, so the auxiliary runner re-pulls those records (from cursor 0, filtered to just that table) on the next tick — without disturbing the main cursor or re-initialising the whole site.

Two shared helpers in repository/src/migrations/helpers.rs make such a migration a few lines:

• seed_sync_request_for_table(connection, table_name) — inserts one sync_request with pull_filter = ChangelogCondition::table_name::equal(table_name) and reference_id = NULL (so the runner groups it fresh and assigns a reference on first run).
• pull_has_started(connection) — the fresh-install guard. Returns true only if a pull has run before (checks sync_log_v7.pull_started_datetime or sync_log.pull_central_started_datetime). On a brand-new site the upcoming initial sync already covers everything, so the seed is skipped and a queued request would just sit unrun.

The precedent is repository/src/migrations/v3_00_00/seed_sync_request_user_tables.rs, which guards on pull_has_started() then loops seed_sync_request_for_table over user_account, user_permission and user_store_join.

Implication for adding a new table: if you add a table that is new in v7 (or you turn on v7 coverage for an existing table) and sites are already running v7, you must add a migration that seeds a sync_request for it — otherwise already-migrated sites never pull the historic records, only ones changed after the migration. This is called out in the add-a-table reference docs and skill.

Patient re-syncs

Unlike store re-sync (which goes via the auxiliary sync_request runner), bringing a patient's data onto a site is done synchronously and in memory via the patient_data_for_site API call, so the front end can be told immediately that the data is available. See Patient data sync for the full flow; the rest of Patient Lookup covers the search step.

Patient data visibility

Draft: There is a product question, about what data can be synced, I think there needs to be a recorded acknowledgement by a user that they request this patient and their data to be visible on this site, and perhaps some throttling for user (to not be able to do this too often), something similar here, make sure someone can’t use our sync API to query and get all the patients and their data.

Transfers

We maintain the promise that the site that owns the record should be the only site that is editing this record, therefore transfer generation logic will always happen on the site that should own the transfer record. This means that any related records that are needed for transfer generation will be routed to the corresponding site.

The processors are responsible for actually generating and linking transfers

Example Transfer

Store A is active on site A, and store A linked to name A

Store B is active on site B, and store B links to name B

When store A creates a shipment destined for name B, it’s sent to the central server, like any other remote record. When site B syncs, it will receive a copy of this shipment because its destination is name B, which is linked to store B, an active store on site B. After synchronisation, processors will generate a transfer record, with name A as the origin, and will perform the required linking, which will in turn sync to the central server. When site A syncs it will also get the newly generated transfer from site B, because once again the name specified on the shipment will be linked to a store that is active on that site, name A. At this point another processor will link original shipment to the transfer shipment, only editing the record that site A owns.

Transfer Processors

Processors examine changelog and process records sequentially, performing various actions when certain conditions are met.

There are lots of actions included in processor for transfers, these include but are not limited to

• creating transfer
• linking original record to the transfer record
• updating status on the original record based on the transfer
• updating status on the transfer based on the original record

There is a full description of processors and their action here:

• Processors in general
• Transfers in general
• Requisition transfers
• Invoice transfers

Draft

Documentation diagrams are probably out of date, I think the supplier/customer return are not listed in the diagrams. Diagrams should really be in vs code

Merging

We have an implementation of linking tables (Draft link to name_link and item_link) that helps reduce the surface area of merge operations. When merge is performed, a new id is set on the related link table, overwriting the previous id that is being merged.

Link tables are not part of a sync system and are maintained by every site. Merge operation can only be performed on the central server, when this happens a sync_message is sent to all sites, requesting a merge, this sync_message will be processed on a remote site, updating relevant link records.

It's important that this mechanism works for multiple sequential merges, and does not rely on sync_message being in a particular order

Other operations for merging like updating of name_store_join can be done on central and synced to remote

Merging Considerations

Draft: It sounds like there is a use case of order we need to consider for sync_messages, also sync_messages create extra moving part to the whole mechanism. An alternative is to allow link tables to be synced out from central site, however the 'id' of the link is the same as name, so would need a way to work around that in sync_buffer and changelog (should work v7 abstractions), this may also mean that links are exposed out side of the repository.

Patient Lookup

Remote patient lookup is two separate operations against the central server, both over the v7 sync API (not proxied GraphQL): first a search to find candidates, then — once the user picks one — a link + data pull that makes the patient and their data immediately available locally. Both run synchronously, outside the normal Synchroniser loop.

Lookup (search)

When a patient is not found locally, the remote calls the central server's v7 sync patient_search endpoint with the search filter. Central runs the search against its full name table and returns matching patient candidates. Because this goes over the v7 sync API it uses sync-site authentication (the remote authenticates as a site), and the central search is not limited to stores active on the requesting site.

Code: patient_search_central_v7 in service/src/programs/patient/search_central.rs → SyncApiV7::patient_search. (On a v6 remote the legacy v4/COGS path is used instead — see What happens in v5?.)

Lookup Considerations

Draft: Logging for every patient request query, and rate limits ?

Patient data sync (link + in-memory integration)

When the user picks a found patient and requests they be made accessible on this site, the remote does a single synchronous API call and integrates the result in memory — there is no temporary cursor, no sync_request, and the records are not persisted to the sync_buffer table. This is link_patient_to_store_v7 → pull_and_integrate_patient_data (service/src/sync_v7/patient_lookup.rs):

1. The remote generates a name_store_join id (uuid) up front and calls the central v7 patient_data_for_site endpoint with { patient_id, store_id, name_store_join_id }.
2. On central (sync_on_central::patient_data_for_site): in one transaction it creates the name_store_join (via create_patient_name_store_join) making the patient visible to that store, then generates a SyncBatchV7 of all the patient's records, filtered by patient_data_for_site(site_id) AND patient_id matching, starting at cursor 0 (full history, not a delta). It returns the batch plus the created name_store_join_id.
3. Back on the remote, the returned records are mapped into in-memory SyncBufferRows (source_site_id = central's site id) and passed to validate_translate_integrate_in_memory with SyncContext::PatientLookup { active_stores }. This runs the normal validate→translate→integrate pipeline in a single transaction, ordered by INTEGRATION_ORDER (upserts forward, deletes in reverse), but reading from the in-memory slice instead of querying sync_buffer.
4. The remote returns the name_store_join_id; the patient and their data are now available locally and the operation is complete — synchronously, so the front end can proceed immediately.

Because integration is in memory and synchronous, it doesn't touch the main pull cursor or interleave with the Synchroniser, so it can't partially-integrate against a normal sync (contrast the reference_id isolation needed for auxiliary sync_request syncs). The patient's ongoing changes thereafter sync normally, because the name_store_join created in step 2 makes the patient visible to the site (see Patient Visibility).

Patient data sync Considerations

Draft: There is a product question about what data can be synced and whether a recorded user acknowledgement / throttling is needed, so the sync API can't be used to harvest all patients and their data (see Patient data visibility).

Prescriber lookup

Draft: not yet implemented. This would happen in the same way as patient lookup (search), except that we don't need to pull/integrate any data from the central server afterwards, just:

* Prescriber is created on remote site with information from the lookup result

* Clinician store join is created

Compatibility

It’s possible for schema to have different shape between central server and remote sites, for example

1. Field name is changed
2. New optional field is added
3. New required field is added
4. Field type is changed
5. New table is added
6. Table name is changed
7. New constraint is added

There are number of ways to deal with this, with their own constraints, in the context we work in:

• We are not able to update remote sites at will, due to low internet and product changes that require extra training
• We are able to update central server to the latest version

To deal with compatibility, we would need to version API and:

1. Not allowing syncing of remote site with higher version then central site
2. As much as possible, keep backwards compatibility when making schema changes
   1. When new field is added keep it optional
   2. When changing type of existing field, also send previous type of the field in sync_record and allow for translating from previous type when remote site sends it to central
   3. When remote site is upgraded do necessary migrations and re-integrate data from sync_buffer

For the above to happen we would need to send the version of the remote site for every sync request and add an extra step before and after translations that allows for manipulation of translated records based on the remote site version. Similar to how database migrations work, we should be able to apply migrations on translated records to bring it to the version that’s compatible with remote site or central site.

Tests

We cannot guarantee that compatibility issues are caught in the implementation and review stage, thus our integration test would need to be quite extensive, and they would need to run automatically in our CI process. They would need to check for different permutations of:

• Different permutations of central and remote site versions
• With at least one insert/update/delete of all of the record types
• Re-initialisation and upgrades after the above

Consideration

At some point we need to weigh in the overhead created by compatibility support and tests vs update over the write, where a remote site is not allowed to sync until it’s received a new patched version (over sync) and is auto upgraded.

Tables

Key tables used in v7 sync

• key_value_store is used to persist sync site credentials, cursors and current site_id
• changelog keeps track of record changes
• sync_buffer is intermediate storage of sync_records
• sync_log a log of sync operations
• site is used to store credentials for sites on central server
• store is used to link store to a site
• name_store_join is used to route patient and patient data to store where patient is visible

Site

Store

Only fields related to sync are listed

key_value_store

Only fields related to sync are listed

Key value store repository is queried a lot throughout sync operations and in our in general, quite often the queries should resolve quickly (like site_id before inserting changelog), this this reason the whole repository is cached and stored in memory as well as persisting in database.

id and value mapping

• SyncPullCursor, SyncPushCursor: value_bigint
• SiteName, SitePasswordHash: value_string
• SiteId: value_bigint
• StoreCursor_{store_id}: value_bigint (for cursor), value_string (for filter)

changelog

sync_buffer

sync_request

Drives auxiliary sync: one row per backfill request (transferred store, re-synced patient, post-migration table re-sync). Persisted so requests survive restarts and partial-failure retries.

sync_log

name_store_join

Only fields related to sync are listed

Specifications for specific tables

Users

Users remain centrally configured, but under V7 their state is synced rather than fetched on demand. user_account (including the password hash), user_store_join and user_permission all have V7 sync styles, so a site keeps a current local copy of every user relevant to it — which is what makes a site immediately usable after initialisation and keeps permissions current without a central round-trip.

Login uses this synced state:

• A V7 remote validates credentials by asking the central server's user-login endpoint (a password check only — it returns no user state). On success it trusts central and reads the local user row; if central is unreachable it falls back to verifying against the locally-synced password hash. Permissions and password changes are not fetched at login — they arrive via regular sync, so a permission change takes effect once sync propagates it (a manual sync can refresh sooner).
• The central server is the source of truth for users: it has no remote login lookup and verifies locally, relying on frequent sync to keep every site's user data current.

This is the V7 behaviour; the legacy per-version login flow and the transition implications are described in the transition doc.

Patients

Draft: explanation of what patient data encompasses. Perhaps it should just be a link to a spreadsheet of tables filtered by a “patient” label.

Patient data encompasses all the records we keep that relate to a particular patient. These include:

• Prescriptions (invoice)
• Prescription lines (invoice_line)
• Vaccinations (vaccination)
• Encounters (encounter)
• Program enrolments (program_enrolment)

Patient Visibility

Systems may have millions of patients - our full Nigeria deployment reference point would be 100s of millions of patients with billions of related prescription records. Obviously your run of the mill Android tablet can’t store terabytes of data, let alone use that much data! So Visibility is used to determine which patients’ records sync to what sites.

mSupply does this with a join table, name_store_join, where the record exists (and a boolean flag) if the patient (name table) is visible to a store. A patient is synced to a site if that patient is visible to any store active on that site.

All of the patients’ data is synced to where it is visible.

Draft:: something to consider is the scalability of this solution? Sync queries on the NSJ table quite a lot to determine visibility, probably one of the largest overheads to OG sync :).

Remote patient lookup

Remote patient look up is used to enable dispensaries to use an active internet connection to make a patient search request to the Central Server which has all patient records. If a match is found, the remote site then can receive the patient’s additional details and dispensing history and immediately make a prescription to the patient.

As patients only sync to a remote site if they are visible to that site, without this feature users would otherwise create many duplicates if they could only search their site’s local database.

In v7 this is implemented over the v7 sync API as two synchronous steps — a patient_search call, then a patient_data_for_site call whose returned batch is integrated in memory on the remote (no sync_buffer persistence, no Synchroniser run). See Patient Lookup for the full flow and code references.

What happens in v5?

On a v6 remote (i.e. not yet on v7), the legacy v4 lookup path is used instead: the remote patient lookup endpoint lives on COGS (patient_search_central_v4 / link_patient_to_store_v6 in service/src/programs/patient/search_central.rs). The v7 path above replaces this with the v7 sync API endpoints on COMS.

Store merge and creating store from names

TODO: Capturing those two use cases as per the header. TODO: The latter case would suggest that changelogs might need to be updated to have transfer_store_id set

V7 Specification

Draft: The COMS site is configured to have all patients and their data visible via COGS (investigate what this looks like on a large data file with lots of patients, when initialising).

As implemented, the remote does not proxy a GraphQL call. It uses the V7 sync API: a patient_search call to find the patient, then a patient_data_for_site call where COMS creates the name_store_join and returns the patient's records as a batch, which the remote integrates in memory (filtered to that patient, outside the normal Synchroniser loop) so the data is available immediately. See Patient Lookup for the full mechanism.

Assets

Draft: In the GAPS work for FEB2025 we are planning to sync assets to all sites, however this might need to be toggled to only go to owned sites for future cases.

Draft: See moving stores between sites for asset logs transfer

Security

Draft: lens on protocols used (https+certs), site ownership, site authentication, user authentication, patient sync…