# LeadPilot — Authoritative Table-by-Table Schema Specification

**Document Version:** 1.0.0 (Phase 5 Database Architecture Lock)  
**Status:** Approved & Formally Recorded  
**Engine:** MySQL 8.0+ / MariaDB 10.6+ InnoDB (`utf8mb4_unicode_ci`)  

---

## 1. Identity & Multi-Tenancy Tables

### 1.1 `users`
```sql
CREATE TABLE `users` (
    `id` CHAR(36) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `avatar_url` VARCHAR(512) NULL,
    `email_verified_at` TIMESTAMP NULL,
    `status` VARCHAR(32) NOT NULL DEFAULT 'active', -- active, suspended, invited
    `last_login_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### 1.2 `workspaces`
```sql
CREATE TABLE `workspaces` (
    `id` CHAR(36) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `slug` VARCHAR(255) NOT NULL,
    `currency` VARCHAR(3) NOT NULL DEFAULT 'USD',
    `timezone` VARCHAR(64) NOT NULL DEFAULT 'UTC',
    `locale` VARCHAR(16) NOT NULL DEFAULT 'en',
    `business_description` TEXT NULL, -- Context for AI qualification
    `status` VARCHAR(32) NOT NULL DEFAULT 'active', -- active, suspended, archived
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `workspaces_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### 1.3 `workspace_members`
```sql
CREATE TABLE `workspace_members` (
    `id` CHAR(36) NOT NULL,
    `workspace_id` CHAR(36) NOT NULL,
    `user_id` CHAR(36) NOT NULL,
    `role` VARCHAR(32) NOT NULL DEFAULT 'member', -- owner, admin, member
    `status` VARCHAR(32) NOT NULL DEFAULT 'active', -- active, invited, suspended
    `invited_at` TIMESTAMP NULL,
    `joined_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `ws_members_unique` (`workspace_id`, `user_id`),
    CONSTRAINT `fk_ws_members_ws` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ws_members_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

---

## 2. Pipeline Management Tables

### 2.1 `pipelines`
```sql
CREATE TABLE `pipelines` (
    `id` CHAR(36) NOT NULL,
    `workspace_id` CHAR(36) NOT NULL,
    `name` VARCHAR(255) NOT NULL DEFAULT 'Default Sales Pipeline',
    `is_default` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_pipelines_ws` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### 2.2 `pipeline_stages`
```sql
CREATE TABLE `pipeline_stages` (
    `id` CHAR(36) NOT NULL,
    `workspace_id` CHAR(36) NOT NULL,
    `pipeline_id` CHAR(36) NOT NULL,
    `name` VARCHAR(100) NOT NULL, -- New, Contacted, Qualified, Proposal, Won, Lost
    `slug` VARCHAR(64) NOT NULL, -- new, contacted, qualified, proposal, won, lost
    `position` INT UNSIGNED NOT NULL DEFAULT 0,
    `is_terminal` TINYINT(1) NOT NULL DEFAULT 0, -- 1 for Won and Lost
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `pipeline_stage_pos_unique` (`pipeline_id`, `position`),
    CONSTRAINT `fk_stages_ws` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_stages_pipeline` FOREIGN KEY (`pipeline_id`) REFERENCES `pipelines` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

---

## 3. Lead Ingestion & Lead Core Tables

