Modelo físico en PostgreSQL de los conceptos del modelo de dominio. Sigue tres criterios:
La base protege lo que puede expresar: llaves, unicidad, estados válidos, horas positivas y semanas que inician en lunes.
El historial se conserva: usuarios, períodos, espacios y vinculaciones se cierran o finalizan; no se borran.
Los archivos van fuera de la base: solo se guarda su storage_key, lo que permite pasar del volumen local a Cloud Storage sin cambiar tablas.
Las reglas que dependen de varias filas, de la fecha actual o del usuario autenticado las valida el backend (sección 5).
2. Diagrama entidad-relación
Notación. PK: llave primaria; FK: llave foránea; FK*: parte de una FK compuesta; UK: valor único. Línea continua: relación identificadora; punteada: no identificadora. Cada línea indica la FK de la tabla hija y su ON DELETE. La tabla gris (notificacion) está fuera de la Entrega 1.
Lectura. Las 12 tablas forman tres bloques: identidad y acceso, seguimiento operativo y reportes con IA. vinculacion es el centro: une una persona con un rol asignado y un espacio académico, y de ella dependen tareas, rectificaciones y el cálculo de horas. usuario es la tabla más referenciada, casi siempre como autor de una acción (*_por). Solo las dos relaciones de usuario_rol son identificadoras; las demás tablas tienen un UUID propio.
3. Entidades
Entidad
Descripción
Reglas relacionadas
usuario
Persona que puede iniciar sesión
RF-01, RF-02
rol / usuario_rol
Catálogo de cuatro roles y su asignación a usuarios (N:M)
RN-01, RF-02.2
periodo_academico
Ciclo académico; habilita la creación de espacios
RF-03.2, RN-07
espacio_academico
Curso o proyecto de un profesor
RN-06 a RN-09
vinculacion
Persona con rol en un espacio, con horas contratadas
RN-02, RN-10 a RN-19
vinculacion_rectificacion
Historial de correcciones del administrador
RN-12, RF-04.5
tarea
Actividad de una semana dentro de una vinculación
RN-20 a RN-34
adjunto
Archivo de evidencia de una tarea
RN-23
lote_reporte
Solicitud de reportes de un profesor para una semana
RN-41, RN-44
reporte_semanal
PDF de una persona dentro de un lote
RN-42 a RN-46
notificacion
Aviso interno; tabla creada sin lógica en la Entrega 1
RN-38 a RN-40, RNF-04
Convenciones: llaves <tabla>_id de tipo UUID; autor <acción>_por; momento <acción>_en (TIMESTAMPTZ); día fecha_<nombre> (DATE); booleanos es_<nombre>; estados en VARCHAR con CHECK. Todas las columnas son NOT NULL salvo las marcadas.
CHECK ((estado = 'CERRADO') = (cerrado_en IS NOT NULL))
Cierre real
creado_en
TIMESTAMPTZ
No
Registro
actualizado_en
TIMESTAMPTZ
No
Última modificación
vinculacion
Columna
Tipo
Nulo
Restricciones
Descripción
vinculacion_id
UUID
No
PK
Identificador
usuario_id
UUID
No
FK* → usuario_rol
Persona vinculada
rol_id
UUID
No
FK* → usuario_rol
MONITOR o ASISTENTE_GRADUADO
espacio_academico_id
UUID
No
FK → espacio_academico
Curso o proyecto
creado_por
UUID
No
FK → usuario
Quién la creó
estado
VARCHAR(20)
No
CHECK ACTIVA, FINALIZADA
Estado
horas_semanales
INTEGER
No
CHECK (horas_semanales > 0)
Horas contratadas (RN-12)
creado_en
TIMESTAMPTZ
No
Creación
finalizado_en
TIMESTAMPTZ
Sí
Finalización
actualizado_en
TIMESTAMPTZ
No
Última modificación
Índice único parcial (usuario_id, espacio_academico_id) WHERE estado = 'ACTIVA'. El profesor responsable (RN-11) se obtiene de espacio_academico.profesor_id.
vinculacion_rectificacion
Columna
Tipo
Nulo
Restricciones
Descripción
vinculacion_rectificacion_id
UUID
No
PK
Identificador
vinculacion_id
UUID
No
FK → vinculacion
Vinculación corregida
rol_anterior_id
UUID
No
FK → rol
Rol previo
rol_nuevo_id
UUID
No
FK → rol
Rol nuevo
rectificado_por
UUID
No
FK → usuario
Administrador
motivo
TEXT
No
Razón
horas_semanales_anteriores
INTEGER
No
Horas previas
horas_semanales_nuevas
INTEGER
No
CHECK (> 0)
Horas nuevas
rectificado_en
TIMESTAMPTZ
No
Momento
Es un historial inmutable, por eso no tiene actualizado_en.
tarea
Columna
Tipo
Nulo
Restricciones
Descripción
tarea_id
UUID
No
PK
Identificador
vinculacion_id
UUID
No
FK → vinculacion
Vinculación (RN-21)
titulo
VARCHAR(200)
No
Título
estado
VARCHAR(20)
No
CHECK ABIERTO, EN_DESARROLLO, FINALIZADO
Estado (RN-27)
descripcion
TEXT
No
Descripción breve
observaciones
TEXT
Sí
Notas
horas
INTEGER
No
CHECK (horas > 0)
Horas enteras (RN-24)
es_tardia
BOOLEAN
No
Reporte tardío (RN-32)
fecha_inicio_semana
DATE
No
CHECK (EXTRACT(ISODOW FROM fecha_inicio_semana) = 1)
UNIQUE (lote_reporte_id, usuario_id). El profesor y la semana se toman del lote.
notificacion
Columna
Tipo
Nulo
Restricciones
Descripción
notificacion_id
UUID
No
PK
Identificador
usuario_id
UUID
No
FK → usuario
Destinatario
mensaje
TEXT
No
Texto
es_leida
BOOLEAN
No
Leída
creado_en
TIMESTAMPTZ
No
Generación
actualizado_en
TIMESTAMPTZ
No
Última modificación
4. Relaciones y cardinalidades
Padre
Hija
FK
Cardinalidad
Tipo
ON DELETE
usuario
usuario_rol
usuario_id
1 : 1..*
Identificadora
CASCADE
rol
usuario_rol
rol_id
1 : 0..*
Identificadora
RESTRICT
usuario_rol
vinculacion
(usuario_id, rol_id)
1 : 0..*
No identificadora
RESTRICT
espacio_academico
vinculacion
espacio_academico_id
1 : 0..*
No identificadora
RESTRICT
periodo_academico
espacio_academico
periodo_academico_id
1 : 0..*
No identificadora
RESTRICT
usuario
espacio_academico
profesor_id
1 : 0..*
No identificadora
RESTRICT
vinculacion
tarea
vinculacion_id
1 : 0..*
No identificadora
CASCADE
vinculacion
vinculacion_rectificacion
vinculacion_id
1 : 0..*
No identificadora
CASCADE
tarea
adjunto
tarea_id
1 : 0..*
No identificadora
CASCADE
lote_reporte
reporte_semanal
lote_reporte_id
1 : 1..*
No identificadora
CASCADE
usuario
reporte_semanal
usuario_id
1 : 0..*
No identificadora
RESTRICT
usuario
lote_reporte
solicitado_por
1 : 0..*
No identificadora
RESTRICT
usuario
notificacion
usuario_id
1 : 0..*
No identificadora
CASCADE
usuario
usuario_rol
asignado_por
1 : 0..*
No identificadora
RESTRICT
usuario
espacio_academico
cerrado_por
0..1 : 0..*
No identificadora
RESTRICT
usuario
vinculacion
creado_por
1 : 0..*
No identificadora
RESTRICT
usuario
vinculacion_rectificacion
rectificado_por
1 : 0..*
No identificadora
RESTRICT
rol
vinculacion_rectificacion
rol_anterior_id, rol_nuevo_id
1 : 0..*
No identificadora
RESTRICT
CASCADE se usa solo cuando la hija no existe sin su padre (composición); RESTRICT, en el resto, para proteger el historial. Las reglas se combinan: borrar un usuario con vinculaciones falla porque el RESTRICT de vinculacion bloquea la cascada sobre usuario_rol.
5. Restricciones de integridad
Restricción
Dónde se aplica (BD / código)
Regla
Estados y tipos válidos
BD: CHECK en cada columna estado y tipo
RN-06, RN-08, RN-27, RN-28
Horas enteras positivas
BD: INTEGER + CHECK (> 0)
RN-24
Semana inicia en lunes
BD: CHECK sobre fecha_inicio_semana
RN-29
La persona tiene el rol con que se vincula
BD: FK compuesta → usuario_rol
RN-02, RN-11
Una vinculación activa por persona y espacio
BD: índice único parcial
RN-13
Un reporte por persona y lote
BD: UNIQUE (lote_reporte_id, usuario_id)
RN-42
Rol de la vinculación es MONITOR o ASISTENTE_GRADUADO
Código: C5
RN-02
Horas contratadas inmutables, salvo rectificación
Código: C5
RN-12
Límites de 22 h, 3 monitorías, 12 h y 40 %
Código: C5, en una transacción con pg_advisory_xact_lock(usuario_id)
RN-14 a RN-19
Creación de espacios solo en período ACTIVO y habilitado
Código: C3, C4
RN-07
Espacio cerrado en solo consulta
Código: C4, C5, C6
RN-09
Edición solo en semana activa; tardías inmutables
Código: C6 (America/Bogota)
RN-30 a RN-34
Visibilidad por rol y propiedad
Código: C1 y componente dueño del dato
RN-03 a RN-05
Índices sobre FK: vinculacion (usuario_id, estado) y (espacio_academico_id); espacio_academico (profesor_id) y (periodo_academico_id); tarea (vinculacion_id, fecha_inicio_semana); adjunto (tarea_id); vinculacion_rectificacion (vinculacion_id); reporte_semanal (usuario_id); lote_reporte (solicitado_por, fecha_inicio_semana); notificacion (usuario_id, es_leida).
6. Almacenamiento de archivos
Archivo
Clave
Escribe / lee
Adjunto
adjuntos/{tarea_id}/{adjunto_id}
C6
Reporte PDF
reportes/{lote_reporte_id}/{usuario_id}.pdf
C8, con el PDF que entrega el worker
En la Entrega 1, volumen Docker archivos montado en /data/archivos (api y worker). En las siguientes, Cloud Storage, sin cambios en las tablas.
Las claves se generan con UUID; el nombre del cliente se guarda solo en nombre_archivo.
Las descargas pasan por la API, que verifica permisos.
Al eliminar una tarea, la base borra sus adjuntos en cascada y el backend borra los archivos.
7. Migraciones y datos semilla
Migraciones SQL versionadas (subida y bajada), embebidas en el binario de la API y aplicadas al arrancar. Orden: identidad (rol, usuario, usuario_rol) → periodo_academico → espacio_academico → vinculacion y vinculacion_rectificacion → tarea y adjunto → lote_reporte y reporte_semanal → notificacion.
Datos semilla:
Los cuatro roles se cargan en la primera migración.
El administrador inicial lo crea el backend al arrancar, a partir de ADMIN_CORREO y ADMIN_CONTRASENA, porque la contraseña requiere hash bcrypt y no debe quedar en el repositorio.
Decisiones de diseño
D-03.1 · Identificadores UUID
Contexto: la API expone los identificadores en sus rutas.
Justificación y compromisos: no revelan volumen ni son adivinables, y el backend los genera antes de insertar (útil para storage_key). Cuestan más espacio en índices.
Hipótesis: volumen moderado de datos por semestre.
D-03.2 · Vinculación referenciada a usuario_rol con FK compuesta
Contexto: la persona debe tener el rol con que se vincula (RN-02).
Alternativas consideradas: FK separadas a usuario y rol, validando solo en código.
Justificación y compromisos: la base garantiza la pareja persona-rol e impide retirar un rol en uso. Obliga a asignar el rol antes de vincular, y no excluye ADMIN o PROFESOR, que se valida en C5.
Hipótesis: el administrador asigna los roles operativos antes de que los profesores vinculen.
D-03.3 · Reglas de horas en el backend con bloqueo por persona
Contexto: RN-14 a RN-19 suman varias filas y pueden llegar solicitudes concurrentes para la misma persona.
Alternativas consideradas: triggers, aislamiento SERIALIZABLE, SELECT … FOR UPDATE sobre usuario.
Justificación y compromisos: la regla queda en el dominio de C5 y se prueba sin base de datos. pg_advisory_xact_lock serializa solo por persona y no bloquea una tabla de otro componente. Exige que solo C5 escriba vinculaciones.
Hipótesis: baja concurrencia sobre una misma persona.
D-03.4 · Estados como VARCHAR con CHECK
Contexto: siete columnas con valores cerrados.
Alternativas consideradas:ENUM de PostgreSQL, tablas de catálogo.
Justificación y compromisos: un CHECK se modifica fácilmente en una migración y no requiere uniones. Los valores se repiten en el código y se validan con pruebas.
Hipótesis: —
D-03.5 · Semana como fecha de su lunes
Contexto: la semana es la unidad de seguimiento (RN-20, RN-29).
Alternativas consideradas: tabla semana; año y número ISO.
Justificación y compromisos: un DATE con CHECK se compara, agrupa e indexa sin crear semanas por adelantado. La semana activa se calcula, no se guarda.
Hipótesis: zona horaria única America/Bogota.
D-03.6 · Historial sin borrado y archivos fuera de la base
Contexto: el seguimiento debe ser consultable (RN-20) y los archivos migrarán a Cloud Storage.
Alternativas consideradas: borrado lógico generalizado; archivos en BYTEA.
Justificación y compromisos:RESTRICT protege las entidades con historial, que se cierran o finalizan. La storage_key desacopla la base del almacenamiento. Base y almacenamiento no comparten transacción, así que puede quedar un archivo huérfano.
Hipótesis: los archivos huérfanos son raros y se limpian después.
