# SQL Catalog

SQL Catalog is a post-detection demo for receiving discovered SQL anomalies, maintaining digest
dossiers, running bounded Treatments, and reporting progress. Clinic, AI agents, integrations, and
human reporters own discovery and severity decisions; SQL Catalog owns intake and the governance
loop after discovery.

The implementation keeps the backend small and uses the same frontend stack as the referenced slow-log tools:

- Python API server with PyMySQL.
- TiDB persistence for development and deployment.
- React + Vite + TypeScript frontend.
- Tailwind v4 + shadcn-style components.
- React Query for API data.
- ECharts for all analytical charts, with shared theme and responsive sizing behavior.
- lucide-react icons.
- Shared English and Simplified Chinese catalogs with React and request-scoped backend i18n.

## Run

Create the backend environment and install its dependencies:

```bash
python3 -m venv .venv
.venv/bin/pip install -r backend/requirements.txt
```

Install frontend dependencies:

```bash
cd frontend
pnpm install
```

Run the API server from the repository root:

```bash
.venv/bin/python -m backend.sql_catalog.server
```

Run the React dev server from `frontend/`:

```bash
cd frontend
pnpm exec vite --host 127.0.0.1 --port 5173
```

Development URL:

```text
http://localhost:5173
```

API URL:

```text
http://127.0.0.1:8765
```

Build production assets:

```bash
cd frontend
pnpm build
```

After build, the Python server also serves the React app from `frontend/dist` at:

```text
http://127.0.0.1:8765
```

Build the production container from the repository root. Auth0 values exposed
to Vite are public SPA configuration, never secrets:

```bash
docker build --platform linux/amd64 \
  --build-arg VITE_AUTH0_DOMAIN=tenant.auth0.com \
  --build-arg VITE_AUTH0_CLIENT_ID=replace-with-the-spa-client-id \
  -t sql-catalog:local .
```

Kubernetes uses `/api/v1/healthz/live` for liveness and
`/api/v1/healthz/ready` for readiness. Apply migrations without starting the
HTTP server with `python -m backend.sql_catalog.server --migrate-only`.

Runtime environment variables can be placed in `.env`:

```dotenv
SQL_CATALOG_DATABASE_URL=mysql://user@gateway.example.com:4000/sql_catalog?ssl=true
SQL_CATALOG_DATABASE_PASSWORD_ENV=DB_PASSWD
DB_PASSWD=replace-with-the-database-password
```

The application initializes an empty TiDB database to the current schema at startup. It refuses to
run without `SQL_CATALOG_DATABASE_URL`; SQLite is retained only as the isolated unit-test backend and
as a source for the one-time migration command. `.env` and `.env.*` are ignored by git. Keep database
passwords and Clinic API keys there. Initial connections retry transient gateway errors so a sleeping
TiDB Starter cluster can wake without making the application fail on its first attempt.

### Auth0

The web app is an Auth0 **Single Page Application** and uses Authorization Code with PKCE. Configure
the Auth0 application with:

- Application Type: `Single Page Application`
- Type of Users: `Individuals` (Organization Usage: `Deny`)
- Allowed Callback URLs: `http://localhost:5173/`, `https://sql-catalog.pingcap.net/`
- Allowed Logout URLs: `http://localhost:5173/`, `https://sql-catalog.pingcap.net/`
- Allowed Web Origins: `http://localhost:5173`, `https://sql-catalog.pingcap.net`
- Enabled connection: `google-oauth2`

The SPA derives its callback and logout URL from the current browser origin, so
the same immutable image works on localhost and production without embedding an
environment-specific redirect URL.

No separate Auth0 API or audience is required. The SPA obtains the standard Auth0 access token and
the backend validates it through Auth0's `/userinfo` endpoint, then independently requires a
verified Google identity and an email in `AUTH0_ALLOWED_EMAIL_DOMAINS`. Restricting the login
connection alone is not an organization-domain security boundary.

The authorize request explicitly uses `google-oauth2`, so no database login or self-registration UI
is exposed. The backend also rejects any identity whose resulting `sub` is not from that connection.
SQL Catalog does not use Auth0 Organizations as its tenant model. If Type of Users is left as
`Business Users`, Auth0 rejects a request that combines the Organization prompt with an explicit
connection; use `Individuals` for this application.

