← back to AbramsOS
connectors: Plaid 'Connect a bank' UI (Wells Fargo etc.) + incremental/idempotent sync
954cc77443950c65314dc853f3939648b13bcf04 · 2026-07-13 00:38:05 -0700 · Steve
- views/connectors.ejs: Plaid Link flow — connect button, secure login (Plaid handles bank creds), exchange, per-bank sync; graceful 'setup needed' state when keys absent
- routes/plaid.js: GET /api/plaid/status; sync now resumes from persisted cursor + ON CONFLICT dedup (was re-pulling everything each sync -> dup purchases)
- lib/plaid-client.js: isConfigured() helper
- 0012_plaid_sync_cursor.sql: connector_account.sync_cursor + unique(user_id,order_number) on purchase
- Plaid (not Stripe) is correct for reading a bank; sandbox works now, real Wells Fargo needs Steve's prod keys
Files touched
A db/migrations/0012_plaid_sync_cursor.sqlM lib/plaid-client.jsM routes/plaid.jsM views/connectors.ejs
Diff
commit 954cc77443950c65314dc853f3939648b13bcf04
Author: Steve <steve@designerwallcoverings.com>
Date: Mon Jul 13 00:38:05 2026 -0700
connectors: Plaid 'Connect a bank' UI (Wells Fargo etc.) + incremental/idempotent sync
- views/connectors.ejs: Plaid Link flow — connect button, secure login (Plaid handles bank creds), exchange, per-bank sync; graceful 'setup needed' state when keys absent
- routes/plaid.js: GET /api/plaid/status; sync now resumes from persisted cursor + ON CONFLICT dedup (was re-pulling everything each sync -> dup purchases)
- lib/plaid-client.js: isConfigured() helper
- 0012_plaid_sync_cursor.sql: connector_account.sync_cursor + unique(user_id,order_number) on purchase
- Plaid (not Stripe) is correct for reading a bank; sandbox works now, real Wells Fargo needs Steve's prod keys
---
db/migrations/0012_plaid_sync_cursor.sql | 17 ++++++++
lib/plaid-client.js | 6 ++-
routes/plaid.js | 28 +++++++++----
views/connectors.ejs | 67 ++++++++++++++++++++++++++++++++
4 files changed, 110 insertions(+), 8 deletions(-)
diff --git a/db/migrations/0012_plaid_sync_cursor.sql b/db/migrations/0012_plaid_sync_cursor.sql
new file mode 100644
index 0000000..343a5b0
--- /dev/null
+++ b/db/migrations/0012_plaid_sync_cursor.sql
@@ -0,0 +1,17 @@
+-- 0012_plaid_sync_cursor.sql
+-- Make bank (Plaid) sync incremental + idempotent:
+-- • connector_account.sync_cursor — persist Plaid's transactions/sync cursor so each
+-- sync only pulls what's NEW (without it, every sync re-pulled everything → dup rows).
+-- • unique (user_id, order_number) on purchase — Plaid stores its stable transaction_id
+-- in order_number; this lets the sync ON CONFLICT DO NOTHING so a transaction never
+-- lands twice even across cursor resets.
+-- Idempotent. Safe to re-run.
+
+BEGIN;
+
+ALTER TABLE connector_account ADD COLUMN IF NOT EXISTS sync_cursor text;
+
+CREATE UNIQUE INDEX IF NOT EXISTS purchase_user_order_uidx
+ ON purchase (user_id, order_number) WHERE order_number IS NOT NULL;
+
+COMMIT;
diff --git a/lib/plaid-client.js b/lib/plaid-client.js
index fb453be..f1fe276 100644
--- a/lib/plaid-client.js
+++ b/lib/plaid-client.js
@@ -19,4 +19,8 @@ function client() {
const PRODUCTS = [Products.Transactions];
const COUNTRIES = [CountryCode.Us];
-module.exports = { client, PRODUCTS, COUNTRIES };
+function isConfigured() {
+ return Boolean(process.env.PLAID_CLIENT_ID && process.env.PLAID_SECRET);
+}
+
+module.exports = { client, isConfigured, PRODUCTS, COUNTRIES };
diff --git a/routes/plaid.js b/routes/plaid.js
index 8e5c80e..d5b62f1 100644
--- a/routes/plaid.js
+++ b/routes/plaid.js
@@ -8,6 +8,17 @@ const plaidLib = require('../lib/plaid-client');
const router = express.Router();
const DEV_USER_ID = 'user_steve';
+// 0) Is Plaid wired up? Lets the UI show "Connect a bank" vs a setup hint instead of 500ing.
+router.get('/api/plaid/status', async (_req, res) => {
+ const configured = plaidLib.isConfigured();
+ const banks = await db.query(
+ `SELECT id, external_subject_id, last_sync_at, created_at
+ FROM connector_account WHERE user_id = $1 AND provider = 'plaid' ORDER BY created_at DESC`,
+ [DEV_USER_ID]
+ );
+ res.json({ configured, env: process.env.PLAID_ENV || 'sandbox', banks: banks.rows });
+});
+
// 1) Browser asks for a Link token; we hand one back, they open Plaid Link in their browser.
router.post('/api/plaid/link/token', async (req, res) => {
try {
@@ -79,7 +90,7 @@ router.post('/api/plaid/exchange', async (req, res) => {
router.post('/api/connectors/plaid/:id/sync', async (req, res) => {
const connectorId = req.params.id;
const r = await db.query(
- `SELECT refresh_token_encrypted, refresh_token_iv, refresh_token_tag
+ `SELECT refresh_token_encrypted, refresh_token_iv, refresh_token_tag, sync_cursor
FROM connector_account WHERE id = $1 AND user_id = $2 AND provider = 'plaid'`,
[connectorId, DEV_USER_ID]
);
@@ -97,12 +108,12 @@ router.post('/api/connectors/plaid/:id/sync', async (req, res) => {
let purchases = 0;
try {
const c = plaidLib.client();
- // Use /transactions/sync (the modern incremental cursor endpoint)
- let cursor = null;
+ // /transactions/sync — resume from the saved cursor so we only pull what's NEW.
+ let cursor = r.rows[0].sync_cursor || null;
let added = [];
let hasMore = true;
let calls = 0;
- while (hasMore && calls < 5) { // cap at 5 pages per sync to stay under Plaid limits
+ while (hasMore && calls < 20) { // safety cap; cursor is persisted so the next sync resumes
const resp = await c.transactionsSync({ access_token: accessToken, cursor: cursor || undefined });
added = added.concat(resp.data.added || []);
cursor = resp.data.next_cursor;
@@ -114,10 +125,12 @@ router.post('/api/connectors/plaid/:id/sync', async (req, res) => {
// Plaid amounts are positive for outflow (purchase), negative for inflow (refund/deposit).
if (tx.amount == null || tx.amount <= 0) continue;
const purchaseId = id('purchase');
- await db.query(
+ const ins = await db.query(
`INSERT INTO purchase
(id, user_id, source_message_id, merchant_name, merchant_domain, order_number, purchase_date, total_amount, currency, confidence, raw_extract)
- VALUES ($1, $2, NULL, $3, $4, $5, $6, $7, $8, $9, $10)`,
+ VALUES ($1, $2, NULL, $3, $4, $5, $6, $7, $8, $9, $10)
+ ON CONFLICT (user_id, order_number) WHERE order_number IS NOT NULL DO NOTHING
+ RETURNING id`,
[
purchaseId,
DEV_USER_ID,
@@ -131,6 +144,7 @@ router.post('/api/connectors/plaid/:id/sync', async (req, res) => {
tx,
]
);
+ if (!ins.rows.length) continue; // already had this transaction
purchases += 1;
await audit.log({
actorType: 'system',
@@ -141,7 +155,7 @@ router.post('/api/connectors/plaid/:id/sync', async (req, res) => {
});
}
- await db.query(`UPDATE connector_account SET last_sync_at = now() WHERE id = $1`, [connectorId]);
+ await db.query(`UPDATE connector_account SET last_sync_at = now(), sync_cursor = $2 WHERE id = $1`, [connectorId, cursor]);
await audit.log({
actorType: 'system',
objectType: 'connector_account',
diff --git a/views/connectors.ejs b/views/connectors.ejs
index 80907c0..5dd3aae 100644
--- a/views/connectors.ejs
+++ b/views/connectors.ejs
@@ -5,6 +5,18 @@
<a class="button primary" href="/auth/google/start">Connect another Gmail</a>
</section>
+<section class="glass" id="bankSection" style="padding:1.25rem 1.5rem;margin-bottom:1rem">
+ <div style="display:flex;align-items:center;justify-content:space-between;gap:1rem;flex-wrap:wrap">
+ <div>
+ <h2 style="margin:.2rem 0">Banks & cards</h2>
+ <p class="subtle" style="margin:0">Connect Wells Fargo (or any US bank) via <strong>Plaid</strong> — a secure read-only link that pulls transactions into your purchases, bills & savings. AbramsOS never sees your bank password; Plaid handles the login.</p>
+ </div>
+ <div id="bankAction"><span class="subtle">checking…</span></div>
+ </div>
+ <div id="bankList" style="margin-top:.75rem"></div>
+ <div id="bankStatus" class="subtle" style="margin-top:.5rem"></div>
+</section>
+
<% if (!connectors.length) { %>
<section class="empty glass">
<p>No connectors yet. <a href="/auth/google/start">Connect Gmail</a> to start ingesting receipts.</p>
@@ -58,4 +70,59 @@
});
</script>
+<script src="https://cdn.plaid.com/link/v2/stable/link-initialize.js"></script>
+<script>
+(async function bank(){
+ const action=document.getElementById('bankAction'), list=document.getElementById('bankList'), statusEl=document.getElementById('bankStatus');
+ const say=(m)=>{ statusEl.textContent=m||''; };
+ let st;
+ try { st=await (await fetch('/api/plaid/status')).json(); }
+ catch(e){ action.innerHTML='<span class="subtle">status unavailable</span>'; return; }
+
+ if(!st.configured){
+ action.innerHTML='<span class="badge">setup needed</span>';
+ say('Add PLAID_CLIENT_ID + PLAID_SECRET (free sandbox keys at dashboard.plaid.com) via the secrets skill, then reload. Production keys are needed to link a real Wells Fargo account.');
+ return;
+ }
+ action.innerHTML='<button class="button primary" id="connectBank">+ Connect a bank</button>'
+ + ' <span class="subtle">('+st.env+')</span>';
+
+ // Render connected banks
+ if(st.banks && st.banks.length){
+ list.innerHTML='<div class="connector-list">'+st.banks.map(b=>
+ '<article class="connector glass"><header><h3>Bank · '+(b.external_subject_id||'').slice(0,10)+'…</h3></header>'
+ +'<dl class="meta"><dt>Linked</dt><dd>'+new Date(b.created_at).toLocaleString()+'</dd>'
+ +'<dt>Last sync</dt><dd>'+(b.last_sync_at?new Date(b.last_sync_at).toLocaleString():'never')+'</dd></dl>'
+ +'<button class="button" data-bank-sync="'+b.id+'">Sync transactions</button> <span data-bsync="'+b.id+'" class="subtle"></span></article>'
+ ).join('')+'</div>';
+ list.querySelectorAll('[data-bank-sync]').forEach(btn=>btn.addEventListener('click',async()=>{
+ const id=btn.dataset.bankSync, s=list.querySelector('[data-bsync="'+id+'"]'); btn.disabled=true; s.textContent='Syncing…';
+ try{ const j=await (await fetch('/api/connectors/plaid/'+id+'/sync',{method:'POST'})).json();
+ s.textContent=j.ok?('fetched '+j.fetched+', +'+j.purchases+' purchases'):('error: '+(j.error||'?')); }
+ catch(e){ s.textContent='error: '+e.message; } finally{ btn.disabled=false; }
+ }));
+ }
+
+ document.getElementById('connectBank').addEventListener('click',async()=>{
+ say('Opening secure Plaid login…');
+ let token;
+ try{ const r=await (await fetch('/api/plaid/link/token',{method:'POST',headers:{'Content-Type':'application/json'},body:'{}'})).json();
+ if(r.error){ say('Plaid error: '+r.error); return; } token=r.link_token; }
+ catch(e){ say('Could not start Plaid: '+e.message); return; }
+ const handler=Plaid.create({
+ token,
+ onSuccess: async (public_token, metadata)=>{
+ say('Linking '+((metadata&&metadata.institution&&metadata.institution.name)||'bank')+'…');
+ const x=await (await fetch('/api/plaid/exchange',{method:'POST',headers:{'Content-Type':'application/json'},
+ body:JSON.stringify({public_token, institution_name:(metadata&&metadata.institution&&metadata.institution.name)||null})})).json();
+ if(x.ok){ say('Linked! Syncing…'); await fetch('/api/connectors/plaid/'+x.connector_id+'/sync',{method:'POST'}); location.reload(); }
+ else say('Link failed: '+(x.error||'?'));
+ },
+ onExit: (err)=>{ if(err) say('Closed: '+(err.display_message||err.error_message||'')); }
+ });
+ handler.open();
+ });
+})();
+</script>
+
<%- include('partials/footer') %>
← f4e4da6 docs: roadmap adds health track (vitals trends, BP↔meds, rem
·
back to AbramsOS
·
docs: roadmap — banking (Plaid) track shipped; real-bank key a71abd4 →