Commit Chronicles-Your Obsession Leaves a Trail. Mine Gives It a Plot.

What I Built

Commit Chronicles reads one public GitHub repo and gives it back to you as a story. Snowflake fetches the repository, decides which story exists, gathers the evidence, asks Cortex to narrate exactly that thread, validates the result, and returns structured JSON. Cloud Run just turns it into a 1200×630 PNG-the size a README embed and a social preview both want.

This is one of my repos and every dot, timestamp, and quoted commit on it is real. The color isn't just decoration-Cortex picks the accent hex as a reading of the arc, so a repo that died and one that came back and shipped don't look the same.

The scope is deliberately one repository, not a whole profile. A year-in-review across a profile turns to mush. A repo has a clean arc: commits start, cluster, pause, restart, or stop.

Two rules hold it together:

• Cortex interprets the shape. It never invents the facts. Every timestamp, count, gap, and quoted message on the card is real.
• It reads the arc; it does not reach past it. Motivation isn't in the data, so the model is forbidden from claiming any.

A repo with no real story says so. Sparse histories get an honest grey card-"no story here"-and Cortex never runs. Not every repo is an obsession, and a tool that admits that is the one you trust when it says otherwise.

Why I built it

🪤 DEV said passion, but I don't call it passion. I call it obsession. Passion is the word you use in a conference slide. Obsession is the word for what really happened: the repo you couldn't put down, the one you abandoned in April and came back to at 3:32 in the morning, or that one week where every single commit was a revert. And it's all right there.

We scroll past those commits a hundred times a week and read none of them. They're bookkeeping. I wanted my latest obsession to tell the story hiding behind those commits-the ones you take for granted in every project you've ever shipped. A contribution graph tells you that work happened. It never tells you what happened.

So the goal was one thing: AI can do it. Prove a model can find something true in a commit history without being allowed to invent the story. Point a model at a year of commit messages and everyone already knows what you get back-slop. A horoscope. A LinkedIn post about your coding journey. I bet a weekend that it doesn't have to be, and that if it did, none of the rest of this was worth building. Everything in the sections below-the six detectors, the caps, the thirteen checks, the rule that it never gets to tell you why-is the price of that sentence being true.

Obsession, as a WHERE clause

🪧 DEV's prompt calls passion "the love that fuels late-night side projects." I have a detector named NOCTURNE. It fires when at least half a repo's commits land between 22:00 and 04:59. It has five siblings:

Six shapes an obsession takes, and each one is a SQL view.

Demo

Run it on your favourite personal project, then paste the card in the comments. I want to see what the detector says about you. Type any public owner/repo and submit once. Close the tab. The job runs on a Cloud Tasks worker request, not your connection. Come back to /{owner}/{repo}. The card is there. The card is a public bucket object, so you can link it straight into a README, or into a comment on this post.

🪙 If you hit the daily cap, that's my wallet, not a bug. Live generations are capped and the queue runs two at a time. Find me on Discord or email me at anchildress1@gmail.com and I'll raise the ceiling-I would much rather pay for a card you actually wanted than leave you staring at a limit.

Code

anchildress1 / commit-chronicles

Developer analytics powered by Snowflake Cortex. Visualize Git commit history, coding habits, commit patterns, productivity trends, AI-assisted commits, and repository insights beyond GitHub graphs.

Commit Chronicles - A contribution graph tells you that work happened. It never tells you what happened. Paste a public GitHub repo. Snowflake fetches its own commit history, finds the one story hiding in it with plain SQL, and narrates that single thread with Cortex. You get a card you can drop into a README.

• Live: commitchronicles.anchildress1.dev
• Prize target: Best Use of Snowflake

Judging scope: v1.0.0 is the challenge submission. It was cut for the DEV Weekend Challenge deadline and is the only thing that should be judged. The repo was created on 10 Jul 2026 and everything in the entry was built inside the challenge window-the badges above are the receipt. Any commit or release after v1.0.0 is post-deadline work and is not part of the entry.

Two real repos, two storylines the detector found unaided-a binge and a nocturne. Every dot, timestamp, and… View on GitHub

Judged state: v1.0.0 - tagged for this submission. main will keep moving; that tag won't. Every line quoted below is pinned to it:

• snowflake/ - the whole app. Five SQL files, deployed with the snow CLI.
• detector.sql - 15 views, six storylines, not one model call.
• ai_functions.sql - CHRONICLE_CARD, the one Cortex call.
• read_repo.sql - the single entry point Cloud Run is allowed to call, and every guard that runs before a card is written.
• Architecture diagrams - the request path and the detector, rendered in the README.

⚖️ This project is licensed under PolyForm Shield 1.0.0.

Here's where the story gets chosen-a single window function, and no LLM has been called yet, nor will be until this has picked exactly one:

CREATE OR REPLACE VIEW REPO_STORYLINE AS
SELECT
    f.REPO_OWNER,
    f.REPO_NAME,
    COALESCE(s.STORYLINE, 'none') AS STORYLINE,
    COALESCE(s.SCORE, 0) AS SCORE,
    s.PIVOT_AT,
    -- every fact the card will ever print, computed here, in SQL
    OBJECT_CONSTRUCT(
        'commitCount', f.COMMIT_COUNT,
        'nightCommits', f.NIGHT_COMMITS,
        'activeDays', f.ACTIVE_DAYS,
        'daysSinceLast', f.DAYS_SINCE_LAST,
        'largestGap', OBJECT_CONSTRUCT('days', g.GAP_DAYS, ...)
    ) AS FACTS
