STOP-TB Sync Redesign: What Changed Since X, Full Offline Data and Station Hand-offs

Date: 2026-10-29 (rewritten after a full assessment)

Repos: FLW-API, Identity-API, TM-API, MMU-API, Common-API (all on release-3.12.0), AMRIT-DB (main; no release-3.12.0 branch), STOP-TB-App

Related:STOP-TB-App PR #321 (sync reliability fixes); STOP-TB sync gap analysis and Aug 2026 field log analysis (available on request)

Contents

1. Summary

2. How it works today

3. Requirements

4. Problem 1: "What changed since X?"

5. Problem 2: All data on the tablet

6. Problem 3: Station hand-offs and overwrites

7. Target design

8. Order of work

9. Testing and measurement

10. Rollout

11. Decisions needed

12. Appendix: evidence

13. Implementation status

1. Summary

Three problems, in the order to solve them:

1. "What changed since X?" cannot be answered today.

   • Every pull downloads everything. With 4,000 beneficiaries, a pull is about 400 page requests plus every TB record. In camp mode pulls run back to back while the app is open.

   • Timestamps alone can't fix this:

     • several write paths freeze, backdate or null LastModDate;

     • rows that reach the hub from central keep central's older timestamp;

     • deletes aren't recorded;

     • the columns are second-precision with no indexes.

   • Fix: a change log on each camp laptop (hub), filled by database triggers. Tablets read changes with an opaque token from that laptop, through one endpoint. Central is not involved.

2. Tablets need all the data, not one village. After login, users take tablets to villages and work offline.

   • Fix: the scope becomes all villages assigned to the user. Endpoints accept multiple villages, the village picker becomes a view filter, and photos are downloaded separately and lazily.

3. Stations overwrite each other. Registrar → screening → nurse → lab technician → counsellor each work on their own tablet.

   • Only edited records are pushed, but each is sent with all its fields, not just the ones that changed. The server keeps the last write with no version check.

   • A stale tablet can undo a registrar's correction, roll back a weight, or erase a lab result.

   • Fix, in order of value:

     • field ownership by role;

     • pushes send only changed fields;

     • a version check that uses the change log;

     • a "fetch this beneficiary now" call for when a person arrives at a station.

Problem 1 is the foundation: problems 2 and 3 both depend on it.

2. How it works today

Diagram 1: Current data flow (Mermaid source below; image attachment d1.png)

flowchart LR
  subgraph Village["Village (offline)"]
    T1[Tablet<br/>Room DB]
  end
  subgraph Camp["Camp (local Wi-Fi)"]
    T2[Registrar tablet]
    T3[Nurse tablet]
    T4[Lab / counsellor tablets]
    HUB[(Camp hub laptop<br/>FLW / Identity / TM / MMU APIs<br/>own MySQL, VanID = N)]
  end
  C[(Central server<br/>stoptb.piramalswasthya.org)]

  T2 -- "push edited records (all fields of each)<br/>pull EVERYTHING ~every 10s" --> HUB
  T3 -- "push edited records (all fields of each)<br/>pull EVERYTHING" --> HUB
  T4 -- "push / pull" --> HUB
  HUB -- "upsync (manual / 30 min, off by default)<br/>keeps hub LastModDate" --> C
  C -- "downsync (manual, VanID = N only)<br/>keeps central LastModDate" --> HUB
  T1 -. "syncs when back at a camp" .-> HUB

What a pull does now (STOP-TB app):

• Beneficiaries:flw-api/beneficiary/getBeneficiaryData, village-scoped, 10 per page with OFFSET, ordered by registration date.

  • New registrations during a pull shift the pages, so records get skipped or repeated.

  • On the server, each record costs about 8 queries plus one HTTP call to FHIR.

• 9 TB and form datasets, each the full village list in one response with no paging: screening, general examination, general OPD, diagnostics, suspected, confirmed, counselling-completed, and 4 contact-tracing/TPT form types.

  • Confirmed has no village filter; it returns every confirmed case for the user.

  • Suspected re-runs incentive logic on every pull.

• Diagnostic order status: 6 calls, plus one result call per completed order.

• Master data: 3 tables (visit categories, chief complaints, drugs) deleted and rewritten on every pull.

Hub ↔ central (MMU-API data sync):

