# VSR DB Bridge — live-data sidecar for the DB-structure report

A thin, **read-only** HTTP + SSE JSON bridge that lets the self-contained VSR
database-structure report (`docs/vsr-dev-db-structure-*.html`) render **live**
table rows. Browsers cannot speak the PostgreSQL binary wire protocol, so this
process sits in front of the databases and exposes them over CORS-enabled
HTTP/JSON.

It is **remote-first**: the dev RDS instance on AWS is the primary target;
QA and PROD follow the same pattern; the local Docker Postgres is a vestigial /
secondary fallback for offline work.

```text
┌──────────────────────┐        HTTP/JSON + SSE (CORS)        ┌───────────────────────────┐
│  DB-structure report │  ───────────────────────────────▶    │  vsr-db-bridge (:8787)    │
│  (HTML, file:// ok)  │  ◀───────────────────────────────    │  read-only SELECT, allow- │
└──────────────────────┘         rows[] / event-stream        │  list, pooled pg          │
                                                              └──────────────┬────────────┘
                                                     dev (RDS) │ qa │ prod │ local (vestigial)
                                                              ▼
                                            PostgreSQL (dev RDS on AWS is primary)
```

---

## Prerequisites

- **Node.js ≥ 24** (repo `.nvmrc`).
- **Dependencies installed once at the repo root.** The bridge has no
  `package.json` of its own — it reuses the `pg` driver already installed in
  `packages/vsr-graphql-server`:

  ```bash
  pnpm install          # from the repo root
  ```

- **Network access to the target database.** The dev RDS instance is only
  reachable over **VPN**. If `/health` shows
  `getaddrinfo ENOTFOUND …rds.amazonaws.com`, connect the VPN and retry.
  (Snowflake is public and unaffected by VPN state.)
- **Credentials** for the dev RDS instance (ask the team lead). They live only in
  `tools/vsr-db-bridge/.env.rds`, which is gitignored and never committed.

---

## Quick start

```bash
# From the repo root
node tools/vsr-db-bridge/server.mjs
```

On **first run** with no connection info, the bridge writes a template
`tools/vsr-db-bridge/.env.rds` (from `.env.shadow`) and prints a short setup
walkthrough. Fill in the dev RDS credentials, then re-run. The bridge still
starts either way — unconfigured targets simply report `not-configured`.

Health check:

```bash
curl -s http://localhost:8787/health
# { "ok": true, "port": 8787, "primary": "dev",
#   "dbs": { "dev": "up", "qa": "not-configured", "prod": "not-configured", "local": "not-configured" } }
```

Open the report and pick a source from the **Live table data** dock (defaults to
the dev RDS target). Use **Test** to check the bridge `/health`.

---

## Onboarding — zero to running (team walkthrough)

A new team member can go from a fresh clone to live dev data in a few minutes:

1. **Install dependencies** (once, from the repo root):

   ```bash
   pnpm install
   ```

2. **Start the bridge** — it bootstraps its own config on first run:

   ```bash
   node tools/vsr-db-bridge/server.mjs
   ```

   With no credentials yet, it creates `tools/vsr-db-bridge/.env.rds` from the
   committed `.env.shadow` template, prints a setup walkthrough, and keeps running
   with every target reported as `not-configured`.

3. **Add the dev RDS credentials** — open `tools/vsr-db-bridge/.env.rds` and fill
   in the dev values the team lead provides:

   ```ini
   RDS_HOST=<dev-rds-host>.rds.amazonaws.com
   RDS_USER=<user>
   RDS_PASSWORD=<password>
   ```

   The file is gitignored; the password stays on disk and is never printed or
   committed.

4. **Connect the VPN** — the dev RDS host only resolves over VPN.

5. **Re-run the bridge** and confirm the dev target is healthy:

   ```bash
   node tools/vsr-db-bridge/server.mjs
   curl -s http://localhost:8787/health      # expect  "dev": "up"
   ```

6. **Open the report** — `docs/vsr-dev-db-structure-*.html` in a browser, choose
   **dev RDS (AWS)** from the *Live table data* dock, then press **Test** and
   **Live**. You are now viewing live dev RDS rows.

