Sistema de inventario de laboratorio: encuentre rápidamente la ubicación de los reactivos con SQL + índices + Express

Al finalizar este tema

Podrá crear su propio mini LIMS (Sistema de gestión de información de laboratorio) para administrar el inventario de su laboratorio como una aplicación web, combinando los conceptos de esquema SQL, índices de base de datos y servidor Express que aprendió en la escuela. Entenderá por qué la búsqueda se vuelve 5000 veces más rápida con solo agregar un índice y por qué un esquema relacional es más adecuado para la colaboración que Excel, todo a través del código.

Este artículo es un ejemplo didáctico. Los LIMS reales (Benchling, LabWare, etc.) tienen funciones mucho más amplias, pero el modelo de datos y los patrones de API en su núcleo son los mismos que aprenderá aquí.

"¿Dónde está el anticuerpo que compré ayer?" - Limitaciones de Excel

Supongamos que su laboratorio administra el inventario de la siguiente manera:

• Lista de reactivos: inventario_compartido.xlsx (Google Drive)
• Ubicación del refrigerador: en la memoria de cada persona
• Fecha de caducidad: debe verificarse directamente en la etiqueta
• Historial de pedidos: búsqueda en el correo electrónico

Problemas prácticos de este método:

Problema 1: Conflictos de edición simultánea. Si 5 personas abren la misma hoja de cálculo de Excel, las modificaciones de alguien pueden perderse. Google Sheets lo mejora, pero aún no es completamente seguro.

Problema 2: Velocidad de búsqueda. Encontrar 5000 reactivos en Excel con un filtro lleva unos segundos y es difícil obtener una coincidencia exacta. "Anticuerpo P53" y "anticuerpo anti-p53" se tratan como diferentes.

Problema 3: La representación de las relaciones es incómoda. Si un reactivo está dividido en varios refrigeradores y cada ubicación tiene un lote con una fecha de caducidad diferente, la representación en Excel requiere celdas combinadas complejas.

Problema 4: La automatización es imposible. Es difícil implementar lógica automatizada en Excel, como "notificar automáticamente los reactivos con 30 días de vida útil restantes" o "solicitar un pedido cuando se alcanza el stock mínimo".

El enfoque real es una base de datos relacional y una API web. Almacene el inventario en PostgreSQL y proporcione una API de búsqueda y modificación con un servidor web Express. El cliente puede ser una aplicación web, una CLI o un bot de Slack.

Desarmando la caja negra: explorando el LIMS

Hay cuatro componentes clave.

Componente 1: Diseño del esquema SQL

El principio de un esquema relacional es separar cada concepto en una tabla y representar las relaciones con claves externas. Cuando se aplica al inventario del laboratorio:

CREATE TABLE reagents (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    catalog_number TEXT,
    vendor TEXT,
    cas_number TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE storage_locations (
    id SERIAL PRIMARY KEY,
    room TEXT NOT NULL,
    unit TEXT NOT NULL,        -- Ejemplo: "Refrigerador A", "Congelador -80 2"
    shelf TEXT,
    temperature_c INTEGER
);

CREATE TABLE inventory_lots (
    id SERIAL PRIMARY KEY,
    reagent_id INTEGER REFERENCES reagents(id) ON DELETE CASCADE,
    location_id INTEGER REFERENCES storage_locations(id),
    lot_number TEXT,
    quantity NUMERIC NOT NULL,
    unit TEXT NOT NULL,        -- "mL", "μg", "vial"
    expiration_date DATE,
    received_date DATE DEFAULT CURRENT_DATE,
    is_opened BOOLEAN DEFAULT FALSE,
    notes TEXT
);

Este esquema de 3 tablas tiene la ventaja de representar de forma natural la situación en la que un reactivo se encuentra en varios lotes y en varias ubicaciones.

Parte 2: Índices

Un índice es una estructura de datos que permite realizar búsquedas rápidas en columnas específicas. Por defecto, se utilizan los índices de árbol B.

Búsqueda sin índice:

SELECT * FROM reagents WHERE name = 'anti-p53';

Si esta consulta se ejecuta en una tabla de 5000 filas, se realizará un escaneo completo (escaneo secuencial). En promedio, se leerán 2500 filas y se verificará la coincidencia. El tiempo que tarda = de unos pocos milisegundos a decenas de milisegundos.

Agregar un índice:

CREATE INDEX idx_reagents_name ON reagents(name);

La misma consulta ahora realiza un escaneo de índice. La búsqueda en árbol B tiene una complejidad de tiempo de O(log n). En 5000 filas, encuentra la ubicación exacta en 12 pasos o menos. El tiempo que tarda es de microsegundos.

En 5000 filas, la diferencia no es grande, pero con 50 millones de filas, un escaneo completo tardaría varios segundos, mientras que un escaneo de índice seguiría tardando menos de un milisegundo.

Los índices para la búsqueda de coincidencias parciales son diferentes:

CREATE INDEX idx_reagents_name_trgm ON reagents USING GIN (name gin_trgm_ops);

Este es un índice trigrama que utiliza la extensión pg_trgm, lo que permite encontrar rápidamente coincidencias parciales como LIKE '%p53%'.

Índice para consultar elementos próximos a su fecha de vencimiento:

CREATE INDEX idx_lots_expiration ON inventory_lots(expiration_date)
  WHERE expiration_date IS NOT NULL;

WHERE Un índice con una cláusula WHERE es un índice parcial, ya que solo las filas que cumplen la condición se incluyen en el índice, lo que resulta en un tamaño de índice más pequeño.

Componente 3: Estructura básica del servidor Express

import express from "express";
import pg from "pg";

const app = express();
const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL
});

