# Database — A&B Hub

67 tables, 122 foreign keys, 212 indexes (50/89/153 after Phase 1,
57/105/174 after Phase 2, 59/110/185 after Phase 3, 63/116/199 after
Phase 4; Phase 5 added the AI modules and integrations tables below).
Every table was actually created (`CREATE TABLE`) against a live MySQL
8.0.46 instance during development — `database/schema.sql` is not a
design document that might not run, it's the exact source the
migrations in `database/migrations/` are mechanically generated from,
and both were verified to import cleanly, in order, with zero errors,
on every phase.

## Conventions (apply to every table unless noted)

| Convention | Detail |
|---|---|
| Engine / charset | `InnoDB`, `utf8mb4`, `utf8mb4_unicode_ci` — full emoji/international-name support, real foreign key enforcement |
| Primary key | `id BIGINT UNSIGNED AUTO_INCREMENT` |
| Audit fields | `created_by`, `updated_by` — nullable `FK -> users.id ON DELETE SET NULL` (deleting a user never deletes their history, it just anonymizes the "by" field) |
| Timestamps | `created_at`, `updated_at`, both `DATETIME`, auto-managed by `BaseModel` (or `DEFAULT CURRENT_TIMESTAMP` / `ON UPDATE CURRENT_TIMESTAMP` at the schema level for tables written outside the model layer) |
| Soft deletes | `deleted_at DATETIME NULL` on any record that must remain queryable for history/reporting after a user "deletes" it (leads, customers, companies, appointments, invoices, campaigns, automations, users themselves...) — a real `DELETE` is reserved for tables where there's no reporting value in keeping a removed row (e.g. `lead_tasks`) |
| Money | Always a `DECIMAL(12,2)` amount paired with a `CHAR(3) currency` column, `DEFAULT 'CAD'` — never a bare float, never an assumed currency |
| Polymorphic relations | `activities`, `notes`, `attachments`, `taggables` all use an `(entity_type VARCHAR(40), entity_id BIGINT UNSIGNED)` pair instead of a real foreign key. MySQL cannot enforce a foreign key against more than one possible parent table — this is documented in `schema.sql` at each occurrence, and integrity is enforced in the model layer instead (a model calling `ActivityLogger::logEntityActivity('lead', $id, ...)` is trusted to pass a real, current `leads.id`) |

## Table groups

### 1. RBAC, Auth & Multi-Business Access (13 tables)
`business_lines`, `roles`, `permissions`, `role_permissions`,
`user_permission_overrides`, `users`, `user_business_lines`,
`user_invitations`, `password_resets`, `remember_tokens`,
`login_attempts`, `rate_limit_hits`, `api_keys`, `sessions`

The two companies (`business_lines`) are data, not a hardcoded enum —
a third brand could be added with an `INSERT`, not a migration.
`user_business_lines` is what makes "Both Brands" / "Homecare only" /
"Janitorial only" in the User Management table real. RBAC is three
layers deep: a user's `role_id` grants a baseline via
`role_permissions`; `user_permission_overrides` can allow or deny a
*specific* permission for one user on top of that (deny always wins).

### 2. CRM Core (8 tables)
`lead_sources`, `lead_stages`, `leads`, `lead_tasks`, `companies`,
`service_plans`, `customers`, `customer_contacts`

`lead_stages` is data (with `is_won`/`is_lost` flags), not an ENUM —
the Kanban columns on the Leads page are meant to be admin-configurable
eventually, and an ENUM would make that a migration every time. A
`leads` row can optionally belong to a `companies` row (B2B) and, once
won, is linked forward to the `customers` row it became via
`customers.source_lead_id` — the full lead-to-customer history is
never lost.

### 3. Shared / Polymorphic (5 tables)
`activities`, `notes`, `attachments`, `tags`, `taggables`

One `activities` table backs *every* entity's "Activity Timeline"
(Lead Details, Customer Details, and anything future). One
`attachments` table backs the "Files" tab wherever it appears. This
avoids the alternative — a `lead_activities` table, a
`customer_activities` table, a `company_activities` table, all with
identical shape — which is pure duplication with no benefit.

### 4. Scheduling (2 tables)
`service_types`, `appointments`

An `appointments` row can reference a `customer_id` (recurring
service) or a `lead_id` (pre-sale assessment) — never neither, though
that specific rule is enforced in the model layer rather than a CHECK
constraint (see the note in §6 below on why).

