Bulk categorization: background worker + quicker calibration

[antonio-ivanovski]

Problem

Bulk categorization is synchronous with all state in browser / in-process memory (calibrate → preview → bulkUpdateCategories). Long AI calls block the user, are lost on reload/timeout, and hold state only in memory.

Decisions

• Trigger: Manual only — admin clicks "Categorize all" to enqueue job. No cron/auto.
• Naive pre-pass: Suggest only, no auto-apply.
• Completion: Notification → dedicated review/merge UI.
• Sync path: Removed entirely. No fallback wizard; all bulk work via persisted background job.

Proposal

1. Quicker calibration

• Run existing naive suggestCategoryFromTitle (dictionary + naive search on samples) before AI to pre-fill obvious suggestions instantly in calibration.
• Use smaller AI batches with fewer samples per round for faster feedback / fewer tokens. Naive suggestions are suggest-only — user still confirms.

2. Async bulk categorization

• Replace in-process flow with pg-boss background job. Enqueued manually per group, processes eligible general expenses in batches with server-persisted state (retriable, survives reload).
• Job never auto-writes. On completion it notifies user and exposes result for review.

UX (high-level)

1. Admin opens Bulk Categorize → sees naive suggestions instantly on sample, calibrates few rows if needed.
2. Clicks "Categorize all" → job enqueued, wizard closes. Group shows "Categorization running in background" state.
3. On completion: in-app (+ email per preferences) notification → links to dedicated review page (list: current vs suggested, edit/exclude per row, bulk accept) → explicit Confirm to call bulkUpdateCategories.

Out of scope

Job schema, batch sizing, retry/backoff, concurrency limits — to be decided in implementation.

Acceptance

• Calibration uses naive pre-pass as suggest-only
• Smaller AI batches
• Manual-only background batched job with DB-persisted state, in-process state removed
• Notification + dedicated merge/review UI before apply

[antonio-ivanovski]

Hey @kdelalic — would you be willing to take on #96?

You already dug into this area fixing #89 "[Bug] Bulk categorization calibration rejects model responses", so you're already in the weeds on the calibration / bulk flow — you'd be an ideal candidate to carry this next step (moving it to a manual-only background worker with persisted state + the quicker naive suggestCategoryFromTitle pre-pass and dedicated merge/review UI).

Happy to provide any extra context on the current sync flow / pg-boss setup if you want to pick it up. No pressure if timing doesn't work — just let us know!

[pullfrog]

Maintainer evaluation — issue #96

I reviewed the current bulk-categorization implementation (apps/api/src/lib/ai/categorize.ts, apps/api/src/lib/api/category-bulk.ts, the ai.bulkCategorize router, the frontend wizard, the worker/jobs packages, and handoff/bulk-categorizer-spike.md) to ground this evaluation.

What is being requested

The real ask is: stop treating bulk categorization as a blocking, single-sitting task. Today the whole flow (calibrate → preview → bulkUpdateCategories) runs synchronously inside one tRPC request, holding up to ~20 sequential AI calls (~500 expenses in chunks of 25) on a single HTTP connection, with all wizard state living in a client-side reducer. Requests can blow past comfortable timeouts and everything is lost on reload. The request is to make bulk categorization (a) much cheaper/faster to calibrate by pre-filling obvious matches from the existing naive title matcher before any AI call, and (b) durable: a manual-only background job that survives reload, notifies the admin when done, and gates every write behind an explicit review/merge screen. The "sync path removed entirely" line is the answer to the current flow's fragility — the underlying need is resumability + a human safety gate, not merely moving where the code runs.

Ambiguities / missing context

• Sync/async boundary. The proposals contradict each other on this. "Quicker calibration" is described as an interactive, instant pre-pass (admin sees naive suggestions, calibrates a few rows, then clicks "Categorize all"), yet "Sync path removed entirely" reads as if calibration itself also goes. Whether calibration stays a fast synchronous step (and only the full preview/apply becomes a job) determines most of the work. The existing spike handoff recommends exactly that split (calibration sync, conversion async), so this seems like the intended reading — but it should be stated.
• What exactly is enqueued. The target set could be a snapshot taken at enqueue time or re-derived at run time, and it's unspecified whether the admin's calibration priors accompany the job. This drives the data model, and the handoff already flags snapshot semantics as an open decision.
• Rate limiting. Bulk AI calls are gated by an ai-bulk per-account quota (20/hr). A background worker bypasses the request-path limiter unless it is re-imposed at enqueue or per-chunk — otherwise a single 500-expense job can exceed the whole hourly quota. Not addressed.
• Job persistence/cadence. "Job schema, batch sizing, retry/backoff, concurrency limits" are explicitly out of scope, but the review/merge UI needs a durable result store (a status query must read from somewhere), a retention/cleanup story, and a decision on one-job-per-group concurrency. These aren't speculative — they're load-bearing for the acceptance criteria.
• Notification semantics. "in-app (+ email per preferences)" — there's an activity/notification pipeline and EXPENSE_CATEGORIES_BULK_UPDATED is already a mapped event, but whether completion is a new event type/category with its own delivery prefs or reuses existing ones is unstated.
• Naive pre-pass scope. The naive matcher is described as suggest-only for calibration. Whether it also short-circuits the full preview (skip AI for confident hits) is left open — the "fewer tokens" motivation implies it might.

