# DDL-SPEC: Schema core_auth

## Identificacion

| Campo | Valor |
|-------|-------|
| **Schema** | core_auth |
| **Modulo** | MGN-001 |
| **Version** | 1.0 |
| **Estado** | En Diseño |
| **Autor** | System |
| **Fecha** | 2025-12-05 |

---

## Descripcion General

El schema `core_auth` contiene todas las tablas relacionadas con autenticación, manejo de sesiones, tokens y recuperación de contraseñas. Es el schema más crítico desde el punto de vista de seguridad.

### Alcance

- Gestión de tokens JWT (refresh tokens)
- Control de sesiones activas
- Historial de login/logout
- Recuperación de contraseñas
- Blacklist de tokens revocados

> **Nota:** La tabla `users` reside en `core_users`, no en este schema.

---

## Diagrama Entidad-Relacion

```mermaid
erDiagram
    %% Entidades externas (referencia)
    users ||--o{ refresh_tokens : "tiene"
    users ||--o{ session_history : "registra"
    users ||--o{ login_attempts : "intenta"
    users ||--o{ password_reset_tokens : "solicita"
    users ||--o{ password_history : "historial"

    refresh_tokens {
        uuid id PK
        uuid user_id FK
        uuid tenant_id FK
        varchar token_hash
        uuid family_id
        boolean is_used
        timestamptz used_at
        uuid replaced_by FK
        varchar device_info
        inet ip_address
        timestamptz expires_at
        timestamptz revoked_at
        varchar revoked_reason
        timestamptz created_at
    }

    revoked_tokens {
        uuid id PK
        varchar jti UK
        uuid user_id FK
        uuid tenant_id FK
        varchar token_type
        timestamptz original_exp
        varchar revocation_reason
        timestamptz revoked_at
        timestamptz created_at
    }

    session_history {
        uuid id PK
        uuid user_id FK
        uuid tenant_id FK
        varchar action
        varchar device_info
        inet ip_address
        jsonb metadata
        timestamptz created_at
    }

    login_attempts {
        uuid id PK
        varchar email
        uuid tenant_id FK
        boolean success
        varchar failure_reason
        inet ip_address
        varchar user_agent
        timestamptz attempted_at
    }

    password_reset_tokens {
        uuid id PK
        uuid user_id FK
        uuid tenant_id FK
        varchar token_hash
        integer attempts
        timestamptz expires_at
        timestamptz used_at
        timestamptz created_at
    }

    password_history {
        uuid id PK
        uuid user_id FK
        uuid tenant_id FK
        varchar password_hash
        timestamptz created_at
    }
```

---

## Tablas

### 1. refresh_tokens

Almacena los refresh tokens emitidos para mantener sesiones activas.

#### Estructura

| Columna | Tipo | Nullable | Default | Descripcion |
|---------|------|----------|---------|-------------|
| `id` | UUID | NOT NULL | gen_random_uuid() | PK |
| `user_id` | UUID | NOT NULL | - | FK → core_users.users |
| `tenant_id` | UUID | NOT NULL | - | FK → core_tenants.tenants |
| `jti` | VARCHAR(64) | NOT NULL | - | JWT ID (unique identifier) |
| `token_hash` | VARCHAR(255) | NOT NULL | - | Hash bcrypt del token |
| `family_id` | UUID | NOT NULL | - | Familia de tokens (para rotación) |
| `is_used` | BOOLEAN | NOT NULL | false | Si el token ya fue usado |
| `used_at` | TIMESTAMPTZ | NULL | - | Cuando se usó para refresh |
| `replaced_by` | UUID | NULL | - | Token que lo reemplazó |
| `device_info` | VARCHAR(500) | NULL | - | User-Agent del dispositivo |
| `ip_address` | INET | NULL | - | IP desde donde se creó |
| `expires_at` | TIMESTAMPTZ | NOT NULL | - | Fecha de expiración |
| `revoked_at` | TIMESTAMPTZ | NULL | - | Cuando fue revocado |
| `revoked_reason` | VARCHAR(50) | NULL | - | Razón de revocación |
| `created_at` | TIMESTAMPTZ | NOT NULL | NOW() | Fecha de creación |

#### Constraints

