Idempotent State Transitions and Claims-Based RLS in Practice

A smart lock platform is only as trustworthy as its weakest boundary. Physical access to a room is the end result of a long chain: an invitation is accepted, a role is granted, a reservation is created, a payment is reconciled, a check-in is recorded, and finally a credential is issued. If any link in that chain is ambiguous — two roles that mean the same thing, a payment that is "maybe paid", a check-in that belongs to "some" booking — the system either denies access to a legitimate guest or grants it to the wrong one.

This article walks through four hardening patterns that recently shaped our access-control and reservation lifecycle work: canonical identity for authorization, row-level security keyed off verified claims, idempotent state transitions with self-healing, and ports and adapters for outbound side effects. All examples are generalized; the point is the shape of the solution, not our internal schema.

---

1. Authorization starts with a canonical identity

The most common authorization bug is not a missing check — it is a check that compares the wrong thing. Role systems tend to grow organically: a display name ("Facility Manager"), a localized label ("施設管理者"), a UUID, and a slug (facility_manager) all end up floating through the codebase. Each one becomes an implicit key, and every comparison site is a chance to drift.

The fix is to elect exactly one role key as the identity, fold every variant onto it, and make everything else presentation.

export const ROLE_KEYS = ['owner', 'facility_manager', 'staff', 'guest'] as const;
export type RoleKey = (typeof ROLE_KEYS)[number];
const LEGACY_ROLE_ALIASES: Record<string, RoleKey> = {
  admin: 'owner',
  'facility-admin': 'facility_manager',
  manager: 'facility_manager',
  member: 'staff',
};
export function toRoleKey(input: string | null | undefined): RoleKey | null {
  if (!input) return null;
  const normalized = input.trim().toLowerCase().replace(/[\s-]+/g, '_');
  if ((ROLE_KEYS as readonly string[]).includes(normalized)) {
    return normalized as RoleKey;
  }
  return LEGACY_ROLE_ALIASES[normalized] ?? null;
}

Two properties matter here:

1. toRoleKey returns null for anything unknown. Unknown input must never silently degrade into a permissive default.
2. The union type makes exhaustive checks possible, so adding a role forces every decision site to be revisited.

With a canonical key, permission checks become total functions rather than string soup:

type Action = 'issue_credential' | 'cancel_reservation' | 'invite_member' | 'view_audit_log';
const POLICY: Record<RoleKey, ReadonlySet<Action>> = {
  owner: new Set(['issue_credential', 'cancel_reservation', 'invite_member', 'view_audit_log']),
  facility_manager: new Set(['issue_credential', 'cancel_reservation', 'invite_member']),
  staff: new Set(['issue_credential']),
  guest: new Set([]),
};
export function can(role: RoleKey, action: Action): boolean {
  return POLICY[role].has(action);
}

A subtle but important detail: role membership in a multi-tenant system must be scoped. A user who is a manager at facility A is not a manager at facility B. Membership rows therefore carry the scope, and the lookup is always (user_id, facility_id) -> role_key, never (user_id) -> role_key. Getting this wrong turns a legitimate cross-facility invitation into either a rejection or a privilege escalation.

export interface Membership {
  userId: string;
  facilityId: string;
  roleKey: RoleKey;
}
export function resolveRole(memberships: readonly Membership[], facilityId: string): RoleKey | null {
  return memberships.find((m) => m.facilityId === facilityId)?.roleKey ?? null;
}

---

2. Enforce the policy in the database, not only in the app

Application-layer checks are necessary but insufficient. Any new endpoint, any admin script, any forgotten code path can bypass them. Row-level security (RLS) moves the final word into the database, where every query — regardless of which service issued it — is filtered.

The pattern that scales is to put the tenant/role facts into the verified JWT claims at login time (via an auth hook), then write policies that read those claims. This avoids recursive policy lookups, which are a classic source of both infinite recursion and accidental full-table exposure.

ALTER TABLE organization_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE organization_members FORCE ROW LEVEL SECURITY;
CREATE OR REPLACE FUNCTION auth_facility_ids()
RETURNS uuid[] LANGUAGE sql STABLE AS $$
  SELECT COALESCE(
    ARRAY(SELECT jsonb_array_elements_text(
      NULLIF(current_setting('request.jwt.claims', true), '')::jsonb -> 'facility_ids'
    )::uuid),
    ARRAY[]::uuid[]
  );
$$;
CREATE OR REPLACE FUNCTION auth_role_key(target uuid)
RETURNS text LANGUAGE sql STABLE AS $$
  SELECT NULLIF(current_setting('request.jwt.claims', true), '')::jsonb
         -> 'roles' ->> target::text;
$$;
CREATE POLICY members_select_same_facility ON organization_members
  FOR SELECT USING (facility_id = ANY (auth_facility_ids()));
