# Snowflake Schema Index — TRD

## Status

Draft — 2026-03-22

**Update (2026-03-24):** Incremental polling was implemented. The index now uses `fetchCatalogDiff()` with `LAST_ALTERED` filtering and `HybridSearch.add()/update()/remove()` instead of full rebuilds. The two-phase lifecycle described below was consolidated into a single-pass load (`phase2Complete` / `isPhase2Complete` were removed).

## Overview

The Snowflake tools today issue live `SHOW TABLES LIKE '%query%'` and `DESCRIBE TABLE` calls on every user request. This means every table discovery re-runs the same metadata queries, search is limited to Snowflake's `LIKE` pattern matching (no semantic or glossary-based ranking), and the agent has no awareness of joinable relationships between tables.

The Snowflake Schema Index solves this with a **two-phase indexing strategy**:

- **Phase 1 (fast metadata scan):** At server startup, run `SHOW TABLES IN ACCOUNT` and `SHOW VIEWS IN ACCOUNT` to build a lightweight in-memory catalog. This completes in 1-5 seconds and immediately enables keyword search over table names, databases, schemas, and comments.

- **Phase 2 (background column enrichment):** After Phase 1 completes, batch-`DESCRIBE` all tables with a concurrency-limited semaphore. Once column metadata is available, rebuild the search index with embeddings and glossary context, and infer a foreign-key relationship graph from column naming conventions.

The agent cannot search 1,000+ tables via live `DESCRIBE` on every query. Two-phase indexing gives it immediate search capability (Phase 1) while full column-level search, semantic ranking, and join discovery build in the background (Phase 2). This mirrors the existing GraphQL schema index pattern.

## Goals

1. **Eliminate redundant metadata queries.** Introspect once at startup, serve from memory, poll for changes.
2. **Enable hybrid search over Snowflake metadata.** Keyword + vector + glossary ranking over table names, comments, and column names.
3. **Infer table relationships.** Build an FK graph from column naming conventions so the agent can discover joinable tables without manual exploration.
4. **Graceful degradation.** If introspection fails or config is missing, all tools fall back to their current live-query behavior. The index is a quality/performance improvement, not a requirement.
5. **Non-blocking startup.** Index building is fire-and-forget. The server starts serving requests immediately.

## Architecture

### Two-Phase Lifecycle

The index has three states: unloaded, Phase 1 complete (keyword-only search), and Phase 2 complete (full hybrid search with column data and FK graph). The server never blocks on index construction.

```mermaid
stateDiagram-v2
    [*] --> Unloaded
    Unloaded --> Phase1Loading : warmUpSnowflakeIndex()
    Phase1Loading --> Phase1Complete : SHOW TABLES/VIEWS succeed
    Phase1Loading --> Unloaded : permanent error (auth, account, IP)
    Phase1Loading --> Phase1Loading : transient error (retry with backoff)
    Phase1Complete --> Phase2Loading : background DESCRIBE starts
    Phase2Loading --> FullyLoaded : all DESCRIBEs complete, index rebuilt
    Phase2Loading --> Phase1Complete : Phase 2 fails (columns = null, search still works)
    FullyLoaded --> Polling : startSnowflakePolling()
    Polling --> FullyLoaded : schema change detected, incremental update
```

### Timing Diagram

