Sahel Disinformation ObservatoryCenter of African Studies, Charles University

← Method & Data

Internal design document, rendered for transparency. Operational sections (deployment, job recipes, credentials) have been removed. Dated 2026-10-19; may be amended by later work recorded in the changes log.

GDELT as a second conflict-event source — design

Date: 2026-10-19 Status: draft for user approval (brainstorming 2026-10-19, builder check done same day, amendments folded in); not implemented Scope: ingest + alignment + GDELT tensor + UCDP↔GDELT link + coverage export. No analysis in this branch.

1. Purpose

The pipeline has one conflict-event series, UCDP GED (ucdp_event, c_tensor, spec 2026-10-14). UCDP is hand-checked, one row per real incident with deaths, dated by the day it happened, but it is slow (monthly candidate releases) and lists only lethal events. The user wants a second, high-frequency source so that

  1. coverage improves: an incident one source misses, the other may list;
  2. reaction speed can be measured: how soon after an incident the Sahel Telegram channels start talking about it, which needs the moment the news broke, not just the day the event happened;
  3. lead signals can be looked for: whether anything happens in the channels before an incident, which needs a trustworthy event date as the anchor.

GDELT 2.0 is a machine that reads world news every 15 minutes and emits one row per reported event with a guessed place, a guessed event date and the 15-minute slot in which the report first appeared. It is fast and wide but noisy: the same incident appears under many ids, the event date is often the report date, and places are sometimes wrong.

Decisions already taken with the user (2026-10-19):

Facts about GDELT 2.0 the design relies on (public documentation; the builder verifies each against two real files at implementation time, §7):

2. Data model (migration 0023)

2.1 gdelt_event — source of truth, one row per GlobalEventID

Identity / provenance

column type note
global_event_id BIGINT PK GDELT id
stream TEXT NOT NULL CHECK IN ('english','translation') which file family
file_ts TIMESTAMPTZ NOT NULL the 15-minute file the row came from
date_added TIMESTAMPTZ NOT NULL DATEADDED: first-report slot
match_scope TEXT NOT NULL CHECK IN ('action','actor_only') why the row was kept, §3.3
first_seen_at TIMESTAMPTZ NOT NULL DEFAULT now() never updated
ingested_at TIMESTAMPTZ NOT NULL DEFAULT now() updated on every upsert
raw_s3_key TEXT NOT NULL the daily raw object this row was parsed from

Event

sql_date DATE NOT NULL, fraction_date REAL, is_root_event BOOLEAN, event_code TEXT NOT NULL, event_base_code TEXT, event_root_code SMALLINT NOT NULL, quad_class SMALLINT NOT NULL, goldstein REAL, num_mentions INT, num_sources INT, num_articles INT, avg_tone REAL.

Actors (the ten CAMEO fields per actor are rarely all present): actor1_code TEXT, actor1_name TEXT, actor1_country_code TEXT, actor1_type1_code TEXT, the same four for actor 2, and one JSONB actors holding the full twenty actor fields verbatim.

Place as GDELT gives it (action geography)

action_geo_type SMALLINT, action_geo_fullname TEXT, action_geo_country_code TEXT (FIPS), action_geo_adm1_code TEXT, action_geo_adm2_code TEXT, action_geo_lat DOUBLE PRECISION, action_geo_lon DOUBLE PRECISION, action_geo_feature_id TEXT. Actor 1 and actor 2 geographies in one JSONB actor_geo. No geometry column; no PostGIS.

Source

source_url TEXT, source_domain TEXT (host, lower-cased, leading www. stripped), source_url_norm TEXT (scheme and www. stripped, query tracking keys utm_*/fbclid removed, fragment removed, trailing slash removed). source_url_norm exists so the study can later join GDELT to document.url in the web corpus; the join itself is out of scope.

Alignment columns — written ONLY by conflict align, never by the fetcher:

column type note
gid_1 TEXT GADM 4.1 admin-1 gid
geo_resolution TEXT CHECK IN ('admin1','country','unresolved') NULL = never aligned
align_version TEXT sha256 of the gazetteer directory used

No gid_2: GDELT admin-2 codes are sparse and unreliable (my call).

