1. Current Gap
StopTB workers are each assigned to one VAN/laptop. But when a worker registers a beneficiary directly from their phone (no laptop), the record is created in central with no VanID set. Because down-sync filters by VanID, the record never reaches the worker's laptop — the field worker cannot see or track that beneficiary offline.
2. What We Need
When a StopTB worker registers via phone, the system must tag the record with that worker's VanID automatically, using the existing login→VAN mapping (M_uservanmapping). No manual step. No change to the field worker's workflow.
Once tagged, the record will be picked up by the normal down-sync on the worker's next reconnect.
End-to-End Flow:
| Define Van Structure -> Create VANs in central DB, Map Tab logins with VAN -> Allocate Beneficiaries to Vans -> Central DB (all ben) -> Down-sync (filter by vanid) -> Laptop Local DB (van-specific records) -> Field worker tracks beneficiaries |
|---|
3. Planning Steps:
Phase 1 - Define VAN Structure
- Decide total no. of VANs and names
- List all field locations and corresponding logins
- Get the Beneficiary count per tab login
- Finalize the Location -> Login -> VAN allocation
Phase 2 - Central DB Setup
- Create all VAN records in the central
- Map each tab login to its VAN ID
- Verify all the logins are mapped to a VAN
Phase 3 - Beneficiary Allocation
- Beneficiary record - look at “CreatedBy” - assign the ben to that VAN of CreatedBy login’s VAN mapping
- Verify - VAN ID | Beneficiary Count
Phase 4 - Laptop Configuration & Down-sync
- In each local laptop - configured the VAN
- Trigger the down-sync process from the application of local laptop
- Down-sync pulls all beneficiary records for the laptop's VAN
- Verify the count on laptop
4. Tables Involved in VAN Setup and Sync
| Table | Purpose |
|---|---|
| m_van | VAN master records |
| m_uservanmapping | Maps user login to VAN |
| m_parkingplace | Area / Parking Place |
| m_servicepoint | Service Points |
| m_synctabledetail | Up-Sync table configuration |
| m_providerservicemapping | State _ Service mapping |
| m_vanserialpointmap | VAN to village mapping |
| m_userparkingplacemap | User to Area mapping |
| m_downsynctabledetail | Down-sync table configuration (central → local) — new table to be created |
5. TB Clinical Tables - Verify Sync Columns (VanId, VanSerialNo, ParkingPlaceID, Processed, SyncedBy, SyncedDate, SyncFailureReason)
- Tb_screening
- Tb_suspected
- Tb_stoptb_visit
6. New columns to track per-record down-sync delivery
Central DB (Add to each table which involved in downsync):
| Column | Type | Default | Purpose |
|---|---|---|---|
| DownSynced | char(1) | 'N' | 'N' : Never Sent 'P' : Processed (downsynced) 'F' : Failed or conflict 'U' : Update Pending re-sync |
| DownSyncDate | datetime | Null | When last successfully delivered to local |
| DownSyncFailureReason | varchar(255) | Null | Failure detail if 'F'. Set to 'CONFLICT' when a conflict is detected. |
Local DB:
| Column | Type | Default | Purpose |
|---|---|---|---|
| LastDownSyncDate | datetime | Null | When this specific record was last received from central - the reference point for conflict / update detection |
Note: Processed = 'F' + SyncFailureReason : Carries all required information
Any edits on the record resets Processed back to 'N' - same as a new record.
LastDownSyncDate provides the conflict detection reference during up-sync.
7. Down-Sync Order - FK Dependency Chain
Records must arrive on the laptop in this order:
Master / Reference Data
- M_state / m_district / m_districtblock
- M_providerservicemapping
- m_van/ m_vantype / m_parkingplace
- M_servicepoint / m_servicepointvillagemap
- M_user / m_uservanmapping
- Clinical masters
Beneficiary Root Tables
- M_beneficiaryregidmapping
- I_beneficiarymapping
Then all transactional / clinical tables
Note: The down-sync query for each transactional table is simply SELECT * FROM {table} WHERE VanID = {vanID}
8. m_downsynctabledetail - Down-Sync Table Configuration
Down-sync order and query behaviour are driven by this table, analogous to M_synctabledetail for up-sync.
| Column | Type | Default |
|---|---|---|
| DownSyncTableDetailID | int | auto-increment |
| SchemaName | varchar | db_identity / db_iemr |
| TableName | varchar | tables to down-sync |
| ServerColumnName | text | columns to SELECT from central |
| VanColumnName | text | Columns in local to map with central |
| VanAutoIncColumnName | varchar | Local auto-increment PK column — skipped on INSERT so local DB generates its own value |
| TableType | varchar | MASTER = full pull, no VanID filter / TRANSACTIONAL = filter by VanID + DownSynced |
| SyncOrder | int | Sync sequence — lower number syncs first, enforces FK dependency chain |
| IsActive | bit(1) | Enable or disable a table without deleting the row |
Conflict Handling:
A conflict arises only when the same record is edited in two places after the last down-sync — meaning the central record was changed after the laptop last received it, AND the laptop also has unsaved local changes.
If central.LastModDate > local.LastDownSyncDate and local.processed = 'N' → CONFLICT |
|---|
Master Table Conflicts:
Master / reference tables are central-authoritative. Local never edits them, so no conflict handling is required for those tables.
Overview:
After initial down-sync, two types of edits can happen simultaneously:
- Edits a record directly in the Central DB
- Edits the same record on the local laptop while offline.
Any record edited in the central after down-synced is automatically flagged for re-sync (downsync). Any local edit always resets Processed back to 'N' - covers both new records and records edited after a previous sync.
Key Actions on Each Event:
| Event | Columns Changed |
|---|---|
| New record created in central | Central: DownSynced='N' |
| Record edited in central (was 'P') | Central: DownSynced='U', LastModDate=Now() |
| Down-Sync success | Central: DownSynced='P', DownSyncDate=Now() Local: LastDownSyncDate=Now() |
| Edits record in local | Local: Processed='N', LastModDate=Now() |
| Up-Sync Success | Local: Processed='P', SyncedDate=Now() Central: LastModDate, DownSynced='N' |
| Conflict detected | Local: Processed='F', SyncFailureReason='CONFLICT' Central: DownSynced='F', DownSyncFailureReason='CONFLICT' |
Finding and Resolving Conflicted Records:
select * from <table> where DownSynced='F' and DownSyncFailureReason='CONFLICT' |
|---|
From the list of records, reviews the central version Vs local changes - edits / confirms as needed, and saves. On save, the app updates the Downsynced='N' and DownSyncFailureReason=NULL. On the next down-sync, the central version is pushed to the local laptop. Local is updated to Processed='P', SyncFailureReason=NULL. Conflict cleared.