export_patient_referrals
export_patient_referrals
A log of the latest state/version of every referral derived from a patient. One row per referral on the patient record.
referral_status and days_from_expiry are computed as of each daily refresh. updated_at advances when referral_status changes (on the start date, and on the day after the end date). days_from_expiry changes every day without moving updated_at; if you ingest incrementally, compute it from referral_end_date.
The source has no referral ID, so referral_id is a hash of the patient and the referral’s content. An edited referral arrives as a tombstone (is_deleted = TRUE) for the old referral_id plus a new row with a new referral_id. Removed referrals remain as tombstones so incremental consumers can remove them; filter is_deleted = FALSE for current referrals.
Primary Key: referral_id
Identifiers
Unique ID of the referral: a hash of the patient and the referral’s content. Stable while the referral is unchanged; an edit retires it and issues a new one
Unique ID associated with the patient in the Candid system
Medical Record Number - the foreign key to map patient to user’s own system
Referral number
Timestamps
Time at which the referral record was last updated in the database
Organization
User’s unique organization ID in the Candid system
User’s Organization / Company Name
Organization’s External ID - the ID that the organization uses to map to their own upstream system
Referral
Start date of the referral’s authorized period
End date of the referral’s authorized period, if known
Whether the referral has a known end date (distinguishes open-ended referrals from data-quality unknowns)
Number of days until (positive) or since (negative) the referral’s end date
Status of the referral: Pending, Active, Expired, or Unknown
Unit type the referral authorizes: VISIT or UNIT
Quantity of visits or units the referral authorizes
Whether the referral applies to all CPT codes rather than a specific set
Array of CPT codes the referral authorizes
Free-text notes on the referral
Referring Provider
Canonical ID of the referring provider, if present in the source data
First name of the referring provider
Last name of the referring provider
NPI of the referring provider
Type of the referring provider
Service Facility
ID of the referral’s service facility
Name of the referral’s service facility
NPI of the referral’s service facility
Deletes
Whether the referral was removed or edited in the Candid system. Removed referrals remain in this table as tombstones so incremental consumers can remove them.
When the referral was removed or edited in the Candid system