OXYGENHUB
◆ draft-ready oxygenhub / crates / hub / migrations / 0012_account_links.sql
770 B · SQL · last changed Password reset and email verification: single-use hashed links, reset by verified email or an administrator's link, verification on every address change, notification mail only to verified addresses, and a file:// mail transport for development 1 month ago source blame history raw
34beb1a7 Andy McCall ↶ 1 
-- Email verification and password reset links (hub spec §5.1.1). Only the
2 
-- BLAKE3 hash of a link's secret is stored.
3
4 
CREATE TABLE account_links (
5 
    id         INTEGER PRIMARY KEY,
6 
    account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
7 
    kind       TEXT NOT NULL CHECK (kind IN ('reset', 'verify')),
8 
    hash       TEXT NOT NULL UNIQUE,
9 
    -- The address a verification link verifies.
10 
    email      TEXT NOT NULL DEFAULT '',
11 
    -- The administrator who issued a reset link, when one did.
12 
    created_by INTEGER REFERENCES accounts(id) ON DELETE SET NULL,
13 
    created_ms INTEGER NOT NULL,
14 
    expires_ms INTEGER NOT NULL,
15 
    used_ms    INTEGER
16 
);
17 
CREATE INDEX account_links_by_account ON account_links (account_id, kind, created_ms DESC);