# Biometrics Data-Loss and Four-Slot Fix — Staged Phase Report Date: 2026-07-28 Status: staged implementation only; not migrated, deployed, replayed, or run against live attendance ## Root causes The CSV processing endpoint truncated `tbl_biometrics_logs` on every request. Because the frontend sends 500-row chunks, every chunk also removed all earlier chunks. This erased HIK mirror rows and could leave an empty or partial raw table after failure. The records UI discarded `attendance_status`, deduplicated punches by minute, and assigned the first four timestamps to four attendance columns by ordinal position. Missing slots therefore shifted later punches into the wrong column. ## Backups The pre-change backup is outside Apache's document root at: `C:\xampp\solidmark-backups\biometrics-phase-20260728-132705` It contains copies of the six requested files and a consistent dump of: - `tbl_biometrics_logs` - `hik_sync_events` - `hik_sync_employee_map` - `attendance` The dump was created through a read-only, single-transaction export. No credentials are stored in the phase report. ## Files changed - `frontend/src/components/biometrics/biometrics.jsx` - `frontend/src/components/biometrics/BiometricsRecords.jsx` - `frontend/src/components/biometrics/biometricsGrouping.js` - `backend/attendance_biometrics/import.php` - `backend/attendance_biometrics/fetch_records.php` - `backend/attendance_biometrics/import_attendance_by_personid.php` - `backend/helpers/biometrics_status_helper.php` - `backend/hik-sync/lib.php` - `backend/hik-sync/recover_missing_raw.php` Fixture tests and migration files were added under the corresponding `tests` and `migrations` directories. ## Destructive behavior removed The active CSV endpoint contains no `TRUNCATE`, raw-log `DELETE`, or runtime `ALTER TABLE`. Each chunk uses a transaction and appends successful new rows. A failed chunk rolls back that chunk and cannot erase an earlier chunk or an existing HIK row. ## Canonical status mappings The shared backend normalizer returns one of: - `morning_in` - `morning_out` - `afternoon_in` - `afternoon_out` - `unclassified` Mappings: - Morning In: `Check-in`, `checkIn`, `check-in`, `in`, `morningIn`, `morning-in`, `Morning In` - Morning Out: `Break-Out`, `breakOut`, `break-out`, `morningOut`, `morning-out`, `Morning Out` - Afternoon In: `Break-In`, `breakIn`, `break-in`, `afternoonIn`, `afternoon-in`, `Afternoon In` - Afternoon Out: `Check-out`, `checkOut`, `check-out`, `out`, `afternoonOut`, `afternoon-out`, `Afternoon Out` A recognized status wins. If status is not recognized, a recognized explicit event label is used. Unknown values remain `unclassified`; time is never used as a fallback. ## Import batches and identity The frontend creates one UUID when a file is selected and sends it as `import_batch_id` with every 500-row request. CSV rows use source type `ivms_csv`. Their deterministic identity hashes: 1. source type; 2. normalized checkpoint/device source; 3. normalized person ID; 4. normalized event timestamp; 5. canonical slot, or normalized original status if unclassified. The batch ID is not part of identity, so importing the same file again remains idempotent. Before migration, the endpoint uses a compatible exact source/person/time/ status lookup. After migration, it uses the unique source event key while also checking pre-migration CSV rows. ## Grouping rules Records are grouped by person and calendar date, then classified by canonical slot: - earliest Morning In; - latest Morning Out; - earliest Afternoon In; - latest Afternoon Out. Missing slots stay empty. Same-minute punches with different statuses remain separate. Extra punches and unclassified punches remain attached to the group for review. Source, original status, canonical slot, unclassified counts, and extra counts are available in the UI/export. Apply Attendance is blocked while unclassified punches exist. The three-punch fixture now produces: `09:00 / NULL / 13:00 / 18:00` ## Apply Attendance The endpoint: - validates every selected time against its raw record ID and canonical status; - rejects groups containing unclassified raw punches; - requires an exact active `employee_id` match; - uses one transaction per chunk; - never changes raw biometrics; - uses the same schedule and late/undertime calculation helpers as normal attendance creation; - uses employee/date upsert for repeat application; - reports `logs_cleared: false`. Default upsert retains existing manual slot values through `COALESCE`. Credit calculation also includes those retained values, avoiding a mismatch between preserved times and recalculated credit. Corrective mode is prepared but is not used by the frontend. It requires both top-level `corrective_mode: true` and `confirm_corrective_mode: true` on an admin-authenticated request. In that mode, missing incoming slots can replace an already-wrong value with NULL. Production correction remains a separate approval gate. ## Migration prepared Prepared, not executed: `backend/attendance_biometrics/migrations/20260728_01_biometrics_source_identity.sql` It adds nullable: - `source_type` - `import_batch_id` - `source_event_key` It also prepares person/time and source/batch indexes and: `UNIQUE(source_type, source_event_key)` Existing blank/NULL `source_file` rows remain unclassified. They are not silently labeled as CSV or HIK. A rollback SQL file is included beside the migration. ## HIK compatibility HMAC v2, shared-secret handling, allowed-device validation, event deduplication, active employee mapping, and mirror-state behavior are unchanged. HIK continues using `source_file = hikvision_api`. After migration, new HIK raw rows also use `source_type = hikvision_api` and the existing HIK `event_key` as `source_event_key`. Original HIK status and event label remain preserved in `hik_sync_events`. ## Targeted recovery Prepared, never executed: `backend/hik-sync/recover_missing_raw.php` Dry-run is the default. Writes require both: - `--apply` - `--confirm-recovery` Supported filters: - `--device-key` - `--hik-employee-no` - `--date-from` - `--date-to` - `--serial` - `--limit` The script only examines marked-mirrored events, requires an active mapping, checks both event identity and exact legacy raw identity, detects conflicts, uses a transaction, and never updates `hik_sync_events.mirrored_at`. ## Validation results - PHP syntax: all modified/new PHP files passed. - Backend fixtures/static safeguards: 46 assertions passed. - Frontend grouping fixtures: 11 scenarios passed. - Frontend production build: passed; 5,063 modules transformed and PWA files generated in an isolated temporary output directory. - Targeted ESLint parsed all changed frontend files. It still reports pre-existing unused React/state and prop-validation findings in the two legacy JSX components; the new grouping module and fixture are clean. - Active import-path scan found no raw-table truncate or global delete. These are fixture/static checks only. Migration, endpoint integration, concurrency, authorization scope, and live recovery still require a staging database. ## Deployment order 1. Review this phase report and code diff. 2. Refresh file and database backups immediately before staging. 3. Deploy the shared helper and compatible application code to staging. 4. Execute the migration in staging only. 5. Run CSV/HIK coexistence, duplicate, 501+ row, failure, attendance upsert, authorization, and concurrency integration tests. 6. Run recovery in staging dry-run mode with narrow filters. 7. Compare dry-run counts to independent read-only queries. 8. Approve and test narrowly filtered recovery writes in staging. 9. Schedule production maintenance and take fresh backups. 10. Execute the production migration, deploy code, smoke-test, and monitor. 11. Run production recovery only under separate explicit approval. ## Rollback 1. Stop new CSV processing through operational controls. 2. Restore the six backed-up application files or deploy the prior release. 3. If the migration ran, first roll back application code, then run the staged rollback SQL only after a fresh backup. 4. Restore `tbl_biometrics_logs` or affected attendance rows from the timestamped database dump only when approved and scoped. 5. Do not globally reset `mirrored_at`. 6. Reconcile HIK events and raw rows with read-only queries before reopening processing. ## Remaining risks and approval gates - No migration has been executed. - No live CSV or Apply Attendance request has been sent. - No recovery or HIK replay has run. - No service has restarted and nothing has been deployed. - Existing wrong attendance rows need a separately approved corrective plan. - Existing blank-source rows require human/source evidence before backfill. - Company/brand authorization behavior must receive staging integration verification with real authenticated tenant roles. - Concurrent import behavior and migration duration need staging measurement. - The 126 previously marked-mirrored/missing raw events require a reviewed recovery dry run before any restoration approval.