Dev Spec — Nâng cấp Voucher Management: Kiểm soát, Thống kê & Đối tác

Version: 1.0 Ngày: 2026-03-19 PRD version: v1.1 Tham chiếu: docs/features/voucher-enhancement/prd.mdUI Spec: docs/features/voucher-enhancement/ui-spec.mdPerf-fix prerequisite: docs/features/voucher-enhancement/perf-fix-campaign-detail.md

C1. Scope & Approach

1.1 Phạm vi

Aspect	Chi tiết
Feature	Nâng cấp Voucher Management: Kiểm soát thời gian, Thống kê nhân viên, Đối tác Affiliate, Chu kỳ sử dụng
Complexity	Large — 4 phases, cross-module
Modules FE	`cms` (voucher), `ecommerce` (order), `settings`, `affiliate`
Services BE	`ecommerce-api`, `controller` (Hasura), `export-api`
Database	PostgreSQL — schema `public` (ecommerce domain)
Platform	Admin web (Vue 3 + Quasar) + Diva Partner app (API only)

1.2 Phân phase

Phase	Priority	Nội dung	Phụ thuộc
PERF-FIX	Prerequisite	Tối ưu `get_voucher_analytics_summary` — expression index, merge CTEs, split aggregate	Không — deploy TRƯỚC P2/P4
P1	P0 — Urgent	Kiểm soát thời gian kích hoạt → sử dụng + Thời hạn voucher manual + Settings	Độc lập
P2	P1 — High	Thống kê nhân viên phát voucher + export + Partner API	PERF-FIX
P3	P1 — High	Đối tác Affiliate — gán vào campaign, quota, badge	P2 (reuse report)
P4	P2 — Medium	Chu kỳ sử dụng — KPI, bucket chart, so sánh	PERF-FIX

1.3 Approach tổng quan

Backend-first: Mỗi phase deploy migration + Go handler + Hasura metadata trước, FE sau
Atomic migration: Mỗi phase có migration riêng, rollback độc lập
Reuse pattern: SQL function follow pattern get_voucher_branch_statistics (migration 1766800000000)
Timezone: Tất cả time comparison dùng Asia/Ho_Chi_Minh (ref: DEC-Q01)
Soft delete: Table mới có deleted_at khi cần (ref: DB Pattern chuẩn Diva)

1.4 Dependency Graph

                    PERF-01 (expression index)
                         │
                    PERF-02 (merge CTEs)
                         │
                    PERF-03 (split FE query)
                         │
                    ┌────┴────┐
                    ▼         ▼
               P2 tasks   P4 tasks
                    │
                    ▼
               P3 tasks

P1 tasks ─────────────────────────► (độc lập, triển khai song song)

C2. Impact Analysis

2.1 Backend — Go files

File	Thay đổi	Phase	Risk
`services/ecommerce-api/action/create_order.go`	Thêm time check trong `validateOrderVouchers()` (line ~707-843) — check `min_activation_hours` + override existence	P1	Medium — logic validation order, cần test kỹ
`services/ecommerce-api/action/activate_offline_voucher.go`	Set `expired_at` cho manual voucher + affiliate quota check trong `updateVoucherToActivated()`	P1, P3	Medium — ảnh hưởng flow kích hoạt offline
`services/ecommerce-api/action/activate_voucher.go`	Set `distributor_type` trong `updateVoucherToActivated()` dựa vào staff_id lookup	P2	Low — additive field
`services/ecommerce-api/action/approve_voucher_override.go`	MỚI — action handler cho override flow	P1	Low — file mới, không ảnh hưởng code hiện có
`services/ecommerce-api/action/create_draft_voucher_campaign.go`	Thêm INSERT `voucher_campaign_affiliates` (với `distribution_method`) sau khi tạo campaign	P3	Low — additive
`services/ecommerce-api/action/update_voucher_campaign.go`	Thêm UPSERT `voucher_campaign_affiliates` khi update campaign	P3	Medium — upsert logic cần atomic
`services/ecommerce-api/action/generate_affiliate_voucher_batch.go`	MỚI — Generate batch mã voucher pre-assign cho đối tác offline	P3	Medium — bulk INSERT + atomic quota
`pkg/store/app_setting.go`	Thêm `VoucherSetting` struct vào `AppSettings`	P1	Low — additive struct
`pkg/store/user_vouchers.go`	Thêm `DistributorType` field vào struct	P2	Low — additive field
`pkg/store/voucher_campaigns.go`	Thêm `MinActivationHours` field vào struct	P1	Low — additive field

2.2 Backend — Hasura Metadata

File	Loại	Phase
`controller/metadata/databases/ecommerce/tables/public_voucher_activation_overrides.yaml`	MỚI — table tracking + permissions	P1
`controller/metadata/databases/ecommerce/tables/public_voucher_campaign_affiliates.yaml`	MỚI — table tracking + permissions	P3
`controller/metadata/databases/ecommerce/functions/public_get_voucher_staff_statistics.yaml`	MỚI — function tracking	P2
`controller/metadata/databases/ecommerce/functions/public_get_voucher_usage_cycle_statistics.yaml`	MỚI — function tracking	P4
`controller/metadata/databases/ecommerce/functions/public_get_voucher_usage_cycle_distribution.yaml`	MỚI — function tracking	P4
`controller/metadata/databases/ecommerce/tables/public_voucher_campaigns.yaml`	SỬA — thêm column `min_activation_hours` vào select permissions	P1
`controller/metadata/databases/ecommerce/tables/public_user_vouchers.yaml`	SỬA — thêm column `distributor_type` vào select permissions	P2

2.3 Backend — Migrations

Migration	Nội dung	Phase
`XXXXXXXXXX_voucher_time_control/up.sql`	ALTER `voucher_campaigns` + CREATE `voucher_activation_overrides`	P1
`XXXXXXXXXX_voucher_staff_statistics/up.sql`	ALTER `user_vouchers` + CREATE FUNCTION `get_voucher_staff_statistics`	P2
`XXXXXXXXXX_voucher_campaign_affiliates/up.sql`	CREATE `voucher_campaign_affiliates` (với `distribution_method`)	P3
`XXXXXXXXXX_alter_uv_assigned_affiliate/up.sql`	ALTER `user_vouchers` ADD `assigned_affiliate_id` + index (batch pre-assign)	P3
`XXXXXXXXXX_voucher_usage_cycle/up.sql`	CREATE FUNCTION `get_voucher_usage_cycle_statistics` + `get_voucher_usage_cycle_distribution`	P4

2.4 Frontend — Files

File	Loại	Phase	Mô tả
`src/modules/cms/components/voucher/voucher-campaign/VoucherConfigForm.tsx`	SỬA	P1	Thêm field `min_activation_hours`
`src/modules/cms/components/voucher/VoucherTimeBlockDialog.tsx`	MỚI	P1	Dialog chặn + override flow
`src/modules/cms/pages/voucher-management/VoucherOverrideDashboard.tsx`	MỚI	P1	Dashboard giám sát override
`src/modules/settings/components/VoucherSettingsSection.tsx`	MỚI	P1	Config mặc định voucher
`src/modules/cms/components/voucher/voucher-campaign/tabs/VoucherStaffStatisticsTab.tsx`	MỚI	P2	Tab thống kê nhân viên
`src/modules/cms/components/voucher/voucher-campaign/StaffDrillDownDialog.tsx`	MỚI	P2	Drill-down chi tiết per NV
`src/modules/cms/components/voucher/voucher-campaign/AffiliateAssignStep.tsx`	MỚI	P3	Wizard step chọn đối tác + kênh phát (app/batch/both) + quota
`src/modules/cms/components/voucher/voucher-campaign/AffiliateBatchPanel.tsx`	MỚI	P3	Panel generate batch + download PDF/Excel + thống kê
`src/modules/cms/components/voucher/voucher-campaign/GenerateBatchDialog.tsx`	MỚI	P3	Dialog nhập số lượng + confirm generate batch
`src/modules/ecommerce/components/order/AffiliateSourceBadge.tsx`	MỚI	P3	Badge "Voucher do KOL X phát"
`src/modules/cms/components/voucher/voucher-campaign/tabs/VoucherUsageCycleTab.tsx`	MỚI	P4	Tab chu kỳ sử dụng
`src/modules/cms/components/voucher/voucher-campaign/CycleBucketChart.tsx`	MỚI	P4	Horizontal bar chart (Chart.js)
`src/modules/cms/graphql/voucher.graphql`	SỬA	P1-P4	Thêm queries/mutations mới
`src/modules/cms/types.ts`	SỬA	P1-P4	Thêm types mới
`src/modules/cms/pages/voucher-management/VoucherCampaignDetail.tsx`	SỬA	P2, P4	Thêm tabs thống kê + chu kỳ
`src/modules/cms/pages/voucher-management/VoucherCreate.tsx`	SỬA	P3	Thêm wizard step Đối tác

2.5 Frontend — File Structure

src/modules/cms/
├── pages/voucher-management/
│   ├── VoucherCampaignDetail.tsx          ← SỬA (thêm tabs P2, P4)
│   ├── VoucherCreate.tsx                   ← SỬA (thêm affiliate step P3)
│   └── VoucherOverrideDashboard.tsx        ← MỚI (P1)
├── components/voucher/
│   ├── voucher-campaign/
│   │   ├── VoucherConfigForm.tsx           ← SỬA (thêm min_activation_hours P1)
│   │   ├── tabs/
│   │   │   ├── VoucherStaffStatisticsTab.tsx  ← MỚI (P2)
│   │   │   └── VoucherUsageCycleTab.tsx       ← MỚI (P4)
│   │   ├── StaffDrillDownDialog.tsx        ← MỚI (P2)
│   │   ├── CycleBucketChart.tsx            ← MỚI (P4)
│   │   └── AffiliateAssignStep.tsx         ← MỚI (P3)
│   └── VoucherTimeBlockDialog.tsx          ← MỚI (P1)
├── graphql/voucher.graphql                 ← SỬA (P1-P4)
└── types.ts                                ← SỬA (P1-P4)

src/modules/ecommerce/
├── components/order/
│   └── AffiliateSourceBadge.tsx            ← MỚI (P3)

src/modules/settings/
└── components/VoucherSettingsSection.tsx    ← MỚI (P1)

C3. Rules & Formulas

FORMULA-001: Time Validation (Kiểm tra thời gian kích hoạt)

Mô tả: Kiểm tra voucher đã đủ thời gian chờ sau kích hoạt trước khi cho phép sử dụng trong đơn hàng

Công thức:

hours_elapsed = EXTRACT(EPOCH FROM (NOW() AT TIME ZONE 'Asia/Ho_Chi_Minh' - voucher.activated_at)) / 3600
IF hours_elapsed < campaign.min_activation_hours → REJECT

Biến số:
- NOW() AT TIME ZONE 'Asia/Ho_Chi_Minh': thời điểm hiện tại theo timezone Việt Nam — nguồn: runtime
- voucher.activated_at: thời điểm kích hoạt voucher — nguồn: user_vouchers.activated_at
- campaign.min_activation_hours: thời gian chờ tối thiểu (giờ) — nguồn: voucher_campaigns.min_activation_hours
Đơn vị: giờ (decimal, precision 2)
Ví dụ: Voucher kích hoạt lúc 10:00 01/04. Campaign set 24h. NV tạo ĐH lúc 12:00 01/04 → hours_elapsed = 2.00 → 2 < 24 → REJECT. Còn lại = 24 - 2 = 22 giờ
Edge cases:
- activated_at = NULL → skip check (voucher chưa kích hoạt, validation khác sẽ chặn)
- min_activation_hours = 0 → skip check (campaign không giới hạn thời gian chờ)
- Voucher manual (không thuộc campaign) → đọc AppSettings.VoucherSetting.DefaultMinActivationHours thay cho campaign.min_activation_hours
- Đã có override record cho user_voucher_id → skip check
- Timezone edge: nửa đêm UTC+7, voucher kích hoạt 23:30 → chờ 24h → dùng được 23:30 hôm sau
Ref: DEC-T01, DEC-B01, DEC-B02, FR-P1-02

