Charged in Pinellas, No Lawyer Yet
Pulls each day's new drug charges from Pinellas County public court filings and lists every defendant with a mailing address.
The Problem
Pinellas County publishes a file of every new criminal case filed each day. It is public, but it is one file per day, mixed across every charge type, and nobody reads it. Anyone who wanted to know which of those cases were drug charges, how serious they were, and where the defendant gets mail would have to open the file every morning and sort it by hand.
What It Does
Each run checks the county's public directory for daily filing files it has not already processed. It downloads each new file, saves the original to an archive, and reads every row. It keeps only defendants whose charge text matches a drug offense, sorts each charge into a category and a severity tier, flags the substance named in the charge, splits the mailing address into street, city, state, and ZIP, and notes whether an attorney is on the case. Each charge lands as one row in a master sheet, keyed so reruns never create duplicates, and a summary email reports what was found.
A direct mail company or a criminal defense practice that wants to reach people newly charged with drug offenses in Pinellas County, where that kind of outreach is permitted. Ryan runs it daily without an end customer yet.
Outcome
Since late April 2026 it has processed 141 daily files covering 14,911 case rows and kept 2,004 drug charges across 1,241 cases, every file processed without a failure. About 96 percent of those charges show no attorney listed at filing. A separate script Ryan runs on the same sheet sets aside charges where the defendant's address is transient or general delivery, since only leads with a real mailing address are worth anything.
Screenshots
How It's Built
A Code node resets a per-run counter block in workflow static data. An HTTP node fetches the county's public directory listing as HTML, and a Code node regex-extracts every dated daily CSV link, sorted by date. A Google Sheets node reads the Processed Files ledger, and a Code node drops any file already marked SUCCESS, so the ledger is the only state that matters between runs. A Loop Over Items node handles one file at a time: download the CSV as binary, fan out to a Google Drive upload of the raw file and a Merge node that waits for the upload to finish, then a custom tolerant CSV parser in a Code node. The main Code node keeps DEFENDANT rows only, runs the charge text through a bank of regexes to decide if it is a drug offense and which category, tier, and substance flags apply, parses the address, derives attorney status, and builds a Unique ID of Case Number, Charge Number, and Party Type. An IF gate skips files with zero matches; otherwise the records are expanded and upserted into the Master Drug Cases tab on that Unique ID. Every file then writes a SUCCESS or FAILED row to the ledger, waits two seconds, and returns to the loop. When the loop drains, a summary email goes out with files, rows, matches, and exclusions for the run.
By the numbers
- daily files processed
- 141 (2026-04-28 through 2026-09-15), all SUCCESS
- case rows reviewed
- 14,911
- drug charge rows on the master tab
- 2,004
- distinct cases on the master tab
- 1,241
- charges per case
- 1 to 16, median 1
- drug charges per daily file
- 4 to 53, median 16
- rows per daily file
- 50 to 230, median 104
- no attorney listed
- 1,921 of 2,004 rows (96%)
- category split
- Simple Possession 1,204; Paraphernalia 459; Sale, Delivery, or Intent to Sell 207; Trafficking 128; Drug House 6
- severity split
- Tier 1 341; Tier 2 1,400; Tier 3 263
- felony share
- 1,728 of 2,004 rows filed as felony case type
- address parsed cleanly
- 1,997 of 2,004; 7 flagged for review
- rows set aside for transient or general delivery address
- 664, by a separate Google Apps Script on the same sheet
- run time
- about 1.5 seconds when nothing new is posted; 47.8 seconds to process 5 new files
Key Decisions
Wrote a custom CSV parser instead of using the built-in Extract From File node
The county export has unescaped double quotes inside fields, such as nicknames and address fragments, and the strict parser threw Invalid Closing Quote. The custom parser treats a quote as closing only when the next character is a comma, newline, or end of file, and keeps any other quote as a literal.
Ledger-driven idempotency: a file is skipped only if the Processed Files tab says SUCCESS
A failed parse is logged as FAILED rather than SUCCESS, so it is retried automatically on the next run, and one bad file cannot stop the loop or the rest of the day's files.
One row per charge, not per person, keyed on Case Number | Charge Number | Party Type
The upsert on that key makes reruns and backfills safe with no duplicates, and keeps every charge count and substance flag visible instead of collapsing them to one line per defendant.
Ingest everything first, including cases that already have an attorney, and filter later
Ryan wanted the first pass to bring in as much information as possible. Filtering happens per client based on what that client needs. Keeping attorney-listed cases also produces marketing numbers, like what percentage of people charged already have an attorney in place.
Severity tiers: Tier 1 for trafficking, sale, delivery, intent to sell, or drug house; Tier 2 for any other felony; Tier 3 for everything else
Ryan recalls it was meant to track the severity of the crime but does not remember the exact reasoning behind the cutoffs.
Category priority order: Trafficking beats Sale or Delivery, which beats Drug House, which beats Paraphernalia, which beats Simple Possession
The classifier checks the most serious match first, so a charge whose text hits more than one pattern lands in the heaviest category instead of the first one the regex happens to see.
Transient and general delivery addresses are cleaned out by a separate Google Apps Script on the sheet, not by the workflow
The workflow only drops rows with a completely blank address. Ryan says the only leads worth anything are the ones with a real mailing address, so the script moves the rest to a separate tab.
Two runs per day
Ryan says this was an accident left over from testing when the county posted its files. One run a day is enough.
What broke & how it was fixed
On 2026-08-17 the built-in Extract From File node started failing with Invalid Closing Quote because the county's CSV contained stray double quotes inside fields. Before the fix, a single malformed file killed the whole execution. The fix was the tolerant Parse CSV Rows node plus an error branch that logs the file as FAILED and lets the loop continue, so the file is retried the next run instead of blocking everything behind it.
Where else this applies
Any clerk, agency, or regulator that publishes a daily file has the same shape: a directory of dated exports, a ledger of what has already been ingested, a parser that tolerates the publisher's mistakes, a keyword classifier, and an upsert keyed on the record's natural ID. Swap the regex bank and the same build captures DUI filings, evictions, code violations, or new business licenses. The ledger-plus-upsert pattern is what lets it backfill months of history on day one and then run unattended.