maarcadetweet/migrations/pds/0004_invite_codes.sql

-- PDS database schema 0004: invite codes for `com.atproto.server.createAccount`.
--
-- Why
--
-- Until now `createAccount` had no gate of any kind: no invite code, no rate
-- limit, and `describeServer` advertised `invite_code_required: false`. That
-- was survivable while the only reachable instance was `127.0.0.1:2583`. It
-- stops being survivable the moment the PDS answers on a public name, because
-- every accepted account is not just a row in `users` — it creates a `repos`
-- head, a key pair the server has to keep, an MST that grows with every write,
-- and firehose events that every subscribed AppView is obliged to index. A
-- single script could mint accounts until the disk filled up, and nothing in
-- the write path would consider that abnormal.
--
-- This migration adds the smallest gate that actually closes that hole: an
-- account may only be created by presenting a code the operator handed out.
-- The gate is opt-in via `PDS_INVITE_REQUIRED` (default `false`, so the dozens
-- of integration tests that create throwaway accounts keep working); the
-- tables below exist unconditionally so that switching the flag on is a
-- restart, not a migration.
--
-- Two tables, not one
-- -------------------
-- A code can be worth more than one account (`--uses 5` for a group of
-- friends, a conference badge, a family). That means "who redeemed this code"
-- is a *set*, not a single column, so it cannot live on the code row. Putting
-- a `redeemed_by TEXT` column on `invite_codes` would have forced either
-- one-code-one-account (losing the multi-use case the operator actually wants)
-- or an array column that no foreign key, index or `COUNT(*)` can reason
-- about. `invite_code_uses` is that set, one row per redemption.
--
-- The redemption *counter* still lives on the code row even though it is
-- derivable from `COUNT(*)` over `invite_code_uses`. That duplication is
-- deliberate and is the entire concurrency story — see below.
--
-- invite_codes
-- ------------
--
--   code        TEXT PRIMARY KEY — the code itself, and the natural key. No
--               surrogate `id`: the code is what the user types, what the
--               operator pastes into a chat window, and what the redeem query
--               looks up, so a second identifier would only add a join.
--               Codes are generated lowercase from a 32-character
--               Crockford-style alphabet (`crates/pds-server/src/invite.rs`),
--               and the server lowercases and trims what the client sends
--               before looking it up. Because every stored code is already
--               lowercase ASCII, that normalisation happens in Rust rather
--               than as `WHERE lower(code) = …`, which would throw away this
--               primary-key index on the hottest lookup this table has.
--
--   created_at  when the operator minted it. Purely for the `invite list`
--               output and for answering "where did this wave of signups come
--               from" after the fact.
--
--   note        free-text label the operator can attach at creation time
--               (`--note "meetup 2026-09"`). Nullable, never interpreted.
--               It exists because a bare list of random strings is unusable
--               a month later.
--
--   max_uses    how many accounts this code may create. `CHECK (max_uses > 0)`
--               because a zero-use code is not a thing you would ever mean to
--               create — it is a typo that would silently hand out a code that
--               can never work.
--
--   used_count  how many it has already created. Kept in sync with
--               `invite_code_uses` inside the same transaction that writes
--               both.
--
--   disabled    a code the operator wants to stop honouring *without* losing
--               the audit trail. Deleting the row would work for the future
--               but would take the `invite_code_uses` rows with it (see the
--               FK below) and with them the record of which accounts came
--               from that code — which is the one question you ask when a
--               code leaks. A boolean keeps the history and is checked in the
--               same `WHERE` clause as the counter, so disabling costs nothing
--               at redeem time.
--
--   CHECK (used_count <= max_uses) — the belt to the redeem query's braces.
--               The application never over-redeems (the conditional UPDATE
--               below makes that impossible), but this constraint means that
--               *no* future query — a hand-written `UPDATE` during an
--               incident, a bug in a later refactor — can hand out more
--               accounts than the operator authorised. The database refuses.
--
-- How the redeem race is closed
-- -----------------------------
-- The obvious implementation is "SELECT the code, check `used_count <
-- max_uses` in Rust, then UPDATE". That is a check-then-act, and two
-- registrations arriving at the same instant with the same last remaining use
-- both read `used_count = 0`, both decide they are allowed, and both write
-- `used_count = 1` — two accounts from a one-use code, with the row still
-- claiming a single redemption.
--
-- So the check and the act are one statement, and the database performs both:
--
--     UPDATE invite_codes
--        SET used_count = used_count + 1
--      WHERE code = $1
--        AND NOT disabled
--        AND used_count < max_uses
--     RETURNING used_count, max_uses;
--
-- Under Postgres's READ COMMITTED isolation the second transaction to reach
-- this row blocks on the row lock the first one took. When the first commits,
-- the second does not proceed with its stale snapshot: it re-reads the updated
-- row and re-evaluates the `WHERE` clause against it (EvalPlanQual). The
-- counter is now `1`, `used_count < max_uses` is false, the row no longer
-- matches, and the statement returns zero rows. Zero rows returned *is* the
-- rejection — the route turns it into `400 InvalidInviteCode` without ever
-- having formed an opinion of its own about whether the code was still valid.
--
-- If the first transaction instead rolls back — the handle turned out to be
-- taken, key generation failed, anything — the lock is released with the
-- counter back at `0` and the waiting transaction's re-check succeeds. The
-- code is only consumed by a registration that actually completed, which is
-- why the redemption is issued inside `create_account`'s existing
-- transaction rather than before it.
--
-- This is also why `used_count` is stored rather than computed. A
-- `COUNT(*) FROM invite_code_uses` has no row to lock — concurrent counters
-- both see the same pre-insert count and both pass. The counter column gives
-- the conditional UPDATE a single row to serialise on.
CREATE TABLE IF NOT EXISTS invite_codes (
    code           TEXT PRIMARY KEY,
    created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    note           TEXT,
    max_uses       INTEGER NOT NULL DEFAULT 1,
    used_count     INTEGER NOT NULL DEFAULT 0,
    disabled       BOOLEAN NOT NULL DEFAULT FALSE,
    CONSTRAINT invite_codes_max_uses_positive CHECK (max_uses > 0),
    CONSTRAINT invite_codes_used_count_sane   CHECK (used_count >= 0 AND used_count <= max_uses)
);

-- `invite list` shows the newest codes first, and that is the only listing
-- this table has. Small table, but the operator runs it interactively and an
-- ordered index keeps the output instant even after a few thousand codes.
CREATE INDEX IF NOT EXISTS invite_codes_created_at_idx
    ON invite_codes (created_at DESC);

-- =====================================================
-- invite_code_uses — which account came from which code
-- =====================================================
--
--   code       FK to `invite_codes(code)` ON DELETE CASCADE. Cascading is the
--              right call *here* (unlike `firehose_events.did` in 0003, which
--              deliberately has no FK) because these rows are meaningless
--              without the code they describe: they exist to answer "which
--              accounts did code X create", and a use-row whose code has been
--              deleted answers nothing. The operator who wants to stop a code
--              but keep the trail sets `disabled` instead of deleting — which
--              is precisely why that column exists.
--
--   did        the account that was created. Intentionally NOT a foreign key
--              to `users(did)`: this is an audit record of something that
--              happened, and it has to survive the account being deleted. If
--              it cascaded from `users`, deleting a spam account would erase
--              the evidence linking it to the code that let it in — the exact
--              moment the link matters most. The trade-off is that a `did`
--              here may point at a user that no longer exists; that is
--              accepted and is what an audit log looks like.
--
--   handle     the handle as it was at creation time, denormalised on
--              purpose. Handles can change, and `users` may be gone entirely
--              (see above); this column is a snapshot so the listing stays
--              readable without a join that may find nothing.
--
--   used_at    when the redemption happened.
--
--   PRIMARY KEY (code, did) — one account can only consume a given code once.
--              This is not the mechanism that enforces the use limit (the
--              conditional UPDATE is), it is a guard against a redemption
--              being recorded twice for one account, which would make
--              `used_count` and this table disagree.
CREATE TABLE IF NOT EXISTS invite_code_uses (
    code           TEXT NOT NULL REFERENCES invite_codes(code) ON DELETE CASCADE,
    did            TEXT NOT NULL,
    handle         TEXT NOT NULL,
    used_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (code, did)
);

-- The reverse lookup: "which code let this account in?". Asked per-account
-- during abuse triage, so it needs its own index — the (code, did) primary
-- key cannot serve a query whose only predicate is `did`.
CREATE INDEX IF NOT EXISTS invite_code_uses_did_idx
    ON invite_code_uses (did);