Intel Data Platform Guide

Who this is for: analysts, researchers, and team members who want to use the data we collect — where it lives, how it gets there, when it refreshes, and how to query it. No code access required.

This file is the source of truth. The Markdown file docs/data/data_guide.md is the source; the online page at datadocs.hml.st is generated from it automatically by CI on every push to main. Edit here, open a PR — CI renders and publishes. See How this page is built.

Overview

Everything we collect — social media, TV, radio, online news — plus everything we derive from it — narratives, subjects, events, briefs — lives in two stores:

Store	What it holds	How you query it
Google BigQuery — `intel-487218.datawarehouse`	Raw collected content (posts, articles, transcripts) and analytics time-series (daily mention/volume tables)	SQL, directly (this is the analyst-facing store)
PostgreSQL (Render-managed app DB)	Source-of-truth catalogs and definitions (POIs, narratives, subjects), analysis results surfaced in the app (events, briefs, alignment), plus auth & ops	Through the app UI / API; direct SQL is for engineers

Rule of thumb: if you want to analyze content or trends, you want BigQuery. If you want the curated definitions and finished results the product shows, that's PostgreSQL — reachable via the app at hml.st or the API.

The data moves through four layers: Collection → Storage → Processing → Presentation. The full diagram is in docs/diagrams/data-flow.txt.

Architecture diagrams

Two diagrams live in docs/diagrams/, each in ASCII, Excalidraw, and SVG:

data-flow.txt (svg / excalidraw) — end-to-end Collection → Storage → Processing → Presentation, plus detailed Publishers/messaging-funnel and Events sub-pipelines.
deployment.txt (svg / excalidraw) — what runs where: Render vs GCP, schedulers, workflows, jobs, secrets, external APIs.

Refreshed 2026-07-12 to match the ScrapeCreators migration, the official X API / col:3x tohar collection paths, and the Pixel pipeline. If this guide and the embedded ASCII ever drift again, the tables above in this guide are the current source of truth — diagrams are refreshed on request, not on every change.

Data flow diagram (ASCII, click to expand)

Intel — Data Flow Diagram
==========================

Last updated: 2026-07-12

Three layers: COLLECTION (ingest) → STORAGE (BQ + PG) → PROCESSING (analysis)
              → PRESENTATION (API, UI, MCP, /cc agent).


┌─────────────────────────────────────────────────────────────────────────────┐
│                         ① COLLECTION (Cloud Run Jobs)                       │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                             │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐  ┌────────────────┐    │
│  │   XPOZ API   │  │ScrapeCreators│  │ScrapeCreators│  │ official X API │    │
│  │ (TW + IG)    │  │  (TikTok)    │  │  (Facebook)  │  │ (col:x-api /   │    │
│  │              │  │              │  │              │  │  kashaviya)    │    │
│  └──────┬───────┘  └──────┬───────┘  └──────┬───────┘  └────────┬───────┘    │
│         │                 │                 │                   │           │
│         ▼                 ▼                 ▼                   ▼           │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐  ┌────────────────┐   │
│  │  ingest-tw   │  │ingest-tiktok │  │  ingest-fb   │  │  ingest-x-api  │   │
│  │   (hourly)   │  │   (daily)    │  │  (daily) +   │  │ (daily 13:00,  │   │
│  │+ ingest-ig   │  │              │  │  ingest-fb-  │  │  + Fri 05:00)  │   │
│  │  (daily,seq) │  │              │  │  publishers  │  │                │   │
│  └──────┬───────┘  └──────┬───────┘  └──────┬───────┘  └────────┬───────┘   │
│         │                 │                 │                   │           │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐  ┌────────────────┐   │
│  │  Drive (TV)  │  │  Online news │  │  Drive (KB)  │  │   Telegram     │   │
│  └──────┬───────┘  └──────┬───────┘  └──────┬───────┘  └────────┬───────┘   │
│         ▼                 ▼                 ▼                   ▼           │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐  ┌────────────────┐   │
│  │  ingest-tv   │  │ingest-online │  │  ingest-kb   │  │ingest-telegram │   │
│  │   (daily)    │  │ -papers (2h) │  │   (daily)    │  │                │   │
│  └──────┬───────┘  └──────┬───────┘  └──────┬───────┘  └────────┬───────┘   │
│         │                 │                 │                   │           │
│  ┌──────────────────────────────────────────────────────────────────────┐   │
│  │  ingest-gly (Galei Yisrael radio, daily) — runs in parallel           │   │
│  └───────────────────────────────────┬──────────────────────────────────┘   │
│         │                 │          │          │                   │        │
│         └─────────┬───────┴──────────┴──────────┴───────────┬───────┘        │
│                   │                                         │                │
│  Separate "tohar" collection (col:3x-tagged POIs, twice daily 08:00 & 20:00,  │
│  via the `tohar-collection` workflow, NOT the above jobs):                   │
│  ingest-x-api-tohar (official X API) + ingest-fb-tohar (ScrapeCreators)      │
│  ──► twitter_tweets, fb_pages/profiles/posts                                 │
│                                                                             │
│  Pixel (disguised-venue detection, ScrapeCreators, daily 06:00):             │
│  ingest-pixel ──► pixel_venues, pixel_posts, pixel_venue_snapshots,          │
│                   pixel_lean_daily, pixel_flip_state, pixel_coordination     │
│                                                                             │
└───────────────────┼─────────────────────────────────────────┼────────────────┘
                    │                 │                   │
                    ▼                 ▼                   ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│              ② STORAGE — BigQuery `intel-487218.datawarehouse`              │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                             │
