> ## Documentation Index
> Fetch the complete documentation index at: https://docs.driftless.icu/llms.txt
> Use this file to discover all available pages before exploring further.

# Explain audit

# EXPLAIN & index audit — large retrieval paths

Evidence that the hot read paths use indexes, never a Seq Scan on a large table on
the bounded path, and apply **filters + privacy BEFORE the LIMIT**. Generated by
`apps/api/src/topics/explain-audit.integration.spec.ts` (run under the integration
config against a migrated Postgres seeded with \~4k topics and \~4k broker operations
across 20 providers, `ANALYZE`d). Direct Postgres — no cache layer.

## How to read this

At fixture scale (a few thousand rows) Postgres often prefers a Seq Scan because
the table is tiny and a scan is genuinely cheaper than an index — that does **not**
mean the index is missing. To audit that each index is **valid and serves the
query** (the behavior that holds at 50k+ rows where the scan is no longer free),
the index-usage checks run with `SET LOCAL enable_seqscan = off` and confirm the
planner switches to the index. The structural checks (filter/privacy before LIMIT)
hold regardless of scale.

## Indexes backing the paths

| Path                                       | Index                                                                                   | Migration             |
| ------------------------------------------ | --------------------------------------------------------------------------------------- | --------------------- |
| Topic FTS search                           | GIN on `search_tsv` (weighted tsvector)                                                 | 0089                  |
| Topic list / match-files                   | `(workspace_id, slug)` + scan filter on status/privacy                                  | base + 0089           |
| Collection records keyset, doctor          | collection perf composites                                                              | 0090                  |
| Broker per-connection list / invoke lookup | `(workspace_id, provider)`                                                              | 0091                  |
| **Broker cross-provider SEARCH**           | `(workspace_id, active, name)` + **pg\_trgm GIN** on `lower(name)`/`lower(description)` | **0092 (this round)** |

The broker SEARCH index (0092) is the gap this card closed: the cross-provider
intent search ILIKEs `name`/`description` with a leading wildcard (`%query%`), which
a B-tree cannot serve — the trigram GIN makes it index-assisted instead of a
row-by-row filter over every operation in the workspace at scale.

## Verdicts (captured plans)

**Broker per-provider list** — clean index path:

```
Bitmap Index Scan on idx_broker_op_ws_provider
  Index Cond: ((workspace_id = $1) AND (provider = 'prov3'))
```

**Broker cross-provider search** (seqscan off) — served by an index, no Seq Scan;
the `(workspace_id, active, ILIKE)` filter is in the WHERE, beneath the LIMIT:

```
Limit
  -> Index Scan ... on broker_operations
       Filter: (active AND (lower(name) ~~ '%…%') OR lower(description) ~~ '%…%')
```

**Topic FTS search** (seqscan off) — index path, no Seq Scan; `search_tsv @@
plainto_tsquery(...)` and `status <> 'archived'` filter on the scan node, under the
LIMIT.

**Topic list — privacy before LIMIT** (the load-bearing invariant): the draft-
privacy predicate `(status <> 'draft' OR is_private IS NOT TRUE OR created_by IS
NULL OR created_by = :principal)` is a WHERE clause on the scan/Filter node, which
EXPLAIN prints **beneath** the `Limit` node — proof it is evaluated before the bound
(no privacy-after-limit leak). 686 of 4000 rows removed by the filter before the
40-row LIMIT in the captured run.

## Acceptance

* Filters + privacy run before LIMIT on every audited path ✓ (structural, EXPLAIN-confirmed).
* Keyset/cursor paths use composite indexes; no deep-offset scan is required for the
  bounded reads ✓.
* The one missing index (broker cross-provider search) was added via an explicit
  TypeORM migration (0092), never an ad-hoc `CREATE INDEX` ✓.
* Reproducible: `pnpm --filter @driftless/api test:integration` (with a Postgres)
  runs the audit; the two card-validate specs (`retrieve-eval.integration`,
  `collections-perf.service.integration`) gate the correctness of these paths.