Skip to content
Ordermatic Docs

Gopher Research Agent Architecture

Gopher Research Agent

Section titled “Gopher Research Agent”

Gopher is Ordermatic’s operational mascot and research agent. He equips operations managers, customer service representatives (CSRs), and sales engineers to “dig into” enterprise data, investigating order histories, customer pricing tiers, inventory levels, invoice aging, and processing queues without slow manual ERP exports or fragile spreadsheets.

                   ┌──────────────────────────────────────────────┐
                   │                 UI Surfaces                  │
                   │  - Floating Review Companion (review.astro)  │
                   │  - Inbox work-item discussion (@gopher)      │
                   │  - Scheduled audit results (/gopher/audits)  │
                   └──────────────────────┬───────────────────────┘
                                          │
                                          ▼
                   ┌──────────────────────────────────────────────┐
                   │             apps/webapp (Astro SSR)          │
                   │  - POST /api/gopher/chat (SSE or JSON)       │
                   │  - GET  /api/gopher/threads[/:threadId]      │
                   │  - GET/POST/DELETE /api/gopher/insights      │
                   │  - Clerk org auth, flag gate, IDOR guards    │
                   └──────────────────────┬───────────────────────┘
                                          │
                        Temporal Execute  │ (gopher-interactive queue)
                                          ▼
┌─────────────────────────────────────────────────────────────────────────────────┐
│                      apps/temporal-worker (Python Runtime)                       │
│                                                                                 │
│   ┌─────────────────────────────────────────────────────────────────────────┐   │
│   │  GopherResearchWorkflow / execute_gopher_research                       │   │
│   │  ScheduledGopherAuditDispatcher  ──►  ScheduledGopherDormancyAudit-     │   │
│   │    (one child per active connection)   Workflow / ScheduledGopher-      │   │
│   │                                        PriceLeakageAuditWorkflow        │   │
│   │                                                                         │   │
│   │   ┌─────────────────────────────────────────────────────────────────┐   │   │
│   │   │                      packages/gopher-py                         │   │   │
│   │   │                                                                 │   │   │
│   │   │   ┌──────────────────────┐          ┌───────────────────────┐   │   │   │
│   │   │   │ GopherAgent          │          │ sqlglot AST Parser    │   │   │   │
│   │   │   │ (Mirascope >= 2.3)   │ ───────► │ (Read-Only Validator) │   │   │   │
│   │   │   └──────────┬───────────┘          └───────────┬───────────┘   │   │   │
│   │   │              │                                  │               │   │   │
│   │   │              ▼                                  ▼               │   │   │
│   │   │   ┌─────────────────────────────────────────────────────────┐   │   │   │
│   │   │   │ DuckDB in-memory session per connection, warm 300s      │   │   │   │
│   │   │   │ Image-baked httpfs + iceberg extensions                 │   │   │   │
│   │   │   │ Per-tenant views over Gold Parquet, fixtures in demo    │   │   │   │
│   │   │   └─────────────────────────────┬───────────────────────────┘   │   │   │
│   │   └─────────────────────────────────┼───────────────────────────────┘   │   │
│   └─────────────────────────────────────┼───────────────────────────────────┘   │
└─────────────────────────────────────────┼───────────────────────────────────────┘
                                          │
                      ┌───────────────────┴───────────────────────────┐
                      ▼                                               ▼
┌───────────────────────────────────────────┐   ┌───────────────────────────────────────────┐
│   Cloudflare R2 / Local Disk Gold Layer   │   │    Webapp internal API (bearer token)     │
│  s3://{bucket}/gold/{table}/**/*.parquet  │   │   live ERP reads + operational queues     │
└───────────────────────────────────────────┘   └───────────────────────────────────────────┘

1. Architectural Principles

Section titled “1. Architectural Principles”
  1. Zero new HTTP microservices. Gopher adds no standalone service. Execution is hosted inside existing runtimes: Astro SSR (apps/webapp) and the Temporal Python worker (apps/temporal-worker).
  2. Direct columnar analytical queries. Analytical queries run against the Gold Parquet layer with embedded DuckDB. No gold records are duplicated into a secondary OLAP store.
  3. Tenant isolation at the session layer, in two of the three deployment modes. Every session is an in-memory DuckDB database built for one connection in packages/gopher-py/gopher/session.py and shared only by runs on that same connection (see Warm sessions), so the only names that resolve are the Gold model names the session itself creates. In cloud and local mode each model that has data becomes a view whose definition ends in WHERE connection_id = '<tenant>', and a model with no data becomes an empty typed table that holds no rows to leak. demo mode is not a tenant boundary: _setup_mock_fixtures seeds five unfiltered fixture tables whose rows all carry connection_id = 'conn-default', and it ignores the connection id it is handed. Generated SQL is additionally parsed with sqlglot to reject mutations, multi-statement injections, and filesystem or settings access.
  4. Read-only ERP safety. Live ERP calls are read-only and capped at 5 calls per conversation turn by a shared counter (LiveErpLimiter, packages/gopher-py/gopher/tools/live_erp.py), so a chatty model cannot hammer a customer’s on-premise ERP.

2. Surfaces

Section titled “2. Surfaces”

A. Floating review cockpit companion