│  Raw tables (per platform): twitter_*, tiktok_*, fb_pages/profiles/posts,   │
│  tv_*, online_paper_*, mainstream_articles, kb_*, telegram_*, gly_*,        │
│  pixel_* (venues/posts/snapshots/lean_daily/flip_state/coordination —       │
│           investigation targets, kept separate from fb_pages/fb_posts)      │
│  Audit tables:              <platform>_etl_runs, <platform>_etl_run_errors  │
│  Fetch tracking:            <platform>_posts_fetches (delta windows)        │
│                                                                             │
│  ──── Reads ────►  ③ Analysis  ──── Writes ────►                            │
│                                                                             │
│  Analysis tables: narratives, mention_daily, narrative_sources,             │
│                   subjects_daily, subjects_sources, emerging_terms,         │
│                   dashboard_summary, brief_correlation, post_correlation    │
│  PG analysis results: events, post_alignment, weekly_briefs, daily_brief    │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────┘
                                      │
                                      ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│            ③ PROCESSING — Analysis pipelines (Cloud Run Jobs)               │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                             │
│   ┌──────────────────────┐   ┌──────────────────────┐                       │
│   │      analysis        │   │  analysis-subjects   │                       │
│   │  (narratives,        │   │  (subject extraction │                       │
│   │   meta-narratives,   │   │   + alignment)       │                       │
│   │   embeddings)        │   │                      │                       │
│   └──────────┬───────────┘   └──────────┬───────────┘                       │
│              │                          │                                   │
│              └──────────┬───────────────┘                                   │
│                         ▼                                                   │
│   ┌──────────────────────────────────────────────────────────┐              │
│   │              messaging-funnel-workflow                   │              │
│   │   collection → ingest-fb-publishers → alignment          │              │
│   │   (Twitter + Facebook posts → Gemini judge → BQ)         │              │
│   └────────────────────────┬─────────────────────────────────┘              │
│                            ▼                                                │
│   ┌────────────────────┐  ┌───────────────────┐  ┌────────────────────┐     │
│   │   events-pipeline  │  │   weekly-brief    │  │  messaging-briefs  │     │
│   │   (every 2h)       │  │    (daily)        │  │     (daily)        │     │
│   └────────────────────┘  └───────────────────┘  └────────────────────┘     │
│                            ┌───────────────────┐                             │
│                            │   daily-brief     │                             │
│                            │    (daily)        │                             │
│                            └───────────────────┘                             │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────┘
                                      │
                                      ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                  ④ PRESENTATION — Backend API + Frontend                    │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                             │
│   FastAPI backend (Render Web Service)                                      │
│   ├─ /api/v1/sources/...        ──► /sources page (overview, drill-down)    │
│   ├─ /api/v1/bq-cache/...       ──► /cc Twitter cache (read + writeback)    │
│   ├─ /api/v1/fb-cache/...       ──► /cc Facebook cache (read + writeback)   │
│   ├─ /api/v1/cc/...             ──► /cc Claude Code session orchestrator    │
│   ├─ /api/v1/daily-brief/...    ──► daily brief (per audience, PDF/Slack)   │
│   ├─ /api/v1/events/, papers/, narratives/, subjects/, ... (read API)       │
│   └─ /mcp  ──► MCP Intel server (~14 tool modules: twitter, tiktok,        │
│               facebook, instagram, tv, gly, kb, papers, narratives,         │
│               subjects, events, briefs, sources, domain_knowledge, …)       │
│                                                                             │
│   PostgreSQL (Render managed)                                               │
│   ├─ poi (source-of-truth POIs to track)                                    │
│   ├─ publisher_outlet (handles per platform — tw, tt, fb, ig)               │
│   ├─ users / sessions / roles (auth)                                        │
│   ├─ domain_knowledge, briefs metadata                                      │
│   └─ post_alignment (alignment results)                                     │
│                                                                             │
│   React UI (Render Static Site)                                             │
│   ├─ /sources             — platform tiles (real brand icons via            │
│   │                         react-icons + lucide), drill-down, timelines    │
│   ├─ /cc                  — Claude Code chat (SSE streaming)                │
│   ├─ /daily-brief, /weekly-brief, /events, /narratives, /subjects, …        │
│   └─ /admin/...           — admin tools (users, domain knowledge, …)        │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────┘
                                      ▲
                                      │
┌─────────────────────────────────────────────────────────────────────────────┐
│              ⑤ /cc agent (Claude Code Web UI) — interactive               │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                             │
│   User → /cc UI → Backend `cc_service` → ClaudeSDKClient (Anthropic API)    │
│                          │                                                  │
│                          ├──► MCP Intel server (tweets, fb, papers, …)      │
│                          ├──► XPOZ MCP (live Twitter retrieval)             │
│                          ├──► /api/v1/bq-cache (Twitter cache + writeback)  │
│                          └──► /api/v1/fb-cache (Facebook cache + writeback) │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────┘

See [`data-flow.txt`](diagrams/data-flow.txt) for the full file, including the detailed Publishers/messaging-funnel and Events sub-pipeline diagrams (omitted here for length).

Deployment diagram (ASCII, click to expand)

Intel — Deployment Diagram
==========================

Last updated: 2026-07-12

