Data Dictionary

Overview

This page is the complete column-level reference for all 12 tables in the Popolo-Graph database. Every column, its type, nullability, constraints, and purpose is documented here. Use it as the authoritative source when querying the database or building downstream analyses.

Conventions

Core Tables

`area`

Geographic areas used to map electoral districts, states, and constituencies.

Column	Type	Nullable	Constraints	Description
id	TEXT	NO	PRIMARY KEY	Unique ID with prefix A01, A02, etc.
nombre	TEXT	NO	—	Name of the area (state, district, or constituency)
clasificacion	TEXT	NO	CHECK IN (‘estado’,‘distrito’,‘circunscripcion’)	Classification of the geographic area
parent_id	TEXT	YES	FK → area(id)	Parent area (e.g., district within a state)
geometry	TEXT	YES	—	Optional GeoJSON geometry for map visualization

`organization`

Political parties, parliamentary blocs, coalitions, government bodies, and other organizations.

Column	Type	Nullable	Constraints	Description
id	TEXT	NO	PRIMARY KEY	Unique ID with prefix O01, O02, etc.
nombre	TEXT	NO	UNIQUE	Official name of the organization
abbr	TEXT	YES	—	Abbreviation (MORENA, PAN, PRI, etc.)
clasificacion	TEXT	NO	CHECK IN (‘partido’,‘bancada’,‘coalicion’,‘gobierno’,‘institucion’,‘otro’)	Type of organization
fundacion	TEXT	YES	—	Founding date in ISO 8601 format
disolucion	TEXT	YES	—	Dissolution date in ISO 8601 format

`person`

Legislators and other individuals tracked in the system.

Column	Type	Nullable	Constraints	Description
id	TEXT	NO	PRIMARY KEY	Unique ID with prefix P01, P02, etc.
nombre	TEXT	NO	—	Full name of the person
fecha_nacimiento	TEXT	YES	—	Date of birth in ISO 8601 format
genero	TEXT	YES	CHECK IN (‘M’,‘F’,‘NB’)	Gender: M=male, F=female, NB=non-binary
curul_tipo	TEXT	YES	CHECK IN (‘mayoria_relativa’,‘plurinominal’,‘suplente’)	Seat type: MR, proportional representation, or substitute
circunscripcion	INTEGER	YES	CHECK BETWEEN 1 AND 5	Electoral constituency (1-5)
start_date	TEXT	YES	—	Start date of legislative term
end_date	TEXT	YES	—	End date of legislative term
corriente_interna	TEXT	YES	CHECK IN (‘Monreal’,‘AMLO’,‘Sheinbaum’,‘institucionalista’,NULL)	Internal party faction (for loyalty analysis)
vulnerabilidad	TEXT	YES	CHECK IN (‘alta’,‘media’,‘baja’,NULL)	Estimated vulnerability level (game theory)
observaciones	TEXT	YES	—	Free-text notes about the legislator
identifiers_json	TEXT	YES	—	JSON with external identifiers (e.g., `{"sitl_id": 108}`)

`membership`

Links a person to an organization with a specific role and time period.

Column	Type	Nullable	Constraints	Description
id	TEXT	NO	PRIMARY KEY	Unique ID with prefix M01, M02, etc.
person_id	TEXT	NO	FK → person(id) ON DELETE CASCADE	Reference to the person
org_id	TEXT	NO	FK → organization(id) ON DELETE CASCADE	Reference to the organization
rol	TEXT	NO	—	Role within the organization (diputado, senador, suplente)
label	TEXT	YES	—	Human-readable description (e.g., “Diputado plurinominal, Circ. 4”)
start_date	TEXT	NO	—	Start date of membership
end_date	TEXT	YES	—	End date (NULL = currently active)
on_behalf_of	TEXT	YES	FK → organization(id)	Organization on whose behalf the role is exercised (e.g., coalition)

`post`

Formal positions within an organization (chamber), optionally tied to a geographic area.

