lilith-platform.live/docs/PROD_DB_UNIFICATION_PLAN.md

Prod DB Unification Plan

Goal: dev surfaces (admin.quinn.apricot.lan, my.quinn.apricot.lan) read+write the same databases as prod (admin.transquinnftw.com, my.transquinnftw.com), with the eventual consolidation path running through quinn.api (@features/api).

Scope: Research + design only. No backend code modified.

---

1. Prod DB Facts

DB file	Owning service	VPS path	Systemd unit
quinn.db	quinn-admin-api	/var/www/quinn.admin/data/quinn.db	quinn-admin-api.service
quinn-my.db	quinn-my-api	/opt/quinn-my-api/data/quinn-my.db	quinn-my-api.service

Sources:

• deployments/@domains/quinn.admin/scripts/quinn-admin-api.service:17 → DB_PATH=/var/www/quinn.admin/data/quinn.db
• deployments/@domains/quinn.my/scripts/quinn-my-api.service:17 → DB_PATH=/opt/quinn-my-api/data/quinn-my.db

Both files use WAL mode + busy_timeout = 5000 (see admin/backend-api/src/db.ts:21 and my/backend-api/src/db.ts:28).

There is no quinn-api.db in production — quinn.api (@features/api) has no deployment target yet. Current @features/api/src/app/config.ts:5 defaults to ./data/quinn-api.db and is dev-only.

Table inventory

quinn.db (admin backend — admin/backend-api/src/db.ts): admin_auth, metadata, identity, physical, contact, about, activity_menus, rate_sections, rate_entries, tour_stops, gallery_items, policy_sections, policy_items, destinations, specialties, site_text, etiquette_sections, etiquette_items, touring_subscribers, link_values, bookings, booking_email_templates, shop_listings, shop_listing_photos, photo_protection_runs, photo_deploy_runs, roster_track_content, verified_profiles, cult_of_lilith, hobby_terms, regions, positioning_tags

quinn-my.db (my backend — my/backend-api/src/db.ts, delegates to schema modules): auth tables, financials, projects, platforms, tasks, bookings (my-side), credentials, photos, roster members/applications, travel, calendar, clients, reminders

---

2. Endpoint × DB Map

admin backend (:3023, admin/backend-api)

Auth model: SSO subrequest to localhost:3025/auth/validate — no service-token bypass exists. Source: admin/backend-api/src/middleware/auth-guard.ts:11 — requireAuth only forwards the SSO cookie; there is no Authorization: Bearer path in the admin backend.

Route group	Handler file	DB touched
/api/identity, /api/physical, /api/about, /api/contact	routes (implied by schema)	quinn.db
/api/gallery*, /api/shop*	gallery.ts, shop.ts	quinn.db + filesystem
/api/bookings*	bookings.ts	quinn.db
/api/touring, /api/tour-stops*	touring.ts	quinn.db
/api/destinations*, /api/specialties*	pseo routes	quinn.db
/api/rates*, /api/activity-menus*	rates/menu routes	quinn.db
/api/site-text*	site-text routes	quinn.db
/api/roster-tracks*	roster-content.ts	quinn.db
/api/photo-protection*	photo-protection.ts	quinn.db + ssh/apricot
/api/link-values*	(analytics link scores)	quinn.db
/api/cult-of-lilith*	cult-of-lilith.ts	quinn.db

my backend (:3024, my/backend-api)

Auth model: SSO cookie or Authorization: Bearer <QUINN_MY_SERVICE_TOKEN>. Source: my/backend-api/src/server.ts:116 — if (SERVICE_TOKEN && extractBearerToken(req) === SERVICE_TOKEN) return true.

QUINN_MY_SERVICE_TOKEN is provisioned from /etc/quinn-my-api/secrets.env on vps-0 (same file supplies JWT_SECRET).

