> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cfosilvia.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Transaction categorization

# Transaction categorization on Silvia Analyst

Status: phase 1 (shadow) in this PR, off by default.

Plaid's category mapping stays the fallback. User rules and hand edits always
win. Silvia Analyst fills in what those miss, and every user fix becomes a
training label.

## Non-invasive by design

* Two new tables, `txcat_predictions` and `txcat_labels`. Nothing existing gets
  a new column, trigger or foreign key.
* Ids that point at existing tables (`transaction_id`, category uuids) are soft
  references with no FK, so existing tables never depend on txcat. Dropping the
  two tables removes the feature.
* Phase 1 only reads existing tables. Users see nothing new.
* The tables live in `public` with a `txcat_` prefix rather than their own
  schema, because a new schema has to be exposed in the Supabase API settings
  before supabase-js can reach it.

## Pipeline

```mermaid theme={null}
flowchart TB
  A[Plaid / SnapTrade sync] --> B[(transactions, unchanged)]
  B -- read only --> J[Nightly /api/cron/txcat-shadow]
  J --> S{rule_id set or edited by hand?}
  S -- yes --> K[skip: the user decided]
  S -- no --> E[Silvia Analyst picks from the user's categories]
  E --> P[(txcat_predictions)]
  B --> U[Silvia app, unchanged]
  U --> X[User edits a category]
  X --> B
  B -- 7 days later: where did the category end up? --> L[(txcat_labels)]
  L --> R[Compare model vs Plaid mapping on user decisions]
  R --> G{Model wins?}
  G -- yes --> W[Phase 2: write category_uuid on untouched rows]
  G -- no --> T[Fine-tune on labels, try again]
```

## Tables

```mermaid theme={null}
erDiagram
  txcat_predictions ||--o| txcat_labels : "prediction_id"
  txcat_predictions }o..|| transactions : "transaction_id (soft)"
  txcat_predictions }o..o| transaction_categories : "predicted / baseline uuid (soft)"
  txcat_labels }o..o| transaction_categories : "final_category_uuid (soft)"

  txcat_predictions {
    uuid id PK
    text transaction_id "soft ref"
    text clerk_id
    text model "served name, e.g. quant-analyst"
    text model_version "TXCAT_MODEL_VERSION"
    uuid predicted_category_uuid "null = abstained"
    numeric confidence "0 to 1"
    uuid baseline_category_uuid "category at prediction time"
    integer latency_ms
    timestamptz predicted_at
  }
  txcat_labels {
    uuid id PK
    uuid prediction_id FK
    text transaction_id "soft ref"
    text clerk_id
    uuid final_category_uuid
    text label_kind "user_edit | user_rule | untouched | gone"
    timestamptz observed_at
  }
```

## How it maps onto existing tables

| Existing | Used for | Access |
| - | - | - |
| `transactions` | name, merchant, amount, channel as model input; `category_uuid` as baseline and label | read |
| `transactions.auto_categorized` | `false` = hand edit (see migration `20260811_01`), skipped and labeled `user_edit` | read |
| `transactions.rule_id` | set = a user rule decided, skipped and labeled `user_rule` | read |
| `accounts` | type and subtype as model input | read |
| `personal_finance_categories` | Plaid's primary and detailed label, given to the model as a hint | read |
| `transaction_categories` + `transaction_groups` | the list the model picks from: shared rows (`clerk_id` null) plus the user's own, same as the app shows | read |

The model sees numbered categories and numbered transactions and answers with
numbers. It never sees or writes a uuid, and a number outside the list is
dropped.

## Turning it on

1. Apply `supabase/migrations/20260925_02_txcat_shadow.sql`.
2. Set `TXCAT_SHADOW_ENABLED=true` (and optionally `TXCAT_SHADOW_MAX_ROWS`,
   default 500, at most 1000) in the environment.
3. Set `TXCAT_MODEL_VERSION` to the model behind the endpoint (default
   `silvia-analyst-1.0`). The served name (`quant-analyst`) is the same across
   versions, so this is the only thing that tells them apart. Each version
   predicts once per transaction, so a new version re-predicts recent rows and
   two versions can be compared on the same transactions.
4. The cron runs daily at 07:45 UTC. It needs `QUANT_ANALYST_MODAL_URL` and
   `QUANT_ANALYST_MODAL_KEY`; without them it returns `skipped`.

## Reading the results

After a week, labels start landing. The first question is accuracy on user
decisions, model vs the Plaid mapping:

```sql theme={null}
select
  count(*) as n,
  avg((p.predicted_category_uuid = l.final_category_uuid)::int) as model_acc,
  avg((p.baseline_category_uuid = l.final_category_uuid)::int) as plaid_acc,
  p.model_version
from txcat_labels l
join txcat_predictions p on p.id = l.prediction_id
where l.label_kind in ('user_edit', 'user_rule')
group by p.model_version;
```

## Phase 2 (not in this PR)

Only if the model beats the Plaid mapping on user decisions: write
`transactions.category_uuid` for rows with no `rule_id`, `auto_categorized`
not false, and confidence above a cutoff picked from the phase 1 data, and
record which rows were written so the change can be rolled back.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.