auth/account_queries.ts

The full account column set, named explicitly so a row read fails loud on schema drift.

SELECT * silently omits a dropped column, which the login lookups then misread: query_account_by_username_or_email filters its result with account.deleted_at === null, so a missing deleted_at column reads back as undefined, undefined === null is false, and *every* login resolves to "not found" (401) — a silent, total auth outage instead of an error. Selecting named columns turns that drift into a hard Postgres column "..." does not exist. Mirrors the Rust side (fuz_auth/src/account_queries.rs), which selects named columns and decodes them positionally. Keep in sync with Account and the account DDL in auth/auth_ddl.ts.

Identifying values snapshotted into a deletion/purge audit event so the identity behind a now-orphaned audit_log id isn't lost. Mirrors the Rust AccountIdentitySnapshot.

The full actor column set — the same fail-loud discipline as ACCOUNT_COLUMNS (this module owns both tables). Keep in sync with Actor and the actor DDL in auth/auth_ddl.ts.

Options for query_admin_account_list.

Find an account by email (case-insensitive).

Find an active account by id (deleted_at IS NULL).

This is the auth-resolution workhorse (build_request_context / build_account_context) and the admin-target lookup, so it deliberately excludes soft-deleted accounts: a tombstoned account must not authenticate (delete = soft, purge = hard). Purge, which must operate on soft-deleted rows too, uses query_purge_account directly.

Find an account by username (case-insensitive).

Find an account by username or email.

If the input contains @, tries email lookup first then username. Otherwise tries username first then email. This supports a single login field that accepts either format.

Excludes soft-deleted accounts (deleted_at IS NULL) — this is the login lookup, and a tombstoned account must not authenticate. The underlying query_account_by_username / query_account_by_email stay unfiltered because the invite-collision checks need to see soft-deleted accounts (usernames/emails stay reserved after a soft-delete).

Check if any account exists.

Soft-delete an account — the reversible tombstone (delete = soft).

Stamps deleted_at + deleted_by (the initiator's actor) on the active row — paired like role_grant's revoked_at / revoked_by, and deliberately leaving updated_at / updated_by untouched (deletion isn't a content edit). Returns the identity snapshot for the account_delete audit event, or undefined when no active row matched (missing or already soft-deleted). Auth resolution (query_account_by_id) already excludes soft-deleted accounts; the caller revokes sessions/tokens.

Reactivate a soft-deleted account — clears the deleted_at / deleted_by tombstone (the inverse of query_account_soft_delete).

Operates only on a currently soft-deleted row (deleted_at IS NOT NULL) and returns the identity snapshot for the account_undelete audit event, or undefined when no soft-deleted row matched (missing or already active). Reactivation does not restore the revoked sessions/tokens — the account is live again but its principals re-auth fresh. updated_at / updated_by stay untouched.

List active (non-tombstoned) actors on an account, ordered by created_at.

Filters deleted_at IS NULL so a soft-deleted actor can never be resolved as the acting actor (and carry its role_grants into the role gate). Used by resolve_acting_actor to resolve the acting actor for a request: 1 actor picks transparently, multiple require an explicit acting field on the request payload. The admin/snapshot handlers that legitimately need tombstoned rows stay on the unfiltered query_actors_by_account.

Find an actor by id.

Soft-delete one actor (sets deleted_at + deleted_by). Returns true when an active row flipped, false when none matched. Emitted per actor alongside the account-level soft-delete.

Reactivate one soft-deleted actor (clears deleted_at / deleted_by). Returns true when a soft-deleted row flipped back, false when none matched. Emitted per actor alongside the account-level reactivation.

List every actor on an account, ordered by created_at, including soft-deleted (tombstoned) rows.

Used by the admin/snapshot handlers (account_delete / account_purge / account_undelete) that must enumerate every actor that ever existed on the account to snapshot names into per-actor audit events. For the acting-actor resolution path — which must never resolve a tombstoned actor — use query_active_actors_by_account. For lookups by id, use query_actor_by_id instead.

List accounts with their actors, active role_grants, and pending inbound role_grant offers for admin display.

Pages the accounts query (one round-trip), then fans out three parallel lookups scoped to the page's account_ids (one round-trip). The role_grants and offers queries use a subquery on actor.account_id so the page bound pushes through to the DB without round-tripping actor.ids back to the application. Pending offers surface the "offer pending — awaiting acceptance" UX; message is intentionally excluded (cross-admin visibility of grantor notes would expand beyond what the audit log discloses).

Create a new account.

Create an account and its actor in a single operation.

For v1, every account gets exactly one actor with the same name as the username.

Create a new actor for an account.

Hard-purge an account — irreversible cascading removal (purge = hard).

Physically deletes the row, cascading to actors, role_grants, sessions, and tokens. Operates on active OR already-soft-deleted rows. Returns the identity snapshot for the account_purge audit event, or undefined when no row matched. The audit_log identity columns carry no FK, so the purged id survives on historical rows for forensic correlation back to the purge event.

Keeper-gated, loud, irreversible — restrict to the keeper credential and confirm explicitly at the call site. The purge name flags the danger.

Update the password hash for an account, conditional on the current stored hash matching expected_hash — the verify-write atomic guard.

The condition closes the race where two concurrent password changes both verify against the pre-update hash (loaded by the authorization phase outside the route's transaction) and would otherwise both UPDATE, silently clobbering whichever lands first. With the conditional WHERE, the second UPDATE matches zero rows; the route reads the boolean return and surfaces 401 instead of pretending success.

Pass the same hash the verify ran against — typically ctx.account.password_hash from the request context.