docs/maintenance-jobs.md

Rama main · réplica de lectura del repositorio original.

# Operator-driven maintenance jobs

Maintenance runs through a separate HTTP listener in the API container. There
is no startup backfill, scheduler, background executor, or automatic restart.
The initial registered processor is `issue_status_category`, version 1. The
shared `maintenance_job` table also supports later database repair/backfill
processors with their own parameter, checkpoint and result schemas.

## Enable the local entrypoint

Set `MAINTENANCE_PORT` to an unused port (for example 6061) when deploying the
API. Unset means disabled. The bind address is always **127.0.0.1** and cannot
be overridden. Invalid configuration or a bind failure disables only this
listener and logs an error; check the listener before attempting maintenance.

Do not publish this port, add it to a Service/Ingress, or proxy these routes.
There is deliberately no application authentication. Access relies on container
exec/operational permissions and network-namespace isolation; another process
in the same namespace can also connect. Host-networked containers share the
host loopback namespace. This API is never mounted on the public router.
Avoid placing credentials or user content in job parameters or logs.

The backend image includes `/app/maintenance`, a standalone Go HTTP client.
It needs no Python, curl, source checkout, or database credentials. It only
connects to loopback, ignores HTTP proxy variables, and refuses redirects.
Merely enabling the listener does not create or advance a job.
No production execution is part of submitting this PR.

Enable the listener using the deployment's existing configuration/release flow:

- Helm: set `backend.config.maintenancePort: "6061"` in the release values.
  The ConfigMap supplies `MAINTENANCE_PORT`; its checksum rolls the backend.
  Neither the Service nor the container's published ports gains a maintenance port.
- Compose: set `MAINTENANCE_PORT=6061` in the deployment `.env`, then recreate
  the backend with `docker compose -f docker-compose.selfhost.yml up -d backend`.
  The maintenance port is not published to the host.

Run every command below in the **target backend container**. For example:

```bash
# Compose; omit -T only when an interactive terminal is desired.
docker compose -f docker-compose.selfhost.yml exec -T backend /app/maintenance status --job JOB_UUID
# Kubernetes; select a backend pod from the intended release/namespace.
kubectl exec -n NAMESPACE POD -c backend -- /app/maintenance status --job JOB_UUID
```

Both forms inherit `MAINTENANCE_PORT` from the container. `--port 6061` can
explicitly select another configured loopback port. Use `create --help` or
`run --help` to check that the deployed image includes the command. A successful
`status` or `create` request confirms the listener is reachable; a connection
error means configuration/listener startup must be checked before continuing.
No public API token or kubectl port-forward is required. Container exec is a
privileged operational capability. Keep the attached exec session open until
the command returns; this is not a background service.

## Category backfill prerequisites

1. Deploy PR1's matching migration runner through 478. Inspect the actual CHECKs
   and functions, not just the migration ledger. Never execute historical 469
   directly. The API checks the ledger, compatibility vocabulary, mapping
   function, terminal spellings and its maintenance indexes.
2. Finish the full compatible backend rollout, including every writer. Capture
   deployment revision/replica evidence: the database cannot prove this.
   `parameters.writers_upgraded=true` is the operator's explicit attestation,
   not automatic proof of deployment.
3. Apply migrations 485–488. They only create the maintenance table and three
   unique indexes; indexes run concurrently in separate migration files with
   invalid-index cleanup hooks. No issue-status rows are changed.
4. Record baseline API latency/error rate, row-lock waits, DB I/O, WAL rate and
   replica lag. Choose batch/delay limits against the environment's existing
   alert budgets, beginning with a bounded canary. Pause if any metric exceeds
   its agreed budget; reduce batch size/increase delay before resuming.
   No fixed throughput or historical row count is a current safety guarantee.

## Dry-run, then apply

The driver requires explicit batch size, delay and both SQL timeouts for
**create and configure**, including dry-runs. The following values are an
example for a monitored canary, not production defaults or a throughput promise.
Select them against the target database's latency/lock/replica-lag budgets.
Commands are shown without the container-exec prefix for readability:

```bash
/app/maintenance create --idempotency-key status-category-v1-check-001 \
  --batch-size 500 --delay-ms 100 --lock-timeout-ms 500 --statement-timeout-ms 3000
```

Keep the returned `id`. Creation defaults to dry-run; dry-run persists its
progress/report but never changes category data. One command can drive the
whole survey, with each HTTP request still advancing only one short transaction:

```bash
# Canary: exits 2 (incomplete) unless this one batch completes the job.
/app/maintenance run --job JOB_UUID --max-batches 1 --max-seconds 30
# After checking canary metrics, run a bounded window automatically.
/app/maintenance run --job JOB_UUID --max-batches 3000 --max-seconds 1800
/app/maintenance status --job JOB_UUID
```

For 600,000 rows at batch size 500, a dry-run needs about 1,201 advances;
apply plus verification needs about 2,402, including end-of-pass detection.
The 3,000-batch/1,800-second example has headroom for that row count but is
still a hard stop. At a 100ms batch delay, waiting alone is about 240 seconds
for apply plus verification, before database and HTTP time. A smaller batch,
more rows, contention, or retries can exceed either budget. Re-run `status`,
inspect metrics, and explicitly start another bounded `run` if incomplete.
No one needs to invoke thousands of curl commands manually.

`run` writes the observed job JSON after each advance and exits:

- **0:** the persisted job is `completed` (for dry-run, only the survey completed).
- **2:** incomplete because either batch or elapsed-time budget ran out.
- **1:** invalid arguments, cancellation, a paused/cancelled job, or an API error.

Time includes requests, delay and retries; sleeps and requests are cancellable.
A timeout or transport error can leave a final commit's acknowledgement unknown.
Read `status` before continuing; do not interpret incomplete/error as rollback.
Keep stdout and stderr in the operator session log. Process exit never restarts
itself or schedules later work.

Once dry-run is completed, create a **new** apply record with a new key:

```bash
/app/maintenance create --idempotency-key status-category-v1-apply-001 \
  --apply --parameters '{"writers_upgraded":true}' \
  --batch-size 500 --delay-ms 100 --lock-timeout-ms 500 --statement-timeout-ms 3000
```

Use the batch/delay/timeouts selected from the dry-run and monitored apply
canary. Run one batch first, check the metrics, then continue with an explicit
budget as above. Dry-run cannot measure update WAL/lock impact; the apply
canary is required too. Tuning a paused record preserves the cursor.

Creating with the same idempotency key and identical normalized parameters
returns the original job. Reusing that key with different input conflicts.
Only one ready/paused record per job type and scope is permitted, including
dry-runs. Finish or explicitly cancel the existing record before starting
another. Different logical scopes must be disjoint; new processors that allow
overlapping scopes must implement an additional conflict guard.

## Pause, tune, resume, failures

Stopping calls stops progress. Query `status`, then use its revision for an
intentional mutation. These operations may return busy while a short batch
holds the job row; query/retry after it finishes. A stale revision performs no
work. Each successful mutation returns the new revision:

```bash
/app/maintenance pause --job JOB_UUID --revision CURRENT_REVISION
/app/maintenance configure --job JOB_UUID --revision PAUSED_REVISION \
  --batch-size 100 --delay-ms 1000 --lock-timeout-ms 250 --statement-timeout-ms 2000
/app/maintenance resume --job JOB_UUID --revision CONFIGURED_REVISION
/app/maintenance run --job JOB_UUID --max-batches 3000 --max-seconds 1800
# To stop permanently, using the latest revision:
/app/maintenance cancel --job JOB_UUID --revision CURRENT_REVISION
```

