Sales-CSKH Debt Follow-up & Handover — Dev Spec

v2.0 — 15/05/2026

Thay đổi	Section	Ảnh hưởng
C3 FORMULA-003 rewrite — SQL canonical với half-open range + exclude wallet	C3 FORMULA-003	BE, DBA
C3 FORMULA-006B mới — customer-level aging CTE rollup	C3 FORMULA-006B	BE, DBA
C3 FORMULA-007 mới — service representative SQL `DISTINCT ON`	C3 FORMULA-007	BE
C3 FORMULA-008 mới — weighted % tham gia multi-order + migration plan	C3 FORMULA-008	BE, DBA
C5.1 action `recordDebtFollowupContact` chi tiết hơn — flow update `last_contacted_at`	C5.1 Existing actions	BE
C5.2 query mới `get_priority_debt_banner` (Ref DEC-033 as-of NOW)	C5.2 Existing queries + C5.3 Delta	BE
C5.5 API param `view_role` mới cho KPI × Role canonical (Ref DEC-024)	C5.5 API view_role	BE, FE
C6.1 cron `daily_debt_alert` đổi từ 17:05 → 7h00 sáng (Ref DEC-031, PD-002 resolved) + SQL trigger `>=` + dedupe + 4 re-trigger rules	C6.1 Daily Debt Alert	BE, DevOps
C7.1/C7.2 mandatory 4 critical indexes (Ref DEC-034) trên `order` table	C7.1 Verify + C7.2 Conditional ALTER	DBA
C9.2 capacity model + gate Day-30 cho snapshot (Ref DEC-034)	C9.2 Capacity Model	TL, DBA, Ops
C11.4 task DB-05 (Phase 2 conditional snapshot)	C11.4 DB Tasks	DBA

Canonical Source of Truth: SOURCE_OF_TRUTH.md — DEC-001: codebase Diva là ground truth; HTML intent v4.12 là target sau hardening pass. Slug: sales-cskh-debt-handover · Version: v2.0 hardening · Updated: 2026-05-15 Audience: Backend devs (Go) · Frontend devs (Vue/TS, Flutter) · DBA · DevOps Spec Index: prd.md · ui-spec.md · qa-test-plan.md · go-live-checklist.md · handoff.md · prod-issues.mdEvidence pack: EVIDENCE_PACK.md chứa quote SQL/Go/TS với line numbers cụ thể.

ⓘ Cách đọc Dev Spec này

Đây là hardening dev spec — không phải greenfield. Mỗi section có 2 lớp:

Existing (codebase) — file/migration/action thực tế trong Diva hiện tại
Delta — phần cần extend/fix để khớp HTML intent v4.12

Quy tắc DEC-001: Khi spec mâu thuẫn với codebase → codebase thắng. Spec phải sửa, không phải code.

C1. Scope kỹ thuật

C1.1 In scope Day-1

Hạng mục	Codebase status	Hành động hardening
Tab Hiệu suất tư vấn (`/dm/debt/consulting-performance`)	🟡 Component sẵn	Bổ sung KPI cards FR-003/FR-004 (Khách mua trong kỳ + Tỉ lệ chuyển đổi); align label với HTML intent
Tab Công nợ (`/dm/debt/debt`)	🟡 Component sẵn	Bổ sung Scope chip vai trò + Summary banner + Daily Focus top 5 (FR-005, FR-007)
Tab Thống kê (`/dm/debt/statistics`)	🟡 Component sẵn	Bổ sung Leaderboard NV + Drawer chi tiết NV 720px (FR-014…020)
Cài đặt cảnh báo (`DebtManagerSetting.tsx`)	🟢 Form đầy đủ	Validate UI hardening: branch override view + lịch sử thay đổi (FR-008)
Daily alert scheduler	🟢 `scheduler/daily_debt_alert.go`	Runbook + monitor + alert delivery rate ≥ 95% (FR-009)
Drawer lịch nhắc + Popup tạo lịch	🟢 Component sẵn	Verify permission + filter scope (FR-013)
Wizard handover SCR-05→08	🟢 9 components sẵn	Verify limit 200 khách/lần + rollback 24h enforcement (FR-010, FR-011)
Export XLSX	🟢 `XExcel` + `XTable`	Verify column mapping theo B-Export spec (FR-012)
Mobile MOB-01..05	🟡 Module `debt_management` sẵn	Extend KPI layout + section collapsible + notification types
Hasura table permissions harden	🟡 Tables sẵn	Apply least-data per DEC-012 cho `debt_alert_*`, `customer_handover_log`, `debt_contact_log`, `debt_followup_task`

C1.2 Out of scope Day-1

Tạo bảng debt_daily_snapshot materialized (DEC-013 — chỉ khi capacity gate fail; capacity test gắn trong NFR)
AI auto-assign debt owner / SLA escalation multi-level / CRM campaign automation
API mobile riêng (reuse GraphQL/Hasura hiện tại — DEC-009)

C2. Impact Analysis

C2.1 Database (PostgreSQL via Hasura)

Object	Vai trò	File evidence	Day-1 action
`order`	Cột `debt_owner_id` (TEXT FK → `ecommerce_user.id`)	migration `1772851084921_run_sql_migration`	🟢 Reuse — không sửa
`order_service`	Per-service Sale assignment + `is_primary_owner`	migrations ecommerce	🟡 Verify field exists; nếu chưa, alter để support FR-002 v4.12
`debt_alert_config`	Config global/branch ngưỡng + giờ gửi	`1773718673920_add_debt_alert_config_log`	🟢 Reuse
`debt_alert_config_log`	Audit thay đổi config	`1773718673920_*`	🟢 Reuse
`debt_alert_log`	Log gửi alert + dedupe key	`pkg/store/debt.go:52-65`	🟢 Reuse
`debt_alert_notification_schedule`	Pipeline pending → sent	`1775791888659_add_debt_alert_notification_schedule`	🟢 Reuse
`customer_handover_log`	Audit + snapshot + rollback	`1772851084921_run_sql_migration:33`	🟢 Reuse
`debt_contact_log`	Log liên hệ khách	`pkg/store/debt.go:106-123`	🟢 Reuse
`debt_followup_task`	Task follow-up	`1772851084921_run_sql_migration:63`	🟢 Reuse
`debt_followup_notification_schedule`	Lịch gửi notification cho task	`1775637198503_add_debt_followup_notification_schedule`	🟢 Reuse
`debt_order_item_snapshot`	Per-item debt tracking	`1774509789555_add_debt_order_item_snapshot_table`	🟢 Reuse — phục vụ FORMULA-005
`ecommerce_user.last_contacted_at` (cột)	Denormalized "cuộc gọi gần nhất" — được app mobile cập nhật qua action `recordDebtFollowupContact` (đồng thời INSERT `debt_contact_log` + UPDATE `last_contacted_at`)	`pkg/store/ecommerce_user.go` + `services/ecommerce-api/action/record_debt_followup_contact.go:88-94`	🟢 Reuse — source cho SCR-02 cột "Cuộc gọi" (Ref DEC-028)
`debt_contact_log`	Full history cuộc gọi/liên hệ (mỗi cuộc gọi = 1 row) — track audit, đếm count	`pkg/store/debt.go:106-123` + insert qua action `recordDebtFollowupContact`	🟢 Reuse — phục vụ SCR-03 (last contact) + audit
Function `customer_debt_dashboard_order_scope()`	Aging buckets + portfolio	`1776054869987_add_customer_debt_statistics_dashboard_apis`	🟢 Reuse
Result tables `dashboard_customer_debt_*_result`	KPI snapshot/ranking/trend	`1776054869987_*`	🟢 Reuse
View `customer_handover_source_report`, `_target_report`, `_log_report`	Handover audit views	`1775458258853..1775458940433`	🟢 Reuse
Function `search_report_customer_debt_management`	List khách nợ với filter	`1775037115455_consolidate_customer_debt_management_and_priority` + `1775810246370_remove_default_limit_*`	🟢 Reuse

