Esquema de Base de Datos

Este documento define la arquitectura de datos del sistema. Se utiliza una base de datos relacional (PostgreSQL) con extensiones geográficas (PostGIS) para optimizar las búsquedas por cercanía.

Relación: Traduce el Modelo de Dominio a SQL/NoSQL.

Alcance

Este documento contendrá la estructura de tablas y entidades de la base de datos del proyecto (por ejemplo users, profiles, y las entidades del modelo de dominio). Se irá completando conforme se avance en la tarea DOM-01 del Roadmap Técnico.

Lineamientos de modelado

PK expuestas a API: usar uuid (DEFAULT gen_random_uuid()); evitar IDs autoincrementales en interfaces públicas.
Public vs internal ID: si hay PK interna numérica, generar public_id UUID y exponer solo ese en API.
FKs: mantener integridad referencial; usar ON DELETE CASCADE donde aplique (ej. affiliaciones, tokens).
Índices: GIST para geolocalización, GIN para FTS.
Borrado lógico: deleted_at/is_active para histórico.

1. Modelo Entidad-Relación (ERD)

Este diagrama representa la estructura de tablas y sus vínculos. Se prioriza la integridad referencial para la Validación Triple.

2. Diccionario de Tablas Principales

2.1 Tabla: `users` (Identidad Core)

Campo	Tipo	Restricciones	Descripción
`id`	UUID	PK, DEFAULT gen_random_uuid()	ID global de usuario.
`email`	VARCHAR(255)	UNIQUE, INDEX, NOT NULL	Correo electrónico principal.
`password_hash`	TEXT	NOT NULL	Hash generado con Argon2id.
`roles`	TEXT[] / ENUM[]	DEFAULT ['PATIENT']	Roles acumulativos: PATIENT, DOCTOR, ENTITY_ADMIN, CATALOG_ADMIN, SUPERADMIN.
`status`	ENUM	PENDING, ACTIVE, SUSPENDED	Estado vital de la cuenta.
`mfa_secret`	TEXT	NULLABLE	Secreto para TOTP (Google Authenticator).
`is_verified`	BOOLEAN	DEFAULT FALSE	Estado de verificación de identidad.

2.2 Tabla: `refresh_tokens` (Seguridad de Sesión)

Implementa Refresh Token Rotation y Blacklisting.

Campo	Tipo	Restricciones	Descripción
`id`	UUID	PK	ID único del token.
`user_id`	UUID	FK -> users.id, INDEX	Usuario dueño de la sesión.
`token_hash`	TEXT	UNIQUE, NOT NULL	Hash del token para comparación segura.
`expires_at`	TIMESTAMPTZ	NOT NULL	Fecha de caducidad (TTL).
`is_revoked`	BOOLEAN	DEFAULT FALSE	Usado para invalidar sesiones robadas.

2.3 Tabla: `verification_tokens` (Identidad)

Campo	Tipo	Restricciones	Descripción
`id`	UUID	PK	Token de verificación/recuperación.
`user_id`	UUID	FK -> users.id, INDEX	Usuario objetivo.
`purpose`	ENUM	VERIFY_EMAIL, RESET_PASSWORD, MFA_CHALLENGE	Motivo.
`token_hash`	TEXT	NOT NULL	Hash del código/OTP.
`expires_at`	TIMESTAMPTZ	NOT NULL	TTL corto (15-30 min).
`used_at`	TIMESTAMPTZ	NULLABLE	Marcar consumo único.

2.4 Tabla: `user_profiles` (Datos comunes)

