Wise + Telegram Financial Tracking System

Actionable Implementation Spec

Created: February 7, 2026 Status: Phase 6 Complete Est. Cost: ~$1.50/month Timeline: 6 phases

Why This Exists

All Arlem money flows through Wise. Right now, transactions live in the Wise dashboard, receipts are scattered across email/photos/Dropbox, and there’s no structured way to track expenses, categorise spending, or prepare for BAS/tax. We can’t ask AI “what did we spend on COGS last month?” because the data isn’t queryable.

This system captures every Wise transaction in real-time, sends it to a dedicated Telegram bot, lets you reply with a receipt (photo, PDF, screenshot) or text description, uses Gemini 2.5 Flash to extract and categorise everything, and stores it all in BigQuery where both Telegram commands and Claude Code MCP tools can query it.

Three goals:

1. Tax/BAS compliance (GST extraction, receipt retention, audit trail)
2. Real-time P&L visibility (know exactly where money goes)
3. AI-queryable financial data (Claude Code can run our books)

Architecture

DATA SOURCES
  Wise Polling (15 min)      Telegram Bot
  (ALL transactions:         (receipts, notes,
   card, transfers,           categories, commands)
   conversions, deposits)
         |                         |
         v                         v
                    AZURE FUNCTIONS (analytics/)

  scheduledWiseSync         httpTelegramWebhook
  - Poll balance statement  - Button callbacks
  - All currencies          - Photo/PDF/screenshot
  - Dedup via wise_id       - Text descriptions
  - Balance snapshots       - Bot commands (/pnl)
         |                         |
         v                         v
              Service Bus Queues
  ┌──────────────────┬───────────────────┐
  │ categorise-      │ process-          │
  │ transaction      │ receipt           │
  └──────────────────┴───────────────────┘
         |                    |
         v                    v
  queueCategorise        queueProcess
  Transaction            Receipt
  - Check rules          - Download from Telegram
  - Grok AI categorise   - Upload to Blob Storage
  - Learn from user      - Grok AI extraction
    overrides            - Parse vendor/GST/items
                         - Update transaction
         |                    |
         v                    v
                      DATA STORAGE

  BigQuery                        Azure Blob Storage
  (all financial data)            (receipt images/PDFs)
  - transactions                  - receipts/{tx_id}/{file}
  - receipts
  - categorisation_rules
  - account_balances
         |
         v
                    ACCESS LAYER

  MCP Server (arlem-financial)    Telegram Bot Commands
  - list_transactions             /pnl [period]
  - get_pnl_report                /expenses [period]
  - get_tax_summary               /balances
  - run_financial_sql             /uncategorised
  - categorise_transaction        /tax [fy]
  - get_vendor_spend              /vendor <name>

Architecture change (Feb 2026): Pivoted from Wise webhooks to polling.

Wise webhooks don’t cover card/debit card spend, only transfers and deposits.

The balance statement API returns ALL transaction types, so polling every 15 minutes

gives complete coverage including card spend, with simpler architecture.

Expense Categories

AI auto-categorises every transaction. User can override via Telegram button tap.

Subcategories are optional and free-text (e.g. COGS/fabriclinen, MARKETINGPAID/meta_ads).

Transaction Flow

1. Transaction Occurs

Wise fires webhook. Azure Function receives, stores, and notifies.

2. Telegram Notification

Outgoing Transaction

Amount: -$2,450.00 AUD
To: Ollko Textiles (Michelle Li)
Reference: INV-2026-042
Date: 7 Feb 2026, 2:34 PM
Category: COGS (AI, 95% confidence)

Reply with receipt (photo/PDF/screenshot)
or describe what this was for.

[COGS] [Shipping] [Marketing] [Software]
[Equipment] [Operations] [Samples] [Other]

3. User Responds (Any Combination)

Option A: Tap category button — Instant. Updates BigQuery. Done.

Option B: Reply with receipt image/PDF/screenshot — Grok AI extracts vendor, invoice #, date, line items, GST. Updates transaction. Sends confirmation.

Option C: Reply with text description — “50 linen covers from Ollko for March restock”. Grok AI extracts context, confirms category, adds as note.

Option D: Both — Send a photo AND a description. AI processes both together for higher confidence extraction.

4. AI Extraction Confirmation

Receipt processed!

Vendor: Ollko Textiles
Invoice: INV-2026-042
Items: 50x Linen covers @ $45ea
GST: $222.73
Total: $2,450.00

