# Isis Job Lifecycle Migration Design

Last Updated: 2026-02-22  
Checklist: `MIG-001` (`ISIS-0170`)  
Scope: existing rows in `jobs` and `job_outbox`

## Purpose

Define the production migration design that moves historical job records into
the current lifecycle/status model used by the queue-backed execution path.

This design is intentionally implementation-ready and is the input for:

- `MIG-002` (workflow ID backfill)
- `MIG-003` (provenance backfill)
- `MIG-004` (cost backfill)
- `MIG-005` (rollback and verification scripts)

## Source-of-Truth Lifecycle Model

Canonical job statuses:

- `pending`
- `queued`
- `running`
- `completed`
- `failed`
- `cancelled`

Canonical outbox statuses:

- `pending`
- `processing`
- `enqueued`
- `failed`
- `dead_letter`
- `cancelled`

Operational invariants:

1. Non-terminal jobs (`pending`, `queued`, `running`) must have consistent
   queue/outbox representation.
2. Terminal jobs (`completed`, `failed`, `cancelled`) must have `completed_at`
   populated.
3. `running` jobs should have `started_at` populated.
4. `completed` jobs should have `output` payload and no terminal `error`.
5. `failed`/`cancelled` jobs should have an explanatory `error`.
6. Outbox idempotency key must remain unique and stable.

## Existing Migration-Related Capabilities

Current code already provides partial recovery logic that this migration design
builds on:

- Legacy pending recovery API route:
  - `POST /api/v1/jobs/admin/legacy-pending/recover`
- Recovery implementation:
  - `apps/isis/generation-api/src/services/job.service.ts`
    (`recoverLegacyPendingJobs`)
- Idempotency fallback behavior:
  - `resolveLegacyPendingIdempotencyKey(...)`
- Outbox replay and DLQ controls:
  - `GET /api/v1/jobs/dead-letter`
  - `POST /api/v1/jobs/dead-letter/:outboxId/replay`

The migration here expands beyond legacy `pending` recovery to cover all
historical lifecycle/status drift.

## Drift Classes To Migrate

### Class A: Non-canonical status labels

Historical rows may contain status strings outside the canonical set. Normalize
status by mapping aliases to canonical values before other repairs.

Examples:

- `processing`, `executing` -> `running`
- `done`, `success`, `succeeded` -> `completed`
- `error`, `errored` -> `failed`
- `canceled` -> `cancelled`

### Class B: Non-terminal rows missing outbox linkage

Rows in `pending`, `queued`, or `running` without corresponding outbox rows
cannot be deterministically progressed through queue semantics.

Repair strategy:

- Backfill outbox with deterministic envelope/idempotency key.
- Prefer `pending` outbox state for replay-safe re-dispatch unless queue
  metadata proves the row is already enqueued.

### Class C: Status/field inconsistency

Examples:

- `running` with null `started_at`
- terminal status with null `completed_at`
- `completed` with missing `output`
- `failed` with null `error`

Repair strategy is deterministic and auditable (see Mapping Rules below).

### Class D: Stale non-terminal records

Very old `pending`/`queued`/`running` rows with no queue activity are migrated
to safe terminal states or routed through outbox recovery based on explicit age
and metadata conditions.

## Deterministic Mapping Rules

Evaluation order is strict and idempotent.

1. Canonicalize status token (`lower(trim(status))`) using alias map.
2. If normalized status is terminal:
   - ensure `completed_at` exists (fallback to `NOW()` if missing)
   - ensure terminal reason fields are populated
3. If normalized status is `running` and `started_at` missing:
   - set `started_at = COALESCE(created_at, NOW())`
4. If status is `completed` and output missing:
   - convert to `failed` with migration error marker
   - reason: terminal success without output is not trustworthy
5. For non-terminal statuses missing outbox rows:
   - backfill outbox envelope and idempotency key
   - queue for replay via outbox relay
