FP&A Data Warehouse

Hospital Financial Planning & Analysis data warehouse built on Supabase PostgreSQL with star-schema design, MongoDB sync pipeline, and integrated React dashboards.

Design Principles

1. Star schema — central fact tables + conformed dimensions, optimized for slice-and-dice
2. Supabase-native — leverages Postgres, materialized views, pg_cron, realtime
3. MongoDB sync — edge function pulls SalesOrder/Invoice/Encounter data hourly
4. In-app dashboards — Recharts + MUI in the existing web monorepo, same auth
5. Dimension reuse — extends existing gold_dim_scheme, gold_dim_time, terminology_cache

Data Model — Star Schema

                     ┌──────────────────┐
                     │  fpa_dim_time    │ (existing gold_dim_time)
                     └────────┬─────────┘
                              │
┌──────────────┐    ┌────────┴─────────┐    ┌──────────────────┐
│fpa_dim_payer │────│  fpa_fact_charge  │────│fpa_dim_department│
│(extends      │    │  (grain: line    │    │ + cost center    │
│ gold_dim_    │    │   item per SO)   │    │ + revenue code   │
│ scheme)      │    └────────┬─────────┘    └──────────────────┘
└──────────────┘             │
                    ┌────────┴─────────┐
┌──────────────┐    │fpa_fact_encounter │    ┌──────────────────┐
│fpa_dim_      │────│ (grain: one per  │────│fpa_dim_service   │
│ diagnosis    │    │  encounter)      │    │ _line            │
│(ICD-10+DRG)  │    └────────┬─────────┘    └──────────────────┘
└──────────────┘             │
                    ┌────────┴─────────┐
┌──────────────┐    │fpa_fact_labor    │    ┌──────────────────┐
│fpa_dim_      │────│ (grain: shift/   │────│fpa_dim_physician │
│ procedure    │    │  pay period)     │    └──────────────────┘
│(ICD-9+CPT)   │    └──────────────────┘
└──────────────┘
                    ┌──────────────────┐
                    │fpa_fact_census   │
                    │ (grain: daily    │
                    │  per ward/unit)  │
                    └──────────────────┘

                    ┌──────────────────┐
                    │fpa_budget        │
                    │ (grain: monthly  │
                    │  per cost center)│
                    └──────────────────┘

Dimension Tables

fpa_dim_department (NEW)

Maps organizational units to cost centers, revenue codes, and GL accounts.

Column	Type	Description
id	serial PK
dept_code	text UNIQUE	Internal department code
dept_name	text	Department name (EN)
dept_name_th	text	Department name (TH)
cost_center_code	text	Cost center for accounting
cost_center_name	text
revenue_code	text	UB-04 revenue code (4-digit)
revenue_code_desc	text	Revenue code description
gl_account	text	General ledger account
dept_type	text	‘clinical’, ‘ancillary’, ‘support’, ‘admin’
is_revenue_generating	bool
parent_dept_id	int FK	Hierarchy

fpa_dim_service_line (NEW)

Maps DRGs and departments to service lines for executive reporting.

Column	Type	Description
id	serial PK
service_line_code	text UNIQUE	e.g., ‘CARDIO’, ‘ORTHO’, ‘ONCO’
service_line_name	text
service_line_name_th	text
drg_ranges	jsonb	Array of DRG code ranges that map to this line
dept_codes	text[]	Department codes that map to this line
is_active	bool

fpa_dim_physician (NEW)

Provider dimension for contribution margin analysis.

Column	Type	Description
id	serial PK
physician_id	text UNIQUE	MongoDB user _id
physician_name	text
physician_name_th	text
specialty	text
department_id	int FK	Primary department
npi	text	National provider ID (if applicable)
employment_type	text	‘staff’, ‘contract’, ‘visiting’
fte	numeric(4,2)	Full-time equivalent
is_active	bool

fpa_dim_diagnosis (NEW)

ICD-10 + DRG cross-reference for case-mix analysis. Synced from terminology_cache + coding_worklist.

Column	Type	Description
id	serial PK
icd10_code	text	ICD-10-CM code
icd10_display	text
icd10_chapter	text	Chapter (e.g., “IX - Circulatory”)
icd10_block	text	Block (e.g., “I20-I25”)
drg_code	text	Mapped DRG
drg_description	text
drg_type	text	‘MS-DRG’, ‘Thai-DRG’, ‘AP-DRG’
relative_weight	numeric(6,4)	DRG relative weight
gmlos	numeric(4,1)	Geometric mean LOS
arithmetic_mlos	numeric(4,1)	Arithmetic mean LOS
mdc	text	Major Diagnostic Category
mdc_description	text
service_line_id	int FK	Maps to fpa_dim_service_line

