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 claim_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 claim_type VARCHAR(50), -- professional, institutional form_type VARCHAR(20), -- CMS-1500, UB-04 -- Dates service_date_from DATE NOT NULL, service_date_to DATE NOT NULL, submitted_date DATE, -- Amounts total_charges DECIMAL(10,2) NOT NULL, allowed_amount DECIMAL(10,2), paid_amount DECIMAL(10,2), patient_responsibility DECIMAL(10,2), adjustment_amount 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_date); -- Claim Line Items CREATE TABLE claim_line ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), claim_id UUID NOT NULL REFERENCES claim(id), line_number INTEGER NOT NULL, -- Service Details service_date 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_amount DECIMAL(10,2) NOT NULL, allowed_amount DECIMAL(10,2), paid_amount 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(claim_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_amount DECIMAL(10,2) NOT NULL, payment_date DATE NOT NULL, -- Deposit deposit_date 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), claim_id UUID NOT NULL REFERENCES claim(id), claim_line_id UUID REFERENCES claim_line(id), allocated_amount DECIMAL(10,2) NOT NULL, created_at TIMESTAMP DEFAULT NOW() ); -- Denials CREATE TABLE denial ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), claim_id UUID NOT NULL REFERENCES claim(id), claim_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_date DATE, resolution_date DATE, resolution_notes TEXT, -- Financial Impact denied_amount DECIMAL(10,2), recovered_amount DECIMAL(10,2), created_at TIMESTAMP DEFAULT NOW(), updated_at TIMESTAMP DEFAULT NOW() ); CREATE INDEX idx_denial_claim ON denial(claim_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 daysAverage 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_name,
COUNT(c.id) as total_claims,
AVG(CURRENT_DATE - c.service_date_from) as avg_days_ar,
SUM(c.total_charges - COALESCE(c.paid_amount, 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_name
ORDER BY avg_days_ar DESC;Top Denial Reasons
SELECT d.denial_code, d.denial_reason, COUNT(*) as denial_count, SUM(d.denied_amount) as total_denied, AVG(COALESCE(d.recovered_amount, 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;