FORMULA-002: Conversion Rate per Staff (Tỉ lệ chuyển đổi per nhân viên)

Mô tả: Tính phần trăm voucher đã được sử dụng so với tổng số voucher phát ra, theo từng nhân viên
Công thức: conversion_rate = (total_redeemed / total_distributed) × 100
Biến số:
- total_redeemed: số voucher đã sử dụng — nguồn: COUNT(user_vouchers) WHERE status = 'redeemed' hoặc voucher_logs action = 'voucher_redeemed' (net, sau restore)
- total_distributed: tổng voucher đã phát — nguồn: COUNT(user_vouchers) WHERE staff_id = ?
Đơn vị: % (2 decimal)
Ví dụ: NV Nguyễn A phát 120 voucher, 45 đã dùng (net) → 45 / 120 × 100 = 37.50%
Edge cases:
- total_distributed = 0 → hiển thị "—" (không hiện 0% hay NaN)
- NULL values → exclude khỏi tính toán
- Voucher restored (ĐH hủy) → không tính vào total_redeemed (dùng net count)
Ref: DEC-T05, FR-P2-01

FORMULA-003: Cycle Days (Số ngày từ kích hoạt → sử dụng)

Mô tả: Tính số ngày giữa thời điểm kích hoạt voucher và thời điểm sử dụng lần cuối (net)
Công thức: cycle_days = EXTRACT(EPOCH FROM (redeemed_at - activated_at)) / 86400
Biến số:
- redeemed_at: thời điểm sử dụng cuối cùng — nguồn: voucher_logs với DISTINCT ON (voucher_id) WHERE action = 'voucher_redeemed' ORDER BY created_at DESC
- activated_at: thời điểm kích hoạt — nguồn: user_vouchers.activated_at
Đơn vị: ngày (decimal, hiển thị integer rounded)
Ví dụ: Kích hoạt 01/04 10:00, sử dụng 09/04 14:00 → (9×86400 + 4×3600) / 86400 = 9.17 → hiển thị 9 ngày
Edge cases:
- activated_at = NULL → exclude khỏi tính toán
- Kích hoạt và sử dụng cùng ngày → 0 ngày
- Voucher bị restore rồi redeem lại → lấy created_at của log redeem cuối cùng (DESC)
Ref: DEC-T05, FR-P4-01

FORMULA-004: Median Cycle (Trung vị chu kỳ sử dụng)

Mô tả: Tính giá trị trung vị của tất cả cycle_days trong tập dữ liệu được filter
Công thức: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY cycle_days)
Biến số:
- cycle_days: mảng giá trị từ FORMULA-003 — nguồn: derived từ voucher_logs
Đơn vị: ngày (1 decimal)
Ví dụ: Tập [1, 3, 5, 9, 14, 20, 30] → median = 9.0 ngày (element thứ 4 trong 7)
Edge cases:
- Không có voucher nào redeemed → NULL → hiển thị "—"
- Chỉ 1 voucher → median = cycle_days của voucher đó
- Số chẵn elements → interpolation giữa 2 giá trị giữa (PostgreSQL PERCENTILE_CONT tự xử lý)
Ref: DEC-T06, FR-P4-01

FORMULA-005: Bucket Distribution (Phân bổ theo nhóm chu kỳ)

Mô tả: Tính phần trăm voucher rơi vào từng bucket thời gian
Công thức: percentage = (bucket_count / total_count) × 100
Biến số:
- bucket_count: số voucher trong bucket — nguồn: COUNT theo CASE WHEN
- total_count: tổng voucher (bao gồm pending) — nguồn: SUM(all bucket counts)
Đơn vị: % (1 decimal)
Ví dụ: Tổng 200 voucher. Bucket "0-3 ngày" có 56 voucher → 56 / 200 × 100 = 28.0%
Buckets:
- 0-3 ngày, 4-7 ngày, 8-14 ngày, 15-30 ngày, 31-60 ngày, 60+ ngày, pending (chưa sử dụng)
Edge cases:
- total_count = 0 → tất cả bucket hiển thị 0%
- Tổng percentage có thể ≠ 100% do rounding → không cần normalize
Ref: DEC-T07, FR-P4-02

FORMULA-006: Affiliate Quota Check (Kiểm tra quota đối tác)

Mô tả: Kiểm tra đối tác còn quota phát voucher không, dùng atomic increment để tránh race condition

Công thức:

IF quota IS NOT NULL AND distributed_count >= quota → REJECT
Atomic: UPDATE voucher_campaign_affiliates
        SET distributed_count = distributed_count + 1
        WHERE id = ? AND (quota IS NULL OR distributed_count < quota)

Biến số:
- quota: giới hạn phát tối đa — nguồn: voucher_campaign_affiliates.quota
- distributed_count: số đã phát — nguồn: voucher_campaign_affiliates.distributed_count
Đơn vị: integer
Ví dụ: KOL Hương, quota = 200, đã phát 198. Phát thêm 1 → distributed_count = 199 → OK. Phát thêm 1 → distributed_count = 200 → OK. Phát thêm 1 → 200 >= 200 → REJECT
Edge cases:
- quota = NULL → unlimited, không check
- Concurrent requests: atomic UPDATE với WHERE condition distributed_count < quota → chỉ 1 request thành công, request còn lại affected_rows = 0 → REJECT
- Race condition retry: nếu affected_rows = 0 nhưng SELECT lại thấy distributed_count < quota → retry 1 lần (transient lock contention)
Ref: DEC-T04, DEC-Q02, FR-P3-02

C4. Data Model

4.1 NEW TABLE: `voucher_activation_overrides` (Phase 1)

sql

-- Migration: XXXXXXXXXX_voucher_time_control/up.sql (part 1)

CREATE TABLE voucher_activation_overrides (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_voucher_id UUID NOT NULL REFERENCES user_vouchers(id),
    order_id UUID,                          -- ĐH được tạo sau override (nullable — set sau khi tạo ĐH thành công)
    approved_by UUID NOT NULL,              -- Manager/Admin user ID
    branch_id UUID,                         -- Chi nhánh nơi override xảy ra
    reason_code TEXT NOT NULL CHECK (reason_code IN (
        'vip_customer',                     -- Khách VIP
        'special_event',                    -- Sự kiện đặc biệt
        'manager_directive',                -- Chỉ đạo cấp trên
        'system_error',                     -- Lỗi hệ thống
        'other'                             -- Khác
    )),
    reason_note TEXT NOT NULL,              -- Ghi chú bắt buộc (min 10 chars trong Go handler)
    hours_remaining NUMERIC(8,2),           -- Số giờ còn lại tại thời điểm override (audit purpose)
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID NOT NULL
);

-- Indexes
CREATE INDEX idx_voucher_overrides_user_voucher_id
    ON voucher_activation_overrides(user_voucher_id);
CREATE INDEX idx_voucher_overrides_approved_by
    ON voucher_activation_overrides(approved_by);
CREATE INDEX idx_voucher_overrides_branch_id
    ON voucher_activation_overrides(branch_id);
CREATE INDEX idx_voucher_overrides_created_at
    ON voucher_activation_overrides(created_at);

COMMENT ON TABLE voucher_activation_overrides IS 'Audit trail cho override thời gian chờ voucher — ref DEC-T02';
COMMENT ON COLUMN voucher_activation_overrides.order_id IS 'ĐH tạo sau override, NULL nếu chưa tạo ĐH';
COMMENT ON COLUMN voucher_activation_overrides.hours_remaining IS 'Số giờ còn lại tại thời điểm override, dùng cho audit';

Rollback:

sql

-- Migration: XXXXXXXXXX_voucher_time_control/down.sql (part 1)
DROP TABLE IF EXISTS voucher_activation_overrides;

4.2 ALTER TABLE: `voucher_campaigns` (Phase 1)

sql

-- Migration: XXXXXXXXXX_voucher_time_control/up.sql (part 2)

ALTER TABLE voucher_campaigns
    ADD COLUMN min_activation_hours INTEGER NOT NULL DEFAULT 24;

COMMENT ON COLUMN voucher_campaigns.min_activation_hours IS 'Thời gian chờ tối thiểu (giờ) từ kích hoạt đến sử dụng. 0 = không chặn — ref DEC-B02';

Rollback:

sql

-- Migration: XXXXXXXXXX_voucher_time_control/down.sql (part 2)
ALTER TABLE voucher_campaigns DROP COLUMN IF EXISTS min_activation_hours;

4.3 ALTER TABLE: `user_vouchers` (Phase 2)

sql

-- Migration: XXXXXXXXXX_voucher_staff_statistics/up.sql (part 1)

ALTER TABLE user_vouchers
    ADD COLUMN distributor_type TEXT CHECK (distributor_type IN ('internal_staff', 'affiliate'));

CREATE INDEX idx_user_vouchers_distributor_type
    ON user_vouchers(distributor_type)
    WHERE distributor_type IS NOT NULL;

sql

-- Migration: XXXXXXXXXX_alter_uv_assigned_affiliate/up.sql (Hybrid Distribution — P3)

ALTER TABLE user_vouchers
    ADD COLUMN assigned_affiliate_id UUID REFERENCES affiliate_user(id);

CREATE INDEX idx_uv_assigned_affiliate
    ON user_vouchers(assigned_affiliate_id)
    WHERE assigned_affiliate_id IS NOT NULL;

COMMENT ON COLUMN user_vouchers.assigned_affiliate_id IS
    'Pre-assign mã cho đối tác batch. NULL = chưa gán hoặc phát qua app';

COMMENT ON COLUMN user_vouchers.distributor_type IS 'Loại người phát: internal_staff hoặc affiliate. NULL = legacy data — ref DEC-T03';

Rollback:

sql

-- Migration: XXXXXXXXXX_voucher_staff_statistics/down.sql (part 1)
DROP INDEX IF EXISTS idx_user_vouchers_distributor_type;
ALTER TABLE user_vouchers DROP COLUMN IF EXISTS distributor_type;

4.4 NEW TABLE: `voucher_campaign_affiliates` (Phase 3)

sql

-- Migration: XXXXXXXXXX_voucher_campaign_affiliates/up.sql

CREATE TABLE voucher_campaign_affiliates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    campaign_id UUID NOT NULL REFERENCES voucher_campaigns(id),
    affiliate_user_id UUID NOT NULL REFERENCES affiliate_user(id),
    quota INTEGER,                          -- NULL = không giới hạn
    distributed_count INTEGER NOT NULL DEFAULT 0,
    distribution_method TEXT NOT NULL DEFAULT 'app'
        CHECK (distribution_method IN ('app', 'batch', 'both')),
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_by UUID,
    deleted_at TIMESTAMPTZ,                 -- Soft delete
    UNIQUE(campaign_id, affiliate_user_id)
);

-- Partial indexes (soft-delete aware)
CREATE INDEX idx_vca_campaign_id
    ON voucher_campaign_affiliates(campaign_id)
    WHERE deleted_at IS NULL;
CREATE INDEX idx_vca_affiliate_user_id
    ON voucher_campaign_affiliates(affiliate_user_id)
    WHERE deleted_at IS NULL;

-- Atomic quota check index (for concurrent updates)
CREATE INDEX idx_vca_quota_check
    ON voucher_campaign_affiliates(id, distributed_count)
    WHERE deleted_at IS NULL AND is_active = true;

-- Auto-update updated_at
CREATE TRIGGER update_voucher_campaign_affiliates_timestamp
    BEFORE UPDATE ON voucher_campaign_affiliates
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

