DEV Community: Soumyadeep Dey

Your Dog Can't Tell You Where It Hurts. MATCH_RECOGNIZE in Snowflake Can.

Soumyadeep Dey — Sun, 16 Aug 2026 18:47:31 +0000

This is a submission for Weekend Challenge: Dog Days Edition

Target categories: Best use of Snowflake + Best use of Solana.

A diagnosis is a sequence, so make the detector a regular expression

A 2022 study screened 500 dogs with no osteoarthritis diagnosis on file and found 188 of them, 38%, had it. The number that matters more is the next one: fewer than half, 47.2%, had ever been presented for stiffness or lameness (Wright et al.). Most arthritic dogs never showed their owner the sign the owner was watching for, because a visibly weak animal is a target and the instinct outlived the danger (Merck).

So the only witness that does not editorialise is movement - and every consumer tracker destroys it on the way in, compressing a day of movement into a scalar and thresholding it. But a vet does not diagnose from a scalar. A vet diagnoses from an ordered sequence of events: the dog walks, stops, walks, stops again. Sniffs, circles, sniffs again without ever settling. Rises slowly, three separate times, before ten in the morning. Average those and the finding is gone before you look at it. Not weakened, gone, because averaging is precisely the operation that discards ordering, and ordering was the entire signal.

Both dogs walked 56 of their 60 seconds. Every total a step counter can compute is identical, and one of them is limping. The difference only exists at one row per second, which is the only resolution MATCH_RECOGNIZE cares about.

So the question is not "what threshold?" It is: what if the detector were a regular expression over rows instead of a number? That is a real, standardised SQL feature almost nobody reaches for - SQL:2016 row pattern recognition, and Snowflake's MATCH_RECOGNIZE does exactly it: partition, order, then match a regex whose alphabet is rows.

A differential diagnosis is a sequence. A sequence is a pattern. A pattern is a query.

100 Hz dual-IMU collar --> Snowflake --> Snowflake --> Snowflake --> Solana (devnet)
  45 dogs, 27 breeds,      Dynamic       MATCH_          Cortex       a signed claim
  video-annotated,         Tables DAG    RECOGNIZE       vet notes    a shelter can
  10.6M sensor rows        no cron       6 syndromes     + triage     read without us

TELLTAIL turns a dog collar into a syndrome detector. Not "active minutes", but six named clinical presentations, each a row pattern, each explainable down to the individual second that satisfied each symbol:

10.6M sensor rows → 198,681 classified epochs → 118 findings across 32 dogs → 5,955 per-second symbol rows → 117 signed attestations. Every arrow is SQL running inside one Snowflake account.

Every dog on two independent deviations and its finding count. Colour is the triage band a Cortex task wrote, so position and colour agreeing is a result rather than a construction.

One thing up front, because it changes how you read every number below

Held-out accuracy is 77.46%, macro F1 0.650, dog-disjoint across 10 unseen animals. That number is low because the protocol is honest, and the protocol is the interesting part. At 100 Hz, two samples 40 ms apart are near-identical; a random row split puts adjacent samples from the same dog on both sides, the model memorises the individual, and the accuracy is a fiction. The published work on this corpus reports single-subject classifiers falling from ~91% to ~70-74% when generalising to unseen dogs. 77.46% sits above that band. The 91% was available and it would have been a lie, so the dashboard prints 77.46% in 44px type.

3.65% of epochs carry a heuristic state. SHAKE and SCRATCH are not first-class labels here, so they are derived and flagged state_source = 'HEURISTIC' - in the data, in a banner on every tab, and in a provenance table.

Two of the six syndromes never fired. S1 and S5 have zero matches, and are still in the catalogue, still swept, still displayed at zero. A detector that ships only the patterns which happened to hit is not a detector, it is a highlight reel.

Full accounting in What's real and what's simplified.

The walkthrough

Two minutes: the replayer streaming 100 Hz collar rows in, MATCH_RECOGNIZE catching a syndrome, and the claim landing on devnet.

SoumyaEXE / TellTail

TELLTAIL

A dog cannot tell you where it hurts. Ten million rows of collar data can.

Every consumer dog tracker computes activity minutes and step counts, because that is what you get from AVG() and SUM(). But a vet does not diagnose from an average. A vet diagnoses from a sequence: this, then that, then this again, in that order, that many times.

TELLTAIL streams 10.6 million rows of real dog collar telemetry into Snowflake classifies every second into a behavioural state, and then hunts for clinical syndromes expressed as regular expressions over rows using MATCH_RECOGNIZE.

Not "scratching is high today." Instead:

head shake, scratch cluster, head shake, scratch cluster, emerging from rest

which is otitis and nothing else.

The thesis. A threshold cannot detect a syndrome. Aggregation tells you a number changed. Only row pattern recognition tells you that events happened in a diagnostic order. Snowflake…

View on GitHub

Verify this in 60 seconds — no keys, no clone

1 · The detector is SQL. warehouse/07_syndromes.sql — every syndrome is a PATTERN (...) and a DEFINE, and the dashboard prints the exact clause that produced each finding.

2 · The feature is one function. warehouse/04_staging_dt.sql — CORR(vm_neck, vm_back), and the whole detection argument rests on it.

3 · The findings are on a public chain. One devnet wallet signs every attestation, and each transaction carries the claim in its Memo instruction:

Publishing wallet — ERcokh7aHySS2nLmfT7UjDzTtp8ZT1szwV5x2oKcAFFx
117 confirmed transactions · 31 dogs · 0.000585 SOL in total fees

Open any of these and expand Memo to read the bytes Snowflake produced. The right-hand column is the row pattern that fired, verbatim from REF.SYNDROME_CATALOGUE — the regex on the left is what put the claim on the right on chain:

Finding	Devnet transaction	The pattern that fired
S2 · intermittent lameness	`56Ptv79m…SbH2CgLkz`	`stride{3,} halt+ stride2{1,3} halt2+ stride3{1,3} halt3+`
S4 · reluctance to rise	`4Lz1C7pi…4jZDT44Q`	`settled{10,} lever rise settled2{10,}`
S6 · GI discomfort	`2xyQwPKu…DGTbmBLu8`	`probe{5,} turn{2,} probe2{5,}`

The receipt I care about most

Everything above is a static artefact. This one is a stopwatch. While writing this section I had the collar feed running and the bridge polling, and the warehouse found something on its own:

12:48:23.067   ORACLE.SP_ENQUEUE_ATTESTATIONS stages claim #1314
12:48:29.925   bridge claims it, signs it, submits to devnet
12:48:56.017   confirmed in slot 484450346
               ────────────────────────────────────────────
               32.95 s   warehouse row  ->  public ledger

Three timestamps from one clock, one signature, one slot — transaction 3DMXbMBT…NhWpCg28. I trace that claim back through every layer of the build — down to the fourteen seconds that produced it, and the arithmetic behind its confidence — further down this post.

The warehouse stages the claim; a separate Node process holds the key. This table is the audit trail, not the source of truth. The chain is.

Two sensors, one question

The corpus is Kumpulainen, Vehkaoja et al. (2021): 45 dogs, 27 breeds, video-annotated at one-second resolution, and critically two IMUs, one on the collar and one on a back harness.

Most canine accelerometry uses a single collar device, and it works well. But one sensor has a blind spot no amount of model capacity fixes: a collar alone cannot distinguish a dog walking from a dog shaking its head. Both are vigorous neck motion. The harness resolves it physically rather than statistically - in locomotion the two sensors rise and fall together, and in a head shake the neck moves while the body does not, so their correlation collapses toward zero.

-- warehouse/04_staging_dt.sql — one SQL function, and only possible
-- because this dataset has two sensors.
CORR(vm_neck, vm_back)                       AS neck_back_corr,
STDDEV(vm_neck) / NULLIF(STDDEV(vm_back), 0) AS neck_dominance,

The collar rides the cervical vertebrae, which the neck can swing on its own; the harness rides the thoracic vertebrae, which only move when the whole animal does.

The whole detector rests on this picture, and CORR(vm_neck, vm_back) is the difference between the two placements.

That 100 Hz → 1 Hz collapse is a hard gate, not an optimisation. 10.6M samples become ~106K one-second epochs, because MATCH_RECOGNIZE pointed at the raw table scans ~10.6M rows per partition and never returns. At the epoch layer the same six patterns come back in seconds.

The same minutes, spent differently. Two dogs can log identical walking totals and have completely different bout-length distributions - and that difference is where lameness becomes visible before any syndrome fires.

The feature then has to justify itself, so the dashboard plots that correlation grouped by true annotated label: locomotion should sit high, neck-dominant behaviours should collapse toward zero, and if it came out flat the project would be over. It is the one chart I built expecting to be disappointed by.

The state ladder

A classifier trained on locomotion and posture labels physically cannot emit CIRCLE or PAUSE - those are defined by geometry and context, not by the dataset's label vocabulary. So every epoch is resolved through a strict precedence ladder, and which rung fired is a column, not a comment:

0  QUALITY   n_samples below the gate            -> UNKNOWN        LOW_QUALITY
1  GEOMETRY  yaw signature the model cannot see  -> CIRCLE, PACE   GEOMETRY
2  NECK      shake/scratch, if unlabelled        -> SHAKE, SCRATCH HEURISTIC
3  CONTEXT   stillness bracketed by locomotion   -> PAUSE          CONTEXT
4  MODEL     whatever the classifier said                          MODEL

That is where the 3.65% comes from. Rung 2 is conditional: V_NECK_LABELS_PRESENT asks the data at refresh time whether SHAKE, SCRATCH and PACE are real annotations here, and derives only what is not observed. The COALESCE around its BOOLOR_AGG is load-bearing - over an empty label map BOOLOR_AGG returns NULL, NOT NULL is NULL, every heuristic branch silently fails to match, and S1 becomes unable to fire at all, on a warehouse that looks perfectly healthy.

The money layer: a diagnosis as a regular expression

Here is S2, intermittent lameness, exactly as it exists in REF.SYNDROME_CATALOGUE:

PATTERN ( stride{3,} halt+ stride2{1,3} halt2+ stride3{1,3} halt3+ )
DEFINE
    stride  AS state = 'WALK',   halt  AS state = 'PAUSE',
    stride2 AS state = 'WALK',   halt2 AS state = 'PAUSE',
    stride3 AS state = 'WALK',   halt3 AS state = 'PAUSE'

Read it aloud: walk at least three seconds, stop, walk a little, stop again, walk a little, stop again. That is a limp, written as a regex - and the catalogue row says why it cannot be a threshold: step count and daily distance are unchanged, but stride interruption frequency is rising. That is "he seems fine, he just stops a lot now", which is what the other 52.8% of Wright's undiagnosed arthritic dogs look like. A sequence, which is exactly what a daily average deletes.

S6, GI discomfort, is PATTERN ( probe{5,} turn{2,} probe2{5,} ) over SNIFF/CIRCLE/SNIFF: sniffing is the most common outdoor behaviour and circling precedes every normal elimination, so only the repetition without resolution is abnormal - and repetition is an ordering property. S3, exercise intolerance, fires when total activity minutes are identical and the same minutes have been redistributed into shorter bursts with longer recoveries.

Six syndromes, 118 matches across 32 dogs. And the corpus rejected my first draft of S3: I defined recovery as state = 'REST', it matched nothing, and that was not a bug - TROT -> REST occurs zero times in 106K epochs, because a dog recovering from exertion stands or sits long before it lies down. It matches on activity_class now.

The part that makes it explainable

ONE ROW PER MATCH gives you a finding. ALL ROWS PER MATCH with CLASSIFIER() gives you which pattern variable consumed each individual second:

MATCH_RECOGNIZE (
    PARTITION BY dog_id, test_num  ORDER BY epoch_ts
    MEASURES MATCH_NUMBER() AS match_id,
             CLASSIFIER()   AS symbol,        -- which variable ate this row
             COUNT(*)       AS seq_in_match
    ALL ROWS PER MATCH
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ( ... )  DEFINE ... )

5,955 of those rows are materialised, and the dashboard renders them as a ribbon: one coloured block per real second, coloured by the symbol it played. So a generated sentence like "repeated gait interruption" is printed directly beside the seconds that produced it - prose checkable against the rows, which is not a feature most AI-assisted health tools offer.

Each block is one real second, coloured by the symbol the row-pattern engine assigned it.

Linked selection compiled into the Vega spec - no rerun, no callback, not one further query.

Confidence is a UDF, not a vibe

Every finding carries a confidence from one definition used everywhere: MARTS.F_CONFIDENCE, weighting three terms 0.45 / 0.35 / 0.20. evidence is how far a match exceeds its pattern's minimum length, because a match sitting on the quantifier floor is the weakest kind. quality is mean epoch completeness. purity is the fraction of matched epochs whose state came from the classifier rather than a heuristic rung - so a finding assembled out of derived states is scored down automatically, and the honesty is arithmetic rather than editorial. Hold on to those three terms.

The quantifier is not a magic number

stride{3,}. Why 3? A hand-tuned quantifier is exactly the magic number I spent the opening criticising. So the pattern text is not code - it is rows in REF.SYNDROME_VARIANTS, three strictness settings per syndrome, and SP_SYNDROME_SWEEP() cursors that table, string-builds a MATCH_RECOGNIZE per row and runs it with EXECUTE IMMEDIATE. Eighteen row-pattern queries, one loop, zero hand-written variants, and the sensitivity curve is the output.

The sweep also keeps its own scoreboard, because it had to. Every variant succeeds alone; executing all eighteen inside one procedure intermittently trips a Snowflake internal error (370001), and an unhandled one abandons the sweep while the procedure still reports success. A truncated curve looks exactly like a real curve. So each variant is wrapped in its own BEGIN ... EXCEPTION and writes ran_ok plus the verbatim error to MARTS.SWEEP_STATUS - which is how I know the 229 matches behind the published curve came from 11 completed variants and not eighteen.

The DAG: 12 Dynamic Tables, zero cron, and four things a Dynamic Table cannot do

There is no scheduler in the transform layer. Each table declares its TARGET_LAG and Snowflake resolves the refresh order: raw telemetry into epoch features, into the ML state prediction and the ethogram, then bouts, transitions, baselines and deviations, and finally the syndrome layer that feeds Cortex and the publish queue.

The account itself, not a diagram of it. REF seeds, RAW lands, STAGING derives, MARTS scores, ML fits, ORACLE queues - and nothing flows right to left.

Each bar is an object's observed refresh lag as a multiple of the TARGET_LAG it declared, so past the dotted line is a table falling behind its own promise.

Why the syndrome layer is a task and not a Dynamic Table. This is the most useful thing I learned this weekend: a Dynamic Table containing MATCH_RECOGNIZE will not refresh incrementally. REFRESH_MODE = INCREMENTAL fails compilation with an unsupported construct, and AUTO silently resolves to FULL - a full rebuild on a one-minute lag is a bill you did not agree to, so that layer is an explicit task on a two-minute schedule. Same at the feature layer, where the sample-level window functions are not incrementally refreshable either and FULL is declared rather than inferred. (Two more, so you do not rediscover them: MATCH_RECOGNIZE cannot appear inside a recursive CTE, and must never be pointed at the raw 100 Hz table.)

So tasks exist for exactly four jobs Dynamic Tables cannot do: row pattern recognition, calling a stored procedure, anything budgeted that must be rate-limited by schedule rather than refresh lag, and snapshotting. They chain with AFTER, so it is one DAG with a single root rather than five schedules racing:

T_ROOT (2 min)
  └─ T_SYNDROMES ────── MATCH_RECOGNIZE, all six patterns
       ├─ T_MATCH_ROWS ─ ALL ROWS PER MATCH symbol tagging
       └─ T_SNAPSHOT ─── per-minute activity history
            └─ T_BOUNDARY ── fix the single train/detect split point
                 ├─ T_FORECAST ── ML.FORECAST
                 │    └─ T_ANOMALY ── ML.ANOMALY_DETECTION
                 │         └─ T_DRIVERS ── TOP_INSIGHTS
                 └─ T_NOTES ───── AI_COMPLETE
                      └─ T_TRIAGE ── AI_CLASSIFY over the cached notes
                           └─ T_BRIEF ── AI_AGG
                                └─ T_ATTEST ── enqueue for the bridge

