Proyecto práctico: Sistema de inventario con auditoría y control de stock en PostgreSQL

Este tutorial te guía para crear una base de datos de inventario robusta en PostgreSQL: modelo normalizado, tabla de eventos (transacciones), proyección de stock, funciones atómicas para mutar stock, materialized view para reportes y ejemplos de consultas y optimizaciones.

Requisitos previos

• PostgreSQL 12+ instalado (psql disponible).
• Acceso a la línea de comandos o pgAdmin para ejecutar scripts.
• Conocimientos básicos de SQL y transacciones.

Estructura de carpetas

sql-inventory/
├── schema.sql           -- tablas y constraints
├── functions.sql        -- funciones y procedimientos principales
├── triggers.sql         -- triggers opcionales (si decides usarlos)
├── sample_data.sql      -- datos de ejemplo
├── queries.sql          -- consultas y reportes útiles
└── README.md            -- instrucciones rápidas

1) schema.sql — diseño de tablas

-- schema.sql
-- Esquema básico: products, locations, stock (proyección), transactions (evento)

CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; -- para uuids si lo prefieres

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  sku TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  unit_cost NUMERIC(12,4) NOT NULL DEFAULT 0, -- para valoración
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

CREATE TABLE locations (
  location_id SERIAL PRIMARY KEY,
  code TEXT UNIQUE NOT NULL,
  description TEXT
);

-- Stock es la proyección derivada del stream de transacciones.
CREATE TABLE stock (
  product_id INT NOT NULL REFERENCES products(product_id) ON DELETE CASCADE,
  location_id INT NOT NULL REFERENCES locations(location_id) ON DELETE CASCADE,
  quantity NUMERIC(14,4) NOT NULL DEFAULT 0,
  last_updated TIMESTAMP WITH TIME ZONE DEFAULT now(),
  PRIMARY KEY (product_id, location_id)
);

-- Transactions actúan como ledger (event sourcing). No se debe modificar el pasado.
CREATE TYPE tx_type AS ENUM ('purchase','sale','adjustment','transfer_out','transfer_in');

CREATE TABLE transactions (
  tx_id BIGSERIAL PRIMARY KEY,
  tx_type tx_type NOT NULL,
  product_id INT NOT NULL REFERENCES products(product_id),
  location_id INT, -- ubicación afectada (nullable para movimientos de tipo global)
  quantity NUMERIC(14,4) NOT NULL, -- cantidad positiva; su signo se interpreta por tx_type
  unit_cost NUMERIC(12,4), -- opcional, útil para valoración
  reference TEXT, -- PO, invoice, nota
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

-- Índices útiles
CREATE INDEX idx_transactions_product ON transactions(product_id);
CREATE INDEX idx_transactions_created_at ON transactions(created_at);

Por qué: Separamos eventos (transactions) de la proyección (stock). Esto permite auditar todo lo ocurrido y recalcular stock si detectas inconsistencias. Mantener stock como tabla actualizada optimiza lecturas frecuentes (fast reads) sin recomputar el sumatorio cada vez.

2) functions.sql — funciones atómicas para mutar inventario

-- functions.sql
-- Función helper para aplicar una transacción de inventario de forma atómica.
-- La función inserta un registro en transactions y ajusta (o crea) la fila en stock.

CREATE OR REPLACE FUNCTION add_inventory_transaction(
  p_tx_type tx_type,
  p_product_id INT,
  p_location_id INT,
  p_quantity NUMERIC,
  p_unit_cost NUMERIC DEFAULT NULL,
  p_reference TEXT DEFAULT NULL
) RETURNS BIGINT
LANGUAGE plpgsql
AS $$
DECLARE
  v_signed_qty NUMERIC := p_quantity; -- interpretamos cantidad siempre positiva
  v_tx_id BIGINT;
