## Demo
Proves SURTR-213 end-to-end: live QuickBooks Deposits are fetched, filtered to expense-account lines only, transformed with the positive-sign convention, and loaded into the new staging_education.quickbooks_deposits table — verified by reading the rows back from Redshift.
Backend — Deposit expense-line transform (reproducible, no creds)
Ran a throwaway script that imports handler._build_accounts_map + handler._transform_to_deposit_records and calls them directly on synthetic QB fixtures — a vendor-refund deposit (bank line + expense line) and a deposit with no expense lines:
[miami] deposit 2524: no Expense-account lines found, skipped account types: ['Bank', 'Other Current Asset']. Emitting no rows.Input: 2 deposits (4 lines total) -> emitted 1 row(s)
Expected: 1 (the single Expense-account line; the all-non-expense deposit emits 0)
{"transaction_id": "2798", "txn_date": "2026-03-23", "account_id": "104", "account_number": "63220", "account_name": "Workshops", "class_name": "Alpha Miami", "line_amount": "1120.0000", "line_description": "Alpha Miami-Refund"}
Only the Expense-account line is emitted; the bank "deposit-to" line and the all-non-expense deposit are dropped (the latter emits zero rows — no header-level fallback). line_amount is stored positive; account_number/account_name come from the chart of accounts.
Pipeline + Data — live run loads the new table
Ran the pipeline locally against production QuickBooks for one company (miami, Q1 2026) with a real Redshift write (date-windowed, idempotent; downstream trigger not configured locally, so skipped):
[quickbooks_deposits] Uploading 3 records to s3://.../quickbooks_deposits/miami/...jsonl[quickbooks_deposits] Running atomic DELETE + COPY for miami (2026-01-01 to 2026-03-31)
[quickbooks_deposits] Loaded 3 records for miami
[miami] OK: 163 bills, 7 credits (7 vc lines), 100 bill payments, 55 journal entries, 42 purchases, 19 deposits (3 deposit expense lines)
No DOWNSTREAM_PIPELINE_ID configured, skipping core tables trigger
19 raw Deposits → 3 expense lines loaded. Reading them back from Redshift:
SELECT transaction_id, txn_date, account_number, account_name, class_name, line_amount, line_descriptionFROM staging_education.quickbooks_deposits WHERE company_id='miami' ORDER BY txn_date, account_number;
transaction_id | txn_date | account_number | account_name | class_name | line_amount | line_description
2208 | 2026-01-23 | 62400 | Utilities | Alpha Miami | 0.0100 | Alpha Miami-Test
2507 | 2026-02-24 | 62400 | Utilities | Alpha Miami | 0.0100 | Alpha Miami-Test payment
2798 | 2026-03-23 | 63220 | Workshops | Alpha Miami | 1120.0000 | Alpha Miami-Refund
The $1,120 refund to 63220 Workshops is the exact vendor-refund credit the pre-implementation reconciliation flagged as missing — now itemized with its expense account_id.
Most at risk from this change — the new query_accounts / query_deposits fetches and the new table write sit inside the *existing* per-company AP sync loop, so the danger is regressing entities that already work. Verified they all still sync in the same run (bills, vendor_credits, bill_payments, journal_entries, purchases all loaded for miami above; 1 succeeded / 0 failed), and the scoped tests pass:
uv run pytest tests/test_handler.py tests/test_redshift_handler.py tests/test_end_of_month.py -q92 passed in 0.23s
---
## Overview
Extends the quickbooks-ap-sync Lambda to fetch the QuickBooks Deposit entity — never fetched before — and write a new staging_education.quickbooks_deposits staging table, one row per Deposit expense-account line (vendor refunds posted back to an expense account). These refunds already net into quickbooks_pl_monthly, but no line-level table ingested them, so the Aerie "Actual vs Model" drill-down's itemized sum exceeded the net line total ("underage" cells). The new table is a structural twin of quickbooks_bills / quickbooks_vendor_credits so a downstream UNION ALL arm needs no per-source column mapping.
Linear: [SURTR-213 — QB sync: capture Deposit line items (vendor refunds) with expense account_id](https://linear.app/builder-team/issue/SURTR-213)
## Pre-implementation confirmation (live QB)
The ticket-mandated live-QB confirmation PASSED — all three diagnosed cells were verified as genuine Deposit expense lines. QB returns the refund Amount as positive, confirming the positive-store convention (downstream negates).
| Company | Cell | Confirmed amount |
|---|---|---|
| Alpha Miami | Deposit expense line | −1,120 |
| Scottsdale | Deposit expense line | −562.81 |
| Carrolton | Deposit expense line | −30 |
## Implementation summary
src/qb_client.py
- NEW query_deposits() — queries the QB Deposit entity over the same [start_date, end_date] window via the existing _query_qb() pagination + Fault-handling helper.
- NEW query_accounts() — SELECT * FROM Account ORDERBY Id (deterministic pagination) for the chart-of-accounts lookup.
src/handler.py
- NEW _build_accounts_map() — builds {account_id → name / number / type}.
- NEW _transform_to_deposit_records() — reads DepositLineDetail (AccountRef / ClassRef), emits a row ONLY for lines mapping to an AccountType == "Expense" account (skips bank / income / balance-sheet lines), stores line_amount POSITIVE (downstream negates, mirroring vendor_credits), with NO header-level fallback row.
- Wired query_accounts() + query_deposits() into the per-company loop and added quickbooks_deposits to table_writes.
src/redshift_handler.py
- Registered quickbooks_deposits in TABLE_CONFIGS (column set identical to bills / vendor_credits).
ddl/create_quickbooks_deposits.sql
- NEW staging table; PK (company_id, transaction_id, line_id); DISTKEY company_id; SORTKEY (txn_date, account_id).
Tests
- tests/test_handler.py — deposit transformer: DepositLineDetail parsing, expense-only filter, positive sign, multi-line, no-fallback.
- tests/test_redshift_handler.py — test_deposits_config_matches_bills.
- tests/test_end_of_month.py — added query_accounts / query_deposits mocks.
## Key design decisions
- DepositLineDetail, not AccountBasedExpenseLineDetail. QB Deposit lines carry a different detail type than Bills/VendorCredits/Purchases. The transformer reads DepositLineDetail.AccountRef / .ClassRef rather than reusing the existing expense-detail-type path.
- Expense-only filter via the new query_accounts(). The pipeline had no chart-of-accounts lookup. A single Deposit mixes the bank/clearing line with the expense-account refund lines, so we fetch the Account list once per company, build an {account_id → AccountType} map, and keep only lines whose account is AccountType == "Expense".
- Positive-store sign convention + reconciliation invariant. line_amount is stored as the positive raw QB Amount (mirroring quickbooks_vendor_credits); the downstream consumer negates. The full 5-way reconciliation invariant, per (account_id, company_id, month), is:
bills + purchases + journal_entries + vendor_credits(-line_amount) + deposits(-line_amount) ≈ pl_monthly.amount
within $0.50.
- No header-level fallback row. A Deposit whose lines are all bank/income/balance-sheet is legitimately not an expense transaction and emits zero rows (unlike the Bills/VendorCredit transformers); a Deposit with lines but no qualifying expense line logs a WARN.
## Test coverage
Full pipeline suite 147 passing; ruff clean.
## Self-review
No CRITICAL/IMPORTANT findings. Two MINOR items fixed: deterministic account pagination (ORDERBY Id) and a doc-notation fix in FEATURE.md's reconciliation line.
## Out of scope
- core_education.fct_expense UNION ALL arm — adding quickbooks_deposits as a negated UNION ALL source is downstream quickbooks-core-tables work, tracked separately. The table is a structural twin so that arm needs no per-source column mapping.
- Aerie pl-transactions.ts wiring — surfacing the new deposit rows in the Aerie "Actual vs Model" drill-down is a separate AERIE-repo ticket.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
