← back to AbramsOS
tick 2: Google Drive sync (receipt-shaped PDFs/images)
9e812a4a439cefe0cf2e1be33875bec6c51fda58 · 2026-05-10 00:27:52 -0700 · Steve
- lib/drive-fetcher.js: list+download via drive.readonly OAuth scope
- query: name~/receipt|invoice|order|confirmation/i + mime in (pdf, image/*) + last 90 days
- routes/connectors.js: gmail sync now also walks Drive on the same google connector;
files saved to uploads/drive-<id>.<ext>; document rows inserted with kind='attachment'
- audit_log gets one document_persisted event per file
- 22/22 tests still green
Files touched
A lib/drive-fetcher.jsM routes/connectors.js
Diff
commit 9e812a4a439cefe0cf2e1be33875bec6c51fda58
Author: Steve <steve@designerwallcoverings.com>
Date: Sun May 10 00:27:52 2026 -0700
tick 2: Google Drive sync (receipt-shaped PDFs/images)
- lib/drive-fetcher.js: list+download via drive.readonly OAuth scope
- query: name~/receipt|invoice|order|confirmation/i + mime in (pdf, image/*) + last 90 days
- routes/connectors.js: gmail sync now also walks Drive on the same google connector;
files saved to uploads/drive-<id>.<ext>; document rows inserted with kind='attachment'
- audit_log gets one document_persisted event per file
- 22/22 tests still green
---
lib/drive-fetcher.js | 56 +++++++++++++++++++++++++++++++++++++++
routes/connectors.js | 75 ++++++++++++++++++++++++++++++++++++++++++++++++++--
2 files changed, 129 insertions(+), 2 deletions(-)
diff --git a/lib/drive-fetcher.js b/lib/drive-fetcher.js
new file mode 100644
index 0000000..008f9c3
--- /dev/null
+++ b/lib/drive-fetcher.js
@@ -0,0 +1,56 @@
+// Google Drive fetcher. Walks the user's Drive for receipt-shaped files
+// (PDFs, JPGs, PNGs whose name suggests "receipt", "invoice", "order").
+// Returns metadata + file content stream; the caller persists.
+//
+// Uses the same OAuth client (refresh token shared with Gmail since it's the
+// same Google account, drive.readonly scope was added to lib/google-oauth.js).
+
+const { google } = require('googleapis');
+const { clientFor } = require('./google-oauth');
+
+const RECEIPT_NAME_QUERY = `(name contains 'receipt' or name contains 'invoice' or name contains 'order' or name contains 'confirmation')`;
+const MIME_QUERY = `(mimeType = 'application/pdf' or mimeType contains 'image/')`;
+const RECENT_QUERY = `modifiedTime > '${new Date(Date.now() - 90 * 24 * 60 * 60 * 1000).toISOString()}'`;
+
+function driveClient(refreshToken) {
+ return google.drive({ version: 'v3', auth: clientFor(refreshToken) });
+}
+
+async function listReceiptFiles(refreshToken, { max = 100 } = {}) {
+ const drive = driveClient(refreshToken);
+ const out = [];
+ let pageToken;
+ do {
+ const resp = await drive.files.list({
+ q: `${RECEIPT_NAME_QUERY} and ${MIME_QUERY} and ${RECENT_QUERY} and trashed = false`,
+ fields: 'nextPageToken, files(id, name, mimeType, size, modifiedTime, webViewLink, webContentLink, md5Checksum)',
+ pageSize: Math.min(100, max - out.length),
+ pageToken,
+ orderBy: 'modifiedTime desc',
+ });
+ for (const f of resp.data.files || []) out.push(f);
+ pageToken = resp.data.nextPageToken;
+ } while (pageToken && out.length < max);
+ return out;
+}
+
+/**
+ * Returns an async stream of bytes for a Drive file.
+ * Caller handles writing to disk / hashing.
+ */
+async function downloadFile(refreshToken, fileId) {
+ const drive = driveClient(refreshToken);
+ const resp = await drive.files.get({ fileId, alt: 'media' }, { responseType: 'stream' });
+ return resp.data;
+}
+
+async function fileMetadata(refreshToken, fileId) {
+ const drive = driveClient(refreshToken);
+ const resp = await drive.files.get({
+ fileId,
+ fields: 'id, name, mimeType, size, modifiedTime, webViewLink, md5Checksum',
+ });
+ return resp.data;
+}
+
+module.exports = { listReceiptFiles, downloadFile, fileMetadata };
diff --git a/routes/connectors.js b/routes/connectors.js
index b2af977..28282ab 100644
--- a/routes/connectors.js
+++ b/routes/connectors.js
@@ -4,9 +4,65 @@ const db = require('../lib/db');
const audit = require('../lib/audit');
const { id } = require('../lib/ids');
const { decrypt } = require('../lib/crypto');
+const fs = require('fs');
+const path = require('path');
const fetcher = require('../lib/gmail-fetcher');
+const drive = require('../lib/drive-fetcher');
const extractor = require('../lib/receipt-extractor');
+const UPLOADS_DIR = path.join(__dirname, '..', 'uploads');
+fs.mkdirSync(UPLOADS_DIR, { recursive: true });
+
+async function syncDrive(refreshToken, connectorId) {
+ const files = await drive.listReceiptFiles(refreshToken, { max: 50 });
+ let added = 0;
+ for (const f of files) {
+ // Skip if already ingested (look up by hash if Drive supplied md5, else by file id)
+ const existing = await db.query(
+ `SELECT id FROM document WHERE user_id = $1 AND (object_path = $2 OR hash = $3)`,
+ [DEV_USER_ID, `drive:${f.id}`, f.md5Checksum || `drive:${f.id}`]
+ );
+ if (existing.rows.length) continue;
+
+ // Stream the file to uploads/drive-<id>.<ext>
+ let ext = '.bin';
+ if (f.mimeType === 'application/pdf') ext = '.pdf';
+ else if (f.mimeType.startsWith('image/jpeg')) ext = '.jpg';
+ else if (f.mimeType.startsWith('image/png')) ext = '.png';
+
+ const localPath = path.join(UPLOADS_DIR, `drive-${f.id}${ext}`);
+ try {
+ const stream = await drive.downloadFile(refreshToken, f.id);
+ await new Promise((resolve, reject) => {
+ const out = fs.createWriteStream(localPath);
+ stream.on('error', reject);
+ out.on('error', reject);
+ out.on('finish', resolve);
+ stream.pipe(out);
+ });
+ } catch (err) {
+ console.warn('[drive sync] download failed for', f.id, err.message);
+ continue;
+ }
+
+ const docId = id('document');
+ await db.query(
+ `INSERT INTO document (id, source_message_id, user_id, kind, mime, hash, object_path, parsed_status)
+ VALUES ($1, NULL, $2, $3, $4, $5, $6, 'pending')`,
+ [docId, DEV_USER_ID, 'attachment', f.mimeType, f.md5Checksum || `drive:${f.id}`, `drive:${f.id}`]
+ );
+ added += 1;
+ await audit.log({
+ actorType: 'system',
+ objectType: 'document',
+ objectId: docId,
+ eventType: 'document_persisted',
+ metadata: { source: 'drive', drive_id: f.id, name: f.name, mime: f.mimeType },
+ });
+ }
+ return { fetched: files.length, added };
+}
+
const router = express.Router();
const DEV_USER_ID = 'user_steve';
@@ -141,6 +197,21 @@ router.post('/api/connectors/:id/sync', async (req, res) => {
}
}
+ // Walk Drive too (same Google account, drive.readonly scope)
+ let driveStats = { fetched: 0, added: 0 };
+ try {
+ driveStats = await syncDrive(refreshToken, connectorId);
+ } catch (err) {
+ console.warn('[drive sync] failed:', err.message);
+ await audit.log({
+ actorType: 'system',
+ objectType: 'connector_account',
+ objectId: connectorId,
+ eventType: 'drive_sync_failed',
+ metadata: { error: err.message },
+ });
+ }
+
await db.query(`UPDATE connector_account SET last_sync_at = now() WHERE id = $1`, [connectorId]);
await audit.log({
@@ -148,10 +219,10 @@ router.post('/api/connectors/:id/sync', async (req, res) => {
objectType: 'connector_account',
objectId: connectorId,
eventType: 'sync_completed',
- metadata: { fetched: ids.length, inserted, purchases },
+ metadata: { fetched: ids.length, inserted, purchases, drive: driveStats },
});
- res.json({ ok: true, fetched: ids.length, inserted, purchases });
+ res.json({ ok: true, fetched: ids.length, inserted, purchases, drive: driveStats });
} catch (err) {
console.error('[sync]', err);
await audit.log({
← 323f8a8 tick 1: receipt extractor tier-2 LLM fallback (Mac1 qwen3:14
·
back to AbramsOS
·
tick 3 (scaffold): CPSC recall_event + recall_match tables + 600c717 →