Day-1 schema delta:

❌ KHÔNG có CREATE TABLE mới Day-1
⚠️ Có thể cần ALTER nếu verify cột order_service.is_primary_owner chưa tồn tại (FR-002 v4.12)
⚠️ Hasura YAML permission cần harden (xem C8)

C2.2 Backend (Go services)

Service	File	Vai trò	Day-1 action
`ecommerce-api/action/customer_handover_support.go`	Action xử lý handover wizard (chọn nguồn/đích/scope/effective_date)	🟢 Reuse	Verify limit 200 + validate state lệch
`ecommerce-api/action/rollback_customer_handover.go`	Action rollback trong 24h	🟢 Reuse	Verify enforcement 24h
`ecommerce-api/action/record_debt_followup_contact.go` (nếu có)	Mark contacted + optional close task	🟢 Reuse	Verify pattern
`ecommerce-api/scheduler/daily_debt_alert.go`	Daily planner → notification schedule	🟢 Reuse	Verify cron schedule (PD-002), idempotent, dedupe
`ecommerce-api/event/debt_followup_task_notification.go`	Event trigger fire khi `debt_followup_task` insert/update	🟢 Reuse	Verify schedule generation
`notification-api` (v2)	Send in-app + push FCM	🟢 Reuse	Verify template_type cho NTF-DEBT-DAILY-001, NTF-HANDOVER-*, NTF-FOLLOWUP-REMIND

C2.3 Frontend Web (diva-admin)

Xem ui-spec.md §0 Codebase Mapping cho bảng SCR → file path.

Tóm tắt files đụng vào:

src/modules/debt-manager/pages/DebtManager.tsx (layout shell — không đụng)
src/modules/debt-manager/component/consulting-performance/ (🟡 extend KPI cards)
src/modules/debt-manager/component/debt/ (🟡 extend scope chip + summary banner + daily focus)
src/modules/debt-manager/component/statistics/ (🟡 extend leaderboard + drawer NV)
src/modules/debt-manager/component/DebtManagerFilter.tsx (🟢 verify branch header)
src/modules/debt-manager/component/DebtManagerCreateReminderSchedule.tsx (🟢 verify validation)
src/modules/debt-manager/component/DebtManagerActionViewCalendar.tsx + DebtManagerReminderScheduleHistory.tsx (🟢 verify drawer)
src/modules/settings/pages/DebtManagerSetting.tsx (🟢 verify history view)
src/modules/user/components/customer-handover/CustomerHandover* (🟢 verify wizard, no schema change)

C2.4 Frontend Mobile (diva-flutter staff)

lib/presentation/modules/debt_management/debt_management/views/debt_management_screen.dart (🟡 extend KPI cards Consultation tab)
lib/presentation/modules/debt_management/debt_management/views/debt_management_debt_tab.extension.dart (🟡 pre-filter overdue + scope chip)
lib/presentation/modules/debt_management/debt_customer_detail/views/debt_customer_detail_screen.dart (🟡 thêm section Lịch sử LH + Lịch nhắc collapsible)
lib/presentation/modules/debt_management/widgets/debt_create_reminder_sheet.dart (🟢 reuse)
lib/presentation/modules/notification/views/notification_screen.dart + lib/presentation/route/app_notification_route.dart (🟡 thêm notification types: isDebtHandoverCompleted, action card)

C3. Domain Rules & Formulas (implementation delta)

Canonical business formulas: prd.md A10. Section này chỉ ghi SQL implementation delta.

FORMULA-001: Số khách đã tư vấn

Ref: PRD A10 FORMULA-001
Implementation: Function customer_debt_dashboard_order_scope() đã cover (xem 1776054869987_*)
Source mapping: consulted_customer_count ← COUNT(DISTINCT order.customer_id) filter consultant_id IS NOT NULL trong period
Index: idx_order_consultant_id (verify tồn tại)

FORMULA-002: Tỷ lệ chuyển đổi sau tư vấn

Ref: PRD A10 FORMULA-002
SQL delta: closed_customer_count / consulted_customer_count × 100
Source mapping:
- closed_customer_count ← COUNT(DISTINCT order.customer_id) WHERE order.actual_revenue > 0 trong period
- consulted_customer_count ← FORMULA-001
Precision: numeric(5,2)
Edge case: Return NULL hoặc 0 khi denominator = 0 — UI handle hiển thị "—"

FORMULA-003: Doanh thu ròng + Tỷ lệ công nợ/doanh thu (CANONICAL — Ref DEC-022)

Ref: PRD A10 FORMULA-003 + DEC-022

SQL canonical:

sql

SELECT
  SUM(order.actual_revenue) AS net_revenue,
  SUM(GREATEST(order.total - COALESCE(order.paid_amount, 0), 0)) AS total_debt,
  CASE
    WHEN SUM(order.actual_revenue) > 0
    THEN ROUND(SUM(GREATEST(order.total - COALESCE(order.paid_amount, 0), 0))::numeric / SUM(order.actual_revenue) * 100, 2)
    ELSE NULL
  END AS debt_ratio
FROM order
WHERE order.created_at >= $from::date
  AND order.created_at <  ($to::date + interval '1 day')   -- half-open range (Ref [CLAUDE.md § SQL gotchas])
  AND order.status != 'cancelled'
  AND order.deleted_at IS NULL
  AND order.payment_method_id NOT IN ('wallet', 'wallet_promotion');   -- exclude wallet (Ref [CLAUDE.md § Payment methods])

Source mapping:
- net_revenue ← SUM(order.actual_revenue) — column pre-computed bởi BE (đã trừ refund + discount). KHÔNG dùng paid_amount - refund_amount (legacy DEC-009 superseded)
- total_debt ← SUM(GREATEST(total - paid_amount, 0)) (clamp âm về 0)
- debt_ratio ← computed
NULL handling: net_revenue = 0 → debt_ratio = NULL → UI hiển thị "—"
View Manager/BOD (Ref DEC-024 + A12.4):
- Manager: thêm AND order.branch_id IN (...allowed_branches)
- BOD/Admin: không filter branch
- Cả 2 view: KHÔNG quy đổi × % HH

FORMULA-004: Tỷ lệ thu nợ

Ref: PRD A10 FORMULA-004
SQL delta: Tính qua debt_order_item_snapshot.is_settled = true
Source mapping: paid_in_period ← SUM(item_paid_amount) trên snapshot, total_debt_created ← initial snapshot amount

FORMULA-005: Số ngày thu nợ trung bình

Ref: PRD A10 FORMULA-005
SQL delta: AVG(EXTRACT(EPOCH FROM (settled_at - debt_started_at)) / 86400) trên debt_order_item_snapshot WHERE is_settled = true
Precision: numeric(5,1) ngày
Index: idx_dois_settled_at (verify)

FORMULA-006: Nhóm nợ (Order-level Aging bucket)

Ref: PRD A10 FORMULA-006
Implementation: SQL CASE trong customer_debt_dashboard_order_scope() — đã có 4 buckets 0-29, 30-59, 60-89, ≥90
TZ: Asia/Ho_Chi_Minh — (NOW() AT TIME ZONE 'Asia/Ho_Chi_Minh')::date - order.created_at::date

FORMULA-006B: Customer-level aging bucket (CRITICAL — Ref DEC-023)

Đây là rollup từ order-level → customer-level. Mọi UI hiển thị aging (chart phân loại, bảng SCR-03 cột Nhóm nợ, SCR-01 KPI count khách per bucket) đều dùng customer-level.

Ref: PRD A10 FORMULA-006B + DEC-023

SQL canonical:

sql

