A territory with children is a container, not a total

G12 (Friendly Circles) is a multi-tenant product for churches and discipleship networks. Its core records are people, circles with their leaders, a network tree that shows which circle started which, territories marked on a map, and training that can be dispatched and tracked.

Territories are where the data model and the real world disagree most often. A "territory" can be a state, a local government area, a ward, or a village. It can be a point with a radius, or a named area someone drew. And it can contain other territories — which is exactly where our coverage numbers went wrong.

The double count

The coverage summary had a bug that looked like arithmetic and was really semantics. It summed the target population of every active territory, flat. Then, for coverage, it added the target of every covered territory independently. Both counts included every row.

Give a state a target of five million people and its eighteen villages their own targets, and the funnel's denominator becomes the state plus every village — the same people counted twice, once as a region and once as the places inside it. The numerator does the same thing, so the percentage looks plausible while both numbers are wrong.

We could have fixed the query. The reason we did not is that the query was faithfully implementing a model that had no rule for what a parent's number means.

The rule we wrote down

A territory with children is a container, and a container is excluded from population totals. Leaves carry the numbers, so a roll-up is the sum of the leaves.

That one sentence settles several cases at once:

• A state with villages contributes no target of its own; the villages' targets roll up to it.
• A territory nobody has subdivided stays a leaf and keeps its own number, so the common "drop a pin and set a radius" case is unchanged.
• A village that is later split into two areas stops contributing directly, and its children take over — no migration, no stored total to keep in sync.

The alternative — storing a denormalized total on every parent — would have made the bug disappear from the summary screen while making it permanent in the data. Someone would eventually add a child and forget to update the parent, and the number would be wrong in a way no query could detect.

The rule also has to live where records are created, not only where they are read. If a territory with children can still be given a target, the summary will keep being wrong no matter how it is computed.

Coverage is not one switch

The same lesson applied to coverage itself. The product had a hand-pickable six-state control, which made every state a claim someone could set directly. But "engaged", "connected", "established" and "multiplying" are descriptions of what has happened in a circle; they are derived from recorded activity, not chosen from a menu.

So the model now separates them: mapped and assigned are managed by a person, because they describe an intention. The upper states are derived from activity. A leader can override a derived state, but only with a written reason, and the override is audited.

That is a data rule again. If a derived state can be typed in freely, it stops meaning anything, and every report built on it becomes an opinion.

The map we almost rewrote

The territory work also nearly cost us a working map for the wrong reason.

The planning documents said the map used OpenFreeMap with MapLibre. The code used Leaflet with Esri raster tiles. The obvious move was to "correct the drift" by rewriting the map to match the plan.

Then we checked the constraint the plan line existed to protect: no paid map budget, and no API key. The tiles already in use meet it. The three basemaps — light, satellite, terrain — are keyless and free, and the map already prints the credit line that makes the free tier honest. A rewrite would have swapped working marker labels, a hover card, a command-scroll zoom guard and a radius floor for vector styling this map does not need.

The decision was to keep the stack and correct the document. The lesson is that a plan line is not a requirement until you know which constraint it was protecting.

Honest about population

We wanted a population-density layer and could not find one that was both free and keyless. The alternatives were checked and failed: a demographics service answered 400 without a key, a population dataset was only available as a 1.7 GB global file, another service answered 403, and a free tile set carried no population layer at all.

What shipped is a layer of population centres — about 4.9 MB of Natural Earth places, reduced to a smaller GeoJSON file and drawn as circles sized by population, off by default. The panel calls them population centres rather than a density raster, because that is what they are. Crediting the source is honest even where the licence does not require it.

What generalizes

Three things from this work transfer beyond G12:

1. When a total is wrong, ask what the record means before changing the query. A query that sums every row is not a bug if every row is a leaf. It is a bug because the model allowed a row that is not.
2. Derive what can be observed; let people set only what they intend. A status anyone can type is a label, not a measurement.
3. Write the constraint, not just the choice. "Use MapLibre" aged badly; "free and keyless" was still true, and checking it would have saved a rewrite.

None of this is visible in a screenshot. It is the difference between a coverage number a leader can act on and one that merely looks precise.