### 3.1 `lead_sources`
```sql
CREATE TABLE `lead_sources` (
    `id` CHAR(36) NOT NULL,
    `workspace_id` CHAR(36) NOT NULL,
    `name` VARCHAR(255) NOT NULL, -- e.g. "Main Website Contact Form"
    `type` VARCHAR(32) NOT NULL, -- website_form, capture_page, webhook, rest_api, csv_import, manual
    `configuration` JSON NULL, -- Schema key mappings, form parameters
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `leads_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_sources_ws` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### 3.2 `leads` (The Flagship Entity)
```sql
CREATE TABLE `leads` (
    `id` CHAR(36) NOT NULL,
    `workspace_id` CHAR(36) NOT NULL,
    `lead_source_id` CHAR(36) NULL,
    `pipeline_id` CHAR(36) NOT NULL,
    `pipeline_stage_id` CHAR(36) NOT NULL,
    `assigned_user_id` CHAR(36) NULL,
    
    -- Contact Information
    `first_name` VARCHAR(128) NOT NULL,
    `last_name` VARCHAR(128) NULL,
    `email` VARCHAR(255) NULL,
    `normalized_email` VARCHAR(255) NULL, -- lowercase(trim(email)) for deduplication
    `phone` VARCHAR(64) NULL,
    `normalized_phone` VARCHAR(64) NULL, -- E.164 stripped
    `company` VARCHAR(255) NULL,
    `inquiry_text` TEXT NOT NULL,
    
    -- Operational & Financial State
    `status` VARCHAR(32) NOT NULL DEFAULT 'active', -- active, won, lost, archived
    `deal_value` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    `lost_reason` VARCHAR(64) NULL, -- price, competitor, no_response, not_qualified, timing, other
    `lost_notes` TEXT NULL,
    
    -- AI Intelligence (Denormalized for Sub-10ms Sorting)
    `ai_score` SMALLINT UNSIGNED NOT NULL DEFAULT 50, -- 0 to 100
    `temperature` VARCHAR(16) NOT NULL DEFAULT 'WARM', -- HOT, WARM, COLD
    `ai_status` VARCHAR(32) NOT NULL DEFAULT 'pending', -- pending, completed, fallback_rules, failed
    `recommended_action` VARCHAR(255) NULL,
    
    -- Operational Timestamps & SLA Tracking
    `first_contacted_at` TIMESTAMP NULL,
    `first_response_seconds` INT UNSIGNED NULL, -- Duration between created_at & first_contacted_at
    `last_activity_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `next_followup_at` TIMESTAMP NULL,
    `won_at` TIMESTAMP NULL,
    `lost_at` TIMESTAMP NULL,
    
    -- "No Lead Left Behind" Recovery Attributes
    `is_recovered` TINYINT(1) NOT NULL DEFAULT 0,
    `recovered_at` TIMESTAMP NULL,
    `is_possible_duplicate` TINYINT(1) NOT NULL DEFAULT 0,
    `duplicate_of_id` CHAR(36) NULL,
    
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    KEY `idx_leads_ws_status_stage` (`workspace_id`, `status`, `pipeline_stage_id`),
    KEY `idx_leads_ws_temp_score` (`workspace_id`, `temperature`, `ai_score`),
    KEY `idx_leads_ws_next_followup` (`workspace_id`, `next_followup_at`),
    KEY `idx_leads_ws_assigned` (`workspace_id`, `assigned_user_id`),
    KEY `idx_leads_ws_created` (`workspace_id`, `created_at`),
    KEY `idx_leads_normalized_email` (`workspace_id`, `normalized_email`),
    KEY `idx_leads_normalized_phone` (`workspace_id`, `normalized_phone`),
    CONSTRAINT `fk_leads_ws` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_leads_source` FOREIGN KEY (`lead_source_id`) REFERENCES `lead_sources` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_leads_pipeline` FOREIGN KEY (`pipeline_id`) REFERENCES `pipelines` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_leads_stage` FOREIGN KEY (`pipeline_stage_id`) REFERENCES `pipeline_stages` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_leads_assignee` FOREIGN KEY (`assigned_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

---

## 4. Activities, Notes & Tagging Tables

### 4.1 `lead_activities` (Immutable Timeline Log)
```sql
CREATE TABLE `lead_activities` (
    `id` CHAR(36) NOT NULL,
    `workspace_id` CHAR(36) NOT NULL,
    `lead_id` CHAR(36) NOT NULL,
    `user_id` CHAR(36) NULL, -- NULL indicates system action
    `event_type` VARCHAR(64) NOT NULL, -- lead_created, stage_changed, follow_up_completed, ai_scored, note_added, marked_won, marked_lost
    `description` VARCHAR(512) NOT NULL,
    `old_value` VARCHAR(255) NULL,
    `new_value` VARCHAR(255) NULL,
    `metadata` JSON NULL, -- Structured event payload (Zero secrets)
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_activities_lead_created` (`lead_id`, `created_at`),
    CONSTRAINT `fk_activities_ws` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_activities_lead` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_activities_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### 4.2 `lead_notes`
```sql
CREATE TABLE `lead_notes` (
    `id` CHAR(36) NOT NULL,
    `workspace_id` CHAR(36) NOT NULL,
    `lead_id` CHAR(36) NOT NULL,
    `user_id` CHAR(36) NOT NULL,
    `content` TEXT NOT NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_notes_ws` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_notes_lead` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_notes_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### 4.3 `tags` & `lead_tags`
```sql
CREATE TABLE `tags` (
    `id` CHAR(36) NOT NULL,
    `workspace_id` CHAR(36) NOT NULL,
    `name` VARCHAR(64) NOT NULL,
    `color_hex` VARCHAR(7) NOT NULL DEFAULT '#64748B',
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `tags_ws_name_unique` (`workspace_id`, `name`),
    CONSTRAINT `fk_tags_ws` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `lead_tags` (
    `lead_id` CHAR(36) NOT NULL,
    `tag_id` CHAR(36) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`lead_id`, `tag_id`),
    CONSTRAINT `fk_lt_lead` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_lt_tag` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

---

## 5. Follow-Up Engine & Message Templates

### 5.1 `message_templates`
```sql
CREATE TABLE `message_templates` (
    `id` CHAR(36) NOT NULL,
    `workspace_id` CHAR(36) NOT NULL,
    `title` VARCHAR(255) NOT NULL, -- e.g. "Agency Discovery Intro"
    `category` VARCHAR(64) NOT NULL DEFAULT 'general', -- intro, follow_up, proposal_nudge, break_up
    `channel` VARCHAR(32) NOT NULL DEFAULT 'email', -- email, message, whatsapp
    `subject` VARCHAR(255) NULL,
    `body` TEXT NOT NULL, -- Supports merge tags: {{lead.first_name}}, {{workspace.name}}
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_tmpl_ws` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### 5.2 `follow_up_sequences` & `follow_up_sequence_steps`
```sql
CREATE TABLE `follow_up_sequences` (
    `id` CHAR(36) NOT NULL,
    `workspace_id` CHAR(36) NOT NULL,
    `name` VARCHAR(255) NOT NULL, -- e.g. "Default 7-Day Agency Sequence"
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `auto_pause_on_reply` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_seq_ws` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `follow_up_sequence_steps` (
    `id` CHAR(36) NOT NULL,
    `sequence_id` CHAR(36) NOT NULL,
    `message_template_id` CHAR(36) NOT NULL,
    `step_number` INT UNSIGNED NOT NULL, -- 1, 2, 3, 4
    `delay_days` INT UNSIGNED NOT NULL DEFAULT 1, -- Days after previous step
    `delay_hours` INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `seq_step_unique` (`sequence_id`, `step_number`),
    CONSTRAINT `fk_step_seq` FOREIGN KEY (`sequence_id`) REFERENCES `follow_up_sequences` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_step_tmpl` FOREIGN KEY (`message_template_id`) REFERENCES `message_templates` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### 5.3 `follow_ups` (Execution Records)
```sql
CREATE TABLE `follow_ups` (
    `id` CHAR(36) NOT NULL,
    `workspace_id` CHAR(36) NOT NULL,
    `lead_id` CHAR(36) NOT NULL,
    `assigned_user_id` CHAR(36) NULL,
    `sequence_id` CHAR(36) NULL,
    `sequence_step_id` CHAR(36) NULL,
    `message_template_id` CHAR(36) NULL,
    
    `type` VARCHAR(32) NOT NULL DEFAULT 'manual_reminder', -- manual_reminder, sequence_nudge
    `status` VARCHAR(32) NOT NULL DEFAULT 'scheduled', -- scheduled, due, completed, snoozed, cancelled
    `reminder_notes` VARCHAR(512) NULL,
    
    `due_at` TIMESTAMP NOT NULL,
    `completed_at` TIMESTAMP NULL,
    `cancelled_at` TIMESTAMP NULL,
    `snoozed_until` TIMESTAMP NULL,
    
    `idempotency_token` VARCHAR(64) NULL, -- Prevents duplicate cron dispatch
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    KEY `idx_followups_ws_due_status` (`workspace_id`, `due_at`, `status`),
    KEY `idx_followups_lead` (`lead_id`, `status`),
    UNIQUE KEY `followups_idempotency_unique` (`idempotency_token`),
    CONSTRAINT `fk_followups_ws` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_followups_lead` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_followups_user` FOREIGN KEY (`assigned_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_followups_seq` FOREIGN KEY (`sequence_id`) REFERENCES `follow_up_sequences` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_followups_step` FOREIGN KEY (`sequence_step_id`) REFERENCES `follow_up_sequence_steps` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_followups_tmpl` FOREIGN KEY (`message_template_id`) REFERENCES `message_templates` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

---

## 6. AI Subsystem & Usage Tables

### 6.1 `ai_scores`
```sql
CREATE TABLE `ai_scores` (
    `id` CHAR(36) NOT NULL,
    `workspace_id` CHAR(36) NOT NULL,
    `lead_id` CHAR(36) NOT NULL,
    `provider` VARCHAR(64) NOT NULL, -- openai-gpt4o-mini, anthropic, fallback_rules
    `score` SMALLINT UNSIGNED NOT NULL, -- 0 to 100
    `temperature` VARCHAR(16) NOT NULL, -- HOT, WARM, COLD
    `urgency` VARCHAR(16) NOT NULL, -- HIGH, MEDIUM, LOW
    `intent` VARCHAR(255) NOT NULL,
    `fit_summary` TEXT NOT NULL,
    `reason` TEXT NOT NULL,
    `recommended_action` VARCHAR(255) NOT NULL,
    `input_hash` CHAR(64) NOT NULL, -- SHA-256 hash of input text to prevent duplicate runs
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_ai_scores_lead` (`lead_id`),
    CONSTRAINT `fk_ai_scores_ws` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ai_scores_lead` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### 6.2 `ai_usage_logs`
```sql
CREATE TABLE `ai_usage_logs` (
    `id` CHAR(36) NOT NULL,
    `workspace_id` CHAR(36) NOT NULL,
    `lead_id` CHAR(36) NOT NULL,
    `provider` VARCHAR(64) NOT NULL,
    `tokens_prompt` INT UNSIGNED NOT NULL DEFAULT 0,
    `tokens_completion` INT UNSIGNED NOT NULL DEFAULT 0,
    `tokens_total` INT UNSIGNED NOT NULL DEFAULT 0,
    `estimated_cost_usd` DECIMAL(8, 6) NOT NULL DEFAULT 0.000000,
    `execution_ms` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` VARCHAR(32) NOT NULL DEFAULT 'success', -- success, fallback, timeout, error
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_ai_usage_ws_created` (`workspace_id`, `created_at`),
    CONSTRAINT `fk_ai_usage_ws` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

---

## 7. Webhooks, API & CSV Ingestion Tables

### 7.1 `webhook_sources` & `webhook_logs`
```sql
CREATE TABLE `webhook_sources` (
    `id` CHAR(36) NOT NULL,
    `workspace_id` CHAR(36) NOT NULL,
    `name` VARCHAR(255) NOT NULL, -- e.g. "Webflow Landing Page"
    `endpoint_uuid` CHAR(36) NOT NULL,
    `secret_hash` VARCHAR(255) NULL, -- Optional HMAC verification secret
    `field_mapping` JSON NOT NULL, -- Key mapping schema
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `last_received_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `wh_sources_uuid_unique` (`endpoint_uuid`),
    CONSTRAINT `fk_wh_sources_ws` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `webhook_logs` (
    `id` CHAR(36) NOT NULL,
    `workspace_id` CHAR(36) NOT NULL,
    `webhook_source_id` CHAR(36) NOT NULL,
    `raw_payload` JSON NOT NULL,
    `status` VARCHAR(32) NOT NULL, -- processed, ignored_duplicate, failed
    `error_message` VARCHAR(512) NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_wh_logs_source` (`webhook_source_id`, `created_at`),
    CONSTRAINT `fk_wh_logs_ws` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_wh_logs_source` FOREIGN KEY (`webhook_source_id`) REFERENCES `webhook_sources` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### 7.2 `import_batches` (CSV Chunk Tracking)
```sql
CREATE TABLE `import_batches` (
    `id` CHAR(36) NOT NULL,
    `workspace_id` CHAR(36) NOT NULL,
    `user_id` CHAR(36) NOT NULL,
    `filename` VARCHAR(255) NOT NULL,
    `total_rows` INT UNSIGNED NOT NULL DEFAULT 0,
    `processed_rows` INT UNSIGNED NOT NULL DEFAULT 0,
    `successful_rows` INT UNSIGNED NOT NULL DEFAULT 0,
    `skipped_rows` INT UNSIGNED NOT NULL DEFAULT 0,
    `failed_rows` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` VARCHAR(32) NOT NULL DEFAULT 'pending', -- pending, processing, completed, failed
    `error_log` JSON NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_import_ws` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_import_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

---

## 8. Security, Governance & System Tables

### 8.1 `api_keys`
```sql
CREATE TABLE `api_keys` (
    `id` CHAR(36) NOT NULL,
    `workspace_id` CHAR(36) NOT NULL,
    `user_id` CHAR(36) NOT NULL,
    `name` VARCHAR(255) NOT NULL, -- e.g. "Zapier Integration Token"
    `key_identifier` VARCHAR(16) NOT NULL, -- Plain prefix e.g. "lp_live_9f83"
    `token_hash` VARCHAR(64) NOT NULL, -- SHA-256 hash of plain token
    `scopes` JSON NOT NULL, -- ["leads:write", "leads:read"]
    `last_used_at` TIMESTAMP NULL,
    `expires_at` TIMESTAMP NULL,
    `revoked_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `api_keys_hash_unique` (`token_hash`),
    CONSTRAINT `fk_api_keys_ws` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_api_keys_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### 8.2 `notifications`
```sql
CREATE TABLE `notifications` (
    `id` CHAR(36) NOT NULL,
    `workspace_id` CHAR(36) NOT NULL,
    `user_id` CHAR(36) NOT NULL,
    `type` VARCHAR(64) NOT NULL, -- response_overdue, follow_up_due, lead_assigned
    `title` VARCHAR(255) NOT NULL,
    `body` VARCHAR(512) NOT NULL,
    `action_url` VARCHAR(512) NULL,
    `read_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_notifications_user_read` (`user_id`, `read_at`),
    CONSTRAINT `fk_notif_ws` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_notif_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### 8.3 `settings`
```sql
CREATE TABLE `settings` (
    `id` CHAR(36) NOT NULL,
    `workspace_id` CHAR(36) NOT NULL,
    `namespace` VARCHAR(64) NOT NULL DEFAULT 'general', -- general, working_hours, notifications, lost_reasons
    `key` VARCHAR(64) NOT NULL,
    `value` JSON NOT NULL, -- Typed setting value
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `settings_ws_key_unique` (`workspace_id`, `namespace`, `key`),
    CONSTRAINT `fk_settings_ws` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### 8.4 `audit_logs`
```sql
CREATE TABLE `audit_logs` (
    `id` CHAR(36) NOT NULL,
    `workspace_id` CHAR(36) NOT NULL,
    `user_id` CHAR(36) NULL,
    `action` VARCHAR(64) NOT NULL, -- user_login, member_invited, api_key_revoked, license_activated
    `entity_type` VARCHAR(64) NOT NULL,
    `entity_id` CHAR(36) NOT NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` VARCHAR(255) NULL,
    `metadata` JSON NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_audit_ws_created` (`workspace_id`, `created_at`),
    CONSTRAINT `fk_audit_ws` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### 8.5 `license_records`
```sql
CREATE TABLE `license_records` (
    `id` CHAR(36) NOT NULL,
    `license_key` VARCHAR(64) NOT NULL,
    `edition` VARCHAR(32) NOT NULL DEFAULT 'pro', -- starter, pro, agency
    `entitlements` JSON NOT NULL, -- {"max_workspaces": 10, "ai_qualification": true}
    `payload_signature` TEXT NOT NULL, -- Cryptographically signed server token
    `last_verified_at` TIMESTAMP NULL,
    `offline_grace_expires_at` TIMESTAMP NOT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `license_key_unique` (`license_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### 8.6 Database Queue Infrastructure Tables (`jobs` & `failed_jobs`)
```sql
CREATE TABLE `jobs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `queue` VARCHAR(255) NOT NULL,
    `payload` LONGTEXT NOT NULL,
    `attempts` TINYINT UNSIGNED NOT NULL,
    `reserved_at` INT UNSIGNED NULL,
    `available_at` INT UNSIGNED NOT NULL,
    `created_at` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `failed_jobs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(255) NOT NULL,
    `connection` TEXT NOT NULL,
    `queue` TEXT NOT NULL,
    `payload` LONGTEXT NOT NULL,
    `exception` LONGTEXT NOT NULL,
    `failed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```