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:

Zone Master - Admin creates "Raichur Zone" (saved in m_zone)
Parking Place - Admin creates "Raichur Parking Place", links to Zone (saved in m_parkingplace)
Sub-District Mapping - Admin maps Parking Place to District "Raichur" + Block "Manvi" (saved in m_parkingplacesubdistrictmap)
Store/Facility - Admin creates "Gunjalli Store" (saved in m_facility, FacilityID=4)
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)
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

Search Patient - Nurse searches "Lakshmi" by name/phone. System finds benRegID=50001. (No vanID used here)
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."
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.
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:

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.
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

Search Patient - Same as before, no change.
Submit to Nurse - System writes facilityID=4 in i_ben_flow_outreach instead of vanID. vanID and parkingPlaceID will be NULL for new records.
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.
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

	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