// 01 — INTRO

Que es SQL

SQL es el lenguaje para hablar con bases de datos relacionales. PostgreSQL es el motor mas completo y robusto — soporta JSON, indices avanzados, full-text search y extensiones como PostGIS.

SQL (Structured Query Language) lleva mas de 50 anos vigente porque el modelo relacional resuelve un problema fundamental: almacenar datos con estructura y consultarlos eficientemente. En el ERP de carnicerías, PostgreSQL guarda ventas, inventario, cortes de caja, trazabilidad de producto y configuracion de sucursales.

🗄️

Datos relacionales

Tablas vinculadas por claves. Un producto se relaciona con sus movimientos, sus ventas y su proveedor.

⚡

Indices y velocidad

Con indices correctos, buscar entre millones de ventas tarda milisegundos. Sin ellos, segundos.

🔒

Transacciones ACID

Atomicas, Consistentes, Aisladas, Durables. Un corte de caja nunca queda a medias aunque falle la luz.

📦

JSON nativo

PostgreSQL almacena y consulta JSON con operadores propios. Perfecto para datos flexibles como configuracion de sucursales.

primeros-pasos.sqlSQL

-- Conectar con psql
-- psql -h localhost -U erp_user -d erp_carniceria

-- Ver todas las tablas
SELECT tablename FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;

-- Ver version de PostgreSQL
SELECT version();

-- Estructura de una tabla
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'productos'
ORDER BY ordinal_position;

// 02 — DDL

DDL — Crear Tablas

DDL (Data Definition Language): CREATE, ALTER, DROP. Define la estructura — tablas, tipos de dato, restricciones y relaciones. Se ejecuta una vez, pero se piensa mucho antes.

schema-basico.sql — tablas del ERPDDL

-- Tipos de dato mas usados en PostgreSQL
-- UUID, TEXT, VARCHAR(n), INTEGER, BIGINT, NUMERIC(p,s)
-- BOOLEAN, DATE, TIMESTAMP WITH TIME ZONE, JSONB

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";  -- para uuid_generate_v4()

-- Tabla de sucursales
CREATE TABLE sucursales (
  id          UUID         PRIMARY KEY DEFAULT uuid_generate_v4(),
  nombre      TEXT         NOT NULL,
  direccion   TEXT         NOT NULL,
  ciudad      TEXT         NOT NULL DEFAULT 'CDMX',
  activa      BOOLEAN      NOT NULL DEFAULT true,
  creado_en   TIMESTAMPTZ  NOT NULL DEFAULT now()
);

-- Tabla de productos con CHECK constraints
CREATE TABLE productos (
  id            UUID           PRIMARY KEY DEFAULT uuid_generate_v4(),
  codigo        TEXT           UNIQUE NOT NULL,
  nombre        TEXT           NOT NULL,
  categoria     TEXT           NOT NULL
                  CHECK (categoria IN ('res','cerdo','pollo','embutido','otro')),
  precio        NUMERIC(10,2)  NOT NULL CHECK (precio > 0),
  unidad        TEXT           NOT NULL
                  CHECK (unidad IN ('kg','pieza','litro')),
  perecedero    BOOLEAN        NOT NULL DEFAULT true,
  activo        BOOLEAN        NOT NULL DEFAULT true,
  creado_en     TIMESTAMPTZ    NOT NULL DEFAULT now(),
  actualizado_en TIMESTAMPTZ   NOT NULL DEFAULT now()
);

-- Tabla de ventas (FK a sucursales)
CREATE TABLE ventas (
  id            UUID           PRIMARY KEY DEFAULT uuid_generate_v4(),
  sucursal_id   UUID           NOT NULL
                  REFERENCES sucursales(id) ON DELETE RESTRICT,
  folio         TEXT           UNIQUE NOT NULL,
  total         NUMERIC(12,2)  NOT NULL CHECK (total >= 0),
  metodo_pago   TEXT           NOT NULL
                  CHECK (metodo_pago IN ('efectivo','tarjeta','transferencia')),
  estado        TEXT           NOT NULL DEFAULT 'pagada'
                  CHECK (estado IN ('abierta','pagada','cancelada')),
  creado_en     TIMESTAMPTZ    NOT NULL DEFAULT now()
);

-- Tabla de items de venta
CREATE TABLE venta_items (
  id          UUID          PRIMARY KEY DEFAULT uuid_generate_v4(),
  venta_id    UUID          NOT NULL REFERENCES ventas(id) ON DELETE CASCADE,
  producto_id UUID          NOT NULL REFERENCES productos(id),
  cantidad    NUMERIC(10,3) NOT NULL CHECK (cantidad > 0),
  precio      NUMERIC(10,2) NOT NULL,
  subtotal    NUMERIC(12,2) GENERATED ALWAYS AS (cantidad * precio) STORED
);

-- ALTER TABLE — modificar tablas existentes
ALTER TABLE productos ADD COLUMN descripcion TEXT;
ALTER TABLE productos ALTER COLUMN nombre SET NOT NULL;
ALTER TABLE ventas   ADD CONSTRAINT ventas_total_check CHECK (total >= 0);

