Skip to content

VirtualFD AI Build — Deep Dive Review

Date: 2026-02-11
Repo: /root/clawd/repos/virtualfd-ai-build
Brand: OYN (Own Your Numbers)
Partner: Kieran
Reviewer: Friday (subagent)

1. Architecture Review

High-Level Architecture

┌─────────────────────────────────────────────────────────┐
│  Hetzner VPS (Docker Compose)                           │
│                                                          │
│  ┌─────────┐   ┌──────────────┐   ┌──────────────────┐ │
│  │  Caddy   │──▶│  FastAPI      │──▶│  PostgreSQL 17   │ │
│  │  (TLS)   │   │  (Python 3.11)│   │  (asyncpg)       │ │
│  └─────────┘   └──────┬───────┘   └──────────────────┘ │
│                        │                                  │
│              ┌─────────┼─────────┐                       │
│              ▼         ▼         ▼                       │
│         Xero API   Gemini AI  Mailgun                    │
│         (OAuth2)   (2.5 Flash) (Email)                   │
│                                                          │
│  ┌───────────┐  ┌───────────┐  ┌────────────────────┐  │
│  │ Alloy     │  │ cAdvisor  │  │ Postgres Exporter  │  │
│  │ (OTel)    │  │           │  │                    │  │
│  └─────┬─────┘  └───────────┘  └────────────────────┘  │
│        ▼                                                 │
│  Grafana Cloud (traces, metrics, logs, Faro RUM)        │
│                                                          │
│  ┌──────────────────────────────────────────────────┐   │
│  │ Backup: pg_dump → Restic → Cloudflare R2         │   │
│  └──────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────┘

