04 — DP2 — Job Substrate and 9-State Work State Machine

DESIGN-ONLY. Cites Điều 45 §1.2 group D, §3.1, §4, §6.6, §6.7, §7, §11.2, §14. No DDL, no DML.

---

§1. Goal

Define a job-execution substrate distinct from event_outbox so that long-running work (DOT runs, Agent/Hermes/Codex jobs, MARK→CUT pipeline steps, staging cleanup sweeps, vector sync drains, MOT steps, future customer-care/email/message jobs) has a single, governed lifecycle on a PG-native table.

The substrate must:

1. Carry signal only (§4) — source_ref, target_ref, payload_ref, status; never the actual work payload.
2. Honour the §6.6 event-vs-job distinction (do not piggyback on event_outbox).
3. Implement the §6.7 minimum 9-state work state machine.
4. Be claim-and-lease-protected (DP3 details retry/lease/DLQ).
5. Allow events to generate jobs and job completion to emit events without creating a duplicate SoT (doc 13).

---

§2. Current state

• No generic job table exists.
• dot_iu_command_run is a ledger (post-hoc), not a queue (pre-execution).
• dot_iu_command_catalog registers commands but does not enqueue them.
• iu_core.iu_staging_record is a data-tier lifecycle (7-state), not a work-tier lifecycle. The two must not be conflated (per §6.7 reminder).
• Long-running Agent/Hermes/Codex/MOT work is orchestrated externally with no PG presence.

---

§3. Proposed design — job_queue

§3.1 Conceptual model

A job_queue row represents one unit of executable work. Each row:

• has an idempotency_key (UNIQUE) so re-enqueue is safe;
• has an executor field naming a member of the §11.5 whitelist;
• carries source_ref / target_ref / payload_ref (never the payload itself);
• transitions through the §6.7 9-state machine via gated functions;
• correlates to event_outbox.id if event-spawned and to dot_iu_command_run.run_id if it's a DOT-execution variant.

§3.2 NON-EXECUTABLE schema sketch

NON-EXECUTABLE DESIGN SKETCH — DO NOT APPLY

table public.job_queue (
  job_id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  job_kind              text NOT NULL,                                  -- vocab in consumer_registry (DP5)
  event_domain          text NOT NULL,                                  -- CHECK = event_outbox 9-domain set
  idempotency_key       text NOT NULL,                                  -- natural key per kind
  source_ref            text NULL,                                      -- KB path / canonical_address / uuid
  target_ref            text NULL,
  payload_ref           text NULL,                                      -- staging_record_id / blob URI / kb_doc_id
  safe_payload          jsonb NOT NULL DEFAULT '{}'::jsonb
      CHECK (NOT safe_payload ?| ARRAY['body','content','raw','vector','embedding','secret','token','password','ssn','personal_data']),
  payload_classification text NOT NULL DEFAULT 'safe_metadata'
      CHECK (payload_classification IN ('safe_metadata','restricted')),

  executor              text NOT NULL
      CHECK (executor IN ('DOT','Agent','Hermes','Codex','PG_worker','external_worker','future_Kestra_adapter')),
  -- §11.5 whitelist verbatim; MOT NOT IN the set.

  status                text NOT NULL DEFAULT 'queued'
      CHECK (status IN ('queued','leased','in_progress','succeeded','failed','retry_waiting','dead_letter','cancelled','cleaned')),
  -- §6.7 9-state minimum verbatim.

  attempts              integer NOT NULL DEFAULT 0 CHECK (attempts >= 0),
  last_error            text NULL,
  last_error_at         timestamptz NULL,

  -- lease / claim (DP3)
  lease_name            text NULL,                                       -- FK target dot_iu_runtime_lease.lease_name
  lease_holder          text NULL,
  lease_token           uuid NULL,
  leased_until          timestamptz NULL,

  -- scheduling
  process_after         timestamptz NOT NULL DEFAULT now(),
  deadline_at           timestamptz NULL,
  priority              smallint NOT NULL DEFAULT 0,                     -- 0=default; positive=higher

  -- correlation
  correlation_id        text NULL,
  causation_event_id    uuid NULL REFERENCES event_outbox(id),           -- nullable; set when job spawned by event
  parent_job_id         uuid NULL REFERENCES job_queue(job_id),          -- MOT graph
  workflow_id           uuid NULL REFERENCES job_workflow(workflow_id),  -- MOT parent

  -- audit
  enqueued_by           text NOT NULL CHECK (btrim(enqueued_by) <> ''),
  enqueued_at           timestamptz NOT NULL DEFAULT now(),
  started_at            timestamptz NULL,
  finished_at           timestamptz NULL,
  source_system         text NOT NULL CHECK (btrim(source_system) <> ''),

  UNIQUE (job_kind, idempotency_key)
);

table public.job_workflow (
  workflow_id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  workflow_kind         text NOT NULL,        -- vocab: mark_cut_pipeline, mot_template_v1, customer_inbound, ...
  generator             text NOT NULL,        -- e.g. 'MOT:template_xyz' — §13.4 makes MOT a generator, not executor
  status                text NOT NULL DEFAULT 'active'
      CHECK (status IN ('active','succeeded','failed','partially_failed','cancelled')),
  correlation_id        text NULL,
  enqueued_by           text NOT NULL,
  enqueued_at           timestamptz NOT NULL DEFAULT now(),
  finished_at           timestamptz NULL
);

§3.3 What stays in event_outbox

• Every event (fact) — unchanged.
• The fact "job X was enqueued/finished" — emitted as a normal event via fn_iu_emit_event from the gated functions in DP2. This keeps event_outbox the durable fact ledger and job_queue the executable queue. No duplicate SoT (doc 13 §3).

---

§4. Lifecycle / status — the 9-state machine

            +-----------+ enqueue +-----------+ claim   +-----------+ work +------------+
   (none) → |  queued   | ─────→ |   leased  | ─────→ | in_progress| ───→ | succeeded  | (terminal)
            +-----------+        +-----------+        +-----------+        +------------+
                |                     ↑                   |
                |                     | lease_renew       | refusal / error
                |                     |                   v
                |                +-----------+      +------------+
                |                | retry_waiting| ←─ |   failed   | (transient)
                |                +-----------+      +------------+
                |                                         |
                |                                         | attempts >= max
                |                                         v
                |                                  +------------+
                |                                  | dead_letter| (terminal-until-resolution)
                |                                  +------------+
                |
                | operator action / supersede
                v
            +-----------+ retention sweep +-----------+
            | cancelled | ─────────────→ |  cleaned  | (terminal)
            +-----------+                +-----------+

Transitions only via gated functions:

NON-EXECUTABLE DESIGN SKETCH — DO NOT APPLY

fn_job_enqueue(p_kind, p_idempotency_key, p_executor, p_source_ref, p_target_ref, p_payload_ref, p_safe_payload, p_priority, p_process_after, p_deadline_at, p_correlation_id, p_causation_event_id, p_actor) RETURNS jsonb
fn_job_claim(p_kind, p_executor, p_lease_name, p_lease_holder, p_lease_ttl_seconds) RETURNS jsonb   -- SELECT FOR UPDATE SKIP LOCKED + lease acquire
fn_job_progress(p_job_id, p_status_signal_only, p_lease_token, p_actor) RETURNS jsonb               -- writes started_at on first call
fn_job_succeed(p_job_id, p_lease_token, p_result_ref, p_actor) RETURNS jsonb
fn_job_fail_transient(p_job_id, p_lease_token, p_error_code, p_error_message, p_actor) RETURNS jsonb -- moves to retry_waiting or dead_letter per max_attempts
fn_job_fail_permanent(p_job_id, p_lease_token, p_error_code, p_error_message, p_actor) RETURNS jsonb -- moves directly to dead_letter (§8.5 refusal contract)
fn_job_cancel(p_job_id, p_reason, p_actor) RETURNS jsonb
fn_job_cleanup_sweep() RETURNS jsonb                                                                 -- moves succeeded/cancelled past retention to cleaned
fn_job_lease_reaper() RETURNS jsonb                                                                  -- recovers stale leases per DP3

Each gated fn writes a dot_iu_command_run row (so DOT-style audit is uniform across executors) and emits a dot.job.<lifecycle_event> event into event_outbox.

---

§5. Indexes / performance notes

NON-EXECUTABLE DESIGN SKETCH — DO NOT APPLY

UNIQUE INDEX  ux_job_queue_kind_idempotency_key   ON job_queue(job_kind, idempotency_key)
PARTIAL INDEX ix_job_queue_claimable              ON job_queue(job_kind, process_after, priority DESC, enqueued_at)
                                                    WHERE status='queued'
PARTIAL INDEX ix_job_queue_lease_active           ON job_queue(lease_name, leased_until)
                                                    WHERE status IN ('leased','in_progress')
PARTIAL INDEX ix_job_queue_dead_letter            ON job_queue(job_kind, last_error_at DESC)
                                                    WHERE status='dead_letter'
INDEX        ix_job_queue_workflow_id             ON job_queue(workflow_id)
INDEX        ix_job_queue_correlation_id          ON job_queue(correlation_id)

• claim query is SELECT … FROM job_queue WHERE status='queued' AND process_after <= now() AND job_kind = ANY(...) ORDER BY priority DESC, enqueued_at LIMIT 1 FOR UPDATE SKIP LOCKED;
• Partial indexes keep hot-path I/O proportional to backlog, not table size.
• Estimated row size: ~400 bytes mean; 100k active rows ≈ 40 MB; well within hot-cache budget.

---

§6. Security / governance

• Only workflow_admin role may call enqueue/claim/progress/cleanup fns; executors hold dot_executor role inheriting necessary GRANTs.
• safe_payload CHECK is structural; violating producers refuse at INSERT.
• executor CHECK is structural; an unrecognised executor is refused.
• MOT cannot claim — claim fn refuses executor='MOT' (MOT is not in the CHECK list and is enumerated as a forbidden label in §13.4).

---

§7. Rollback / disable

• New dot_config key queue.job_substrate.enabled (default false at Phase 1).
• Enqueue fn returns {refused:true, reason:'job_substrate_disabled'} when flag is off.
• Cleanup fn returns 0 rows touched when flag is off.
• Reversal: drop the new tables (after archiving) in a future deprecation pack — not in scope here.

---

§8. Healthcheck / observability

NON-EXECUTABLE DESIGN SKETCH — DO NOT APPLY

view v_job_queue_backlog                AS
  SELECT job_kind, count(*) AS backlog FROM job_queue
   WHERE status='queued' GROUP BY job_kind;

view v_job_queue_in_progress            AS
  SELECT job_kind, count(*) AS in_progress FROM job_queue
   WHERE status IN ('leased','in_progress') GROUP BY job_kind;

view v_job_queue_dead_letter_open       AS
  SELECT * FROM job_queue WHERE status='dead_letter';

function fn_job_queue_health() RETURNS jsonb  -- §15.3 contract
  -- returns {backlog, in_progress, dead_letter_open, oldest_queued_age_s, lease_active}

Aggregated into v_queue_health (DP4).

---

§9. Compatibility with Điều 45 v1.0

Clause	Compliance
§1.2 group D (jobs)	✅ substrate added
§3.1 single substrate per category	✅ one job_queue table covers all job kinds
§4 signal-not-data	✅ CHECK on safe_payload; payload_ref pattern
§6.6 event-vs-job	✅ distinct table
§6.7 9-state minimum	✅ verbatim
§7 idempotency	✅ UNIQUE (job_kind, idempotency_key)
§11.2 producer/queue/executor not mixed	✅ enqueue/claim/progress separated
§11.5 executor whitelist	✅ CHECK list verbatim, MOT excluded
§14 NVSZ	✅ CHECK forbids vector keys; no FK to vector tier
§15 observability	✅ views + health fn

---

§10. Implementation prerequisites

• DP1 must define cadence (so the claim fn is actually called).
• DP3 must specify max_attempts + backoff (so retry_waiting → dead_letter is bounded).
• DP4 must add heartbeat (so §15.5 is enforced).
• DP5 consumer_registry must exist before any event-spawned job is enqueued.
• DP6 job_subscription must exist before per-executor routing is meaningful.

DP2 is the gravity centre of the implementation roadmap — Phase 1 of doc 14.

---

§11. Open questions

#	Question	Routed to
DP2-Q1	Approve job_queue as a single table for all job kinds, or one table per kind?	Council
DP2-Q2	Approve executor CHECK list verbatim from §11.5 (no extension)?	Council
DP2-Q3	Approve priority smallint default 0 (allows ordering without fancy scheduler)?	Council
DP2-Q4	Should job_queue rows store payload_ref as text (one column) or as jsonb (multiple typed refs)?	Council
DP2-Q5	Should a result_ref column exist alongside payload_ref for outputs?	Council
DP2-Q6	Approve emitting dot.job.<lifecycle_event> events (and registering them in event_type_registry)?	Council + §6.4 inclusion gate
DP2-Q7	Should job_workflow be Phase 1 or Phase 6 (defer until MOT)?	Council

---

§12. Self-test

self_test:
  cites_dieu45_section: §1.2,§3.1,§4,§6.6,§6.7,§7,§11.2,§11.5,§14
  defines_status_lifecycle_compatible_with_§6_7: yes (9-state verbatim)
  defines_idempotency_key: yes (UNIQUE (job_kind, idempotency_key))
  defines_retry_dlq: defers numbers to DP3; structure present
  defines_lease: lease_name/holder/token/leased_until columns
  defines_observability_view: v_job_queue_backlog, _in_progress, _dead_letter_open
  defines_dot_config_disable_flag: queue.job_substrate.enabled
  defines_executor_set_compatible_with_§11_5: yes (CHECK verbatim)
  no_vector_in_transient: yes (CHECK on safe_payload keys; no FK to vector tier)
  signal_not_data: yes (payload_ref pattern; CHECK)
  pg_sot: yes
  rollback_concept: dot_config flag + later deprecation pack
  no_pg_cron_dependency_phase_1: yes
  no_pg_18_dependency: yes
  no_mutation_authored: yes

---

DP2 design. No mutation. Authored 2026-05-26 by Claude Opus 4.7 (1M).