WorkInternal tool

Portal access blocker triage

Commure · 2026

A dashboard that reads noisy operational logs and works out which insurance payer portals are genuinely broken, then ranks them by the value of the claims stuck behind them. The interesting part is not the ranking but the detection, because the raw logs contain far more noise than signal and a triage tool that raises false alarms gets ignored within a week.

RetoolPostgreSQLSQLJavaScript

Employer work, described in general terms. No patient data, no identifiers, no internal URLs, no screenshots. Third-party clearinghouse names and internal status constants are generalized, and no dollar figure is claimed.

The problem

Billing operations had two problems layered on top of each other. The first was that a single failed portal attempt says almost nothing: it might be a broken credential, or it might be a typo, a timeout, or someone hitting the wrong site. The second was that even once you knew a portal was down, nothing told you whether it was holding up four hundred dollars or four hundred thousand. So the team worked whatever was loudest, which is not the same as whatever mattered.

The system

Raw attempts
mostly noise
Filter
drop non-portal traffic
Latest per claim
window function
Majority rule
failures vs successes
Ranked by money
stranded charges

Four of these five steps exist to decide what counts as a real portal failure. Only the last one ranks anything, which is the opposite of where the effort looks like it should go.

Majority-rules blocker detection

For every site and payer pair, a set of CTEs weighs the historical volume of successful portal actions against failed ones. If failures outweigh successes, the pair is flagged as blocked. It is a judgement about a portal's health over time rather than a reaction to one bad event.

Noise reduction before anything else

Window functions pick out the most recent valid attempt per claim, and the query filters out everything that is not really a portal visit: clearinghouse traffic, automated internal routing, and administrative unblock tasks that would otherwise distort agent success rates.

Ranked by money, not by count

A top-blocked-sites module aggregates across databases, groups blocked payers by site, and orders strictly by total stranded charges, so the list opens on the bottleneck holding up the most revenue.

Drilldown and outreach

Every row opens into claim-level detail. Selecting a blocked site populates the outreach recipients from a queried contact list, so the email goes to the right clinic without anyone searching for the address.

Technical decisions

What was chosen, what was chosen against, and what the trade cost.

Chose: Majority rules: weighing successes against failures over a window of history.
Instead of: Flagging a portal on its most recent failure.
Why: Last-error detection is a few lines of SQL and it is what the data seems to invite. It also produces a wall of false positives, because most individual failures are transient. The real failure mode of a triage tool is not being slightly wrong; it is being wrong often enough that people stop opening it. Majority rules costs more query complexity and buys output the team believes.

Chose: ROW_NUMBER() OVER (…) to select the latest valid attempt.
Instead of: GROUP BY with MAX(timestamp), joined back to the source table.
Why: The grouped version needs a second pass to recover the rest of the row, and if two attempts share a timestamp the join returns both, a duplicate that quietly doubles a count. Ranking in a window keeps the whole row in one pass and makes ties a decision you write down rather than one the query makes for you.

Chose: Mapping foreign keys client-side, from dictionaries built once per load.
Instead of: A cross-database join, or a foreign data wrapper.
Why: The data spans two separate PostgreSQL databases, one for configuration and lookups, one for operational tasks. Joining across them meant either infrastructure I did not own or shipping both result sets somewhere to be merged anyway. Building a Map of site IDs to names once and mapping rows in the UI is constant-time per row and needed no infrastructure change. The tradeoff is real and worth stating: the lookup data is only as fresh as the page.

Chose: Ordering by total stranded charges.
Instead of: Ordering by the number of blocked claims.
Why: Count and value are not correlated. A payer generating hundreds of small blocked claims looks urgent and often is not; one generating a handful of large ones looks quiet and is the actual bottleneck. Ranking by count optimises for a metric nobody in finance cares about.

Chose: Handling nulls explicitly at every comparison and aggregation.
Instead of: Letting SQL's default null semantics apply.
Why: A comparison against NULL returns NULL, not false, so rows do not fail a filter; they vanish from it. In a tool whose entire job is deciding what is missing, silently dropping rows is the one bug that would make every number on the page wrong while everything still looked fine.

The result

The dashboard turns raw task logs into a ranked worklist, with the money attached to each row. The part I would defend hardest in review is not the ranking or the drilldowns; it is the decision to accept a slower, more complicated detection rule in exchange for output nobody has to second-guess.