Skip to content

Phase 8A — Cashflow Spine (Kickoff Plan)

Status: SHIPPED 2026-06-16

All 5 PRs merged. finance/ module live: person, cash_location (10 seeded rows), cashflow_category (12 seeded rows, kind enum: inflow/outflow/both), cashflow_entry (inflow-XOR-outflow CHECK constraint, bigNumber capital_in/out_sgd with raw_* companions). Phase-4 stub Module Links activated: import_batch_contributor.person_id → person, import_batch_fee.related_cashflow_id → cashflow_entry, batch_adjustment.related_cashflow_id → cashflow_entry. Capabilities: manage_finance (admin + finance), view_finance (admin, finance, ops read-only). Admin API at /admin/dashboard/finance/{people,cash-locations,categories,cashflow}. Dashboard pages under /finance/cashflow. Migration20260614200000_finance_init creates + seeds idempotently; module loader re-seeds on boot. Next: Phase 8B (stakeholders, equity events, payouts).

What Phase 4 already delivered (don't rebuild this)

  • import_batch_contributor.person_id as nullable text with a name_snapshot companion (Migration20260508012114)
  • import_batch_fee.related_cashflow_id as nullable text (Migration20260508012114)
  • batch_adjustment.related_cashflow_id as nullable text (Migration20260508021023)
  • computeOrderLineProfit service in apps/server/src/modules/dca/ (derives revenue/COGS/profit at query time, never stored)

These three nullable-text columns are intentional Phase 8 entry-points. 8A activates them by introducing the target tables + Module Links — no Phase 4 column changes required.

What Phase 8A adds

Scope: capital tracking only

Phase 8A is capital tracking (cash movements, deposits, salaries, supplier payments, contributor capital, refunds-out, transfers between locations). It is NOT revenue tracking — sales revenue is derived from Medusa orders + computeOrderLineProfit and surfaces in Phase 8C dashboards. The cashflow journal records money movements that are not direct sales (the same way the Q1 2026 Cashflow.csv does today).

Tables (4 new)

All under a new apps/server/src/modules/finance/ module.

Table Purpose
person Single root identity table. One row per real human the merchant transacts with (stakeholders, contributors, payees, cashflow POCs, buyers). Not joined to Medusa user — Medusa user is for staff login, person is for anyone the business touches.
cash_location Where money lives. Admin-managed list; seeded with Maribank, OCBC, Wise Business, Wise Personal, Shopee Cash, Shopee Cash In Transit, Cash (ZW), Cash (Ivan), Cash (XQ), Physical Cash (matches Finance.csv).
cashflow_category Category list for cashflow entries. Admin-managed; seeded with Salary, Investment, Withdrawal, Refund, Sale, Marketing, Commission, Card Show Booth Fee, Supplier Payment, Additional Purchase, Transport, Interest (matches Cashflow.csv actually-used categories).
cashflow_entry One row per money movement. Column shape mirrors Cashflow.csv (Date, Description, Buyer, POC, Category, Capital In (SGD), Capital Out (SGD)) plus operational fields (cash_location_id, related_* Module Links, created_by_user_id, audit timestamps).

8A wires Module Links to the existing Phase 4 columns rather than altering Phase 4 tables:

  • import_batch_contributor.person_id → person.id
  • import_batch_fee.related_cashflow_id → cashflow_entry.id
  • batch_adjustment.related_cashflow_id → cashflow_entry.id

Plus 8A's own forward links:

  • cashflow_entry.poc_person_id → person.id (the human responsible for this entry)
  • cashflow_entry.related_batch_id → import_batch.id (Module Link, nullable — for Additional Purchase / Supplier Payment rows that originated as PO fees)
  • cashflow_entry.related_order_id → Medusa order.id (Module Link, nullable — for Refund / Sale rows tied to a customer order)
  • cashflow_entry.cash_location_id → cash_location.id (mandatory — every entry is somewhere)

Locked design decisions (D1–D7)

D1 — person is a standalone root identity, NOT joined to Medusa user

Medusa user is purely the staff login identity (auth + user_role). person is the business-counterparty identity: stakeholders, capital contributors, payees, suppliers' POCs, cashflow POCs, buyers (eventually).

The two may correspond — e.g., Ivan Chin is both a Medusa user (he logs in) and a person (he is a stakeholder, contributes capital, is the POC on many cashflow rows). We model this with a nullable, optional person.linked_user_id text column (audit-only — not a Module Link, because most person rows will not be staff). Adding the back-reference later is non-breaking.

person columns:

Column Type Notes
id text PK Medusa-generated id
display_name text NOT NULL What shows in dropdowns. Single field — no separate first/last (matches CSV reality).
email text NULL Optional.
phone text NULL Optional.
notes text NULL Free-form.
linked_user_id text NULL Audit-only reference to a Medusa user.id when the person is also a staff member.
archived boolean NOT NULL default false Hide from dropdowns but preserve history.
created_at, updated_at timestamptz Standard.

No legal_name / id_number / KYC columns. If those become needed later, they extend person non-breakingly.

D2 — cashflow_entry columns mirror Cashflow.csv 1-for-1, plus operational fields

The merchant's spreadsheet is the spec. Column-for-column mapping:

Cashflow.csv column cashflow_entry column Type Notes
Date entry_date date NOT NULL Date the movement happened, NOT when it was logged.
Description description text NOT NULL Free-form.
Buyer buyer_name_snapshot text NULL Snapshot, not FK. Real CSV uses this on sale rows only; 8A keeps it as text. Linking to a customer entity is a Phase 8C decision.
POC poc_person_id text NULL → Module Link to person.id The human responsible. Display name is resolved through the link, not stored on the row.
Category category_id text NOT NULL → FK to cashflow_category.id Mandatory (the CSV has it on every row).
Capital In (SGD) capital_in_sgd numeric(14,2) NOT NULL default 0
Capital Out (SGD) capital_out_sgd numeric(14,2) NOT NULL default 0

Plus operational fields:

Column Type Notes
cash_location_id text NOT NULL → FK to cash_location.id Where the money is. CSV doesn't have this column today; the operator decides it at entry time.
related_batch_id text NULL → Module Link to import_batch.id For PO-linked entries (e.g., Additional Purchase rows that should reconcile against a batch fee).
related_order_id text NULL → Module Link to Medusa order.id For order-linked entries (e.g., Refund rows for a specific customer order).
created_by_user_id text NOT NULL Audit. The Medusa user that submitted the entry.
created_at, updated_at timestamptz Standard.

Check constraint: (capital_in_sgd > 0 AND capital_out_sgd = 0) OR (capital_in_sgd = 0 AND capital_out_sgd > 0) — a row is either an inflow or an outflow, never both, never neither. Mirrors how the spreadsheet uses the two columns (every CSV row has exactly one populated).

D3 — cashflow_category and cash_location are admin-managed with seeds, archive-only

Both are reference tables. Operators with manage_finance can:

  • Add new categories / locations
  • Rename them
  • Archive them (archived = true)

They cannot delete — historical cashflow_entry rows reference them. Archive hides them from dropdowns but preserves the join.

Seeds (run in PR-1):

  • cashflow_category: 12 categories from Cashflow.csv (Salary, Investment, Withdrawal, Refund, Sale, Marketing, Commission, Card Show Booth Fee, Supplier Payment, Additional Purchase, Transport, Interest).
  • cash_location: 10 locations from Finance.csv (Maribank, OCBC, Wise Business, Wise Personal, Shopee Cash, Shopee Cash In Transit, Cash (ZW), Cash (Ivan), Cash (XQ), Physical Cash).

Categories carry a kind enum (inflow | outflow | both) so the UI can grey out the wrong amount field at entry time:

  • inflow: Investment, Sale, Interest
  • outflow: Salary, Withdrawal, Marketing, Commission, Card Show Booth Fee, Supplier Payment, Additional Purchase, Transport
  • both: Refund (refund-in if we received it, refund-out if we issued one)

D4 — Capability matrix: view_finance + manage_finance

Two new capabilities added to apps/server/src/modules/staff/permissions.ts:

Capability Verbs
view_finance GET on every /admin/dashboard/finance/* endpoint
manage_finance POST / PATCH / DELETE on every /admin/dashboard/finance/* endpoint

Role grants:

Role view_finance manage_finance
admin ✓ ✓
finance ✓ ✓
ops ✓ ✗ (read-only — needs to see cashflow context but can't edit it)
event_staff ✗ ✗

Server-side enforcement via requireCapability("manage_finance") on every mutating route. Dashboard RoleGate for UX (already established as the source-of-truth-is-server pattern from Phase 5).

The three Phase 4 stub columns (import_batch_contributor.person_id, import_batch_fee.related_cashflow_id, batch_adjustment.related_cashflow_id) become reachable from both sides via defineLink Module Links in PR-3. No column changes, no backfill needed (values are all NULL today).

After PR-3, query.graph() calls from Phase 4 surfaces (e.g., the /imports/[id] detail page) can pull contributor.person.display_name and fee.related_cashflow.entry_date. The dashboard surfacing of those joins is a 8C concern (the /finance/* pages built in PR-5 don't need them).

The name_snapshot column on import_batch_contributor stays as a fallback for any pre-8A rows whose person_id is still NULL. Future contributors created post-8A populate person_id and the snapshot stays as audit (mirrors how buyer_name_snapshot stays on cashflow_entry).

D6 — No revenue side in 8A

The spreadsheet records sales revenue as one-line cashflow entries with category Sale (typically representing a Shopee escrow release or batch payout, not individual orders). 8A preserves that pattern — operators log net revenue as a single Sale entry against the appropriate cash location.

8C ("Profitability / PnL views") wires per-order revenue from computeOrderLineProfit into the dashboards. 8C does NOT auto-create cashflow entries from orders — the operator continues to log net-of-fees escrow movements manually. Connecting Shopee escrow records (shopee_escrow table from Phase 3) into auto-generated cashflow_entry rows is a 8C decision and explicitly out of 8A scope.

D7 — UTC dates, SGT presentation

entry_date stored as date (no time component, matches CSV). All API responses serialize as YYYY-MM-DD strings. Dashboard renders as Mar 29, 2026 style (matching CSV) using Asia/Singapore for any timestamp display (created_at shown in audit views).

The merchant operates entirely in SGT — no multi-timezone support. Phase 8D period-locking will use SGT month boundaries.

PR breakdown

Each PR is reviewable in ~30 min and merges independently. Later PRs depend on earlier ones for data plumbing but never break older PRs' code paths.

PR Scope Tests
PR-1 New apps/server/src/modules/finance/ module. person, cash_location, cashflow_category models + migrations + seeds. Register in medusa-config.ts. Module unit tests for CRUD on each. 3 unit specs (one per model)
PR-2 cashflow_entry model + migration. Module Links: cashflow_entry.poc_person_id → person.id, cashflow_entry.related_batch_id → import_batch.id, cashflow_entry.related_order_id → Medusa order.id. Service: FinanceModuleService.createCashflowEntry + the inflow-xor-outflow check constraint enforced at both DB and service layers. 1 unit spec + 1 integration spec covering check-constraint + both Module Links resolvable
PR-3 Activate Phase 4 stub Module Links: import_batch_contributor.person_id → person.id, import_batch_fee.related_cashflow_id → cashflow_entry.id, batch_adjustment.related_cashflow_id → cashflow_entry.id. No new migrations (columns already exist from Phase 4). 1 integration spec asserting bidirectional query.graph() traversal works
PR-4 Admin API: /admin/dashboard/finance/people (CRUD), /admin/dashboard/finance/cash-locations (CRUD), /admin/dashboard/finance/categories (CRUD), /admin/dashboard/finance/cashflow (CRUD + list with ?category_id=, ?cash_location_id=, ?from=, ?to=, ?poc_person_id=, ?related_batch_id=, ?related_order_id= filters + pagination). New capabilities view_finance + manage_finance in the staff matrix. Full DTO surface in @tcg/shared-types/finance. HTTP integration tests: permissions × every endpoint × every role + happy-path lifecycle for each table.
PR-5 Dashboard pages: /finance/cashflow list (filters + Add Entry button), /finance/cashflow/new form, /finance/cashflow/[id] detail, /finance/categories admin, /finance/cash-locations admin, /finance/people admin. Sidebar comingSoon removed from Finance / Costing. RHF + Zod schemas re-exported from @tcg/shared-types. Playwright E2E happy-path: login as finance → add person → add category → add cash_location → log a cashflow entry → see it in the list filtered by category + date.

After PR-5 merges, the Phase 8A exit criterion is met: operator can log a day's worth of cashflow movements into the dashboard instead of the spreadsheet, with categories + cash locations + POCs typed. Real Q1 2026 data import is a manual operator task and not part of 8A.

Risks ratified at design lock

Risk Mitigation
Spreadsheet has multiple worksheets (Cashflow / Finance / PnL / Master Inventory / etc.); 8A only models one (Cashflow). Acknowledged. Finance.csv = 8C (cash position view derived from cashflow_entry aggregation per cash_location); PnL.csv = 8C (profitability dashboards); Master Inventory = out of Phase 8 entirely (already covered by Medusa inventory + variant editor).
Importing the existing Q1 2026 spreadsheet rows. Operator-driven manual entry in 8A. A bulk CSV importer can ship in 8C or as a one-off script — not in 8A.
Real cashflow entries sometimes split across cash locations (e.g., "Supplier paid $X from Maribank + $Y from Wise"). Operators log two cashflow_entry rows. The spreadsheet currently does this; 8A matches. A "split entry" UI is a later UX nicety.
cash_location balances drift if entries are mis-categorized as inflow vs outflow. The check constraint (D2) makes the operator commit at entry time. Period-locking + reconciliation (8D) catches drift after the fact.
person proliferation if operators add a new row for the same human under slightly different names. UI dedup on creation (autocomplete against existing person.display_name). Hard-merge tooling is out of scope; archive + relink can fix it manually.
finance role didn't exist before; activating capabilities for an unused role. The finance role is already defined in staff from Phase 5. PR-4 just grants it new capabilities — no new role.

Out of scope (deferred to later slices)

  • 8B (Stakeholders & payouts): stakeholder, equity_event, stakeholder_payout, payee. Builds on person.
  • Phase 4 close-out (gated correctness for 8C): refundOrder workflow (goods-returned vs money-only branching), sales_channel_config (per-channel fee schemes), order_status_event (cross-channel status log). Inserted between 8B and 8C.
  • 8C (Profitability / PnL): Per-batch / per-channel / monthly PnL dashboards leveraging computeOrderLineProfit + cashflow rollups. Cash balance per location (SUM(in) - SUM(out) GROUP BY cash_location). Asset valuation snapshots. Optional: Shopee escrow → cashflow_entry auto-bridge.
  • 8D (Period locking + month-close): batch_adjustment.applied_at-driven period locks; close-month workflow + runbook.
  • Phase 4 ergonomics (no urgency): consolidation_event + consolidateBatches, tcg_channel_listing.is_listed.
  • Within 8A: Bulk CSV import, split-across-locations UI, person-merge tooling, multi-currency (cashflow is SGD-only), recurring-entry scheduler (e.g., auto-create monthly salary rows).