# L4D2 Player Stats Setup Guide

A short guide for adding persistent player stats and a web UI to a Debian-based L4D2 dedicated server. Records campaign completions, kills, FF, skill plays, and more — viewable on a public website.

**Components:**
- **Plugin:** Jackz's L4D2 Stats Recorder (SourceMod plugin compiled from source)
- **Database:** MariaDB (or MySQL)
- **Web UI:** Jackz's Astro stats website
- **Reverse proxy:** nginx with Let's Encrypt HTTPS

**Assumptions:**
- Debian 12+ or Ubuntu 22.04+ server
- L4D2 dedicated server installed (LinuxGSM or similar)
- SourceMod 1.11+ and MetaMod:Source installed
- A domain name with an A record pointing at your VPS
- Non-root user with sudo (referred to as `$USER` below)

---

## 1. Install MariaDB

```bash
sudo apt update
sudo apt install -y mariadb-server
sudo systemctl enable --now mariadb
sudo mysql_secure_installation     # answer "Y" to all
```

Lock root to unix_socket auth (no password attack surface):

```bash
sudo mysql
```

```sql
ALTER USER 'root'@'localhost' IDENTIFIED VIA unix_socket;
FLUSH PRIVILEGES;
EXIT;
```

---

## 2. Create database and user

```bash
sudo mysql
```

```sql
CREATE DATABASE stats CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'l4d2stats'@'localhost' IDENTIFIED BY 'STRONG_PASSWORD_HERE';
GRANT ALL PRIVILEGES ON stats.* TO 'l4d2stats'@'localhost';
FLUSH PRIVILEGES;
EXIT;
```

Replace `STRONG_PASSWORD_HERE` with `openssl rand -base64 24`.

---

## 3. Install MariaDB i386 client library

L4D2 dedicated server is 32-bit (i386). SourceMod's MySQL extension needs the matching 32-bit client lib.

```bash
sudo dpkg --add-architecture i386
sudo apt update
sudo apt install -y libmariadb3:i386

# Symlink so SourceMod can find it
sudo ln -sf /usr/lib/i386-linux-gnu/libmariadb.so.3 /usr/lib/i386-linux-gnu/libmysqlclient.so.18
```

Verify SourceMod sees it:

```bash
./l4d2server send "sm exts list"
sleep 2
tail -20 ~/log/console/l4d2server-console.log | grep -i mysql
```

Should show `dbi.mysql.ext.so` loaded without errors.

---

## 4. Configure SourceMod's database connection

Edit `~/serverfiles/left4dead2/addons/sourcemod/configs/databases.cfg` and add:

```
"Databases"
{
    "default"
    {
        "driver"            "mysql"
        "host"              "127.0.0.1"
        "database"          "stats"
        "user"              "l4d2stats"
        "pass"              "STRONG_PASSWORD_HERE"
        "port"              "3306"
    }
    "stats"
    {
        "driver"            "mysql"
        "host"              "127.0.0.1"
        "database"          "stats"
        "user"              "l4d2stats"
        "pass"              "STRONG_PASSWORD_HERE"
        "port"              "3306"
    }
}
```

---

## 5. Get the stats website repo (contains SQL schemas)

```bash
cd ~
git clone https://github.com/Jackzmc/l4d2-stats-website.git
```

Import the schema:

```bash
mysql -u l4d2stats -p stats < ~/l4d2-stats-website/sql/stats_database.sql
```

Then apply v3 migration if present:

```bash
mysql -u l4d2stats -p stats < ~/l4d2-stats-website/sql/v3-migration.sql 2>/dev/null
```

Verify:

```bash
mysql -u l4d2stats -p stats -e "SHOW TABLES;"
```

Should list ~12 tables (`stats_users`, `stats_games`, `stats_sessions`, `stats_map_info`, etc.).

---

## 6. Compile the stats plugin from source

The precompiled binary in releases is often v2 — won't work with v3 schema. Compile fresh:

```bash
cd /tmp
git clone --depth 1 https://github.com/Jackzmc/l4d2-stats-plugin.git jackz-stats
cd jackz-stats
```

Copy SourcePawn includes to your scripting directory:

```bash
cp scripting/include/*.inc ~/serverfiles/left4dead2/addons/sourcemod/scripting/include/
cp -r scripting/stats ~/serverfiles/left4dead2/addons/sourcemod/scripting/
cp scripting/l4d2_stats_recorder.sp ~/serverfiles/left4dead2/addons/sourcemod/scripting/
```

Compile:

```bash
cd ~/serverfiles/left4dead2/addons/sourcemod/scripting
./spcomp l4d2_stats_recorder.sp
```

You should get `l4d2_stats_recorder.smx` with no errors. Move to plugins:

```bash
mv l4d2_stats_recorder.smx ../plugins/
```

---