```
Server Start
  |
  t=0s    warmUpSnowflakeIndex() fires (non-blocking)
  |
  |        ┌─────────────────────────────────────────────────────┐
  |        │  Phase 1: SHOW TABLES + SHOW VIEWS IN ACCOUNT      │
  |        │  2 sequential queries                               │
  |        │  Expected: 1-5 seconds                              │
  |        └──────────┬──────────────────────────────────────────┘
  |                   |
  t=1-5s   Phase 1 complete. Keyword-only HybridSearch built.
  |        Tools can now search by table name, database, comment.
  |                   |
  |        ┌──────────v──────────────────────────────────────────┐
  |        │  Phase 2: batch DESCRIBE (concurrency = 5)          │
  |        │  Each DESCRIBE: ~50-100ms                           │
  |        │  1,000 tables at concurrency 5: ~10-20 seconds      │
  |        │  Individual failures: logged and skipped             │
  |        └──────────┬──────────────────────────────────────────┘
  |                   |
  t=15-25s Phase 2 complete. Full HybridSearch rebuilt with:
  |        - Column names and comments in search documents
  |        - Embedding vectors via TransformersEmbeddingProvider
  |        - Glossary context injected from snowflake-glossary.json
  |        - FK graph inferred from column naming conventions
  |                   |
  |        ┌──────────v──────────────────────────────────────────┐
  |        │  Polling: every 5 min (configurable)                │
  |        │  Re-run SHOW, compare changed_on timestamps         │
  |        │  Only DESCRIBE changed/new tables                   │
  |        │  Rebuild index only if schema hash changes          │
  |        └─────────────────────────────────────────────────────┘
```

### System Pool

A new lightweight connection pool dedicated to metadata-only operations. This pool is separate from the per-request pool to avoid contention.

| Characteristic | System Pool (new)                              | Request Pool (existing)               |
| -------------- | ---------------------------------------------- | ------------------------------------- |
| Purpose        | Schema introspection (SHOW, DESCRIBE)          | User queries (SELECT)                 |
| Identity       | None (metadata only, no session variables)     | Per-request session variables for RLS |
| Lifecycle      | Server startup through shutdown                | Per-request acquire/release           |
| Pool size      | 1-2 connections                                | 2-20 connections                      |
| Used by        | Schema index polling                           | Tool handlers                         |
| Auth           | Same `SNOWFLAKE_READER_*` key-pair credentials | Same credentials + session vars       |

The system pool uses the same `SNOWFLAKE_READER_*` credentials (account, user, role, warehouse, private key). No new credential configuration is required. It is created via `createSystemPool()` in `server/src/db/snowflake/pool.ts` and uses the existing `SecureConnection` read-only guard (SHOW/DESCRIBE/SELECT only, single-statement, no comments).

### Connection to Snowflake RBAC Model

The Snowflake Schema Index operates within the existing RBAC framework documented in `docs/snowflake-views.md`:

- **Service identity:** The system pool authenticates as `AGENT_ANALYTICS_SVC_USER` with the `AGENT_ANALYTICS_SVC` role, using key-pair authentication. The private key is stored in AWS Secrets Manager.
- **Visibility boundary:** `SHOW TABLES/VIEWS IN ACCOUNT` and `DESCRIBE TABLE` return only objects visible to the service role. Source teams control what the agent can see by granting their database roles (e.g., `MY_DB.AGENT_READ`) to `AGENT_ANALYTICS_SVC`.
- **No session variables on the system pool.** Session variables (`APP_PROFILE_ID`, `APP_PROFILE_TYPE`, etc.) are only relevant for row-level access policies on `SELECT` queries. Metadata operations (SHOW, DESCRIBE) return the same results regardless of session variables, so the system pool skips identity context entirely.
- **AGENT_CONSUMABLE tag discovery** is out of scope for this iteration. When source teams begin tagging views with `AGENT_CONSUMABLE`, the index can boost tagged views via glossary priority or a scoring modifier.

### HybridSearch Integration

A single `HybridSearch<SnowflakeTableEntry>` instance indexes all tables and views. Unlike the GraphQL schema index (which has separate query-field and type indexes), Snowflake tables/views form a flat catalog and one index is sufficient.

The search document for each table concatenates:

- Table name (e.g., `VW_REVENUE_BY_COUNTRY`)
- Database and schema names
- Comment (if present)
- Column names (Phase 2)
- Column comments (Phase 2)
- Glossary context from `snowflake-glossary.json` (if matched)

The embedding provider is the shared `TransformersEmbeddingProvider` singleton, also used by the GraphQL schema index. No duplicate model loading.

