Manual CSV / Excel Upload Runbook

TL;DR#

1. cd apps/web → open /connectors/uploads while signed into the target org.
2. Drop in a .csv, .xlsx, or .xls file under 8 MiB.
3. Pick the upload type (orders / creators / inventory / creator_costs / targets / products) for Wave 1‑2 streams, or a mapping template for Wave 3 streams (ad spend, payouts, returns, warehouse moves, etc.).
4. Submit. The worker parses, normalises, and writes to staging (import_staging_rows) or bronze (raw_events), then promotes to silver.
5. Watch the job row in manual_upload_jobs for status and rejected_reasons.

Supported data types#

For Wave 1‑2 data types the column aliases live in packages/connectors/core/src/manual-upload.ts (Vietnamese + English headers). For Wave 3, create a mapping template under /connectors/uploads/templates describing the column transforms.

Column mapping template anatomy#

A template is a Zod-validated JSON document with three required sections:

{
  "formatVersion": 1,
  "targetStream": "ad_spend", // which silver table this lands in
  "parser": {
    "delimiter": ",", // , ; | \t
    "quote": "\"",
    "skipLines": 0, // skip title rows / metadata
    "encoding": "utf8", // utf8 | utf16le | latin1 | win1258
    "trimHeaders": true,
    "headerCaseInsensitive": true,
  },
  "columns": [
    {
      "source": "Date", // CSV header
      "target": "day", // canonical field
      "required": true,
      "transform": {
        "kind": "date",
        "format": "DD/MM/YYYY", // Vietnamese day-first dates
        "output": "date", // 'iso' for full ISO-8601 timestamps
      },
    },
    {
      "source": "Spend",
      "target": "spend_vnd",
      "required": true,
      "transform": {
        "kind": "int",
        "thousandsSep": ",",
        "decimalSep": ".",
        "stripCurrency": true, // strips đ, VND, $, %, whitespace
        "parenthesesAsNegative": false, // accounting exports
      },
    },
  ],
  "required": ["platform", "day", "spend_vnd"],
  "dedupeKey": ["platform", "campaign_external_id", "day"],
}

Transform kinds (closed set)#

Adding a new kind is a code change in packages/connectors/core/src/mapping/transforms.ts. The set today:

• pass — keep the raw string.
• string — trim, case fold, collapse whitespace, default fallback.
• int — strip thousands separator, strip currency symbols (stripCurrency: true by default), drop decimal portion, optional multiplyBy, optional parenthesesAsNegative for accounting exports.
• decimal — same as int but emits a float; never use for VND (CLAUDE.md §3 — VND is integer).
• date — format-driven parser (DD/MM/YYYY, YYYY-MM-DD, M/D/YYYY, plus time variants). Output date → YYYY-MM-DD, output iso → full ISO-8601 in UTC.
• enum — explicit source-value → canonical-value map. Unknown values default, reject, or pass.
• concat — join multiple source columns with a separator. Used for synthetic dedupe keys.
• constant — emit a fixed value regardless of source.

Things that bite (and the fix)#

Tomorrow's customer file — recommended pre-flight#

1. Open the file in a text editor (not Excel — Excel silently re-encodes). Confirm encoding via file <path>. Vietnamese exports from older POS systems can be Windows-1258.
2. Inspect the header row. If it's Vietnamese (Mã đơn, Ngày tạo) AND the customer uses the Wave 1-2 orders stream, the hardcoded aliases handle it — no template needed.
3. Spot-check three rows for: currency symbols, date format, and any non-printable characters.
4. Upload the first 100 rows as a test. Validate manual_upload_jobs.rejected_reasons is empty.
5. Upload the full file only after the test passes.

Verifying a successful upload#

-- Recent jobs and their counts
SELECT id, upload_type, status, rows_processed, rows_upserted, rows_rejected,
       LEFT(COALESCE(rejected_reasons::text, ''), 200) AS sample_errors
FROM manual_upload_jobs
WHERE org_id = '<org-uuid>'
ORDER BY created_at DESC
LIMIT 5;

For Wave 3 templated imports:

SELECT status, COUNT(*) FROM import_staging_rows
WHERE template_id = '<template-uuid>'
GROUP BY status;

queued → still being promoted. promoted → made it to the silver table. rejected → check error column on the row.

When to escalate#

• The same row hash keeps producing different dedupe keys → file an issue against @yng/connectors-core/mapping/apply with the row contents.
• A non-trivial fraction of rows are rejected and the error mentions Zod issue paths → the canonical schema in packages/connectors/core/src/mapping/streams.ts rejected the post-transform shape. Surface to engineering.
• A .xlsx file fails with unsupported_extension after the worker has been redeployed → confirm xlsx is in apps/worker/package.json deps (not peer).

Test scripts#

End-to-end smoke tests for the upload pipeline live in:

• apps/web/src/__tests__/import-mapping.test.ts — pure transform unit tests (Vietnamese dates, currency stripping, parens-as-negative, dedupe-key stability, CSV → accept/reject decisions, idempotent replay).
• apps/web/e2e/manual-upload.spec.ts — Playwright smoke ensuring /connectors/uploads and the templates editor are reachable while authenticated.

Run them with:

pnpm --filter @yng/web test            # vitest unit suite
pnpm --filter @yng/web test:e2e        # playwright (workers=1)