# VBMS Database Schema Inventory

---
date: 2026-10-18
Evidence basis: static DDL analysis (schema.sql + migration_step1..N)
Scope: `backend/src/scripts/schema.sql` + all `migration_step*.sql` files, applied in the numeric order used by `runMigrations.js` (step number asc, then filename localeCompare). Non-step SQL files (migration_overall.sql, master_setup_migration.sql, etc.) are noted but are NOT applied by the migration runner (`runMigrations.js:217-227` filters `^migration_step\d+.*\.sql$`); their content is either duplicated inside step files or is manual/legacy.
---

## Section 1: Table Index

Effective schema = 61 domain tables + 1 bookkeeping table (`schema_migrations`) = **62 tables**.
(Note: the task brief said base schema has 26 tables; `schema.sql` actually contains **24** CREATE TABLE statements — `grep -c "CREATE TABLE" schema.sql` = 24. The remaining tables come from migration steps.)

| Table | Purpose | Introduced by | Row-estimate / seed source |
|---|---|---|---|
| users | Login accounts, roles, profile | schema.sql:8 | seed: runMigrations.js `seedCoreRows` (default admin) |
| states | Indian states master | schema.sql:21 | — |
| locations | Cities/locations master (per state) | schema.sql:29 | — |
| customers | Banks/customers master | schema.sql:41 | — |
| branches | Customer bank branches | schema.sql:50 | — |
| service_types | Service categories + default duration | schema.sql:64 | — |
| materials | Inventory/materials per branch (renamed from `assets` on legacy DBs) | schema.sql:73; rename: migration_step4 | — |
| field_members | Field staff master (MIS profile) | schema.sql:91 | — |
| member_documents | Member KYC document files | schema.sql:111 | — |
| member_locations | Member eligible locations (bootstrap FK version) | schema.sql:127; re-created no-FK: migration_step38 | — |
| member_skills | Member ↔ service-type skills | schema.sql:136 | — |
| coordinator_locations | Coordinator geographic scope | schema.sql:145 | — |
| attendance | Daily attendance register (unique member+date) | schema.sql:156 | — |
| leave_requests | Member leave applications + approval | schema.sql:172 | — |
| jobs | Job/ticket header (18-state workflow) | schema.sql:186 | — |
| job_assignments | Job ↔ member assignment history | schema.sql:218 | — |
| job_tracking | GPS travel/work event trail per job | schema.sql:231; re-created no-FK: migration_step30 | — |
| job_photos | Before/during/after job photos | schema.sql:244 | — |
| job_checklists | Per-job checklist items | schema.sql:258 | — |
| job_reviews | VBMS/CUB review decisions + sign-off | schema.sql:268 | — |
| job_feedback | Review feedback/correction items | schema.sql:281 | — |
| job_reassignments | Old→new member reassignment log | schema.sql:294 | — |
| notifications | Per-user in-app notifications | schema.sql:307 | — |
| audit_logs | Entity change audit (old/new JSON) | schema.sql:319 | — |
| companies | Internal/external company master (VOLTECH) | migration_step1_schema.sql:11 | seed: step1:302-304 (VOLTECH) |
| customer_contacts | Customer-level contacts with optional login | migration_step1:27 (dup at step2_master:79) | — |
| holiday_calendars | Per-year holiday calendars (voltech/customer/location/state) | migration_step1:48 | seed: runMigrations.js `seedCoreRows` (VOLTECH calendar) |
| holidays | Individual holiday dates in a calendar | migration_step1:65 | seed: runMigrations.js:332-348 (9 national holidays) |
| working_day_overrides | One-off working/non-working day exceptions (scoped) | migration_step1:82; scope: step45 | — |
| employee_capacity | Per-member daily capacity (hours) | migration_step1:100 | — |
| employee_scopes | Polymorphic role scope grants (customer/state/location) | migration_step1:121 | — |
| job_checkins | Detailed GPS check-in records per job | migration_step1:134 | — |
| job_checkouts | Detailed check-out records + actual hours | migration_step1:151 | — |
| employee_daily_work | Per-member per-day work calendar roll-up | migration_step1:167 | — |
| salary_inputs | Monthly payroll aggregates per member | migration_step1:194 | — |
| mis_records | Monthly MIS reporting aggregates per member | migration_step1:223 | seed: create_dummy_mis.js (dev only) |
| permissions | Fine-grained RBAC permission catalog | migration_step1:244 | seed: step1:309-379; + step24, step25 |
| role_permissions | role (string) ↔ permission grants | migration_step1:252 | seed: step1:386-427; step23, step27, step28, step43, step48b, step50b |
| customer_addresses | Multiple addresses per customer (one primary) | migration_step2_master_setup.sql:55 | — |
| branch_locations | Branch ↔ location link with address/GPS | migration_step3:38 | — |
| location_contacts | Contacts at a branch_location (migrated from customer_contacts) | migration_step3:63 | data-migration: step3:96-122 |
| app_settings | Key/value application settings | migration_step5:1 | seed: step5, step6, step29, step30, step31, step37 |
| login_attempts | Failed/successful login telemetry | migration_step6:24 | — |
| account_lockouts | Per-email lockout window | migration_step6:34 | — |
| user_sessions | Active JWT/session registry per user | migration_step7:2 | — |
| user_devices | Known device registry per user | migration_step7:22 | — |
| holiday_rules | Recurring holiday rules (nth_weekday etc.) per calendar | migration_step8:2 | — |
| member_police_docs | Multiple police-verification files per member | migration_step11:4 | — |
| job_daily_tasks | Per-job per-day ticket (check-in/out, photos, status machine) | migration_step15:4 | — |
| daily_attendance | Derived per-member per-day attendance/capacity roll-up | migration_step16:4 | — |
| attendance_config | Attendance refresh schedule config | migration_step16:28 | seed: step16:35-38 |
| ticket_redo_history | Redo/replacement ticket lineage log | migration_step18:10 | — |
| mail_formats | Email template catalog (subject + HTML body) | migration_step20:3 | seed: step21 (Reassign template) |
| feedback_templates | Customer-satisfaction survey templates (JSON questions) | migration_step24:6 | seed: step24:100-176 (4 surveys) |
| feedback_requests | Sent feedback survey instance (tokenized link) | migration_step24:22 | — |
| feedback_recipients | Multiple recipients per feedback request (per-recipient token) | migration_step24:49 | — |
| feedback_responses | Submitted survey answers (JSON + score + signature) | migration_step24:62 | — |
| job_location_pings | Periodic member GPS pings while checked in | migration_step30:15 | — |
| roles | Named role catalog + child_permissions JSON for UI gating | migration_step33:8 | seed: step27:14-46, step28:20-23 |
| user_preferences | Per-user UI personalization | migration_step33:19 | — |
| password_reset_tokens | Single-use password reset tokens | migration_step34:8 | — |
| material_types | Material category lookup | migration_step34:19 | seed: step34:26-29 |
| schema_migrations | Migration bookkeeping (filename → applied_at) | runMigrations.js:154-162 (not SQL file) | — |

## Section 2: Per-Table Column Details

Legend: PK = primary key; UK = part of unique key; AI = auto increment. "—" = no default beyond MySQL implicit. All TIMESTAMP `created_at` columns default `CURRENT_TIMESTAMP`; `updated_at` where present = `CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP` (audit timestamps).