### Component Diagram

```mermaid
graph TD
    subgraph Server Startup
        S[server.ts] -->|creates| SP[System Pool]
        S -->|calls| W[warmUpSnowflakeIndex]
        S -->|calls| P[startSnowflakePolling]
    end

    subgraph Schema Index Facade
        W --> SI[schema-index.ts]
        P --> SI
        SI -->|Phase 1| I[introspect.ts]
        SI -->|Phase 2| I
        SI -->|rebuild| SR[search.ts]
    end

    subgraph Introspection
        I -->|SHOW TABLES/VIEWS| SP
        I -->|DESCRIBE TABLE| SP
        SP -->|SecureConnection| SF[(Snowflake)]
    end

    subgraph Search Layer
        SR --> HS[HybridSearch]
        SR --> FKG[FK Graph]
        SR --> GL[snowflake-glossary.json]
        SR --> EP[TransformersEmbeddingProvider]
    end

    subgraph Tool Handlers
        T1[search_snowflake_schema] -->|index search| SI
        T2[search_snowflake_tables] -->|index first, live fallback| SI
        T3[describe_snowflake_table] -->|cache first, live fallback| SI
        T4[snowflake_explore_skill] -->|index first, live fallback| SI
    end
```

## Detailed Design

### Phase 1: Table Catalog

On server startup, `warmUpSnowflakeIndex()` is called fire-and-forget:

1. Acquire a connection from the system pool.
2. Execute `SHOW TABLES IN ACCOUNT` and `SHOW VIEWS IN ACCOUNT` (two queries, run in parallel via `Promise.all`).
3. Parse results into `SnowflakeTableEntry[]` with: `fqn` (DATABASE.SCHEMA.TABLE), `database`, `schema`, `name`, `kind` (table/view), `comment`, `rowCount`, `changedOn` timestamp, `columns: null`, and tokenized `keywords`.
4. Build a `SnowflakeSchemaState` containing the table array and a `changeMap` (Map of FQN to `changedOn` for change detection).
5. Build a keyword-only `HybridSearch` over the table-level metadata (no embeddings yet).
6. Set `loaded = true`. Tools can now search by table name, database, and comment.

### Phase 2: Column Enrichment

After Phase 1 completes, Phase 2 runs in the background:

1. For every table in the catalog, issue `DESCRIBE TABLE "DATABASE"."SCHEMA"."TABLE"` with a concurrency-limited semaphore (default 5 concurrent).
2. As each `DESCRIBE` completes, populate the table entry's `columns` array with `SnowflakeColumnEntry[]` (name, type, nullable, comment). Failures are logged and skipped; the table retains `columns: null`.
3. Re-tokenize each table's keywords to include column name segments.
4. Rebuild the `HybridSearch` with column-enriched documents and embedding vectors via `TransformersEmbeddingProvider`.
5. Build the inferred FK graph (see below).
6. Load `snowflake-glossary.json` and inject glossary context into search documents.
7. Set `phase2Complete = true`.

### Polling via changed_on

After initial load, a configurable interval timer (default 5 minutes) polls for schema changes:

1. Re-run `SHOW TABLES/VIEWS IN ACCOUNT`.
2. Build a new `changeMap` and compute a SHA-256 schema hash (sorted `fqn:changedOn` pairs).
3. If the hash matches the last hash, skip (no changes).
4. If changed, diff the old and new change maps to identify added, changed, and removed tables.
5. Carry forward column data for unchanged tables.
6. Only `DESCRIBE` tables whose `changedOn` has changed or that are newly added.
7. Remove tables that no longer appear (dropped).
8. Rebuild `HybridSearch` and FK graph.

A typical poll with no changes: 2 SHOW queries, 0 DESCRIBEs, no rebuild. A poll with 5 changed tables: 2 SHOW + 5 DESCRIBE + rebuild, approximately 2-3 seconds.

### Retry Strategy

Phase 1 retries transient errors with exponential backoff:

| Attempt | Delay                |
| ------- | -------------------- |
| 1       | 10 seconds           |
| 2       | 20 seconds           |
| 3       | 40 seconds           |
| 4       | 80 seconds           |
| 5       | 120 seconds (capped) |

After 5 retries, the index is abandoned. Tools fall back to live queries (existing behavior). Polling retries are handled per-tick: a failed poll logs a warning and tries again at the next interval.

**Permanent errors (no retry):**

| Error Code     | Meaning                                       |
| -------------- | --------------------------------------------- |
| `390100`       | Authentication failure (bad key/credentials)  |
| `390201`       | Account not found or disabled                 |
| `390422`       | IP not whitelisted                            |
| Config missing | No `SNOWFLAKE_READER_*` environment variables |

### FK Inference Algorithm

After Phase 2, an in-memory FK graph is built from column naming conventions:

1. Build a lookup map: `table_name (uppercased)` to FQN.
2. Add every table as a node in a `Graph<SnowflakeTableEntry>`.
3. For each column in each table, check if the column name matches the pattern `<X>_ID`.
4. For each match, try to resolve `X` to a table by checking candidates in order: `X`, `DIM_X`, `FACT_X`.
5. If a match is found and it is not a self-reference, add an edge: `this_table --references--> target_table`. First match wins.

**Example:** `FACT_REVENUE` has column `VENDOR_ID` and `DIM_VENDOR` exists in the index. Edge: `FACT_REVENUE --references--> DIM_VENDOR`.

This requires zero additional Snowflake queries. The graph enables the agent to answer "what tables can I join with FACT_REVENUE?" by traversing neighbors.

### Glossary

A `snowflake-glossary.json` file maps business terms to Snowflake table/view names. It uses the same `GlossaryEntry` interface as the GraphQL glossary:

```json
{
  "entries": [
    {
      "terms": ["revenue", "revenue by country", "sales by territory"],
      "targets": ["VW_REVENUE_BY_COUNTRY"],
      "context": "Revenue breakdown by country/territory from royalty accounting",
      "domain": "revenue",
      "priority": "primary"
    }
  ]
}
```

At index build time, glossary context is injected into each matching table's search document, boosting relevance for business-term queries. The initial glossary covers revenue, contracts, balances, flowthrough, vendor, lifecycle, migration, and neighbouring rights domains.

### Data Model

```typescript
interface SnowflakeTableEntry {
  fqn: string; // "DATABASE.SCHEMA.TABLE"
  database: string;
  schema: string;
  name: string;
  kind: "table" | "view";
  comment: string | null;
  rowCount: number | null;
  changedOn: string; // DDL change timestamp from SHOW
  columns: SnowflakeColumnEntry[] | null; // null until Phase 2
  keywords: string[]; // tokenized for search scoring
}

interface SnowflakeColumnEntry {
  name: string;
  type: string;
  nullable: boolean;
  comment: string | null;
}

interface SnowflakeSchemaState {
  tables: SnowflakeTableEntry[];
  changeMap: Map<string, string>; // FQN -> changedOn
}
```

### Tool Integration

| Tool                            | Current behavior                  | With schema index                                                                 |
| ------------------------------- | --------------------------------- | --------------------------------------------------------------------------------- |
| `search_snowflake_tables`       | Live `SHOW TABLES LIKE '%query%'` | Search index first; live fallback if not loaded                                   |
| `describe_snowflake_table`      | Live `DESCRIBE TABLE`             | Serve from index cache; live fallback if columns not cached                       |
| `query_snowflake`               | Executes user SQL                 | No change                                                                         |
| `snowflake_explore_skill`       | Live `SHOW` + batch `DESCRIBE`    | Search index + cached columns + FK neighbors; live fallback                       |
| `search_snowflake_schema` (new) | N/A                               | HybridSearch over full index with FK neighbors; only enabled when index is loaded |

All existing tools include graceful degradation: if the index is not loaded (startup in progress, introspection failed, config missing), they fall back to their current live-query behavior.

