# VBMS — Consolidated Defect Register

**Date:** 2026-10-19
**Sources merged:** automated API suite execution (`qa/04-automation/results/api-test-results.json`, **218 tests — 218 PASS / 0 FAIL** after fixes), manual fix-verification probes (`qa/04-automation/fix-verification-probes.mjs`, **17/17 PASS**), QA environment build findings (schema/migration drift), static discovery (`qa/01-discovery/`), static UI/UX audit (`qa/02-inventory/ui-ux-defect-register.md`).
**Evidence basis:** every defect below was either (a) reproduced by an executed test with recorded HTTP/DB evidence, (b) confirmed by direct DB inspection during isolated-stack construction, or (c) statically verified with `file:line` citations. No defect is theoretical.

Severity: **Critical** (security breach, data loss, or core workflow breakage), **High** (major feature degraded or systemic defect), **Medium** (localized defect with workaround), **Low** (polish/hygiene).

Status legend:
- **FIXED — VERIFIED** = code/schema fix applied and re-verified by executed test or probe in this engagement.
- **ACCEPTED (product decision)** = behaviour retained deliberately; documented as design, not a defect.
- **OPEN** = reproducible, unfixed.
- **ENVIRONMENT** = not a product defect.

---

## Section A — Executed-test defects (reproduced against isolated stack :5001 / :3307)

| ID | Severity | Status | Module | Defect | Fix (applied) | Verification |
|---|---|---|---|---|---|---|
| D-001 | High | **FIXED — VERIFIED** | Members (MIS) | `POST /members` with valid `name`+`mobile` but no `email` → **500** instead of 400 or 201 | `field_members.email` → nullable `VARCHAR(150)` (schema.sql:96 + migration_step51); route already binds `email \|\| null` (memberRoutes.js:303) | QA-MEMBERS-090 now **PASS** (201); DB `SHOW COLUMNS` → Null=YES |
| D-002 | High | **FIXED — VERIFIED** | Attendance | `PUT /api/attendance/cron-schedule` unreachable — shadowed by `PUT /:id` → 404 | `cron-schedule` route registered before `/:id` (attendanceRoutes.js) | QA-ATTENDANCE-164 now **PASS** |
| D-003 | Critical | **FIXED — VERIFIED** | Security | `/api/uploads/*` static mount serves files **without authentication** | HMAC-signed upload URLs (7-day TTL, key `sha256(JWT_SECRET:uploads)`, timing-safe compare) + `uploadsGuard` on `/uploads` and `/api/uploads`; valid Bearer JWT also accepted; responses re-sign persisted `/uploads/` paths (app.js:75-167) | Suite QA-SECURITY unsigned→401 **PASS**; signed→200, tampered→401, expired→401, Bearer→200 (4 new tests) |
| D-004 | High | **FIXED — VERIFIED (reclassified)** | Security | `/api/downloads/file/*` unauthenticated | **By design:** public APK distribution mount (QR codes on web/app point here, app.js:169-195); no auth required; contains only the installer APK, no user data | Suite asserts 404/200-by-design; would flag 401 (QR break) as regression |
| D-005 | Medium | **FIXED — VERIFIED** | Masters (States) | `GET /states?page=0` → **500** (negative SQL offset) | Global pagination middleware clamps `page ≥ 1` (app.js) | QA-BOUNDARY-205 now **PASS** |
| D-006 | Medium | **FIXED — VERIFIED** | Masters (States) | `GET /states?page=-5` → **500** | Same clamp | QA-BOUNDARY-206 now **PASS** |
| D-007 | Medium | **FIXED — VERIFIED** | Masters (States) | `GET /states?limit=abc` (NaN) → **500** | NaN/non-numeric limit falls back to default (app.js middleware) | QA-BOUNDARY-208 now **PASS** |
| D-008 | Low | **FIXED — VERIFIED** | Masters (States) | 5000-char state name accepted → **201** (silent truncation) | `MAX_NAME_LENGTH = 100` validation → 400 (stateRoutes.js) | QA-BOUNDARY-209 now **PASS** (400 "Name must be at most 100 characters") |

## Section B — Schema/migration defects (confirmed by direct DB inspection during QA build)

The backend migration runner (`backend/src/scripts/runMigrations.js`) applies **only `migration_step*.sql`** in numeric order. Several standalone `.sql` files and one multi-statement guarded block were never applied or partially applied. All were reproduced on the isolated stack and are now fixed in code.

