DEV Community: Alexandra de Lima Diniz

The last month is always a lie: lessons from querying 1 billion rows of Brazilian crime data

Alexandra de Lima Diniz — Fri, 30 Oct 2026 17:58:08 +0000

We run Crime Brasil, a site that turns official Brazilian public-safety data into maps and rankings for every neighbourhood and municipality in the country. Under the hood that is about 1 billion rows: police reports from state security departments, health-ministry mortality records, court and prison data, traffic and more.

Most of the hard problems are not about scale. They are about data that looks right and isn't. Here are two rules we now apply before any number leaves the database, plus what they say about São Paulo in 2026.

Rule 1: the last month in the file is always incomplete

State police data arrives in batches. A report filed today may describe a crime from last week, so the newest month keeps growing for weeks after it "ends".

Here is the São Paulo police-report feed by day, at the end of our current extract:

Day	Reports
2026-08-26	2,459
2026-08-27	2,413
2026-08-28	2,275
2026-08-29	2,061
2026-08-30	1,665
2026-08-31	994

A normal day in São Paulo is 2,400 to 2,900 reports. If you compare "August 2026" with "August 2025" you get a drop in crime that is really a drop in paperwork.

The check is one query. Count by day over the last two weeks and look for the tail:

SELECT data_fato::date AS day, count(*)
FROM crimes
WHERE state = 'SP'
  AND data_fato::date >= (SELECT max(data_fato::date) - 14 FROM crimes WHERE state = 'SP')
GROUP BY 1
ORDER BY 1;

If the last days have half the volume of the days before, close your window one month earlier. We do this for every state, every time.

Rule 2: keep huge microdata out of your main tables

Some official datasets are far bigger than what a website needs. Brazil's hospital-admission records alone are over 120 million rows. Loading that into normal Postgres tables would eat disk and make backups crawl, for data the site touches a few times a day.

So every dataset gets one of two homes:

Site-facing data lives in regular Postgres tables, partitioned by state, indexed for the queries the pages actually run.
Big research microdata stays as Parquet files on disk, queried from inside Postgres with pg_duckdb. Same SQL, same connection, no copy.

The rule we enforce: never bulk-load a Parquet dataset into a heap table. When we need a slice for the site, we aggregate it first and store only the result.

What São Paulo looks like in 2026

With both rules applied (January to July only, because August is still filling in), here is São Paulo state compared with the same months of 2025. These are counts of police reports from the SSP-SP open-data portal:

Crime	Jan–Jul 2025	Jan–Jul 2026	Change
Robbery (excluding vehicles, cargo and banks)	96,706	80,180	−17.1%
Intentional homicide	1,415	1,278	−9.7%
Robbery followed by death (latrocínio)	84	47	−44.0%

The robbery decline is not a one-month blip: the monthly count went from about 15,000 in mid-2024 to about 10,500 in July 2026, falling a little almost every month.

You can explore the same data by city and neighbourhood on the São Paulo state page, or compare places in the rankings.

Takeaways

Before you compute a change, prove the newest month is complete.
Decide where each dataset lives before you load it, not after the disk fills up.
Show your window. "January to July" is less exciting than "2026", and much harder to get wrong.

If you work with Brazilian open data, I'd like to hear which datasets gave you the most trouble.

Querying 3M+ Brazil crime records with a free public REST API

Alexandra de Lima Diniz — Thu, 23 Apr 2026 23:50:28 +0000

Brazil has one of the most complex crime landscapes in Latin America — but getting clean, queryable data has historically been a nightmare of PDFs, broken government portals, and inconsistent formats.

We built Crime Brasil to fix that. It's a free platform with 3M+ geocoded crime records from RS, RJ and MG, exposed via a public REST API.

What's available

3M+ individual crime records from Rio Grande do Sul (bairro-level granularity)
Municipality-level aggregates for Rio de Janeiro and Minas Gerais
Data from 2019–2026, updated monthly from official SSP sources
Rates per 100,000 inhabitants for fair regional comparison

Quick API examples

Get state-level totals for Rio Grande do Sul:

GET https://crimebrasil.com.br/api/heatmap/states?selected_states=RS

