> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cfosilvia.com/llms.txt
> Use this file to discover all available pages before exploring further.

# PLAID LOAN SIGN NORMALIZATION

# 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`](../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](https://github.com/CFO-Silvia/Silvia-Web/pull/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:

```ts theme={null}
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`.

```typescript theme={null}
// 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`:

1. **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.
2. **Fallback path** (stored value when Plaid sync failed) — strict predicate.
3. **Cached path** (item already synced today) — strict predicate.
4. **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`](../supabase/migrations/20260719_01_normalize_plaid_loan_sign_in_account_details.sql)
adds two `CASE WHEN` branches to the Plaid subquery of the view:

```sql theme={null}
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:

1. `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.
2. The next Plaid sync would re-insert the negative value; a raw-table fix
   would silently regress on every sync.
3. 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).

## 8. Related surfaces / references

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


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.