01Convenciones y prefijos

02data-erd-section

00bERD interactivo · click tabla ve columnas + RLS

20 tablas core del schema Marketplace SMC. Click en cualquier tabla revela columnas · RLS policy · relaciones FK. Zoom para detalle.

02Multi-tenancy · pieza central

Cada tenant = una "empresa operadora" dentro del Hub. Hoy hay 1 tenant (Smart Connection / Marketplace SMC SpA). Futuro: N marcas propias adicionales.

Beneficios concretos:

• 1 sola BD · 1 sola app desplegada
• Aislamiento garantizado via RLS Postgres (tenant A NUNCA ve data tenant B)
• Agregar tenant nuevo = 1 INSERT en mk_tenants · no es deploy nuevo
• Métricas consolidadas posibles (admin del Hub puede ver todos)

03Tablas Core

id UUID PK

slug TEXT UNIQUE              -- 'smc' · 'marca-1' · 'marca-x'
name TEXT                     -- razón social legal
rut TEXT                      -- RUT empresa
giro TEXT                     -- giro principal SII
domicilio JSONB               -- dirección estructurada
plan TEXT                     -- 'mvp' · 'standard' · 'enterprise'
settings JSONB                -- configuración tenant-específica
active BOOLEAN DEFAULT true
created_at TIMESTAMPTZ
updated_at TIMESTAMPTZ

id UUID PK

email TEXT UNIQUE
full_name TEXT
auth_id UUID                  -- ref Supabase Auth
role TEXT                     -- 'admin' · 'operator' · 'viewer' · 'accountant'
active BOOLEAN DEFAULT true
created_at TIMESTAMPTZ

user_id UUID FK → mk_users

tenant_id UUID FK → mk_tenants
role TEXT                     -- rol específico en este tenant
PRIMARY KEY (user_id, tenant_id)

id UUID PK

tenant_id UUID           -- NULL si es categoría global SMC
slug TEXT
name TEXT
parent_id UUID                -- self-ref para jerarquía
meta JSONB

04Tablas Inventory · el corazón

id UUID PK

tenant_id UUID NOT NULL
sku TEXT NOT NULL             -- código interno
ean_upc TEXT                  -- código barras
title TEXT NOT NULL
description TEXT
brand TEXT
category_id UUID FK
weight_grams INTEGER
dim_cm JSONB                  -- {length, width, height}
images JSONB                  -- array URLs
attributes JSONB              -- {color, size, ...}
parent_sku UUID               -- self-ref para variantes
landed_cost NUMERIC           -- costo real puesto en bodega
status TEXT                   -- 'active' · 'discontinued' · 'draft'
UNIQUE (tenant_id, sku)

id UUID PK

tenant_id UUID
name TEXT                     -- 'Casa Matriz' · 'Bodega Sur'
address JSONB
active BOOLEAN

id UUID PK

tenant_id UUID
product_id UUID FK
warehouse_id UUID FK
location TEXT                 -- 'rack-A-3-bin-12'
physical_qty INTEGER          -- stock real contado
reserved_qty INTEGER          -- asignado a órdenes pendientes
available_qty INTEGER         -- generated: physical - reserved
updated_at TIMESTAMPTZ
UNIQUE (tenant_id, product_id, warehouse_id)

id UUID PK

tenant_id UUID
product_id UUID FK
warehouse_id UUID FK
direction TEXT                -- 'IN' · 'OUT' · 'ADJUST'
quantity INTEGER              -- positivo siempre · direction indica signo
reason TEXT                   -- 'purchase' · 'sale' · 'return' · 'damage' · 'count'
reference_type TEXT           -- 'order' · 'po' · 'manual'
reference_id UUID             -- FK polimórfico
notes TEXT
created_at TIMESTAMPTZ

05Tablas Channels & Adapters

id UUID PK

tenant_id UUID
type TEXT                     -- 'meli' · 'mp' · 'd2c' · 'b2b' · 'falabella' · 'cencosud'
name TEXT                     -- alias amigable
credentials JSONB             -- OAuth tokens · API keys (cifrado at rest)
config JSONB                  -- pricing rules · default markup · etc
active BOOLEAN
last_sync_at TIMESTAMPTZ

