Esquema de Base de Datos

Vista General

El Observatorio del Congreso usa un esquema unificado Popolo-Graph almacenado en SQLite. Contiene 12 tablas organizadas en cuatro grupos funcionales:

Grupo	Tablas	Propósito
Entidades base	person, organization, area	Legisladores, partidos, divisiones geográficas
Actividad legislativa	motion, vote_event, vote, count	Iniciativas, sesiones de votación, votos individuales, recuentos
Relaciones estructurales	membership, post	Afiliaciones partidistas, cargos en comisiones
Redes de poder	relacion_poder, actor_externo, evento_politico	Vínculos de lealtad, actores externos, eventos políticos

Todas las tablas usan claves primarias de texto con una convención de prefijos (ej. P00001, VE_D00001). Las claves foráneas imponen integridad referencial con ON DELETE CASCADE donde aplica. Las restricciones CHECK validan enumeraciones y rangos a nivel de base de datos.

Diagrama Entidad-Relación

                        +-------------+
                        |    area     |
                        +-------------+
                        | id (PK)     |
                        | nombre      |
                        | parent_id FK|----+
                        +-------------+    |
                               ^          |
                               |          |
+---------------+    +------------------+  |  +------------------+
| organization  |    |      post        |  |  | actor_externo    |
+---------------+    +------------------+  |  +------------------+
| id (PK)       |<---| org_id (FK)      |  |  | id (PK)          |
| nombre        |    | area_id (FK)     |--+  | nombre           |
| abbr          |    +------------------+     | tipo             |
| clasificacion |                             | area_id (FK)     |--+
+------+--------+                             +------------------+  |
       |            +----------------+                              |
       |            |   membership   |                              |
       |            +----------------+                              |
       |            | id (PK)        |                              |
       +---org_id-->| person_id (FK) |-----+                        |
       |            | rol            |     |                        |
       |            | on_behalf_of FK|-----+                        |
       |            +----------------+     |                        |
       |                                   v                        |
       |                            +-------------+                 |
       |                            |    person   |                 |
       |                            +-------------+                 |
       |                            | id (PK)     |                 |
       |                            | nombre      |                 |
       |                            | genero      |                 |
       |                            +-------------+                 |
       |                                   |                        |
       |                                   v                        |
       |       +----------------+   +-------------+                 |
       |       |    vote_event  |   |    vote     |                 |
       |       +----------------+   +-------------+                 |
       +--org_id| id (PK)       |<--| vote_event  |                 |
               | motion_id (FK)|   | voter_id FK |                 |
               +-------+--------+   +-------------+                 |
                       |                                            |
                       v                                            |
                +-------------+       +------------------+          |
                |   motion    |       |   count          |          |
                +-------------+       +------------------+          |
                | id (PK)     |       | id (PK)          |          |
                | texto       |       | vote_event_id FK |----------+
                | clasificacion       | option           |          |
                +-------------+       | group_id (FK) ---+----------+
                                      +------------------+

        +-------------------+     +-------------------+
        | relacion_poder    |     | evento_politico   |
        +-------------------+     +-------------------+
        | id (PK)           |     | id (PK)           |
        | source_type       |     | fecha             |
        | source_id         |     | tipo              |
        | target_type       |     | descripcion       |
        | target_id         |     | motion_id (FK) ---+--> motion
        | tipo              |     +-------------------+
        | peso (1-5)        |
        +-------------------+
  source/target: person | organization | actor_externo

Convención de IDs

Todas las claves primarias usan un prefijo de texto para identificar el tipo de entidad y, donde aplica, la cámara de origen. La porción numérica se rellena con ceros a 5 dígitos.

Entidad	Prefijo	Ejemplo	Relleno
Vote Event (Diputados)	VE_D	VE_D00001	5 dígitos
Vote Event (Senado)	VE_S	VE_S00001	5 dígitos
Vote (Diputados)	V_D	V_D00001	5 dígitos
Vote (Senado)	V_S	V_S00001	5 dígitos
Motion (Diputados)	Y_D	Y_D00001	5 dígitos
Motion (Senado)	Y_S	Y_S00001	5 dígitos
Membership (Diputados)	M_D	M_D00001	5 dígitos
Membership (Senado)	M_S	M_S00001	5 dígitos
Person	P	P00001	5 dígitos
Count	C	C00001	5 dígitos