BEGIN
  -- Validaciones básicas
  IF p_quantity <= 0 THEN
    RAISE EXCEPTION 'quantity must be > 0';
  END IF;

  -- convertir tipo en signo (sales restan, purchases suman)
  IF p_tx_type = 'sale' OR p_tx_type = 'transfer_out' THEN
    v_signed_qty := -1 * p_quantity;
  END IF;

  -- Inserta en ledger
  INSERT INTO transactions(tx_type, product_id, location_id, quantity, unit_cost, reference)
  VALUES (p_tx_type, p_product_id, p_location_id, p_quantity, p_unit_cost, p_reference)
  RETURNING tx_id INTO v_tx_id;

  -- Actualiza la proyección de stock
  LOOP
    -- Intentamos actualizar fila existente
    UPDATE stock
    SET quantity = quantity + v_signed_qty,
        last_updated = now()
    WHERE product_id = p_product_id AND (location_id IS NOT DISTINCT FROM p_location_id);

    IF FOUND THEN
      -- Chequeo: no permitir stock negativo si ese es el requisito
      PERFORM 1 FROM stock WHERE product_id = p_product_id AND (location_id IS NOT DISTINCT FROM p_location_id) AND quantity < 0;
      IF FOUND THEN
        RAISE EXCEPTION 'stock negative for product % at location %', p_product_id, p_location_id;
      END IF;
      RETURN v_tx_id;
    END IF;

    -- No había fila: intentamos insertar
    BEGIN
      INSERT INTO stock(product_id, location_id, quantity, last_updated)
      VALUES (p_product_id, p_location_id, GREATEST(v_signed_qty, 0), now());
      -- Si v_signed_qty era negativo, insertamos 0 y la actualización anterior fallaría; evitamos permitir crear stock negativo
      IF v_signed_qty < 0 THEN
        RAISE EXCEPTION 'cannot create negative stock for product % at location %', p_product_id, p_location_id;
      END IF;
      RETURN v_tx_id;
    EXCEPTION WHEN unique_violation THEN
      -- otra sesión pudo haber creado la fila antes de nuestro INSERT; reintentamos
      CONTINUE;
    END;
  END LOOP;
END;
$$;

-- Otorgar permisos (ejemplo): solo ejecutar la función en lugar de permitir inserts directos
-- GRANT EXECUTE ON FUNCTION add_inventory_transaction(tx_type,int,int,numeric,numeric,text) TO inventory_app_user;

Por qué: La función encapsula la lógica de negocio: inserta el evento y actualiza la proyección de stock dentro de una misma transacción. Evita triggers implícitos que pueden ocultar el flujo y dificulta el control de errores. El patrón "ledger + projection" facilita auditoría y reconciliación.

3) sample_data.sql — datos de ejemplo

-- sample_data.sql
INSERT INTO products(sku, name, unit_cost) VALUES
('SKU-001','Tornillo 3mm', 0.05),
('SKU-002','Tuerca 3mm', 0.03),
('SKU-003','Arandela', 0.01);

INSERT INTO locations(code, description) VALUES
('WH-1','Almacén Central'),
('WH-2','Sucursal Norte');

-- Añadimos compras (purchases) para poblar stock
SELECT add_inventory_transaction('purchase', 1, 1, 1000, 0.05, 'PO-1001');
SELECT add_inventory_transaction('purchase', 2, 1, 2000, 0.03, 'PO-1002');
SELECT add_inventory_transaction('purchase', 3, 2, 500, 0.01, 'PO-1003');

-- Venta
SELECT add_inventory_transaction('sale', 1, 1, 120, NULL, 'SO-2001');

-- Ajuste
SELECT add_inventory_transaction('adjustment', 2, 1, 10, NULL, 'stock-take-2026-02');

4) queries.sql — reportes y consultas útiles

-- queries.sql
-- 1) Stock actual por producto y ubicación
SELECT p.sku, p.name, l.code AS location, s.quantity, p.unit_cost,
       (s.quantity * p.unit_cost)::numeric(18,4) AS stock_value
FROM stock s
JOIN products p USING(product_id)
JOIN locations l USING(location_id)
ORDER BY p.sku, l.code;

-- 2) Productos por debajo de un umbral (alerta de bajo stock)
SELECT p.sku, p.name, s.quantity
FROM stock s
JOIN products p USING(product_id)
WHERE s.quantity < 50
ORDER BY s.quantity;

