# MySQL Client for Kubernetes Controller Runtime

This package implements a `client.Client` interface from `sigs.k8s.io/controller-runtime/pkg/client` that uses MySQL as the storage backend instead of etcd/Kubernetes API server.

## Features

### Core Operations

- **Create**: Creates a new cluster with initial resource version 1
- **Get**: Retrieves a single cluster by namespace/name
- **List**: Lists clusters with support for namespace and label selector filtering
- **Update**: Updates a cluster with optimistic locking via resource version
- **Delete**: Soft-deletes a cluster by setting deletion timestamp
- **Patch**: Supports merge patch operations

### Resource Version & Conflict Handling

- Resource version is stored as `uint64` in the `cluster_specs` table
- Incremented on every update/delete operation
- Update operations check the current resource version to detect conflicts
- Returns error if resource version mismatch is detected

### Watch Mechanism via Events

- Every Create/Update/Delete operation generates a `ClusterEvent` record
- Events include:
  - Event type: `CLUSTER_CREATED`, `CLUSTER_UPDATED`, `CLUSTER_DELETED`
  - Resource version at the time of the event
  - Source component that triggered the event
  - Status: `PENDING`, `PROCESSING`, `DONE`
  - TTL expiration for cleanup

### Soft Deletion

- Clusters are not physically deleted from the database
- Instead, `deletion_timestamp` is set on the cluster record
- All queries filter out soft-deleted records with `WHERE deletion_timestamp IS NULL`

### Label Selector Support

- Labels are stored as JSON in the `cluster_specs.labels` field
- Label selectors are converted to MySQL JSON queries using `JSON_EXTRACT`
- Supported operators:
  - `In`: Match labels with values in a set
  - `NotIn`: Match labels with values not in a set
  - `Exists`: Match if label key exists
  - `DoesNotExist`: Match if label key doesn't exist

## Database Schema

### Tables

#### `clusters`

Primary cluster information:

- `namespace`: Kubernetes namespace
- `cluster_id`: Cluster name (primary key)
- `region`: Cloud region
- `kubernetes`: Kubernetes cluster identifier
- `tenant`: Tenant ID
- `project_id`: Project ID
- `biz_type`: Business type
- `creation_timestamp`: Auto-set on creation
- `update_timestamp`: Auto-updated on modification
- `deletion_timestamp`: Set on soft delete

#### `cluster_specs`

Cluster specification (labels, annotations, config):

- `cluster_id`: Foreign key to clusters
- `labels`: JSON map of labels
- `annotations`: JSON map of annotations
- `resource_version`: Optimistic locking version
- `endpoint_service_name`: Endpoint service name
- `endpoint_service_user_arn`: AWS ARN for endpoint
- `endpoint_service_shared`: Whether endpoint is shared
- `mute_alert`: Alert muting flag
- `vm_account_id`: VictoriaMetrics account ID
- `vm_project_id`: VictoriaMetrics project ID
- `vm_support_serverless`: Serverless support flag
- `vm_alert_max_queue_size`: Alert queue size
- `vm_cluster_name`: VM cluster name
- `vm_availability_zone`: Availability zone
- `vm_rule_selector`: JSON label selector for rules
- `vm_alert_resources`: JSON resource requirements
- `vm_alert_managers`: JSON array of alertmanager endpoints

#### `cluster_statuses`

Cluster status information:

- `cluster_id`: Foreign key to clusters
- `phase`: Current phase (e.g., ACTIVE, PENDING)
- `dns_name`: DNS name for the cluster

#### `cluster_events`

Event log for watch functionality:

- `event_id`: Auto-increment primary key
- `namespace`: Cluster namespace
- `name`: Cluster name
- `kubernetes`: Kubernetes cluster identifier
- `event_type`: CLUSTER_CREATED, CLUSTER_UPDATED, CLUSTER_DELETED
- `status`: PENDING, PROCESSING, DONE
- `source_component`: Component that generated the event
- `resource_version`: Resource version at event time
- `ttl_expiration`: When the event should be cleaned up
- `created_at`: Event creation time
- `updated_at`: Event update time

## Usage Example