### users  (login accounts; schema.sql:8 + step2_admin:9 + step7:37-40 + step35 + step36)
| Column | Type | Null | Default | Constraints / notes |
|---|---|---|---|---|
| id | INT | NO | AI | PK |
| name | VARCHAR(100) | NO | — | |
| username | VARCHAR(50) | YES | — | UNIQUE |
| email | VARCHAR(150) | **YES** (step36 relaxed NOT NULL) | NULL | UNIQUE (multiple NULLs allowed) |
| password | VARCHAR(255) | NO | — | bcrypt hash |
| original_password | VARCHAR(255) | YES | NULL | **plaintext password for display/reset flows** (step35:6-9) |
| role | ENUM('super_admin','admin','coordinator','field_member','vbms_reviewer','cub_reviewer','customer_admin','customer_viewer','customer_operator','house_keeping') | NO | 'coordinator' | final form per step28:14-15 (10 values) |
| status | ENUM('active','inactive') | NO | 'active' | |
| phone | VARCHAR(20) | YES | NULL | step7 |
| avatar_url | VARCHAR(500) | YES | NULL | step7 |
| photo_url | VARCHAR(500) | YES | NULL | step35 |
| last_login_at | TIMESTAMP | YES | NULL | step7/step35 |
| created_at / updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | audit |

### states  (schema.sql:21 + step2_master:11-13,105)
id PK AI; name VARCHAR(100) NOT NULL; status ENUM('active','inactive') DEFAULT 'active'; updated_at TIMESTAMP; created_by INT FK→users(id) ON DELETE SET NULL; created_at TIMESTAMP.

### locations  (schema.sql:29 + step2_master:18-20,106)
id PK AI; state_id INT NOT NULL FK→states(id); name VARCHAR(100) NOT NULL; latitude/longitude DECIMAL(10,7) NULL; status ENUM('active','inactive') DEFAULT 'active'; updated_at; created_by INT FK→users(id) SET NULL; created_at.

### customers  (schema.sql:41 + step1:270-272 + step2_master:40-49,112 + step13)
id PK AI; company_id INT FK→companies(id) (step1); code VARCHAR(50) UNIQUE; company_name VARCHAR(200); industry VARCHAR(150); email VARCHAR(150); website VARCHAR(255); customer_currency VARCHAR(10) DEFAULT 'INR'; gst_no VARCHAR(30); pan_no VARCHAR(10); holiday_calendar_id INT NULL FK→holiday_calendars(id) SET NULL (step13); status ENUM('active','inactive') DEFAULT 'active'; updated_at; created_by INT FK→users(id) SET NULL; created_at. Name column: name VARCHAR(150) NOT NULL (schema.sql:43).

### branches  (schema.sql:50 + step3:22-32)
id PK AI; customer_id INT NOT NULL FK→customers(id); **location_id and address DROPPED by step3:22-24** (moved to branch_locations); code VARCHAR(50); status ENUM('active','inactive') DEFAULT 'active'; created_at; updated_at; created_by INT (intended FK→users SET NULL — see risk R2). **No location FK anymore.**

### service_types  (schema.sql:64 + step2_master)
id PK AI; name VARCHAR(100) NOT NULL; default_duration ENUM('2','4','8') DEFAULT '4'; status ENUM('active','inactive') DEFAULT 'active'; updated_at; created_by INT FK→users(id) SET NULL; created_at.

### materials  (schema.sql:73 + step1 + step4 + step34)
id PK AI; company_id INT FK→companies(id) (step4:21-26); branch_id INT **NULL** (step4:33-35 relaxed NOT NULL) FK→branches(id); customer_id INT (nullable, **no FK** — implied customers.id); material_code VARCHAR(50) NOT NULL; order_no VARCHAR(50) (step34); material_month VARCHAR(7) 'YYYY-MM' (step34); material_type VARCHAR(100); material_name VARCHAR(200) (step34); mode_of_bought VARCHAR(100); price DECIMAL(12,2) DEFAULT 0; total_price DECIMAL(12,2) DEFAULT 0; cgst_percent/sgst_percent/cess_percent DECIMAL(5,2) DEFAULT 0; additional_cost DECIMAL(12,2) DEFAULT 0; cgst_amount/sgst_amount/cess_amount DECIMAL(12,2) DEFAULT 0; attachment_url VARCHAR(500); description TEXT; qty INT DEFAULT 0 (step4); service_type_id INT FK→service_types(id); material_type_id INT FK→material_types(id) ON DELETE SET NULL (step34:80-82, constraint `fk_material_type`); owner_type ENUM('customer','company') DEFAULT 'company' (step34); status ENUM('active','inactive') DEFAULT 'active'; created_by INT FK→users(id) SET NULL; created_at/updated_at. Indexes: idx_materials_order_no, idx_materials_material_month (step34:85-91).

### field_members  (schema.sql:91 + step1:265-267 + step9 + step10)
id PK AI; company_id INT FK→companies(id) (step1); user_id INT FK→users(id); employee_id VARCHAR(50) NOT NULL **UNIQUE**; name VARCHAR(100) NOT NULL; email VARCHAR(150) NOT NULL (member email — distinct from users.email); mobile VARCHAR(20) NOT NULL; aadhaar_no VARCHAR(20); dob DATE; esi_number VARCHAR(50); epf_number VARCHAR(50); police_verification_doc VARCHAR(255); login_enabled TINYINT(1) DEFAULT 0; login_role VARCHAR(50); joining_date DATE; state_id INT FK→states(id); availability_status ENUM('available','limited','leave','sick','inactive') DEFAULT 'available'; employment_status ENUM('active','inactive') DEFAULT 'active'; profile_photo VARCHAR(255); created_at/updated_at. Dropped by step10: aadhaar_verified, police_verification_status.

### member_documents  (schema.sql:111)
id PK AI; member_id INT NOT NULL FK→field_members(id); document_type ENUM('aadhaar','police_verification','pan_card','ration_card','other') NOT NULL; document_name VARCHAR(255); document_number VARCHAR(100); file_path VARCHAR(255) NOT NULL; original_name VARCHAR(255); mime_type VARCHAR(100); issue_date DATE; created_at/updated_at. **No unique on (member_id, document_type).**

### member_locations
Bootstrap (schema.sql:127): id PK AI; member_id INT NOT NULL FK→field_members(id); location_id INT NOT NULL FK→locations(id).
Migration path (step38:20-27): same id/member_id/location_id **plus is_primary TINYINT(1) DEFAULT 0, NO foreign keys**; indexes idx_member_locations_member, idx_member_locations_location. Shape depends on provisioning path (drift risk, R3).

### member_skills  (schema.sql:136)
id PK AI; member_id INT NOT NULL FK→field_members(id); service_type_id INT NOT NULL FK→service_types(id). No unique on pair (duplicates possible).

### coordinator_locations  (schema.sql:145)
id PK AI; coordinator_id INT NOT NULL FK→users(id); state_id INT FK→states(id); location_id INT FK→locations(id).

### attendance  (schema.sql:156 + step1:275-279)
id PK AI; member_id INT NOT NULL FK→field_members(id); customer_id INT FK→customers(id) (step1); date DATE NOT NULL; check_in TIMESTAMP NULL; check_out TIMESTAMP NULL; total_hours DECIMAL(4,2); status ENUM('present','present_limited','leave','absent','holiday','inactive') DEFAULT 'absent' (schema.sql:163 — 6 values, never altered); holiday_work TINYINT(1) DEFAULT 0; holiday_work_approved TINYINT(1) DEFAULT 0; location_lat/lng DECIMAL(10,7); created_at. **UNIQUE unique_attendance(member_id, date)** (schema.sql:168).

### leave_requests  (schema.sql:172 + step40)
id PK AI; member_id INT NOT NULL FK→field_members(id); from_date DATE NOT NULL; to_date DATE NOT NULL; reason TEXT; leave_type VARCHAR(30) NULL (step40:10-11); status ENUM('pending','approved','rejected') DEFAULT 'pending'; approved_by INT FK→users(id); created_at. No CHECK (to_date >= from_date).

### jobs  (schema.sql:186 + step14)
id PK AI; job_no VARCHAR(30) NOT NULL **UNIQUE**; job_date DATE NOT NULL; job_end_date DATE NULL (step14); state_id INT NOT NULL FK→states(id); location_id INT NOT NULL FK→locations(id); customer_id INT NOT NULL FK→customers(id); branch_id INT NOT NULL FK→branches(id); material_id INT FK→materials(id); service_type_id INT NOT NULL FK→service_types(id); description TEXT; address TEXT (step14); latitude DECIMAL(10,8); longitude DECIMAL(11,8); priority ENUM('low','medium','high','critical') DEFAULT 'medium'; planned_duration ENUM('2','4','8') DEFAULT '4'; scheduled_start/end DATETIME; actual_start/end DATETIME; actual_duration DECIMAL(4,2); status ENUM 18 values (see §4); created_by INT FK→users(id); created_at/updated_at.