Category: COGS / fabric_linen

5. Auto-Categorisation (Recurring)

Known recurring payments skip the “needs categorisation” step:

• Shopify monthly fee → SOFTWARE
• Meta Platforms → MARKETING_PAID
• Google Ads → MARKETING_PAID
• Julie Zhu → COGS
• Ollko Textiles → COGS

When user overrides an AI category, the system learns and creates a new rule.

Reminders

Uncategorised or incomplete transactions get follow-up reminders.

Reminder Schedule

Daily Digest (if pending items exist)

Sent once daily at 9am AEST if there are uncategorised transactions:

Morning Summary

3 transactions need your attention:

1. $156.89 to Meta Platforms (3 days ago)
   [Marketing] [Software] [Other]

2. $89.00 to Spotlight (2 days ago)
   [COGS] [Equipment] [Samples] [Other]

3. $45.00 to Canva (1 day ago)
   [Marketing] [Software] [Other]

2 transactions missing receipts:
- $2,450.00 to Ollko Textiles (5 days ago)
- $89.00 to Spotlight (2 days ago)

Reply to any with a photo or description.

Reminder Rules

• Auto-categorised transactions with high confidence (>90%) don’t need reminders
• Transactions under $10 get deprioritised (single reminder only)
• Owner contributions (OWNER_IN) skip receipt reminders
• Banking fees (BANKING) skip receipt reminders

Receipt Input Flexibility

The system accepts ANY combination of inputs per transaction:

Grok AI Extraction Prompt

Extract financial data from this receipt/invoice/description.
This is for an Australian e-commerce business selling bedhead cushions.

Return JSON:
{
  "vendor": "Supplier name or null",
  "invoice_number": "INV-123 or null",
  "invoice_date": "2026-02-07 or null",
  "total_amount": 2450.00,
  "gst_amount": 222.73,
  "currency": "AUD",
  "items": [
    {"description": "Linen covers", "quantity": 50, "unit_price": 45.00, "total": 2250.00}
  ],
  "suggested_category": "COGS",
  "suggested_subcategory": "fabric_linen",
  "confidence": 0.95,
  "notes": "Any additional context extracted"
}

Rules:
- If GST not shown but total is GST-inclusive, calculate: gst = total / 11
- If currency not AUD, note the original currency
- Return null for fields you cannot extract confidently
- For text descriptions without a receipt image, extract what you can

Telegram Bot Commands

On-demand financial reports via Telegram.

Example: /pnl

P&L Report — Last 30 Days

Revenue                    $5,925.00

Cost of Goods Sold        -$2,100.00
Shipping & Packaging        -$450.00
                           ----------
Gross Profit               $3,375.00   57%

Operating Expenses
  Marketing (Paid)          -$650.00
  Software & Tools           -$87.00
  Banking & Fees             -$42.00
                           ----------
Total Opex                  -$779.00

                           ----------
Net Profit                 $2,596.00   44%

15 transactions | 3 uncategorised

BigQuery Schema

transactions table

CREATE TABLE arlem.transactions (
  id STRING NOT NULL,
  wise_id STRING,

  -- Core
  transaction_date TIMESTAMP NOT NULL,
  type STRING NOT NULL,          -- incoming, outgoing, conversion, fee
  status STRING DEFAULT 'completed',

  -- Amounts (signed: positive = in, negative = out)
  amount NUMERIC NOT NULL,
  currency STRING NOT NULL,
  amount_aud NUMERIC,
  exchange_rate NUMERIC,
  fees NUMERIC,

  -- Counterparty
  counterparty_name STRING,
  counterparty_type STRING,      -- supplier, customer, platform, owner
  reference STRING,
  description STRING,

  -- Category
  category STRING,               -- COGS, MARKETING_PAID, SOFTWARE, etc.
  subcategory STRING,
  category_source STRING,        -- user, ai, auto_rule
  category_confidence FLOAT64,

  -- Receipt
  has_receipt BOOL DEFAULT FALSE,
  receipt_vendor STRING,
  receipt_items STRING,
  receipt_gst NUMERIC,
  receipt_invoice_number STRING,

  -- Notes & context
  notes STRING,
  user_description STRING,       -- text description from Telegram reply
  tags ARRAY<STRING>,

  -- Tax
  is_tax_deductible BOOL,
  is_gst_claimable BOOL,
  financial_year STRING,         -- 2025-26, 2026-27

  -- Telegram
  telegram_message_id STRING,
  telegram_chat_id STRING,

  -- Reminders
  reminder_count INT64 DEFAULT 0,
  last_reminder_at TIMESTAMP,
  needs_attention BOOL DEFAULT TRUE,

  -- Audit
  source STRING DEFAULT 'wise',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
  updated_at TIMESTAMP,

  -- Computed
  year INT64 GENERATED ALWAYS AS (EXTRACT(YEAR FROM transaction_date)),
  month INT64 GENERATED ALWAYS AS (EXTRACT(MONTH FROM transaction_date)),
  quarter INT64 GENERATED ALWAYS AS (EXTRACT(QUARTER FROM transaction_date))
)
PARTITION BY DATE(transaction_date)
CLUSTER BY category, type;

