spb/company-atlas
Public
Python 66.3%
TypeScript 22.7%
JavaScript 8.6%
HTML 1.4%
CSS 0.7%
1"""Company Atlas — initial schema (spec §6–7, §22, §26, §70–76).23Historical-first: nothing here is ever overwritten by the pipeline — observations, snapshots, changes and events are append-only;4entities (jobs, people, products, plans, locations) carry first_seen / last_seen / removed_at and a status instead of being deleted.56Revision ID: 00017Revises:8"""9from __future__ import annotations1011from alembic import op1213revision = "0001"14down_revision = None15branch_labels = None16depends_on = None1718SQL = r"""19create extension if not exists pg_trgm;20create extension if not exists "uuid-ossp";2122-- ============================================================================================================ reference23create table if not exists industries (24 slug text primary key,25 name text not null,26 parent_slug text references industries(slug),27 description text,28 keywords text[] not null default '{}',29 sort_order int not null default 10030);3132create table if not exists countries (33 code char(2) primary key,34 name text not null,35 region text,36 subregion text,37 lat double precision,38 lon double precision39);4041-- ============================================================================================================ companies42create table if not exists companies (43 id text primary key,44 slug text not null unique,45 legal_name text,46 display_name text not null,47 canonical_domain text not null unique,48 website text not null,49 description text,50 industries text[] not null default '{}',51 industry_primary text references industries(slug),52 country char(2) references countries(code),53 hq_city text,54 hq_region text,55 founded_year int,56 company_type text,57 public_company boolean not null default false,58 ticker text,59 exchange text,60 employees_band text,61 employees int,62 wikidata_id text unique,63 lei text,64 sec_cik text,65 logo_url text,66 status text not null default 'ACTIVE',67 onboarding_status text not null default 'pending',68 onboarding_error text,69 importance real not null default 0.2,70 tier smallint not null default 4,71 indexed boolean not null default false,72 source_meta jsonb not null default '{}'::jsonb,73 stats jsonb not null default '{}'::jsonb,74 discovered_at timestamptz not null default now(),75 first_observed_at timestamptz,76 last_observed_at timestamptz,77 last_change_at timestamptz,78 last_event_at timestamptz,79 created_at timestamptz not null default now(),80 updated_at timestamptz not null default now(),81 search tsvector generated always as (82 setweight(to_tsvector('simple', coalesce(display_name, '')), 'A') ||83 setweight(to_tsvector('simple', coalesce(legal_name, '')), 'B') ||84 setweight(to_tsvector('simple', coalesce(canonical_domain, '')), 'B') ||85 setweight(to_tsvector('english', coalesce(description, '')), 'C')) stored86);87create index if not exists companies_search_idx on companies using gin (search);88create index if not exists companies_name_trgm_idx on companies using gin (display_name gin_trgm_ops);89create index if not exists companies_domain_trgm_idx on companies using gin (canonical_domain gin_trgm_ops);90create index if not exists companies_country_idx on companies (country);91create index if not exists companies_industry_idx on companies using gin (industries);92create index if not exists companies_importance_idx on companies (importance desc);93create index if not exists companies_onboarding_idx on companies (onboarding_status) where onboarding_status <> 'active';94create index if not exists companies_last_event_idx on companies (last_event_at desc nulls last);9596create table if not exists company_aliases (97 company_id text not null references companies(id) on delete cascade,98 alias text not null,99 alias_norm text not null,100 kind text not null default 'alias', -- alias | legal | brand | former | ticker | native101 source text,102 primary key (company_id, alias_norm)103);104create index if not exists company_aliases_norm_idx on company_aliases (alias_norm);105106create table if not exists domains (107 id text primary key,108 company_id text not null references companies(id) on delete cascade,109 domain text not null,110 kind text not null default 'primary', -- primary | alias | redirect | subdomain | former111 first_seen_at timestamptz not null default now(),112 last_seen_at timestamptz not null default now(),113 status text not null default 'active',114 unique (domain, company_id)115);116create index if not exists domains_domain_idx on domains (domain);117118create table if not exists company_relationships (119 id text primary key,120 from_company_id text not null references companies(id) on delete cascade,121 to_company_id text references companies(id) on delete set null,122 to_name text,123 kind text not null, -- PARENT_OF | SUBSIDIARY_OF | ACQUIRED_BY | ACQUIRED | PARTNER_OF | COMPETITOR_OF | INVESTOR_IN | BRAND_OF124 valid_from date,125 valid_to date,126 first_seen_at timestamptz not null default now(),127 last_seen_at timestamptz not null default now(),128 source_url text,129 confidence real not null default 0.5,130 provenance jsonb not null default '{}'::jsonb131);132create index if not exists company_relationships_from_idx on company_relationships (from_company_id, kind);133create index if not exists company_relationships_to_idx on company_relationships (to_company_id, kind);134135-- ============================================================================================================ connectors / sensors136create table if not exists connectors (137 id text primary key, -- generic-html-v1, greenhouse-v1 …138 name text not null,139 version text not null,140 category text not null, -- surface141 fetch_mode text not null default 'http',142 supports_discovery boolean not null default false,143 supports_incremental boolean not null default true,144 default_interval_s int not null default 86400,145 enabled boolean not null default true,146 created_at timestamptz not null default now(),147 updated_at timestamptz not null default now(),148 stats jsonb not null default '{}'::jsonb149);150151create table if not exists sensors (152 id text primary key,153 company_id text not null references companies(id) on delete cascade,154 surface text not null,155 connector_id text not null references connectors(id),156 url text not null,157 canonical_url text not null,158 domain text not null,159 discovery_confidence real not null default 0.5,160 discovery_method text, -- nav | sitemap | robots | pattern | ats | feed | manual | event161 quality_score real not null default 50,162 status text not null default 'pending',163 tier char(1) not null default 'D',164 base_interval_s int not null default 86400,165 current_interval_s int not null default 86400,166 next_run_at timestamptz not null default now(),167 last_run_at timestamptz,168 last_success_at timestamptz,169 last_change_at timestamptz,170 last_meaningful_change_at timestamptz,171 last_status int,172 last_failure_class text,173 last_error text,174 consecutive_failures int not null default 0,175 consecutive_unchanged int not null default 0,176 etag text,177 last_modified text,178 last_content_hash text,179 last_normalized_hash text,180 last_structural_hash text,181 last_snapshot_id text,182 snapshot_count int not null default 0,183 observation_count int not null default 0,184 change_count int not null default 0,185 meaningful_change_count int not null default 0,186 event_count int not null default 0,187 config jsonb not null default '{}'::jsonb,188 priority real not null default 0.5,189 claimed_by text,190 claimed_at timestamptz,191 created_at timestamptz not null default now(),192 updated_at timestamptz not null default now(),193 retired_at timestamptz,194 unique (company_id, canonical_url)195);196create index if not exists sensors_due_idx on sensors (next_run_at) where status in ('active', 'failing', 'pending');197create index if not exists sensors_company_idx on sensors (company_id, surface);198create index if not exists sensors_domain_idx on sensors (domain);199create index if not exists sensors_status_idx on sensors (status);200create index if not exists sensors_connector_idx on sensors (connector_id);201create index if not exists sensors_claimed_idx on sensors (claimed_at) where claimed_by is not null;202203create table if not exists domain_budgets (204 domain text primary key,205 max_concurrency int not null default 2,206 requests_per_minute int not null default 20,207 daily_budget int not null default 600,208 browser_budget int not null default 20,209 used_today int not null default 0,210 browser_used_today int not null default 0,211 budget_day date not null default current_date,212 crawl_delay_s real,213 blocked_until timestamptz,214 block_reason text,215 notes text,216 updated_at timestamptz not null default now()217);218219-- ============================================================================================================ observations / snapshots220create table if not exists observations (221 id text primary key,222 sensor_id text not null references sensors(id) on delete cascade,223 company_id text not null references companies(id) on delete cascade,224 fetched_at timestamptz not null default now(),225 status_code int,226 duration_ms int,227 transport text not null default 'http',228 final_url text,229 redirects int not null default 0,230 not_modified boolean not null default false,231 changed boolean not null default false,232 failure_class text,233 error text,234 content_hash text,235 normalized_hash text,236 structural_hash text,237 object_key text,238 size_bytes int,239 content_type text,240 connector_version text,241 collection_method text not null default 'live',242 worker text243);244create index if not exists observations_sensor_idx on observations (sensor_id, fetched_at desc);245create index if not exists observations_company_idx on observations (company_id, fetched_at desc);246create index if not exists observations_time_idx on observations (fetched_at desc);247create index if not exists observations_failure_idx on observations (failure_class, fetched_at desc) where failure_class is not null;248249create table if not exists snapshots (250 id text primary key,251 sensor_id text not null references sensors(id) on delete cascade,252 company_id text not null references companies(id) on delete cascade,253 observation_id text references observations(id) on delete set null,254 previous_snapshot_id text,255 version_no int not null default 1,256 fetched_at timestamptz not null default now(),257 content_hash text not null,258 normalized_hash text not null,259 structural_hash text not null,260 object_key text, -- raw bytes261 text_key text, -- normalized text262 blocks_key text, -- JSON blocks263 extracted jsonb not null default '{}'::jsonb, -- structured fields (jobs, prices, people, locations, news, products, meta)264 extracted_summary jsonb not null default '{}'::jsonb, -- small counters for listings (job_count, plan_count …)265 title text,266 language text,267 size_bytes int,268 text_length int,269 block_count int,270 content_type text,271 connector_version text,272 collection_method text not null default 'live'273);274create index if not exists snapshots_sensor_idx on snapshots (sensor_id, fetched_at desc);275create index if not exists snapshots_company_idx on snapshots (company_id, fetched_at desc);276277create table if not exists changes (278 id text primary key,279 sensor_id text not null references sensors(id) on delete cascade,280 company_id text not null references companies(id) on delete cascade,281 surface text not null,282 snapshot_before text references snapshots(id) on delete set null,283 snapshot_after text not null references snapshots(id) on delete cascade,284 detected_at timestamptz not null default now(),285 significance real not null,286 kind text not null, -- noise | minor | meaningful | major | critical287 blocks_added int not null default 0,288 blocks_removed int not null default 0,289 blocks_modified int not null default 0,290 blocks_moved int not null default 0,291 text_delta_ratio real not null default 0,292 similarity real,293 diff jsonb not null default '{}'::jsonb, -- block-level diff (bounded)294 structured_delta jsonb not null default '{}'::jsonb, -- typed deltas (jobs added/removed, prices, people …)295 status text not null default 'pending', -- pending | processed | enriched | archived296 processed_at timestamptz,297 diff_version text not null default 'diff-v1'298);299create index if not exists changes_company_idx on changes (company_id, detected_at desc);300create index if not exists changes_sensor_idx on changes (sensor_id, detected_at desc);301create index if not exists changes_kind_idx on changes (kind, detected_at desc);302create index if not exists changes_pending_idx on changes (status) where status = 'pending';303304-- ============================================================================================================ events305create table if not exists event_clusters (306 id text primary key,307 company_id text not null references companies(id) on delete cascade,308 cluster_key text not null unique,309 event_type text not null,310 event_subtype text,311 title text,312 first_detected_at timestamptz not null default now(),313 last_detected_at timestamptz not null default now(),314 source_count int not null default 1,315 surfaces text[] not null default '{}',316 confidence real not null default 0.5,317 canonical_event_id text318);319320create table if not exists events (321 id text primary key,322 company_id text not null references companies(id) on delete cascade,323 sensor_id text references sensors(id) on delete set null,324 change_id text references changes(id) on delete set null,325 cluster_id text references event_clusters(id) on delete set null,326 surface text,327 event_type text not null,328 event_subtype text not null,329 importance real not null default 0.5,330 confidence real not null default 0.7,331 confidence_label text not null default 'LIKELY',332 title text not null,333 summary text,334 old_value text,335 new_value text,336 payload jsonb not null default '{}'::jsonb,337 entities jsonb not null default '{}'::jsonb, -- {jobs:[…], people:[…], products:[…], locations:[…], amounts:[…]}338 tags text[] not null default '{}',339 detected_at timestamptz not null default now(),340 effective_at timestamptz,341 published_at timestamptz,342 source_url text,343 snapshot_before text,344 snapshot_after text,345 language text,346 origin text not null default 'deterministic', -- deterministic | llm | hybrid | backfill347 model_provider text,348 model_name text,349 model_version text,350 prompt_version text,351 schema_version text not null default 'event-v1',352 status text not null default 'active',353 retracted_reason text,354 dedupe_key text unique,355 created_at timestamptz not null default now(),356 search tsvector generated always as (357 setweight(to_tsvector('english', coalesce(title, '')), 'A') ||358 setweight(to_tsvector('english', coalesce(summary, '')), 'B')) stored359);360create index if not exists events_company_idx on events (company_id, detected_at desc);361create index if not exists events_time_idx on events (detected_at desc) where status = 'active';362create index if not exists events_type_idx on events (event_type, detected_at desc);363create index if not exists events_subtype_idx on events (event_subtype, detected_at desc);364create index if not exists events_importance_idx on events (importance desc, detected_at desc);365create index if not exists events_search_idx on events using gin (search);366create index if not exists events_cluster_idx on events (cluster_id);367create index if not exists events_tags_idx on events using gin (tags);368369create table if not exists event_sources (370 event_id text not null references events(id) on delete cascade,371 sensor_id text references sensors(id) on delete set null,372 source_url text not null,373 snapshot_id text,374 surface text,375 detected_at timestamptz not null default now(),376 kind text not null default 'primary',377 primary key (event_id, source_url)378);379380-- ============================================================================================================ extracted entities381create table if not exists jobs (382 id text primary key,383 company_id text not null references companies(id) on delete cascade,384 sensor_id text references sensors(id) on delete set null,385 external_id text,386 fingerprint text not null,387 title text not null,388 department text,389 team text,390 location_text text,391 city text,392 region text,393 country char(2),394 remote boolean,395 employment_type text,396 seniority text,397 skills text[] not null default '{}',398 salary_min numeric,399 salary_max numeric,400 salary_currency text,401 salary_period text,402 url text,403 description_hash text,404 posted_at timestamptz,405 first_seen_at timestamptz not null default now(),406 last_seen_at timestamptz not null default now(),407 removed_at timestamptz,408 status text not null default 'open', -- open | no_longer_listed409 is_ai boolean not null default false,410 is_engineering boolean not null default false,411 raw jsonb not null default '{}'::jsonb,412 unique (company_id, fingerprint)413);414create index if not exists jobs_company_idx on jobs (company_id, status);415create index if not exists jobs_first_seen_idx on jobs (first_seen_at desc);416create index if not exists jobs_country_idx on jobs (country) where status = 'open';417create index if not exists jobs_ai_idx on jobs (company_id) where is_ai and status = 'open';418create index if not exists jobs_title_trgm_idx on jobs using gin (title gin_trgm_ops);419420create table if not exists people (421 id text primary key,422 company_id text not null references companies(id) on delete cascade,423 sensor_id text references sensors(id) on delete set null,424 name text not null,425 name_norm text not null,426 title text,427 role_category text, -- ceo | cfo | cto | coo | founder | president | chair | board | vp | head | other428 is_executive boolean not null default false,429 first_seen_at timestamptz not null default now(),430 last_seen_at timestamptz not null default now(),431 removed_at timestamptz,432 status text not null default 'listed', -- listed | no_longer_listed433 source_url text,434 unique (company_id, name_norm)435);436create index if not exists people_company_idx on people (company_id, status);437438create table if not exists products (439 id text primary key,440 company_id text not null references companies(id) on delete cascade,441 sensor_id text references sensors(id) on delete set null,442 name text not null,443 name_norm text not null,444 category text,445 description text,446 url text,447 first_seen_at timestamptz not null default now(),448 last_seen_at timestamptz not null default now(),449 removed_at timestamptz,450 status text not null default 'listed',451 unique (company_id, name_norm)452);453create index if not exists products_company_idx on products (company_id, status);454455create table if not exists pricing_plans (456 id text primary key,457 company_id text not null references companies(id) on delete cascade,458 sensor_id text references sensors(id) on delete set null,459 plan_name text not null,460 plan_norm text not null,461 currency text,462 billing_period text, -- month | year | one_time | usage | contact463 price numeric,464 price_text text,465 unit text,466 features jsonb not null default '[]'::jsonb,467 contact_sales boolean not null default false,468 version_no int not null default 1,469 valid_from timestamptz not null default now(),470 valid_to timestamptz,471 first_seen_at timestamptz not null default now(),472 last_seen_at timestamptz not null default now(),473 status text not null default 'current', -- current | superseded | removed474 source_url text475);476create index if not exists pricing_plans_company_idx on pricing_plans (company_id, status);477create index if not exists pricing_plans_current_idx on pricing_plans (company_id, plan_norm) where status = 'current';478479create table if not exists locations (480 id text primary key,481 company_id text not null references companies(id) on delete cascade,482 sensor_id text references sensors(id) on delete set null,483 kind text not null default 'office', -- headquarters | office | store | factory | warehouse | lab | data_center | other484 name text,485 name_norm text not null,486 city text,487 region text,488 country char(2),489 lat double precision,490 lon double precision,491 first_seen_at timestamptz not null default now(),492 last_seen_at timestamptz not null default now(),493 removed_at timestamptz,494 status text not null default 'listed',495 source_url text,496 unique (company_id, name_norm)497);498create index if not exists locations_company_idx on locations (company_id, status);499create index if not exists locations_country_idx on locations (country) where status = 'listed';500501create table if not exists news_items (502 id text primary key,503 company_id text not null references companies(id) on delete cascade,504 sensor_id text references sensors(id) on delete set null,505 url text not null,506 canonical_url text not null,507 title text not null,508 summary text,509 category text, -- press | blog | changelog | research | ir | other510 published_at timestamptz,511 first_seen_at timestamptz not null default now(),512 language text,513 entities jsonb not null default '{}'::jsonb,514 unique (company_id, canonical_url)515);516create index if not exists news_items_company_idx on news_items (company_id, coalesce(published_at, first_seen_at) desc);517create index if not exists news_items_time_idx on news_items (first_seen_at desc);518519-- ============================================================================================================ metrics520create table if not exists metrics_current (521 company_id text not null references companies(id) on delete cascade,522 metric text not null,523 value double precision not null,524 confidence real not null default 0.5,525 inputs jsonb not null default '{}'::jsonb,526 formula_version text not null,527 computed_at timestamptz not null default now(),528 primary key (company_id, metric)529);530create index if not exists metrics_current_metric_idx on metrics_current (metric, value desc);531532create table if not exists metric_series (533 company_id text not null references companies(id) on delete cascade,534 metric text not null,535 day date not null,536 value double precision not null,537 confidence real not null default 0.5,538 formula_version text not null,539 primary key (company_id, metric, day)540);541create index if not exists metric_series_metric_day_idx on metric_series (metric, day desc);542543create table if not exists company_daily (544 company_id text not null references companies(id) on delete cascade,545 day date not null,546 observations int not null default 0,547 changes int not null default 0,548 meaningful_changes int not null default 0,549 events int not null default 0,550 events_by_type jsonb not null default '{}'::jsonb,551 jobs_open int,552 jobs_new int not null default 0,553 jobs_removed int not null default 0,554 jobs_ai_open int,555 news_items int not null default 0,556 sensors_active int,557 primary key (company_id, day)558);559create index if not exists company_daily_day_idx on company_daily (day desc);560561create table if not exists baselines (562 company_id text not null references companies(id) on delete cascade,563 metric text not null,564 mean double precision not null,565 stddev double precision not null,566 samples int not null,567 window_days int not null,568 computed_at timestamptz not null default now(),569 primary key (company_id, metric)570);571572create table if not exists global_daily (573 day date primary key,574 companies_active int not null default 0,575 sensors_active int not null default 0,576 observations int not null default 0,577 changes int not null default 0,578 meaningful_changes int not null default 0,579 events int not null default 0,580 events_by_type jsonb not null default '{}'::jsonb,581 jobs_open int,582 jobs_new int not null default 0,583 jobs_removed int not null default 0,584 activity_index double precision, -- coverage-normalised Global Corporate Activity Index (100 = baseline)585 by_country jsonb not null default '{}'::jsonb,586 by_industry jsonb not null default '{}'::jsonb,587 computed_at timestamptz not null default now()588);589590create table if not exists signals (591 id text primary key,592 company_id text references companies(id) on delete cascade,593 scope text not null default 'company', -- company | industry | country | global594 scope_key text,595 kind text not null, -- hiring_surge | hiring_freeze | launch_buildup | expansion | pricing_migration | developer_push | enterprise_repositioning | ai_acceleration | abnormal_activity596 strength real not null,597 confidence real not null,598 title text not null,599 explanation text,600 evidence jsonb not null default '{}'::jsonb,601 window_days int not null default 30,602 detected_at timestamptz not null default now(),603 expires_at timestamptz,604 status text not null default 'active'605);606create index if not exists signals_company_idx on signals (company_id, detected_at desc);607create index if not exists signals_scope_idx on signals (scope, scope_key, detected_at desc);608609create table if not exists trends (610 term text not null,611 day date not null,612 mentions int not null default 0,613 companies int not null default 0,614 primary key (term, day)615);616617-- ============================================================================================================ operations618create table if not exists queue_jobs (619 id text primary key,620 kind text not null, -- discover | run_sensor | enrich_change | recompute_metrics | digest | repair_sensor621 key text not null unique, -- idempotency (sensor_id + window …)622 payload jsonb not null default '{}'::jsonb,623 priority real not null default 0.5,624 run_at timestamptz not null default now(),625 locked_at timestamptz,626 locked_by text,627 attempts int not null default 0,628 max_attempts int not null default 3,629 status text not null default 'pending', -- pending | running | done | failed | dead630 last_error text,631 created_at timestamptz not null default now(),632 finished_at timestamptz633);634create index if not exists queue_jobs_due_idx on queue_jobs (kind, priority desc, run_at) where status = 'pending';635create index if not exists queue_jobs_status_idx on queue_jobs (status, kind);636637create table if not exists llm_jobs (638 id text primary key,639 kind text not null, -- classify_change | summarize_event | extract_entities | classify_industry640 ref_id text not null,641 company_id text references companies(id) on delete cascade,642 model text,643 prompt_version text,644 status text not null default 'pending',645 attempts int not null default 0,646 request_tokens int,647 response_tokens int,648 latency_ms int,649 result jsonb,650 error text,651 created_at timestamptz not null default now(),652 started_at timestamptz,653 finished_at timestamptz654);655create index if not exists llm_jobs_status_idx on llm_jobs (status, created_at);656create index if not exists llm_jobs_ref_idx on llm_jobs (ref_id);657658create table if not exists failures (659 id text primary key,660 sensor_id text references sensors(id) on delete cascade,661 company_id text references companies(id) on delete cascade,662 at timestamptz not null default now(),663 failure_class text not null,664 status_code int,665 message text,666 url text667);668create index if not exists failures_time_idx on failures (at desc);669create index if not exists failures_sensor_idx on failures (sensor_id, at desc);670671create table if not exists crawl_runs (672 id text primary key,673 kind text not null, -- scheduler_tick | onboarding | metrics | daily | backup | repair674 worker text,675 started_at timestamptz not null default now(),676 finished_at timestamptz,677 stats jsonb not null default '{}'::jsonb,678 error text679);680create index if not exists crawl_runs_kind_idx on crawl_runs (kind, started_at desc);681682create table if not exists review_queue (683 id text primary key,684 kind text not null, -- company_merge | major_event | low_confidence | sensor_migration | legal_sensitive | unexpected_activity | blocked_source685 ref_id text,686 company_id text references companies(id) on delete cascade,687 payload jsonb not null default '{}'::jsonb,688 status text not null default 'open', -- open | accepted | rejected | resolved689 resolution text,690 created_at timestamptz not null default now(),691 resolved_at timestamptz692);693create index if not exists review_queue_open_idx on review_queue (kind, created_at) where status = 'open';694695create table if not exists cost_ledger (696 day date not null,697 dimension text not null, -- fetch | browser | llm | storage_gb | event698 key text not null default '',699 units double precision not null default 0,700 cost_estimate double precision not null default 0,701 primary key (day, dimension, key)702);703704-- ============================================================================================================ users-light (no accounts required)705create table if not exists owners (706 token_hash text primary key,707 created_at timestamptz not null default now(),708 last_seen_at timestamptz not null default now(),709 email text,710 plan text not null default 'free'711);712713create table if not exists watchlists (714 id text primary key,715 owner_hash text not null references owners(token_hash) on delete cascade,716 name text not null default 'My Watchlist',717 created_at timestamptz not null default now()718);719create table if not exists watchlist_items (720 watchlist_id text not null references watchlists(id) on delete cascade,721 company_id text not null references companies(id) on delete cascade,722 added_at timestamptz not null default now(),723 primary key (watchlist_id, company_id)724);725726create table if not exists alerts (727 id text primary key,728 owner_hash text not null references owners(token_hash) on delete cascade,729 company_id text references companies(id) on delete cascade,730 name text not null,731 condition jsonb not null, -- {event_types:[…], min_importance, metrics:{activity_score:{gt:80}}, industries, countries}732 channel text not null default 'web', -- web | email | webhook733 target text,734 enabled boolean not null default true,735 created_at timestamptz not null default now(),736 last_fired_at timestamptz737);738create table if not exists alert_deliveries (739 id text primary key,740 alert_id text not null references alerts(id) on delete cascade,741 event_id text references events(id) on delete cascade,742 delivered_at timestamptz not null default now(),743 channel text not null,744 status text not null default 'queued',745 detail text746);747create index if not exists alert_deliveries_alert_idx on alert_deliveries (alert_id, delivered_at desc);748749create table if not exists api_keys (750 id text primary key,751 key_hash text not null unique,752 prefix text not null,753 name text not null,754 tier text not null default 'authenticated', -- anonymous | authenticated | paid | internal755 owner_hash text references owners(token_hash) on delete set null,756 created_at timestamptz not null default now(),757 last_used_at timestamptz,758 request_count bigint not null default 0,759 revoked_at timestamptz760);761762create table if not exists settings_kv (763 key text primary key,764 value jsonb not null,765 updated_at timestamptz not null default now()766);767insert into settings_kv (key, value) values ('dataset_started_at', to_jsonb(now())) on conflict (key) do nothing;768"""769770771def upgrade() -> None:772 for stmt in _split(SQL):773 op.execute(stmt)774775776def downgrade() -> None:777 raise RuntimeError("forward-only migrations: historical data is never dropped")778779780def _split(sql: str) -> list[str]:781 out: list[str] = []782 buf: list[str] = []783 for line in sql.splitlines():784 buf.append(line)785 if line.rstrip().endswith(";"):786 stmt = "\n".join(buf).strip()787 body = "\n".join(x for x in stmt.splitlines() if not x.strip().startswith("--")).strip()788 if body and body != ";":789 out.append(stmt)790 buf = []791 return out792