Summary

A read-only audit of the central STOP-TB database (snapshot taken 2026-11-06 08:45 UTC, 218 checks run on a local copy) found several data-quality problems. Some stopped with the 31 Oct sync fix (2026-10-31 12:42 IST) but the old rows were never repaired. Others are still happening. The full report is in STOP-TB-Central-Data-Quality-Audit-2026-11.docx (shared separately).

Still happening (fix in this release)

1. STOP-TB general exam / OPD linked to the wrong visit. tb_stoptb_general_examination and tb_stoptb_general_opd still arrive with the laptop's benVisitID instead of the central one (case mismatch in the van-link translation). 111,486 rows in total, 24,005 since the fix.
2. Duplicate synced rows. None of the 18 synced tables has a unique key on (VanID, VanSerialNo). Parallel upload batches insert 2-3 copies of the same row within a minute. 26,557 extra visits in t_benvisitdetail in total; about 3.5% of visits received since the fix are copies.
3. Diagnostic orders not producing results. 102,695 orders: 36,090 FAILED (hub could not reach the device on 192.168.1.x), 28,563 EXPIRED, 28,209 COMPLETED. 63,479 people had an X-ray ordered and only 21,672 have a completed result. 8,182 orders marked done have no result; 4,244 results have no order; 1,063 orders were sent to a devtunnels.ms URL.
4. Diagnostic documents missing from S3. 58,121 documents have no S3 copy.
5. Missing linking keys. tb_suspected and tb_stoptb_diagnostics have visitCode NULL on about 100% of rows; 3,568 vitals since 2 Nov have no VisitCode; 77,342 screenings have no ben_id.

Historic, needs a repair script

1. Clinical rows pointing at another patient's visit. 391,406 rows across the t_ child tables (vitals, exam, prescriptions etc.) received before the fix; 168 since. 44,014 of 44,100 pre-fix prescribed-drug rows point at the wrong prescription.
2. Identity. 75 beneficiary mappings cross-linked to another person's details; 1,196 mappings with no details row; 255 probable duplicate beneficiaries; placeholder phone 9999999999 used about 138k times.
3. Reporting DB behind. db_reporting is missing 60,757 visits and 173,541 beneficiaries.

Security

1. App DB user piramaldev has all privileges including GRANT OPTION from host %.

Suggested fixes (proposals, not yet run)

• Fix the case mismatch so tb_stoptb_general_* use the translated central visit ID.
• Add UNIQUE (VanID, VanSerialNo) on synced tables (after deduping) and make sync inserts idempotent (upsert).
• Carry visitCode / ben_id through on TB tables and vitals.
• Retry / surface FAILED diagnostic orders and missing S3 uploads; block non-prod endpoint URLs.
• Write a reviewed repair script for the historic mislinks and duplicates (backup first, run on UAT copy).
• Re-run the reporting ETL; restrict the app DB user's privileges.

Reproduce

Checks are reusable: run_audit.py + checks.py, SQL in sql/checks_catalogue.sql (in plans/stoptb-data-audit). Related: STOP-757.