FROM REPO_FACTS f
LEFT JOIN REPO_LARGEST_GAP g USING (REPO_OWNER, REPO_NAME)
LEFT JOIN STORYLINE_SCORES s USING (REPO_OWNER, REPO_NAME)
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY f.REPO_OWNER, f.REPO_NAME
    ORDER BY s.SCORE DESC NULLS LAST, s.DRAMA_RANK -- ties break toward drama
) = 1;

Every storyline gates on MIN_COMMITS = 15, so bot noise can't win. Scoring is deterministic: the same repo always yields the same template. And the tiebreak in CARD_EVIDENCE, which is the difference between a card and a card that changes its mind:

-- Rebases and batch pushes share an AUTHORED_AT. Without SHA as the final
-- tiebreak, the commits Cortex sees could differ between two reads of the
-- same repo - and the card would quietly rewrite itself.
ORDER BY
    ABS(DATEDIFF(hour, w.PIVOT_AT, c.AUTHORED_AT)),
    c.AUTHORED_AT,
    c.SHA

How I Built It

Snowflake is the prize tech, and it's also the whole engine-ingest, detection, narration, and validation all run inside the warehouse. Everything below is how.

My entire API surface is one line:

CALL READ_REPO('anchildress1', 'save-the-sun');

There is no application logic deciding the story. Snowflake decides the story. My backend has never read one of your commit messages and would not know what to do with one.

1. Why every layer narrows

🪙 I already used my free Snowflake trial. Cortex is coming out of my pocket. So the architecture has exactly one obsession of its own-give the model as little as possible and still get a story back-and every slice, cap, floor, and filter here exists because I am personally paying for the tokens on the other side of it.

Which is not a compromise. Cheap scales. Expensive doesn't. I have been doing this long enough to know that a per-request cost you can't bound is a system with its tombstone already etched, and the fastest way to build something that holds up under real traffic is to build it as though every call is coming out of your own account-because eventually, for someone, it is.

So the bound is the feature. The model's input is 20 to 140 lines. Always. A repo with twenty thousand commits and one with two hundred have the same maximum narration cost-the expensive call doesn't grow with your history, so the version running on my card and the version running for ten thousand people are the same architecture. I don't have to rewrite it later.

2. Snowflake goes and gets its own data

🛰️ An EXTERNAL ACCESS INTEGRATION lets a Python stored procedure call api.github.com from inside the warehouse, which means there is no ingestion service, no ETL job, and no Cloud Function in the middle holding a copy of your commits.

Ingest caps at 500 commits, which is the first cut and the first thing standing between a monorepo and my bill. A longer history sets windowed, and the card prints it-"last 500 commits · quiet since Feb 25"-because a cap you hide is a lie, and reporting a slice as a repo's whole life is false.

This is the one place a model runs before the story is chosen, and it barely runs at all. Regex and GitHub's own account type settle roughly 99% of the bot question. Only the genuine ambiguities-a human-looking account committing like a machine, a subject line that mentions an AI tool without being written by one-get handed to AI_CLASSIFY and AI_FILTER, deduped by (author, email) so it's one call per distinct identity rather than one per commit. No candidate, no call.

3. The detector is free

💸 Scoring six storylines across a repo's whole history costs nothing but warehouse seconds-not one model call in fifteen views-and that layer is what makes the expensive layer cheap. By the time a model is involved, SQL has already dropped the merges and the bots, scored every candidate narrative, picked exactly one winner, and selected the commit lines belonging to that thread only.

Snowflake could hand the model your entire repo without breaking a sweat. It doesn't have to, so it doesn't. The warehouse decides what's worth reading before a single token gets spent, which is the difference between a bill that scales with a repo and one that doesn't.

4. The model is a SQL function

🔬 CHRONICLE_CARD is a hand-written UDF wrapping AI_COMPLETE(claude-sonnet-4-5), and here's the part that matters: the model is invoked from inside a SELECT. There is no HTTP call, no SDK, no retry wrapper, no queue of prompts, and no service account carrying an API key. It's a function, in a query, sitting next to the rows it reads. The evidence never leaves the warehouse to get narrated, and the narration lands back in a table on the way out.

The prompt is built in SQL too-string concatenation, inside the UDF, from the arguments READ_REPO hands it. So the only non-deterministic step in this entire pipeline is the sentence, and there is deterministic SQL standing on both sides of it: SQL computes the facts, picks the storyline, selects the evidence, and writes the prompt; the model writes prose; SQL then validates what came back before any of it reaches a card.

I prototyped it in Cortex AI Function Studio and then wrote it out as a plain UDF, so the function lives in the repo and deploys with the snow CLI-a function clicked into existence in a UI doesn't live in your git history.

It runs at temperature: 0.4, on purpose. I started at zero, because zero is the responsible number, and zero was boring-the prose came back correct and dead. So I turned it up until the writing had a pulse and made the warehouse carry the safety instead of the sampler. The story selection is deterministic; the sentence isn't.

It's fed CARD_EVIDENCE: the winning thread's commit lines, budgeted at 25% of the repo's commit lines, floored at 20, capped at 140. That cap is the invoice-the only number in this project I tuned with a calculator instead of taste. Squash-merge bodies get exploded into individual lines first, so work buried inside a merge is still readable.

Cortex is never taught to produce a number. The commit count, the status verb, the anchor timestamps, the gap panel, the caption-all of it is composed by the renderer, from facts SQL already computed. That rule came from a real failure. Handed the facts as one JSON blob, the model wrote "fifty-six commits after midnight" about a repo with fifty-six commits in total and forty-seven at night. It read an adjacent integer and captioned it wrong. Now every fact arrives as its own labelled argument, and the model isn't allowed near a digit.

So the schema constrains exactly nine keys, and that is the entire surface area of the writing. Here's the real row out of CARDS for the card up top:

{
    "kicker": "a graveyard shift",
    "headline_upright": "Fifty-six percent of"
}