...
- DB: ZERO changes to
i_ben_flow_outreach(no new column, no backfill) - HWC-API: ZERO changes to login, worklist, visit save
- HWC-UI: ZERO changes to login, worklist, visit save
- Inventory-API: ~3 files (hierarchy walk using Van → FacilityID → ParentFacilityID)
...
- 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 - COMPLETE IMPACT ANALYSIS
...
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 |
| 50005 | V_50005_1 | 20 | - | NULL | 5 (HWC) | 2 | 9 |
facilityID column does not exist yet. vanID is the only location identifier. Row 50005 is an existing pending TC request - vanID already updated to TM specialist Van (20) when HWC doctor referred.
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) |
| 20 | TM Specialist Van | - | NULL | 6 (TM) |
Every HWC Van must have FacilityID. This is the bridge for backfill. TM Van has NULL FacilityID - this is fine, TM queries by vanID not facilityID.
...
2. PRE-DEPLOYMENT MANDATORY CHECKS (Before running DB script)
This is the only genuine risk in the entire implementation.
If any HWC Van has FacilityID = NULL in m_van, the backfill JOIN will set facilityID = NULL for those patient records. After code deploy, new worklist query WHERE facilityID = 4 will NOT find those patients. They disappear from worklist.
Check 1: Must return 0 rows. If not, fix those Vans in Admin first.
SELECT VanID, VanName FROM m_van
WHERE FacilityID IS NULL
AND Deleted = false
AND ProviderServiceMapID = 5; -- HWC service map only
If any Van returned → go to Admin → open that Van → map it to correct Facility → recheck.
Only proceed to DB script after this returns 0 rows.
...
3. 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 |
| 50005 | 20 | - | NULL | 5 (HWC) | 2 |
Column added. All NULL. Old code still running. vanID still there. All users working normally. Zero disruption.
...
4. 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 | note |
|---|---|---|---|---|---|---|
| 50001 | 5 | 3 | 4 (HWC) | 5 (HWC) | 1 | filled from Van 5 |
| 50002 | 5 | 3 | 4 (HWC) | 5 (HWC) | 1 | filled from Van 5 |
| 50003 | 9 | 4 | 7 (MMU) | 3 (MMU) | 1 | filled from Van 9 |
| 50005 | 20 | - | NULL | 5 (HWC) | 2 | TM Van - NULL is fine, TM queries by vanID=20 |
vanID=5 → FacilityID=4 (HWC records filled) vanID=9 → FacilityID=7 (MMU records filled) vanID=20 → FacilityID=NULL (TM Van - no facility mapping needed, TM still works)
vanID untouched for all records. Old code still runs fine.
Check 2: Post-backfill verification. Must return 0 before deploying code.
SELECT COUNT(*) FROM i_ben_flow_outreach
WHERE facilityID IS NULL
AND vanID IN (
SELECT VanID FROM m_van WHERE ProviderServiceMapID = 5
)
AND providerServiceMapID = 5;
If this returns any count → some HWC Van still has NULL FacilityID → fix in Admin → re-run backfill → recheck.
Only proceed to code deployment after this returns 0.
...
5. AFTER CODE DEPLOY - NEW PATIENT VISITS
| benRegID | vanID | parkingPlaceID | facilityID | providerServiceMapID | nurseFlag | note |
|---|---|---|---|---|---|---|
| 50001 | 5 | 3 | 4 | 5 (HWC) | 1 | old - backfilled |
| 50002 | 5 | 3 | 4 | 5 (HWC) | 1 | old - backfilled |
| 50099 | NULL | NULL | 4 | 5 (HWC) | 1 | new - direct |
| 50003 | 9 | 4 | 7 | 3 (MMU) | 1 | MMU - untouched |
| 50005 | 20 | - | NULL | 5 (HWC) | 2 | TM TC - vanID=20 intact |
...
6. HOW EACH SERVICE READS THIS TABLE
HWC Nurse worklist (new query - confirmed from code):
WHERE facilityID = 4 AND providerServiceMapID = 5 AND nurseFlag = 1
Finds: 50001, 50002 (old backfilled) + 50099 (new direct). All HWC patients. Correct.
MMU Nurse worklist (unchanged query - confirmed from code):
WHERE vanID = 9 AND providerServiceMapID = 3 AND nurseFlag = 1
Finds: 50003. Only MMU patients. Correct.
HWC records never appear in MMU query. MMU records never appear in HWC query. Zero overlap.
...
7. TM (TELEMEDICINE) - ZERO CHANGE. ZERO IMPACT.
TM uses HWC code for TC flow. We are not touching TM-API at all.
When HWC doctor refers patient to TM specialist, HWC-API updates the record:
SET vanID = TM_Specialist_Van_20,
specialist_flag = 1,
tCSpecialistUserID = 300
The record's vanID is overwritten with TM specialist's vanID (20) at the time of TC request. This happens whether the original record had vanID=5 (old) or vanID=NULL (new after our change).
Old pending TC (before go-live):
| benRegID | vanID | facilityID | specialist_flag |
|---|---|---|---|
| 50005 | 20 (TM Van) | NULL | 1 (pending) |
TM specialist worklist:
WHERE specialist_flag = 1 AND vanID = 20
Finds 50005. Old TC request visible. Zero break.
New TC request (after go-live): New HWC record has vanID=NULL, facilityID=4. Doctor refers to TM → update writes vanID=20 → TM finds it. Zero break.
...
8. FLW - ZERO CHANGE. ZERO IMPACT.
FLW ASHA workers register patients and do home visits. Their data lives in separate FLW-specific tables (AncCare, PNCCare, ChildVaccination etc.). They do NOT write to i_ben_flow_outreach.
When FLW registers patient Lakshmi → benRegID=50001 in shared beneficiary tables. When Lakshmi walks into HWC → nurse searches by name/phone → finds benRegID=50001 → visible in HWC.
Search is by name/phone/Aadhaar. vanID never part of search. Zero break.
...
9. CROSS SERVICE - SAME PATIENT VISITS MULTIPLE SERVICES
Lakshmi (benRegID=50001) visited MMU in January, 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 → Jan MMU visit only HWC query: WHERE facilityID=4 AND psmID=5 → Mar HWC visit only
Each service sees only its own records. No overlap. No data leak.
...
10. OLD USER LOGIN ON PROD (No admin action needed)
Old HWC user Dr. Ravi - mapped to Van:
| UserID | VanID | m_van FacilityID |
|---|---|---|
| 100 | 5 | 4 |
New login API fallback:
UserID=100 → m_uservanmapping → VanID=5
→ m_van → FacilityID=4
→ m_facility(4) → District=Raichur, Block=Manvi
Session: facilityID=4
Dr. Ravi logs in. Worklist shows all patients (old backfilled + new). No re-mapping needed.
New user Dr. Priya - mapped directly to facility:
Admin maps: UserID=200 → FacilityID=4 (m_userfacilitymapping)
Login: UserID=200 → m_userfacilitymapping → FacilityID=4
Both old and new users work simultaneously. No conflict.
...
11. COMPLETE TABLE AND SERVICE LINE IMPACT
| Table | Change | HWC | MMU | TM | FLW | 104 | 1097 | ECD | FHIR | Scheduler | Impact |
|---|---|---|---|---|---|---|---|---|---|---|---|
| i_ben_flow_outreach | ADD facilityID + backfill | facilityID column | vanID untouched | TC vanID=TM Van intact | Not used | Not used | Not used | Not used | Reads data | Not used | Zero operational impact |
| m_van | NOT touched | Old user fallback | Still reads | Still reads | Still reads | - | - | - | Still reads | Still reads | Zero |
| m_facility | Hierarchy done | facilityID + location | Not used | Not used | Not used | facilityID field | Not used | Not used | Reads data | Not used | Zero |
| m_userfacilitymapping | NEW table | New users only | Not used | Not used | Not used | Not used | Not used | Not used | Not used | Not used | Additive only |
| m_uservanmapping | NOT touched | Old user fallback | Still reads | Still reads | - | - | - | - | - | - | Zero |
| t_beneficiary | NOT touched | Search by name/phone | Still reads | Still reads | Still reads | Still reads | Still reads | Still reads | Reads data | - | Zero |
| i_beneficiarymapping | NOT touched | vanID nullable | Still reads | Still reads | Still reads | Still reads | Still reads | Still reads | Reads data | - | Zero |
| FLW-specific tables | NOT touched | Not used | Not used | Not used | Fully intact | Not used | Not used | Not used | Not used | Not used | Zero |
...
12. ALL REPOS CONFIRMED FROM CODE
| Repo | Change | Status |
|---|---|---|
| HWC-API | YES | Login, worklist, visit save |
| HWC-UI | YES | Login flow, session, worklist |
| Admin-API | SMALL | User-facility mapping endpoint |
| Inventory-API | SMALL | Hierarchy walk for items |
| TM-API | ZERO | TC flow unaffected. Not touching TM code |
| FLW-API | ZERO | Separate tables. benRegID search unchanged |
| MMU-API | ZERO | Separate codebase, own vanID |
| Identity-API | ZERO | vanID nullable in beneficiary tables |
| BeneficiaryID-Gen-API | ZERO | MMU offline sync only. HWC never calls it |
| Common-API | ZERO | FacilityLoginRepo already exists |
| Helpline104-API | ZERO | IMRMMR reporting only. No worklist impact |
| Helpline1097-API | ZERO | Zero vanID/facilityID usage at all |
| ECD-API | ZERO | One nullable DTO field only |
| Scheduler-API | ZERO | Van scheduling for TM only |
| FHIR-API | ZERO | Data exchange only. Models already support facilityID |
...
13. PROD GO-LIVE STEP BY STEP
Step 1: Run pre-check query
SELECT VanID FROM m_van WHERE FacilityID IS NULL
AND Deleted = false AND ProviderServiceMapID = 5
→ Must return 0. Fix any Vans in Admin if needed.
Step 2: Run DB script
ALTER TABLE (add facilityID column)
Old code still running. Users unaffected.
Step 3: Run backfill
UPDATE from m_van JOIN
All HWC records get facilityID filled silently.
Step 4: Run post-backfill verification
SELECT COUNT(*) WHERE facilityID IS NULL (HWC records)
→ Must return 0.
Step 5: Deploy HWC-API and HWC-UI
New code uses facilityID.
Old records already have it. New records write it directly.
Step 6: Verify on prod
Old users login and check worklist.
New patient registration and worklist check.