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, so rows that carry it get a new _cds_row_version and re-sync daily. To avoid that churn, compute it from authorization_end_date instead of storing it.
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 when the authorization or its patient was last updated, or when the authorization’s removal was detected. Also advances when the authorization starts or expires.
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. Delivered as ARRAY<STRING> in BigQuery-direct datasets.
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
Sync columns
Time when this row’s delivered data last changed. Use this for incremental ingestion.
Hash of this row’s delivered data. Changes whenever any value in the row changes.