The SPA never reads the Auth0 Client Secret. Browser requests obtain an API access token and send it
as `Authorization: Bearer <token>`. `GET /api/v1/healthz` stays public; all other `/api/v1/*` routes
require a valid token. Future service integrations should use an Auth0 Machine-to-Machine client
instead of sharing a user token.

To migrate one existing Cluster Scope from the former SQLite development database, run:

```bash
.venv/bin/python scripts/migrate_sqlite_scope_to_tidb.py \
  --scope-name "Example SQL Clusters" \
  --dry-run

.venv/bin/python scripts/migrate_sqlite_scope_to_tidb.py \
  --scope-name "Example SQL Clusters"
```

The migration follows the Scope's dependency closure, upserts in dependency order, and verifies all
selected primary keys and Finding references after commit. It is safe to rerun after an interruption.

Treatment details can copy their SQL analysis context and open Investibook. The hosted entry defaults
to `https://investibook.tidbcloud.com`; set `VITE_INVESTIBOOK_URL` to use another deployment.

Clinic metadata uses the canonical Data Proxy API. Set `CLINIC_API_KEY`; requests use
`Authorization: Bearer <key>` against `${CLINIC_ENDPOINT}/data-proxy/api/v1`. `CLINIC_ENV` selects
`dev`, `staging`, or `prod`, and `CLINIC_ENDPOINT` can override the selected host. Known Clusters are
resolved through `GET /clusters/{clusterID}`. Organization cluster lists use the DBAAS Org ID at
`GET /orgs/{orgID}/clusters`; the application retains any mapped Clinic Portal Org ID separately so
existing Finding ownership and Health Check links remain stable.

## Sync Clinic Health Check Reports

Open a Cluster's SQL configuration page to browse its Clinic Health Check report history, select
completed reports, and sync their SQL findings. Cluster Scope creation also offers **Save and sync
reports** as an optional next step; synchronization is still performed independently for each
member Cluster and does not create Group-owned Findings or configuration inheritance.

The backend reads Clinic report manifests and selected item details, creates one Finding Run per
report, and submits one idempotent Finding per `reportId + SQL Digest`. It replays each Finding at
the report's Clinic creation time. The full API and mapping contract is documented in
[Clinic Health Check Report Sync API](docs/api/clinic-health-check-sync.md).

## Import Clinic Health Check Extracts

Customer-derived Health Check extracts contain SQL text, metrics, cluster metadata, and evidence
links. They belong under the ignored local `data/` directory and must never be committed. Automated
tests use deterministic sanitized fixtures instead of production extracts.

The API key is read from `CLINIC_API_KEY` and is never written to the extract. For a newly added
Cluster, `--latest-days 14` selects the latest 14 distinct observation dates and keeps one report per
date, preferring `success` over `partial` and then the newest rerun:

```bash
python3 scripts/fetch_clinic_health_check_reports.py \
  --org-id <clinic-portal-org-id> \
  --cluster-id <cluster-id> \
  --latest-days 14 \
  --dbaas-org-id <dbaas-org-id> \
  --org-name <org-name> \
  --cluster-name <cluster-name> \
  --project-id <project-id> \
  --provider <provider> \
  --region <region> \
  --output data/clinic-health-check/<org-id>/<cluster-id>/reports.json
```

The fetcher retains SQL check summaries, metrics, and evidence links while excluding temporary
signed URLs and complete raw slow-log records. Validate and import an extract with:

```bash
python3 scripts/import_clinic_health_check_extract.py \
  data/clinic-health-check/<org-id>/<cluster-id>/reports.json \
  --dry-run

python3 scripts/import_clinic_health_check_extract.py \
  data/clinic-health-check/<org-id>/<cluster-id>/reports.json
```

The importer records one Finding Run per report and submits one idempotent Finding per
`report_id + sql_digest`. Multiple checks and ranking dimensions for the same digest in one report
become Symptoms, metrics, and evidence on that Finding. Each report is replayed at its explicit
Clinic creation time so Catalog registration, updates, relations, and Timeline events retain the
original inspection sequence.

Treatment lifecycle, fields, outcomes, evidence requirements, and API examples are documented in
[Treatment Management API](docs/api/treatment-management.md).

## Register a Catalog Item Directly

Finding intake remains the normal automatic registration path. A customer ticket, a high-frequency
SQL, or a proactive watch can also create a dossier before any Finding exists. Open `/catalog/new`
for the human workflow. It searches Clinic for one Cluster and derives the Organization.

