AI-Verified Economic Analytics Pipeline

Macro Economic Dashboard

This dashboard brings together 10 FRED economic series and 1,547 Hacker News stories about work and technology. A SQLite star schema supports the charts, recession model, topic analysis, and a conversational interface that answers questions in plain English. Every numerical claim generated by the language model is checked against a fresh database query before the dashboard displays it as verified.

I built it to show the parts of analytics work that are easy to omit from a model demo: ingestion, schema design, SQL, data adjustment, validation, testing, and a usable interface.

Source Code

The source code is on GitHub.

Dashboard

The Question

The information sector has been losing jobs per capita since 2022. Specialty trades keep growing. Power output is climbing. The yield curve sat inverted for over two years. I wanted to know whether these signals line up or whether I was reading patterns into noise.

I pulled 10 FRED series and 1,500+ Hacker News stories to examine it from two angles: the macro numbers (recession indicators, employment divergence, inflation) and the text (what tech workers are talking about, and whether their sentiment tracks the employment data).

The dashboard has five sections. The first lets visitors ask questions and receive answers checked against the database. The others cover macroeconomic conditions, recession risk, NLP topics, and the labor-market effects associated with AI adoption. The seed database includes 21 generated insights, each checked against the source data, and the test suite contains 573 tests.

Data Sources

I used FRED because it provides a consistent API for U.S. macroeconomic data. The 10 selected series also map cleanly into a star schema, which kept the ingestion work focused.

Hacker News replaced my original plan to use Reddit. Reddit's access requirements made a reproducible, credential-free demo impractical, while the Hacker News Algolia API requires no authentication.

The Hacker News audience still provides the labor and technology discussion I wanted to study. I pulled stories matching layoff, AI jobs, and career themes from January 2022 onward, scored them with a RoBERTa sentiment model, and grouped them with non-negative matrix factorization.

Findings

Per-capita normalization changed the central result.

Raw information-sector employment looks flat. After dividing by working-age population, it fell 7.2%. Specialty trades grew 13.5% on the same basis. The gap exceeds 20 percentage points and is easy to miss in the raw series.

The yield curve inverted in 26 of the months tracked. Every U.S. recession since the 1970s was preceded by an inversion. Inflation hit 37% cumulative over the dataset window. Headline unemployment sits near 4.4%, but U6 (which includes discouraged and involuntary part-time workers) runs 3.3 points higher. That gap has stayed wide since 2020.

Electric power output is up 8.5% since ChatGPT launched in November 2022.

On the NLP side, "Software Engineering Careers" is the dominant HN topic at 585 stories. The most negative topic by sentiment is "Executive Firings & Restructuring." Layoff story volume and the U6-U3 unemployment gap move together across the 2022-2026 window, but this analysis does not establish a causal relationship.

Claim Verification

The main engineering problem was numerical reliability. In early iterations, about 15% of claims failed verification when the model computed values itself. I changed the division of labor so the model never performs the calculation.

Step 1: Python computes the claims. For each of the 21 analytical slices, a script queries the database and builds two to four checkable statements. Examples include "USINFO changed -3.2% between 2025-04 and 2026-03" and "The yield curve was inverted in 26 months." The model receives those results but does not calculate them.

Step 2: The LLM writes prose only. The computed claims and their context go to llama3.1:8b through a local Ollama instance. The model writes readable paragraphs around the supplied numbers but does not calculate them.

Step 3: A separate process verifies the result. Another script queries the database for every claim and compares the expected and actual values. It allows a 5% relative or 0.5 absolute tolerance for values and checks the sign for trends. Those tolerances catch fabricated numbers without treating ordinary rounding as a failure.

The dashboard shows a badge on each insight block: green if all claims pass, orange if some fail (with "X of Y confirmed"), red if none pass. A "Show sources" panel inside each block shows every claim, the expected value, the actual value, and whether they matched.

21 insights ship in the seed database. All 21 pass verification. The demo works without Ollama installed.

Ask the Data

The 21 batch insights answer the questions I chose. Ask the Data lets visitors ask their own.

The interface uses a LangGraph ReAct agent with two tools: a read-only SQL query tool and a retrieval tool backed by the same vector store used for batch insights. The agent selects one or both tools and drafts an answer. A post-processing step then runs the verification pipeline described above. The status badges and "Show sources" panel work the same way for stored and live answers.

The SQL tool is sandboxed. A regex filter rejects mutations (INSERT, UPDATE, DELETE, DROP), the connection opens in read-only mode, and results are capped at 100 rows. The agent gets up to 2 SQL round-trips per question before it has to synthesize.

On Streamlit Cloud, the agent runs against Anthropic's API (Claude Haiku). Locally it defaults to Ollama. The cloud deployment has an access key gate: visitors enter a key validated with hmac.compare_digest, and the session unlocks for 2 minutes before requiring re-entry. Keys live in Streamlit secrets, never in the repo.

Ask the Data appears first because it connects the database, retrieval index, language model, and verification process in one interaction. The stored insights remain available throughout the other dashboard sections.

COVID Adjustment

COVID broke every rolling-window calculation in the dataset. Unemployment went from 3.5% to 14.8% in a single month. A 12-month YoY window touching April 2020 produces swings of +300% and -58% that dominate the charts for two full years.

For each series, I fit an ARIMA model on pre-COVID data and used the forecast as a counterfactual for March 2020 through January 2022, with a 3-month taper blending back to actual values. The raw data stays in the value column. The adjusted version goes in value_covid_adjusted. Every query uses the adjusted column except the COVID recovery chart, which shows the real shock intentionally.