Indexes: (sql_date), (date_added), (action_geo_country_code, sql_date), (align_version), (event_root_code), (source_domain).

2.2 gdelt_sync_run — one row per fetch

id BIGSERIAL PK, run_id TEXT UNIQUE NOT NULL, started_at, finished_at, status TEXT CHECK IN ('running','success','failed'), window_start DATE, window_end DATE, days_requested INT, days_complete INT, days_skipped_complete INT (already had a complete manifest), files_listed INT, files_downloaded INT, files_missing INT (in the expected 96-slot grid but absent from the master list), rows_read BIGINT, rows_kept INT, events_upserted INT, error TEXT.

2.3 gdelt_day_manifest — one row per (day, stream), the resume key

column type note
day DATE NOT NULL UTC day of the 15-minute files
stream TEXT NOT NULL
files_expected INT NOT NULL 96
files_listed INT NOT NULL present in the master list for that day
files_fetched INT NOT NULL downloaded and md5-verified
rows_read INT NOT NULL
rows_kept INT NOT NULL
raw_s3_key TEXT NOT NULL the daily raw object, §3.4
complete BOOLEAN NOT NULL files_fetched = files_listed
run_id TEXT NOT NULL
built_at TIMESTAMPTZ NOT NULL DEFAULT now()

PK (day, stream). A day is re-processed only when it has no manifest row, or its row is not complete, or the master list now lists more files for that day than files_listed (GDELT occasionally back-fills an outage).

2.4 g_tensor — derived GDELT fact table (mirror of c_tensor)

Grain admin1 × day × event_root_code. This table is also the within-GDELT collapse: the many GlobalEventIDs that describe one incident on one day in one region under one root code become one cell.

column type
id BIGSERIAL PK
pipeline_version TEXT NOT NULL
admin1 TEXT NOT NULL — GADM gid_1 or 'unlocated'
day DATE NOT NULL — sql_date
event_root_code SMALLINT NOT NULL
event_count INT NOT NULL — distinct GlobalEventIDs
root_event_count INT NOT NULL — those with is_root_event
mentions INT NOT NULL — sum num_mentions
sources INT NOT NULL — sum num_sources
articles INT NOT NULL — sum num_articles
distinct_domains INT NOT NULL
first_report_at TIMESTAMPTZ NOT NULL — min date_added
last_report_at TIMESTAMPTZ NOT NULL — max date_added
goldstein_mean REAL
tone_mean REAL
has_english BOOLEAN NOT NULL
has_translation BOOLEAN NOT NULL
run_id TEXT NOT NULL
built_at TIMESTAMPTZ NOT NULL DEFAULT now()

UNIQUE (pipeline_version, admin1, day, event_root_code).

first_report_at is the column the reaction-speed study needs: the earliest 15-minute slot in which any outlet GDELT reads reported that cell. day is GDELT's guess of the event day, which is often the report day; the study must treat it as approximate and prefer the UCDP date where a link exists.

2.5 g_tensor_event — traceability bridge

tensor_id BIGINT REFERENCES g_tensor(id) ON DELETE CASCADE, global_event_id BIGINT REFERENCES gdelt_event(global_event_id), PK (tensor_id, global_event_id). The integrity audit is a join.

column type note
ucdp_id BIGINT REFERENCES ucdp_event(id)
global_event_id BIGINT REFERENCES gdelt_event(global_event_id)
rule TEXT NOT NULL CHECK IN ('A','B') §5.1
day_offset SMALLINT NOT NULL gdelt.sql_date − ucdp.date_start
link_version TEXT NOT NULL sha256 of the rule parameters
run_id TEXT NOT NULL
built_at TIMESTAMPTZ NOT NULL DEFAULT now()

PK (ucdp_id, global_event_id, link_version). One UCDP event may link to many GDELT rows and one GDELT row to several UCDP events on the same day in the same region; both are expected and kept. A link is a claim "these probably describe the same incident", not an identity.

2.7 incident_coverage — the combined list the user asked for

Grain admin1 × day, violent events only (UCDP: all three type_of_violence; GDELT: event_root_code IN (18,19,20) and match_scope = 'action'). Rows exist only for days ≥ gdelt_window_start (before that GDELT was simply not fetched, and a row would read as a GDELT miss) and only where at least one source has a violent event.

