export_service_line_expected_revenue
export_service_line_expected_revenue
Expected revenue for each service line, priced against the organization’s configured fee schedules. Recorded as a ledger like export_transaction: entries are never edited, and a change writes a void entry plus a new entry. One row per (expected revenue entry, charge transaction) pair, so de-duplicate on expected_revenue_id before summing amount_cents.
Primary Key: service_line_expected_revenue_pk
Identifiers
Primary key: a hash of expected_revenue_id and charge_transaction_id, unique per row. Use it to upsert rows during incremental ingestion.
ID of the expected revenue entry. Deduplicate by filtering to current charge_transaction_id by setting both voided_by_transaction_id and replaced_by_transaction_id to NULL.
The charge transaction covered by this entry. Joins to export_transaction.transaction_id on charge rows, and to export_transaction.charge_transaction_id. NULL when the entry has no charge yet. Considered current when both voided_by_transaction_id and replaced_by_transaction_id are NULL.
The service line this entry prices. Joins to export_service_line.service_line_id and export_transaction.service_line_id.
The parent charge. Joins to export_transaction.charge_id, which is how payments and adjustments tie back to their charge.
The payer expected to pay, as a payer UUID. This matches how payers appear in export_transaction.counter_party_id. NULL when the patient’s primary insurance has no payer on file.
The payer plan group from the patient’s primary insurance at the time the entry was priced. NULL when the insurance has no plan group.
On a void entry, the ID of the entry it reverses. NULL on all other entries.
On an entry that has been reversed, the ID of the void entry. NULL if the entry has not been voided.
The payer plan group recorded on the charge transaction.
If the charge transaction has been voided, the ID of the voiding transaction.
If the charge transaction has been replaced (for example after a recode), the ID of the charge transaction that replaced it.
Organization
Candid organization ID.
Organization or company name.
Organization’s External ID - the ID that the organization uses to map to their own upstream system
Details
PAYER when counter_party_id is populated; otherwise NULL.
How the entry was priced. Currently always FEE_FOR_SERVICE. More pricing methods may be added in the future.
Expected amount in cents. Active entries are positive (or 0); void entries have the opposite sign of the entry they reverse. NULL means no expected amount could be determined. Deduplicate expected_revenue_id before aggregating.
When the entry was recorded in Candid (UTC). A void entry and the new entry that replaces it share the same timestamp.
Included for consistency with export_transaction. Currently always on the same date as transaction_timestamp.
presubmission for charges created before the claim was submitted; billed for charges created at submission (and for self-pay).
Whether the service line was deleted in the Candid system. Deleted service lines remain in this table as tombstones so incremental consumers can remove them.
Timestamps
Time when the entry was last updated. Advances when the entry is voided, when a charge transaction is linked, and when the linked charge transaction is voided or replaced.
When the entry was created.
When the service line was deleted 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.