Repository navigation
feat: detector + card contract that matches the render - #4
Conversation
- untrack the .vite/ dependency-cache directory left by local tooling Commit-generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
Snowflake's RLIKE implicitly anchors a pattern at BOTH ends, so the prefix pattern in COMMITS_CLEAN only ever matched a subject exactly equal to the prefix itself. A real "Bump lodash from 4.17.20 to 4.17.21" did not match, so NOT RLIKE returned true and dependency and merge noise flowed straight into the detector's input. The filter had never excluded a single commit; PARENT_COUNT and IS_BOT were silently covering for it. - append the load-bearing .* so the alternation matches as a prefix - note the both-ends anchoring inline, since it reads like a no-op otherwise Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
The detector scores six candidate storylines deterministically and keeps exactly one. No LLM and no warehouse cost: this is the part that decides what the card is about, so Cortex is only ever pointed at the thread that won. - score relapse, nocturne, binge, collapse, fight, and resurrection on 0-100 - apply commit floors so bot noise and six-commit repos cannot win - break score ties by drama, never alphabetically, so binge stops winning on b - scale binge flattest of the six; a dull 10-day streak was beating a real 56% night habit - expose CARD_EVIDENCE: the winner's ~20 commits, all Cortex is ever shown - surface repos with no story as 'none' rather than manufacturing drama Verified against the two repos the build plan calls out, unaided: rai-lint reads resurrection (107 days dark, back at 03:32, then shipped) and my-hermantic-agent reads nocturne (84% night, last commit 03:53). Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
AGENTS.md was both authoritative and wrong: it still mandated Firestore and Firebase Hosting, which the Snowflake-native rescaffold deleted. Every future agent session inherited that. The spec contradicted itself in the same breath, its architecture naming the GCS bucket while its user flow subscribed to a Firestore document. Records the decision that the card is rendered by Cloud Run, not Snowflake. Templating an SVG proves nothing about a warehouse, and doing it in-warehouse drags a STORAGE INTEGRATION, an external stage, and a service-account IAM grant onto the critical path to buy nothing. The Snowflake case rests on the ingest, the detector, and the Cortex call. - purge Firestore and Firebase Hosting from every doc that still named them - name the deployed objects as they actually exist, and mark what is left - rewrite the README, which still described the abandoned percentile product - drop references to design-directive.md and mockup.html, which do not exist Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
Built with Cortex AI Function Studio via SNOWFLAKE.CORTEX.CREATE_AI_FUNCTION, so the card is a named, callable function that lives in this file and deploys with the snow CLI. Declaring several OUTPUTS fields yields the whole card as one schema-constrained JSON object, so there is a single Cortex round trip and nothing to parse out of prose. - read the winning storyline into kicker, headline, thesis and accent - forbid restating a number under a label it does not carry: given 56 commits total and 47 at night, the model captioned the total as "fifty-six commits after midnight" - forbid inventing releases, users, production or motivation, none of which are observed - reject the storyline keyword as a kicker; "nocturne" is the input, not an answer - draw every prompt example from an arc no repo has, since an example that IS the right answer for a real repo just gets parroted back and proves nothing CREATE_AI_FUNCTION has no OR REPLACE, so the function is dropped first. Without that a redeploy silently keeps the old prompt and the file quietly lies about what is running. Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
The only part of the pipeline that costs money, so it runs on a cache miss and nowhere else. The detector has already cut the history down to the winning thread, so Cortex reads roughly twenty commits rather than twenty thousand. - call CHRONICLE_CARD once and persist the result to CARDS - hand the model a labelled fact sheet instead of a raw JSON blob, so adjacent integers cannot be swapped and captioned wrong - derive the status label in SQL: it falls out of the commit dates, so the model is never given the chance to get it wrong - add CARD_PLOT, the full commit scatter, which goes straight to the renderer and is never shown to a model - record the Cortex query id for cost audit, as the spec requires - treat a NULL narration as a hard failure: a structured Cortex response that hits its token ceiling returns NULL rather than raising, which looks exactly like an outage Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
The docs described the narration layer as a bare AI_COMPLETE call. It is a registered custom AI function, and the object table omitted it entirely. - name CHRONICLE_CARD and CREATE_AI_FUNCTION in the stack description - add CHRONICLE_CARD and CARDS to the Snowflake objects table Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
A 'none' storyline has a NULL PIVOT_AT, which makes ABS(DATEDIFF(...)) NULL for every row in the partition. The whole window then ties, and ROW_NUMBER picks twelve commits arbitrarily — differently on each run. The detector promises the same repo always yields the same story, and this quietly broke that promise for every storyless repo. - skip the pivot window entirely when there is no pivot; a no-story repo gets its opening and its ending, which is all it has - tiebreak the pivot ordering on AUTHORED_AT: two commits equidistant from the pivot tie as well, which is the same bug wearing a smaller hat - tiebreak the opening and ending windows on SHA Reported by Codex, which caught the NULL pivot. The equidistant-tie case was not. Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
The old pattern only matched a Co-authored-by / Generated-by trailer, which is one project's commit convention rather than a fact about the world. A commit whose body just says "generated with Claude" was counted as unassisted. - match an AI tool named anywhere in the message, subject or body - leave out cursor, opus, haiku, sonnet and grok: each is an ordinary English or programming word, and matching them bare relabels "reset cursor position" as AI-assisted. A claude-sonnet-4-5 attribution still matches on claude Existing rows keep the old flag until a repo is re-ingested. Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
The fact sheet was English prose assembled with || inside a SQL view — prompt formatting smeared into the data layer. CREATE_AI_FUNCTION has a user prompt template for exactly this, so the labelling moves there and SQL goes back to passing values. - pass each fact as its own typed input; the template names it in the line it belongs to - drop FACT_SHEET from READ_REPO - skip Cortex entirely for a 'none' storyline and write a template card instead. Paying for an inference call to be told a repo is unremarkable is the bill the cost guards exist to prevent, and the spec asks for an honest template, not manufactured drama - create CARDS IF NOT EXISTS rather than OR REPLACE: every row was paid for in tokens, and redeploying a procedure should not silently re-bill the gallery - drop both CHRONICLE_CARD arities; Snowflake overloads on arity, so dropping only the current signature would strand the old function alive and callable Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
The prompt was an essay: flowing paragraphs, rhetorical justification, an argument for why each rule mattered. That is persuasion aimed at a human. A model needs directives. - restructure as labelled constraint blocks: GROUNDING, TASK, VOICE, FIELDS - state each rule once, imperatively, and cut the reasoning behind it - keep the style reference as a compact shape-only example Same constraints, roughly half the tokens, and every rule is now addressable. Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
…edure The function was registered through SNOWFLAKE.CORTEX.CREATE_AI_FUNCTION, which the docs state plainly is "not intended to be called directly" and whose "signatures and behavior may change without notice". It was reverse-engineered from DESC PROCEDURE because the reference page 404s. That is an unsupported API holding up the whole narration layer. The supported alternatives are a Snowsight wizard and the Cortex Code CLI, and a function clicked into existence in a UI does not live in this repo — which the spec forbids. CREATE_AI_FUNCTION emits an ordinary UDF wrapping AI_COMPLETE with a system/user message array and a response_format. There is no privileged machinery in it, so write that. - hand-write CHRONICLE_CARD against the public AI_COMPLETE API - gain CREATE OR REPLACE, which the Studio procedure lacks; its absence already cost a cycle where a redeploy silently kept the old prompt and the file lied about what ran - drop every function the Studio registered in earlier builds, at both arities Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
The files had become a debugging diary: war stories, rationale essays, and running commentary on what had gone wrong and when. That belongs in commit messages, which is where it is read, not in code, where it rots. - keep only one-line notes for constraints the code cannot express: Snowflake regex anchoring, NULL-on-truncation, UTC hour skew, the arity overload - drop the REPO column migration; the column no longer exists and the path is dead - drop the NDJSON file format and stage, left over from the PUT/COPY INTO loader that the ingest procedure replaced - drop the trailing verification SELECTs, which scanned on every deploy 195 lines net removed. All five files redeploy clean; the 3 generated cards are untouched. Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
Cortex constrained decoding does not support minLength, maxLength or format on strings. An unsupported keyword is a schema rejection, and inside a UDF that surfaces as NULL rather than an error — so the whole card came back empty and looked like a model outage. The keywords had never actually run: AI Function Studio built its schema from the OUTPUTS argument and silently discarded them. Hand-writing the UDF was the first time they were sent for real. - keep the length limits in the prompt, where they are honoured - READ_REPO now returns ready end to end on the hand-written UDF Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
The payload could not render the design. Four elements had no source.
- largest gap now computed for every repo, not just relapse: the card draws a void panel
over the longest silence whichever storyline wins, and a nocturne card was rendering
without one
- PLOT carries the minute. The card annotates exact times ("3:53 AM") and an integer hour
cannot produce :53
- capture the GitHub login at ingest. The card credits @handle, and a handle is not
derivable from a git display name
- carry the primary author, and the first and last commit subjects
Add REFRESH_CARD_DATA: the renderer inputs are derived, so they rebuild from the views
without re-narrating. Doing this through READ_REPO would re-bill every card in the gallery.
AUTHOR_LOGIN is NULL on rows ingested before this; a repo needs re-ingesting to fill it.
Generated-by: Claude Opus 4.8 <noreply@anthropic.com>
Signed-off-by: Ashley Childress <anchildress1@gmail.com>
There was a problem hiding this comment.
Pull request overview
This PR implements the Snowflake-side “story detector + card payload” pipeline for Commit Chronicles, aligning the generated card contract with what the renderer is intended to draw (facts-derived layout + Cortex-written voice surface).
Changes:
- Add a plain-SQL detector that scores six storylines and exposes a single winner per repo plus a tightly scoped
CARD_EVIDENCEpayload. - Add
CHRONICLE_CARD(a schema-constrainedAI_COMPLETEwrapper) andREAD_REPOto generate/store card payloads with the new three-slot headline contract. - Update ingestion/schema/docs to match the repo-first flow and bucket-cached rendering architecture.
Reviewed changes
Copilot reviewed 9 out of 10 changed files in this pull request and generated 2 comments.
Show a summary per file
| File | Description |
|---|---|
| snowflake/schema.sql | Updates COMMITS schema and the cleaned detector input view. |
| snowflake/read_repo.sql | Adds CARDS, CARD_PLOT, and READ_REPO procedure for card payload generation. |
| snowflake/ingest_pipeline.sql | Extends ingest to persist AUTHOR_LOGIN and updates AI-assisted detection heuristics. |
| snowflake/detector.sql | Introduces storyline scoring views and the CARD_EVIDENCE view used for Cortex grounding. |
| snowflake/ai_functions.sql | Adds CHRONICLE_CARD UDF wrapping a single constrained AI_COMPLETE call. |
| README.md | Updates top-level product description to repo-first, story-centric positioning. |
| docs/initial-design-spec.md | Updates architecture/contract docs to reflect Snowflake detector + Cloud Run rendering + bucket cache. |
| docs/build-plan.md | Updates build plan to match the new pipeline and responsibility split. |
| AGENTS.md | Updates canonical repo instructions to match the Snowflake-native architecture and constraints. |
| .gitignore | Ignores Vite and agent scratch artifacts. |
💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.
There was a problem hiding this comment.
💡 Codex Review
Here are some automated review suggestions for this pull request.
Reviewed commit: 8bbf06ece5
ℹ️ About Codex in GitHub
Your team has set up Codex to review pull requests in this repo. Reviews are triggered when you
- Open a pull request for review
- Mark a draft as ready
- Comment "@codex review".
If Codex has suggestions, it will comment; otherwise it will react with 👍.
Codex can also answer questions or update the PR. Try commenting "@codex address that feedback".
- widen CHRONICLE_CARD to 19 args, returning nine keys: three-slot headline plus anchor labels - drop label_void and caption from Cortex; the renderer composes both from FACTS - rebuild CARDS and CARD_PLOT for the new shape; plot rows carry exact timestamp and minute - carry PIVOT_AT through CARD_EVIDENCE so the pivot anchor pins to a real dot - document the renderer-vs-Cortex ownership split and the status verb map Generated-by: Claude Opus 4.7 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
- .cortex/ holds agent plan scratch, not repo state Commit-generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
- aligns with the sonar workflow, which was already on v7 Commit-generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
3733a2a to
b75419e
Compare
- the nine-key contract has no thesis field; the three-slot headline carries the interpretation - describe the headline as upright / italic accent fragment / upright, not "the second clause" - add the anchor labels, which only appear on the anchors a storyline actually uses Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
- reviewing, deploying, or verifying another agent's work is not authorship - when the authoring model is not recorded, ask rather than guess Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
- add lefthook.yml: commitlint on commit-msg, actionlint on staged workflows - add commitlint config extending conventional plus commitlint-plugin-rai - rai-footer-exists and rai-signed-off-by make the AGENTS.md commit rules real, not aspirational - add a package.json to hang the devDependencies on; ignore node_modules Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
- constrain accent to the muted-neon family (amber, coral, lime, mint, sky) with five named anchor hexes; ban greys, browns, fluorescents, and deep saturated primaries; grey stays reserved for the none-story template - rewrite the CHRONICLE_CARD system message as terse imperative blocks: task, output-field semantics with inline limits, grounding, voice, headline, anchor labels, accent colour, and two examples inlined as JSON literals matching the response_format schema - switch the user turn to key=value pairs so field names bind rules to inputs without translation - align the sample JSON in the docs to the palette (amber #e8a04a) and add a swatch table to the design spec Generated-by: Claude Opus 4.7 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
- capture the CHRONICLE_CARD output into a single VARIANT so the empty
guard, the reject checks, and the INSERT all read the same row without
re-billing the model
- reject cards that misbehave in ways SQL can prove: accent not a valid
#rrggbb, any string over its field cap, digits leaking into a poetic
label, kicker echoing the storyline keyword. rejected cards return
{status:failed, errorCode:cortex_rejected, reasons:[...]} instead of
storing a broken row
- apply the two conditional-empty repairs at INSERT time: label_pivot=""
when the pivot equals the last commit, label_last="" when status is
not active. table invariants now, not prompt hopes
- capture LAST_QUERY_ID() into cortex_query_id right after the Cortex
SELECT, so the audit id records the model call and the INSERT can
carry it in a single write
Generated-by: Claude Opus 4.7 <noreply@anthropic.com>
Signed-off-by: Ashley Childress <anchildress1@gmail.com>
- TO_TIMESTAMP_TZ on a DATE produced midnight in the session timezone, matching no dot - resolve the pivot to the last actual AUTHORED_AT on the closing day of the streak - binge was the only storyline with a synthetic pivot; all three binge repos were orphaned Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
- the payload shape was inconsistent across branches; CARDS.SCORE was already populated - clients can now read score without branching on storyline Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
- plsql is the only analyzer Sonar has for .sql, so it applies Oracle grammar to files that never claimed to follow it - ignore SizeConstraintMissingCheck (Snowflake STRING is unsized) and S1192 (the duplicated literals are OBJECT_CONSTRUCT keys; SQL has no constants) - widen the existing non-printable-character ignore to every dollar-quoted body - exclude warehouse DDL from coverage; no test runner emits coverage for it - drop the superfluous DEFAULT NULL initialisers, which was a real finding Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
- GitHub omits author.login when it cannot link a commit to an account, and six of eight test repos had a null login on every commit - parse the login back out of "id+login@users.noreply.github.com" and the legacy form - FACTS.primaryAuthorLogin was null for those repos, so the card lost its author handle Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
- Cloud Run calls one procedure, so a repo nobody has read before returned no_commits - ingest failures (private, missing, empty, oversized) pass straight back to the caller - no_commits now means the opposite of what it did: commits exist, but all are bots or merges Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
- MODE() breaks ties nondeterministically; the same history could credit a different person between reads, which the detector's determinism promise forbids - rank authors by commit count, then login, then name - Hello-World reported two different authors from the same two commits Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
- CREATE OR REPLACE TABLE CARDS fired on each deploy of the procedure and truncated it - move the table to schema.sql behind CREATE TABLE IF NOT EXISTS - cards cost a Cortex call each; a deploy must not silently spend them again Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
- the only CROSS JOIN target is DETECTOR_CONFIG, a FROM-less SELECT of tuning constants - one row by construction, so it broadcasts constants and cannot multiply rows Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
- move bot and AI-tool detection out of the Python handler and into a SQL step. the ingest stages raw rows into a temp table, then classifies in SQL so the AI functions can layer on top of the regex signals without a second write path - IS_BOT: fast-path (GitHub type=Bot, name/email regex) resolves the obvious cases. ambiguous authors — no fast-path hit AND GitHub could not attribute the commit to a User — go to AI_CLASSIFY, deduped by (author, email) so one call covers a distinct identity, not each of their commits - IS_AI_ASSISTED: strict tool-name regex still wins outright. when only a candidate token appears (common-English tool names — cursor, opus, haiku, sonnet, grok — or a generic phrase like "generated by"), AI_FILTER decides. no candidate token, no LLM call - capture GH_AUTHOR_TYPE and GH_COMMITTER_TYPE into the staged row so SQL has everything the Python _classify helper used to see; drop the helper and its two regex constants verified: procedure DDL and the embedded INSERT compile against a real account under CHRONICLES.RAW. Generated-by: Claude Opus 4.7 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
- INGEST_REPO_COMMITS failed outright: "Unsupported statement type 'temporary TABLE'" - an owner's-rights procedure may not create a temporary table - declare INGEST_STAGE as a transient table in schema.sql, scoped and cleared per repo - classification logic is untouched Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
- REGEXP_LIKE anchors at both ends and '.' does not match a newline without the s flag - the pattern runs over SUBJECT || CHR(10) || BODY, so '.*copilot.*' never matched - every commit came back IS_AI_ASSISTED = false, including "Copilot/fix lock file pnpm" - carbon-trace: 0 flagged before, 41 after, matching the regex the Python used to run Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
- MODE(UTC_HOUR) broke ties arbitrarily, and that hour reaches Cortex as nocturne evidence - rank the typical night hour by count, then by the earlier hour - add SHA as the final tiebreak on the pivot window; rebases share an AUTHORED_AT Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
- the hook called actionlint directly; a contributor without it could not commit - skip loudly with the brew install line instead of failing - the npm package is a third-party wasm repackage, so it is not a dependency here Generated-by: Claude Opus 4.8 <noreply@anthropic.com> Signed-off-by: Ashley Childress <anchildress1@gmail.com>
|



What
The Snowflake side of Commit Chronicles: plain-SQL storyline detection, and a card contract shaped to what the renderer actually draws.
detector.sql) — six storyline views (relapse, nocturne, binge, collapse, fight, resurrection), scored, one winner per repo.CARD_EVIDENCEis the only thing Cortex ever sees.CHRONICLE_CARD(ai_functions.sql) — a hand-written SQL UDF overAI_COMPLETE. 19 discrete fact args in, nine keys out. Not Cortex AI Function Studio, for the reasons inAGENTS.md.READ_REPO(read_repo.sql) —CARDS,CARD_PLOT, and the one Cortex call in the pipeline.The contract
Cortex writes only the words a reader hears in the author's voice: the genre, a three-slot headline (
upright/ italicaccent/trail), the poetic tail on each anchor, and the accent colour. Everything else on the card — commit counts, timestamps, gap dates, status verbs, the caption — the renderer composes fromFACTS.label_voidandcaptionwere pure facts and are gone from the model's surface entirely.Not every storyline uses every anchor.
label_pivotis empty when the pivot is the last commit;label_lastis empty unless the repo is still active. Ownership split is documented indocs/initial-design-spec.md.Verification
Deployed to
CHRONICLES.RAWand generated a real card foranchildress1/carbon-trace(83 commits ingested, 41 after bot/merge filtering):nocturne, score 56,dormant#4a5f8c— midnight blue, for work done after darkplot[0].tpins exactly tofacts.firstCommitAtEvery number on the card is real: 41 commits, 40 AI-assisted, 52-day span. Both empty labels are empty for the right reason. No invented drama.
🤖 Generated with Claude Code