spb/websensor

1-- WebSensor 0.3 — Source Factory & coverage (2026-09-13). Additive only.23-- ---- Sources: origin (seed = YAML registry · import = admin import · factory = Source Factory) ----4alter table sources add column if not exists origin text not null default 'seed';5alter table sources add column if not exists sector text;                              -- coverage sector that produced the source6create index if not exists sources_origin_idx on sources (origin);7create index if not exists sources_domain_idx on sources (domain);89-- ---- Sensors: SHADOW status (observed, evidence stored, never published) ----------------------10create index if not exists sensors_status_idx on sensors (status);11create index if not exists sensors_url_host_idx on sensors ((lower(split_part(split_part(url, '/', 3), ':', 1))));1213-- ---- Factory seeds: organizations to expand -------------------------------------------------14create table if not exists factory_seeds (15  id text primary key,                                   -- kebab id; becomes the source id when a new source is created16  name text not null,17  domain text not null,18  homepage text,19  categories text[] not null default '{}',20  country text,21  language text,22  tier text not null default 'B',23  weight real not null default 1,24  importance smallint not null default 2,               -- 1 long tail · 2 notable · 3 systemic25  aliases text[] not null default '{}',26  first_party boolean not null default true,27  sector text,28  universe text,29  hints jsonb not null default '{}',30  status text not null default 'queued',                 -- queued | discovering | discovered | blocked | error | done31  attempts integer not null default 0,32  source_id text,                                        -- existing or created source33  candidates integer not null default 0,34  shadow integer not null default 0,35  accepted integer not null default 0,36  rejected integer not null default 0,37  last_error text,38  discovered_at timestamptz,39  created_at timestamptz not null default now(),40  updated_at timestamptz not null default now()41);42create index if not exists factory_seeds_status_idx on factory_seeds (status, importance desc, created_at);43create index if not exists factory_seeds_domain_idx on factory_seeds (domain);44create index if not exists factory_seeds_sector_idx on factory_seeds (sector);4546-- ---- Discovery candidates: full lifecycle (candidate → shadow → accepted | rejected | duplicate | blocked) ----47alter table discovery_candidates add column if not exists seed_id text;48alter table discovery_candidates add column if not exists connector text;49alter table discovery_candidates add column if not exists type text;50alter table discovery_candidates add column if not exists name text;51alter table discovery_candidates add column if not exists kind_class text;             -- news press blog ir filings changelog releases security pricing legal careers docs api status sitemap models data posture other52alter table discovery_candidates add column if not exists config jsonb not null default '{}';53alter table discovery_candidates add column if not exists tier text;54alter table discovery_candidates add column if not exists score_value real;55alter table discovery_candidates add column if not exists reason text;56alter table discovery_candidates add column if not exists shadow_sensor_id text;57alter table discovery_candidates add column if not exists decided_at timestamptz;58alter table discovery_candidates add column if not exists updated_at timestamptz not null default now();59create index if not exists discovery_candidates_status_idx on discovery_candidates (status, score_value desc);60create index if not exists discovery_candidates_seed_idx on discovery_candidates (seed_id);61create index if not exists discovery_candidates_shadow_idx on discovery_candidates (shadow_sensor_id) where shadow_sensor_id is not null;6263-- ---- Factory daily counters (funnel) ----------------------------------------------------------64create table if not exists factory_daily (65  day date primary key,66  seeds_processed integer not null default 0,67  requests integer not null default 0,68  candidates integer not null default 0,69  shadow_created integer not null default 0,70  accepted integer not null default 0,71  rejected integer not null default 0,72  blocked integer not null default 073);74