Column	Type	Nullable	Constraints	Description
id	TEXT	NO	PRIMARY KEY	Unique ID with prefix T01, T02, etc.
org_id	TEXT	NO	FK → organization(id)	Organization (chamber) this post belongs to
area_id	TEXT	YES	FK → area(id)	Geographic area associated with the post
label	TEXT	NO	—	Human-readable description of the position
start_date	TEXT	NO	—	Start date of the position
end_date	TEXT	YES	—	End date of the position

`motion`

Legislative initiatives: constitutional reforms, secondary laws, and ordinary bills.

Column	Type	Nullable	Constraints	Description
id	TEXT	NO	PRIMARY KEY	Unique ID with prefix Y01, Y02, etc.
texto	TEXT	NO	—	Full text or summary of the initiative
clasificacion	TEXT	NO	CHECK IN (‘reforma_constitucional’,‘ley_secundaria’,‘ordinaria’,‘otra’)	Type of legislative initiative
requirement	TEXT	NO	CHECK IN (‘mayoria_simple’,‘mayoria_calificada’,‘unanime’)	Majority type required for approval
result	TEXT	YES	CHECK IN (‘aprobada’,‘rechazada’,‘pendiente’,‘retirada’,NULL)	Result of the vote (NULL if not yet voted)
date	TEXT	YES	—	Date of the vote
legislative_session	TEXT	YES	—	Legislative session name (e.g., “LXVI Legislatura”)
fuente_url	TEXT	YES	—	URL of the official source

`vote_event`

A specific voting event (roll call) on a motion, held by a chamber.

Column	Type	Nullable	Constraints	Description
id	TEXT	NO	PRIMARY KEY	Unique ID with prefix VE01, VE02, etc.
motion_id	TEXT	NO	FK → motion(id)	Reference to the motion being voted
start_date	TEXT	NO	—	Date/time when the vote event started
organization_id	TEXT	NO	FK → organization(id)	Chamber that held the vote
result	TEXT	YES	CHECK IN (‘aprobada’,‘rechazada’,‘empate’,NULL)	Outcome of the vote
sitl_id	INTEGER	YES	—	SITL system voting identifier (votaciont parameter)
voter_count	INTEGER	YES	—	Number of legislators who participated
legislatura	TEXT	YES	—	Legislature identifier (LX, LXI, …, LXVI)
requirement	TEXT	YES	CHECK IN (‘mayoria_simple’,‘mayoria_calificada’,‘unanime’,NULL)	Majority requirement (copied from motion)
source_id	TEXT	YES	—	Original portal ID for deduplication across sources
identifiers_json	TEXT	YES	—	JSON with Popolo external identifiers

`vote`

Individual vote cast by a legislator in a vote event.

Column	Type	Nullable	Constraints	Description
id	TEXT	NO	PRIMARY KEY	Unique ID with prefix V01, V02, etc.
vote_event_id	TEXT	NO	FK → vote_event(id)	Reference to the vote event
voter_id	TEXT	NO	FK → person(id)	Reference to the legislator who voted
option	TEXT	NO	CHECK IN (‘a_favor’,‘en_contra’,‘abstencion’,‘ausente’)	Vote option: yes, no, abstain, absent
”group”	TEXT	YES	—	Party/bloc the legislator belonged to at vote time

Note: The group column name is a SQLite reserved keyword and must be quoted in queries.

`count`

Aggregated vote counts per option per party within a vote event.

Column	Type	Nullable	Constraints	Description
id	TEXT	NO	PRIMARY KEY	Unique ID with prefix C01, C02, etc.
vote_event_id	TEXT	NO	FK → vote_event(id)	Reference to the vote event
option	TEXT	NO	CHECK IN (‘a_favor’,‘en_contra’,‘abstencion’,‘ausente’)	Vote option being counted
value	INTEGER	NO	CHECK >= 0	Number of votes in this count
group_id	TEXT	YES	FK → organization(id)	Party contributing these votes

Extension Tables

These three tables extend the Popolo schema to support power network analysis. They model external political actors, relationships between entities, and political events that shape legislative behavior.

`actor_externo`

Political actors outside the legislature: governors, mayors, former presidents, party leaders, judges, and others who influence legislative outcomes.

