...
- Admin still needs 6 pages to set up a new HWC (fake Van, Zone, Parking Place still required)
- vanID is still the main identifier everywhere in HWC code
- For every new HWC facility in hierarchy, admin must still create a corresponding Van
...
Side by Side
| Option A (Remove Van) | Option B (Keep Van + Hierarchy) | |
|---|---|---|
| DB migration | Yes (ALTER + backfill) | No |
| Login flow | Changed (facilityID) | No change |
| Worklist queries | Changed (16 queries) | No change |
| Visit save | Changed | No change |
| Admin setup for new HWC | 2 pages | 6 pages (same as now) |
| Fake Van/Zone needed | No | Yes (same as now) |
| Hierarchy available | Yes (direct) | Yes (through Van → FacilityID) |
| Inventory hierarchy walk | Yes | Yes |
| Risk | Medium (many changes) | Very low (minimal changes) |
| Old data handling | Backfill needed | No change needed |
| Rollback | Redeploy old code | Almost nothing to rollback |
...
OLD DATA HANDLING - IN DEPTH
...
1. HOW DATA IS STORED TODAY (Before any change)
i_ben_flow_outreach table - current state:
| benRegID | visitCode | vanID | parkingPlaceID | facilityID | providerServiceMapID | nurseFlag | doctorFlag |
|---|---|---|---|---|---|---|---|
| 50001 | V_50001_1 | 5 | 3 | NULL | 5 (HWC) | 1 | 0 |
| 50002 | V_50002_1 | 5 | 3 | NULL | 5 (HWC) | 1 | 1 |
| 50003 | V_50003_1 | 9 | 4 | NULL | 3 (MMU) | 1 | 0 |
| 50004 | V_50004_1 | 9 | 4 | NULL | 3 (MMU) | 1 | 1 |
facilityID column does not exist yet. vanID is the only location identifier.
m_van table - the bridge:
| VanID | VanName | ParkingPlaceID | FacilityID | ProviderServiceMapID |
|---|---|---|---|---|
| 5 | Gunjalli HWC Van | 3 | 4 | 5 (HWC) |
| 9 | MMU Van Raichur | 4 | 7 | 3 (MMU) |
Every Van already has FacilityID. This is the key.
...
2. STEP 1 - ADD COLUMN (DB Script, before code deploy)
ALTER TABLE i_ben_flow_outreach ADD COLUMN facilityID INT DEFAULT NULL;
i_ben_flow_outreach after ALTER TABLE:
| benRegID | vanID | parkingPlaceID | facilityID | providerServiceMapID | nurseFlag |
|---|---|---|---|---|---|
| 50001 | 5 | 3 | NULL | 5 (HWC) | 1 |
| 50002 | 5 | 3 | NULL | 5 (HWC) | 1 |
| 50003 | 9 | 4 | NULL | 3 (MMU) | 1 |
| 50004 | 9 | 4 | NULL | 3 (MMU) | 1 |
Column added. All NULL. Old code still runs. vanID still there. Zero impact on running system.
...
3. STEP 2 - BACKFILL (same DB script, before code deploy)
UPDATE i_ben_flow_outreach bf
JOIN m_van v ON bf.vanID = v.VanID
SET bf.facilityID = v.FacilityID
WHERE bf.facilityID IS NULL;
i_ben_flow_outreach after backfill:
| benRegID | vanID | parkingPlaceID | facilityID | providerServiceMapID | nurseFlag |
|---|---|---|---|---|---|
| 50001 | 5 | 3 | 4 (HWC facility) | 5 (HWC) | 1 |
| 50002 | 5 | 3 | 4 (HWC facility) | 5 (HWC) | 1 |
| 50003 | 9 | 4 | 7 (MMU facility) | 3 (MMU) | 1 |
| 50004 | 9 | 4 | 7 (MMU facility) | 3 (MMU) | 1 |
vanID=5 → FacilityID=4 (from m_van, HWC record) vanID=9 → FacilityID=7 (from m_van, MMU record)
Both HWC and MMU records filled. vanID still untouched.
...
4. AFTER CODE DEPLOY - NEW PATIENT VISITS
New HWC patient record (vanID = NULL, facilityID direct from session):
| benRegID | vanID | parkingPlaceID | facilityID | providerServiceMapID | nurseFlag |
|---|---|---|---|---|---|
| 50001 | 5 | 3 | 4 | 5 (HWC) | 1 |
| 50002 | 5 | 3 | 4 | 5 (HWC) | 1 |
| 50099 | NULL | NULL | 4 | 5 (HWC) | 1 |
| 50003 | 9 | 4 | 7 | 3 (MMU) | 1 |
| 50004 | 9 | 4 | 7 | 3 (MMU) | 1 |
...
5. HOW EACH SERVICE READS THIS TABLE
HWC Nurse worklist query (new):
WHERE facilityID = 4 AND providerServiceMapID = 5 AND nurseFlag = 1
Finds: benRegID 50001, 50002 (old backfilled) + 50099 (new direct). All HWC patients. Correct.
MMU Nurse worklist query (unchanged):
WHERE vanID = 9 AND providerServiceMapID = 3 AND nurseFlag = 1
Finds: benRegID 50003, 50004. Only MMU patients. Correct.
HWC records (vanID=5) never appear in MMU query. MMU records (vanID=9) never appear in HWC query. Each service sees only its own patients. No overlap, no data leak.
...
6. CROSS SERVICE - SAME PATIENT VISITS BOTH
Lakshmi (benRegID=50001) visited MMU in January, now visits HWC in March:
| benRegID | vanID | facilityID | providerServiceMapID | service | date |
|---|---|---|---|---|---|
| 50001 | 9 | 7 | 3 | MMU | Jan visit |
| 50001 | NULL | 4 | 5 | HWC | Mar visit |
MMU query: WHERE vanID=9 AND psmID=3 → finds Jan MMU visit only HWC query: WHERE facilityID=4 AND psmID=5 → finds Mar HWC visit only
Same patient, two rows, two services. benRegID is shared (patient identity). Visit records are completely isolated by providerServiceMapID + identifier column.
...
7. OLD USER LOGIN ON PROD (No re-mapping needed)
Dr. Ravi currently mapped to Van in production:
| UserID | ParkingPlaceMapID | VanID |
|---|---|---|
| 100 | 45 | 5 |
m_van:
| VanID | FacilityID |
|---|---|
| 5 | 4 |
New login API reads:
UserID=100 → m_uservanmapping → VanID=5 → m_van → FacilityID=4
Then: m_facility(4) → District=Raichur, Block=Manvi
Dr. Ravi logs in. Gets facilityID=4. No admin action needed.
New user Dr. Priya (joined after go-live):
Admin maps: UserID=200 → FacilityID=4 (m_userfacilitymapping)
Login: UserID=200 → m_userfacilitymapping → FacilityID=4
Both work. Old mapping tables stay. New mapping table added alongside.
...
8. COMPLETE SERVICE LINE IMPACT ON PROD DB
| Table | HWC change | MMU reads | TM reads | 104 reads | Impact |
|---|---|---|---|---|---|
| i_ben_flow_outreach | ADD facilityID + backfill | vanID column (untouched) | Not read | Not read | Zero impact on MMU |
| m_van | NOT touched | Still reads | Still reads | - | Zero impact |
| m_parkingplace | NOT touched | Still reads | Still reads | - | Zero impact |
| m_facility | Hierarchy data added (already done) | Not used | Not used | facilityID field | Zero impact |
| m_userfacilitymapping | NEW table created | Not used | Not used | Not used | Additive only |
| m_uservanmapping | NOT touched | Still reads | Still reads | - | Zero impact |
| t_beneficiary | NOT touched | Still reads | Still reads | Still reads | Zero impact |
| i_beneficiarymapping | NOT touched | Still reads | Still reads | Still reads | Zero impact |
...
9. SUMMARY - WHAT PROD WILL LOOK LIKE ON GO-LIVE DAY
BEFORE deployment:
DB script runs silently in background
Old code still running, users working normally
facilityID fills up in i_ben_flow_outreach
No one notices anything
AFTER code deployment:
HWC users: Login works, worklist shows all patients
(old backfilled + new direct)
MMU users: Absolutely nothing changes
TM users: Absolutely nothing changes
104 users: Absolutely nothing changes
Old HWC patients: All visible in worklist
New HWC patients: Written with facilityID directly
Old HWC users: Login works via Van fallback
New HWC users: Login works via facility direct