Proyecto práctico: Sistema de inventario con PostgreSQL — modelado, índices, funciones y triggers

Objetivo

Construir un esquema de base de datos en PostgreSQL para un sistema de inventario sencillo pero robusto: modelado relacional, scripts de migración, índices para rendimiento, funciones y triggers para integridad y operaciones atómicas. Incluye ejemplos de consultas útiles para reporting.

Requisitos previos

• PostgreSQL 12+ instalado y acceso a psql.
• Permisos para crear esquemas, tablas y extensiones (o pedir a DBA).
• Conocimientos básicos de SQL y transacciones.

Estructura de carpetas (sugerida)

inventory-sql/
  migrations/
    1_init.sql
    2_seed.sql
    3_indexes.sql
    4_functions_triggers.sql
    5_queries.sql
  README.md

Archivo 1: migrations/1_init.sql

-- crear esquema y tablas base
create schema if not exists inventory;

-- extensión para trigram search (opcional pero útil)
create extension if not exists pg_trgm;

create table if not exists inventory.products (
  id serial primary key,
  sku text not null unique,
  name text not null,
  description text,
  preferred_reorder_quantity integer default 0,
  min_stock integer default 0
);

create table if not exists inventory.warehouses (
  id serial primary key,
  name text not null unique,
  location text
);

-- movimientos de inventario: recibos, salidas, ajustes
create type inventory.movement_type as enum ('in','out','adjust');

create table if not exists inventory.stock_movements (
  id bigserial primary key,
  product_id integer not null references inventory.products(id) on delete restrict,
  warehouse_id integer not null references inventory.warehouses(id) on delete restrict,
  quantity numeric not null check (quantity <> 0),
  movement inventory.movement_type not null,
  unit_cost numeric(12,2), -- opcional para valoración
  note text,
  created_at timestamptz not null default now()
);

-- tabla denormalizada para stock actual por producto y bodega
create table if not exists inventory.product_stock (
  product_id integer references inventory.products(id) on delete cascade,
  warehouse_id integer references inventory.warehouses(id) on delete cascade,
  quantity numeric not null default 0,
  last_updated timestamptz default now(),
  constraint product_stock_pk primary key (product_id, warehouse_id)
);

Por qué así

• Separamos movimientos (audit trail) de la tabla de stock actual para permitir reconciliaciones y trazabilidad.
• El tipo movement_type fuerza valores válidos.
• La tabla denormalizada product_stock acelera consultas de stock en tiempo real evitando agregaciones constantes sobre movimientos.

Archivo 2: migrations/2_seed.sql

-- datos de ejemplo
insert into inventory.products (sku, name, description, preferred_reorder_quantity, min_stock)
values
  ('SKU-001','Tornillo 8mm','Tornillo métrico 8mm',100,10),
  ('SKU-002','Tuerca 8mm','Tuerca para tornillo 8mm',200,20),
  ('SKU-003','Arandela 8mm','Arandela plana 8mm',500,50)
on conflict (sku) do nothing;

insert into inventory.warehouses (name, location)
values
  ('Almacén Central','Madrid'),
  ('Almacén Norte','Bilbao')
on conflict (name) do nothing;

-- movimientos iniciales
insert into inventory.stock_movements (product_id, warehouse_id, quantity, movement, unit_cost)
values
  (1,1,100,'in',0.05),
  (2,1,200,'in',0.03),
  (3,2,500,'in',0.01);

-- inicializar tabla product_stock sumando movimientos actuales
truncate inventory.product_stock;

insert into inventory.product_stock (product_id, warehouse_id, quantity, last_updated)
select product_id, warehouse_id, coalesce(sum(quantity),0), max(created_at)
from inventory.stock_movements
group by product_id, warehouse_id;

Por qué así

• Los seed permiten probar consultas y validar triggers y funciones.
• La inicialización de product_stock se hace agregando movimientos existentes.

Archivo 3: migrations/3_indexes.sql

-- índices para consultas frecuentes
create index if not exists idx_stockmovements_product_created
  on inventory.stock_movements (product_id, created_at desc);

create index if not exists idx_stockmovements_warehouse_created
  on inventory.stock_movements (warehouse_id, created_at desc);

-- índice para consultas por producto/almacén (ya hay PK en product_stock)

-- índice trigram para búsqueda por nombre de producto
create index if not exists idx_products_name_trgm
  on inventory.products using gin (name gin_trgm_ops);

-- índice parcial para productos bajos de stock (según tabla denormalizada)
create index if not exists idx_product_stock_low
  on inventory.product_stock (warehouse_id)
  where quantity <= 50; -- ajuste según umbral típico

-- estadísticas de ayuda
analyze inventory.products;
analyze inventory.stock_movements;
analyze inventory.product_stock;

Por qué así

• Índices compuestos que acompañan las consultas por producto/fecha o almacén/fecha.
• Trigram para búsquedas textuales rápidas en name.
• Índice parcial para acelerar la detección de productos con stock bajo.

Archivo 4: migrations/4_functions_triggers.sql

