ryan
50 lines
1.8 KiB
SQL
50 lines
1.8 KiB
SQL
CREATE TABLE IF NOT EXISTS snreg_users (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
username TEXT NOT NULL UNIQUE,
|
|
email TEXT NOT NULL UNIQUE,
|
|
full_name TEXT NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'pending_email',
|
|
email_verified_at TIMESTAMPTZ,
|
|
trusted_domain BOOLEAN NOT NULL DEFAULT FALSE,
|
|
is_admin BOOLEAN NOT NULL DEFAULT FALSE,
|
|
ipa_provisioned_at TIMESTAMPTZ,
|
|
ipa_message TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS snreg_email_tokens (
|
|
token TEXT PRIMARY KEY,
|
|
user_id BIGINT NOT NULL REFERENCES snreg_users(id) ON DELETE CASCADE,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
used_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS snreg_audit_log (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
actor_user_id BIGINT REFERENCES snreg_users(id) ON DELETE SET NULL,
|
|
target_user_id BIGINT REFERENCES snreg_users(id) ON DELETE SET NULL,
|
|
action TEXT NOT NULL,
|
|
detail TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS snreg_users_status_idx ON snreg_users(status);
|
|
CREATE INDEX IF NOT EXISTS snreg_tokens_user_idx ON snreg_email_tokens(user_id);
|
|
|
|
|
|
ALTER TABLE snreg_users ADD COLUMN IF NOT EXISTS oidc_subject TEXT UNIQUE;
|
|
ALTER TABLE snreg_users ADD COLUMN IF NOT EXISTS auth_groups TEXT NOT NULL DEFAULT '';
|
|
CREATE INDEX IF NOT EXISTS snreg_users_oidc_subject_idx ON snreg_users(oidc_subject);
|
|
|
|
CREATE TABLE IF NOT EXISTS snreg_password_tokens (
|
|
token TEXT PRIMARY KEY,
|
|
username TEXT NOT NULL,
|
|
email TEXT NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
used_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS snreg_password_tokens_username_idx ON snreg_password_tokens(username);
|