Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt

Use this file to discover all available pages before exploring further.

Date: 2026-02-09
Related Spec: FA-20 (Plaid Bank Feed Integration)
Branch: cursor/plaid-balances-transactions-7b69

​
Executive Summary

After thorough review of the Plaid integration code against the Plaid API documentation and the database schema, 7 critical bugs were identified that collectively prevent balances and transactions from being posted. All bugs have been fixed in this branch. The root cause is a schema-code mismatch: the edge functions were written assuming columns and constraints that don’t exist on the actual database tables. The code was never validated against the live schema after the initial migration.

​
Bugs Found & Fixed

​
Bug 1: Missing status Column on fa_bank_statement_lines (CRITICAL)

Impact: Every transaction insert/update fails silently
Location: supabase/functions/plaid-sync/index.ts lines 311, 355, 376
Location: supabase/functions/plaid-webhook/index.ts line 229 Problem:
The plaid-sync edge function wrote status: normalized.is_pending ? 'pending' : 'posted' and the webhook handler wrote status: 'removed', but the fa_bank_statement_lines table has no status column. The original table creation (migration 20251127) defines only: id, statement_id, line_number, transaction_date, post_date, description, amount, transaction_type, check_number, reference_number, is_matched, matched_at, custom_fields. Subsequent migrations added organization_id, teller_transaction_id, and plaid_transaction_id but never a status column. When Supabase receives an upsert with an unknown column, the operation fails with a Postgres error. Fix:
Moved the pending/posted/removed status into custom_fields.plaid_status. This follows the existing pattern where custom_fields JSONB stores provider-specific metadata.

​
Bug 2: transaction_type CHECK Constraint Violation (CRITICAL)

Impact: Every transaction insert fails with constraint violation
Location: supabase/functions/_shared/plaid-client.ts function mapTransactionType() Problem:
The mapTransactionType() function returned rich types ('check', 'ach', 'pos', 'fee', 'interest', 'transfer') based on Plaid transaction characteristics, but fa_bank_statement_lines.transaction_type originally had a CHECK constraint limited to CHECK (transaction_type IN ('debit', 'credit')). Any value other than ‘debit’ or ‘credit’ caused a constraint violation. Fix:
Expanded the CHECK constraint on fa_bank_statement_lines.transaction_type to support the full set of types: 'debit', 'credit', 'check', 'ach', 'wire', 'pos', 'fee', 'interest', 'transfer'. The mapTransactionType() function now returns these richer types directly. Additional provider-specific metadata (e.g., check numbers) is stored in custom_fields.check_number.

​
Bug 3: Column Name Mismatch current_balance vs balance (CRITICAL)

Impact: Balance not saved when connecting Plaid accounts
Location: supabase/functions/plaid-exchange-token/index.ts line 211 Problem:
The token exchange function wrote current_balance: currentBalance || 0 but the actual column on fa_bank_accounts is named balance (not current_balance). This means the initial balance from Plaid was silently dropped. Fix:
Changed to balance: currentBalance || 0.

​
Bug 4: Missing Required NOT NULL Fields (CRITICAL)

Impact: Account creation fails with NOT NULL constraint violation
Location: supabase/functions/plaid-exchange-token/index.ts lines 203-222 Problem:
The fa_bank_accounts table requires:
  • account_number VARCHAR(50) NOT NULL
  • gl_account_id UUID NOT NULL REFERENCES fa_accounts(id)
The Plaid token exchange function didn’t provide either. It wrote last_four: lastFour (which is not a column on fa_bank_accounts) and completely omitted gl_account_id. Fix:
  • Set account_number to lastFour || '0000' (the last 4 digits of the bank account, or a placeholder)
  • Auto-discover a GL cash account by looking for active accounts with “cash” in the name, falling back to any active account

​
Bug 5: account_type CHECK Constraint Violation (CRITICAL)

Impact: Account creation fails for credit, investment, and loan accounts
Location: supabase/functions/plaid-exchange-token/index.ts function mapPlaidAccountType() Problem:
The original mapper returned values like 'credit_card', 'money_market', 'investment', 'loan', 'other' but the CHECK constraint only allows 'checking' | 'savings' | 'payroll' | 'reserve'. Fix:
All Plaid types now map to one of the four allowed values:
  • depository/checking → checking
  • depository/savings → savings
  • depository/money market → savings
  • credit → checking (safe default)
  • loan → reserve
  • investment → reserve

