# Auditoría de Base de Datos — SGETECBA

**Fecha:** 2026-06-17
**Motor:** PostgreSQL 18 · `server_encoding = UTF8`
**Esquema:** `sgetecba`
**Estado de la BD:** limpia (solo login + parámetros) · migraciones `001`–`010` aplicadas
**Dump que acompaña este informe (misma carpeta `BD/MD/`):**
- `sgetecba_17-06-2026.sql` — estructura + datos de login y parámetros institucionales, autocontenido

---

## 1. Resumen ejecutivo

Sobre la base auditada el 2026-06-16 (migrations 001–006) se aplicaron cuatro migraciones nuevas (007–010) que completan el modelo académico para habilitar el desarrollo del backend. Los cambios principales son: incorporación del modelo de **facultades** (multifacultad por alumno), **calificaciones** con cálculo automático de aprobación, **parámetros institucionales** configurables y **bloqueo de prerrequisitos** al inscribir materias.

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

| Severidad | Cantidad | Estado |
|---|---|---|
| 🔴 Hallazgo crítico | 0 | — |
| 🟡 Corrección aplicada | 4 | ✅ en migrations 007–010 |
| 🔵 Decisión de diseño | 3 | ✅ documentadas |
| 🟢 Observación abierta | 2 | documentadas (ver §7) |

---

## 2. Cómo correr esta BD

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

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

- Credenciales locales: 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 operar hay que sembrarlos desde `BD/backups/seed_catalogos_raw.sql`. Para auditar la estructura no hace falta.
- ⚠️ **Facultades vacías**: la tabla `facultades` existe pero sin filas. Se pobla cuando la institución confirme nombres y códigos oficiales.

---

## 3. Inventario estructural (post-010)

| Objeto | Antes (006) | Ahora (010) | Δ |
|---|---:|---:|---:|
| Tablas | 49 | 52 | +3 netas (+4 creadas, −1 eliminada) |
| Vistas | 8 | 9 | +1 |
| Índices | 196 | 209 | +13 |
| Triggers | 45 | 54 | +9 |
| Funciones PL/pgSQL | 5 | 9 | +5 creadas, −1 eliminada |
| Foreign keys | 89 | 93 | +4 |
| CHECK constraints | — | — | +3 nuevos en calificaciones |
| **FK sin índice** | **0** ✅ | **0** ✅ | — |

**Tablas eliminadas (1):** `secuencias_codigo_estudiante` — ya no es necesaria con el código manual.

**Tablas nuevas (4):**
- `facultades` — catálogo de facultades de la institución
- `estudiante_facultades` — relación N:M alumno↔facultad (máx. 2 activas por alumno)
- `calificaciones` — nota por inscripción, con `aprobado` calculado automáticamente
- `parametros_institucionales` — reglas académicas configurables sin tocar código

**Vistas nuevas (1):**
- `vw_materias_desbloqueadas` — qué materias puede inscribir cada alumno según prerrequisitos cumplidos

**Funciones eliminadas (1):** `fn_generar_codigo_estudiante` — el código del alumno ya no es auto-generado.

**Funciones nuevas (5):**
- `fn_max_facultades_estudiante` — bloquea >2 facultades activas por alumno
- `fn_calcular_aprobacion` — calcula `aprobado = (valor >= nota_minima)` al insertar/actualizar nota
- `fn_contar_reprobaciones_anio` — cuenta reprobaciones de un alumno en un año académico
- `fn_evaluar_baja_institucional` — registra baja automática al superar el máximo de reprobaciones
- `fn_verificar_prerequisitos` — bloquea inscripción si el alumno no aprobó las materias requeridas

---

## 4. Cambios aplicados en esta auditoría (migrations 007–010)

### migration_007 — Facultades + código de estudiante manual

