# Appointment Engine — data model

**Module:** [AI Appointment System](/docs/modules_handbook/manage/appointment-engine/readMe.md) · **Namespace:** `Src\AppointmentEngine` · **Tables:** `ae_*` + the shared `appointments`

Every table the engine owns, every column, and every constant its models define. The engine follows the house rules in [GUIDELINES §7](/GUIDELINES.md): **no schema-level foreign keys** — every `*_id` below is a convention enforced in code — `snake_case`, `is_` booleans, `_at` timestamps, `_by` blame columns, and a `uuid` on key models (runtime child tables deliberately have none).

> Companion docs: [bridges.md](/docs/modules_handbook/manage/appointment-engine/bridges.md) explains the ids that leave this module; [runner.md](/docs/modules_handbook/manage/appointment-engine/runner.md) explains what writes these rows.

> **⚠️ Read the 2026-09-09 tidy first.** On the owner's instruction the engine's duplicate tables merged into the platform's own — the [changelog entry for 2026-09-09](/docs/modules_handbook/manage/appointment-engine/changelog.md#2026-09-09--the-database-tidy-the-engines-duplicate-tables-merge-into-the-master-ones) is the map. In short: `ae_projects` → `projects` (every `ae_project_id` column is now `project_id`); `ae_calls` + `ae_call_turns` → `ai_voice_calls` + `ai_voice_call_turns` (which gained `group_id`, `ae_lead_id`, `channel`, `made_by`, `notes`, `outcome`, `refusal_reason`, `telephony_cost`, `lead_spoke`; `appointments.ae_call_id` is now `ai_voice_call_id`); `ae_blocked_numbers` → `ai_call_blocked_numbers` (which gained `group_id`); and `ae_appointments`, `ae_visits`, `ae_threads`, `ae_messages`, `ae_flow_nodes`, `ae_routing_rules`, `ae_settings`, `ae_connections` were dropped. **The sections below that describe those tables are history** — kept because their column notes explain the columns that came across. The engine's tables today are `ae_leads`, `ae_workflows`, `ae_workflow_nodes`, `ae_workflow_edges`, `ae_workflow_runs`, `ae_workflow_run_logs`, `ae_content_items`, `ae_documents`, `ae_project_closers`, `ae_closer_handoffs`, `ae_sheet_cursors`, plus the shared `appointments`, `projects`, `ai_voice_calls` and `ai_call_blocked_numbers`.
>
> **2026-09-10 additions (the Subsale owner listing):** `ae_leads.owner_listing_row_id` (nullable → `flg_owner_listing_rows.id`; a lead started from the project's owner listing, `source = owners`), `WorkflowRun::STATUS_PAUSED = 'paused'` (held by a person — not open, so nothing moves it; `context.paused_from` keeps the status it resumes to), a new trigger node type `trigger.owner_listing` (config `pace_per_minute`) and plan entry `owners`. The listing itself is the Subsale module's `flg_owner_listing_*` tables on the same `projects` row.
>
> **2026-09-18:** a listing no longer has to be a strata roll. `flg_owner_listing_rows.row_key` (unique per project, replacing `unique(project_id, unit_no)`) is the row's identity — the unit number when the listing has one, else `"<phone>|<OWNER NAME>"` — and `unit_no` is nullable and display-only. A unit-less listing gets no floor plan, no type and no rent guidance; everything else (`Triggers::startOwner`, the Owners tab, pause / resume) works on it unchanged, since all of those already key on the row id or the phone. See the [changelog](/docs/modules_handbook/manage/appointment-engine/changelog.md).
>
> **⚠️ 2026-09-18, same day: `flg_owner_listing_rows.lead_id` is a LINK, never a creation.** The import used to resolve every owner through `LeadRepository::firstOrCreateForIdentity()`, which put 792 owners into the CRM's `leads` (and `users`) in one minute; the owner's ruling is that a property owner is not a customer, and 1,151 such leads were deleted. `resolveLeadId()` now uses the read-only `LeadRepository::findForIdentity()`: an owner who already IS a lead is linked (and that lead gains the `SOURCE_OWNER_LISTING` attribution), and an owner we do not know keeps `lead_id` NULL. So a listing row is the owner's record — `owner_name` / `owner_phone` / `owner_email` are the source of truth for outreach, which addresses people by `flg_owner_outreach_recipients.owner_listing_row_id` + `phone_e164`. Never reintroduce a creating call here.

The AI Appointment Engine ("AE") began by keeping its own book. Its tables were `ae_`-prefixed and deliberately **separate** from the CRM's `leads`, `ai_voice_calls` and `whatsapp_*` tables — the migration that created them states why: it is a different product, sold on its own to agencies, and sharing the CRM's tables would mean neither product could ever diverge on a status, a column or a retention rule (`database/migrations/2026_08_29_160000_create_appointment_engine_tables.php:7-25`). That stance was reversed table by table: appointments on 2026-09-06, and projects, calls and the block list on 2026-09-09. **`ae_leads` is the one book that stays the engine's own**, bridged to the CRM person by `lead_id` — the reasons are in the changelog entry above.

The first exception was **appointments**. Since the merge of 2026-09-06 the engine stopped writing its own `ae_appointments` and now writes THE book, `appointments`, which the CRM lead page and calendars already read (`database/migrations/2026_09_06_170000_add_ae_columns_to_appointments_table.php:7-16`).

### Conventions that apply to every table here

| Convention | What it means |
|---|---|
| **No schema foreign keys** | This codebase declares none, anywhere. Every `*_id` column is a relationship maintained at the Eloquent level only (`database/migrations/2026_09_07_190000_drop_ae_campaigns_tables.php:29-31` restates the rule). Deleting a parent orphans children unless a service cleans up — see `Services\LeadEraser`. |
| **`group_id` = tenancy** | Nearly every table carries a nullable `group_id` → `groups.id`. `NULL` means "the platform's own", not "unscoped". Reads/writes go through `Support\AeScope` because platform staff *choose* an agency for the session (`src/AppointmentEngine/Support/AeScope.php:24-71`). Its docblock says "never `GroupScope` directly" — that is the rule, with **one live exception**: `Support/AiCallBook.php:193` still calls `GroupScope::apply()` for the AI Calls page. |
| **`uuid` = the public id** | Root/entity tables carry `char(36) uuid UNIQUE`. `Diver\Database\Eloquent\Traits\HasUuid` generates it on create and makes route-model binding resolve by `uuid`, so sequential ids never appear in URLs (`diver/Database/Eloquent/Traits/HasUuid.php:19-38`). **Foreign keys always reference `id`, never `uuid`** — with two deliberate exceptions noted below (node config `script_id`/`knowledge_id`, and the uuid-keyed correlation bag on **`whatsapp_flow_runs.meta->ae`** — written by `ChatTakeover.php:77` and `WhatsappAiTakeover.php:101`, read back by `LeadEraser.php:50`; note it lives on the HOST flow run, not on `WorkflowRun.context`). |
| **Blame columns** | `created_by` / `updated_by` / `deleted_by` → `users.id`, auto-filled by `Diver\...\Traits\RecordsBlame` from the authenticated user; nothing is recorded for console/queue writes (`diver/Database/Eloquent/Traits/RecordsBlame.php:19-60`). |
| **Soft delete** | Only `ae_leads`, `ae_projects`, `ae_workflows`, `ae_content_items` (and legacy `ae_appointments`) plus the shared `appointments`. Everything else is a hard-delete table. |
| **Runtime child tables** | `ae_call_turns`, `ae_messages`, `ae_workflow_run_logs`, `ae_sheet_cursors` have **no `uuid`** and no blame columns — they exist only under a parent. (`ae_project_closers` is **not** one of them: it is *config*, and carries `created_by`/`updated_by` via `RecordsBlame` — `database/migrations/2026_09_08_120000_create_ae_project_closers_table.php:13-14`.) `ae_closer_handoffs` is the odd one: a runtime table that *does* carry a uuid, because the Telegram accept/pass links have to address a row. |

### Table map

| Table | Model | uuid | Soft delete | Blame | Grain |
|---|---|---|---|---|---|
| `ae_leads` | `Lead` | ✔ | ✔ | c/u/d | one person in the engine's book |
| `ae_projects` | `Project` | ✔ | ✔ | — | one deal being sold |
| `ae_project_closers` | `ProjectCloser` | ✘ | ✘ | c/u | one closer on one project's list, per agency |
| `ae_workflows` | `Workflow` | ✔ | ✔ | c/u/d | one automation |
| `ae_workflow_nodes` | `WorkflowNode` | ✔ | ✘ | — | one step on the graph |
| `ae_workflow_edges` | `WorkflowEdge` | ✔ | ✘ | — | one connection |
| `ae_workflow_runs` | `WorkflowRun` | ✔ | ✘ | c | one lead through one workflow |
| `ae_workflow_run_logs` | `WorkflowRunLog` | ✘ | ✘ | — | one line of the trail (append-only) |
| `ae_calls` | `Call` | ✔ | ✘ | c/u | one call attempt (AI **or** human) |
| `ae_call_turns` | `CallTurn` | ✘ | ✘ | — | one utterance |
| `ae_closer_handoffs` | `CloserHandoff` | ✔ | ✘ | — | one offer of a booking to one closer |
| `ae_sheet_cursors` | `SheetCursor` | ✘ | ✘ | — | one Google-Sheet trigger's read position |
| `ae_settings` | `Setting` | ✔ | ✘ | c/u | one agency's numbers |
| `ae_connections` | `Connection` | ✔ | ✘ | c/u | one agency's credentials for one provider |
| `ae_content_items` | `ContentItem` | ✔ | ✔ | c/u/d | one parsed piece of AI content |
| `ae_documents` | `Document` | ✔ | ✘ | c | one uploaded file being read |
| `ae_flow_nodes` | `FlowNode` | ✔ | ✘ | c/u | one step of the legacy per-agency canvas |
| `ae_threads` | `Thread` | ✔ | ✘ | c/u | one WhatsApp conversation |
| `ae_messages` | `Message` | ✘ | ✘ | — | one WhatsApp message |
| `ae_routing_rules` | `RoutingRule` | ✔ | ✘ | c/u | one first-match-wins routing rule |
| `ae_blocked_numbers` | `BlockedNumber` | ✔ | ✘ | c | one do-not-call number |
| `appointments` | `Src\Appointment\Appointment` | ✔ | ✔ | c/u/d | THE booking (shared with the CRM) |
| `ae_appointments` | *(none)* | ✔ | ✔ | c/u/d | **retired** — see "Orphan tables" |
| `ae_visits` | *(none)* | ✔ | ✘ | c/u | **orphan** — see "Orphan tables" |

---

## `ae_leads` — the engine's own lead book

One person the engine is working. Distinct from `Src\Lead\Lead` (the CRM's `leads`): the same human is two rows, because the two products are allowed to disagree about them (`src/AppointmentEngine/Lead.php:13-25`).

| Column | Type | Meaning |
|---|---|---|
| `id` | bigint unsigned PK | |
| `uuid` | char(36) UNIQUE | public id; route key |
| `group_id` | bigint unsigned NULL | agency → `groups.id` |
| `team_id` | bigint unsigned NULL | team inside the agency → `teams.id`. Stamped from the closer's `admins.team_id` when the engine assigns one, or the actor's team on a manual add; NULL = not on a team. Appointments and runs follow their lead, so only the lead carries it (`database/migrations/2026_09_08_130000_add_team_id_to_ae_leads_table.php:7-13`) |
| `lead_id` | bigint unsigned NULL | the CRM person → `leads.id`. NULL = the identity resolver refused to link rather than risk a wrong merge (`database/migrations/2026_09_06_150000_add_lead_id_to_ae_leads_table.php:7-17`) |
| `name` | varchar(191) NULL | falls back to `'Lead ' + last 4 digits`, or `'Hidden number'` (`src/AppointmentEngine/Runner/Triggers.php:597`) |
| `phone` | varchar(32) NULL | **nullable since 2026-09-07**: a WhatsApp username hides the number. Such a lead runs the chat half only; the AI-call step refuses with `no_phone` (`database/migrations/2026_09_07_190000_make_ae_leads_phone_nullable_add_wa_contact.php:7-13`) |
| `wa_contact_id` | bigint unsigned NULL | → `whatsapp_contacts.id`; the identity anchor when `phone` is null |
| `email` | varchar(191) NULL | |
| `source_campaign` | varchar(191) NULL | WHICH ad (stores Meta's `referral.source_id`) |
| `ctwa_clid` | varchar(191) NULL | Meta's click id for a click-to-WhatsApp lead; the join key Meta's ads reporting and CAPI speak. **First-touch: written once at enrolment, never overwritten** (`database/migrations/2026_09_02_100001_add_ctwa_clid_to_ae_leads_table.php:7-14`) |
| `source` | varchar(16) NULL | HOW the lead arrived — `Lead::SOURCE_*` |
| `project` | varchar(191) NULL | display STRING copied from the workflow (or the sheet row) at enrolment |
| `ae_project_id` | bigint unsigned NULL | → `ae_projects.id`. Filled where blank on every touch, **never moved** (`Triggers.php:580-584`) |
| `stage` | tinyint unsigned NOT NULL default 1 | `Lead::STAGE_*` |
| `intent` | varchar(24) NULL | `Lead::INTENTS` key |
| `budget` | varchar(24) NULL | the words the lead used ("around 800k, maybe a bit more") — kept verbatim |
| `budget_amount` | decimal(12,2) NULL | the numeric READING of `budget`; NULL when no number could be read, so a routing rule never compares against a string (`database/migrations/2026_08_29_235000_add_budget_amount_to_ae_leads_table.php:7-15`) |
| `timeline` | varchar(24) NULL | free text from the call analysis |
| `journey_stage` | varchar(24) NULL | `Lead::JOURNEY_STAGES` key |
| `self_reported_first_property` | varchar(24) NULL | what the lead SAID (`'yes'` is the value `journeyMismatch()` tests) |
| `system_observed_viewings` | smallint unsigned NULL | what our records show |
| `assigned_admin_id` | bigint unsigned NULL | → `users.id` |
| `first_contacted_at` | timestamp NULL | |
| `last_activity_at` | timestamp NULL | bumped on every touch |
| `created_by` / `updated_by` / `deleted_by` | bigint unsigned NULL | → `users.id` |
| `created_at` / `updated_at` / `deleted_at` | timestamp NULL | soft-deletable |

**Indexes:** `phone`, `source_campaign`, `ctwa_clid`, `source`, `project`, `ae_project_id`, `stage`, `intent`, `journey_stage`, `budget_amount`, `assigned_admin_id`, `last_activity_at`, `team_id`, `lead_id`, `wa_contact_id`, plus composites `(group_id, stage)` and `(group_id, assigned_admin_id)`. **There is no unique on `phone`** — de-duplication happens in `Runner\Triggers` at enrolment.

### Constants (`src/AppointmentEngine/Lead.php:31-89`)

Stages are the product's own funnel, ordered, "because the whole Leads screen is a reading of where the AI got to before it stopped".

| Constant | Value | Label | Colour |
|---|---|---|---|
| `STAGE_NEW` | `1` | New | slate |
| `STAGE_CALLED` | `2` | AI called | sky |
| `STAGE_ANSWERED` | `3` | Answered | sky |
| `STAGE_APPOINTMENT` | `4` | Appointment | emerald |
| `STAGE_SHOWED_UP` | `5` | Showed up | emerald |
| `STAGE_LOST` | `6` | Lost | rose |

`STAGE_CALLED` means the AI dialled *whether or not anyone picked up*; `STAGE_ANSWERED` means someone answered **and spoke**.

| Constant | Value | Label |
|---|---|---|
| `SOURCE_MANUAL` | `manual` | Manual add |
| `SOURCE_IMPORT` | `import` | Imported |
| `SOURCE_WHATSAPP` | `whatsapp` | WhatsApp |
| `SOURCE_META` | `meta` | Meta lead form |
| `SOURCE_SHEET` | `sheet` | Google Sheet |
| `SOURCE_CTWA` | `ctwa` | Click-to-WhatsApp ad |

`INTENTS` (no `INTENT_*` constants — the keys are the values): `investor` → Investor, `own_stay` → Own stay, `upgrader` → Upgrader.

| Constant | Value | Label | Colour |
|---|---|---|---|
| `JOURNEY_FIRST_TIME` | `first_time` | First property | amber |
| `JOURNEY_SOME_EXPERIENCE` | `some_experience` | Viewed a few | sky |
| `JOURNEY_EXPERIENCED` | `experienced` | Seasoned buyer | violet |

### Casts, relationships, helpers

`$casts`: `stage` → integer, `budget_amount` → `decimal:2`, `system_observed_viewings` → integer, `first_contacted_at`/`last_activity_at` → datetime (`Lead.php:101-107`). `$fillable` covers every writable column including all three blame columns.

Relationships (`Lead.php:152-194`): `crmLead()` → `Src\Lead\Lead` on `lead_id`; `waContact()` → `WhatsappContact` on `wa_contact_id`; `aeProject()` → `Project` on `ae_project_id`; `runs()` → `WorkflowRun` on `ae_lead_id`; `assignedAdmin()` → `User` on `assigned_admin_id`; `handoffs()` → `CloserHandoff` on `ae_lead_id`, **ordered `latest('id')`**.

Static helper `Lead::parseBudget(?string): ?float` (`Lead.php:120-143`) reads "800k", "1.2m", "RM 750,000"; multiplies by 1 000 / 1 000 000 on a `k`/`m` suffix; and **returns NULL below a floor of 10 000** — a bare "3" is a bedroom count, and a routing rule reading 0 for "no idea" would file every un-qualified lead into the cheapest bracket.

`journeyMismatch(): bool` (`Lead.php:204-211`) returns true only when `self_reported_first_property === 'yes'` **and** `system_observed_viewings > 0`; both null-guarded. The disagreement is surfaced, never resolved.

**Deleting a lead is a service, not `$lead->delete()`.** `Services\LeadEraser::erase()` runs one transaction that ends the shadow WhatsApp runs, deletes `whatsapp_flow_runs` rows tagged `meta->ae->lead`, deletes `ae_workflow_run_logs` → `ae_closer_handoffs` → `ae_workflow_runs` → `ae_calls`, **soft**-deletes the `appointments` rows, then `forceDelete()`s the lead. A hard delete because the sheet reader matches by phone, the enrol-once rule keys on (lead, workflow), and the takeover guard keys on ended runs — a lingering row keeps the person out of the flow forever (`src/AppointmentEngine/Services/LeadEraser.php:15-85`).

---

## `ae_projects` — one deal being sold

Content, scripts and facts are project-specific, so they are scoped to one (`src/AppointmentEngine/Project.php:10`).

| Column | Type | Meaning |
|---|---|---|
| `id` | bigint unsigned PK | |
| `uuid` | char(36) UNIQUE | |
| `group_id` | bigint unsigned NULL | → `groups.id`; NULL = the platform's own, readable by every agency |
| `project_id` | bigint unsigned NULL | → CRM `projects.id` — the row holding commission, prices, catalogue link and the engagements/bookings pipeline. Nullable: "an unlinked row is recoverable, a mis-link is not" (`database/migrations/2026_09_06_210000_add_project_id_to_ae_projects_table.php:7-14`) |
| `name` | varchar(191) NOT NULL | |
| `is_active` | tinyint(1) NOT NULL default 1 | |
| `created_at` / `updated_at` / `deleted_at` | timestamp NULL | soft-deletable |

No blame columns and **no `RecordsBlame`** on the model — `Project` uses `HasUuid` only. Casts: `is_active` → boolean. Relationships: `crmProject()` → `Src\Property\Project` on `project_id`; `contentItems()`, `workflows()`, `leads()` all on `ae_project_id`; `closers()` → `ProjectCloser` **ordered by `position`** (`Project.php:28-59`).

---

## `ae_project_closers` — who closes this project's bookings, per agency

Config, not runtime state. An AE project is shared downward (a `group_id NULL` row is the platform's list); each agency names its own closers for the same project (`database/migrations/2026_09_08_120000_create_ae_project_closers_table.php:7-15`).

| Column | Type | Meaning |
|---|---|---|
| `id` | bigint unsigned PK | |
| `ae_project_id` | bigint unsigned NOT NULL | → `ae_projects.id` |
| `group_id` | bigint unsigned NULL | → `groups.id`; NULL = the platform's own list for this project |
| `admin_id` | bigint unsigned NOT NULL | → **`users.id`** (despite the name) |
| `position` | int unsigned NOT NULL default 0 | the order the leader picked — the rotation's tiebreak |
| `created_by` / `updated_by` | bigint unsigned NULL | → `users.id` |
| `created_at` / `updated_at` | timestamp NULL | |

**No `uuid`, no soft delete** — a removed closer is simply off the list. Casts force `ae_project_id`, `group_id`, `admin_id`, `position` to integer (`src/AppointmentEngine/ProjectCloser.php:27-32`). Relationships: `project()`, `group()`, `admin()` (`ProjectCloser.php:34-50`).

Read by `Services\CloserRotation::projectCloserIds()`: rows for the lead's own `group_id` first; if that agency has none **and** the lead has a group, it falls back to the `group_id IS NULL` platform rows (`src/AppointmentEngine/Services/CloserRotation.php:368-382`).

---

## `ae_workflows` — one automation an agency has built

Usually one per project, but a project-less catch-all is legitimate (`src/AppointmentEngine/Workflow.php:18-24`).

| Column | Type | Meaning |
|---|---|---|
| `id` | bigint unsigned PK | |
| `uuid` | char(36) UNIQUE | |
| `group_id` | bigint unsigned NULL | → `groups.id` |
| `name` | varchar(191) NOT NULL | |
| `description` | varchar(191) NULL | |
| `plan` | json NULL | the **editable** truth since the 2026-09-06 simple-editor redesign: a flat step list the project page edits like a Messages flow. The node/edge graph stays the RUNNER's truth and `Services\PlanCompiler` rebuilds it from `plan` on every save. NULL = a pre-redesign workflow not yet rebuilt (`database/migrations/2026_09_06_230000_add_plan_to_ae_workflows_table.php:7-12`) |
| `ae_project_id` | bigint unsigned NULL | → `ae_projects.id` |
| `is_active` | tinyint(1) NOT NULL default 0 | live or not — separate from `published_at` so a workflow can be paused and resumed without losing when it first went live |
| `published_at` | timestamp NULL | |
| `created_by` / `updated_by` / `deleted_by` | bigint unsigned NULL | |
| `created_at` / `updated_at` / `deleted_at` | timestamp NULL | soft-deletable |

Casts: `plan` → array, `is_active` → boolean, `published_at` → datetime. Relationships: `nodes()`, `edges()` on `ae_workflow_id`; `project()` on `ae_project_id`.

### The `plan` JSON document (`src/AppointmentEngine/Support/Plan.php:5-22`)

`Plan::normalize()` whitelists and type-coerces every save, so the stored document carries **only** these keys whatever the client sent.

| Key | Shape |
|---|---|
| `entry` | one of `Plan::ENTRIES` = `['sheet','ctwa','keyword']` |
| `entry_config` | per-entry keys — sheet: `access` (`link`\|`service_account`), `sheet_url`, `worksheet`, `poll_minutes` (1–1440, default 15), `on_first_sync` (`new_only`\|`enrol_all`), `pace_per_minute` (1–10, default 2); ctwa: `channel_id`, `ad_id`, `fallback_keywords`; keyword: `channel_id`, `keywords`, `match` (`contains`\|`starts_with`\|`exact`) |
| `steps[]` (max 20) | `{kind:'call', pos, wait:{value 0-999, unit minutes\|hours\|days, business_hours bool}, profile_id, max_attempts 1-5, retry_gaps[{value,unit}]}` or `{kind:'message', pos, wait, channel_id, mode template\|text, template_id, variables[], body}` |
| `hours` | `{start,end}` `H:i`, must be a same-day window; anything malformed falls back to `09:00`–`21:00` |
| `layout` | `{start\|stop\|booked\|chat: {x,y}\|null}` — presentation only, the compiler never reads it |
| `chat` | `{enabled, profile_id, goal, objective}`. `Plan::CHAT_GOALS` has exactly one key, `appointment`; a hand-tuned non-empty objective is never overwritten |
| `booked` | `{channel_id, confirm_template_id, variables[], reminder_hours_before (0–168, default 16), reminder_template_id, assign, assignment{…}, showroom_address, showroom_note, showroom_map_url}` |

`Plan::ENTRY_TYPES` maps entry → compiled trigger node type: `sheet` → `trigger.google_sheet`, `ctwa` → `trigger.ctwa`, `keyword` → `trigger.whatsapp_keyword` (`Plan.php:53-57`).

`Plan::ASSIGNMENT_DEFAULTS` (`Plan.php:40-43`) — read by any plan saved before 2026-09-08, which has no `assignment` block: `pool_type='project'`, `team_id=null`, `admin_ids=[]`, `strategy='round_robin'`, `require_zoom=true`, `accept_minutes=15`. `normalizeAssignment()` clamps `pool_type` to `project|group|team|admins`, `strategy` to `round_robin|balance`, `accept_minutes` to 0–240.

### `Workflow::problems()` — the publish gate

Checked on demand, not enforced edit-by-edit: a half-built graph is the normal state of a canvas someone is working on; the check is what stands between a half-built graph and a LIVE one (`Workflow.php:61-141`). It returns human sentences and blocks going live. It reports: no trigger / more than one trigger; fewer than two nodes; any non-trigger node with no incoming edge; any declared output branch with no edge (unless the branch is `default` and the node's spec sets `may_end`); `missingConfig()` per node; then three per-type blocks — `sheetProblems()`, `aiCallProblems()`, `whatsappAiProblems()`.

`aiCallProblems()` (`Workflow.php:155-185`) blocks deliberately, because "an AI call whose profile cannot capture an appointment time books nothing, silently, forever". It batches the profile lookup **scoped to `$this->group_id`**, mirroring the runtime's exact scoping. A profile whose `objectiveGoal()` is not `AiCallProfile::GOAL_APPOINTMENT` must have an extraction field named in `AiCall::APPOINTMENT_KEYS`, or publishing is blocked.

`sheetProblems()` blocks when the URL is filled but `SheetRef::fromUrl()` cannot parse it, and when `access === 'service_account'` while no verified `Connection` with `provider = google_sheets` exists for the group.

---

## `ae_workflow_nodes` — one step on the canvas

| Column | Type | Meaning |
|---|---|---|
| `id` | bigint unsigned PK | |
| `uuid` | char(36) UNIQUE | |
| `ae_workflow_id` | bigint unsigned NOT NULL | → `ae_workflows.id` |
| `type` | varchar(64) NOT NULL | a key of `NodeCatalogue::types()` |
| `name` | varchar(191) NULL | the person's own name; NULL renders the type's name |
| `config` | json NULL | the values the person chose |
| `x` / `y` | int NOT NULL default 0 | canvas position |
| `created_at` / `updated_at` | timestamp NULL | |

**No `group_id`** — scoped through its workflow. Casts: `config` → array, `x`/`y` → integer.

Everything about what a step *is* comes from `NodeCatalogue` at read time — its group, outputs and field schema. Copying the catalogue into each row "would freeze a node at the shape it had when it was dropped, and a fix to a node type would never reach the workflows already using it" (`src/AppointmentEngine/WorkflowNode.php:9-18`).

Methods: `spec()`, `group()` (defaults to `GROUP_ACTION`), `outputs()` (defaults to `['default']`), `needs()`, `displayName()`, `config(string $key)`, `missingConfig()`.

`config($key)` **falls back to the schema default** when the stored value is absent, `null` or `''` — a field added to a node type after a workflow was built has no stored value, and rendering null there would show an empty box where the default is what actually happens (`WorkflowNode.php:92-117`).

`needs()` is per-NODE, not per-type: `trigger.google_sheet` needs `Connection::SHEETS` only when `config('access') === 'service_account'`; everything else answers from the catalogue's `needs` key (`WorkflowNode.php:59-76`).

`missingConfig()` adds three rules the flat `required` flag cannot express (`WorkflowNode.php:124-160`): a **WhatsApp-sending** step is identified by carrying a `template_id` field — *not* by having a `mode` field, because "Assign an agent" has a mode too — and then needs `body` when `mode === 'text'`, else `template_id`; and `end.booked` needs `confirm_template_id`, plus `reminder_template_id` whenever `reminder_hours_before > 0`.

### The node catalogue (`src/AppointmentEngine/NodeCatalogue.php`)

Groups (`:27-30`): `GROUP_TRIGGER='trigger'`, `GROUP_ACTION='action'`, `GROUP_CONDITION='condition'`, `GROUP_END='end'`.
Stages — WHERE in the journey (`:37-45`): `STAGE_LEAD_GEN='lead_gen'` (Lead generation), `STAGE_APPOINTMENT='appointment'` (Appointment), `STAGE_CLOSING='closing'` (Closing).
Kinds — WHO does it (`:52-68`): `KIND_TRIGGER='trigger'`, `KIND_AI_ACTION='ai_action'`, `KIND_AI_ANALYSIS='ai_analysis'`, `KIND_HUMAN='human'`, `KIND_CONDITION='condition'`, `KIND_SYSTEM='system'`, `KIND_END='end'`.

Every valid `type` value, with the branches its edges may carry:

| `type` | group | stage | kind | outputs | needs | may_end |
|---|---|---|---|---|---|---|
| `trigger.meta_lead_form` | trigger | lead_gen | trigger | default | meta | |
| `trigger.whatsapp_keyword` | trigger | lead_gen | trigger | default | whatsapp_channel | |
| `trigger.ctwa` | trigger | lead_gen | trigger | default | whatsapp_channel | |
| `trigger.manual` | trigger | lead_gen | trigger | default | — | |
| `trigger.google_sheet` | trigger | lead_gen | trigger | default | *(per node)* | |
| `action.assign_agent` | action | lead_gen | human | default | — | |
| `action.whatsapp` | action | appointment | ai_action | default | whatsapp_channel | |
| `action.email` | action | appointment | ai_action | default | — | |
| `action.ai_call` | action | appointment | ai_action | default | caller | |
| `action.whatsapp_ai` | action | appointment | ai_action | `booked`, `no_booking` | whatsapp_channel | |
| `action.wait` | action | appointment | system | default | — | |
| `action.set_stage` | action | appointment | system | default | — | |
| `action.notify_team` | action | appointment | system | default | — | |
| `condition.answered` | condition | appointment | condition | `yes`, `no` | — | |
| `condition.call_outcome` | condition | appointment | condition | `booked`, `objection`, `no_answer`, `gave_up`, `invalid` | — | |
| `condition.booked` | condition | appointment | condition | `yes`, `no` | — | |
| `condition.replied` | condition | appointment | condition | `yes`, `no` | — | |
| `condition.field` | condition | appointment | ai_analysis | `yes`, `no` | — | |
| `ai.qualify` | action | appointment | ai_analysis | default | — | |
| `action.zoom_invite` | action | closing | ai_action | default | whatsapp_channel | |
| `human.record_outcome` | action | closing | human | default | — | |
| `condition.attended` | condition | closing | condition | `yes`, `no` | — | |
| `end.closed` | end | closing | end | *(none)* | — | |
| `end.booked` | **action** | appointment | ai_action | default | whatsapp_channel | **yes** |
| `end.stop` | end | closing | end | *(none)* | — | |

Note `end.booked` is catalogued in `GROUP_ACTION` with `may_end = true` — a booking may be the last step, or the flow may continue to closing.

**Config keys that are foreign keys by convention** (all inside the `config` JSON): `channel_id` → `whatsapp_channels.id`; `template_id` / `confirm_template_id` / `reminder_template_id` → `whatsapp_templates.id` (`src/AppointmentEngine/Runner/WhatsappSender.php:75`); `profile_id` → `ai_call_profiles.id` (`Workflow.php:94-98`); `flow_id` → `whatsapp_flows.id` (`Workflow.php:201`); `team_id` → `teams.id`. **`script_id` and `knowledge_id` store a `ae_content_items.uuid`, not its id** — `ContentItem::where('uuid', $config[$key])` (`src/AppointmentEngine/Services/ProfileKnowledge.php:45-46`).

---

## `ae_workflow_edges` — one connection between two steps

A row rather than a line in a JSON blob, because an edge has its own identity: it is deleted on its own, it carries which way out of a condition it represents, and the runner asks "what follows this?" on every step of every lead (`src/AppointmentEngine/WorkflowEdge.php:9-16`).

| Column | Type | Meaning |
|---|---|---|
| `id` | bigint unsigned PK | |
| `uuid` | char(36) UNIQUE | |
| `ae_workflow_id` | bigint unsigned NOT NULL | → `ae_workflows.id` |
| `from_node_id` | bigint unsigned NOT NULL | → `ae_workflow_nodes.id` |
| `to_node_id` | bigint unsigned NOT NULL | → `ae_workflow_nodes.id` |
| `branch` | varchar(16) NOT NULL default `default` | which way out of a condition |
| `created_at` / `updated_at` | timestamp NULL | |

**`UNIQUE (from_node_id, branch)`** — one connection per output. "A second edge off the same branch would fork the lead down two paths at once, which is not a workflow anyone can reason about" (`database/migrations/2026_08_30_100000_create_ae_workflow_tables.php:83-86`).

Constants (`WorkflowEdge.php:21-23`): `BRANCH_DEFAULT = 'default'`, `BRANCH_YES = 'yes'`, `BRANCH_NO = 'no'`. Note these three do **not** cover every branch a node may declare — `action.whatsapp_ai` emits `booked`/`no_booking` and `condition.call_outcome` emits five — and `branch` is in fact **validated nowhere** — not against `NodeCatalogue`, not against these constants. It does not need to be: every edge is created server-side by `Services/PlanCompiler.php:79` or `WorkflowTemplates.php:322-326`, and no client-supplied branch ever reaches a `WorkflowEdge::create`.

No `group_id`, no casts. Relationships: `fromNode()`, `toNode()`.

---

## `ae_workflow_runs` — one lead going through one workflow

Created **PENDING** by whatever put the lead in and picked up by the runner, so a lead is never lost between "we decided to run this" and "it started" (`src/AppointmentEngine/WorkflowRun.php:9-15`).

| Column | Type | Meaning |
|---|---|---|
| `id` | bigint unsigned PK | |
| `uuid` | char(36) UNIQUE | also the token stashed in a WhatsApp flow run's `meta->ae->run` |
| `group_id` | bigint unsigned NULL | → `groups.id` |
| `ae_lead_id` | bigint unsigned NOT NULL | → `ae_leads.id` |
| `ae_workflow_id` | bigint unsigned NOT NULL | → `ae_workflows.id` |
| `current_node_id` | bigint unsigned NULL | → `ae_workflow_nodes.id`; NULL until the runner starts it |
| `status` | varchar(16) NOT NULL default `pending` | `WorkflowRun::STATUS_*` |
| `resume_at` | timestamp NULL | timer wake-up |
| `waiting_for` | varchar(32) NULL | the HUMAN event a parked run resumes on. "A run parked on nothing in particular could only be resumed by a timer, and a person's action would go unnoticed" (`database/migrations/2026_08_30_150000_runner_columns_and_run_logs.php:7-13`) |
| `steps` | int unsigned NOT NULL default 0 | **loop fuse** — a graph is acyclic by construction, but a retry storm must not walk one lead a thousand steps |
| `context` | json NULL | the run's scratchpad (below) |
| `last_error` | varchar(191) NULL | set only on `failed`, truncated to 250 chars |
| `started_at` / `finished_at` | timestamp NULL | |
| `created_by` | bigint unsigned NULL | → `users.id` |
| `created_at` / `updated_at` | timestamp NULL | |

Not soft-deletable; `LeadEraser` hard-deletes runs.

### Status (`WorkflowRun.php:20-34`)

| Constant | Value | Label | Colour |
|---|---|---|---|
| `STATUS_PENDING` | `pending` | Waiting to start | slate |
| `STATUS_RUNNING` | `running` | In progress | sky |
| `STATUS_WAITING` | `waiting` | Waiting on a step | amber |
| `STATUS_DONE` | `done` | Finished | emerald |
| `STATUS_STOPPED` | `stopped` | Stopped | slate |
| `STATUS_FAILED` | `failed` | Failed | rose |

`isOpen()` is true for exactly `pending`, `running`, `waiting` (`WorkflowRun.php:69-72`) — the same three **`enrol()`** tests before starting a second run for the same (lead, workflow) (`Triggers.php:620-628`).

> ⚠️ **There are TWO enrolment guards and they are not the same.** `enrol()` blocks only while a run is OPEN, so a lead whose run finished can be enrolled again. **`enrolOnce()`** (`Triggers.php:514-522`), used by the pull/sheet path, tests `WorkflowRun::where('ae_lead_id')->where('ae_workflow_id')->exists()` with **no status filter** — one run per (lead, workflow) *ever*. Its docblock calls itself "stricter than `enrol()`" on purpose: a pull source re-sees rows whenever deletions shift indexes, and "the AI rang me again because a row above mine was tidied away" is not explainable to anyone.

### Wait tokens (`WorkflowRun.php:45-51`)

`WAIT_CALL = 'call_result'`, `WAIT_OUTCOME = 'outcome'`, `WAIT_VISIT = 'visit'`, `WAIT_VISIT_ANALYSED = 'visit_analysed'`, `WAIT_REPLY = 'reply'`, `WAIT_WA_BOOKING = 'wa_booking'`.

### `context` keys observed in the code

| Key | Written by | Meaning |
|---|---|---|
| `call_id`, `call_attempts` | `Handlers\AiCall:246` | the current call row and the attempt counter |
| `call_exhausted` | `Handlers\AiCall:114,170` | the retry ladder is spent |
| `analysis_nudged` | `Handlers\AiCall:96` | a re-poll for the provider's analysis was already asked for |
| `wait_node` | `Handlers\Wait:18,36` | which node the timer belongs to; removed when it fires |
| `replied_since` | `Handlers\ConditionReplied:26` | ISO timestamp the reply window opened |
| `wa_booking` | `Handlers\WhatsappAiTakeover:107` | `{flow_id, channel_id, dispatched_at, deadline}`; `RecordWhatsappBooking:113-116` adds `appointment_id`. Cleared by `clear()` so a later `whatsapp_ai` node starts fresh |
| `appointment_id`, `link`, `meeting_link`, `venue`, `venue_link` | `Handlers\EndBooked:50-57` | the appointment this confirmation is about, **pinned** so a reminder hours later renders the same booking even if a newer one exists |
| `zoom_meeting_id`, `link` | `Handlers\ZoomInvite:39,92` | the provisioned meeting |

Casts: `resume_at`/`started_at`/`finished_at` → datetime, `context` → array, `steps` → integer.

Method `log(string $event, string $summary, ?array $detail = null, ?WorkflowNode $node = null)` writes one `WorkflowRunLog` row, defaulting `node_id`/`node_type` to the run's current node and **truncating the summary to 250 chars** (`WorkflowRun.php:82-93`).

---

## `ae_workflow_run_logs` — the append-only trail

| Column | Type | Meaning |
|---|---|---|
| `id` | bigint unsigned PK | |
| `ae_workflow_run_id` | bigint unsigned NOT NULL | → `ae_workflow_runs.id` |
| `node_id` | bigint unsigned NULL | → `ae_workflow_nodes.id` |
| `node_type` | varchar(64) NULL | copied at write time so the line still reads after the node is deleted |
| `event` | varchar(24) NOT NULL | |
| `summary` | varchar(191) NOT NULL | the sentence shown on the lead page |
| `detail` | json NULL | |
| `created_at` | timestamp NULL, indexed | |

**`public const UPDATED_AT = null`** — the model has no `updated_at`, matching the table (`src/AppointmentEngine/WorkflowRunLog.php:12`). No `uuid`, no `group_id`.

`event` values written by `Runner\WorkflowRunner`: `resumed` (:127), `started` (:148), `done` (:179), `waiting` (:206), `parked` (:218), plus the finish status itself — `done` / `stopped` / `failed` — via `finish()` (:238-249). Handlers add `booking` and `assign`. Historical rows also carry `simulated`, whose writer no longer exists.

---

## `ae_calls` — one call ledger for AI *and* human calls

Four columns exist here because the CRM's `ai_voice_calls` lacked them, and each gap cost something real (`database/migrations/2026_08_29_170000_create_ae_calls_tables.php:7-29`).

| Column | Type | Meaning |
|---|---|---|
| `id` | bigint unsigned PK | |
| `uuid` | char(36) UNIQUE | |
| `group_id` | bigint unsigned NULL | → `groups.id` |
| `channel` | varchar(8) NOT NULL default `ai` | `ai` or `human`. Defaulted rather than nullable so AI-only queries never have to say `channel = 'ai' OR channel IS NULL` |
| `made_by` | bigint unsigned NULL | → `users.id`; set only on a human call |
| `ae_lead_id` | bigint unsigned NULL | → `ae_leads.id` |
| `provider_call_id` | varchar(191) NULL **UNIQUE** | Retell's id; the webhook idempotency key |
| `to_number` | varchar(32) NULL | |
| `status` | tinyint unsigned NOT NULL | what the PHONE did |
| `outcome` | tinyint unsigned NULL | what the BUSINESS got |
| `disconnection_reason` | varchar(191) NULL | the provider's raw reason, truncated to 50 chars on write |
| `refusal_reason` | varchar(40) NULL | why WE declined to dial. **Any non-null value means the phone never rang** |
| `duration_seconds` | int unsigned NULL | |
| `provider_cost` | decimal(10,4) NULL | Retell, converted from US cents |
| `telephony_cost` | decimal(10,4) NULL | the carrier, billed separately and rounded up to whole minutes. Storing only one "made cost-per-call read at half the truth" |
| `lead_spoke` | tinyint(1) NOT NULL default 0 | true only if a `user` turn exists |
| `analysis` | json NULL | Retell's whole `call_analysis` object |
| `notes` | text NULL | what a person wrote after a human call; the AI's equivalent is `analysis` |
| `recording_url` | varchar(1024) NULL | |
| `started_at` / `ended_at` / `analyzed_at` | timestamp NULL | |
| `created_by` / `updated_by` | bigint unsigned NULL | |
| `created_at` / `updated_at` | timestamp NULL | |

Composite indexes `(group_id, created_at)` and `(group_id, ae_lead_id)`. Not soft-deletable.

### Status — what the phone did (`src/AppointmentEngine/Call.php:23-42`)

| Constant | Value | Label | Colour |
|---|---|---|---|
| `STATUS_REFUSED` | `0` | Not called | slate |
| `STATUS_PENDING` | `1` | Pending | slate |
| `STATUS_IN_PROGRESS` | `2` | In progress | sky |
| `STATUS_COMPLETED` | `3` | Completed | emerald |
| `STATUS_NO_ANSWER` | `4` | No answer | amber |
| `STATUS_BUSY` | `5` | Busy | amber |
| `STATUS_VOICEMAIL` | `6` | Voicemail | amber |
| `STATUS_FAILED` | `7` | Failed | rose |

`STATUS_REFUSED = 0` means we never reached the provider — see `refusal_reason`; the phone never rang.

### Outcome — what the business got (`Call.php:44-56`)

| Constant | Value | Label | Colour |
|---|---|---|---|
| `OUTCOME_APPOINTMENT` | `1` | Appointment set | emerald |
| `OUTCOME_CALLBACK` | `2` | Call-back asked | sky |
| `OUTCOME_NOT_INTERESTED` | `3` | Not interested | slate |
| `OUTCOME_DO_NOT_CALL` | `4` | Do not call | rose |
| `OUTCOME_UNCLEAR` | `5` | Unclear | amber |

### Refusal reasons (`Call.php:58-71`)

| Constant | Value | Label |
|---|---|---|
| `REFUSAL_QUIET_HOURS` | `quiet_hours` | Outside calling hours |
| `REFUSAL_BUDGET` | `daily_budget` | Daily budget reached |
| `REFUSAL_BLOCKED` | `blocked_number` | Number on the do-not-call list |
| `REFUSAL_NO_PHONE` | `no_phone` | No phone number (hidden by WhatsApp) |
| `REFUSAL_NOT_CONNECTED` | `not_connected` | AI caller not connected |

Note `REFUSAL_REASONS` lists `no_phone` **before** `not_connected`, unlike the constant declaration order — labels only, no behaviour rides on it.

**Refusal is evaluated in a fixed order**, and the order matters because only the first reason is recorded. `Handlers\AiCall` first short-circuits on a missing phone → `REFUSAL_NO_PHONE` (`AiCall.php:64-65`), then evaluates a `match(true)` ladder (`AiCall.php:195-199`): connection missing or `verified_at` null → `not_connected`; profile null or not callable → `not_connected`; `BlockedNumber::blocks()` → `blocked_number`; outside the node's window → `quiet_hours`; daily budget exhausted → `daily_budget`.

### Channels (`Call.php:78-84`)

`CHANNEL_AI = 'ai'` (AI, violet), `CHANNEL_HUMAN = 'human'` (Person, sky). One ledger, because "a lead's call history is one story — the AI rang twice and then a person followed up".

### Casts, relationships, helpers

Casts: `status`/`outcome`/`duration_seconds` → integer, `provider_cost`/`telephony_cost` → `decimal:4`, `lead_spoke` → boolean, `analysis` → array, `started_at`/`ended_at`/`analyzed_at` → datetime.

Relationships: `lead()` on `ae_lead_id`; `turns()` → `CallTurn` on `ae_call_id` **ordered by `seq`**; `maker()` → `User` on `made_by`.

- `wasRefused(): bool` — true when `status === STATUS_REFUSED` **or** `refusal_reason !== null`. "Must never render as a missed call."
- `totalCost(): ?float` — NULL when both cost columns are null, deliberately not `0.0`, "because a zero on a total reads as *this call was free*".
- `analysisIsEvidence(): bool` — `lead_spoke && analyzed_at !== null`. The provider analyses every completed call **including ones where only the agent spoke**, and answers the whole extraction schema from the agent's own words; an 11-second call with zero customer turns once produced five "facts" about that person. Anything writing a fact about a human must ask this first (`Call.php:140-150`).

`Services\RetellCallMapper` owns the provider mapping: `status()` derives the status from `disconnection_reason` first and only falls back to `call_status`; `settle()` converts `call_cost.combined_cost` from **cents to dollars, 4dp**, computes `duration_seconds` with `abs()` (Carbon 3's `diffInSeconds` is signed and `max(0, …)` silently zeroed every duration), sets `lead_spoke` from the presence of a `user` turn, and **replaces the turn rows whole** so Retell's retries are idempotent (`src/AppointmentEngine/Services/RetellCallMapper.php:34-80`).

---

> ⚠️ **Half of `ae_calls`' stated design has no producer.** `Call.php:79-83` describes it as one ledger for AI *and* human calls, but both writers hard-code `CHANNEL_AI` (`AiCall.php:59`, `:203`) and all 13 read sites filter `->where('channel', Call::CHANNEL_AI)`. `CHANNEL_HUMAN`, `made_by` and `notes` are never written.

## `ae_call_turns` — one utterance

| Column | Type | Meaning |
|---|---|---|
| `id` | bigint unsigned PK | |
| `ae_call_id` | bigint unsigned NOT NULL | → `ae_calls.id` |
| `seq` | smallint unsigned NOT NULL | 0-based, assigned in transcript order |
| `role` | varchar(16) NOT NULL | who spoke |
| `content` | text NULL | truncated to 4000 chars on write |
| `offset_ms` | int unsigned NULL | from the first word's start time |
| `created_at` / `updated_at` | timestamp NULL | |

**`UNIQUE (ae_call_id, seq)`**. No `uuid`, no `group_id`. Turns are rows and not a blob on the call so that "did the customer speak?" is a `COUNT` rather than a JSON parse on every read (`database/migrations/2026_08_29_170000_create_ae_calls_tables.php:68-70`).

Constants: `ROLE_AGENT = 'agent'`, `ROLE_LEAD = 'lead'` (`src/AppointmentEngine/CallTurn.php:11-12`). Casts: `seq`, `offset_ms` → integer. Relationship: `call()`.

---

## `ae_closer_handoffs` — every offer of a booking to a closer

Two jobs in one table: the Telegram accept/pass links resolve to a row, and the round-robin reads "who was offered least recently" from it, so the rotation self-heals as people join and leave the pool — there is no pointer to drift (`database/migrations/2026_09_08_100000_create_ae_closer_handoffs_table.php:7-15`).

| Column | Type | Meaning |
|---|---|---|
| `id` | bigint unsigned PK | |
| `uuid` | char(36) UNIQUE | the public id inside the accept/pass links |
| `group_id` | bigint unsigned NULL | → `groups.id` |
| `ae_lead_id` | bigint unsigned NOT NULL | → `ae_leads.id` |
| `appointment_id` | bigint unsigned NULL | → **`appointments.id`** |
| `ae_workflow_run_id` | bigint unsigned NULL | → `ae_workflow_runs.id` |
| `admin_id` | bigint unsigned NOT NULL | → **`users.id`** |
| `status` | int unsigned NOT NULL default 1 | `CloserHandoff::STATUS_*` |
| `attempt` | int unsigned NOT NULL default 1 | 1 = first offer for this booking, 2 = after the first passed/expired… |
| `expires_at` | timestamp NULL | the accept window (`assignment.accept_minutes`, default 15) |
| `accepted_at` / `passed_at` / `expired_at` | timestamp NULL | |
| `note` | varchar(200) NULL | a sentence for the run log ("Passed by Zen", "No answer in 15 min") |
| `created_at` / `updated_at` | timestamp NULL | |

| Constant | Value | Label | Colour |
|---|---|---|---|
| `STATUS_PENDING` | `1` | Waiting for accept | amber |
| `STATUS_ACCEPTED` | `2` | Accepted | emerald |
| `STATUS_PASSED` | `3` | Passed | slate |
| `STATUS_EXPIRED` | `4` | No answer | rose |

The model does **not** use `HasUuid`; it sets the uuid in its own `booted()` `creating` hook (`src/AppointmentEngine/CloserHandoff.php:46-51`), so `getRouteKeyName()` is still `id` — `HandoffsController` therefore looks the row up explicitly with `where('uuid', $id)` (`app/Http/Controllers/Manage/AppointmentEngine/HandoffsController.php:78`). No `RecordsBlame`.

Casts: `status`, `attempt` → integer; the four timestamps → datetime. Relationships: `lead()`, `appointment()` → `Src\Appointment\Appointment`, `admin()` → `User`, `run()` → `WorkflowRun`. Helper `isPending()`.

**Related, but code-only:** `Services\CloserRotation` defines the pool and strategy vocabularies read out of `plan.booked.assignment` — `POOL_PROJECT='project'`, `POOL_GROUP='group'`, `POOL_TEAM='team'`, `POOL_ADMINS='admins'`; `STRATEGY_ROUND_ROBIN='round_robin'`, `STRATEGY_BALANCE='balance'`, `STRATEGY_SPECIFIC='specific'`, `STRATEGY_RULES='rules'`; `CLASH_MINUTES = 60` (`src/AppointmentEngine/Services/CloserRotation.php:60-86`). It also **writes `ae_leads.team_id`** on assignment: `['assigned_admin_id' => $closer->id, 'team_id' => $closer->admin?->team_id ?: $lead->team_id]` (`CloserRotation.php:210`).

---

## `ae_sheet_cursors` — how far a Google-Sheet trigger has read

Runtime state that **cannot** live in the node's `config`, because the step drawer rebuilds `config` from the field schema on every save and would wipe anything the sweep stashed there (`database/migrations/2026_09_01_100000_create_ae_sheet_cursors_table.php:7-17`).

| Column | Type | Meaning |
|---|---|---|
| `id` | bigint unsigned PK | |
| `ae_workflow_node_id` | bigint unsigned NOT NULL **UNIQUE** | → `ae_workflow_nodes.id`; one row per trigger node |
| `group_id` | bigint unsigned NULL | → `groups.id` |
| `fingerprint` | varchar(64) NULL | `sha1(spreadsheetId | gid-or-tab | access mode)` — WHICH sheet the cursor counts rows of |
| `rows_seen` | int unsigned NOT NULL default 0 | data rows (header excluded) already processed |
| `baseline_rows` | int unsigned NOT NULL default 0 | the connect-time size of the sheet |
| `last_polled_at` | timestamp NULL | |
| `last_error` | varchar(500) NULL | the user-facing sentence from the last failed read, shown in the step drawer — "a sheet that silently stops importing is the worst failure mode this feature has" |
| `created_at` / `updated_at` | timestamp NULL | |

No `uuid`. Casts: `rows_seen`, `baseline_rows` → integer, `last_polled_at` → datetime.

Cursor semantics, documented on the model (`src/AppointmentEngine/SheetCursor.php:7-24`):

- A first poll — **or a poll after the `fingerprint` changed** (URL, tab or access mode) — treats the sheet as fresh. With `on_first_sync = new_only` the cursor baselines at the current row count and enrols nothing; with `enrol_all` it starts at zero.
- Rows appended after that are new: indexes `>= rows_seen`.
- Rows deleted mid-sheet clamp `rows_seen` down to the new count; rows that shift into the "new" window are absorbed by the enrolment dedupe (one run per lead per workflow, ever).
- **Rows edited above the cursor are never re-read** — a documented limitation: with no write-back there is nothing to reconcile an edit against.

`baseline_rows` exists separately from `rows_seen` because "sync everyone, call only the leads that arrive after us" cannot survive a multi-poll baseline: a 5 000-row sheet baselines over several bounded polls, and without this column poll #2 would treat the rest of the backlog as fresh arrivals and dial them (`database/migrations/2026_09_06_120000_add_baseline_rows_to_ae_sheet_cursors_table.php:7-16`).

---

## `ae_settings` — one agency's numbers

> ⚠️ **Nothing in the codebase can create a row here.** The only references anywhere are three *reads* — `AiCall.php:306`, `DashboardController.php:104` and `:500` — all through `Setting::forGroup()` (`Setting.php:44-48`), which returns an **unsaved blank model** when no row exists. The table holds 0 rows. So every number below is currently its PHP default, and the daily-call-budget fuse never fires.

| Column | Type | Meaning |
|---|---|---|
| `id` | bigint unsigned PK | |
| `uuid` | char(36) UNIQUE | |
| `group_id` | bigint unsigned NULL **UNIQUE** | → `groups.id`; one row per agency |
| `commission_per_appointment` | decimal(12,2) NULL | gross commission of one attended appointment |
| `cost_per_manual_appointment` | decimal(12,2) NULL | what a person costs to set one by hand |
| `currency` | varchar(3) NOT NULL default `MYR` | |
| `daily_call_budget_usd` | decimal(10,2) NULL | the call fuse — every answered minute is billed twice, and a misconfigured workflow can dial all night (`database/migrations/2026_08_30_110000_add_calling_channel_and_blocklist.php:53-58`) |
| `created_by` / `updated_by` | bigint unsigned NULL | |
| `created_at` / `updated_at` | timestamp NULL | |

**Every money field is nullable and stays null until somebody types it.** The dashboard's largest tile is a commission figure, and any default here would put an invented number in it (`src/AppointmentEngine/Setting.php:9-16`). Named columns rather than a key/value bag, so a typo'd key cannot sit in the database looking saved while the dashboard silently uses a default.

`Setting::forGroup(?int $groupId): self` returns the agency's row **or an unsaved blank one** seeded with `currency = 'MYR'`, so every caller can read the fields without a null check — "we have no row" and "nobody has told us the number" mean the same thing to the screen (`Setting.php:44-48`).

Casts: `commission_per_appointment`, `cost_per_manual_appointment` → `decimal:2`. **`daily_call_budget_usd` is NOT in `$fillable`** and is not cast; it is read as a plain attribute by `Handlers\AiCall:306`.

---

## `ae_connections` — one agency's credentials for one provider

Deliberately not `messaging_credentials`: that table is one row per install, right for a CRM whose install IS the customer. This product is sold per agency, so a shared key would mean one customer's calls billed to another's account (`database/migrations/2026_08_29_230000_create_ae_connections_table.php:7-18`).

| Column | Type | Meaning |
|---|---|---|
| `id` | bigint unsigned PK | |
| `uuid` | char(36) UNIQUE | |
| `group_id` | bigint unsigned NULL | → `groups.id` |
| `provider` | varchar(32) NOT NULL | `caller` \| `meta` \| `google_sheets` |
| `credentials` | text NULL | one `Crypt::encryptString(json_encode(...))` blob, so adding a provider is a catalogue entry rather than a migration |
| `display_identity` | varchar(191) NULL | the bound identity (a phone number, an ad account). Plain text on purpose: it is what the screen shows, and decrypting a blob to render a status line would be silly |
| `is_active` | tinyint(1) NOT NULL default 0 | |
| `verified_at` | timestamp NULL | **the gate** — `Handlers\AiCall` refuses to dial when this is null |
| `last_error` | varchar(191) NULL | |
| `created_by` / `updated_by` | bigint unsigned NULL | |
| `created_at` / `updated_at` | timestamp NULL | |

**`UNIQUE (group_id, provider)`.**

Constants: `META = 'meta'`, `CALLER = 'caller'`, `SHEETS = 'google_sheets'` (`src/AppointmentEngine/Connection.php:26-28`).

`PROVIDERS` (`Connection.php:39-73`) is a catalogue driving the form, validation and per-field masking:

| Provider | Identity field | Fields (`secret` / `required`) | What it blocks when missing |
|---|---|---|---|
| `caller` (AI caller) | `from_number` | `api_key` (secret, required — doubles as the webhook signing secret), `agent_id` (required), `from_number` (required) | AI calling, and every appointment the AI would book |
| `meta` (Meta Ads) | `ad_account_id` | `ad_account_id` (required), `access_token` (secret, required) | Campaigns, cost per lead, cost per appointment |
| `google_sheets` | `null` | `service_account_json` (secret, required, multiline, max 10000) | The Google Sheet trigger, when the sheet is private |

`secret` fields are **never returned to the client — not masked, not partially**: a masked secret still leaks its length, and a screen that can show four characters of a token is a screen that has the token in a payload. Google Sheets has no safe field to echo, so the verifier writes the key's `client_email` into `display_identity` instead.

Casts: `is_active` → boolean, `verified_at` → datetime. **`protected $hidden = ['credentials']`.**

Methods (`Connection.php:98-192`): `credentialValues()` returns `[]` on a `DecryptException` rather than throwing — an `APP_KEY` rotation would otherwise take down every page that merely lists connections; `credential(string $key)`; `putCredentials(array)` **drops blank values** so re-saving a form that shows no secrets cannot wipe the stored ones; `isComplete()` (false when the provider has no field spec at all); `missingFields()` returns the labels, so the screen can say *which*.

---

## `ae_content_items` — parsed AI content

Stored **parsed**, never as an opaque file: "a PDF the AI cannot read is not content, it is an attachment — and a library full of attachments looks exactly like a library full of content until the first call goes badly" (`src/AppointmentEngine/ContentItem.php:10-17`).

| Column | Type | Meaning |
|---|---|---|
| `id` | bigint unsigned PK | |
| `uuid` | char(36) UNIQUE | **what node config's `script_id`/`knowledge_id` store** |
| `group_id` | bigint unsigned NULL | → `groups.id` |
| `ae_project_id` | bigint unsigned NULL | → `ae_projects.id` |
| `kind` | varchar(24) NOT NULL | `knowledge` \| `script` \| `sequence` |
| `category` | varchar(32) NULL | which KIND of knowledge |
| `name` | varchar(191) NOT NULL | |
| `body` | json NULL | the structured form the AI consumes — the `entries` array lifted from the source document |
| `status` | varchar(24) NOT NULL default `draft` | |
| `version` | smallint unsigned NOT NULL default 1 | |
| `source_filename` | varchar(191) NULL | |
| `created_by` / `updated_by` / `deleted_by` | bigint unsigned NULL | |
| `created_at` / `updated_at` / `deleted_at` | timestamp NULL | soft-deletable |

Composite index `(group_id, kind, status)`.

| `KINDS` | Value | Name | Blurb |
|---|---|---|---|
| `KIND_KNOWLEDGE` | `knowledge` | Knowledge base | Facts the AI can answer questions from |
| `KIND_SCRIPT` | `script` | Calling script | What the AI says on the phone |
| `KIND_SEQUENCE` | `sequence` | WhatsApp sequence | The ordered follow-up messages |

| `CATEGORIES` | Value | Name | Blurb |
|---|---|---|---|
| `CATEGORY_PROPERTY` | `property_info` | Property information | Prices, layouts, facilities, location, developer |
| `CATEGORY_SALES` | `sales_technique` | Sales technique | How to open, qualify and close |
| `CATEGORY_OBJECTIONS` | `objection_handling` | Objection handling | What to say when they push back |
| `CATEGORY_PROCESS` | `booking_process` | Booking & loan process | Steps, documents, timelines |
| `CATEGORY_FAQ` | `faq` | Customer Q&A | The questions buyers actually ask |
| `CATEGORY_OTHER` | `other` | Other | Anything that fits nowhere above |

| `STATUSES` | Value | Name | Colour |
|---|---|---|---|
| `STATUS_DRAFT` | `draft` | Draft | slate |
| `STATUS_PENDING_REVIEW` | `pending_review` | Waiting for review | amber |
| `STATUS_ACTIVE` | `active` | Live | emerald |

`STATUS_PENDING_REVIEW` is a gate, not an error condition: the spec forbids unreviewed content reaching the live AI. Casts: `body` → array, `version` → integer. Relationship `project()`. Helper `isLive()` = `status === STATUS_ACTIVE`.

---

## `ae_documents` — an upload on its way to becoming content

The type is a **guess with a confidence attached**, shown back for correction before anything commits: "a pipeline that silently files a calling script as a FAQ produces an AI that answers questions with a sales opener, and nobody finds out until a customer hears it" (`src/AppointmentEngine/Document.php:9-16`).

| Column | Type | Meaning |
|---|---|---|
| `id` | bigint unsigned PK | |
| `uuid` | char(36) UNIQUE | |
| `group_id` | bigint unsigned NULL | → `groups.id` |
| `ae_project_id` | bigint unsigned NULL | → `ae_projects.id` |
| `category` | varchar(32) NULL | a `ContentItem::CATEGORIES` key, carried onto the content item on approval |
| `filename` | varchar(191) NOT NULL | |
| `path` | varchar(191) NULL | storage path |
| `size_bytes` | bigint unsigned NULL | |
| `mime` | varchar(120) NULL | |
| `detected_type` | varchar(32) NULL | the guess; **NULL when not confident enough** |
| `confidence` | tinyint unsigned NULL | 0–100; nulled alongside `detected_type` |
| `status` | varchar(24) NOT NULL default `uploaded` | |
| `extracted` | json NULL | `{title, reason, suggested_type, suggested_confidence, entries[]}` (`app/Jobs/AppointmentEngine/ClassifyDocument.php:121-133`) |
| `last_error` | text NULL | |
| `created_by` | bigint unsigned NULL | |
| `created_at` / `updated_at` | timestamp NULL | **no soft delete, no `updated_by`** |

| `TYPES` | Value | Name | Becomes |
|---|---|---|---|
| `TYPE_WHATSAPP_SEQUENCE` | `whatsapp_sequence` | WhatsApp sequence | The follow-up messages the AI sends |
| `TYPE_CALLING_SCRIPT` | `calling_script` | Calling script | What the AI says on the phone |
| `TYPE_FAQ` | `faq` | Project Q&A | Facts the AI answers questions from |
| `TYPE_OTHER` | `other` | Not recognised | Held for you to classify |

| `STATUSES` | Value | Name | Colour |
|---|---|---|---|
| `STATUS_UPLOADED` | `uploaded` | Uploaded | slate |
| `STATUS_CLASSIFYING` | `classifying` | Reading it | sky |
| `STATUS_PENDING_REVIEW` | `pending_review` | Waiting for review | amber |
| `STATUS_FAILED` | `failed` | Could not read it | rose |

**`status` ends at `PENDING_REVIEW`, not at `active`** — activation is a separate, deliberate act, and it happens on the *content item*, not here. `ClassifyDocument` leaves `detected_type` NULL when the model said `other` or scored below `MIN_CONFIDENCE`, "so the screen says *not read yet* rather than showing a guess as a fact". Approving a document creates a **DRAFT** `ContentItem` (never a live one) carrying `extracted['entries']` as its `body` (`app/Http/Controllers/Manage/AppointmentEngine/IngestController.php:242-254`).

Casts: `extracted` → array, `confidence`, `size_bytes` → integer. Relationship `project()`. `HasUuid` only — no `RecordsBlame`.

---

## `ae_flow_nodes` — the legacy per-agency canvas

Superseded by `ae_workflow_nodes`. One row per step of one agency's fixed six-step flow, seeded once from `CallProfileFlow`'s code defaults the first time an agency opens the canvas, then owned by them (`src/AppointmentEngine/FlowNode.php:11-22`).

| Column | Type | Meaning |
|---|---|---|
| `id` | bigint unsigned PK | |
| `uuid` | char(36) UNIQUE | |
| `group_id` | bigint unsigned NULL | → `groups.id` |
| `node_key` | varchar(32) NOT NULL | the stable identity, matching a `CallProfileFlow` node id. **Edges reference this, never the row id** — a re-seed on a fresh install must not break the graph |
| `type` | varchar(24) NOT NULL | `trigger` \| `whatsapp` \| `call` \| `decision` \| `success` |
| `title` | varchar(191) NOT NULL | |
| `detail` | varchar(191) NULL | |
| `x` / `y` | int NOT NULL default 0 | **canvas pixels**, not grid cells |
| `settings` | json NULL | typed values from the schema |
| `created_by` / `updated_by` | bigint unsigned NULL | |
| `created_at` / `updated_at` | timestamp NULL | |

**`UNIQUE (group_id, node_key)`.** Edges are deliberately *not* in this table: the release that shipped it edited nodes and positions only, so the graph's shape stayed in code where it could not be half-changed into something the engine could not run (`database/migrations/2026_08_29_240500_create_ae_flow_nodes_table.php:14-18`).

`FlowNode::forGroup(?int $groupId, ?int $actorId = null)` seeds inside a `DB::transaction` **only when the agency has no rows at all** — "an agency that deliberately emptied a field never has the default written back over it on the next page load" — and returns rows sorted into `CallProfileFlow`'s authored order, not row order. `setting($key)` falls back to the schema default; `fields()` merges schema + current value and returns **every** field including `locked` ones — it only omits the `value` key for a locked field (`FlowNode.php:127-133`); `requiredConnection()` reads the code default rather than the row, because it is a fact about what the step DOES.

The code catalogue (`src/AppointmentEngine/CallProfileFlow.php:23-48` and `nodes()`): `COLUMN = 232`, `ROW = 132` (grid → pixels, applied once at seeding); types `TYPE_TRIGGER='trigger'` (Trigger), `TYPE_WHATSAPP='whatsapp'` (WhatsApp), `TYPE_CALL='call'` (AI call), `TYPE_DECISION='decision'` (Decision), `TYPE_SUCCESS='success'` (Success). Its six nodes: `inbound` (trigger, needs meta), `notice` (whatsapp), `call` (call, needs caller), `answered` (decision), `booked` (success, needs whatsapp), `nurture` (whatsapp).

---

## `ae_threads` — one WhatsApp conversation

| Column | Type | Meaning |
|---|---|---|
| `id` | bigint unsigned PK | |
| `uuid` | char(36) UNIQUE | |
| `group_id` | bigint unsigned NULL | → `groups.id` |
| `ae_lead_id` | bigint unsigned NULL | → `ae_leads.id` |
| `phone` | varchar(32) NOT NULL | |
| `human_took_over_at` | timestamp NULL | set the moment a human steps in; while NULL the AI is replying |
| `taken_over_by` | bigint unsigned NULL | → `users.id` |
| `flagged_at` | timestamp NULL | a leader asking someone to look |
| `last_message_at` | timestamp NULL | |
| `unread_count` | int unsigned NOT NULL default 0 | |
| `created_by` / `updated_by` | bigint unsigned NULL | |
| `created_at` / `updated_at` | timestamp NULL | |

Composite index `(group_id, last_message_at)`.

**Takeover and flag are separate deliberately**: flagging asks a human to look, taking over stops the AI mid-conversation. Collapsing them would mean a leader cannot say "someone should read this" without also silencing the automation currently doing the work (`src/AppointmentEngine/Thread.php:12-19`).

Casts: `human_took_over_at`, `flagged_at`, `last_message_at` → datetime, `unread_count` → integer. Relationships: `lead()`, `messages()` **ordered by `sent_at`**, `takenOverBy()`. Helper `aiIsHandling()` = `human_took_over_at === null`.

---

## `ae_messages` — one WhatsApp message

| Column | Type | Meaning |
|---|---|---|
| `id` | bigint unsigned PK | |
| `ae_thread_id` | bigint unsigned NOT NULL | → `ae_threads.id` |
| `author` | varchar(16) NOT NULL | `lead` \| `ai` \| `human` \| `unknown` |
| `author_user_id` | bigint unsigned NULL | → `users.id` |
| `provider_message_id` | varchar(128) NULL **UNIQUE** | the wamid |
| `body` | text NULL | |
| `delivery` | varchar(16) NULL | |
| `failure_reason` | varchar(191) NULL | |
| `sent_at` / `delivered_at` / `read_at` | timestamp NULL | |
| `created_at` / `updated_at` | timestamp NULL | |

Composite index `(ae_thread_id, sent_at)`. No `uuid`, no `group_id`.

**Four authors, not two** (`src/AppointmentEngine/Message.php:8-29`). `UNKNOWN` is the one that matters: some outbound rows genuinely cannot carry a sender, and filing them as `human` is how thousands of automated sends come to look like a person typed them — the spec makes "AI-authored content is visually distinct" an acceptance criterion, which cannot hold if the schema has nowhere to put "we don't know".

| Constant | Value | Name | Colour |
|---|---|---|---|
| `LEAD` | `lead` | Lead | slate |
| `AI` | `ai` | AI | violet |
| `HUMAN` | `human` | You | sky |
| `UNKNOWN` | `unknown` | Unattributed | slate |

| Delivery | Value | Name | Colour | `DELIVERY_RANK` |
|---|---|---|---|---|
| `DELIVERY_QUEUED` | `queued` | Sending | slate | 0 |
| `DELIVERY_SENT` | `sent` | Sent | slate | 1 |
| `DELIVERY_DELIVERED` | `delivered` | Delivered | sky | 2 |
| `DELIVERY_READ` | `read` | Read | emerald | 3 |
| `DELIVERY_FAILED` | `failed` | Failed | rose | 4 |

**Delivery only ever moves forward.** Meta's receipts are not ordered — a `delivered` can arrive after a `read`, and applying it would walk a message backwards in front of a supervisor who already saw it was read. `failed` is terminal and ranks above everything, so a stale receipt never overwrites a failure. `advancesTo(string $delivery): bool` is the guard; an unknown key ranks `-1` on both sides (`Message.php:50-89`).

`provider_message_id` is unique and **not scoped to the thread**: a wamid is unique per WhatsApp account, and scoping it would let the same message land twice under two threads if a lead's number were ever re-parented. Without it, "a retried webhook becomes a duplicate in the thread and a delivery receipt becomes a message of its own" (`database/migrations/2026_08_30_090000_add_provider_ids_to_ae_messages_table.php:7-33`).

Casts: `sent_at`, `delivered_at`, `read_at` → datetime. Relationship `thread()`. Helper `isOutbound()` = `author !== LEAD`.

---

## `ae_routing_rules` — first match wins, in `position` order

Scoring would be more flexible and far less explainable, "and the person who has to answer *why did that lead go to him?* reads this list top to bottom" (`src/AppointmentEngine/RoutingRule.php:11-21`).

| Column | Type | Meaning |
|---|---|---|
| `id` | bigint unsigned PK | |
| `uuid` | char(36) UNIQUE | |
| `group_id` | bigint unsigned NULL | → `groups.id` |
| `name` | varchar(191) NOT NULL | |
| `position` | int unsigned NOT NULL default 0 | evaluation order |
| `is_active` | tinyint(1) NOT NULL default 1 | |
| `journey_stage` | varchar(32) NULL | condition; NULL = does not constrain |
| `intent` | varchar(32) NULL | condition |
| `budget_min` / `budget_max` | decimal(12,2) NULL | condition |
| `assign_admin_id` | bigint unsigned NULL | → `users.id`; NULL + `balance_by_load` = spread across the team |
| `balance_by_load` | tinyint(1) NOT NULL default 0 | |
| `matched_count` | int unsigned NOT NULL default 0 | proof it does something — a rule nobody notices firing can be told apart from one that never matches |
| `last_matched_at` | timestamp NULL | |
| `created_by` / `updated_by` | bigint unsigned NULL | |
| `created_at` / `updated_at` | timestamp NULL | |

**A rule with every condition NULL is a catch-all — a legitimate last line, not a bug.**

`matches(?Lead $lead): bool` (`RoutingRule.php:63-94`) short-circuits on `! is_active`, then tests `journey_stage`, then `intent`, then the budget band. **A lead field that is NULL fails any condition naming it**: "budget over 1M" must not match a lead whose budget nobody has asked about, "or the highest-value rule quietly becomes the catch-all for every un-qualified lead in the system". `conditionSummary(): array<string>` builds the words the editor, the list and any audit trail all use, so the same rule cannot be described three different ways; money renders as `RM` + `number_format()`.

Casts: `is_active`, `balance_by_load` → boolean; `position`, `matched_count` → integer; `budget_min`, `budget_max` → `decimal:2`; `last_matched_at` → datetime. Relationship `assignee()`.

---

## `ae_blocked_numbers` — the do-not-call list

| Column | Type | Meaning |
|---|---|---|
| `id` | bigint unsigned PK | |
| `uuid` | char(36) UNIQUE | |
| `group_id` | bigint unsigned NULL | → `groups.id` |
| `phone` | varchar(32) NOT NULL | |
| `reason` | varchar(191) NULL | "a block with no reason is one nobody will ever dare remove" |
| `created_by` | bigint unsigned NULL | → `users.id` |
| `created_at` / `updated_at` | timestamp NULL | |

**`UNIQUE (group_id, phone)`.** No `updated_by`, no soft delete.

> **It does have a write path — in the HOST Calls module, not here.** `POST /manage/calls/ai-calls/{id}/block-number` and `/unblock-number` (`routes/web.php:490-491`, `permission:manage-calls`) reach `AiCallsController::blockNumber()`, which routes the write to whichever blocklist that caller reads: an **AE** call → `BlockedNumber::firstOrCreate(['group_id' => …, 'phone' => $engineCall->to_number], ['reason' => 'Blocked from AI Calls'])` (`AiCallsController.php:155-158`); a **CRM** `AiVoiceCall` → `AiCallBlockedNumberRepository::block()`. The controller states the rule: *"Each caller checks its OWN list before dialling … one written to the other is a promise the dialler never reads."*
>
> ⚠️ **Two blocklists coexist on one screen.** `Support/AiCallBook.php:260` checks this table per group for AE calls; `:304` checks `Src\VoiceAgent\AiCallBlockedNumber` for CRM calls — **with no group filter at all**. Blocking a number on one side does not block it on the other.
> `unblockNumber()` (`:173-195`) loads the whole group list and compares `preg_replace('/\D+/','',…)` in PHP — the same unnormalised O(n) comparison as `BlockedNumber::blocks()`.

**A safety feature, not a preference.** Checked by the caller before every call, *not* by the workflow that asked for one: "a workflow is configuration and can be wrong; someone who asked not to be called must not be called because a step was misconfigured" (`src/AppointmentEngine/BlockedNumber.php:8-13`).

`BlockedNumber::blocks(?int $groupId, string $phone): bool` (`BlockedNumber.php:35-45`) strips every non-digit from both sides and compares **digits only** — a list holding `+60123456789` must still catch `60123456789` arriving from a webhook. It returns false when the incoming number has no digits at all. Note the implementation loads the group's whole list and filters in PHP rather than querying by phone.

---

## `appointments` — THE booking (shared with the CRM)

`Src\Appointment\Appointment`, table `appointments`. Since 2026-09-06 every AE booking is a row here (`database/migrations/2026_09_06_170000_add_ae_columns_to_appointments_table.php:7-16`).

| Column | Type | Meaning |
|---|---|---|
| `id` | bigint unsigned PK | |
| `uuid` | char(36) UNIQUE | route key |
| `lead_id` | bigint unsigned NULL | → CRM `leads.id` |
| `project_id` | bigint unsigned NULL | → `projects.id` |
| `engagement_id` | bigint unsigned NULL | → `engagements.id` |
| `group_id` | bigint unsigned NULL | **AE column** → `groups.id` |
| `ae_lead_id` | bigint unsigned NULL | **AE column** → `ae_leads.id` |
| `ae_call_id` | bigint unsigned NULL | **AE column** → `ae_calls.id` — WHICH call earned the booking |
| `wa_flow_run_id` | bigint unsigned NULL | **AE column** → `whatsapp_flow_runs.id` — the chat-side sibling of `ae_call_id`, and the WhatsApp capture listener's **idempotency key** |
| `assigned_admin_id` | bigint unsigned NULL | **AE column** → `users.id` — the closer the engine assigned |
| `source` | varchar(10) NULL | **AE column** — `ai` \| `human` (`Booking::SOURCE_*`) |
| `scheduled_at` | datetime NOT NULL | KL wall-clock; `now()` is already Asia/KL, so comparisons need no conversion |
| `location` | varchar(255) NULL | |
| `type` | int unsigned NOT NULL default 1 | `Appointment::TYPE_*` |
| `status` | int unsigned NOT NULL default 1 | scheduling lifecycle |
| `outcome` | int unsigned NULL | what happened; **NULL = not recorded, never a no-show** |
| `notes` / `outcome_notes` | text NULL | |
| `zoom_meeting_id` | varchar(30) NULL | **AE column** |
| `meeting_link` | varchar(500) NULL | **AE column** |
| `created_by` / `updated_by` / `deleted_by` | bigint unsigned NULL | |
| `created_at` / `updated_at` / `deleted_at` | timestamp NULL | soft-deletable |

### CRM constants (`src/Appointment/Appointment.php:27-160`)

**Types:** `TYPE_SHOWROOM_VISIT = 1` (Showroom Visit, indigo), `TYPE_VIDEO_CALL = 2` (Video Call, violet), `TYPE_PHONE_CALL = 3` (Phone Call, amber), `TYPE_SITE_VISIT = 4` (Site Visit, emerald).

**Statuses — selectable (`STATUSES`):** `STATUS_SCHEDULED = 1` (Scheduled, brand), `STATUS_CONFIRMED = 2` (Confirmed, indigo), `STATUS_CANCELLED = 7` (Cancelled, slate).

**Statuses — retired, display-only (`ALL_STATUSES` adds these):** `STATUS_ATTENDED = 3`, `STATUS_NO_SHOW = 4`, `STATUS_CLOSED_WON = 5`, `STATUS_CLOSED_LOST = 6`. They are absent from `STATUSES` so `Rule::in` rejects them. **They must not be deleted**: the committed migration `2026_07_14_100004_backfill_engagements_from_appointments` references `STATUS_CLOSED_WON`/`STATUS_CLOSED_LOST`, and removing them fatals any fresh `migrate` / CI run. `STATUS_CANCELLED` keeps the value 7 and is *not* part of the retired block.

**Outcomes:** `OUTCOME_ATTENDED = 1` (Attended, **indigo**), `OUTCOME_NO_SHOW = 2` (No Show, rose), `OUTCOME_FOLLOW_UP_NEEDED = 3` (Follow-up Needed, amber), `OUTCOME_CLOSED = 4` (Closed, emerald), `OUTCOME_NOT_CLOSED = 5` (Not Closed, slate). Attended is indigo, not emerald, deliberately: emerald is reserved for the one outcome that means money.

**Phase — derived, never stored:** `PHASE_UPCOMING='upcoming'`, `PHASE_ONGOING='ongoing'`, `PHASE_AWAITING_OUTCOME='awaiting_outcome'`, `PHASE_DONE='done'`, `PHASE_CANCELLED='cancelled'`; `ONGOING_WINDOW_MINUTES = 60`. There is no `phase` column and deliberately no job that writes one. `getPhaseAttribute()` resolves **top-down, first match wins** (`Appointment.php:310-344`): cancelled → `PHASE_CANCELLED`; `outcome !== null` → `PHASE_DONE`; `scheduled_at` null or future → `PHASE_UPCOMING`; within 60 minutes of the start → `PHASE_ONGOING`; else `PHASE_AWAITING_OUTCOME`. `PHASE_UPCOMING` is absent from `PHASES` so it falls back to the stored status label.

`getOutcomeLabelAttribute()` / `getOutcomeColorAttribute()` carry an **explicit `=== null` guard** rather than the bare `?? null` idiom used elsewhere: `??` suppresses a missing key, not a null offset, and `$map[null]` raises a deprecation on PHP 8.5+. `outcome` is nullable by design for most rows, so it would fire on nearly every render — do not "restore consistency" (`Appointment.php:276-304`).

Relationships: `lead()`, `project()`, `engagement()`, `createdByUser()`, `aeLead()` → `Src\AppointmentEngine\Lead` on `ae_lead_id`, `assignedAdmin()` → `User` on `assigned_admin_id`.

### The AE ⇄ CRM dialect (`src/AppointmentEngine/Support/Booking.php:17-119`)

The engine speaks a *channel* and a *string outcome*; the CRM speaks `type`/`outcome`/`status` integers. **The mapping lives in exactly one place**, because a second copy is how the same appointment ends up read two ways.

| AE constant | Value | Maps to |
|---|---|---|
| `CHANNEL_SHOWROOM` | `showroom` (Showroom, amber) | `TYPE_SHOWROOM_VISIT` |
| `CHANNEL_ZOOM` | `zoom` (Zoom, sky) | `TYPE_VIDEO_CALL` |
| `OUTCOME_ATTENDED` | `attended` (emerald) | `OUTCOME_ATTENDED` |
| `OUTCOME_NO_SHOW` | `no_show` (rose) | `OUTCOME_NO_SHOW` |
| `OUTCOME_CANCELLED` | `cancelled` (slate) | a **status** (`STATUS_CANCELLED`), not an outcome — handled by callers |
| `SOURCE_AI` / `SOURCE_HUMAN` | `ai` / `human` | `appointments.source` |

`channelFor(?int $type)` reads **every non-video type as showroom** — the engine's product only distinguishes "they come to us" from "we meet online". `aeOutcome()` checks cancelled first, so a cancelled row never reports an outcome. `zoomLinkPending()` flags the degraded state an agent must fix by hand: `channel = zoom` with a NULL `meeting_link`, "the appointment is the money row; the link is decoration".

`Support\AppointmentRow::make()` is the single row shape both the global Appointments list and a project's Appointments tab render; its `via` field derives `'call'` vs `'chat'` from `source === 'ai'` and whether `ae_call_id` is set (`src/AppointmentEngine/Support/AppointmentRow.php:24-61`).

---

## Orphan tables

Both still exist in the live schema and both have **no Eloquent model and no reader or writer anywhere in `src/`, `app/`, `routes/` or `resources/`** — only migrations and comments mention them.

### `ae_appointments` (1 row) — retired by the 2026-09-06 merge

Columns: `id`, `uuid`, `group_id`, `ae_lead_id`, `ae_call_id`, `wa_flow_run_id`, `assigned_admin_id`, `source` varchar(16) default `ai`, `scheduled_at`, `channel` varchar(16) NULL, `zoom_meeting_id`, `meeting_link` varchar(500), `outcome` varchar(16) NULL, blame columns, timestamps, `deleted_at`. Its columns migrated onto `appointments` (`database/migrations/2026_08_29_200000_create_ae_appointments_table.php`, `…2026_09_02_100000_add_channel_to_ae_appointments_table.php`). The `channel` column was nullable on purpose: NULL meant a pre-column row, rendered as Showroom but never *claimed* to have been chosen.

### `ae_visits` (0 rows) — the showroom/Zoom recording pipeline

Columns: `id`, `uuid`, `group_id`, `channel` varchar(16) default `showroom`, `ae_lead_id`, `ae_appointment_id`, `admin_id`, `recorded_at`, `duration_seconds`, `audio_path`, `audio_name`, `transcript` mediumtext, `analysis` json, `stage` varchar(16) default `uploaded`, `last_error`, `followed_up_at`, blame, timestamps. Designed as the other half of the showroom question — `ae_appointments` answered "did they turn up", this answered "what happened when they did" — with a documented stage ladder `uploaded → transcribing → analysing → done | failed` and the rule that **the audio is never the record** (`database/migrations/2026_08_30_120000_create_ae_visits_table.php:7-19`). Nothing implements it today.

### Dropped tables

`ae_campaigns` and `ae_campaign_days` were dropped on 2026-09-07. They never had a writer at all — no sync job, no webhook, no import — so both were empty on every install and the page could only draw a blank report; ad performance belongs to the Operations suite's `meta_ad_insights` (`database/migrations/2026_09_07_190000_drop_ae_campaigns_tables.php:7-23`).

---

## Every foreign key by convention

There are no DB-level constraints. This is the complete list of columns that reference another table.

| From | Column | → Table.column |
|---|---|---|
| `ae_leads` | `group_id` | `groups.id` |
| `ae_leads` | `team_id` | `teams.id` |
| `ae_leads` | `lead_id` | `leads.id` (CRM person) |
| `ae_leads` | `wa_contact_id` | `whatsapp_contacts.id` |
| `ae_leads` | `ae_project_id` | `ae_projects.id` |
| `ae_leads` | `assigned_admin_id`, `created_by`, `updated_by`, `deleted_by` | `users.id` |
| `ae_projects` | `group_id` / `project_id` | `groups.id` / `projects.id` |
| `ae_project_closers` | `ae_project_id`, `group_id`, `admin_id`, `created_by`, `updated_by` | `ae_projects.id`, `groups.id`, `users.id` ×3 |
| `ae_workflows` | `group_id`, `ae_project_id`, blame | `groups.id`, `ae_projects.id`, `users.id` |
| `ae_workflow_nodes` | `ae_workflow_id` | `ae_workflows.id` |
| `ae_workflow_nodes` | `config.channel_id` / `.template_id` / `.confirm_template_id` / `.reminder_template_id` / `.profile_id` / `.flow_id` / `.team_id` | `whatsapp_channels.id` / `whatsapp_templates.id` ×3 / `ai_call_profiles.id` / `whatsapp_flows.id` / `teams.id` |
| `ae_workflow_nodes` | `config.script_id` / `.knowledge_id` | **`ae_content_items.uuid`** (not `.id`) |
| `ae_workflow_edges` | `ae_workflow_id`, `from_node_id`, `to_node_id` | `ae_workflows.id`, `ae_workflow_nodes.id` ×2 |
| `ae_workflow_runs` | `group_id`, `ae_lead_id`, `ae_workflow_id`, `current_node_id`, `created_by` | `groups.id`, `ae_leads.id`, `ae_workflows.id`, `ae_workflow_nodes.id`, `users.id` |
| `ae_workflow_run_logs` | `ae_workflow_run_id`, `node_id` | `ae_workflow_runs.id`, `ae_workflow_nodes.id` |
| `ae_calls` | `group_id`, `ae_lead_id`, `made_by`, `created_by`, `updated_by` | `groups.id`, `ae_leads.id`, `users.id` ×3 |
| `ae_call_turns` | `ae_call_id` | `ae_calls.id` |
| `ae_closer_handoffs` | `group_id`, `ae_lead_id`, `appointment_id`, `ae_workflow_run_id`, `admin_id` | `groups.id`, `ae_leads.id`, **`appointments.id`**, `ae_workflow_runs.id`, `users.id` |
| `ae_sheet_cursors` | `ae_workflow_node_id`, `group_id` | `ae_workflow_nodes.id`, `groups.id` |
| `ae_settings` | `group_id`, blame | `groups.id`, `users.id` |
| `ae_connections` | `group_id`, blame | `groups.id`, `users.id` |
| `ae_content_items` | `group_id`, `ae_project_id`, blame | `groups.id`, `ae_projects.id`, `users.id` |
| `ae_documents` | `group_id`, `ae_project_id`, `created_by` | `groups.id`, `ae_projects.id`, `users.id` |
| `ae_flow_nodes` | `group_id`, blame | `groups.id`, `users.id` |
| `ae_flow_nodes` | `node_key` | `CallProfileFlow` node id (code, not a table) |
| `ae_threads` | `group_id`, `ae_lead_id`, `taken_over_by`, blame | `groups.id`, `ae_leads.id`, `users.id` |
| `ae_messages` | `ae_thread_id`, `author_user_id` | `ae_threads.id`, `users.id` |
| `ae_routing_rules` | `group_id`, `assign_admin_id`, blame | `groups.id`, `users.id` |
| `ae_blocked_numbers` | `group_id`, `created_by` | `groups.id`, `users.id` |
| `appointments` | `lead_id`, `project_id`, `engagement_id`, `group_id`, `ae_lead_id`, `ae_call_id`, `wa_flow_run_id`, `assigned_admin_id`, blame | `leads.id`, `projects.id`, `engagements.id`, `groups.id`, `ae_leads.id`, `ae_calls.id`, `whatsapp_flow_runs.id`, `users.id` ×4 |
| `whatsapp_flow_runs` | `meta->ae->run` / `meta->ae->lead` / `meta->ae->node` | **`ae_workflow_runs.uuid` / `ae_leads.uuid` / `ae_workflow_nodes.uuid`** (JSON, by uuid) |

`admin_id` on `ae_project_closers` and `ae_closer_handoffs` points at **`users.id`**, not `admins.id`, despite the name — both migrations say so explicitly, and `CloserRotation::candidates()` builds a `User` query.

## Every migration that shaped this schema

The full list, oldest first — the tables above are the *result*; this is how they got there. (40 files; the three `appointments` ones predate the engine and belong to the CRM's own book, which the engine later merged into.)

| Migration | Date | What it does |
|---|---|---|
| `2026_06_16_100001_create_appointments_table` | 2026-06-16 | Appointments between agents and leads (property owners imported via the FLG flow). |
| `2026_06_16_100002_add_outcome_notes_to_appointments_table` | 2026-06-16 | Adds outcome_notes to capture what happened after a terminal appointment status (Attended, No Show, Closed Won, Closed Lost, Cancelled). |
| `2026_07_14_100003_add_engagement_id_to_appointments_table` | 2026-07-14 | Re-parent appointments onto the per-(lead, project) engagement. |
| `2026_07_14_100004_backfill_engagements_from_appointments` | 2026-07-14 | Seed the new per-(lead, project) engagements from existing appointments: every distinct (lead_id, project_id) that already has an appointment becomes  |
| `2026_07_31_000002_add_outcome_to_appointments_table` | 2026-07-31 | Split "what happened at the appointment" out of `appointments.status` (see plan_doc/appointmentoutcome.md §1). |
| `2026_07_31_000003_backfill_appointment_outcomes` | 2026-07-31 | Move the four retired TERMINAL statuses out of `appointments.status` and into the new `outcome` column added by 2026_07_31_000002 (see plan_doc/appoin |
| `2026_08_29_100001_grant_appointment_engine_permissions` | 2026-08-29 | Creates the AI Appointment System console permissions and grants the VIEW half to the two oversight roles on EXISTING installs. |
| `2026_08_29_160000_create_appointment_engine_tables` | 2026-08-29 | The AI Appointment System's own tables. |
| `2026_08_29_170000_create_ae_calls_tables` | 2026-08-29 | The appointment engine's own call ledger. |
| `2026_08_29_180000_create_ae_threads_tables` | 2026-08-29 | The appointment engine's own WhatsApp threads and messages. |
| `2026_08_29_190000_create_ae_campaigns_tables` | 2026-08-29 | The appointment engine's own Meta campaign records. |
| `2026_08_29_200000_create_ae_appointments_table` | 2026-08-29 | The appointment engine's own showroom appointments. |
| `2026_08_29_210000_create_ae_content_tables` | 2026-08-29 | The AI's own content: what it says, what it knows, and per project. |
| `2026_08_29_220000_create_ae_documents_table` | 2026-08-29 | Uploaded project documents, and what the AI made of each. |
| `2026_08_29_230000_create_ae_connections_table` | 2026-08-29 | Per-agency provider credentials for the appointment engine. |
| `2026_08_29_233000_create_ae_settings_table` | 2026-08-29 | One row per agency: the numbers this product needs told to it. |
| `2026_08_29_234000_create_ae_routing_rules_table` | 2026-08-29 | Who closes which appointment (spec §5.9 / §5.10). |
| `2026_08_29_235000_add_budget_amount_to_ae_leads_table` | 2026-08-29 | A number to route on, alongside the words the lead actually used. |
| `2026_08_29_240500_create_ae_flow_nodes_table` | 2026-08-29 | The automation flow, per agency, as rows the canvas reads and writes. |
| `2026_08_29_243000_reseed_ae_flow_node_settings` | 2026-08-29 | Clears the flow so it re-seeds with TYPED settings. |
| `2026_08_30_090000_add_provider_ids_to_ae_messages_table` | 2026-08-30 | The provider's own id for a message, and what happened to it. |
| `2026_08_30_100000_create_ae_workflow_tables` | 2026-08-30 | Workflows: many per agency, each a graph a person builds. |
| `2026_08_30_120000_create_ae_visits_table` | 2026-08-30 | A recorded showroom conversation. |
| `2026_08_30_130000_add_channel_to_ae_visits_table` | 2026-08-30 | A recorded conversation is a recorded conversation, wherever it happened. |
| `2026_08_30_160000_add_category_to_ae_knowledge` | 2026-08-30 | Knowledge is per PROJECT and per CATEGORY. |
| `2026_09_01_100000_create_ae_sheet_cursors_table` | 2026-09-01 | Where a Google Sheet trigger has read up to. |
| `2026_09_02_100000_add_channel_to_ae_appointments_table` | 2026-09-02 | Where the appointment happens — showroom or Zoom — chosen by the LEAD in conversation (a meeting_type extraction / the chat's booking token) with a pe |
| `2026_09_02_100001_add_ctwa_clid_to_ae_leads_table` | 2026-09-02 | Meta's click id for a lead that arrived via a click-to-WhatsApp ad. |
| `2026_09_03_100000_add_ae_project_id_to_ae_leads_table` | 2026-09-03 | The lead's project as a real key, not a name. |
| `2026_09_06_120000_add_baseline_rows_to_ae_sheet_cursors_table` | 2026-09-06 | The connect-time size of the sheet. |
| `2026_09_06_150000_add_lead_id_to_ae_leads_table` | 2026-09-06 | The bridge to the CRM's person record (Phase 1 of lead convergence, owner-approved 2026-09-06). |
| `2026_09_06_170000_add_ae_columns_to_appointments_table` | 2026-09-06 | The appointment merge (owner-approved 2026-09-06): the Appointment Engine stops keeping its own `ae_appointments` book and writes THE book — `appointm |
| `2026_09_06_210000_add_project_id_to_ae_projects_table` | 2026-09-06 | The bridge to THE project (Phase 1 of project convergence, owner-approved 2026-09-06): `ae_projects` stays the engine's per-deal state (brain, workflo |
| `2026_09_06_230000_add_plan_to_ae_workflows_table` | 2026-09-06 | The simple-editor redesign (2026-09-06): a workflow's EDITABLE truth becomes `plan` — a flat step list (entry point, steps, chat brain, booked setting |
| `2026_09_07_190000_drop_ae_campaigns_tables` | 2026-09-07 | Drop the appointment engine's own ad-spend tables. |
| `2026_09_07_190000_make_ae_leads_phone_nullable_add_wa_contact` | 2026-09-07 | WhatsApp usernames hide the phone number: a lead who messages in from a hidden-number contact has NO phone, so the column goes nullable and the WhatsA |
| `2026_09_08_100000_create_ae_closer_handoffs_table` | 2026-09-08 | The closer hand-off ledger: every time the engine OFFERS a booked appointment to a closer, one row — who was asked, when, whether they accepted, passe |
| `2026_09_08_110000_backfill_appointment_engine_subscriptions` | 2026-09-08 | Backfills the appointment engine's three default-on events — `ae.lead_assigned`, `ae.team_alert`, `ae.nudge` — for every PERSONAL destination that alr |
| `2026_09_08_120000_create_ae_project_closers_table` | 2026-09-08 | Who closes a project's booked appointments, PER GROUP. |
| `2026_09_08_130000_add_team_id_to_ae_leads_table` | 2026-09-08 | A lead's TEAM inside its agency (owner, 2026-09-08: a project page reads per team — Alpha's leads and appointments are not Bravo's). |