Section titled “A. Floating review cockpit companion”

  • Location: GopherReviewCompanion.tsx, mounted at fixed bottom-16 right-6 z-40 on apps/webapp/src/pages/orders/pdf/[documentId]/review.astro, behind the gopher_companion_v1 flag.

  • Ambient document context: sends only context.document_id and context.order_id (the extracted order on screen, read from ?order= at send time so an in-page order switch is honored). POST /api/gopher/chat treats them as hints: resolveReviewContext in apps/webapp/src/lib/gopher/review-context.ts reads, only for a document the caller could open on the review page (org, plus ownership or inbox.view.all for personal documents):

    • the document filename, customer, ERP customer id, PO number, and status;
    • the reviewed order’s live line items: ERP item id, name, customer part, quantity, PO UOM, and the PO’s own unit price (extracted_unit_price, divided by its price_unit basis). The listing stops at 40 lines;
    • a PO total over every live line, saying how many lines have no PO price.

    It prefixes that as a Context: ... line to the question sent to GopherResearchWorkflow. Document text is stripped of control and invisible characters and quotes. Ids that do not resolve are dropped, and the thread stores only verified ids.

  • Two-state adaptive canvas: a 430px floating flyout with ambient prompt chips, expanding to a 640px docked side rail with room for wide data tables without colliding with the sticky ReadinessFooter submit CTA. The chips each name the check they run against the context above:

    • “Compare prices to past orders”: PO price against what the customer last paid.
    • “Check prices against our pricing”: get_item_price at the ordered quantity.
    • “Check stock on hand”: dim_inventory as of the last sync. Shown only on Prophet21 and WhereFour connections (inventorySynced), since Eclipse E4 syncs no inventory.
    • “Check credit and open AR”: credit limit, hold status, open AR and aging, and whether the PO total would exceed the limit.

  • Chip fast path: with gopher_chip_fast_path_v1 on, a chip sends its id (past_prices, our_pricing, stock, credit) as quick_action, and packages/gopher-py/gopher/quick_actions.py answers it with fixed, parameterised SQL (or get_item_price per line for our_pricing) plus one model call to word the answer, instead of the agent loop. The route sends it only on a review_companion turn whose document resolved to an order with line items (the customer chips answer “match the customer first” without a lookup when no ERP customer is matched); the customer id, line items, PO total and received date (quick_facts) come from resolveReviewContext, never the request body. Typed questions always take the agent loop. The rules the fast path keeps:

    • Past prices read only fact_order_lines rows dated at least a day before the document arrived (its UTC date, minus one day for ERPs that store a local order date), so a submitted copy of this PO is never its own last price. More than 5% away from the last price is flagged.
    • The PO price is the price the document stated (extracted_unit_price), never a price auto-pricing filled into an unpriced line.
    • Our pricing matches within 0.5% of the get_item_price result.
    • Stock sums qty_on_hand across every location, and says so. Its units are never reported as verified, because P21 keeps on-hand in the stocking unit while dim_products holds the selling unit.
    • Our pricing runs at most 8 price lookups at once.
    • A line whose PO unit differs from the ERP unit (dim_products.unit_of_measure, or the unit the price lookup returns) gets no verdict. A unit that cannot be confirmed is reported as unverified. Common spellings (EACH/PC as EA, CASE as CS) count as the same unit.
    • Credit over_limit is unknown, not false, when the customer has no credit limit or any line lacks a PO price (no full PO total).

    It falls back to the agent loop with the original question when the facts are incomplete or the order has more than 40 lines, when a table the chip needs is an empty stand-in (fact_order_lines; dim_inventory; dim_customers and fact_ar_aging), when a query or price lookup fails, when no line matches an inventory row or no line has any prior order, or when the model call returns nothing. A fast-path answer reports rounds = 1.

  • What a turn renders: the running thought line, assistant text, and any tabular artifacts with a CSV download. On a failed excavation the companion renders the error’s problem and remediation as the assistant bubble.

  • No provenance panel. The companion used to carry an “Inspect Excavation” tray advertising freshness, tables scanned, and last executed SQL. Every value in it was invented in the component, so it was deleted rather than left to look authoritative. Nothing on this surface reports which tables a turn read or what SQL it ran.

  • Sprite rig: 16-frame GIF loops at 130ms per frame under apps/webapp/public/sprites/gopher/, with PNG and WebP frame sets alongside. One GIF exists per MascotState in @repo/contracts: idle, digging, thumbs_up (success.gif), alert, sleeping, investigating, typing, confused, coffee, peek, confetti.

    Five of the eleven have a setter. The companion sets digging on send and idle when the fetch itself throws, and adopts the mascot_state carried by any SSE event it parses. A local AFK timer sets sleeping after 120s with no mousemove, keydown, wheel, or pointerdown, and returns to idle on the next one. /api/gopher/chat emits digging while the workflow runs, then the terminal state the agent reported: thumbs_up on success, alert on a failed excavation, or idle when the route itself threw before the agent could report. The remaining six states have render arms in the component and no setter anywhere in the repo.

B. Inbox work-item discussions

Section titled “B. Inbox work-item discussions”
  • Work-item notes are a discussion stream where CSRs can chat with teammates and mention @gopher (apps/webapp/src/lib/gopher/inbox-discussion.ts).
  • A mention runs GopherResearchWorkflow with the work item’s context and posts the answer back into the same discussion as a Gopher-authored note (author_kind = 'gopher', author_user_id = NULL) that notifies the asker.
  • One inbox_collab thread per work item, enforced by a partial unique index. Each reply note’s id is a deterministic UUIDv5 of gopher-reply:<userNoteId> inserted with ON CONFLICT DO NOTHING, so a retried route or workflow cannot double-post.

C. Scheduled audit results

Section titled “C. Scheduled audit results”
  • /gopher/audits lists persisted findings from the scheduled warehouse audits, grouped by run, newest first. The page requires both gopher_companion_v1 and gopher_scheduled_audits; otherwise it redirects to /inbox.

D. Chief of Staff team activity chat

