HTML 77.2%
TypeScript 10.5%
Python 9.6%
JavaScript 2.5%
1"""AI Atlas initial schema — entities, aliases, identifiers, sources, connectors, documents, snapshots, claims, relations,2change events, benchmark results, prices, jobs, LLM jobs, review queue, stats.34Revision ID: 00015"""6from __future__ import annotations78from alembic import op910revision = "0001"11down_revision = None12branch_labels = None13depends_on = None1415SQL = r"""16create extension if not exists pg_trgm;17create extension if not exists "uuid-ossp";18do $$ begin19 create extension if not exists vector;20exception when others then21 raise notice 'pgvector unavailable: %', sqlerrm;22end $$;2324-- ---------------------------------------------------------------------------------------------- reference: sources & connectors25create table if not exists sources (26 id text primary key,27 key text not null unique, -- e.g. 'openai.com', 'huggingface.co', 'arxiv.org'28 name text not null,29 domain text not null,30 organization_id text, -- entity id of the owning organization (domain trust graph)31 tier smallint not null default 2, -- 1 official primary, 2 quality secondary, 3 community, 4 unverified32 kind text not null default 'website', -- website|docs|feed|repository|registry|dataset|leaderboard|regulator33 category text not null default 'lab',34 base_url text,35 robots_policy text not null default 'respect', -- respect|documented-exception36 rate_limit_per_min integer not null default 30,37 crawl_interval_s integer not null default 86400,38 enabled boolean not null default true,39 priority smallint not null default 2, -- 0 = P040 notes text,41 meta jsonb not null default '{}'::jsonb,42 created_at timestamptz not null default now(),43 updated_at timestamptz not null default now()44);4546create table if not exists connectors (47 name text primary key,48 source_id text references sources(id),49 label text not null,50 description text,51 enabled boolean not null default true,52 priority smallint not null default 2,53 interval_seconds integer not null default 3600,54 min_interval_seconds integer not null default 900,55 max_interval_seconds integer not null default 604800,56 parser_version text not null default '1',57 rate_limit_per_min integer not null default 30,58 owner text not null default 'ai-atlas',59 expected_min_records integer not null default 0, -- breakage detection: fewer records than this = suspected failure60 last_attempt_at timestamptz,61 last_success_at timestamptz,62 last_change_at timestamptz,63 next_run_at timestamptz,64 last_duration_ms integer,65 consecutive_failures integer not null default 0,66 consecutive_unchanged integer not null default 0,67 circuit_open_until timestamptz,68 health text not null default 'unknown', -- ok|degraded|failing|disabled|unknown69 meta jsonb not null default '{}'::jsonb,70 created_at timestamptz not null default now(),71 updated_at timestamptz not null default now()72);7374create table if not exists connector_runs (75 id text primary key,76 connector_name text not null references connectors(name),77 started_at timestamptz not null,78 finished_at timestamptz,79 status text not null, -- running|success|unchanged|failed|skipped|suspect80 duration_ms integer,81 docs_discovered integer not null default 0,82 docs_fetched integer not null default 0,83 docs_changed integer not null default 0,84 docs_unchanged integer not null default 0,85 docs_failed integer not null default 0,86 entities_created integer not null default 0,87 entities_updated integer not null default 0,88 claims_written integer not null default 0,89 relations_written integer not null default 0,90 events_emitted integer not null default 0,91 error text,92 meta jsonb not null default '{}'::jsonb93);94create index if not exists connector_runs_name_idx on connector_runs (connector_name, started_at desc);9596create table if not exists connector_errors (97 id bigserial primary key,98 connector_name text not null,99 run_id text,100 url text,101 error_type text not null,102 message text not null,103 created_at timestamptz not null default now()104);105create index if not exists connector_errors_name_idx on connector_errors (connector_name, created_at desc);106107-- ---------------------------------------------------------------------------------------------- entities (unified graph)108create table if not exists entities (109 id text primary key,110 entity_type text not null,111 canonical_name text not null,112 slug text not null unique,113 description text,114 status text not null default 'active', -- active|deprecated|retired|announced|unknown|merged115 organization_id text references entities(id),116 attributes jsonb not null default '{}'::jsonb, -- current value per property (materialised from claims)117 provenance jsonb not null default '{}'::jsonb, -- property -> {source, snapshot_id, url, observed_at, tier, confidence}118 quality jsonb not null default '{}'::jsonb, -- source_count, primary_source_ratio, freshness, completeness, agreement, score119 counts jsonb not null default '{}'::jsonb, -- cached relation / claim / event counts120 first_seen_at timestamptz not null default now(),121 last_seen_at timestamptz not null default now(),122 merged_into text references entities(id),123 search tsvector,124 created_at timestamptz not null default now(),125 updated_at timestamptz not null default now()126);127create index if not exists entities_type_idx on entities (entity_type, canonical_name);128create index if not exists entities_org_idx on entities (organization_id);129create index if not exists entities_search_idx on entities using gin (search);130create index if not exists entities_name_trgm_idx on entities using gin (canonical_name gin_trgm_ops);131create index if not exists entities_attrs_idx on entities using gin (attributes jsonb_path_ops);132create index if not exists entities_updated_idx on entities (updated_at desc);133create index if not exists entities_first_seen_idx on entities (first_seen_at desc);134135create table if not exists entity_aliases (136 id bigserial primary key,137 entity_id text not null references entities(id) on delete cascade,138 alias text not null,139 alias_norm text not null,140 kind text not null default 'alias', -- alias|former_name|abbreviation|localized|typo141 snapshot_id text,142 created_at timestamptz not null default now(),143 unique (entity_id, alias_norm)144);145create index if not exists entity_aliases_norm_idx on entity_aliases (alias_norm);146147create table if not exists entity_identifiers (148 id bigserial primary key,149 entity_id text not null references entities(id) on delete cascade,150 scheme text not null, -- hf_repo|github_repo|arxiv|doi|openrouter|domain|pypi|wikidata|url|provider_model_id151 value text not null,152 snapshot_id text,153 created_at timestamptz not null default now(),154 unique (scheme, value)155);156create index if not exists entity_identifiers_entity_idx on entity_identifiers (entity_id);157158-- ---------------------------------------------------------------------------------------------- documents & snapshots (raw archive)159create table if not exists documents (160 id text primary key,161 source_id text references sources(id),162 connector_name text references connectors(name),163 url text not null unique,164 canonical_url text,165 doc_type text not null default 'page', -- page|feed|feed_item|model_card|docs|pricing|release|paper|pdf|json|sitemap|repo|readme|leaderboard166 title text,167 entity_id text references entities(id),168 status text not null default 'active', -- active|gone|blocked|error169 first_seen_at timestamptz not null default now(),170 last_fetched_at timestamptz,171 last_changed_at timestamptz,172 last_status integer,173 etag text,174 last_modified text,175 content_hash text,176 fetch_count integer not null default 0,177 change_count integer not null default 0,178 fail_count integer not null default 0,179 fetch_interval_s integer,180 next_fetch_at timestamptz,181 priority smallint not null default 2,182 needs_llm boolean not null default false,183 meta jsonb not null default '{}'::jsonb184);185create index if not exists documents_connector_idx on documents (connector_name, last_fetched_at desc);186create index if not exists documents_entity_idx on documents (entity_id);187create index if not exists documents_next_idx on documents (next_fetch_at) where status = 'active';188189create table if not exists snapshots (190 id text primary key,191 document_id text not null references documents(id) on delete cascade,192 run_id text,193 url text not null,194 final_url text,195 observed_at timestamptz not null default now(),196 http_status integer,197 headers jsonb not null default '{}'::jsonb,198 content_type text,199 content_hash text not null,200 byte_size integer not null default 0,201 raw_path text, -- content-addressed gzip under AIA_DATA_DIR/raw202 text_path text, -- cleaned text (gzip) under AIA_DATA_DIR/text203 text_hash text,204 structured jsonb, -- deterministic extraction (json-ld, meta, tables, embedded json)205 parser_version text not null default '1',206 connector_version text not null default '1',207 transport text not null default 'direct', -- direct|browser|scrapfly|firecrawl|file|git208 changed boolean not null default true,209 diff jsonb, -- structural diff vs previous changed snapshot210 processing_status text not null default 'stored', -- stored|extracted|llm_pending|llm_done|failed211 created_at timestamptz not null default now()212);213create index if not exists snapshots_doc_idx on snapshots (document_id, observed_at desc);214create index if not exists snapshots_hash_idx on snapshots (content_hash);215create index if not exists snapshots_status_idx on snapshots (processing_status) where processing_status in ('stored','llm_pending');216217-- ---------------------------------------------------------------------------------------------- temporal facts218create table if not exists claims (219 id text primary key,220 entity_id text not null references entities(id) on delete cascade,221 property text not null,222 value jsonb not null,223 value_text text,224 value_num double precision,225 unit text,226 source_id text references sources(id),227 snapshot_id text references snapshots(id),228 source_url text,229 tier smallint not null default 2,230 confidence text not null default 'medium', -- verified|high|medium|low|conflicted231 status text not null default 'current', -- current|superseded|conflicting|retracted232 extractor text not null default 'deterministic',233 extractor_version text not null default '1',234 observed_at timestamptz not null default now(),235 effective_at timestamptz,236 valid_from timestamptz not null default now(),237 valid_to timestamptz,238 created_at timestamptz not null default now()239);240create index if not exists claims_entity_prop_idx on claims (entity_id, property, valid_from desc);241create index if not exists claims_current_idx on claims (entity_id, property) where status = 'current';242create index if not exists claims_snapshot_idx on claims (snapshot_id);243create index if not exists claims_observed_idx on claims (observed_at desc);244245create table if not exists relations (246 id text primary key,247 subject_id text not null references entities(id) on delete cascade,248 predicate text not null,249 object_id text not null references entities(id) on delete cascade,250 attributes jsonb not null default '{}'::jsonb,251 source_id text references sources(id),252 snapshot_id text references snapshots(id),253 source_url text,254 tier smallint not null default 2,255 confidence text not null default 'medium',256 observed_at timestamptz not null default now(),257 valid_from timestamptz not null default now(),258 valid_to timestamptz,259 created_at timestamptz not null default now()260);261create unique index if not exists relations_live_uniq on relations (subject_id, predicate, object_id) where valid_to is null;262create index if not exists relations_subject_idx on relations (subject_id, predicate);263create index if not exists relations_object_idx on relations (object_id, predicate);264265create table if not exists change_events (266 id text primary key,267 entity_id text references entities(id) on delete cascade,268 event_type text not null, -- NEW_MODEL|MODEL_UPDATED|PRICE_CHANGED|CONTEXT_CHANGED|NEW_PAPER|RELEASE|BENCHMARK_RESULT|NEW_PROVIDER|DOCUMENT_CHANGED|…269 category text not null default 'update', -- model|price|benchmark|paper|release|company|hardware|framework|provider|dataset|regulation|incident|repository270 property text,271 old_value jsonb,272 new_value jsonb,273 summary text,274 importance smallint not null default 2, -- 0 minor … 3 major275 observed_at timestamptz not null default now(),276 effective_at timestamptz,277 source_id text references sources(id),278 snapshot_id text references snapshots(id),279 source_url text,280 connector_name text,281 dedupe_key text unique,282 meta jsonb not null default '{}'::jsonb283);284create index if not exists change_events_observed_idx on change_events (observed_at desc);285create index if not exists change_events_entity_idx on change_events (entity_id, observed_at desc);286create index if not exists change_events_type_idx on change_events (event_type, observed_at desc);287create index if not exists change_events_category_idx on change_events (category, observed_at desc);288289-- ---------------------------------------------------------------------------------------------- domain tables290create table if not exists benchmark_results (291 id text primary key,292 model_id text not null references entities(id) on delete cascade,293 benchmark_id text not null references entities(id) on delete cascade,294 score double precision not null,295 metric text,296 unit text,297 higher_is_better boolean not null default true,298 config jsonb not null default '{}'::jsonb, -- prompting, judge, tool use, sampling, harness, model variant string299 evaluated_at timestamptz,300 observed_at timestamptz not null default now(),301 source_id text references sources(id),302 snapshot_id text references snapshots(id),303 source_url text,304 tier smallint not null default 2,305 confidence text not null default 'medium',306 dedupe_key text unique,307 valid_to timestamptz308);309create index if not exists benchmark_results_model_idx on benchmark_results (model_id, benchmark_id, observed_at desc);310create index if not exists benchmark_results_bench_idx on benchmark_results (benchmark_id, score desc);311312create table if not exists prices (313 id text primary key,314 model_id text not null references entities(id) on delete cascade,315 provider_id text not null references entities(id) on delete cascade,316 provider_model_id text,317 input_per_mtok double precision,318 output_per_mtok double precision,319 cached_input_per_mtok double precision,320 cache_write_per_mtok double precision,321 batch_input_per_mtok double precision,322 batch_output_per_mtok double precision,323 per_image double precision,324 per_request double precision,325 currency text not null default 'USD',326 context_length integer,327 max_output_tokens integer,328 features jsonb not null default '{}'::jsonb,329 observed_at timestamptz not null default now(),330 valid_from timestamptz not null default now(),331 valid_to timestamptz,332 source_id text references sources(id),333 snapshot_id text references snapshots(id),334 source_url text,335 tier smallint not null default 2,336 meta jsonb not null default '{}'::jsonb337);338create unique index if not exists prices_live_uniq on prices (model_id, provider_id, coalesce(provider_model_id, '')) where valid_to is null;339create index if not exists prices_provider_idx on prices (provider_id, valid_from desc);340create index if not exists prices_model_idx on prices (model_id, valid_from desc);341342create table if not exists domains (343 domain text primary key,344 organization_id text references entities(id),345 trust_tier smallint not null default 3,346 category text,347 notes text,348 created_at timestamptz not null default now()349);350351-- ---------------------------------------------------------------------------------------------- work queues352create table if not exists jobs (353 id text primary key,354 kind text not null, -- fetch_document|llm_extract|embed_entity|reprocess_snapshot|recompute_quality|resolve_entity355 payload jsonb not null default '{}'::jsonb,356 priority smallint not null default 5, -- 0 highest357 status text not null default 'queued', -- queued|running|done|failed|dead358 attempts integer not null default 0,359 max_attempts integer not null default 3,360 run_after timestamptz not null default now(),361 locked_by text,362 locked_at timestamptz,363 started_at timestamptz,364 finished_at timestamptz,365 error text,366 batch_id text,367 dedupe_key text,368 created_at timestamptz not null default now()369);370create index if not exists jobs_pick_idx on jobs (status, priority, run_after) where status = 'queued';371create unique index if not exists jobs_dedupe_idx on jobs (dedupe_key) where status in ('queued','running');372create index if not exists jobs_batch_idx on jobs (batch_id);373374create table if not exists llm_jobs (375 id text primary key,376 job_id text,377 task_type text not null,378 stage text not null, -- small|medium|large379 engine text not null,380 model text not null,381 node text,382 schema_name text,383 snapshot_id text references snapshots(id),384 entity_id text references entities(id),385 input_tokens integer,386 output_tokens integer,387 duration_ms integer,388 status text not null, -- ok|failed|invalid_json|schema_error389 output jsonb,390 error text,391 created_at timestamptz not null default now()392);393create index if not exists llm_jobs_created_idx on llm_jobs (created_at desc);394395create table if not exists review_queue (396 id text primary key,397 kind text not null, -- merge_candidate|conflict|blocked_source|parser_breakage|unusual_change|new_source398 entity_ids text[] not null default '{}',399 payload jsonb not null default '{}'::jsonb,400 reason text not null,401 status text not null default 'pending', -- pending|approved|rejected|edited402 resolution jsonb,403 created_at timestamptz not null default now(),404 resolved_at timestamptz,405 dedupe_key text unique406);407create index if not exists review_queue_status_idx on review_queue (status, created_at desc);408409-- ---------------------------------------------------------------------------------------------- embeddings (optional; local models)410do $$ begin411 if exists (select 1 from pg_extension where extname = 'vector') then412 execute 'create table if not exists entity_embeddings (413 entity_id text primary key references entities(id) on delete cascade,414 model text not null,415 embedding vector(1024) not null,416 text_hash text not null,417 created_at timestamptz not null default now())';418 end if;419end $$;420421-- ---------------------------------------------------------------------------------------------- stats, views, api keys422create table if not exists stats_snapshots (423 id bigserial primary key,424 computed_at timestamptz not null default now(),425 counts jsonb not null426);427428create table if not exists page_views (429 path text not null,430 day date not null,431 views integer not null default 0,432 primary key (path, day)433);434435create table if not exists api_keys (436 id text primary key,437 key_hash text not null unique,438 label text not null,439 owner_email text,440 plan text not null default 'developer',441 rate_per_min integer not null default 60,442 enabled boolean not null default true,443 created_at timestamptz not null default now(),444 last_used_at timestamptz,445 usage_count bigint not null default 0446);447448create table if not exists metric_definitions (449 key text primary key,450 label text not null,451 version text not null,452 description text not null,453 formula text,454 created_at timestamptz not null default now()455);456457-- ---------------------------------------------------------------------------------------------- search vector maintenance458create or replace function entities_search_update() returns trigger language plpgsql as $$459begin460 new.search :=461 setweight(to_tsvector('simple', coalesce(new.canonical_name, '')), 'A') ||462 setweight(to_tsvector('simple', coalesce(new.attributes->>'family', '')), 'B') ||463 setweight(to_tsvector('simple', coalesce(new.entity_type, '')), 'C') ||464 setweight(to_tsvector('english', left(coalesce(new.description, ''), 4000)), 'C');465 new.updated_at := now();466 return new;467end $$;468drop trigger if exists entities_search_trg on entities;469create trigger entities_search_trg before insert or update of canonical_name, description, attributes, entity_type on entities470 for each row execute function entities_search_update();471"""472473474def upgrade() -> None:475 for statement in _split(SQL):476 op.execute(statement)477478479def downgrade() -> None:480 raise RuntimeError("AI Atlas migrations are forward-only: historical data is never disposable")481482483def _split(sql: str) -> list[str]:484 """Split on semicolons outside `$$ … $$` blocks."""485 out: list[str] = []486 buf: list[str] = []487 in_dollar = False488 for line in sql.splitlines():489 stripped = line.strip()490 if stripped.count("$$") % 2 == 1:491 in_dollar = not in_dollar492 buf.append(line)493 if not in_dollar and stripped.endswith(";"):494 body = [ln for ln in buf if not ln.strip().startswith("--") or in_dollar]495 stmt = "\n".join(body).strip()496 if stmt:497 out.append(stmt)498 buf = []499 tail = "\n".join(ln for ln in buf if not ln.strip().startswith("--")).strip()500 if tail:501 out.append(tail)502 return out503