NULL Means You Can't See It

There is a decision buried in V9's permission-aware calculations that looks like a detail and is actually load-bearing: when the system denies you a measure, what value does it return? The wrong answer — zero — is the tempting one, and it would quietly corrupt every aggregate downstream. V9's answer is NULL, and this closing V9 post is about why the difference between NULL and zero is the difference between honest redaction and silent data corruption. It is the subtlest correctness call in the permission model, and getting it right is what makes permission-aware analytics analytically sound rather than just visually filtered.

Two kinds of “you can't see this”

When RecalculateSpecificMeasuresV9 computes a measure for a viewer who lacks permission, it does not use one sentinel for denial — it uses two, and which one depends on the measure:

-- inside RecalculateSpecificMeasuresV9, on a permission failure:
IF NOT has_permission THEN
    IF measure.IsPermissionFiltered THEN
        v_value := NULL;    -- redacted: "you may not see this number"
    ELSE
        v_value := 0;       -- genuinely zero for this viewer's scope
    END IF;
END IF;

The distinction is real. A measure flagged IsPermissionFiltered is one whose value is hidden from unauthorized viewers — the number exists, you just are not cleared for it, so it comes back NULL. A measure that is not permission-filtered but falls outside the viewer's scope genuinely is zero for them — there is no hidden value, their slice really contains nothing. “I am not allowed to tell you” and “the answer for you is nothing” are different statements, and V9 encodes them as different values.

That is the crux: NULL is redaction, zero is emptiness. Conflating them is the mistake, and most naive permission filtering makes it.

Why zero would corrupt everything

Here is the concrete danger, and it is the whole reason this matters. Suppose a viewer is denied CommissionTotal. If the system returned 0 for the denial, and that value flowed into any aggregate — an average commission across employees, a company total, a rollup — the zero would be counted. The average would be dragged down by a commission that is not actually zero, just hidden. The total would be understated. And nobody would see an error, because a zero is a perfectly valid commission value. The redaction would silently falsify every calculation built on top of it.

NULL behaves completely differently in aggregates, and that behaviour is exactly what you want. AVG ignores NULLs — it averages over the values that exist, not padding the denominator with redactions. SUM skips them. A NULL says “no data here to include,” which is the honest meaning of a redacted value: it should not participate in aggregates, because including it would be inventing a number. Return NULL and the downstream average of visible commissions is correct; return zero and it is corrupt. The choice of sentinel is the difference between a permission system that filters presentation and one that keeps the analytics sound.

Recording the redaction

V9 does not just return the right sentinel; it records why. Facts carry permission metadata so a redaction is self-documenting:

• IsPermissionFiltered — was this value redacted for permission reasons?
• RequiresPermission and PermissionType — what clearance does it need? The SaleType dimension, for instance, is marked PermissionType = 'SaleTypeAccess'.
• PermissionContext JSONB — the full context under which the value was (or was not) computed.

So a NULL in a V9 result is not an ambiguous absence — you can inspect the row and see that it is a deliberate redaction, what permission it needed, and the context. That is important, because NULL is otherwise overloaded: it can mean “not computed,” “not applicable,” or “missing.” The IsPermissionFiltered flag disambiguates — this NULL specifically means “withheld for permission reasons,” not “we failed to compute it.”

SELECT Id, SaleType, TotalSale, SaleTypeTotal, RegionTotal, CommissionTotal,
       IsPermissionFiltered, PermissionContext
FROM CurrentSalesStateV9
ORDER BY Id;

Run that as different viewers and the same columns come back populated or NULL depending on clearance, with IsPermissionFiltered and PermissionContext explaining every blank. The redaction is visible, attributed, and queryable.

Permission-aware time travel

The NULL-means-redacted semantics extend to history. GetSalesStateAtTimeByPermissionGroup reconstructs a sale as it stood at a past instant and as a given permission group is allowed to see it — so a historical query redacts exactly the measures the viewer could not see, with the same NULL-for-hidden discipline. Time travel has been in KC Star since V1; V9 makes it permission-aware, so you can ask “what did this sale look like last March, to a store rep” and get a historically-accurate, permission-correct answer, redactions and all.

The subtlest correctness call

I want to end V9 on this because it is the version's quietest good decision. Everything else in V9 is about ambition — fusing two engines, scaling with groups, caching for speed — and that ambition is what makes V9 the most complex version. But this one small choice, NULL over zero for redaction, is about correctness, and it is the choice that determines whether permission-aware analytics can be trusted at all. A permission system that returns zero for denied values produces confidently wrong aggregates; one that returns NULL produces honest ones. The complexity is debatable; this is not.

It is also, quietly, the strongest thing V9 carries forward. When V9 Simplified throws out the groups, the cache, and the per-employee machinery, it keeps this — because the NULL-vs-zero discipline is not complexity, it is correctness, and simplification should delete cleverness, not correctness. Telling those two apart is the entire art of the next version.

That is where the series goes. V9 is the pinnacle of accumulated complexity. V9 Simplified is the reckoning — the version that asks which of all this ambition was actually worth its weight, and deletes the rest.