Pipeline Stages & Data Flow
Ingests submitted 837 claims and received 835 remittance files into an indexed operational store.
Matches claim lines to corresponding CARC/RARC adjustments, calculating true uncollected balances, copay/coinsurance offsets, and net open liability.
Calculates days elapsed since Date of Service (DOS) against payer-specific filing limits. Flags claims within the critical 30-day and 15-day risk windows.
Renders prioritized worklists for billing teams ordered by recoverable value multiplied by timely-filing urgency.
Data Model
The core data tables behind the aging and timely-filing calculation, expressed in SQLite syntax. These are the engine’s data model rather than a description of any customer deployment. A licensed instance runs against the practice’s own isolated data.
-- Core Claim Ledger
CREATE TABLE ar_claims (
claim_id TEXT PRIMARY KEY,
patient_control_num TEXT NOT NULL,
payer_id TEXT NOT NULL,
payer_name TEXT NOT NULL,
provider_npi TEXT NOT NULL,
date_of_service DATE NOT NULL,
billed_amount DECIMAL(10,2) NOT NULL,
paid_amount DECIMAL(10,2) DEFAULT 0.00,
adjustment_amount DECIMAL(10,2) DEFAULT 0.00,
current_balance DECIMAL(10,2) NOT NULL,
last_status_code TEXT,
days_in_ar INTEGER GENERATED ALWAYS AS (julianday('now') - julianday(date_of_service)) STORED,
tfl_days_limit INTEGER NOT NULL DEFAULT 90,
tfl_days_remaining INTEGER GENERATED ALWAYS AS (tfl_days_limit - (julianday('now') - julianday(date_of_service))) STORED,
status TEXT CHECK(status IN ('open', 'appealed', 'paid', 'written_off'))
);
-- Timely Filing Risk View
CREATE VIEW vw_timely_filing_worklist AS
SELECT
claim_id,
payer_name,
billed_amount,
current_balance,
days_in_ar,
tfl_days_remaining,
CASE
WHEN tfl_days_remaining <= 15 THEN 'CRITICAL'
WHEN tfl_days_remaining <= 30 THEN 'WARNING'
ELSE 'MONITOR'
END AS risk_tier
FROM ar_claims
WHERE status = 'open' AND current_balance > 0
ORDER BY tfl_days_remaining ASC, current_balance DESC;Timely-Filing Rules Engine
The engine evaluates each open claim against the filing deadline for its payer and flags the ones running out of time. Filing windows and appeal deadlines are not hardcoded here: they are read from the sourced, per-payer data on the Timely Filing Reference Guide, which is the single canonical home for those figures and cites each payer's own policy page.