Database Schema

Overview

The Observatorio del Congreso uses a Popolo-Graph unified schema stored in SQLite. It contains 12 tables organized into four functional groups:

Group	Tables	Purpose
Core entities	person, organization, area	Legislators, parties, geographic divisions
Legislative activity	motion, vote_event, vote, count	Bills, voting sessions, individual votes, tallies
Structural relations	membership, post	Party affiliations, committee seats
Power networks	relacion_poder, actor_externo, evento_politico	Loyalty ties, external actors, political events

All tables use text-based primary keys with a prefix convention (e.g. P00001, VE_D00001). Foreign keys enforce referential integrity with ON DELETE CASCADE where appropriate. CHECK constraints validate enums and ranges at the database level.

Entity-Relationship Diagram

                        +-------------+
                        |    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

ID Convention

All primary keys use a text prefix to identify the entity type and, where applicable, the chamber of origin. Numeric portions are zero-padded to 5 digits.

Entity	Prefix	Example	Padding
Vote Event (Diputados)	VE_D	VE_D00001	5 digits
Vote Event (Senado)	VE_S	VE_S00001	5 digits
Vote (Diputados)	V_D	V_D00001	5 digits
Vote (Senado)	V_S	V_S00001	5 digits
Motion (Diputados)	Y_D	Y_D00001	5 digits
Motion (Senado)	Y_S	Y_S00001	5 digits
Membership (Diputados)	M_D	M_D00001	5 digits
Membership (Senado)	M_S	M_S00001	5 digits
Person	P	P00001	5 digits
Count	C	C00001	5 digits

Note

Chamber-specific prefixes (_D for Diputados, _S for Senado) are used for entities that differ between chambers. Shared entities like person and count use a single prefix.

Key Organizations

These are the core political organizations stored in the organization table:

ID	Abbreviation	Name	Classification
O08	Diputados	Camara de Diputados	gobierno
O09	Senado	Camara de Senadores	gobierno
O11	MORENA	Movimiento de Regeneracion Nacional	partido
O12	PAN	Partido Accion Nacional	partido
O13	PRI	Partido Revolucionario Institucional	partido
O14	PRD	Partido de la Revolucion Democratica	partido
O15	PT	Partido del Trabajo	partido
O16	PVEM	Partido Verde Ecologista de Mexico	partido
O17	MC	Movimiento Ciudadano	partido

Table-by-Table Reference

area — Geographic Divisions

Stores geographic divisions used for electoral districts and states. Areas can be nested via parent_id to model the hierarchy: district within state.

Column	Type	Nullable	Description
id	TEXT	NO (PK)	Primary key, prefix A01, A02…
nombre	TEXT	NO	Name of the area
clasificacion	TEXT	NO	Classification: estado, distrito, circunscripcion
parent_id	TEXT	YES	Foreign key to area(id). District points to its parent state
geometry	TEXT	YES	Optional GeoJSON geometry

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

organization — Political Organizations

Stores political parties, legislative chambers, coalitions, and government institutions.

Column	Type	Nullable	Description
id	TEXT	NO (PK)	Primary key, prefix O01, O02…
nombre	TEXT	NO	Official name (UNIQUE)
abbr	TEXT	YES	Abbreviation (MORENA, PAN, etc.)
clasificacion	TEXT	NO	Type: partido, bancada, coalicion, gobierno, institucion, otro
fundacion	TEXT	YES	Founding date in ISO 8601
disolucion	TEXT	YES	Dissolution date in ISO 8601

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

person — Legislators

Core table for legislators (deputies and senators). Includes demographic data, seat type, internal factions, and vulnerability assessments.

