Self-Hosting Appsmith and PostgreSQL on a VPS: Build a High-Speed Internal Inventory Management System for SMEs

Introduction: The Internal Tool Dilemma for Growing SMEs

As small and medium enterprises (SMEs) scale, managing core operations through spreadsheets quickly becomes a bottleneck. Data siloization, lack of real-time synchronization, and human error frequently disrupt inventory tracking, order fulfillment, and supply chain logistics. To solve this, businesses typically face two costly extremes: purchasing rigid, expensive SaaS platforms, or funding protracted custom software development cycles.

However, a modern alternative has emerged that balances speed, cost, and customization: Low-Code development coupled with self-hosted infrastructure. By deploying Appsmith—an open-source low-code internal tool builder—alongside a robust PostgreSQL database on a virtual private server (VPS), your business can build a tailored internal Enterprise Resource Planning (ERP) or inventory management tool in days rather than months. This guide provides an architectural blueprint and step-by-step methodology to implement this high-performance stack.

Why Choose the Appsmith + PostgreSQL + VPS Stack?

Before diving into the implementation details, it is crucial to understand why this specific technology stack offers an unparalleled return on investment (ROI) for SMEs:

• Absolute Data Ownership and Security: By self-hosting on a VPS, your proprietary inventory data, vendor margins, and customer databases remain entirely under your control, mitigating third-party compliance and data breach risks.
• Elimination of Per-User Licensing Fees: Commercial SaaS tools charge per user, per month. Appsmith’s open-source community edition allows you to scale your internal workforce without exponential software costs.
• Enterprise-Grade Performance: PostgreSQL is renowned for its reliability, data integrity, and complex query performance, making it the ideal foundational database for transactional ERP systems.
• Rapid UI Development: Appsmith replaces manual HTML/CSS/React coding with a drag-and-drop interface, allowing your team to bind UI widgets directly to SQL queries instantly.

---

Architectural Overview and System Requirements

To ensure high availability and sub-second latency for your internal ERP, your self-hosted infrastructure must be properly sized. For a typical SME with 10 to 100 concurrent internal users, the following hardware allocation is highly recommended:

Recommended VPS Specification:
• CPU: 2 vCPUs (Dedicated threads preferred)
• RAM: 4 GB to 8 GB (To comfortably run Docker containers, Appsmith backend services, and PostgreSQL buffer pools)
• Storage: 40 GB+ NVMe SSD (For rapid read/write operations)
• OS: Ubuntu 22.04 LTS or Ubuntu 24.04 LTS

We will utilize Docker Compose to orchestrate our environment. This containerized approach ensures isolated environments, effortless backup processes, and simple version upgrades.

---

Step-by-Step Deployment Guide

Step 1: Preparing the VPS Environment

First, securely access your VPS via SSH and update your system packages to the latest stable versions. Run the following commands sequentially:

sudo apt update && sudo apt upgrade -y
sudo apt install curl git docker.io docker-compose -y

Ensure the Docker service is enabled and running automatically upon system boot:

sudo systemctl enable docker
sudo systemctl start docker

Step 2: Configuring the PostgreSQL Database

To avoid manual configuration errors, we will define both PostgreSQL and Appsmith within a unified docker-compose.yml file. Create a dedicated directory for your infrastructure deployment:

mkdir ~/internal-erp && cd ~/internal-erp
nano docker-compose.yml

Insert the following configuration layout into the file, ensuring you replace the placeholder passwords with strong, unique credentials:

version: "3"\n\nservices:\n  postgres_db:\n    image: postgres:15-alpine\n    container_name: erp_postgres\n    restart: always\n    environment:\n      POSTGRES_USER: erp_admin\n      POSTGRES_PASSWORD: SecureYourPasswordHere123\n      POSTGRES_DB: inventory_db\n    volumes:\n      - ./postgres_data:/var/lib/postgresql/data\n    ports:\n      - "5432:5432"\n\n  appsmith:\n    image: appsmith/appsmith-ce\n    container_name: appsmith_core\n    restart: always\n    ports:\n      - "80:80"\n      - "443:443"\n    volumes:\n      - ./appsmith_stacks:/appsmith-stacks\n    depends_on:\n      - postgres_dbSave and close the file. Launch the containers in detached mode by executing:
docker-compose up -d
Verify that both containers are running optimally by executing docker ps. Your Appsmith instance will now be accessible via your VPS IP address over standard HTTP.
---
Modeling the Inventory Schema for ERP Functionality
With your PostgreSQL container running, you must establish the database tables necessary to track inventory movements. Connect to your database instance using an administrative tool or via the command line, and execute the following DDL script to create a normalized, relational inventory schema:CREATE TABLE products (\n    id SERIAL PRIMARY KEY,\n    sku VARCHAR(50) UNIQUE NOT NULL,\n    name VARCHAR(255) NOT NULL,\n    description TEXT,\n    price NUMERIC(10, 2) NOT NULL,\n    current_stock INT DEFAULT 0,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE TABLE inventory_logs (\n    id SERIAL PRIMARY KEY,\n    product_id INT REFERENCES products(id) ON DELETE CASCADE,\n    transaction_type VARCHAR(10) CHECK (transaction_type IN ('IN', 'OUT')),\n    quantity INT NOT NULL,\n    notes TEXT,\n    operator VARCHAR(100),\n    logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);

This fundamental schema ensures structured relational integrity: the products table maintains master records, while the inventory_logs table tracks audit logs for every stock addition or removal transaction.

---

Building the Internal GUI via Appsmith

1. Establishing the Database Connection

Navigate to your Appsmith dashboard by entering your VPS IP address into a web browser. Follow the initial on-screen prompts to set up an administrative account. Once inside your new workspace:

1. Click on Datasources in the left sidebar and select PostgreSQL.
2. Input your database credentials defined in your configuration script. Use your VPS host IP or the internal Docker bridge network IP address.
3. Click Test Resolution to validate network connectivity, followed by Save Datasource.

2. Constructing the Inventory Dashboard UI

Appsmith offers an intuitive layout editor. To construct a high-speed stock control dashboard, drag and drop the following UI components onto the workspace canvas:

• Table Widget: Rename this to tbl_inventory. Bind this component to an SQL query that reads directly from your PostgreSQL instance (e.g., SELECT * FROM products ORDER BY id DESC;).
• Input Field Group: Create a form dedicated to introducing new products into the system or searching existing inventory lines by SKU or product name.
• JSON Form Widget: Deploy a transaction execution form. When a warehouse operator inputs stock variations, this form dynamically executes transactional updates.

3. Writing High-Speed SQL Triggers and Queries

To update your stock numbers securely when an operator creates an inventory transaction log, implement a PostgreSQL Transaction Block within Appsmith's query window. This guarantees atomic updates—preventing race conditions or incomplete data states:

BEGIN;\n\nINSERT INTO inventory_logs (product_id, transaction_type, quantity, notes, operator)\nVALUES ({{tbl_inventory.selectedRow.id}}, {{form_type.selectedOptionValue}}, {{input_qty.text}}, {{input_notes.text}}, {{appsmith.user.email}});\n\nUPDATE products\nSET current_stock = CASE \n    WHEN {{form_type.selectedOptionValue}} = 'IN' THEN current_stock + {{input_qty.text}}\n    WHEN {{form_type.selectedOptionValue}} = 'OUT' THEN current_stock - {{input_qty.text}}\n    ELSE current_stock\nEND\nWHERE id = {{tbl_inventory.selectedRow.id}};\n\nCOMMIT;

This query leverages Appsmith's syntax engine, parsing live user input arrays inside the double curly braces ({{ }}) straight into parameterized SQL statements securely, protecting your database against SQL injection vectors.

---

Best Practices for Maintenance, Performance, and Security

Operating critical internal software requires rigorous attention to maintenance protocols. Adhere to these enterprise-grade guidelines to preserve system health:

• Enforce SSL/TLS Certificates: Do not operate your internal ERP over unencrypted HTTP channels. Use Let’s Encrypt alongside an Nginx reverse proxy to configure HTTPS access encryption for your Appsmith domain endpoint.
• Automated Database Backups: Configure a daily automated pg_dump cron job on your VPS host. Shipping compressed database snapshots securely offsite to an isolated cloud object storage target prevents catastrophic data loss.
• Fine-Tune Postgres Indexing: As transactional volume expands into thousands of audit records, execute optimization indexing commands (such as CREATE INDEX idx_sku ON products(sku);) to ensure interface tables render quickly.

Conclusion

By self-hosting Appsmith and PostgreSQL on your own VPS infrastructure, your organization gains a robust, highly extensible ERP platform without the associated enterprise software price tag. This stack offers the unique blend of rapid development times seen in low-code systems with the infinite control and customizability inherent in open-source systems. As your internal operations shift, you can seamlessly expand tables, integrate external delivery APIs, and continually optimize workflows at zero additional licensing cost.