Golden State Signal

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.

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 purchase order says it was finally spent, typically years later, and by then the decision is long made.

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.

Supporting sources: 435 ETC evaluations, 118 workforce signals, 19 lifecycle records.

• Account strategy documents Ranked plays with an explicit confidence label on every claim, and positioning written in the department’s own published strategic language.
• Opportunity scans All 160 departments ranked for a product category, with the score components shown rather than hidden.
• Envision 2026 gap analysis California’s own published IT strategy scored against actual departmental spend. This is the most defensible analytic in the system.
• A public demo and sample document Live at goldenstatesignal.com, each with a scope-confined chat assistant.

SQLite and server-rendered HTML are deliberate. This is one analyst’s tool against a 276MB dataset with no concurrent writers. Postgres and a React front end would be resume-driven development. The constraint that would justify them, row-level isolation between paying clients, does not exist yet.

Four stages run most-authoritative-first. Each one only sees what the previous stage could not resolve.

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.

Uncategorized residue now sits at 15,000 purchase orders, or 7.4% of the corpus. 96.1% of purchase orders carry line-item detail.

A model pass I built, measured, and threw away

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.

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 model gets tools, never SQL

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.

Corroboration as a write gate

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.

Confidence tiers that survive contact with a customer

Three tiers, kept visually distinct so a guess never reads with the authority of a fact.

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.

The case where this caught a real error

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.

The Envision 2026 gap scanner

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 tiering, measured on the same task

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.

Two incompatible schemas ship under the same file extension

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.

The files are HTML tables named .xls

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.

Line-level money does not reconcile, so it is banned from spend math

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.

Human judgment outlives the database

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.

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.

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.

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. 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 document generation all generalize.