NutriTrack — Esquema PostgreSQL

Tablas

Índices

Extensiones

Políticas RLS

500M+

Filas biométricas

1536

Dims embeddings

Esquema de tablas

🏢

organizations

Raíz del árbol multi-tenant. Cada clínica o consulta.

RLS Base JSONB settings

org_idPK · IDENTITY

BIGINT

nameNOT NULL · CHECK len

TEXT

slugUNIQUE · regex

TEXT

planCHECK IN list

TEXT

settingsCHECK object

JSONB

created_atDEFAULT now()

TIMESTAMPTZ

👤

users

Nutricionistas y pacientes con rol y hash de contraseña.

RLS LOWER(email) index

user_idPK · IDENTITY

BIGINT

org_idFK → organizations

BIGINT

emailDOMAIN email · UQ(org,email)

password_hashNOT NULL · pgcrypto

TEXT

roleNOT NULL

user_role ENUM

is_activeNOT NULL DEFAULT TRUE

BOOLEAN

last_loginNULL OK

TIMESTAMPTZ

Índices

EXPRUNIQUE (org_id, LOWER(email)) — búsquedas case-insensitive

B-tree(org_id) — FK manual obligatorio

🩺

patients

Perfil extendido del paciente. Arrays para goals y alergias.

RLS JSONB meta

patient_idPK · IDENTITY

BIGINT

user_idFK UNIQUE → users

BIGINT

nutritionist_idFK → users

BIGINT

birth_dateNULL OK

DATE

goals

TEXT[]

allergies

TEXT[]

metadataCHECK object

JSONB

Índices

GIN(goals) y (allergies) — @>, && en arrays

B-tree(nutritionist_id) — FK manual

🥗

food_items

Catálogo de alimentos con macros y búsqueda full-text/fuzzy.

FTS TSVECTOR pg_trgm LIKE

food_idPK · IDENTITY

BIGINT

org_idFK nullable (global si NULL)

BIGINT

calories_kcalCHECK ≥ 0

NUMERIC(8,2)

protein_g / carbs_g / fat_g

NUMERIC(8,2)

search_vectorGENERATED STORED

TSVECTOR

🔑 Tipos de datos

BIGINT GENERATED ALWAYS AS IDENTITY en todas las PKs (no SERIAL)
NUMERIC(p,s) para macros/calorías (nunca FLOAT)
TIMESTAMPTZ para fechas (no TIMESTAMP sin zona)
TEXT para strings (no VARCHAR/CHAR)
DOMAIN email reutilizable con regex CHECK

🗂 Indexación

FK manuales indexadas (PostgreSQL no las indexa automáticamente)
B-tree compuesto (patient_id, eaten_at DESC) para timeline
Índice PARTIAL para datos calientes (<90 días)
UNIQUE EXPR LOWER(email) para case-insensitive
GIN en arrays TEXT[] y JSONB para operadores @>, &&

⚡ Workloads especiales

Hypertabla TimescaleDB: chunk mensual, compresión 6m, retención 5a
Upsert: ON CONFLICT en PK compuesta (patient, metric, time)
EXCLUDE GIST en daterange: previene planes solapados
UNLOGGED viable para staging de sync de wearables
Columna GENERATED STORED: search_vector y rating

🔒 Seguridad y RLS

RLS activado en users, patients, biometric_logs
Función current_org_id() desde JWT session
Nutricionista ve solo sus pacientes; paciente solo a sí mismo
pgcrypto para hashing de contraseñas (crypt())
pgaudit habilitado para auditoría completa

🧬 Características avanzadas

pgvector: embeddings 1536-dim con índice IVFFLAT coseno
pg_trgm: GIN sobre nombre de alimento para LIKE '%query%'
FTS: TSVECTOR generado con idioma 'spanish' explícito
JSONB: GIN default para @> y ? ; B-tree extraído para igualdad
DATERANGE + EXCLUDE para validación anti-solapamiento

🛤 Migración segura

Columnas nullable primero; NOT NULL después de rellenar
DDL transaccional: BEGIN/ALTER/ROLLBACK para pruebas seguras
CREATE INDEX CONCURRENTLY para producción sin bloquear
Defaults NO volátiles para evitar rewrites de tabla
DROP CONSTRAINT antes de DROP COLUMN

Fragmento SQL clave — biometric_logs (upsert wearable)

upsert.sql ON CONFLICT · DO UPDATE

-- Sync wearable Fitbit → upsert atómico y eficiente
-- Requiere PK compuesta: (patient_id, metric_type, recorded_at)

INSERT INTO biometric_logs
  (patient_id, metric_type, recorded_at, value, source)
VALUES
  (42, 'weight_kg', '2026-06-16 08:00:00+02', 73.4, 'fitbit'),
  (42, 'heart_rate_bpm', '2026-06-16 08:00:00+02', 64, 'fitbit'),
  (42, 'body_fat_pct', '2026-06-16 08:00:00+02', 18.2, 'fitbit')
ON CONFLICT (patient_id, metric_type, recorded_at)
DO UPDATE SET
  value  = EXCLUDED.value,
  source = EXCLUDED.source
-- Solo escribe si el valor cambió (reduce WAL bloat)
WHERE biometric_logs.value IS DISTINCT FROM EXCLUDED.value;

fts_search.sql Full-text + Fuzzy

-- Búsqueda de alimentos: full-text primero, fallback fuzzy

SELECT food_id, name, brand, calories_kcal,
       ts_rank(search_vector, query) AS rank
FROM food_items,
     to_tsquery('spanish', 'pollo & asado') query
WHERE search_vector @@ query
   OR name % 'pollo asado'         -- pg_trgm similarity fallback
ORDER BY rank DESC, similarity(name, 'pollo asado') DESC
LIMIT 20;

-- Búsqueda vectorial de recomendaciones similares
SELECT rec_id, content, category, confidence
FROM ai_recommendations
WHERE patient_id = 42
ORDER BY embedding <=> '[0.12, -0.34, ...]'::vector -- coseno
LIMIT 5;