Billing

Medical Billing Guide

Complete guide to medical billing systems, claims processing workflows, revenue cycle management, and payment optimization for healthcare organizations.

Medical Billing Overview

Medical billing is the process of submitting and following up on claims with health insurance companies to receive payment for services rendered. It bridges clinical care and financial operations, ensuring healthcare providers are properly compensated for their services.

The revenue cycle encompasses patient registration, charge capture, claims submission, payment posting, denial management, and collections. Efficient billing processes are critical to healthcare organization financial health.

Revenue Cycle Workflow

Patient Registration

Demographics, insurance verification, prior authorization

Charge Capture

Service documentation, CPT/ICD-10 coding, charge entry

Claims Generation

CMS-1500/UB-04 creation, scrubbing, validation

Claims Submission

EDI 837 transmission to payers, acknowledgment tracking

Adjudication

Payer processes claim, determines payment amount

Payment Posting

ERA/EOB processing, payment allocation, adjustment entry

Denial Management

Denial analysis, appeals, resubmission

Patient Collections

Patient statements, payment plans, bad debt

Medical Billing Database Schema

-- Claims Table
CREATE TABLE claim (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Identifiers
  clm_number        VARCHAR(50) UNIQUE NOT NULL,
  patient_id          UUID NOT NULL,
  encounter_id        UUID,
  
  -- Payer Info
  payer_id            UUID NOT NULL,
  subscriber_id       VARCHAR(100),
  group_number        VARCHAR(100),
  
  -- Claim Type
  clm_type          VARCHAR(50),                     -- professional, institutional
  form_type           VARCHAR(20),                     -- CMS-1500, UB-04
  
  -- Dates
  service_dt_from   DATE NOT NULL,
  service_dt_to     DATE NOT NULL,
  submitted_dt      DATE,
  
  -- Amounts
  total_charges       DECIMAL(10,2) NOT NULL,
  allowed_amt      DECIMAL(10,2),
  paid_amt         DECIMAL(10,2),
  patient_responsibility DECIMAL(10,2),
  adjustment_amt   DECIMAL(10,2),
  
  -- Status
  status              VARCHAR(50),                     -- draft, submitted, paid, denied
  billing_provider    VARCHAR(10),                     -- NPI
  
  -- Metadata
  created_at          TIMESTAMP DEFAULT NOW(),
  updated_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_claim_patient ON claim(patient_id);
CREATE INDEX idx_claim_payer ON claim(payer_id);
CREATE INDEX idx_claim_status ON claim(status);
CREATE INDEX idx_claim_submitted ON claim(submitted_dt);

-- Claim Line Items
CREATE TABLE claim_line (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clm_id            UUID NOT NULL REFERENCES claim(id),
  
  line_number         INTEGER NOT NULL,
  
  -- Service Details
  service_dt        DATE NOT NULL,
  cpt_code            VARCHAR(10) NOT NULL,
  cpt_modifier1       VARCHAR(2),
  cpt_modifier2       VARCHAR(2),
  
  -- Diagnosis Pointers
  icd10_code1         VARCHAR(10),
  icd10_code2         VARCHAR(10),
  icd10_code3         VARCHAR(10),
  icd10_code4         VARCHAR(10),
  
  -- Units & Amounts
  units               INTEGER DEFAULT 1,
  charge_amt       DECIMAL(10,2) NOT NULL,
  allowed_amt      DECIMAL(10,2),
  paid_amt         DECIMAL(10,2),
  
  -- Provider
  rendering_provider  VARCHAR(10),                     -- NPI
  
  -- Status
  line_status         VARCHAR(50),
  denial_code         VARCHAR(10),
  denial_reason       TEXT,
  
  created_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_line_claim ON claim_line(clm_id);
CREATE INDEX idx_line_cpt ON claim_line(cpt_code);

-- Payments
CREATE TABLE payment (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Payment Source
  payer_id            UUID,
  patient_id          UUID,
  payment_type        VARCHAR(50),                     -- insurance, patient, adjustment
  
  -- Payment Details
  payment_number      VARCHAR(100),
  payment_method      VARCHAR(50),                     -- check, EFT, credit card
  payment_amt      DECIMAL(10,2) NOT NULL,
  payment_dt        DATE NOT NULL,
  
  -- Deposit
  deposit_dt        DATE,
  batch_id            VARCHAR(100),
  
  -- Metadata
  created_at          TIMESTAMP DEFAULT NOW()
);

-- Payment Allocations
CREATE TABLE payment_allocation (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  payment_id          UUID NOT NULL REFERENCES payment(id),
  clm_id            UUID NOT NULL REFERENCES claim(id),
  clm_line_id       UUID REFERENCES claim_line(id),
  
  allocated_amt    DECIMAL(10,2) NOT NULL,
  
  created_at          TIMESTAMP DEFAULT NOW()
);

-- Denials
CREATE TABLE denial (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clm_id            UUID NOT NULL REFERENCES claim(id),
  clm_line_id       UUID REFERENCES claim_line(id),
  
  -- Denial Details
  denial_code         VARCHAR(10) NOT NULL,
  denial_reason       TEXT,
  denial_category     VARCHAR(100),                    -- clinical, technical, eligibility
  
  -- Resolution
  appeal_status       VARCHAR(50),                     -- pending, submitted, won, lost
  appeal_dt         DATE,
  resolution_dt     DATE,
  resolution_notes    TEXT,
  
  -- Financial Impact
  denied_amt       DECIMAL(10,2),
  recovered_amt    DECIMAL(10,2),
  
  created_at          TIMESTAMP DEFAULT NOW(),
  updated_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_denial_claim ON denial(clm_id);
CREATE INDEX idx_denial_category ON denial(denial_category);

Common Denial Codes & Reasons

Top Denial Reasons (70% of all denials)

• Missing or invalid patient information
• Authorization/referral not obtained
• Service not covered by payer
• Duplicate claim submission
• Timely filing deadline exceeded

Key Performance Indicators

Days in A/R

< 40 days

Average time to collect payment

Clean Claim Rate

> 95%

Claims paid on first submission

Denial Rate

< 5%

Percentage of claims denied

Collection Rate

> 95%

Net collected / net charges

Cost to Collect

< 3%

RCM cost / collections

Point of Service Collections

> 50%

Patient payments at visit

SQL Query Examples

Calculate Days in A/R by Payer

SELECT 
  p.payer_nm,
  COUNT(c.id) as total_claims,
  AVG(CURRENT_DATE - c.service_dt_from) as avg_days_ar,
  SUM(c.total_charges - COALESCE(c.paid_amt, 0)) as outstanding_balance
FROM claim c
JOIN payer p ON c.payer_id = p.id
WHERE c.status IN ('submitted', 'partially_paid')
GROUP BY p.id, p.payer_nm
ORDER BY avg_days_ar DESC;

Top Denial Reasons

SELECT 
  d.denial_code,
  d.denial_reason,
  COUNT(*) as denial_count,
  SUM(d.denied_amt) as total_denied,
  AVG(COALESCE(d.recovered_amt, 0)) as avg_recovered
FROM denial d
WHERE d.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY d.denial_code, d.denial_reason
ORDER BY denial_count DESC
LIMIT 10;

Related Healthcare Guides

EHR Systems →ICD-10 Codes →CPT Codes →Healthcare Glossary →

Browse Related Definitions

Billing Provider NPI Revenue Cycle Management

Browse all healthcare data guides

All guides