The adjustment did not change the conclusions. It kept the COVID shock from dominating rolling calculations long after the event.

Per-Capita Normalization

Raw employment numbers grow partly because the U.S. working-age population grows about 0.5% per year. Comparing specialty trades employment of 4,256k in 2016 to 5,244k in 2026 overstates the real expansion because some of that growth is just more people.

USINFO and CES2023800001 are divided by CNP16OV (civilian noninstitutional population 16+) to get employees per 1,000 working-age persons, then indexed to 100 at the start date.

Before normalization, the information sector shows index 101. After normalization, it shows 93. That is the difference between "the sector barely moved" and "it shrank 7.2% relative to population." The central finding depends on this methodological choice.

Recession Model

I trained a logistic regression and random forest on 11 FRED-derived features, including yield spread, unemployment change, GDP growth, CPI momentum, and employment ratios. Three Hacker News features capture rolling sentiment, story volume, and layoff frequency. The model writes a monthly probability between 0 and 1 to the recession_predictions table.

The HN features have near-zero importance in the shipped model. The pre-2022 training period has no HN data, so those months get filled with training-period medians. That constant fill dilutes whatever signal exists in the 24 post-2022 months. I kept the features to make the limitation visible. Dropping them would improve the presentation without answering whether they become useful after a longer post-2022 history accumulates.

The Recession Risk tab shows a probability timeline, a feature snapshot with directional signals, and a What If scenario explorer where visitors can adjust sliders and see how the risk score responds.

NLP Topic Modeling

I ran sklearn's NMF (Non-negative Matrix Factorization) over 1,547 HN story titles and excerpts to extract 8 topics. I tested values from 6 to 10 and chose 8 because it produced the most distinct clusters without fragmenting related themes.

Non-AI proper nouns such as Musk, Twitter, Tesla, Meta, and Facebook initially produced personality-driven topics instead of labor themes. I added them to the stop-word list. OpenAI and Altman stayed because they are directly tied to the question being studied.

The NLP Analysis section has four charts:

Topic distribution over time (stacked area, shows how the conversation shifted)
Sentiment by topic (box plot, which themes carry the most negative tone)
Layoff story volume vs the U6-U3 gap (dual-axis, tests whether HN activity tracks macro slack)
Topic sentiment vs USINFO per-capita employment (dual-axis, tests whether sentiment tracks actual jobs)

Monthly bigram frequencies are pre-computed and shown as a quarterly heatmap in an expander.

RAG Citations

Each AI insight pulls context from a vector store before generation. FRED series metadata and curated U.S. federal publications (BEA, EIA, CEA reports, all public domain) are chunked at sentence boundaries, embedded with sentence-transformers, and stored in ChromaDB. The top-k chunks for each analytical slice get injected into the prompt as reference context.

The prompt asks the model to cite these sources with [ref:N] tags, but llama3.1:8b ignores that instruction consistently. The "Show sources" panel therefore attaches the retrieved chunks in code, regardless of whether the prose cites them. Readers can still inspect the context, but the model-driven citation feature did not work as designed.

Architecture

FRED API + HN Algolia API
        |
  data_pull.py + hackernews_pull.py
        |
  sentiment_score.py
        |
  db_setup.py  ->  covid_adjustment.py  ->  topic_model.py
        |
  export_csv.py  ->  embed_references.py  ->  recession_model.py
        |
  ai_insights.py  ->  verify_insights.py
        |
  dashboard/app.py  <->  agent/ (LangGraph ReAct: SQL tool + RAG tool + verification)

SQLite with a star schema. Main tables:

Table	Role
`series_metadata`	Display names, categories, units for each FRED series
`observations`	Raw values and ARIMA-adjusted values side by side
`ai_insights`	Narratives, pre-computed claims, verification results, RAG citations
`recession_predictions`	Monthly probability scores, feature snapshots, model metadata
`hn_stories`	1,547 HN stories with sentiment scores and topic assignments
`hn_topics`	8 NMF topics with labels and top terms
`hn_ngram_monthly`	520 monthly bigram frequency rows
`reference_docs`	FRED metadata, scholarly docs, and HN social refs for RAG

Two modes: seed (default, everything pre-computed, works on clone with no API calls) and full (live pull, requires a free FRED API key).

Analysis Queries

Eight SQL queries using CTEs, window functions, joins, and per-capita normalization:

Query	Question
Q1	Yield curve inversions vs unemployment (T10Y2Y monthly avg + UNRATE with 12-month lag)
Q2	Info vs trades divergence, per-capita normalized, indexed to 100
Q3	GDP annualized growth with NBER recession shading
Q4	Rolling 12-month per-capita employment growth by sector
Q5	COVID recovery comparison (raw values, the one exception to adjusted data)
Q6	U6 vs U3 unemployment gap
Q7	Electric power output vs info employment
Q8	CPI inflation month-over-month and year-over-year

Quick Start

git clone https://github.com/ShameekConyers/sql_python_dashboard.git
cd sql_python_dashboard
python3 -m venv .venv
.venv/bin/pip install -r requirements-dev.txt
.venv/bin/streamlit run dashboard/app.py

No API key needed. The seed database ships with all 10 FRED series, 1,547 HN stories, 8 NMF topics, recession predictions, and 21 verified AI insights.

Tools: Python, SQL/SQLite, pandas, scikit-learn, pmdarima, Plotly, Streamlit, LangGraph, langchain, sentence-transformers, ChromaDB, Ollama, FRED API, Algolia HN API