WITH customer_orders AS (
  SELECT
    o.customer_id,
    o.id AS order_id,
    (CURRENT_DATE AT TIME ZONE 'Asia/Ho_Chi_Minh')::date - o.created_at::date AS overdue_days,
    GREATEST(o.total - COALESCE(o.paid_amount, 0), 0) AS order_remaining
  FROM order o
  WHERE o.total - COALESCE(o.paid_amount, 0) > 0          -- chỉ đơn còn nợ
    AND o.status != 'cancelled'
    AND o.deleted_at IS NULL
    AND o.payment_method_id NOT IN ('wallet', 'wallet_promotion')
    AND (CURRENT_DATE AT TIME ZONE 'Asia/Ho_Chi_Minh')::date - o.created_at::date >= 0   -- exclude future-dated
),
customer_rollup AS (
  SELECT
    customer_id,
    MAX(overdue_days) AS max_overdue_days,
    SUM(order_remaining) AS total_customer_debt,
    COUNT(*) AS order_count
  FROM customer_orders
  GROUP BY customer_id
)
SELECT
  customer_id,
  max_overdue_days,
  total_customer_debt,
  order_count,
  CASE
    WHEN max_overdue_days <= 29 THEN 'new_debt'
    WHEN max_overdue_days <= 59 THEN 'watch_list'
    WHEN max_overdue_days <= 89 THEN 'high_risk'
    ELSE 'very_high_risk'
  END AS customer_bucket
FROM customer_rollup;

Index hỗ trợ:
- idx_order_customer_status_paid (customer_id, status, paid_amount) — verify tồn tại
- idx_order_created_at — verify
Verify existing function: Check customer_debt_dashboard_order_scope() migration 1776054869987_* đã có rollup customer-level chưa. Nếu đã có → reuse; nếu chỉ có order-level → cần extend (Day-1 priority).

Aggregate UI (biểu đồ phân loại + bảng count):

sql

-- Count khách per bucket
SELECT customer_bucket, COUNT(*) AS customer_count, SUM(total_customer_debt) AS bucket_total_debt
FROM customer_rollup_view  -- output của FORMULA-006B
GROUP BY customer_bucket;

NUL handling: Customer settled hết (total_customer_debt = 0) → exclude khỏi list
Edge cases: Xem ui-spec.md B-EdgeCases KPI Edge Cases

FORMULA-008: Weighted participation percent — Multi-order rollup (CRITICAL — Ref DEC-029)

Giải quyết gap function search_report_debt_performance_consultation hiện tại chỉ pick 1 reference_order → mất visibility các đơn khác trong cùng row tư vấn.

Ref: PRD A10 FORMULA-008 + DEC-029
Vấn đề hiện tại trong code: Function search_report_debt_performance_consultation (migration 1775036505870_consolidate_debt_report_functions/up.sql:160-200) pick LIMIT 1 đơn từ evidence_logs filter source_type = 'order_reference_appointment' order by event_at DESC → mất visibility các đơn khác.

SQL canonical (cần EXTEND function hiện tại):

sql

-- Trong CTE consultation_reference_orders (đổi từ singular → plural):
WITH consultation_reference_orders AS (
  SELECT
    cf.customer_id,
    cf.branch_id,
    cf.consulted_date,
    ref.order_id AS reference_order_id,
    ref.event_at
  FROM consultation_filtered cf
  LEFT JOIN LATERAL (
    SELECT
      NULLIF(log_item ->> 'order_id', '')::uuid AS order_id,
      COALESCE(NULLIF(log_item ->> 'event_at', '')::timestamptz, cf.latest_consulted_at) AS event_at
    FROM jsonb_array_elements(COALESCE(cf.evidence_logs, '[]'::jsonb)) AS log_item
    WHERE log_item ->> 'source_type' = 'customer_consultation_source_order_reference_appointment'
      AND COALESCE(log_item ->> 'order_id', '') <> ''
  ) ref ON TRUE
  -- BỎ LIMIT 1 — giữ tất cả orders
),
per_order_participation AS (
  SELECT
    cro.customer_id,
    cro.branch_id,
    cro.consulted_date,
    cro.reference_order_id,
    o.total AS order_total,
    o.code AS order_code,
    o.debt_owner_id,
    -- Per-order participation % theo logic order_commission hiện tại
    CASE
      WHEN o.debt_owner_id IS NULL THEN NULL
      WHEN COALESCE(o.total, 0) = 0 THEN NULL
      ELSE ROUND(
        COALESCE(SUM(
          CASE
            WHEN oc.unit = 'commission_percent' THEN
              COALESCE(oi.amount, o.total)::numeric * oc.amount::numeric / 100
            WHEN oc.unit = 'commission_vnd' THEN
              oc.amount::numeric
            ELSE 0::numeric
          END
        ), 0) / NULLIF(o.total::numeric, 0) * 100,
        2
      )
    END AS per_order_pct
  FROM consultation_reference_orders cro
  LEFT JOIN public."order" o ON o.id = cro.reference_order_id
    AND o.order_service_status NOT IN ('order_canceled', 'prepaid_canceled')
  LEFT JOIN public.order_commission oc ON oc.order_id = o.id AND oc.user_id = o.debt_owner_id
  LEFT JOIN public.order_item oi ON oi.id = oc.order_item_id
  GROUP BY cro.customer_id, cro.branch_id, cro.consulted_date, cro.reference_order_id, o.total, o.code, o.debt_owner_id
),
weighted_participation AS (
  SELECT
    customer_id,
    branch_id,
    consulted_date,
    -- Weighted avg theo order.total — Ref FORMULA-008
    CASE
      WHEN SUM(order_total) > 0
      THEN ROUND(
        SUM(per_order_pct * order_total)::numeric / NULLIF(SUM(order_total)::numeric, 0),
        2
      )
      ELSE NULL
    END AS debt_owner_participation_percent,
    -- Tooltip data: array of per-order breakdown
    JSONB_AGG(
      JSONB_BUILD_OBJECT(
        'order_id', reference_order_id,
        'order_code', order_code,
        'order_total', order_total,
        'pct', per_order_pct
      ) ORDER BY order_total DESC
    ) FILTER (WHERE reference_order_id IS NOT NULL) AS participation_breakdown
  FROM per_order_participation
  WHERE per_order_pct IS NOT NULL
  GROUP BY customer_id, branch_id, consulted_date
)
-- Then JOIN weighted_participation thay vì reference_order_participation cũ
SELECT ... FROM consultation_filtered cf
  LEFT JOIN weighted_participation wp ON wp.customer_id = cf.customer_id
    AND wp.branch_id = cf.branch_id
    AND wp.consulted_date = cf.consulted_date;

Migration plan:
1. Tạo migration mới (sau 1775036505870_consolidate_debt_report_functions) để DROP + RECREATE function search_report_debt_performance_consultation
2. Thêm field participation_breakdown JSONB vào type report_debt_performance_consultation_result
3. Hasura metadata reload (track field mới)
4. Update GraphQL query GetDebtPerformanceConsultationList thêm field participation_breakdown

FE rendering (tooltip drill-down):

// Tooltip text với drill-down data
const tooltipHtml = `
  <strong>% tham gia: ${formatPercent(row.debt_owner_participation_percent)}</strong>
  <br/>(weighted theo tổng giá trị đơn)
  <br/><br/>Chi tiết theo đơn:
  ${row.participation_breakdown
    ?.map(b => `• Đơn #${b.order_code} (${formatMoney(b.order_total)}): ${formatPercent(b.pct)} tham gia`)
    .join('<br/>')}