COMMENT ON TABLE voucher_campaign_affiliates IS 'Junction table: chiến dịch ↔ đối tác affiliate — ref DEC-T04';
COMMENT ON COLUMN voucher_campaign_affiliates.quota IS 'NULL = unlimited. Atomic check: distributed_count < quota';
COMMENT ON COLUMN voucher_campaign_affiliates.distributed_count IS 'Counter — chỉ increment qua Go handler (atomic). Tính cả app + batch';
COMMENT ON COLUMN voucher_campaign_affiliates.distribution_method IS 'Kênh phát: app = real-time Partner App, batch = generate mã offline, both = cả hai';

Rollback:

sql

-- Migration: XXXXXXXXXX_voucher_campaign_affiliates/down.sql
DROP TRIGGER IF EXISTS update_voucher_campaign_affiliates_timestamp ON voucher_campaign_affiliates;
DROP TABLE IF EXISTS voucher_campaign_affiliates;

4.5 ALTER `app_setting`: VoucherSetting (Phase 1)

Thêm vào AppSettings JSONB (theo pattern DEC-T08):

json

{
  "AppSettings": {
    "VoucherSetting": {
      "DefaultMinActivationHours": 24,
      "DefaultManualVoucherExpiryDays": 30
    }
  }
}

Go struct (file: pkg/store/app_setting.go):

type VoucherSetting struct {
    DefaultMinActivationHours    int `json:"DefaultMinActivationHours"`
    DefaultManualVoucherExpiryDays int `json:"DefaultManualVoucherExpiryDays"`
}

// Thêm vào struct AppSettings hiện có
type AppSettings struct {
    // ... existing fields ...
    VoucherSetting VoucherSetting `json:"VoucherSetting"`
}

Migration: Không cần ALTER — app_setting.value là JSONB, chỉ cần UPDATE:

sql

-- Chạy 1 lần trong migration hoặc seed script
UPDATE app_setting
SET value = jsonb_set(
    value,
    '{AppSettings,VoucherSetting}',
    '{"DefaultMinActivationHours": 24, "DefaultManualVoucherExpiryDays": 30}'::jsonb
)
WHERE key = 'AppSettings';

4.6 SQL Functions

4.6.1 `get_voucher_staff_statistics` (Phase 2)

sql

-- Migration: XXXXXXXXXX_voucher_staff_statistics/up.sql (part 2)

-- Return type table
CREATE TABLE IF NOT EXISTS voucher_staff_statistics_result (
    staff_id UUID,
    staff_name TEXT,
    branch_name TEXT,
    distributor_type TEXT,
    total_distributed BIGINT,
    total_activated BIGINT,
    total_redeemed BIGINT,
    total_expired BIGINT,
    conversion_rate NUMERIC(5,2),
    total_revenue BIGINT,
    avg_days_to_redeem NUMERIC(8,1)
);

CREATE OR REPLACE FUNCTION get_voucher_staff_statistics(
    filter_campaign_id UUID DEFAULT NULL,
    filter_branch_ids UUID[] DEFAULT NULL,
    filter_from_date TIMESTAMPTZ DEFAULT NULL,
    filter_to_date TIMESTAMPTZ DEFAULT NULL
)
RETURNS SETOF voucher_staff_statistics_result
LANGUAGE plpgsql STABLE
AS $$
BEGIN
    RETURN QUERY
    WITH filtered_vouchers AS (
        SELECT
            uv.id,
            uv.staff_id,
            uv.status,
            uv.activated_at,
            uv.distributor_type,
            uv.branch_id
        FROM user_vouchers uv
        WHERE uv.deleted_at IS NULL
            AND uv.staff_id IS NOT NULL
            AND (filter_campaign_id IS NULL OR uv.campaign_id = filter_campaign_id)
            AND (filter_branch_ids IS NULL
                 OR CARDINALITY(filter_branch_ids) = 0
                 OR uv.branch_id = ANY(filter_branch_ids))
            AND (filter_from_date IS NULL
                 OR COALESCE(uv.issued_at, uv.activated_at) >= filter_from_date)
            AND (filter_to_date IS NULL
                 OR COALESCE(uv.issued_at, uv.activated_at) <= filter_to_date)
    ),
    -- 1 lần JOIN voucher_logs — pattern từ perf-fix-campaign-detail.md
    voucher_log_summary AS (
        SELECT
            vl.voucher_id,
            vl.action,
            vl.before_voucher_values,
            vl.created_at AS log_created_at,
            ROW_NUMBER() OVER (
                PARTITION BY vl.voucher_id
                ORDER BY vl.created_at DESC
            ) AS rn
        FROM voucher_logs vl
        JOIN filtered_vouchers fv ON vl.voucher_id = fv.id
        WHERE vl.action IN ('voucher_redeemed', 'voucher_restored')
    ),
    -- Net redeemed: last log is redeemed (not restored)
    net_redeemed AS (
        SELECT voucher_id, log_created_at AS redeemed_at,
               COALESCE(before_voucher_values, 0) AS revenue
        FROM voucher_log_summary
        WHERE rn = 1 AND action = 'voucher_redeemed'
    ),
    staff_info AS (
        SELECT u.id AS user_id, u.name AS staff_name
        FROM "user" u
    ),
    branch_info AS (
        SELECT b.id AS branch_id, b.name AS branch_name
        FROM branch b
        WHERE b.deleted_at IS NULL
    )
    SELECT
        fv.staff_id,
        COALESCE(si.staff_name, 'Unknown')::TEXT,
        COALESCE(bi.branch_name, 'Unknown')::TEXT,
        COALESCE(fv.distributor_type, 'internal_staff')::TEXT,
        COUNT(fv.id)::BIGINT AS total_distributed,
        COUNT(fv.id) FILTER (WHERE fv.status IN ('activated', 'redeemed', 'expired'))::BIGINT AS total_activated,
        COUNT(nr.voucher_id)::BIGINT AS total_redeemed,
        COUNT(fv.id) FILTER (WHERE fv.status = 'expired')::BIGINT AS total_expired,
        CASE
            WHEN COUNT(fv.id) = 0 THEN NULL
            ELSE ROUND((COUNT(nr.voucher_id)::NUMERIC / COUNT(fv.id)) * 100, 2)
        END AS conversion_rate,
        COALESCE(SUM(nr.revenue), 0)::BIGINT AS total_revenue,
        CASE
            WHEN COUNT(nr.voucher_id) = 0 THEN NULL
            ELSE ROUND(AVG(EXTRACT(EPOCH FROM (nr.redeemed_at - fv.activated_at)) / 86400)::NUMERIC, 1)
        END AS avg_days_to_redeem
    FROM filtered_vouchers fv
    LEFT JOIN net_redeemed nr ON nr.voucher_id = fv.id
    LEFT JOIN staff_info si ON si.user_id = fv.staff_id
    LEFT JOIN branch_info bi ON bi.branch_id = fv.branch_id
    GROUP BY fv.staff_id, si.staff_name, bi.branch_name, fv.distributor_type
    ORDER BY total_distributed DESC;
END;
$$;

COMMENT ON FUNCTION get_voucher_staff_statistics IS 'Thống kê phát voucher theo nhân viên — pattern từ get_voucher_branch_statistics (migration 1766800000000)';

4.6.2 `get_voucher_usage_cycle_statistics` (Phase 4)

sql

-- Migration: XXXXXXXXXX_voucher_usage_cycle/up.sql (part 1)

CREATE TABLE IF NOT EXISTS voucher_usage_cycle_statistics_result (
    group_key TEXT,
    group_name TEXT,
    avg_cycle_days NUMERIC(8,1),
    min_cycle_days NUMERIC(8,1),
    median_cycle_days NUMERIC(8,1),
    usage_rate NUMERIC(5,2),
    total_activated BIGINT,
    total_redeemed BIGINT,
    total_expired BIGINT,
    total_pending BIGINT
);

CREATE OR REPLACE FUNCTION get_voucher_usage_cycle_statistics(
    filter_campaign_ids UUID[] DEFAULT NULL,
    filter_branch_ids UUID[] DEFAULT NULL,
    filter_staff_ids UUID[] DEFAULT NULL,
    filter_from_date TIMESTAMPTZ DEFAULT NULL,
    filter_to_date TIMESTAMPTZ DEFAULT NULL
)
RETURNS SETOF voucher_usage_cycle_statistics_result
LANGUAGE plpgsql STABLE
AS $$
BEGIN
    RETURN QUERY
    WITH filtered_vouchers AS (
        SELECT
            uv.id,
            uv.campaign_id,
            uv.staff_id,
            uv.branch_id,
            uv.status,
            uv.activated_at
        FROM user_vouchers uv
        WHERE uv.deleted_at IS NULL
            AND uv.activated_at IS NOT NULL
            AND (filter_campaign_ids IS NULL
                 OR CARDINALITY(filter_campaign_ids) = 0
                 OR uv.campaign_id = ANY(filter_campaign_ids))
            AND (filter_branch_ids IS NULL
                 OR CARDINALITY(filter_branch_ids) = 0
                 OR uv.branch_id = ANY(filter_branch_ids))
            AND (filter_staff_ids IS NULL
                 OR CARDINALITY(filter_staff_ids) = 0
                 OR uv.staff_id = ANY(filter_staff_ids))
            AND (filter_from_date IS NULL OR uv.activated_at >= filter_from_date)
            AND (filter_to_date IS NULL OR uv.activated_at <= filter_to_date)
    ),
    -- Last redeem log per voucher (net, after restores) — ref DEC-T05
    last_redeem AS (
        SELECT DISTINCT ON (vl.voucher_id)
            vl.voucher_id,
            vl.created_at AS redeemed_at
        FROM voucher_logs vl
        JOIN filtered_vouchers fv ON vl.voucher_id = fv.id
        WHERE vl.action = 'voucher_redeemed'
        ORDER BY vl.voucher_id, vl.created_at DESC
    ),
    cycle_data AS (
        SELECT
            fv.id AS voucher_id,
            fv.campaign_id,
            CASE
                WHEN lr.redeemed_at IS NOT NULL
                THEN EXTRACT(EPOCH FROM (lr.redeemed_at - fv.activated_at)) / 86400
                ELSE NULL
            END AS cycle_days,
            fv.status
        FROM filtered_vouchers fv
        LEFT JOIN last_redeem lr ON lr.voucher_id = fv.id
    )
    SELECT
        'all'::TEXT AS group_key,
        'Tổng quan'::TEXT AS group_name,
        ROUND(AVG(cd.cycle_days)::NUMERIC, 1) AS avg_cycle_days,
        ROUND(MIN(cd.cycle_days)::NUMERIC, 1) AS min_cycle_days,
        ROUND(
            (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY cd.cycle_days))::NUMERIC,
            1
        ) AS median_cycle_days,
        CASE
            WHEN COUNT(*) = 0 THEN NULL
            ELSE ROUND(
                (COUNT(*) FILTER (WHERE cd.cycle_days IS NOT NULL)::NUMERIC / COUNT(*)) * 100,
                2
            )
        END AS usage_rate,
        COUNT(*)::BIGINT AS total_activated,
        COUNT(*) FILTER (WHERE cd.cycle_days IS NOT NULL)::BIGINT AS total_redeemed,
        COUNT(*) FILTER (WHERE cd.status = 'expired')::BIGINT AS total_expired,
        COUNT(*) FILTER (WHERE cd.status = 'activated')::BIGINT AS total_pending
    FROM cycle_data cd;
END;
$$;

COMMENT ON FUNCTION get_voucher_usage_cycle_statistics IS 'KPI tổng quan chu kỳ sử dụng voucher — ref DEC-T06, FORMULA-003, FORMULA-004';

4.6.3 `get_voucher_usage_cycle_distribution` (Phase 4)

sql

-- Migration: XXXXXXXXXX_voucher_usage_cycle/up.sql (part 2)

CREATE TABLE IF NOT EXISTS voucher_usage_cycle_distribution_result (
    group_key TEXT,
    group_name TEXT,
    bucket TEXT,
    count BIGINT,
    percentage NUMERIC(5,1)
);

