The modeled schema
Budgets, accounts, payees, category groups, categories, months, month categories, transactions, subtransactions, scheduled transactions, and their subtransactions. Each row carries deleted and deleted_at; deletes never reverse and timestamps never reset.
One function per entity
SELECT ynab.apply_response(123); -- dispatches to apply_accounts, apply_transactions, ...
-- each: extract from jsonb -> INSERT ... ON CONFLICT DO UPDATE -> return count- Idempotent. Applying the same response twice changes nothing.
- All or nothing. A failure in one entity rolls back the whole response.
- Partial indexes on
deleted = falsekeep the common queries fast.
Where the docs and the payload disagreed
The first transform was written from the API documentation. The real payload has subtransactions as top-level arrays, no split flag on transactions, and currency format as an object. A second SQL file replaced the affected functions. The raw rows were replayed. No data was lost, because of part 3.
Audio · 3 minWhy deletes are soft and terminal
Free to borrow for your feed · Square edition
Where you are: clean tables you can rebuild from raw at will. Next, they refresh themselves every hour.
