Multi-Tenancy, Zero-Trust Access & Schema History
You'll learn to
- -Design row-level security for a multi-tenant schema so tenants are isolated at the data layer, not just the application layer
- -Model zero-trust access control and a git-like version-controlled schema history in the same design
This chapter combines three concerns that show up together constantly in real large-scale systems: keeping multiple tenants' data safely isolated in one shared schema, verifying every access explicitly rather than trusting network location, and tracking a full version history of changing records - three different problems, all solved with variations on techniques already covered earlier in this course.
Multi-Tenancy: Shared Schema, Isolated Data
A multi-tenant system (Machine City hosting many independent factions' data) can isolate tenants with entirely separate databases per tenant (maximum isolation, high operational overhead at scale) or a single shared schema with a tenant_id column on every table (efficient at scale, but isolation now depends entirely on every single query correctly filtering by tenant_id - a mistake here is a serious data leak between tenants).
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);Row-Level Security moves tenant isolation from "every developer must remember to filter by tenant_id in every query, forever" to "the database enforces it automatically, even if a query forgets" - the same enforce-it-in-the-schema-not-the-application-code principle from the constraints chapter, applied here to tenant boundaries instead of business rules.
Zero-Trust Access Control
Zero-trust means every access request is independently verified - no session is trusted just because a user authenticated once, or because a request originated from inside a "trusted" network. At the schema level, this means every sensitive read or write should be checked against current session/permission state, and every access attempt (successful or denied) is worth logging for audit purposes.
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
);Git-Like Version-Controlled Schema History
A version-control-style schema (The Source Code) needs every change to a record preserved as an immutable version, with the ability to reconstruct any past version and see exactly what changed between two versions - a direct extension of the event-sourcing pattern from the Advanced Transactional module, applied to arbitrary structured content instead of financial transactions.
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)Notice that content_versions is a self-referencing structure, like the friend-graph chapter, but forming a chain (or tree, if branching is supported) instead of a general graph - each version has at most one parent, which is exactly what makes reconstructing "history" a simple walk rather than a general 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.
Design Zion Access Control, The Source Code, and Machine City in the LLD Lab's Matrix act.