CREATE OR REPLACE FUNCTION get_voucher_usage_cycle_distribution(
    filter_campaign_ids UUID[] DEFAULT NULL,
    filter_branch_ids UUID[] DEFAULT NULL,
    filter_staff_ids UUID[] DEFAULT NULL,
    filter_from_date TIMESTAMPTZ DEFAULT NULL,
    filter_to_date TIMESTAMPTZ DEFAULT NULL
)
RETURNS SETOF voucher_usage_cycle_distribution_result
LANGUAGE plpgsql STABLE
AS $$
BEGIN
    RETURN QUERY
    WITH filtered_vouchers AS (
        SELECT
            uv.id,
            uv.campaign_id,
            uv.staff_id,
            uv.branch_id,
            uv.status,
            uv.activated_at
        FROM user_vouchers uv
        WHERE uv.deleted_at IS NULL
            AND uv.activated_at IS NOT NULL
            AND (filter_campaign_ids IS NULL
                 OR CARDINALITY(filter_campaign_ids) = 0
                 OR uv.campaign_id = ANY(filter_campaign_ids))
            AND (filter_branch_ids IS NULL
                 OR CARDINALITY(filter_branch_ids) = 0
                 OR uv.branch_id = ANY(filter_branch_ids))
            AND (filter_staff_ids IS NULL
                 OR CARDINALITY(filter_staff_ids) = 0
                 OR uv.staff_id = ANY(filter_staff_ids))
            AND (filter_from_date IS NULL OR uv.activated_at >= filter_from_date)
            AND (filter_to_date IS NULL OR uv.activated_at <= filter_to_date)
    ),
    last_redeem AS (
        SELECT DISTINCT ON (vl.voucher_id)
            vl.voucher_id,
            vl.created_at AS redeemed_at
        FROM voucher_logs vl
        JOIN filtered_vouchers fv ON vl.voucher_id = fv.id
        WHERE vl.action = 'voucher_redeemed'
        ORDER BY vl.voucher_id, vl.created_at DESC
    ),
    cycle_data AS (
        SELECT
            fv.id AS voucher_id,
            CASE
                WHEN lr.redeemed_at IS NOT NULL
                THEN EXTRACT(EPOCH FROM (lr.redeemed_at - fv.activated_at)) / 86400
                ELSE NULL
            END AS cycle_days
        FROM filtered_vouchers fv
        LEFT JOIN last_redeem lr ON lr.voucher_id = fv.id
    ),
    bucketed AS (
        SELECT
            CASE
                WHEN cycle_days IS NULL THEN 'pending'
                WHEN cycle_days BETWEEN 0 AND 3 THEN '0-3'
                WHEN cycle_days BETWEEN 4 AND 7 THEN '4-7'
                WHEN cycle_days BETWEEN 8 AND 14 THEN '8-14'
                WHEN cycle_days BETWEEN 15 AND 30 THEN '15-30'
                WHEN cycle_days BETWEEN 31 AND 60 THEN '31-60'
                WHEN cycle_days > 60 THEN '60+'
            END AS bucket
        FROM cycle_data
    ),
    total AS (
        SELECT COUNT(*) AS total_count FROM bucketed
    ),
    all_buckets AS (
        SELECT unnest(ARRAY['0-3', '4-7', '8-14', '15-30', '31-60', '60+', 'pending']) AS bucket
    )
    SELECT
        'all'::TEXT AS group_key,
        'Tổng quan'::TEXT AS group_name,
        ab.bucket::TEXT,
        COALESCE(COUNT(b.bucket), 0)::BIGINT AS count,
        CASE
            WHEN t.total_count = 0 THEN 0
            ELSE ROUND((COALESCE(COUNT(b.bucket), 0)::NUMERIC / t.total_count) * 100, 1)
        END AS percentage
    FROM all_buckets ab
    CROSS JOIN total t
    LEFT JOIN bucketed b ON b.bucket = ab.bucket
    GROUP BY ab.bucket, t.total_count
    ORDER BY CASE ab.bucket
        WHEN '0-3' THEN 1
        WHEN '4-7' THEN 2
        WHEN '8-14' THEN 3
        WHEN '15-30' THEN 4
        WHEN '31-60' THEN 5
        WHEN '60+' THEN 6
        WHEN 'pending' THEN 7
    END;
END;
$$;

COMMENT ON FUNCTION get_voucher_usage_cycle_distribution IS 'Phân bổ chu kỳ sử dụng theo bucket — ref DEC-T07, FORMULA-005';

4.7 ER Diagram (tables mới + quan hệ)

┌─────────────────────────┐     ┌─────────────────────────────────────┐
│   voucher_campaigns     │     │          user_vouchers              │
│─────────────────────────│     │─────────────────────────────────────│
│ id                  PK  │◄────│ campaign_id               FK       │
│ ...existing fields...   │     │ id                        PK       │
│ min_activation_hours    │     │ staff_id                           │
│ (NEW — P1)         INT  │     │ activated_at                       │
└───────────┬─────────────┘     │ expired_at                         │
            │                   │ status                             │
            │                   │ distributor_type (NEW — P2) TEXT   │
            │                   │ ...existing fields...              │
            │                   └──────────┬──────────────────────────┘
            │                              │
            │                              │ user_voucher_id FK
            │                              ▼
            │              ┌───────────────────────────────────────┐
            │              │   voucher_activation_overrides        │
            │              │   (NEW — P1)                          │
            │              │───────────────────────────────────────│
            │              │ id                              PK   │
            │              │ user_voucher_id                 FK   │
            │              │ order_id                             │
            │              │ approved_by                          │
            │              │ branch_id                            │
            │              │ reason_code                          │
            │              │ reason_note                          │
            │              │ hours_remaining                      │
            │              │ created_at, created_by               │
            │              └───────────────────────────────────────┘
            │
            │ campaign_id FK
            ▼
┌─────────────────────────────────────────┐     ┌─────────────────────────┐
│   voucher_campaign_affiliates           │     │    affiliate_user       │
│   (NEW — P3)                            │     │   (existing)            │
│─────────────────────────────────────────│     │─────────────────────────│
│ id                                 PK   │     │ id                 PK  │
│ campaign_id                        FK   │────►│ ...existing fields...  │
│ affiliate_user_id                  FK   │     └─────────────────────────┘
│ quota                                   │
│ distributed_count                       │
│ is_active                               │
│ created_at, created_by                  │
│ updated_at, updated_by                  │
│ deleted_at                              │
│ UNIQUE(campaign_id, affiliate_user_id)  │
└─────────────────────────────────────────┘

C5. API Contracts & Hasura Metadata

5.1 Hasura Action: `approve_voucher_override` (Phase 1)

Endpoint: POST /actions/approve_voucher_override

Request:

graphql

mutation ApproveVoucherOverride($input: ApproveVoucherOverrideInput!) {
  approve_voucher_override(input: $input) {
    override_id
    hours_remaining
  }
}

input ApproveVoucherOverrideInput {
  user_voucher_id: uuid!
  reason_code: String!    # enum: vip_customer | special_event | manager_directive | system_error | other
  reason_note: String!    # min 10 chars
}

Response (success):

json

{
  "override_id": "uuid-of-override",
  "hours_remaining": 22.15
}

Go handler signature (file: services/ecommerce-api/action/approve_voucher_override.go):

type ApproveVoucherOverrideInput struct {
    UserVoucherID string `json:"user_voucher_id" binding:"required,uuid"`
    ReasonCode    string `json:"reason_code" binding:"required,oneof=vip_customer special_event manager_directive system_error other"`
    ReasonNote    string `json:"reason_note" binding:"required,min=10"`
}

type ApproveVoucherOverrideOutput struct {
    OverrideID     string  `json:"override_id"`
    HoursRemaining float64 `json:"hours_remaining"`
}

func (h *ActionHandler) ApproveVoucherOverride(c *gin.Context) {
    // 1. Parse input
    // 2. Validate role: ctx.Access.Role must be Manager or Admin
    // 3. Fetch user_voucher + campaign (JOIN voucher_campaigns for min_activation_hours)
    // 4. Check voucher is in time-blocked state (FORMULA-001)
    // 5. Calculate hours_remaining
    // 6. INSERT into voucher_activation_overrides
    // 7. Log: INFO {override_id, voucher_id, approved_by, branch_id, reason_code}
    // 8. Return override_id + hours_remaining
}

Validation logic chi tiết:

Step	Check	Error nếu fail
1	`user_voucher_id` phải tồn tại + `deleted_at IS NULL`	`VOUCHER_NOT_FOUND`
2	Voucher phải ở status `activated`	`VOUCHER_NOT_FOUND` (generic, không leak state)
3	Caller role = Manager hoặc Admin (`ctx.Access.Role`)	`OVERRIDE_UNAUTHORIZED`
4	Manager chỉ approve voucher thuộc branch mình (`ctx.Access.BranchIds`)	`OVERRIDE_UNAUTHORIZED`
5	Voucher phải đang bị time-block (hours_elapsed < min_activation_hours)	`VOUCHER_NOT_BLOCKED` (idempotent — voucher đã đủ thời gian)
6	Chưa có override record active cho voucher này	Trả override_id cũ (idempotent)
7	`reason_note` >= 10 characters	Validation error (binding)

5.2 Hasura Action Definition YAML

yaml

# controller/metadata/actions.yaml — append
- name: approve_voucher_override
  definition:
    kind: synchronous
    handler: '{{ECOMMERCE_API_URL}}/actions'
    forward_client_headers: true
  permissions:
    - role: user
  comment: 'Override thời gian chờ voucher — P1, ref DEC-T02'

5.3 GraphQL Queries mới

Phase 1 — Override Dashboard

graphql

# Danh sách override (VoucherOverrideDashboard.tsx)
query GetVoucherOverrides(
  $where: voucher_activation_overrides_bool_exp!
  $offset: Int
  $limit: Int
  $order_by: [voucher_activation_overrides_order_by!]
) {
  voucher_activation_overrides(
    where: $where
    offset: $offset
    limit: $limit
    order_by: $order_by
  ) {
    id
    user_voucher_id
    user_voucher {
      code
      voucher_name
      campaign_id
      voucher_campaign {
        name
      }
    }
    order_id
    approved_by
    approved_by_user {
      name
    }
    branch_id
    branch {
      name
    }
    reason_code
    reason_note
    hours_remaining
    created_at
  }
  voucher_activation_overrides_aggregate(where: $where) {
    aggregate {
      count
    }
  }
}

Phase 1 — Settings

graphql

# Đọc VoucherSetting (VoucherSettingsSection.tsx)
query GetVoucherSettings {
  app_setting(where: { key: { _eq: "AppSettings" } }) {
    value
  }
}

# Cập nhật VoucherSetting — dùng Hasura action update_app_setting hiện có
mutation UpdateAppSetting($key: String!, $value: jsonb!) {
  update_app_setting(
    where: { key: { _eq: $key } }
    _set: { value: $value }
  ) {
    affected_rows
  }
}

Phase 2 — Staff Statistics

graphql

# Thống kê nhân viên (VoucherStaffStatisticsTab.tsx)
query GetVoucherStaffStatistics(
  $args: get_voucher_staff_statistics_args!
  $order_by: [voucher_staff_statistics_result_order_by!]
  $offset: Int
  $limit: Int
) {
  get_voucher_staff_statistics(
    args: $args
    order_by: $order_by
    offset: $offset
    limit: $limit
  ) {
    staff_id
    staff_name
    branch_name
    distributor_type
    total_distributed
    total_activated
    total_redeemed
    total_expired
    conversion_rate
    total_revenue
    avg_days_to_redeem
  }
  get_voucher_staff_statistics_aggregate(args: $args) {
    aggregate {
      count
    }
  }
}

# Drill-down per NV (StaffDrillDownDialog.tsx)
query GetStaffVoucherDetail(
  $where: user_vouchers_bool_exp!
  $offset: Int
  $limit: Int
  $order_by: [user_vouchers_order_by!]
) {
  user_vouchers(
    where: $where
    offset: $offset
    limit: $limit
    order_by: $order_by
  ) {
    id
    code
    voucher_name
    customer {
      name
      phone
    }
    status
    activated_at
    redeemed_at
    expired_at
    distributor_type
  }
  user_vouchers_aggregate(where: $where) {
    aggregate {
      count
    }
  }
}

