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 servicesite
- FK to facility where the service was performed.
Several optional fields provide additional data:
visit
- FK to thevisits
table to associate theclaim
with additional clinical datasite_class
- FK tosite_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 toexternal_system_statuses
that reflects the most recentclaim_transaction.claim_status
current_balance
- the current balance of theclaim
, 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 associatedinsurance_carrier
from the most recentclaim_transaction
activityfirst_claim_transaction
- FK to the firstclaim_transaction
, ordered byclaim_sequence
current_claim_transaction
- FK to the most recentclaim_transaction
, ordered byclaim_sequence
These timestamps will be set upon the first occurrence of the events which they represent:
charge_post
- the timestamp from theclaim_transaction.transaction_post
of the firstclaim_transaction.claim_status
with auniversal_event_type
ofcharged
claim_create
- the timestamp for the claim creation datezero_balance
- the timestamp for the first time when thecurrent_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 billedservice_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 toemployees
for the provider who is billing for the servicetransaction_post
- the timestamp when thisclaim_transaction
occurredclaim_status
- FK toexternal_system_statuses
claim_transaction_type
- FK toclaim_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 chargesbalance
- the current balance after thetransaction_amount
has been processed against the previousbalance
, in units of the smallest divisible currency (e.g. cents if locality = US) - this field is calculated, only thetransaction_amount
is sent in the message
Optional fields:
ordering_employee
- FK toemployees
for the provider who ordered the serviceperforming_employee
- FK toemployees
for the provider who performed the service (not always the same provider who billed, but frequently the same)posting_employee
- FK toemployees
for the billing team member who entered/posted the transactionunit_count
- count of units of HCPCS code being billed, purely used as a decorator / tracker (thetransaction_amount
divided by theunit_count
could be used for per-unit pricing)carrier_id
- FK to theinsurance_carrier
involved in the transactioninsurance_policy_id
- FK to theinsurance_policy
involved in the transactionself_pay
- boolean to indicate if a transaction is a self-pay vs a carrier (can beNULL
if information not present in messaging)auth_number
- authorization number from the insurance claimcomments
- text entered by theposting_employee
about the transactionclaim_denial_reason
- FK to thereasons
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 morecms_modifiers
with aclaim_transaction
claim_transaction_diagnoses
: this vertical table associates zero or more ICD codes with aclaim_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
- fromclaim_transactions
(will always be present)transaction_post
- fromclaim_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
- associatedclaim_transaction
foreign keyclaim_id
- associatedclaim_transaction
foreign keysite_id
-sites
foreign keysite_class_id
-site_classes
foreign keycms_hcpcs_code_id
-cms_hcpcs_codes
foreign keyinsurance_policy_id
-insurance_policies
foreign keyinsurance_carrier_plan_id
-insurance_carrier_plan
foreign key frominsurance_policies
insurance_carrier_plan_grouping_id
-insurance_carrier_plan_grouping
foreign key frominsurance_carrier_plans
insurance_carrier_id
-insurance_carriers
foreign keyordering_employee_id
-employees
foreign keyperforming_employee_id
-employees
foreign keybilling_employee_id
-employees
foreign keyposting_employee_id
-employees
foreign keyclaim_status_id
-external_system_statuses
foreign key fromuniversal_event_type_id
-universal_event_types
foreign key fromexternal_system_statuses
mapping onclaim_transactions.claim_status_id
claim_transaction_type_id
-claim_transaction_types
foreign keyclaim_denial_reason_id
-reasons
FK fromclaim_transactions.claim_denials_id
self_pay
- theself_pay
column fromclaim_transactions
unit_count
- theunit_count
column fromclaim_transactions
transaction_amount
- thetransaction_amount
column fromclaim_transactions