Campo	Tipo	Restricciones	Descripción
`user_id`	UUID	PK, FK -> users.id	Identidad base.
`first_name`	VARCHAR(120)	NOT NULL	Nombre(s).
`last_name`	VARCHAR(120)	NOT NULL	Apellido(s).
`full_name`	VARCHAR(255)	GENERATED ALWAYS AS (first_name
`phone`	VARCHAR(20)	NOT NULL	Teléfono (PII, cifrado en app antes de DB).
`birth_date`	DATE	NULLABLE	Fecha de nacimiento.
`country_code`	CHAR(2)	NOT NULL	ISO país (selector LATAM).
`language`	VARCHAR(10)	DEFAULT 'es'	Preferencia de idioma.
`primary_insurance_id`	UUID	NULLABLE	Convenio principal (opcional).
`created_at`	TIMESTAMPTZ	DEFAULT now()	Auditoría.
`updated_at`	TIMESTAMPTZ	DEFAULT now()	Auditoría.

Auditoría y trazabilidad: toda actualización de user_profiles debe emitir evento USER_PROFILE_UPDATED con changed_by, timestamp y diff en metadatos. Ideal capturar IP/UA para cumplir trazas de seguridad.

2.4 Tabla: `doctor_profiles`

Campo	Tipo	Restricciones	Descripción
`id`	UUID	PK	Identificador del perfil.
`user_id`	UUID	FK -> users.id, UNIQUE	Vínculo con cuenta Auth.
`professional_prefix`	ENUM	DR, DRA, ENF, LIC, ODONT, PSIC, NUT, FISIO, QF, TEC	Prefijo mostrado en perfil público.
`license_number`	VARCHAR(20)	UNIQUE	Número de colegiado.
`specialty_id`	UUID	FK -> medical_taxonomy.id	Especialidad primaria.
`metadata`	JSONB	NOT NULL	Formación, idiomas, subs, documentos.
`status`	ENUM	PENDING, APPROVED, REJECTED, DISABLED	Revisión y habilitación.
`created_by`	UUID	FK -> users.id	Auditoría (quién solicitó).
`approved_by`	UUID	FK -> users.id	Auditoría (quién aprobó).

Auditoría: cambios y aprobaciones deben emitir eventos DOCTOR_PROFILE_UPDATED / DOCTOR_PROFILE_APPROVED con changed_by, timestamp e IP/UA.

2.5 Tabla: `medical_taxonomy` (Areas y Especialidades)

Campo	Tipo	Restricciones	Descripción
`id`	UUID	PK	Identificador del nodo.
`code`	VARCHAR(64)	UNIQUE NOT NULL	Código estable (ej. `area_infantil`, `esp_pediatria`).
`name`	VARCHAR(255)	NOT NULL	Nombre a mostrar.
`slug`	VARCHAR(255)	UNIQUE	Para URLs/búsqueda.
`parent_id`	UUID	FK -> medical_taxonomy.id, NULLABLE	Jerarquia area -> especialidad.
`enabled`	BOOLEAN	DEFAULT TRUE	Control de exposición.
`created_at`	TIMESTAMPTZ	DEFAULT now()	Auditoría.
`updated_at`	TIMESTAMPTZ	DEFAULT now()	Auditoría.

Uso: las areas tienen parent_id = null; las especialidades tienen parent_id apuntando al area principal.

2.6 Tabla: `area_taxonomy` (Relacion muchas-a-muchas area ↔ especialidad)

Campo	Tipo	Restricciones	Descripción
`area_id`	UUID	PK parcial, FK -> medical_taxonomy(id)	Debe ser un nodo área (parent_id null).
`taxonomy_id`	UUID	PK parcial, FK -> medical_taxonomy(id)	Especialidad reutilizable.
`created_at`	TIMESTAMPTZ	DEFAULT now()	Auditoría.

Permite reutilizar especialidades en multiples areas cuando aplique (UNIQUE(area_id, taxonomy_id)).

2.6 Tabla: `health_entities` (Sedes Polimórficas)

Campo	Tipo	Restricciones	Descripción
`id`	UUID	PK	ID de la sede física.
`type`	ENUM	CLINIC, LAB, PHARMACY	Módulo de dominio.
`owner_user_id`	UUID	FK -> users.id	Dueño/gestor principal.
`name`	VARCHAR(255)	NOT NULL	Nombre comercial indexable.
`legal_name`	VARCHAR(255)	NOT NULL	Razón social.
`email`	VARCHAR(255)	NOT NULL	Contacto principal.
`address`	TEXT	NOT NULL	Dirección descriptiva.
`location`	GEOGRAPHY(POINT)	INDEX GIST	Coordenadas PostGIS (4326).
`geography_id`	UUID	FK -> geography.id	Vinculo a localidad (municipio/ciudad) o region segun el pais.
`config`	JSONB	NOT NULL	Datos extendidos: `schedules`, `services`, `amenities`, `terms_version_accepted`.
`status`	ENUM	PENDING, APPROVED, REJECTED, DISABLED	Flujo de aprobación.

Auditoría: creación/actualización debe emitir ENTITY_UPDATED con changed_by, timestamp e IP/UA. Rechazos/aprobaciones deben registrar motivo.

2.7 Tabla: `affiliations` (Intersección Operativa)

Campo	Tipo	Restricciones	Descripción
`doctor_id`	UUID	FK -> doctor_profiles.id ON DELETE CASCADE	El médico que atiende.
`entity_id`	UUID	FK -> HEALTH_ENTITIES ON DELETE CASCADE	La sede donde atiende.
`schedule`	JSONB	NOT NULL	Días y horas (Ej: `{"Mon": ["08:00-12:00"]}`).
`room_number`	VARCHAR(50)	NULLABLE	Oficina o consultorio específico.
PK	(`doctor_id`, `entity_id`)		Evita duplicados.

2.8 Tabla: `insurances`

Campo	Tipo	Restricciones	Descripción
`id`	UUID	PK	Identificador del seguro.
`name`	VARCHAR(255)	UNIQUE, NOT NULL	Nombre comercial.
`logo_url`	TEXT	NULLABLE	Logo para UI.
`plans`	JSONB	NULLABLE	Lista de planes soportados.

2.9 Tabla: `insurance_agreements`

Campo	Tipo	Restricciones	Descripción
`id`	UUID	PK	ID del convenio.
`insurance_id`	UUID	FK -> insurances.id	Aseguradora.
`entity_id`	UUID	FK -> health_entities.id	Sede con convenio.
`coverage`	JSONB	NOT NULL	Planes, copagos, restricciones.
`status`	ENUM	ACTIVE, SUSPENDED	Estado operativo.
UK	(`insurance_id`,`entity_id`)		Evita convenios duplicados.

2.10 Tabla: `doctor_accreditations`

Campo	Tipo	Restricciones	Descripción
`id`	UUID	PK	ID del registro.
`doctor_id`	UUID	FK -> doctor_profiles.id ON DELETE CASCADE	Profesional.
`insurance_id`	UUID	FK -> insurances.id	Seguro que acredita.
`credential_url`	TEXT	NULLABLE	Evidencia digital.
`status`	ENUM	ACTIVE, PENDING, REJECTED	Estado de acreditación.
UK	(`doctor_id`,`insurance_id`)		Evita acreditaciones duplicadas.

2.11 Tabla: `reviews`

Campo	Tipo	Restricciones	Descripción
`id`	UUID	PK	ID de la reseña.
`patient_id`	UUID	FK -> users.id (rol PATIENT)	Autor.
`doctor_id`	UUID	FK -> doctor_profiles.id	Opcional si reseña al doctor.
`entity_id`	UUID	FK -> health_entities.id	Opcional si reseña la sede.
`rating`	SMALLINT	CHECK 1-5	Puntuación.
`comment`	TEXT	NULLABLE	Texto libre.
`evidence_file_id`	UUID	FK -> file_assets.id	Comprobante requerido si negativa.
`status`	ENUM	PENDING, PUBLISHED, REJECTED	Moderación.

2.12 Tabla: `appointments` (Citas)

Campo	Tipo	Restricciones	Descripción
`id`	UUID	PK	ID de la cita.
`patient_id`	UUID	FK -> users.id (rol PATIENT)	Paciente que agenda.
`doctor_id`	UUID	FK -> doctor_profiles.id ON DELETE CASCADE	Profesional.
`entity_id`	UUID	FK -> health_entities.id ON DELETE CASCADE	Sede donde atiende.
`insurance_id`	UUID	FK -> insurances.id	Seguro aplicado (NULL si particular).
`slot_start`	TIMESTAMPTZ	NOT NULL	Inicio de la cita.
`slot_end`	TIMESTAMPTZ	NOT NULL	Fin de la cita. (CHECK `slot_end > slot_start`).
`status`	ENUM	PENDING, CONFIRMED, CANCELLED, NOSHOW	Estado operativo.
`reason`	VARCHAR(255)	NULLABLE	Motivo breve (sin PHI).
UK	(`doctor_id`,`slot_start`,`slot_end`)		Evita doble booking exacto.
IDX	(`patient_id`,`slot_start`)		Consultas de historial.
`room_number`	VARCHAR(50)	NULLABLE	Sala/consultorio.
`created_at`	TIMESTAMPTZ	DEFAULT now()
`updated_at`	TIMESTAMPTZ	DEFAULT now()
`deleted_at`	TIMESTAMPTZ	NULLABLE	Soft delete.

2.13 Tabla: `notification_preferences`

Campo	Tipo	Restricciones	Descripción
`user_id`	UUID	PK, FK -> users.id ON DELETE CASCADE	Dueño de la preferencia.
`email_enabled`	BOOLEAN	DEFAULT TRUE	Opt-in email transaccional.
`sms_enabled`	BOOLEAN	DEFAULT TRUE	Opt-in SMS transaccional.
`push_enabled`	BOOLEAN	DEFAULT TRUE	Opt-in push.
`marketing_opt_in`	BOOLEAN	DEFAULT FALSE	Opt-in marketing.
`updated_at`	TIMESTAMPTZ	DEFAULT now()	Control de cambios.

2.14 Tabla: `notification_logs`

Campo	Tipo	Restricciones	Descripción
`id`	UUID	PK	ID del log.
`user_id`	UUID	FK -> users.id ON DELETE SET NULL	Receptor.
`channel`	ENUM	EMAIL, SMS, PUSH, INAPP	Canal usado.
`template`	VARCHAR(100)	NOT NULL	Plantilla disparada.
`status`	ENUM	SENT, FAILED, RETRIED	Estado de envío.
`provider_msg_id`	VARCHAR(255)	NULLABLE	ID del proveedor (SES/Twilio).
`error`	TEXT	NULLABLE	Mensaje de error si aplica.
`created_at`	TIMESTAMPTZ	DEFAULT now()

2.15 Tabla: `file_assets`

Campo	Tipo	Restricciones	Descripción
`id`	UUID	PK	ID del archivo.
`owner_id`	UUID	FK -> users.id	Propietario.
`bucket_path`	TEXT	NOT NULL	Ruta en S3/CloudFront.
`mime_type`	VARCHAR(80)	NOT NULL	Tipo de archivo.
`size_bytes`	BIGINT	CHECK > 0	Tamaño para control de costos.

2.16 Tabla: `audit_logs`

Campo	Tipo	Descripción
`id`	UUID	PK
`user_id`	UUID	FK -> users.id
`action`	VARCHAR(50)	LOGIN_MFA, UPDATE_LICENSE, VIEW_PATIENT_DATA
`old_values`	JSONB	Estado previo
`new_values`	JSONB	Estado posterior
`ip_address`	INET	IP origen
`trace_id`	UUID	Correlación con OpenTelemetry
`created_at`	TIMESTAMPTZ	DEFAULT now()

2.17 Tabla: `coupons` (Promociones)

Campo	Tipo	Restricciones	Descripción
`id`	UUID	PK	ID del cupón.
`owner_id`	UUID	FK -> users.id ON DELETE CASCADE	Dueño (DOCTOR/ENTITY_ADMIN).
`entity_id`	UUID	FK -> health_entities.id	Alcance por sede (opcional).
`code`	VARCHAR(32)	UNIQUE, NOT NULL	Código visible al paciente.
`title`	VARCHAR(120)	NOT NULL	Nombre de campaña.
`description`	TEXT	NULLABLE	Copy promocional.
`discount_type`	ENUM	PERCENT, FIXED	Tipo de descuento.
`discount_value`	NUMERIC(10,2)	CHECK > 0	Valor del descuento.
`starts_at`	TIMESTAMPTZ	NOT NULL	Inicio vigencia.
`ends_at`	TIMESTAMPTZ	NOT NULL	Fin vigencia.
`max_redemptions`	INTEGER	NULLABLE	Cupo total.
`max_per_user`	INTEGER	DEFAULT 1	Cupo por usuario.
`status`	ENUM	DRAFT, ACTIVE, PAUSED, REJECTED, EXPIRED	Estado operativo.
`created_at`	TIMESTAMPTZ	DEFAULT now()
`updated_at`	TIMESTAMPTZ	DEFAULT now()
`deleted_at`	TIMESTAMPTZ	NULLABLE	Soft delete.

2.18 Tabla: `professional_posts` (Blog profesional)

Campo	Tipo	Restricciones	Descripción
`id`	UUID	PK	ID del artículo.
`author_id`	UUID	FK -> users.id ON DELETE CASCADE	Autor (DOCTOR/ENTITY_ADMIN).
`entity_id`	UUID	FK -> health_entities.id	Sede asociada (opcional).
`title`	VARCHAR(180)	NOT NULL	Título del contenido.
`slug`	VARCHAR(200)	UNIQUE, NOT NULL	URL amigable.
`excerpt`	VARCHAR(320)	NULLABLE	Resumen corto.
`body`	TEXT	NOT NULL	Contenido principal.
`cover_file_id`	UUID	FK -> file_assets.id	Portada opcional.
`status`	ENUM	DRAFT, PUBLISHED	Estado editorial.
`published_at`	TIMESTAMPTZ	NULLABLE	Fecha de publicación.
`created_at`	TIMESTAMPTZ	DEFAULT now()
`updated_at`	TIMESTAMPTZ	DEFAULT now()
`deleted_at`	TIMESTAMPTZ	NULLABLE	Soft delete.

3. Catálogos Jerárquicos (Master Data)

Para evitar texto libre, estas tablas gestionan la normalización:

GEOGRAPHY: Tabla recursiva (id, name, parent_id, level).
- Niveles canonicos: 0:Pais, 1:Region, 2:Localidad.
- Labels por pais (Departamento/Estado/Provincia, Municipio/Ciudad/Partido) se resuelven por metadata, no por columnas nuevas.
MEDICAL_TAXONOMY: Tabla recursiva (id, name, parent_id, level).
- Niveles: 0:Area, 1:Especialidad.

4. Estrategia de Índices y Rendimiento

Geo-búsqueda: CREATE INDEX idx_location ON health_entities USING GIST (location);
Validación Triple: CREATE INDEX idx_triple_val ON affiliations (doctor_id, entity_id);
Búsqueda Semántica: CREATE INDEX idx_doc_metadata ON doctor_profiles USING GIN (metadata);
Sesiones: CREATE INDEX idx_token_expiry ON refresh_tokens (expires_at) WHERE is_revoked IS FALSE;
Full-Text Search: Uso de tsvector en PostgreSQL para búsquedas por síntomas o nombres en los metadatos JSONB.
Particionado opcional: Evaluar particionado por fecha en audit_logs y reviews si el volumen supera 50M registros/año.
Citas: CREATE INDEX idx_appointments_patient ON appointments (patient_id, slot_start DESC); y CREATE INDEX idx_appointments_doctor ON appointments (doctor_id, slot_start DESC);
Convenios: CREATE INDEX idx_agreements_insurance ON insurance_agreements (insurance_id, entity_id);
FTS Doctores/Entidades: CREATE INDEX idx_doctor_search ON doctor_profiles USING GIN (to_tsvector('spanish', metadata::text)); y CREATE INDEX idx_entity_search ON health_entities USING GIN (to_tsvector('spanish', name || ' ' || config::text));
Trazas/Logs: incluir siempre trace_id, user_id/doctor_id/entity_id en logs de mutaciones críticas para correlación con OTel.
Cupones: CREATE INDEX idx_coupons_owner_status ON coupons (owner_id, status, ends_at);
Blog profesional: CREATE INDEX idx_posts_author_status ON professional_posts (author_id, status, published_at DESC);

5. Auditoría y Trazabilidad

Toda tabla crítica debe incluir campos de control:

created_at: Timestamp de creación.
updated_at: Timestamp de última modificación.
deleted_at: Timestamp para Borrado Lógico (Soft Delete).
version: Entero para control de concurrencia optimista.

Toda mutación de datos sensibles o PHI (Protected Health Information) se registra en audit_logs:

Campo	Tipo	Descripción
`action`	VARCHAR(50)	LOGIN_MFA, UPDATE_LICENSE, VIEW_PATIENT_DATA.
`old_values`	JSONB	Estado del registro antes del cambio.
`new_values`	JSONB	Estado del registro después del cambio.
`ip_address`	INET	IP origen de la petición.
`trace_id`	UUID	ID para correlación con OpenTelemetry (Go logs).

6. Requerimientos No Funcionales (NFR) de Datos

6.1 Retención y Borrado

Soft Delete: Se implementa la columna deleted_at en todas las tablas transaccionales.
Retención PII: Datos de usuarios borrados se anonimizan tras 24 meses; se conservan agregados estadísticos sin PII.

6.2 Estrategia de Backup

RPO (Recovery Point Objective): ≤ 5 minutos (RDS Point-in-time recovery + snapshots horarios si aplica).
RTO (Recovery Time Objective): ≤ 60 minutos para restauración completa de base de datos en producción.
Snapshot: Snapshot automático diario con retención de 35 días.

6.3 Protección de PII/PHI

Cifrado en tránsito: TLS 1.3 obligatorio entre servicios y DB.
Cifrado en reposo: RDS TDE habilitado; columnas sensibles (phone, mfa_secret) cifradas en capa aplicación antes de persistir.
Mascaramiento: Endpoints públicos nunca retornan PII completa; aplicar masking en logs y trazas.

6.4 Política de anonimización y legal hold

Dataset	Trigger de anonimización	Técnica	Excepciones
`user_profiles`	Cuenta eliminada + 24 meses	Hash irreversible de email/teléfono + null de PII directa	Litigios o fraude activo (`legal_hold=true`)
`appointments`	Ventana legal vencida	Remover datos de contacto y conservar métricas operativas	Investigación médica/seguridad aprobada
`reviews`	Solicitud GDPR/LOPD	Reemplazar autor por seudónimo no reversible	Moderación pendiente
`audit_logs`	Retención cumplida (mín. 24 meses)	Purga por partición + checksum de evidencia	Incidente abierto o auditoría regulatoria

Reglas:

La anonimización debe ser idempotente y auditable (ANONYMIZATION_EXECUTED en audit_logs).
legal_hold se define por soporte/compliance y bloquea purga/anonimización automática.
No se permite restaurar PII luego de anonimizar; solo conservar trazabilidad técnica mínima.

7. Matrix de Trazabilidad (Brecha Corta)

Entidad Dominio	Tabla DB	Endpoint Go (CRUD)	Owner Técnico
Doctor	`doctor_profiles`	`POST /api/v1/doctors`	Backend Team
Sede	`health_entities`	`POST /api/v1/entities`	Backend Team
Seguro	`insurances`	`GET /api/v1/insurances`	Backend Team
Sesión	`refresh_tokens`	`POST /api/v1/auth/refresh`	Security Team
Acreditación	`doctor_accreditations`	`POST /api/v1/doctor-accreditations`	Backend Team
Convenio	`insurance_agreements`	`POST /api/v1/insurance-agreements`	Backend Team
Reseña	`reviews`	`POST /api/v1/reviews`	Product & QA

8. ENUMs Oficiales

Para mantener coherencia entre BD, API y frontend, estos valores son únicos y deben versionarse.

Nombre	Valores	Uso
`user_role`	PATIENT, DOCTOR, ENTITY_ADMIN, CATALOG_ADMIN, SUPERADMIN	`users.roles` (claims y autorización).
`user_status`	PENDING, ACTIVE, SUSPENDED	`users.status`.
`verification_purpose`	VERIFY_EMAIL, RESET_PASSWORD, MFA_CHALLENGE	`verification_tokens.purpose`.
`entity_type`	CLINIC, LAB, PHARMACY	`health_entities.type`.
`agreement_status`	ACTIVE, SUSPENDED	`insurance_agreements.status`.
`accreditation_status`	ACTIVE, PENDING, REJECTED	`doctor_accreditations.status`.
`professional_prefix`	DR, DRA, ENF, LIC, ODONT, PSIC, NUT, FISIO, QF, TEC	`doctor_profiles.professional_prefix` (display).
`review_status`	PENDING, PUBLISHED, REJECTED	`reviews.status`.
`review_rating`	1,2,3,4,5	Constraint CHECK en `reviews.rating`.
`coupon_status`	DRAFT, ACTIVE, PAUSED, REJECTED, EXPIRED	`coupons.status`.
`coupon_discount_type`	PERCENT, FIXED	`coupons.discount_type`.
`post_status`	DRAFT, PUBLISHED	`professional_posts.status`.

9. DDL sugerido para ENUMs (PostgreSQL)

CREATE TYPE user_role AS ENUM ('PATIENT', 'DOCTOR', 'ENTITY_ADMIN', 'CATALOG_ADMIN', 'SUPERADMIN');
CREATE TYPE user_status AS ENUM ('PENDING', 'ACTIVE', 'SUSPENDED');
CREATE TYPE verification_purpose AS ENUM ('VERIFY_EMAIL', 'RESET_PASSWORD', 'MFA_CHALLENGE');
CREATE TYPE entity_type AS ENUM ('CLINIC', 'LAB', 'PHARMACY');
CREATE TYPE agreement_status AS ENUM ('ACTIVE', 'SUSPENDED');
CREATE TYPE accreditation_status AS ENUM ('ACTIVE', 'PENDING', 'REJECTED');
CREATE TYPE professional_prefix AS ENUM ('DR', 'DRA', 'ENF', 'LIC', 'ODONT', 'PSIC', 'NUT', 'FISIO', 'QF', 'TEC');
CREATE TYPE review_status AS ENUM ('PENDING', 'PUBLISHED', 'REJECTED');
CREATE TYPE coupon_status AS ENUM ('DRAFT', 'ACTIVE', 'PAUSED', 'REJECTED', 'EXPIRED');
CREATE TYPE coupon_discount_type AS ENUM ('PERCENT', 'FIXED');
CREATE TYPE post_status AS ENUM ('DRAFT', 'PUBLISHED');
-- Para rating usamos SMALLINT con CHECK (1-5) en lugar de ENUM para facilitar agregados

10. DDL de Tablas (Ejemplo)

Referencia rápida para implementación en migrations. Ajustar tamaños y defaults según decisión final de infraestructura.

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  roles TEXT[] NOT NULL DEFAULT ARRAY['PATIENT'],
  status user_status NOT NULL DEFAULT 'PENDING',
  mfa_secret TEXT,
  is_verified BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at TIMESTAMPTZ
);

