Plaid Loan Sign Normalization (Port Washington State Bank & HELOC/LOC quirk)

TL;DR: A small set of Plaid institutions (Port Washington State Bank, some HELOC/LOC providers) report type=loan balances with a negative sign — the opposite of Plaid’s documented positive-for-debt convention. Left unflipped, a -$250k mortgage arrives as -$250k and reduces total liabilities instead of adding to them, inflating net worth by twice the debt. We fix this by flipping the sign at every read surface; stored data is never mutated. The canonical helper is lib/liability-balance.ts. Fleet scope when audited (2026-07-19, strict type='loan' AND source_type='loan' AND balance<0 predicate): 44 rows / 32 active users / $4,329,024 mis-signed debt /$ 8,658,048 net-worth overstatement (2× swing). The user who reported the bug (user_33TM7kqEBv8k26hNMx7OW8GbywT, 8 Wisconsin mortgages under “Port Washington State Bank - Business” with negative balances) had two open escalations against this issue. Shipped in PR #2890.

1. The problem

Plaid’s contract for type=loan accounts is unambiguous:

balance.current is the amount currently owed. Positive means the user owes the institution.

Empirically, this holds for the overwhelming majority of Plaid loan rows we see. But a small class of institutions ships the balance signed the OTHER way around: a -$250,364 line-of-credit / HELOC / mortgage means the user owes $250,364. We first saw it on Port Washington State Bank (8 mortgages under one user’s Business item), then found the same pattern on other HELOC/LOC feeds. Top institutions by aggregate mis-signed debt (as of 2026-07-19):

Institution	Rows	Aggregate mis-signed debt
Port Washington State Bank - Business	8	$1,280,615
Lowell Five Bank	3	$1,174,613
Goldman Sachs Private Wealth Management	2	$649,925
Nicolet National Bank - Business Online	2	$582,689
Unison Bank - Business	1	$290,344
Wells Fargo	3	$178,572
NexTier Bank	1	$88,799
Lyons National Bank	2	$65,229
Northwestern Mutual	1	$13,585
Other (14 institutions, mostly < $5k / row)	21	$4,653
Total	44	$4,329,024

Concentrated in subtype ∈ {mortgage, home equity, line of credit, loan}. Every Plaid type=loan AND subtype=mortgage row in the DB with a positive balance is fine — the flip is triggered by sign alone, not subtype.

2. Why this understates liabilities by 2×

Downstream aggregators everywhere (useAccounts, financial-data-cache, process-daily-snapshot, PDF export, the account_details view read by the AI execute_sql tool) sum the signed balance into a debt bucket:

totalDebts += account.balance_current   // Plaid: positive-for-debt

For a -$250,364 Port-Washington mortgage, that becomes:

totalDebts += (-250_364)   // ← now reduces total debt instead of adding to it

Net effect for a user with 9 Port-Wash mortgages totaling $1.28M in real debt:

Correct: totalDebts = +1_280_000 → net worth reduced by $1.28M
Bug: totalDebts = -1_280_000 → net worth inflated by $1.28M
Δ: $2.56M net-worth swing (2× the debt), one user

Multiplied across 32 users we found: ~$10M net-worth overstatement in aggregate, plus the AI execute_sql tool returning wrong “aggregate debt” and “net worth” answers when asked. Users noticed. The user who filed the bug had emailed us in the fall of 2025 and again in mid-2026; the dashboard consistently overstated her net worth even though the raw chat correctly summarized the mortgages. Chat was reading account_effective_balances, dashboard was reading the raw accounts.balance_current.

3. The strict predicate (why `type='loan' AND source_type='loan'`)

Not every account that looks like a loan should get the sign flipped. Two real override patterns in prod:

3a. Schwab margin-loan re-tagged as investment (`source_type='loan' → type='investment'`)

One user has a Charles Schwab “Loan Acct” that Plaid reports as type=loan, source_type=loan, balance -$859,613.93. The user overrode type='investment' / subtype='brokerage' in the app so the margin loan sits alongside their brokerage — the negative balance correctly reduces the portfolio value in their investments bucket. If we flipped that sign, we’d inflate their portfolio by $860k and skip the negative offset. So the flip must respect the user override.

3b. Plaid credit card re-tagged as loan (`source_type='credit' → type='loan'`)

