20 lines
1.0 KiB
SQL
20 lines
1.0 KiB
SQL
-- Users of this organization's instance. One database == one organization, so no
|
|
-- org_id. Passwords are stored only as argon2id PHC strings.
|
|
--
|
|
-- `updated_at` is maintained manually in UPDATE statements (as in the object table);
|
|
-- there is no auto-update trigger and no update path exists yet.
|
|
CREATE TABLE app_user (
|
|
id UUID PRIMARY KEY,
|
|
email TEXT NOT NULL CHECK (email <> ''),
|
|
password_hash TEXT NOT NULL CHECK (password_hash <> ''),
|
|
role TEXT NOT NULL CHECK (role IN ('admin', 'editor')),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Case-insensitive uniqueness on email, enforced at the database. The application
|
|
-- stores normalized (lowercased) emails and looks up via `lower(email) = $1`, so this
|
|
-- functional unique index both backs those lookups and guarantees no case-variant
|
|
-- duplicate can exist even if a non-normalized value were ever written.
|
|
CREATE UNIQUE INDEX app_user_email_lower_key ON app_user (lower(email));
|