id UUID PK

tenant_id UUID
product_id UUID FK
channel_id UUID FK
external_id TEXT              -- ID del producto en el canal (MLC123 · MP456)
status TEXT                   -- 'active' · 'paused' · 'error' · 'pending'
price_clp NUMERIC             -- precio aplicado en este canal
last_synced_at TIMESTAMPTZ
sync_error TEXT               -- último error si status='error'
external_data JSONB           -- payload completo del canal
UNIQUE (tenant_id, product_id, channel_id)

id UUID PK

tenant_id UUID
channel_type TEXT             -- 'meli' · 'mp' etc.
formula JSONB                 -- {markup_pct, commission_pct, iva_pct, ...}
applies_to JSONB              -- {category_ids: [], product_ids: []}
priority INTEGER              -- regla más específica gana
active BOOLEAN

06Tablas Orders & Fulfillment

id UUID PK

tenant_id UUID
channel_id UUID FK          -- de qué canal vino
external_order_id TEXT        -- ID en canal origen (orden MeLi · folio MP)
customer_id UUID FK
status TEXT                   -- 'pending' · 'paid' · 'fulfilled' · 'delivered' · 'cancelled'
total_clp NUMERIC
paid_clp NUMERIC
currency TEXT DEFAULT 'CLP'
placed_at TIMESTAMPTZ
shipped_at TIMESTAMPTZ
delivered_at TIMESTAMPTZ
meta JSONB                    -- payload original del canal
INDEX (tenant_id, channel_id, status, placed_at)

id UUID PK

tenant_id UUID
order_id UUID FK
product_id UUID FK
quantity INTEGER
unit_price_clp NUMERIC
subtotal_clp NUMERIC

id UUID PK

tenant_id UUID
type TEXT                     -- 'person' · 'company' · 'public-org'
name TEXT
rut TEXT
email TEXT
phone TEXT
address JSONB
is_meli_anonymous BOOLEAN
external_ids JSONB            -- {meli: 'XXX', mp: 'YYY'}
created_at TIMESTAMPTZ

id UUID PK

tenant_id UUID
order_id UUID FK
courier TEXT                  -- 'chilexpress' · 'starken' · 'bluexpress'
tracking_number TEXT
label_url TEXT
status TEXT                   -- 'pending' · 'in-transit' · 'delivered' · 'returned'
cost_clp NUMERIC
shipped_at TIMESTAMPTZ
delivered_at TIMESTAMPTZ

07Tablas Sourcing & Suppliers

id UUID PK

tenant_id UUID
name TEXT
rut TEXT
contact JSONB                 -- {email, phone, whatsapp}
country TEXT                  -- 'CL' · 'CN' · etc
lead_time_days INTEGER
default_terms TEXT            -- 'NET-30' · '50/50'
rating NUMERIC                -- 1-5
active BOOLEAN

id UUID PK

tenant_id UUID
supplier_id UUID FK
status TEXT                   -- 'draft' · 'sent' · 'confirmed' · 'shipped' · 'received'
po_number TEXT                -- correlativo legible
total_clp NUMERIC
terms TEXT
eta_date DATE
notes TEXT
created_at TIMESTAMPTZ
received_at TIMESTAMPTZ

id UUID PK

tenant_id UUID
po_id UUID FK
product_id UUID FK
quantity INTEGER
unit_cost_clp NUMERIC
landed_cost_clp NUMERIC       -- post recepción · incluye costos asociados

08Tablas Finance & Accounting

id UUID PK

tenant_id UUID
code TEXT                     -- '1.1.01.001'
name TEXT                     -- 'Bancos · Cuenta Corriente Bci'
type TEXT                     -- 'asset' · 'liability' · 'equity' · 'revenue' · 'expense'
parent_id UUID                -- jerarquía
is_iva_relevant BOOLEAN
balance_clp NUMERIC           -- saldo calculado (vista materializada)

id UUID PK

tenant_id UUID
entry_date DATE
description TEXT
source_type TEXT              -- 'order' · 'po' · 'payment' · 'manual'
source_id UUID
total_debit_clp NUMERIC       -- debe sumar igual al haber
total_credit_clp NUMERIC
status TEXT                   -- 'draft' · 'posted' · 'reversed'
created_at TIMESTAMPTZ