### job_assignments  (schema.sql:218 + step1:282-286)
id PK AI; job_id INT NOT NULL FK→jobs(id); member_id INT NOT NULL FK→field_members(id); assignment_type ENUM('primary','alternate','reassigned') DEFAULT 'primary'; assigned_at TIMESTAMP; released_at TIMESTAMP NULL; reason TEXT; is_automatic TINYINT(1) DEFAULT 0; changed_by INT FK→users(id); changed_at TIMESTAMP.

### job_tracking  (schema.sql:231; no-FK re-create step30:31-40)
id PK AI; job_id INT NOT NULL; member_id INT NOT NULL; event_type ENUM('travel_start','arrived','work_start','work_end') NOT NULL; latitude/longitude DECIMAL(10,7); event_time TIMESTAMP. Bootstrap FKs: job_id→jobs, member_id→field_members. Migration path: **no FKs**, index idx_tracking_job_time(job_id, event_time).

### job_photos  (schema.sql:244)
id PK AI; job_id INT NOT NULL FK→jobs(id); member_id INT NOT NULL FK→field_members(id); photo_type ENUM('before','during','after') NOT NULL; file_path VARCHAR(255) NOT NULL; captured_at TIMESTAMP; latitude/longitude DECIMAL(10,7).

### job_checklists  (schema.sql:258)
id PK AI; job_id INT NOT NULL FK→jobs(id); item VARCHAR(255) NOT NULL; response ENUM('yes','no','na') NULL; remarks TEXT.

### job_reviews  (schema.sql:268 + step1:289-292 + step48b:18-19)
id PK AI; job_id INT NOT NULL FK→jobs(id); review_stage ENUM('vbms','cub') NOT NULL; review_cycle INT DEFAULT 1; previous_status VARCHAR(30); new_status VARCHAR(30); reviewer_id INT NOT NULL FK→users(id); decision ENUM('approved','correction_required','member_change_required','rejected','comment') NOT NULL (5 values after step48b); remarks TEXT; reviewed_at TIMESTAMP.

### job_feedback  (schema.sql:281)
id PK AI; job_id INT NOT NULL FK→jobs(id); review_id INT FK→job_reviews(id); feedback_type ENUM('minor_correction','photo_issue','work_quality','wrong_allocation','wrong_info','approved') NOT NULL; remarks TEXT NOT NULL; status ENUM('pending','in_progress','resolved') DEFAULT 'pending'; created_at.

### job_reassignments  (schema.sql:294)
id PK AI; job_id INT NOT NULL FK→jobs(id); old_member_id INT NOT NULL FK→field_members(id); new_member_id INT NOT NULL FK→field_members(id); reason TEXT; created_at.

### notifications  (schema.sql:307 + step1:295-297)
id PK AI; user_id INT NOT NULL FK→users(id); job_id INT **(no FK — implied jobs.id)**; type VARCHAR(50) NOT NULL; scope_type VARCHAR(30); scope_id INT (polymorphic, unenforced); message TEXT NOT NULL; read_at TIMESTAMP NULL; created_at.

### audit_logs  (schema.sql:319)
id PK AI; user_id INT FK→users(id); action VARCHAR(100) NOT NULL; entity VARCHAR(100) NOT NULL; entity_id INT; old_data JSON; new_data JSON; created_at.

### companies  (step1:11)
id PK AI; name VARCHAR(150) NOT NULL; code VARCHAR(50) NOT NULL **UNIQUE**; type ENUM('internal','external') DEFAULT 'external'; address TEXT; phone VARCHAR(30); email VARCHAR(150); status ENUM('active','inactive') DEFAULT 'active'; created_at/updated_at.

### customer_contacts  (step1:27; identical re-create step2_master:79-95 + conditional is_primary/login columns step2_master:97-99)
id PK AI; customer_id INT NOT NULL FK→customers(id) CASCADE; branch_id INT FK→branches(id) SET NULL; name VARCHAR(150) NOT NULL; email VARCHAR(150); mobile VARCHAR(30); designation VARCHAR(100); is_primary TINYINT(1) DEFAULT 0; login_required ENUM('yes','no') DEFAULT 'no'; login_role VARCHAR(50); username VARCHAR(100); password VARCHAR(255); status ENUM('active','inactive') DEFAULT 'active'; created_by INT; created_at/updated_at. (Superseded in practice by location_contacts after step3 data migration.)

### holiday_calendars  (step1:48 + step45:49-50 + step47:9-19)
id PK AI; name VARCHAR(150) NOT NULL; year YEAR NOT NULL; calendar_type ENUM('voltech','customer','location','state') DEFAULT 'voltech' (step47 extended); customer_id INT FK→customers(id) CASCADE; location_id INT FK→locations(id) CASCADE; state_id INT NULL FK→states(id) CASCADE + index idx_hc_state (step47); weekend_days VARCHAR(20) NOT NULL DEFAULT '0,6' (step45; day numbers 0=Sun..6=Sat); status ENUM('active','inactive') DEFAULT 'active'; created_at/updated_at.

### holidays  (step1:65 + step48a:7-8)
id PK AI; holiday_calendar_id INT NOT NULL FK→holiday_calendars(id) CASCADE; holiday_date DATE NOT NULL; holiday_name VARCHAR(200) NOT NULL; holiday_type ENUM('public','customer','location','special','state') DEFAULT 'public' (5 values after step48a); is_working_day TINYINT(1) DEFAULT 0; remarks TEXT; status ENUM('active','inactive') DEFAULT 'active'; created_at. **UNIQUE unique_calendar_date(holiday_calendar_id, holiday_date)** (step1:76).

### working_day_overrides  (step1:82 + step45:19-47)
id PK AI; scope ENUM('all','company','state','location','customer') NOT NULL DEFAULT 'customer' (step45); customer_id INT **NULL** (relaxed step45) FK→customers(id) CASCADE; location_id INT FK→locations(id) SET NULL; company_id INT FK→companies(id) CASCADE (fk_wdo_company); state_id INT FK→states(id) CASCADE (fk_wdo_state); override_date DATE NOT NULL; is_working_day TINYINT(1) DEFAULT 1; reason TEXT; approved_by INT FK→users(id); status ENUM('active','inactive') DEFAULT 'active'; created_at. CHECK `chk_wdo_scope_target` (step45:30-47) enforces exactly one scope target. Indexes: idx_wdo_override_date, idx_wdo_state, idx_wdo_company.

### employee_capacity  (step1:100)
id PK AI; member_id INT NOT NULL FK→field_members(id) CASCADE; capacity_date DATE NOT NULL; customer_id INT FK→customers(id) SET NULL; location_id INT FK→locations(id) SET NULL; capacity_hours ENUM('2','4','8') DEFAULT '8'; assigned_hours DECIMAL(4,2) DEFAULT 0; actual_hours DECIMAL(4,2) DEFAULT 0; status ENUM('available','limited','full','overloaded') DEFAULT 'available'; created_at/updated_at. **UNIQUE unique_member_date(member_id, capacity_date)**.

### employee_scopes  (step1:121)
id PK AI; user_id INT NOT NULL FK→users(id) CASCADE; scope_type ENUM('customer','state','location') NOT NULL; scope_id INT NOT NULL (**polymorphic — no FK possible, implied/unenforced**). **UNIQUE unique_user_scope(user_id, scope_type, scope_id)**.