One procedure per task, deliberately. A multi-statement AS BEGIN ... END; task body is valid Snowflake and also unparseable by any client that splits on semicolons - including my own run_sql.py, which would create the task with only its first CALL and execute the rest as orphaned statements. The task then exists and does a third of its job, silently.

ASOF JOIN for the baseline, and it matters clinically. Every dog is its own control, because a Husky doing forty minutes of galloping is a Tuesday and a twelve-year-old Bulldog doing the same is an emergency:

FROM MARTS.ACTIVITY_EPOCH cur
ASOF JOIN MARTS.ACTIVITY_BASELINE base
     MATCH_CONDITION (cur.epoch_ts >= base.window_end)
     ON cur.dog_id = base.dog_id

ASOF earns its place over LAG(n) because LAG counts ROWS, not TIME. A collar that drops out for ten minutes is not hypothetical, and with LAG that dog is quietly compared against the wrong hour and its deviation becomes fiction. For a health metric, silently wrong is the worst failure mode available: it does not look like a bug, it looks like a finding.

Each dog is its own control - the band is that dog's own trailing hour, matched on the timestamp condition itself.

The classifier, and two decisions that decide whether the number means anything

Identifiers are not features. dog_id, test_num and epoch_ts are carried through the training view for the split and for joining back, and dropped before the model sees a row. Leave dog_id in the vector and the model learns "dog 23 mostly trots", which invalidates the entire holdout protocol while making the accuracy go up.

The holdout is breed-spread, and the obvious way to build it collapses. Take FLOOR(n_breed * fraction) per breed with a floor of one so none is skipped, and on this roster - 26 breeds across 45 dogs - GREATEST(1, ...) fires for nearly every breed and the "22% holdout" becomes 26 of 45 dogs: more than half the corpus. What works is to rank dogs within their breed, interleave breeds globally, and take the first N - exact count, spread preserved. That seed table is also CREATE TABLE IF NOT EXISTS on purpose: with CREATE OR REPLACE, retuning an unrelated threshold empties the holdout as a side effect, ML.V_TRAIN quietly widens to all 45 dogs, and the next reported accuracy is a row-split fiction wearing a dog-disjoint label.

And 7.9% of the corpus needed rescuing first. The dataset carries up to three simultaneous annotations and the posture is not always in the first: Panting is primary for 836K rows and is respiratory, not a posture - the actual posture sits in Behavior_2 (48% Sitting, 48% Standing, 4% Lying). Mapping it to one posture is wrong about half the time; dropping those rows throws away the same 7.9%. A COALESCE across the two columns recovers it, and label_source records which one answered.

Cortex, and the rule that the LLM never produces a number

SQL and fitted models compute every number. The LLM only writes prose. Nothing an LLM says reaches a state, a match, a confidence, or the chain.

Three AI functions, batched into tables by tasks: AI_COMPLETE writes a SOAP-format vet handoff note per finding, AI_CLASSIFY assigns a triage band over that note, AI_AGG produces one pack brief. Every number in every prompt was computed upstream in SQL - the prompt is handed the evidence VARIANT verbatim and instructed, in the system text, to not diagnose. It is a handoff document for a clinician, not a verdict.

Two decisions worth stealing. AI_CLASSIFY classifies the note, not the raw evidence, so the triage badge and the document a human reads cannot drift apart. And every AI procedure catches its own exception, logs it to AI.USAGE_LOG, and returns normally, so a Cortex hiccup degrades one batch instead of taking the task DAG down. The render path never calls Cortex at all: a trial account is capped at roughly ten credits of AI Function usage per day, and a dashboard that bills on page load dies mid-demo.

Position is measured; colour is generated. Because colour is not one of the three axes, the two agreeing is a result rather than a construction.

The time-series gotcha that cost an hour: ANOMALY_DETECTION rejects a call whose training and detection windows overlap. The fix is not to guess a gap - compute the boundary T once, into a table, then strict < on the train side and >= on the detect side. It has its own task, T_BOUNDARY, upstream of forecast, anomaly and drivers so all three inherit the same number.

The honest substitution: every model-introspection accessor on this account raises Computation Error in function __SHOW_* while PREDICT works fine, so SHOW_FEATURE_IMPORTANCE is permanently empty. Rather than ship an empty panel or quietly relabel something else, the dashboard computes a one-way ANOVA F-ratio per feature in SQL, says in the caption that it answers a different question from a tree model's split gain, and prints the account error verbatim beneath it.

Publishing the claim, not the data

A dog arriving at a shelter arrives with no history. Vets, shelters, kennels and adopters share no database and commercially never will, and a health record living in one vendor's cloud dies the moment the dog changes hands - which is precisely when it matters most. So findings at severity >= 2 are staged into a queue and attested on Solana.

{"conf":0.996,"finding":"S4","model":"state-v3+match-v1","oracle":"TELLTAIL",
 "severity":2,"source":"Snowflake MARTS.SYNDROME_MATCHES",
 "subject":"b80a39d78c5fb38f","window":"2026-08-15T02:18:23Z/PT47S",
 "authority":"ERcokh7aHySS2nLmfT7UjDzTtp8ZT1szwV5x2oKcAFFx"}

Snowflake output on a block explorer: 117 confirmed transactions, 31 dogs, 0.000585 SOL in total fees, and not one needed a smart contract. subject is a salted hash of the dog id - no telemetry, no breed, no owner, no location, only that a finding of this code, severity and confidence occurred in this window. Publish the claim, never the data.

The security property worth checking: the keypair never touches Snowflake. The warehouse stages claims in ORACLE.PUBLISH_QUEUE and does nothing else; a small Node bridge holds the key, signs, submits, and writes the signature back. Grep the SQL codebase for key material and there is nothing to find, because Snowflake has no code path that can sign anything. The state machine itself is SQL (SP_CLAIM_BATCH, SP_MARK_CONFIRMED, SP_MARK_FAILED), so two bridges polling the same queue cannot double-publish: claiming flips rows to SENT before anything is signed. It is outbound polling rather than Snowflake calling out because External Access Integrations are unsupported on trial accounts (error 509009, confirmed the hard way).

One finding, all the way down

Every architecture diagram in every hackathon post is a row of boxes with arrows between them. Here is the alternative: one real finding, and the actual value at every layer it passed through. Claim #1314, detected while I was writing the section above.

#	Layer	The actual value
1	`RAW.COLLAR_TELEMETRY`	~1,400 samples, 6 channels, dog 23, session 2
2	`STAGING.EPOCH_FEATURES`	14 epochs, `neck_back_corr` computed per second
3	`MARTS.EPOCH_STATES`	`SNIFF SNIFF SNIFF SNIFF SNIFF CIRCLE CIRCLE SNIFF SNIFF SNIFF SNIFF SNIFF SNIFF SNIFF`
4	`MATCH_RECOGNIZE`	`PROBE{5} TURN{2} PROBE2{7}` - cast, circle, cast, unresolved
5	`MARTS.SYNDROME_MATCHES`	S6 GI discomfort · 13 s · conf 0.7321 · purity 0.7857 · quality 1.0
6	`evidence` (VARIANT)	`{"cast_epochs":[5,7],"circle_epochs":2,"unresolved":true}`
7	`AI.VET_NOTES` (`AI_COMPLETE`)	"repetitive cast-circle-cast sequence without resolution (5 cast epochs, 2 circle epochs, 7 cast epochs)"
8	`AI.TRIAGE` (`AI_CLASSIFY`)	schedule appointment, severity 2
9	`ORACLE.PUBLISH_QUEUE`	subject `33e35a39e17b15ec` - a salted hash, no dog id
10	Solana devnet	slot 484450346, `3DMXbMBT…WpCg28`

Read row 3 and row 7 next to each other. The LLM wrote "5 cast epochs, 2 circle epochs, 7 cast epochs" - and rows 3 and 4 are those exact epochs, in that exact order, with the symbol that consumed each one. The model did not count them. CLASSIFIER() counted them in SQL, the counts went into the prompt as an evidence object, and the note is constrained to narrate numbers it was handed. That is the difference between an explainable finding and a fluent one.

Now decompose the number that went on-chain

Row 5 says conf 0.7321 - the 7.32e-01 in the memo instruction of a transaction anyone can open. It is not a score a model emitted. Take F_CONFIDENCE and the match's own facts: 14 epochs against S6-tuned's min_epochs of 12, quality 1.0, purity 0.7857.

evidence  0.4 + 0.6 * ((14 - 12) / 12)  = 0.5000  ->  x 0.45 = 0.22500
quality                                   1.0000  ->  x 0.35 = 0.35000
purity                                    0.7857  ->  x 0.20 = 0.15714
                                                            ─────────
                                                    ROUND(,4)  0.7321

That is the published digit, reached by hand. And purity 0.7857 is not a coincidence - it is exactly 11/14: eleven of the fourteen matched epochs came from the classifier and three from a heuristic rung of the state ladder, so the finding was scored down for its own provenance, automatically, in SQL, before anything was signed.

Then read row 9 against rows 1-8. Everything that makes the finding useful to a vet stays in the warehouse. Everything that reaches a public ledger is a code, a severity, a confidence and a window against a salted hash. A shelter can verify the claim without the previous owner, the vendor, or me - and still cannot tell you which dog it is.

Row 7 and row 3 in one screenshot: the note on top, the seconds that produced it underneath.

The honest caveat on this particular finding - click to expand

This finding came off the replayer: ingest/replay.py streams the published corpus into the live landing table with a fresh timestamp anchor. The sensor data, feature, classifier, pattern match, Cortex call and Solana transaction all genuinely happened while I was writing this post - but these are not fourteen dog-seconds never observed before, since the same samples also exist in the bulk path under their original timestamps. So 118 findings is not a claim about 118 distinct clinical events in the world.

The dashboard runs inside the warehouse

Streamlit in Snowflake. Eleven pages, 45 figures, zero external hosting - the app, the data, the ML and the LLM all in the same account. Three renderers, each for the one thing it is unmatched at: plotly for 3D, Altair for linked selection compiled into the Vega spec, Bokeh for a shared x_range scrubber over a 100 Hz waveform.

That sandbox has no outbound network, so <img src="https://…"> is a broken icon no matter how reachable the host and breed photographs live base64 in a table. Those photographs are of the breed, never of the study animal - stated in a column and repeated under every image, because a photograph implying it is the animal being diagnosed would be the most misleading thing this project could put on screen.

Three SiS hazards that reproduce nowhere else:

Hazard	Symptom	Fix
`to_pandas()` returns numerics as object-dtype `Decimal`	plotly treats them as categories; every line chart is the same straight diagonal	convert element-wise with `float()`
SiS ships Altair 4	`selection_point` / `.add_params` are Altair 5 API - an `AttributeError` took the entire Syndromes tab down	resolve the spelling at call time
The plotly version is unknowable from here	`fillgradient`, `barcornerradius`, `griddash` each raise `ValueError` at figure construction on an older build	probe each at import, with a fallback

The Altair one is the nastiest bug in the build, because every development machine on earth has Altair 5 or 6: I wrote the modern spelling, watched it work locally, and watched Snowflake render the Syndromes tab heading followed by a red traceback where the cross-filter should have been. The money tab, dead, in the one environment I could not reproduce. So every effect is capability-probed now, and tests/test_chart_layer.py builds one of everything twice - natively, and with all probes forced False - because the degraded path only ever runs in production.

Three axes, and the separation is either there or it is not. This is the picture that decides whether CORR(vm_neck, vm_back) was a good idea, which is why it is rotatable rather than a projection I flattened for you.

Everything Snowflake is actually doing here

The test I held myself to: could this be swapped for a Postgres box and a cron job without losing the argument? If yes, it did not belong in the build.

Feature	Where	Why it is not decoration
`MATCH_RECOGNIZE`	`07_syndromes.sql`	the entire thesis: six diagnoses as `PATTERN` + `DEFINE`
`CLASSIFIER()` + `ALL ROWS PER MATCH`	`07_syndromes.sql`	turns a finding into an explanation, second by second
`EXECUTE IMMEDIATE` over a pattern table	`07_syndromes.sql`	18 row-pattern queries from one loop, so the sweep is a result
SQL UDF `F_CONFIDENCE`	`07_syndromes.sql`	one confidence definition, decomposable by hand
12 Dynamic Tables + `TARGET_LAG`	`04`-`06`	declarative freshness, no cron in the transform layer
`ASOF JOIN`	`06_marts_dt.sql`	a feed gap degrades the baseline instead of silently shifting it
`SNOWFLAKE.ML.CLASSIFICATION`	`05_ml_classification.sql`	dog-disjoint classifier, 77.46%, above the published cross-dog band
`FORECAST` / `ANOMALY_DETECTION` / `TOP_INSIGHTS`	`08_ml_timeseries.sql`	projection, deviation, drivers - one shared split boundary
`AI_COMPLETE` / `AI_CLASSIFY` / `AI_AGG`	`09_ai_layer.sql`	notes, triage, brief - batched by tasks, never on render
Task graph with `AFTER`	`11_tasks.sql`	one root, twelve tasks, one procedure each
Streamlit in Snowflake	`streamlit_app.py`	the app runs inside the account with the data, no egress

What it cost, read out of the account rather than estimated: 53.34 of a 400-credit trial grant over 7 days, 118 AI_COMPLETE + 118 AI_CLASSIFY + 540 AI_AGG with 0 failed batches, 0.000585 SOL in fees, and zero Cortex calls per page render, by construction - so a trial account cannot be drained by someone refreshing a tab during judging.

Where this ends up

The last tab is allowed to be quiet, because the context around it is not. American shelters have been at or above capacity for years, with dogs in shelters up roughly 22% since 2021 and non-live outcomes up 27% in a single year. Big dogs wait longest. Waiting is itself the harm.

Austin publishes a decade of intake records, and behaviour is a named outcome reason in them. The categories TELLTAIL detects on a collar, at home, from movement alone are the same categories written down at intake, after the relationship has already broken down. Two independent counts, deliberately on their own scales - a shared axis would invite a comparison the numbers do not support.

The behaviour-linked series is a subset of the total, so it is drawn inside it rather than beside it.

What's real and what's simplified

Real and running: everything in the Snowflake table above, against 3.1M live plus 10.6M bulk rows, 198,681 classified epochs, 118 matches across 32 dogs, 50,000 Austin records and 117 confirmed devnet attestations. python tests/run_all.py is green with no account and no network: 13 pattern tests, 9 demo-signal tests, the SiS compatibility suite, the chart layer native and degraded, and a parse of all twelve warehouse scripts.

Simplification	Why	Honest label
SHAKE and SCRATCH are heuristic, not model states	not first-class labels in this corpus	`state_source = 'HEURISTIC'`, 3.65% of epochs, banner on every tab, and `purity` scores those findings down
The "live" feed is a replayer over the real corpus	there is no live dog wearing this collar	`ingest/replay.py`, real rows and timestamps; only 12 of 45 dogs have a waveform, and the picker says so
11 of 18 sweep variants completed on the published run	Snowflake internal error `370001` on long `EXECUTE IMMEDIATE` loops	`MARTS.SWEEP_STATUS` records `ran_ok` and the verbatim error per variant
Breed photos are reference images	the corpus ships no per-dog photographs	stated in a column, under every image, and in the rail
Model introspection unavailable on this account	`Computation Error in function __SHOW_*`	ANOVA F-ratio in SQL, labelled as a different question
Attestations go to devnet, bridge is centralised	an attestation bridge, not a decentralised oracle network	stated plainly on the On Chain tab
S6 accounts for 109 of 118 matches	sniff, circle, sniff is genuinely the most common outdoor pattern	per-syndrome breakdown on screen, the two zero syndromes shown at zero

What this unlocks

The dog is the costume. Underneath is a primitive worth stealing: MATCH_RECOGNIZE turns "a sequence of events that means something" into a query, and almost every domain currently reaching for a threshold actually wants a sequence.

Fraud is a sequence. Churn is a sequence. Equipment failure is a sequence. Sepsis is famously a sequence. Every one is presently detected by someone thresholding a rolling average and then hand-writing an application to recover the ordering the average destroyed. They are all a PATTERN (...), a DEFINE, and a TARGET_LAG - plus, if you want anyone to act on the output, a confidence you can decompose and an evidence object the prose is not allowed to exceed.

One last thing, since it is nearly the 26th

International Dog Day is August 26th, and the date is not a marketing pick. Colleen Paige chose it because that is the day her family brought her first dog home from a shelter, when she was ten. So the holiday is not really about dogs. It is about the gap between a dog sitting in a shelter and a dog sitting in a house, and how many animals never cross it.

