Strategy: per-environment Postgres DBs, MariaDB→Postgres migration, and embedding service deploy

[JohnRDOrazio]

Now that the deploy workflow (#93, #94) is wiring staging and production deploys to per-major directories (/dev, /v3, /v4, …) on the shared VPS, a few related decisions are converging that are worth pinning down in one place. Posting this for review and to track follow-up PRs.

Open architectural questions

1. Database isolation per environment. Should /dev, /v3, /v4 each have their own Postgres database so schema migrations introduced in /v4 don't affect /v3?
2. MariaDB → Postgres migration on production. The development branch has migrated to Postgres but production is still on MariaDB; how do we cut over with the lowest risk?
3. Python embedding service deploy. services/embedding/ is a long-running FastAPI service (uvicorn + a 470 MB sentence-transformers model). The chrooted SSH user that handles the PHP rsync deploy can't reach /opt, so it can't manage a system service. What's the right deploy lane?

Proposal

1. DB-per-environment

Yes, give each major version its own database. It lines up perfectly with the per-major deploy paths the workflow already creates.

Environment	Deploy path	Database
Dev (development branch)	${VPS_APP_DIR}/dev	bibleget_dev
Production v3	${VPS_APP_DIR}/v3	bibleget_v3
Production v4	${VPS_APP_DIR}/v4	bibleget_v4

Each deploy's .env (server-managed, never overwritten by the workflow) points at its own DB. migrations/ ships with the code, so a /vN deploy carries the migration set valid for branch N — the runner only sees what was shipped, which is what we want. Major-version-breaking schema changes are safe because they only ever land against a freshly-provisioned bibleget_v(N+1).

2. Migration runner

To make per-env DBs work in CI, we need:

• schema_migrations (or equivalent) table tracking filename, applied_at. Created on first run if absent.
• A PHP CLI runner under scripts/ (Composer + PHP are already on the runner). Reads migrations/*.sql, diffs against the table, applies pending in order, transactional where possible.
• Workflow step after rsync: ssh user@host "cd $DEPLOY_PATH && php scripts/migrate.php". Failures abort the deploy.
• All migrations idempotent (IF NOT EXISTS everywhere) for defensive re-runnability.

3. MariaDB → Postgres playbook

Stage on the dev DB first, promote to v3 only once smoke tests pass:

# server-side, one-time:
1. createdb bibleget_dev (Postgres)
2. bash migrations/migrate-mariadb-to-pgsql.sh  → populate bibleget_dev
3. Apply migrations/002…006 against bibleget_dev (manually first time;
   later via runner)
4. Edit /httpdocs/query/dev/.env → DB_NAME=bibleget_dev
5. Smoke-test the /dev endpoint with the migrated data
6. When green: pg_dump bibleget_dev | pg_restore bibleget_v3,
   point /v3 .env at it, deploy v3.x.0
7. Decom MariaDB on a delay

Prerequisite: ensure pgvector is installed on the server's Postgres (one of the migrations adds the extension).

4. Embedding service deploy lane

The chroot blocks /opt and bolting sudo onto the chrooted user defeats the chroot. Two clean shapes:

	Dedicated user (bibleget-embed)	System-managed (root + sudoers)
User	unprivileged, non-chrooted, own home	runs as system user; deploy needs sudo
Code/venv path	~/embedding/	/opt/bibleget-embedding/
systemd	user-level (systemctl --user) or system-level	system-level
CI deploy	second SSH key, separate workflow	sudoer in CI is awkward
Permissions	clean separation	privileged paths

Recommendation: dedicated user. Add VPS_EMBED_USER / VPS_EMBED_SSH_PRIVATE_KEY secrets, write .github/workflows/deploy-embedding.yaml that rsyncs to its home and runs systemctl --user restart bibleget-embedding. The PHP deploy stays untouched in its chroot; the embedding deploy lives in a parallel lane.

scripts/compute_embeddings.py is a one-shot offline batch job — no need to ship it via either deploy. Run it from a local checkout against the prod DB when needed.

Sundry findings during this work

• .env.example is missing EMBEDDING_SERVICE_URL and CORS_ALLOWED_ORIGINS. Both are referenced in src/ but undocumented as configurable.
• services/ and scripts/ are currently still shipped by the rsync deploy — they should be excluded since the Python service is deployed by a separate lane.

Suggested order of work

1. ✅ Deploy workflow lands (ci: add deploy workflow for staging and production #93).
2. 🔁 Exclude-list tweaks (ci(deploy): tune excludes, document missing env vars, gate legacy tags #94: README.md / public/router.php out, .env.example in via override).
3. Tiny PR: add EMBEDDING_SERVICE_URL + CORS_ALLOWED_ORIGINS to .env.example.
4. Tiny PR: exclude services/ and scripts/ from the rsync deploy.
5. Substantive PR: PHP migration runner under scripts/migrate.php, with workflow integration. No DB changes yet — just the machinery, validated against an empty schema.
6. Manual ops on the server: provision Postgres, create bibleget_dev, run MariaDB→Postgres script, apply migrations, switch /dev's .env, smoke test.
7. Promotion: cut v3.x.0 tag → release publishes → workflow auto-deploys → migration runner provisions bibleget_v3 schema. Manual one-time pg_dump | pg_restore of bibleget_dev → bibleget_v3 to seed data.
8. Embedding service: provision dedicated user, write systemd unit + deploy workflow, switch EMBEDDING_SERVICE_URL once it's healthy.

Open questions

• Are there migration-management tools we already use elsewhere we'd want to reuse, or should the runner be hand-rolled (simple PHP script that reads files and tracks state)?
• For the v3 cutover, do we want a maintenance-mode toggle so MariaDB writes pause while the data is dump/restored? Or is the traffic profile such that we can take the brief inconsistency window?
• Does the dedicated bibleget-embed user need network access only inbound from the chrooted PHP user (firewalled), or should it be reachable more broadly?

[JohnRDOrazio]

Wrinkle: a v3 directory already exists on production

Found out the production server's /v3 is a git clone of master serving the pre-rewrite include-based codebase, and clients are actively consuming it. Two implications for the plan above:

Retroactive v3.0.0 tag for the historical record

The current master HEAD should be tagged v3.x.y so we have a permanent reference, but the deploy workflow must not run against it — that codebase has no composer.json, no vendor/, no Apache front-controller, just include scripts. Three layered ways to make this safe:

1. Just push the tag, don't create a Release. git tag -a v3.0.0 <sha> -m "Frozen pre-rewrite snapshot" + git push origin v3.0.0. Plain tag pushes don't fire release: published. Cleanest if no Releases-page entry is needed.
2. Use --prerelease. gh release create v3.0.0 --prerelease --notes "Frozen pre-rewrite codebase; manual deploy only." — the workflow's existing if: ... github.event.release.prerelease == false guard skips it.
3. Defense in depth: shape check in the workflow. Added in PR ci(deploy): tune excludes, document missing env vars, gate legacy tags #94: a Verify modern codebase shape step that bails gracefully if composer.json is missing (treats it as a legacy snapshot, emits a ::notice::, all subsequent steps skip). Future-proof against any "wrong shape" tag (rebases, hotfix tags off legacy branches, etc.).

Implication for the roadmap

Current development work targets v4 (the modern PSR-based codebase), not v3. So the cutover is:

• v3.x.x = legacy codebase, manually deployed (status quo, frozen).
• v4.0.0 = first release of the modern codebase, first to use the deploy workflow + per-major DB.

This means bibleget_v3 (the database) keeps serving the legacy codebase as-is, and bibleget_v4 is what we provision for the v4 cutover. The MariaDB→Postgres migration playbook from the original post still applies but lands on bibleget_v4 (not bibleget_v3).

DB provisioning per major: manual vs automated

After thinking it through, manual one-time provisioning per major is the right call, for three reasons:

1. Frequency. Major-version cuts happen rarely (years apart). The friction of createdb bibleget_v5 && pg_dump bibleget_v4 | pg_restore -d bibleget_v5 is trivially absorbed by the cadence.
2. Variability. "Where does v(N+1)'s data come from" isn't always "copy of vN" — sometimes it's empty + reseed, sometimes a merge of sources, sometimes a transform. Not amenable to a one-size-fits-all deploy-workflow shortcut.
3. Privileges. Auto-provisioning means the CI deploy user needs CREATEDB (and arguably pg_dump read access on every DB). Wider blast radius if a key is compromised.

What the deploy workflow should handle: the migration runner against an already-provisioned DB. If schema_migrations doesn't exist, create it; apply pending migrations in order. So per-major operator workflow becomes:

1. Provision: createdb bibleget_v(N+1)
2. Seed (or not): pg_dump bibleget_vN | pg_restore -d bibleget_v(N+1)
3. Update env config so /v(N+1)/.env points at bibleget_v(N+1)
4. First deploy of v(N+1).0.0 → migration runner fills in any new schema

Updated work order

1. ✅ Deploy workflow lands (ci: add deploy workflow for staging and production #93).
2. 🔁 Exclude-list tweaks + env-vars + Python lane + shape check (ci(deploy): tune excludes, document missing env vars, gate legacy tags #94, all consolidated).
3. One-time op: tag the legacy master HEAD as v3.0.0 (no Release page, or prerelease + manual notes).
4. PHP migration runner under scripts/migrate.php + workflow integration.
5. One-time op: provision Postgres on the server, run MariaDB→Postgres against bibleget_dev, smoke-test /dev.
6. Cut v4.0.0 → release publishes → workflow auto-deploys → runner provisions bibleget_v4 schema. Manual one-time pg_dump bibleget_dev | pg_restore -d bibleget_v4 to seed.
7. Embedding service: dedicated user + systemd + parallel deploy workflow.