```sql
-- Primary Key
CONSTRAINT pk_refresh_tokens PRIMARY KEY (id),

-- Unique
CONSTRAINT uk_refresh_tokens_jti UNIQUE (jti),

-- Foreign Keys
CONSTRAINT fk_refresh_tokens_user
    FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE,
CONSTRAINT fk_refresh_tokens_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
CONSTRAINT fk_refresh_tokens_replaced_by
    FOREIGN KEY (replaced_by) REFERENCES core_auth.refresh_tokens(id),

-- Check
CONSTRAINT chk_refresh_tokens_revoked_reason
    CHECK (revoked_reason IN ('user_logout', 'logout_all', 'token_rotation', 'security_breach', 'admin_action', 'password_change'))
```

#### Indices

```sql
CREATE INDEX idx_refresh_tokens_user_id ON core_auth.refresh_tokens(user_id);
CREATE INDEX idx_refresh_tokens_tenant_id ON core_auth.refresh_tokens(tenant_id);
CREATE INDEX idx_refresh_tokens_family_id ON core_auth.refresh_tokens(family_id);
CREATE INDEX idx_refresh_tokens_expires_at ON core_auth.refresh_tokens(expires_at) WHERE revoked_at IS NULL;
CREATE INDEX idx_refresh_tokens_active ON core_auth.refresh_tokens(user_id, tenant_id) WHERE revoked_at IS NULL AND is_used = false;
```

---

### 2. revoked_tokens

Blacklist de access tokens revocados (para invalidación inmediata).

#### Estructura

| Columna | Tipo | Nullable | Default | Descripcion |
|---------|------|----------|---------|-------------|
| `id` | UUID | NOT NULL | gen_random_uuid() | PK |
| `jti` | VARCHAR(64) | NOT NULL | - | JWT ID del token revocado |
| `user_id` | UUID | NOT NULL | - | Usuario dueño del token |
| `tenant_id` | UUID | NOT NULL | - | Tenant del token |
| `token_type` | VARCHAR(20) | NOT NULL | 'access' | Tipo: access, refresh |
| `original_exp` | TIMESTAMPTZ | NOT NULL | - | Expiración original del token |
| `revocation_reason` | VARCHAR(50) | NOT NULL | - | Razón de revocación |
| `revoked_at` | TIMESTAMPTZ | NOT NULL | NOW() | Cuando se revocó |
| `created_at` | TIMESTAMPTZ | NOT NULL | NOW() | Fecha de creación |

#### Constraints

```sql
CONSTRAINT pk_revoked_tokens PRIMARY KEY (id),
CONSTRAINT uk_revoked_tokens_jti UNIQUE (jti),
CONSTRAINT fk_revoked_tokens_user
    FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE,
CONSTRAINT fk_revoked_tokens_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
CONSTRAINT chk_revoked_tokens_type CHECK (token_type IN ('access', 'refresh'))
```

#### Indices

```sql
CREATE INDEX idx_revoked_tokens_jti ON core_auth.revoked_tokens(jti);
CREATE INDEX idx_revoked_tokens_exp ON core_auth.revoked_tokens(original_exp);
```

> **Nota:** Esta tabla es un fallback. La blacklist principal debe estar en Redis para mejor performance.

---

### 3. session_history

Historial de eventos de sesión para auditoría.

#### Estructura

| Columna | Tipo | Nullable | Default | Descripcion |
|---------|------|----------|---------|-------------|
| `id` | UUID | NOT NULL | gen_random_uuid() | PK |
| `user_id` | UUID | NOT NULL | - | Usuario |
| `tenant_id` | UUID | NOT NULL | - | Tenant |
| `action` | VARCHAR(30) | NOT NULL | - | Tipo de evento |
| `device_info` | VARCHAR(500) | NULL | - | User-Agent |
| `ip_address` | INET | NULL | - | IP del cliente |
| `metadata` | JSONB | NULL | '{}' | Datos adicionales |
| `created_at` | TIMESTAMPTZ | NOT NULL | NOW() | Timestamp del evento |

#### Constraints

```sql
CONSTRAINT pk_session_history PRIMARY KEY (id),
CONSTRAINT fk_session_history_user
    FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE,
CONSTRAINT fk_session_history_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
CONSTRAINT chk_session_history_action
    CHECK (action IN ('login', 'logout', 'logout_all', 'refresh', 'password_change', 'password_reset', 'account_locked', 'account_unlocked', 'mfa_enabled', 'mfa_disabled'))
```