I am not going to claim a collar closes that gap. Most surrenders are housing and money, and no amount of SQL touches either. But some fraction of that intake is a behaviour that was a treatable medical problem eighteen months earlier, and that fraction is what this project is aimed at. A dog that stops rising easily is not being difficult - it hurts, it hurt quietly, and by the time it was legible as behaviour it had stopped being legible as pain.

Somewhere in that corpus is a dog that was showing a pattern for eleven days before a human noticed. The warehouse noticed on day two. That is not a claim about SQL - it is a claim about a gap a dog has to live inside while we catch up. A collar cannot close it. But "he seems fine, he just stops a lot now" is a sequence, and sequences are queryable, and it turns out we have had the right SQL clause since 2016.

Give your dog a scratch on the 26th. Then watch how it gets up.

Run it yourself

git clone https://github.com/SoumyaEXE/TellTail
cp .env.example .env                 # Snowflake creds, Kaggle token, devnet keypair
pip install -r requirements.txt && npm install

python scripts/profile_dataset.py    # Gate A: read the real CSV header first
python scripts/load_raw.py           # 10.6M rows
python scripts/run_sql.py --all      # schemas, DAG, ML, syndromes, AI, tasks
python ingest/replay.py --speed 60 --dogs 12   # the live feed
python scripts/austin_sync.py        # shelter data
npm run bridge:once                  # queue -> signed devnet attestations
python scripts/deploy_streamlit.py   # the dashboard, into the warehouse

python tests/run_all.py runs every offline check with no account and no network: the real PATTERN and DEFINE clauses compiled out of the SQL and exercised against fixtures, synthesised IMU signal through the real feature math, the SiS compatibility suite, and a parse of every warehouse script.

References

Wright, A. et al. (2022). Identification of canine osteoarthritis using an owner-reported questionnaire. Journal of Small Animal Practice.
Merck Veterinary Manual. Recognizing and Assessing Pain in Animals.
Kumpulainen, P., Vehkaoja, A. et al. (2021). Dog behaviour classification with movement sensors placed on the harness and the collar. Applied Animal Behaviour Science 241, 105393. Data: Mendeley, CC BY 4.0.
SQL:2016, ISO/IEC TR 19075-5:2016, Row Pattern Recognition.
TIME (8 Jan 2024), Surge in Unwanted Dogs Fuels 'Crisis' Across U.S. Animal Shelters · City of Austin, Animal Center intakes and outcomes.

Built with @dronzer2code within the challenge window, with AI pair-programming. Breed photographs: Stanford Dogs (Khosla et al., FGVC 2011). TELLTAIL is not a diagnostic device and nothing in it substitutes for a veterinarian.

My dashboard took 7.6 seconds to render fifteen numbers 🐌

Soumyadeep Dey — Thu, 23 Jul 2026 06:19:53 +0000

This is a submission for DEV's Summer Bug Smash: Clear the Lineup, powered by Sentry.

Submitting for: Clear the Lineup · Best Use of Sentry

My Sentry trace had no database spans. The database was 77% of the request.

7,580 ms to 1,671 ms. 24.8 MB of serialized result down to 6.0 MB. Same fifteen numbers on screen.

Fifteen numbers. Four stat tiles, three donut slices, six bars, four recent invoice rows. That is the entire visible output of the page.

It took 7.58 seconds.

On my laptop the same page took 111 ms, because my laptop had four invoices in it. Same code, same query, same render, same everything. The only variable that moved was how much there was.

So I installed @sentry/nextjs, opened the trace, and found one grey box with dashboard written on it and nothing underneath.

The instrument was silent. I read that as good news for about ten minutes.

Reproduce every number in this post: github.com/SoumyaEXE/dashboard-perf-repro - seeder, benchmark harness, and the before and after query pair on a throwaway MongoDB. The application repo stays private because it holds real client invoices.

TL;DR

A Next.js 16 dashboard took 7.58 s to render fifteen aggregate numbers. The first Sentry trace showed no database spans at all, because mongoIntegration() and mongooseIntegration() are not among the default integrations in @sentry/node. Turning them on revealed that MongoDB was 77% of the request, and that the slow spans were getMore (cursor continuation), not find. That distinction matters: a slow find means an index problem, a long getMore means a payload problem. The root cause was an unprojected Invoice.find() pulling a base64 logo out of all 2,004 documents, plus a JavaScript reduction over 4,806 tasks to produce eight rows. Three changes, a .select() projection, a $facet aggregation, and a comparator that returns 0, took it to 1.67 s and dropped the serialized result from 24.8 MB to 6.0 MB.

If your trace shows no database spans, verify the integration before you conclude the database is fast.

The measurement rules, before any number below

Two different numbers appear in this post and they are not the same measurement. The 14.89 s in the trace screenshots is a development request: Turbopack compiling, dev overhead, 100% Sentry sampling. The 7,580 ms in every before and after table is the benchmark: the same server functions, called directly, no instrumentation attached. The trace diagnosed the bug. The benchmark scored it.

Every byte figure is a serialized result size, Buffer.byteLength(JSON.stringify(result)), produced by my own harness. It is not compressed network traffic. Real wire numbers need MongoDB profiler data or driver-level metrics, and I did not collect those, so I am not going to imply I did.

Every timing is the median of five runs against the same seeded corpus, before and after.

Metric	Before	After	Change
Total server-function time	7,580 ms	1,671 ms	4.5x faster
`getInvoicesAdmin()`	6,669 ms	1,597 ms	4.2x faster
`getDashboardOverview()`	911 ms	74 ms	12.3x faster
Serialized MongoDB result	24,849 KB	6,048 KB	76% lower
Overview serialized result	2,490 KB	2.0 KB	99.9% lower
MongoDB `getMore` spans	Present	Absent	Result fell below the cursor batch threshold
Self-contradictory comparator	`true`	`false`	Correctness defect, not a speed one

Project overview

DevLabs is the agency platform my team runs on: Next.js 16, React 19, MongoDB, Mongoose, roughly 34,000 lines. Marketing site, internal dashboard, and a client-facing portal holding invoices, contracts and customer records.

This is not a demo I built to have something to profile. Clients open this page. The data on it is real customer names and real invoice totals, which is exactly why the instrumentation section below has a privacy subsection instead of a shrug, and why the application repo is private. Every diff in this post is the actual change that shipped, and the public repro repo above runs the same query shapes against a schema you can seed yourself.

Why a Sentry trace can show no database spans

The first trace had one opaque server span. No find. No getMore. Nothing that looked like a database at all.

The tempting read is the database is fine, go look at React. So before I went looking at React, I asked the package what it was actually loading:

$ node -e "const n=require('@sentry/node'); \
  const d=n.getDefaultIntegrations({}); \
  console.log('total:', d.length); \
  console.log('mongo:', d.map(i=>i.name).filter(x=>/[Mm]ongo/.test(x)))"

total: 17
mongo: []

Seventeen default integrations on this version. Zero of them knew what MongoDB was.

A trace with no database spans has not told you the database is fast. It has told you nobody is watching it. Those are wildly different sentences and only one of them is in your dashboard.

// src/instrumentation.ts
const integrations = isNode
  ? await import("@sentry/node").then((node) => [
      node.mongoIntegration(),
      node.mongooseIntegration(),
    ])
  : [];

Sentry.init({
  dsn: DSN,
  tracesSampleRate,
  integrations,
  sendDefaultPii: false,
});

Two lines. The grey box turned into this:

Span	Duration
`mongodb` `{"getMore": ...}`	4.42 s
`mongoose.projects.find`	2.67 s
`mongodb` `{"getMore": ...}`	1.35 s
`mongoose.customers.find`	134 ms
`mongodb` `{"find": {"firebaseUid": "?"}}`	86 ms

Two getMore spans totalling 5.77 seconds in a single dashboard load.

What a slow MongoDB getMore span actually tells you

getMore is cursor continuation. When a result does not fit in one batch, the driver goes back for the rest. A getMore span on its own proves nothing except that a result set was large enough to need more than one trip.

That is still the sentence that reframed the whole investigation. A slow find would have sent me to indexes and query predicates, which is where I would have gone by instinct and where I would have found nothing, because the predicate here was {} and there is no index that makes {} faster. Long getMore next to a 21.8 MB serialized result says something different: the query is not struggling to locate the documents, it is struggling to carry them.

The dev trace made the shape obvious once the spans existed:

http.server  GET /dashboard ................. 14.89 s
  db  mongoose.invoices.find ................ 13.32 s
  db  mongoose.users.findOne ................ 845 ms
  resolve page components ................... 3.93 ms
  build component tree ...................... 11.73 ms

Sentry span breakdown: db 77% · default 12% · function.nextjs 12%

Those children do not add up to the parent and they are not supposed to. Spans overlap, and smaller framework spans are omitted. 14.89 s is the transaction, the rest is attribution. Also worth naming: the saslStart, saslContinue and createIndexes spans are connection handshake, they fire once per process, and I threw them out of the recurring diagnosis rather than counting them as dashboard cost.

Three point nine three milliseconds to resolve the page components. Eleven point seven to build the tree. React was never the problem, and if the integrations had stayed off I would have spent the evening proving that the slow way.

77% of the transaction, in a request I had already been told contained no database work.

Making my laptop lie convincingly

The bug could not be reproduced locally because four invoices is not a data volume, it is a rounding error. So the first thing I wrote was not a fix. It was a seeder.

It points at whatever MONGODB_URI is configured and follows two rules I would want any script pointed at a real database to follow:

It only ever inserts. It never updates or deletes a document it did not create.
Every generated document is tagged, so cleanup can only ever touch generated data.

const SEED_TAG = "[perf-seed]";

// Stands in for the base64 data URI on a real uploaded logo.
const FAKE_LOGO = `data:image/png;base64,${"iVBORw0KGgoAAAANSUhEUg".repeat(360)}`;

That second constant is the whole post. If your seeder generates tidy little documents, your seeded database is not your production database wearing a costume, it is a different database that happens to have the same row count. Seed the ugly fields.

npm run seed:perf -- --yes        # 2,000 invoices, 40 projects
npm run bench:dashboard           # read-only

Corpus: 2,004 invoices, 42 projects, 4,806 tasks visible to the overview query, 5 runs, median reported.

getInvoicesAdmin()      6668.8 ms    22358.6 KB serialized result
getDashboardOverview()   911.4 ms     2490.0 KB serialized result, 4806 tasks scanned
                                      8 activity rows kept
total server time       7580.2 ms

Four thousand eight hundred and six tasks scanned. Eight rows kept.

What the page was actually doing

flowchart TD
    A["GET /dashboard"] --> B["getInvoicesAdmin()"]
    A --> C["getDashboardOverview()"]

    B --> D["Invoice.find()<br/>no filter · no limit · no projection"]
    D --> E["2,004 full documents<br/>base64 logo in every invoice<br/>21.8 MB serialized"]
    E --> F["invoiceFieldsFrom() × 2,004"]

    C --> H["Project.find().select('name board activity')"]
    H --> I["every board, every task, every log row<br/>2.4 MB · 4,806 tasks"]
    I --> J["reduce in JavaScript<br/>8 activity rows + counters"]

    F --> K["return 2,004 invoice rows"]
    J --> K
    K --> L["render 15 numbers"]

    style E fill:#c62828,color:#fff
    style I fill:#c62828,color:#fff
    style L fill:#2e7d32,color:#fff

Three defects, and none of them are exotic.

Defect 1: an unprojected Mongoose find() shipping a picture with every row

const invoices = await Invoice.find()
  .populate(INVOICE_POPULATE)
  .sort({ createdAt: -1 })
  .lean();

No projection means every field of every document. The invoice schema carries fields.companyDetails.logo, and uploaded logos live there as base64 data URIs. So every one of the 2,004 invoices came over the wire carrying a complete copy of an image.

The list renders a serial number, a customer name and a total. It has never once rendered the logo. It was being handed one anyway, two thousand times, on every load.

+const INVOICE_LIST_EXCLUDE =
+  "-fields.companyDetails.logo -fields.metadata -access";

 const invoices = await Invoice.find()
+  .select(INVOICE_LIST_EXCLUDE)
   .populate(INVOICE_POPULATE)
   .sort({ createdAt: -1 })
   .lean();

serialized result: 22,358 KB -> 6,046 KB
execution time:     6,669 ms -> 1,597 ms

One line. The detail view still selects the full document, so nothing on screen changed anywhere. The list contract is identical. The only thing removed is fields the caller was never reading.

Defect 2: the comment was true, the query was not

The code said this:

// Aggregated CRM data for the dashboard Overview tab. One query pass,
// everything trimmed to what the cards render.

The code then did this:

Project.find().select("name board activity").lean()

Every board. Every task on every board. The entire append-only activity log, for every project. Then JavaScript reduced all of it down to eight activity rows, four counters and four project bars, and threw the rest away.

The comment was not lying about the intent. It was describing a reduction that was happening in the wrong process. So I moved it to the one that already has the data:

Project.aggregate([
  {
    $facet: {
      recentActivity: [
        { $unwind: "$activity" },
        { $sort: { "activity.time": -1 } },
        { $limit: 8 },
        {
          $project: {
            _id: 0,
            projectId: "$_id",
            projectName: "$name",
            text: "$activity.text",
            time: "$activity.time",
          },
        },
      ],
      taskTotals: [
        { $unwind: "$board" },
        { $unwind: "$board.tasks" },
        { $group: { _id: "$board.tasks.status", count: { $sum: 1 } } },
      ],
      perProject: [
        { $unwind: "$board" },
        { $unwind: "$board.tasks" },
        {
          $group: {
            _id: "$_id",
            name: { $first: "$name" },
            total: { $sum: 1 },
            done: {
              $sum: {
                $cond: [{ $eq: ["$board.tasks.status", "Done"] }, 1, 0],
              },
            },
          },
        },
        { $sort: { total: -1 } },
        { $limit: 4 },
      ],
    },
  },
]);

$facet runs three independent reductions in one request, so the three cards do not become three round trips.

execution time:          911 ms -> 74 ms
serialized result size:  2,490 KB -> 2.0 KB

Two point zero kilobytes. That is what fifteen numbers actually weigh.

Defect 3: a JavaScript comparator that disagrees with itself

Two files in this path contained this:

.sort((a, b) => (b.createdAt > a.createdAt ? 1 : -1))

It never returns 0. Feed it two invoices with the same timestamp and it says a comes first. Hand it the same two in the other order and it says b comes first. That is not a sort order, that is an opinion that depends on the input.

Once a comparator violates its contract, the output is at the mercy of array length, input order and whatever the engine felt like doing that day. And equal timestamps are not a thought experiment here: bulk imports, seed runs and fast manual entry all produce them.

-.sort((a, b) => (b.createdAt > a.createdAt ? 1 : -1))
+.sort((a, b) => (
+  a.createdAt === b.createdAt
+    ? 0
+    : a.createdAt < b.createdAt
+      ? 1
+      : -1
+))

self-contradictory comparator: true -> false

This runs in a client component, so inconsistent ordering could in principle produce different server and browser output. I did not reproduce a hydration mismatch in the 64-element benchmark, so that stays a consequence I can argue for and not a symptom I measured. This fix is worth zero milliseconds. It is in here because it was sitting in the same code path and it was wrong.

The trace, after

The getMore spans are gone. Not shorter. Gone, because the projected result fits in batches the driver does not need to chase.

The bars getting smaller is the boring evidence. The shape changing is the real one:

Before: full documents -> oversized result -> long getMore spans
After:  projected fields -> smaller result -> no getMore at all

Best Use of Sentry

Distributed Tracing did the diagnosis, and it did it by showing me nothing. An empty waterfall under a 7.6 second request is a finding. It is the finding that reframed the entire investigation, because mongoIntegration() and mongooseIntegration() are not in the 17 defaults, so the database was invisible rather than fast.

Once the spans existed, getMore was the tell. A slow find sends you to indexes. A long getMore beside a 21.8 MB result says the query is not struggling to locate documents, it is struggling to carry them. Different bug, different fix, and I would have spent the evening in the wrong file without that span.

Span attributes tag the flow so the trace answers itself on the next incident:

const span = Sentry.getActiveSpan();
span?.setAttribute("invoice.count", invoices.length);
span?.setAttribute("invoice.projection", "list");
span?.setAttribute("invoice.serialized_kb", serializedKb);