### job_checkins  (step1:134 + step44:29-35)
id PK AI; job_id INT NOT NULL FK→jobs(id) CASCADE; member_id INT NOT NULL FK→field_members(id); checkin_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP; latitude/longitude DECIMAL(10,7); distance_from_location DECIMAL(8,2); geo_fence_verified TINYINT(1) DEFAULT 0; remarks TEXT. Index: idx_checkins_job_member_id(job_id, member_id, id) (step44).

### job_checkouts  (step1:151 + step44:37-43)
id PK AI; job_id INT NOT NULL FK→jobs(id) CASCADE; member_id INT NOT NULL FK→field_members(id); checkout_time TIMESTAMP; latitude/longitude DECIMAL(10,7); actual_hours DECIMAL(4,2); remarks TEXT. Index: idx_checkouts_job_member_id(job_id, member_id, id).

### employee_daily_work  (step1:167)
id PK AI; member_id INT NOT NULL FK→field_members(id) CASCADE; work_date DATE NOT NULL; customer_id INT FK→customers(id) SET NULL; location_id INT FK→locations(id) SET NULL; holiday_calendar_id INT FK→holiday_calendars(id) SET NULL; is_working_day TINYINT(1) DEFAULT 1; scheduled_hours/assigned_hours/actual_hours DECIMAL(4,2) DEFAULT 0; leave_status ENUM('none','full_day','half_day') DEFAULT 'none'; attendance_status ENUM('present','absent','holiday','leave') DEFAULT 'absent'; holiday_work TINYINT(1) DEFAULT 0; assignment_status ENUM('unassigned','assigned','in_progress','completed') DEFAULT 'unassigned'; created_at/updated_at. **UNIQUE unique_member_date_customer(member_id, work_date, customer_id)** — note: NULL customer_id breaks uniqueness (MySQL allows duplicate NULLs).

### salary_inputs  (step1:194)
id PK AI; member_id INT NOT NULL FK→field_members(id) CASCADE; month INT NOT NULL; year INT NOT NULL; working_days/calendar_working_days/present_days/leave_days/customer_holiday_days/holiday_worked_days INT DEFAULT 0; assigned_hours/actual_hours DECIMAL(6,2) DEFAULT 0; customer_visits/locations_visited/jobs_completed/rework_jobs/approved_jobs INT DEFAULT 0; holiday_work_hours DECIMAL(6,2) DEFAULT 0; status ENUM('draft','finalized','exported') DEFAULT 'draft'; created_at/updated_at. **UNIQUE unique_member_month(member_id, month, year)**.

### mis_records  (step1:223)
id PK AI; member_id INT NOT NULL FK→field_members(id) CASCADE; month INT NOT NULL; year INT NOT NULL; total_jobs/completed_jobs/pending_jobs/rework_count/approved_jobs→approved_count INT DEFAULT 0; avg_completion_hours DECIMAL(6,2) DEFAULT 0; customer_satisfaction DECIMAL(5,2) NULL; attendance_pct DECIMAL(5,2) DEFAULT 0; created_at. **UNIQUE unique_mis_month(member_id, month, year)**.

### permissions  (step1:244)
id PK AI; name VARCHAR(100) NOT NULL **UNIQUE**; module VARCHAR(100) NOT NULL; description TEXT; created_at. Seeded ~60 base perms (step1:309-379) + feedback.* (step24:74-81) + documentation.* (step25:8-11).

### role_permissions  (step1:252)
id PK AI; role VARCHAR(50) NOT NULL (**free-text — no FK to roles.name or users.role enum; implied, unenforced**); permission_id INT NOT NULL FK→permissions(id) CASCADE. **UNIQUE unique_role_permission(role, permission_id)**.

### customer_addresses  (step2_master:55)
id PK AI; customer_id INT NOT NULL FK→customers(id) CASCADE; location_id INT FK→locations(id) SET NULL; address_line1/2 VARCHAR(255); city VARCHAR(100); state_id INT FK→states(id) SET NULL; pincode VARCHAR(10); latitude/longitude DECIMAL(10,7); is_primary TINYINT(1) DEFAULT 0 (**no UNIQUE(customer_id, is_primary) — multiple primaries possible**); status ENUM('active','inactive') DEFAULT 'active'; created_by INT FK→users(id) SET NULL; created_at/updated_at.

### branch_locations  (step3:38)
id PK AI; branch_id INT NOT NULL FK→branches(id) CASCADE; location_id INT NOT NULL FK→locations(id) CASCADE; address_line1/2 VARCHAR(255); city VARCHAR(100); state_id INT FK→states(id) SET NULL; pincode VARCHAR(10); latitude/longitude DECIMAL(10,7); status ENUM('active','inactive') DEFAULT 'active'; created_by INT FK→users(id) SET NULL; created_at/updated_at. **No unique(branch_id, location_id)** — duplicate branch↔location rows possible (step3 migration inserts are guarded by NOT EXISTS, but not by constraint).

### location_contacts  (step3:63)
id PK AI; branch_location_id INT NOT NULL FK→branch_locations(id) CASCADE; name VARCHAR(150) NOT NULL; email/mobile/designation; is_primary TINYINT(1) DEFAULT 0; login_required ENUM('yes','no') DEFAULT 'no'; login_role VARCHAR(50); username VARCHAR(100); password VARCHAR(255); status ENUM('active','inactive') DEFAULT 'active'; created_by INT FK→users(id) SET NULL; created_at/updated_at.

### app_settings  (step5:1 + step32 repair)
id PK AI; setting_key VARCHAR(100) NOT NULL UNIQUE (implicit) / UNIQUE INDEX uk_setting_key (step32:25-27); setting_value TEXT; created_at/updated_at. Seeded keys: company_id, company_name, support_email, timezone (step5); 18 security/GPS/notification keys (step6); geofence_meters/office_latitude/office_longitude (step29); location_ping_interval_seconds/geofence_alert_interval_seconds/location_retention_days (step30); morning_brief_enabled/morning_brief_time (step31, step37).

### login_attempts  (step6:24)
id PK AI; email VARCHAR(150) NOT NULL; ip_address VARCHAR(45); attempted_at TIMESTAMP; success TINYINT(1) DEFAULT 0. Index idx_email_time(email, attempted_at).

### account_lockouts  (step6:34)
id PK AI; email VARCHAR(150) NOT NULL **UNIQUE**; locked_until DATETIME NOT NULL; created_at.

### user_sessions  (step7:2)
id PK AI; user_id INT NOT NULL FK→users(id) CASCADE; token_hash VARCHAR(64) NOT NULL; ip_address VARCHAR(45); user_agent TEXT; device_type/os/browser VARCHAR; location VARCHAR(200); is_active TINYINT(1) DEFAULT 1; last_active_at TIMESTAMP; created_at. Indexes: idx_user_active(user_id, is_active), idx_token(token_hash), idx_last_active(last_active_at).

### user_devices  (step7:22)
id PK AI; user_id INT NOT NULL FK→users(id) CASCADE; device_name VARCHAR(200); os/browser/device_type VARCHAR(50) (device_type DEFAULT 'unknown'); fingerprint VARCHAR(64); last_seen_at TIMESTAMP; created_at. Index idx_user_devices(user_id).

### holiday_rules  (step8:2)
id PK AI; holiday_calendar_id INT NOT NULL FK→holiday_calendars(id) CASCADE; rule_name VARCHAR(200) NOT NULL; rule_type ENUM('nth_weekday','every_weekday','date_range') NOT NULL; config JSON NOT NULL (shape in COMMENT step8:7); status ENUM('active','inactive') DEFAULT 'active'; created_by/updated_by INT (no FK); created_at/updated_at. Index idx_cal_rule(holiday_calendar_id, status).

### member_police_docs  (step11:4)
id PK AI; member_id INT NOT NULL FK→field_members(id) CASCADE; file_path VARCHAR(500) NOT NULL; original_name VARCHAR(255); created_at.