• Upsync (hub → central): sends rows with Processed != 'P'.

  • Central inserts or updates each row by (VanSerialNo, VanID); the hub always wins.

  • Then the hub marks the row processed, which moves its LastModDate to the hub's current time.

• Downsync (central → hub): started manually, and only for rows with VanID = N.

  • Updates keep central's LastModDate; inserts end up with the hub's current time by accident.

  • Downsync also creates worklist rows, without which tablets don't see the beneficiary at all.

• IDs:

  • BenRegId / BeneficiaryID are the same on hub and central.

  • Every other primary key differs: central keeps the hub's key as VanSerialNo, the hub keeps central's as CentralID.

• Tablets talk only to a hub (since 1f19f944). Each hub has its own clock and its own data.

3. Requirements

4. Problem 1: "What changed since X?"

4.1 Why LastModDate > X fails today

You could patch each of these one by one (§4.4 option B), but holes would keep coming back as new code is written.

4.2 Recommended: a change log on each camp laptop, filled by triggers

Each camp laptop (hub) gets one change log, in db_iemr; triggers on db_identity tables write to it across schemas, so the tablet holds a single token. Production central gets neither the table nor the triggers, and the log is never synced to central: it isn't in the upsync table list (m_synctabledetail) and must not be added. Tablets only talk to hubs, so the log is only needed there. The one exception is UAT central (uatstoptb.piramalswasthya.org), which gets its own log so tablets can be tested against it directly (§7 explains the gate).

CREATE TABLE t_sync_change_log (            -- one table, in db_iemr; db_identity triggers write to it too
  seq            BIGINT AUTO_INCREMENT PRIMARY KEY,   -- order of changes, local to this hub
  entity         VARCHAR(40) NOT NULL,                -- record type the app receives (beneficiary, tb_screening, ...)
  row_pk         BIGINT NOT NULL,                     -- that record's key (BenRegId for beneficiary)
  op             CHAR(1) NOT NULL,                    -- I, U or D
  ben_reg_id     BIGINT NULL,                         -- whichever ID the source row carries;
  beneficiary_id BIGINT NULL,                         -- the feed resolves the village from these when it reads
  changed_at     DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  KEY (ben_reg_id, seq), KEY (beneficiary_id, seq), KEY (entity, row_pk),
  KEY (changed_at)                                    -- for the clean-up of old lines (§4.6)
);

• Triggers:AFTER INSERT, AFTER UPDATE and AFTER DELETE on each STOP-TB table write a row. Child tables (complaints, drugs, answers, vitals) also write a row for their parent and carry ben_reg_id.

• Why triggers: they fire for every write path. That includes JPA, native SQL, rows arriving on the hub by downsync from central, Flyway backfills and manual fixes. They fire for hard deletes too. The tablet never relies on LastModDate.

• The token: what the tablet keeps is {serverId, seq}, where serverId is the hub's VanID.

  • If the tablet presents a token from a different server, that server starts it again from a fresh snapshot.

  • This handles a tablet that moves between hubs, and it never compares clocks between machines.

• Late commits:AUTO_INCREMENT values are taken before commit, so a transaction holding seq 41 can commit after one holding 42. To be safe:

  • the server returns only up to a safe point, the highest seq older than a short lag (for example 5 seconds);

  • the tablet re-reads from slightly before its token;

  • duplicates are harmless because the app applies the server copy only when it's newer.

• Retention: keep 30 days of log. A token older than that gets a fresh snapshot.

Worked example: how the token picks up an edit to an older record

The number the tablet remembers is the line number in the log (seq), not a record ID. Every change appends a new line at the end, whichever record it touches:

Log (seq = line number, always increasing)
 ...
 #1000  beneficiary 900   updated   ← this tablet has read up to here
 #1001  tb_screening 455  inserted
 #1002  beneficiary 900   updated   ← another tablet edits record 900 again
 #1003  vitals 77         inserted

• The tablet's token says "read up to #1000".

• Another tablet edits record 900. That writes a new line, #1002, at the end of the log.

• On its next sync the tablet asks for everything after #1000. It gets #1001 to #1003, sees that record 900 changed, and the server sends the current version of record 900.

So an edit to an old record always reaches every tablet: the edit creates a new line after each tablet's bookmark.

• Several edits to one record: if record 900 changes five times between two syncs, the server sends it once, in its latest state.

• Deletes: a delete also writes a line (beneficiary 900 deleted), so tablets learn about deletes the same way.

