michangarrito/database/schemas/04-auth.sql

-- =============================================================================
-- MICHANGARRITO - 04 AUTH
-- =============================================================================
-- Autenticación y usuarios
-- =============================================================================

-- Usuarios
CREATE TABLE IF NOT EXISTS auth.users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    -- Identificación
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(100),
    name VARCHAR(100) NOT NULL,

    -- Autenticación
    pin_hash VARCHAR(255),
    biometric_enabled BOOLEAN DEFAULT false,
    biometric_key TEXT,

    -- Rol
    role VARCHAR(20) NOT NULL DEFAULT 'owner',
    permissions JSONB DEFAULT '{}',

    -- Estado
    status VARCHAR(20) DEFAULT 'active',
    last_login_at TIMESTAMPTZ,
    failed_attempts INTEGER DEFAULT 0,
    locked_until TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id, phone)
);

CREATE INDEX idx_users_tenant ON auth.users(tenant_id);
CREATE INDEX idx_users_phone ON auth.users(phone);

CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON auth.users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Sesiones
CREATE TABLE IF NOT EXISTS auth.sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

    token_hash VARCHAR(255) NOT NULL,
    refresh_token_hash VARCHAR(255),

    device_type VARCHAR(20),
    device_info JSONB,
    ip_address VARCHAR(45),

    expires_at TIMESTAMPTZ NOT NULL,
    refresh_expires_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    last_activity_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_sessions_user ON auth.sessions(user_id);
CREATE INDEX idx_sessions_token ON auth.sessions(token_hash);

-- Códigos OTP
CREATE TABLE IF NOT EXISTS auth.otp_codes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    phone VARCHAR(20) NOT NULL,

    code VARCHAR(6) NOT NULL,
    purpose VARCHAR(20) NOT NULL,

    attempts INTEGER DEFAULT 0,
    max_attempts INTEGER DEFAULT 3,

    expires_at TIMESTAMPTZ NOT NULL,
    used_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_otp_phone ON auth.otp_codes(phone, purpose);