Day anchoring (my call, after builder review). The day of a row is the event day, as well as it can be known:

column type
id BIGSERIAL PK
pipeline_version TEXT NOT NULL
admin1 TEXT NOT NULL
day DATE NOT NULL
ucdp_event_count INT NOT NULL
ucdp_deaths_best INT NOT NULL
ucdp_imprecise_events INT NOT NULL — UCDP events with date_prec >= 4 (month or coarser; never linked, §5.1)
ucdp_published BOOLEAN NOT NULL — FALSE when day is after UCDP's coverage end (the last day of the month of source_candidate_max, or the stable coverage end), i.e. UCDP has not published this day yet
gdelt_event_count INT NOT NULL — attributed as above
gdelt_mentions INT NOT NULL
gdelt_first_report_at TIMESTAMPTZ — min date_added over the attributed rows; NULL when none
linked_ucdp_events INT NOT NULL — UCDP events on this cell with ≥ 1 link
coverage TEXT NOT NULL CHECK IN ('both','ucdp_only','gdelt_only')
run_id TEXT NOT NULL
built_at TIMESTAMPTZ NOT NULL DEFAULT now()

UNIQUE (pipeline_version, admin1, day).

coverage: 'both' when linked_ucdp_events > 0; 'ucdp_only' when ucdp_event_count > 0 and no link (GDELT columns may still be non-zero from unlinked same-day rows, which are kept visible, never hidden); 'gdelt_only' when ucdp_event_count = 0. Agreement is therefore asserted only through links, so a GDELT protest report and an unrelated UCDP ambush on the same day never read as agreement. A 'gdelt_only' row with ucdp_published = FALSE means "UCDP has not caught up yet", not "UCDP missed it"; the study filters on that column before counting misses. Cells with 'unlocated' are kept, so country-level coverage can still be read.

Downgrade drops the seven tables.

3. Fetch — src/sahel_de/ingest_conflict/gdelt.py

3.1 Day planning (plan_days(window_start, window_end, manifests, master) -> DayPlan)

Pure function. Inputs: the window, the existing gdelt_day_manifest rows, and the parsed master lists (per stream, the set of files per UTC day with size and md5). Output: the ordered list of (day, stream) pairs to process and, per pair, the file urls expected. A pair is skipped when its manifest row is complete and the master list does not now list more files than files_listed. Days are processed oldest first so a killed backfill resumes where it stopped.

3.2 Default window

gdelt_window_start (default 2025-10-19) → yesterday UTC. Yesterday, not today: all 96 files of a UTC day exist only after 00:15 UTC the next day, and the daily job runs at 04:00 UTC (§6.2). --start/--end override. The backfill is the same command with the default window on an empty table.

3.3 Row filter (the only step that is not replayable from raw)

A row is kept when

Everything else is dropped before storage. This departs from the pipeline's raw-first rule, deliberately: keeping every global 15-minute file would cost ≈ 70 GB and 70,000 objects a year on CL2, whose 1 M-object quota is already projected to bind around December 2026, for rows the project will never read. The filter is a pure column test, the 61 columns of every kept row are stored verbatim, and GDELT keeps every file online forever, so widening the filter later means re-running the fetch, not losing data. Row counts before and after the filter are recorded per day (§2.3) so a filter bug is visible.

3.4 Raw layout

One object per (day, stream) in bucket_raw:

raw/gdelt/v2/events/{stream}/{day:%Y-%m-%d}/events.tsv.gz
raw/gdelt/v2/events/{stream}/{day:%Y-%m-%d}/_manifest.json

events.tsv.gz = the kept rows of all that day's files, 61 columns verbatim, prefixed with two extra columns file_ts and stream. The manifest lists every file consumed (url, size, md5, rows_read, rows_kept), the files the grid expected but the master list lacked, the filter parameters, and the run_id. The raw object is written BEFORE any row is upserted; parsing for the upsert reads the bytes that were written. 2 objects per day per stream = 4 objects a day, ≈ 1,500 a year.

3.5 Download and verification

