# Local DB Clones — Staging & Prod

How to run the backend against the two local database clones (staging and prod)
and switch between them. The selector is the **`NODE_ENV`** environment variable,
which `app/config/config.json` keys off of (via Sequelize + `models/index.js`).

Both clones live on the **single default Postgres server (port 5432)** as separate
databases — no extra cluster to manage. One Postgres server hosts many databases
side by side; the port identifies the *server*, the database name identifies the
*clone*.

---

## 1. What exists locally

All on **PG17 @ port 5432** (the normal installed Postgres Windows service, auto-starts on boot):

| Env (`NODE_ENV`) | Database           | Source snapshot      | Migrations / tables |
|------------------|--------------------|----------------------|---------------------|
| `staging-clone`  | `staging_db_clone` | `Stagingdb_Snapshot` | 593 / —             |
| `prod-clone`     | `prod_db_clone`    | `Prod_snapshot`      | 491 / 380           |
| `local`          | `prod_db_clone`    | (same as prod-clone) | → points at the same `prod_db_clone` |

All three use `host: 127.0.0.1`, `port: 5432`, `username: postgres`, `password: 1234`.
Config blocks are in [`app/config/config.json`](app/config/config.json).

> **Note:** `local` and `prod-clone` now point at the **same** database (`prod_db_clone`).
> The old, separate `prod_db_clone` that used to be there was replaced by the fresh
> `Prod_snapshot`. Use `prod-clone` as the canonical name; `local` is kept only for
> backward-compat with anything that still defaults to it.

---

## 2. Switch the backend between clones

`NODE_ENV` set in the shell takes precedence over the value in `.env`
(dotenv v16 does not override already-set process vars). So just export it
before starting the server — no need to edit `.env`.

### PowerShell

```powershell
# --- Run against STAGING clone ---
$env:NODE_ENV = 'staging-clone'
npm run dev

# --- Run against PROD clone ---
$env:NODE_ENV = 'prod-clone'
npm run dev
```

`$env:NODE_ENV` only lasts for the current PowerShell window. Open a new window
(or re-set it) to change targets. Confirm what's active with:

```powershell
echo $env:NODE_ENV
```

### Git Bash

```bash
NODE_ENV=staging-clone npm run dev
NODE_ENV=prod-clone    npm run dev
```

> **Port-in-use note:** the app listens on `process.env.PORT || 3000`. If 3000 is
> already taken (e.g. another backend instance), start on a different port:
> `$env:PORT='3009'; $env:NODE_ENV='prod-clone'; npm run dev`

---

## 3. Run Sequelize migrations against a clone

Same selector — set `NODE_ENV`, then run the CLI:

```powershell
$env:NODE_ENV = 'staging-clone'
npx sequelize-cli db:migrate:status   # list applied (up) vs pending (down)
npx sequelize-cli db:migrate          # apply pending migrations
npx sequelize-cli db:migrate:undo     # roll back the last one
```

Swap `staging-clone` → `prod-clone` to target the prod clone instead.

---

## 4. DBeaver connections

Both clones are on the same server, so **one DBeaver connection** to `localhost:5432`
with "Show all databases" enabled will list both (`staging_db_clone`, `prod_db_clone`,
plus `cadmachdb`, `postgres`, etc.). To open a clone directly, set its name as the
default database:

| Connection    | Host      | Port | Database           | User       | Password |
|---------------|-----------|------|--------------------|------------|----------|
| Staging clone | localhost | 5432 | `staging_db_clone` | `postgres` | `1234`   |
| Prod clone    | localhost | 5432 | `prod_db_clone`    | `postgres` | `1234`   |

---

## 5. Reset / re-restore a clone

The clones are throwaway. To rebuild one from its snapshot:

```powershell
$env:PGPASSWORD = '1234'
$bin = "C:\Program Files\PostgreSQL\17\bin"

# --- STAGING ---
& "$bin\dropdb.exe"   -U postgres -h 127.0.0.1 -p 5432 --force staging_db_clone
& "$bin\createdb.exe" -U postgres -h 127.0.0.1 -p 5432 staging_db_clone
& "$bin\pg_restore.exe" -U postgres -h 127.0.0.1 -p 5432 -d staging_db_clone `
    --no-owner --no-privileges -j 4 `
    "C:\Users\naveen\Desktop\nucleus\files\Stagingdb_Snapshot"

# --- PROD ---
& "$bin\dropdb.exe"   -U postgres -h 127.0.0.1 -p 5432 --force prod_db_clone
& "$bin\createdb.exe" -U postgres -h 127.0.0.1 -p 5432 prod_db_clone
& "$bin\pg_restore.exe" -U postgres -h 127.0.0.1 -p 5432 -d prod_db_clone `
    --no-owner --no-privileges -j 4 `
    "C:\Users\naveen\Desktop\nucleus\files\Prod_snapshot"
```

> `--no-owner --no-privileges` remaps everything to `postgres` (the snapshots'
> original owners were `nucleusstaging` / `doadmin`). A few harmless GRANT/role
> warnings during restore are expected.
> `--force` on `dropdb` terminates any open connections first — close the app and
> any DBeaver query editors on that DB to avoid surprises.

---

## 6. Quick reference

| I want to…                         | Do this                                                        |
|------------------------------------|----------------------------------------------------------------|
| Run app on staging data            | `$env:NODE_ENV='staging-clone'; npm run dev`                   |
| Run app on prod data               | `$env:NODE_ENV='prod-clone'; npm run dev`                      |
| Check pending migrations (staging) | `$env:NODE_ENV='staging-clone'; npx sequelize-cli db:migrate:status` |
| Browse data                        | DBeaver → localhost:5432, pick the database (see §4)           |
| Rebuild a clone from snapshot      | drop → create → pg_restore (see §5)                            |