💡

UUID vs SERIAL como primary keyUUID es mejor para sistemas distribuidos — no requiere coordinacion central y no expone el volumen de registros. Usa gen_random_uuid() (PostgreSQL 13+) en lugar de uuid_generate_v4() para evitar instalar la extension uuid-ossp.

// 03 — DML

DML — CRUD

DML (Data Manipulation Language): INSERT, SELECT, UPDATE, DELETE. Son las 4 operaciones de toda la vida. PostgreSQL los extiende con RETURNING, ON CONFLICT, UPDATE FROM y mas.

crud.sql — operaciones basicas con extensiones PGDML

-- INSERT basico
INSERT INTO productos (nombre, codigo, categoria, precio, unidad)
VALUES ('Arrachera Premium', 'RES-001', 'res', 189.50, 'kg');

-- INSERT multiples filas
INSERT INTO productos (nombre, codigo, categoria, precio, unidad) VALUES
  ('Costilla de res',   'RES-002', 'res',     145.00, 'kg'),
  ('Pechuga de pollo',  'POL-001', 'pollo',   89.00,  'kg'),
  ('Chorizo artesanal', 'EMB-001', 'embutido',124.00, 'pieza');

-- RETURNING — devuelve las filas afectadas (muy util con Node.js)
INSERT INTO ventas (sucursal_id, folio, total, metodo_pago)
VALUES ('uuid-sucursal-01', 'VTA-0001', 450.00, 'efectivo')
RETURNING id, folio, creado_en;

-- ON CONFLICT — upsert (insert o actualizar si ya existe)
INSERT INTO productos (codigo, nombre, precio, categoria, unidad)
VALUES ('RES-001', 'Arrachera', 199.00, 'res', 'kg')
ON CONFLICT (codigo) DO UPDATE SET
  precio        = EXCLUDED.precio,
  actualizado_en = now()
RETURNING id, precio;

-- UPDATE con condicion
UPDATE productos
SET
  precio         = precio * 1.05,   -- aumento del 5%
  actualizado_en = now()
WHERE categoria = 'res' AND activo = true
RETURNING nombre, precio;

-- UPDATE FROM — actualizar usando datos de otra tabla
UPDATE venta_items vi
SET    precio = p.precio
FROM   productos p
WHERE  vi.producto_id = p.id
  AND  vi.precio != p.precio;   -- sincronizar precios desactualizados

-- DELETE con RETURNING
DELETE FROM productos
WHERE activo = false AND creado_en < now() - INTERVAL '1 year'
RETURNING id, nombre;

-- Soft delete — preferible al DELETE fisico en el ERP
UPDATE productos
SET activo = false, actualizado_en = now()
WHERE id = 'uuid-producto';

⚠️

Nunca DELETE o UPDATE sin WHERE en produccionUn UPDATE sin WHERE actualiza todas las filas de la tabla. En el ERP eso podria cambiar los precios de todos los productos. Practica siempre: primero un SELECT con el mismo WHERE, verifica las filas, luego ejecuta el UPDATE/DELETE.

// 04 — SELECT

SELECT Avanzado

SELECT es el 80% del trabajo SQL diario. WHERE, ORDER BY, LIMIT, OFFSET, LIKE, BETWEEN, IN, IS NULL — mas los operadores de texto, fecha y comparacion de PostgreSQL.

select-avanzado.sqlSELECT

-- Clausulas basicas
SELECT id, nombre, precio, categoria
FROM   productos
WHERE  activo = true
ORDER BY precio DESC
LIMIT  20 OFFSET 0;  -- paginacion: pagina 1

-- WHERE con multiples condiciones
SELECT * FROM productos
WHERE  categoria IN ('res', 'cerdo')
  AND  precio BETWEEN 100 AND 300
  AND  activo = true;

-- LIKE e ILIKE (case-insensitive)
SELECT nombre, precio FROM productos
WHERE  nombre ILIKE '%arrachera%';  -- busqueda parcial sin importar mayusculas

-- Full text search (mejor rendimiento que LIKE)
SELECT nombre, precio FROM productos
WHERE  to_tsvector('spanish', nombre) @@ plainto_tsquery('spanish', 'arrachera premium');

-- Columnas calculadas y alias
SELECT
  nombre,
  precio                           AS precio_sin_iva,
  precio * 1.16                    AS precio_con_iva,
  ROUND(precio * 1.16, 2)         AS precio_iva_redondeado,
  CASE
    WHEN precio < 100  THEN 'economico'
    WHEN precio < 200  THEN 'medio'
    ELSE                    'premium'
  END                              AS segmento
FROM productos
WHERE activo = true;

-- Fechas — filtrar ventas de hoy y del mes
SELECT * FROM ventas
WHERE creado_en >= CURRENT_DATE               -- desde inicio del dia
  AND creado_en <  CURRENT_DATE + INTERVAL '1 day';

