Glean Health — what the data would look like as a relational schema

One-time orientation page. The actual storage is JSON-on-disk (see data/extracted/, data/pipeline/, data/authors/). This page is the same shape projected into Postgres-style tables — useful for forming a mental model of joins and granularity. Sample rows are real, pulled from the on-disk corpus as of 2026-05-28.

Threaded through every sample is one specific UC post (1rmcvbj) where the author talked about "inflammation in sigmoid" while trying Remicade, Tremfya, and prednisone — so you can trace one Reddit post all the way through to its cluster row.

Companion artifact: data_dictionary.csv — flat machine-readable schema (one row per column, 103 rows total). Open in Excel to sort/filter by table, key type, or FK reference. This page and the CSV are coupled: any schema change should update both.

Relationships at a glance

                            +-------------+
                            | conditions  |    26 rows
                            +------+------+
                                   |
                                   | 1:N
                                   v
                            +-------------+
                            |   sources   |    29 rows (some conditions have multiple subs)
                            +------+------+
                                   |
                                   | 1:N
                                   v
+-----------+   1:N   +-----------------+   1:1   +-------------+
|  authors  |<--------+    raw_items    +-------->| extractions |  102,458 extracted JSONs
+-----------+         +-----------------+         +------+------+
      ^                       ^                          |
      | 1:N                   | (only some raw items     | 1:N
      |                       |  pass filter+extract)    v
+-----+-----+                 |                   +---------------+
| author_sub|                 |                   | post_symptoms |
|  _counts  |                 |                   +---------------+
+-----------+                 |                          |
                              |                          | (each extraction also has)
                              |                          v
                              |                   +-----------------+
                              |                   | treatments_raw  |   133,190 treatment events
                              |                   +--------+--------+
                              |                            |
                              |                  M:N       | 1:N
                              |              (clustered    v
                              |               at stage 7)  +-----------------------------+
                              |                            | treatment_symptoms_targeted |
                              |                            +-----------------------------+
                              |                            |
                              v                            v
                       +--------------+         +------------------------+
                       |   clusters   |<--------+ cluster_treatment_events|  M:N join
                       +------+-------+         +------------------------+
                              |
              +---------------+-----+--------------+----------+
              | 1:N           1:N    1:N            1:N        1:N
              v               v      v              v          v
       cluster_aliases  outcomes  cluster_subs   pubmed_articles
                       (per-     (which subs    (PubMed enrichment
                        cluster   the cluster    + classification pro/
                        outcome   was seen in)   con/neutral)
                        tally)

independent lookup/state:
  rxnorm_cache   58,894 input_name -> generic_name mappings (used at stage 7+9)
  manifest       per-condition x per-stage freshness tracking (the integrity perimeter)

Core ingestion (config → reddit data → LLM extraction)

1. conditions

The 26 chronic conditions the system covers. Each is a top-level “tile” on the patient surface. Adding a new condition = adding a row here + onboarding its subreddit sources.

Schema

Sample data

2. sources

The subreddits + date windows each condition pulls from. Most conditions have one source; T1D has three (diabetes_t1, Type1Diabetes, diabetes) because the condition is spread across general and specific subs.

Schema

Sample data

3. raw_items

Reddit submissions and comments after the term-match filter. The raw Reddit JSON has 50+ fields; only the load-bearing ones are modeled here. Each item gets at most one extraction (stage 4 LLM call).

Schema

Sample data

4. extractions

One row per Reddit item that survived the LLM extraction pass. The extraction itself produces structured JSON; this table holds the post-level metadata. Symptoms and treatments are joined off this row.

Schema

Sample data

5. post_symptoms

Symptoms the author mentions having (post-level — not specific to any one treatment). This is the field currently dropped during cluster aggregation — the data is here but the patient surface doesn't index it. Phase 1 of the symptom-first frontend = aggregating this into a per-cluster symptom-targeted counts map.

Schema

Sample data

6. treatments_raw

