From Per-Employee to Per-Group

Part 1 ended on the problem that defines V9: fuse per-employee permissions with per-change recalculation and you multiply your work by your headcount. A thousand employees, a thousand times the recalculation. This post is the fix, and it is the most important design decision in the version — collapsing employees into a handful of permission groups, so the system recomputes per-group instead of per-person. V9 ships both the naive version and the optimised one, side by side, and the gap between them is the whole lesson.

The naive baseline: files 01 and 02

The first implementation, in the folder's files 01 and 02, is the honest, obvious fusion. Every calculation is keyed on an individual employee. RecalculateSpecificMeasuresV9, GetAffectedMeasuresV9, the permission-folding hash — all take a p_EmployeeId and compute that employee's permission-aware result.

It is correct, and it is clear, and it does not scale. Because clearance is a property of a person, and the hash folds the person in, each employee produces a distinct computed state for the same underlying data. Recomputing a measure “for the warehouse” is no longer meaningful — you recompute it for an employee, and there are as many recomputations as there are employees who might view it. The work is O(employees × affected measures). For a demo with five people, fine. For an enterprise with thousands, fatal.

I kept this implementation deliberately, and that matters. It is not dead code — it is the baseline, the naive thing the optimisation is defined against. Keeping it makes the improvement legible: you can read files 01/02 to understand the idea, then read 03/04 to understand how to make the idea affordable. That is the V5 two-implementations pattern done right — on purpose, documented, with the naive one retained as pedagogy rather than left to drift.

The insight: people cluster

The optimisation rests on an observation about how permissions actually distribute. In a real organisation, a thousand employees do not have a thousand distinct permission sets. They have a handful of roles — sales manager, sales rep, finance user, read-only — and everyone in a role has the same clearance. Permissions cluster hard.

So instead of keying calculations on the employee, key them on the permission group. If two hundred employees are all SalesRep, they all see exactly the same permission-filtered numbers, so you compute the SalesRep view once and every rep reads it. The recalculation cost drops from O(employees) to O(groups) — from thousands to seven. That is the entire optimisation, and it is the kind of change that only works because it matches the shape of the real world: authorization is role-shaped, so make the computation role-shaped too.

The optimised implementation: files 03 and 04

Files 03 and 04 build the group machinery. Three tables model the grouping:

• PermissionGroups — the roles themselves, seven of them, each with a priority: SalesManager (100), RegionalManager (90), FinanceUser (85), SalesRep (80), StoreSalesRep / OnlineSalesRep (70), ReadOnlyUser (50).
• PermissionGroupMappings — which permissions each group has, with JSONB FilterConditions for channel scoping (StoreSalesRep carries '{"sale_type":"Store"}', OnlineSalesRep carries '{"sale_type":"Online"}').
• EmployeePermissionGroups — which employees belong to which groups.

And the functions mirror V8's, but keyed on groups: GetAffectedMeasuresByPermissionGroup, RecalculateMeasuresByPermissionGroup, BatchRecalculateByPermissionGroups, CreateAggregatedFactsByPermissionGroup. The usage examples exercise this path exclusively — the naive per-employee path exists to be understood, the group path exists to be used:

-- affected measures for a SaleType change, per group, not per person
SELECT * FROM GetAffectedMeasuresByPermissionGroup(7, 1);  -- SalesManager (full access)
SELECT * FROM GetAffectedMeasuresByPermissionGroup(7, 4);  -- StoreSalesRep (store only)

-- recalculate once for the SalesRep group; every rep benefits
SELECT * FROM RecalculateMeasuresByPermissionGroup(1, 7, 2, 'Permission Test - SalesRep Group');

GetAffectedMeasuresByPermissionGroup(7, 4) asks “when SaleType (dim 7) changes, which measures does the StoreSalesRep group need recomputed?” — and the answer respects that group's store-only filter. One call covers every store rep.

The claimed payoff

V9 puts a number on it: “90%+ reduction in permission calculations.” The mechanism behind that number is exactly the clustering — collapse a large employee count into a small group count and you eliminate the redundant per-person recomputation of identical results. I treat the specific percentage with the same caution I apply to all the claims, but the direction is unarguable: if a hundred people share a role, computing their shared view once instead of a hundred times is a real and large saving, and it grows with headcount.

The honest tension

Here is what the group optimisation costs, because nothing is free. The per-employee model was simple: an employee, their permissions, their view. The per-group model adds three tables, a group-assignment step, and a layer of indirection — you no longer ask “what can this person see,” you ask “what group is this person in, and what can that group see.” That indirection is the price of scalability, and it is real complexity.

And it is complexity stacked on complexity. V9 already fused two engines; the group layer is a third thing on top. The version now has a naive path and an optimised path and a cache on top of that, all for permission-aware recalculation. Each layer is individually justified — the fusion is the feature, the groups make it scale, the cache makes it fast — and collectively they are a lot of moving parts for “different people see different numbers, and we only recompute what changed.”

That accumulation is precisely what V9 Simplified reacts against. Where V9 answers “make the clever thing scale” by adding group machinery, V9 Simplified asks whether the clever thing was worth its complexity at all — and deletes the groups entirely in favour of direct permissions. The two are deliberate alternatives, and the choice between them is the real payoff of the whole permission arc.

Before that reckoning, two more pieces of V9's machinery. The group model still has to check permissions constantly, and checking is expensive — which is why V9 caches the yes/no answers. That cache is next.