Skip to content

REFORMATION BRIEF R-00 — THE RAILS

Authentication & Tenancy Constitution

UCCA Platform · 18 March 2026

This brief is the constitutional document for the UCCA platform. Every surface, every client, every credential issued by this system inherits from what is defined here. Build it once. Build it right.

SURFACE: engine-db (D1 schema) + ucca-api (Python backend) + ucca-ops (ops console) DO NOT TOUCH: ucca-site, ucca-ir, ucca-keys, rtopacks-site, ucca-track Cloudflare Account: e5a9830215a8d88961dc6c80a8c7442a

→ ALEX

1. Database Schema — Lock This First

Execute against engine-db (0efa8970-0053-4623-8436-4e877af10887). This is the canonical identity schema. Nothing touches users or tenancy without going through this.

-- Canonical identity store. All users L1–L4 in one table.
CREATE TABLE IF NOT EXISTS users (
  id            TEXT PRIMARY KEY,           -- UUID v4
  email         TEXT UNIQUE NOT NULL,
  auth0_sub     TEXT UNIQUE,                -- NULL for L1/L2 (CF Access only)
  display_name  TEXT,
  avatar_url    TEXT,
  created_at    INTEGER NOT NULL DEFAULT (unixepoch()),
  updated_at    INTEGER NOT NULL DEFAULT (unixepoch())
);

-- Every L3 client (RTO, TAFE, etc.) is a tenant.
CREATE TABLE IF NOT EXISTS tenants (
  id            TEXT PRIMARY KEY,           -- UUID v4
  slug          TEXT UNIQUE NOT NULL,       -- e.g. "rtopacks", "tafe-nsw"
  display_name  TEXT NOT NULL,
  config_json   TEXT NOT NULL DEFAULT '{}', -- branding, feature flags
  created_at    INTEGER NOT NULL DEFAULT (unixepoch()),
  suspended_at  INTEGER                     -- NULL = active
);

-- The join table that governs all access.
-- L1/L2: tenant_id IS NULL (global roles).
-- L3/L4: tenant_id IS NOT NULL (scoped roles).
CREATE TABLE IF NOT EXISTS user_tenant_roles (
  id            TEXT PRIMARY KEY,
  user_id       TEXT NOT NULL REFERENCES users(id),
  tenant_id     TEXT REFERENCES tenants(id), -- NULL for L1/L2
  role          TEXT NOT NULL CHECK (role IN ('L1','L2','L3','L4')),
  granted_by    TEXT REFERENCES users(id),
  granted_at    INTEGER NOT NULL DEFAULT (unixepoch()),
  revoked_at    INTEGER                      -- NULL = active
);