Phase 3 — Affiliates

graphql

# Danh sách đối tác trong campaign (AffiliateAssignStep.tsx)
query GetCampaignAffiliates(
  $campaign_id: uuid!
) {
  voucher_campaign_affiliates(
    where: {
      campaign_id: { _eq: $campaign_id }
      deleted_at: { _is_null: true }
    }
  ) {
    id
    affiliate_user_id
    affiliate_user {
      name
      phone
      code
    }
    quota
    distributed_count
    is_active
  }
}

# Tìm kiếm affiliate users (autocomplete trong AffiliateAssignStep)
query SearchAffiliateUsers($search: String!) {
  affiliate_user(
    where: {
      _or: [
        { name: { _ilike: $search } }
        { phone: { _ilike: $search } }
        { code: { _ilike: $search } }
      ]
      deleted_at: { _is_null: true }
      is_active: { _eq: true }
    }
    limit: 20
  ) {
    id
    name
    phone
    code
  }
}

Phase 4 — Usage Cycle

graphql

# KPI chu kỳ sử dụng (VoucherUsageCycleTab.tsx)
query GetVoucherUsageCycleStatistics(
  $args: get_voucher_usage_cycle_statistics_args!
) {
  get_voucher_usage_cycle_statistics(args: $args) {
    group_key
    group_name
    avg_cycle_days
    min_cycle_days
    median_cycle_days
    usage_rate
    total_activated
    total_redeemed
    total_expired
    total_pending
  }
}

# Phân bổ bucket (CycleBucketChart.tsx)
query GetVoucherUsageCycleDistribution(
  $args: get_voucher_usage_cycle_distribution_args!
) {
  get_voucher_usage_cycle_distribution(args: $args) {
    group_key
    group_name
    bucket
    count
    percentage
  }
}

5.4 Hasura Table Metadata

`public_voucher_activation_overrides.yaml` (P1)

yaml

table:
  name: voucher_activation_overrides
  schema: public
object_relationships:
  - name: user_voucher
    using:
      foreign_key_constraint_on: user_voucher_id
  - name: approved_by_user
    using:
      manual_configuration:
        remote_table:
          name: user
          schema: public
        column_mapping:
          approved_by: id
  - name: branch
    using:
      manual_configuration:
        remote_table:
          name: branch
          schema: public
        column_mapping:
          branch_id: id
select_permissions:
  - role: user
    permission:
      columns:
        - id
        - user_voucher_id
        - order_id
        - approved_by
        - branch_id
        - reason_code
        - reason_note
        - hours_remaining
        - created_at
        - created_by
      filter: {}
      # Branch scoping tại FE level (pattern giống report)
      # FE WHERE builder: branch_id IN globalStore.account.branches

`public_voucher_campaign_affiliates.yaml` (P3)

yaml

table:
  name: voucher_campaign_affiliates
  schema: public
object_relationships:
  - name: voucher_campaign
    using:
      foreign_key_constraint_on: campaign_id
  - name: affiliate_user
    using:
      foreign_key_constraint_on: affiliate_user_id
select_permissions:
  - role: user
    permission:
      columns:
        - id
        - campaign_id
        - affiliate_user_id
        - quota
        - distributed_count
        - is_active
        - created_at
        - updated_at
      filter:
        deleted_at:
          _is_null: true
insert_permissions:
  - role: user
    permission:
      columns:
        - campaign_id
        - affiliate_user_id
        - quota
        - is_active
      check: {}
      # Insert qua Go handler (action), không cho FE insert trực tiếp
      # Permission này dùng cho admin tooling / debug
update_permissions:
  - role: user
    permission:
      columns:
        - quota
        - is_active
        - updated_by
      filter:
        deleted_at:
          _is_null: true

Function Metadata (P2, P4)

yaml

# public_get_voucher_staff_statistics.yaml
function:
  name: get_voucher_staff_statistics
  schema: public
configuration:
  session_argument: hasura_session
  exposed_as: query

# public_get_voucher_usage_cycle_statistics.yaml
function:
  name: get_voucher_usage_cycle_statistics
  schema: public
configuration:
  session_argument: hasura_session
  exposed_as: query

# public_get_voucher_usage_cycle_distribution.yaml
function:
  name: get_voucher_usage_cycle_distribution
  schema: public
configuration:
  session_argument: hasura_session
  exposed_as: query

5.5 Error Contract

Scenario	Error code	HTTP Status	GraphQL Extension	FE handling
Voucher chưa đủ thời gian chờ	`VOUCHER_TIME_BLOCKED`	200 (GraphQL error)	`extensions.code`, `extensions.hours_remaining`, `extensions.available_at`	Show `VoucherTimeBlockDialog` với thông tin chi tiết
Override — không có quyền	`OVERRIDE_UNAUTHORIZED`	200 (GraphQL error)	`extensions.code`	Ẩn nút "Xác nhận duyệt", chỉ hiện "Đóng"
Override — voucher không tồn tại	`VOUCHER_NOT_FOUND`	200 (GraphQL error)	`extensions.code`	Show error toast "Voucher không tồn tại hoặc đã bị xóa"
Override — voucher không bị chặn	`VOUCHER_NOT_BLOCKED`	200 (GraphQL error)	`extensions.code`	Show info toast "Voucher đã đủ thời gian, tạo ĐH bình thường"
Đối tác hết quota	`AFFILIATE_QUOTA_EXCEEDED`	200 (GraphQL error)	`extensions.code`, `extensions.quota`, `extensions.distributed_count`	Show error message "Đối tác đã hết quota phát voucher"
Campaign không tồn tại	`CAMPAIGN_NOT_FOUND`	200 (GraphQL error)	`extensions.code`	Show error state trên page

Error response format (Go):

// Pattern chuẩn Diva — return GraphQL-compatible error
func returnActionError(c *gin.Context, code string, message string, extensions map[string]interface{}) {
    c.JSON(http.StatusBadRequest, gin.H{
        "message": message,
        "extensions": gin.H{
            "code": code,
            // merge extensions
        },
    })
}

// Ví dụ: VOUCHER_TIME_BLOCKED
returnActionError(c, "VOUCHER_TIME_BLOCKED",
    fmt.Sprintf("Voucher cần chờ thêm %.0f giờ nữa mới được sử dụng", hoursRemaining),
    map[string]interface{}{
        "hours_remaining": hoursRemaining,
        "available_at": availableAt.Format(time.RFC3339),
    },
)

C6. Scheduler / Cron Jobs

6.1 Cron hiện có (không đổi, chỉ leverage)

Cron	File	Schedule	Liên quan
`expired_vouchers`	`services/ecommerce-api/scheduler/expired_vouchers.go`	Daily 00:00	P1 — voucher manual có `expired_at` sẽ được cron này xử lý tự động

6.2 Cron mới: Không cần

Không cần thêm scheduler mới:

Override cleanup: Không cần — override records là audit trail, không xóa
Affiliate quota reset: Không cần — quota per campaign, không reset theo chu kỳ
Statistics cache: Không cần — DB-level aggregation đủ nhanh (target < 2s)

6.3 Notification triggers (event-based, không phải cron)

Event	Trigger	Xử lý
Override created	Go handler sau INSERT thành công	Call notification-api `noti_voucher_override_created`
Voucher manual sắp hết hạn (3 ngày)	Cron `expired_vouchers` check `expired_at - 3 days`	Push `noti_voucher_manual_expiring` (cần thêm logic vào cron hiện có)
Affiliate quota > 90%	Go handler sau quota increment	Check `distributed_count / quota > 0.9` → call `noti_affiliate_quota_warning`

Lưu ý P1: Cần sửa expired_vouchers.go để thêm check "sắp hết hạn 3 ngày" cho notification. Logic: WHERE expired_at BETWEEN NOW() AND NOW() + INTERVAL '3 days' AND status = 'activated' → gửi notification nếu chưa gửi.

C7. Migration Plan

7.1 Migration Order

┌──────────────────────────────────────────────────────────────────────┐
│  DEPLOY ORDER (mỗi step = 1 migration file riêng)                  │
├──────────────────────────────────────────────────────────────────────┤
│                                                                      │
│  [PERF-FIX] ──► Prerequisite, deploy trước P2/P4                   │
│    ├── PERF-01: CREATE INDEX CONCURRENTLY expression index           │
│    └── PERF-02: CREATE OR REPLACE FUNCTION (merge CTEs)             │
│                                                                      │
│  [P1] ──► Độc lập, deploy song song với PERF-FIX                   │
│    ├── P1-migration: ALTER voucher_campaigns                         │
│    │                 + CREATE voucher_activation_overrides            │
│    │                 + UPDATE app_setting (VoucherSetting)            │
│    ├── P1-metadata: Hasura tables + action YAML                      │
│    └── P1-go: Go handler + struct updates                            │
│                                                                      │
│  [P2] ──► Sau PERF-FIX deploy thành công                           │
│    ├── P2-migration: ALTER user_vouchers                             │
│    │                 + CREATE FUNCTION get_voucher_staff_statistics   │
│    │                 + CREATE TABLE voucher_staff_statistics_result   │
│    ├── P2-metadata: Hasura function + column permission update       │
│    └── P2-go: Go struct update                                       │
│                                                                      │
│  [P3] ──► Sau P2                                                    │
│    ├── P3-migration: CREATE voucher_campaign_affiliates              │
│    ├── P3-metadata: Hasura table YAML                                │
│    └── P3-go: Campaign handlers + quota check                        │
│                                                                      │
│  [P4] ──► Sau PERF-FIX deploy thành công                           │
│    ├── P4-migration: CREATE FUNCTION get_voucher_usage_cycle_*       │
│    │                 + CREATE TABLE result types                      │
│    ├── P4-metadata: Hasura function YAML                             │
│    └── P4-go: Không cần (functions gọi qua Hasura)                  │
│                                                                      │
└──────────────────────────────────────────────────────────────────────┘

7.2 Migration File Naming Convention

Theo pattern Diva:

controller/migrations/ecommerce/{unix_timestamp_ms}_{description}/up.sql
controller/migrations/ecommerce/{unix_timestamp_ms}_{description}/down.sql

Migration	File name pattern	Phase
Expression index	`XXXXXXXXXX_add_expression_index_user_vouchers_coalesce/`	PERF
Merge CTEs	`XXXXXXXXXX_optimize_voucher_analytics_summary_merge_ctes/`	PERF
Time control	`XXXXXXXXXX_voucher_time_control/`	P1
Staff statistics	`XXXXXXXXXX_voucher_staff_statistics/`	P2
Campaign affiliates	`XXXXXXXXXX_voucher_campaign_affiliates/`	P3
Usage cycle	`XXXXXXXXXX_voucher_usage_cycle_functions/`	P4

7.3 Rollback Strategy

Phase	Rollback	Risk
PERF-FIX	DROP INDEX + CREATE OR REPLACE FUNCTION (revert to old version)	Low — additive
P1	DROP TABLE `voucher_activation_overrides`, DROP COLUMN `min_activation_hours`, revert `app_setting`	Medium — cần revert Go handler routing
P2	DROP COLUMN `distributor_type`, DROP FUNCTION, DROP TABLE result type	Low — additive
P3	DROP TABLE `voucher_campaign_affiliates`	Low — new table
P4	DROP FUNCTIONs, DROP TABLE result types	Low — new functions

7.4 Data Backfill

Scenario	Migration/Script	Phase
Existing campaigns get `min_activation_hours = 24`	Handled by `DEFAULT 24` in ALTER	P1
Existing `user_vouchers.distributor_type` = NULL for legacy	No backfill — NULL = legacy, OK	P2
`app_setting` VoucherSetting init	UPDATE in migration	P1