Get municipality heatmap data:

GET https://crimebrasil.com.br/api/heatmap/municipios?selected_states=RJ&ano=2024

Search for a location:

GET https://crimebrasil.com.br/api/search?q=Porto+Alegre

Get detailed stats for a specific location:

GET https://crimebrasil.com.br/api/location-stats?state=RS&municipio=PORTO+ALEGRE&bairro=CENTRO+HISTORICO&ultimos_meses=12

Python example

import httpx

base = "https://crimebrasil.com.br/api"

# Get top municipalities by crime rate in RJ
r = httpx.get(f"{base}/heatmap/municipios", params={
    "selected_states": "RJ",
    "ano": 2024,
    "rate_mode": True
})

data = r.json()
top10 = sorted(data, key=lambda x: x["weight"], reverse=True)[:10]
for m in top10:
    print(f"{m['name']}: {m['weight']:.1f} per 100K")

Open data, open source

All source data comes from official Brazilian state security secretariats (SSP/RS, ISP/RJ, SEJUSP/MG) under open data laws. The platform itself is also open — explore it at https://crimebrasil.com.br or check our datasets on Kaggle, Zenodo, and HuggingFace.

Happy to answer questions about the data or methodology in the comments.

3 million geocoded crime records from Brazil — free API and datasets

Alexandra de Lima Diniz — Thu, 23 Apr 2026 22:47:45 +0000

We've been quietly building Crime Brasil — an open-data platform covering violent crime across Brazil. Here's what's in it and how to use it.

What the data covers

Rio Grande do Sul: 2.99 million occurrences (2022–2025), 497 municipalities, geocoded to neighborhood level
Minas Gerais: 965K violent-crime records (2019–2026), 853 municipalities
Rio de Janeiro: 37K ISP/CISP records
PRF DATATRAN: ~500K federal road accidents nationally (2020–2026)
DATASUS SINAN: interpersonal-violence records nationally (2009–2024)

All data comes from state Secretariats of Public Security (SSP-RS, SSP-MG, ISP-RJ) and federal sources. License: CC BY 4.0.

The API

Every city/state has its own endpoint:

# Crime counts by month for Porto Alegre
curl 'https://crimebrasil.com.br/api/city/rs/porto-alegre?type=monthly'

# Top 10 neighborhoods by homicide, last 12 months
curl 'https://crimebrasil.com.br/api/city/rs/porto-alegre/bairros?crime=homicidio'

The response is plain JSON — crime counts aggregated by type, period, and neighborhood.

Downloading datasets

Full datasets are on:

Zenodo — DOI 10.5281/zenodo.19712036 (RS, 3M rows)
Zenodo — DOI 10.5281/zenodo.19711971 (MG, 965K rows)
Kaggle — MG dataset
HuggingFace — RS dataset

Quick Python example

import pandas as pd

# Load RS dataset from Zenodo
df = pd.read_csv(
    'https://zenodo.org/records/19712036/files/crime-brasil-rs-2022-2025.csv.gz',
    compression='gzip'
)

# Homicides per year in Porto Alegre
homicidios = df[
    (df['municipio_fato'] == 'PORTO ALEGRE') &
    (df['tipo_enquadramento'].str.contains('HOMICIDIO'))
].groupby(df['year_month'].str[:4]).size()

print(homicidios)

Coverage and methodology

We cover 99.4% of the Brazilian population across the states we have data for. The geocoding pipeline assigns lat/lon using a four-pass hierarchy:

Exact address match
Neighborhood centroid
Municipality centroid
State centroid (fallback)

99.99% of RS records land at pass 1 or 2.

LAI requests in progress

We've filed Freedom of Information (LAI) requests in 14 states: AL, AM, BA, ES, MA, MG, PR, RJ, RN, RO, RR, RS, SC, TO. As responses arrive, we publish them.

The platform is at crimebrasil.com.br. Press kit and methodology at crimebrasil.com.br/imprensa.

How We Built an Open Crime Data Platform for Brazil (3 Million Records)

Alexandra de Lima Diniz — Thu, 23 Apr 2026 22:42:20 +0000

How We Built an Open Crime Data Platform for Brazil (3 Million Records)