CREATE POLICY members_write_requires_manager ON organization_members
  FOR ALL USING (auth_role_key(facility_id) IN ('owner', 'facility_manager'))
  WITH CHECK (auth_role_key(facility_id) IN ('owner', 'facility_manager'));

Three rules we apply to every RLS rollout:

• FORCE ROW LEVEL SECURITY so that even the table owner is subject to the policy. Without it, a migration or job running as the owner silently bypasses everything.
• Separate USING and WITH CHECK. USING controls what you can see and modify; WITH CHECK controls what you can write. A policy with only USING can let a user move a row into a tenant they do not belong to.
• Service-role usage must be explicit and narrow. When a backend function legitimately needs to bypass RLS (for example, writing an audit event on behalf of an already-authorized actor), it should acquire the elevated client at the call site, after the authorization decision, and never as a module-level default.

export async function recordCheckIn(ctx: RequestContext, input: CheckInInput) {
  const role = resolveRole(ctx.memberships, input.facilityId);
  if (!role || !can(role, 'issue_credential')) {
    throw new ForbiddenError('check_in.not_authorized');
  }
  // Authorization decided above; elevated client used only for the audited write.
  const admin = createServiceRoleClient();
  return admin.from('check_ins').insert({
    reservation_id: input.reservationId,
    actor_id: ctx.userId,
    actor_role: role,
    source: input.source,
  });
}

The ordering is the whole point: decide, then elevate. Elevating first and hoping a later branch catches the problem is how privilege leaks happen.

---

3. Model the reservation lifecycle as an explicit state machine

Reservation and payment states are where reliability bugs cluster: a payment intent is canceled but the row stays pending; a refunded reservation is reopened and inherits refunded; a retried confirmation succeeds but never flips payment_status to paid, so the guest hits a 402 loop at the door.

Every one of those is the same root cause — transitions were implemented as scattered UPDATE statements rather than as a single, total function over a declared state space.

export type PaymentStatus = 'unpaid' | 'authorized' | 'paid' | 'refunded' | 'failed';
export type ReservationStatus =
  | 'pending'
  | 'confirmed'
  | 'checked_in'
  | 'completed'
  | 'cancelled'
  | 'expired';
type Event =
  | { type: 'CONFIRM'; paymentStatus: PaymentStatus }
  | { type: 'PAYMENT_CANCELED' }
  | { type: 'CHECK_IN'; at: string }
  | { type: 'REOPEN' }
  | { type: 'EXPIRE' };
export interface Reservation {
  status: ReservationStatus;
  paymentStatus: PaymentStatus;
  checkedInAt: string | null;
}
const ALLOWED: Record<ReservationStatus, ReadonlySet<ReservationStatus>> = {
  pending: new Set(['confirmed', 'cancelled', 'expired']),
  confirmed: new Set(['checked_in', 'cancelled', 'expired']),
  checked_in: new Set(['completed', 'cancelled']),
  completed: new Set([]),
  cancelled: new Set(['pending']),
  expired: new Set(['pending']),
};

The reducer enforces two invariants that are easy to state and hard to violate once centralized: no transition outside the declared graph, and repeating an event is a no-op, not a mutation.

export function reduce(state: Reservation, event: Event): Reservation {
  switch (event.type) {
    case 'CONFIRM': {
      if (state.status === 'confirmed') {
        // Idempotent replay: still reconcile payment, never rewrite identity fields.
        return state.paymentStatus === event.paymentStatus
          ? state
          : { ...state, paymentStatus: event.paymentStatus };
      }
      assertTransition(state.status, 'confirmed');
      return { ...state, status: 'confirmed', paymentStatus: event.paymentStatus };
    }
    case 'PAYMENT_CANCELED': {
      assertTransition(state.status, 'cancelled');
      return { ...state, status: 'cancelled', paymentStatus: 'failed' };
    }
    case 'CHECK_IN': {
      if (state.status === 'checked_in') {
        // Preserve the ORIGINAL timestamp: it is audit evidence, not a cache value.
        return state;
      }
      assertTransition(state.status, 'checked_in');
      return { ...state, status: 'checked_in', checkedInAt: event.at };
    }
    case 'REOPEN': {
      assertTransition(state.status, 'pending');
      // Reopening must not inherit a terminal payment state.
      return { ...state, status: 'pending', paymentStatus: 'unpaid', checkedInAt: null };
    }
    case 'EXPIRE': {
      assertTransition(state.status, 'expired');
      return { ...state, status: 'expired' };
    }
  }
}
function assertTransition(from: ReservationStatus, to: ReservationStatus): void {
  if (!ALLOWED[from].has(to)) {
    throw new InvalidTransitionError(`illegal transition ${from} -> ${to}`);
  }
}