C8. Security

8.1 Role-Based Access Control

Override Action (P1)

Aspect	Rule
Who can approve	Manager (branch scope), Admin (all)
Go check	`ctx.Access.Role` must be `manager` or `admin`
Branch scope	Manager chỉ approve voucher thuộc `ctx.Access.BranchIds`
Audit	Mọi override ghi vào `voucher_activation_overrides` (immutable)

Override Dashboard (P1)

Role	Quyền	Branch scope
ITLeader	Xem tất cả	All branches
BOD	Xem tất cả	All branches
AccountantLeader	Xem tất cả	All branches
Manager	Xem override tại chi nhánh	`branch_id IN account.branches` (FE WHERE builder)
Staff	KHÔNG truy cập	Menu ẩn

Staff Statistics (P2)

Role	Quyền	Scope
ITLeader	Xem tất cả NV	All branches
ITStaff	Xem tất cả NV	All branches
BOD	Xem tất cả NV	All branches
AccountantLeader	Xem tất cả NV	All branches
Manager	Xem NV tại chi nhánh	`branch_id IN account.branches`
Staff	Xem thống kê bản thân (Partner app)	`staff_id = X-Hasura-User-Id`

Affiliate Management (P3)

Role	Quyền
ITLeader	Gán/gỡ đối tác, xem quota
ITStaff	Gán/gỡ đối tác, xem quota
BOD	Xem quota
Manager	Xem quota (branch scope)
Staff	KHÔNG truy cập

8.2 Data Security

Concern	Mitigation
Override abuse	Dashboard giám sát + alert > 50/tháng (ref A8)
Quota tampering	`distributed_count` chỉ tăng qua Go handler (atomic), không cho FE update trực tiếp
Audit immutability	`voucher_activation_overrides` không có UPDATE/DELETE permission trong Hasura
PII trong report	Staff name hiển thị theo role access, Partner app chỉ thấy data bản thân
Branch isolation	FE WHERE builder `branch_id IN globalStore.account.branches` + function params `filter_branch_ids`

8.3 Timezone Security

Rule	Implementation
Tất cả time comparison dùng `Asia/Ho_Chi_Minh`	Go: `time.LoadLocation("Asia/Ho_Chi_Minh")`
DB function dùng timezone-aware	`NOW() AT TIME ZONE 'Asia/Ho_Chi_Minh'`
FE hiển thị format Vietnam	`dayjs.tz('Asia/Ho_Chi_Minh')`
TIMESTAMPTZ stored	Tất cả cột time dùng `TIMESTAMPTZ`, không `TIMESTAMP`

C9. Non-Functional Requirements (NFR)

9.1 Performance Targets

Scenario	Target	Baseline	Đo bằng
Time validation trong `create_order`	< 50ms additional overhead	Hiện tại order creation ~ 200ms	Go benchmark test
Override dashboard (list + pagination)	< 500ms (20 rows/page)	N/A (new)	Browser Network tab
Staff statistics function (1000 NV × 50k vouchers)	< 2s	Pattern `get_voucher_branch_statistics` ~ 1.5s cho 100 branches	`EXPLAIN ANALYZE` + Grafana
Cycle statistics function (100k vouchers)	< 3s	N/A (new)	`EXPLAIN ANALYZE` + Grafana
Cycle distribution function (100k vouchers)	< 3s	N/A (new)	`EXPLAIN ANALYZE` + Grafana
Export Excel staff statistics (5000 rows)	Async trigger < 1s, file ready < 30s	Pattern export-api hiện có	export-api logs

9.2 Scalability

Resource	Current Scale	Projected Scale (6 months)	Strategy
`user_vouchers` rows	~3M	~5M	Partial indexes + function optimization
`voucher_logs` rows	~6M	~10M	Index `(voucher_id, action, created_at DESC)` đã có
`voucher_activation_overrides` rows	0 (new)	~500/tháng (est.)	Lightweight, index on `created_at` đủ
`voucher_campaign_affiliates` rows	0 (new)	~200 (campaigns × affiliates)	Negligible

9.3 Capacity Model

Bắt buộc vì user_vouchers > 100k rows (ref: CLAUDE.md)

Scenario: Staff statistics cho campaign lớn

Input:
- 1 campaign, 1000 staff, 50,000 vouchers
- voucher_logs: ~150,000 rows (avg 3 logs/voucher)

Query plan (estimated):
1. filtered_vouchers CTE: Index scan user_vouchers(campaign_id) → 50,000 rows
2. voucher_log_summary CTE: Index scan voucher_logs(voucher_id, action, created_at DESC) → 150,000 rows
3. ROW_NUMBER() window: In-memory sort per voucher_id → ~150,000 rows
4. net_redeemed filter: rn = 1 AND action = 'voucher_redeemed' → ~15,000 rows
5. GROUP BY staff_id: Hash aggregate → 1,000 rows
6. LEFT JOIN staff + branch: Lookup → 1,000 rows

Estimated time: 1.2-1.8s (within 2s target)
Memory: ~50MB work_mem (acceptable)

Scenario: Cycle distribution cho multi-campaign

Input:
- 5 campaigns, all branches, 100,000 vouchers
- voucher_logs: ~300,000 rows

Query plan (estimated):
1. filtered_vouchers CTE: Index scan(campaign_id ANY) → 100,000 rows
2. last_redeem CTE: DISTINCT ON(voucher_id) → 100,000 rows scan, ~30,000 results
3. cycle_data: LEFT JOIN → 100,000 rows
4. bucketed: CASE → 100,000 rows
5. GROUP BY bucket: 7 rows
6. percentage: Simple math

Estimated time: 2.0-2.8s (within 3s target)
Memory: ~100MB work_mem (acceptable)

9.4 Indexes Summary (tất cả phases)

Table	Index	Columns	Type	Phase
`user_vouchers`	`idx_user_vouchers_campaign_coalesce_date`	`(campaign_id, COALESCE(issued_at, activated_at) DESC)`	Expression	PERF
`user_vouchers`	`idx_user_vouchers_distributor_type`	`(distributor_type) WHERE distributor_type IS NOT NULL`	Partial	P2
`voucher_activation_overrides`	`idx_voucher_overrides_user_voucher_id`	`(user_voucher_id)`	B-tree	P1
`voucher_activation_overrides`	`idx_voucher_overrides_approved_by`	`(approved_by)`	B-tree	P1
`voucher_activation_overrides`	`idx_voucher_overrides_branch_id`	`(branch_id)`	B-tree	P1
`voucher_activation_overrides`	`idx_voucher_overrides_created_at`	`(created_at)`	B-tree	P1
`voucher_campaign_affiliates`	`idx_vca_campaign_id`	`(campaign_id) WHERE deleted_at IS NULL`	Partial	P3
`voucher_campaign_affiliates`	`idx_vca_affiliate_user_id`	`(affiliate_user_id) WHERE deleted_at IS NULL`	Partial	P3
`voucher_campaign_affiliates`	`idx_vca_quota_check`	`(id, distributed_count) WHERE deleted_at IS NULL AND is_active = true`	Partial	P3

9.5 Existing Indexes (reference — đã có, không đổi)

Table	Index	Columns
`user_vouchers`	`idx_user_vouchers_campaign_source_status`	`(campaign_id, channel_source, status)`
`user_vouchers`	`idx_user_vouchers_campaign_activated_at`	`(campaign_id, activated_at DESC)`
`voucher_logs`	`idx_voucher_logs_voucher_action_created`	`(voucher_id, action, created_at DESC)`
`voucher_logs`	`idx_voucher_logs_action_redeemed`	`(action, voucher_id) WHERE action = 'voucher_redeemed'`

C10. Observability

10.1 Logging

Event	Level	Fields	Phase	File
Override created	INFO	`{override_id, voucher_id, user_voucher_id, approved_by, branch_id, reason_code}`	P1	`approve_voucher_override.go`
Time block triggered	INFO	`{voucher_id, hours_remaining, campaign_id, min_activation_hours, order_id}`	P1	`create_order.go`
Time block — override exists, bypassed	INFO	`{voucher_id, override_id, campaign_id}`	P1	`create_order.go`
Voucher manual expired_at set	INFO	`{voucher_id, expired_at, expiry_days, activated_at}`	P1	`activate_offline_voucher.go`
Affiliate quota exceeded	WARN	`{affiliate_id, campaign_id, quota, distributed_count}`	P3	`activate_offline_voucher.go`
Affiliate quota check race condition retry	WARN	`{affiliate_id, campaign_id, attempt, distributed_count}`	P3	`activate_offline_voucher.go`
Affiliate assigned to campaign	INFO	`{campaign_id, affiliate_user_id, quota, action: "assigned"}`	P3	`create_draft_voucher_campaign.go`
Affiliate removed from campaign	INFO	`{campaign_id, affiliate_user_id, action: "removed"}`	P3	`update_voucher_campaign.go`
VoucherSetting updated	INFO	`{setting_key, old_value, new_value, updated_by}`	P1	`app_setting` handler

10.2 Metrics (Prometheus)

Metric	Type	Labels	Mô tả	Phase
`voucher_override_total`	Counter	`branch_id`, `reason_code`	Tổng số override đã tạo	P1
`voucher_time_block_total`	Counter	`campaign_id`	Tổng số lần bị chặn thời gian	P1
`voucher_time_block_bypass_total`	Counter	`campaign_id`	Tổng số lần bypass (có override)	P1
`voucher_staff_stats_query_duration`	Histogram	`campaign_id`	Thời gian query staff statistics	P2
`voucher_cycle_stats_query_duration`	Histogram	—	Thời gian query cycle statistics	P4
`affiliate_quota_usage_ratio`	Gauge	`campaign_id`, `affiliate_id`	Tỉ lệ sử dụng quota (distributed_count / quota)	P3
`affiliate_quota_exceeded_total`	Counter	`campaign_id`, `affiliate_id`	Số lần bị chặn vì hết quota	P3

10.3 Alerts

Alert	Condition	Severity	Channel	Phase
Override rate cao	`voucher_override_total` > 50/tháng	Warning	Slack `#voucher-alerts`	P1
Time block bypass rate cao	`voucher_time_block_bypass_total / voucher_time_block_total` > 30%	Warning	Slack `#voucher-alerts`	P1
Statistics query chậm	`voucher_staff_stats_query_duration` P95 > 5s	Warning	Grafana dashboard	P2
Cycle statistics query chậm	`voucher_cycle_stats_query_duration` P95 > 5s	Warning	Grafana dashboard	P4
Affiliate quota gần hết	`affiliate_quota_usage_ratio` > 0.9	Info	Slack `#voucher-alerts`	P3
Affiliate quota exceeded burst	`affiliate_quota_exceeded_total` > 10 trong 1 giờ	Warning	Slack `#voucher-alerts`	P3

10.4 Grafana Dashboard

Dashboard: Voucher Enhancement Monitoring

Panel	Type	Data source	Phase
Override count by branch (daily)	Bar chart	`voucher_override_total`	P1
Override count by reason_code	Pie chart	`voucher_override_total`	P1
Time block vs bypass rate	Time series	`voucher_time_block_total`, `voucher_time_block_bypass_total`	P1
Staff stats query latency P50/P95/P99	Heatmap	`voucher_staff_stats_query_duration`	P2
Affiliate quota utilization	Gauge per affiliate	`affiliate_quota_usage_ratio`	P3
Cycle stats query latency	Heatmap	`voucher_cycle_stats_query_duration`	P4

C11. Tasks Breakdown

Prerequisite: Perf-fix Campaign Detail (~3 ngày)

Chi tiết: perf-fix-campaign-detail.md

Task ID	Mô tả	Assignee	Est.	Dependency	Ref
PERF-01	Expression index `idx_user_vouchers_campaign_coalesce_date` (`CONCURRENTLY`)	BE	0.5d	—	perf-fix Task 1
PERF-02	Merge 4 CTEs `voucher_logs` → 1 CTE trong `get_voucher_analytics_summary`	BE	1.5d	PERF-01	perf-fix Task 2
PERF-03	Tách aggregate query ra khỏi paginated query (FE GraphQL + component)	FE	1d	PERF-02	perf-fix Task 3

