TradeOps — Business Management & Invoicing Platform

TradeOps is a production-grade business management and invoicing platform built for a wholesale and retail trading operation. It automates manual spreadsheets, generates compliant tax invoices, tracks payments, and aggregates real-time business analytics under a zero-trust, VPN-isolated architecture.

---

What Is This Repository?

This is the public engineering documentation for the TradeOps platform. It details the system architecture, database design, network security choices, and product specifications.

The private production codebase is not public to protect client-specific operational data. This repository showcases the design-to-delivery rigor of building a custom, highly secure B2B business platform.

Who is this for? Engineering leaders, technical reviewers, and developers evaluating real-world system design decisions, network-level security patterns, and asynchronous data sync strategies.

---

The Problem This Solves

The Client formerly ran their entire trading business across a mix of handwritten physical ledgers and manual Excel spreadsheets. This process introduced several severe operational liabilities:

1. Lack of Auditability: Edits or deletions of sales/purchase entries left no history, making fraud detection and auditing impossible.
2. Manual & Error-Prone Invoicing: Every Indian GST tax invoice had to be typed individually in Excel. Mismatched buyer details, incorrect state tax calculations (CGST+SGST vs. IGST), and HSN lookup errors occurred regularly.
3. Stale Financial Tracking: Receivables and payables were tracked mentally or via scattered notes. There was no real-time ledger indicating who owed what across partial payments.
4. No Centralized Analytics: Generating monthly sales trends, calculating overall tax liabilities, or tracking profit/loss required hours of manual spreadsheet math.
5. Security Risks: Accessing business records remotely required sharing spreadsheet files insecurely, exposing sensitive financial data.

---

High-Level Architecture

TradeOps uses a two-surface deployment model to completely separate the public landing page from private administrative capabilities:

flowchart TD
    subgraph Public Surface [Public Internet]
        A[Visitor / Browser] -->|HTTPS| B(Vercel Public Website)
        B -->|POST /api/contact| C(FastAPI Public Backend)
        C -->|Write-only| DB[(Neon PostgreSQL)]
    end

    subgraph Private Surface [Tailscale VPN Mesh]
        D[Admin Device / Enrolled Node] -->|Secure VPN tunnel| E(Vercel Private Admin Panel)
        E -->|Authenticated REST API| F(FastAPI Private Backend)
        F -->|SQL Queries| DB
        F -->|Async Background Task| G(Google Sheets API)
        F -->|GSTIN Verification| H[AppyFlow GSTIN API]
    end

Loading

For a detailed container diagram and data-flow specifications, see System Architecture.

---

Tech Stack & Design Rationale

Component	Choice	Engineering Justification
Frontend Framework	Next.js 14 (App Router)	Static site generation (SSG) for fast public loading and SEO; server-side rendering (SSR) for admin routing.
State Management	React Query + Zustand	React Query manages asynchronous server state caching and background refreshes; Zustand provides a lightweight store for auth tokens.
Backend Framework	FastAPI (Python 3.11)	Async-native execution loop prevents blocking on database queries and PDF/Excel generation; automatic Swagger generation.
ORM	SQLAlchemy 2.0 (Async)	Standard Python ORM utilizing asyncpg to run concurrent database operations without blocking I/O threads.
Database	Neon PostgreSQL	Serverless PostgreSQL providing ACID transactions, GIN indexes for JSONB line-item arrays, and flexible connection scaling.
Network Security	Tailscale VPN	Encrypts admin panel traffic at the network level. Admin endpoints do not expose a public login page — they are unreachable.
Asynchronous Ledger	Google Sheets API v4	Synchronizes writes in the background so accountants can access a familiar ledger without accessing the database.
PDF Engine	ReportLab	Generates pixel-accurate GST invoices in pure Python with no external dependencies (Puppeteer, WebSockets, or system binaries).
GSTIN Lookup	AppyFlow API	Validates and auto-fills buyer business information dynamically, backed by a 30-day PostgreSQL cache.

---

Key Architectural Decisions (ADRs)

1. PostgreSQL as Primary, Google Sheets as Sync-Only: All transactional reads, writes, and dashboard analytics are served directly by PostgreSQL. Google Sheets is a write-only target. A sync failure never impacts application availability.
2. Stateless JWT Auth: High-security session tracking using stateless JWTs (in-memory access tokens + HTTP-only cookies).
3. FastAPI BackgroundTasks for Syncing: Syncing entries to Google Sheets takes 200–800ms. To keep the UI fast, the backend writes to Postgres, returns an HTTP 201 immediately, and delegates the Google Sheets API write to a non-blocking background thread.
4. Network-Level VPN Isolation Over Login Walls: Admin surfaces are hosted behind a private Tailscale network. The admin API endpoints do not exist on the public-facing server (returning a 404 instead of a login wall). Enrolled admin nodes access the DB and API directly over VPN.
5. Server-Side Computed Payment Status: The database generated column (balance_due = grand_total - amount_paid) and strict backend validation make it impossible to manually set or mismatch payment status, eliminating human calculation error.
6. Fixed-Height ReportLab PDF Layout: Built a pixel-verified, fixed-height section layout in ReportLab that guarantees the generated invoice always renders exactly 10 item slots on a single A4 sheet, eliminating blank page overflows.

---

Detailed Documentation

Document	Purpose & Deep-Dive Focus
📐 System Architecture	Split-deployment topology, Tailscale VPN integration, and C4 containers.
🛡️ GSTIN Lookup Design	3-layer lookup pattern: client regex validation, Postgres caching (30-day TTL), and API fallback.
📊 Sheets Sync Mechanism	Asynchronous task execution, append-only Sheets schemas, and transaction logging (sync_log).
💸 Payment Tracking Design	Generated columns, server-calculated states, and partial payment history.
🗄️ Database Design	Database schema details, generated columns, index tuning, and JSONB structure.
🔌 API Reference	Complete REST API endpoint contracts for Auth, Invoices, Payments, and Analytics.
🗺️ Roadmap	Post-delivery analysis, what would be built next, and engineering tradeoffs.

---

Diagrams

The repository includes visual specifications rendering directly on GitHub:

• High-Level Architecture (C4 Model)
• GSTIN Auto-Fill Sequence
• Asynchronous Google Sheets Sync
• Entity-Relationship Diagram (ERD)

---

Mock Datasets

Mock data payloads representing core schemas are available for review:

• Sample GST Invoice JSON Payload
• Sample Payment Receipt JSON Payload
• For instructions on schema validation and API modeling, read the Demo README.

---

What I Would Do Differently (Retrospective)

• Cloudflare Zero Trust: For non-technical users, installing a client app (Tailscale) can be a minor point of friction. Cloudflare Access could sit in front of the Vercel admin panel as an identity wall, removing client app dependencies.
• Message Queues for Background Jobs: Using FastAPI BackgroundTasks processes jobs in-memory. If the server restarts, pending Sheets sync jobs are lost. Implementing a task queue (like Celery + Redis) would provide persistence and retry logic.
• Bi-directional Webhook Sync: The current sheets sync is write-only. If an admin edits a record, the original sheet row remains unaltered. Storing the spreadsheet row index in Postgres would allow update webhooks to maintain sync accuracy.