-- Ventas del mes actual
SELECT * FROM ventas
WHERE DATE_TRUNC('month', creado_en) = DATE_TRUNC('month', now());

-- DISTINCT — sin duplicados
SELECT DISTINCT categoria FROM productos WHERE activo = true;

-- COALESCE — valor por defecto si NULL
SELECT
  nombre,
  COALESCE(descripcion, 'Sin descripcion') AS descripcion
FROM productos;

Simulador SQL — consulta la tabla de productos del ERP▶ LIVE

SELECT nombre, categoria, precio, CASE WHEN precio < 100 THEN 'economico' WHEN precio < 200 THEN 'medio' ELSE 'premium' END AS segmento FROM productos WHERE activo = true ORDER BY precio DESC

// resultados

Escribe una query y ejecuta...

// 05 — JOINS

JOINs — Combinar Tablas

Los JOINs combinan filas de dos o mas tablas usando una condicion. INNER JOIN es el mas comun. LEFT JOIN incluye filas sin match. Son la esencia del modelo relacional.

joins.sqlJOIN

-- INNER JOIN — solo filas con match en ambas tablas
SELECT
  v.folio,
  v.total,
  v.metodo_pago,
  s.nombre  AS sucursal,
  v.creado_en
FROM  ventas v
INNER JOIN sucursales s ON s.id = v.sucursal_id
WHERE v.estado = 'pagada'
ORDER BY v.creado_en DESC
LIMIT 50;

-- JOIN multiple — ventas con sus items y productos
SELECT
  v.folio,
  p.nombre      AS producto,
  p.categoria,
  vi.cantidad,
  vi.precio,
  vi.subtotal
FROM       ventas      v
INNER JOIN venta_items vi ON vi.venta_id    = v.id
INNER JOIN productos   p  ON p.id          = vi.producto_id
WHERE v.id = 'uuid-venta';

-- LEFT JOIN — incluye ventas aunque no tengan cliente asignado
SELECT
  v.folio,
  v.total,
  COALESCE(c.nombre, 'Venta mostrador') AS cliente
FROM      ventas   v
LEFT JOIN clientes c ON c.id = v.cliente_id
ORDER BY v.creado_en DESC;

-- LEFT JOIN para encontrar productos sin ventas
SELECT p.nombre, p.categoria
FROM      productos   p
LEFT JOIN venta_items vi ON vi.producto_id = p.id
WHERE vi.id IS NULL        -- el LEFT JOIN devuelve NULL cuando no hay match
  AND p.activo = true;

-- SELF JOIN — comparar filas de la misma tabla
-- Productos mas caros que el promedio de su categoria
SELECT p1.nombre, p1.precio, p1.categoria
FROM       productos p1
INNER JOIN (
  SELECT categoria, AVG(precio) AS promedio
  FROM   productos
  GROUP BY categoria
) p2 ON p2.categoria = p1.categoria
WHERE p1.precio > p2.promedio
ORDER BY p1.categoria, p1.precio DESC;

🔗

Tipos de JOIN en resumenINNER JOIN: solo filas con match en ambas tablas. LEFT JOIN: todas las filas de la izquierda + las que hacen match. RIGHT JOIN: inverso del LEFT (raro, prefiere LEFT JOIN). FULL OUTER JOIN: todas las filas de ambas tablas. CROSS JOIN: producto cartesiano (todas contra todas — rara vez util).

// 06 — AGREGACION

Agregacion y GROUP BY

Las funciones de agregacion (COUNT, SUM, AVG, MAX, MIN) colapsan multiples filas en un resumen. GROUP BY agrupa por categorias. HAVING filtra grupos — es el WHERE de los grupos.

agregacion.sql — reportes del ERPAGG

-- Funciones de agregacion basicas
SELECT
  COUNT(*)                AS total_ventas,
  COUNT(DISTINCT sucursal_id) AS sucursales_activas,
  SUM(total)             AS ingresos_totales,
  AVG(total)             AS ticket_promedio,
  MAX(total)             AS venta_mayor,
  MIN(total)             AS venta_menor
FROM ventas
WHERE estado = 'pagada'
  AND creado_en >= DATE_TRUNC('month', now());

-- GROUP BY — ventas por sucursal
SELECT
  s.nombre              AS sucursal,
  COUNT(v.id)          AS num_ventas,
  SUM(v.total)         AS total,
  ROUND(AVG(v.total),2) AS promedio
FROM       ventas      v
INNER JOIN sucursales  s ON s.id = v.sucursal_id
WHERE  v.estado = 'pagada'
  AND  v.creado_en >= DATE_TRUNC('month', now())
GROUP BY s.id, s.nombre
ORDER BY total DESC;

-- HAVING — filtrar grupos (no puede usar alias del SELECT)
SELECT
  sucursal_id,
  COUNT(*) AS num_ventas,
  SUM(total) AS total_mes
FROM  ventas
WHERE estado = 'pagada'
GROUP BY sucursal_id
HAVING SUM(total) > 50000  -- solo sucursales con mas de 50k en ventas
ORDER BY total_mes DESC;

-- Productos mas vendidos del mes (top 10)
SELECT
  p.nombre,
  p.categoria,
  SUM(vi.cantidad)   AS kg_vendidos,
  SUM(vi.subtotal)   AS ingresos,
  COUNT(vi.venta_id) AS num_ventas
FROM       venta_items vi
INNER JOIN productos   p  ON p.id = vi.producto_id
INNER JOIN ventas      v  ON v.id = vi.venta_id
WHERE v.estado = 'pagada'
  AND v.creado_en >= DATE_TRUNC('month', now())
GROUP BY p.id, p.nombre, p.categoria
ORDER BY ingresos DESC
LIMIT 10;

-- Ventas por dia de la semana
SELECT
  TO_CHAR(creado_en, 'Day') AS dia,
  EXTRACT(DOW FROM creado_en) AS num_dia,
  COUNT(*) AS ventas,
  SUM(total) AS total
FROM  ventas
WHERE estado = 'pagada'
GROUP BY dia, num_dia
ORDER BY num_dia;

// 07 — SUBQUERIES Y CTEs

Subqueries y CTEs

Una subquery es una SELECT dentro de otra. Los CTEs (Common Table Expressions, WITH) son subqueries con nombre — mas legibles, reutilizables y que el optimizador puede materializar.

cte-subqueries.sqlCTE

-- Subquery en WHERE
SELECT nombre, precio
FROM   productos
WHERE  precio > (
  SELECT AVG(precio) FROM productos WHERE activo = true
);

-- Subquery EXISTS — productos que se han vendido este mes
SELECT nombre, categoria
FROM   productos p
WHERE  EXISTS (
  SELECT 1 FROM venta_items vi
  INNER JOIN ventas v ON v.id = vi.venta_id
  WHERE vi.producto_id = p.id
    AND v.creado_en >= DATE_TRUNC('month', now())
);

-- CTE basico (WITH)
WITH ventas_mes AS (
  SELECT
    sucursal_id,
    SUM(total)  AS total_mes,
    COUNT(*) AS num_ventas
  FROM  ventas
  WHERE estado = 'pagada'
    AND creado_en >= DATE_TRUNC('month', now())
  GROUP BY sucursal_id
)
SELECT
  s.nombre,
  vm.total_mes,
  vm.num_ventas,
  ROUND(vm.total_mes / vm.num_ventas, 2) AS ticket_prom
FROM       ventas_mes  vm
INNER JOIN sucursales s ON s.id = vm.sucursal_id
ORDER BY   vm.total_mes DESC;

-- CTEs encadenados — reporte de corte de caja
WITH
ventas_hoy AS (
  SELECT * FROM ventas
  WHERE sucursal_id = 'uuid-suc'
    AND creado_en >= CURRENT_DATE
    AND estado = 'pagada'
),
resumen_metodo AS (
  SELECT metodo_pago, SUM(total) AS monto, COUNT(*) AS num
  FROM  ventas_hoy
  GROUP BY metodo_pago
),
top_productos AS (
  SELECT p.nombre, SUM(vi.subtotal) AS vendido
  FROM       ventas_hoy              v
  INNER JOIN venta_items           vi ON vi.venta_id = v.id
  INNER JOIN productos             p  ON p.id = vi.producto_id
  GROUP BY p.nombre
  ORDER BY vendido DESC
  LIMIT 5
)
SELECT 'resumen_metodo' AS tipo, metodo_pago AS clave, monto::TEXT AS valor
FROM resumen_metodo
UNION ALL
SELECT 'top_producto', nombre, vendido::TEXT
FROM top_productos;

💡

CTE vs subquery — cual usar?Usa CTE cuando necesitas reutilizar el mismo resultado en multiples partes de la query, o cuando la subquery anidada es dificil de leer. Usa subquery inline cuando es simple y se usa solo una vez. En PostgreSQL 12+, el optimizador puede "inlinear" CTEs automaticamente si no son recursivos.

// 08 — INDICES

Indices — Velocidad

Un indice es una estructura de datos adicional que acelera las busquedas a costo de espacio y escrituras mas lentas. Sin indices correctos, el sistema se vuelve lento con el tiempo.

B-TreeGINGiST ParcialCompuestoCONCURRENTLY

indices.sql — indices del ERPIDX

-- B-Tree (default) — ideal para =, <, >, BETWEEN, ORDER BY
CREATE INDEX idx_ventas_sucursal_id   ON ventas(sucursal_id);
CREATE INDEX idx_ventas_creado_en     ON ventas(creado_en DESC);
CREATE INDEX idx_venta_items_venta_id ON venta_items(venta_id);
CREATE INDEX idx_productos_codigo     ON productos(codigo);

-- Indice compuesto — acelera queries con multiples columnas en WHERE
-- Orden importa: primero la mas selectiva, o la que filtra mas
CREATE INDEX idx_ventas_sucursal_estado_fecha
  ON ventas(sucursal_id, estado, creado_en DESC);