Section titled “D. Chief of Staff team activity chat”
  • /gopher/chief-of-staff is a full-page studio (ChiefOfStaffStudio in apps/webapp/src/components/gopher/chief-of-staff/) on the chief_of_staff surface: a catalog of questions, saved insights and threads, the conversation, and a canvas for the selected table. The SSE client is the shared useGopherChat hook. Org admins ask about each team member’s orders submitted, inbox actions and manual overrides, and get answers with charts and tables that show names instead of user ids.
  • The canvas shows only what the answer carries: the table, the question, the answer time, and the worker’s provenance sentence verbatim. For a get_team_bandwidth table it also lists facts computed from the rows (team-workload-facts.ts): the largest count in each column with every tied name, and who has nothing recorded in any column, each followed by the window label. It states nothing for any other table, for a label without a real time zone, or when a cell is missing or not a whole number. No fact calls anyone idle, adds columns or people together, or names a display name that two rows share.
  • Saved insights live in gopher_saved_insights, one row per user, assistant message and artifact index. The row is a reference, never the artifact’s rows. GET and POST /api/gopher/insights and DELETE /api/gopher/insights/{id} accept a signed-in session only, scope every row to the caller’s user and org, and read the artifact back from the thread through the same visibleTo rule. A saved insight therefore disappears when its creator loses analytics.view.org, and is deleted with its thread. GET returns the newest 100 insights with their tables (GOPHER_SAVED_INSIGHTS_LIMIT) and, in saved, a reference to every saved artifact, uncapped, so the canvas never calls an older save unsaved. It answers 500, not an empty list, when Clerk cannot answer the role lookup: the viewer check passes throwOnError down to getUserOrganizationRole, which otherwise reads a Clerk error as “not an admin”. A 500 body carries a trace id; the error itself stays in the log.
  • GET /api/gopher/threads?surface=chief_of_staff also answers 500 on that failure, instead of listing no threads. GET /api/gopher/threads/{id}?surface=... 404s a thread from another surface, and useGopherChat refuses one too, so a Chief of Staff link cannot open an org-shared review thread and write team answers into it.
  • The chat stream names the thread on its first event and the persisted assistant row (message_id, with its stored message_created_at) on every text_delta and artifact event, because the route writes the turn before it streams it. A client that loses done still sends its follow-up to the same thread and can still save the answer. The answer time shown is the stored one, never the viewer’s clock. Errors the route sends carry a problem, a remediation and a trace id; the cause (a failed query names its SQL and params) is only logged.
  • The page needs the analytics.view.org inbox capability and gopher_companion_v1; without either it redirects to /inbox. POST /api/gopher/chat refuses to open a new chief_of_staff thread for anyone else with a 403.
  • The data comes from the get_team_bandwidth tool (packages/gopher-py/gopher/tools/operational.py), which reads GET /api/gopher/bandwidth (apps/webapp/src/pages/api/gopher/bandwidth.ts) for a today, 7d or 30d range. Its table has the columns Team member, Orders submitted, Inbox actions and Manual overrides. Team activity counts says what each one counts.
  • The internal service token alone reads nothing from /api/gopher/bandwidth. When an org admin sends a turn in a chief_of_staff thread, chat.ts mints a team grant in Redis (apps/webapp/src/lib/gopher/team-grant.ts, 40 minute TTL) and passes it to the workflow. The worker sends it back in the x-gopher-team-grant header, and the route accepts it only while it is live and names the same connection and org. Any other surface, including an admin in the review companion, runs without the tool. Open follow-ups for this grant are tracked in TODOS.md under Security.

Volume and usage reports

Section titled “Volume and usage reports”

The two reports are separate artifacts on purpose. Sales gets the operational CSV. The billing statement is the invoice basis and the only one with money columns. They are never in one file.

  • get_volume_report runs bp_ops_volume_report through the executor with start_date and end_date, both YYYY-MM-DD. It defaults to all synced history. It groups by customer only, because gold has no source or status column.
  • get_usage_statement reads app Postgres through the service-token route GET /api/gopher/usage-statement. The route verifies the org and connection and checks the flag. Intake source is work_items.source_type (email, upload, clone, voice, zendesk, sms). EDI is not a category because it is not persisted as a source.
  • The billing statement filters on billing_period_start in whole UTC months. It shows each event’s effective_per_line_item_rate, which is null for events written before that column existed and for a null plan. Events with no billing period are excluded and counted in events_without_period. The statement caps at 5000 rows.
  • Both Postgres reports cover the whole organization, not one connection.

Team activity counts

Section titled “Team activity counts”

Window. Each range covers whole local dates in one time zone: today is today’s date, 7d is today and the 6 dates before it, and 30d is today and the 29 dates before it. The window starts at the first instant of its first date (calculateDateCutoff) and ends when the counts are read. Where a daylight saving change skips local midnight, as in Asia/Beirut and Africa/Cairo when clocks spring forward, that date starts at 01:00. The time zone is the admin’s browser zone: useGopherChat sends it with every turn, chat.ts stores it in the team grant, and the route uses the grant’s zone over any timeZone query parameter, falling back to UTC when neither is set. The window label names the zone, for example last 7 days (America/New_York).

People. Only Clerk user actors, ids starting user_, are counted. Automation actors such as system, mcp-service and temporal-worker are left out. Ordermatic platform support and sys admins can act inside a customer org, so the route looks up every counted id in Clerk (lookupPlatformStaff in apps/webapp/src/lib/inbox/member-names.ts) and drops staff. When Clerk cannot return the org roster or answer the staff lookup, the route answers 503 and there is no table. Every current member gets a row, with zeros when nothing is recorded, and so does anyone outside the roster with rows in the window.