Two clouds: Render (user-facing app) and GCP (heavy ETL + datawarehouse).
External APIs (Anthropic, ScrapeCreators, XPOZ, official X API, Apify,
Gemini) are called from both.


┌──────────────────────────────────────────────────────────────────────────────┐
│                                  USERS                                       │
│           (browsers — hamal-elections.co.il Google SSO only)                 │
└──────────────────────────────────────┬───────────────────────────────────────┘
                                       │ HTTPS
                                       ▼
┌──────────────────────────────────────────────────────────────────────────────┐
│                              RENDER (PaaS)                                   │
│                           Workspace: Hamal-Elections                         │
├──────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│   ┌──────────────────────────────┐    ┌────────────────────────────────┐     │
│   │   frontend-km4p              │    │   backend-km4p                 │     │
│   │   (Static Site)              │    │   (Web Service)                │     │
│   │                              │    │                                │     │
│   │   React + Vite + Tailwind    │◄──►│   FastAPI / async SQLAlchemy   │     │
│   │   shadcn/ui, react-icons,    │    │   /mcp (FastMCP), /cc (SSE)    │     │
│   │   lucide-react               │    │   ClaudeSDKClient sessions     │     │
│   └──────────────────────────────┘    └────────────┬───────────────────┘     │
│                                                    │                         │
│   ┌──────────────────────────────┐                 ▼                         │
│   │   PostgreSQL (managed)       │   ┌────────────────────────────────┐      │
│   │                              │◄──┤   App DB connection            │      │
│   │   poi, publisher_outlet,     │   └────────────────────────────────┘      │
│   │   users, roles, sessions,    │                                           │
│   │   domain_knowledge, briefs,  │                                           │
│   │   post_alignment, …          │                                           │
│   │   (Alembic-managed)          │                                           │
│   └──────────────────────────────┘                                           │
│                                                                              │
└────────────────────────────────────────┬─────────────────────────────────────┘
                                         │
              Backend reads BQ via service account; reads/writes PG
                                         │
                                         ▼
┌──────────────────────────────────────────────────────────────────────────────┐
│                       GOOGLE CLOUD (project intel-487218)                    │
│                            Region: us-west1                                  │
├──────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│  ┌────────────────────────────────────────────────────────────────────────┐  │
│  │                          Cloud Scheduler                               │  │
│  │   collection-analysis-trigger           ─►  Workflow collection-analysis│  │
│  │                                             (daily 13:00 + Fri 05:00)  │  │
│  │   events-pipeline-hourly-trigger        ─►  Workflow events-pipeline   │  │
│  │   events-pipeline-daily-trigger         ─►  Workflow events-pipeline-  │  │
│  │                                             daily (04:00)              │  │
│  │   messaging-funnel-workflow-daily-trigger─►  Workflow messaging-funnel │  │
│  │   messaging-brief-etl-daily-trigger     ─►  Job ingest-messaging-briefs│  │
│  │   ingest-tw-trigger                     ─►  Job ingest-tw (hourly)     │  │
│  │   pixel-ingest-daily-trigger            ─►  Job ingest-pixel (06:00)   │  │
│  │   tohar-collection-trigger-morning      ─►  Workflow tohar-collection  │  │
│  │                                             (08:00)                    │  │
│  │   tohar-collection-trigger-evening      ─►  Workflow tohar-collection  │  │
│  │                                             (20:00)                    │  │
│  └────────────────────────────────────┬───────────────────────────────────┘  │
│                                       │                                      │
│                                       ▼                                      │
│  ┌────────────────────────────────────────────────────────────────────────┐  │
│  │                       Cloud Workflows                                  │  │
│  │   collection-analysis         — daily ingest fan-out + analysis +      │  │
│  │                                 events-pipeline + briefs               │  │
│  │   events-pipeline             — every 2h: mainstream-papers + events   │  │
│  │   events-pipeline-daily       — daily 04:00: events 7d rescore         │  │
│  │   messaging-funnel-workflow   — daily 07:30 IL: collection→fb→align    │  │
│  │   tohar-collection            — 08:00 & 20:00: X API + FB for col:3x   │  │
│  │                                 POIs (ingest-x-api-tohar/-fb-tohar)     │  │
│  └────────────────────────────────────┬───────────────────────────────────┘  │
│                                       │                                      │
│                                       ▼                                      │
│  ┌────────────────────────────────────────────────────────────────────────┐  │
│  │                      Cloud Run Jobs (`etl` image)                      │  │
│  │                                                                        │  │
│  │   INGEST                              ANALYSIS                         │  │
│  │   ─ ingest-tw  (Twitter/X, XPOZ)      ─ analysis                       │  │
│  │   ─ ingest-ig  (Instagram, XPOZ,      ─ analysis-subjects              │  │
│  │                 sequential after tw)  ─ events-pipeline                │  │
│  │   ─ ingest-x-api (official X API,     ─ events-pipeline-daily          │  │
│  │      col:x-api/kashaviya POIs)        ─ messaging-funnel-collection    │  │
│  │   ─ ingest-tiktok (ScrapeCreators)     ─ messaging-funnel-alignment     │  │
│  │   ─ ingest-fb (ScrapeCreators)        ─ ingest-messaging-briefs        │  │
│  │   ─ ingest-fb-publishers              ─ weekly-brief                   │  │
│  │   ─ ingest-gly  (Galei Yisrael)       ─ daily-brief                    │  │
│  │   ─ ingest-tv                                                          │  │
│  │   ─ ingest-online-papers                                               │  │
│  │   ─ ingest-mainstream-papers (hourly, feeds events-pipeline)           │  │
│  │   ─ ingest-kb                                                          │  │
│  │   ─ ingest-telegram                                                    │  │
│  │   ─ ingest-pixel (ScrapeCreators, daily 06:00 — disguised venues)       │  │
│  │   ─ ingest-x-api-tohar + ingest-fb-tohar (col:3x POIs, 08:00 & 20:00,   │  │
│  │      via the tohar-collection Cloud Workflow — not shown above)        │  │
│  │                                                                        │  │
│  └─────────┬─────────────────────────────────┬────────────────────────────┘  │
│            │ writes                          │ reads/writes                  │
│            ▼                                 ▼                               │
│  ┌──────────────────────────┐    ┌──────────────────────────────────────┐    │
│  │   BigQuery               │    │   Secret Manager                     │    │
│  │   datawarehouse          │    │   ─ scrapecreators-api-key           │    │
│  │                          │    │   ─ xpoz-mcp-tokens                  │    │
│  │   raw + analysis tables  │    │   ─ x-bearer-token                   │    │
│  └──────────────────────────┘    │   ─ apify-api-key (FB-search only —  │    │
│                                  │     pixel_discover / fb_coordination-│    │
│                                  │     _spike, ScrapeCreators has none) │    │
│                                  │   ─ database-url                    │    │
│                                  │   ─ slack-*-webhook-url(s)           │    │
│                                  │   ─ anthropic-api-key                │    │
│                                  │   ─ gemini-api-key                   │    │
│                                  └──────────────────────────────────────┘    │
│                                                                              │
│  ┌──────────────────────────┐    ┌──────────────────────────────────────┐    │
│  │  Cloud Build (us-west1)  │───►│   Artifact Registry                  │    │
│  │  (auto on push to main)  │    │   us-west1-docker.pkg.dev/.../etl    │    │
│  └──────────────────────────┘    └──────────────────────────────────────┘    │
│                                                                              │
│  ┌──────────────────────────────────────────────────────────────────────┐    │
│  │     Service account: intel-service@intel-487218.iam                  │    │
│  │     Used by: Cloud Run Jobs, Workflows, Schedulers                   │    │
│  └──────────────────────────────────────────────────────────────────────┘    │
│                                                                              │
└──────────────────────────────────────────────────────────────────────────────┘


External APIs (called from Cloud Run Jobs and/or Render backend)
================================================================

  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐  ┌────────────────┐
  │  Anthropic   │  │ScrapeCreators│  │     XPOZ     │  │   Gemini API   │
  │  (Claude API │  │  (TikTok +   │  │  (Twitter/X  │  │ (judge models, │
  │   for /cc,   │  │  Facebook +  │  │   + IG live  │  │   embeddings)  │
  │   summaries) │  │   Pixel)     │  │   data)      │  │                │
  └──────────────┘  └──────────────┘  └──────────────┘  └────────────────┘
  ┌──────────────┐  ┌──────────────┐
  │ official X   │  │    Apify     │
  │ API (col:x-  │  │ (FB organic  │
  │ api/kashaviya│  │  search only │
  │ /col:3x POIs)│  │  — pixel_    │
  │              │  │  discover)   │
  └──────────────┘  └──────────────┘
  ┌──────────────┐
  │ Google Drive │
  │ (TV files,   │
  │   KB files)  │
  └──────────────┘

See [`deployment.txt`](diagrams/deployment.txt) for the full file (CI/CD and admin URL sections omitted here for length).

Ingestion (Collection)

Scheduled Cloud Run Jobs (region us-west1) pull from each source and write raw rows to BigQuery. POI handles to track come from Postgres (pois). No ingest job writes to Postgres — all raw data lands in BigQuery.

Source	Collected via	Entrypoint	Cadence	Lands in (BQ)
Twitter / X	XPOZ API	`backend/xpoz_etl.py`	hourly	`twitter_*`
Twitter / X (official API)	official X API	`backend/x_api_etl.py` (`ingest-x-api`, `col:x-api`/`kashaviya`-tagged POIs, incremental via `since_id`)	daily 13:00 (+ Fri 05:00, within `collection-analysis`)	`twitter_tweets`
Twitter / X (tohar)	official X API	`backend/x_api_etl.py` (`ingest-x-api-tohar`, `--tags col:3x`)	twice daily (08:00, 20:00, via `tohar-collection` workflow)	`twitter_tweets`
Instagram	XPOZ API	`backend/ig_etl.py`	daily (after Twitter)	`ig_*`
TikTok	ScrapeCreators	`backend/tiktok_etl.py`	daily	`tiktok_*`
Facebook	ScrapeCreators	`backend/fb_etl.py` (`ingest-fb` + `ingest-fb-publishers`)	daily	`fb_pages`, `fb_profiles`, `fb_posts`
Facebook (tohar)	ScrapeCreators	`backend/fb_etl.py` (`ingest-fb-tohar`, `--tags col:3x`)	twice daily (08:00, 20:00, via `tohar-collection` workflow)	`fb_pages`, `fb_profiles`, `fb_posts`
Pixel (disguised venues)	ScrapeCreators	`backend/scripts/pixel_ingest.py` (`ingest-pixel`)	daily 06:00	`pixel_*` (see below)
Telegram	web scraper	`backend/telegram_etl.py`	daily	`telegram_*`
TV (Channel 14)	Google Drive	`backend/tv_etl.py`	daily	`tv_*`
Radio (Galei Yisrael)	site scraper	`backend/gly_etl.py`	daily	`gly_*`
Online papers	site scrapers	`backend/online_paper_etl.py`	every 2h / daily	`online_paper_articles`
Mainstream papers	site scrapers	`backend/mainstream_papers_etl.py` (`ingest-mainstream-papers`; feeds the events pipeline, separate taxonomy from Online papers above)	hourly (within `events-pipeline` workflow)	`mainstream_articles`
Knowledge Base	Google Drive	`backend/kb_etl.py`	daily	`kb_*`

