-- Email-address verification: a nullable verified timestamp plus single-use,
-- expiring verification tokens. Addresses that predate this migration are
-- trusted (back-filled as verified).
ALTER TABLE user_emails ADD COLUMN verified_at BIGINT;
UPDATE user_emails SET verified_at = created_at;

CREATE TABLE email_tokens (
    token      TEXT PRIMARY KEY,
    email_id   TEXT NOT NULL REFERENCES user_emails(id) ON DELETE CASCADE,
    expires_at BIGINT NOT NULL,
    created_at BIGINT NOT NULL
);
CREATE INDEX idx_email_tokens_email ON email_tokens (email_id);