Two Engines, One Version

V9 is the version where two of KC Star's biggest ideas collide. V6 made calculations permission-aware — different people see different numbers. V8 made recalculation intelligent — change something, recompute only what it affects. V9 fuses them: intelligent recalculation that is also permission-aware, so the system recomputes only the affected measures and only for the people allowed to see them. The README calls it “the pinnacle of the inverted star schema system.” I would call it the version where the design's ambition and its complexity finally have to be reconciled — and this series is that reckoning.

What fusing the two engines means

Individually, the two engines are clean. Together, they multiply. V8's engine asks “which measures does this change affect?” V6's engine asks “which measures may this person see?” V9 has to answer both at once, for every change, and the interaction is where the complexity lives.

The clearest place to see the fusion is the version hash — the fingerprint that has grown at every governance boundary. In V9 it folds in the employee and their permission context:

CREATE OR REPLACE FUNCTION CalculateVersionHashV9(
    p_SalesReferenceId INT,
    p_EmployeeId INT DEFAULT NULL
)
RETURNS BYTEA AS $$
-- hashes: dimension values + SaleType + avg DataQualityScore
--         + the employee id + a pipe-joined string of their permissions
$$ LANGUAGE plpgsql;

Read what that means. A sale's identity is no longer just its data — it is its data as seen by a particular employee with particular permissions. The same sale can have different hashes for different viewers, because a viewer's clearance is now part of what defines the sale's computed state. That is the fusion made concrete: permissions do not filter the result after the fact, they are baked into the change-detection identity itself. It is elegant and, I will argue across this series, a little too clever.

The permission-aware recalculation functions

Every V8 function grew a permission-aware twin. GetAffectedMeasuresV9(p_DimTypeId, p_EmployeeId) returns the affected measures plus whether the employee has permission for each. RecalculateSpecificMeasuresV9 recomputes measures but, when the employee lacks permission, writes NULL for permission-filtered measures and 0 otherwise — a distinction that gets its own post. And the orchestrator ProcessIntelligentDimensionChangeV9 returns a richer result than V8's: MeasuresAffected, MeasuresRecalculated, MeasuresPermissionFiltered, and total execution time.

That last breakdown is the fusion in a single return value. A change now has three counts: how many measures it affected (the V8 question), how many were actually recalculated, and how many were permission-filtered out (the V6 question). The system is doing selective recalculation and permission filtering in one pass, and reporting on both.

The complexity problem, foreshadowed

Here is where I have to be honest about what fusion cost, because it is the through-line of the entire V9 story. The V6 permission model was per-employee: clearance is a property of a person. V8's recalculation was per-change. Fuse them naively and you get per-employee recalculation — every calculation potentially recomputed for every employee, because each employee's permission context produces a different hash and thus a different computed state.

That does not scale. Ten employees means ten times the recalculation work; a thousand means a thousand times. The fusion, done the obvious way, multiplies your recalculation cost by your headcount. V9 knows this, which is why the folder contains two implementations — a naive per-employee one (files 01 and 02) that demonstrates the idea, and an optimised per-permission-group one (files 03 and 04) that makes it tractable. Part 2 is that split, and it is the most important design decision in the version.

The existence of two implementations for one idea should sound familiar — it is the V5 pattern again, but this time deliberate and documented rather than drifted. V9 keeps the naive baseline on purpose, as the thing the optimisation improves on, which is a healthier version of the same shape.

What V9 stores

The schema grows to hold all this. Permission tables arrive in force — PermissionTypes gains a PermissionGroup and Cacheable flag, and new tables PermissionGroups, PermissionGroupMappings (with JSONB FilterConditions), EmployeePermissionGroups, and a PermissionCache appear, alongside the legacy EmployeePermissions kept “for backward compatibility.” Fact and dimension rows gain RequiresPermission, IsPermissionFiltered, and a PermissionContext JSONB column. The seed data defines seven permission groups from SalesManager (priority 100) down to ReadOnlyUser (50), with the channel-specific groups carrying filter conditions like '{"sale_type":"Store"}'.

Every one of those additions serves the fusion, and every one adds surface area. V9 is the most capable version and the most complex, and those are the same sentence.

Where the series goes

1. Two engines, one version — this post.
2. From per-employee to per-group — the naive and optimised implementations, and why groups make the fusion tractable.
3. A cache for yes and no — PermissionCache, its TTL, and caching authorization decisions.
4. NULL means you can't see it — the semantics of denial, and why NULL and 0 mean different things.

V9 is the pinnacle and the warning at once. The next post is where the warning gets specific: fuse two per-thing engines and you multiply, unless you collapse the things into groups.