# Optimize Organization Sync

## Background

`MigrateOrganizationJob` runs every two minutes. Each run lists every cloud
organization and calls `InsertOrUpdateCloudOrg` once per organization. That
function starts a database transaction and reads the organization before it
decides whether anything changed, so an unchanged organization still causes a
lookup and a transaction commit. Production performance evidence showed these
repeated organization lookups and commits contributing sustained RDS load.

## Goal

Keep the existing two-minute synchronization freshness while making the common
no-change run cheap:

- read the local sync state for the returned cloud organizations in bounded
  queries;
- skip organizations whose `updated_at` value has not changed;
- keep the existing transactional write path for new or changed organizations;
- prevent a new scheduled organization sync from starting while the previous
  scheduled run is still executing.

## Current Behavior

- `MigrateOrganizationJob` calls `RealTimeListOrganizations` and then calls
  `InsertOrUpdateCloudOrg` for every result.
- `InsertOrUpdateCloudOrg` opens a transaction before checking the existing
  row, including when the row is unchanged.
- The cron chain recovers panics but does not skip overlapping executions.
- The manual Ops synchronization uses `UpdatePlan=true` to force a complete
  refresh and must retain that behavior.

## Target Contract

- A scheduled `MigrateOrganizationJob` (`UpdatePlan=false`) loads the existing
  `dbaas_org_id` and `updated_at` values for the cloud results in bounded
  batches.
- An existing organization with an equal `updated_at` is skipped without
  opening a write transaction.
- A missing organization or one with a different `updated_at` continues through
  the existing `InsertOrUpdateCloudOrg` function.
- A manual `UpdatePlan=true` run keeps its current per-organization force-update
  behavior and is not filtered by the scheduled-job optimization.
- Only `MigrateOrganizationJob` receives a non-overlap cron wrapper. Other cron
  jobs retain their current scheduling behavior.
- Each run emits one bounded summary log with fetched, skipped, processed, and
  failed counts plus duration. It does not log organization payloads.

## Affected Modules and Runtime Path

- Go module: repository root (`github.com/pingcap-inc/clinic`).
- Packages: `pkg/resource_limit` and `pkg/db/model`.
- Consumer: `MigrateOrganizationJob` started from `cmd/server/main.go`.
- API behavior: no customer-facing API contract changes. The manual Ops sync
  retains its current behavior.
- Executable: `cmd/server`.
- Image: `clinic`.
- Clinic CD component: `clinic`.
- app-delivery application: `clinic-api`.
- Kubernetes workload: Clinic Deployment.
- Runtime environments affected after a later release: staging and prod-cloud.
- app-delivery change: image-only bump; no manifest or configuration change.

The module/deployment ownership documented in
`docs/clinic-module-overview.md` is unchanged, so that overview does not require
an update.

## Implementation Steps

1. Add a focused model query that returns local cloud organization sync state
   for supplied DBaaS organization IDs in bounded batches.
2. Add a small comparison seam used by scheduled organization syncs to select
   only missing or changed organizations.
3. Keep `InsertOrUpdateCloudOrg` unchanged and call it only for selected rows.
4. Add a per-job cron registration path using `cron.SkipIfStillRunning` and use
   it only for `MigrateOrganizationJob`.
5. Add focused tests for unchanged, changed, missing, and forced-refresh
   behavior, plus the non-overlap registration contract where practical.
6. Add one run summary log and preserve existing per-error logs.

## Exclusions

- No schema or index changes.
- No schedule-frequency change.
- No bulk upsert or large write transaction.
- No distributed lock or cross-replica leader-election change.
- No changes to event-bus organization updates.
- No Health Check cleanup or other cron optimization.

## Compatibility and Risks

- The scheduled job uses the same `updated_at` comparison already used by
  `InsertOrUpdateCloudOrg`; the optimization moves that no-change decision
  earlier without changing which changed records are written.
- New and changed rows still use the existing transactional implementation, so
  concurrent event-bus updates retain their current behavior.
- Manual `UpdatePlan=true` calls bypass the filter because they intentionally
  force fields that are absent from the list response.
- If any state-prefetch batch fails, the run stops rather than falling back to
  the expensive per-row path. The next two-minute run can retry.

## Test Plan

- Run the focused `pkg/resource_limit` tests proving unchanged organizations do
  not reach the writer, while changed and new organizations do.
- Prove `UpdatePlan=true` continues to process every organization.
- Run focused `pkg/db/model` tests for the local-state query if the package's
  existing database-test seam supports it; otherwise verify its generated SQL
  through a dry-run unit seam.
- Run `go test ./pkg/resource_limit ./pkg/db/model` from the root module.
- Run formatting and inspect the final three-dot diff from `origin/master`.

## Staging Verification

Staging validation was later authorized and completed on the feature image:

- Clinic CD run `32809461596` succeeded for commit
  `c27d37695307ee215998d8d51b8e7fd6ac10deeb`.
- app-delivery PR `78209` merged with only the staging Clinic image update.
- The Clinic Deployment was Ready `1/1`; the Pod ran the expected image digest
  and had zero restarts.
- The first scheduled run after rollout reported `fetched=1564`,
  `skipped=1564`, `processed=0`, `failed=0`, and a duration of about 235 ms.

This backend-only change had no API or UI surface, so no screenshot was needed.

## Release Boundary

Clinic PR `1691` merged as commit
`bc7ccaa6f0b2558dd9809ced2d6853d0bef16680`. Production app-delivery PR `78210`
then released that image to `prod-cloud`.

Production exposed a scale limit that staging did not: about 166,000 DBaaS
organization IDs exceeded MySQL's prepared-statement parameter limit in the
single prefetch query. The bounded-query hotfix is tracked in
`2026-08-25-batch-organization-sync-prefetch.md`.