-- Immutable audit trail. Written on every state-changing action.
CREATE TABLE IF NOT EXISTS audit_log (
  id              TEXT PRIMARY KEY,
  actor_id        TEXT NOT NULL,             -- who took the action
  impersonating_id TEXT,                     -- set when L1/L2 drops into L3/L4
  tenant_id       TEXT,
  action          TEXT NOT NULL,             -- e.g. "user.create", "role.grant"
  target_table    TEXT,
  target_id       TEXT,
  payload_json    TEXT,
  ip              TEXT,
  user_agent      TEXT,
  ts              INTEGER NOT NULL DEFAULT (unixepoch())
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_utr_user     ON user_tenant_roles(user_id);
CREATE INDEX IF NOT EXISTS idx_utr_tenant   ON user_tenant_roles(tenant_id);
CREATE INDEX IF NOT EXISTS idx_audit_actor  ON audit_log(actor_id);
CREATE INDEX IF NOT EXISTS idx_audit_tenant ON audit_log(tenant_id);
CREATE INDEX IF NOT EXISTS idx_audit_ts     ON audit_log(ts);

2. Python Middleware — JWT Validation & Tenant Scoping

Add to ucca-api. This middleware runs on every request.

Two rails, validated separately:

Rail 1 — Internal (L1/L2): Request arrives at internal subdomain. Middleware checks Cf-Access-Jwt-Assertion header. Validates against Cloudflare public keys. Extracts email claim. Looks up user in users table. Checks user_tenant_roles for global L1 or L2 role (tenant_id IS NULL). Rejects if not found.

Rail 2 — External (L3/L4): Request arrives with Authorization: Bearer <token>. Middleware validates JWT against Auth0 JWKS endpoint. Extracts sub, tenant_id, role claims. Enforces: every L3/L4 query MUST include WHERE tenant_id = :tenant_id. Middleware injects tenant_id — the route handler never trusts a tenant_id from the request body.

# Pseudocode — implement as FastAPI dependency

async def get_current_user(request: Request) -> AuthContext:
    # Rail 1: Internal
    cf_jwt = request.headers.get("Cf-Access-Jwt-Assertion")
    if cf_jwt:
        claims = verify_cf_jwt(cf_jwt)
        user = db.get_user_by_email(claims["email"])
        role = db.get_global_role(user.id)  # L1 or L2, tenant_id IS NULL
        return AuthContext(user=user, role=role, tenant_id=None, impersonating=None)

    # Rail 2: External
    bearer = request.headers.get("Authorization", "").removeprefix("Bearer ")
    if bearer:
        claims = verify_auth0_jwt(bearer)
        # Impersonation: actor claim present = L1/L2 dropping into L3/L4
        impersonating = claims.get("actor")
        tenant_id = claims["tenant_id"]
        user = db.get_user_by_auth0_sub(claims["sub"])
        role = db.get_scoped_role(user.id, tenant_id)
        return AuthContext(user=user, role=role, tenant_id=tenant_id, impersonating=impersonating)

    raise HTTPException(401)

Audit log write — every state-changing action:

def write_audit(ctx: AuthContext, action: str, target_table: str, target_id: str, payload: dict, request: Request):
    db.insert("audit_log", {
        "id": uuid4(),
        "actor_id": ctx.user.id,
        "impersonating_id": ctx.impersonating,
        "tenant_id": ctx.tenant_id,
        "action": action,
        "target_table": target_table,
        "target_id": target_id,
        "payload_json": json.dumps(payload),
        "ip": request.client.host,
        "user_agent": request.headers.get("user-agent"),
        "ts": int(time.time())
    })

3. Impersonation Endpoint

L1/L2 drops into any L3/L4 user's session without credentials.

# POST /internal/impersonate
# Requires: valid L1/L2 CF Access JWT
# Body: { "target_user_id": "uuid", "tenant_id": "uuid" }

async def impersonate(body: ImpersonateRequest, ctx: AuthContext = Depends(get_current_user)):
    assert ctx.role in ("L1", "L2"), "Forbidden"
    target = db.get_user(body.target_user_id)
    scoped_role = db.get_scoped_role(target.id, body.tenant_id)

    # Mint a time-limited JWT (15 min) with actor claim
    token = auth0.mint_token(
        sub=target.auth0_sub,
        tenant_id=body.tenant_id,
        role=scoped_role.role,
        actor=ctx.user.id,       # ← immutable impersonation marker
        expires_in=900
    )

    write_audit(ctx, "impersonation.start", "users", target.id, {
        "tenant_id": body.tenant_id
    }, request)

    return {"token": token, "expires_in": 900}

4. Auth0 Enterprise — Provisioning Checklist

Provision in Auth0 dashboard. Do not start app builds until this is live.

  • Create Auth0 tenant: ucca.au.auth0.com
  • Enable Organizations feature (B2B multi-tenancy)
  • Create application: UCCA Platform (L3/L4)
  • Enable Passkeys (FIDO2/WebAuthn) as primary auth method
  • Configure Magic Link as fallback (Twilio SendGrid delivery)
  • Set custom claims action: inject tenant_id and role into JWT on login
  • Configure Australian data residency
  • JWKS endpoint: https://ucca.au.auth0.com/.well-known/jwks.json
  • JIT Provisioning action: on first SSO callback, upsert users table before session

5. Ops Console — Account Switcher & User Database

Add to ucca-ops (ops.ucca.online). This activates the account switcher.

New world block: IDENTITY & TENANCY — place at Level 1 in ops nav.

Pages to scaffold (stubs acceptable for now, real data required):

/w/identity/users/ — All users table. Columns: ID, email, display_name, role, tenant, created_at, status. Search + filter by role and tenant. Click row → user detail.

/w/identity/tenants/ — All tenants table. Columns: slug, display_name, user_count, created_at, suspended_at. Click row → tenant detail with user list.

/w/identity/roles/ — user_tenant_roles table. Filter by user or tenant. Show granted_by, granted_at, revoked_at.

/w/identity/audit/ — audit_log table. Filter by actor, tenant, action, date range. Show impersonating_id column — highlight rows where impersonation was active.

/w/identity/impersonate/ — Search for any L3/L4 user. Select tenant context. Click "Drop In" → calls /internal/impersonate → opens scoped session in new tab.

Account switcher (persistent in ops header): Dropdown showing current context. Options: [L1 — Global], then all active tenants. Selecting a tenant sets the active tenant_id context for the ops session. Visually distinct when impersonating — amber banner: IMPERSONATING: [user email] in [tenant slug].

6. Seed Data

After schema is applied, seed engine-db with:

-- Tim as L1
INSERT INTO users (id, email, display_name) VALUES 
  ('00000000-0000-0000-0000-000000000001', 'tim@ucca.online', 'Tim Rignold');

INSERT INTO user_tenant_roles (id, user_id, tenant_id, role, granted_by) VALUES
  ('00000000-0000-0000-0000-000000000010', '00000000-0000-0000-0000-000000000001', NULL, 'L1', '00000000-0000-0000-0000-000000000001');

-- RTOpacks as first tenant
INSERT INTO tenants (id, slug, display_name) VALUES
  ('00000000-0000-0000-0000-000000000100', 'rtopacks', 'RTOpacks');

OPS STUB TASK

Add to ops console task queue:

R-00 · THE RAILS
Schema applied to engine-db ✓
Python middleware live ✓
Auth0 tenant provisioned ✓
Impersonation endpoint live ✓
Ops identity world live ✓
Seed data applied ✓

→ TIM

What this brief does:

R-00 builds the foundation that every future surface sits on. It does four things:

  1. Creates the user and tenant database — one canonical table for all users L1 through L4, one table for all client organisations (RTOs, TAFEs), and a join table that controls who can see what. Every RTO client that ever joins the platform gets a row in tenants. Every learner gets a row in users. No exceptions.

  2. Locks the two security rails — your internal ops (L1/L2) stays behind Cloudflare Access as it is now. Everything external (L3 RTO admins, L4 learners) goes through Auth0 Enterprise with Passkeys as the primary login. These two rails never cross.

  3. Builds the impersonation engine — you can drop into any learner's session from ops, see exactly what they see, debug their problem, and leave a clean audit trail showing it was you, not them. This is how good SaaS platforms handle support.

  4. Activates the account switcher in ops — the Identity & Tenancy world in ops becomes the live user database and tenant manager. Every user on the platform is visible here. Every tenant. Every role grant. Every impersonation event. If it doesn't exist in ops, it doesn't exist.

Why this happens before anything else:

The first RTO that logs into their dashboard is an L3 tenant. Their first learner is an L4 user. If the rails aren't in place before that happens, you're rebuilding on a live product. This brief makes sure you never have to do that.

Auth0 cost note: Auth0 Enterprise pricing is usage-based. At zero paying customers the cost is negligible. The budget conversation happens when you have RTOs onboarded — at which point the cost is a line item against revenue, not a startup burn.

REFORMATION BRIEF R-00 · UCCA Inc · 18 March 2026 "The rails are the product."