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