To add **QA** or **PROD** later, copy the commented `QA_RDS_*` / `PROD_RDS_*`
blocks from `.env.shadow` into `.env.rds`, fill them in, and select that target in
the report. Treat PROD as read-only.

---

## Configuration

Credentials live in `tools/vsr-db-bridge/.env.rds` — **gitignored, never
committed**. Copy the template and fill it in, or let first-run bootstrap create
it for you:

```bash
cp tools/vsr-db-bridge/.env.shadow tools/vsr-db-bridge/.env.rds
```

Each target may be given as discrete fields (no URL-encoding needed) or a full
URL. Bare `RDS_*` is the **dev** target (backward compatible with existing files).

| Target | Env vars | Notes |
| --- | --- | --- |
| **dev** (primary) | `RDS_HOST` / `RDS_PORT` / `RDS_DB` / `RDS_USER` / `RDS_PASSWORD` (or `DEV_RDS_*`, or `RDS_DATABASE_URL`) | default `db=dev` |
| **qa** | `QA_RDS_HOST` / `QA_RDS_PORT` / `QA_RDS_DB` / `QA_RDS_USER` / `QA_RDS_PASSWORD` (or `QA_RDS_URL`) | pathway |
| **prod** | `PROD_RDS_*` (or `PROD_RDS_URL`) | read-only diagnostics only |
| local (vestigial) | `LOCAL_DATABASE_URL` | offline lab only |

TLS is enabled automatically for the remote targets (`rejectUnauthorized: false`,
suitable for dev RDS whose CA is not in the local trust store). Override the port
with `PORT`, or point at an alternate env file with `ENV_FILE=/path/to/file`.

---

## Routes

| Route | Description |
| --- | --- |
| `GET /health` | `{ ok, port, primary, dbs }` — per-target status |
| `GET /tables/:table?db=dev\|qa\|prod\|local&limit=200` | rows for one table (ETag / `If-None-Match` cached). `db` defaults to `dev`. |
| `GET /stream/:table?db=dev&interval=3000` | Server-Sent Events; emits `{ table, rows }` only when the data changes |

`:table` is validated against a fixed allow-list
(`users`, `accounts`, `account_user_access`, `carts`, `cart_items`, `orders`,
`order_items`, `order_form_uploads`, `notifications_subscriptions`). Only
read-only `SELECT`s are issued; untrusted input can never reach the SQL text.

---

## Deployment (running the sidecar)

The bridge is a single dependency-light `.mjs` (it reuses the `pg` driver already
installed in `packages/vsr-graphql-server`). Typical run modes:

**Foreground (local dev):**

```bash
node tools/vsr-db-bridge/server.mjs
```

**Background:**

```bash
node tools/vsr-db-bridge/server.mjs &   # or nohup … &, or a pm2/systemd unit
```

**Custom port / env file:**

```bash
PORT=9797 ENV_FILE=~/.config/vsr/db-bridge.env node tools/vsr-db-bridge/server.mjs
```

Stop it with the dev-stack skill teardown, or `lsof -ti TCP:8787 | xargs kill -9`.

It requires network reachability to the target database (dev RDS needs VPN). It
holds a small pool (`max: 4`) per configured target and issues only `SELECT`s.

---

## Security

- **Read-only:** the only SQL executed is `SELECT * FROM <allow-listed table> ORDER BY 1 LIMIT n`.
- **Allow-list:** table names are validated against a fixed set — no dynamic identifiers from the request reach SQL.
- **Secrets:** credentials stay in `.env.rds` (gitignored). The bridge reads the file directly (no shell sourcing) and never prints secret values to stdout or over the wire.
- **CORS:** open (`*`) so the `file://` report can read it — safe because the endpoint is read-only, allow-listed, and bound to localhost.
- **Never** point `prod` at anything but read-only diagnostics.

---

## Files

| File | Purpose |
| --- | --- |
| `server.mjs` | the bridge (HTTP/SSE, multi-target, bootstrap) |
| `.env.shadow` | committed template — copy to `.env.rds` |
| `.env.rds` | **gitignored** — your real credentials |
| `../../docs/vsr-dev-db-structure-*.html` | the report this bridge feeds |