Each pipeline follows the same pattern: raw content tables, plus <platform>_etl_runs / _etl_run_errors (run audit) and <platform>_*_fetches (delta-window tracking). Raw tables upsert through a transient <table>_staging twin.

Processing (Analysis)

Analysis jobs read from BigQuery, load their catalogs (narratives, subjects, POIs, domain knowledge) from Postgres at startup, and write outputs back to both stores.

Pipeline	Entrypoint	Reads	Writes
Narratives	`backend/analysis_narratives.py`	BQ raw posts	PG `narratives` (catalog) · BQ `narratives`, `mention_daily`, `narrative_sources`, `emerging_terms`, `dashboard_summary`
Subjects	`backend/analysis_subjects.py`	BQ raw posts	PG `subjects` · BQ `subjects_sources`, `subjects_daily`
Events	`backend/events_pipeline.py`	BQ `mainstream_articles` (clustering input) + `online_paper_articles`/social/TV counts (amplification)	PG `events`, `event_sources`, `event_feedback`
Messaging-funnel alignment	`backend/messaging_funnel_workflow.py`	BQ `twitter_tweets`, `fb_posts`	PG `post_alignment`
Briefs (weekly / daily)	`backend/weekly_brief_etl.py`, daily brief ETL	BQ aggregates + PG catalogs	PG `weekly_briefs`, `daily_brief`
Keyword monitors	`keyword_monitor_runner.py` (on-demand)	BQ raw tables	PG `keyword_monitors`, `keyword_narratives`

Most analysis uses Gemini for classification, extraction, embeddings, and Hebrew synthesis.

When the flows run

All schedules are Asia/Jerusalem; schedulers live in us-west1 (Cloud Scheduler → Workflows).

Time (IL)	What runs
hourly	`ingest-tw` (Twitter)
every 2h, 06:00–22:00	`events-pipeline` (mainstream-papers → events analysis, 3h window)
daily 04:00	`events-pipeline-daily` (7-day rescore window)
daily 06:00	`ingest-messaging-briefs` (parse desk briefs); `ingest-pixel` (disguised-venue collection)
daily 07:30	`messaging-funnel-workflow` (collection → FB publishers → alignment)
08:00 & 20:00 daily	`tohar-collection` workflow: `ingest-x-api-tohar` + `ingest-fb-tohar` (official X API + FB for `col:3x`-tagged POIs)
daily 13:00 (+ extra Fri 05:00)	`collection-analysis`: all ingest jobs → narratives + subjects analysis → events → weekly & daily briefs

To check the last successful run of any pipeline, query its <platform>_etl_runs table in BigQuery, or use /admin/jobs in the app.

Where things live — BigQuery vs PostgreSQL

The same concept often has a definition in Postgres and time-series data in BigQuery. This map tells you which side to query:

Entity	PostgreSQL (source of truth)	BigQuery
Narratives	`narratives`	`narratives`, `mention_daily`, `narrative_sources`
Meta-narratives	`meta_narratives`	denormalized onto `narratives.meta_narrative`
Group narratives	`narrative_groups` + `narratives`	same `narratives` / `mention_daily` / `narrative_sources`
Keyword narratives	`keyword_monitors`, `keyword_narratives`	(reads raw tables; no dedicated BQ output)
Subjects	`subjects`	`subjects_sources`, `subjects_daily`
Events	`events`, `event_sources`, `event_feedback`	(reads `online_paper_articles` + counts; no BQ output)
Weekly / daily briefs	`weekly_briefs`, `daily_brief`	—
POIs / domain knowledge	`pois`, `domain_knowledge`	—
Raw social/news/TV/radio content	—	`twitter_`, `tiktok_`, `fb_`, `ig_`, `telegram_`, `tv_`, `gly_`, `online_paper_articles`, `kb_`
Pixel (disguised-venue investigation)	—	`pixel_venues`, `pixel_posts`, `pixel_venue_snapshots`, `pixel_lean_daily`, `pixel_flip_state`, `pixel_coordination`

BigQuery table catalog

Grouped by source. Key columns are a starting point — the full schema is visible in the BigQuery console.

🐦 Twitter / X

Table	Contents	Key columns
`twitter_users`	X profiles	`username`, `name`, `description`, `location`, `followers_count`, `verified`, `gist` (AI role summary)
`twitter_tweets`	Tweets (partitioned by `created_at` day)	`id`, `text`, `author_username`, `created_at`, `created_at_date`, `like_count`, `retweet_count`, `reply_count`, `quote_count`, `hashtags`
`twitter_followers`	follower→followee edges	`follower_id`, `followee_id`
`twitter_user_geocodes`	geocoded user locations	`normalized_location`, `latitude`, `longitude`

🎵 TikTok