Per file: GET the zip, compare md5 with the master list entry; mismatch → retry up to 3 times, then fail the run (no partial day is ever written). 404 on a listed file → retry once after 60 s, then count in files_missing and continue (GDELT's list and its storage are briefly out of step at times). Zip is read in memory, one member, streamed line by line; memory stays under 100 MB. One request at a time, 0.2 s between files; 429/5xx exponential backoff, max 5 attempts, then fail. Master lists are fetched once per run (≈ 30 MB each; cached in the pod's tmp for the run only).

Backfill cost at these settings: ≈ 70,000 files, ≈ 4 h of download at ≈ 5 files/s, well inside one Job. The bottleneck is GDELT's server, not the cluster. The run row is inserted running first and finalised in a finally, as for UCDP; the stale-running reaper threshold is 14 h (the UCDP job uses 6 h) so a slow backfill is never reaped by the next daily run.

3.6 Row shaping (to_row(fields: list[str], stream, file_ts, raw_key) -> dict)

Ints via int(x) with empty string → None; DATEADDED → UTC timestamp; SQLDATE → date; coordinates float or None. A line with a column count other than 61, or without GlobalEventID/SQLDATE/DATEADDED/ EventRootCode, raises MalformedRow and fails the run; nothing is skipped silently. source_domain/source_url_norm derived here.

3.7 Upsert rule

INSERT INTO gdelt_event (...) VALUES (...)
ON CONFLICT (global_event_id) DO UPDATE SET <all event columns>,
    stream, file_ts, date_added, match_scope, raw_s3_key,
    ingested_at = now(),
    gid_1 = NULL, geo_resolution = NULL, align_version = NULL;

Re-processing a day is idempotent. The same GlobalEventID appearing in both streams (rare; GDELT assigns ids per stream, but the builder verifies) keeps the later file. first_seen_at is never touched.

3.8 Settings

gdelt_base_url: str = "http://data.gdeltproject.org/gdeltv2/", gdelt_window_start: date = date(2025, 10, 19), gdelt_country_fips: str = "ML,UV,NG", gdelt_streams: str = "english,translation", gdelt_file_pause_s: float = 0.2. No API key; GDELT is open (CC BY-style attribution). HTTP client httpx, already in the ingest extra.

4. Alignment to GADM 4.1 — extend src/sahel_de/coupling/conflict_align.py

Frame: GADM 4.1, as for UCDP and the news. One new table-driven ladder for gdelt_event; the existing UCDP ladder is untouched.

4.1 Resolution ladder (per event; result in geo_resolution)

  1. admin1 by code — action_geo_adm1_code found in the new curated file configs/gazetteer/gdelt_adm1.csv (fips_adm1,country_iso3,gid_1, note; ≈ 30 rows: Mali's 8 FIPS regions + Bamako, Burkina Faso's 13, Niger's 8) → gid_1.
  2. admin1 by name — else, if action_geo_fullname has the form place, region, country, the middle part is folded with normalize_place and looked up with the existing Gazetteer.lookup(level='admin1'), then ucdp_aliases.csv (the 2025 Burkina region names recur in GDELT text) → gid_1.
  3. country — action_geo_type = 1 (country-level geography) or no adm1 code and no three-part fullname. Lands in unlocated.
  4. unresolved — an adm1 code or region name present but matching nothing. Counted, listed (first 20 distinct per country) in the run output; conflict align exits 1 when unresolved / aligned_this_run > conflict_align_max_unresolved (0.02), same threshold as UCDP.

actor_only rows are aligned too (their action geography is foreign, so they resolve to unresolved or country); this is expected and they are excluded from the threshold ratio.

4.2 gdelt_adm1.csv

Built once by the builder from the FIPS 10-4 ADM1 list for ML, UV, NG, each code mapped to the GADM 4.1 region it falls inside. FIPS regions predate every Sahel reform, so each maps to exactly one GADM region except where the builder finds otherwise; a code that straddles two GADM regions gets NO row and resolves by name or at country level. Every row is pinned in tests/unit/test_conflict_align.py against the committed gazetteer, as the UCDP aliases are.

4.3 Re-alignment

Same align_version gating as UCDP: rows where align_version IS DISTINCT FROM <current> are processed; --all forces every row. conflict align gains --source ucdp|gdelt|all (default all).

Candidates: every aligned UCDP event admissible for c_tensor (§5.2 of the UCDP spec) with date_prec <= 3 (day, a few days, or a week; events dated only to a month or a year would link every violent report in the region for that span and are never linked, my call) × every GDELT row with match_scope = 'action', event_root_code IN (18,19,20), geo_resolution = 'admin1', same gid_1.

Both rules write a row with day_offset. Rule parameters (root codes, the −1/+3 window) are constants in the module; link_version is the sha256 of their repr, so changing them rebuilds every link on the next run. UCDP events with geo_resolution IN ('country','unresolved') are never linked (a country-wide window would link everything); they still appear in incident_coverage under 'unlocated'.

Rebuild: DELETE FROM conflict_event_link WHERE link_version = $1, then re-insert, one transaction. Linking is deterministic.

5.2 g_tensor build — src/sahel_de/coupling/conflict_tensor.py (extend)

Admissible: match_scope = 'action', action_geo_country_code ∈ gdelt_country_fips, sql_date within the window, geo_resolution IS NOT NULL (else fail: run align first). admin1 = gid_1 for 'admin1', 'unlocated' otherwise. All root codes are kept (protests, threats and statements are the "before the incident" material); the violent subset is a filter at read time. Rebuild semantics as c_tensor: one transaction per pipeline_version, refuse to delete on an empty admissible set, deterministic ordering, byte-identical rerun.

5.3 incident_coverage build

After c_tensor, g_tensor and the link exist for the current pipeline_version, in one transaction:

  1. UCDP side: violent c_tensor cells (summed over type_of_violence) for day >= gdelt_window_start, plus ucdp_imprecise_events counted from ucdp_event with date_prec >= 4.
  2. GDELT side: every admissible violent gdelt_event row is assigned ONE day: the date_start of its nearest linked UCDP event at the current link_version (smallest |day_offset|, tie → earlier), else its own sql_date. Aggregate per (admin1, day): count, mentions, min date_added.
  3. FULL OUTER JOIN on (admin1, day); linked_ucdp_events from the link table at the current link_version only (older versions stay in the table but are ignored); ucdp_published from the UCDP coverage end recorded by the latest c_tensor build; coverage as defined in §2.7.

Same rebuild semantics as the tensors (delete by pipeline_version, refuse on empty, deterministic). The link and coverage rebuilds take a Postgres advisory lock (one constant key) so the daily GDELT job and the monthly UCDP job, which both run them, cannot interleave on the 6th.

5.4 Export

Beside c_tensor.parquet in bucket_derived:

{tensor_s3_prefix}/v={pipeline_version}/g_tensor.parquet
{tensor_s3_prefix}/v={pipeline_version}/incident_coverage.parquet
{tensor_s3_prefix}/v={pipeline_version}/conflict_event_link.parquet
{tensor_s3_prefix}/v={pipeline_version}/_g_fingerprint.json

One fingerprint file for the three (md5 over ordered rows of each, in one JSON), same idiom as tensor.export_tensor; per-run manifest at .../_runs/{run_id}-gdelt.json with window, row counts, link_version, align_version. Unchanged → zero objects written.

5.5 Join contract (module docstring)

g_tensor: one row per (admin1, day, event_root_code); sum over root codes for totals; filter roots 18–20 for violence. LEFT JOIN from s_tensor on (admin1, day): a missing row means GDELT reported nothing for that cell on that day, which is "no report", NOT "no event" — unlike c_tensor, GDELT is a measure of coverage. first_report_at is in UTC. incident_coverage.coverage is the only place where "both sources agree" is asserted, and only through links.

8. Out of scope (this branch)

The Mentions and GKG tables (per-mention spread over time, themes, per-article URLs: the next step if first_report_at proves too coarse); admin-2 aggregation; point-in-polygon geocoding from GDELT coordinates; the GDELT ↔ web-corpus URL join (source_url_norm is stored for it); any statistical analysis, including the reaction-speed and lead-signal study itself, which is the next branch once the coverage table has been read; a BigQuery backfill path; dashboard charts.

9. Calls made by the spec author, for the user to check

Rendered from 2026-10-19-gdelt-second-source-design.md · changes log