Labels. A row shows the member’s Clerk first and last name, or else their Clerk identifier: the part of a sign-in email before the @, or any other identifier, such as a phone number or username, as Clerk holds it. Without either it reads Team member N. An id Clerk has an account for but the roster does not reads Not a current member N. An id Clerk returns no account for reads Account not found N. Clerk does not say why: the account may have been deleted, or the id may come from another Clerk instance. That row cannot be checked for staff, so it is kept and labelled rather than dropped or guessed at from an email. Members who share a display name get (N) after it. All numbering follows Clerk user id order, so Team member N and (N) name the same person in every range and turn. Not a current member N and Account not found N depend on who has rows in that range. The tool keeps the (N) when it caps a name at 60 characters.

Column What it counts Source and filters Not counted
Orders submitted Orders the ERP accepted from Submit, and quotes the ERP created from Generate Quote, each split quote on its own, each counted once. audit_logs with event_type in order_submitted, quote_submitted and target_type = 'order'; count(distinct target_id) per actor_id. persistOrderResultActivity and persistQuoteResultActivity in apps/ts-temporal-worker/src/activities/erp-writes.ts write them on success only. Automated submissions, which have no user actor. Failed or rejected attempts. Quotes created before quote_submitted rows were written, including quote runs started before that deploy, which carry no submitter.
Inbox actions Setting an item aside, archiving one with a label, and assigning or unassigning an email from the email page. audit_logs with event_type in inbox_rejected, inbox_labelled, inbox_reassigned; count(*) per actor_id, so a bulk action on N items counts N. Rows with the routing-rule reassign description or target_type = 'settings' are left out. Assigning or unassigning from the work item inbox or supervisor views (/api/work-items/bulk-actions), claiming, and archiving without a label, none of which writes an audit row. Routing-rule moves, the supervisor’s escalate-at-risk action, and trash (inbox_deleted).
Manual overrides A unit price typed in the line editor; either choice on the price-mismatch card, keeping the PO price or taking the ERP price; the suggested-price Accept button; the batch Validate at tier price button, one row per line; a template revision picked by hand (counted even if its save then fails); and each ERP item pick that replaced a different item on the line. field_decisions with actor in operator, operator-batch and decision_stage in manual_override, accept_erp_price, accept_gross_price, leaving out reason keep_bound or auto_single_candidate and field = 'price_effectivity'; plus audit_logs with event_type = 'line_item_repicked'. Both are counted per actor and added. Recorded but left out: keeping the bound item, a revision the picker applied on its own, stale-price justifications, the price_decisions accept_erp_price row a priced ERP item pick writes, including on a line that held no item, operator_confirm quantity rows from the short-ship Apply button, and shipping_address_correction_confirmed audit rows. Not counted either: returning a line to the item it held before the operator first changed it, re-picks made before line_item_repicked rows were written, and other quantity, unit of measure, description, customer and ship-to edits.

Where the override rows come from.

  • line_item_repicked is written by the autosave PUT (apps/webapp/src/pages/api/extracted-orders/[id]/index.ts). assignErpMatch marks a line with the session-only pickedViaErpSearch flag for every pick it makes. Its callers are the ERP item search modal, the TemplateRevisionPicker catalog search, the OrderItemCard “Did you mean” suggestions and the revision tab’s catalog suggestions. The PUT writes one row, as the saving user, when a flagged line commits an item different from the one it held, read under a row lock. It writes none when the new item is the line’s original item: the item replaced by the line’s first recorded change, which is the earlier of its first line_item_repicked row and its first revision pick counted as an override. metadata holds fromErpItemId, toErpItemId and extractedOrderId. Revision picks and undo clear the flag; revision picks are field_decisions rows instead.
  • applyTemplatePick posts the revision field_decisions row at once, without waiting for the autosave, because a newer revision row must supersede an earlier keep_bound row even if the save is slow. A pick whose save then fails is still counted, a known limit.
  • The TemplateRevisionPicker applies a stem’s only template on its own, and the field_decisions row it posts carries the viewing user as its actor. That row has reason = 'auto_single_candidate' (AUTO_SINGLE_CANDIDATE_REASON in apps/webapp/src/utils/template-revision.ts), which keeps it out of Manual overrides. Automatic picks made before this reason was written carry none, so they are counted.
  • Both choices on the price-mismatch card count. Taking the ERP price writes an accept_erp_price row. Keeping the PO price leaves the price unchanged but writes a manual_override price row through the line override endpoint, with the reason “Operator kept the PO price over the ERP tier price”.
  • Nothing writes accept_gross_price today; the filter includes the stage anyway.

What the model is told. The tool returns an error instead of a table, and never reads 0, when a count is missing, null, negative or not a whole number, when a name is missing, or when the response lacks creMetrics, a window label containing its time zone, or namesResolved: true. Rule 8 in packages/gopher-py/gopher/agent.py and the tool description forbid calling anyone idle, busy, busiest, most or least active or the hardest worker, adding columns together, and adding or totalling a column across people. A zero means nothing recorded. Rows are sorted by the counts, and the tool says that order is not a ranking.

E. Thread persistence