Session Replay, configured for a page that renders real client names and real invoice totals:

Sentry.replayIntegration({
  maskAllText: true,
  blockAllMedia: true,
});

sendDefaultPii: false covers the event payload. Masking and media blocking cover the recording. Different data paths, and setting one does not set the other. Replay on a billing screen without those two flags is a data export with a nice UI.

Sampling is deliberate rather than inherited. 100% in development, because while I am diagnosing I want every request. Sampled in production, because I do not need every request to know where time goes.

What Seer said about it

[SCREENSHOT: Seer root cause analysis panel for the GET /dashboard transaction]

[Quote Seer's actual root cause text here, verbatim, then one or two
sentences on where it agreed with your manual diagnosis and where it
did not. The interesting version of this section is the disagreement,
not the agreement.]

The signal I was missing, and the one I added afterwards

Traces found this bug. Traces would not have caught it.

Traces are sampled, and the one request a client complains about is reliably the one that got sampled out. The bug never threw, so Issues stayed empty the entire time. The page returned 200 for months while it was ruining someone's afternoon.

Errors, traces, logs, metrics. I had one of the four pointed at this page, and it was the sampled one. So after the fix I turned on the two that would have paged me instead of the client.

// LOG: the decision point, on every request, not sampled.
Sentry.logger.info("dashboard invoice list", {
  rows: invoices.length,
  projection: "list",
  serialized_kb: serializedKb,
});

Logs are searchable by client and by route without hoping the right request survived sampling. If one account starts returning three thousand rows where everyone else returns forty, the log says so before anybody files a ticket.

The metric is the aggregate version of the same question. dashboard.result_bytes, a distribution keyed by route, is the line that moves for weeks before a page ever feels slow. Every unprojected find() is a bet that N stays small. A distribution on result size is that bet, plotted. Latency alerts fire after the page is already bad. Payload size trends up first, and it trends up quietly.

What I did not fix, and what I refuse to claim

Post-fix benchmark:

getInvoicesAdmin()      1,597 ms
getDashboardOverview()     74 ms
total server time       1,671 ms

1.671 s is not the finish line. It is the point where guessing stops paying. The 5.9 seconds I removed came out of measurement. The remaining 1.597 s has not been decomposed into query, population, transform, serialization and connection timings yet, and until it is I am not assigning it to a cause. Guessing is how a base64 logo survived in a list view in the first place.

What I did verify is that the page still says the same things:

Output	Before	After
Activity rows	8	8
Project bars	4	4
Task-status totals	Identical	Identical
Invoice totals	Identical	Identical
Intended invoice ordering	Preserved	Preserved

"Identical" means the displayed values and the fields the list reads. The raw invoice documents are deliberately not byte-identical, because removing unused fields was the entire point.

The optimization I turned down

The obvious next move is computing invoice totals inside MongoDB and never loading the documents at all. Here is why I did not:

export function getTotalValue(data: ZodCreateInvoiceSchema): number {
  let total = getSubTotalValue(data) - getDiscountValue(data);

  for (const detail of data.invoiceDetails.billingDetails) {
    if (detail.type === "percentage") {
      total += (total * detail.value) / 100;
    } else {
      total += detail.value;
    }
  }

  return Math.round(total * 100) / 100;
}

The billing details are sequential. Each percentage applies to the running total, so order of operations changes the answer. Porting that into an aggregation pipeline means two implementations of financial arithmetic that have to stay behaviourally identical forever, and the day they drift is the day a client gets an invoice with the wrong number on it.

Faster page, second source of truth for money. No thanks. I made the documents smaller instead.

Reproduce it

Everything below runs against the public repro repo:

SoumyaEXE / dashboard-perf-repro

dashboard-perf-repro

Minimal reproduction for My dashboard took 7.6 seconds to render fifteen numbers.

An unprojected Invoice.find() carrying a base64 logo in every document and a JavaScript reduction over thousands of tasks to produce eight rows Seed it, benchmark it, apply the two fixes, benchmark again.

The application this came from is a private agency platform holding real client invoices. This repo reproduces the query shapes and the failure mode, not the application.

Run it

cp .env.example .env      # point MONGODB_URI at a THROWAWAY database
npm install
npm run seed -- --yes     # 2,000 invoices, 40 projects
npm run bench             # read-only, median of 5 runs
npm run clean -- --yes    # removes only documents tagged [perf-seed]

What you should see

Metric	Before	After
`getInvoicesAdmin()`	~6,700 ms	~1,600 ms
`getDashboardOverview()`	~910 ms	~74 ms
Serialized invoice result	~22 MB	~6 MB
Overview serialized result	~2.5 MB	~2 KB

Absolute milliseconds…

View on GitHub

git clone https://github.com/SoumyaEXE/dashboard-perf-repro
cd dashboard-perf-repro
cp .env.example .env          # point MONGODB_URI at a throwaway database
npm install
npm run seed -- --yes         # 2,000 invoices, 40 projects
npm run bench                 # read-only, median of 5

The harness is read-only and calls the same functions the dashboard calls:

const RUNS = 5;

function median(values: number[]) {
  const sorted = [...values].sort((a, b) => a - b);
  const middle = Math.floor(sorted.length / 2);
  return sorted.length % 2
    ? sorted[middle]
    : (sorted[middle - 1] + sorted[middle]) / 2;
}

async function time<T>(fn: () => Promise<T>): Promise<[T, number]> {
  const started = performance.now();
  const result = await fn();
  return [result, performance.now() - started];
}

async function serializedResultBytes(model: Model<any>, select?: string) {
  const query = model.find({});
  if (select) query.select(select);

  const result = await query.lean();
  return Buffer.byteLength(JSON.stringify(result), "utf8");
}

const times: number[] = [];

for (let run = 0; run < RUNS; run += 1) {
  times.push((await time(getInvoicesAdmin))[1]);
}

console.log(`time: ${median(times).toFixed(1)} ms`);
console.log(
  `serialized result: ${(await serializedResultBytes(Invoice) / 1024).toFixed(1)} KB`,
);
console.log("rendered: 4 tiles, 3 donut counts, 6 bars, 4 rows");

One caveat on the tables above: function-level and summary byte totals differ slightly (22,358 vs 24,849 KB) because they are serialized at different scopes. I left both numbers in rather than quietly picking the flattering one. Your absolute milliseconds will differ from mine depending on where your database lives. The ratio is the part that should reproduce.

Three greps, and you probably have this bug

1. Unprojected list queries.

rg "\.find\(\)" --type ts -A 3 | rg -v "select|limit|projection"

Read every hit and ask the only question that matters: does the caller read every field of every document it just asked for?

2. Large embedded fields. Base64 images, rich text bodies, append-only logs, uncapped arrays. One fat field multiplied by a few thousand documents beats any index you were about to add.

3. Input cardinality versus output cardinality. If a function reads N documents and always returns a constant number of rows, the reduction belongs in the database. Scanning 4,806 tasks to display four numbers is not a subtle signal.

And the one that made this whole investigation possible: if the trace shows no database spans, verify the integration before you conclude the database is fast.

FAQ

Why does my Sentry trace show no database spans?

Because the database integration is probably not loaded. @sentry/node ships 17 default integrations and neither mongoIntegration() nor mongooseIntegration() is among them. Check what you actually have with getDefaultIntegrations({}).map(i => i.name) before you conclude the database is fast.

What does a long MongoDB getMore span mean?

getMore is cursor continuation. The driver returns for another batch when a result set does not fit in one. Long getMore time points at result size, not at a missing index. If your predicate is {} there is no index that helps, so look at what fields you are pulling back instead.

Will adding .select() to a Mongoose query break the callers?

Only if a caller reads a field you excluded. Exclude fields at the list layer and keep the full document on the detail route. In this case the list rendered a serial number, a customer name and a total, and had never once rendered the logo it was being handed.

When should a reduction move from JavaScript into a MongoDB aggregation?

When input cardinality and output cardinality diverge. Reading 4,806 tasks to display four numbers means the reduction is happening in the wrong process. $facet lets several independent reductions run in one round trip.

Is a JavaScript comparator that never returns 0 actually a bug?

Yes. Array.prototype.sort requires a consistent comparator. One that never returns 0 gives contradictory answers for equal elements, and the resulting order depends on array length and engine internals. Equal timestamps are common from bulk imports and seed runs.

What actually changed

Three diffs. A .select(), a $facet, and a comparator that returns 0.

None of it is clever. That is the point. The dashboard was not badly written, it was written against four invoices, and it kept being correct right up until the client got busy enough to make it slow. The code did not rot. The business grew, and every unprojected find() in a codebase is a quiet bet that N stays small.

The expensive part was never the fix. It was believing an empty trace.

7.58 s to 1.67 s, same fifteen numbers on screen.

I wanted Snowflake to do more than store data. So I built a pipeline where it judges World Cup conviction, then writes its verdict back to Solana. The architecture ended up being my favorite part of the project.

Soumyadeep Dey — Thu, 16 Jul 2026 06:08:45 +0000

DEV Weekend Challenge: Passion Edition Submission

Soumyadeep Dey

Jul 12

We Taught a Snowflake Warehouse to Judge World Cup Conviction and Write the Verdict Back to Solana

#devchallenge #weekendchallenge #snowflake #100daysofsolana

15 min read

We Taught a Snowflake Warehouse to Judge World Cup Conviction and Write the Verdict Back to Solana

Soumyadeep Dey — Sun, 12 Jul 2026 18:09:31 +0000

This is a submission for Weekend Challenge: Passion Edition

Target categories: Best use of Snowflake + Best use of Solana.

The single most common mistake in cross-platform hackathon projects is using
one platform as a database and the other as a logo. A dashboard that reads
Solana data through an API and renders it in a UI is not a Solana project.
It's a dashboard.

The actual first question is: what can each platform do that the other
physically cannot?

Solana has behavioral data that exists nowhere else - every buy, sell, and
hold, public and permanent. But a blockchain cannot compute aggregates over
its own history; that's not what the runtime is for. Snowflake can chew
through millions of decoded transactions with a declarative SQL DAG - but it
has no way to make a statement the rest of the on-chain world can read and
build on.

Strip away the World Cup framing and what I built is a pattern: a data
warehouse acting as a first-class blockchain participant. Read state that
only exists on-chain, compute something the chain cannot compute about its
own history, and commit the answer back where other programs can consume it.

Solana (mainnet) --read--> Snowflake --compute--> Snowflake --write back--> Solana (devnet)
  live swaps and             streaming              conviction               an oracle account
  transfers, decoded         ingest + Dynamic       scoring + Cortex         any program can read
  by Helius                  Tables DAG             ML

FERVOR is a real-time conviction oracle for World Cup fandom on Solana.
It tracks sixteen national token communities from the 2026 field - Argentina
to Japan, Morocco to Canada - wired into eight derby rivalries (ARG-BRA,
FRA-ENG, POR-ESP, GER-NOR, USA-MEX, NED-BEL, CRO-MAR, JPN-CAN), and it does
not measure hype, because following a crowd is cheap. It measures
conviction: the wallet that keeps buying its country's token while the
price bleeds, the holder who never sells through a losing run. Passion,
defined mechanically: holding when it hurts.

One thing up front, because it changes how you read every number below

The pipeline is real and running unattended. The fan wallets are not - yet.

Unofficial "national" meme tokens need per-mint liquidity vetting before you
point mainnet ingest at them, so the sixteen mints in REF.TEAM_TOKENS are
still PLACEHOLDER_MINT_*, and the wallet activity flowing through the DAG
is a labeled synthetic feed (_batch_id = 'DEMO_SEED').

What that means precisely: the seeder injects Helius-shaped transactions
upstream, into the raw landing table, and the real warehouse computes every
downstream number from there. Nothing in MARTS is hand-written. Nothing is
mocked below the landing zone. One UPDATE per row in REF.TEAM_TOKENS
points ingest at verified mints and the identical SQL scores real traders.

Full accounting in What's real vs. what's simplified at the bottom. I'd rather you
read the architecture knowing this than discover it in a footnote.

Verify this in 60 seconds - no keys, no clone

The oracle is live. Snowflake's output lands on devnet every 5 minutes, signed and confirmed. Watch the oracle account
- or open the Norway write below and expand the Memo instruction to read the JSON the warehouse emitted.
The warehouse is real. The Dynamic Tables DAG, live in Snowsight
- the actual lineage, not the diagram. And no cron anywhere: every table declares TARGET_LAG and Snowflake works out the refresh order.
The scoring is SQL. warehouse/03_marts_dt.sql
- the conviction score is twelve lines, and it is the only place a wallet's loyalty is decided.

Repo: github.com/SoumyaEXE/weekend-challenge

Every 5 minutes, a Snowflake task stages the freshly computed index and a
signing bridge writes it to Solana devnet as a confirmed transaction:

{
  "oracle": "FERVOR",
  "source": "Snowflake MARTS.TEAM_FERVOR_INDEX",
  "team_id": 10,
  "team": "NORWAY",
  "fervor_index": 236,
  "momentum": -21
}

That JSON is Snowflake output living on a block explorer. fervor_index is a
SQL aggregate. momentum is a Cortex forecast - a machine-learning output
from inside the warehouse, published to a blockchain. Negative momentum means
the model expects the index to fall.

And this is the league the warehouse produces:

Snapshot. The index recomputes every minute; the on-chain writes are every five.

Argentina leads on conviction (109 average days held), Japan is the surprise
runner-up, England has the deepest bench of believers, and Norway folds under
pressure - on the seeded feed.

Norway is the transaction linked above. The prose, the league table, and the
block explorer are three views of one number, and none of them was written by
hand. And "folds under pressure" is the model's verdict, not mine: the
momentum: -21 in that payload is Cortex forecasting Norway's conviction to
slide another 21 points, and that projection is what got signed on-chain.

And that is the honest claim worth making. This table does not prove
Argentina's fans are loyal. It proves the engine discriminates: sixteen
behavioral profiles go into the raw landing table, and the warehouse alone
decides who is a believer and who is a tourist. The ranking is an output, not
an input. Swap the mints and the same SQL judges real money.

The architecture, actually explained

Six stages. Every stage does load-bearing work; there is no component whose
only job is to name-drop a technology.

#	Stage	Tech	Latency	What it does
1	INGEST	Node worker + Helius enhanced transactions	8 s batches	Polls decoded mainnet activity for the 16 tracked country tokens
2	LAND	micro-batch inserts	seconds	Raw VARIANT JSON into `RAW.SOLANA_TX_LANDING`
3	TRANSFORM	Snowflake Dynamic Tables	1 min lag	7-table declarative DAG: decode, price, position, score
4	INTELLIGENCE	Snowflake Cortex	5 min task	`ANOMALY_DETECTION`, `FORECAST`, `COMPLETE`
5	WRITE-BACK	Node signing bridge + Anchor	5 min task	Queue staged in SQL, signed and confirmed on devnet
6	SERVE	Streamlit-in-Snowflake	live	8-tab analytics app, zero external hosting

One database, six schemas, data flowing strictly left to right:

Schema	Purpose	Key objects
`REF`	hand-loaded seed	`TEAM_TOKENS` (mint, decimals, rival_team_id), `PARAMS`
`RAW`	untouched VARIANT landing	`SOLANA_TX_LANDING`
`STAGING`	decoded events (Dynamic Tables)	`TOKEN_TRANSFERS`, `SWAP_EVENTS`, `PRICE_TICKS`
`MARTS`	conviction metrics (Dynamic Tables)	`WALLET_POSITIONS`, `WALLET_FERVOR`, `TEAM_FERVOR_INDEX`, `DEFECTION_FLOWS`
`ML`	Cortex outputs + time series	`TEAM_INDEX_HISTORY`, `FERVOR_ANOMALIES`, `FERVOR_FORECAST`
`ORACLE`	write-back queue + audit	`PUBLISH_QUEUE`, `PUBLISH_LOG`

Stage 1: ingest - a cursor trick that makes polling feel like streaming

Helius decodes raw Solana transactions into clean JSON, so I ingest
structured events instead of byte soup. The worker polls each tracked mint
every 8 seconds, but the until cursor means each poll returns only
transactions newer than the last one seen. Functionally, it's a stream:

// ingest/poller.ts - the cursor is the whole trick
const cursor = new Map<string, string>(); // address -> newest seen signature