fpa_dim_procedure (NEW)

CPT/HCPCS + ICD-9-CM + APC cross-reference.

Column	Type	Description
id	serial PK
cpt_code	text	CPT/HCPCS code
cpt_description	text
icd9_code	text	ICD-9-CM procedure code
icd9_description	text
apc_code	text	Ambulatory Payment Classification
apc_description	text
apc_weight	numeric(6,4)	APC relative weight
procedure_class	text	‘surgical’, ‘diagnostic’, ‘therapeutic’, ‘ancillary’
service_line_id	int FK	Maps to fpa_dim_service_line

Fact Tables

fpa_fact_encounter (grain: 1 row per encounter)

Central fact for volume, revenue, cost, and case-mix analysis.

Column	Type	Source
id	bigserial PK
encounter_id	text UNIQUE	MongoDB encounter _id
encounter_vn	text	Visit number
encounter_class	text	‘AMB’, ‘IMP’, ‘EMER’
admission_date	date
discharge_date	date
los_days	int	Computed: discharge - admission
patient_id	text	MongoDB patient _id
patient_hn	text
physician_id	int FK	→ fpa_dim_physician
department_id	int FK	→ fpa_dim_department
service_line_id	int FK	→ fpa_dim_service_line
diagnosis_id	int FK	→ fpa_dim_diagnosis (PDX)
payer_scheme_code	text FK	→ gold_dim_scheme
Revenue fields
gross_charges	numeric(14,2)	SalesOrder.total
contractual_adj	numeric(14,2)	coverage adjustments
discount	numeric(14,2)	SalesOrder.discount
net_revenue	numeric(14,2)	SalesOrder.netPay
Cost fields
direct_cost	numeric(14,2)	Sum of charge-level costs
indirect_cost	numeric(14,2)	Allocated overhead
total_cost	numeric(14,2)	direct + indirect
Margin
contribution_margin	numeric(14,2)	net_revenue - direct_cost
operating_margin	numeric(14,2)	net_revenue - total_cost
DRG
drg_code	text	From coding_worklist
relative_weight	numeric(6,4)
adj_relative_weight	numeric(6,4)
expected_payment	numeric(14,2)	RW × base rate
cmi_contribution	numeric(6,4)	This encounter’s CMI contribution
Status
sales_order_id	text	MongoDB SO _id
sales_order_status	text
claim_status	text
collection_status	text	‘collected’, ‘partial’, ‘pending’, ‘written_off’
synced_at	timestamptz	Last sync from MongoDB

fpa_fact_charge (grain: 1 row per line item per sales order)

Charge-level detail for department revenue, supply cost, and revenue code analysis.

Column	Type	Source
id	bigserial PK
encounter_id	text FK	→ fpa_fact_encounter
sales_order_id	text	MongoDB SO _id
line_number	int
department_id	int FK	→ fpa_dim_department
procedure_id	int FK	→ fpa_dim_procedure
revenue_code	text	UB-04 4-digit
charge_code	text	Internal item code
charge_description	text
quantity	numeric(10,2)
unit_price	numeric(12,2)
gross_amount	numeric(14,2)	qty × unit_price
discount_amount	numeric(14,2)
net_amount	numeric(14,2)
cost_amount	numeric(14,2)	Item cost (if available)
margin	numeric(14,2)	net_amount - cost_amount
charge_date	date
charge_category	text	‘room’, ‘pharmacy’, ‘lab’, ‘imaging’, ‘procedure’, ‘supply’, ‘professional’, ‘other’

fpa_fact_labor (grain: 1 row per staff per pay period)

Labor cost and productivity tracking.

Column	Type	Description
id	bigserial PK
staff_id	text
staff_name	text
department_id	int FK	→ fpa_dim_department
pay_period_start	date
pay_period_end	date
job_class	text	‘RN’, ‘MD’, ‘tech’, ‘admin’, etc.
employment_type	text	‘full_time’, ‘part_time’, ‘contract’, ‘agency’
fte	numeric(4,2)
regular_hours	numeric(6,2)
overtime_hours	numeric(6,2)
premium_hours	numeric(6,2)
total_hours	numeric(6,2)
regular_pay	numeric(12,2)
overtime_pay	numeric(12,2)
premium_pay	numeric(12,2)
benefits_cost	numeric(12,2)
total_labor_cost	numeric(14,2)
units_of_service	numeric(10,2)	Patient days, visits, cases
hours_per_uos	numeric(6,2)	Productivity metric

fpa_fact_census (grain: 1 row per ward per day)

Daily bed census for occupancy and capacity analysis.