## 7. Install dependencies

The plugin requires **L4D Info Editor** (by SilverShot). Download from:
https://forums.alliedmods.net/showthread.php?t=310586

Extract and copy `addons/sourcemod/*` over your install:

```bash
unzip /tmp/l4d_info_editor.zip -d /tmp/info-editor
cp -r /tmp/info-editor/addons/sourcemod/* ~/serverfiles/left4dead2/addons/sourcemod/
```

Also ensure **Left 4 DHooks Direct** and **skill_detect** are installed (most modern L4D2 servers already have them).

---

## 8. Load and verify

```bash
./l4d2server send "sm plugins load l4d2_stats_recorder"
sleep 3
./l4d2server send "sm plugins info l4d2_stats_recorder.smx"
sleep 2
tail -30 ~/log/console/l4d2server-console.log | grep -iE "stats_recorder|connected to database|error"
```

Should see `Connected to database stats` and no errors.

**Important:** The plugin only writes `stats_games` rows on `finale_win`. To test, play a finale map (e.g., `c2m5_concert`) to the rescue. Then:

```bash
mysql -u l4d2stats -p stats -e "SELECT * FROM stats_games;"
mysql -u l4d2stats -p stats -e "SELECT * FROM stats_users;"
```

---

## 9. Install Node.js 22 and pnpm (for the website)

```bash
curl -fsSL https://deb.nodesource.com/setup_22.x | sudo bash -
sudo apt install -y nodejs
sudo npm install -g pnpm
```

---

## 10. Set up the Astro website

```bash
cd ~/l4d2-stats-website/website

# Configure pnpm to run install scripts (needed for skia-canvas native module)
cat > .npmrc <<EOF
enable-pre-post-scripts=true
node-linker=hoisted
EOF

# Install deps
pnpm install --production=false

# Configure
cat > .env.local <<EOF
DATABASE_URL=mysql://l4d2stats:STRONG_PASSWORD_HERE@localhost/stats
PUBLIC_SITE_URL=https://YOUR_DOMAIN_HERE
PUBLIC_SITE_NAME=My L4D2 Stats
HOST=0.0.0.0
PORT=4321
EOF

# Build
pnpm build
```

If `pnpm build` fails complaining about `skia.node` missing:

```bash
cd ~/l4d2-stats-website/website/node_modules/.pnpm/skia-canvas@*/node_modules/skia-canvas
node lib/prebuild.mjs download
cd ~/l4d2-stats-website/website
pnpm build
```

---

## 11. Set up systemd service for the website

```bash
sudo tee /etc/systemd/system/l4d2-stats-web.service > /dev/null <<EOF
[Unit]
Description=L4D2 Stats Astro Web UI
After=network.target mariadb.service

[Service]
Type=simple
User=$USER
WorkingDirectory=/home/$USER/l4d2-stats-website/website
EnvironmentFile=/home/$USER/l4d2-stats-website/website/.env.local
ExecStart=/usr/bin/node /home/$USER/l4d2-stats-website/website/dist/server/entry.mjs
Restart=on-failure
RestartSec=10

[Install]
WantedBy=multi-user.target
EOF

sudo systemctl daemon-reload
sudo systemctl enable --now l4d2-stats-web
sudo systemctl status l4d2-stats-web --no-pager
```

Test: `curl http://localhost:4321/` should return HTML.

---

## 12. Set up nginx reverse proxy with HTTPS

```bash
sudo apt install -y nginx certbot python3-certbot-nginx
```

Create `/etc/nginx/sites-available/l4d2-stats`:

```nginx
server {
    listen 80;
    listen [::]:80;
    server_name YOUR_DOMAIN_HERE;

    location /.well-known/acme-challenge/ {
        root /var/www/html;
    }

    location / {
        return 301 https://$host$request_uri;
    }
}

server {
    listen 443 ssl http2;
    listen [::]:443 ssl http2;
    server_name YOUR_DOMAIN_HERE;

    location / {
        proxy_pass http://127.0.0.1:4321;
        proxy_http_version 1.1;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
    }
}
```

Enable and get the cert:

```bash
sudo ln -s /etc/nginx/sites-available/l4d2-stats /etc/nginx/sites-enabled/
sudo certbot --nginx -d YOUR_DOMAIN_HERE
sudo systemctl reload nginx
```

Certbot auto-modifies the config with cert paths and sets up auto-renewal.

---

## 13. Point the in-game URL at your site

Edit `~/serverfiles/left4dead2/cfg/sourcemod/l4d2_stats_recorder.cfg`:

```
l4d2_stats_url "https://YOUR_DOMAIN_HERE/"
```

Apply live:

```bash
./l4d2server send 'l4d2_stats_url "https://YOUR_DOMAIN_HERE/"'
```

---

## 14. Known gotchas

