# Specification: 037-sms-delivery-cost-tracking

## Status

| Field | Value |
|-------|-------|
| **Created** | 2026-02-11 |
| **Current Phase** | Implementation Plan Complete, Ready for Implementation |
| **Last Updated** | 2026-02-11 |

## Documents

| Document | Status | Notes |
|----------|--------|-------|
| product-requirements.md | completed | Reviewed by Codex, all findings resolved |
| solution-design.md | completed | All 13 ADRs confirmed, Codex-reviewed, 8/8 findings resolved |
| implementation-plan.md | completed | 7 phases, ~200 tasks, full TDD structure |

**Status values**: `pending` | `in_progress` | `completed` | `skipped`

## Decisions Log

| Date | Decision | Rationale |
|------|----------|-----------|
| 2026-02-11 | Start with PRD | Full requirements discovery before design |
| 2026-02-11 | All SMS paths in scope | Both TextMessageService and Chat, NOT legacy loyalty |
| 2026-02-11 | Provider actual costs | Query Twilio API + Vonage delivery receipts for real cost data |
| 2026-02-11 | Delivery status: Completed Buys + Chat | User-facing delivery status, costs are internal-only |
| 2026-02-11 | Flat rate pricing model | Stores charged per billingSmsCategoryConfig, we track our actual cost for profit |
| 2026-02-11 | Real-time via Ably | Delivery status updates pushed live to Chat UI |
| 2026-02-11 | Toast on all SPA pages | Failed delivery toast notifications on any Workbook page |
| 2026-02-11 | No webhook signature validation | URL obscurity sufficient for v1 |
| 2026-02-11 | Forward-only, no backfill | Track from deployment onward |
| 2026-02-11 | US/Canada only | No international rate handling |
| 2026-02-11 | Simulate for demo stores | Auto-set "Delivered" for demo, exclude from cost reporting |
| 2026-02-11 | Per-store delivery rate KPI | Flag stores below 90% delivery rate |
| 2026-02-11 | SMS Cost report inside Billing module | Not standalone — lives in existing Billing section |
| 2026-02-11 | No timeout on "Sent" status | If no receipt, stays "Sent" forever |
| 2026-02-11 | Keep 6-state DB enum, 3-state UI | chat_messages keeps full enum (pending/queued/sent/delivered/failed/undelivered); UI maps to Sent/Delivered/Failed |
| 2026-02-11 | Buy delivery via ChatBridgeService | Link Completed Buys delivery status through existing chat_messages records created by ChatBridgeService |
| 2026-02-11 | Cost columns on billingSmsUsage | Add providerCostUsd, costStatus, costCapturedAt to existing table — no separate cost table |
| 2026-02-11 | Separate webhook routes per provider | POST /api/webhooks/twilio-delivery/:token and /api/webhooks/vonage-delivery/:token |
| 2026-02-11 | TaskEngine job for Twilio cost lookup | Webhook dispatches SmsCostLookupJob per message; keeps webhooks fast |
| 2026-02-11 | Syncfusion Grid + Chart for SMS report | Stacked bar (cost/revenue) + line (margin %) chart with Grid data table below |
| 2026-02-11 | Global WorkbookToast service | Shared toast manager listening to Ably delivery events; works on any Workbook SPA page |
| 2026-02-11 | Per-provider ENV webhook secrets | TWILIO_WEBHOOK_SECRET and VONAGE_WEBHOOK_SECRET in .env |
| 2026-02-11 | Store lookup via billingSmsUsage | Webhook resolves typeNum by querying central billingSmsUsage.providerMessageId |
| 2026-02-11 | Auto-enable workbook_embedded_chat | Migration enables feature flag for all stores on 037 deploy |
| 2026-02-11 | Support GET + POST for Vonage DLR | Handle both HTTP methods for Vonage delivery receipts |
| 2026-02-11 | FA6 native icon classes | Use fa-solid fa-check etc. instead of FA4 shim classes |
| 2026-02-11 | Ignore unmatched webhook receipts | If providerMessageId not found in billingSmsUsage, log and discard (accept minor data loss for simplicity) |
| 2026-02-11 | Stacked bar + margin line chart | Monthly cost/revenue stacked bars with overlaid profit margin % line |
| 2026-02-11 | Demo sim via TaskEngine delayed job | TwilioTextSender/VonageTextSender dispatch delayed job to set delivery_status='delivered' in demo mode |
| 2026-02-11 | SDD scope: Must + Should + extension points | Full design for Must/Should Have; extension points noted for Could Have features |
| 2026-02-11 | Implementation Plan created | 7 phases with TDD structure, ~200 tasks, 5 parallel work groups |

## Codex PRD Review (2026-02-11)

**Blockers Resolved:**
1. **Inbound Twilio cost flow contradiction** — Fixed Feature 5 to clarify that Twilio inbound webhooks do NOT include cost; requires async API lookup using message SID (same pattern as outbound)
2. **Delivery status mapping undefined** — Added explicit Twilio/Vonage status → 3-state mapping table in Feature 1 acceptance criteria

**High Priority Resolved:**
3. **No webhook failure monitoring** — Added Feature 1a (Webhook Processing Monitoring) with logging, error tracking, and processing failure events

