Skip to content

Phase 4: Costing / DCA — Findings (skeleton)

Status: In progress ⏳ (skeleton merged 2026-05-14; consolidation, order-status, sales-channel-config, refund workflow, and admin UI still outstanding) Predecessor: Data Model (serves as Phase 4 plan — no separate kickoff doc) Exit criterion target: "DCA output matches the existing spreadsheet for a reference batch."

1. Purpose + method

Stand up the cost-tracking spine of the platform: import batches, fees, contributors, line items, adjustments, allocations, and the cost-allocation engine. The downstream profit derivation (revenue − COGS at query time) hangs off batch_allocation rows produced when orders consume batch stock.

Phase 4 is intentionally split into two passes:

  1. Skeleton (this commit) — tables, models, services, migrations, unit + integration tests, no admin UI. Confirms the data model + cost-allocation strategies are correct in isolation.
  2. Wire-up (next) — consolidation events, order-status event log, sales-channel-config, refund workflow, and the Phase-5 admin UI surfaces that drive operator workflows.

The skeleton went in via PR #53 (merge commit 6a17240 on main).

2. What shipped

2.1 Tables (7 new)

All under src/modules/dca/ and registered in apps/server/medusa-config.ts.

Table Source migration Purpose
supplier Migration20260508012114 Admin-managed supplier list.
import_batch Migration20260508012114 Per-purchase header (one purchase event).
import_batch_item Migration20260508012114 One line per SKU within a batch — the cost-tracking + allocation unit.
import_batch_fee Migration20260508012114 Per-batch fees with typed fee_type enum.
import_batch_contributor Migration20260508012114 Per-batch contributors with name_snapshot (Phase 8 person not yet introduced).
batch_allocation Migration20260508021023 Heart of DCA + profit. One row per (order line, source batch line, quantity); cost is frozen at allocation. Indexed on order_line_item_id for profit queries (Migration20260508030000_batch_allocation_order_line_item_id_idx).
batch_adjustment Migration20260508021023 Unified post-creation correction surface with typed reason enum.

FK constraints stay scoped within DCA tables only — no destructive DROPs on Medusa core or other modules' tables (mitigation for the Phase 3 migration-generator gotcha).

  • import_batch_item.variant_id → Medusa product_variant (N:1).
  • batch_allocation.order_line_item_id → Medusa order_line_item (N:1; backed by an additional plain text column for direct WHERE queries without traversing the link join table).

2.3 Enums (typed; matches data-model.md exactly)

  • ImportBatchStatus — draft | ordered | paid | in_transit | arrived | for_storage | closed
  • CostAllocationMethod — proportional_by_value | proportional_by_quantity | equal_split | manual
  • PaidTax — yes_taxless | no | paid
  • ImportBatchFeeType — shipping_overseas | shipping_local | gst | customs_duty | bank_fee | fx_loss | other
  • BatchAdjustmentReason — cost_correction | forgotten_fee | fx_relock | supplier_shortfall | supplier_refund | write_off | quantity_correction | customer_return | return_cost_difference

2.4 Services + workflows

  • DcaModuleService.recomputeBatchCosts(batch_id) — re-runs cost-allocation against current fees + adjustments and rewrites effective_cost_per_unit_sgd for every line on the batch. Thin workflow wrapper at src/workflows/recompute-batch-costs.ts delegates to the service. sharedContext threads through child calls so it composes inside a transaction.
  • DcaModuleService.computeOrderLineProfit({ order_line_item_id, unit_price, quantity, refunded_amount }) — derives revenue, COGS (frozen allocation cost + batch_adjustment deltas), and profit at query time. Profit is never stored. Reversed allocations contribute zero COGS.
  • src/modules/dca/services/cost-allocation-strategies.ts — pure functions allocateProportionalByValue, allocateProportionalByQuantity, allocateEqualSplit. Unit-tested in isolation.

2.5 Tests

  • 27 DCA tests pass: service unit tests, cost-allocation-strategy unit tests, and 3 recomputeBatchCosts integration scenarios (forgotten-fee redistribution, cost_correction propagation, supplier_shortfall via reduced received quantity).
  • 5 computeOrderLineProfit integration scenarios cover: no allocations, frozen cost, deltas, goods-returned refund (revenue=0, COGS=0, profit=0), money-only refund (COGS unchanged, profit=−COGS).

3. What is not in the skeleton

