Base de datos

BCN Extractor usa PostgreSQL 15+ como base de datos principal. El esquema está diseñado para soportar búsqueda full-text en español, consultas JSONB y detección eficiente de cambios.

Tablas

tipos_normas — Catálogo de tipos

Tabla de referencia con los tipos de normas que maneja la BCN (Decreto, Ley, Resolución, etc.).

CREATE TABLE tipos_normas (
  id                  SERIAL PRIMARY KEY,
  nombre              TEXT NOT NULL UNIQUE,
  abreviatura         TEXT,
  fecha_creacion      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

---

instituciones — Instituciones de la BCN

Almacena las instituciones públicas cuyos cuerpos normativos están en la BCN.

CREATE TABLE instituciones (
  id                INTEGER PRIMARY KEY,  -- ID propio de la BCN
  nombre            TEXT NOT NULL,
  fecha_agregada    TIMESTAMP,
  fecha_actualizada TIMESTAMP
);

Note

El id es el mismo que usa la BCN en sus servicios web, no un autoincremental propio. Esto simplifica la sincronización y evita duplicados.

---

normas — Tabla central

La tabla principal del sistema. Almacena metadatos, contenido y referencias a los archivos en disco.

CREATE TABLE normas (
  id                  INTEGER PRIMARY KEY,     -- ID propio de la BCN
  id_tipo             INTEGER REFERENCES tipos_normas(id),
  numero              VARCHAR(50),
  titulo              TEXT,
  estado              VARCHAR(20) DEFAULT 'vigente',  -- 'vigente' | 'derogada'
  fecha_publicacion   DATE,
  fecha_promulgacion  DATE,
  organismo           TEXT,
  xml_path            TEXT,                    -- ruta al XML en disco
  md_path             TEXT,                    -- ruta al Markdown en disco
  contenido_texto     TEXT,                    -- texto completo para FTS
  metadata_json       JSONB,                   -- materias, organismos, flags
  hash_xml            VARCHAR(32),             -- MD5 para detectar cambios
  fecha_descarga      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  fecha_actualizacion TIMESTAMP
);

---

normas_instituciones — Relación muchos-a-muchos

Una norma puede pertenecer a múltiples instituciones y una institución puede tener múltiples normas.

CREATE TABLE normas_instituciones (
  id_norma        INTEGER REFERENCES normas(id),
  id_institucion  INTEGER REFERENCES instituciones(id),
  fecha_asociacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id_norma, id_institucion)
);

---

descargas — Log de operaciones

Registra cada intento de descarga o sincronización para trazabilidad y debugging.

CREATE TABLE descargas (
  id              SERIAL PRIMARY KEY,
  id_norma        INTEGER REFERENCES normas(id),
  tipo_descarga   VARCHAR(50),   -- 'descarga' | 'sincronizacion'
  estado          VARCHAR(50),   -- 'exitosa' | 'error'
  fecha_intento   TIMESTAMP,
  error_mensaje   TEXT
);

---

Índices y optimizaciones

Full-text search en español

-- Índice GIN sobre el campo titulo para FTS en español
CREATE INDEX idx_normas_titulo_fts
ON normas
USING GIN (to_tsvector('spanish', titulo));

-- Consulta de ejemplo
SELECT id, titulo
FROM normas
WHERE to_tsvector('spanish', titulo) @@ to_tsquery('spanish', 'medioambiente')
ORDER BY ts_rank(to_tsvector('spanish', titulo), to_tsquery('spanish', 'medioambiente')) DESC;

Índices B-tree

CREATE INDEX idx_normas_id_tipo   ON normas(id_tipo);
CREATE INDEX idx_normas_estado    ON normas(estado);
CREATE INDEX idx_tipos_nombre     ON tipos_normas(nombre);

Índice GIN sobre JSONB

-- Para consultas por claves dentro de metadata_json
CREATE INDEX idx_normas_metadata ON normas USING GIN (metadata_json);

-- Consulta de ejemplo: normas sobre una materia específica
SELECT id, titulo FROM normas
WHERE metadata_json @> '{"materias": ["salud"]}';

Detección de cambios via MD5

El campo hash_xml almacena el MD5 del XML descargado. En cada sincronización:

1. Se descarga el XML de la BCN
2. Se calcula su MD5
3. Si el MD5 coincide con el almacenado → se omite el upsert
4. Si difiere → se actualiza la norma y su hash

Esto hace que las sincronizaciones incrementales sean muy rápidas en instituciones con pocas normas modificadas.

---

Consultas útiles

-- Estadísticas generales
SELECT
  (SELECT COUNT(*) FROM normas)         AS total_normas,
  (SELECT COUNT(*) FROM instituciones)  AS total_instituciones,
  (SELECT COUNT(*) FROM tipos_normas)   AS total_tipos,
  (SELECT COUNT(*) FROM normas WHERE estado = 'vigente')  AS vigentes,
  (SELECT COUNT(*) FROM normas WHERE estado = 'derogada') AS derogadas;

-- Normas por tipo
SELECT t.nombre, COUNT(n.id) AS total
FROM tipos_normas t
LEFT JOIN normas n ON n.id_tipo = t.id
GROUP BY t.nombre
ORDER BY total DESC;

-- Instituciones con más normas
SELECT i.nombre, COUNT(ni.id_norma) AS total_normas
FROM instituciones i
JOIN normas_instituciones ni ON ni.id_institucion = i.id
GROUP BY i.nombre
ORDER BY total_normas DESC
LIMIT 20;

-- Últimas normas descargadas
SELECT id, titulo, fecha_descarga
FROM normas
ORDER BY fecha_descarga DESC
LIMIT 10;

Conexión directa

Puedes conectarte con cualquier cliente PostgreSQL usando las credenciales de tu .env:

# psql
psql -h localhost -p 5432 -U postgres -d bcn_extractor

# Docker exec
docker exec -it bcn_extractor_db psql -U postgres -d bcn_extractor