**The plugin only commits on `finale_win`.** Partial campaigns don't get recorded as games. The site requires at least one completed finale to render the Summary page.

**`stats_map_info.flags` must be `1`** for the Summary page to recognize a map as official:

```sql
UPDATE stats_map_info SET flags = 1 WHERE flags = 0;
```

Run this any time a new finale map gets added to the table.

**Schema/plugin lockstep:** If you ever update either Jackz's plugin or website via `git pull`, update both together. Version drift causes hard-to-debug column mismatch errors.

**LinuxGSM uses `l4d2server.cfg`, not `server.cfg`** — they're loaded at different times. Don't confuse them.

---

## 15. Hardening (recommended)

```bash
# Firewall - close everything except essentials
sudo ufw default deny incoming
sudo ufw default allow outgoing
sudo ufw allow 22/tcp
sudo ufw allow 80/tcp
sudo ufw allow 443/tcp
sudo ufw allow 27015/udp comment 'L4D2 game'
sudo ufw allow 27020/udp comment 'SourceTV'
sudo ufw enable

# fail2ban for SSH brute-force protection
sudo apt install -y fail2ban
sudo systemctl enable --now fail2ban

# Auto-apply security updates
sudo apt install -y unattended-upgrades
sudo dpkg-reconfigure -plow unattended-upgrades
```

Daily backup cron (place script in `~/backup.sh`, chmod +x, then `crontab -e`):

```cron
0 4 * * * /home/$USER/backup.sh
```

First, drop the DB password into `~/.my.cnf` so `mysqldump` can pick it up without exposing it on the command line (which would leak to `ps` while the dump runs):

```bash
cat > ~/.my.cnf <<EOF
[client]
user=l4d2stats
password=STRONG_PASSWORD_HERE
EOF
chmod 600 ~/.my.cnf
```

Then the backup script itself (`~/backup.sh`):

```bash
#!/bin/bash
# Daily L4D2 stats backup — DB dump + critical configs, 30-day rotation.
# Invoked by cron at 04:00 every day.

set -euo pipefail

BACKUP_DIR="$HOME/backups"
DATE=$(date +%Y-%m-%d)
RETENTION_DAYS=30

mkdir -p "$BACKUP_DIR"

# 1. Database dump (single transaction, gzipped). Credentials read from
#    ~/.my.cnf — keeps the password out of process listings.
mysqldump --single-transaction --quick --routines stats \
    | gzip -9 > "$BACKUP_DIR/stats-$DATE.sql.gz"

# 2. Tarball of the configs that take real effort to rebuild. The
#    "|| true" swallows missing-file errors so an absent .env.local
#    (e.g., reinstall in progress) doesn't kill the rest of the backup.
tar czf "$BACKUP_DIR/configs-$DATE.tar.gz" \
    "$HOME/serverfiles/left4dead2/addons/sourcemod/configs/databases.cfg" \
    "$HOME/serverfiles/left4dead2/cfg/sourcemod/l4d2_stats_recorder.cfg" \
    "$HOME/l4d2-stats-website/website/.env.local" \
    /etc/nginx/sites-available/l4d2-stats \
    2>/dev/null || true

# 3. Prune anything older than the retention window.
find "$BACKUP_DIR" -name 'stats-*.sql.gz'   -mtime +$RETENTION_DAYS -delete
find "$BACKUP_DIR" -name 'configs-*.tar.gz' -mtime +$RETENTION_DAYS -delete

# 4. One-line success log — cheap audit trail.
echo "$(date -Iseconds) backup OK ($(du -sh $BACKUP_DIR | cut -f1) on disk)" \
    >> "$BACKUP_DIR/backup.log"
```

Set executable and test once interactively before relying on cron:

```bash
chmod +x ~/backup.sh
~/backup.sh
ls -la ~/backups/
```

You should see one `stats-YYYY-MM-DD.sql.gz` and one `configs-YYYY-MM-DD.tar.gz`.

**Restore drill:** practice the restore at least once *before* you need it.

```bash
# DB restore
gunzip -c ~/backups/stats-YYYY-MM-DD.sql.gz | mysql stats

# Configs restore (extract back into place)
tar xzf ~/backups/configs-YYYY-MM-DD.tar.gz -C /
```

**Offsite copy:** local backups don't help if the VPS itself dies. After the script lands cleanly, add a second cron line that rsyncs `~/backups/` to a remote box, B2 bucket, or whatever you have.

---

## Summary

After all steps:
- Plugin records games to MariaDB on each `finale_win`
- Website at `https://YOUR_DOMAIN_HERE` displays leaderboards, summary, maps, game history
- In-game chat shows the stats URL at finale completion
- All traffic is HTTPS, ports are locked down, backups run nightly

Estimated time: 1-2 hours start to finish, more if you hit the schema/plugin drift gotcha.