Wordship✳

Game state and access

Two friends on two phones each see a board, but there is one game. It lives in Postgres, and rules in the database decide what each player may write and read. The page sends requests to the app’s server, which works out who is asking. Functions inside the database then check that this player may make the move, take a lock, apply it and return the result.

Authority

SECURITY DEFINER PL/pgSQL · search_path pinned · client writes revoked · FOR UPDATE; room writes: match, then round · one STABLE snapshot · answers null until complete

A move is a call to a function that runs inside Postgres with its own rights, so a modified page cannot write a game row, a placement or a guess.

How it works

The definer functions that make game moves pin their search path. A move locks its game row before it changes anything, and a room write locks the match before its round, always in that order. When two players move at the same moment, one waits for the other, and the fixed order keeps them from deadlocking. One STABLE function builds the whole authorized game view from a single snapshot. Both players’ answers stay null until the game is complete, so the secret word stays on the server until then.

Access

row-level security · anon and authenticated revoked · explicit JSON projection · missing and forbidden look alike

Row-level security and restricted writes separate application tables from server-owned records, with the database checking who may read or change game state.

How it works

Tables only the server should touch, such as guest sessions, receipts, budgets and account links, also revoke every privilege from the anonymous and signed-in roles. The read functions return only an explicit JSON projection of the data. A missing game and a game the caller is not in get the same answer, so game ids cannot be probed for existence.

Live updates

Realtime Broadcast, private channels · RLS on realtime.messages · topic parsed per subscription · send only on own typing channel · RESTRICTIVE beside permissive · guests reconcile over HTTP

The database authorizes each subscription, where policies on realtime.messages parse the topic name and admit only that game’s participants.

How it works

Live updates travel on private Broadcast channels. A client may send only on its own typing channel, and an opponent’s draft becomes visible only once the reader has finished their own solve. Wordship’s permissive policies are paired with restrictive ones, so a broader policy on the same table cannot widen access to a Wordship topic. Guests are not on Broadcast. Their page reconciles over HTTP while it is visible.

Retries

expected slot number · unique (game, guesser, guess_number) · 64-hex operation key · advisory locks: key, then player · OPERATION_KEY_CONFLICT · replayed: true

If the reply is lost and the client sends the same word for the same slot again, the function returns the row it already committed, without a second write, feedback calculation or broadcast.

How it works

A guess names the slot it expects to fill and is written under the game’s row lock and a unique index on game, guesser and guess number. Only the same word replays, and a different word for a filled slot is refused. Creating a room takes a client-made 64-hex operation key under two advisory locks, one for the key and one for the player, and records a receipt. If the same player retries, they get the same room back, marked as replayed. The same key from a different player is refused with a conflict, so it cannot fetch someone else’s room. Practice guesses, friend-room secrets, Salvo shots and challenge guesses use the same expected-slot pattern.

Identity

256-bit token, SHA-256 digest stored · HttpOnly, SameSite=Lax, Secure · 365-day expiry, revocable · account to player link table · one atomic claim · erasure core + tombstone

The server mints a 256-bit token and keeps it in a host-only HttpOnly cookie that page scripts cannot read, and Postgres stores only its SHA-256 digest.

How it works

A friend can play from a link without signing up. The server mints a 256-bit token and keeps it in a host-only HttpOnly cookie that page scripts cannot read. Postgres stores only its SHA-256 digest, so the database holds no usable credential. The session expires after 365 days and can be revoked. A player’s lasting identity is a profile kept separate from any sign-in account and joined to it through a private one-to-one link table. Naming a guest and attaching an account is therefore one atomic claim, and it rewrites no game row. Deleting a guest or an account runs the same erasure core. It removes the profile, notifications and every game the player took part in, both seats’ turns included. Opponents’ profiles stay intact. Erasure also writes a digest-only tombstone so a late request cannot recreate an erased guest, and a daily job erases expired guests in bounded batches.

Abuse

HMAC-SHA256 of the requester IP · INSERT ... ON CONFLICT budget · fails closed without a client IP · 5 symbols, 32-character alphabet · CHECK on the code format · oracle gate

Anonymous room creates and code joins draw on fixed-window budgets kept in Postgres, so the bound holds across cookie resets and concurrent Worker instances.

How it works

Anonymous room creates and code joins draw on fixed-window budgets kept in Postgres and updated by one atomic upsert, so the bound holds across cookie resets and concurrent Worker instances. The bucket key is an HMAC-SHA256 digest of the requester’s address and the operation. The raw address is never the key. The API refuses the request when no trusted client address is present. A spoken room code is 5 symbols from a 32-character alphabet without I, O, 0 or 1, enforced by a CHECK constraint in the database and the same pattern at the API. Replaying a submitted secret word is permitted only in friend rooms where the players chose the words. On Daily and practice games the same path would be an answer-checking oracle, so it is refused there.

Delivery

forward-only migrations · pg_get_functiondef assertion · Vitest checks · test, typecheck, lint, then build · artifact secret scan before deploy · Cloudflare Workers via OpenNext

The schema has moved through forward-only migrations, and every web push to main runs the Vitest suite, typecheck and lint before a guarded OpenNext build.

How it works

The schema has moved through forward-only migrations. A migration that reworks identity reads the deployed function body with pg_get_functiondef and asserts that each expected fragment appears exactly once. It then rewrites the body around an explicit player and raises if any auth.uid() dependency remains, so drift in production aborts the migration before behaviour can change unnoticed. Every web push to main runs the Vitest suite, typecheck and lint before a guarded OpenNext build. That build refuses a checkout with local env files or server secrets in its environment, scans the compiled Worker for secret names and the client chunks for localhost URLs, and re-runs that scan right before wrangler deploy. Releases are serialized and each is tagged with the commit it came from. The app is served from a Cloudflare Worker on its own domain.

Limits. This description comes from source inspection; the live database catalog was not queried and the product’s tests were not rerun for this page. The TypeScript game engines and the PL/pgSQL functions implement the same rules in parallel. The unit tests exercise the TypeScript side. Keeping the SQL in step is a manual discipline, and no automated check compares the two. Guests receive no live Broadcast. No latency, scale, concurrency or player number has been measured. A native client shares the server boundary in code, with Android in closed testing rather than a public store release. Erasure deletes the player’s Wordship data. Wordship now has its own sign-in. Deleting an account removes that sign-in as well as its Wordship play; guests can erase their play from the device they used. The source is private and is not linked from this page.