Section titled “E. Thread persistence”
  • Backed by the PostgreSQL tables gopher_threads and gopher_messages in @repo/db. gopher_threads carries the org, connection, surface (review_companion, inbox_collab, desktop, scheduled_audit, chief_of_staff), title, the document / order / PO / work-item context, and an external_run_id for audit runs. context_order_id references orders, not extracted_orders, so the companion stores the extracted order’s linked orders row, or nothing when there is none. gopher_messages carries role, content, thought, tool calls, artifacts, and mascot state, ordered by a per-thread sequence that clients must render by, never created_at.
  • Threads are read back through GET /api/gopher/threads and GET /api/gopher/threads/{threadId}, which restore conversation history across page navigations and browser refreshes.
  • Threads are org-shared except chief_of_staff, which only its creator can list or open, and only while they still hold analytics.view.org. The internal service token never sees chief_of_staff threads (visibleTo in apps/webapp/src/lib/gopher/threads.ts).

3. Feature flags

Section titled “3. Feature flags”

All five default off. gopher_companion_v1, gopher_inbox_mentions_v1 and gopher_chip_fast_path_v1 are on when Flagsmith has them on for either the organization or the signed-in user (a per-user identity override, or a user-trait segment such as the emailDomain-keyed internal one; no segment carries a Gopher override today, so per-person access is an identity override). That lets Ordermatic staff use Gopher inside a customer org without giving that customer’s users the companion or the @gopher chip, though Gopher’s inbox replies are internal notes every member of the org can read. The user-level answer (isFlagOnForUserOrOrg) only counts for the org the user is signed into, so a staff member viewing another org’s document on the review page does not get the companion there. /api/gopher/* always scopes a signed-in caller to that same org; only the internal service token may name an org_id. gopher_scheduled_audits is organization-only: the dispatcher runs with no user present, so the /gopher/audits page checks both of its flags per organization as well.

Flag Gates
gopher_companion_v1 The companion mount on review.astro, the /gopher/chief-of-staff page, POST /api/gopher/chat, both /api/gopher/threads routes, and the /api/gopher/insights routes. Also required by the other flags’ surfaces.
gopher_inbox_mentions_v1 @gopher handling in POST /api/work-items/{id}/notes and the work-item conversation route. Without it a mention stays plain text.
gopher_scheduled_audits The per-connection fan-out in fetch_gopher_audit_connections, and the /gopher/audits page. A connection whose org is not flagged in is skipped.
gopher_chip_fast_path_v1 The chip fast path in POST /api/gopher/chat. Off, a chip runs the agent loop like a typed question.
gopher_volume_report_v1 The get_volume_report and get_usage_statement tools on the chief_of_staff surface, and GET /api/gopher/usage-statement, which 404s when off.

4. Two-Tier Execution Architecture

Section titled “4. Two-Tier Execution Architecture”
Execution Tier Fast Path (Interactive) Slow Path (Scheduled)
Entry point Astro SSR POST /api/gopher/chat Temporal schedule, daily
Workflow GopherResearchWorkflow ScheduledGopherAuditDispatcher fanning out one child per connection
Task queue gopher-interactive gopher-interactive
Transport Server-Sent Events, or JSON when stream: false Temporal workflow execution
Use cases In-context review questions, line-item price checks Customer dormancy scans, price leakage and margin variance

Both tiers execute inside the Temporal worker. The Astro route authenticates, resolves the tenant connection, persists the turn, then starts the workflow and relays its result; it does not run the agent itself.

Concurrency and queue isolation

Section titled “Concurrency and queue isolation”

Gopher workflows run on their own Temporal task queue, gopher-interactive (override with GOPHER_TASK_QUEUE), isolated from the pdf-extraction queue so user prompts never wait behind a 40-page OCR batch. Worker concurrency is tuned by GOPHER_MAX_CONCURRENT_ACTIVITIES and GOPHER_MAX_CONCURRENT_WORKFLOWS.

Warm sessions

Section titled “Warm sessions”

Building a cloud session costs 5 to 14 seconds (the R2 LIST plus one DESCRIBE per Gold model); a query on a built one costs about 0.3 seconds. So the worker keeps one session per ERP connection for GOPHER_SESSION_TTL_SECONDS (default 300; 0 builds a fresh session for every run) and leases it to each run through get_gopher_warehouse_session. The cache key starts with the connection id and also holds the deployment mode, bucket, local data directory, R2 endpoint and whether R2 keys are set, so two tenants never share a session. Concurrent cold misses for one connection wait on a per-connection lock and reuse a single build.

  • Each run queries through its own con.cursor(), because a DuckDB connection is not safe to share. An expired or replaced session is closed only after the last run holding it releases its lease, since closing the connection kills every cursor made from it.
  • The session’s empty_tables list and table mtimes are as old as the session, so a Gold model that syncs inside the window is not seen until the session expires.
  • A DuckDB FatalException, InternalException or ConnectionException from query_gold_warehouse evicts that connection’s session, and the next run builds a new one.
  • GOPHER_SESSION_THREADS (default 4) and GOPHER_SESSION_MEMORY_LIMIT (default 1GB) set the DuckDB caps in _lock_down. They are a budget per warm session, so every run on that connection shares them.

Planner rounds and budgets

Section titled “Planner rounds and budgets”

Each planner round is a full model round trip, so the agent works to need fewer of them:

  • The system prompt carries a compact list of every bound Gold table and its columns (gold_schema_prompt, from one information_schema.columns query), leaving out empty stand-ins, so the planner rarely needs describe_warehouse_tables.
  • Rule 10 tells the planner to request independent tool calls (one query per line item, or a price and a history lookup) in the same turn. A round’s calls run concurrently with asyncio.gather, and their results go back to the model in the planner’s order. A call that starts after the tool budget is spent is skipped.

GOPHER_MAX_ITERATIONS (default 25) and GOPHER_TOOL_TIME_BUDGET_SECONDS (default 90) are the env limits. apply_surface_cap in config.py (SURFACE_TOOL_CAPS) lowers them for interactive surfaces, and never raises them:

Surface Rounds Tool time
review_companion 6 30s
inbox_collab 10 60s
chief_of_staff 10 60s
Any other surface env env

Every result carries rounds, the planner calls the run made (tool rounds plus any answer-only call), and the worker logs gopher run finished: rounds=N elapsed=Xs setup=Ys for each agent-loop run, where setup is the time before the first planner call (mostly the session lease).

5. LLM Strategy: Standardizing on Mirascope

Section titled “5. LLM Strategy: Standardizing on Mirascope”

Gopher standardizes on Mirascope (>=2.3.0), the monorepo’s library for structured outputs and tool calling. packages/gopher-py/gopher/llm.py translates one provider-neutral planner contract to and from Mirascope messages, so every backend runs the same tool code.

Five providers are supported via GOPHER_LLM_PROVIDER:

  • gemini (cloud default): native Google provider. Model comes from GOPHER_CLOUD_MODEL, default gemini-3-flash-preview. Key: GEMINI_API_KEY or GOOGLE_API_KEY, because docker-compose blanks the former to keep google-genai from turning on AFC.
  • openai: OpenAI completions provider. Model comes from GOPHER_CLOUD_MODEL, default gpt-4o. Key: OPENAI_API_KEY.
  • anthropic: Anthropic provider. Model comes from GOPHER_CLOUD_MODEL, default claude-sonnet-4-6; Claude 4.7 and newer reject the temperature the planner always sends, so 4.6 is the newest Claude that runs unchanged on this path. Key: ANTHROPIC_API_KEY.
  • vllm: OpenAI-compatible provider for the Ordermatic on-premises appliance, owning the vllm/ model namespace so slash-bearing model ids survive. Model comes from GOPHER_LOCAL_MODEL, default google/gemma-3-27b-it; base URL from GOPHER_VLLM_BASE_URL, default http://vllm:8000/v1. No API key required.
  • mock: the offline heuristic planner used by tests and demo mode. Never touches the network.

Each cloud provider owns its default model in CLOUD_DEFAULT_MODELS (packages/gopher-py/gopher/config.py). There is no shared cloud fallback: a model id belongs to exactly one endpoint, and one shared default meant selecting openai sent Gemini’s model name to api.openai.com. test_from_env_cloud_default_model_is_provider_specific in packages/gopher-py/tests/test_llm.py pins one default per key-requiring provider.

GOPHER_DEPLOYMENT_MODE picks the defaults: cloud selects gemini, local selects vllm, and demo selects mock. Outside demo mode, a key-requiring provider with no key raises a structured error rather than silently falling back to mock and answering real questions with canned text. An unrecognized provider raises as well.

Defense and compliance alignment: the local appliance path routes to approved US or allied open weights, excluding PRC-origin models, per FY2026 NDAA §1532.

6. Security and Multi-Tenant Guarantees

Section titled “6. Security and Multi-Tenant Guarantees”

SQL AST validator (sqlglot)

Section titled “SQL AST validator (sqlglot)”

Model-generated SQL is validated in packages/gopher-py/gopher/parser.py before it reaches DuckDB:

  • Comments stripped first, preserving string literals, so a comment cannot hide an injection.
  • Single statement only. More than one parsed statement is rejected.
  • SELECT or UNION only. Anything else, including INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, ATTACH, COPY, and PRAGMA, is rejected by statement type.
  • Function blocking. PROHIBITED_FUNCTIONS covers the file readers and writers (READ_PARQUET, ICEBERG_SCAN, PARQUET_SCAN, READ_CSV[_AUTO], READ_JSON[_AUTO], READ_JSON_OBJECTS[_AUTO], READ_NDJSON[_AUTO], READ_NDJSON_OBJECTS, READ_TEXT, READ_BLOB, WRITE_CSV, WRITE_PARQUET), file listing and metadata (GLOB, PARQUET_METADATA, PARQUET_SCHEMA, PARQUET_FILE_METADATA, PARQUET_KV_METADATA), anything that runs a SQL string the validator never sees (QUERY, QUERY_TABLE, JSON_EXECUTE_SERIALIZED_SQL, SQL_AUTO_COMPLETE), and settings, secrets and host access (SYSTEM, CURRENT_SETTING, GETENV, GETVARIABLE, WHICH_SECRET). Any PRAGMA_* or DUCKDB_* function is blocked by prefix. Matching ignores underscores, and a raw-text check for read_parquet(, iceberg_scan(, and parquet_scan( backs the AST pass.
  • Row-source allowlist. Every table reference must be a Gold view name from GOLD_TABLE_METADATA or a CTE visible in that scope. CTE names are resolved per scope with sqlglot’s traverse_scope, so a CTE named pg_settings in one UNION branch cannot whitelist the real pg_settings in another. Table functions are rejected except range() / generate_series(), schema-qualified names are rejected, and LATERAL is allowed only over a subquery.
  • Table references. Names resolving to s3:// prefixes, .parquet, or .csv are rejected.
  • Deterministic row limits. A missing LIMIT is injected and a larger one is clamped to max_rows, default 500, configurable with GOPHER_MAX_ROWS.

The validator is a parser, so the engine is locked down behind it (_lock_down in session.py). Once the views exist, each session sets allowed_directories to the tenant’s own Gold partition directories (the Gold files themselves in local mode), turns enable_external_access off, caps itself at GOPHER_SESSION_MEMORY_LIMIT (default 1GB) and GOPHER_SESSION_THREADS (default 4), and sets lock_configuration = true, so no query can turn access back on. A query that slipped past the validator still cannot read another tenant’s partition, glob the bucket, or reach an https:// URL.

Tenant-scoped views

Section titled “Tenant-scoped views”

The view set is generated from GOLD_TABLE_METADATA in packages/gopher-py/gopher/tools/catalog.py, and the names are not gold_-prefixed:

dim_customers, dim_products, dim_orders, dim_shipping_addresses, fact_order_lines, fact_ar_invoices, fact_ar_invoice_lines, fact_ar_aging, fact_customer_item_history, xref_products.

That dict is the planner’s only description of the warehouse, so its columns are transcribed from DESCRIBE SELECT * FROM read_parquet(...) against the real catalog buckets and cross-checked against apps/dagster/dbt/models/gold/*.sql. It also carries the traps a correct column list cannot: dim_orders has no status, PO number, or currency column and a total_amount that is NULL for whole connections, so order value comes from SUM(fact_order_lines.extended_price); fact_ar_invoices holds open invoices only, while fact_ar_invoice_lines holds every billed line on Prophet21 connections, is empty on other ERPs, and must be joined to it for open A/R; fact_customer_item_history is derived from WhereFour order lines and is empty for every other ERP. connection_id is on every view but deliberately absent from the column lists, because the session has already applied it. The curated few-shot templates in golden_sql.py are re-bound against this dict by packages/gopher-py/tests/test_warehouse.py on every run, so a template that drifts from the catalogue fails the suite rather than the LLM.

Each cloud view is SELECT * FROM read_parquet('s3://{bucket}/gold/{table}/connection_id=<tenant>/**/*.parquet', hive_partitioning=false, union_by_name=true) WHERE connection_id = '<tenant>', with the connection id SQL-escaped. The glob covers only the tenant’s own partition directory and the view still filters on the connection_id column, so a row is visible only when both its path and its data name the tenant; a row written under the wrong prefix is visible to nobody. hive_partitioning is off on purpose: with it on, the path supplies a non-NULL connection_id for the one-row all-NULL Parquet that dbt writes for an empty partition, and that row would be reported as a record. union_by_name keeps a partition readable if it ever holds files written before and after a column was added.

