# GCP TopSQL Month-Scoped Sources ## Background GCP TopSQL data is stored as independent Parquet/Delta directory trees per component and instance. The current BigQuery reconciliation discovers every historical instance that has any `date=` partition and creates one source URI per instance ending in `date=*`. On clusters with long instance histories this produces thousands of URIs, makes both DDL reconciliation and subsequent query planning slow, and can cross the synchronous gateway timeout. TopSQL queries already require a literal `date` partition predicate. The requested dates provide a bounded source-discovery scope that the GCP planner does not currently use. ## Goal - Derive a finite calendar-date scope from logical GCP TopSQL SQL. - Reconcile deterministic month-scoped Parquet external tables that include only instances with data in the requested month. - Support bounded cross-month predicates without mutating one shared view. - Keep the original user predicate in the final query. - Reject unbounded or excessively broad logical GCP TopSQL queries before GCS discovery. ## Current behavior - `topsql` is rewritten to `topsql_`. - The logical view depends on four cluster-global physical objects. - TiDB and TiKV discovery lists all instance directories and retains an instance when it has any historical `date=` directory. - Each retained instance contributes `instance=/date=*` to BigQuery. - Concurrent queries for different dates share the same global objects. ## Target contract For logical `topsql` queries routed to GCP: - Accept literal `date` predicates using `=`, `IN`, `BETWEEN`, `>=`, `>`, `<=`, and `<`. - Combine comparison bounds as a half-open interval. For example, `date >= '2026-07-30' AND date <= '2026-08-02'` selects July and August. - Limit one query to at most 31 calendar days. Reject a missing finite bound, invalid/reversed date range, dynamic expression, or wider range with a clear validation error. - Rewrite the logical table to a deterministic scoped view such as `topsql__m202607_202608`. - Reconcile four physical tables per selected month, such as `topsql_tidb__m202607`. - Metric external tables include only instances having a partition in that month, and use exactly one wildcard per URI: `instance=/date=2026-07-*`. - Metadata external tables use `date=2026-07-*`. - A cross-month scoped view explicitly `UNION ALL`s the selected monthly sources. The original date predicate remains in the rewritten SQL and is the exact row-level filter. - Object names are immutable for a cluster/month scope, so concurrent queries for different ranges cannot change each other's source set. - Cache each successful month-scoped source discovery in the Data Service process for 30 minutes. Requests in that interval reuse the reconciled external objects instead of listing every historical instance again. Direct physical TopSQL table references retain their existing global behavior in this change. The new mandatory date-range planning applies to the logical `topsql` API contract only. ## Affected modules and routes - `clinic_data_service/pkg/dialect`: literal date-range extraction from the parsed SQL AST. - `clinic_data_service/pkg/service`: GCP logical TopSQL planning and physical name rewrite before table management. - `clinic_data_service/pkg/engine`: scoped-name parsing, month-specific GCS discovery, external-table reconciliation, and multi-month view DDL. - Existing Data Proxy SQL query routes that call `DataProxyService.Query`. No executable ownership, image name, workflow input, or app-delivery mapping changes. ## Implementation steps 1. Add a conservative AST date-scope extractor with a 31-day span limit. 2. Detect logical TopSQL requests after datasource routing and, for GCP, add the requested month suffix to the logical table rewrite. 3. Parse scoped logical and physical names in the BigQuery table manager. 4. Reconcile each selected month's four dependencies concurrently. 5. Restrict GCS date discovery and generated source URIs to one calendar month. 6. Cache successful month discovery for 30 minutes and refresh after expiry. 7. Generate a scoped logical view that unions the selected month branches. 8. Add focused parser, service rewrite/name, discovery, manager, and DDL tests. ## Exclusions - No asynchronous API/job contract change. - No background prewarming scheduler. - No distributed cache for GCS instance discovery. - No automatic deletion of existing legacy cluster-global TopSQL objects. - No AWS, Azure, or AliCloud TopSQL behavior change. - No app-delivery manifest change and no deployment in this task without separate authorization. Month-scoped objects are created only for queried months. A separate lifecycle task may add BigQuery object expiry or periodic cleanup after the query contract has been validated in staging. ## Compatibility concerns - Existing valid GCP logical TopSQL equality/IN queries remain valid and now resolve to scoped objects. - Callers relying on an unbounded logical TopSQL query receive a validation error instead of triggering a global scan. - `skipSQLRewrite=true` cannot safely provide scoped logical planning and is rejected for logical GCP TopSQL requests. - The scoped suffix is internal and does not change user-facing logical table names. ## Test plan - Unit-test all supported operators, cross-month ranges, invalid dates, unbounded/reversed ranges, dynamic expressions, and the 31-day limit. - Unit-test scoped object parsing and physical name generation. - Unit-test that discovery uses month-specific prefixes and URIs and excludes instances without data in that month. - Unit-test single-month and cross-month view DDL. - Run focused tests for `pkg/dialect`, `pkg/service`, and `pkg/engine`, then run the broader `go test ./clinic_data_service/pkg/...` if dependencies permit. ## Staging verification After explicit release authorization: 1. Run Clinic CD from the feature branch for `staging` with `components=clinic-data-service`. 2. Wait for the app-delivery staging PR, GitOps rollout, and Ready workload. 3. Query a recent single-day range and a range crossing a month boundary using canonical fields (`timestamps`, `topsql_cpu_time_ms`). 4. Confirm the generated physical URI counts are bounded to the requested month and that both requests complete inside the gateway timeout. 5. Confirm invalid/unbounded and greater-than-31-day queries return validation errors without GCS discovery. ## Deployment impact ### Affected source modules - Go module: `clinic_data_service` - Packages: `pkg/dialect`, `pkg/service`, `pkg/engine` - Executable: `clinic_data_service/cmd/api/main.go` - Image: `clinic/data-service` ### Required deployment modules - Clinic CD component: `clinic-data-service` - app-delivery application: `clinic-data-service` - Kubernetes workload: Deployment `clinic-data-service` - Target environments after approval: staging first; `prod-cloud` only after staging validation, merge, and explicit production authorization. Nearby `clinic`, `clinic-control-plane`, `diag-client`, `diag-collector`, and `clinic-ui` components do not require deployment for this runtime change.