SQL Expert Lab — Multi-Tenant Database Architecture (Expert)

🖥️

SQL Terminal

📚

Reference

🗂️

Schema

⭐⭐⭐⭐⭐ EXPERT OBJECTIVES

○Audit: find all tables missing tenant_id FK

○Enable Row Level Security on all tenant tables

○Create RLS policies using current_setting('app.tenant_id')

○Add tenant_id prefix to all indexes (composite)

○Build tenant_purge() stored procedure (GDPR)

○Test RLS: verify cross-tenant data isolation

○Design tenant sharding key for horizontal scale

○Produce Architecture Decision Record (ADR)

0 / 8 complete

EXPERT SQL TERMINAL — MULTI-TENANT DATABASE ARCHITECTURE (EXPERT)

PostgreSQL 16 — Expert Lab Environment loaded ⭐ Multi-Tenant Database Architecture (Expert) — Expert Tier ⚠ No guidance provided. Mastery is assessed on correctness AND trade-off analysis. ⚠ Use -- comments or REPORT to document every design decision. ──────────────────────────────────────────

pg=#

KEY COMMANDS

SELECT table_name FROM information_schema.columns WHERE table_schema='public' ...
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders USING (tenant_id=current_setting('app...
CREATE INDEX idx_orders_tenant_status ON orders(tenant_id, status);
CREATE OR REPLACE PROCEDURE tenant_purge(p UUID) LANGUAGE plpgsql AS $$ BEGIN ...
SET app.tenant_id='tenant-uuid'; SELECT COUNT(*) FROM orders;
CALL tenant_purge('uuid-to-delete');

Expert tier: syntax starting points only. Your reasoning counts as much as the SQL.

SCHEMA BROWSER

Type \d or SHOW TABLES to list tables, SHOW SEARCH_PATH for active schema.

EXPERT BRIEFING

MULTI-TENANT — ORBIS SAAS — EXPERT ⭐⭐⭐⭐⭐

⭐⭐⭐⭐⭐ Expert tier. No walkthrough. No syntax examples beyond the reference window. You are assessed on correctness, justification of trade-offs, and depth of reasoning.

SCENARIO

Orbis SaaS has hit critical scale: their single-schema multi-tenant database is leaking data between tenants (a bug in WHERE clauses missing tenant_id), performance degrades as top tenants grow, and GDPR requires hard-deletion of all data for a specific tenant within 30 days. You must redesign the isolation model, implement Row Level Security, create tenant-aware connection pooling, and build a GDPR purge procedure — all without downtime.

OBJECTIVES (8)

Audit: find all tables missing tenant_id FK
Enable Row Level Security on all tenant tables
Create RLS policies using current_setting('app.tenant_id')
Add tenant_id prefix to all indexes (composite)
Build tenant_purge() stored procedure (GDPR)
Test RLS: verify cross-tenant data isolation
Design tenant sharding key for horizontal scale
Produce Architecture Decision Record (ADR)

EXPERT RULES

Every DDL decision must be justified with a -- comment or REPORT statement
Trade-off analysis is required for all design choices
All 8 objectives must be completed before the report is accepted
Reference window provides syntax starting points only

Display Mode