M1.2 — Migration 007 : `work_order` colonnes manquantes

[zestones]

Note

Milestone: M1 — Data Layer
Planning doc: docs/planning/M1-data-layer/issues.md

Scope. Étendre work_order pour porter les sorties d'agents.

Colonnes à ajouter.

Colonne	Type	Default	Rôle
rca_summary	text	null	RCA produit par Investigator
recommended_actions	jsonb	null	Actions structurées (steps)
generated_by_agent	boolean	false	Distinguer manuels vs agents
trigger_anomaly_time	timestamptz	null	Timestamp anomalie source

Note sur status. La contrainte actuelle est
CHECK (status IN ('open','in_progress','completed','cancelled')). Le flow agent
introduit 'detected' (Sentinel) et 'analyzed' (post-Investigator).

Note

Decided — étendre le CHECK.
Migration 007 :

ALTER TABLE work_order DROP CONSTRAINT work_order_status_check;
ALTER TABLE work_order ADD CONSTRAINT work_order_status_check
  CHECK (status IN ('detected','analyzed','open','in_progress','completed','cancelled'));

Flow : detected (Sentinel) → analyzed (Investigator a posé rca_summary) →
open (Work Order Generator a posé recommended_actions) → in_progress →
completed. Frontend filtre/colore par statut.

Acceptance.

• Migration applique proprement
• INSERT avec status='detected' accepté
• WorkOrderCreate Pydantic mis à jour pour accepter ces champs (issue M1.6 — M1.6 — Mettre à jour WorkOrderCreate / WorkOrderUpdate Pydantic #7, out of scope here)

---

Work order status state machine

stateDiagram-v2
    [*] --> detected: Sentinel detects anomaly
    detected --> analyzed: Investigator emits rca_summary
    analyzed --> open: Work Order Generator emits recommended_actions
    open --> in_progress: operator picks up
    in_progress --> completed: technician closes
    open --> cancelled: operator dismisses
    in_progress --> cancelled: operator dismisses
    completed --> [*]
    cancelled --> [*]

Loading

Important

The CHECK constraint must include all six states. Frontend uses the same enum
for filters and badge colours (see M9.1).

[zestones]

Implementation notes (M1.2)

Note

Added work_order extension to the existing 007_aria_kb_workorder_extension.up.sql (same file as M1.1 — scope matches the filename). 4 new columns + widened status CHECK. No other files touched — Pydantic + repository changes belong to M1.6 (#7).

Correction vs planning SQL

Important

Planning doc and issue body used the name work_order_status_check (Postgres default format) for the existing constraint. The real name — set in migration 005_aria_schema.up.sql line 97 via CONSTRAINT chk_wo_status CHECK (...) — is chk_wo_status. Using the planning SQL verbatim would have failed with constraint "work_order_status_check" of relation "work_order" does not exist. Migration uses the correct name.

Status state machine (now enforced by DB)

stateDiagram-v2
    [*] --> detected: Sentinel anomaly
    detected --> analyzed: Investigator posts rca_summary
    analyzed --> open: Work Order Generator posts recommended_actions
    open --> in_progress: operator picks up
    in_progress --> completed: technician closes
    open --> cancelled
    in_progress --> cancelled
    completed --> [*]
    cancelled --> [*]

Loading

What was verified

Applied the new DDL inside BEGIN; … ROLLBACK; against the live dev DB to validate before shipping the migration file. All three checks pass:

Check	Result
ALTER TABLE ADD COLUMN × 4	OK — columns present with correct types/defaults
DROP CONSTRAINT chk_wo_status + re-add with 6 values	OK
INSERT … status='detected'	accepted (row returned)
INSERT … status='analyzed'	accepted (row returned)
INSERT … status='nonsense'	rejected with check_violation
make lint	clean
make typecheck	0 errors

Out of scope (tracked elsewhere)

Warning

• M1.6 (M1.6 — Mettre à jour WorkOrderCreate / WorkOrderUpdate Pydantic #7) — backend/modules/work_order/schemas.py:
  • Status = Literal["open","in_progress","completed","cancelled"] (line 12) still has only 4 values. API requests with status="detected" or status="analyzed" will be rejected by Pydantic 422 before reaching the DB. The DB accepts them, but the API won't forward them until M1.6 lands.
  • WorkOrderCreate / WorkOrderUpdate don't expose rca_summary, recommended_actions, generated_by_agent, trigger_anomaly_time.
  • WorkOrderRepository.JSON_FIELDS (repository.py:11) needs "recommended_actions" added.
• This means writes via the HTTP layer can't yet produce status='detected' rows — only direct SQL / the future Sentinel agent can. That's the expected M1.2 → M1.6 gap.

[zestones]

⏱️ Total tracked time: 7min 47s

#	Date	Duration
1	2026-04-22	7min 47s