# Athena TopSQL Instance Projection Self-Healing

## Background and Goal

TopSQL queries can fail with Athena `INVALID_TABLE_PROPERTY` when a managed
Glue table has an empty `projection.instance.values`. The affected tables use
enum partition projection for their `instance` partition. Retrying the same
query cannot recover the table because Athena classifies the failure as
non-retryable.

The goal is to prevent new invalid tables and make existing TopSQL and Top RU
tables repair themselves from their authoritative S3 instance prefixes before
retrying the failed query once.

## Target Contract

- Match only Athena failures that identify the
  `projection.instance.values` invalid-table property.
- Repair each affected physical table independently from its own S3 component
  path and update only its Glue table parameters.
- Use `__clinic_no_instance__` when S3 listing succeeds but contains no
  instances, so an empty dataset produces an empty result instead of a 500.
- Treat an S3 listing failure as an error and never overwrite Glue metadata.
- Never retry an Athena query more than once and never repair unrelated Athena
  failures.
- Periodically reconcile existing Glue enum values with S3 every 600 seconds in
  staging and prod-cloud.

## Implementation

- Add structured Athena error detection and an instance-projection repair path
  to Clinic Data Service.
- Expand managed TopSQL and Top RU views to their physical dependencies and
  collapse concurrent repairs per region, database, and table.
- Reject table creation when instance discovery fails; use the reserved empty
  marker when discovery succeeds with no instances.
- Reject the known misplaced AWS configuration key with an actionable error.
- Move/add `instance_projection_refresh_seconds: 600` under
  `aws.table_manager` in the app-delivery staging and prod-cloud overlays.

## Exclusions and Compatibility

- Do not delete or recreate Glue tables and do not change S3 data.
- Do not change public API request or response schemas.
- Do not change non-AWS query engines, Kong, internal-api, or prod-op.
- Existing valid enum lists remain unchanged unless the S3 instance set has
  changed.

## Validation and Release Boundary

- Run focused config, engine, and service unit tests from the nested
  `clinic_data_service` module and run `git diff --check` in both repositories.
- Render the staging and prod-cloud clinic-data-service overlays and verify the
  mounted ConfigMap and workload remain correctly connected.
- Deploy only the Clinic CD `clinic-data-service` component to staging first,
  then verify first-request repair, second-request idempotency, Pod health, and
  redacted repair logs.
- Do not merge either PR or release prod-cloud without separate authorization.

## Deployment Impact

- Affected source module: `clinic_data_service`; entrypoint `cmd/api`; image
  `clinic/data-service`.
- Required deployment module: Clinic CD component `clinic-data-service`;
  app-delivery application and Kubernetes Deployment `clinic-data-service` in
  staging and prod-cloud.
- No deployment is required for Clinic API, internal-api, task-manager, Kong,
  NGM, or cloud-server.