Three lessons are encoded above, each learned from a real failure mode:

• The idempotent branch must still reconcile. A retried confirmation that short-circuits without syncing payment_status leaves the door rejecting a guest who has already paid. "Already done" is not the same as "nothing to do".
• Terminal payment states must be cleared on reopen. Otherwise a refunded-then-reopened reservation is treated as settled.
• Audit timestamps are immutable. Re-entering a state must not overwrite the first occurrence, because downstream dispute resolution depends on it.

Self-healing sweeps close the gap with external systems

When money or hardware is involved, the local row and the external system can diverge — a webhook is lost, a process dies between the external cancel and the local update. A periodic reconciler that re-derives the correct state from the external source of truth turns a permanent inconsistency into a temporary one.

export async function reconcileStuckPending(now: Date, deps: Deps): Promise<void> {
  const stale = await deps.repo.findPending({ olderThan: minusMinutes(now, 30) });
  for (const reservation of stale) {
    const remote = await deps.payments.getIntent(reservation.paymentIntentId);
    const event: Event | null =
      remote.status === 'canceled' ? { type: 'PAYMENT_CANCELED' }
      : remote.status === 'succeeded' ? { type: 'CONFIRM', paymentStatus: 'paid' }
      : null;
    if (!event) continue;
    const next = reduce(toState(reservation), event);
    await deps.repo.applyIfUnchanged(reservation.id, reservation.version, next);
    deps.audit.emit('reservation.reconciled', {
      reservationId: reservation.id,
      from: reservation.status,
      to: next.status,
      reason: remote.status,
    });
  }
}

Note applyIfUnchanged(id, version, next) — optimistic concurrency. A sweeper racing a live request must lose gracefully rather than clobber a newer state.

---

4. Validate at the boundary, and make invalid states unrepresentable

A surprising share of "weird" production behavior traces back to inputs that should never have been accepted: a blank room type name, a negative buffer interval, a reservation created without the room type needed to mint the right credential. Each is trivial in isolation and corrosive in aggregate, because downstream code starts defending against values that should not exist.

Parse once, at the edge, into a type the rest of the system can trust.

import { z } from 'zod';
export const ReservationInputSchema = z.object({
  facilityId: z.string().uuid(),
  roomTypeId: z.string().uuid({ message: 'reservation.room_type_required' }),
  guestEmail: z.string().email(),
  startAt: z.string().datetime({ offset: true }),
  endAt: z.string().datetime({ offset: true }),
  bufferMinutes: z.number().int().min(0).max(24 * 60),
}).refine((v) => Date.parse(v.endAt) > Date.parse(v.startAt), {
  message: 'reservation.end_before_start',
  path: ['endAt'],
});
export type ReservationInput = z.infer<typeof ReservationInputSchema>;

Two practices make this pay off:

• Numeric inputs get explicit bounds. min(0) on a buffer or a price is a one-line change that eliminates an entire class of scheduling and billing anomalies.
• Error messages are stable i18n keys, not prose. The UI can localize, tests can assert, and log aggregation can group. Free-form strings are neither translatable nor greppable.

When a field change alters the security-relevant output — for example, changing the room on an existing reservation — the correct behavior is not to patch the row but to re-derive the dependent artifacts. A room change must invalidate and reissue the credential; otherwise an old key silently keeps working on a room the guest no longer occupies.

export async function changeRoom(reservationId: string, nextRoomId: string, deps: Deps) {
  const reservation = await deps.repo.get(reservationId);
  if (reservation.roomId === nextRoomId) return reservation;
  await deps.credentials.revoke(reservation.credentialId, { reason: 'room_changed' });
  const credential = await deps.credentials.issue({ reservationId, roomId: nextRoomId });
  deps.audit.emit('credential.reissued', { reservationId, from: reservation.roomId, to: nextRoomId });
  return deps.repo.update(reservationId, { roomId: nextRoomId, credentialId: credential.id });
}

---

5. Isolate outbound side effects behind a port

Notifications, SMS providers, calendar sync, webhooks — every outbound integration is a place where a third-party outage can become your outage, and where retries can become duplicate messages. A ports-and-adapters boundary keeps the domain testable and the failure modes contained.

export interface NotificationMessage {
  readonly idempotencyKey: string;
  readonly to: string;
  readonly template: string;
  readonly payload: Record<string, string | number>;
}
export interface NotificationPort {
  readonly channel: 'email' | 'sms' | 'webhook';
  send(message: NotificationMessage): Promise<DeliveryResult>;
}
export type DeliveryResult =
  | { status: 'delivered'; providerId: string }
  | { status: 'rejected'; reason: string }
  | { status: 'retryable'; reason: string };

The adapter's job is to translate provider-specific failures into that closed result union — the domain must never see a raw HTTP error.

