On-prem data stays on-prem: Gateway architecture

On-prem data stays on-prem: the Gateway architecture, explained

For the technically curious, here's exactly how a query from the cloud reaches an on-prem database — and how the response comes back — without ever opening an inbound port.

The pieces

• Cloud control plane — hosted at aiinsights365.net. Stores datasource metadata, dispatches jobs, renders charts.
• Gateway agent — a small Windows desktop app installed inside the customer's network. Holds the local connection strings, opens local DB connections, executes SQL.
• Job queue table in the cloud DB — backed by a SQL table called GatewayJobs. Each job is a tuple of (jobId, gatewayGuid, jobType, querySql, connectionString?, resultJson?).

The protocol

1. The user clicks Test & Load Fields in the wizard.
2. The cloud inserts a row into GatewayJobs with status=Pending.
3. The gateway agent — which is already long-polling GET /api/gateway/poll over HTTPS — picks up the job.
4. The agent runs the SQL locally, captures the rows, and POSTs the result to /api/gateway/results over HTTPS.
5. Meanwhile, the cloud has been polling the same GatewayJobs row for status=Completed. When it sees the result, it returns the rows to the original web request.

What never crosses the WAN

• The on-prem database's IP / hostname (it's in the connection string that lives in the gateway's local config, encrypted at rest)
• The on-prem service account credentials
• Any data the agent didn't explicitly include in the result rows

What does cross the WAN

• The SQL the agent will run (visible to your gateway admins in the local log)
• The result rows after the agent has executed and chosen to send them back

Auditing

Every job that crosses the gateway is logged in:

• The cloud's GatewayJobs table (per-org)
• The gateway agent's local transactions.log (per-machine)

You can correlate by jobId across both.