Motir

The three tables the engine runs on, as a Prisma migration, following motir-core's schema conventions.

The tables

• job_event — the emitted event log. One row per sendEvent; a dispatcher fans it out to one job_queue row per subscribing job. Carries the event name, its payload, received_at, and the workspace_id when the payload has one.
• job_queue — one row per RUN: the job id, the triggering event, run_at (when it becomes claimable), attempts, state, the claiming worker and its lease. This is the table the worker's claim loop reads.
• job_step — memoized step results, keyed (run_id, step_id) with a unique index. This is what makes step.run idempotent across retries and restarts, and it is the whole reason the 84 existing call sites do not have to change.

Conventions this must respect

• Every FK is modelled as a Prisma @relation on both sides with matching onDelete / onUpdate, never created in raw SQL alone — a scalar column with a raw-SQL FK puts the schema graph and the migration-built database in permanent drift and makes the next migrate dev propose dropping it.
• RLS, following the pattern job_run / job_run_dlq already use (MOTIR-63). workspace_id is NULL for system jobs, and the policy must handle that case rather than assuming it is always present.
• A hand-written partial index must not reuse the column list of an @@index on the same model — the differ pairs indexes by column list and reports a permanent spurious RENAME. Give any partial index a column list of its own, chosen from what its query actually filters on.

Acceptance criteria

• The three tables exist with their relations modelled on both sides, and prisma migrate diff --from-schema … --to-config-datasource --exit-code reports no drift after a from-empty replay.
• (run_id, step_id) is unique on job_step — asserted by a test that tries to insert a duplicate, not by reading the schema.
• RLS policies are present and a cross-tenant read is refused, tested against a real Postgres under the app role rather than the owner role.
• The claim query's supporting index is chosen from what that query actually filters and orders on, and the choice is justified in a comment.
• Unit/integration tests ship with the migration.

Context refs

• prisma/schema.prisma — where the models go
• lib/jobs/dlq.ts and the job_run / job_run_dlq models — the existing ledger tables and their RLS, the pattern to mirror
• motir-core/CLAUDE.md § Migrations — the @relation and partial-index rules above
• The decision record from the foundation card, which may specify tables of its own to sit beside