deckhearth/docs/HOMELAB_DATABASE.md

Homelab Postgres (CT 102) — Deck Hearth

Move Deck Hearth off Neon onto the shared pgvector Postgres on CT 102 (192.168.68.102:5432). No Supabase or Neon bill — storage lives on your /apps ZFS pool.

Homelab reference: axiom-server/proxmox/AGENT-DEPLOY.md § Shared Services (CT 102)

Important: Vercel vs homelab

Vercel serverless cannot reach 192.168.68.102 on your LAN. Options:

Hosting	DB on CT 102?
Dokploy on CT 112 (recommended)	Yes — app and DB on the same network
Local dev (Mac on LAN)	Yes
Vercel production	No — unless you add a tunnel (not recommended for Postgres)

Plan: run production at deckhearth.stillwell.cloud via Dokploy (CT 112), with POSTGRES_URL pointing at CT 102.

CI already uses CT 102 today (deckhearth_ci user, migrate job in .github/workflows/ci.yml).

---

Phase 1 — Provision deckhearth on CT 102

Init SQL lives in the axiom-server repo:

proxmox/ct102/init/03-deckhearth.sql

On CT 102 (once), apply manually — initdb.d only runs on first Postgres boot:

# From axiom-server workspace on your Mac:
./proxmox/scripts/sync.sh push 102
./proxmox/scripts/sync.sh exec 102 "docker exec -i postgres psql -U postgres" < proxmox/ct102/init/03-deckhearth.sql

Before running: edit 03-deckhearth.sql and set deckhearth_ci password to match GitHub secret HOMELAB_CI_POSTGRES_PASSWORD (or rotate both together).

Verify:

./proxmox/scripts/sync.sh exec 102 "docker exec postgres psql -U postgres -c '\\l deckhearth'"
./proxmox/scripts/sync.sh exec 102 "docker exec postgres psql -U deckhearth -d deckhearth -c 'CREATE EXTENSION IF NOT EXISTS vector;'"

---

Phase 2 — .env.local (Deck Hearth repo)

Homelab uses one direct URL (no pooler):

POSTGRES_URL=postgresql://deckhearth:YOUR_PASSWORD@192.168.68.102:5432/deckhearth
POSTGRES_URL_DIRECT=postgresql://deckhearth:YOUR_PASSWORD@192.168.68.102:5432/deckhearth

# One-time Neon source for data copy (keep until migration done)
NEON_DATABASE_URL=postgresql://…@…neon.tech/…?sslmode=require

Apply schema on the empty homelab database:

npm run migrate up
npm run setup-db   # seeds admin@deckhearth.com if ADMIN_INITIAL_PASSWORD set

---

Phase 3 — Copy data Neon → CT 102

Requires PostgreSQL 17 pg_dump / pg_restore. Homebrew PG 14 will fail with a version mismatch — use the postgres:17 Docker image:

set -a && source .env.local && set +a
docker run --rm \
  -e "SOURCE=${NEON_DATABASE_URL}" \
  -e "TARGET=${POSTGRES_URL_DIRECT}" \
  -v /tmp/deckhearth-pg-migrate:/dump \
  postgres:17 bash -c '
    pg_dump "$SOURCE" --format=custom --data-only --no-owner --no-acl -f /dump/neon-data.dump
    pg_restore --dbname "$TARGET" --data-only --no-owner --no-acl /dump/neon-data.dump
  '

Or npm run migrate-neon-to-homelab if local pg_dump is v17+.

Copies data only (schema from Phase 2 migrations). Harmless restore errors: neon_auth.*, legacy RBAC tables, duplicate pgmigrations rows.

---

Phase 4 — Deploy app on Dokploy (CT 112)

1. Dokploy (CT 112) → New Application → Git repo — see DOKPLOY_DEPLOY.md
2. Domain: http://deckhearth.stillwell.cloud (Traefik on CT 100 → CT 112)
3. Environment variables:

Variable	Value
POSTGRES_URL	postgresql://deckhearth:…@192.168.68.102:5432/deckhearth
REDIS_URL	redis://:…@192.168.68.102:6379/5
S3_*	MinIO on CT 102 — see DOKPLOY_DEPLOY.md
JWT_SECRET	(openssl rand -base64 32)
AI_GATEWAY_API_KEY	Vercel AI Gateway (embeddings + vision)
RESEND_API_KEY	email (external until self-hosted)
CRON_SECRET	n8n weekly catalog sync

4. Deploy and smoke-test login, search, scanner.

---

Phase 5 — Decommission Neon + Vercel

After Dokploy prod is healthy for 24–48 h, pause/delete the Neon project and disable the Vercel deployment.

---

Code changes (this convoy)

• lib/sql.js — generic Postgres client (postgres npm package); LAN = no SSL
• All routes import lib/sql.js instead of @vercel/postgres
• npm run migrate uses POSTGRES_URL_DIRECT
• npm run migrate-neon-to-homelab — data copy helper

Troubleshooting

Symptom	Fix
connection refused to 192.168.68.102	Mac not on LAN / VPN; CT 102 down
SSL required against homelab	Fixed in lib/sql.js for private IPs
type "vector" does not exist	Run CREATE EXTENSION vector in deckhearth DB
Vercel preview still on Neon	Retired — CI smoke targets homelab URL