root
ee21f512a9
Phone becomes the required, unique login identifier; email and name turn optional. Login now accepts either via a single identifier field, refresh re-checks tenant membership, and the dev seed provides phone numbers. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
20 lines
1022 B
SQL
20 lines
1022 B
SQL
CREATE EXTENSION IF NOT EXISTS pg_trgm;
|
|
|
|
CREATE TABLE users (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
email VARCHAR(255),
|
|
phone VARCHAR(20) NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
name VARCHAR(100),
|
|
avatar_url TEXT,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'active',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMPTZ
|
|
);
|
|
CREATE UNIQUE INDEX uk_users_email_active ON users(email) WHERE email IS NOT NULL AND deleted_at IS NULL;
|
|
CREATE UNIQUE INDEX uk_users_phone_active ON users(phone) WHERE deleted_at IS NULL;
|
|
CREATE INDEX idx_users_email_trgm_active ON users USING GIN (email gin_trgm_ops) WHERE email IS NOT NULL AND deleted_at IS NULL;
|
|
CREATE INDEX idx_users_phone_trgm_active ON users USING GIN (phone gin_trgm_ops) WHERE deleted_at IS NULL;
|
|
CREATE INDEX idx_users_name_trgm_active ON users USING GIN (name gin_trgm_ops) WHERE name IS NOT NULL AND deleted_at IS NULL;
|