6. For stale non-terminal rows older than migration threshold:
   - if recoverable envelope can be produced: keep non-terminal and replay
   - otherwise mark `failed` with `Legacy lifecycle migration failed` reason

## Data-Change Plan (Phased)

### Phase 0: Preflight (read-only)

- Capture baseline counts for:
  - status distribution
  - non-canonical statuses
  - non-terminal rows missing outbox
  - terminal rows missing `completed_at`
  - `running` rows missing `started_at`
  - `completed` rows missing output
- Persist baseline snapshot to migration report artifact.

### Phase 1: Status normalization + consistency repair

- Normalize status aliases.
- Repair timestamp/field consistency for canonical statuses.
- Do not enqueue anything in this phase.

### Phase 2: Outbox backfill for non-terminal records

- Backfill outbox rows in bounded batches.
- Use deterministic idempotency keys from existing metadata when present,
  otherwise fallback to job ID strategy.
- Mark newly backfilled rows as `pending` and schedule immediate relay attempt.

### Phase 3: Recovery dispatch

- Trigger outbox relay processing in bounded windows.
- Monitor queue depth and DLQ growth during dispatch.
- Use `isis jobs triage` and `isis jobs replay` for operator control.

### Phase 4: Post-migration verification

- Re-run all preflight diagnostics and compare against baseline.
- Block completion if any critical invariant remains violated.

## SQL Diagnostics (Design Baseline)

```sql
-- status distribution
SELECT status, COUNT(*)
FROM jobs
GROUP BY status
ORDER BY COUNT(*) DESC;

-- non-canonical statuses
SELECT status, COUNT(*)
FROM jobs
WHERE status NOT IN ('pending','queued','running','completed','failed','cancelled')
GROUP BY status
ORDER BY COUNT(*) DESC;

-- non-terminal jobs missing outbox
SELECT COUNT(*) AS non_terminal_without_outbox
FROM jobs j
LEFT JOIN job_outbox o ON o.job_id = j.id
WHERE j.status IN ('pending','queued','running')
  AND o.id IS NULL;

-- running jobs missing started_at
SELECT COUNT(*) AS running_missing_started_at
FROM jobs
WHERE status = 'running'
  AND started_at IS NULL;

-- terminal jobs missing completed_at
SELECT COUNT(*) AS terminal_missing_completed_at
FROM jobs
WHERE status IN ('completed','failed','cancelled')
  AND completed_at IS NULL;
```

## Safety and Rollback Design

Safety requirements before execution:

1. Production snapshot backup and restore verification.
2. Migration performed in bounded batches with checkpoints.
3. Write-ahead migration report including changed IDs/counts per class.
4. Hard stop criteria on error-rate, lock contention, and DLQ surge.

Rollback posture:

- Follow `docs/domains/isis/remediation/ISIS_ROLLBACK_CRITERIA.md`.
- Prefer forward-fix for field-level repairs when safe.
- Use snapshot restore only for critical corruption scenarios.

## Operational Controls During Execution

- Queue/worker metrics: `GET /api/v1/jobs/stats`
- Queue health: `GET /api/v1/jobs/admin/queue/health`
- Legacy recovery (batch): `POST /api/v1/jobs/admin/legacy-pending/recover`
- DLQ inspection/replay:
  - `GET /api/v1/jobs/dead-letter`
  - `POST /api/v1/jobs/dead-letter/:outboxId/replay`
- Operator CLI:
  - `isis jobs triage`
  - `isis jobs replay`

## Deliverables for Next Tasks

Required outputs for follow-on implementation tasks:

1. `MIG-002`: SQL and app-level backfill for missing workflow IDs.
2. `MIG-003`: provenance reconstruction plan per output storage record.
3. `MIG-004`: provider log to job-cost reconciliation map.
4. `MIG-005`: executable migration, rollback, and verification scripts derived
   from this design.
   - `scripts/isis/verify_job_lifecycle_migration.sh`
   - `scripts/isis/rollback_job_lifecycle_migration.sh`
