consultoria-as
a386ca5b4a
- F0 blindaje: Redis/Celery, pg_hba sin trust LAN, secretos a env 600, backups, webhook auth, jobs muertos - F1 caja: bugs POS, reembolsos al corte, apartados, CFDI, topes de descuento en cotizacion/apartado - F2/F7 IA de catalogo: autocategorizacion, dedup de SKUs con embeddings, OCR de facturas, descripciones, claves SAT, limpieza de fitment - F3 IA en el flujo: vision Yonke, preguntas MeLi, venta cruzada, anomalias, sinonimos, busqueda semantica (rerank/embeddings) - F4/F8 escala y cartera: /metrics+Prometheus+Grafana, pool, score de credito, churn, metricas multi-tenant - Nexus Yonke (M1-M6): modelo de datos 1-de-1, alta por donador, vitrina publica, MeLi usado, consignacion, garantia por dias, reportes, etiquetas - Marketplace B2B entre yonkes (catalogo + pedidos cross-tenant) - Seguridad: auth en /api/admin/* (35 endpoints), alertas por tenant - Migraciones v4.27/v4.28; timers expire-demos y rollup-metrics
57 lines
3.5 KiB
SQL
57 lines
3.5 KiB
SQL
-- ═══════════════════════════════════════════════════════════════════════════
|
|
-- v4.28 — MeLi commerce: listing economics, inventory-match metadata
|
|
-- Additive + idempotent. Runs against tenant DBs.
|
|
--
|
|
-- Goal: make MercadoLibre listings sellable end-to-end by persisting, per
|
|
-- listing, the data that today is only computed transiently:
|
|
-- * how the ML item was linked to local inventory (backfill audit)
|
|
-- * the ML commission and the resulting net price / margin
|
|
-- * the cost at the moment of publishing (so margin stays comparable)
|
|
-- * the listing type and the description actually published
|
|
--
|
|
-- Also normalizes marketplace_questions.status to lowercase: ML returns
|
|
-- "UNANSWERED"/"ANSWERED" and the auto-answer/SLA queries filter lowercase.
|
|
-- ═══════════════════════════════════════════════════════════════════════════
|
|
|
|
-- ─── 1. marketplace_listings: match metadata + economics ────────────────────
|
|
ALTER TABLE marketplace_listings
|
|
ADD COLUMN IF NOT EXISTS listing_type_id VARCHAR(30),
|
|
ADD COLUMN IF NOT EXISTS inventory_match_source VARCHAR(40),
|
|
ADD COLUMN IF NOT EXISTS inventory_matched_at TIMESTAMPTZ,
|
|
ADD COLUMN IF NOT EXISTS meli_fee NUMERIC(12,2),
|
|
ADD COLUMN IF NOT EXISTS meli_fee_pct NUMERIC(6,2),
|
|
ADD COLUMN IF NOT EXISTS meli_net_price NUMERIC(12,2),
|
|
ADD COLUMN IF NOT EXISTS meli_fee_source VARCHAR(20),
|
|
ADD COLUMN IF NOT EXISTS cost_at_publish NUMERIC(12,2),
|
|
ADD COLUMN IF NOT EXISTS margin_pct NUMERIC(8,2),
|
|
ADD COLUMN IF NOT EXISTS meli_description TEXT,
|
|
ADD COLUMN IF NOT EXISTS economics_updated_at TIMESTAMPTZ;
|
|
|
|
-- Fast lookup of the listings that still need an inventory match (backfill).
|
|
CREATE INDEX IF NOT EXISTS idx_marketplace_listings_unmapped
|
|
ON marketplace_listings(channel)
|
|
WHERE inventory_id IS NULL;
|
|
|
|
-- ─── 2. marketplace_questions: status normalization ─────────────────────────
|
|
-- ML's API returns "UNANSWERED" / "ANSWERED"; the service compares lowercase.
|
|
-- Normalize historical rows so auto-answer and the SLA job see them.
|
|
UPDATE marketplace_questions
|
|
SET status = LOWER(status), updated_at = NOW()
|
|
WHERE status IS NOT NULL AND status <> LOWER(status);
|
|
|
|
-- Index for the SLA sweep: oldest unanswered questions first.
|
|
CREATE INDEX IF NOT EXISTS idx_marketplace_questions_status_date
|
|
ON marketplace_questions(status, question_date);
|
|
|
|
-- ─── 3. Allow several active ML listings per local product ──────────────────
|
|
-- The original v3.4 unique index assumed one ML listing per inventory item.
|
|
-- Real MeLi catalogs routinely publish several listings (different fitments /
|
|
-- titles) that share the same internal part number; the inventory backfill
|
|
-- must be able to link them all, and order conversion maps each order item by
|
|
-- external_item_id. Replace the uniqueness with a plain lookup index — the
|
|
-- publish path now upserts by (inventory_id, channel) explicitly.
|
|
DROP INDEX IF EXISTS idx_marketplace_listings_unique;
|
|
CREATE INDEX IF NOT EXISTS idx_marketplace_listings_inv_channel
|
|
ON marketplace_listings(inventory_id, channel) WHERE is_active = true;
|
|
|