Tenant Isolation, Atomic RPCs and Unforgeable History

Introduction

A smart lock platform is not a CRUD app with a nicer UI. Every row we write eventually decides whether a physical door opens for a specific human being at a specific minute. That raises the cost of three classes of bug that most products can survive:

1. Scope bugs — a write or read that crosses a tenant boundary.
2. Race bugs — two requests that both believe they took the last locker, or both replaced the same pricing period.
3. History bugs — a record that can be rewritten after the fact, so the audit trail no longer explains what happened.

This post collects the patterns we converged on while shipping a recent block of work: multi-tenant option products with stock, a date-class pricing calendar with period history, provisioning verbs backed by atomic RPCs, a billing ledger, and the validation/test discipline that keeps those honest. The techniques are generic — they apply to any Postgres-backed, multi-tenant, money-and-access system.

1. Scope is a schema property, not a handler habit

The most dangerous defect we fixed in this cycle was mundane: a query that returned a guest-only column to a caller that should never have seen it, and a write path where the tenant scope came from the request body instead of the session. Both are the same root cause — the boundary was enforced in application code, so every new handler had to re-derive it correctly.

The fix is to make the database refuse. Row Level Security turns "the developer remembered" into "the engine enforced":

alter table option_products enable row level security;
alter table option_products force row level security;
create policy option_products_tenant_read on option_products
  for select
  using (facility_id = any (auth_facility_ids()));
create policy option_products_tenant_write on option_products
  for all
  using (facility_id = any (auth_facility_ids()))
  with check (facility_id = any (auth_facility_ids()));

Two details matter more than the policy itself:

• force row level security also applies the policy to the table owner. Without it, a migration or a service role silently bypasses everything you just wrote.
• with check is what stops a cross-tenant write. using filters what you can see; only with check stops you from inserting or updating a row into someone else's scope. A policy with using alone is a read guard masquerading as a write guard.

On the read side, column leaks deserve the same treatment. Instead of select * plus a hand-written allowlist in TypeScript, expose a view with only the columns a role may see, and let the type generator derive the client type from it:

create view option_products_public as
  select id, facility_id, name, price, stock_visible
  from option_products;

Then the leak becomes a compile error rather than a production incident, because the generated type simply has no guest_note field to read.

2. Read-modify-write is a race; collapse it into one statement

Stock decrement, provisioning, and "replace the currently active period" all share a shape: read a row, decide, then write. Between the read and the write, another request can win. In a locker or access-credential context, that is not a rounding error — it is two guests holding the same box.

The rule we settled on: any decision that depends on current state must execute inside a single database call that takes the lock it needs.

create or replace function reserve_option_stock(
  p_product_id uuid,
  p_reservation_id uuid,
  p_quantity int
) returns table (unit_id uuid) language plpgsql security invoker as $$
declare
  v_available int;
begin
  select stock_remaining into v_available
  from option_products
  where id = p_product_id
  for update;
  if v_available is null then
    raise exception 'product_not_found' using errcode = 'P0002';
  end if;
  if v_available < p_quantity then
    raise exception 'insufficient_stock' using errcode = 'P0001';
  end if;
  update option_products
    set stock_remaining = stock_remaining - p_quantity
    where id = p_product_id;
  return query
    insert into option_allocations (product_id, reservation_id, quantity)
    values (p_product_id, p_reservation_id, p_quantity)
    returning id;
end;
$$;

Notes worth stealing:

• for update inside the function is the whole point. Serialization happens at the row, not in a Node.js mutex that dies with the process.
• security invoker keeps RLS in force for the caller. Reach for security definer only when you genuinely need elevation, and then re-assert the tenant check inside the function body — a definer function is a hole punched through your policies.
• Distinct errcodes let the caller map failures to typed results instead of string-matching an error message.

On the TypeScript side, the RPC result becomes a discriminated union so that callers cannot forget the failure branch:

export type ReserveResult =
  | { ok: true; unitIds: string[] }
  | { ok: false; reason: 'insufficient_stock' | 'product_not_found' };
export async function reserveStock(input: ReserveInput): Promise<ReserveResult> {
  const { data, error } = await rpc('reserve_option_stock', input);
  if (!error) return { ok: true, unitIds: data.map((r) => r.unit_id) };
  if (error.code === 'P0001') return { ok: false, reason: 'insufficient_stock' };
  if (error.code === 'P0002') return { ok: false, reason: 'product_not_found' };
  throw error;
}

The union forces an exhaustive switch at every call site. Unknown errors still throw — silently swallowing an unrecognised database error is how a stock overdraft becomes invisible.

3. History must be unforgeable and replacement must be atomic

Pricing periods, business-hour seasons, and access policies all have the same requirement: what was the effective rule at time T? If the history table can be edited by the client, that question has no trustworthy answer, and every downstream dispute — a billing complaint, an access audit — becomes unresolvable.

Three constraints make history dependable:

1. The client never supplies the transition timestamp. Use now() from the database, not a value from the request payload.
2. Closing the old period and opening the new one happen in one transaction, so there is never a gap where no rule applies, and never an overlap where two do.
3. The database rejects overlaps structurally, rather than trusting application ordering.

alter table pricing_periods
  add constraint pricing_periods_no_overlap
  exclude using gist (
    facility_id with =,
    tstzrange(effective_from, effective_to) with &&
  );
create or replace function replace_pricing_period(
  p_facility_id uuid,
  p_rates jsonb
) returns uuid language plpgsql as $$
declare
  v_now timestamptz := now();
  v_id uuid;
