OCC Build Bible
Everything we've built — details, URLs, credentials pointers, and how it all connects.
Everything we've built — details, URLs, credentials pointers, and how it all connects.
| What it does | Details |
|---|---|
| Purpose | Central dashboard replacing the old management tool. All OCC operations in one place — customers, routes, money, prepaid, automations. |
| Backend Worker | occ-command-center — proxies all API calls (GD, Square, D1). Auth token: occ-cc-2026 |
| D1 Database | ID: e1ab48e2-7f56-425a-95e2-ae1a58adc93d — tables: customers, sync_log, customer_changes |
| Nightly sync | Cron 0 7 * * * (2 AM ET) — full GorillaDESK sync. Apr 5 cleanup: 1,866 active / 2,733 inactive (1,797 with QB ID, 69 new signups). Sync now respects inactive status — won't overwrite manual cleanup. |
| Modules | |
| Dashboard | Live Square revenue KPIs, quick actions, worker status, morning brief alerts (urgent prepaid, watch list) |
| Morning Brief | Today's route, stops, crew, prepaid alerts, live Square revenue (this month, YTD gross/fees/net), monthly bar chart vs $37k target. Morning Watch merged in — no CSV uploads needed, all automatic. |
| Customers | Lookup (D1 search), Prepaid Renewals, Stale Accounts, Churn, Referrals, Customer Reminders (6-slot system) |
| Prepaid Renewals | 55 customers with live sheet sync, urgency badges (Urgent=1 left, Watch=2, Coming Up=3-4, OK=5+), renewal notice builder, Mark Sent tracking (localStorage toggle per customer with date stamp) |
| Stale Accounts | WC-based logic: no saved card AND no order in 3 years = stale. "Run WC Check" button, "Mark All Inactive" (D1 only — GD done manually) |
| Routes | 5 tabs: Route Health (zone cards, truck load bars, dismissable flags, fleet table), Day by Day (ZIP-level per day), Density by ZIP (57 ZIPs), Profitability, Game Day (SMS sent/confirmed/skipped/no response KPIs + per-customer status table, Send SMS Now button) |
| Money | Revenue (live Square), Financials (2025 QB actuals + 2026 projected), Forecast, A/R, Reconciliation (full Square transactions with gross/fees/net/card/CSV export), Refund tool with log |
| Pricing | All tiers M1–M8 with monthly and annual prepaid rates (2026 rates, no bimonthly/quarterly) |
| Customer Profile | NEW Apr 5: Full-screen overlay, 6 tabs (Overview, Contacts & Locations, Service History, Billing, Activity, Notes), persistent right sidebar, yellow Top Note, invoice slide-out with Send dropdown (Email/SMS/Both), Add Payment + Edit on unpaid invoices |
| Customer List | NEW Apr 5: Smart filter sidebar (Total, With Service, No QB, No Email, No Phone, Inactive, New 30 days), A-Z filter, search, pagination, live D1 counts |
| SMS Inbox | NEW Apr 5: 3-pane layout (nav, list, thread) — needs Brevo integration for real messages |
| Notifications | NEW Apr 5: Bell icon in header with panel (failed payments, new payments, reschedules, new customers) |
| Invoice Edit | NEW Apr 5: Multi-line items, discount, tax, terms. Single click = side panel, double click = full detail. |
| New Job Form | NEW Apr 5: Slide-out creation form |
| Reports | NEW Apr 5: Financial, Operational, Forecasting + scheduled reports. Added to sidebar nav. |
| Daily Ops Link | NEW Apr 5: "Daily Operations" button launches OCC_Daily.html |
| Automations | All 13 workers listed with ping status, KV namespaces |
| Vendors | 12 vendors with login links, billing links, costs |
| Customer Reminders | 6 reminder slots in Comms tab. 3 preset: (1) 7 days before = email, (2) Day before = email + SMS, (3) Day of = SMS with approved EMPTY template. 3 custom: email or SMS, free-form with {first} {last} {address} {date} merge tags. All via Brevo. Test to Howard / Send to Customer buttons. Full send log with history. |
| Reconciliation | Rebuilt — pulls full Square transaction data via transactions action. Date range picker (This Month, Last Month, 30/60/90 days). Summary KPIs (gross/fees/net/refunded). CSV export for QuickBooks. |
| Inbox Sorter | Nav button opens standalone OCC_Inbox_Sorter.html (requires Microsoft OAuth). Syntax bug fixed (line 218 template literal). |
| Worker API Actions | |
| Customer actions | customers_search, customers_stats, customers_by_zip, customers_churn, customers_recent, customers_stale |
| Sync actions | sync_gd (paginated), sync_status |
| Stale actions | stale_wc_check (WC card+order check), stale_mark_inactive (D1 only) |
| Square passthrough | square_revenue, square_search, square_refund — all route through occ-square-proxy |
| Deploy | |
| Frontend | cp Active/OCC_Command_Center.html index.html && npx netlify-cli deploy --prod |
| Backend | cd "Cloudflare Worker/occ-command-center" && deploy.bat |
| Files | Active/OCC_Command_Center.html (frontend) Cloudflare Worker/occ-command-center/worker.js (backend) |
| What it does | Details |
|---|---|
| Purpose | Separate app for running OCC daily — the operational counterpart to Command Center (which is the management/numbers side). Built April 5, 2026. |
| Calendar | REAL route data from schedule engine D1. Day/Week/Month views. Route filter by Route 1-4. Click any stop → customer profile with real data (route day, route number, plan, price, cans, county, tax, next service, prepaid status). |
| Customer List | Smart filters + A-Z + live D1 search. Full customer profile overlay with 6 tabs + right sidebar. |
| SMS Inbox | 3-pane (nav, list, thread) — needs Brevo integration for real messages |
| Leads | Status badges, pipeline view |
| Reviews | Google rating, recent reviews |
| Reports | 3 categories (Financial, Operational, Forecasting) |
| Dashboard | KPI cards + invoices aging |
| Settings | 14 categories pulling real data (Plans from D1, Taxes, Users, etc.) |
| Global Search | Header search bar with instant D1 results dropdown |
| Navigation | Icon sidebar: Calendar, Customers, Dashboard, Inbox, Leads, Reports, Reviews, Settings, Command Center link |
| File | Active/OCC_Daily.html |
| What it does | Details |
|---|---|
| Purpose | Marketing site for selling back-office automation to other bin cleaning companies. Front door for PlusAutomation.ai / HACJAC Capital LLC. |
| Pricing | Starter: $500 setup + $49/mo (owner-operator 1-3 trucks). Pro: $1,000 setup + $99/mo (5+ trucks, $1M+). LOCKED. |
| How It Works | 40+ automations across 8 categories with visual showcase |
| Closed-Box Strategy | CSV import + webhook interceptor for CRMs with no API (like Jobatory). Works around CRM limitations. |
| Platform Comparison | iRoutes, Jobber, GorillaDESK, ServiceWorks — open vs closed API comparison |
| Positioning | "Plugin, not a platform" — works WITH existing CRM. Day-of AND day-after models supported. |
| Logo | Running wheelie bin with cog wheels (needs Fiverr vectorization) |
| Lead Capture | Form submission — currently mailto fallback, needs Netlify Forms or Worker endpoint |
| What it does | Details |
|---|---|
| Purpose | Full working demo with 200 fictional Cincinnati customers showing everything the product can do. 9 pages all pulling live API data. |
| Pages | |
| Dashboard | Metrics, automation proof, action items |
| Customers | 200 fictional clickable profiles with full 5-tab customer detail (Overview, Service History, Billing, Activity, Notes) |
| Routes | Progress cards, route table, SVG map with numbered pins, drag-and-drop reorder, auto-optimize (nearest neighbor algorithm), total distance calculation (haversine), start/end markers |
| Billing | Failed payments, expiring cards, prepaid renewals |
| Marketing Hub | Email health metrics, campaign table with open/click/revenue, customer segments, 12 "What BCA Powers" capability cards |
| Campaigns | 7 campaigns with analytics |
| Reviews | Ratings, recent reviews |
| Referrals | Tracking pipeline |
| Automations | 30-day log of all automations fired |
| Settings (GD-Style Two-Panel) | |
| General | Account, Plans (28 plans), Users, Taxes, Services & Pricing (unified — replaces Line Items + Service Templates) |
| Templates | System Templates, Custom Templates, Broadcast Templates, Email Inbox |
| Automation | Automation Rules (12 automations with expandable timing/messaging/targeting) |
| Configuration | Cleaning Model (day-of/day-after), Billing Model, Connections (email provider picker: Brevo/SMTP/SendGrid/Gmail/Outlook) |
| Customer Profile (Full Rebuild) | |
| Header | Dark (#0f172a) with name clearly visible |
| Overview | Top Note (editable), Account Details (editable), Location (add/edit/map link), Cards on File (add/primary/update), Quick Stats, Prepaid Status, Recent Activity, Quick Actions (email, SMS, new job, create invoice) |
| Invoice Slide-out | Click any invoice → line items, qty, price, tax (7.8%), subtotal, total, amount paid, card used, terms, notes. Send dropdown (Email/SMS/Both). Add Payment + Edit on unpaid. |
| Billing Tab | 4 metric cards (Balance, Last Payment, YTD, Credits), full invoice table, Cards on File with add/primary |
| Activity | Filter dropdown (All/Emails/SMS/Payments/Jobs), compose buttons |
| Notes | Add note with pin-to-top, crew notes with avatars, office notes |
| What it does | Details |
|---|---|
| Purpose | API backend for BCA product demo. Serves all dashboard data. |
| D1 Database | 10 tables, seeded with 200 fictional Cincinnati-area customers with real street addresses and lat/lng coordinates |
| Webhook Interceptor | POST /api/webhook/signup — captures form submissions from client websites (closed-box CRM strategy) |
| Embeddable Script | GET /api/embed.js?cid=YOUR_ID — JS snippet clients add to their website to capture signups |
| All API endpoints | Customers, routes, billing, marketing, campaigns, reviews, referrals, automations, settings — all working |
| What it does | Details |
|---|---|
| Purpose | OAuth-based QuickBooks Online sync for PlusAutomation/BCA clients. Customer sync, invoice creation, payment recording, P&L reports, AR aging. |
| D1 Database | ID: 9c436054 — tables: connections, sync_log, entity_map |
| Intuit App | "PLUSAUTOMATION.AI" in Intuit Developer portal, sandbox company exists |
| Status | NOT YET WORKING — OAuth returns "undefined didn't connect" error. Likely Intuit propagation delay on new app. Retry needed. |
| Features | Customer sync, invoice create, payment record, P&L reports, AR aging — all coded, waiting on OAuth |
| What it does | Details |
|---|---|
| Purpose | 3-email drip sequence to convert one-time cleaning customers to recurring plans |
| Schedule | Runs daily at 9am UTC (cron: 0 9 * * *) — all 3 emails checked in one pass |
| Email 1 — Day 3: "Your cans are clean — let's keep them that way" | |
| Trigger | OT jobs completed 3–4 days ago |
| Content | Fresh-clean pitch, 2×2 plan cards, "Pick My Plan →" CTA to signup page |
| Dedup | KV: ot_followup_{jobId} |
| Subject | Your cans are clean — let's keep them that way |
| Test | send_test_ot.py |
| Email 2 — Day 21: "How do your cans smell?" | |
| Trigger | OT jobs completed 21–22 days ago |
| Content | Short/witty copy ("Be honest — how do your cans smell right now?"), plan cards as preview only with "Here's what we offer:" label, big green "Click Here to Pick Your Plan →" CTA |
| Signup page | Links with &touch=2 — headline changes to "How do your cans smell, [Name]?" |
| Dedup | KV: ot_followup2_{jobId} |
| Subject | How do your cans smell? |
| Test | send_test_ot2.py |
| Email 3 — Day 86: "It's been about 3 months" | |
| Trigger | OT jobs completed 86–87 days ago |
| Content | Short copy, plan card preview, big green recurring CTA, bold "OR" divider, then OT box with cleaning + sanitizing mascots flanking ~~$70~~ $60 (SAVE $10!) and "Book One-Time Cleaning →" button |
| OT booking | Links to /book-onetime on signup page — skips landing page, shows instant confirmation, processes WC order in background with $10 discount |
| Dedup | KV: ot_followup3_{jobId} |
| Subject | It's been a while — how are those cans? |
| Test | send_test_ot3.py |
| Smart Behavior | |
| Conversion kill | All emails skip if converted_from_ot_{email} exists in KV |
| Repeat OT flag | When someone books via the 86-day email, KV key ot_repeat_{email} is set. Emails 1 & 2 are skipped on next OT job — only email 3 fires again (~quarterly check-in) |
| BCC | howard@ohiocleancans.com on every send |
| Sender | Howard Cooper - Ohio Clean Cans <howard@ohiocleancans.com> via Brevo |
| File | Cloudflare Worker/occ-ot-followup/worker.js |
| What it does | Details |
|---|---|
| Purpose | Sends a branded onboarding email to every new OCC customer within ~1 hour of their GD job being created. Two separate email types: OT welcome and recurring welcome. |
| Schedule | Hourly cron — scans GD for jobs created in last 2 hours |
| Trigger | GD API: new jobs with service_type matching OT, M*, BM*, Q*, or *PREPAID* |
| Two-email architecture | WooCommerce sends "order received" immediately at checkout. Our worker sends the "scheduling details" welcome email after the GD job exists (~5 min delay). This is intentional — GD job doesn't exist at checkout time. |
| Email Type 1 — OT Welcome (One-Time Customers) | |
| Subject | Thank you for your order, [First]! Your cleaning is scheduled |
| Content | Header "Thank You for Your Order!" (or "Welcome Back!" for repeats). Order info box: name, address, scheduled date with year, service name (e.g. "One-Time Cleaning 1 Can"), invoice total. What to Expect: no set arrival time 9am–8pm route, cleaned+sanitized hang tag, reminders (7 days / day before / morning of), PLEASE LEAVE CANS OUT, prohibited items list. Private drive note. Howard Cooper signature. |
| Dynamic data | Plan name + order total pulled from GD location note fields "Plan:" and "Total:" via parseOrderDetails(note). Scheduled date comes from first job date with full year (e.g. "Friday, April 4, 2026"). |
| Dedup | KV key welcome_ot_{customerId} — prevents duplicate OT welcome sends |
| Email Type 2 — Recurring Welcome (Monthly / BM / Quarterly / Prepaid) | |
| Subject | Welcome to Ohio Clean Cans, [First]! Here's your scheduling info |
| Content | Header "Welcome to the Family!" Account info box: name, address, route frequency (Every 4/8/12 weeks), first cleaning date with year, plan name + annotation (e.g. "M2 Monthly 2 Cans — M = Monthly"), invoice total/cost. What to Expect: annual cleanings + reschedule limit, no set time 9am–8pm, cleaned+sanitized tag, reminders, PLEASE LEAVE CANS OUT, private drive policy, $20 fuel recovery fee, prohibited items, portal link to update payment. Howard Cooper signature. |
| Plan annotation | planAnnotation(serviceType): M* → "M = Monthly", BM* → "BM = Bi-Monthly", Q* → "Quarterly", PREPAID → "Annual Prepaid" |
| Dedup | KV key welcome_{customerId} — one recurring welcome per customer, ever |
| Shared Details | |
| BCC | howard@ohiocleancans.com on every send |
| No set time line | "There is no set arrival time. You are placed on a route schedule — our crew runs from approximately 9:00am to 8:00pm. Wherever you fall on the route is when we'll arrive." |
| Private drive line | "Your garbage truck comes to you, so we do too. We clean your cans wherever they are emptied on your property — no need to bring them to the curb." |
| Manual trigger | GET /?trigger=1&token=SECRET_TOKEN |
| Manual OT block | To prevent auto-send for a specific customer: npx wrangler kv key put --remote --namespace-id=KV_ID "welcome_ot_{customerId}" "manual" |
| File | Cloudflare Worker/occ-welcome-email/worker.js |
| What it does | Details |
|---|---|
| Purpose | Two-stage internal alert system: monthly overview + 8-day reminder. Also sends customer pickup delay notifications on approval. |
| Email 1 — 1st of Month (~30 days heads up) | |
| Schedule | Cron: 0 12 1 * * (7am ET on 1st of every month) |
| Content | Full next-month holiday overview to info@ — "Action May Be Needed" (amber) + "No Service Delays" (gray) sections |
| Dedup | KV key holiday_internal_YYYY-MM — one per month |
| Manual trigger | ?monthly=1&token=SECRET[&month=YYYY-MM][&force=1] |
| Email 2 — 22nd of Month (8-day reminder to Jason) | |
| Schedule | Cron: 0 12 22 * * (7am ET on 22nd of every month) |
| Content | Fires ONLY if a route-affecting holiday is in the next 8 days. Amber banner, holiday table with days-out (turns red at ≤3 days), "action needed: confirm routes in GD" footer note. |
| Logic | Quiet month with no upcoming holidays = no email sent. Checks 8-day window from the 22nd (catches holidays on 23rd–30th). |
| Dedup | KV key holiday_midmonth_YYYY-MM — one per month |
| Manual trigger | ?reminder=1&token=SECRET[&force=1] |
| Customer Notification — 7-day approval flow | |
| Schedule | Daily cron 0 12 * * * — checks for holidays exactly 7 days out |
| Flow | Howard gets approval email with full customer list + email preview → clicks Approve → customers get pickup delay email · Cancel = no send |
| Recipients | info@ohiocleancans.com (Howard + Jason) for all internal emails · customer list from KV (synced from WC orders) |
| Haulers covered | Rumpke Cincinnati · Republic Services |
| Rumpke delays | Christmas Day and New Year's Day ONLY |
| Republic delays | New Year's Day, Memorial Day, July 4, Labor Day, Thanksgiving, Christmas |
| File | Cloudflare Worker/occ-holiday-notify/worker.js |
| What it does | Details |
|---|---|
| Purpose | Real-time email sorting for the OCC Outlook inbox via Microsoft Graph API webhooks. Also processes backlog batches. |
| Real-time | Graph webhook subscription on inbox → categorizes each new email on arrival → auto-moves to correct folder |
| Categories | keep (OCC business, revenue ops — stays in inbox) · Customer Responses · OCC Expenses · Personal · Purchases · delete (spam/marketing → Deleted Items) |
| OCC domains (keep) | ohiocleancans.com, gorilladesk.com, stripe.com, squareup.com, intuit.com, clicklaboratory.com |
| Revenue ops (keep) | Square receipts, Stripe notifications, GorillaDESK notifications |
| Spam auto-delete | SEO pitches, marketing domains (substack, alignable, linkedin newsletters, wayfair, etc.) |
| Endpoints |
POST /notify — Graph webhook receiver (real-time) POST /setup — create/renew Graph subscription GET /status — check subscription health POST /count — dry-run count of inbox (auth required) POST /backlog — process 50 emails per call (auth required, paginated) |
| Auth | Microsoft Graph (client credentials flow). Backlog/count endpoints require secret: "occ-reorg-2026" |
| Subscription renewal | Cron-triggered — auto-renews Graph subscription every 3 days |
| KV namespace | OCC_INBOX_KV — stores subscription ID, expiry, backlog cursor |
| File | Cloudflare Worker/occ-inbox-sorter/worker.js |
| What it does | Details |
|---|---|
| Purpose | Personalized plan selection page linked from OT follow-up emails. Customer sees their name, can count, and 4 plan cards. |
| URL params | ?email=&cans=&first=&last=&touch= — pre-loads customer info. touch=2 changes headline to "How do your cans smell?" |
| Plan card order | Monthly (Best Value/blue) top-left · Annual Prepaid (Best Deal/green) top-right · Quarterly (Good/red) bottom-left · Bi-Monthly (Better/black) bottom-right |
| Buttons | "Sign Up" on all 4 cards. Clicking opens a confirmation modal (plan name, price, details). Shows "Processing your order — please wait…" on submit. |
| "How did you hear?" | Required dropdown in confirmation overlay (before phone field). 8 options: Neighbor/Word of Mouth, Saw Our Truck, Door Hanger/Flyer, Google Search, Facebook/Social Media, Nextdoor, Referral Code, Other. Can't submit without selecting. Stored as heard_about_us in WC order meta. Shows in Howard's notification email. |
| Speed | Confirmation page shown instantly. WC order creation + emails run in background via ctx.waitUntil(). No more 20-second wait. |
| On confirm | POSTs to /signup → shows confirmation instantly → background: creates WC order, sends customer email + Howard notification, marks KV as converted |
| /book-onetime | Direct OT booking from 86-day email. GET /book-onetime?email=&cans=&first=&last= — no landing page, instant "You're booked!" confirmation, $10 discount applied, sets ot_repeat_{email} KV flag |
| OT product | WC product ID 232, variations: 1 can=498, 2=499, 3=500, 4=501, 5=502, 6=503 |
| WooCommerce | Creates order with correct product/variation ID for plan + can count. Uses POST (not PUT) to bypass GoDaddy firewall. |
| KV namespace | 57151d059c9a4866ba45f5d4bbb8ee89 |
| Files (3 — keep in sync) |
Cloudflare Worker/occ-signup-page/worker.js Cloudflare Worker/occ-signup-page/preview.html Active/signup-preview.html (localhost:7799) |
| What it does | Details |
|---|---|
| Purpose | Self-service customer portal. Loads account info from GD and lets customers submit service requests. Every action writes a timestamped note to the customer's GD account. |
| Entry URL | /?id=GD_CUSTOMER_ID — personalized link with GD customer ID |
| Account info shown | Name, service address, plan, email, phone, next cleaning date (pulled from GorillaDESK) |
| Action: Reschedule My Cleaning | Pushes next cleaning to the following 4-week cycle. Shows credit usage (X of Y this year). Blocks submission if limit reached and alerts info@. Seasonal customers route to info@ as manual flagged request (date shift, not cancellation). |
| Action: Update Contact | New phone/email form → email request to info@ |
| Action: Update Address | New address form → email request to info@ |
| Action: Add a Can | Current + additional can count → email request to info@ |
| Action: Update Payment | No card data collected — sends request to info@ to reach out manually |
| GD note on every action | Timestamped note written to GD customer account: [Apr 1, 2026 9:30 AM] Customer portal: Reschedule — Push 4 Weeks |
| Reschedule limits | Monthly/Prepaid: 3/yr · Bi-monthly/Quarterly: 2/yr · Seasonal: manual routing only (no portal limit) |
| Limit exceeded | Submit button disabled client-side. If POST submitted anyway: alert email to info@ with customer details + "limit reached" page shown to customer. |
| Seasonal reschedules | Flagged email to info@ — "date shift not cancellation" language on both form and email. Jason handles scheduling manually. |
| Request email goes to | info@ohiocleancans.com. Reply-to set to customer email. |
| Test account | Howard Cooper · GD ID: DL1ElPkYLz · howard@ohiocleancans.com · 7486 Stillwater Drive, Maineville 45039 |
| File | Cloudflare Worker/occ-portal/worker.js |
| What it does | Details |
|---|---|
| Purpose | Morning SMS batch sender. On service days, texts each customer a confirmation link ("Are your cans out?") before the crew arrives. |
| Schedule | Cron: 0 12 * * 1-5 (7 AM ET Mon–Fri) |
| Customer lookup | Embeds full LU_SCHEDULE (4-week rotation by ZIP). Queries D1 for active customers in today's ZIPs with a phone number. ANCHOR_MONDAY = Feb 23, 2026 (Week 4). |
| SMS provider | Brevo first (env: BREVO_SMS_KEY). Twilio fallback (env: TWILIO_SID / TWILIO_TOKEN / TWILIO_FROM). Dry-run log-only if neither configured. |
| Token security | HMAC-SHA256 signed daily tokens. Shared secret TOKEN_SECRET must match occ-empty-confirm. Tokens expire after 2 days. |
| Dedup | KV key sms_sent:YYYYMMDD:customerId — 48h TTL. Won't re-send same customer same day. |
| Auth token | occ-sms-2026 |
| Manual actions |
?action=preview&token= — dry run, shows who would be texted ?action=send&token= — live send ?action=status&token= — per-customer SMS sent + confirmation status ?action=test_link&token= — generate a test confirm link |
| KV namespace | Shared: 57151d059c9a4866ba45f5d4bbb8ee89 |
| D1 database | e1ab48e2-7f56-425a-95e2-ae1a58adc93d (occ-customers) |
| Secrets needed | TOKEN_SECRET (shared with occ-empty-confirm) · BREVO_SMS_KEY or Twilio creds (when phone number approved) |
| Status | Built and deployed. Not yet live — waiting on Brevo/Twilio phone number approval. |
| File | Cloudflare Worker/occ-sms-dispatch/worker.js |
| What it does | Details |
|---|---|
| Purpose | Customer-facing tap-to-confirm page linked from the day-of SMS. Customer taps "Yes — My Cans Are Out!" or "My cans won't be out today." |
| Token system | HMAC-SHA256 daily tokens (TOKEN_SECRET shared with occ-sms-dispatch). Accepts today or yesterday (late crew). Expired/invalid tokens show error page. |
| GET /?t=TOKEN | Shows confirmation page with customer first name + service address (fetched from GD with ?include=locations) |
| POST /confirm | Stores choice in KV: empty:YYYYMMDD:customerId = {status, confirmed_at, customer_id}. First response wins (no overwrite). 48h TTL. |
| Choices | empty — cans are out (green ✅ confirmation) · skip — won't be out today (yellow 👍, "no no-show fee") |
| Already confirmed | Shows status page instead of buttons. Prevents double-tapping. |
| KV namespace | Shared: 57151d059c9a4866ba45f5d4bbb8ee89 |
| Secrets needed | TOKEN_SECRET (must match occ-sms-dispatch) |
| File | Cloudflare Worker/occ-empty-confirm/worker.js |
| What it does | Details |
|---|---|
| Purpose | Manages referral agents — register agents, generate unique codes, auto-create WooCommerce coupons, track referred orders, report revenue per agent |
| Database | Cloudflare D1 — tables: referral_agents, referral_orders |
| Coupon creation | Two coupons per agent (v4.2): {CODE} = $10 fixed_cart (OT), {CODE}S = 50% percent (subscription). Both share description "Referral coupon for [Name]" — WPCode snippet uses this to detect and auto-swap them. |
| Referral links | ohiocleancans.com/ref/CODE — 2 uppercase letters + 3 digits (e.g., AB123). Sub coupon always appends S (ab123s). |
| API endpoints |
GET /api/agents — list all agents GET /api/agent/{CODE} — agent detail + orders + revenue POST /api/register-agent — name, email → creates agent + WC coupon POST /api/delete-agent — removes agent + WC coupon + orders POST /api/generate-code — get a unique code without registering POST /api/track-order — log a referred order POST /api/mark-contacted — mark agent as contacted |
| Auth | GET endpoints are public. POST/DELETE require Authorization: Bearer {API_SECRET} |
| WC integration | Uses WC_CONSUMER_KEY + WC_CONSUMER_SECRET env vars for coupon create/delete |
| Dashboard | Root URL serves basic API status page. Desktop tracker tool used for full management. |
| Local file | Cloudflare Worker/occ-referral-agent-v4.2.js |
| What it does | Details |
|---|---|
| Purpose | Generates personalized renewal notice emails for prepaid customers as their cleanings run out |
| Data source | GorillaDESK prepaid CSV (public URL in memory reference) |
| Renewal date calc | Last cleaning date + 4 weeks |
| Plans M1–M7 | See pricing table for updated prepaid rates |
| File | Active/OCC_Prepaid_Renewal.html |
| Deploy | npx netlify-cli deploy --prod --dir "Active" --site 947d3828-e499-4a55-a1a6-a53d20e887fa |
| What it does | Details |
|---|---|
| Purpose | Every Monday, scans all 56 prepaid customers via GorillaDESK API. When a customer hits 1 job remaining, emails Howard a summary for approval. One click sends all renewal notices. |
| Schedule | Cron: 0 14 * * 1 (every Monday at 9am ET / 14:00 UTC) |
| Data source | 56 customers hardcoded in worker.js (updated March 2026 pricing). Live job count pulled from GD API per customer on each scan. |
| Trigger threshold | ≤ 1 scheduled job remaining (live from GD API) |
| Dedup | KV key renewal_sent_{gd_acct} — 90-day TTL. Won't re-queue a customer until 90 days after their last notice. |
| Approval flow | Howard gets an email with a table of all due customers → clicks green "Send All" button → worker sends customer emails → confirmation page shows who was sent |
| Customer email | Personalized renewal notice with plan details, last clean date, renewal date (last + 4 weeks), 13th cleaning free bonus. BCC to howard@. |
| No GD ID customers | Flagged in approval email but not auto-sent (no live data available). Handle manually via the Prepaid Renewal Tool. |
| Manual scan trigger | GET /?scan=1&token=occ-renewal-2026 |
| Approval endpoint | GET /approve?token=occ-renewal-2026&batch={id} — link is in the approval email. Expires 7 days. |
| KV namespace | OCC_RENEWAL_KV — ID: dd49390aad764dd68b6e1ece9a2f375f |
| Files | Cloudflare Worker/occ-prepaid-renewal/worker.js Cloudflare Worker/occ-prepaid-renewal/deploy.bat — double-click to deploy |
| What it does | Details |
|---|---|
| Purpose | Central proxy for all Square API operations. Backend for the Refund Tool, Reconciliation Tool, and revenue dashboards. |
| Actions (via ?action=) |
search — search payments by name or amount (last 90 days) transactions — full transaction list with fees, net, card details refunds — list all refunds with original payment context revenue_summary — monthly breakdown: gross, fees, net, refunds, declines refund (POST) — issue a refund by payment_id |
| Auth | All requests require ?secret=occ-square-2026 |
| Location | Square Location ID: L94SZM9XD87WH |
| Pagination | Automatically paginates through all Square results (up to 10–20 pages) |
| CORS | Open (*) — designed to be called from local HTML tools |
| What it does | Details |
|---|---|
| Purpose | Look up a Square payment by customer name or amount and issue a refund |
| Backend | Calls occ-square-proxy worker (search + refund actions) |
| Status | Live |
| What it does | Details |
|---|---|
| Purpose | Reconciles Square transactions against expected charges for a date range |
| Backend | Calls occ-square-proxy worker (transactions action) |
| Status | Live |
| What it does | Details |
|---|---|
| Purpose | Automated daily email report of yesterday's Square transactions — gross, fees, net, refunds, per-customer breakdown |
| Schedule | Runs daily on cron (sends yesterday's activity each morning) |
| Email to | info@ohiocleancans.com (Howard + Jason) |
| Content | Summary cards (transactions count, gross, fees, net deposited), full transaction table with customer names/times, separate refund table if any, refund alert banner |
| No activity | Sends a simple "No Square activity" message instead of empty tables |
| Manual trigger | ?token=occ-square-daily-2026 |
| APIs | Square Orders Search, Payments, Refunds APIs → Brevo for email delivery |
| File | Cloudflare Worker/occ-square-daily/worker.js |
| What it does | Details |
|---|---|
| Purpose | Automatically creates a GorillaDESK customer record when a new WooCommerce order comes in. Eliminates manual re-entry. |
| Schedule | Cron-triggered (checks WC orders from last 48 hours) |
| Flow | Fetches WC orders (processing/completed) → for each new order, creates GD customer with name, address, phone, location note |
| Location note includes | Neighborhood, trash company, recycle day, garbage day, pickup time, county, order details, coupon used, customer note |
| WC meta fields read | Trash day, trash time, recycle day, next recycle date, waste company, county, neighborhood, SKU |
| Dedup | KV key wc_order_{orderId} — one create per order. Expires 90 days. |
| Duplicate handling | If GD returns 422 (customer already exists), marks as skipped, no error |
| Manual trigger | GET /?trigger=1&token=SECRET_TOKEN |
| Reprocess | GET /?trigger=1&token=SECRET_TOKEN&reprocess=ORDER_ID — clears KV and re-runs for that order |
| File | Cloudflare Worker/occ-wc-to-gd/worker.js |
| What it does | Details |
|---|---|
| Purpose | CORS proxy that bridges the OCC Management Tool HTML to Google Apps Script (Google Sheets data) and GorillaDESK API |
| Google Apps Script | Forwards all non-GD requests to the published GAS URL with original query params |
| GD endpoints |
?action=gd_customers_new — customers created this month ?action=gd_count — customer count by status (active/inactive), paginated |
| GD auth | Requires &token=oyDVMFy-CB1VGX_huR9avQ |
| CORS | Open (*) — designed for browser HTML tools |
| What it does | Details |
|---|---|
| Purpose | Diagnostic tool that tests various GD API endpoints and returns what works vs. what doesn't. Used during development to map API capabilities. |
| Tests run | GET customer, PATCH customer, location paths (4 variations), GET jobs by customer_id, PATCH job |
| Status | Development utility — not used in production flows |
| Cans | Monthly | Bi-Monthly | Quarterly | One-Time | Annual Prepaid (12 + 1 free) |
|---|---|---|---|---|---|
| 1 | $26 | $30 | $34 | $55 | $312 |
| 2 | $32 | $36 | $40 | $70 | $384 |
| 3 | $42 | $48 | $53 | $95 | $504 |
| 4 | $52 | $60 | $66 | $120 | $624 |
| 5 | $62 | $72 | $79 | $145 | $744 |
| 6 | $72 | $84 | $92 | $170 | $864 |
| 7 | $86 | $96 | $105 | $195 | $1,032 |
| 8 | $98 | $108 | $118 | $220 | $1,176 |
| 9 | $102 | $120 | $131 | $245 | $1,224 |
| 10 | $112 | $132 | $144 | $270 | $1,344 |
| Service | Notes | Where to find keys |
|---|---|---|
| Brevo Email | All transactional email. Sender always howard@ohiocleancans.com. NEVER use Gmail. | Memory: OCC Brevo Email API |
| GorillaDESK | Bearer token in workers. Read-mostly — no location management via API (v1 limitation). | Hardcoded in worker files |
| WooCommerce | REST API. Use POST not PUT (GoDaddy WAF blocks PUT). Product + variation IDs in memory. | Memory: OCC WooCommerce API |
| Square | Production API. Proxy worker handles search, transactions, refunds, revenue summary. | Memory: OCC Square Tools |
| Microsoft Graph | Client credentials flow for Outlook inbox sorting. Tenant ID + Client ID + Secret in worker env. | occ-inbox-sorter worker env vars |
| Google Apps Script | Published GAS URL for management tool data (Google Sheets). Proxied through occ-gas-proxy. | Hardcoded in occ-gas-proxy worker |
| Cloudflare D1 | Two databases: (1) Command Center — e1ab48e2, ~4,900 customers, nightly GD sync, churn tracking. (2) Referral tracker — referral_agents + referral_orders tables. | occ-command-center + occ-referral-agent worker bindings |
| Cloudflare Workers KV | Multiple namespaces across workers for dedup, state, and caching. | wrangler.toml in each worker |
| Twilio SMS | Console exists but no API keys yet — need SID + Auth Token from Howard. | Not yet configured |
| Netlify | Site ID: 947d3828-e499-4a55-a1a6-a53d20e887fa. Hosts Active tools at occ-tools.netlify.app. | See deploy command above |
| Endpoint | Result | Notes |
|---|---|---|
| GET /customers/{id} | ✅ Works | Returns customer record. Data wrapped in .data key. Address fields are empty without ?include=locations. |
| GET /customers/{id}?include=locations | ✅ Works | Required to get address data. Without this param, all address fields return empty. Address is in c.locations[0].address_line_1, city in c.locations[0].city, zip in c.locations[0].zip. |
| POST /customers/{id}/notes | ✅ Works | Creates a note on the customer's account. Send as FormData with content field. Used by portal to log every customer action with timestamp. |
| GET /customers/{id}/notes | ✅ Works | Returns array of notes. Used by portal to count reschedule credits used this year. |
| POST /customers | ✅ Works | Creates customer with location. Used by WC→GD sync. |
| PATCH /customers/{id} | ❌ 404 | Cannot update customer fields via API |
| GET /customers/{id}/locations | ❌ 404 | Location management not exposed in v1 |
| POST /customers/{id}/locations | ❌ 404 | Cannot create locations via API |
| GET /jobs?customer_id= | ✅ Works | Returns jobs filtered by customer |
| GET /jobs?status=completed | ✅ Works | Used by OT follow-up worker |
| GET /jobs?date=YYYY-MM-DD | ❌ 404 | "Page not found" — GD does not support date-based job filtering |
| GET /jobs?created_after=&status=scheduled | ✅ Works | Used by welcome email worker |
| GET /customers?limit=&created[gte]= | ✅ Works | New customers this month — used by management tool proxy |
| GET /customers?status=active | ✅ Works | Customer count by status — used by management tool proxy |
THE GorillaDESK replacement. Handles scheduling, job generation, reschedules, route board, billing, and receipts. Built April 1, 2026.
Location: +OHIO CLEAN CANS/GD Replacement/ (separate from Tools)
| Component | Details |
|---|---|
| D1 Tables (16) | schedules, jobs, reschedule_log, route_assignments, hold_log, job_generation_log, line_items, service_templates, payment_types, customer_payment_methods, payment_log, payment_reminders, customer_notes, contact_preferences, scheduled_reports, referral_rewards |
| Line Items | 132 loaded — M1-M10, M1P-M10P, BM1-BM10, Q1-Q15, OT1-OT20, SEA6/SEA8, BW, SUM3/SUM4, PK3, addons, fees, commercial, legacy |
| Service Templates | 124 loaded — color-coded by category, invoice descriptions on every one |
| Payment Types | 7: Square, Stripe, check, credit, gift cert, cash, prepaid |
| Schedules | 1,739 active customers imported from GD service lookup |
| Route Assignments | 158 ZIP→route mappings across Mon-Fri, 4 routes each |
| Cron | 0 6 * * * (1 AM ET) — auto-generates jobs + reminders |
API Actions (40+):
| Category | Actions |
|---|---|
| Schedule CRUD | schedules_list, schedule_get, schedule_create, schedule_update, schedule_pause, schedule_resume, schedule_cancel |
| Jobs | jobs_by_date, jobs_by_route, jobs_for_customer, job_complete, job_skip, job_reorder |
| Reschedule | reschedule, reschedule_auto_slot, reschedule_history |
| Generation | generate_jobs, generation_status |
| Route Board | route_board (Jason's 4-column daily view) |
| Config | line_items_list, service_templates_list, payment_types_list, route_assignments, + CRUD for each |
| Import | import_line_items, import_service_templates, import_schedules |
| Stats | schedule_stats |
Pricing Rules (confirmed Apr 1, 2026):
| Plan | Base (1 can) | 2 cans | Per-can after |
|---|---|---|---|
| Monthly | $26 | $32 | +$10 |
| Prepaid | $26/clean ($312/yr) | $32 ($384/yr) | +$10 (×12 annual) |
| Bi-Monthly | $30 | $36 | +$12 |
| Quarterly | $34 | $40 | +$13 |
| Bi-Weekly | $22 | $26 | +$10 |
| One-Time | $55 | $70 | +$25 |
| Seasonal 6 | $34 | $42 | +$10 |
| Seasonal 8 | $30 | $36 | +$10 |
| Summer3 | $50 | $55 | +$10 |
| Summer4 | $45 | $50 | +$10 |
| Pick3 | $40 | $45 | +$15 |
Mobile-first app for the crew (Jason, Coby, Brady). PIN-protected, see today's route, tap to complete jobs.
| Feature | Details |
|---|---|
| PIN Auth | 4-digit PIN keypad. 5 attempts then 15-min lockout. PINs stored in KV (custom) or Cloudflare Secrets (temp). |
| First-Time PIN Setup | Temp PIN (Jason=2026, Coby=2027, Brady=2028, Howard=1234) → first login triggers "Create Your PIN" flow → enter + confirm → saved to KV. Bans simple PINs (0000, 1234, etc). After setup, temp PIN no longer works. |
| PIN Change | /change-pin endpoint — requires current PIN, validates new PIN, saves to KV. |
| Crew Select | Jason, Coby, Brady, Howard — saves to localStorage with session token |
| Route Picker | 4 route buttons with stop counts, switch between routes |
| Progress Bar | Visual completion % for current route |
| Job Cards | Customer name, address, plan code, can count — color-coded by plan type |
| Job States | White=scheduled, GREEN=completed/auth'd, YELLOW=circle-back, RED=skipped, BLUE=captured/charged |
| Done Button | Confirm dialog → marks complete → triggers billing auth → receipt on capture |
| Skip Button | Reason required (cans not out, gate locked, weather, customer request, other) |
| Nav Button | Opens Apple Maps (iOS) or Google Maps (Android) with address |
| Crew Buttons | Garbage Full, Recycle Full, Bags in Can, Upsell Recycle $10 — auto-sends SMS to customer |
| Can Adjust | Fewer Cans / More Cans — big number picker, recalculates price, cancels auth if needed. Prepaid can't reduce. |
| Data Tags | Private Drive, Gate Code, Dog, Side Yard, Garage, Add Note — saved as pinned customer notes in D1 |
| Notify Remaining | Route-level button — sends position update SMS to all remaining customers |
Crew hands tablet to customer at the curb. One-page form, sign up, we clean right now.
| Feature | Details |
|---|---|
| Form Fields | First/Last name, address, city, state (OH locked), ZIP, email, phone — all on one screen, big touch targets |
| Can Picker | 1-6 cans, tap to select, prices update live |
| Service Toggle | One-Time ($55-170) or Monthly ($26-72) — big buttons with prices |
| Price Display | Big blue gradient banner showing total + plan description |
| On Submit | Creates schedule in D1 via Schedule Engine API, sends welcome email to customer, sends notification email to Howard |
| Howard Notification | Full customer details + purple "TABLET / ON-ROUTE" source badge |
| Success Screen | Green check, personalized message, "New Signup" button to reset |
| Phone Format | Auto-formats as (513) 555-1234 on input |
| Crew Context | Pass ?crew=Jason&route=2 in URL — auto-fills crew member and route |
| Source Tracking | All signups tagged as "Crew / On-Route" in heard_about field |
Day-of SMS links here. Customer sees logo, their name, address, and a big green EMPTY button. No skip button (intentional friction). "I need to reach us" → call/text/email options. Tapping EMPTY flags job as CONFIRMED EMPTY in D1.
Review request emails/SMS link here. Google, Facebook, Nextdoor buttons. Auto-sent 2 days after cleaning with 30-day cooldown per customer. Uses real Google review link.
Built into the Schedule Engine worker. Runs daily at 9 AM ET via cron. Tracks via payment_reminders table in D1.
| Day | Subject | Tone | Sender |
|---|---|---|---|
| Day 3 | "A Gentle Reminder" | Friendly, "things happen!" | billing@ohiocleancans.com |
| Day 7 | "Just Checking In" | Offer help with card, phone number | billing@ohiocleancans.com |
| Day 14 | "Payment Past Due" | Firm, Howard's name, red Pay Now button | Howard Cooper |
| Day 21 | "Final Notice" | Service pause warning, personal from Howard | Howard Cooper |
| Day 28 | — | Auto-pause service, cancel future jobs | — |
Built into the Schedule Engine worker. Fires automatically when crew marks a job complete.
| Step | What Happens |
|---|---|
| 1. Complete | Job marked complete in D1, schedule advances to next service date |
| 2. Card Lookup | Square Customers API searched by email → finds card on file |
| 3a. Charge OK | Card charged via Square Payments API → receipt email sent via Brevo |
| 3b. Charge FAILS | Auto-sends "Payment Issue" email with "Update Payment Method" button → ohiocleancans.com/my-account/ |
| 3c. No Card | Same failure email — asks customer to add a card |
| Prepaid | No charge — sends "cleaning complete" email with cleanings remaining count |
| Free | No charge, no email |
36 pre-written templates built into the Schedule Engine. Crew and Howard can send with one tap. Merge tags: {first}, {address}, {new_date}, {position}, {amount}
| Category | Templates |
|---|---|
| Back-Office (14) | Confirm thanks, final can ready, CC update link, missed by garbage/recycle man, no cans 1st/2nd/free pass, overcharged, can't skip quarter, reschedule confirm, position update, running late |
| Crew (12) | Garbage/recycle still full, no cans out (comeback/waiting), both full, retrieved cans, recycling bagged, garbage/recycle done no return, bags removed, all set, upsell recycle $10 |
| Broadcast (10) | Price adjustment, move to day after, change to quarterly, too cold (delayed/tomorrow warmer), all canceled weather, annual thank you, referral reminder, review request, service area expansion |
Rebuilt April 1, 2026. Auth on completion, batch capture at 8 PM when route is done.
| Step | What Happens | Job Color |
|---|---|---|
| Crew taps Done | Auth only (hold on card, no charge) | GREEN |
| Wrong amount? | Cancel auth → run full sale for correct amount | — |
| 8 PM ET (cron) | Checks if all jobs done → batch captures all auths | BLUE |
| After capture | Receipt emails sent, settlement report to Howard | — |