Idempotent Key Issuance and Tenant-Safe Webhooks

Introduction

When a bug in a CRUD app ships, someone sees a wrong number on a screen. When a bug in a smart-lock platform ships, a door opens for the wrong person — or refuses to open for the right one. The blast radius is physical.

This post walks through a recent batch of changes in the UnlockOS codebase that share one theme: never let a distributed system invent extra credentials, extra tenants, extra retries, or extra alerts. Each section is written so the pattern transfers to any system that mints short-lived access material.

---

1. One Key Per Check-in, Even Under Concurrency

A guest taps show my key. The screen mounts twice (React StrictMode, a re-render, a flaky network retry). Two refreshKey calls fly at the lock vendor at the same time. If the issuing path is not idempotent, you now have two live PINs for one stay — and only one of them is tracked in your revocation list.

There are three layers to fix here, and you need all three.

Layer 1: in-process single-flight

Collapse concurrent callers in the same process into one promise:

const inflight = new Map<string, Promise<AccessKey>>();
export function refreshKey(checkInId: string): Promise<AccessKey> {
  const existing = inflight.get(checkInId);
  if (existing) return existing;
  const task = issueKey(checkInId).finally(() => {
    inflight.delete(checkInId);
  });
  inflight.set(checkInId, task);
  return task;
}

This is cheap and removes 90% of the duplicates in practice. It is also not a correctness guarantee: two server instances, or two serverless invocations, each have their own map.

Layer 2: a database constraint that makes the invariant impossible to break

create unique index access_keys_one_active_per_checkin
  on access_keys (check_in_id)
  where status = 'active';

A partial unique index encodes the business rule at most one active key per check-in in the only place that sees every writer. Any second issuer now fails loudly with a unique-violation instead of silently succeeding.

Layer 3: serialize the read-then-write

A unique index turns a race into an error. An advisory lock turns it into a wait, so the loser returns the same key rather than an error page:

create or replace function issue_checkin_key(p_check_in_id uuid)
returns access_keys
language plpgsql
as $$
declare
  v_key access_keys;
begin
  perform pg_advisory_xact_lock(hashtextextended(p_check_in_id::text, 0));
  select * into v_key
    from access_keys
   where check_in_id = p_check_in_id
     and status = 'active'
     and expires_at > now()
   limit 1;
  if found then
    return v_key;
  end if;
  insert into access_keys (check_in_id, status, pin, expires_at)
  values (p_check_in_id, 'active', generate_pin(), now() + interval '1 day')
  returning * into v_key;
  return v_key;
end;
$$;

The advisory lock is transaction-scoped, so it releases on commit or rollback — no leaked locks if the vendor call fails.

Rule of thumb: idempotency implemented only in application code is a performance optimization. Idempotency implemented as a database constraint is a guarantee.

---

2. Credentials Have a Lifecycle — Model It Explicitly

A related class of bug: a PIN is issued eagerly at reservation time so the guest can arrive late at night. When the guest actually checks in, a new stay-scoped key is minted. If nobody retires the first one, the reservation PIN outlives the reservation.

The fix is to stop treating key status as a free-form string and start treating it as a finite state machine with a whitelist of legal transitions.

export type KeyState =
  | 'pre_issued'   // minted from a reservation, before arrival
  | 'active'       // bound to a live check-in
  | 'revoked'      // withdrawn on purpose
  | 'expired';     // aged out

const LEGAL: Record<KeyState, readonly KeyState[]> = {
  pre_issued: ['active', 'revoked', 'expired'],
  active: ['revoked', 'expired'],
  revoked: [],
  expired: [],
};

export function assertTransition(from: KeyState, to: KeyState): void {
  if (!LEGAL[from].includes(to)) {
    throw new IllegalKeyTransitionError(`${from} -> ${to} is not allowed`);
  }
}

Terminal states with an empty transition list are the important part: a revoked key can never be resurrected by a retried webhook or an out-of-order job.

Check-in then becomes an explicit hand-off, performed in one transaction so the two keys can never both be active:

export async function completeCheckIn(db: Tx, reservationId: string) {
  return db.transaction(async (tx) => {
    const checkIn = await createCheckIn(tx, reservationId);
    const eager = await findPreIssuedKeys(tx, reservationId);
    for (const key of eager) {
      assertTransition(key.state, 'revoked');
      await revokeAtVendor(key.vendorKeyId);
      await markRevoked(tx, key.id, { reason: 'superseded_by_checkin' });
      await appendAudit(tx, {
        actor: 'system',
        action: 'key.revoked',
        subject: key.id,
        reason: 'superseded_by_checkin',
      });
    }
    return issueCheckInKey(tx, checkIn.id);
  });
}

Every revocation writes an audit row carrying who, what, and why. reason is what makes an incident review possible six months later; without it you can prove a key was revoked but not that it was revoked for the right cause.

