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

:::note

• All dates are stored in ISO 8601 format (YYYY-MM-DD or YYYY-MM-DDTHH:MM:SS).
• All text is UTF-8 encoded.
• Foreign keys are enforced (PRAGMA foreign_keys = ON).
• NULL means “not specified” or “not applicable”. No sentinel values are used.
• identifiers_json fields contain valid JSON arrays or objects.
• The "group" column in the vote table uses SQL quoting because group is a reserved keyword in SQLite.
• Cascading deletes on membership (person_id, org_id) ensure referential integrity when a person or organization is removed. :::

Core Tables

area

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

organization

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

person

Legislators and other individuals tracked in the system.

membership

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

post

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

motion

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

vote_event

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

vote

Individual vote cast by a legislator in a vote event.

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.

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.

relacion_poder

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

evento_politico

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

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);

-- Vote event filters
CREATE INDEX idx_vote_event_legislatura ON vote_event(legislatura);
CREATE INDEX idx_vote_event_org ON vote_event(organization_id);
CREATE INDEX idx_vote_event_start_date ON vote_event(start_date);

-- 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);

-- Deduplication (UNIQUE)
CREATE UNIQUE INDEX idx_vote_unique ON vote(vote_event_id, voter_id);
CREATE UNIQUE INDEX idx_count_unique ON count(vote_event_id, option, 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
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;

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

-- Membership: validate dates on INSERT
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;

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