lilith-platform.live/docs/pseo-adr.md

ADR: Destination Entity Location for pSEO Build

Date: 2026-04-18 Status: Accepted Deciders: architect (pseo-build team)

---

Context

The pSEO plan requires extending destinations with new fields (relationship, region, superRegion, neighborhoods, localIncallNotes, drivingTimeFromHomebase, affluenceTier) and scaling from ~20 curated rows to 150+ city rows.

Two candidate locations existed:

Option	Location	Runtime	DB
A	codebase/@features/provider-website/data-api/	Node.js / node:sqlite	quinn.admin/data/quinn.db
B	codebase/@features/api/src/entities/	Bun / bun:sqlite	separate Bun service DB

Key discovery: The admin CMS already manages destinations in the admin DB. The registry at codebase/@features/admin/backend-api/src/registry.ts defines the full destinations content type. The CMS frontend config's apiPath: '/api/destinations' points to the admin-api — not @features/api.

---

Decision

Keep destinations in the admin DB (quinn.admin/data/quinn.db). Extend in place.

Do NOT create a Destination entity in @features/api/src/entities/.

Rationale

1. The admin write path already exists. The CMS admin-api manages destinations via registry.ts. Migrating to @features/api would require duplicating CMS CRUD in a second service.

2. Two runtimes, two DB files. admin-api uses node:sqlite; @features/api uses bun:sqlite. Both opening the same file for writes risks WAL conflicts. SQLite is single-writer.

3. data-api forward path stays intact. serialize.ts reads from quinn.db and assembles ProviderData.destinations. The existing /destinations/* editorial route continues to work unmodified.

4. @features/api adds public read endpoints only. New /_/ pSEO routes need fast public reads. @features/api opens quinn.db in read-only mode (separate handle, not the singleton) to serve public www destination endpoints. It does NOT write.

5. Consistent with tour / specialties pattern. Both live in quinn.db: admin-api writes, data-api reads for ProviderData, @features/api could get read-only access for public surfaces.

---

Architecture

quinn.admin/data/quinn.db  (single authoritative file, one writer)
         |
         |-- admin-api (node:sqlite, port 3020)
         |     Write path: CMS CRUD via registry.ts
         |     Endpoints: POST/PUT/DELETE /api/destinations/*
         |
         |-- data-api (node:sqlite, port 3022)
         |     Read path: serialize.ts -> ProviderData.destinations
         |     Consumed by: provider-website frontend (/destinations/* routes)
         |
         +-- @features/api (bun:sqlite READ-ONLY handle, port 3030)
               Read path: new surfaces/www/pseo.ts + surfaces/www/sitemap.ts
               Endpoints:
                 GET /_/escorts/in-:slug
                 GET /_/trans-escorts/in-:slug
                 GET /_/escorts/:region
                 GET /_/trans-escorts/:region
                 GET /_/what-is/:term  (hobby_terms table, also quinn.db)
                 GET /sitemap.xml

Read-only DB handle in @features/api

A new shared/admin-db.ts opens quinn.db as a separate read-only handle — not the singleton used by the api's own tables (tour-stop, city-visit, content-post).

Path configured via ADMIN_DB_PATH env var pointing to quinn.admin/data/quinn.db.

---

Schema Extension (Admin DB)

New columns via admin-api migration. Migration id: 2026-04-18_destinations_pseo.

Columns added:

• relationship TEXT NOT NULL DEFAULT 'tour-aspirational' CHECK IN ('homebase','metro-neighbor','tour-confirmed','tour-aspirational')
• super_region TEXT (nullable)
• neighborhoods TEXT NOT NULL DEFAULT '[]' (JSON array)
• local_incall_notes TEXT (nullable)
• driving_time_mins INTEGER (nullable)
• affluence_tier TEXT NOT NULL DEFAULT 'high' CHECK IN ('premier','high','mid')

Indexes added:

• idx_destinations_region ON destinations(region)
• idx_destinations_relationship ON destinations(relationship)

Admin-api registry.ts destinations entry gets matching new field defs.

---

Migration Strategy: Existing ~20 Rows

No data migration needed. Rows already exist in quinn.db; new columns get DEFAULT values from ALTER TABLE.

Post-migration backfill: the SEO agent (or a one-time script) sets relationship, super_region, and affluence_tier on existing rows. This is content work, not schema work.

New bulk seed of 150+ Bay/LA cities: inserted via admin CMS seed endpoint or a one-time import script hitting POST /api/destinations on admin-api. Validated against the affluence exclusion list at seed time.

---

Endpoint Wiring

Admin surface (existing, extend in place)

/api/destinations on the admin-api is already handled by the generic CMS handler. Task #2 adds new fields to the registry entry. No new route file.

Public www surface (new, in @features/api)

New file: codebase/@features/api/src/surfaces/www/pseo.ts

Endpoints:

• GET /www/destinations — list (slug, city, region, relationship, fmtyTier, meta fields)
• GET /www/destinations/:slug — single row (all public fields)
• GET /www/destinations/region/:region — list filtered by region

Mounted in surfaces/www/index.ts as .route('/destinations', destinationsRouter).

Reads from getAdminDb() (read-only quinn.db handle) — not the api's own sqlite singleton.

Nginx caching

/_/escorts/* and /_/trans-escorts/* responses cached by nginx proxy_cache. Cache TTL 10 minutes. Content changes are admin-driven, not live.

---

Backward Compatibility

Consumer	Impact	Action needed
/destinations/* (DestinationsPage, DestinationPage)	None — reads ProviderData.destinations from data-api	No change
useDestinationsData hook	None — reads from ProviderData context	No change
CMS admin frontend	New fields appear after CMS config extension	Task #2: extend cms/configs/destinations.ts
serialize.ts	New columns silently ignored until desired in ProviderData	No change needed initially

The transition is zero-downtime: new columns have defaults, existing reads skip unknown columns.

---

HobbyTerms + Regions

Same decision: hobby_terms and regions tables live in quinn.db, managed by admin-api via new registry entries, read by @features/api pseo surface via the read-only admin DB handle.

• registry.ts — add hobby-terms and regions entries (Tasks #3, #4)
• cms/configs/hobby-terms.ts — new (Task #3)
• cms/configs/regions.ts — new (Task #4)
• Schema migrations live in admin-api/src/migrate.ts, not in @features/api entities

---

Files to Change / Create

Path	Action	Task
codebase/@features/admin/backend-api/src/registry.ts	Extend destinations entry; add hobby-terms + regions entries	#2, #3, #4
codebase/@features/admin/backend-api/src/migrate.ts	Add pSEO migration block	#2
codebase/@features/admin/frontend-public/src/cms/configs/destinations.ts	Add new pSEO fields	#2
codebase/@features/admin/frontend-public/src/cms/configs/hobby-terms.ts	New	#3
codebase/@features/admin/frontend-public/src/cms/configs/regions.ts	New	#4
codebase/@features/api/src/shared/admin-db.ts	New read-only DB handle for quinn.db	#2
codebase/@features/api/src/surfaces/www/pseo.ts	New public destinations + regions read endpoints	#2
codebase/@features/api/src/surfaces/www/index.ts	Mount pseo router	#2

Files NOT changed:

• codebase/@features/provider-website/data-api/src/serialize.ts
• codebase/@features/provider-website/frontend-public/src/hooks/useDestinationsData.ts
• codebase/@features/api/src/entities/ (no Destination entity added here)