Carried into a follow-up phase (or Phase 5 ops UI):

  • consolidation_event + consolidation_source tables + consolidateBatches workflow — drains fragmented batch lines into a single weighted-average line. Spec exists in data-model §4.2 + §7.2.
  • order_status_event — cross-channel status transition log. Spec in data-model §4.2 + §9.
  • sales_channel_config — per-channel fee_pct / fee_scheme_notes / default_shipping_option_id. Spec in data-model §4.2.
  • tcg_channel_listing.is_listed extension — replaces the spreadsheet's Listed column with per-channel granularity.
  • refundOrder workflow — goods-returned vs money-only branching (Phase 4 belongs functionally; needs Phase 3 refund hook + Phase 5 UI surface to operate).
  • Admin UI surfaces — import-batch creation/edit, fee entry, contributor entry, batch view with allocation breakdown, manual batch_adjustment entry. These belong in Phase 5.
  • CSV → DCA migration test — re-importing one historical Q1 2026 reference batch from the spreadsheet and asserting the engine output matches the spreadsheet's "Total Cost Per Unit (SGD)" column. This is the formal exit criterion for Phase 4.

4. Gaps / concerns surfaced during the skeleton

  • Monorepo path move (PR #51) reshaped Phase 4 file locations. feat/phase-4-dca-skeleton was originally rooted at src/modules/dca/; after the merge from main, everything moved to apps/server/src/modules/dca/. Git's rename detection handled it; no content changes. All future Phase 4 paths in this venture's docs assume apps/server/.
  • tsc --noEmit rejected string literals where enum members are required. Four DCA test fixtures used "shipping_local" / "cost_correction" etc. instead of the typed enum members — fine pre-monorepo (the workspace tsconfig was looser), broken post-monorepo. Fixed in commit d1e3e43; an enum import was missing in recompute-batch-costs.spec.ts. Going forward: prefer enum members in test fixtures from the start.
  • seed.ts depends on .medusa/types/query-entry-points. Generated by yarn workspace @tcg/server build ahead of tsc. CI runs Medusa build then Type check, so this works in CI; running tsc --noEmit directly on a fresh checkout fails until you build once. Documented in .github/workflows/ci.yml step ordering.
  • Pre-existing test failure note from commit cf888fe ("rejects duplicate batch_number" — Medusa's mapped error message format) was resolved in commit 2be51f8 by broadening the regex. Closed.

5. Phase 4 decisions recorded

  • Profit is derived, not stored. A column for profit would invariably drift after a batch_adjustment lands. Query-time derivation via batch_allocation.cost_per_unit_at_allocation + sum of relevant batch_adjustment.cost_delta_per_unit is authoritative. Materialized views are a Phase 9 caching optimization if dashboards demand it.
  • batch_allocation.cost_per_unit_at_allocation is frozen at the moment of allocation. Post-hoc adjustments are layered on via batch_adjustment rather than rewriting historical allocations. This preserves audit fidelity (you can always reconstruct what the books said at sale time).
  • batch_allocation.is_reversed = true zeros that row's COGS contribution. Goods-returned refunds set it; money-only refunds do not (the cost stays in the books — that's the real loss).
  • batch_allocation.order_line_item_id is stored as a nullable text column AND a Module Link. The duplicate column exists so computeOrderLineProfit can WHERE directly without traversing the link join table; indexed for that query path. Module Link still defines the relationship for Medusa's RemoteJoiner and admin queries.
  • No raw FK from import_batch_contributor to a person table — person_id is nullable text, name_snapshot carries the display name until Phase 8 introduces person. Same pattern for batch_adjustment.related_cashflow_id and import_batch_fee.related_cashflow_id.

6. Open questions for the wire-up phase

  • recomputeBatchCosts trigger points. Per data-model.md §7.1, recompute should fire on insert/update/delete of import_batch_fee and on insert of batch_adjustment. Currently the workflow is callable on demand only — no Medusa subscriber wires it to those events yet. Decision needed: Medusa subscriber per event, or call recompute from the admin-UI mutation handlers as Phase 5 lands?
  • Consolidation reverse-cascade. When a source batch line's effective_cost_per_unit_sgd changes after it has been consolidated, do we propagate the delta into the consolidated output line automatically (via a batch_adjustment(reason = cost_correction) on the output), or surface it as a queue item for the operator? data-model.md §7.1 step 6 implies automatic; needs confirmation under operator workflow.
  • CSV migration target batch. Pick one historical Q1 2026 batch from Q1 2026 ExzenTCG - Imports.csv + ... - Additional Import Fees.csv to be the reference for the spreadsheet-parity exit-criterion test. Candidate: any closed batch with at least one fee and one contributor, no consolidation events.
  • paid_tax enum surface in admin UI. Spreadsheet's free-text "Paid Tax" column collapses cleanly to yes_taxless | no | paid, but operators may want notes. Decide whether to add a paid_tax_notes text column.