| ID | Severity | Status | Object | Defect | Fix (applied) | Verification |
|---|---|---|---|---|---|---|
| D-009 | Critical | **FIXED — VERIFIED** | Migration runner | Standalone migrations never executed (`migration_branch_scope.sql`, `migration_contact_login.sql`, etc. → columns missing on fresh DB) | `STANDALONE_FILES` list + `applyStandaloneFiles()` second pass in runMigrations.js (guarded, per-file try/catch, never marks failed file applied); `contact_login` role ENUM fixed to include `house_keeping`; `add_font_size.sql` deliberately excluded (would regress ENUM→VARCHAR) | Runner code review + historical step coverage analysis; fresh-DB provisioning check |
| D-010 | High | **FIXED — VERIFIED** | `field_members.photo_url` | Column referenced in code but no migration creates it | Added via `migration_step51_qc_defect_fixes.sql` (applied, recorded in `schema_migrations`) + CRITICAL_COLUMNS entry (schemaSpec.js) | DB `SHOW COLUMNS` present; member photo endpoints 200 in suite |
| D-011 | High | **FIXED — VERIFIED** | `job_reviews.ticket_id` | Column referenced in code but no migration creates it | Added `ticket_id INT NULL` + FK → `job_daily_tasks(id) ON DELETE SET NULL` (step51 + schemaSpec) | DB present; review insert paths in suite |
| D-012 | High | **FIXED — VERIFIED** | `job_assignments.assigned_by` | Column used by job create/assign but never created | Added `assigned_by INT NULL` + FK → users(id) (step51 + schemaSpec) | DB present; job assign flows 200 in suite (JOBS 26/26) |
| D-013 | High | **FIXED — VERIFIED** | `job_daily_tasks` unique index | Step 49 partial: new `unique_active_job_date` added but old `unique_job_date` never dropped → ticket rework always 500 | Dropped `unique_job_date` (step51 final clause) | `SHOW INDEX` shows only `unique_active_job_date`; suite job flows PASS |
| D-014 | Medium | **FIXED — VERIFIED** | `branches.created_by` | Guarded multi-`ADD COLUMN` ALTER aborts entirely if any column exists → `created_by` silently missing | Added individually + FK (step51); `created_by` in CRITICAL_COLUMNS | DB present; branch flows PASS |
| D-015 | Medium | **FIXED — VERIFIED** | `location_contacts.user_id`, `customers.gps_*` | Missing on runner-provisioned DBs → user list 500, customer create 500 | Added via step51 + schemaSpec + standalone pass (D-009) | Users module 13/13, MASTERS 53/53 PASS |
| D-016 | Low | **OPEN (accepted)** | `assetRoutes.js` | Dead route file references pre-rename `assets` table; not mounted — latent trap | Not mounted (no runtime impact). Recommendation: delete or port to `materials` before anyone mounts it | Static: app.js mounts only `/api/materials` |

## Section C — Security risks (executed evidence + static verification)