Column	Type	Nullable	Description
id	TEXT	NO (PK)	Primary key, prefix P01, P02…
nombre	TEXT	NO	Full name
fecha_nacimiento	TEXT	YES	Birth date in ISO 8601
genero	TEXT	YES	Gender: M, F, NB
curul_tipo	TEXT	YES	Seat type: mayoria_relativa, plurinominal, suplente
circunscripcion	INTEGER	YES	Electoral circumscription number (1-5)
start_date	TEXT	YES	Start of legislative term
end_date	TEXT	YES	End of legislative term
corriente_interna	TEXT	YES	Internal faction: Monreal, AMLO, Sheinbaum, institucionalista, or NULL
vulnerabilidad	TEXT	YES	Political vulnerability: alta, media, baja, or NULL
observaciones	TEXT	YES	Free-text notes
identifiers_json	TEXT	YES	JSON object with external IDs

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)

Tip

The corriente_interna field tracks political factions within parties. This is particularly relevant for MORENA, where internal divisions (Monrealistas, Sheinbaumistas, etc.) significantly affect voting behavior.

membership — Person-Organization Relations

Links persons to organizations with role, date range, and optional coalition representation.

Column	Type	Nullable	Description
id	TEXT	NO (PK)	Primary key, prefix M01, M02…
person_id	TEXT	NO	Foreign key to person(id), ON DELETE CASCADE
org_id	TEXT	NO	Foreign key to organization(id), ON DELETE CASCADE
rol	TEXT	NO	Role: diputado, senador, suplente
label	TEXT	YES	Human-readable description of the membership
start_date	TEXT	NO	Start date of membership
end_date	TEXT	YES	End date. NULL means currently active
on_behalf_of	TEXT	YES	Foreign key to organization(id). Coalition member voting under another party

Note

The on_behalf_of column handles coalition representation. When a deputy from Party A was elected under a coalition with Party B, this field records the coalition entity.

post — Legislative Positions

Represents formal positions within organizations (e.g., committee chair, board secretary).

Column	Type	Nullable	Description
id	TEXT	NO (PK)	Primary key, prefix T01, T02…
org_id	TEXT	NO	Foreign key to organization(id)
area_id	TEXT	YES	Foreign key to area(id) for geographically bound posts
label	TEXT	NO	Title of the position
start_date	TEXT	NO	Start date
end_date	TEXT	YES	End date. NULL means position is still held

motion — Legislative Initiatives

Stores legislative proposals including constitutional reforms, secondary laws, and ordinary legislation.

Column	Type	Nullable	Description
id	TEXT	NO (PK)	Primary key, prefix Y01, Y02…
texto	TEXT	NO	Full text or summary of the initiative
clasificacion	TEXT	NO	Type: reforma_constitucional, ley_secundaria, ordinaria, otra
requirement	TEXT	NO	Vote threshold required: mayoria_simple, mayoria_calificada, unanime
result	TEXT	YES	Outcome: aprobada, rechazada, pendiente, retirada, or NULL
date	TEXT	YES	Date of the initiative
legislative_session	TEXT	YES	Legislative session label, e.g. “LXVI Legislatura”
fuente_url	TEXT	YES	Source URL for the initiative

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

vote_event — Voting Events

Records voting sessions. Each vote event is tied to a motion and an organization (chamber).

Column	Type	Nullable	Description
id	TEXT	NO (PK)	Primary key, prefix VE01, VE02…
motion_id	TEXT	NO	Foreign key to motion(id)
start_date	TEXT	NO	Date and time of the vote
organization_id	TEXT	NO	Foreign key to organization(id) (chamber)
result	TEXT	YES	Outcome: aprobada, rechazada, empate, or NULL
sitl_id	INTEGER	YES	SITL (Sistema de Informacion Legislativa) voting ID
voter_count	INTEGER	YES	Number of voters recorded
legislatura	TEXT	YES	Legislature code: LX, LXI, …, LXVI
requirement	TEXT	YES	Vote threshold: mayoria_simple, mayoria_calificada, unanime, or NULL
source_id	TEXT	YES	Original portal ID, used for deduplication
identifiers_json	TEXT	YES	JSON with external identifiers

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

vote — Individual Votes

Records each individual legislator’s vote within a vote event.

Column	Type	Nullable	Description
id	TEXT	NO (PK)	Primary key, prefix V01, V02…
vote_event_id	TEXT	NO	Foreign key to vote_event(id)
voter_id	TEXT	NO	Foreign key to person(id)
option	TEXT	NO	Vote cast: a_favor, en_contra, abstencion, ausente
group	TEXT	YES	Party affiliation at the time of the vote

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

Tip

The group column captures party affiliation at the time of the vote, which may differ from the person’s current membership. This is essential for tracking party-switching legislators.

count — Vote Counts by Group

Aggregated vote tallies per option per group (party). Used for quick statistical queries without scanning individual vote rows.

Column	Type	Nullable	Description
id	TEXT	NO (PK)	Primary key, prefix C01, C02…
vote_event_id	TEXT	NO	Foreign key to vote_event(id)
option	TEXT	NO	Vote option: a_favor, en_contra, abstencion, ausente
value	INTEGER	NO	Count of votes for this option (>= 0)
group_id	TEXT	YES	Foreign key to organization(id). The party being tallied

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

actor_externo — External Actors

Stores political actors outside the legislature who influence legislative outcomes: governors, mayors, former presidents, judges, party leaders.

Column	Type	Nullable	Description
id	TEXT	NO (PK)	Primary key, prefix AE01, AE02…
nombre	TEXT	NO	Full name
tipo	TEXT	NO	Actor type: gobernador, alcalde, ex_presidente, dirigente, juez, otro
area_id	TEXT	YES	Foreign key to area(id). Geographic jurisdiction
start_date	TEXT	YES	Start of relevance period
end_date	TEXT	YES	End of relevance period
observaciones	TEXT	YES	Free-text notes

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

relacion_poder — Power Networks

Polymorphic relationship table modeling political connections between any combination of persons, organizations, and external actors. This is a Popolo extension specific to this project.

Column	Type	Nullable	Description
id	TEXT	NO (PK)	Primary key, prefix RP01, RP02…
source_type	TEXT	NO	Source entity type: person, organization, actor_externo
source_id	TEXT	NO	ID of the source entity
target_type	TEXT	NO	Target entity type: person, organization, actor_externo
target_id	TEXT	NO	ID of the target entity
tipo	TEXT	NO	Relationship type: lealtad, presion, influencia, familiar, clientelismo, conflicto, alianza
peso	INTEGER	NO	Weight/strength of the relationship (1-5)
start_date	TEXT	YES	Start of the relationship
end_date	TEXT	YES	End of the relationship
fuente	TEXT	YES	Source of the relationship data
nota	TEXT	YES	Additional notes

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

Note

relacion_poder uses a polymorphic design: source_type/source_id and target_type/target_id can point to any of three tables. This trades SQL-level foreign key enforcement for flexibility in modeling complex political networks.

evento_politico — Political Events

Records significant political events that may affect legislative behavior or outcomes.

Column	Type	Nullable	Description
id	TEXT	NO (PK)	Primary key, prefix EP01, EP02…
fecha	TEXT	NO	Date of the event
tipo	TEXT	NO	Type of event (free text)
descripcion	TEXT	NO	Description of the event
consecuencia	TEXT	YES	Expected or observed consequences
fuente_url	TEXT	YES	Source URL
motion_id	TEXT	YES	Foreign key to motion(id). Related legislative initiative

Indexes

The following indexes are defined to optimize common query patterns:

Index Name	Table	Columns
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

Four triggers enforce date consistency between start_date and end_date fields:

trg_person_dates

Fires BEFORE INSERT on person. Ensures end_date is not earlier than start_date when both are present.

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

Same validation on UPDATE of 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

Fires BEFORE INSERT on membership. Prevents memberships with inverted date ranges.

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

Same validation on UPDATE of 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;

Statistics

Current data volumes in the production database:

Metric	Value
Individual votes	~3,510,053
Vote events	~8,123
Persons	~3,849
Organizations	~20
Legislatures covered	7 (LX through LXVI)