A distribution business looks like a spreadsheet if you only ever see one row of it. Target in one column, bookings in the next, payments after that, and a total at the bottom that somebody reconciles by hand on a Friday afternoon. But the thing that spreadsheet is flattening is a tree. Someone at the top sets a number for the whole country. That number gets split across regions, the regions across clusters, and the clusters land on a field officer who knows the four or five distributors on his patch by their first names. Sooner or later two people open what they believe is the same report, one of them standing in a field at six in the morning with one bar of signal and one of them at a head office with two monitors, and they need to see different numbers that are both correct.
We built one of these for seed distribution across five states: annual planning at the top, distributor onboarding at the bottom, and bookings, indents, payments, wallets and dispatch in between. The moment you take the hierarchy seriously, the spreadsheet model breaks, and it breaks in a specific place. It assumes there is one answer to the question "how are we doing". There isn't. There are as many answers as there are positions in the tree, and the system's whole job is to give each person the one that belongs to them.
Role tells you the verbs, not the rows
The first instinct is to model access as a role. A field officer can create a booking, a manager can approve it, finance confirms the payment. That is real, and you do need it. We gate every write on it. But a role on its own answers the wrong question. It tells you what a person is allowed to do. It says nothing about which part of the world they are allowed to do it to. Two field officers hold the identical role and must never, under any circumstance, see each other's distributors.
So position in the tree has to be attached to the person as a separate fact from their role. In our model that is a mapping from an officer to the clusters that are his. A regional head carries a set of clusters. The national head carries none at all, because in this design carrying nothing is how you say "all of it". Role is a frozen set of permissions; scope is a subtree. Conflate the two and you will spend the next year writing special cases for the manager who also covers a patch himself, or the officer who was moved between regions in March.
Roll it up from where the viewer is standing
Here is the mistake that feels efficient and is poison. You compute the national total once, because you have all the data anyway, and then you filter that total down for each user. It seems thrifty. It is also wrong in a way that is very hard to see in a demo and very easy to see in a client's face six weeks later, because the number a field officer gets by subtraction almost never matches the number he gets by adding up his own distributors. Filtering a total after the fact and summing from the leaves are not the same arithmetic, and the difference is exactly the rounding, the exclusions and the deduplication you didn't think about.
The rule we settled on: scope first, against the viewer's position, then aggregate only what survives. The report is computed on the subtree, not extracted from a total. It costs you a query that is shaped by who is asking. It buys you numbers that reconcile, which is the entire point of building the thing.
Bookings, indents and dispatch are three facts, not one
Every version of this project has a well-meaning person who wants a single table called Order. Resist. A booking is a soft thing, an intention the distributor registers so the plan can see demand forming: draft, submitted, confirmed, or cancelled. An indent is a firm order with money against it, one product line, paid up front, then finance approves or rejects. Dispatch is a physical event, a truck leaving a warehouse, and in our case it belonged to a separate logistics platform that we spoke to over a deliberately narrow, pull-only contract so neither system could take the other down.
Collapse those into one "order" and you lose the ability to say the one thing distribution runs on: that a distributor indented forty tonnes, twenty-five shipped this week and fifteen are still pending. A single order row with a single status cannot hold a partial shipment. It can hold "shipped" or "not shipped", and reality is almost always somewhere in between. Keep them as three records that reference each other and a partial dispatch is just an ordinary state of the world, not an exception you have to bolt on later.
I will admit where we stopped short. In the roll-up, the dispatched and invoiced columns still render as a dash for whole branches of the tree, because we drew the boundary at the logistics platform and never fully pulled that data back across it. It is honest, in that a dash means "we do not know from here" rather than a confident zero, and a confident zero is the more dangerous of the two. But it is unfinished, and anyone reading the report has to know that a blank there is a gap in our model and not a gap in the business.
Targets fall, actuals climb
The same tree gets walked in two directions and it took us a while to say that out loud. Targets cascade down: a national number is divided across regions, regions across clusters, clusters onto officers and finally onto a plan row for a specific variety and grade. That is a top-down traversal, and it is mostly division. Actuals roll up: a confirmed booking is a leaf event, and it sums into the officer, the cluster, the region, the country. Bottom-up, and mostly addition. When someone asks "why is my achievement red" they are really asking to see a downward number and an upward number meeting at their own node, and if you have modelled only one direction you can compute the gap but you cannot explain it.
The total that does not equal its parts
Which brings me to the bug we actually shipped, the one that every hierarchy system produces at least once. A manager opens his dashboard, sees a headline figure, then expands the rows underneath, adds them up out of suspicion, and gets a different answer. Nothing erodes trust in a reporting tool faster. Ours was a "distributors covered" cell that counted distinct distributors at every node. Fine at a leaf. Wrong the instant a distributor bought two varieties through two different officers, because that distributor is one distributor to the regional head and one distributor to each officer, so the parent counted him once and his children counted him twice, and the column stopped adding up. Counts of distinct things are not additive up a tree. Quantities are. Tonnes and rupees sum; headcounts and "number of distributors" do not, and you either compute them from a deduplicated set at each level or you don't put them in a column that people will instinctively try to total.
# A person is not just a role. The role says which verbs you may use;
# the scope says which slice of the world those verbs may touch.
class FieldAssignment(models.Model):
officer = models.ForeignKey(User, related_name='assignments', on_delete=models.CASCADE)
cluster = models.ForeignKey(Cluster, on_delete=models.CASCADE) # the officer's patch
def visible_plans(user, roles):
qs = SalesPlan.objects.filter(is_active=True)
if 'national_head' in roles:
return qs # the whole tree
if 'regional_head' in roles:
clusters = user.assignments.values('cluster')
return qs.filter(location__cluster__in=clusters)
if 'field_officer' in roles:
return qs.filter(officer=user) # only your own leaves
return qs.none()
# Right order: scope first against the viewer's position, then roll up
# whatever survives. Never total everything and filter the total afterwards.
scoped = visible_plans(request.user, request.user.roles)
tree = build_tree(scoped, group_by=['state', 'cluster', 'district'])If you are about to build one of these, the shortest advice I have is this. Write down, before any code, which of your numbers are quantities and which are counts of distinct things, because they behave differently the moment you walk them up a tree, and the ones that misbehave are the ones a manager will check by hand on the first day.