### 5. Billing (4 tables)
`invoices`, `invoice_items`, `payments`, `billing_accounts`

Deliberately two separate concepts that could easily get conflated:
`invoices`/`invoice_items`/`payments` is A&B billing *their* customers
and companies. `billing_accounts` is the Hub's *own* SaaS subscription
(the "$899/mo Enterprise Plan" on the Settings > Billing tab) — one
row, unrelated to customer billing.

### 6. Email (3 tables)
`email_accounts`, `email_messages`, `email_templates`

`email_accounts.credentials_encrypted` stores an OAuth token or SMTP
secret — application-level encrypted (using `APP_KEY`), never
plaintext, never logged.

### 7. Campaigns (2 tables)
`campaigns`, `campaign_recipients`

A campaign's `recipients_count`/`opens_count`/`clicks_count`/
`unsubscribes_count` are cached rollups on the `campaigns` row itself
(what the Campaigns list table renders) — the per-recipient detail
each rollup is computed from lives in `campaign_recipients`.

### 8. Automation (3 tables)
`automation_workflows`, `automation_steps`, `automation_runs`

Mirrors exactly what the Automation Workflow Builder page renders: a
workflow is an ordered list of steps (trigger → send email → wait →
condition → branch), stored as `automation_steps` rows with a
`sort_order` and a `parent_branch` for the yes/no fork shown in that
page's diagram. `automation_runs` is the execution log — one row per
entity that workflow has fired for.

### 9. AI Assistant, Chatbot & Voice Agent (3 tables)
`chatbot_settings`, `conversations`, `conversation_messages`

Chatbot conversations and voice calls are unified under one
`conversations` table with a `channel` enum
(`chatbot`/`voice_agent`) rather than two parallel tables — the
Conversation Logs page lists both from a single query with a channel
badge, exactly matching the schema.

### 10. Notifications & Activity Log (3 tables)
`notifications`, `notification_preferences`, `activity_logs`

`notification_preferences` is opt-out, not opt-in: the *absence* of a
row for a given `(user_id, type, channel)` means enabled, matching
every toggle on the Notifications page starting in the "on" position.
`activity_logs` is the system-wide audit trail with a `category` enum
(`user_action`/`system`/`security`/`admin`) matching the four filter
chips on the Activity Logs page exactly.

### 11. Settings & Integrations (2 tables)
`settings`, `integrations`

`settings` is a simple key-value store — deliberately schema-flexible
so a future setting doesn't need a migration, just a new row.

### 12. Org Structure & Auth Hardening — Phase 2 (7 tables + `users` additions)
`departments`, `teams`, `team_members`, `password_histories`,
`email_verification_tokens`, `two_factor_recovery_codes`,
`user_preferences`

Added additively in migration `0013` — no Phase 1 table was redesigned;
`users` only gained new nullable columns (`department_id`,
`email_verified_at`, `password_changed_at`, `failed_login_count`,
`locked_until`), safe on a table that may already have data.

- **`departments`** / **`teams`** / **`team_members`** — a user belongs
  to at most one department (`users.department_id`) but can sit on
  multiple teams via the `team_members` pivot; a team optionally
  belongs to a department and optionally scopes to one business line.
- **`password_histories`** — every password a user has ever set (as a
  hash, never plaintext), checked by `PasswordPolicyService::isReused()`
  against `config('security.password.history_limit')` before a change
  is allowed.
- **`email_verification_tokens`** — one table serves both first-time
  verification (`new_email IS NULL`) and email-change re-verification
  (`new_email` set to the pending address).
- **`two_factor_recovery_codes`** — single-use backup codes, hashed at
  rest, generated once when 2FA is enabled via
  `TwoFactorService::generateRecoveryCodes()`.
- **`user_preferences`** — same key-value shape as `settings`, scoped
  per-user (theme, default landing page, etc.).

`rate_limit_hits` (generic AJAX rate limiting) and `sessions` (DB-backed
session storage) were introduced in Section 1 during Phase 1.

### 13. CRM Module Extensions — Phase 3 (2 tables + `appointments`/`leads`/`customers` additions)
`comments`, `import_batches`

Additive again — migration `0014`. `comments` is deliberately separate
from `notes` (Phase 1): notes are longer-form structured entries,
comments are short and optionally threaded (`parent_id`) — a lead's
detail page shows both in their own tab rather than one undifferentiated
feed.

