OpenFinance Skill

Query your financial accounts and transactions via the OpenFinance API.

Setup

1. Go to openfinance.sh and create an account
2. Link a bank account through the dashboard
3. Copy your API key from the Connect tab
4. Set the environment variable:

   export OPENFINANCE_API_KEY="your_api_key_here"
   

All commands below use these variables:

BASE_URL="${OPENFINANCE_URL:-https://api.openfinance.sh}"
AUTH_HEADER="Authorization: Bearer $OPENFINANCE_API_KEY"

1. Get Accounts

Fetch all connected financial accounts with balances and institution info.

curl -s "$BASE_URL/api/accounts" -H "$AUTH_HEADER" | cat

Response shape per account:

• id (number) — account ID (use for filtering transactions)
• name, officialName — account names
• type (e.g. "depository", "credit"), subtype (e.g. "checking", "credit card")
• mask — last 4 digits
• currentBalance, availableBalance, isoCurrencyCode
• institutionName, institutionUrl
• status — "active" or "hidden"
• isSyncing (boolean), syncError (object or null)

A non-null syncError means the bank connection needs the owner to sign in again. Use Create Reconnect Link to fix it.

2. Get Transactions

Search and filter transactions. Returns newest first by default.

curl -s -G "$BASE_URL/api/transactions" \
  -H "$AUTH_HEADER" \
  --data-urlencode "startDate=YYYY-MM-DD" \
  --data-urlencode "endDate=YYYY-MM-DD" \
  --data-urlencode "search=coffee" \
  --data-urlencode "merchants=Starbucks,Walmart" \
  --data-urlencode "accountId=123" \
  --data-urlencode "limit=100" \
  --data-urlencode "cursor=YYYY-MM-DD:ID" \
  --data-urlencode "pending=false" \
  --data-urlencode "status=active,hidden" \
  --data-urlencode "fields=name,amount,date,merchantName" \
  --data-urlencode 'amountFilters=[{"operator":">","amount":100}]' \
  | cat

All query parameters are optional. Only include the ones you need.

Parameters

Transaction fields

id, name, amount, date, authorizedDate, pending, merchantName, isoCurrencyCode, accountId, status, createdAt, updatedAt

3. Query Transactions (SQL)

Run one validated SQL SELECT (maximum 2000 characters) against the unqualified txns relation for aggregations, grouping, and analysis. Only the caller's transactions are available; deleted transactions and hidden accounts are excluded. The query runs read-only with a 5-second timeout and a maximum of 1000 result rows. Aggregations still use all matching input rows, and smaller LIMITs and OFFSETs are preserved.

Filtering, DISTINCT, grouping, HAVING, sorting, subqueries, joins, and UNION/UNION ALL are supported. Every table source must be txns or a derived SELECT whose sources are txns. Other tables and schemas, caller-defined CTEs, window functions, SELECT INTO, locking clauses, table functions, transaction commands, and multiple statements are rejected.

Approved functions: COUNT, SUM, AVG, MIN, MAX, ABS, ROUND, CEIL, CEILING, FLOOR, LOWER, UPPER, LENGTH, TRIM, BTRIM, LTRIM, RTRIM, SUBSTRING, SUBSTR, REPLACE, DATE_TRUNC, DATE_PART, EXTRACT, TO_CHAR. CASE, COALESCE, NULLIF, GREATEST, LEAST, CURRENT_DATE, and CURRENT_TIMESTAMP are also supported. Casts are limited to built-in boolean, integer, numeric, floating-point, text, date, timestamp, and interval types. Arithmetic, comparisons, LIKE/ILIKE, IN, BETWEEN, and NULL/boolean tests are supported; custom operators, regex operators, COLLATE, and unlisted syntax are rejected. Function and type names may be unqualified or qualified with pg_catalog.

curl -s -X POST "$BASE_URL/api/transactions/query" \
  -H "$AUTH_HEADER" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT SUM(amount), COUNT(*) FROM txns WHERE merchant_name ILIKE '\''%starbucks%'\''"}' \
  | cat

txns CTE columns

id, name, amount (numeric), date, authorized_date, merchant_name, pending, iso_currency_code, account_id, status, created_at, updated_at

Note: SQL column names use snake_case (e.g. merchant_name), while the REST API returns camelCase (e.g. merchantName).

Example queries

Monthly spend breakdown:

SELECT TO_CHAR(date, 'YYYY-MM') as month, SUM(amount) as total, COUNT(*) as count
FROM txns GROUP BY 1 ORDER BY 1

Top merchants by total spend:

SELECT COALESCE(merchant_name, name) as merchant, SUM(amount) as total
FROM txns GROUP BY 1 ORDER BY total DESC LIMIT 10

Spending in a date range:

SELECT SUM(amount) as total FROM txns
WHERE date >= '2026-01-01' AND date < '2026-02-01'

Large transactions:

SELECT name, merchant_name, amount, date FROM txns
WHERE amount > 500 ORDER BY amount DESC

Response shape

Success: { "rows": [...], "rowCount": N } Error: { "error": "error message" } — fix the SQL and retry.

4. Create Reconnect Link

Create a public link that reconnects a disconnected account without signing in to OpenFinance. Give the URL to the account's owner. Opening it walks them through their bank's sign-in (Plaid or Quiltt), then shows the account re-syncing. Only the owner can finish this step: bank credentials and verification codes can't be sent through the API.

Find accounts that need reconnecting:

curl -s "$BASE_URL/api/accounts" -H "$AUTH_HEADER" \
  | jq '.accounts[] | select(.syncError != null) | {id, institutionName, syncError}'

Create a link for one of them:

curl -s -X POST "$BASE_URL/api/accounts/ACCOUNT_ID/reconnect-link" \
  -H "$AUTH_HEADER" \
  | cat

Response (201):

{
  "url": "https://app.openfinance.sh/reconnect/<token>",
  "expiresAt": "2026-01-02T15:04:05.000Z",
  "accountId": 123,
  "institutionName": "Chase",
  "provider": "plaid"
}

• Links expire after 24 hours. Once the account is reconnected, the link only shows the sync's progress. If the bank still wants a sign-in afterwards, the same link can try again.
• To check it worked, poll GET /api/accounts: the account's syncError turns null as soon as the owner reconnects, and isSyncing stays true until the re-sync finishes, which can take a few minutes.
• Reconnecting repairs the account's whole bank connection, so it also fixes the other accounts linked through the same sign-in.
• 404: no account with that ID. 422: the account's provider (MX) isn't supported.

Tips

• Use SQL for aggregations — monthly totals, top merchants, recurring charges. It's faster and returns less data than fetching all transactions. Transactions have no categories; group by COALESCE(merchant_name, name).
• Use fields parameter on GET /api/transactions to reduce payload size when you only need a few columns.
• Pagination: the response doesn't include a cursor. If a page returns limit transactions, request the next one with cursor=<date>:<id> from the last transaction, keeping the other parameters the same. Cursors only work with the default date sort.
• Amount values are positive for debits (money spent) and negative for credits (money received).
• When constructing SQL, always reference the txns CTE directly — do not define your own CTE or use transaction control statements.
• Disconnected accounts: if an account has a syncError, create a reconnect link and send it to the owner rather than asking them to open the app.