# Database

## Connection

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

## Schema

119 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) | - |

### Accounts & Companies

| 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 |
| `companies` | name, source, website, industry, state, city, address, logo, created_by | Lead's company |

### Leads

| Table | Key Columns | FK/Notes |
|-------|-------------|----------|
| `leads` | company_id, name, email, country_code, phone, 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 |
| `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)` |

### 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, approval_status, approved_by, approved_at, payment_status, is_bill_raised | Decimal(4) for price/quantity/total |
| `sale_incentive_slabs` | product_id, user_id, min_quantity, max_quantity, min_price, max_price, incentive_percentage, created_by, status, is_deleted | Per-user incentive rules for a product; `user_id` is required (no product-wide/all-users slab) |
| `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, qty, price, total | Line items |
| `quotation_product_details` | quotation_id, product_id, description, qty, unit_price, total | Alternative line items |
| `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, created_by, assigned_by, assigned_to, description, start_date, end_date, priority, status, stage, is_completed, completed_at, is_recurring, recurring_type, recurring_interval | Supports multiple assignees via task_assignees (not found, may use JSON) |
| `task_products` | task_id, product_id | Products linked to tasks |
| `task_tags` | task_id, tag_id | Tags on tasks |
| `task_queries` | task_id, created_by, query, response, is_resolved | Q&A on tasks |
| `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 |

### 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/both), excel_file_path, excel_original_name, excel_imported_count, lead_imported_count | `lead_group_id` is the only lead-based audience source |
| `campaign_recipients` | campaign_id, source_type (lead/excel), 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_*` |
| `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, default_value, is_required | `channel_type: null` = global fallback mapping; a channel-specific mapping always wins over it for the same `template_variable` |

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

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