| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- Core users table. username is citext for case-insensitive uniqueness; |
| 4 | -- display casing is preserved for UI rendering. password_hash stores a |
| 5 | -- PHC-encoded argon2id string; password_algo identifies the encoding so |
| 6 | -- we can roll forward later without DB-side migration. |
| 7 | -- |
| 8 | -- primary_email_id is a forward-reference to user_emails.id (FK added |
| 9 | -- after that table exists in 0004 to avoid circular DDL). |
| 10 | |
| 11 | -- +goose Up |
| 12 | CREATE TABLE users ( |
| 13 | id bigserial PRIMARY KEY, |
| 14 | username citext NOT NULL UNIQUE, |
| 15 | display_name text NOT NULL DEFAULT '', |
| 16 | primary_email_id bigint, |
| 17 | password_hash text NOT NULL, |
| 18 | password_algo text NOT NULL DEFAULT 'argon2id-v1', |
| 19 | password_updated_at timestamptz NOT NULL DEFAULT now(), |
| 20 | email_verified boolean NOT NULL DEFAULT false, |
| 21 | last_login_at timestamptz, |
| 22 | suspended_at timestamptz, |
| 23 | suspended_reason text, |
| 24 | deleted_at timestamptz, |
| 25 | created_at timestamptz NOT NULL DEFAULT now(), |
| 26 | updated_at timestamptz NOT NULL DEFAULT now(), |
| 27 | |
| 28 | CONSTRAINT users_username_length CHECK (char_length(username::text) BETWEEN 1 AND 39), |
| 29 | CONSTRAINT users_password_algo CHECK (password_algo IN ('argon2id-v1')) |
| 30 | ); |
| 31 | |
| 32 | CREATE INDEX users_deleted_at_idx ON users (deleted_at) WHERE deleted_at IS NOT NULL; |
| 33 | CREATE INDEX users_suspended_at_idx ON users (suspended_at) WHERE suspended_at IS NOT NULL; |
| 34 | |
| 35 | CREATE TRIGGER set_updated_at BEFORE UPDATE ON users |
| 36 | FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at(); |
| 37 | |
| 38 | -- +goose Down |
| 39 | DROP TABLE IF EXISTS users; |
| 40 |