# Database

## Connection

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

## Schema

143 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, 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 (nullable), assigned_to (nullable), is_inactive (bool, indexed), inactive_at (nullable), previous_created_by (nullable), previous_assigned_to (nullable) | `company_id` -> companies, `created_by`/`assigned_to` -> users (both nullable so a released lead can be detached from its owners). `is_inactive`/`inactive_at`/`previous_created_by`/`previous_assigned_to` support the inactive-lead pool (Approach C): the daily `ReleaseInactiveLeadsJob` flags raw/verified leads with no activity for longer than `lead_settings.inactivity_threshold_days`, snapshots the previous owners, and nulls `created_by`/`assigned_to`. `previous_*` -> users (nullOnDelete). `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_exclusion_reason, 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. `incentive_exclusion_reason` is the per-sale incentive audit: `null` when incentive was calculated (see `incentive_slab_id` for the source slab), otherwise the reason none applied — `not_paid`, `blackout`, `target_not_met`, `no_matching_slab`, or `locked` |
| `sale_incentive_slabs` | product_id, user_id, min_quantity, max_quantity, min_price, max_price, incentive_percentage, priority, 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). `priority` (bool) marks a slab that outranks all non-priority matches for a sale; create/update rejects a second priority slab that overlaps an existing one (same user+product) on every shared, constrained dimension (quantity/price/date). `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` | term, created_by, is_deleted | Quotation terms & conditions (org-wide) |
| `purchase_order_terms` | term, created_by, is_deleted | Purchase order terms & conditions (org-wide, separate from quotation terms) |
| `email_body_templates` | name, body, is_default, created_by, is_deleted | Reusable email body templates chosen at quotation/PO creation (copied into the record's `email_body`); `is_default` supplies the fallback body |
| `quotation_emails` | quotation_id, from_email, to_emails, subject, body, sent_at | Email history |
| `sale_orders` | so_number, document_no, so_date, billing_from_* (seller: name/address/urn/gstin/gst_state(+code)/email/pan), billing_to_* (buyer) + place_of_supply, taxable_amount, cgst, sgst, igst, round_off, tax_total, grand_total, amount_in_words, tax_amount_in_words, declaration, jurisdiction, bank_* + secondary_bank_*, created_by, status, is_deleted | GST Tax Invoice header. Column set is invoice-driven (not copied from `purchase_orders`); two parties only (seller/buyer), no shipping/freight/charges. Both in-words fields + `so_number` (SO-YYYY-####) derived on the model. Decimal(15,4). |
| `sale_order_items` | sale_order_id, sl_no, description, hsn_sac, gst_rate, quantity(nullable), rate(nullable), per(nullable), amount, cgst_rate, cgst_amount, sgst_rate, sgst_amount, total_tax_amount | Invoice lines; qty/rate/per nullable for amount-only service lines. GST split per line; the PDF's HSN tax-summary table is computed by grouping these rows. |
| `sale_order_emails` | sale_order_id, recipient_emails, subject, attachment_paths, pdf_path, sent_by, status, sent_at, is_deleted | Sale order (Tax Invoice) email history |
| `sale_order_terms` | term, created_by, is_deleted | Sale order terms & conditions (org-wide, separate from quotation/PO terms) |

### 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 |
| `onboarding_settings` | from_email, from_name, ticket_applied_to (nullable), default_to_emails (JSON), default_cc_emails (JSON), ticket_title_template | Onboarding email FROM + default recipients and the super admin the generated helpdesk ticket is applied to. Singleton row, read/written through `App\Models\OnboardingSetting` and exposed via `onboarding/setting/*` endpoints. See the Onboarding section |
| `lead_settings` | is_enabled (bool, default true), inactivity_threshold_days (uint, default 30) | Inactive-lead sweep configuration. Singleton row, read/written through `App\Models\LeadSetting` (`current()`/`clearCache()`) and exposed via `lead/setting/*` endpoints. Drives the daily `ReleaseInactiveLeadsJob` |

### 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, uncovered_amount (overspend debt not yet backed by an allocation), payment_type_id (nullable FK → payment_types), expense_date (nullable, chosen date expense was incurred), type (direct/request/company), status (pending/approved/rejected), actioned_by, actioned_at, remarks, rejection_reason | Reimbursement + company-expense records. `type=company` rows are outgoing company costs with no beneficiary that always deduct from the holder's allocation. `payment_type_id` drives whether a reimbursement deducts (see `expense_settings`). Overspend is allowed: FIFO covers what it can, the shortfall is stored in `uncovered_amount` and back-filled automatically when a new allocation is created (invariant: `amount = SUM(ledger amount_consumed) + uncovered_amount`) |
| `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 |
| `expense_settings` | deducting_payment_type_ids (json array of payment_type IDs) | Singleton module settings. A reimbursement deducts from the holder's allocation only when its `payment_type_id` is in this set; behavior-based, no payment-type name is special-cased |
| `expense_allocation_tags` | expense_allocation_id, tag_id | Pivot: many-to-many between allocations and shared `tags`. Unique (allocation, tag) |
| `expense_reimbursement_tags` | expense_reimbursement_id, tag_id | Pivot: many-to-many between reimbursements/company expenses and shared `tags`. Unique (reimbursement, tag). Powers the report `by_tag` chart and `tag_id` filter |

### 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 |
| `incentive_blackout_periods` | user_id, start_date, end_date, reason, status, created_by, is_deleted | Per-user, all-products blackout windows; sales with `sale_date` inside a period earn no incentive |
| `lead_sale_incentives` | lead_sale_id, user_id, role, share, full_share_amount, incentive, incentive_base, consumed_amount, incentive_slab_id, incentive_percentage, incentive_exclusion_reason, incentive_status | Per-participant incentive for a sale. A sale with a distinct creator and assignee yields two rows (one each) split by `creator_share_percentage`; a sole-owner sale yields one creator row with `share = 1.0`. Each row is independently gated by that participant's sale target. Incentive is earned on the **FIFO excess** above the target: `full_share_amount` = `total * share`, `consumed_amount` = portion filling the target (earns nothing), `incentive_base` = earning excess, `incentive` = slab% * `incentive_base`. `incentive_status` is `incentivized` / `partially_incentivized` / `target_consumed` for a participating row, or null when excluded (see `incentive_exclusion_reason`: `not_paid`, `blackout`, `target_not_met`, `no_matching_slab`). `lead_sales.incentive` is the sum of these rows |
| `incentive_payouts` | user_id, period_type, year_mode, period_year, period_month, period_quarter, period_half, period_starts_at, period_ends_at, settle_after_period, lump_sum_amount, allocated_amount, credit_amount, remarks, paid_by, paid_at, is_deleted, deleted_by, deleted_at | One admin payout: a lump sum paid to one user for a resolved period, allocated **FIFO** (oldest earned incentive line first) against that user's settled, unpaid `lead_sale_incentives`. `allocated_amount` is what reached incentive lines; `credit_amount` is the unallocated overpay remainder. When `settle_after_period` is true, overpay spills FIFO into the user's eligible lines dated after `period_ends_at`. Soft delete (`is_deleted`) excludes the payout's allocations from every paid-to-date computation, restoring payability |
| `incentive_payout_allocations` | incentive_payout_id, lead_sale_incentive_id, lead_sale_id, user_id, amount, in_period | FIFO breakdown of a payout: the portion (`amount`) of a single earned incentive line a payout paid. A line can be split across multiple allocations/payouts (partial payment); a line's paid-to-date is the sum of its allocations from non-deleted payouts, and its remaining payable is `incentive - paid_to_date`. `in_period` is true when the paid line falls inside the payout's selected period, false when it was spilled forward (an advance against a later sale). Links payout ↔ incentive line ↔ sale for two-way traceability |

> **Removed:** the monthly-incentive bracket/eligibility engine (`monthly_incentives`, `monthly_incentive_items`, `monthly_incentive_brackets`, `monthly_incentive_eligibilities`, `monthly_incentive_item_brackets`, `monthly_incentive_item_bracket_sales`, `monthly_incentive_item_eligibility_sales`) was dropped by migration `2026_10_13_140000_drop_monthly_incentive_tables`. Incentive is now a per-sale value stored on `lead_sales.incentive` (gated by sale targets), and incentive reporting is computed read-only from it via `SaleIncentiveReportController`.

### 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 |

### Onboarding

| Table | Key Columns | Notes |
|-------|-------------|-------|
| `product_onboarding_fields` | product_id, label, description (nullable), key, type (text/textarea/number/email/url/date/dropdown/file), is_required, options (JSON, dropdown only), lead_map_path (nullable), sort_order | Per-product onboarding form field definitions. `description` is optional help text shown with the field. `lead_map_path` maps to a lead attribute for display-only pre-fill (catalog in `App\Support\OnboardingLeadMap`). `product_id` -> products. Read/written via `product/onboarding/field/*` endpoints |
| `onboardings` | product_id, lead_id, created_by, status (pending/completed), helpdesk_ticket_id (nullable, legacy), share_token (unique, nullable), share_expires_at (nullable), email_status (sent/failed, nullable), email_error (nullable), email_recipients (JSON {to,cc}, nullable), email_sent_at (nullable), submitted_at, completed_at | One onboarding instance per product+lead. Created as a **pending** shell with a public `share_token` (no values, no ticket); the lead/client fills + submits the form via the tokenized public link (`onboarding/public/*`), which persists values, sends the email, flips status to `completed`, and nulls `share_token`. `share_expires_at` bounds the link's validity (`ONBOARDING_SHARE_LINK_EXPIRY_HOURS`). The `email_*` columns record the post-submit email delivery outcome (non-fatal on failure). `helpdesk_ticket_id` -> helpdesk_tickets is retained for legacy records only (closing that ticket also completes the onboarding). Exposed via `onboarding/*` endpoints |
| `onboarding_field_values` | onboarding_id, product_onboarding_field_id, value (text, nullable), file_path (nullable) | Submitted values. File-type fields store an uploaded path on the `public` disk; other types store `value` |
| `onboarding_settings` | from_email, from_name, ticket_applied_to (nullable, legacy), default_to_emails (JSON), default_cc_emails (JSON), ticket_title_template (legacy) | Singleton settings. Configures the fixed FROM for onboarding emails and the default recipient lists used when the public form is submitted. `ticket_applied_to`/`ticket_title_template` are legacy (the helpdesk-ticket flow was replaced by shareable links). Read/written through `App\Models\OnboardingSetting` (`current()`/`clearCache()`) via `onboarding/setting/*` endpoints |

### 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, field (`created_by`/`assigned_to`), old_user_id, new_user_id, changed_by (nullable), action, lead_status | Lead ownership audit. `action` values include `transfer`, `bulk_transfer`, `share`, `claim`, `released` (inactive-lead sweep) and `reclaimed` (pool reclaim). `changed_by` is nullable so system-performed releases (no human actor) can be recorded |
| `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)