Table	Contents	Key columns
`tiktok_users`	accounts	`username`, `nickname`, `follower_count`, `like_count`, `post_count`, `is_verified`
`tiktok_posts`	videos	`id`, `username`, `description`, `created_at_date`, `play_count`, `like_count`, `comment_count`, `share_count`, `transcript`

📘 Facebook

Table	Contents	Key columns
`fb_pages`	pages	`username`, `name`, `category`, `followers_count`, `page_url`
`fb_profiles`	personal profiles	`username`, `name`, `bio`, `friends_count`, `location`
`fb_posts`	posts (pages + profiles + groups)	`id`, `source_type`, `username`, `text`, `permalink`, `created_at_date`, `reactions_total`, `comments_count`, `shares_count`

📸 Instagram

Table	Contents	Key columns
`ig_users`	accounts	`username`, `full_name`, `biography`, `follower_count`, `media_count`
`ig_posts`	posts / reels	`id`, `username`, `post_type`, `caption`, `like_count`, `comment_count`, `video_play_count`, `transcript`, `created_at_date`

✈️ Telegram

Table	Contents	Key columns
`telegram_channels`	channels	`username`, `title`, `description`, `member_count`
`telegram_messages`	messages	`id`, `channel_username`, `text`, `created_at_date`, `views`, `forwards`, `forwarded_from_channel`, `link_domain`

📺 TV (Channel 14) & 📻 Radio (Galei Yisrael)

Table	Contents	Key columns
`tv_documents`	parsed broadcast transcripts	`broadcast_date`, `doc_type`, `content_markdown`, `word_count`, `speakers`
`tv_segments`	per-speaker transcript segments	`file_id`, `speaker`, `text`, `timestamp_str`
`gly_segments`	radio program segment headlines	`program_name`, `headline`, `subtitle`, `broadcast_date`, `mp3_url`
`gly_programs`	program registry	`program_id`, `title`, `description_html`

📰 Online papers & 📚 Knowledge Base

Table	Contents	Key columns
`online_paper_articles`	news articles	`outlet_slug`, `title`, `author`, `url`, `published_at`, `summary`, `content_text`, `ai_summary`
`kb_documents`	internal Drive documents	`file_id`, `content_markdown`, `word_count`, `page_count`

📈 Analysis time-series

Table	Contents	Key columns
`narratives`	narrative snapshot (denormalized)	`narrative_id`, `name_he`, `meta_narrative`, `escalation_stage`, `political_camp`
`mention_daily`	daily narrative metrics	`narrative_id`, `source_platform`, `mention_date`, `volume`, `engagement`, `sentiment_*`
`narrative_sources`	per-post narrative mentions	`narrative_id`, `source_item_id`, `source_platform`, `mention_date`, `is_poi`
`subjects_daily`	daily subject metrics	`subject_id`, `mention_date`, `source_platform`, `camp`, `volume`, `engagement`, `top_actors`, `top_posts`
`subjects_sources`	per-post subject mentions	`subject_id`, `source_platform`, `author_id`, `camp`, `mention_date`, `engagement`
`emerging_terms`	trending terms	`term`, `heat_score`, `growth_pct`, `first_seen_date`
`dashboard_summary`	per-run KPIs	`run_date`, `total_posts_7d`, `active_narratives_count`

🕵️ Pixel (disguised-venue detection)

Kept deliberately separate from fb_pages/fb_posts: these are investigation targets (suspected sleeper pages), not tracked POIs.

Table	Contents	Key columns
`pixel_venues`	candidate venues + A1 provenance	`handle`, `name`, `page_url`, `admin_country`, `former_names`, `runs_political_ads`
`pixel_posts`	venue posts	`post_id`, `venue_handle`, `text`, `reactions_total`, `is_political`, `stance_json`
`pixel_venue_snapshots`	daily follower/like/post-count snapshots	`handle`, `snapshot_date`, `followers_count`, `likes_count`, `post_count`
`pixel_lean_daily`	daily pro-Likud lean per venue (flip-watch timeline)	`handle`, `day`, `lean`, `political_posts`
`pixel_flip_state`	latest flip verdict per venue (dedup for alerting)	`handle`, `flipped`, `changepoint_day`, `lean_before`, `lean_after`
`pixel_coordination`	cross-venue co-shared URL/media clusters	`cluster_id`, `kind`, `venues`, `coordinated`, `span_minutes`, `evidence`

Ignore operational tables ending in _etl_runs, _run_errors, _fetches, and _staging — they are internal ETL bookkeeping, not analysis data.

Full BigQuery table inventory

The sections above are the analyst-facing curated view. For completeness, this is every table any backend code defines or writes to in intel-487218.datawarehouse, grouped by the source file that owns its schema. 🗃️ = content/analysis table (covered above); ⚙️ = operational/internal (audit, delta-tracking, cache) — safe to ignore for analysis.