receipts table

CREATE TABLE arlem.receipts (
  id STRING NOT NULL,
  transaction_id STRING NOT NULL,

  -- Storage
  blob_path STRING NOT NULL,
  blob_url STRING,
  file_type STRING,              -- image/jpeg, image/png, application/pdf
  file_size INT64,

  -- AI extraction
  ai_vendor STRING,
  ai_invoice_number STRING,
  ai_invoice_date DATE,
  ai_total_amount NUMERIC,
  ai_gst_amount NUMERIC,
  ai_currency STRING,
  ai_items JSON,                 -- [{description, quantity, unit_price, total}]
  ai_suggested_category STRING,
  ai_confidence FLOAT64,
  ai_raw_response STRING,

  -- Processing
  processing_status STRING,      -- pending, success, failed
  processing_error STRING,
  processed_at TIMESTAMP,

  -- Audit
  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
  source STRING                  -- telegram_photo, telegram_document, telegram_text
);

categorisation_rules table

CREATE TABLE arlem.categorisation_rules (
  id STRING NOT NULL,

  -- Match pattern
  counterparty_pattern STRING,   -- exact or contains match
  reference_pattern STRING,
  amount_min NUMERIC,
  amount_max NUMERIC,

  -- Result
  category STRING NOT NULL,
  subcategory STRING,

  -- Learning
  confidence FLOAT64 DEFAULT 1.0,
  times_applied INT64 DEFAULT 0,
  times_overridden INT64 DEFAULT 0,
  created_from STRING,           -- bootstrap, user_override, ai_learned
  is_active BOOL DEFAULT TRUE,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
  updated_at TIMESTAMP
);

account_balances table

CREATE TABLE arlem.account_balances (
  snapshot_date DATE NOT NULL,
  currency STRING NOT NULL,
  balance NUMERIC NOT NULL,
  balance_aud NUMERIC,
  exchange_rate NUMERIC,
  captured_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
)
PARTITION BY snapshot_date;

Key Views

• vmonthlypnl — Revenue, COGS, gross profit, opex by category, net profit per month
• vexpensebreakdown — Spend by category/subcategory with receipt coverage
• vvendorspend — Total spend per vendor with transaction frequency
• vtaxsummary — Revenue, GST collected, GST paid, deductions per financial year
• v_uncategorised — Transactions needing attention (no category or low confidence)
• vreminderqueue — Transactions due for reminder based on age and reminder count

MCP Tools (arlem-financial)

New MCP server for Claude Code financial queries.

Environment Variables

New (analytics package)

# Wise API (polling - no webhook secret needed)
WISE_API_TOKEN=<already in MCP, needs adding to Azure>
WISE_PROFILE_ID=<already in MCP, needs adding to Azure>

# Telegram Bot (NEW dedicated bot) — Phase 2
ARLEM_FINANCE_BOT_TOKEN=123456789:ABCdef...
ARLEM_FINANCE_CHAT_ID=-1001234567890
TELEGRAM_WEBHOOK_SECRET=random-verification-secret

# Azure Blob Storage (receipts)
AZURE_STORAGE_CONNECTION_STRING=DefaultEndpointsProtocol=https;...
BLOB_CONTAINER_RECEIPTS=arlem-receipts

# Gemini AI (via OpenAI-compatible endpoint)
GEMINI_API_KEY=AIza...

Existing (already configured)

WISE_API_TOKEN          # Used by MCP server
WISE_PROFILE_ID         # Used by MCP server
BIGQUERY_PROJECT_ID     # Used by analytics
BIGQUERY_CREDENTIALS    # Used by analytics
SERVICE_BUS_CONNECTION_STRING  # Used by analytics
GEMINI_API_KEY          # Used by analytics (Gemini via OpenAI SDK)

