Plan: Generalized Job System with OCR + Translation Services

Goals

1. Generalize the job system so the backend can post tts, ocr, and translation jobs that worker services pick up, process, and produce output for.
2. OCR: create a new OCR'd PDF saved next to the original on disk, populate documents.content (extracted text) and documents.ocr_pdf_storage_key.
3. Translation: add columns to documents for the translated text and the source/target languages.

Decisions (confirmed)

• OCR-extracted text is stored in documents.content (replaces the stubbed "Extract text" button).
• Full migration: replace tts_jobs with a generic jobs table. The app is not in production; migrations are rewritten in place, no backwards compatibility.
• Translation engine: local facebook/nllb-200-distilled-600M via transformers/torch (no API keys), FLORES-200 codes (already used by the app).
• OCR fast path: extract text with pypdf; only run Tesseract OCR (and store the new PDF) when no text layer exists or "Force OCR" is checked.

Architecture

Extends the existing pattern (Postgres-backed queue, Go worker goroutine in the backend, stateless Python HTTP services):

Frontend ── POST job ──> Go backend ── INSERT ──> jobs table (generic: tts | ocr | translation)
                            │  worker goroutines (one per type, SKIP LOCKED claim, 2s poll)
                            ├──> tts:8000        (existing, SSE)
                            ├──> ocr:8000        (new, SSE result w/ PDF+text)
                            └──> translator:8000 (new, SSE progress + result)
                            └── on completion, publish outputs to documents table / /data disk

1. Database (rewrite migrations in place — dev DBs must be reset)

Rewrite backend/db/migrations/009_create_tts_jobs.*.sql → 009_create_jobs_table:

CREATE TABLE jobs (
    job_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    document_id UUID REFERENCES documents(document_id) ON DELETE CASCADE,
    job_type TEXT NOT NULL CHECK (job_type IN ('tts','ocr','translation')),
    name TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'queued',          -- queued | running | completed | failed
    params JSONB NOT NULL DEFAULT '{}',             -- type-specific input
    result JSONB,                                    -- type-specific output metadata
    progress DOUBLE PRECISION NOT NULL DEFAULT 0,    -- 0..1, updated from SSE progress
    error TEXT,
    created_at/updated_at/started_at/completed_at TIMESTAMPTZ ...,
    UNIQUE (user_id, job_type, name)
);
CREATE INDEX jobs_status_type_created_at_idx ON jobs (status, job_type, created_at);

CREATE TABLE tts_job_segments (...)  -- unchanged, but FK → jobs(job_id) ON DELETE CASCADE

• Blank out migration 011 (renamed 011_add_document_id_to_jobs, empty like the existing 004 — document_id is now in 009).
• Name convention per document job: project-<docID> — no collision across types thanks to job_type in the unique constraint.

New 013_add_translation_to_documents:

ALTER TABLE documents
    ADD COLUMN translated_content TEXT,
    ADD COLUMN translation_source_language VARCHAR(50),   -- FLORES-200 codes (dan_Latn …)
    ADD COLUMN translation_target_language VARCHAR(50);

OCR text goes into existing documents.content; the OCR'd PDF key into existing ocr_pdf_storage_key.

2. Backend (Go)

Models — new internal/models/job.go: Job struct with JobType constants, Status constants reused from tts_job.go, typed param accessors (TTS: text/language/instruct/voice/speed; OCR: language/force; Translation: source/target lang).

Store — internal/storage/postgres/job_store.go replaces tts_job_store.go:

• CreateJob, GetJobByName(userID, type, name), GetJobByID, ListJobs(userID, type)
• ClaimNextQueuedJob(jobType) — same FOR UPDATE SKIP LOCKED transaction
• RequeueStaleRunningJobs(30min) — all types
• MarkJobCompleted/Failed, UpdateJobProgress, SetJobResult
• Segment functions (InsertJobSegment, GetJobSegments) and ReplaceSpeechFromJobSegments move over unchanged.

Worker — new internal/jobs/ package; Run(ctx) starts one polling goroutine per job type (a slow OCR never blocks TTS):

• jobs/tts.go — port of internal/tts/worker.go processJob (params now from JSONB; segments/SSE/sound_snippets logic identical). Move the generic SSE parser from internal/tts/sse.go into jobs/sse.go (keep TTS audio decode in tts.go).
• jobs/ocr.go — new processor:
  1. Load document; fail if original_pdf_storage_key is NULL.
  2. Read /data/{user_id}/{key}.pdf, multipart POST to OCR service (fields: language tesseract code, force), parse SSE.
  3. On result event: if ocr_performed, save PDF bytes via new storage key (files package pattern from pdf.go), set ocr_pdf_storage_key (replacing old file if present); always set documents.content = text.
  4. Mark completed with result {ocr_performed, pages}.