begin
  update pricing_periods
    set effective_to = v_now
    where facility_id = p_facility_id and effective_to is null;
  insert into pricing_periods (facility_id, rates, effective_from, effective_to)
    values (p_facility_id, p_rates, v_now, null)
    returning id into v_id;
  return v_id;
end;
$$;

The exclusion constraint is the load-bearing part. It means that even a buggy future migration, a manual psql session, or a retried request cannot produce two simultaneously-active rules. Correctness stops depending on code review.

A related lesson from the same batch: price-at-booking-time is an input, not an inference. When a checkout flow re-derives the price by looking at "today's rates," it silently charges whatever the rules say now — not what the guest agreed to. Pass the booking identifier through the whole chain and resolve rates as of that record's timestamp.

const quote = await priceQuote({
  checkInId,
  asOf: reservation.createdAt,
});

4. Delete is a lie when you have an audit trail

Deleting a notification workflow used to cascade away its delivery history. That is convenient until someone asks "was the guest actually notified before check-in?" — and the evidence is gone precisely because an admin tidied up the UI.

Soft delete keeps referential history intact while still removing the row from every operational surface:

alter table notification_workflows add column deleted_at timestamptz;
create policy workflows_visible on notification_workflows
  for select using (deleted_at is null or current_setting('app.include_deleted', true) = 'on');
create index on notification_workflows (facility_id) where deleted_at is null;

The partial index matters: soft delete without one turns every list query into a full scan of your accumulated tombstones. And the default read path must exclude deleted rows by policy, not by every developer remembering to add .is('deleted_at', null).

5. Client validation is UX; server validation is the contract

A quota field capped at 24 hours per day in the form component is a hint. The same cap enforced in the RPC — and, better, as a check constraint — is a guarantee. We treat any bound that protects an invariant as needing three expressions: schema constraint, server validation, client hint. The first two are mandatory.

alter table membership_plans
  add constraint daily_hours_range check (daily_hours between 0 and 24);

export const membershipPlanSchema = z.object({
  dailyHours: z.number().int().min(0).max(24),
  name: z.string().trim().min(1).max(120),
});
export type MembershipPlanInput = z.infer<typeof membershipPlanSchema>;

Deriving the TypeScript type from the schema (rather than declaring both) means the validator and the type can never drift. The same pattern applies to message bodies: requiring a subject and capping length server-side prevents an unbounded payload from reaching a downstream delivery provider, where the failure mode is a partial send rather than a clean rejection.

6. Time zones are a correctness problem, not a formatting problem

Two separate defects in this batch came from the same root: constructing a date from a local Date object and letting the runtime's offset decide the calendar day. A picker returning the day before the selected one, and a check-in window rendered in the browser's zone rather than the facility's, are both symptoms of treating a timestamp as a string problem.

The durable rule: a calendar day is a value (YYYY-MM-DD) with an explicit zone; never derive it from a Date implicitly.

export function toFacilityDate(instant: Date, timeZone: string): string {
  return new Intl.DateTimeFormat('en-CA', {
    timeZone,
    year: 'numeric',
    month: '2-digit',
    day: '2-digit',
  }).format(instant);
}

In an access-control system this is a security property, not cosmetics: a credential that activates a day early is an unauthorized entry window.

7. Golden tests for the paths that actually write

Unit tests on pure helpers are cheap and shallow. The tests that earn their keep cover the apply path — the full journey from input to persisted effect, asserted against a checked-in expected artifact.

it('applies extracted fields and leaves unknowns to defaults', async () => {
  const result = await applyExtraction(loadFixture('golden/partial-input.json'));
  expect(result.applied).toMatchSnapshot();
  expect(result.skipped).toEqual(['seasonRates']);
  expect(result.warnings).toHaveLength(0);
});

Golden cases catch the regressions that matter here: a pricing unit that quietly discards weekend/tier/season modifiers, an extraction that truncates before the approval screen has enough to judge, a partial apply that overwrites fields it should have left alone. Each of those is a silent wrong answer, not a crash — exactly the class that types alone will not catch.

8. A backup you have never restored is a hypothesis

Finally: production backup scripts shipped as a triple — backup, verify, restore. The verify step is the one teams skip and the one that makes the other two real.

set -euo pipefail
pg_dump --format=custom --file="$DUMP" "$DATABASE_URL"
pg_restore --list "$DUMP" > /dev/null
psql "$SCRATCH_URL" -c 'drop schema if exists public cascade; create schema public;'
pg_restore --dbname="$SCRATCH_URL" --exit-on-error "$DUMP"
psql "$SCRATCH_URL" -tAc 'select count(*) from reservations' | grep -qv '^0$'

set -euo pipefail and --exit-on-error are deliberate: a restore script that partially succeeds and exits 0 is worse than no script, because it manufactures false confidence.

Summary

The through-line across all of these is the same: push invariants down to the layer that cannot be bypassed.

Invariant	Wrong layer	Right layer
Tenant isolation	handler filter	RLS using + with check
No double-allocation	app-level check	for update inside one RPC
No overlapping rules	ordered writes	exclude using gist constraint
Audit survives deletion	cascade delete	deleted_at + partial index
Value bounds	form validation	check constraint + server schema
Calendar correctness	local Date	explicit zone formatting

Every row in that table converts a bug that requires vigilance into a bug that is impossible. In a system that opens doors and moves money, that conversion is the whole job.