Backend (Python/FastAPI)

  • Entry: oyn/backend/api/main.py — FastAPI app with lifespan, CORS, session middleware
  • Routers: analysis (/api/analyze), auth (/auth/*), admin (/api/admin/*), xero (/xero/*), waitlist (/api/waitlist/*), profiles, sessions, UI routes, prompt endpoints
  • Processing Pipeline: processing/pipeline_v2.py — orchestrates Excel parsing → financial snapshot building → AI analysis → PDF report generation
  • AI: Dual-provider via core/model_interface.py — Gemini (Vertex AI) primary + OpenRouter (GPT-5, Claude, Grok) secondary. Multi-model comparison mode available.
  • Database: PostgreSQL 17 via asyncpg (raw queries, no ORM for data access). Alembic for migrations.
  • Auth: Custom JWT (HS256) + magic links + Google OAuth + Xero OAuth. Session cookies for admin HTML guard.

Frontend

  • No SPA framework — plain HTML/CSS/JS admin portal at oyn/frontend/admin/
  • Admin dashboard, login page, coming-soon/waitlist landing page
  • Static files served by FastAPI's StaticFiles mount

Integrations

  • Xero API: OAuth 2.0 with PKCE, token refresh, invoice-based AR/AP aging (cost-optimized), Trial Balance, P&L, Balance Sheet
  • Google OAuth: For user authentication
  • Mailgun: Magic link + waitlist emails (EU endpoint)
  • Google Sheets: Usage logging for FD visibility
  • Grafana Cloud: Full observability stack (OpenTelemetry traces, Alloy collector, cAdvisor, Postgres exporter, Faro RUM)
  • Cloudflare R2: Database backup via Restic

Deployment

  • Docker Compose on Hetzner VPS (separate compose files for local/stg/beta/prd)
  • Caddy reverse proxy with auto-TLS, security headers (HSTS, CSP, X-Frame-Options DENY)
  • GitHub Actions: Build → GHCR push → Auto-deploy staging → Manual deploy prod
  • Ansible: deploy.yml handles .env generation, compose up, health check, Alembic migrations
  • Secrets: Infisical for secret management (.infisical.json present), secrets injected via Ansible env lookups

2. Security Audit

✅ Strengths

Area Finding
JWT hardening Fail-fast in customer mode if JWT_SECRET is empty/default. Strong validation.
PKCE Xero OAuth uses PKCE (S256) — best practice
Session cookies HttpOnly, Secure (in prod), SameSite=Lax, HMAC-signed
Docker security Non-root user (UID 10001), no-new-privileges, read_only filesystem, tmpfs for /tmp
Caddy headers HSTS preload, X-Frame-Options DENY, CSP, nosniff, no-referrer
Data retention Background task prunes sensitive files (24h), flow logs (30d), reports
Rate limiting Magic links (3/15min/email), admin invites (5/hr/admin), waitlist (10/min/IP)
User enumeration prevention Magic link always returns 202; waitlist always returns success
API docs disabled in prod OpenAPI/Swagger/ReDoc disabled when APP_ENV=production
Audit logging All admin actions logged to audit_log table
Token revocation Xero tokens revoked on disconnect; magic links single-use
Backup strategy Daily pg_dump → Restic → Cloudflare R2 with retention (14d/8w/12m)
Gitleaks .gitleaks.toml present for secret scanning

⚠️ Concerns

Priority Issue Detail
P0 No JWT token blacklist/revocation Logout deletes cookies but JWT remains valid until expiry. jwt_expiry_seconds=604800 (7 days in prod!) means a stolen token is usable for a week.
P0 AUTH_ENABLED defaults to false in prod compose docker-compose.prd.yml sets AUTH_ENABLED: ${AUTH_ENABLED:-false}. If the env var isn't set, auth is completely disabled. This is a footgun.
P1 Session secret fallback session_secret = settings.session_signing_secret or settings.jwt_secret or "dev-session-secret" — in dev, this falls through to a hardcoded string. Not exploitable in prod (JWT_SECRET is enforced), but the fallback chain is fragile.
P1 In-memory rate limiting Rate limits stored in Python dicts — reset on restart, not shared across workers. Fine for single-process Uvicorn but won't scale.
P1 Xero tokens stored in plaintext access_token and refresh_token stored as plain strings in xero_connections table. Should be encrypted at rest (application-level encryption).
P1 Google OAuth access_token stored in DB oauth_accounts table stores Google's access_token in plaintext. Unnecessary — only needed for initial user info fetch.
P1 No CSRF protection on state-changing POST endpoints SessionMiddleware is present but CSRF tokens aren't validated on form submissions. The SameSite=Lax cookie mitigates most vectors, but not all.
P2 CSP allows unsafe-inline/unsafe-eval script-src 'self' 'unsafe-inline' 'unsafe-eval' defeats much of CSP's XSS protection. Should move to nonce-based CSP.
P2 Error messages leak stack traces analyze_excel_files catches generic Exception and returns f"Processing failed: {str(e)}" — could leak internal paths/library names.
P2 No request size limit at Caddy level Large file uploads could be used for DoS. Only Python-level max_file_size_mb=100 exists.
P2 /xero/connections/{id}/test-reports endpoint Comment says "development testing only" but no guard against production use beyond standard auth.
P2 Grafana Faro collector URL exposed in compose The Faro collector URL is in plaintext in docker-compose.prd.yml. Not a secret per se (it's a public collector endpoint), but worth noting.

SQL Injection

Not vulnerable — all database queries use parameterized queries via asyncpg ($1, $2, etc.). The admin search uses ILIKE $1 with a pattern, which is safe.

XSS

Low risk — API returns JSON only; no server-side HTML rendering with user input. The admin frontend is static HTML. The CSP unsafe-inline is the main concern.

3. Code Quality

Positives

  • Well-structured: Clean separation of concerns (routes → processing → core → db → models)
  • Comprehensive docstrings: Almost every function has detailed docstrings with Args/Returns/Raises
  • Type hints: Used throughout (Pydantic models, type annotations)
  • Error handling: Consistent patterns — specific exceptions caught first, generic fallback, logging at every level
  • Tooling: Ruff, Black, isort, mypy configured in pyproject.toml; pre-commit hooks
  • Data policies: customer vs internal mode controls data retention, debug logging, upload persistence

Issues

Issue Detail
57 test files, 5 broken tests/broken_tests/ contains 5 test files that presumably don't pass. Technical debt.
No test for auth/admin/xero routes test_auth.py, test_admin.py exist but testing is unclear without running them. Many tests are financial-logic focused (good) but integration test coverage for API routes appears thin.
Dead code: Supabase/GoTrue Settings still have supabase_url, supabase_service_key, gotrue_jwt_secret marked "(deprecated - will be removed)". Should be cleaned up.
Dead code: build/ directory Contains old virtualfd/extract/ module that appears to be superseded.
Dual JSON sanitizers _sanitize_non_finite in endpoints.py and _sanitize_json in xero.py do the same thing with different implementations. Should be unified.
Lambda debug logging in production code endpoints.py line: "ai_insights": (lambda x: (logger.info(f"🚀 ABOUT TO CALL..."), _format_ai_insights(x))[1])(...) — debug lambda left in production code.
Excessive debug logging Multiple logger.info(f"🔍 ...") debug statements in _format_ai_insights. Should be logger.debug.
100MB max file size max_file_size_mb=100 is very generous for Excel files. Could be used for memory exhaustion.

4. Feature Map

Core Features

Feature Description Status
Excel Upload Analysis Upload .xlsx/.xls files → extract financial data → AI analysis → PDF report ✅ Production
Xero Integration OAuth connect → fetch TB/P&L/BS/invoices → analyze ✅ Production
AI Financial Analysis Gemini 2.5 Flash (primary) + OpenRouter multi-model ✅ Production
PDF Report Generation WeasyPrint-based FD-style financial reports ✅ Production
Magic Link Auth Passwordless login via email ✅ Production
Google OAuth Social login ✅ Production
Admin Portal User management, invitations, audit log ✅ Production
Waitlist/Landing Page get.ownyournumbers.app email collection ✅ Production
Usage Logging Google Sheets integration for FD visibility ✅ Production
Observability OpenTelemetry + Grafana Cloud (traces, metrics, RUM) ✅ Production
Data Retention Auto-pruning of sensitive data and reports ✅ Production

API Endpoints

Method Path Auth Description
POST /api/analyze No* Upload Excel files for analysis
GET /api/health No Health check
GET /api/health/ai No AI provider status
POST /auth/magic/request No Request magic link
POST/GET /auth/magic/verify No Verify magic link
GET /auth/oauth/google/authorize No Start Google OAuth
GET /auth/oauth/google/callback No Google OAuth callback
GET /auth/me Yes Current user profile
POST /auth/logout No Clear session cookies
GET /api/admin/users Admin List users
POST /api/admin/invite Admin Invite user
PATCH /api/admin/users/{id}/role Admin Update role
DELETE /api/admin/users/{id} Admin Delete user
POST /api/admin/revoke Admin Revoke user
GET /api/admin/audit Admin Audit log
GET/POST/DELETE /api/admin/invitations/* Admin Invitation management
GET /api/admin/email/diagnostic Admin Email config check
GET /xero/connect Yes Start Xero OAuth
GET /xero/callback No Xero OAuth callback
GET /xero/connections Yes List Xero connections
DELETE /xero/connections/{id} Yes Disconnect Xero org
GET /xero/connections/{id}/test-reports Yes Test Xero data fetch
POST /xero/connections/{id}/analyze Yes Run Xero analysis
POST /api/waitlist/signup No Waitlist signup

*/api/analyze — auth depends on AUTH_ENABLED flag (currently defaults to off in prod compose).

Financial Processing Pipeline

  1. File Discovery → sort/classify uploaded Excel files
  2. Excel Parsing → Trial Balance, GL, AR aging, AP aging extraction
  3. Account Classification → Multi-signal classifier for account types
  4. Snapshot Building → Monthly snapshots + unified snapshot (trends, burn rate, cushion)
  5. Data Quality Assessment → Sanity checks, reconciliation, validators
  6. AI Analysis → Multi-model pipeline (Gemini + optional OpenRouter)
  7. PDF Report → WeasyPrint FD-style report with cover page, KPIs, AI insights
  8. Usage Logging → Google Sheets for FD tracking

Financial Metrics Calculated

  • Net cash position, cash in bank
  • Burn rate (monthly cost base)
  • Cushion (runway in months)
  • Working capital ratio
  • AR/AP aging buckets (current, 1-30, 31-60, 61-90, 90+)
  • Revenue growth, gross margin, operating margin
  • Top customers with debt, upcoming payments
  • Risk rating (AI-generated)
  • VAT/payroll due date inference (UK + Swiss policies)

5. Business Analysis

Market Position

OYN targets SME owners and their accountants/bookkeepers who use Xero, offering automated "Virtual FD" analysis — the kind of financial health check that a Finance Director would do, but automated and affordable.

Competitors: Fathom, Spotlight Reporting, Futrli, Syft Analytics, Float (cash flow), Fluidly.

Differentiators

  1. AI-powered narrative — Not just charts; generates FD-style written analysis with risk ratings and action items
  2. PDF "month end deck" — Familiar format for accountants; looks professional
  3. Cost-optimized Xero integration — Invoice-based aging (2 API calls vs N) is smart for Xero's 2026 pricing changes
  4. Multi-model AI — Can compare outputs from different AI providers for confidence

Weaknesses

  1. No dashboard/SPA — Plain HTML admin panel. No interactive visualizations, no real-time data exploration
  2. No multi-tenant — Single-user feel; no concept of "firms" managing multiple clients
  3. Excel-first — The Excel upload path is the primary flow; Xero integration is secondary
  4. No recurring automation — User must manually trigger analysis; no scheduled monthly reports
  5. UK/Swiss only — VAT policies only cover UK and Switzerland

Features to Add for Competitiveness

Priority Feature Impact
High Interactive dashboard (React SPA) Table stakes for SaaS. Charts, drill-downs, trend visualization.
High Multi-client management Accountants manage dozens of clients. Need firm → client hierarchy.
High Scheduled monthly reports Auto-generate on Xero sync, email to stakeholders.
High Cash flow forecasting Extend from analysis to prediction. Use AR/AP aging + burn rate.
Medium Benchmarking Compare client metrics to industry averages (anonymized).
Medium Alerts/notifications "Your cash cushion dropped below 2 months" email/Slack alerts.
Medium White-label Let accounting firms brand the reports.
Medium QuickBooks/FreeAgent integration Expand beyond Xero.
Low Mobile app Quick KPI glance on phone.
Low AI chat "Ask questions about your financials" conversational interface.

6. Recommendations (Prioritized)

P0 — Critical (Fix Now)

  1. Fix AUTH_ENABLED default in production compose
    Change AUTH_ENABLED: ${AUTH_ENABLED:-false} to AUTH_ENABLED: ${AUTH_ENABLED:-true} in docker-compose.prd.yml. The current default means if the env var is accidentally unset, the entire API is unauthenticated.

  2. Reduce JWT expiry + add token revocation
    7-day JWT with no revocation mechanism is dangerous. Options:

  3. Reduce to 1 hour + implement refresh tokens
  4. Add Redis-backed token blacklist checked on every request

  5. At minimum, reduce to 24h

  6. Encrypt Xero tokens at rest
    Xero refresh tokens grant 60-day access to financial data. Encrypt with an application-level key (e.g., Fernet) before storing in PostgreSQL.

P1 — Important (This Sprint)

  1. Clean up dead Supabase/GoTrue code
    Remove deprecated settings, references, and old auth patterns. Reduces confusion and attack surface.

  2. Remove debug lambda from production code
    The lambda x: (logger.info(...), _format_ai_insights(x))[1] pattern in endpoints.py is a code smell. Replace with normal function call + debug logging.

  3. Don't store Google OAuth access_token
    The Google access token is only needed during the callback to fetch user info. Don't persist it in oauth_accounts.

  4. Unify JSON sanitizers
    Extract _sanitize_non_finite to a shared utility module.

  5. Protect /xero/connections/{id}/test-reports
    Either remove this endpoint or add an internal_mode / APP_ENV != production guard.

  6. Move rate limiting to Redis
    When scaling beyond single-process, in-memory rate limits won't work. Prepare for this.

P2 — Improvements (Next Quarter)

  1. Tighten CSP
    Replace unsafe-inline/unsafe-eval with nonce-based CSP. This requires changing how the admin frontend loads scripts.

  2. Add Caddy request size limits
    Add request_body { max_size 100MB } to Caddyfile to prevent oversized uploads hitting the backend.

  3. Sanitize error messages
    Don't return str(e) to API clients. Return generic messages and log details server-side.

  4. Delete build/ directory
    Old build artifacts shouldn't be in the repo.

  5. Fix broken tests
    Address the 5 tests in broken_tests/ — either fix them or delete them if they test removed features.

  6. Add integration tests for API routes
    Auth flows, Xero OAuth callbacks, admin operations need automated test coverage.

  7. Build a React frontend
    The plain HTML admin portal is a significant limitation for user experience and product competitiveness.

  8. Add CSRF tokens
    For any form-based state-changing operations, validate CSRF tokens.

  9. Reduce max file size
    100MB for Excel files is excessive. 20-30MB would cover virtually all real workbooks while reducing DoS risk.

Summary

OYN/VirtualFD is a well-engineered MVP with solid foundations: good code structure, comprehensive financial processing pipeline, thoughtful security measures (PKCE, parameterized queries, non-root Docker, data retention), and full observability. The codebase shows careful thought about Xero's pricing model and cost optimization.

Critical gaps: The AUTH_ENABLED default in production, 7-day non-revocable JWTs, and plaintext Xero token storage need immediate attention given this handles sensitive financial data.

Biggest product gap: No interactive dashboard or multi-client management — these are table stakes for the competitive landscape. The Excel-upload-first approach limits scale; leaning into Xero integration with scheduled automation would be the strongest growth path.

Files reviewed: 40+ key files across backend, config, Docker, Ansible, CI/CD, and frontend.