Claims

claims is a summary table of the current status and details about the billing cycle of a particular claim, from charge creation through zero balance. The data it summarizes is the claim_transactions table (details below), which contain transaction-level detail about sequence of events in the billing cycle.

The unique identifier for a claim is the claim_number and external_system FK. There are additional mandatory fields:

• patient_mrn - FK to the unique identifier (within a given domain) of the patient receiving the service
• site - FK to facility where the service was performed.

Several optional fields provide additional data:

• visit - FK to the visits table to associate the claim with additional clinical data
• site_class - FK to site_classes to determine CMS place of service, patient type, etc.
• original_charge - the initial value of the charge, in units of the smallest divisible currency (e.g. cents if locality = US)

There are several fields that change over time:

• current_claim_status - FK to external_system_statuses that reflects the most recent claim_transaction.claim_status
• current_balance - the current balance of the claim, should eventually reach zero (via payments, adjustments, etc.), in units of the smallest divisible currency (e.g. cents if locality = US)
• current_carrier_id - FK to the associated insurance_carrier from the most recent claim_transaction activity
• first_claim_transaction - FK to the first claim_transaction, ordered by claim_sequence
• current_claim_transaction - FK to the most recent claim_transaction, ordered by claim_sequence

These timestamps will be set upon the first occurrence of the events which they represent:

• charge_post - the timestamp from the claim_transaction.transaction_post of the first claim_transaction.claim_status with a universal_event_type of charged
• claim_create - the timestamp for the claim creation date
• zero_balance - the timestamp for the first time when the current_balance reaches zero

Claim Transactions

claim_transactions are the detailed, event-level records that comprise a claim (associated via the claim_id FK). Each row is analogous to a line item and there is a claim_sequence integer which always increases with each new event (e.g. 1, 2, 3, etc.) The unique identifier for a row is the claim_id and claim_sequence.

Other mandatory fields for every transaction:

• cms_hcpcs_code the service being billed
• service_performed - the timestamp (frequently truncated to only a date) the service was performed, e.g. rad_exam_times.end_exam for a radiology exam)
• billing_employee - FK to employees for the provider who is billing for the service
• transaction_post - the timestamp when this claim_transaction occurred
• claim_status - FK to external_system_statuses
• claim_transaction_type - FK to claim_transaction_types to describe the type of transaction (details below)
• transaction_amount - the amount of money transacted, in units of the smallest divisible currency (e.g. cents if locality = US), positive for payments, negative for charges
• balance - the current balance after the transaction_amount has been processed against the previous balance, in units of the smallest divisible currency (e.g. cents if locality = US) - this field is calculated, only the transaction_amount is sent in the message

Optional fields:

• ordering_employee - FK to employees for the provider who ordered the service
• performing_employee - FK to employees for the provider who performed the service (not always the same provider who billed, but frequently the same)
• posting_employee - FK to employees for the billing team member who entered/posted the transaction
• unit_count - count of units of HCPCS code being billed, purely used as a decorator / tracker (the transaction_amount divided by the unit_count could be used for per-unit pricing)
• carrier_id - FK to the insurance_carrier involved in the transaction
• insurance_policy_id - FK to the insurance_policy involved in the transaction
• self_pay - boolean to indicate if a transaction is a self-pay vs a carrier (can be NULL if information not present in messaging)
• auth_number - authorization number from the insurance claim
• comments - text entered by the posting_employee about the transaction
• claim_denial_reason - FK to the reasons table, used to explain why a claim was denied by the carrier

Other Child Tables

There are two child tables that provide additional information about a claim_transaction:

• claim_transaction_cms_modifiers this vertical table associates zero or more cms_modifiers with a claim_transaction
• claim_transaction_diagnoses: this vertical table associates zero or more ICD codes with a claim_transaction

Claim Transaction Types

claim_transaction_types describe the transaction_type of a claim_transaction (e.g. charge or adjustment) and are currently a display helper only (no logic associated with the types).

Claim Transaction Facts

The claim_transaction_facts table is a second normal form representation of common metrics and dimensions (filters) for claim transaction data. Large, aggregate queries against claim_transactions directly in conjunction with information from joined tables can sometimes perform poorly. claim_transaction_facts is built specifically to be performant for reporting/analytical queries.

All claim_transactions will have a corresponding row in claim_transaction_facts.

The table is kept up to date in real-time by the data manager. This means whenever a claim transaction is updated with new information, the corresponding claim_transaction_fact row will also be updated with the same information. In the case of an update to a parent table of claim_transactions (e.g. external_system_statuses gets a different universal_event_type_id mapped) then all the matching claim_transaction_facts with that claim_status_id will have the universal_event_type_id updated appropriately. The caveat is that this will take some small period time (usually minutes) for the table to catch up to the change because it could be millions of rows that need to be updated.

For the following two timestamps:

• service_performed - from claim_transactions (will always be present)
• transaction_post - from claim_transactions (will always be present)

the component values values get extracted to integers for use in filtering/searching, with a column name in the format of timestamp_component, so for the hour component of the service_performed timestamp, the field name would be service_performed_hour. The full list of components is:

• timestamp_year - year extracted from timestamp (not the ISO 8601 year, so value will always match timestamp value)
• timestamp_month - month extracted from timestamp
• timestamp_day - day extracted from timestamp
• timestamp_hour - hour extracted from timestamp
• timestamp_minute - minute extracted from timestamp
• timestamp_epoch - Unix time (seconds since midnight UTC January 1st, 1970) transformation of timestamp
• timestamp_dow - ISO 8601 day of the week (Monday=1, Sunday=7) extracted from timestamp
• timestamp_woy - ISO 8601 week of the year (end days of one year can be week 1 of next year, early days of new year can be week 52 or 53 of previous year) extracted from timestamp

Details for the other field in claim_transaction_facts:

• claim_transaction_id - associated claim_transaction foreign key
• claim_id - associated claim_transaction foreign key
• site_id - sites foreign key
• site_class_id - site_classes foreign key
• cms_hcpcs_code_id - cms_hcpcs_codes foreign key
• insurance_policy_id - insurance_policies foreign key
• insurance_carrier_plan_id - insurance_carrier_plan foreign key from insurance_policies
• insurance_carrier_plan_grouping_id - insurance_carrier_plan_grouping foreign key from insurance_carrier_plans
• insurance_carrier_id - insurance_carriers foreign key
• ordering_employee_id - employees foreign key
• performing_employee_id - employees foreign key
• billing_employee_id - employees foreign key
• posting_employee_id - employees foreign key
• claim_status_id - external_system_statuses foreign key from
• universal_event_type_id - universal_event_types foreign key from external_system_statuses mapping on claim_transactions.claim_status_id
• claim_transaction_type_id - claim_transaction_types foreign key
• claim_denial_reason_id - reasons FK from claim_transactions.claim_denials_id
• self_pay - the self_pay column from claim_transactions
• unit_count - the unit_count column from claim_transactions
• transaction_amount - the transaction_amount column from claim_transactions