async function fetchNewTxs(address: string): Promise<EnhancedTx[]> {
  const until = cursor.get(address);
  const url =
    `https://api.helius.xyz/v0/addresses/${address}/transactions` +
    `?api-key=${HELIUS_API_KEY}&limit=50` +
    (until ? `&until=${until}` : "");
  const res = await fetch(url);
  const txs = (await res.json()) as EnhancedTx[];
  if (txs.length > 0) cursor.set(address, txs[0].signature); // newest first
  return txs;
}

Landing is a multi-row INSERT ... SELECT PARSE_JSON(?) through the Node
connector. Honest note: true Snowpipe Streaming needs the Java ingest SDK.
8-second micro-batches demo identically (rows appear in Snowflake seconds
after they land on-chain) and were far more reliable to stand up in a
weekend.

Stage 3: the Dynamic Tables DAG - no cron, declared lag

I did not write a single scheduler for the transform layer. Each table
declares TARGET_LAG = '1 minute' and Snowflake works out the refresh order:

RAW.SOLANA_TX_LANDING
    |- STAGING.TOKEN_TRANSFERS ----> MARTS.DEFECTION_FLOWS
    |- STAGING.SWAP_EVENTS -------> STAGING.PRICE_TICKS
    |- MARTS.WALLET_POSITIONS
           |- MARTS.WALLET_FERVOR ----> MARTS.TEAM_FERVOR_INDEX
                                             |- (1 min task) ML.TEAM_INDEX_HISTORY
                                             |- (5 min task) Cortex models
                                             |- (5 min task) ORACLE.PUBLISH_QUEUE

The result is a per-minute index series where each of the sixteen nations
charts its own story:

Two pieces of the transform SQL earned their place the hard way.

Decoding BUY/SELL without per-DEX parsers. Helius does not emit a parsed
swap event for every venue, so I classify from token-balance movement: fee
payer receives the tracked token = BUY, sends it = SELL. Routed swaps get
their legs summed, and self-arbitrage transactions (fee payer both sends AND
receives the same token in one tx) are dropped entirely - an arb bot carries
zero conviction signal:

-- warehouse/02_staging_dt.sql (excerpt)
team_legs AS (
  SELECT l.signature,
         MIN(l.block_time)                  AS block_time,
         l.raw_payload:feePayer::string     AS wallet,
         tt.value:mint::string              AS mint,
         r.team_id,
         IFF(tt.value:toUserAccount::string = l.raw_payload:feePayer::string,
             'BUY', 'SELL')                 AS side,
         SUM(tt.value:tokenAmount::float)   AS token_amount
  FROM dedup l,
       LATERAL FLATTEN(input => l.raw_payload:tokenTransfers) tt
  JOIN REF.TEAM_TOKENS r ON r.mint = tt.value:mint::string
  WHERE tt.value:toUserAccount::string   = l.raw_payload:feePayer::string
     OR tt.value:fromUserAccount::string = l.raw_payload:feePayer::string
  GROUP BY l.signature, l.raw_payload:feePayer::string,
           tt.value:mint::string, r.team_id, side
  -- self-arb filter: a signature with BOTH a BUY and a SELL row for the
  -- same token makes this window count 2, and the tx is excluded
  QUALIFY COUNT(*) OVER (PARTITION BY l.signature, r.team_id) = 1
)

The 24-hour price change that survives trading gaps. The naive version is
LAG(price, 24) over hourly buckets. That counts ROWS, not HOURS - one
quiet hour and your "24h change" silently becomes a 25h change. ASOF JOIN
matches each tick to the nearest tick at or before 24 hours earlier, with a
warm-up fallback for series younger than a day:

-- warehouse/02_staging_dt.sql (excerpt)
SELECT cur.team_id, cur.window_start, cur.price_usd,
       100.0 * (cur.price_usd - COALESCE(day_ago.price_usd, first_tick.price_usd))
             / NULLIF(COALESCE(day_ago.price_usd, first_tick.price_usd), 0)
         AS price_change_24h_pct
FROM hourly cur
ASOF JOIN hourly day_ago
  MATCH_CONDITION (DATEADD('hour', -24, cur.window_start) >= day_ago.window_start)
  ON cur.team_id = day_ago.team_id
LEFT JOIN ( -- warm-up: earliest tick, for series younger than 24h
  SELECT team_id, price_usd FROM hourly
  QUALIFY ROW_NUMBER() OVER (PARTITION BY team_id ORDER BY window_start) = 1
) first_tick ON first_tick.team_id = cur.team_id;

That distinction is not pedantry. A dip is what conviction is measured
against, so a 25-hour "24h change" quietly reclassifies who was buying the
bleed. Prices use the hourly median, not VWAP - SOL-leg attribution is
heuristic, and one mis-attributed leg destroys a volume-weighted mean.

The conviction score - the creative payload

Hype is a headcount. Conviction is a cost. Per wallet, per country, 0 to 100:

Component	Max points	What it rewards
`25 x ln(1 + hold_days) / ln(366)`	25	longevity of the relationship
`30 x min(1, dip_buys / 5)`	30	buying while THEIR token bled 5%+ in 24h
`25 x min(1, underwater_buys / 5)`	25	buying below their own average cost
`-20 x (sold within 3 days)`	-20	punishes paper hands

-- warehouse/03_marts_dt.sql (excerpt)
ROUND(GREATEST(0, LEAST(100,
      25 * LN(1 + a.hold_duration_days) / LN(1 + 365)
    + 30 * LEAST(1, COALESCE(c.buy_against_decline, 0) / 5.0)
    + 25 * LEAST(1, COALESCE(c.buy_at_loss, 0) / 5.0)
    - 20 * IFF(a.last_sell_time > DATEADD('day', -3, SYSDATE()), 1, 0)
)), 1) AS fervor_score

Two engineering decisions worth stealing:

Tenure in fractional days (DATEDIFF('second', ...) / 86400.0). Whole-day granularity freezes longevity at zero for 24 hours and flat-lines the index on launch day.
Zero-signal wallets are excluded from the national average. The raw swap feed is dominated by one-shot MEV bots whose score is 0 by construction. Averaging them in measures bot traffic, not the fan base:

ROUND(( COALESCE(AVG(IFF(f.fervor_score > 0, f.fervor_score, NULL)), 0) * 0.6
      + LEAST(100, COUNT_IF(f.fervor_score >= 60) / 10.0) * 0.4 ) * 10, 0)
  AS fervor_index   -- 0 to 1000, this exact number goes on-chain

And the emotional centerpiece, MARTS.DEFECTION_FLOWS: the same wallet
selling one country and buying another within 24 hours. Selling ARGENTINA to
buy BRAZIL is not a portfolio rebalance. It is treason, and it gets its own
Sankey:

Stage 4: Cortex - and the rule that the LLM never produces a number

One rule governs every metric in this system: SQL and fitted models compute
the numbers. The LLM only writes prose. Nothing an LLM says reaches the
index, the league table, or the chain.

Three Cortex models, retrained every 5 minutes by a task.

ANOMALY_DETECTION - with a strict train/detect split.