### job_daily_tasks  (step15:4 + step17 + step18:6-7 + step42 + step46 + step49 + step50a)
id PK AI; job_id INT NOT NULL FK→jobs(id) CASCADE; task_date DATE NOT NULL; checkin_time/checkout_time DATETIME NULL; before_photo_url/after_photo_url VARCHAR(500) (primary photo); before_photos/after_photos TEXT (JSON array, step42); status ENUM('pending','in_progress','waiting_vbms_review','waiting_cub_review','completed','reassigned','skipped','redo','absent') DEFAULT 'pending' (9 values, final per step50a:8-9); latitude/longitude DECIMAL(10,7); checkin_latitude/longitude, checkout_latitude/longitude DECIMAL(10,7); checkin_address/checkout_address TEXT (step46); notes TEXT; assigned_member_id INT FK→field_members(id) SET NULL; original_member_id INT **(no FK — implied field_members.id)** (step17); assignment_source ENUM('AUTO','MANUAL') DEFAULT 'AUTO' (step17); created_by INT FK→users(id) SET NULL; active_day TINYINT **GENERATED ALWAYS AS** (CASE WHEN status IN ('redo','skipped','reassigned') THEN NULL ELSE 1 END) STORED (step49:17-19); created_at/updated_at.
Constraints/indexes: UNIQUE unique_active_job_date(job_id, task_date, active_day) (step49; replaced unique_job_date from step15/step22); idx_daily_tasks_job, idx_daily_tasks_date, idx_daily_tasks_status (step15); idx_daily_tasks_original_member (step17).

### daily_attendance  (step16:4)
id PK AI; member_id INT NOT NULL FK→field_members(id) CASCADE; attendance_date DATE NOT NULL; total_checkins/total_checkouts/total_work_minutes/total_expected_minutes INT DEFAULT 0; capacity_pct DECIMAL(5,2) DEFAULT 0; jobs_attended/customers_served INT DEFAULT 0; first_checkin/last_checkout TIME NULL; status ENUM('present','absent','partial','holiday') DEFAULT 'absent' (step16:17); created_at/updated_at. **UNIQUE unique_member_date(member_id, attendance_date)**; indexes idx_attendance_date, idx_attendance_member.

### attendance_config  (step16:28)
id PK AI; config_key VARCHAR(50) NOT NULL UNIQUE; config_value VARCHAR(100) NOT NULL; updated_at. Seeds: refresh_hour=18, refresh_minute=0, auto_refresh_enabled=1.

### ticket_redo_history  (step18:10)
id PK AI; original_ticket_id INT **(no FK — implied job_daily_tasks.id)**; replacement_ticket_id INT **(no FK — implied)**; job_id INT NOT NULL FK→jobs(id) CASCADE (fk_redo_job); redo_reason TEXT NOT NULL; redo_by INT FK→users(id) SET NULL (fk_redo_by); redo_at TIMESTAMP; original_member_id INT (**no FK**); original_task_date DATE; original_status VARCHAR(30). Indexes: idx_redo_history_job, idx_redo_history_original, idx_redo_history_replacement.

### mail_formats  (step20:3)
id PK AI; email_name VARCHAR(150) NOT NULL UNIQUE (uk_email_name); subject VARCHAR(500) NOT NULL; body_html LONGTEXT NOT NULL; status ENUM('active','inactive') DEFAULT 'active'; created_by/updated_by INT (no FK); created_at/updated_at.

### feedback_templates  (step24:6)
id PK AI; name VARCHAR(200) NOT NULL; description TEXT; service_type VARCHAR(100); questions JSON NOT NULL (array of {id,text,type,required,options}); status ENUM('active','inactive') DEFAULT 'active'; created_by INT FK→users(id) SET NULL; updated_by INT FK→users(id) SET NULL; created_at/updated_at.

### feedback_requests  (step24:22 + step26 + step39)
id PK AI; token VARCHAR(64) NOT NULL (UNIQUE + uk_feedback_token — duplicate unique index on same column); customer_id INT NOT NULL FK→customers(id) CASCADE; branch_id INT FK→branches(id) SET NULL; location_id INT FK→**branch_locations**(id) SET NULL (misleading name, step24:42); template_id INT NOT NULL FK→feedback_templates(id) CASCADE; sent_by INT NOT NULL FK→users(id) CASCADE; job_id INT NULL FK→jobs(id) SET NULL + KEY idx_feedback_requests_job (step39); job_no VARCHAR(50); assigned_person VARCHAR(255); service_type VARCHAR(100); mail_status ENUM('pending','sent','failed') DEFAULT 'pending'; mail_error TEXT; feedback_status ENUM('pending','submitted') DEFAULT 'pending'; submitted_at TIMESTAMP NULL; submitted_by_name/email/designation VARCHAR(200); token_used TINYINT(1) DEFAULT 0; created_at/updated_at.

### feedback_recipients  (step24:49 + step26:7-14)
id PK AI; request_id INT NOT NULL FK→feedback_requests(id) CASCADE; contact_name VARCHAR(200) NOT NULL; contact_email VARCHAR(200) NOT NULL; contact_mobile VARCHAR(30); contact_designation VARCHAR(200); contact_type ENUM('branch','company') DEFAULT 'branch'; token VARCHAR(64) NULL UNIQUE (uk_feedback_recipient_token); token_used TINYINT(1) DEFAULT 0; mail_status ENUM('pending','sent','failed') DEFAULT 'pending'; mail_error TEXT; sent_at TIMESTAMP NULL; responded_at TIMESTAMP NULL; created_at.

### feedback_responses  (step24:62 + step26:17-23 + step41)
id PK AI; request_id INT NOT NULL FK→feedback_requests(id) CASCADE; recipient_id INT NULL (**index only, no FK — implied feedback_recipients.id**, step26:18-23); answers JSON NOT NULL; overall_score DECIMAL(5,2); rating_label VARCHAR(50); comments JSON; submitted_by_name/email/designation/contact_name VARCHAR(200); signature LONGTEXT (base64 PNG, step41); submitted_at TIMESTAMP.

### job_location_pings  (step30:15)
id PK AI; job_id INT NOT NULL (**no FK — telemetry, intentional**); member_id INT NOT NULL (**no FK**); task_id INT NULL (**no FK — implied job_daily_tasks.id**); latitude/longitude DECIMAL(10,7) NOT NULL; accuracy DECIMAL(8,2); distance_from_location DECIMAL(8,2); geo_fence_verified TINYINT(1) DEFAULT 1; event ENUM('ping','geofence_exit','geofence_enter') DEFAULT 'ping'; recorded_at TIMESTAMP. Indexes: idx_pings_job_time, idx_pings_member_time, idx_pings_job_member_id (job_id, member_id, id; step44).

### roles  (step33:8)
id PK AI; name VARCHAR(50) NOT NULL **UNIQUE**; description VARCHAR(255); child_permissions JSON NULL (module→child→action booleans for UI gating); created_by INT FK→users(id) SET NULL; created_at/updated_at.

### user_preferences  (step33:19)
id PK AI; user_id INT NOT NULL FK→users(id) CASCADE + UNIQUE idx_user_id (step33:44, also step19:10 on legacy DBs); display_name VARCHAR(100); preferred_name VARCHAR(100); nickname VARCHAR(50); theme VARCHAR(20) DEFAULT 'default'; primary_color VARCHAR(20) DEFAULT '#059669'; accent_color VARCHAR(20) DEFAULT '#06b6d4'; appearance_mode ENUM('light','dark','system') DEFAULT 'light'; menu_layout ENUM('vertical','horizontal') DEFAULT 'vertical'; sidebar_state ENUM('expanded','collapsed') DEFAULT 'expanded'; menu_density ENUM('compact','comfortable') DEFAULT 'comfortable'; sidebar_color_mode ENUM('light','theme-tinted','custom','dark') DEFAULT 'light'; sidebar_custom_color VARCHAR(20) DEFAULT '#1e293b'; show_breadcrumbs/auto_collapse_sidebar/animations_enabled TINYINT(1) DEFAULT 1; compact_header TINYINT(1) DEFAULT 0; font_family VARCHAR(20) DEFAULT 'inter'; font_size ENUM('small','medium','large','xlarge') DEFAULT 'medium'; topbar_color_mode ENUM('light','dark','theme-tinted','custom') DEFAULT 'light'; topbar_custom_color VARCHAR(20) DEFAULT '#f8fafc'; created_at/updated_at.

