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 - IN DEPTH

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.

m_van table - the bridge:

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:

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:

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

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:

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:

m_van:

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

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