Proyecto práctico: sistema de tareas con PostgreSQL — esquema, consultas avanzadas y rendimiento

Este proyecto te guía para diseñar una base de datos de tareas (todo app) en PostgreSQL con buen diseño, consultas útiles, optimizaciones y scripts de mantenimiento. Enfócate en el SQL: esquema, índices, upserts, full-text search, materialized views, particionado y auditoría.

Requisitos previos

• PostgreSQL 12+ (preferible 13+).
• psql o cliente equivalente.
• Opcional: Node.js 16+ para ejemplos de ejecución desde app.
• Conceptos básicos: transacciones, índices, constraints, joins.

Estructura de carpetas

project-sql-tasks/
├─ migrations/
│  ├─ 01_schema.sql
│  ├─ 02_indexes.sql
│  └─ 03_triggers_and_partitions.sql
├─ seeds/
│  └─ seeds.sql
├─ queries/
│  └─ queries.sql
├─ scripts/
│  ├─ run_migrations.sh
│  └─ refresh_materialized_views.sql
└─ examples/
   └─ upsert_task.js    (opcional: ejemplo Node.js)

Archivo principal: migrations/01_schema.sql

-- Crear extensiones útiles
CREATE EXTENSION IF NOT EXISTS pg_trgm;    -- para búsqueda por trigramas
CREATE EXTENSION IF NOT EXISTS unaccent;   -- normalizar texto

-- Usuarios
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL UNIQUE,
  name TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

-- Tareas
CREATE TABLE tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  description TEXT,
  status TEXT NOT NULL DEFAULT 'pending', -- pending, in_progress, done
  due_at TIMESTAMP WITH TIME ZONE,
  priority SMALLINT DEFAULT 3, -- 1 high .. 5 low
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

-- Tags (normalizado)
CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

-- Many-to-many entre tasks y tags
CREATE TABLE task_tags (
  task_id UUID REFERENCES tasks(id) ON DELETE CASCADE,
  tag_id INT REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (task_id, tag_id)
);