### password_reset_tokens  (step34:8)
id PK AI; user_id INT NOT NULL FK→users(id) CASCADE; token VARCHAR(64) NOT NULL UNIQUE; expires_at TIMESTAMP NOT NULL; used TINYINT(1) DEFAULT 0; created_at.

### material_types  (step34:19)
id PK AI; name VARCHAR(100) NOT NULL UNIQUE; status ENUM('active','inactive') DEFAULT 'active'; created_at.

### schema_migrations  (runMigrations.js:154-162)
id INT AUTO_INCREMENT PK; filename VARCHAR(255) NOT NULL UNIQUE; applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP. Behavior: created idempotently at runner start; `SELECT filename` set-membership check skips already-applied files; `INSERT IGNORE` marks applied (runMigrations.js:169-174); bootstrap of schema.sql is also recorded (markApplied('schema.sql')). Errors matching `isAlreadyAppliedError()` (duplicate column/table/key, "doesn't exist", etc.) are skipped per-statement (runMigrations.js:120-147, 238-244), so a step can be recorded as applied even when individual statements silently failed — bookkeeping ≠ verified schema (acknowledged by schemaSpec.js:11-14).

## Section 3: Relationship Map

### Enforced foreign keys (113 definitions; 4 are bootstrap-only — see note)

Parent → Child (column → referenced, ON DELETE):
1. states → locations (state_id), default RESTRICT
2. customers → branches (customer_id)
3. branches → materials (branch_id)
4. service_types → materials (service_type_id)
5. users → materials (created_by, SET NULL)
6. companies → materials (company_id)
7. material_types → materials (material_type_id, SET NULL)
8. users → field_members (user_id)
9. states → field_members (state_id)
10. companies → field_members (company_id)
11. field_members → member_documents (member_id)
12. field_members → member_locations (member_id) — **bootstrap only (schema.sql)**
13. locations → member_locations (location_id) — **bootstrap only**
14. field_members → member_skills (member_id)
15. service_types → member_skills (service_type_id)
16. users → coordinator_locations (coordinator_id)
17. states → coordinator_locations (state_id)
18. locations → coordinator_locations (location_id)
19. field_members → attendance (member_id)
20. customers → attendance (customer_id)
21. field_members → leave_requests (member_id)
22. users → leave_requests (approved_by)
23. states/locations/customers/branches/materials/service_types/users → jobs (state_id, location_id, customer_id, branch_id, material_id, service_type_id, created_by)
24. jobs → job_assignments (job_id); field_members → job_assignments (member_id); users → job_assignments (changed_by)
25. jobs → job_tracking (job_id); field_members → job_tracking (member_id) — **bootstrap only (schema.sql:239-240); step30 re-create has no FKs**
26. jobs → job_photos (job_id); field_members → job_photos (member_id)
27. jobs → job_checklists (job_id)
28. jobs → job_reviews (job_id); users → job_reviews (reviewer_id)
29. jobs → job_feedback (job_id); job_reviews → job_feedback (review_id)
30. jobs → job_reassignments (job_id); field_members → job_reassignments (old_member_id, new_member_id)
31. users → notifications (user_id)
32. users → audit_logs (user_id)
33. customers → customer_contacts (CASCADE); branches → customer_contacts (SET NULL); users → customer_contacts (SET NULL)
34. customers → holiday_calendars (CASCADE); locations → holiday_calendars (CASCADE); states → holiday_calendars (CASCADE, step47)
35. holiday_calendars → holidays (CASCADE)
36. customers → working_day_overrides (CASCADE); locations → working_day_overrides (SET NULL); users → working_day_overrides (approved_by); companies → working_day_overrides (CASCADE); states → working_day_overrides (CASCADE)
37. field_members → employee_capacity (CASCADE); customers/locations → employee_capacity (SET NULL)
38. users → employee_scopes (CASCADE)
39. jobs → job_checkins (CASCADE); field_members → job_checkins
40. jobs → job_checkouts (CASCADE); field_members → job_checkouts
41. field_members → employee_daily_work (CASCADE); customers/locations/holiday_calendars → employee_daily_work (SET NULL)
42. field_members → salary_inputs (CASCADE)
43. field_members → mis_records (CASCADE)
44. permissions → role_permissions (CASCADE)
45. users → states/locations/service_types/customers (created_by, SET NULL)
46. customers → customer_addresses (CASCADE); locations/states → customer_addresses (SET NULL); users → customer_addresses (SET NULL)
47. branches → branch_locations (CASCADE); locations → branch_locations (CASCADE); states → branch_locations (SET NULL); users → branch_locations (SET NULL)
48. branch_locations → location_contacts (CASCADE); users → location_contacts (SET NULL)
49. users → user_sessions (CASCADE); users → user_devices (CASCADE)
50. holiday_calendars → holiday_rules (CASCADE)
51. field_members → member_police_docs (CASCADE)
52. jobs → job_daily_tasks (CASCADE); field_members → job_daily_tasks (SET NULL); users → job_daily_tasks (SET NULL)
53. field_members → daily_attendance (CASCADE)
54. jobs → ticket_redo_history (CASCADE); users → ticket_redo_history (SET NULL)
55. users → feedback_templates (CASCADE, created_by/updated_by)
56. customers → feedback_requests (CASCADE); branches → feedback_requests (SET NULL); branch_locations → feedback_requests (location_id, SET NULL); feedback_templates → feedback_requests (CASCADE); users → feedback_requests (CASCADE); jobs → feedback_requests (SET NULL, step39)
57. feedback_requests → feedback_recipients (CASCADE)
58. feedback_requests → feedback_responses (CASCADE)
59. users → roles (SET NULL); users → user_preferences (CASCADE); users → password_reset_tokens (CASCADE)
60. users → customers (created_by, SET NULL)

### Implied, unenforced relationships (naming-only; no FK in DDL)
| Child column(s) | Implied parent | Evidence |
|---|---|---|
| employee_scopes.scope_id (per scope_type) | customers.id / states.id / locations.id | step1:121-129 (polymorphic) |
| role_permissions.role (VARCHAR) | roles.name / users.role enum | step1:252-258; no FK added anywhere |
| notifications.job_id | jobs.id | schema.sql:310 (job_id has no FK) |
| notifications.scope_type/scope_id | polymorphic entities | step1:295-297 |
| job_daily_tasks.original_member_id | field_members.id | step17:5-6 (no FK) |
| ticket_redo_history.original_ticket_id / replacement_ticket_id / original_member_id | job_daily_tasks.id / field_members.id | step18:12-20 (indexes only) |
| job_location_pings.job_id / member_id / task_id | jobs.id / field_members.id / job_daily_tasks.id | step30:8-10 (intentional, comment) |
| member_locations (migration path) member_id/location_id | field_members.id/locations.id | step38:13-15 (intentional, comment); FKs exist only on bootstrap path |
| job_tracking (migration path) job_id/member_id | jobs.id/field_members.id | step30:31-40 (no FK) |
| feedback_responses.recipient_id | feedback_recipients.id | step26:18-23 (KEY only, no FK) |
| materials.customer_id | customers.id | schema.sql:76 (nullable, no FK) |
| holiday_rules.created_by/updated_by | users.id | step8:9-10 (no FK) |
| mail_formats.created_by/updated_by | users.id | step20:9-10 (no FK) |
| customer_contacts.created_by / customer_addresses.created_by | users.id | step1:36 (no FK on customer_contacts.created_by) |
| login_attempts.email / account_lockouts.email | users.email | by convention only |
| audit_logs.entity/entity_id | polymorphic | schema.sql:322-324 |

## Section 4: Constraints, Indexes, ENUMs

