tilley-mcp-web - Tool Documentation

ADMIN ONLY. Step 1 of 3. Returns a guidance prompt that maps question domains to catalogs. Currently supports DATANAC-Users (members, agencies, agents, producer farms, insurance scenarios/plans, budgets) and DATANAC-Markets (Futures, Options, SpecialOptions, uvw_Quotes view, FuturesSymbols). Call this before AdHocQuery_ExploreSchema or AdHocQuery_ExecuteQuery.

ADMIN ONLY. Step 3 of 3 (small result sets). Executes T-SQL as the DATANAC_READONLY user. Writes are impossible — the SQL login only has db_datareader. Prefer explicit column lists over SELECT *. Joins and aggregates are fine. 30-second command timeout. For long-running queries or large exports use AdHocQuery_ExecuteQueryAsync.

ADMIN ONLY. Queues a long-running T-SQL query (up to 5 minutes). Returns a task_id immediately. Poll AdHocQuery_QueryAsyncStatus every 5–10 s until done. On completion, result is a CSV with header row uploaded to S3; status returns a 1-hour presigned URL.

ADMIN ONLY. Step 2 of 3. Regex search over DATANAC schema — results are GROUPED BY TABLE so you always see which table each column belongs to. Boilerplate (indexes, defaults) is stripped; column definitions ([name] [type] nullable) and FOREIGN KEY references are kept. WORKFLOW — run these searches before writing T-SQL: (a) Search each table by exact name to see its columns: 'InsurancePlan', 'Agency', 'Member', 'Agent'. (b) Search 'FOREIGN KEY' to see all join columns with their REFERENCES targets. (c) Search a shared key name like 'producer_token' to confirm which tables share it — results are grouped so you see '[Member] agent_token', '[ProducerFarm] agent_token', etc. Use ONLY the exact column names returned by this tool in your T-SQL — never guess. Max 200 output lines total; increase max_lines or narrow the regex if needed.

ADMIN ONLY. Returns the status of an async query started with AdHocQuery_ExecuteQueryAsync. Possible responses: 'running (elapsed Ns)', 'failed: <message>', or 'done: <presigned S3 URL>'. Presigned URL is valid for 1 hour. Status TTL is also 1 hour.

Get a presigned S3 GET URL (1-day expiry, attachment filename) for a raw PASS file. IMPORTANT: These files are large pipe-delimited text. Do NOT load the full content. Instead, use the URL with grep/awk/head to sample specific record types or lines. Example shell: curl -s '<url>' | grep '^[^|]*|[^|]*|P55A' | head -20

List raw PASS files under a vendor/server S3 prefix. Returns key, size, and lastModified for each object. Use AipPass_Files_FileGetDocumentLink to get a presigned download URL.

List AIP PASS vendor/server folders in S3 (one entry per AIP vendor). Returns the top-level S3 prefix names. Use these as the `vendor` parameter in other AipPass tools.

Fetch the README.json metadata index for a given AIP PASS vendor/server folder. Returns structured JSON with aip.agencies[] (unique P55A agency records), aip.producers[] (unique P10 producers), aip.files[] (files with record-type counts), and run metadata (server, generatedAt). Returns null/empty when no README.json exists yet.

Search county-level ARC-CO yield overrides (modeling / stress test inputs).

Search ARC-CO vs. PLC program elections by farm, commodity, crop year, and location.

[User Data] Select ProgramElection rows (full table projection) for advanced use.

[Calculation] Generic ARC-County (ARC-CO) and PLC payment model: pass crop year, state, county, commodity, program (arc-co or plc), base acres, share, and PLC yield. For ARC-CO when FSA publishes split Irrigated/Non-Irrigated yields, choose one of two approaches: (1) arc_co_practice = "irrigated" or "non-irrigated" to model a single practice, OR (2) historical_irrigation_percentage (HIP, 0–1) to blend both practices by irrigated weight. Counties with a single unsplit practice need neither parameter. Loads ARCCOYields, ARCCOPrices, EffectiveReferencePrices, MarketYearAveragePrices, and PLCPaymentRates from DATANAC/FSA.ARCPLC via the same SMOCI SelectObject path as aip-pass /data (cached in-process by crop year + location + commodity). Does not use ProgramElection, producer_token, or DATANAC-Users. Outputs totalArcCoPayment, totalPlcPayment, totalArcPlcPayment, paymentsByCommodity; guidanceHints note missing FSA rows. Not an FSA official determination.

