TCG Platform Data Model¶

Status: Active — Phase 4 DCA + PO flow stable on staging; Phase 5 RBAC, Phase 6 Shopee multi-env, Phase 7 Shopee profile tables, and post-cutover token cleanup documented. Last updated: 2026-05-18 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: consolidation_event, consolidation_source, order_status_event, sales_channel_config, and refund workflow. 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:

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. The remaining four (consolidation_event, consolidation_source, 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).

import_batch¶

Header for one purchase event. Pre-orders are batches in paid or in_transit status that haven't created inventory_level rows yet.

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).

consolidation_event¶

DCA consolidation: a workflow that drains source batch lines and creates a new consolidated line.

consolidation_source¶

Per-source-batch row for a consolidation. Lets recomputeBatchCosts propagate post-consolidation cost adjustments into the consolidated batch.

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 — extended)¶

Add: is_listed: boolean (default true). Editable per channel per listing. Replaces the spreadsheet's Listed column with a per-channel granularity.

5. Phase 8 entities — sketched, not scaffolded¶

These are documented here so Phase 4 tables don't accidentally block them. No migrations until Phase 8 begins.

5.1 ERD (deferred entities)¶

erDiagram
    PERSON ||--o{ STAKEHOLDER : "is"
    PERSON ||--o{ PAYEE : "is"
    PERSON ||--o{ IMPORT_BATCH_CONTRIBUTOR : "is"
    PERSON ||--o{ CASHFLOW_ENTRY : "POC"
    STAKEHOLDER ||--o{ EQUITY_EVENT : "events"
    STAKEHOLDER ||--o{ STAKEHOLDER_PAYOUT : "payouts"
    CASHFLOW_ENTRY }o--|| CASH_LOCATION : "moves through"
    CASHFLOW_ENTRY }o--|| CASHFLOW_CATEGORY : "classified as"
    CASH_LOCATION ||--o{ CASH_BALANCE_SNAPSHOT : "snapshots"
    ASSET_VALUATION_SNAPSHOT ||--o{ ASSET_VALUATION_LINE : "lines"

5.2 Entities¶

• person — single identity that can play multiple roles (stakeholder, payee, contributor, cashflow POC). One row per real human.
• stakeholder — equity-holding role. Columns: person_id, stake_pct, initial_investment_sgd, monthly_payout_sgd. Pre-seeded: Ivan 45%, ZW 45%, X 10%.
• equity_event — adjustments to stakeholder equity over time.
• stakeholder_payout — recurring monthly payouts; ad-hoc bonuses recorded separately.
• payee — non-equity service providers. Columns: person_id, payee_type (salary / director_fee / bookkeeping / other), amount, period (monthly / yearly).
• cashflow_entry — date, description, category_id, cash_location_id, capital_in_sgd, capital_out_sgd, poc_person_id, related_batch_id, related_order_id. The related_* FKs let a cashflow entry promote into a batch fee or refund.
• cashflow_category — admin-managed list. Initial seed: Salary, Investment, Withdrawal, Refund, Sale, Marketing, Commission, Card Show Booth Fee, Supplier Payment, Additional Purchase, Transport, Interest.
• cash_location — Maribank, OCBC, Wise Business, Wise Personal, Shopee Cash, Shopee Cash In Transit, Physical Cash, etc.
• 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 leaves room for these by: - Storing import_batch_contributor.person_id as nullable text now (Module Link target deferred until Phase 8 introduces person). - Storing batch_adjustment.related_cashflow_id and import_batch_fee.related_cashflow_id as nullable text, populated only after Phase 8 lands. - Storing the bot_order_id and poc_person_id similarly.

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: re-run consolidation math and emit a batch_adjustment on the consolidated output line.

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 = arrived. 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.

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 (Phase 6 — planned)
• 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 (see §5.2). The Finance sheet's Asset / Cash / Equity / Source / Stakeholder / Manpower / Payee sections all map to Phase 8 entities. 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.is_listed extension.
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.