-- Log de auditoría (append-only)
CREATE TABLE audit_logs (
  id BIGSERIAL PRIMARY KEY,
  table_name TEXT NOT NULL,
  record_id TEXT,
  action TEXT NOT NULL, -- INSERT, UPDATE, DELETE
  payload JSONB,
  occurred_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

Índices y optimización: migrations/02_indexes.sql

-- Índices para consultas frecuentes
CREATE INDEX ON tasks (user_id, status, priority);
CREATE INDEX ON tasks (due_at);

-- GIN para full-text search sobre title + description
ALTER TABLE tasks ADD COLUMN tsv tsvector;

-- Llenar la columna tsv con configuración en español y sin acentos
UPDATE tasks SET tsv = to_tsvector('spanish', coalesce(unaccent(title),'') || ' ' || coalesce(unaccent(description),''));

-- Trigger actualizar tsv (lo definimos en triggers)
CREATE INDEX tasks_tsv_idx ON tasks USING GIN (tsv);

-- Trigramas para búsquedas tipo LIKE
CREATE INDEX tasks_title_trgm ON tasks USING GIN (title gin_trgm_ops);

-- Índice para audit_logs particionado (se creará por partición)

Triggers y particionado: migrations/03_triggers_and_partitions.sql

-- Función para mantener updated_at y tsv
CREATE OR REPLACE FUNCTION tasks_updated_at_tsv_trigger()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  NEW.tsv = to_tsvector('spanish', coalesce(unaccent(NEW.title), '') || ' ' || coalesce(unaccent(NEW.description), ''));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tasks_updated_at_tsv
BEFORE INSERT OR UPDATE ON tasks
FOR EACH ROW EXECUTE FUNCTION tasks_updated_at_tsv_trigger();

-- Función de auditoría (simple): insertar un registro en audit_logs
CREATE OR REPLACE FUNCTION audit_changes()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM pg_notify('audit', json_build_object('table', TG_TABLE_NAME, 'action', TG_OP, 'id', NEW.id)::text);
  INSERT INTO audit_logs(table_name, record_id, action, payload)
  VALUES (TG_TABLE_NAME, COALESCE(NEW.id::text, OLD.id::text), TG_OP, row_to_json(COALESCE(NEW, OLD)));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Asociar auditoría a tablas relevantes
CREATE TRIGGER tasks_audit AFTER INSERT OR UPDATE OR DELETE ON tasks
FOR EACH ROW EXECUTE FUNCTION audit_changes();

-- Particionar audit_logs por mes para contener crecimiento
-- (Postgres declarative partitioning)
ALTER TABLE audit_logs
  PARTITION BY RANGE (occurred_at);

-- Crear particiones para los próximos meses (ejemplo)
CREATE TABLE audit_logs_2026_01 PARTITION OF audit_logs
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE audit_logs_2026_02 PARTITION OF audit_logs
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- NOTA: crea particiones periódicamente con un job

Datos de ejemplo: seeds/seeds.sql

-- Users
INSERT INTO users (id, email, name) VALUES
  ('11111111-1111-1111-1111-111111111111', 'alice@example.com', 'Alice'),
  ('22222222-2222-2222-2222-222222222222', 'bob@example.com', 'Bob');

-- Tags
INSERT INTO tags (name) VALUES ('work'), ('personal'), ('urgent');

-- Tasks
INSERT INTO tasks (user_id, title, description, status, due_at, priority) VALUES
  ('11111111-1111-1111-1111-111111111111', 'Enviar informe', 'Enviar el informe mensual al equipo', 'pending', now() + interval '2 days', 2),
  ('22222222-2222-2222-2222-222222222222', 'Comprar regalo', 'Comprar regalo de cumpleaños', 'pending', now() + interval '7 days', 4);

-- Asociar tags (ejemplo simplificado seleccionando ids)
INSERT INTO task_tags (task_id, tag_id)
  SELECT t.id, tg.id FROM tasks t JOIN tags tg ON tg.name = 'work' WHERE t.title ILIKE '%informe%';

Consultas útiles: queries/queries.sql

-- 1) Paginación con total (eficiente): keyset pagination recomendado, pero aquí un ejemplo con window
-- Página 1: 10 elementos ordenados por created_at desc
SELECT * FROM (
  SELECT t.*, count(*) OVER() AS total_count
  FROM tasks t
  WHERE t.user_id = '11111111-1111-1111-1111-111111111111'
  ORDER BY created_at DESC
  LIMIT 10
) s;

-- 2) Upsert (crear o actualizar tarea)
INSERT INTO tasks (id, user_id, title, description, status, due_at, priority)
VALUES ($1, $2, $3, $4, $5, $6, $7)
ON CONFLICT (id) DO UPDATE SET
  title = EXCLUDED.title,
  description = EXCLUDED.description,
  status = EXCLUDED.status,
  due_at = EXCLUDED.due_at,
  priority = EXCLUDED.priority,
  updated_at = now();

-- 3) Full-text search con ranking
SELECT t.*, ts_rank_cd(t.tsv, plainto_tsquery('spanish', unaccent($1))) AS rank
FROM tasks t
WHERE t.tsv @@ plainto_tsquery('spanish', unaccent($1))
ORDER BY rank DESC, priority ASC
LIMIT 20;

-- 4) Búsqueda tipo fuzzy usando trigramas
SELECT * FROM tasks WHERE similarity(title, $1) > 0.3 ORDER BY similarity(title, $1) DESC LIMIT 10;

-- 5) Materialized view para estadísticas por usuario
CREATE MATERIALIZED VIEW IF NOT EXISTS user_task_stats AS
SELECT
  user_id,
  count(*) FILTER (WHERE status = 'pending') AS pending_count,
  count(*) FILTER (WHERE status = 'done') AS done_count,
  avg(priority) AS avg_priority