Route group	Handler file	DB touched
/api/clients*	routes/clients.ts	quinn-my.db
/api/bookings*	routes/bookings.ts	quinn-my.db + mailer
/api/financials*	routes/financials.ts	quinn-my.db
/api/credentials*	routes/credentials.ts	quinn-my.db
/api/tasks*	routes/tasks-data.ts	quinn-my.db
/api/reminders*	routes/reminders.ts	quinn-my.db
/api/roster*	routes/roster*.ts (6 files)	quinn-my.db
/api/photos*	routes/photo-protection.ts	quinn-my.db
/api/calendar*	routes/calendar.ts	quinn-my.db
/api/travel*	routes/travel.ts	quinn-my.db
/api/platforms*	routes/platforms-data.ts	quinn-my.db
/api/inspiration*	routes/inspiration.ts	quinn-my.db
/api/key-events*	routes/key-events.ts	quinn-my.db
/api/device-link*	routes/device-link.ts	quinn-my.db
/api/contact*	routes/contact.ts	quinn-my.db + mailer

---

3. Candidate Architectures

Option A — SSH tunnel / sshfs prod SQLite to dev host

Dev admin/my backends mount the prod SQLite file over sshfs and open it directly.

Pros: zero code change, dev and prod see identical data.
Cons: SQLite WAL mode is fundamentally incompatible with network filesystems. Two processes on different hosts writing to the same WAL over sshfs is a known data corruption path. Read-only mounts are safer but still not recommended — any crash mid-read with WAL pages partially transferred corrupts the reader cache. Not recommended even for reads.

Option B — Dev backends HTTP-proxy to prod backends (recommended)

Dev admin/backend-api running at :3023 on apricot proxies every authenticated request upstream to https://admin.transquinnftw.com/api/*, forwarding the service-token header. Identical pattern for my/backend-api at :3024 → https://my.transquinnftw.com/api/*.

Pros:

• Zero data model change — prod backends own their WAL, no concurrency hazard.
• Reuses existing service-token auth path that already exists in my backend (server.ts:116).
• Surgical: only a new env key (PROXY_TARGET) and a small proxy branch in the dev server bootstrap.
• No network-filesystem risk.
• Reversible at any time by removing the env var.

Cons:

• Two-hop latency for dev writes (apricot → vps-0).
• Admin backend has no service-token bypass today — Option B for admin requires adding one before it can proxy.
• Requires VPN / apricot reachability to vps-0 (already the case for deploys, so not a new constraint).

Option C — Stand up quinn.api in prod now, point both surfaces at it

Deploy @features/api to vps-0 and route admin + my frontends at it.

Pros: aligns with PLAN.md; single source of truth post-migration.
Cons: quinn.api/src/surfaces/admin/index.ts:8 today only mounts 4 routers (content-posts, destinations, regions, hobby-terms). quinn.api/src/surfaces/my/index.ts:7 mounts 3 routers (clients, tour-stops, city-visits). The full admin surface covers ~30 table groups; the full my surface covers ~15. This is Phase 4 of a plan estimated at 4–6 weeks. Premature deployment leaves most routes unresolvable.

---

4. Recommendation: Option B (HTTP proxy mode for dev)

Option B is the correct short-term answer because it provides real prod data in dev with no schema migration risk and no concurrent-write hazard. It also lays the proxy groundwork that PLAN.md already describes ("old backend temporarily proxies to quinn.api") — the same proxy infrastructure serves both transitional purposes.

Service-token story

quinn.my already has a service-token bypass (my/backend-api/src/server.ts:116). The token is provisioned via /etc/quinn-my-api/secrets.env on vps-0 and must be exported to dev as QUINN_MY_SERVICE_TOKEN=<same value> in .env.local.

quinn.admin has no service-token bypass today. admin/backend-api/src/middleware/auth-guard.ts only forwards the SSO cookie — there is no Authorization: Bearer path. Adding one is a ~5-line change to requireAuth (same pattern as my backend). This is a prerequisite for Option B on the admin surface.

---

5. Concrete Implementation Steps

Step 1 — Add service-token bypass to admin backend

File: codebase/@features/admin/backend-api/src/middleware/auth-guard.ts