​
Bug 6: Frontend Response Field Name Mismatch (HIGH)

Impact: Frontend always shows 0 transactions synced even when sync succeeds
Location: src/platform/banking/providers/plaid/usePlaidSync.ts lines 73-79 Problem:
The edge function returns snake_case JSON keys (transactions_added, transactions_modified, transactions_removed, has_more) but the frontend hook tried to read camelCase properties (transactionsAdded, transactionsModified, etc.). This meant the result always showed 0 changes. Fix:
Added fallback to read both snake_case and camelCase: data?.transactions_added ?? data?.transactionsAdded ?? 0.

​
Bug 7: Balance Never Updated After Sync (HIGH)

Impact: Bank balance card always shows initial balance, never refreshes
Location: supabase/functions/plaid-sync/index.ts Problem:
The sync function only updated plaid_cursor and plaid_last_synced_at after syncing transactions, but never fetched or updated the account balance. Plaid’s /transactions/sync endpoint doesn’t include balance data — you must separately call /accounts/get to get current balances. Fix:
After syncing transactions, the function now calls getAccounts() with the access token to fetch fresh balance data, then updates fa_bank_accounts.balance. The balance is also included in the sync response for the frontend to display.

​
Recommendations for Improvement

All 10 recommendations have been implemented. See commits on branch cursor/plaid-balances-transactions-7b69.

​
1. Add a status Column to fa_bank_statement_lines — IMPLEMENTED

Migration 20260209120000_plaid-schema-improvements.sql adds the column with CHECK constraint. Edge functions now write directly to status: 
ALTER TABLE fa_bank_statement_lines
  ADD COLUMN IF NOT EXISTS status TEXT DEFAULT 'posted'
  CHECK (status IN ('pending', 'posted', 'removed'));
This would enable:
  • Filtering out pending/removed transactions in reconciliation
  • Proper bank rec matching on only posted transactions
  • Querying removed transactions for audit purposes

​
2. Expand account_type CHECK Constraint — IMPLEMENTED

Migration adds money_market, credit_card, loan, investment, other. The plaid-exchange-token mapper now uses these values directly: 
ALTER TABLE fa_bank_accounts
  DROP CONSTRAINT IF EXISTS fa_bank_accounts_account_type_check;
  
ALTER TABLE fa_bank_accounts
  ADD CONSTRAINT fa_bank_accounts_account_type_check
  CHECK (account_type IN (
    'checking', 'savings', 'payroll', 'reserve',
    'money_market', 'credit_card', 'loan', 'investment', 'other'
  ));

​
3. Expand transaction_type for Bank Statement Lines — IMPLEMENTED

Migration widens the column to TEXT and adds check, ach, wire, pos, fee, interest, transfer. mapTransactionType() now returns these richer types: 
ALTER TABLE fa_bank_statement_lines
  ALTER COLUMN transaction_type TYPE TEXT;

ALTER TABLE fa_bank_statement_lines
  DROP CONSTRAINT IF EXISTS fa_bank_statement_lines_transaction_type_check;

ALTER TABLE fa_bank_statement_lines
  ADD CONSTRAINT fa_bank_statement_lines_transaction_type_check
  CHECK (transaction_type IN ('debit', 'credit', 'check', 'ach', 'wire', 'pos', 'fee', 'interest', 'transfer'));

​
4. Auto-Sync on Webhook (Instead of Just Logging) — IMPLEMENTED

The plaid-webhook handler now performs a full incremental sync when SYNC_UPDATES_AVAILABLE is received and plaid_sync_enabled is true. The autoSyncTransactions() function handles the full flow including balance refresh and sync logging: 
// In plaid-webhook handler for SYNC_UPDATES_AVAILABLE:
// Option A: Direct internal sync call
await fetch(`${supabaseUrl}/functions/v1/plaid-sync`, {
  method: 'POST',
  headers: {
    Authorization: `Bearer ${supabaseServiceKey}`,
    'Content-Type': 'application/json',
  },
  body: JSON.stringify({
    bank_account_id: bankAccount.id,
    organization_id: bankAccount.organization_id,
  }),
});