- **`appointments`** gained `parent_appointment_id`, `recurrence_rule`
  (JSON), `is_recurring_parent`, `reminder_minutes_before`, and
  `reminder_sent_at`. Recurring appointments are never virtual/computed
  — `AppointmentService::generateOccurrences()` writes one real row per
  occurrence up front (capped at 52 to bound worst-case generation),
  each individually reschedulable/cancellable without touching the
  series. This was verified live: a weekly rule from Aug 10–Sep 1, 2026
  generated exactly 4 child rows with the correct dates.
- **`leads`** gained `priority` (independent of pipeline stage),
  `merged_into_lead_id` (a merged-away lead is soft-deleted, never hard
  — deleting it would break audit history), and `import_batch_id`.
- **`customers`** gained `import_batch_id` for the same CSV-import
  traceability.
- **`import_batches`** — one row per CSV import run (Leads today,
  same pattern for Customers/Companies later), with a per-row
  `error_log` (JSON array of `{row, message}`) so an admin can see
  exactly which rows were skipped as duplicates vs. which failed
  validation, without re-parsing the original file.

### 14. Marketing System — Phase 4 (5 tables + `campaigns`/`campaign_recipients`/`customers`/`automation_runs` additions)
`email_unsubscribes`, `audience_segments`, `email_queue`, `smtp_settings`,
plus reuse (not redesign) of Phase 1's `email_templates`, `campaigns`,
`campaign_recipients`, `automation_workflows`, `automation_steps`,
`automation_runs` — this phase is almost entirely new *application
code* (`CampaignService`, `AudienceSegmentService`, `EmailQueueService`,
`TrackingService`, `AutomationEngine`) running on top of tables that
were sitting empty since Phase 1.

- **`campaigns`** gained `parent_campaign_id`, `is_recurring_parent`,
  `recurrence_rule` (JSON — same shape as `appointments.recurrence_rule`
  from Phase 3), and `audience_segment_id`. Recurring campaigns
  (Newsletter) follow the identical real-child-row pattern as
  recurring appointments: `CampaignService::generateNextOccurrence()`
  creates an actual draft campaign row per occurrence, never a
  virtual/computed one.
- **`campaign_recipients`** gained `bounced_at`, `replied_at`, and a
  unique `unsubscribe_token` — one opaque per-recipient token doubles
  as the open-pixel token, the click-redirect token, *and* the
  unsubscribe-link token, so a send only needs to mint one secret
  instead of three.
- **`customers`** gained `date_of_birth`, needed for the Birthday
  Email automation trigger.
- **`automation_runs`** (Phase 1) gained `next_step_at` — the one
  column the existing schema was missing to actually support a paused
  "wait" step; added here because it's Phase 4 application code
  (`AutomationEngine`) that needed it, even though the parent table
  dates to Phase 1.
- **`email_unsubscribes`** — the GLOBAL suppression list. Unsubscribing
  from one campaign suppresses the address from every future campaign
  (`AudienceSegmentService::resolve()` filters against this table on
  every call) — updating just the originating campaign's recipient row
  would only have stopped that one campaign from re-contacting them.
- **`audience_segments`** — saved, reusable "Filtered Audience"
  definitions, stored as a small JSON filter DSL rather than a frozen
  recipient list, so a segment's membership is always current at send
  time.
- **`email_queue`** — the actual send queue. `campaigns.status` says
  *what the campaign is doing*; `email_queue` rows are *the individual
  SMTP transactions still owed*. Decoupling them is what lets a
  5,000-recipient send return instantly instead of blocking the HTTP
  request that triggered it — `EmailQueueModel::claimDue()` uses
  `SELECT ... FOR UPDATE` so concurrent worker runs never double-send
  the same row.
- **`smtp_settings`** — single-row (`id` fixed at 1, enforced by a
  `CHECK` constraint) admin-configurable mail transport. The password
  is AES-256-CBC encrypted at rest using a key derived from `APP_KEY`
  (verified round-trip live during development), not stored in
  plaintext.

### 15. AI Modules & Integrations — Phase 5 (5 tables + `conversations`/`appointments` additions)
`ai_prompts`, `knowledge_base_articles`, `training_examples`,
`webhook_events`, plus reuse of Phase 1's `conversations`,
`conversation_messages`, `chatbot_settings`, and `integrations` —
exactly like Phase 4, most of this phase's tables already existed;
this phase is the AI Assistant, Chatbot, and Voice Agent application
code that finally uses them.

