Kolable order payment reconciliation production pack - job 28b0cf53

acceptance-tests.md

E2E Acceptance Tests

A. Import Schema

1. Given Wade uploads CSV/XLSX with the required 10 fields,
2. When PLS runs reconciliation import,
3. Then every row is accepted or rejected with row-level reason.

Expected evidence: import result includes row_count, accepted rows, rejected rows, and schema error list.

B. Payment Classification

1. Given orders have payment_status values paid, pending, refunded, partial_refund, failed,
2. When scorecard is generated,
3. Then revenue is counted only for paid non-refunded rows.

Expected evidence: paid revenue total excludes refunded/pending/duplicate rows.

C. 28,000 Training Order

1. Given a 28,000 TWD training order appears in the export,
2. And it has refund/rebuy evidence,
3. Then it must be classified into paid, refunded, rebought, or unresolved.

Expected evidence: kolable_reconciliation_exceptions includes unresolved rows with Huber/Louis owner.

D. Duplicate Detection

1. Given same customer_key + program_id + amount appears more than once within 5/12-5/21,
2. When import runs,
3. Then duplicate candidates are flagged and excluded from recognized revenue until resolved.

Expected evidence: duplicate exception count and owner action are present.

E. People Sync

1. Given scorecard is generated,
2. Then Louis receives revenue decision summary, Wade receives missing data list, Huber receives 28,000 verification list, zihrou receives pricing/product implications.

Expected evidence: PLS artifacts include people_sync owner/due/expected signal.

F. Anti-Fake Completion

No github_pr or deployment artifact is valid unless a verified repo/deployment URL exists. This job only claims a production pack and data/import spec.

Current Verification

• Primary Gist returns HTTP 200.
• Gist contains HTML console, CSV template, production brief, data model, acceptance tests, decision record, learning memory, sources, and artifact URL/PR note.
• PLS upload-files reports all files uploaded.

artifact-url-or-pr.md

Artifact URL / PR Status

Primary artifact URL: https://gist.github.com/esz135888/f780d15b8d22a76cb950e4da189075ee

No GitHub PR or deployment was created in this job.

Reason: PLS context does not provide Kolable repo URL, local repo path, branch, deployment target, or API credentials. Claiming github_pr or deployment would be fake success.

Next valid step: Wade uploads the real 2026-05-12 to 2026-05-21 order/payment export, or Louis provides Kolable backend repo/API target for a repo_change worker.

data-model.md

Data Model / API / Sync / Permission Spec

Tables

kolable_orders

column	type	note
id	uuid	system id
order_id	text	source order id, unique
created_at	timestamptz	order time
customer_key	text	hashed email/phone; no raw PII in shared artifact
program_id	text	Kolable program/course id
product_name	text	product/course name
amount	numeric	order amount
currency	text	TWD default
discount_code	text	source attribution
source_event	text	live/event/source

kolable_payments

column	type	note
id	uuid	payment row id
order_id	text	FK to kolable_orders.order_id
payment_status	text	paid/pending/failed/refunded/partial_refund
payment_ref	text	transaction id
paid_at	timestamptz	payment time
refund_ref	text	refund id
refunded_at	timestamptz	refund time
settlement_status	text	settled/unsettled/disputed

kolable_reconciliation_exceptions

column	type	note
id	uuid	exception id
order_id	text	source order
exception_type	text	missing_payment_ref, pending, refunded, duplicate, amount_mismatch
severity	text	P0/P1/P2
owner_profile_id	uuid	Wade/Huber/Louis
due_at	timestamptz	action due
resolution_status	text	open/resolved/rejected
audit_note	text	decision trail

API / Sync

• POST /api/kolable/reconciliation/import
  • input: CSV/XLSX file or parsed rows following order-payment-template.csv.
  • output: { import_id, row_count, paid_count, pending_count, refund_count, duplicate_count, exception_count }.
• GET /api/kolable/reconciliation/:import_id/scorecard
  • output: revenue scorecard and exception list.
• POST /api/kolable/reconciliation/:import_id/resolve
  • input: { order_id, resolution_status, owner_note, evidence_ref }.