### 4a. Job status ENUM — 18 values (schema.sql:204, never altered by any migration)
`draft, new, assigned, accepted, travelling, arrived, in_progress, submitted, vbms_review, cub_review, feedback, rework, reassigned, approved, completed, closed, cancelled, exception` (default `draft`).

Implied workflow: **draft → new → assigned → accepted → travelling → arrived → in_progress → submitted → vbms_review → (cub_review) → feedback → rework/reassigned → approved → completed → closed**, with terminal side-paths **cancelled** and **exception**. Two-stage review: VBMS internal review then CUB (customer) review (job_reviews.review_stage ENUM('vbms','cub'), schema.sql:271). Note: a parallel ticket-level status machine now runs the day-to-day flow (job_daily_tasks.status), so jobs.status and ticket status can diverge.

### 4b. Ticket (job_daily_tasks) status ENUM — 9 values (final: migration_step50_absent_status.sql:8-9)
`pending, in_progress, waiting_vbms_review, waiting_cub_review, completed, reassigned, skipped, redo, absent` (default `pending`). History: step15 (5 values) → step18 (added waiting_* + redo) → step50a (added absent). Workflow: pending → in_progress → waiting_vbms_review → waiting_cub_review → completed; redo regenerates a replacement ticket (step18); absent assigned by cron when a scheduled day passes with no check-in (step50a:1-6); reassigned/skipped/redo are archived states (active_day NULL, step49).

### 4c. Attendance status ENUMs
- `attendance.status` (schema.sql:163): `present, present_limited, leave, absent, holiday, inactive` — default `absent`. 6 values; never altered.
- `daily_attendance.status` (migration_step16:17): `present, absent, partial, holiday` — default `absent`. Derived: absent if 0 check-ins; present if actual ≥ 80% of expected minutes; else partial (sp_refresh_daily_attendance CASE, step16:83-87).
- `employee_daily_work.attendance_status` (step1:179): `present, absent, holiday, leave`.
- Ticket-level `absent` (step50a) closes the loop when members never check in.

### 4d. All UNIQUE constraints
| Table | Unique key | Columns | Source |
|---|---|---|---|
| users | (implicit) | username | schema.sql:11 |
| users | (implicit) | email | schema.sql:12 (email now nullable; multiple NULLs OK — step36) |
| customers | (implicit) | code | schema.sql:44 |
| field_members | (implicit) | employee_id | schema.sql:94 |
| jobs | (implicit) | job_no | schema.sql:188 |
| attendance | unique_attendance | (member_id, date) | schema.sql:168 |
| companies | (implicit) | code | step1:14 |
| holidays | unique_calendar_date | (holiday_calendar_id, holiday_date) | step1:76 |
| employee_capacity | unique_member_date | (member_id, capacity_date) | step1:115 |
| employee_scopes | unique_user_scope | (user_id, scope_type, scope_id) | step1:128 |
| employee_daily_work | unique_member_date_customer | (member_id, work_date, customer_id) | step1:188 (NULL customer_id → uniqueness not enforced) |
| salary_inputs | unique_member_month | (member_id, month, year) | step1:217 |
| mis_records | unique_mis_month | (member_id, month, year) | step1:238 |
| permissions | (implicit) | name | step1:246 |
| role_permissions | unique_role_permission | (role, permission_id) | step1:257 |
| app_settings | (implicit + uk_setting_key) | setting_key | step5:3; repair step32:25-27 |
| account_lockouts | (implicit) | email | step6:36 |
| daily_attendance | unique_member_date | (member_id, attendance_date) | step16:20 |
| attendance_config | (implicit) | config_key | step16:31 |
| mail_formats | uk_email_name | (email_name) | step20:13 |
| feedback_requests | token + uk_feedback_token | (token) — duplicate index on same column | step24:24,45 |
| feedback_recipients | uk_feedback_recipient_token | (token) | step26:14 |
| roles | (implicit) | name | step33:10 |
| user_preferences | idx_user_id | (user_id) | step33:44; step19:10 (legacy) |
| password_reset_tokens | (implicit) | token | step34:11 |
| material_types | (implicit) | name | step34:21 |
| job_daily_tasks | unique_active_job_date | (job_id, task_date, active_day) | step49:21 (replaced unique_job_date from step15:27/step22:9) |

### 4e. CHECK constraints
- working_day_overrides `chk_wdo_scope_target` (migration_step45:30-47): exactly one scope target column set per scope value (all/company/state/location/customer). The only declarative CHECK in the effective schema.
- (Historical: fix_font_size_constraint.sql drops a `chk_font_size` CHECK on user_preferences.font_size via helper procedure `drop_chk()` — legacy manual repair; font_size is ENUM in step33 so CHECK is no longer required.)

### 4f. Secondary indexes (explicit, non-PK, non-unique)
| Table | Index | Columns | Source |
|---|---|---|---|
| login_attempts | idx_email_time | (email, attempted_at) | step6:30 |
| user_sessions | idx_user_active / idx_token / idx_last_active | (user_id,is_active) / (token_hash) / (last_active_at) | step7:16-18 |
| user_devices | idx_user_devices | (user_id) | step7:33 |
| holiday_rules | idx_cal_rule | (holiday_calendar_id, status) | step8:14 |
| job_daily_tasks | idx_daily_tasks_job/date/status/original_member | job_id / task_date / status / original_member_id | step15:31-33, step17:16 |
| daily_attendance | idx_attendance_date / idx_attendance_member | attendance_date / member_id | step16:24-25 |
| ticket_redo_history | idx_redo_history_job/original/replacement | job_id / original_ticket_id / replacement_ticket_id | step18:25-27 |
| feedback_responses | idx_feedback_responses_recipient | (recipient_id) | step26:23 |
| feedback_requests | idx_feedback_requests_job | (job_id) | step39:15 |
| job_location_pings | idx_pings_job_time / idx_pings_member_time / idx_pings_job_member_id | (job_id,recorded_at) / (member_id,recorded_at) / (job_id,member_id,id) | step30:27-28, step44:24 |
| job_tracking | idx_tracking_job_time | (job_id, event_time) | step30:39 |
| job_checkins | idx_checkins_job_member_id | (job_id, member_id, id) | step44:33 |
| job_checkouts | idx_checkouts_job_member_id | (job_id, member_id, id) | step44:41 |
| working_day_overrides | idx_wdo_override_date / idx_wdo_state / idx_wdo_company | override_date / state_id / company_id | step45:26-28 |
| holiday_calendars | idx_hc_state | (state_id) | step47:18 |
| member_locations | idx_member_locations_member / _location | member_id / location_id | step38:25-26, 32-33 |
| materials | idx_materials_order_no / idx_materials_material_month | order_no / material_month | step34:86-90 |

