# Solution Design Document ## Validation Checklist - [x] All required sections are complete - [x] No [NEEDS CLARIFICATION] markers remain - [x] All context sources are listed with relevance ratings - [x] Project commands are discovered from actual project files - [x] Constraints → Strategy → Design → Implementation path is logical - [x] Architecture pattern is clearly stated with rationale - [x] Every component in diagram has directory mapping - [x] Every interface has specification - [x] Error handling covers all error types - [x] Quality requirements are specific and measurable - [x] Every quality requirement has test coverage - [x] **All architecture decisions confirmed by user** - [x] Component names consistent across diagrams - [x] A developer could implement from this design --- ## Constraints CON-1 **Language/Framework**: PHP 8.x, Slim 2.6.2, Twig 1.44.8, jQuery + Syncfusion EJ2, Font Awesome 6 with v4 shims. No framework upgrades. CON-2 **Database**: MySQL multi-store architecture. Central DB (`kiosk_buykiosk`) for billing data, per-store DBs (`kiosk_{typeNum}`) for chat data. All schema changes via migration system (`php userfrosting/conductor run`). Never modify tables directly. CON-3 **Real-time**: Ably for server→client push. Existing channel pattern: `store:{typeNum}` with event names like `workbook:chat:*`. CON-4 **SMS Providers**: Twilio REST API + Vonage (legacy Nexmo HTTP API). Must not break existing send flows. Billing tracking must never block SMS sending. CON-5 **TaskEngine**: Background job infrastructure for async processing. Workers process jobs from Redis-backed queues (`high`, `default`, `low`). CON-6 **UI Framework**: Bootstrap 5.3.3 with custom design tokens (`tokens.css`). Syncfusion EJ2 components preferred over custom implementations. CSS built via `php userfrosting/conductor build-css --minify`. CON-7 **Security**: Webhook security via URL obscurity (secret tokens in path). No HMAC signature validation for v1. Per-provider ENV secrets. CON-8 **Scope**: US/Canada only. No international rates. Forward-only (no backfill). No legacy loyalty SMS. ## Implementation Context ### Required Context Sources - ICO-1 General Application Context ```yaml - doc: CLAUDE.md relevance: HIGH why: "Project structure, commands, conventions, migration system" - doc: docs/specs/037-sms-delivery-cost-tracking/product-requirements.md relevance: CRITICAL why: "All feature requirements, acceptance criteria, and success metrics" - doc: docs/specs/037-sms-delivery-cost-tracking/README.md relevance: CRITICAL why: "All architecture decisions from pre-SDD alignment" ``` - ICO-2 SMS Sending Layer ```yaml - file: userfrosting/src/BuyerKiosk/SMS/TextMessageService/TwilioTextSender.php relevance: HIGH why: "Twilio send implementation. Must add StatusCallback URL. Demo mode blocks sends." - file: userfrosting/src/BuyerKiosk/SMS/TextMessageService/VonageTextSender.php relevance: HIGH why: "Vonage send implementation. Already configures delivery receipt URL but target doesn't exist." - file: userfrosting/src/BuyerKiosk/SMS/TextMessageService/TextMessageService.php relevance: HIGH why: "Orchestrator. Calls logBillingUsage() after every send. Provider injected at construction." ``` - ICO-3 Chat System ```yaml - file: userfrosting/src/BuyerKiosk/Chat/Controllers/ChatWebhookController.php relevance: HIGH why: "Inbound webhook handler. Pattern for new delivery webhook handlers." - file: userfrosting/src/BuyerKiosk/Chat/Controllers/ChatApiController.php relevance: HIGH why: "Outbound message sending. Sets delivery_status='sent'. Publishes Ably events." - file: userfrosting/src/BuyerKiosk/Chat/Events/ChatAblyPublisher.php relevance: HIGH why: "publishDeliveryUpdate() exists but is never called. Channel pattern: store:{typeNum}." - file: userfrosting/src/BuyerKiosk/Chat/Services/ChatBridgeService.php relevance: HIGH why: "Bridges legacy buy SMS into chat_messages. Feature-flagged (workbook_embedded_chat)." - file: userfrosting/routes/chat/webhooks.php relevance: MEDIUM why: "Existing webhook route pattern. New delivery routes follow same structure." ``` - ICO-4 Billing System ```yaml - file: userfrosting/src/BuyerKiosk/Billing/Services/SmsUsageTracker.php relevance: HIGH why: "Current billing SMS tracking. logUsage() stores providerMessageId. Never throws." - file: userfrosting/src/BuyerKiosk/Billing/Repositories/BillingConfigRepository.php relevance: MEDIUM why: "Store billing configuration. billingSmsCategoryConfig access." - file: userfrosting/src/BuyerKiosk/Billing/Controllers/BillingPageController.php relevance: MEDIUM why: "Existing billing pages. SMS Cost report will be added here." - file: userfrosting/src/BuyerKiosk/Billing/Controllers/BillingApiController.php relevance: MEDIUM why: "Existing billing API. SMS cost endpoints will be added here." - file: userfrosting/migrations/input/20260210_036_001_billing_sms_usage.json relevance: HIGH why: "billingSmsUsage table schema. Must add cost columns via new migration." - file: userfrosting/migrations/input/20260210_036_004_billing_sms_config.json relevance: MEDIUM why: "billingSmsCategoryConfig table. Defines per-store, per-category rate config." ``` - ICO-5 TaskEngine ```yaml - file: userfrosting/src/BuyerKiosk/TaskEngine/Jobs/InvoiceGenerationJob.php relevance: MEDIUM why: "Example job pattern: BaseJob, getName(), handle(), JobResult, progress tracking." - file: userfrosting/src/BuyerKiosk/TaskEngine/Domain/Job/BaseJob.php relevance: MEDIUM why: "Base job class with lifecycle hooks, logging, progress, and checkpoint support." - file: userfrosting/src/BuyerKiosk/TaskEngine/Commands/TaskCommandFactory.php relevance: LOW why: "Job dispatch pattern. How to enqueue a job from application code." ``` - ICO-6 Frontend ```yaml - file: userfrosting/templates/themes/default/workspace/partials/chat-panel-content.html relevance: HIGH why: "Chat message template with delivery status icons. Already has FA icon map and Handlebars template." - file: userfrosting/templates/themes/default/workspace/partials/completed/completed-buys.html relevance: HIGH why: "Completed buys grid. Must add delivery status column." - file: public_html/js/workspace/modules/chat/chat-ably-sync.js relevance: HIGH why: "Already subscribes to workbook:chat:delivered events. Delivery update handler exists." - file: public_html/js/workspace/modules/completed/CompletedManager.js relevance: MEDIUM why: "DataTables initialization. Must render delivery status in new column." - file: userfrosting/templates/themes/default/billing/dashboard.html relevance: MEDIUM why: "Existing billing dashboard pattern. SMS cost page follows same layout." ``` - ICO-7 External Provider Documentation ```yaml - url: https://www.twilio.com/docs/messaging/api/message-resource relevance: HIGH sections: [StatusCallback, Message properties (price, status)] why: "Twilio status callback configuration and cost data retrieval" - url: https://developer.vonage.com/en/messaging/sms/guides/delivery-receipts relevance: HIGH sections: [DLR payload format, price field, status values] why: "Vonage delivery receipt format and cost extraction" ``` ### Implementation Boundaries - **Must Preserve**: All existing SMS sending flows (buy completion, service, survey, chat, custom). Billing usage tracking. Chat message display. Ably real-time updates. ChatBridgeService legacy bridge. - **Can Modify**: TwilioTextSender (add StatusCallback), VonageTextSender (update webhook URL), SmsUsageTracker (add cost tracking), ChatAblyPublisher (call existing publishDeliveryUpdate), Completed Buys template (add column), Chat message template (update FA icons to v6), BillingApiController (add SMS cost endpoints), BillingPageController (add SMS cost page). - **Must Not Touch**: TextMessageService core send logic. ChatWebhookController inbound handling. Store.php core properties. Legacy loyalty SMS routes. BaseModel.php queue processing. ### External Interfaces #### System Context Diagram ```mermaid graph TB Staff[Store Staff] -->|View delivery status| WorkbookUI[Workbook SPA] Admin[Business Admin] -->|View SMS costs| BillingUI[Billing Module] WorkbookUI -->|REST API| AppServer[BuyerKiosk PHP App] BillingUI -->|REST API| AppServer AppServer -->|Send SMS| Twilio[Twilio API] AppServer -->|Send SMS| Vonage[Vonage API] Twilio -->|Status Callback| TwilioWebhook[Twilio Delivery Webhook] Vonage -->|Delivery Receipt| VonageWebhook[Vonage Delivery Webhook] TwilioWebhook --> AppServer VonageWebhook --> AppServer AppServer -->|Cost Lookup| TwilioMsgAPI[Twilio Message Resource API] AppServer -->|Dispatch Jobs| TaskEngine[TaskEngine Workers] TaskEngine -->|Cost Lookup| TwilioMsgAPI AppServer -->|Publish Events| Ably[Ably Real-time] Ably -->|Push Updates| WorkbookUI AppServer --> CentralDB[(kiosk_buykiosk)] AppServer --> StoreDB[(kiosk_{typeNum})] TaskEngine --> CentralDB TaskEngine --> StoreDB ``` #### Interface Specifications ```yaml # Inbound Interfaces inbound: - name: "Twilio Status Callback" type: HTTPS POST format: application/x-www-form-urlencoded authentication: URL secret token (TWILIO_WEBHOOK_SECRET) path: /api/webhooks/twilio-delivery/{token} data_flow: "Delivery status updates for outbound Twilio messages" payload_fields: [MessageSid, MessageStatus, ErrorCode, ErrorMessage] - name: "Vonage Delivery Receipt" type: HTTPS GET or POST format: Query params (GET) or form/JSON (POST) authentication: URL secret token (VONAGE_WEBHOOK_SECRET) path: /api/webhooks/vonage-delivery/{token} data_flow: "Delivery receipts with status and price for outbound Vonage messages" payload_fields: [msisdn, to, messageId, status, price, err-code, scts] - name: "Billing SMS Cost Report API" type: HTTPS GET format: JSON authentication: Session (admin role) path: /api/billing/sms-costs data_flow: "SMS cost/revenue/profit data for reporting" - name: "Store SMS Cost Report API" type: HTTPS GET format: JSON authentication: Session (admin or store owner) path: /api/billing/{typeNum}/sms-costs data_flow: "Per-store SMS cost breakdown" # Outbound Interfaces outbound: - name: "Twilio Message Resource API" type: HTTPS GET format: JSON authentication: Basic Auth (TWILIO_SID:TWILIO_TOKEN) endpoint: "https://api.twilio.com/2010-04-01/Accounts/{SID}/Messages/{MessageSid}.json" data_flow: "Retrieve message cost (price field) after delivery" criticality: MEDIUM rate_limit: "Respect Twilio rate limits; batch lookups with backoff" - name: "Ably Publish" type: REST format: JSON authentication: ABLY_KEY channel: "store:{typeNum}" events: ["workbook:chat:delivered"] data_flow: "Push delivery status updates to connected browsers" criticality: LOW (best-effort) # Data Interfaces data: - name: "Central Database (kiosk_buykiosk)" type: MySQL connection: PDO via dbConnectCentral() tables: [billingSmsUsage, billingSmsCategoryConfig, stores, smsWebhookLog] data_flow: "Billing usage, cost tracking, webhook audit logging" - name: "Store Database (kiosk_{typeNum})" type: MySQL connection: PDO via dbConnectByName() tables: [chat_messages, chat_threads] data_flow: "Delivery status updates on per-store chat messages" - name: "Redis" type: Redis connection: Via TaskEngine data_flow: "Job queue for async cost lookups and demo simulation" ``` ### Cross-Component Boundaries - **API Contracts**: Webhook endpoints are external-facing contracts with Twilio/Vonage. Path format and response (always 200 OK) are fixed. - **Shared Resources**: `billingSmsUsage` table is shared between SmsUsageTracker (writes at send time) and webhook handlers (updates with cost/delivery data). Indexed on `providerMessageId` for fast lookups. - **Breaking Change Policy**: No changes to existing API endpoints. New endpoints only. billingSmsUsage column additions are backward-compatible (nullable with defaults). ### Project Commands ```bash # Environment Setup Install Dependencies: cd userfrosting && composer install Environment Variables: .env (add TWILIO_WEBHOOK_SECRET, VONAGE_WEBHOOK_SECRET) Start Development: php -S localhost:8080 -t public_html # Database Migrations Run Migrations: php userfrosting/conductor run Migration Input: userfrosting/migrations/input/ # CSS Build Development: php userfrosting/conductor build-css Production: php userfrosting/conductor build-css --minify Watch: php userfrosting/conductor build-css --watch # Testing All Tests: ./test.sh Unit Tests: ./test.sh --testsuite unit Integration Tests: ./test.sh --testsuite integration Coverage: ./test.sh --coverage Targeted: cd userfrosting && ./vendor/bin/phpunit --filter "ClassName" Static Analysis: cd userfrosting && ./vendor/bin/phpstan analyse # TaskEngine Start Worker: php userfrosting/bin/task worker:start Dispatch Job: php userfrosting/bin/task job:dispatch Queue Status: php userfrosting/bin/task queue:status --detailed # Deployment Deploy: ./deploy.sh ``` ## Solution Strategy - **Architecture Pattern**: Event-driven webhook processing with async cost enrichment. Incoming webhooks are fast (log + update + publish), expensive operations (Twilio API cost lookups) are offloaded to TaskEngine background jobs. - **Integration Approach**: Minimal-touch extension of existing systems. The SMS sending layer gets 2 small additions (StatusCallback URL in Twilio, updated webhook URL in Vonage). The billing layer gets 3 new columns on an existing table. A new webhook controller handles delivery receipts. The existing `ChatAblyPublisher::publishDeliveryUpdate()` finally gets called. The Billing module UI gets a new page. - **Justification**: This approach maximizes reuse of existing infrastructure (chat_messages already has delivery columns, Ably already has the delivery event, ChatBridgeService already bridges buy SMS into chat). The only truly new components are the webhook handlers, cost lookup job, and billing report UI. - **Key Decisions**: 1. Keep 6-state DB enum, map to 3 in UI — preserves provider granularity 2. Cost columns on existing `billingSmsUsage` — avoids new table and JOINs 3. TaskEngine job per message for Twilio cost — keeps webhooks under 200ms 4. Store resolution via central `billingSmsUsage.providerMessageId` — reliable, no cross-DB search 5. Auto-enable `workbook_embedded_chat` flag — ensures all stores have delivery tracking ## Building Block View ### Components ```mermaid graph LR subgraph SMS Sending TTS[TwilioTextSender] VTS[VonageTextSender] TMS[TextMessageService] CBS[ChatBridgeService] end subgraph Webhook Processing TWH[TwilioDeliveryHandler] VWH[VonageDeliveryHandler] DSP[DeliveryStatusProcessor] end subgraph Cost Tracking CLJ[SmsCostLookupJob] DSJ[DemoSimulationJob] SUT[SmsUsageTracker] end subgraph Real-time CAP[ChatAblyPublisher] WBT[WorkbookToast] end subgraph Billing Report BPC[BillingPageController] BAC[BillingApiController] SCR[SMS Cost Report UI] end subgraph Data Stores BSU[(billingSmsUsage)] CM[(chat_messages)] WHL[(smsWebhookLog)] end TMS --> TTS TMS --> VTS TMS --> SUT CBS --> CM TTS -->|StatusCallback| TWH VTS -->|DLR callback| VWH TWH --> DSP VWH --> DSP DSP -->|Update delivery status| CM DSP -->|Update delivery status| BSU DSP -->|Log webhook| WHL DSP -->|Publish event| CAP DSP -->|Dispatch job| CLJ CLJ -->|Twilio API| TwilioAPI[Twilio Message API] CLJ -->|Update cost| BSU DSJ -->|Simulate delivery| CM DSJ -->|Publish event| CAP CAP -->|Ably| Browser[Browser] Browser --> WBT BAC --> BSU BPC --> SCR ``` ### Directory Map **Backend: New Files** ``` userfrosting/ ├── src/BuyerKiosk/ │ ├── SMS/ │ │ ├── Webhooks/ │ │ │ ├── TwilioDeliveryHandler.php # NEW: Twilio status callback handler │ │ │ ├── VonageDeliveryHandler.php # NEW: Vonage DLR handler │ │ │ ├── DeliveryStatusProcessor.php # NEW: Shared processing logic │ │ │ └── DeliveryStatusMapper.php # NEW: Provider status → 3-state mapping │ │ └── TextMessageService/ │ │ ├── TwilioTextSender.php # MODIFY: Add StatusCallback URL │ │ └── VonageTextSender.php # MODIFY: Update webhook URL path │ ├── Billing/ │ │ ├── Controllers/ │ │ │ ├── BillingApiController.php # MODIFY: Add SMS cost endpoints │ │ │ └── BillingPageController.php # MODIFY: Add SMS cost page route │ │ ├── Services/ │ │ │ ├── SmsUsageTracker.php # MODIFY: Add cost update method │ │ │ └── SmsCostReportService.php # NEW: Cost/profit aggregation queries │ │ └── Repositories/ │ │ └── SmsUsageRepository.php # MODIFY: Add cost update + report queries │ └── TaskEngine/ │ └── Jobs/ │ ├── SmsCostLookupJob.php # NEW: Async Twilio cost lookup │ ├── DemoDeliverySimulationJob.php # NEW: Simulate delivery for demo stores │ └── SmsStaleCostSweepJob.php # NEW: Daily sweep for stale costStatus='pending' ├── routes/ │ └── sms/ │ └── webhooks.php # NEW: Delivery webhook routes ├── migrations/ │ └── input/ │ ├── 20260211_037_001_billing_sms_cost_columns.json # NEW: Add cost columns to billingSmsUsage │ ├── 20260211_037_002_sms_webhook_log.json # NEW: Create smsWebhookLog table │ ├── 20260211_037_003_sms_cost_lookup_job.json # NEW: Register SmsCostLookupJob │ ├── 20260211_037_004_demo_simulation_job.json # NEW: Register DemoDeliverySimulationJob │ └── 20260211_037_005_enable_embedded_chat.json # NEW: Auto-enable workbook_embedded_chat └── templates/themes/default/ └── billing/ └── sms-costs.html # NEW: SMS cost report page ``` **Backend: Modified Files** ``` userfrosting/ ├── src/BuyerKiosk/ │ ├── SMS/TextMessageService/ │ │ ├── TwilioTextSender.php # MODIFY: Add StatusCallback + demo job dispatch │ │ └── VonageTextSender.php # MODIFY: Update webhook URL + demo job dispatch │ ├── Chat/Controllers/ │ │ ├── ChatWebhookController.php # MODIFY: Add inbound cost tracking after message save │ │ └── ChatApiController.php # MODIFY: Ensure provider_message_id stored on send │ ├── Billing/ │ │ ├── Controllers/BillingApiController.php # MODIFY: Add SMS cost API endpoints │ │ ├── Controllers/BillingPageController.php # MODIFY: Add smsCosts() page method │ │ ├── Services/SmsUsageTracker.php # MODIFY: Add updateCost() method │ │ └── Repositories/SmsUsageRepository.php # MODIFY: Add cost queries │ └── Core/Store.php # READ ONLY: getDev() for demo detection ├── routes/billing/ │ ├── api.php # MODIFY: Add SMS cost routes │ └── pages.php # MODIFY: Add SMS cost page route └── templates/themes/default/ ├── workspace/partials/ │ ├── completed/completed-buys.html # MODIFY: Add delivery status column │ └── chat-panel-content.html # MODIFY: Update FA icons to v6 ├── billing/dashboard.html # MODIFY: Add SMS Costs nav link └── menus/sidebar.html # MODIFY: Add SMS Costs menu item (if needed) ``` **Frontend: New Files** ``` public_html/ ├── js/workspace/modules/ │ └── shared/ │ └── WorkbookToast.js # NEW: Global toast manager ├── css/admin/modules/ │ └── billing.css # MODIFY: Add SMS cost report styles ``` **Frontend: Modified Files** ``` public_html/ ├── js/workspace/modules/ │ ├── completed/CompletedManager.js # MODIFY: Render delivery status column │ ├── chat/chat-ably-sync.js # MODIFY: Handle delivery updates (already subscribed) │ ├── chat/chat-notifications.js # MODIFY: Use WorkbookToast for delivery failures │ └── workbook/ably-sync.js # MODIFY: Subscribe to delivery events globally ``` ### Interface Specifications #### Data Storage Changes **Migration 037_001: Add cost columns to billingSmsUsage** ```yaml Table: billingSmsUsage (kiosk_buykiosk) ALTER COLUMN: smsCategory → ADD 'inbound' to ENUM COMMENT: "Add 'inbound' category for tracking inbound message costs" ADD COLUMN: providerCostUsd DECIMAL(10,6) DEFAULT NULL COMMENT: "Actual cost charged by provider in USD" ADD COLUMN: costStatus ENUM('pending','captured','unknown') DEFAULT 'pending' COMMENT: "Cost capture state: pending=awaiting, captured=cost known, unknown=lookup failed" ADD COLUMN: costCapturedAt DATETIME DEFAULT NULL COMMENT: "When cost was successfully captured" ADD COLUMN: deliveryStatus ENUM('pending','sent','delivered','failed') DEFAULT 'pending' COMMENT: "Mapped 3-state delivery status for reporting" ADD COLUMN: providerRawStatus VARCHAR(50) DEFAULT NULL COMMENT: "Original status string from provider webhook" ADD COLUMN: deliveryUpdatedAt DATETIME DEFAULT NULL COMMENT: "When delivery status was last updated from webhook" ADD COLUMN: deliveryErrorCode VARCHAR(20) DEFAULT NULL COMMENT: "Provider error code on failure" ADD COLUMN: deliveryErrorMessage VARCHAR(500) DEFAULT NULL COMMENT: "Human-readable error description" ADD COLUMN: costRetryCount TINYINT UNSIGNED DEFAULT 0 COMMENT: "Number of cost lookup retries attempted" ADD INDEX: idx_cost_pending (costStatus, createdAt) COMMENT: "For SmsCostLookupJob retry sweep" ADD INDEX: idx_delivery_status (typeNum, deliveryStatus, sentAt) COMMENT: "For delivery rate KPI calculations" ``` **Migration 037_002: Create smsWebhookLog table** ```yaml Table: smsWebhookLog (kiosk_buykiosk) (NEW) id: BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY provider: ENUM('twilio','vonage') NOT NULL webhookType: ENUM('delivery','inbound_cost') NOT NULL providerMessageId: VARCHAR(255) DEFAULT NULL httpMethod: VARCHAR(10) NOT NULL COMMENT "GET or POST" rawPayload: TEXT NOT NULL COMMENT "Full webhook payload for debugging" processingResult: ENUM('success','error','unmatched') NOT NULL errorMessage: VARCHAR(500) DEFAULT NULL processingTimeMs: INT UNSIGNED DEFAULT NULL createdAt: DATETIME DEFAULT CURRENT_TIMESTAMP INDEX: idx_provider_msg (providerMessageId) INDEX: idx_created (createdAt) INDEX: idx_result (processingResult, createdAt) ``` **Migration 037_003: Register SmsCostLookupJob** ```yaml Table: taskJobDefinitions (kiosk_buykiosk) INSERT jobName: 'sms:cost-lookup' jobClass: 'BuyerKiosk\\TaskEngine\\Jobs\\SmsCostLookupJob' description: 'Look up actual SMS cost from Twilio API for a specific message' cronExpression: NULL (event-driven, not scheduled) isActive: 1 queueName: 'low' maxRetries: 3 retryDelaySeconds: 3600 (1 hour between retries = 3 retries over ~4 hours) timeoutSeconds: 30 payload: '{}' ``` **Migration 037_004: Register DemoDeliverySimulationJob** ```yaml Table: taskJobDefinitions (kiosk_buykiosk) INSERT jobName: 'sms:demo-delivery-simulation' jobClass: 'BuyerKiosk\\TaskEngine\\Jobs\\DemoDeliverySimulationJob' description: 'Simulate delivery receipt for demo store messages' cronExpression: NULL (event-driven) isActive: 1 queueName: 'low' maxRetries: 1 retryDelaySeconds: 5 timeoutSeconds: 10 payload: '{}' ``` **Migration 037_004b: Register SmsStaleCostSweepJob** ```yaml Table: taskJobDefinitions (kiosk_buykiosk) INSERT jobName: 'sms:stale-cost-sweep' jobClass: 'BuyerKiosk\\TaskEngine\\Jobs\\SmsStaleCostSweepJob' description: 'Daily sweep for messages stuck in costStatus=pending after 24 hours' cronExpression: '0 6 * * *' (daily at 6am) isActive: 1 queueName: 'low' maxRetries: 1 retryDelaySeconds: 3600 timeoutSeconds: 300 payload: '{}' ``` **Migration 037_005: Auto-enable workbook_embedded_chat** ```yaml Table: storeFeatureFlags (kiosk_buykiosk) UPDATE SET isActive = 1 WHERE featureName = 'workbook_embedded_chat' COMMENT: "Required for delivery tracking — ChatBridgeService needs this flag ON" ``` #### Internal API Changes ```yaml # SMS Cost Report - All Stores (Admin Only) Endpoint: Get SMS Cost Summary Method: GET Path: /api/billing/sms-costs Authentication: Session, requires uri_admin_billing permission Query Parameters: period: string (YYYY-MM format, required) groupBy: string (enum: store|category, default: store) Response: success: summary: totalCost: number (USD) totalRevenue: number (USD) profitMargin: number (percentage) totalMessages: integer costCaptureRate: number (percentage of messages with known cost) breakdown: array of: label: string (typeNum or category name) messageCount: integer segmentCount: integer providerCost: number (USD) revenue: number (USD) profit: number (USD) marginPct: number deliveryRate: number (percentage) chartData: periods: array of string (YYYY-MM) costSeries: array of number revenueSeries: array of number marginSeries: array of number error: error_code: string message: string # SMS Cost Report - Single Store Endpoint: Get Store SMS Cost Detail Method: GET Path: /api/billing/{typeNum}/sms-costs Authentication: Session, requires uri_admin_billing OR uri_store_billing for typeNum Query Parameters: period: string (YYYY-MM, required) startDate: string (YYYY-MM-DD, optional override) endDate: string (YYYY-MM-DD, optional override) Response: success: store: typeNum: string provider: string (twilio|vonage) billingActive: boolean summary: totalCost: number totalRevenue: number profitMargin: number deliveryRate: number costCaptureRate: number categories: array of: category: string (enum from billingSmsCategoryConfig) categoryLabel: string (human-readable, e.g., "Buy Completion", "Service", "Chat") messageCount: integer segmentCount: integer providerCost: number ratePerMessage: number (from billingSmsCategoryConfig) revenue: number profit: number billable: boolean includedInBasePlan: boolean (true when billable=0, i.e., included in store's base billing rate) deliveryBreakdown: delivered: integer failed: integer sent: integer (no receipt) total: integer deliveredPct: number failedPct: number unknownPct: number error: error_code: string message: string # SMS Delivery Rate KPI - All Stores Endpoint: Get Delivery Rate KPI Method: GET Path: /api/billing/sms-delivery-rates Authentication: Session, requires uri_admin_billing Query Parameters: period: string (YYYY-MM, required) Response: success: stores: array of: typeNum: string deliveryRate: number deliveredCount: integer failedCount: integer unknownCount: integer totalCount: integer flagged: boolean (true if deliveryRate < 90%) error: error_code: string message: string # Chart Data for SMS Costs (12-month trend) Endpoint: Get SMS Cost Trend Method: GET Path: /api/billing/sms-costs/trend Authentication: Session, requires uri_admin_billing Query Parameters: months: integer (default: 12, max: 24) typeNum: string (optional, filter to single store) Response: success: periods: array of string (YYYY-MM) series: cost: array of number revenue: array of number margin: array of number (percentages) error: error_code: string message: string ``` #### Application Data Models ```pseudocode # Modified: SmsUsageTracker ENTITY: SmsUsageTracker (MODIFIED) BEHAVIORS: logUsage(typeNum, category, segments, provider, msgId, success, billable): void # EXISTING + updateDeliveryStatus(providerMessageId, deliveryStatus, rawStatus, errorCode, errorMessage): bool # NEW + updateCost(providerMessageId, costUsd): bool # NEW + markCostUnknown(providerMessageId, reason): bool # NEW + incrementCostRetry(providerMessageId): int # NEW — returns new retry count # New: DeliveryStatusProcessor ENTITY: DeliveryStatusProcessor (NEW) FIELDS: smsUsageTracker: SmsUsageTracker ablyPublisher: ChatAblyPublisher logger: KLogger BEHAVIORS: processDeliveryUpdate(provider, providerMessageId, rawStatus, errorCode, errorMessage): ProcessingResult # Steps: # 1. Map raw status to 3-state via DeliveryStatusMapper # 2. Look up billingSmsUsage by providerMessageId → get typeNum # 3. Update billingSmsUsage delivery columns # 4. If typeNum found: update chat_messages in store DB # 5. If typeNum found: publish Ably delivery event # 6. If Twilio + delivered/failed: dispatch SmsCostLookupJob # 7. Return ProcessingResult # New: DeliveryStatusMapper ENTITY: DeliveryStatusMapper (NEW) BEHAVIORS: mapTwilioStatus(rawStatus): MappedStatus mapVonageStatus(rawStatus): MappedStatus getErrorDescription(provider, errorCode): string # Human-readable translation # New: MappedStatus (Value Object) ENTITY: MappedStatus (NEW) FIELDS: displayStatus: string (sent|delivered|failed) rawStatus: string isFinal: boolean (true for delivered, failed; false for sent/queued/pending) # New: ProcessingResult (Value Object) ENTITY: ProcessingResult (NEW) FIELDS: success: boolean providerMessageId: string mappedStatus: MappedStatus typeNum: ?string (null if unmatched) chatMessageUpdated: boolean ablyPublished: boolean costJobDispatched: boolean errorMessage: ?string # New: SmsCostLookupJob ENTITY: SmsCostLookupJob (NEW) extends BaseJob FIELDS: providerMessageId: string (from job payload) provider: string (from job payload) BEHAVIORS: handle(): JobResult # Steps: # 1. Query Twilio Message Resource API by SID # 2. Extract price field (string, e.g., "-0.00750") # 3. Convert to positive decimal USD # 4. Call smsUsageTracker->updateCost(msgId, costUsd) # 5. Return success/failure # New: DemoDeliverySimulationJob ENTITY: DemoDeliverySimulationJob (NEW) extends BaseJob FIELDS: providerMessageId: string typeNum: string BEHAVIORS: handle(): JobResult # Steps: # 1. Sleep random 3-5 seconds # 2. Update chat_messages.delivery_status = 'delivered' in store DB # 3. Update billingSmsUsage.deliveryStatus = 'delivered' # 4. Set providerCostUsd = 0.00, costStatus = 'captured' # 5. Publish Ably delivery event # 6. Return success # New: SmsStaleCostSweepJob ENTITY: SmsStaleCostSweepJob (NEW) extends BaseJob BEHAVIORS: handle(): JobResult # Steps: # 1. Query billingSmsUsage WHERE costStatus='pending' AND createdAt < NOW() - INTERVAL 24 HOUR # 2. For each stale record: # a. If provider='twilio': dispatch SmsCostLookupJob (one final attempt) # b. If provider='vonage': markCostUnknown() immediately (DLR was the only chance) # 3. Log count of records processed # 4. Return JobResult::success with stats # New: SmsCostReportService ENTITY: SmsCostReportService (NEW) FIELDS: db: PDO (central) BEHAVIORS: getSmsCostSummary(period, ?typeNum, groupBy): array getStoreCostDetail(typeNum, period, ?startDate, ?endDate): array getDeliveryRates(period): array getCostTrend(months, ?typeNum): array # All queries exclude demo stores (stores.dev = 1) # All queries use billingSmsUsage joined with billingSmsCategoryConfig for rates # New: TwilioDeliveryHandler ENTITY: TwilioDeliveryHandler (NEW) BEHAVIORS: handle(request): Response # Steps: # 1. Validate webhook token from URL matches TWILIO_WEBHOOK_SECRET # 2. Extract: MessageSid, MessageStatus, ErrorCode, ErrorMessage # 3. Log to smsWebhookLog # 4. Call DeliveryStatusProcessor->processDeliveryUpdate() # 5. Return 200 OK (always, even on processing failure) # New: VonageDeliveryHandler ENTITY: VonageDeliveryHandler (NEW) BEHAVIORS: handle(request): Response # Steps: # 1. Validate webhook token from URL matches VONAGE_WEBHOOK_SECRET # 2. Extract from GET params or POST body: messageId, status, price, err-code # 3. Log to smsWebhookLog # 4. Call DeliveryStatusProcessor->processDeliveryUpdate() # 5. If price present: call smsUsageTracker->updateCost() directly # 6. Return 200 OK (always) # New: WorkbookToast (Frontend) ENTITY: WorkbookToast (NEW) BEHAVIORS: init(ablyChannel): void # Subscribe to delivery failure events showDeliveryFailure(customerName, typeNum, errorReason): void showToast(message, type, duration): void # Generic toast display dismiss(toastId): void ``` #### Integration Points ```yaml # Inter-Component Communication - from: TwilioTextSender/VonageTextSender to: DemoDeliverySimulationJob protocol: TaskEngine job dispatch data_flow: "Demo stores dispatch simulation job instead of expecting real webhook" - from: TwilioDeliveryHandler/VonageDeliveryHandler to: DeliveryStatusProcessor protocol: Direct PHP method call data_flow: "Parsed webhook data → delivery status processing" - from: DeliveryStatusProcessor to: SmsCostLookupJob protocol: TaskEngine job dispatch data_flow: "Twilio message SID → async cost lookup" - from: DeliveryStatusProcessor to: ChatAblyPublisher protocol: Direct PHP method call → Ably REST API data_flow: "Delivery status update → real-time browser push" - from: ChatAblyPublisher (Ably) to: WorkbookToast (Browser) protocol: Ably WebSocket subscription data_flow: "workbook:chat:delivered event → toast notification on failure" # External System Integration Twilio: - outbound_change: Add StatusCallback URL to messages->create() parameters - new_inbound: POST /api/webhooks/twilio-delivery/{TWILIO_WEBHOOK_SECRET} - new_outbound: GET https://api.twilio.com/.../Messages/{SID}.json for cost lookup - critical_data: [MessageSid, MessageStatus, ErrorCode, price] Vonage: - outbound_change: Update callback URL from /api/webhooks/vonage-delivery-simple.php to /api/webhooks/vonage-delivery/{VONAGE_WEBHOOK_SECRET} - new_inbound: GET or POST /api/webhooks/vonage-delivery/{VONAGE_WEBHOOK_SECRET} - critical_data: [messageId, status, price, err-code] Ably: - existing_channel: store:{typeNum} - existing_event: workbook:chat:delivered (already defined, now actually used) - payload: threadId: int (chat thread ID) messageId: int (chat message ID) status: string (sent|delivered|failed) errorReason: ?string (human-readable error on failure) customerName: ?string (first + last initial, e.g., "John D." — from chat_threads.customer join) typeNum: string (store identifier, for toast context) ``` ### Implementation Examples #### Example: DeliveryStatusProcessor Core Logic **Why this example**: This is the central orchestration point where webhooks, database updates, Ably events, and job dispatches converge. Getting this flow right is critical. ```php // DeliveryStatusProcessor.php — Core processing flow // This demonstrates the expected logic flow, not the exact implementation class DeliveryStatusProcessor { public function processDeliveryUpdate( string $provider, string $providerMessageId, string $rawStatus, ?string $errorCode, ?string $errorMessage ): ProcessingResult { $result = new ProcessingResult($providerMessageId); // 1. Map provider status to our 3-state model $mapped = $this->mapper->mapStatus($provider, $rawStatus); $result->mappedStatus = $mapped; // 2. Look up the usage record in central DB to find typeNum $usageRecord = $this->usageRepo->findByProviderMessageId($providerMessageId); if (!$usageRecord) { // Unmatched — log and return (per decision: ignore unmatched) $this->logger->warning("Unmatched delivery receipt: {$providerMessageId}"); $result->success = false; $result->errorMessage = 'No matching billingSmsUsage record'; return $result; } $result->typeNum = $usageRecord['typeNum']; // 3. Update billingSmsUsage in central DB $humanError = $errorCode ? $this->mapper->getErrorDescription($provider, $errorCode) : $errorMessage; $this->usageTracker->updateDeliveryStatus( $providerMessageId, $mapped->displayStatus, $rawStatus, $errorCode, $humanError ); // 4. Update chat_messages in store DB (if message exists there) try { $storeDb = dbConnectByName('kiosk_' . $usageRecord['typeNum']); $chatUpdated = $this->updateChatMessage( $storeDb, $providerMessageId, $mapped, $humanError ); $result->chatMessageUpdated = $chatUpdated; } catch (\Exception $e) { // Non-fatal: billing data is already updated $this->logger->error("Chat message update failed: " . $e->getMessage()); } // 5. Publish Ably event for real-time UI update (with customer name for toast) if ($result->chatMessageUpdated) { try { $customerName = $this->getCustomerName($storeDb, $chatMessage['thread_id']); $this->ablyPublisher->setTypeNum($usageRecord['typeNum']); $this->ablyPublisher->publishDeliveryUpdate( $chatMessage['thread_id'], $chatMessage['id'], $mapped->displayStatus, $humanError, // errorReason for toast $customerName, // "John D." for toast $usageRecord['typeNum'] // store context for toast ); $result->ablyPublished = true; } catch (\Exception $e) { // Non-fatal: best-effort real-time $this->logger->warning("Ably publish failed: " . $e->getMessage()); } } // 6. Dispatch cost lookup job for Twilio (Vonage cost comes in webhook) if ($provider === 'twilio' && $mapped->isFinal) { $this->dispatchCostLookup($providerMessageId, $provider); $result->costJobDispatched = true; } $result->success = true; return $result; } } ``` #### Example: Twilio Cost Lookup Job **Why this example**: Shows the async cost retrieval pattern with retry and error handling, which is the most complex background job. ```php // SmsCostLookupJob.php — Async Twilio cost lookup class SmsCostLookupJob extends BaseJob { public function handle(): JobResult { $messageSid = $this->payload['providerMessageId']; $provider = $this->payload['provider']; $this->info("Looking up cost for {$provider} message: {$messageSid}"); // Only Twilio needs API lookup; Vonage cost comes in DLR if ($provider !== 'twilio') { return JobResult::success([], 'Non-Twilio message, skipping'); } try { $client = new \Twilio\Rest\Client( $_ENV['TWILIO_SID'], $_ENV['TWILIO_TOKEN'] ); $message = $client->messages($messageSid)->fetch(); if ($message->price !== null) { // Twilio returns price as negative string (e.g., "-0.00750") $costUsd = abs((float) $message->price); $this->usageTracker->updateCost($messageSid, $costUsd); $this->info("Cost captured: \${$costUsd}"); return JobResult::success(['cost' => $costUsd], "Cost: \${$costUsd}"); } // Price not yet available — Twilio may still be processing $retryCount = $this->usageTracker->incrementCostRetry($messageSid); if ($retryCount < 3) { $this->warning("Price not available yet, retry {$retryCount}/3"); // Job will be retried per maxRetries config (3 retries, 1hr apart) return JobResult::failure("Price not available, will retry"); } // Max retries exhausted $this->usageTracker->markCostUnknown($messageSid, 'Price unavailable after 3 retries'); return JobResult::success([], 'Marked cost_unknown after max retries'); } catch (\Twilio\Exceptions\RestException $e) { if ($e->getStatusCode() === 404) { $this->usageTracker->markCostUnknown($messageSid, 'Message not found in Twilio'); return JobResult::success([], 'Message not found, marked unknown'); } // Transient error — allow retry $this->error("Twilio API error: " . $e->getMessage()); return JobResult::failure($e->getMessage()); } } } ``` #### Example: Delivery Status Mapping **Why this example**: The status mapping is critical for correctness and must handle all known provider statuses. ```php // DeliveryStatusMapper.php class DeliveryStatusMapper { private const TWILIO_MAP = [ // Sent (in-progress) 'accepted' => 'sent', 'queued' => 'sent', 'sending' => 'sent', 'sent' => 'sent', // Delivered (final) 'delivered' => 'delivered', 'read' => 'delivered', // WhatsApp read receipt (future-proof) // Failed (final) 'failed' => 'failed', 'undelivered' => 'failed', 'canceled' => 'failed', ]; private const VONAGE_MAP = [ // Sent (in-progress) 'submitted' => 'sent', 'buffered' => 'sent', 'accepted' => 'sent', // Delivered (final) 'delivered' => 'delivered', // Failed (final) 'failed' => 'failed', 'rejected' => 'failed', 'expired' => 'failed', 'unknown' => 'failed', // Vonage "unknown" = carrier couldn't confirm ]; private const FINAL_STATUSES = ['delivered', 'failed']; // Error code → human-readable translations private const TWILIO_ERRORS = [ '30001' => 'Queue overflow — message not sent', '30002' => 'Account suspended', '30003' => 'Unreachable phone — may be disconnected or out of range', '30004' => 'Message blocked by carrier', '30005' => 'Unknown destination handset', '30006' => 'Landline or unreachable carrier', '30007' => 'Carrier content filtering', '30008' => 'Unknown error from carrier', '30010' => 'Message price exceeds max price', '21610' => 'Recipient opted out (STOP)', ]; private const VONAGE_ERRORS = [ '0' => 'Delivered successfully', '1' => 'Unknown error', '2' => 'Absent subscriber — temporary', '3' => 'Absent subscriber — permanent', '4' => 'Call barred by user', '5' => 'Portability error', '6' => 'Anti-spam rejection', '7' => 'Handset busy', '8' => 'Network error', '9' => 'Illegal number', '11' => 'Unroutable', '12' => 'Destination unreachable', '13' => 'Subscriber age restriction', '99' => 'General error', ]; } ``` #### Example: Frontend WorkbookToast Service **Why this example**: Shows the global toast pattern that works across all SPA pages. ```javascript // WorkbookToast.js — Global delivery failure toast manager class WorkbookToast { constructor() { this.container = null; this.TOAST_TIMEOUT = 8000; // 8 seconds for failure toasts } init(ablyChannel) { this.createContainer(); // Listen for delivery status updates globally ablyChannel.subscribe('workbook:chat:delivered', (message) => { const data = message.data; if (data.status === 'failed') { this.showDeliveryFailure(data); } }); } createContainer() { if (document.getElementById('workbook-toast-container')) return; const container = document.createElement('div'); container.id = 'workbook-toast-container'; container.className = 'toast-container position-fixed top-0 end-0 p-3'; container.style.zIndex = '1090'; document.body.appendChild(container); this.container = container; } showDeliveryFailure(data) { const customerName = data.customerName || 'Unknown'; const message = `SMS to ${customerName} failed to deliver`; const detail = data.errorReason || 'No additional details from carrier'; this.showToast(message, 'danger', this.TOAST_TIMEOUT, detail); } showToast(message, type = 'info', duration = 5000, detail = null) { const toastId = 'toast-' + Date.now(); const iconClass = type === 'danger' ? 'fa-solid fa-circle-xmark' : 'fa-solid fa-circle-info'; const html = ` `; this.container.insertAdjacentHTML('beforeend', html); const toastEl = document.getElementById(toastId); const bsToast = new bootstrap.Toast(toastEl); bsToast.show(); // Clean up DOM after hidden toastEl.addEventListener('hidden.bs.toast', () => toastEl.remove()); } } // Initialize globally in workspace.js window.workbookToast = new WorkbookToast(); ``` #### Test Examples as Interface Documentation ```php // Unit test documenting DeliveryStatusProcessor interface contract class DeliveryStatusProcessorTest extends TestCase { public function test_twilio_delivered_status_updates_both_databases_and_dispatches_cost_job(): void { // Given: A message sent via Twilio exists in billingSmsUsage $processor = new DeliveryStatusProcessor( $this->mockUsageTracker, $this->mockAblyPublisher, $this->mockLogger ); // When: Twilio webhook reports "delivered" $result = $processor->processDeliveryUpdate( provider: 'twilio', providerMessageId: 'SM1234567890', rawStatus: 'delivered', errorCode: null, errorMessage: null ); // Then: Both databases updated, Ably published, cost job dispatched $this->assertTrue($result->success); $this->assertEquals('delivered', $result->mappedStatus->displayStatus); $this->assertTrue($result->mappedStatus->isFinal); $this->assertTrue($result->chatMessageUpdated); $this->assertTrue($result->ablyPublished); $this->assertTrue($result->costJobDispatched); } public function test_unmatched_message_is_logged_and_discarded(): void { // Given: No matching record in billingSmsUsage $this->mockUsageRepo->method('findByProviderMessageId')->willReturn(null); // When: Webhook arrives for unknown message $result = $processor->processDeliveryUpdate( 'twilio', 'SM_UNKNOWN', 'delivered', null, null ); // Then: Logged but not processed $this->assertFalse($result->success); $this->assertNull($result->typeNum); $this->assertStringContains('No matching', $result->errorMessage); } public function test_vonage_failure_includes_human_readable_error(): void { // When: Vonage reports failure with error code $result = $processor->processDeliveryUpdate( 'vonage', 'MSG123', 'failed', '6', null ); // Then: Error translated to human-readable $this->assertEquals('failed', $result->mappedStatus->displayStatus); // Error code 6 = "Anti-spam rejection" } } ``` ## Runtime View ### Primary Flow: Outbound SMS with Delivery Tracking 1. Staff sends message (Chat, buy completion, or any other path) 2. TextMessageService calls TwilioTextSender/VonageTextSender 3. Sender includes StatusCallback URL in API request (Twilio) or callback URL (Vonage) 4. Sender returns provider message ID 5. SmsUsageTracker logs usage with `costStatus='pending'`, `deliveryStatus='pending'` 6. ChatBridgeService (for buys) or ChatApiController (for chat) stores `delivery_status='sent'` in chat_messages 7. For demo stores: sender dispatches `DemoDeliverySimulationJob` instead of expecting webhook ```mermaid sequenceDiagram actor Staff participant UI as Workbook UI participant TMS as TextMessageService participant Sender as Twilio/VonageSender participant SUT as SmsUsageTracker participant CBS as ChatBridgeService participant Provider as Twilio/Vonage API Staff->>UI: Send message UI->>TMS: sendBuyText() / sendCustomText() TMS->>Sender: sendText(message, phone) Sender->>Provider: Create message (with StatusCallback URL) Provider-->>Sender: Message SID/ID Sender-->>TMS: {provider, status, id} TMS->>SUT: logUsage(typeNum, category, ..., providerMessageId) Note over SUT: billingSmsUsage: costStatus=pending, deliveryStatus=pending TMS->>CBS: bridgeFromLegacySend(buyId, typeNum, customer, result) Note over CBS: chat_messages: delivery_status=sent TMS-->>UI: Success response ``` ### Primary Flow: Delivery Receipt Processing 1. Provider sends delivery receipt webhook to our endpoint 2. Webhook handler validates token, extracts data, logs to smsWebhookLog 3. DeliveryStatusProcessor maps provider status → 3-state 4. Processor updates billingSmsUsage (central DB) 5. Processor updates chat_messages (store DB) 6. Processor publishes Ably event for real-time UI 7. For Twilio: dispatches SmsCostLookupJob 8. For Vonage: updates cost directly (included in DLR payload) ```mermaid sequenceDiagram participant Provider as Twilio/Vonage participant WH as Webhook Handler participant WHL as smsWebhookLog participant DSP as DeliveryStatusProcessor participant BSU as billingSmsUsage participant CM as chat_messages participant Ably as Ably participant Job as SmsCostLookupJob participant Browser as Staff Browser Provider->>WH: POST /api/webhooks/{provider}-delivery/{token} WH->>WHL: Log raw payload WH->>DSP: processDeliveryUpdate(provider, msgId, status, error) DSP->>BSU: findByProviderMessageId(msgId) → typeNum DSP->>BSU: updateDeliveryStatus(msgId, mapped, raw, error) DSP->>CM: UPDATE delivery_status, delivery_error WHERE provider_message_id DSP->>Ably: publishDeliveryUpdate(threadId, messageId, status) Ably-->>Browser: workbook:chat:delivered event alt Twilio (no cost in webhook) DSP->>Job: dispatch SmsCostLookupJob(msgId) else Vonage (cost in DLR) DSP->>BSU: updateCost(msgId, price) end WH-->>Provider: 200 OK (always) Browser->>Browser: Update delivery icon / show toast ``` ### Secondary Flow: Twilio Cost Lookup ```mermaid sequenceDiagram participant Worker as TaskEngine Worker participant Job as SmsCostLookupJob participant API as Twilio Message API participant BSU as billingSmsUsage Worker->>Job: handle() Job->>API: GET /Messages/{SID}.json alt Price available API-->>Job: {price: "-0.00750", ...} Job->>BSU: updateCost(msgId, 0.00750) Note over BSU: costStatus=captured, costCapturedAt=now() else Price null (still processing) API-->>Job: {price: null, ...} Job->>BSU: incrementCostRetry(msgId) alt Retries < 3 Job-->>Worker: JobResult::failure (will retry in 1hr) else Max retries reached Job->>BSU: markCostUnknown(msgId, reason) Job-->>Worker: JobResult::success (marked unknown) end else API error API-->>Job: 429/500/etc Job-->>Worker: JobResult::failure (will retry) end ``` ### Secondary Flow: Inbound SMS Cost Capture 1. Customer sends inbound SMS → existing ChatWebhookController handles it 2. ChatWebhookController extracts provider and providerMessageId from payload 3. After saving the inbound chat_message, ChatWebhookController calls SmsUsageTracker to log inbound usage with `smsCategory='inbound'`, `costStatus='pending'` 4. **Vonage path**: Vonage inbound webhook includes `message-price` field → extract and call `updateCost()` immediately → `costStatus='captured'` 5. **Twilio path**: Twilio inbound webhook does NOT include cost → dispatch SmsCostLookupJob with the inbound MessageSid → same async retry pattern as outbound 6. Store attribution: ChatMatchingService (already used by ChatWebhookController) determines typeNum ```mermaid sequenceDiagram participant Customer participant Provider as Twilio/Vonage participant CWC as ChatWebhookController participant SUT as SmsUsageTracker participant Job as SmsCostLookupJob Customer->>Provider: Send SMS to store number Provider->>CWC: POST /api/webhooks/chat-inbound CWC->>CWC: Route to store via ChatMatchingService → typeNum CWC->>CWC: Save inbound chat_message (existing flow) CWC->>SUT: logUsage(typeNum, 'inbound', segments, provider, msgId, true, false) Note over SUT: billingSmsUsage: smsCategory='inbound', costStatus='pending' alt Vonage (price in inbound webhook) CWC->>SUT: updateCost(msgId, webhookPrice) Note over SUT: costStatus='captured' else Twilio (no price in webhook) CWC->>Job: dispatch SmsCostLookupJob(msgId, 'twilio') Note over Job: Same async pattern as outbound end ``` **Key implementation detail**: The inbound cost capture is added to the existing `ChatWebhookController::handleInbound()` method, AFTER the existing message-saving logic. It follows the same never-throw pattern — cost tracking failures are logged but never block inbound message processing. ### Secondary Flow: Demo Store Simulation ```mermaid sequenceDiagram participant Sender as Twilio/VonageSender participant Job as DemoDeliverySimulationJob participant CM as chat_messages participant BSU as billingSmsUsage participant Ably as Ably Note over Sender: store->getDev() === true Sender->>Job: dispatch(msgId, typeNum) with 3-5s delay Note over Job: Sleep random 3-5 seconds Job->>CM: UPDATE delivery_status='delivered' WHERE provider_message_id Job->>BSU: UPDATE deliveryStatus='delivered', costStatus='captured', providerCostUsd=0.00 Job->>Ably: publishDeliveryUpdate(threadId, messageId, 'delivered') ``` ### Error Handling - **Idempotency**: Webhook handlers are idempotent. DeliveryStatusProcessor checks `billingSmsUsage.deliveryUpdatedAt` — if the incoming raw status matches the existing `providerRawStatus` and `deliveryUpdatedAt` is already set, the update is skipped (no-op). This prevents duplicate webhooks from Twilio/Vonage from triggering redundant DB writes, Ably events, or cost lookup jobs. For status transitions, only forward transitions are applied: a "delivered" status cannot be overwritten by a subsequent "sent" status. The `smsWebhookLog` still records every webhook received (for audit), but processing is skipped for duplicates. - **Webhook handler failure**: Always returns 200 OK. Processing failures logged to smsWebhookLog with `processingResult='error'` and full payload. `sms.webhook.processing_failed` event emitted. - **Unmatched providerMessageId**: Logged at WARNING level. smsWebhookLog entry with `processingResult='unmatched'`. No retry (accept minor data loss). - **Store DB connection failure**: Non-fatal. billingSmsUsage (central) still updated. Chat message stays at previous status. Ably event skipped. - **Ably publish failure**: Non-fatal. Silently caught. Status is correct in DB; browser will see it on next page load. - **Twilio cost API failure**: Job returns `JobResult::failure`, TaskEngine retries per config (3 retries, 1hr apart). After exhaustion: `costStatus='unknown'`. - **Vonage cost missing from DLR**: Set `costStatus='unknown'` immediately (Vonage DLR is the only chance). - **Stale costStatus='pending' sweep**: A scheduled `SmsStaleCostSweepJob` runs daily (via TaskEngine cron). It queries `billingSmsUsage WHERE costStatus='pending' AND createdAt < NOW() - INTERVAL 24 HOUR`. For each stale record: if provider is `twilio`, dispatch one final `SmsCostLookupJob`; if provider is `vonage`, mark `costStatus='unknown'` immediately. This catches records where the initial cost lookup job was never dispatched (e.g., delivery webhook never arrived). The sweep job is registered in migration 037_003b. - **Invalid webhook token**: Return 200 OK (don't signal failure to attacker). Log with `processingResult='error'`, message `'Invalid token'`. - **Malformed webhook payload**: Return 200 OK. Log full payload to smsWebhookLog. Emit `sms.webhook.processing_failed` event. ### Complex Logic: Cost/Profit Calculation ``` ALGORITHM: Calculate SMS Profit for Report INPUT: typeNum (optional), period (YYYY-MM), groupBy (store|category) OUTPUT: cost/revenue/profit breakdown 1. QUERY billingSmsUsage for the period: - Filter: billingPeriod = :period - Filter: NOT demo store (JOIN stores WHERE dev != 1) - Filter: typeNum = :typeNum (if specified) - Aggregate: SUM(providerCostUsd) as totalCost, COUNT(*) as messages 2. JOIN billingSmsCategoryConfig for revenue calculation: - For each (typeNum, smsCategory) group: - Get ratePerMessage from billingSmsCategoryConfig - If billable = 1: revenue = messageCount * ratePerMessage - If billable = 0: revenue = 0 (included in base plan) 3. CALCULATE profit per group: - profit = revenue - cost - marginPct = (profit / revenue) * 100 (or 0 if revenue = 0) 4. CALCULATE delivery rate per group: - deliveredPct = (delivered / total) * 100 - failedPct = (failed / total) * 100 - unknownPct = (sent / total) * 100 5. RETURN structured breakdown ``` ## Deployment View ### Single Application Deployment - **Environment**: Standard PHP application server. No new infrastructure required. - **Configuration**: Add 2 new environment variables: ``` TWILIO_WEBHOOK_SECRET= VONAGE_WEBHOOK_SECRET= ``` Generate via: `openssl rand -hex 16` - **Dependencies**: Twilio PHP SDK (already installed). No new composer packages. - **Performance**: Webhook endpoints must respond in <200ms. Cost lookups async via TaskEngine. ### Deployment Sequence 1. **Run migrations** (`php userfrosting/conductor run`) - 037_001: Add cost columns to billingSmsUsage - 037_002: Create smsWebhookLog table - 037_003: Register SmsCostLookupJob definition - 037_004: Register DemoDeliverySimulationJob definition - 037_004b: Register SmsStaleCostSweepJob definition (daily scheduled) - 037_005: Enable workbook_embedded_chat for all stores 2. **Set environment variables** (TWILIO_WEBHOOK_SECRET, VONAGE_WEBHOOK_SECRET) 3. **Deploy application code** (./deploy.sh) 4. **Build CSS** (`php userfrosting/conductor build-css --minify`) 5. **Verify TaskEngine workers** are running and can pick up new job types 6. **Configure Twilio** (one-time): No Twilio console change needed — StatusCallback is per-message in the API call 7. **Verify Vonage** delivery receipt URL update takes effect (URL change is in the send code, not Vonage dashboard) ### Feature Flags - `workbook_embedded_chat`: Auto-enabled by migration 037_005. Required for delivery tracking on buy SMS. - No new feature flag for the SMS Cost report — it's admin-only behind `uri_admin_billing` permission. ### Rollback Strategy - **Database**: Migrations are additive only (new columns, new table). Rollback = deploy previous code. New columns/table are ignored by old code. - **Webhooks**: If delivery webhooks cause issues, set ENV secrets to empty string to disable. Webhooks will return 200 OK but skip processing. - **Cost lookup jobs**: Disable job in `taskJobDefinitions` table (`isActive = 0`). Jobs in queue will be processed but no new ones dispatched. ## Cross-Cutting Concepts ### Pattern Documentation ```yaml # Existing patterns used - pattern: Webhook processing (ChatWebhookController) relevance: HIGH why: "New delivery webhook handlers follow same pattern: always 200 OK, log payload, process async" - pattern: TaskEngine jobs (InvoiceGenerationJob) relevance: HIGH why: "SmsCostLookupJob and DemoDeliverySimulationJob follow BaseJob pattern" - pattern: Ably real-time events (ChatAblyPublisher) relevance: HIGH why: "publishDeliveryUpdate() already exists; delivery events follow same channel/event pattern" - pattern: Billing never blocks SMS (SmsUsageTracker) relevance: CRITICAL why: "All cost/delivery tracking must be wrapped in try/catch. SMS sending is never interrupted." - pattern: Syncfusion EJ2 Grid + Chart (billing/dashboard.html) relevance: MEDIUM why: "SMS Cost report follows same grid initialization, export, and styling patterns" # New patterns created - pattern: Per-provider webhook routing relevance: MEDIUM why: "Separate routes per provider with token validation. Reusable for future webhook types." - pattern: Global WorkbookToast service relevance: LOW why: "Replaces per-module toast implementations. Can be reused for other cross-module notifications." ``` ### System-Wide Patterns - **Security**: URL obscurity with per-provider ENV secrets. No signature validation (v1). Admin-only access to cost/profit data. - **Error Handling**: Webhook endpoints ALWAYS return 200 OK. Processing failures are logged, never surfaced to providers. Non-critical operations (Ably, chat update) wrapped in try/catch. - **Performance**: Webhooks respond in <200ms (log + update central DB only). Expensive operations (Twilio API, store DB updates) can be async. Cost lookups on `low` queue to avoid impacting real-time operations. - **Logging**: All webhook requests logged to `smsWebhookLog` with full payload. Processing failures emit tracking events (`sms.webhook.processing_failed`). Cost lookup retries tracked (`sms.cost.retry_attempted`). ### Implementation Patterns #### Code Patterns and Conventions - **Namespace**: New classes under `BuyerKiosk\SMS\Webhooks\` for webhook handlers, `BuyerKiosk\TaskEngine\Jobs\` for jobs, `BuyerKiosk\Billing\Services\` for report service - **Naming**: camelCase columns, PascalCase classes, camelCase methods (matching existing codebase) - **DI**: Webhook handlers receive dependencies via constructor (consistent with ChatWebhookController) - **Null safety**: All provider fields that might be null use `?string` type hints and null coalescing #### State Management Patterns - **Delivery status lifecycle**: `pending` → `sent` (on API send success) → `delivered`|`failed` (on webhook) - **Cost status lifecycle**: `pending` (on send) → `captured` (on cost data) OR `unknown` (after retries exhausted) - **Dual-write**: Both `billingSmsUsage` (central) and `chat_messages` (store) are updated. Central is source of truth for billing; store DB is source of truth for UI. #### Performance Characteristics - **Webhook throughput**: Target <200ms response time. Log + one central DB UPDATE per webhook. - **Cost lookup**: Async, on `low` queue. Twilio API ~200-500ms per call. Batch not needed at current volume (<10K messages/day total). - **Report queries**: billingSmsUsage indexed on `(typeNum, billingPeriod)`, `(typeNum, deliveryStatus, sentAt)`, `(costStatus, createdAt)`. Sub-second for single-store, <3s for all-stores aggregate. #### Integration Patterns - **Provider abstraction**: DeliveryStatusProcessor doesn't know provider details. TwilioDeliveryHandler and VonageDeliveryHandler parse provider-specific payloads and pass normalized data. - **Fire-and-forget for non-critical**: Ably publish, chat message update, and webhook logging failures don't block the main delivery status update flow. #### Component Structure Pattern ```pseudocode # Webhook Handler Pattern (TwilioDeliveryHandler / VonageDeliveryHandler) COMPONENT: ProviderDeliveryHandler INITIALIZE: processor (DeliveryStatusProcessor), logger, webhookSecret HANDLE_REQUEST: IF token != webhookSecret: log warning, return 200 EXTRACT provider-specific fields from request LOG to smsWebhookLog (raw payload) TRY: result = processor.processDeliveryUpdate(...) IF provider == vonage AND price present: processor.updateCost(...) LOG success to smsWebhookLog CATCH: LOG error to smsWebhookLog RETURN 200 OK (always) ``` #### Data Processing Pattern ```pseudocode # Billing Report Query Pattern FUNCTION: getSmsCostSummary(period, typeNum?, groupBy) VALIDATE: period format, typeNum exists (if provided) QUERY: billingSmsUsage JOIN billingSmsCategoryConfig ON (typeNum, smsCategory) JOIN stores ON typeNum (exclude dev stores) WHERE billingPeriod = :period GROUP BY :groupBy CALCULATE: revenue = SUM(IF billable THEN messageCount * ratePerMessage ELSE 0) CALCULATE: profit = revenue - SUM(providerCostUsd) CALCULATE: marginPct = profit / revenue * 100 RETURN structured result ``` #### Error Handling Pattern ```pseudocode # Webhook Error Pattern FUNCTION: handleWebhookError(provider, providerMessageId, error) CLASSIFY: IF token_invalid: log WARNING, track sms.webhook.processing_failed IF unmatched_message: log WARNING, track sms.webhook.processing_failed IF db_error: log ERROR, track sms.webhook.processing_failed IF ably_error: log WARNING (non-fatal, skip) LOG: Full error context + raw payload to smsWebhookLog RESPOND: 200 OK (always — never signal failure to provider) ``` #### Test Pattern ```pseudocode # Testing approach for delivery tracking TEST_SCENARIO: "Twilio delivered status updates all systems" SETUP: - billingSmsUsage record with providerMessageId='SM123' - chat_messages record with provider_message_id='SM123' - Mock Ably publisher - Mock TaskEngine dispatcher EXECUTE: processor.processDeliveryUpdate('twilio', 'SM123', 'delivered', null, null) VERIFY: - billingSmsUsage.deliveryStatus = 'delivered' - billingSmsUsage.providerRawStatus = 'delivered' - chat_messages.delivery_status = 'delivered' - Ably publishDeliveryUpdate called with correct threadId/messageId - SmsCostLookupJob dispatched with SM123 TEST_SCENARIO: "Webhook with invalid token is silently rejected" SETUP: TWILIO_WEBHOOK_SECRET = 'correct_token' EXECUTE: POST /api/webhooks/twilio-delivery/wrong_token VERIFY: - Response = 200 OK - smsWebhookLog entry with processingResult='error' - No delivery status updates - No jobs dispatched ``` ### Integration Points - **Connection Points**: Webhook routes connect to Slim router. DeliveryStatusProcessor connects to existing SmsUsageTracker and ChatAblyPublisher. SmsCostLookupJob connects to existing TaskEngine infrastructure. SMS Cost report connects to existing Billing module. - **Data Flow**: Provider → webhook → central DB + store DB + Ably → browser. TaskEngine → Twilio API → central DB. - **Events**: `workbook:chat:delivered` (existing Ably event, now populated). Tracking events emitted via `error_log()` for operational monitoring (see Tracking Events Map below). ### Tracking Events Map All 11 PRD-defined tracking events mapped to their emission points: | Event | Emitted By | Emission Point | |-------|-----------|----------------| | `sms.outbound.sent` | `SmsUsageTracker::logUsage()` | After successful provider send, during existing usage logging | | `sms.outbound.delivered` | `DeliveryStatusProcessor::processDeliveryUpdate()` | After mapping status to "delivered" and updating billingSmsUsage | | `sms.outbound.failed` | `DeliveryStatusProcessor::processDeliveryUpdate()` | After mapping status to "failed" and updating billingSmsUsage | | `sms.outbound.cost_captured` | `SmsUsageTracker::updateCost()` | After successfully writing providerCostUsd to billingSmsUsage | | `sms.outbound.cost_unknown` | `SmsUsageTracker::markCostUnknown()` | After marking costStatus='unknown' (max retries or Vonage missing price) | | `sms.inbound.received` | `ChatWebhookController::handleInbound()` | After saving inbound chat_message AND logging inbound usage | | `sms.webhook.received` | `TwilioDeliveryHandler::handle()` / `VonageDeliveryHandler::handle()` | First action after token validation passes (before processing) | | `sms.webhook.processing_failed` | `TwilioDeliveryHandler::handle()` / `VonageDeliveryHandler::handle()` | In catch block when DeliveryStatusProcessor throws or returns error | | `sms.cost.retry_attempted` | `SmsCostLookupJob::handle()` | After incrementCostRetry() when price is null and retries remain | | `sms.delivery_rate.calculated` | `SmsCostReportService::getDeliveryRates()` | When delivery rate KPI is computed (on API request) | | `billing.sms_report.viewed` | `BillingApiController::getSmsCosts()` | At start of API handler, logging userId and filters | **Emission pattern**: All events use `error_log(json_encode(['event' => $eventName, 'data' => [...], 'timestamp' => date('c')]))`. This matches the existing codebase pattern for tracking events. ## Architecture Decisions All decisions were confirmed during pre-SDD alignment (see README.md Decisions Log). - [x] ADR-1 **DB Status Enum Strategy**: Keep 6-state enum in `chat_messages`, map to 3-state in UI and `billingSmsUsage.deliveryStatus` - Rationale: Preserves provider-level granularity for debugging. No schema migration on per-store DBs. - Trade-offs: UI mapping logic needed in both PHP and JS. - User confirmed: Yes (2026-02-11) - [x] ADR-2 **Buy Delivery via ChatBridgeService**: Track buy SMS delivery status through `chat_messages` records created by existing ChatBridgeService - Rationale: ChatBridgeService already bridges buy SMS into chat_messages with `provider_message_id`. No duplicate tracking needed. - Trade-offs: Requires `workbook_embedded_chat` flag ON for all stores (handled by migration 037_005). - User confirmed: Yes (2026-02-11) - [x] ADR-3 **Cost on Existing Table**: Add cost columns to `billingSmsUsage` rather than creating separate table - Rationale: Avoids JOINs in reporting queries. Cost data naturally belongs with usage data. - Trade-offs: Table gets wider (9 new columns). Acceptable for reporting table. - User confirmed: Yes (2026-02-11) - [x] ADR-4 **Separate Provider Webhook Routes**: `/api/webhooks/twilio-delivery/:token` and `/api/webhooks/vonage-delivery/:token` - Rationale: Each provider has different payload format and cost handling. Separate routes = separate handlers = cleaner code. - Trade-offs: Two routes instead of one. Minor — each handler is small and focused. - User confirmed: Yes (2026-02-11) - [x] ADR-5 **TaskEngine for Twilio Cost**: Dispatch SmsCostLookupJob from webhook handler - Rationale: Keeps webhook response under 200ms. TaskEngine handles retries natively (3 retries, 1hr apart). - Trade-offs: Cost data is delayed (seconds to hours). Acceptable — cost is internal-only, not user-facing. - User confirmed: Yes (2026-02-11) - [x] ADR-6 **Per-Provider ENV Secrets**: `TWILIO_WEBHOOK_SECRET` and `VONAGE_WEBHOOK_SECRET` - Rationale: Allows rotating one provider's secret without affecting the other. - Trade-offs: Two ENV vars instead of one. Negligible overhead. - User confirmed: Yes (2026-02-11) - [x] ADR-7 **Store Lookup via Central DB**: Resolve typeNum from `billingSmsUsage.providerMessageId` - Rationale: Central DB query is fast (indexed). Avoids searching all store DBs. - Trade-offs: If billingSmsUsage record doesn't exist yet (race condition), webhook is discarded. - User confirmed: Yes (2026-02-11) - [x] ADR-8 **Auto-Enable Feature Flag**: Migration enables `workbook_embedded_chat` for all stores - Rationale: Delivery tracking depends on ChatBridgeService which requires this flag. Ensures no store is left without tracking. - Trade-offs: Bold — enables a feature for all stores. But flag should already be ON for production stores. - User confirmed: Yes (2026-02-11) - [x] ADR-9 **Ignore Unmatched Webhooks**: Log and discard delivery receipts with no matching `billingSmsUsage` record - Rationale: Simplest approach. Race condition is rare (Vonage DLR faster than send logging). Minor data loss acceptable. - Trade-offs: Some delivery status updates may be lost. Mitigated by fast send logging. - User confirmed: Yes (2026-02-11) - [x] ADR-10 **Syncfusion Grid + Chart Combo**: Stacked bar (cost/revenue) + margin % line overlay - Rationale: Rich financial visualization. Matches Billing module's existing Syncfusion EJ2 usage. - Trade-offs: More frontend complexity than plain table. Worth it for data-driven pricing decisions. - User confirmed: Yes (2026-02-11) - [x] ADR-11 **Global WorkbookToast**: Shared toast service listening to Ably delivery events - Rationale: Delivery failure toasts must work on ANY Workbook page, not just Chat. Global listener is cleanest. - Trade-offs: New shared module. But replaces need for per-module toast implementations. - User confirmed: Yes (2026-02-11) - [x] ADR-12 **Demo Simulation via TaskEngine Job**: Sender dispatches delayed job to simulate delivery - Rationale: Exercises the real delivery status update path (DB update + Ably publish). Realistic UX in demo. - Trade-offs: Requires TaskEngine running in demo environments. Already expected. - User confirmed: Yes (2026-02-11) - [x] ADR-13 **FA6 Native Icon Classes**: Use `fa-solid fa-check` etc. instead of FA4 shim classes - Rationale: Future-proof. FA4 shims may be removed eventually. - Trade-offs: Must update existing chat template icons (minor churn). - User confirmed: Yes (2026-02-11) ## Quality Requirements - **Webhook Response Time**: <200ms p95. Measured by `smsWebhookLog.processingTimeMs`. - **Cost Capture Rate**: >95% of messages have `costStatus='captured'` within 4 hours of sending. Measured by querying `billingSmsUsage` weekly. - **Delivery Visibility**: 100% of outbound messages sent after deployment have a tracked delivery status. Measured by counting records with `deliveryStatus != 'pending'` vs total. - **Report Query Time**: <3 seconds for all-stores aggregate, <1 second for single-store. Measured by API response time. - **Zero SMS Disruption**: No increase in SMS send failure rate after deployment. Measured by comparing `billingSmsUsage.status='failed'` rates before/after. - **Ably Event Delivery**: >99% of delivery status Ably events published successfully. Measured by `ProcessingResult.ablyPublished` success rate. ## Risks and Technical Debt ### Known Technical Issues - **VonageTextSender webhook URL points to non-existent file**: Currently set to `/api/webhooks/vonage-delivery-simple.php`. Will be updated to new Slim route. - **ChatAblyPublisher::publishDeliveryUpdate() never called**: Method exists but has no callers. May have undiscovered bugs until activated. - **Chat message template uses FA4 shim classes**: `fa fa-check-double` etc. Will be updated to FA6 native. ### Technical Debt - **Dual-write delivery status**: Both `billingSmsUsage.deliveryStatus` and `chat_messages.delivery_status` store delivery state. These could diverge if one update fails. Mitigation: central DB (`billingSmsUsage`) is source of truth for reporting; store DB is source of truth for UI. - **Per-module toast implementations**: Chat, TimePunch, and LayoutManager each have their own `showToast()`. WorkbookToast replaces one use case but doesn't consolidate the others. Future: migrate all toasts to WorkbookToast. - **No webhook signature validation**: URL obscurity is v1 approach. Should add Twilio signature validation and Vonage IP allowlisting in future. ### Implementation Gotchas - **PDO named param reuse**: If any query uses the same named parameter twice, it will silently fail with HY093. Use unique param names (e.g., `:providerMessageId1`, `:providerMessageId2`). - **Twilio price is a string**: `$message->price` returns a string like `"-0.00750"`. Must cast to float and `abs()`. Null means price not yet available. - **Vonage DLR can arrive as GET**: When Vonage sends delivery receipts via GET, params are in the query string, not body. Handler must check both. - **billingSmsUsage is in central DB**: Don't try to join with `chat_messages` (which is in per-store DB). Two separate queries. - **ChatBridgeService is static**: `bridgeFromLegacySend()` is a static method. Cannot easily inject DeliveryStatusProcessor. The webhook handler updates chat_messages directly. - **Demo stores return fake provider message ID**: TwilioTextSender returns `'DEMO_' . uniqid()` for demo sends. DemoDeliverySimulationJob uses this to find the record. - **Ably channel name**: The channel is `store:{typeNum}` (not just `{typeNum}` as some older code suggests). Verify by checking `ChatAblySync.subscribeToChannel()`. ## Test Specifications ### Critical Test Scenarios **Scenario 1: Twilio Delivered — Full Happy Path** ```gherkin Given: An outbound SMS was sent via Twilio with message SID "SM123" And: billingSmsUsage record exists with providerMessageId="SM123", typeNum="ou00" And: chat_messages record exists with provider_message_id="SM123" in kiosk_ou00 When: Twilio sends POST to /api/webhooks/twilio-delivery/{TWILIO_WEBHOOK_SECRET} With: MessageSid=SM123, MessageStatus=delivered Then: billingSmsUsage.deliveryStatus = "delivered" And: billingSmsUsage.providerRawStatus = "delivered" And: chat_messages.delivery_status = "delivered" in kiosk_ou00 And: Ably event "workbook:chat:delivered" published on "store:ou00" channel And: SmsCostLookupJob dispatched with payload {providerMessageId: "SM123", provider: "twilio"} And: smsWebhookLog entry created with processingResult="success" And: HTTP response = 200 OK ``` **Scenario 2: Vonage Failed — With Cost and Error** ```gherkin Given: An outbound SMS was sent via Vonage with messageId "MSG456" And: billingSmsUsage record exists with providerMessageId="MSG456", typeNum="pa00" When: Vonage sends GET to /api/webhooks/vonage-delivery/{VONAGE_WEBHOOK_SECRET} With: messageId=MSG456, status=failed, err-code=6, price=0.0068 Then: billingSmsUsage.deliveryStatus = "failed" And: billingSmsUsage.deliveryErrorCode = "6" And: billingSmsUsage.deliveryErrorMessage = "Anti-spam rejection" And: billingSmsUsage.providerCostUsd = 0.0068 And: billingSmsUsage.costStatus = "captured" And: chat_messages.delivery_status = "failed" in kiosk_pa00 And: chat_messages.delivery_error = "Anti-spam rejection" And: Ably event includes errorReason = "Anti-spam rejection" And: No SmsCostLookupJob dispatched (cost already captured) And: HTTP response = 200 OK ``` **Scenario 3: Invalid Webhook Token** ```gherkin Given: TWILIO_WEBHOOK_SECRET = "abc123" When: POST to /api/webhooks/twilio-delivery/wrong_token Then: HTTP response = 200 OK And: smsWebhookLog entry with processingResult="error", errorMessage="Invalid token" And: No billingSmsUsage updates And: No chat_messages updates And: No Ably events And: No jobs dispatched ``` **Scenario 4: Unmatched Provider Message ID** ```gherkin Given: No billingSmsUsage record with providerMessageId="SM_ORPHAN" When: Twilio sends delivered webhook for MessageSid="SM_ORPHAN" Then: HTTP response = 200 OK And: smsWebhookLog entry with processingResult="unmatched" And: No database updates And: No Ably events ``` **Scenario 5: Twilio Cost Lookup — Price Available** ```gherkin Given: SmsCostLookupJob dispatched for "SM123" And: Twilio API returns price="-0.00750" for SM123 When: Job executes Then: billingSmsUsage.providerCostUsd = 0.00750 And: billingSmsUsage.costStatus = "captured" And: billingSmsUsage.costCapturedAt = now() And: Job returns JobResult::success ``` **Scenario 6: Twilio Cost Lookup — Price Unavailable, Retries Exhausted** ```gherkin Given: SmsCostLookupJob dispatched for "SM789" And: Twilio API returns price=null for SM789 And: costRetryCount is already 2 (this is attempt 3) When: Job executes Then: billingSmsUsage.costStatus = "unknown" And: Job returns JobResult::success (marked unknown, no more retries) ``` **Scenario 7: Demo Store Simulation** ```gherkin Given: Store "dm00" has dev=1 (demo store) And: TwilioTextSender sends a message in demo mode When: DemoDeliverySimulationJob executes after 3-5 second delay Then: chat_messages.delivery_status = "delivered" And: billingSmsUsage.deliveryStatus = "delivered" And: billingSmsUsage.providerCostUsd = 0.00 And: billingSmsUsage.costStatus = "captured" And: Ably delivery event published ``` **Scenario 8: SMS Cost Report — All Stores** ```gherkin Given: Admin user with uri_admin_billing permission And: billingSmsUsage has data for period 2026-02 When: GET /api/billing/sms-costs?period=2026-02&groupBy=store Then: Response includes: - summary.totalCost (sum of all providerCostUsd) - summary.totalRevenue (sum of messageCount * ratePerMessage for billable categories) - summary.profitMargin (calculated percentage) - breakdown[] with per-store data - Demo stores excluded from all calculations ``` ### Test Coverage Requirements - **Business Logic**: DeliveryStatusMapper (all provider status mappings), SmsCostReportService (profit calculations), cost/revenue aggregation, demo store exclusion - **Webhook Handlers**: Token validation, payload parsing (both GET and POST for Vonage), idempotency, error handling - **Integration Points**: billingSmsUsage updates, chat_messages updates, Ably publish, TaskEngine job dispatch - **Edge Cases**: Unmatched messages, null cost from Twilio, Vonage GET vs POST, demo store detection, concurrent webhook processing - **Error Recovery**: DB connection failures, Ably publish failures, Twilio API timeouts, malformed payloads ### SMS Cost Report UI Rendering Notes **Base Plan Label**: Categories where `includedInBasePlan=true` (i.e., `billable=0`) display a `Base Plan` badge next to the category name in the Syncfusion Grid. Revenue column shows "$0.00" for these rows. Tooltip explains: "Included in store's base billing rate — no additional charge per message." **Demo Store Badge**: The `SmsCostReportService` query excludes demo stores (`stores.dev != 1`) by default. If an admin-override filter is ever added to include demo stores, the API response would include an `isDemo: boolean` flag on each row, and the UI would render a `Demo` badge with italicized row text. For v1, demo stores are simply excluded from all report data. **Delivery Rate Flagging**: Stores with `deliveryRate < 90%` in the KPI grid render with `text-danger` class on the percentage cell and a `` icon. ## Extension Points (Could Have Features) ### Feature 10: Delivery Failure Retry (Future) **Where it plugs in**: Chat UI message template. Failed messages would show a "Retry" button. On click, frontend calls existing `POST /api/{typeNum}/chat/threads/{threadId}/messages` with the same content. New `chat_messages` record created (preserving original failed record). DeliveryStatusProcessor handles the new message's delivery lifecycle independently. **Required additions**: Frontend "Retry" button in message bubble. API call to resend. No backend changes needed — existing send flow handles it. ### Feature 11: Cost Trend Charts (Future) **Where it plugs in**: The `GET /api/billing/sms-costs/trend` endpoint already returns `periods[]`, `costSeries[]`, `revenueSeries[]`, `marginSeries[]`. A future enhancement would add a Syncfusion Chart component to the SMS Cost report page consuming this endpoint. The chart would show daily/weekly breakdown in addition to the monthly view. **Required additions**: Frontend chart component. Additional API endpoint for daily/weekly granularity. No schema changes. --- ## Glossary ### Domain Terms | Term | Definition | Context | |------|------------|---------| | Delivery Receipt (DLR) | Notification from SMS provider confirming message delivery status | Received via webhook from Twilio/Vonage | | Segment | A single 160-char (GSM-7) or 70-char (UCS-2) unit of an SMS message | Providers charge per segment; a long message = multiple segments | | Provider Cost | The actual USD amount charged by Twilio/Vonage for sending or receiving a message | Stored in billingSmsUsage.providerCostUsd | | Profit Margin | (Revenue - Cost) / Revenue * 100 | Per-store, per-category, or aggregate | | Base Plan | SMS categories included in a store's base billing rate (billable=0) | Show cost but $0 revenue in reports | | Cost Capture Rate | Percentage of messages with known provider cost (costStatus='captured') | Target: >95% within 4 hours | | Delivery Rate | Percentage of messages confirmed delivered (vs failed or unknown) | Per-store KPI; flag if <90% | ### Technical Terms | Term | Definition | Context | |------|------------|---------| | StatusCallback | Twilio parameter that specifies a URL to receive delivery status webhooks | Added to messages->create() call | | Message SID | Twilio's unique identifier for a message (format: SM + 32 hex chars) | Used for cost lookup via Message Resource API | | DLR callback | Vonage's delivery receipt webhook, sent to the URL specified in the send request | Includes status, price, and error code | | costStatus | Lifecycle state of cost data: pending → captured OR unknown | Drives retry logic and reporting accuracy | | providerRawStatus | The exact status string from the provider webhook (e.g., "delivered", "undelivered") | Preserved for debugging; mapped to 3-state for display | ### API/Interface Terms | Term | Definition | Context | |------|------------|---------| | Webhook Token | Secret string embedded in the webhook URL path for authentication | Per-provider ENV vars (TWILIO_WEBHOOK_SECRET, VONAGE_WEBHOOK_SECRET) | | ProcessingResult | Value object returned by DeliveryStatusProcessor | Contains success/failure, mapped status, and what was updated | | MappedStatus | Value object with displayStatus (3-state), rawStatus, and isFinal flag | Drives UI rendering and cost job dispatch logic | | billingPeriod | YYYY-MM format string identifying a billing month | Used for aggregation in cost reports |