# LogStats for Dedicated and Premium

## Current Design

O11y LogStats is a global Delta Lake table per logical region bucket:

```text
s3://o11y-prod-shared-{logic_region}/deltalake/global/log-stats/
```

Data files are partitioned as:

```text
date={yyyyMMdd}/org={tenant_id}/cluster={cluster_id}/part-....parquet
```

The LogStats schema from the O11y document is:

| Column | Type | Note |
| --- | --- | --- |
| hour | INT | Log hour, 0-23 |
| project | STRING | Project ID |
| level | STRING | Log level |
| file | STRING | Source file |
| line | INT | Source line |
| count | BIGINT | Count in the hour |
| msg_digest | STRING | Digest with parameters masked |
| sample | STRING | Raw representative sample |
| component | STRING | Component, for example `pd` |
| date | STRING | Partition, `yyyyMMdd` |
| org | STRING | Partition, tenant ID |
| cluster | STRING | Partition, cluster ID |

Clinic Data Proxy currently supports standard tables for slow query and TopSQL:

- `slow_query_logs`
- `topsql`, `topru`
- `topsql_tidb`, `topsql_tikv`, `topsql_plan_meta`, `topsql_sql_meta`, `topsql_topru`

The query handler infers query type from the standard table name, then chooses `slowQueryS3Url` or `topsqlS3Url` from the internal cluster-dataurls API. The service rewrites logical table names to cluster-specific physical table names such as `slow_query_logs_{clusterId}` and `topsql_{clusterId}`.

The existing URL resolver has two important bucket rules:

- Dedicated AWS uses the cluster region directly, for example `us-east-1`.
- Premium AWS reuses the existing nextgen bucket-region rule: `ap-southeast-2` and `ap-south-1` use `-ng`; other AWS premium regions use `-premium`.

We verified the current production data layout with `AWS_PROFILE=clinic`:

- Dedicated `10989049060142230334` exists in `o11y-prod-shared-us-east-1`, under tenant `1372813089209061633`.
- Premium `10378381880046679304` exists in `o11y-prod-shared-ap-southeast-2-ng`, under tenant `1372813089209274541`; both tenant cluster and parent resource-pool partitions exist.

## Risks

The largest risk is accidentally exposing or scanning the global LogStats table without tenant and cluster constraints. A plain external table at `deltalake/global/log-stats/` contains many tenants and clusters.

Other risks:

- LogStats must be read through a Delta-aware table. A normal Hive/Parquet external table, partition projection table, or table over the raw partition directories can include Delta data files that have already been removed by the transaction log and produce wrong counts.
- Reusing `slowQueryS3Url` or `topsqlS3Url` would register LogStats against the wrong root path.
- Clinic internal API `orgId` is not the LogStats `org` partition. LogStats `org` must use tenant ID, currently returned as `clinicTenantId`.
- Premium has both tenant-cluster and parent/resource-pool partitions. The default tenant-facing Data Proxy table must use the requested tenant cluster ID, not parent ID.
- Athena Delta tables must be registered at the Delta table root that contains `_delta_log`. There is no valid cluster-level Delta location unless O11y publishes per-cluster Delta roots.
- Athena Delta table creation does not use traditional Hive partition repair. Metadata/schema drift is a separate ownership issue and should be handled by recreating/updating the Glue table or by an O11y-owned crawler.
- App delivery keeps separate runtime config. Any table template change in this repo must be mirrored in app-delivery before deployment.

## Target Contract

Add a new standard logical table:

```text
log_stats
```

Supported initial scope:

- Dedicated AWS production clusters.
- Premium AWS staging/production clusters when LogStats data exists in the expected bucket.

Explicitly out of scope for the first implementation:

- Starter/Essential/BYOC.
- Non-AWS engines until their table managers and live paths are validated.
- Automatic fallback from tenant-cluster partition to parent/resource-pool partition.
- Reading LogStats through `slow_query_logs`, `topsql`, or their data sources.

Data source contract:

- Internal API exposes `logStatsS3Url`.
- `logStatsS3Url` points to the global LogStats root, not to a date/org/cluster leaf.
- The root must contain `_delta_log`.
- For AWS, root is:

```text
s3://o11y-prod-shared-{log_stats_bucket_region}{env_suffix}/deltalake/global/log-stats/
```

Cluster-scoping contract:

- Public SQL uses `clinic_data_proxy.log_stats`.
- Data Proxy rewrites it to a cluster-specific view/table name, `log_stats_{clusterId}`.
- `log_stats_{clusterId}` must only expose rows where:

```sql
org = '<clinicTenantId>' AND cluster = '<clusterId>'
```

Partition contract:

- `date` remains required for all LogStats queries.
- `org` and `cluster` are injected by the cluster-scoped view, so callers do not need to know tenant ID.
- The `date` format is `yyyyMMdd`, matching the O11y layout.
- There is no `MSCK REPAIR TABLE` or `ALTER TABLE ADD PARTITION` path for LogStats. Correctness comes from the Delta transaction log, not from Glue partition discovery.

Canonical source of truth:

- Cluster metadata, tenant ID, provider, region, deploy type, and parent ID come from Clinic internal cluster-dataurls.
- Bucket-region selection lives in `pkg/o11y/urls`, alongside existing slow query and TopSQL URL construction.
- Data Proxy table/schema support lives in `clinic_data_service`, but it must not duplicate product-tier bucket rules.

## Proposed Implementation

1. Extend internal URL response.
   - Add `LogStatsS3URL` to `pkg/types.ClusterURLs`.
   - Add `logStatsS3Url` to `pkg/api/internal_api.ClusterDataURLsResponse`.
   - Add the same field to `clinic_data_service/pkg/clinic-internal-api.ClusterDataURLs`.
   - In `ClusterURLsResolver.Resolve`, compute LogStats root for supported Dedicated/Premium AWS clusters.

