The database
Application code has bugs. Constraints don't care. The schema is written so that illegal rows can't be stored, even by a hand-typed SQL statement.
The tables
Shape: the idea behind the constraints
In TypeScript, each status carries exactly its own fields. The database enforces the same thing, with one CHECK constraint per status. "Shape" is just the name for which timestamp columns a row of that status is allowed to have.
| Status | verified_at | last_seen_at | at_risk_since | lapsed_at | expired_at | transferred_at | abandoned_at |
|---|---|---|---|---|---|---|---|
-- domain_claims_at_risk_shape
CHECK (status <> 'at_risk' OR (
verified_at IS NOT NULL AND
last_seen_at IS NOT NULL AND
at_risk_since IS NOT NULL AND
lapsed_at IS NULL AND
expired_at IS NULL AND
transferred_at IS NULL AND
abandoned_at IS NULL
))
The status <> '...' OR (...) part is how you write "if this status, then these rules" in SQL. For every other status the left side is true, so the rule doesn't apply.
Partial unique indexes
A normal unique index covers every row. A partial one covers only rows matching a condition, so any number of pending claims can exist while only one can be the owner.
The record's name is pinned by the database too
Two CHECK constraints on challenges spell out the record: challenges_name_prefix needs the name to start with _prufy-challenge., and challenges_value_matches_token needs the value to be prufy-domain-verification= followed by the row's own token. So when the product was renamed from Relay, changing CHALLENGE_LABEL and RECORD_VALUE_PREFIX in packages/core/src/constants.ts wasn't enough. The stored rows had to change with it.
ALTER TABLE "challenges" DROP CONSTRAINT "challenges_value_matches_token";
ALTER TABLE "challenges" DROP CONSTRAINT "challenges_name_prefix";
The old constraints would refuse a renamed row, so they have to go before any row changes.
The newer tables follow the same habits
| Constraint | What it refuses |
|---|---|
| sessions_expiry_after_creation | A session that's expired before it starts |
| sessions.token_hash UNIQUE | Two sessions sharing one token |
| accounts_email_lowercase | The same address stored twice in different capitals |
| accounts_verified_needs_email | A confirmation date with no address to confirm |
| accounts_email_token_pair | A confirmation token with no expiry, or an expiry with no token |
| claim_shares_expiry_after_creation | A setup link that's expired before it's made |
| push_subscriptions.endpoint UNIQUE | One browser subscribed twice |
Four columns hold a hash where you might expect a secret: sessions.token_hash, claim_shares.token_hash, accounts.email_token_hash and domain_connect_states.state_hash. Reading the database gives nobody a way in. The Sessions chapter shows why.
The audit log can't be edited
A hand-written migration adds triggers that refuse UPDATE, DELETE and TRUNCATE on claim_events. Application code never tries to, and now it couldn't. That's also why tests create a fresh database rather than emptying tables.
Rows never leave the database layer
mapping.ts is the bridge. claimFromRow turns a row into a core Claim with brands applied. columnsFromState goes the other way and always writes every per-status column, setting unused ones to null. The rest of the system never touches a raw row.