### File Structure

```
server/src/ai/tools/snowflake/
  schema-index.ts       # NEW — facade: lifecycle, polling, module state
  introspect.ts         # NEW — SHOW/DESCRIBE, data model, change detection
  search.ts             # NEW — HybridSearch adapter, FK graph, glossary
  definitions.ts        # MODIFY — add search_snowflake_schema
  handlers.ts           # MODIFY — add index-backed handler with live fallback
  index.ts              # MODIFY — re-export new public API
  __tests__/
    system-pool.test.ts
    introspect.test.ts
    search.test.ts
    schema-index.test.ts
    search-handler.test.ts

server/src/search/glossaries/
  snowflake-glossary.json  # NEW — business term -> table mappings

server/src/ai/skills/snowflake-explore/
  handler.ts            # MODIFY — use index with live fallback

server/src/db/snowflake/
  pool.ts               # MODIFY — add createSystemPool()
  types.ts              # MODIFY — add SystemPoolConfig

server/src/config/load-config.ts   # MODIFY — add schema index env vars
server/src/types/domain.ts         # MODIFY — add SnowflakeSchemaConfig
server/src/app-locals.ts           # MODIFY — add snowflakeSystemPool
server/src/server.ts               # MODIFY — create system pool, call warmup
```

## Alternatives Explored

### Why two-phase instead of single-phase?

A single-phase approach that runs SHOW + DESCRIBE for all tables before building the index would delay the first usable search by 15-25 seconds. Two-phase gives the agent search capability after 1-5 seconds (table names, databases, comments) while column-level detail builds in the background. The agent already handles "columns not yet available" gracefully by issuing a live DESCRIBE for a specific table when needed.

### Why not pre-cache everything at deploy time?

Pre-caching (e.g., storing schema snapshots in Redis or the database) was considered but rejected because:

- Schema changes would become stale between deploys.
- It adds a deployment dependency (cache must be warm before tools work).
- The live introspection approach (1-5s Phase 1) is fast enough to not need pre-caching.
- The polling mechanism handles schema changes automatically.

### Why a system pool instead of reusing the request pool?

The request pool manages per-request session variables (`APP_PROFILE_ID`, `APP_PROFILE_TYPE`, etc.) for row-level security. The system pool:

- Avoids setting/clearing session variables on connections used only for metadata queries.
- Uses a smaller pool (1-2 connections vs. 2-20) to minimize Snowflake session overhead.
- Has an independent lifecycle (server start to shutdown) rather than per-request acquire/release.
- Prevents metadata polling from competing with user query connections.

### Why inferred FK graph instead of Snowflake's actual FK constraints?

Snowflake's `SHOW IMPORTED KEYS` / `SHOW EXPORTED KEYS` could provide actual FK metadata, but:

- Many Snowflake deployments (including the current royalty platform views) do not define formal FK constraints.
- The naming convention (`<X>_ID` referencing table `X`, `DIM_X`, or `FACT_X`) is well-established in the data warehouse.
- Inference is zero-cost (pure in-memory matching) and requires no additional Snowflake queries.
- False positives are tolerable because the FK graph is advisory (for join suggestions), not enforced.

## Cost Analysis

### Snowflake Compute

- **SHOW TABLES/VIEWS IN ACCOUNT:** These are metadata queries that run against Snowflake's metadata layer, not the warehouse compute engine. They incur no warehouse credit cost.
- **DESCRIBE TABLE:** Also a metadata query. No warehouse credit cost.
- **Polling overhead:** At a 5-minute interval, the system runs 2 SHOW queries per poll. Over 24 hours, that is 576 SHOW queries, all on the metadata layer. Changed-table DESCRIBEs are incremental.

**Net Snowflake cost impact: effectively zero.** All queries are metadata-only.

### Engineering Effort

- 3 new modules (introspect, search, schema-index): ~800 lines total
- Modifications to 7 existing files: ~200 lines of additions
- 5 new test files: ~400 lines
- Estimated: 3-5 engineering days for implementation + testing