Column	Type	Description
id	bigserial PK
census_date	date
department_id	int FK	→ fpa_dim_department
ward_code	text
ward_name	text
total_beds	int	Licensed/staffed beds
occupied_beds	int	Midnight census
admissions	int	Today’s admissions
discharges	int	Today’s discharges
transfers_in	int
transfers_out	int
occupancy_rate	numeric(5,2)	occupied / total × 100
patient_days	int	= occupied_beds
alos_snapshot	numeric(5,2)	Running ALOS for current patients

fpa_budget (grain: 1 row per cost center per month)

Budget and forecast for variance analysis.

Column	Type	Description
id	bigserial PK
fiscal_year	int
fiscal_month	int	1-12
department_id	int FK	→ fpa_dim_department
budget_type	text	‘original’, ‘revised’, ‘forecast’
revenue_budget	numeric(14,2)
direct_cost_budget	numeric(14,2)
indirect_cost_budget	numeric(14,2)
labor_budget	numeric(14,2)
supply_budget	numeric(14,2)
fte_budget	numeric(6,2)
volume_budget	numeric(10,2)	Expected encounters/patient days
approved_by	text
approved_at	timestamptz

Materialized Views (Aggregate Layer)

fpa_agg_monthly_revenue

Monthly revenue rollup by all key dimensions.

GROUP BY: fiscal_year, fiscal_month, department_id, service_line_id, payer_scheme_code
MEASURES: gross_charges, net_revenue, contractual_adj, discount,
          encounter_count, patient_days, avg_los, avg_charge_per_encounter

fpa_agg_monthly_cost

Monthly cost rollup by cost center.

GROUP BY: fiscal_year, fiscal_month, department_id
MEASURES: direct_cost, indirect_cost, labor_cost, supply_cost,
          cost_per_encounter, cost_per_patient_day, cost_per_rvu

fpa_agg_service_line_pl

Service line profit & loss — the FP&A holy grail.

GROUP BY: fiscal_year, fiscal_month, service_line_id
MEASURES: gross_charges, net_revenue, direct_cost, indirect_cost,
          contribution_margin, operating_margin,
          encounter_volume, avg_cmi, avg_los

fpa_agg_payer_profitability

Per-payer profitability analysis.

GROUP BY: fiscal_year, fiscal_month, payer_scheme_code
MEASURES: net_revenue, total_cost, margin, encounter_count,
          denial_rate, avg_days_to_payment, write_off_amount

fpa_agg_drg_performance

DRG-level performance for case-mix analysis.

GROUP BY: fiscal_year, fiscal_month, drg_code
MEASURES: case_count, avg_los, gmlos_expected, los_variance,
          avg_rw, total_rw, avg_cost, avg_revenue, avg_margin,
          readmission_count

fpa_agg_physician_contribution

Physician contribution margin (politically sensitive — RLS restricted).

GROUP BY: fiscal_year, fiscal_month, physician_id
MEASURES: encounter_count, total_revenue, total_cost,
          contribution_margin, avg_rvu, avg_cmi

fpa_agg_ar_aging

Accounts receivable aging buckets.

GROUP BY: snapshot_date, payer_scheme_code
MEASURES: current_0_30, aging_31_60, aging_61_90, aging_91_120, aging_over_120,
          total_ar, days_in_ar, collection_rate

MongoDB Sync Pipeline

Edge Function: fpa-mongo-sync

Runs hourly via pg_cron. Connects to MongoDB via the REST API gateway.

Sync flow:

1. Query fpa_sync_cursor for last sync timestamp per collection
2. Fetch changed SalesOrders since cursor (via gateway /v2/financial/salesOrder/list)
3. For each SalesOrder:
   • Upsert fpa_fact_encounter (encounter-level aggregates)
   • Upsert fpa_fact_charge (line items)
   • Resolve dimensions (department, physician, diagnosis, procedure)
4. Update cursor
5. Refresh materialized views (if stale > 1 hour)

Sync tracking table:

CREATE TABLE fpa_sync_cursor (
  collection text PRIMARY KEY,
  last_synced_id text,
  last_synced_at timestamptz,
  row_count bigint,
  error_count int DEFAULT 0,
  last_error text
);

Dashboard Modules (Frontend)

Module Structure