FROM tasks
GROUP BY user_id;

-- REFRESH MATERIALIZED VIEW CONCURRENTLY user_task_stats;

-- 6) Transacción segura para asignar tag a tarea (evitar duplicados en race)
BEGIN;
  -- opcional: lock granularity
  -- LOCK TABLE task_tags IN SHARE ROW EXCLUSIVE MODE;
  INSERT INTO task_tags (task_id, tag_id)
  VALUES ($1, $2)
  ON CONFLICT DO NOTHING;
COMMIT;

-- 7) Consulta de tareas vencidas
SELECT * FROM tasks WHERE due_at IS NOT NULL AND due_at < now() AND status != 'done' ORDER BY due_at ASC;

Script de migraciones simple: scripts/run_migrations.sh

#!/usr/bin/env bash
set -euo pipefail
PSQL="psql $PG_CONN"  # exporta PG_CONN="postgresql://user:pass@host:port/db"
for f in migrations/*.sql; do
  echo "Applying $f"
  $PSQL -f "$f"
done

Ejemplo Node.js: examples/upsert_task.js (opcional)

const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.PG_CONN });

async function upsertTask(task) {
  const sql = `INSERT INTO tasks (id, user_id, title, description, status, due_at, priority)
    VALUES ($1,$2,$3,$4,$5,$6,$7)
    ON CONFLICT (id) DO UPDATE SET
      title = EXCLUDED.title,
      description = EXCLUDED.description,
      status = EXCLUDED.status,
      due_at = EXCLUDED.due_at,
      priority = EXCLUDED.priority,
      updated_at = now()
    RETURNING *`;
  const vals = [task.id, task.user_id, task.title, task.description, task.status, task.due_at, task.priority];
  const { rows } = await pool.query(sql, vals);
  return rows[0];
}

(async () => {
  const t = await upsertTask({
    id: 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
    user_id: '11111111-1111-1111-1111-111111111111',
    title: 'Prueba upsert',
    description: 'Descripción',
    status: 'pending',
    due_at: null,
    priority: 3
  });
  console.log(t);
  await pool.end();
})();

Por qué estas decisiones

• Normalizar tags: evita duplicación y facilita agregados y filtros.
• tsvector y trigram: combinan búsquedas full-text (relevancia) y fuzzy (nombre parecido).
• ON CONFLICT (upsert): evita condiciones de carrera en creación/actualización desde API.
• Triggers para tsv y updated_at: delegar consistencia al motor evita errores en la capa app.
• Audit logs particionados: el log crece rápido; particionar por rango (mes) hace mantenimiento y borrado eficiente.
• Materialized views: precomputar agregados costosos y refrescarlos periódicamente para dashboards.
• Índices específicos (GIN, trgm, compuesto): reducen scans y aceleran las consultas más comunes.

Mantenimiento y operaciones

• Vacuum/Analyze automático: configurar autovacuum y revisar tablas grandes.
• Refrescar materialized views con CONCURRENTLY si la vista y la DB lo permiten.
• Crear job para añadir particiones periódicamente y para archivar/borrar particiones antiguas.
• Monitoreo: habilita pg_stat_statements y revisa queries lentas, usa EXPLAIN (ANALYZE, BUFFERS) para tuning.

Buenas prácticas de seguridad

• No expongas credenciales en código: usa variables de entorno o un vault.
• Principio de menor privilegio: usa roles con permisos limitados para la aplicación (no superuser).
• Siempre parametriza queries desde la app para evitar SQL injection.

Siguiente paso: automatiza estas migraciones en CI/CD, añade tests de integración (restaurar DB en contenedor, ejecutar migrations, comprobar constraints) y monta métricas básicas (latencia por query, tamaño por tabla). Un consejo avanzado: usa pg_repack o particiones para evitar bloat en tablas grandes y revisa los planos de las consultas con EXPLAIN ANALYZE antes de crear índices (un índice mal pensado puede empeorar el rendimiento).