- **`conversations`** gained an `ai_assistant` channel value (was
  `chatbot`/`voice_agent` only), plus `direction` (voice calls only),
  `external_ref` (Twilio Call SID — how `VoiceAgentService` correlates
  a webhook back to the right conversation row), and `transcript_text`
  (a flattened full transcript, separate from the turn-by-turn
  `conversation_messages` rows, convenient for quick reading or
  AI summarization).
- **`appointments`** gained `google_calendar_event_id` for two-way
  Google Calendar sync bookkeeping.
- **`ai_prompts`** — admin-editable system prompts, one per AI feature
  (`ai_assistant`/`chatbot`/`voice_agent`), optionally scoped to one
  business line; `AiPromptModel::activeFor()` resolves the
  business-line-specific prompt first, falling back to the
  both-brands one.
- **`knowledge_base_articles`** — FAQ-style retrieval, `FULLTEXT`
  indexed on `question`+`answer`. `KnowledgeBaseModel::search()`
  degrades to a plain `LIKE` search when the FULLTEXT query returns
  nothing (MySQL's FULLTEXT engine ignores very short queries and
  stopwords), verified live with both paths.
- **`training_examples`** — curated prompt/ideal-response pairs kept
  for human review and JSONL export, deliberately NOT auto-fed into
  every request (that's what `ai_prompts` and
  `knowledge_base_articles` are for).
- **`webhook_events`** — every inbound Twilio/Meta/WhatsApp webhook
  payload is preserved here (raw JSON) before processing, so a failed
  or malformed webhook is debuggable from Admin instead of only ever
  existing in a provider's dashboard.
- **`integrations`** (Phase 1) needed no schema change — its
  `credentials_encrypted` blob was already generic enough for every
  provider in this phase, from a single API key (OpenAI, ElevenLabs)
  to a full OAuth token set (Google Calendar: access token, refresh
  token, expiry) to a Twilio Account SID/Auth Token pair. Only more
  catalog rows needed seeding.

- **`users.role_id → roles.id`**: `ON DELETE SET NULL`, not `CASCADE` —
  deleting a role should never delete the users who held it; they just
  lose their permissions until reassigned.
- **`leads.converted_customer_id`**: nullable, set once a lead becomes
  a customer, while `customers.source_lead_id` points the other
  direction — the relationship is intentionally bidirectional so both
  "show me this customer's original lead" and "show me what this old
  lead became" are single-lookup queries, not a search.
- **`invoices.customer_id` and `invoices.company_id`**: both nullable,
  exactly one should be set (an invoice bills either an individual
  customer or a B2B company, never both, never neither). This is
  enforced in `InvoiceModel`'s validation rather than a SQL `CHECK`
  constraint — see the note directly in `schema.sql` at that table:
  MySQL forbids a column from being both part of a `CHECK` constraint
  and the child side of a foreign key with a `CASCADE`/`SET NULL`
  referential action (error 3823), and both of those FKs need `SET
  NULL`-adjacent behavior elsewhere. This was caught by actually
  attempting to import the schema, not discovered later.
- **`user_permission_overrides.effect`**: `'allow'` or `'deny'` — a
  `'deny'` row always wins, even over an `'allow'` role permission;
  see `App\Core\Rbac::permissionsFor()`.

## Regenerating migrations after a schema change

```bash
php database/build_migrations.php   # re-splits schema.sql into database/migrations/*.sql
php bin/migrate.php                 # applies whatever's newly pending
```

`schema.sql` is the only file to hand-edit — `build_migrations.php`
splits it on its `-- SECTION N` headers into the numbered files
`migrate.php` actually applies, deleting and regenerating all of them
every run so there's never a stale, out-of-sync migration file sitting
around.

## Running the migrations

```bash
php bin/migrate.php            # apply anything not yet applied, in order
php bin/migrate.php --fresh    # DROP everything and start over (never run in production)
php bin/seed.php               # populate reference data + demo accounts (run after migrate)
```

`schema_migrations` tracks exactly which numbered file has been
applied and in which batch, so `migrate.php` is safe to run repeatedly
— a second run with nothing pending prints "Nothing to migrate" and
exits cleanly, which was verified during development, not assumed.