Nota

Los prefijos por cámara (_D para Diputados, _S para Senado) se usan en entidades que difieren entre cámaras. Las entidades compartidas como person y count usan un único prefijo.

Organizaciones Clave

Estas son las organizaciones politicas principales almacenadas en la tabla organization:

ID	Abreviatura	Nombre	Clasificación
O08	Diputados	Cámara de Diputados	gobierno
O09	Senado	Cámara de Senadores	gobierno
O11	MORENA	Movimiento de Regeneración Nacional	partido
O12	PAN	Partido Acción Nacional	partido
O13	PRI	Partido Revolucionario Institucional	partido
O14	PRD	Partido de la Revolución Democrática	partido
O15	PT	Partido del Trabajo	partido
O16	PVEM	Partido Verde Ecologista de México	partido
O17	MC	Movimiento Ciudadano	partido

Referencia por Tabla

area — Divisiones Geográficas

Almacena las divisiones geográficas usadas para distritos electorales y estados. Las áreas se pueden anidar mediante parent_id para modelar la jerarquía: distrito dentro de estado.

Columna	Tipo	Nulable	Descripción
id	TEXT	NO (PK)	Clave primaria, prefijo A01, A02…
nombre	TEXT	NO	Nombre del área
clasificacion	TEXT	NO	Clasificación: estado, distrito, circunscripcion
parent_id	TEXT	SÍ	Clave foránea a area(id). El distrito apunta a su estado padre
geometry	TEXT	SÍ	Geometría GeoJSON opcional

clasificacion IN ('estado', 'distrito', 'circunscripcion')

organization — Organizaciones Políticas

Almacena partidos políticos, cámaras legislativas, coaliciones e instituciones de gobierno.

Columna	Tipo	Nulable	Descripción
id	TEXT	NO (PK)	Clave primaria, prefijo O01, O02…
nombre	TEXT	NO	Nombre oficial (UNIQUE)
abbr	TEXT	SÍ	Abreviatura (MORENA, PAN, etc.)
clasificacion	TEXT	NO	Tipo: partido, bancada, coalicion, gobierno, institucion, otro
fundacion	TEXT	SÍ	Fecha de fundación en ISO 8601
disolucion	TEXT	SÍ	Fecha de disolución en ISO 8601

clasificacion IN ('partido', 'bancada', 'coalicion', 'gobierno', 'institucion', 'otro')

person — Legisladores

Tabla central de legisladores (diputados y senadores). Incluye datos demográficos, tipo de curul, corrientes internas y evaluaciones de vulnerabilidad.

Columna	Tipo	Nulable	Descripción
id	TEXT	NO (PK)	Clave primaria, prefijo P01, P02…
nombre	TEXT	NO	Nombre completo
fecha_nacimiento	TEXT	SÍ	Fecha de nacimiento en ISO 8601
genero	TEXT	SÍ	Género: M, F, NB
curul_tipo	TEXT	SÍ	Tipo de curul: mayoria_relativa, plurinominal, suplente
circunscripcion	INTEGER	SÍ	Número de circunscripción electoral (1-5)
start_date	TEXT	SÍ	Inicio del período legislativo
end_date	TEXT	SÍ	Fin del período legislativo
corriente_interna	TEXT	SÍ	Corriente interna: Monreal, AMLO, Sheinbaum, institucionalista, o NULL
vulnerabilidad	TEXT	SÍ	Vulnerabilidad política: alta, media, baja, o NULL
observaciones	TEXT	SÍ	Notas de texto libre
identifiers_json	TEXT	SÍ	Objeto JSON con IDs externos