Session setup first runs one glob() over s3://{bucket}/gold/**/*.parquet to learn which models have a partition for this connection (_materialized_gold_tables). A model with no partition for the tenant gets _create_empty_schema_table instead of a view: DuckDB binds a glob eagerly, so a view over an unmaterialized model raises IOException: No files found that match the pattern and took the whole session down, for every tenant. The stand-in is an empty, correctly typed table, so it answers “no data” with the right column names and has no rows from any source to leak. The session also records the stand-in names (GopherWarehouseSession.empty_tables), and the agent appends them to the system prompt so the planner says the data has not synced instead of querying each empty table in turn. A model that has a partition but no rows for the tenant is not on that list; the planner still learns it from the 0-row query hint. If no model has any partition at all, or the LIST fails, setup raises, because treating an unreadable warehouse as an empty one would have Gopher tell a tenant with plenty of data that it has none.

In local mode the same views are built over a local Parquet directory, and a model whose file is missing gets the same empty stand-in. In demo mode a subset is seeded as unfiltered in-memory fixture tables. In cloud mode, missing R2 credentials raise rather than silently serving fixtures.

DuckDB session bootstrap

Section titled “DuckDB session bootstrap”

session.py opens every cloud session with SET GLOBAL extension_directory = '/tmp/duckdb-extensions', then for each of httpfs and iceberg reads duckdb_extensions() and runs INSTALL only when that extension reports itself absent, followed by LOAD. The INSTALL and the LOAD are each wrapped in contextlib.suppress(Exception); the SET is not, so a bad extension directory raises at session start instead of degrading into a confusing failure further down. The worker image bakes both extensions into that directory (apps/temporal-worker/Dockerfile), so a container with no egress to extensions.duckdb.org does not fail silently at session start and then surface as an unintelligible error on the first s3:// view. The bake runs LOAD as well as INSTALL, because INSTALL iceberg alone does not fetch its transitive avro extension, and a following --network=none layer replays the bootstrap with the suppression removed so a bake under the wrong DuckDB version or platform directory fails the build.

