Inventory & E-Commerce

A full-stack Sage 50 inventory management platform with a customer-facing e-commerce portal, warehouse operations (picking, dispatch, stock takes, transfers), real-time ERP sync via a custom .NET 8 Windows connector, an Android barcode scanning companion app, and AI-powered product enrichment via Google Gemini.

📦 Sage Inventory & E-Commerce Platform

Long-Form Project Summary for KNWS Showcase

Inventory is a full-stack inventory management and e-commerce system built to bridge the gap between Sage 50 desktop accounting software and modern web-based warehouse operations. It combines a Python/Flask backend, a dynamic Bootstrap/HTMX/Alpine.js frontend, a custom .NET 8 Windows connector for real-time Sage 50 synchronisation, an Android barcode scanning companion app built in Kotlin, and AI-powered product enrichment via Google Gemini -- all tied together with WebAuthn/passkey authentication and a pluggable modular architecture.

The system manages the full lifecycle of inventory operations: from stock intake and warehouse storage through picking, packing, dispatch, and customer returns -- with a built-in e-commerce portal where customers can browse products, place orders, and track their status through an approval workflow.

Live at: https://qp.knws.co.uk

🚀 Project Goals

Create a modern web interface for Sage 50 inventory data, removing the need for direct Sage desktop access for day-to-day warehouse tasks.
Synchronise bidirectionally with Sage 50 in real time using a custom Windows connector service.
Support full warehouse operations -- stock takes, picking lists, dispatch notes, inter-warehouse transfers, and returns/RMA processing.
Provide a customer-facing e-commerce portal with product browsing, order submission, and order tracking.
Implement an order approval workflow -- customers submit orders, administrators review and approve, then approved orders sync back to Sage.
Build an Android companion app for barcode scanning and mobile warehouse operations.
Leverage AI for product enrichment -- auto-generate descriptions, categories, and metadata using Google Gemini.
Secure access with modern authentication -- WebAuthn/passkey support alongside traditional credentials.

🧠 Core Features

1. Real-Time Sage 50 Synchronisation

