# VBMS — UAT Pack & Release Readiness Recommendation

**Date:** 2026-10-19 (post-fix revision)
**Prepared from:** discovery inventories, **218 executed automated API tests (218/218 PASS)**, 17/17 manual fix-verification probes, static UI/UX audit, schema/migration audit.
**Client instructions honored:** production environment untouched (no destructive SQL; `https://vsfm.voltechgroup.com` never modified); UI redesign not performed; `users.original_password` plaintext storage retained per explicit client direction (R-01 reclassified as product behaviour). Code fixes for genuine defects were approved and applied this engagement.

---

## 1. Executive summary

The VBMS/VSFM web stack (Express 5 + MySQL backend, React 19 + Vite frontend) was assessed end-to-end, and **all genuine defects found were fixed and re-verified** against an isolated, production-like stack (backend :5001, MariaDB :3307, schema provisioned by the project's own migration runner).

- **Automated API suite: 218/218 PASS** (0 failures) — including 4 new signed-URL security regression tests.
- **Manual fix-verification probes: 17/17 PASS** (R-04 IP limiter, R-05 role whitelists, R-07 lockout lifecycle).
- **8 executed-test defects (D-001..D-008), 7 schema/migration defects (D-009..D-015), and 6 security risks (R-02/03/04/05/07/08) are FIXED and verified by execution.**
- **R-01 (plaintext `original_password`)** is retained as directed — mitigations: excluded from API responses (`toPublicUser`), scrubbed from audit JSON (`scrubSecrets`), documented in migration step51 as intentional.
- Remaining open: 30 cosmetic/systemic UI/UX defects (no-redesign constraint), ~48 manual/UAT cases, R-06 (SMTP environment), D-016 (dead file, accepted).

Core business workflow is **functionally sound at the API layer**: login/lockout/reset, master CRUD, member provisioning, job lifecycle (create → assign → auto daily tickets → geofenced check-in/out → submit → VBMS approve → CUB queue → redo/replacement lineage), attendance/leaves/capacity, and the full RBAC matrix behave as designed on a correctly provisioned database.

## 2. Release recommendation

### 🟡 CONDITIONAL GO — API/security layer green; frontend E2E + manual UAT backlog remain.

**Fixed & verified (were blocking):**

| ID | Item | Verification |
|---|---|---|
| D-003/R-03 | Unauthenticated `/api/uploads` — now signed URLs + JWT guard | suite: unsigned 401 / signed 200 / tampered 401 / expired 401 / Bearer 200 |
| D-004 | `/api/downloads` — reclassified **by design** (public APK distribution for QR codes; no user data) | suite asserts 404/200-by-design |
| D-009 | Migration runner skips standalone migrations — `STANDALONE_FILES` + `applyStandaloneFiles()` pass | runner code + historical coverage analysis |
| D-013 | Step-49 partial apply — old unique index dropped (step51) | job/rework flows PASS in suite |
| R-02 | RoleGuard fallback bypass — unknown paths → `/dashboard` | ESLint clean; E2E re-verification pending |
| R-04 | No IP rate limiting — per-IP limiter added (429 after 40 fails/15 min) | probe: first 429 at attempt 41 |
| R-05 | Role escalation via `role`/`login_role` — 3 whitelists + super_admin assignment gate | probes R-05a..f all PASS |
| R-07 | Orphaned/inherited lockout rows — cleared on create/delete/reset/change | probes R-07a/b PASS |

**Remaining before full release sign-off:**

| Priority | Item |
|---|---|
| 1 | Frontend E2E run (puppeteer + Edge): all routes, 4 viewports, R-02 route behaviour, screenshots |
| 2 | ~48 manual/UAT cases (Section 5) — holidays/ticket generation is the highest business risk |
| 3 | Schedule UI/UX backlog (DEF-UI-001..030) — font-scale (DEF-UI-001) first for accessibility |
| 4 | R-06: configure SMTP in staging (env only) |
| 5 | D-016: delete or port dead `assetRoutes.js` before anyone mounts it |

### Conditional-go path
Apply `migration_step51_qc_defect_fixes.sql` + runner update to a staging clone, re-run `node qa/04-automation/api-test-suite.mjs` expecting **218/218**, run `node qa/04-automation/fix-verification-probes.mjs` expecting **17/17**, complete Section 5 UAT, then release.

## 3. Schema fixes applied to the QA stack (reproduce on staging/prod)

All applied idempotently via **`backend/src/scripts/migration_step51_qc_defect_fixes.sql`** (recorded in `schema_migrations`); the standalone-migration gap is fixed upstream by `runMigrations.js` (`applyStandaloneFiles`).

```sql
-- D-015 / D-009 fallout
ALTER TABLE location_contacts ADD COLUMN user_id INT DEFAULT NULL AFTER password;
ALTER TABLE customers ADD COLUMN gps_enabled TINYINT(1) DEFAULT 0 AFTER holiday_calendar_id;
ALTER TABLE customers ADD COLUMN gps_update_interval INT DEFAULT 15 AFTER gps_enabled;
ALTER TABLE customers ADD COLUMN gps_geofence_buffer INT DEFAULT 100 AFTER gps_update_interval;
ALTER TABLE customers ADD COLUMN gps_idle_threshold INT DEFAULT 5 AFTER gps_geofence_buffer;

-- D-001 (email relaxed) + D-010
ALTER TABLE field_members MODIFY COLUMN email VARCHAR(150) NULL;
ALTER TABLE field_members ADD COLUMN photo_url VARCHAR(500) NULL;

-- D-011
ALTER TABLE job_reviews ADD COLUMN ticket_id INT NULL;
ALTER TABLE job_reviews ADD FOREIGN KEY (ticket_id) REFERENCES job_daily_tasks(id) ON DELETE SET NULL;

-- D-012
ALTER TABLE job_assignments ADD COLUMN assigned_by INT NULL AFTER is_automatic;
ALTER TABLE job_assignments ADD FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE SET NULL;

-- D-014
ALTER TABLE branches ADD COLUMN created_by INT NULL AFTER updated_at;
ALTER TABLE branches ADD FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL;

-- D-013 (repair partial step 49: drop the obsolete flat unique key)
ALTER TABLE job_daily_tasks DROP INDEX unique_job_date;
-- (unique_active_job_date(job_id, task_date, active_day) must exist — step49 adds it)
```

## 4. What was verified green (reusable confidence)

- **Auth:** login valid/invalid/missing/type-confused/overlong, `/me` token variants incl. forged-expired (signed with the QA JWT secret), check-username, forgot-password anti-enumeration (identical generic 200 for unknown vs existing), reset-password token validation, change-password (same/wrong/weak/valid + re-login), account lockout (5 fails → 423; correct password during lock → 423; admin unaffected). **New:** reset/change password clear stale lockouts (R-07); per-IP limiter returns 429 (R-04).
- **RBAC:** all 10 roles probed; 18/18 positive+negative cases match the permission matrix. **New:** role whitelists verified (unknown role → 400; non-super-admin minting super_admin → 403; member/contact `login_role` injection defused → field_member/customer_viewer).
- **Masters:** full CRUD + duplicates + soft-delete + 404s across States, Locations, Customers (incl. branches, branch-locations, contacts with login provisioning + scope inserts), Materials (single + batch), Material Types, Service Types, Companies.
- **Jobs lifecycle:** create (auto daily tickets), assign, generate, manual ticket, geofenced check-in (reject-before-write at ~200 km, accept within fence, gps-disabled customer bypass), checkout, submit, VBMS approve, rework → redo lineage (`ticket_redo_history`, original frozen as `redo`, replacement generated), redo-history endpoints.
- **Attendance/Leaves/Capacity:** checkin/checkout/duplicate/double/missing, leaves create/range-validation/approve + DB status, capacity POST validation + GET. **New:** `PUT /cron-schedule` reachable (D-002).
- **Security probes:** SQLi (parameterized), stored XSS round-trips raw (React escapes on render), 200KB body → 413, malformed JSON → 400, unknown route → 404, uploads guarded (401 unsigned), signed-URL tamper/expiry rejected (401).
- **Boundary:** `page=0/-5`, `limit=abc/100000` (clamped ≤ 5000), oversize names → 400, string-safe IDs → 404, empty-body creates → 400 — all green.

## 5. Pending manual/UAT backlog (~48 cases, see coverage-matrix.md §1)

Priority order for UAT execution:
1. **Holidays & ticket generation** (calendar CRUD, rules, overrides, holiday-skip in generation) — highest business risk not yet executed.
2. **CUB Review + Feedback** (final sign-off, neutral comment, feedback, public tokenized survey double-submit).
3. **Scopes adversarial tests** (R-05 remainder: scoped user must not see out-of-scope rows; customer_admin grant attempts).
4. **MIS documents/member-locations/skills/photo flows** (photo column now present).
5. **Notifications, mail formats, email integration** (R-06 env-limited — configure SMTP in staging first).
6. **My Assignments as field_member** (browser, ticket edit, submit, redo).
7. **UI/UX regression pass** against DEF-UI-001..030 (font-scale actually scaling, single toast/confirm/button language after refactor).

## 6. How to re-run the automated suite + probes

```powershell
# QA stack (if down):
Start-Process mysqld.exe -ArgumentList '--defaults-file=C:\Users\ssraj\AppData\Local\Temp\opencode\vbms-qa\my.ini'
# Backend on :5001 with env DB_HOST=127.0.0.1 DB_PORT=3307 DB_USER=root DB_PASSWORD=vbms_qa_local
#   DB_NAME=fieldservice PORT=5001 JWT_SECRET=qa_test_secret_not_for_prod AUTO_MIGRATE=false

node qa/04-automation/api-test-suite.mjs
# → expected: 218/218 PASS  (results: qa/04-automation/results/api-test-results.json + api-test-report.md)

node qa/04-automation/fix-verification-probes.mjs
# → expected: 17/17 PASS  (NOTE: restart the QA backend afterwards — R-04 leaves the per-IP limiter hot)
```

## 7. Deliverable index

| Artifact | Path |
|---|---|
| Discovery — frontend pages | qa/01-discovery/frontend-page-inventory.md |
| Discovery — backend endpoints | qa/01-discovery/backend-endpoint-inventory.md |
| Discovery — DB schema | qa/01-discovery/database-schema-inventory.md |
| Discovery — role/permission matrix | qa/01-discovery/role-permission-matrix.md |
| Field dictionary & glossary | qa/01-discovery/field_dictionary_and_glossary.md |
| Module & screen inventory (54 modules) | qa/02-inventory/module-inventory.md |
| UI/UX defect register (30) | qa/02-inventory/ui-ux-defect-register.md |
| CRUD/workflow test registers | qa/03-testcases/module-crud-test-registers.md |
| Automated suite + results | qa/04-automation/api-test-suite.mjs, results/ |
| Fix-verification probes | qa/04-automation/fix-verification-probes.mjs |
| Consolidated defect register (54 findings) | qa/05-report/defect-register.md |
| Coverage matrix | qa/05-report/coverage-matrix.md |
| This UAT pack / release recommendation | qa/05-report/uat-and-release-readiness.md |