-- Sirve para: WHERE sucursal_id=X AND estado='pagada' ORDER BY creado_en DESC

-- Indice parcial — solo indexa un subconjunto de filas
-- Mucho mas pequeno y rapido que un indice completo
CREATE INDEX idx_productos_activos
  ON productos(nombre, categoria)
  WHERE activo = true;    -- 90% de las queries solo usan productos activos

CREATE INDEX idx_ventas_abiertas
  ON ventas(sucursal_id, creado_en)
  WHERE estado = 'abierta';  -- solo las ventas en proceso (minoría)

-- GIN — para full-text search y JSONB
CREATE INDEX idx_productos_nombre_fts
  ON productos USING GIN(to_tsvector('spanish', nombre));

CREATE INDEX idx_sync_payload ON sync_eventos USING GIN(payload);  -- busqueda en JSONB

-- CONCURRENTLY — crear indice sin bloquear la tabla (produccion)
CREATE INDEX CONCURRENTLY idx_venta_items_producto_id
  ON venta_items(producto_id);

-- Ver indices de una tabla
SELECT indexname, indexdef
FROM   pg_indexes
WHERE  tablename = 'ventas';

-- Ver indices no usados (candidatos a eliminar)
SELECT
  schemaname, tablename, indexname,
  idx_scan    AS veces_usado,
  pg_size_pretty(pg_relation_size(indexrelid)) AS tamano
FROM  pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Tipo	Operadores	Caso de uso en ERP
B-Tree	=, <, >, BETWEEN, LIKE 'x%'	Busqueda por ID, fecha, codigo de barras
GIN	@>, <@, @@, ?	Full-text search en nombres, JSONB de configuracion
Parcial	(cualquiera + WHERE)	Solo productos activos, solo ventas abiertas
Compuesto	(multi-columna)	Queries con varios filtros: sucursal + estado + fecha

⚠️

Mas indices no es mejorCada indice ralentiza INSERT, UPDATE y DELETE porque PostgreSQL debe actualizarlos. El principio: crea indices basandote en queries reales lentas identificadas con EXPLAIN ANALYZE. Nunca indices de forma preventiva o especulativa.

// 09 — WINDOW FUNCTIONS

Window Functions

Las window functions calculan un valor por fila considerando un grupo de filas relacionadas — sin colapsar filas como GROUP BY. Son el arma secreta del SQL avanzado para rankings, acumulados y comparativas.

window-functions.sqlADV

-- OVER() basico — agrega sin colapsar filas
SELECT
  nombre,
  categoria,
  precio,
  AVG(precio) OVER ()              AS precio_promedio_global,
  precio - AVG(precio) OVER ()    AS diferencia_promedio
FROM productos
WHERE activo = true;

-- PARTITION BY — ventana por categoria
SELECT
  nombre,
  categoria,
  precio,
  AVG(precio) OVER (PARTITION BY categoria) AS prom_categoria,
  MAX(precio) OVER (PARTITION BY categoria) AS max_categoria
FROM productos
WHERE activo = true;

-- ROW_NUMBER / RANK — ranking de productos por ingresos
SELECT
  ROW_NUMBER() OVER (ORDER BY ingresos DESC) AS posicion,
  RANK()       OVER (ORDER BY ingresos DESC) AS rank_con_empates,
  nombre,
  ingresos
FROM (
  SELECT p.nombre, SUM(vi.subtotal) AS ingresos
  FROM       venta_items vi
  INNER JOIN productos   p ON p.id = vi.producto_id
  GROUP BY p.id, p.nombre
) t;

-- Ranking por categoria — top 3 de cada categoria
SELECT * FROM (
  SELECT
    nombre, categoria, precio,
    RANK() OVER (PARTITION BY categoria ORDER BY precio DESC) AS rank_cat
  FROM productos
  WHERE activo = true
) ranked
WHERE rank_cat <= 3;

-- Total acumulado de ventas por dia (running total)
SELECT
  fecha,
  total_dia,
  SUM(total_dia) OVER (ORDER BY fecha) AS acumulado_mes
FROM (
  SELECT
    DATE_TRUNC('day', creado_en) AS fecha,
    SUM(total)                   AS total_dia
  FROM  ventas
  WHERE estado = 'pagada'
    AND creado_en >= DATE_TRUNC('month', now())
  GROUP BY 1
) diario
ORDER BY fecha;

-- LAG / LEAD — comparar con la fila anterior/siguiente
SELECT
  fecha,
  total_dia,
  LAG(total_dia) OVER (ORDER BY fecha)  AS dia_anterior,
  total_dia - LAG(total_dia) OVER (ORDER BY fecha) AS variacion
FROM ( -- misma subquery de arriba
  SELECT DATE_TRUNC('day', creado_en) AS fecha, SUM(total) AS total_dia
  FROM ventas WHERE estado='pagada'
  GROUP BY 1
) d ORDER BY fecha;

