TypeScript 55.4%
Python 43.2%
SQL 1.2%
1-- WebSensor 0.2 — signal intelligence upgrade (2026-09-11).2-- Additive only: new columns, tables and indexes. No data is destroyed.34-- ---- Sources: provenance & geography ----------------------------------------------------5alter table sources add column if not exists first_party boolean not null default true;6alter table sources add column if not exists country text;7alter table sources add column if not exists language text;8alter table sources add column if not exists kind text not null default 'registry'; -- registry | custom9alter table sources add column if not exists owner_token text; -- custom monitors only10create index if not exists sources_country_idx on sources (country) where country is not null;11create index if not exists sources_owner_idx on sources (owner_token) where owner_token is not null;1213-- ---- Sensors: lifecycle status (DISCOVERED → PENDING → VALIDATED → ACTIVE → DEGRADED → DISABLED)14alter table sensors add column if not exists status text not null default 'ACTIVE';15alter table sensors add column if not exists validated_at timestamptz;16alter table sensors add column if not exists priority smallint not null default 2; -- 0 critical … 3 low17update sensors set status = case when not enabled then 'DISABLED' when health in ('ERROR','RATE_LIMITED') then 'DEGRADED' else 'ACTIVE' end where status = 'ACTIVE';1819-- ---- Changes: semantic class -------------------------------------------------------------20alter table changes add column if not exists change_class text; -- meaningful | pricing | policy | product | personnel | cosmetic | navigation | timestamp | advertisement | boilerplate21alter table changes add column if not exists field_changes jsonb; -- [{label, before, after, delta_pct}]2223-- ---- Events: richer scores & provenance ----------------------------------------------------24alter table events add column if not exists fingerprint text; -- deterministic (sensor + canonical hashes) → idempotent ingestion25alter table events add column if not exists signal_score real; -- WebSensor Signal Score 0–10026alter table events add column if not exists velocity_score real not null default 0;27alter table events add column if not exists impact_score real not null default 0;28alter table events add column if not exists anomaly_score real not null default 0;29alter table events add column if not exists change_class text;30alter table events add column if not exists first_party boolean not null default true;31alter table events add column if not exists country text;32alter table events add column if not exists language text;33alter table events add column if not exists canonical_url text;34alter table events add column if not exists field_changes jsonb;35alter table events add column if not exists score_reasons jsonb not null default '[]'; -- explainable score decomposition36create unique index if not exists events_fingerprint_uidx on events (fingerprint) where fingerprint is not null;37create index if not exists events_signal_idx on events (signal_score desc, detected_at desc) where signal_score is not null;38create index if not exists events_country_idx on events (country, detected_at desc) where country is not null;39create index if not exists events_first_party_idx on events (detected_at desc) where first_party;40create index if not exists events_class_idx on events (change_class) where change_class is not null;4142-- ---- Clusters: propagation & confirmation -------------------------------------------------43alter table event_clusters add column if not exists slug text;44alter table event_clusters add column if not exists source_count integer not null default 1;45alter table event_clusters add column if not exists first_party_count integer not null default 0;46alter table event_clusters add column if not exists external_count integer not null default 0;47alter table event_clusters add column if not exists velocity real not null default 0; -- signals per hour over the active window48alter table event_clusters add column if not exists state text not null default 'watching'; -- breaking | developing | confirmed | watching | closed49alter table event_clusters add column if not exists lead_time_ms bigint; -- first-party detection → first external report50alter table event_clusters add column if not exists first_party_at timestamptz;51alter table event_clusters add column if not exists first_external_at timestamptz;52alter table event_clusters add column if not exists timeline jsonb not null default '[]'; -- [{at, event_id, source_id, first_party, kind}]53create unique index if not exists event_clusters_slug_uidx on event_clusters (slug) where slug is not null;54create index if not exists event_clusters_state_idx on event_clusters (state, last_at desc);55update event_clusters set slug = id where slug is null;5657-- ---- Entity daily activity (heatmaps, baselines, anomaly) ---------------------------------58create table if not exists entity_daily (59 entity_id text not null references entities(id) on delete cascade,60 day date not null,61 events integer not null default 0,62 silent integer not null default 0,63 breaking integer not null default 0,64 max_importance real not null default 0,65 primary key (entity_id, day)66);67-- Backfill from the existing event history (idempotent).68insert into entity_daily (entity_id, day, events, silent, breaking, max_importance)69select ee.entity_id, (e.detected_at at time zone 'UTC')::date, count(*), sum(case when e.silent_change then 1 else 0 end), sum(case when e.importance >= 80 then 1 else 0 end), max(e.importance)70from events e join event_entities ee on ee.event_id = e.id71group by ee.entity_id, (e.detected_at at time zone 'UTC')::date72on conflict (entity_id, day) do update set events = excluded.events, silent = excluded.silent, breaking = excluded.breaking, max_importance = excluded.max_importance;7374-- Source daily activity (source quality, heatmaps)75create table if not exists source_daily (76 source_id text not null references sources(id) on delete cascade,77 day date not null,78 checks integer not null default 0,79 not_modified integer not null default 0,80 errors integer not null default 0,81 raw_changes integer not null default 0,82 events integer not null default 0,83 primary key (source_id, day)84);8586-- ---- Bookmarks (anonymous owner token, phase 1) -------------------------------------------87create table if not exists bookmarks (88 owner_token text not null,89 event_id text not null references events(id) on delete cascade,90 note text,91 created_at timestamptz not null default now(),92 primary key (owner_token, event_id)93);94create index if not exists bookmarks_owner_idx on bookmarks (owner_token, created_at desc);9596-- ---- Alerts: channel configuration + delivery log ------------------------------------------97alter table alerts add column if not exists channel_config jsonb not null default '{}'; -- webhook: {url, secret}98alter table alerts add column if not exists fired_count integer not null default 0;99alter table notifications add column if not exists channel text not null default 'web';100alter table notifications add column if not exists status text not null default 'queued'; -- queued | delivered | failed101alter table notifications add column if not exists delivered_at timestamptz;102alter table notifications add column if not exists error text;103create index if not exists notifications_alert_idx on notifications (alert_id, created_at desc);104create index if not exists notifications_status_idx on notifications (status) where status = 'queued';105106-- ---- Saved views (URL filter presets) ------------------------------------------------------107create table if not exists saved_views (108 id text primary key,109 owner_token text not null,110 name text not null,111 query text not null,112 created_at timestamptz not null default now()113);114create index if not exists saved_views_owner_idx on saved_views (owner_token);115116-- ---- Watchlist item kinds are free text already; nothing to migrate.117