• Why this beats timestamps: the database assigns the line number at the moment of the change, so unlike LastModDate it can't be frozen, backdated or copied from another server.

Diagram 2: Change log filled by triggers (Mermaid source below; image attachment d2.png)

flowchart TB
  subgraph Writers["Anything that writes"]
    A[FLW/Identity/TM/MMU APIs<br/>JPA + native SQL]
    B[Downsync from central /<br/>upsync bookkeeping]
    M[Manual fixes / Flyway backfills]
  end
  subgraph DB["MySQL on the camp laptop (hub)"]
    TBL[(STOP-TB tables<br/>beneficiary, TB, OPD, forms, orders)]
    TRG{{AFTER INSERT / UPDATE / DELETE<br/>triggers}}
    LOG[(t_sync_change_log<br/>seq, table, pk, op, ben_reg_id, village_id)]
  end
  FEED[FLW-API<br/>GET /stoptb/sync/changes?token=]
  TAB[Tablet]

  A --> TBL
  B --> TBL
  M --> TBL
  TBL --> TRG --> LOG
  LOG --> FEED -->|"changes + nextToken"| TAB

4.3 One "changes" endpoint instead of 15+ calls

POST flw-api/stoptb/sync/changes, served by FLW-API wherever the change log is installed. Tablets call it on their camp hub (or UAT central when testing).

Request:  { token: "<opaque or null>", scope: { villageIds: [...] }, limit: 500 }
Response: { serverId, items: [ { type: "beneficiary" | "household" | "tbScreening" | "generalExam" | "generalOpdVisit"
                                       | "diagnostics" | "suspected" | "confirmed" | "diagnosticOrder" | "formResponse",
                                 op: "upsert" | "delete", key: {...}, version: <seq>, data: {...} } ],
            nextToken, hasMore, snapshot: true|false }

• Token null: snapshot.

  • The server notes the current safe point, then pages through everything in scope ordered by (type, benRegId, pk) using keyset paging, not OFFSET.

  • When the last page is sent, nextToken holds the noted safe point. Changes made during the snapshot are then picked up as ordinary changes.

• Token present: changes since that point.

  • The server reads log rows in scope with seq > token, up to limit, removes duplicates by (table, pk), and loads the current rows for them.

  • A deleted row comes back as op: delete.

• Payload builders: these reuse the mappers that today's getAll endpoints use (StopTBServiceImpl, TBSuspectedServiceImpl, BeneficiaryServiceImpl's record builder), but batched, so there are no per-record round trips.

  • The FHIR health-ID lookup is batched or moved out of this path.

  • Photos are not included; see §5.3.

• version = the latest seq for that row. Push uses it for the version check (§6).

• Old endpoints stay as they are (R7).

Diagram 3: Changes feed: snapshot, then changes (Mermaid source below; image attachment d3.png)

