feat(cache): pipe cache invalidation via table/scope extraction from named queries #178

Overview

Named pipe ("pipes") cache entries are currently namespaced by pipe name (safePipeName) rather than by the underlying tables they query. Because Bento ingest invalidates cache entries by table name after successful ClickHouse inserts, pipe cache entries are not invalidated on writes and can remain stale until TTL expiry, defeating read-your-writes for pipe consumers.

Context

In the current implementation (internal/api/pipes.go), the cache namespace for a pipe execution is set to the pipe name:

// TODO: scope impl
scope := ""
safePipeName := query.SafeEncodeNATS(name)

However, the Bento ingest worker invalidates cache by table name (via InvalidateCache(ctx, table, scopes)). Since the pipe cache entries are keyed under the pipe name namespace rather than a table namespace, ingest-triggered invalidation has no effect on them.

Problem

Unlike structured queries (where the table is an explicit request parameter), named pipes contain arbitrary SQL that may reference one or more tables. There is currently no mechanism to:

1. Parse the tables referenced by a pipe's SQL at registration or execution time.
2. Map those table names to cache namespaces so ingest invalidation can evict stale pipe results.

Proposed Solution (to be designed in follow-up)

Some approaches to consider:

• Static analysis at pipe registration: parse the SQL in PUT /v1/admin/pipes/{name} to extract referenced table names and store them alongside the pipe definition. Use these stored table names as cache tags on Set and ensure InvalidateByTags / InvalidateCache covers them.
• Runtime extraction: use the same naive SQL regex already used for structured queries (FROM/JOIN extraction) at pipe Execute time to derive table names dynamically and pass them as cache tags.
• Explicit metadata on pipe definition: allow pipe authors to declare tables: ["clicks", "events"] in the pipe JSON, which are used as cache invalidation tags.

Acceptance Criteria

• Pipe cache entries are invalidated when a Bento ingest flush successfully inserts data into a table referenced by the pipe's SQL.
• Read-your-writes is upheld for pipe consumers after an ingest event.
• The solution handles pipes referencing multiple tables (multi-table invalidation).

References

• Introduced in PR feat: caching for local table/scope invalidation #177 (cache-invalidation): feat: caching for local table/scope invalidation #177
• Specific comment: feat: caching for local table/scope invalidation #177 (comment)
• Related issues: feat(ingest): implement per-table cache invalidation on Bento batch flush #85, perf(cache): query cache returns stale data after writes (read-your-writes) #73

/cc @EricAndrechek