Phase 1: Kiểm soát thời gian + Thời hạn voucher manual (~8 ngày)

Task ID	Mô tả	Assignee	Est.	Dependency	FR Ref
P1-01	Migration: ALTER `voucher_campaigns` + `min_activation_hours`	BE	0.5d	—	FR-P1-01
P1-02	Migration: CREATE TABLE `voucher_activation_overrides` + indexes	BE	0.5d	—	FR-P1-03, FR-P1-04
P1-03	Hasura metadata: `public_voucher_activation_overrides.yaml` + relationships + action YAML	BE	0.5d	P1-02	FR-P1-04
P1-04	Go handler: `approve_voucher_override.go` (validation, insert, logging, metrics)	BE	1d	P1-02, P1-03	FR-P1-03
P1-05	Go: Modify `create_order.go` — time validation trong `validateOrderVouchers()` + override check	BE	1d	P1-01, P1-02	FR-P1-02
P1-06	Go: Modify `activate_offline_voucher.go` — set `expired_at` cho manual voucher	BE	0.5d	P1-07	FR-P1-06
P1-07	Go: `app_setting.go` — thêm `VoucherSetting` struct + migration UPDATE app_setting	BE	0.5d	—	FR-P1-05
P1-08	FE: `VoucherConfigForm.tsx` — thêm field `min_activation_hours` (input number, hint, validation 0-720)	FE	0.5d	P1-01	FR-P1-01
P1-09	FE: `VoucherTimeBlockDialog.tsx` — dialog chặn + override flow + role-based button	FE	1d	P1-04, P1-05	FR-P1-02, FR-P1-03
P1-10	FE: `VoucherOverrideDashboard.tsx` — danh sách override, filter, pagination	FE	1.5d	P1-03	FR-P1-04
P1-11	FE: `VoucherSettingsSection.tsx` — config DefaultMinActivationHours + DefaultManualVoucherExpiryDays	FE	0.5d	P1-07	FR-P1-05, FR-P1-06
P1-12	FE: GraphQL queries + codegen cho P1 (overrides, settings)	FE	0.5d	P1-03	All P1

Phase 2: Thống kê nhân viên (~6 ngày)

Task ID	Mô tả	Assignee	Est.	Dependency	FR Ref
P2-01	Migration: CREATE FUNCTION `get_voucher_staff_statistics` + result type table	BE	2d	PERF-02	FR-P2-01
P2-02	Migration: ALTER `user_vouchers` + `distributor_type` + index	BE	0.5d	—	FR-P3-03 (prep)
P2-03	Hasura metadata: function YAML + `user_vouchers` column permission update	BE	0.5d	P2-01, P2-02	FR-P2-01
P2-04	FE: `VoucherStaffStatisticsTab.tsx` — bảng thống kê + filter bar (campaign, branch, thời gian, loại NV) + sort + pagination	FE	2d	P2-03, PERF-03	FR-P2-01, FR-P2-03
P2-05	FE: `StaffDrillDownDialog.tsx` — click row → chi tiết voucher per NV	FE	1d	P2-04	FR-P2-02
P2-06	FE: Export integration — "Xuất Excel" button, call export-api, async nếu > 5000 rows	FE	0.5d	P2-04	FR-P2-05
P2-07	BE: Partner app API — row-level security `staff_id = X-Hasura-User-Id` trên function result	BE	0.5d	P2-01	FR-P2-04

Phase 3: Đối tác Affiliate — Hybrid Distribution (~7 ngày)

Task ID	Mô tả	Assignee	Est.	Dependency	FR Ref
P3-01	Migration: CREATE TABLE `voucher_campaign_affiliates` (với `distribution_method`) + indexes + trigger	BE	0.5d	—	FR-P3-01
P3-01a	Migration: ALTER `user_vouchers` ADD `assigned_affiliate_id` + index (batch pre-assign)	BE	0.5d	—	FR-P3-01a
P3-02	Hasura metadata: `public_voucher_campaign_affiliates.yaml` + relationships (bao gồm `batch_vouchers` composite relationship)	BE	0.5d	P3-01, P3-01a	FR-P3-01
P3-03	Go: Modify `create_draft_voucher_campaign.go` — INSERT affiliates (với `distribution_method`) + action log	BE	0.5d	P3-01	FR-P3-01
P3-04	Go: Modify `update_voucher_campaign.go` — UPSERT affiliates (DEC-T04 pattern)	BE	0.5d	P3-01	FR-P3-01
P3-05	Go: Modify `activate_offline_voucher.go` — affiliate quota check (atomic increment, FORMULA-006) + validate `distribution_method` IN ('app', 'both')	BE	1d	P3-01, P2-02	FR-P3-02
P3-05a	Go: MỚI `generate_affiliate_voucher_batch.go` — bulk generate + pre-assign + atomic quota (error codes: `AFFILIATE_BATCH_NOT_ALLOWED`, `AFFILIATE_QUOTA_EXCEEDED`, `AFFILIATE_BATCH_INVALID_QUANTITY`, `AFFILIATE_CAMPAIGN_NOT_FOUND`, `AFFILIATE_DEACTIVATED`)	BE	1.5d	P3-01, P3-01a	FR-P3-01a
P3-06	FE: `AffiliateAssignStep.tsx` — wizard step: autocomplete affiliate, set quota, chọn kênh phát (app/batch/both), list assigned	FE	1.5d	P3-02	FR-P3-01, FR-P3-02
P3-06a	FE: MỚI `AffiliateBatchPanel.tsx` + `GenerateBatchDialog.tsx` — panel generate batch + download PDF/Excel + thống kê (batch_count, activated_count, remaining) trên campaign detail	FE	1.5d	P3-02, P3-05a	FR-P3-01a
P3-07	FE: `AffiliateSourceBadge.tsx` — badge trong order creation (lookup `assigned_affiliate_id`)	FE	0.5d	P3-02	FR-P3-04
P3-08	Go: Affiliate action logging — INSERT `affiliate_action_log` khi gán/gỡ + `batch_voucher_generated`	BE	0.5d	P3-03	FR-P3-05

Phase 4: Chu kỳ sử dụng (~5 ngày)

Task ID	Mô tả	Assignee	Est.	Dependency	FR Ref
P4-01	Migration: CREATE FUNCTION `get_voucher_usage_cycle_statistics` + result type	BE	1.5d	PERF-02	FR-P4-01
P4-02	Migration: CREATE FUNCTION `get_voucher_usage_cycle_distribution` + result type	BE	1d	PERF-02	FR-P4-02
P4-03	Hasura metadata: function YAML × 2	BE	0.5d	P4-01, P4-02	FR-P4-01, FR-P4-02
P4-04	FE: `VoucherUsageCycleTab.tsx` — KPI cards + filter (campaigns, branches, staff, thời gian, group by) + bảng	FE	1.5d	P4-03, PERF-03	FR-P4-01, FR-P4-03, FR-P4-04
P4-05	FE: `CycleBucketChart.tsx` — horizontal bar chart (Chart.js), hỗ trợ so sánh multi-campaign	FE	1d	P4-03	FR-P4-02, FR-P4-03
P4-06	FE: Export integration — "Xuất Excel" button cho cycle data	FE	0.5d	P4-04	FR-P4-05

Summary

Phase	BE Tasks	FE Tasks	Total Est.
PERF-FIX	2	1	~3d
P1	7	5	~8d
P2	4	3	~6d (after PERF)
P3	7	3	~7d (after P2)
P4	3	3	~5d (after PERF)
Total	21	14	~27d

Lưu ý: P1 và PERF-FIX có thể chạy song song. P2 và P4 có thể chạy song song sau PERF-FIX. P3 phải chờ P2.

C12. Traceability Matrix

FR → FE Component → BE Artifact → Test Case

FR	FE Component	BE Artifact	DB Artifact	API/Query	Test Case
FR-P1-01	`VoucherConfigForm.tsx` (field `min_activation_hours`)	`voucher_campaigns.go` — `MinActivationHours` field	ALTER `voucher_campaigns` + `min_activation_hours`	Hasura mutation `update_voucher_campaigns`	TC-P1-01: Tạo campaign với min_activation_hours = 48
FR-P1-02	`VoucherTimeBlockDialog.tsx` (hiển thị thông tin chặn)	`create_order.go` — `validateOrderVouchers()` time check	—	Error `VOUCHER_TIME_BLOCKED`	TC-P1-02: Tạo ĐH với voucher kích hoạt < 24h → chặn
FR-P1-03	`VoucherTimeBlockDialog.tsx` (override flow)	`approve_voucher_override.go` — action handler	CREATE `voucher_activation_overrides`	`approve_voucher_override` action	TC-P1-03: Manager override → ĐH tạo thành công
FR-P1-04	`VoucherOverrideDashboard.tsx`	—	SELECT `voucher_activation_overrides`	`GetVoucherOverrides` query	TC-P1-04: Dashboard hiển thị danh sách override + filter
FR-P1-05	`VoucherSettingsSection.tsx`	`app_setting.go` — `VoucherSetting` struct	UPDATE `app_setting` JSONB	`GetVoucherSettings`, `UpdateAppSetting`	TC-P1-05: Thay đổi DefaultMinActivationHours → apply cho voucher manual
FR-P1-06	`VoucherConfigForm.tsx` (hiển thị expired_at)	`activate_offline_voucher.go` — set `expired_at`	`user_vouchers.expired_at` (existing column)	—	TC-P1-06: Kích hoạt voucher manual → expired_at = activated_at + 30d
FR-P1-07	Partner app (external)	—	SELECT `user_vouchers.expired_at`	Hasura select	TC-P1-07: Partner app hiển thị hạn sử dụng voucher
FR-P2-01	`VoucherStaffStatisticsTab.tsx`	—	`get_voucher_staff_statistics` function	`GetVoucherStaffStatistics` query	TC-P2-01: Bảng thống kê hiển thị đúng per NV
FR-P2-02	`StaffDrillDownDialog.tsx`	—	SELECT `user_vouchers WHERE staff_id`	`GetStaffVoucherDetail` query	TC-P2-02: Click NV → danh sách voucher đúng
FR-P2-03	`VoucherStaffStatisticsTab.tsx` (sort headers)	—	Function `ORDER BY` parameter	`GetVoucherStaffStatistics` + `order_by`	TC-P2-03: Sort theo conversion_rate DESC
FR-P2-04	Partner app (external)	—	Function + row-level security	`GetVoucherStaffStatistics` + `X-Hasura-User-Id`	TC-P2-04: NV chỉ thấy data bản thân
FR-P2-05	`VoucherStaffStatisticsTab.tsx` (export button)	`export-api` template	—	export-api endpoint	TC-P2-05: Export > 5000 rows → async + download link
FR-P3-01	`AffiliateAssignStep.tsx`	`create_draft_voucher_campaign.go` — INSERT affiliates	CREATE `voucher_campaign_affiliates`	`GetCampaignAffiliates`, `SearchAffiliateUsers`	TC-P3-01: Gán 2 đối tác với quota → record tạo đúng
FR-P3-02	`AffiliateAssignStep.tsx` (quota display)	`activate_offline_voucher.go` — quota check	Atomic UPDATE `distributed_count`	—	TC-P3-02: Đối tác phát đúng quota → chặn
FR-P3-03	`VoucherStaffStatisticsTab.tsx` (column `distributor_type`)	—	`user_vouchers.distributor_type`	`GetVoucherStaffStatistics`	TC-P3-03: Filter "Chỉ xem đối tác" → đúng
FR-P3-04	`AffiliateSourceBadge.tsx`	—	JOIN `user_vouchers` → `voucher_campaign_affiliates`	Hasura relationship	TC-P3-04: Badge hiển thị "Voucher do KOL X phát"
FR-P3-05	—	`affiliate_action_log` INSERT in Go handler	INSERT `affiliate_action_log`	—	TC-P3-05: Gán đối tác → log action `campaign_affiliate_assigned`
FR-P4-01	`VoucherUsageCycleTab.tsx` (KPI cards)	—	`get_voucher_usage_cycle_statistics` function	`GetVoucherUsageCycleStatistics`	TC-P4-01: KPI hiển thị avg, min, median, usage_rate
FR-P4-02	`CycleBucketChart.tsx`	—	`get_voucher_usage_cycle_distribution` function	`GetVoucherUsageCycleDistribution`	TC-P4-02: Chart hiển thị 7 buckets đúng %
FR-P4-03	`VoucherUsageCycleTab.tsx` (compare mode)	—	Function với `filter_campaign_ids` array	`GetVoucherUsageCycleDistribution` (multi)	TC-P4-03: So sánh 2 campaigns → chart chồng
FR-P4-04	`VoucherUsageCycleTab.tsx` (filter bar)	—	Function params	All P4 queries	TC-P4-04: Filter branch + staff → data đúng scope
FR-P4-05	`VoucherUsageCycleTab.tsx` (export button)	`export-api` template	—	export-api endpoint	TC-P4-05: Export cycle data → file đúng format

