Databases
Relational model: design and migrate a multi-tenant schema
Reading about schema design is not the same as living with a schema for two years. Design a real multi-tenant marketplace schema, defend every key and constraint choice, isolate tenants with RLS, then prove you can change it under load without breaking a single consumer — the loop you actually run on a production database.
Turn the unit into one engineering artifact: a Postgres schema where every key, type, constraint, and storage decision is justified by a query pattern, tenants are isolated in depth, and a breaking change ships with zero downtime — each claim backed by a migration you can run.
Design a multi-tenant B2B marketplace schema in Postgres (workspaces, users, products, orders, order_items, tags) where every key, type, and constraint is justified by a query pattern; enforce tenant isolation with Row-Level Security; then perform a zero-downtime breaking change with expand-then-contract — all as runnable migration files.
- A schema.sql plus ordered migration files that apply cleanly on an empty Postgres and recreate the full schema, including all constraints, indexes, the RLS policy, and the expand-then-contract migration.
- A one-page design rationale mapping every non-obvious decision (each surrogate key, each FK ON DELETE choice, the JSONB-vs-side-table tag call, the unit-price snapshot) to the query pattern or failure mode it defends against.
- EXPLAIN ANALYZE output for the three hot queries demonstrating index scans on the FK and tag columns at ~1M rows — captured, not assumed — plus a demonstration that RLS returns only the current workspace's rows when app.current_workspace_id is set.
- Proof the expand-then-contract migration runs against the populated table with the parity query returning zero mismatches and no reader breaking during the transition (old column still readable until the contract step).
- Add a state-transition guard with a BEFORE UPDATE trigger that refuses illegal order-status moves (for example completed cannot return to pending), and a test that the trigger rejects the bad transition.
- Add a stored aggregate: a workspace_revenue_cents column maintained by a trigger on order_items, with a reconciliation query that re-derives it from the source rows and proves zero drift.
- Add a DEFERRABLE INITIALLY DEFERRED foreign key to support a genuine circular or multi-step insert (for example a self-referential category tree), and document why immediate checking would block it.
- Benchmark BIGSERIAL versus UUIDv4 versus UUIDv7 as the primary key on the largest table at ~10M rows and report index size and insert throughput, confirming the locality story from the heap-and-TOAST lesson.
This is the loop you run on every real schema: design for the queries and the lifetime, not the first insert; put identity in an immutable surrogate key and guard business keys with UNIQUE NOT NULL; normalize to 3NF and denormalize only with explicit reconciliation; choose JSONB, a typed column, or a side table by how you query the value; make referential integrity and tenant isolation the engine’s job, not the application’s; and prove you can change the schema under load with expand-then-contract. Doing it once on a marketplace schema makes the production version judgment, not guesswork.