app.use(express.json());

app.get("/api/reagents", async (req, res) => {
  const { search } = req.query;

  let query = "SELECT * FROM reagents";
  const params = [];

  if (search) {
    query += " WHERE name ILIKE $1 OR catalog_number ILIKE $1";
    params.push(`%${search}%`);
  }

  query += " ORDER BY name LIMIT 100";

  const result = await pool.query(query, params);
  res.json({ reagents: result.rows });
});

app.get("/api/reagents/:id/lots", async (req, res) => {
  const { id } = req.params;
  const result = await pool.query(
    `SELECT il.*, sl.room, sl.unit, sl.shelf
       FROM inventory_lots il
       JOIN storage_locations sl ON il.location_id = sl.id
      WHERE il.reagent_id = $1
      ORDER BY il.expiration_date NULLS LAST`,
    [id]
  );
  res.json({ lots: result.rows });
});

app.post("/api/lots", async (req, res) => {
  const { reagent_id, location_id, lot_number, quantity, unit, expiration_date } = req.body;

  const result = await pool.query(
    `INSERT INTO inventory_lots
       (reagent_id, location_id, lot_number, quantity, unit, expiration_date)
     VALUES ($1, $2, $3, $4, $5, $6)
     RETURNING *`,
    [reagent_id, location_id, lot_number, quantity, unit, expiration_date]
  );
  res.status(201).json({ lot: result.rows[0] });
});

app.listen(3000, () => console.log("LIMS server on http://localhost:3000"));

Parte 4: Consultas parametrizadas

El lector atento habrá notado que en el código anterior se utiliza $1 en lugar de ${search}. Esta es la práctica estándar para la prevención de la inyección SQL.

// Código peligroso
const query = `SELECT * FROM reagents WHERE name = '${userInput}'`;

// Código seguro
const query = "SELECT * FROM reagents WHERE name = $1";
const result = await pool.query(query, [userInput]);

En la segunda forma, $1 nunca puede formar parte de una consulta SQL. Incluso si el usuario introduce '; DROP TABLE reagents; --, esa cadena se tratará simplemente como un valor de cadena.

Combinemos las cuatro partes: lógica de búsqueda práctica

Ahora implementaremos un escenario práctico. Si buscamos "anticuerpo p53", queremos obtener de una sola vez la lista de reactivos relacionados, junto con el lote, la ubicación y la fecha de caducidad de cada reactivo.

app.get("/api/search", async (req, res) => {
  const { q } = req.query;

  if (!q || q.length < 2) {
    return res.json({ results: [] });
  }

  const query = `
    SELECT
      r.id, r.name, r.catalog_number, r.vendor,
      COALESCE(json_agg(
        json_build_object(
          'lot_id', il.id,
          'lot_number', il.lot_number,
          'quantity', il.quantity,
          'unit', il.unit,
          'expiration_date', il.expiration_date,
          'location', sl.room || ' / ' || sl.unit || ' / ' || COALESCE(sl.shelf, '')
        ) ORDER BY il.expiration_date NULLS LAST
      ) FILTER (WHERE il.id IS NOT NULL), '[]') AS lots
    FROM reagents r
    LEFT JOIN inventory_lots il ON il.reagent_id = r.id
    LEFT JOIN storage_locations sl ON il.location_id = sl.id
    WHERE r.name ILIKE $1 OR r.catalog_number ILIKE $1
    GROUP BY r.id
    ORDER BY r.name
    LIMIT 50
  `;

  const result = await pool.query(query, [`%${q}%`]);
  res.json({ results: result.rows });
});

Con una sola consulta, devuelve la lista de reactivos y los lotes de cada reactivo. Este patrón, que utiliza json_agg, es el estándar para la composición de las respuestas de la API REST.

Código en el frontend para llamar a esta API:

async function searchReagent(query) {
  const response = await fetch(`/api/search?q=${encodeURIComponent(query)}`);
  const data = await response.json();
  return data.results;
}

document.getElementById("search").addEventListener("input", async (e) => {
  const results = await searchReagent(e.target.value);
  renderResults(results);
});

Desvanecimiento — Los tres espacios en blanco que deben completar

Espacio en blanco 1: Notificación de vencimiento próximo

Endpoint que busca automáticamente los lotes que vencerán dentro de los próximos 30 días.

