Files
moyi-model-intelligence-hub/control-plane/migrations/001_auth.sql

62 lines
2.2 KiB
SQL

CREATE TABLE IF NOT EXISTS users (
id uuid PRIMARY KEY,
account varchar(320) NOT NULL UNIQUE,
account_kind varchar(16) NOT NULL CHECK (account_kind IN ('email', 'phone', 'username')),
password_hash text NOT NULL,
display_name varchar(80) NOT NULL,
role varchar(16) NOT NULL DEFAULT 'user' CHECK (role IN ('user', 'admin')),
status varchar(16) NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'disabled')),
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
last_login_at timestamptz
);
CREATE TABLE IF NOT EXISTS invitation_codes (
id uuid PRIMARY KEY,
code_hash char(64) NOT NULL UNIQUE,
code_prefix varchar(12) NOT NULL,
label varchar(100) NOT NULL,
max_uses integer NOT NULL DEFAULT 1 CHECK (max_uses > 0),
used_count integer NOT NULL DEFAULT 0 CHECK (used_count >= 0 AND used_count <= max_uses),
expires_at timestamptz,
disabled_at timestamptz,
created_by uuid NOT NULL REFERENCES users(id),
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS invitation_redemptions (
invitation_id uuid NOT NULL REFERENCES invitation_codes(id),
user_id uuid NOT NULL REFERENCES users(id),
redeemed_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (invitation_id, user_id)
);
CREATE TABLE IF NOT EXISTS auth_sessions (
id uuid PRIMARY KEY,
token_hash char(64) NOT NULL UNIQUE,
user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
expires_at timestamptz NOT NULL,
revoked_at timestamptz,
created_at timestamptz NOT NULL DEFAULT now(),
last_seen_at timestamptz NOT NULL DEFAULT now(),
user_agent text,
ip_address inet
);
CREATE INDEX IF NOT EXISTS auth_sessions_user_id_idx ON auth_sessions(user_id);
CREATE INDEX IF NOT EXISTS auth_sessions_expires_at_idx ON auth_sessions(expires_at);
CREATE TABLE IF NOT EXISTS auth_audit_log (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
actor_user_id uuid REFERENCES users(id),
event_type varchar(64) NOT NULL,
target_type varchar(32),
target_id text,
ip_address inet,
user_agent text,
details jsonb NOT NULL DEFAULT '{}'::jsonb,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS auth_audit_log_created_at_idx ON auth_audit_log(created_at DESC);