## GeoLynx Web App Deployment Guide

### Guide to deploy the web app to a new VPS server

> Using OVHCloud Servers (Ubuntu)

Reference:
- https://docs.ovhcloud.com/en/guides/bare-metal-cloud/virtual-private-servers/starting-with-a-vps
- https://docs.ovhcloud.com/en/guides/bare-metal-cloud/virtual-private-servers/secure-your-vps

---

## Step 1: Initial Server Setup

- [x] Log in to the VPS and change the default password
- [x] `sudo apt update && sudo apt upgrade -y`

---

## Step 2: Secure the VPS

### 2.1 Set up SSH key-pair authentication

**First, a quick audit** — before adding your own key, confirm nothing's already there. On a genuinely fresh VPS where you only ever received a password, this should come back empty; if it doesn't, find out why before going further.

```bash
cat ~/.ssh/authorized_keys 2>/dev/null || echo "no file for $(whoami)"
sudo cat /root/.ssh/authorized_keys 2>/dev/null || echo "no file for root"
getent passwd | awk -F: '$3 >= 1000 {print}'   # any unexpected non-system users?
last -a                                         # login history so far
```

Normal, expected output for this last check on a fresh Ubuntu cloud VPS — **not** a red flag:
- `ubuntu` (UID 1000) — the default admin user Ubuntu cloud images create via cloud-init; this is just you.
- `nobody` (UID 65534) — a standard non-privileged system placeholder present on virtually every Linux box, shell is `/usr/sbin/nologin` so it can't be used to log in at all. It only shows up here because UID 65534 is conventionally kept outside the normal system-UID range, not because it's a real user.

Anything else in that list — especially an account with an actual login shell you don't recognize — is worth investigating before proceeding.

On your **local machine**, generate a key pair (skip if you already have one you want to reuse):

```bash
ssh-keygen -t ed25519 -C "your_email@example.com"
```

This will prompt you twice:

1. **`Enter file in which to save the key (/home/you/.ssh/id_ed25519):`**
   Just press Enter to accept the default location. Only bother changing this if you already have a default key in use for something else and want a separate one just for this server (e.g. `/home/you/.ssh/geolynx_vps`) — if you do, remember to reference that filename with `-i` when you SSH in later (`ssh -i ~/.ssh/geolynx_vps ...`).
2. **`Enter passphrase (empty for no passphrase):`**
   Optional extra layer of protection on the private key file itself — if someone steals your laptop/key file, they still can't use it without this passphrase. Press Enter twice for no passphrase (simplest, fine for a lot of setups), or set one if you want that extra protection (you'll be asked for it each time you use the key, unless you cache it with `ssh-agent`).

This produces two files: `id_ed25519` (private key — never share this) and `id_ed25519.pub` (public key — this is the one that gets copied to the server).

Copy the public key to the server (still using password auth at this point):

**On Windows 11**, `ssh-copy-id` isn't recognised because it doesn't exist there — it's a bash script that ships alongside OpenSSH on Linux/macOS, but Windows' built-in OpenSSH client (the optional Windows feature) only includes the core tools (`ssh`, `scp`, `ssh-keygen`, `ssh-agent`), not that wrapper script. Do the equivalent manually from PowerShell instead — this pipes your public key over SSH and appends it to the server's authorized_keys file:

```powershell
Get-Content $env:USERPROFILE\.ssh\id_ed25519.pub | ssh youruser@server_ip "mkdir -p ~/.ssh && chmod 700 ~/.ssh && cat >> ~/.ssh/authorized_keys && chmod 600 ~/.ssh/authorized_keys"
```

You'll be prompted for your (still password-based, at this point) login password one last time to authorize this.

> On Mac/Linux, `ssh-copy-id -p 22 youruser@server_ip` does the same thing in one command, if you're ever doing this from a machine that has it.

Confirm you can log in with the key **before** touching password auth:

```bash
ssh youruser@server_ip
```

**Using PuTTY?** PuTTY needs the private key in its own `.ppk` format, not the OpenSSH format `ssh-keygen` produces:

1. Open **PuTTYgen** → Conversions → Import key → select `id_ed25519` → click **"Save private key"** (not "Save public key" — the public half is already on the server from the ssh-copy-id step, nothing to do with it here) → save as `.ppk`. If it warns about saving with no passphrase, that's expected if you chose not to set one earlier.
2. **Launch PuTTY.** The window that opens *is* the "Session" screen (top of the left-hand category tree) — nothing to navigate to yet.
3. On that Session screen: enter the server's IP into **"Host Name (or IP address)"**, port `22` for now.
4. Below that, find the **"Saved Sessions"** box. Type a name for this connection (e.g. `GeoLynx VPS`) and click **Save** — it'll appear in the list underneath.
5. In the left-hand tree, click the **+** next to **Connection**, then **+** next to **SSH**, then click **Auth** (on newer PuTTY versions there's a sub-item under Auth called **Credentials** — click that instead if present).
6. Find **"Private key file for authentication"** and **Browse** to your `.ppk` file.
7. **Easy to miss:** scroll back up and click **Session** again at the top of the tree. Your saved session name should still be highlighted — click **Save** *again*. This is what actually writes the key setting into the saved session; skip it and PuTTY forgets the key as soon as you close the window.
8. Click **Open** to connect. Next time, just double-click the saved session name in the list — the key comes with it.

> ⚠️ **Important — password auth is still enabled at this point**, so a successful login here doesn't by itself prove the key worked. If PuTTY logs you straight in with no prompt (or only asks for the key's own passphrase, if you set one — not your account password), the key worked. If it prompts for your **account password** and you type it, you've just re-confirmed password auth, not the key — changing your password earlier has no bearing on this either way, the two methods are independent.
>
> The definitive check is the server's auth log, which records which method was actually used for each login:
> ```bash
> sudo grep "Accepted" /var/log/auth.log | tail -5
> ```
> You want to see `Accepted publickey for youruser` for your most recent session, not `Accepted password for youruser`.

**Using WinSCP for file transfers?** It's built on the same underlying engine as PuTTY, so it uses the same `.ppk` key — reuse the one already made above rather than converting again:

1. Open WinSCP, edit the site (or New Site): File protocol `SFTP`, Host name = server IP, Port `2222`, User name = your username, password field left blank.
2. Click **Advanced** (gear icon) → **SSH → Authentication** in the left tree.
3. Under "Private key file", browse to the same `.ppk` file.
4. **Save**, then **Login**.

(Any old saved password on an existing site from before password auth was disabled is harmless to leave, but fine to clear for tidiness.)

### 2.2 Harden `sshd_config`

Edit `/etc/ssh/sshd_config`:

```
Port 2222                     # pick a non-default port
PermitRootLogin no
PasswordAuthentication no
PubkeyAuthentication yes
```

Restart SSH:

```bash
sudo systemctl restart sshd
```

> ⚠️ **On Ubuntu 22.04+, this restart alone may not actually move the port.** Since 22.04, `openssh-server` listens via systemd socket activation — a separate `ssh.socket` unit binds to port 22 directly, independent of the `Port` line above. Restarting `sshd`/`ssh.service` doesn't touch what the socket is listening on. Symptom: old port (22) keeps working, new port doesn't respond at all.
>
> Check whether this is happening:
> ```bash
> sudo ss -tlnp | grep -E ':22|:2222'
> systemctl status ssh.socket
> ```
> If `ssh.socket` shows `active (listening)` and only `:22` shows up, disable socket activation so `sshd` binds its own port directly instead:
> ```bash
> sudo systemctl disable --now ssh.socket
> sudo systemctl enable --now ssh.service
> sudo ss -tlnp | grep ssh   # should now show :2222, not :22
> ```
> If that still shows `:22` with the same PID as before, `enable --now` didn't actually restart it — it only starts a unit if it isn't already running, and this one already was (started earlier via the socket). Force it explicitly:
> ```bash
> sudo systemctl restart ssh.service
> sudo ss -tlnp | grep ssh   # now shows a new PID, listening on :2222
> ```