`;

Performance note: Loại bỏ LIMIT 1 có thể tăng số rows trong CTE per order. Index idx_order_id trên order_commission + idx_order_pk cần verify. Capacity test với scale 100k consultation × 3 orders/consultation = 300k rows.
Edge cases:
- Chỉ 1 order: weighted = per_order_pct (no-op aggregation)
- Tất cả orders có debt_owner_id = NULL → per_order_pct = NULL → weighted = NULL → UI "—"
- Đơn cancelled (order_canceled, prepaid_canceled) → exclude trong JOIN
- SUM(order_total) = 0 → return NULL → UI "—"

FORMULA-007: Service representative (cột "Dịch vụ" SCR-02 — Ref DEC-027)

Ref: PRD A10 FORMULA-007 + DEC-027

SQL representative:

sql

-- 1 row: representative service per (customer, period) hoặc per (customer, branch, date)
SELECT DISTINCT ON (customer_id)
  customer_id,
  os.id AS rep_order_service_id,
  s.name AS rep_service_name,
  (
    SELECT COUNT(*) - 1 FROM order_service os2
    JOIN consultation c2 ON c2.id = os2.consultation_id
    WHERE os2.customer_id = os.customer_id
      AND c2.consultation_date::date = c.consultation_date::date
  ) AS additional_service_count
FROM order_service os
JOIN consultation c ON c.id = os.consultation_id
JOIN service s ON s.id = os.service_id
WHERE os.customer_id IN (...)
  AND c.consultation_date BETWEEN $from AND $to
ORDER BY customer_id, c.consultation_date DESC, os.id DESC;

Output formatting (FE responsibility):
- additional_service_count = 0: display rep_service_name
- additional_service_count >= 1: display {rep_service_name} (+{additional_service_count} DV khác)
Tooltip data query: Khi user hover, FE call query phụ liệt kê tất cả DV cho customer trong period đó
Edge cases: rep_service_name IS NULL → display Dịch vụ #{rep_order_service_id}

C4. Data Model (existing — không tạo bảng mới Day-1)

Quy tắc: Day-1 KHÔNG tạo CREATE TABLE mới. Chỉ ALTER nếu cần verify column thiếu.

C4.1 Tóm tắt bảng đã có

Bảng	Migration sinh ra	Vai trò Day-1
`debt_alert_config`	(đã có trước) + `1773718673920_add_debt_alert_config_log`	Config ngưỡng cảnh báo
`debt_alert_config_log`	`1773718673920_*`	Audit thay đổi config
`debt_alert_log`	`pkg/store/debt.go:52-65` (model Go)	Log gửi alert + dedupe
`debt_alert_notification_schedule`	`1775791888659_*`	Pipeline pending → sent
`customer_handover_log`	`1772851084921_*:33`	Audit handover + snapshot + rollback
`debt_contact_log`	`pkg/store/debt.go:106-123`	Log liên hệ khách
`debt_followup_task`	`1772851084921_*:63`	Task follow-up
`debt_followup_notification_schedule`	`1775637198503_*`	Lịch gửi notification cho task
`debt_order_item_snapshot`	`1774509789555_*`	Per-item debt tracking + settled time

Schema chi tiết (cột, type, FK, index): xem migration files cụ thể trong diva-backend/services/controller/migrations/ecommerce/. KHÔNG copy schema vào spec — dễ drift.

C4.2 Potential ALTER Day-1 (cần verify trước khi merge code)

Item	Verify command	Action nếu thiếu
`order_service.is_primary_owner` (BOOL)	`\d order_service` trong psql	ALTER TABLE thêm cột + default false + backfill cho 1-sale orders
Index `idx_order_debt_owner_id`	`\di idx_order_debt_owner_id`	CREATE INDEX nếu thiếu
Index `idx_debt_followup_task_remind_at`	`\di idx_debt_followup_task_remind_at`	CREATE INDEX nếu thiếu (cần cho scheduler scan)
Index `idx_order_branch_status_paid` (Ref DEC-034)	`\di idx_order_branch_status_paid`	CREATE INDEX dashboard performance
Index `idx_order_debt_owner_created_at` (Ref DEC-034)	`\di idx_order_debt_owner_created_at`	CREATE INDEX FORMULA-006B + KPI tab Analytics
Index `idx_order_customer_paid_total` (Ref DEC-034)	`\di idx_order_customer_paid_total`	CREATE INDEX customer-level rollup
Index `idx_order_commission_user_order` (Ref DEC-034)	`\di idx_order_commission_user_order`	CREATE INDEX % tham gia weighted (FORMULA-008)

Migration safety: Nếu cần ALTER, follow pattern PL/pgSQL với pre-check + idempotent guard (xem CLAUDE.md § Migration safety).

C5. API Contracts (existing Hasura actions + GraphQL)

C5.1 Existing Hasura actions reuse

Action	File	Request shape	Use case
`customer_handover_support`	`services/ecommerce-api/action/customer_handover_support.go`	`{source_owner_id, target_owner_id, scope_type, scope_payload, effective_date, note}` → `{handover_id, customer_count, debt_amount}`	SCR-07 nút [Xác nhận bàn giao]
`rollback_customer_handover`	`services/ecommerce-api/action/rollback_customer_handover.go`	`{handover_id, reason}` → `{success, customer_count}`	SCR-08 nút [↩ Hoàn tác]
`recordDebtFollowupContact`	`services/ecommerce-api/action/record_debt_followup_contact.go`	`{customer_id, contacted_at, is_contacted, note, mark_task_done, followup_task_id?}` → INSERT `debt_contact_log` + UPDATE `ecommerce_user.last_contacted_at` + (optional) UPDATE `debt_followup_task.status='done'`	SCR-03 [✓ Đã LH], Mobile click-to-call end (SCR-02 cột "Cuộc gọi" Ref DEC-028)

C5.2 Existing GraphQL queries reuse

Query/Function	Use case
`search_report_customer_debt_management`	SCR-03 table + MOB-02 list
`dashboard_customer_debt_overview_result`	SCR-01 KPI cards (tab Công nợ) — respect `date_to` filter
`dashboard_customer_debt_ranking_result`	SCR-01-TAB-ANALYTICS leaderboard
`dashboard_customer_debt_trend_result`	SCR-01 chart aging trend
`customer_handover_log_report`	SCR-08 timeline
`customer_handover_source_report` / `_target_report`	SCR-08 audit summary
`customer_debt_dashboard_order_scope()`	Backbone aging + portfolio (gọi từ trên)
`get_priority_debt_banner` (Ref DEC-033) 🆕	Banner "Ưu tiên xử lý nợ" — AS-OF NOW, không nhận `date_to` param. Trả COUNT khách + SUM tiền nợ + breakdown bucket. Filter chỉ nhận `branch_ids[]` + `owner_ids[]` (không date)

C5.3 Delta — new mutations/queries cần thêm (nếu thiếu)

⚠️ Mutation mark_followup_task_done / cancel_followup_task — verify có chưa, nếu không thì thêm
⚠️ Mutation bulk_mark_contacted (FR-009 toolbar batch) — verify có chưa
⚠️ Query history config (debt_alert_config_log) cho SCR-04 PHẦN 3

🆕 Query get_priority_debt_banner (Ref DEC-033) — banner as-of NOW, không nhận date param. SQL canonical:

sql

CREATE OR REPLACE FUNCTION public.get_priority_debt_banner(
  _branch_ids uuid[] DEFAULT NULL::uuid[],
  _owner_ids text[] DEFAULT NULL::text[]
) RETURNS TABLE (
  customer_count bigint,
  total_overdue_debt numeric,
  bucket_breakdown jsonb   -- {new_debt: N, watch_list: N, ...}
) AS $$
  WITH customer_aging AS (
    -- Reuse FORMULA-006B logic AS-OF NOW (no date_to param)
    ...
  ),
  overdue_customers AS (
    SELECT customer_id, customer_bucket, total_customer_debt
    FROM customer_aging
    WHERE max_overdue_days >= (SELECT COALESCE(threshold_days, 30) FROM debt_alert_config WHERE scope_type='global' AND is_active=true LIMIT 1)
      AND (_branch_ids IS NULL OR branch_id = ANY(_branch_ids))
      AND (_owner_ids IS NULL OR debt_owner_id = ANY(_owner_ids))
  )
  SELECT
    COUNT(*)::bigint AS customer_count,
    COALESCE(SUM(total_customer_debt), 0) AS total_overdue_debt,
    JSONB_OBJECT_AGG(customer_bucket, bucket_count) AS bucket_breakdown
  FROM (
    SELECT customer_bucket, COUNT(*) AS bucket_count
    FROM overdue_customers
    GROUP BY customer_bucket
  ) b;
$$ LANGUAGE sql STABLE;

FE call pattern (Ref DEC-033): FE Banner component call query này INDEPENDENT với dashboard KPI queries. Refresh khi user click [Xử lý ngay] → reset filter date về TODAY rồi navigate SCR-03

Pattern: Theo Diva — chỉ tạo Hasura action mới. KHÔNG tự đặt REST /api/... (xem CLAUDE.md § Endpoint patterns).

C5.4 GraphQL header requirements

X-Hasura-Role: staff | manager | admin (resolve từ Dynamic Permission v2)
X-Hasura-Branch-Id: branch hiện tại Manager đã pick (URQL header per request)
Permission resolution server-side, FE chỉ là defense-in-depth (DEC-011, DEC-012)

C5.5 API param `view_role` cho KPI queries (Ref DEC-024 + A12.4 KPI × Role canonical) 🆕

Mọi query KPI dashboard nhận thêm param view_role:

Param value	Behavior backend	Use case
`individual` (default cho Sale/CSKH/Telesale)	Filter `owner_id = X-Hasura-User-Id` + quy đổi `× % HH` per order_service	Sale xem KPI cá nhân
`manager`	Filter `branch_id IN allowed_branches` + raw aggregate (KHÔNG quy đổi)	Manager xem KPI team trong branches quản lý
`bod`	Không filter scope + raw aggregate	BOD/Admin xem toàn hệ thống

Pattern resolve:

Nếu request không truyền view_role → BE tự resolve từ Hasura role:
- staff → individual
- manager → manager
- admin → bod
Nếu truyền explicit → BE validate role có quyền dùng view đó:
- Sale truyền view_role=manager → 403 forbidden
- Manager truyền view_role=bod → 403 forbidden
- Manager truyền view_role=individual → OK (drill-down view của 1 staff cụ thể)

Cột ẩn theo view (FE responsibility):

view_role = manager | bod: ẩn cột "Khách nợ quy đổi", "Tiền nợ quy đổi", "% Của tôi", "Doanh thu ròng quy đổi"
view_role = individual: hiển thị đầy đủ

Manager kiêm Sale toggle UI:

FE pill switcher: [View team] [View của tôi]
Default: View team (view_role=manager)
Khi switch sang View của tôi → API call lại với view_role=individual + filter owner_id=X-Hasura-User-Id

C6. Scheduler Behavior

C6.1 Daily Debt Alert (Ref DEC-025 + DEC-026)

File: services/ecommerce-api/scheduler/daily_debt_alert.go
Cron: 0 7 * * * (Asia/Ho_Chi_Minh) — chạy 7h00 sáng hằng ngày (Ref DEC-031, PD-002 resolved 2026-05-15). SLA: 100% noti delivered trước 8h00 (60 phút processing window — Sale check trước giờ làm 8h). Nếu scheduler chậm > 60 phút → alert Ops

Pipeline canonical SQL (verify match implementation):

sql

-- Step 1: Read config (global + branch override)
-- branch_override ?? global_threshold

-- Step 2: Scan candidates per branch using customer-level aging (FORMULA-006B)
WITH customer_aging AS (
  -- FORMULA-006B output here
)
SELECT
  o.debt_owner_id AS owner_id,
  ca.customer_id,
  ca.customer_bucket AS current_bucket,
  ca.max_overdue_days AS overdue_days,
  ca.total_customer_debt AS debt_amount
FROM customer_aging ca
JOIN order o ON o.customer_id = ca.customer_id AND o.deleted_at IS NULL
WHERE ca.max_overdue_days >= $threshold_days         -- Trigger >=, inclusive (Ref DEC-025)
  AND o.debt_owner_id IS NOT NULL
  AND ca.total_customer_debt > 0
GROUP BY o.debt_owner_id, ca.customer_id, ca.customer_bucket, ca.max_overdue_days, ca.total_customer_debt;

Dedupe logic (Ref DEC-026):
- Within-day dedupe key: (owner_id, customer_id, current_bucket, run_date) — duy nhất trong 1 ngày, prevent retry duplicate
- Cross-day daily resend: Mỗi ngày sinh row mới (run_date khác → key khác) → user nhận noti mỗi ngày (rule 1)
- Bucket change → escalation noti: Compare current_bucket với previous_day_bucket (lookup debt_alert_log ngày trước). Nếu bucket cao hơn → fire thêm NTF-DEBT-BUCKET-ESCALATE (rule 2)
- Partial payment: Khách thanh toán 1 phần → total_customer_debt giảm nhưng > 0 → vẫn trong list, noti với debt_amount mới (rule 3)
- Settled drop: total_customer_debt = 0 → exclude khỏi candidate list ngày sau (rule 4)
Implementation note: Cần thêm column lookup ngày trước nếu chưa có. Cách dễ: query debt_alert_log last_bucket per (owner, customer) cho ngày run_date - 1.
Retry policy:
- 3 lần với exponential backoff (60s, 300s, 1800s)
- Sau 3 lần fail → status=failed + alert Ops (Prometheus metric debt_alert_failed_total)
Monitor (FR-009):
- Metric debt_alert_delivery_rate (sent / scheduled) → alert nếu < 95%
- Metric debt_alert_bucket_escalation_total (counter) per bucket transition
- Runbook: kiểm tra FCM token health, recheck dedupe key
Test cases bắt buộc (Ref qa-test-plan):
- Seed overdue_days = threshold_days (đúng ngưỡng) → CÓ gửi
- Seed overdue_days = threshold_days - 1 → KHÔNG gửi
- Seed cùng customer ngày sau vẫn overdue → gửi noti mới (key khác)
- Seed customer chuyển bucket (29 → 30) → gửi 2 noti (daily + escalation)
- Seed customer partial pay nhưng còn > 0 → vẫn nhận daily
- Seed customer settled → ngày sau drop khỏi list

C6.2 Followup Reminder Scheduler

Pattern: Event trigger debt_followup_task_notification fire khi debt_followup_task insert/update → generate row trong debt_followup_notification_schedule với send_after = remind_at
Sub-pipeline: Sender đọc schedule khi send_after <= now() → send in-app web (DEC-020 — không push mobile)
Cleanup: Soft delete schedule sau 7 ngày nếu status=sent/cancelled

C6.3 Push Notification (FCM)

Setup: Đã có trong diva-flutter/staff/lib/app_delegate.dart:15-34
Feature flag: ff_mobile_push_notification (DEC-018) — control rollout
Deeplink handler: lib/presentation/route/app_notification_route.dart — map notification_id → screen:
- isDebtOverdueDailyAlert → DebtManagementScreen (debt tab)
- isDebtFollowupReminder → ReminderScreen (NOTE: per DEC-020 không push mobile — KHÔNG dùng path này)
- isDebtHandoverCompleted → DebtManagementScreen (handover refresh) — 🟡 cần extend

C7. Migration Steps (Day-1)

Day-1 không có CREATE TABLE mới. Chỉ ALTER nếu verify thiếu cột/index.

C7.1 Verify pre-deploy

sql

-- 1. Verify cột is_primary_owner trong order_service
SELECT column_name FROM information_schema.columns
WHERE table_name = 'order_service' AND column_name = 'is_primary_owner';

-- 2. Verify index quan trọng
SELECT indexname FROM pg_indexes
WHERE tablename IN ('order', 'debt_followup_task', 'debt_order_item_snapshot')
  AND indexname IN ('idx_order_debt_owner_id', 'idx_debt_followup_task_remind_at');

-- 3. Verify function dashboard
SELECT proname FROM pg_proc WHERE proname = 'customer_debt_dashboard_order_scope';

C7.2 Conditional ALTER (chỉ chạy nếu verify thiếu)

sql

-- Nếu order_service thiếu is_primary_owner
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'order_service' AND column_name = 'is_primary_owner'
  ) THEN
    ALTER TABLE order_service ADD COLUMN is_primary_owner BOOLEAN DEFAULT false;
    -- Backfill 1-sale orders: set is_primary_owner=true cho sale duy nhất
    UPDATE order_service os SET is_primary_owner = true
    WHERE os.role = 'sale'
      AND (SELECT COUNT(*) FROM order_service os2
           WHERE os2.order_id = os.order_id AND os2.role = 'sale') = 1;
  END IF;
END $$;

C7.3 Hasura metadata changes

Track existing tables (nếu chưa track)
Apply harden permission YAML (xem C8)
Reload metadata

Migration safety: Idempotent guard + pre-check anchor — xem CLAUDE.md § Migration safety.

C8. Security & RBAC

C8.1 Quy tắc tổng

Permission ground truth: Dynamic Permission v2 — module_id=debt_manager với actions access | create | update | view_all | export
KHÔNG hard-code role name (Sale/CSKH/Manager) ở FE/BE — chỉ check permission action
Branch scoping: Manager qua X-Hasura-Branch-Id header; row filter trong Hasura permission
Sensitive data (DEC-012): phone, debt_amount, customer_phone — BE apply permission, FE chỉ là defense-in-depth

C8.2 Hasura permission harden (delta cần làm Day-1)

Pattern cho debt_alert_config:

yaml

# Staff: KHÔNG truy cập (config do Manager/Admin)
# Manager: SELECT trong branch quản lý + UPDATE branch override
# Admin: SELECT/INSERT/UPDATE all
manager:
  select:
    columns: '*'
    filter:
      _or:
        - scope_type: { _eq: 'global' }
        - branch_id: { _in: X-Hasura-Allowed-Branch-Ids }
  update:
    columns: ['threshold_days', 'notify_time', 'is_active']
    filter:
      scope_type: { _eq: 'branch' }
      branch_id: { _in: X-Hasura-Allowed-Branch-Ids }
admin:
  select: { columns: '*', filter: {} }
  insert: { columns: '*', check: {} }
  update: { columns: '*', filter: {} }

Pattern cho debt_alert_log:

yaml

staff:
  select:
    columns: ['id', 'customer_id', 'threshold_days', 'status', 'created_at']
    filter:
      owner_id: { _eq: X-Hasura-User-Id }
manager:
  select:
    columns: '*'
    filter:
      branch_id: { _in: X-Hasura-Allowed-Branch-Ids }
admin:
  select: { columns: '*', filter: {} }

Pattern cho customer_handover_log:

yaml

staff:
  select:
    columns: ['handover_code', 'source_owner_id', 'target_owner_id', 'effective_date']
    filter:
      _or:
        - source_owner_id: { _eq: X-Hasura-User-Id }
        - target_owner_id: { _eq: X-Hasura-User-Id }
manager:
  select:
    columns: '*'
    filter:
      _or:
        - source_branch_id: { _in: X-Hasura-Allowed-Branch-Ids }
        - target_branch_id: { _in: X-Hasura-Allowed-Branch-Ids }
admin: { select: { columns: '*', filter: {} } }

Pattern cho debt_contact_log:

yaml

staff:
  select: { columns: '*', filter: { contacted_by: { _eq: X-Hasura-User-Id } } }
  insert: { columns: ['customer_id', 'note', 'is_contacted'], check: { contacted_by: { _eq: X-Hasura-User-Id } } }
manager:
  select: { columns: '*', filter: { branch_id: { _in: X-Hasura-Allowed-Branch-Ids } } }
admin: { select: { columns: '*', filter: {} } }

Pattern cho debt_followup_task:

yaml

staff:
  select: { columns: '*', filter: { _or: [{owner_id: {_eq: X-Hasura-User-Id}}, {assigned_to: {_eq: X-Hasura-User-Id}}] } }
  insert: { columns: '*', check: { owner_id: { _eq: X-Hasura-User-Id } } }
  update: { columns: ['status', 'completed_at'], filter: { _or: [{owner_id: {_eq: X-Hasura-User-Id}}, {assigned_to: {_eq: X-Hasura-User-Id}}] } }
manager:
  select: { columns: '*', filter: { branch_id: { _in: X-Hasura-Allowed-Branch-Ids } } }
admin: { select: { columns: '*', filter: {} } }

C8.3 Field-level masking

Sale/CSKH/Telesale: Xem customer.phone_number đầy đủ cho khách mình quản lý; mask cho khách khác (nếu có cross-team list)
Admin (xem ngang): luôn mask phone field (cho governance, không vận hành)

C9. NFR (Non-Functional Requirements)

C9.1 Performance

Metric	Target	Strategy
SCR-01 KPI load (filter <= 1 quý)	< 1.5s p95	Function `customer_debt_dashboard_order_scope` đã optimize; verify index
SCR-03 table render (100 rows)	< 1s p95	Server-side pagination; `LIMIT 100 OFFSET ...`
Export XLSX 50k rows	< 30s	Async worker nếu > 10k rows; show progress toast
Daily alert delivery	100% within 30 min	Scheduler 17:05 → done by 17:35; alert nếu > 30 min
Mobile push delivery	95% within 5 min	FCM monitoring

C9.2 Capacity Model

Resource	Day-1 estimate	Day-365 projection	Gate
`order` table	~500K rows	~1.5M rows	Capacity gate trước khi materialized snapshot
`debt_followup_task`	~10K rows	~50K rows	OK
`customer_handover_log`	~100 rows/year	~500 rows	OK
`debt_alert_log`	~3K rows/day	~1M rows/year	Partition by month nếu > 1M rows
Daily alert volume	~500 noti/day	~2K noti/day	FCM quota OK

Capacity gate cho debt_daily_snapshot (Ref DEC-034 LOCKED):

Day-1 KHÔNG build snapshot. Áp dụng Option D strategy: index-first + gate Day-30.

Pre-deploy mandatory indexes (Day-1):

sql

-- Migration mới: add_debt_dashboard_indexes
-- Verify tồn tại trước; tạo nếu thiếu (IF NOT EXISTS)

CREATE INDEX IF NOT EXISTS idx_order_branch_status_paid
  ON public.order (branch_id, status, paid_amount)
  WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_order_debt_owner_created_at
  ON public.order (debt_owner_id, created_at DESC)
  WHERE debt_owner_id IS NOT NULL AND deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_order_customer_paid_total
  ON public.order (customer_id, paid_amount, total)
  WHERE deleted_at IS NULL AND status != 'cancelled';

CREATE INDEX IF NOT EXISTS idx_order_commission_user_order
  ON public.order_commission (user_id, order_id);

Gate trigger snapshot build Phase 2:

Condition	Threshold	Action
`dashboard_query_duration_seconds` p95 (tab Analytics)	> 1500ms liên tiếp 7 ngày	Trigger Phase 2 build
Same metric p99	> 3000ms bất kỳ 24h period	Trigger Phase 2 build
Manager complaint qua support ticket	>= 3 trong 30 ngày	Trigger Phase 2 build
`order` table row count	> 3M rows	Trigger Phase 2 build

Snapshot scope (nếu trigger Phase 2):

Chỉ build cho tab Thống kê (SCR-01-TAB-ANALYTICS) — Admin/Manager aggregate large scope
Tab Hiệu suất Tư vấn + Công nợ (per-user scope) → vẫn on-the-fly
Banner "Ưu tiên xử lý nợ" (Ref DEC-033 as-of NOW) → vẫn on-the-fly real-time

Monitor setup Day-1:

Prometheus metric dashboard_query_duration_seconds{endpoint="analytics_tab"} histogram
Grafana dashboard Debt Manager Performance với p50/p95/p99 line chart
Alert rule: p95(dashboard_query_duration_seconds{endpoint="analytics_tab"}) > 1.5 for 7d → page Ops

C9.3 Reliability

Scheduler daily_debt_alert failure → retry 3 lần + alert Ops + Sentry
Handover action failure mid-transaction → BE rollback + return 5xx với error code; UI giữ form data
Hasura permission breach → alert Ops + log to security channel

C10. Observability

C10.1 Metrics (Prometheus)

Metric	Type	Labels	Alert
`debt_alert_scheduled_total`	counter	branch_id	—
`debt_alert_sent_total`	counter	branch_id, channel	—
`debt_alert_failed_total`	counter	branch_id, error_code	rate > 5%/h
`debt_alert_delivery_rate`	gauge	—	< 95% — page Ops
`customer_handover_completed_total`	counter	source_role, target_role	—
`customer_handover_rolled_back_total`	counter	reason	—
`followup_task_created_total`	counter	role	—
`followup_task_completed_rate`	gauge	—	< 70% — surface in dashboard
`dashboard_query_duration_seconds`	histogram	endpoint	p95 > 2s

C10.2 Logs

Action logs: customer_handover_support, rollback_customer_handover — INFO with handover_id, customer_count
Scheduler logs: daily_debt_alert — INFO start/end with branches processed, alerts sent
Permission denials: WARN với user_id, action, resource

C10.3 Dashboards (Grafana)

Debt Alert Health: delivery rate, retry rate, error rate
Handover Activity: volume per day, rollback rate, avg debt_amount
Followup Task: created/completed/cancelled per day per role

C11. Implementation Breakdown

C11.1 FE Web (diva-admin)

Task ID	Description	File	Effort
FE-WEB-01	Extend `DebtManagerConsultingPerformance.tsx` với KPI cards FR-003/004	`consulting-performance/`	2d
FE-WEB-02	Extend `DebtManagerDebt.tsx` với scope chip + summary banner + daily focus	`debt/`	3d
FE-WEB-03	Extend `DebtManagerStatistics.tsx` với leaderboard + drawer NV 720px	`statistics/`	4d
FE-WEB-04	Verify `DebtManagerSetting.tsx` history view + branch override	`settings/pages/`	1d
FE-WEB-05	Verify `CustomerHandover*` wizard tuân thủ limit 200 + rollback 24h	`user/components/customer-handover/`	2d
FE-WEB-06	Verify column mapping XLSX export theo B-Export	core export	1d
FE-WEB-07	I18n keys mới theo B7 Copy Text + B9 Tooltip	i18n files	1d
FE-WEB-08	Onboarding tour SCR-01 (B-Onboarding)	new component	2d

C11.2 FE Mobile (diva-flutter)

Task ID	Description	File	Effort
FE-MOB-01	Extend `debt_management_screen.dart` thêm KPI layout Consultation tab	`debt_management/views/`	2d
FE-MOB-02	Pre-filter overdue + scope chip trong debt tab	`debt_management_debt_tab.extension.dart`	2d
FE-MOB-03	Thêm collapsible sections trong `debt_customer_detail_screen.dart`	`debt_customer_detail/views/`	3d
FE-MOB-04	Verify `debt_create_reminder_sheet.dart`	`widgets/`	1d
FE-MOB-05	Thêm notification types + deep link cho `isDebtHandoverCompleted`	`notification/views/` + `route/`	2d

C11.3 Backend (Go)

Task ID	Description	File	Effort
BE-01	Verify `customer_handover_support` enforce limit 200 + state check	`action/customer_handover_support.go`	1d
BE-02	Verify `rollback_customer_handover` enforce 24h + idempotent	`action/rollback_customer_handover.go`	1d
BE-03	Add mutation `mark_followup_task_done`, `cancel_followup_task` (nếu thiếu)	`action/`	1d
BE-04	Add mutation `bulk_mark_contacted` (nếu thiếu)	`action/`	1d
BE-05	Add query `get_debt_alert_config_history` cho SCR-04 PHẦN 3 (nếu thiếu)	metadata	0.5d
BE-06	Verify scheduler `daily_debt_alert` retry policy + monitoring	`scheduler/daily_debt_alert.go`	1d
BE-07	Add notification template_type cho NTF-* (verify với notification-api)	`notification-api` config	1d

C11.4 DB / Hasura

Task ID	Description	File	Effort
DB-01	Verify cột `order_service.is_primary_owner` + backfill	migration mới (nếu thiếu)	0.5d
DB-02	Verify index quan trọng (4 indexes critical Ref DEC-034) + tạo migration `add_debt_dashboard_indexes`	migration mới	0.5d
DB-03	Harden Hasura permission YAML cho 5 bảng	`controller/metadata/databases/ecommerce/tables/*.yaml`	2d
DB-04	Capacity test SCR-01-TAB-ANALYTICS @ 1.5M order rows (Ref DEC-034 — verify p95 < 1.5s sau khi add indexes)	benchmark	1d
DB-05 (Phase 2 conditional)	Build `debt_daily_snapshot` table + cron refresh (chỉ trigger khi gate DEC-034 fail Day-30)	migration + scheduler	3-5d

C11.5 QA / Ops

Task ID	Description	Effort
QA-01	Test plan execution (xem `qa-test-plan.md`)	5d
OPS-01	Runbook daily_debt_alert	1d
OPS-02	Grafana dashboards	1d
OPS-03	Alert rules (debt_alert_failed, handover_failed)	0.5d

Total estimate: ~35 person-days (5 devs × 1 sprint)

C12. Traceability Matrix

FR-ID	AC	UI surface	Code (FE/BE/DB)	Test case
FR-001	AC-001	SCR-01 filter	`DebtManagerFilter.tsx`	TC-CP-01
FR-002	AC-002	SCR-02 + popup hoa hồng	`DebtManagerConsultingPerformance.tsx` + commission popup	TC-CP-02, 08c-f
FR-003	AC-003	SCR-01 KPI tiếp khách	`dashboard_customer_debt_overview_result`	TC-CP-03
FR-004	AC-004	SCR-01 KPI mua + chuyển đổi	FORMULA-002 in `customer_debt_dashboard_order_scope`	TC-CP-04
FR-005	AC-005	SCR-01 KPI công nợ + SCR-03	`dashboard_customer_debt_*_result`	TC-CP-05, 09
FR-006	AC-006	SCR-01 KPI avg_days_to_collect	FORMULA-005 in `debt_order_item_snapshot`	TC-CP-06
FR-007	AC-007	SCR-01 aging chart	FORMULA-006 + function aging	TC-CP-07
FR-008	AC-008	SCR-04 config	`DebtManagerSetting.tsx` + `debt_alert_config` + history table	TC-CP-11
FR-009	AC-009	NTF-DEBT-DAILY-001 + in-app	`daily_debt_alert.go` + `debt_alert_notification_schedule`	TC-CP-12, 13, 14
FR-010	AC-010	SCR-05..07 wizard	`CustomerHandover*` + `customer_handover_support`	TC-CP-15, 16, 17
FR-011	AC-011	SCR-08 audit + rollback	`customer_handover_log_report` + `rollback_customer_handover`	TC-CP-18, 19, 20
FR-012	AC-012	Export XLSX all pages	`XExcel` + `XTable`	TC-CP-21
FR-013	AC-013	SCR-03-DRAWER + Popup + MOB-04	`debt_followup_task` + `DebtManagerCreateReminderSchedule` + `debt_create_reminder_sheet.dart`	TC-CP-22, 23, 24
FR-014	AC-014	SCR-01-TAB-ANALYTICS — Tỷ lệ thu nợ	`dashboard_customer_debt_ranking_result`	TC-CP-25
FR-015	AC-015	SCR-01-TAB-ANALYTICS — Avg days to collect	FORMULA-005	TC-CP-26
FR-016	AC-016	SCR-01-TAB-ANALYTICS — Leaderboard	ranking result	TC-CP-27
FR-017	AC-017	SCR-01-TAB-ANALYTICS — Drawer NV chi tiết	drawer 720px	TC-CP-28
FR-018	AC-018	SCR-01-TAB-ANALYTICS — Segment so sánh	KPI cards	TC-CP-29
FR-019	AC-019	SCR-01-ANALYTICS-DRAWER aging + Top 5	ranking result	TC-CP-30
FR-020	AC-020	SCR-01-TAB-ANALYTICS export	export feature	TC-CP-31

Test case IDs (TC-CP-XX) tham chiếu qa-test-plan.md D4 Critical Path Test Scenarios.

C13. Pitfalls Map references

Khi implement, đọc các pitfalls đã document trong CLAUDE.md:

Khu vực	Pitfall ref
Endpoint pattern (chỉ 4 patterns)	`CLAUDE.md § Endpoint patterns`
Hasura action 4-place registration	`CLAUDE.md § Endpoint patterns > Hasura action có 4 nơi đăng ký`
Date range BETWEEN trap	`CLAUDE.md § SQL gotchas > Date range`
SUM(DISTINCT) undercount	`CLAUDE.md § SQL gotchas > SUM(DISTINCT)`
Payment methods exclude wallet	`CLAUDE.md § Payment methods`
Migration safety (sentinel + idempotent + anchor)	`CLAUDE.md § Migration safety`
Schema gotchas (display_name vs full_name, phone_number vs phone)	`CLAUDE.md § Schema gotchas`
Print template (nếu cần invoice render handover)	`CLAUDE.md § Print template`

Sales-CSKH Debt Follow-up & Handover — Dev Spec ​

ⓘ Cách đọc Dev Spec này ​

C1. Scope kỹ thuật ​

C1.1 In scope Day-1 ​

C1.2 Out of scope Day-1 ​

C2. Impact Analysis ​

C2.1 Database (PostgreSQL via Hasura) ​

C2.2 Backend (Go services) ​

C2.3 Frontend Web (diva-admin) ​

C2.4 Frontend Mobile (diva-flutter staff) ​

C3. Domain Rules & Formulas (implementation delta) ​

FORMULA-001: Số khách đã tư vấn ​

FORMULA-002: Tỷ lệ chuyển đổi sau tư vấn ​

FORMULA-003: Doanh thu ròng + Tỷ lệ công nợ/doanh thu (CANONICAL — Ref DEC-022) ​

FORMULA-004: Tỷ lệ thu nợ ​

FORMULA-005: Số ngày thu nợ trung bình ​

FORMULA-006: Nhóm nợ (Order-level Aging bucket) ​

FORMULA-006B: Customer-level aging bucket (CRITICAL — Ref DEC-023) ​

FORMULA-008: Weighted participation percent — Multi-order rollup (CRITICAL — Ref DEC-029) ​

FORMULA-007: Service representative (cột "Dịch vụ" SCR-02 — Ref DEC-027) ​

C4. Data Model (existing — không tạo bảng mới Day-1) ​

C4.1 Tóm tắt bảng đã có ​

C4.2 Potential ALTER Day-1 (cần verify trước khi merge code) ​

C5. API Contracts (existing Hasura actions + GraphQL) ​

C5.1 Existing Hasura actions reuse ​

C5.2 Existing GraphQL queries reuse ​

C5.3 Delta — new mutations/queries cần thêm (nếu thiếu) ​

C5.4 GraphQL header requirements ​

C5.5 API param view_role cho KPI queries (Ref DEC-024 + A12.4 KPI × Role canonical) 🆕 ​

C6. Scheduler Behavior ​

C6.1 Daily Debt Alert (Ref DEC-025 + DEC-026) ​

C6.2 Followup Reminder Scheduler ​

C6.3 Push Notification (FCM) ​

C7. Migration Steps (Day-1) ​

C7.1 Verify pre-deploy ​

C7.2 Conditional ALTER (chỉ chạy nếu verify thiếu) ​

C7.3 Hasura metadata changes ​

C8. Security & RBAC ​

C8.1 Quy tắc tổng ​

C8.2 Hasura permission harden (delta cần làm Day-1) ​

C8.3 Field-level masking ​

C9. NFR (Non-Functional Requirements) ​

C9.1 Performance ​

C9.2 Capacity Model ​

C9.3 Reliability ​

C10. Observability ​

C10.1 Metrics (Prometheus) ​

C10.2 Logs ​

C10.3 Dashboards (Grafana) ​

C11. Implementation Breakdown ​

C11.1 FE Web (diva-admin) ​

C11.2 FE Mobile (diva-flutter) ​

C11.3 Backend (Go) ​

C11.4 DB / Hasura ​

C11.5 QA / Ops ​

C12. Traceability Matrix ​

C13. Pitfalls Map references ​

Sales-CSKH Debt Follow-up & Handover — Dev Spec

ⓘ Cách đọc Dev Spec này

C1. Scope kỹ thuật

C1.1 In scope Day-1

C1.2 Out of scope Day-1

C2. Impact Analysis

C2.1 Database (PostgreSQL via Hasura)

C2.2 Backend (Go services)

C2.3 Frontend Web (diva-admin)

C2.4 Frontend Mobile (diva-flutter staff)

C3. Domain Rules & Formulas (implementation delta)

FORMULA-001: Số khách đã tư vấn

FORMULA-002: Tỷ lệ chuyển đổi sau tư vấn

FORMULA-003: Doanh thu ròng + Tỷ lệ công nợ/doanh thu (CANONICAL — Ref DEC-022)

FORMULA-004: Tỷ lệ thu nợ

FORMULA-005: Số ngày thu nợ trung bình

FORMULA-006: Nhóm nợ (Order-level Aging bucket)

FORMULA-006B: Customer-level aging bucket (CRITICAL — Ref DEC-023)

FORMULA-008: Weighted participation percent — Multi-order rollup (CRITICAL — Ref DEC-029)

FORMULA-007: Service representative (cột "Dịch vụ" SCR-02 — Ref DEC-027)

C4. Data Model (existing — không tạo bảng mới Day-1)

C4.1 Tóm tắt bảng đã có

C4.2 Potential ALTER Day-1 (cần verify trước khi merge code)

C5. API Contracts (existing Hasura actions + GraphQL)

C5.1 Existing Hasura actions reuse

C5.2 Existing GraphQL queries reuse

C5.3 Delta — new mutations/queries cần thêm (nếu thiếu)

C5.4 GraphQL header requirements

C5.5 API param `view_role` cho KPI queries (Ref DEC-024 + A12.4 KPI × Role canonical) 🆕

C6. Scheduler Behavior

C6.1 Daily Debt Alert (Ref DEC-025 + DEC-026)

C6.2 Followup Reminder Scheduler

C6.3 Push Notification (FCM)

C7. Migration Steps (Day-1)

C7.1 Verify pre-deploy

C7.2 Conditional ALTER (chỉ chạy nếu verify thiếu)

C7.3 Hasura metadata changes

C8. Security & RBAC

C8.1 Quy tắc tổng

C8.2 Hasura permission harden (delta cần làm Day-1)

C8.3 Field-level masking

C9. NFR (Non-Functional Requirements)

C9.1 Performance

C9.2 Capacity Model

C9.3 Reliability

C10. Observability

C10.1 Metrics (Prometheus)

C10.2 Logs

C10.3 Dashboards (Grafana)

C11. Implementation Breakdown

C11.1 FE Web (diva-admin)

C11.2 FE Mobile (diva-flutter)

C11.3 Backend (Go)

C11.4 DB / Hasura

C11.5 QA / Ops

C12. Traceability Matrix

C13. Pitfalls Map references