```go
import (
    "context"
    "github.com/tidbcloud/observability/pkg/storagesdk/client/mysql"
    "github.com/tidbcloud/pkgv2/gormutil"
    v1 "github.com/tidbcloud/observability/api/regionalserver/resourceworker/v1"
    "sigs.k8s.io/controller-runtime/pkg/client"
)

// Create MySQL client
cfg := &gormutil.Config{
    Host:     "localhost",
    Port:     3306,
    User:     "root",
    Password: "password",
    Database: "observability",
}

mysqlClient, err := mysql.NewMySQLClient(
    cfg,
    "my-component",           // component name
    map[string]string{},      // index field mappings
    10 * time.Minute,         // event TTL
)
if err != nil {
    panic(err)
}

ctx := context.Background()

// Create a cluster
cluster := &v1.Cluster{
    ObjectMeta: metav1.ObjectMeta{
        Name:      "test-cluster",
        Namespace: "default",
        Labels: map[string]string{
            "observability.pingcap.com/region": "us-west-2",
            "observability.pingcap.com/tenantID": "tenant-1",
        },
    },
    Spec: v1.ClusterSpec{
        Endpoint: v1.Endpoint{
            Name:   "test-endpoint",
            Shared: true,
        },
        VictoriaAccount: v1.VictoriaAccount{
            AccountID:     1,
            VMClusterName: "vmcluster",
        },
    },
}

err = mysqlClient.Create(ctx, cluster)
if err != nil {
    panic(err)
}

// Get the cluster
key := client.ObjectKey{Name: "test-cluster", Namespace: "default"}
retrievedCluster := &v1.Cluster{}
err = mysqlClient.Get(ctx, key, retrievedCluster)
if err != nil {
    panic(err)
}

// Update the cluster
retrievedCluster.Spec.MuteAlert = true
err = mysqlClient.Update(ctx, retrievedCluster)
if err != nil {
    panic(err)
}

// List clusters with label selector
clusterList := &v1.ClusterList{}
err = mysqlClient.List(ctx, clusterList,
    client.InNamespace("default"),
    client.MatchingLabels{"observability.pingcap.com/region": "us-west-2"},
)
if err != nil {
    panic(err)
}

// Delete the cluster
err = mysqlClient.Delete(ctx, retrievedCluster)
if err != nil {
    panic(err)
}
```

## Conversion Details

### K8s Object → MySQL Records

**Cluster Table:**

- `namespace` ← `ObjectMeta.Namespace`
- `cluster_id` ← `ObjectMeta.Name`
- `region` ← `Labels["observability.pingcap.com/region"]`
- `tenant` ← `Labels["observability.pingcap.com/tenantID"]`
- `project_id` ← `Labels["observability.pingcap.com/projectID"]`
- `biz_type` ← `Labels["observability.pingcap.com/bizType"]`

**ClusterSpec Table:**

- `labels` ← `ObjectMeta.Labels` (JSON)
- `annotations` ← `ObjectMeta.Annotations` (JSON)
- `resource_version` ← `ObjectMeta.ResourceVersion` (parsed as uint64)
- `endpoint_service_name` ← `Spec.Endpoint.Name`
- `endpoint_service_user_arn` ← `Spec.Endpoint.UserARN`
- `endpoint_service_shared` ← `Spec.Endpoint.Shared`
- `vm_account_id` ← `Spec.VictoriaAccount.AccountID`
- `vm_cluster_name` ← `Spec.VictoriaAccount.VMClusterName`
- etc.

**ClusterStatus Table:**

- `phase` ← `Status.Phase`
- `dns_name` ← `Status.DnsName`

### MySQL Records → K8s Object

The reverse conversion reconstructs the full Kubernetes Cluster object from the three tables (clusters, cluster_specs, cluster_statuses) using GORM preloading.

## Transaction Safety

All write operations use database transactions to ensure atomicity:

1. Begin transaction
2. Perform operation (insert/update/delete)
3. Create cluster event
4. Commit transaction

If any step fails, the entire transaction is rolled back.

## Not Implemented

The following methods are intentionally not implemented as they're not needed for the current use case:

- `DeleteAllOf`: Bulk deletion
- `Scheme`: Runtime scheme
- `RESTMapper`: REST mapping
- `SubResource.Get`: Sub-resource retrieval

These methods will panic if called.

## Testing

Refer to the reference implementation at:
`/Users/just/Workspace/pingcap/cloud/tidb-management-service/api/serverless_infra/client/mysql`

## Metrics

The MySQL client automatically exports Prometheus metrics to monitor operation performance:

### `observability_mysql_client_operation_duration_seconds`

Histogram metric tracking the duration of each MySQL client operation.

**Labels:**
- `operation`: The type of operation (`get`, `list`, `create`, `update`, `delete`, `patch`, `status_update`)
- `status`: Operation result (`success` or `error`)

**Example queries:**

```promql
# Average operation duration by operation type
rate(observability_mysql_client_operation_duration_seconds_sum[5m])
/
rate(observability_mysql_client_operation_duration_seconds_count[5m])

# 95th percentile latency for Get operations
histogram_quantile(0.95,
  rate(observability_mysql_client_operation_duration_seconds_bucket{operation="get"}[5m])
)

# Error rate by operation
rate(observability_mysql_client_operation_duration_seconds_count{status="error"}[5m])
```

## Future Enhancements

1. **Field Selector**: Add support for field selectors in List operations
2. **Pagination Improvements**: Enhance pagination with cursor-based navigation