Auditoría PostgreSQL — LeadFlow SaaS

📊

Resumen de impacto estimado

Área	Hallazgo	Impacto en prod.	Esfuerzo fix	Prioridad
Seguridad RLS	`lead_events` sin RLS activado	Fuga de datos entre tenants	🟢 Bajo	P0
Seguridad RLS	Políticas `IN (SELECT …)` por fila	Full table scan en cada row check	🟢 Bajo	P0
Rendimiento	Query 3: LIKE `%term%` + OFFSET en 8M filas	Timeout >30 s en prod	🟡 Medio	P0
Rendimiento	FK `users.org_id` sin índice	Seq scan en joins	🟢 Bajo	P0
Esquema	`TIMESTAMP` sin timezone en 5 tablas	Bug DST, datos incorrectos	🔴 Alto	P1
Esquema	`SERIAL` en leads/sequences/enrollments	Límite 2B, no distribuido	🟡 Medio	P1
Esquema	`VARCHAR(255)` genérico	Sin impacto directo	🟢 Bajo	P2
Rendimiento	Sin índice compuesto `(org_id, status, created_at)`	Slow query en dashboard	🟢 Bajo	P1
Rendimiento	Query 1: `SELECT *` implícito en subqueries	Rows transferidas innecesarias	🟢 Bajo	P2
Rendimiento	`lead_events` sin partición creada	Error en insert si falta partición	🟡 Medio	P1
Seguridad	Búsqueda sin full-text index (SQL injection via LIKE)	Performance + riesgo menor	🟡 Medio	P2

🚨

Hallazgos Críticos (P0)

Critical

C1 · lead_events sin Row Level Security

La tabla lead_events tiene particionado por rango pero no tiene RLS activado ni política. Cualquier usuario autenticado puede leer eventos de otros tenants con un query directo. En Supabase, el cliente PostgREST expone directamente cualquier tabla sin RLS.

Seguridad · RLS

Corrección — añadir a la migración de seguridad:

SQL FIX

-- Activar RLS en la tabla particionada (aplica a todas las particiones)
ALTER TABLE lead_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE lead_events FORCE ROW LEVEL SECURITY;

-- Política de aislamiento por tenant
-- Patrón correcto: (SELECT auth.uid()) evaluado una vez, no por fila
CREATE POLICY lead_events_org_isolation ON lead_events
  FOR ALL USING (
    lead_id IN (
      SELECT id FROM leads
      WHERE org_id = (SELECT org_id FROM users WHERE id = (SELECT auth.uid()))
    )
  );

-- Índice en lead_id para que la política sea O(log n)
CREATE INDEX idx_lead_events_lead_id ON lead_events(lead_id);

Critical

C2 · Políticas RLS llaman función por fila (anti-patrón Supabase)

La política de sequences usa IN (SELECT …) sin envolver en (SELECT …). PostgreSQL evalúa la subquery para cada fila, causando un full scan en users por cada fila de sequences evaluada. En tablas con millones de filas esto destruye el rendimiento.

Seguridad · RLS · Perf

❌ Actual

-- Subquery evaluada POR CADA FILA
CREATE POLICY sequences_org_isolation
  ON sequences
  FOR ALL USING (
    org_id IN (
      SELECT org_id FROM users
      WHERE id = auth.uid()
    )
  );

✅ Correcto

-- (SELECT …) evalúa UNA VEZ por query
CREATE POLICY sequences_org_isolation
  ON sequences
  FOR ALL USING (
    org_id = (SELECT org_id
                FROM users
                WHERE id = (SELECT auth.uid()))
  );

Aplicar mismo patrón a la política leads_org_isolation (ya la usa pero en secuencias no).

Critical

C3 · Query 3: LIKE con comodín inicial + OFFSET — timeout garantizado

El patrón LIKE '%term%' impide usar índices B-tree. Con 8M filas sin FTS, PostgreSQL hace Seq Scan completo. Además OFFSET $3 en paginación descarta filas en memoria: para la página 500 lee 10.000 filas y tira 9.980. Combinados provocan timeouts a partir de ~1M filas.

Rendimiento · Búsqueda

Solución: full-text search con GIN index + cursor pagination:

SQL FIX

-- 1. Columna de búsqueda generada (no ocupa espacio duplicado)
ALTER TABLE leads
  ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (
      to_tsvector('spanish', coalesce(name, '') || ' ' || coalesce(email, ''))
    ) STORED;

-- 2. Índice GIN para búsqueda full-text O(log n)
CREATE INDEX idx_leads_search_gin ON leads USING GIN(search_vector);

-- 3. Query con cursor pagination (sin OFFSET)
SELECT id, name, email, score, status, created_at
FROM   leads
WHERE  org_id = $1
  AND  search_vector @@ plainto_tsquery('spanish', $2)
  AND  deleted_at IS NULL
  AND  id > $3           -- cursor (último id de la página anterior)
ORDER BY id
LIMIT 20;

Critical

C4 · FK users.org_id sin índice

La clave foránea users.org_id → organizations.id no tiene índice. Toda query que joinee estas tablas (incluyendo las políticas RLS) hace Seq Scan en users. Regla: siempre indexar FKs, sin excepciones.

Rendimiento · FK

SQLFIX

-- Índices en claves foráneas (los que faltan)
CREATE INDEX idx_users_org_id         ON users(org_id);
CREATE INDEX idx_leads_org_id         ON leads(org_id);
CREATE INDEX idx_sequences_org_id     ON sequences(org_id);
CREATE INDEX idx_enrollments_lead_id  ON enrollments(lead_id);
CREATE INDEX idx_enrollments_seq_id   ON enrollments(sequence_id);

⚠️

Alta Severidad (P1)

High

H1 · TIMESTAMP sin timezone en todas las tablas

TIMESTAMP almacena sin zona horaria. En producción multi-región o con cambios de DST los datos quedan inconsistentes. Usar siempre TIMESTAMPTZ (alias timestamp with time zone).

Esquema · Tipos

SQLMIGRATION

-- Migración para corregir tipos de timestamp (requiere mantenimiento breve)
ALTER TABLE organizations
  ALTER COLUMN created_at TYPE timestamptz
    USING created_at AT TIME ZONE 'UTC';

ALTER TABLE users
  ALTER COLUMN last_login TYPE timestamptz
    USING last_login AT TIME ZONE 'UTC';

-- Repetir para: leads.deleted_at, leads.created_at, leads.updated_at,
-- sequences.created_at, enrollments.enrolled_at, enrollments.completed_at,
-- lead_events.created_at (partición)

High

H2 · SERIAL en IDs — escalado limitado y no distribuido

SERIAL usa int4 (máx 2.147M). Con 8M leads actuales y crecimiento previsto, enrollments y lead_events pueden llegar al límite. Usar BIGINT GENERATED ALWAYS AS IDENTITY o UUIDv7 para IDs distribuibles.

Esquema · IDs

❌ Actual