### Memory

See Performance Analysis below.

## Performance Analysis

### Phase 1 (Table Catalog)

| Metric                 | Value                                                |
| ---------------------- | ---------------------------------------------------- |
| Queries                | 2 (SHOW TABLES + SHOW VIEWS, parallelized)           |
| Latency                | 1-5 seconds                                          |
| Blocking               | None (fire-and-forget)                               |
| Search available after | Phase 1 completion                                   |
| Search quality         | Keyword-only (table name, database, schema, comment) |

### Phase 2 (Column Enrichment)

| Metric                 | Value                                                          |
| ---------------------- | -------------------------------------------------------------- |
| Queries                | 1 DESCRIBE per table, concurrency-limited to 5                 |
| Per-DESCRIBE latency   | ~50-100ms                                                      |
| Total for 1,000 tables | ~10-20 seconds at concurrency 5                                |
| Failure handling       | Individual failures logged and skipped                         |
| Search quality after   | Full hybrid (keyword + vector + glossary + columns + FK graph) |

### Search Performance

| Metric                                 | Value                                               |
| -------------------------------------- | --------------------------------------------------- |
| Search latency (keyword-only, Phase 1) | Sub-millisecond                                     |
| Search latency (full hybrid, Phase 2)  | Sub-millisecond (in-memory index)                   |
| Index rebuild time                     | < 1 second for keyword; 1-2 seconds with embeddings |

### Memory Footprint

| Component                    | Estimate       |
| ---------------------------- | -------------- |
| Table entries (with columns) | ~1KB per table |
| 1,000 tables                 | ~1MB           |
| HybridSearch vectors         | ~1-2MB         |
| FK graph                     | < 0.5MB        |
| **Total**                    | **~3-5MB**     |

### Polling Performance

| Scenario         | Queries                        | Time         |
| ---------------- | ------------------------------ | ------------ |
| No changes       | 2 SHOW, 0 DESCRIBE, no rebuild | < 2 seconds  |
| 5 changed tables | 2 SHOW + 5 DESCRIBE + rebuild  | ~2-3 seconds |
| 50 new tables    | 2 SHOW + 50 DESCRIBE + rebuild | ~5-7 seconds |

## Scaling Characteristics

### Hundreds of Tables (current state)

Phase 1 and Phase 2 complete quickly. Memory is negligible. No concerns.

### Thousands of Tables

Phase 2 concurrency becomes meaningful. At 5,000 tables with concurrency 5, Phase 2 takes ~50-100 seconds. This is acceptable because:

- Phase 1 completes in seconds and provides immediate search.
- Phase 2 runs entirely in background.
- Polling is incremental (only changed tables re-described).

If Phase 2 time becomes a concern, increase `SNOWFLAKE_SCHEMA_DESCRIBE_CONCURRENCY` (at the cost of more concurrent Snowflake sessions).

### 10,000+ Tables

At this scale:

- Memory grows to ~10-50MB (still manageable).
- Phase 2 could take several minutes.
- The keyword-based search in HybridSearch remains fast (in-memory), but embedding computation during index rebuild could become noticeable.
- **HNSW vector search** would be needed at this scale for sub-linear embedding lookups (known future extension).

## Breakdown Points & Mitigations

### Snowflake Unreachable at Startup

**Risk:** Network issues or Snowflake maintenance prevents initial introspection.

**Mitigation:** Retry with exponential backoff (10s, 20s, 40s, 80s, 120s, up to 5 retries). If all retries fail, log a warning and operate without the index. Tools fall back to live queries. The server is fully functional without the index.

### Stale Index

**Risk:** Tables are added, dropped, or altered between polls.

**Mitigation:** Polling at 5-minute intervals with `changed_on` comparison. A 5-minute staleness window is acceptable for schema metadata. The `changed_on` timestamp from Snowflake's `SHOW` output tracks DDL changes, so structural modifications are detected. Data-only changes (INSERT/UPDATE) do not require index updates.

