Design intent. One button press, one complete capture. The operator is scrolling a Facebook group and has seconds — so everything about this workflow optimises for not interrupting them: the extension grabs the rendered thread, this shapes it into four row types, and a toast comes back inside five seconds. It is the only place in the system where a human is the trigger and the clock.

Starts

POST /fb-capture — the Copy button in the Brave extension

Writes

public.posts · public.commenters · public.posters · public.commenter_verdicts

Reads

public.posts (for dedup)

Counters

captures_received · rows_written · capture_errors

Depends on

codex-postgres · OpenRouter · the Brave extension

Connects to

→ Ch 2 ZIP RANKING (via /zip-county) · → Ch 4 PROPOSER (via Commentor Verdicts)

First symptom

The Stream Deck toast reports a capture the sheet did not receive. Since Fix K the toast derives from actual write outcomes, so this now surfaces as ok:false naming the write that missed.

JSON

vCUMCGQPireL7kxE · buyer-intel/sb-buyer-intel.json

The operator finds an off-market post in a Facebook REI group and presses Copy. The extension scrapes the rendered post plus every comment, resolves the ZIP, and POSTs the whole thread here. An LLM parses it into four row types — the post itself, the people who commented, the person who posted, and a first-pass verdict on each commenter — then each lands on its own tab.

Features worth knowing

Upgrade surfaces.

• A new capture type (story, reel): teach Normalize FB Clipboard the new capture_type, add a Sheet Append, wire it into Await All Writes.
• Per-commenter enrichment (phone extraction, profile scoring): the seam is between Sheet - Commenters and Await All Writes.
• Sheets → Postgres — DONE 2026-08-24. All four appends moved; the merge topology was untouched, as predicted. Sheet - Posters turned out to target a DELETED tab and had been discarding poster rows silently — it now writes public.posters.

• Dedup on re-capture. Read Posts For Dedup → Mark Duplicate compares against existing rows. A re-grabbed post does not write a second Posts row, but its commenters still land: people who commented after the first capture are new leads, and person-level dedup happens downstream on profile_url.
• The ZIP rule is 2a/2b/2c/2d, implemented in the extension, not here — the workflow trusts the ZIP it is given.
• County routing never blocks capture. A ZIP in a county we already cover still gets captured; coverage only decides whether it is proposed.

Response topology. The four Sheet appends (Sheet - Posts, Sheet - Commenters, Sheet - Posters, Sheet - Commenter Verdicts) converge on an Await All Writes merge before Build Response runs, so the Stream Deck receives exactly one response per capture. Build Response reads row counts from each Sheet node's actual output (post-write), and ok is derived from whether the sheet(s) relevant to this capture_type landed rows — not hardcoded. Sheet failures still surface via settings.errorWorkflow into Obsidian, and also flip the Stream Deck toast to ok: false with a summary naming which write missed.

Standing gaps.

• [gaps-query] Query current proposals for this section: /gaps Buyer Intel (from any Cipher surface).

Design intent. Five unrelated questions share one workflow because they share one tab. Splitting them would mean five workflows all reading ZIP Ranking, five sets of credentials, and five places for the sheet id to drift. The cost of that choice is that a failure in one chain is invisible to the other four.

Starts

five webhooks: /zip-ranking /zip-status /county-buy-box /zip-county /operator-score

Writes

public.zip_ranking (upsert on zip)

Reads

public.county_buy_box · public.zip_buyers · public.posts · public.commenters

Counters

zips_ranked · ranking_lookups · ranking_errors

Depends on

Buyer Intel sheet · Census ZCTA data (via Zip County Resolver)

Connects to

← Ch 1 BUYER INTEL · ← Ch 3 ZIP INGEST · → Ch 4 PROPOSER · → PW 3 listsource-county-zips

First symptom

A post with a real location is skipped because no five-digit ZIP appears in it. The capture reports "no ZIP anywhere" and moves on — the lead is not logged as lost, it is simply never seen.

JSON

4GlPOFgi7PdHEjiH · buyer-intel/sb-buyer-intel-zip-ranking.json

Not one process — five independent chains that happen to share a workflow because they all serve the ZIP Ranking tab.

Features worth knowing

Known trade-offs.

• /zip-county is deliberately shared upstream and downstream. The Copy button and the Proposer resolve through the same endpoint so a ZIP can never mean two different counties at two ends of the pipeline.
• Recency lives here rather than on Drill Down, because tier is a property of the ZIP, not of a research view over deed rows.
• Tier follows where activity is, not where the median falls. 42 buyers inside three months is hot even when a long tail drags the median past six.

Upgrade surfaces.

• A sixth endpoint: add a webhook and its own chain; nothing is shared but the sheet.
• Buy-box fit scoring: /county-buy-box is the natural home, and it already reads the box.

The buy box widens itself — the loop. A county's box is not built once. It bootstraps from one manually-found ZIP and then converges:

1 ZIP (manual copy-post discovery)  →  buy box: sound, but narrow
        ↓
ListSource ranks that county's ZIPs against it
        ↓
those ZIPs  →  XLeads  →  buyers + deeds for each
        ↓
box recomputed across 8, then 16 ZIPs  →  the county's real profile

This is why a single-ZIP box is served, not blocked. It only has to be good enough to point ListSource at the right ZIPs; the data that comes back replaces it. Blocking it would freeze the loop at step one — the county could never acquire the ZIPs that would widen it.

The gate therefore separates two questions. Not sound (too few transactions, or no real concentration) is blocked — that box would send ListSource hunting on noise. Sound but narrow (one ZIP, good numbers) is served with a note: more ZIPs widen what the box can claim, they do not make it more correct.

Granularity is county-level by choice. The box could be computed per ZIP, but that is too narrow to steer a county-wide search — it would rank ZIPs against a single street's profile. County-level averages are the working unit.

Pooling is by transaction, not by ZIP. cbbStats_ pools every buyer row in the county and derives one distribution, so a ZIP with 175 deeds moves the county box more than one with 40. That is a volume weighting, not a per-ZIP average, and it is usually the right call: it follows where the buying actually is.

Beds and baths get a width, not a measurement. Price, sqft, year and lot are stored as p25/median/p75 bands. Beds and baths are stored as a mode — "3bd (51%)" — and a range is reconstructed from it downstream by widening ±1 (Buy Box Resolver.js L191). ListSource itself takes ranges and the live endpoint serves them (BEDROOMS_NBR:[2,4]), so nothing is lost at the consumer.

What is lost is the shape. Because the widening starts from a single number, the range is always exactly ±1 whatever buyers actually did: a real 2–5 spread still emits [2,4] and drops the 5-bed buyers, while a genuinely tight market still emits [2,4] and reaches into homes nobody wants. Baths is additionally one-sided — it never widens below the mode.

Fixed 2026-08-20. cbbBand_ now grows a window outward from the mode until it holds a real, concentrated majority, and stores the band with its coverage: 2-4 bd + 90.3%. Quartiles were the obvious alternative and are wrong here — on live Harris data p25–p75 gives [3,4] and silently drops all 26 two-bedroom buyers, so do not “tidy” beds/baths to match the sqft/year/lot fields.

Two guards ride with it. A band is only trusted when it also beats what a window that wide would hold on a flat distribution (Harris: 4.27x; flat data scores ~1.0x and is marked weak) — because a coverage target on its own always succeeds, it just returns a uselessly wide range. And beds/baths were the two fields where this mattered most: the old rule could never reach below the mode, which excluded Harris's 18 one-bath buyers, 12% of that market.

Three implementations were reconciled at the same time — County Buy Box.js, Buy Box Resolver.js and the n8n Build Box node each widened a mode independently. All three now read the stored band.

How a location is resolved (built 2026-08-19). Four ZIP rules run first — 2a post body, 2b author reply, 2c commenter agreement, 2d unverified. When none of them find a five-digit ZIP, a fifth pass looks for a city:

1. Known-name match against a Texas city list, longest names first so north richland hills beats richland hills.
2. Free-text pattern — in <Title Case Words>[, TX] — which catches the long tail the list cannot hold.

A city candidate goes to Google Geocoding for verification, because the extension has no way to know whether Springtown is a real place.

A geocoded city is marked as a guess, not a fact. Every capture carries resolver_source: in_text_regex when a ZIP was stated, google_geocoding when a city was inferred. A city spans dozens of ZIPs across possibly two counties, so it is good enough to capture and propose the county — never good enough to pin a ZIP. The distinction travels with the row rather than being lost at the boundary.

Standing gaps.

• [gaps-query] Query current proposals for this section: /gaps Buyer Intel (from any Cipher surface).

Design intent. Replace, never accumulate. A ZIP scraped twice should hold the second scrape, not both — so this deletes the previous rows before appending the new ones. Getting that order wrong is why the delete is the most carefully written node in the workflow.

Starts

POST /zip-buyers — from xleads-to-sheet.cjs, in 60-row chunks

Writes

public.zip_buyers · public.zip_buyer_roll

Reads

public.zip_buyers (to find stale rows)

Counters

buyer_rows_received · buyer_rows_written · ingest_errors

Depends on

PW 3 xleads-buyer-pull · codex-postgres

Connects to

← PW 3 xleads-buyer-pull · → Ch 2 ZIP RANKING (recency reads ZIP Buyers)

First symptom

A ZIP re-scrape lands but the roll does not update, so recency tiers keep reporting the previous pull. The rows are right and the derived view is stale.

JSON

gM4myni6M3Z1CJen · buyer-intel/sb-buyer-intel-zip-ingest.json

XLeads returns real deed records for a ZIP. This deletes the previous scrape for that ZIP, appends the new rows, then derives the per-buyer roll.

Known trade-offs.

