Spaces:
Running
Running
File size: 4,256 Bytes
f50a610 92ac48d f50a610 92ac48d f50a610 8ad7ac2 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 | -- Enable PostGIS
CREATE EXTENSION IF NOT EXISTS postgis;
-- 4.2 active_conflicts (War-level rollup)
CREATE TABLE active_conflicts (
conflict_id SERIAL PRIMARY KEY,
name VARCHAR(200), -- 'Russo-Ukrainian War'
countries TEXT[], -- ['UKR', 'RUS']
region VARCHAR(100),
start_date DATE,
status VARCHAR(20), -- ACTIVE | CEASEFIRE | RESOLVED
intensity VARCHAR(20), -- WAR | CRISIS | DISPUTE | TENSION
total_events INTEGER DEFAULT 0,
last_event_at TIMESTAMP
);
-- 4.1 conflict_events
CREATE TABLE conflict_events (
id BIGSERIAL PRIMARY KEY,
event_id VARCHAR(100) UNIQUE NOT NULL, -- CIQ-YYYYMMDD-ISO3-NNNNN
source VARCHAR(20) NOT NULL, -- GDELT | RSS | RELIEFWEB | UCDP
source_reliability VARCHAR(10) DEFAULT 'MEDIUM', -- HIGH | MEDIUM | LOW
event_time TIMESTAMP NOT NULL,
event_date DATE NOT NULL,
year SMALLINT GENERATED ALWAYS AS (EXTRACT(YEAR FROM event_date)) STORED,
week SMALLINT GENERATED ALWAYS AS (EXTRACT(WEEK FROM event_date)) STORED,
country VARCHAR(100) NOT NULL,
country_iso3 CHAR(3) NOT NULL,
region VARCHAR(100),
admin1 VARCHAR(100), -- Province / Oblast
admin2 VARCHAR(100), -- District / Rayon
city VARCHAR(100),
lat DECIMAL(9,6),
lon DECIMAL(10,6),
geom GEOGRAPHY(POINT,4326),
geo_precision SMALLINT DEFAULT 3, -- 1=city 2=district 3=country
event_type VARCHAR(50), -- Battles | Explosions | Violence vs Civilians | Protests
event_subtype VARCHAR(80), -- Armed clash | Air/drone strike | Shelling…
interaction_code VARCHAR(60), -- Military vs Military | Military vs Civilians…
actor1 VARCHAR(200),
actor1_type VARCHAR(50), -- Military Forces | Rebel Group | Civilians…
actor2 VARCHAR(200),
actor2_type VARCHAR(50),
fatalities INTEGER DEFAULT 0,
fatalities_civilians INTEGER DEFAULT 0,
fatalities_confidence VARCHAR(10) DEFAULT 'LOW', -- HIGH | MEDIUM | LOW
severity VARCHAR(20), -- HIGH | MEDIUM | LOW
severity_score DECIMAL(4,2), -- 0.00 – 10.00
title VARCHAR(500),
notes TEXT,
tags TEXT[], -- ['artillery','urban-combat','civilian-area']
source_url TEXT,
conflict_name VARCHAR(200), -- 'Russo-Ukrainian War'
conflict_id INTEGER REFERENCES active_conflicts(conflict_id),
category VARCHAR(20) DEFAULT 'GENERAL',
ingested_at TIMESTAMP DEFAULT NOW()
);
-- INDEXES (sub-10ms guaranteed)
CREATE INDEX idx_event_time ON conflict_events(event_time DESC);
CREATE INDEX idx_country_time ON conflict_events(country_iso3, event_time DESC);
CREATE INDEX idx_geom ON conflict_events USING GIST(geom);
CREATE INDEX idx_severity ON conflict_events(severity_score DESC);
CREATE INDEX idx_category ON conflict_events(category);
CREATE INDEX idx_type ON conflict_events(event_type, event_time DESC);
CREATE INDEX idx_tags ON conflict_events USING GIN(tags);
-- We trigger a NOTIFY when a new conflict event is inserted
-- This pushes data to the websocket
CREATE OR REPLACE FUNCTION notify_new_conflict_event()
RETURNS trigger AS $$
BEGIN
PERFORM pg_notify(
'new_conflict_event',
row_to_json(NEW)::text
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_new_conflict_event
AFTER INSERT ON conflict_events
FOR EACH ROW EXECUTE FUNCTION notify_new_conflict_event();
-- 5. Intelligence Articles Hub
CREATE TABLE intel_articles (
id SERIAL PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL, -- Supports Markdown
author VARCHAR(100),
tags TEXT[],
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW(),
search_vector TSVECTOR GENERATED ALWAYS AS (
to_tsvector('english', title || ' ' || content)
) STORED
);
CREATE INDEX idx_articles_search ON intel_articles USING GIN(search_vector);
CREATE INDEX idx_articles_created ON intel_articles(created_at DESC);
|