Returns **Cost of Production** rows (one per practice in a given state/county for that year — yield, projected price, reported acreage, projected revenue, production cost). Each row rolls up under an **Enterprise Budget**. Provide `budget_id` (preferred — drills under a known Enterprise Budget) **or** `year` + `state_name` + `county_name` + `commodity_name` (+ optional `type_name` / `practice_name`) for a dimensional query. Use the returned `id` as `budget_county_practice_id` for `Budget_LineItem_Search` or `Budget_LineItemDetail_Search`.

Create or update one **Cost of Production** row (yield, projected price, reported acreage, geography) under a given Enterprise Budget. Requires `budget_id` (the parent Enterprise Budget id). Insert: omit `id` — call `Util_NewGuid` first to mint a fresh value. Update: pass the existing `id` returned from `Budget_CostOfProduction_Search`.

Returns **Enterprise Budget** rows (the year-level header containing all counties and crops). Typical filters: `year`, `status`, `id`. Only `status="active"` Enterprise Budgets feed into other modules. Use the returned `id` as `budget_id` for `Budget_CostOfProduction_Search` to drill into per-practice rows.

Create or update an **Enterprise Budget** header (name, year, status). Insert: omit `id` — call `Util_NewGuid` first to mint a fresh value, then pass it as `id`. Update: pass the existing `id` returned from `Budget_EnterpriseBudget_Search`. Does not touch Cost of Production rows or line items.

Returns **Budget Line Item Details** under a **Cost of Production** row — the inputs/components inside each line item (e.g. "Ammonia", "Nitrogen" under the "Chemicals" line item). Requires `budget_county_practice_id`. Optional `line_item_name` filter narrows to details under one parent line item.

Batch upsert **Budget Line Item Details** under one **Cost of Production** row. Requires `budget_county_practice_id` and a JSON array of detail objects (use `Util_NewGuid` for the `id` of each new row). Each detail's `line_item_name` must match an existing parent Budget Line Item under the same Cost of Production row. Pre-existing items not present in the array are unaffected.

Returns **Budget Line Items** (e.g. "Seed", "Chemicals", "Rent") under one **Cost of Production** row. Requires `budget_county_practice_id` (the Cost of Production id, returned as `id` from `Budget_CostOfProduction_Search`).

Batch upsert **Budget Line Items** under one **Cost of Production** row. Requires `budget_county_practice_id` and a JSON array of line item objects (use `Util_NewGuid` for the `id` of each new row). Pre-existing items not present in the array are unaffected.

Runs the hosted code interpreter (Responses API with built-in code_interpreter; Python in the provider sandbox). Put the task or instructions in input. Optional: instructions, model, tool_choice, memory_limit (1g|4g|16g|64g), file_ids (comma-separated), container_id (reuse an existing container).

Do not call this tool until every required parameter has a concrete value—never invoke with placeholders, omissions, or guesses; collect missing values from the user or from prerequisite tool calls first. Calculates Soybean Cyst Nematode (SCN) yield loss percentage, yield loss in bu/acre, and profit loss per acre. Conversation flow (do not stall on sand/pH—the user picks a formation; you fetch sand/pH from SSURGO): 1) First user message: establish state and county and begin collecting yield_potential, soybean_price, and scn_egg_count as needed. Do NOT call soil_composition_lookup on that opening message only—wait until county is confirmed and you are on a follow-up turn. 2) Early—on your next reply once state and county are known (second interaction onward)—you MUST call soil_composition_lookup WITHOUT map_unit_name, SHOW the returned formation (map unit) names to the user in full, and PROMPT them to choose one. Do not treat formations as optional fine print, do not wait until yield/price/egg count are all collected before this lookup, and do not repeatedly offer to list formations without actually calling the tool and displaying results. 3) Continue collecting any still-missing yield_potential, soybean_price, or scn_egg_count across turns as needed. 4) After the user picks a formation, call soil_composition_lookup WITH that map_unit_name to obtain Avg_sand and Avg_soil_ph. 5) Call this tool only when every required argument is ready. Do NOT prompt the user for female_index — the state-average default is applied automatically.

