NutriCoach Pro — Diseño de Esquema de Base de Datos

🗺

Diagrama ERD

Entidades y relaciones del modelo de datos

📜

Migración SQL — PostgreSQL 16

Tablas principales con constraints, soft delete y auditoría

SQL Migration

migrations/0001_nutricoach_init.sql

-- ============================================================
-- NutriCoach Pro -- Migracion inicial
-- Stack: PostgreSQL 16 + Supabase
-- ============================================================

-- ENUMS
CREATE TYPE plan_clinica AS ENUM ('starter', 'pro', 'enterprise');
CREATE TYPE rol_profesional AS ENUM ('admin', 'nutricionista', 'dietista');
CREATE TYPE objetivo_paciente AS ENUM ('perdida_peso', 'ganancia_masa', 'mantenimiento', 'tratamiento');
CREATE TYPE estado_consulta AS ENUM ('programada', 'completada', 'cancelada');
CREATE TYPE operacion_audit AS ENUM ('INSERT', 'UPDATE', 'DELETE');

-- ============================================================
-- TABLA: clinicas (tenant raiz)
-- ============================================================
CREATE TABLE clinicas (
  id            TEXT           PRIMARY KEY DEFAULT gen_cuid(),
  nombre        TEXT           NOT NULL,
  slug          TEXT           NOT NULL UNIQUE,
  plan          plan_clinica    NOT NULL DEFAULT 'starter',
  max_pacientes INT            NOT NULL DEFAULT 200,
  created_at    TIMESTAMPTZ    NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ    NOT NULL DEFAULT now(),
  deleted_at    TIMESTAMPTZ    -- soft delete
);

-- ============================================================
-- TABLA: pacientes
-- ============================================================
CREATE TABLE pacientes (
  id              TEXT             PRIMARY KEY DEFAULT gen_cuid(),
  clinica_id      TEXT             NOT NULL REFERENCES clinicas(id),
  profesional_id  TEXT             REFERENCES profesionales(id),
  user_id         UUID             REFERENCES auth.users(id),
  nombre          TEXT             NOT NULL,
  apellidos       TEXT             NOT NULL,
  email           TEXT,
  fecha_nacimiento DATE,
  objetivo        objetivo_paciente,
  altura_cm       NUMERIC(5,2),
  estado          TEXT             NOT NULL DEFAULT 'activo',
  version         INTEGER          NOT NULL DEFAULT 1, -- bloqueo optimista
  created_at      TIMESTAMPTZ      NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ      NOT NULL DEFAULT now(),
  deleted_at      TIMESTAMPTZ      -- NUNCA DELETE real
);

-- ============================================================
-- TABLA: planes_alimentacion (con versioning)
-- ============================================================
CREATE TABLE planes_alimentacion (
  id             TEXT       PRIMARY KEY DEFAULT gen_cuid(),
  paciente_id    TEXT       NOT NULL REFERENCES pacientes(id),
  profesional_id TEXT       NOT NULL REFERENCES profesionales(id),
  nombre         TEXT       NOT NULL,
  kcal_objetivo  INTEGER,
  proteinas_g    NUMERIC(6,2),
  carbos_g       NUMERIC(6,2),
  grasas_g       NUMERIC(6,2),
  activo         BOOLEAN    NOT NULL DEFAULT false,
  version        INTEGER    NOT NULL DEFAULT 1,
  fecha_inicio   DATE       NOT NULL,
  fecha_fin      DATE,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at     TIMESTAMPTZ
);

-- Solo un plan activo por paciente (indice parcial unico)
CREATE UNIQUE INDEX idx_plan_activo_unico
  ON planes_alimentacion(paciente_id)
  WHERE activo = true AND deleted_at IS NULL;

-- ============================================================
-- TABLA: seguimiento_diario (alto volumen: ~50k/mes)
-- Con particionamiento mensual por fecha
-- ============================================================
CREATE TABLE seguimiento_diario (
  id               TEXT        PRIMARY KEY DEFAULT gen_cuid(),
  paciente_id      TEXT        NOT NULL REFERENCES pacientes(id),
  plan_id          TEXT        REFERENCES planes_alimentacion(id),
  fecha            DATE        NOT NULL,
  peso_kg          NUMERIC(5,2),
  kcal_consumidas  INTEGER,
  adherencia_pct   INTEGER     CHECK(adherencia_pct BETWEEN 0 AND 100),
  agua_ml          INTEGER,
  pasos            INTEGER,
  notas            TEXT,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(paciente_id, fecha)
) PARTITION BY RANGE(fecha);

-- Particiones mensuales
CREATE TABLE seguimiento_2026_06 PARTITION OF seguimiento_diario
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

