System context (DB‑centric)
[Vendor Browser SPA] [Rider (WhatsApp + OTP)]
| |
| JSON (GET/POST) | Media, messages
v v
+----------------------+ USSD (POST /ussd) +-------------------------+
| Flask Backend |<----------------------| Telco/CPaaS Aggregator |
| (standalone_app_*) |---------------------> | (dials *120*2477*77*) |
+----------+-----------+ Webhooks/OTP/Media +-----------+-------------+
| |
| SQLAlchemy / File fallback |
v v
+--------------------------------------------+
| PostgreSQL Database |
| - products (inventory) |
| - ussd_bundles (prefix/suffix) |
| - orders |
| - delivery_proof (OTP, POD photos) |
| - notifications (sms/wa) |
| - riders (optional) |
+--------------------------------------------+
How the database works
- System of record: PostgreSQL is the source of truth for inventory, bundles, orders, OTP/POD and notifications.
- Inventory: Products are created/updated via API and stored in products; the dashboard reads them to render stock.
- USSD bundles: Vendor prefix + product code are mapped in ussd_bundles, letting the USSD endpoint resolve dialed codes.
- Orders: When a customer confirms checkout on USSD, an entry is written to orders with items, totals and status.
- OTP scaffold: The first delivery OTP is generated and persisted in delivery_proof with otp_created_at and otp_attempts.
- Verification: Verify compares the provided OTP against delivery_proof, enforcing TTL and attempt limits before marking delivered.
- Resend: Resend issues a new OTP, updates delivery_proof, and triggers notifications; the dashboard feed updates accordingly.
- POD photos: WhatsApp media webhooks attach photo URLs to delivery_proof.pod_photos; the UI shows proxied thumbnails.
- Notifications: Outbound WhatsApp/SMS are recorded in notifications and merged into the Delivery Proof feed.
- Riders: Optional tables keep rider registration and delivery metrics for assignment and analytics.
- File fallback: If DB is unavailable, critical flows persist to files; on reconnect, DB-first code paths take precedence.
- Consistency: All dashboard analytics and lists read from DB-backed views/joins to reflect the latest writes.
USSD Phone Simulator
Try a full USSD flow:
*120*2477*77*2025*25#
Tip: Use the keypad, then press Dial. Follow the on‑screen steps (Confirm → Payment).
Vendor: inventory and USSD code registration
- GET /portal → serves SPA
- Inventory: GET /api/vendor/products • POST /api/vendor/products • PUT /api/vendor/products/<product_id>
- USSD code preview: GET /api/ussd/code?vendor=V&code=C
- Register code mapping: POST /api/ussd/bundles {vendor, code, name, price, items}
- List bundles: GET /api/ussd/bundles?vendor=V
Customer: USSD dial → order
- Dial pattern: *Network*CPaaS*Platform*Vendor*Product#
- Aggregator: POST /ussd (form: sessionId, phoneNumber, text)
- Backend resolves vendor/code via DB (ussd_bundles), creates order, persists OTP (delivery_proof)
Rider: Delivery Proof (OTP + POD)
- Verify OTP: POST /api/delivery/verify-otp {order_number, otp}
- Resend OTP: POST /api/delivery/resend-otp {order_number}
- Feed for dashboard: GET /api/delivery-proof/feed
- OTP config for TTL/attempts: GET /api/config
- POD thumbnails: GET /api/media/proxy?url=...
Analytics & reporting
- Orders: GET /api/orders
- Dashboard stats: GET /api/dashboard/stats