What HackerNews is talking about: trending topics with an LLM

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); the custom-source walkthrough covers that side in detail, so we won’t repeat it here.

The full example is in the CocoIndex v1 repo.

How it works

Each recent story becomes its own 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:

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 so any provider works. memo=True caches the result, so an unchanged message is never sent to the model twice:

@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):

@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:

@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.

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:

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:

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:

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 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 helps more developers find CocoIndex and keeps the project growing.

CocoIndex

Fresh context for long-horizon agents.

Learn the concept → View on GitHub

About the author.

Linghua Jin Cofounder & CEO of CocoIndex · @linghuaj

CEO and cofounder of CocoIndex. Writes about incremental data infrastructure, agents, and the engineering decisions behind the engine.