spb/social-runtime-crawler

1-- Social Runtime Crawler — prototype schema (§36). Raw observations are append-only; canonical tables are derived.2CREATE TABLE IF NOT EXISTS sessions (3  session_id     text PRIMARY KEY,4  platform       text NOT NULL,5  account_alias  text NOT NULL,6  mode           text,7  goal           text,8  started_at     timestamptz NOT NULL DEFAULT now(),9  ended_at       timestamptz,10  health         text,11  stats          jsonb12);1314-- Every event, raw (§37). Never updated.15CREATE TABLE IF NOT EXISTS observations (16  event_id    text PRIMARY KEY,17  session_id  text NOT NULL REFERENCES sessions(session_id) ON DELETE CASCADE,18  platform    text NOT NULL,19  event_type  text NOT NULL,20  step        integer,21  ts          timestamptz NOT NULL,22  payload     jsonb NOT NULL,23  provenance  jsonb,24  discovered_via jsonb25);26CREATE INDEX IF NOT EXISTS observations_session_idx ON observations(session_id, step);27CREATE INDEX IF NOT EXISTS observations_type_idx ON observations(event_type);2829CREATE TABLE IF NOT EXISTS entities (30  fingerprint   text PRIMARY KEY,31  platform      text NOT NULL,32  entity_type   text NOT NULL,33  platform_id   text,34  url           text,35  name          text,36  text_excerpt  text,37  author        text,38  metrics       jsonb,39  media         jsonb,40  fields        jsonb NOT NULL DEFAULT '{}'::jsonb,   -- field → {value, provenance[]}  (§38 evidence model)41  confidence    real,42  first_seen    timestamptz NOT NULL DEFAULT now(),43  last_seen     timestamptz NOT NULL DEFAULT now(),44  seen_count    integer NOT NULL DEFAULT 1,45  first_session text,46  canonical_id  text47);48CREATE INDEX IF NOT EXISTS entities_platform_type_idx ON entities(platform, entity_type);4950CREATE TABLE IF NOT EXISTS entity_observations (51  id          bigserial PRIMARY KEY,52  fingerprint text NOT NULL REFERENCES entities(fingerprint) ON DELETE CASCADE,53  session_id  text NOT NULL,54  step        integer,55  ts          timestamptz NOT NULL,56  surfaces    text[] NOT NULL,57  snapshot    jsonb NOT NULL58);59CREATE INDEX IF NOT EXISTS entity_observations_fp_idx ON entity_observations(fingerprint);6061CREATE TABLE IF NOT EXISTS media (62  fingerprint        text PRIMARY KEY,63  platform           text NOT NULL,64  media_type         text NOT NULL,65  platform_media_id  text,66  page_url           text,67  url                text,68  title              text,69  author             text,70  duration_s         real,71  width              integer,72  height             integer,73  thumbnail_url      text,74  delivery           jsonb,75  frames             text[],76  provenance         jsonb,77  first_seen         timestamptz NOT NULL DEFAULT now(),78  last_seen          timestamptz NOT NULL DEFAULT now()79);8081CREATE TABLE IF NOT EXISTS relationships (82  id         bigserial PRIMARY KEY,83  session_id text NOT NULL,84  from_fp    text NOT NULL,85  to_fp      text NOT NULL,86  rel_type   text NOT NULL,87  step       integer,88  UNIQUE (from_fp, to_fp, rel_type)89);9091CREATE TABLE IF NOT EXISTS actions (92  id           bigserial PRIMARY KEY,93  session_id   text NOT NULL REFERENCES sessions(session_id) ON DELETE CASCADE,94  step         integer NOT NULL,95  action_id    text,96  action_type  text NOT NULL,97  label        text,98  target_url   text,99  planner      text,100  expected_gain real,101  novelty      real,102  relevance    real,103  reason       text,104  scores       jsonb,105  before_state jsonb,106  after_state  jsonb,107  success      boolean,108  error        text,109  duration_ms  integer,110  ts           timestamptz NOT NULL DEFAULT now()111);112CREATE INDEX IF NOT EXISTS actions_session_idx ON actions(session_id, step);113114CREATE TABLE IF NOT EXISTS network_responses (115  request_id    text PRIMARY KEY,116  session_id    text NOT NULL,117  step          integer,118  url           text NOT NULL,119  method        text,120  status        integer,121  kind          text,122  content_type  text,123  body_size     integer,124  shape_hash    text,125  hostname      text,126  path_pattern  text,127  graphql_operation text,128  entity_count  integer,129  entity_types  text[],130  confidence    real,131  ts            timestamptz NOT NULL132);133CREATE INDEX IF NOT EXISTS network_responses_shape_idx ON network_responses(shape_hash);134135CREATE TABLE IF NOT EXISTS schema_patterns (136  platform      text NOT NULL,137  shape_hash    text NOT NULL,138  fingerprint   jsonb NOT NULL,139  schema        jsonb NOT NULL,140  sample_url    text,141  observed_count integer NOT NULL DEFAULT 1,142  first_seen    timestamptz NOT NULL DEFAULT now(),143  last_seen     timestamptz NOT NULL DEFAULT now(),144  PRIMARY KEY (platform, shape_hash)145);146147CREATE TABLE IF NOT EXISTS feed_items (148  id             bigserial PRIMARY KEY,149  session_id     text NOT NULL,150  step           integer,151  page_url       text,152  page_type      text,153  feed_position  integer,154  fingerprint    text NOT NULL,155  entity_type    text,156  visible        boolean,157  ts             timestamptz NOT NULL158);159160CREATE TABLE IF NOT EXISTS connector_versions (161  id          bigserial PRIMARY KEY,162  platform    text NOT NULL,163  compiled_at timestamptz NOT NULL DEFAULT now(),164  confidence  integer,165  summary     jsonb,166  manifest_path text167);168169CREATE TABLE IF NOT EXISTS crawl_jobs (170  job_id      text PRIMARY KEY,171  session_id  text,172  platform    text NOT NULL,173  mode        text,174  goal        text,175  budget      jsonb,176  status      text,177  created_at  timestamptz NOT NULL DEFAULT now(),178  finished_at timestamptz,179  result      jsonb180);181