Do not call this tool until every required parameter has a concrete value—never invoke with placeholders, omissions, or guesses; collect missing values from the user or from prerequisite tool calls first. GSSURGO soil composition lookup — filter by state_name, county_name, and optional map_unit_name. Required inputs for every call: state_name and county_name. After county is known, call WITHOUT map_unit_name early in the thread (not on the user's very first message—see SCN tool flow): list formations, display them prominently, and ask the user to select one for sand/pH. Then call again WITH that exact map_unit_name to get Avg_sand and Avg_soil_ph for SCN_Soybean_Risk_Calculator.

Search indexed document content via RG Memory/Item and return raw payload.

Search DocumentDictionary catalog rows linked to `document_id`.

[User Data] Return the raw text payload resolved from RG contentUrl for a financial document.

[User Data] Return a presigned document URL for viewing/downloading the original uploaded file.

List financial document file rows (file name, type, year, ids) for producer/agent scope.

Get a presigned download URL for a ToTilley **email attachment** file. Pass `id` from `Emails_Search` (attachment row where `document_url` is set). Files are stored at S3 key `producer_token/file_document_id` by totilley-daemon — not the Document vault / RG chunk pipeline. Returns `download_url` (expires ~1 day) and `document_name` for Content-Disposition.

Search **Emails** (alias: **Email Attachments**) — ToTilley inbox rows for a producer. Each row is either the email body (content-only; empty document_url) or an attachment (document_name = file name). The `content` field holds the email body text (stored as notes). When `document_url` is present, download the raw attachment with `Emails_AttachmentFileGetLink` (ToTilley S3 path). Do not use `Document_FileGetLink` for these rows — that targets the RG FinancialDocument pipeline.

Search ProducerFarmBaseAcres (Farm Bill base acres / PLC yields).

Search ProducerFarmFieldCropHistory (APH yields).

Returns planted crop rows by field and crop year (commodity, type, practice, acreage/yields as exposed by search). For drill-down, pass `producer_farm_field_id` from farm field search. For exploratory search, filter with `location_state_name`, `location_county_name`, `commodity_name`, and optional `type_name` / `practice_name`. Prefer a specific `crop_year` before loosening other filters. Rows respect producer vs agent visibility (`GetProducersByRole` in SQL).

Returns tract/field rows (identifiers, acreage, location context as exposed by search) under a farm. Call after you have a farm: pass `producer_farm_id` from a farm search row. Use `crop_year` when acreage rollups should reflect one season; omitting year can widen or aggregate acreage across seasons depending on stored data.

Returns farm-level rows (location, identifiers, rollups as exposed by search) for the scoped producer or agent. Use as the primary **farm discovery** query before tract/field or planted-crop detail. Typical filters: `crop_year`; optional `location_state_name` and `location_county_name` (always pair county with state).

Select ProducerFarmBaseAcres rows as objects (SMOCI SelectObject).

Select ProducerFarmFieldCropHistory rows via SMOCI SelectObject.

Select ProducerFarmFieldCrop rows as objects (SMOCI SelectObject).

Select ProducerFarmField rows as objects (SMOCI SelectObject).

Select ProducerFarm rows as objects (SMOCI SelectObject).

Start a financial interview wizard session; returns session metadata and an empty bundle template.

Finish the interview: returns the final JSON bundle for the chat and removes server-side session state.

Update or inspect the interview: pass session_id and optionally replace accounts for one step via accounts_json + step_category_key; returns the merged JSON bundle so far.

SMOCI Search via aip-pass DataController.

SMOCI SelectObject via aip-pass DataController.

SMOCI SelectFile (tab-separated export) via aip-pass DataController.

Search joined producer farm crop + history (DATANAC / AIP.PASS).

[System Data — RMA actuarial] Select available insurance plans/offers for a county and commodity year.

Returns claim / indemnity rows that relate to insurance plans via `insurance_plan_id`. Prefer passing that key from **InsurancePlan_Search** when you already have a plan. Use `reinsurance_year` to pin the reinsurance window for indemnities. Optionally narrow with `commodity_name`, `location_state_name`, and `location_county_name` to trim large sets. The underlying search allows many null filters and favors active rows—avoid stacking filters unless the user wants a tight slice.

Returns insurance plan coverage rows (plan name, coverage level, `state_name`, `county_name`, `commodity_name`, `type_name`, `practice_name`, etc.). If you already have an insurance scenario, filter with `insurance_scenario_id` before narrowing by `state_name`, `county_name`, `commodity_name`, `type_name`, and `practice_name`. Otherwise combine `crop_year` with those same fields when refining.

[User Data] Select insurance scenario headers (name, crop year, status) via SMOCI SelectObject.

Returns pre-computed stress-test grid cells (yield/price scenarios) for an insurance scenario. Always pass `insurance_scenario_id` first to scope the grid. Optionally add `commodity_name`, `state_name`, and `county_name` only if the result volume is noisy.

Returns the exact allowed string values for DATANAC filters that used to be large JSON-schema enums. Call this BEFORE any tool that takes commodity_name, type_name, practice_name, state_name, or county_name. Those tools WILL NOT work with informal/guessed labels — pass a value from this list verbatim. lookup: commodity_name | type_name | practice_name | state_name | county_name. county_name requires state_name (full state name from lookup=state_name).

Search the producer's grain buyers (elevators, merchants, processors).

Search CropProfile (marketing crop profiles).

[User Data] Search broker-posted market transaction history (OTC, futures/options from brokerage).

[User Data] Search sales breakdown by contract category for a year and commodity.

Search top-level marketing position rollup (sold vs produced, % marketed, average price, etc.).

[User Data] Select grain sales / contracts (cash, HTA, basis, futures, OTC) via SMOCI SelectObject.

Search producer brokerage account / futures-options transaction history.

Select CropProfile rows via DATANAC/Users SMOCI SelectObject.

Futures / options reference data via DATANAC Markets stored proc Futures_Search (symbol_year maps to reinsurance_year).

Admin-only: search member / billing profile rows via Users API MemberController. Requires a real filter: `last_name`, `business_name`, `id`, `producer_token`, or `agent_token`. `is_active` alone is rejected (no wildcard dump). Phonetic/soundex on name fields. No `first_name` filter — for two-part names try both orderings as `last_name`, or use AdHoc.

**Find clients** / farmers / producers linked to an agent via UserGrant (`POST /UserGrant/search`). Required: `agent_token` (GUID). Broad/unscoped searches are rejected. SQL command timeout: 30s (admin) / 10s (otherwise).

Stripe checkout URL for one or more unpaid invoices. Wraps POST /Member/invoices/payment/getSessionUrl. Requires producer_token or agent_token, invoice_numbers JSON array, and pay_total. Does not write DATANAC rows.

Reusable Stripe Payment Link (does not expire in 24h like Checkout Sessions). Wraps StripeHelper.GetPaymentLinkUrl — pass customer_code (Member.id), subtotal, tax, optional invoice_number. Amounts usually come from CustomerTransactions Sales via AdHocQuery.

Stripe hosted receipt URL for a paid charge. Looks up Charge.receipt_url by Payments.ApprovalCode (Stripe charge id). Query CustomerTransactions.Payments first (AdHocQuery), then pass approval_code here.

Stripe checkout URL for a member's subscription (and optional add-ons). Wraps POST /Member/payment/getSessionUrl — creates a Stripe Checkout Session URL (does not write DATANAC rows). Prefer this for Tilley member pay-now links.

Select a Member row via SelectObject (DATANAC/Users/Member). Required: `id` (member GUID). Optional: `producer_token` / `agent_token` / `is_active`. Broad searches rejected. SQL command timeout: 30s (admin) / 10s (otherwise).

[User Data] Look up subscription / customer status in Central Payments for a member id.

Date_ExportObjectGraph on DATANAC-RMA — sales closing / planting dates JSON for county and crop. The stored procedure uses reinsurance year, commodity, state, and county only. Optional type/practice/plan arguments are forwarded for vault authorization metadata only—they do not change the proc inputs.

Area coverage levels by insurance offer (uvw_ix_AreaCoverageLevelByInsuranceOffer). Filters by year, commodity, location, and optional type/practice/plan match LINQ predicates on the view.

InsuranceOffer_Search — commodity / type / practice / plan rows for county and year.

SummaryOfBusinessByInsurancePlan_Search — SOB across years (commodity_year null) by county / crop / practice.

SummaryOfBusiness_Search — SOB for one commodity year (crop / county / practice filters).

SummaryOfBusinessCauseOfLoss_Search with commodity_year null (all years).

SummaryOfBusinessCauseOfLoss_Search with commodity_year set.

Return **your** Member.email from the signed-in JWT identity (producer / agent / admin). This is the only address SendEmail_Send will deliver to. No producer_token or To address arguments — always resolves from your Member row.

Send an email via **AWS SES** to **yourself only**. Recipient is looked up from Member.email for the signed-in user — never from tool arguments. Even admins can only send to their own Member.email. There is no To/CC/BCC parameter. Reports/CSV/Excel → attachments (plain text or base64 content). Stripe receipt URL → put the URL in `body` (do not call code_interpreter).

ADMIN ONLY. Fetch a Stripe Charge by id (ch_…). Same id as CustomerTransactions.Payments.ApprovalCode. Returns status, amounts, receipt_url, card last4, CustomerCode metadata, refunded flag.

ADMIN ONLY. Search charges for CS. Prefer customer_code (Member.id / metadata CustomerCode) or email (receipt_email). Omit both to list the most recent charges. Optional raw Stripe Search query overrides filters.

ADMIN ONLY. Fetch a Checkout Session by id (cs_…). Returns status, amount, payment_intent id, url, and Tilley metadata.

ADMIN ONLY. List the most recent Checkout Sessions (cs_…). Use when you need a session id before Stripe_CheckoutSession_Get.

ADMIN ONLY. Step 1. Returns a customer-service guide mapping common Stripe support tasks to Stripe_* MCP tools (charges, payment intents, receipts, customers, refunds) and when to use AdHocQuery CustomerTransactions vs Member_Payment_* pay links.

ADMIN ONLY. Search Stripe Customer objects by email (or raw Stripe Search query). Note: Tilley often keys payments by metadata CustomerCode without a durable Stripe Customer.

ADMIN ONLY. Fetch a PaymentIntent by id (pi_…). Useful for checkout status and Tilley metadata (CustomerCode, InvoiceNumber / InvoiceNumbers).

ADMIN ONLY. Search PaymentIntents by Tilley metadata CustomerCode and/or InvoiceNumber, or a raw Stripe Search query. Use when Charge search misses a pending/failed checkout.

ADMIN ONLY. Hosted Stripe receipt URL for a Charge id (ch_… / Payments.ApprovalCode). Same as Member_Payment_GetReceiptUrl but admin-scoped for CS.

ADMIN ONLY. List refunds attached to a Charge (ch_…). Read-only — does not create refunds.

Generate one fresh RFC 4122 v4 UUID and return it as a lowercased dashed string (e.g. "a4a37083-d069-408e-87ec-822832d78fbe"). Call this whenever you need a new `id` value for an Update tool's insert path (e.g. inserting a new Enterprise Budget, Cost of Production, Budget Line Item, or Budget Line Item Detail row). Each call returns a different value — do not cache the result across rows; for batch inserts, call once per new row.

Download xlsx and extract text (rg toolkit extract_web_excel). Optional regex returns named capture groups.

Download PDF and extract text (rg toolkit extract_web_pdf). Do not guess or invent a regex — omit `regex` unless a skill, system instructions, or the user explicitly supplies or clearly describes a pattern. When regex is provided, use named capture groups (?<name>...).

Download URL as text and apply named-group regex (rg toolkit extract_web_text).

HTTP GET a URL and return body text with metadata (rg toolkit get_web_text).

Search the web via Perplexity Search API (ranked URLs with snippets). Same intent as RG toolkit locate_web. Domain scope is fixed in server configuration (Perplexity:DomainFilter), not a tool parameter.