Caching Calculations, Not Permissions

Both V9 and V9 Simplified cache. That is the surface similarity. The difference is what each one caches, and it turns out to be one of the sharpest design contrasts in the whole project. V9 cached yes/no permission answers and inherited a real revocation-staleness hazard for its trouble. V9 Simplified caches finished calculation results, keyed on the employee's permission list — and that one different choice makes the staleness hazard vanish. This post is why caching the right thing is better than caching the fast thing, and how the simplified recalculation path underneath it stays simple too.

Two caches, different contents

V9's PermissionCache stored authorization decisions: for this permission question, the answer is yes, valid for an hour. V9 Simplified's cache stores something further downstream:

CREATE TABLE PermissionCalculationCache (
    CacheId SERIAL PRIMARY KEY,
    EmployeeId INT,
    PermissionList TEXT,          -- the employee's permissions, as a key
    CachedResults JSONB,          -- the finished, permission-filtered calculation
    ExpiresAt TIMESTAMP,
    UNIQUE(EmployeeId, PermissionList)
);

It caches the output — the actual permission-filtered calculation results, as JSONB — not the authorization inputs. And critically, the cache key includes PermissionList: a representation of the employee's current permission set. The entry is keyed on who the employee is and what they can currently see. Functions GetCachedPermissionCalculation and CachePermissionCalculation read and write it; ClearExpiredPermissionCache reaps stale entries; GetPermissionCacheStats reports on it.

Why the key change fixes everything

Here is the elegant consequence, and it is the whole point. When an employee's permissions change, their PermissionList changes — and because the list is part of the cache key, the old cached entry is keyed on a permission list that no longer describes them. The next lookup builds a key from their new permissions, does not match the old entry, misses, and recomputes fresh. The stale entry is never served because it is never found — it is orphaned under a key nobody asks for anymore, and expires out on its own.

Contrast that with V9's permission cache. There, the cache stored HasPermission = true keyed on the permission question. Revoke the permission and the cached “true” is still keyed on the same question — the next lookup finds it and serves the stale yes, granting access that should be gone, until the TTL expires or someone remembers to call ClearPermissionCache. V9's cache required active invalidation on every permission change. V9 Simplified's cache is self-invalidating, because the thing that changed (the permission set) is baked into the key.

That is the difference between caching the fast thing and caching the right thing. Both caches speed up repeated work. But V9's choice created a coupling — every revocation must remember to flush — that is exactly the kind of easy-to-forget correctness obligation that produces security incidents. V9 Simplified's choice makes the correctness structural: a permission change cannot serve stale access, not because someone flushes the cache, but because the cache key stopped matching. Put what can change into the key, and staleness solves itself.

The simplified recalculation underneath

The cache sits on top of a recalculation path that got the same simplifying treatment as everything else. V9 Simplified keeps the dependency-driven recalculation idea from V8 but strips the permission-context-in-the-hash complexity from V9. The version hash goes back to a single argument:

-- V9 Complex: hash folds in the employee and their permission string
CalculateVersionHashV9(p_SalesReferenceId INT, p_EmployeeId INT DEFAULT NULL)
-- V9 Simplified: back to one argument, hashing the sale's state
CalculateVersionHashV9(p_SalesReferenceId INT)

The simplified hash reads the PermissionContext from SalesDim rather than folding a per-employee permission string into the fingerprint. GetAffectedMeasuresV9 returns the affected measures with an IsPermissionAware flag, and TR_IntelligentDimensionChangeV9 drives selective recalculation the same way V8 did — without V9's per-employee multiplication. The intelligence survives; the complexity that made it multiply by headcount does not.

The presentation stays simple too. AdvancedAnalyticsDashboardV9 is a three-part UNION — a sales summary, a permission distribution, and quality metrics — that gives you the operational picture in one view without the group-based dashboard machinery V9 needed.

Caching results has its own trade

I will not pretend caching results is strictly superior with no cost. Caching outputs means the cache is larger and less shareable than caching decisions — each employee-permission-list combination gets its own cached result, where V9's permission answers could be shared across everyone with the same clearance. So V9 Simplified's cache trades some memory efficiency and hit-rate breadth for its self-invalidating correctness. For a small deployment that is a fine trade: memory is cheap, the number of distinct employee-permission-list keys is small, and correctness-by-construction is worth far more than a marginally higher hit rate.

It is the same theme as the whole simplified version: give up an optimisation that mattered at scale in exchange for a system that is correct without maintenance and simple to reason about. V9 optimised the cache for throughput and inherited an invalidation obligation. V9 Simplified optimised it for correctness and accepted a bit more memory. For the deployment I would actually run, the second trade is the right one every time.

The better choice, generalised

The lesson outlives KC Star. When you reach for a cache, the instinctive question is “what is expensive to recompute?” — and V9 answered it correctly (permission checks are expensive) and cached them. The better question is “what changes, and how do I make the cache notice?” V9 Simplified answered that — permissions change, so put them in the key — and got a cache that cannot serve stale access. Same performance goal, a smarter choice about what to store, and a whole class of bug designed out of existence.

That is the last of the machinery. What remains of KC Star is not code but intention — the wishlist of engines I designed and never built, honestly labelled as such, which is next.