Dispatch Flow Control WWT World Wide Technologies · Snowflake / Postgres → Diff ETL → Twilio
loading…

Proof

my sent messages in Twilio ↗

Group

Chart guide — every group, and what it earns you

The data model — one governed brain, live from Snowflake

Pulled from DISPATCH.INFORMATION_SCHEMA. Key columns only — 🔑 primary key, ↗ foreign key. Solid lines are joins; dashed lines are the feedback loops that make it self-correcting.

The driver The freight The decision layer The closed loop

DRIVERS SF

🔑 DRIVER_ID
PHONE_E164 · EMAIL
CURRENT_TIMEZONE ← position
EQUIPMENT_TYPE

DRIVER_NOTIFICATION_PREFS SF

🔑↗ DRIVER_ID
DO_NOT_CONTACT absolute
QUIET_HOURS · MAX_MSGS_PER_DAY
BEST_CONTACT_CHANNEL

DRIVER_HOS_STATUS SF · ELD

🔑↗ DRIVER_ID
DUTY_STATUS driving?
DRIVE_HOURS_REMAINING
WINDOW_END_TS 14-h wall

LOADS SF

🔑 LOAD_ID
ORIGIN → DEST · PAY_AMOUNT
EST_DRIVE_HOURS HOS math
STATUS · PICKUP_DEADLINE_TS

LOAD_RECOMMENDATIONS SF · scoring engine

🔑 RECOMMENDATION_ID
↗ DRIVER_ID   ↗ LOAD_ID
MATCH_SCORE 0–1
COMPUTED_AT volatile — never synced

ELIGIBLE_PERFECT SF · VIEW

the nine-gate waterfall as SQL
consent · safety · legality · timing
caps · quiet hours · best-load-only

NOTIFICATION_QUEUE SF + PG twin · outbox

🔑 QUEUE_ID = sync PK
↗ RECOMMENDATION_ID · ↗ DRIVER_ID
BODY frozen at enqueue
STATUS: pending→eligible→sent

V_NOTIFICATIONS_TO_SEND_* SF+PG · VIEWS

QUEUE_ID · TO_ADDRESS · BODY
all the sync engine sees —
no volatile column can re-fire a send

DRIVER_INTERACTIONS PG→SF mirror

🔑 INTERACTION_ID · ↗ DRIVER_ID
BODY · DIRECTION
CLASSIFIED_INTENT STOP→DNC

NOTIFICATIONS_SENT PG→SF mirror

🔑 SENT_ID · ↗ QUEUE_ID
PROVIDER_MSG_SID Twilio receipt
DELIVERY_STATUS · SENT_AT

DIFF SYNC → TWILIO

diff on 🔑 QUEUE_ID · one SMS per new row
exactly once, by construction
❝

Reach is cheap — the moment is everything. Twilio is the only layer of this stack that touches a human, and everything upstream exists to earn that touch: what to send, to whom, and when — consented, timed, exactly once.

Benny Garner
Sales Engineering

Help

The waiting queue — … messages held on purpose

Every held message is a decision, not a defect. Click a reason to see the exact drivers behind it. Below: what each hold protects, and which buyer at a prospect account cares about it most.

⚖️ Compliance & Legal — General Counsel, Compliance Officer

Quiet hours · do-not-contact · consent gating. TCPA statutory damages run $500–$1,500 per text and class actions are filed on autopilot — one bad campaign to 10,000 drivers is an eight-figure exposure. Here, STOP becomes hard suppression in under a minute, quiet hours follow the driver's current timezone, and every send is audit-trailed (sync logs + Twilio delivery records). The pitch: compliance as enforced pipeline, not training material.

🛡️ Safety & Operations — VP Ops, Safety Director

DRIVING suppression · HOS-illegal filtering. FMCSA penalties reach ~$16k per HOS violation, and CSA scores drive insurance premiums. This system never buzzes a phone at 70 mph and never advertises a load the driver can't legally haul — the two biggest red bands in the chart are safety refusals. The pitch: fewer violations, defensible duty-of-care, drivers who don't feel spammed into unsafe behavior.

