Search Knowledge

© 2026 LIBREUNI PROJECT

Database Systems/Data at scale and synthesis
Page 19 of 20

Database Capstone

Design, test, measure, and defend a multi-tenant order system whose guarantees survive contention, failure, replication, and hostile access.

Lab briefData at scale and synthesis · 20 h · 4 outcomes

Required knowledge

  • Relational design, SQL, functional dependencies, normalization, and query planning
  • Indexes, transactions, concurrency control, recovery, distributed-data trade-offs, and database security
  • Ability to write tests, read execution plans, construct traces, and explain assumptions in a design review

Learning outcomes

  • Produce a coherent schema, query portfolio, transaction/state model, and operational plan for a multi-tenant system.
  • Demonstrate each material invariant with a constraint, transaction trace, or a named procedural enforcement boundary.
  • Measure a workload claim with reproducible data, plans, failure injection, and counterexample tests.
  • Defend limits, trade-offs, and incident recovery behavior rather than presenting a feature checklist.

Assessment target

Construct and verify a runnable multi-tenant order-system design that preserves stated invariants under adversarial queries, concurrent requests, a crash/recovery drill, and one replica or migration failure.

Scope boundary

  • The capstone does not require production deployment, payment-card handling, or a full DBMS implementation.
  • Cloud-product configuration and formal distributed-consensus proofs may support a design only when their stated assumptions are testable.

Build a database-backed marketplace for independent merchants. A merchant owns catalog items and warehouses; customers place orders; an order records immutable line prices; inventory is reserved before fulfillment; a provider sends duplicate and out-of-order payment webhooks. The target is not a collection of SQL snippets. It is an argument that the following facts survive normal operation and stated failures:

  • a tenant cannot access another tenant’s orders, products, inventory, or audit records;
  • each order line retains the price accepted at checkout;
  • inventory never becomes negative and one reservation is not counted twice;
  • payment and fulfillment states advance only through legal, idempotent transitions; and
  • an acknowledged order can be recovered, investigated, and measured.

Establish the data contract before selecting tables

Code
rectangle "tenant" as T
rectangle "store" as S
rectangle "product\ncurrent catalog state" as P
rectangle "warehouse_stock" as W
rectangle "orders" as O
rectangle "order_line\nimmutable unit_price" as L
rectangle "payment_event\nidempotency key + state" as E
T --> S : owns
S --> P : sells
S --> W : stocks
S --> O : receives
O --> L : contains
P --> L : price snapshot is copied
O --> E : payment history
note right of L
Order history must not change
when catalog price changes.
end note
tenantstoreproductcurrent catalog statewarehouse_stockordersorder_lineimmutable unit_pricepayment_eventidempotency key + stateOrder history must not changewhen catalog price changes.ownssellsstocksreceivescontainsprice snapshot is copiedpayment history

Write a one-page domain contract before DDL. It must name entity identity, tenant ownership, deletion policy, money representation, time zone, legal state transitions, service-level targets, and values that can be stale. For example, use integer minor units or an exact numeric representation for money; state whether a product may be soft-deleted after being ordered; and distinguish a current catalog price from a historical purchase price.

Prediction — find the missing fact

A draft schema stores order total and a foreign key to the current product price. A merchant changes a price after checkout. What report can now become false?

Worked solutionExpand

Historical revenue and line-item invoices can change retroactively if they derive accepted price from the mutable product row. Store the accepted unit price and currency on each order line, or reference an immutable price version. If an order total is stored for performance, declare whether it is derived or atomically maintained and test it against the line sum.

Make invalid relationships unrepresentable

CREATE TABLE orders (
  tenant_id      bigint NOT NULL,
  order_id       bigint GENERATED ALWAYS AS IDENTITY,
  customer_id    bigint NOT NULL,
  status         text NOT NULL,
  created_at     timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, order_id),
  CHECK (status IN ('pending', 'paid', 'cancelled', 'fulfilled'))
);