The full request contract and lifecycle behavior are documented in
[Catalog Registration API](docs/api/catalog-registration.md). Direct registration creates no Finding,
Symptom, or synthetic observation; a later Finding for the same Organization, Cluster, and digest
reuses the dossier.

Catalog Item 不保存可写的混合业务状态。当前治理态、风险信号和下一步动作由 Finding、
Treatment、Record 与 Timeline 实时投影，规则见
[Catalog Item Current Posture](design/discuss_2/catalog_item_current_posture_and_usability.md)。
已有结论如何覆盖重复证据、何时因显著变化重新研判，以及对应 API 见
[Catalog Governance Decisions](docs/api/catalog-governance-decisions.md)。

## Create a Finding

The complete external contract, field requirements, validation rules, idempotency behavior, and
response examples are documented in [Finding Intake API](docs/api/finding-intake.md).

Language selection, terminology, translation boundaries, and the localized API error contract are
documented in [Internationalization](docs/i18n.md).

For human entry, open `/findings/new`. The page searches Clinic for one Cluster, derives its
Organization, and only asks for SQL identity, registered Symptoms, human severity/assessment,
observed time, and optional evidence. The JSON contract below is for external integrations.

```bash
curl -sS http://127.0.0.1:8765/api/v1/findings \
  -H "authorization: Bearer $SQL_CATALOG_ACCESS_TOKEN" \
  -H 'content-type: application/json' \
  -d '{
    "source": "manual",
    "source_type": "manual",
    "source_event_id": "manual:<unique-event-id>",
    "org_id": "<org-id>",
    "cluster_id": "<cluster-id>",
    "sql_digest": "<sql-digest>",
    "observed_at": "2026-07-10T08:00:00Z",
    "detector_code": "manual.user_report",
    "symptoms": [{
      "code": "large_key_scan",
      "severity": "high",
      "assessment": {
        "method": "human",
        "summary": "The observed scan volume is causing customer-visible latency."
      }
    }],
    "metrics": {}
  }'
```

The API will:

1. Idempotently create the Finding by `source + source_event_id`.
2. Create a Catalog Item if this digest has not been seen.
3. Link the Finding to the Catalog Item.
4. Write timeline events.

## MVP API Surface

```text
GET   /api/v1/healthz
GET   /api/v1/workbench
GET   /api/v1/workbench/cluster-health
POST  /api/v1/findings
GET   /api/v1/findings
GET   /api/v1/findings/stats
GET   /api/v1/findings/{id}
GET   /api/v1/symptom-definitions
POST  /api/v1/finding-batches
POST  /api/v1/finding-deletions/preview
POST  /api/v1/finding-deletions
POST  /api/v1/finding-restorations
POST  /api/v1/finding-runs
GET   /api/v1/finding-runs
GET   /api/v1/cluster-metadata
GET   /api/v1/catalog-items
POST  /api/v1/catalog-items
GET   /api/v1/catalog-items/stats
GET   /api/v1/catalog-items/{id}
PATCH /api/v1/catalog-items/{id}
POST  /api/v1/catalog-items/{id}/triage
POST  /api/v1/catalog-items/{id}/treatments
POST  /api/v1/catalog-items/{id}/records
GET   /api/v1/treatments
GET   /api/v1/treatments/stats
POST  /api/v1/treatments
GET   /api/v1/treatments/{id}
PATCH /api/v1/treatments/{id}
POST  /api/v1/treatments/{id}/transition
POST  /api/v1/treatments/{id}/records
POST  /api/v1/treatments/{id}/observations/start
POST  /api/v1/treatments/{id}/complete
POST  /api/v1/treatments/{id}/reopen
POST  /api/v1/records
GET   /api/v1/records/{id}
PATCH /api/v1/records/{id}
```

Workflow state, stage, outcome, observation, completion, and reopen changes use the domain action
endpoints above. Generic `PATCH` endpoints cannot bypass those invariants.

## Validation

```bash
python3 -m py_compile backend/sql_catalog/server.py
python3 scripts/check_i18n.py
python3 -m unittest discover -s backend/tests -v
cd frontend && pnpm build
```

After a frontend build, the Python server serves the SPA and supports direct navigation to detail
routes such as `/catalog/{id}`.