[CONSEJO — Doc 08] Contrarian: El diseño de datos es la decisión más difícil de revertir. Una columna mal tipada con 50k filas es una migración de 2 días en producción. Vale la pena tomarse 4 horas extra diseñando el schema antes de escribir una sola línea de código de aplicación. First Principles: El motor estadístico consume series temporales. El modelo de datos debe ser optimizado primero para servir esas series temporales eficientemente, y segundo para el CRUD de la aplicación. Si el motor necesita un full-scan de la tabla de movimientos para correr, el diseño está al revés. Expansionist: El schema debe soportar multi-país desde el día 1 (campos de moneda, locale, régimen fiscal). Agregar una columna country_code después de 100k filas de movimientos de inventario es trivial; cambiar el tipo de datos del campo de precio de integer a numeric lo es mucho menos. Outsider: Los sistemas de inventario tienen un patrón de acceso sesgado: 90% de los queries son sobre los últimos 90 días de movimientos para el motor. Considerar particionamiento por rango de fecha en sm_stock_movements cuando la tabla supere 10M filas. Executor: Nombrar todas las tablas con prefijo sm_ (StockMind) para convivir limpiamente con schemas futuros de otros módulos Cheryx (sl_ SalesMind, bk_ BookMind, pm_ PayMind) en la misma base de datos PostgreSQL. Security Auditor: Los campos de precio y costo del proveedor son datos comerciales sensibles que los clientes no quieren que nadie vea. Cifrar en columna con pgcrypto los campos unit_cost, supplier_price, contract_price para proteger en caso de acceso no autorizado a la base de datos. Chairman: Un modelo de datos limpio y documentado es el activo técnico más valioso del early stage. Es lo que permite a un segundo desarrollador incorporarse sin quebrar el sistema en semana 1.

1. Convenciones del schema

2. Schema completo

2.1 sm_tenants — Empresas cliente

CREATE TABLE sm_tenants (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name            VARCHAR(255) NOT NULL,
    slug            VARCHAR(100) UNIQUE NOT NULL,  -- usado en subdominios futuros
    country_code    CHAR(2) NOT NULL DEFAULT 'CR', -- ISO 3166-1 alpha-2
    locale          VARCHAR(10) NOT NULL DEFAULT 'es-CR',
    currency_code   CHAR(3) NOT NULL DEFAULT 'CRC', -- ISO 4217
    timezone        VARCHAR(50) NOT NULL DEFAULT 'America/Costa_Rica',
    plan            TEXT NOT NULL DEFAULT 'trial'
                    CHECK (plan IN ('trial', 'starter', 'growth', 'pro', 'enterprise')),
    plan_status     TEXT NOT NULL DEFAULT 'active'
                    CHECK (plan_status IN ('active', 'trial', 'past_due', 'cancelled')),
    trial_ends_at   TIMESTAMPTZ,
    plan_started_at TIMESTAMPTZ,
    settings        JSONB NOT NULL DEFAULT '{}',   -- configuraciones del motor por tenant
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at      TIMESTAMPTZ
);

CREATE INDEX idx_tenants_slug ON sm_tenants(slug) WHERE deleted_at IS NULL;
CREATE INDEX idx_tenants_plan_status ON sm_tenants(plan_status) WHERE deleted_at IS NULL;

settings JSONB estructura:

{
  "service_level": 0.95,
  "default_lead_time_days": 14,
  "currency_display": "CRC",
  "fiscal_year_start_month": 1,
  "motor": {
    "min_history_days": 90,
    "forecast_horizon_days": 30,
    "holding_cost_rate": 0.25
  }
}

2.2 sm_users — Usuarios del sistema

CREATE TABLE sm_users (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id       UUID NOT NULL REFERENCES sm_tenants(id),
    email           VARCHAR(320) NOT NULL,
    name            VARCHAR(255),
    role            TEXT NOT NULL DEFAULT 'manager'
                    CHECK (role IN ('owner', 'manager', 'viewer', 'cheryx_admin')),
    last_login_at   TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at      TIMESTAMPTZ,
    UNIQUE (tenant_id, email)  -- email único por tenant, no global
);

CREATE INDEX idx_users_tenant ON sm_users(tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_email ON sm_users(email) WHERE deleted_at IS NULL;

2.3 sm_magic_link_tokens — Tokens de autenticación

CREATE TABLE sm_magic_link_tokens (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id     UUID NOT NULL REFERENCES sm_users(id),
    token_hash  VARCHAR(64) NOT NULL UNIQUE,  -- SHA-256 del token real
    used_at     TIMESTAMPTZ,
    expires_at  TIMESTAMPTZ NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Cleanup automático de tokens expirados (purgar con pg_cron diariamente)
CREATE INDEX idx_magic_tokens_expires ON sm_magic_link_tokens(expires_at);

El token real (enviado por email) nunca se almacena — solo su hash SHA-256.

2.4 sm_suppliers — Proveedores

CREATE TABLE sm_suppliers (
    id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id           UUID NOT NULL REFERENCES sm_tenants(id),
    name                VARCHAR(255) NOT NULL,
    code                VARCHAR(50),
    contact_name        VARCHAR(255),
    contact_email       VARCHAR(320),
    contact_phone       VARCHAR(50),
    country_code        CHAR(2),
    currency_code       CHAR(3) NOT NULL DEFAULT 'CRC',
    default_lead_time   INTEGER,  -- días
    payment_terms_days  INTEGER,
    notes               TEXT,
    external_id         VARCHAR(255),  -- ID en Alegra u otro sistema externo
    metadata            JSONB NOT NULL DEFAULT '{}',
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at          TIMESTAMPTZ,
    UNIQUE (tenant_id, code)
);

CREATE INDEX idx_suppliers_tenant ON sm_suppliers(tenant_id) WHERE deleted_at IS NULL;

2.5 sm_skus — SKUs (unidades de stock)

CREATE TABLE sm_skus (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id       UUID NOT NULL REFERENCES sm_tenants(id),
    code            VARCHAR(100) NOT NULL,
    name            VARCHAR(500) NOT NULL,
    description     TEXT,
    unit            VARCHAR(50) NOT NULL DEFAULT 'unit',  -- unit, kg, lt, mt, etc.

    -- Clasificación (calculada por el motor, actualizada en cada run)
    abc_class       CHAR(1) CHECK (abc_class IN ('A', 'B', 'C')),
    xyz_class       CHAR(1) CHECK (xyz_class IN ('X', 'Y', 'Z')),
    demand_pattern  TEXT CHECK (demand_pattern IN (
                        'smooth', 'erratic', 'intermittent', 'lumpy', 'unknown'
                    )),

    -- Estado de stock (calculado, actualizado con cada movimiento)
    stock_current   NUMERIC(15, 4) NOT NULL DEFAULT 0,
    stock_status    TEXT NOT NULL DEFAULT 'nodata'
                    CHECK (stock_status IN ('critical', 'low', 'ok', 'overstock', 'nodata')),

    -- Parámetros de política (calculados por el motor)
    reorder_point   NUMERIC(15, 4),
    safety_stock    NUMERIC(15, 4),
    eoq             NUMERIC(15, 4),
    max_stock       NUMERIC(15, 4),

    -- Parámetros de aprovisionamiento (ingresados / sincronizados de Alegra)
    lead_time_days  INTEGER,
    moq             NUMERIC(15, 4),  -- minimum order quantity
    supplier_id     UUID REFERENCES sm_suppliers(id),

    -- Costos (cifrados si plan Pro+)
    unit_cost       NUMERIC(15, 4),  -- costo unitario actual

    -- Integración
    external_id     VARCHAR(255),   -- ID en Alegra u otro sistema
    external_source TEXT,           -- 'alegra', 'manual', 'csv'

    -- Motor
    last_classified_at  TIMESTAMPTZ,
    last_forecasted_at  TIMESTAMPTZ,
    motor_params        JSONB NOT NULL DEFAULT '{}',  -- parámetros específicos del motor para este SKU

    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at      TIMESTAMPTZ,
    UNIQUE (tenant_id, code)
);

CREATE INDEX idx_skus_tenant ON sm_skus(tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_skus_status ON sm_skus(tenant_id, stock_status) WHERE deleted_at IS NULL;
CREATE INDEX idx_skus_abc ON sm_skus(tenant_id, abc_class) WHERE deleted_at IS NULL;
CREATE INDEX idx_skus_supplier ON sm_skus(supplier_id) WHERE deleted_at IS NULL;
-- Índice para búsqueda de texto (fuzzy search en nombre + código)
CREATE INDEX idx_skus_search ON sm_skus USING GIN (
    to_tsvector('spanish', coalesce(code, '') || ' ' || coalesce(name, ''))
) WHERE deleted_at IS NULL;

motor_params JSONB estructura:

{
  "service_level_override": null,
  "lead_time_override": null,
  "exclude_from_forecast": false,
  "force_method": null,
  "adi": 2.4,
  "cv_squared": 0.38
}

2.6 sm_stock_movements — Movimientos de inventario

CREATE TABLE sm_stock_movements (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id       UUID NOT NULL REFERENCES sm_tenants(id),
    sku_id          UUID NOT NULL REFERENCES sm_skus(id),

    movement_type   TEXT NOT NULL CHECK (movement_type IN (
                        'sale', 'purchase', 'adjustment_in', 'adjustment_out',
                        'return_customer', 'return_supplier', 'transfer_in', 'transfer_out',
                        'opening_stock', 'shrinkage'
                    )),
    quantity        NUMERIC(15, 4) NOT NULL,  -- siempre positivo, el tipo indica dirección
    unit_cost       NUMERIC(15, 4),           -- costo unitario en el momento del movimiento

    reference       VARCHAR(255),  -- número de factura, OC, ajuste
    notes           TEXT,
    movement_date   DATE NOT NULL, -- fecha del movimiento (puede ser diferente a created_at)

    -- Trazabilidad
    order_id        UUID REFERENCES sm_purchase_orders(id),
    user_id         UUID REFERENCES sm_users(id),
    external_id     VARCHAR(255),
    external_source TEXT,

    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
    -- No updated_at: los movimientos son inmutables (si hay error, crear movimiento inverso)
    -- No deleted_at: los movimientos nunca se eliminan (solo se compensan)
);

-- Índice crítico para el motor estadístico (query: historial 90d por SKU)
CREATE INDEX idx_movements_sku_date ON sm_stock_movements(sku_id, movement_date DESC);
CREATE INDEX idx_movements_tenant_date ON sm_stock_movements(tenant_id, movement_date DESC);
CREATE INDEX idx_movements_type ON sm_stock_movements(tenant_id, movement_type, movement_date DESC);

-- Particionamiento futuro (cuando la tabla supere 10M filas):
-- PARTITION BY RANGE (movement_date)

2.7 sm_purchase_orders — Órdenes de compra

CREATE TABLE sm_purchase_orders (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id       UUID NOT NULL REFERENCES sm_tenants(id),
    supplier_id     UUID REFERENCES sm_suppliers(id),

    status          TEXT NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending', 'approved', 'sent', 'received', 'rejected', 'cancelled')),
    source          TEXT NOT NULL DEFAULT 'motor'
                    CHECK (source IN ('motor', 'manual')),

    -- Fechas
    suggested_at    TIMESTAMPTZ,  -- cuándo el motor sugirió esta OC
    approved_at     TIMESTAMPTZ,
    sent_at         TIMESTAMPTZ,
    expected_date   DATE,         -- fecha estimada de recepción
    received_at     TIMESTAMPTZ,
    rejected_at     TIMESTAMPTZ,

    -- Montos
    total_estimated NUMERIC(15, 4),
    total_actual    NUMERIC(15, 4),  -- real al recibir (puede diferir del estimado)
    currency_code   CHAR(3) NOT NULL DEFAULT 'CRC',

    -- Trazabilidad
    approved_by     UUID REFERENCES sm_users(id),
    rejected_by     UUID REFERENCES sm_users(id),
    rejection_reason TEXT,
    notes           TEXT,

    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_tenant_status ON sm_purchase_orders(tenant_id, status);
CREATE INDEX idx_orders_supplier ON sm_purchase_orders(supplier_id);
CREATE INDEX idx_orders_suggested ON sm_purchase_orders(tenant_id, suggested_at DESC);

2.8 sm_purchase_order_items — Líneas de OC

CREATE TABLE sm_purchase_order_items (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    order_id        UUID NOT NULL REFERENCES sm_purchase_orders(id),
    sku_id          UUID NOT NULL REFERENCES sm_skus(id),
    tenant_id       UUID NOT NULL REFERENCES sm_tenants(id),  -- desnormalizado para RLS

    quantity_suggested  NUMERIC(15, 4) NOT NULL,
    quantity_approved   NUMERIC(15, 4),  -- puede diferir si el usuario modifica
    quantity_received   NUMERIC(15, 4),  -- real al recibir

    unit_cost_estimated NUMERIC(15, 4),
    unit_cost_actual    NUMERIC(15, 4),

    -- Por qué el motor sugirió esta línea
    motor_reason    JSONB NOT NULL DEFAULT '{}',

    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_order_items_order ON sm_purchase_order_items(order_id);
CREATE INDEX idx_order_items_sku ON sm_purchase_order_items(sku_id);

motor_reason JSONB estructura:

{
  "stock_current": 120,
  "reorder_point": 200,
  "safety_stock": 45,
  "eoq": 350,
  "lead_time_days": 14,
  "days_until_reorder": 8,
  "forecast_30d": 180,
  "method": "CrostonTSB"
}

2.9 sm_forecast_results — Resultados del motor

CREATE TABLE sm_forecast_results (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id       UUID NOT NULL REFERENCES sm_tenants(id),
    sku_id          UUID NOT NULL REFERENCES sm_skus(id),
    run_id          UUID NOT NULL,  -- todas las filas de una misma ejecución del motor comparten run_id

    -- Método seleccionado
    method          VARCHAR(50) NOT NULL,   -- 'CrostonTSB', 'SES', 'ADIDA', etc.
    method_rank     INTEGER,                -- ranking de métodos en el backtest
    backtest_error  NUMERIC(15, 6),         -- MASE o similar del método ganador

    -- Series de forecast (JSONB para flexibilidad de horizonte)
    forecast_series JSONB NOT NULL,  -- [{date, value, ci_lower, ci_upper}]

    -- Parámetros de política
    reorder_point   NUMERIC(15, 4),
    safety_stock    NUMERIC(15, 4),
    eoq             NUMERIC(15, 4),

    -- Clasificación en este run
    abc_class       CHAR(1),
    xyz_class       CHAR(1),
    demand_pattern  TEXT,
    adi             NUMERIC(8, 4),
    cv_squared      NUMERIC(8, 4),

    -- Drift detection
    drift_ratio     NUMERIC(8, 4),
    drift_status    TEXT CHECK (drift_status IN ('ok', 'alert', 'critical')),

    -- Explicación (generada por Jinja2 en el worker)
    explanation_html TEXT,

    run_at          TIMESTAMPTZ NOT NULL DEFAULT now(),

    -- Solo se guarda el resultado más reciente por SKU (o se archivan los anteriores)
    UNIQUE (tenant_id, sku_id, run_id)
);

-- Índice crítico: frontend consulta el resultado más reciente por SKU
CREATE INDEX idx_forecast_sku_recent ON sm_forecast_results(sku_id, run_at DESC);
CREATE INDEX idx_forecast_run ON sm_forecast_results(run_id);
CREATE INDEX idx_forecast_tenant_run ON sm_forecast_results(tenant_id, run_at DESC);

2.10 sm_forecast_runs — Registro de ejecuciones del motor

CREATE TABLE sm_forecast_runs (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id       UUID NOT NULL REFERENCES sm_tenants(id),

    status          TEXT NOT NULL DEFAULT 'queued'
                    CHECK (status IN ('queued', 'running', 'completed', 'failed', 'partial')),
    trigger         TEXT NOT NULL CHECK (trigger IN ('scheduled', 'manual', 'import')),

    skus_total      INTEGER,
    skus_processed  INTEGER DEFAULT 0,
    skus_failed     INTEGER DEFAULT 0,

    started_at      TIMESTAMPTZ,
    completed_at    TIMESTAMPTZ,
    duration_seconds NUMERIC(10, 2),

    error_log       JSONB,         -- errores por SKU si hubo fallos parciales
    summary_stats   JSONB,         -- estadísticas del run (métodos más usados, etc.)

    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_runs_tenant ON sm_forecast_runs(tenant_id, created_at DESC);
CREATE INDEX idx_runs_status ON sm_forecast_runs(status);

2.11 sm_integrations — Credenciales de integración (cifradas)

CREATE TABLE sm_integrations (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id       UUID NOT NULL REFERENCES sm_tenants(id),

    provider        TEXT NOT NULL CHECK (provider IN ('alegra', 'siigo', 'bsale', 'shopify', 'mercado_libre')),
    status          TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'error', 'disconnected')),

    -- Las credenciales se almacenan cifradas con pgcrypto (AES-256)
    credentials_enc BYTEA NOT NULL,  -- pgp_sym_encrypt(json_credentials, key)

    last_sync_at    TIMESTAMPTZ,
    last_error      TEXT,
    sync_config     JSONB NOT NULL DEFAULT '{}',  -- frecuencia, qué datos sincronizar

    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (tenant_id, provider)
);

CREATE INDEX idx_integrations_tenant ON sm_integrations(tenant_id);

2.12 sm_subscriptions — Suscripciones y facturación

CREATE TABLE sm_subscriptions (
    id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id           UUID NOT NULL UNIQUE REFERENCES sm_tenants(id),

    plan                TEXT NOT NULL CHECK (plan IN ('starter', 'growth', 'pro', 'enterprise')),
    billing_cycle       TEXT NOT NULL DEFAULT 'monthly' CHECK (billing_cycle IN ('monthly', 'annual')),

    -- ONVO Pay
    onvo_customer_id    VARCHAR(255) UNIQUE,
    onvo_subscription_id VARCHAR(255) UNIQUE,

    amount              NUMERIC(15, 4) NOT NULL,
    currency_code       CHAR(3) NOT NULL DEFAULT 'USD',

    current_period_start TIMESTAMPTZ NOT NULL,
    current_period_end   TIMESTAMPTZ NOT NULL,
    cancel_at_end        BOOLEAN NOT NULL DEFAULT FALSE,

    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

2.13 sm_invoices — Historial de facturas

CREATE TABLE sm_invoices (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id       UUID NOT NULL REFERENCES sm_tenants(id),
    subscription_id UUID REFERENCES sm_subscriptions(id),

    onvo_invoice_id VARCHAR(255) UNIQUE,

    status          TEXT NOT NULL CHECK (status IN ('paid', 'pending', 'failed', 'refunded')),
    amount          NUMERIC(15, 4) NOT NULL,
    currency_code   CHAR(3) NOT NULL,

    period_start    TIMESTAMPTZ,
    period_end      TIMESTAMPTZ,
    paid_at         TIMESTAMPTZ,

    invoice_url     TEXT,  -- URL al PDF de factura generado por ONVO Pay

    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_invoices_tenant ON sm_invoices(tenant_id, created_at DESC);

3. Diagrama de relaciones (simplificado)

sm_tenants ──┬── sm_users (N)
             ├── sm_skus (N) ──┬── sm_stock_movements (N)
             │                 ├── sm_purchase_order_items (N)
             │                 └── sm_forecast_results (N)
             ├── sm_suppliers (N) ── sm_skus (FK)
             ├── sm_purchase_orders (N) ── sm_purchase_order_items (N)
             ├── sm_forecast_runs (N)
             ├── sm_integrations (N)
             └── sm_subscriptions (1) ── sm_invoices (N)

4. Queries críticas del motor estadístico

4.1 Historial de ventas (90 días) por SKU — query más frecuente

-- Esta query se ejecuta para cada SKU en cada run del motor
-- El índice idx_movements_sku_date hace que sea O(log N) en lugar de O(N)
SELECT
    movement_date::date AS date,
    SUM(quantity) AS demand
FROM sm_stock_movements
WHERE
    sku_id = :sku_id
    AND movement_type = 'sale'
    AND movement_date >= CURRENT_DATE - INTERVAL '90 days'
    AND tenant_id = :tenant_id  -- RLS defense in depth
GROUP BY movement_date::date
ORDER BY date ASC;

4.2 SKUs para clasificación ABC (revenue 90 días)

SELECT
    s.id,
    s.code,
    s.name,
    COALESCE(
        SUM(sm.quantity * sm.unit_cost),
        0
    ) AS revenue_90d
FROM sm_skus s
LEFT JOIN sm_stock_movements sm ON (
    sm.sku_id = s.id
    AND sm.movement_type = 'sale'
    AND sm.movement_date >= CURRENT_DATE - INTERVAL '90 days'
    AND sm.tenant_id = :tenant_id
)
WHERE s.tenant_id = :tenant_id
    AND s.deleted_at IS NULL
GROUP BY s.id, s.code, s.name
ORDER BY revenue_90d DESC;

4.3 Dashboard summary (una query, no N+1)

SELECT
    COUNT(*) FILTER (WHERE stock_status = 'critical') AS critical_count,
    COUNT(*) FILTER (WHERE stock_status = 'low') AS low_count,
    COUNT(*) FILTER (WHERE stock_status = 'ok') AS ok_count,
    COUNT(*) FILTER (WHERE stock_status = 'overstock') AS overstock_count,
    COUNT(*) FILTER (WHERE stock_status = 'nodata') AS nodata_count,
    COUNT(*) FILTER (WHERE abc_class = 'A') AS class_a,
    COUNT(*) FILTER (WHERE abc_class = 'B') AS class_b,
    COUNT(*) FILTER (WHERE abc_class = 'C') AS class_c
FROM sm_skus
WHERE tenant_id = :tenant_id AND deleted_at IS NULL;

4.4 OCs pendientes con detalle (JOIN única)

SELECT
    po.id,
    po.status,
    po.suggested_at,
    po.total_estimated,
    s.name AS supplier_name,
    COUNT(poi.id) AS line_count,
    SUM(poi.quantity_suggested) AS total_units
FROM sm_purchase_orders po
JOIN sm_suppliers s ON s.id = po.supplier_id
JOIN sm_purchase_order_items poi ON poi.order_id = po.id
WHERE po.tenant_id = :tenant_id AND po.status = 'pending'
GROUP BY po.id, po.status, po.suggested_at, po.total_estimated, s.name
ORDER BY po.suggested_at DESC;

5. Migraciones (Alembic)

5.1 Convención de nombres de revisiones

alembic/versions/
  001_create_sm_tenants.py
  002_create_sm_users.py
  003_create_sm_magic_link_tokens.py
  004_create_sm_suppliers.py
  005_create_sm_skus.py
  006_create_sm_stock_movements.py
  007_create_sm_purchase_orders.py
  008_create_sm_purchase_order_items.py
  009_create_sm_forecast_results.py
  010_create_sm_forecast_runs.py
  011_create_sm_integrations.py
  012_create_sm_subscriptions_invoices.py
  013_add_rls_policies.py

5.2 Política de migraciones

• Cada migración debe ser reversible (tener downgrade() implementado)
• Nunca ejecutar DROP COLUMN en producción sin período de deprecación de al menos 1 sprint
• Columnas nuevas siempre con DEFAULT para que la migración sea online (no bloquee la tabla)
• Si la migración requiere backfill de datos, hacerlo en un script separado, no en la migración DDL

6. Row-Level Security (RLS)

-- Habilitar RLS en todas las tablas de negocio
ALTER TABLE sm_skus ENABLE ROW LEVEL SECURITY;
ALTER TABLE sm_stock_movements ENABLE ROW LEVEL SECURITY;
ALTER TABLE sm_purchase_orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE sm_purchase_order_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE sm_forecast_results ENABLE ROW LEVEL SECURITY;
-- ... etc para todas las tablas con tenant_id

-- Política de aislamiento (misma estructura para todas las tablas)
CREATE POLICY tenant_isolation ON sm_skus
    USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

-- El backend establece el tenant_id al inicio de cada transacción:
-- SET LOCAL app.tenant_id = '<uuid-del-tenant>';

El current_setting('app.tenant_id', true) usa el segundo argumento true para retornar NULL en lugar de error si la variable no está seteada, previniendo acceso accidental en contextos de background sin tenant.

7. Mantenimiento y housekeeping

Ver también: Doc 07 (SRD Backend — ORM SQLAlchemy y lógica de aplicación sobre este schema) · Doc 09 (Motor Core — cómo lee y escribe en sm_forecast_results y sm_stock_movements) · Doc 10 (Seguridad — RLS, cifrado de credenciales, pgcrypto) · wiki/04-stack-tecnico.md