Brazil has one of the world's highest homicide rates — over 47,000 murders per year. Yet accessing crime statistics is surprisingly hard. Data is fragmented across 27 state secretariats, each with different formats, different crime taxonomies, and wildly inconsistent update schedules.

We built Crime Brasil to fix that.

What it does

Crime Brasil is an interactive crime map covering Rio Grande do Sul, Rio de Janeiro, and Minas Gerais — with bairro (neighborhood) level granularity for RS, and municipality-level for the others.

Key features:

3+ million individual crime records for RS (2022–2026)
Choropleth maps at state → city → neighborhood drill-down
Per-100K-inhabitants rate normalization (so you can actually compare)
Crime type filters (homicide, robbery, drug offenses, etc.)
Time range selector (last 6/12/24/36 months or full history)
API access — all data available via REST endpoints

The tech stack

Backend: FastAPI + SQLAlchemy + SQLite (yeah, SQLite — handles 3M rows fine with proper indexes)
Frontend: Next.js 14 + Leaflet.js + Tailwind CSS
Deployment: Docker + zero-downtime atomic alias swap (no Kubernetes needed at this scale)
Data pipeline: Python ETL from SSP/RS ZIP files, ISP/RJ CSV, SEJUSP/MG CSV

The hardest part: bairro name normalization

The same neighborhood appears as:

CENTRO HISTÓRICO
CENTRO HISTORICO
centro historico
CENTRO-HISTÓRICO
CTR HIST

Across police records, census data, and map boundaries.

We built a phonetic normalization pipeline using custom Brazilian Portuguese rules — strips accents, normalizes common abbreviations (RUA → R, AVENIDA → AV), handles compound names. This alone took about 3 weeks of iteration against real data.

Why SQLite, not PostgreSQL

Conventional wisdom says "use Postgres for anything serious." But SQLite with covering indexes on (state, year_month, tipo_enquadramento) serves our heatmap queries in <100ms at 3M rows. The database is ~800MB. It fits on any $5 VPS. Zero connection pooling headaches. Zero migrations for most schema changes.

We do have one performance rule: always use BETWEEN for year filters, never LIKE. SQLite uses the covering index for BETWEEN '{ano}-01' AND '{ano}-12' but not for LIKE '{ano}-%'.

Open data

All datasets are published:

Kaggle: MG Violent Crime 2019–2026
Zenodo: RS Full Dataset — DOI 10.5281/zenodo.19712036
HuggingFace: RS 2022–2026

What's next

São Paulo coverage (aggregated municipal-level data already being processed)
API authentication for high-volume consumers
Historical comparison charts per location

The entire REST API is public: https://crimebrasil.com.br/api/heatmap/municipios returns municipality-level heatmap data, https://crimebrasil.com.br/api/location-stats returns detailed stats per location.

Happy to answer questions about the data pipeline, the normalization approach, or the zero-downtime deploy architecture.

Crime Brasil: https://crimebrasil.com.br — open crime data for Brazil

How we built Crime Brasil: an open-data crime map for 214 million Brazilians

Alexandra de Lima Diniz — Thu, 23 Apr 2026 22:19:37 +0000

Brazil has one of the highest homicide rates in the world — yet finding reliable, granular crime data at the neighbourhood level has historically been nearly impossible for ordinary citizens, journalists and researchers.

We built Crime Brasil to fix that.

What it does

Crime Brasil is a free, open-data interactive map that lets anyone explore violence and criminality statistics across Brazil — broken down by state, city and neighbourhood. No paywall. No signup required.

Current coverage:

Rio Grande do Sul — neighbourhood-level (bairro) data
Rio de Janeiro — city-level breakdown
Minas Gerais — city-level breakdown
3 million+ crime records from official government registers

The tech stack

Next.js 15 (App Router) + TypeScript
PostgreSQL with PostGIS for geospatial queries
Deck.gl + MapLibre for the map rendering
Cloudflare CDN and WAF
REST API with per-100k-inhabitants normalisation built in

Open data, all the way

All source data is published as open datasets:

Try it

👉 crimebrasil.com.br

