---
phase: 06-background-worker
plan: 01
type: execute
wave: 1
depends_on: []
files_modified:
  - backend/go.mod
  - backend/go.sum
  - backend/internal/db/queries/files.sql
  - backend/internal/db/sqlc/files.sql.go
  - backend/internal/jobs/heartbeat.go
  - backend/internal/jobs/orphan_cleanup.go
  - backend/internal/jobs/error_handler.go
  - backend/internal/jobs/heartbeat_test.go
  - backend/internal/jobs/orphan_cleanup_test.go
  - backend/internal/jobs/error_handler_test.go
autonomous: true
requirements:
  - WORK-01
  - WORK-02
  - WORK-03
  - WORK-04

must_haves:
  truths:
    - "D-01: river and riverpgxv5 are importable from backend/internal/jobs/ without build errors (go.mod contains github.com/riverqueue/river)"
    - "D-04: HeartbeatWorker.Work() returns nil and logs 'worker heartbeat' with job_id and attempt fields"
    - "D-04: OrphanCleanupWorker.Work() iterates orphan rows, deletes S3 object then DB row, logs per-run summary"
    - "D-06: SlogErrorHandler implements river.ErrorHandler (both HandleError and HandlePanic compile)"
    - "D-04: ListOrphanFiles sqlc query is generated and returns []ListOrphanFilesRow with {ID, TabloID, S3Key}"
    - "Unit tests for HeartbeatWorker, OrphanCleanupWorker, and SlogErrorHandler pass"
  artifacts:
    - path: "backend/go.mod"
      provides: "river dependency declarations"
      contains: "github.com/riverqueue/river"
    - path: "backend/internal/jobs/heartbeat.go"
      provides: "HeartbeatArgs + HeartbeatWorker"
      exports: ["HeartbeatArgs", "HeartbeatWorker"]
    - path: "backend/internal/jobs/orphan_cleanup.go"
      provides: "OrphanCleanupArgs + OrphanCleanupWorker + constructor"
      exports: ["OrphanCleanupArgs", "OrphanCleanupWorker", "NewOrphanCleanupWorker"]
    - path: "backend/internal/jobs/error_handler.go"
      provides: "SlogErrorHandler"
      exports: ["SlogErrorHandler"]
    - path: "backend/internal/db/queries/files.sql"
      provides: "ListOrphanFiles query"
      contains: "name: ListOrphanFiles :many"
  key_links:
    - from: "backend/internal/jobs/orphan_cleanup.go"
      to: "backend/internal/db/sqlc/files.sql.go"
      via: "sqlc.New(w.pool).ListOrphanFiles(ctx)"
      pattern: "ListOrphanFiles"
    - from: "backend/internal/jobs/orphan_cleanup.go"
      to: "backend/internal/files/store.go"
      via: "w.store.Delete(ctx, f.S3Key)"
      pattern: "\.store\.Delete"
---

<objective>
Add the river dependency and implement the `internal/jobs/` package — job arg types, worker structs, and the error handler — plus the `ListOrphanFiles` sqlc query that the orphan cleanup worker depends on. Unit tests prove each component in isolation before the binary is wired in Plan 02.

Purpose: Delivers the entire domain layer for Phase 6 (WORK-01 partially, WORK-02, WORK-03, WORK-04) as a vertical slice that compiles and tests cleanly before cmd/worker is wired.
Output: `backend/internal/jobs/` package with three files + tests; updated `files.sql` and regenerated sqlc; river in `go.mod`.
</objective>

<execution_context>
@/Users/arthur.belleville/Documents/perso/projects/xtablo-source/.claude/get-shit-done/workflows/execute-plan.md
@/Users/arthur.belleville/Documents/perso/projects/xtablo-source/.claude/get-shit-done/templates/summary.md
</execution_context>

<context>
@.planning/ROADMAP.md
@.planning/REQUIREMENTS.md
@.planning/phases/06-background-worker/06-CONTEXT.md
@.planning/phases/06-background-worker/06-RESEARCH.md
@.planning/phases/06-background-worker/06-PATTERNS.md

<interfaces>
From backend/internal/files/store.go:
  type FileStorer interface {
    Upload(ctx context.Context, key string, file io.Reader) (contentType string, bytesWritten int64, err error)
    Delete(ctx context.Context, key string) error
    PresignDownload(ctx context.Context, key string) (string, error)
  }

From backend/internal/db/sqlc/db.go:
  func New(db DBTX) *Queries   // *pgxpool.Pool satisfies DBTX

From backend/internal/db/queries/files.sql (existing queries — append ListOrphanFiles after DeleteTabloFile):
  -- name: DeleteTabloFile :exec
  DELETE FROM tablo_files WHERE id = $1 AND tablo_id = $2;

  -- DeleteTabloFileParams is generated as: {ID uuid.UUID, TabloID uuid.UUID}
  -- These fields are available from ListOrphanFilesRow — no new delete query needed.

From backend/internal/db/sqlc/files.sql.go (generated pattern — ListOrphanFiles will follow this shape):
  func (q *Queries) ListFilesByTablo(ctx context.Context, tabloID uuid.UUID) ([]TabloFile, error)
  // ListOrphanFiles will be: func (q *Queries) ListOrphanFiles(ctx context.Context) ([]ListOrphanFilesRow, error)
  // ListOrphanFilesRow fields: ID uuid.UUID, TabloID uuid.UUID, S3Key string
</interfaces>
</context>

<tasks>

<task type="auto" tdd="true">
  <name>Task 1: Add river dependency + ListOrphanFiles sqlc query</name>
  <files>backend/go.mod, backend/go.sum, backend/internal/db/queries/files.sql, backend/internal/db/sqlc/files.sql.go</files>
  <read_first>
    - backend/go.mod — read current require block before adding deps
    - backend/internal/db/queries/files.sql — read existing queries to understand file style and append position
  </read_first>
  <behavior>
    - After `go get`, `go.mod` contains `github.com/riverqueue/river` and `github.com/riverqueue/river/riverdriver/riverpgxv5`
    - `ListOrphanFiles` query appended to files.sql follows exact annotation style of existing queries in that file
    - After `just generate`, `backend/internal/db/sqlc/files.sql.go` exports `ListOrphanFiles(ctx context.Context) ([]ListOrphanFilesRow, error)` and `ListOrphanFilesRow` struct with fields `ID`, `TabloID`, `S3Key`
    - `go build ./...` exits 0 after changes
  </behavior>
  <action>
    Step 1 — add river modules (run from backend/ directory):
      go get github.com/riverqueue/river@v0.37.0
      go get github.com/riverqueue/river/riverdriver/riverpgxv5@v0.37.0
    These commands update both go.mod and go.sum. Do NOT hand-edit go.mod for these deps.

    Step 2 — append the orphan query to backend/internal/db/queries/files.sql (after the existing DeleteTabloFile query). The query name annotation is "name: ListOrphanFiles :many". The SELECT returns three columns: id, tablo_id, s3_key from tablo_files tf WHERE NOT EXISTS (SELECT 1 FROM tablos t WHERE t.id = tf.tablo_id). Include a comment line above the annotation explaining its purpose ("Find tablo_files rows whose owning tablo no longer exists. Used by the orphan-file cleanup worker (Phase 6 WORK-02)."). Use the alias "tf" for tablo_files per the NOT EXISTS subquery.

    Step 3 — regenerate sqlc:
      cd backend && just generate
    Verify the generated file at backend/internal/db/sqlc/files.sql.go contains "ListOrphanFiles" and "ListOrphanFilesRow".
  </action>
  <verify>
    <automated>cd /Users/arthur.belleville/Documents/perso/projects/xtablo-source/backend && grep -c "riverqueue/river" go.mod && grep -c "ListOrphanFiles" internal/db/sqlc/files.sql.go && go build ./...</automated>
  </verify>
  <done>
    go.mod contains github.com/riverqueue/river (grep -c returns >= 1). internal/db/sqlc/files.sql.go contains ListOrphanFiles. go build ./... exits 0.
  </done>
  <acceptance_criteria>
    - `grep "riverqueue/river" backend/go.mod` returns at least two lines (river + riverpgxv5)
    - `grep "riverqueue/river/riverdriver/riverpgxv5" backend/go.mod` returns a line
    - `grep "ListOrphanFiles" backend/internal/db/sqlc/files.sql.go` returns lines for both function and row type
    - `grep "ListOrphanFilesRow" backend/internal/db/sqlc/files.sql.go` confirms the row struct exists
    - `cd backend && go build ./...` exits 0
  </acceptance_criteria>
</task>

<task type="auto" tdd="true">
  <name>Task 2: Create internal/jobs/ package — HeartbeatWorker + OrphanCleanupWorker + SlogErrorHandler + unit tests</name>
  <files>
    backend/internal/jobs/heartbeat.go,
    backend/internal/jobs/orphan_cleanup.go,
    backend/internal/jobs/error_handler.go,
    backend/internal/jobs/heartbeat_test.go,
    backend/internal/jobs/orphan_cleanup_test.go,
    backend/internal/jobs/error_handler_test.go
  </files>
  <read_first>
    - backend/internal/auth/session.go — struct definition with injected dep pattern to replicate
    - backend/internal/files/store.go — FileStorer interface (lines 17-22) used by OrphanCleanupWorker
    - backend/internal/db/sqlc/files.sql.go — confirm ListOrphanFilesRow and DeleteTabloFileParams field names after regeneration
    - backend/internal/db/sqlc/db.go — confirm sqlc.New signature (takes DBTX)
    - .planning/phases/06-background-worker/06-RESEARCH.md — Pattern 1 (job structs), Pattern 4 (SlogErrorHandler)
    - .planning/phases/06-background-worker/06-PATTERNS.md — Pattern assignments for all three files
  </read_first>
  <behavior>
    HeartbeatWorker:
    - HeartbeatArgs struct with no fields; Kind() string returns "heartbeat"
    - HeartbeatWorker struct embeds river.WorkerDefaults[HeartbeatArgs]
    - Work(ctx, job) calls slog.Info("worker heartbeat", "job_id", job.ID, "attempt", job.Attempt) and returns nil
    - TestHeartbeatWorker: construct HeartbeatWorker, call Work(ctx, job), assert no error

    OrphanCleanupWorker:
    - OrphanCleanupArgs struct with no fields; Kind() string returns "orphan_file_cleanup"
    - OrphanCleanupWorker struct embeds river.WorkerDefaults[OrphanCleanupArgs]; fields: pool *pgxpool.Pool, store files.FileStorer
    - NewOrphanCleanupWorker(pool *pgxpool.Pool, store files.FileStorer) *OrphanCleanupWorker constructor
    - Work(ctx, job): calls sqlc.New(w.pool).ListOrphanFiles(ctx); loops over results; deletes S3 object first (w.store.Delete(ctx, f.S3Key)), then calls sqlc.New(w.pool).DeleteTabloFile(ctx, sqlc.DeleteTabloFileParams{ID: f.ID, TabloID: f.TabloID}) for each; increments deleted/errCount counters; logs slog.Info("orphan cleanup complete", "orphans_found", len(orphans), "deleted", deleted, "errors", errCount); returns nil (does not fail the job for partial errors — logs them)
    - TestOrphanCleanupWorker_NoOrphans: pure unit test using a mock DB querier (implement a mockQuerier that satisfies the sqlc Querier interface, returning an empty []ListOrphanFilesRow) and a mock FileStorer; confirm Work returns nil
    - TestOrphanCleanupWorker_DeletesOrphan: pure unit test using a mock DB querier that returns one fake ListOrphanFilesRow{ID: someUUID, TabloID: otherUUID, S3Key: "orphan-key"} and a mock FileStorer that records Delete calls; run Work and assert (a) mock FileStorer.Delete was called with "orphan-key", (b) mock querier.DeleteTabloFile was called with the matching ID and TabloID, (c) Work returns nil. Do NOT use a real test DB for this test — the FK constraint on tablo_files.tablo_id references tablos(id), making a real-DB orphan insert unreliable without complex setup. The mock approach gives deterministic coverage of the loop logic without DB dependency.

    SlogErrorHandler:
    - SlogErrorHandler struct with no fields
    - HandleError(ctx, job *rivertype.JobRow, err error) *river.ErrorHandlerResult: calls slog.Error("job error", "job_id", job.ID, "job_kind", job.Kind, "attempt", job.Attempt, "max_attempts", job.MaxAttempts, "err", err); returns nil
    - HandlePanic(ctx, job *rivertype.JobRow, panicVal any, trace string) *river.ErrorHandlerResult: calls slog.Error("job panic", "job_id", job.ID, "job_kind", job.Kind, "panic", panicVal, "trace", trace); returns nil
    - TestSlogErrorHandler: construct handler, call HandleError with a fake JobRow and error, confirm no panic and return value is nil
  </behavior>
  <action>
    Create three Go source files under backend/internal/jobs/ (package declaration: "package jobs").

    heartbeat.go: Define HeartbeatArgs (no fields) with Kind() returning "heartbeat". Define HeartbeatWorker embedding river.WorkerDefaults[HeartbeatArgs] with Work method logging slog.Info("worker heartbeat", "job_id", job.ID, "attempt", job.Attempt) then returning nil. Import "github.com/riverqueue/river" and "log/slog" and "context".

    orphan_cleanup.go: Define OrphanCleanupArgs (no fields) with Kind() returning "orphan_file_cleanup". Define OrphanCleanupWorker struct with fields pool *pgxpool.Pool and store files.FileStorer; embed river.WorkerDefaults[OrphanCleanupArgs]. Implement NewOrphanCleanupWorker(pool *pgxpool.Pool, store files.FileStorer) *OrphanCleanupWorker. Implement Work(ctx, job) per the behavior spec: ListOrphanFiles → loop (S3 delete first, then DB delete, counting deleted/errCount) → log summary → return nil. On individual S3 or DB errors in the loop, log slog.Error with relevant fields (s3_key or file_id) and increment errCount; continue to next item. Import "backend/internal/db/sqlc", "backend/internal/files", "github.com/jackc/pgx/v5/pgxpool", "github.com/riverqueue/river", "log/slog", "context", "fmt".

    error_handler.go: Define SlogErrorHandler struct (no fields). Implement HandleError and HandlePanic per the behavior spec. Import "context", "github.com/riverqueue/river", "github.com/riverqueue/river/rivertype", "log/slog".

    For the test files: heartbeat_test.go and error_handler_test.go use pure unit tests (no DB needed). orphan_cleanup_test.go uses pure unit tests with mock implementations — do NOT use a real test DB. Define a mockQuerier struct in the test file implementing the methods ListOrphanFiles(ctx) and DeleteTabloFile(ctx, params) used by OrphanCleanupWorker.Work; the mock records calls so tests can assert on them. Define a mockFileStorer struct implementing files.FileStorer with a Delete method that records the keys it was called with. TestOrphanCleanupWorker_NoOrphans: mockQuerier returns empty slice, assert Work returns nil. TestOrphanCleanupWorker_DeletesOrphan: mockQuerier returns one row, assert Delete was called with the correct S3 key and DeleteTabloFile was called with the correct params, assert Work returns nil. The verify command is: go test ./internal/jobs/... -run TestOrphanCleanupWorker -v -count=1.

    After writing all files: cd backend && go test ./internal/jobs/...
  </action>
  <verify>
    <automated>cd /Users/arthur.belleville/Documents/perso/projects/xtablo-source/backend && go build ./internal/jobs/... && go test ./internal/jobs/... -v -count=1</automated>
  </verify>
  <done>
    go build ./internal/jobs/... exits 0. go test ./internal/jobs/... passes with no skips required (all tests are pure unit tests with mocks). All three files exist with correct package declarations and exported types.
  </done>
  <acceptance_criteria>
    - `cd backend && go build ./internal/jobs/...` exits 0
    - `grep "HeartbeatArgs" backend/internal/jobs/heartbeat.go` returns a line
    - `grep "HeartbeatWorker" backend/internal/jobs/heartbeat.go` returns a line with WorkerDefaults embedding
    - `grep "OrphanCleanupWorker" backend/internal/jobs/orphan_cleanup.go` returns a line
    - `grep "NewOrphanCleanupWorker" backend/internal/jobs/orphan_cleanup.go` returns a line
    - `grep "SlogErrorHandler" backend/internal/jobs/error_handler.go` returns a line
    - `grep "HandleError" backend/internal/jobs/error_handler.go` returns a line
    - `grep "HandlePanic" backend/internal/jobs/error_handler.go` returns a line
    - `cd backend && go test ./internal/jobs/... -v -count=1` exits 0 (all tests pass — no DB required)
    - `cd backend && go build ./...` exits 0 (whole module compiles)
  </acceptance_criteria>
</task>

</tasks>

<threat_model>
## Trust Boundaries

| Boundary | Description |
|----------|-------------|
| Worker process → Postgres | Worker reads/writes job rows and tablo_files; all operations use the project pgxpool with the same Postgres credentials as cmd/web |
| Worker process → S3/MinIO | Worker deletes S3 objects using credentials from env vars; no user-supplied input reaches these calls in Phase 6 |

## STRIDE Threat Register

| Threat ID | Category | Component | Disposition | Mitigation Plan |
|-----------|----------|-----------|-------------|-----------------|
| T-06-01 | Tampering | OrphanCleanupWorker — river_job row args | accept | Phase 6 periodic jobs use empty args structs (HeartbeatArgs{}, OrphanCleanupArgs{}); no user-supplied data is deserialized from job args; tampering with river_job rows requires DB access, which is already a full compromise |
| T-06-02 | Information Disclosure | S3 credentials in env vars | mitigate | Credentials read from env vars (never hardcoded); .env.example documents required vars without values; go.sum pins river to v0.37.0 to prevent supply-chain substitution |
| T-06-03 | Elevation of Privilege | Worker binary running as root | transfer | Phase 7 scope — Dockerfile non-root user is a Phase 7 DEPLOY requirement; documented in README as a known gap |
| T-06-04 | Denial of Service | MaxWorkers exceeding pool MaxConns | mitigate | MaxWorkers: 10 matches db.NewPool MaxConns: 10; documented in river.Config comment; never exceeds pool capacity |
</threat_model>

<verification>
After Plan 01 is complete:
- `cd backend && go build ./...` exits 0
- `cd backend && go test ./internal/jobs/... -v` passes (all unit tests with mocks — no DB required)
- `grep "riverqueue/river" backend/go.mod` shows at least 2 lines (river + riverpgxv5)
- `grep "ListOrphanFiles" backend/internal/db/sqlc/files.sql.go` returns at least 1 line
- `grep "HeartbeatWorker" backend/internal/jobs/heartbeat.go` returns 1 line
- `grep "SlogErrorHandler" backend/internal/jobs/error_handler.go` returns 1 line
</verification>

<success_criteria>
- river importable from internal/jobs/ without compile errors
- HeartbeatWorker, OrphanCleanupWorker, SlogErrorHandler defined with correct signatures
- ListOrphanFiles sqlc query generated with ListOrphanFilesRow type
- Unit tests for all three job types pass (pure mock-based, no DB dependency)
- go build ./... exits 0 — whole module compiles clean
</success_criteria>

<output>
After completion, create `.planning/phases/06-background-worker/06-01-SUMMARY.md`
</output>