-- warehouse/04_ml_cortex.sql (excerpt)
CREATE OR REPLACE SNOWFLAKE.ML.ANOMALY_DETECTION ML.FERVOR_ANOMALY_MODEL(
  INPUT_DATA => SYSTEM$QUERY_REFERENCE(
    'SELECT team_id, ts, fervor_index FROM FERVOR.ML.TEAM_INDEX_HISTORY
      WHERE NOT COALESCE(is_synthetic, FALSE)          -- never train on demo spikes
        AND ts < DATEADD(minute, -15, SYSDATE())'),    -- strict train/detect split
  SERIES_COLNAME => 'TEAM_ID', TIMESTAMP_COLNAME => 'TS',
  TARGET_COLNAME => 'FERVOR_INDEX', LABEL_COLNAME => '');

The gotcha that cost an hour: my first version trained on ts < now and
detected on the last hour. Cortex rejected it -
All evaluation timestamps must be after the last timestamp in fitting data.
Training and detection must not overlap. Split both at the same boundary,
strict < on one side, >= on the other.

The is_synthetic flag is the honesty mechanism, and it is load-bearing. A
demo needs a passion spike on camera, so scripts/demo_spike.py injects
labeled rows that detection sees but training never fits. The detector
flags the surge because it genuinely is one - not because it was taught to.

FORECAST - and this one goes on-chain. Every nation's index is projected
12 minutes out. The delta between that forecast and the live index is
momentum - the second field in the oracle payload. So a Snowflake Cortex ML
output is signed onto Solana every five minutes, next to the SQL aggregate it
predicts. The dashboard draws it as a dumbbell (live index → forecast) so you
read who is strengthening and who is cracking in one glance, with an explicit
label that it forecasts holding behavior, not token price.

COMPLETE - prose only. A scheduled task writes hype lines; the AI insights
tab calls Cortex on demand from inside the app - no external API, no key,
the LLM runs where the data lives:

SNOWFLAKE.CORTEX.COMPLETE('mistral-large2',
  'You are a crypto market analyst covering World Cup fan tokens. 3 bullets: '
  || 'one strength, one risk, one outlook. ' || team_name || ' index '
  || fervor_index || '/1000, ' || devoted_wallet_count
  || ' believer wallets, average hold ' || avg_hold_days || ' days.')

Every number in that prompt was computed upstream, in SQL. The model is handed
facts and asked for sentences. It is never asked for arithmetic.

The market tabs render real 1-hour candlesticks built in SQL - no OHLC
API, just MIN_BY/MAX_BY over decoded swap executions:

SELECT TIME_SLICE(s.block_time, 1, 'HOUR')      AS h,
       MIN_BY(s.price_usd, s.block_time)        AS open,
       MAX(s.price_usd)                         AS high,
       MIN(s.price_usd)                         AS low,
       MAX_BY(s.price_usd, s.block_time)        AS close,
       SUM(s.sol_amount)                        AS volume
FROM STAGING.SWAP_EVENTS s
WHERE s.team_id = ? AND s.block_time >= DATEADD('hour', -72, CURRENT_TIMESTAMP())
GROUP BY 1 ORDER BY 1;

And the USD leg is live: the bridge pushes the real SOL/USD rate into
REF.PARAMS every 60 seconds, so the whole DAG's pricing tracks the market.

Stage 5: the write-back - where the trust boundary lives

The security property judges should check: the oracle keypair never touches
Snowflake. The warehouse stages numbers in a queue table and nothing more. A
small Node bridge holds the key, signs, submits, and writes the audit trail
back:

-- warehouse/05_oracle.sql
CREATE OR REPLACE TABLE ORACLE.PUBLISH_QUEUE (
  publish_id   NUMBER IDENTITY,
  team_id      NUMBER, team_name STRING,
  fervor_index NUMBER,
  momentum     NUMBER DEFAULT 0,          -- Cortex forecast minus current index
  status       STRING DEFAULT 'PENDING',  -- PENDING -> SENT -> CONFIRMED | FAILED
  queued_at    TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
  tx_signature STRING, explorer_url STRING, last_error STRING
);

Until the Anchor program is deployed the bridge writes signed Memo
transactions - real, confirmed, explorer-visible. Once FERVOR_PROGRAM_ID is
set it flips to the PDA oracle automatically: one account per country,
init_if_needed, so the first write creates it.

// oracle-program/programs/fervor_oracle/src/lib.rs (excerpt)
#[account]
pub struct FervorAccount {
    pub authority: Pubkey,   // first writer claims the PDA
    pub team_id: u16,
    pub fervor_index: u32,
    pub momentum: i32,
    pub updated_slot: u64,
}

I also built the inbound design - a Snowpark procedure calling the bridge
directly over HTTPS, using an External Access Integration with a SECRET and a
NETWORK RULE. Snowflake trial accounts reject it
(509009: External access is not supported for trial accounts), so the
submission ships the queue-poll design. I verified the other path anyway - an
authenticated POST /publish through a cloudflared tunnel produced a confirmed
devnet transaction - and warehouse/06_external_access.sql runs as-is on a paid
account. Same on-chain result, one fewer moving part.

Here is the oracle's actual output over one evening - every marker a confirmed
devnet transaction:

Stage 6: the dashboard runs inside the warehouse

Streamlit-in-Snowflake, eight tabs (Standings, World Cup, Wallets and
rivalries, Market, Token markets, Head to head, AI insights, On-chain oracle),
national colors with SVG emblems, a rotatable 3D conviction scatter (days held
× dip buys × score, sized by SOL traded), a per-wallet drill-down. Zero
external hosting. The app, the data, the ML and the LLM all live in the same
account - which is also why the oracle needs a bridge at all: the SiS sandbox
cannot reach the public internet.

The live World Cup, joined to on-chain conviction

The dashboard's World Cup tab reads the real 2026 FIFA World Cup: fixtures,
scores with extra-time and penalty-shootout detail, the group tables, and the
live Golden Boot race - pulled from football-data.org and set directly beside
the conviction index.

Same constraint as the oracle, same solution: SiS cannot reach the internet, so
a host-side sync writes the tournament into Snowflake and the app only ever
reads a table.

# scripts/football_sync.py (excerpt) - three endpoints -> three REF tables
matches   = get("/matches",   KEY)   # scores, half-time, extra-time, penalties
standings = get("/standings", KEY)   # group tables: W/D/L, goals for/against, pts
scorers   = get("/scorers",   KEY)   # Golden Boot race: goals + assists
# every row maps the API country name to our REF.TEAM_TOKENS team_id via
# our_team(), so the real tournament joins straight onto on-chain conviction

On the day I wrote this, France knocked Morocco out 2-0 in the quarter-final
and Argentina beat Switzerland 3-1 after extra time, setting up an England vs
Argentina semi-final. Mbappe and Messi were tied on eight.

That is not decoration. It is the join that makes the thesis falsifiable. A
conviction oracle exists to answer exactly one question - did the losing fan
base hold, or capitulate? - and you cannot ask it without a real scoreline on
the other side of the join. The tournament is real today. Point the mints at
real tokens and both halves of that question are answerable at once.

The exact line between real and simplified

Stated at the top; itemised here.

Real and running unattended: the full 7-table Dynamic Tables DAG at
1-minute lag; the conviction scoring above; three Cortex models retraining on a
5-minute task; confirmed devnet transactions for all sixteen nations, one
publish cycle every 5 minutes, each with a clickable Solscan link in
ORACLE.PUBLISH_LOG; the live 2026 FIFA World Cup synced into REF.WC_MATCHES,
REF.WC_STANDINGS and REF.WC_SCORERS and joined to conviction; the 8-tab
dashboard inside Snowflake.

Simplified, with reasons:

Simplification	Why	Honest label
Country token mints are placeholders pending verification	unofficial "national" meme tokens need per-mint liquidity vetting before pointing mainnet ingest at them	`PLACEHOLDER_MINT_*` in `REF.TEAM_TOKENS`; one UPDATE per row to go live, the poller skips placeholders
Demo data seeded	placeholder mints produce no organic feed yet	every seeded row labeled: `_batch_id = 'DEMO_SEED'`, wallets end in `demo`, `is_synthetic` history is excluded from model training, sample oracle rows carry `is_demo = TRUE` and are replaced by real bridge writes
8 s micro-batches, not Snowpipe Streaming SDK	SDK is Java-only	noted in code comments
BUY/SELL from balance deltas, not per-DEX decoding	Helius doesn't parse every venue	self-arb filter compensates
Token prices decoded from swap legs, not a DEX API	the pipeline must work from raw transactions alone	USD via a LIVE SOL/USD rate the bridge refreshes into `REF.PARAMS` every 60 s
Writes go to devnet; bridge is centralized	this is an oracle BRIDGE, not a decentralized oracle network	stated plainly

The seeder injects Helius-shaped transactions upstream, into the raw
landing table, and lets the real DAG compute every downstream number - each
nation with its own volatility, trend and fan-base depth, and a slowly drifting
sentiment so believers capitulate or buy the dip and the index genuinely moves
(which is what makes the Cortex forecast diverge). Nothing in MARTS is
hand-written.

These are unofficial Solana meme tokens named after national teams, not
licensed fan tokens (those live on Chiliz). FERVOR analyzes public on-chain
behavior only.

What this unlocks

The World Cup is the costume. Underneath it is a primitive: a data warehouse
that reads a chain, computes what the chain cannot compute about itself, and
signs the answer back where other programs can consume it - with the key held
in a minimal, auditable bridge instead of the warehouse.

Proof-of-reserve attestations. Risk scores for lending protocols.
Activity-based airdrop eligibility. Sybil scoring. Every one of them is an
"aggregate off-chain, attest on-chain" problem, and every one of them currently
gets solved by a bespoke indexer somebody has to babysit.

They are all TARGET_LAG = '1 minute' and a queue table.

Run it yourself

git clone https://github.com/SoumyaEXE/weekend-challenge
cp .env.example .env             # Helius key, Snowflake creds, devnet keypair
npm install
python scripts/run_sql.py --all  # entire warehouse: schemas, DAG, tasks
python scripts/demo_seed.py      # labeled demo data through the real DAG
npm run bridge                   # queue -> signed devnet writes
python scripts/football_sync.py  # live World Cup -> matches, standings, scorers
python scripts/deploy_streamlit.py

Within about two minutes the DAG refreshes, tasks snapshot history, and the
bridge confirms its first write. npm run publish:once forces a publish for
the impatient. python scripts/demo_spike.py makes the anomaly detector fire on
camera; --clean removes the evidence.

To go live on real trades: fill config/mints.json with verified mints, run
python scripts/set_mints.py (it auto-fetches each token's decimals and logo),
then npm run ingest. Not one line of SQL changes.

Built with @dronzer2code within the challenge window with AI pair-programming. All simplifications and data labeling are documented above and in the README.

Hermes Agent for Developers: The Open Source AI Agent That Learns & Remembers

Soumyadeep Dey — Sat, 30 May 2026 04:28:57 +0000

Why Hermes Agent Is One of the Most Practical Open Source AI Agents for Developers

This is a submission for the Hermes Agent Challenge.

Author: SoumyaEXE

Portfolio: isoumya.xyz

LinkedIn: isoumyadeyy

Introduction

Agent frameworks are everywhere right now, but most of them still feel like demos dressed up as products. They can answer prompts, call a tool or two, and generate nice screenshots for launch posts, yet many fall apart when the task gets longer, messier, or more dependent on memory.

That is why Hermes Agent caught attention. It is not positioned as just another chatbot wrapper or an IDE sidekick. Hermes Agent is designed as an autonomous, self-improving agent that can plan, use tools, build reusable skills, and persist memory across sessions. That combination matters a lot for developers who want something more durable than one-shot prompt orchestration.

This post focuses on the developer angle. Instead of repeating marketing copy, it looks at what Hermes Agent is, why its design stands out, how a developer can start using it, and where it fits in a real workflow. The goal is simple: help other developers decide whether Hermes Agent is worth their time for learning, experimentation, and actual projects.

Why this topic matters now

The timing of this challenge is interesting because the AI tooling landscape is moving from simple assistants toward more persistent systems. The official Hermes Agent Challenge asks writers to publish something that educates, inspires, or sparks discussion around Hermes Agent, and submissions are judged on clarity, originality, practical value, and writing quality [1].

That judging criteria actually says a lot about what kind of post works best. A strong submission should not just praise the tool. It should teach something useful, present a clear point of view, and leave readers with a practical understanding they can apply immediately.

So instead of writing a shallow overview, this article takes a position: Hermes Agent is one of the more practical open source agent systems for developers because it treats memory, skills, tooling, and deployment as first-class parts of the product rather than afterthoughts.

What Hermes Agent actually is

According to the official documentation, Hermes Agent is a self-improving AI agent built by Nous Research, and its core idea is a built-in learning loop that creates skills from experience, improves them during use, persists knowledge, and builds a deeper model across sessions [2].

That description is important because it changes how the agent should be evaluated. Many systems are good at completing a single session well enough. Hermes Agent is trying to become more useful over time. That means the benchmark is not only immediate output quality. The benchmark is whether repeated use produces a smarter, more capable system without forcing the developer to constantly rebuild context from scratch.

In practice, Hermes Agent presents itself as more than a local toy. The docs highlight that it can run across local environments and multiple terminal backends including Docker, SSH, Daytona, Singularity, and Modal, which makes it easier to think of it as infrastructure rather than a single-device assistant [2].

The four ideas that make Hermes Agent stand out

1. Persistent memory is not optional

One reason many agent demos feel brittle is that they start fresh too often. A tool might solve a task today, then forget everything tomorrow. Hermes Agent is built around a memory system that grows across sessions, with cross-session recall listed as a core feature in the official docs [2].

For developers, this matters in obvious ways. If an agent remembers preferred workflows, recurring project context, prior fixes, or known environment quirks, it becomes more valuable with repetition. That turns the system from a novelty into something closer to an assistant that can actually compound usefulness over time.

This is especially relevant for software work because software work is repetitive in structure, even when the tasks themselves differ. A developer often revisits the same repositories, conventions, setup steps, deployment targets, and debugging habits. An agent that remembers these patterns can reduce friction in a way stateless systems simply cannot.

2. Skills turn experience into reusable procedure

Hermes Agent also emphasizes a skills system. The docs describe procedural memory and reusable skills as a major feature, and independent writeups on Hermes highlight that the agent can generate and reuse skill documents after completing complex tasks [2][3].

That is a stronger idea than it sounds at first glance. A lot of AI tooling relies on the user becoming the memory layer. The human has to remember which prompt worked, which sequence of steps got the right result, and which format produced reliable output. Hermes Agent tries to capture some of that procedure itself.

For a developer, this creates an appealing loop:

Solve a task once
Let the agent retain the pattern
Reuse the pattern later with less prompting
Improve the pattern as usage continues

That is one of the most convincing arguments for Hermes Agent. It is not only trying to answer better. It is trying to operationalize experience.

3. Tool use is built into the agent identity

The official documentation positions Hermes Agent as an agent with broad tool access, including web search, browsing, extraction, image generation, TTS, MCP support, and more than 60 built-in tools depending on configuration [2].

This matters because real agentic work is almost never pure text generation. Useful systems need retrieval, execution, inspection, iteration, and output shaping. A good model alone is not enough. Hermes seems to understand that the value of an agent lies in the relationship between reasoning, memory, and tools.

There is also a practical advantage here. Developers do not want a fragile stack where every new capability requires wiring together five more packages. Hermes Agent appears to reduce that setup burden by making tool use part of the default mental model rather than an advanced extension.

4. It is designed to live outside the laptop

One of the better ideas in the Hermes documentation is the insistence that the agent is not tied to a single laptop session. The docs explicitly frame Hermes as something that can run on a cheap VPS, a GPU cluster, or serverless infrastructure, while also connecting through platforms such as Telegram, Discord, Slack, WhatsApp, Teams, and more [2].

That changes the type of projects developers can imagine. Instead of thinking only in terms of a command line assistant opened for a few minutes, it becomes easier to imagine background research agents, message-based task bots, or persistent personal infrastructure that can keep working while the user is away.

A lot of agent products claim flexibility. Hermes Agent makes that flexibility feel architectural.

Why developers should care

The strongest reason to care about Hermes Agent is not hype. It is leverage.

A developer usually does not need an AI tool that looks impressive for thirty seconds. A developer needs a system that can help with repeated workflows, multi-step tasks, context retention, and automation that survives beyond a single prompt window.

Hermes Agent lines up well with that need in several areas:

It supports persistent memory, which makes repeated use more valuable [2]
It supports reusable skills, which can convert successful task patterns into future shortcuts [2][3]
It supports multiple deployment environments, which opens the door to self-hosted and remote workflows [2]
It supports many communication platforms and tool integrations, which makes it easier to bring the agent into real work channels instead of leaving it isolated in a terminal [2]

That combination is what makes Hermes Agent interesting beyond the current agent trend cycle. It is built around continuity.

A practical use case: an automated research pipeline

To make this concrete, consider a small automated research pipeline. This is the sort of project that fits both the challenge spirit and everyday developer needs.

A basic research workflow usually involves the same sequence again and again:

Accept a topic or question
Search for relevant sources
Open and extract content from the strongest pages
Compare claims across sources
Summarize findings in a structured format
Preserve useful patterns for later runs

Hermes Agent is well suited to this type of workflow because it combines tool use, memory, and iterative reasoning. The official docs specifically highlight web search, browsing, extraction, memory, scheduled automations, and subagents for parallel workstreams as supported capabilities [2].

That means a developer can imagine a workflow like this:

Hermes receives a research prompt
It searches the web using integrated tools
It extracts content from relevant sources
It writes a structured summary
It stores notes about the format, source preferences, and common research patterns for future work

The interesting part is not just that Hermes can do each step. It is that the system is designed to improve the workflow over time.

Fast setup path

The official installation flow is refreshingly direct. The docs provide a one-line install script for Linux, macOS, and WSL2, and they recommend hermes setup --portal as the fastest path to a working agent with model access plus tool gateway features such as web search, image generation, TTS, and browser access [2].

Here is a compact example setup flow.

curl -fsSL https://raw.githubusercontent.com/NousResearch/hermes-agent/main/scripts/install.sh | bash
hermes setup --portal

That installation story matters more than people admit. In fast-moving AI tooling, the difference between curiosity and abandonment is often ten minutes of setup friction. Hermes Agent looks stronger here because the onboarding path is designed around getting to a usable system quickly [2].

A simple developer-oriented workflow

Below is a lightweight conceptual setup for a research-focused Hermes workflow. It is intentionally short because the purpose is to show how approachable the structure can be.

agent:
  name: research-agent
  profile: technical-writer
  memory:
    enabled: true
  skills:
    enabled: true

runtime:
  backend: local

models:
  default: portal/default

goals:
  - Search for high-quality sources
  - Extract useful technical details
  - Summarize findings clearly
  - Reuse successful workflows later

And here is the type of prompt that fits Hermes well:

hermes run research-agent --prompt "Research memory-augmented open source AI agents, compare core features, and draft a developer-friendly summary with practical examples."

This type of workflow plays to the platform's strengths. It is not just asking the model to write. It is asking the agent to search, organize, compare, retain, and evolve.

Where Hermes Agent feels stronger than many alternatives

A lot of frameworks in the agent ecosystem are capable. That is worth stating clearly. There are several good options depending on whether someone wants orchestration, workflow control, coding support, or enterprise tooling.

Still, Hermes Agent feels different in a few practical ways.

Area	Why Hermes Agent stands out
Memory	Persistent cross-session memory is positioned as a core feature, not a plugin [2]
Skills	Reusable procedural skills are central to the product identity [2][3]
Deployment	Local, Docker, SSH, Modal, and other backends are documented as supported paths [2]
Communication	Hermes can connect to many messaging platforms, which expands real-world usage options [2]
Learning loop	The built-in self-improvement story is more explicit than in many general-purpose frameworks [2][3]

That does not mean Hermes Agent is universally better. Some developers may still prefer lower-level orchestration frameworks if they want to custom-build every layer. Others may prefer vendor-specific ecosystems if they are deeply invested in a single cloud stack.

But Hermes Agent makes a compelling case for developers who want a system that is usable now, extensible later, and architected around long-term improvement.

What kind of developer should try Hermes Agent

Hermes Agent is especially attractive for a few categories of builders:

Developers who want to self-host or run agents on their own infrastructure [2]
Builders interested in long-running assistants rather than one-session chat tools [2]
People experimenting with research workflows, coding helpers, task bots, or messaging-based agents [2]
Developers who care about reusable procedure, not just prompt output [2][3]
Tinkerers who want an open system with MCP support and portable skills [2]

That last point matters a lot. A framework becomes much more interesting when it lets developers build systems that feel personal and durable. Hermes Agent seems designed for that mode of use.

The trade-offs to think about honestly

A good technical post should not read like promotion. So it is worth talking about trade-offs.

First, a system with memory, tools, multiple backends, and cross-platform integration will naturally be more complex than a simple chat interface. That complexity can be a strength, but it also raises the bar for understanding, debugging, and configuration.

Second, the promise of self-improvement is powerful, but any learning system depends on the quality of the tasks, stored artifacts, and feedback loops around it. In other words, Hermes Agent may create better workflows over time, but that still requires thoughtful usage. Good systems amplify good practice. They do not remove the need for it.

Third, the more ambitious the workflow, the more important observability becomes. If an agent searches, extracts, remembers, and adapts, developers need to inspect what it did and why. That is not a Hermes-only issue. It is a general truth of agent engineering.

These are not reasons to avoid Hermes Agent. They are reasons to approach it like infrastructure rather than magic.

What makes a strong DEV challenge post

Since this article is also a challenge submission, it is worth stepping back and asking what kind of Hermes post is likely to perform well with readers and judges.

The official challenge rules say the write track is judged on clarity and depth of explanation, originality of perspective or insight, practical value to the community, and quality of writing [1]. That means the strongest posts will probably do three things well:

Teach something concrete
Offer a distinct point of view
Stay readable for developers who are curious but not yet invested

That is why Hermes Agent is such a good subject for writing. It naturally supports several strong angles:

A setup guide for local or VPS deployment
A breakdown of the memory and skills model
A comparison against other agent approaches
A case study around a useful workflow like research, automation, or coding assistance
A broader essay on what persistent agents mean for developer tooling

For reach, the best post is usually not the most academic one. It is the one that gives readers enough technical substance to trust the writer while keeping the narrative clear enough that they actually finish it.

Why Hermes Agent is worth watching

There are many open source AI projects that generate excitement for a week and then disappear into a GitHub tab graveyard. Hermes Agent feels more durable because the core ideas behind it map onto real problems developers actually have.

Developers need systems that remember, adapt, and integrate with tools. They need agents that can live outside a browser tab. They need workflows that become easier after the fifth run, not harder.

Hermes Agent is interesting because it is trying to solve those exact problems. The official docs frame it as an autonomous agent with persistent memory, reusable skills, broad tool access, multiple runtime backends, and rich communication options across platforms [2]. That is not a small ambition, but it is the right ambition.

Final thoughts

Hermes Agent stands out because it treats agency as a systems problem, not just a prompting trick. Memory, skills, execution environments, communication channels, and tool access are all part of the same product story [2].

That makes it one of the more practical open source agent projects to study right now. Not because it promises magic, but because it is built around the idea that useful agents should improve with experience, persist across contexts, and fit into the way developers actually work [2][3].

For developers exploring open source agent frameworks in 2026, Hermes Agent looks less like a passing novelty and more like a serious attempt at building software that can keep getting better after the first run [2][3].

Useful links

Challenge page: Hermes Agent Challenge
Official rules: Hermes Agent Challenge Contest Rules [1]
Documentation: Hermes Agent Docs [2]
GitHub: NousResearch/hermes-agent
GitHub profile: SoumyaEXE
Portfolio: isoumya.xyz
LinkedIn: Soumya Dey

Your AI agent has amnesia. You've just normalized it.

Soumyadeep Dey — Sat, 23 May 2026 17:44:06 +0000

Three hackathons in three months. Three different agent setups. Three times I wrote a system prompt that said something like "you are a helpful assistant that can use tools and reason across steps." Three times I shipped something, closed the terminal, and the agent forgot everything it had ever done.

I didn't think of it as a problem. I thought of it as just how agents work.

That's the part worth interrogating.

There's a normalization happening in how we build with AI. We've collectively agreed that agents are stateless, that every session starts cold, that the "memory" problem is either solved by shoving things into a context window or punted to some RAG pipeline you bolt on after the fact. We treat amnesia as a default and then we build around it. Bigger context windows. Better prompts. Smarter retrieval.

But none of that solves the actual problem, which is: the agent isn't getting better at working with you specifically. It's not accumulating judgment. It's not noticing that you always want JSON back, or that your last three API integrations broke on rate limits, or that you prefer terse explanations over verbose ones. Every session, you re-establish who you are. Every session, the agent starts from scratch.

That's not intelligence. That's a very fast amnesiac with a lot of knowledge.

The thing that made me stop and actually think was this line from Hermes Agent's README:

"The self-improving AI agent... it creates skills from experience, improves them during use, nudges itself to persist knowledge, searches its own past conversations, and builds a deepening model of who you are across sessions."

Not "we have a memory feature." Not "long-term context support." Skills from experience. A deepening model of who you are.

That's a different claim. And it's architectural, not cosmetic.

When Hermes completes a task, it can crystallise what it did into a reusable skill, tag it, store it, and pull from it next time a similar problem comes up. The first time you ask it to do something gnarly, it figures it out. By the third time, it's refined the approach. This is not a context window trick. The learning loop is built into the agent itself.

Here's a concrete thing to sit with: imagine you're onboarding a junior developer. Day one, you explain your codebase, your conventions, your preferences. Day two, they remember. Day thirty, they're fast because they've internalized how you work. Now imagine that junior dev reset every morning with zero memory of the previous day. You'd re-onboard them every single time. You'd eventually stop delegating anything non-trivial because the overhead is too high.

That's what most of us are doing with agents right now, and calling it "agentic AI."

Hermes Agent is at least trying to solve the actual problem instead of building better workarounds for it.

The model-agnostic architecture is worth one paragraph because it doesn't get enough attention. Hermes runs on anything: Claude, GPT-4o, Gemini, 200+ models through OpenRouter, your own endpoint. Switch models with hermes model, no code changes. This matters less as a convenience feature and more as a philosophical stance. The model is not the agent. The learning loop, the skill library, the memory system, the planning layer: those are the agent. The model is just the reasoning engine you slot in. When the next frontier model drops in six months and blows the current one out of the water, Hermes users swap it in with one command. Everyone building on a single-model API foundation has to rearchitect.

The infrastructure story also compounds with everything above. You can run this on a $5 VPS. Not "you can try it on a $5 VPS before you upgrade," actually run it there. The serverless option means you're paying essentially nothing when it's idle. You can message it from Telegram while it does work on a cloud machine. That's not a demo feature, it's a deployment story that actually works outside your laptop.

I want to be honest: I haven't run Hermes for six months and watched it compound knowledge into something that feels like a real collaborator. Neither has basically anyone outside the Nous Research team. The learning loop architecture is genuinely interesting but the proof will be in sustained use cases, and those take time to surface.

But here's what I do know: the agents I've built and shipped have all hit the same ceiling. They can reason well within a session. They fall apart across sessions. Every improvement I made was either a bigger context window, a more detailed system prompt, or a retrieval system I had to design and maintain separately. None of it made the agent better at working with me. I was compensating for statelessness, not solving it.

Hermes Agent is trying to solve it. The architecture is open source, the model layer is swappable, and the whole thing runs on infrastructure you control.

In a space where most "agents" are prompt chains with an API call attached, that's worth taking seriously.

[NousResearch/hermes-agent](https://github.com/NousResearch/hermes-agent) on GitHub. Quickstart video is linked in the docs. Worth an afternoon.

Gemma 4 Has Four Variants. Here's How to Pick the Right One Before You Write a Single Line of Code.

Soumyadeep Dey — Sat, 23 May 2026 16:46:16 +0000

This is a submission for the Gemma 4 Challenge: Write About Gemma 4

The single most common mistake developers make when picking a local model is choosing based on benchmark scores. The second most common mistake is choosing based on what fits in VRAM.

Both of those things matter. But neither one is the actual first question.

The actual first question is: where does your model need to live, and what does it need to do there?

Gemma 4 ships in four variants - E2B, E4B, 26B A4B (MoE), and 31B - and Google made very deliberate architectural choices for each one. If you understand those choices, picking the right variant takes about five minutes. If you skip that step and benchmark-shop, you'll end up either underbuilding (a phone-ready E4B doing work that needs 256K context) or overbuilding (a 31B model sitting on $80/month of cloud compute when an E4B running locally would have been fine).

This post is that five-minute decision guide.

What Gemma 4 Actually Is

Released on April 2, 2026 under Apache 2.0, Gemma 4 is Google DeepMind's latest open-weight model family. Every variant ships with multimodal understanding (text + image as baseline, audio natively on the two smallest models), native function calling, and support for over 140 languages.

The headline capability that separates Gemma 4 from previous generations isn't any single feature. It's the intelligence-per-parameter ratio. The 26B MoE model only activates roughly 4B parameters per forward pass. The E4B runs on a phone. The 31B scores 89.2% on AIME 2026 math benchmarks - a score that would have required a model several times larger just a year ago.

The architecture decisions that make this possible:

Alternating local/global attention layers (local layers use sliding windows of 512-1024 tokens, global layers handle long-range context)
Per-Layer Embeddings (PLE) on the edge variants, which keeps the parameter count low while maintaining expressivity
Mixture-of-Experts on the 26B that routes each token through only the relevant expert layers, not the full network

This isn't just efficiency for efficiency's sake. It's what allows a 4-billion-parameter model to run offline on an Android phone with 4GB of RAM while still having a 128K context window. That combination didn't exist before.

The Four Variants, Actually Explained

Gemma 4 E2B - The Phone Model

~2.3B effective parameters, ~5.1B total with PLE, 35 layers, 128K context

This is the model you reach for when the edge is the deployment target. It runs on Android 12+ via Google AICore, on Raspberry Pi, and on Jetson devices. It supports text, image, and audio natively.

The "E" in the name stands for effective - because PLE means the model has more total parameters than it activates per forward pass, similar to how MoE works at a different level of the architecture. The practical result is a 1.5GB footprint with capabilities that land well above what a raw 2B parameter count would suggest.

Use E2B when: you're building a mobile app, an edge inference pipeline, a device-local assistant, or anything where network latency or data privacy makes sending requests to a remote API unacceptable.

Real use case: a receipt-scanning expense tracker that runs fully offline, reads image input, parses line items, and categorizes spending - all on device, no API call, no data leaving the phone.

# Running E2B locally with transformers
from transformers import AutoTokenizer, AutoModelForCausalLM
import torch

model_id = "google/gemma-4-E2B-it"

tokenizer = AutoTokenizer.from_pretrained(model_id)
model = AutoModelForCausalLM.from_pretrained(
    model_id,
    torch_dtype=torch.bfloat16,
    device_map="auto"
)

messages = [
    {
        "role": "user",
        "content": "Extract the total amount and vendor name from this receipt text: ..."
    }
]

inputs = tokenizer.apply_chat_template(
    messages,
    return_tensors="pt",
    return_dict=True
).to(model.device)

with torch.no_grad():
    outputs = model.generate(**inputs, max_new_tokens=256)

response = tokenizer.decode(outputs[0][inputs["input_ids"].shape[1]:], skip_special_tokens=True)
print(response)

Gemma 4 E4B - The Laptop Model

~4.5B effective parameters, ~8B total, 42 layers, 128K context

This is the everyday workhorse for developers who want to run a capable model locally without dedicated GPU hardware. It runs comfortably on a MacBook with 16GB unified memory, on a mid-range laptop with an integrated GPU, and on any machine where you'd rather not spin up a cloud instance.

The jump from E2B to E4B isn't just more parameters. The additional layers and parameter budget give it noticeably better instruction following, more reliable structured output, and stronger performance on tasks that require holding context across a long conversation.

It supports the same text, image, and audio modalities as E2B, which makes it genuinely multimodal in a way that matters for developer tooling - you can feed it screenshots, diagrams, or audio transcripts as part of a pipeline without needing a separate vision model.

Use E4B when: local inference is the requirement, your hardware doesn't have a discrete GPU, or you're prototyping something you'll later scale to a larger model and want fast iteration cycles.

Real use case: a local code review tool that takes a screenshot of your editor alongside the diff, understands both, and gives context-aware feedback - all running on your laptop, no telemetry.

# Quick Ollama setup for E4B (easiest local path)
# After installing Ollama: https://ollama.com

# In terminal:
# ollama pull gemma4:e4b

import ollama

response = ollama.chat(
    model="gemma4:e4b",
    messages=[
        {
            "role": "user",
            "content": "Review this function for edge cases and suggest improvements:",
        }
    ],
    options={
        "temperature": 0.3,
        "num_ctx": 8192  # can go up to 128K
    }
)

print(response["message"]["content"])

Gemma 4 26B A4B (MoE) - The Consumer GPU Model

25.2B total parameters, ~3.8B active per forward pass, ~30 layers, 256K context

This is the one that makes the architecture story interesting. The 26B MoE sounds like it needs 26 billion parameters worth of compute. It doesn't. Only about 4 billion parameters activate for each token, which means it runs on a single RTX 3090 or RTX 4090 at full precision while delivering quality that competes with much larger dense models.

The jump to 256K context window is significant for developers. At 128K you can fit roughly a medium-sized codebase or a very long document. At 256K you're fitting large repositories, multi-document research contexts, or full conversation histories in customer-facing applications.

The MoE architecture also means that quality degrades more gracefully with quantization than a dense model of equivalent total parameters would. INT4 at 26B MoE looks better than INT4 at a comparable dense model.

Use 26B A4B when: you have a consumer GPU (24GB VRAM), need 256K context, and want near-flagship quality without flagship hardware costs. Also the right choice for anything agentic where the model needs to reason across large amounts of context to plan multi-step tasks.

Real use case: an agentic document processor that ingests a full legal contract (or a full codebase) in a single prompt, reasons across the entire document, and extracts structured data or answers specific questions - running locally on a 4090.

# Using the Gemma 4 26B with native function calling
from transformers import AutoTokenizer, AutoModelForCausalLM
import torch
import json

model_id = "google/gemma-4-26B-A4B-it"

tokenizer = AutoTokenizer.from_pretrained(model_id)
model = AutoModelForCausalLM.from_pretrained(
    model_id,
    torch_dtype=torch.bfloat16,
    device_map="auto",
    load_in_4bit=True  # fits on 24GB with 4-bit quant
)

# Native function calling - define your tools
tools = [
    {
        "name": "search_contracts",
        "description": "Search the contract database by clause type or party name",
        "parameters": {
            "type": "object",
            "properties": {
                "query": {"type": "string", "description": "Search query"},
                "clause_type": {
                    "type": "string",
                    "enum": ["liability", "termination", "payment", "IP"],
                    "description": "Type of clause to filter by"
                }
            },
            "required": ["query"]
        }
    }
]

messages = [
    {
        "role": "user",
        "content": "Find all termination clauses across the Q1 vendor contracts and summarize the notice periods."
    }
]

inputs = tokenizer.apply_chat_template(
    messages,
    tools=tools,
    return_tensors="pt",
    return_dict=True
).to(model.device)

outputs = model.generate(**inputs, max_new_tokens=512)
response = tokenizer.decode(outputs[0][inputs["input_ids"].shape[1]:], skip_special_tokens=True)
print(response)

Gemma 4 31B - The Server Model

31 billion dense parameters, 256K context, full multimodal, thinking mode

This is the flagship. Every capability available in the family is present here. Thinking mode (chain-of-thought reasoning) is enabled. Math benchmark scores are serious: 89.2% on AIME 2026, compared to Gemma 3 27B's 20.8% on the same benchmark. It sits at #3 on the Arena open model leaderboard.

It requires ~20GB VRAM at FP16, or ~12GB with INT4 quantization. A single A100 80GB handles it comfortably at full precision. Two RTX 4090s with tensor parallelism also work. This is the model you deploy to a server, not run on a laptop.

Use 31B when: benchmark quality matters for your application, you need thinking mode for reasoning-heavy tasks, you're building a production service that will handle requests from multiple users, or you need the best math and coding performance available in an open-weight model.

Real use case: a coding assistant API that developers on your team query through a self-hosted endpoint - one 31B instance serving your whole engineering org at a cost that's a fraction of equivalent proprietary API calls.

# Serving 31B with vLLM for production throughput
# pip install vllm

from vllm import LLM, SamplingParams

llm = LLM(
    model="google/gemma-4-31B-it",
    tensor_parallel_size=2,   # across 2x RTX 4090
    dtype="bfloat16",
    max_model_len=65536        # 64K for production balance
)

sampling_params = SamplingParams(
    temperature=0.2,
    top_p=0.9,
    max_tokens=2048
)

# Thinking mode for complex reasoning
prompts = [
    "<start_of_turn>user\nThink step by step: Given this algorithm, what's the worst-case time complexity and where is the bottleneck?\n\n[your code here]\n<end_of_turn>\n<start_of_turn>model\n"
]

outputs = llm.generate(prompts, sampling_params)
for output in outputs:
    print(output.outputs[0].text)

The Decision Matrix

Here's the five-minute version:

Situation	Model
Mobile app, Raspberry Pi, offline-first	E2B
Laptop development, no GPU, fast iteration	E4B
Consumer GPU (24GB), 256K context needed	26B A4B MoE
Server deployment, best quality, team-serving	31B
Agentic pipeline with many tool calls	26B A4B MoE (active param efficiency)
Math, coding, or reasoning-heavy production	31B
Privacy-sensitive user data, no API calls	E4B or E2B
You have an A100 and want the best	31B

The Bigger Thing Happening Here

I want to step back from the specs for a second.

A model that scores 89.2% on a serious math benchmark, supports 256K context, runs multimodal inference, and has native function calling for agentic tasks... is now open-weight, Apache 2.0, and runs on hardware that a developer can actually own.

The E4B running on a laptop with 128K context and audio support isn't a "small model compromise." It's a capability that would have been frontier-level two years ago. The E2B running on a phone offline isn't a demo trick. It's a production-viable deployment target.

What that actually means is that the architectural question of "cloud or local?" is no longer primarily a capability question. It's a cost, latency, and privacy question. And for a lot of applications - the ones where user data is sensitive, where offline availability matters, where API costs compound at scale - local wins.

Gemma 4 doesn't make that argument. It just makes it very hard to argue against.

Getting Started in Under 5 Minutes

The fastest path to running any Gemma 4 variant locally is Ollama:

# Install Ollama (macOS/Linux)
curl -fsSL https://ollama.com/install.sh | sh

# Pull the variant you want
ollama pull gemma4:e4b     # ~5GB, laptop-ready
ollama pull gemma4:26b     # ~15GB, GPU-ready

# Run it
ollama run gemma4:e4b

# Or use the API directly
curl http://localhost:11434/api/chat -d '{
  "model": "gemma4:e4b",
  "messages": [
    { "role": "user", "content": "Hello, what can you do?" }
  ]
}'

If you want Python with the full transformers ecosystem (function calling, thinking mode, multimodal), the Hugging Face model cards for each variant have complete working examples. Start with google/gemma-4-E4B-it - it's the most accessible entry point and covers most development use cases.

Quick Note on Licensing

Apache 2.0 means you can use Gemma 4 commercially, modify the weights, build products on top of it, and distribute your derivative work - without paying royalties or asking permission. That is not the case for every "open" model out there, and it matters a lot for anyone building a business on top of local inference.

The right Gemma 4 variant is the one that runs where your users are, fits the hardware you can actually provision, and has enough context to do the task you're designing for. Everything else is optimization.

Start with E4B if you're unsure. Scale up when the task demands it.

Tags: devchallenge gemmachallenge gemma ai machinelearning python opensource

What Is WebMCP? The Google I/O 2026 Web Standard That Changes AI Agent Tool Use

Soumyadeep Dey — Sat, 23 May 2026 16:39:52 +0000

A first-look at the most underreported thing to come out of Google I/O 2026

I was watching the Google I/O 2026 Developer Keynote on my laptop at 11:30 PM with a cup of cold chai. Gemini 3.5 Flash landed. Antigravity 2.0 got a standing ovation. The smart glasses got the screenshots. Everyone was talking about Gemini Omni.

And then, buried between slide 40 and a code snippet, a presenter said the words "WebMCP."

I sat up straight. I replayed that section three times.

Here is why.

A little context on what I'm building

For the past few months I've been working on a sales platform that stitches together an omnichannel inbox (Chatwoot) with a generative UI layer inside chat. The core problem is this: sales agents need to close deals inside conversations, not by jumping between five tabs. I want the chat widget itself to become the deal-closing surface - surfacing interactive UI components, pulling CRM context, letting a buyer sign a proposal inside the chat thread.

The hardest unsolved piece of that puzzle has always been: how do you let an AI agent do something meaningful on a webpage without either (a) screen-scraping like it's 2019, or (b) building a bespoke API integration for every single surface it needs to touch?

WebMCP is Google's answer to that question. And it's a good one.

What WebMCP Actually Is

WebMCP is a proposed open web standard that lets developers annotate their JavaScript functions and HTML forms so that browser-based AI agents can call them directly as structured tools - with the same reliability you'd expect from a typed API, not from a model guessing where to click.

Think of it as MCP, but for the open web. Regular MCP handles the vertical connections: agent to database, agent to file system, agent to third-party API. WebMCP handles the horizontal layer: agent to the website sitting in front of you right now.

The origin trial starts in Chrome 149. Here is what the developer-side contract looks like conceptually:

// You annotate your JS functions with WebMCP metadata
// so agents know what they can call, what inputs they need,
// and what effect they'll produce

const webmcpManifest = {
  tools: [
    {
      name: "schedule_demo",
      description: "Books a product demo for a qualified lead",
      parameters: {
        name: { type: "string", required: true },
        email: { type: "string", required: true },
        preferred_slot: { type: "string", enum: ["morning", "afternoon", "evening"] }
      },
      handler: scheduleDemo
    },
    {
      name: "generate_proposal",
      description: "Creates a personalized pricing proposal and returns a preview URL",
      parameters: {
        company_name: { type: "string", required: true },
        seats: { type: "number", required: true },
        plan: { type: "string", enum: ["starter", "growth", "enterprise"] }
      },
      handler: generateProposal
    }
  ]
};

// Register once. Any WebMCP-capable agent can now call these
// without scraping your DOM or guessing your form structure.
window.__webmcp__ = webmcpManifest;

The agent on the other end - whether it's Gemini in Chrome, an Antigravity subagent, or your own custom orchestrator - now has a reliable, developer-defined surface to work with. No brittle CSS selectors. No "find the button that looks like it submits the form." Just a typed function call.

Why This Matters More Than It Looks

Here is the frame I keep coming back to: every website becomes a tool.

Right now, when you build an AI agent that needs to do anything on the web, you have two bad options and one expensive option.

Bad option 1 is prompt the model to act like a browser automation script. You're essentially doing Playwright-via-LLM, which works until a div gets renamed and your entire pipeline breaks silently at 2 AM.

Bad option 2 is write a bespoke connector for every website or service you want the agent to interact with. Fine for the five services you use every day. Not fine for the long tail of things your users will eventually ask your agent to handle.

The expensive option is to build everything in-house, behind your own API, so the agent only ever talks to surfaces you control. This is what most serious teams do. It scales, but it costs months.

WebMCP opens a fourth path: the website author defines the agent interface themselves, once, as part of their normal development work. You annotate the functions that make sense to expose. You describe what they do. You ship it as part of your site. From that point forward, any agent with WebMCP support can call those functions correctly - without you writing a new connector, and without the agent guessing.

The protocol also composes cleanly with the rest of the stack Google laid out this year:

MCP handles agent-to-infrastructure connections (databases, APIs, file systems)
A2A handles agent-to-agent coordination across vendors
WebMCP handles agent-to-website interaction in the browser

Three protocols, three layers, one coherent answer to "how does an agent actually do things in the real world?"

The Angle Nobody Is Talking About

Most I/O coverage framed WebMCP as a tool for making AI assistants more useful to end users. Fair. But I think the more interesting frame is what it does for developers building agentic products.

If I'm building the sales platform I described above, WebMCP means that instead of my agentic layer needing to "know" how to use each tool on the page through brittle DOM inspection, I can define those tools explicitly as part of my product. My checkout flow, my proposal generator, my slot-booking widget - they're all first-class agent interfaces the moment I annotate them.

The generative UI layer I was building with Tambo (the agentic React UI toolkit) suddenly has a much cleaner answer to "how does the agent actually trigger actions?" instead of "it passes props and hopes for the best." The agent calls a WebMCP-registered tool. The tool fires a handler. The handler updates state. The UI responds.

That is a clean, auditable, testable loop. That matters when you are building something real, not a demo.

What I'm Watching Next

A few things I'm paying close attention to as WebMCP moves through the origin trial:

Security boundaries. Right now the spec assumes the website author is intentional about what they expose. But as soon as agent-browsing becomes mainstream, you'll see adversarial cases: pages that register fake tools designed to manipulate agents into taking actions users didn't intend. The security model around what a WebMCP tool is "allowed" to do on behalf of a user needs to be tight before this goes anywhere near financial or identity workflows.

Cross-browser adoption. This is proposed by Google and Microsoft engineers under the W3C Web Machine Learning community group - which is a good sign for eventual cross-browser support. But Chrome 149 origin trials don't mean it's in Firefox next quarter. For developers building agent-facing products today, you'll need fallback strategies for a while.

Standardization lag vs. tooling speed. The web standards process moves on a timescale that AI tooling doesn't. By the time WebMCP is a full W3C Recommendation, the agent landscape will have changed dramatically. Google's bet here is that shipping an origin trial fast and getting real-world feedback is worth more than waiting for perfect committee consensus. I think that bet is correct.

The Bigger Shift Underneath All of It

There was a line in the keynote that I keep turning over in my head: "We've transitioned from AI that simply assists you, to agents that can independently navigate complex tasks across your entire workflow."

That is a product vision, not a product feature. And WebMCP is one of the infrastructure pieces that makes that vision non-fictional.

The assistive AI era had a ceiling: the model could help you think, draft, and plan, but doing required a human in the loop who could actually click the button, fill the form, and trigger the action. The agentic era removes that ceiling - but only if agents can interact with the web in a way that's reliable enough to trust in production.

WebMCP is a small, well-scoped proposal. It doesn't solve everything. But it solves the exact right problem at the exact right layer: giving website developers a first-class way to say "here is what an agent is allowed to do on this page, and here is how to do it correctly."

For anyone building agentic products on the web, that is not a quiet announcement. That is the foundation you were waiting for.

If you're building something that sits at the intersection of AI agents and web UIs, I'd genuinely love to compare notes. Drop a comment or find me at my usual haunts.

Tags: googleiochallenge webdev ai javascript webstandards

I built a fully interactive 3D Solar System you can explore right from your browser.

Soumyadeep Dey — Sat, 16 Aug 2025 13:55:40 +0000

🪐 Explore the Cosmos from Your Browser

I’m stoked to share my recent project: 3D Solar System Simulation, crafted with Three.js and accelerated with Vite! Tap into the wonders of our cosmic neighborhood through your screen—no telescopes needed.

🔗 Check out the live project below:

https://3d-solar-system-three-js.vercel.app/

📸 Gallery Snapshot

💡 Why I Built It

To visualize planetary orbits, rotations, and atmospheres in real-time
To flex the power of Three.js combined with modern tooling like Vite
To experiment with interactive controls and responsive design
To have a fun showcase for learning and collaboration

🚀 Project Highlights

Realistic 3D models of the Sun, planets, dwarf planets, and moons
Smooth orbit animations combined with planet rotations
Scaled visuals for better clarity and navigation
Full interaction via mouse: orbit, zoom, and pan
Modern tooling with Vite for blazing-fast development
Mobile-friendly interface with responsive scaling

🛠 Behind the Scenes

3d-Solar-System-ThreeJS/
├── public/
│   └── textures/             # Planet & celestial textures
├── src/
│   ├── main.js               # Core rendering logic
│   └── additional modules... # Planet classes, UI controls, etc.
├── index.html
├── package.json
├── vite.config.js
└── README.md

⚡ Installation & Local Run

Clone and run locally:

git clone https://github.com/SoumyaEXE/3d-Solar-System-ThreeJS.git
cd 3d-solar-system-three-js
npm install
npm run dev

Then visit

http://localhost:5173

To build a production version:

npm run build

📚 What I Learned

Skill	Takeaway
Three.js fundamentals	Scene setup, camera controls, mesh rendering
Animation techniques	Smooth orbits & rotations using `requestAnimationFrame`
Performance tuning	Optimized for smoother animations with Vite
Responsive design	UI/UIX that works seamlessly across devices

🌌 Join the Journey

I’d love your thoughts! Let me know:

What features you’d like added next
How the scaling feels on mobile
Any performance hiccups you spotted

Don’t forget to ⭐ the repo if you enjoy the simulation — and open an issue or PR if you want to contribute!

🔗 Related Projects & Resources

Three.js Examples — official showcase
Three.js Documentation — API deep dive
Vite — next-gen frontend tooling

Thanks for exploring with me!
— Soumya

What Is Vibe Coding? How To Do It In 2025

Soumyadeep Dey — Sat, 12 Jul 2025 07:00:25 +0000

🧑‍💻 Follow me on GitHub for more experiments, tools, and guides: github.com/SoumyaEXE

What if you could skip the boilerplate and just say what you want your app to do?

That’s vibe coding—a 2025 workflow where you build web apps, prototypes, and backend APIs using plain English. Whether you’re a pro developer tired of repetitive tasks or a beginner with big ideas, vibe coding changes how you ship.

This article breaks down what vibe coding is, how it works, tools to try, and how GitHub plays a major role in your AI-powered workflow.

🔍 What Is Vibe Coding?

Vibe coding is building software by describing your app, component, or logic in natural language. Tools like Cursor, Claude Code, or Replit AI translate your intent into code. GitHub Copilot and Vercel’s AI SDKs are integrating this even deeper into production-ready workflows.

🧠 “The hottest new programming language is English.” — Andrej Karpathy

Vibe coding isn't just code generation—it's iteration + intention + automation.

⚡ Why Developers (and Non-Devs) Love It

🧑‍🎓 Beginners: Build apps with zero syntax knowledge
💼 Professionals: Skip boilerplate, test logic fast
⏱️ Everyone: Launch MVPs in hours, not weeks

🛠️ Tools for Vibe Coding in 2025

Tool	What it’s good for	GitHub Integration
Cursor	Code generation in VS Code	✅ Commits + Copilot sync
Claude Code	Complex structured logic via prompts	❌ Manual export
Replit AI	Full-stack in-browser development	✅ GitHub sync
Vercel AI SDK	Integrate AI responses in Next.js apps	✅ Native
Postman AI	API testing & backend autogen	✅ Push collections

Want GitHub commits that make sense? Cursor and Replit are great for AI-generated commits, clean histories, and Copilot X preview features.

✍️ How to Vibe Code

1. Write Specific Prompts

Bad:

Make a website

Good:

Build a dark-themed Next.js blog with a subscribe form and Markdown support.

Even better:

Create a TypeScript-based Next.js 14 app with Tailwind CSS. Add a newsletter form using Vercel’s Edge Functions and connect to a Notion DB.

2. Test, Commit, Repeat

Once the tool generates the code:

Run locally (npm run dev)
Test features and endpoints
Fix bugs via prompts
Auto-commit to GitHub

git add .
git commit -m "✨ AI-generated landing page with Tailwind and form handling"
git push

AI even suggests commits now. Just review and approve.

3. Use GitHub for Versioning + Deploying

Every good AI project needs version control. Here's a simple Vercel-integrated flow:

vercel init
vercel --prod

Want preview links after every push? GitHub Actions + Vercel CI does it for you.

🚨 Pitfalls to Avoid

Problem	Fix
Vague prompts	Be specific with structure + tech
Wrong framework	Always mention your stack
Code doesn't run	Use Replit or local dev server
AI bloats the code	Refactor manually or with Copilot
Debugging is messy	Break into smaller prompts

🧪 Example Vibe Prompt (Backend + DB)

“Create an Express.js REST API with a /books route, connect it to a SQLite DB, and deploy it with Vercel Serverless Functions.”

⬇️ AI Output:

/api/books.js with CRUD routes
db.sqlite pre-seeded with sample data
vercel.json configured
.env support added

All ready to deploy + preview live via GitHub commits.

🔮 What’s Next?

AI-driven workflows are becoming GitHub-native.

Copilot Workspace is in preview
GitHub Actions trigger based on AI-generated code
aidd.io is launching something major on July 15, 2025

Get ready for AI-powered commit messages, automated PR descriptions, and test coverage reports—all from natural language.

💬 Final Thoughts

Vibe coding is a real shift in how we build. You still need logic and structure, but AI handles the scaffolding.

✅ Start small

✅ Be specific

✅ Use GitHub for backup and versioning

✅ Iterate fast

Want to ship ideas without the grind? Vibe coding might be your new workflow.

🔗 Stay in the Loop

Follow me on GitHub: SoumyaEXE
Leave a ❤️ if you vibe with this
Share your AI-generated repos in the comments!

See you on the other side of the commit log 🚀

Top 10 Must-Know GitHub Repositories for Backend Developers (2025) 🔥

Soumyadeep Dey — Sat, 12 Jul 2025 06:43:05 +0000

As a backend developer, mastering the right tools and libraries can significantly improve your workflow, scalability, and project maintainability. Here’s a concise list of 10 highly useful GitHub repositories tailored for backend developers in 2025.

1. expressjs/express

Minimalist Web Framework for Node.js

Express is a fast, unopinionated, and widely-used web framework for building APIs and server-side applications.

const express = require('express');
const app = express();

app.get('/', (req, res) => {
  res.send('Hello Backend World!');
});

app.listen(3000, () => console.log('Server running'));

✅ Lightweight and flexible
✅ Robust routing
✅ Middleware support

2. typeorm/typeorm

ORM for TypeScript and JavaScript

TypeORM allows you to interact with SQL databases using an object-oriented approach.

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;
}

✅ Supports multiple DB engines (PostgreSQL, MySQL, etc.)
✅ Migration and CLI support
✅ Decorator-based modeling

3. prisma/prisma

Next-Generation Type-Safe ORM

Prisma offers a powerful and type-safe interface for querying your database using auto-generated TypeScript types.

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  posts Post[]
}

