AI-powered Catholic tech content aggregator with knowledge graph

[JohnRDOrazio]

Summary

Proposal for an AI-powered system that automatically discovers, classifies, and indexes content about technology and the Catholic Church from Catholic media sources.

What it does

• Daily aggregation — A Python worker scours RSS feeds, Catholic news sites, and Vatican documents for tech-related content
• AI classification — Each article is scored for relevance, summarized, tagged, and has named entities extracted (using Claude or OpenAI)
• Full-text search — PostgreSQL tsvector indexes enable fast search across all stored articles
• Knowledge graph — Apache AGE (PostgreSQL extension) builds a property graph linking articles, entities, tags, and sources — queryable with openCypher
• Web interface — A new /research section on the CDCF website with faceted search and an interactive D3.js knowledge graph visualization

Architecture

• PostgreSQL + Apache AGE — single database for relational data, FTS, and graph queries
• Python worker — modular design with swappable AI providers (Claude/OpenAI) and pluggable fetchers (RSS, web scraper, Vatican.va)
• Next.js /research route — queries PostgreSQL directly (independent of WordPress CMS)
• Docker Compose — opt-in via --profile aggregator

Implementation plan

See docs/content-aggregator.md for the full plan including:

• Database schema (tables + graph model)
• Python worker architecture
• Next.js frontend design
• 5-phase implementation timeline
• Complete file list

🤖 Generated with Claude Code

[JohnRDOrazio]

Some feedback from Mark Evgenev, alternatives to postgresql:
https://surrealdb.com/
https://www.helix-db.com/
https://www.lancedb.com/

[JohnRDOrazio]

Some further input from Claude Code, on evaluating the usefulness of such tools for this project (Claude's responses of course need to be evaluated themselves):

Having read the full spec, the honest answer is: none of those three add meaningful value here — just add pgvector to the PostgreSQL instance you're already running.

Here's the reasoning:

---

What the project actually needs

The spec has no vector embeddings at all currently, which is actually a gap worth filling. The search story is pure tsvector FTS + Apache AGE graph traversal. That works, but it means:

• "AI Ethics" articles won't surface when someone searches "machine learning morality" unless the keywords overlap
• Articles from multilingual sources (EN/ES/PT/IT) won't cross-pollinate in search
• Entity disambiguation is purely name-matching — "Bishop Barron" vs "Robert Barron" vs "Bp. Barron" could create duplicate entity nodes
• "Related articles" would have to be tag-overlap or graph proximity, not semantic similarity

These are all solvable with a vector column on articles and an HNSW index. At the scale of this project — a niche Catholic tech aggregator doing daily cron runs, probably reaching low tens of thousands of articles over its lifetime — pgvector handles it comfortably without breaking a sweat.

---

Why not the other three

SurrealDB — The multi-model pitch is compelling in theory (graph + doc + vector + FTS in one query), but you're already doing that with PostgreSQL + AGE. The graph is openCypher via AGE, FTS is tsvector, and you'd just be adding pgvector. Migrating to SurrealDB would mean rewriting the schema, losing AGE's maturity, and betting on a younger ecosystem — for no architectural gain.

HelixDB — Native graph + vector is its core value prop, and that does match this project's shape. But it's early-stage (YC-backed, thin docs, small community), and the project's graph needs are already well-served by AGE. The risk-reward is bad for a mission-focused volunteer project.

LanceDB — It's genuinely excellent, but it's solving a different problem: ML training pipelines, multimodal data, petabyte-scale lakehouse operations. This project ingests maybe a few hundred articles per day. LanceDB would be like using a freight train to commute to work.

---

What to actually add

A single migration gets you vector search:

-- Add to the articles table
ALTER TABLE articles ADD COLUMN embedding vector(1536);  -- or 768 for nomic-embed-text
CREATE INDEX idx_articles_embedding ON articles USING hnsw (embedding vector_cosine_ops);

Then in the Python worker, after the AI classification step, generate an embedding for each article (title + summary is usually enough — full content is rarely necessary):

# In pipeline.py, after classify_article()
embedding = await ai_provider.embed(f"{article.title}\n\n{article.summary}")
await db.execute(
    "UPDATE articles SET embedding = $1 WHERE id = $2",
    embedding, article.id
)

This unlocks:

• Semantic search alongside the existing tsvector FTS (hybrid search with RRF reranking)
• Cross-lingual search — an ES article about "IA y la Iglesia" will surface for an EN query about "AI and the Church"
• Related articles sidebar — ORDER BY embedding <=> $current_article_embedding LIMIT 5
• Entity deduplication — cluster near-duplicate entity names before creating graph nodes
• Source quality ranking — articles semantically far from the corpus centroid are likely off-topic before the AI scorer even runs

The embedding model can be nomic-embed-text (free, local, 768-dim, strong multilingual) via Ollama, which you're already referencing in the SurrealDB example in the codebase. Zero new infrastructure, zero new database, keeps everything in one place where your AGE graph queries and tsvector indexes already live.

[JohnRDOrazio]

Vector Embeddings — spec additions

Added pgvector with HNSW indexes and an embedding pipeline to the content aggregator spec (docs/content-aggregator.md). Three revisions were made after comparing with an earlier design sketch:

1. Default to nomic-embed-text via Ollama — Replaced Voyage AI with Ollama as the default provider (768-dim, local, free). OpenAI kept as cloud fallback. Added Ollama container to Docker Compose with auto-pull of the model on first start. Updated the architecture diagram to show Ollama as a separate service.

2. Switched from weighted sum to RRF — Replaced the fts_rank * 0.4 + similarity * 0.6 hybrid search with Reciprocal Rank Fusion (1/(60+rank)). Removed the AGG_SEARCH_FTS_WEIGHT and AGG_SEARCH_SEMANTIC_WEIGHT env vars since RRF doesn't need weight tuning.

3. Added corpus centroid pre-filter — New pipeline steps generate an embedding early (from title + content excerpt) and compare against the corpus centroid before the expensive AI classification. Articles too far from the centroid are skipped. The final embedding is regenerated after classification with enriched text (title + summary + tags). Added AGG_CENTROID_MAX_DISTANCE env var (default 0.65).

What this unlocks

• Semantic search — "machine learning morality" finds "AI Ethics" articles even without keyword overlap
• Cross-language discovery — multilingual embeddings map EN/ES/PT/IT/FR/DE into the same vector space
• Entity disambiguation — auto-merge "Bishop Barron" / "Robert Barron" / "Bp. Barron" via embedding similarity (threshold 0.92, manual review band 0.80–0.92)
• Related articles sidebar — nearest-neighbor query, no tag-overlap or graph proximity needed
• Corpus centroid pre-filter — cheap screening of off-topic articles before expensive AI classification

[JohnRDOrazio]

Hardware Requirements Analysis

Six resource-consuming components run concurrently during a pipeline run. Here's what each needs and what the totals look like.

Per-component breakdown

Component	CPU	RAM	GPU/VRAM	Disk	Notes
PostgreSQL + AGE + pgvector	1–2 cores	1–4 GB	—	10–50 GB SSD	HNSW index for 768-dim vectors: ~3 KB/vector. At 50K articles ≈ 150 MB index. shared_buffers should be ~25% of available RAM. AGE graph traversals can spike memory on complex queries.
Ollama (nomic-embed-text)	1–2 cores	~1 GB	Optional (any GPU w/ ≥2 GB VRAM)	~600 MB (model weights)	274M param model. Runs fine on CPU (~50ms/embedding). GPU speeds it up but is not required at this throughput.
faster-whisper (local transcription)	2–4 cores	1–10 GB (model-dependent)	Optional (see below)	200 MB–3 GB (model weights)	This is the heaviest component. Model memory on CPU w/ int8: base ≈ 1 GB, small ≈ 2 GB, medium ≈ 5 GB, large-v3 ≈ 10 GB. Transcription is CPU-intensive: a 1-hour podcast takes ~15 min on base/4 cores, ~45 min on medium.
Crawl4AI (Playwright/Chromium)	1–2 cores	512 MB–1.5 GB	—	—	Each Chromium tab ≈ 200–500 MB. shm_size: 512m already set in compose. Concurrent tabs multiply this.
Python worker (pipeline)	1 core	500 MB–1 GB	—	2 GB tmpfs (audio temp)	Orchestration, feedparser, PDF extraction (PyMuPDF), yt-dlp. Lightweight except during PDF processing.
Next.js (standalone)	1 core	256–512 MB	—	—	Serves /research search UI. The pg connection pool and query-time embedding call (to Ollama) are the only notable costs.

Whisper model vs. hardware tradeoff

This is the main sizing decision. Everything else is modest.

Whisper model	Params	CPU-only (int8) RAM	GPU VRAM	Realtime factor (CPU, 4 cores)	Realtime factor (GPU)
base (default)	74M	~1 GB	~1 GB	~0.25x (4× faster than realtime)	~0.05x
small	244M	~2 GB	~2 GB	~0.5x (2× faster)	~0.1x
medium	769M	~5 GB	~5 GB	~1.5x (slower than realtime)	~0.15x
large-v3	1.5B	~10 GB	~10 GB	~3x (3× slower)	~0.3x

With base (the default), a daily batch of ~15 YouTube videos (avg 20 min) + ~5 podcast episodes (avg 45 min) = ~300 min of audio ≈ 75 min wall-clock on CPU. That's fine for a nightly cron job. Moving to medium or large-v3 for better accuracy pushes this to 7+ hours on CPU — at which point a GPU becomes important.

Recommended configurations

Minimum viable (CPU-only, Whisper base)

• CPU: 4 cores / 8 threads (e.g., Intel i3-12100, AMD Ryzen 3 3100, or equivalent VPS)
• RAM: 8 GB (tight — all components must share; only one Chromium tab at a time)
• GPU: None
• Disk: 50 GB SSD
• Cost estimate: ~$20–40/mo on Hetzner/OVH VPS, or a modest home server

This runs everything but with no headroom. PostgreSQL gets ~2 GB shared_buffers, Whisper base takes ~1 GB, Ollama ~1 GB, Chromium ~500 MB, leaving ~3 GB for OS + Python + spikes. Workable for a small source list.

Recommended (CPU-only, Whisper small)

• CPU: 8 cores / 16 threads (e.g., Ryzen 5 5600, Intel i5-12400, or 8-vCPU VPS)
• RAM: 16 GB
• GPU: None
• Disk: 100 GB SSD
• Cost estimate: ~$40–60/mo on Hetzner dedicated, or a mid-range home server

Comfortable for the full source list (17 RSS + 7 YouTube + 6 podcasts + 12 academic). PostgreSQL gets 4 GB, Whisper small takes 2 GB, parallel Chromium tabs are fine, and there's room for growth. Daily pipeline completes well within the cron window.

With GPU (Whisper medium or large-v3)

• CPU: 8+ cores
• RAM: 32 GB
• GPU: NVIDIA with ≥6 GB VRAM (e.g., RTX 3060 12GB, RTX 4060 8GB, or A2000)
• Disk: 100 GB SSD
• Cost estimate: ~$80–120/mo on GPU VPS (Vast.ai, Lambda), or a desktop with a dedicated GPU

Only needed if transcription accuracy is critical (non-English audio, heavy accents, technical jargon). The GPU would be used exclusively by faster-whisper — Ollama's nomic-embed-text doesn't benefit meaningfully at this throughput. large-v3 with an RTX 3060 processes audio at ~3× realtime, so the daily batch finishes in ~100 min.

Storage growth projection

Data	Per article	At 10K articles	At 50K articles
Article text + HTML	~20 KB	~200 MB	~1 GB
Transcripts (AV content)	~40 KB avg	~100 MB (subset)	~500 MB
Embeddings (768-dim float32)	3 KB	30 MB	150 MB
HNSW index overhead	~2× embedding	60 MB	300 MB
AGE graph (nodes + edges)	~1 KB	10 MB	50 MB
FTS GIN index	~5 KB	50 MB	250 MB
Total database		~450 MB	~2.3 GB

Storage is not a concern at this scale. Even at 50K articles the entire database fits comfortably in RAM on the recommended config.

Summary

For a niche Catholic tech aggregator doing daily cron runs: 8 cores, 16 GB RAM, no GPU, 100 GB SSD is the sweet spot. That's a $40–60/mo dedicated server or a modest home machine. GPU is only worth it if you want to run Whisper medium/large-v3 for better multilingual transcription accuracy.

[JohnRDOrazio]

Architecture update: two-server topology with FastAPI

Redesigned the aggregator to run on a dedicated server (Hetzner/OVH) separate from the CDCF website's Plesk server, connected via a REST API.

Why not a single server?

The Plesk server hosting the CDCF website isn't suited for running Ollama, faster-whisper, Crawl4AI/Playwright, and PostgreSQL+AGE+pgvector alongside WordPress and Next.js. A dedicated server keeps the heavy processing isolated and lets us size it independently (8 cores, 16 GB RAM, 100 GB SSD — ~$40–60/mo).

How it works

Plesk Server                    Dedicated Server
┌─────────────────┐             ┌──────────────────────────┐
│ Next.js (CDCF)  │── HTTPS ──►│ FastAPI (research API)   │
│ /research pages │  + API key  │ PostgreSQL + AGE + pgvec │
│                 │             │ Ollama (nomic-embed-text) │
│ WordPress (CMS) │             │ Python worker (daily cron)│
└─────────────────┘             └──────────────────────────┘

• FastAPI is the only internet-facing service. PostgreSQL, Ollama, and the worker are internal to the Docker network.
• API key auth via X-API-Key header. AGG_API_KEYS supports comma-separated keys for rotation.
• Cache-Control headers on API responses (5 min for search, 1 hour for articles, 15 min for tags/graph) — the Next.js frontend uses next: { revalidate } for ISR.
• Docker resource limits prevent the worker from starving the API during pipeline runs (worker: 4 CPU / 8 GB max; API: 2 CPU / 2 GB reserved).

Why NOT separate API and worker servers?

At this scale (low tens of thousands of articles, daily cron), splitting further adds complexity for no benefit:

• PostgreSQL's MVCC means readers (API) never block writers (worker)
• The daily pipeline runs 1–2 hours with most time on network I/O; the other 22+ hours the worker is idle
• Docker resource limits are sufficient to guarantee API responsiveness during aggregation

What changed in the spec

• Replaced Next.js app/api/research/ routes with a FastAPI service on the dedicated server
• Added typed API client (lib/research/client.ts) on the CDCF frontend — same pattern as the existing WPGraphQL integration
• Removed pg connection pool from Next.js — the frontend just does fetch() calls
• Self-contained docker-compose.yml for the dedicated server (DB + API + worker + Ollama)
• Caddy reverse proxy for TLS termination
• Split env vars: dedicated server (AGG_*) and Plesk (AGGREGATOR_API_URL, AGGREGATOR_API_KEY)

See commit 58b8e4a for the full diff.