Case file
An inbound lead scoring webhook. The caller fires once, reads nothing and never retries, so every node has to produce a usable answer when the thing it depends on is gone, and say so in the response.
The constraint that shaped everything
The caller is a contact form on someone's marketing site. It fires once, reads nothing, and retries never. A 500 from this service is a lead that no longer exists.
That single fact decides the architecture. Every node has to produce a usable answer even when the thing it depends on is unavailable, which is easy to say and quietly hard to hold: the tempting failure mode is not a crash but a silent downgrade, where the service keeps answering 200 while the intelligence behind the answer has been gone for weeks. So the second rule follows from the first. Degradation is allowed, but it has to be visible in the response body, not just in a log nobody reads.
Two fields carry that. scored_by says whether the score came from the model or
the keyword fallback, and logged_to_sheet says whether the row reached the
spreadsheet. A caller that ignores them still gets a valid score. A caller that
reads them can tell a healthy pipeline from a limping one without access to the
host.
Four nodes
1. Intake. POST /webhook/lead takes name, email, company and message. A
name and a syntactically valid email are required; the other two default to
empty. Validation errors are rewritten into a 422 that names the offending
fields rather than returning a schema dump.
On bad input: 422, and the request never reaches the model or the sheet.
2. Scoring. Gemini is called at temperature 0.2 with a sales-triage system prompt and a JSON response schema, so the model returns a score, a tag and a one-line reason citing the signal that drove it. Whatever comes back is clamped: an out-of-range score is pulled into 1 to 10, and a missing or invalid tag is re-derived from the score rather than throwing the answer away.
On no key, timeout, quota, safety block or unparseable text: a deterministic keyword heuristic scores it instead, and
scored_byreadsfallback.
3. Storage. One row per lead, appended through a service account. The header row is written on first contact with an empty sheet. gspread is synchronous, so every call is pushed onto a worker thread rather than blocking the event loop.
On an append failure: one retry with a fresh connection, then the row is dropped, logged at ERROR, and reported as
logged_to_sheet: false. Never a 500.
4. Response. Always 200 for a valid payload. Score, tag, reason, the two honesty flags, and a UTC timestamp. Each request carries an eight-character id that links its intake, scoring and storage log lines, so one lead can be followed through the pipeline after the fact.
The taxonomy
Three bands, defined in the prompt and enforced after it. The model is told to weigh the message body most heavily, and told explicitly that a free-mail address with no company name is a mild negative signal rather than a disqualification, because the opposite is the obvious way to build a scorer that rejects sole traders.
| Band | Score | Meaning |
|---|---|---|
| Hot | 8 to 10 | Clear need, urgency, and a budget or buying-authority signal |
| Warm | 5 to 7 | Genuine interest, but vague on timeline, budget or fit |
| Cold | 1 to 4 | Spam, job hunting, vendor pitches, students, no real need |
POST /webhook/lead
{"score": 9, "tag": "hot", "reason": "Approved budget and a two-week start date.",
"logged_to_sheet": true, "scored_by": "gemini",
"timestamp": "2026-08-14T09:31:07+00:00"}
The fallback that runs when the model is unreachable is not an approximation of the model. It is 18 buying-signal phrases, 14 solicitation patterns and 10 free-mail domains, scored arithmetically and labelled as such in the reason string, so nobody reads a heuristic verdict as a judgement.
Two things the live API taught me
The pipeline was pinned to a named Flash model, and it ran in fallback
permanently. The key was valid and the code was fine. gemini-1.5-flash,
gemini-2.0-flash and gemini-2.5-flash all return 404 on a newly issued key:
retired, or no longer offered to new users. What makes this worth writing down is
that GET /v1beta/models still lists models that generateContent then refuses,
so the discovery endpoint cannot be trusted to tell you what you can actually
call.
Every candidate was then confirmed with a real call against three fixture leads rather than taken from the list:
| Model | Success | Latency |
|---|---|---|
gemini-flash-lite-latest |
3/3 | ~0.8 s |
gemini-3.5-flash-lite |
3/3 | ~1 s |
gemini-3.1-flash-lite |
3/3 | ~3 to 6 s |
gemini-3.7-flash |
1/3 | 8.6 s, then 429 |
gemini-flash-latest |
2/3 | 12 s, then 429 |
All four lite models agreed on the tags, and the heavy ones were slower than a
webhook should be while exhausting free-tier quota. Lead scoring is a short
classification, so the cheapest model that agrees with the expensive ones is the
right one. The default is the -latest alias rather than a pinned version, since
pinned versions are precisely what got retired above.
A 256-token output budget was generous for a three-field JSON object, and it had
been fine. Current models spend output tokens on internal reasoning first:
roughly 249 of the 256 went to thinking, the JSON came back truncated with
finish_reason=MAX_TOKENS, and the parser fell through to the heuristic.
This is the exact failure the design was supposed to prevent. The service
answered 200, the scores looked plausible, and the only symptom was a field most
callers never read. The budget is now 2048, and more usefully the response reader
logs an error naming the variable when it sees that finish reason, so the next
occurrence is diagnosable in one line rather than inferred from a pattern of
suspiciously round scores. Turning the thinking off is not available: the legacy
google-generativeai SDK rejects the config field.
Where it stands
The pipeline works end to end against a live key and a real spreadsheet. What it does not have is as worth stating.
| Component | State | Notes |
|---|---|---|
| Intake and validation | verified | Typed schema, readable 422, no partial writes |
| Gemini scoring | verified | Model choice measured against a live key, not assumed |
| Fallback heuristic | verified | Deterministic, and labelled in the response |
| Sheets append | verified | Threaded, retried once, degrades to a flag |
| Automated tests | none | Verified by hand against fixture leads. The scorer's clamping and the credential parser are the two obvious first suites |
| Webhook auth | not built | Fine behind a form backend you control. A public URL can be spammed into the sheet and the model quota |
| Formula escaping | partial | The message field is neutralised before it reaches the sheet; the other text fields are not yet |
None of those three is hard. They are listed because a service that accepts anonymous input and writes it into a spreadsheet the sales team opens every morning should be honest about its edges, and because the whole design argument above is that invisible degradation is the thing worth engineering against. A gap nobody wrote down is the same problem in a different coat.