spb/satelliteindex
Public
TypeScript 66.5%
Python 30.9%
JavaScript 1.4%
CSS 0.7%
1"""initial canonical schema23Revision ID: 00014Revises:5Create Date: 2026-09-116"""7from __future__ import annotations89from alembic import op1011revision = "0001"12down_revision = None13branch_labels = None14depends_on = None1516SQL = r"""17create extension if not exists pg_trgm;18create extension if not exists "uuid-ossp";1920-- ---------------------------------------------------------------- sources & connector observability21create table sources (22 id text primary key, -- e.g. 'celestrak'23 name text not null,24 type text not null, -- orbital | catalog | registry | regulatory | company | news | weather25 base_url text,26 official boolean not null default false,27 country_code text,28 authority_type text, -- government | intergovernmental | scientific | operator | secondary29 license text,30 attribution_required boolean not null default true,31 attribution_text text,32 update_frequency_seconds integer,33 enabled boolean not null default true,34 priority integer not null default 100,35 created_at timestamptz not null default now(),36 updated_at timestamptz not null default now()37);3839create table connectors (40 name text primary key, -- e.g. 'celestrak_gp'41 source_id text not null references sources(id),42 description text,43 interval_seconds integer not null,44 enabled boolean not null default true,45 priority integer not null default 100,46 config jsonb not null default '{}'::jsonb,47 consecutive_failures integer not null default 0,48 circuit_open_until timestamptz,49 last_success_at timestamptz,50 last_attempt_at timestamptz,51 last_duration_ms integer,52 next_run_at timestamptz,53 created_at timestamptz not null default now(),54 updated_at timestamptz not null default now()55);5657create table connector_runs (58 id text primary key,59 connector_name text not null references connectors(name),60 source_id text not null references sources(id),61 started_at timestamptz not null default now(),62 finished_at timestamptz,63 status text not null default 'running', -- running | success | unchanged | failed | skipped64 duration_ms integer,65 records_fetched integer not null default 0,66 records_created integer not null default 0,67 records_updated integer not null default 0,68 records_skipped integer not null default 0,69 error text,70 payload_hash text,71 meta jsonb not null default '{}'::jsonb72);73create index connector_runs_name_started_idx on connector_runs (connector_name, started_at desc);7475create table connector_errors (76 id bigserial primary key,77 connector_name text not null references connectors(name),78 run_id text references connector_runs(id),79 occurred_at timestamptz not null default now(),80 error_type text,81 message text not null,82 context jsonb83);8485create table raw_records (86 id text primary key,87 source_id text not null references sources(id),88 connector_name text not null,89 run_id text references connector_runs(id),90 source_native_id text, -- group name, file name, query…91 content_type text not null,92 payload_hash text not null,93 byte_size bigint not null default 0,94 storage_path text, -- relative to SI_DATA_DIR/raw95 source_url text,96 fetched_at timestamptz not null default now(),97 processed_at timestamptz,98 processing_status text not null default 'pending', -- pending | processed | failed | unchanged99 record_count integer,100 error text101);102create index raw_records_source_fetched_idx on raw_records (source_id, fetched_at desc);103create index raw_records_hash_idx on raw_records (payload_hash);104105-- ---------------------------------------------------------------- reference: countries, owners, launch sites106create table countries (107 code text primary key, -- ISO 3166-1 alpha-2108 iso3 text,109 name text not null,110 slug text not null unique,111 region text,112 flag text113);114115create table owner_codes (116 code text primary key, -- SATCAT owner code (US, PRC, CIS, SES, ESA…)117 name text not null,118 kind text not null, -- country | organization | intergovernmental | consortium | unknown119 country_code text references countries(code),120 organization_id text121);122123create table launch_sites (124 code text primary key, -- SATCAT launch site code125 name text not null,126 slug text not null unique,127 country_code text references countries(code),128 latitude double precision,129 longitude double precision,130 active boolean not null default true131);132133-- ---------------------------------------------------------------- organizations & constellations134create table organizations (135 id text primary key,136 slug text not null unique,137 name text not null,138 normalized_name text not null,139 kind text not null default 'operator', -- operator | manufacturer | agency | military | launch_provider | consortium140 country_code text references countries(code),141 official_url text,142 description text,143 founded_year integer,144 created_at timestamptz not null default now(),145 updated_at timestamptz not null default now()146);147create index organizations_normalized_idx on organizations (normalized_name);148create index organizations_trgm_idx on organizations using gin (name gin_trgm_ops);149150create table organization_aliases (151 id bigserial primary key,152 organization_id text not null references organizations(id) on delete cascade,153 alias text not null,154 normalized text not null,155 source_id text references sources(id),156 unique (organization_id, normalized)157);158create index organization_aliases_normalized_idx on organization_aliases (normalized);159160create table constellations (161 id text primary key,162 slug text not null unique,163 name text not null,164 operator_id text references organizations(id),165 country_code text references countries(code),166 service_type text, -- communications | earth-observation | navigation | iot | weather | science | military | technology167 orbit_class text, -- LEO | MEO | GEO | HEO | MIXED168 lifecycle_stage text not null default 'OPERATIONAL',169 description text,170 official_url text,171 planned_count integer,172 authorized_count integer,173 match_patterns jsonb not null default '[]'::jsonb,174 celestrak_groups jsonb not null default '[]'::jsonb,175 created_at timestamptz not null default now(),176 updated_at timestamptz not null default now()177);178179-- ---------------------------------------------------------------- launches180create table launch_vehicle_families (181 id text primary key, slug text not null unique, name text not null, manufacturer_id text references organizations(id), country_code text references countries(code)182);183create table launch_vehicles (184 id text primary key, slug text not null unique, name text not null, family_id text references launch_vehicle_families(id), variant text185);186187create table launches (188 id text primary key,189 cospar_launch_id text not null unique, -- '1998-067'190 launch_date date,191 launch_year integer,192 launch_site_code text references launch_sites(code),193 launch_vehicle_id text references launch_vehicles(id),194 provider_id text references organizations(id),195 owner_codes text[] not null default '{}',196 payload_count integer not null default 0,197 object_count integer not null default 0,198 on_orbit_count integer not null default 0,199 primary_name text,200 first_seen_at timestamptz not null default now(),201 updated_at timestamptz not null default now()202);203create index launches_date_idx on launches (launch_date desc);204create index launches_site_idx on launches (launch_site_code);205206-- ---------------------------------------------------------------- satellites (canonical objects)207create table satellites (208 id text primary key,209 slug text not null unique,210 canonical_name text not null,211 normalized_name text not null,212 norad_id integer unique,213 cospar_id text,214 object_type text not null default 'UNKNOWN', -- PAYLOAD | ROCKET_BODY | DEBRIS | UNKNOWN | STATION | CREWED215 status text not null default 'UNKNOWN', -- ACTIVE | INACTIVE | DECAYED | LOST | FAILED | UNKNOWN | PLANNED216 ops_status_code text, -- SATCAT: + P B S X D ?217 operator_id text references organizations(id),218 owner_code text references owner_codes(code),219 country_code text references countries(code),220 constellation_id text references constellations(id),221 launch_id text references launches(id),222 launch_date date,223 launch_site_code text references launch_sites(code),224 decay_date date,225 mission_type text, -- communications | earth-observation | navigation | weather | science | military | technology | station | unknown226 orbit_class text, -- LEO | MEO | GEO | HEO | OTHER227 period_minutes double precision,228 inclination_deg double precision,229 apogee_km double precision,230 perigee_km double precision,231 rcs_m2 double precision,232 orbit_center text,233 orbit_type text,234 has_gp boolean not null default false,235 latest_epoch timestamptz,236 mass_kg double precision,237 description text,238 official_url text,239 first_seen_at timestamptz not null default now(),240 last_seen_at timestamptz not null default now(),241 created_at timestamptz not null default now(),242 updated_at timestamptz not null default now()243);244create index satellites_cospar_idx on satellites (cospar_id);245create index satellites_status_idx on satellites (status);246create index satellites_type_status_idx on satellites (object_type, status);247create index satellites_constellation_idx on satellites (constellation_id);248create index satellites_operator_idx on satellites (operator_id);249create index satellites_country_idx on satellites (country_code);250create index satellites_launch_idx on satellites (launch_id);251create index satellites_launch_date_idx on satellites (launch_date desc);252create index satellites_decay_date_idx on satellites (decay_date desc);253create index satellites_orbit_class_idx on satellites (orbit_class);254create index satellites_name_trgm_idx on satellites using gin (canonical_name gin_trgm_ops);255create index satellites_norad_text_idx on satellites ((norad_id::text) text_pattern_ops);256257create table satellite_aliases (258 id bigserial primary key,259 satellite_id text not null references satellites(id) on delete cascade,260 alias text not null,261 normalized text not null,262 source_id text references sources(id),263 unique (satellite_id, normalized)264);265create index satellite_aliases_normalized_idx on satellite_aliases (normalized);266267create table satellite_slugs (268 slug text primary key,269 satellite_id text not null references satellites(id) on delete cascade,270 created_at timestamptz not null default now()271);272273create table satellite_tags (274 satellite_id text not null references satellites(id) on delete cascade,275 tag text not null, -- celestrak group name or derived tag276 source_id text references sources(id),277 first_seen_at timestamptz not null default now(),278 last_seen_at timestamptz not null default now(),279 primary key (satellite_id, tag)280);281create index satellite_tags_tag_idx on satellite_tags (tag);282283create table satellite_status_history (284 id bigserial primary key,285 satellite_id text not null references satellites(id) on delete cascade,286 field text not null,287 old_value text,288 new_value text,289 source_id text references sources(id),290 changed_at timestamptz not null default now()291);292create index satellite_status_history_sat_idx on satellite_status_history (satellite_id, changed_at desc);293create index satellite_status_history_changed_idx on satellite_status_history (changed_at desc);294295create table constellation_memberships (296 id bigserial primary key,297 satellite_id text not null references satellites(id) on delete cascade,298 constellation_id text not null references constellations(id) on delete cascade,299 method text not null, -- celestrak_group | name_pattern | manual300 since timestamptz not null default now(),301 until timestamptz302);303create index constellation_memberships_sat_idx on constellation_memberships (satellite_id) where until is null;304305-- ---------------------------------------------------------------- orbital history (append only)306create table orbital_elements (307 id bigserial primary key,308 satellite_id text not null references satellites(id) on delete cascade,309 source_id text not null references sources(id),310 epoch timestamptz not null,311 mean_motion double precision not null,312 eccentricity double precision not null,313 inclination double precision not null,314 raan double precision not null,315 arg_of_perigee double precision not null,316 mean_anomaly double precision not null,317 bstar double precision,318 mean_motion_dot double precision,319 mean_motion_ddot double precision,320 element_set_no integer,321 rev_at_epoch integer,322 classification text,323 ephemeris_type integer,324 semi_major_axis_km double precision,325 perigee_km double precision,326 apogee_km double precision,327 period_minutes double precision,328 element_format text not null default 'omm_json',329 raw_omm jsonb,330 received_at timestamptz not null default now(),331 created_at timestamptz not null default now(),332 unique (satellite_id, source_id, epoch)333);334create index orbital_elements_sat_epoch_idx on orbital_elements (satellite_id, epoch desc);335create index orbital_elements_received_idx on orbital_elements (received_at desc);336337-- latest element set per satellite (kept in sync by the connector; avoids DISTINCT ON over history)338create table orbital_state (339 satellite_id text primary key references satellites(id) on delete cascade,340 element_id bigint not null references orbital_elements(id),341 source_id text not null references sources(id),342 epoch timestamptz not null,343 mean_motion double precision not null,344 eccentricity double precision not null,345 inclination double precision not null,346 raan double precision not null,347 arg_of_perigee double precision not null,348 mean_anomaly double precision not null,349 bstar double precision,350 mean_motion_dot double precision,351 mean_motion_ddot double precision,352 semi_major_axis_km double precision,353 perigee_km double precision,354 apogee_km double precision,355 period_minutes double precision,356 orbit_class text,357 updated_at timestamptz not null default now()358);359create index orbital_state_epoch_idx on orbital_state (epoch);360create index orbital_state_class_idx on orbital_state (orbit_class);361362-- ---------------------------------------------------------------- identifiers, provenance, quality363create table entity_identifiers (364 id bigserial primary key,365 entity_type text not null,366 entity_id text not null,367 source_id text references sources(id),368 identifier_type text not null, -- norad | cospar | jcat | un_registration | fcc | itu | source_native_id | launch_id369 identifier_value text not null,370 confidence double precision not null default 1.0,371 first_seen_at timestamptz not null default now(),372 last_seen_at timestamptz not null default now(),373 verified boolean not null default false,374 metadata jsonb,375 unique (entity_type, entity_id, identifier_type, identifier_value)376);377create index entity_identifiers_lookup_idx on entity_identifiers (identifier_type, identifier_value);378379create table field_provenance (380 id bigserial primary key,381 entity_type text not null,382 entity_id text not null,383 field_name text not null,384 field_value text,385 source_id text not null references sources(id),386 source_record_id text,387 confidence double precision not null default 1.0,388 observed_at timestamptz not null default now(),389 selected_as_canonical boolean not null default true,390 unique (entity_type, entity_id, field_name, source_id)391);392create index field_provenance_entity_idx on field_provenance (entity_type, entity_id);393394create table data_quality_flags (395 id bigserial primary key,396 entity_type text not null,397 entity_id text not null,398 flag text not null, -- SOURCE_CONFLICT | MISSING_ID | AMBIGUOUS_ENTITY | STALE_DATA | SUSPECT_ORBIT | UNKNOWN_OPERATOR | UNKNOWN_COUNTRY | DUPLICATE_OBJECT399 detail text,400 created_at timestamptz not null default now(),401 resolved_at timestamptz,402 unique (entity_type, entity_id, flag)403);404create index data_quality_flags_open_idx on data_quality_flags (flag) where resolved_at is null;405406create table manual_review_queue (407 id bigserial primary key,408 kind text not null, -- possible_duplicate | unknown_owner | conflict409 entity_a_type text, entity_a_id text, entity_b_type text, entity_b_id text,410 confidence double precision,411 detail jsonb,412 status text not null default 'open', -- open | merged | kept_separate | dismissed413 created_at timestamptz not null default now(),414 resolved_at timestamptz, resolved_by text415);416417create table entity_merges (418 id bigserial primary key,419 entity_type text not null, kept_id text not null, merged_id text not null,420 reason text, performed_by text, performed_at timestamptz not null default now(), snapshot jsonb421);422423-- ---------------------------------------------------------------- events424create table events (425 id text primary key,426 type text not null,427 title text not null,428 summary text,429 event_time timestamptz not null,430 detected_at timestamptz not null default now(),431 confidence double precision not null default 1.0,432 source_id text references sources(id),433 source_url text,434 dedupe_key text unique,435 metadata jsonb not null default '{}'::jsonb,436 created_at timestamptz not null default now()437);438create index events_time_idx on events (event_time desc);439create index events_type_time_idx on events (type, event_time desc);440441create table event_entities (442 event_id text not null references events(id) on delete cascade,443 entity_type text not null,444 entity_id text not null,445 relationship text not null default 'subject',446 primary key (event_id, entity_type, entity_id, relationship)447);448create index event_entities_entity_idx on event_entities (entity_type, entity_id);449450-- ---------------------------------------------------------------- search451create table search_index (452 entity_type text not null,453 entity_id text not null,454 slug text not null,455 title text not null,456 subtitle text,457 keywords text not null default '',458 weight double precision not null default 1.0,459 tsv tsvector,460 updated_at timestamptz not null default now(),461 primary key (entity_type, entity_id)462);463create index search_index_tsv_idx on search_index using gin (tsv);464create index search_index_title_trgm_idx on search_index using gin (title gin_trgm_ops);465create index search_index_keywords_trgm_idx on search_index using gin (keywords gin_trgm_ops);466467-- ---------------------------------------------------------------- derived metrics (versioned)468create table metric_definitions (469 key text primary key, name text not null, version text not null, methodology text not null, inputs jsonb not null default '[]'::jsonb, updated_at timestamptz not null default now()470);471472create table stats_snapshots (473 key text primary key, -- 'global' | 'orbit_buckets' | …474 computed_at timestamptz not null default now(),475 payload jsonb not null476);477478-- ---------------------------------------------------------------- trending479create table page_views (480 day date not null, entity_type text not null, entity_id text not null, views integer not null default 0,481 primary key (day, entity_type, entity_id)482);483484-- ---------------------------------------------------------------- materialized views485create materialized view country_stats as486select c.code, c.name, c.slug,487 count(s.id) filter (where s.object_type in ('PAYLOAD','STATION') and s.status = 'ACTIVE') as active_payloads,488 count(s.id) filter (where s.object_type in ('PAYLOAD','STATION') and s.decay_date is null) as on_orbit_payloads,489 count(s.id) filter (where s.object_type in ('PAYLOAD','STATION')) as total_payloads,490 count(s.id) filter (where s.object_type = 'DEBRIS' and s.decay_date is null) as debris_on_orbit,491 count(s.id) filter (where s.object_type = 'ROCKET_BODY' and s.decay_date is null) as rocket_bodies_on_orbit,492 count(s.id) filter (where s.decay_date is null) as objects_on_orbit,493 count(s.id) as total_objects,494 count(distinct s.launch_id) as launches,495 count(distinct s.operator_id) as operators,496 count(s.id) filter (where s.launch_date >= (current_date - interval '365 days') and s.object_type in ('PAYLOAD','STATION')) as payloads_last_365d497from countries c left join satellites s on s.country_code = c.code498group by c.code, c.name, c.slug;499create unique index country_stats_code_idx on country_stats (code);500501create materialized view operator_stats as502select o.id, o.slug, o.name, o.kind, o.country_code,503 count(s.id) filter (where s.status = 'ACTIVE' and s.object_type in ('PAYLOAD','STATION')) as active_payloads,504 count(s.id) filter (where s.object_type in ('PAYLOAD','STATION') and s.decay_date is null) as on_orbit_payloads,505 count(s.id) filter (where s.object_type in ('PAYLOAD','STATION')) as total_payloads,506 count(s.id) filter (where s.decay_date is not null) as decayed,507 count(distinct s.constellation_id) as constellations,508 count(distinct s.launch_id) as launches,509 count(s.id) filter (where s.launch_date >= (current_date - interval '365 days')) as payloads_last_365d,510 min(s.launch_date) as first_launch, max(s.launch_date) as last_launch511from organizations o left join satellites s on s.operator_id = o.id512group by o.id, o.slug, o.name, o.kind, o.country_code;513create unique index operator_stats_id_idx on operator_stats (id);514515create materialized view constellation_stats as516select k.id, k.slug, k.name, k.operator_id, k.country_code, k.service_type, k.orbit_class,517 count(s.id) filter (where s.status = 'ACTIVE') as active,518 count(s.id) filter (where s.status = 'INACTIVE') as inactive,519 count(s.id) filter (where s.decay_date is not null) as decayed,520 count(s.id) filter (where s.decay_date is null) as on_orbit,521 count(s.id) as total,522 count(s.id) filter (where s.launch_date >= (current_date - interval '365 days')) as launched_last_365d,523 count(s.id) filter (where s.launch_date >= (current_date - interval '30 days')) as launched_last_30d,524 count(distinct s.launch_id) as launches,525 min(s.launch_date) as first_launch, max(s.launch_date) as last_launch,526 percentile_cont(0.5) within group (order by s.perigee_km) filter (where s.status='ACTIVE') as median_perigee_km,527 percentile_cont(0.5) within group (order by s.inclination_deg) filter (where s.status='ACTIVE') as median_inclination_deg528from constellations k left join satellites s on s.constellation_id = k.id529group by k.id, k.slug, k.name, k.operator_id, k.country_code, k.service_type, k.orbit_class;530create unique index constellation_stats_id_idx on constellation_stats (id);531532create materialized view launch_year_stats as533select l.launch_year as year,534 count(*) as launches,535 sum(l.payload_count) as payloads,536 count(*) filter (where l.launch_site_code is not null) as with_site537from launches l where l.launch_year is not null538group by l.launch_year;539create unique index launch_year_stats_year_idx on launch_year_stats (year);540541create materialized view orbital_bucket_stats as542with b as (543 select s.id, s.object_type, s.status, s.perigee_km, s.apogee_km, s.orbit_class,544 case545 when s.orbit_class = 'GEO' then 'GEO'546 when s.orbit_class = 'MEO' then 'MEO'547 when s.orbit_class = 'HEO' then 'HEO'548 when s.perigee_km is null then 'UNKNOWN'549 when s.perigee_km < 200 then '0-200'550 when s.perigee_km < 300 then '200-300'551 when s.perigee_km < 400 then '300-400'552 when s.perigee_km < 500 then '400-500'553 when s.perigee_km < 600 then '500-600'554 when s.perigee_km < 800 then '600-800'555 when s.perigee_km < 1000 then '800-1000'556 when s.perigee_km < 2000 then '1000-2000'557 else 'OTHER' end as bucket558 from satellites s where s.decay_date is null and s.orbit_center = 'EA'559)560select bucket,561 count(*) as objects,562 count(*) filter (where object_type in ('PAYLOAD','STATION') and status = 'ACTIVE') as active_payloads,563 count(*) filter (where object_type in ('PAYLOAD','STATION')) as payloads,564 count(*) filter (where object_type = 'DEBRIS') as debris,565 count(*) filter (where object_type = 'ROCKET_BODY') as rocket_bodies566from b group by bucket;567create unique index orbital_bucket_stats_bucket_idx on orbital_bucket_stats (bucket);568"""569570571def upgrade() -> None:572 for stmt in _split(SQL):573 op.execute(stmt)574575576def downgrade() -> None:577 op.execute("drop schema public cascade; create schema public;")578579580def _split(sql: str) -> list[str]:581 """Split on ';' at end of line — the DDL above never contains ';' inside string literals except in comments."""582 out: list[str] = []583 buf: list[str] = []584 for line in sql.splitlines():585 stripped = line.split("--")[0].rstrip() if not line.lstrip().startswith("--") else ""586 if line.lstrip().startswith("--") and not buf:587 continue588 buf.append(line)589 if stripped.endswith(";"):590 out.append("\n".join(buf))591 buf = []592 if "".join(buf).strip():593 out.append("\n".join(buf))594 return out595