Moving the CRM maths out of Python and into Postgres

The first version of the CRM dashboard did its maths in Python: ask the database for rows, loop over them, count. It worked, but the counting was happening in the wrong place.

What the Python was doing

Take the lead status chart. Python fetched the status of every lead, then tallied them in a dictionary. The seven headline cards took seven queries. The figures behind the daily briefing took ten. All in, one dashboard load made about 33 queries, and several of them fetched whole rows just to read a count.

That had two costs. Every figure meant shipping rows across the network to a Python process that only wanted a total. And a count in Python is only as complete as the rows the API chooses to send back.

What replaced it

I moved the calculations into Postgres functions: 12 of them, in 327 lines of SQL, each returning one JSON object. The Python side shrank to one call per function:

# before: fetch every row, count in Python
rows = db.table("leads_raw").select("lead_status")
for row in rows:
    counts[row.lead_status] += 1

# after: Postgres counts, Python receives the answer
counts = db.rpc("get_lead_status_breakdown")

The SQL leans on GROUP BY and FILTER clauses, so a source’s total, junk and in-pipeline counts come out of one pass. The briefing’s function takes a date, so the same maths can be rerun for any day.

A dashboard load is now 13 calls. The Python database client went from 419 lines to 197, and the dashboard caches the results for 30 minutes on top.

Rules on top of the numbers

Two fixed rules read the computed tables and write plain sentences into the briefing’s input. A salesperson with more than 100 active leads and a thin pipeline is flagged as overloaded. A source with more than five leads, at least 30% of them junk, is flagged as toxic. The model is told it must report both.

The bug the move introduced

The old Python called a source’s junk share junk_percentage. The new SQL function called it junk_pct. The toxic-channel rule still asked for the old name, Python’s .get() quietly returned nothing, and the rule could no longer fire. There was no error and no warning. A briefing would simply have arrived with one flag fewer.

A later commit the same evening read the new name and lowered the bar from 100% junk to 30%. The lesson is the one I’d pass on: a renamed key fails silently, so test that rules fire, not only that numbers compute.

What moving didn’t fix

Moving code doesn’t merge definitions. The junk rule is still written out in five places, and the copy behind the briefing lists two lead statuses where the other four list three. “Open pipeline” means “not lost” in the briefing’s input and “neither won nor lost” on the dashboard’s card. The fix is one SQL function or view per rule, called from everywhere.

Rules I’d keep

1. Count where the data lives. Return answers, not rows.
2. One call per set of figures, in a fixed JSON shape.
3. Test that rules fire, not just that numbers compute.
4. One definition per business rule.