Tickets Architecture¶

Canonical reference for how missed-call tickets are created, deduplicated, and surfaced in the editor. Read this before touching ticketsFromCallLogsScheduler, callLogsTicketQuery, the tickets / ticket_call_events tables, or the tickets page in the editor.

See also: Subscribe to Tickets (Daily Missed-Call Alerts) for the operator notification side.

TL;DR¶

• The call-logs-driven scheduler is the canonical pipeline for missed-call ticket creation across all orgs.
• Reads from asterisk_cdr using the same ANSWERED+billsec>0-preferring dedup the /api/v1/calls UI uses, so the operator's call-log and ticket views never disagree.
• Settle window operates at session level (max end_time across all rows of a linkedid), so multi-retry calls never produce false-positive tickets.
• Enabled org-wide via TICKETS_FROM_CALLLOGS_ENABLED_ORG_IDS='*' — new tenants auto-enrolled.
• Manual ticket close only; no auto-close magic.
• The same scheduler runs a Callback detection pass each tick: it flags an open ticket whose number has since had a completed (answered) call in either direction, surfaced as the editor Callback column ("Found answered call 49s at 9:52 am"). See Callback detection.
• An optional Owner/Admin per-org policy — Ensure Callback — turns that callback signal into a close precondition: when on, a ticket can't be closed until a callback is found.

Data model¶

tickets table¶

Dedup invariant: at most one open ticket per (org_id, caller_number). Repeat calls increment missed_count and re-evaluate priority. Closed tickets are NOT reopened — a fresh call after close starts a brand-new ticket.

ticket_call_events table¶

Append-only timeline of every call attempt recorded against a ticket — the unique fingerprint of the new pipeline (legacy classifier never wrote here). Populated by the scheduler.

UNIQUE (ticket_id, linkedid) is the idempotency guard. The scheduler's window overlaps consecutive polls; re-emitting the same event on the overlap is a silent no-op.

Pipeline (call-logs-driven scheduler)¶

Poll cycle every 60 seconds. Implemented in api/src/jobs/ticketsFromCallLogsScheduler.js.

1. parseEnabledOrgs(process.env.TICKETS_FROM_CALLLOGS_ENABLED_ORG_IDS)
   → Set of org UUIDs OR the wildcard token '*'
   → empty set = scheduler idle (no-op)

2. buildMissedCallsQuery({ orgIds, windowSecs=300, settleSecs=60 })
   → returns the SQL contract below

3. sequelize.query(sql, replacements) → one row per linkedid

4. For each missed row:
     decideSourceAndKind(row) → { source: 'queue_timeout'|'missed_call', kind: 'missed' }
     Ticket.upsertFromCdr(...) → find-or-increment per (org, caller)
     TicketCallEvent.recordSafe({ ticket_id, linkedid, occurred_at, kind, meta })
       → duplicate-key on (ticket_id, linkedid) absorbed silently

5. One ticketStream.broadcast per org per tick → editor SSE refresh

SQL contract — session-level settle¶

WITH ranked AS (
  SELECT
    c.linkedid, c.uniqueid, c.accountcode AS org_id, c.src, c.dst,
    c.dstchannel, c.lastapp, c.duration, c.billsec, c.disposition, c.calldate,
    DATE_ADD(c.calldate, INTERVAL c.duration SECOND) AS end_time,
    -- LATEST end_time across the whole session (all rows sharing this linkedid).
    MAX(DATE_ADD(c.calldate, INTERVAL c.duration SECOND))
      OVER (PARTITION BY c.linkedid) AS session_end_time,
    ROW_NUMBER() OVER (
      PARTITION BY c.linkedid
      ORDER BY
        CASE WHEN c.disposition='ANSWERED' AND c.billsec>0 THEN 1 ELSE 0 END DESC,
        c.duration DESC, c.id DESC
    ) AS rk
  FROM asterisk_cdr c
  WHERE c.channel NOT LIKE 'Local/%'
    AND c.dcontext LIKE '%\_incoming' ESCAPE '\\'
    AND <org filter — IN(orgIds...) OR accountcode IS NOT NULL for wildcard>
    AND DATE_ADD(c.calldate, INTERVAL c.duration SECOND)
          >= DATE_SUB(NOW(), INTERVAL <prefilterSecs> SECOND)
)
SELECT * FROM ranked
WHERE rk = 1
  AND session_end_time
        BETWEEN DATE_SUB(NOW(), INTERVAL <windowSecs> SECOND)
            AND DATE_SUB(NOW(), INTERVAL <settleSecs> SECOND)
  AND NOT (
    disposition='ANSWERED' AND billsec>0
    AND (dstchannel REGEXP '^PJSIP/[a-zA-Z0-9_-]+-'
         OR dstchannel REGEXP '^Local/qm[a-f0-9]{32}@')
  )