If you're working on public safety research, data journalism, or civic tech in Brazil — we'd love to hear from you.

4,524 commits in 7 weeks: what I learned shipping a real product with Claude Code

Alexandra de Lima Diniz — Thu, 23 Apr 2026 19:23:29 +0000

TL;DR

4,524 git commits in 7 weeks (~92/day)
~42,000 lines of code across Python backend + Next.js frontend
~3M records ingested, 79,024 neighborhoods geocoded, zero-downtime production deploys, E2E smoke tests, APScheduler jobs, SEO snapshot system
Total spend: ~$500 (Claude Max $200 × 2 months + Hetzner ~$30 × 2 months)
Built alone. Shipped.

I'm not going to tell you Claude Code is magic. It isn't. But if you treat it like a very fast, very literal colleague who forgets things between sessions, you can ship more than you'd think. Here's the workflow that worked for me.

The product is Crime Brasil — an open-data platform for Brazilian crime statistics. Doesn't matter for this post. What matters is the process.

Workflow that actually worked

1. Plan mode before anything non-trivial

Before any change touching >3 files or introducing new abstractions, I forced a /plan pass. No code, just a tree of what changes where and why.

This cut the "Claude confidently wrote 400 lines of the wrong thing" bug in half. The other half came from me being sloppy about what I was actually asking for.

Pattern:

1. Tell Claude the goal
2. Ask for a plan (no code)
3. Push back on anything that feels wrong — usually scope creep
4. When the plan feels right, say "implement"

Boring. Works.

2. Sub-agents for parallel anything

Independent work ran in parallel sub-agents. Research + implementation + QA could all run concurrently instead of me waiting for each to finish.

research agent — "understand how X works, come back with a map"
implement agent — "apply the diff described"
qa agent — "verify the fix, run the regression tests"
review agent — "is this safe to merge"

I'd fire three of these in parallel and get stitched output in a third the wall time.

3. Session scratchpad as an anti-compaction tool

Claude Code compacts its context when conversations get long. After compaction you can lose crucial decisions from earlier.

Fix: every session writes to a scratchpad file (~/claude-sessions/session_<topic>.md) with timestamped entries. Key decisions, user preferences, things we tried that failed. Before spawning any sub-agent I inject the relevant scratchpad entries into the prompt.

This alone probably saved a dozen hours of "wait, why did we decide not to do that again?"

4. Memory system for permanent preferences

Claude Code has a memory system that persists across conversations. I ended up writing ~80 memory files over 7 weeks, categorized:

user — who I am, what I work on, what I already know
feedback — rules I've enforced ("never push to main directly", "always chrome-verify fixes, not just curl")
project — state of ongoing work
reference — where external resources live (dashboards, tokens, API docs)

Every time I corrected Claude on the same thing twice, I saved a memory rule. Examples from my actual file:

Never slow bot renders for SEO — fixed a bad server-side bot-detection idea
No lossy analytics filters — never exclude geographic data to "clean up" analytics
Wait-loop pattern — use while pgrep not until ... && ! kill -0 (the latter busy-waits)
No arbitrary safeguards — don't add population floors to hide bad data, fix the data
Never substitute solutions — if user asks to fix X, fix X, don't silently solve by changing Y

That last one came up repeatedly. Claude loves to pivot to an "easier" problem.

5. Hooks for guardrails

Some rules can't be enforced by just asking nicely. For those, I used Claude Code's hook system to block certain actions entirely.

Examples I ended up needing:

Block any git commit --no-verify (never bypass pre-commit hooks)
Block rm on data files without explicit confirmation
Warn on any git push origin main — all changes must go through dev branch + staging first

Hooks turned bad defaults into impossible-by-default. Worth the setup time.

6. Verification before declaring "done"

Claude loves to say "fixed!" based on an API test passing. That's not the same as the feature actually working in a browser.

I ended up with a rule: every UI-touching change gets Chrome-verified before being marked complete. A Playwright script clicks through the real flow and screenshots the result. If the screenshot looks wrong, it's not done.

Memory file: "NEVER report a fix as done without Chrome visual verification."

Before this rule: "fixed!" → deploy → user finds it broken → revert. After: "fixed, verified on /cidade/rs/porto-alegre, panel shows 509K crimes, screenshot attached."

