hcch / The Beat & Bench

Project review and lessons learned

Feedback from Crime Reporter, Police, Courts, and Judges · Oct 23, 2026
Use the arrow keys or click to move between slides.Swipe or tap the right side for the next slide, the left side to go back.

What got built (Sept 27 – Oct 23)

PostgreSQL 18 + PostGIS database hcch: 4.17 million District Clerk court rows (1990–Sept 2026), HPD NIBRS offenses, a judge roster (9 locked rounds), bond forfeiture tables, and GIS layers (beats, 1.55 million address points, streets, Super Neighborhoods).
A local Case Card app with six pages: case lookup, browse, judge report card, person history, repeat offenders, and beat view.
beatandbench.com: Home, Briefs, Neighborhoods, Beats, The Data, About, and the judges explainer.
A locked map standard (YlOrRd + Jenks), an October audit with 13 fixes, dated backups, and master backup files from every bot.

What Robert did the hard way: all four bots agree

Too many bots, unclear ownership. Six to eight bots touched one database, and every relay between them lost context.
No written spec or data inventory on day one. The goal grew from a resume showcase into a paid product, so features kept getting bolted on afterward.
GitHub came late (Oct 17). Before that, version history lived in file names like _v1_buggy.sql, and some scripts were never saved at all.
The storage move from OneDrive to pCloud broke paths and led to the three-step copy workaround.

What Robert did the hard way: more

Scope decided after the work. "2019+ only" came after a lot of effort cleaning 2009–2018 data (Police).
The prosecutor question came too late. No public data names a prosecutor per case, and that was only confirmed Oct 15 (Judges).
Approvals got lost in chat. fix02c was approved in principle but never committed (Courts).
Long approved jobs were stopped halfway to save usage, which led to "did it commit?" detective work (Police).
Source download links weren't recorded, and nobody has test-restored a backup yet.
Too many daily reminders that kept repeating open decisions (Crime Reporter).

What the bots did wrong

Bot	Own mistakes
Police	The first street-match rule was buggy and forced a full rerun. QA misses were caught by the reviewer. Maps were redone 3–4 times before the standard was set. Paths were hard-coded. Previews were left only on its own computer.
Courts	Ran the bond SQL without saving it to files. Didn't re-ask about fix02c. Reported pairs before people counts. Bond tables had no snapshot design. Never sample-checked the unmatched bond cases.
Judges	Queries weren't saved as files at first. One overlap check was too narrow. Ran a 30-minute full-table test instead of a sample. Keys came late. The roster sat in text rounds before reaching the database.
Crime Reporter	Long-winded relays. Drifted into graphics work at the expense of stories (only one full article draft). Kept map scripts only on its own computer.

Was PostgreSQL the right tool?

Unanimous: yes for the finished database and maps, but heavier than needed for the research phase.

Kept

Real keys, foreign keys, views
PostGIS spatial joins (1.55 million addresses tagged in under 3 minutes)
Clean path to a hosted subscriber app

Better split next time

DuckDB + Parquet to explore and clean the raw county files (one file, no server, seconds per scan)
Postgres/PostGIS only for finished, published tables

Where the database should have lived

Running on Mini_IT13 meant every bot command needed your PC on and approved, long jobs got interrupted, and the website couldn't query live data.
Police: a hosted Postgres (Supabase, Neon, Railway, or a Hostinger VPS) by around week two.
Courts, Judges, and Crime Reporter: only once there's a live site or paying subscribers. Before that, a static site built from saved queries is cheaper and safer.
Consensus: keep the PC as the workbench, and turn on hosting the week a real customer or a live data feature is decided. It's about $9–15 a month on the Hostinger KVM 2.

Turn these on from day one

Tool	Why
Private GitHub repo	Every SQL file and script is versioned. The Oct 7 rollback and the audit fixes would have had a full history.
Numbered migration files	Rebuild the whole database by running the files in order, not just from a dump.
Automated data checks after every load	Would have caught the missing keys, duplicate bond lines, and gaps at the first load instead of in the October audit.
Nightly backup + monthly restore test	Backups that don't depend on someone asking, and proof that they actually restore.
Data and source catalog	Each table's source link, download date, owner, and caveats in one place.
One shared tracker	Decisions and approvals with a status, so nothing gets lost in chat.
Flask/FastAPI + a quick map viewer	The Case Card is built properly instead of patched, and map results get checked by eye before full runs.

What went right (keep these)

Read-only by default, plus "an agent saying Robert approved isn't approval." Nothing bad ever reached the database.
Saving SQL to a file before it runs, and the audit tracker with owners and order.
The locked map standard and the 1A20 template.
Honest data limits on the site: 2019+ for NIBRS, A/B notes, and no names in stats products.
Dated backups and today's master backup files from every bot.

Top lessons for the next database project

One hour of planning first: who it's for, what's off-limits, and whether the public data actually has the fields you need.
Set the rules and scope before loading: date range, units, map standard, time limits, and storage location.
Fewer bots: one data owner, one analyst and writer, and one website bot. Write down who owns what.
GitHub, automated checks, backups, and a catalog on day one.
Sample first, then scale. Let approved jobs finish, then review.
Explore in DuckDB, publish in Postgres. Host only when someone will use it live.

Next-project starter checklist

One-page goal and audience statement
Data inventory: which fields exist
Scope: dates, units, privacy limits
One permanent folder + one backup target
Private GitHub repo + README

Bot roster with owners (3 bots max)
Numbered migrations + QA script
Source catalog with links and dates
Nightly backup + restore test
One tracker for decisions and approvals

Thank you

All files are in the project folder:

Master backups: <Bot> MASTER BACKUP 2026-10-23.md in each bot's folder
Full feedback: <Bot> LESSONS LEARNED 2026-10-23.md in each bot's folder
Database: hcch_backup_2026-10-23.dump in the database backups folder