• Delete-then-append rather than upsert. XLeads rows carry no stable key, so there is nothing to match on. Replacing the ZIP wholesale is the only operation that is correct twice in a row.
• 60-row chunks because a full ZIP payload hit 1.56 MB and nginx caps the body at 1 MB.
• The delete is a batch code node, not a Sheets delete (Fix J, 2026-08-19). It removes the specific stale_row_numbers, sorted descending so row indexes do not shift underneath it. The earlier version deleted the span from first to last row, which would have taken other ZIPs' rows with it the first time a re-scrape hit an interleaved tab.

Upgrade surfaces.

• A second deed source: the webhook contract is {zip, rows[]}; anything producing that shape can post here.
• Sheets → Postgres — DONE 2026-08-24. Delete-then-append is now a transaction, and the ordering hazard is gone.

Standing gaps.

• [gaps-query] Query current proposals for this section: /gaps Buyer Intel (from any Cipher surface).

Design intent. A cheap, reversible gate in front of an expensive, irreversible one. Proposing a county costs a line at the morning brief; onboarding one costs a scrape, a buy box and weeks of outreach. So this workflow is allowed to be wrong, and is deliberately not allowed to act.

Starts

POST /buyer-intel-propose — from the Apps Script Proposer, ~8 AM

Writes

public.proposals

Reads

(none directly — the Apps Script does the reading)

Counters

proposals_received · proposals_written · proposal_errors

Depends on

codex-postgres · Buyer Intel Proposer.js (project-d, still reads the SHEET)

Connects to

← Ch 1 BUYER INTEL (via Commentor Verdicts) · ← Ch 2 ZIP RANKING (via /zip-county) · → the morning brief

First symptom

The morning brief has no county to discuss, and a missing proposal is indistinguishable from "nothing qualified today".

JSON

EgGihZv5JZNn6XsQ · buyer-intel/sb-buyer-intel-proposer.json

Clusters commenters by the counties their interested_zips resolve to, and when a county clears the threshold and is not already onboarded, writes one Proposals row. That row is what the operator reads at the morning brief.

Known trade-offs.

• It runs on interest, not deeds — and that is the correct half of the pipeline. Deed rows only exist for counties already scraped, and scraping only happens after onboarding; using them here would be circular.
• Shape Proposal throws on purpose for a missing county or a zero buyer count. It has no error track by design — a malformed proposal should fail loudly and write nothing.
• Nothing auto-fires. PROPOSER_ARMED = false and the trigger is not installed. Proposing is a suggestion to a human, permanently.
• Deed verification is not possible and never will be. XLeads can only be searched by county, ZIP or street — never by person. A workflow was once built to ask "what has this buyer purchased?" and it could never work; it has been deactivated. This is why the Proposer clusters on stated interest rather than confirmed purchase history.

Upgrade surfaces.

• Scoring proposals (rank several counties): Shape Proposal builds the row and is where a score would attach.
• Auto-arming: PROPOSER_ARMED in the Apps Script is the single switch, and it is off by design.

Standing gaps.

• [gaps-query] Query current proposals for this section: /gaps Buyer Intel (from any Cipher surface).

Design intent. Write the first message while the evidence is still to hand. The workflow drafts and stops — status begins at draft and every transition after that is the operator's, because a first message to a real buyer is not something a schedule should send.

Built to the door, not through it. This workflow exists; the engagement track it opens onto does not. Engagement over text, email and calls is live elsewhere in this Atlas — engagement through social media is not built, and that is what buyer outreach actually requires. It is deliberately deferred to Phase 3, because a social engagement layer generates data every upstream system then has to process.

Starts

POST /buyer-intel-message — after a county is approved

Writes

public.outreach (the tab it targeted never existed — see The move to Postgres*)*

Reads

public.commenter_verdicts

Counters

outreach_requested · drafts_written · messenger_errors

Depends on

Buyer Intel sheet · OpenRouter

Connects to

← Ch 4 PROPOSER (a county must be approved first) · → the operator, by hand

First symptom

Not yet in service. When it does run, a failed draft shows as LLM spend on the funding tracker with no matching row on Outreach.

JSON

BiN5ABDZt9r0QFjs · buyer-intel/sb-buyer-intel-messenger.json

Reads the verdicts for an approved county, picks the buyers worth contacting, and has an LLM draft a first message per person.

Known trade-offs.

• Drafts, never sends. There is no send node and that is deliberate; the operator reviews every first message.
• The prose fallback keeps a malformed draft rather than discarding it. A human is reading these anyway, so partially-usable text beats a null.

Upgrade surfaces.

• Send-side automation: Append Drafts is the terminus; a send would attach after it, gated on a status change.
• Per-county voice: Prompt Builder is where county context enters.

Not to be confused with the Buyer Intel Extension. The Brave extension that actually performs the capture Chapter 1 receives — the Copy and Post buttons themselves — lives as its own component, buyer-intel-extension/, and is documented separately in The buyer's-side toolchain below rather than as a seventh chapter here. Chapters 1-5 are the n8n workflows the capture lands in; the extension is the client that produces the capture.

Design intent. The mouth of the business. Seven document types × five to seven counties, each on its own schedule, because county clerk portals publish on their own rhythms and hammering all 42 at once gets you blocked. The 52 separate cron triggers are not disorganisation — they are the schedule, expressed one source at a time.

Starts

52 cron triggers · a Drive trigger · POST /write-processing

Writes

public.lis_pendens · public.prep

Reads

(none — it is the source)

Counters

documents_written · intake_errors

Depends on

PW 1 scraper · /scrape · /process-tax · /skip-trace · codex-postgres

Connects to

→ Ch 8 DOC EXTRACTOR v2 · → Ch 14 SCH LEADS (via Prep)

First symptom

One county × doctype stops producing while the other 41 keep running. Nothing halts — the day just yields fewer documents from one source, and only the counter shows it.

JSON

BbZd6AHJZQBJ048aajPc3 · doc-extractor/sb-processing-sheet-writer.json

The true point zero of the lead side. Nothing precedes it: county clerk documents are scraped here, and every lead downstream traces back to a row this workflow wrote.

Seven document types across five to seven counties. Probate, Lis Pendens, Divorce, Mechanics Lien, Federal Tax Lien, State Tax Lien and Abstract of Judgment run in Williamson, Travis, Hays, Bell and Bastrop; Tax Delinquent additionally covers Burnet and Caldwell. That grid is why there are 52 separate cron triggers on one canvas — one per source, each on its own schedule.

Features worth knowing

• The 42 dispatches are pokes, not transfers. Each tells the sourcing server to start a scrape; the server answers immediately and works in the background.
• A failed poke is silent and costly. Unlike the Cipher notifications elsewhere in this document — which are correctly left untracked because they report on work already done — a failed dispatch means that county × doctype never runs that day and no leads come from it. Instrumented 2026-08-18: all 47 external nodes now route to Obsidian, and the formatter names the doctype and county so a row reads "LIS PENDENS / WILLIAMSON" rather than a node name.
• Host-down is escalated. If the sourcing server is unreachable the failure is graded critical and says so explicitly — that is not one source missing, it is all 42 and intake is dark for the day.

Known trade-offs.

• 42 dispatches instead of one loop. A loop would be tidier and would fail as one unit; separate nodes mean one dead county portal cannot take the other 41 down with it.
• Poke, not transfer. Each dispatch asks the sourcing server to start a scrape and returns immediately. The work happens out of process, which is why a failure here means "never ran" rather than "ran and lost".

Upgrade surfaces.

• A new county: add one cron per doctype pointing at the same /scrape route; nothing else changes.
• A new doctype: same, one row of the grid.
• Replacing the grid with a registry: county-registry.js on the sourcing server already knows which counties are active — the 52 crons could read it instead of being hard-coded.

Standing gaps.

• [gaps-query] Query current proposals for this section: /gaps Lead Pipeline / Dedup (from any Cipher surface).

Design intent. Turn a scanned court filing into a row with an address on it. Vision reads the document, the address is resolved against a real geocoder, and only then does it become something the rest of the pipeline can use. Thirteen counters exist because every stage of that can fail differently and the distinctions matter — a document skipped is not a document that failed.

Starts

POST /write · a UUID webhook · 4 crons

Writes

public.lis_pendens (unique on filename) · public.prep

Reads

public.lis_pendens

Counters

13 — docs_processed · docs_skipped · docs_needs_address · docs_complete · docs_error · vision_calls_made · …

Depends on

OpenRouter vision · /lookup-address · an Apps Script Web App · Processing · Codex_Core

Connects to

← Ch 7 PROCESSING SHEET WRITER · → Ch 9 DATA VER · → Ch 14 SCH LEADS

First symptom

docs_needs_address climbs while docs_complete flattens. The pipeline keeps running and every document is read, but nothing becomes a lead.

JSON

RO1yF9unzbnbeXqq · doc-extractor/sb-doc-extractor.json

Vision-reads the scraped documents, extracts the owner and property, resolves the address, and updates Prep. Between them, chapters 7 and 8 turn a county clerk filing into a lead record.

The most heavily counted workflow in the estate — 13 counters, including docs_processed, docs_skipped, docs_needs_address, vision_calls_made and prep_sheet_updates. It was built with observability from the start rather than retrofitted.

Live conflict: this workflow and chapter 7 both register a POST /write webhook, and both went active on 2026-08-18. n8n gives the path to only one of them — whichever registered last — and the other silently never receives a request. The two handlers feed near-identical Extract Items chains, which suggests v2 was built to supersede the writer's copy, but that has not been confirmed. Until it is, /write traffic is going somewhere unverified. (See gap #12.)

Known trade-offs.

• Vision over OCR. Court filings are scans of varying quality with no consistent layout; a vision model reads context where OCR reads characters. It costs more per document and is the reason vision_calls_made is counted.
• Address resolution is a separate step, not part of the read. A document can be perfectly extracted and still lack a usable address, and that state (docs_needs_address) is worth seeing rather than folding into failure.

Upgrade surfaces.