Table	Owning file	Kind
`twitter_users`	`xpoz_etl_bigquery.py` / `xpoz_etl.py`	🗃️
`twitter_tweets`	`xpoz_etl_bigquery.py` / `xpoz_etl.py`	🗃️
`twitter_followers`	`xpoz_etl_bigquery.py` / `xpoz_etl.py`	🗃️
`twitter_user_geocodes`	`xpoz_etl.py`	🗃️
`tweet_context_cache`	`xpoz_etl.py`	⚙️
`twitter_tweets_fetches`	`xpoz_etl.py`	⚙️
`twitter_tweet_interactions_fetches`	`xpoz_etl.py`	⚙️
`twitter_followers_fetches`	`xpoz_etl.py`	⚙️
`twitter_following_fetches`	`xpoz_etl.py`	⚙️
`etl_runs`, `etl_run_errors`	`xpoz_etl.py` (shared Twitter/IG audit)	⚙️
`ig_users`, `ig_posts`	`ig_etl_bigquery.py`	🗃️
`ig_posts_fetches`	`ig_etl_bigquery.py`	⚙️
`ig_etl_runs`, `ig_etl_run_errors`	`ig_etl_bigquery.py`	⚙️
`ig_enrichment_thresholds`	`ig_etl_bigquery.py`	⚙️
`tiktok_users`, `tiktok_posts`	`tiktok_etl_bigquery.py`	🗃️
`tiktok_posts_fetches`	`tiktok_etl_bigquery.py`	⚙️
`tiktok_etl_runs`, `tiktok_etl_run_errors`	`tiktok_etl_bigquery.py`	⚙️
`fb_pages`, `fb_profiles`, `fb_posts`	`fb_etl_bigquery.py`	🗃️
`fb_posts_fetches`, `fb_entities_fetches`	`fb_etl_bigquery.py`	⚙️
`fb_etl_runs`, `fb_etl_run_errors`	`fb_etl_bigquery.py`	⚙️
`telegram_channels`, `telegram_messages`	`telegram_etl_bigquery.py`	🗃️
`telegram_messages_fetches`	`telegram_etl_bigquery.py`	⚙️
`telegram_etl_runs`, `telegram_etl_run_errors`	`telegram_etl_bigquery.py`	⚙️
`tv_documents`, `tv_segments`	`tv_etl_bigquery.py`	🗃️
`tv_files`, `tv_file_scans`	`tv_etl_bigquery.py`	⚙️
`tv_etl_runs`, `tv_etl_run_errors`	`tv_etl_bigquery.py`	⚙️
`gly_segments`, `gly_programs`	`gly_etl_bigquery.py`	🗃️
`gly_etl_runs`	`gly_etl_bigquery.py`	⚙️
`online_paper_articles`	`online_paper_etl_bigquery.py`	🗃️
`online_paper_etl_runs`, `online_paper_etl_run_errors`	`online_paper_etl_bigquery.py`	⚙️
`mainstream_articles`	`mainstream_papers_etl_bigquery.py` (events-pipeline's news source — separate taxonomy from `online_paper_articles`)	🗃️
`mainstream_etl_runs`, `mainstream_etl_run_errors`	`mainstream_papers_etl_bigquery.py`	⚙️
`kb_documents`	`kb_etl_bigquery.py`	🗃️
`kb_files`, `kb_file_scans`	`kb_etl_bigquery.py`	⚙️
`kb_etl_runs`, `kb_etl_run_errors`	`kb_etl_bigquery.py`	⚙️
`pixel_venues`, `pixel_posts`, `pixel_venue_snapshots`, `pixel_lean_daily`, `pixel_flip_state`, `pixel_coordination`	`pixel_etl_bigquery.py`	🗃️
`narratives`, `mention_daily`, `narrative_sources`, `emerging_terms`, `dashboard_summary`	`analysis_narratives_bigquery.py`	🗃️
`camp_hourly_cdf`	`analysis_narratives_bigquery.py` (posting-time model for the events amplification baseline)	🗃️
`tweet_media_enrichment`	`analysis_narratives_bigquery.py` (OCR/vision cache for tweet images)	⚙️
`post_entities`	`analysis_narratives_bigquery.py` (per-post NER cache for the entity-mentions report)	⚙️
`entity_mention_daily`	`analysis_narratives_bigquery.py` (per-audience daily entity-mention counts)	🗃️
`analysis_narratives_runs`, `analysis_narratives_run_errors`	`analysis_narratives_bigquery.py`	⚙️
`subjects_sources`, `subjects_daily`	`analysis_subjects_bigquery.py`	🗃️
`analysis_subjects_runs`, `analysis_subjects_run_errors`	`analysis_subjects_bigquery.py`	⚙️
`brief_correlation`, `post_correlation`	`report_correlation.py` (cosine-similarity scores linking messaging briefs/items to publisher posts)	🗃️
`narrative_centroids`, `subject_centroids`	`embedding_utils.py` (embedding-centroid cache used by the events pipeline to correlate articles to subjects/narratives)	⚙️

Query these through the app / API rather than direct SQL. Grouped by role (representative, not exhaustive):

Group	Tables
Catalogs & definitions	`pois`, `narratives`, `meta_narratives`, `narrative_groups`, `subjects`, `subject_narratives`, `keyword_monitors`, `keyword_narratives`, `domain_knowledge`, `message_narrative_catalog`
Analysis results	`events`, `event_sources`, `event_feedback`, `post_alignment`, `weekly_briefs`, `daily_brief`, `channel_metrics_daily`, `kpi_daily_snapshots`, `network_analysis_results`, `network_cluster_summaries`
Messaging & audiences	`audiences`, `audience_tags`, `audience_reports`, `message_brief`, `message_narrative`, `message_subtitle`, `message_item`, `messaging_item`, `messaging_item_metric`, `publisher`, `publisher_user`, `publisher_channel`, `intelligence_report`
Product surfaces	`cc_sessions`, `cc_messages`, `cc_config`, `situation_rooms`, `situation_room_stories`, `recommended_items`, `item_distributions`
Auth & ops	`users`, `roles`, `permissions`, `role_permissions`, `user_roles`, `oauth_clients`, `oauth_codes`, `oauth_refresh_tokens`, `api_tokens`, `operation_costs`, `pipeline_runs`

Accessing the data

BigQuery (analysts)

Your identity is your @hamal-elections.co.il Google account. To query, you need these IAM roles on project intel-487218:

Role	Why
`roles/bigquery.dataViewer`	read the tables
`roles/bigquery.jobUser`	run queries
`roles/bigquery.studioUser` + `roles/bigquery.readSessionUser`	(optional) BigQuery Studio / notebooks

Ask one of the project developers to grant these — see For admins.

Three ways to query:

BigQuery Studio (no install): open https://console.cloud.google.com/bigquery?project=intel-487218, expand intel-487218 → datawarehouse, write SQL, Run.
bq CLI: bash gcloud auth login gcloud config set project intel-487218 bq query --use_legacy_sql=false \ 'SELECT username, followers_count FROM `intel-487218.datawarehouse.twitter_users` ORDER BY followers_count DESC LIMIT 10'
Python: ```python from google.cloud import bigquery

client = bigquery.Client(project="intel-487218") for r in client.query( "SELECT username, followers_count " "FROM intel-487218.datawarehouse.twitter_users " "ORDER BY followers_count DESC LIMIT 10" ).result(): print(r.username, r.followers_count) ```

PostgreSQL

The Postgres DB is the application's operational store. For almost everyone, the right way to read it is through the product:

The app UI at hml.st (narratives, subjects, events, briefs, sources).
The read API under /api/v1/... and the Intel MCP server at api.hml.st/mcp (for AI clients).

Direct SQL access to Postgres is reserved for engineers via the Render dashboard.

Example queries (BigQuery)

Top tweets by likes in the last 7 days (filter on the partition column to keep it cheap):

SELECT author_username, text, like_count, retweet_count
FROM `intel-487218.datawarehouse.twitter_tweets`
WHERE created_at_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
ORDER BY like_count DESC
LIMIT 10;

Daily mention volume of a narrative:

SELECT mention_date, SUM(volume) AS volume, SUM(engagement) AS engagement
FROM `intel-487218.datawarehouse.mention_daily`
WHERE narrative_id = 'NARRATIVE_ID_HERE'
GROUP BY mention_date
ORDER BY mention_date;

Search Telegram messages for a term:

SELECT channel_username, created_at_date, views, text
FROM `intel-487218.datawarehouse.telegram_messages`
WHERE text LIKE '%TERM%'
  AND created_at_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
ORDER BY views DESC
LIMIT 50;

Top subjects on TikTok yesterday:

SELECT subject_id, SUM(volume) AS volume
FROM `intel-487218.datawarehouse.subjects_daily`
WHERE mention_date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  AND source_platform = 'tiktok'
GROUP BY subject_id
ORDER BY volume DESC
LIMIT 20;

Things worth knowing (gotchas)

Filter by date to save money. twitter_tweets and the post tables are partitioned by created_at_date. Always add WHERE created_at_date >= ... — otherwise the query scans the whole table and costs more.
Skip operational tables (_etl_runs, _run_errors, _fetches, _staging).
camp / political_camp marks the political camp — useful for slicing subjects/narratives.
Hebrew text: use LIKE '%...%' for exact matching, or REGEXP_CONTAINS for smarter matching.
Data is not real-time. Most sources refresh daily (Twitter hourly, papers every 2h). Check freshness via the _etl_runs tables or /admin/jobs.
The catalog can drift. This page is regenerated from the codebase by the /update-datadocs maintenance command — if a table here looks wrong, re-run it (see below).

For admins: granting BigQuery access

Give a new analyst read-only access with:

gcloud projects add-iam-policy-binding intel-487218 \
  --member="user:NEW_ANALYST@hamal-elections.co.il" \
  --role="roles/bigquery.dataViewer" --condition=None
gcloud projects add-iam-policy-binding intel-487218 \
  --member="user:NEW_ANALYST@hamal-elections.co.il" \
  --role="roles/bigquery.jobUser" --condition=None

# optional — BigQuery Studio / notebooks
gcloud projects add-iam-policy-binding intel-487218 \
  --member="user:NEW_ANALYST@hamal-elections.co.il" \
  --role="roles/bigquery.studioUser" --condition=None
gcloud projects add-iam-policy-binding intel-487218 \
  --member="user:NEW_ANALYST@hamal-elections.co.il" \
  --role="roles/bigquery.readSessionUser" --condition=None

Developers get a broader custom role (intelDev, includes write) via make gcloud-grant-dev-access. Do not give that to analysts — read-only is enough.

How this page is built and published

docs/data/render.py renders this Markdown into a styled, self-contained docs/data/site/index.html (table of contents, dark/light, copy-to-clipboard on code).
.github/workflows/publish-datadocs.yml runs the renderer and deploys to Cloudflare Pages on every push to main touching docs/data/**.
Authentication is enforced by Cloudflare Access — only @hamal-elections.co.il Google logins reach datadocs.hml.st.

Preview locally:

uv run --with markdown python docs/data/render.py
open docs/data/site/index.html

Keep it current: run the /update-datadocs command — it re-scans the codebase for schema, source, and schedule changes and updates this file. One-time CI/Access setup is documented in docs/data/README.md.