Mirror YNAB budget into Postgres with delta sync and change log #55
Reference in New Issue
Block a user
Delete Branch "feat/ynab-mirror"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Why
The YNAB tools called the live API on every question and filtered results in Python, so many questions they couldn't answer at all. YNAB has no event log, but it supports delta requests (
server_knowledge), so this keeps a local copy in Postgres and answers from it.What
ynab_*tables (accounts, payees, categories, groups, months, month categories, transactions, subtransactions, sync state). The migration was generated by aerich and only adds tables, so it's safe for blue-green deploys.blueprints/ynab/sync.py): full download on the first run (~5 s, ~8 MB), then a delta everyYNAB_SYNC_INTERVAL(default 900 s) and on demand when the data is >2 min old, plus a full resync once a day. Concurrent syncs, including across blue and green, are serialized by an asyncio lock and a Postgres advisory lock.ynab_changes: a log of real edits (transactions, assignments, renames) as old → new values, built by diffing each sync against the stored copy. Derived fields like balances and activity aren't logged.ynab_budget_summary,ynab_account_balances,ynab_search_transactions(splits expanded),ynab_spending_report(by category, group, payee, account or month),ynab_category_budget,ynab_recent_changes.ynab_sql: read-only SELECT over theynab_*tables. sqlglot validation (one SELECT, table allowlist resolved through scopes, dangerous functions denied), then a READ ONLY transaction, 10 s timeout and 200-row cap.make ynab-sync/make ynab-resync,python -m blueprints.ynab [--full].utils/ynab_service.pyand the unusedynabSDK; addedsqlglot.Notes
activitynets in the Credit Card Payments categories: it shows -$434 for October 2026 against $3,805 actually spent.internal, so only the category-level flag is used for filtering.ynab_sqlis admin-only.Testing
.venv/bin/pytest tests/ -q: 122 passed (newtests/unit/test_ynab_mirror.pycovers delta application, change-log diffs, split/delete handling, SQL guard bypass cases and month validation).🤖 Generated with Claude Code
- Full resyncs mark mirror rows missing from the download as deleted; YNAB omits deleted entities from non-delta responses, so deletions made just before a full sync never reached the mirror. - YNAB tools return errors as text instead of raising, since ToolNode re-raises and would fail the whole chat turn. ynab_sql catches all sqlglot errors (e.g. an unbalanced quote) and runs the SQL regenerated from the validated tree, which also drops comments that broke the LIMIT wrapper. Results keep duplicate column names. - Sync fetches from YNAB before opening the DB transaction, discards the payload if the other container synced meanwhile, re-checks freshness under the lock so parallel tool calls share one sync, and backs off for 60s after a failure. - The mirror is pinned to one budget; a changed YNAB_BUDGET_ID errors instead of mixing budgets, and an unset id stays on the budget the first sync resolved from "last-used". - Change log: no entries for empty month rollover rows, and id fields are logged as names (category, payee, account). ynab_recent_changes accepts the names it prints ("assignment") and plurals. - Outputs sized to the 4000-char tool result cap; ynab_sql reuses the shared admin message. - YNAB API client moves back to utils/ynab_service.py, and the frontend tool status labels list the new tools. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>- Failure backoff is re-checked under the sync lock, so parallel tool calls share one failed YNAB request instead of retrying in turn. - Budget id: YNAB_BUDGET_ID aliases ("last-used", "default") count as unset; state is always keyed by the real budget id, a mismatched response errors instead of silently skipping every sync, and more than one stored budget is an error rather than an arbitrary pick. - Change log renames only id fields that resolve to names (account, payee, category, group); subtransaction transaction_id stays as is. - ynab_recent_changes: correct singular for "categories", multi-word types, and the "month" type. - The SQL wrap test now parses the wrapped query. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>