MCP server config (.mcp.json)

{
  "arlem-financial": {
    "command": "node",
    "args": ["./mcp/dist/arlem-financial/index.js"],
    "env": {
      "BIGQUERY_PROJECT_ID": "...",
      "BIGQUERY_CREDENTIALS": "..."
    }
  }
}

Cost Estimate

Implementation Phases

Phase 1: Foundation — BigQuery + Wise Polling ✅

Goal: Capture every Wise transaction in BigQuery via polling.

Pivot: Originally planned to use Wise webhooks, but discovered webhooks don’t cover

card/debit card spend. Switched to polling the balance statement API every 15 minutes,

which returns ALL transaction types (card, transfers, conversions, deposits).

New files:

• analytics/src/functions/scheduledWiseSync.ts (polls every 15 min)
• analytics/repositories/wiseApi.ts (Wise API client)
• analytics/types/financial.ts
• analytics/data/financial-constants.ts

Reuse:

• analytics/repositories/bigquery.ts (batch load pattern)
• analytics/src/functions/scheduledShopifySync.ts (timer sync pattern)

Tasks:

• Define TypeScript types for transactions, balances, statement entries, categories
• Define financial constants (categories, table names)
• Create BigQuery tables (transactions, receipts, categorisationrules, accountbalances)
• Remove default 60-day partition expiration from partitioned tables
• Create wiseApi.ts repository

- [x] Fetch balance statements across all currencies - [x] Fetch balances for snapshots

• Create scheduledWiseSync Azure Function (every 15 min)

- [x] Fetch ALL currency balance statements since last sync - [x] Transform statement entries to BigQuery rows - [x] Compute amount_aud from exchange details - [x] Calculate financial_year (Australian: Jul 1 - Jun 30) - [x] Dedup via wise_id (referenceNumber from statement) - [x] Capture balance snapshots on each run - [x] Set needs_attention = true for all new transactions

• Deploy to Azure (arlem-analytics function app)
• Create process-transaction Service Bus queue (created but unused after pivot)
• Document BigQuery table management in analytics CLAUDE.md
• Add WISEAPITOKEN and WISEPROFILEID to Azure Function App Settings
• Verify first sync run picks up transactions
• Delete Wise webhooks from Wise dashboard (no longer needed)

Success criteria: Transactions appear in BigQuery within 15 minutes of any Wise activity (including card spend).

Phase 2: Telegram Bot — Notifications + Category Buttons ✅

Goal: Every transaction sends a Telegram notification. User can tap to categorise.

Implementation note: Used existing Arlem bot (ARLEMBOTTOKEN) via Cloudflare Telegram proxy

instead of creating a new dedicated bot. Used axios instead of grammy. No Service Bus queues

needed — direct webhook processing is fast enough for our volume.

Files created:

• analytics/src/functions/httpTelegramWebhook.ts
• analytics/repositories/telegramBot.ts

Tasks:

• Create telegramBot.ts repository (static class with axios, via Cloudflare proxy)

- [x] Helper: send transaction notification with inline keyboard - [x] Helper: send/edit messages - [x] Format currency, dates for Telegram markdown

• Send Telegram notifications from scheduledWiseSync (no queue needed)

- [x] Format transaction details (amount, counterparty, date, reference) - [x] Attach inline keyboard with category buttons - [x] Store telegrammessageid in BigQuery for linking

• Create httpTelegramWebhook Azure Function

- [x] Handle callback queries (button taps) - [x] Update transaction category in BigQuery - [x] Update Telegram message to show selected category - [x] Set needsattention = false, categorysource = 'user'

• Register Telegram webhook URL via Cloudflare proxy
• Deploy and test

Success criteria: ✅ Transaction notification in Telegram. Tap “COGS” and BigQuery updates instantly.

Phase 3: AI-Powered Transaction Enrichment ✅

Goal: Reply to a transaction with a receipt photo or text description. Grok AI extracts receipt data, categorises the transaction, and generates a summary. No AI runs at sync time — enrichment happens when the user replies.

Implementation note: Merged receipt processing + AI categorisation into a single phase.

No Service Bus queues — webhook processes everything inline (fast enough for our volume).

Created dedicated grokAI.ts repository instead of extending openAI.ts.

Removed category buttons from notifications — replaced with conversational enrichment flow.

AI migration (Feb 2026): Switched from Grok to Gemini 2.5 Flash via Google’s OpenAI-compatible

endpoint. Faster (6s vs 15s), cheaper, better OCR accuracy. Uses existing OpenAI SDK —

just swapped baseURL and model name. Receipt prompt hardened against hallucination

(explicit “read EXACTLY as printed” instructions, detail: 'high' for vision).

Files created:

• analytics/repositories/grokAI.ts (Grok AI: receipt extraction + categorise+summarise)
• analytics/repositories/blobStorage.ts (Azure Blob Storage for receipts)

Files modified:

• analytics/types/financial.ts (added summary, BigQueryReceipt, Grok result types)
• analytics/data/financial-constants.ts (added GROKAPIKEY, GROKBASEURL, RECEIPTBLOBCONTAINER)
• analytics/repositories/telegramBot.ts (removed buttons, added file download, reply, message update methods)
• analytics/src/functions/httpTelegramWebhook.ts (handles photo/text replies, AI enrichment pipeline)
• analytics/src/functions/scheduledWiseSync.ts (added summary: null to transform)

Tasks:

• Add @azure/storage-blob dependency
• Create blobStorage.ts repository

- [x] Upload receipt buffer to blob ({date}/{counterparty}/{filename}) - [x] Auto-create container on first use

• Create grokAI.ts repository

- [x] extractReceiptData() — Grok vision model extracts vendor, ABN, GST, line items - [x] categoriseAndSummarise() — categorises + generates summary from ALL available data

• Update telegramBot.ts

- [x] Remove inline keyboard buttons - [x] Add getFile(), downloadFile() for receipt download via proxy - [x] Add replyToMessage(), updateTransactionMessage() - [x] Message format shows category + summary when enriched

• Update httpTelegramWebhook.ts

- [x] Handle photo/document replies (download → blob upload → Grok vision → insert receipt) - [x] Handle text replies (save as user_description) - [x] Re-fetch tx + receipts, then AI categorise+summarise with ALL data - [x] Edit original message with enrichment results - [x] Send confirmation reply - [x] Vanilla messages (no reply) get helper text - [x] Keep legacy callback_query handler for old button messages

• Add summary column to BigQuery transactions table
• Add missing columns to BigQuery receipts table
• Set GROKAPIKEY in Azure Function App settings
• Deploy and verify clean compile
• Switch AI backend from Grok to Gemini 2.5 Flash (better accuracy, 3x faster, cheaper)
• Harden receipt extraction prompt against hallucination (read exactly, don’t guess)
• Add detail: 'high' to vision requests for better OCR
• Fix BigQuery timestamp object handling in blobStorage.ts and telegramBot.ts
• Fix generic receipt error message — now surfaces actual error
• Add AZURE_STORAGE env var to Azure Function App settings

Success criteria: ✅ Reply with receipt photo → receipt extracted, categorised, summary generated, message updated. Reply with text → summary regenerated with all data.

Phase 4: Auto-Categorisation + Reminders ✅

Goal: AI auto-categorises transactions at sync time. Recurring payments skip manual input. Daily digest chases uncategorised items.

Implementation note: No separate queue function needed. Auto-categorisation runs inline

during scheduledWiseSync before sending the Telegram notification. Rules matched first,

then Grok AI fallback. Daily digest at midnight UTC (9:30am ACST) re-sends individual

notifications so user can reply directly. Alibaba mapped to COGS (Ollko Textiles).

Owner contributions matched via widdas pattern.

Files created:

• analytics/data/bootstrap-rules.ts (10 vendor rules)
• analytics/repositories/categorisation.ts (rule matching, learning, AI fallback)
• analytics/src/functions/scheduledFinancialReminders.ts (daily digest)

Files modified:

• analytics/types/financial.ts (added CategorisationRule, AutoCategoriseResult)
• analytics/src/functions/scheduledWiseSync.ts (auto-categorise before notification)
• analytics/src/functions/httpTelegramWebhook.ts (learn from user overrides)
• analytics/repositories/telegramBot.ts (low-confidence footer message)

Tasks:

Auto-categorisation:

• Create bootstrap categorisation rules

