Base de datos

Schema, migraciones, convenciones y workflow de la BD.

Stack

• PostgreSQL 15 (Supabase local en dev, Supabase managed en prod)
• Drizzle ORM como capa de acceso (single schema file)
• drizzle-kit para generación de migraciones
• postgres.js como driver (rápido, soporta Supabase pooler)

Schema

Single file en src/lib/server/db/schema.ts. ~25 tablas mapeadas a los 9 módulos del MVP.

Ver docs/MODULES.md para el mapeo módulo → tablas.

Convenciones

• Nombres de tabla: snake_case plural (patients, meal_plan_items).
• Nombres de columna: snake_case (full_name, created_at).
• PKs: uuid con defaultRandom() excepto profiles.id que viene de auth.users.
• FKs: references() con onDelete explícito:
  • cascade para dependencias fuertes (mediciones → paciente).
  • restrict para datos financieros/históricos (pagos → paciente).
  • set null para referencias opcionales.
• Timestamps: timestamp({ withTimezone: true }) siempre.
• Montos: decimal(precision, scale) con escala explícita. CLP como integer (no hay centavos).
• Enums: pgEnum para campos con valores fijos.
• Índices: en FKs y en columnas de búsqueda frecuente. Definidos inline en el pgTable(...).
• No usar mode: 'string' para timestamps.

Patrones de modelado

Profile que extiende auth.users:

export const profiles = pgTable('profiles', {
  id: uuid('id').primaryKey(), // = auth.users.id (no FK porque Supabase la maneja)
  role: userRoleEnum('role').notNull(),
  email: text('email').notNull().unique(),
  fullName: text('full_name').notNull(),
  // ...
});

Relación 1:1 con extensión:

export const patients = pgTable('patients', {
  id: uuid('id').primaryKey().defaultRandom(),
  profileId: uuid('profile_id')
    .notNull()
    .unique()
    .references(() => profiles.id, { onDelete: 'cascade' }),
  nutritionistId: uuid('nutritionist_id')
    .notNull()
    .references(() => profiles.id, { onDelete: 'restrict' }),
  // ...
});

Relación many-to-many con tabla de unión:

export const patientAchievements = pgTable(
  'patient_achievements',
  {
    patientId: uuid('patient_id').notNull().references(() => patients.id, { onDelete: 'cascade' }),
    achievementId: uuid('achievement_id').notNull().references(() => achievements.id, { onDelete: 'cascade' }),
    unlockedAt: timestamp('unlocked_at', { withTimezone: true }).defaultNow().notNull()
  },
  (t) => [primaryKey({ columns: [t.patientId, t.achievementId] })]
);

JSONB para datos semi-estructurados:

services: jsonb('services').$type<Array<{ name: string; priceClp: number; description: string }>>()

Usar solo cuando la forma varía (ej. servicios del profesional). Para datos estructurados, crear tablas.

Migraciones

Workflow en dev

# 1. Editar src/lib/server/db/schema.ts
# 2. Generar la migración
pnpm db:generate
# Esto crea un archivo .sql en /drizzle/

# 3. Aplicar a la BD local
pnpm db:migrate
# O más rápido para iterar:
pnpm db:push

db:push aplica cambios directamente sin generar migration files — solo para dev. db:migrate aplica las migrations generadas — usar antes de commit y en prod.

Workflow para deploy

# 1. Local: db:generate, revisar SQL generado, commitear
# 2. Prod: db:migrate (con DATABASE_URL apuntando a Supabase prod)

Naming de migrations

Drizzle usa timestamps: 0000_white_silver_surfer.sql. No renombrar a mano.

RLS (Row Level Security)

Por ahora NO definidas. Las queries pasan por Drizzle con service-role connection, así que RLS no bloquea el server code. Pero:

• Defensa en profundidad: si en el futuro habilitamos queries desde el browser via Supabase client, RLS ya debería estar.
• Auth-based queries: si en algún momento un paciente hace queries al server con su sesión, RLS en patients debería limitar a nutritionist_id = auth.uid().

Cuando agreguemos RLS, lo hacemos en supabase/migrations/ (no en Drizzle migrations, porque RLS es concepto de Postgres/Supabase, no del ORM):

-- supabase/migrations/20260101120000_rls_patients.sql
ALTER TABLE patients ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Patients can read their own record"
  ON patients FOR SELECT
  USING (profile_id = auth.uid());

CREATE POLICY "Nutritionists can read their patients"
  ON patients FOR SELECT
  USING (nutritionist_id = auth.uid());

Seed

supabase/seed.sql se ejecuta automáticamente con pnpm supabase:reset. Tiene:

• Categorías de alimentos del Cono Sur
• Achievements por defecto (gamificación)

Cliente Drizzle

Single client en src/lib/server/db/index.ts. Se importa así:

import { db, tables } from '$server/db';
// o equivalentemente:
import { db } from '$server/db';
import * as tables from '$server/db/schema'; // menos limpio, preferí el barrel

db está tipado, las queries heredan tipos del schema.

Tipos inferidos

Para cada tabla, Drizzle infiere:

type Patient = typeof tables.patients.$inferSelect;
type NewPatient = typeof tables.patients.$inferInsert;

Están exportados en schema.ts. Usar estos en services, no any.

Queries comunes (cheat sheet)

import { db, tables } from '$server/db';
import { eq, and, desc, asc, like, sql, gte, lte, inArray } from 'drizzle-orm';

// Select con where
const myPatients = await db
  .select()
  .from(tables.patients)
  .where(eq(tables.patients.nutritionistId, userId))
  .orderBy(desc(tables.patients.createdAt));

// Select con join + where compuesto
const results = await db
  .select({ patient: tables.patients, profile: tables.profiles })
  .from(tables.patients)
  .innerJoin(tables.profiles, eq(tables.profiles.id, tables.patients.profileId))
  .where(and(eq(tables.patients.nutritionistId, userId), eq(tables.patients.isActive, true)));

// Insert
const [newPatient] = await db
  .insert(tables.patients)
  .values({ profileId, nutritionistId, birthDate })
  .returning();

// Update
await db
  .update(tables.patients)
  .set({ isActive: false })
  .where(eq(tables.patients.id, patientId));

// Delete
await db.delete(tables.appointments).where(eq(tables.appointments.id, apptId));

// Transacción
await db.transaction(async (tx) => {
  await tx.insert(tables.patients).values(...);
  await tx.insert(tables.profiles).values(...);
});

Limits Always Free (Supabase free tier)

Para un MVP chico sobra. Cuando crezca, migrar a Pro de Supabase o a Postgres managed en Oracle Cloud.

Backups

Drizzle migrations son el "schema backup". Para data backups:

• Dev: supabase db dump exporta todo.
• Prod: configurar backups automáticos en el dashboard de Supabase (Pro plan) o vía pg_dump cron en Oracle Cloud.

Performance

• Índices: definidos en pgTable(...) para FKs y queries frecuentes. Si una query es lenta, agregar índice.
• N+1: usar innerJoin / leftJoin en vez de loops.
• Paginación: usar .limit(N).offset(M) por ahora; migrar a cursor-based cuando haya >10K rows por tabla.
• Aggregations: usar SQL crudo con sql template tag si Drizzle no tiene helper.

Limitaciones actuales

• No hay soft delete. Borrar es hard delete (excepto payments que están protegidos con restrict).
• No hay auditoría. No guardamos quién modificó qué. Agregar tabla audit_log post-MVP.
• No hay multi-tenancy. Un nutricionista no puede compartir pacientes con otros (salvo admin).
• No hay multi-currency. Todo en CLP.