# Batch Organization Sync Prefetch

## Background

The organization sync optimization introduced a single query whose `IN` list
contains every cloud organization ID. Production currently returns about
166,000 organizations, which exceeds MySQL's 65,535 prepared-statement
parameter limit. The scheduled job therefore stops before processing any
organization.

The same change also made manual `UpdatePlan=true` synchronization continue
after an organization-detail fetch fails. The previous implementation stopped
the run at that point through its outer panic recovery. This hotfix preserves
that effective stop-on-error behavior without relying on a nil dereference.

## Goal

- Make local organization-state prefetch work for production-sized ID sets.
- Preserve the existing lowest-local-row and soft-deleted-row behavior.
- Preserve the pre-optimization manual forced-sync stop-on-detail-error
  behavior.
- Keep the fix limited to the production regression and focused tests.

## Current Behavior

- `ListCloudOrganizationUpdatedAt` puts all supplied IDs into one GORM `IN`
  query.
- A scheduled production run supplies about 166,000 IDs, so the query fails
  before any organization reaches the existing upsert path.
- A manual forced run logs a detail-fetch error and continues with later
  organizations, unlike the previous effective behavior.

## Target Contract

- Deduplicate the supplied DBaaS organization IDs and query them sequentially
  in bounded batches.
- Merge each batch into the same `dbaas_org_id -> updated_at` map.
- Keep using unscoped rows, `id ASC`, and the lowest-ID row when legacy
  duplicates exist.
- Return an error if any batch fails; the scheduled job stops and retries on
  the next run instead of falling back to per-row database work.
- A manual forced run stops cleanly when an organization detail cannot be
  fetched.

## Affected Modules and Runtime Path

- Go module: repository root (`github.com/pingcap-inc/clinic`).
- Packages: `pkg/db/model` and `pkg/resource_limit`.
- Entrypoint: `cmd/server/main.go`.
- Image: `clinic`.
- Clinic CD component: `clinic`.
- app-delivery application: `clinic-api`.
- Kubernetes workload: Clinic Deployment.
- Target environments after separately authorized releases: staging and
  prod-cloud.
- Nearby components that do not require deployment: Clinic UI and data-service.

The ownership and deployment mapping in `docs/clinic-module-overview.md` does
not change.

## Implementation

1. Add a small internal loader seam around the existing GORM state query.
2. Deduplicate IDs and invoke that loader in sequential batches of 5,000.
3. Keep the current merge and duplicate-row selection behavior.
4. Stop a manual forced run after a detail-fetch error.
5. Add focused regression tests for an input larger than 65,535 IDs, duplicate
   input IDs, loader errors, and manual forced-sync error behavior.

## Exclusions

- No RDS schema or index change.
- No new concurrency, retry loop, checkpoint, or fallback path.
- No schedule, event-bus, API, or UI change.
- No refactor of unrelated organization or cluster synchronization code.

## Compatibility and Risk

The query result and write decisions remain the same; only the number of SQL
statements changes from one oversized statement to a bounded sequence. The
batch size is well below MySQL's parameter limit. Sequential execution avoids
turning recovery into a burst of concurrent RDS reads.

As before, state can change between the prefetch and upsert decision. This
hotfix does not expand that existing behavior or change the event-driven sync
path.

## Test Plan

- First demonstrate that a loader called with more than 65,535 unique IDs is
  not bounded by the current implementation.
- Verify each loader call contains at most 5,000 unique IDs and all requested
  IDs are covered exactly once.
- Verify a batch error is returned without producing a partial result.
- Verify manual forced sync stops after a detail-fetch error.
- Run focused `pkg/db/model` and `pkg/resource_limit` tests, then the smallest
  useful broader package tests and `git diff --check`.

## Staging Verification

Skipped with the user's approval. Staging has only about 1,500 organizations,
so it cannot reproduce the production-only prepared-statement limit. The
focused test covers more than 65,535 unique IDs; CI and production scheduled-run
logs are the useful acceptance signals for this fix.

## Release Boundary

The user authorized creating the issue and Clinic PR, enabling source-PR
auto-merge after required checks, and releasing the merged `master` revision to
`prod-cloud`. This authorization includes merging the generated production
app-delivery PR after confirming it only updates the intended Clinic image and
its required checks pass. No other environment or component is in scope.