Spaces:
Running
Running
| # ICC Interac Manager — Complete Build Prompt & Specification | |
| --- | |
| ## System Prompt / Role | |
| You are an expert Senior Full-Stack Developer specializing in modern web applications, desktop apps, email integration (IMAP/Gmail API), and AI-powered data extraction. Your task is to build the **"ICC Interac Manager"** — a dual-platform application (Vite.js Web + Electron Desktop) that automatically scans email accounts for Interac e-Transfer notifications, extracts structured transaction data using AI, categorizes each transaction by city/branch, displays results in a live dashboard table, and generates PDF receipts. | |
| --- | |
| ## 1. Project Overview | |
| **App Name:** ICC Interac Manager | |
| **Platforms:** | |
| - **Web App (Primary):** Vite.js + React + TypeScript — deployed as a live SaaS-style web application | |
| - **Desktop App (Secondary):** Electron wrapping the same React codebase for offline/local use | |
| **Purpose:** Automate the scanning, parsing, categorization, and reporting of Interac e-Transfer emails received across multiple ICC branch email accounts. | |
| ### Key Features | |
| 1. **Gmail OAuth Login** — User authenticates with their Gmail account (Google OAuth 2.0). No passwords stored. | |
| 2. **Email Scanning** — Fetch emails ONLY from `notify@payments.interac.ca` via Gmail API or IMAP, with flexible date range controls: scan today only, last 7 days, or any custom date range. | |
| 3. **AI-Powered Parsing** — Extract structured transaction data from each email body using an LLM. | |
| 4. **City/Branch Routing** — Map the `recipient_email` field to a known ICC branch based on a configuration table. | |
| 5. **Live Dashboard** — Display all parsed transactions in a sortable, filterable table with columns: `Date | Expéditeur | Montant | Référence | Succursale/Ville | Statut`. | |
| 6. **PDF Receipt Generation** — Generate a branded PDF receipt per transaction or in batch. | |
| 7. **Export** — Export data to CSV/Excel. | |
| 8. **Real-time Sync** — Live updates via WebSockets or Server-Sent Events in the web version. | |
| --- | |
| ## 2. Architecture — Monorepo with Shared Core | |
| ### Project Structure (Monorepo) | |
| ``` | |
| icc-interac-manager/ | |
| ├── packages/ | |
| │ ├── shared/ # Shared types, constants, utilities | |
| │ │ ├── src/ | |
| │ │ │ ├── types/ | |
| │ │ │ │ ├── transaction.ts # InteracTransaction, BranchConfig, etc. | |
| │ │ │ │ └── scan.ts # ScanPreset, ScanDateRange, ScanRequest, ScanJob | |
| │ │ │ ├── constants/ | |
| │ │ │ │ └── branches.ts # Complete email→branch mapping | |
| │ │ │ ├── utils/ | |
| │ │ │ │ ├── parser.ts # AI prompt templates | |
| │ │ │ │ └── formatter.ts # Currency, date formatting | |
| │ │ │ └── index.ts | |
| │ │ ├── package.json | |
| │ │ └── tsconfig.json | |
| │ │ | |
| │ ├── web/ # ★ Vite.js Web Application | |
| │ │ ├── src/ | |
| │ │ │ ├── main.tsx | |
| │ │ │ ├── App.tsx | |
| │ │ │ ├── components/ | |
| │ │ │ │ ├── layout/ | |
| │ │ │ │ │ ├── Sidebar.tsx | |
| │ │ │ │ │ ├── Header.tsx | |
| │ │ │ │ │ └── MainLayout.tsx | |
| │ │ │ │ ├── dashboard/ | |
| │ │ │ │ │ ├── TransactionTable.tsx | |
| │ │ │ │ │ ├── TransactionRow.tsx | |
| │ │ │ │ │ ├── TransactionDetail.tsx | |
| │ │ │ │ │ ├── SummaryBar.tsx | |
| │ │ │ │ │ ├── FilterPanel.tsx | |
| │ │ │ │ │ └── StatusBadge.tsx | |
| │ │ │ │ ├── auth/ | |
| │ │ │ │ │ ├── GoogleLoginButton.tsx | |
| │ │ │ │ │ └── ProtectedRoute.tsx | |
| │ │ │ │ ├── settings/ | |
| │ │ │ │ │ ├── BranchManager.tsx | |
| │ │ │ │ │ ├── AIProviderConfig.tsx | |
| │ │ │ │ │ └── EmailAccountConfig.tsx | |
| │ │ │ │ ├── scan/ | |
| │ │ │ │ │ ├── ScanControls.tsx # Preset toggle + custom date pickers | |
| │ │ │ │ │ ├── ScanProgress.tsx # WebSocket-powered progress bar | |
| │ │ │ │ │ ├── ScanHistory.tsx # Past scans table | |
| │ │ │ │ │ └── QuickScanButtons.tsx # Dashboard header quick actions | |
| │ │ │ │ └── receipts/ | |
| │ │ │ │ ├── ReceiptPreview.tsx | |
| │ │ │ │ └── BatchExport.tsx | |
| │ │ │ ├── pages/ | |
| │ │ │ │ ├── DashboardPage.tsx | |
| │ │ │ │ ├── LoginPage.tsx | |
| │ │ │ │ ├── SettingsPage.tsx | |
| │ │ │ │ ├── ReportsPage.tsx | |
| │ │ │ │ └── ScanPage.tsx | |
| │ │ │ ├── hooks/ | |
| │ │ │ │ ├── useTransactions.ts | |
| │ │ │ │ ├── useEmailScan.ts | |
| │ │ │ │ ├── useScanDateRange.ts # Manages preset selection + custom date state | |
| │ │ │ │ ├── useAuth.ts | |
| │ │ │ │ └── useRealtime.ts | |
| │ │ │ ├── services/ | |
| │ │ │ │ ├── api.ts # Axios/fetch wrapper to backend | |
| │ │ │ │ ├── gmail.ts # Gmail API client-side helpers | |
| │ │ │ │ └── websocket.ts # Real-time connection | |
| │ │ │ ├── stores/ | |
| │ │ │ │ ├── authStore.ts # Zustand auth state | |
| │ │ │ │ ├── transactionStore.ts | |
| │ │ │ │ └── settingsStore.ts | |
| │ │ │ └── lib/ | |
| │ │ │ ├── pdf.ts # Client-side PDF generation | |
| │ │ │ └── export.ts # CSV/Excel export | |
| │ │ ├── index.html | |
| │ │ ├── vite.config.ts | |
| │ │ ├── tailwind.config.ts | |
| │ │ ├── postcss.config.js | |
| │ │ ├── tsconfig.json | |
| │ │ └── package.json | |
| │ │ | |
| │ ├── server/ # Express.js / Fastify Backend API | |
| │ │ ├── src/ | |
| │ │ │ ├── index.ts # Server entry point | |
| │ │ │ ├── routes/ | |
| │ │ │ │ ├── auth.ts # Google OAuth callback, token mgmt | |
| │ │ │ │ ├── emails.ts # Scan trigger, fetch status | |
| │ │ │ │ ├── transactions.ts # CRUD, list, filter, export | |
| │ │ │ │ ├── settings.ts # Branch config, AI provider config | |
| │ │ │ │ └── receipts.ts # PDF generation endpoint | |
| │ │ │ ├── services/ | |
| │ │ │ │ ├── gmailService.ts # Gmail API integration | |
| │ │ │ │ ├── imapService.ts # IMAP fallback | |
| │ │ │ │ ├── aiService.ts # Unified AI provider abstraction | |
| │ │ │ │ ├── parserService.ts # Email body → AI → JSON | |
| │ │ │ │ ├── routingService.ts# Branch routing logic | |
| │ │ │ │ └── pdfService.ts # Server-side PDF generation | |
| │ │ │ ├── providers/ | |
| │ │ │ │ ├── anthropic.ts # Claude adapter | |
| │ │ │ │ ├── openai.ts # GPT adapter | |
| │ │ │ │ ├── google.ts # Gemini adapter | |
| │ │ │ │ ├── openrouter.ts # OpenRouter adapter | |
| │ │ │ │ ├── ollama.ts # Local Ollama adapter | |
| │ │ │ │ └── index.ts # Provider factory | |
| │ │ │ ├── middleware/ | |
| │ │ │ │ ├── auth.ts # JWT verification | |
| │ │ │ │ ├── rateLimit.ts # API rate limiting | |
| │ │ │ │ └── errorHandler.ts | |
| │ │ │ ├── db/ | |
| │ │ │ │ ├── schema.ts # Drizzle ORM / Prisma schema | |
| │ │ │ │ ├── migrations/ | |
| │ │ │ │ └── seed.ts # Seed branch config | |
| │ │ │ ├── websocket/ | |
| │ │ │ │ └── scanEvents.ts # Real-time scan progress | |
| │ │ │ └── config/ | |
| │ │ │ └── env.ts # Environment config validation | |
| │ │ ├── tsconfig.json | |
| │ │ └── package.json | |
| │ │ | |
| │ └── desktop/ # Electron wrapper (optional) | |
| │ ├── src/ | |
| │ │ ├── main.ts # Electron main process | |
| │ │ ├── preload.ts | |
| │ │ └── ipc/ # IPC handlers for native features | |
| │ ├── electron-builder.yml | |
| │ └── package.json | |
| │ | |
| ├── package.json # Root workspace config | |
| ├── pnpm-workspace.yaml # or npm workspaces | |
| ├── turbo.json # Turborepo config (optional) | |
| ├── .env.example | |
| ├── docker-compose.yml # For local dev (PostgreSQL, Redis) | |
| └── README.md | |
| ``` | |
| --- | |
| ## 3. Tech Stack — Vite.js Web Version (Primary) | |
| ### Frontend (Vite + React) | |
| ``` | |
| Framework: Vite 6 + React 19 + TypeScript 5 | |
| Styling: Tailwind CSS 4 + shadcn/ui | |
| Routing: React Router v7 (or TanStack Router) | |
| State: Zustand (global) + TanStack Query v5 (server state / caching) | |
| Tables: TanStack Table v8 (sorting, filtering, pagination) | |
| Forms: React Hook Form + Zod validation | |
| Charts: Recharts (summary statistics) | |
| PDF: @react-pdf/renderer (client-side) + server-side fallback | |
| Export: SheetJS (xlsx) + native CSV generation | |
| Date handling: date-fns (with fr-CA locale) | |
| Icons: Lucide React | |
| Notifications: Sonner (toast notifications) | |
| Real-time: Socket.io client or native EventSource (SSE) | |
| ``` | |
| ### Backend (Node.js API) | |
| ``` | |
| Runtime: Node.js 20+ LTS | |
| Framework: Express.js 5 or Fastify 5 | |
| Language: TypeScript 5 | |
| ORM: Drizzle ORM (lightweight, SQL-first) or Prisma | |
| Database: PostgreSQL 16 (production) / SQLite (dev/desktop) | |
| Cache: Redis (optional, for scan job queue) | |
| Job Queue: BullMQ (for background email scanning) or simple cron | |
| Auth: Google OAuth 2.0 + JWT sessions | |
| Email: googleapis npm package (Gmail API) | |
| IMAP: imapflow (for non-Gmail accounts) | |
| AI SDKs: @anthropic-ai/sdk, openai, @google/generative-ai | |
| PDF: PDFKit or Puppeteer (server-side generation) | |
| Validation: Zod (shared schemas with frontend) | |
| WebSocket: Socket.io or ws (for real-time scan updates) | |
| Logging: Pino | |
| ``` | |
| ### Vite Configuration | |
| ```typescript | |
| // vite.config.ts | |
| import { defineConfig } from 'vite'; | |
| import react from '@vitejs/plugin-react-swc'; | |
| import path from 'path'; | |
| export default defineConfig({ | |
| plugins: [react()], | |
| resolve: { | |
| alias: { | |
| '@': path.resolve(__dirname, './src'), | |
| '@shared': path.resolve(__dirname, '../shared/src'), | |
| }, | |
| }, | |
| server: { | |
| port: 5173, | |
| proxy: { | |
| '/api': { | |
| target: 'http://localhost:3001', | |
| changeOrigin: true, | |
| }, | |
| '/ws': { | |
| target: 'ws://localhost:3001', | |
| ws: true, | |
| }, | |
| }, | |
| }, | |
| build: { | |
| outDir: 'dist', | |
| sourcemap: true, | |
| }, | |
| }); | |
| ``` | |
| ### Environment Variables | |
| ```env | |
| # .env.example (Web version) | |
| # Google OAuth | |
| VITE_GOOGLE_CLIENT_ID=your-google-client-id.apps.googleusercontent.com | |
| GOOGLE_CLIENT_SECRET=your-google-client-secret | |
| # Backend | |
| PORT=3001 | |
| DATABASE_URL=postgresql://user:pass@localhost:5432/icc_interac | |
| REDIS_URL=redis://localhost:6379 | |
| JWT_SECRET=your-jwt-secret | |
| # AI Provider (pick one or configure multiple in settings) | |
| AI_PROVIDER=anthropic | |
| ANTHROPIC_API_KEY=sk-ant-... | |
| OPENAI_API_KEY=sk-... | |
| GOOGLE_AI_API_KEY=AIza... | |
| OPENROUTER_API_KEY=sk-or-... | |
| OLLAMA_BASE_URL=http://localhost:11434 | |
| # App | |
| SCAN_INTERVAL_MINUTES=5 | |
| APP_URL=http://localhost:5173 | |
| ``` | |
| --- | |
| ## 4. Authentication & Email Access | |
| ### Gmail OAuth 2.0 Flow (Web Version) | |
| ``` | |
| ┌──────────┐ ┌──────────────┐ ┌──────────────┐ ┌──────────┐ | |
| │ Browser │────▶│ Google OAuth │────▶│ Backend │────▶│ Database │ | |
| │ (Vite) │ │ Consent │ │ /api/auth/ │ │ (tokens) │ | |
| │ │◀────│ Screen │◀────│ callback │◀────│ │ | |
| └──────────┘ └──────────────┘ └──────────────┘ └──────────┘ | |
| Flow: | |
| 1. User clicks "Se connecter avec Google" button | |
| 2. Frontend redirects to Google OAuth consent URL | |
| 3. Scopes requested: | |
| - https://www.googleapis.com/auth/gmail.readonly | |
| - https://www.googleapis.com/auth/userinfo.email | |
| - https://www.googleapis.com/auth/userinfo.profile | |
| 4. Google redirects back to /api/auth/google/callback with auth code | |
| 5. Backend exchanges code for access_token + refresh_token | |
| 6. Backend stores encrypted tokens in DB, issues JWT to frontend | |
| 7. Frontend stores JWT in httpOnly cookie or secure localStorage | |
| 8. All subsequent API calls include JWT in Authorization header | |
| ``` | |
| **Google Cloud Console Setup:** | |
| - Create project → Enable Gmail API | |
| - OAuth consent screen → External → Add scopes | |
| - Credentials → OAuth 2.0 Client ID → Web application | |
| - Authorized redirect URIs: `http://localhost:3001/api/auth/google/callback` (dev) + production URL | |
| - Authorized JavaScript origins: `http://localhost:5173` (dev) + production URL | |
| ### IMAP Fallback (For cPanel / Outlook / non-Gmail) | |
| ```typescript | |
| // Server-side only — never expose IMAP credentials to frontend | |
| interface IMAPConfig { | |
| host: string; // e.g., "mail.iccameriques.org" or "imap.gmail.com" | |
| port: number; // 993 (SSL) | |
| secure: true; | |
| auth: { | |
| user: string; // e.g., "finances@iccameriques.org" | |
| pass: string; // App password or OAuth2 token | |
| }; | |
| } | |
| // Search filter: FROM "notify@payments.interac.ca" | |
| ``` | |
| --- | |
| ## 5. Email Scanning — Date Range System | |
| ### Scan Mode Presets | |
| The scan interface MUST provide three modes for selecting which emails to scan. These control the Gmail API `after:` / `before:` search operators (or IMAP `SINCE` / `BEFORE` filters). | |
| | Mode | Label (FR) | Behavior | | |
| |------|-----------|----------| | |
| | **Today** | `Aujourd'hui` | From midnight (00:00:00) of the current day in the user's timezone → now. Default mode. | | |
| | **Last 7 Days** | `7 derniers jours` | From midnight 7 days ago → now. | | |
| | **Custom Range** | `Période personnalisée` | User selects a start date and end date via a date picker. Supports ranges spanning months or years (e.g., 2024-01-10 to 2025-03-12). | | |
| ### Shared Types | |
| ```typescript | |
| // packages/shared/src/types/scan.ts | |
| export type ScanPreset = 'today' | 'last7days' | 'custom'; | |
| export interface ScanDateRange { | |
| preset: ScanPreset; | |
| startDate: string; // ISO 8601 date: "2024-01-10T00:00:00Z" | |
| endDate: string; // ISO 8601 date: "2025-03-12T23:59:59Z" | |
| } | |
| export interface ScanRequest { | |
| dateRange: ScanDateRange; | |
| forceRescan?: boolean; // re-process emails already in DB (default: false) | |
| } | |
| export interface ScanJob { | |
| jobId: string; | |
| status: 'queued' | 'scanning' | 'parsing' | 'completed' | 'failed'; | |
| dateRange: ScanDateRange; | |
| progress: { | |
| emailsFound: number; | |
| emailsProcessed: number; | |
| emailsSkipped: number; // already in DB (deduplication) | |
| emailsErrored: number; | |
| currentEmail?: string; // subject or sender being processed now | |
| }; | |
| startedAt: string; | |
| completedAt?: string; | |
| } | |
| // Helper to resolve preset → concrete dates | |
| export function resolveScanDates(preset: ScanPreset, customStart?: string, customEnd?: string): ScanDateRange { | |
| const now = new Date(); | |
| switch (preset) { | |
| case 'today': { | |
| const midnight = new Date(now); | |
| midnight.setHours(0, 0, 0, 0); | |
| return { preset, startDate: midnight.toISOString(), endDate: now.toISOString() }; | |
| } | |
| case 'last7days': { | |
| const sevenDaysAgo = new Date(now); | |
| sevenDaysAgo.setDate(sevenDaysAgo.getDate() - 7); | |
| sevenDaysAgo.setHours(0, 0, 0, 0); | |
| return { preset, startDate: sevenDaysAgo.toISOString(), endDate: now.toISOString() }; | |
| } | |
| case 'custom': { | |
| if (!customStart || !customEnd) throw new Error('Custom range requires startDate and endDate'); | |
| return { | |
| preset, | |
| startDate: new Date(customStart + 'T00:00:00').toISOString(), | |
| endDate: new Date(customEnd + 'T23:59:59').toISOString(), | |
| }; | |
| } | |
| } | |
| } | |
| ``` | |
| ### Gmail API Query Construction | |
| ```typescript | |
| // packages/server/src/services/gmailService.ts | |
| function buildGmailQuery(dateRange: ScanDateRange): string { | |
| // Gmail search uses YYYY/MM/DD format for after: and before: | |
| const start = new Date(dateRange.startDate); | |
| const end = new Date(dateRange.endDate); | |
| // Gmail after:/before: are DATE-only (not datetime), and after: is exclusive | |
| // So subtract 1 day from start to make it inclusive | |
| const afterDate = new Date(start); | |
| afterDate.setDate(afterDate.getDate() - 1); | |
| // Add 1 day to end to make before: inclusive of the end date | |
| const beforeDate = new Date(end); | |
| beforeDate.setDate(beforeDate.getDate() + 1); | |
| const fmt = (d: Date) => `${d.getFullYear()}/${d.getMonth() + 1}/${d.getDate()}`; | |
| return `from:notify@payments.interac.ca after:${fmt(afterDate)} before:${fmt(beforeDate)}`; | |
| } | |
| // Examples: | |
| // Today (Feb 23, 2026): | |
| // "from:notify@payments.interac.ca after:2026/2/22 before:2026/2/24" | |
| // | |
| // Last 7 days (Feb 17-23, 2026): | |
| // "from:notify@payments.interac.ca after:2026/2/16 before:2026/2/24" | |
| // | |
| // Custom (Jan 10, 2024 → Mar 12, 2025): | |
| // "from:notify@payments.interac.ca after:2024/1/9 before:2025/3/13" | |
| ``` | |
| ### IMAP Date Filter Construction | |
| ```typescript | |
| // packages/server/src/services/imapService.ts | |
| function buildIMAPSearch(dateRange: ScanDateRange): any[] { | |
| const start = new Date(dateRange.startDate); | |
| const end = new Date(dateRange.endDate); | |
| return [ | |
| { from: 'notify@payments.interac.ca' }, | |
| { since: start }, // IMAP SINCE is inclusive (on or after) | |
| { before: end }, // IMAP BEFORE is exclusive (before this date) | |
| ]; | |
| } | |
| ``` | |
| ### Scan UI Component (Frontend) | |
| ```typescript | |
| // packages/web/src/components/scan/ScanControls.tsx | |
| // | |
| // Layout: | |
| // ┌─────────────────────────────────────────────────────────────────────┐ | |
| // │ Scanner les courriels Interac │ | |
| // │ │ | |
| // │ ┌─────────────┐ ┌──────────────────┐ ┌─────────────────────────┐ │ | |
| // │ │ Aujourd'hui │ │ 7 derniers jours │ │ Période personnalisée │ │ | |
| // │ │ (active) │ │ │ │ │ │ | |
| // │ └─────────────┘ └──────────────────┘ └─────────────────────────┘ │ | |
| // │ │ | |
| // │ [Only visible when "Période personnalisée" is selected:] │ | |
| // │ ┌──────────────────┐ ┌──────────────────┐ │ | |
| // │ │ Date de début │ │ Date de fin │ │ | |
| // │ │ 📅 2024-01-10 │ │ 📅 2025-03-12 │ │ | |
| // │ └──────────────────┘ └──────────────────┘ │ | |
| // │ │ | |
| // │ ┌───────────────────────────────────────────┐ │ | |
| // │ │ 🔍 Scanner maintenant │ │ | |
| // │ └───────────────────────────────────────────┘ │ | |
| // │ │ | |
| // │ ☐ Forcer le re-scan (re-traiter les courriels déjà importés) │ | |
| // │ │ | |
| // │ [During scan — progress bar:] │ | |
| // │ ┌───────────────────────────────────────────┐ │ | |
| // │ │ ████████████░░░░░░░░░░ 47/120 courriels │ │ | |
| // │ │ Période: 10 janv. 2024 → 12 mars 2025 │ │ | |
| // │ │ Dernier traité: John Doe — 150,00 $ │ │ | |
| // │ └───────────────────────────────────────────┘ │ | |
| // └─────────────────────────────────────────────────────────────────────┘ | |
| // | |
| // Implementation notes: | |
| // - Use shadcn/ui ToggleGroup for the 3 preset buttons | |
| // - Use shadcn/ui DatePicker (Popover + Calendar) for custom range | |
| // - Custom date pickers only visible when "Période personnalisée" is active | |
| // - Date validation: startDate must be before endDate, endDate cannot be in the future | |
| // - "Scanner maintenant" button disabled during an active scan | |
| // - Progress bar powered by WebSocket scan:progress events | |
| // - Show scan summary toast on completion: "47 nouveaux virements importés" | |
| ``` | |
| ### Scan Quick Actions in Dashboard Header | |
| ```typescript | |
| // The dashboard header should include quick-scan buttons for common operations: | |
| // | |
| // ┌─────────────────────────────────────────────────────────────┐ | |
| // │ 📊 Tableau de bord │ | |
| // │ │ | |
| // │ [Scanner aujourd'hui] [Scanner 7 jours] [Scan avancé →] │ | |
| // └─────────────────────────────────────────────────────────────┘ | |
| // | |
| // "Scanner aujourd'hui" → immediate scan with preset='today' | |
| // "Scanner 7 jours" → immediate scan with preset='last7days' | |
| // "Scan avancé →" → navigates to full ScanPage with custom date picker | |
| ``` | |
| ### Large Date Range Handling | |
| For custom scans spanning months or years (e.g., Jan 2024 → Mar 2025 = ~14 months), the system must handle potentially thousands of emails: | |
| ```typescript | |
| // packages/server/src/services/scanService.ts | |
| const BATCH_SIZE = 50; // Process emails in batches | |
| const RATE_LIMIT_DELAY = 100; // ms between Gmail API calls (respect quota) | |
| async function executeScan(scanRequest: ScanRequest, userId: string): Promise<void> { | |
| const { dateRange } = scanRequest; | |
| // 1. Build query and fetch all matching message IDs | |
| const query = buildGmailQuery(dateRange); | |
| const messageIds = await gmail.fetchAllMessageIds(query); | |
| // For large ranges, this could return 1000+ IDs | |
| // 2. Filter out already-processed emails (deduplication) | |
| const existingIds = await db.getExistingEmailIds(messageIds); | |
| const newIds = scanRequest.forceRescan | |
| ? messageIds | |
| : messageIds.filter(id => !existingIds.has(id)); | |
| // 3. Emit initial progress | |
| websocket.emit('scan:started', { | |
| jobId, totalEmails: messageIds.length, | |
| newEmails: newIds.length, | |
| skipped: messageIds.length - newIds.length, | |
| dateRange, | |
| }); | |
| // 4. Process in batches | |
| for (let i = 0; i < newIds.length; i += BATCH_SIZE) { | |
| const batch = newIds.slice(i, i + BATCH_SIZE); | |
| const results = await Promise.allSettled( | |
| batch.map(id => processEmail(id, userId)) | |
| ); | |
| // 5. Emit progress after each batch | |
| websocket.emit('scan:progress', { | |
| jobId, | |
| processed: Math.min(i + BATCH_SIZE, newIds.length), | |
| total: newIds.length, | |
| batchResults: results.map(r => r.status), | |
| }); | |
| await sleep(RATE_LIMIT_DELAY); | |
| } | |
| // 6. Emit completion | |
| websocket.emit('scan:completed', { jobId, summary: { ... } }); | |
| } | |
| ``` | |
| --- | |
| ## 6. Email Parsing with AI | |
| ### What to Extract | |
| From each Interac e-Transfer notification email, the AI must extract: | |
| ```typescript | |
| interface InteracTransaction { | |
| sender: string; // "John Doe" | |
| amount: number; // 150.00 | |
| currency: string; // "CAD" | |
| reference: string; // "CA1b2c3d4e5f" | |
| message: string | null; // "Dime pour mars 2025" | |
| recipient_email: string; // "montreal.finances@iccameriques.org" | |
| date: string; // "2025-02-15T14:30:00Z" (ISO 8601) | |
| status: 'deposited' | 'pending' | 'expired' | 'cancelled'; | |
| } | |
| ``` | |
| ### AI Prompt Template | |
| ``` | |
| You are a financial data extraction assistant. Given the raw text/HTML of an Interac e-Transfer notification email from notify@payments.interac.ca, extract the following fields into a JSON object: | |
| - sender: The name of the person who sent the money | |
| - amount: The dollar amount (numeric, no $ sign) | |
| - currency: Always "CAD" | |
| - reference: The Interac reference number | |
| - message: The personal message or memo (sometimes called "dime") | |
| - recipient_email: The email address the transfer was sent TO (the ICC branch email) | |
| - date: The date/time of the transfer in ISO 8601 format | |
| - status: One of "deposited", "pending", "expired", "cancelled" | |
| Rules: | |
| - If a field is not found, set it to null. | |
| - The amount must be a number (e.g., 150.00 not "$150.00"). | |
| - Return ONLY valid JSON, no markdown, no explanation. | |
| Email content: | |
| """ | |
| {EMAIL_BODY} | |
| """ | |
| ``` | |
| ### Unified AI Provider Interface | |
| ```typescript | |
| // packages/server/src/services/aiService.ts | |
| interface AIProvider { | |
| name: string; | |
| parse(emailBody: string): Promise<InteracTransaction>; | |
| } | |
| class AIService { | |
| private provider: AIProvider; | |
| constructor(providerName: string) { | |
| this.provider = AIProviderFactory.create(providerName); | |
| } | |
| async parseEmail(emailBody: string): Promise<InteracTransaction> { | |
| const result = await this.provider.parse(emailBody); | |
| // Validate with Zod schema before returning | |
| return InteracTransactionSchema.parse(result); | |
| } | |
| } | |
| ``` | |
| --- | |
| ## 7. AI Model Options | |
| ### Tier 1: Cloud API — Best Accuracy | |
| | Provider | Model | API Endpoint | Best For | Approx Cost | | |
| |----------|-------|-------------|----------|-------------| | |
| | **Anthropic** | `claude-sonnet-4-20250514` | `api.anthropic.com/v1/messages` | Best structured extraction | ~$3/$15 per 1M tokens | | |
| | **Anthropic** | `claude-haiku-4-5-20251001` | `api.anthropic.com/v1/messages` | Fast + cheap extraction | ~$0.80/$4 per 1M tokens | | |
| | **OpenAI** | `gpt-4o` | `api.openai.com/v1/chat/completions` | Great all-rounder | ~$2.50/$10 per 1M tokens | | |
| | **OpenAI** | `gpt-4o-mini` | `api.openai.com/v1/chat/completions` | Cheapest OpenAI option | ~$0.15/$0.60 per 1M tokens | | |
| | **Google** | `gemini-2.0-flash` | `generativelanguage.googleapis.com` | Very fast, good accuracy | ~$0.10/$0.40 per 1M tokens | | |
| | **Google** | `gemini-2.5-pro` | `generativelanguage.googleapis.com` | Top-tier Google model | ~$1.25/$10 per 1M tokens | | |
| | **Mistral** | `mistral-large-latest` | `api.mistral.ai/v1/chat/completions` | European hosting, strong | ~$2/$6 per 1M tokens | | |
| | **Mistral** | `mistral-small-latest` | `api.mistral.ai/v1/chat/completions` | Budget option | ~$0.20/$0.60 per 1M tokens | | |
| ### Tier 2: Aggregator APIs — Access Multiple Models via One Key | |
| | Aggregator | URL | Models Available | Notes | | |
| |------------|-----|-----------------|-------| | |
| | **OpenRouter** | `openrouter.ai/api/v1` | Claude, GPT-4o, Gemini, Llama, Mistral, etc. | Single API key for 200+ models, pay-per-use | | |
| | **Together AI** | `api.together.xyz` | Llama 3.1, Mixtral, Qwen, CodeLlama | Great for open-source models, fast inference | | |
| | **Fireworks AI** | `api.fireworks.ai` | Llama, Mixtral, custom fine-tunes | Fastest open-source inference | | |
| | **Eden AI** | `api.edenai.run` | GPT, Claude, Gemini, Cohere, etc. | True multi-provider aggregator | | |
| | **Groq** | `api.groq.com` | Llama 3.1 70B/8B, Mixtral | Extremely fast (custom LPU hardware) | | |
| ### Tier 3: Local/Private Models — No Data Leaves Your Machine | |
| | Tool | Model | Notes | | |
| |------|-------|-------| | |
| | **Ollama** | `llama3.1:8b`, `mistral:7b`, `phi-3:mini` | Run locally, zero cost, full privacy | | |
| | **LM Studio** | Any GGUF model | GUI + local API server at `localhost:1234` | | |
| | **Jan.ai** | Various | Desktop app with built-in model management | | |
| | **llama.cpp** | Any GGUF | CLI-based, most lightweight option | | |
| | **vLLM** | Llama, Mistral, etc. | Self-hosted, production-grade serving | | |
| ### Recommended Setup | |
| ``` | |
| Production: Claude Haiku 4.5 via Anthropic API (best cost/accuracy for structured extraction) | |
| Development: Ollama + llama3.1:8b (free, private, no API key needed) | |
| Fallback: OpenRouter (auto-failover to cheapest available model) | |
| ``` | |
| --- | |
| ## 8. City/Branch Routing Configuration | |
| The `recipient_email` extracted by the AI determines which ICC branch the transaction belongs to. | |
| ```typescript | |
| // packages/shared/src/constants/branches.ts | |
| export const BRANCH_MAPPING: Record<string, string> = { | |
| "finances@iccameriques.org": "ICC Montréal", | |
| "montreal.finances@iccameriques.org": "ICC Montréal", | |
| "quebec.finances@iccameriques.org": "ICC Québec", | |
| "troisrivieres.finances@iccameriques.org": "ICC Trois-Rivières", | |
| "trois-rivieres.finances@iccameriques.org": "ICC Trois-Rivières", | |
| "sherbrooke.finances@iccameriques.org": "ICC Sherbrooke", | |
| "gatineau.finances@iccameriques.org": "ICC Gatineau", | |
| "ottawa.finances@iccameriques.org": "ICC Ottawa", | |
| "gatineauottawa.finances@iccameriques.org": "ICC Gatineau-Ottawa", | |
| "toronto.finances@iccameriques.org": "ICC Toronto", | |
| "hamilton.finances@iccameriques.org": "ICC Hamilton", | |
| "london.finances@iccameriques.org": "ICC London", | |
| "windsor.finances@iccameriques.org": "ICC Windsor", | |
| "sudbury.finances@iccameriques.org": "ICC Sudbury", | |
| "timmins.finances@iccameriques.org": "ICC Timmins", | |
| "winnipeg.finances@iccameriques.org": "ICC Winnipeg", | |
| "regina.finances@iccameriques.org": "ICC Regina", | |
| "saskatoon.finances@iccameriques.org": "ICC Saskatoon", | |
| "calgary.finances@iccameriques.org": "ICC Calgary", | |
| "edmonton.finances@iccameriques.org": "ICC Edmonton", | |
| "vancouver.finances@iccameriques.org": "ICC Vancouver", | |
| "victoria.finances@iccameriques.org": "ICC Victoria", | |
| "halifax.finances@iccameriques.org": "ICC Halifax", | |
| "moncton.finances@iccameriques.org": "ICC Moncton", | |
| "charlottetown.finances@iccameriques.org": "ICC Charlottetown", | |
| "stjohns.finances@iccameriques.org": "ICC St. John's", | |
| "lvl.finances@iccameriques.org": "ICC Laval", | |
| "lgl.finances@iccameriques.org": "ICC Longueuil", | |
| "victoriaville.finances@iccameriques.org": "ICC Victoriaville", | |
| "valleyfield.finances@iccameriques.org": "ICC Valleyfield", | |
| "finances.rdl@iccameriques.org": "ICC Rivière-du-Loup", | |
| "rimouski.finances@iccameriques.org": "ICC Rimouski", | |
| "septiles.finances@iccameriques.org": "ICC Sept-Îles", | |
| "saguenay.finances@iccameriques.org": "ICC Saguenay", | |
| "chibougamau.finances@iccameriques.org": "ICC Chibougamau", | |
| "valdor.finances@iccameriques.org": "ICC Val-d'Or", | |
| "rouyn.finances@iccameriques.org": "ICC Rouyn-Noranda", | |
| "kingston.finances@iccameriques.org": "ICC Kingston", | |
| "shawinigan.finances@iccameriques.org": "ICC Shawinigan", | |
| "drummondville.finances@iccameriques.org": "ICC Drummondville", | |
| "granby.finances@iccameriques.org": "ICC Granby", | |
| "sthyacinthe.finances@iccameriques.org": "ICC St-Hyacinthe", | |
| "stjerome.finances@iccameriques.org": "ICC St-Jérôme", | |
| "joliette.finances@iccameriques.org": "ICC Joliette", | |
| "terrebonne.finances@iccameriques.org": "ICC Terrebonne", | |
| "repentigny.finances@iccameriques.org": "ICC Repentigny", | |
| "siege@iccameriques.org": "ICC Siège", | |
| "mission.finances@iccameriques.org": "ICC Mission", | |
| }; | |
| export function resolveBranch(recipientEmail: string): string { | |
| return BRANCH_MAPPING[recipientEmail.toLowerCase()] ?? "Non classifié"; | |
| } | |
| ``` | |
| The mapping is also stored in the `branch_config` database table and is editable from the Settings page. | |
| --- | |
| ## 9. Live Dashboard — Table UI | |
| ### Table Columns | |
| | Column | Field | Type | Notes | | |
| |--------|-------|------|-------| | |
| | Date | `date` | DateTime | Format: `YYYY-MM-DD HH:mm` | | |
| | Expéditeur | `sender` | String | Full name of sender | | |
| | Montant | `amount` | Currency | Format: `1 500,00 $` or `$1,500.00` | | |
| | Référence | `reference` | String | Interac reference number | | |
| | Succursale/Ville | `branch` | String | Resolved from city routing map | | |
| | Statut | `status` | Badge | Color-coded status indicator | | |
| ### Status Badge Mapping | |
| ```typescript | |
| export const STATUS_MAP = { | |
| deposited: { label: "Déposé", color: "bg-emerald-100 text-emerald-800", icon: "CheckCircle" }, | |
| pending: { label: "En attente", color: "bg-amber-100 text-amber-800", icon: "Clock" }, | |
| expired: { label: "Expiré", color: "bg-red-100 text-red-800", icon: "XCircle" }, | |
| cancelled: { label: "Annulé", color: "bg-gray-100 text-gray-600", icon: "Ban" }, | |
| } as const; | |
| ``` | |
| ### Dashboard Features | |
| - **Real-time updates**: WebSocket push from server when new scans complete; auto-refresh fallback every 5 min | |
| - **Filters**: By branch/city (multi-select dropdown), date range picker, status checkboxes, amount range slider | |
| - **Search**: Full-text search across sender, reference, message (debounced, 300ms) | |
| - **Sort**: Clickable column headers with ascending/descending/none cycle | |
| - **Pagination**: 25/50/100 rows per page with TanStack Table | |
| - **Summary bar**: Total transactions count, total $ amount, breakdown by branch (mini bar chart) | |
| - **Row expansion**: Click row to reveal full details — message/dime, raw email snippet, recipient email | |
| - **Bulk actions**: Checkbox select → Export PDF, Export CSV, Mark as reviewed | |
| - **Scan controls**: Three quick-scan buttons — "Aujourd'hui" (today), "7 derniers jours" (last 7 days), "Période personnalisée" (custom date range with dual date pickers). Progress bar with batch count during active scan. | |
| - **Scan history**: View past scans with their date ranges, results count, and duration | |
| - **Branch tabs**: Quick filter tabs at the top for the most active branches | |
| ### Key React Components | |
| ```typescript | |
| // TransactionTable.tsx — powered by TanStack Table v8 | |
| // Uses server-side pagination for performance (API: GET /api/transactions?page=1&limit=25&branch=...) | |
| // Columns are fully configurable and resizable | |
| // Supports column visibility toggle | |
| // SummaryBar.tsx — displays at the top of dashboard | |
| // Shows: total count | total amount | deposited vs pending pie chart | top 5 branches | |
| // FilterPanel.tsx — collapsible sidebar or top bar | |
| // Branch multi-select, date range (react-day-picker), status checkboxes, amount min/max | |
| // ScanProgress.tsx — shown during active scan | |
| // WebSocket-powered: "Scanning... 47/120 emails processed" | |
| // Shows: progress bar, date range being scanned, current email being parsed | |
| // Example: "Période: 10 janv. 2024 → 12 mars 2025 | 47/120 courriels traités" | |
| ``` | |
| --- | |
| ## 10. PDF Receipt Generation | |
| Each transaction is exportable as a branded PDF receipt: | |
| ``` | |
| ┌────────────────────────────────────────┐ | |
| │ ICC AMÉRIQUES │ | |
| │ Reçu de virement Interac │ | |
| ├────────────────────────────────────────┤ | |
| │ Date: 2025-02-15 14:30 │ | |
| │ Expéditeur: John Doe │ | |
| │ Montant: 1 500,00 $ CAD │ | |
| │ Référence: CA1b2c3d4e5f │ | |
| │ Message: Dime pour mars 2025 │ | |
| │ Succursale: ICC Montréal │ | |
| │ Statut: Déposé ✓ │ | |
| ├────────────────────────────────────────┤ | |
| │ Reçu généré automatiquement par │ | |
| │ ICC Interac Manager │ | |
| │ Date de génération: 2025-02-20 │ | |
| └────────────────────────────────────────┘ | |
| ``` | |
| **Implementation:** | |
| - Client-side: `@react-pdf/renderer` for instant preview + download in browser | |
| - Server-side: `PDFKit` for batch generation (POST /api/receipts/batch with array of transaction IDs) | |
| - Both produce identical output using shared template constants | |
| --- | |
| ## 11. Database Schema | |
| ### PostgreSQL (Web / Production) | |
| ```sql | |
| -- Users table (linked to Google accounts) | |
| CREATE TABLE users ( | |
| id UUID PRIMARY KEY DEFAULT gen_random_uuid(), | |
| google_id TEXT UNIQUE NOT NULL, | |
| email TEXT UNIQUE NOT NULL, | |
| name TEXT, | |
| avatar_url TEXT, | |
| access_token TEXT, -- encrypted Google OAuth token | |
| refresh_token TEXT, -- encrypted | |
| token_expires TIMESTAMPTZ, | |
| role TEXT DEFAULT 'viewer' CHECK(role IN ('admin','editor','viewer')), | |
| created_at TIMESTAMPTZ DEFAULT NOW(), | |
| updated_at TIMESTAMPTZ DEFAULT NOW() | |
| ); | |
| -- Transactions table | |
| CREATE TABLE transactions ( | |
| id UUID PRIMARY KEY DEFAULT gen_random_uuid(), | |
| email_id TEXT UNIQUE NOT NULL, -- Gmail message ID or IMAP UID | |
| user_id UUID REFERENCES users(id), -- who scanned this | |
| date TIMESTAMPTZ NOT NULL, | |
| sender TEXT NOT NULL, | |
| amount NUMERIC(12,2) NOT NULL, | |
| currency TEXT DEFAULT 'CAD', | |
| reference TEXT, | |
| message TEXT, -- dime/memo | |
| recipient_email TEXT, | |
| branch TEXT, -- resolved branch name | |
| status TEXT CHECK(status IN ('deposited','pending','expired','cancelled')), | |
| raw_email TEXT, -- original email body for audit | |
| parsed_at TIMESTAMPTZ DEFAULT NOW(), | |
| reviewed BOOLEAN DEFAULT FALSE, | |
| reviewed_by UUID REFERENCES users(id), | |
| created_at TIMESTAMPTZ DEFAULT NOW() | |
| ); | |
| CREATE INDEX idx_tx_date ON transactions(date DESC); | |
| CREATE INDEX idx_tx_branch ON transactions(branch); | |
| CREATE INDEX idx_tx_status ON transactions(status); | |
| CREATE INDEX idx_tx_user ON transactions(user_id); | |
| CREATE INDEX idx_tx_sender ON transactions(sender); | |
| -- Branch configuration (editable from Settings) | |
| CREATE TABLE branch_config ( | |
| id SERIAL PRIMARY KEY, | |
| email TEXT UNIQUE NOT NULL, | |
| branch TEXT NOT NULL, | |
| active BOOLEAN DEFAULT TRUE, | |
| updated_at TIMESTAMPTZ DEFAULT NOW() | |
| ); | |
| -- Scan logs (audit trail) | |
| CREATE TABLE scan_logs ( | |
| id SERIAL PRIMARY KEY, | |
| user_id UUID REFERENCES users(id), | |
| scan_preset TEXT CHECK(scan_preset IN ('today','last7days','custom')), | |
| scan_start_date TIMESTAMPTZ NOT NULL, -- start of scanned date range | |
| scan_end_date TIMESTAMPTZ NOT NULL, -- end of scanned date range | |
| force_rescan BOOLEAN DEFAULT FALSE, | |
| started_at TIMESTAMPTZ NOT NULL, | |
| finished_at TIMESTAMPTZ, | |
| emails_found INTEGER DEFAULT 0, | |
| emails_parsed INTEGER DEFAULT 0, | |
| emails_skipped INTEGER DEFAULT 0, -- already in DB (deduplication) | |
| errors INTEGER DEFAULT 0, | |
| error_details JSONB, | |
| ai_provider TEXT, -- which AI was used | |
| ai_model TEXT, | |
| created_at TIMESTAMPTZ DEFAULT NOW() | |
| ); | |
| -- AI provider settings (per-user or global) | |
| CREATE TABLE ai_settings ( | |
| id SERIAL PRIMARY KEY, | |
| user_id UUID REFERENCES users(id), -- NULL = global default | |
| provider TEXT NOT NULL, -- 'anthropic', 'openai', 'ollama', etc. | |
| model TEXT NOT NULL, -- 'claude-haiku-4-5-20251001', etc. | |
| api_key TEXT, -- encrypted, NULL for Ollama | |
| base_url TEXT, -- custom endpoint if needed | |
| is_active BOOLEAN DEFAULT TRUE, | |
| created_at TIMESTAMPTZ DEFAULT NOW() | |
| ); | |
| ``` | |
| --- | |
| ## 12. API Endpoints (Backend) | |
| ### Authentication | |
| ``` | |
| GET /api/auth/google → Redirect to Google OAuth | |
| GET /api/auth/google/callback → Handle OAuth callback, issue JWT | |
| POST /api/auth/refresh → Refresh expired JWT | |
| POST /api/auth/logout → Invalidate session | |
| GET /api/auth/me → Get current user info | |
| ``` | |
| ### Email Scanning | |
| ``` | |
| POST /api/scan/start → Trigger email scan | |
| Body: { | |
| preset: "today" | "last7days" | "custom", | |
| startDate?: "2024-01-10", // required if preset=custom (YYYY-MM-DD) | |
| endDate?: "2025-03-12", // required if preset=custom (YYYY-MM-DD) | |
| forceRescan?: false // re-process emails already in DB | |
| } | |
| Returns: { jobId, dateRange, estimatedEmails } | |
| GET /api/scan/status/:jobId → Get scan progress (or use WebSocket) | |
| Returns: { | |
| jobId, status, dateRange, | |
| progress: { found, processed, skipped, errored, currentEmail } | |
| } | |
| GET /api/scan/history → List past scan logs with date ranges used | |
| Returns: [{ id, dateRange, emailsFound, emailsParsed, startedAt, duration }] | |
| ``` | |
| ### Transactions | |
| ``` | |
| GET /api/transactions → List with filters (?branch=&status=&from=&to=&page=&limit=&search=) | |
| GET /api/transactions/:id → Single transaction detail | |
| PATCH /api/transactions/:id → Update (mark reviewed, edit branch) | |
| DELETE /api/transactions/:id → Soft delete | |
| GET /api/transactions/stats → Summary statistics (totals by branch, status, month) | |
| ``` | |
| ### Receipts & Export | |
| ``` | |
| GET /api/receipts/:id/pdf → Generate single PDF receipt | |
| POST /api/receipts/batch → Generate batch PDF (body: { ids: [...] }) | |
| GET /api/export/csv → Export filtered results as CSV | |
| GET /api/export/xlsx → Export filtered results as Excel | |
| ``` | |
| ### Settings | |
| ``` | |
| GET /api/settings/branches → List branch mappings | |
| POST /api/settings/branches → Add new branch mapping | |
| PUT /api/settings/branches/:id → Update branch mapping | |
| DELETE /api/settings/branches/:id → Delete branch mapping | |
| GET /api/settings/ai → Get AI provider config | |
| PUT /api/settings/ai → Update AI provider/model/key | |
| ``` | |
| ### WebSocket Events | |
| ``` | |
| ws://localhost:3001/ws | |
| Server → Client events: | |
| scan:started { jobId, timestamp, dateRange, totalEmails, newEmails, skipped } | |
| scan:progress { jobId, processed, total, skipped, errored, latest: InteracTransaction } | |
| scan:completed { jobId, summary: { found, parsed, skipped, errors }, dateRange } | |
| scan:error { jobId, message, dateRange } | |
| transaction:new { transaction: InteracTransaction } // real-time insert | |
| ``` | |
| --- | |
| ## 13. Application Flow | |
| ``` | |
| ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ | |
| │ User Login │────▶│ Select Date │────▶│ Gmail API │────▶│ AI Parse │ | |
| │ Google OAuth│ │ Range: │ │ Fetch emails│ │ Each Email │ | |
| │ (Vite SPA) │ │ • Today │ │ from:notify │ │ → JSON │ | |
| │ │ │ • 7 days │ │ @payments. │ │ │ | |
| │ │ │ • Custom │ │ interac.ca │ │ │ | |
| │ │ │ ──────── │ │ after:/ │ │ │ | |
| │ │ │ POST /api/ │ │ before: │ │ │ | |
| │ │ │ scan/start │ │ date filter │ │ │ | |
| └──────────────┘ └──────────────┘ └──────────────┘ └──────┬───────┘ | |
| │ │ | |
| │ WebSocket: scan:progress │ | |
| ▼ ▼ | |
| ┌──────────────┐ ┌──────────────┐ | |
| │ Vite React │◀─────────────────────────│ Route to │ | |
| │ Dashboard │ transaction:new event │ Branch by │ | |
| │ (Live Table)│ │ recipient │ | |
| │ │ │ email │ | |
| └──────┬───────┘ └──────────────┘ | |
| │ | |
| ┌────────┼────────┐ | |
| ▼ ▼ ▼ | |
| ┌──────────┐ ┌──────┐ ┌──────────┐ | |
| │Export CSV │ │ PDF │ │ Reports │ | |
| │/ Excel │ │Reçus │ │& Charts │ | |
| └──────────┘ └──────┘ └──────────┘ | |
| ``` | |
| --- | |
| ## 14. Deployment — Web Version | |
| ### Option A: Vercel + Railway (Recommended) | |
| ``` | |
| Frontend (Vite): Vercel (free tier works for starters) | |
| Backend (Express): Railway or Render | |
| Database: Neon (serverless PostgreSQL) or Supabase | |
| Redis: Upstash (serverless Redis for job queues) | |
| ``` | |
| ### Option B: VPS / Self-hosted | |
| ``` | |
| Server: Ubuntu 24.04 VPS (DigitalOcean, Hetzner, OVH) | |
| Process Manager: PM2 (Node.js backend) | |
| Reverse Proxy: Nginx or Caddy (auto-HTTPS) | |
| Database: PostgreSQL 16 (same server or managed) | |
| Frontend: Vite build → served as static files by Nginx | |
| CI/CD: GitHub Actions → SSH deploy or Docker | |
| ``` | |
| ### Option C: Docker Compose (Full Stack) | |
| ```yaml | |
| # docker-compose.yml | |
| version: '3.9' | |
| services: | |
| frontend: | |
| build: | |
| context: ./packages/web | |
| dockerfile: Dockerfile | |
| ports: | |
| - "5173:80" | |
| depends_on: | |
| - backend | |
| backend: | |
| build: | |
| context: ./packages/server | |
| dockerfile: Dockerfile | |
| ports: | |
| - "3001:3001" | |
| environment: | |
| - DATABASE_URL=postgresql://icc:password@postgres:5432/icc_interac | |
| - REDIS_URL=redis://redis:6379 | |
| depends_on: | |
| - postgres | |
| - redis | |
| postgres: | |
| image: postgres:16-alpine | |
| environment: | |
| POSTGRES_DB: icc_interac | |
| POSTGRES_USER: icc | |
| POSTGRES_PASSWORD: password | |
| volumes: | |
| - pgdata:/var/lib/postgresql/data | |
| ports: | |
| - "5432:5432" | |
| redis: | |
| image: redis:7-alpine | |
| ports: | |
| - "6379:6379" | |
| volumes: | |
| pgdata: | |
| ``` | |
| ### Dockerfile for Vite Frontend | |
| ```dockerfile | |
| # packages/web/Dockerfile | |
| FROM node:20-alpine AS builder | |
| WORKDIR /app | |
| COPY package*.json ./ | |
| RUN npm ci | |
| COPY . . | |
| RUN npm run build | |
| FROM nginx:alpine | |
| COPY --from=builder /app/dist /usr/share/nginx/html | |
| COPY nginx.conf /etc/nginx/conf.d/default.conf | |
| EXPOSE 80 | |
| ``` | |
| --- | |
| ## 15. Security Requirements | |
| - **Never store Gmail passwords** — OAuth tokens only, encrypted at rest (AES-256) | |
| - **Encrypt all API keys** — Use environment variables, never commit to git | |
| - **JWT sessions** — Short-lived access tokens (15 min) + refresh tokens (7 days) | |
| - **HTTPS only** — Enforce in production (Caddy auto-TLS or Cloudflare) | |
| - **Rate limiting** — API rate limits (express-rate-limit) + respect Gmail API quota | |
| - **CORS** — Restrict to known frontend origins only | |
| - **Input validation** — Zod schemas on every API endpoint | |
| - **SQL injection prevention** — Parameterized queries via ORM (Drizzle/Prisma) | |
| - **Role-based access** — Admin can manage settings, Viewer can only see dashboard | |
| - **Audit log** — Log every scan operation with user, timestamp, results | |
| - **Content Security Policy** — Strict CSP headers in production | |
| --- | |
| ## 16. Sample Interac Email Structure (for AI testing) | |
| ``` | |
| From: notify@payments.interac.ca | |
| To: montreal.finances@iccameriques.org | |
| Subject: INTERAC e-Transfer: John Doe vous a envoyé de l'argent | |
| Bonjour, | |
| John Doe vous a envoyé un virement INTERAC de 150,00 $ (CAD). | |
| Le virement a été automatiquement déposé dans votre compte. | |
| Référence: CA1b2c3d4e5f | |
| Message de l'expéditeur: Dime pour mars 2025 | |
| Merci d'utiliser le Virement INTERAC. | |
| ``` | |
| --- | |
| ## 17. Internationalization Notes | |
| - The app UI should be in **French** by default (matching the ICC operational language) | |
| - Use `react-i18next` for translations (French primary, English secondary) | |
| - Status labels: Déposé, En attente, Expiré, Annulé | |
| - Currency format: `1 500,00 $` (French-Canadian format using `Intl.NumberFormat('fr-CA', { style: 'currency', currency: 'CAD' })`) | |
| - Date format: `YYYY-MM-DD` (ISO) by default — use `date-fns` with `fr-CA` locale | |
| - All form labels, buttons, error messages, and toasts in French | |
| --- | |
| ## 18. Implementation Checklist | |
| ### Phase 1: Project Scaffolding & Auth | |
| - [ ] Initialize monorepo (pnpm workspaces or npm workspaces) | |
| - [ ] Scaffold Vite + React + TypeScript frontend (`npm create vite@latest`) | |
| - [ ] Set up Tailwind CSS 4 + shadcn/ui component library | |
| - [ ] Scaffold Express.js backend with TypeScript | |
| - [ ] Set up PostgreSQL + Drizzle ORM with migrations | |
| - [ ] Implement Google OAuth 2.0 flow (frontend redirect + backend callback + JWT) | |
| - [ ] Build login page with "Se connecter avec Google" button | |
| - [ ] Implement protected routes and auth middleware | |
| - [ ] Set up shared package with types and constants | |
| ### Phase 2: Email Scanning Engine | |
| - [ ] Implement Gmail API service (search + fetch full MIME body) | |
| - [ ] Implement IMAP fallback service | |
| - [ ] Build scan date range system with 3 presets: today, last 7 days, custom | |
| - [ ] Implement `resolveScanDates()` helper (shared package) | |
| - [ ] Implement `buildGmailQuery()` with `after:` / `before:` date operators | |
| - [ ] Implement `buildIMAPSearch()` with `SINCE` / `BEFORE` date filters | |
| - [ ] Build ScanControls UI component (ToggleGroup + DatePicker for custom range) | |
| - [ ] Add date validation (start < end, end not in future) | |
| - [ ] Build scan job system (BullMQ or simple async queue) | |
| - [ ] Implement batched processing for large date ranges (50 emails per batch) | |
| - [ ] Add rate limiting between Gmail API calls (respect quota) | |
| - [ ] Add deduplication (check email_id before processing) | |
| - [ ] Add `forceRescan` option to re-process existing emails | |
| - [ ] Implement WebSocket for real-time scan progress with date range context | |
| - [ ] Build progress bar UI showing batch count + current email + date range | |
| - [ ] Add quick-scan buttons in dashboard header ("Aujourd'hui" / "7 jours") | |
| - [ ] Build scan history page showing past scans with date ranges and results | |
| ### Phase 3: AI Integration | |
| - [ ] Create unified `AIProvider` interface + factory | |
| - [ ] Implement Anthropic Claude adapter | |
| - [ ] Implement OpenAI GPT adapter | |
| - [ ] Implement Google Gemini adapter | |
| - [ ] Implement OpenRouter adapter | |
| - [ ] Implement Ollama (local) adapter | |
| - [ ] Build extraction prompt + Zod JSON validation layer | |
| - [ ] Add retry logic with exponential backoff | |
| - [ ] Build Settings page for AI provider/model selection | |
| ### Phase 4: Branch Routing & Business Logic | |
| - [ ] Implement branch routing with shared BRANCH_MAPPING | |
| - [ ] Seed database with all 48 branch email mappings | |
| - [ ] Build Settings page for managing branch mappings (CRUD) | |
| - [ ] Handle "Non classifié" fallback routing | |
| ### Phase 5: Dashboard UI | |
| - [ ] Build main TransactionTable with TanStack Table v8 | |
| - [ ] Implement all columns: Date, Expéditeur, Montant, Référence, Succursale, Statut | |
| - [ ] Add status badges with color coding | |
| - [ ] Build FilterPanel (branch multi-select, date range, status, amount) | |
| - [ ] Add full-text search with debounce | |
| - [ ] Add sortable column headers | |
| - [ ] Add pagination (25/50/100 per page) | |
| - [ ] Build SummaryBar with statistics | |
| - [ ] Implement row expansion for full transaction details | |
| - [ ] Add bulk selection + actions (export, mark reviewed) | |
| - [ ] Connect WebSocket for live "transaction:new" updates | |
| - [ ] Build branch quick-filter tabs | |
| ### Phase 6: Receipts & Export | |
| - [ ] Implement PDF receipt template with @react-pdf/renderer | |
| - [ ] Build server-side PDF generation with PDFKit | |
| - [ ] Add single receipt download from row action menu | |
| - [ ] Implement batch PDF export endpoint | |
| - [ ] Implement CSV export | |
| - [ ] Implement Excel (xlsx) export via SheetJS | |
| ### Phase 7: Reports Page | |
| - [ ] Monthly summary by branch (bar chart) | |
| - [ ] Trend line: total amount over time (line chart) | |
| - [ ] Status distribution pie chart | |
| - [ ] Top senders table | |
| - [ ] Date range selector for custom report periods | |
| ### Phase 8: Polish & Deploy | |
| - [ ] Error handling and user-friendly error messages (French) | |
| - [ ] Loading skeletons and progress bars | |
| - [ ] Responsive design (mobile-friendly dashboard) | |
| - [ ] Dark mode support (Tailwind dark: prefix) | |
| - [ ] Docker Compose for local development | |
| - [ ] Production deployment (Vercel + Railway or Docker on VPS) | |
| - [ ] CI/CD pipeline (GitHub Actions: lint → test → build → deploy) | |
| - [ ] Write user documentation (French) | |
| - [ ] Set up monitoring (Sentry for errors, Uptime Robot for availability) | |
| ### Phase 9: Desktop Wrapper (Optional) | |
| - [ ] Wrap Vite build in Electron | |
| - [ ] Add native file save dialogs for PDF/CSV export | |
| - [ ] Add system tray with scan notifications | |
| - [ ] Build installers (Windows .exe, macOS .dmg) | |
| --- | |
| *This prompt contains everything needed to build the ICC Interac Manager as a Vite.js web application with Express backend. Feed it to Claude, GPT-4, or any capable coding assistant along with the specific phase you want to implement.* | |