-- función para aplicar un movimiento y mantener product_stock en una transacción
create or replace function inventory.apply_movement()
returns trigger language plpgsql as $$
begin
  -- aplicamos el movimiento: ajustamos la fila product_stock correspondiente
  if tg_op = 'INSERT' then
    perform 1 from inventory.product_stock
      where product_id = new.product_id and warehouse_id = new.warehouse_id;

    if found then
      update inventory.product_stock
      set quantity = quantity + new.quantity,
          last_updated = new.created_at
      where product_id = new.product_id and warehouse_id = new.warehouse_id;
    else
      insert into inventory.product_stock(product_id, warehouse_id, quantity, last_updated)
      values (new.product_id, new.warehouse_id, new.quantity, new.created_at);
    end if;
    return new;
  elsif tg_op = 'DELETE' then
    -- para simplicidad, no permitimos borrar movimientos históricos; si ocurre,
    -- recalculamos la fila afectada
    update inventory.product_stock ps
    set quantity = sub.sumq, last_updated = now()
    from (
      select product_id, warehouse_id, coalesce(sum(quantity),0) as sumq
      from inventory.stock_movements
      where product_id = old.product_id and warehouse_id = old.warehouse_id
      group by product_id, warehouse_id
    ) sub
    where ps.product_id = sub.product_id and ps.warehouse_id = sub.warehouse_id;
    return old;
  elsif tg_op = 'UPDATE' then
    -- en actualización, recomputar por seguridad
    update inventory.product_stock ps
    set quantity = sub.sumq, last_updated = now()
    from (
      select product_id, warehouse_id, coalesce(sum(quantity),0) as sumq
      from inventory.stock_movements
      where product_id in (old.product_id, new.product_id)
        and warehouse_id in (old.warehouse_id, new.warehouse_id)
      group by product_id, warehouse_id
    ) sub
    where ps.product_id = sub.product_id and ps.warehouse_id = sub.warehouse_id;
    return new;
  end if;
end;
$$;

-- trigger que llama a la función después de cada insert/update/delete en stock_movements
create trigger trg_apply_movement
after insert or update or delete on inventory.stock_movements
for each row execute function inventory.apply_movement();

Por qué así

• La función/trigger mantiene la tabla denormalizada en la misma transacción que inserta el movimiento, evitando inconsistencias.
• Usamos after para que el movimiento exista antes de actualizar el stock; se podría usar before si se quiere validar contra saldo actual.
• Para cargas masivas se podría deshabilitar el trigger y recomputar al final por rendimiento.

Archivo 5: migrations/5_queries.sql

-- 1) Stock actual por producto y almacén
select p.sku, p.name, w.name as warehouse, ps.quantity
from inventory.product_stock ps
join inventory.products p on p.id = ps.product_id
join inventory.warehouses w on w.id = ps.warehouse_id
order by p.sku, w.name;

-- 2) Movimientos recientes de un SKU
select sm.id, sm.created_at, sm.movement, sm.quantity, sm.unit_cost, sm.note, w.name
from inventory.stock_movements sm
join inventory.products p on p.id = sm.product_id
join inventory.warehouses w on w.id = sm.warehouse_id
where p.sku = 'SKU-001'
order by sm.created_at desc
limit 50;

-- 3) Productos por debajo del stock mínimo
select p.sku, p.name, sum(ps.quantity) as total_stock, p.min_stock
from inventory.product_stock ps
join inventory.products p on p.id = ps.product_id
group by p.sku, p.name, p.min_stock
having coalesce(sum(ps.quantity),0) <= p.min_stock
order by total_stock;

-- 4) Valoración simple por almacén (cantidad * último unit_cost aproximado)
-- nota: para valoración real hay metodologías (FIFO, LIFO, promedio ponderado)
with last_cost as (
  select distinct on (product_id, warehouse_id) product_id, warehouse_id, unit_cost
  from inventory.stock_movements
  where unit_cost is not null
  order by product_id, warehouse_id, created_at desc
)
select w.name as warehouse, sum(ps.quantity * coalesce(lc.unit_cost,0)) as total_value
from inventory.product_stock ps
left join last_cost lc on lc.product_id = ps.product_id and lc.warehouse_id = ps.warehouse_id
join inventory.warehouses w on w.id = ps.warehouse_id
group by w.name;

-- 5) Búsqueda de producto por nombre (trigram)
select id, sku, name from inventory.products
where name ilike '%tornillo%'
order by similarity(name,'tornillo') desc
limit 10;

Por qué estas consultas

• Cubren reporting típico: stock por almacén, historial por SKU, alertas de reaprovisionamiento y valoración.
• La valoración usa el último unit_cost por simplicidad; en producción decide la política de coste (FIFO/LIFO/prom. ponderado).

Cómo ejecutar

-- desde la línea de comandos (conéctate a la BD donde quieres crear el esquema)
psql -d tu_basedatos -f migrations/1_init.sql
psql -d tu_basedatos -f migrations/2_seed.sql
psql -d tu_basedatos -f migrations/3_indexes.sql
psql -d tu_basedatos -f migrations/4_functions_triggers.sql
-- después puedes probar las consultas
psql -d tu_basedatos -f migrations/5_queries.sql

Buenas prácticas y decisiones de diseño

• Mantener movimientos históricos permite auditoría y reconciliación; no borres movimientos salvo con proceso controlado.
• Denormalizar (product_stock) para lecturas rápidas es habitual; mantén la fuente de verdad en stock_movements.
• Para altas tasas de escritura considera batch inserts y deshabilitar triggers temporalmente, luego recomputar agregados.
• Piensa en particionado si la tabla stock_movements crece mucho (por fecha o por warehouse).
• Usa EXPLAIN (ANALYZE, BUFFERS) para investigar consultas lentas y ajustar índices.

Tip avanzado: si necesitas valoración FIFO exacta, implementa una tabla de lotes/entradas con cantidades residuales y consume lotes desde más antiguos hacia nuevos en una función que haga la salida. Eso mantiene trazabilidad de coste por salida y evita aproximaciones basadas en último coste.

Advertencia de seguridad: si este esquema se expone vía una API, evita construir SQL dinámico concatenando entradas de usuario; usa sentencias parametrizadas o funciones con parámetros para prevenir inyección SQL.

Siguiente paso sugerido: agrega un proceso de reconciliación periódica que compare product_stock con la agregación de stock_movements y genere alertas en caso de divergencia.