Skip to content
LLD Learn/Planet-Scale Schema Design
Browsing as a guest. Sign in to save your progress and earn XP as you complete chapters.

Multi-Tenancy, Zero-Trust Access & Schema History

5 min read

You'll learn to

  • -Isolate tenants in a shared table with Row-Level Security, and see what goes wrong without it
  • -Explain zero trust and why an access log records denials as well as grants
  • -Store a version history as a chain of rows that each point at their parent

This chapter joins three concerns that show up together in real large systems: keeping many tenants' data safely apart in one shared schema, checking every access explicitly instead of trusting network location, and keeping a full version history of changing records. All three reuse techniques from earlier in the course.

An Analogy: The Apartment Building

An apartment building shares one structure, one set of pipes and one front door, but every flat has its own locked door. A tenant is one customer or group whose data must stay private from the others. You could build a separate building per tenant, which is the most private and the most expensive. Or you could share the building and make sure every door is secured, which is cheaper but only safe if no door is ever left open by mistake.

Multi-Tenancy: Shared Schema, Isolated Data

The two options for a multi-tenant system are a separate database per tenant, with the strongest isolation and a high operating cost at scale, or one shared schema with a tenant_id column on every table, which is efficient at scale. The shared option has a danger. Isolation now depends on every single query remembering to filter by tenant_id, and one mistake leaks one tenant's data to another.

Run the demo to see the leak and its fix. Three rows belong to two tenants. A query that forgets the filter sees everyone's rows. The same careless query run through a tenant-aware session sees only the right ones, because the session applies the policy itself.

Press Run: the forgetful query leaks, until the database applies the filter

In a real database this is Row-Level Security: a policy attached to the table so the database filters every query by tenant automatically.

Tenant isolation enforced at the database level via Row-Level Security
CREATE TABLE faction_resources (
    id INTEGER PRIMARY KEY,
    tenant_id INTEGER NOT NULL,
    resource_name TEXT NOT NULL,
    quantity INTEGER NOT NULL
);

-- Row-Level Security: the database itself filters every query by tenant,
-- rather than trusting every application query to remember WHERE tenant_id = ...
ALTER TABLE faction_resources ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON faction_resources
    USING (tenant_id = current_setting('app.current_tenant_id')::INTEGER);

That moves tenant isolation from "every developer must remember to filter by tenant_id in every query, forever" to "the database enforces it even if a query forgets". It is the same principle as the constraints chapter: enforce it in the schema, not in application code.

Zero-Trust Access Control

Zero trust means every access request is checked on its own. A session is not trusted just because the user logged in once, or because the request came from inside a trusted network. At the schema level, check each sensitive read or write against the current session and permission state, and log every attempt, whether it was allowed or refused.

An access log capturing every attempt, not just successful ones
CREATE TABLE access_log (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    resource_id INTEGER NOT NULL,
    action TEXT NOT NULL,               -- 'read', 'write', 'delete'
    was_granted BOOLEAN NOT NULL,       -- logging denials is as important as successes
    session_verified_at TIMESTAMP NOT NULL,
    logged_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Logging denials matters as much as logging grants. A pattern of refused requests, such as one user trying a hundred resources they may not open, is exactly how misuse is spotted.

Git-Like Version-Controlled History

A version-controlled record keeps every change as an immutable version, so any past version can be rebuilt and the difference between two versions shown. It extends the event-sourcing pattern from the transactional module to arbitrary content. Each version points at its parent, like a commit in git. The demo commits three versions and then finds the current one and walks its history.

Content versions, each pointing at its parent - just like a commit graph
CREATE TABLE content_versions (
    id INTEGER PRIMARY KEY,
    document_id INTEGER NOT NULL,
    parent_version_id INTEGER REFERENCES content_versions(id),  -- NULL for the first version
    content TEXT NOT NULL,
    author_id INTEGER NOT NULL,
    committed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- the current version of a document is whichever version has no
-- child pointing back at it as parent_version_id (the tip of the chain)
Press Run: the tip of the chain is the current version, and history is a walk back through parents

This is a self-referencing structure, like the friend graph, but each version has at most one parent, so it forms a chain (or a tree if branching is supported) and not a general graph. That is why rebuilding history is a simple walk instead of a graph traversal.

Interview Signal is part of Pro

See a real weak answer next to a real strong one for this exact topic.

Quiz is part of Pro

Test what you just read with a short quiz, and bank the XP.

Ready to Build This?

Design Zion Access Control, The Source Code, and Machine City in the LLD Lab's Matrix act.

Discussion0

Join the Discussion

Sign in to leave comments, reply to others, or like insights.

Sign In to ScaleDojo

No comments yet. Be the first to start the thread!