export_patient_authorizations
export_patient_authorizations
A log of the latest state/version of every authorization derived from a patient. One row per authorization on the patient record.
authorization_status and days_from_expiry are computed as of each daily refresh. updated_at advances when authorization_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 authorization_end_date.
The source has no authorization ID, so authorization_id is a hash of the patient and the authorization’s content. An edited authorization arrives as a tombstone (is_deleted = TRUE) for the old authorization_id plus a new row with a new authorization_id. Removed authorizations remain as tombstones so incremental consumers can remove them; filter is_deleted = FALSE for current authorizations.
Primary Key: authorization_id
Identifiers
Unique ID of the authorization: a hash of the patient and the authorization’s content. Stable while the authorization 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
Authorization number
Timestamps
Time at which the authorization 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
Authorization
Payer ID the authorization was issued under
Name of the payer the authorization was issued under
Start date of the authorization’s authorized period
End date of the authorization’s authorized period, if known
Whether the authorization has a known end date (distinguishes open-ended authorizations from data-quality unknowns)
Number of days until (positive) or since (negative) the authorization’s end date
Status of the authorization: Pending, Active, Expired, or Unknown
Unit type the authorization authorizes: VISIT or UNIT
Quantity of visits or units the authorization authorizes
CPT code the authorization authorizes
Whether the authorization applies to all CPT codes rather than a specific set
Whether the source system flagged this as not requiring prior authorization
Array of diagnosis codes the authorization covers
NPI of the authorization’s billing provider
Free-text notes on the authorization
Service Facility
ID of the authorization’s service facility
Name of the authorization’s service facility
NPI of the authorization’s service facility
Deletes
Whether the authorization was removed or edited in the Candid system. Removed authorizations remain in this table as tombstones so incremental consumers can remove them.
When the authorization was removed or edited in the Candid system