id UUID PK

tenant_id UUID
entry_id UUID FK
account_id UUID FK
debit_clp NUMERIC DEFAULT 0
credit_clp NUMERIC DEFAULT 0
description TEXT
CHECK (debit_clp = 0 OR credit_clp = 0)  -- una sola dirección por línea

id UUID PK

tenant_id UUID
type INTEGER                  -- 33 · 34 · 39 · 56 · 61
folio TEXT                    -- correlativo SII
status TEXT                   -- 'issued' · 'rejected' · 'cancelled'
issued_at TIMESTAMPTZ
receiver_rut TEXT
total_clp NUMERIC
iva_clp NUMERIC
xml TEXT                      -- XML firmado SII
pdf_url TEXT
related_order_id UUID         -- nullable · si nace de una orden
external_provider_id TEXT     -- ID en Open Factura o SII

id UUID PK

tenant_id UUID
direction TEXT                -- 'inbound' (cobro) · 'outbound' (pago)
method TEXT                   -- 'webpay' · 'mercadopago' · 'transfer' · 'cash'
amount_clp NUMERIC
related_document_id UUID      -- FK al DTE relacionado
related_po_id UUID            -- FK al PO si es outbound
reference TEXT                -- nro transferencia · authcode
received_at TIMESTAMPTZ

09Tablas Eventos & Audit

id UUID PK

tenant_id UUID
event_type TEXT               -- 'order.created' · 'inventory.received' · etc
aggregate_type TEXT           -- 'order' · 'po' · 'inventory'
aggregate_id UUID             -- ID del objeto principal
actor_id UUID                 -- user o 'system'
payload JSONB                 -- datos del evento
created_at TIMESTAMPTZ
INDEX (tenant_id, event_type, created_at DESC)

id UUID PK

tenant_id UUID
table_name TEXT
row_id UUID
action TEXT                   -- 'INSERT' · 'UPDATE' · 'DELETE'
changed_by UUID
diff JSONB                    -- {field: {before, after}}
created_at TIMESTAMPTZ

id UUID PK

tenant_id UUID
endpoint TEXT
method TEXT
status_code INTEGER
duration_ms INTEGER
source TEXT                   -- 'meli' · 'mp' · 'groq' · 'open-factura' · 'internal'
error_message TEXT
request_params JSONB
created_at TIMESTAMPTZ

10Diagrama ERD simplificado

ERD simplificado · líneas continuas = FK directa · líneas punteadas = relación via tenant_id o evento

11Smart Cache v5 · mkt_opportunity_cache

Tabla específica del sistema Opportunities (ADR-0001). NO es data lake · es cache ligero con TTL diferenciado.

Schema mkt_opportunity_cache

CREATE TABLE mkt_opportunity_cache (

sku_canonico TEXT PRIMARY KEY,
score NUMERIC,
category TEXT,
n_stores INTEGER,
price_min NUMERIC,
price_max NUMERIC,
top_url TEXT,
last_synced TIMESTAMPTZ,
n_queries INTEGER DEFAULT 1
);
CREATE TABLE mkt_category_premium (
category TEXT PRIMARY KEY,
multiplier NUMERIC,  -- ej: accesorios celular 1.3x · línea blanca 0.9x
active BOOLEAN DEFAULT true
);

12Tablas Supplier (dropshipping · ADR-0003)

Soporte para modo dropshipping Y1 · cada proveedor tiene su adapter (Supplier Adapter Pattern · ADR-0003 · ver Doc 13 §08).

Schema

CREATE TABLE mkt_suppliers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  supplier_code TEXT NOT NULL,            -- ej: 'proveedor-x'
  display_name TEXT NOT NULL,
  rut TEXT,                                -- RUT chileno
  auth_mode TEXT NOT NULL CHECK (auth_mode IN ('api_key','oauth','email_automated','whatsapp_business')),
  credentials JSONB,                       -- cifrado en repo de Supabase Vault
  contact_email TEXT,
  contact_phone TEXT,
  payment_terms TEXT,                      -- ej: '7d post-venta · transferencia CLP'
  status TEXT DEFAULT 'active' CHECK (status IN ('active','paused','suspended')),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (tenant_id, supplier_code)
);

