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:

Several optional fields provide additional data:

There are several fields that change over time:

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

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:

Optional fields:

Other Child Tables

There are two child tables that provide additional information about 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:

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:

Details for the other field in claim_transaction_facts: