Resumen ejecutivo
Estado del esquema
Necesita mejoras
| Tablas analizadas | 9 |
| Columnas totales | 87 columnas |
| Foreign keys declaradas | 0 de ~18 esperadas |
| Índices existentes | 7 total (varios redundantes) |
| Tablas con PKs | 9/9 ✓ |
| Volumen estimado | ~4.1M filas |
Diagnóstico de rendimiento
Crítico
| Query | Freq/día | P95 (ms) | Estado |
|---|---|---|---|
| pipeline_view | 8,500 | 920 | Crítico |
| revenue_forecast | 120 | 1,850 | Crítico |
| lead_search | 4,200 | 340 | Lento |
| campaign_stats | 950 | 640 | Lento |
| deal_activities | 3,100 | 185 | Aceptable |
| my_leads | 6,800 | 280 | Aceptable |
Puntuación de salud por dimensión
2/10
Integridad referencial
0 FK declaradas sobre ~18 columnas de relación
4/10
Cobertura de índices
7 índices existentes, 7 de alta prioridad faltantes
4.5/10
Normalización
5 violaciones 1NF/3NF (CSV columns, JSON en VARCHAR)
5.5/10
Constraints de datos
10 constraints faltantes (NOT NULL, UNIQUE, CHECK)
8/10
Tipos de datos
Tipos mayormente correctos; 2 antipatrones VARCHAR
7.5/10
Naming conventions
snake_case consistente, todos plural excepto 1
ERD — Diagrama de entidad-relación (generado automáticamente)
PK = Clave primaria
FK = Clave foránea (sin constraint declarado)
||--o{ = relación uno a muchos
🏢 companies
PKidINTEGER
nameVARCHAR
domainVARCHAR
tags*VARCHAR
FKowner_user_idINTEGER
👤 users
PKidINTEGER
emailVARCHAR UNIQUE
full_nameVARCHAR
roleVARCHAR
preferences*VARCHAR
🎯 leads
PKidINTEGER
emailVARCHAR
company_name*VARCHAR
FKcompany_idINTEGER
statusVARCHAR
FKassigned_toINTEGER
tags*VARCHAR
+5 más
🔁 pipelines
PKidINTEGER
nameVARCHAR
FKowner_user_idINTEGER
📋 pipeline_stages
PKidINTEGER
FKpipeline_idINTEGER
nameVARCHAR
probabilityINTEGER
💵 deals
PKidINTEGER
titleVARCHAR
FKlead_idINTEGER
FKpipeline_idINTEGER
FKstage_idINTEGER
FKowner_user_idINTEGER
amountDECIMAL
contact_names*VARCHAR
☕ activities
PKidINTEGER
typeVARCHAR
FKlead_idINTEGER
FKdeal_idINTEGER
FKuser_idINTEGER
💌 email_campaigns
PKidINTEGER
nameVARCHAR
FKsender_user_idINTEGER
target_segment*VARCHAR
📨 campaign_recipients
PKidINTEGER
FKcampaign_idINTEGER
FKlead_idINTEGER
⚠ Columnas marcadas con *
contienen datos no atómicos (CSV, JSON serializado) — violaciones de 1NF que impiden indexación y búsquedas eficientes.
Issues detectados: Constraints y normalización
Constraints faltantes (10 issues)
pipeline_stages
MISSING_FOREIGN_KEY
medium
pipeline_id no tiene FK constraint declarado
Fix: ALTER TABLE pipeline_stages ADD CONSTRAINT fk_ps_pipeline FOREIGN KEY (pipeline_id) REFERENCES pipelines(id);
users
MISSING_UNIQUE
medium
email debería tener constraint UNIQUE
Fix: CREATE UNIQUE INDEX idx_users_email ON users(email);
deals
MISSING_NOT_NULL
low
title permite NULL pero es campo obligatorio de negocio
Fix: ALTER TABLE deals ALTER COLUMN title SET NOT NULL;
leads
MISSING_CHECK
low
score sin validación de rango; puede recibir valores negativos
Fix: ALTER TABLE leads ADD CONSTRAINT chk_score CHECK (score BETWEEN 0 AND 100);
pipeline_stages
MISSING_CHECK
low
probability sin CHECK (0-100); puede almacenar 9999
Fix: ALTER TABLE pipeline_stages ADD CONSTRAINT chk_prob CHECK (probability BETWEEN 0 AND 100);
Violaciones de normalización (5 issues)
leads
1NF VIOLATION
high
tags VARCHAR(500) almacena CSV: "inbound,hot,q2,enterprise"
Fix: Crear tabla lead_tags (lead_id, tag) + índice GIN o índice en tag;
deals
1NF VIOLATION
high
contact_names VARCHAR(500): "Ana García, Pedro López" — múltiples contactos en una columna
Fix: Tabla deal_contacts (deal_id, lead_id) para relación N:M;
users
ANTIPATTERN
high
preferences VARCHAR(2000) serializa JSON: no indexable, sin validación de schema
Fix: Migrar a columna JSONB o tabla user_preferences separada;
leads
3NF VIOLATION
medium
company_name redundante con companies.name cuando company_id está presente
Fix: Deprecar company_name tras completar la relación FK a companies;
deals
3NF VIOLATION
medium
deal_source duplica leads.source; no hay sincronización garantizada
Fix: Normalizar a tabla lead_sources(id, name) compartida;
Recomendaciones de índices (optimizador automático)
Alta prioridad — 7 índices
CREATE INDEX idx_deals_lead_id_join ON deals (lead_id);
CREATE INDEX idx_activities_deal_id ON activities (deal_id);
CREATE INDEX idx_activities_user_id_join ON activities (user_id);
CREATE INDEX idx_campaign_recipients_campaign_id
ON campaign_recipients (campaign_id);
-- Índice compuesto para my_leads (assigned_to + status)
CREATE INDEX idx_leads_assigned_status
ON leads (assigned_to, status)
INCLUDE (last_activity_at, score);
-- Índice parcial para pipeline activo
CREATE INDEX idx_deals_pipeline_status_open
ON deals (pipeline_id, stage_id, updated_at DESC)
WHERE status = 'open';
Índices redundantes a eliminar (5)
❌
idx_leads_assigned
eliminar
Reemplazar por idx_leads_assigned_status (compuesto). El índice simple es subconjunto.
⚠
idx_deals_owner
revisar
owner_user_id tiene baja selectividad (350 usuarios, 45K deals). Evaluar si el query set lo justifica.
⚠
idx_leads_email_company_name_created_at
revisar
Covering index demasiado amplio; idx_leads_email_company_name sería suficiente.
ℹ
idx_activities_lead (existente)
mantener
Válido. Con idx_activities_deal_id añadido, cubren los dos casos de uso principales.
Impacto estimado post-optimización
| Query | Antes | Después | Mejora |
|---|---|---|---|
| pipeline_view | 920ms | ~75ms | -92% |
| revenue_forecast | 1,850ms | ~120ms | -94% |
| lead_search | 340ms | ~45ms | -87% |
| campaign_stats | 640ms | ~30ms | -95% |
Plan de migración zero-downtime (expand-contract)
Estrategia expand-contract para LeadSpark en producción
Zero-downtime
1
EXPAND — Añadir índices sin bloquear
Los índices CONCURRENTLY no bloquean DML. Ejecutar en ventana de bajo tráfico (< 100 req/s).
-- 20260615_001_add_missing_indexes.up.sql
-- Tiempo estimado: ~8min en 4.1M filas | Zero lock
CREATE INDEX CONCURRENTLY idx_deals_lead_id_join
ON deals (lead_id);
CREATE INDEX CONCURRENTLY idx_activities_deal_id
ON activities (deal_id);
CREATE INDEX CONCURRENTLY idx_activities_user_id_join
ON activities (user_id);
CREATE INDEX CONCURRENTLY idx_campaign_recipients_campaign_id
ON campaign_recipients (campaign_id);
CREATE INDEX CONCURRENTLY idx_deals_pipeline_status_open
ON deals (pipeline_id, stage_id, updated_at DESC)
WHERE status = 'open';
CREATE INDEX CONCURRENTLY idx_leads_assigned_status
ON leads (assigned_to, status)
INCLUDE (last_activity_at, score);
2
EXPAND — Añadir FK constraints (validación diferida)
Añadir NOT VALID primero, validar en background. No bloquea operaciones existentes.
-- 20260615_002_add_fk_constraints.up.sql
-- NOT VALID = añade constraint sin escanear filas existentes (instantáneo)
ALTER TABLE deals
ADD CONSTRAINT fk_deals_lead
FOREIGN KEY (lead_id) REFERENCES leads(id)
NOT VALID,
ADD CONSTRAINT fk_deals_pipeline
FOREIGN KEY (pipeline_id) REFERENCES pipelines(id)
NOT VALID,
ADD CONSTRAINT fk_deals_stage
FOREIGN KEY (stage_id) REFERENCES pipeline_stages(id)
NOT VALID;
ALTER TABLE activities
ADD CONSTRAINT fk_activities_deal
FOREIGN KEY (deal_id) REFERENCES deals(id)
NOT VALID,
ADD CONSTRAINT fk_activities_lead
FOREIGN KEY (lead_id) REFERENCES leads(id)
NOT VALID;
ALTER TABLE campaign_recipients
ADD CONSTRAINT fk_cr_campaign
FOREIGN KEY (campaign_id) REFERENCES email_campaigns(id)
NOT VALID,
ADD CONSTRAINT fk_cr_lead
FOREIGN KEY (lead_id) REFERENCES leads(id)
NOT VALID;
-- Validar en background (ShareLock, no AccessExclusiveLock)
ALTER TABLE deals VALIDATE CONSTRAINT fk_deals_lead;
ALTER TABLE deals VALIDATE CONSTRAINT fk_deals_pipeline;
3
TRANSITION — Corregir constraints y añadir NOT NULL
Solo para columnas con datos válidos. Ejecutar ANALYZE antes para actualizar estadísticas.
-- 20260622_003_fix_constraints.up.sql
ANALYZE leads, deals, activities;
-- Añadir CHECK constraints (instantáneo si datos son válidos)
ALTER TABLE leads
ADD CONSTRAINT chk_score CHECK (score BETWEEN 0 AND 100);
ALTER TABLE pipeline_stages
ADD CONSTRAINT chk_probability CHECK (probability BETWEEN 0 AND 100);
-- NOT NULL en deals.title (verificar primero que no hay NULLs)
-- SELECT COUNT(*) FROM deals WHERE title IS NULL; -- debe ser 0
ALTER TABLE deals ALTER COLUMN title SET NOT NULL;
-- NOT NULL en pipeline_stages.pipeline_id
ALTER TABLE pipeline_stages ALTER COLUMN pipeline_id SET NOT NULL;
4
CONTRACT — Eliminar índices redundantes + cleanup
Solo ejecutar cuando los nuevos índices estén validados y en producción ≥ 72h.
-- 20260629_004_cleanup.up.sql (semana posterior)
-- Eliminar índice simple reemplazado por compuesto
DROP INDEX CONCURRENTLY idx_leads_assigned;
-- Migrar preferences a JSONB (ORM + backfill batch)
ALTER TABLE users ADD COLUMN preferences_jsonb JSONB;
UPDATE users
SET preferences_jsonb = preferences::jsonb
WHERE id IN (SELECT id FROM users WHERE preferences_jsonb IS NULL LIMIT 5000);
-- Repetir hasta 0 filas. Luego:
-- ALTER TABLE users DROP COLUMN preferences;
-- ALTER TABLE users RENAME COLUMN preferences_jsonb TO preferences;
-- Rollback script (20260629_004_cleanup.down.sql)
CREATE INDEX CONCURRENTLY idx_leads_assigned ON leads (assigned_to);
ALTER TABLE users DROP COLUMN IF EXISTS preferences_jsonb;
Hoja de ruta priorizada
| Prioridad | Acción | Tablas afectadas | Esfuerzo | Impacto | Semana |
|---|---|---|---|---|---|
| P0 | Crear 6 índices faltantes de alta prioridad (CONCURRENTLY) | deals, activities, campaign_recipients, leads | 1h | -92% latencia pipeline | S1 |
| P0 | Añadir FK constraints (NOT VALID → VALIDATE) | deals, activities, campaign_recipients | 2h | Integridad referencial | S1 |
| P1 | Corregir CHECK constraints (score, probability) | leads, pipeline_stages | 30min | Calidad de datos | S2 |
| P1 | Migrar preferences VARCHAR → JSONB (backfill batch) | users | 4h | Searchable + validado | S2 |
| P1 | Normalizar tags (leads, companies, deals) → tabla N:M | leads, companies | 1 sprint | 1NF + búsqueda por tag | S3 |
| P2 | Crear tabla deal_contacts (N:M) y deprecar contact_names | deals | 1 sprint | Modelo de datos correcto | S5 |
| P2 | Normalizar status/source → tablas lookup FK | leads, deals | 2 sprints | Extensibilidad | S6 |