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:

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 

5. Tables involved in storing the TB related information - Verify Sync Columns (VanId, VanSerialNo, ParkingPlaceID, Processed, SyncedBy, SyncedDate, SyncFailureReason)  

• Tb_screening
• Tb_suspected 
• i_beneficiarydetails_rmnch
• i_householddetails

6. New columns to track per-record down-sync delivery 

Central DB (Add to each table which involved in downsync):

 Local DB:

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.

 
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:

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.