📈 Growth & Marketing — CMO, Head of Driver Experience

Perfect-timing holds · frequency caps · best-load-only. Waiting for a meal window and sending one great match instead of nine mediocre ones is why reply rates run ~2.4× higher and opt-outs drop ~64% (modeled). Every message held here is list-equity preserved — the contact list is a depreciating asset this queue protects. The pitch: fewer, better messages that keep the channel alive.

🏗️ Data Platform — Head of Data / Analytics Engineering

The whole queue lives in the warehouse. No second copy of customer data in a marketing tool: eligibility is SQL, the queue is a table, the diff engine activates it in place and syncs only diffs. Governance, lineage, and debugging happen where the data already lives. The pitch: activation without surrendering the source of truth.

💰 Finance — CFO

Every held message is spend not wasted. Volume down ~57% while conversions rise; per-message channel fees only go to messages with a chance to convert; and the whole delivery layer is configuration — no custom pipeline team to fund. The pitch: revenue lift (faster load pickup) with a shrinking messaging bill.

Engagement/volume figures are modeled on the synthetic pilot; legal figures are statutory (TCPA 47 U.S.C. §227; FMCSA 49 CFR 395).

Dispatch Flow Control — presenter guide

What this dashboard is

A single screen showing the whole notification pipeline: Snowflake computes who should be told about which load (nine eligibility gates), Postgres carries realtime events, a differential ETL layer detects exactly what's new, and Twilio Programmable Messaging turns each new row into a delivered SMS. Everything on screen is live data.

Reading the flow chart

Red bands = compliance & safety suppressions (do-not-contact, no consent, driving, HOS-illegal). Amber = held, on purpose (quiet hours, waiting for a meal window, frequency caps, the trial gate). Green = flowing toward delivery. Click any band or node to see the actual drivers there and why. Click again to clear.

The buttons

Q & A you may get

Why does nobody get texted twice?
The sync layer diffs on a stable primary key (QUEUE_ID) and the message body is frozen at enqueue — a row can never look "changed", so it syncs exactly once. Prove it live: trigger a sync twice; the second run shows 0 added.
Why is the biggest red band "HOS-illegal"?
Most recommendations die because the load doesn't fit the driver's remaining legal drive hours (49 CFR 395). The warehouse refuses to advertise freight a driver can't legally haul.
Why do Twilio attempts fail?
By design: trial credentials + magic numbers mean every API call is real but delivery is refused at Twilio's gate. In a regulated channel, that's the last line of defense working. Swap production credentials and the identical rows go to real phones.
What's the promoter task for?
Log-based CDC reacts to data changes, not the clock. "Send at 3pm" writes nothing at 3pm — so a 1-minute job physically flips due rows to eligible; that UPDATE is the event.
Realtime vs perfect-timing?
Realtime = event-born (geofence, dispatch), seconds-to-a-minute. Perfect-timing = held for the driver's meal window or an active reply session, released in 1-minute batches, best load only.

Use-case checklist (ticks saved in this browser)

Good to know

Data refreshes: chart lineage is recomputed on demand (♻); the platform tiles poll every 5 s. Recompute is atomic — viewers never see empty tables. This page: twilio.cuppy.io. Backend: systemd flow-viewer · data in Supabase VIEWER schema · full script in deck/DEMO_RUNBOOK.md.

Where every recommendation goes

Live lineage across all four platforms — click any band or node to see the actual records behind it.

Suppressed — compliance & safety, never sends Held — right message, wrong moment Flowing — on its way to delivery Twilio-safe mark — stopped before the API; only clean, compliant traffic sends

Presenter actions

Run the demo
Sync engine
Data
Driver stories — one click to their view

Records

Click the chart to filter. Showing everything.

DriverLaneStageWhy Match strengthBest load / messageMatches