CREATE TABLE user_profiles (
  user_id UUID PRIMARY KEY REFERENCES users(id),
  first_name VARCHAR(120) NOT NULL,
  last_name VARCHAR(120) NOT NULL,
  full_name VARCHAR(255) GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED,
  phone VARCHAR(20) NOT NULL,
  birth_date DATE,
  country_code CHAR(2) NOT NULL,
  language VARCHAR(10) NOT NULL DEFAULT 'es',
  primary_insurance_id UUID REFERENCES insurances(id),
  dependents JSONB,
  frequent_locations JSONB,
  medical_history JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE verification_tokens (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  purpose verification_purpose NOT NULL,
  token_hash TEXT NOT NULL,
  expires_at TIMESTAMPTZ NOT NULL,
  used_at TIMESTAMPTZ
);

CREATE TABLE refresh_tokens (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  token_hash TEXT NOT NULL UNIQUE,
  expires_at TIMESTAMPTZ NOT NULL,
  is_revoked BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE doctor_profiles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL UNIQUE REFERENCES users(id),
  professional_prefix professional_prefix NOT NULL DEFAULT 'DR',
  license_number VARCHAR(20) UNIQUE,
  specialty_id UUID REFERENCES medical_taxonomy(id),
  metadata JSONB NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE geography (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  parent_id UUID REFERENCES geography(id),
  level SMALLINT NOT NULL CHECK (level BETWEEN 0 AND 2)
);
CREATE INDEX idx_geo_parent ON geography(parent_id);

CREATE TABLE medical_taxonomy (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  code VARCHAR(64) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(255) UNIQUE,
  parent_id UUID REFERENCES medical_taxonomy(id),
  enabled BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_medtax_parent ON medical_taxonomy(parent_id);

CREATE TABLE area_taxonomy (
  area_id UUID NOT NULL REFERENCES medical_taxonomy(id),
  taxonomy_id UUID NOT NULL REFERENCES medical_taxonomy(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (area_id, taxonomy_id)
);

CREATE TABLE health_entities (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  type entity_type NOT NULL,
  name VARCHAR(255) NOT NULL,
  location GEOGRAPHY(POINT, 4326),
  geography_id UUID REFERENCES geography(id),
  config JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE affiliations (
  doctor_id UUID REFERENCES doctor_profiles(id),
  entity_id UUID REFERENCES health_entities(id),
  schedule JSONB NOT NULL,
  room_number VARCHAR(50),
  PRIMARY KEY (doctor_id, entity_id)
);

CREATE TABLE insurances (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) UNIQUE NOT NULL,
  logo_url TEXT,
  plans JSONB
);

CREATE TABLE insurance_agreements (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  insurance_id UUID NOT NULL REFERENCES insurances(id),
  entity_id UUID NOT NULL REFERENCES health_entities(id),
  coverage JSONB NOT NULL,
  status agreement_status NOT NULL DEFAULT 'ACTIVE',
  UNIQUE (insurance_id, entity_id)
);

CREATE TABLE doctor_accreditations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  doctor_id UUID NOT NULL REFERENCES doctor_profiles(id),
  insurance_id UUID NOT NULL REFERENCES insurances(id),
  credential_url TEXT,
  status accreditation_status NOT NULL DEFAULT 'PENDING',
  UNIQUE (doctor_id, insurance_id)
);

CREATE TABLE file_assets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id UUID NOT NULL REFERENCES users(id),
  bucket_path TEXT NOT NULL,
  mime_type VARCHAR(80) NOT NULL,
  size_bytes BIGINT NOT NULL CHECK (size_bytes > 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE appointments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  patient_id UUID NOT NULL REFERENCES users(id),
  doctor_id UUID NOT NULL REFERENCES doctor_profiles(id),
  entity_id UUID NOT NULL REFERENCES health_entities(id),
  insurance_id UUID REFERENCES insurances(id),
  slot_start TIMESTAMPTZ NOT NULL,
  slot_end TIMESTAMPTZ NOT NULL,
  room_number VARCHAR(50),
  status VARCHAR(20) NOT NULL CHECK (status IN ('PENDING','CONFIRMED','CANCELLED','NOSHOW')),
  reason VARCHAR(255),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at TIMESTAMPTZ,
  CHECK (slot_end > slot_start),
  UNIQUE (doctor_id, slot_start, slot_end)
);

CREATE TABLE reviews (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  patient_id UUID NOT NULL REFERENCES users(id),
  doctor_id UUID REFERENCES doctor_profiles(id),
  entity_id UUID REFERENCES health_entities(id),
  rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment TEXT,
  evidence_file_id UUID REFERENCES file_assets(id),
  status review_status NOT NULL DEFAULT 'PENDING',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE audit_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id),
  action VARCHAR(50) NOT NULL,
  old_values JSONB,
  new_values JSONB,
  ip_address INET,
  trace_id UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Consider partitioning audit_logs and reviews by month if volume > 50M/year

CREATE TABLE coupons (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  entity_id UUID REFERENCES health_entities(id),
  code VARCHAR(32) NOT NULL UNIQUE,
  title VARCHAR(120) NOT NULL,
  description TEXT,
  discount_type coupon_discount_type NOT NULL,
  discount_value NUMERIC(10,2) NOT NULL CHECK (discount_value > 0),
  starts_at TIMESTAMPTZ NOT NULL,
  ends_at TIMESTAMPTZ NOT NULL,
  max_redemptions INTEGER,
  max_per_user INTEGER NOT NULL DEFAULT 1,
  status coupon_status NOT NULL DEFAULT 'DRAFT',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at TIMESTAMPTZ,
  CHECK (ends_at > starts_at)
);

CREATE TABLE professional_posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  entity_id UUID REFERENCES health_entities(id),
  title VARCHAR(180) NOT NULL,
  slug VARCHAR(200) NOT NULL UNIQUE,
  excerpt VARCHAR(320),
  body TEXT NOT NULL,
  cover_file_id UUID REFERENCES file_assets(id),
  status post_status NOT NULL DEFAULT 'DRAFT',
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at TIMESTAMPTZ
);


CREATE TABLE notification_preferences (
  user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  email_enabled BOOLEAN DEFAULT TRUE,
  sms_enabled BOOLEAN DEFAULT TRUE,
  push_enabled BOOLEAN DEFAULT TRUE,
  marketing_opt_in BOOLEAN DEFAULT FALSE,
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE notification_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  channel VARCHAR(10) NOT NULL CHECK (channel IN ('EMAIL','SMS','PUSH','INAPP')),
  template VARCHAR(100) NOT NULL,
  status VARCHAR(10) NOT NULL CHECK (status IN ('SENT','FAILED','RETRIED')),
  provider_msg_id VARCHAR(255),
  error TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Índices sugeridos
CREATE INDEX idx_appointments_patient ON appointments (patient_id, slot_start DESC);
CREATE INDEX idx_appointments_doctor ON appointments (doctor_id, slot_start DESC);
CREATE INDEX idx_agreements_insurance ON insurance_agreements (insurance_id, entity_id);
CREATE INDEX idx_coupons_owner_status ON coupons (owner_id, status, ends_at);
CREATE INDEX idx_posts_author_status ON professional_posts (author_id, status, published_at DESC);

11. Contratos API (Swagger Draft)

El contrato OpenAPI completo se mantiene en static/openapi.yaml para generar Swagger UI. Acceso local: http://localhost:3000/openapi.yaml. Para verlo en UI, usar Swagger UI/Redocly apuntando a esa URL. Actualizar el archivo YAML vía PR cuando cambien endpoints o esquemas.

Alcance​

Lineamientos de modelado​

1. Modelo Entidad-Relación (ERD)​

2. Diccionario de Tablas Principales​

2.1 Tabla: users (Identidad Core)​

2.2 Tabla: refresh_tokens (Seguridad de Sesión)​

2.3 Tabla: verification_tokens (Identidad)​

2.4 Tabla: user_profiles (Datos comunes)​

2.4 Tabla: doctor_profiles​

2.5 Tabla: medical_taxonomy (Areas y Especialidades)​

2.6 Tabla: area_taxonomy (Relacion muchas-a-muchas area ↔ especialidad)​

2.6 Tabla: health_entities (Sedes Polimórficas)​

2.7 Tabla: affiliations (Intersección Operativa)​

2.8 Tabla: insurances​

2.9 Tabla: insurance_agreements​

2.10 Tabla: doctor_accreditations​

2.11 Tabla: reviews​

2.12 Tabla: appointments (Citas)​

2.13 Tabla: notification_preferences​

2.14 Tabla: notification_logs​

2.15 Tabla: file_assets​

2.16 Tabla: audit_logs​

2.17 Tabla: coupons (Promociones)​

2.18 Tabla: professional_posts (Blog profesional)​

3. Catálogos Jerárquicos (Master Data)​

4. Estrategia de Índices y Rendimiento​

5. Auditoría y Trazabilidad​

6. Requerimientos No Funcionales (NFR) de Datos​

6.1 Retención y Borrado​

6.2 Estrategia de Backup​

6.3 Protección de PII/PHI​

6.4 Política de anonimización y legal hold​

7. Matrix de Trazabilidad (Brecha Corta)​

8. ENUMs Oficiales​

9. DDL sugerido para ENUMs (PostgreSQL)​

10. DDL de Tablas (Ejemplo)​

11. Contratos API (Swagger Draft)​