The R2 credentials (R2_ACCESS_KEY_ID, R2_SECRET_ACCESS_KEY, and R2_ENDPOINT_URL or R2_ENDPOINT on the worker) become a DuckDB secret, CREATE SECRET gopher_r2 (TYPE s3, ...), not SET GLOBAL s3_* settings. Settings are readable by any query (duckdb_settings(), pg_settings, current_setting()), and these keys read the whole multi-tenant bucket. A secret is redacted in duckdb_secrets(), and showing it unredacted needs allow_unredacted_secrets, which the configuration lock freezes off. Secrets live on the database instance, so the con.cursor() connections QueryGoldWarehouseTool queries on use it too. Only unsafe_enable_version_guessing is still set GLOBAL, for the same reason the extension directory is: a cursor inherits no connection-local settings. The scope of GLOBAL here is this session’s own anonymous :memory: database, so nothing is shared between tenants; test_global_settings_do_not_leak_between_sessions in tests/test_isolation.py holds that line.

Multi-tenant isolation test suite

Section titled “Multi-tenant isolation test suite”

packages/gopher-py/tests/test_isolation.py covers eight categories:

  1. Direct SELECT across tenants, both through DuckDB and through the warehouse tool.
  2. Adversarial CTEs, including nested CTEs targeting another tenant’s rows.
  3. Adversarial joins and subqueries: self joins, cross joins, IN and correlated EXISTS subqueries, multi-table joins, UNION, and GROUP BY / aggregation leaks.
  4. Concurrent and overlapping cross-tenant sessions, including a negative control that fails if views are ever created unscoped.
  5. AST defense-in-depth against view bypass via read_parquet and system introspection.
  6. connection_id injection resistance at session creation.
  7. Fuzzy catalog search isolation.
  8. Partitioned R2-shaped views: that a row is visible only when its partition path and its connection_id column both name the tenant, that the locked session refuses raw reads outside the tenant’s partition (bypassing the validator on purpose) and enforces its memory and thread caps, and that global DuckDB settings do not leak between sessions.