genero IN ('M', 'F', 'NB')
curul_tipo IN ('mayoria_relativa', 'plurinominal', 'suplente')
circunscripcion BETWEEN 1 AND 5
corriente_interna IN ('Monreal', 'AMLO', 'Sheinbaum', 'institucionalista', NULL)
vulnerabilidad IN ('alta', 'media', 'baja', NULL)

Consejo

El campo corriente_interna rastrea las facciones políticas dentro de los partidos. Esto es particularmente relevante para MORENA, donde las divisiones internas (monrealistas, sheinbaumistas, etc.) afectan significativamente el comportamiento de voto.

membership — Relaciones Persona-Organización

Vincula personas con organizaciones incluyendo rol, rango de fechas y representación opcional de coalición.

Columna	Tipo	Nulable	Descripción
id	TEXT	NO (PK)	Clave primaria, prefijo M01, M02…
person_id	TEXT	NO	Clave foránea a person(id), ON DELETE CASCADE
org_id	TEXT	NO	Clave foránea a organization(id), ON DELETE CASCADE
rol	TEXT	NO	Rol: diputado, senador, suplente
label	TEXT	SÍ	Descripción legible de la membresía
start_date	TEXT	NO	Fecha de inicio de la membresía
end_date	TEXT	SÍ	Fecha de fin. NULL significa activa actualmente
on_behalf_of	TEXT	SÍ	Clave foránea a organization(id). Miembro de coalición votando bajo otro partido

Nota

La columna on_behalf_of maneja la representación de coaliciones. Cuando un diputado del Partido A fue electo bajo una coalición con el Partido B, este campo registra la entidad de coalición.

post — Cargos Legislativos

Representa cargos formales dentro de organizaciones (ej. presidente de comisión, secretario de mesa directiva).

Columna	Tipo	Nulable	Descripción
id	TEXT	NO (PK)	Clave primaria, prefijo T01, T02…
org_id	TEXT	NO	Clave foránea a organization(id)
area_id	TEXT	SÍ	Clave foránea a area(id) para cargos vinculados geográficamente
label	TEXT	NO	Título del cargo
start_date	TEXT	NO	Fecha de inicio
end_date	TEXT	SÍ	Fecha de fin. NULL significa que el cargo aún se mantiene

motion — Iniciativas Legislativas

Almacena propuestas legislativas incluyendo reformas constitucionales, leyes secundarias y legislación ordinaria.

Columna	Tipo	Nulable	Descripción
id	TEXT	NO (PK)	Clave primaria, prefijo Y01, Y02…
texto	TEXT	NO	Texto completo o resumen de la iniciativa
clasificacion	TEXT	NO	Tipo: reforma_constitucional, ley_secundaria, ordinaria, otra
requirement	TEXT	NO	Umbral de votación requerido: mayoria_simple, mayoria_calificada, unanime
result	TEXT	SÍ	Resultado: aprobada, rechazada, pendiente, retirada, o NULL
date	TEXT	SÍ	Fecha de la iniciativa
legislative_session	TEXT	SÍ	Etiqueta de la sesión legislativa, ej. “LXVI Legislatura”
fuente_url	TEXT	SÍ	URL fuente de la iniciativa

clasificacion IN ('reforma_constitucional', 'ley_secundaria', 'ordinaria', 'otra')
requirement IN ('mayoria_simple', 'mayoria_calificada', 'unanime')
result IN ('aprobada', 'rechazada', 'pendiente', 'retirada', NULL)

vote_event — Eventos de Votacion

Registra sesiones de votación. Cada evento de votación está vinculado a una motion y una organización (cámara).

Columna	Tipo	Nulable	Descripción
id	TEXT	NO (PK)	Clave primaria, prefijo VE01, VE02…
motion_id	TEXT	NO	Clave foránea a motion(id)
start_date	TEXT	NO	Fecha y hora de la votación
organization_id	TEXT	NO	Clave foránea a organization(id) (cámara)
result	TEXT	SÍ	Resultado: aprobada, rechazada, empate, o NULL
sitl_id	INTEGER	SÍ	ID de votación del SITL (Sistema de Información Legislativa)
voter_count	INTEGER	SÍ	Número de votantes registrados
legislatura	TEXT	SÍ	Código de legislatura: LX, LXI, …, LXVI
requirement	TEXT	SÍ	Umbral de votación: mayoria_simple, mayoria_calificada, unanime, o NULL
source_id	TEXT	SÍ	ID del portal original, usado para deduplicación
identifiers_json	TEXT	SÍ	JSON con identificadores externos

