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.

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.

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 tracking (yet). ticket_call_events.kind enum has an outbound_attempt slot reserved for future "hospital called back the missed customer" logging. Wiring deferred.

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. - 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. 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.
• 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 — SQL builder + decision helpers + wildcard logic
• api/src/jobs/ticketsFromCallLogsScheduler.js — poll loop + per-row processing + stats
• api/src/models/Ticket.js — upsertFromCdr (the find-or-increment with row-locking) + sweepArchive
• api/src/models/TicketCallEvent.js — recordSafe (the idempotent insert)
• api/tests/call-logs-ticket-query.test.js → CL1-CL22 (SQL shape, dedup, wildcard, session-settle)
• api/tests/tickets-from-call-logs-scheduler.test.js → S1-S8 (poll loop behaviour)
• api/tests/ticket-upsert-idempotency.test.js → U1-U6 (per-linkedid guard branches; counter no-op on dedup; legacy no-linkedid back-compat)
• Subscribe to Tickets (Daily Missed-Call Alerts) — operator-side daily summary