HWC - Removing Van Chain & Implementing Facility Hierarchy

1. EXISTING SYSTEM - How It Works Today

Admin Setup (One-time, for each HWC)

To set up one HWC like "Gunjalli HWC", admin must go through 6 pages in sequence:

1. Zone Master - Admin creates "Raichur Zone" (saved in m_zone)
2. Parking Place - Admin creates "Raichur Parking Place", links to Zone (saved in m_parkingplace)
3. Sub-District Mapping - Admin maps Parking Place to District "Raichur" + Block "Manvi" (saved in m_parkingplacesubdistrictmap)
4. Store/Facility - Admin creates "Gunjalli Store" (saved in m_facility, FacilityID=4)
5. Van Master - Admin creates a fake "Gunjalli HWC Van" (no real vehicle exists), links to Parking Place and Store (saved in m_van, VanID=5)
6. Work Location Mapping - Admin maps user "Dr. Ravi" to Parking Place, then to Van (saved in m_userparkingplacemap + m_uservanmapping)

Problem: HWC is a fixed building, not a moving vehicle. But the system forces admin to create fake Zone, Parking Place, and Van just to make login and worklist work. This creates unnecessary complexity and 7 database records across 6 tables for one HWC.

User Login (Every time a user opens the app)

When Dr. Ravi opens HWC app:

API Call 1: /userAuthenticate - Dr. Ravi enters username + password. System returns userID=100 and providerServiceMapID=5.

API Call 2: /getUserVanSpDetails - System does 3 table joins to find Dr. Ravi's location:

• m_userparkingplacemap (UserID=100) → ParkingPlaceID=3
• m_uservanmapping (ParkingPlaceMapID) → VanID=5
• m_van (VanID=5) → FacilityID=4, ParkingPlaceID=3

Returns: vanID=5, facilityID=4, parkingPlaceID=3

API Call 3: /getLocDetailsBasedOnSpIDAndPsmID - System does 4 table joins to get location name:

• m_van → m_parkingplacesubdistrictmap → m_district → m_districtblock

Returns: state="Karnataka", district="Raichur", block="Manvi"

App stores vanID, parkingPlaceID, facilityID, district, block in session. Dr. Ravi sees the dashboard.

3 API calls, 7+ table reads just to log in.

Daily Patient Flow

1. Search Patient - Nurse searches "Lakshmi" by name/phone. System finds benRegID=50001. (No vanID used here)

2. Submit to Nurse - System writes a record in i_ben_flow_outreach table with vanID=5 and parkingPlaceID=3 (from session). This is how system knows "this patient is at this HWC."

3. Nurse Worklist - Query runs: WHERE vanID = 5 AND nurseFlag = 1. Shows "Lakshmi" waiting for nurse. There are 16 such queries in the codebase for nurse, doctor, lab, pharmacist, radiologist, TC specialist worklists.

4. Nurse completes → Doctor worklist → Doctor completes → Lab → Pharmacist - Same pattern. Every worklist query filters by vanID. Every visit record stores vanID and parkingPlaceID.

vanID is being used as a location identifier, not as a vehicle identifier.

2. NEW SYSTEM - How It Will Work After Facility Hierarchy

Admin Setup (One-time, for each HWC)

To set up "Gunjalli HWC", admin goes through 2 pages only:

1. Facility Hierarchy (ALREADY DONE) - Admin creates the hierarchy in one page:

   • Raichur DH (FacilityID=1)
     • Manvi CHC (FacilityID=2, Parent=1)
       • Kavital PHC (FacilityID=3, Parent=2)
         • Gunjalli SC (FacilityID=4, Parent=3)

   All stored in m_facility table which already exists. Each facility has StateID, DistrictID, BlockID directly - no separate mapping needed.

2. Work Location Mapping - Admin maps "Dr. Ravi" directly to "Gunjalli SC" (FacilityID=4). Saved in m_userfacilitymapping. No Zone, no Parking Place, no Van needed.

2 pages, 2 tables. Location info is already inside the facility record.

User Login (Every time a user opens the app)

When Dr. Ravi opens HWC app:

API Call 1: /userAuthenticate - Same as before, no change. Returns userID=100 and providerServiceMapID=5.

API Call 2: /getUserFacilityDetails (NEW) - System reads 2 tables:

• m_userfacilitymapping (UserID=100) → FacilityID=4
• m_facility (FacilityID=4) → FacilityName="Gunjalli SC", District="Raichur", Block="Manvi"

Returns everything in one call: facilityID, facilityName, state, district, block.

No 3rd API call needed. Location already comes from m_facility directly. No Van chain, no Parking Place chain.

App stores facilityID, district, block in session. Dr. Ravi sees the dashboard.

2 API calls, 2 table reads. Login done.

For old users like Dr. Ravi who were already mapped to a Van: The new API has a fallback. It reads m_uservanmapping → m_van → FacilityID. So Dr. Ravi logs in without any admin re-mapping.

Daily Patient Flow

1. Search Patient - Same as before, no change.

2. Submit to Nurse - System writes facilityID=4 in i_ben_flow_outreach instead of vanID. vanID and parkingPlaceID will be NULL for new records.

3. Nurse Worklist - Query runs: WHERE facilityID = 4 AND nurseFlag = 1. Same 16 queries, just vanID replaced with facilityID. Same result for the nurse - no visible difference.

4. Rest of the flow - Same as before. Doctor, Lab, Pharmacist all see their worklists filtered by facilityID instead of vanID. Patient data (vitals, diagnosis, prescription) is saved exactly the same way.

Nurse and Doctor experience does not change at all. Only the internal identifier changes from vanID to facilityID.

3. IMPACT ON OTHER SERVICE LINES

Each service line has its own API codebase, its own repository, and its own deployment. They share the same database but do not share code.

HWC-API: CHANGED. Login API, 16 worklist queries, visit save - all switch from vanID to facilityID. Changes only in HWC-API repo (~25 files).

HWC-UI: CHANGED. Login flow, session storage, worklist calls, visit save - all switch to facilityID. Changes only in HWC-UI repo (~25 files).

Inventory-API: SMALL CHANGE. Add hierarchy walk - when a facility has no medicine stock, walk up the parent chain (SC → PHC → CHC → DH) to find the nearest parent that has stock (~3 files).

Admin-API: SMALL CHANGE. Add user-facility mapping endpoint for new users (~3 files).

MMU-API: ZERO CHANGE. Separate codebase, separate repo. Still uses Van chain. Van tables are NOT deleted from database, so MMU continues to work exactly as before.

TM-API: ZERO CHANGE. Separate codebase. TM consultation in HWC uses benRegID + visitCode (not vanID), so it works fine.

104-API, 1097-API, ECD-API, FLW-API, Common-API: ZERO CHANGE. Separate codebases, no Van dependency for these.

Key point: We are NOT deleting any Van tables (m_van, m_parkingplace, m_zone, m_uservanmapping, m_userparkingplacemap). All these tables stay in the database. MMU and TM continue reading them. We are only stopping HWC from using them.

4. DATABASE CHANGES

What we ADD (before deploying new code)

Change 1: Add facilityID column to worklist table

ALTER TABLE db_iemr.i_ben_flow_outreach
ADD COLUMN facilityID INT DEFAULT NULL;

This is the table where every patient visit is tracked. Currently it has vanID for filtering. We add facilityID alongside it. vanID column stays.

Change 2: Backfill facilityID for all existing records

UPDATE db_iemr.i_ben_flow_outreach bf
JOIN db_iemr.m_van v ON bf.vanID = v.VanID
SET bf.facilityID = v.FacilityID
WHERE bf.facilityID IS NULL;

Every Van already has a FacilityID in m_van table. This query copies that FacilityID to all old patient records. After this, old records have both vanID=5 and facilityID=4. New worklist query WHERE facilityID=4 will find old records too.

Change 3: Add index for performance

CREATE INDEX idx_benflow_facilityid
ON db_iemr.i_ben_flow_outreach(facilityID);

16 worklist queries will filter by facilityID. Index ensures fast lookup.

Change 4: Create user-facility mapping table