• jobs/translation.go — new processor: read documents.content (fail if empty), POST {text, src_lang, tgt_lang} to translator service, update progress from SSE progress events, on result set translated_content + both language columns, mark completed.
• All processors use context.WithoutCancel and long/no HTTP client timeouts (like TTS today). Service URLs from env: OCR_SERVICE_URL (default http://ocr:8000), TRANSLATION_SERVICE_URL (default http://translator:8000).
• main.go: start jobs.NewWorker(store).Run(ctx) replacing the TTS worker startup.

Handlers:

• Project-scoped TTS job endpoints: GET /project/text-to-speech/:projectID returns job status, GET /project/text-to-speech/:projectID/segments returns the published speech snippets (was GetProjectSpeech), plus existing POST/PUT/DELETE. The generic free-text /text-to-speech/jobs* endpoints were removed (only the AudioTest dev page used them; it was removed too), leaving a consistent project-scoped job API across all three types.
• New ocr.go handler: POST /user/project/ocr/:projectID {force?, language?} (language defaults from document.language via new FLORES→tesseract map: eng_Latn→eng, dan_Latn→dan, …) and GET /user/project/ocr/:projectID (job status). Dedup/requeue semantics copied from GenProjectTextToSpeech.
• New translation.go handler: POST /user/project/translation/:projectID {target_language, source_language?} (source defaults document.language), GET, PUT (force re-translate), DELETE (clear translated columns).
• New GET /user/project/ocr-pdf/:documentID — serves the OCR'd PDF (404 when none), mirroring GetPDF.
• Document store additions: SetDocumentContent, SetOCRPDFStorageKey (non-if-empty variant), SetDocumentTranslation(content, from, to), ClearDocumentTranslation. Pre-existing bugs in PutDocument (missing commas in the ON CONFLICT clause) and CreateDocument/DeleteDocument (using Query instead of Exec) were fixed while rewriting the store.
• New GET /user/project/ocr-pdf/:documentID — serves the OCR'd PDF (404 when none), mirroring GetPDF.

3. New Python services

ocr/ (lightweight, CPU-only; port of pdfutil.py with fixes):

• POST /ocr — multipart in, SSE out. Fast path: pypdf text extraction; if empty text or force=true: ocrmypdf.ocr(in, out, deskew=True, language=lang, force_ocr=force) (force_ocr avoids ocrmypdf's text-layer error that silently breaks the reference implementation). SSE events: {"type":"progress", ...} keepalives, {"type":"result", "text", "pdf_b64", "ocr_performed"}, {"type":"error", ...}.
• Single-concurrency lock like tts/main.py.
• Dockerfile: python 3.12-slim + tesseract-ocr, tesseract-ocr-eng, tesseract-ocr-dan, ghostscript, pip: ocrmypdf, pypdf, fastapi, uvicorn (+ .dev variant).

translator/ (mirrors tts structure incl. profile variants):

• POST /translate — JSON {text, src_lang, tgt_lang} in, SSE out. Port of translation.py: NLLB pipeline (facebook/nllb-200-distilled-600M, FLORES-200 codes pass through directly), model loaded at startup (not import time), chunking improved: split on newlines + sentences, force-split oversized chunks at token limit.
• SSE: {"type":"progress","done":n,"total":m} per chunk + keepalives, {"type":"result","text"}, error events.
• Dockerfile cpu/cuda/rocm variants; gunicorn 1 worker + SSE keepalives so long jobs survive timeouts.
• compose/translator-profiles.yaml mirroring compose/tts-profiles.yaml; HF cache volume ../.translator-cache:/cache/huggingface.

4. Compose & CI

• docker-compose.yaml: add ocr (always on, backend network) and translator (default cpu template, cuda/rocm optional profiles).
• .env.example: nothing new required (no API keys — all local models).
• .forgejo/workflows/docker-build.yaml: build/push 5 images instead of 3 (add ocr, translator).
• Add short ocr/README.md + translator/README.md following tts/README.md style.

5. Frontend

• Text.tsx: wire the stubbed "Extract text" → POST /user/project/ocr/{documentId} {force: checkboxValue} → poll job (extract pollJobUntilDone from Speech.tsx into a shared util) → refetch document (content now populated). Show link to OCR PDF (/user/project/ocr-pdf/{documentId}) when ocr_pdf_storage_key is set. Note: OCR overwrites content — add a confirm prompt if content is non-empty. "Force Optical Character Recognition" checkbox becomes functional.
• New Translate.tsx step in the project flow (between Text and Speech): source language (defaults document.language), target language dropdown (curated FLORES list, dan_Latn/eng_Latn + common), translate button → poll → display translated_content, from/to languages; re-translate and clear actions.
• types/document.ts / App.tsx: add the three new nullable fields.
• Speech.tsx updated: polls project-scoped job status (GET /project/text-to-speech/:id) instead of the removed name-based endpoint. Preview.tsx now reads speech from /segments. AudioTest page removed (only consumer of the generic free-text TTS endpoints).

6. Implementation order

1. Migrations 009-rewrite, 011-blank, 013-new + models + job_store + port TTS worker/handlers to generic table — verify TTS still works end-to-end before proceeding.
2. OCR service (Python) + jobs/ocr.go + endpoints + compose.
3. Translator service + jobs/translation.go + endpoints + compose + profiles.
4. Frontend (Text.tsx wiring, Translate.tsx, shared poll util, types).
5. CI images, READMEs.

7. Verification

• Wipe .database volume, docker compose up (dev override), run full flow: create doc → upload PDF → extract text (fast path + forced OCR) → verify OCR PDF at /data/{user}/{key}.pdf + content/ocr_pdf_storage_key set → translate da→en → verify columns → TTS → playback.
• gofmt + Go build + tsc frontend build.

Risks / notes

• Dev databases must be recreated (migrations rewritten) — confirmed OK.
• NLLB first run downloads ~2.4 GB; CPU translation of long docs is slow (progress column + SSE mitigate UX).
• OCR SSE result carries the PDF base64 (≤ ~27 MB for the 20 MB upload cap) — fine in practice.
• Danish OCR now gets the dan tesseract pack (the reference silently OCR'd Danish with English only).