Who Gets to See the Numbers

Through five versions, KC Star answered what happened and who did it. V5 built the org chart — organisation, regions, offices, people — and valued every sale at every level of it. What it never asked was who is allowed to look. A regional manager should see their region; a sales rep should see their own sales, maybe filtered to one channel; nobody outside finance should see commissions. V6 is the version where the warehouse learns that not everyone gets every number. Role-based access control, permission-aware calculations, grants that expire.

Before I go further, a warning I will spend a whole post on: the V6 CHANGELOG describes a security system that was never built. It documents tables, functions and triggers that appear nowhere in the SQL. Everything in this series is grounded in the actual folder implementation, which is smaller, simpler, and real. When I describe V6, I am describing the code, not the changelog.

What V6 actually ships

The real RBAC model is three tables. That is the whole foundation, and its restraint is the point.

CREATE TABLE PermissionTypes (
    PermissionTypeId SERIAL PRIMARY KEY,
    PermissionName VARCHAR(100) UNIQUE,
    Description TEXT
);

Eight permission types are seeded: VIEW_SALES, VIEW_ANALYTICS, VIEW_ORGANIZATIONAL, VIEW_CROSS_DATABASE, VIEW_AGGREGATED, VIEW_DERIVED, VIEW_BASE, VIEW_ALL. They map onto the things V6 can show — the sales star, the analytical star, the org hierarchy, the cross-database facts — plus a catch-all VIEW_ALL.

CREATE TABLE HierarchyLevels (
    HierarchyLevelId SERIAL PRIMARY KEY,
    LevelName VARCHAR(50) UNIQUE,
    LevelOrder INT,          -- 1..5, the ordinal that drives everything
    Description TEXT
);

Five levels, ordered: Employee (1), Office (2), Region (3), Organization (4), Global (5). That LevelOrder integer is the engine of the whole permission model — access is decided by comparing ordinals, not by enumerating object grants. Part 2 is entirely about that decision.

CREATE TABLE EmployeePermissions (
    PermissionId SERIAL PRIMARY KEY,
    EmployeeId INT,
    PermissionTypeId INT REFERENCES PermissionTypes(PermissionTypeId),
    HierarchyLevel VARCHAR(50),
    GrantedBy INT,
    GrantedDate TIMESTAMP,
    ExpiryDate TIMESTAMP,
    IsActive BOOLEAN
);

The grant table ties an employee to a permission type at a hierarchy level, with a GrantedBy audit trail and — importantly — an ExpiryDate. Permissions are not forever; they can be time-boxed, which is exactly what you want for temporary access.

What V6 inherits, and where security sits

V6 keeps everything the previous versions built and adds a gate in front of it. The three stars of V5 — sales, org, and analytical — are all still here, along with the governance columns from V4 and the cross-database bridge. What changes is that reading them now goes through a permission check. This is authorization, not authentication: V6 assumes an employee has already been identified (it never handles passwords or logins, whatever the changelog claims) and concerns itself only with what an identified employee may see.

That framing matters because it keeps V6 small. A full identity system — credentials, sessions, lockout — would dwarf the warehouse. By scoping strictly to authorization, V6 adds three tables and four functions and calls it done. The permission model bolts onto the side of the existing stars rather than rebuilding them, which is exactly why an ordinal-based design is enough: it is a filter on reads, not a rewrite of the data.

Granting, and the functions that read grants

Grants are made through a procedure, GrantPermission, so granting is auditable and consistent:

CALL GrantPermission(2, 'VIEW_ANALYTICS', 'Office', 1, '2024-12-31 23:59:59');
--                    emp  permission        level   by   expires

The GrantedBy argument (here, employee 1) records who authorised the grant, and the ExpiryDate bounds how long it lasts. Both land in EmployeePermissions, so the grant table doubles as an audit trail: you can always ask who gave whom access to what, and until when. A RBACPermissionSummary view rolls those grants up for review, which is the query a security audit actually runs — not “what can employee 2 do” but “show me every active grant and who is responsible for it.”

And three functions read the grants back:

• GetEmployeePermissions(p_EmployeeId) returns an employee's active permissions, joining PermissionTypes and HierarchyLevels and filtering out anything inactive or past its ExpiryDate.
• CheckPermission(p_EmployeeId, p_PermissionName, p_HierarchyLevel) returns a boolean — may this employee do this thing at this level? This is the workhorse, and its ordinal-comparison logic is part 2.
• GetFilteredAnalyticalData(p_EmployeeId, p_HierarchyLevel) returns only the analytical rows an employee is cleared to see — the aggregate that censors itself, which is part 3.

Permission-aware measures, mostly aspirational

V6 also declares two new measures, 19 and 20 — PermissionBasedTotalSale and PermissionBasedNetRevenue. Their CalculationFormula is worth quoting because it is not real SQL:

'SUM(TotalSale) WITH PERMISSION FILTER'

There is no WITH PERMISSION FILTER clause in PostgreSQL. These measures are declared but their filtering happens procedurally, in GetFilteredAnalyticalData and in persona-specific CASE expressions, not through the formula string. It is the same formula-as-specification pattern from V2 — the formula records intent, the code does the work — but here the gap is wider, because the “formula” is frankly pseudocode. I would rather show that than pretend measures 19 and 20 are anything other than a labelled intention that the real filtering functions fulfil.

Where the series goes

1. Who gets to see the numbers — this post.
2. Hierarchy as an ordinal — how CheckPermission decides access by comparing a single integer, and what that buys and costs.
3. The aggregate that censors itself — GetFilteredAnalyticalData, rows that self-select by clearance, and the persona demos.
4. The changelog that describes a different system — the V6 CHANGELOG documents a security model that was never built, and how I verified that.
5. Backfilling the facts the pipeline skipped — the fix scripts that repair a half-run pipeline, and the hand-off to V7.

V6 keeps everything V5 built and adds the gate. Access is decided by an ordinal, and that ordinal is where we go next.