> ⚠️ **`PasswordAuthentication no` may silently not take effect, even though the restart itself works.** Ubuntu cloud images ship drop-in configs under `/etc/ssh/sshd_config.d/*.conf` (e.g. `50-cloud-init.conf`), included via an `Include` line that's usually the very first line in `sshd_config` — processed *before* your own edit further down. For most directives, sshd uses first-occurrence-wins: if a drop-in sets `PasswordAuthentication yes` before your `no` is ever reached, your edit is silently ignored, with no error. Symptom: everything else about the hardening works fine, but password login (e.g. via WinSCP, or any other client) still succeeds.
>
> Check the actual effective setting, not just what you wrote:
> ```bash
> sudo sshd -T | grep -i passwordauthentication
> sudo grep -ri "passwordauthentication" /etc/ssh/sshd_config /etc/ssh/sshd_config.d/*.conf
> ```
> If it says `yes` despite your edit, find which drop-in is winning (alphabetically-first filename among the `.d/*.conf` files that sets it) and fix it there directly — that's the one actually taking effect:
> ```bash
> sudo nano /etc/ssh/sshd_config.d/50-cloud-init.conf   # change yes -> no
> sudo systemctl restart ssh.service
> sudo sshd -T | grep -i passwordauthentication   # confirm: no
> ```

> ⚠️ **Don't close your current session yet.** Open a brand new terminal and confirm you can log in on the new port with your key first (`ssh -p 2222 youruser@server_ip`). If it fails, you still have your original session open to fix it.
>
> Also double check UFW isn't blocking the new port if you've already enabled it (step 2.3 below): `sudo ufw allow 2222/tcp`.

### 2.3 Firewall (UFW)

```bash
sudo ufw allow 2222/tcp     # your new SSH port — do this before enabling ufw
sudo ufw allow 80/tcp
sudo ufw allow 443/tcp
sudo ufw enable
sudo ufw status
```

### 2.4 (Optional but recommended) fail2ban

```bash
sudo apt install fail2ban -y
sudo systemctl enable --now fail2ban
```

> You may see a **"Service restarts being deferred"** message listing things like `networkd-dispatcher.service`, `systemd-logind.service`, `unattended-upgrades.service`. That's not from fail2ban — it's `needrestart`, which Ubuntu runs after any apt install/upgrade to flag services still using an old version of a library that just got updated. All three are low-risk to restart individually, or just leave them and do a full reboot once the rest of the install (Step 3) is done — a reboot at that point is worth doing anyway, to confirm sshd (on the new port), UFW, Xvfb, and fail2ban all come back up on their own rather than only working because you set them up by hand this session.

### 2.5 Adding another user (a colleague, the client's IT, etc.)

Access is controlled entirely by what's *in* an account's `authorized_keys` — not by the account name — so give each person their own dedicated Linux account rather than sharing `ubuntu`. Separate accounts mean logs show who actually did what, and one person's access can be revoked without touching anyone else's. Sharing `ubuntu` instead means giving them the same full-`sudo`, indistinguishable-in-the-logs access as you — there's no way to do it partially.

```bash
sudo adduser colleaguename
```

Get their **public** key from them (never the private key — they generate their own pair on their own machine, same as 2.1, and send you only the `.pub` file):

```bash
sudo mkdir -p /home/colleaguename/.ssh
echo "paste-their-public-key-contents-here" | sudo tee /home/colleaguename/.ssh/authorized_keys
sudo chown -R colleaguename:colleaguename /home/colleaguename/.ssh
sudo chmod 700 /home/colleaguename/.ssh
sudo chmod 600 /home/colleaguename/.ssh/authorized_keys
```

If they need admin rights: `sudo usermod -aG sudo colleaguename`. If they only need limited access (e.g. checking logs), a narrower `/etc/sudoers.d/` entry is worth setting up instead of full `sudo` group membership.

Nothing else needs to change — `PermitRootLogin no`, `PasswordAuthentication no`, the UFW rule for port 2222, and fail2ban all already apply globally to every account, this one included. They connect the same shape as you: `ssh -p 2222 colleaguename@server_ip -i their_private_key`.

### 2.6 Adding access to a new machine (same user — e.g. your laptop as well as your PC)

This is the opposite case — same account (`ubuntu`), a second device for you. Since `PasswordAuthentication no` is already set, the original bootstrap trick from 2.1 (using the account password to self-service the first key) no longer works — there's no password fallback anymore. Instead, do it from a session that's **already** authenticated on the existing machine:

1. On the **new machine**, generate its own key pair — don't reuse/copy the private key from your existing machine between devices:
   ```bash
   ssh-keygen -t ed25519 -C "dave-laptop"   # descriptive comment so you can tell keys apart later
   ```
2. If the new machine also uses PuTTY, convert the key to `.ppk` via PuTTYgen and set up a saved session, same as 2.1.
3. From your **existing, already-connected** session (on your current machine), append — not overwrite — the new machine's public key to the same `authorized_keys` file:
   ```bash
   echo "paste-the-new-machine's-public-key-here" >> ~/.ssh/authorized_keys
   ```
4. Test connecting from the new machine before relying on it, and confirm it actually used the key (same check as 2.1):
   ```bash
   sudo grep "Accepted" /var/log/auth.log | tail -5   # want "Accepted publickey", for the new machine's session
   ```

---

## Step 3: Install required software

### 3.1 Apache2 + PHP

```bash
sudo apt install apache2 -y
sudo apt install php libapache2-mod-php php-pgsql php-mbstring php-xml php-curl php-zip php-gd -y
sudo a2enmod rewrite ssl
sudo systemctl restart apache2
```

Create a vhost at `/etc/apache2/sites-available/geolynx.conf` pointing at the app's `www/` folder (this is the document root, per the repo layout):

```apache
<VirtualHost *:80>
    ServerName yourdomain.co.uk
    DocumentRoot /var/www/geolynx-app/www

    <Directory /var/www/geolynx-app/www>
        AllowOverride All
        Require all granted
    </Directory>

    ErrorLog ${APACHE_LOG_DIR}/geolynx_error.log
    CustomLog ${APACHE_LOG_DIR}/geolynx_access.log combined
</VirtualHost>
```

```bash
sudo a2ensite geolynx.conf
sudo a2dissite 000-default.conf
sudo systemctl reload apache2
```

Before the real app is deployed (Step 4), create the doc root and drop in a placeholder to confirm Apache + PHP are actually working end to end:

```bash
sudo mkdir -p /var/www/geolynx-app/www
sudo chown -R www-data:www-data /var/www/geolynx-app
echo '<?php echo "GeoLynx"; ?>' | sudo tee /var/www/geolynx-app/www/index.php
```