✅ Type safety for queries
✅ Built-in migrations
✅ Great developer experience

4. nestjs/nest

Progressive Framework for Building Scalable Server-Side Apps

NestJS uses modern JavaScript, TypeScript, and modular architecture inspired by Angular.

@Controller('users')
export class UsersController {
  @Get()
  findAll(): string {
    return 'This returns all users';
  }
}

✅ Dependency injection
✅ Modular architecture
✅ Ideal for microservices and enterprise apps

5. docker/awesome-compose

Collection of Docker Compose Examples

This repository provides practical and production-ready Docker Compose templates.

services:
  backend:
    build: .
    ports:
      - 3000:3000
  db:
    image: postgres
    environment:
      POSTGRES_PASSWORD: example

✅ Quick environment setup
✅ Great for learning and deployment
✅ Covers common backend stacks

6. sql-js/sql.js

SQLite Compiled to JavaScript

SQL.js allows you to run a full SQLite database in the browser or Node.js.

const SQL = require('sql.js');
const db = new SQL.Database();
db.run("CREATE TABLE test (col1, col2);");

✅ No external dependencies
✅ Perfect for testing and learning SQL
✅ Use in web apps or sandboxed environments

7. kamranahmedse/developer-roadmap

Visual Learning Path for Developers

This roadmap outlines the core skills and technologies needed to become a backend developer.