CREATE TABLE leads (
  id SERIAL PRIMARY KEY,
  -- int (4 bytes, max 2.1B)
  ...

✅ Recomendado

CREATE TABLE leads (
  id bigint GENERATED ALWAYS AS
     IDENTITY PRIMARY KEY,
  -- bigint (8 bytes, max 9.2 × 10¹⁸)
  ...

High

H3 · Índice compuesto faltante para Query 1 (dashboard)

La query del dashboard filtra por (org_id, deleted_at IS NULL, created_at >= …) y agrupa por status. Sin un índice compuesto hace Seq Scan en 8M filas. El orden del índice importa: igualdad primero, rango después.

Rendimiento · Índices

SQLFIX

-- Índice compuesto: igualdad (org_id) → rango (created_at) → columnas cubiertas
CREATE INDEX idx_leads_org_active_created
  ON leads(org_id, created_at DESC)
  INCLUDE(status, score)
  WHERE deleted_at IS NULL;  -- índice parcial: excluye borrados

-- EXPLAIN ANALYZE antes/después (ejecutar en staging con datos reales):
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT org_id, status, COUNT(*), AVG(score)
FROM  leads
WHERE org_id = '550e8400-e29b-41d4-a716-446655440000'
  AND deleted_at IS NULL
  AND created_at >= NOW() - INTERVAL '30 days'
GROUP BY org_id, status;

High

H4 · lead_events particionado sin particiones creadas

La tabla usa PARTITION BY RANGE (created_at) pero no hay particiones definidas. PostgreSQL lanza ERROR: no partition of relation "lead_events" en cualquier INSERT. Además faltan índices en las particiones hijas.

Esquema · Particionado

SQLFIX

-- Crear particiones mensuales (adaptar al rango histórico real)
CREATE TABLE lead_events_2026_05 PARTITION OF lead_events
  FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE lead_events_2026_06 PARTITION OF lead_events
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

CREATE TABLE lead_events_2026_07 PARTITION OF lead_events
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

-- Índice en cada partición (o globalmente si PG ≥ 11)
CREATE INDEX ON lead_events(lead_id, created_at DESC);

-- Automatizar con pg_partman o función cron en Supabase Edge

💡

Media Severidad (P2)

Medium

M1 · VARCHAR(255) innecesario — usar TEXT

En PostgreSQL no hay diferencia de rendimiento entre VARCHAR(255) y TEXT. Usar TEXT simplifica el esquema y evita errores silenciosos de truncado. Añadir CHECK (length(email) < 320) si se quiere validar.

Esquema · Tipos

Medium

M2 · Sin restricciones ON DELETE en FKs

Ninguna FK define comportamiento al borrar el padre. Al eliminar una organización, sus usuarios, leads, sequences y eventos quedan huérfanos. Definir ON DELETE CASCADE o ON DELETE RESTRICT según la lógica de negocio.

Integridad · FK

SQLFIX

ALTER TABLE users
  DROP CONSTRAINT users_org_id_fkey,
  ADD CONSTRAINT  users_org_id_fkey
    FOREIGN KEY (org_id) REFERENCES organizations(id)
    ON DELETE CASCADE;  -- o RESTRICT según política

-- Mismo patrón para leads.org_id, sequences.org_id

Medium

M3 · enrollments sin FK declaradas a leads/sequences

enrollments.lead_id y enrollments.sequence_id son enteros simples sin restricción FOREIGN KEY. Se pueden insertar enrollments huérfanos. Añadir las FK garantiza integridad referencial a nivel de base de datos.

Integridad · FK

SQLFIX

ALTER TABLE enrollments
  ADD CONSTRAINT enrollments_lead_id_fkey
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
  ADD CONSTRAINT enrollments_sequence_id_fkey
    FOREIGN KEY (sequence_id) REFERENCES sequences(id) ON DELETE CASCADE;

✅

Checklist de revisión (estado actual)

✗

Columnas WHERE/JOIN indexadas

✗

RLS activado en todas las tablas

✗

Políticas RLS con patrón (SELECT auth.uid())

✗

Claves foráneas con índices

✗

TIMESTAMPTZ en todos los campos de fecha

✗

Sin OFFSET pagination en tablas grandes

✗

IDs con BIGINT en tablas de alto volumen

✗

Particiones creadas en lead_events

~

EXPLAIN ANALYZE en queries críticas

~

Transacciones cortas (no bloqueos externos)

✓

Queries parametrizadas (sin SQL injection)

✓

JSONB para datos semi-estructurados

🗺️

Plan de migración en 3 fases

Fase 1 — Esta semana

1. Activar RLS en lead_events + política
2. Corregir política sequences (IN → =)
3. Crear índices de FKs faltantes
4. Crear particiones lead_events

Fase 2 — Siguiente sprint

1. Añadir columna search_vector + GIN index
2. Reemplazar queries LIKE con FTS
3. Crear índice compuesto dashboard
4. Añadir ON DELETE en FKs

Fase 3 — Siguiente mes

1. Migrar TIMESTAMP → TIMESTAMPTZ
2. Migrar SERIAL → BIGINT IDENTITY
3. Cambiar VARCHAR(255) → TEXT
4. Activar pg_stat_statements

Auditoría de Base de Datos — LeadFlow SaaS