One Audit Writer, Deny-by-Default RLS, and Step-Up Auth

Introduction

A smart lock platform is not a CRUD app with a door attached. Every write can translate into a physical door opening for a person who should — or should not — be standing in front of it. Over the last development cycle, a large share of our commits were not features at all: they were about making the trustworthy path the only path.

This article walks through five patterns that came out of that work:

1. A single audit writer, so "we have logs" is a structural guarantee rather than a habit.
2. Deny-by-default row-level security, with facility scoping enforced on writes, not just reads.
3. Step-up authentication and anti-framing headers for privileged consoles.
4. Capability gating for sandboxes, backed by a machine-checked guard ledger.
5. One resolved configuration object, so every policy decision for a given entity comes from the same source.

All examples are generalized; they are patterns you can lift into any access-control-heavy system.

---

1. One Writer for Every Audit Event

The most common audit logging failure is not a missing table. It is drift: twelve call sites, nine shapes, three of them silently swallowing errors, and the one operation that actually mattered — a refusal — never recorded because the code returned early.

We collapsed every audit path (remote unlock, front desk operations, refunds, manual receipts, integration changes, export jobs, key links) into a single writer with one schema.

export type AuditOutcome = 'succeeded' | 'refused' | 'failed';
export interface AuditEvent {
  /** Stable verb: 'lock.remote_unlock', 'payment.refund', 'integration.update' */
  action: string;
  outcome: AuditOutcome;
  /** Who acted. Service-role callers must declare themselves explicitly. */
  actor: { type: 'user' | 'service' | 'guest'; id: string | null; email?: string };
  /** What was acted upon, always tenant-scoped. */
  target: { facilityId: string; resource: string; resourceId: string | null };
  /** Why a refusal happened — the most valuable field in the record. */
  reason?: string;
  /** Non-PII context only. Validated before write. */
  metadata?: Record<string, string | number | boolean | null>;
  occurredAt: string;
}

The writer itself has three jobs: validate, persist, and forward — without ever becoming the reason a request fails.

export async function recordAuditEvent(event: AuditEvent): Promise<void> {
  const parsed = auditEventSchema.safeParse(event);
  if (!parsed.success) {
    // A malformed audit record is a bug, not a user-facing error.
    logger.error('audit.schema_violation', { issues: parsed.error.issues });
    return;
  }
  const row = parsed.data;
  const results = await Promise.allSettled([
    auditStore.insert(row),
    logDrain.send({ ...row, level: row.outcome === 'succeeded' ? 'info' : 'warn' }),
  ]);
  for (const result of results) {
    if (result.status === 'rejected') {
      logger.error('audit.sink_failed', { action: row.action, error: String(result.reason) });
    }
  }
}

Three design decisions are worth calling out.

Refusals are first-class. outcome: 'refused' with a reason is what lets you answer "did someone repeatedly try to unlock a door they had no claim to?" A log that only contains successes is a receipt, not an audit trail.

Two sinks, independently settled. The durable table is the system of record; the log drain gives alerting and retention. Promise.allSettled means a flaky observability vendor cannot take down an unlock request, and a database hiccup still leaves a trace in the drain.

Audit never throws into the caller. The writer is the one place in the codebase allowed to be best-effort, precisely because every other call site is now guaranteed to use it.

Make the audit pipeline observable

A silent audit pipeline is worse than none, because it manufactures false confidence. We exposed a scoped health probe so a monitor can assert the pipeline is configured, not just that the process is alive:

export async function statusHealth(scope: string | null) {
  if (scope === 'audit') {
    const checks = {
      storeReachable: await auditStore.ping(),
      drainTokenPresent: Boolean(process.env.AUDIT_DRAIN_TOKEN),
      drainReachable: await logDrain.ping(),
    };
    const ok = Object.values(checks).every(Boolean);
    return { status: ok ? 'ok' : 'degraded', checks };
  }
  return { status: 'ok' };
}

Note what is not returned: no secret values, no partial tokens. Presence is a boolean.

---

2. Deny-by-Default RLS — and Enforce It on Writes

Row-level security is widely adopted for reads and widely under-specified for writes. A USING clause filters which rows you can see; only WITH CHECK constrains the rows you may create or move. We found and closed several variants of this gap.