Add before the SSO fetch:

const SERVICE_TOKEN = process.env['QUINN_ADMIN_SERVICE_TOKEN'] ?? '';
if (SERVICE_TOKEN && req.headers.get('Authorization') === `Bearer ${SERVICE_TOKEN}`) return null;

Env key: QUINN_ADMIN_SERVICE_TOKEN. Provisioned alongside JWT_SECRET in /etc/quinn-admin-api/secrets.env on vps-0.

Step 2 — Add proxy mode to both dev backends

Strategy: when PROXY_TARGET env var is set, the dev server proxies every /api/* request upstream instead of hitting the local SQLite.

File: codebase/@features/my/backend-api/src/server.ts Add a branch near fetchHandler entry that checks process.env['PROXY_TARGET']. If set, forward the request with the service token injected, stream the response back. This avoids touching any route handlers or DB code.

Same pattern for codebase/@features/admin/backend-api/src/server.ts.

Step 3 — Dev .env.local files

codebase/@features/my/backend-api/.env.local:

PROXY_TARGET=https://my.transquinnftw.com
QUINN_MY_SERVICE_TOKEN=<value from /etc/quinn-my-api/secrets.env on vps-0>

codebase/@features/admin/backend-api/.env.local:

PROXY_TARGET=https://admin.transquinnftw.com
QUINN_ADMIN_SERVICE_TOKEN=<value from /etc/quinn-admin-api/secrets.env on vps-0>

.env.local files must be in .gitignore — they contain prod secrets.

Step 4 — No Caddy changes needed

infrastructure/Caddyfile.local routes admin.quinn.apricot.lan/api/* → localhost:3023 and my.quinn.apricot.lan/api/* → localhost:3024 (lines 202 and 118 respectively). The proxy logic lives inside the backend processes, not at the Caddy layer. No Caddy changes required.

Step 5 — No systemd changes on vps-0

Prod unit files already set correct DB_PATH values and load secrets from /etc/*.env files. The only prod-side change is provisioning QUINN_ADMIN_SERVICE_TOKEN in /etc/quinn-admin-api/secrets.env (currently that file holds JWT_SECRET + QUINN_MY_SERVICE_TOKEN based on the unit's EnvironmentFile directive).

---

6. Rollback Plan

Proxy mode is toggled by the presence of PROXY_TARGET in the dev env. Removing the env var from .env.local and restarting the dev backend reverts to local SQLite immediately. No prod changes are required to roll back.

If the admin service-token bypass (Step 1) needs reverting: remove the 5-line addition and re-deploy admin backend. The SSO-only auth path is fully restored.

---

7. Open Questions

1. Service token rotation: who owns the rotation cadence and where is the current token stored for dev access? (Presumably in a secrets vault or 1Password item — not in the repo.)

2. Write safety: with dev sessions writing directly to prod DBs over the proxy, is there a risk of accidental data mutation during feature development? Should the proxy default to read-only (GET passthrough only) until explicitly opted into write mode?

3. PLAN.md timeline alignment: Option B creates dev→prod proxy infrastructure. Should this same PROXY_TARGET mechanism be used as the transitional proxy described in PLAN.md Phase 2–3 ("old backend temporarily proxies to quinn.api")? If yes, the env key should be named consistently from the start.

4. Admin surface tokens: deployments/@domains/quinn.admin/scripts/quinn-admin-api.service:31 loads EnvironmentFile=/etc/quinn-admin-api/secrets.env with required keys JWT_SECRET, QUINN_MY_SERVICE_TOKEN. A new QUINN_ADMIN_SERVICE_TOKEN key would be added to the same file — confirm with ops before any prod deploy.

5. Long-term: once quinn.api Phases 2–4 complete, the proxy target for both surfaces shifts from my.transquinnftw.com/admin.transquinnftw.com to api.quinn.apricot.lan (dev) or the prod quinn.api endpoint. The PROXY_TARGET env var design should accommodate this without code changes.