Spaces:
Running
Running
| -- VenThrive business-news system — run this once against your Supabase project's | |
| -- SQL editor (or `psql`). See docs/blueprint/adr/0015-business-news-system-design.md | |
| -- for the reasoning behind every choice below. | |
| create extension if not exists pgcrypto; | |
| -- ─── Sources ──────────────────────────────────────────────────────────────── | |
| -- Sources are data, not code, so an admin can add/disable one without a deploy. | |
| -- 'rss' sources are polled with feedparser; 'html' sources with a generic | |
| -- title + first-paragraph extractor (see backend/news/scrape.py). | |
| create table if not exists news_sources ( | |
| id uuid primary key default gen_random_uuid(), | |
| name text not null, | |
| url text not null unique, | |
| source_type text not null check (source_type in ('rss', 'html')), | |
| -- Which of the three sourcing strategies this feeds (ADR-0015) — purely | |
| -- informational, doesn't change scrape behavior. | |
| source_group text not null check (source_group in ('national_news', 'agency_bulletin', 'funder_program')), | |
| active boolean not null default true, | |
| created_at timestamptz not null default now() | |
| ); | |
| -- A handful of real, well-known starter sources per ADR-0015's three source | |
| -- groups. Add more through this table directly — no code change needed. | |
| insert into news_sources (name, url, source_type, source_group) values | |
| ('BusinessDay', 'https://businessday.ng/feed/', 'rss', 'national_news'), | |
| ('Nairametrics', 'https://nairametrics.com/feed/', 'rss', 'national_news'), | |
| ('Premium Times — Business', 'https://www.premiumtimesng.com/category/business/feed', 'rss', 'national_news'), | |
| ('NiMet — Weather Alerts', 'https://www.nimet.gov.ng', 'html', 'agency_bulletin'), | |
| ('NEMA — Advisories', 'https://nema.gov.ng', 'html', 'agency_bulletin'), | |
| ('SMEDAN', 'https://smedan.gov.ng', 'html', 'funder_program'), | |
| ('Bank of Industry — News', 'https://www.boi.ng/category/news/', 'html', 'funder_program'), | |
| ('Tony Elumelu Foundation', 'https://www.tonyelumelufoundation.org/news', 'html', 'funder_program') | |
| on conflict (url) do nothing; | |
| -- ─── Items ────────────────────────────────────────────────────────────────── | |
| create table if not exists news_items ( | |
| id uuid primary key default gen_random_uuid(), | |
| source_id uuid references news_sources(id) on delete set null, | |
| url text not null, | |
| url_hash text not null unique, | |
| title text not null, | |
| raw_text text not null default '', | |
| published_at timestamptz, | |
| scraped_at timestamptz not null default now(), | |
| status text not null default 'pending' | |
| check (status in ('pending', 'classified', 'needs_review', 'rejected')), | |
| -- Classification output (ADR-0015) — all null until classified. No 'threat' value: | |
| -- reporting something bad that already happened has no value once it can't be acted | |
| -- on; a predictive risk system is a separate, forward-looking feature. | |
| impact text check (impact in ('opportunity', 'neutral')), | |
| category text check (category in ('policy', 'weather', 'infrastructure', 'funding', 'security', 'other')), | |
| business_types text[], -- subsector ids from data/sectors.ts, see backend/news/subsectors.json | |
| location text, | |
| deadline date, -- only set for an opportunity with an explicit deadline in the source text | |
| expires_at timestamptz, -- deadline if set, else scraped_at + the category's default window | |
| confidence numeric, | |
| review_reason text, -- why this needs_review, for the admin queue | |
| classified_at timestamptz, | |
| -- The completeness bar from ADR-0015, enforced here, not just in application | |
| -- code: a classified item must have a category, at least one business type, | |
| -- and a location. | |
| constraint classified_items_are_complete check ( | |
| status <> 'classified' | |
| or (category is not null and location is not null | |
| and business_types is not null and array_length(business_types, 1) > 0) | |
| ) | |
| ); | |
| create index if not exists news_items_status_idx on news_items (status); | |
| create index if not exists news_items_expires_at_idx on news_items (expires_at); | |
| create index if not exists news_items_business_types_idx on news_items using gin (business_types); | |
| -- ─── Poll run log ─────────────────────────────────────────────────────────── | |
| -- One row per admin-triggered poll (ADR-0015: admin-triggered only, daily | |
| -- auto-poll deferred) — an audit trail and a place to surface run-level errors. | |
| create table if not exists news_polls ( | |
| id uuid primary key default gen_random_uuid(), | |
| triggered_by text not null, -- admin's Firebase UID | |
| since_days int not null check (since_days between 1 and 30), | |
| started_at timestamptz not null default now(), | |
| finished_at timestamptz, | |
| items_scraped int not null default 0, | |
| items_deduped_out int not null default 0, | |
| items_classified int not null default 0, | |
| items_flagged int not null default 0, | |
| items_rejected int not null default 0, -- auto-discarded: incomplete and the model wasn't confident either | |
| current_source text, -- name of the source being scraped right now, for a live run console | |
| error text | |
| ); | |
| alter table news_polls add column if not exists items_rejected int not null default 0; | |
| alter table news_polls add column if not exists current_source text; | |
| -- 'threat' removed from impact (ADR-0015 addendum): a reactive "this already happened" | |
| -- label has no value to a business owner who can't act on it anymore. Existing rows | |
| -- are reclassified neutral; a predictive risk/forecast system is planned separately. | |
| update news_items set impact = 'neutral' where impact = 'threat'; | |
| alter table news_items drop constraint if exists news_items_impact_check; | |
| alter table news_items add constraint news_items_impact_check check (impact in ('opportunity', 'neutral')); | |