Motir

Estimate: 28m · Depends on: 1.6.3

Codify the cross-cutting patterns every future job (Epic 5 notifications, Epic 7 LLM calls, Epic 6 search indexing) will follow. Five concrete moves:

• Retry policy module (lib/jobs/retries.ts): three named policies defineJob accepts via a retryPolicy shorthand — "transient" (3 attempts, exponential), "idempotent" (5 attempts, longer backoff — safe for read-only or naturally-idempotent operations), "none" (1 attempt, fail fast — for system jobs where retry semantics are wrong, like "send signup notification once or not at all"). The wrapper translates the policy to Inngest's retries + cancelOn configuration; the policy choice is documented per-job and visible in the dashboard.
• Dead-letter queue:
  • Prisma migration adds job_run_dlq: id (cuid), workspace_id (FK, nullable for system events), function_id, event_name, event_data (jsonb), failure (jsonb), attempts (int), first_failed_at, last_failed_at, replayed_at (nullable). Indexes: (workspace_id, last_failed_at desc).
  • defineJob's failure path: after the final retry exhausts, write a job_run_dlq row inside a tx that also flips the job_run.status to failed. This is the durable record the dashboard reads from; Inngest's own failure surface stays available for deep tracing but isn't the source of truth for operator action.
  • lib/jobs/dlq.ts exposes replayDLQ(dlqId, tx) — the service-layer function the dashboard's "Replay" button calls. Re-emits the original event via sendEvent(), sets the DLQ row's replayed_at. Replay is auditable; a workspace owner can see when a DLQ entry was retried and by whom (reuse Story 1.5's identity propagation through Server Actions).
• Scheduled-job primitive: defineJob accepts an optional cron field. Inngest's { cron: "0 9 * * *" } trigger pattern lets us schedule jobs without a separate scheduler service. Document the canonical cron usage in docs/jobs.md: scheduled jobs emit a synthetic event so the dashboard treats them uniformly with event-triggered jobs (the job_run row's event_name is "scheduled.{job_id}"). Add a placeholder system.daily-health-check job (cron "0 9 * * *") that no-ops and writes a job_run row — proves the scheduled path works end-to-end. Replaces the 1.6.2 system.ping smoke job.
• RLS on job_run + job_run_dlq: follow the Story 1.2 workspace-RLS pattern exactly — a policy that filters rows by workspace_id = current_setting('prodect.workspace_id')::text. System events (workspace_id IS NULL) are visible only when the request context sets prodect.system_admin = 'true' (the same escape hatch Story 1.2 ships for cross-workspace admin tooling). Test: cross-workspace reads return zero rows even when the row exists; system-event reads succeed only with the admin context set.
• Documentation: docs/jobs.md grows three new sections — Retry policies (when to pick each named policy + examples), Dead-letter queue (operator runbook: how DLQ rows appear, how to replay, when NOT to replay), Scheduled jobs (cron syntax, the synthetic-event convention, how scheduled-job failures surface in the dashboard).

Why bundle these four patterns into one Subtask: they share the same migration boundary (the DLQ table + RLS policies on both tables ship together as a single Prisma migration), they share the same wrapper surface (defineJob grows three new options at once), and they share the documentation block. Splitting them would mean three Prisma migrations and three wrapper-API edits stacked across three PRs against the same files — a high-collision shape per past parallel-Subtask lessons.

Acceptance criteria

• lib/jobs/retries.ts ships the three named policies; defineJob accepts a retryPolicy shorthand and maps it to the underlying Inngest config.
• Prisma migration adds job_run_dlq per the schema in the description; RLS policies on both job_run and job_run_dlq mirror the Story 1.2 workspace-scope pattern (with the system-admin escape hatch).
• defineJob's failure path writes a job_run_dlq row inside a transaction that also flips job_run.status to failed; verified by a Vitest spec that forces a deliberate handler failure beyond the retry budget and asserts both rows exist.
• lib/jobs/dlq.ts exports replayDLQ(dlqId, tx); calling it re-emits the original event and sets replayed_at; idempotency-keyed events stay deduped after replay (same key → no double-execute) — this interaction is documented in docs/jobs.md with the workaround (re-shape the idempotency key when a code change makes the original a no-op, or wait the window out).
• defineJob accepts an optional cron field; the system.daily-health-check job exists in the registry with a documented cron expression; the 1.6.2 system.ping smoke job is removed (replaced by this).
• RLS specs in tests/jobs/rls.test.ts cover: cross-workspace reads return zero rows; system-event reads require the prodect.system_admin context; the existing workspace-isolation E2E spec is extended with a job-run isolation case.
• docs/jobs.md grows the three new sections (Retry policies, DLQ, Scheduled jobs) with worked examples for each.
• All quality gates green; existing tests + E2E stay green.

Context refs

• motir-core/CLAUDE.md — 4-layer rule (auto-loaded)
• prisma/sql/ + Story 1.2's RLS migration — the canonical RLS pattern to mirror
• lib/db.ts + the workspace-context middleware from Story 1.2 — how prodect.workspace_id gets set on the session
• lib/jobs/* from 1.6.2 + 1.6.3 — the wrapper to extend
• Inngest retries + cron triggers docs
• The 1.6.3 email.send job — concrete exemplar to retrofit with a named retry policy