Copilot Money

Query local data from the Copilot Money Mac app to analyze transactions, spending patterns, account balances, investments, and budgets. Data is stored in both SQLite (transactions, balances) and Firestore LevelDB cache (recurring names, budgets, investments).

Database Location

~/Library/Group Containers/group.com.copilot.production/database/CopilotDB.sqlite

Schema

Transactions Table

Primary table for all financial transactions.

accountDailyBalance Table

Daily balance snapshots per account.

Firestore Cache (LevelDB)

Additional data is stored in Firestore's local LevelDB cache, not in the SQLite database.

Location:

~/Library/Containers/com.copilot.production/Data/Library/Application Support/firestore/__FIRAPP_DEFAULT/copilot-production-22904/main/*.ldb

Collections

Recurring Definitions

Data Not in SQLite

• Recurring names - human-readable names like "Rent", "Netflix"
• Budget amounts - monthly budget per category
• Investment data - holdings, prices, performance, splits
• Account/institution names - Chase, Fidelity, etc.
• Category names - Restaurants, Travel, Groceries, etc.

Extracting Data from LevelDB

List all recurring names:

for f in ~/Library/Containers/com.copilot.production/Data/Library/Application\ Support/firestore/__FIRAPP_DEFAULT/copilot-production-22904/main/*.ldb; do
  strings "$f" 2>/dev/null | grep -B10 "^state$" | grep -A1 "^name$" | grep -v "^name$" | grep -v "^--$"
done | sort -u | grep -v "^$"

List all collections:

for f in ~/Library/Containers/com.copilot.production/Data/Library/Application\ Support/firestore/__FIRAPP_DEFAULT/copilot-production-22904/main/*.ldb; do
  strings "$f" 2>/dev/null
done | grep -oE "documents/[a-z_]+/" | sort | uniq -c | sort -rn

Find category names:

for f in ~/Library/Containers/com.copilot.production/Data/Library/Application\ Support/firestore/__FIRAPP_DEFAULT/copilot-production-22904/main/*.ldb; do
  strings "$f" 2>/dev/null
done | grep -iE "^(groceries|restaurants|shopping|entertainment|travel|transportation|utilities)$" | sort -u

Common Queries

Recent Transactions

SELECT date, name, amount, category_id
FROM Transactions
WHERE user_deleted = 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_deleted = 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_deleted = 0 AND date >= date('now', '-30 days')
GROUP BY category_id
ORDER BY total DESC;

Search Transactions

SELECT date, name, amount
FROM Transactions
WHERE name LIKE '%SEARCH_TERM%' AND user_deleted = 0
ORDER BY date DESC;

List Recurring Transactions

SELECT DISTINCT name, recurring_id
FROM Transactions
WHERE recurring = 1 AND user_deleted = 0
ORDER BY name;

Usage

Use sqlite3 to query the database:

sqlite3 ~/Library/Group\ Containers/group.com.copilot.production/database/CopilotDB.sqlite "YOUR_QUERY"

For formatted output:

sqlite3 -header -column ~/Library/Group\ Containers/group.com.copilot.production/database/CopilotDB.sqlite "YOUR_QUERY"

Notes

• Category IDs are opaque strings - group by them for analysis (names are in Firestore cache)
• Amounts are positive for expenses, negative for income
• Filter user_deleted = 0 to exclude deleted transactions
• Both databases are actively used by the app; read-only access is safe
• SQLite has recurring_id linking to Firestore recurring definitions
• Use strings on LevelDB files to extract human-readable data from Firestore cache