Page History

...

...

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

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;

...

ADD COLUMN facilityID INT DEFAULT NULL;

i_ben_flow_outreach after

...

ALTER TABLE:

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

...

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:

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

...

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.