export class SmsAdapter implements NotificationPort {
  readonly channel = 'sms' as const;
  constructor(private readonly client: ProviderClient) {}
  async send(message: NotificationMessage): Promise<DeliveryResult> {
    try {
      const res = await this.client.messages.create({
        to: message.to,
        body: render(message.template, message.payload),
        idempotencyKey: message.idempotencyKey,
      });
      return { status: 'delivered', providerId: res.sid };
    } catch (error) {
      const status = getHttpStatus(error);
      if (status === 429 || (status !== undefined && status >= 500)) {
        return { status: 'retryable', reason: `provider_${status}` };
      }
      return { status: 'rejected', reason: classify(error) };
    }
  }
}

The scheduler on top then only deals with three outcomes, and the idempotencyKey — derived deterministically from (reservationId, template, scheduledFor) — guarantees that a retried cron run cannot double-send.

export async function dispatchDue(now: Date, ports: Map<string, NotificationPort>, repo: Repo) {
  const due = await repo.claimDue(now, { limit: 100, leaseSeconds: 60 });
  for (const job of due) {
    const port = ports.get(job.channel);
    if (!port) {
      await repo.markRejected(job.id, 'no_adapter_for_channel');
      continue;
    }
    const result = await port.send(toMessage(job));
    if (result.status === 'delivered') await repo.markDelivered(job.id, result.providerId);
    else if (result.status === 'rejected') await repo.markRejected(job.id, result.reason);
    else await repo.scheduleRetry(job.id, backoff(job.attempts), result.reason);
  }
}

claimDue with a lease is what makes overlapping cron runs safe. The same reasoning applies to CI: adding concurrency guards to workflows prevents two deploy or migration pipelines from racing on the same branch.

---

6. Tests that survive refactoring

Security and lifecycle code is exactly the code you will refactor. Tests that assert on incidental details (argument count, call ordering of unrelated helpers) break on every refactor and get disabled — which is worse than having no test.

Assert on observable contracts: the resulting state, the emitted audit event, the authorization decision.

import { assertEquals, assertThrows } from '@std/assert';
Deno.test('check-in is idempotent and preserves the original timestamp', () => {
  const first = reduce(
    { status: 'confirmed', paymentStatus: 'paid', checkedInAt: null },
    { type: 'CHECK_IN', at: '2026-07-20T09:00:00Z' },
  );
  const second = reduce(first, { type: 'CHECK_IN', at: '2026-07-20T11:30:00Z' });
  assertEquals(second.checkedInAt, '2026-07-20T09:00:00Z');
  assertEquals(second, first);
});
Deno.test('reopen clears terminal payment state', () => {
  const reopened = reduce(
    { status: 'cancelled', paymentStatus: 'refunded', checkedInAt: '2026-07-01T00:00:00Z' },
    { type: 'REOPEN' },
  );
  assertEquals(reopened.paymentStatus, 'unpaid');
  assertEquals(reopened.checkedInAt, null);
});
Deno.test('illegal transitions are rejected', () => {
  assertThrows(() =>
    reduce({ status: 'completed', paymentStatus: 'paid', checkedInAt: null }, { type: 'CHECK_IN', at: 'now' })
  );
});

For authorization, property-style coverage over the full role × action matrix is cheap and catches regressions the moment a role is added:

Deno.test('guests can perform no privileged action', () => {
  const actions: Action[] = ['issue_credential', 'cancel_reservation', 'invite_member', 'view_audit_log'];
  for (const action of actions) {
    assertEquals(can('guest', action), false, `guest must not ${action}`);
  }
});

One more habit worth institutionalizing: when a feature changes behavior, update the consistency tests in the same change set. A red CI on the integration branch that lingers trains the team to ignore red, which is the most expensive habit a security-critical project can acquire.

---

Summary

Concern	Pattern	Failure it prevents
Role identity	Single canonical role_key, normalize aliases, null for unknown	Permission checks comparing the wrong field
Multi-tenancy	Scoped (user, facility) -> role, RLS with FORCE + WITH CHECK	Cross-tenant reads and writes
Privilege escalation	Decide authorization first, elevate at the narrow write	Service-role clients used as a default
Lifecycle	Explicit transition graph + idempotent reducer	Illegal states, payment/check-in desync
External drift	Reconciler with optimistic concurrency + audit events	Permanently stuck rows
Input	Parse at the boundary, bounded numerics, i18n error keys	Invalid states propagating downstream
Side effects	Port/adapter with closed result union + idempotency key	Duplicate sends, provider outages leaking in
Change safety	Contract-level tests, concurrency guards in CI	Tests disabled during refactors, racing pipelines

None of these patterns are exotic. What makes them valuable is that they are applied consistently, at every boundary, so that the question "can this user open this door right now?" has exactly one answer derived from exactly one source of truth.