TCG Platform Data Model¶

Status: Active — Phases 4–7 documented; Phase 8 connector-platform reform (capability registry + cross-connector Listings spine, #235–#242), Lazada catalog-import connector (#255/#257), suppliers management (#256), structured product naming (#259/#260), the 4-status PO lifecycle (#265), Phase 8A cashflow spine (person, cash_location, cashflow_category, cashflow_entry — finance/ module, activated Phase-4 stub links, manage_finance/view_finance capabilities), and Phase 8C profitability (sales_channel_config fee policy + three derived finance reports — order-profit, channel-profitability, inventory-valuation) now reflected. Phase 8D (monthly P&L + finance dashboard UI + CSV/PDF exports) is designed but not built; Phase 8B equity/payouts was dropped. Last updated: 2026-06-19 Predecessors: Project Plan · PRD · Phase 2 Findings · Phase 3 Findings · Phase 4 Findings

This document is the cross-phase source of truth for every custom table, column, and relationship the TCG Commerce Operations Platform adds on top of Medusa core. Phase 4 reads from here for migrations and module scaffolding. Phase 8 reuses the sketched-only entities at the bottom.

1. Scope and design principles¶

• Single-tenant. Per project-plan.md "Deferred: SaaS & Multi-Tenancy", no tenant_id columns, no tenant routing, no subscription tables. The merchant's identity is environment config, not data.
• Configurable over hardcoded. Every business-policy knob — channel fee %, item margin, cost-allocation method, refund handling, listing visibility — lives in editable DB tables. An admin edits these via UI without code changes. Hardcoding the merchant's specific rules is forbidden.
• Module Links over foreign keys to Medusa internals. Custom tables connect to Medusa entities (product_variant, order, stock_location, customer_group, sales_channel) via defineLink() only — never raw FK to Medusa internal table names. This insulates the schema from Medusa upgrades pulled by Dependabot.
• Order is the source of truth for "what happened to this sale." Revenue and COGS reverse on goods-returned refunds. Profit is derived from inputs at query time, never stored as a column. Adjustments are first-class events.
• TCG semantics live behind a clean module boundary. A future non-TCG merchant could disable src/modules/tcg/ and run Medusa's vanilla product model.

2. What Medusa already provides¶

The platform uses these Medusa primitives unchanged. Custom modules link to them via Module Links.

3. Existing custom tables (shipped / active)¶

These are already in the database; this section documents them for completeness.

Still pending: order_status_event and the refund workflow remain design references; sales_channel_config shipped in Phase 8C (see §8C). See Phase 4 Findings, Phase 4 Wire-up Findings, and Phase 4 PO Flow Findings for the staged split.

3.1 Current admin API surfaces¶

The operator dashboard now creates catalog shells and variants outside the stock Medusa admin flow:

Structured product naming (product.metadata.tcg) — Phase 8 (#259 / #260)¶

Stored in the schemaless product.metadata.tcg JSON column (no migration). Drives a derived display name and connector backfill.

Derived name (deriveProductName, apps/dashboard/lib/product-naming.ts): at dashboard product-create time only, the form seeds product.title as name_override || "<Brand> <Item Name> <Item Type> [<Language>][<Set Code>]" (empty segments drop out; the bracket suffix vanishes when both bracket fields are blank). This is not a persisted invariant — the product API accepts an independent title, PATCH edits title while the TCG metadata stays read-only, so title and the structured fields can later diverge.

Reverse-parse (#260): applyReverseParsedTcg — the inverse of deriveProductName — backfills the five fields by parsing a title (longest-first token matching). Scope (connector-specific): Shopee applies it both when creating a new product (parsing the marketplace row title) and on its ALREADY_IMPORTED refresh pass — where it parses the existing Medusa product title (falling back to the marketplace title only when the product title is blank) and updates metadata only, not the title (#249). Lazada applies it only when creating a new product — LINK_ONLY commits just create listing rows and ALREADY_IMPORTED items are skipped, so existing products are not backfilled via Lazada. The merge is additive/idempotent (setIfAbsent, never overwrites operator values); unresolved brand/type sets naming_review = true instead of guessing. Field vocabularies are aggregated at GET /admin/dashboard/products/field-vocab.

4. Phase 4 tables — design reference¶

The active Phase 4 DCA scope. The base seven DCA tables shipped 2026-05-14, the operator wire-up shipped 2026-05-15, and the PO-flow follow-up added partial receipts on 2026-05-16. consolidation_event and consolidation_source shipped in Phase 8 (Migration20260614000000_consolidation). The remaining two (order_status_event, sales_channel_config) are still design references.

4.1 ERD¶

erDiagram
    SUPPLIER ||--o{ IMPORT_BATCH : "supplies"
    IMPORT_BATCH ||--|{ IMPORT_BATCH_ITEM : "contains lines"
    IMPORT_BATCH ||--o{ IMPORT_BATCH_FEE : "incurs fees"
    IMPORT_BATCH ||--o{ IMPORT_BATCH_CONTRIBUTOR : "funded by"
    IMPORT_BATCH_ITEM ||--o{ BATCH_ALLOCATION : "allocates to"
    IMPORT_BATCH_ITEM ||--o{ BATCH_ADJUSTMENT : "corrected by"
    IMPORT_BATCH_ITEM ||--o{ CONSOLIDATION_SOURCE : "drained by"
    CONSOLIDATION_EVENT ||--|{ CONSOLIDATION_SOURCE : "drains"
    CONSOLIDATION_EVENT ||--|| IMPORT_BATCH_ITEM : "creates"
    BATCH_ALLOCATION }o--|| MEDUSA_ORDER_LINE_ITEM : "links to"
    IMPORT_BATCH_ITEM }o--|| MEDUSA_PRODUCT_VARIANT : "links to"
    ORDER_STATUS_EVENT }o--|| MEDUSA_ORDER : "logs transitions for"
    SALES_CHANNEL_CONFIG }o--|| MEDUSA_SALES_CHANNEL : "extends"

4.2 Tables¶

supplier¶

Admin-managed list. Replaces the spreadsheet's free-text supplier codes (T, M, MM, CY, K, R, D, DCA, Buyback, Conversion).

Masking: non-admin callers (role without view_supplier_details) receive name, country, and notes as null via maskSupplier() (apps/server/src/api/_lib/can-see-supplier-names.ts). code is the unmaskable handle operators use to distinguish suppliers in PO lists. (#256 — supplier codename masking.)

import_batch¶

Header for one purchase event. Pre-orders are batches in in_transit status that haven't created inventory_level rows yet (payment is tracked separately via paid_at / total_sgd_paid, no longer a status).

import_batch_item¶

One line per SKU within a batch. The unit of cost-tracking and order allocation.

import_batch_receipt¶

One physical receipt event against an import batch item. This is distinct from batch_adjustment: receipts are the normal PO flow; adjustments are corrections, refunds, and write-offs.

import_batch_fee¶

Each fee on a batch. Triggers recomputeBatchCosts on insert/update/delete.

import_batch_contributor¶

Per-batch capital contributors. Repayment policy deferred to Phase 8.

batch_allocation¶

The heart of DCA + profit. One row per (order line, source batch line, quantity).

A single order line can produce multiple allocations if the line spans batches.

batch_adjustment¶

Unified table for all post-creation changes to a batch line. Triggers recomputeBatchCosts(batch_id) on insert.

The combination of cost_delta_per_unit and quantity_delta lets one row express compound corrections (e.g., supplier refund: qty −5 and cost reallocation across remaining lines via the recompute service).

D5 cost propagation: append-only epoch ledger¶

Shipped concurrency + cost-correctness hardening.

When a consolidation source line's cost later changes, the new cost is propagated onto the consolidated output line as append-only batch_adjustment rows — never by mutating an existing row. Each propagation row carries consolidation_source_id (the originating consolidation_source), a consolidation_epoch, the incremental share delta in cost_delta_per_unit, and its own immutable applied_at. The ledger key (consolidation_source_id, consolidation_epoch) is protected by a partial unique index (WHERE consolidation_source_id IS NOT NULL), so a given (source, epoch) can be appended at most once — concurrent recompute attempts converge instead of double-applying.

• Migration20260616000000_batch_adjustment_consolidation_source adds consolidation_source_id and the initial partial-unique on (consolidation_source_id) (IDX_batch_adjustment_consolidation_source_uniq).
• Migration20260617000000_batch_adjustment_consolidation_epoch adds consolidation_epoch and swaps the partial-unique to the composite (consolidation_source_id, consolidation_epoch) (IDX_batch_adjustment_consolidation_source_epoch_uniq).

Concurrency model¶

Consolidate / undo / rollback / recompute all serialize per-variant and per-item via Postgres transaction-scoped advisory locks (pg_advisory_xact_lock), with classid 1276 = variant and 1277 = item. recomputeBatchCosts is a fixpoint-retry driver: it computes the transitive consolidation closure, advisory-locks the accumulated scope, and retries in fresh transactions until the closure is a fixpoint (⊆ the advisory scope). Only then does it take row locks in one global order — events → outputs → items — so there is no post-lock scope growth and no lock-order inversion (no deadlocks). Invalid consolidation graphs are rejected up front by cycle detection (Kahn's algorithm), which raises a distinct ConsolidationCycleError rather than looping.

COGS correctness¶

computeOrderLineProfit applies a system_recompute batch_adjustment to an allocation only when allocation.created_at < adjustment.applied_at (operator adjustments always apply). This prevents D5 propagation from being double-counted against sales that already froze the propagated cost. The batched profit method behind the orders spreadsheet, getOrderLinesEconomics, now applies the same per-allocation temporal gate; previously it summed all adjustments per batch item and could double-count post-propagation sales. The auto-compensation (forgotten-fee) pass excludes D5 rows (consolidation_source_id IS NOT NULL) and is skipped entirely for is_consolidation_output items. D5 propagation itself is processed in topological order over the consolidation graph (parent before child).

consolidation_event¶

Shipped — Phase 8. Migration20260614000000_consolidation.

DCA consolidation: merges a variant's fragmented batch lines (≥2 lines with unsold-on-hand > 0) into one weighted-average line under the system CONSOLIDATION supplier. Cost-accounting only — no physical inventory change. Undo is allowed only before any sale against the consolidated line. A recompute pass (recomputeBatchCosts) propagates later source-cost adjustments onto the consolidated line via consolidation_source.

consolidation_source¶

Shipped — Phase 8. Migration20260614000000_consolidation.

Per-source-line drain record for a consolidation. Snapshots quantity_drained and cost_at_drain_sgd at the moment of consolidation, and lets recomputeBatchCosts propagate later source-cost adjustments onto the consolidated line. Each consolidation_source is the propagation key (consolidation_source_id) for the D5 epoch ledger on batch_adjustment — see D5 cost propagation.

order_status_event¶

Cross-channel transition log. One row per status change, regardless of which status field changed.

Connector-native status (Shopee, Lazada, future TikTok) lives on the connector's own *_order_sync table. This events table is a unified replay log; consumers (audit reports, exception triage) query by status_field.

sales_channel_config¶

Editable per-channel policy. Extends Medusa's sales_channel via Module Link.

Alternative implementation: store fee_pct in sales_channel.metadata JSONB. A standalone table is preferred for type safety and indexing; the metadata fallback is a Phase 4 implementation choice if Module Link complexity is undesirable.

4.3 Columns added to existing tables¶

Medusa order.metadata (extended)¶

These are not new tables — they live in Medusa's existing metadata JSONB column on order. Phase 4 just defines the keys.

tcg_channel_listing (Phase 3 — reshaped Phase 8 #236)¶

The cross-connector listings spine, keyed by (channel, external_listing_id, external_variation_id). This key has a non-unique index — one row per marketplace listing/variation is intended but not enforced, and concurrent catalog imports can race and insert duplicate rows for the same key (see Lazada commit.ts). The same variant may have multiple listings on the same channel (intentional cross-listings, #215). Linked to either a product_variant or a tcg_serialized_item (polymorphic; CHECK ensures exactly one is set).

The Listings API (GET /admin/dashboard/listings) enriches rows with variant SKU/title/condition, summed inventory, and channel display-name/icon from the connector registry. The Phase 4 per-channel boolean toggle was superseded by the status enum on this richer table. Note: coverage currently counts rows regardless of status — inactive/out_of_stock/paused listings still register as covered.

Lazada connector tables (Phase 8 — #255 / #257)¶

The Lazada connector currently implements catalog import only (OAuth + signed catalog scan/commit). Inventory sync and order ingestion are not yet built — there is intentionally no lazada_order_sync table (see Marketplace status, below).

lazada_connector_profile¶

Single-slot connector config (one row, SG region). Migration20260611120000_create_lazada_auth_tables.

lazada_auth_token¶

One token row per profile (unique profile_id). Mirrors the Shopee token diagnostics model. Migration20260611120000_create_lazada_auth_tables.

lazada_catalog_import¶

Per-import-run header. Migration20260612120000_create_lazada_catalog_import.

lazada_catalog_import_item¶

One row per discovered SKU in a run. Migration20260612120000_create_lazada_catalog_import.

5. Phase 8 entities — deferred (8B / 8C and beyond)¶

Phase 8A shipped person, cash_location, cashflow_category, and cashflow_entry — see §3 and the finance/ module rows. Phase 8C shipped sales_channel_config and the derived profitability reports — see §8C. The equity/payout entities below were scoped to the now-dropped Phase 8B and are no longer planned; they are retained here only for historical context.

5.1 ERD (deferred entities)¶

erDiagram
    PERSON ||--o{ STAKEHOLDER : "is"
    PERSON ||--o{ PAYEE : "is"
    STAKEHOLDER ||--o{ EQUITY_EVENT : "events"
    STAKEHOLDER ||--o{ STAKEHOLDER_PAYOUT : "payouts"
    CASH_LOCATION ||--o{ CASH_BALANCE_SNAPSHOT : "snapshots"
    ASSET_VALUATION_SNAPSHOT ||--o{ ASSET_VALUATION_LINE : "lines"

5.2 Entities (still deferred — Phase 8B / 8C)¶

• stakeholder — equity-holding role. Columns: person_id, stake_pct, initial_investment_sgd, monthly_payout_sgd. Pre-seeded: Ivan 45%, ZW 45%, X 10%. (Phase 8B — dropped.)
• equity_event — adjustments to stakeholder equity over time. (Phase 8B — dropped.)
• stakeholder_payout — recurring monthly payouts; ad-hoc bonuses recorded separately. (Phase 8B — dropped.)
• payee — non-equity service providers. Columns: person_id, payee_type (salary / director_fee / bookkeeping / other), amount, period (monthly / yearly).
• cash_balance_snapshot — periodic actual count per cash_location, used for discrepancy calculation.
• asset_valuation_snapshot + asset_valuation_line — periodic inventory valuation by category (Sealed JP / EN / CH / KR / Others / Outstanding / Singles).

Phase 4 stubs that have now been activated by Phase 8A (see §6 Module Links): - import_batch_contributor.person_id — now a live Module Link → finance/person (previously nullable text stub). - import_batch_fee.related_cashflow_id — now a live Module Link → finance/cashflow_entry (previously nullable text stub). - batch_adjustment.related_cashflow_id — now a live Module Link → finance/cashflow_entry (previously nullable text stub).

5.3 cashflow_entry — shipped correctness & concurrency hardening¶

The Phase 8A cashflow_entry create path is the only ledger-writing surface, so it carries idempotency, money-precision, and referential guards as shipped (the entity summary is in §3; Migration20260614200000_finance_init created the base tables).

Idempotent create. cashflow_entry gained an idempotency_key (nullable text) column. Create is idempotent via three partial unique indexes — on idempotency_key, related_order_id, and related_batch_id, each WHERE col IS NOT NULL AND deleted_at IS NULL — and the service returns the existing row on a unique-violation conflict (PG SQLSTATE 23505) instead of erroring or double-inserting. Migration Migration20260616120000_cashflow_hardening.

Money precision. capital_in_sgd / capital_out_sgd are validated and normalized at the API/service boundary to an exact 2-dp decimal via integer-cents — rejecting fractional cents, non-finite values, negatives, and any amount over MAX_AMOUNT_SGD (= 1,000,000,000 SGD). No binary-float arithmetic is used for ledger values (BigInt cents internally; persisted as Medusa bigNumber with raw_* companions).

Create-path validation (in-transaction). A create requires a non-deleted, non-archived cashflow_category and cash_location (and poc_person_id when supplied), and enforces category kind versus amount direction — an inflow category accepts capital_in_sgd only, an outflow category accepts capital_out_sgd only — on top of the inflow-XOR-outflow guard (the latter also a DB CHECK).

Race-safe reference seeds. cash_location and cashflow_category each carry a partial unique index on the active name (WHERE deleted_at IS NULL AND archived = false), and seeding uses INSERT … ON CONFLICT DO NOTHING, so concurrent boot/seed paths can't create duplicate active reference rows. Migration Migration20260616130000_finance_reference_unique.

Transfers deferred (v1). A cashflow_entry is single-leg — one cash_location, exactly one of capital_in_sgd / capital_out_sgd > 0 (the schema/CHECK is the guard). There is intentionally no double-entry / journal / paired-leg model in v1; a transfer between two cash locations is recorded as two separate entries.

Server-side response-shape convention. Finance route code defines local, hand-synced response shapes in modules/finance/types/api-shapes.ts rather than importing @tcg/shared-types, because medusa build (tsconfig rootDir = apps/server) forbids importing the shared-types source from outside apps/server. This mirrors the dca/ module's types/api-shapes.ts pattern.

6. Module Links inventory¶

Every cross-module relationship is realized via Medusa's defineLink(). No raw FKs point at Medusa internal tables.

7. Workflows¶

Three load-bearing workflows are implied by this data model. Implementation lives in src/modules/dca/workflows/ and src/modules/ops/workflows/.

7.1 recomputeBatchCosts(batch_id)¶

Triggered on: - Insert, update, or delete of import_batch_fee for the batch. - Insert of batch_adjustment against any of the batch's lines. - Insert, update, or delete of import_batch_item belonging to the batch.

Steps: 1. Sum invoice value across all batch lines. 2. Compute each line's value share according to import_batch.cost_allocation_method. 3. Sum batch fees; allocate per line by share. 4. Compute effective_cost_per_unit_sgd from line invoice, allocated fees, manual overrides where applicable, and operator-entered batch_adjustment.cost_delta_per_unit rows. system_recompute rows bridge already-sold allocations and do not feed the unsold-stock effective cost. 5. Update each line's effective_cost_per_unit_sgd. 6. If any line is a source for a consolidation_event, propagate the cost change onto the consolidated output line as an append-only D5 epoch-ledger batch_adjustment (keyed (consolidation_source_id, consolidation_epoch)). This is the fixpoint-retry, advisory-locked path described under D5 cost propagation: it computes the transitive closure, serializes per-variant/per-item (advisory classids 1276/1277), takes row locks in the global events→outputs→items order, processes the graph in topological order, and rejects cycles via ConsolidationCycleError.

7.2 consolidateBatches(variant_id, source_batch_item_ids?)¶

Operator-triggered (or auto-triggered when stock is fragmented). Replaces the spreadsheet's Method = Conversion mechanism cleanly, without phantom orders.

Steps: 1. If source_batch_item_ids is null, select all import_batch_item rows for variant_id where quantity_remaining > 0. 2. Compute weighted-average cost = Σ(quantity_remaining × effective_cost_per_unit) / Σ(quantity_remaining). 3. Create a new import_batch with supplier code CONSOLIDATION (system supplier), status = completed. 4. Create a single import_batch_item on the new batch with the consolidated quantity and weighted-avg cost; mark is_consolidation_output = true. 5. Create a consolidation_event row pointing at the new batch_item. 6. For each source: create a consolidation_source row capturing quantity_drained and cost_at_drain_sgd; set source's quantity_remaining = 0.

7.3 refundOrder(order_id, refund_type, amount, line_items?)¶

Operator chooses refund_type at refund time: - goods_returned — customer returns the item; we restock. - money_only — customer keeps the goods; we eat the loss.

For goods_returned: 1. Run Medusa native return workflow on the order line. 2. For each affected batch_allocation: - If source import_batch_item.quantity_remaining > 0 (still active): mark allocation reversed; source's quantity_remaining += quantity. - If source has been consolidated/closed: emit batch_adjustment(batch_item_id = current_active_for_variant, quantity_delta = +qty, reason = customer_return) plus a second batch_adjustment(cost_delta_per_unit = original_cost − active_cost, reason = return_cost_difference). 3. Update order.refunded_amount += amount. 4. Set order.ops_status = Refunded. 5. For manual orders: create cashflow_entry (Capital Out). For marketplace orders: rely on escrow tracking — no separate cashflow line.

For money_only: 1. No inventory change. 2. Update order.refunded_amount += amount. 3. Set order.ops_status = Refunded. 4. For manual orders: create cashflow_entry. Marketplace: escrow.

Profit then derives correctly: goods-returned → revenue 0, COGS 0, profit 0. Money-only → revenue 0, COGS unchanged, profit = −COGS (real loss surfaces in P&L).

8. Profit derivation¶

Profit is not stored. It is derived at query time from four inputs:

effective_cost_per_unit(allocation) =
    cost_per_unit_at_allocation
    + Σ batch_adjustment.cost_delta_per_unit
        WHERE adjustment.batch_item_id = allocation.batch_item_id
        AND adjustment.applied_at ≤ as_of_date

line_cogs    = effective_cost_per_unit × quantity_allocated × (allocation.is_reversed ? 0 : 1)
line_revenue = unit_price × quantity − refunded_amount_proportional
line_profit  = line_revenue − line_cogs

This guarantees that any batch_adjustment automatically flows into every affected order's profit on the next query. No denormalized columns to update, no risk of stale values.

For dashboards at scale, materialized views (profit_by_order_mv, profit_by_channel_mv) refreshed on batch_adjustment insert are an acceptable optimization. They are caches, not the source of truth.

8.1 Phase 8C profitability (shipped)¶

Phase 8C makes the derivation above operator-facing. It ships the sales_channel_config fee-policy table (see §3) plus three read-only reporting endpoints and the config CRUD, all in the finance/ module. No new profit state is persisted — every figure is derived at query time from existing inputs (allocations, adjustments, refunds, escrow, fee config).

Endpoints under /admin/dashboard/finance/:

Profit model (per order):

order_profit = revenue − refunds − COGS − fees

• COGS comes from the DCA cost basis via getOrderLinesEconomics, which applies the same A1-temporal-correct per-allocation adjustment gate as computeOrderLineProfit (an allocation only picks up a system_recompute batch_adjustment when allocation.created_at < adjustment.applied_at) — so post-propagation sales are not double-counted.
• Refunds are netted once at the order level from order.summary.refunded_total, never per-line, avoiding double-deduction.
• Fees resolve in priority order, recorded as fee_source: escrow | configured | none:
  1. escrow — Shopee actual escrow settlement (commission + service + seller-transaction + actual-shipping + credit-card fees), used only when a live shopee_order_sync row exists for the order.
  2. configured — otherwise revenue × fee_pct + fixed_fee_sgd from the order's sales_channel_config.
  3. none — otherwise fees are 0.

Inventory valuation:

inventory_value = Σ (quantity_remaining × effective_cost_per_unit_sgd)

evaluated point-in-time via resolveUnsoldOnHand (consolidation-safe: it accounts for quantity_consolidated_out so consolidated lines are not double-counted).

Date ranges: report windows are SGT-local dates converted to UTC as a half-open [from, to) interval, so a day boundary belongs to exactly one period.

8.2 Phase 8D — monthly P&L + finance dashboard UI + exports (designed, not built)¶

9. Status state machines¶

9.1 ops_status¶

Universal across all order origins. Operator's primary daily indicator.

Allocated → Completed → (terminal)
Allocated → Cancelled → (terminal)
Allocated → Error     (recoverable)
Completed → Refunded  (terminal)

Allocation happens automatically on order ingestion (Phase 3 already wires this for Shopee). The Apps Script "For Allocation" intermediate state is dropped — the new system allocates synchronously.

9.2 finance_status¶

Same column, two enum families depending on order origin.

Marketplace (Shopee, Lazada, future TikTok):

Pending Order Confirmation
  → Pending Order Received Confirmation
  → Ready to Release
  → Released to My Balance
  → (terminal)
* → Cancelled

Manual (Telegram, POS, Card Show, Web):

Unpaid → Partial → Paid → (terminal)
*       → Refunded

9.3 Channel-native status¶

Not stored on order. Lives on the connector's own sync table:

• shopee_order_sync.shopee_status (Phase 3 — shipped)
• lazada_order_sync.lazada_status (planned — order ingestion not built; catalog-import only shipped #255/#257)
• Future tiktok_order_sync.tiktok_status (post-MVP)

For Telegram / POS / Card Show / Web orders, there is no channel-native status — ops_status and finance_status are sufficient.

This pattern means adding a new platform requires zero changes to the order table. The connector module brings its own sync table; order_status_event picks up transitions automatically via the status_field string.

9.4 Composite "fully done" view¶

There is no canonical "the order is done" status. The UI surfaces all three views and computes:

fully_done = (
  ops_status = Completed
  AND finance_status IN ('Released to My Balance', 'Paid')
  AND channel_native_status IN (terminal_set OR null)
)

Operators reading the order list see all three columns side by side, matching how the spreadsheets present them today.

10. CSV → schema mapping¶

Every column in the merchant's nine Q1 2026 spreadsheets, mapped to its destination.

10.1 Master Inventory¶

10.2 Imports¶

10.3 Additional Import Fees¶

10.4 Cashflow¶

10.5 Finance, PnL¶

Mostly derived. The Finance sheet's Cash section maps to finance/cash_location + cashflow_entry (Phase 8A shipped — see §3). The Equity / Stakeholder / Manpower / Payee sections map to deferred Phase 8B/8C entities (see §5.2). PnL is a derived monthly aggregate.

10.6 Orders, Shopee Orders, Lazada Orders¶

11. Open questions (deferred)¶

• Period locking policy for batch_adjustment.applied_at — when March's books are closed, do late adjustments redirect to current period or are they allowed retroactively? Phase 8 decision.
• Stocktake module — stocktake_session + stocktake_line for periodic physical counts. Master Inventory's Actual Stocktake column suggests this exists informally. Phase 5 (admin UI) is the natural home.
• Box composition / kits — Contents, num_packs, cards_per_pack columns. Decision deferred per user direction; revisit when Phase 7 (Event/POS) needs box-opening flows.
• Person model — when Phase 8 introduces the unified person entity, decide whether Ivan-as-stakeholder, Ivan-as-contributor, Ivan-as-payee are one row or separate. Affects import_batch_contributor.person_id resolution.
• Per-batch contributor repayment policy — pooled vs. batch-specific repayment, schedules, partial settlement. Phase 8.
• Cash discrepancy handling — Finance sheet's Discrepancies -$10,959.45 line. Tolerated drift or system-enforced reconciliation? Phase 8 policy.
• Channel-fee complexity — sales_channel_config.fee_pct is single-value. If Shopee's commission scheme later requires tiered or category-specific rates, extend to a sales_channel_fee_rule table.

12. Implementation order recommendation¶

1. supplier, import_batch, import_batch_item, import_batch_fee, import_batch_contributor (skeleton tables — let admin start recording imports).
2. batch_allocation + Module Link to order_line_item (cost flows to orders → unblocks profit derivation).
3. batch_adjustment + the recomputeBatchCosts workflow (cascading recompute).
4. consolidation_event, consolidation_source, the consolidateBatches workflow.
5. order_status_event, the columns added to order.metadata.
6. sales_channel_config.
7. tcg_channel_listing cross-connector reshape (#236).
8. Refund workflow (refundOrder).
9. Admin UI surfaces (Phase 5 territory).

Phase 8 entities deferred. CSV migration (FR-15) runs after step 4 against staging to validate batch_allocation and DCA arithmetic against the spreadsheet.

Links¶

• Project Plan
• PRD
• Phase 2 Findings — TCG metadata + serialized item validation.
• Phase 3 Findings — Shopee connector lessons.
• tcg-platform CLAUDE.md — code-side orientation.