**Medium Priority Resolved:**
4. **Subjective acceptance criteria** — Tightened UI specs: specific icon descriptions (gray check, green double-check, red X), color token references, toast content format, error translation examples
5. **Cost retry not formalized** — Added explicit retry requirement to Feature 4: "up to 3 retries over 4-hour window"
6. **Billing module dependency** — Added fallback assumption: "deploy behind admin-only feature flag if spec-036 not ready"

**Additional Improvements:**
- Added TaskEngine as explicit constraint/dependency
- Added measurement windows to KPIs (4h for cost capture, 5min for delivery status)
- Added `sms.webhook.processing_failed` and `sms.cost.retry_attempted` tracking events
- Changed "As the system" user stories to "As a platform operator"

**Review Result:** All blockers resolved. PRD is ready for SDD.

## Codex SDD Review (2026-02-11)

**Blockers Resolved:**
1. **Inbound cost capture flow not designed** — Added complete "Secondary Flow: Inbound SMS Cost Capture" section with sequence diagram. ChatWebhookController modification documented.
2. **`inbound` category missing from smsCategory enum** — Added ALTER COLUMN to migration 037_001. Added ChatWebhookController to modified files list.

**High Priority Resolved:**
3. **Ably payload missing customerName/typeNum for toast** — Expanded Ably payload spec to include customerName, typeNum, errorReason. Updated DeliveryStatusProcessor example code to fetch customer name.
4. **Idempotency not defined** — Added idempotency strategy: check deliveryUpdatedAt + providerRawStatus before updating. Only forward status transitions allowed. smsWebhookLog still records all webhooks.
5. **PRD tracking events not mapped to emission points** — Added complete Tracking Events Map table mapping all 11 PRD events to specific classes and methods.

**Medium Priority Resolved:**
6. **"Base Plan" label not in API/UI spec** — Added categoryLabel field to API response. Added UI rendering note for Base Plan badge (`badge bg-secondary`).
7. **Demo stores not marked as simulated in reports** — Documented that demo stores are excluded by default via `stores.dev != 1`. Added UI rendering note for demo badge if admin override ever added.
8. **Stale costStatus='pending' has no sweep** — Added SmsStaleCostSweepJob (daily at 6am), migration 037_004b, entity model, and deployment step. Dispatches final cost lookup for Twilio, marks unknown for Vonage.

**Review Result:** All 8 findings resolved. SDD is ready for Implementation Plan.

## Codex PLAN Review (2026-02-11)

**Blockers:** None found.

**Important Findings (All Resolved):**
1. **ChatApiController provider_message_id not in plan** — Added T3B.1.5, T3B.2.7-2.8, T3B.3.5 to verify/modify ChatApiController to persist provider_message_id and delivery_status='sent' on outbound sends
2. **PRD Feature 1a webhook counts not queryable** — Added T6.2.12 and T6.3.5b for `getWebhookHealth()` method on SmsCostReportService to query smsWebhookLog success/failure counts
3. **Retry window not explicit** — Added T4.2.2.3 to verify migration 037_003 job definition has correct retry config (maxRetries: 3, retryDelaySeconds: 3600)
4. **T3C/T4 dependency ordering** — Fixed: T4 now depends on T1 (not T2), T3C now depends on T2+T4 (needs SmsCostLookupJob class before dispatching it)
5. **T5B.3.4 too vague** — Made explicit: JOIN chat_messages on provider_message_id via ChatBridgeService reference for delivery_status field

**Enhancements (All Applied):**
6. **Definition of Done per phase** — Added DoD to all 7 phases + integration checkpoint
7. **Mid-plan integration checkpoint** — Added T4.5b after T3A/T3B/T4, before UI work, to verify core delivery + cost pipeline end-to-end
8. **Updated dependency graph** — Revised phase ordering to reflect correct sequencing

**Review Result:** All 5 important findings resolved. 3 enhancements applied. Plan is ready for implementation.

## Context

Modify Vonage/Twilio APIs and sending modules to receive delivery status callbacks and cost data for every SMS message sent and received through the system. Currently:

- **Vonage**: Configures delivery receipt URL but webhook endpoint is NOT implemented
- **Twilio**: No delivery status callback configured
- **Cost tracking**: `billingSmsUsage` table tracks segment counts and categories, but NOT actual provider costs
- **Chat system**: `chat_messages.delivery_status` column exists but never gets updated from provider callbacks
- **Legacy sends**: Bridge into chat system via `ChatBridgeService` but delivery tracking stops at "sent"

Key files:
- `userfrosting/src/BuyerKiosk/SMS/TextMessageService/TwilioTextSender.php`
- `userfrosting/src/BuyerKiosk/SMS/TextMessageService/VonageTextSender.php`
- `userfrosting/src/BuyerKiosk/SMS/TextMessageService/TextMessageService.php`
- `userfrosting/src/BuyerKiosk/Chat/Controllers/ChatWebhookController.php`
- `userfrosting/src/BuyerKiosk/Billing/Services/SmsUsageTracker.php`
- `userfrosting/routes/chat/webhooks.php`
- `userfrosting/routes/groups/sms.php`

---
*This file is managed by the specification-management skill.*