Technical feasibility

Feasible with caveats, and unusually well-supported by existing infrastructure:

• A production pg-boss worker already exists (packages/jobs registry + apps/worker handlers) for recurring expenses, notifications, and budgets — the durable-job pattern (typed payloads, DLQs, leases) is established, just under-utilized for AI work.
• The naive pre-pass already exists and is tested (suggestCategoryFromTitle in packages/domain/src/category-search/), and already powers the single-expense form — calibration speedup reuses a proven component.
• The apply side is already durable: bulkUpdateExpenseCategories runs one transaction with optimistic row updates, logs an activity, and enqueues notifications/budget evaluation — a background worker can call it near-unchanged.
• Notable new patterns: running LLM calls from the worker (today AI is only ever called in the request lifecycle), a status-query + polling surface (handoff explicitly notes no polling/SSE exists today), and new persisted job/result rows (a schema migration).

Blast radius

Broad — API/tRPC (new enqueue/status/review procedures, calibrate rework, rate-limit handling), data model (new persisted job + results), worker (new handler + AI-in-worker), frontend (wizard restructure, new async review surface), and notifications (completion event). Both auth/permissions (admin-only enqueue/review, archived-group handling, viewer-redaction via redactGroupForViewer semantics) and infrastructure are touched.

Existing behavior and interactions

This issue formalizes what handoff/bulk-categorizer-spike.md already plans ("preview + save should move to a durable background job"). Nothing conflicts; it mostly adds the calibration speedup and removes the sync fallback that the handoff had kept as an option. Risk is contained by the feature being behind PUBLIC_ENABLE_BULK_CATEGORIZE (default off), frontend-gated, and already marked "undergoing rework — not for external consumption" in the OpenAPI generator, so removing sync procedures is low-user-surfacing.

Important consequences and edge cases

• Data integrity: A snapshot-based job staleness vs. re-derived set drift on concurrent edits; the optimistic per-row apply already skips stale rows safely, and the naive pre-pass stays suggest-only, so no silent writes — good.
• Concurrency: Unbounded per-group jobs can race over the same general pool; a singleton key (pattern already exists for budget evaluation) is the natural guard.
• Performance/tokens: "Smaller AI batches" reduces per-round tokens, but should be benchmarked against calibration getting more rounds — a genuine trade-off to validate.
• Compliance/privacy: titles are sent to the AI provider and would now be persisted in job results asynchronously; retention/cleanup and admin-only (not public-view) exposure need explicit handling.
• Backcompat/migration: a new table/cleanup job is required; existing flag-gated behavior limits breakage.

Scope assessment

Large / cross-cutting. It spans API, DB, worker, notifications, and two separate frontend surfaces (calibration + review page), and introduces two new patterns (AI from a worker; async status/polling). Not small and not localized, though almost every piece builds on existing machinery.

Open questions

1. Is calibration interactive-sync (with only the full preview/apply async), as the handoff recommends — or truly fully async?
2. Is the job target a snapshot (with calibration priors) or re-derived at run time, and what happens to new eligible expenses mid-run?
3. How are background AI calls rate-limited — the same ai-bulk quota, enforced at enqueue?
4. Where and for how long are job results persisted (schema + retention)?
5. One active job per group, or allow overlap?
6. Is completion a new notification event/category, and where does the "in-app" notification live?

Maintainer summary

1. What it's really asking for: Make bulk categorization non-blocking and resumable — move the expensive multi-call preview/apply into a durable, manual-only background job with persisted state, a completion notification, and an explicit review gate before any write; and make calibration instant + cheaper by pre-filling obvious matches from the existing naive title matcher.
2. Feasibility: Yes — the pg-boss worker, notification pipeline, naive matcher, and transactional apply already exist; the genuinely new pieces are AI-in-worker calls, persisted job results, and a polling-based review surface.
3. Blast radius: Broad and cross-cutting (API, data model/migration, worker, notifications, web), but safely contained behind a default-off, non-external feature flag.
4. Most important unresolved question: the exact sync/async split and how the job captures its target set plus calibration priors (and how background AI calls are rate-limited) — the answers determine the data model, worker shape, quota semantics, and most of the frontend.

Task list (2/3 completed)

• Gather context on current bulk-categorize flow (calibrate/preview/apply, wizard state, worker/jobs, notifications, naive suggest)
• Write maintainer evaluation analysis for issue Bulk categorization: background worker + quicker calibration #96
• Post analysis to the issue

  ^{｜ Implement plan ➔ ｜ View workflow run ｜ via Pullfrog ｜ Using openai-compatible/deepseek-v4-flash-free ｜ 𝕏}