#### Indices

```sql
CREATE INDEX idx_session_history_user_id ON core_auth.session_history(user_id);
CREATE INDEX idx_session_history_tenant_id ON core_auth.session_history(tenant_id);
CREATE INDEX idx_session_history_created_at ON core_auth.session_history(created_at DESC);
CREATE INDEX idx_session_history_action ON core_auth.session_history(action);
```

---

### 4. login_attempts

Registro de intentos de login (exitosos y fallidos) para seguridad.

#### Estructura

| Columna | Tipo | Nullable | Default | Descripcion |
|---------|------|----------|---------|-------------|
| `id` | UUID | NOT NULL | gen_random_uuid() | PK |
| `email` | VARCHAR(255) | NOT NULL | - | Email usado en intento |
| `tenant_id` | UUID | NULL | - | Tenant (si aplica) |
| `user_id` | UUID | NULL | - | Usuario (si existe) |
| `success` | BOOLEAN | NOT NULL | - | Si fue exitoso |
| `failure_reason` | VARCHAR(50) | NULL | - | Razón de fallo |
| `ip_address` | INET | NOT NULL | - | IP del cliente |
| `user_agent` | VARCHAR(500) | NULL | - | User-Agent |
| `attempted_at` | TIMESTAMPTZ | NOT NULL | NOW() | Timestamp del intento |

#### Constraints

```sql
CONSTRAINT pk_login_attempts PRIMARY KEY (id),
CONSTRAINT fk_login_attempts_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenants.tenants(id) ON DELETE SET NULL,
CONSTRAINT fk_login_attempts_user
    FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE SET NULL,
CONSTRAINT chk_login_attempts_failure_reason
    CHECK (failure_reason IS NULL OR failure_reason IN ('invalid_credentials', 'account_locked', 'account_inactive', 'tenant_inactive', 'mfa_required', 'mfa_failed'))
```

#### Indices

```sql
CREATE INDEX idx_login_attempts_email ON core_auth.login_attempts(email);
CREATE INDEX idx_login_attempts_ip ON core_auth.login_attempts(ip_address);
CREATE INDEX idx_login_attempts_attempted_at ON core_auth.login_attempts(attempted_at DESC);
CREATE INDEX idx_login_attempts_email_ip_recent ON core_auth.login_attempts(email, ip_address, attempted_at DESC);
```

---

### 5. password_reset_tokens

Tokens para recuperación de contraseña.

#### Estructura

| Columna | Tipo | Nullable | Default | Descripcion |
|---------|------|----------|---------|-------------|
| `id` | UUID | NOT NULL | gen_random_uuid() | PK |
| `user_id` | UUID | NOT NULL | - | Usuario |
| `tenant_id` | UUID | NOT NULL | - | Tenant |
| `token_hash` | VARCHAR(255) | NOT NULL | - | Hash del token |
| `attempts` | INTEGER | NOT NULL | 0 | Intentos de uso |
| `expires_at` | TIMESTAMPTZ | NOT NULL | - | Expiración (1 hora) |
| `used_at` | TIMESTAMPTZ | NULL | - | Cuando se usó |
| `invalidated_at` | TIMESTAMPTZ | NULL | - | Cuando se invalidó |
| `created_at` | TIMESTAMPTZ | NOT NULL | NOW() | Fecha de creación |

#### Constraints

```sql
CONSTRAINT pk_password_reset_tokens PRIMARY KEY (id),
CONSTRAINT fk_password_reset_tokens_user
    FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE,
CONSTRAINT fk_password_reset_tokens_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
CONSTRAINT chk_password_reset_tokens_attempts CHECK (attempts >= 0 AND attempts <= 3)
```

#### Indices

```sql
CREATE INDEX idx_password_reset_tokens_user_id ON core_auth.password_reset_tokens(user_id);
CREATE INDEX idx_password_reset_tokens_expires_at ON core_auth.password_reset_tokens(expires_at);
CREATE INDEX idx_password_reset_tokens_active ON core_auth.password_reset_tokens(user_id)
    WHERE used_at IS NULL AND invalidated_at IS NULL;
```

---

### 6. password_history

Historial de contraseñas para evitar reutilización.

#### Estructura