web/packages/miniapps/fpa-dashboard/
├── index.ts
├── FpaDashboard.tsx              — Main layout (sidebar nav + content + filters)
├── types.ts
├── hooks/
│   ├── useFpaData.ts             — Supabase queries + realtime subscriptions
│   ├── useFpaFilters.ts          — Universal filter state
│   └── useFpaPeriodComparison.ts — Period-over-period calculations
├── components/
│   ├── FpaFilterBar.tsx          — Universal dimension filters
│   ├── KpiCard.tsx               — Reusable KPI card with sparkline
│   ├── WaterfallChart.tsx        — Revenue waterfall (gross → net)
│   └── ContributionMarginTable.tsx — Sortable CM table
├── pages/
│   ├── ExecutiveSummary.tsx       — KPI cards + trends
│   ├── RevenueAnalysis.tsx        — By dept/service line/payer/DRG/ICD-10
│   ├── CostAnalysis.tsx           — By cost center/case/day/RVU
│   ├── Profitability.tsx          — CM by service line/DRG/payer/physician
│   ├── LaborProductivity.tsx      — FTEs, hours/UOS, overtime, agency
│   ├── ArCollections.tsx          — Aging, days in AR, collection rate, denials
│   ├── VolumeActivity.tsx         — Census, admissions, ALOS, occupancy, CMI
│   └── StrategicPlanning.tsx      — Forecasts, capex, volume projections
└── sample-data/
    └── generator.ts               — Realistic seed data for demos

Universal Filter System

Every dashboard page supports slicing by:

• Time: fiscal year, quarter, month, custom range; actual vs budget vs prior year
• Entity: facility, department, cost center
• Service line: mapped from DRG or department
• Payer: scheme code, payer type (public/private/self-pay)
• Physician: individual or by specialty
• DRG / APC: case-mix grouping
• Encounter class: AMB, IMP, EMER

Migration Plan

Migration	Content
090_fpa_dimensions.sql	All dimension tables + seed data
091_fpa_facts.sql	Fact tables + indexes
092_fpa_aggregates.sql	Materialized views + refresh functions
093_fpa_budget.sql	Budget tables + variance views
094_fpa_sync.sql	Sync cursor + helper functions
095_fpa_seed_demo.sql	Realistic demo data (18 months)
096_fpa_rls.sql	Row-level security policies
097_fpa_cron.sql	pg_cron schedules for refresh + sync

50-Iteration Roadmap

Phase 1: Data Foundation (1-10)

1. Architecture doc (this file)
2. Migration 090: dimension tables
3. Migration 091: fact tables
4. Migration 092: materialized views
5. Migration 093: budget tables
6. Migration 094-095: sync + demo seed
7. MongoDB sync edge function
8. Seed data generator (realistic 18-month hospital data)
9. Fix revenue-explorer chart + scaffold FPA module
10. FPA routing, layout, universal filter bar

Phase 2: Executive Dashboard (11-16)

11. KPI card component with sparklines
12. Revenue trend time-series (monthly, by payer)
13. Volume & activity cards (admissions, ALOS, occupancy, CMI)
14. Payer mix donut + trend
15. Gross-to-net revenue waterfall
16. Executive summary page assembly

Phase 3: Revenue Analysis (17-24)

17. Revenue by department / revenue code (horizontal bar + trend)
18. Revenue by service line
19. Revenue by payer (net revenue, contractual adjustments)
20. Revenue by DRG (top 20 DRGs by revenue, case count)
21. Revenue by ICD-10 chapter
22. Revenue per case / per patient day / per visit trends
23. Gross vs net waterfall by department
24. Denials & write-offs analysis

Phase 4: Cost Analysis (25-30)

25. Cost by cost center / department (treemap + table)
26. Cost per case / per patient day / per RVU
27. Direct vs indirect cost split (stacked bar)
28. Variable vs fixed cost analysis
29. Supply cost per case for high-volume procedures
30. Actual vs budget variance (heat map)

Phase 5: Profitability (31-36)

31. Contribution margin by service line — P&L table
32. Contribution margin by DRG / procedure
33. Contribution margin by payer
34. Contribution margin by physician (RLS-gated)
35. Service line P&L statement view (income statement format)
36. Payer-level profitability with contract analysis

Phase 6: Labor & Productivity (37-40)

37. FTE tracking (actual vs budgeted, stacked by job class)
38. Worked hours per unit of service
39. Overtime % and agency/contract labor spend
40. Productivity dashboard (hours/UOS by department)

Phase 7: AR & Collections (41-44)

41. AR aging report (0-30, 30-60, 60-90, 90+ days stacked bar)
42. Days-sales-outstanding trend
43. Collection rate by payer + denial rate
44. Denial analysis by reason code (Pareto chart)

Phase 8: Operational & Strategic (45-48)

45. LOS vs expected (GMLOS from DRG) — variance chart
46. Readmission rates by DRG
47. Occupancy & bed utilization heat map
48. Volume forecasting by service line (trend + projection)

Phase 9: Polish & Integration (49-50)

49. Data export (Excel/PDF), print layouts, scheduled email reports
50. Drill-down navigation (click service line → DRG → encounter detail)