Schema for Billing Cycles

HardSQL · PostgreSQL

Problem

How would you design a schema to track patient billing cycles over time at Candid Health?

Represent patients, billing cycles, and cycle events, then return a chronological cycle history that shows prior-cycle context and the latest recorded event for each cycle.

Output

One row per billing cycle, excluding patients without cycles
Columns: patient identity, cycle dates and status, billed amount, prior cycle status, days between cycles, latest event type, and latest event timestamp
Sort by patient ID, cycle start date, and cycle ID

Schema

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
patient_name	VARCHAR(120)	Patient display name
external_patient_key	VARCHAR(50)	Optional identifier from a connected provider system

billing_cycles

Column	Type	Description
cycle_idPK	INT	Unique billing-cycle identifier
patient_id	INT	Patient associated with the cycle
cycle_start_date	DATE	Date the billing cycle began
cycle_end_date	DATE	Date the billing cycle ended, NULL for an open cycle
cycle_status	VARCHAR(30)	Current lifecycle status of the cycle
payer_name	VARCHAR(100)	Payer associated with the cycle
billed_amount	NUMERIC(12,2)	Amount billed for the cycle

billing_events

Column	Type	Description
event_idPK	INT	Unique billing event identifier
cycle_id	INT	Billing cycle affected by the event
event_type	VARCHAR(40)	Event recorded for the billing cycle
event_at	TIMESTAMP	Timestamp when the event occurred
event_note	TEXT	Optional operational note

Tablespatientsbilling_cyclesbilling_events