Data Validation (Đối soát dữ liệu)

Why this exists#

Customers send raw export files snapshotted at different times, and the same window can show different numbers in each snapshot. The recurring incident: NMV 6–12/4 diverges, and the root cause turns out to be ONE order (2604075JXB1Q54) whose status is Đã hủy in the system's snapshot but Đang giao in the customer's file — cancelled orders zero NMV, so the whole range drifts. Finding that by hand takes hours.

Data Validation turns that hunt into one upload: pick a date range + data type, upload the customer's raw file, and the system reports exactly which order numbers (and which fields) diverge.

What it does#

1. Upload — operator picks a dataset (Shopee/Lazada/TTS Sales Orders, Shopee/TTS Creator-Affiliate Orders), an ICT day range, and drops the raw .csv/.xlsx/.xls (8 MiB cap, same as manual uploads).
2. Parse (file side) — the worker parses the file with the org's import mapping template for that format (import_mapping_templates.source_kind) — the same template real ingests use — then recomputes order financials with the same arithmetic the silver promoter writes with: computeOrderFinancials() + canonicalOrderStatus() from @yng/connectors-core/order-financials. Any diff the report shows is real data divergence, never formula drift.
3. Read (system side) — silver verbatim: orders.gmv_vnd, orders.nmv_blo_vnd (NMV is never recomputed from line items), orders.status; creator datasets read creator_performance_daily.
4. Diff — diffValidationFacts() (pure, unit-tested) full-outer-joins on order number, classifies every divergence (missing_in_db / missing_in_file / status_mismatch / metric_mismatch with the exact differing fields), and rolls both sides up per ICT day. Status comparison uses the cancelled/active CLASS only (statusEffectClass) — the two sides snapshot the order lifecycle at different times and each platform speaks its own dialect, so "completed vs delivered/Đã giao" is timing noise, never a mismatch. The only status divergence that moves money is cancellation (the NMV filter is status ≠ Đã hủy, nothing else). The report still DISPLAYS the customer's verbatim file status next to the system's canonical one.
5. Report — run detail page shows:
   • KPI verdicts: #orders, GMV, NMV — system vs upload, ✓/✗ per metric
   • per-day table (orders / GMV / NMV, mismatching pairs highlighted)
   • per-order drilldown filterable by diff type AND by metric (Lệch GMV / Lệch NMV chips, backed by mismatched_fields @> + per-field counts in summary.mismatchedFieldCounts), mismatched fields tinted
6. History + retention — every run is kept; a nightly purge (04:15 VN) clears the uploaded file bytes + diff details of runs older than organizations.validation_retention_days (default 30, super-admin editable in Admin → Organizations → drawer → Tenancy). Run summaries survive as light history (details_purged = true).

Semantics worth knowing#

• ICT everywhere. File-side day labels come from ictDateLabel(placed_at); the comparison window is inclusive ICT days. File orders outside the picked window are skipped and counted in the run summary (outOfRangeFileCount).
• Cancelled orders keep their gross GMV but contribute 0 NMV on BOTH sides (computeOrderFinancials zeroes the file side; nmv_blo_vnd is already 0 in silver). A cancelled-in-DB / active-in-file order therefore surfaces as status_mismatch with nmvVnd in mismatchedFields. Any other lifecycle difference (pending/confirmed/shipped/delivered/completed, per-platform dialects) is deliberately NOT a mismatch.
• Shopee capitalisation trap is honoured: the file side reads sourcePayload verbatim, so col 29 (capital "Người") SUM-per-line vs the older lowercase col 46 MAX-per-order resolution matches the promoter exactly (see packages/connectors/core/src/order-financials.ts).
• Creator/affiliate datasets compare at the day grain — the system stores that stream as creator_performance_daily rollups, so there is no per-order drilldown for them (the UI says so). Metrics: ordersCount, gmvVnd (payment amount, non-cancelled).
• Order-diff cap: at most 5 000 per-order diff rows are persisted per run (summary.orderDiffsTruncatedAt records the truncation; the UI shows "first N of M").
• Brand scope: a run created with a brandId compares against rows with brand_id = X OR brand_id IS NULL (org-wide rows stay visible), matching dashboard semantics.

Architecture / extension seam (Meta Ads, Google Ads, …)#

The pipeline is metric-agnostic end to end; only the fact builders know a dataset's shape.

┌─ packages/connectors/core/src/validation/
│   datasets.ts   VALIDATION_DATASETS — the registry (key, platform,
│                 sourceKind, targetStream, grain, metricKeys, labelKey)
│   diff.ts       diffValidationFacts() — pure engine over ValidationFact
│                 { key, day, status?, metrics: Record<string, number> }
├─ packages/connectors/core/src/order-financials.ts
│                 computeOrderFinancials / canonicalOrderStatus — the SINGLE
│                 money implementation shared with the silver promoter
├─ apps/worker/src/processors/validation-run.ts
│                 buildOrderFacts / buildCreatorDayFacts — per-targetStream
│                 fact builders (file side + DB side)
│   validation-purge.ts   nightly retention sweep (04:15 VN)
├─ apps/web/src/server/routers/validation.ts   tRPC (createRun, listRuns,
│                 getRun, dayDiffs, orderDiffs, datasets)
└─ apps/web/src/app/(dashboard)/validation/    UI (list + run detail)

To add a new dataset when an ads connector lands (e.g. Meta Ads spend):

1. Template — seed an import_mapping_templates row for the export format (source_kind: 'meta-ads-spend-csv', spec mapping its columns to canonical fields).
2. Registry — add an entry to VALIDATION_DATASETS with a new targetStream (e.g. 'ad_spend'), grain: 'day' (ads exports are daily), and its metricKeys (e.g. ['spendVnd', 'impressions', 'clicks']).
3. Fact builder — add a buildAdSpendFacts() branch in validation-run.ts keyed on the new targetStream: file side from the parsed template rows, DB side from the ads silver table.
4. i18n — add validation.datasets.<key> labels (vi + en).

No changes needed in: the diff engine, the three tables (extra metrics ride in the file_metrics/db_metrics jsonb maps), the tRPC router, the run list/detail UI (it renders from summary.metricKeys), the retention purge.

Failure modes#

Deterministic failures mark the run failed without BullMQ retries; only transient DB errors retry (2 attempts).

Tests#

• apps/worker/src/processors/__tests__/order-financials.test.ts — pins the per-platform money block (TTS NMV column + fallback, Shopee col-29 trap + seller-vs-platform voucher, Lazada per-line, cancelled-zeroing, status ranking, allocation exactness).
• apps/worker/src/processors/__tests__/validation-diff.test.ts — pins the diff engine, including the canonical Đang giao vs Đã hủy snapshot-drift case down to the order number.