Cancellation preserves already committed data and the global audit record;
it does not reverse a backfill. Configuration replaces operational limits,
preserving checkpoint and immutable parameters. Limits: batch 1–5000,
delay 1–60000ms, and 1 <= lock timeout <= statement timeout <= 5000ms.
Increasing/decreasing limits cannot erase an existing delay. The raw HTTP API
retains default normalization for omitted/zero limits; operational runs should
use the packaged driver, which requires explicit values.

The underlying routes remain `POST /maintenance/jobs`,
`GET /maintenance/jobs/{id}`, and `POST /maintenance/jobs/{id}/{action}`.
Mutations carry `{"revision":N}`; configure also carries `options`.
The driver retries an ambiguous advance using its **original** revision.
409 with a changed revision acknowledges current state without advancing again;
429 respects the database delay; 409 without a job retries busy with backoff.

Each batch locks the maintenance row NOWAIT, scans an indexed ID page, updates
only still-legacy categories from their current values, and commits data,
checkpoint, counters and revision in one transaction. It includes system,
custom and archived rows, preserving all fields except category. No issue
update events or agent runs are emitted. It does not use SKIP LOCKED or OFFSET.

On SQL failure the batch savepoint rolls back all data changes. The outer
transaction records a paused state and error without advancing the checkpoint.
Timeout/deadlock/serialization errors are marked retryable; the driver retries
with capped exponential backoff (default 3, configurable up to 10), using the
new revision to resume that failed batch. Other errors stop the driver.
A previously paused job is not automatically resumed without `--resume`.
There is no automatic retry after process exit. The driver has batch and wall
time budgets; Ctrl-C stops driving requests.

If the connection/container disappears before commit, data and progress both
roll back. If it disappears after commit, another replica can resume from the
stored cursor. If cancellation prevents even the error record from committing,
the checkpoint stays unchanged and server logs/client transport error provide
the failure evidence; inspect GET before retrying. Graceful shutdown drains
the internal listener before closing the database pool.

## Completion and evidence

Application runs move from the scan phase into a fresh, independently paged
verification phase. Verification rejects residual legacy values, unknown
categories and noncanonical system pairs. A bad page pauses with the first
offending status ID. Fix the cause, then resume; for residuals behind the scan
cursor, cancel and start a new pass after eliminating old writers.

Only a complete verification pass produces `status=completed` and
`result.remaining_legacy=0`. That report depends on the no-old-writers rollout
precondition; a paged scan cannot certify that an old writer will never write
again. Dry-run completion only means the read-only survey completed, not that
the data has been converted. Dry-run counts and `would_update` are observations
during a paged scan, not a globally consistent snapshot. Progress is cumulative;
`updated` counts actual committed updates, while `would_update` counts old
values observed before any concurrent application normalization. Neither is a
live remaining-row count. Use a fresh dry-run to refresh backlog evidence.

Save the job JSON, deployment evidence, monitoring/canary results and operator
session log. Completed/cancelled rows are retained; repeated maintenance creates
new records rather than resetting history. Down migrations intentionally retain
state and indexes. Keep the compatible backend and fix forward on failures.

PR3 is a separate release; this job never triggers schema contraction. For SaaS,
finish the apply/verify pass before deploying PR3. Self-host upgrades do not
require this job: migration 491 automatically converts any residual old values
through the ordinary upgrade entrypoint. See the [release sequence](issue-status-lifecycle-rollout.md).

After 492/494, the category v1 processor intentionally fails its compatibility
preflight. Do not start/resume it on the contracted schema or weaken the
preflight. Completed/cancelled records remain readable for audit, and the generic
maintenance framework remains available for future processors. Installed-client
API compatibility remains after storage contraction.

## Adding a processor

Register a code-defined `maintenance.Processor` with a type and version. Validate
scope and parameters, implement preflight and one bounded Step, and return
versioned checkpoint/progress/result JSON objects plus a completion flag.
A processor uses only the supplied primary-database transaction. Unknown
types/versions and malformed state fail closed. This is not an arbitrary SQL
execution endpoint. Checkpoint atomicity does not cover external side effects;
such processors need their own idempotency/recovery design before registration.