Specific bugs Claude caused

Honest list. These are patterns; I saw each of them multiple times.

"Let me also refactor while I'm here"

Ask for a small bug fix, get back 400 lines of unrelated cleanup. Sometimes broke unrelated features.

Memory rule: "Don't add features, refactor, or introduce abstractions beyond what the task requires. Three similar lines is better than a premature abstraction."

Inventing API endpoints

Asked Claude to "call the API to get X data." It wrote code calling /api/thing that didn't exist. Tests failed. When pushed: "oh right, that endpoint doesn't exist. Let me write it first."

Fix: when consuming an API, I started forcing it to read the actual source files first. Read src/api/routes.py, then write the client. Slower, zero hallucinations.

Silent feature deletion

Twice Claude "fixed" a bug by removing the buggy feature. Asked to fix a map polygon render issue, got back code that just disabled polygons entirely. Clean green tests. Feature gone.

Memory rule: "NEVER remove features unless user explicitly says to. 'bugado' means fix, not remove."

Overclaiming completion

The biggest pattern. "Done!" → I check → 2 of 4 items done, 2 untouched. Claude was genuinely under the impression it had finished.

Fix: I stopped taking "done" at face value. Every "done" got a follow-up "walk me through what you changed file-by-file, and show me the diff for each." Usually the second response corrected the first.

What Claude can't do (yet)

Architect from scratch. Given a vague brief like "build a data platform for X," Claude produces a generic Django blog. Needs strong direction.
Know your domain. Took months to teach it Brazilian bairro-name quirks — there's no substitute for local knowledge, and Claude will confidently generalize from Stack Overflow examples that don't apply.
Remember across sessions without help. Compaction + context limits mean every session is partial amnesia. The scratchpad + memory system is the only thing that saved me.
Decide what not to build. Claude will happily implement bad feature ideas. Saying "no" is still human work.

Cost breakdown (~$500)

Item	Cost
Claude Max subscription ($200/mo × 2)	$400
Hetzner dedicated server ($30/mo × 2)	$60
Resend email API (free tier)	$0
Cloudflare (free tier)	$0
Domain	$12
Total	~$472

For reference: a single senior contract dev week at market rates is $4-6K. I shipped this in 7 weeks of evenings + weekends for ~$472 in tools + hosting.

The caveat: this is a solo project. I didn't have to coordinate with anyone, and nobody needed to review my work. That's a rare situation.

Lines of code

Category	Lines
Python backend (FastAPI + scrapers + ingest + APIs)	23,559
TypeScript frontend (Next.js + Leaflet)	18,762
MDX content (articles)	6,165
Total	48,486

92 commits/day sounds insane. In practice most commits are small and auto-checkpointed. Real "unit of work" commits are maybe 20-30/day.

What I wouldn't skip if doing it again

In priority order:

Scratchpad files. Save at least 10x their cost in "wait, what did we decide?" time.
The /plan discipline. Every non-trivial change gets a plan first. Boring, works.
Memory rules for every recurring correction. Rule of twos: if you correct Claude on the same thing twice, write it down.
Chrome-verify before declaring done. API tests don't cover UI. UI bugs always happen on the last Friday.
Hooks for dangerous defaults. Anything you really don't want — make it impossible by default, not just discouraged.

What I'd do differently

Start the memory system earlier. I was 2 weeks in before I realized it existed. Week 1 was slower than it needed to be.
Spin up sub-agents sooner. For a while I was doing everything sequentially in the main agent. Parallel work is a huge speed-up.
Write the CLAUDE.md from day 1. A per-project CLAUDE.md explaining architecture and conventions prevents a lot of drift.

Closing

Crime Brasil is live at crimebrasil.com.br. It serves ~3M records across 497 municipalities, gets Google traffic for specific city and neighborhood queries, and was built almost entirely in a Claude Code harness.

I'm happy to answer questions about the workflow. The answer to "can AI replace developers" is still no, but "can a developer with AI replace two developers without AI" is absolutely yes, and that's interesting enough.

Follow along on X at @Crime_Brasil if you want to see what ships next.