### FK Inference False Positives

**Risk:** A column named `COUNTRY_ID` matches table `COUNTRY` but the relationship is not a real FK.

**Mitigation:** The FK graph is advisory only. It surfaces join suggestions for the agent but does not enforce anything. False positives result in occasionally suggesting an irrelevant join, which the agent can evaluate based on the table's columns and comments. The alternative (no join suggestions at all) is worse for user experience.

### Memory Pressure

**Risk:** Very large catalogs consume significant memory.

**Mitigation:** At ~1KB per table, even 10,000 tables consume only ~10MB for table data plus ~10-20MB for vectors. Monitor memory usage. If needed, filter the catalog to only index tables in specific databases or schemas (e.g., only those visible to the service role, which is already the case via RBAC).

### Permanent Auth/Config Errors

**Risk:** Bad credentials, disabled account, or IP restrictions cause immediate failure.

**Mitigation:** Permanent errors (codes 390100, 390201, 390422) and missing config are detected and not retried. A clear error message is logged. The server continues without the index.

## Decision Log

| Decision                                             | Rationale                                                                  |
| ---------------------------------------------------- | -------------------------------------------------------------------------- |
| Two-phase index (immediate keyword, background full) | Minimizes time-to-first-search while still building a rich index           |
| System pool (1-2 connections)                        | Separates metadata ops from user queries; avoids session variable overhead |
| `SHOW ... IN ACCOUNT` scope                          | Discovers all tables visible to the service role across all databases      |
| SHA-256 schema hash for poll diffing                 | Avoids unnecessary index rebuilds when nothing has changed                 |
| Inferred FK graph from `_ID` convention              | Zero-cost relationship discovery; no additional Snowflake queries          |
| Single `HybridSearch` instance (not per-database)    | Flat catalog; one index is simpler and sufficient                          |
| Shared `TransformersEmbeddingProvider` singleton     | Avoids duplicate model loading (already used by GraphQL index)             |
| Glossary as static JSON file                         | Same pattern as GraphQL; easy to extend without code changes               |
| Exponential backoff with 2-minute cap                | Balances recovery speed against not hammering a failing service            |
| Fire-and-forget startup                              | Server availability is not gated on Snowflake reachability                 |

## Dependencies

| Dependency                                                | Type           | Notes                                         |
| --------------------------------------------------------- | -------------- | --------------------------------------------- |
| `HybridSearch` from `@server/search`                      | Internal       | Already exists; used by GraphQL schema index  |
| `Graph` from `@coda/common`                               | Internal       | Already exists; used by GraphQL FK graph      |
| `TransformersEmbeddingProvider` from `@server/search`     | Internal       | Singleton; shared with GraphQL                |
| `Semaphore` from `@coda/common`                           | Internal       | Already exists; used for concurrency limiting |
| `SecureConnection` from `@server/db/snowflake/connection` | Internal       | Read-only query guard                         |
| `tokenize` from `@server/ai/utils/tokenize`               | Internal       | Keyword tokenization                          |
| `snowflake-sdk`                                           | External (npm) | Snowflake Node.js driver                      |
| Snowflake `AGENT_ANALYTICS_SVC_USER`                      | Infrastructure | Service user with key-pair auth               |
| AWS Secrets Manager                                       | Infrastructure | Stores Snowflake private key                  |

No new external dependencies. All building blocks exist in the codebase. No changes to `common/` or `@server/search` are needed.

## Testing Strategy

### Unit Tests

Each new module has dedicated unit tests with mocked dependencies:

| Test File                | Coverage                                                                                                                                         |
| ------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------ |
| `system-pool.test.ts`    | `createSystemPool` creates pool with correct config and pool size overrides                                                                      |
| `introspect.test.ts`     | `parseShowTablesRows` parsing, `parseDescribeRows` parsing, `buildTableEntry` keyword tokenization, `diffChangeMaps` add/change/remove detection |
| `search.test.ts`         | `buildFkGraph` FK inference (DIM\_ prefix, exact match, self-reference prevention, null columns)                                                 |
| `schema-index.test.ts`   | Lifecycle state (`isSnowflakeIndexLoaded`), polling timer management (`start`/`stop`/`replace`)                                                  |
| `search-handler.test.ts` | `search_snowflake_schema` handler returns results from index, validates required `query` param                                                   |

All tests mock the Snowflake SDK, logger, and inter-module dependencies. No real Snowflake connections are needed.

### Integration Verification

After implementation:

1. `pnpm lint` — no lint errors in new/modified files.
2. `pnpm typecheck` — no type errors.
3. `pnpm test:unit` — all existing tests continue to pass, all new tests pass.
4. Manual verification with `SNOWFLAKE_READER_*` credentials configured: confirm Phase 1 log message within 5 seconds, Phase 2 completion within 30 seconds, and `search_snowflake_schema` tool returns results.

## Rollout Plan

### Phase 1: Implementation (Tasks 1-5)

1. **System pool infrastructure** — Add `createSystemPool()` to `pool.ts`.
2. **Introspection module** — Data model, SHOW/DESCRIBE parsing, change detection.
3. **Search adapter** — HybridSearch integration, FK graph builder, glossary loading.
4. **Schema index facade** — Two-phase lifecycle, polling, module state.
5. **Tool definition and handler** — `search_snowflake_schema` tool with index-backed search.

### Phase 2: Integration (Tasks 6-7)

6. **Update existing tools** — Add index-first logic with live fallback to `search_snowflake_tables`, `describe_snowflake_table`, and `snowflake_explore_skill`.
7. **Configuration and server startup** — Add env vars, create system pool in `server.ts`, wire up warmup and polling.

### Phase 3: Verification (Task 8)

8. **End-to-end verification** — Full lint/typecheck/test suite, `.env.shadow` documentation.

### Feature Flag / Opt-In

The feature is opt-in by nature: if `SNOWFLAKE_READER_*` environment variables are not configured, the schema index is skipped entirely. No code changes are needed to disable it. In environments with Snowflake credentials, the index activates automatically.

### Rollback

Remove the `warmUpSnowflakeIndex()` and `startSnowflakePolling()` calls from `server.ts`. All tools revert to live-query behavior. No data migration or cleanup needed.

## Open Questions

1. **`SHOW ... IN ACCOUNT` privileges.** Does the `AGENT_ANALYTICS_SVC` role have sufficient privileges for `SHOW TABLES IN ACCOUNT` / `SHOW VIEWS IN ACCOUNT`? If not, the fallback is `SHOW ... IN DATABASE` scoped to the pool's default database. This needs verification against the actual Snowflake role grants.

2. **AGENT_CONSUMABLE tag integration.** When source teams begin tagging views with `AGENT_CONSUMABLE`, should tagged views receive a scoring boost in the search index? The glossary mechanism can handle this, but the tag discovery query (`SNOWFLAKE.ACCOUNT_USAGE.TAG_REFERENCES`) would need to be added to Phase 1. Deferred until the tag convention is deployed.

3. **Column-level search tool.** Searching by column name or type (e.g., "find all tables with a VENDOR_ID column") is a natural extension of the index. Should this be a separate tool or a mode of `search_snowflake_schema`? Deferred to a follow-up.

4. **Cross-database FK inference.** The current FK algorithm only matches tables within the index. If the service role has access to tables in multiple databases, should FK inference consider cross-database matches? The current implementation matches by table name regardless of database, which may produce unexpected edges if two databases have tables with the same name.

5. **Per-user table visibility.** SHOW/DESCRIBE return the same metadata regardless of user identity. If per-user table visibility is needed in the future (e.g., some users should not see certain tables in search results), the system pool approach would need to change to per-user introspection or a post-filter based on grants. This is not needed currently since the service role defines the visibility boundary.