---
title: "What HackerNews is talking about: trending topics with an LLM"
description: "Rank trending HackerNews topics in Postgres with an incremental CocoIndex v1 pipeline: scrape threads and comments, extract topics with an LLM."
last_updated: 2025-12-02
doc_version: "2025-12-02"
canonical: https://cocoindex.io/blogs/hackernews-trending-topics/
---
# What HackerNews is talking about: trending topics with an LLM

> Rank trending HackerNews topics in Postgres with an incremental CocoIndex v1 pipeline: scrape threads and comments, extract topics with an LLM.

Published: 2025-12-02 · Canonical: https://cocoindex.io/blogs/hackernews-trending-topics/

The tech community's attention is hiding in plain sight, scattered across story titles and thousands of comments. This post builds a pipeline that reads recent HackerNews threads and their comments, asks an LLM to pull the topics out of every message, and ranks what's trending in Postgres, rewritten for CocoIndex v1.

Two pieces do the work: the LLM extraction that turns free-form text into normalized topics, and a SQL score that turns those topics into a leaderboard. This post focuses on both. The source here is a custom one (two async functions over the [Algolia HN API](https://hn.algolia.com/api)); the [custom-source walkthrough](https://cocoindex.io/blogs/custom-source-hackernews) covers that side in detail, so we won't repeat it here.

The full example is in the [CocoIndex v1 repo](https://github.com/cocoindex-io/cocoindex/tree/main/examples/hn_trending_topics).

## How it works

Each recent story becomes its own [processing component](https://cocoindex.io/docs/programming_guide/processing_component/). The component fetches the story and its comments, extracts topics from each message with an LLM, and declares rows into two Postgres tables:

- `hn_messages`: one row per message (the story and each comment), for full-text search
- `hn_topics`: one row per topic mention, keyed on `(topic, message_id)`

Because the same topic mentioned in many messages becomes many `hn_topics` rows, ranking becomes a simple SQL aggregate. Re-running catches up on new stories, and because per-message extraction is memoized, only content CocoIndex hasn't seen hits the LLM.

## The topic extraction is the product

The interesting part is not calling an LLM; it's getting topics back in a shape you can rank. Raw model output would give you "postgres" in one comment and "PostgreSQL" in another, or a whole phrase like "books for autistic kids" as a single blob. Those never aggregate.

The fix lives entirely in the prompt, expressed as the description of a Pydantic field. The model normalizes three ways: it splits combined phrases into parts, keeps proper nouns canonical, and emits aliases for the same thing:

```python title=models.py
class TopicsResponse(BaseModel):
    """Response containing a list of extracted topics."""

    topics: list[str] = Field(
        description="""List of extracted topics.

Each topic can be a product name, technology, model, people, company name, business domain, etc.
Capitalize for proper nouns and acronyms only.
Use the form that is clear alone.

For topics that are a phrase combining multiple things, normalize into multiple topics. Examples:
- "books for autistic kids" -> "book", "autistic", "autistic kids"

When there're multiple common ways to refer to the same thing, use multiple topics. Examples:
- "John Kennedy", "JFK"
"""
    )
```

That normalization is what makes the trending ranking meaningful later. Get it wrong and "PostgreSQL", "postgres", and "psql" split their votes three ways.

Extraction itself is one CocoIndex function: text in, a list of topics out, through [litellm](https://cocoindex.io/docs/ops/litellm/) so any provider works. `memo=True` caches the result, so an unchanged message is never sent to the model twice:

```python title=main.py
@coco.fn(memo=True)
async def extract_topics(text: str | None) -> list[str]:
    if not text or not text.strip():
        return []

    response = await acompletion(
        model=LLM_MODEL,
        messages=[
            {"role": "user", "content": f"Extract topics from the following text:\n\n{text[:4000]}"}
        ],
        response_format=TopicsResponse,
    )
    content = response.choices[0].message.content
    return TopicsResponse.model_validate_json(content).topics
```

## Declare a row per message and per topic

The two target tables are plain dataclasses. A thread and each of its comments both become an `HnMessage` (distinguished by `content_type`), and every extracted topic becomes an `HnTopic` keyed on `(topic, message_id)`:

```python title=main.py
@dataclass
class HnMessage:
    id: str
    thread_id: str
    content_type: str  # "thread" or "comment"
    author: str | None
    text: str | None
    url: str | None
    created_at: datetime | None

@dataclass
class HnTopic:
    topic: str
    message_id: str
    thread_id: str
    content_type: str
    created_at: datetime | None
```

`process_thread` fetches one story, extracts topics for the story and every comment, and declares the rows. You declare what should exist; CocoIndex works out the inserts, updates, and deletes:

```python title=main.py
@coco.fn
async def process_thread(thread_id: str, targets: TableTargets) -> None:
    async with aiohttp.ClientSession() as session:
        thread = await fetch_thread(session, thread_id)
    thread_topics = await extract_topics(thread.text)

    targets.messages.declare_row(row=HnMessage(
        id=thread.id, thread_id=thread.id, content_type="thread",
        author=thread.author, text=thread.text, url=thread.url, created_at=thread.created_at,
    ))
    for topic in thread_topics:
        targets.topics.declare_row(row=HnTopic(
            topic=topic, message_id=thread.id, thread_id=thread.id,
            content_type="thread", created_at=thread.created_at,
        ))

    for comment in thread.comments:
        comment_topics = await extract_topics(comment.text)
        targets.messages.declare_row(row=HnMessage(..., content_type="comment", ...))
        for topic in comment_topics:
            targets.topics.declare_row(row=HnTopic(..., content_type="comment", ...))
```

`app_main` mounts the two Postgres tables and fans out one `process_thread` per recent story with `coco.mount_each`. The two async fetch functions that make up the custom source are covered in the [custom-source post](https://cocoindex.io/blogs/custom-source-hackernews).

## Ranking what's trending

Trending is a SQL query over `hn_topics`. Each row contributes a weight to its topic's score: a thread-level mention counts for more than a comment-level one, because a topic in the story title is the subject, while a topic in a comment is supporting discussion. Group by topic, order by score:

```sql
SELECT
    topic,
    SUM(CASE WHEN content_type = 'thread' THEN 5 ELSE 1 END) AS score,
    MAX(created_at) AS latest_mention,
    COUNT(DISTINCT thread_id) AS thread_count
FROM coco_examples.hn_topics
GROUP BY topic
ORDER BY score DESC, latest_mention DESC
LIMIT 20
```

Weighting thread mentions 5 to 1 over comment mentions is what makes the ranking read as "what HackerNews is about" rather than "what people mentioned in passing." The same score, filtered to one topic and grouped by `thread_id`, ranks which threads are driving a given topic.

## Run it

Install the example and build the index:

```sh
pip install -e .
cocoindex update main
```

This fetches the most recent stories, runs topic extraction on every message, and writes `coco_examples.hn_messages` and `coco_examples.hn_topics`. This source is a one-shot catch-up per run; re-run `cocoindex update main` anytime to pick up new stories, and only new messages hit the LLM.

Then explore from the command line, top trending topics or a single topic:

```sh
python main.py            # top 20 trending topics + interactive search
python main.py "rust"     # search content for one topic
```

## What you can build on top

Once topics are structured and ranked, the pipeline is a foundation:

- **Trending dashboards.** Serve the leaderboard and per-topic threads to a live page that shows what's hot and which discussions are driving it.
- **Sentiment-aware trends.** Add a second extraction step for sentiment to track not just what's trending, but how people feel about it.
- **Contributor tracking.** Every message stores its `author`, so you can find who drives conversation around a given topic.
- **A knowledge graph.** Feed the topics into a [graph](https://cocoindex.io/blogs/knowledge-graph-for-docs) linking companies, products, and people mentioned across HackerNews.

Each of these is a new transform or target on the same declared source, not a rewrite. The extraction and the ranking, the two pieces this post focused on, stay exactly as they are.

## Support us ❤️

If this was useful, a [star on GitHub](https://github.com/cocoindex-io/cocoindex) helps more developers find CocoIndex and keeps the project growing.

## Sitemap

- [Blog index](https://cocoindex.io/blogs/)
- [Site index (llms.txt)](https://cocoindex.io/llms.txt)
- [Full blog corpus](https://cocoindex.io/llms-full.txt)
- [Markdown sitemap](https://cocoindex.io/sitemap.md)
- [XML sitemap](https://cocoindex.io/sitemap.xml)
- [RSS feed](https://cocoindex.io/blogs/rss.xml)