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:
pendingqueuedrunningcompletedfailedcancelled
Canonical outbox statuses:
pendingprocessingenqueuedfaileddead_lettercancelled
Operational invariants:
- Non-terminal jobs (
pending,queued,running) must have consistent queue/outbox representation. - Terminal jobs (
completed,failed,cancelled) must havecompleted_atpopulated. runningjobs should havestarted_atpopulated.completedjobs should haveoutputpayload and no terminalerror.failed/cancelledjobs should have an explanatoryerror.- 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-letterPOST /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->runningdone,success,succeeded->completederror,errored->failedcanceled->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
pendingoutbox state for replay-safe re-dispatch unless queue metadata proves the row is already enqueued.
Class C: Status/field inconsistency#
Examples:
runningwith nullstarted_at- terminal status with null
completed_at completedwith missingoutputfailedwith nullerror
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.
- Canonicalize status token (
lower(trim(status))) using alias map. - If normalized status is terminal:
- ensure
completed_atexists (fallback toNOW()if missing) - ensure terminal reason fields are populated
- ensure
- If normalized status is
runningandstarted_atmissing:- set
started_at = COALESCE(created_at, NOW())
- set
- If status is
completedand output missing:- convert to
failedwith migration error marker - reason: terminal success without output is not trustworthy
- convert to
- For non-terminal statuses missing outbox rows:
- backfill outbox envelope and idempotency key
- queue for replay via outbox relay
- For stale non-terminal rows older than migration threshold:
- if recoverable envelope can be produced: keep non-terminal and replay
- otherwise mark
failedwithLegacy lifecycle migration failedreason
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 runningrows missingstarted_atcompletedrows 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
pendingand 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 triageandisis jobs replayfor 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)#
-- 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:
- Production snapshot backup and restore verification.
- Migration performed in bounded batches with checkpoints.
- Write-ahead migration report including changed IDs/counts per class.
- 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-letterPOST /api/v1/jobs/dead-letter/:outboxId/replay
- Operator CLI:
isis jobs triageisis jobs replay
Deliverables for Next Tasks#
Required outputs for follow-on implementation tasks:
MIG-002: SQL and app-level backfill for missing workflow IDs.MIG-003: provenance reconstruction plan per output storage record.MIG-004: provider log to job-cost reconciliation map.MIG-005: executable migration, rollback, and verification scripts derived from this design.scripts/isis/verify_job_lifecycle_migration.shscripts/isis/rollback_job_lifecycle_migration.sh