Visit `http://server_ip/` in a browser — seeing "GeoLynx" confirms Apache is serving from the right document root and PHP is processing correctly. (Delete this file before Step 4 — it'll be replaced by the real app anyway, but no point leaving even a placeholder reachable longer than needed.)

### 3.2 Point the subdomain at this server (Dynu)

Before going further — set up the subdomain now on Dynu, e.g. `myclient.geolynx.co.uk`, pointing (A record) at this server's IP. Do this early because **Certbot (3.4) needs the domain resolving to this server** before it can issue a certificate, and DNS can take a little while to propagate — better it's already settled by the time you get there.

- [ ] Add the subdomain in Dynu, pointing at this server's public IP
- [ ] Once it's had time to propagate, confirm it resolves: `nslookup myclient.geolynx.co.uk`
- [ ] Confirm it actually reaches this box: visit `http://myclient.geolynx.co.uk/` and check you get the same placeholder page as the IP did
- [ ] Update the vhost's `ServerName` in `/etc/apache2/sites-available/geolynx.conf` to match (no `www.` variant needed — see the note in 3.4), then `sudo systemctl reload apache2`

### 3.3 PostgreSQL + PostGIS + pgRouting

> The dev box runs PG13; this server will likely end up on something newer (whatever Ubuntu's default repo ships, or later if you add the PGDG repo below). PG maintains strong SQL-level backward compatibility, so this generally isn't risky, but worth knowing:
> - **PG15+** stops non-owner roles creating objects in the `public` schema by default — irrelevant here since `db.php` connects as the `postgres` superuser/owner, but worth knowing if a less-privileged app role gets added later.
> - **PG14+** defaults `pg_hba.conf` auth to `scram-sha-256` instead of `md5` — fine for PHP's PDO/libpq, just different from the PG13 dev config.
> - **Collation/glibc version differences** between this server's OS and the dev box can silently corrupt text-based indexes (postcodes, addresses) after a restore — sort order changes but nothing errors. Cheap insurance: run `REINDEX DATABASE netplanner;` after restoring below, regardless of version. PG15+ will also explicitly warn (`collation version mismatch`) if it detects this.
> - Test the PostGIS/pgRouting-dependent code paths specifically after restore (map load, `project_auto_route.php`) rather than assuming they carry over.
> - PG13 itself hit end-of-life in Nov 2025, so it's already unsupported upstream — worth bringing the dev box forward to match at some point too.

Plain `apt install postgresql` only installs whatever single version is default for this Ubuntu release — no visibility into other options. To see (and pick from) every currently-supported major version, add the official PostgreSQL (PGDG) repo first:

```bash
sudo apt install curl ca-certificates -y
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt update
apt-cache madison postgresql   # lists every major version currently available to install
```

> ⚠️ Repo setup steps like this occasionally change — double check against https://www.postgresql.org/download/linux/ubuntu/ if this doesn't work as expected. Also worth checking https://www.postgresql.org/support/versioning/ directly for the current supported-version/EOL list rather than relying on anyone's memory of it.

> **Note (2026):** on a new-enough Ubuntu release, `apt-cache madison postgresql` may only show one major version (currently PG18) — PGDG generally only builds currently-supported PG majors for a brand-new LTS, and PG13–14 are already past end-of-life, so they were never packaged for it. Nothing wrong with that, just a bigger jump from the PG13 dev box than originally planned — before committing, confirm PostGIS/pgRouting actually have packages built for whatever version you land on (ecosystem packages can lag a few months behind a brand-new PG major):
> ```bash
> apt-cache madison postgresql-18-postgis-3    # swap 18 for whatever version madison actually offers
> apt-cache madison postgresql-18-pgrouting
> ```
> Empty results mean no PostGIS/pgRouting for that PG version yet — no spatial features, which this app can't do without.

```bash
sudo apt install postgresql postgresql-contrib -y
psql --version   # note the major version, e.g. 18
sudo apt install postgresql-18-postgis-3 postgresql-18-pgrouting -y   # swap 18 for your version
```

Set up the database and extensions:

```bash
sudo -u postgres psql
```

```sql
CREATE DATABASE netplanner;
\c netplanner
CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;
\password postgres   -- set a strong password, matches www/fn/db.php
\q
```

Test the password actually works before moving on. `sudo -u postgres psql` (above) connects over the local Unix socket, which uses **peer** authentication by default on Ubuntu — that checks your OS user identity, not the password, so it'll let you in either way and doesn't prove anything. Force a TCP connection instead, which actually requires the password:

```bash
psql -h 127.0.0.1 -U postgres -d netplanner -W
```

#### Export the latest schema from dev

Don't restore from the checked-in `sql/geolynx_ddl.sql` blind — it drifts out of date as the dev DB evolves (confirmed: it currently has zero mention of `users.user_roles` / `users.role_permissions` / `users.user_item_permissions`, despite those being live parts of the permission system per CLAUDE.md). Always regenerate it fresh from dev instead.

On the **dev machine**, dump the current schema (no data yet):

```bash
pg_dump -U postgres -h localhost -d netplanner --schema-only --no-owner --no-privileges -f netplanner_schema_$(date +%Y%m%d).sql
```

`--no-owner`/`--no-privileges` skip `OWNER TO`/`GRANT` statements tied to dev-specific roles that may not exist on the new server.

Transfer the file to the new server. Since you're on Windows with PuTTY, use **`pscp`** (ships with the PuTTY suite) from PowerShell, or **WinSCP** if you'd rather drag-and-drop:

```powershell
pscp -P 2222 netplanner_schema_20260705.sql youruser@server_ip:/home/youruser/
```

#### Restore the schema

```bash
psql -h 127.0.0.1 -U postgres -d netplanner -f netplanner_schema_20260705.sql
```

(`-h 127.0.0.1` matters here, not just habit — without it, `psql` connects over the local Unix socket, which uses `peer` auth by default on Ubuntu and will reject you unless you're logged into the terminal as the `postgres` OS user. `-h` forces a TCP connection through the `host ... scram-sha-256` rule instead, which is what actually checks the password.)

(`sql/geolynx_ddl.sql` in the repo is a reasonable fallback only if you genuinely can't reach the dev DB — prefer the fresh export otherwise.)

#### Transfer data

**This deployment specifically** — the client's been beta-testing directly against the dev server, so the plan is bringing everything across (cleaning out test entries afterward) rather than cherry-picking tables. At ~75GB, split into three steps rather than one giant dump — a single-file restore of this size ran the disk out of space (100GB+ of expanded table/index data plus the 75GB dump file itself, on top of the OS, easily exceeds what looks like comfortable headroom). Smaller files are also easier to manage if something goes wrong partway.

Compress on the way, too — plain SQL format doesn't take pg_dump's `--compress` flag directly (that's custom/directory/tar formats only), but piping through `gzip` does the same job in one command, and SQL dumps like this compress very well. Restoring by decompressing straight into `psql` also means the full uncompressed file never has to sit on disk at all.

**1. On dev — export each part:**

```bash
pg_dump -U postgres -h localhost -d netplanner --data-only --no-owner --disable-triggers --schema=basedata | gzip > netplanner_data_basedata_$(date +%Y%m%d).sql.gz

pg_dump -U postgres -h localhost -d netplanner --data-only --no-owner --disable-triggers --schema=adhoc | gzip > netplanner_data_adhoc_$(date +%Y%m%d).sql.gz

pg_dump -U postgres -h localhost -d netplanner --data-only --no-owner --disable-triggers --schema=openreach | gzip > netplanner_data_openreach_$(date +%Y%m%d).sql.gz

pg_dump -U postgres -h localhost -d netplanner --data-only --no-owner --disable-triggers --exclude-schema=basedata --exclude-schema=adhoc  --exclude-schema=openreach| gzip > netplanner_data_rest_$(date +%Y%m%d).sql.gz
```

> Expect (and ignore) **"circular foreign-key constraints"** warnings on the third one, for `projects`, `accounts`, `stocklists`, `agreements` — this is exactly what `--disable-triggers` is for. FK constraints are implemented internally as triggers, and `--disable-triggers` disables those too (not just user-defined ones), so there's no valid load order needed for the cycle — checks are off during the load, and Postgres doesn't retroactively re-validate already-loaded rows once they're switched back on. Confirm nothing got left disabled after restoring, in case the run was interrupted partway:
> ```sql
> SELECT tgname, tgrelid::regclass FROM pg_trigger WHERE tgenabled = 'D';
> ```
> Empty result = good.

**2. Transfer and restore each in turn**, deleting the file on the new server once it's loaded to free space for the next one — `basedata` and `adhoc` first (small), `rest` last (the big one):

```powershell
pscp -P 2222 netplanner_data_basedata_20260708.sql.gz youruser@server_ip:/home/youruser/
```
```bash
gunzip -c netplanner_data_basedata_20260708.sql.gz | psql -h 127.0.0.1 -U postgres -d netplanner
rm netplanner_data_basedata_20260708.sql.gz
```

Repeat the same transfer → restore → delete pattern for `adhoc`, then `rest`.

- [ ] Go through and remove test/junk entries (dummy projects, accounts, wayleave records, test users) before handing off to the client

> **If a restore ever fails partway through** (disk space or otherwise), don't try to resume mid-file — `psql -f`/piped restores aren't wrapped in one transaction, so whatever already loaded is already committed, and re-running the same file risks duplicate-row/constraint errors. Reset to a clean slate instead, then retry from step 1:
> ```bash
> sudo -u postgres psql -c "DROP DATABASE netplanner;"
> sudo -u postgres psql -c "CREATE DATABASE netplanner;"
> sudo -u postgres psql -d netplanner -c "CREATE EXTENSION postgis; CREATE EXTENSION pgrouting;"
> psql -h 127.0.0.1 -U postgres -d netplanner -f netplanner_schema_20260705.sql
> ```

**For future client setups** — only steps 1 and 2 above are needed, for `basedata` and `adhoc`; skip the "everything else" export entirely, since new clients start empty in the client-specific schemas (`accounts`, `projects`, `wayleave`, `stocklists`, `users`, etc.) rather than inheriting dev's data.

> Update `www/fn/db.php` with this server's DB credentials. (It's correctly listed in `.gitignore` and has never actually been committed, despite containing real credentials on disk — confirmed via `git log --all --full-history`, no exposure here.)

#### Connection service file (`pg_service.conf`) — required for the QGIS PDF exports

The QGIS project files used by the PDF exports (`Project_PDF_Export.qgz`, `Opportunity_PDF_Export.qgz`) **do not contain database credentials**. Every PostGIS layer in them refers to a named connection instead:

```
service='geolynx' sslmode=disable key='id' srid=27700 type=MultiLineString table="projects"."network_cables_v2" (geom)
```

That name is resolved at connect time from an external file, which is what keeps the `.qgz` files portable between dev, this server, and any future client box — the project says *"connect to `geolynx`"*, and each machine decides what `geolynx` means. **Without this file the exports fail**, and (until the scripts are fixed) they fail unhelpfully: every layer silently comes back empty and the browser reports "Generated file not found" rather than a connection error.

Create the file (there's a template in the repo at `pg_service.conf.example`):

```bash
sudo mkdir -p /etc/geolynx
sudo nano /etc/geolynx/pg_service.conf
```
```ini
[geolynx]
host=localhost
port=5432
dbname=netplanner
user=postgres
password=THIS_SERVER'S_POSTGRES_PASSWORD
```

The stanza header must be exactly `[geolynx]`, on its own line, with no leading whitespace — the layers in the `.qgz` files reference that name and nothing else. Keep the name identical on every server; only the values underneath it change.

**Permissions.** The QGIS process is spawned by PHP and therefore runs as **`www-data`**, not as you — so `www-data` must be able to read both the file and the directory containing it. Created as root, it'll be `600 root:root` by default and QGIS will find nothing:

```bash
sudo chown root:www-data /etc/geolynx/pg_service.conf
sudo chmod 640 /etc/geolynx/pg_service.conf
sudo chmod 755 /etc/geolynx           # www-data needs to traverse the directory
```

**Point libpq at it.** `project_export_pdf.php` / `opportunity_export_pdf.php` already pass `PGSERVICEFILE=/etc/geolynx/pg_service.conf` in their `$env_vars` array, so the exports need no further setup. For your *own* shell (and desktop QGIS, if you install it here), either symlink it to the default location libpq checks — `ln -s /etc/geolynx/pg_service.conf ~/.pg_service.conf` — or set `PGSERVICEFILE` in `/etc/environment`.

Verify as the user that actually matters:

```bash
sudo -u www-data cat /etc/geolynx/pg_service.conf
sudo -u www-data env PGSERVICEFILE=/etc/geolynx/pg_service.conf \
  psql "service=geolynx" -c "select current_database()"
```

The `cat` is the permissions test; the `psql` is the resolution test. Both must pass.

> ⚠️ **The `env PGSERVICEFILE=...` prefix is not optional in that test.** `sudo -u www-data` starts a clean environment, so without it libpq never looks at `/etc/geolynx/` and you get `definition of service "geolynx" not found` — which looks exactly like a broken config but proves nothing. (It has to be `env VAR=...`, too: sudo strips bare `VAR=value` prefixes.)

> **Alternative worth considering:** libpq searches `PGSERVICEFILE`, then `~/.pg_service.conf`, then a system-wide `$PGSYSCONFDIR/pg_service.conf` — which on Ubuntu is `/etc/postgresql-common/pg_service.conf`. Putting the file *there* instead means every user and process on the box resolves `service=geolynx` with no environment setup at all: your shell, desktop QGIS, `www-data`, and psycopg2 alike. One less thing to get wrong per deployment, and it makes the `PGSERVICEFILE` lines in the PHP belt-and-braces rather than load-bearing.

#### Remote access for maintenance (SSH tunnel)

For connecting a DB client (pgAdmin, DBeaver, etc.) from your own machine for ongoing maintenance — going with an SSH tunnel rather than exposing port 5432 directly, since this is a single-client box and you already have hardened key-only SSH on it.

**No changes needed to `postgresql.conf`, `pg_hba.conf`, or UFW.** Postgres already only needs to accept connections on `127.0.0.1` (the default `listen_addresses`, and the `host ... 127.0.0.1/32 ... scram-sha-256` line already in `pg_hba.conf` from the default install) — an SSH tunnel makes your remote connection *appear* to originate from localhost on the server, so the existing default config is already exactly right. Port 5432 stays closed to the outside world entirely; the tunnel rides over the SSH port (2222) that's already open.

**Set up the tunnel in PuTTY** (reusing the saved session from 2.1):

1. Open the saved session, but don't connect yet — go to **Connection → SSH → Tunnels**.
2. Source port: `5433` (any free local port on your machine). Destination: `127.0.0.1:5432`. Leave **Local** selected.
3. Click **Add** — you should see `L5433  127.0.0.1:5432` appear in the list.
4. **Same gotcha as the private key setup:** scroll back to **Session** at the top of the tree and click **Save** again, or this tunnel config won't persist to the saved session.
5. Click **Open**. This opens a normal terminal *and* keeps the tunnel active in the background for as long as this PuTTY window stays open.

**In your DB client**, connect to `localhost` port `5433` (not the server's IP) — database `netplanner`, user `postgres`, same password as always. As far as the DB client is concerned it's talking to a local Postgres instance; PuTTY is silently forwarding it over SSH.

> If your DB client has a built-in SSH tunnel option instead (DBeaver and pgAdmin both do, under the connection's "SSH Tunnel" tab) and you'd rather skip the separate PuTTY window — use it the same way (host, port 2222, your username, private key). One gotcha: those typically want the original OpenSSH-format private key (`id_ed25519`), not the `.ppk` PuTTY-converted one.

**DataGrip specifically:** the SSH tunnel is configured *separately* from the actual Postgres connection, which trips people up — the two easily get mismatched:
- **General tab** (the Postgres data source itself): Host = `localhost`, Port = `5432`. Not the server's public IP, and not a manually-chosen local port — DataGrip handles the forwarding internally, so this should be the target exactly as Postgres sees itself (matching the same `127.0.0.1:5432` reasoning as above).
- **SSH/SSL tab**: enable "Use SSH tunnel", pointing at the server's public IP, port `2222`, and the OpenSSH-format private key (same gotcha as above — not `.ppk`). Can reference a saved SSH Configuration (Tools → SSH Configurations) instead of re-entering this each time.
- The easy mistake: leaving the server's public IP in the General tab instead of `localhost` — connection then either times out or bypasses the tunnel entirely.

### 3.4 Certbot (Let's Encrypt)

> No need for a `www.` variant (e.g. `www.myclient.geolynx.co.uk`) — that convention only matters for a root/apex domain someone might type either way. It doesn't apply to a per-client subdomain like this; nobody's typing "www." in front of it. Just the one domain:

```bash
sudo apt install certbot python3-certbot-apache -y
sudo certbot --apache -d myclient.geolynx.co.uk
```

Test auto-renewal:

```bash
sudo certbot renew --dry-run
```

### 3.5 QGIS (for PDF export)

`project_export_pdf.php` / `opportunity_export_pdf.php` shell out to a Python script using PyQGIS, so QGIS + its Python bindings need to be installed system-wide.

> ⚠️ QGIS's apt repo signing setup has changed a few times over the years — double-check current steps against https://qgis.org/resources/installation-guide/ before running this.

```bash
sudo apt install gnupg software-properties-common -y
sudo mkdir -m755 -p /etc/apt/keyrings
wget -qO - https://download.qgis.org/downloads/qgis-archive-keyring.gpg | sudo tee /etc/apt/keyrings/qgis-archive-keyring.gpg >/dev/null

sudo tee /etc/apt/sources.list.d/qgis.sources > /dev/null <<EOF
Types: deb deb-src
URIs: https://qgis.org/debian
Suites: $(lsb_release -cs)
Architectures: amd64
Components: main
Signed-By: /etc/apt/keyrings/qgis-archive-keyring.gpg
EOF

sudo apt update
sudo apt install qgis qgis-plugin-grass python3-qgis -y
```

Set up the Python virtualenv the export scripts run from (matches the `/opt/netplanner_env` path referenced in `opportunity_export_pdf.php`). `--system-site-packages` is required so the venv can see the `qgis.core` bindings installed above:

```bash
sudo apt install python3-venv -y
sudo python3 -m venv /opt/netplanner_env --system-site-packages
sudo /opt/netplanner_env/bin/pip install --upgrade pip
```

Don't guess which packages the export script needs, and don't trust a raw `pip freeze` on the dev venv either — it was created with `--system-site-packages`, so plain `pip freeze` dumps the *entire* system Python environment (apt/OS tooling like `apturl`, `ufw`, `unattended-upgrades`, printing/braille/VirtualBox packages, prototyping-era libraries, none of it relevant), and some entries aren't even real PyPI packages, so `pip install -r` on that raw output fails outright. `pip freeze --local` narrows it to just what's actually `pip install`ed in the venv, but the reliable source of truth is simpler still: **check what the export scripts actually import.**

For this project, both `.py` export scripts import:
```python
import sys, os                    # stdlib — nothing to install
from qgis.core import (...)       # apt (python3-qgis, 3.5 above) — not pip
from qgis.PyQt.QtCore import ...  # apt, bundled with QGIS — not pip
from qgis.PyQt.QtGui import ...   # apt, bundled with QGIS — not pip
import db_config as config        # a local file (db_config.py), not a package — see below
import psycopg2                   # the one real pip dependency
```

So the venv genuinely only needs one package:

```bash
sudo /opt/netplanner_env/bin/pip install psycopg2-binary
```

(`psycopg2-binary` rather than `psycopg2` — precompiled, no need for build tools/`libpq-dev` on the server just to compile it.)

Copy the actual export script(s) over from the dev server (currently at `/home/davebasnett/Python/netplanner/` there) to the same path on the new box, or update the `$pythonScript` path in `project_export_pdf.php` / `opportunity_export_pdf.php` to match wherever you put them.

> ⚠️ Don't forget **`db_config.py`** alongside the main script — it's a plain local file the script imports (`import db_config as config`), not something pip installs, so it's easy to copy the main script and miss this sibling file. It'll also need updating with this server's DB credentials, the same as `www/fn/db.php`.
>
> This is a *third* copy of the credentials, and only covers the script's own psycopg2 connection (used to look up the project/opportunity name). The **QGIS layers inside the `.qgz` files connect via the `pg_service.conf` service file from 3.3 instead** — so both have to be right, or the export fails. Since psycopg2 is libpq underneath, `psycopg2.connect(service="geolynx")` would let `db_config.py` be deleted outright and put the whole export path on one credential source; worth doing when the scripts are next touched.

> ⚠️ The `.qgz` files also have to come across — they're referenced by an **absolute path** hardcoded inside each script (`project_path = "/home/davebasnett/Python/netplanner/Project_PDF_Export.qgz"`). Either reproduce that exact path on this server or edit the constant. If it's wrong, the script prints `Failed to load project`, exits **0** anyway, and the browser shows "Generated file not found" — see the warning in 4.7.

### 3.6 Virtual display for headless QGIS rendering (Xvfb)

This is the piece you couldn't remember the name of: **Xvfb** (X Virtual FrameBuffer). QGIS still expects an X display to render to, even when running from a script with no monitor attached. The PHP code hardcodes `DISPLAY=:0`, so rather than `xvfb-run` (which picks a random display number per-run), set up a persistent virtual display bound to `:0` as a service.

```bash
sudo apt install xvfb x11-utils -y
```

Create `/etc/systemd/system/xvfb.service`:

```ini
[Unit]
Description=Xvfb virtual display for QGIS headless rendering
After=network.target

[Service]
ExecStart=/usr/bin/Xvfb :0 -screen 0 1920x1080x24 -nolisten tcp
Restart=always

[Install]
WantedBy=multi-user.target
```

```bash
sudo systemctl daemon-reload
sudo systemctl enable --now xvfb.service
sudo systemctl status xvfb.service
```

Verify it's up:

```bash
DISPLAY=:0 xdpyinfo | head
```

The PHP code also expects a handful of writable scratch directories for the `www-data` user (`$env_vars` in `project_export_pdf.php`). Make these persistent across reboots with a tmpfiles.d config, since `/tmp` is otherwise cleared on boot:

Create `/etc/tmpfiles.d/geolynx-qgis.conf`:

```
d /tmp/runtime-www-data     0700 www-data www-data -
d /tmp/www-data-config      0700 www-data www-data -
d /tmp/www-data-data        0700 www-data www-data -
d /tmp/www-data-cache       0700 www-data www-data -
d /tmp/www-data-home        0700 www-data www-data -
d /tmp/www-data-qgis-auth   0700 www-data www-data -
```

```bash
sudo systemd-tmpfiles --create /etc/tmpfiles.d/geolynx-qgis.conf
```

Smoke-test as `www-data` (the user Apache actually runs as):

```bash
sudo -u www-data DISPLAY=:0 /opt/netplanner_env/bin/python3 -c "from qgis.core import QgsApplication; print('QGIS OK')"
```

> ⚠️ **If this fails with `ModuleNotFoundError: No module named 'qgis'`**, even though `python3-qgis` is installed — this QGIS/Ubuntu pairing (QGIS 4.2 on 26.04 "resolute") installs the Python bindings to `/usr/share/qgis/python`, not any standard `dist-packages` location, so nothing on the default Python path can find them. Confirm with:
> ```bash
> dpkg -L python3-qgis | grep qgis/core/__init__.py   # shows the real install path
> python3 -c "import sys; print('\n'.join(sys.path))"  # confirm it's absent from here
> ```
> Fix it with a `.pth` file (the standard mechanism Python reads on startup to extend `sys.path`). Placing it in `/usr/lib/python3/dist-packages/` fixes both plain system Python *and* the venv in one go, since the venv inherits system site-packages via `--system-site-packages`:
> ```bash
> echo "/usr/share/qgis/python" | sudo tee /usr/lib/python3/dist-packages/qgis.pth
> ```
> Re-run the smoke test above (and `python3 -c "from qgis.core import QgsApplication"` on its own, to confirm the system-level import works too).

If that prints `QGIS OK`, the PDF export path should work end-to-end.

### 3.7 GeoServer (Docker)

GeoServer serves the WMS/WFS layers the app consumes. On dev it lives on its own subdomain (`geoserver.geolynx.co.uk/geoserver`); here we're putting it on the **same** domain as the app, reached at `myclient.geolynx.co.uk/geoserver`, by reverse-proxying through the Apache instance already set up in 3.1. Two nice side effects of same-domain: the existing TLS cert from Certbot (3.4) already covers it (no extra subdomain or cert needed), and the app→GeoServer calls become same-origin, sidestepping the cross-origin/CORS handling the split-subdomain dev setup needs.

**Why Docker for this one service** (and not the rest of the stack): GeoServer is the piece where containerisation buys the most and costs the least — version upgrades become "change the image tag, restart, roll back by reverting the tag" rather than swapping out an `/opt` install, and extensions install against the exact matching GeoServer version automatically (native, version-mismatched extension zips are the classic footgun). On a Linux host the performance cost is negligible (containers are host processes via namespaces/cgroups, not a VM). PHP/PostgreSQL/QGIS stay native — simpler, and no benefit to containerising them here. The data directory is trivially recreatable from the app database anyway, so persistence isn't a concern — the bind mount below is for convenience, not safety.

> The official image internally runs GeoServer on Tomcat, but that's bundled and maintained inside the image — so the GS3 Jakarta EE / Tomcat-10 migration is handled for you, not something you deal with.

#### Install Docker

`docker.io` is the engine; the Compose v2 plugin (`docker compose`, used below) is packaged separately on Ubuntu:

```bash
sudo apt install docker.io docker-compose-v2 -y
sudo systemctl enable --now docker
```

#### Run GeoServer (via Docker Compose)

Rather than a long `docker run` one-liner, define the whole setup in a **`docker-compose.yml`** — it's readable, version-controllable, makes upgrades a one-line tag change, and turns adding/removing a plugin into a one-line list edit (and it's the natural artifact to template with Ansible later). We'll use the official image, `docker.osgeo.org/geoserver`. Find the current stable version number at https://geoserver.org/download/ (it lists only GA releases, so you won't grab a release candidate by mistake — a 3.0-RC circulated in April 2026). That version number *is* the tag: e.g. `docker.osgeo.org/geoserver:2.28.2`. Use it in place of `<VERSION>` below. (To see every tag the registry holds: `curl -s https://docker.osgeo.org/v2/geoserver/tags/list` — plain tags plus `-gdal` variants that add raster/GDAL support; use the **plain** tag for this PostGIS-vector setup.)

Create the data dir on the host and a place for the compose file:

```bash
sudo mkdir -p /opt/geoserver_data /opt/geoserver
```

Create `/opt/geoserver/docker-compose.yml`:

```yaml
services:
  geoserver:
    image: docker.osgeo.org/geoserver:3.0.0
    container_name: geoserver
    restart: unless-stopped
    ports:
      - "127.0.0.1:8080:8080"          # loopback-only — see note below
    mem_limit: 4g
    environment:
      - JAVA_OPTS=-Xms512m -Xmx2g
      # --- plugins: BOTH lines change together to add extensions ---
      # INSTALL_EXTENSIONS is the on/off switch (must be true), STABLE_EXTENSIONS is the comma-separated list.
      # Default (no plugins): INSTALL_EXTENSIONS=false and an empty list.
      # To add plugins: set INSTALL_EXTENSIONS=true AND list them, e.g. STABLE_EXTENSIONS=ysld,ogcapi-features
      - INSTALL_EXTENSIONS=false
      - STABLE_EXTENSIONS=
      # --- reverse-proxy CSRF whitelist: REQUIRED, or the public /geoserver login silently fails ---
      # must be the actual client subdomain this box is served on (matches ServerName / Proxy Base URL)
      - GEOSERVER_CSRF_WHITELIST=myclient.geolynx.co.uk
    volumes:
      - /opt/geoserver_data:/opt/geoserver_data
```

- **`127.0.0.1:8080:8080`** — the security-critical line. Publishes the port to **loopback only**, so only Apache on this host can reach GeoServer; never exposed to the public internet. (Same role the native `JETTY_HOST=127.0.0.1` played.) A bare `"8080:8080"` binds `0.0.0.0` and exposes it — don't.
- **`mem_limit` / `JAVA_OPTS` heap** — RAM is what GeoServer needs bounding, not disk. There's no VM-style disk to pre-size — the container's writable layer grows on the host filesystem on demand; the data dir lives on the host via the volume mount. Tune the heap to the box.
- **Plugins/extensions** — this is where they live. Set `INSTALL_EXTENSIONS=true` and list them in `STABLE_EXTENSIONS` (comma-separated, e.g. `ysld,ogcapi-features`); the image downloads the versions matching this GeoServer build at startup. To add one later: edit the list, `docker compose up -d`. For a fully pinned multi-client setup down the line, baking extensions into a small custom image (`FROM docker.osgeo.org/geoserver:<VERSION>`) is more reproducible than startup-time downloads — but the env-var list is fine to start.
- **`GEOSERVER_CSRF_WHITELIST`** — don't skip this. GeoServer 3 has CSRF protection that **silently drops the login POST** when the request comes through a reverse proxy on a different host — the symptom is clicking Login and getting *nothing*: no error, no redirect. Whitelisting the public subdomain here is what lets the proxied login work. (After editing this, recreate the container with `docker compose up -d` — a `restart` alone won't pick up env changes. Verify with `sudo docker exec geoserver env | grep -i csrf`.)

Bring it up (run from `/opt/geoserver`, where the compose file lives — no separate `docker pull` needed, `up` fetches the image if absent):

```bash
cd /opt/geoserver
sudo docker compose up -d
```

> Do **not** add a UFW rule for 8080 — the loopback port binding already keeps it host-only; all external access goes through Apache on 443.

Give it a minute to start (GeoServer is slow to boot), then confirm it's up locally on the host:

```bash
curl -I http://127.0.0.1:8080/geoserver/web/
sudo docker compose logs   # if the curl fails, check here
```

> Upgrading later: edit the image tag in `docker-compose.yml`, then `sudo docker compose up -d` (recreates the container on the new image). Roll back by reverting the tag and running it again.

#### Reverse proxy `/geoserver` through Apache

Enable the proxy modules **and `mod_headers`** (needed for the forwarded-proto header below):

```bash
sudo a2enmod proxy proxy_http headers
```

Add the directives inside the **`*:443` VirtualHost** block. Because Certbot (3.4) split the config into an SSL vhost, that block lives in the `-le-ssl.conf` file it generated — e.g. `/etc/apache2/sites-available/geolynx-le-ssl.conf`, **not** the plain `geolynx.conf` (which now just holds the `:80`→`:443` redirect). Confirm which file with `grep -l "VirtualHost \*:443" /etc/apache2/sites-available/*.conf`. Place these between the `<VirtualHost *:443>` and `</VirtualHost>` tags:

```apache
    # --- GeoServer reverse proxy ---
    ProxyPreserveHost On
    RequestHeader set X-Forwarded-Proto "https"
    ProxyPass        /geoserver http://127.0.0.1:8080/geoserver
    ProxyPassReverse /geoserver http://127.0.0.1:8080/geoserver
```

```bash
sudo apache2ctl configtest && sudo systemctl reload apache2
```

- The context path stays `/geoserver` on both sides, so there's no path rewriting to get wrong.
- `RequestHeader set X-Forwarded-Proto "https"` tells GeoServer the original request was HTTPS — the proxy talks to it over plain HTTP (`127.0.0.1:8080`), so without this GeoServer thinks the connection was insecure and generates/redirects to the wrong protocol.

#### First login and Proxy Base URL (bootstrapping — read this order carefully)

There's a chicken-and-egg here: **the proxied login won't work until the Proxy Base URL is set, but setting it requires logging in.** Without it, GeoServer builds its login form-action/redirect URLs from what it sees internally (`localhost:8080`) rather than the public address, and the browser silently refuses the mismatched POST — same dead-click symptom as the CSRF issue. Break the loop by doing the first login **directly, bypassing the proxy, over an SSH tunnel:**

1. Set up an SSH local port-forward (same mechanism as the DB tunnel in 3.3) — source port e.g. `8088`, destination `127.0.0.1:8080`. In PuTTY: **Connection → SSH → Tunnels**, add `8088` → `127.0.0.1:8080`, Open. Keep the window open.
2. Browse to `http://localhost:8088/geoserver/web/` — this is same-origin (`localhost`), so no proxy, CSRF, or CSP host-mismatch to trip over. Log in with the default `admin` / `geoserver`.
3. **Change the passwords immediately** (GeoServer is about to be internet-reachable):
   - Admin user password: **Security → Users, Groups, Roles → Users/Groups → admin**.
   - Master/root keystore password: **Security → Passwords** (also ships with a well-known default).
4. Set the **Proxy Base URL** — in GeoServer 3 this is at **top menu → Server → Global Settings → Proxy Base URL**:
   ```
   https://myclient.geolynx.co.uk/geoserver
   ```
   Submit/Save.
5. Confirm it persisted (data dir is bind-mounted to the host):
   ```bash
   grep -i proxyBaseUrl /opt/geoserver_data/global.xml
   ```
   Should show `<proxyBaseUrl>https://myclient.geolynx.co.uk/geoserver</proxyBaseUrl>`.

Now test the real thing: `https://myclient.geolynx.co.uk/geoserver/web/` should log in normally (no container restart needed — the Proxy Base URL is read live). If it still dead-clicks, re-check the three prerequisites: CSRF whitelist actually in the running container (`docker exec geoserver env | grep -i csrf`), `X-Forwarded-Proto` header present in the vhost, and the Proxy Base URL grep above — in practice a blank Proxy Base URL is the usual culprit.

#### Reminder

- [ ] Set up the layers in GeoServer (workspaces, stores, layers) — you know this part; just don't forget it, nothing serves until it's done.

---

## Step 4: Deploy the app

The app lives in a **private** GitLab repo (`https://gitlab.com/geolynx-apps/geolynx-app.git`), so the server needs authenticated read access to clone it. We'll do everything here with `sudo` (as root), because the target `/var/www` is root-owned — keeping the git operations as root avoids file-ownership tangles later.

### 4.1 Install git

```bash
sudo apt install git -y
```

### 4.2 Give the server read access to the repo (SSH deploy key)

A **deploy key** is an SSH key tied to a single repo, read-only — the safe way to let a server pull code without exposing your GitLab account. It's the same kind of key pair as your server login (2.1), just used the other direction: the server holds the private half, GitLab holds the public half.

Generate a key pair for root (the `-N ""` gives it no passphrase, so pulls never hang waiting for input):

```bash
sudo ssh-keygen -t ed25519 -f /root/.ssh/gitlab_deploy -N "" -C "geolynx-deploy-myclient"
```

Print the **public** key and copy it:

```bash
sudo cat /root/.ssh/gitlab_deploy.pub
```

In GitLab (browser): open the **geolynx-app** project → **Settings → Repository → Deploy keys → Expand → Add new key**. Paste the public key, give it a title (e.g. "myclient VPS"), leave **"Grant write permissions"** unticked (read-only is all a server needs), and add it.

Tell the server's SSH to use that key for gitlab.com — create `/root/.ssh/config`:

```bash
sudo nano /root/.ssh/config
```
```
Host gitlab.com
    HostName gitlab.com
    User git
    IdentityFile /root/.ssh/gitlab_deploy
    IdentitiesOnly yes
```

Test the connection:

```bash
sudo ssh -T git@gitlab.com
```

First time it asks to trust gitlab.com's fingerprint — type `yes`. Success looks like `Welcome to GitLab, @...` or a message confirming the deploy key authenticated. (If it says "permission denied", the public key didn't get added correctly — recheck the Deploy keys page.)

### 4.3 Clone the repo

The placeholder doc root from 3.1 is in the way — `git clone` needs the target to not already exist (or be empty), so remove it first, then clone. Note the URL is the **SSH** form (`git@gitlab.com:...`), not the HTTPS one, so it uses the deploy key:

```bash
sudo rm -rf /var/www/geolynx-app
sudo git clone git@gitlab.com:geolynx-apps/geolynx-app.git /var/www/geolynx-app
```

This clones the default branch (**main** — the production branch; `dev` is for development). The repo's `www/` subfolder becomes `/var/www/geolynx-app/www`, which is exactly what the Apache `DocumentRoot` points at.

### 4.4 Create `db.php` (it is NOT in the repo)

`www/fn/db.php` is gitignored — it holds DB credentials, so it's deliberately kept out of git. **After cloning it won't exist**, and the app can't connect until you create it. Easiest is to copy the one from the dev server (via WinSCP/`pscp`) into `/var/www/geolynx-app/www/fn/db.php`, then edit it to match *this* server's Postgres password (the one you set in 3.3). Or create it by hand:

```bash
sudo nano /var/www/geolynx-app/www/fn/db.php
```
```php
<?php
error_reporting(E_ALL);
ini_set('display_errors', '0');   // 0 on a client-facing box — don't leak errors to users
ob_start();
if (!isset($_SESSION)) { session_start(); }

$hostname = "localhost";
$username = "postgres";
$password = "THIS_SERVER'S_POSTGRES_PASSWORD";
$dbname   = "netplanner";

$pdo = new PDO("pgsql:host=$hostname;port=5432;dbname=$dbname;user=$username;password=$password")
    or die("Database connection not established.");
?>
```

### 4.5 Set ownership

Hand the whole tree to the user Apache runs as, so it can read the code:

```bash
sudo chown -R www-data:www-data /var/www/geolynx-app
```

### 4.6 File uploads: directory + PHP limits

**a) Create the upload directory.** All three upload endpoints (`file_upload.php`, `file_upload_v2.php`, `image_upload.php`) write to a fixed base path — **`/var/www/netplanner-files/`** — and create `{entity}/{id}/` subfolders themselves at runtime (`mkdir(..., 0755, true)`) for each of `project`, `stocklist`, `account`, `opportunity`, `wayleave`. So `www-data` must **own** the base directory, not merely be able to read it — otherwise uploads fail with "Failed to create upload directory".

```bash
sudo mkdir -p /var/www/netplanner-files
sudo chown -R www-data:www-data /var/www/netplanner-files
sudo chmod 755 /var/www/netplanner-files
```

> ⚠️ Note this lives **outside the web root** (`/var/www/netplanner-files`, *not* under the `/var/www/geolynx-app/www` DocumentRoot). That's deliberate — uploaded files are served back through `serve_file.php` / `serve_image.php`, which check the user is logged in first. Never move it into the web root, or every uploaded file becomes publicly fetchable by URL. A useful side effect: it's untouched by `git pull` and by the `chown` in 4.5, so uploads survive app updates.

**b) Raise PHP's upload limits.** The app permits uploads up to **50MB**, but stock PHP caps `upload_max_filesize` at 2M and `post_max_size` at 8M — and nothing in the repo overrides this. Without this change, anything over 2MB fails (often confusingly, with an empty `$_FILES`). Edit the Apache PHP config (swap `8.x` for your version — check with `php -v`):

```bash
php -v            # note the version, e.g. 8.5
ls /etc/php/      # confirm the version directory
sudo nano /etc/php/8.5/apache2/php.ini      # <-- the apache2 one, NOT cli
```
```ini
upload_max_filesize = 50M
post_max_size = 52M      ; must exceed upload_max_filesize (leaves room for other form fields)
memory_limit = 256M
max_execution_time = 300 ; large uploads/PDF exports need longer than the 30s default
```
```bash
sudo systemctl restart apache2     # a reload isn't always enough for ini changes — restart
```

> ⚠️ **PHP has a separate `php.ini` per SAPI**, and this trips everyone up: `/etc/php/8.5/**cli**/php.ini` is what the `php` command uses, `/etc/php/8.5/**apache2**/php.ini` is what your website uses. Edit the **apache2** one.
>
> For the same reason, **do not verify with `php -i`** — that reports the *CLI* config and will keep showing the old 2M/8M, making it look like your change didn't take. Check the file you edited, then confirm what Apache's PHP actually loaded via a temporary phpinfo page fetched over localhost (never left exposed publicly):
> ```bash
> grep -E "^upload_max_filesize|^post_max_size" /etc/php/8.5/apache2/php.ini
>
> echo '<?php phpinfo();' | sudo tee /var/www/geolynx-app/www/_phpinfo.php > /dev/null
> curl -sk https://127.0.0.1/_phpinfo.php -H "Host: myclient.geolynx.co.uk" | grep -oE "upload_max_filesize</td><td[^>]*>[^<]*"
> sudo rm /var/www/geolynx-app/www/_phpinfo.php
> ```

**c) Migrating existing files from dev.** The data restore in 3.3 copies the *database rows* that reference uploaded files (e.g. `projects.project_files.file_path`), but **not the files themselves** — those live on the dev server's filesystem. If the client beta-tested on dev, copy the actual files across too, or every existing attachment/image link will 404:

```bash
# from the dev server
rsync -avz -e "ssh -p 2222" /var/www/netplanner-files/ youruser@server_ip:/tmp/netplanner-files/
```
```bash
# on the new server — move into place and fix ownership
sudo rsync -a /tmp/netplanner-files/ /var/www/netplanner-files/
sudo chown -R www-data:www-data /var/www/netplanner-files
```

### 4.7 Copy the QGIS export scripts

These live outside the repo (on dev, `/home/davebasnett/Python/netplanner/`). Copy them — **including `db_config.py` and the two `.qgz` project files** (per 3.5) — to the path referenced in `project_export_pdf.php` / `opportunity_export_pdf.php`, and point `db_config.py` at this server's DB.

Confirm the service file from 3.3 is in place before testing, since the `.qgz` layers depend on it:

```bash
sudo -u www-data env PGSERVICEFILE=/etc/geolynx/pg_service.conf \
  psql "service=geolynx" -c "select current_database()"
```

> ⚠️ **"Generated file not found" means almost nothing — debug it by hand.** Both scripts end with an unconditional `print(f"PDF created: {export}")` and never call `sys.exit(1)`, so *any* internal failure (missing `.qgz`, unresolved `service=geolynx`, unwritable output dir, missing layout) still exits 0 having printed `PDF created: None`. The PHP sees a zero return code, skips its error branch, and reports "Generated file not found" — discarding the real reason, which the script did print. Run it directly as `www-data` with the same environment to see the actual error:
>
> ```bash
> sudo -u www-data env \
>   DISPLAY=:0 XDG_RUNTIME_DIR=/tmp/runtime-www-data \
>   XDG_CONFIG_HOME=/tmp/www-data-config XDG_DATA_HOME=/tmp/www-data-data \
>   XDG_CACHE_HOME=/tmp/www-data-cache HOME=/tmp/www-data-home \
>   QGIS_AUTH_DB_DIR_PATH=/tmp/www-data-qgis-auth \
>   PGSERVICEFILE=/etc/geolynx/pg_service.conf \
>   /opt/netplanner_env/bin/python3 /path/to/project_export_pdf_v2.py <project_id> 101
> ```
>
> A successful run prints `Applied filter to '<layer>'` for each of the six layers, then `PDF successfully exported to: ...`. If the filters apply but no features are found, the DB connected fine and it's a data problem; if the layers come back empty across the board, it's the service file.

Also ensure the output directory exists and `www-data` can write to it — the scripts write to `/var/lib/netplannerexports` (hardcoded), and bail out early if it isn't writable:

```bash
sudo mkdir -p /var/lib/netplannerexports
sudo chown www-data:www-data /var/lib/netplannerexports
```

### 4.8 Test end-to-end

- [ ] Load `https://myclient.geolynx.co.uk/` and log in
- [ ] Open the map — confirm layers render (this exercises GeoServer + the DB)
- [ ] Run a project PDF export (exercises QGIS + Xvfb + the export scripts)
- [ ] Upload a file to a project, and confirm an existing attachment still opens (exercises 4.6 — the directory permissions, the PHP limits, and the migrated files)

> **Updating the app later:** because the tree is owned by `www-data`, run pulls as root and tell git the directory is trusted (one-time): `sudo git config --global --add safe.directory /var/www/geolynx-app`, then `sudo git -C /var/www/geolynx-app pull`, and re-run the `chown` from 4.5 afterward to catch any new files.