Operations & Maintenance Ticketing

0. Executive Summary

A lightweight, mobile-first, Arabic-first ticketing system for tracking vehicle maintenance and branch/facility maintenance requests, with a self-driving escalation engine that pushes overdue work up the management chain over WhatsApp automatically — with no human chasing anyone.

The system's real value is not the ticket list. Dozens of tools do ticket lists. The value is the sentence in the client's voice note:

"أنا أرسلتها لمسؤول الصيانة بس إلى الحين ما سوى شيء للإدارة"

"I sent it to the maintenance officer, but so far he hasn't done anything — [escalate] to management."

Everything in this spec exists to make that escalation happen automatically, on a clock, without anyone remembering to check. Build the escalation engine first; the CRUD around it is commodity work.

1. Requirements Traceability — Voice Note → System

Mapping the client's spoken requirements to numbered requirements. Three requirements below (BR-08, BR-09, BR-10) appear in the audio but were not in the written brief — they materially affect the data model, so please confirm them.

⚠️ Design consequence of BR-08: every table carries company_id and every query is tenant-scoped from commit #1. Retrofitting tenancy later is a rewrite. This costs ~2 extra days now.

⚠️ Design consequence of BR-09: a ticket is not "resolved" when the workshop finishes. It is resolved when the vehicle is physically back in company custody. A ticket sitting at a finished workshop for four days is still costing the company a vehicle, and the SLA clock must keep running. This is modelled as ticket_handovers (§6.6).

2. Core Modules

M1 — Identity, Org Chart & Roles

Users, roles, branches, departments, and the reporting line.

• User record keyed on phone number in E.164 — the phone is the identity, because all interaction happens over WhatsApp.
• users.manager_id self-referencing FK. This single column powers escalation: "escalate to my boss" is a graph walk, not a hardcoded list. When Abdulaziz doesn't respond, the engine already knows who Abdulaziz reports to.
• Role assignment is scoped: a Field Supervisor is a supervisor of Branch X, not globally.
• Out of scope v1: SSO, AD sync, granular per-field permissions.

M2 — Asset Registry

The things that break.

• Vehicles: plate (Arabic + Latin), make/model/year, VIN, owning branch, assigned driver, odometer, istimara (استمارة) and insurance expiry.
• Branches / Facilities: branch record plus optional itemised facility_assets (AC units, generators, chillers, signage).
• Asset status reflects availability: ACTIVE | IN_WORKSHOP | GROUNDED | SOLD. Opening a vehicle ticket flips the vehicle to IN_WORKSHOP — the fleet list then answers "how many cars are actually on the road right now?" for free.
• Out of scope v1: preventive-maintenance schedules, fuel cards, tyre history.

M3 — Ticketing (Core)

Create → assign → acknowledge → commit ETA → work → hand back → close.

• Two categories (VEHICLE, FACILITY) over one table with a CHECK constraint enforcing the right subject FK.
• Human-readable ticket numbers: QRW-VEH-2026-000123. People will read these aloud over the phone.
• Append-only status history — every transition records who, when, and through which channel (APP / WHATSAPP / SYSTEM).
• Photo attachments on create and on close. Field supervisors will document with photos; make it one tap.
• Comments/notes thread per ticket.

M4 — SLA Engine

Turns a policy into four concrete deadlines the moment a ticket is assigned.

Four independent clocks per ticket — this is the key modelling decision, because "late" means four different things:

The fourth clock is the one that catches the real-world failure mode: a ticket that was acknowledged, given an ETA, and then quietly forgotten for a week. Standard helpdesk tools miss this. resolution_due_at is deliberately overwritten by the assignee's own commitment (BR-03/BR-04) — he is then escalated against a number he chose, which is far harder to argue with.

M5 — Escalation Engine

The heart of the product. See §5 for the full specification.

• Scans for expired clocks, fires the configured ladder step, records an immutable log, schedules the next step.
• Idempotent: safe to run twice, safe after a crash, no duplicate 3 a.m. messages to the Executive Manager.
• Ladder targets resolve dynamically (ASSIGNEE_MANAGER, ROLE, CHANNEL) rather than to hardcoded user IDs, so a staff change doesn't break escalation.

M6 — Notification Gateway

Provider-agnostic outbox. See §8.

• Transactional outbox table; a dispatcher worker drains it with retry + backoff.
• Transports: WhatsApp, SMS (fallback), Telegram (management group), Web Push.
• Inbound webhook handling: a tapped WhatsApp button becomes a ticket state change (BR-02). This is what makes the system usable by people who will never open the web app.
• Quiet hours, opt-out, delivery receipts.

M7 — Management Dashboard

Role-filtered views + an executive digest.

• Field Supervisor: my tickets, my branch, big "report a problem" button.
• Maintenance Supervisor: my queue sorted by deadline, overdue in red.
• Maintenance Manager: team-wide, SLA compliance, escalations against my staff, vendor performance.
• Executive: single-page overview — open by category, breached count, cost month-to-date, longest-running ticket, and who is blocking what.
• Daily digest at 07:30 Riyadh pushed to the executive's WhatsApp/Telegram. Executives do not log in to dashboards; the dashboard has to go to them.
• KPIs: MTTA, MTTR, SLA compliance %, escalation rate by person, vehicle downtime days, cost per vehicle, vendor turnaround.

M8 — HR Module (Phase 2 — BR-10)

Deliberately deferred, but designed for.

Employees, leave requests, and document expiry tracking (iqama, driving licence, istimara, insurance). Note that document expiry is structurally identical to an SLA breach: a date passes, nobody acted, escalate. The M5 engine should be built generic enough to serve it — this is why escalation_rules keys on a trigger_type rather than being hardcoded to tickets.

3. Roles & Permission Matrix

Scope levels (roles.scope_level): 1 self · 2 branch · 3 company · 4 all companies. Every list query is filtered by the caller's maximum scope — implement this once in a base query-set/repository layer, never per-endpoint.

Executive auto-inclusion (from the brief): the Executive role is auto-subscribed to overview channels via notification_channels.auto_include_roles. Adding a new executive to the org gives them full visibility with zero configuration — no one has to remember to add Abu Omar to the group.

4. Ticket Lifecycle

4.1 Status Set

⚠️ READY keeps the resolution clock running on purpose. A finished car still sitting at the workshop is still an unavailable car. Pausing the clock here would hide the most common real-world delay. Only AWAITING_APPROVAL and ON_HOLD pause the clock, because in both cases the assignee is genuinely blocked by someone else.