ALTER TABLE mkt_suppliers ENABLE ROW LEVEL SECURITY;
CREATE POLICY p_tenant_isolation ON mkt_suppliers
  USING (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);

CREATE TABLE mkt_supplier_products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  supplier_id UUID NOT NULL REFERENCES mkt_suppliers(id) ON DELETE CASCADE,
  product_id UUID REFERENCES products(id),  -- NULL si solo catálogo proveedor · no publicado aún
  supplier_sku TEXT NOT NULL,
  supplier_name TEXT,
  cost_clp INTEGER NOT NULL,
  available_qty INTEGER DEFAULT 0,
  last_synced_at TIMESTAMPTZ DEFAULT NOW(),
  margin_pct NUMERIC,
  is_active BOOLEAN DEFAULT true,
  UNIQUE (supplier_id, supplier_sku)
);

ALTER TABLE mkt_supplier_products ENABLE ROW LEVEL SECURITY;
CREATE POLICY p_tenant_isolation ON mkt_supplier_products
  USING (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);

CREATE TABLE mkt_purchase_orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  supplier_id UUID NOT NULL REFERENCES mkt_suppliers(id),
  order_id UUID REFERENCES mkt_orders(id),  -- venta que disparó esta OC (dropship)
  status TEXT DEFAULT 'created' CHECK (status IN ('created','confirmed','shipped','delivered','cancelled')),
  items JSONB NOT NULL,                     -- [{supplier_sku, qty, unit_cost}]
  ship_to JSONB NOT NULL,                   -- dirección destino final
  external_po_id TEXT,                       -- ID en sistema del proveedor
  tracking_number TEXT,
  total_clp INTEGER NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  shipped_at TIMESTAMPTZ,
  delivered_at TIMESTAMPTZ,
  cancelled_at TIMESTAMPTZ,
  cancel_reason TEXT
);

ALTER TABLE mkt_purchase_orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY p_tenant_isolation ON mkt_purchase_orders
  USING (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);

CREATE INDEX idx_supp_products_supplier ON mkt_supplier_products(supplier_id);
CREATE INDEX idx_supp_products_active ON mkt_supplier_products(is_active) WHERE is_active = true;
CREATE INDEX idx_po_order ON mkt_purchase_orders(order_id);
CREATE INDEX idx_po_status ON mkt_purchase_orders(status, created_at DESC);

Trigger migración dropship → stock local

-- Materialized view que detecta SKUs >$5K/mes 2 meses seguidos
CREATE MATERIALIZED VIEW mkt_dropship_migration_candidates AS
SELECT
  sp.product_id,
  sp.supplier_id,
  SUM(oi.qty * oi.unit_price) FILTER (WHERE o.created_at > NOW() - INTERVAL '60 days') AS revenue_60d,
  COUNT(DISTINCT o.id) FILTER (WHERE o.created_at > NOW() - INTERVAL '60 days') AS orders_60d
FROM mkt_supplier_products sp
JOIN mkt_order_items oi ON oi.product_id = sp.product_id
JOIN mkt_orders o ON o.id = oi.order_id
GROUP BY sp.product_id, sp.supplier_id
HAVING SUM(oi.qty * oi.unit_price) FILTER (WHERE o.created_at > NOW() - INTERVAL '60 days') > 10000000;
-- $10M CLP / 2 meses = $5M/mes promedio

REFRESH MATERIALIZED VIEW CONCURRENTLY mkt_dropship_migration_candidates;
-- Cron 6am diario

13Tablas ADS (publicidad multicanal · Doc 02 BBP P14)

Soporte para campañas publicitarias cross-canal (Mercado Ads · Meta · Google · TikTok). Fase 2 Y1 · escala D2C Y2.

Schema

CREATE TABLE mkt_ads_accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  provider TEXT NOT NULL CHECK (provider IN ('mercado_ads','meta','google_ads','tiktok')),
  external_account_id TEXT NOT NULL,         -- ID cuenta en el provider
  display_name TEXT,
  credentials JSONB,                          -- OAuth tokens cifrados
  is_active BOOLEAN DEFAULT true,
  monthly_budget_clp INTEGER,                 -- presupuesto mensual
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (tenant_id, provider, external_account_id)
);

