Purchase Order Management System (ERP Module)

A modular, web-based Purchase Order (PO) Management System built to simplify vendor management, inventory tracking, and the generation of purchase orders.

This project consists of a FastAPI backend that provides robust REST APIs (including Google OAuth authentication) and a clean, responsive HTML5, CSS3, Vanilla JavaScript & Bootstrap 5 frontend interface.

The database follows a relational schema designed for strict data integrity and multi-tenant security (user-based isolation).

• User-Centric Isolation: Most tables (Vendors, Products, Purchase Orders) include an owner_email or user_email foreign key linked to the Users table. This ensures that each user only manages their own supply chain data.
• Normalized Product Tracking: The Products table uses a Composite Unique Constraint on (owner_email, sku). This prevents a single user from creating duplicate SKUs while allowing different users to use the same SKU for different items.
• Hierarchical Order Structure:
  • Purchase_Orders (Header): Stores the primary reference (ref_no) and high-level metadata like total amount and order status.
  • Order_Items (Line Items): Linked via order_ref to that header. It uses a price_at_order field to snapshot the cost at the time of purchase, protecting historical financial records from future price fluctuations in the Products table.
• Vendor Management: Includes unique constraints on email and phone_no per user to maintain clean communication records and prevent duplicate vendor profiles.
• Data Integrity: All primary keys are supported by Sequences (Auto-increment) for Products and Vendors, while Purchase_Orders uses a custom ref_no string to support business-specific naming conventions.

• Google Authentication: Secure login using Google OAuth2. Token-based authentication (JWT via Cookies and Bearer).
• Vendor Management: Complete CRUD operations for adding, editing, and managing supplier vendors.
• Product Inventory: Track product names, SKUs, unit prices, and stock levels. Quickly adjust stock up or down directly from the table.
• Purchase Orders:
  • Generate dynamic, multi-item purchase orders.
  • Real-time calculation of subtotals, tax, and total amounts.
  • Status tracking (PENDING, DONE, REJECTED).
  • Automatic inventory replenishment when a PO is marked as DONE (Approved).
• Interactive Dashboard: High-level overview of total vendors, products, active POs, and recent order history.

Backend

• Python 3.x
• FastAPI: High-performance REST framework.
• SQLAlchemy: ORM for database modeling and queries.
• Uvicorn: ASGI web server.
• Authlib: For handling Google OAuth logic.

Frontend

• HTML5 & CSS3
• Bootstrap 5 & Bootstrap Icons: For rapid, responsive, and clean UI/UX.
• Vanilla JavaScript (ES6): Fetch API for backend integration, module separation without heavy frameworks (No React/Vue/Angular).

po_management_system/
├── backend/
│   ├── api/                   # API routers (auth, product, vendor, purchase_order)
│   ├── database.py            # DB engine and session configuration
│   ├── dependencies.py        # Shared FastAPI dependencies (auth token extraction)
│   ├── main.py                # FastAPI app entry point and CORS/Middleware config
│   ├── models.py              # SQLAlchemy database models
│   ├── schemas.py             # Pydantic data validation schemas
│   └── requirements.txt       # Python dependencies (assumed)
│
├── frontend/
│   ├── css/
│   │   └── style.css          # Global styling, ERP layout overrides
│   ├── js/
│   │   ├── api.js             # Reusable Fetch API wrappers and HTTP handlers
│   │   └── app.js             # Shared UI logic, formatting, and DOM utilities
│   ├── pages/
│   │   ├── dashboard.html     # Analytics and recent orders
│   │   ├── vendors.html       # Vendor CRUD
│   │   ├── products.html      # Product inventory CRUD
│   │   ├── purchase-orders.html # PO listing
│   │   ├── create-po.html     # Dynamic PO creation form
│   │   └── po-details.html    # PO summary and Approve/Reject action view
│   └── index.html             # Login Page (OAuth trigger)
└── README.md

1. Navigate to the backend directory:

   cd backend

2. Setup Virtual Environment (Optional but recommended):

   python -m venv venv
   source venv/bin/activate  # On Windows: venv\Scripts\activate

3. Install Dependencies:

   pip install -r requirements.txt

4. Environment Variables: Create a .env file in the backend/ directory with the following keys:

   SESSION_SECRET_KEY=your_secure_session_secret
   SECRET_KEY=your_jwt_secret_key
   ALGORITHM=HS256
   
   # Google OAuth Credentials
   GOOGLE_CLIENT_ID=your_google_client_id
   GOOGLE_CLIENT_SECRET=your_google_client_secret
   
   # Database Configuration
   PG_DATABASE_URI=postgresql://postgres:postgres@localhost:5432/postgres
   
   # Where the backend should redirect the user after Google login
   FRONTEND_URL=http://localhost:5500/pages/dashboard.html

5. Run the API Server:

   uvicorn main:app --reload

   The backend will start at http://localhost:8000.

The frontend does not require a build step since it uses Vanilla JS. However, it must be served over an HTTP server to avoid CORS/file protocol restrictions.

1. Navigate to the frontend directory:

   cd frontend

2. Start a local development server (Port 5500): (We recommend port 5500 to match the default CORS and redirect settings in your backend.)

   python -m http.server 5500

   (Alternatively, use the Live Server extension in VS Code).
3. Open the App: Navigate your browser to: http://localhost:5500/index.html

1. User visits index.html and clicks Sign In With Google.
2. Frontend redirects to http://localhost:8000/auth/login.
3. After Google consent, Google redirects to http://localhost:8000/auth/callback.
4. The backend registers/updates the user, creates a JWT, sets it in an HTTP cookie (samesite="lax"), and redirects the browser back to the FRONTEND_URL (Dashboard).
5. All subsequent api.js requests automatically include credentials: 'include' to pass the auth cookie to the backend.

1. Log in with a Google account.
2. Add a Vendor in the Vendors tab.
3. Add Products in the Products tab. Ensure you set the initial unit price and stock.
4. Create a Purchase Order: Select a Vendor, add products, adjust quantities. Calculations happen automatically.
5. Approve PO: Navigate to the PO details and mark it as DONE to automatically increment your product stock by the ordered amount.