app.get("/api/lots/expiring", async (req, res) => {
  const { days = 30 } = req.query;

  const query = `
    -- TODO: devolver los lotes que cumplan las siguientes condiciones
    -- 1. expiration_date está dentro de los próximos :days días
    -- 2. unir el nombre del reactivo y la información de ubicación
    -- 3. ordenar por proximidad del vencimiento
  `;

  // TODO: ejecutar pool.query y devolver los resultados
});

Pista:

WHERE expiration_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '1 day' * $1

Si esta consulta se ejecuta con frecuencia, el índice idx_lots_expiration que creó anteriormente será eficaz.

Espacio en blanco 2: transacción para reducir el inventario

Cuando se utiliza un reactivo, se disminuye la cantidad del lote y, si llega a ser menor o igual que 0, se marca automáticamente como agotado. Estos dos pasos deben tener éxito o fallar simultáneamente.

app.post("/api/lots/:id/consume", async (req, res) => {
  const { id } = req.params;
  const { amount } = req.body;

  const client = await pool.connect();
  try {
    await client.query("BEGIN");

    // TODO 1: consultar la cantidad actual con SELECT ... FOR UPDATE (bloqueo de fila)
    // TODO 2: comprobar que la cantidad sea mayor o igual que amount; si no, lanzar un error
    // TODO 3: reducir la cantidad con UPDATE
    // TODO 4: si la cantidad es 0, establecer is_opened en true (o usar otra columna de estado)

    await client.query("COMMIT");
    // Devolver el resultado
  } catch (e) {
    await client.query("ROLLBACK");
    res.status(400).json({ error: e.message });
  } finally {
    client.release();
  }
});

Pista: FOR UPDATE bloquea una fila para que ninguna otra transacción la modifique hasta que la transacción se complete.

Espacio en blanco 3: Índice para optimizar las búsquedas

ILIKE '%anything%' no se acelera con el índice B-tree predeterminado. Active el índice trigrama y mida el rendimiento.

-- TODO 1: activar la extensión pg_trgm
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- TODO 2: crear un índice de trigramas para el nombre de reagents
CREATE INDEX idx_reagents_name_trgm ON reagents USING GIN (name gin_trgm_ops);

Verificación del rendimiento:

-- Comparar antes y después de aplicar el índice
EXPLAIN ANALYZE
SELECT * FROM reagents WHERE name ILIKE '%p53%';

Objetivo: Respuesta inferior a 10 ms con más de 10.000 filas.

Reflexión: ¿Cómo difiere este LIMS de un sistema de producción?

Registro de auditoría: Un LIMS de producción registra todos los cambios de datos. Realiza un seguimiento de quién, cuándo y cuánto utilizó. Esencial para el cumplimiento de GLP (Buenas prácticas de laboratorio)/GMP. Método: ampliar pgaudit o utilizar las columnas updated_by/updated_at y los activadores.

Escaneo de códigos de barras/QR: En un sistema de producción, cada lote tiene un código de barras y se escanea para obtener información instantánea. Agregar un punto final que imprima los ID de lote como códigos QR aumentaría significativamente la utilidad de su sistema.

Administración de permisos: En un sistema de producción, los permisos de acceso se dividen por roles de usuario. Por ejemplo, los estudiantes solo pueden ver, los investigadores posdoctorales pueden registrar y modificar, y los investigadores principales pueden aprobar pedidos. Enfoque estándar: rol authenticator del esquema pg + validación de token JWT.

Interfaz de usuario web: Lo que ha creado es solo un servidor API. Un sistema de producción utiliza una SPA como React/Vue para proporcionar una experiencia de usuario similar a la de una aplicación de escritorio. Alternativamente, puede agregar rápidamente una interfaz de usuario simple con un marco de Python como Streamlit/Dash.

Sincronización y móvil: Un sistema de producción puede requerir edición sin conexión y sincronización, y soporte para aplicaciones móviles. Las pilas como Supabase o PouchDB son adecuadas para esto.

Proyectos de expansión

1. Integración de bot de Slack: Al escribir /reagent p53 en Slack, se llamará a su API y los resultados se mostrarán en el canal.

2. Flujo de trabajo de pedidos: Genere automáticamente una solicitud de pedido cuando el inventario caiga por debajo del nivel mínimo. Almacene la información del proveedor y genere automáticamente un PDF del pedido.

3. Estadísticas de uso: Cree un panel que muestre qué reactivos y cuánto se consumieron cada mes. Utilícelo para la planificación presupuestaria.

4. Integración con el cuaderno de laboratorio: Cuando registre un experimento, reste automáticamente los reactivos utilizados del inventario. Integración en el estilo de la API de Benchling.

Mapa de estos componentes

• [F] Diseño del esquema SQL: Normalización, claves externas, representación de relaciones. Modelo de inventario de 3 tablas.
• [F] Índices de base de datos: B-tree, índices parciales, índices GIN trigram. Verifique el rendimiento con EXPLAIN ANALYZE.
• [W] Servidor Express: Enrutamiento, middleware, análisis del cuerpo JSON.
• [W] Grupo de conexiones de base de datos: Reutilice las conexiones con pg.Pool.

[F] = Usted implementa / [W] = Concepto de herramienta proporcionada como código completo.