// 10 — TRANSACCIONES

Transacciones ACID

Una transaccion agrupa multiples operaciones en una unidad atomica. O se ejecutan todas (COMMIT) o ninguna (ROLLBACK). Esencial para ventas, cortes de caja y cualquier operacion multi-tabla.

transacciones.sql + Node.js pgACID

-- Transaccion SQL explicita
BEGIN;

  -- 1. Registrar la venta
  INSERT INTO ventas (sucursal_id, folio, total, metodo_pago)
  VALUES ('suc-01', 'VTA-1042', 567.50, 'efectivo')
  RETURNING id INTO venta_id;

  -- 2. Registrar los items
  INSERT INTO venta_items (venta_id, producto_id, cantidad, precio)
  VALUES
    (venta_id, 'prod-001', 2.5,  189.00),
    (venta_id, 'prod-002', 1.0,  145.00);

  -- 3. Actualizar inventario
  UPDATE inventario
  SET    stock = stock - 2.5
  WHERE  producto_id = 'prod-001'
    AND  sucursal_id = 'suc-01';

COMMIT;  -- todas las operaciones persisten juntas

-- Si algo falla: ROLLBACK (deshace todo desde BEGIN)

-- SAVEPOINT — puntos de control dentro de una transaccion
BEGIN;
  INSERT INTO ventas (...) VALUES (...);
  SAVEPOINT despues_venta;

  INSERT INTO venta_items (...) VALUES (...);
  -- si esto falla, podemos volver al savepoint sin cancelar todo
  ROLLBACK TO despues_venta;

COMMIT;

-- ── En Node.js con pg (patron recomendado) ────────────
// import { Pool } from 'pg'
// const db = new Pool(config.db)
//
// async function registrarVenta(dto) {
//   const client = await db.connect()
//   try {
//     await client.query('BEGIN')
//
//     const { rows: [venta] } = await client.query(
//       'INSERT INTO ventas(...) VALUES($1,$2,$3) RETURNING *',
//       [dto.sucursalId, dto.folio, dto.total]
//     )
//
//     for (const item of dto.items) {
//       await client.query(
//         'INSERT INTO venta_items(venta_id,...) VALUES($1,$2,$3,$4)',
//         [venta.id, item.productoId, item.cantidad, item.precio]
//       )
//     }
//
//     await client.query('COMMIT')
//     return venta
//   } catch (err) {
//     await client.query('ROLLBACK')
//     throw err
//   } finally {
//     client.release()  // siempre devolver al pool
//   }
// }

// 11 — JSON EN POSTGRES

JSON en PostgreSQL

PostgreSQL tiene soporte nativo para JSON con el tipo JSONB — binario, indexable, con operadores propios. Perfecto para configuraciones de sucursal, metadatos de productos y payloads de sincronizacion.

json-postgres.sql — JSONB en el ERPPG

-- Crear tabla con columna JSONB
CREATE TABLE sucursal_config (
  sucursal_id  UUID   PRIMARY KEY REFERENCES sucursales(id),
  config       JSONB  NOT NULL DEFAULT '{}'
);

-- Insertar datos JSON
INSERT INTO sucursal_config VALUES (
  'uuid-suc-01',
  '{
    "impresora": "EPSON-TM88",
    "bascula":   "OHAUS-D52P",
    "turnos":    ["manana","tarde"],
    "descuentos": { "empleado": 0.15, "mayoreo": 0.10 },
    "cfdi":      { "rfc": "VLM010101AAA", "regimen": "601" }
  }'
);

-- Operadores JSONB
-- ->  devuelve valor como JSON
-- ->> devuelve valor como TEXT
-- #>  acceso por ruta como JSON
-- #>> acceso por ruta como TEXT

SELECT
  sucursal_id,
  config ->> 'impresora'              AS impresora,
  config -> 'descuentos' ->> 'empleado' AS desc_empleado,
  config #>> '{cfdi,rfc}'             AS rfc
FROM sucursal_config;

-- Filtrar por contenido JSONB (operador @>)
SELECT sucursal_id
FROM   sucursal_config
WHERE  config @> '{"bascula": "OHAUS-D52P"}';  -- contiene este sub-JSON

-- Actualizar campo especifico del JSON (sin reemplazar todo)
UPDATE sucursal_config
SET    config = jsonb_set(config, '{descuentos,empleado}', '0.20')
WHERE  sucursal_id = 'uuid-suc-01';

-- Agregar clave nueva al JSON existente (||)
UPDATE sucursal_config
SET    config = config || '{"pos_version": "2.4.1"}'::jsonb
WHERE  sucursal_id = 'uuid-suc-01';

-- Eliminar clave del JSON
UPDATE sucursal_config
SET    config = config - 'impresora'  -- elimina la clave
WHERE  sucursal_id = 'uuid-suc-01';

-- jsonb_array_elements — expandir array JSON en filas
SELECT
  sucursal_id,
  jsonb_array_elements_text(config -> 'turnos') AS turno