ORDER BY session_end_time ASC

Three things this query gets right that the legacy classifier did not:

1. Session-level settle. Multi-retry queue calls emit one CDR row per retry attempt (NO ANSWER then ANSWERED seconds later). Settling on session_end_time ensures every retry row is in the DB before we pick a representative — so the dedup correctly picks the ANSWERED row and no false-positive ticket is ever created.
2. Dedup matches the call-logs UI. ROW_NUMBER() PARTITION BY linkedid ORDER BY (ANSWERED+billsec>0) DESC — same predicate the /api/v1/calls endpoint applies (test S2 in api/tests/server-routes.test.js enforces this). The tickets table and call-logs view always agree on what was missed.
3. Bridged-answer exclusion. The outer NOT (disposition='ANSWERED' AND billsec>0 AND <real bridge regex>) filter ignores any row that successfully bridged to a real PJSIP endpoint OR through the qm-helper context — those are completed calls, not misses.

Callback detection¶

Same scheduler, second pass per tick (detectCallbacks → buildAnsweredCallbackQuery). It answers: "has this open ticket's number since been reached by a completed call?" — so an agent doesn't waste time calling back someone who already got through. Hospital reception routinely misses a call and the patient calls right back (or reception calls them back) minutes later; the ticket stays open but the person is reached. Surfaced as the editor Callback column.

What it does each tick (runs even when there were no missed rows this tick — a callback can land on a ticket created long ago):

