feat: Add app_category column to revenue transactions (TODO 3)

[2ndtlmining]

Problem

The revenue transaction table shows app_type (git/docker) but no higher-level category. A transaction for a Minecraft server shows as "docker" with no indication it is a gaming app. Users cannot filter or understand revenue by app category.

Requirements

Add an app_category column to revenue_transactions that classifies each transaction into Gaming, Crypto Nodes, WordPress, or uncategorized (NULL).

Functional Requirements

• New transactions get app_category set automatically during sync
• Historical transactions backfilled via admin endpoint
• Category visible in the Revenue Transaction Log table (between TYPE and TRANSACTION_ID)
• Category included in CSV export
• Searchable by category in the transaction filter

Categories (from existing CATEGORY_CONFIG)

• gaming — minecraft, palworld, enshrouded, valheim, satisfactory, etc.
• crypto — presearch, kaspa, alephium, bittensor, kadena, etc.
• wordpress — wp-nginx
• null — uncategorized (direct payments, unknown apps)

Architecture

Key Challenge

categorizeImage() needs a Docker image name (repotag), but during sync we only have the app name. Solution: expand globalSpecsCache to store name->repotag mapping, with fallback to matching app_name directly against category keywords.

Data Flow

Blockchain tx -> lookupAppName(hash) -> app_name
              -> lookupAppType(app_name) -> app_type
              -> lookupAppCategory(app_name) -> app_category (NEW)
              -> insertTransaction() -> DB -> API -> Frontend

Files to Change

Database (3 files)

• supabase/migrations/006_app_category.sql — ALTER TABLE + index + update RPC functions
• src/lib/db/adapters/sqliteAdapter.js — column + index + 2 new functions
• src/lib/db/adapters/supabaseAdapter.js — column in upserts + 2 new functions
• src/lib/db/database.js — add new functions to re-export list

Backend (2 files)

• src/lib/services/revenueService.js — imageMap cache, lookupAppCategory(), backfillAppCategories(), update processTransaction()
• src/server.js — backfill + recategorize admin endpoints

Frontend (1 file)

• src/lib/components/RevenueTransactions.svelte — CATEGORY column in table + CSV

Config (1 file)

• src/lib/config.js — getCategoryLabel() helper

Deployment Order (Critical)

1. Apply Supabase migration FIRST (column + RPC update)
2. Deploy backend code (auto-categorizes new transactions)
3. Trigger POST /api/admin/backfill-app-categories for historical data
4. Frontend can deploy with Step 2

SQLite instances auto-migrate on startup (no manual action needed).

Keeping Categories Up to Date

1. Add keywords to CATEGORY_CONFIG in config.js
2. Deploy new code
3. Run POST /api/admin/backfill-app-categories for NULL rows
4. Run POST /api/admin/recategorize-all-transactions for full re-scan

Backup Impact

revenue_transactions is NOT backed up to R2 (by design — re-derived from blockchain). Category data is fully re-derivable during re-sync. No changes needed to backup service.

Risks

• RPC update must happen BEFORE backend deploy (else API errors)
• imageMap cache miss: fallback to app_name keyword matching
• Backfill on large tables: use DISTINCT app_name (~5k unique) not individual rows (~200k)
• NULL categories for unknown apps: expected, add keywords over time