### 4g. Complete ENUM column catalog (table.column → values)
- users.role → super_admin, admin, coordinator, field_member, vbms_reviewer, cub_reviewer, customer_admin, customer_viewer, house_keeping (step28 superset of step2_admin)
- users.status; states.status; locations.status; customers.status; branches.status; service_types.status; companies.status; materials.status; field_members (employment_status); customer_contacts.status; holiday_calendars.status; holidays.status; working_day_overrides.status; app-inactive rows: mail_formats.status; feedback_templates.status; material_types.status; holiday_rules.status — all `active,inactive`
- service_types.default_duration / jobs.planned_duration / employee_capacity.capacity_hours → `2,4,8`
- field_members.availability_status → available, limited, leave, sick, inactive
- member_documents.document_type → aadhaar, police_verification, pan_card, ration_card, other
- attendance.status → present, present_limited, leave, absent, holiday, inactive
- leave_requests.status → pending, approved, rejected
- jobs.priority → low, medium, high, critical
- jobs.status → 18 values (§4a)
- job_assignments.assignment_type → primary, alternate, reassigned
- job_tracking.event_type → travel_start, arrived, work_start, work_end
- job_photos.photo_type → before, during, after
- job_checklists.response → yes, no, na
- job_reviews.review_stage → vbms, cub; decision → approved, correction_required, member_change_required, rejected, comment (step48b)
- job_feedback.feedback_type → minor_correction, photo_issue, work_quality, wrong_allocation, wrong_info, approved; status → pending, in_progress, resolved
- companies.type → internal, external
- customer_contacts.login_required / location_contacts.login_required → yes, no
- holiday_calendars.calendar_type → voltech, customer, location, state (step47)
- holidays.holiday_type → public, customer, location, special, state (step48a)
- working_day_overrides.scope → all, company, state, location, customer (step45)
- employee_capacity.status → available, limited, full, overloaded
- employee_scopes.scope_type → customer, state, location
- employee_daily_work.leave_status → none, full_day, half_day; attendance_status → present, absent, holiday, leave; assignment_status → unassigned, assigned, in_progress, completed
- salary_inputs.status → draft, finalized, exported
- daily_attendance.status → present, absent, partial, holiday
- job_daily_tasks.status → 9 values (§4b); assignment_source → AUTO, MANUAL
- mail_formats.status; feedback mail_formats → pending, sent, failed (feedback_requests.mail_status, feedback_recipients.mail_status)
- feedback_requests.feedback_status → pending, submitted; feedback_recipients.contact_type → branch, company
- job_location_pings.event → ping, geofence_exit, geofence_enter
- user_preferences: appearance_mode (light,dark,system); menu_layout (vertical,horizontal); sidebar_state (expanded,collapsed); menu_density (compact,comfortable); sidebar_color_mode (light,theme-tinted,custom,dark); font_size (small,medium,large,xlarge); topbar_color_mode (light,dark,theme-tinted,custom)
- holiday_rules.rule_type → nth_weekday, every_weekday, date_range
- materials.owner_type → customer, company (step34)

## Section 5: Views & Stored Procedures

### v_attendance_capacity (VIEW, migration_step16:41-58; re-declared in migration_overall.sql:2145)
`CREATE OR REPLACE VIEW` over job_daily_tasks ⋈ jobs ⋈ service_types; groups by (assigned_member_id, task_date); computes total_tasks, total_checkins, total_checkouts, actual_work_minutes = Σ TIMESTAMPDIFF(minute, checkin, checkout), expected_work_minutes = Σ COALESCE(jobs.planned_duration, service_types.default_duration)×60, jobs_count, customers_count, first_checkin, last_checkout. Only rows with assigned_member_id NOT NULL. No customer/job scoping filters.

### sp_refresh_daily_attendance (PROCEDURE, migration_step16:62-103)
`IN p_date DATE` (defaults to CURDATE via COALESCE). Upserts into daily_attendance from v_attendance_capacity for the given date using `ON DUPLICATE KEY UPDATE` on unique_member_date. Derives capacity_pct = actual/expected×100 and status: absent if 0 check-ins; present if actual ≥ 0.8×expected; else partial. Refresh schedule seeded in attendance_config (refresh_hour=18, auto_refresh_enabled=1).

### Helper (not applied by runner)
- `drop_chk()` procedure in fix_font_size_constraint.sql:21 and migration_overall.sql:3941 — manual helper that drops the legacy chk_font_size CHECK; invoked only when those scripts are run manually.

## Section 6: Data-Integrity Risks Observed (evidence-based)

**R1 — Plaintext password column.** `users.original_password VARCHAR(255)` added by migration_step35_user_columns.sql:6-9 ("plain password stored for display/reset flows"); also seeded by runMigrations.js:312-313 which inserts the default admin's plaintext password. A DB leak exposes usable credentials alongside bcrypt hashes. Similarly `location_contacts.password` / `customer_contacts.password` (step3:74, step2_master:98) store login passwords in a column named `password` with no DDL evidence of hashing.

**R2 — branches.created_by silently missing on bootstrap DBs.** schema.sql:50-61 already defines `status` and `created_at` for branches; migration_step3:27-32 issues ONE multi-clause `ALTER TABLE branches ADD COLUMN status..., created_at..., updated_at..., created_by..., ADD FOREIGN KEY...`. The duplicate `status` column aborts the whole statement; the runner skips such errors per-statement (runMigrations.js:123 "Duplicate column"). Result: DBs bootstrapped from schema.sql likely lack `branches.created_by` (and its FK) while migration-only DBs have it — schema drift with no heal entry in schemaSpec.js.

**R3 — Intentionally FK-less tables.** job_location_pings (step30:8-10), member_locations migration path (step38:13-15), and job_tracking migration path (step30) carry no foreign keys by design (charset/PK errno-150 workarounds). Orphaned GPS telemetry and member↔location links are possible; cleanup relies on an application cron "purges by age AND orphans" (step30:10) that is not enforced by the database. member_locations has FKs on the schema.sql bootstrap path but not the step38 path — two shapes for one table.

**R4 — CASCADE delete chains destroy history.** jobs.id cascades to job_daily_tasks (step15:24), job_checkins (step1:144), job_checkouts (step1:160), ticket_redo_history (step18:21) — deleting a job silently erases check-in evidence, tickets, and audit lineage. customers.id cascades to customer_addresses (step2_master:71), holiday_calendars (step1:58), holidays via calendar, working_day_overrides (step1:92), feedback_requests/responses (step24:40,70) — deleting a customer destroys survey responses and payroll-relevant calendar history. No soft-delete columns exist in DDL.

**R5 — Unenforced ticket lineage in redo flow.** ticket_redo_history.original_ticket_id / replacement_ticket_id / original_member_id have indexes but no FKs (step18:12-27). Because job_daily_tasks.job_id CASCADEs, deleting a job removes the tickets but leaves redo history rows pointing at nonexistent ticket IDs (only job_id FK cascade removes rows whose job survived in history... rows are removed only when their own job_id row is deleted; original/replacement ticket refs dangle if individual task rows are deleted).

**R6 — Polymorphic / free-text references.** employee_scopes.(scope_type, scope_id) (step1:121-129), role_permissions.role (step1:252 — no FK to roles.name, and step27 deletes/re-seeds grants by string), notifications.(scope_type, scope_id) (step1:295-297), notifications.job_id (schema.sql:310 — no FK), audit_logs.entity/entity_id (schema.sql:322-324). Dangling or mistyped values are undetectable by the DB.

**R7 — Destructive in-migration dedupe DELETEs.** step19:6-8 deletes duplicate user_preferences rows; step22:4-6 hard-deletes "duplicate" job_daily_tasks rows keeping lowest id (could remove in-progress tickets that share a day); step32:14-16 deletes duplicate app_settings rows. No WHERE-guard beyond the dedupe predicate; irreversible if run against wrong data.

**R8 — Naming/constraint gaps on feedback + misc.** feedback_requests.location_id actually references branch_locations(id) (step24:42) — misleading joins; feedback_requests.token has both an inline UNIQUE and uk_feedback_token (duplicate index); feedback_responses.recipient_id has an index but no FK (step26:18-23); employee_daily_work unique_member_date_customer(member_id, work_date, customer_id) is ineffective when customer_id is NULL (MySQL permits duplicate NULLs, step1:188); member_documents has no unique(member_id, document_type); member_skills has no unique(member_id, service_type_id); branch_locations has no unique(branch_id, location_id); customers.is_primary pattern in customer_addresses lacks a composite unique, allowing multiple "primary" addresses; parallel status machines (jobs.status 18-value ENUM never migrated vs job_daily_tasks.status) can disagree with no constraint tying them.

**Additional observation (drift/process risk):** schema_migrations records a filename even when individual statements were skipped as "already applied" or "doesn't exist" (runMigrations.js:123, 143-145), so bookkeeping cannot prove the live schema matches the DDL — an entire class of drift is acknowledged by schemaSpec.js:1-14 and its boot-time self-heal list (holiday_calendars.state_id/weekend_days/calendar_type, holidays.holiday_type, customers.holiday_calendar_id, working_day_overrides.scope/company_id/state_id). Tables/columns outside that list (e.g., R2) have no self-heal.
