Overview

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

All tables use text-based primary keys with a prefix convention (e.g. P00001, VE_D00001). Foreign keys enforce referential integrity — 14 foreign keys total: 4 with ON DELETE CASCADE (child tables) and 10 with ON DELETE RESTRICT (lookup/parent tables). CHECK constraints validate enums and ranges at the database level, using the correct IS NULL OR field IN(...) pattern for nullable enums.

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.

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

:::note O08 (Diputados) and O09 (Senado) are classified as institucion, not gobierno. O10 (SHH) is the ruling coalition. Historical parties (O12–O15) may have dissolution dates set. :::

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.

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

organization — Political Organizations

Stores political parties, legislative chambers, coalitions, and government institutions. This table has no foreign keys.

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.

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

:::caution The nullable enum fields (corriente_interna, vulnerabilidad) use the pattern field IS NULL OR field IN(...). The older field IN('val1', 'val2', NULL) pattern is ineffective because NULL IN (...) evaluates to NULL (not FALSE), bypassing the constraint entirely. :::

:::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.

:::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).

motion — Legislative Initiatives

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

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

vote_event — Voting Events

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

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

vote — Individual Votes

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

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.

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.

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.

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.

Indexes

The following 18 indexes are defined to optimize common query patterns, including 2 unique composite indexes for deduplication:

:::tip The unique composite indexes (idx_vote_unique and idx_count_unique) prevent duplicate records at the database level. idx_vote_unique ensures a legislator cannot vote twice in the same event, and idx_count_unique ensures one tally per option per group per event. :::

Triggers

Four triggers enforce date consistency between start_date and end_date fields. They use the SELECT CASE WHEN ... THEN RAISE(ABORT, ...) END pattern with a WHEN clause at the trigger level:

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
WHEN NEW.end_date IS NOT NULL AND NEW.start_date IS NOT NULL
BEGIN
    SELECT CASE WHEN NEW.end_date < NEW.start_date
    THEN RAISE(ABORT, 'end_date debe ser >= start_date en person')
    END;
END;

trg_person_dates_update

Same validation on UPDATE of person.

CREATE TRIGGER trg_person_dates_update
BEFORE UPDATE ON person
WHEN NEW.end_date IS NOT NULL AND NEW.start_date IS NOT NULL
BEGIN
    SELECT CASE WHEN NEW.end_date < NEW.start_date
    THEN RAISE(ABORT, 'end_date debe ser >= start_date en person')
    END;
END;

trg_membership_dates

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

CREATE TRIGGER trg_membership_dates
BEFORE INSERT ON membership
WHEN NEW.end_date IS NOT NULL AND NEW.start_date IS NOT NULL
BEGIN
    SELECT CASE WHEN NEW.end_date < NEW.start_date
    THEN RAISE(ABORT, 'end_date debe ser >= start_date en membership')
    END;
END;

trg_membership_dates_update

Same validation on UPDATE of membership.

CREATE TRIGGER trg_membership_dates_update
BEFORE UPDATE ON membership
WHEN NEW.end_date IS NOT NULL AND NEW.start_date IS NOT NULL
BEGIN
    SELECT CASE WHEN NEW.end_date < NEW.start_date
    THEN RAISE(ABORT, 'end_date debe ser >= start_date en membership')
    END;
END;

Statistics

Current data volumes in the production database: