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-14; may be amended by later work recorded in the changes log.

UCDP conflict-event ingest, alignment and conflict tensor — design

Date: 2026-10-14 Status: approved in conversation (brainstorming, approach A); implemented on branch ucdp-conflict-ingest 2026-10-14 (two planning amendments marked below) Scope: ingest + aligned export only. No analysis in this branch.

1. Purpose

The pipeline has ingested Telegram and web news for the three Sahel countries since mid-2026, with Telegram history back to 2021–2024 depending on the channel. The next research question is the relationship between what those channels publish and conflict on the ground. That needs a conflict event series that (a) covers the whole news window, (b) lives inside the pipeline under the same invariants as everything else (raw first, derived tables rebuilt in one transaction, deterministic fingerprinted export), and (c) joins the news fact table s_tensor on (admin1, day) with no further work.

Decisions already taken with the user:

2. Data model (migration 0021)

2.1 ucdp_event — source of truth, one row per GED event id

Identity / provenance

column type note
id BIGINT PK GED event id
relid TEXT
source_version TEXT NOT NULL e.g. 26.1, 26.0.8
is_candidate BOOLEAN NOT NULL source_version has two dots
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 raw page this row was parsed from

Event

year INT, type_of_violence SMALLINT NOT NULL CHECK (type_of_violence IN (1,2,3)), conflict_name TEXT, dyad_name TEXT, side_a TEXT, side_b TEXT, number_of_sources INT, event_clarity SMALLINT.

Place as UCDP gives it

country_id INT NOT NULL, country TEXT, adm_1 TEXT, adm_2 TEXT, where_coordinates TEXT, where_description TEXT, latitude DOUBLE PRECISION, longitude DOUBLE PRECISION, where_prec SMALLINT NOT NULL. No geometry column; no PostGIS.

Time

date_start DATE NOT NULL, date_end DATE NOT NULL, date_prec SMALLINT NOT NULL.

Deaths

deaths_a INT, deaths_b INT, deaths_civilians INT, deaths_unknown INT, best INT NOT NULL, high INT NOT NULL, low INT NOT NULL.

Source metadata, one JSONB column source_meta holding source_article, source_headline, source_office, source_original, source_date as UCDP ships them (the study may later ask which outlets UCDP relied on).

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

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

Indexes: (source_version), (country_id, date_start), (align_version), (geo_resolution).

2.2 ucdp_sync_run — one row per fetch (replaces SIEM ingestion_log)

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, versions_requested TEXT[], versions_published TEXT[], versions_unpublished TEXT[], events_fetched INT, events_upserted INT, events_skipped_supersession INT, raw_pages_written INT, error TEXT.

2.3 c_tensor — derived conflict fact table (mirror of s_tensor)

column type
id BIGSERIAL PK
pipeline_version TEXT NOT NULL
admin1 TEXT NOT NULL — GADM gid_1 or 'unlocated'
day DATE NOT NULL
type_of_violence SMALLINT NOT NULL
event_count INT NOT NULL
deaths_best INT NOT NULL
deaths_low INT NOT NULL
deaths_high INT NOT NULL
deaths_civilians INT NOT NULL
events_imprecise_date INT NOT NULL — events with date_prec >= 3
source_stable_version TEXT NOT NULL
source_candidate_max TEXT — highest candidate month admitted, or NULL
run_id TEXT NOT NULL
built_at TIMESTAMPTZ NOT NULL DEFAULT now()

UNIQUE (pipeline_version, admin1, day, type_of_violence).

2.4 c_tensor_event — traceability bridge

tensor_id BIGINT REFERENCES c_tensor(id) ON DELETE CASCADE, ucdp_id BIGINT REFERENCES ucdp_event(id), PK (tensor_id, ucdp_id). Same role as s_tensor_doc: the integrity audit is a join.

Downgrade drops the four tables.

3. Fetch and supersession — src/sahel_de/ingest_conflict/ucdp.py

3.1 Version selection (select_versions(window_start, window_end, is_published) -> VersionPlan)

Pure function; is_published(version) -> bool is injected (one-row probe in production, a dict in tests).

  1. Candidate stable = f"{window_end.year % 100}.1". If published, it is the stable release and its coverage end is window_end.year - 1-12-31. Else fall back to f"{(window_end.year - 1) % 100}.1" with coverage end one year earlier. (Stable yy.1 is published mid-year yy and covers through Dec 31 of yy-1.)
  2. Candidate months = every yy.0.N for months strictly after the stable coverage end and ≤ window_end's month, intersected with the window. Candidate months already covered by the chosen stable are NEVER fetched: the stable release merges/drops candidate events under new ids, and re-fetching them would re-insert superseded rows the upsert rule cannot catch.
  3. --versions a,b,c on the CLI bypasses the plan entirely (repairs, experiments). The plan is recorded on ucdp_sync_run.

3.2 Default window

2021-01-01 → today. Candidate data for the three countries begins 2021; the earliest Telegram history is in the same range. The run is idempotent, so re-fetching the whole window monthly (≈ 5,500 rows) is cheap and picks up late corrections. --start/--end override.

3.3 Raw first

Every API page is written to bucket_raw BEFORE parsing:

raw/ucdp/ged/{version}/{country_id}/{fetched:%Y-%m-%d}/page-{n:04d}.json
raw/ucdp/ged/{version}/{country_id}/{fetched:%Y-%m-%d}/_manifest.json

The manifest records request params, TotalCount, TotalPages, page count and the sync run_id. Parsing reads the bytes that were written, so a parser bug can be replayed from the archive (ucdp-sync --replay-from <prefix> is NOT in scope; the layout simply permits it later). Pagination follows NextPageUrl exactly as the API returns it. pagesize=1000.

3.4 Row shaping (to_row(event: dict, version: str, raw_key: str) -> dict)

As in the SIEM script: ints via int(float(x)), dates truncated to YYYY-MM-DD, None preserved for nullable fields. A page whose JSON lacks Result or an event lacking id/date_start/country_id/where_prec raises MalformedPage — the run fails; nothing is skipped silently.

3.5 Upsert rule

INSERT INTO ucdp_event (...) VALUES (...)
ON CONFLICT (id) DO UPDATE SET <all event columns>, source_version,
    is_candidate, raw_s3_key, ingested_at = now(),
    -- alignment columns are reset so the row is re-aligned:
    gid_1 = NULL, gid_2 = NULL, geo_resolution = NULL, align_version = NULL
WHERE ucdp_event.is_candidate OR NOT EXCLUDED.is_candidate;

A candidate never overwrites a stable row; a stable always overwrites. Every row the UPDATE touches has its alignment columns reset, so a monthly re-fetch of the stable release re-aligns the whole stable history on the next conflict align — ≈ 5,500 rows, a few seconds, and it guarantees a changed place name is never served under a stale gid. first_seen_at is never touched. Skipped rows (WHERE false) are counted as events_skipped_supersession.

Rows that a later stable release dropped stay in the table under their candidate version; §5.2 excludes them from the tensor without deleting evidence.

3.6 Politeness and failure

One request at a time; 0.5 s between pages. 429 and 5xx: exponential backoff, max 5 attempts, then fail the run. 400/404 on a version = "not published", recorded in versions_unpublished, not an error. Any other status fails the run. The ucdp_sync_run row is inserted as running at start and finalised in a finally, so a failed run is visible on the dashboard.

HTTP client: httpx (already in the ingest extra). Settings: ucdp_api_key: str = "" (from env SAHEL_UCDP_API_KEY), ucdp_country_ids: str = "432,436,439", ucdp_base_url, ucdp_page_size: int = 1000, ucdp_window_start: date = 2021-01-01.

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

Frame: GADM 4.1, the same frame doc_geo uses. The project froze this frame deliberately for Mali's 2023 split (P3-13); the same policy absorbs Burkina Faso's 2025 regions.