✅ Covers languages, tools, protocols, DBs
✅ Updated regularly
✅ Community-driven and beginner-friendly

8. elastic/elasticsearch

Search and Analytics Engine

Elasticsearch is used for full-text search, data analysis, logging, and monitoring.

curl -X GET "localhost:9200/_cat/indices?v"

✅ Distributed and scalable
✅ Powerful querying and filtering
✅ Widely used with Logstash, Kibana

9. auth0/node-jsonwebtoken

JWT Authentication for Node.js

A simple and robust library for creating and verifying JSON Web Tokens.

const jwt = require('jsonwebtoken');
const token = jwt.sign({ userId: 123 }, 'your-secret', { expiresIn: '1h' });

✅ Stateless authentication
✅ Supports expiration and claims
✅ Easy to integrate into APIs

10. fastify/fastify

High-Performance Node.js Web Framework

Fastify is designed for high throughput and low overhead.

const fastify = require('fastify')();

fastify.get('/', async (request, reply) => {
  return { hello: 'world' };
});

fastify.listen({ port: 3000 });

✅ Schema-based validation
✅ Highly extensible plugin system
✅ Blazing fast performance

✅ Final Thoughts

These repositories are more than just popular — they provide the backbone for robust, scalable, and efficient backend development.

Use Express or Fastify for APIs
Manage data using Prisma or TypeORM
Secure your backend with JWT
Containerize with Docker
Monitor with Elasticsearch

Explore these repos, contribute to them, and use them in real-world projects to become a more effective backend developer in 2025.

📌 Stay Connected

Follow me on GitHub
Bookmark this article for reference
Leave a ❤️ if you found it helpful

Happy building! 🚀

What's the most challenging coding problem you've encountered, and how did you overcome it? 😮

Soumyadeep Dey — Sun, 03 Sep 2023 14:42:25 +0000

So What Was The Biggest Problem You've Encountered While Doing Coding? 😶

Let Everybody Know In Comments! 🤔❤

(Hit a Follow To Me ~ Its a Small Promotion 🤣✋🏻)