Erasing a user — the contract, and how to not reopen a hole that took four rounds to close

Audience: anyone adding a table that holds user data, adding a row to verification, or touching admin::erase_user. Read this before you write the row, not after.

Signet is sold as a self-hostable auth server with data erasure as a hosted-product obligation. DELETE /admin/v1/users/{id} and POST /api/auth/admin/remove-user both answer 200 with a note saying the user's rows "are permanently gone". That sentence has been false twice. This document exists so it is not false a third time.

1. The one mechanism, and the one thing it cannot do

admin::erase_user is the single erasure mechanism. It walks USER_DEPENDENTS, a list of (table, column) pairs, and for each runs DELETE FROM <table> WHERE <column> = <user_id>, then deletes the user row.

⚠ It can only erase a child that keys on a user id in a REAL COLUMN.

That is not a gap in the list — it is the shape of the list. A row that records its owner inside a JSON blob, as the whole value string, or as an email in the identifier is not merely missing from USER_DEPENDENTS; it cannot be expressed by it, and no amount of auditing the list will find it.

Two consequences that have both actually happened:

• Auditing the list cannot find what the list cannot express. "Is every credential table in USER_DEPENDENTS?" is answerable by reading USER_DEPENDENTS, returns YES, and is worthless. Ask it from the other end: enumerate every WRITER of user data, then ask which table each one writes into.
• oauthAccessToken was unwritten while oauthRefreshToken was latent. The former remains a harmless no-op delete. Once oauthRefreshToken became live, its list entry began erasing hashed OAuth and MCP refresh credentials. Authorization codes and access tokens remain verification rows; both stores are part of the erasure contract.

2. Adding a table that holds user data

1. Give it a real column naming the user ("userId" text, camelCase, double-quoted in the migration — see §5).
2. Add ("yourTable", "userId") to USER_DEPENDENTS, before any table it references.
3. Add a foreign key to "user"("id") as a backstop, not as the mechanism — the MemoryAdapter that most suites run on enforces no foreign keys at all, so a cascade-only fix is invisible to every in-memory test. Green by construction is not green.

3. Adding a row to verification — the sharp edges

verification is not a scratch table. It holds email-verification codes, OTPs, WebAuthn challenges, SIWE nonces, SSO relay state, OAuth request state, password-reset tokens, 2FA challenges — and the live authorization-code/access-token credentials: mcp-code:, mcp-access:, oauth2-code:, oauth2-access:. Refresh tokens moved to the dedicated oauthRefreshToken table; older mcp-refresh: rows are legacy inert data.

Sixteen physical statements insert into this table. Fifteen of them bypass oauth_core::create_verification entirely, using the generic create helpers. So:

⚠ Writing your row the way the file next to you writes its row is how this reopens. The subject parameter on create_verification binds one writer in sixteen. It cannot stop you.

If your row is a credential — anything a bearer can later present in order to act as somebody — it MUST go through oauth_core::create_verification and pass Some(user_id). None is not the safe default; None is the erasure hole.

And it must ALSO carry userId inside its JSON value. Both copies are load-bearing and they are read by different halves of the fix (§4). A row with one copy still works; a row whose two copies disagree is refused as corrupt.

⚠ value is not uniformly JSON across this table — several writers store a bare string, and the password-reset and 2FA-challenge writers store a raw user id as the entire value. Any sweep or backfill that assumes serde_json::from_str(&row["value"]) succeeds is wrong on those. Those two rows are also currently invisible to both erasure halves — an open item, not a settled design.

4. Why there are two mechanisms, and why they are not redundant

• (A) Deletion — the subject column plus the USER_DEPENDENTS entry. Removes the row.
• (B) Liveness — oauth_core::live_credential_subject. Refuses to honour a credential row whose subject no longer exists, whether or not the row was deleted.

They look redundant and are not. (A) cannot reach history — rows written before the column existed carry NULL and are unreachable by an equality delete. (B) can, because it asks about the subject rather than about the row. (B) is also what holds when the erasure loop half-ran (it is not transactional — each delete_many commits independently), when a backup is restored, on a replica, and when a future writer forgets the column.