FROM sucursal_config;

-- jsonb_object_keys — ver las claves del JSON
SELECT jsonb_object_keys(config) AS clave
FROM   sucursal_config
WHERE  sucursal_id = 'uuid-suc-01';

💡

JSONB vs JSONSiempre usa JSONB en lugar de JSON. JSONB almacena en formato binario (mas rapido para leer), elimina espacios y claves duplicadas, y soporta indices GIN. JSON solo conserva el texto original exacto — util unicamente si necesitas preservar el orden de claves o espacios.

// 12 — EXPLAIN Y PERFORMANCE

EXPLAIN y Performance

EXPLAIN ANALYZE muestra como PostgreSQL ejecuta una query — que operaciones hace, cuanto tiempo tarda cada una y si usa indices. Es la herramienta principal para diagnosticar queries lentas.

explain-performance.sqlPERF

-- EXPLAIN — plan sin ejecutar (estimaciones)
EXPLAIN
SELECT * FROM ventas
WHERE sucursal_id = 'uuid-suc' AND estado = 'pagada';
-- Index Scan using idx_ventas_sucursal_estado_fecha   ← usa indice (bueno)
-- Seq Scan on ventas                                  ← escaneo completo (lento)

-- EXPLAIN ANALYZE — ejecuta la query y mide tiempos reales
EXPLAIN ANALYZE
SELECT
  v.folio, s.nombre, SUM(vi.subtotal) AS total_items
FROM       ventas      v
INNER JOIN sucursales  s  ON s.id = v.sucursal_id
INNER JOIN venta_items vi ON vi.venta_id = v.id
WHERE v.creado_en >= now() - INTERVAL '30 days'
GROUP BY v.id, v.folio, s.nombre;

-- Lo que debes buscar en el output:
-- actual time=0.012..0.034    → tiempo real (bajo = bueno)
-- rows=1243                   → filas reales procesadas
-- Planning Time: 2.1 ms       → tiempo de planificacion
-- Execution Time: 18.4 ms     → tiempo total de ejecucion

-- EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS) — mas detalle
EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS)
SELECT * FROM ventas WHERE sucursal_id = 'uuid';

-- Queries lentas activas
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duracion,
  query
FROM  pg_stat_activity
WHERE state = 'active'
  AND query_start < now() - INTERVAL '5 seconds'
ORDER BY duracion DESC;

-- Tablas con mas actividad (candidatas a analizar)
SELECT
  relname AS tabla,
  seq_scan        AS escaneos_secuenciales,
  idx_scan        AS escaneos_por_indice,
  n_live_tup      AS filas_activas
FROM  pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 10;

-- VACUUM ANALYZE — actualizar estadisticas para el planner
ANALYZE ventas;           -- actualizar estadisticas
VACUUM ANALYZE ventas;    -- limpiar filas muertas + actualizar estadisticas

Señal en EXPLAIN	Problema	Solucion
Seq Scan (filas altas)	Sin indice en columna filtrada	CREATE INDEX en la columna del WHERE
Hash Join (memoria alta)	work_mem insuficiente	SET work_mem = '64MB' para la sesion
rows estimado vs real muy diferente	Estadisticas desactualizadas	ANALYZE tabla
Nested Loop (muchas iteraciones)	JOIN sin indice en clave foranea	Indice en la columna FK

// 13 — SCHEMA DEL ERP

Schema del ERP Carnicerías

El schema completo de produccion: 12 tablas, claves foraneas, indices criticos, triggers para timestamps y vistas materializadas para reportes.

schema-erp-carniceria.sql — schema de produccionREAL

-- ── Extensions ────────────────────────────────────────
CREATE EXTENSION IF NOT EXISTS "pgcrypto";   -- gen_random_uuid()
CREATE EXTENSION IF NOT EXISTS "unaccent";  -- busqueda sin acentos

-- ── Trigger para actualizado_en automatico ────────────
CREATE OR REPLACE FUNCTION set_actualizado_en()
RETURNS TRIGGER AS $$
BEGIN
  NEW.actualizado_en = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ── Tabla: usuarios ───────────────────────────────────
