25 lines
1.1 KiB
SQL
25 lines
1.1 KiB
SQL
-- 001: Users table (user-service)
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
phone VARCHAR(20) UNIQUE,
|
|
email VARCHAR(100) UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
nickname VARCHAR(50),
|
|
avatar_url VARCHAR(500),
|
|
kyc_level SMALLINT NOT NULL DEFAULT 0 CHECK (kyc_level BETWEEN 0 AND 3),
|
|
wallet_mode VARCHAR(10) NOT NULL DEFAULT 'standard' CHECK (wallet_mode IN ('standard', 'external', 'pro')),
|
|
role VARCHAR(20) NOT NULL DEFAULT 'user' CHECK (role IN ('user', 'issuer', 'market_maker', 'admin')),
|
|
status VARCHAR(20) NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'frozen', 'deleted')),
|
|
residence_state VARCHAR(5),
|
|
nationality VARCHAR(5),
|
|
last_login_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_users_phone ON users(phone);
|
|
CREATE INDEX idx_users_email ON users(email);
|
|
CREATE INDEX idx_users_status ON users(status);
|
|
CREATE INDEX idx_users_role ON users(role);
|
|
CREATE INDEX idx_users_kyc_level ON users(kyc_level);
|