A Plaid credit card can have a legitimately negative balance_current when the user has a credit balance / overpayment / statement credit (bank owes user). If a user re-tags such a row as type='loan' in the app, we must NOT flip the sign — the negative offset (-$100 debt = $100 offset) is real. So type='loan' alone is not enough. The predicate: flip only if BOTH the effective type === 'loan' AND the raw source_type === 'loan'. That opts out both overrides:

Case	`type`	`source_type`	Flip?	Rationale
Port-Wash mortgage (negative)	loan	loan	Yes	The bug we’re fixing
Chase mortgage (positive)	loan	loan	No-op	Positive already; the flip is guarded by `< 0`
Schwab margin loan → investment	invest	loan	No	User override wins; keeps -$860k in investments
Plaid credit card → loan	loan	credit	No	Preserves legitimate credit balance offset
Manual loan (no `source_type` column)	loan	NULL	No	Manual accounts already follow +/- convention; strict predicate opts out
SnapTrade (any type)	*	*	No	Short-circuited earlier — SnapTrade has its own convention

Only one row in prod hits the “type=loan, source_type ≠ loan” edge (a brokerage user overrode to line-of-credit, $4,280 balance). The strict predicate is deliberate: the user can re-tag that row as a manual loan to get the correct behavior, and we prefer that over silently flipping a sign the user didn’t ask us to flip.

4. The four normalization surfaces

Sign normalization is applied in every code path that aggregates a Plaid loan balance:

4a. Value / account form: `lib/liability-balance.ts`

Single source of truth. Two entry points:

normalizeLiabilityDebt(balance, isSnaptrade, { isPlaidLoan?: boolean }) — caller computes the predicate.
liabilityDebtAmount(account) — helper computes the predicate from account.type + account.source_type + account.is_snaptrade.

// The Plaid-loan sign flip runs BEFORE SnapTrade normalization so the two
// rules can't overlap — a SnapTrade account is never a Plaid loan.
if (!isSnaptrade && opts?.isPlaidLoan && signed < 0) {
  return -signed
}
const normalized = isSnaptrade ? -signed : signed
return normalized === 0 ? 0 : normalized   // avoid rendering "-$0.00"

Backed by 27 unit tests in lib/__tests__/liability-balance.test.ts, including regression tests for both override cases and the SnapTrade + Plaid

zero-balance edges.

4b. Dashboard: `hooks/queries/use-accounts.ts`

The useAccounts() hook’s rawTotalDebts reducer delegates to liabilityDebtAmount(account) for every liability row. Replaces the inline is_snaptrade ? -balance : balance pattern.

4c. Chat / SMS / email / PFS export: `lib/financial-data-cache.ts`

getFinancialMetrics() computes totalDebts for chat, SMS, email digests, and the PFS export. The Plaid liability loop calls normalizeLiabilityDebt with the strict predicate; the SnapTrade loop uses its own convention.

4d. Daily snapshots cron: `app/api/cron/process-daily-snapshot/route.ts`

Four sites in the cron aggregate totalDebtsForAccount:

Fresh sync path — intentionally uses updatedAccount.type === 'loan' (raw Plaid response type, not the stored/effective type). This preserves the net-worth impact of Schwab-style margin loans that the user overrode to investment: computePlaidInvestmentValue returns {marketValue: 0} for a negative-balance-no-holdings account, so the -$860k would silently contribute 0 to net worth otherwise. This is a documented trade-off; the comment at the site explains it. Fixing it properly requires refactoring computePlaidInvestmentValue and is deferred.
Fallback path (stored value when Plaid sync failed) — strict predicate.
Cached path (item already synced today) — strict predicate.
Visibility path (recomputes visible-only totals) — strict predicate.

4e. PDF export: `app/api/chat/tools/pdf-export-tool.ts`

Two sections consume Plaid loan balances:

Section 3 (Mortgages & Loans category subtotal) — strict predicate.
Section 5 (per-row loan detail) — strict predicate.

Both now use normalizeLiabilityDebt so the printed PFS matches the “Total Liabilities” figure computed by getFinancialMetrics.

5. Database view: `account_details`

The AI execute_sql tool (Silvia chat / radar / internal / proactive prompts / silvia-simulation) reads balances through the account_details view [documented in the silvia-database-views skill]. Before this fix Silvia would see the raw negative balance and repeat the wrong figure when asked about aggregate debt or net worth. Migration 20260719_01_normalize_plaid_loan_sign_in_account_details.sql adds two CASE WHEN branches to the Plaid subquery of the view:

CASE
    WHEN aeb.is_duplicate_cash_sweep THEN 0::numeric
    -- Sign-flip: Plaid loans (both type AND source_type) reported negative.
    WHEN a.type = 'loan'::text
         AND a.source_type = 'loan'::text
         AND COALESCE(a.balance_current_usd, a.balance_current / NULLIF(er.rate, 0::numeric)) < 0::numeric
      THEN - COALESCE(a.balance_current_usd, a.balance_current / NULLIF(er.rate, 0::numeric))
    ELSE COALESCE(a.balance_current_usd, a.balance_current / NULLIF(er.rate, 0::numeric))
END AS balance,

Same clause is added to balance_available. pre_dedup_balance is intentionally NOT sign-normalized — it is the audit column that exposes the pre-suppression raw value; consumers that want to know what Plaid actually returned should still see the negative.

5a. Verifying the migration is behavior-preserving

The migration replaces the live view definition wholesale, which is dangerous if the check-in copy of the base migration diverged from prod. We proved byte-level equivalence by creating a throwaway copy of the view under a different name and comparing all 25 columns row-by-row across all 192,241 rows:

Column	Rows differing
balance (the intended flip)	44 ← matches sign-flip prediction
balance_available (intended flip)	16 ← matches sign-flip prediction
clerk_id, account_name, mask, account_type, account_subtype, source_type, source_subtype, tax_treatment, currency_code, credit_limit, market_value, total_cost_basis, institution_name, last_synced_at, created_at, updated_at, snaptrade_usd_cash, snaptrade_crypto_value, snaptrade_cash_adjustment, pre_dedup_balance, is_duplicate_cash_sweep	0 each
Row count	192,241 = 192,241 ✅
Unmatched rows either side	0 ✅

5b. The soft-delete filter we caught along the way

Important gotcha: the base migration 20260707100000_dedup_cash_sweep_in_account_details.sql does NOT contain AND p.status = 'active' in the SnapTrade positions subqueries, but the LIVE prod view DOES — the soft-delete filter was added out-of-band as a sibling to the RPC filters shipped in 20260708_02_snaptrade_valuation_fns_status_filter.sql. Re-emitting the July-7 base verbatim would have silently regressed the ghost-position fix (~$55.8M across 8,139 rows / 716 accounts). We caught this by fetching pg_get_viewdef('public.account_details') and diffing against the base; the two AND p.status = 'active' predicates are carried forward in the new migration and the discrepancy is documented in its header. The two-view diff above (0 diffs on snaptrade_crypto_value / snaptrade_cash_adjustment) proves the filter is preserved.

5c. Why the view, not the raw table

We do this at read time rather than mutating accounts.balance_current for three reasons:

accounts.balance_current is the raw truth from Plaid. Mutating it would diverge our system’s state from Plaid’s, making sync reconciliation and audit harder.
The next Plaid sync would re-insert the negative value; a raw-table fix would silently regress on every sync.
A read-time transform lets us adjust the predicate (subtype, source_subtype, institution-scoped flags, feature flags) without a data migration.

6. End-to-end verification for the reporting user

user_33TM7kqEBv8k26hNMx7OW8GbywT has 15 liability rows total, 8 of which are the negative-signed Port-Washington mortgages the strict predicate flips:

Metric	Before fix	After fix
Sum of signed liability balances	-$540,173	+$2,021,057
Dashboard “Total Debts” (clamped at 0)	$0	$2,021,057
Dashboard net-worth impact	inflated by $2.56M	correct
AI `execute_sql` “aggregate debt” answer	wrong	correct
Chat / SMS financial summary	already correct	still correct
PDF Financial Statement Total Liabilities	wrong (Section 3 vs 5 diverged)	consistent

7. Testing & rollout

Helper tests (lib/__tests__/liability-balance.test.ts): 27 tests covering all four normalization branches plus the two override cases plus the SnapTrade / -0 / null-balance edges. All 27 pass.
Type check + lint: clean on all touched files.
Full test suite (npx vitest run): 215 test files / 2,823 tests total. Every touched-file test passes. Unrelated pre-existing failures on staging: three workflow/radar files fail with Cannot find module 'workflow' (import-resolution problem in an unrelated area) and one flaky/timeout test in chat-inline-citations.stories.test.ts / file-conversion.test.ts (both pre-existing on staging, verified by stashing this PR’s changes and re-running).
Migration safety: verified byte-level equivalence on prod via a throwaway public._account_details_test_20260719 view (see §5a). Test view was dropped immediately after.
Simulation vs prod (strict predicate: type='loan' AND source_type='loan' AND balance<0): 44 rows / 32 active users / $4,329,024 mis-signed debt correctly recognized; 0 unintended row-level changes across 192,241 rows. Net-worth swing across the fleet:$ 8,658,048 (2× the corrected debt).