The centrepiece integration is a custom .NET 8 (C#) Windows service that acts as a bridge between the Flask web application and the Sage 50 desktop database:

Runs as a background service on the same Windows machine as Sage 50
Connects to Sage via the Sage Data Objects (SDO) COM interface
Communicates with the Flask backend over WebSocket for real-time bidirectional sync
Pushes product data, stock levels, pricing, and customer records to the web platform
Receives approved orders and writes them back into Sage as sales orders
Handles connection resilience, reconnection logic, and conflict resolution
Supports scheduled full syncs and event-driven incremental updates

This connector eliminates the traditional CSV import/export workflow that plagues most Sage integrations.

2. Warehouse Operations Suite

The platform provides comprehensive warehouse management tools:

Stock Takes -- Full and partial inventory counts with variance reporting
Picking Lists -- Generated from approved orders, optimised for warehouse layout
Dispatch Management -- Shipment creation, tracking number assignment, carrier integration
Inter-Warehouse Transfers -- Move stock between locations with full audit trail
Returns & RMA -- Return merchandise authorisation workflow with reason codes, condition grading, and restocking logic
Stock Alerts -- Low stock notifications, reorder point triggers

All operations maintain a complete audit history with user attribution and timestamps.

3. E-Commerce Customer Portal

A customer-facing storefront built into the platform:

Product catalogue with search and filtering
Category browsing and product detail pages
Shopping basket and order submission
Order history and status tracking
Account management and address book
Responsive design for desktop and mobile

The portal is styled with Bootstrap 5 and uses HTMX for seamless partial page updates without full reloads.

4. Order Approval Workflow

A structured multi-stage order pipeline:

Customer submits an order through the portal
Order enters review queue with all line items, quantities, and pricing visible
Administrator reviews -- can approve, modify, or reject
Approved orders sync to Sage 50 via the .NET connector
Customer receives status updates at each stage
Dispatch triggers once stock is picked and packed

This workflow prevents unauthorised purchases and gives administrators control over pricing and stock allocation.

5. Android Barcode Scanning App

A companion mobile application built natively in Kotlin:

Camera-based barcode and QR code scanning
Connects to the Flask backend via REST API
Supports stock take counting -- scan items and submit counts
Product lookup -- scan a barcode to view stock details, location, and pricing
Picking assistance -- scan items to confirm pick accuracy
Offline queue with sync-when-connected capability
Material Design UI following Android conventions

6. AI Product Enrichment (Google Gemini)

An integrated AI module that enhances product data automatically:

Takes raw product names and basic Sage data as input
Generates rich product descriptions suitable for e-commerce display
Suggests appropriate categories and tags
Creates SEO-friendly metadata
Processes product images for alt-text generation
Batch processing mode for enriching entire catalogues
Manual override and review before publishing

7. WebAuthn / Passkey Authentication

Modern passwordless authentication alongside traditional login:

WebAuthn registration and authentication flows
Passkey support for biometric login (fingerprint, Face ID)
Fallback to email/password authentication
Session management with secure cookies
Role-based access control (admin, warehouse staff, customer)
CSRF protection on all forms via Flask-WTF

8. Pluggable Module Architecture

The codebase is organised into independent, pluggable modules:

AI Module -- Gemini integration for product enrichment
Portal Module -- Customer-facing e-commerce frontend
Dropshipping Module -- Third-party fulfilment integration
Labels Module -- Shipping label generation and printing
Reports Module -- Sales, stock, and warehouse analytics
RMA Module -- Returns processing workflow

Each module registers its own routes, templates, and database models, allowing features to be enabled or disabled per deployment.

🛠 Technical Architecture

Backend

Language: Python 3.x
Framework: Flask with Blueprints for modular routing
ORM: SQLAlchemy with 33 models covering products, orders, customers, stock movements, users, sessions, and more
Database: MySQL
Routes: 20 route files organised by feature area
Authentication: Flask-Login + WebAuthn (py_webauthn)
Forms: Flask-WTF with CSRF protection
API: RESTful endpoints for the Android app and WebSocket for the Sage connector

Frontend

CSS Framework: Bootstrap 5
Interactivity: HTMX for server-driven partial updates
Client Logic: Alpine.js for lightweight reactive components
Templates: Jinja2 with template inheritance

Sage Connector

Platform: .NET 8 (C#)
Hosting: Windows Service
Communication: WebSocket (persistent connection to Flask backend)
Sage Interface: Sage Data Objects (SDO) COM automation

Mobile App

Language: Kotlin
Platform: Android (minimum SDK 26)
Camera: CameraX + ML Kit for barcode scanning
Networking: Retrofit for REST API communication
UI: Material Design 3

Infrastructure

Hosted on KNWS infrastructure
MySQL database on the dedicated database server
Flask application served via WSGI
.NET connector deployed on the Sage 50 Windows workstation

🔧 Pipeline & Workflow

Sage data flows through the .NET connector to the Flask backend in real time.
Warehouse staff use the web interface or Android app for daily operations.
Customers browse the portal, submit orders, and track status.
Administrators review and approve orders in the management dashboard.
Approved orders sync back to Sage 50 for invoicing and accounting.
AI enrichment runs on new products to generate e-commerce-ready descriptions.
Reports are generated on demand for sales analysis, stock health, and warehouse performance.

📈 Outcome & Impact

This project demonstrates:

Enterprise integration engineering -- real-time bidirectional sync with Sage 50 via a custom .NET connector
Full-stack web development -- Python/Flask backend with a modern HTMX/Alpine.js frontend
Mobile development -- native Android barcode scanning app in Kotlin
AI integration -- practical use of Google Gemini for product data enrichment
Modern authentication -- WebAuthn/passkey implementation for passwordless login
Modular architecture -- pluggable feature modules with independent routing and models
Warehouse domain expertise -- comprehensive coverage of inventory operations, from intake to dispatch to returns
E-commerce capability -- customer-facing portal with order approval workflows

The platform bridges the gap between traditional desktop accounting software and modern web-based operations, providing a unified system for inventory management, e-commerce, and warehouse operations.

Technology