detectCallbacks(enabledOrgs):
  rows = buildAnsweredCallbackQuery({ orgIds, windowSecs: CALLBACK_WINDOW_SECS })
  for each row:
    UPDATE tickets SET callback_found_at=?, callback_duration_sec=?, callback_linkedid=?
     WHERE id=? AND callback_found_at IS NULL AND status IN ('open','in_progress')   -- guarded set-once
  one ticketStream.broadcast per org actually stamped (rolled into the missed-call tick's broadcast)

Design points (all enforced by api/tests/call-logs-callback-query.test.js CB1–13 and tickets-callback-detection.test.js CBD1–5):

1. Direction-agnostic. A call "reached the customer" when the ticket's caller_number (always the customer's last-10, never an internal extension or the org's own DID) appears on either end of an answered leg — src (they called in) or the dialled dst (we called them). The two are unpivoted into one cust10 via UNION ALL, so the tickets join is a single equality (t.caller_number = l.cust10) that uses idx_tickets_org_caller_status — = src OR = dst would force a full tickets scan.
2. Same "real bridge" predicate as missed-call detection. A leg counts only when disposition='ANSWERED' AND billsec>0 and dstchannel REGEXP '^PJSIP/<ep>-' or ^Local/qm<32hex>@. This excludes voicemail/IVR "answers", so a customer who calls again and just hits voicemail is not falsely flagged as reached. Tickets, the call-logs UI, and callback detection all agree on what "completed" means.
3. Anchored on last_call_at, not created_at. The scheduler creates the ticket 60–300s after the miss (settle window), so created_at lags — a patient calling right back would be < created_at and missed. last_call_at is the miss's end-time (same source/timezone as calldate) and advances to the latest miss, so the check means "answered after the most recent miss".
4. Set-once + clear semantics. The first answered session (earliest MIN(calldate) per ticket; multi-leg sessions collapse per linkedid with MAX(billsec) = talk time) stamps the ticket and the guarded UPDATE makes overlapping polls no-ops. A fresh miss (upsertFromCdr increment branch) clears the three fields so a stale "reached" can't hide a newer miss — the detector then re-fires for an answer after the new miss. Closing a ticket (PATCH /tickets/:id) also clears them, so a reopened ticket re-detects cleanly.
5. CALLBACK_WINDOW_SECS = 1 day (TICKETS_CALLBACK_WINDOW_SECS, default 86400). The window only bounds the CDR scan — the precise filter is the JOIN's l.calldate > last_call_at. 1 day is the actionable horizon (a callback from last week is stale) and is a small enough fraction of CDR retention that the scan stays an idx_calldate range (verified on staging); larger windows can tip the optimiser into a full table scan. On first deploy this also backfills open tickets with a callback in the last day.

No new columns on ticket_call_events and no new kind — callback is a per-ticket scalar denormalised onto tickets (the Callback column is read on every list load, so it must be an O(1) column read, not a join). The reserved outbound_attempt event kind is still unused; wiring callbacks into the timeline panel is a possible later follow-up.

Ensure Callback (close gate)¶

An optional per-org policy (PR #468 / 2026-06-19): when on, a ticket can only be closed once an answered callback has been detected for it (callback_found_at is set — see Callback detection). When the gate blocks a close, the operator sees "No answered callback yet — can't close"; once the detector stamps the green "Found answered call …", Close is allowed.

Why: for reception/clinic workflows "don't close a missed-call ticket until the patient has actually been reached" is a real SLA. The callback detector already knows when a number was reached — this just turns that signal into a close precondition. It adds no CDR/scheduler logic; it only reads the scalar the detector maintains.

Storage. One boolean on the org — organizations.settings.ticket_ensure_callback = { enabled: bool } (JSON column, no migration; same pattern as ticket_whatsapp). Round-tripped via:

Enforcement is two-layer (defence in depth):

1. Client gate — the tickets page disables the Closed status button (with a tooltip) and toasts when ensureCallback && !selected.callback_found_at. The toggle sits next to the Status/Source/Date filters and is visible only to Owner/Admin (localStorage.user_role, read in a useEffect to stay hydration-safe). A green/amber hint in the detail drawer reuses the existing formatCallback().
2. Server gate — PATCH /api/v1/tickets/:id rejects the close with 400 ("An answered callback is required before closing this ticket.") when the org policy is on and the ticket has no callback_found_at. The check reads the ticket's current callback_found_at before the close branch clears the callback_* fields — so it sees the real value, not the about-to-be-nulled one.

Interaction with set-once/clear semantics. Closing still clears callback_found_at (existing behaviour). A ticket reopened after a gated close therefore needs the callback pass to re-detect (it guards on callback_found_at IS NULL) before it can be closed again — consistent with "the customer must have been reached since the latest miss". The policy only gates the open|in_progress → closed transition; re-saving an already-closed ticket is unaffected.

Bulk close & "Sweep Called"¶

Two operator paths to close many tickets at once, both governed by one hard invariant: a ticket is only ever bulk-closed if it has an answered callback (callback_found_at IS NOT NULL). "Call not found" tickets (em-dash in the Callback column) are never touched by either path — independent of the org's Ensure Callback toggle (and always consistent with it, since bulk close only ever closes callback-found tickets). This lets a reception desk clear the backlog of already-reached callers in one action without risking an un-reached caller being silently closed.

One model helper carries the invariant — Ticket.bulkCloseCallbackFound(org_id, ids = null) in api/src/models/Ticket.js. The WHERE clause is the safety rule (there is no code path that closes a call-not-found ticket in bulk):

UPDATE tickets SET status='closed', closed_at=NOW(),
  callback_found_at=NULL, callback_duration_sec=NULL, callback_linkedid=NULL, updated_at=NOW()
WHERE org_id=? AND status IN ('open','in_progress') AND callback_found_at IS NOT NULL
  [AND id IN (?, …)]   -- the id-list clause is present ONLY in ids-mode

It closes (clears the callback_* fields exactly like the single-ticket PATCH, so the 24h lazy archive sweep + any re-detect behave identically) and returns { closed: affectedRows }.

Two modes, one endpoint — POST /api/v1/tickets/bulk-close (authenticateOrg):

After either mode the handler fires ticketStream.broadcast(orgId, { type: 'refresh' }) so every open tickets page (and the sidebar badge) updates within one SSE poll. No remarks/notes are required (the single-ticket PATCH never required them either).

Editor UI (editor/app/dashboard/[orgId]/tickets/page.tsx, Owner/Admin-gated): - Multi-select — a leading checkbox column on the tickets tab. The per-row checkbox is disabled on call-not-found rows (disabled={!isClosable(ticket)}, where isClosable mirrors the server WHERE: callback_found_at set and status open/in_progress) with a "no answered callback — can't bulk close" tooltip, so the invariant is visible, not just server-enforced. Select-all picks eligible rows only. Ticking a box uses e.stopPropagation() so it doesn't open the detail Sheet. Selection clears on tab/filter/page change. A bulk action bar ("N selected · Close N · Clear") drives mode:'ids'. - "Sweep Called" header button (BrushCleaning icon) → a confirm → running → done Dialog. runSweep() calls mode:'sweep', then animates a 0→N count-up (requestAnimationFrame) over "tickets cleaned" (shows "No called tickets to clean" when closed is 0). Sweep is server-side and org-wide, so it clears called tickets on pages the operator never scrolled to.

The client disabled/eligibility checks are UX, not the guard — the server WHERE is the backstop: a hand-crafted mode:'ids' POST containing a call-not-found id closes nothing (closed:0). Covered by api/tests/tickets-bulk-close.test.js.

Wildcard flag — *¶

TICKETS_FROM_CALLLOGS_ENABLED_ORG_IDS='*' means "all orgs go through this scheduler". The SQL drops the accountcode IN (...) clause and replaces it with accountcode IS NOT NULL AND <> ''. The legacy classifier gate in pollCdr honours the wildcard via isOrgEnabled(orgId, set) and short-circuits to skip-for-everyone.

Operational implication: new tenants created via the API are auto-enrolled. No env var update needed. No deploy needed. The next inbound call from the new org's DID is picked up by the scheduler within 60s.

To narrow the rollout (e.g. for an experiment), set the env to specific UUIDs:

TICKETS_FROM_CALLLOGS_ENABLED_ORG_IDS=<uuid-a>,<uuid-b>

Anything not in the list falls back to the legacy classifier (which still exists, just unused under wildcard).

What the scheduler does NOT do¶

• No auto-close. Once a ticket is open, only operator action (or the lazy archive sweep) changes its status. The legacy classifier had a cross-batch UPDATE that closed bogus tickets when a later ANSWERED row arrived; the new scheduler prevents the bogus ticket from being created in the first place (via session-level settle), so the auto-close isn't needed.
• No bot_dropped detection (yet) — see Issue #215. The legacy classifier created bot_dropped tickets when an AI agent answered and the caller dropped under 8s. The new scheduler needs an AI-bridge column added to the call-logs SQL to surface this — deferred until any org with AI agents (GrandEstancia) starts generating live bot-handled traffic.
• No outbound-attempt timeline event (yet). ticket_call_events.kind enum has an outbound_attempt slot reserved for future per-attempt logging. Note: the "has the customer been reached since the miss?" question is already answered by Callback detection (a per-ticket scalar on tickets, either direction) — what remains deferred is surfacing each such call as a row in the drawer's call-timeline panel.

Editor surfaces¶

The tickets page at /dashboard/<orgId>/tickets reads from these endpoints:

UI features wired off this: - Counts header strip ("Open: N · Closed: M") next to the Refresh button — excludes archived. - Sortable list with open tickets pinned to the top regardless of recency. - Closed time column — shows closed_at formatted, or em-dash for open rows. - Callback column (replaced the old "Category" column) — renders "Found answered call {billsec}s at {h:mm a}" (green, with a phone-incoming icon) when callback_found_at is set, else em-dash. Read straight from the denormalised tickets.callback_* columns; the existing SSE refresh shows newly-stamped callbacks within one poll. Mirrored on the mobile card. See Callback detection. - Bulk close + "Sweep Called" — a multi-select checkbox column (disabled on call-not-found rows) with a "Close N" bar, and a "Sweep Called" header button that closes every call-found ticket org-wide with an animated count-up. Both refuse call-not-found tickets. See Bulk close & "Sweep Called". - Call timeline panel — fetched on Sheet drawer open; shows [Missed] / [Bot Dropped] / [Outbound] badge + timestamp + duration per attempt. - Missed-count fallback — auto-generated tickets render N missed call(s) in the Summary column when summary is empty. - Drawer "Missed attempts" line — sources from selectedEvents.length (the call-timeline count) when events are loaded; falls back to tickets.missed_count only for legacy tickets that pre-date the call-logs scheduler and have no events. This guarantees the headline number matches the timeline below it. Implemented in editor/app/dashboard/[orgId]/tickets/page.tsx after PR #236 / 2026-05-18.

Sidebar "Tickets" badge — MariaDB SSE, not Firestore¶

The red count next to the "Tickets" menu item in editor/components/layout/Sidebar.tsx reads subscribeToOpenTicketCount(orgId, …) from @/lib/tickets/api — the same SSE-driven hook the org overview "Open Tickets" card uses. Until PR #240 / 2026-05-18 the badge instead held a Firestore onSnapshot listener on astrapbx/<orgId>/tickets where status=='open'; that diverged from the canonical MariaDB store under TICKETS_FROM_CALLLOGS_ENABLED_ORG_IDS='*' (the API still dual-writes to Firestore via the events.astradial.com proxy, and the Firestore lifecycle isn't kept in lockstep with MariaDB). After the cutover, the badge, overview card, list, and drawer all read the same store and always agree.

The events.astradial.com → Firestore POST in api/src/server.js:~7670 still fires for every inbound CDR. Removing it is a separable cutover (any external consumers of the Firestore tickets collection need to migrate first); the badge migration just stopped reading the Firestore side.

Legacy classifier (still present, gated)¶

api/src/services/ticketClassifier.js and the per-row classifyAndUpsertTicket call inside pollCdr are still in the codebase for orgs explicitly excluded from the wildcard (currently: none). The classifier:

• Reads per-row from asterisk_cdr.
• Applies a disposition override for IVR/queue-abandoned ANSWERED rows (cdrDispositionOverride.js).
• Has cross-batch auto-close logic for the case where a NO_ANSWER row creates a ticket before the ANSWERED row arrives.

It will be removed once the new scheduler has been running stably long enough that the legacy code is genuinely dead weight. Don't add new features there.

Gotchas¶

• Wildcard interaction with the legacy gate. isOrgEnabled(orgId, set) returns true if the set contains '*' OR the specific UUID. Both the SQL builder and the pollCdr gate consult this helper — don't .has() the set directly or you'll miss the wildcard.
• ticket_call_events.linkedid is the natural key, not uniqueid. Multi-retry sessions share a linkedid; uniqueid differs per retry. The UNIQUE index is on (ticket_id, linkedid).
• Settle / window defaults are env-tunable. TICKETS_FROM_CALLLOGS_POLL_MS, TICKETS_FROM_CALLLOGS_WINDOW_SECS, TICKETS_FROM_CALLLOGS_SETTLE_SECS — defaults 60_000 / 300 / 60. Operator tolerance for ticket latency is 60-90s past call end; don't shrink settle below ~30s without verifying multi-retry calls don't regress.
• No backfill (missed-call pass). The scheduler only sees sessions whose session_end_time is inside the current window. A session that ended >5 minutes ago when the scheduler is OFF (or the window misses it) will NOT be picked up on a later restart. For now this is acceptable — the daily WhatsApp alert scheduler reads from tickets and doesn't depend on backfill. (The callback pass is different: its 1-day window means deploying it does backfill the last day of callbacks for already-open tickets.)
• Callback join: COLLATE the derived key, not the column. asterisk_cdr is created by Asterisk/ODBC (utf8mb4_uca1400_ai_ci on MariaDB 11) while tickets is Sequelize-created (utf8mb4_unicode_ci). The callback join compares a CDR-derived cust10 to tickets.caller_number, so without an explicit collation it errors "Illegal mix of collations". buildAnsweredCallbackQuery collates the derived cust10 to utf8mb4_unicode_ci (the tickets column's collation) — collating the column instead would defeat its index. This only bites when you JOIN a CDR-derived string to a tickets column; the missed-call query compares CDR columns to literals and never hits it.
• Callback window is a scan bound, not the filter. TICKETS_CALLBACK_WINDOW_SECS (default 86400 = 1 day) only limits how far back the CDR leg scan reads; the precise "after the miss" filter is the JOIN's l.calldate > last_call_at. Keep it a small fraction of CDR retention so the scan stays an idx_calldate range — a multi-week window can flip the optimiser to a full table scan (observed at 30d on staging's small table).
• Ticket.upsertFromCdr MUST be called with linkedid from the scheduler. The 300s scan window vs 60s poll interval means every CDR row appears in ~4 consecutive polls before its session_end_time slides out. Without per-linkedid idempotency on the increment side, missed_count inflates ~4× per actual call. Fixed PR #240 / 2026-05-18: upsertFromCdr accepts a linkedid arg and, when an existing ticket is found AND a ticket_call_events row already exists for (ticket_id, linkedid), the increment is a no-op (only last_call_at is nudged forward). Scheduler passes r.linkedid || r.uniqueid — same key TicketCallEvent.recordSafe uses, so the two stay in lockstep on overlap. Legacy classifier and manual-ticket callers don't pass linkedid and retain the old unconditional-increment behaviour (the legacy classifier uses lastCdrId watermarking, not a window, so it doesn't have the overlap shape). Reproduced before the fix: 2 actual CDR rows, 2 events, missed_count=8 (caller 9677949475, Thangavelu Hospital). One-shot backfill SQL ran 2026-05-18 to repair ~29 inflated open tickets and recompute their priorities; legacy tickets with zero events (predate the events table) were intentionally left untouched. Six unit tests cover the branches: api/tests/ticket-upsert-idempotency.test.js.

Where to read more¶

• api/src/services/callLogsTicketQuery.js — buildMissedCallsQuery + buildAnsweredCallbackQuery (callback SQL) + decision helpers + wildcard logic
• api/src/jobs/ticketsFromCallLogsScheduler.js — poll loop + per-row processing + detectCallbacks + stats
• api/src/models/Ticket.js — upsertFromCdr (find-or-increment with row-locking; clears callback_* on a fresh miss) + sweepArchive
• api/src/models/TicketCallEvent.js — recordSafe (the idempotent insert)
• api/database/migrations/20260609130000-add-ticket-callback.js — the callback_* columns
• api/tests/call-logs-ticket-query.test.js → CL1-CL22 (SQL shape, dedup, wildcard, session-settle)
• api/tests/call-logs-callback-query.test.js → CB1-13 (callback SQL: direction-agnostic, real-bridge, collation, last_call_at anchor, single-equality join)
• api/tests/tickets-callback-detection.test.js → CBD1-5 (guarded set-once UPDATE, runs with zero missed rows, failure doesn't abort the tick)
• api/tests/tickets-from-call-logs-scheduler.test.js → S1-S8 (poll loop behaviour)
• api/tests/ticket-upsert-idempotency.test.js → U1-U8 (per-linkedid guard branches; counter no-op on dedup; legacy no-linkedid back-compat; U7/U8 clear-callback-on-new-miss vs no-op branch)
• Subscribe to Tickets (Daily Missed-Call Alerts) — operator-side daily summary