- [x] Shopify → SOFTWARE / ecommerce - [x] Meta Platforms / Facebook → MARKETING / paid-ads - [x] Google Ads → MARKETING / paid-ads - [x] Julie Zhu / Hefei Charming → COGS / insert-manufacturing - [x] Alibaba (Ollko) → COGS / fabric-supplier - [x] Aureta Living → COGS / cover-manufacturing - [x] Widdas (owner) → OWNER_IN - [x] WIRE reference → BANKING / transfer-fee

• Create categorisation.ts repository

- [x] Match transaction against rules (counterparty pattern + reference pattern) - [x] Return matched category + confidence - [x] Learn from user overrides (create/update rules) - [x] Track rule effectiveness (timesapplied vs timesoverridden)

• Auto-categorise in scheduledWiseSync (inline, no queue)

- [x] Check categorisation rules first (pattern match = high confidence) - [x] If no rule match, call Grok AI for categorisation - [x] Apply category with source (auto_rule or ai) and confidence score - [x] If confidence >= 90%, set needs_attention = false - [x] If confidence < 90%, keep needs_attention = true (will trigger reminder) - [x] Notification shows category + summary

• When user overrides a category (button tap or AI re-categorisation):

- [x] If existing rule, increment times_overridden - [x] If no rule for this counterparty, create new rule from override - [x] If rule overridden 3+ times, deactivate it

• Low-confidence footer: “Low confidence. Reply with receipt to confirm.”

Reminders:

• Create scheduledFinancialReminders Azure Function (midnight UTC = 9:30am ACST)

- [x] Query needsattention = true AND remindercount < 4 transactions - [x] Skip transactions where: - Auto-categorised with > 90% confidence - Under $10 after first reminder - [x] Send daily digest summary message - [x] Re-send individual notifications for each pending tx - [x] Update telegrammessageid so user can reply to latest message - [x] Update remindercount and lastreminder_at in BigQuery - [x] Skip digest entirely if nothing to remind about

• Seed bootstrap rules on first sync run (ensureBootstrapRules)
• Deploy and verify clean compile
• Fix ensureBootstrapRules() — add verification polling (eventual consistency)
• Fix silent error swallowing in autoCategorise() — now logs errors
• Wrap ensureBootstrapRules() in try/catch in scheduledWiseSync
• Fix BigQuery null params error — use inline NULL for nullable fields
• Add OWNER_OUT category for owner withdrawals
• Exclude OWNERIN, OWNEROUT, PERSONAL from P&L, expenses, and tax reports
• Create httpFinancialBackfill endpoint for re-categorising + re-notifying all transactions

Success criteria: Shopify fee auto-categorised without any input. New unknown vendor gets AI suggestion. Uncategorised items get morning reminder.

Phase 5: Telegram Commands + Financial Queries ✅

Goal: On-demand financial reports via Telegram slash commands.

Implementation note: No BigQuery views created. The codebase has zero SQL migration

infrastructure, so all queries stay inline in financialQueries.ts. Phase 6’s MCP server

will have its own queries. No queue functions needed — commands are low-volume and fast

enough to handle inline in the webhook. getAustralianFinancialYear moved from

scheduledWiseSync.ts to financialQueries.ts as the shared source of truth.

Files created:

• analytics/repositories/financialQueries.ts (period parser + 7 BigQuery query methods)
• analytics/repositories/telegramCommands.ts (command routing, handlers, Markdown formatters)

Files modified:

• analytics/repositories/telegramBot.ts (formatCurrency made public)
• analytics/src/functions/httpTelegramWebhook.ts (slash command routing before reply handling)
• analytics/src/functions/scheduledWiseSync.ts (imports shared getAustralianFinancialYear)

Tasks:

Financial query repository:

• getPnl(period) — returns P&L data for any period
• getExpenseBreakdown(period, groupBy) — expenses by category/vendor
• getBalances() — current Wise balances (via Wise API, falls back to BigQuery snapshot)
• getUncategorised() — pending transactions
• getTaxSummary(fy) — tax data for financial year
• getVendorSpend(name) — spend with a vendor
• searchTransactions(query) — full-text search
• Period parser: 2026-01 → month, 2026-Q1 → quarter, 2025-26 → FY

Telegram commands:

• /pnl [period] — formatted P&L report
• /expenses [period] — expense breakdown by category
• /balances — current Wise balances (all currencies)
• /uncategorised — list pending transactions
• /tax [fy] — GST/tax summary
• /vendor <name> — spend with vendor
• /search <query> — search transactions
• Register commands with BotFather for autocomplete