---

3. Resolve Tenants on Every Row You Touch

Payment webhooks are a classic place to leak across tenants. The naive handler looks up one row by payment-intent ID, checks the tenant on that row, and then writes to all rows sharing the intent. The moment one intent can cover several reservations (a group booking), the check covers a subset of the writes.

export async function onPaymentSucceeded(event: StripeEvent) {
  const intentId = event.data.object.id;
  const rows = await findReservationsByIntent(intentId);
  if (rows.length === 0) {
    // Unknown intent: ignore, do not create anything.
    return { status: 'ignored' as const, reason: 'no_matching_reservation' };
  }
  const tenants = new Set(rows.map((r) => r.tenantId));
  if (tenants.size !== 1) {
    throw new TenantIntegrityError(`intent ${intentId} spans ${tenants.size} tenants`);
  }
  const [tenantId] = [...tenants];
  if (tenantId !== event.account_tenant_id) {
    throw new TenantMismatchError(intentId);
  }
  for (const row of rows) {
    await markPaid(row.id, { tenantId, intentId });
  }
}

Three properties worth copying:

1. Plural by default. The resolver returns a list even when one row is expected; the shape of the code stops lying about cardinality.
2. Fail closed on ambiguity. A single intent that touches two tenants is a data-integrity incident, not something to paper over with rows[0].
3. Unknown input is ignored, not created. A webhook must never be an implicit INSERT path for objects it does not recognize.

The same discipline applies to plain REST routes. A list endpoint filtered only by API key, or a detail endpoint that fetches by ID and then checks permission after the read, both leak. Scope the query itself:

select id, name, last_used_at
  from api_keys
 where tenant_id = $1          -- always, not just on the list route
   and id = coalesce($2, id);

---

4. Base64 Is Not Encryption, and RPCs Inherit Roles

Two failure modes that frequently travel together:

• A secret is encoded rather than encrypted, so anyone with a row dump has the plaintext.
• A security definer function that decrypts secrets is left executable by the logged-in user role, which turns a read-restricted column into a public API.

Hardening looks like this:

-- 1. Real encryption, keyed outside the table.
create or replace function store_provider_secret(p_facility uuid, p_secret text)
returns void
language plpgsql
security definer
set search_path = public, pg_temp
as $$
begin
  insert into provider_secrets (facility_id, ciphertext, key_version)
  values (p_facility, pgp_sym_encrypt(p_secret, current_setting('app.data_key')), 2)
  on conflict (facility_id) do update
     set ciphertext = excluded.ciphertext,
         key_version = excluded.key_version,
         rotated_at = now();
end;
$$;
-- 2. Decryption is reachable only by trusted server roles.
revoke execute on function read_provider_secret(uuid) from anon, authenticated;
grant execute on function read_provider_secret(uuid) to service_role;

Note key_version: a rotation is only operable if you can tell which rows are still on the old key, and which rows were written by a version of the code whose format you can no longer read.

The mirror image on the write side is row-level security. If clients can INSERT into a table that grants entitlements — subscriptions, memberships, credits — they can grant themselves access:

alter table subscriptions enable row level security;
revoke insert, update, delete on subscriptions from authenticated;
create policy subscriptions_read_own on subscriptions
  for select to authenticated
  using (user_id = auth.uid());

Writes land through a server-side path that validates payment first. Likewise, audit-style writes (payment events, admission logs) should use the service role rather than the caller's client — otherwise the caller's RLS can silently drop the very record you rely on for forensics.

---

5. Do Not Retry What Cannot Succeed

Lock vendors love returning HTTP 200 with a business error in the body. If your client keys retry logic off the status code, an authorization failure looks like a transient blip and you hammer the vendor with token refreshes forever.

type VendorResult<T> =
  | { ok: true; data: T }
  | { ok: false; code: string; retryable: boolean };

export async function callVendor<T>(path: string, init: RequestInit): Promise<VendorResult<T>> {
  const res = await fetch(path, init);
  const body = await res.json();
  if (res.status >= 500) {
    return { ok: false, code: `http_${res.status}`, retryable: true };
  }
  if (typeof body.code === 'string' && body.code !== 'SUCCESS') {
    // 200 OK with a business error: authorization problems are NOT transient.
    const retryable = TRANSIENT_CODES.has(body.code);
    return { ok: false, code: body.code, retryable };
  }
  return { ok: true, data: body.data as T };
}

The complementary control is a hard ceiling on cached credential lifetime. Trust the upstream expires_in, but clamp it so a misreported value cannot pin a stale token in memory:

const MAX_TOKEN_LIFETIME_MS = 95 * 60 * 1000;
const SAFETY_MARGIN_MS = 60 * 1000;
export function cacheTtl(expiresInSeconds: number): number {
  const advertised = expiresInSeconds * 1000 - SAFETY_MARGIN_MS;
  return Math.max(0, Math.min(advertised, MAX_TOKEN_LIFETIME_MS));
}

One more habit from the same family: when logging upstream failures, log the error code, endpoint, and correlation ID — never the request body. Webhook and messaging payloads carry user identifiers and tokens, and log stores rarely have the same retention and access controls as your primary database.

---

6. All-or-Nothing Group Operations

Reserving N rooms as a group must be atomic: either all N are held, or none are. Partial success is the worst outcome — the guest is charged for a group they cannot use, and an operator has to unwind it by hand.

Push the invariant into the database, where overlap detection and rollback are free:

alter table stays
  add constraint stays_no_overlap
  exclude using gist (
    room_id with =,
    tstzrange(starts_at, ends_at, '[)') with &&
  ) where (status <> 'cancelled');

create or replace function reserve_booking_group(p_group_id uuid, p_rows jsonb)
returns setof stays
language plpgsql
as $$
declare
  r jsonb;
begin
  for r in select * from jsonb_array_elements(p_rows) loop
    return query
      insert into stays (booking_group_id, room_id, starts_at, ends_at, status)
      values (p_group_id, (r->>'room_id')::uuid,
              (r->>'starts_at')::timestamptz, (r->>'ends_at')::timestamptz, 'held')
      returning *;
  end loop;
end;
$$;

An exclusion constraint violation on row 3 aborts the whole function, and the caller's transaction rolls rows 1 and 2 back. No compensating logic to write, no compensating logic to get wrong. The payment side then attaches one intent to the group ID, so refunds and captures have a single object to act on.

---

7. Count Failures Once

Alerting has an idempotency problem of its own. A nightly job that reports the same failed auto-top-up in every run inflates the metric and trains the on-call team to ignore it. Deduplicate on a natural key:

insert into billing_failures (facility_id, kind, occurred_on, detail)
values ($1, $2, $3::date, $4)
on conflict (facility_id, kind, occurred_on) do nothing;

The same principle scales up: route health-check failures into a tracked issue keyed by check name, update it while the condition persists, close it when it clears. One signal per condition, not one signal per poll.

---

8. Deletions Must Not Erase the Audit Trail

Deleting a guest account should remove personal data, not the record that a door was opened. A cascading foreign key does both.

alter table stays drop constraint stays_guest_id_fkey;
alter table stays add constraint stays_guest_id_fkey
  foreign key (guest_id) references guests(id) on delete set null;

Because the stay now has a null guest, anything the operational view needs must be snapshotted at write time (room label, plan name, pseudonymous reference). The UI, in turn, must render deleted account rather than a blank cell — an empty field reads as a bug and invites a support ticket, while an explicit tombstone reads as a policy.

---

9. Test the Constraints, Not Just the Functions

Every invariant above lives in SQL: partial unique indexes, exclusion constraints, RLS policies, security definer grants. Unit tests with a mocked database cannot see any of them. Run behavioral tests against a real Postgres in CI:

begin;
select plan(3);
select lives_ok(
  $$select issue_checkin_key('11111111-1111-1111-1111-111111111111')$$,
  'first issuance succeeds');
select is(
  (select count(*)::int from access_keys
    where check_in_id = '11111111-1111-1111-1111-111111111111' and status = 'active'),
  1, 'a second call reuses the existing active key');
select throws_ok(
  $$insert into subscriptions (user_id, plan_id) values (auth.uid(), 'p1')$$,
  '42501', null, 'clients cannot insert their own subscription');
select * from finish();
rollback;

Wrapping each case in begin ... rollback keeps the suite hermetic. Pair it with a migration gate in CI that resolves its target environment from its own inputs rather than from the triggering event — a workflow that infers the environment from whoever called it will eventually point a staging migration at production.

---

Summary

Risk	Control
Duplicate credentials under concurrency	Single-flight + partial unique index + advisory lock
Stale pre-issued PIN	Explicit state machine with terminal states, revoke-on-hand-off in one transaction
Cross-tenant writes	Resolve every affected row, fail closed on ambiguity, scope queries by tenant
Secret exposure	Real encryption with key versioning, decrypt RPC granted only to service roles
Retry storms	Classify upstream errors by body code, clamp cached token lifetime
Partial group bookings	Exclusion constraint + single transaction, no compensating logic
Alert fatigue	Deduplicate on a natural key, one signal per condition
Lost audit trail	on delete set null plus snapshotted fields, explicit tombstones in the UI

The common thread: move invariants as close to the data as possible, and make illegal states unrepresentable rather than merely unlikely. Application-level checks are a good first line of defense, but in a system where a mistake unlocks a door, the last line has to be a constraint the code cannot talk its way past.