63 lines
2.0 KiB
SQL
63 lines
2.0 KiB
SQL
CREATE TABLE IF NOT EXISTS users (
|
|
id UUID PRIMARY KEY DEFAULT uuidv7(),
|
|
email TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT,
|
|
google_id VARCHAR(255) UNIQUE,
|
|
auth_provider VARCHAR(50) NOT NULL DEFAULT 'local',
|
|
is_verified BOOLEAN NOT NULL DEFAULT false,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
token_version INT NOT NULL DEFAULT 1,
|
|
refresh_token TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
updated_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_profiles (
|
|
user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
|
|
display_name TEXT,
|
|
full_name TEXT,
|
|
avatar_url TEXT,
|
|
bio TEXT,
|
|
location TEXT,
|
|
website TEXT,
|
|
country_code CHAR(2),
|
|
phone TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
updated_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS roles (
|
|
id UUID PRIMARY KEY DEFAULT uuidv7(),
|
|
name TEXT UNIQUE NOT NULL,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
updated_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_roles (
|
|
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
role_id UUID REFERENCES roles(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (user_id, role_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_verifications (
|
|
id UUID PRIMARY KEY DEFAULT uuidv7(),
|
|
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
verify_type SMALLINT NOT NULL, -- 1 = ID_CARD, 2 = EDUCATION, 3 = EXPERT
|
|
document_url TEXT NOT NULL,
|
|
status SMALLINT NOT NULL DEFAULT 1, -- 1 pending, 2 approved, 3 rejected
|
|
reviewed_by UUID REFERENCES users(id),
|
|
reviewed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_tokens (
|
|
id UUID PRIMARY KEY DEFAULT uuidv7(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
token VARCHAR(255) NOT NULL UNIQUE,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
token_type SMALLINT NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|