© A MoneyFund Company
Window/picture
PUBLIC • NO LOGIN
Back to /port
Window — Port Mode Data Flow
Secure read-only access from local Advantage ODBC to remote users
PORT MODE DATA FLOW

From local Advantage ODBC
to your device (port mode)

This is exactly how it works when someone uses the /port page from a phone or remote computer. The ODBC part never leaves one machine.

PORT MODE PATH (what remote users actually use)
1. SOURCE (Local network only)
Advantage Database Server
On-prem Entrée DB (necssql).
arinvt01, artrans, arcusto, etc.
Inside the warehouse LAN • TCP
→
Local ODBC (TCP)
Advantage driver • 32-bit only
2. THE BRIDGE PC (only machine that talks ODBC)
entree-bridge.js
• 32-bit PowerShell (SysWOW64)
• System.Data.Odbc → Advantage driver
• Executes the real query locally
• Relay Poller (runs constantly)
Local HTTP server on :3099 (for direct LAN use only)
→
Outbound HTTPS only (from bridge PC)
No inbound connections ever accepted
3. CENTRAL (Supabase + Vercel)
Relay Queue + Snapshots
• shanetree_relay_requests (job queue)
• shanetree_snapshots (pre-pulled data)
• Storage (for large results like xlsx)
Bridge claims jobs + sends results here via HTTPS
→
HTTPS (read-only)
Browser never talks to bridge or DB
4. USER DEVICE
Browser on /port
Sees either:
• A pre-saved snapshot, or
• The result of a Bridge Relay job
All data arrives over normal HTTPS
EXACT SEQUENCE IN PORT MODE
  1. 1. Remote user on /port clicks to request fresh data (e.g. Low Slots or Subs for tomorrow).
  2. 2. The request is queued in Supabase (shanetree_relay_requests table).
  3. 3. The bridge poller (running on the warehouse PC) periodically calls the central API to claim pending jobs.
  4. 4. Bridge runs the actual ODBC query locally using 32-bit PowerShell + the Advantage driver. This is identical to a direct query.
  5. 5. Bridge sends the result back to the fulfill endpoint (small data goes in the DB row, big files go to Supabase Storage).
  6. 6. The user’s browser picks up the fulfilled result (via Supabase) and displays it in the UI. The user never connected to the bridge or the database.
HOW THE /SALES PAGE WORKS (CONCRETE EXAMPLE)
The /sales report is one of the more complex ones. It follows the exact relay/snapshot flow above, but does heavy work on the bridge:
  • Pulls invoice headers from arinvoi (dates, CUSTNO, COMPANY, SALESMN, ITOTAL, RPT_CASES, etc.) for the requested range.
  • Pulls line-level detail from artrans (current) UNION arytran (archive): ITEM, EXTAMOUNT, QTY, UNITPR, CLASS, BRAND, TEMP_ZONE on the line (plus fallback enrichment).
  • Server-side enrichment against arinvt01 for the authoritative CLASS and TEMP_ZONE (D/C/R/F) because transaction rows can be stale.
  • Bridge aggregates everything once: global totals, byClass, byTempZone (with separate case counts per zone), bySalesman, topCustomers (by $), topItems by $ and a separate topItems by qty, plus a full daily series with zone-bucketed cases (D/C/R/F/unknown) for the trend chart.
  • The entire structured payload is either saved as a snapshot on the bridge PC or delivered fresh through the relay fulfill step.
  • On the /port browser you receive this big JSON object over HTTPS. All charts, rankings, and the daily line are built client-side from it — no ODBC ever happens in the browser.
CONNECTION TYPES (WHAT ACTUALLY HAPPENS)
ODBC (local TCP)
Only between the Advantage DB server and the Bridge PC. Entirely inside the warehouse network. 32-bit driver.
HTTPS (outbound from bridge)
The bridge PC makes outbound calls to claim jobs and return results. The bridge accepts zero inbound connections from the internet.
HTTPS (browser)
Your phone/laptop only ever talks to the main app (Vercel) and Supabase. It receives snapshots or relay results.
No file copy or direct bridge exposure to remote users.
HTTPS vs SFTP — Which is more secure in this architecture?

Short answer: In the current design, HTTPS (outbound TLS) is at least as secure as SFTP — and often more secure in practice because of how the system is architected.

HTTPS (what is actually used today)
  • Modern TLS 1.3 with perfect forward secrecy.
  • The bridge PC makes only outbound connections. No ports are listening for inbound traffic from the internet. This is the single biggest security win.
  • Easy to lock down with firewall rules to only specific domains (Vercel + Supabase).
  • Matches the rest of the web stack (no need to run extra services on the sensitive machine).
SFTP (SSH File Transfer Protocol)
  • Also very strong cryptography (based on SSH, public-key auth, etc.).
  • However, to achieve the same "bridge only talks outbound" model with SFTP you would need the bridge to act as an SFTP client pushing files to a central server. This is more complicated than the current JSON + Supabase Storage approach.
  • Running an SFTP server on the office computer (the more common mental model) would require opening inbound ports — which directly contradicts the current security model and increases risk.

Conclusion: The protocol name matters less than the overall architecture. The current "no inbound listeners on the machine that has ODBC access to the DB" rule gives HTTPS a practical security advantage here over how SFTP would realistically be deployed for the same job.

Potential Improvements — Better Performance While Staying Secure

The current implementation runs on a personal office computer. It works, but has clear limitations in performance, reliability, and security hygiene.

1. Dedicated Hardware (Highest Impact)
Move the bridge process to a purpose-built, always-on mini-PC or small server located in the warehouse, on the same fast LAN as the Advantage database server.
  • Good choices: Intel NUC, Beelink, or used enterprise mini-PC (decent i5/i7 CPU, 16–32 GB RAM, SSD).
  • Run Windows + entree-bridge.js as a Windows Service under a dedicated low-privilege account.
  • This machine should be dedicated — no browsing, no email, minimal other software.
  • Benefit: Much lower ODBC latency (direct LAN instead of going through office PC + longer network path) + better sustained performance for heavy reports.
2. Network & Connection Improvements
  • Physical proximity to the DB server is the biggest performance lever for ODBC queries.
  • Ensure the outbound HTTPS path (to Vercel/Supabase) has good bandwidth and consistent latency. Office internet uplinks are often the hidden bottleneck for large result sets.
  • Keep the security model: the bridge machine only ever initiates outbound connections. No inbound ports.
3. Security Hardening on the Bridge Machine
  • Strict outbound firewall rules (only the exact domains and ports used by the relay).
  • Run the bridge under a highly restricted Windows service account.
  • Consider running it inside a VM or with additional isolation (AppLocker, etc.).
  • Automated patching + minimal attack surface.
4. Longer-term Architecture Ideas (Still Secure)
  • Use an outbound-initiated secure tunnel (Cloudflare Tunnel, Tailscale, WireGuard) from the dedicated bridge machine. This can give more reliable private connectivity without ever exposing the machine publicly.
  • More aggressive scheduled snapshot generation on the bridge during off-hours.
  • Proper monitoring and alerting on the bridge machine itself (CPU, ODBC errors, relay queue health).
  • If query volume grows significantly, a slightly more powerful dedicated box is still far cheaper and more secure than trying to expose the DB directly.

Core rule that must be preserved: The machine that can talk to the Advantage database via ODBC must never accept unsolicited inbound connections from the internet. Everything else (hardware choice, location, tunnel technology) can be optimized around that constraint.

For comparison: when you are on the same LAN and use the normal /entree page, your browser talks directly to the bridge at http://bridge-pc:3099. Port mode exists so people on phones or in other locations can still use the tools without that direct access.