result IN ('aprobada', 'rechazada', 'empate', NULL)
requirement IN ('mayoria_simple', 'mayoria_calificada', 'unanime', NULL)

vote — Votos Individuales

Registra el voto individual de cada legislador dentro de un evento de votación.

Columna	Tipo	Nulable	Descripción
id	TEXT	NO (PK)	Clave primaria, prefijo V01, V02…
vote_event_id	TEXT	NO	Clave foránea a vote_event(id)
voter_id	TEXT	NO	Clave foránea a person(id)
option	TEXT	NO	Voto emitido: a_favor, en_contra, abstencion, ausente
group	TEXT	SÍ	Afiliación partidista al momento de la votación

option IN ('a_favor', 'en_contra', 'abstencion', 'ausente')

Consejo

La columna group captura la afiliación partidista al momento de la votación, que puede diferir de la membresía actual de la persona. Esto es esencial para rastrear legisladores que cambian de partido.

count — Recuentos de Votos por Grupo

Tallies agregadas de votos por opción y por grupo (partido). Usadas para consultas estadísticas rápidas sin escanear filas individuales de vote.

Columna	Tipo	Nulable	Descripción
id	TEXT	NO (PK)	Clave primaria, prefijo C01, C02…
vote_event_id	TEXT	NO	Clave foránea a vote_event(id)
option	TEXT	NO	Opción de voto: a_favor, en_contra, abstencion, ausente
value	INTEGER	NO	Conteo de votos para esta opción (>= 0)
group_id	TEXT	SÍ	Clave foránea a organization(id). El partido que se está contabilizando

option IN ('a_favor', 'en_contra', 'abstencion', 'ausente')
value >= 0

actor_externo — Actores Externos

Almacena actores políticos fuera del legislativo que influyen en los resultados legislativos: gobernadores, alcaldes, ex presidentes, jueces, dirigentes partidistas.

Columna	Tipo	Nulable	Descripción
id	TEXT	NO (PK)	Clave primaria, prefijo AE01, AE02…
nombre	TEXT	NO	Nombre completo
tipo	TEXT	NO	Tipo de actor: gobernador, alcalde, ex_presidente, dirigente, juez, otro
area_id	TEXT	SÍ	Clave foránea a area(id). Jurisdicción geográfica
start_date	TEXT	SÍ	Inicio del período de relevancia
end_date	TEXT	SÍ	Fin del período de relevancia
observaciones	TEXT	SÍ	Notas de texto libre

tipo IN ('gobernador', 'alcalde', 'ex_presidente', 'dirigente', 'juez', 'otro')

relacion_poder — Redes de Poder

Tabla de relaciones polimórficas que modela conexiones políticas entre cualquier combinación de personas, organizaciones y actores externos. Esta es una extensión Popolo específica de este proyecto.

Columna	Tipo	Nulable	Descripción
id	TEXT	NO (PK)	Clave primaria, prefijo RP01, RP02…
source_type	TEXT	NO	Tipo de entidad origen: person, organization, actor_externo
source_id	TEXT	NO	ID de la entidad origen
target_type	TEXT	NO	Tipo de entidad destino: person, organization, actor_externo
target_id	TEXT	NO	ID de la entidad destino
tipo	TEXT	NO	Tipo de relación: lealtad, presion, influencia, familiar, clientelismo, conflicto, alianza
peso	INTEGER	NO	Peso/fuerza de la relación (1-5)
start_date	TEXT	SÍ	Inicio de la relación
end_date	TEXT	SÍ	Fin de la relación
fuente	TEXT	SÍ	Fuente de los datos de la relación
nota	TEXT	SÍ	Notas adicionales