4.1 Resolution ladder (per event; result in geo_resolution)

  1. admin2 — where_prec <= 3 AND adm_2 matches one GADM admin-2 unit → gid_2 set, gid_1 = parent. adm_1 is NOT consulted (this is what makes the new region names harmless).
  2. admin1 — otherwise, if where_prec <= 4 AND adm_1 matches one GADM admin-1 unit (surface forms + structural aliases) → gid_1 only.
  3. country — where_prec >= 5, OR adm_1 and adm_2 both NULL. Only the country is trusted; the row lands in unlocated in the tensor.
  4. unresolved — a non-null adm_2 (with where_prec <= 3) or adm_1 (with where_prec <= 4) that matched nothing. Counted, listed (first 20 distinct names per country) in the run output. conflict align exits 1 when unresolved / aligned_this_run > --max-unresolved (default 0.02), so a UCDP naming change cannot silently erode coverage.

A name matching TWO gids is a data bug → ValueError (same stance as build_acled_lookup).

4.2 Matching mechanics

4.3 New curated aliases (configs/gazetteer/ucdp_aliases.csv)

New curated aliases live in a SEPARATE file configs/gazetteer/ucdp_aliases.csv (country_iso3,level,alias,gid,note), read only by conflict_align.py and validated against the gazetteer's gids. They are NOT merged into aliases.csv, because that table feeds the shared Gazetteer index the P3-09 text matcher uses, and admin-1 aliases such as Soum/Tapoa would give the matcher new multi-level hits on news text (amendment during planning, 2026-10-14).

Mali

Niger

Burkina Faso (2025 regions → GADM 4.1 regions, admin1), only where the new region sits inside ONE old region:

tests/unit/test_gazetteer.py and tests/unit/test_ner_geo.py are untouched (nothing they read changed); tests/unit/test_conflict_align.py pins every alias against the committed gazetteer.

4.4 Re-alignment

align_version = sha256 over the sorted bytes of every file in gazetteer_dir. conflict align processes rows where align_version IS DISTINCT FROM <current>; --all forces every row. Editing an alias therefore re-aligns exactly the affected history on the next run.

5. Conflict tensor and export — src/sahel_de/coupling/conflict_tensor.py

5.1 Grain

admin1 × day × type_of_violence, matching s_tensor's admin-1 × day grain so the join is ON (admin1, day) with no bridge. Admin-2 stays on event rows and is NOT aggregated in this branch.

5.2 Admissible events

5.3 Day attribution and measures

day = date_start. Multi-day events are not spread. Events with `date_prec

= 3count ondate_startAND inevents_imprecise_date, so the study can down-weight them. Measures: event_count, deaths_best/low/high, deaths_civilians, events_imprecise_date`.

5.4 Rebuild semantics

One transaction: DELETE FROM c_tensor WHERE pipeline_version = $1 (cascades the bridge), re-aggregate, insert bridge rows. If the admissible set is EMPTY the build refuses to delete and exits 1. A rerun on unchanged inputs reproduces identical rows (deterministic ordering; same fingerprint).

5.5 Export

{tensor_s3_prefix}/v={pipeline_version}/c_tensor.parquet in bucket_derived, beside s_tensor.parquet; _c_fingerprint.json beside it (md5 over ordered fact rows computed in SQL, same idiom as tensor.export_tensor); per-run manifest at .../_runs/{run_id}-conflict.json recording source_stable_version, source_candidate_max, row count and the align_version. Unchanged tensor → zero objects written. pipeline_version in the key is the NEWS pipeline's, so a consumer always picks matching pairs.

5.6 Join contract (module docstring, verbatim intent)

One c_tensor row per (admin1, day, type_of_violence); sum over type_of_violence for totals. LEFT JOIN from s_tensor to c_tensor on (admin1, day): a missing conflict row means ZERO events, never missing data, because UCDP covers every day of the window.

8. Out of scope (this branch)

Statistical analysis of any kind; admin-2 aggregation; point-in-polygon geocoding (GADM geometries are not vendored); ACLED↔UCDP reconciliation; dashboard chart of conflict series; raw replay command. These belong to the next branch once the data is verified.

GDELT as a second source: see docs/superpowers/specs/2026-10-19-gdelt-second-source-design.md

Rendered from 2026-10-14-ucdp-conflict-ingest-design.md · changes log