# Database

## Relevant Migrations

- `database/migrations/2026_01_19_113036_create_leads_table.php`
- `database/migrations/2026_01_19_113036_create_lead_events_table.php`
- `database/migrations/2026_01_19_115447_add_pricing_tier_to_leads_table.php`
- `database/migrations/2026_01_20_000000_create_users_table.php`
- `database/migrations/2026_01_20_000001_create_password_reset_tokens_table.php`
- `database/migrations/2026_02_05_000000_add_enriched_tracking_to_leads_table.php`
- `database/migrations/2026_04_20_000001_add_calculator_fields_to_leads_table.php`
- `database/migrations/2026_05_09_000001_harden_leads_dashboard_and_schema.php`

Seeder:

- `database/seeders/DatabaseSeeder.php` is empty.

## Leads Table

Created by: `database/migrations/2026_01_19_113036_create_leads_table.php`

Modified by:

- `database/migrations/2026_01_19_115447_add_pricing_tier_to_leads_table.php`
- `database/migrations/2026_02_05_000000_add_enriched_tracking_to_leads_table.php`
- `database/migrations/2026_04_20_000001_add_calculator_fields_to_leads_table.php`
- `database/migrations/2026_05_09_000001_harden_leads_dashboard_and_schema.php`

Model: `app/Models/Lead.php`

Expected columns from migrations:

- `id`
- `uuid` unique
- `name`
- `email`
- `phone`
- `company`
- `service_slug`
- `service_name`
- `industry_slug`
- `industry_name`
- `pricing_tier`
- `pricing_value`
- `selected_project_type`
- `selected_scope`
- `selected_addons`
- `selected_budget_range`
- `message`
- `budget`
- `timeline`
- `source_page`
- `first_page`
- `source_button`
- `utm_source`
- `utm_medium`
- `utm_campaign`
- `utm_term`
- `utm_content`
- `gclid`
- `fbclid`
- `msclkid`
- `entry_service_hint`
- `entry_intent_hint`
- `recommended_service`
- `session_language`
- `generated_summary`
- `referrer`
- `referrer_domain`
- `ip_address`
- `user_agent`
- `device_type`
- `locale`
- `detected_country`
- `market_multiplier`
- `complexity_level`
- `estimated_hours`
- `estimated_price_min`
- `estimated_price_max`
- `country`
- `region`
- `city`
- `browser`
- `os`
- `timezone`
- `status`
- `form_completion_percentage`
- `first_interaction_at`
- `last_interaction_at`
- `submitted_at`
- `created_at`
- `updated_at`

Indexes from migration:

- `email`
- `status`
- `service_slug`
- `created_at`

Status enum in migration:

- `cold`
- `warm`
- `hot`
- `submitted`
- `contacted`
- `converted`
- `lost`
- `archived`

Status consistency migration:

- `database/migrations/2026_05_09_000001_harden_leads_dashboard_and_schema.php`
- On MySQL, modifies `leads.status` to include `cold`, `warm`, `hot`, `submitted`, `contacted`, `converted`, `lost`, `archived`.
- On SQLite, no enum alteration is needed because Laravel stores the enum as a string.

## Local SQLite Schema Observed

File checked: `database/database.sqlite`

Observed `leads` columns after running migrations:

- Base lead columns
- `pricing_tier`, `pricing_value`
- Enriched tracking columns: `referrer_domain`, `device_type`, `country`, `region`, `city`, `browser`, `os`, `timezone`
- Calculator/tracking columns from `quote-calculator.js` and `LeadController`

## Lead Events Table

Created by: `database/migrations/2026_01_19_113036_create_lead_events_table.php`

Model: `app/Models/LeadEvent.php`

Columns:

- `id`
- `lead_id`: foreign key to `leads.id`, cascade delete
- `event_type`
- `event_source`
- `event_data`: JSON/text depending DB
- `page_url`
- `page_title`
- `created_at`: timestamp default current

Indexes:

- `event_type`
- `created_at`

Relationship:

- `Lead::events()` has many `LeadEvent`
- `LeadEvent::lead()` belongs to `Lead`

Event types:

- `click`
- `page_view`
- `form_start`
- `field_filled`
- `form_abandoned`
- `form_submitted`

## Users Table

Created by: `database/migrations/2026_01_20_000000_create_users_table.php`

Model: `app/Models/User.php`

Columns:

- `id`
- `name`
- `email` unique
- `email_verified_at`
- `password`
- `is_admin`
- `remember_token`
- `created_at`
- `updated_at`

Used by:

- `config/auth.php` provider `users`
- `AuthController::login`
- Dashboard `auth` middleware
- Dashboard `admin` middleware through `app/Http/Middleware/EnsureAdmin.php`

Migration `database/migrations/2026_05_09_000001_harden_leads_dashboard_and_schema.php` adds `is_admin` default false and marks existing users as admin to preserve current dashboard access.

## Password Reset Tokens Table

Created by: `database/migrations/2026_01_20_000001_create_password_reset_tokens_table.php`

Columns:

- `email` primary
- `token`
- `created_at`

Used by config:

- `config/auth.php` password broker `users`

Needs verification: no routes/controllers/views for password reset were found.

## Contact Fallback Storage

Fallback contact submissions are stored in the database and backed up to JSON.

File: `storage/app/contact-submissions.json`

Writer: `App\Http\Controllers\ContactController::submit`

Behavior:

- Creates a submitted row in `leads`.
- Logs `lead_events.event_type = form_submitted`.
- Sets `source_button = Contact form fallback`.
- Triggers submitted lead broadcast/Beams notifications.
- Appends validated contact submission plus `submitted_at` and `locale`.
- Keeps last 500 entries.
- JSON backup write failures are logged and ignored for UX.

## Database Config

File: `config/database.php`

Default:

- `env('DB_CONNECTION', 'mysql')`

Connections configured:

- `sqlite`
- `mysql`
- `pgsql`

Redis configured for default/cache.

## Data Flow

```mermaid
flowchart TD
    A["Contact form JS submit"] --> B["LeadController::submitFormHandler"]
    B --> C["leads.uuid lookup or create"]
    C --> D["Update contact/project/tracking/status fields"]
    D --> E["Lead::logEvent form_submitted"]
    E --> F["lead_events row"]
    D --> G["LeadNotification + Beams"]
```

```mermaid
flowchart TD
    A["Native POST /contact fallback"] --> B["ContactController::submit"]
    B --> C["Validate request"]
    C --> D["storage/app/contact-submissions.json"]
    D --> E["Redirect thank-you"]
```

## Relationships

- `leads.id` -> `lead_events.lead_id`
- Deleting a lead cascades delete to its events.
- `users` has no direct relationship to leads in code.

## Risks / Needs Verification

- Local database is not fully migrated relative to visible migration files.
- `archived` status needs schema verification.
- Contact fallback JSON storage is separate from dashboard leads, so native POST submissions will not appear in the leads dashboard.
- There are no seeders for admin users.