API2 agent SQL timeout analysis (June 2026)

Summary

API2 managed-agent query_data timeouts are overwhelmingly coming from agent-written SQL against conversation and transcript views. Braintrust showed 1,125 query_data statement-timeout spans in the 30-day logs view, compared with 1,127 total statement-timeout spans in the 30-day project monitor. In practice, almost every statement timeout in that Braintrust monitor was an agent SQL timeout.

The main cause is not one bad query. It is a set of repeatable query shapes:

The most impactful fix is to keep both conversation_parts and conversation_parts_full, but make their split explicit and enforce it:

1. Use conversation_parts for metadata-only scans.
2. Use conversation_parts_full only when message text is required.
3. Never join conversation_parts to conversation_parts_full.

Sources

Frequency

Daily distribution from the 1,000 exported rows, grouped by America/New_York date:

Representative bad query shape

This production-failing shape looks narrow, but it expands the same underlying Rails table twice:

SELECT
  cp.id AS part_id,
  cp.external_created_at,
  cp.author_type,
  cp.author_actor_type,
  cp.author_name,
  cp.channel_type,
  left(cpf.content, 1200) AS snippet
FROM conversation_parts cp
JOIN conversation_parts_full cpf ON cpf.id = cp.id
WHERE cp.conversation_id = 3376984
ORDER BY cp.external_created_at ASC
LIMIT 40;

Local EXPLAIN showed PostgreSQL expanding conversation_parts_full into its own public.conversation_parts q_cp_1 branch. In the bad self-join shape, the full-view branch applied the organization filter and then joined back by q_cp.id = q_cp_1.id, instead of cleanly starting from the single conversation. On production-sized orgs, that can turn an exact conversation lookup into broad work across all org parts.

The safer version uses conversation_parts_full directly:

SELECT
  id AS part_id,
  external_created_at,
  author_type,
  author_actor_type,
  author_name,
  channel_type,
  left(content, 1200) AS snippet
FROM conversation_parts_full
WHERE conversation_id = 3376984
ORDER BY external_created_at ASC
LIMIT 40;

In the rewritten plan, the conversation_id predicate is applied inside the full-view branch.

Why not remove conversation_parts?

Do not remove it. The two views have different cost profiles.

conversation_parts is the cheap metadata view. Use it for:

• Counts.
• Channel, author, actor-type, and date filtering.
• Recent customer reply scans.
• Bot-vs-human checks.
• Selecting candidate conversation_ids before reading transcript text.

conversation_parts_full is the expensive content view. Use it for:

• Reading message text.
• Snippets.
• Transcript-like evidence.
• Content-based inspection after narrowing the candidate set.

Removing conversation_parts would force metadata-only queries through the full-content view and would likely make ordinary counts and scans slower. The better fix is to keep both views and block the bad mixed usage.

Local DB evidence

Local row counts are tiny, so local timings are not production-representative:

Useful existing indexes:

• conversations: primary key, prefix_id, many org/date/risk indexes.
• conversations: (organization_id, external_updated_at).
• conversations: (organization_id, started_at).
• conversations: GIN on search_vector.
• conversation_parts: conversation_id, (conversation_id, author_type), external_created_at, organization_id, and type.
• Detail tables have btree indexes on conversation_part_id.

Important gaps:

• No composite part index for organization_id + conversation_id + external_created_at.
• No composite part index for organization_id + author_type + channel_type + external_created_at.
• No GIN/trigram indexes on chat/email/note body or content columns.
• conversation_parts_full.content is a view-level COALESCE across chat, email, note, and call transcript sources.
• conversations.search_vector is indexed, but the failed export did not use it.

Root causes

1. The full-content view is expensive by design

conversation_parts_full.content is not a stored column. It is flattened out of:

• Chat detail content and redacted content.
• Email body and redacted body.
• Note body and redacted body.
• Call transcript aggregation.

That is the right view for reading text, but it is the wrong first stop for broad scans.

2. The agent repeatedly joins the metadata and full-content views

conversation_parts_full already exposes id, conversation_id, channel_type, author_type, author_actor_type, author_name, external_created_at, and created_at. Joining conversation_parts to conversation_parts_full just to get those columns adds work without adding information.

3. Agents use SQL for text search

The export had 730 content-text-scan failures and 554 ILIKE/LIKE failures. Leading-wildcard ILIKE '%term%' and regex predicates over conversation_parts_full.content do not have an ordinary btree access path.