CREATE TABLE order_line (
  tenant_id       bigint NOT NULL,
  order_id        bigint NOT NULL,
  line_no         integer NOT NULL,
  product_id      bigint NOT NULL,
  quantity        integer NOT NULL CHECK (quantity > 0),
  unit_price_minor bigint NOT NULL CHECK (unit_price_minor >= 0),
  PRIMARY KEY (tenant_id, order_id, line_no),
  FOREIGN KEY (tenant_id, order_id)
    REFERENCES orders (tenant_id, order_id)
);

CREATE TABLE warehouse_stock (
  tenant_id   bigint NOT NULL,
  warehouse_id bigint NOT NULL,
  product_id  bigint NOT NULL,
  on_hand     integer NOT NULL CHECK (on_hand >= 0),
  PRIMARY KEY (tenant_id, warehouse_id, product_id)
);

The code is a starting artifact, not the full schema. Add product/customer ownership foreign keys, a tenant-scoped SKU uniqueness rule, state-transition enforcement, webhook-event uniqueness, and deletion policy. A row-local CHECK cannot safely enforce an aggregate rule over arbitrary sibling rows; identify the transaction or summary relation that carries that responsibility.

Application — prove one key choice

Give a counterexample that the composite order-line foreign key rejects but a foreign key on order_id alone could accept in a system where order identifiers are tenant-local.

HintExpand

Construct the same order number in two tenants.

Worked solutionExpand

Let tenant 12 and tenant 13 both have order ID 9. A line with tenant 12 and order ID 9 must reference tenant 12’s order, not whichever row an unconstrained ID lookup finds. The composite foreign key enforces that pair. A global surrogate ID can also work, but it does not remove the need to enforce tenant ownership on every related object and access path.

Design a short, recoverable order transaction

Code
participant "checkout client" as C
participant "order transaction" as T
database "orders + stock + outbox" as D
participant "payment worker" as W
participant "provider" as P
C -> T : request with idempotency key
T -> D : validate tenant; reserve stock; insert order/outbox
T -> D : COMMIT
W -> D : claim committed outbox record
W -> P : authorize with same key
P -> W : webhook may repeat or reorder
W -> D : legal monotonic state transition
checkout clientorder transactionorders . stock . outboxpayment workerprovidercheckout clientcheckout clientorder transactionorder transactionorders + stock + outboxorders + stock + outboxpayment workerpayment workerproviderproviderrequest withidempotency keyvalidate tenant; reservestock; insertorder/outboxCOMMITclaim committed outboxrecordauthorize with same keywebhook may repeat orreorderlegal monotonic statetransition

The reservation must protect its own invariant. A conditional update of one stock row can reject a request whose quantity exceeds available stock; an aggregate reservation across warehouses needs a defined lock/serialization boundary. Store the client idempotency key under a uniqueness rule so retries converge on one order. The provider call is external: use the durable outbox and a webhook event ID to deduplicate it.

Worked trace — competing last-unit requests

  1. T1 conditionally reserves the last unit and commits.
  2. T2 executes the same conditional update after T1; its affected-row count is zero.
  3. T2 records or returns a rejection without creating a successful order state.
  4. A committed outbox worker can retry provider communication with the same idempotency key.

Structuring the checkout and payment boundary

During flash-sale contention, a checkout service begins a database transaction, locks the warehouse_stock row with SELECT ... FOR UPDATE, makes a synchronous 400 ms HTTPS call to an external payment gateway, and then commits the order.

Evaluate the architectural trade-offs of holding stock locks across external payment gateway invocations:

Measure the workload and physical design

WorkloadCandidate evidenceRequired counterexample
tenant order historyplan for tenant/time range; index on tenant then timetenant with 90% of all orders
stock lookup/reservationprimary key or narrow tenant/warehouse/product pathconcurrent requests for same SKU
daily revenuegrain-first aggregate and calendar left joinday with zero sales, duplicate lines
webhook processinguniqueness lookup on provider event IDduplicate and out-of-order event
retentiontime partition/pruning plan and drop policytenant-only query that cannot prune

Seed enough data to expose selectivity, skew, and cache behavior. Record database version, schema/index definition, data generator distribution, concurrency level, warm-up, query text, plan, estimated and actual rows, and latency distribution. A single low elapsed time is not a benchmark and a single plan screenshot is not a physical-design argument.

Transfer — reject a seductive index