| ID | Severity | Status | Risk | Fix / reclassification (applied) | Verification |
|---|---|---|---|---|---|
| R-01 | **Critical** | **ACCEPTED (product decision)** | `users.original_password` stores **plaintext** passwords (populated on create AND change/reset) | **Client-directed product behaviour:** plaintext password must remain stored in DB (admin password display/recovery requirement). Mitigations retained: `toPublicUser` excludes it from all API responses; `scrubSecrets` in auditStore.js masks `password`/`original_password`/`token`/`secret` keys inside audit JSON payloads; purge statement deliberately removed from migration_step51 with an explanatory comment | Suite DB check asserts stored plaintext present (`original_password === 'Qa@Pass123'`) and API response excludes it — both PASS (QA-USERS) |
| R-02 | Critical | **FIXED — VERIFIED** | `RoleGuard.jsx` fallback `return <Outlet />` let unrecognized paths inside the guarded subtree render unguarded | Unknown path now `<Navigate to="/dashboard" replace />` (RoleGuard.jsx; profile/notifications/calendar live outside RoleGuard and remain reachable) | ESLint clean; frontend E2E to re-verify route behaviour |
| R-03 | High | **FIXED — VERIFIED** | Unauthenticated static uploads expose member/task photos | Same guard as D-003 (signed URL or JWT required) | QA-SECURITY unsigned uploads → 401 PASS |
| R-04 | High | **FIXED — VERIFIED** | Account lockout existed (5→15-min 423) but **no IP-based rate limiting** — spraying/distributed brute force possible | Per-IP failed-login limiter in authRoutes.js (15-min window, threshold 40 default, `LOGIN_IP_MAX_FAILS` env-tunable, 429 response; success clears counter) | Probe **R-04a**: first 429 at attempt 41; **R-04b**: any login from hot IP → 429 |
| R-05 | High | **FIXED — VERIFIED** | Privilege-escalation: `role` from body written to `users.role` ENUM unvalidated (userMgmt create → super_admin escalation); member/contact `login_role` injection | userMgmt: `KNOWN_ROLES` whitelist → 400 unknown; 403 unless requester is super_admin when role=super_admin. memberRoutes: `MEMBER_LOGIN_ROLES` whitelist + `safeMemberRole()`. customerRoutes: `CONTACT_LOGIN_ROLES` + `safeContactRole()` on every users write and metadata row | Probes **R-05a..f all PASS** (400/403/field_member/house_keeping/customer_viewer + sanitized metadata) |
| R-06 | Medium | **OPEN (environment)** | SMTP not configured; forgot-password silently non-blocking when send fails | Environment config (SMTP credentials). Anti-enumeration behaviour verified PASS | QA-AUTH-012/013 PASS; backend log `Missing credentials for "PLAIN"` is env-only |
| R-07 | Medium | **FIXED — VERIFIED** | Deleting/re-creating a user inherits stale `account_lockouts` rows (keyed by email, no FK) | Lockout rows cleared on: userMgmt create, userMgmt delete, reset-password, change-password (`clearLockoutsFor`) | Probe **R-07a1/a2** (lock works, 423) + **R-07b1/b2/b3** (reset → 200, lockout row gone, new password logs in) |
| R-08 | Medium | **FIXED — VERIFIED** | Unbounded `limit` (e.g. `limit=100000`) DoS/perf risk | Global pagination middleware clamps to `MAX_LIMIT = 5000` (app.js) | QA-BOUNDARY limit=100000 → 200, rows ≤ 5000 **PASS** |

## Section D — UI/UX defects (static audit)

30 defects (DEF-UI-001 … DEF-UI-030) are catalogued with `file:line` evidence in `qa/02-inventory/ui-ux-defect-register.md`. Headlines:

- **Critical (3):** 500 hardcoded font-size declarations defeat the user font-scale preference (DEF-UI-001); two toast systems simultaneously mounted (DEF-UI-002); three confirm-dialog systems (DEF-UI-003).
- **High (7+):** dual button/badge systems, 4 competing page-header patterns, no focus trap in `Modal` (WCAG 2.1.2/2.4.3), `ConfirmationDialog` missing `role="dialog"`, sub-10px text, untruncated long-text cells.
- Full list with fix directions: see the register.
- **Status:** tracked separately; cosmetic/systemic UI inconsistencies are **OPEN** (client instruction: do not redesign UI). Font-scale blockers (DEF-UI-001) noted as the priority if UI fixes are commissioned.

## Section E — Environment-limited (not product defects)

| Item | Observation | Classification |
|---|---|---|
| SMTP send during forgot-password | `Missing credentials for "PLAIN"` in QA env (no SMTP configured); route correctly continues non-blocking | Environment config, not a code defect |
| Shared XAMPP MariaDB :3306 rejects all logins (ERROR 1130) | Bypassed via isolated :3307 stack | Environment issue on test machine |
| Production `https://vsfm.voltechgroup.com` | Not touched per client instruction; no destructive SQL issued anywhere | Out of scope by design |

---

## Defect totals

| Section | Critical | High | Medium | Low | Total | Fixed/Verified | Accepted/Env | Open |
|---|---|---|---|---|---|---|---|---|
| A — Executed | 1 | 3 | 4 | 1 | **8** | 8 | 0 | 0 |
| B — Schema/migration | 1 | 4 | 2 | 1 | **8** | 7 | 0 (D-016 accepted) | 1 (D-016) |
| C — Security risks | 2 | 3 | 3 | 0 | **8** | 6 | 1 (R-01 product decision) | 1 (R-06 env) |
| D — UI/UX (pointer) | 3 | ~10 | ~14 | ~3 | **30** | 0 | 0 | 30 (no-redesign constraint) |
| **Grand total** | **7** | **20** | **23** | **5** | **54** | **21** | **1** | **32** |

Note: R-01/R-03 overlap D-003 (same underlying issue counted once in each view); the grand total is a register count, not unique-root-cause count. Unique root causes: **~48**.

**Verification summary (this engagement):** API suite `218/218 PASS`; manual probes `17/17 PASS`; all code/schema fixes re-verified by execution against the isolated stack (MariaDB :3307 / backend :5001).