4. Agents do not use the available indexed search path

The conversations.search_vector column is exposed and backed by a GIN index, but zero exported failed queries used it. That suggests get_schema and tool docs do not make the safe search path clear enough.

5. Provider detection is being done through URL text

335 exported failures used external_url ILIKE '%zendesk%'. That should be replaced with a structured provider or data-source field in agent_api.conversations if one is available.

Recommendations

1. Add a validator guard for the self-join anti-pattern

Reject any query_data SQL that references both conversation_parts and conversation_parts_full.

Suggested error:

Do not join conversation_parts to conversation_parts_full. conversation_parts_full
already includes id, conversation_id, author/channel/date metadata. Use
conversation_parts for metadata-only scans, or use conversation_parts_full
directly when you need content.

Impact: This directly targets 463 of the 1,000 exported failures and turns a 20-second DB timeout into an immediate repairable tool error.

2. Add performance guidance to get_schema

Add explicit performance notes to the schema metadata for high-volume views:

conversation_parts:
  Cheap metadata view. Use for counts, author/channel/date filters, candidate
  selection, and grouping. Does not include content.

conversation_parts_full:
  Expensive content view. Use only when message text is required. Do not join
  it to conversation_parts. Narrow by conversation_id, author_type, channel_type,
  or external_created_at before selecting content.

conversations.search_vector:
  GIN-indexed search over external_id, subject, Jira issue key, and summary.
  Prefer this over subject/summary ILIKE when searching conversation metadata.

3. Add composite indexes for repeated part-scan shapes

These should be Rails migrations because the underlying tables are Rails-owned public.* tables, not API2 Drizzle-owned api2.* tables.

Candidate indexes:

CREATE INDEX CONCURRENTLY idx_conversation_parts_org_conv_created
ON public.conversation_parts (
  organization_id,
  conversation_id,
  external_created_at
);
 
CREATE INDEX CONCURRENTLY idx_conversation_parts_org_author_type_created
ON public.conversation_parts (
  organization_id,
  author_type,
  type,
  external_created_at DESC,
  conversation_id
);

Also consider a partial open-ticket recency index:

CREATE INDEX CONCURRENTLY idx_conversations_org_open_ticket_updated
ON public.conversations (
  organization_id,
  external_updated_at
)
WHERE ticket_status = 0
  AND interaction_type = 'ticket'
  AND source_deleted_at IS NULL;

Validate against production table sizes and existing index bloat before shipping.

4. Stop provider filtering via external_url ILIKE

Expose a structured provider/source field in agent_api.conversations if one is available from the backing Rails schema. Then teach agents to use that field instead of URL substring matching.

5. Add a transcript search tool

Add a dedicated search_conversation_parts tool backed by Turbopuffer, Postgres full-text search, or a denormalized transcript-search table. It should return:

• conversation_id.
• part_id.
• external_created_at.
• author_type.
• author_actor_type.
• channel_type.
• Snippet.
• Match score.

Then SQL can do deeper analysis over a small set of IDs instead of using regex or ILIKE over the full transcript view.

6. Attach plan summaries on timeout

On statement timeout, run non-ANALYZE EXPLAIN (FORMAT JSON) with the same search_path, role, org setting, and read-only transaction settings, then attach a compact plan summary to Braintrust or Sentry. This would make the next timeout cluster faster to classify.

Agent-facing rules to add

- If you need message text, query conversation_parts_full directly.
  Do not join conversation_parts to conversation_parts_full on id.

- If you only need counts, dates, authors, channels, or part ids,
  use conversation_parts, not conversation_parts_full.

- Avoid content ILIKE '%...%' and regex over conversation_parts_full.content
  unless you have already reduced to a small conversation_id set.

- For broad keyword search, use the transcript search tool when available.
  If not available, prefer conversations.search_vector for subject, summary,
  external id, and Jira key before reading message bodies.

- Do not detect Zendesk or other providers with external_url ILIKE.
  Use the structured provider/source column when exposed.

- For recent customer-message scans, filter parts by author_type, channel_type,
  and external_created_at before reading content.

Immediate implementation plan

1. Add get_schema descriptions and managed-agent SQL-convention bullets for the view split.
2. Add validator rejection for queries referencing both conversation_parts and conversation_parts_full.
3. Add regression tests for the validator and prompt text.
4. Add Rails index migrations for the two repeated conversation_parts shapes after checking production index size and query frequency.
5. Design the transcript search tool as a separate path from SQL.