SOS Architecture & Data Model

System Overview

Seller SOS chạy như một Cloudflare Workers application độc lập sử dụng framework Hono, kết nối đến Supabase (PostgreSQL). Module nhận dữ liệu từ nhiều nguồn (OMS, WMS, Helpdesk, Accounting), tính toán điểm SOS, và dispatch webhook events đến các hệ thống subscriber.

Architecture Diagram

┌─────────────────────────────────────────────────────────────────────┐
│                    External Systems (Data Sources)                    │
│                                                                       │
│  ┌─────────┐  ┌─────────┐  ┌──────────┐  ┌───────────────────────┐ │
│  │   OMS   │  │   WMS   │  │ Helpdesk │  │  Accounting System    │ │
│  │ Orders  │  │Inventory│  │ Tickets  │  │  Payments & Revenue   │ │
│  └────┬────┘  └────┬────┘  └────┬─────┘  └──────────┬────────────┘ │
│       │            │            │                     │              │
│       └────────────┴────────────┴─────────────────────┘              │
│                            │ POST /api/sync/*                        │
│                            ▼                                         │
│  ┌───────────────────────────────────────────────────────────────┐  │
│  │              Boxme SOS (Cloudflare Workers + Hono)             │  │
│  │                                                                 │  │
│  │  ┌──────────┐  ┌────────────────┐  ┌────────────────────────┐ │  │
│  │  │ Auth &   │  │ Scoring Engine │  │ Webhook Dispatcher     │ │  │
│  │  │ RBAC     │  │ P/O/T/F/I calc │  │ HMAC-SHA256 signed     │ │  │
│  │  └──────────┘  └────────────────┘  └──────────┬─────────────┘ │  │
│  │                                                │               │  │
│  └────────────────────────────────────────────────┼───────────────┘  │
│                            │                       │                  │
│                            ▼                       ▼                  │
│  ┌─────────────────────────────┐  ┌──────────────────────────────┐  │
│  │     Supabase (PostgreSQL)   │  │   Webhook Subscribers        │  │
│  │     ~25 SOS tables          │  │   (WMS, OMS, BI, Alerts)     │  │
│  └─────────────────────────────┘  └──────────────────────────────┘  │
└─────────────────────────────────────────────────────────────────────┘

Data Flow

1. External systems push data → POST /api/sync/*
   ├── /sync/orders        → sos_order_operation_raw
   ├── /sync/forecasts     → seller_forecast_submission
   ├── /sync/campaign-actuals → campaign_actual_result
   ├── /sync/tickets       → sos_ticket_sla_raw
   ├── /sync/payments      → sos_payment_raw
   ├── /sync/inventory     → sos_inventory_health_raw
   └── /sync/revenue       → am_portfolio_period_score

2. Admin triggers scoring → POST /api/sos/monthly/calculate
   └── Scoring Engine reads raw data + config tables
       ├── calcPScore() → sos_planning_raw + sos_p_score_config
       ├── calcOScore() → sos_order_operation_raw + sos_o_score_config
       ├── calcTScore() → sos_ticket_sla_raw + sos_t_score_config
       ├── calcFScore() → sos_payment_raw + sos_f_score_config
       └── calcIScore() → sos_inventory_health_raw + sos_i_score_config
   └── Results → seller_sos_period_score (status: draft)

3. Admin finalizes → PATCH /api/sos/monthly/:id/finalize
   └── Webhook Dispatcher → seller.score_finalized event
   └── If tier changed → seller.tier_changed event

4. Surcharges/Appeals → Webhook events dispatched automatically

Project Structure

seller-sos/
├── src/
│   ├── index.tsx              # Main entry, static assets, SPA shell
│   ├── routes/
│   │   ├── api.ts             # Route aggregator (mounts all sub-routes)
│   │   ├── auth.ts            # Login, session verify, password update
│   │   ├── sellers.ts         # Seller CRUD, exceptions, AMs
│   │   ├── campaigns.ts       # Campaign CRUD, forecasts, actuals
│   │   ├── scores.ts          # SOS score endpoints, finalize, calculate
│   │   ├── appeals.ts         # Appeal CRUD, 2-level review
│   │   ├── webhooks.ts        # Webhook subscription management
│   │   ├── dashboard.ts       # Dashboard statistics
│   │   ├── surcharges.ts      # Surcharge records
│   │   ├── sync.ts            # Data sync push endpoints
│   │   ├── config.ts          # 11 config table CRUD (factory pattern)
│   │   └── am.ts              # AM portfolio scores
│   ├── lib/
│   │   ├── scoring.ts         # P/O/T/F/I scoring engine
│   │   ├── webhook-dispatcher.ts  # HMAC signing + retry delivery
│   │   ├── supabase.ts        # Supabase client factory
│   │   └── d1_wrapper.ts      # D1-compatible wrapper over Supabase
│   └── types/
│       └── index.ts           # TypeScript type definitions
├── migrations/
│   ├── 0001_sos_schema.sql    # Full schema (~25 tables)
│   └── 0002_sample_data.sql   # Sample data for testing
├── public/static/             # Frontend assets
├── tests/                     # Vitest test suites
├── wrangler.jsonc             # Cloudflare Workers config
└── vite.config.ts             # Build config

Database Tables

Seller Core

Table	Description
seller	Master data: seller_code, company_name, contract, warehouse, AM
account_manager	AM profiles linked to sellers
campaign	Sales campaigns (major/mini) with date ranges
seller_forecast_submission	Seller forecast volumes per campaign
campaign_actual_result	Actual order volumes post-campaign

Score Data (Raw)

Table	Source	Description
sos_planning_raw	Scoring Engine	P-Score aggregated planning data
sos_order_operation_raw	OMS Sync	O-Score order timing raw data
sos_ticket_sla_raw	Helpdesk Sync	T-Score ticket response metrics
sos_payment_raw	Accounting Sync	F-Score payment timeliness
sos_inventory_health_raw	WMS Sync	I-Score inventory aging data

Score Results

Table	Description
seller_sos_period_score	Monthly SOS scores (P/O/T/F/I raw + weighted + total)
seller_score_exception	Score overrides for specific sellers/components
seller_sos_appeal	Appeal records with 2-level review workflow
seller_surcharge_record	Surcharge records triggered by SOS rules

Configuration

Table	Description
sos_score_weight_config	Component weight percentages
sos_tier_config	Tier definitions (name, score range, color, benefits)
sos_p_score_config	P-Score calculation parameters
sos_o_score_config	O-Score thresholds and penalties
sos_t_score_config	T-Score SLA tier definitions
sos_f_score_config	F-Score payment tier definitions
sos_i_score_config	I-Score aging thresholds
sos_surcharge_config	Surcharge trigger rules
sos_am_bonus_penalty_config	AM bonus/penalty rules based on SOS delta
am_kpi_weight_config	AM KPI component weights
seller_grace_period_config	Grace period settings for new sellers
frappe_helpdesk_config	Frappe Helpdesk integration settings

Webhook

Table	Description
webhook_subscription	Active webhook subscribers
webhook_delivery_log	Delivery attempts with status and response

AM Performance

Table	Description
am_portfolio_period_score	AM monthly KPI scores

Tech Stack

Layer	Technology
Runtime	Cloudflare Workers (Edge)
Framework	Hono v4.x
Database	Supabase (PostgreSQL)
Frontend	Vanilla JS + CSS
Build	Vite + @hono/vite-build
Testing	Vitest
Deploy	Wrangler CLI + Cloudflare Pages