# Database

## Connection

- **Driver**: MySQL (production), SQLite (testing)
- **Database**: `crm_app_db`
- **Host**: 127.0.0.1:3306

## Schema

132 migration files. All tables use InnoDB engine with UTF-8 charset.

## Tables & Relationships

### Core Auth & User Management

| Table | Key Columns | FK Relationships |
|-------|-------------|-----------------|
| `users` | account_id, name, email, phone, password, otp, otp_expires_at, image_path, fcm_token, device_type, status | `account_id` -> accounts |
| `personal_access_tokens` | tokenable_id, name, token, abilities | `tokenable_id` -> users (Sanctum) |
| `user_profiles` | user_id, address, city, state, zipcode, country, dob, gender, bank_name, account_no, ifsc, pan_no, aadhar_no, emergency_contact, blood_group, marital_status | `user_id` -> users |
| `user_documents` | user_id, name, file_path | `user_id` -> users |
| `user_addresses` | user_id, address_type, address | `user_id` -> users |
| `user_assets` | user_id, asset_name, asset_type, assigned_at, returned_at | `user_id` -> users |
| `user_working_days` | user_id, day_of_week, is_weekoff | `user_id` -> users |
| `user_mappings` | (purpose unknown) | - |
| `user_app_settings` | user_id (unique), fcm_token, web_fcm_token, current_app_version, linked_device_mpin, total_linked_devices_count, is_acknowledge_policies, app_mpin | `user_id` -> users. `linked_device_mpin` (5-digit, bcrypt-hashed) gates linked-device signout; `app_mpin` (6-digit, `encrypted` cast — reversible) is the app-level MPIN, set via `AppSettingsController@setMpin` and verified via `AppSettingsController@matchMpin` — see `docs/api.md` |

### Accounts

| Table | Key Columns | Notes |
|-------|-------------|-------|
| `accounts` | name, website, logo, email_body, user_limit, valid_until, year_type, status | Tenant boundaries |
| `account_documents` | account_id, created_by, name, file_path | File storage for accounts |

### Leads

