Supplier Inventory: Building the Interface (AppSheet)

This is Part 2 of the Supplier Inventory series.

The Problem

A database is only as good as its input. The best schema in the world is useless if nobody enters data — or if they enter it wrong.

Remember the original story? I used Excel as a frontend to eliminate learning curves. Same principle here, different tool.

Operation staff does not always sit at desks. They’re on the site, scanning materials, logging deliveries. They need flexible, fast, foolproof data entry.

Why AppSheet?

I needed a frontend that could:

The tradeoff: Less control than custom code, but 10x faster to build. A perfect fit for proof of concept.

Architecture

┌─────────────────────────────────────────────────────────┐
│  AppSheet (Mobile App)                                  │
│  Non-technical users: browse inventory, log movements,  │
│  manage vendors — no SQL required                       │
└────────────────────────┬────────────────────────────────┘
                         │  live read/write
                         ▼
┌─────────────────────────────────────────────────────────┐
│  PostgreSQL 16 (DigitalOcean VPS, Docker)               │
│  Single source of truth for all procurement data        │
└─────────────────────────────────────────────────────────┘

AppSheet connects directly to the PostgreSQL database — no intermediate API layer. Changes in the app reflect in the database immediately.

App Views

The app has three main views designed for different user workflows:

1. Home — Current Inventory

Use case: Quick stock check. “How much bitumen do we have at Pekanbaru?“

2. Inventory Log — Movement History

Use case: Audit trail. “Where did this batch come from? When was it received?”

The supplier_id foreign key in inventory_movement enables supplier traceability — every receipt links back to its source.

3. Vendors — Supplier Directory

Use case: Contact lookup. “What’s the address of Supplier X?”

Design Decisions

Offline-First

Warehouse WiFi is unreliable. The app queues entries locally and syncs when connected. AppSheet handles conflict resolution automatically.

Validation at Entry

AppSheet lets you define validation rules:

[quantity] > 0                     -- No negative movements
[movement_type] IN ("RECEIPT", "CONSUMPTION")  -- Only valid types
ISNOTBLANK([material_id])          -- Required field

Catch errors at input, not in SQL queries later.

Minimal Taps

Most common actions take 3 taps or less:

1. Tap “Add Movement”
2. Select material from dropdown
3. Enter quantity, save

Speed matters when you’re logging 50 movements per shift.

Read-Only Where Appropriate

Some tables are read-only in the app:

• contract — Only procurement team modifies contracts
• supplier — Only admin adds new suppliers

Warehouse staff can view contracts but not edit. Permissions enforced at the AppSheet layer.

Connecting to PostgreSQL

AppSheet connects directly to the VPS:

1. PostgreSQL running in Docker on DigitalOcean VPS
2. Port 5432 exposed with firewall rules
3. AppSheet connects using the public IP + database credentials

AppSheet Cloud → DigitalOcean VPS (public IP:5432) → PostgreSQL

For a production system, I’d add:

• SSL/TLS encryption for the connection
• VPN or IP allowlisting
• A dedicated read-only database user for AppSheet

For a portfolio demo? Direct connection works fine.

What I’d Do Differently

1. Add barcode scanning — AppSheet supports device camera for scanning. Would speed up material selection.

2. Build a custom app — For production use, a Flutter or React Native app would give more control over UX.

3. Add approval workflows — High-value movements should require supervisor approval before syncing.

But for demonstrating the concept of connecting non-technical users to a relational database? AppSheet delivers.

Previous: ← Designing the Schema

Next: Building the Dashboard →

Try the Live App →