4.2 State Machine

stateDiagram-v2
    [*] --> NEW
    NEW --> ASSIGNED : assign
    NEW --> CANCELLED : withdraw
    ASSIGNED --> ACKNOWLEDGED : assignee taps استلمت
    ASSIGNED --> ASSIGNED : reassign
    ASSIGNED --> REJECTED : reject with reason
    ACKNOWLEDGED --> SCHEDULED : enter expected days
    SCHEDULED --> AT_VENDOR : handover OUT
    SCHEDULED --> IN_PROGRESS : in-house work
    AT_VENDOR --> AWAITING_PARTS : parts needed
    AT_VENDOR --> AWAITING_APPROVAL : cost > threshold
    IN_PROGRESS --> AWAITING_PARTS : parts needed
    IN_PROGRESS --> AWAITING_APPROVAL : cost > threshold
    AWAITING_APPROVAL --> AT_VENDOR : approved
    AWAITING_APPROVAL --> REJECTED : declined
    AWAITING_PARTS --> AT_VENDOR : parts arrived
    AT_VENDOR --> READY : workshop finished
    IN_PROGRESS --> READY : work finished
    READY --> RESOLVED : handover IN (asset returned)
    IN_PROGRESS --> ON_HOLD : pause with reason
    ON_HOLD --> IN_PROGRESS : resume
    RESOLVED --> CLOSED : verified
    RESOLVED --> ASSIGNED : reopen (problem persists)
    CLOSED --> ASSIGNED : reopen within 7 days
    CLOSED --> [*]
    CANCELLED --> [*]
    REJECTED --> [*]

4.3 Step-by-Step Workflow

Stage 1 — Logging (0 min)

1. Branch employee or driver opens the PWA (or replies عطل to the WhatsApp bot) and reports a problem.
2. Picks category: سيارة or فرع.
3. Picks the asset — plate number for vehicles, branch + optional facility asset for facilities. Recent/nearby assets are pre-listed to avoid typing.
4. Enters a short title, description, priority, and attaches photos.
5. System creates the ticket with status = NEW, generates ticket_no, resolves the matching sla_policy, and computes ack_due_at.

Stage 2 — Assignment (target: instant) 6. Auto-assignment rule fires: (company, category, branch) → default responsible user. If no rule matches, it lands in the Maintenance Manager's unassigned queue. 7. status → ASSIGNED, assigned_at stamped, ack_due_at = assigned_at + policy.ack_minutes. 8. WhatsApp template ticket_assigned is sent to the assignee with two reply buttons: استلمت ✅ and تعذّر الاستلام.

Stage 3 — Acknowledgement (BR-02, SLA: 2h) 9. Assignee taps استلمت. The inbound webhook maps the button payload ACK:<ticket_id> to the ticket → status = ACKNOWLEDGED, acknowledged_at = now(), changed_via = WHATSAPP. No login required. This is the single most important usability decision in the product. 10. ❌ If he does not tap within ack_minutes → Escalation L0 (reminder to him). Still nothing after the L0 window → L1 to his manager (§5). 11. If he taps تعذّر الاستلام, he is prompted for a reason; the ticket returns to the manager's queue for reassignment and the ack clock restarts.

Stage 4 — ETA Commitment (BR-03 / BR-04, SLA: 4h) 12. Immediately after acknowledgement the system asks: «كم يوم تحتاج لإصلاحها؟» with quick-reply buttons يوم / يومين / ٣ أيام / أكثر. 13. أكثر triggers a free-text reply parsed for a number of days, or a link into the app. 14. promised_days is stored, promised_at = now() + days, and resolution_due_at is overwritten with promised_at. He is now measured against his own commitment. 15. status → SCHEDULED. Requester is notified: "تم استلام طلبك ومتوقع الإصلاح خلال ٢ يوم." 16. ❌ No ETA within eta_minutes → escalation, treated as seriously as no-acknowledgement. A silent acknowledgement with no commitment is the classic way to game a ticketing system.

Stage 5 — Execution & Custody (BR-09) 17. Assignee records handover OUT: vendor/workshop, odometer, expected return date, optional photos. status → AT_VENDOR, vehicle status → IN_WORKSHOP. 18. Progress updates: parts, cost estimate, delay notices. Every update resets next_update_due_at. 19. If estimated_cost > approval_threshold → AWAITING_APPROVAL, resolution clock pauses, approval request goes to the Maintenance Manager (and to the Executive above a second, higher threshold). 20. ❌ No update for stale_update_minutes while in progress → escalation. This catches the forgotten ticket. 21. Delay request: if the workshop extends, the assignee submits a new ETA with a reason. It does not silently reset the clock — the original commitment is preserved, promised_at is revised, and every extension is logged and counted in his performance report. Two extensions on one ticket auto-notifies the Maintenance Manager.

Stage 6 — Return & Resolution (BR-09) 22. Workshop finishes → status = READY. Clock keeps running. 23. Assignee records handover IN: return odometer, actual cost, invoice reference, photos. status → RESOLVED, returned_at and resolved_at stamped, vehicle status → ACTIVE. 24. Requester is notified and asked to confirm.

Stage 7 — Closure 25. Requester (or Maintenance Manager after 48h of silence) confirms → status = CLOSED. 26. System computes downtime_minutes, SLA met/breached per clock, and writes the final history row. 27. If the requester says the problem persists → reopen, reopened_count++, ticket returns to ASSIGNED with a fresh clock and a flag. Reopen rate is a headline KPI — it separates "closed" from "fixed".

5. SLA & Escalation Engine — Detailed Spec

5.1 Escalation Ladder (default configuration)

Same ladder shape applies to all four triggers (NO_ACK, NO_ETA, SLA_BREACH, STALE) with different delays. The client asked for "رسالتين واتساب" — two messages (BR-05). L0 and L1 satisfy that literally; L2/L3 exist because two messages will not be enough for the ticket that genuinely goes wrong, and the executive explicitly asked for visibility when it does.

5.2 Escalation Sequence

