Copilot Money Mac

Copilot Money

查询 local data from the Copilot Money Mac 应用 to analyze transactions, spending patterns, account balances, investments, and bud获取s. Data is stored in 机器人h SQLite (transactions, balances) and Fi恢复 LevelDB 缓存 (recurring names, bud获取s, investments).

Database Location ~/库/Group ContAIners/group.com.copilot.production/database/CopilotDB.sqlite

模式 Transactions Table

Primary table for all financial transactions.

Column Type Description id TEXT Primary key date DATE Transaction date name TEXT Merchant/transaction name original_name TEXT Raw name from bank amount DOUBLE Transaction amount (positive = expense) iso_currency_code TEXT Currency (e.g., "USD") account_id TEXT Linked account reference category_id TEXT Category reference pending BOOLEAN Whether transaction is pending recurring BOOLEAN Whether transaction is recurring recurring_id TEXT Links to recurring definition (see Fi恢复) user_note TEXT User-添加ed notes user_删除d BOOLEAN Soft-删除d by user accountDAIlyBalance Table

DAIly balance snapshots per account.

Column Type Description date TEXT Snapshot date account_id TEXT Account reference current_balance DOUBLE Balance on that date avAIlable_balance DOUBLE AvAIlable balance Fi恢复 缓存 (LevelDB)

添加itional data is stored in Fi恢复's local LevelDB 缓存, not in the SQLite database.

Location:

~/库/ContAIners/com.copilot.production/Data/库/应用 Support/fi恢复/__FIR应用_DEFAULT/copilot-production-22904/mAIn/.ldb

Collections Collection Description items Linked bank accounts/institutions investment_prices Historical security prices investment_performance TWR (time-weighted return) per holding investment_splits Stock split 历史 securities Stock/fund metadata users/.../bud获取s Bud获取 definitions (amount, category_id) users/.../recurrings Recurring transaction definitions amazon Amazon order matching data Recurring Definitions Field Description name Display name (e.g., "Water / Sewer", "Rent") match_string Transaction name to match (e.g., "检查 PAID") plAId_category_id Category ID for the recurring 状态 "active" or "inactive" Data Not in SQLite Recurring names - human-readable names like "Rent", "Netflix" Bud获取 amounts - monthly bud获取 per category Investment data - holdings, prices, performance, splits Account/institution names - Chase, Fidelity, etc. Category names - Restaurants, Travel, Groceries, etc. 提取ing Data from LevelDB

列出 all recurring names:

for f in ~/库/ContAIners/com.copilot.production/Data/库/应用\ Support/fi恢复/__FIR应用_DEFAULT/copilot-production-22904/mAIn/.ldb; do strings "$f" 2>/dev/null | grep -B10 "^状态$" | grep -A1 "^name$" | grep -v "^name$" | grep -v "^--$" done | 排序 -u | grep -v "^$"

列出 all collections:

for f in ~/库/ContAIners/com.copilot.production/Data/库/应用\ Support/fi恢复/__FIR应用_DEFAULT/copilot-production-22904/mAIn/.ldb; do strings "$f" 2>/dev/null done | grep -oE "documents/[a-z_]+/" | 排序 | uniq -c | 排序 -rn

Find category names:

for f in ~/库/ContAIners/com.copilot.production/Data/库/应用\ Support/fi恢复/__FIR应用_DEFAULT/copilot-production-22904/mAIn/.ldb; do strings "$f" 2>/dev/null done | grep -iE "^(groceries|restaurants|shopping|entertAInment|travel|transportation|utilities)$" | 排序 -u

Common Queries Recent Transactions SELECT date, name, amount, category_id FROM Transactions WHERE user_删除d = 0 ORDER BY date DESC LIMIT 20;

Monthly Spending Summary SELECT strftime('%Y-%m', date) as month, SUM(amount) as total FROM Transactions WHERE amount > 0 AND user_删除d = 0 GROUP BY month ORDER BY month DESC;

Spending by Category SELECT category_id, SUM(amount) as total, COUNT(*) as count FROM Transactions WHERE amount > 0 AND user_删除d = 0 AND date >= date('now', '-30 days') GROUP BY category_id ORDER BY total DESC;

搜索 Transactions SELECT date, name, amount FROM Transactions WHERE name LIKE '%搜索_TERM%' AND user_删除d = 0 ORDER BY date DESC;

列出 Recurring Transactions SELECT DISTINCT name, recurring_id FROM Transactions WHERE recurring = 1 AND user_删除d = 0 ORDER BY name;

Usage

Use sqlite3 to 查询 the database:

sqlite3 ~/库/Group\ ContAIners/group.com.copilot.production/database/CopilotDB.sqlite "YOUR_查询"

For 格式化ted 输出:

sqlite3 -header -column ~/库/Group\ ContAIners/group.com.copilot.production/database/CopilotDB.sqlite "YOUR_查询"

Notes Category IDs are opaque strings - group by them for analysis (names are in Fi恢复 缓存) Amounts are positive for expenses, negative for income 过滤器 user_删除d = 0 to exclude 删除d transactions 机器人h databases are actively used by the 应用; read-only 访问 is safe SQLite has recurring_id linking to Fi恢复 recurring definitions Use strings on LevelDB files to 提取 human-readable data from Fi恢复 缓存