-- ============================================================
-- AUDIT LOG (trigger-based, append-only)
-- ============================================================
CREATE TABLE audit_log (
  id            BIGINT    GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tabla         TEXT      NOT NULL,
  registro_id   TEXT      NOT NULL,
  operacion     operacion_audit NOT NULL,
  datos_antes   JSONB,
  datos_despues JSONB,
  user_id       UUID      REFERENCES auth.users(id),
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION fn_audit_trigger() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log(tabla, registro_id, operacion, datos_antes, datos_despues, user_id)
  VALUES(
    TG_TABLE_NAME,
    COALESCE(NEW.id, OLD.id),
    TG_OP::operacion_audit,
    CASE WHEN TG_OP != 'INSERT' THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP != 'DELETE' THEN to_jsonb(NEW) END,
    auth.uid()
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_pacientes
  AFTER INSERT OR UPDATE OR DELETE ON pacientes
  FOR EACH ROW EXECUTE FUNCTION fn_audit_trigger();

CREATE TRIGGER trg_audit_consultas
  AFTER INSERT OR UPDATE OR DELETE ON consultas
  FOR EACH ROW EXECUTE FUNCTION fn_audit_trigger();

⚡

Drizzle ORM Schema — TypeScript

Schema type-safe para Next.js 14 App Router

Drizzle ORM

db/schema/nutricoach.ts

import {
  pgTable, pgEnum, text, boolean, integer, numeric,
  date, timestamp, uniqueIndex, index,
} from 'drizzle-orm/pg-core';
import { createId } from '@paralleldrive/cuid2';

// ENUMS
export const planClinicaEnum = pgEnum('plan_clinica', ['starter', 'pro', 'enterprise']);
export const rolProfEnum     = pgEnum('rol_profesional', ['admin', 'nutricionista', 'dietista']);
export const objetivoEnum    = pgEnum('objetivo_paciente', ['perdida_peso', 'ganancia_masa', 'mantenimiento']);

// CLINICAS
export const clinicas = pgTable('clinicas', {
  id:          text('id').primaryKey().$defaultFn(() => createId()),
  nombre:      text('nombre').notNull(),
  slug:        text('slug').notNull().unique(),
  plan:        planClinicaEnum('plan').notNull().default('starter'),
  maxPacientes: integer('max_pacientes').notNull().default(200),
  createdAt:   timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt:   timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  deletedAt:   timestamp('deleted_at', { withTimezone: true }),
});

// PACIENTES
export const pacientes = pgTable('pacientes', {
  id:            text('id').primaryKey().$defaultFn(() => createId()),
  clinicaId:     text('clinica_id').notNull().references(() => clinicas.id),
  profesionalId: text('profesional_id').references(() => profesionales.id),
  nombre:        text('nombre').notNull(),
  apellidos:     text('apellidos').notNull(),
  objetivo:      objetivoEnum('objetivo'),
  alturaCm:      numeric('altura_cm', { precision: 5, scale: 2 }),
  version:       integer('version').notNull().default(1), // optimistic lock
  createdAt:     timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt:     timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  deletedAt:     timestamp('deleted_at', { withTimezone: true }),
}, (t) => ({
  idxClinicaActivo: index('idx_pac_clinica_activo').on(t.clinicaId, t.deletedAt),
  idxProfesional:   index('idx_pac_profesional').on(t.profesionalId),
}));

// SEGUIMIENTO_DIARIO (particionado por fecha)
export const seguimientoDiario = pgTable('seguimiento_diario', {
  id:             text('id').primaryKey().$defaultFn(() => createId()),
  pacienteId:     text('paciente_id').notNull().references(() => pacientes.id),
  planId:         text('plan_id').references(() => planesAlimentacion.id),
  fecha:          date('fecha').notNull(),
  pesoKg:         numeric('peso_kg', { precision: 5, scale: 2 }),
  kcalConsumidas: integer('kcal_consumidas'),
  adherenciaPct:  integer('adherencia_pct'),
  createdAt:      timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => ({
  uniqPacienteFecha: uniqueIndex('uq_seg_paciente_fecha').on(t.pacienteId, t.fecha),
  idxFecha:          index('idx_seg_fecha').on(t.fecha),
}));

// Types inferidos
export type InsertPaciente = typeof pacientes.$inferInsert;
export type SelectPaciente = typeof pacientes.$inferSelect;

🔒

Políticas RLS — Row-Level Security

Seguridad por fila para aislamiento multi-tenant en Supabase

pac_clinica_isolation

pacientes

FOR ALL · app_user

-- Profesionales solo ven pacientes de SU clinica
ALTER TABLE pacientes ENABLE ROW LEVEL SECURITY;

CREATE POLICY pac_clinica_isolation ON pacientes
  FOR ALL TO app_user
  USING (
    clinica_id IN (
      SELECT clinica_id FROM profesionales
      WHERE user_id = auth.uid() AND activo = true
    )
  );

pac_no_deleted

pacientes

FOR SELECT · app_user

-- Soft delete: nunca mostrar pacientes eliminados
CREATE POLICY pac_no_deleted ON pacientes
  FOR SELECT TO app_user
  USING (deleted_at IS NULL);

consultas_notas_privadas

consultas

FOR SELECT · app_user

-- Notas privadas: solo el profesional creador o admin
ALTER TABLE consultas ENABLE ROW LEVEL SECURITY;

CREATE POLICY consultas_notas_privadas ON consultas
  FOR SELECT TO app_user
  USING (
    deleted_at IS NULL AND (
      profesional_id IN (
        SELECT id FROM profesionales
        WHERE user_id = auth.uid()
      )
      OR EXISTS (
        SELECT 1 FROM profesionales p
        JOIN pacientes pac ON pac.clinica_id = p.clinica_id
        WHERE p.user_id = auth.uid() AND p.rol = 'admin'
          AND pac.id = consultas.paciente_id
      )
    )
  );

seguimiento_propio

seguimiento_diario

FOR ALL · paciente_user

-- Paciente solo ve su propio seguimiento
ALTER TABLE seguimiento_diario ENABLE ROW LEVEL SECURITY;

CREATE POLICY seguimiento_propio ON seguimiento_diario
  FOR ALL TO paciente_user
  USING (
    paciente_id IN (
      SELECT id FROM pacientes
      WHERE user_id = auth.uid()
    )
  );

⚡

Estrategia de Índices

14 índices optimizados para las queries críticas del dominio

Tabla	Nombre del Índice	Columnas	Tipo	Razón
clinicas	idx_clinicas_slug	slug	Unique	Lookup por slug en auth flow
profesionales	idx_prof_clinica_activo	clinica_id, activo, deleted_at	Composite	Listar profesionales activos por clínica
profesionales	idx_prof_user	user_id	FK	JOIN con auth.users en cada request
pacientes	idx_pac_clinica_activo	clinica_id, deleted_at	Partial	WHERE deleted_at IS NULL (listado principal)
pacientes	idx_pac_profesional	profesional_id	FK	Pacientes asignados a un profesional
pacientes	idx_pac_email	email, clinica_id	Composite	Búsqueda de paciente por email en clínica
planes_alimentacion	idx_plan_activo_unico	paciente_id WHERE activo=true	Partial Unique	Solo 1 plan activo por paciente
planes_alimentacion	idx_plan_paciente_fecha	paciente_id, fecha_inicio DESC	Composite	Historial de planes del paciente
consultas	idx_cons_pac_fecha	paciente_id, fecha DESC	Composite	Historial de consultas del paciente
consultas	idx_cons_prof_fecha	profesional_id, fecha, estado	Covering	Agenda del profesional (sin heap fetch)
seguimiento_diario	uq_seg_paciente_fecha	paciente_id, fecha	Unique	Un registro por día por paciente
seguimiento_diario	idx_seg_pac_rango	paciente_id, fecha	Composite	Gráficas de evolución (rango de fechas)
audit_log	idx_audit_tabla_registro	tabla, registro_id	Composite	Historial de cambios de un registro
audit_log	idx_audit_user_ts	user_id, created_at DESC	Composite	Actividad de un usuario para compliance

⚠

Errores Comunes Evitados

Decisiones de diseño que previenen problemas a escala

📄

Particionamiento en seguimiento_diario

Con 50k registros/mes, la tabla crecería 600k filas/año. Partición mensual por fecha mantiene queries en menos de 1M filas activas.

🔐

CUID en lugar de UUID secuencial

Los UUIDs v4 aleatorios fragmentan el B-tree. CUIDs son ordenables por tiempo, mejoran locality de índices en tablas grandes.

🧹

Índice parcial en soft delete

WHERE deleted_at IS NULL sin índice = full scan. El índice parcial solo indexa filas activas, reduciendo tamaño y mejorando velocidad.

🔒

Bloqueo optimista en pacientes/planes

Columna version previene que dos profesionales sobreescriban cambios simultáneos. El UPDATE falla si version no coincide.

🏢

RLS en vez de filtros en capa app

Un bug en el ORM no expondrá datos de otra clínica: la base de datos rechaza la query a nivel de fila, no el código de aplicación.

📊

Unique constraint plan activo