| Table | Key Columns | FK/Notes |
|-------|-------------|----------|
| `companies` | name, source, website, industry, state, city, address, logo, created_by, credit_limit, credit_duration_days | B2B organization a lead belongs to; `hasMany(Lead)` |
| `leads` | company_id, name, email, country_code, phone (nullable, unique), source, designation, priority, status, is_interest, is_call_back, is_primary, cnvt_to_verified_at, cnvt_to_client_at, created_by, assigned_to | `company_id` -> companies, `created_by`/`assigned_to` -> users. `phone` is nullable so the importer can free a number held by a soft-deleted lead (nulls it + adds a `lead_notes` entry) rather than purge that FK-referenced lead |
| `lead_phones` | lead_id, country_code, phone, is_primary | Multiple phones per lead |
| `lead_emails` | lead_id, email, is_primary | Multiple emails per lead |
| `lead_follow_ups` | lead_id, created_by, remarks, follow_up_date | `lead_id` -> leads |
| `lead_notes` | lead_id, created_by, note | `lead_id` -> leads |
| `lead_call_backs` | lead_id, created_by, date_time, remark | `lead_id` -> leads |
| `lead_services` | lead_id, product_id | Many-to-many between leads and products |
| `lead_groups` | company_id, name, description, created_by | Reusable, fixed-snapshot saved audiences (built from explicit lead IDs, not a live filter) — see [campaign-management.md §1.5](campaign-management.md#15-lead-groups--reusable-saved-audiences-built-in-the-leads-module) |
| `lead_group_leads` | lead_group_id, lead_id | Many-to-many pivot, `unique(lead_group_id, lead_id)` |
| `lead_import_reports` | imported_by, account_id, file_name, total_rows, total_imported, total_failed, total_skipped, total_normalized, row_counts (JSON), column_mapping (JSON), field_rules (JSON), created_by_distribution (JSON), assigned_to_distribution (JSON), errors (JSON), skips (JSON), normalization_warnings (JSON) | Persisted audit of each `lead/import/with_mapping` run: summary counts + full per-row error/skip/normalization detail. `imported_by` -> users; `account_id` denormalized for per-tenant querying |

### Meetings

| Table | Key Columns | Notes |
|-------|-------------|-------|
| `meetings` | lead_id, created_by, description, meeting_date, is_interest, is_arrived, arrived_at, is_started, started_at, start_lat/long, is_completed, completed_at, end_lat/long, is_cancelled, cancelled_at, cancel_reason, otp, otp_expires_at, is_deleted | Status flags as unsignedTinyInt |
| `meeting_schedules` | meeting_id, schedule_date | Multiple reschedules |
| `meeting_users` | meeting_id, user_id, is_active | Tracks active user |
| `meeting_products` | meeting_id, product_id | Products discussed |
| `meeting_companions` | meeting_id, user_id | Companion employees |

### Demos

| Table | Key Columns | Notes |
|-------|-------------|-------|
| `demos` | google_event_id, lead_id, created_by, meeting_link, description, is_scheduled/started/completed/cancelled, start_date | Google Calendar integration |
| `demo_assignees` | demo_id, user_id | Multiple assignees |
| `demo_products` | demo_id, product_id | Products in demo |
| `demo_schedules` | demo_id, user_id, schedule_date, is_current | Reschedule history |

### Sales & Finance

| Table | Key Columns | Notes |
|-------|-------------|-------|
| `lead_sales` | lead_id, product_id, created_by, assigned_to, sale_by, sale_date, price, quantity, total, remarks, incentive, incentive_slab_id, incentive_percentage, incentive_applied_at, incentive_locked, approval_status, approved_by, approved_at, payment_status, is_bill_raised | Decimal(4) for price/quantity/total. `incentive_applied_at` is set when incentive is auto-applied on becoming fully paid and cleared if it drops back out of `paid`; `incentive_locked` freezes incentive fields once manually overridden |
| `sale_incentive_slabs` | product_id, user_id, min_quantity, max_quantity, min_price, max_price, incentive_percentage, start_date, end_date, created_by, status, is_deleted | Per-user incentive rules for a product; `user_id` is required (no product-wide/all-users slab). `start_date`/`end_date` are nullable and always set together — when present, the slab is a "special occasion" slab only eligible while a sale's `sale_date` falls in that window, and it outranks any matching dateless slab for that period |
| `sale_payments` | lead_sale_id, amount, payment_date, payment_type_id, reference_no, is_deleted | Payment tracking |
| `payment_types` | name, description | Cash, Bank, Card, etc. |
| `payroll_payments` | user_id, month, year, base_salary, total_addition, total_deduction, total_payable | Monthly payroll |
| `payment_additions` | payment_id, reason, amount | Bonuses/reimbursements |
| `payment_deductions` | payment_id, reason, days, amount | Penalties/absences |

### Quotations

| Table | Key Columns | Notes |
|-------|-------------|-------|
| `quotations` | lead_id, created_by, total_amount, status, template_id, is_draft | - |
| `quotation_details` | quotation_id, product_id | Links a product to a quotation; line-item pricing lives in `quotation_product_details` |
| `quotation_product_details` | quotation_detail_id, description, price, quantity, unit, total | Line items; `price`/`quantity`/`total` are Decimal(4), matching `lead_sales` precision |
| `quotation_templates` | title, content, created_by | Reusable templates |
| `quotation_terms` | quotation_id, terms | Terms & conditions |
| `quotation_emails` | quotation_id, from_email, to_emails, subject, body, sent_at | Email history |

### Tasks & Projects

| Table | Key Columns | Notes |
|-------|-------------|-------|
| `tasks` | lead_id, project_id, project_ticket_id, created_by, description, start_date, due_date, priority, type, interval, is_completed, completed_at, has_recurred, stop_recurrence | No single assigned_to column; assignees live in `task_assignees` |
| `task_assignees` | task_id, user_id | Multiple assignees per task (same pattern as `demo_assignees`/`project_assignees`) |
| `task_products` | task_id, product_id | Products linked to tasks |
| `task_tags` | task_id, tag_id | Tags on tasks |
| `task_subtasks` | task_id, created_by, assigned_to, title, due_date, position, carry_forward_on_recurrence, is_completed, completed_at | Monday.com-style checklist/subtask items under a task; single assignee (its own assigned_to, unrelated to task_assignees), `position` controls drag-reorder, `carry_forward_on_recurrence` (default false) controls whether the subtask is regenerated on the next occurrence of a recurring task |
| `projects` | lead_id, created_by, team_leader, project_name, description, project_time, project_cost, start/end_date, priority, status | - |
| `project_assignees` | project_id, user_id | - |
| `project_tickets` | project_id, created_by, title, description, priority, status | Support tickets |
| `project_tags` | project_id, tag_id | - |
| `project_media_files` | project_id, uploaded_by, file_path | - |
| `project_chats` | project_id, user_id, message | - |
| `project_chat_files` | project_chat_id, file_path | - |

### HR & Attendance

| Table | Key Columns | Notes |
|-------|-------------|-------|
| `shifts` | name, start_time, end_time, late_threshold, halfday_threshold | - |
| `attendances` | user_id, attendance_date, check_in/out_time, check_in/out_lat/lng, check_in/out_image, is_present/absent/holiday/weekoff/halfday/late, shift_id, leave_type_id | Boolean flags |
| `location_tracking` | user_id, attendance_id, latitude, longitude, accuracy, recorded_at | GPS during attendance |
| `leave_types` | name, default_days, is_active | - |
| `leave_entitlements` | user_id, leave_type_id, start/end_date, total_days, used_days | - |
| `leave_applications` | user_id, applied_to, leave_type_id, start/end_date, total_days, reason, status | - |
| `leave_approvals` | leave_application_id, approver_id, status, approver_remarks, actioned_at | - |
| `holidays` | created_by, from_date, to_date, name | Simple date-range holidays |

### Notifications & Communication

| Table | Key Columns | Notes |
|-------|-------------|-------|
| `push_notifications` | user_id, title, body, data, type, status (pending/sent/failed) | FCM notifications |
| `scheduled_notification_logs` | notifiable_type, notifiable_id, user_id, reminder_type, activity_at, reminder_at, status, sent_at, failed_at | Tracks notification delivery. `reminder_type` values include `follow_up`/`callback`/`meeting`/`demo`/`meeting_*` (activity reminders) and `payment_reminder` (unpaid/partial sale reminders, see business-rules.md § Payment Reminders). Unique on `(user_id, reminder_type, notifiable_type, notifiable_id, reminder_at)` — used for once-per-day dedup |
| `notification_integrations` | operation, is_active | Toggle features |
| `sms_credentials` | created_by, api_key, sender_id, template_id | SMS provider config |
| `sms_templates` | - | SMS templates |
| `waba_credentials` | created_by, api_key, phone_number_id, waba_business_id | WhatsApp Business API. `waba_business_id` used by Journey WhatsApp components (see Marketing & Campaigns below) |
| `waba_templates` | - | WhatsApp templates |
| `rcs_credentials` | - | RCS (Rich Communication Services) |
| `rcs_templates` / `rcs_template_cards` / `rcs_card_suggestions` | - | RCS message templates |
| `ivr_credentials` | created_by, api_key | IVR system credentials |
| `smtp_credentials` | - | SMTP config for sending emails |
| `phone_call_logs` | user_id, call_timestamp, name, number, duration, unix_timestamp, type | Call logs from device |
| `incoming_call_reports` | action, caller, call_time, channel, call_status, circle, operator, dtmfinputkeys | IVR incoming |
| `outgoing_call_reports` | requestid, agent/caller number/status/duration, callrecording | IVR outgoing |

### Meta (Facebook/Instagram) Lead Ads

Single-tenant app, so no organization/tenant column on any of these — `meta_connections` is a
single active row (matching the `campaign_api_credentials`/`waba_credentials` pattern). `ad_leads`,
`ad_field_mappings`, and `ad_webhook_logs` carry a `provider` column (default `'meta'`) so a future
non-Meta ad-lead source can reuse them without a rename; `meta_connections`/`meta_pages` stay
Meta-specific since Page-level OAuth is a Meta-only concept.

| Table | Key Columns | FK/Notes |
|-------|-------------|----------|
| `meta_connections` | meta_user_id, app_id, app_secret, verify_token, access_token, token_expires_at, status (active/inactive/expired), created_by | `app_secret`/`access_token` use Laravel's `encrypted` cast. Single row expected; `app_secret` is used both for `X-Hub-Signature-256` verification and (later) Graph API OAuth |
| `meta_pages` | meta_connection_id, page_id (unique), page_name, page_access_token, status | `meta_connection_id` -> meta_connections. `page_access_token` uses `encrypted` cast |
| `ad_leads` | provider (default 'meta'), provider_lead_id, page_id, form_id, ad_id, adset_id, campaign_id, name, email, phone, field_data (JSON), raw_data (JSON), lead_created_at, status (received/processed/failed), error_message, crm_lead_id | `crm_lead_id` -> leads, nullable. `unique(provider, provider_lead_id)` is the idempotency key for duplicate webhook redelivery. `raw_data` retains the full Graph response so leads can be reprocessed if field mappings change |
| `ad_field_mappings` | provider (default 'meta'), form_id, source_field, crm_field | `unique(provider, form_id, source_field)`. Maps a Meta lead form's raw field name (e.g. `full_name`) to a CRM field (e.g. `name`) |
| `ad_webhook_logs` | provider (default 'meta'), page_id, external_id, payload (JSON), status (received/processed/failed), error_message, attempts, processed_at | Raw webhook delivery log for debugging/auditing, independent of `ad_leads` |

### Marketing & Campaigns

Full detail (endpoints, request/response shapes, per-channel quirks, variable-mapping rules) in
[campaign-management.md](campaign-management.md).

| Table | Key Columns | Notes |
|-------|-------------|-------|
| `campaigns` | name, strategy (failover/parallel), journey_name, status (draft/scheduled/running/paused/completed/failed), scheduled_at/started_at/completed_at, created_by, total_recipients, sent_count, failed_count, delivered_count, error_message | `journey_name` is the provider-side Journey registered for this campaign at launch (set once, idempotent) |
| `campaign_channels` | campaign_id, channel_type (sms/rcs/whatsapp), priority, template_id (provider ID string, not local row id), sender_id, credentials_config (JSON), is_enabled, fallback_time | `sender_id`/`credentials_config` carry per-send-critical fields (DLT sender, RCS AGENTID, WhatsApp header media links) that are **not** stored on the template records. `fallback_time` (**minutes** — must be one of `1, 15, 30, 60, 120, 240`, confirmed via Bluewaves' own validation error) is how long the Journey waits on this channel before falling back to the next one in priority order |
| `campaign_api_credentials` | api_key, created_by | Single-row Bearer token for the Journey API (`journey/create` + `channel: "Journey"` sends) — distinct from `sms_credentials`/`rcs_credentials`/`waba_credentials` since a Journey call spans multiple channels |
| `campaign_audiences` | campaign_id, lead_group_id, source_type (lead/excel/test/both), excel_file_path, excel_original_name, excel_imported_count, lead_imported_count, test_numbers (JSON), test_imported_count | `lead_group_id` is the only lead-based audience source; `test_numbers` is the raw manually-typed phone list for `source_type = 'test'` |
| `campaign_recipients` | campaign_id, source_type (lead/excel/test), lead_id, phone, email, country_code, recipient_data (JSON), variables (JSON), status (pending/processing/sent/delivered/failed), error_message, processed_at, delivered_at | For lead-sourced recipients, `recipient_data` includes the lead's own fields plus its Company's fields prefixed `company_*`. For test-sourced recipients, `recipient_data` is just `{"phone": "<number>"}` |
| `campaign_logs` | campaign_id, campaign_recipient_id, campaign_channel_id (nullable), channel_type (sms/rcs/whatsapp/journey), message_id, status, request_payload, response_data, error_message, retry_count, queued_at/sent_at/delivered_at/failed_at | One row per recipient Journey send attempt (`channel_type: "journey"`, `campaign_channel_id: null`) since one call now covers every enabled channel; older rows may still be per-channel |
| `campaign_variable_mappings` | campaign_id, channel_type (nullable), template_variable, data_source_column (nullable), default_value, is_required | `channel_type: null` = global fallback mapping; a channel-specific mapping always wins over it for the same `template_variable`. `data_source_column` is nullable — a default-value-only mapping (no real data column, e.g. for a test audience) omits it |

### Module Settings & Configuration

| Table | Key Columns | Notes |
|-------|-------------|-------|
| `meeting_settings` | key (unique), value (text, nullable), type (integer/string/boolean/float), label, description | Meeting behavior settings (overdue buffer, arrival window, OTP expiry). Seeded from `App\Support\MeetingSettingCatalog.php` via `MeetingSettingSeeder`; read/written through `App\Models\MeetingSetting` (`MeetingSetting::get()`/`::set()`) and exposed via `meeting/setting/*` endpoints |
| `credit_settings` | key (unique), value (text, nullable), type, label, description | Credit behavior settings (overdue window days, default credit duration days). Seeded from `App\Support\CreditSettingCatalog.php` via `CreditSettingSeeder`; read/written through `App\Models\CreditSetting` and exposed via `credit/setting/*` endpoints |
| `notification_settings` | key (unique), value (text, nullable), type, label, description | Notification behavior settings (upcoming-activity reminder windows for follow-up/callback/meeting/demo). Seeded from `App\Support\NotificationSettingCatalog.php` via `NotificationSettingSeeder`; read/written through `App\Models\NotificationSetting` and exposed via `notification/setting/*` endpoints |
| `quotation_settings` | cc_super_admin (bool), cc_quotation_creator (bool), cc_lead_creator (bool), cc_lead_assignee (bool) | Quotation email CC behavior toggles. Singleton row, read/written through `App\Models\QuotationSetting` and exposed via `quotation/setting/*` endpoints |

### Activity & Audit

| Table | Key Columns | Notes |
|-------|-------------|-------|
| `activity_log` | log_name, description, subject_type/subject_id, causer_type/causer_id, properties | Spatie Activitylog |
| `event_logs` | task_id, meeting_id, demo_id, created_by, log, duration_seconds | Time tracking on activities |

### Permissions

| Table | Notes |
|-------|-------|
| `permissions` | Spatie: name, guard_name, module, sub_module, action |
| `roles` | Spatie: name, guard_name |
| `model_has_permissions` | Spatie: morph pivot |
| `model_has_roles` | Spatie: morph pivot |
| `role_has_permissions` | Spatie: pivot |
| `model_permissions` | Custom: name, module, sub_module, action, guard_name, description, is_active |

### Other

| Table | Purpose |
|-------|---------|
| `products` | Product catalog (name, description, exclude_from_sales) |
| `tags` | Tagging system (name) |
| `visits` | Lead visit tracking |
| `app_versions` | Mobile app version management |
| `google_access_tokens` | Google API tokens |
| `google_tokens` | Alternative Google token storage |
| `sessions` | Database session driver |
| `cache` / `cache_locks` | Database cache store |
| `jobs` / `job_batches` / `failed_jobs` | Queue system |

### Expense Management

| Table | Key Columns | Notes |
|-------|-------------|-------|
| `expense_allocations` | user_id, amount, allocated_by, allocated_at, note | Budget allocated to a user |
| `expense_reimbursements` | holder_user_id, beneficiary_user_id (nullable), requested_by, title, amount, expense_date (nullable, chosen date expense was incurred), type (direct/request), status (pending/approved/rejected), actioned_by, actioned_at, remarks, rejection_reason | Reimbursement records; `beneficiary_user_id` optional for direct disbursements |
| `expense_reimbursement_docs` | expense_reimbursement_id, file_path | Supporting documents |
| `expense_ledger_entries` | user_id, type (allocation/reimbursement/deduction), amount, balance_after, reference_type, reference_id | Double-entry style ledger |

### Incentives

| Table | Key Columns | Notes |
|-------|-------------|-------|
| `incentive_settings` | key, value, type, label, description | Global incentive config |
| `sale_settings` | key, value, type, label, description | Sale behavior config |
| `monthly_incentive_brackets` | min_amount, max_amount, percentage, created_by | Tiered incentive brackets |
| `monthly_incentive_eligibilities` | min_sales_count, min_sales_amount, created_by | Eligibility criteria |
| `monthly_incentives` | user_id, month, year, status, total_sales_amount, total_incentive_amount | Monthly summary |
| `monthly_incentive_items` | monthly_incentive_id, lead_sale_id, sale_amount, incentive_amount | Per-sale breakdown |
| `monthly_incentive_item_eligibility_sales` | monthly_incentive_item_id, lead_sale_id | Eligibility linkage |
| `monthly_incentive_item_brackets` | monthly_incentive_item_id, bracket_id, amount | Bracket application |
| `monthly_incentive_item_bracket_sales` | monthly_incentive_item_bracket_id, lead_sale_id | Bracket-sale linkage |

### Helpdesk & Policies

| Table | Key Columns | Notes |
|-------|-------------|-------|
| `helpdesk_tickets` | user_id, subject, description, priority, status, assigned_to | Support tickets |
| `account_policies` | account_id, title, content, is_active | Account-level policy documents |

### Location & Devices

| Table | Key Columns | Notes |
|-------|-------------|-------|
| `location_fencings` | name, latitude, longitude, radius, created_by | Geofence definitions |
| `linked_devices` | user_id, device_id, device_name, last_active_at | Multi-device management |

### Additional Tables

| Table | Key Columns | Notes |
|-------|-------------|-------|
| `task_attachments` | task_id, file_path, uploaded_by | Task file attachments |
| `payment_taxes` | name, percentage, is_active | Tax types for payments |
| `lead_ownership_histories` | lead_id, from_user_id, to_user_id, transferred_by, transferred_at | Lead transfer audit |
| `lead_sales_approval_status` | lead_sale_id, status, actioned_by, actioned_at, remarks | Sale approval workflow |
| `lead_credit_exceeded_requests` | lead_id, requested_by, status, amount | Credit limit override requests |
| `project_chat_mentions` | project_chat_id, user_id | Chat @mentions |

## Key Indexes

- `lead_phones`: Unique on `phone`, composite on `(lead_id, is_primary)`
- `lead_emails`: Unique on `email`, composite on `(lead_id, is_primary)`
- `personal_access_tokens`: Unique on `token`
- `activity_log`: Composite on `(subject_id, subject_type)`
- `attendances`: Likely composite on `(user_id, attendance_date)`

## Important Business Columns

- **leads.status**: Raw -> Verified -> Client conversion pipeline
- **leads.priority**: Lead priority level
- **meetings.*_at**: Tracked status with timestamps (arrived, started, completed, cancelled)
- **meetings.otp / otp_expires_at**: 4-digit OTP, 5-min expiry
- **users.otp / lead_users.otp**: `varchar(255)`, stores a `Hash::make()` hash of the 4-digit login OTP (not the raw digits) — see [business-rules.md](business-rules.md#login-otp-user--client-portal-auth)
- **lead_sales.approval_status**: Pending -> Approved/Rejected workflow
- **lead_sales.payment_status**: unpaid / partial / paid (auto-synced via `syncPaymentStatus()`)
- **tasks.is_recurring / recurring_type / recurring_interval**: daily/weekly/fortnightly/monthly/quarterly/half-yearly/yearly
- **attendances**: Boolean flags (is_present, is_absent, is_holiday, is_weekoff, is_halfday, is_late) - mutually exclusive logic

## Migration Conventions

- Namespaced by domain (permissions table, spatie package)
- Timestamp prefixes like `0001_01_01_*` for core tables
- Some migrations in separate batches for refactoring (e.g., `2026_04_07_*` for meetings refactor, `2026_06_04_*` for lead contacts normalization)
- Soft deletes used extensively (`deleted_at` column)
- No explicit enum types - statuses stored as strings or integer flags
- Foreign keys use `unsignedBigInteger` + `->references('id')->on('table')`

## Seeders

- `DatabaseSeeder.php` - Base seeder
- `PermissionSeeder.php` - Seeds all permissions from `PermissionCatalog`
- `MeetingSettingSeeder.php` / `CreditSettingSeeder.php` / `NotificationSettingSeeder.php` - Seed module behavior settings from their catalogs (idempotent upsert)