California publishes each stage of its own buying cycle in a different place, and never joins them up. This is what it took to join them, and to make the result answer questions.
Skip to the engineering detail ↓The date on the record is a delivery date. The contract term is buried in free text, in a format no schema describes. 62% of dated purchase orders look like this. Representative record; field structure and term syntax are as they appear in the source data.
Scaled from a nine-department, $17.2B pilot to full statewide coverage, and from a single-source purchase archive to seven joined sources that track a dollar from budget request through project approval and live solicitation to purchase order and paid ledger line. Filings run 2007 through 2026, at full density from 2016. Every purchase order carries a category label. Zero duplicates.
Any vendor or reseller selling infrastructure into a state agency wants to know three things: what does this department already own, when does it come up for renewal, and who currently sells to them. California publishes the data that answers all three. None of it is usable as shipped. Two incompatible export formats arrive under the same file extension. There is no product taxonomy. Contract terms live in free-text line descriptions. The same supplier is spelled three different ways in one file.
Solving that was version one. The harder problem turned out to be timing. A purchase order is the record of a decision that was already made, often years earlier. A system that reads only purchase orders arrives after every conversation that could have changed the outcome.
What was built.
California publishes each stage of its own buying cycle in a different place. A budget change proposal says a department asked for money. A CDT project record says the spend got designed and approved. A live solicitation says bids are open. A purchase order says it was finally spent, typically years later, and by then the decision is long made. And the state ledger says what was actually paid, which is not the same list.
Joined on the four-digit state organization code, those become a demand chain. Reading them together tells you where money is moving before it becomes a purchase order, which is the only point at which a vendor conversation can still change the outcome. The ledger at the end catches the relationships the purchase record cannot see: money routed through a statewide vehicle pays out of a department’s books without ever generating that department’s purchase order.
The approval tracker is the least obvious source and the most defensible one. It publishes current state, not history. Snapshotting it weekly is what makes transitions observable, and the loudest signal in the whole chain is a project leaving the list, which usually means implementation was approved and the department is about to buy. That event exists in no single fetch of the page. It only exists in the series.
| Source | What it is | Volume |
|---|---|---|
| eSCPRS / Cal eProcure | Purchase orders. What was actually bought. | 267,267 POs 957,847 lines |
| DOF Budget Change Proposals | Budget requests. Money asked for, not yet spent. Five fiscal years, one row per initiative so a project asking five years running reads as five years of asking, not five opportunities. | 1,574 PDFs 8,106 observations 1,551 cost extractions |
| CDT PAL / IPOR | Project pipeline. Approval stages with Red, Yellow, Green ratings, snapshotted weekly so stage transitions and list departures are observable. | 176 PAL + 104 IPOR observations, and growing |
| Cal eProcure live solicitations | Bids open right now. The funnel’s missing middle. A solicitation closing means bids are in. | 373 events weekly series |
| Open FI$Cal vendor payments | The ledger beside the paper. What was paid, which catches vehicle-routed relationships that generate no departmental purchase order. | 433,153 payments |
| DGS statewide awards | Contract vehicles. Who is allowed to sell to whom. | 94 awards 72 vehicles 435 ETC evaluations |
| CalCareers workforce signals | What departments say they run, in their own job postings. A recurring monthly feed with per-posting dwell time, because a req still open after 90 days is a department that tried to hire and could not. | 118 signals monthly cadence |
Supporting: 29 vendor lifecycle records (end-of-sale and end-of-support), each carrying the public URL it was verified against.
The first deliverable was a file. A strategy document, generated, sent, and immediately beginning to go stale in someone’s inbox. That was the wrong container, and the reason is a sales problem rather than a technical one: the moment a salesperson learns something in the field, the document is wrong, and there is nowhere to put the correction.
The product is now the Account Runbook. One runbook is one California department, assigned to one named salesperson, served behind per-user identity, with an advisor inside it that answers only from that department’s own filings.
Three mechanisms make it different from the file it replaced. The advisor refuses rather than estimates, and when it cannot answer, the refusal is written to an event log — the gap between what users ask and what the corpus holds is the highest-quality roadmap input available. A revision loop lets a salesperson say what they heard on a call; the ranked play rewrites itself against the same evidence rules the document was generated under, re-tiered honestly in both directions, with the field statement stored beside what changed. And a flag control on every fact gives errors somewhere to go, because a reader who finds a mistake and has nowhere to report it stops trusting the whole document silently.
Generation stays local: the Python pipeline emits a validated JSON snapshot per department. Serving is Cloudflare — Pages, Pages Functions, D1, R2, and Zero Trust Access. Nothing generates in production, so there is no pipeline to keep alive and no 3am failure mode. The publish command pushes finished artifacts up and then verifies the live result rather than trusting its own exit code.
The same snapshot JSON both renders the page and grounds the advisor, so the two cannot drift apart and tell a customer different things. And a tenant_id sits on every row and every query, including read-only ones, from the first commit — because customer two may be customer one’s competitor, and retrofitting isolation later costs a rewrite.
SQLite and server-rendered HTML are deliberate. Generation is one analyst’s tool against a single-file dataset with no concurrent writers, and Postgres with a React front end would be resume-driven development for that job.
The constraint that would once have justified them — row-level isolation between paying clients — now exists, and it was met without abandoning either choice. SQLite locally, D1 in production: same shape, same query language, nothing to learn twice. Isolation lives in the serving layer, where it belongs, rather than in a rewrite of the pipeline that never needed it.
Everything above is what it does. Everything below is how it works, written for people who build these systems.
The stages run most-authoritative-first. Each one only sees what the previous stage could not resolve.
| Stage | Method | Resolved | Marginal cost |
|---|---|---|---|
| 1 | Keyword rules on PO title and line-item text | 65.0% | zero |
| 2 | UNSPSC, the State’s own product codes | 23.4% | zero |
| 3 | Hand corrections and splits, kept in a durable ledger | 0.9% | zero |
| 4 | LLM, instructed to answer Uncategorized rather than guess | 9.6% | billed |
| — | Unresolved by any stage, labelled as such | 1.1% | zero |
Stage 2 is the one worth dwelling on. The State already classifies its own line items, and nobody was reading the field. Switching one department from the summary export to the detail export dropped its uncategorized share from 20.3% to 5.0%, resolving 679 of 898 purchase orders for zero API cost. Across the corpus, 96% of the entire uncategorized problem traced back to files exported without line detail.
Every purchase order now carries a category label, and the duplicate count is zero. Neither was reached by loosening a rule. What remains is an explicit Uncategorized bucket of 22,926 orders, 8.6% of the corpus and $2.77B: 19,723 where the model followed its instruction and answered Uncategorized rather than guess, mostly non-IT purchases the taxonomy was never meant to hold, and 2,848 that no stage could resolve. That bucket is a labelled fact about the data, not a hidden residue, and the distinction matters. A classifier that reports 100% coverage by forcing every row into a real category is lying about the 8.6%.
I ran an LLM enrichment over Caltrans IT Goods to turn terse purchase order titles into readable product descriptions. On inspection it looked like an improvement. Measured, it was not.
| 7,575 | enriched rows carrying only 681 distinct texts. The model was converging on boilerplate. |
| 19% | was pure hedging language. |
| 74% | of rows dropped a model number or SKU that was present in the original. |
"HPE ProLiant Compute DL380 Gen12 Performance Heat Sink Kit""HPE ProLiant or Synergy server platform, option, or support line"
The enrichment was fluent and less useful than the input. Loading it would have buried the best identifying text in the dataset under a vaguer restatement. I kept the column and the round-trip, because they are the right shape for a better source, and dropped the data. The one genuinely useful thing the pass found was contract term dates, which a deterministic extractor now pulls instead.
The advisor answers questions about the data through seven typed tools: spend_breakdown, vendor_presence, renewals, aging_assets, find_purchases, buyers_and_contacts, and list_departments. It never sees a connection string and never writes a query. Tool arguments are validated against the department and category names that actually exist. An unknown value returns a warning rather than being silently dropped, so a typo surfaces as a question instead of an empty result the model then explains away.
Two rules make the output usable rather than merely plausible. Every figure must come from a tool result, with no arithmetic on remembered numbers and no filling gaps from general knowledge. And the dataset’s known weaknesses are written into the system prompt: the uncategorized share, departments loaded without line detail, delivery dates masquerading as contract terms, the thin published-EOL coverage. The model qualifies its own claims instead of being confidently wrong about a hole in the source.
Contract terms are buried in free text, written as TERM: 7/1/26 TO 6/30/2029 inside a line description, because 62% of dated purchase orders record a delivery date where the contract term should be.
An extracted date is only written if that date literally appears in California’s own line text. Anything else is reported and dropped. That is a deterministic check on an extraction step, and it is what lets a harvested date carry the same confidence tier as a natively filed one. It is the State’s own statement either way, just written in the wrong box.
Three tiers, kept visually distinct so a guess never reads with the authority of a fact.
| Tier | Basis | What it is allowed to show |
|---|---|---|
| Confirmed | a dated term on the purchase order itself | expiry date and status |
| Publicized | vendor EOL or EOS bulletin | date plus source URL |
| Inferred | age only | a flag and discovery questions, never a date |
The strategy generator extends this to cross-source claims with four labels. CORROBORATED means two independent sources agree. REQUESTED ONLY means stated intent with no transaction history, and is always a question, never a finding. TIMING means an expiry and requested money collide. COST CHECK means two filings state the same figure, or a gap between them. The system prompt forbids upgrading a label, and requires ranking plays by label strength before dollar size.
A reseller ran one of these accounts through a general-purpose assistant and got back “lead with Zero Trust” as the top recommendation for CDCR. Scored against actual spend, CDCR was already established on Zero Trust, at 11.1% penetration and a gap score of 0. The recommendation was backwards. It was pitching them something they had already bought.
The same analysis claimed identity procurement was “strangely weak” based on $12.8M in the Security and Identity category, while identity-adjacent spend sat at $63.8M in End User Computing alone. A category-scoping blind spot read as a market gap.
Both errors are what a fluent model does with a document that does not contain the disconfirming evidence. The fix was structural. The Envision scanner now computes established-versus-gap per theme, and the system prompt carries an explicit rule that established themes are not openings. Regenerated, the same document correctly identifies the department as established on Zero Trust, and elevates the genuine 0.9% penetration gap instead.
California publishes its own IT strategy with dated commitments. Ten themes are scored per department: Zero Trust, identity, SOC-as-a-Service, cyber resilience, AI readiness, cloud-smart, system health, digitization, process mining, and accessibility. The score is absence × capacity × peer-proof × priority-weight.
Capacity normalizes against the largest related budget actually observed rather than a fixed divisor, because a constant flattens the scale so far that a $23M budget scores nearly as high as a $3.3B one. Peer-proof counts how many departments have already moved on that theme.
The output is an argument made entirely from the customer’s own published commitments. A stated statewide priority with no departmental spend behind it is budget that has to move.
One department’s workforce research cost $2.20 against 1,027,385 input tokens without cache control, and $0.33 with it. The reason is specific and worth knowing. A resumed server-tool turn replays the entire assistant block, and that block holds every page fetched so far. Without cache_control you re-pay for the whole accumulated context on every hop.
| Model | Cost | Tool calls | What it actually found |
|---|---|---|---|
| Opus 5 | $0.47 | 14 | Three-way vendor overlap, the consolidation argument |
| Sonnet 5 | $0.11 | 7 | Two vendors, reseller fragmentation, a 2017 array with no refresh plan |
| Haiku 4.5 | $0.06 | 14 | Same headline vendors, more generic framing |
All three correctly reported that a vendor with zero presence had zero presence, and declined to pitch it. Grounding held across the whole range, which means the guardrails are doing the work rather than the model’s capability. What scaled with model strength was depth of investigation. Only Opus went looking for competing platforms. So: Sonnet for exploration, Opus for the opening question and anything going in front of a customer.
Web search runs about $10 per 1,000 searches.
The same state report exports either one row per purchase order or one row per line item. Conflating them counts line items as purchase orders. 75,196 spreadsheet rows are really 24,171 purchase orders. Format is now detected from the row shape, not the filename.
pandas refuses them outright, so the reader sniffs leading bytes rather than trusting the extension. And every money column is a formatted string like "$83996". A naive parse turns those into NaN, then 0.00. The ingest completes, row counts look perfect, and every financial figure is silently zero. That failure mode has no error message, which is what makes it dangerous.
Only 34% of purchase orders have line totals summing to the grand total within 2%. The worst case is an $89.2M purchase order against $43.7M of lines, because amendments and partial lines are not represented. grand_total, taken once per purchase order, is the only source of spend. That is enforced as a standing rule rather than left to whoever writes the next query.
The purchases table is disposable. Three separate flags delete and rebuild it. Hand corrections are the one thing that cannot be regenerated from source, so they live in a separate ledger keyed on (department, category, po_number), saved before every ingest and re-applied after load but before categorization, so restored rows never reach the billed stage.
One operational note: the database lives in a OneDrive-synced folder. WAL mode there is a corruption risk, because the sidecar files get touched by sync mid-write, so the ingest scripts that had enabled it were reverted to rollback journaling after an integrity check and checkpoint.
Rows were grouped into orders by purchase order number alone. But an eSCPRS number is only unique within a department. Orders that shared a number across departments merged into one, the first department won, and every other one vanished — with its line items reattached to the survivor, so the line count still looked plausible. One file collapsed 5,452 real orders into 4,914. Correcting the grouping key across the corpus restored 8,695 purchase orders.
Nothing had ever errored. Row counts went down and no counter was watching the direction. The lesson is the same one as the "$83996" money column above: a pipeline that only reports what it wrote cannot tell you what it lost.
The write path called .prepare() on a wrapper that exposes first, all, and run — and has no prepare. It threw on every single request since the feature shipped.
What made it invisible is the interesting part. Telemetry a few lines above logged the model’s intention to revise rather than the outcome of the write, so the dashboard cheerfully reported successful revisions against a permanently empty table. The fix was two lines of database call and one line of telemetry, and the telemetry line was the one that mattered: the flag now reports what actually happened.
Ranked plays were keyed play-1 through play-N in generation order, and the schema enforced the pattern. Any refresh that dropped or reordered a play would shift every id beneath it and silently relayer a salesperson’s field intelligence onto a different argument — a note about a lost renewal reattached to an unrelated recommendation, with no way to tell from the record.
Caught while the revisions table was still empty, which made the fix free. A month later it would have needed a migration and a guess about which note belonged where. Position is not identity, and a schema that enforces a positional key is a schema that will eventually corrupt meaning rather than data.
Run from the wrong directory, the deploy tool found no functions directory, uploaded the static assets, reported success, and shipped no server code at all. The site then answered 404 where the middleware should have answered 403, so the symptom read as a missing file rather than a missing application, which sent the first hour of debugging in the wrong direction entirely.
Fixed by making the deploy command verify the live result instead of trusting its own exit code. That single change is what the publish path now does at every step.
I added a rule to the strategy generator’s system prompt covering how to treat project-pipeline data. It ran about 1,900 characters across six nested bullets. The next run spent nearly its entire 24,000 token budget reasoning and emitted 123 words of finished document.
Compressed to roughly 700 characters, same rule and same constraints with less prose, the next run produced 1,910 complete words with no truncation. The system prompt had drifted to 71% rules by volume, and that ratio, not the raw token count, was the problem.
Two things came out of this. Truncation is now detected explicitly with stop_reason == "max_tokens" and surfaced as a loud banner on the document rather than left to be noticed by a reader, because a silently truncated deliverable is the worst failure mode in a paid product. And prompt length is budgeted like any other resource. Every sentence of instruction is a sentence of output you did not buy.
A related constraint worth knowing: past a certain max_tokens, the SDK requires streaming, because a request that might exceed ten minutes cannot use the blocking call. Raising a ceiling changed the call signature.
content array can lead with a thinking block, so content[0] is not reliably the answer. Assuming index 0 made a live chat widget report a connection failure on perfectly successful replies. Both widgets now scan for the first block of type === "text".The public chat runs through a Cloudflare Worker, and the system prompt is held there rather than in the page. A prompt sent from the browser is a prompt anyone can replace. Read the source, copy the endpoint, POST your own system prompt, and the bill is mine. Held server-side, the Worker only ever runs the assistant it was deployed with. The cost is that editing copy means a redeploy, which is the right trade.
The deployment is deliberately standalone: its own API key, its own budget, its own kill switch, and origin-locked CORS. A public marketing page with unpredictable traffic should never share a key with anything else. One mode dispatches between separate scoped prompts, so a second assistant did not need a second service.
The document assistant is confined to one document. No web, no database, no other departments. That confinement is the product argument, not a limitation. A general-purpose model reading a strategy document reasons past the evidence, and this project has a documented case of exactly that. An assistant that says “the document does not say” is worth more in front of a customer than one that guesses well.
Model behaviour is checked by spot-diffing outputs and by the corroboration gates described above. That catches silent degradation, and it is how the 74% SKU drop was found, but it is a manual process, not a regression suite. A golden set of documents with assertions on grounding and label discipline is the obvious next build, and it is the gap I would close first.
Everything is SQL against structured data, which is correct for this problem, because the questions are aggregations rather than semantic search. But the 8,106 BCP observations are free text, and finding the right one is currently keyword matching. That is a real embedding use case that I have not built.
There are 17 suites carrying 632 assertions offline and 23 more that run against live production data, concentrated on the serving layer and the tenant boundary, the places where a defect reaches a customer rather than a spreadsheet. The deploy script runs all of them first and refuses to ship on red. It has already refused a publish once, on a check I had written wrong that same afternoon, which is the system working on its author.
Two rules came out of building them. A test file that fails to load counts as a failure, because the twelve-case JWT forgery suite sat broken on a bad import for days, reporting nothing, and nothing looks a great deal like fine. And the assertions are written against entry points rather than helpers, after I once unit-tested a scope-building function in isolation, never called the function that actually runs it, and shipped a parameter-binding bug into a paid generation run. Testing the piece you edited instead of the thing that runs it is exactly how a change looks verified and is not.
Adequate for one operator. Not adequate for a team.
This entry used to say single-user by design, on the argument that isolation should be built against a real requirement rather than a hypothetical one. The requirement arrived with the second prospect. There is now per-user identity through Cloudflare Zero Trust with JWT signatures verified rather than decoded, a tenant_id on every row and every query, a per-tenant token budget, and a kill switch on contract end.
The invariant is enforced, not trusted. A static sweep over the source reads every SQL literal in the serving code and fails on any that lacks a tenant filter, with a written exemption registry for the handful of resolution queries that legitimately cannot have one. That sweep has caught three real bugs. Isolation is then proven against live production data in both directions, with refusals logged, and the static runbook pages fail closed: a page with no owning row is invisible to everyone rather than visible to everyone on the login list.
I would make the same call again, with one correction: the tenant column went in from the first commit even while the system was single-user, because that is the one piece that is genuinely expensive to retrofit. Everything else waited for a paying customer and was cheap when it came. Defer the mechanism, not the key.
I needed to see any customer’s runbook to troubleshoot it, and the obvious design, a staff role that sees everything, would have put a bypass in every query and made the sweep above unable to tell a legitimate exception from a leak. So a support session instead rebinds one staff login to one tenant for a bounded hour, with a typed reason. Every existing query stays byte-identical. Everything done inside a session is attributed to support, excluded from the customer’s adoption metrics, and disclosed in their own report: opened twice this month, for these logged reasons. For a product whose whole argument is provenance, “we log every time we look, and here it is” is a stronger position than silence.
Version one was a 450-record HTML dashboard built for my own territory. This is what it became once the question changed from what do I need to what would a vendor pay for — and then changed again, from what would they pay for to what would they keep paying for, which is the question that produced the Account Runbook.
It is not built for one territory or one manufacturer. Every state, county, and large municipality publishes procurement data with the same characteristics, and the taxonomy, the demand chain, and the generation step all generalize. The first customer runbooks are in test.
A public site whose front page is one real runbook chain, a read-only demo with two sanitised sample runbooks (one manufacturer, one reseller), and per-account runbooks behind a login, each with a scope-confined advisor.
goldenstatesignal.com → the live demo →Architected end to end. Every scoping, data model, and design decision mine. Implementation directed using AI coding tools.