Bridging Cloud and Local: A Database Command Queue Pattern

When your cloud UI needs to print a physical label on a USB printer sitting in a warehouse — you need a creative architectural solution. Here's the pattern I built.

The Problem

My custom ERP frontend runs on Retool Cloud. My label printer (Brother QL-700) is connected via USB to a Windows PC in the warehouse. My database (NeonDB) is in the cloud. When an employee clicks "Print Label" in Retool, a physical label needs to come out of a printer 3,000 km away from the Retool servers.

Retool Cloud can't talk to a local printer. There's no direct network path. Port forwarding is fragile. VPNs add complexity. Webhooks require a public endpoint.

I needed something simpler and more reliable.

The Solution: PostgreSQL as a Command Queue

The insight was that both sides — Retool Cloud and the local backend — already share one thing: the PostgreSQL database. Why not use it as a message broker?

Retool Cloud                     NeonDB (PostgreSQL)
    │                                    │
    │  INSERT INTO                       │
    │  extraction_command_queue           │
    │  (type='print', payload=...)       │
    │ ──────────────────────────────────→ │
    │                                    │
    │                                    │  ← POLL every 5s
    │                                    │     (SELECT ... WHERE
    │                                    │      status='pending')
    │                                    │
    │                     Local Backend (Windows)
    │                           │
    │                           │  Process command
    │                           │  Download label PDF
    │                           │  Send to Brother QL-700
    │                           │  UPDATE status='completed'
    │                           │
    │  ← Query result           │
    │    (status='completed')   │

The Engineering Details

Exclusive Locking

What if two backend instances are running? Or if the backend crashes mid-command? I implemented an exclusive lock pattern:

On startup, the CommandPoller acquires a lock by writing its hostname and PID to a backend_lock table
Before processing any command, it verifies it still holds the lock
If another instance tries to start, it sees the lock and backs off

Heartbeat Monitoring

A lock without a heartbeat is a deadlock waiting to happen. Every 30 seconds, the active backend updates its last_heartbeat timestamp. If the heartbeat is stale (>60 seconds), any new backend instance can steal the lock — the assumption being that the previous holder has crashed.

Command Types

The queue handles more than just printing:

Print label — Download carrier label from Wallapop API, generate barcode, send to Brother QL-700
Print LPN label — Generate Code128 barcode for inventory item, print on thermal label
Enrich item — Trigger the AI enrichment pipeline (scrape Amazon → categorize → generate description)
Run extraction — Trigger an on-demand extraction of orders/chats/listings

Failure Recovery

Commands can fail (printer offline, Amazon blocking scrape, network timeout). The system handles this:

Failed commands are marked with status='failed' and an error message
Retool shows the error to the user with a "Retry" button
On startup, the backend checks for orphaned commands (status='processing' but heartbeat stale) and resets them to 'pending'

Why This Works Better Than Alternatives

vs. Webhooks: No public endpoint needed. No SSL certs. No DDNS. The local backend is just a consumer polling a database
vs. Message queues (RabbitMQ, Redis): No additional infrastructure. PostgreSQL is already there. For my volume (~50 commands/day), it's perfect
vs. WebSocket: No persistent connection to maintain. Polling every 5 seconds is fine for label printing — a 5-second delay is imperceptible

Production Results

This system has been running in production for over a year. It processes print commands, enrichment requests, and extraction triggers reliably. The failover has been tested (unintentionally) multiple times when my local PC rebooted — the backend restarts, reclaims the lock, and resumes processing within 60 seconds.

The pattern is open-source: github.com/AspiranteD/cloud-local-command-bridge