| # | Cambio | Motivo |
|---|---|---|
| C1 | Nueva tabla `facultades` (codigo, nombre, activo, soft-delete, trigger updated_at) | Requisito de negocio: la institución organiza carreras por facultad |
| C2 | `carreras.facultad_id` FK nullable a `facultades` | Nullable para no bloquear el esquema hasta el seed oficial |
| C3 | Nueva tabla `estudiante_facultades` + trigger `fn_max_facultades_estudiante` | Un alumno puede pertenecer a máximo 2 facultades; la BD lo fuerza |
| C4 | DROP trigger `trg_estudiantes_generar_codigo` + DROP function `fn_generar_codigo_estudiante` + DROP tabla `secuencias_codigo_estudiante` | El código ya no se auto-genera: lo asigna la administración/sistemas |
| C5 | `estudiantes.codigo_estudiante` pasa a `NOT NULL` | Con la BD vacía de alumnos el ALTER es seguro; fuerza que el operador ingrese el código |

### migration_008 — Parámetros institucionales + Calificaciones

| # | Cambio | Motivo |
|---|---|---|
| C6 | Nueva tabla `parametros_institucionales` (clave-valor, tipo, descripcion, modificable) | Permite cambiar reglas académicas sin deploy |
| C7 | Parámetros iniciales: `nota_minima_aprobacion = 51` y `max_reprobaciones_anio = 3` | Valores por defecto acordados; modificables desde el panel de admin |
| C8 | Nueva tabla `calificaciones` con `tipo_nota` (PARCIAL/FINAL/RECUPERATORIO/HABILITACION), `valor`, `aprobado`, `registrado_por` | Permite registrar notas básicas por inscripción |
| C9 | Trigger `fn_calcular_aprobacion` en `calificaciones` BEFORE INSERT/UPDATE OF valor | `aprobado` se calcula automáticamente consultando `nota_minima_aprobacion`; no requiere lógica en el backend |
| C10 | Índice FK faltante en `calificaciones.registrado_por` detectado y corregido | Detectado post-migración en la verificación T2; corregido antes del dump |

### migration_009 — Regla de reprobaciones (baja institucional)

| # | Cambio | Motivo |
|---|---|---|
| C11 | Función `fn_contar_reprobaciones_anio(est_id, anio)` | Helper que cuenta reprobaciones del alumno en un año académico via `calificaciones → inscripciones → ofertas → periodos` |
| C12 | Trigger `fn_evaluar_baja_institucional` AFTER INSERT/UPDATE OF aprobado en `calificaciones` | Cuando `aprobado = false` y el conteo alcanza `max_reprobaciones_anio`: INSERT automático en `abandonos_estudiantes` + UPDATE del estado del alumno |

### migration_010 — Bloqueo de prerrequisitos

| # | Cambio | Motivo |
|---|---|---|
| C13 | Trigger `fn_verificar_prerequisitos` BEFORE INSERT en `inscripciones` | Consulta `pensum_prerequisitos` → verifica `calificaciones.aprobado = true` para cada prerrequisito; RAISE EXCEPTION con el nombre de la materia faltante si no cumple |
| C14 | Vista `vw_materias_desbloqueadas` | Para secretaría: qué puede inscribir cada alumno según sus aprobaciones actuales |

---

## 5. Integridad referencial

**93 FKs totales — 0 sin índice** ✅

Reparto de acciones ON DELETE:

| Acción | FKs | Lectura |
|---|---:|---|
| `RESTRICT` | ~43 | Protege catálogos y entidades padre |
| `SET NULL` | ~26 | Campos opcionales (registrado_por, aprobado_por, periodo…) |
| `CASCADE` | ~14 | Detalles dependientes (convalidaciones, prerrequisitos…) |
| `NO ACTION` | ~10 | Sin cláusula explícita (ver §7.1) |

- **Soft-delete** (`eliminado_en`) homogéneo en todas las tablas operativas.
- **Unicidad** condicionada a soft-delete: CI, correo, código de estudiante únicos entre activos.
- **Unicidad en calificaciones**: un solo registro por (inscripcion_id, tipo_nota).
- **Unicidad en estudiante_facultades**: un alumno no puede tener la misma facultad dos veces.

---

## 6. Lógica de negocio implementada en BD