CREATE TABLE usuarios (
  id             UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  email          TEXT        UNIQUE NOT NULL,
  password_hash  TEXT        NOT NULL,
  nombre         TEXT        NOT NULL,
  rol            TEXT        NOT NULL CHECK (rol IN ('admin','gerente','cajero','almacen')),
  sucursal_id    UUID        REFERENCES sucursales(id),
  activo         BOOLEAN     NOT NULL DEFAULT true,
  ultimo_acceso  TIMESTAMPTZ,
  creado_en      TIMESTAMPTZ NOT NULL DEFAULT now(),
  actualizado_en TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ── Tabla: inventario ─────────────────────────────────
CREATE TABLE inventario (
  id             UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
  sucursal_id    UUID           NOT NULL REFERENCES sucursales(id),
  producto_id    UUID           NOT NULL REFERENCES productos(id),
  stock          NUMERIC(10,3)  NOT NULL DEFAULT 0 CHECK (stock >= 0),
  stock_minimo   NUMERIC(10,3)  NOT NULL DEFAULT 0,
  actualizado_en TIMESTAMPTZ    NOT NULL DEFAULT now(),
  UNIQUE (sucursal_id, producto_id)
);

-- ── Tabla: clientes ───────────────────────────────────
CREATE TABLE clientes (
  id             UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  nombre         TEXT        NOT NULL,
  rfc            TEXT        UNIQUE,
  email          TEXT,
  telefono       TEXT,
  credito_limite NUMERIC(10,2) NOT NULL DEFAULT 0,
  notas          TEXT,
  creado_en      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ── Indices criticos ──────────────────────────────────
CREATE INDEX idx_ventas_suc_est_fecha ON ventas(sucursal_id, estado, creado_en DESC);
CREATE INDEX idx_items_venta          ON venta_items(venta_id);
CREATE INDEX idx_items_producto       ON venta_items(producto_id);
CREATE INDEX idx_inventario_bajo      ON inventario(sucursal_id, producto_id)
  WHERE stock <= stock_minimo;        -- solo items en alerta de stock
CREATE INDEX idx_productos_fts        ON productos
  USING GIN(to_tsvector('spanish', unaccent(nombre)));

-- ── Vista materializada — resumen diario (pre-calculado) ─
CREATE MATERIALIZED VIEW mv_ventas_diarias AS
SELECT
  DATE_TRUNC('day', v.creado_en) AS fecha,
  v.sucursal_id,
  COUNT(*)         AS num_ventas,
  SUM(v.total)    AS total,
  AVG(v.total)    AS promedio,
  v.metodo_pago
FROM  ventas v
WHERE v.estado = 'pagada'
GROUP BY 1, 2, 6
WITH DATA;

-- Refrescar la vista (correr cada noche con pg_cron)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_ventas_diarias;

// 14 — QUERIES DEL ERP

Queries Reales del ERP

Las queries que corren en produccion: corte de caja, alertas de stock, reporte de ventas por sucursal, trazabilidad de producto y cierre mensual.

queries-produccion.sqlREAL

-- 1. CORTE DE CAJA ─────────────────────────────────────
WITH corte AS (
  SELECT
    metodo_pago,
    COUNT(*) AS num_ventas,
    SUM(total) AS monto
  FROM  ventas
  WHERE sucursal_id = '$1'
    AND creado_en >= CURRENT_DATE
    AND estado = 'pagada'
  GROUP BY metodo_pago
)
SELECT
  metodo_pago, num_ventas, monto,
  ROUND(monto * 100.0 / SUM(monto) OVER(), 1) AS pct_total
FROM corte
ORDER BY monto DESC;

-- 2. ALERTAS DE STOCK BAJO ─────────────────────────────
SELECT
  p.nombre, p.categoria, p.unidad,
  i.stock,
  i.stock_minimo,
  i.stock_minimo - i.stock AS faltante
FROM       inventario i
INNER JOIN productos  p ON p.id = i.producto_id
WHERE i.sucursal_id = '$1'
  AND i.stock <= i.stock_minimo
  AND p.activo = true
ORDER BY faltante DESC;

-- 3. RANKING DE SUCURSALES — VENTANA MENSUAL ───────────
SELECT
  s.nombre,
  SUM(v.total) AS total_mes,
  RANK() OVER (ORDER BY SUM(v.total) DESC) AS posicion,
  ROUND(SUM(v.total) * 100.0 /
    SUM(SUM(v.total)) OVER(), 1) AS pct_red
FROM       ventas     v
INNER JOIN sucursales s ON s.id = v.sucursal_id
WHERE v.estado = 'pagada'
  AND v.creado_en >= DATE_TRUNC('month', now())
GROUP BY s.id, s.nombre
ORDER BY posicion;

-- 4. TRAZABILIDAD — historia de un producto ────────────
WITH movimientos AS (
  SELECT
    'venta' AS tipo,
    v.creado_en AS fecha,
    v.folio     AS referencia,
    -vi.cantidad AS cantidad,    -- negativo = salida
    s.nombre    AS sucursal
  FROM       venta_items vi
  INNER JOIN ventas      v ON v.id = vi.venta_id
  INNER JOIN sucursales  s ON s.id = v.sucursal_id
  WHERE vi.producto_id = '$1'
    AND v.estado = 'pagada'
)
SELECT
  tipo, fecha, referencia, sucursal,
  SUM(cantidad) OVER (ORDER BY fecha) AS stock_acumulado
FROM  movimientos
ORDER BY fecha DESC
LIMIT 100;

🥩

PostgreSQL es el centro del ERPCada venta, cada item, cada movimiento de inventario pasa por estas queries. Moleculer llama a estas queries desde sus servicios, Node.js las ejecuta con el driver pg, y el pool de conexiones asegura que multiples sucursales puedan operar en paralelo sin conflictos.