Tables

📄️ address_canonical

Canonical address information retrieved from smarty.com API. See address_seen for field details. (https://www.smarty.com/products/us-address-verification)

📄️ address_seen

Stores every address we've ever encountered from any source in the database.

📄️ api_metrics

Stores metrics for Application Programming Interface (API) calls.

📄️ claim

Information about a healthcare or prescription drug claim

📄️ claim_adjustment_reason_code_lookup

📄️ claim_anesthesia_procedure

The anesthesia related surgical procedure (Loop 2300 HI). This could be the principal (HI01-01='BP') or secondary (HI01-01='BO') Healthcare Common Procedure Coding System (HCPCS) code. See the 837i x12 spec for more information.

📄️ claim_coordination_of_benefits

Information about other subscribers that may be responsible for payment during coordination of benefits. Some repricers also use the SBR/CAS segments to indicate the price for a primary payer, even if that is not technically spec compliant

📄️ claim_coordination_of_benefits_adjustment

Lists the adjustments that have been performed on a claim during the process of adjudication. Only used for Coordination of Benefits according to the EDI spec, but may be used for repricing with the primary payer (from 2320 loop). (https://www.ehealthinsurance.com/resources/individual-and-family/coordination-of-benefits)

📄️ claim_coverage

Provides information about member coverage for a specific plan during service dates

📄️ claim_diagnosis

Provides information about the ICD diagnosis codes associated with a claim. https://www.cms.gov/medicare/coding-billing/icd-10-codes

📄️ claim_drg

Provides information about the Diagnosis-Related Group (DRG) code associated with an inpatient claim. https://www.cms.gov/medicare/payment/prospective-payment-systems/acute-inpatient-pps/ms-drg-classifications-and-software

📄️ claim_member

Member information for a claim. Includes subscriber and patient (when provided) information

📄️ claim_member_match_hash

Text hash used for finding matches between claims (claim_member) and eligibility (member_seen)

📄️ claim_member_to_member_seen_match

Describes matches between claims (claim_member) and eligibility (member_seen).

📄️ claim_note

Notes for a claim

📄️ claim_occurrence

Provides information about the NUBC occurrence codes associated with a claim

📄️ claim_occurrence_span

Provides information about the NUBC occurrence span codes associated with a claim

📄️ claim_paperwork

Describes a paper attachment following the claim (https://www.cgsmedicare.com/jb/claims/pwk_segment.html)

📄️ claim_pay_to_plan

Information about the subrogation demand payer

📄️ claim_procedure

Provides information about the ICD procedure codes associated with a claim. (https://www.cms.gov/medicare/coding-billing/icd-10-codes)

📄️ claim_provider

Connects a claim to one or more providers

📄️ claim_repricing

Results of a pricing request

📄️ claim_repricing_code_lookup

Claim-level repricing codes

📄️ claim_rx_clinical

Information about clinical diagnoses associated with an RX claim

📄️ claim_rx_clinical_measurement

Information about measurements associated with a diagnosis on an RX claim

📄️ claim_rx_workers_comp_employer

Employer information related to workers' compensation for RX claims

📄️ claim_rx_workers_comp_pay_to

Pay-to information related to workers' compensation for RX claims

📄️ claim_status

Results of a pricing request

📄️ claim_status_lookup

Available statuses for a claim

📄️ claim_tooth_status

Provides information about the status of individual teeth

📄️ claim_value

Provides information about the NUBC value codes associated with a claim. (https://med.noridianmedicare.com/web/jea/topics/claim-submission/value-codes)

📄️ coordination_of_benefits_subscriber

Contains unique subscriber details for coordination of benefits

📄️ coverage

A unique set of data associated with a member coverage.

📄️ coverage_coordination_of_benefits

Coordination of Benefits information for a member

📄️ coverage_group_or_plan

Pulls together all the information available to map a set of members into one or more groups and one or more plans

📄️ cpt_concept_description

CPT concept descriptions. A CPT concept is the term used by the American Medical Association (AMA) to describe all the concepts surrounding CPT codes (e.g. documentation, hierarchy, codes, etc.). In our case, we're interested in the Descriptor or text definition of each CPT concept.

📄️ cpt_hcup_group

Links CPT code to the appropriate HCUP CCS Group. See https://hcup-us.ahrq.gov/toolssoftware/ccs_svcsproc/ccssvcproc.jsp for more information.

📄️ cpt_hierarchy

Hierarchy for CPT codes.

📄️ cpt_hierarchy_crosswalk

Crosswalk between cpt and cpt hierarchy..

📄️ cpt_hierarchy_lookup

Lookup for CPT code hierarchy.

📄️ eligibility

A unique set of data associated with a member eligibility. (https://www.stedi.com/edi/x12/transaction-set/834)

📄️ eligibility_person_or_org

Contains unique people or organizations that are in some way connected to a member

📄️ email

A unique list of every email address we've seen in claims and eligibility

📄️ extractor_column

Defines a column in an extractor output

📄️ extractor_column_data_type

Data types for extractor output columns

📄️ extractor_column_group

Defines a group of extractor columns

📄️ extractor_column_group_column

Associates extractor columns with column groups

📄️ extractor_input

Stores extractor input configuration information

📄️ extractor_input_csv_column

Maps input columns to output columns

📄️ extractor_input_filename_regex

Input filename matching information

📄️ extractor_input_user_value

Maps user-selected values to output columns

📄️ extractor_output

Stores extractor output tables

📄️ extractor_output_check_required

Enforces required data for an extractor output. A row is invalid if every column in any check-required group is empty.

📄️ extractor_output_reference

Associates names with outputs so the outputs can be referenced

📄️ extractor_output_skip_if_duplicate

Columns used to detect duplicate rows in extractor outputs. If another row has the same values in all columns in a skip-if-duplicate condition, the row is skipped.

📄️ extractor_output_skip_if_empty

Stores column groups that define when an output row should be skipped. A row is skipped if any skip-if-empty column group has all of its columns empty.

📄️ extractor_wizard

Defines extractor wizards

📄️ extractor_wizard_bucket_option

Stores bucket options for wizard bucket requests

📄️ extractor_wizard_bucket_request

Stores wizard bucket requests

📄️ extractor_wizard_input_form

Stores wizard input forms

📄️ extractor_wizard_input_form_input

Stores inputs for wizard input forms

📄️ extractor_wizard_map_output

Stores wizard map outputs, which specify how input columns are transformed into output rows

📄️ extractor_wizard_map_output_check_required_override

Stores check-required overrides for wizard map outputs

📄️ extractor_wizard_map_output_dependency

Stores dependencies of a wizard map output

📄️ extractor_wizard_map_output_group

Defines a wizard map output group

📄️ extractor_wizard_map_output_group_map_output

Associates wizard map outputs with map output groups

📄️ extractor_wizard_map_output_skip_if_empty_override

Stores skip-if-empty overrides for wizard map outputs

📄️ extractor_wizard_map_output_value

Wizard map output value mappings

📄️ extractor_wizard_question

Stores wizard questions

📄️ extractor_wizard_question_answer

Stores answers to wizard questions

📄️ extractor_wizard_step

Stores steps in extractor wizards. Each step is either an input form, bucket request, question, map output, or map output group.

📄️ fax

A unique list of fax numbers we've seen in claims and eligibility

📄️ group

Information about a group of employees for a plan sponsor of a health plan (e.g. Intel California). For a small employer, this will usually match the company name. Note that this is our group definition, and may not match with the group definition of a TPA or other trading partner.

📄️ group_match

Information used to match members received from a TPA, plan sponsor, or other trading partner to our definition of a group (in the group table).

📄️ hcpcs_hcup_group

Links HCPCS code to the appropriate HCUP CCS Group. See https://hcup-us.ahrq.gov/toolssoftware/ccs_svcsproc/ccssvcproc.jsp for more information.

📄️ hcup_diagnosis_chronic_condition_indicator_lookup

Identifies diagnosis codes that indicate a chronic condition.

📄️ hcup_diagnosis_crosswalk

HCUP diagnosis crosswalk. See icd_diagnosis_hcup_group for the linkage between icd_diagnosis_id and hcup_diagnosis_group_id. (https://hcup-us.ahrq.gov/toolssoftware/ccsr/dxccsr.jsp)

📄️ hcup_diagnosis_group_lookup

Information about the HCUP CCS groups for diagnosis codes. See https://hcup-us.ahrq.gov/toolssoftware/ccs10/ccs10.jsp for more information.

📄️ hcup_hcpcs_crosswalk

HCUP HCPCS crosswalk. See hcpcs_hcup_group for the linkage between product_service_id and hcup_hcpcs_group_id. (https://hcup-us.ahrq.gov/toolssoftware/ccs_svcsproc/ccssvcproc.jsp)

📄️ hcup_hcpcs_group_lookup

Information about the HCUP CCS groups for HCPCS codes. See https://hcup-us.ahrq.gov/toolssoftware/ccs_svcsproc/ccssvcproc.jsp for more information.

📄️ hcup_procedure_crosswalk

HCUP procedure crosswalk. See icd_procedure_hcup_group for the linkage between icd_procedure_id and hcup_procedure_group_id. https://hcup-us.ahrq.gov/toolssoftware/ccsr/prccsr.jsp

📄️ hcup_procedure_group_lookup

Information about the HCUP CCS groups for HCPCS codes. See https://hcup-us.ahrq.gov/toolssoftware/procedureicd10/procedure_icd10.jsp for more information.

📄️ icd_diagnosis_hcup_group

Links ICD Diagnosis code to the appropriate HCUP CCS Group. See https://hcup-us.ahrq.gov/toolssoftware/ccs10/ccs10.jsp for more information.

📄️ icd_diagnosis_hierarchy

ICD diagnosis code hierarchy.

📄️ icd_diagnosis_hierarchy1_lookup

ICD diagnosis code hierarchy level 1 lookup (https://www.cms.gov/medicare/coding-billing/icd-10-codes).

📄️ icd_diagnosis_hierarchy2_lookup

ICD diagnosis code hierarchy level 2 lookup (https://www.cms.gov/medicare/coding-billing/icd-10-codes).

📄️ icd_diagnosis_hierarchy3_lookup

ICD diagnosis code hierarchy level 3 lookup (https://www.cms.gov/medicare/coding-billing/icd-10-codes).

📄️ icd_diagnosis_hierarchy4_lookup

ICD diagnosis code hierarchy level 4 lookup (https://www.cms.gov/medicare/coding-billing/icd-10-codes).

📄️ icd_diagnosis_hierarchy_crosswalk

ICD diagnosis code hierarchy crosswalk.

📄️ icd_diagnosis_horizon_blue_cross_case_management_lookup

Complex case management trigger diagnosis codes (Horizon Blue Cross Blue Shield) See: https://www.horizonblue.com/sites/default/files/ICD_10_charts-FINAL-July.pdf.

📄️ icd_diagnosis_lookup

List of ICD Diagnosis codes from CMS. (https://www.cms.gov/medicare/coding-billing/icd-10-codes)

📄️ icd_diagnosis_stop_loss_trigger

List of ICD Diagnosis stop loss trigger codes

📄️ icd_procedure_hcup_group

Links ICD Procedure code to the appropriate HCUP CCS Group. See https://hcup-us.ahrq.gov/toolssoftware/procedureicd10/procedure_icd10.jsp for more information.

📄️ icd_procedure_hierarchy

Crosswalk between ICD procedure code and the hierarchy lookups.

📄️ icd_procedure_hierarchy1_lookup

ICD procedure code hierarchy level 1 lookup.

📄️ icd_procedure_hierarchy2_lookup

ICD procedure code hierarchy level 2 lookup.

📄️ icd_procedure_hierarchy3_lookup

ICD procedure code hierarchy level 3 lookup.

📄️ icd_procedure_hierarchy4_lookup

ICD procedure code hierarchy level 4 lookup.

📄️ icd_procedure_hierarchy5_lookup

ICD procedure code hierarchy level 5 lookup.

📄️ icd_procedure_hierarchy_crosswalk

ICD procedure code hierarchy crosswalk.

📄️ icd_procedure_hierarchy_title_lookup

Stores the titles used to describe each of the hierarchy levels, which vary depending on the section code (hierarchy level 1). For example in 'Medical and Surgical procedures' the letters of the procedure describe the 'section', 'body system', 'operation', etc. but for 'Physical Rehabilitation and Diagnostic Audiology', the letters of the procedure describe the 'section', 'section qualifier', 'type', etc. This table makes it possible to correctly build a ICD-procedure drill-down UI.

📄️ icd_procedure_lookup

List of ICD Procedure codes from CMS. See: https://www.cms.gov/medicare/coding-billing/icd-10-codes

📄️ insurer_payer

Identifies the insurer / network / carrier or other entity responsible for paying the claim on behalf of the employer

📄️ job

The list of jobs (e.g. extract, load, etc.)

📄️ line_item

Information about a line item for a health, dental, prescription drug, etc. claim

📄️ line_item_compound_ingredient

Lists ingredients used in a compound drug mixture.

📄️ line_item_coordination_of_benefits

Describes the adjudication performed for a line item on behalf of the payer. According to the EDI specification, this is only used for Coordination of Benefits, but some reprices also use it for claims repricing

📄️ line_item_coordination_of_benefits_adjustment

A log of all adjustments made to a line item by a Coordination of Benefits payer. Sometimes used by repricing organizations to show pricing adjustments

📄️ line_item_form

A form that has been filled out to provide documentation

📄️ line_item_form_response

Responses to a codified questionnaire

📄️ line_item_measurement

Measurement details for a line item

📄️ line_item_note

Notes for a line item

📄️ line_item_paperwork

Describes a paper attachment following the line_item

📄️ line_item_provider

Connects a line item to a provider

📄️ line_item_repricing

Results of a pricing request for a single service line

📄️ line_item_tooth

Provides information about the status of individual teeth

📄️ line_repricing_code_lookup

Line-level Medicare repricing code

📄️ login

Information about application logins

📄️ matching_session

An append-only table of sessions

📄️ medicare_source_lookup

Medicare amount sources

📄️ member_canonical

The canonical member table. Other tables have foreign keys to this table.

📄️ member_canonical_archive

An archive of old member_canonical records. Rows in this table are created when a user merges two member_canonical, effectively deleting one.

📄️ member_canonical_lock

A lock corresponding to a human currently matching using a member_canonical record. This does not correspond to any Postgres lock as no continuous connection is held during the duration of the matching but updates are still not allowed.

📄️ member_canonical_reconfiguration

📄️ member_coverage

Keeps track of each load of an enrolled member's coverage

📄️ member_coverage_coordination_of_benefits

Coordination of Benefits information for a specific enrolled member

📄️ member_coverage_provider

Member provider selections for primary care, capitated physicians, pharmacy, etc.

📄️ member_eligibility

Keeps track of member eligibility information

📄️ member_eligibility_person_or_org

Links a member_eligibility to employer, custodial parent, school, etc. person_or_org records

📄️ member_eligibility_race_or_ethnicity

Identifies one or more race or ethnicity categories for a member.

📄️ member_history

📄️ member_seen

Contains unique member information from eligibility files

📄️ member_seen_lock

A lock corresponding to a human currently matching using a member_seen record. This does not correspond to any Postgres lock as no continuous connection is held during the duration of the matching but updates are still not allowed.

📄️ member_seen_match_hash

Text hash used for finding matches between claims (claim_member) and eligibility (member_seen)

📄️ member_seen_to_member_seen_match

Describes matches between rows in member_seen.

📄️ msdrg_lookup

Contains Medicare Severity Diagnosis-Related Group (MS-DRG) information. (See: https://www.cms.gov/medicare/payment/prospective-payment-systems/acute-inpatient-pps/ms-drg-classifications-and-software)

📄️ name_given_alias

Provides the canonical, formal version of names where applicable (e.g. Bob, Bobby, Rob -> Robert). From https://www.behindthename.com/api/download.php

📄️ nanpa_area_code_lookup

A table for looking up information about North American Numbering Plan Administration (NANPA) area code.

📄️ ndc_wasteful_drugs

Johns Hopkins University wasteful drugs. https://www.pbgh.org/wp-content/uploads/2021/01/PBGH-Wasteful-Drugs-Guidebook-FINAL.pdf

📄️ npi_address

A provider's address.

📄️ npi_affiliate

Information about NPI's that are related to another NPI in some way.

📄️ npi_ccn

Association of National Provider Identifier (NPI) to CMS Certification Number (CCN).

📄️ npi_country_lookup

Global country codes used within NPI data. See https://www.cms.gov/Regulations-and-Guidance/Administrative-Simplification/NationalProvIdentStand/Downloads/Data_Dissemination_File-Code_Values.pdf#page=12 section 1.10 for all Country Codes.

📄️ npi_fax

Fax number(s) associated with a provider

📄️ npi_license

Provider licensing data.

📄️ npi_match_hash

Text hash used for finding possible providers for matching.

📄️ npi_name

Names associated with a provider

📄️ npi_other_identifier

Other identifiers of a provider.

📄️ npi_phone

Phone number(s) associated with a provider

📄️ npi_provider

The details about a provider. There will always be exactly one npi_provider row per provider but there may be many locations etc.

📄️ npi_state_lookup

Information about states and territories of the United States of America.

📄️ npi_tax_identifier

Tax IDs associated with an NPI in claims or contract data.

📄️ npi_taxonomy

A provider's taxonomy information.

📄️ nppes_authorized_official

Contact information for the authorized official for a provider.

📄️ nppes_endpoint

Stores information to securely transmit authenticated and encrypted medical information using a variety of Endpoint Types.

📄️ nubc_admission_source_lookup

NUBC admission source codes (AKA Point of origin). Used to describe where patient was admitted from. https://med.noridianmedicare.com/web/jea/topics/claim-submission/point-of-origin-codes

📄️ nubc_admission_type_lookup

The type/priority of admission for hospital stays. https://med.noridianmedicare.com/web/jea/topics/claim-submission/type-of-admission-or-visit-codes

📄️ nubc_bill_type_facility_lookup

NUBC bill type digit 1 (AKA type of facility) codes. https://med.noridianmedicare.com/web/jea/topics/claim-submission/bill-types

📄️ nubc_bill_type_frequency_lookup

NUBC bill type digit 3 (AKA frequency) codes https://med.noridianmedicare.com/web/jea/topics/claim-submission/bill-types

📄️ nubc_bill_type_lookup

NUBC bill type codes. This includes digit 1 and 2. https://med.noridianmedicare.com/web/jea/topics/claim-submission/bill-types

📄️ nubc_bill_type_to_place_of_service_crosswalk

Crosswalk was designed by Health Rosetta to map Bill Type (UB-04) to Place of Service (HCFA) codes as well as possible for uniform reporting across form types.

📄️ nubc_condition_lookup

NUBC condition codes used to explain special payment, patient, treatment, etc. conditions. https://med.noridianmedicare.com/web/jea/topics/claim-submission/condition-codes

📄️ nubc_occurrence_lookup

NUBC occurrence codes used to specify extra dates associated with a claim. (https://med.noridianmedicare.com/web/jea/topics/claim-submission/occurrence-codes) (https://med.noridianmedicare.com/web/jfa/topics/claim-submission/occurrence-span-codes)

📄️ nubc_patient_status_lookup

NUBC patient status codes (AKA discharge status) used to describe the patient's status at the end of the claim period. https://med.noridianmedicare.com/web/jea/topics/claim-submission/patient-discharge-status-codes

📄️ nubc_revenue_code_group_lookup

NUBC revenue code groups provide a higher-level grouping of revenue codes. Grouping brings together up to 10 similar types of revenue codes by making the final digit of the revenue code into an 'X' (e.g. '0022', '0023', and '0024' become '002X') https://med.noridianmedicare.com/web/jea/topics/claim-submission/revenue-codes

📄️ nubc_revenue_code_lookup

NUBC revenue codes describe the department performing a service in a facility. https://med.noridianmedicare.com/web/jea/topics/claim-submission/revenue-codes

📄️ nubc_value_lookup

Additional numeric values related to the claim. See https://med.noridianmedicare.com/web/jea/topics/claim-submission/value-codes

📄️ nucc_taxonomy_lookup

National Uniform Claim Committee (NUCC) Structured taxonomy information from https://nucc.org.

📄️ pecos_associate

Information about PECOS associates. For most enrollment scenarios, the PAC ID maps to a unique SSN for individual providers and an EIN for organizational providers, with a few rare exceptions such as sole proprietors. A provider may enroll multiple times, under different enrollment scenarios. One common example is when a provider enrolls in multiple states to bill to Medicare in multiple states. For each enrollment, an enrollment ID is issued and associated with the PAC ID.

📄️ pecos_associate_address

Address information for PECOS associates.

📄️ pecos_associate_name

Name information for PECOS associates.

📄️ pecos_enrollment

Information about providers and their Medicare enrollment

📄️ pecos_enrollment_address

📄️ pecos_enrollment_npi

Identifies the NPI number(s) associated with a provider enrollment application.

📄️ pecos_enrollment_organization

📄️ pecos_enrollment_ownership_or_control

Information about ownership or control of provider enrollments

📄️ pecos_enrollment_reassignment

📄️ pecos_enrollment_specialty

📄️ pecos_facility_enrollment

Information about facility enrollments that CMS exports (about half)

📄️ pecos_opt_out

Information about providers who have chosen to opt out of Medicare

📄️ pecos_ownership_or_control_type_lookup

PECOS owner role type code lookup.

📄️ pecos_specialty_type_lookup

Provider enrollment specialty code (AKA provider type) and description

📄️ phone

A unique list of every phone number we've seen

📄️ place_of_service_group_lookup

Place of service group lookup was developed by Health Rosetta to group related place of service codes together.

📄️ place_of_service_lookup

CMS Place of Service codes which explain where a service was performed. https://www.cms.gov/medicare/coding-billing/place-of-service-codes

📄️ plan

The list of plans in use. To see the plans available for implementation, see the plan_template table

📄️ plan_hierarchy

Tracks relationships between different components of a plan.

📄️ plan_match

Information needed to match eligibility/enrollment and claims data to a plan

📄️ plan_member_assignment

Links specific mebers to a plan when other enrollment filters are not sufficient

📄️ plan_role

Roles assigned to the plan sponsor, solution, or individual login for a plan.

📄️ plan_solution

Identifies the solution(s) that are associated with a plan.

📄️ plan_sponsor

Information about the plan sponsor (usually employer) of a health plan.

📄️ plan_sponsor_match

Information needed to match eligibility/enrollment and claims data to a plan sponsor

📄️ plan_template

Information about an employee benefit plan. This contains all the plan design so that it can be easily applied to a new group.

📄️ product_service_code_lookup

Unified lookup for product/service codes (CPT, HCPCS, ADA CDT, and user-defined).

📄️ product_service_code_lookup_raw

Raw data for product/service code lookup.

📄️ provider_seen

A unique list of every way we've ever seen a provider identified. Table includes values for UB04 FL 01, 02, 05, 56, 57, 76, 77, 78, and 79 / HCFA items 17, 24j, 25, 32, 33.

📄️ provider_seen_match

The list of providers that may be a match with the information in the provider_seen row

📄️ pub_sub

Messages received from Pub/Sub. Used to ensure that we only process each message once.

📄️ release

The list of releases that have been loaded into the database

📄️ role

Login roles available for assignment

📄️ snowflake_node

Keeps track of all the current snowflake nodes in use

📄️ solution

A list of individuals and organizations which provide services on behalf of plans

📄️ sql_metrics

Keeps track of SQL queries that are executed by the application

📄️ subscriber_or_patient

Contains unique subscribers and patients associated with a claim

📄️ taxonomy_specialty_crosswalk

This table maps all of the CMS Medicare Specialty Codes to the appropriate NUCC provider taxonomy code

📄️ trading_partner

Provides information about trading partners we send data to and/or receive data from.

📄️ trading_partner_extract_ncpdp

Information needed to process NCPDP prescription drug data.

📄️ trading_partner_extract_text

Configuration for extracting text (e.g. CSV or fixed width) files.

📄️ trading_partner_extract_x12

Configuration for extracting X12 EDI files.

📄️ trading_partner_load

Configuration for loading files from trading partners into the database

📄️ trading_partner_load_claim

Configuration on how to load and send claims data by trading partner.

📄️ trading_partner_load_enrollment

Information needed to process eligibility / enrollment data.

📄️ trading_partner_load_rx

Information to load Prescription Drug data.

📄️ trading_partner_sftp

SFTP connection information for a trading partner.

📄️ trading_partner_sftp_sync

Configuration for sending and receiving files from a remote SFTP connection.

📄️ trading_partner_submission

Filename, submit time, and other information about data submitted via EDI, CSV or some other format

📄️ uploaded_file

Information about uploaded files

📄️ zip_to_cbsa_crosswalk

A crosswalk of ZIP to CBSA from https://www.huduser.gov/portal/datasets/usps_crosswalk.html.