Station signup: station-passcode.ts lifecycle module (encrypt, verify, rotate, cooldown)

[jakebromberg]

Problem

The station-signup epic needs a single module owning the passcode's whole lifecycle: generation, encryption, verification, rotation, revocation, cooldown evaluation, and the attempt log. Four downstream issues consume its exported surface, so it must land before them and its API must be settled here rather than assumed by each caller.

Desired end state

shared/authentication/src/station-passcode.ts exists, exporting a named surface from the package barrel.

This is the repo's first encryption-at-rest code — no createCipheriv exists in Backend-Service today. Put the key handling and its threat model in a comment at the top of the module.

Encryption

AES-256-GCM, key from STATION_PASSCODE_KEY, stored as iv:tag:ciphertext base64 in one column.

Be precise about what this buys: the key ships to the EC2 host's .env via set-ec2-env-var.yml, the same file holding DB_PASSWORD. It protects a leaked dump or RDS snapshot; it does not protect against host compromise, where an attacker has both halves. Worth having — dumps outlive and travel further than host access — but not the broader guarantee the phrase usually implies.

Encrypted rather than hashed because the code is meant to be read back: a stationManager reveals the current code and reads it to a stranded DJ by phone, without rotating. Rotating under the two-row cap can invalidate the note everyone else in the room is using.

Key rotation is free and needs no re-encryption: rows live <=14 days and there are <=2, so set a new key and rotate the passcode. Old ciphertext becomes undecryptable garbage, harmless. A decrypt failure must fail closed — refuse — never fall through to a "no match" that would silently disable the gate if the key were wrong.

Generation

Eight characters from a 32-character unambiguous alphabet (no 0/O, 1/l/I): ~10^12, still legible on a sticky note. Generated, never manager-chosen — a manager-typed code will be wxyc2026.

Verification

Both active rows decrypted and compared in constant time, with no early exit; results combined at the end. Returning on first match leaks through response timing which of the two codes was used.

Rotation and the two-row cap

At most two rows active at once (revoked_at IS NULL AND expires_at > now()), enforced at rotation under a lock. Rotation is read-then-insert, so it must be serialized on a station-global constant pg_advisory_xact_lock key inside the rotate transaction. The key must be distinct from the repo's existing advisory-lock keys. Two concurrent rotations would otherwise yield three or four active rows.

The lock is station-global rather than per-passcode because the cap counts rows matching a predicate: the row being inserted does not exist yet, so there is nothing per-row to key on, and two rotations locking different existing rows would both count 2 and proceed.

SELECT ... FOR UPDATE was previously listed here as an alternative. It is not one, and has been removed: row locks cannot lock the absence of rows. With one active row, two rotations both SELECT ... FOR UPDATE; the loser blocks, the winner inserts its new row and commits, the loser then re-evaluates only the row it matched, never sees the phantom insert, counts one active row, and inserts. Three active rows. With zero active rows nothing is locked at all and N concurrent rotations yield N rows. Such an implementation passes every non-concurrent test while failing exactly the acceptance criterion this section states.

Note this is rotation's mechanism only. The use-claim is not lock-based: it is a single conditional UPDATE ... SET use_count = use_count + 1 WHERE id = $1 AND use_count < max_uses RETURNING id, which is atomic on its own and never touches the advisory-lock space. Rotation's lock is therefore the module's only advisory lock, with no deadlock surface.

The cap's rationale is not CPU — decryption is microseconds. It is that every active code is a live credential written on paper somewhere in the building. Two is a rotation overlap window; five is having lost track.

Cooldown

A failed attempt matches no passcode row, so there is nothing to attribute the failure to. Do not increment a per-row counter — incrementing every active row would let a few wrong guesses from anywhere on the internet revoke both live codes and lock out the control room, which is the outage this whole design exists to prevent.

Instead: count failures in station_signup_attempt within a window — but refusal and alerting count different row sets.

• Refusal: only genuine no-match failures (passcode_fail) count. More than 20 in 10 minutes enters a 15-minute cooldown.
• Alerting: all failure outcomes count toward the digest email.

This split is load-bearing. The cooldown check runs before verification, so if a stale-sticky-note failure (a real code that is expired, revoked, or exhausted) counted toward refusal, a room of legitimate DJs would trip a station-global cooldown — and the manager's phoned-in correct code would then be refused too, requiring a second manager action (clear-cooldown) to undo a lockout that legitimate users caused. That is the failure shape #2365 forbids. Decoupling keeps the stale-note alert — the mechanism's primary designed use case — fully intact while never refusing service over it. Excluding these rows costs nothing against attackers: a guesser's random codes match nothing and still feed refusal exactly as before.