One row per treatment mention. This is the granular pre-clustering layer — each Reddit post mentioning Remicade produces a new treatment_event_id. Stage 7 (embedding + clustering) merges these into clusters.

Schema

Sample data

7. treatment_symptoms_targeted

The link table that says “treatment X was tried specifically for symptom Y.” This is the high-precision symptom signal — not every symptom the author mentioned, but specifically what they were targeting with this treatment. Currently aggregated to nothing on the patient surface; it's the input to the deferred symptom-first frontend.

Schema

Sample data

Authority signal (Phase B credibility)

8. authors

Per-author behavioral features used by Phase B credibility scoring (stage 6). Pulled from Arctic Shift author-history queries; n_items caps at 1000 due to Arctic Shift's per-author rate-limit floor. The truncated flag tells stage 6 to fall back to Phase A heuristics for those authors.

Schema

Sample data

9. author_sub_counts

Per-author per-subreddit activity counts. Used to compute sub_entropy, identify cross-condition authors (an author posting in >=2 of the 26 condition subs), and detect cluster-poisoning patterns.

Schema

Sample data

Clustering output (stage 7 + 8 + 9)

10. clusters

The aggregated treatments per condition. This is what powers the patient-facing Browse tab and the dev corpus view. Each row collapses many treatments_raw mentions of the same drug/treatment (under different LLM-written names) into one entity with a canonical_name and aggregate metrics.

Schema

Sample data

11. cluster_aliases

The original LLM-extracted names that all map to one canonical_name. Stage 7 builds these by embedding similarity + RxNorm brand→generic mapping. This is where the “humira / adalimumab / Hyrimoz / Yusimry” problem gets solved.

Schema

Sample data

12. cluster_outcomes

Tally of outcomes across all member treatment_events. Same enum as treatments_raw.outcome; this is the histogram per cluster.

Schema

Sample data

13. cluster_subs

Which subreddits the cluster's mentions came from. For single-source conditions this is one row; for multi-source like T1D it identifies how distributed the signal is.

Schema

Sample data

14. cluster_treatment_events

The M:N join between clusters and treatments_raw. This is what makes the patient drilldown work — clicking a cluster on the dashboard shows you the original posts.

Schema

Sample data

Evidence enrichment (stage 9 + 9.5)

15. pubmed_articles

PubMed enrichment per cluster. Stage 9 queries PubMed with "<canonical_name>[Title/Abstract] AND <condition_pubmed_term>[Title/Abstract]", fetches top-N (typically 5) abstracts, and stage 9.5 has Haiku classify each abstract as pro/con/neutral for that cluster.

Schema

Sample data

Lookup / state tables

16. rxnorm_cache

Brand→generic mapping cache for stage 7 (clustering canonicalization) and stage 9 (PubMed query construction). 58,894 entries; empty list = “not in RxNorm or in the manual fallback dict.” Hand-maintained fallback dict in extract/rxnorm.py covers non-US brands and recently-approved FDA drugs RxNorm hasn't ingested yet (e.g., slynd).

Schema

Sample data

17. manifest

Per-condition x per-pipeline-step freshness tracking. This is the integrity perimeter: each pipeline step writes a row on success, and check_manifest.py compares stored shas to current code/feature shas to detect staleness.

Schema

Sample data

What this view does NOT capture

• Text bodies in full — raw_items.body is shown truncated; the real corpus has the full text on disk in JSONL form.
• Embeddings — stage 7 uses sentence embeddings (sentence-transformers, in-memory only) for clustering. Not persisted.
• Author full history — data/authors/<username>.jsonl holds the raw 1000-item Arctic Shift pull per author. Only the aggregated features in authors survive.
• Proximity maps — per-condition CSV files (conditions/<name>_proximity_map.csv) that anti-poison clusters by tagging semantically-related terms. Currently one-cluster stubs for 23/26 conditions .
• Per-condition prompts — the LLM extraction prompt is in extract/prompt.md, condition-agnostic.

What lives where on disk (file→table mapping)