Scheduled Wise sync: (moved to Phase 1, already complete)

• scheduledWiseSync runs every 15 min (polls all currencies, captures balance snapshots)

Success criteria: /pnl returns accurate P&L in Telegram. /uncategorised shows pending items.

Phase 6: MCP Server + Polish ✅

Goal: Claude Code can query all financial data. System is production-ready.

Implementation note: MCP server follows arlem-analytics pattern exactly (shebang, Server + StdioServerTransport).

11 tools total (10 planned + get_balances). SQL injection prevented via escapeString() helper

since MCP BigQuery client doesn’t support parameterised queries. Historical backfill and Shopify

revenue backfill added as modes to existing httpFinancialBackfill endpoint. /retry Telegram

command re-processes failed receipts. System alerts sent to Telegram on sync/reminder/webhook failures.

Files created:

• mcp/src/arlem-financial/index.ts (server setup + routing, 11 tools)
• mcp/src/arlem-financial/bigquery-client.ts (singleton BQ client, copied from arlem-analytics)
• mcp/src/arlem-financial/queries.ts (period parser, SQL constants, escapeString, categories)
• mcp/src/arlem-financial/tools/transactions.ts (list, get, search, uncategorised)
• mcp/src/arlem-financial/tools/reports.ts (pnl, expenses, tax, balances, vendor)
• mcp/src/arlem-financial/tools/admin.ts (categorise, run_sql)

Files modified:

• .mcp.json (added arlem-financial server config)
• CLAUDE.md + mcp/CLAUDE.md (updated server/tool counts)
• analytics/src/functions/httpFinancialBackfill.ts (added historical + shopify backfill modes)
• analytics/src/functions/httpTelegramWebhook.ts (receipt error tracking, log prefixes, system alerts)
• analytics/src/functions/scheduledWiseSync.ts (log prefixes, system alerts on failure)
• analytics/src/functions/scheduledFinancialReminders.ts (log prefixes, system alerts on failure)
• analytics/repositories/telegramBot.ts (added sendSystemAlert() method)
• analytics/repositories/telegramCommands.ts (added /retry command)
• analytics/repositories/blobStorage.ts (added downloadReceipt() method)
• analytics/types/financial.ts (added processing_error to BigQueryReceipt)

Tasks:

MCP server:

• Create arlem-financial MCP server (follow arlem-analytics pattern)
• Implement tools:

- [x] list_transactions — filter by date, category, type, counterparty, receipt status - [x] get_transaction — full detail including receipt extractions - [x] search_transactions — full-text search - [x] getpnlreport — P&L for any period - [x] getexpensereport — breakdown by category, subcategory, or vendor - [x] gettaxsummary — GST and deductions for financial year - [x] get_balances — latest balance snapshots per currency - [x] runfinancialsql — custom SELECT queries (read-only, no DML) - [x] categorise_transaction — override category from Claude Code - [x] getvendorspend — vendor-specific spend analysis - [x] get_uncategorised — transactions needing attention

• Add to .mcp.json configuration
• Test all tools from Claude Code
• Fix getbalances dedup (QUALIFY ROWNUMBER for latest per currency)

Polish:

• Historical backfill: httpFinancialBackfill?mode=historical&startDate=YYYY-MM-DD
• Backfill Shopify order payments as revenue transactions: httpFinancialBackfill?mode=shopify
• Error handling: /retry Telegram command re-processes last failed receipt
• Logging: [FunctionName] prefixed structured logs for all financial functions
• Telegram error notifications: sendSystemAlert() on sync/reminder/webhook failures
• Added processing_error column to BigQuery receipts table

Success criteria: ✅ Can ask Claude Code “what did we spend on COGS last quarter?” and get an accurate answer via MCP tools. All 11 tools verified working. DML correctly blocked. System alerts configured.

Key Files Reference

Existing (to reuse patterns from)

New (to create)

Security

• Wise webhook verified via HMAC-SHA256 signature (removed: using polling instead)
• Telegram webhook verified via secret token header
• Telegram chat ID validated against allowlist
• Receipt blob storage is private (SAS token required for access)
• MCP runfinancialsql restricted to SELECT only (no INSERT/UPDATE/DELETE)
• All data stored in Australia region (BigQuery australia-southeast1)
• Receipt images retained 7 years (ATO requirement)
• Financial year calculated as Australian FY (Jul 1 - Jun 30)