feat(query): implement time-bucketed caching ("tail chopping") and NATS-driven table invalidation #86

Problem

This issue addresses the implementation of the caching strategy decided in #73 (perf(cache): query cache returns stale data after writes).

To solve the "read-your-writes" problem without building a complex sequence-tracking system, we agreed on an "acceptable delay" paradigm where writes are buffered in Bento for $X$ seconds. Once Bento flushes to ClickHouse, users must see their data immediately.

To achieve this, we need a blunt-force, per-table cache invalidation triggered by Bento. However, if we only implement table invalidation, heavily written tables will constantly wipe their caches, driving our cache hit rate to 0% and crushing ClickHouse under the weight of recalculating full historical aggregates for sliding-window queries (e.g., now() - 30m).

To protect ClickHouse from this "cache thrash," we are implementing a Time-Bucketed Caching read path (aka "Tail Chopping") for structured queries.

Proposed Solution

Part 1: The Invalidation Trigger (Bento $\rightarrow$ NATS)
We will use NATS as a Pub/Sub middleman to ensure all API nodes clear stale data the exact millisecond it lands in ClickHouse.

1. Bento Publish: Upon a successful HTTP 200 flush to ClickHouse, Bento publishes an empty message to a NATS subject: cache.invalidate.<table_name>.
2. API Subscribe: All API instances run a background watcher subscribed to cache.invalidate.*.
3. Table-Level Eviction: When an API node receives the signal, it drops all cached data (including historical buckets and query results) associated with that specific table.

Part 2: Time-Bucketed Caching & "Tail Chopping" (API $\rightarrow$ ClickHouse)
Instead of caching the entire response of a sliding window query, the API will decompose the time range into discrete, immutable "Closed Buckets" (e.g., 5-minute chunks) and volatile "Tails" (the fractions of time at the beginning and end of the query).

When a structured query requests data from 10:02 to 10:32:

1. Calculate Buckets (Go): The API splits the request into:
   • Start fraction (Volatile past): 10:02 to 10:05
   • Closed Buckets (Immutable): 10:05 to 10:30 (in 5-min increments)
   • End fraction (Volatile present): 10:30 to 10:32
2. Cache Check: The API checks the cache for the Closed Buckets. Let's assume it finds 10:05-10:15, but 10:15-10:30 were wiped by a recent Bento flush.
3. Targeted CH Query: The API dynamically constructs a ClickHouse query to fetch only what is missing: the missing closed buckets (10:15-10:30) and the volatile fractions.
4. Stitch & Re-cache: * The Go API caches the newly fetched Closed Buckets (10:15-10:30) so subsequent queries don't need to ask ClickHouse for them.
   • It sums/stitches the cached buckets, the new buckets, and the fractions together, returning the final result to the user.

Note: Raw SQL ad-hoc queries (/v1/query) bypass this logic entirely and query ClickHouse directly.

Implementation Checklist
Phase 1: Invalidation Pipeline

• Bento: Update internal/ingest/clickhouse.go. After a successful batch write, extract the table name and publish to NATS (cache.invalidate.<table_name>).
• Cache Engine: Ensure the caching layer supports grouping or tagging entries by table name so we can execute a targeted eviction for a specific table.
• API Server: Create a background watcher that subscribes to the NATS invalidation subjects and triggers the table eviction logic.

Phase 2: Tail Chopping Read Path

• Query Builder: Update the structured query handler to identify time boundaries (start / end) and align them to a configurable bucket grid (e.g., 5 minutes).
• Cache Lookup: Implement the Go logic to iterate through the required closed buckets and attempt to fetch them from the cache.
• Query Construction: Modify the ClickHouse query generation to specifically request the missing intervals (WHERE (time >= start_fraction_start AND time < start_fraction_end) OR (time >= missing_bucket_start...)). Use ClickHouse's toStartOfFiveMinutes() or equivalent.
• Stitching Engine: Write the aggregation logic in Go to combine the cached bucket results with the newly returned ClickHouse bucket/fraction results.
• Re-caching: Ensure newly fetched closed buckets are injected back into the cache and correctly associated with the queried table.

Additional Context

Any mockups, examples, or references that help explain the request.