# deploy/ — Operator Guide: stats.wise-relations.com

Self-hosted GoatCounter SaaS ("wise-relations Webanalyse") on the Hetzner CX22 VPS that already hosts lobout.com.

---

## Architecture

```
Internet
  └── Caddy (Docker, lobout.com docker-compose) — TLS termination
        ├── stats.wise-relations.com       → host.docker.internal:8100 (GoatCounter)
        ├── static.stats.wise-relations.com → host.docker.internal:8100
        └── *.stats.wise-relations.com     → host.docker.internal:8100 (on-demand TLS)

VPS host (systemd)
  ├── stats.service       — GoatCounter saas, 127.0.0.1:8100
  └── stats-ask.service   — Caddy on-demand TLS ask gate, 127.0.0.1:8101
```

Caddy handles TLS. GoatCounter is told `-tls http` so it never touches ACME itself.

Tenant subdomains (e.g. `acme.stats.wise-relations.com`) get individual DV certs from Let's Encrypt via HTTP-01 on-demand TLS. Before issuing each cert, Caddy calls `http://host.docker.internal:8101/ask?domain=<hostname>`. The ask server queries GoatCounter's SQLite DB and returns 200 (known) or 404 (unknown).

---

## Prerequisites

### 1. DNS records

Add these in your DNS provider (Hetzner DNS or wherever wise-relations.com is managed):

| Name | Type | Value |
|---|---|---|
| `stats.wise-relations.com` | A | `<vps-ip>` |
| `static.stats.wise-relations.com` | A | `<vps-ip>` |
| `*.stats.wise-relations.com` | A | `<vps-ip>` |

The wildcard A record routes all tenant subdomain traffic to the VPS. Each subdomain gets its own cert via on-demand TLS (no wildcard TLS cert needed).

### 2. Gmail App Password

GoatCounter sends transactional email (signup confirmation, reports) via Gmail.

1. Log into the Google account that owns `info@wise-relations.com`
2. Go to https://myaccount.google.com/apppasswords
3. Select app: **Mail**, device: **Other** — name it `stats.wise-relations.com`
4. Copy the 16-character App Password (no spaces)
5. You'll use it in Step 3 below

---

## Installation

Run on the VPS as a user with `sudo` access.

### Step 1: Get the binary onto the VPS

Option A — build on VPS (requires Go 1.22+, installs it via apt if missing):
```bash
cd /path/to/stats.wise-relations.com
sudo bash deploy/install.sh --build-on-vps --vhost admin.stats.wise-relations.com --email info@wise-relations.com
```

Option B — cross-compile on dev machine, copy to VPS, then install:
```bash
# On dev machine (Linux target):
CGO_ENABLED=1 GOOS=linux GOARCH=amd64 go build -o goatcounter-linux-amd64 ./cmd/goatcounter
scp goatcounter-linux-amd64 deploy@<vps-ip>:/tmp/goatcounter

# On VPS:
sudo bash deploy/install.sh --use-prebuilt /tmp/goatcounter --vhost admin.stats.wise-relations.com --email info@wise-relations.com
```

The install script:
- Creates the `stats` system user (no shell, no home)
- Creates `/opt/stats/{,data,backups}`
- Installs the binary, ask-server, backup script, and systemd units
- Runs database migrations
- Creates the first admin site
- Sets up the hourly backup cron job
- Starts and enables both services
- Runs a health check

### Step 2: Set the real SMTP password

```bash
sudo nano /opt/stats/env
# Replace GENERATE_APP_PASSWORD with the App Password from Prerequisites step 2
sudo systemctl restart stats
```

Verify email works:
```bash
journalctl -u stats -n 30 | grep -i smtp
```

### Step 3: Add the Caddyfile snippet to lobout.com

On the VPS, in the lobout.com working directory:

```bash
# Check the current Caddyfile:
cat Caddyfile

# Add the snippet (append to end of Caddyfile):
cat /path/to/stats.wise-relations.com/deploy/Caddyfile.snippet >> Caddyfile

# Review the result — make sure braces are balanced:
cat Caddyfile
```

If there is already a `{ }` global options block in the Caddyfile, you must merge the `on_demand_tls` block into the existing global block manually (two separate `{ }` blocks at the top level cause a Caddy parse error).

Reload Caddy:
```bash
docker compose -f docker-compose.prod.yml exec caddy caddy reload --config /etc/caddy/Caddyfile
```

Check Caddy logs for errors:
```bash
docker compose -f docker-compose.prod.yml logs caddy --tail=50
```

---

## Verifying the Install

### Health checks