| Columna | Tipo | Nullable | Default | Descripcion |
|---------|------|----------|---------|-------------|
| `id` | UUID | NOT NULL | gen_random_uuid() | PK |
| `user_id` | UUID | NOT NULL | - | Usuario |
| `tenant_id` | UUID | NOT NULL | - | Tenant |
| `password_hash` | VARCHAR(255) | NOT NULL | - | Hash de la contraseña |
| `created_at` | TIMESTAMPTZ | NOT NULL | NOW() | Cuando se estableció |

#### Constraints

```sql
CONSTRAINT pk_password_history PRIMARY KEY (id),
CONSTRAINT fk_password_history_user
    FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE,
CONSTRAINT fk_password_history_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenants.tenants(id) ON DELETE CASCADE
```

#### Indices

```sql
CREATE INDEX idx_password_history_user_id ON core_auth.password_history(user_id);
CREATE INDEX idx_password_history_user_recent ON core_auth.password_history(user_id, created_at DESC);
```

---

## Row Level Security (RLS)

### Politicas de Seguridad

```sql
-- Habilitar RLS en todas las tablas
ALTER TABLE core_auth.refresh_tokens ENABLE ROW LEVEL SECURITY;
ALTER TABLE core_auth.revoked_tokens ENABLE ROW LEVEL SECURITY;
ALTER TABLE core_auth.session_history ENABLE ROW LEVEL SECURITY;
ALTER TABLE core_auth.login_attempts ENABLE ROW LEVEL SECURITY;
ALTER TABLE core_auth.password_reset_tokens ENABLE ROW LEVEL SECURITY;
ALTER TABLE core_auth.password_history ENABLE ROW LEVEL SECURITY;

-- Politica: Solo ver tokens del propio tenant
CREATE POLICY tenant_isolation_refresh_tokens ON core_auth.refresh_tokens
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

CREATE POLICY tenant_isolation_revoked_tokens ON core_auth.revoked_tokens
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

CREATE POLICY tenant_isolation_session_history ON core_auth.session_history
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

CREATE POLICY tenant_isolation_login_attempts ON core_auth.login_attempts
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid OR tenant_id IS NULL);

CREATE POLICY tenant_isolation_password_reset ON core_auth.password_reset_tokens
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

CREATE POLICY tenant_isolation_password_history ON core_auth.password_history
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);
```

---

## Triggers

### 1. Limpieza automática de tokens expirados

```sql
-- Función para limpiar tokens expirados
CREATE OR REPLACE FUNCTION core_auth.cleanup_expired_tokens()
RETURNS TRIGGER AS $$
BEGIN
    -- Eliminar refresh tokens expirados hace más de 7 días
    DELETE FROM core_auth.refresh_tokens
    WHERE expires_at < NOW() - INTERVAL '7 days';

    -- Eliminar revoked tokens cuyo original_exp pasó
    DELETE FROM core_auth.revoked_tokens
    WHERE original_exp < NOW();

    -- Eliminar password_reset_tokens expirados hace más de 24 horas
    DELETE FROM core_auth.password_reset_tokens
    WHERE expires_at < NOW() - INTERVAL '24 hours';

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Nota: Este trigger se ejecuta via pg_cron o job scheduler, no en cada INSERT
```

### 2. Invalidar tokens anteriores al crear nuevo reset

```sql
CREATE OR REPLACE FUNCTION core_auth.invalidate_previous_reset_tokens()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE core_auth.password_reset_tokens
    SET invalidated_at = NOW()
    WHERE user_id = NEW.user_id
      AND id != NEW.id
      AND used_at IS NULL
      AND invalidated_at IS NULL;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_invalidate_previous_reset_tokens
AFTER INSERT ON core_auth.password_reset_tokens
FOR EACH ROW
EXECUTE FUNCTION core_auth.invalidate_previous_reset_tokens();
```

### 3. Mantener solo los últimos 5 passwords en historial

```sql
CREATE OR REPLACE FUNCTION core_auth.limit_password_history()
RETURNS TRIGGER AS $$
BEGIN
    -- Eliminar passwords antiguos, mantener solo los últimos 5
    DELETE FROM core_auth.password_history
    WHERE user_id = NEW.user_id
      AND id NOT IN (
          SELECT id FROM core_auth.password_history
          WHERE user_id = NEW.user_id
          ORDER BY created_at DESC
          LIMIT 5
      );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_limit_password_history
AFTER INSERT ON core_auth.password_history
FOR EACH ROW
EXECUTE FUNCTION core_auth.limit_password_history();
```

