spb/market-atlas

1-- Market Atlas — initial schema (PostgreSQL 17)2-- Metadata (instruments, exchanges, sources, connectors, events) + time-series (observations partitioned by day, bars).34create table if not exists schema_migrations (5  version text primary key,6  applied_at timestamptz not null default now()7);89create table if not exists countries (10  code text primary key,11  name text not null,12  region text not null,13  currency text14);1516create table if not exists exchanges (17  id text primary key,18  mic text,19  name text not null,20  operator text,21  country text not null references countries(code),22  city text,23  timezone text not null,24  currency text,25  website text,26  sessions jsonb not null default '{"regular":[]}'::jsonb,27  lat double precision,28  lon double precision,29  asset_classes text[] not null default '{}',30  created_at timestamptz not null default now(),31  updated_at timestamptz not null default now()32);33create index if not exists exchanges_country_idx on exchanges(country);3435create table if not exists exchange_holidays (36  exchange_id text not null references exchanges(id) on delete cascade,37  date date not null,38  name text not null,39  kind text not null default 'CLOSED',40  close_time text,41  source_id text,42  primary key (exchange_id, date)43);4445create table if not exists companies (46  id text primary key,47  name text not null,48  cik text,49  country text,50  sector text,51  industry text,52  website text,53  created_at timestamptz not null default now(),54  updated_at timestamptz not null default now()55);56create index if not exists companies_cik_idx on companies(cik);5758create table if not exists instruments (59  id text primary key,60  symbol text not null,61  name text not null,62  asset_class text not null,63  exchange_id text references exchanges(id),64  mic text,65  currency text,66  country text,67  company_id text references companies(id),68  isin text,69  security_type text,70  base text,71  quote text,72  is_active boolean not null default true,73  metadata jsonb not null default '{}'::jsonb,74  search_text text generated always as (lower(symbol || ' ' || name)) stored,75  created_at timestamptz not null default now(),76  updated_at timestamptz not null default now()77);78create index if not exists instruments_symbol_idx on instruments(upper(symbol));79create index if not exists instruments_class_idx on instruments(asset_class);80create index if not exists instruments_exchange_idx on instruments(exchange_id);81create index if not exists instruments_country_idx on instruments(country);82create index if not exists instruments_company_idx on instruments(company_id);83create index if not exists instruments_search_idx on instruments using gin (to_tsvector('simple', search_text));8485create table if not exists symbol_aliases (86  alias text not null,87  source_id text not null default '*',88  instrument_id text not null references instruments(id) on delete cascade,89  primary key (alias, source_id)90);91create index if not exists symbol_aliases_instrument_idx on symbol_aliases(instrument_id);9293create table if not exists sources (94  id text primary key,95  name text not null,96  organization text,97  source_type text not null,98  homepage text,99  jurisdiction text,100  rights_status text not null,101  realtime_status text not null,102  family text,103  category text not null default 'PUBLIC_MARKET_SOURCE',104  enabled boolean not null default true,105  created_at timestamptz not null default now(),106  updated_at timestamptz not null default now()107);108109create table if not exists connectors (110  id text primary key,111  source_id text not null references sources(id),112  name text not null,113  version text not null,114  source_type text not null,115  rights_status text not null,116  realtime_status text not null,117  enabled boolean not null default true,118  paused boolean not null default false,119  metadata jsonb not null default '{}'::jsonb,120  state jsonb not null default '{}'::jsonb,           -- connector-private persisted state (cursors, seen ids)121  schema_fingerprints jsonb not null default '{}'::jsonb, -- kind -> [fingerprints]122  status text not null default 'STARTING',123  last_message_at timestamptz,124  last_success_at timestamptz,125  last_error_at timestamptz,126  last_error text,127  messages_total bigint not null default 0,128  errors_total bigint not null default 0,129  reconnects bigint not null default 0,130  reliability_score double precision,131  created_at timestamptz not null default now(),132  updated_at timestamptz not null default now()133);134135create table if not exists connector_versions (136  connector_id text not null references connectors(id) on delete cascade,137  version text not null,138  first_seen_at timestamptz not null default now(),139  metadata jsonb not null default '{}'::jsonb,140  primary key (connector_id, version)141);142143create table if not exists connector_schema_changes (144  id bigserial primary key,145  connector_id text not null references connectors(id) on delete cascade,146  kind text not null,147  old_fingerprint text,148  new_fingerprint text not null,149  sample jsonb,150  detected_at timestamptz not null default now(),151  acknowledged boolean not null default false152);153create index if not exists connector_schema_changes_idx on connector_schema_changes(connector_id, detected_at desc);154155-- Time series of connector health snapshots (1 per connector per minute).156create table if not exists connector_health (157  ts timestamptz not null,158  connector_id text not null,159  status text not null,160  messages_1m integer not null default 0,161  errors_1m integer not null default 0,162  median_latency_ms integer,163  p95_latency_ms integer,164  parse_success_rate double precision,165  instruments_covered integer,166  reconnects integer not null default 0,167  reliability_score double precision,168  primary key (connector_id, ts)169);170create index if not exists connector_health_ts_idx on connector_health(ts);171172-- L3 observations, partitioned by received day. Values are kept at source precision.173create table if not exists observations (174  received_at timestamptz not null,175  source_ts timestamptz,176  instrument_id text not null,177  field text not null,178  value double precision not null,179  currency text,180  source_id text not null,181  connector_id text not null,182  rights_status text not null,183  realtime_status text not null,184  timestamp_trust text not null,185  confidence real,186  latency_ms bigint,187  sequence text,188  fingerprint text not null,189  raw_ref text,190  normalizer_version text not null,191  meta jsonb192) partition by range (received_at);193create index if not exists observations_instr_idx on observations(instrument_id, field, received_at desc);194create index if not exists observations_source_idx on observations(source_id, received_at desc);195create index if not exists observations_fp_idx on observations(fingerprint);196197create table if not exists canonical_quotes (198  instrument_id text primary key references instruments(id) on delete cascade,199  symbol text not null,200  price double precision,201  open double precision,202  high double precision,203  low double precision,204  previous_close double precision,205  change double precision,206  change_percent double precision,207  volume double precision,208  bid double precision,209  ask double precision,210  currency text,211  source_count integer not null default 0,212  dispersion_bps double precision,213  confidence double precision not null default 0,214  freshness_ms bigint,215  realtime_status text not null default 'UNKNOWN',216  rights_status text not null default 'UNKNOWN',217  updated_at timestamptz not null,218  source_ts timestamptz,219  session_high double precision,220  session_low double precision,221  contributions jsonb not null default '[]'::jsonb,222  consensus_version text not null223);224create index if not exists canonical_quotes_updated_idx on canonical_quotes(updated_at desc);225create index if not exists canonical_quotes_change_idx on canonical_quotes(change_percent);226227create table if not exists bars (228  instrument_id text not null,229  resolution text not null,230  ts timestamptz not null,231  open double precision not null,232  high double precision not null,233  low double precision not null,234  close double precision not null,235  volume double precision,236  source_count integer not null default 1,237  producer text not null,238  version text not null,239  primary key (instrument_id, resolution, ts)240);241create index if not exists bars_res_ts_idx on bars(resolution, ts desc);242243create table if not exists market_events (244  id text primary key,245  type text not null,246  ts timestamptz not null,247  instrument_ids text[] not null default '{}',248  entity_ids text[] not null default '{}',249  severity text not null,250  confidence double precision not null,251  source_count integer not null default 1,252  sources text[] not null default '{}',253  title text not null,254  summary text,255  data jsonb not null default '{}'::jsonb,256  fingerprint text not null unique,257  supporting_observations text[] not null default '{}',258  first_seen_at timestamptz not null default now(),259  confirmed_at timestamptz260);261create index if not exists market_events_ts_idx on market_events(ts desc);262create index if not exists market_events_type_idx on market_events(type, ts desc);263create index if not exists market_events_instr_idx on market_events using gin (instrument_ids);264265create table if not exists filings (266  id text primary key,267  source_id text not null,268  cik text,269  company_name text not null,270  form_type text not null,271  filed_at timestamptz not null,272  url text not null,273  instrument_ids text[] not null default '{}',274  metadata jsonb not null default '{}'::jsonb,275  created_at timestamptz not null default now()276);277create index if not exists filings_filed_idx on filings(filed_at desc);278create index if not exists filings_cik_idx on filings(cik);279create index if not exists filings_form_idx on filings(form_type);280281create table if not exists corporate_actions (282  id text primary key,283  instrument_id text references instruments(id),284  company_id text references companies(id),285  type text not null,286  announced_at timestamptz,287  ex_date date,288  effective_date date,289  payable_date date,290  value double precision,291  currency text,292  ratio text,293  source_id text not null,294  url text,295  metadata jsonb not null default '{}'::jsonb,296  created_at timestamptz not null default now()297);298299-- Slowly-changing facts diff log (market cap, shares, profile…).300create table if not exists fact_changes (301  id bigserial primary key,302  entity_type text not null,303  entity_id text not null,304  field text not null,305  old_value jsonb,306  new_value jsonb,307  detected_at timestamptz not null default now(),308  effective_at timestamptz,309  source_id text310);311create index if not exists fact_changes_entity_idx on fact_changes(entity_type, entity_id, detected_at desc);312313-- Content fingerprints for change detection on documents/pages.314create table if not exists document_snapshots (315  connector_id text not null,316  url text not null,317  document_hash text not null,318  section_hashes jsonb not null default '{}'::jsonb,319  fetched_at timestamptz not null default now(),320  raw_ref text,321  primary key (connector_id, url)322);323324create table if not exists system_counters (325  key text primary key,326  value bigint not null default 0,327  updated_at timestamptz not null default now()328);329330create table if not exists observation_archives (331  partition_name text primary key,332  day date not null,333  rows_archived bigint not null,334  path text not null,335  bytes bigint not null,336  archived_at timestamptz not null default now()337);338339-- Daily counts used by the homepage telemetry (observations today, etc.).340create table if not exists daily_stats (341  day date primary key,342  observations bigint not null default 0,343  events bigint not null default 0,344  filings bigint not null default 0,345  updated_at timestamptz not null default now()346);347