7. Developer Experience

Section titled “7. Developer Experience”

Offline demo runner

Section titled “Offline demo runner”

The demo runs without credentials or external databases, against the in-memory fixtures in session.py and the mock planner. GOPHER_DEPLOYMENT_MODE=demo is required: the CLI reads GopherConfig.from_env(), which defaults to cloud, and a cloud session with no R2 credentials raises before the agent ever plans a step.

Terminal window
cd packages/gopher-py
GOPHER_DEPLOYMENT_MODE=demo uv run python -m gopher.cli --demo

It walks three prompts covering customers, orders, and a live-ERP stock lookup, and prints thoughts, tool invocations, artifacts, and timing. A single ad-hoc prompt works too:

Terminal window
GOPHER_DEPLOYMENT_MODE=demo uv run python -m gopher.cli --query "Who are our top customers by credit limit?"

The fixtures cover five of the ten Gold models: dim_customers, dim_products, dim_orders, fact_order_lines, and fact_ar_aging. A demo prompt that reaches any other model gets an unresolved name, not fixture data. The fixtures and the canned SQL the mock planner emits both still carry the pre-rewrite column names, customer_name, order_number, po_number. They agree with each other, so the demo runs, but they no longer agree with GOLD_TABLE_METADATA, so demo mode does not exercise the column contract a real provider plans against.

Error contract

Section titled “Error contract”

Errors emitted over SSE or JSON follow the GopherError contract, defined in packages/contracts/src/gopher.ts and mirrored in packages/gopher-py/gopher/models.py:

interface GopherError {
  problem: string; // What went wrong in plain English
  cause: string; // Technical root cause: syntax, timeout, permission
  remediation: string; // Actionable developer or user fix
  docs_url?: string; // Link to relevant documentation
  trace_id: string; // UUID correlation identifier for log tracing
}

Failure propagation

Section titled “Failure propagation”

A failed excavation is reported as a failure at every hop. GopherQueryResult carries an error field, and GopherAgent.run_query() fills it from the terminal error event, along with the alert mascot state that event carries. A run that ends with no error event but also no answer and no artifacts is the same silent failure in different clothes, so the agent synthesizes the error itself.

  • execute_gopher_research returns status: "failed" with the serialized error and logs at error level (apps/temporal-worker/activities/gopher_research.py).
  • The scheduled audits call _guard_audit_result, which raises GopherExcavationError. Nothing is persisted and no notification is sent, because an empty finding set written as a completed audit is indistinguishable from a genuinely clean scan.
  • POST /api/gopher/chat checks result.error before persisting. The JSON path returns a 500 carrying the GopherError; the SSE path emits an error event followed by done and closes. Either way no assistant turn is written, so a reload does not replay a failure as an answer. The user’s own turn was persisted before the workflow started and stays.

Persistence failures are fail-loud on the same principle: if a turn cannot be written to gopher_messages, the route returns or streams a GopherError rather than running the chat unpersisted.

7. System 1 Decision & Verification Layer (Jev & Gemini Flash)

Section titled “7. System 1 Decision & Verification Layer (Jev & Gemini Flash)”

For canonical ERP questions (top customers, AR aging, dormancy, open orders), running a full multi-turn ReAct loop through a frontier LLM burns 4–12s and risks text-to-SQL errors.

The System 1 Acceleration Layer (packages/gopher-py/gopher/decision/, gated by Flagsmith’s gopher_system_one_acceleration) adds two deterministic, non-autoregressive boundaries:

  1. Pre-Execution Routing (GopherDecisionRouter):

    • Classifies user queries against pre-compiled Golden SQL templates or ERP tools in a single turn.
    • Evaluates a deterministic policy: confidence >= 0.85, safe_read_only >= 0.70, and ambiguity < 2.
    • On match, queries bypass the LLM ReAct loop and execute in pure DuckDB in <40ms.
    • On low confidence, ambiguity, or custom requirements, falls back transparently to the multi-turn ReAct loop.

  2. Post-Generation Output Verification (GopherOutputVerifier):

    • Adopts the critical/ai-output-verification pattern from Jevs-Garage.
    • Validates drafted LLM narratives against executed tabular records.
    • If numeric figures conflict (hallucination detected), prose is suppressed and only the verified tabular artifact is streamed to the user.

Dual-Backend & Transition Strategy

Section titled “Dual-Backend & Transition Strategy”

To support immediate deployment while TypeSafe AI Jev API keys are in preview, the architecture provides dual backends via GOPHER_SYSTEM_ONE_PROVIDER:

  • Gemini Flash Bridge (Current Stopgap): Uses gemini-2.5-flash-lite via google.genai with strict Pydantic JSON schemas (GeminiRouteClassification, GeminiVerificationClassification). Achieves ~680ms routing and 2.28s total excavation time.
  • TypeSafe AI Jev (Production Target): Uses typesafe-sdk with Choice, Noul, and Score questions. Achieves sub-150ms routing and ~1.0s total excavation time once TYPESAFE_API_KEY is provisioned.
  • Detailed roadmap and transition plan: docs/plan/gopher-system-one-jev-transition-plan.md.