evaluateSignupCooldown must therefore return both counts (in-window no-match failures, and in-window all-failures). Settle that return shape here: #2361, #2362, #2363 and #2364 all consume this surface, which is why this module lands first. #2362's status endpoint should surface both so a manager can distinguish "the room is failing on a stale code" from "we are under a guessing flood".

The cooldown is self-healing, needs no manager, and never revokes anything. Revocation stays manual.

A manager clearing the cooldown writes a cooldown_cleared row, and evaluation counts failures only at or after the most recent one — the clear is a floor on the window, not a deletion. Clearing must never delete attempt rows: they are the detection record the cooldown derives from and the 30-day audit the prune job assumes.

Barrel exports

Name the surface in shared/authentication/src/index.ts — not export *, so lifecycle internals stay private, with a line saying why (the barrel already annotates why particular symbols are exported; see the grantsAdminFlag BS#2282 note):

generateStationPasscode, verifyStationPasscode, revealStationPasscode, rotateStationPasscode, revokeStationPasscode, evaluateSignupCooldown, clearSignupCooldown, readRecentSignupAttempts, pruneSignupAttempts

The last three have consumers outside this module: the admin API clears the cooldown and reads counts, and the prune runs from a job.

Tests — split by what they can actually prove

jest.unit.config.ts maps @wxyc/database and any shared/database/src/client path to a chainable stub with canned values, so no atomicity claim can be tested there — a mock enforces nothing and every concurrency case would pass vacuously.

• tests/unit/authentication/station-passcode.test.ts (TypeScript, per jest.unit.config.ts:6): encrypt/decrypt round-trip, a wrong key failing closed, constant-time comparison, cooldown arithmetic, the clear-as-window-floor calculation, code generation and alphabet, no-early-exit combination logic, expiry and revocation predicates.
• tests/integration/station-passcode.spec.js (plain JS, .spec.js — jest.config.json's testMatch is **/tests/integration/?(*.)+(spec).js and all 119 existing files there are .js): the two-row cap under concurrent rotation, and that a failed attempt mutates no passcode row.

Acceptance criteria

• Module exists with the full lifecycle surface
• Barrel exports named individually, with rationale comment
• Decrypt failure fails closed, with a test
• Verification is constant-time and does not short-circuit, with a test
• Two-row cap holds under concurrent rotation (integration test, real Postgres)
• Cooldown never revokes a passcode, with a test
• Cooldown clear acts as a window floor and deletes no rows, with a test

Context

Plan: ~/Downloads/wxyc-station-passcode-signup-plan.md.

Related

Blocked by the schema issue. Blocks the endpoint, admin API, and prune job.

Blocked by

• Station signup: schema for station_passcode, station_signup_attempt, and the review columns #2358

Outcome vocabulary (settled)

passcode_fail must not cover two materially different events. The vocabulary is:

outcome	meaning	passcode_id	feeds refusal	feeds alert
passcode_ok	valid code, use claimed	set	no	no
passcode_fail	matched no row at all	NULL	yes	yes
passcode_expired	matched a real row, past expires_at	set	no	yes
passcode_revoked	matched a real row, revoked_at set	set	no	yes
passcode_exhausted	matched a real row at its use cap, or lost the claim race	set	no	yes
cooldown_refused	refused while in cooldown	NULL	no	no
cooldown_cleared	manager cleared the cooldown	NULL	no	no
passcode_revealed	manager revealed the code	set	no	no

passcode_exhausted also covers the claim-race loser — a correct code whose conditional UPDATE returned zero rows. It is a correct code, so it must never feed refusal.

Enforce the vocabulary with a TypeScript union in this module, not a DB CHECK. The table has exactly one writer by design; a CHECK costs a migration now and another on every future token, and compile-time enforcement catches the same misspelling class.

Classification requires reading inactive rows

Verification compares against the two active rows, so a stale code is otherwise indistinguishable from garbage. Classification must additionally decrypt recently-inactive rows over a bounded horizon (e.g. expires_at/revoked_at within the last 30 days, matching the audit horizon) — unbounded, the scan grows forever, since nothing prunes station_passcode.

Two decrypt-failure policies, not one:

• Active row decrypt failure fails closed — gate integrity.
• Inactive row decrypt failure is silently skipped — after a key rotation, old rows are undecryptable by design, and failing closed on them would break the endpoint for the entire classification horizon.

Classification must run identically on every failure path so failure kinds stay timing-indistinguishable to the client, and the client response stays generic per #2361. The split lives only in the log.

use_count concurrency (settled)

Single conditional statement, no advisory lock and no CHECK constraint:

UPDATE station_passcode SET use_count = use_count + 1
WHERE id = $1 AND use_count < max_uses
RETURNING id;

Under READ COMMITTED, concurrent statements on the same row serialize on the row lock and the loser re-evaluates the predicate against the winner's committed value, so over-issue is impossible by construction. Zero rows returned means the cap was hit → passcode_exhausted.

Validation still runs strictly before the claim — the ordering constraint from #2365 stands. An earlier design that claimed first let fumbled usernames burn the code.

No CHECK (use_count <= max_uses): the invariant that actually matters (two active rows) cannot be expressed as a CHECK anyway, the conditional UPDATE makes the constraint unreachable except through a future second writer, and the acceptance criteria already require a real-Postgres concurrent-claim test.

Production enablement

STATION_PASSCODE_KEY and STATION_SIGNUP_IP_HMAC_KEY are absent from .github/workflows/set-ec2-env-var.yml's secret allowlist (the resolve-step env block and the case "$SECRET_NAME", which hard-fails with "Unsupported secret_name"). Both must be added there in this issue, or the feature cannot be enabled in production.

The ip_hash HMAC derivation is specified in the ip_hash column comment in shared/database/src/schema.ts — reference it, do not restate it. Duplicated spec prose across files is unfixable drift once a migration freezes one copy.

[jakebromberg]

Design decisions settled before implementation

Four open questions in this spec are now closed. The issue body has been updated to match; this comment records the reasoning so the decisions are not silently re-litigated.

1. The outcome vocabulary splits, and refusal and alerting count different row sets.

The cooldown check runs before verification. Under a single passcode_fail token, a room of legitimate DJs working from a stale sticky note trips the station-global cooldown — and the manager's phoned-in correct code is then refused too, because the cooldown blocks everything. Undoing it needs a second manager action. That is the exact failure shape #2365 forbids: a legitimate user's mistake converted into an outage.

Excluding those rows from refusal would normally cost detection, since the stale-note alert is this mechanism's primary designed use case. It does not have to: nothing about sending an email requires refusing service. So refusal counts only genuine no-match failures, while the alert counts all failure outcomes. Both come off the same (outcome, attempted_at) index.

Against attackers the exclusion costs nothing — a guesser's random codes match nothing and feed refusal exactly as before.

2. The use-claim is a single conditional UPDATE, with no advisory lock and no CHECK.

The previous wording — "the same concurrency treatment the use-claim gets" followed by a list of lock mechanisms — reads as though the claim is lock-based. It is not; the plan and #2361 both specify the conditional UPDATE. Under READ COMMITTED that statement is atomic on its own, so over-issue is impossible by construction, with no lock-management surface and no deadlock interaction with rotation.

No CHECK (use_count <= max_uses): the invariant that actually matters — two active rows — cannot be expressed as a CHECK anyway, so a constraint here buys a partial posture at the cost of a migration.

3. SELECT ... FOR UPDATE is removed from the rotation spec as unsound.

It was offered as an equivalent to pg_advisory_xact_lock. It is not: row locks cannot lock the absence of rows. With one active row, two rotations both select it; the loser blocks, the winner inserts and commits, and the loser re-evaluates only the row it matched, never sees the phantom insert, and inserts as well — three active rows. With zero active rows nothing is locked at all.

Code written against that option passes every non-concurrent test and fails exactly the acceptance criterion this issue states. The lock is station-global rather than per-passcode because the cap counts rows matching a predicate, and the row being inserted does not exist yet.

4. evaluateSignupCooldown returns both counts, and the shape is settled here.

In-window no-match failures and in-window all-failures. #2361, #2362, #2363 and #2364 all consume this surface, which is the reason this module ships first. #2362's status endpoint should surface both, so a manager can tell "the room is failing on a stale code" apart from "we are under a guessing flood".

Consequences carried into the body: the claim-race loser is a correct code and must never feed refusal, so it classifies as passcode_exhausted; classification requires decrypting recently-inactive rows over a bounded horizon; and decrypt failure has two policies — active rows fail closed for gate integrity, inactive rows are silently skipped, because after a key rotation old rows are undecryptable by design and failing closed on them would break the endpoint for the whole horizon.

[jakebromberg]

Decision amendments (implemented in #2370)

A blocking review and a design analysis found two coupled defects in the shipped module, both rooted in one claim: that rotating STATION_PASSCODE_KEY was free. The resolution is dual-key decryption plus a key fingerprint, which dissolves the coupling rather than patching either half. This comment records what changed against the decisions settled in the pinned comment above, so none of it is silently re-litigated.

What was actually broken

The module header, docs/env-vars.md and set-ec2-env-var.yml's comment all documented the same one-liner: set a new STATION_PASSCODE_KEY and call rotateStationPasscode. That procedure bricked the signup gate. Pre-rotation rows stay active for up to 14 days, and verifyStationPasscode / revealStationPasscode throw on any undecryptable active row before comparing anything — so every attempt failed closed for the entire window. With revokeStationPasscode having no operator surface until #2362, the only recovery was raw SQL against prod RDS. Two brick shapes, not one: cap-exceeded (two poisoned rows still count as active, so rotation itself threw StationPasscodeCapExceededError and no working code could be minted) and poisoned-verify (an undecryptable active row fails every request closed, so even a freshly minted code could not get through).

Separately, classifyInactiveStationPasscode continued past undecryptable rows into { outcome: 'passcode_fail' } — the refusal token. Undecryptable rows therefore fed the station-global cooldown, so a key misconfiguration could push the station into cooldown on legitimate traffic. That is the "a legitimate user's mistake becomes an outage" shape #2365 forbids, arriving through a path nobody had looked at.

(a) Decision 5 — AMENDED with a third decrypt-failure policy

The pinned comment settled two policies: active row fails closed (gate integrity), inactive row silently skipped (pre-rotation rows are undecryptable by design). Both stand unchanged on the request path.

There is now a third, off the request path: during rotation, an active row that will not decrypt is administratively revoked. rotateStationPasscode decrypts every active row under its existing advisory lock, before the cap count, and revokes any it cannot open with revoked_reason = 'undecryptable_after_key_rotation' and a loud log line.

This is what makes the documented one-step runbook true even when the operator skips setting the previous key, and it fixes both brick shapes at once: revoked rows no longer count toward the two-row cap, and the stale row leaves the active set so the new code actually verifies. It is safe precisely because it is not on the request path — rotation is a deliberate operator action whose entire purpose is to change which codes are live, and a code nobody can decrypt is a code nobody can verify, so revoking it destroys no capability that still existed. The sticky note carrying that code was already dead the moment the key changed; the difference is whether the control room gets a working replacement or stays locked out.

(b) Decision 5 — REFINED: what the skip falls through to

Skip-vs-throw is unchanged. What was never decided is what the skip falls through to, and the first implementation fell through to passcode_fail — the one token that feeds refusal.

It is now passcode_unverifiable, a ninth token (21 characters, fits varchar(24), no migration). Logged when an attempt matches no active row and no decryptable in-horizon inactive row and at least one in-horizon inactive row was skipped as undecryptable. It joins ALERT_OUTCOMES and never feeds refusal.

passcode_id is always NULL on it: the plaintext behind an undecryptable row is unknowable, so attributing the attempt to a specific row would be a guess.

A second producer was added from the same review — an active-row decrypt failure previously threw before any insertSignupAttempt, so a gate that was down for every request left no trace whatsoever in the attempt log that #2362's status endpoint and #2364's digest both read. That now logs passcode_unverifiable (best-effort, so a failed insert can never mask the decrypt error) before throwing, with the row id in the log line rather than in passcode_id, keeping the token's single NULL invariant intact for downstream consumers.

Under dual-key this is a true edge case — corruption, an operator who skipped STATION_PASSCODE_KEY_PREVIOUS, or two rotations inside one 30-day horizon — not the common path it would have been otherwise. It exists so the skip path can never fall through to passcode_fail again.

(c) Decision 7 — AMENDED to state the achieved property honestly

The original wording asked for classification to "run identically on every failure path so failure kinds stay timing-indistinguishable to the client." Literal path-identical timing was never achievable and is not being implemented. cooldown_refused returns before touching any passcode row at all, and passcode_ok differs from every failure by response body — so the property was already false in two places on the day it was written. Burning symmetric time on the exhausted path to approximate it would be a maintenance trap: every future edit would silently erode a guarantee nothing can test.

The property actually achieved, stated honestly:

Timing reveals nothing about active codes beyond what the responses already reveal. Comparison against the active rows is constant-time with no early exit, so which of the two live codes was submitted does not leak. Timing may distinguish a never-valid submission from a formerly-valid one, because classification returns early on a match against a recently-inactive row.

That is accepted. A dead code cannot be used, learning that some string was once valid does not shrink the live 32^8 keyspace by a single candidate, and the cooldown throttles the guessing that would be needed to explore it. The classification split still lives only in the log, and the client response stays generic per #2361.

(d) The dual-key + fingerprint decision, and why it dissolves the coupling

Dual-key. STATION_PASSCODE_KEY_PREVIOUS (optional) joins a key ring consulted for decryption only. Encryption always uses the current key, so the old key drains out of the table on its own and is never written to again. An active row fails closed only when both keys fail.

The runbook is now: (1) set STATION_PASSCODE_KEY_PREVIOUS to the key in use, (2) set STATION_PASSCODE_KEY to the new key, (3) call rotateStationPasscode, (4) retire the previous key once no row written under it is still active. Step 4 has a wrinkle worth recording: set-ec2-env-var.yml refuses an empty value, so "clear it" is performed by setting it equal to the current key — the ring drops a previous key identical to the current one, making that an exact no-op. A malformed previous key is logged once per process and ignored rather than thrown: taking the gate down over a typo in an optional variable is the outage #2365 forbids, and ignoring it degrades to exactly the old single-key behavior, which auto-revoke then recovers from.

Fingerprint. Stored ciphertext is now keyid:iv:tag:ciphertext, where keyid is the first 8 hex characters of SHA-256 over the raw key bytes. A clean break from the 3-part format with no legacy reader — station_passcode is empty in production, which is the only moment such a change is free, and accepting the old shape would mean guessing which key wrote a row, which is precisely the ambiguity the fingerprint removes. Publishing 32 bits of a digest of a random 32-byte key narrows nothing an attacker holding the dump could act on, and the fact that a key changed is already obvious from the ciphertext not opening.

Why this dissolves the coupling. Rotation and classification were entangled because a single event — a key change — meant two incompatible things depending on which row it hit, and neither could be distinguished from corruption. Under dual-key, rows written before a rotation keep opening across the whole 30-day horizon, so classification stays exact and the refusal count keeps full fidelity against guessers rather than degrading for a month after every key change. The fingerprint then turns the residual failures into three named cases: malformed, unknown_key (the rotation shape — recoverable by setting the previous key, and the shape auto-revoke retires) and corrupt (the fingerprint is one of ours and AES-GCM still rejected it, so no key configuration fixes it). Auto-revoke can act confidently because it knows which of those it is looking at, and every error message tells the operator which lever to reach for.

Also in this PR

• evaluateSignupCooldown's counts moved into SQL — count(*) FILTER (WHERE outcome = 'passcode_fail') plus a plain count(*), with rows fetched only for the trigger math, restricted to passcode_fail (bounded by construction) and capped at 500 newest-first. It previously materialized every failure row in the 25-minute lookback into Node on every request. passcode_exhausted and passcode_unverifiable are refusal-exempt by design — that exemption is the whole availability argument — so they are unbounded: anyone looping a dead code grows the set without ever engaging the cooldown that would stop them, and every subsequent request paid to materialize the pile. The near cliff was Node materialization and row transfer, not the 5s statement_timeout. The LIMIT cannot cause a false negative, since the retained rows are the newest and 500 is far above the 20-failure threshold. The exported shape { inCooldown, noMatchFailureCount, allFailureCount } is unchanged — Station signup: POST /auth/wxyc/station-signup, the passcode-gated self-signup endpoint #2361, Station signup: manager API for reveal, rotate, revoke, clear-cooldown, status, and approve #2362, Station signup: jobs/station-signup-attempt-prune for 30-day attempt-log retention #2363 and Station signup: jobs/station-signup-review for the pending digest and 30-day downgrade #2364 all consume it.
• The use-claim UPDATE re-checks the full active predicate (revoked_at IS NULL AND expires_at > now), not just use_count < max_uses, so a code revoked between the SELECT and the claim cannot still be claimed. Revocation is this design's one authoritative manual lever, since the cooldown deliberately never revokes anything. A loser still classifies as passcode_exhausted — it is the claim-race token by definition, it never feeds refusal either way, and the row's own revoked_at / expires_at carry the reason for anyone auditing later.
• Documentation corrected in all three shipped locations (module header, docs/env-vars.md, set-ec2-env-var.yml's comment), and STATION_PASSCODE_KEY_PREVIOUS added to both halves of the workflow allowlist — the resolve-step env: block and the case "$SECRET_NAME" statement that hard-fails with "Unsupported secret_name".
• The stale passcode_id note in shared/database/src/schema.ts is closed. It still said the vocabulary was "OPEN, for BS#2359 to settle" and that "passcode_fail implies NULL" was not an invariant. This PR is BS#2359: it is an invariant now, and so is the same for passcode_unverifiable. Still no CHECK constraint, for the reason already settled — one writer, compile-time union.