# Auditoría de Base de Datos — SGETECBA

**Fecha:** 2026-06-16
**Motor:** PostgreSQL 18 · `server_encoding = UTF8`
**Esquema:** `sgetecba`
**Estado de la BD:** limpia (solo login) · migraciones `001`–`006` aplicadas
**Dump que acompaña este informe (misma carpeta `BD/MD/`):**
- `sgetecba_16-06-2026.sql` — estructura + datos de login, autocontenido (para correr la BD igual que acá)

---

## 1. Resumen ejecutivo

La BD se vació por completo (solo quedaron `usuarios`, `usuario_roles`, `roles` para el login) y se alineó el esquema al modelo de negocio nuevo. La **lógica estructural es sólida y consistente**: soft-delete homogéneo, auditoría `actualizado_en` en todas las tablas operativas, unicidad bien planteada y sin tablas sin PK.

En esta auditoría se detectaron y **corrigieron** 5 puntos (migration_005 + migration_006). La BD no tiene hallazgos abiertos estructurales.

> ✅ **La BD pasa 13/13 pruebas de integridad y el dump recarga en una base nueva sin un solo error.** Detalle en §9.

| Severidad | Cantidad | Estado |
|---|---|---|
| 🔴 Error de lógica | 2 | ✅ corregidos en 005 |
| 🟡 Inconsistencia / optimización | 3 | ✅ corregidos en 005 |
| 🔵 Decisión de diseño estructural | 1 | ✅ resuelto en 006 |
| 🟢 Observaciones menores | 3 | documentadas |

---

## 2. Cómo correr esta BD (para replicar la auditoría)

```bash
# 1. Crear la base vacía
createdb -h localhost -U postgres sgetecba

# 2. Cargar estructura + login
psql -h localhost -U postgres -d sgetecba -f sgetecba_16-06-2026.sql
```

- Credenciales locales del proyecto: usuario `postgres` / pass `sa` (ver `backend/.env`).
- El dump es autocontenido: incluye `CREATE SCHEMA sgetecba`, la extensión `citext` y todos los objetos.
- ⚠️ **La BD NO trae catálogos** (estados, turnos, grupos, niveles, modalidades, planes de pago, etc.): se vaciaron a propósito. Para poder **operar** (crear estudiantes, ofertas…) hay que re-sembrarlos desde `BD/backups/seed_catalogos_raw.sql`. Para **auditar la estructura** no hace falta.

---

## 3. Inventario estructural (post-006)

| Objeto | Cantidad |
|---|---:|
| Tablas | 49 |
| Vistas | 8 |
| Índices | 196 |
| Triggers | 45 |
| Funciones PL/pgSQL | 5 |
| Foreign keys | 89 |
| CHECK constraints | 364 |
| **FK sin índice de soporte** | **0** ✅ |

Funciones: `fn_establecer_actualizado_en`, `fn_generar_codigo_estudiante`, `fn_un_solo_periodo_actual`, `fn_verificar_choque_horario_estudiante`, `fn_verificar_choque_horario_docente`.

---

## 4. Hallazgos corregidos

### 4.1 En `migration_005` (esta auditoría)

| # | Hallazgo | Corrección |
|---|---|---|
| C1 | **Trigger de código** devolvía NULL en silencio si la carrera no tenía código | Ahora hace `RAISE EXCEPTION` explícito. Formato confirmado `CARRERA-YYNNN` (ej. `CIA-25126`), igual a la imagen |
| C2 | **Choque de horario** (estudiante y docente) solo validaba en `INSERT` | Los triggers ahora cubren `INSERT OR UPDATE OF oferta_asignatura_id` |
| C3 | **Inconsistencia de tipo**: `carreras.modalidad_academica_id` era `bigint` y la PK referida es `smallint` | Unificado a `smallint` (FK preservada) |
| C4 | **14 columnas FK sin índice** → seq-scan en JOINs y cascadas | 14 índices creados (FK sin índice ahora = 0) |

### 4.2 En `migration_006` (esta auditoría)

| # | Hallazgo | Corrección |
|---|---|---|
| C5 | **Plan/estado de pago por materia** (en `inscripciones`): no se puede responder "¿quién pagó?" a nivel de matrícula | Nueva tabla `matriculaciones` (estudiante + carrera + período) con `plan_pago_id`, `estado_pago_id`, `tipo_inscripcion`, `turno`, `grupo`. `inscripciones` pasa a tener `matriculacion_id`. Vista `vw_reporte_pagos` para consultas de pago |