A proposal adds a wide covering index on every order column to speed a nightly export. The table receives continuous inserts and status updates. What evidence would justify or reject it?

Worked solutionExpand

Compare the export plan and buffer/I/O behavior with and without the index, then measure insert/update throughput, index size, vacuum/replication cost, and cache pressure under representative concurrency. If the export reads most rows once nightly, a sequential or partition-aware plan may dominate. The index earns its cost only when measured read benefit exceeds the continuing maintenance cost for the stated workload.

Rehearse recovery, replication, and security

DrillPass evidence
bad migrationisolated point-in-time restore, invariant checks, measured RPO/RTO
primary losspromotion path, client fencing/epoch behavior, acknowledged-write loss statement
replica lagread-after-write behavior and stale-result contract
leaked runtime credentialrevocation/rotation time, narrowed replacement role, audit review
tenant attackaltered tenant ID, direct runtime-role query, composite-FK and policy rejection
negative inventory alertfreeze affected workflow, trace first invariant break, reconcile from order/audit facts

Write an incident runbook for each drill: detection, containment, evidence preservation, integrity check, recovery action, client communication boundary, and regression test. A replica is not a backup after a bad delete; a backup is not security control if its archive role is broadly shared.

Synthesis analysis prompt

Choose one intentional relaxation: asynchronous search indexing, a delayed analytics aggregate, time partitioning, a cross-service saga, or a readable replica. State the invariant it does not preserve immediately, the visible state it permits, the repair/reconciliation process, and the test that detects a broken implementation.

HintExpand

A strong answer names one bounded fact that may lag and a separate fact that still cannot be violated.

Worked solutionExpand

A satisfactory analysis distinguishes the relaxed projection from the authoritative transaction. For example, search may lag after an order commit, but the order row and tenant authorization remain authoritative; an idempotent event consumer replays missed work and a watermark/lag alert detects delay. Do not call the relaxation safe without an observable bound, source of truth, and repair path.

System specification and verification checklist

A complete implementation package runs from an empty database and includes:

  1. domain contract, ER/domain diagram, DDL, migration plan, and seed fixture;
  2. query portfolio with declared result grain, expected outputs, adversarial NULL/duplicate/tenant tests, and representative plans;
  3. transaction/schedule dossier for inventory, idempotency, webhook order, and one aggregate invariant;
  4. physical-design and benchmark report with workload assumptions and a rejected alternative;
  5. recovery, replication, security, and incident-runbook evidence; and
  6. a concise design analysis that identifies one limit the system intentionally accepts.
CriterionInvariant standardObservable verification evidence
semantic and tenant correctnessstrict tenant isolation across all tablesexecutable constraints, counterexamples, and tested ownership
query/physical reasoningplans reflect intended index usage and grainmeasured plans plus write-cost and skew evidence
concurrency and recoveryACID isolation and crash recoverytraces, failure injection, safe retries, restore drill
security and operationsleast-privilege roles and audit trailleast privilege, attack tests, audit/rotation/runbooks
clarity of trade-offsexplicit failure boundaries and non-goalsrejected alternatives and explicit assumptions

Capstone invariant verification

0 / 4 answered

Which combination of mechanisms prevents both cross-tenant order leakage and duplicate payment state transitions in the marketplace capstone?

