LerNota/openspec/changes/categoria/design.md

Context

LerNotaFiscal is a FastAPI app that ingests Brazilian invoices (PDF/image), extracts supplier_name, purchase_date, total_paid via extract_with_ai (app/ai_extraction.py, OpenAI chat.completions.create with vision, JSON mode), with an OCR/heuristic fallback in ingestion.py when AI is disabled or fails. Persistence is raw sqlite3 (app/database.py): no ORM, no migrations framework — the SCHEMA string is executed via executescript() on every connection open, so new tables/columns must be added additively (CREATE TABLE IF NOT EXISTS, ALTER TABLE ... ADD COLUMN guarded by a PRAGMA table_info check, since SQLite's ADD COLUMN IF NOT EXISTS isn't available in older syntax). The Dashboard (app/routes/dashboard_routes.py + app/templates/dashboard.html) currently shows KPIs, a monthly bar chart, and a top-8 supplier bar chart, with no filter controls; filtering by supplier/date exists only on the documents list page (documents_routes.py, via list_fiscal(conn, start, end, supplier)). There is currently no rate-limiting, retry, or caching layer anywhere in the codebase — AI safety today is a bare try/except around the extraction call.

This change adds category classification to fiscal documents, with AI as the primary classifier and a user-maintained keyword table as deterministic fallback, plus safety limits so the new AI call path cannot loop or blow up token spend.

Goals / Non-Goals

Goals:

• Persist a category per fiscal document, derived automatically (AI first, keyword fallback second, reserved "Não Encontrado" category otherwise — never left unclassified for documents that go through the flow).
• Let the categorization result be visible and filterable on the Dashboard.
• Guarantee the AI categorization call can run at most once per document per ingestion, with a supplier-level cache and a per-batch call ceiling, so it can never loop or run away on cost.
• Keep the categoria table simple and editable (id, categoria, palavra_chave) so non-technical users can extend keyword matching without code changes.

Non-Goals:

• No multi-category-per-document (one category per fiscal document in this change).
• No retraining/fine-tuning of the AI model; categorization uses the existing OpenAI client with a prompt, not a separate ML pipeline.
• No UI for bulk re-categorization of historical documents (existing documents predating this change simply show "Sem categoria" (categoria_id still NULL) until reprocessed; a manual "re-run categorization" action is out of scope unless trivial to add in tasks).
• No category hierarchy/subcategories.

Decisions

1. Categorization is a separate step after extraction, not baked into extract_with_ai's prompt. Rationale: extract_with_ai already has a fixed JSON schema for fornecedor/data/valor and is also exercised by the OCR-fallback path (where there is no AI call at all). Categorization needs its own safety limits (cache, per-batch ceiling) that are orthogonal to extraction, so it's cleaner as a new module app/categorization.py with a function like categorize_supplier(supplier_name: str, conn, batch_id) -> int, called from ingestion.py right before/at the same time fiscal_documents is created. The function always returns a valid categoria.id — a real category, or 1 ("Não Encontrado") when AI and keyword matching both fail — never None. Alternative considered: extend the extraction prompt to also return a category — rejected because it would apply AI categorization even when the caller only wants OCR fallback, and would entangle unrelated retry/cache logic with extraction.

2. categoria_id lives on fiscal_documents (nullable FK), not a separate join table. Rationale: one category per document is sufficient (see Non-Goals); a nullable integer FK matches the existing schema style (INTEGER PRIMARY KEY AUTOINCREMENT, snake_case). Added via ALTER TABLE fiscal_documents ADD COLUMN categoria_id INTEGER REFERENCES categoria(id), guarded by a PRAGMA table_info(fiscal_documents) check before running the ALTER, consistent with the additive-schema pattern already used in database.py. detected_documents is left unchanged since categorization only needs to run once a document is confirmed into fiscal_documents. The column stays nullable at the schema level purely so pre-existing rows (created before this migration) don't need a backfill — the categorization flow itself never writes NULL; it always writes either a real category id or the reserved 1 (see Decision 3).

3. Reserved row categoria.id = 1 = "Não Encontrado", auto-ensured, undeletable. Rationale: the user will populate the categoria table with their own categories/keywords, but the categorization flow needs a guaranteed, stable target to write to when AI and keyword matching both fail — it cannot depend on the user having remembered to seed anything. So the schema initialization step (the same executescript/setup path that creates the categoria table) also runs an idempotent INSERT OR IGNORE INTO categoria (id, categoria, palavra_chave) VALUES (1, 'Não Encontrado', ''), guaranteeing the row exists on first run without ever overwriting it if the user has since edited its categoria/palavra_chave values. delete_categoria rejects deletion when categoria_id == 1 (checked before running the delete). Alternative considered: have the app raise/500 if row 1 is missing and require the user to seed it manually first — rejected as too fragile; a one-line idempotent insert removes an entire class of "why isn't categorization working" support issues at negligible cost.