sequenceDiagram
    participant S as Scheduler (every 5 min)
    participant E as Escalation Engine
    participant DB as PostgreSQL
    participant O as Outbox Dispatcher
    participant W as WhatsApp API
    participant M as Manager

    S->>E: tick()
    E->>DB: claim tickets past next_escalation_at<br/>FOR UPDATE SKIP LOCKED LIMIT 200
    DB-->>E: due tickets
    loop per ticket
        E->>E: resolve rule for (level, trigger)
        E->>E: resolve target (ASSIGNEE_MANAGER → users.manager_id)
        E->>DB: INSERT escalation_logs (idempotency_key) ON CONFLICT DO NOTHING
        alt row inserted (not a duplicate)
            E->>DB: INSERT notifications (QUEUED)
            E->>DB: UPDATE tickets SET escalation_level, next_escalation_at
        end
    end
    O->>DB: claim QUEUED where scheduled_for <= now()
    O->>W: POST /messages (template + idempotency key)
    W-->>O: message_id
    O->>DB: status = SENT
    W-->>M: "⚠️ تذكرة QRW-VEH-2026-000123 متأخرة"
    M->>W: taps "تم التوجيه"
    W-->>E: inbound webhook
    E->>DB: escalation_logs.acknowledged_at = now()

5.3 Non-Negotiable Engine Rules

1. Idempotency. Every escalation writes escalation_logs with a unique idempotency_key = "{ticket_id}:{rule_id}:{repeat_index}", inserted ON CONFLICT DO NOTHING. The notification is only queued if the insert actually created a row. Two scheduler instances, or a crash mid-loop, can never double-message the Executive Manager. Get this wrong and management will mute the system in week one.
2. SKIP LOCKED claiming. Enables safe horizontal scaling of workers with no distributed lock.
3. Dynamic target resolution. Never store a user ID in a rule when a relationship will do. ASSIGNEE_MANAGER walks users.manager_id at fire time, so staff changes never break the ladder. If the walk finds nobody (or the manager is inactive/on leave), fall back to the role target one level up and log the fallback.
4. Quiet hours. 22:00–06:30 Riyadh: MEDIUM/LOW escalations are queued with scheduled_for = next 07:00. HIGH/CRITICAL send immediately. Configurable per company.
5. Business calendar. Saudi work week Sun–Thu. Optional business_hours_only per SLA policy — a Wednesday-evening ticket with a 4h business-hours SLA is not "breached" at 8 a.m. Sunday. v1 recommendation: keep SLA in calendar hours (simple, predictable) and apply quiet hours only to notification delivery. Add a business-minutes calculator in v1.1 once real data shows whether it matters.
6. De-escalation. Any status change, comment, or handover clears next_escalation_at and recomputes it from the new state. Progress must visibly stop the alarms, or people will stop making progress visible.
7. Cap and cool-down. max_repeats per level; a ticket can never generate more than N messages/day (default 6). Alert fatigue kills escalation systems faster than bugs do.
8. Manual escalation. Any user can escalate to L2 manually with a reason. Sometimes a human knows it's urgent before the clock does.

5.4 Scanner Query (reference implementation)

-- Runs every 5 minutes; safe to run concurrently across workers.
SELECT id, company_id, assignee_id, escalation_level, sla_policy_id, priority
FROM tickets
WHERE next_escalation_at IS NOT NULL
  AND next_escalation_at <= now()
  AND status NOT IN ('RESOLVED','CLOSED','CANCELLED','REJECTED','ON_HOLD','AWAITING_APPROVAL')
ORDER BY next_escalation_at
LIMIT 200
FOR UPDATE SKIP LOCKED;

Backed by a partial index (§6.5) this stays sub-millisecond well past 100k tickets — the index only contains rows that are actually escalatable.

6. Data Model

6.1 Design Decisions (read before the DDL)

Requires PostgreSQL 15+ for UNIQUE NULLS NOT DISTINCT. On PG 14 or below, replace with a unique index over COALESCE(branch_id, 0).

6.2 Entity Relationships