sequenceDiagram
  participant T as Tablet
  participant H as Camp hub (FLW-API)
  participant L as Change log
  T->>H: changes(token=null, scope)
  H->>L: note current safe point S
  loop snapshot pages (keyset)
    H-->>T: items (snapshot=true), nextToken=page cursor
  end
  H-->>T: last page, nextToken={hubId, S}
  Note over T: saves token in encrypted prefs
  loop every tick (camp) / on reconnect
    T->>H: changes(token={hubId, S})
    H->>L: seq > S AND scope, ≤ safe point
    H-->>T: only the changed rows (often 0), nextToken={hubId, S'}
  end
  T->>H: changes(token={otherHub, X})
  H-->>T: token is from another server → snapshot again

4.4 Options considered

Things to check for option C: how much the triggers slow writes on i_ben_flow_outreach and the identity tables (measure on a hub), that a trigger never causes an insert to fail (keep the body minimal), and that the log is never replicated: it stays on each camp laptop.

4.5 How it works end to end

What changes besides the log table

How the server returns "everything after line N"

The tablet sends token = {hub 415, N = 1000}, scope villages [12, 14].

Step 1: find what changed. The log query:

SELECT table_name, row_pk, MAX(seq) AS last_seq, MAX(op = 'D') AS deleted
FROM t_sync_change_log
WHERE village_id IN (12, 14) AND seq > 1000 AND seq <= :safePoint
GROUP BY table_name, row_pk
ORDER BY last_seq
LIMIT 500;

This returns a short list, e.g. "beneficiary 900, tb_screening 455, vitals 77". A record changed five times appears once.

Step 2: load the current records, one query per table:SELECT … FROM tb_screening WHERE id IN (455, …), the same for beneficiaries, vitals and so on. The existing builders turn them into JSON; deleted ones go out as op: delete.

Step 3: return the records plus nextToken = {hub 415, last_seq of the last item}. If there were more than 500, hasMore = true and the tablet calls again with the new token.

Every query is either indexed or a lookup by ID, so the work is proportional to what changed, not to the size of the village.

What the tablet does each sync

The tablet never works out which records to fetch. It keeps one bookmark and the hub decides what is new from the log.

What it keeps: the token {hubVanId, seq}, e.g. {415, 1000}, meaning "I have applied everything from hub 415 up to log line #1000". It lives in encrypted prefs.

One sync round:

1. Ask: send the token and the user's villages: { token: {415, 1000}, scope: { villageIds: [12, 14] }, limit: 500 }.

2. The hub answers as described above: the changed records in their current state, deletes as op: delete, plus nextToken (e.g. {415, 1003}) and hasMore. If nothing changed, items is empty and nextToken is the safe point.

3. Apply and save together: in one Room transaction the tablet upserts or deletes the items and then saves nextToken. If the app is killed halfway, the token was not saved, so the next round re-reads the same lines; applying them twice is harmless. Records with unsynced local edits are not overwritten (the same rule as today's pull).

4. Repeat while hasMore is true, straight away with the new token, until nothing is left.

When the hub sends a snapshot instead of changes:

The normal case in camp: nothing changed, so one indexed lookup returns zero rows and the token stays the same. That is what the 10-second camp loop costs.

Logging in again, a new tablet, or a different hub

This is the snapshot path. It's the same for re-login, a new tablet, a reinstall, a different hub, or a token older than the log's 30-day retention.

• On logout: the app wipes its database and preferences (clearAllTables, deleteForLogout), so the saved token is gone.

• On the next login: the tablet sends token = null, and the server:

  1. notes the log's current line number, e.g. S = 5,230;

  2. sends everything in the user's scope, read from the real tables rather than the log, page by page with keyset paging, so no records are skipped while new ones are being added;

  3. on the last page, returns nextToken = {hub 415, S = 5230}.

• Edits made by other tablets during the download have log lines after 5,230, so the very next sync picks them up and nothing is missed.

• After that, every sync is a delta.

So re-login costs one full, properly paged download, then only changes.

Caution on logout: wiping the database at logout deletes records that haven't synced. The logout dialog warns with the count (fixed in PR #321), but it still lets the user go ahead. With tablets working offline in villages, consider blocking logout until everything has synced, or at least requiring a second confirmation (decision 8 in §11).

4.6 Scaling

The change log is one table, but each row is small: a line number, record type, row ID, change type, beneficiary IDs and time. That's roughly 100–150 bytes per line including indexes, and none of it is patient data. The log notes that something changed; the data itself stays in the normal tables.

Size per camp laptop (estimates; to be measured in step 1):

Why reads stay fast at any size:

• A tablet always asks for "lines after #N for my villages". The line number is the primary key, so the hub reads only the new lines and checks each against the tablet's villages; old lines are never scanned, so a full 30 days of lines is about as fast as a few thousand.

• A quiet sync costs one index lookup that returns nothing.

• Compared with today, where every pull re-reads every record in the village, this is far cheaper.

Keeping it small:

1. Hourly clean-up. A MySQL event deletes lines older than the retention period (30 days), in small batches (for example 10,000 rows at a time) so it never holds long locks. The changed_at index keeps each batch quick. At hub scale that's about 15,000 lines a day, which is trivial.

2. Log the parent, not noisy child rows. Counselling answers are deleted and re-inserted on every save, so 40 questions would mean 80 log lines. Instead, log one line for the parent form response. The same goes for drug and complaint rows under an OPD visit. The feed then sends the parent with its children.

3. Each server keeps its own log, never synced. A laptop's log is bounded by one camp's activity (about 60 MB at 30 days), whatever the number of camps. Central and state servers also carry a log of their own writes (one small insert per save), cleaned up the same way; nothing reads it there.

Cost on saves: each save also writes one small extra row, in the same transaction. That's microseconds per save, but it must be measured on a real camp laptop (step 1). Saves running at the same time don't block each other on the counter in MySQL's default configuration (innodb_autoinc_lock_mode = 2).

Alternative if triggers are a concern: read MySQL's own change history (the binlog) with a tool such as Debezium. It adds nothing to saves, but needs Kafka or another extra service running on every camp laptop, which is a lot for field hardware. For hubs, triggers are the simpler choice.

5. Problem 2: All data on the tablet

5.1 Scope

Fix needed first:UserServiceImpl only reads the user's first role-mapping row (getUserRole(userId).get(0)), so villages on other rows are dropped. It should combine villages from all rows.

5.2 Size

These figures are for the first download. With the changes feed, later pulls move only what changed.

5.3 Photos

• Today: photos are sent inline as base64 inside every beneficiary record, on every pull. They are the bulk of the download.

• Target: the changes feed sends only a photo reference (ID plus hash). The tablet downloads photos in the background, Wi-Fi only:

  • first for beneficiaries in today's worklist;

  • the rest over time;

  • or on demand when a record is opened.

• Security: photos are stored encrypted. Today they are plain JPEG files in filesDir.

5.4 App changes for full scope

• Village picker: the selected village becomes a view filter only. Sync always covers the whole scope.

• Paging: large lists move to Paging 3. About 40 Flow<List<…>> queries load whole tables into memory and re-run on every write during sync.

• Database: add indexes or precomputed columns for age and TB-status filters (the BEN_BASIC_CACHE view scans every row).

  • Room migrations are needed for this. That conflicts with R8, but these are schema changes for the UI, not sync state, and can come later.

  • fallbackToDestructiveMigration was removed in PR #321, so migrations are now safe to add.

• Security:

  • SQLCipher on every field build; today it is off in debug builds.

  • Show a storage estimate and progress before the first download.

  • A policy for deleting data the user is no longer assigned to.

6. Problem 3: Station hand-offs and overwrites

6.1 Who writes what

How saves work today:

• Only records edited on the tablet (marked unsynced) are pushed. Each one is sent with all its fields from that tablet's copy, not just the changed ones (asNetworkPostModel, TBScreeningSaveRequest.from(cache)).

• Anthropometry, screening status and death outcome mark the whole beneficiary record as changed. The next push then re-sends demographics as they are on that tablet.

• The server matches records by natural key and overwrites every column. There is no @Version anywhere.

6.2 What gets lost

6.3 Fix, in order of value

1. Field ownership by role, checked on the server and respected in the app:

   • anthropometry gets its own push, not the beneficiary record;

   • updateScreeningStatus stops marking the whole record as changed;

   • the server ignores demographic fields from non-registrar users;

   • diagnostics: the nurse owns orders and referrals, the lab tech and the server own results;

   • tb/suspected/saveAll never overwrites result fields: configure modelMapper to skip nulls, or use explicit setters.

2. Partial pushes: send only the fields that changed, and treat a missing key as "leave it alone". The liquidCultureResult handling already does this; extend it to every field.

3. Version check, using the change log:

   • each pushed record carries the version (seq) the tablet last pulled;

   • if the server's row has a newer version, the server returns a conflict with its current row;

   • the app re-applies only the fields its role owns, then pushes again. No user prompt for owned fields.

   • This version is the same one the changes feed returns, so no new column is needed.

4. "Fetch this beneficiary now": a quick hand-off when a person reaches a station.

   • GET flw-api/stoptb/beneficiary/{benRegId}/all returns the beneficiary plus all their TB, form and order records, with versions.

   • The Examine screen calls it when it opens, if the device is online.

   • Identity getByBenId already exists, but the app's call to it is commented out.

Diagram 4: Station hand-off with version check (Mermaid source below; image attachment d4.png)

sequenceDiagram
  participant R as Registrar tablet
  participant H as Camp hub
  participant N as Nurse tablet
  R->>H: push beneficiary (changed fields: name) v=40
  H-->>R: ok, v=57
  Note over N: beneficiary walks up to the nurse
  N->>H: fetch beneficiary 123 (all records)
  H-->>N: beneficiary v=57 + TB rows
  N->>H: push anthropometry (own endpoint, base v=57)
  H-->>N: ok, v=61
  Note over N,H: if the nurse's base were older than the server's version<br/>for a field she owns, she gets a conflict:<br/>re-apply her owned fields, push again
  loop every camp tick
    R->>H: changes(token)
    H-->>R: beneficiary 123 v=61 (weight)
  end

7. Target design

Diagram 5: Target design (Mermaid source below; image attachment d5.png)

flowchart LR
  subgraph Tablet
    UI[Screens<br/>village = view filter]
    DB[(Room DB<br/>whole user scope)]
    SY[Sync engine<br/>token per server,<br/>field-level push]
  end
  subgraph Hub["Camp hub laptop"]
    API["FLW-API<br/>/sync/changes<br/>/beneficiary/:id/all<br/>save endpoints + version check"]
    MY[(MySQL + t_sync_change_log<br/>triggers)]
    PH[Photo store]
  end
  C[(Central)]
  UI --> DB
  DB --> UI
  DB --> SY
  SY --> DB
  SY -->|"changes(token, scope)"| API
  SY -->|"push changed fields + version"| API
  SY -->|"photos, Wi-Fi only, lazy"| PH
  API --> MY
  MY --> API
  MY -->|"upsync"| C
  C -->|"downsync (triggers record arrivals)"| MY

How the change log is installed. It is part of AMRIT-DB's main migrations (dbiemr/V132__Sync_Change_Log.sql, dbidentity/V32__Sync_Change_Log_Beneficiary.sql), so it runs on every server with the next db.war: no .env setting, no new database, nothing to do on the 140 camp laptops.

• Why not hub-only: a hub-only gate needed a setting on every laptop (or relying on STOPTB_VAN_ID), and logging into 140 laptops isn't possible. Installing it everywhere costs central and state servers one small insert per save, which is acceptable.

• Privileges (every server): the migration user needs TRIGGER and EVENT (and, with binary logging, SUPER or log_bin_trust_function_creators=1 to create triggers), or V132 fails and db.war doesn't start. The db_identity user, which defines the beneficiary triggers, needs INSERT on db_iemr.t_sync_change_log, or beneficiary saves fail. Camp laptops use one account with GRANT ALL; check central and state servers before deploying.

• Clean-up: a MySQL event, so event_scheduler must be on (the MySQL 8 default).

• FLW-API: serves /stoptb/sync/changes only where the log table exists and every source table has its triggers (checked from information_schema); a server on an older AMRIT-DB answers supported:false and the app keeps the full pull.

8. Order of work

Steps 1–3 deliver "what changed since X?". Step 4 can start in parallel once step 2 has its scope parameter. Steps 6–7 can run in parallel with 4–5.

9. Testing and measurement

• Change log:

  • every write path creates a log row, including JPA, native SQL, the downsync insert/update, upsync, and a hard delete;

  • two parallel transactions that commit out of order are both delivered;

  • a token from another hub triggers a new snapshot.

• Changes feed:

  • a snapshot followed by changes equals a fresh full pull;

  • 0 items when nothing changed;

  • deletes arrive;

  • paging returns no duplicates and no gaps while rows are inserted during the snapshot.

• Conflicts: run L1–L6 as tests. None loses data after step 7; L1, L2, L4 and L5 are already fixed by step 6.

• Load at camp scale (one hub, 5 tablets, 4k and 20k beneficiaries): requests per minute, bytes per hour, hub CPU and DB load, and hand-off time (target under 30 seconds, under 3 seconds on demand).

• Field comparison: a day of sync logs against the August baseline. Each record should be downloaded at most once per change.

10. Rollout

1. AMRIT-DB: one db.war release goes everywhere and installs the change log and triggers on every server (main migrations V132 / V32, §7). Check the privileges in §7 on central and state servers first. Delete flags and indexes go everywhere. Test on UAT central first.

2. FLW-API / Identity-API release with the new endpoints. Old apps are unaffected.

3. App release that uses the changes feed when the hub offers it, and old endpoints otherwise.

4. Field ownership and version checks are switched on per endpoint once every app in the field sends versions. Until then, a push without a version is accepted as it is today.

11. Decisions needed

1. Change tracking: do you accept DB triggers plus a change log (recommended), or should we go with the fallback of fixing timestamps everywhere?

2. Scope: is it all villages assigned to the user (recommended), the camp catchment, or the whole service area?

3. Photos: lazy and Wi-Fi only, or none on the tablet unless the record is opened?

4. Field ownership map: confirm which role owns which fields, especially screening (registrar and nurse both edit it today) and death/outcome (counsellor).

5. Deletes on the tablet: remove the row, or keep it and hide it?

6. Retention: how long to keep the change log (30 days is suggested), and what a tablet that has been away longer gets (a fresh snapshot).

7. Old-data policy: what happens to data on a tablet when the user is no longer assigned to a village?

8. Logout with unsynced records: block logout until everything has synced, or allow it after a second confirmation? Logout wipes the tablet's database, so unsynced records are lost.

12. Appendix: evidence

• Change tracking: per-table assessment of mod columns, broken write paths, soft and hard deletes, and indexes, based on AMRIT-DB origin/main (no release-3.12.0 branch) and the API repos on release-3.12.0.

  • Key files: Identity RmnchDataSyncServiceImpl.java:187,410,485,524; TM BenVisitDetailRepo.java:64,107; FLW FormResponseItemSaver.java:349; V96 (tb_diagnostic_order); V120/V121 (downsync columns).

• Hub↔central:

  • MMU UploadDataToServerImpl, GetDataFromVanAndSyncToDBImpl:810-864, DownSyncDataFromServerImpl:440-652, DataSyncRepositoryCentralDownload:245-354, AutoSyncService:125-135.

  • AMRIT-Deployment-Files/camp-laptop-setup/config/env.template (STOPTB_VAN_ID; add AMRIT_HUB=true).

  • STOP-TB commit 1f19f944.

• Scope:UserServiceImpl.java:31 (get(0)), BeneficiaryServiceImpl.java:143-178,229-280,603, StopTBServiceImpl getAll paths; app ServiceLocationActivity, RecordsRepo.allVillageIds, InAppDb BEN_BASIC_CACHE.

• Station writes:RoleManager/RoleModuleConfig; AnthropometryViewModel.kt:84-101; BenDao.updateScreeningStatus; StopTBServiceImpl save paths (:239-301, :515-559); TBSuspectedServiceImpl:78-116; DiagnosticOrderServiceImpl:229-239,528-543,841; Identity RmnchDataSyncServiceImpl:280-418,892-948.

13. Implementation status (2026-10-29)

Built and pushed, not yet merged or deployed. PRs: AMRIT-DB #225, FLW-API #393, STOP-TB-App #322.

Differences from the design above:

• One log, village resolved on read. The log has ben_reg_id and beneficiary_id instead of village_id. The feed scopes through i_ben_flow_outreach when it reads, like today's getAll endpoints, so triggers do no lookups and a beneficiary who changes village is handled.

• Token is d.<server_uuid>.<seq>.<issued>.<typesMask> (or s.… mid-snapshot). The hub's MySQL server_uuid identifies the server, so no van ID is needed; the issue time tells the hub when the token is older than the log's retention.

• Clean-up is a MySQL event (hourly, 10,000 rows per run) in the migration, not an application scheduler.

• Safety checks. FLW-API serves the feed only where the log exists and every source table has its triggers (checked from information_schema); otherwise {supported:false} and the app keeps the full pull.

• Installed everywhere by the main migrations, not gated to hubs: nothing to set on 140 laptops (2026-10-29 decision). The hub gate (AMRIT_HUB, *_hub folders) and AMRIT-Deployment-Files #150 were dropped.

Not done yet:

• App: form responses (counselling, contact tracing, TPT) and diagnostic orders still use their own endpoints; the feed can already serve them (formResponse, diagnosticOrder).

• App: deletes from the feed are counted but not applied (decision 5).

• App: scope is still the selected village; all assigned villages (step 4) next.

• A record whose beneficiary reaches the tablet after it (rare ordering) is skipped until it changes again or the next re-login snapshot.

• Upsync marks every pushed row processed, which logs an update for it; harmless (deduplicated, re-sent once) but worth measuring.

• Every server's DB users need TRIGGER and EVENT (plus SUPER or log_bin_trust_function_creators=1 with binlog), and the db_identity user needs INSERT on db_iemr.t_sync_change_log (camp laptops already have all of this).

• The feed, like the existing getAll endpoints, trusts the service and villages in the request. Checking them against the user's assigned villages goes with step 4 (multi-village scope).

• A feed page carries at most 25 beneficiaries (each costs a FHIR call); tokens are bound to the record types they were issued for.