Column	Type	Nullable	Constraints	Description
id	TEXT	NO	PRIMARY KEY	Unique ID with prefix AE01, AE02, etc.
nombre	TEXT	NO	—	Full name of the external actor
tipo	TEXT	NO	CHECK IN (‘gobernador’,‘alcalde’,‘ex_presidente’,‘dirigente’,‘juez’,‘otro’)	Type of external actor
area_id	TEXT	YES	FK → area(id)	Geographic area of influence
start_date	TEXT	YES	—	Start date of their relevance/term
end_date	TEXT	YES	—	End date of their relevance/term
observaciones	TEXT	YES	—	Free-text notes about the actor

`relacion_poder`

Power relationships between any two entities (persons, organizations, or external actors). Used for loyalty analysis, pressure mapping, and coalition tracking.

Column	Type	Nullable	Constraints	Description
id	TEXT	NO	PRIMARY KEY	Unique ID with prefix RP01, RP02, etc.
source_type	TEXT	NO	CHECK IN (‘person’,‘organization’,‘actor_externo’)	Type of the source entity
source_id	TEXT	NO	—	ID of the source entity
target_type	TEXT	NO	CHECK IN (‘person’,‘organization’,‘actor_externo’)	Type of the target entity
target_id	TEXT	NO	—	ID of the target entity
tipo	TEXT	NO	CHECK IN (‘lealtad’,‘presion’,‘influencia’,‘familiar’,‘clientelismo’,‘conflicto’,‘alianza’)	Type of power relationship
peso	INTEGER	NO	CHECK BETWEEN 1 AND 5	Relationship strength (1=very weak, 5=very strong)
start_date	TEXT	YES	—	Start date of the relationship
end_date	TEXT	YES	—	End date of the relationship
fuente	TEXT	YES	—	Source of the information (URL or reference)
nota	TEXT	YES	—	Additional notes about the relationship

`evento_politico`

Political events (reforms, crises, agreements) that may or may not be tied to a legislative motion.

Column	Type	Nullable	Constraints	Description
id	TEXT	NO	PRIMARY KEY	Unique ID with prefix EP01, EP02, etc.
fecha	TEXT	NO	—	Date of the event
tipo	TEXT	NO	—	Type of event (free text: reforma, votacion, crisis, acuerdo, etc.)
descripcion	TEXT	NO	—	Detailed description of the event
consecuencia	TEXT	YES	—	Consequence or impact of the event
fuente_url	TEXT	YES	—	URL of the source
motion_id	TEXT	YES	FK → motion(id)	Related legislative motion (if applicable)

Indexes Reference

-- Membership lookups
CREATE INDEX idx_membership_person ON membership(person_id);
CREATE INDEX idx_membership_org    ON membership(org_id);

-- Vote event lookups
CREATE INDEX idx_vote_event_motion ON vote_event(motion_id);
CREATE INDEX idx_vote_event_source ON vote_event(source_id);

-- Individual vote lookups
CREATE INDEX idx_vote_voter ON vote(voter_id);
CREATE INDEX idx_vote_event ON vote(vote_event_id);

-- Count aggregations
CREATE INDEX idx_count_event ON count(vote_event_id);
CREATE INDEX idx_count_group ON count(group_id);

-- Power relationship traversals
CREATE INDEX idx_relacion_source ON relacion_poder(source_type, source_id);
CREATE INDEX idx_relacion_target ON relacion_poder(target_type, target_id);
CREATE INDEX idx_relacion_tipo   ON relacion_poder(tipo);

-- Loyalty analysis
CREATE INDEX idx_person_corriente ON person(corriente_interna);

-- External actor filtering
CREATE INDEX idx_actor_tipo ON actor_externo(tipo);

Triggers Reference

Four triggers enforce the constraint end_date >= start_date on the person and membership tables, for both INSERT and UPDATE operations.

-- Person: validate dates on INSERT
CREATE TRIGGER trg_person_dates_insert
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;

-- Person: validate dates on UPDATE
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;

-- Membership: validate dates on INSERT
CREATE TRIGGER trg_membership_dates_insert
BEFORE INSERT ON membership
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;

-- Membership: validate dates on UPDATE
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.start_date IS NOT NULL
      AND NEW.end_date < NEW.start_date;
END;