4. AI categorization is a lightweight, separate OpenAI call scoped to just the supplier name. Rationale: sending only supplier_name (plus the list of known category names as allowed options, sourced from distinct categoria.categoria values already in the table) keeps the prompt tiny and cheap compared to the vision-based extraction call, and lets us validate the AI's answer against a closed set of categories. If the AI returns a value outside that set (or the call fails), it's treated as "no usable category" and the flow falls to keyword fallback. Alternative considered: let the AI invent free-text categories — rejected, since ungoverned category proliferation would break the keyword table concept and the Dashboard grouping.

5. Keyword fallback matching is case-insensitive substring match of palavra_chave in supplier_name, first match wins, excluding the reserved row. Rationale: simplest possible rule that a non-technical user can reason about when populating the categoria table; matches the existing LIKE-based supplier filter style already in list_fiscal. Row order (id ASC, id != 1) determines precedence when multiple keywords could match — documented so admins can order entries if needed. Important: find_categoria_by_keyword must exclude id = 1 from its search. Since the reserved row's palavra_chave is normally an empty string, and an empty string is a substring of every value, including it in the ordered scan would make it match first (lowest id) on every call and defeat all real keyword matching. id = 1 is only ever reached as the final, explicit fallback in categorize_supplier when find_categoria_by_keyword (over id != 1) returns no match — never as a keyword-table hit itself. Alternative considered: token-based/fuzzy matching — deferred as unnecessary complexity for v1.

6. Safety limits are implemented as a small in-process module, not an external rate-limiter. Rationale: the app has no existing rate-limiting infrastructure and volume is modest (single-user/small-team invoice processing), so a simple in-memory cache dict (supplier_name -> categoria_id) plus a per-import-batch counter (reset at the start of each import_batches row) is sufficient and avoids new dependencies. Concretely:

• No retry: the categorization call wraps a single try/except, mirroring extract_with_ai's existing pattern — on any exception, treat as "no category from AI" and continue.
• Supplier cache: keyed by normalized (strip().upper()) supplier name, populated the first time a supplier is categorized (whether by AI, keyword, or the 1/"Não Encontrado" fallback) within the process lifetime; on cache hit, skip the AI call entirely and reuse the cached categoria_id. This is an in-memory dict for this change (module-level), acceptable since it degrades gracefully (worst case: re-categorize on process restart, still bounded by the per-batch limit).
• Per-batch ceiling: a configurable CATEGORIZATION_MAX_AI_CALLS_PER_BATCH (default e.g. 50) in app/config.py; a counter tied to the current import_batches.id is incremented per AI call and checked before each call; once reached, remaining documents in that batch use keyword fallback (then 1/"Não Encontrado" if no keyword matches) with no further AI calls.
• Logging: each skip (cache hit or limit reached) logs at INFO/DEBUG with supplier name and reason, using the existing logging setup. Alternative considered: persisting the cache in a DB table — deferred; in-memory is enough to satisfy "no infinite loop / no runaway cost" and keeps the change additive and low-risk.

7. Dashboard category breakdown reuses the existing chart/query pattern (supplier_totals-style function), plus a new category query param alongside start/end. Rationale: dashboard_routes.py already computes monthly_totals and supplier_totals from fiscal_documents; a new category_totals(conn, start, end) follows the same shape, and threading an optional category filter through the existing query functions (list_fiscal, monthly_totals, supplier_totals, new category_totals) is consistent with how supplier filtering already works. The category filter control is added to the Dashboard template, populated from list_categorias (which includes "Não Encontrado") plus a separate "Sem categoria" option for any legacy categoria_id IS NULL rows.

8. Categoria CRUD admin UI mirrors the existing "Documentos" list/form pattern exactly — new files, same conventions, no new UI framework. Rationale: the project already has an established, working pattern for list + create/edit + delete pages (documents_list.html + document_form.html, driven by documents_routes.py), all server-rendered Jinja2, plain HTML forms (POST-only, no JS/AJAX), CSRF via a hidden csrf_token field checked with auth.check_csrf, and a shared stylesheet (app/static/styles.css, classes like card, table, table-scroll, page-head, btn btn-primary/ghost/sm, linkbtn danger, field, filters). Reusing it exactly (rather than introducing a component library, modal dialogs, or AJAX) keeps the new admin screens visually and behaviorally indistinguishable from the rest of the app. Concretely:

• New router app/routes/categoria_routes.py (router = APIRouter()), imported and registered in app/main.py alongside the existing routers, following the same GET/POST route-pair convention used for documents:
  • GET /categorias — list (reuses card/table/table-scroll markup)
  • GET /categorias/new, POST /categorias/new — create form + handler
  • GET /categorias/{categoria_id}/edit, POST /categorias/{categoria_id}/edit — edit form + handler
  • POST /categorias/{categoria_id}/delete — delete (inline per-row form with onsubmit="return confirm(...)" and hidden csrf_token, exactly like the documents list's delete action)
• New templates app/templates/categorias_list.html and app/templates/categoria_form.html, both {% extends "base.html" %}, the form template reused for both create and edit via a mode variable ("new"/"edit") exactly as document_form.html does.
• app/database.py gains create_categoria(conn, categoria, palavra_chave), get_categoria(conn, categoria_id), update_categoria(conn, categoria_id, categoria, palavra_chave), delete_categoria(conn, categoria_id), alongside the already-planned list_categorias/find_categoria_by_keyword, matching the naming style of create_fiscal/update_fiscal.
• A new "Categorias" link is added to the nav bar in base.html (<nav class="nav">), alongside "Dashboard"/"Documentos", using the same active-link request.url.path.startswith(...) pattern.
• delete_categoria first checks categoria_id == 1 and rejects the delete (flash error, no DB change) per Decision 3's reserved-row protection. Otherwise, deleting a categoria that is referenced by existing fiscal_documents.categoria_id must not orphan those rows or raise a foreign-key error: delete_categoria explicitly runs UPDATE fiscal_documents SET categoria_id = 1 WHERE categoria_id = ? (reassigning affected documents to "Não Encontrado") before deleting the categoria row, in the same transaction, rather than relying on ON DELETE SET NULL — SQLite foreign key enforcement (PRAGMA foreign_keys) is off by default and this codebase does not currently enable it, so the app must enforce this itself. Alternative considered: leave orphaned documents' categoria_id as NULL on delete instead of reassigning to 1 — rejected for consistency: 1 ("Não Encontrado") is now the single canonical "not properly classified" bucket for anything the categorization flow processes or re-processes, while NULL is reserved strictly for legacy pre-migration rows never touched by this flow.

Risks / Trade-offs

• [Risk] In-memory supplier cache and per-batch counter are lost on process restart (e.g., app redeploy mid-batch) → could allow a few extra AI calls right after restart. Mitigation: the per-batch ceiling is intentionally conservative (default well below any reasonable per-import volume), and the ceiling still applies from the moment the process restarts, bounding worst-case cost; a persistent cache can be added later if needed.
• [Risk] AI-suggested categories drifting from the closed set (typos, casing) could be silently rejected, causing over-reliance on keyword fallback. Mitigation: normalize AI output (trim/case-fold) before validating against known categories; log rejected AI suggestions so gaps in the categoria table are visible for the admin to fix.
• [Risk] Existing fiscal documents (created before this change) will show "Sem categoria" (categoria_id NULL) until reprocessed, distinct from "Não Encontrado" (categoria_id = 1) used for documents the flow actively tried and failed to classify. Mitigation: acceptable for this change (Non-Goal: no bulk re-categorization); documented as an intentional distinction so it isn't mistaken for a bug; can be revisited if users need a backfill.
• [Risk] If the operator edits the reserved row's categoria/palavra_chave values (e.g. changes palavra_chave to something non-empty), it could start matching real suppliers via the keyword fallback path, diluting its meaning as a pure "nothing matched" bucket. Mitigation: document that id = 1 is reserved for "no match" semantics and its palavra_chave should normally stay empty; the app does not enforce this beyond the initial seed since the user is expected to manage the table's content.
• [Trade-off] Choosing "first keyword match wins" instead of "most specific match wins" is simpler but could misclassify if keywords overlap (e.g., "MERCADO" matching both a generic and specific entry). Documented as a known limitation; admins should keep keywords reasonably distinct.

Migration Plan

1. Add categoria table (with the idempotent id = 1 "Não Encontrado" seed insert) and fiscal_documents.categoria_id column via additive executescript/ALTER TABLE in app/database.py (guarded by existence checks so it's safe to run against existing databases).
2. Ship app/categorization.py with the AI-then-keyword-then-none flow and safety limits, wired into ingestion.py at document confirmation time.
3. Update dashboard_routes.py/dashboard.html to add category totals + filter.
4. Ship app/routes/categoria_routes.py + categorias_list.html/categoria_form.html for CRUD admin management of the categoria table, registered in app/main.py and linked from base.html's nav.
5. Deploy is a normal code + schema update; no data backfill required (existing rows simply have categoria_id = NULL).
6. Rollback: revert code; the added column/table can remain harmless if rolled back (nullable, unused), or be dropped manually if desired.

Open Questions

• Should users be able to manually override/edit a document's category from the documents list UI in this change, or is that a follow-up? (Assumed follow-up unless trivial.)