source_type IN ('person', 'organization', 'actor_externo')
target_type IN ('person', 'organization', 'actor_externo')
tipo IN ('lealtad', 'presion', 'influencia', 'familiar', 'clientelismo', 'conflicto', 'alianza')
peso BETWEEN 1 AND 5

Nota

relacion_poder usa un diseño polimórfico: source_type/source_id y target_type/target_id pueden apuntar a cualquiera de tres tablas. Esto sacrifica la aplicación de claves foráneas a nivel SQL a cambio de flexibilidad para modelar redes políticas complejas.

evento_politico — Eventos Politicos

Registra eventos políticos significativos que pueden afectar el comportamiento o los resultados legislativos.

Columna	Tipo	Nulable	Descripción
id	TEXT	NO (PK)	Clave primaria, prefijo EP01, EP02…
fecha	TEXT	NO	Fecha del evento
tipo	TEXT	NO	Tipo de evento (texto libre)
descripcion	TEXT	NO	Descripción del evento
consecuencia	TEXT	SÍ	Consecuencias esperadas u observadas
fuente_url	TEXT	SÍ	URL fuente
motion_id	TEXT	SÍ	Clave foránea a motion(id). Iniciativa legislativa relacionada

Índices

Los siguientes índices están definidos para optimizar los patrones de consulta más comunes:

Nombre del Índice	Tabla	Columnas
idx_membership_person	membership	person_id
idx_membership_org	membership	org_id
idx_vote_event_motion	vote_event	motion_id
idx_vote_event_source	vote_event	source_id
idx_vote_voter	vote	voter_id
idx_vote_event	vote	vote_event_id
idx_count_event	count	vote_event_id
idx_count_group	count	group_id
idx_relacion_source	relacion_poder	source_type, source_id
idx_relacion_target	relacion_poder	target_type, target_id
idx_relacion_tipo	relacion_poder	tipo
idx_person_corriente	person	corriente_interna
idx_actor_tipo	actor_externo	tipo

Triggers

Cuatro triggers imponen consistencia de fechas entre los campos start_date y end_date:

trg_person_dates

Se ejecuta BEFORE INSERT en person. Garantiza que end_date no sea anterior a start_date cuando ambos están presentes.

CREATE TRIGGER trg_person_dates
BEFORE INSERT ON person
BEGIN
    SELECT RAISE(ABORT, 'end_date must be >= start_date')
    WHERE NEW.end_date IS NOT NULL
      AND NEW.start_date IS NOT NULL
      AND NEW.end_date < NEW.start_date;
END;

trg_person_dates_update

Misma validación en UPDATE de person.

CREATE TRIGGER trg_person_dates_update
BEFORE UPDATE ON person
BEGIN
    SELECT RAISE(ABORT, 'end_date must be >= start_date')
    WHERE NEW.end_date IS NOT NULL
      AND NEW.start_date IS NOT NULL
      AND NEW.end_date < NEW.start_date;
END;

trg_membership_dates

Se ejecuta BEFORE INSERT en membership. Previene membresías con rangos de fechas invertidos.

CREATE TRIGGER trg_membership_dates
BEFORE INSERT ON membership
BEGIN
    SELECT RAISE(ABORT, 'end_date must be >= start_date')
    WHERE NEW.end_date IS NOT NULL
      AND NEW.end_date < NEW.start_date;
END;

trg_membership_dates_update

Misma validación en UPDATE de membership.

CREATE TRIGGER trg_membership_dates_update
BEFORE UPDATE ON membership
BEGIN
    SELECT RAISE(ABORT, 'end_date must be >= start_date')
    WHERE NEW.end_date IS NOT NULL
      AND NEW.end_date < NEW.start_date;
END;

Estadísticas

Volúmenes actuales de datos en la base de datos de producción:

Métrica	Valor
Votos individuales	~3,510,053
Eventos de votacion	~8,123
Personas	~3,849
Organizaciones	~20
Legislaturas cubiertas	7 (LX a LXVI)