transaction-syncing

name

transaction-syncing

description

Refresh investment activities and card/bank expenses into family_office.db, then review spending. Investment activities come from the DB transactions table (SnapTrade); debit-card and bank spending comes from the DB bank_transactions table (SimpleFIN), auto-categorized. Sync-first so nothing is stale. USE WHEN user mentions "sync transactions", "transaction history", "expense tracker", "categorize spending", OR "update expenses".

TransactionSyncing

Refresh financial activity into family_office.db: investment activities (transactions table) and card/bank spending (bank_transactions table), auto-categorized for budget review.

family_office.db is the system of record. The Google Sheets export was retired 2026-07-31.

Step 0: Refresh (sync-first, mandatory)

Both halves read from the local DB, refreshed FIRST so nothing is stale. Follow the shared Sync-First + DB-Read pattern. This skill needs two sources:

uv run python -m src.integrations.snaptrade.sync_transactions_db          # investment activities -> transactions
uv run python -m src.integrations.simplefin.sync_expenses_db --months 3   # card/bank spending -> bank_transactions
# or refresh everything at once:
uv run python -m src.integrations.refresh_all --months 3

Completion criterion: the transactions and bank_transactions tables carry this run's synced_at.

Direction and sign

bank_transactions.direction is resolved by resolve_direction() in src/integrations/simplefin/sync_expenses_db.py. Explicit feed wording wins over amount sign, because Fidelity's CMA reports inbound payroll with the same negative sign it uses for outflows. amount is signed to match direction, so SUM(amount) is real cash flow: credits positive, debits negative.

Workflow Routing

Workflow	Trigger	File
IngestTransactions	"ingest transactions", "import history", user points to a Downloads CSV	workflows/IngestTransactions.md

CSV ingest is an archive and fallback path. The primary flow is the Step 0 refresh above.

Examples

Example 1: Sync after downloading Fidelity transaction history

User: "sync transactions"
-> Reads History_for_Account_{account_id}.csv from notebooks/transactions/
-> Creates/updates Transactions tab with full Fidelity data
-> Routes DEBIT CARD PURCHASE entries to Expense Tracker
-> Auto-categorizes expenses (H-E-B -> Groceries, Tesla -> Auto & Transport)
-> Reports: "Added 45 transactions, 12 expenses categorized"

Example 2: Import new transaction export

User: "import the transaction history"
-> Invokes SyncTransactions workflow
-> Detects duplicates by date + action + amount
-> Skips existing entries, adds only new ones
-> Flags uncategorized expenses for manual review

Example 3: Check recent transactions

User: "import fidelity transactions and update expense tracker"
-> Full sync with expense routing
-> Generates summary of dividends received, purchases, margin interest

Architecture Overview

Data Flow

SnapTrade activities            SimpleFIN dump (bun run src/dump.ts)
        |                                |
        v                                v
  sync_transactions_db            sync_expenses_db (categorize.py)
        |                                |
        v                                v
+------------------+           +--------------------+
| transactions     |           | bank_transactions  |  <- categorized, upserted
| table (DB)       |           | table (DB)         |
+------------------+           +--------------------+

family_office.db is the terminus. Query the tables directly for review; there is no downstream export.

Transaction Types Handled

Fidelity Action	Table	Category
DIVIDEND RECEIVED	transactions	DIVIDEND
REINVESTMENT	transactions	REINVESTMENT
DEBIT CARD PURCHASE	bank_transactions	Auto-categorized
MARGIN INTEREST	transactions	MARGIN_INTEREST
DIRECT DEPOSIT	bank_transactions (credit)	INCOME
LONG-TERM CAP GAIN	transactions	CAP_GAIN
JOURNALED	transactions	INTERNAL_TRANSFER

Smart Categorization

Categorization is executable and runs inside the expense adapter, so the category column arrives pre-filled on every bank_transactions row. The rules live in code at src/integrations/simplefin/categorize.py (categorize_expense(text, amount)), which is the source of truth mirroring the human-readable CategoryRules.md. Keep the two in sync when adding patterns.

Sample patterns:

• H-E-B, KROGER, COSTCO, WAL-MART -> Groceries
• Tesla, SUPERCHA -> Auto & Transport
• BENIHANA, GOLDEN CORRAL, PAPA JOHN -> Dining Out
• CVS, PHARMACY -> Health & Wellness
• amount < $1.00 or verification text -> Exempt; no match -> Uncategorized

Input Sources: the local DB (primary)

Half A: Investment activities (transactions table)

After Step 0's refresh, read investment activity from the DB:

sqlite3 family_office.db \
  "SELECT date, type, symbol, description, amount, quantity, currency FROM transactions ORDER BY date;"

Each row has a stable shape (type, date, symbol, amount, quantity, currency, description). Map it onto the master Transactions tab (type -> Action, date -> Date, amount -> Amount, etc.).

Half B: Card / bank expenses (bank_transactions table)

Debit-card and bank spending has no SnapTrade equivalent, so it comes from SimpleFIN via the expense adapter, already normalized and categorized:

sqlite3 family_office.db \
  "SELECT date, payee, description, amount, direction, category FROM bank_transactions ORDER BY date DESC;"

The adapter (src/integrations/simplefin/sync_expenses_db.py) pulls the SimpleFIN dump, categorizes each row via the executable rules in src/integrations/simplefin/categorize.py (the code source of truth mirroring CategoryRules.md), and upserts into bank_transactions keyed on (account_id, txn_id). Route direction == "debit" rows to the Expense Tracker using the category column.

Dedupe: the DB layer is idempotent (activities via dedupe_key, expenses via the (account_id, txn_id) upsert key), so re-running a sync is always safe and needs no external ledger to compare against.

Fallback: the Fidelity History CSV path below remains a manual reconciliation fallback only; it is no longer the primary path.

Core Workflow

1. Read Fidelity Transaction History CSV

Location: notebooks/transactions/History_for_Account_{account_id}.csv

CSV Columns:

Run Date, Action, Symbol, Description, Type, Price ($), Quantity,
Commission ($), Fees ($), Accrued Interest ($), Amount ($),
Cash Balance ($), Settlement Date

2. Reconcile against the DB

Compare CSV rows against the transactions table to spot anything the live sync missed. Match on Date + Action + Amount. This is a reconciliation check, not an import path: the live sync owns the data.

3. Generate Summary

SYNC SUMMARY - [Date]
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

transactions:       45 inserted, 12 updated
bank_transactions:  18 inserted, 3 uncategorized

BY TYPE:
  Dividends: $342.50
  Margin Interest: -$18.43
  Debit Card: -$1,245.67
  Direct Deposit: +$5,054.09
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Uncategorized rows are the follow-up: add the pattern to src/integrations/simplefin/categorize.py and mirror it in CategoryRules.md.

Reference Files

• CategoryRules.md: Pattern matching rules for expense categorization
• src/integrations/simplefin/categorize.py: executable source of truth for categories
• src/integrations/simplefin/sync_expenses_db.py: direction resolution and upsert

Pre-Flight Checklist

Before syncing transactions:

• DATABASE_URL and SIMPLEFIN_ACCESS_URL are set in .env
• SnapTrade account is enabled and routed in config/snaptrade-accounts.yaml
• Current date retrieved via date command

---

Skill Type: Domain (workflow guidance) Enforcement: SUGGEST Priority: Medium Line Count: < 300 (following 500-line rule)