rckrdmrd
5043a640e4
- Replace old DDL structure with new numbered files (01-24) - Update migrations and seeds for new schema - Clean up deprecated files Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
253 lines
8.8 KiB
PL/PgSQL
253 lines
8.8 KiB
PL/PgSQL
-- =============================================================
|
|
-- ARCHIVO: 02-auth-devices.sql
|
|
-- DESCRIPCION: Dispositivos, credenciales biometricas y sesiones
|
|
-- VERSION: 1.0.0
|
|
-- PROYECTO: ERP-Core V2
|
|
-- FECHA: 2026-01-10
|
|
-- =============================================================
|
|
|
|
-- =====================
|
|
-- TABLA: devices
|
|
-- Dispositivos registrados por usuario
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS auth.devices (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
tenant_id UUID REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Identificacion del dispositivo
|
|
device_uuid VARCHAR(100) NOT NULL,
|
|
device_name VARCHAR(100),
|
|
device_model VARCHAR(100),
|
|
device_brand VARCHAR(50),
|
|
|
|
-- Plataforma
|
|
platform VARCHAR(20) NOT NULL, -- ios, android, web, desktop
|
|
platform_version VARCHAR(20),
|
|
app_version VARCHAR(20),
|
|
|
|
-- Estado
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
is_trusted BOOLEAN DEFAULT FALSE,
|
|
trust_level INTEGER DEFAULT 0, -- 0=none, 1=low, 2=medium, 3=high
|
|
|
|
-- Biometricos habilitados
|
|
biometric_enabled BOOLEAN DEFAULT FALSE,
|
|
biometric_type VARCHAR(50), -- fingerprint, face_id, face_recognition
|
|
|
|
-- Push notifications
|
|
push_token TEXT,
|
|
push_token_updated_at TIMESTAMPTZ,
|
|
|
|
-- Ubicacion ultima conocida
|
|
last_latitude DECIMAL(10, 8),
|
|
last_longitude DECIMAL(11, 8),
|
|
last_location_at TIMESTAMPTZ,
|
|
|
|
-- Seguridad
|
|
last_ip_address INET,
|
|
last_user_agent TEXT,
|
|
|
|
-- Registro
|
|
first_seen_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
last_seen_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
deleted_at TIMESTAMPTZ,
|
|
|
|
UNIQUE(user_id, device_uuid)
|
|
);
|
|
|
|
-- Indices para devices
|
|
CREATE INDEX IF NOT EXISTS idx_devices_user ON auth.devices(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_devices_tenant ON auth.devices(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_devices_uuid ON auth.devices(device_uuid);
|
|
CREATE INDEX IF NOT EXISTS idx_devices_platform ON auth.devices(platform);
|
|
CREATE INDEX IF NOT EXISTS idx_devices_active ON auth.devices(is_active) WHERE is_active = TRUE;
|
|
|
|
-- =====================
|
|
-- TABLA: biometric_credentials
|
|
-- Credenciales biometricas por dispositivo
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS auth.biometric_credentials (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
device_id UUID NOT NULL REFERENCES auth.devices(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
|
|
-- Tipo de biometrico
|
|
biometric_type VARCHAR(50) NOT NULL, -- fingerprint, face_id, face_recognition, iris
|
|
|
|
-- Credencial (public key para WebAuthn/FIDO2)
|
|
credential_id TEXT NOT NULL,
|
|
public_key TEXT NOT NULL,
|
|
algorithm VARCHAR(20) DEFAULT 'ES256',
|
|
|
|
-- Metadata
|
|
credential_name VARCHAR(100), -- "Huella indice derecho", "Face ID iPhone"
|
|
is_primary BOOLEAN DEFAULT FALSE,
|
|
|
|
-- Estado
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
last_used_at TIMESTAMPTZ,
|
|
use_count INTEGER DEFAULT 0,
|
|
|
|
-- Seguridad
|
|
failed_attempts INTEGER DEFAULT 0,
|
|
locked_until TIMESTAMPTZ,
|
|
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
deleted_at TIMESTAMPTZ,
|
|
|
|
UNIQUE(device_id, credential_id)
|
|
);
|
|
|
|
-- Indices para biometric_credentials
|
|
CREATE INDEX IF NOT EXISTS idx_biometric_credentials_device ON auth.biometric_credentials(device_id);
|
|
CREATE INDEX IF NOT EXISTS idx_biometric_credentials_user ON auth.biometric_credentials(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_biometric_credentials_type ON auth.biometric_credentials(biometric_type);
|
|
|
|
-- =====================
|
|
-- TABLA: device_sessions
|
|
-- Sesiones activas por dispositivo
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS auth.device_sessions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
device_id UUID NOT NULL REFERENCES auth.devices(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
tenant_id UUID REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Tokens
|
|
access_token_hash VARCHAR(255) NOT NULL,
|
|
refresh_token_hash VARCHAR(255),
|
|
|
|
-- Metodo de autenticacion
|
|
auth_method VARCHAR(50) NOT NULL, -- password, biometric, oauth, mfa
|
|
|
|
-- Validez
|
|
issued_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
refresh_expires_at TIMESTAMPTZ,
|
|
|
|
-- Estado
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
revoked_at TIMESTAMPTZ,
|
|
revoked_reason VARCHAR(100),
|
|
|
|
-- Ubicacion
|
|
ip_address INET,
|
|
user_agent TEXT,
|
|
latitude DECIMAL(10, 8),
|
|
longitude DECIMAL(11, 8),
|
|
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Indices para device_sessions
|
|
CREATE INDEX IF NOT EXISTS idx_device_sessions_device ON auth.device_sessions(device_id);
|
|
CREATE INDEX IF NOT EXISTS idx_device_sessions_user ON auth.device_sessions(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_device_sessions_tenant ON auth.device_sessions(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_device_sessions_token ON auth.device_sessions(access_token_hash);
|
|
CREATE INDEX IF NOT EXISTS idx_device_sessions_active ON auth.device_sessions(is_active, expires_at) WHERE is_active = TRUE;
|
|
|
|
-- =====================
|
|
-- TABLA: device_activity_log
|
|
-- Log de actividad de dispositivos
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS auth.device_activity_log (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
device_id UUID NOT NULL REFERENCES auth.devices(id) ON DELETE CASCADE,
|
|
user_id UUID REFERENCES auth.users(id),
|
|
|
|
-- Actividad
|
|
activity_type VARCHAR(50) NOT NULL, -- login, logout, biometric_auth, location_update, app_open
|
|
activity_status VARCHAR(20) NOT NULL, -- success, failed, blocked
|
|
|
|
-- Detalles
|
|
details JSONB DEFAULT '{}',
|
|
|
|
-- Ubicacion
|
|
ip_address INET,
|
|
latitude DECIMAL(10, 8),
|
|
longitude DECIMAL(11, 8),
|
|
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Indices para device_activity_log
|
|
CREATE INDEX IF NOT EXISTS idx_device_activity_device ON auth.device_activity_log(device_id);
|
|
CREATE INDEX IF NOT EXISTS idx_device_activity_user ON auth.device_activity_log(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_device_activity_type ON auth.device_activity_log(activity_type);
|
|
CREATE INDEX IF NOT EXISTS idx_device_activity_created ON auth.device_activity_log(created_at DESC);
|
|
|
|
-- Particionar por fecha para mejor rendimiento
|
|
-- CREATE TABLE auth.device_activity_log_y2026m01 PARTITION OF auth.device_activity_log
|
|
-- FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
|
|
|
|
-- =====================
|
|
-- RLS POLICIES
|
|
-- =====================
|
|
ALTER TABLE auth.devices ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY tenant_isolation_devices ON auth.devices
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
ALTER TABLE auth.biometric_credentials ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY user_own_biometrics ON auth.biometric_credentials
|
|
USING (user_id = current_setting('app.current_user_id', true)::uuid);
|
|
|
|
ALTER TABLE auth.device_sessions ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY tenant_isolation_sessions ON auth.device_sessions
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
ALTER TABLE auth.device_activity_log ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY device_owner_activity ON auth.device_activity_log
|
|
USING (device_id IN (
|
|
SELECT id FROM auth.devices
|
|
WHERE tenant_id = current_setting('app.current_tenant_id', true)::uuid
|
|
));
|
|
|
|
-- =====================
|
|
-- FUNCIONES
|
|
-- =====================
|
|
|
|
-- Funcion para actualizar last_seen_at del dispositivo
|
|
CREATE OR REPLACE FUNCTION auth.update_device_last_seen()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
UPDATE auth.devices
|
|
SET last_seen_at = CURRENT_TIMESTAMP
|
|
WHERE id = NEW.device_id;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Trigger para actualizar last_seen_at cuando hay actividad
|
|
CREATE TRIGGER trg_update_device_last_seen
|
|
AFTER INSERT ON auth.device_activity_log
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION auth.update_device_last_seen();
|
|
|
|
-- Funcion para limpiar sesiones expiradas
|
|
CREATE OR REPLACE FUNCTION auth.cleanup_expired_sessions()
|
|
RETURNS INTEGER AS $$
|
|
DECLARE
|
|
deleted_count INTEGER;
|
|
BEGIN
|
|
DELETE FROM auth.device_sessions
|
|
WHERE expires_at < CURRENT_TIMESTAMP
|
|
AND is_active = FALSE;
|
|
|
|
GET DIAGNOSTICS deleted_count = ROW_COUNT;
|
|
RETURN deleted_count;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- =====================
|
|
-- COMENTARIOS DE TABLAS
|
|
-- =====================
|
|
COMMENT ON TABLE auth.devices IS 'Dispositivos registrados por usuario (moviles, web, desktop)';
|
|
COMMENT ON TABLE auth.biometric_credentials IS 'Credenciales biometricas registradas por dispositivo (huella, face ID)';
|
|
COMMENT ON TABLE auth.device_sessions IS 'Sesiones activas por dispositivo con tokens';
|
|
COMMENT ON TABLE auth.device_activity_log IS 'Log de actividad de dispositivos para auditoria';
|