• A new document type: the vision prompt and the field mapping are the two places that know what a Lis Pendens looks like.
• Batch address resolution: /lookup-address is called per document; a bulk endpoint would attach at the same seam.

Standing gaps. (none — the Drive-cleanup gap was closed 2026-08-20; see below.)

Extracted documents are deleted from Drive (added 2026-08-20). For most of this workflow's life nothing was: Find PNG1 located the scan, Download PNG1 pulled it, and across all 53 nodes there was no delete anywhere, so Drive grew without bound.

Delete Drive PNG now runs after Write Results1 — the moment the data is durably in the Processing sheet, not merely the moment vision returned something. Deleting any earlier would risk destroying a document whose row never landed.

Two details worth knowing before touching it:

• The Drive id had to be threaded through. Prepare Vision Request1 and Parse Extraction1 each rebuild the item from scratch, so the id from Find PNG1 was long gone by the success point. It now rides along explicitly. Re-looking-up by filename was the alternative and is worse — duplicate names would delete the wrong file, and a Drive delete is not reversible.
• The guard is an allow-list. Only complete / success delete. The first version tested !status.includes('error'), which meant a blank status deleted the file — absence of evidence read as consent. skipped, needs_address, error, blank, and any status added later all keep their file: it is the only evidence of what went wrong and the only thing a retry could read.

Fire-and-forget (onError: continueRegularOutput) — the data is already written, so a failed delete costs one leftover file rather than a failed extraction. Counter: drive_files_deleted.

• [gaps-query] Query current proposals for this section: /gaps DOC EXTRACTOR (from any Cipher surface).

Design intent. The judgment step. Every scraped record arrives here and leaves through exactly one of four doors, and the four-way split is the whole point: a record that fails must land somewhere with a reason attached, never be dropped. Six switch nodes exist to make that routing explicit rather than implicit.

Starts

POST /sourcing · POST /fsbo90

Writes

Master · Discovery · Disqualified · Error

Reads

(none — it judges what it is handed)

Counters

7 — leads_in · leads_passed · leads_disqualified · leads_failed · …

Depends on

localhost:3042/zillow-check · Codex_Core

Connects to

← Ch 8 DOC EXTRACTOR · ← Prep Lead Handling.js (Apps Script) · → Ch 10 TPS · → Ch 14 SCH LEADS

First symptom

Everything routes to one destination. Because all four outcomes are "successful" writes, the workflow reports healthy while the mix is wrong — the counters diverging is the only tell.

JSON

I4nyoPgexPYlXjqUeHPTV · data-verification/sb-data-ver.json

The county scrapers post raw records here. This validates, deduplicates, checks Zillow for listing status, and sorts each record into one of four destinations — Master (good), Discovery (needs work), Disqualified (fails the box), Error (broken input).

The four-way split is the whole point: nothing is silently dropped. A record that fails always lands somewhere with a reason attached.

Known trade-offs.

• Four destinations, never a delete. Disqualified and Error are kept rather than discarded, because "why did this not become a lead?" is a question worth being able to answer months later.
• Zillow is checked at intake, not just in batch. A property already listed on the MLS is not off-market, and finding that out before enrichment saves the expensive step.

Upgrade surfaces.

• A fifth outcome: the switch chain is the seam; a new branch needs its own sheet and its own counter.
• Criteria changes: the buy-box comparison lives in the switch conditions, not in code.

Standing gaps.

• [gaps-query] Query current proposals for this section: /gaps DATA VER (from any Cipher surface).

Design intent. Turn an address into a person you can call. It is separated from DATA VER because enrichment is slow, costly and fights anti-bot defences — folding it into validation would make every record pay that price, including the ones about to be disqualified.

Starts

POST /tps

Writes

public.imports_logbook · public.master · public.discovery · public.disqualified · public.error

Reads

public.master

Counters

13 — leads_in · leads_enriched · tps_no_match · tps_phone_invalid · tps_error · tps_calls_made · …

Depends on

PW 5 tps-enrichment (own host) · /cbc-enrich · /zillow-check · Codex_Core

Connects to

← Ch 9 DATA VER · ← Ch 14 SCH LEADS (via Prep) · ← TPS Trigger.js (Apps Script menu) · → Ch 15 GHL IMP (via Master)

First symptom

Match rate falls while call volume holds. TPS answers 200 on a block page, so tps_calls_made stays healthy and leads_enriched quietly drops.

JSON

yOIn6_TkxSP4M7beilypD · tps-enrichment/sb-tps-enrichment.json

Skip-trace and contact enrichment. A property record goes in, phone numbers and owner identity come back. This is the workflow behind the Cloudflare + DataDome boss fight — the enrichment sources actively fight automation, which is why it runs through a dedicated tps.arcosai.io host and needs a real headful browser rather than plain HTTP.

It scores the call list (added 2026-08-20). Cleanup for Master1 already computed Motivation_Score and Score_Tier; it now also writes BuyBox_Fit, BuyBox_Score, BuyBox_Misses and Call_Tier.

The two scales are kept deliberately separate. Score_Tier judges a person — how likely are they to sell. BuyBox_Fit judges a house — can we move it to a buyer who exists. They are never summed: a D-tier lead in a perfect house is not the same as an A2 lead in a house nobody wants, and a sum would say it is. Call_Tier therefore groups by fit and orders by motivation — T1 (STRONG + A1-LP/A2) through T5 — so the operator always calls the most motivated person whose house is sellable.

Beds and baths carry the most weight (30 and 25 of 100) because they are the only dimensions with a measured coverage band behind them; sqft, year, lot and price are quartiles and inform without vetoing. A missing field is not a miss — scoring runs only over dimensions that could be judged — and a lead judged on too little returns UNKNOWN rather than a confident score off one field.

The county band is fetched once per run, before the row loop: that loop is items.map(...) and synchronous, so an await inside it would return promises instead of rows.

Imports LogBook is the audit trail: every enrichment attempt is recorded whether or not it produced a contact.

Known trade-offs.

• Its own host and tunnel. tps.arcosai.io is isolated because this is the one target that actively fights automation; a block there must not take the main sourcing server with it.
• Imports LogBook records attempts, not just successes. A no-match is evidence about the source rather than a non-event.
• Started by a spreadsheet menu. TPS Trigger.js installs operator controls on Codex_Core — start, stop, backfill, retry — so the queue is driven by hand when it needs to be.

Upgrade surfaces.

• A second enrichment vendor: the NO_MATCH branch is the seam.
• Bulk mode: the workflow is per-lead; a batch endpoint would attach at /tps-enrich without changing the routing.

Standing gaps.

• [gaps-query] Query current proposals for this section: /gaps TPS Enrichment (from any Cipher surface).

Design intent. Master decays. A property that was off-market when it was scraped may be listed by the time anyone calls, and calling the seller of an actively listed house is the fastest way to waste a conversation. This keeps the list honest over time rather than only at intake.

Starts

POST /zillow-batch

Writes

public.master — both branches: the scored row and the ❌ ZILLOW_DQ: stamp

Reads

public.master

Counters

properties_checked · zillow_calls_made · master_writes · disqualified_writes

Depends on

sourcing.arcosai.io/zillow-check · codex-postgres · Codex_Core

Connects to

← Master Tab Filter.js (Apps Script) · → Ch 14 SCH LEADS

First symptom

Listed properties stay on Master. Nothing errors — the check simply stops returning listing status, and stale rows look identical to fresh ones.

JSON

_RXoQosQYEsMaDjftIP-n · zillow-batch/sb-zillow-batch.json

Re-checks properties against Zillow in batches. A property that has since listed on the MLS is no longer an off-market opportunity, so this keeps Master honest over time rather than only at intake.

Known trade-offs.

• Batch rather than continuous. Re-checking every property on every read would be expensive and pointless; listing status changes on the order of weeks.
• Started by a script, not a cron. Master Tab Filter.js decides when a re-check is worth running, which keeps the judgment next to the data.

Design intent. Stop a bad number from recycling. Reporting a wrong number is not enough on its own — without a record, the same contact gets re-enriched, re-imported and dialled again. So this poisons the number in Postgres and marks DNC, then sends the record back to Discovery for a fresh attempt at reaching the right person.

Starts

POST /wrong-number · POST /wrong-number-resolved

Writes

public.discovery (append and update) · public.codexcore (poison + DNC)

Reads

(none — the caller supplies the contact)

Counters

wrong_numbers_reported · wn_resolutions · phones_poisoned · dnc_marked · discovery_writes · discovery_deletes

Depends on

codex-postgres

Connects to

← outreach, wherever a wrong number is discovered · → Ch 10 TPS (via Discovery re-enrichment)

First symptom

The same wrong number comes back around. If poisoning fails, the number is re-enriched and dialled again — the loop closes without anyone noticing it reopened.

JSON

PkGEKxNs4au6riMt · wrong-number/sb-wrong-number.json

When outreach reaches the wrong person, the number is flagged and the record goes back to Discovery for re-enrichment. Two endpoints because it is a round trip: one to report, one to close out once a corrected number is found. Without this, bad numbers would recycle through outreach forever.

Known trade-offs.

• Poison rather than delete. The number is kept and marked, because deleting it means the next enrichment pass can rediscover and re-dial it.
• Two endpoints, because it is a round trip. One reports the problem; the other closes it out once a corrected number is found. A single endpoint could not tell the two states apart.

Upgrade surfaces.

• Automatic detection: today a human reports the wrong number. A call transcript classifier could post to the same endpoint.

Standing gaps.

• [gaps-query] Query current proposals for this section: /gaps Wrong Number (from any Cipher surface).

Design intent. The email counterpart to Wrong Number, deliberately simpler. A bad email is corrected in place on Master, because unlike a wrong phone number it does not invalidate the rest of the record — the person is still the right person, only one field was wrong.

Starts

POST /emailverify

Writes

public.master

Reads

public.master

Counters

email_repair_requests · emails_repaired

Depends on

codex-postgres

Connects to

← Ch 18 EMAIL IND (bounces) · → Ch 14 SCH LEADS

First symptom

Bounce rate holds steady while repairs report success. The correction lands on the row but the address is no better than the one it replaced.

JSON

U6S3Od4oVo4WLZW9HeSF5 · email-repair/sb-email-repair.json

Bounced or invalid addresses get re-verified and corrected in place. Writes to Master directly because an email correction does not invalidate the rest of the record — unlike a wrong phone number, which sends the whole record back.

Known trade-offs.

• Repairs in place; does not route to Discovery. A wrong phone means the contact may be wrong entirely; a bounced email usually means a typo or a dead mailbox for the right person.

Design intent. The last thing that is ours. Everything upstream decides whether a lead is worth working; this decides when — staging it on Prep so two very different consumers can take it: the 5:45 importer, and the operator with a phone. That dual audience is why the cron is parked rather than removed; the schedule exists but the judgment is human.

Starts

cron: 0 0 1 15 2 * — parked on a date that will not come round; in practice operator-driven

Writes

public.prep (append and update)

Reads

(none — it stages what enrichment produced)

Counters

skip_trace_dispatched · prep_sheet_updates · prep_sheet_appends · codex_core_appends · leads_scheduled

Depends on

/skip-trace · /dispatch-scheduled · codex-postgres

Connects to

← Ch 10 TPS Enrichment · → Ch 15 GHL IMP · → Ch 25 Call Prep Button

First symptom

Prep stops filling, or fills with stale rows. The operator notices at the phone, not in a log — nothing here errors when the staging simply does not run.

JSON

BrlWdAFQE08GtwGY · schedule-tag-leads/sb-sch-leads.json

Takes enriched, qualified leads and stages them on Prep, where TPS enrichment collects them. Prep is not the tab GHL imports from — that is Master, two stages downstream. The cron is parked on a date that will not come around soon; in practice this is operator-driven.

Prep is a waiting room, not the handoff boundary. (Corrected 2026-08-20 — the earlier text said Prep was what GHL imports at 5:45. It is not.) Leads land on Prep and wait for TPS enrichment (POST /tps, Ch 10). TPS is what splits them:

SCH LEADS → Prep → SB: TPS Enrichment Queue → Master
                                              → Disqualified
                                              → Error / Discovery

Only Master is read by GHL IMP at 5:45, and only rows with Status = ✅ and a Good_To_Process value. Prep sits two stages upstream of the handoff; the boundary is Master.

That distinction matters for anything scored or staged: a column written to Prep attaches to rows that have not been qualified yet, and is not carried across the split.

Lead quality is already settled by the time it arrives here, by three stacked filters upstream. This is a design invariant that is invisible from any single chapter, and removing any one filter would degrade the calls without any workflow reporting a fault:

1. County — the buyer-first loop (Ch 1–5) only enters counties where buyers are demonstrably closing. The market is followed, not guessed.
2. Property — buy-box filtering means desirable to a buyer who exists, rather than desirable in the abstract.
3. Motivation — high-necessity niches only: tax delinquency, pre-foreclosure, probate. Necessity for change, not merely ownership.

By the time a row reaches Prep, two of the three positions are already correct. The call itself filters for the third — is this person motivated now — and that is a conversation, not a system responsibility.

call_tier and buybox_fit are built (2026-08-20) — on Master, not Prep, and computed by Ch 10 TPS Enrichment rather than here. See that chapter.

Standing gaps. (none)

• [gaps-query] Query current proposals for this section: /gaps SCH LEADS (from any Cipher surface).

Known trade-offs.

• The cron is parked, not deleted. 0 0 1 15 2 * fires on 15 February and is effectively manual. Keeping a disabled-by-date trigger preserves the wiring for the day scheduling becomes automatic, without pretending it already is.
• Prep is written, never read. This workflow does not check what is already staged; deduplication is the importer's job, downstream.

Upgrade surfaces.

• Automatic scheduling: the parked cron is the seam — the wiring is intact.

(call_tier and buybox_fit were listed here as a future upgrade. They shipped 2026-08-20 and live on Master, computed by Ch 10 TPS Enrichment.)

Standing gaps.

• A crash is reported, a failing step is not. The workflow has settings.errorWorkflow set and five counters, but zero nodes carry an error track. A failed Prep write or a failed skip-trace dispatch is invisible; the counters simply do not increment, and nothing says why.

Design intent. The handoff. Everything before this chapter is ours; everything after belongs to GoHighLevel until someone replies. It fires at 5:45 because that is two minutes after the morning brief locks the day's agreed import number — the only workflow in the estate whose schedule is set by a conversation rather than by a system.

Starts

cron: 45 5 * * 1-6 — Mon-Sat · POST /calling_all_leads

Writes

public.imports_logbook · public.master (update)

Reads

public.master (ordered by call_tier, then motivation_score) · public.imports_logbook · public.daily_intelligence

Counters

leads_imported · import_failed · imports_requested · logbook_writes · master_discovery_deletes · textible_imported · landline_imported

Depends on

GoHighLevel · codex-postgres · PW 9 Pearl (bulk CSV path)

Connects to

← Ch 10 TPS Enrichment (via Master) · ← Ch 26 OBSIDIAN (via the import cap) · → GoHighLevel, and out of this Atlas

First symptom

The import runs and imports nothing. imports_requested holds while leads_imported falls to zero — either the 4:30 agent never wrote today's row (the filter reports stale_cap: true with the date it looked for), or Approval or Deny denied the run for its own reasons.

JSON

PIDjg86GSpVAgAIj · go-high-level-import/sb-ghl-imp.json

Pushes qualified leads into GoHighLevel, where outreach takes over. Reactivated 2026-08-18 with chapters 7 and 8 — the three together reopen the intake valve.

Features worth knowing

• It reads its own daily cap, and only today's. Daily Inteligence carries Import_Today_Textible_Target / Import_Today_Landline_Overflow, with Import_Today_Actual as the legacy single-number fallback, written by the Obsidian agent at 4:30 that morning. That is the Boss 3 Brain Stage 1 wiring: the brief decides how many leads enter, and the importer obeys. If there is no agreed number for today, it imports nothing — operator decision, 2026-08-20. A fallback would have to be capacity-based, and capacity needs the prior day's call outcomes, which are not yet recorded. The row is matched on Compose_Timestamp (full ISO date) against today in America/Chicago; Snapshot_Time carries no year and the tab is not in chronological order, so neither string matching nor row position is safe.
• Textible and landline are counted separately — they route differently on the GHL side, and one combined figure would hide a split.
• Imports LogBook is the audit trail, read as well as written, so a lead is not imported twice across runs.

Its position in the clock is deliberate and tight. The morning brief locks the agreed import number at 5:43; this fires at 5:45. Two minutes of buffer. If Obsidian's 4:30 synthesis or the 5:20 brief slips, this reads a stale or missing cap — which is why the 5 AM cluster order is load-bearing rather than incidental.

Known trade-offs.

• The cap comes from the brief, not from capacity. Written by the 4:30 agent and confirmed by the operator at 5:20; the importer obeys it. A system that sized its own batches would be simpler and would remove the operator from the decision that most determines the day. It would also need yesterday's call outcomes to size anything honestly — which is precisely what is not yet being captured, and why "no agreed number" resolves to import nothing rather than to a guess.
• Textible and landline counted separately. They route differently on the GHL side, and one combined figure would hide a split.
• Imports LogBook is read as well as written, so a lead is not imported twice across runs.

Upgrade surfaces.

• Import criteria: Filter N leads2 is where the cap is applied and where additional filtering would attach.
• A second destination: the workflow is GHL-shaped throughout; pointing it elsewhere means replacing the import call, not the selection logic.

Standing gaps.

• Two minutes of buffer, and no check that the brief happened. If Obsidian's 4:30 synthesis or the 5:20 brief slips, this reads a stale or missing cap and imports on it without noticing. Nothing verifies the number it obeys was agreed today.

Design intent. Someone texted back — decide what happens next, in seconds, without waking the operator unless it matters. Five AI agents read the message for motivation and intent, history is pulled across three tabs, and the workflow either answers or escalates. Escalation is the product, not the auto-reply: the point is that a human is interrupted exactly when a human is needed.

Starts

POST /inbound-text

Writes

public.smstatus (upsert on phone_number) · public.nimbus · public.smsretry

Reads

public.nimbus · public.stage · public.smstatus · public.nimbus_archive — that table does not exist; see the register

Counters

7 — texts_inbound · autoresponded · inbound_errors · identity_stored · dnc_triggered · …

Depends on

GHL · Telegram · Cipher · OpenRouter · codex-postgres

Connects to

← GoHighLevel (inbound SMS) · → Ch 24 GET FREE SLOTS · → Cipher · → the operator, by Telegram

First symptom

Replies arrive and nothing answers. The webhook still returns 200 because it acknowledges before the chain runs, so GHL sees success while the conversation stalls.

JSON

uJMbaZO190HdWF9W · text-inbound/sb-txt-ind.json

Someone texts back. This identifies them, pulls their history, has an AI agent read the message for motivation and intent, decides whether to auto-respond, logs everything, and alerts the operator when a human should take over.

This is the workflow every other one was measured against in the 2026-08-18 audit. 36 of its 38 external nodes route their error output into a single Format Error -> Obsidian node, which fans out to both SB: OBSIDIAN and a counter. It is the most completely instrumented workflow in the system.

Two calls to sourcing.arcosai.io/cipher-inbound are deliberately left untracked. A Cipher notification is fire-and-forget — nothing downstream breaks if it does not land. That distinction (notification vs handoff) is the rule the whole audit was built on.

⚠️ Live typo: one node points at services.leadconnec**or**thq.com — letters transposed from leadconnectorhq. That host does not resolve. Worth fixing.

Known trade-offs.

• Two Cipher calls are deliberately untracked. /cipher-inbound is fire-and-forget notification about work that already happened; nothing downstream breaks if it does not land. That distinction — notification versus handoff — is the rule the whole error-tracking standard was built on.
• History is read from three tabs, not one. Someone who texts may have already emailed or called, and a reply that ignores that reads as a machine.

Upgrade surfaces.

• A new intent class: the agent prompts and the switch that routes on their output are the two places that know the taxonomy.
• Sheets → Postgres — DONE 2026-08-24. All 17 nodes moved, smstatus gained a unique key on phone_number, and the upsert now preserves fields the payload omits.

This line used to cite public.nimbus_archive as proof the pattern worked. That table never existed. The history lookup reading it carried continueErrorOutput and had failed silently on every inbound text since the node was copied in from TXT & Call. Repointed to public.nimbus, which has all seven columns the query asks for.

Standing gaps.

• [gaps-query] Query current proposals for this section: /gaps TXT IND (from any Cipher surface).

Design intent. Three attempts, each written fresh. Six Analyst agents draft the message rather than filling a template, with separate paths for contacts who have conversation history and those who do not — because the second message to someone who ignored the first should not read like the first.

Starts

POST /outbound

Writes

public.nimbus

Reads

public.nimbus · public.stage

Counters

7 — texts_sent_day1 · texts_sent_day2 · texts_sent_day3 · send_errors · webhook_received · …

Depends on

GHL · Cipher · codex-postgres · OpenRouter

Connects to

← Ch 15 GHL IMP (a lead must be imported first) · → Ch 16 TXT IND (the reply comes back there) · → Ch 19 FOLLOWUP GENERATOR

First symptom

Attempt 2 or 3 stops firing while attempt 1 keeps working. The three counters diverging is the tell; nothing else distinguishes "nobody was due" from "the loop broke".

JSON

2yIFqcGzOXwTDFArp1KlQ · text-outbound/sb-txt-obd.json

The three-attempt outbound sequence. Each attempt runs an Analyst agent that writes the message, with separate paths for contacts who have conversation history and those who do not. Wait nodes hold the gaps between attempts.

Features worth knowing

• SMS Error Router classifies each send: Success / Rate Limited / Failed. This is the closest thing in the estate to a purpose-built error track — it predates the audit.
• It is only half-wired. The Failed fallback output is unconnected, and the Success output goes nowhere. Only Rate Limited reaches Emit Counter — send_errors.
• The four GHL send nodes are still bare. They form a single loop with the Waits, so a thrown 429 kills the run — which means send_errors never fires and Section Health shows zero send errors on a day the sends broke. This is the highest-value outstanding fix in the system.

Known trade-offs.

• Prep and send are split across two systems. This workflow composes and hands to GoHighLevel; the send, its retries and its cancellations live there. That is why per-node error tracks were deliberately not added — the failure is already caught on the far side.
• SMS Error Router classifies rather than retries. Success, Rate Limited and Failed are separated so the counters can tell them apart.
• Six Cipher writes untracked, for the same fire-and-forget reason as Ch 16.

Upgrade surfaces.

• A fourth attempt: the loop is explicit, one branch per attempt, so a fourth is a copy rather than a rewrite.
• Per-niche voice: the Analyst prompts are where tax-delinquency, probate and pre-foreclosure language would diverge.

Standing gaps.

• [gaps-query] Query current proposals for this section: /gaps TXT OBD (from any Cipher surface).

Design intent. The largest workflow in the estate, and the email twin of Ch 16. It reads across all three history tabs because someone who emails may have already texted or called — and it can bridge to SMS, switching channel when the reply suggests a text will land better than another email.

Starts

POST /couriernet

Writes

public.couriernet

Reads

public.nimbus · public.stage · public.smstatus

Counters

8 — emails_received · emails_replied · sms_bridge_triggered · couriernet_logged · telegram_notified · …

Depends on

GHL · Telegram · OpenRouter · codex-postgres

Connects to

← GoHighLevel (inbound email) · → Ch 13 SB_Email_Repair (bounces) · → Ch 17 TXT OBD (the SMS bridge)

First symptom

Email replies stop being answered while texts keep flowing. Both arrive through GHL, so a failure here looks like a quiet day rather than a broken channel.

JSON

AdlQfLEaBeat61RL · email-and-scheduler/email-and-scheduler.json

The email twin of TXT IND. Reads across all three history tabs, because someone who emails may have already texted or called and the response has to know that.

Biggest instrumentation gap in the estate: 39 external nodes, 14 tracked, 25 bare. It has a crash handler, so a total failure is reported, but 25 individual failure points are silent.

Known trade-offs.

• 91 nodes rather than several workflows. Every branch shares the same history lookup and the same identity resolution; splitting would duplicate both.
• Channel switching is one-way. Email can escalate to SMS; SMS does not fall back to email, because a text that goes unanswered means something different from an email that does.

Design intent. Write the nurture message GoHighLevel is about to send. Its Track A/B nodes call out for a body, this composes one that is stage-aware and does not repeat anything the contact has already received, and GHL sends it. Both channels live here, split by a Channel Switch — email one arm, SMS the other.

Starts

POST /followup-generator

Writes

public.couriernet · public.nimbus · public.intake_governor

Reads

public.stage · public.couriernet · public.nimbus

Counters

(none — by design; see Known trade-offs)

Depends on

GHL (two webhook triggers) · OpenRouter · codex-postgres

Connects to

← GoHighLevel (Track A/B nodes) · ← Ch 17 TXT OBD (after the initial burst)

First symptom

Follow-ups go out empty or repeat an earlier message. The GHL node fires either way, so the sequence continues and the contact receives something worse than silence.

JSON

8jvmZU086B3bOwBL · followup-generator/sb-followup-generator.json

Generates the LLM email and SMS bodies for GHL's Track A/B follow-up nodes. Reads Stage plus both history tabs so a follow-up is stage-aware and never repeats a message the contact has already received.

SB: TXT OBD is deliberately untouched by this — the initial three-attempt burst stays where it is. This handles what comes after.

⚠️ All 8 of its external nodes carry continueRegularOutput — every GHL send and every log write swallows its own errors. A follow-up can silently fail to send and the workflow reports clean. Second-highest-value outstanding fix.

Known trade-offs.

• The initial three-attempt burst stays in Ch 17. This handles what comes after; keeping them separate means a change to nurture cannot break first contact.
• Reads three tabs before writing one. Stage for where the contact is, CourierNet and Nimbus for what they have already been sent.

• It emits no counters, and that is correct. Both HTTP nodes hand to GoHighLevel, which owns the send and reports its own outcome. Counting a handoff as though it were a delivery would report volume this workflow cannot observe — the same reason Ch 34 counts presses rather than rows written.

Standing gaps.

• [gaps-query] Query current proposals for this section: /gaps FOLLOWUP GENERATOR (from any Cipher surface).

Design intent. Read a call twice, through two different lenses. An MCTP extractor pulls what the call means for the deal; a Coach extractor pulls what it means for the operator's technique. They are kept deliberately separate so sales extraction and coaching feedback cannot contaminate each other — an agent grading your delivery should not also be deciding the lead's motivation score.

Starts

POST /ghl-call-ext · manual

Writes

Stage (30 coach columns) · Intake Governor

Reads

Stage

Counters

posts to /intake-event — volume and errors, via Emit to Intake Governor

Depends on

GHL (call transcripts) · OpenRouter · Codex_Pulse

Connects to

← GoHighLevel (a completed call) · → Ch 22 BOXING COACH PRIMER (via Stage)

First symptom

Stage stops gaining coach columns after calls. The call still happened and the transcript still exists — only the extraction is missing, and nothing downstream demands it.

JSON

TgVhnTzreWsdJMjM · call-ext-ghl/sb-call-ext-ghl.json

Runs two extractors in parallel over a call transcript — an MCTP lens and a Coach lens — deliberately kept separate so sales extraction and coaching feedback never contaminate each other. Writes 30 coach columns to Stage.

⚠️ Four swallowing nodes, including Emit Error to Intake Governor — the error reporter suppresses its own failures.

Known trade-offs.

• Two extractors rather than one prompt. One agent doing both would blur the lenses, and the coaching output would start justifying the sales read.
• 30 coach columns on Stage rather than a separate tab, so a lead and its coaching history stay on one row.

• Counters go over HTTP, not through an Emit Counter node. Emit to Intake Governor and Emit Error to Intake Governor both POST to /intake-event directly; the Append to IG Sheet (Direct) node beside them is a fallback, not a substitute. Any audit scanning for nodes named Emit Counter — * will report this workflow as uninstrumented. It is not.

Standing gaps.

• [gaps-query] Query current proposals for this section: /gaps GHL Integration (from any Cipher surface).

Design intent. Ten nodes with one job: tell the operator who is calling before they pick up. Everything about it is shaped by that deadline — it writes Stage after alerting, not before, because a row can wait and a ringing phone cannot.

Starts

POST /inbound-call-notify

Writes

public.stage

Reads

(none — the payload carries the caller)

Counters

inbound_calls · caller_id_alerts · stage_writes

Depends on

Telegram · codex-postgres

Connects to

← GoHighLevel (an incoming call) · → the operator, immediately

First symptom

The phone rings with no context attached. The operator answers blind — a failure measured in seconds, and the only one in the estate where being late is the same as being broken.

JSON

SNnh9hUCc_kOXS3JCk7HC · inbound-call-notify/sb-inbound-call-notify.json

Real-time Telegram alert on an inbound call. The deleteMessage call is the interesting part: the alert is removed once it is no longer relevant, so the operator's phone does not accumulate dead notifications.

Known trade-offs.

• It deletes its own alerts. A deleteMessage call removes the notification once it is no longer relevant, so the operator's phone does not accumulate a history of calls that have already been answered.

Standing gaps.

• [gaps-query] Query current proposals for this section: /gaps GHL Integration (from any Cipher surface).

Design intent. Coaching between rounds, not after the fight. Pressed mid-block, it synthesises the calls made so far into something the operator can act on in the next call rather than review at the end of the day — which is why it reads Stage live rather than waiting for an end-of-day rollup.

Starts

POST /coach-primer — Stream Deck or /coach in Telegram · manual

Writes

public.boxingcoachqueue

Reads

public.stage

Counters

coach_presses · primers_queued · coach_errors

Depends on

OpenRouter · codex-postgres · the Cipher bridge (polls the queue)

Connects to

← Ch 20 CALL EXT (via the coach columns on Stage) · → Cipher, by voice or text

First symptom

The button is pressed and Cipher says nothing. Silence is indistinguishable from having nothing to report, so a failure looks like a quiet block.

JSON

QgwcoGKkLODod7FP · boxing-coach/sb-boxing-coach-primer.json

Synthesises the day's calls so far into a coaching primer and queues it. The Cipher bridge polls BoxingCoachQueue for Status='pending' and delivers it by voice or text.

Queue for Bridge is terminal, which made its old swallow flag pure loss: the Stage read, the LLM synthesis and the billed work were all complete, and a failed append meant Cipher simply never spoke. Silence was indistinguishable from having nothing to say. Fixed in the audit.

Known trade-offs.

• A queue rather than a direct send. The workflow writes a row and stops; the Cipher bridge polls for Status='pending'. That keeps voice delivery out of n8n and lets Cipher choose the channel.
• Two entry points, both counted. The Stream Deck button and the manual trigger both increment coach_presses, because a silently failed test press misleads exactly as much as a real one.

Design intent. Calendar access as a service, so nothing else has to hold calendar credentials. Five webhook chains, five independent paths, no shared state between them — a workflow rather than five workflows because the credential is the thing being shared, not the logic.

Starts

/scheduling/create-event get-events update-event delete-event free-slots

Writes

Google Calendar (create · update · delete)

Reads

Google Calendar (getAll) · GoHighLevel (free slots)

Counters

(none — see Standing gaps)

Depends on

Google Calendar · GoHighLevel

Connects to

← Ch 25 Call Prep Button · ← Ch 24 GET FREE SLOTS · ← Cipher · ← Ch 16 TXT IND

First symptom

Booking fails while the seller is still on the phone — the one failure in this chain that cannot go unnoticed.

JSON

YpvsiQ0ANXvJVH9o · scheduling-tools/sb-scheduling-tools.json

Full CRUD over the calendar, exposed as webhooks so any other workflow — or Cipher — can book, move or cancel without owning calendar credentials.

The end of the call chain. SCH LEADS (Ch 14) supplies the lead, the Call Prep Button (Ch 25) opens the conversation, and this is where it is meant to land. Being the terminus makes it the only step whose failure is immediately visible: the operator is still on the phone.

Known trade-offs.

• Two sources of availability. Four chains talk to Google Calendar directly; free-slots uses the native GoHighLevel node instead, because GHL owns the booking rules — buffers, working hours, round-robin — that a raw calendar read cannot see. The calendar says when you are free; GHL says when you are bookable.
• Five chains, no shared nodes. Each webhook has its own normaliser and its own responder. Duplicated by design: a change to update cannot break delete.

Upgrade surfaces.

• A sixth operation (reschedule, availability windows): add a webhook, normaliser, calendar node and responder — the pattern is uniform.
• Migrating off Google Calendar: the four calendar nodes are the only coupling; the normalisers and responders are provider-agnostic.
• The HUD is becoming the operator-facing surface for this. A calendar glance sits on the map's toolkit roster and the Call Prep Button already opens the HUD on a lead — see The HUD (after Ch 36) for what has actually shipped versus what is still a named intent.

Standing gaps.

• Nothing counts bookings. Appointments made is the outcome the entire pipeline exists to produce, and no counter measures it. Section Health can show leads in, texts sent and calls extracted, but not the one number that says whether any of it worked.

Known trade-offs (continued)

• No error track, deliberately. A failed booking surfaces instantly because the operator is on the phone with the seller when it happens. That is the strongest detection in the estate — a human watching in real time — and an error row written after the fact would add nothing they do not already know.

Design intent. Answer one question — when am I actually free? — from real freeBusy data rather than an assumption. It exists separately from Ch 23 because reading availability and mutating the calendar are different risks: this one can be called freely by anything that needs to offer a time, and cannot change anything.

Starts

called by another workflow · POST /cipher

Writes

(none — it answers, it does not record)

Reads

Google Calendar freeBusy

Counters

slot_lookups · slot_lookup_errors

Depends on

Google Calendar API

Connects to

← Ch 16 TXT IND · ← Cipher · → Ch 23 Scheduling Tools (the booking that follows)

First symptom

Offered times are already taken. The lookup answers with stale or empty availability, and the mistake surfaces as a double-booking rather than an error.

JSON

2m-4oVwYHFae2PdOXZum7 · get-free-slots/sb-get-free-slots.json

Queries real free/busy data. Callable both as a sub-workflow and directly by Cipher, which is why it has two entry points.

Known trade-offs.

• Two entry points. A sub-workflow call for other n8n workflows, and /cipher for Cipher directly. Same logic, two callers with different transports.
• Read-only by construction. It cannot book, so it can be called speculatively — during a conversation, in a draft, wherever a time might be offered.

A third surface is arriving. The HUD's map toolkit design names a day/week calendar glance as a sibling to the draw-to-filter tools — reading from whatever this chapter's availability check resolves to. Not built as of this writing; see The HUD (after Ch 36).

Starts

POST /due-diligence

Writes

No writes — returns response to Stream Deck client only.

Reads

Codex_Core Postgres via Lookup Codex Core1 node (lead-specific prep data).

Counters

calls_prepped (emitted on every button press).

Depends on

Prep (Codex_Core) · the comping tool

Connects to

← Ch 14 SCH LEADS copy · → Ch 23 Scheduling Tools

First symptom

The brief comes back thin mid-call and the operator improvises. Nothing logs it — the failure is absorbed by the person on the phone.

JSON

Ke1Y8j-QICLexxV3SkaVi · due-diligence-call-prep/due-diligence-call-prep.json

Design intent. Four nodes, pressed live. It assembles what is known about a lead in the seconds before the operator speaks, and launches the comping tool so condition and price can be worked through with the seller on the same call.

The middle of the call chain. SCH LEADS (Ch 14) supplies the lead, this button opens the conversation, and the call is meant to end in Scheduling Tools (Ch 23) with an appointment booked. The sequence is operator-mediated — none of the three call each other, which is why an automated scan reads all three as isolated.

Known trade-offs. Thin — 4 nodes, no error path. Postgres timeout returns empty brief silently; operator improvises live. No retry.

Upgrade surfaces. Add error output → SB: OBSIDIAN (matches every other chapter). Richer prep sources: last-call notes, LLC unmasker, buyer-side context.

Design intent. The place failures go to be understood rather than merely recorded. Two entry points do different jobs — Error Trigger catches a workflow that died, Execute Workflow Trigger catches a step that failed while the workflow carried on — and at 4:30 it reads everything it has collected and turns the day into the briefing the operator gets at 5:20.

Starts

cron: 30 4 * * 1-6 · POST /Count · called by another workflow · error trigger

Writes

public.obsidian_error_handler · public.nimbus_error · public.couriernet_error · public.sourcing_error · public.intake_governor · public.daily_intelligence

Reads

public.heartbeats · public.proposals · public.funding · public.section_health · the four error tables

Counters

6

Depends on

sourcing.arcosai.io/brain/daily-intel-facts · OpenRouter · codex-postgres

Connects to

← every instrumented workflow (Entry A and Entry B) · → Ch 15 GHL IMP (the import cap) · → the morning brief

First symptom

The 5:20 brief arrives thin or stubbed. Everything upstream keeps running and every error track keeps firing — they just drain into a synthesis nobody reads.

JSON

X-X2WiHq83IAkbGroxTuA · obsidian-air-handler/sb-obsidian.json

Two entry points, and the distinction is the spine of the whole audit:

• Entry A — Error Trigger (Entry A - Crashes). n8n fires this when a workflow dies. Wired by setting settings.errorWorkflow on the other workflow. Catches total failure.
• Entry B — Execute Workflow Trigger (Entry B - Item Failures). Called explicitly when a step fails but the workflow keeps running. This is what every Format Error -> Obsidian node in this document feeds.

A crash handler alone means a failing step is invisible. A track alone means a crash is invisible. Both is the standard.

At 4:30 AM it reads everything — heartbeats, errors, funding, proposals, section health — and synthesises the day into the briefing the operator gets at 5:20.

Deliberately not instrumented. It cannot report its own failures to itself without looping, and the operator's judgment is that its failure probability is low enough not to warrant a separate alert path. If Obsidian dies, every other workflow's error track drains into nothing — accepted, and recorded here so it is a known trade rather than an oversight.

Known trade-offs.

• It carries no error tracks of its own. Reporting its failures to itself would loop, so it is deliberately uninstrumented.
• All 14 Sheets nodes carry retryOnFail — 3 tries, 2s backoff — because socket drops on Sheets writes were its most common failure and a retry solves what a report only describes.
• Four error tabs, not one. Sourcing, Nimbus, CourierNet and the master tab are separated so a domain-specific retry can walk one without reading the others.

Upgrade surfaces.

• A fifth domain: add a tab and a route in the formatter's provider mapping.
• A dead-man's switch in Apps Script. See Standing gaps — the seam already exists.

Error hygiene — the standing rule.

A row in obsidian_error_handler is a claim that something is still wrong. If that stops being true and nobody says so, the count becomes noise and the operator learns to ignore it — which costs more than having no count at all.

When you fix something, close its row in the same pass as the fix. Not later, not "when we do a sweep". The fix and the close are one piece of work.

UPDATE public.obsidian_error_handler
   SET status = 'CLOSED',
       resolution_notes = '<what was actually wrong> — fixed <date>: <what changed>'
 WHERE _id IN (...);

Rules that make the count trustworthy:

• Close on fix, not on age. Old and quiet is not the same as fixed. SB: PREP LEAD HANDLING ran healthy every 15 minutes for days while failing at 05:30 daily — anything that closed it on age would have buried a live bug.
• Always write resolution_notes. A bare status flip erases why. The note is what makes a wrong close findable and reversible.
• Close every row of an incident, not just the one you read. One task-runner stall on 2026-08-31 produced seven rows across two workflows in the same minute. Fixing the cause fixes all seven.
• Never close what you have not fixed. A row you cannot explain stays open. Tidying the number is the one thing this rule exists to prevent.
• Operator-error rows still get closed — with a note saying so. [] and [errorMessage, ...] from the capture button were the guard working correctly; they are not defects, and they are not open problems either.

What closes rows automatically. SB: ERROR REAPER (TXScGI5XKskc6zQJ, 06:30 Mon-Sat) closes a row only when its exact signature — workflow + node + digit-normalised message — has been quiet for 3 days and the workflow has emitted at least 3 sensor events since. Throughput alone is never enough: it proves the workflow is alive, not that the cause was addressed. Everything it closes carries its evidence in resolution_notes.

The reaper is a backstop for rows that quietly stopped mattering. It is not a substitute for closing what you fixed — it cannot know a fix happened, only that a failure stopped recurring.

The HUD needs nothing. It re-reads this table filtered on status every five minutes. The database is the single source of truth; write CLOSED and the count falls on its own.

How its death is detected. An Obsidian watchdog lives in Section Health Aggregation Engine.js — deliberately outside n8n, because a watcher inside the failure domain it watches is no watcher at all. It checks this workflow's last execution against a 2-hour lookback and alerts Cipher directly by Telegram, with a cooldown so it does not spam while n8n is fully down. An unreachable n8n is the true dead-man case and is alerted too.

Its secrets live in Script Properties (SH_N8N_API_KEY, SH_CIPHER_TOKEN, SH_CIPHER_CHAT); until all three are set it no-ops silently.

Known trade-offs.

• Only two counters, and that is honest. n8n never learns how many rows landed, because the write happens on another host. A fabricated "written" counter would report success this workflow cannot observe.
• A fire-and-forget call that still gets an error track. Unlike the Cipher notifications elsewhere, the POST here is the work request — if it fails, the scrape never starts and the operator sees an inert button.

Standing gaps.

• [gaps-query] Query current proposals for this section: /gaps OBSIDIAN (from any Cipher surface).

Design intent. One insert, deliberately fail-open. Every counter in the estate posts here, and telemetry must never block the workflow reporting to it — so it answers {ok:true} whether or not the row landed. That choice is why the response shape matters: {"ok":true,"id":"9274"} is a real insert, {"ok":true} alone is a dropped one.

Assert Row Landed closes the loop that choice used to leave open. Build Response always knew whether a row came back — it tests id != null — but only used it to decorate the response, so a dropped insert looked exactly like a good one and the July outage hid for four weeks. The assert now reads that same fact and throws, routing to Report Drop -> Obsidian. The caller still gets {ok:true}; the difference is that a drop now leaves a trace.

Starts

POST /intake-event

Writes

observability.intake_events

Reads

(none)

Counters

(none — by design; a counter here would POST to itself)

Depends on

codex-postgres

Connects to

← every counter in the estate · → Ch 28 Intake Aggregation

First symptom

Counters return {ok:true} with no id. The response shape is the only tell — a real insert echoes a row id and a timestamp.

JSON

x9pUbtA6gk5p5EXI · intake-governor/sb-intake-event-receiver.json

Known trade-offs.

• No error counter, on purpose. A counter here would POST to /intake-event, which is itself; a Postgres outage would loop until n8n fell over. It reports to Obsidian alone, which writes to Sheets — a separate dependency, reachable precisely when Postgres is what broke.
• Fail-open rather than fail-loud. The alternative would make a telemetry outage into a business outage.
• Two detectors, deliberately separate. Insert Intake Event's error track catches a thrown failure; Assert Row Landed catches the other case — the insert returning without error and without a row, which is what a fail-open wrapper around a dead connection produces. They report through different nodes so neither can trigger the other: the shared error formatter feeds Build Response (that is how the caller still gets {ok:true} when the insert throws), so routing the assert into it would close a cycle whose entry condition is the very outage it exists to catch.
• Respond OK builds its body explicitly rather than echoing $json. The drop flag has to live inside json to survive the node boundary — n8n normalises Code-node items to {json, pairedItem} and discards sibling keys — so the response is a declared shape, not whatever happens to be on the item.
• A malformed payload is still inserted, on purpose. Validate + Defaults stamps workflow/sensor as (missing) rather than rejecting, so a broken emitter leaves greppable evidence instead of vanishing. The row is the only trace that something is misconfigured; refusing it would delete the evidence. The caller is now told — {"ok":true,"skipped":true,"reason":"..."} — which it previously was not: the validator's _ok flag does not survive the Postgres node (RETURNING replaces the item), so Build Response reads the verdict from $("Validate + Defaults") directly.

Measured 20 Aug 2026. 9,422 rows since 19 May; 4 junk rows, 0.042%, every one a test probe. Section Health totals are not meaningfully polluted. Retention prunes at two years, so today's test rows age out in 2028 — not worth a bulk delete against a live table.

Standing gaps. (none)

• [gaps-query] Query current proposals for this section: /gaps Air Handler (from any Cipher surface).

Design intent. Turn a raw event stream into something a person can read. Four times a day it collapses intake_events into per-sensor daily counts and writes them to two independent stores — Postgres for querying, the sheet for reading — neither feeding the other.

Starts

cron: 0 0,9,14 * * * · cron: 55 19 * * * — America/Chicago

Writes

observability.intake_slots_daily · public.intake_governor

Reads

observability.intake_events

Counters

slots_upserted · ig_rows_written · aggregation_errors

Depends on

codex-postgres

Connects to

← Ch 27 Intake Event Receiver · → Ch 26 OBSIDIAN (via Intake Governor)

First symptom

Section Health shows yesterday. Nobody waits on this workflow, so a failure produces no visible symptom — the dashboard keeps its previous values and looks plausible.

JSON

ugU0TZ2XebuodbUs · intake-governor/sb-intake-aggregation.json

Known trade-offs.

• Two sinks, counted separately. One can fail while the other succeeds, leaving the stores disagreeing with nothing to reconcile them. Separate counters are what expose that split.
• No "received" counter. The trigger is a cron; a fire that produced nothing already shows as the absence of the two write counters.
• The :55 timing is intentional. It feeds the four-times-daily funding numbers into the evening block. Funding Tracker moved to 19:50 rather than this one moving, so the two no longer contend for codex-postgres.

Standing gaps.

• [gaps-query] Query current proposals for this section: /gaps Air Handler (from any Cipher surface).

Design intent. One bounded DELETE, keeping intake_events from growing without limit — a slow problem that only becomes visible long after it starts.

Starts

cron: 0 3 * * *

Writes

(deletes from observability.intake_events)

Reads

observability.intake_events

Counters

prune_runs (a heartbeat) · prune_errors

Depends on

codex-postgres

Connects to

← Ch 27 (the table it prunes)

First symptom

None, for about two more years. Its correct result today is deleting zero rows, so a total failure and a perfect run are indistinguishable until the table is old enough for the prune to matter.

JSON

xTY6yrFT3rOftedG · intake-governor/sb-intake-retention.json

Known trade-offs.

• prune_runs is a heartbeat, not a row count. Counting rows_deleted would read 0 forever on healthy runs and be indistinguishable from the workflow being switched off. Proving it ran is the only useful signal.
• Graded warning, not critical. A failed prune loses nothing; the rows simply remain. Grading it critical would teach the operator to discount critical elsewhere.

Standing gaps.

• [gaps-query] Query current proposals for this section: /gaps Air Handler (from any Cipher surface).

Design intent. Ask the one question nothing else can: are the browser sessions still logged in? The probes do no scrape work — each opens a persisted profile and checks the target does not redirect to a login page, or calls a credential-gated API. An expired cookie is invisible to every other form of monitoring and is the most common way the Playwright fleet dies.

Starts

cron: 0 5 * * 1-6 — Mon-Sat

Writes

public.heartbeats (upsert on script_name)

Reads

(none — the probes report)

Counters

heartbeats_recorded · heartbeat_errors

Depends on

PW 4 heartbeats · sourcing.arcosai.io/heartbeats/fire-all · codex-postgres

Connects to

← PW 4 (the 8 probes) · → Ch 26 OBSIDIAN (read at 4:30)

First symptom

Every script shows a healthy timestamp. When this fails the tab keeps yesterday's values, so the dashboard for detecting deadness looks fine precisely when it has stopped working.

JSON

UQyuPu9YA2dZzhzj · intake-governor/sb-heartbeats.json

Known trade-offs.

• Deliberately lightweight probes. They confirm the environment, not the work. A probe that actually scraped would be slower, costlier, and would itself need monitoring.
• A 10-minute timeout for 8 serial probes. Exceeding it means the sourcing server is wedged rather than slow, which is why that failure is graded critical without hedging.

Upgrade surfaces.

• A ninth probe on Gmail IMAP. xleads-auth.js is a single choke point for nine scripts and nothing watches it; the verification-code path failing would take out the whole fleet at once with no signal.

Standing gaps.

• [gaps-query] Query current proposals for this section: /gaps Playwright Fleet (from any Cipher surface).

Design intent. Poke the Apps Script that closes the books, so the 4:30 synthesis has a finished day to read. Three nodes, and the interesting one is the third: a response check, because this is the only external call in the estate that lies about failure.

Starts

cron: 55 23 * * *

Writes

(none directly — the Apps Script writes)

Reads

(none)

Counters

eod_rollups_confirmed · eod_rollup_errors

Depends on

the Section Health Apps Script Web App (/exec)

Connects to

→ the Apps Script Web App · → Ch 26 OBSIDIAN (reads the rollup at 4:30)

First symptom

The morning brief reads yesterday's rollup. Apps Script answers 200 even when it refuses the action, so a failure looks identical to success unless the response body is read.

JSON

hBI5pZKxPPXluEWA · intake-governor/sb-eod-rollup-poke.json

Known trade-offs.

• A response check, not just an error track. Apps Script Web Apps answer HTTP 200 and put the failure in the body. An error track alone would never fire, so Check Rollup Response reads the body and throws. This is the only place in the estate using the pattern — see the silent-vendor-response class.
• A timeout does not prove failure. The node waits 30s; Apps Script runs up to six minutes. The error message says so, and warns to verify before re-running to avoid double-counting.
• retry_eligible: FALSE. Nothing re-pokes this. Tomorrow's fire rolls up tomorrow, so a missed night stays missed unless run by hand.

Upgrade surfaces.

• The stale-deployment trap: /exec serves a frozen snapshot, so editing the script without redeploying leaves this hitting old code and answering 200. The response check is what turns that into a visible event.

Design intent. Four pit stops a day across five vendors, with edge-triggered alerts when a balance crosses a threshold — because the failure mode being guarded against is not overspending, it is discovering at 5 AM that a vendor stopped working overnight.

Starts

four crons — 05:05 · 09:30 · 14:15 · 19:50

Writes

public.funding · public.funding_state (upsert on key)

Reads

public.funding_state · public.intake_governor

Counters

posts to /intake-event via Emit Funding Counts and Emit Spend Counters

Depends on

OpenRouter · Twilio · Telegram · PW 7 openai-balance-hybrid · PW 8 CaptainVerify · codex-postgres

Connects to

← five vendor APIs · → the operator, by Telegram

First symptom

A vendor reads zero and stays there. Nothing errors — a wrong field returns a valid number, and the balance simply stops moving.

JSON

qm4afhjKzBHollxZ · Funding Tracker/SB_ Funding Tracker.json

Known trade-offs.

• All ten spend categories are snapshots, not increments. Aggregate with latest_per_day, never sum. This is the workflow where reading the wrong field — per-key limit-usage instead of account total_usage — made OpenRouter spend read zero for weeks.
• Counters emit over HTTP through Emit Funding Counts and Emit Spend Counters rather than nodes named Emit Counter. An audit scanning by node name will report this workflow as uninstrumented. It is not.
• Edge-triggered alerts, not level alerts. A balance crossing a threshold fires once; a balance sitting below it does not fire every pit stop.
• The evening pit stop is 19:50, not 19:55 (moved 2026-08-19). Both this and Ch 28 wrote to codex-postgres at :55, and a funding write landing mid-aggregation made that pit stop's counts vanish.

Standing gaps.

• [gaps-query] Query current proposals for this section: /gaps Funding Tracker (from any Cipher surface).

Design intent. Judge community chatter before it becomes noise. An LLM gatekeeper decides what is worth keeping and routes knowledge one way, people the other — the two are counted separately because a tip and a lead are different products consumed by different processes.

Starts

POST /jv-signals

Writes

public.jv_tips · public.jv_leads

Reads

(none — the payload carries the comments)

Counters

signals_received · jv_tips_written · jv_leads_written · jv_signal_errors

Depends on

OpenRouter · Community Comments sheet · PW 6 skool-discovery

Connects to

← Ch 34 COMMUNITY COMMENTS (via the sourcing server)

First symptom

signals_received stops incrementing while pull_button_pressed keeps rising. Neither workflow errors — the gap between the two counters is the only evidence the pipe broke.

JSON

pqrk5qbGIl5186o5 · community-comments/sb-jv-signals-skool-surface.json

Known trade-offs.

• The gatekeeper judges the whole batch. If it fails, every signal from that pull is lost at once rather than one row — which is why its failure is graded critical.
• Tips and leads counted separately. One combined figure would hide a routing failure that sends every signal to the wrong side.

Standing gaps.

• Route by tab declares a fallback output that is unwired. An item matching neither branch disappears. Nothing falls through today because the upstream code only ever emits JV Tips or JV Leads, but the shape is the same one that left TXT OBD's Failed output unconnected.

Design intent. Two nodes: deliver a button press. The scrape, the gatekeeper and the sheet write all happen on the Mac; n8n's only job is to ask. The server answers {"status":"started"} immediately, which is why the 15-second timeout is generous rather than tight — it waits on the acknowledgment, not the work.

Starts

POST /9ecdd88e-… — the pull button

Writes

(none — the sourcing server writes)

Reads

(none)

Counters

pull_button_pressed · pull_errors

Depends on

sourcing.arcosai.io/community-comments · PW 6 skool-discovery

Connects to

→ the sourcing server → Ch 33 JV SIGNALS

First symptom

The button is pressed and no rows appear. The POST is acknowledged in under a second regardless, so the button feels like it worked.

JSON

J8HvNUT37g0EKFhs · community-comments/sb-community-comments-pull-button.json

Known trade-offs.

• Only two counters, and that is honest. n8n never learns how many rows landed, because the write happens on another host. A fabricated "written" counter would report success this workflow cannot observe.
• A fire-and-forget call that still gets an error track. Unlike the Cipher notifications elsewhere, the POST here is the work request — if it fails, the scrape never starts and the operator sees an inert button.

Design intent. Replaces two Apps Script functions — runHourlyRollup and runEndOfDayRollup — which were byte-for-byte the same computation on different clocks. One workflow, two crons. It reads 30 days of intake events, computes 44 metrics, and writes the results back.

It matters more than its size suggests. Cipher reads Section Health for the morning brief. Before this, that read went through an Apps Script web app serving a sheet with no writer — a snapshot frozen in place while the business moved. The brief was quoting history and calling it state.

Starts

Cron hourly · cron 23:50 America/Chicago

Writes

section_health — computed columns only

Reads

intake_governor (30-day window) · section_health (definitions)

Counters

(none — it is the measurement layer, not a thing measured)

Depends on

Intake Governor events being emitted by the 18 instrumented workflows

Connects to

→ Cipher's morning brief · → The HUD (below Ch 36) — its gauges band and executions drawer read this table live

First symptom

Metrics all read 0 while events are clearly arriving. Means a category name in the definitions no longer matches what the emitters send.

JSON

uxTQqxtmJIQ5sMEP

Two rules carried over exactly, because getting either backwards is silent.

• spend_* categories use the literal delta, and 0 is a real value. Everything else falls back to 1 when the delta is blank, because a counter event with no delta still means "one occurrence". Backwards, this inflated Today to $1 on genuinely zero-spend days.
• latest_per_day keeps only the newest event per calendar day, then sums across days. Spend is a running total per pit stop, so summing every row multiplies by the number of pit stops.

Known trade-offs.

• The 44 metric definitions were extracted from the Apps Script, not retyped. A mistyped category does not error — it silently returns 0, and the metric reads as dead rather than broken.
• The write names only computed columns. The Apps Script read the whole block, mutated it in memory and wrote it back with setValues, so an operator edit landing mid-run was clobbered. Section, Metric, Channel, Direction and Industry baseline are now untouchable by construction.
• Verified against an independent calculation, not "it ran". A hand-written SQL query put OpenRouter's 7-day spend at 4.6412; the workflow returned 4.641227150000001 — the hardest metric in the set, and the one where latest_per_day had to collapse multiple events per day correctly.

Standing gaps.

• [gaps-query] Query current proposals for this section: /gaps Air Handler (from any Cipher surface).

Design intent. Postgres lives behind SSH; the Mac running Cipher cannot dial it. n8n sits inside that boundary and holds the CodexLibrary credential. This is one authenticated endpoint that runs one SQL statement and returns rows.

The alternative was handing Cipher an n8n admin API key — which is how the migration tooling reaches Postgres, by building a throwaway workflow per query. That would give her full control of every workflow in the estate in order to run a SELECT.

Starts

POST /webhook/cipher-sql — header auth, X-Cipher-Key

Writes

anything the statement writes

Reads

anything the statement reads

Counters

(none)

Depends on

the CodexLibrary Postgres credential · the header-auth credential

Connects to

← telegram_bridge.py

First symptom

403 Authorization data is wrong! — the token in the bridge and the token in the n8n credential have diverged.

JSON

KGvoOeC7NJXWV5fX

Access is arbitrary SQL. The guard is about accidents, not permission.

The operator chose full SQL deliberately so repair work can happen in conversation. The realistic failure is therefore not a stolen token — it is an LLM composing UPDATE section_health SET tier = 'low' and forgetting the WHERE, which in Postgres hits every row and cannot be undone.

A denial happens before the database is touched and says why. The guard strips strings and comments before checking, so a WHERE inside a quoted literal cannot satisfy it and -- WHERE id=1 cannot disguise a bare DELETE.

Known trade-offs.

• It is the only authenticated webhook in the estate. Every other one is open on a public domain, which is survivable for a purpose-built endpoint and not for one that runs arbitrary SQL.
• No bind parameters. $1 has nothing to bind against; callers inline literals with escaped quotes. The limitation is real and permanent.
• Rows cap at 500 and the reply says truncated. A silent cap would have Cipher reason confidently about a partial answer. An uncapped SELECT * on lis_pendens — 3,971 rows — would blow her context and cost real money.
• It shipped with two defects that only firing it revealed. The webhook had responseMode: lastNode while also wiring a Respond to Webhook node, which n8n rejects outright — every request returned HTTP 500 before the guard ran. And a failed query returned ok: true with the error buried in rows[0], because n8n delivers a continued-past error as an ordinary item. The gateway built to give Cipher true state was itself reporting success on failure. Both fixed; callers may now trust ok.