ALTER TABLE mkt_ads_accounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY p_tenant_isolation ON mkt_ads_accounts
  USING (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);

CREATE TABLE mkt_ads_campaigns (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  account_id UUID NOT NULL REFERENCES mkt_ads_accounts(id) ON DELETE CASCADE,
  external_campaign_id TEXT NOT NULL,
  name TEXT NOT NULL,
  product_ids UUID[] DEFAULT '{}',           -- SKUs promocionados
  status TEXT DEFAULT 'active' CHECK (status IN ('draft','active','paused','ended')),
  budget_clp INTEGER NOT NULL,
  start_date DATE,
  end_date DATE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (account_id, external_campaign_id)
);

ALTER TABLE mkt_ads_campaigns ENABLE ROW LEVEL SECURITY;
CREATE POLICY p_tenant_isolation ON mkt_ads_campaigns
  USING (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);

CREATE TABLE mkt_ads_metrics (
  id BIGSERIAL PRIMARY KEY,
  tenant_id UUID NOT NULL,
  campaign_id UUID NOT NULL REFERENCES mkt_ads_campaigns(id) ON DELETE CASCADE,
  date DATE NOT NULL,
  impressions INTEGER DEFAULT 0,
  clicks INTEGER DEFAULT 0,
  conversions INTEGER DEFAULT 0,
  spend_clp INTEGER DEFAULT 0,
  revenue_clp INTEGER DEFAULT 0,             -- atribución revenue
  roas NUMERIC GENERATED ALWAYS AS (
    CASE WHEN spend_clp > 0 THEN revenue_clp::numeric / spend_clp ELSE 0 END
  ) STORED,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (campaign_id, date)
);

ALTER TABLE mkt_ads_metrics ENABLE ROW LEVEL SECURITY;
CREATE POLICY p_tenant_isolation ON mkt_ads_metrics
  USING (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);

CREATE INDEX idx_ads_metrics_campaign_date ON mkt_ads_metrics(campaign_id, date DESC);
CREATE INDEX idx_ads_metrics_roas ON mkt_ads_metrics(roas) WHERE roas < 1.5;
-- Indexa campañas con ROAS bajo para alertas

Pixels conversion API (Y2 escalado D2C)

CREATE TABLE mkt_ads_events (
  id BIGSERIAL PRIMARY KEY,
  tenant_id UUID NOT NULL,
  provider TEXT NOT NULL CHECK (provider IN ('meta_capi','google_enhanced','tiktok_events')),
  event_type TEXT NOT NULL,                  -- 'purchase' · 'add_to_cart' · 'view_content'
  event_id TEXT NOT NULL,                    -- idempotency
  event_time TIMESTAMPTZ DEFAULT NOW(),
  user_data JSONB,                            -- hashed PII per provider spec
  custom_data JSONB,                          -- {value, currency, content_ids}
  sent_to_provider BOOLEAN DEFAULT false,
  provider_response JSONB,
  UNIQUE (provider, event_id)
);

ALTER TABLE mkt_ads_events ENABLE ROW LEVEL SECURITY;
CREATE POLICY p_tenant_isolation ON mkt_ads_events
  USING (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);

CREATE INDEX idx_ads_events_unsent ON mkt_ads_events(provider) WHERE sent_to_provider = false;
-- Cron 5min envía batch pendientes a cada provider

Triggers alertas ROAS

-- Trigger alert si ROAS < 1.5 por 7 días seguidos en campaña activa
CREATE OR REPLACE FUNCTION fn_alert_low_roas() RETURNS TRIGGER AS $$
BEGIN
  IF (
    SELECT AVG(roas) FROM mkt_ads_metrics
    WHERE campaign_id = NEW.campaign_id
      AND date > NOW() - INTERVAL '7 days'
  ) < 1.5 THEN
    PERFORM pg_notify('ads_roas_low', NEW.campaign_id::text);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_alert_low_roas
  AFTER INSERT ON mkt_ads_metrics
  FOR EACH ROW EXECUTE FUNCTION fn_alert_low_roas();