2. Centralize LogStats bucket-region resolution.
   - Reuse the existing Premium AWS rule: `ap-southeast-2` and `ap-south-1` -> `-ng`, other Premium AWS -> `-premium`.
   - Dedicated AWS uses plain region.
   - Non-prod keeps the existing `-staging` env suffix behavior.
   - Return empty URL for unsupported tiers/providers in the first PR.

3. Add Data Proxy logical table support.
   - Add `QueryTypeLogStats`.
   - Add `log_stats` to `SupportedStandardTables`, `IsStandardTable`, `InferQueryType`, schema registry, and SQL rewrite table map.
   - Query/schema auto-fill chooses `logStatsS3Url` for `LOG_STATS`.
   - For LogStats, fetch cluster-dataurls even when a caller provides `options.dataSource`, because tenant ID is still required for cluster scoping.

4. Register Athena objects safely.
   - Create a region-scoped global Delta table, for example `log_stats_global`, at `logStatsS3Url`.
   - The Athena template must be Delta-only:

```sql
CREATE EXTERNAL TABLE `{{.Database}}`.`{{.Table}}`
LOCATION '{{.Location}}'
TBLPROPERTIES ('table_type' = 'DELTA')
```

   - Create `log_stats_{clusterId}` as a view over `log_stats_global` with fixed `org = clinicTenantId` and `cluster = clusterId`.
   - Do not expose `log_stats_global` as a supported public standard table.
   - Do not create `log_stats_{clusterId}` as a direct global-root table without filters.
   - Do not create a Hive/Parquet table, partition projection table, or direct table over `date/org/cluster` leaf paths for LogStats.

5. Extend table management.
   - Add a dedicated path for LogStats views because existing view dependency logic always suffixes dependencies with cluster ID.
   - Pass internal `clinicTenantId` and `clusterId` into the LogStats view template.
   - Keep standard TopSQL dependency handling unchanged.

6. Keep schema API consistent.
   - `GET /data-proxy/schema?tables=log_stats&clusterId=...` returns columns from the LogStats schema and partitions `date`, `org`, `cluster`.
   - The returned table remains `log_stats` logically, even if the underlying object is `log_stats_{clusterId}`.

## Invariants

- A read request never creates or rewrites any S3 data.
- Data Proxy never uses slow query or TopSQL data sources for LogStats.
- Data Proxy never reads LogStats through a non-Delta table.
- `log_stats_global` is internal only and cannot be queried as a standard table.
- Public `log_stats` always scopes to the request cluster.
- `org` means tenant ID, not Clinic internal org ID.
- Premium defaults to tenant cluster ID, not parent/resource-pool ID.
- No fallback to parent/resource-pool partition unless a future requirement explicitly asks for it.
- Date partition filtering is mandatory for LogStats queries.
- No repair-partition path exists for LogStats.

## Feasibility Gates

Before implementation, validate these points with Athena in the target workgroup:

- The workgroup uses Athena engine v3.
- A Delta table registered at `s3://.../deltalake/global/log-stats/` can query the verified Dedicated and Premium samples with `date`, `org`, and `cluster` predicates.
- Query planning time and scanned bytes are acceptable for the target UI/API use case.
- The existing O11y Glue table can only be reused if it has Delta metadata, for example `table_type=delta` and/or Delta provider parameters. A partitioned Parquet-style table is not acceptable even if it has the right columns.

If global Delta planning cost is too high, Clinic cannot safely solve that by pointing Athena at a cluster leaf path. The fallback must be an O11y-owned publication change, such as per-cluster Delta roots or another Delta-aware/materialized serving table.

## Verification Checklist

Local contract tests:

- `IsStandardTable("log_stats")` is true.
- `InferQueryType("log_stats")` returns `LOG_STATS`.
- Unsupported table error includes `log_stats`.
- Query rewrite maps `clinic_data_proxy.log_stats` to `log_stats_{clusterId}`.
- Schema registry returns LogStats fields and `date/org/cluster` partitions.
- Query handler chooses `logStatsS3Url`, not `slowQueryS3Url` or `topsqlS3Url`.
- Query handler still fetches cluster-dataurls for LogStats when `options.dataSource` is provided.
- LogStats table manager creates `log_stats_global` and `log_stats_{clusterId}` view with fixed tenant and cluster filters.
- LogStats table manager does not call `LoadPartitions` or create projection properties for LogStats.
- Dedicated and Premium URL resolver tests cover:
  - `us-east-1` Dedicated -> `o11y-prod-shared-us-east-1`.
  - `ap-southeast-2` Premium -> `o11y-prod-shared-ap-southeast-2-ng`.

Live AWS validation:

- Dedicated sample:

```text
s3://o11y-prod-shared-us-east-1/deltalake/global/log-stats/date=20260618/org=1372813089209061633/cluster=10989049060142230334/
```

- Premium sample:

```text
s3://o11y-prod-shared-ap-southeast-2-ng/deltalake/global/log-stats/date=20260618/org=1372813089209274541/cluster=10378381880046679304/
```

Data Proxy smoke SQL:

```sql
SELECT level, component, SUM(CAST(count AS BIGINT)) AS total
FROM clinic_data_proxy.log_stats
WHERE date = '20260618'
GROUP BY level, component
ORDER BY total DESC
LIMIT 20;
```

Deployment validation:

- Unit tests pass before deployment.
- Runtime config in app-delivery contains the new Athena template.
- After deploy, schema endpoint returns `log_stats`.
- After deploy, the smoke SQL works for the verified Dedicated and Premium clusters.
- Backend logs show Data Proxy using the LogStats URL and `LOG_STATS` query type.