← back to Norma
auto-save: 2026-07-23T07:19:17 (1 files) — scripts/gmail-backfill.mjs
e6864e6473e9e2fa667c70f509b18f8be62c427d · 2026-07-23 07:19:26 -0700 · Steve Abrams
Files touched
A scripts/gmail-backfill.mjs
Diff
commit e6864e6473e9e2fa667c70f509b18f8be62c427d
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Jul 23 07:19:26 2026 -0700
auto-save: 2026-07-23T07:19:17 (1 files) — scripts/gmail-backfill.mjs
---
scripts/gmail-backfill.mjs | 121 +++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 121 insertions(+)
diff --git a/scripts/gmail-backfill.mjs b/scripts/gmail-backfill.mjs
new file mode 100644
index 0000000..60faccc
--- /dev/null
+++ b/scripts/gmail-backfill.mjs
@@ -0,0 +1,121 @@
+#!/usr/bin/env node
+/**
+ * Full-history Gmail backfill for SDCC mailboxes → gmail_messages.
+ * Unlike /api/gmail/sync (200-msg cap, after:-cursor only), this pages the
+ * ENTIRE mailbox (default: 10 years back) and upserts every message.
+ *
+ * Usage: node scripts/gmail-backfill.mjs [--mailbox natalia@studentdebtcrisis.org] [--after 2016/07/23] [--dry]
+ */
+import { google } from 'googleapis';
+import pg from 'pg';
+import fs from 'node:fs';
+import path from 'node:path';
+import { fileURLToPath } from 'node:url';
+
+const ROOT = path.join(path.dirname(fileURLToPath(import.meta.url)), '..');
+
+// minimal .env.local loader (no dotenv dep guarantees)
+for (const line of fs.readFileSync(path.join(ROOT, '.env.local'), 'utf8').split('\n')) {
+ const m = line.match(/^([A-Z_][A-Z0-9_]*)=(.*)$/);
+ if (m && !(m[1] in process.env)) process.env[m[1]] = m[2].replace(/^"|"$/g, '');
+}
+
+const args = process.argv.slice(2);
+const argVal = (f, d) => { const i = args.indexOf(f); return i >= 0 ? args[i + 1] : d; };
+const MAILBOX = argVal('--mailbox', 'natalia@studentdebtcrisis.org');
+const AFTER = argVal('--after', '2016/07/23');
+const DRY = args.includes('--dry');
+
+const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
+
+function getHeader(headers, name) {
+ return headers?.find(h => h.name?.toLowerCase() === name.toLowerCase())?.value || '';
+}
+function walkParts(payload, mime) {
+ if (!payload) return '';
+ if (payload.mimeType === mime && payload.body?.data)
+ return Buffer.from(payload.body.data, 'base64url').toString('utf8');
+ for (const p of payload.parts || []) {
+ const r = walkParts(p, mime);
+ if (r) return r;
+ }
+ return '';
+}
+
+async function main() {
+ const { rows } = await pool.query(
+ 'SELECT id, oauth_tokens FROM mailboxes WHERE LOWER(email)=LOWER($1)', [MAILBOX]);
+ if (!rows.length || !rows[0].oauth_tokens) {
+ console.error(`FATAL: no stored OAuth tokens for ${MAILBOX} — needs browser re-auth.`);
+ process.exit(2);
+ }
+ const mailboxId = rows[0].id;
+
+ const oauth2 = new google.auth.OAuth2(
+ process.env.GMAIL_CLIENT_ID, process.env.GMAIL_CLIENT_SECRET,
+ process.env.GMAIL_REDIRECT_URI);
+ oauth2.setCredentials(rows[0].oauth_tokens);
+ oauth2.on('tokens', async (t) => {
+ await pool.query(
+ `UPDATE mailboxes SET oauth_tokens = COALESCE(oauth_tokens,'{}'::jsonb) || $2::jsonb,
+ needs_reauth = FALSE, updated_at = NOW() WHERE id = $1`,
+ [mailboxId, JSON.stringify(t)]).catch(e => console.error('token persist:', e.message));
+ });
+
+ // Force a refresh up front so an invalid_grant fails fast and loud.
+ try {
+ await oauth2.getAccessToken();
+ console.log('token refresh: OK');
+ } catch (e) {
+ console.error(`FATAL: token refresh failed (${e.message}) — ${MAILBOX} needs browser re-auth at /api/gmail/oauth`);
+ await pool.query('UPDATE mailboxes SET needs_reauth = TRUE, updated_at = NOW() WHERE id = $1', [mailboxId]);
+ process.exit(3);
+ }
+
+ const gmail = google.gmail({ version: 'v1', auth: oauth2 });
+ const q = `after:${AFTER}`;
+ let pageToken, fetched = 0, inserted = 0, skipped = 0, failed = 0;
+
+ do {
+ const list = await gmail.users.messages.list({ userId: 'me', maxResults: 100, q, pageToken });
+ const stubs = list.data.messages || [];
+ pageToken = list.data.nextPageToken || undefined;
+ if (!stubs.length) break;
+
+ for (const stub of stubs) {
+ fetched++;
+ if (DRY) continue;
+ try {
+ const { data: d } = await gmail.users.messages.get({ userId: 'me', id: stub.id, format: 'full' });
+ const headers = d.payload?.headers;
+ const toRaw = getHeader(headers, 'To');
+ const ccRaw = getHeader(headers, 'Cc');
+ const dateSent = getHeader(headers, 'Date');
+ const res = await pool.query(
+ `INSERT INTO gmail_messages
+ (mailbox_id, gmail_id, thread_id, subject, from_address, to_addresses,
+ cc_addresses, date_sent, snippet, body_text, body_html, labels, attachments, is_read)
+ VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
+ ON CONFLICT (mailbox_id, gmail_id) DO NOTHING
+ RETURNING id`,
+ [mailboxId, d.id, d.threadId, getHeader(headers, 'Subject'), getHeader(headers, 'From'),
+ toRaw ? toRaw.split(',').map(a => a.trim()) : [],
+ ccRaw ? ccRaw.split(',').map(a => a.trim()) : [],
+ dateSent ? new Date(dateSent) : null,
+ d.snippet, walkParts(d.payload, 'text/plain') || null, walkParts(d.payload, 'text/html') || null,
+ d.labelIds || [], JSON.stringify([]), !(d.labelIds || []).includes('UNREAD')]);
+ res.rowCount ? inserted++ : skipped++;
+ } catch (e) {
+ failed++;
+ if (failed <= 5) console.error(`msg ${stub.id}: ${e.message}`);
+ }
+ }
+ console.log(`progress: fetched=${fetched} inserted=${inserted} skipped=${skipped} failed=${failed}`);
+ } while (pageToken);
+
+ await pool.query('UPDATE mailboxes SET last_synced_at = NOW(), updated_at = NOW() WHERE id = $1', [mailboxId]);
+ console.log(`DONE ${MAILBOX}: fetched=${fetched} inserted=${inserted} skipped=${skipped} failed=${failed}`);
+ await pool.end();
+}
+
+main().catch(e => { console.error('FATAL:', e.message); process.exit(1); });
← 05b28cd instagram-agent: env-configurable Graph host (IG_GRAPH_HOST)
·
back to Norma
·
auto-save: 2026-07-23T08:19:35 (2 files) — agents/instagram- be52823 →