---

## Funciones de Utilidad

### 1. Verificar si token está en blacklist

```sql
CREATE OR REPLACE FUNCTION core_auth.is_token_revoked(p_jti VARCHAR)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM core_auth.revoked_tokens
        WHERE jti = p_jti
          AND original_exp > NOW()
    );
END;
$$ LANGUAGE plpgsql STABLE;
```

### 2. Contar intentos fallidos recientes

```sql
CREATE OR REPLACE FUNCTION core_auth.count_recent_failed_attempts(
    p_email VARCHAR,
    p_minutes INTEGER DEFAULT 30
)
RETURNS INTEGER AS $$
BEGIN
    RETURN (
        SELECT COUNT(*)
        FROM core_auth.login_attempts
        WHERE email = p_email
          AND success = false
          AND attempted_at > NOW() - (p_minutes || ' minutes')::INTERVAL
    );
END;
$$ LANGUAGE plpgsql STABLE;
```

### 3. Revocar todos los tokens de un usuario

```sql
CREATE OR REPLACE FUNCTION core_auth.revoke_all_user_tokens(
    p_user_id UUID,
    p_reason VARCHAR DEFAULT 'admin_action'
)
RETURNS INTEGER AS $$
DECLARE
    affected_count INTEGER;
BEGIN
    UPDATE core_auth.refresh_tokens
    SET revoked_at = NOW(),
        revoked_reason = p_reason
    WHERE user_id = p_user_id
      AND revoked_at IS NULL;

    GET DIAGNOSTICS affected_count = ROW_COUNT;
    RETURN affected_count;
END;
$$ LANGUAGE plpgsql;
```

---

## Scripts de Migración

### Crear Schema

```sql
-- 001_create_schema_core_auth.sql
CREATE SCHEMA IF NOT EXISTS core_auth;

COMMENT ON SCHEMA core_auth IS 'Schema para autenticación, tokens y sesiones';
```

### Crear Tablas

```sql
-- 002_create_tables_core_auth.sql

-- refresh_tokens
CREATE TABLE IF NOT EXISTS core_auth.refresh_tokens (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    jti VARCHAR(64) NOT NULL UNIQUE,
    token_hash VARCHAR(255) NOT NULL,
    family_id UUID NOT NULL,
    is_used BOOLEAN NOT NULL DEFAULT false,
    used_at TIMESTAMPTZ,
    replaced_by UUID,
    device_info VARCHAR(500),
    ip_address INET,
    expires_at TIMESTAMPTZ NOT NULL,
    revoked_at TIMESTAMPTZ,
    revoked_reason VARCHAR(50),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT fk_refresh_tokens_replaced_by
        FOREIGN KEY (replaced_by) REFERENCES core_auth.refresh_tokens(id),
    CONSTRAINT chk_refresh_tokens_revoked_reason
        CHECK (revoked_reason IS NULL OR revoked_reason IN
            ('user_logout', 'logout_all', 'token_rotation', 'security_breach', 'admin_action', 'password_change'))
);

-- revoked_tokens
CREATE TABLE IF NOT EXISTS core_auth.revoked_tokens (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    jti VARCHAR(64) NOT NULL UNIQUE,
    user_id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    token_type VARCHAR(20) NOT NULL DEFAULT 'access',
    original_exp TIMESTAMPTZ NOT NULL,
    revocation_reason VARCHAR(50) NOT NULL,
    revoked_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT chk_revoked_tokens_type CHECK (token_type IN ('access', 'refresh'))
);

-- session_history
CREATE TABLE IF NOT EXISTS core_auth.session_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    action VARCHAR(30) NOT NULL,
    device_info VARCHAR(500),
    ip_address INET,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT chk_session_history_action CHECK (action IN
        ('login', 'logout', 'logout_all', 'refresh', 'password_change',
         'password_reset', 'account_locked', 'account_unlocked', 'mfa_enabled', 'mfa_disabled'))
);

-- login_attempts
CREATE TABLE IF NOT EXISTS core_auth.login_attempts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL,
    tenant_id UUID,
    user_id UUID,
    success BOOLEAN NOT NULL,
    failure_reason VARCHAR(50),
    ip_address INET NOT NULL,
    user_agent VARCHAR(500),
    attempted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT chk_login_attempts_failure_reason CHECK (
        failure_reason IS NULL OR failure_reason IN
        ('invalid_credentials', 'account_locked', 'account_inactive',
         'tenant_inactive', 'mfa_required', 'mfa_failed'))
);

-- password_reset_tokens
CREATE TABLE IF NOT EXISTS core_auth.password_reset_tokens (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    attempts INTEGER NOT NULL DEFAULT 0,
    expires_at TIMESTAMPTZ NOT NULL,
    used_at TIMESTAMPTZ,
    invalidated_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT chk_password_reset_tokens_attempts CHECK (attempts >= 0 AND attempts <= 3)
);

-- password_history
CREATE TABLE IF NOT EXISTS core_auth.password_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
```