Permissions

• Louis: approve revenue recognition, refund/rebuy decision, customer-facing policy.
• Wade: upload/export orders and payment refs; resolve missing payment refs.
• Huber: verify 28,000 training refund/rebuy rows.
• zihrou: review pricing/product implications.
• PLS worker: import, classify, create action items, write audit logs.

Audit Boundaries

• Shared artifacts must not expose raw email, phone, card number, or payment token.
• Use customer_key hash for duplicate detection.
• Every manual override must store owner, timestamp, evidence_ref, and reason.
• No direct production DB write for reconciliation; import is append-only, then resolved through API/admin action.

Failure / Rollback

• If import schema fails, reject rows and return row-level errors; do not partially overwrite prior scorecards.
• If payment provider data conflicts with Kolable order status, mark amount_mismatch or status_mismatch and route to Louis/Wade.
• If no file arrives by 2026-05-24, PLS should escalate to Louis with missing data owner signal.

decision-record.md

Decision Record

Decision

Adopt a Kolable Order Payment Reconciliation Pack as the primary artifact for this round.

Problem

The current P0 blocker is not a generic Kolable strategy question. It is a concrete missing operating artifact: Wade must export 2026-05-12 to 2026-05-21 orders and payment status, and Louis/Huber must verify the 28,000 training order refund/rebuy state.

Options Considered

1. Build a broad product strategy deck.
   • Rejected: does not unblock overdue payment/order evidence.
2. Claim a repo change or deployment.
   • Rejected: context does not include repo URL, branch, deployment target, or credentials.
3. Produce an import-ready reconciliation pack.
   • Recommended: gives Wade exact export schema, gives Louis revenue decision rules, and gives PLS a path into daily import/API.

Recommendation

Use this pack immediately. The next valid input is Wade's real CSV/XLSX export. If a repo/API target becomes available, dispatch a repo_change worker to implement POST /api/kolable/reconciliation/import.

Adoption Status

Proposed. Acceptance requires one of:

• Wade uploads the export and PLS generates scorecard.
• Louis explicitly says Kolable order data lives in a specific repo/API and asks for repo implementation.

Landing Path

Owner: Louis. Data owner: Wade. Verification owner: Huber for 28,000 training order. Due: 2026-05-24. Acceptance: paid/pending/refunded/duplicate split exists for 2026-05-12 to 2026-05-21.

Feedback If Not Adopted

If this is not the right path, return the actual system of record for orders/payments and who can export it. Without that, PLS should keep the action open and not mark Kolable revenue as verified.

kolable-order-payment-control.html