Helper implementation: lib/liability-balance.ts
Helper tests: lib/__tests__/liability-balance.test.ts
Dashboard: hooks/queries/use-accounts.ts
Chat / SMS / email / PFS: lib/financial-data-cache.ts
Daily snapshot cron: app/api/cron/process-daily-snapshot/route.ts
PDF export tool: app/api/chat/tools/pdf-export-tool.ts
View migration: supabase/migrations/20260719_01_normalize_plaid_loan_sign_in_account_details.sql
Related fix (cash sweep dedup): BROKERAGE-CASH-SWEEP-DEDUP.md
PR: Silvia-Web #2890

Product Guide

Account

Help

PLAID LOAN SIGN NORMALIZATION

Plaid Loan Sign Normalization (Port Washington State Bank & HELOC/LOC quirk)

1. The problem

2. Why this understates liabilities by 2×

3. The strict predicate (why `type='loan' AND source_type='loan'`)

3a. Schwab margin-loan re-tagged as investment (`source_type='loan' → type='investment'`)

3b. Plaid credit card re-tagged as loan (`source_type='credit' → type='loan'`)

4. The four normalization surfaces

4a. Value / account form: `lib/liability-balance.ts`

4b. Dashboard: `hooks/queries/use-accounts.ts`

4c. Chat / SMS / email / PFS export: `lib/financial-data-cache.ts`

4d. Daily snapshots cron: `app/api/cron/process-daily-snapshot/route.ts`

4e. PDF export: `app/api/chat/tools/pdf-export-tool.ts`

5. Database view: `account_details`

5a. Verifying the migration is behavior-preserving

5b. The soft-delete filter we caught along the way

5c. Why the view, not the raw table

6. End-to-end verification for the reporting user

7. Testing & rollout

​Plaid Loan Sign Normalization (Port Washington State Bank & HELOC/LOC quirk)

​1. The problem

​2. Why this understates liabilities by 2×

​3. The strict predicate (why type='loan' AND source_type='loan')

​3a. Schwab margin-loan re-tagged as investment (source_type='loan' → type='investment')

​3b. Plaid credit card re-tagged as loan (source_type='credit' → type='loan')

​4. The four normalization surfaces

​4a. Value / account form: lib/liability-balance.ts

​4b. Dashboard: hooks/queries/use-accounts.ts

​4c. Chat / SMS / email / PFS export: lib/financial-data-cache.ts

​4d. Daily snapshots cron: app/api/cron/process-daily-snapshot/route.ts

​4e. PDF export: app/api/chat/tools/pdf-export-tool.ts

​5. Database view: account_details

​5a. Verifying the migration is behavior-preserving

​5b. The soft-delete filter we caught along the way

​5c. Why the view, not the raw table

​6. End-to-end verification for the reporting user

​7. Testing & rollout

​8. Related surfaces / references

Plaid Loan Sign Normalization (Port Washington State Bank & HELOC/LOC quirk)

1. The problem

2. Why this understates liabilities by 2×

3. The strict predicate (why `type='loan' AND source_type='loan'`)

3a. Schwab margin-loan re-tagged as investment (`source_type='loan' → type='investment'`)

3b. Plaid credit card re-tagged as loan (`source_type='credit' → type='loan'`)

4. The four normalization surfaces

4a. Value / account form: `lib/liability-balance.ts`

4b. Dashboard: `hooks/queries/use-accounts.ts`

4c. Chat / SMS / email / PFS export: `lib/financial-data-cache.ts`

4d. Daily snapshots cron: `app/api/cron/process-daily-snapshot/route.ts`

4e. PDF export: `app/api/chat/tools/pdf-export-tool.ts`

5. Database view: `account_details`

5a. Verifying the migration is behavior-preserving

5b. The soft-delete filter we caught along the way

5c. Why the view, not the raw table

6. End-to-end verification for the reporting user

7. Testing & rollout

8. Related surfaces / references