### Crear Indices

```sql
-- 003_create_indexes_core_auth.sql

-- refresh_tokens
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_user_id ON core_auth.refresh_tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_tenant_id ON core_auth.refresh_tokens(tenant_id);
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_family_id ON core_auth.refresh_tokens(family_id);
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_expires_at ON core_auth.refresh_tokens(expires_at) WHERE revoked_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_active ON core_auth.refresh_tokens(user_id, tenant_id) WHERE revoked_at IS NULL AND is_used = false;

-- revoked_tokens
CREATE INDEX IF NOT EXISTS idx_revoked_tokens_jti ON core_auth.revoked_tokens(jti);
CREATE INDEX IF NOT EXISTS idx_revoked_tokens_exp ON core_auth.revoked_tokens(original_exp);

-- session_history
CREATE INDEX IF NOT EXISTS idx_session_history_user_id ON core_auth.session_history(user_id);
CREATE INDEX IF NOT EXISTS idx_session_history_tenant_id ON core_auth.session_history(tenant_id);
CREATE INDEX IF NOT EXISTS idx_session_history_created_at ON core_auth.session_history(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_session_history_action ON core_auth.session_history(action);

-- login_attempts
CREATE INDEX IF NOT EXISTS idx_login_attempts_email ON core_auth.login_attempts(email);
CREATE INDEX IF NOT EXISTS idx_login_attempts_ip ON core_auth.login_attempts(ip_address);
CREATE INDEX IF NOT EXISTS idx_login_attempts_attempted_at ON core_auth.login_attempts(attempted_at DESC);
CREATE INDEX IF NOT EXISTS idx_login_attempts_email_ip_recent ON core_auth.login_attempts(email, ip_address, attempted_at DESC);

-- password_reset_tokens
CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_user_id ON core_auth.password_reset_tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_expires_at ON core_auth.password_reset_tokens(expires_at);
CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_active ON core_auth.password_reset_tokens(user_id)
    WHERE used_at IS NULL AND invalidated_at IS NULL;

-- password_history
CREATE INDEX IF NOT EXISTS idx_password_history_user_id ON core_auth.password_history(user_id);
CREATE INDEX IF NOT EXISTS idx_password_history_user_recent ON core_auth.password_history(user_id, created_at DESC);
```

---

## Consideraciones de Performance

| Tabla | Volumen Esperado | Estrategia |
|-------|------------------|------------|
| refresh_tokens | Alto (miles/día) | Particionamiento por fecha, cleanup job |
| revoked_tokens | Medio | TTL automático, Redis como primario |
| session_history | Alto | Particionamiento por mes, archivado |
| login_attempts | Alto | Retención 90 días, particionamiento |
| password_reset_tokens | Bajo | Cleanup diario |
| password_history | Bajo | Límite de 5 por usuario |

---

## Notas de Seguridad

1. **Nunca almacenar tokens planos** - Solo hashes
2. **RLS obligatorio** - Aislamiento por tenant
3. **Limpieza programada** - Eliminar datos expirados
4. **Auditoría completa** - session_history para compliance
5. **Indices eficientes** - Para consultas de validación rápidas

---

## Historial de Cambios

| Version | Fecha | Autor | Cambios |
|---------|-------|-------|---------|
| 1.0 | 2025-12-05 | System | Creación inicial |

---

## Aprobaciones

| Rol | Nombre | Fecha | Firma |
|-----|--------|-------|-------|
| DBA | - | - | [ ] |
| Tech Lead | - | - | [ ] |
| Security | - | - | [ ] |