<!doctype html>
<html lang="zh-Hant">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Kolable 訂單付款對帳控制台</title>
  <style>
    :root { --ink:#1d2433; --muted:#667085; --line:#d6dde8; --bg:#f6f8fb; --panel:#fff; --ok:#087443; --warn:#a15c07; --bad:#b42318; --blue:#175cd3; }
    * { box-sizing:border-box; }
    body { margin:0; font:14px/1.55 -apple-system,BlinkMacSystemFont,"Segoe UI",sans-serif; background:var(--bg); color:var(--ink); }
    header { background:var(--panel); border-bottom:1px solid var(--line); padding:28px 32px 18px; }
    h1 { margin:0 0 6px; font-size:26px; letter-spacing:0; }
    h2 { margin:0 0 12px; font-size:17px; }
    main { max-width:1200px; margin:0 auto; padding:22px 18px 42px; display:grid; gap:16px; }
    section { background:var(--panel); border:1px solid var(--line); border-radius:8px; padding:18px; }
    .grid { display:grid; grid-template-columns:repeat(4,minmax(0,1fr)); gap:12px; }
    .card { border:1px solid var(--line); border-radius:8px; padding:14px; background:#fbfcff; min-height:104px; }
    .label { color:var(--muted); font-size:12px; text-transform:uppercase; }
    .value { font-size:22px; font-weight:700; margin-top:4px; }
    .ok { color:var(--ok); } .warn { color:var(--warn); } .bad { color:var(--bad); } .blue { color:var(--blue); }
    table { width:100%; border-collapse:collapse; }
    th,td { text-align:left; vertical-align:top; border-bottom:1px solid var(--line); padding:10px 8px; }
    th { color:var(--muted); font-size:12px; }
    code { background:#eef2f7; border-radius:4px; padding:1px 5px; }
    .flow { display:grid; grid-template-columns:repeat(4,1fr); gap:10px; }
    .step { border:1px solid var(--line); border-radius:8px; padding:12px; background:#fbfcff; }
    .small { color:var(--muted); font-size:12px; }
    @media (max-width:900px){ .grid,.flow{grid-template-columns:1fr;} header{padding:22px 18px;} }
  </style>
</head>
<body>
  <header>
    <h1>Kolable 5/12-5/21 訂單與付款對帳控制台</h1>
    <div class="small">Job 28b0cf53-d122-43f5-b908-ccde725242ad · owner Louis · data owner Wade · due 2026-05-24 · scope 2026-05-12 to 2026-05-21</div>
  </header>
  <main>
    <section>
      <h2>執行判斷</h2>
      <div class="grid">
        <div class="card"><div class="label">本輪主成果</div><div class="value blue">對帳工具箱</div><div class="small">匯入規格、例外分類、LINE 採用訊息、驗收測試。</div></div>
        <div class="card"><div class="label">P0 卡點</div><div class="value bad">缺 Wade 匯出</div><div class="small">沒有真實 CSV/XLSX，不得假算營收。</div></div>
        <div class="card"><div class="label">最小可驗收資料</div><div class="value ok">10 欄位</div><div class="small">order/payment/refund/source/customer/owner 等。</div></div>
        <div class="card"><div class="label">下一步</div><div class="value warn">24h 對帳</div><div class="small">收到檔案後 1 天內輸出缺口與追回清單。</div></div>
      </div>
    </section>
    <section>
      <h2>D1 / D7 / D14 / D30</h2>
      <div class="flow">
        <div class="step"><b>D1</b><br>Wade 匯出 5/12-5/21 訂單付款表；Louis 確認 28,000 訂單是否退刷/重買。</div>
        <div class="step"><b>D7</b><br>接入 Kolable 後台或金流 CSV，每日自動產生 unpaid/refund/duplicate 例外清單。</div>
        <div class="step"><b>D14</b><br>把直播來源、折扣碼、課程版本、客服溝通狀態併入 revenue scorecard。</div>
        <div class="step"><b>D30</b><br>上線週營收治理：訂單轉換、付款完成率、退款率、追回率納入 PLS。</div>
      </div>
    </section>
    <section>
      <h2>對帳規則</h2>
      <table>
        <tr><th>狀態</th><th>判定</th><th>Owner</th><th>動作</th></tr>
        <tr><td>paid</td><td>付款完成且無退款</td><td>PLS</td><td>納入已實收 revenue。</td></tr>
        <tr><td>pending</td><td>有訂單但付款未完成</td><td>Wade</td><td>補付款連結、追客服紀錄。</td></tr>
        <tr><td>refunded</td><td>已退刷或全額退款</td><td>Louis/Huber</td><td>確認是否重買，否則移出 MRR/GMV。</td></tr>
        <tr><td>duplicate</td><td>同 email/phone/course/amount 多筆</td><td>PLS</td><td>合併或人工確認，不重複計營收。</td></tr>
        <tr><td>missing_payment_ref</td><td>訂單缺金流交易 id</td><td>Wade</td><td>回補 transaction id 或標記無效單。</td></tr>
      </table>
    </section>
    <section>
      <h2>匯入欄位</h2>
      <table>
        <tr><th>欄位</th><th>範例</th><th>用途</th></tr>
        <tr><td><code>order_id</code></td><td>KOL-20260512-001</td><td>主鍵</td></tr>
        <tr><td><code>created_at</code></td><td>2026-05-12T20:15:00+08:00</td><td>歸屬直播期間</td></tr>
        <tr><td><code>customer_key</code></td><td>hash(email/phone)</td><td>去識別化重複偵測</td></tr>
        <tr><td><code>program_id</code></td><td>c5f940ee-439c-42e3-9b85-9584f50ea60c</td><td>課程/商品</td></tr>
        <tr><td><code>amount</code></td><td>28000</td><td>金額</td></tr>
        <tr><td><code>payment_status</code></td><td>paid/refunded/pending</td><td>對帳狀態</td></tr>
        <tr><td><code>payment_ref</code></td><td>txn_xxx</td><td>金流追溯</td></tr>
        <tr><td><code>discount_code</code></td><td>H8K8S14</td><td>直播/方案歸因</td></tr>
        <tr><td><code>source_event</code></td><td>2026-05-21 live</td><td>來源場次</td></tr>
        <tr><td><code>owner_note</code></td><td>退刷後重買待確認</td><td>人工處理</td></tr>
      </table>
    </section>
  </main>
</body>
</html>

learning-memory.json

{
  "job_id": "28b0cf53-d122-43f5-b908-ccde725242ad",
  "project": "AI 自建專案：Kolable - 直播銷講工具平台",
  "learned_at": "2026-05-24T05:35:00+08:00",
  "market_context": [
    {
      "source": "Shopify order status and Admin Order API documentation",
      "lesson": "成熟電商系統會區分 order status、payment/financial status、fulfillment status、return/refund status；Kolable 不應只用單一訂單狀態判斷收入。"
    },
    {
      "source": "TikTok Shop seller settlement explanation",
      "lesson": "直播/社群電商需要把付款、交付、結算期間分開，否則 GMV 與實收會混淆。"
    },
    {
      "source": "YouTube Shopping help",
      "lesson": "成熟內容電商會把商品標記與直播/影片來源連動；Kolable 應保存 source_event 和 discount_code 做轉換歸因。"
    }
  ],
  "pls_next_checks": [
    "Kolable 任務先檢查是否有真實訂單付款檔，沒有就追 data owner，不要只產策略摘要。",
    "所有 revenue scorecard 必須排除 refunded、pending、duplicate、missing payment ref。",
    "若有 28,000 培訓訂單，必須標記退刷/重買/待確認，並要求 evidence_ref。"
  ],
  "assumptions_overturned": [
    "Kolable 當前最大風險不是缺產品敘事，而是訂單付款證據未回收。",
    "直播銷講成效不能只看折扣碼或 LINE 訊息，必須接金流狀態。",
    "未取得真實匯出前，任何營收數字都只能是待驗證。"
  ],
  "next_iteration_condition": "Wade 提供 2026-05-12 至 2026-05-21 訂單付款 CSV/XLSX，或 Louis 提供 Kolable 後台 API/repo。"
}

order-payment-template.csv

production-brief.md

Production Brief：Kolable 訂單付款對帳落地包

場景

PLS context 指出 P0 overdue action：「Kolable: Wade 匯出 5/12-5/21 訂單與付款狀態（PLS 由 Louis 追）」，另有「28,000 培訓訂單退刷與重買狀態」相關風險。真實訂單檔尚未在 context 中提供，所以本輪不能假算營收；本輪交付一組可直接採用的對帳工具箱，讓 Wade 匯出後能在 24 小時內完成 revenue / refund / pending / duplicate 判定。

30 天路徑

• D1：Wade 依 order-payment-template.csv 匯出 2026-05-12 至 2026-05-21 訂單付款表；Louis 確認 28,000 訂單的退刷/重買決策。
• D7：把 CSV 對帳流程接成 PLS daily import，產生 unpaid、refunded、duplicate、missing payment ref 例外清單。
• D14：把直播場次、折扣碼、課程版本、客服溝通狀態合併成 revenue scorecard。
• D30：Kolable 上線週營收治理；付款完成率、退款率、追回率、直播場次轉換率進入 PLS 後台。

目的到目的 E2E

原始目的：不要再靠人追問 Kolable 訂單與付款狀態，而是讓 PLS 能自己判斷營收是否成立。 產出物：HTML 控制台、CSV 匯入模板、資料模型、驗收測試、決策紀錄、採用訊息。 人採用：Wade 匯出檔案；Louis 拍板退刷/重買與 revenue 認列；Huber 協助核對 28,000 訂單；zihrou 補產品定價附件。 指標改善：減少付款狀態不明、避免重複計營收、縮短退款/重買確認時間、讓直播銷講工具的商業指標可以被量化。

價值/錢路徑

• 營收：把 5/12-5/21 訂單轉成實收、待收、退款、重複四類，避免 GMV 虛胖。
• 成本：減少 Louis 手動追 Wade/Huber 的往返成本。
• 風險：將退刷、重買、缺金流 ref 的單獨立列，避免錯誤營收與客服爭議。
• 轉換：用折扣碼與直播場次追來源，找出哪場直播、哪個銷講版本帶來付款。
• 釋放人力：Wade 只需按模板匯出，PLS 自動分類並推給各 owner。

提升人的能力

這套工具箱讓 Louis 可以直接看「哪些錢算數、哪些要追回、哪些要砍掉不算」，也讓 Wade/Huber 不再只回文字，而是交可被系統驗收的資料。zihrou 可用同一套對帳結果判斷定價、退款政策與產品版本調整。

Solution Stack

• 脈絡框架：直播場次 → 訂單 → 金流 → 退款/重買 → revenue scorecard。
• 作業流程：Wade 匯出 → PLS import → 例外分類 → owner action → Louis revenue decision。
• 資料/DB 模型：見 data-model.md。
• 可操作工具：kolable-order-payment-control.html 與 order-payment-template.csv。
• 驗收指標：見 acceptance-tests.md。
• 採用與下一輪升級：收到真實檔案後進入 PLS daily import 或派 repo_change 接後台 API。

People Sync / LINE 草稿

Louis：Kolable 這輪不是再討論產品，而是先把 5/12-5/21 的錢對清楚。我已整理好 Wade 匯出模板與對帳規則；請 Wade 今天補 CSV/XLSX，Huber 同步確認 28,000 訂單是否退刷後重買。驗收：明天前要能分出 paid / pending / refunded / duplicate，否則 Kolable 的直播轉換率不能算數。

Wade：請照模板匯出 2026-05-12 到 2026-05-21 所有訂單與付款狀態，至少要有 order_id、created_at、customer_key、program_id、amount、payment_status、payment_ref、discount_code、source_event、owner_note。

Huber：請針對 28,000 培訓訂單標記「退刷、重買、待確認」三類，並附金流 ref 或客服紀錄。

不足資料

• 尚未取得 Wade 真實匯出檔。
• 尚未取得金流平台交易明細。
• 尚未取得 Kolable 後台 API 或 repo URL，因此本輪不聲稱 PR/部署。

sources.md

Market Context Sources

Current practice check date: 2026-05-24 Asia/Taipei.

1. Shopify Help Center, "Understanding your order statuses", accessed 2026-05-24. URL: https://help.shopify.com/en/manual/orders/manage-orders/order-status Use: mature ecommerce separates order, payment, fulfillment, and return statuses.

2. Shopify Admin REST Order API documentation, accessed 2026-05-24. URL: https://shopify.dev/docs/api/admin-rest/latest/resources/order Use: order records expose financial status and order status URL, supporting API-based reconciliation.

3. TikTok Shop Seller Blog, "Settlement periods for TikTok Shop sellers: explained", accessed 2026-05-24. URL: https://seller.tiktok.com/blog/settlement-periods-for-tiktok-shop-sellers-explained/10023278 Use: seller payout/settlement timing must be separated from order creation and delivery.

4. YouTube Help, "Buy products on YouTube", accessed 2026-05-24. URL: https://support.google.com/youtube/answer/9162845 Use: shoppable video/live commerce ties products to videos/live streams, supporting source attribution by event.

5. TechCrunch, "Facebook is shutting down its live shopping feature on October 1", published 2022-08-03, accessed 2026-05-24. URL: https://techcrunch.com/2022/08/03/facebook-shutting-down-live-shopping-feature-october/ Use: social/live commerce channel strategy changes quickly; Kolable should own order/payment data rather than depend on one platform feature.