```bash
# GoatCounter is up and responding:
curl -s http://127.0.0.1:8100/status | python3 -m json.tool

# Ask server is running:
curl -s "http://127.0.0.1:8101/ask?domain=stats.wise-relations.com"
# → HTTP 200

# TLS works (after DNS propagates and Caddy has issued a cert):
curl -I https://stats.wise-relations.com/status
```

### Signup test

1. Open `https://stats.wise-relations.com` in a browser
2. Create an account
3. GoatCounter sends a confirmation email — check it arrives
4. Log in and verify the dashboard loads

---

## Backup and Restore

### Backup

Backups run automatically every hour via cron (`/etc/cron.d/stats-backup`).

Manual backup:
```bash
sudo bash /opt/stats/backup.sh
ls -lh /opt/stats/backups/
```

Last backup status:
```bash
cat /opt/stats/backups/last_backup.json
```

### Restore

```bash
# Stop GoatCounter first:
sudo systemctl stop stats

# Replace the database:
sudo -u stats sqlite3 /opt/stats/data/db.sqlite ".restore '/opt/stats/backups/db-YYYYMMDD-HHMM.sqlite'"

# Restart:
sudo systemctl start stats
```

---

## Updating GoatCounter

```bash
# On VPS, in the stats.wise-relations.com repo:
git pull

# If --build-on-vps was used:
sudo bash deploy/install.sh --build-on-vps --skip-migrate --skip-create-site

# Or build locally and copy:
scp goatcounter-linux-amd64 deploy@<vps-ip>:/tmp/goatcounter
sudo bash deploy/install.sh --use-prebuilt /tmp/goatcounter --skip-migrate --skip-create-site

# The install script will restart the service automatically.
# If it doesn't, restart manually:
sudo systemctl restart stats
```

Run migrations after a version bump:
```bash
sudo -u stats /opt/stats/goatcounter db migrate -db 'sqlite+/opt/stats/data/db.sqlite?_journal_mode=WAL' all
sudo systemctl restart stats
```

---

## Logs

```bash
# GoatCounter service logs:
journalctl -u stats -f
journalctl -u stats -n 100 --no-pager

# Ask server logs:
journalctl -u stats-ask -f

# Caddy logs (from Docker):
docker compose -f docker-compose.prod.yml logs caddy -f

# Backup log:
tail -f /var/log/stats-backup.log
```

---

## File Inventory

| File | Purpose |
|---|---|
| `Caddyfile.snippet` | Append to lobout.com Caddyfile to proxy stats.wise-relations.com |
| `stats.service` | systemd unit for GoatCounter (listens on 127.0.0.1:8100) |
| `stats-ask.service` | systemd unit for on-demand TLS ask server (127.0.0.1:8101) |
| `ask-server.py` | Python HTTP server — validates tenant domains for Caddy |
| `env.example` | Environment variable template (copy to /opt/stats/env, fill in SMTP_URL) |
| `install.sh` | One-shot installer: creates user, dirs, installs binary, starts services |
| `backup.sh` | Hourly SQLite backup with S3 upload and retention policy |
| `README.md` | This file |

---

## Troubleshooting

**GoatCounter won't start — SMTP error:**
Check the SMTP URL in `/opt/stats/env`. The `@` in the email address must be URL-encoded as `%40`. Example:
```
SMTP_URL=smtps://info%40wise-relations.com:myapppassword@smtp.gmail.com:465
```

**Caddy can't reach GoatCounter:**
Caddy is in Docker and reaches the VPS host via `host.docker.internal`. Verify this resolves:
```bash
docker compose -f docker-compose.prod.yml exec caddy nslookup host.docker.internal
```
If it doesn't resolve, add to the Caddy service in docker-compose.prod.yml:
```yaml
extra_hosts:
  - "host.docker.internal:host-gateway"
```

**On-demand TLS cert not issued:**
1. Verify DNS: `dig A customer.stats.wise-relations.com` → `<vps-ip>`
2. Check ask server: `curl -v "http://127.0.0.1:8101/ask?domain=customer.stats.wise-relations.com"`
3. Check Caddy logs: `docker compose -f docker-compose.prod.yml logs caddy --tail=100`
4. Verify GoatCounter has the site: check the admin panel or query the DB:
   ```bash
   sqlite3 /opt/stats/data/db.sqlite "select cname, state from sites"
   ```

**Database locked:**
GoatCounter runs with WAL mode (`?_journal_mode=WAL`). The backup script uses `sqlite3 .backup` which is WAL-safe. If you see "database is locked" errors, check for multiple GoatCounter processes:
```bash
ps aux | grep goatcounter
```
