Core Schema (Identity & Auth)

Users

CREATE TABLE core.users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(20) UNIQUE,
    password_hash VARCHAR(255),
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING_VERIFICATION',
    -- PENDING_VERIFICATION, ACTIVE, SUSPENDED, BANNED

    email_verified_at TIMESTAMP,
    phone_verified_at TIMESTAMP,
    last_login_at TIMESTAMP,

    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email ON core.users(email);
CREATE INDEX idx_users_phone ON core.users(phone) WHERE phone IS NOT NULL;
CREATE INDEX idx_users_status ON core.users(status);

User Profiles

CREATE TABLE core.user_profiles (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL UNIQUE REFERENCES core.users(id) ON DELETE CASCADE,

    first_name VARCHAR(100),
    last_name VARCHAR(100),
    middle_name VARCHAR(100),
    date_of_birth DATE,
    avatar_url VARCHAR(500),

    language VARCHAR(10) DEFAULT 'ru',
    timezone VARCHAR(50) DEFAULT 'Europe/Moscow',

    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Authentication Methods

CREATE TABLE core.auth_methods (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES core.users(id) ON DELETE CASCADE,

    type VARCHAR(20) NOT NULL,
    -- EMAIL_PASSWORD, GOOGLE, FACEBOOK, APPLE, TELEGRAM

    provider_user_id VARCHAR(255),  -- For social logins
    credentials JSONB,              -- Encrypted tokens for social

    is_primary BOOLEAN DEFAULT FALSE,
    verified_at TIMESTAMP,

    created_at TIMESTAMP NOT NULL DEFAULT NOW(),

    UNIQUE(user_id, type)
);

CREATE INDEX idx_auth_methods_provider ON core.auth_methods(type, provider_user_id);

Two-Factor Authentication

CREATE TABLE core.two_factor_auth (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL UNIQUE REFERENCES core.users(id) ON DELETE CASCADE,

    method VARCHAR(20) NOT NULL,  -- TOTP, SMS, EMAIL
    secret_encrypted VARCHAR(500),
    backup_codes_encrypted TEXT,  -- JSON array, encrypted

    is_enabled BOOLEAN DEFAULT FALSE,
    enabled_at TIMESTAMP,

    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Sessions

CREATE TABLE core.sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES core.users(id) ON DELETE CASCADE,

    refresh_token_hash VARCHAR(255) NOT NULL,
    device_fingerprint VARCHAR(255),
    ip_address INET,
    user_agent TEXT,

    expires_at TIMESTAMP NOT NULL,
    revoked_at TIMESTAMP,

    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_sessions_user ON core.sessions(user_id);
CREATE INDEX idx_sessions_token ON core.sessions(refresh_token_hash);
CREATE INDEX idx_sessions_expires ON core.sessions(expires_at) WHERE revoked_at IS NULL;

KYC Verification

CREATE TABLE core.kyc_verifications (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES core.users(id) ON DELETE CASCADE,

    status VARCHAR(30) NOT NULL DEFAULT 'NOT_STARTED',
    -- NOT_STARTED, DOCUMENTS_PENDING, SUBMITTED, UNDER_REVIEW,
    -- APPROVED, REJECTED, NEEDS_INFO

    level VARCHAR(20) NOT NULL DEFAULT 'NONE',
    -- NONE, BASIC, STANDARD, ENHANCED

    -- Personal info (encrypted at application level)
    personal_info JSONB,

    submitted_at TIMESTAMP,
    reviewed_at TIMESTAMP,
    reviewed_by UUID REFERENCES core.users(id),
    rejection_reason TEXT,
    expires_at TIMESTAMP,

    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_kyc_user ON core.kyc_verifications(user_id);
CREATE INDEX idx_kyc_status ON core.kyc_verifications(status);

KYC Documents

CREATE TABLE core.kyc_documents (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    verification_id UUID NOT NULL REFERENCES core.kyc_verifications(id) ON DELETE CASCADE,

    document_type VARCHAR(30) NOT NULL,
    -- PASSPORT, ID_CARD, DRIVER_LICENSE, UTILITY_BILL, BANK_STATEMENT, SELFIE

    file_key VARCHAR(500) NOT NULL,  -- S3/storage key
    file_name VARCHAR(255),
    mime_type VARCHAR(100),
    file_size INT,

    status VARCHAR(20) DEFAULT 'PENDING',
    -- PENDING, VERIFIED, REJECTED

    ocr_data JSONB,
    rejection_reason TEXT,

    uploaded_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_kyc_docs_verification ON core.kyc_documents(verification_id);

Roles & Permissions

CREATE TABLE core.roles (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    is_system BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE core.permissions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(100) NOT NULL UNIQUE,  -- e.g., 'mlm.commissions.read'
    name VARCHAR(100) NOT NULL,
    module VARCHAR(50),
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE core.role_permissions (
    role_id UUID NOT NULL REFERENCES core.roles(id) ON DELETE CASCADE,
    permission_id UUID NOT NULL REFERENCES core.permissions(id) ON DELETE CASCADE,
    PRIMARY KEY (role_id, permission_id)
);

CREATE TABLE core.user_roles (
    user_id UUID NOT NULL REFERENCES core.users(id) ON DELETE CASCADE,
    role_id UUID NOT NULL REFERENCES core.roles(id) ON DELETE CASCADE,
    granted_at TIMESTAMP NOT NULL DEFAULT NOW(),
    granted_by UUID REFERENCES core.users(id),
    PRIMARY KEY (user_id, role_id)
);

---

Related Documents

• MLM Schema
• Operations Schema