OCC System Schema
Every system, trigger, data source, and destination — at a glance.
Every system, trigger, data source, and destination — at a glance.
| System | Type | Trigger | Reads From | Writes / Sends To | Auth / Token | Status |
|---|---|---|---|---|---|---|
| occ-portal Customer Portal |
Worker | Customer visits /?id=GD_ID Customer submits form via POST /request |
GorillaDESK: customer info, next job, notes (credit count) KV: nothing on read |
Brevo → info@ (request email) Brevo → info@ (limit alert / seasonal flag) GD: note written on every action |
BREVO_KEY env | Live Manual link |
| occ-sms-dispatch Day-of SMS Sender |
Worker Auto | Cron 0 12 * * 1-5 (7am ET M–F) Manual: ?action=send&token= |
D1 occ-customers: ZIPs + phones for today's route KV: check sms_sent:DATE:ID (skip if already sent) LU_SCHEDULE hardcoded in worker |
Brevo SMS → customer (or Twilio fallback) KV: write sms_sent:DATE:ID (48h TTL) |
TOKEN_SECRET, BREVO_SMS_KEY, TWILIO_* Auth: occ-sms-2026 |
Pending SMS Built, needs phone # approval |
| occ-empty-confirm Tap-to-Confirm Page |
Worker | Customer taps link from SMS GET /?t=TOKEN POST /confirm |
GorillaDESK: customer name + address (?include=locations) KV: check if already confirmed |
KV: write empty:DATE:ID → {status, confirmed_at} (48h TTL) | TOKEN_SECRET (shared with occ-sms-dispatch) | Live Pending SMS |
| occ-signup-page Personalized Plan Signup |
Worker | Customer clicks link from OT email GET /?email=&first=&cans=&touch= POST /signup · GET /book-onetime |
KV: pricing_{email} for discounted rates URL params: customer info pre-loaded |
WooCommerce: create order (POST, not PUT) Brevo: customer confirmation email + Howard notification KV: converted_from_ot_{email}, ot_repeat_{email} |
No auth (public) · WC keys hardcoded | Live |
| occ-ot-followup OT Conversion Sequence |
Worker Auto | Cron 0 9 * * * (daily 9am UTC) | GorillaDESK: completed OT jobs (day 3, 21, 86 lookback) KV: dedup keys per job |
Brevo: 3-email drip to customer (BCC howard@) KV: ot_followup_{jobId}, ot_followup2_, ot_followup3_ |
BREVO_KEY env · Manual: ?trigger=1&token= | Live Automated |
| occ-welcome-email New Customer Welcome |
Worker Auto | Cron (checks GD jobs created in last 2 hrs) Manual: ?trigger=1&token= |
GorillaDESK: new scheduled jobs (M/BM/Q/Prepaid) GorillaDESK: customer + location note (garbage day) KV: pricing_{email} · KV: welcome_{id} dedup |
Brevo: welcome email to customer (BCC howard@) KV: write welcome_{customerId} (90d TTL) |
BREVO_KEY env | Live Automated |
| occ-holiday-notify Holiday Schedule Alerts |
Worker Auto | 3 crons: 0 12 1 * * → monthly overview (1st) 0 12 22 * * → 8-day reminder (22nd) 0 12 * * * → daily 7-day customer check |
Holiday definitions hardcoded in worker KV: hcust_{hauler}_{dow} (customer lists by hauler+day) KV: dedup keys per month |
Brevo → info@ (1st and 22nd internal alerts) Brevo → howard@ (7-day approval request) Brevo → customers (after Howard approves) KV: pending_holiday_{batchId}, holiday_sent_{date} |
BREVO_KEY, SECRET_TOKEN env | Live Automated |
| occ-prepaid-renewal Prepaid Renewal Notices |
Worker Auto | Cron 0 14 * * 1 (Monday 9am ET) Manual: ?scan=1&token=occ-renewal-2026 |
GorillaDESK: live job count per prepaid customer 56 customers hardcoded with GD IDs KV: renewal_sent_{gd_acct} dedup (90d) |
Brevo → howard@ (approval email with batch table) After approval: Brevo → customers (renewal notice, BCC howard@) KV: renewal_sent_{gd_acct} |
All hardcoded · Approval: /approve?token=occ-renewal-2026&batch= | Live Automated |
| occ-square-daily Daily Revenue Report |
Worker Auto | Daily cron (morning, sends yesterday's data) Manual: ?token=occ-square-daily-2026 |
Square: Orders Search, Payments, Refunds APIs (via occ-square-proxy) |
Brevo → info@ (daily transaction email) | occ-square-daily-2026 | Live Automated |
| occ-wc-to-gd WooCommerce → GD Sync |
Worker Auto | Cron (checks WC orders last 48hrs) Manual: ?trigger=1&token= |
WooCommerce: processing/completed orders (last 48hrs) KV: wc_order_{orderId} dedup |
GorillaDESK: creates customer record with full location note KV: write wc_order_{orderId} (90d TTL) |
SECRET_TOKEN env · WC + GD keys hardcoded | Live Automated |
| occ-command-center Command Center Backend |
Worker | Called by OCC_Command_Center.html Cron 0 7 * * * (2am ET nightly GD sync) |
GorillaDESK: customers (paginated, ?include=locations) Square (via occ-square-proxy): revenue, transactions, refunds D1 occ-customers: search, stats, churn, stale WooCommerce: stale account card check |
D1 occ-customers: full nightly sync (upsert) D1 customer_changes: delta log Square (via proxy): refund actions |
occ-cc-2026 | Live |
| occ-square-proxy Square API Proxy |
Worker | Called by Command Center, Refund Tool, Recon Tool, Daily Report | Square: Payments, Orders, Refunds, Revenue APIs | Returns JSON to caller Square: issues refunds (POST action) |
?secret=occ-square-2026 | Live |
| occ-referral-agent Referral Tracker |
Worker | GET endpoints: public POST endpoints: Bearer token ohiocleancans.com/ref/CODE redirect |
D1 referral DB: agents, orders WooCommerce: coupon existence check |
D1 referral DB: register agent, log order, mark contacted WooCommerce: auto-create $10 coupon on agent registration |
Bearer API_SECRET env | Live |
| occ-inbox-sorter Outlook Inbox Sorter |
Worker Auto | Microsoft Graph webhook (real-time, new email) Cron: subscription auto-renewal every 3 days Manual: POST /backlog (50 emails/call) |
Microsoft Graph: inbox messages KV: subscription ID, cursor |
Microsoft Graph: move emails to folders / Deleted Items KV: subscription state |
Microsoft OAuth client credentials · occ-reorg-2026 | Live |
| occ-gas-proxy Management Tool Proxy |
Worker | Called by legacy OCC Management Tool HTML | Google Apps Script (Sheets data) GorillaDESK: new customers, customer count |
Returns JSON to caller | ?token=oyDVMFy-CB1VGX_huR9avQ | Live Legacy |
| OCC_Command_Center.html Central Dashboard |
Local Tool | Open in browser Netlify: occ-tools.netlify.app/OCC_Command_Center.html |
occ-command-center worker (all data) occ-square-proxy (revenue, refunds) occ-sms-dispatch (Game Day status) |
Via workers: D1 sync, Square refunds, GD sync, SMS send | All via worker tokens | Live |
| OCC_Prepaid_Renewal.html Prepaid Renewal (Manual) |
Local Tool | Open in browser · Netlify: occ-tools.netlify.app | GorillaDESK prepaid CSV (public URL) | Brevo: renewal notice email (manually triggered) No KV writes — manual send tool only |
Brevo key in local config | Live |
| OCC_Square_Refund.html Refund Tool |
Local Tool | Open in browser | occ-square-proxy: search payments | occ-square-proxy: issue refund | Via proxy: occ-square-2026 | Live |
| OCC_Square_Reconciliation.html Reconciliation Tool |
Local Tool | Open in browser | occ-square-proxy: full transaction list | Read-only display + CSV export | Via proxy: occ-square-2026 | Live |
| OCC_Bible.html Build Reference |
Local Tool | Open in browser · Active/OCC_Bible.html | Static HTML — no API calls | Reference only | — | Live |
| Key Pattern | Written By | Read By | Value / Purpose | TTL |
|---|---|---|---|---|
| empty:YYYYMMDD:customerId | occ-empty-confirm | occ-empty-confirm, occ-sms-dispatch | {status, confirmed_at, customer_id} — can confirm choice | 48 hrs |
| sms_sent:YYYYMMDD:customerId | occ-sms-dispatch | occ-sms-dispatch | Dedup — prevents re-sending same customer same day | 48 hrs |
| ot_followup_{jobId} | occ-ot-followup | occ-ot-followup | Dedup for OT email 1 (day 3) | Long TTL |
| ot_followup2_{jobId} | occ-ot-followup | occ-ot-followup | Dedup for OT email 2 (day 21) | Long TTL |
| ot_followup3_{jobId} | occ-ot-followup | occ-ot-followup | Dedup for OT email 3 (day 86) | Long TTL |
| converted_from_ot_{email} | occ-signup-page | occ-ot-followup | Kills future OT emails once customer converts to recurring | Long TTL |
| ot_repeat_{email} | occ-signup-page | occ-ot-followup | Marks customer as repeat OT booker — skips emails 1+2 on next job | Long TTL |
| pricing_{email} | occ-signup-page / occ-wc-to-gd | occ-welcome-email, occ-signup-page | First-cleaning discount pricing data for welcome email | 90 days |
| welcome_{customerId} | occ-welcome-email | occ-welcome-email | Dedup — one welcome per customer ever | 90 days |
| wc_order_{orderId} | occ-wc-to-gd | occ-wc-to-gd | Dedup — one GD create per WC order | 90 days |
| hcust_{hauler}_{dow} | occ-holiday-notify (?sync) | occ-holiday-notify | Customer list for hauler + day combo (for pickup delay emails) | 1 year |
| pending_holiday_{batchId} | occ-holiday-notify | occ-holiday-notify | Pending approval batch with customer list | 10 days |
| holiday_sent_{date} | occ-holiday-notify | occ-holiday-notify | Dedup — prevents sending customer emails twice for same holiday | 400 days |
| holiday_internal_YYYY-MM | occ-holiday-notify | occ-holiday-notify | Dedup — one 1st-of-month email per month | 40 days |
| holiday_midmonth_YYYY-MM | occ-holiday-notify | occ-holiday-notify | Dedup — one 22nd reminder per month | 40 days |
| Table | Column | Type | Notes |
|---|---|---|---|
| customers | id | TEXT PK | GorillaDESK customer ID |
| first_name | TEXT | ||
| last_name | TEXT | ||
| TEXT | |||
| phone | TEXT | ||
| address | TEXT | From locations[0].address_line_1 — requires ?include=locations on sync | |
| city | TEXT | ||
| state | TEXT | ||
| zip | TEXT | Used by occ-sms-dispatch to find today's customers by route ZIPs | |
| status | TEXT | active / inactive | |
| tags | TEXT | JSON array — contains plan type | |
| service_type | TEXT | ||
| created_at | TEXT | ISO date from GD | |
| synced_at | TEXT | Last sync timestamp | |
| customer_changes | id | INTEGER PK | Auto-increment |
| customer_id | TEXT | FK → customers.id | |
| field | TEXT | What changed (status, email, etc.) | |
| old_value | TEXT | ||
| changed_at | TEXT | ISO timestamp | |
| sync_log | id | INTEGER PK | Auto-increment |
| synced_at | TEXT | ||
| total | INTEGER | Customers synced | |
| notes | TEXT | Any sync errors or notes |
| Table | Column | Type | Notes |
|---|---|---|---|
| referral_agents | id | INTEGER PK | Auto-increment |
| name | TEXT | ||
| TEXT | |||
| code | TEXT UNIQUE | 2-letter + 3-digit code (e.g. AB123) | |
| wc_coupon_id | INTEGER | WooCommerce coupon ID for this agent | |
| contacted | INTEGER | 0/1 — marked by Howard | |
| referral_orders | id | INTEGER PK | Auto-increment |
| agent_code | TEXT | FK → referral_agents.code | |
| order_id | TEXT | WooCommerce order ID | |
| customer_email | TEXT | ||
| order_total | REAL |