A test that only observes the end-to-end outcome pins NEITHER, because each mechanism alone satisfies it. Measured: with (A) removed entirely, both end-to-end erasure tests still pass. Any change here needs an isolated regression that fails when one mechanism is removed and the other is left intact.

live_credential_subject reads both copies of the subject and requires them to agree:

⚠ A first-match fallback (column, else blob) is unsafe and was rejected for a specific reason: column names a live user, blob names an erased one, the fallback honours the row on the column — and then every consumer reads the blob and serves the erased subject.

⚠ Honouring a credential and administering one are different verbs. oidc::revoke_access_token deliberately does not check liveness: a check there would make revocation answer 200 while removing nothing, on exactly the rows most in need of deleting.

5. Migration rules that bite silently

• Double-quote the column name. The Postgres adapter introspects information_schema and does no case conversion. ADD COLUMN userId unquoted folds to catalog userid, the JSON key userId then misses, and you get DbError::UnknownColumn — discarded into a generic 500 that does not name the column. Every in-memory test still passes.
• Use text. The adapter maps types through a five-entry allowlist and errors the whole table on anything outside it — so a uuid column breaks every OTP, passkey and 2FA flow, not just yours.
• An unknown field is a hard error, not a dropped key. A binary carrying a new field against an un-migrated database fails every write to that table. database.migrate defaults to true and runs before the adapter is built, so a default-config instance migrates itself at boot — but confirm no instance sets it false before deploying.
• A migration is immutable once it has shipped. sqlx checksums the whole file, comments included, and validates on boot: a comment-only edit makes an already-migrated database refuse to start. Before it ships, correct it freely; after, use a new migration. On a mismatch, reset the database or update the recorded checksum — do not restore the old bytes.
• Nothing ever sweeps this table (delete_many("verification") appears zero times in the tree). Expiry makes a row unusable, never absent.

6. Verify against Postgres, because the suite cannot

Most suites run on MemoryAdapter, which stores rows as maps and accepts any key. It and Postgres disagree about whether your migration is correct, and only one of them is in the suite. Pin the column against real Postgres and read it back through raw SQL rather than through the adapter, so the test cannot agree with a wrong implementation of the thing it is checking.

7. Erasure is not only about retention — it could be REVERSED

Closed 2026-08-02. verify_magic_link_inner and sign_in_email_otp looked the user up by email and, finding none, created the account (emailVerified: true for magic link) and minted a session — so a magic link or sign-in OTP issued before an erasure and redeemed after it reconstituted the erased account under a new id. The row recorded only the ADDRESS the artifact was for, never WHO.

The fix: the mint now BINDS the row to its subject (magic link both copies per §4; email-OTP the userId COLUMN only — its value is the bare "{otp}:{attempts}" string, not JSON), and redemption branches on oauth_core::credential_subject_binding: a live subject signs in by id and never creates, a gone subject is refused, and only a row naming nobody takes the resolve-by-email/create-on-miss sign-up path. A bound row sets the userId column, so admin::erase_user now deletes it (mechanism A); live_credential_subject is the re-expressed two-valued view of the same predicate (mechanism B), for rows the delete cannot reach.

⚠ Still standing — the general rule, which generalises past magic links: a row keyed on an EMAIL is not merely unerasable; it is a standing instruction to re-create the subject. If you add a flow that resolves a user by email (or phone, wallet, or any caller-supplied identifier) and creates on miss, you have added one of these. The tree has six such flows; the fix covered the two that redeem a pre-erasure Signet artifact. The other four (oauth::finish_sign_in, oauth_proxy::finish_sign_in, sso::saml_callback_post, siwe::verify_inner) trigger on a fresh external proof at request time and are deliberately NOT erasure defects — refusing them would mean retaining an erased-identifier blocklist. Preserve that distinction when changing one.