// Option B: Queue for deferred sync (better for rate limiting)
await supabase.from('fa_plaid_sync_queue').insert({
  bank_account_id: bankAccount.id,
  organization_id: bankAccount.organization_id,
  requested_at: new Date().toISOString(),
});

​
5. Plaid Balance Refresh Should Use /accounts/balance/get — IMPLEMENTED

The getBalances() function has been added to plaid-client.ts and is now used by plaid-sync, plaid-webhook auto-sync, and plaid-scheduled-sync. Benefits:
  • Faster (real-time balance from the bank)
  • Doesn’t count against the same rate limits
  • Returns available and current balances
export async function getBalances(
  accessToken: string
): Promise<{ data?: PlaidAccountsGetResponse; error?: PlaidError; status: number }> {
  return plaidRequest<PlaidAccountsGetResponse>('/accounts/balance/get', {
    access_token: accessToken,
  });
}

​
6. GL Account Association Needs User Selection — IMPLEMENTED

After Plaid multi-account connection, BankAccountsPage refreshes the list and shows a toast prompting the user to review GL account assignments. Users click Edit on each account to change the GL mapping. The auto-discovery still works as a sensible default:
  • After Plaid connection, present a dialog asking the user to select/create a GL account for each bank account
  • Store the mapping in fa_bank_accounts.gl_account_id
  • The MultiAccountSetupDialog already exists for Teller but doesn’t appear to be used for Plaid connections

​
7. Scheduled Balance Sync — IMPLEMENTED

New plaid-scheduled-sync edge function that iterates all connected Plaid accounts, refreshes balances via /accounts/balance/get, and optionally syncs transactions. Recommended cron: 0 */4 * * *: 
// supabase/functions/plaid-scheduled-sync/index.ts
// Runs every 4 hours via Supabase cron
// For each connected Plaid account:
//   1. Call /accounts/balance/get
//   2. Update fa_bank_accounts.balance
//   3. Optionally call /transactions/sync if auto_sync is enabled

​
8. Add updated_at to fa_bank_statement_lines — IMPLEMENTED

Migration adds updated_at TIMESTAMPTZ DEFAULT now() column and set_fa_bank_statement_lines_updated_at trigger using the shared update_updated_at_column() function. Now tracks when Plaid transactions were last modified.

​
9. Error Recovery and Retry Queue — IMPLEMENTED

New fa_plaid_sync_log table tracks every sync attempt with status, error codes, retry count, and timing. All sync paths (manual, webhook, scheduled) now create log entries. The table supports a retry queue pattern via status='retrying' and next_retry_at:
  • A fa_plaid_sync_log table tracking sync attempts, results, and errors
  • Automatic retry with exponential backoff for transient failures
  • User notification when sync has been failing for >24 hours

​
10. Plaid Products: Add auth for Account Verification — READY

The infrastructure already supports requesting auth product. The PlaidLinkOptions.products field accepts string[], and the plaid-create-link-token edge function passes it through. To enable, pass products={['transactions', 'auth']} to usePlaidLink or PlaidConnectButton. Benefits:
  • Full account and routing number verification
  • Micro-deposit verification as fallback
  • Direct deposit setup from Plaid (for HR-PAY-03)
This would also feed into the hr_employee_bank_accounts.verification_method = 'plaid' flow.

​
Testing Recommendations

  1. Schema validation test: Add a test that verifies the columns written by each edge function actually exist on the target table
  2. Constraint validation test: Verify that all mapped enum values satisfy CHECK constraints
  3. End-to-end sandbox test: Connect a Plaid sandbox account, sync transactions, verify they appear in fa_bank_statement_lines, verify balance updates on fa_bank_accounts
  4. Webhook integration test: Simulate SYNC_UPDATES_AVAILABLE and TRANSACTIONS_REMOVED webhooks, verify correct handling

​
Files Changed

FileChanges
supabase/functions/_shared/plaid-client.tsFixed mapTransactionType() to only return ‘debit’/‘credit’
supabase/functions/plaid-sync/index.tsRemoved status field, fixed transaction_type, added balance fetch, sequential line numbers
supabase/functions/plaid-exchange-token/index.tsFixed column names, added required fields, fixed account type mapping
supabase/functions/plaid-webhook/index.tsFixed TRANSACTIONS_REMOVED handler to use custom_fields
src/platform/banking/providers/plaid/usePlaidSync.tsFixed snake_case response parsing, added balance display

​
References