CREATE TABLE db_iemr.m_userfacilitymapping (
  UserFacilityMapID INT AUTO_INCREMENT PRIMARY KEY,
  UserID INT NOT NULL,
  FacilityID INT NOT NULL,
  ProviderServiceMapID INT NOT NULL,
  Deleted BOOLEAN DEFAULT false,
  CreatedBy VARCHAR(50),
  CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP
);

For new users, admin maps them directly to a facility. Old users still work through the existing Van mapping tables (fallback).

What we DO NOT change

• m_van - NOT deleted, NOT modified (MMU/TM reads it)
• m_parkingplace - NOT deleted, NOT modified
• m_parkingplacesubdistrictmap - NOT deleted, NOT modified
• m_userparkingplacemap - NOT deleted, NOT modified (old user fallback)
• m_uservanmapping - NOT deleted, NOT modified (old user fallback)
• m_zone - NOT deleted, NOT modified
• m_itemfacilitymapping - NOT changed (already uses facilityID)
• t_itemstockentry - NOT changed (already uses facilityID)

Zero tables deleted. Zero columns deleted. Only 1 column added, 1 table created.

Pre-deployment verification

-- Must return 0 rows. If any Van has NULL FacilityID, fix it first.
SELECT VanID, VanName FROM db_iemr.m_van
WHERE FacilityID IS NULL AND Deleted = false;

Post-deployment verification

-- Must return 0. All old records should have facilityID filled.
SELECT COUNT(*) FROM db_iemr.i_ben_flow_outreach
WHERE facilityID IS NULL AND vanID IS NOT NULL;

Rollback

If anything goes wrong after deployment:

• Redeploy old HWC-API and HWC-UI code
• Old code uses WHERE vanID = :vanID which still works (vanID was never removed)
• The new facilityID column sits harmlessly as NULL, ignored by old code

          Fully reversible with zero data loss

TWO OPTIONS FOR INVENTORY MAP

Option A: Remove Van Chain from HWC (Full Change)

What: Stop using vanID in HWC completely. Login, worklist, visit save - everything uses facilityID directly.

Work involved:

• DB: Add facilityID column to i_ben_flow_outreach, backfill old records, new table m_userfacilitymapping
• HWC-API:  (new login API, 16 worklist queries, visit save, entity change)
• HWC-UI: (login flow, session, worklist calls, visit save)
• Inventory-API:  (hierarchy walk)

Benefit:

]

• No more fake Van, Zone, Parking Place for HWC
• Clean architecture - HWC uses facility directly
• Login: 2 API calls instead of 3
• Hierarchy built-in (DH→CHC→PHC→SC)

Risk:

• Old data needs backfill
• Old user login needs fallback logic

Option B: Keep Van, Use Existing FacilityID for Hierarchy (Minimal Change)

What: Van flow stays exactly as it is. Login still uses vanID, worklist still filters by vanID, visit save still stores vanID. BUT - every Van already has FacilityID in m_van table. And m_facility now has ParentFacilityID (hierarchy is already done). So hierarchy is already connected through the existing link:

Van (VanID=5) → m_van.FacilityID=4 → m_facility (FacilityID=4, ParentFacilityID=3)
                                        → Kavital PHC (3, Parent=2)
                                          → Manvi CHC (2, Parent=1)
                                            → Raichur DH (1)

The chain already exists. No need to change login, worklist, or visit save.

Work involved:

• 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: (hierarchy walk using Van → FacilityID → ParentFacilityID)

Benefit:

• Almost no code changes
• No risk to existing flow - everything stays as is
• No old data migration needed
• Hierarchy is available whenever needed through Van → FacilityID → m_facility
• Inventory hierarchy walk works (Van → FacilityID → walk up parents)
• Can be done very quickly

Limitation:

• 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

OLD DATA HANDLING - COMPLETE IMPACT ANALYSIS

1. HOW DATA IS STORED TODAY (Before any change)

i_ben_flow_outreach table - current state:

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:

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:

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:

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

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):

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:

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:

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

12. ALL REPOS CONFIRMED FROM CODE

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.