### 4.3 Antes de esta auditoría (`migration_004`)

- Código único por estudiante; `estudiante_carreras` sin código propio (multi-carrera).
- Ofertas `REGULAR` (exclusiva de una carrera) vs `LIBRE` (`carrera_id` NULL).
- Regla `trabaja = true → lugar_trabajo` obligatorio.

---

## 5. Integridad referencial

Reparto de acciones `ON DELETE` en las 83 FKs:

| Acción | FKs | Lectura |
|---|---:|---|
| `RESTRICT` | 41 | Protege catálogos y entidades padre (correcto) |
| `SET NULL` | 24 | Campos opcionales (aprobado_por, registrado_por, periodo…) (correcto) |
| `CASCADE` | 13 | Detalles dependientes (detalle_convalidaciones, prerequisitos…) (correcto) |
| `NO ACTION` | 5 | Sin cláusula explícita — ver 7.3 |

- **Soft-delete** (`eliminado_en`) homogéneo en todas las tablas operativas; los índices parciales filtran `WHERE eliminado_en IS NULL`.
- **Unicidad** correcta y condicionada a soft-delete: CI, correo y código de estudiante únicos solo entre activos; oferta única por combinación; una inscripción activa por (estudiante, oferta); un docente por (docente, oferta).
- 0 tablas sin PRIMARY KEY.

---

## 6. Optimización y velocidad

**Bien resuelto:**
- Índices **parciales** `WHERE eliminado_en IS NULL` → más chicos y rápidos para el caso normal.
- Índices compuestos para los accesos típicos: `ofertas (modalidad, carrera, periodo)`, `detalle_asistencia (estudiante, estado)`, `faltas (estudiante, fecha)`.
- Índice GIN sobre los `jsonb` de staging.
- Columna generada `nombre_completo` con índice para búsqueda.

**Aplicado en 005:** las 83 FKs ya tienen índice de soporte (antes faltaban 14).

**Recomendaciones a futuro (cuando haya datos):**
- Correr `ANALYZE` tras la primera carga real.
- Revisar con `EXPLAIN ANALYZE` los reportes de pago y de cumplimiento horario una vez poblada la BD.
- Evaluar índice en `auditoria_logs (creado_en)` y `(modulo)` si se consulta por fecha/módulo.

---

## 7. Hallazgos abiertos (observaciones)

### 7.1 🟢 `ux_horario_clases_oferta_dia` impide 2 bloques el mismo día
El índice único `(oferta_asignatura_id, dia_semana)` no permite que una materia tenga dos franjas el mismo día (ej. lunes 07–09 y lunes 10–12). Revisar si es intencional; si no, cambiar a `(oferta, dia, hora_inicio)`.

### 7.2 🟢 5 FKs con `NO ACTION` en lugar de `RESTRICT` explícito
`carreras_modalidad_academica_id_fkey`, `docentes_usuario_id_fkey`, `estudiantes_carrera_id_fkey`, `fk_pensum_nivel`, `horario_clases_oferta_asignatura_id_fkey`. Funcionalmente casi idéntico a `RESTRICT`; unificar por consistencia.

### 7.3 🟢 Carrera en código de aplicación
Las vistas de reporte ya derivan carrera de `inscripciones → ofertas` (correcto). Falta auditar el **backend/frontend** por si algún endpoint lee `estudiantes.carrera_id` directo como fuente.

---

## 8. Checklist sugerido para la auditoría del compañero

- [ ] Correr `sgetecba_16-06-2026.sql` y confirmar que la estructura carga sin errores.
- [ ] Validar el formato del código `CIA-25126` contra el padrón real.
- [ ] Revisar `ux_horario_clases_oferta_dia` (7.1): ¿una o varias franjas horarias por día?
- [ ] Decidir si re-sembrar catálogos con los valores actuales o ajustarlos al modelo nuevo.
- [ ] Auditar el código de la app por usos directos de `estudiantes.carrera_id`.

---

## 9. Pruebas de validación (13/13 ✅)

Dos pruebas automáticas, ambas en verde el 2026-06-16:

**A. Recarga en limpio** — el dump `sgetecba_16-06-2026.sql` se cargó en una base PostgreSQL **nueva y vacía** con `ON_ERROR_STOP=1`: **0 errores** (exit 0). Garantiza que el dump aplica tal cual en otra máquina.

**B. Batería de integridad** — 13 asserts sobre la base recién cargada, todos OK:

| # | Prueba | Resultado |
|---|---|:--:|
| T1 | Toda tabla tiene PRIMARY KEY | ✅ |
| T2 | Toda FK tiene índice de soporte | ✅ |
| T3 | Tipos de FK consistentes con su PK | ✅ |
| T4 | `actualizado_en` con trigger en todas las tablas | ✅ |
| T5 | Solo el login tiene filas (resto vacío) | ✅ |
| T6 | Las 5 funciones PL/pgSQL existen | ✅ |
| T7 | Choque de horario cubre INSERT y UPDATE | ✅ |
| T8 | Formato de código `CIA-25126` | ✅ |
| T9 | `ofertas.carrera_id` nullable (soporta LIBRE) | ✅ |
| T10 | Constraints de negocio presentes | ✅ |
| T11 | Tabla `matriculaciones` con todas sus columnas | ✅ |
| T12 | Plan/estado de pago NO está en `inscripciones` (movido a `matriculaciones`) | ✅ |
| T13 | Vista `vw_reporte_pagos` presente | ✅ |

**Cómo reproducir** (sobre una base ya cargada con el dump):

```bash
psql -h localhost -U postgres -d sgetecba -v ON_ERROR_STOP=1 -f pruebas.sql
# exit 0  →  todas pasan.  exit != 0  →  el ASSERT que falló indica cuál.
```

`pruebas.sql`:

```sql
-- T5: solo el login tiene filas
DO $$
DECLARE r record; v_count bigint; v_bad text := '';
BEGIN
  FOR r IN SELECT table_name FROM information_schema.tables
           WHERE table_schema='sgetecba' AND table_type='BASE TABLE'
             AND table_name NOT IN ('usuarios','usuario_roles','roles') LOOP
    EXECUTE format('SELECT count(*) FROM sgetecba.%I', r.table_name) INTO v_count;
    IF v_count > 0 THEN v_bad := v_bad || r.table_name || '(' || v_count || ') '; END IF;
  END LOOP;
  ASSERT v_bad = '', format('T5 FALLO: tablas con datos fuera del login: %s', v_bad);
  RAISE NOTICE 'T5  OK';
END $$;

DO $$
DECLARE v int; v_txt text;
BEGIN
  -- T1: PRIMARY KEY en toda tabla base
  SELECT count(*) INTO v FROM information_schema.tables t
   WHERE t.table_schema='sgetecba' AND t.table_type='BASE TABLE'
     AND NOT EXISTS (SELECT 1 FROM information_schema.table_constraints tc
        WHERE tc.table_schema='sgetecba' AND tc.table_name=t.table_name AND tc.constraint_type='PRIMARY KEY');
  ASSERT v=0, format('T1 FALLO: %s tablas sin PK', v);

  -- T2: índice de soporte en toda FK
  SELECT count(*) INTO v FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kcu
      ON tc.constraint_name=kcu.constraint_name AND tc.table_schema=kcu.table_schema
    WHERE tc.constraint_type='FOREIGN KEY' AND tc.table_schema='sgetecba'
      AND NOT EXISTS (SELECT 1 FROM pg_indexes i
        WHERE i.schemaname='sgetecba' AND i.tablename=tc.table_name
          AND i.indexdef LIKE '%'||kcu.column_name||'%');
  ASSERT v=0, format('T2 FALLO: %s FK sin indice', v);

  -- T3: tipos de FK consistentes con su PK
  SELECT count(*) INTO v FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kcu
      ON tc.constraint_name=kcu.constraint_name AND tc.table_schema=kcu.table_schema
    JOIN information_schema.constraint_column_usage ccu
      ON ccu.constraint_name=tc.constraint_name AND ccu.table_schema=tc.table_schema
    JOIN information_schema.columns c1
      ON c1.table_schema=tc.table_schema AND c1.table_name=tc.table_name AND c1.column_name=kcu.column_name
    JOIN information_schema.columns c2
      ON c2.table_schema=ccu.table_schema AND c2.table_name=ccu.table_name AND c2.column_name=ccu.column_name
    WHERE tc.constraint_type='FOREIGN KEY' AND tc.table_schema='sgetecba' AND c1.data_type<>c2.data_type;
  ASSERT v=0, format('T3 FALLO: %s FK con tipo distinto', v);

  -- T4: actualizado_en con trigger
  SELECT count(*) INTO v FROM information_schema.columns c
   WHERE c.table_schema='sgetecba' AND c.column_name='actualizado_en'
     AND NOT EXISTS (SELECT 1 FROM information_schema.triggers t
        WHERE t.event_object_schema='sgetecba' AND t.event_object_table=c.table_name
          AND t.action_statement LIKE '%fn_establecer_actualizado_en%');
  ASSERT v=0, format('T4 FALLO: %s tablas con actualizado_en sin trigger', v);

  -- T6: 5 funciones
  SELECT count(*) INTO v FROM information_schema.routines
   WHERE routine_schema='sgetecba' AND routine_type='FUNCTION'
     AND routine_name IN ('fn_establecer_actualizado_en','fn_generar_codigo_estudiante',
        'fn_un_solo_periodo_actual','fn_verificar_choque_horario_estudiante','fn_verificar_choque_horario_docente');
  ASSERT v=5, format('T6 FALLO: faltan funciones (%s/5)', v);

  -- T7: choque horario cubre UPDATE
  SELECT count(*) INTO v FROM information_schema.triggers
   WHERE trigger_schema='sgetecba'
     AND trigger_name IN ('trg_inscripciones_choque_horario','trg_asignaciones_choque_horario')
     AND event_manipulation='UPDATE';
  ASSERT v=2, format('T7 FALLO: choque horario no cubre UPDATE (%s/2)', v);

  -- T8: formato de codigo
  v_txt := 'CIA' || '-' || LPAD((2025%100)::text,2,'0') || LPAD(126::text,3,'0');
  ASSERT v_txt='CIA-25126', format('T8 FALLO: formato = %s', v_txt);

  -- T9: ofertas.carrera_id nullable
  SELECT count(*) INTO v FROM information_schema.columns
   WHERE table_schema='sgetecba' AND table_name='ofertas_asignaturas'
     AND column_name='carrera_id' AND is_nullable='YES';
  ASSERT v=1, 'T9 FALLO: ofertas.carrera_id no es nullable';

  -- T10: constraints de negocio
  SELECT count(*) INTO v FROM information_schema.table_constraints
   WHERE table_schema='sgetecba'
     AND constraint_name IN ('ck_oferta_tipo','ck_oferta_carrera_por_tipo','ck_estudiantes_trabaja_lugar');
  ASSERT v=3, format('T10 FALLO: faltan constraints (%s/3)', v);

  -- T11: tabla matriculaciones existe con las columnas clave
  SELECT count(*) INTO v FROM information_schema.columns
   WHERE table_schema='sgetecba' AND table_name='matriculaciones'
     AND column_name IN ('id','estudiante_id','carrera_id','periodo_academico_id','plan_pago_id','estado_pago_id','tipo_inscripcion','fecha_matricula');
  ASSERT v=8, format('T11 FALLO: matriculaciones tiene solo %s/8 columnas esperadas', v);

  -- T12: inscripciones NO tiene plan_pago_id ni estado_pago_id
  SELECT count(*) INTO v FROM information_schema.columns
   WHERE table_schema='sgetecba' AND table_name='inscripciones'
     AND column_name IN ('plan_pago_id','estado_pago_id');
  ASSERT v=0, format('T12 FALLO: inscripciones aún tiene plan/estado de pago (%s col)', v);

  -- T13: vista vw_reporte_pagos existe
  SELECT count(*) INTO v FROM information_schema.views
   WHERE table_schema='sgetecba' AND table_name='vw_reporte_pagos';
  ASSERT v=1, 'T13 FALLO: falta vista vw_reporte_pagos';

  RAISE NOTICE 'TODAS LAS PRUEBAS DE BD PASARON (13/13)';
END $$;
```

---

*Generado el 2026-06-16. Migraciones aplicadas: 001, 002, 003, 004, 005, 006. Backup previo a la limpieza en `BD/backups/backup_pre_limpieza_*.dump`.*