erDiagram
    COMPANIES   ||--o{ BRANCHES : owns
    COMPANIES   ||--o{ USERS : employs
    COMPANIES   ||--o{ VEHICLES : owns
    COMPANIES   ||--o{ VENDORS : contracts
    COMPANIES   ||--o{ SLA_POLICIES : defines
    BRANCHES    ||--o{ FACILITY_ASSETS : contains
    BRANCHES    ||--o{ USERS : "staffed by"
    USERS       ||--o{ USERS : "manages (manager_id)"
    USERS       ||--o{ USER_ROLES : has
    ROLES       ||--o{ USER_ROLES : grants
    VEHICLES    ||--o{ TICKETS : "subject of"
    BRANCHES    ||--o{ TICKETS : "subject of"
    FACILITY_ASSETS ||--o{ TICKETS : "subject of"
    USERS       ||--o{ TICKETS : "reports / is assigned"
    VENDORS     ||--o{ TICKETS : services
    SLA_POLICIES ||--o{ TICKETS : governs
    SLA_POLICIES ||--o{ ESCALATION_RULES : configures
    TICKETS     ||--o{ TICKET_STATUS_HISTORY : logs
    TICKETS     ||--o{ TICKET_ASSIGNMENTS : "routed via"
    TICKETS     ||--o{ TICKET_HANDOVERS : "custody chain"
    TICKETS     ||--o{ TICKET_COMMENTS : has
    TICKETS     ||--o{ TICKET_ATTACHMENTS : has
    TICKETS     ||--o{ ESCALATION_LOGS : triggers
    ESCALATION_RULES ||--o{ ESCALATION_LOGS : "fired by"
    ESCALATION_LOGS ||--|| NOTIFICATIONS : sends
    NOTIFICATION_CHANNELS ||--o{ NOTIFICATIONS : "fans out to"
    USERS       ||--o{ NOTIFICATIONS : receives
    NOTIFICATIONS ||--o{ INBOUND_MESSAGES : "replied to"

6.3 Tenancy & Org

CREATE TABLE companies (
    id              BIGSERIAL PRIMARY KEY,
    code            VARCHAR(20)  NOT NULL UNIQUE,          -- 'QARAAWI'
    name_ar         VARCHAR(150) NOT NULL,
    name_en         VARCHAR(150),
    timezone        VARCHAR(50)  NOT NULL DEFAULT 'Asia/Riyadh',
    default_locale  VARCHAR(5)   NOT NULL DEFAULT 'ar',
    quiet_hours_from TIME        NOT NULL DEFAULT '22:00',
    quiet_hours_to   TIME        NOT NULL DEFAULT '06:30',
    approval_threshold NUMERIC(12,2) DEFAULT 1000.00,      -- SAR, cost needing manager sign-off
    exec_threshold     NUMERIC(12,2) DEFAULT 10000.00,     -- SAR, needing executive sign-off
    is_active       BOOLEAN      NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT now()
);

CREATE TABLE departments (
    id          BIGSERIAL PRIMARY KEY,
    company_id  BIGINT NOT NULL REFERENCES companies(id),
    code        VARCHAR(30)  NOT NULL,
    name_ar     VARCHAR(120) NOT NULL,
    UNIQUE (company_id, code)
);

CREATE TABLE branches (
    id          BIGSERIAL PRIMARY KEY,
    company_id  BIGINT NOT NULL REFERENCES companies(id),
    code        VARCHAR(30)  NOT NULL,
    name_ar     VARCHAR(150) NOT NULL,
    city        VARCHAR(80),
    address     TEXT,
    geo_lat     NUMERIC(9,6),
    geo_lng     NUMERIC(9,6),
    manager_id  BIGINT,                    -- FK added after users (circular)
    is_active   BOOLEAN NOT NULL DEFAULT TRUE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (company_id, code)
);

CREATE TABLE users (
    id               BIGSERIAL PRIMARY KEY,
    company_id       BIGINT NOT NULL REFERENCES companies(id),
    employee_no      VARCHAR(30),
    full_name_ar     VARCHAR(150) NOT NULL,
    full_name_en     VARCHAR(150),
    national_id      VARCHAR(20),
    phone_e164       VARCHAR(20) NOT NULL,        -- +9665XXXXXXXX — the primary identity
    whatsapp_opt_in  BOOLEAN NOT NULL DEFAULT TRUE,
    telegram_chat_id VARCHAR(40),
    email            VARCHAR(150),
    password_hash    TEXT,                        -- NULL = WhatsApp-OTP-only user
    job_title_ar     VARCHAR(120),
    department_id    BIGINT REFERENCES departments(id),
    branch_id        BIGINT REFERENCES branches(id),
    manager_id       BIGINT REFERENCES users(id), -- ⭐ escalation ladder walks this
    preferred_locale VARCHAR(5) NOT NULL DEFAULT 'ar',
    on_leave_until   DATE,                        -- engine skips to manager while on leave
    is_active        BOOLEAN NOT NULL DEFAULT TRUE,
    last_login_at    TIMESTAMPTZ,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (company_id, phone_e164)
);

ALTER TABLE branches ADD CONSTRAINT fk_branches_manager
    FOREIGN KEY (manager_id) REFERENCES users(id);

CREATE TABLE roles (
    id          BIGSERIAL PRIMARY KEY,
    code        VARCHAR(40) NOT NULL UNIQUE,
    name_ar     VARCHAR(80) NOT NULL,
    scope_level SMALLINT NOT NULL CHECK (scope_level BETWEEN 1 AND 4)
);
-- Seed: REQUESTER(1), FIELD_SUPERVISOR(2), MAINT_SUPERVISOR(2), MAINT_MANAGER(3),
--       HR_ADMIN(3), EXECUTIVE(3), SYS_ADMIN(4)

CREATE TABLE user_roles (
    id         BIGSERIAL PRIMARY KEY,
    user_id    BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id    BIGINT NOT NULL REFERENCES roles(id),
    branch_id  BIGINT REFERENCES branches(id),   -- NULL = all branches in company
    granted_by BIGINT REFERENCES users(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE NULLS NOT DISTINCT (user_id, role_id, branch_id)   -- PG15+
);

6.4 Assets & Vendors

CREATE TABLE vendors (
    id             BIGSERIAL PRIMARY KEY,
    company_id     BIGINT NOT NULL REFERENCES companies(id),
    name_ar        VARCHAR(150) NOT NULL,
    vendor_type    VARCHAR(20) NOT NULL
                   CHECK (vendor_type IN ('WORKSHOP','AC','ELECTRICAL','PLUMBING',
                                          'REFRIGERATION','CIVIL','IT','GENERAL')),
    contact_person VARCHAR(120),
    phone_e164     VARCHAR(20),
    city           VARCHAR(80),
    vat_number     VARCHAR(20),
    is_approved    BOOLEAN NOT NULL DEFAULT TRUE,
    avg_turnaround_hours NUMERIC(8,2),   -- rolled up nightly from closed tickets
    rating         NUMERIC(2,1),
    created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE vehicles (
    id                 BIGSERIAL PRIMARY KEY,
    company_id         BIGINT NOT NULL REFERENCES companies(id),
    plate_no_ar        VARCHAR(20) NOT NULL,       -- أ ب ج ١٢٣٤
    plate_no_en        VARCHAR(20),                -- ABC 1234
    sequence_no        VARCHAR(20),                -- الرقم التسلسلي
    make               VARCHAR(50),
    model              VARCHAR(50),
    model_year         SMALLINT,
    vin                VARCHAR(30),
    color_ar           VARCHAR(30),
    branch_id          BIGINT REFERENCES branches(id),
    assigned_driver_id BIGINT REFERENCES users(id),
    current_odometer   INTEGER,
    istimara_expiry    DATE,                       -- انتهاء الاستمارة  (feeds M8 alerts)
    insurance_expiry   DATE,
    status             VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'
                       CHECK (status IN ('ACTIVE','IN_WORKSHOP','GROUNDED','SOLD')),
    is_active          BOOLEAN NOT NULL DEFAULT TRUE,
    created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (company_id, plate_no_ar)
);

CREATE TABLE facility_assets (
    id            BIGSERIAL PRIMARY KEY,
    company_id    BIGINT NOT NULL REFERENCES companies(id),
    branch_id     BIGINT NOT NULL REFERENCES branches(id),
    asset_tag     VARCHAR(40),
    name_ar       VARCHAR(150) NOT NULL,
    category      VARCHAR(30) NOT NULL
                  CHECK (category IN ('AC','ELECTRICAL','PLUMBING','CIVIL',
                                      'REFRIGERATION','SIGNAGE','IT','SAFETY','OTHER')),
    location_note VARCHAR(150),                    -- 'الدور الثاني - المستودع'
    installed_on  DATE,
    warranty_until DATE,
    status        VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (company_id, asset_tag)
);

6.5 Tickets (core)

CREATE TABLE tickets (
    id                 BIGSERIAL PRIMARY KEY,
    company_id         BIGINT NOT NULL REFERENCES companies(id),
    ticket_no          VARCHAR(25) NOT NULL,        -- QRW-VEH-2026-000123
    category           VARCHAR(20) NOT NULL CHECK (category IN ('VEHICLE','FACILITY')),
    subcategory        VARCHAR(40),                 -- ENGINE, TYRES, BRAKES, AC, ELECTRICAL...

    -- Subject of the ticket (exactly one branch of the CHECK below)
    vehicle_id         BIGINT REFERENCES vehicles(id),
    branch_id          BIGINT REFERENCES branches(id),
    facility_asset_id  BIGINT REFERENCES facility_assets(id),

    title              VARCHAR(200) NOT NULL,
    description        TEXT,
    priority           VARCHAR(10) NOT NULL DEFAULT 'MEDIUM'
                       CHECK (priority IN ('CRITICAL','HIGH','MEDIUM','LOW')),
    status             VARCHAR(25) NOT NULL DEFAULT 'NEW',

    reported_by_id     BIGINT NOT NULL REFERENCES users(id),
    assignee_id        BIGINT REFERENCES users(id),
    vendor_id          BIGINT REFERENCES vendors(id),
    sla_policy_id      BIGINT REFERENCES sla_policies(id),

    -- ── Lifecycle timestamps ────────────────────────────────────────────
    created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
    assigned_at        TIMESTAMPTZ,
    acknowledged_at    TIMESTAMPTZ,                 -- BR-02
    handover_out_at    TIMESTAMPTZ,                 -- BR-09: asset left custody
    promised_days      SMALLINT,                    -- BR-03: "كم تبي تقعد تصلحها؟"
    promised_at        TIMESTAMPTZ,                 -- BR-04: workshop's quoted deadline
    eta_revisions      SMALLINT NOT NULL DEFAULT 0, -- how many times he moved the goalposts
    ready_at           TIMESTAMPTZ,                 -- workshop finished
    returned_at        TIMESTAMPTZ,                 -- BR-09: asset back in custody
    resolved_at        TIMESTAMPTZ,
    closed_at          TIMESTAMPTZ,
    last_activity_at   TIMESTAMPTZ NOT NULL DEFAULT now(),

    -- ── SLA targets (materialised for the scanner) ──────────────────────
    ack_due_at         TIMESTAMPTZ,
    eta_due_at         TIMESTAMPTZ,
    resolution_due_at  TIMESTAMPTZ,
    next_update_due_at TIMESTAMPTZ,
    clock_paused_at    TIMESTAMPTZ,                 -- set on ON_HOLD / AWAITING_APPROVAL
    paused_total_sec   INTEGER NOT NULL DEFAULT 0,

    -- ── Escalation state ────────────────────────────────────────────────
    escalation_level   SMALLINT NOT NULL DEFAULT 0,
    next_escalation_at TIMESTAMPTZ,
    breach_flags       JSONB NOT NULL DEFAULT '[]'::jsonb,   -- ["ACK","RES"]
    is_breached        BOOLEAN NOT NULL DEFAULT FALSE,

    -- ── Cost & outcome ──────────────────────────────────────────────────
    estimated_cost     NUMERIC(12,2),
    actual_cost        NUMERIC(12,2),
    invoice_ref        VARCHAR(50),
    approved_by_id     BIGINT REFERENCES users(id),
    approved_at        TIMESTAMPTZ,
    downtime_minutes   INTEGER,                     -- computed at close
    reopened_count     SMALLINT NOT NULL DEFAULT 0,
    closed_by_id       BIGINT REFERENCES users(id),
    close_note         TEXT,

    UNIQUE (company_id, ticket_no),
    CONSTRAINT ck_ticket_subject CHECK (
        (category = 'VEHICLE'  AND vehicle_id IS NOT NULL)
     OR (category = 'FACILITY' AND branch_id IS NOT NULL AND vehicle_id IS NULL)
    )
);

-- ⭐ The escalation scanner's index. Partial → contains only escalatable rows.
CREATE INDEX ix_tickets_escalation_due ON tickets (next_escalation_at)
    WHERE next_escalation_at IS NOT NULL
      AND status NOT IN ('RESOLVED','CLOSED','CANCELLED','REJECTED',
                         'ON_HOLD','AWAITING_APPROVAL');

CREATE INDEX ix_tickets_open      ON tickets (company_id, status, priority, created_at DESC)
    WHERE status NOT IN ('CLOSED','CANCELLED','REJECTED');
CREATE INDEX ix_tickets_assignee  ON tickets (assignee_id, status);
CREATE INDEX ix_tickets_vehicle   ON tickets (vehicle_id, created_at DESC);
CREATE INDEX ix_tickets_branch    ON tickets (branch_id, created_at DESC);
CREATE INDEX ix_tickets_no_trgm   ON tickets USING gin (ticket_no gin_trgm_ops);

6.6 Ticket Sub-Entities

CREATE TABLE ticket_status_history (
    id              BIGSERIAL PRIMARY KEY,
    ticket_id       BIGINT NOT NULL REFERENCES tickets(id) ON DELETE CASCADE,
    from_status     VARCHAR(25),
    to_status       VARCHAR(25) NOT NULL,
    changed_by_id   BIGINT REFERENCES users(id),      -- NULL = system
    changed_via     VARCHAR(15) NOT NULL DEFAULT 'APP'
                    CHECK (changed_via IN ('APP','WHATSAPP','SMS','TELEGRAM','API','SYSTEM')),
    note            TEXT,
    duration_in_prev_sec INTEGER,                     -- powers the stage-time report
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_tsh_ticket ON ticket_status_history (ticket_id, created_at);

CREATE TABLE ticket_assignments (
    id            BIGSERIAL PRIMARY KEY,
    ticket_id     BIGINT NOT NULL REFERENCES tickets(id) ON DELETE CASCADE,
    from_user_id  BIGINT REFERENCES users(id),
    to_user_id    BIGINT NOT NULL REFERENCES users(id),
    assigned_by_id BIGINT REFERENCES users(id),
    reason        TEXT,
    accepted_at   TIMESTAMPTZ,
    rejected_at   TIMESTAMPTZ,
    reject_reason TEXT,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ⭐ BR-09 — the custody chain the client described: "يمشي لين يستلمها"
CREATE TABLE ticket_handovers (
    id                BIGSERIAL PRIMARY KEY,
    ticket_id         BIGINT NOT NULL REFERENCES tickets(id) ON DELETE CASCADE,
    direction         VARCHAR(5) NOT NULL CHECK (direction IN ('OUT','IN')),
    counterparty_type VARCHAR(10) NOT NULL CHECK (counterparty_type IN ('VENDOR','USER')),
    vendor_id         BIGINT REFERENCES vendors(id),
    user_id           BIGINT REFERENCES users(id),
    odometer          INTEGER,
    expected_return_at TIMESTAMPTZ,       -- what the workshop promised at drop-off
    occurred_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    recorded_by_id    BIGINT REFERENCES users(id),
    condition_note    TEXT,
    photo_urls        JSONB NOT NULL DEFAULT '[]'::jsonb,
    signature_url     TEXT
);
CREATE INDEX ix_handovers_ticket ON ticket_handovers (ticket_id, occurred_at);

CREATE TABLE ticket_comments (
    id           BIGSERIAL PRIMARY KEY,
    ticket_id    BIGINT NOT NULL REFERENCES tickets(id) ON DELETE CASCADE,
    author_id    BIGINT REFERENCES users(id),
    body         TEXT NOT NULL,
    is_internal  BOOLEAN NOT NULL DEFAULT FALSE,   -- hidden from the requester
    via          VARCHAR(15) NOT NULL DEFAULT 'APP',
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE ticket_attachments (
    id          BIGSERIAL PRIMARY KEY,
    ticket_id   BIGINT NOT NULL REFERENCES tickets(id) ON DELETE CASCADE,
    uploaded_by_id BIGINT REFERENCES users(id),
    kind        VARCHAR(15) NOT NULL DEFAULT 'PHOTO'
                CHECK (kind IN ('PHOTO','INVOICE','QUOTE','DOCUMENT','VOICE')),
    file_url    TEXT NOT NULL,
    mime_type   VARCHAR(80),
    size_bytes  INTEGER,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE ticket_watchers (
    ticket_id BIGINT NOT NULL REFERENCES tickets(id) ON DELETE CASCADE,
    user_id   BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    reason    VARCHAR(30),        -- 'REPORTER','MANAGER','ESCALATED','MANUAL'
    PRIMARY KEY (ticket_id, user_id)
);

6.7 SLA & Escalation

CREATE TABLE sla_policies (
    id                   BIGSERIAL PRIMARY KEY,
    company_id           BIGINT NOT NULL REFERENCES companies(id),
    name_ar              VARCHAR(120) NOT NULL,
    category             VARCHAR(20),      -- NULL = any
    priority             VARCHAR(10),      -- NULL = any
    ack_minutes          INTEGER NOT NULL DEFAULT 120,
    eta_minutes          INTEGER NOT NULL DEFAULT 240,
    resolution_minutes   INTEGER NOT NULL DEFAULT 2880,   -- 48h default
    stale_update_minutes INTEGER NOT NULL DEFAULT 1440,   -- 24h silence
    business_hours_only  BOOLEAN NOT NULL DEFAULT FALSE,
    is_active            BOOLEAN NOT NULL DEFAULT TRUE,
    created_at           TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Match precedence: (category,priority) > (category) > (priority) > catch-all.

CREATE TABLE notification_channels (
    id                 BIGSERIAL PRIMARY KEY,
    company_id         BIGINT NOT NULL REFERENCES companies(id),
    name_ar            VARCHAR(120) NOT NULL,        -- 'قروب متابعة الإدارة'
    kind               VARCHAR(20) NOT NULL
                       CHECK (kind IN ('TELEGRAM_GROUP','WHATSAPP_FANOUT',
                                       'EMAIL_LIST','WEBHOOK')),
    external_id        VARCHAR(120),                 -- telegram chat_id / webhook URL
    member_user_ids    BIGINT[] NOT NULL DEFAULT '{}',
    auto_include_roles VARCHAR(200),                 -- ⭐ 'EXECUTIVE,MAINT_MANAGER'
    is_active          BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE escalation_rules (
    id                BIGSERIAL PRIMARY KEY,
    company_id        BIGINT NOT NULL REFERENCES companies(id),
    sla_policy_id     BIGINT REFERENCES sla_policies(id),   -- NULL = all policies
    level             SMALLINT NOT NULL,
    trigger_type      VARCHAR(20) NOT NULL
                      CHECK (trigger_type IN ('NO_ACK','NO_ETA','SLA_BREACH','STALE','MANUAL')),
    delay_minutes     INTEGER NOT NULL DEFAULT 0,   -- after the missed deadline
    target_type       VARCHAR(20) NOT NULL
                      CHECK (target_type IN ('ASSIGNEE','ASSIGNEE_MANAGER','ROLE',
                                             'USER','CHANNEL','REPORTER')),
    target_role_code  VARCHAR(40),
    target_user_id    BIGINT REFERENCES users(id),
    target_channel_id BIGINT REFERENCES notification_channels(id),
    transports        VARCHAR(60) NOT NULL DEFAULT 'WHATSAPP',  -- 'WHATSAPP,SMS'
    template_code     VARCHAR(60) NOT NULL,
    repeat_every_minutes INTEGER,                   -- NULL = fire once
    max_repeats       SMALLINT NOT NULL DEFAULT 0,
    is_active         BOOLEAN NOT NULL DEFAULT TRUE,
    UNIQUE NULLS NOT DISTINCT (company_id, sla_policy_id, level, trigger_type)
);

CREATE TABLE escalation_logs (
    id                    BIGSERIAL PRIMARY KEY,
    ticket_id             BIGINT NOT NULL REFERENCES tickets(id) ON DELETE CASCADE,
    rule_id               BIGINT REFERENCES escalation_rules(id),
    level                 SMALLINT NOT NULL,
    trigger_type          VARCHAR(20) NOT NULL,
    repeat_index          SMALLINT NOT NULL DEFAULT 0,
    due_at                TIMESTAMPTZ,              -- the deadline that was missed
    overdue_minutes       INTEGER,
    triggered_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    escalated_to_user_id  BIGINT REFERENCES users(id),
    escalated_to_channel_id BIGINT REFERENCES notification_channels(id),
    notification_id       BIGINT,                   -- FK added after notifications
    acknowledged_at       TIMESTAMPTZ,              -- manager tapped "تم التوجيه"
    acknowledged_by_id    BIGINT REFERENCES users(id),
    resolution_note       TEXT,
    -- ⭐ "{ticket_id}:{rule_id}:{repeat_index}" — the duplicate-message guard
    idempotency_key       VARCHAR(120) NOT NULL UNIQUE
);
CREATE INDEX ix_esc_logs_ticket ON escalation_logs (ticket_id, triggered_at DESC);
CREATE INDEX ix_esc_logs_user   ON escalation_logs (escalated_to_user_id, triggered_at DESC);

6.8 Messaging

CREATE TABLE notification_templates (
    id              BIGSERIAL PRIMARY KEY,
    company_id      BIGINT REFERENCES companies(id),   -- NULL = global default
    code            VARCHAR(60) NOT NULL,
    transport       VARCHAR(15) NOT NULL,
    locale          VARCHAR(5) NOT NULL DEFAULT 'ar',
    provider_template_name VARCHAR(80),   -- Meta-approved template name
    body_template   TEXT NOT NULL,        -- local render for SMS/Telegram/preview
    variables       JSONB NOT NULL DEFAULT '[]'::jsonb,
    buttons         JSONB NOT NULL DEFAULT '[]'::jsonb,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    UNIQUE NULLS NOT DISTINCT (company_id, code, transport, locale)
);

-- Transactional outbox. Nothing calls a messaging API directly; everything lands here.
CREATE TABLE notifications (
    id                  BIGSERIAL PRIMARY KEY,
    company_id          BIGINT NOT NULL REFERENCES companies(id),
    ticket_id           BIGINT REFERENCES tickets(id) ON DELETE SET NULL,
    recipient_user_id   BIGINT REFERENCES users(id),
    channel_id          BIGINT REFERENCES notification_channels(id),
    transport           VARCHAR(15) NOT NULL
                        CHECK (transport IN ('WHATSAPP','SMS','TELEGRAM','PUSH','EMAIL')),
    provider            VARCHAR(30),      -- META_CLOUD | TWILIO | UNIFONIC | TAQNYAT | TELEGRAM
    to_address          VARCHAR(120) NOT NULL,
    template_code       VARCHAR(60),
    payload             JSONB NOT NULL DEFAULT '{}'::jsonb,
    rendered_text       TEXT,
    status              VARCHAR(15) NOT NULL DEFAULT 'QUEUED'
                        CHECK (status IN ('QUEUED','SENDING','SENT','DELIVERED',
                                          'READ','FAILED','SKIPPED')),
    provider_message_id VARCHAR(120),
    error_code          VARCHAR(40),
    error_detail        TEXT,
    attempts            SMALLINT NOT NULL DEFAULT 0,
    scheduled_for       TIMESTAMPTZ NOT NULL DEFAULT now(),   -- quiet-hours deferral
    sent_at             TIMESTAMPTZ,
    delivered_at        TIMESTAMPTZ,
    read_at             TIMESTAMPTZ,
    idempotency_key     VARCHAR(120) UNIQUE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_notif_dispatch ON notifications (scheduled_for)
    WHERE status IN ('QUEUED','SENDING');
CREATE INDEX ix_notif_ticket ON notifications (ticket_id, created_at DESC);

ALTER TABLE escalation_logs ADD CONSTRAINT fk_esc_notification
    FOREIGN KEY (notification_id) REFERENCES notifications(id);

-- Inbound replies: a tapped WhatsApp button becomes a state change.
CREATE TABLE inbound_messages (
    id                  BIGSERIAL PRIMARY KEY,
    transport           VARCHAR(15) NOT NULL,
    provider_message_id VARCHAR(120) NOT NULL UNIQUE,   -- webhook de-duplication
    from_address        VARCHAR(120) NOT NULL,
    matched_user_id     BIGINT REFERENCES users(id),
    ticket_id           BIGINT REFERENCES tickets(id),
    body                TEXT,
    button_payload      VARCHAR(120),      -- 'ACK:1042' | 'ETA:2:1042' | 'ESC_ACK:88'
    parsed_action       VARCHAR(40),
    handled             BOOLEAN NOT NULL DEFAULT FALSE,
    handling_error      TEXT,
    received_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
    raw                 JSONB
);

CREATE TABLE audit_logs (
    id          BIGSERIAL PRIMARY KEY,
    company_id  BIGINT NOT NULL,
    actor_id    BIGINT REFERENCES users(id),
    entity      VARCHAR(40) NOT NULL,
    entity_id   BIGINT NOT NULL,
    action      VARCHAR(30) NOT NULL,
    diff        JSONB,
    ip          INET,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_audit_entity ON audit_logs (entity, entity_id, created_at DESC);

7. Internal API Surface

POST-heavy, REST, JWT bearer, all responses tenant-scoped by the token's company_id.

State transitions must be validated server-side against the §4.2 machine. Return 409 Conflict with the allowed transitions on an illegal move. Do not let the client decide what is legal.

8. Messaging Integration — Recommendations

8.1 ⚠️ The Blocker You Need To Know First

The official WhatsApp Cloud API cannot send messages to WhatsApp groups. There is no group endpoint, and there never has been one for the Business API.

The brief says "Top executives should be auto-included in overview channels/groups." If that means "post ticket updates into our existing WhatsApp group", that is not achievable through any official API. The options, ranked:

Recommendation: WhatsApp 1:1 fan-out for individual accountability, plus a Telegram group as the executive "overview room". Present both to the client and let them choose; the notification_channels table supports either with no code change.

8.2 Provider Comparison

Recommended v1 stack:

1. WhatsApp → Meta Cloud API direct (assignments, acknowledgements, escalations L0–L2)
2. SMS fallback → Taqnyat or Unifonic, fired only when a WhatsApp message reaches FAILED or is undelivered after 15 minutes on a HIGH/CRITICAL ticket
3. Telegram → management overview group (L3 + daily digest)
4. Web Push → in-app, free, for users who do open the PWA

Write everything behind one interface — send(transport, to, template_code, params) → provider_message_id — with the provider selected per-transport in config. Switching from Meta direct to a BSP then costs one adapter class, not a refactor.

8.3 Interactive Templates (this is what makes it work)

WhatsApp template messages support quick-reply buttons, which map directly onto the client's "فيضغط هذا حاجة إن استلمتها". The whole acknowledge-and-commit flow happens inside WhatsApp with zero app installs and zero logins — the difference between a system people use and a system people ignore.

ticket_assigned_ar — category UTILITY

🔧 طلب صيانة جديد

رقم التذكرة: {{1}}
النوع: {{2}}
الأصل: {{3}}
العطل: {{4}}
الأولوية: {{5}}
المُبلِّغ: {{6}}

الرجاء تأكيد الاستلام خلال {{7}}.

Buttons: [استلمت ✅] → payload ACK:{{ticket_id}} · [تعذّر الاستلام] → DECLINE:{{ticket_id}}

ticket_eta_request_ar — sent immediately after acknowledgement

شكراً لك. كم يوم تحتاج لإصلاح {{1}} (تذكرة {{2}})؟

Buttons: [يوم واحد] ETA:1:{{id}} · [يومين] ETA:2:{{id}} · [أكثر] ETA:MORE:{{id}} (Max 3 quick-reply buttons per template. أكثر opens a WhatsApp Flow form or a deep link into the PWA.)

ticket_escalation_ar — to management

⚠️ تنبيه إداري — تذكرة متأخرة

رقم التذكرة: {{1}}
الأصل: {{2}}
العطل: {{3}}
المسؤول: {{4}}
الحالة الحالية: {{5}}
متأخرة منذ: {{6}}
السبب: {{7}}

لم يتم اتخاذ إجراء منذ آخر تحديث.

Buttons: [تم التوجيه] ESC_ACK:{{log_id}} · [إعادة الإسناد] deep link

ticket_resolved_ar — to the requester

✅ تم إصلاح {{1}}

تذكرة: {{2}}
مدة الإصلاح: {{3}}
ملاحظات: {{4}}

هل تم حل المشكلة؟

Buttons: [نعم، أغلق التذكرة] CLOSE:{{id}} · [لا، المشكلة مستمرة] REOPEN:{{id}}

8.4 Implementation Rules

• Outbox only. Application code writes a notifications row inside the same transaction as the state change. A separate worker sends. Never call the WhatsApp API inside a request handler — a provider outage must never fail a ticket update.
• Idempotency keys on every send. Retries must not duplicate messages.
• Retries: 3 attempts, exponential backoff (1m, 5m, 25m). Terminal errors (invalid number, opted out) fail immediately without retry.
• Webhook security: verify Meta's X-Hub-Signature-256 HMAC on every inbound request. De-duplicate on provider_message_id — Meta redelivers.
• The 24-hour window: business-initiated messages outside a 24h customer-service window must use a pre-approved template. Every proactive message in this system is a template. Free-text replies are only possible within 24h of the user's last inbound message.
• Template parameters cannot contain newlines, tabs, or more than 4 consecutive spaces, and are capped at 1024 characters. Sanitise every variable — a multi-line description in {{4}} will get the message rejected at send time.
• Rate limits: new numbers start at 250–1000 business-initiated conversations/24h and scale with quality rating. Fine for this use case, but the escalation cap (§5.3.7) also protects the quality rating.
• Opt-out: honour whatsapp_opt_in; the word إيقاف / STOP sets it false and falls back to SMS/in-app.
• Cost control: utility-category conversations are billed per 24h window per recipient. The de-duplication and cool-down rules are a cost control, not just a UX nicety.

8.5 Lead-Time Warning ⏱️

Two items have external approval lead times and must start on day 1, in parallel with development:

1. Meta Business verification + WhatsApp Business Account — commercial registration documents, typically 1–3 weeks.
2. SMS sender ID registration with CITC via a licensed Saudi aggregator — 1–2 weeks, requires a CR.

Use the Twilio WhatsApp sandbox or Telegram for development so engineering is never blocked waiting on either.

9. Non-Functional Requirements

Suggested stack (aligning with existing infrastructure): PostgreSQL 15+, Django 5 + DRF, Celery Beat + Redis for the scheduler/workers, Next.js or plain PWA front end, deployed behind nginx. Celery Beat handles the 5-minute scan; Celery workers drain the outbox. If speed to first demo matters more than control, a Frappe app gives DocTypes, roles, workflow and a scheduler out of the box — but the escalation engine still has to be hand-written either way.

10. Delivery Plan & Code Task Backlog

Phase 1 — Walking Skeleton (~2 weeks)

• T-01 Postgres schema + migrations (§6.3–6.6) (2d)
• T-02 Tenancy scoping layer + base repository/query-set (1d)
• T-03 Auth: WhatsApp OTP login, JWT issue/refresh (2d)
• T-04 Roles, permission decorator, scope filter (1.5d)
• T-05 Master data CRUD: branches, users, vehicles, facility assets, vendors (3d)
• T-06 Ticket create + list + detail, photo upload to S3-compatible storage (3d)
• T-07 State machine service with server-side transition validation (2d)
• T-08 ticket_status_history auto-write on every transition (0.5d)

Phase 2 — The Engine (~2 weeks) ← the actual product

• T-09 SLA policy matching + deadline computation on assign (1.5d)
• T-10 Notification outbox table + dispatcher worker with retry/backoff (2d)
• T-11 Meta Cloud API adapter: template send, delivery webhook (2.5d)
• T-12 Inbound webhook: signature verify, de-dup, button → state change (2.5d)
• T-13 Escalation scanner (SKIP LOCKED + idempotency keys) (3d)
• T-14 Ladder rule resolution incl. ASSIGNEE_MANAGER graph walk + fallback (1.5d)
• T-15 Quiet hours, cool-down, daily message cap (1d)
• T-16 Acknowledge + ETA flow end-to-end over WhatsApp (2d)

Phase 3 — Custody, Cost & Visibility (~2 weeks)

• T-17 Handover in/out with photos, odometer, vendor (2d)
• T-18 Cost capture, approval thresholds, approval routing (2d)
• T-19 Role-based dashboards + KPI aggregation (3d)
• T-20 Executive daily digest (07:30 cron → WhatsApp/Telegram) (1d)
• T-21 Telegram adapter + management overview channel (1d)
• T-22 Reports: SLA compliance, vehicle cost, vendor turnaround (2d)
• T-23 SMS fallback adapter + failure-triggered routing (1d)

Phase 4 — Hardening & Pilot (~1 week)

• T-24 Offline PWA queue for ticket creation (2d)
• T-25 Audit log + admin config screens for SLA/escalation rules (2d)
• T-26 Seed data, Arabic template approval submissions, UAT with one branch (2d)

Estimate: ~7 weeks of one full-time engineer to a pilot-ready v1, excluding WhatsApp verification lead time.

Pilot strategy: launch with one branch and one vehicle fleet for two weeks before company-wide rollout. The SLA numbers in §5.1 are educated guesses; the pilot exists to replace them with real ones. Ship the escalation engine with the ladder configurable from an admin screen so tuning does not require a deploy.

11. Open Questions for the Client

12. Risks

End of document — v0.1. §11 must be answered before Phase 2 begins.