# LeadPilot — Database Architecture Overview & Philosophy

**Document Version:** 1.0.0 (Phase 5 Database Architecture Lock)  
**Status:** Approved & Formally Recorded  
**Engine:** MySQL 8.0+ / MariaDB 10.6+ (InnoDB, strict `utf8mb4_unicode_ci`, UTC timestamps)  
**Positioning:** The system that makes sure every enquiry gets answered and followed up.  
**Signature Concept:** No Lead Left Behind  

---

## 1. Database Architecture Principles

LeadPilot's relational schema is designed for **high data integrity, sub-second query performance, ironclad tenant isolation, and simple operational maintenance**.

```
+---------------------------------------------------------------------------------------------------+
|                                 DATABASE DESIGN INVARIANTS                                        |
+---------------------------------------------------------------------------------------------------+
|  1. TENANT SAFETY FIRST    | Every tenant resource possesses a non-nullable `workspace_id` foreign key.   |
|  2. NORMALIZATION FIRST    | Zero duplicate customer records; clean relational modeling.          |
|  3. EXPLICIT FOREIGN KEYS  | Referential integrity enforced at DB level (`ON DELETE RESTRICT/CASCADE`).|
|  4. CONTROLLED DENORM      | Denormalization allowed only for high-frequency indexable triage flags     |
|                            | (e.g. `leads.ai_score`, `leads.temperature`, `leads.next_followup_at`).     |
|  5. TIMEZONE & UTC PURITY  | All timestamps stored in UTC (`DATETIME` / `TIMESTAMP`); localized in UI. |
|  6. FINANCIAL PRECISION    | Money stored as `DECIMAL(12, 2)`; zero floating-point math on currencies.    |
|  7. CONCURRENCY & LOCKING  | Queue and sequence updates use row-level locking (`SELECT ... FOR UPDATE`). |
+---------------------------------------------------------------------------------------------------+
```

---

## 2. Global Column Naming & Identifier Standards

1. **Primary Keys:** UUID v4 stored as `CHAR(36)` across all public and domain entities (`id`).
2. **Foreign Keys:** `{singular_entity}_id CHAR(36)` (e.g. `workspace_id`, `assigned_user_id`, `lead_id`).
3. **Timestamps:** Standard Laravel `created_at TIMESTAMP NULL` and `updated_at TIMESTAMP NULL`. Specific event timestamps use explicit suffixes (e.g. `first_contacted_at`, `won_at`, `lost_at`, `due_at`, `read_at`).
4. **Boolean Flags:** Prefixed with `is_` or `has_` as `TINYINT(1)` (e.g. `is_recovered`, `is_active`, `has_responded`).
5. **State & Status:** Clean `VARCHAR(32)` strings (e.g. `status = 'active'`, `pipeline_stage_id`). No rigid MySQL `ENUM` types that require table alterations when states expand.