clearinghouse_transaction has one row per lifecycle event - submitted, acknowledged, accepted or rejected, ERA received, crossed over to another payer, and so on. clearinghouse_message has one row per message the clearinghouse returned within one of those events, so a single transaction usually has several messages.
Join them on clearinghouse_message.clearinghouse_transaction_id = clearinghouse_transaction.id. clearinghouse_message also carries claim_id and practice_id copied from its transaction, so you can filter messages by claim or practice without joining first.
These tables’ columns and relationships are shown in the Hosted Database schema.
On
clearinghouse_transaction, type is the normalized lifecycle category (for example SUBMITTED, ACKNOWLEDGED, accepted, DENIED, REJECTED, ERA RECEIVED, CROSSOVER). status is the detailed, human-readable message for the same event and often includes the payer name (for example, “Submitted to clearinghouse for <payer>”). claimmd_id is the identifier assigned by the clearinghouse, not a Hosted Database key.On
clearinghouse_message, message_subject and message change meaning together. For a validation or scrubbing message, message_subject names the claim form field at fault (diag_1, ins_number, prov_npi) and message is readable text such as “Diagnosis code invalid [R108].” For a message derived from an ERA, message_subject is instead a label (Total Paid, ICN, Paid Date) and message holds that label’s value ($83.00, 01-27-2026).message_code tells the two apart. It is populated on messages the clearinghouse returned against a claim submission, and null on ERA-derived rows and on Elation Billing’s own scrubbing status lines. Filter with message_code is not null for submission responses and is null for the rest.Only messages the clearinghouse linked back to a transaction are included. The rest are mostly acknowledgments, plus payer denial reason lines that arrive with a remittance rather than a claim submission, and they cannot be attributed to a practice. For the acknowledgment lifecycle use
clearinghouse_transaction.type. For denial reason analysis use era_matched_adjustment, which carries the payer’s adjustment and remark codes with the amounts and the claim they apply to.- A claim’s progress through the clearinghouse over time
- Submission, acceptance, rejection, and denial counts
- Transactions for a given claim or patient
- Tracing a transaction back to the clearinghouse via claimmd_id
- Which claim form fields most often fail validation before submission
- ERA payment detail returned against a claim
- How many messages the clearinghouse returned per transaction