Pattern A: a write policy that ignores the tenant column

-- BROKEN: any authenticated user can insert a row for ANY facility.
create policy plans_insert on plans
  for insert to authenticated
  with check (true);
-- FIXED: the inserted row must belong to a facility the caller is staff of.
drop policy plans_insert on plans;
create policy plans_insert on plans
  for insert to authenticated
  with check (
    exists (
      select 1 from facility_members fm
      where fm.facility_id = plans.facility_id
        and fm.user_id = auth.uid()
        and fm.role in ('owner', 'staff')
    )
  );

The "blind insert" is especially dangerous because the attacker never needs to read anything. They write a row into someone else's tenant and then interact with it through legitimate flows.

Pattern B: client writes to credential tables

API key tables must never be writable from a browser session token. If a signed-in user of tenant A can insert into api_keys with facility_id of tenant B, your entire tenancy model is decorative.

revoke insert, update, delete on api_keys from authenticated, anon;
alter table api_keys enable row level security;
alter table api_keys force row level security;
-- Reads: metadata only, own tenant only. Hash/secret columns live in a separate
-- table or are exposed exclusively through a view.
create policy api_keys_select on api_keys
  for select to authenticated
  using (facility_id = any (current_staff_facility_ids()));

Minting now happens exclusively in a server function running with elevated privilege, after an authorization check and an audit write.

Pattern C: ambiguous helper overloads

Policies that call a helper function inherit that function's correctness. A stale zero-argument overload of an is_platform_admin()-style helper sitting next to a correct one-argument version is a live hazard: PostgreSQL resolves the call, and you may not be looking at the definition you think you are.

-- Drop the ambiguous overload, then rebind every policy that referenced it.
drop function if exists is_platform_admin();
-- Re-create dependent policies against the explicit, parameterized definition.
create policy check_ins_admin_read on check_ins
  for select to authenticated
  using (is_platform_admin(auth.uid()) or facility_id = any (current_staff_facility_ids()));

Pattern D: aggregates leak what row policies protect

Billing and KPI aggregates are frequently exposed as views or materialized views — and views commonly bypass the RLS of their base tables. Closing them to anon and authenticated and serving them only through an authorized server endpoint is the simplest correct answer.

revoke all on billing_totals_mv from anon, authenticated;
revoke all on kpi_daily_mv from anon, authenticated;

Pattern E: bound privileges by a live claim, not a dangling link

For physical access, authorization must be time-bounded. A guest's right to unlock a room is not "they have a reservation," it is "they have a reservation whose stay is live right now, for this facility."

export function canRemoteUnlock(ctx: UnlockContext, now: Date): AuthzDecision {
  const { reservation, lock } = ctx;
  if (reservation.facilityId !== lock.facilityId) {
    return { allowed: false, reason: 'cross_facility_link' };
  }
  if (reservation.status === 'refunded' || reservation.status === 'cancelled') {
    return { allowed: false, reason: `reservation_${reservation.status}` };
  }
  const start = reservation.checkedInAt ?? reservation.startsAt;
  const end = reservation.checkedOutAt ?? reservation.bookedEndsAt;
  if (now < start || now >= end) {
    return { allowed: false, reason: 'outside_live_stay' };
  }
  return { allowed: true };
}

Every branch returns a machine-readable reason, which flows straight into recordAuditEvent with outcome: 'refused'. Unlinked or legacy records get an explicit upper bound (bookedEndsAt) instead of being treated as open-ended — unbounded access grants are how "temporary" becomes "forever."

---

3. Step-Up Authentication and Anti-Framing for Admin Surfaces

Session length is a tradeoff: long sessions are convenient, long privileged sessions are a liability. The answer is not to shorten everyone's session; it is to re-verify a second factor when a user crosses into privileged territory.