| Regla | Dónde vive | Trigger |
|---|---|---|
| Código de estudiante único y manual | `UNIQUE INDEX` + `NOT NULL` | — |
| Máximo 2 facultades por alumno | CHECK en trigger | `trg_max_facultades_estudiante` |
| Choque de horario (alumno) | Función + trigger | `trg_inscripciones_choque_horario` |
| Choque de horario (docente) | Función + trigger | `trg_asignaciones_choque_horario` |
| Solo un período académico activo por modalidad | Función + trigger | `trg_periodos_un_solo_actual` |
| Trabaja → lugar_trabajo obligatorio | `CHECK` | — |
| Oferta REGULAR → carrera_id obligatorio | `CHECK` | — |
| `aprobado` calculado automáticamente | Función + trigger | `trg_calcular_aprobacion` |
| Baja institucional por >N reprobaciones en un año | Función + trigger | `trg_evaluar_baja_institucional` |
| Bloqueo de inscripción por prerrequisitos | Función + trigger | `trg_verificar_prerequisitos` |
| Nota mínima y máx. reprobaciones configurables | `parametros_institucionales` | — |

---

## 7. Hallazgos abiertos (observaciones)

### 7.1 🟢 ~10 FKs con `NO ACTION` en lugar de `RESTRICT` explícito
Funcionalmente equivalente en PostgreSQL (la diferencia aplica solo dentro de una transacción). Unificar a `RESTRICT` en una futura migration de limpieza para consistencia.

### 7.2 🟢 `ux_horario_clases_oferta_dia` impide 2 bloques el mismo día
El índice único `(oferta_asignatura_id, dia_semana)` bloquea que una materia tenga dos franjas distintas el mismo día (ej. lunes 07–09 y lunes 10–12). Revisar si es intencional con la institución.

---

## 8. Checklist para el equipo de backend

- [ ] Correr `sgetecba_17-06-2026.sql` en la instancia de desarrollo y confirmar 0 errores.
- [ ] Correr `BD/pruebas_integridad_17-06-2026.sql` → verificar 25/25 en verde.
- [ ] Confirmar con la institución nombres y códigos de facultades → seed en `facultades` + UPDATE `carreras.facultad_id`.
- [ ] Aplicar `BD/backups/seed_catalogos_raw.sql` con los catálogos operativos (turnos, estados, planes, etc.).
- [ ] Revisar endpoints existentes de `estudiantes` que usen `carrera_id` directamente (ver §7 de auditoría 16-06-2026).

---

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

Corridas el 2026-06-17 con `ON_ERROR_STOP=1` sobre la BD activa. Script completo en `BD/pruebas_integridad_17-06-2026.sql`.

| # | 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 login y parámetros tienen filas | ✅ |
| T6 | 9 funciones PL/pgSQL presentes | ✅ |
| T6b | `fn_generar_codigo_estudiante` eliminada | ✅ |
| T7 | Choque de horario cubre INSERT y UPDATE | ✅ |
| T8 | `codigo_estudiante` es NOT NULL (asignación manual) | ✅ |
| T9 | `ofertas.carrera_id` nullable (soporta LIBRE) | ✅ |
| T10 | Constraints de negocio presentes | ✅ |
| T11 | `matriculaciones` con todas sus columnas | ✅ |
| T12 | Plan/estado de pago NO está en `inscripciones` | ✅ |
| T13 | Vista `vw_reporte_pagos` presente | ✅ |
| T14 | Tabla `facultades` con columnas clave | ✅ |
| T15 | `carreras.facultad_id` FK presente | ✅ |
| T16 | `estudiante_facultades` + trigger max-2 | ✅ |
| T17 | `secuencias_codigo_estudiante` eliminada | ✅ |
| T18 | `calificaciones` con columnas y constraints | ✅ |
| T19 | Trigger `trg_calcular_aprobacion` activo | ✅ |
| T20 | `parametros_institucionales` con valores iniciales | ✅ |
| T21 | Trigger `trg_evaluar_baja_institucional` activo | ✅ |
| T22 | Trigger `trg_verificar_prerequisitos` en inscripciones | ✅ |
| T23 | Vista `vw_materias_desbloqueadas` presente | ✅ |
| T24 | `nota_minima_aprobacion = 51` (NUMERIC) | ✅ |
| T25 | `max_reprobaciones_anio = 3` (INTEGER, modificable) | ✅ |

**Cómo reproducir:**
```bash
psql -h localhost -U postgres -d sgetecba -v ON_ERROR_STOP=1 -f BD/pruebas_integridad_17-06-2026.sql
# exit 0 → todas pasan. exit != 0 → el ASSERT que falló indica cuál.
```

---

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