Classify each option as correct or incorrect, or leave uncertain options unanswered (“I don't know”) and verify at any time.

1
Composite tenant-aware foreign keys combined with Row-Level Security policies on least-privilege roles enforce strict cross-tenant data isolation.
2
Application-tier WHERE clause filters alone provide sufficient multi-tenant isolation without requiring database-level constraints or policies.
3
A unique constraint on provider_event_id combined with monotonic order state machine checks prevents duplicate webhook processing.
4
Routing payment webhooks and tenant authorization queries to asynchronous read replicas guarantees strict serializable state transitions.
Section Exercises

Deep Practice

Solve these problems to test and consolidate your understanding. Complete worked solutions and diagnostic hints are available for each problem.

Exercise 5.4.1: Multi-Tenant Foreign Key and RLS Defense-in-Depth

Verificationapplication

In the marketplace schema, a malicious tenant attempts to attach an order line referencing product ID prod-99 belonging to a rival merchant. Verify how the combination of composite foreign keys FOREIGN KEY (tenant_id, product_id) REFERENCES Product(tenant_id, product_id) and PostgreSQL Row-Level Security prevents unauthorized reference even if application-level tenant filters are bypassed.

HintExpand

Analyze relational constraint checks during foreign key evaluation under active RLS policies.

Worked solutionExpand

Even if an attacker manipulates the application API to insert an order line with a foreign product_id, the composite foreign key forces the database engine to locate a parent tuple matching (attacker_tenant_id, prod-99) in Product. Because prod-99 belongs to a different tenant, no such tuple exists within the attacker’s tenant key domain, causing immediate referential integrity rejection (violates foreign key constraint). Furthermore, active RLS policies on Product render the rival tenant’s row completely invisible to the attacker’s database session.

Exercise 5.4.2: Idempotent Payment Webhook Processing and Inventory Invariants

Descriptionrecall

Describe the database mechanisms required to guarantee that duplicate delivery of a payment gateway webhook (arriving concurrently across two webhook worker processes) cannot result in double-crediting an order or releasing reserved inventory twice.

HintExpand

Combine database unique constraints with atomic conditional status transitions.

Worked solutionExpand
  1. Unique constraint: Webhooks record their arrival in processed_webhooks keyed on UNIQUE (provider_event_id). The first worker commits the insert; the concurrent duplicate worker fails immediately with a unique key violation (23505) and safely halts execution.
  2. Monotonic state transition: Order status is advanced using guarded conditional updates: UPDATE orders SET status = 'paid' WHERE id = $1 AND status = 'pending';. If the status is already paid, 0 rows are affected, preventing re-execution of inventory fulfillment triggers.

Exercise 5.4.3: High-Volume Order Partitioning and Pruning Strategy

Constructionapplication

An enterprise marketplace accumulates 20,000,000 order records monthly. Construct a PostgreSQL table partitioning scheme for orders partitioned by month, and specify the indexing strategy that enables fast customer point lookups while retaining single-partition query pruning for monthly reports.

HintExpand

Range partition by created_at; include created_at in the composite primary key.

Worked solutionExpand
CREATE TABLE orders (
  tenant_id UUID NOT NULL,
  order_id BIGINT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  customer_id BIGINT NOT NULL,
  total_cents BIGINT NOT NULL,
  PRIMARY KEY (tenant_id, order_id, created_at)
) PARTITION BY RANGE (created_at);

-- Monthly partition
CREATE TABLE orders_2026_03 PARTITION OF orders
  FOR VALUES FROM ('2026-03-01 00:00:00+00') TO ('2026-04-01 00:00:00+00');

-- Local index for customer point lookups
CREATE INDEX idx_orders_customer ON orders (tenant_id, customer_id, created_at DESC);

Queries filtering by created_at prune away 11 out of 12 monthly partitions. Lookups by customer leverage the local B-tree index across partitions.

Exercise 5.4.4: End-to-End Architectural Defense: RPO/RTO Failure Runbook

Generalisationtransfer

An automated failover promoter initiates promotion of a replica after network partition, but the old primary resumes accepting writes. Specify the runbook protocol and fencing mechanisms to guarantee zero split-brain data corruption and satisfy an RTO under 15 minutes.

HintExpand

Use STONITH/fencing tokens and verify client connection pool draining before acknowledging failover.

Worked solutionExpand
  1. Fencing and STONITH: Before promoting the standby, the cluster consensus coordinator issues a shoot-the-other-node-in-the-head (STONITH) or revokes the storage controller lease of the old primary. The old primary’s storage access is immediately terminated, ensuring it cannot commit in-flight writes.
  2. Promotion with Epoch: The standby promotes with generation epoch E+1E+1
  3. DNS / Virtual IP Re-route: Connection routers update endpoint targets to the new primary at epoch E+1E+1
  4. Pool validation: Connection pools close stale idle sockets; client drivers attempting to connect with epoch EE receive connection-refused errors.
  5. Post-failover audit: Compare the promoted primary’s WAL position against archive storage to verify zero lost committed transactions RPO=0\text{RPO} = 0

References & Further Reading