1cc2e28423
Replace @vercel/postgres, Blob, and Upstash with lib/sql.js, MinIO object storage, and CT 102 Redis rate limits. Add Dockerfile for Dokploy deploy, homelab runbooks, Neon data-copy helper, and point CI smoke/visual at the homelab URL instead of Vercel previews. Co-authored-by: Cursor <cursoragent@cursor.com>
135 lines
4.5 KiB
Markdown
135 lines
4.5 KiB
Markdown
# 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:
|
||
|
||
```bash
|
||
# 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:
|
||
|
||
```bash
|
||
./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):
|
||
|
||
```bash
|
||
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:
|
||
|
||
```bash
|
||
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:
|
||
|
||
```bash
|
||
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`](./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 |
|