-- 3) Movimientos recientes (ledger)
SELECT t.tx_id, t.created_at, t.tx_type, p.sku, p.name, l.code AS location, t.quantity, t.reference
FROM transactions t
LEFT JOIN products p ON p.product_id = t.product_id
LEFT JOIN locations l ON l.location_id = t.location_id
ORDER BY t.created_at DESC
LIMIT 100;

-- 4) Valoración total del inventario
SELECT SUM(s.quantity * p.unit_cost) AS total_inventory_value
FROM stock s
JOIN products p USING(product_id);

-- 5) Top productos vendidos por cantidad (agregando transactions)
SELECT p.sku, p.name, SUM(t.quantity) AS total_sold
FROM transactions t
JOIN products p ON p.product_id = t.product_id
WHERE t.tx_type = 'sale'
GROUP BY p.sku, p.name
ORDER BY total_sold DESC
LIMIT 20;

Opcional: triggers.sql — alternativa con trigger

-- triggers.sql
-- Si prefieres que cualquier INSERT en transactions actualice stock automáticamente,
-- puedes usar un trigger AFTER INSERT que invoque la misma lógica.

CREATE OR REPLACE FUNCTION trg_transactions_after_insert()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  -- Aquí podrías llamar a add_inventory_transaction pero ojo con recursión.
  -- Alternativamente, replicar la lógica de ajuste sobre stock de forma segura.

  IF NEW.tx_type = 'sale' OR NEW.tx_type = 'transfer_out' THEN
    UPDATE stock
    SET quantity = quantity - NEW.quantity, last_updated = now()
    WHERE product_id = NEW.product_id AND (location_id IS NOT DISTINCT FROM NEW.location_id);
  ELSE
    UPDATE stock
    SET quantity = quantity + NEW.quantity, last_updated = now()
    WHERE product_id = NEW.product_id AND (location_id IS NOT DISTINCT FROM NEW.location_id);
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_after_insert_transactions
AFTER INSERT ON transactions
FOR EACH ROW EXECUTE FUNCTION trg_transactions_after_insert();

Por qué no siempre recomiendo triggers: los triggers hacen que las mutaciones sean implícitas. Para auditoría y trazabilidad prefiero funciones explícitas que el código de la aplicación invoque; así controlas errores y autorizaciones. Si tu equipo prefiere "escribir en ledger y dejar que la DB gestione la proyección", un trigger es aceptable, pero documenta bien y cubre casos de fallo.

Optimización y buenas prácticas

• Indices: además de los básicos, considera índices compuestos si consultas por producto+fecha.
• Partitioning: si transactions crece mucho, particiona por rango de fecha para archivar rápido.
• Vaciar o refrescar materialized views periódicamente para reportes pesados.
• Control de concurrencia: la función usa bucles para evitar race conditions al insertar stock; puedes usar SELECT ... FOR UPDATE en escenarios más complejos.
• Seguridad: crea roles con permisos mínimos. Concede EXECUTE sobre funciones en lugar de INSERT/UPDATE directos en tablas críticas.

Pruebas y reconciliación

Para verificar consistencia:

-- Recomputar stock a partir del ledger (reconciliación completa)
WITH recomputed AS (
  SELECT product_id, location_id, 
         SUM(CASE WHEN tx_type = 'sale' OR tx_type = 'transfer_out' THEN -quantity ELSE quantity END) AS qty
  FROM transactions
  GROUP BY product_id, location_id
)
-- Comparar con tabla stock
SELECT r.product_id, r.location_id, r.qty AS recomputed, s.quantity AS projected
FROM recomputed r
LEFT JOIN stock s USING (product_id, location_id)
WHERE COALESCE(r.qty,0) <> COALESCE(s.quantity,0);

Si hay diferencias, puedes investigar el ledger y los errores en funciones/trigger que provocaron la divergencia.

Para finalizar: considera auditar quién ejecuta cambios críticos. Para auditoría avanzada, integra la extensión pgaudit o registra el usuario y la sesión en cada transacción.

Siguiente paso avanzado: integra este backend con una cola (ej. Kafka) o un CDC (logical replication) para propagar los eventos de transactions a otros sistemas (ERP, BI) en tiempo casi real. Además, inspecciona el coste de las consultas con EXPLAIN ANALYZE y perfila índices según patrones reales de acceso.