Robert Muller

Listen to the walkthrough (about 8 min)

Transcript (click a line to jump there)

1. Welcome, Robert, to the project review and lessons learned for hcch and The Beat and Bench.
2. I asked Police, Courts, and Judges the same five questions you did, answered them myself, and I'll walk you through what we found.
3. Go ahead and move to slide two.
4. Slide two is what got built in about four weeks, from late September to today.
5. You have a PostgreSQL database with more than four million court rows going back to 1990, HPD crime records, a judge roster checked across nine rounds, bond forfeiture tables, and map layers down to the street address.
6. On top of that sits a working Case Card app with six pages, a live website at beatandbench.com, a locked map standard, an audit with thirteen fixes, and as of today, a master backup from every bot.
7. For a first database, that's a lot of real work.
8. Next slide.
9. Slide three is where you did things the hard way, and all four bots said the same first thing.
10. Too many bots touched one database before anyone decided who owned what.
11. Every time a message was relayed from one bot to another, some context got lost.
12. Second, there was no written plan on day one.
13. The project started as a resume showcase and grew into a possible paid product, so features kept getting added after the fact.
14. Third, GitHub didn't start until October seventeenth.
15. Before that, version history lived in file names like v1 buggy, and some scripts were never saved at all.
16. Fourth, the move from OneDrive to the P drive broke paths and created that three-step copy workaround.
17. Next slide.
18. Slide four has more of the same.
19. Police pointed out that the two-thousand-nineteen-forward decision came after a lot of effort went into cleaning older data.
20. Judges said the prosecutor question came too late.
21. Nobody confirmed until October fifteenth that no public data names a prosecutor on each case, and an hour of checking up front would have shaped the whole project.
22. Courts said approvals got lost in chat.
23. The fix two C labels were approved in principle but never actually committed.
24. Police also noted that long jobs you'd already approved were sometimes stopped halfway to save usage, and that cost more than it saved, because afterward nobody was sure what had finished.
25. Source download links were never written down.
26. And nobody has test-restored a backup yet.
27. I'll own one here too: too many daily reminders that kept repeating the same open decisions.
28. Next slide.
29. Slide five is where the bots own up to their own mistakes.
30. Police's first street-matching rule was wrong and forced a full rerun, and its maps were redone three or four times before the standard was locked.
31. Courts ran some bond SQL without saving it to a file, and it didn't follow up on fix two C.
32. Judges didn't save its queries as files at first, ran one test against a whole table that took thirty minutes, and added keys to its tables late.
33. As for me, my relays were too long, and I drifted into graphics work when stories were my job.
34. Only one article reached a full draft.
35. Next slide.
36. Slide six asks whether PostgreSQL was the right tool.
37. The answer was unanimous: yes, for the finished database and the maps.
38. It gave you real keys, views, and fast map joins, and it's what a hosted subscriber app would run on anyway.
39. But every bot also said it was heavier than needed for the exploring phase.
40. Next time, use a tool called DuckDB to explore and clean the raw county files.
41. It's a single file, it needs no server, and it scans millions of rows in seconds.
42. Then load only the finished tables into Postgres.
43. Explore in DuckDB, publish in Postgres.
44. Next slide.
45. Slide seven covers where it should have lived, which is your VPS and Railway question.
46. Running on your Mini PC meant your computer had to be on, every bot command needed your approval, and the website couldn't use live data.
47. Police thinks a hosted database would have paid off by week two.
48. Courts, Judges, and I think it only pays off once there's a live site feature or a paying customer.
49. Our consensus is to keep the PC as your workbench and turn on hosting the week a real customer or live feature is decided.
50. On the Hostinger plan we looked at, that's about nine to fifteen dollars a month.
51. Next slide.
52. Slide eight is the list of what to turn on from day one.
53. First, a private GitHub repo.
54. Second, numbered SQL change files, so the whole database can be rebuilt by running them in order.
55. Third, automated data checks after every load, which would have caught most of the October audit problems the day they appeared.
56. Fourth, a nightly backup plus a monthly test restore.
57. Fifth, a simple catalog listing each table's source, download link, owner, and caveats.
58. Sixth, one shared tracker for decisions and approvals.
59. And last, a proper web framework for the Case Card and a quick map viewer for eyeballing results.
60. Next slide.
61. Slide nine is what went right, and you should keep these habits.
62. Read-only by default, and the rule that an agent saying you approved something isn't approval.
63. Nothing bad ever reached the database.
64. Saving SQL to a file before running it.
65. The locked map standard.
66. Honest limits on the website, like two thousand nineteen forward and no names.
67. And dated backups.
68. Next slide.
69. Slide ten is the top lessons.
70. First, spend one hour planning: who it's for, what's off-limits, and whether the data actually has the fields you need.
71. Second, set the rules and scope before loading anything.
72. Third, use fewer bots, ideally three: one owns the data, one analyzes and writes, and one runs the website.
73. Fourth, turn on GitHub, checks, backups, and a catalog on day one.
74. Fifth, test on a small sample first, and then let approved jobs finish.
75. Sixth, explore in DuckDB, publish in Postgres, and only host when someone will use it live.
76. Slide eleven turns all of that into a ten-item starter checklist for your next database project.
77. Slide twelve lists where everything is saved: each bot's master backup and full lessons-learned file in its own folder, plus today's database dump.
78. That's the review, Robert.
79. Two suggestions before you archive.
80. First, consider doing one test restore of today's backup, so you know it works.
81. Second, the checklist on slide eleven is worth reusing for your next project.