Decision → Artifact Traceability

Decision	Artifact(s) ảnh hưởng
DEC-T01	`create_order.go` — `validateOrderVouchers()`, FORMULA-001
DEC-T02	CREATE TABLE `voucher_activation_overrides`, `approve_voucher_override.go`
DEC-T03	ALTER `user_vouchers.distributor_type`, `activate_voucher.go`
DEC-T04	`update_voucher_campaign.go` — UPSERT pattern, CREATE TABLE `voucher_campaign_affiliates`
DEC-T05	`get_voucher_usage_cycle_statistics` — `voucher_logs` DISTINCT ON, FORMULA-003
DEC-T06	`get_voucher_usage_cycle_statistics` — `PERCENTILE_CONT(0.5)`, FORMULA-004
DEC-T07	`get_voucher_usage_cycle_distribution` — CASE bucket aggregation, FORMULA-005
DEC-T08	`app_setting.go` — `VoucherSetting` struct, UPDATE `app_setting` JSONB
DEC-B01	`create_order.go` — time check + override flow
DEC-B02	`voucher_campaigns.min_activation_hours` DEFAULT 24
DEC-B03	Không tạo commission system mới — chỉ `total_revenue` trong statistics
DEC-B04	Junction table `voucher_campaign_affiliates` → `affiliate_user` (existing)
DEC-B05	`activate_offline_voucher.go` — set `expired_at`, leverage cron `expired_vouchers`
DEC-B06	4 phases: P1 (P0), P2 (P1), P3 (P1), P4 (P2)
DEC-Q01	Tất cả time comparison dùng `Asia/Ho_Chi_Minh` — Go + SQL
DEC-Q02	FORMULA-006 — atomic increment, `idx_vca_quota_check` index

Hướng dẫn đọc

Audience	Sections ưu tiên	Mục đích
Backend Dev	C3 (Formulas), C4 (Data Model), C5 (API Contracts), C7 (Migration), C11 (Tasks)	Implement Go handlers, migrations, SQL functions, Hasura metadata
Frontend Dev	C2.4 (FE Files), C5.3 (GraphQL Queries), C5.5 (Error Contract), C11 (Tasks)	Implement components, queries, error handling
Tech Lead	C1 (Scope), C2 (Impact), C9 (NFR), C10 (Observability), C12 (Traceability)	Review architecture, performance targets, monitoring
QA	C3 (Formulas — edge cases), C5.5 (Error Contract), C8 (Security), C12 (Traceability → Test Cases)	Viết test cases, verify edge cases, security testing
DevOps	C6 (Scheduler), C7 (Migration), C9 (NFR), C10 (Observability — Grafana, Alerts)	Deploy plan, monitoring setup, alert configuration
PO/BA	C1 (Scope), C11 (Tasks — estimation), C12 (FR → Artifact mapping)	Verify coverage, track progress

Changelog

Version	Ngày	Thay đổi
1.0	2026-03-19	Initial dev spec — 4 phases + perf-fix prerequisite, full C1-C12

Dev Spec — Nâng cấp Voucher Management: Kiểm soát, Thống kê & Đối tác ​

C1. Scope & Approach ​

1.1 Phạm vi ​

1.2 Phân phase ​

1.3 Approach tổng quan ​

1.4 Dependency Graph ​

C2. Impact Analysis ​

2.1 Backend — Go files ​

2.2 Backend — Hasura Metadata ​

2.3 Backend — Migrations ​

2.4 Frontend — Files ​

2.5 Frontend — File Structure ​

C3. Rules & Formulas ​

FORMULA-001: Time Validation (Kiểm tra thời gian kích hoạt) ​

FORMULA-002: Conversion Rate per Staff (Tỉ lệ chuyển đổi per nhân viên) ​

FORMULA-003: Cycle Days (Số ngày từ kích hoạt → sử dụng) ​

FORMULA-004: Median Cycle (Trung vị chu kỳ sử dụng) ​

FORMULA-005: Bucket Distribution (Phân bổ theo nhóm chu kỳ) ​

FORMULA-006: Affiliate Quota Check (Kiểm tra quota đối tác) ​

C4. Data Model ​

4.1 NEW TABLE: voucher_activation_overrides (Phase 1) ​

4.2 ALTER TABLE: voucher_campaigns (Phase 1) ​

4.3 ALTER TABLE: user_vouchers (Phase 2) ​

4.4 NEW TABLE: voucher_campaign_affiliates (Phase 3) ​

4.5 ALTER app_setting: VoucherSetting (Phase 1) ​

4.6 SQL Functions ​

4.6.1 get_voucher_staff_statistics (Phase 2) ​

4.6.2 get_voucher_usage_cycle_statistics (Phase 4) ​

4.6.3 get_voucher_usage_cycle_distribution (Phase 4) ​

4.7 ER Diagram (tables mới + quan hệ) ​

C5. API Contracts & Hasura Metadata ​

5.1 Hasura Action: approve_voucher_override (Phase 1) ​

5.2 Hasura Action Definition YAML ​

5.3 GraphQL Queries mới ​

Phase 1 — Override Dashboard ​

Phase 1 — Settings ​

Phase 2 — Staff Statistics ​

Phase 3 — Affiliates ​

Phase 4 — Usage Cycle ​

5.4 Hasura Table Metadata ​

public_voucher_activation_overrides.yaml (P1) ​

public_voucher_campaign_affiliates.yaml (P3) ​

Function Metadata (P2, P4) ​

5.5 Error Contract ​

C6. Scheduler / Cron Jobs ​

6.1 Cron hiện có (không đổi, chỉ leverage) ​

6.2 Cron mới: Không cần ​

6.3 Notification triggers (event-based, không phải cron) ​

C7. Migration Plan ​

7.1 Migration Order ​

7.2 Migration File Naming Convention ​

7.3 Rollback Strategy ​

7.4 Data Backfill ​

C8. Security ​

8.1 Role-Based Access Control ​

Override Action (P1) ​

Override Dashboard (P1) ​

Staff Statistics (P2) ​

Affiliate Management (P3) ​

8.2 Data Security ​

8.3 Timezone Security ​

C9. Non-Functional Requirements (NFR) ​

9.1 Performance Targets ​

9.2 Scalability ​

9.3 Capacity Model ​

9.4 Indexes Summary (tất cả phases) ​

9.5 Existing Indexes (reference — đã có, không đổi) ​

C10. Observability ​

10.1 Logging ​

10.2 Metrics (Prometheus) ​

10.3 Alerts ​

10.4 Grafana Dashboard ​

C11. Tasks Breakdown ​

Prerequisite: Perf-fix Campaign Detail (~3 ngày) ​

Phase 1: Kiểm soát thời gian + Thời hạn voucher manual (~8 ngày) ​

Phase 2: Thống kê nhân viên (~6 ngày) ​

Phase 3: Đối tác Affiliate — Hybrid Distribution (~7 ngày) ​

Phase 4: Chu kỳ sử dụng (~5 ngày) ​

Summary ​

C12. Traceability Matrix ​

Dev Spec — Nâng cấp Voucher Management: Kiểm soát, Thống kê & Đối tác

C1. Scope & Approach

1.1 Phạm vi

1.2 Phân phase

1.3 Approach tổng quan

1.4 Dependency Graph

C2. Impact Analysis

2.1 Backend — Go files

2.2 Backend — Hasura Metadata

2.3 Backend — Migrations

2.4 Frontend — Files

2.5 Frontend — File Structure

C3. Rules & Formulas

FORMULA-001: Time Validation (Kiểm tra thời gian kích hoạt)

FORMULA-002: Conversion Rate per Staff (Tỉ lệ chuyển đổi per nhân viên)

FORMULA-003: Cycle Days (Số ngày từ kích hoạt → sử dụng)

FORMULA-004: Median Cycle (Trung vị chu kỳ sử dụng)

FORMULA-005: Bucket Distribution (Phân bổ theo nhóm chu kỳ)

FORMULA-006: Affiliate Quota Check (Kiểm tra quota đối tác)

C4. Data Model

4.1 NEW TABLE: `voucher_activation_overrides` (Phase 1)

4.2 ALTER TABLE: `voucher_campaigns` (Phase 1)

4.3 ALTER TABLE: `user_vouchers` (Phase 2)

4.4 NEW TABLE: `voucher_campaign_affiliates` (Phase 3)

4.5 ALTER `app_setting`: VoucherSetting (Phase 1)

4.6 SQL Functions

4.6.1 `get_voucher_staff_statistics` (Phase 2)

4.6.2 `get_voucher_usage_cycle_statistics` (Phase 4)

4.6.3 `get_voucher_usage_cycle_distribution` (Phase 4)

4.7 ER Diagram (tables mới + quan hệ)

C5. API Contracts & Hasura Metadata

5.1 Hasura Action: `approve_voucher_override` (Phase 1)

5.2 Hasura Action Definition YAML

5.3 GraphQL Queries mới

Phase 1 — Override Dashboard

Phase 1 — Settings

Phase 2 — Staff Statistics

Phase 3 — Affiliates

Phase 4 — Usage Cycle

5.4 Hasura Table Metadata

`public_voucher_activation_overrides.yaml` (P1)

`public_voucher_campaign_affiliates.yaml` (P3)

Function Metadata (P2, P4)

5.5 Error Contract

C6. Scheduler / Cron Jobs

6.1 Cron hiện có (không đổi, chỉ leverage)

6.2 Cron mới: Không cần

6.3 Notification triggers (event-based, không phải cron)

C7. Migration Plan

7.1 Migration Order

7.2 Migration File Naming Convention

7.3 Rollback Strategy

7.4 Data Backfill

C8. Security

8.1 Role-Based Access Control

Override Action (P1)

Override Dashboard (P1)

Staff Statistics (P2)

Affiliate Management (P3)

8.2 Data Security

8.3 Timezone Security

C9. Non-Functional Requirements (NFR)

9.1 Performance Targets

9.2 Scalability

9.3 Capacity Model

9.4 Indexes Summary (tất cả phases)

9.5 Existing Indexes (reference — đã có, không đổi)

C10. Observability

10.1 Logging

10.2 Metrics (Prometheus)

10.3 Alerts

10.4 Grafana Dashboard

C11. Tasks Breakdown

Prerequisite: Perf-fix Campaign Detail (~3 ngày)

Phase 1: Kiểm soát thời gian + Thời hạn voucher manual (~8 ngày)

Phase 2: Thống kê nhân viên (~6 ngày)

Phase 3: Đối tác Affiliate — Hybrid Distribution (~7 ngày)

Phase 4: Chu kỳ sử dụng (~5 ngày)

Summary

C12. Traceability Matrix