Data & Persistence

A single service is the easy case. You hold its state in one schema, wrap your SQL in a thin Data Gateway, and move on. Then a second restaurant signs, the menu reads outpace the order writes, someone cancels an order they shouldn't have, and the column you named status last quarter no longer fits the order lifecycle. The persistence altitude is where those pressures show up, and where the over-engineering tax is steepest. A handful of patterns cover almost all of it. Most of the work is knowing which one the moment actually calls for, and which one is a trap dressed as foresight.

Multitenancy

The problem: one running food-delivery platform, many restaurants, and each restaurant's orders and menu must stay theirs. The shape: every tenant-scoped table carries a tenant_id (the restaurant), and the database enforces the boundary so a missing WHERE clause can't leak one brand's orders into another's live-board.

The honest framing is a single axis: isolation against density. At one end, every restaurant gets its own database (maximum isolation, maximum operational cost, a migration to run N times). At the other, every brand shares one schema and a discriminator column (maximum density, one migration, the leak you have to prevent). Schema-per-tenant sits in the middle. Microsoft's Multitenant SaaS database tenancy patterns lays out the spectrum cleanly; the choice is which end of it your actual constraints push you toward.

Start dense. The default is shared schema, a tenant_id on every order and menu row, and Postgres Row-Level Security doing the enforcement. RLS turns "remember the WHERE clause" from a discipline you hope holds into a guarantee the database keeps. You set the restaurant on the connection, and every query the OrderGateway runs is silently filtered to that tenant.

ALTER TABLE "order" ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON "order"
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

// Set once per connection, before any query runs.
await conn.ExecuteAsync(
    "SELECT set_config('app.tenant_id', @TenantId, false)",
    new { TenantId = tenantId.ToString() });

What it buys you in production: one database to back up, patch and migrate, and a leak that's a CREATE POLICY away from impossible rather than one forgotten clause away from one restaurant reading another's orders. You escalate when a real constraint forces it. A national brand that needs menu-schema customisation, or one busy enough at dinner rush to starve the smaller restaurants, earns schema-per-tenant. Database-per-tenant is for when compliance or an enterprise contract demands physical separation, not for when it feels safer. The density you give up is paid for every day in operations.

Density is the default. Isolation is the exception you justify, not the posture you assume.

Skip-if: you have one restaurant. A single-tenant product needs none of this. No tenant_id, no RLS, no spectrum. Build it the day the second brand is real, not the day you imagine them.

Event Sourcing, CQRS and the Materialized View

The problem: an order's reads and writes want different shapes, and at rush they fight over the same tables. The courier wants a task-list, the customer wants order-history, the restaurant wants a live-board, and all three are projections of the same order. The shape: stop storing the order's current state and start storing the events that produced it, then build whatever read models you need from the log.

These three patterns are usually taught apart. Treat them as one story. An order is a lifecycle, not a row: OrderPlaced, ItemPrepared, CourierAssigned, Delivered. Event Sourcing is the write model. Instead of updating a row to status = 'delivered', you append a Delivered event to an immutable log, and the order's current state is the log folded up (Fowler; popularised by Greg Young). CQRS is the umbrella, separating the write model from the read model so neither has to compromise for the other (Greg Young, Udi Dahan). The Materialized View is the read model: a denormalised projection built from the events and optimised purely for queries (Fowler; Azure Cloud Design Patterns). You build one per audience. The courier task-list, the customer order-history, the restaurant live-board.

// Write side: append, never update.
await _events.AppendAsync(orderId, new CourierAssigned(orderId, courierId, assignedAt));

// Read side: each projection updated as events arrive, queried directly.
// SELECT order_id, status, eta FROM courier_tasklist_view WHERE courier_id = @courierId

What it buys you in production: you scale reads without touching the write path, rebuild the live-board by replaying the log, and get a complete audit trail of every order for free because the log is the history. Add the restaurant live-board a year after the courier task-list and you replay events you already have. The write side never knows the read side exists.

Now the honest part. This is the heaviest skip-if in the book, and it is the one teams reach for most often by mistake. Event sourcing means giving up the thing every developer already knows how to do, the UPDATE statement, in exchange for eventual consistency between write and read, projection rebuild logic, event versioning as the order schema evolves, and a debugging story most of your team hasn't lived. The catalogue makes it sound like the mature choice. For the overwhelming majority of systems it is not.

Most CRUD apps must not reach for this. If a row update and a SELECT describe your problem honestly, that is your architecture. Say so and move on.

Skip-if: you can't name the specific read load or audit requirement that a single normalised order table fails to meet. "We might need it later" is not that requirement. Reach for this when you have a genuine read/write asymmetry across the three sides, or a hard append-only audit mandate, and not one paragraph sooner.