TypeScript 55.4%
Python 43.2%
SQL 1.2%
1-- WebSensor core schema (PostgreSQL 17). All timestamps are UTC (timestamptz).23create table if not exists sources (4 id text primary key,5 name text not null,6 domain text not null,7 homepage text,8 description text,9 categories text[] not null default '{}',10 tier text not null default 'B',11 importance_weight real not null default 1.0,12 discover jsonb not null default '{}',13 fallback jsonb not null default '{}',14 enabled boolean not null default true,15 robots_checked_at timestamptz,16 terms_reviewed_at timestamptz,17 allowed_methods text[] not null default '{}',18 rate_limit_per_min integer,19 notes text,20 created_at timestamptz not null default now(),21 updated_at timestamptz not null default now()22);23create index if not exists sources_domain_idx on sources (domain);24create index if not exists sources_categories_idx on sources using gin (categories);2526create table if not exists sensors (27 id text primary key,28 source_id text not null references sources(id) on delete cascade,29 name text not null,30 url text not null,31 type text not null,32 connector text not null,33 tier text not null default 'B',34 importance_weight real not null default 1.0,35 config jsonb not null default '{}',36 base_interval_seconds integer,37 enabled boolean not null default true,38 health text not null default 'UP',39 next_check_at timestamptz not null default now(),40 last_check_at timestamptz,41 last_change_at timestamptz,42 last_event_at timestamptz,43 last_status integer,44 last_error text,45 etag text,46 last_modified text,47 state jsonb,48 last_snapshot_id text,49 consecutive_errors integer not null default 0,50 total_runs integer not null default 0,51 total_not_modified integer not null default 0,52 raw_changes integer not null default 0,53 meaningful_changes integer not null default 0,54 avg_latency_ms integer,55 created_at timestamptz not null default now(),56 updated_at timestamptz not null default now()57);58create index if not exists sensors_next_check_idx on sensors (next_check_at) where enabled;59create index if not exists sensors_source_idx on sensors (source_id);60create index if not exists sensors_url_idx on sensors (url);6162create table if not exists sensor_runs (63 id text primary key,64 sensor_id text not null references sensors(id) on delete cascade,65 started_at timestamptz not null,66 finished_at timestamptz,67 http_status integer,68 outcome text not null,69 error text,70 duration_ms integer,71 bytes integer,72 fetch_method text,73 snapshot_id text74);75create index if not exists sensor_runs_sensor_idx on sensor_runs (sensor_id, started_at desc);76create index if not exists sensor_runs_started_idx on sensor_runs (started_at desc);7778create table if not exists snapshots (79 id text primary key,80 sensor_id text not null references sensors(id) on delete cascade,81 url text not null,82 captured_at timestamptz not null,83 http_status integer,84 content_type text,85 content_length integer,86 content_hash text not null,87 canonical_hash text not null,88 semantic_hash text,89 etag text,90 last_modified text,91 storage_key text,92 canonical_storage_key text,93 parser_version text not null,94 fetch_duration_ms integer,95 fetch_method text,96 mode text not null,97 title text,98 published_at timestamptz,99 extraction_confidence real,100 extra jsonb101);102create index if not exists snapshots_sensor_idx on snapshots (sensor_id, captured_at desc);103create index if not exists snapshots_hash_idx on snapshots (canonical_hash);104105create table if not exists changes (106 id text primary key,107 sensor_id text not null references sensors(id) on delete cascade,108 old_snapshot_id text references snapshots(id),109 new_snapshot_id text not null references snapshots(id),110 detected_at timestamptz not null,111 kind text not null,112 diff jsonb not null,113 diff_storage_key text,114 signal real not null,115 noise_ratio real not null,116 magnitude real not null,117 heuristic jsonb not null,118 meaningful boolean not null default false,119 event_id text120);121create index if not exists changes_sensor_idx on changes (sensor_id, detected_at desc);122create index if not exists changes_detected_idx on changes (detected_at desc);123124create table if not exists event_clusters (125 id text primary key,126 title text not null,127 summary text,128 primary_event_id text,129 entity_ids text[] not null default '{}',130 categories text[] not null default '{}',131 event_count integer not null default 0,132 max_importance real not null default 0,133 first_at timestamptz not null,134 last_at timestamptz not null135);136create index if not exists event_clusters_last_idx on event_clusters (last_at desc);137138create table if not exists events (139 id text primary key,140 slug text not null unique,141 sensor_id text not null references sensors(id) on delete cascade,142 source_id text not null references sources(id) on delete cascade,143 cluster_id text references event_clusters(id),144 change_id text references changes(id),145 old_snapshot_id text references snapshots(id),146 new_snapshot_id text references snapshots(id),147 url text not null,148 event_type text not null,149 title text not null,150 summary text not null,151 why_it_matters text,152 importance real not null,153 importance_components jsonb not null default '{}',154 confidence real not null,155 novelty real not null,156 categories text[] not null default '{}',157 keywords text[] not null default '{}',158 silent_change boolean not null default false,159 evidence_label text not null default 'OBSERVED',160 published_at timestamptz,161 observed_from timestamptz,162 detected_at timestamptz not null,163 processed_at timestamptz not null,164 published_to_feed_at timestamptz,165 detection_latency_ms integer,166 processing_latency_ms integer,167 processing_version text not null,168 interpretation jsonb not null default '{}',169 search tsvector generated always as (170 setweight(to_tsvector('english'::regconfig, coalesce(title, '')), 'A') ||171 setweight(to_tsvector('english'::regconfig, coalesce(summary, '')), 'B') ||172 setweight(array_to_tsvector(coalesce(keywords, '{}'::text[])), 'C')173 ) stored174);175create index if not exists events_detected_idx on events (detected_at desc);176create index if not exists events_importance_idx on events (importance desc, detected_at desc);177create index if not exists events_source_idx on events (source_id, detected_at desc);178create index if not exists events_sensor_idx on events (sensor_id, detected_at desc);179create index if not exists events_type_idx on events (event_type);180create index if not exists events_cluster_idx on events (cluster_id);181create index if not exists events_categories_idx on events using gin (categories);182create index if not exists events_silent_idx on events (detected_at desc) where silent_change;183create index if not exists events_search_idx on events using gin (search);184185create table if not exists interpretations (186 id bigserial primary key,187 event_id text not null references events(id) on delete cascade,188 version integer not null,189 model text not null,190 payload jsonb not null,191 created_at timestamptz not null default now(),192 unique (event_id, version)193);194195create table if not exists entities (196 id text primary key,197 name text not null,198 type text not null,199 description text,200 domain text,201 homepage text,202 importance real not null default 50,203 categories text[] not null default '{}',204 parent_id text references entities(id),205 metadata jsonb not null default '{}',206 event_count integer not null default 0,207 last_event_at timestamptz,208 created_at timestamptz not null default now(),209 search tsvector generated always as (210 setweight(to_tsvector('simple'::regconfig, coalesce(name, '')), 'A') ||211 setweight(to_tsvector('english'::regconfig, coalesce(description, '')), 'B')212 ) stored213);214create index if not exists entities_type_idx on entities (type);215create index if not exists entities_search_idx on entities using gin (search);216create index if not exists entities_domain_idx on entities (domain);217218create table if not exists entity_aliases (219 alias text primary key,220 entity_id text not null references entities(id) on delete cascade221);222create index if not exists entity_aliases_entity_idx on entity_aliases (entity_id);223224create table if not exists event_entities (225 event_id text not null references events(id) on delete cascade,226 entity_id text not null references entities(id) on delete cascade,227 role text not null default 'subject',228 primary key (event_id, entity_id)229);230create index if not exists event_entities_entity_idx on event_entities (entity_id);231232create table if not exists source_entities (233 source_id text not null references sources(id) on delete cascade,234 entity_id text not null references entities(id) on delete cascade,235 primary key (source_id, entity_id)236);237238create table if not exists entity_relations (239 from_id text not null references entities(id) on delete cascade,240 relation text not null,241 to_id text not null references entities(id) on delete cascade,242 metadata jsonb not null default '{}',243 primary key (from_id, relation, to_id)244);245246create table if not exists urls (247 url text primary key,248 domain text not null,249 source_id text references sources(id) on delete cascade,250 sensor_id text references sensors(id) on delete set null,251 first_seen_at timestamptz not null default now(),252 last_seen_at timestamptz not null default now(),253 status text not null default 'active',254 missing_count integer not null default 0,255 snapshot_count integer not null default 0,256 change_count integer not null default 0257);258create index if not exists urls_domain_idx on urls (domain);259create index if not exists urls_sensor_idx on urls (sensor_id);260261create table if not exists url_history (262 id bigserial primary key,263 url text not null,264 at timestamptz not null default now(),265 kind text not null,266 snapshot_id text,267 change_id text,268 event_id text,269 note text270);271create index if not exists url_history_url_idx on url_history (url, at desc);272273create table if not exists watchlists (274 id text primary key,275 owner_token text not null,276 name text not null,277 created_at timestamptz not null default now()278);279create index if not exists watchlists_owner_idx on watchlists (owner_token);280281create table if not exists watchlist_items (282 watchlist_id text not null references watchlists(id) on delete cascade,283 kind text not null,284 value text not null,285 added_at timestamptz not null default now(),286 primary key (watchlist_id, kind, value)287);288289create table if not exists alerts (290 id text primary key,291 owner_token text not null,292 name text not null,293 rule jsonb not null,294 channel text not null default 'web',295 enabled boolean not null default true,296 created_at timestamptz not null default now(),297 last_fired_at timestamptz298);299create index if not exists alerts_owner_idx on alerts (owner_token);300301create table if not exists notifications (302 id bigserial primary key,303 alert_id text not null references alerts(id) on delete cascade,304 event_id text not null references events(id) on delete cascade,305 created_at timestamptz not null default now(),306 read_at timestamptz307);308309create table if not exists connector_health (310 connector text primary key,311 status text not null default 'UP',312 runs_24h integer not null default 0,313 errors_24h integer not null default 0,314 success_rate real,315 avg_latency_ms integer,316 changes_24h integer not null default 0,317 events_24h integer not null default 0,318 last_success_at timestamptz,319 last_error_at timestamptz,320 last_error text,321 http_codes jsonb not null default '{}',322 rate_limit_until timestamptz,323 updated_at timestamptz not null default now()324);325326create table if not exists discovery_candidates (327 id text primary key,328 source_id text not null references sources(id) on delete cascade,329 url text not null,330 kind text not null,331 evidence text,332 score jsonb not null default '{}',333 status text not null default 'candidate',334 found_at timestamptz not null default now(),335 unique (source_id, url)336);337338create table if not exists metrics_daily (339 day date primary key,340 checks bigint not null default 0,341 not_modified bigint not null default 0,342 bytes bigint not null default 0,343 raw_changes bigint not null default 0,344 events bigint not null default 0,345 silent_events bigint not null default 0,346 errors bigint not null default 0,347 llm_calls bigint not null default 0,348 llm_input_tokens bigint not null default 0,349 llm_output_tokens bigint not null default 0350);351352create table if not exists llm_usage (353 id bigserial primary key,354 at timestamptz not null default now(),355 model text not null,356 purpose text not null,357 input_tokens integer not null,358 output_tokens integer not null,359 event_id text,360 ok boolean not null default true361);362