| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- TOTP (time-based one-time password) secrets, one per user. The secret |
| 4 | -- is encrypted at rest with chacha20poly1305 (key from config: secrets.totp_key). |
| 5 | -- secret_nonce is the per-row 12-byte AEAD nonce; never reused for the same key. |
| 6 | -- |
| 7 | -- last_used_counter is the highest TOTP step counter we've accepted; codes |
| 8 | -- whose counter is <= this value are rejected to prevent replay (RFC 6238 §5.2). |
| 9 | -- confirmed_at is NULL during enrollment until the user proves possession of |
| 10 | -- the authenticator with a fresh code. |
| 11 | |
| 12 | -- +goose Up |
| 13 | CREATE TABLE user_totp ( |
| 14 | id bigserial PRIMARY KEY, |
| 15 | user_id bigint NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE, |
| 16 | secret_encrypted bytea NOT NULL, |
| 17 | secret_nonce bytea NOT NULL, |
| 18 | confirmed_at timestamptz, |
| 19 | last_used_counter bigint NOT NULL DEFAULT 0, |
| 20 | created_at timestamptz NOT NULL DEFAULT now(), |
| 21 | updated_at timestamptz NOT NULL DEFAULT now(), |
| 22 | |
| 23 | CONSTRAINT user_totp_nonce_size CHECK (octet_length(secret_nonce) = 12) |
| 24 | ); |
| 25 | |
| 26 | CREATE TRIGGER set_updated_at BEFORE UPDATE ON user_totp |
| 27 | FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at(); |
| 28 | |
| 29 | -- +goose Down |
| 30 | DROP TABLE IF EXISTS user_totp; |
| 31 |