const STEP_UP_WINDOW_MS = 24 * 60 * 60 * 1000;
export function requiresStepUp(session: Session, now: number): boolean {
  if (!session.isPlatformAdmin) return false;
  const verifiedAt = session.mfaVerifiedAt ?? 0;
  return now - verifiedAt > STEP_UP_WINDOW_MS;
}
export async function guardAdminRoute(req: Request): Promise<Response | null> {
  const session = await getSession(req);
  if (!session) return redirect('/login');
  if (requiresStepUp(session, Date.now())) {
    await recordAuditEvent({
      action: 'auth.step_up_required',
      outcome: 'refused',
      actor: { type: 'user', id: session.userId },
      target: { facilityId: session.facilityId, resource: 'admin_console', resourceId: null },
      reason: 'mfa_stale',
      occurredAt: new Date().toISOString(),
    });
    return redirect('/auth/verify?next=' + encodeURIComponent(req.url));
  }
  return null;
}

Two details matter in practice. First, the authenticator issuer label must reflect the real environment — if staging and production both register as "Acme", operators will approve a staging prompt while believing it is production. Derive the issuer from the actual API host, not from a build-time guess. Second, the clock used for the window must be server-side.

Alongside step-up, admin consoles should refuse to be framed at all. Clickjacking against a console that contains an "unlock" button is a physical-world attack.

export const securityHeaders = {
  'Content-Security-Policy': "frame-ancestors 'none'",
  'X-Frame-Options': 'DENY',
  'Referrer-Policy': 'strict-origin-when-cross-origin',
  'X-Content-Type-Options': 'nosniff',
  'Strict-Transport-Security': 'max-age=63072000; includeSubDomains; preload',
};

frame-ancestors 'none' is the modern directive; X-Frame-Options remains for older agents.

---

4. Capability Gating for Sandboxes, Backed by a Guard Ledger

Demo and sandbox tenants are a classic blast-radius problem: they look like real tenants, so they can accidentally send real email, call paid APIs, or hit third-party integrations. Blocking these behaviors with scattered if (isDemo) checks is fragile — the checks rot as new code paths appear.

Model the restrictions as explicit capabilities instead:

export type Capability =
  | 'integration.external_call'
  | 'mail.send_arbitrary_recipient'
  | 'ai.paid_invocation'
  | 'search_engine.indexable';
export interface TenantPolicy {
  kind: 'production' | 'sandbox';
  denied: ReadonlySet<Capability>;
  limits: { paidAiCallsPerDay: number };
  mailAllowlist: readonly string[];
}
export function assertCapability(policy: TenantPolicy, cap: Capability): void {
  if (policy.denied.has(cap)) {
    throw new CapabilityRefused(cap, policy.kind);
  }
}

The second half of the pattern is the part teams usually skip: a guard ledger, a checked-in registry of every guard that must exist, verified by a test. If someone deletes a guard or adds a new external integration without one, CI fails.

const GUARD_LEDGER = [
  { capability: 'integration.external_call', module: 'integrations/dispatch.ts' },
  { capability: 'mail.send_arbitrary_recipient', module: 'mail/send.ts' },
  { capability: 'ai.paid_invocation', module: 'ai/invoke.ts' },
] as const;
test('every registered guard is still enforced', async () => {
  const policy = sandboxPolicy();
  for (const entry of GUARD_LEDGER) {
    const mod = await import(`../src/${entry.module}`);
    await expect(mod.run({ policy, payload: fixtureFor(entry.capability) }))
      .rejects.toBeInstanceOf(CapabilityRefused);
  }
});

This converts "we remembered to block that" into an invariant the build enforces. The same idea extends to outbound mail (allowlist the single prospect address), indexing (noindex plus a visible, non-dismissable banner), and spend caps.

---

5. One Resolved Config Per Decision

A subtler class of security bug: two code paths that should agree about policy but compute it differently. In check-in, the question "what does this reservation require?" has several inputs — facility defaults, entrance-level overrides, reservation-level overrides, and feature flags. When the booking screen and the kiosk each merge those inputs with their own precedence rules, you eventually get a door that opens on one surface and refuses on another.

The fix is to resolve once, in one place, and make every consumer read from the resolved object:

export interface ResolvedCheckinConfig {
  readonly requiresGuestRegister: boolean;
  readonly requiresMemberDoorQr: boolean;
  readonly paymentPolicy: 'prepaid' | 'on_site' | 'none';
  readonly windowOpensAt: Date;
  readonly windowClosesAt: Date;
  readonly staffedHours: StaffedHours | null;
  /** Provenance for every field, for debugging and audit. */
  readonly source: Readonly<Record<keyof Omit<ResolvedCheckinConfig, 'source'>, ConfigLayer>>;
}
type ConfigLayer = 'facility_default' | 'entrance' | 'reservation' | 'flag_override';
export function resolveCheckinConfig(input: ConfigInputs): ResolvedCheckinConfig {
  // Precedence is declared once: reservation > entrance > facility default.
  const layers = [input.facilityDefault, input.entrance, input.reservation];
  return freezeWithProvenance(layers, input.flags);
}

Three properties make this worth the refactor:

• readonly everywhere. A resolved policy object that downstream code can mutate is not a policy, it is a suggestion. Freezing it at the boundary turns a whole class of bugs into type errors.
• Provenance per field. When an operator asks "why did this entrance require a QR?", source.requiresMemberDoorQr === 'entrance' answers it immediately, and the same field can be attached to audit metadata.
• One place to validate impossible combinations. For example, prepaid + hourly pricing + no deposit collects nothing. Catching contradictory configurations at resolve time — and surfacing a warning in the admin UI — prevents a silent revenue and access-policy hole.

Retired or disabled sub-resources must also be filtered at resolution time, not at render time. If a retired service is excluded by the UI but still accepted by the API, you have shipped a hidden endpoint.

---

6. Failing Safe at the Edges

Reliability work is security work: a crash in a guest app during checkout is both a bad experience and an unobserved state transition.

Degrade, don't throw, when a client loses read access. A browser that can no longer read a stay (expired token, rotated session) should return a reduced, clearly-labeled view rather than throwing inside a checkout flow:

export async function loadStay(id: string): Promise<StayView> {
  const result = await stays.get(id);
  if (result.error?.code === 'PGRST116' || result.error?.code === '42501') {
    return { kind: 'unreadable', id, hint: 'session_scope_changed' };
  }
  if (result.error) throw new StayLoadError(result.error);
  return { kind: 'full', stay: result.data };
}

Catch background-task rejections. Service worker update checks, analytics beacons, and prefetches reject for mundane network reasons. Unhandled, they flood your error tracker and bury real incidents:

void registration.update().catch((error) => {
  logger.debug('sw.update_failed', { message: String(error) });
});

Tag every error event with a release. Without a release tag, issues you have already fixed reopen forever and triage loses all signal. Upload source maps to the error tracker and strip them from the deployed bundle — then verify that invariant on a schedule:

#!/usr/bin/env bash
set -euo pipefail
for app in admin booking member checkin; do
  url="https://${app}.example.com"
  bundle=$(curl -fsS "$url" | grep -oE '/assets/[A-Za-z0-9_.-]+\.js' | head -n1)
  status=$(curl -s -o /dev/null -w '%{http_code}' "${url}${bundle}.map")
  if [ "$status" != "404" ]; then
    echo "FAIL: ${app} serves a source map (${status})" >&2
    exit 1
  fi
done
echo 'OK: no deployed app serves source maps'

Cap events per device. A single guest device stuck in a render loop can exhaust your error quota and blind you to everything else. A per-device counter with a daily reset is a cheap circuit breaker.

Guard rollout state in CI. Feature flags are access-control surface. We run a scheduled job that diffs production flag state against a committed ledger, and a release gate that fails a release PR if it ships code whose flag is still off in production. Both failure modes — code without a flag, flag without code — are caught before a human has to remember them.

---

Summary

The common thread across all of these changes is replacing discipline with structure:

Instead of	Build
"Remember to log that"	A single recordAuditEvent writer with a fixed schema, including refusals
"The API checks the tenant"	WITH CHECK on every write policy, with force row level security
"Admins have long sessions"	Step-up MFA on privileged routes + frame-ancestors 'none'
"Don't call external APIs in demo"	Capability denial plus a CI-verified guard ledger
"Both screens compute the same policy"	One frozen, provenance-carrying resolved config
"Source maps aren't deployed"	A daily probe that fails loudly

None of these are exotic techniques. What makes them effective is that each one converts a convention — something a reviewer must notice — into an invariant that the type system, the database, or CI enforces on your behalf. In a system that opens physical doors, that difference is the whole game.