Spaces:
Running
Running
| -- Intel store: Phase 0 + Phase 1 of the predictive business-intelligence system | |
| -- (docs/blueprint/intelligence-system.md, ADR-0017..0021). | |
| -- | |
| -- Portable SQL: runs unchanged on SQLite (Phase 1, local — ADR-0020) and on | |
| -- Postgres/Supabase when the store moves. The price of that portability: | |
| -- timestamps are ISO-8601 UTC text, booleans are 0/1 integers, JSON is text, ids | |
| -- are generated in Python (no serial/uuid defaults). Revisit all three when the | |
| -- store moves to Postgres for good. | |
| -- | |
| -- Applied by `py -m intel.cli init` (idempotent — every statement is IF NOT EXISTS). | |
| -- ─── Gazetteer ────────────────────────────────────────────────────────────── | |
| -- Everything in the store is keyed by location, so this comes first. Ids are | |
| -- dotted paths: 'ng', 'ng.lagos', 'ng.lagos.ikeja'. State/LGA names are exactly | |
| -- the ones users pick in onboarding (data/nigeria-locations.ts → nigeria_locations.json). | |
| create table if not exists locations ( | |
| id text primary key, | |
| level text not null check (level in ('country', 'state', 'lga', 'town')), | |
| name text not null, | |
| parent_id text references locations(id), | |
| lat real, | |
| lon real, | |
| source text not null | |
| ); | |
| -- Normalised alternative names → location. Seeded from the gazetteer itself, | |
| -- boundary-file spellings, a short curated city list, and place-data localities | |
| -- (intel/locations.py). The seed of the Layer 5 search/geocoding service. | |
| create table if not exists location_aliases ( | |
| alias text not null, | |
| location_id text not null references locations(id), | |
| origin text not null, -- 'gazetteer' | 'boundary' | 'curated' | 'places' | |
| primary key (alias, location_id) | |
| ); | |
| -- ─── Layer 1: stale awareness ─────────────────────────────────────────────── | |
| -- One row per external source. "Stale" means late against the source's OWN | |
| -- cadence, and two dates are tracked because a fetch can succeed today and still | |
| -- return last quarter's numbers. | |
| create table if not exists sources ( | |
| id text primary key, | |
| family text not null, | |
| provider text not null, | |
| licence text not null, | |
| expected_cadence_hours integer not null, | |
| latency_hours integer not null default 0, -- how far behind "now" the newest data legitimately is | |
| last_attempt_at text, | |
| last_success_at text, | |
| latest_data_period text, -- newest period the source has given us | |
| last_error text | |
| ); | |
| -- Every layer reports "done" and the receiving layer acknowledges it. Layer 1 | |
| -- flags reports nobody received. | |
| create table if not exists handoffs ( | |
| id text primary key, | |
| run_id text not null, | |
| from_layer text not null, | |
| to_layer text not null, | |
| report text not null, -- JSON | |
| produced_at text not null, | |
| received_at text, | |
| received_by text | |
| ); | |
| create index if not exists handoffs_pending on handoffs (to_layer, received_at); | |
| -- ─── Layer 2: data stream ─────────────────────────────────────────────────── | |
| -- A stream is one metric from one source, stored at its NATIVE resolution | |
| -- (never copied down to finer locations — that would fake precision the models | |
| -- would then learn from). | |
| create table if not exists streams ( | |
| id text primary key, -- 'weather.precipitation_sum' | |
| family text not null, | |
| metric text not null, | |
| unit text not null, | |
| kind text not null check (kind in ('driver', 'indicator')), | |
| native_level text not null check (native_level in ('country', 'state', 'lga', 'town', 'point')), | |
| cadence text not null check (cadence in ('daily', 'weekly', 'monthly', 'quarterly', 'yearly', 'event')), | |
| source_id text not null references sources(id) | |
| ); | |
| -- Numeric time series. Observed history and forecasts share one table: | |
| -- history has is_forecast=0 and issued_at=''; each forecast run keeps its own | |
| -- rows (issued_at = run date) so forecast skill can be measured later. | |
| create table if not exists observations ( | |
| stream_id text not null references streams(id), | |
| location_id text not null references locations(id), | |
| period_start text not null, | |
| is_forecast integer not null default 0 check (is_forecast in (0, 1)), | |
| issued_at text not null default '', | |
| value real not null, | |
| ingested_at text not null, | |
| primary key (stream_id, location_id, period_start, is_forecast, issued_at) | |
| ); | |
| create index if not exists observations_loc on observations (location_id, stream_id, period_start); | |
| -- Things that happen rather than values that move: a policy enacted, a holiday, | |
| -- an incident, a project gazetted. Kept permanently — this is the history the | |
| -- correlation step and models learn from (unlike news_items, which expire from | |
| -- the user-facing feed). | |
| create table if not exists events ( | |
| id text primary key, | |
| family text not null, -- policy | weather | infrastructure | funding | security | calendar | other | |
| type text not null, -- e.g. 'public_holiday', 'religious_holiday', 'season', 'news' | |
| location_id text references locations(id), | |
| starts_on text not null, | |
| ends_on text, | |
| title text not null, | |
| detail text, | |
| is_estimate integer not null default 0 check (is_estimate in (0, 1)), | |
| source_id text not null references sources(id), | |
| source_ref text not null, -- natural key at the source, so re-ingest is idempotent | |
| ingested_at text not null, | |
| unique (source_id, source_ref) | |
| ); | |
| create index if not exists events_loc on events (location_id, starts_on); | |
| -- Points of interest. subsector is one of backend/news/subsectors.json, or null | |
| -- for a context place (worship, civic, landmark) — those still matter as | |
| -- footfall generators even though they aren't competitors. | |
| create table if not exists places ( | |
| id text primary key, -- Overture GERS id | |
| name text, | |
| category text, | |
| taxonomy text, | |
| subsector text, | |
| confidence real, | |
| operating_status text, | |
| lat real not null, | |
| lon real not null, | |
| location_id text references locations(id), -- the LGA it falls in | |
| locality text, | |
| source_id text not null references sources(id), | |
| release text not null | |
| ); | |
| create index if not exists places_loc on places (location_id, subsector); | |