JasonFraser
/
app.bettersight.io
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
app.bettersight.io/CLAUDE.md

6839 lines
255 KiB
Markdown
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

# CLAUDE.md — Bettersight Competitive Intelligence SaaS
> This file is the authoritative specification for Claude Code building Bettersight.
> Read every section before writing any code. Never skip a section.
> When in doubt, re-read this file.
---
## 1. Project Overview
Bettersight is a vertical competitive intelligence SaaS built specifically for adventure
travel operators. It automates competitor price tracking, trip discovery, and market
analysis — replacing hours of manual research with automated intelligence delivered
to tools PMs already use.
**Tagline:** Better sight, better moves.
**Secondary tagline:** Better sight, better decisions.
**Domain:** bettersight.io
**Brand position:** The only competitive intelligence platform that understands
adventure travel — not just that a page changed, but what trip was added, what
price moved, and what it means for your catalogue.
**The anchor feature** is an on-demand competitor analysis Google Sheet add-on that
runs structured AI extraction against competitor trip pages and writes results directly
into the client's spreadsheet. Everything else supports and extends this core.
**Primary reference client:** G Adventures (hackathon origin)
**Target market:** Adventure travel operators — product managers, revenue managers,
strategy leads
---
## 2. Business Context
### Pricing Tiers
| Tier | Price | Seats | Trial |
|---|---|---|---|
| Analyse | $349/month | 5 seats | 14 days — no credit card required |
| Discover | $499/month | 8 seats | 21 days — no credit card required |
| Intelligence | $999/month | 10 seats | 30 days — no credit card required |
| Enterprise | $1,499/month | Unlimited | No trial — custom demo and pilot |
Additional seats: $45/seat/month on any tier.
Enterprise tier is not publicly listed — available on request only.
The $199 Monitor tier has been dropped entirely.
### Tier Feature Summary
**Analyse — $349/month (MVP launch tier)**
- Dashboard as the primary interface — analysis runs, results viewed, push to Sheet
- Trip-intent matching — PM enters destination + duration + style, system finds
the right competitor trip URL automatically via Firecrawl /map (on demand)
- URL confirmation screen before scrape runs — PM can swap any auto-matched URL
- On-demand competitor analysis with side-by-side dashboard results view
- Push to Google Sheet — one button exports results to PM's chosen workbook tab
- Auto-generated battlecards updated after each scrape
- Comparable trip discovery (semantic similarity matching)
- In-dashboard notifications — price changes, new products, alerts in activity feed
- Weekly Monday HTML email brief via Resend
- Fast path (PocketBase cache) / refresh path (live scrape) on-demand analysis
- Firecrawl /monitor for real-time change detection
- Firecrawl /map on demand — for trip-intent URL matching per analysis job only
**Discover — $499/month**
- Everything in Analyse
- Firecrawl /map scheduled — weekly full catalogue crawl per competitor
→ Proactive surveillance, not reactive search
→ Finds new trips the PM never knew to look for
→ New URLs auto-queued for scraping Sunday night without PM involvement
- New trip detection — flags trips added since last crawl
- Catalogue completeness — how many trips per competitor per destination
- Destination gap analysis — destinations competitors cover that you don't
- Sunday night discovery workflow (n8n)
- Monday brief includes all auto-discovered new products
- External notification integrations (PM connects one or more):
→ Slack webhook — real-time alerts into any channel
→ Microsoft Teams webhook — for Microsoft 365 organisations
→ WhatsApp via Twilio — alerts to PM's phone number
**Intelligence — $999/month**
- Everything in Discover
- Full OSINT signal pipeline (15+ sources — see Section 23)
- Competitor Trajectory Score (weekly)
- Wayback Machine positioning history tracking
- Network mapping (leadership, board, investor connections)
- Monthly narrative intelligence report with predicted moves and confidence ratings
- Early warning alerts on significant signal clusters
**Enterprise — $1,499/month (unlisted)**
- Everything in Intelligence
- Dedicated onboarding and account management
- Custom signal sources on request
- Quarterly strategy briefing call
- SLA on scrape freshness and alert delivery
- Custom PDF branded reports
### Tier Pitch Distinction
**Analyse** — "Tell me about this specific trip across these competitors"
PM drives every research job. They describe their product, system finds
the matching competitor trips, PM confirms and runs. Reactive intelligence.
**Discover** — "Tell me everything that changed this week that I didn't know to ask about"
System runs autonomously. PM wakes Monday to a brief that includes trips
they never knew existed. Catches unknown unknowns. Proactive intelligence.
**Intelligence** — "Tell me what my competitors are about to do"
System aggregates signals across 15+ public sources and forecasts competitor
moves 3-6 months ahead. Predictive intelligence.
### MVP Scope (build this first)
Single tier at $349/month (Analyse). Five features only:
1. Dashboard — primary interface for running analysis and viewing results
2. Trip-intent matching — Firecrawl /map on demand finds competitor trip URLs
3. On-demand competitor analysis with dashboard results view + push to Sheet
4. Auto-generated battlecards
5. In-dashboard notifications (activity feed) + Monday HTML email brief via Resend
Comparable trip discovery (semantic similarity) is a Phase 2 addition
within the Analyse tier — build after core analysis flow is working.
Do not build Discover, Intelligence, or Enterprise tier features until
Analyse tier is live with paying clients.
---
## 3. Tech Stack — Authoritative
**Never deviate from this stack without explicit instruction.**
| Layer | Tool | Notes |
|---|---|---|
| Infrastructure | Hetzner VPS | Existing |
| Orchestration | Dokploy | Existing |
| Reverse proxy | Pangolin | Existing |
| Networking | Docker Compose | pangolin_default network |
| Backend API | Flask | Python only. No FastAPI. |
| Scraping engine | app.py (Flask) | Existing — do not rewrite |
| Job queue | RQ (Redis Queue) | Async job processing |
| Queue broker | Redis | Shared with Firecrawl |
| Trip-intent matching | Firecrawl self-hosted | /v1/map on demand — finds competitor trip URLs (Analyse+) |
| Discovery | Firecrawl self-hosted | /v1/map scheduled — weekly catalogue crawl (Discover+ only) |
| Change detection | Content-hash diff in n8n cron | SHA-256 comparison per daily scrape — no external service |
| Proxy — primary | Webshare | ~$3/month, free tier available, used by default |
| Proxy — fallback | Bright Data | Pay-as-you-go, auto-escalated per domain when Webshare blocked |
| AI extraction | OpenRouter | Free tier model cycling with claude-haiku-4-5 paid fallback |
| Embeddings | text-embedding-3-small | Via OpenRouter |
| Database | PocketBase | Multi-tenant + auth |
| DB replication | Litestream | Real-time SQLite WAL streaming to Hetzner S3 — near-zero RPO |
| Auth | PocketBase native | Email/password (default) + Google OAuth |
| API rate limiting | Flask-Limiter | Protects against queue abuse and runaway scripts |
| Workflow automation | n8n | Self-hosted |
| Frontend | Vite + Vue 3 | Minimal dashboard only — no Nuxt runtime |
| UI component library | Nuxt UI v4 (free, MIT) | Same library as Ledra/AccountFlow |
| Base template | nuxt-ui-templates/dashboard-vue | Clone as starting point |
| Routing | Vue Router v4 | Client-side SPA routing |
| State management | Pinia | Lightweight store |
| Date handling | date-fns | Formatting and computation |
| Validation | Zod | Schema validation |
| Sheet add-on | Google Apps Script | Export integration only — push results from dashboard to Sheet |
| User notifications | In-dashboard activity feed | MVP — price changes, new products, alerts |
| User notifications (future) | Slack / Teams / WhatsApp | Discover tier — webhook integrations |
| Owner monitoring alerts | Gotify + iGotify | Self-hosted — owner phone only, never shown to clients |
| Weekly brief | Resend HTML email | Replaces WeasyPrint — no PDF, no Docker dependency |
| Payments | Stripe | Subscription management + customer portal |
| Support tickets | Freescout (self-hosted) | Email-to-ticket — support@bettersight.io → Freescout |
---
## 4. Architecture
### Service Map
```
Bettersight Dashboard — PRIMARY INTERFACE (app.bettersight.io)
→ PM enters: destination + duration + travel style + selected competitors
→ Flask POST /research/find-urls
→ Firecrawl /map per competitor (on demand, <5s each)
→ URL scoring returns best match per competitor
→ Confirmation screen — PM confirms or swaps any URL
→ PM confirms → Flask POST /research → RQ job queued → job_id returned
→ Dashboard polls /research/status/{job_id} every 3 seconds
→ On-demand check flow per competitor:
Path 1 FAST — last_scraped < 24hrs AND change_detected = false
→ PocketBase structured data → LLM analysis narrative (~seconds)
Path 2 REFRESH — last_scraped > 24hrs OR change_detected = true
→ RQ job → app.py Playwright scrape → PocketBase → LLM (~2 mins)
→ Results displayed in dashboard side-by-side comparison view
→ PM copies rows to clipboard, or opens Sheet + Bettersight sidebar to pull
Apps Script (standalone — Sheet-initiated pull integration)
→ getWorkbookTabs() — returns tab list for the sidebar's own selector
→ pullLatestResults(tabName) — Sheet-initiated fetch from Flask + write
→ validateLicence() — confirms PM is authorised
→ Three functions only. Flask never pushes to the Sheet.
Template setup (one-time per PM, via AccountPage.vue)
→ PM opens hosted template Sheet from AccountPage
→ PM copies Bettersight Analysis tab into their existing workbook
via Google Sheets "Copy to → Existing spreadsheet"
→ PM optionally renames the imported tab to anything they want
→ PM installs standalone Apps Script (binds to Google account, not workbook)
→ From that point: every workbook the PM opens has Extensions →
Bettersight available, and pullLatestResults() writes into whichever
workbook and whichever tab is selected in the sidebar dropdown
Change detection (via daily cron content-hash comparison)
→ n8n daily scrape cron computes SHA-256 of normalised page content
→ Compares against previous scrape's content_hash in scrape_runs
→ If changed: sets competitor.change_detected = true, records new hash
→ If unchanged: skips LLM extraction (fast path knows to use cache)
→ Rationale: replaces Firecrawl Monitor which self-hosted requires
credit accounting + Supabase and only runs on cron anyway. Daily
granularity is appropriate for adventure travel pricing cycles.
n8n (scheduled workflows)
→ Daily cron (6am) — scrapes changed/stale competitors
→ Resets change_detected = false after each scrape
→ PocketBase webhook on price_history insert → creates alert record in PocketBase
→ PocketBase webhook on products insert (is_new=true) → creates alert record in PocketBase
→ n8n reads new alert records → sends Resend email notification (daily digest)
→ Monday 6am → brief_service.py → Resend HTML email
→ [Discover tier] Sunday night → Firecrawl /map scheduled → full catalogue crawl
Vue 3 dashboard auth + account
→ PocketBase auth (Google OAuth or email/password)
→ Stripe (payment, subscription status)
```
### Change Detection — Content-Hash Comparison
Change detection is computed in-process by the scrape service itself,
during the daily scrape cron — not by an external monitoring service.
This replaces both changedetection.io (previously removed) and
Firecrawl Monitor (evaluated and rejected — see Section 4's Compose
Services note and Section 27 "What NOT to Build").
**Why content-hash diff over an external monitor:**
- No dependency on Firecrawl's credit-metered, cron-scheduled v2 Monitor
feature, which requires Supabase auth even in self-hosted mode
- Runs as a normal step of the scrape job already being performed daily
- Renders JS fully before hashing (same Playwright render used for
extraction) — works on React/Vue SPAs
- Daily granularity matches adventure travel pricing update cadence
**PocketBase competitor record flags:**
```
change_detected bool — set true when the scrape service detects a hash diff, reset after next scrape
change_detected_at date — timestamp of last detected change
last_scraped date — timestamp of last completed scrape
```
See Section 10 "Content-hash change detection" for `compute_content_hash()`
and `detect_change()` — the functions that implement this during
`jobs.py`'s scrape job.
### Docker Network
All services on `pangolin_default` network.
Internal service calls use container names, never localhost or host IPs.
```
flask-api → http://flask-api:5000
bettersight-worker → connects to redis://bettersight-redis:6379
bettersight-redis → redis://bettersight-redis:6379
firecrawl → http://firecrawl:3002
pocketbase → http://pocketbase:8090
```
### Compose Services
```yaml
services:
flask-api:
build: ./backend
container_name: flask-api
command: gunicorn app:app -w 4 -b 0.0.0.0:5000
env_file: .env
networks:
- pangolin_default
restart: unless-stopped
depends_on:
- bettersight-redis
bettersight-worker:
build: ./backend
container_name: bettersight-worker
command: rq worker high normal low -u redis://bettersight-redis:6379
env_file: .env
networks:
- pangolin_default
restart: unless-stopped
deploy:
replicas: 3
depends_on:
- bettersight-redis
bettersight-redis:
image: redis:alpine
container_name: bettersight-redis
networks:
- pangolin_default
restart: unless-stopped
```
### Firecrawl — real self-hosted stack
Firecrawl self-hosted is a multi-service application, not a single
container. It needs api + playwright-service + a NUQ Postgres + a
RabbitMQ, and the api service shares the existing `bettersight-redis`
container for rate limiting. No Supabase — that's only required if you
enable the credit/auth system, which Bettersight does not use
(`USE_DB_AUTHENTICATION=false`).
There is no separate worker container. Checked upstream's own
`docker-compose.yaml` — it defines exactly one `api` service, command
`node dist/src/harness.js --start-docker`. That harness process spins
up the queue workers internally (`NUQ_WORKER_COUNT` etc — see
`apps/api/src/harness.ts`) rather than as a separate process. An
earlier version of this section documented a second `firecrawl-worker`
container running `pnpm run workers`, based on an older split-process
Firecrawl architecture — against the current codebase that container
crash-loops immediately: the runtime image's final stage only copies
`node_modules`/`dist`/`native` (see `apps/api/Dockerfile`'s runtime
stage), not `package.json`, so pnpm has no manifest to resolve
`workers` against. Do not reintroduce a second container here.
Firecrawl's queue backend (NUQ) requires a real Postgres, not just
Redis — this only surfaces once the api container actually starts:
without `NUQ_DATABASE_URL` set, `harness.ts`'s `setupNuqPostgres()`
tries to auto-provision a Postgres container by shelling out to
`docker`/`podman` from *inside* the api container itself, which has
neither installed (and isn't meant to — no Docker-in-Docker). Firecrawl
ships a purpose-built Postgres image for this at `apps/nuq-postgres/`
— `pg_cron` preloaded plus the NUQ schema (`nuq.sql`) baked in as an
initdb script — build that rather than pointing at a vanilla postgres
image, which would boot without the schema NUQ expects. Setting
`POSTGRES_HOST` to that container's name (anything other than
`localhost`) is what flips `setupNuqPostgres()` into its
docker-compose branch, which just builds the connection URL directly
and skips the Docker/Podman auto-provision path entirely — this is
the code path Firecrawl's own authors intended for exactly this setup,
not a workaround.
`NUQ_RABBITMQ_URL` is required, not optional, despite most call sites
in the API gracefully checking `if (config.NUQ_RABBITMQ_URL)` and
falling back to polling mode when absent. The harness always spawns
an `extract-worker` subprocess (`dist/src/services/extract-worker.js`)
regardless of which routes are actually used, and that subprocess's
`consumeExtractJobs()` throws immediately without it — no config
check gates whether it starts at all. The harness treats any
subprocess exit as fatal to the whole process, so without RabbitMQ
the entire api container crash-loops every ~2s even though
Bettersight never calls `/extract`. Upstream's own docker-compose.yaml
confirms this — it lists `rabbitmq` as a hard `depends_on: condition:
service_healthy` for the api service. Use the stock
`rabbitmq:3-management` image — no build needed.
Build context must be the app subdirectory itself (`apps/api`,
`apps/playwright-service-ts`, `apps/nuq-postgres`), not the repo root
— each Dockerfile's COPY instructions (e.g. `apps/api/Dockerfile`'s
`COPY sharedLibs/go-html-to-md ...`) are written relative to that
subdirectory, matching upstream firecrawl's own docker-compose.yaml
(`build: apps/api`). Pointing context at the repo root and `dockerfile:`
at the nested path resolves the Dockerfile correctly but still
resolves every COPY inside it against the repo root instead — breaks
on the first COPY that assumes otherwise. Confirmed against a real
build attempt: `context: <repo>` + `dockerfile: apps/api/Dockerfile`
fails with `"/sharedLibs/go-html-to-md": not found`; `context:
<repo>/apps/api` builds correctly. For a remote git context, Docker's
syntax puts the subdirectory after a colon in the fragment
(`#branch:subdirectory`), not as a separate `dockerfile:` field:
```yaml
firecrawl-rabbitmq:
image: rabbitmq:3-management
container_name: firecrawl-rabbitmq
networks:
- pangolin_default
restart: unless-stopped
firecrawl-nuq-postgres:
build:
context: https://github.com/firecrawl/firecrawl.git#main:apps/nuq-postgres
container_name: firecrawl-nuq-postgres
environment:
- POSTGRES_USER=postgres
- POSTGRES_PASSWORD=postgres
- POSTGRES_DB=postgres
networks:
- pangolin_default
restart: unless-stopped
firecrawl-api:
build:
context: https://github.com/firecrawl/firecrawl.git#main:apps/api
container_name: firecrawl-api
environment:
- PORT=3002
- HOST=0.0.0.0
- REDIS_URL=redis://bettersight-redis:6379
- REDIS_RATE_LIMIT_URL=redis://bettersight-redis:6379
- PLAYWRIGHT_MICROSERVICE_URL=http://firecrawl-playwright:3000/scrape
- USE_DB_AUTHENTICATION=false
- NUM_WORKERS_PER_QUEUE=8
- POSTGRES_HOST=firecrawl-nuq-postgres
- POSTGRES_PORT=5432
- POSTGRES_USER=postgres
- POSTGRES_PASSWORD=postgres
- POSTGRES_DB=postgres
- NUQ_RABBITMQ_URL=amqp://firecrawl-rabbitmq:5672
depends_on:
- bettersight-redis
- firecrawl-playwright
- firecrawl-nuq-postgres
- firecrawl-rabbitmq
networks:
- pangolin_default
restart: unless-stopped
firecrawl-playwright:
build:
context: https://github.com/firecrawl/firecrawl.git#main:apps/playwright-service-ts
container_name: firecrawl-playwright
environment:
- PORT=3000
networks:
- pangolin_default
restart: unless-stopped
mem_limit: 4g
```
Resource budget: the playwright-service alone wants ~2-4GB RAM.
The CX31 VPS (2 vCPU, 8GB RAM) sized in Section 31 handles this
alongside PocketBase, Flask, and 3 RQ workers because Firecrawl is
only used for /v1/map calls (trip-intent matching + weekly discovery),
never continuously. Only the api service is called by Bettersight — at
`http://firecrawl-api:3002/v1/map`. playwright-service, nuq-postgres,
and rabbitmq are internal-only.
```yaml
freescout:
image: freescout/freescout:latest
container_name: freescout
environment:
- APP_URL=https://support.bettersight.io
- MAIL_FROM=support@bettersight.io
networks:
- pangolin_default
restart: unless-stopped
# Pangolin routes support.bettersight.io → freescout:80
litestream:
image: litestream/litestream:latest
container_name: litestream
command: replicate
volumes:
- pocketbase_data:/pb_data # same volume as PocketBase
environment:
- LITESTREAM_ACCESS_KEY_ID=${HETZNER_S3_ACCESS_KEY}
- LITESTREAM_SECRET_ACCESS_KEY=${HETZNER_S3_SECRET_KEY}
configs:
- source: litestream_config
target: /etc/litestream.yml
networks:
- pangolin_default
restart: unless-stopped
depends_on:
- pocketbase
# litestream.yml config:
# dbs:
# - path: /pb_data/data.db
# replicas:
# - url: s3://bettersight-backups/litestream
# endpoint: https://fsn1.your-objectstorage.com
networks:
pangolin_default:
external: true
volumes:
pocketbase_data:
```
**Litestream recovery procedure:**
```bash
# Restore PocketBase database from Litestream replica
docker run --rm \
-e LITESTREAM_ACCESS_KEY_ID=${HETZNER_S3_ACCESS_KEY} \
-e LITESTREAM_SECRET_ACCESS_KEY=${HETZNER_S3_SECRET_KEY} \
-v ./pb_data:/pb_data \
litestream/litestream restore \
-o /pb_data/data.db \
s3://bettersight-backups/litestream
```
Recovery point objective (RPO): seconds — not 24 hours.
Recovery time objective (RTO): under 5 minutes from backup restore to running.
---
## 5. Project Structure
```
bettersight/
├── backend/
│ ├── core/
│ │ ├── scraper.py # Playwright stealth browser — never touches industry logic
│ │ ├── extractor.py # OpenRouter model cycling, JSON parsing, fallback chain
│ │ ├── ai_fetch.py # AI web fetch fallback
│ │ └── proxy_service.py # Proxy provider — swap here to change from Bright Data
│ ├── industries/
│ │ └── adventure_travel/
│ │ ├── fields.py # STANDARD_FIELDS, NGS_FIELDS column maps
│ │ ├── prompt.py # build_prompt(), inject_page_content()
│ │ └── schema.py # JSON extraction schema for this vertical
│ ├── config/
│ │ └── client.py # SHEET_ID, tab names, column assignments
│ ├── services/
│ │ ├── licence_service.py # Domain + seat validation (no sheet_id)
│ │ ├── trip_finder_service.py # find_competitor_trip_url(), score_and_rank_urls()
│ │ ├── battlecard_service.py
│ │ ├── embedding_service.py
│ │ ├── brief_service.py # HTML email brief generation
│ │ └── alert_service.py
│ ├── repositories/
│ │ ├── tenant_repository.py
│ │ ├── product_repository.py
│ │ ├── seat_repository.py
│ │ ├── scrape_repository.py
│ │ └── proxy_repository.py
│ ├── templates/
│ │ └── emails/
│ │ ├── welcome.html
│ │ ├── trial_reminder.html
│ │ ├── brief.html
│ │ ├── price_alert.html
│ │ └── new_product.html
│ ├── api.py # Flask routes
│ ├── jobs.py # RQ job functions
│ ├── app.py # Entry point — imports and wires everything
│ ├── requirements.txt
│ └── Dockerfile
├── frontend/ # Vite + Vue 3 + Nuxt UI v4 dashboard
│ ├── src/
│ │ ├── pages/
│ │ │ ├── LoginPage.vue # Auth — email/password + Google OAuth + forgot password
│ │ │ ├── ResetPasswordPage.vue # /reset-password?token=xxx — PocketBase token confirm
│ │ │ ├── AccountPage.vue # Subscription, seats, upgrade prompt, template download
│ │ │ ├── CompetitorsPage.vue # Add/edit competitors with status badges + CSV upload
│ │ │ ├── AnalysePage.vue # CORE PAGE — full analysis workflow
│ │ │ ├── ActivityPage.vue # Market movement feed + alerts
│ │ │ └── BattlecardsPage.vue # Battlecard view per competitor
│ │ ├── components/
│ │ │ ├── DotGrid.vue # Reusable dot grid SVG — signature element
│ │ │ ├── analyse/
│ │ │ │ ├── TripIntentForm.vue # Destination + duration + style + competitor checkboxes
│ │ │ │ ├── UrlConfirmation.vue # Confirm or swap matched URLs before run
│ │ │ │ ├── JobProgress.vue # Per-competitor real-time status during job
│ │ │ │ ├── ResultsTable.vue # Side-by-side competitor comparison
│ │ │ │ ├── ConfidenceBar.vue # Per-competitor confidence indicator
│ │ │ │ ├── SyncToSheet.vue # Copy rows button + pull instruction card
│ │ │ │ └── RunHistory.vue # Last 5 runs with restore option
│ │ │ ├── activity/
│ │ │ │ ├── FeedItem.vue # Single activity feed row with dot grid
│ │ │ │ └── AlertCard.vue # Alert card with dot grid
│ │ │ ├── shared/
│ │ │ │ ├── StatCard.vue # Stat card with accent bar + dot grid
│ │ │ │ ├── SeatManager.vue # Add/remove seats table
│ │ │ │ ├── CompetitorForm.vue # Add/edit competitor modal form
│ │ │ │ ├── BattlecardCard.vue # Single battlecard display
│ │ │ │ ├── SubscriptionCard.vue
│ │ │ │ ├── CsvUpload.vue # File picker + PapaParse + row validation
│ │ │ │ └── ImportPreviewModal.vue # Valid/invalid preview before bulk submit
│ │ ├── composables/
│ │ │ ├── useAuth.js
│ │ │ ├── useTenant.js
│ │ │ ├── useAnalysis.js # Analysis job lifecycle management
│ │ │ ├── useCompetitors.js
│ │ │ └── useOnboardingTour.js # Shepherd.js guided tour — 5 steps
│ │ ├── assets/
│ │ │ └── tour.css # Custom Shepherd.js styles matching design system
│ │ ├── services/
│ │ │ └── api_service.js # All Flask API calls
│ │ ├── repositories/
│ │ │ ├── tenant_repository.js
│ │ │ ├── seat_repository.js
│ │ │ ├── competitor_repository.js
│ │ │ └── analysis_repository.js # Research jobs, history, results
│ │ ├── stores/
│ │ │ ├── auth_store.js # Pinia — JWT, email, tier
│ │ │ └── analysis_store.js # Pinia — active job, results, history
│ │ ├── router/
│ │ │ └── index.js
│ │ └── main.js
│ ├── package.json
│ ├── vite.config.js
│ └── index.html
├── appscript/ # Sheet-initiated pull integration — 3 files
│ ├── Code.gs # onOpen(), install entry point
│ ├── Validation.gs # validateLicence() — email only
│ ├── Sync.gs # pullLatestResults(), getWorkbookTabs(), detectFieldRows_()
│ └── Sidebar.html # Tab selector + pull button + last-pull status
├── n8n/
│ └── workflows/ # n8n workflow JSON exports
├── docker-compose.yml
├── .env.example
└── CLAUDE.md
```
---
## 5b. Frontend Setup — Nuxt UI v4
### Bootstrap
```bash
# Clone the dashboard-vue base template
git clone https://github.com/nuxt-ui-templates/dashboard-vue frontend
cd frontend
npm install
# Additional dependencies
npm install pinia date-fns zod axios
```
### Key dependencies (package.json)
```json
{
"dependencies": {
"@nuxt/ui": "^4.0.0",
"vue": "^3.4.0",
"vue-router": "^4.0.0",
"pinia": "^2.0.0",
"date-fns": "^3.0.0",
"zod": "^3.0.0",
"axios": "^1.6.0"
},
"devDependencies": {
"vite": "^5.0.0",
"@vitejs/plugin-vue": "^5.0.0"
}
}
```
### Nuxt UI v4 component usage
Use Nuxt UI components throughout — never build custom UI primitives.
Key components for this dashboard:
```
UButton — all buttons
UInput — all text inputs
UCard — content containers
UTable — seat management, competitor list
UBadge — subscription status, tier labels
UModal — add competitor, add seat dialogs
UForm — all forms with Zod validation
UAlert — error and success messages
UAvatar — user display
UDropdown — action menus
UDivider — section separators
UContainer — page layout wrapper
USkeleton — loading states
UNotification — toast notifications
```
### Design tokens — align with Ledra/AccountFlow
```javascript
// app.config.ts or equivalent
export default {
ui: {
primary: 'indigo',
gray: 'cool'
}
}
```
### Separation of concerns — frontend
```
Pages (src/pages/)
→ orchestrate composables and repositories
→ NO direct API calls
→ NO business logic
Composables (src/composables/)
→ reusable stateful logic
→ calls repositories
→ NO direct API calls
Repositories (src/repositories/)
→ all API calls via api_service.js
→ returns plain data objects
→ NO component or store references
Stores (src/stores/)
→ global state only (auth, tenant)
→ NO API calls directly
→ calls repositories for data
Services (src/services/)
→ api_service.js is the single Axios instance
→ all endpoints configured here
→ NO business logic
```
### Six pages — full dashboard
**LoginPage.vue** — email/password default + Google OAuth option
**AccountPage.vue**
- Subscription status card (tier, renewal date, seat count, trial countdown)
- **Upgrade prompt card** — shown below subscription for Analyse and Discover tier clients:
```
┌────────────────────────────────────────────────┐
│ 🚀 Upgrade to Discover │
│ │
│ + Weekly catalogue auto-discovery │
│ + Spot new competitor trips automatically │
│ + Slack, Teams and WhatsApp alerts │
│ │
│ $499/month · 3 more seats · 21-day trial │
│ │
│ [Upgrade now →] │
└────────────────────────────────────────────────┘
```
→ [Upgrade now] creates a Stripe checkout session for the next tier
→ On successful upgrade: PocketBase tier updated via Stripe webhook
→ Upgrade card hidden for Intelligence and Enterprise clients
- Template import section — three items:
1. [Open template ↗] button that opens the hosted template Sheet
in a new tab (GET /account/template returns the Drive URL)
2. Two-step import instruction card:
```
Import the Bettersight tab into your workbook:
1. Right-click any tab in the template → Copy to →
Existing spreadsheet → select your workbook
2. Rename the tab to anything you want (optional)
3. Install the Bettersight sidebar (one click):
[Install sidebar ↗]
```
3. Verification helper — after import, PM can paste their workbook
URL into an "It worked" confirmation input which marks
onboarding_checklist.template_imported = true and dismisses this
section. Purely a completion signal — no data leaves the browser.
- Seat management table (add/remove seats)
- Stripe customer portal link (manage billing, cancel)
- Onboarding checklist (tracked in PocketBase)
- "↺ Take the guided tour again" link
**CompetitorsPage.vue**
- Table of active competitors with status badges (✓ Current, ⚠ Changed, ○ Stale)
- Primary market flag per competitor
- Add competitor modal — name, website, catalogue URL, primary market (required)
- Edit/deactivate actions
- [Download CSV template] button — pre-formatted with correct headers
- [Upload CSV] button — bulk import with preview and validation
**AnalysePage.vue — THE CORE PAGE**
- TripIntentForm — destination, duration, travel style, competitor checkboxes
- [Find & Analyse] → POST /research/find-urls → UrlConfirmation screen
- UrlConfirmation — matched URL per competitor, swap any URL, manual entry for no-match
- [Confirm & Run] → POST /research → job_id → polling begins
- JobProgress — per-competitor real-time status (queued / scraping / done / failed)
- ResultsTable — side-by-side competitor comparison on completion
Multi-currency display rule:
→ Show competitor's primary market currency as hero price
(GB competitor = GBP hero, US competitor = USD hero)
→ Always show USD alongside in secondary muted text
→ Format: "£2,190 $2,780 USD"
→ Never currency-convert — use extracted values only
→ If primary market currency not extracted, fall back to USD only
- ConfidenceBar — per-competitor AI confidence with low-confidence warning
- RunHistory — last 5 runs, restore any run to current results view
- SyncToSheet — post-analysis affordance replacing the removed PushToSheet:
→ [Copy rows] button — puts tab-separated results on the clipboard
→ Instruction card below: "Your results are saved. To pull them into
your Sheet: 1) Open your workbook 2) Extensions → Bettersight →
Pull latest results 3) Select the tab to write to."
→ No tab selector on this page — tab selection lives inside the sidebar
where the workbook is actually open
**ActivityPage.vue**
- Stat cards (price changes, drops, new products, competitors tracked)
- Market movement feed — all competitor events this week with dot grid rows
- Alerts panel — unread alerts with badge count in sidebar nav
- Price position progress bars
- Mark as read / dismiss per alert
- Bell icon in topbar shows unread count
**BattlecardsPage.vue**
- Battlecard per competitor — positioning summary, strengths, weaknesses, pricing observation
- Last updated timestamp
- Topic pills (destinations, warnings, signals)
- Comparable trips section (semantic matches)
### tenants
```
id auto
name text
email text (admin email from signup)
domain text (e.g. gadventures.com — used for seat validation)
tier select [analyse, discover, intelligence, enterprise]
status select [trial, active, inactive]
trial_ends_at date (set on signup based on tier trial period)
max_seats integer (set from TIER_SEATS on signup)
timezone text (IANA timezone e.g. Europe/London — captured on signup)
stripe_customer_id text
stripe_subscription_id text
onboarding_checklist json (tracks completion of onboarding steps)
data_region text (default: 'global' — for future GDPR regional routing)
created autodate
```
Note: `sheet_id` binding removed — standalone Apps Script model,
validation is email-only. `monitor` tier removed — dropped entirely.
**Timezone capture on first login (not signup — see note):**
There is no signup form in the dashboard's scope — account creation
happens via the Stripe `checkout.session.completed` webhook or the
manual onboarding runbook (§32), neither of which runs browser JS.
First login is the earliest point `Intl.DateTimeFormat` can actually
run, so that's where this happens instead: `auth_store.js`'s
`fetchTenant()` checks `tenant.timezone` after every `GET /account/me`,
and if it's still empty, captures the browser's timezone and sends it
to `PATCH /account/timezone`. Never asks the PM to select one
manually. `PATCH /account/timezone` never overwrites an
already-captured value — first write wins.
```javascript
// auth_store.js — fires once per tenant, on whichever login is first
const timezone = Intl.DateTimeFormat().resolvedOptions().timeZone
// e.g. "Europe/London", "America/New_York", "Australia/Sydney"
```
### tenant_seats
```
id auto
tenant_id relation → tenants
email text
active bool
last_accessed date
added_by text
created autodate
```
### competitors
```
id auto
tenant_id relation → tenants
name text
website url
catalogue_url url
primary_market text (ISO country code e.g. GB, US, AU — required on add)
change_detected bool (set true by scrape service on content-hash diff, reset after next scrape)
change_detected_at date (timestamp of last detected change)
active bool
last_scraped date
created autodate
```
### products
```
id auto
tenant_id relation → tenants
competitor_id relation → competitors
trip_name text
url url
destination text
duration_days integer
majority_price_usd number
price_low_usd number
price_high_usd number
aud_price number (nullable)
cad_price number (nullable)
eur_price number (nullable)
gbp_price number (nullable)
date_used text
seasonality text
group_size integer
meals integer
service_level text
activities text
start_location text
end_location text
target_audience text
hotels text
comments text
relevancy integer
trip_code text
is_new bool
first_seen autodate
last_seen date
is_active bool
embedding json (vector array for similarity matching)
```
### products_ngs_meta
```
id auto
product_id relation → products
exclusive_access text
group_leader text
sustainability text
```
### price_history
```
id auto
tenant_id relation → tenants
product_id relation → products
majority_price_usd number
price_low_usd number
price_high_usd number
aud_price number (nullable)
cad_price number (nullable)
eur_price number (nullable)
gbp_price number (nullable)
date_used text
change_field text (which price field changed)
change_amount number
change_percent number
scraped_at autodate
```
### scrape_runs
```
id auto (also used as RQ job_id)
tenant_id relation → tenants
competitor_id relation → competitors (nullable for bulk runs)
triggered_by select [cron, webhook, manual, sheet]
status select [pending, running, complete, failed]
competitors_total integer
competitors_done integer
results json
error_log text
created_by_email text (the seat/PM who submitted the run — set once at
creation from the dashboard's auth session, api.py's
research() route; not persisted for cron-triggered
rows. Shown in RunHistory.vue's "last 5 runs" list.)
started_at autodate
completed_at date
```
### scrape_runs (additions for content-hash detection)
Add these fields to the existing scrape_runs collection:
```
content_hash text # SHA-256 of normalised page content
content_length integer # raw byte length of normalised content
hash_algorithm text # "sha256" — allows future migration
```
Indexed via a composite index — `(competitor_id, content_hash, started_at)`
— rather than on content_hash alone, since every lookup is scoped to
one competitor and sorted by recency (`get_last_hash_for_url()`).
An index on content_hash by itself wouldn't serve that query.
The `competitors.change_detected` and `competitors.change_detected_at`
fields already exist — no schema change needed for them. Semantics
change from "set by Firecrawl webhook" to "set by scrape service on
hash diff." No repository or service signature changes.
### alerts
```
id auto
tenant_id relation → tenants
competitor_id relation → competitors
product_id relation → products (nullable)
alert_type select [price_change, new_product, product_removed, new_comparable]
message text
change_amount number (nullable)
change_percent number (nullable)
delivered_dashboard bool (always true — alert always appears in dashboard)
delivered_email bool (set true when included in daily digest email)
delivered_slack bool (Discover tier — set true when Slack webhook fires)
delivered_teams bool (Discover tier — set true when Teams webhook fires)
delivered_whatsapp bool (Discover tier — set true when WhatsApp fires)
read bool (PM marks as read in dashboard)
created autodate
```
### battlecards
```
id auto
tenant_id relation → tenants
competitor_id relation → competitors
content text (AI generated battlecard text)
content_json json (structured: top_routes, avg_price, positioning, strengths, weaknesses)
generated_at autodate
```
### comparable_matches
```
id auto
tenant_id relation → tenants
client_product text (client trip name — not a PocketBase relation)
competitor_product relation → products
similarity_score number
price_difference number
duration_difference integer
first_matched autodate
last_matched date
dismissed bool
```
### tenant_notifications
```
id auto
tenant_id relation → tenants
slack_webhook_url text (nullable — Discover tier only)
teams_webhook_url text (nullable — Discover tier only)
whatsapp_number text (nullable — Discover tier only)
email_digest bool (default true — daily digest email)
email_digest_time text (default "18:00" — local time in tenant timezone)
created autodate
```
**Timezone-aware digest scheduling in n8n:**
```
Daily digest n8n workflow:
→ Query all active tenants with email_digest = true
→ For each tenant:
Convert 18:00 tenant local time → UTC using tenants.timezone
If current UTC time matches → send digest for this tenant
→ Each tenant receives their digest at 6pm their local time
```
n8n Code node timezone conversion:
```javascript
// n8n Code node — per tenant digest time calculation
const { DateTime } = require('luxon')
const localTime = '18:00'
const tenantTz = tenant.timezone || 'UTC'
const sendAt = DateTime.fromFormat(localTime, 'HH:mm', { zone: tenantTz })
.toUTC()
// Compare sendAt.hour and sendAt.minute against current UTC time
```
Implemented in `n8n/workflows/daily_digest.json` — runs hourly (not
once daily at a fixed UTC hour), comparing only `.hour` since
`email_digest_time` is minute-granular but the schedule itself isn't.
`email_digest`'s "default true" isn't a PocketBase-level field
default (the migration never configured one) — it's handled in the
Code node instead: a tenant with no `tenant_notifications` row at all
is treated as enabled at the default 18:00, since nothing in this
codebase creates that row yet. If something later creates a
`tenant_notifications` row without explicitly setting `email_digest`,
PocketBase's bool zero-value (`false`) would silently disable digests
for that tenant — always set it explicitly on creation.
### digest_logs
```
id auto
tenant_id relation → tenants
digest_type select [weekly, monthly]
pdf_path text
sent_at autodate
recipient_email text
status select [sent, failed]
```
### proxy_overrides
```
id auto
domain text (e.g. intrepidtravel.com)
provider select [webshare, brightdata]
blocked_count integer
last_blocked date
created autodate
```
### client_trips
```
id auto
tenant_id relation → tenants
trip_name text
destination text
duration_days integer
price_usd number
activities text
embedding json
created autodate
```
---
## 7. On-Demand Analysis — Check Flow
This is the core data flow for every on-demand analysis request from the Sheet.
It determines whether to serve from PocketBase (fast) or trigger a live scrape (slow).
The PM always gets fresh, relevant data. The system decides the path invisibly.
```
PM triggers analysis in Sheet sidebar
│
▼
For each selected competitor:
│
├── Check PocketBase competitor record
│ last_scraped < 24hrs? AND change_detected = false?
│ │
│ ├── YES → FAST PATH (~seconds)
│ │ Pull structured data from PocketBase products table
│ │ Pass JSON to LLM for analysis narrative only (no extraction)
│ │ Write results to Sheet
│ │ Show: ✅ Current (updated {time})
│ │
│ └── NO → REFRESH PATH (~2 mins)
│ last_scraped > 24hrs OR change_detected = true
│ Trigger RQ job → Playwright scrape → AI extraction
│ Update PocketBase (products, price_history)
│ Reset change_detected = false
│ Update last_scraped = now
│ Pass fresh data to LLM for analysis narrative
│ Write results to Sheet
│ Show: 🔄 Refreshed just now
│
▼
Sheet sidebar status indicators:
✅ Current (updated today 6:02am) — fast path served
⚠️ Change detected (2hrs ago) — triggers refresh automatically
🔄 Refreshing... — slow path in progress
✓ Complete — results written to Sheet
```
### Fast path — LLM role
In the fast path the LLM does NOT extract data — it analyses it.
PocketBase already has structured JSON. The LLM receives that JSON
and generates the analysis narrative, comments, and battlecard content only.
The returned dict must still carry the cached trip/pricing fields
(tripName, majorityPrice, duration, gbpPrice, etc.) alongside the fresh
narrative — the LLM call itself is constrained to return only
`{comments, relevancy}`, and those two get merged into the cached
fields, not returned as the entire result. A real "complete" job with
an empty-looking dashboard results table surfaced why this matters: an
earlier version returned the LLM's raw output as the whole result, and
since that prompt only ever asked for a narrative with no schema, the
model invented its own ad-hoc JSON shape on every call (a bare
`{"analysis": "..."}`, sometimes with extra invented keys) — never the
`tripName`/`majorityPrice`/`gbpPrice`/`duration`/`comments` shape
`ResultsTable.vue` actually renders. The fast path's whole point is to
reuse already-extracted data, not have the LLM re-derive (or
mis-derive) it.
```python
def analyse_from_cache(tenant_id: str, competitor_id: str, context: dict) -> dict:
"""
Fast path analysis — uses cached PocketBase data instead of live scrape.
LLM receives structured JSON and generates narrative analysis only.
Data flow:
competitor_id → PocketBase products (latest) →
structured fields mapped from the cached record (trip_name →
tripName, majority_price_usd → majorityPrice, etc.) →
client trip context → LLM prompt constrained to
{comments, relevancy} only →
cached structured fields + fresh comments/relevancy merged and
returned
"""
```
### Staleness threshold
24 hours is the default staleness threshold.
This is a configurable constant — adjust if client feedback indicates
they need fresher data or if server load requires a longer window:
```python
CACHE_STALENESS_HOURS = 24 # in core/scraper.py — adjust here only
```
### PM-initiated cache bypass — `force_refresh`
A collapsed "Advanced options" disclosure on `TripIntentForm.vue` (closed
by default, not a prominent field) exposes a single "Force fresh scrape"
checkbox, unchecked by default. Unchecked, a run respects the normal
fast/refresh-path decision (§7) — a competitor scraped recently is served
from cache quickly. When checked, `force_refresh: true` rides through the
`/research` POST body → `jobs.py`'s per-competitor `context` dict →
`analysis_service.run_competitor_analysis()`, bypassing BOTH cache
shortcuts for that run: the initial fast-path gate (`needs_refresh()`) and
the post-scrape "content unchanged, reuse cached narrative" shortcut — so
a forced run always ends in a real `extract_with_openrouter()` call,
never `analyse_from_cache()`. Content-hash bookkeeping (`scrape_runs` row,
`change_detected` clearing) still runs normally even when forced, so
future non-forced runs keep an accurate hash history. Not persisted on
the `scrape_runs` job record — it's a one-time execution-time override
with no restore/display use case, unlike `tab_type`.
This briefly defaulted to fully-fresh (checkbox inverted to an opt-in
"prefer cache") while a real bug was being tracked down: the confirmed/
matched trip URL was silently discarded (see below), so cached data could
be for the *wrong page entirely*, not just stale. Once that was fixed, the
cache reflects the correct trip page, so the original opt-in design (fast
by default, force-fresh as the explicit override) is trustworthy again and
was reverted to.
The daily cron (`/internal/scrape/<tenant_id>`, `triggered_by='cron'`)
builds its own request body with no `force_refresh` key and no trip-intent
fields at all — `jobs.py`'s `data.get('force_refresh', False)` naturally
stays `False` there regardless of the dashboard's default, so the cron's
caching efficiency is untouched by this.
### Confirmed/matched URL actually reaches the scrape
`context['confirmed_url']` — the page confirmed at `UrlConfirmation.vue`
(a Firecrawl `/map` match or a manual swap), threaded per-competitor
through `jobs.py`'s loop — is what `run_competitor_analysis()` actually
scrapes, falling back to `competitor['website']` (the stored root domain)
only when nothing was confirmed for this run (e.g. the cron path, which
has no trip-intent context). This was a real regression fixed in this
session: `jobs.py` previously only ever read `entry['id']` from each
confirmed competitor, silently discarding `entry['url']` — every scrape
fell back to the competitor's bare root domain regardless of what was
matched or manually swapped, which is why trip-intent matching (§8)
appeared to have no effect on real analysis runs.
### Generic-homepage detection — `is_generic_page`
`services/url_utils.py`'s `is_generic_homepage(url)` flags a URL with no
meaningful path (the bare root domain, with or without a trailing slash,
query string, or `www` prefix) — used in two places:
- `score_and_rank_urls()` (§8) excludes bare-root candidates before
scoring, the same hard-gate treatment as the destination-keyword check —
a competitor whose only Firecrawl `/map` candidate is its own homepage
correctly falls through to `found: False` rather than a false positive.
- `run_competitor_analysis()` computes `is_generic_page` from whichever URL
was actually scraped this run (or the cached record's own URL, on the
fast path) and returns it as a top-level sibling of `path`/`result` —
not nested inside `result`, since it's a meta-fact about the scrape, not
an extracted trip field. `ResultsTable.vue` renders a "⚠ Generic page"
badge in the competitor's column header when set.
`UrlConfirmation.vue` also gained a per-competitor "Skip this competitor"
toggle, scoped to the `found: false` case — a PM can now exclude a
competitor with no real match from the batch entirely, instead of being
pressured into typing a low-value guess (like the competitor's own
homepage) just to unblock "Confirm & Run."
### ResultsTable.vue — clickable links, cache-source pill, own-trip column
- The `link` row (every column, including the own-trip column below) now
renders as a real `<a target="_blank" rel="noopener">` instead of
plain text, wherever a value is present — opens the actual scraped
page in a new tab.
- Every column header shows a subtle blue database SVG icon next to the
name whenever this run's data came from PocketBase (`path === 'fast'`
or `unchanged: true`) — a fresh live scrape gets no icon at all, since
that's the default expectation and doesn't need flagging. Gives the PM
direct, per-competitor visibility into which path §7's fast/refresh
decision actually took, rather than having to infer it. Went through
two iterations: first a text badge ("From PocketBase"), which was too
noisy and also got silently clipped on any column whose name+badge
combination didn't fit the shared `nowrap` + `text-overflow: ellipsis`
rule (only ever appeared on whichever competitor had the shortest
name) — replaced with an icon-only indicator, and header cells now
wrap (name renders in its own `div`) instead of truncating.
- A dedicated "🏠 {tenant name}" column renders BEFORE the first
competitor column whenever `context['own_trip']` was populated this
run (client self-scrape feature, §8's trip-intent matching applied to
the tenant's own site) — same row layout, same cache pill (based on
`own_trip.cached`, set by `embedding_service.scrape_own_trip()`), same
clickable link. Omitted entirely when no own-trip URL was confirmed
this run (no website configured, or explicitly skipped) — this was
previously invisible in the dashboard even when the feature was
working, since `own_trip` was only ever consumed internally by
`build_prompt()` and never returned to the frontend at all.
`jobs.py`'s `run_research_job()` now includes it as
`results['own_trip']` on the persisted `scrape_runs` record, so it
also survives into RunHistory restores.
- The `comments` row label was hardcoded to `"Suggested changes for
G/ Competitive Comments"` — a leftover literal "G" from "G Adventures"
(the hackathon reference client), showing every tenant's own table as
if it belonged to a company literally named "G". `resultFields.js`'s
label is now `"Suggested changes for {company}/ Competitive
Comments"`, substituted at render time via `formatLabel(label,
companyName)` — used by both `ResultsTable.vue` (display) and
`SyncToSheet.vue` (clipboard copy) via a new `companyName` prop each,
wired from `AnalysePage.vue`'s `authStore.tenant?.name`.
- `analyse_from_cache()`'s (§7 fast path) narrative prompt used to dump
the raw `context` dict (Python repr) into the prompt with no priority
framing — `own_trip`'s presence was easy for the model to under-weight
against the typed destination/duration/style fields sitting right next
to it in the same blob. It now explicitly builds a `client_context_desc`
string that's grounded in `context['own_trip']` (the tenant's own REAL
extracted trip) FIRST when present, falling back to the PM's typed
form fields only when no own-trip data was returned for this run —
matching `build_prompt()`'s existing own_trip-first priority on the
refresh path, so both paths ground `comments` the same way.
- The `comments` cell bolds every comparison-section label
`build_prompt()`'s own JSON schema instruction asks the LLM for —
"(Key) Differences:", "Strength(s):", "Weakness(es):", "Pricing
(Position):" (case-insensitive) — wherever they occur in every
column's comments text, so a PM can skim multiple competitors' analysis
at a glance. Includes the own-trip column, though that one is always
blank since `build_own_trip_prompt()` never asks for a `comments`
field (nothing to compare against at extraction time). `ResultsTable.vue`'s
`boldKeywords()` HTML-escapes the text first, then re-inserts only its
own `<strong>` tags around an exact keyword match, so `v-html` can't be
used to inject arbitrary markup even though the underlying text
originates from an LLM extraction. Purely a display change —
`SyncToSheet.vue`'s clipboard copy (plain text, pasted into the real
Sheet template) is untouched.
---
## 8. Trip-Intent Matching
### Overview
The PM never manually enters a competitor trip URL. They describe their
own product — destination, duration, travel style — and the system finds
the best matching trip page on each competitor site automatically via
Firecrawl /map with scoring.
This feature is available in the **Analyse tier** (on demand, per job).
The **Discover tier** extends this with a scheduled weekly crawl.
### Dashboard Flow
```
PM fills in the Analyse form:
Product name: Inca Trail Classic
Destination: Peru
Duration: 15 days
Travel style: Classic
Competitors: ☑ Intrepid ☑ Exodus ☑ Flash Pack ☑ Kimkim
PM clicks [Find & Analyse]
→ Flask POST /research/find-urls
→ Firecrawl /map runs per competitor simultaneously (async)
→ URL scoring selects best match per competitor
Confirmation screen shown in dashboard:
Intrepid Travel → intrepidtravel.com/peru-classic-15-days [✓ Use] [↗ Swap]
Exodus Travels → exodustravels.com/machu-picchu-trek [✓ Use] [↗ Swap]
Flash Pack → flashpack.com/peru-adventure-15d [✓ Use] [↗ Swap]
Kimkim → No match found [Enter URL manually]
PM confirms (or swaps any URL)
→ Flask POST /research with confirmed URLs
→ RQ job queued → analysis runs
→ Results displayed in dashboard
```
### Flask Route
```python
@app.route('/research/find-urls', methods=['POST'])
def find_urls():
"""
Finds the best matching competitor trip URL for each selected
competitor given the PM's research intent (destination, duration,
travel style). Runs Firecrawl /map per competitor concurrently.
Body: {
destination: str,
duration: int,
travel_style: str,
competitor_ids: list[str]
}
Returns: {
matches: [
{ competitor_id, competitor_name, url, confidence, found }
]
}
Data flow:
competitor_ids → PocketBase (get root URLs per competitor) →
Firecrawl /map per competitor (concurrent, ThreadPoolExecutor) →
score_and_rank_urls() per competitor →
return best match per competitor with confidence score
"""
```
### URL Finding Service — `services/trip_finder_service.py`
```python
def find_competitor_trip_url(root_url: str, destination: str,
duration: int, travel_style: str) -> dict:
"""
Uses Firecrawl /map with search terms to find the most likely
trip page URL on a competitor site matching the PM's research intent.
Data flow:
root_url + search terms → Firecrawl /map →
filtered URL list → score_and_rank_urls() →
{ url, confidence, found } returned
"""
response = requests.post(
"http://firecrawl:3002/v1/map",
json={
"url": root_url,
"search": f"{destination} {duration} days {travel_style} tour"
},
timeout=15
)
urls = response.json().get("links", [])
if not urls:
return {"url": None, "confidence": 0, "found": False}
return score_and_rank_urls(urls, destination, duration)
def score_and_rank_urls(urls: list, destination: str,
duration: int) -> dict:
"""
Scores candidate URLs by keyword match against destination
and duration. Returns highest scoring URL with confidence score.
A URL must contain at least one destination keyword to be considered
at all — this is a hard gate, not an additive bonus. Regression found
in production: a URL for a completely different country could still
cross the found threshold purely from a duration-digit match plus a
generic category-keyword match (a Turkey trip page scored 0.75
confidence against a Peru search just because it happened to be a
"9 day classic" tour) — category/duration signals only ever score
URLs that pass the destination gate, they can never compensate for
failing it.
A bare root-domain URL (no path at all — see services/url_utils.py's
is_generic_homepage()) is excluded the same way: Firecrawl's /map
candidate list commonly includes the site's own root URL, and if the
destination keyword happens to appear in the domain itself (e.g. a
competitor literally named "peru-travel.com"), the root URL could
otherwise score high enough to be returned as a confident "found"
match — silently presenting a homepage as if it were a trip-specific
page.
Scoring rules (applied only to URLs that pass both gates):
+1 per destination keyword found in URL
+2 if duration (as integer) appears in URL
+1 if travel category words appear (classic, trek, trail, etc.)
Confidence = top_score / max_possible_score, capped at 1.0
If no URL passes both gates, or confidence < 0.3,
returns found=False — prompt manual entry.
"""
dest_keywords = destination.lower().split()
category_keywords = ['classic', 'trek', 'trail', 'safari',
'adventure', 'explorer', 'journey']
scores = []
for url in urls:
if is_generic_homepage(url):
continue # bare root domain — never a trip-specific candidate
url_lower = url.lower()
dest_matches = sum(1 for kw in dest_keywords if kw in url_lower)
if dest_matches == 0:
continue # zero destination relevance — never a candidate
score = dest_matches
if str(duration) in url_lower:
score += 2
score += sum(1 for kw in category_keywords if kw in url_lower)
scores.append((score, url))
scores.sort(reverse=True)
if not scores:
return {"url": None, "confidence": 0, "found": False}
top_score, top_url = scores[0]
max_possible = len(dest_keywords) + 2 + 1
confidence = min(top_score / max_possible, 1.0)
return {
"url": top_url,
"confidence": round(confidence, 2),
"found": confidence >= 0.3
}
```
### Concurrent Execution
Run /map calls for all competitors simultaneously — not sequentially:
```python
from concurrent.futures import ThreadPoolExecutor, as_completed
def find_all_competitor_urls(competitors: list, destination: str,
duration: int, travel_style: str) -> list:
"""
Runs trip URL finding for all competitors concurrently.
Max 5 concurrent Firecrawl /map calls — respects server limits.
Data flow:
competitors list → ThreadPoolExecutor (max 5) →
find_competitor_trip_url() per competitor in parallel →
collect results → return matches list
"""
results = []
with ThreadPoolExecutor(max_workers=5) as executor:
futures = {
executor.submit(
find_competitor_trip_url,
c['website'], destination, duration, travel_style
): c for c in competitors
}
for future in as_completed(futures):
competitor = futures[future]
match = future.result()
results.append({
"competitor_id": competitor['id'],
"competitor_name": competitor['name'],
**match
})
return results
```
---
## 9. API Routes
All routes require `X-API-Key` header matching `API_SECRET_KEY` env var
except `/health` and `/stripe/webhook`.
### Licence and Auth
```
POST /validate-email
Body: { email }
Returns: { valid, tier, reason }
Logic: domain check → seat count → seat registration
Note: sheet_id binding removed — standalone script model
POST /stripe/webhook
Stripe signature verified via STRIPE_WEBHOOK_SECRET
Creates/updates tenant on payment events
Sets status=inactive on cancellation
```
### Trip-Intent Matching + Research
```
POST /research/find-urls
Body: { destination, duration, travel_style, competitor_ids }
Returns: { matches: [ { competitor_id, name, url, confidence, found } ] }
Runs Firecrawl /map concurrently per competitor
POST /research
Body: { tenant_id, destination, duration, travel_style, tab_type,
competitors: [ { id, name, url (confirmed) } ],
prompt_template? }
Returns: { job_id, status: "queued" }
Enqueues to RQ — never runs synchronously
GET /research/status/<job_id>
Returns: { status, progress, results, error }
GET /research/history
Query: email, limit (default 5)
Returns: last N runs for tenant
GET /research/history/latest
Query: email
Returns: full stored results for the PM's most recent completed job
Used by Apps Script sidebar for [Pull latest results]
GET /research/history/<job_id>
Returns: full stored results for a specific run
POST /preview-prompt
Returns: { prompt } — dry run, no scraping
```
### Dashboard Data
```
GET /competitors
Header: X-User-Email
Returns: [ { id, name, url, primary_market, last_scraped,
change_detected, status } ]
POST /battlecards
Body: { email }
Returns: [ { competitor_name, content, content_json, generated_at } ]
POST /comparable-trips
Body: { email }
Returns: [ { client_trip, competitor_trip, competitor,
similarity_score, price_difference, duration_difference } ]
```
### Apps Script Sheet Sync
No dedicated routes. The Sheet-initiated pull uses the existing
research history endpoints:
```
GET /research/history/latest?email={email}
Returns: full stored results for the PM's most recent completed job
Called by the Apps Script sidebar's [Pull latest results] button
See Section 15 — Google Apps Script for the full pull flow
GET /research/history/<job_id>
Returns: full stored results for a specific run (used by dashboard
history panel — see Trip-Intent Matching + Research routes above)
```
Rationale: A stateless Flask backend has no way to reach into whichever
Sheet the PM currently has open. Only the Apps Script itself, running
inside that open Sheet, can write to it. So the Sheet pulls from Flask
rather than Flask pushing to the Sheet. `getWorkbookTabs()` runs inside
the script and needs no Flask counterpart.
### Dashboard Account
```
GET /account/<tenant_id>
POST /account/seats
DELETE /account/seats/<seat_id>
POST /account/competitors — add single competitor (requires: name, website,
catalogue_url, primary_market)
POST /account/competitors/bulk — bulk import from CSV (validates, previews, imports)
GET /account/competitors/template — download blank CSV template with correct headers
PUT /account/competitors/<id>
DELETE /account/competitors/<id> — deactivates the competitor record
GET /account/template
PATCH /account/onboarding — update onboarding_checklist fields
PATCH /account/timezone — captures browser timezone on first login,
never overwrites an existing value
GET /billing/portal — Stripe customer portal session URL
POST /billing/upgrade — creates Stripe checkout for tier upgrade
Body: { tenant_id, target_tier }
Returns: { checkout_url }
POST /auth/request-password-reset — Body: { email } → triggers PocketBase reset email
POST /auth/confirm-password-reset — Body: { token, password, passwordConfirm }
```
### Internal (n8n calls)
```
POST /internal/scrape/<tenant_id>
POST /internal/battlecard/<competitor_id>
POST /internal/embed-products/<tenant_id>
POST /internal/match-comparable/<tenant_id>
POST /internal/weekly-brief/<tenant_id>
POST /internal/daily-digest/<tenant_id>
POST /internal/welcome-email/<tenant_id>
POST /internal/data-cleanup — global, not per-tenant (§22 DATA_RETENTION)
POST /internal/discover/<tenant_id> — Discover tier only
```
### System
```
GET /health — returns { status: "ok", version }
```
---
## 10. Concurrency — RQ Job Queue
The `/research` endpoint NEVER runs synchronously.
Every research job is enqueued to RQ and processed by background workers.
### Queue Priority
```python
# Three priority queues — Intelligence tier gets processed first
high_q = Queue('high', connection=redis_conn) # Intelligence tier
normal_q = Queue('normal', connection=redis_conn) # Discover + Analyse
low_q = Queue('low', connection=redis_conn) # background tasks
# Worker consumes in priority order
# command: rq worker high normal low -u redis://bettersight-redis:6379
```
### Job Function (jobs.py)
The job function calls scrape and extract functions from `core/scraper.py`
and `core/extractor.py`. It adds PocketBase status updates and the
fast/slow path check around the existing logic.
When running as an RQ job, `jobs.py` imports from `core/scraper.py`
and `core/extractor.py`. Never bypass the layer structure.
### Content-hash change detection
Every scrape job computes a content hash and compares against the last
recorded hash for the same competitor URL. This replaces Firecrawl
Monitor's role in the original design.
```python
import hashlib
def compute_content_hash(page_html: str) -> str:
"""
Returns SHA-256 hash of normalised page content.
Normalisation strips whitespace, script blocks, and known
ephemeral elements (timestamps, session IDs) so cosmetic changes
do not trigger false positives.
"""
normalised = normalise_html(page_html)
return hashlib.sha256(normalised.encode('utf-8')).hexdigest()
def detect_change(competitor_id: str, new_hash: str) -> bool:
"""
Compares new_hash against last recorded hash on scrape_runs.
On change: sets competitor.change_detected = true,
competitor.change_detected_at = now. Never clears the flag itself —
detection and extraction happen in the same scrape now (unlike the
old async Firecrawl Monitor webhook), so there's no "detected but
not yet handled" state for this function to resolve. Clearing is
the caller's responsibility — see below.
"""
last = scrape_repository.get_last_hash_for_url(competitor_id)
if last is None:
return True # first scrape — always process
if last != new_hash:
competitor_repository.update(competitor_id, {
'change_detected': True,
'change_detected_at': datetime.now().isoformat()
})
return True
return False
```
**`change_detected` stays visible until confirmed stable, not until "handled."**
The scrape that finds a change also fully extracts and saves it — so
there's no later "handled" event to reset the flag on. The caller
(`analysis_service.run_competitor_analysis()`) does NOT reset it after
a successful extraction; it leaves the flag as `detect_change()` set
it, so the CompetitorsPage "⚠ Changed" badge, the Apps Script sidebar
status, and the next day's n8n cron filter (`change_detected = true ||
stale`) can all still see it. It's only cleared on the *next* scrape
that finds the content unchanged since — at most one extra hash-only
confirmation scrape per genuine change, with no LLM cost since an
unchanged result always skips extraction.
An earlier version of this design reset `change_detected` to false
immediately after every successful extraction, in the same function
call that `detect_change()` set it — meaning nothing outside that one
call could ever observe it as true. That silently broke the status
badge and the cron filter's `change_detected = true` clause. Do not
reintroduce that reset.
Fields on `scrape_runs` collection gain: `content_hash`, `content_length`,
`hash_algorithm` — see Section 6's "scrape_runs (additions for
content-hash detection)" for the composite index that backs
`get_last_hash_for_url()`'s competitor-scoped, recency-sorted lookup.
---
## 9. Licence Validation Logic
### Three layers — all must pass
**Layer 1 — Domain check**
Extract domain from email. Look up active tenant with matching domain in PocketBase.
If no match → reject with "Domain not registered".
**Layer 2 — Seat count**
Check if email already has an active seat for this tenant.
If yes → update last_accessed, allow.
If no → count active seats. If at limit → reject with "Seat limit reached".
If under limit → register new seat, allow.
**Layer 3 — Sheet ID binding**
On first validation for a tenant, store the sheet_id in the tenants record.
On subsequent validations, compare sheet_id. If mismatch → reject with
"Licence bound to a different Sheet. Contact support."
### Cache
Apps Script caches validation result for 6 hours via CacheService.
Cache key: `BETTERSIGHT_VALID` and `BETTERSIGHT_TIER`.
On cache hit — skip API call entirely.
---
## 10. Scraping Architecture
### Refactor app.py Before New Development
The existing `app.py` must be refactored into the modular three-layer
structure before any new development begins. Logic stays identical —
it is reorganised into correct layers, not rewritten.
**Core IP preserved in `core/`:**
- Playwright stealth browser with cookie consent handling and lazy load scrolling
- BeautifulSoup HTML cleaning
- AI web fetch fallback via OpenRouter for bot-protected sites
- OpenRouter model cycling with fallback chain
- Multi-currency extraction
**Industry config moved to `industries/adventure_travel/`:**
- NGS vs Standard field differentiation
- Prompt builder and injection
- JSON extraction schema
**Client config moved to `config/client.py`:**
- Google Sheet write-back via gspread
- Sheet ID, tab names, column assignments
### Modular Scraping Architecture
app.py must be refactored into a three-layer modular structure before
any new development begins. This separates infrastructure from industry
config from client config — making the codebase maintainable, testable,
and extensible to other industries without touching core scraping logic.
**Target folder structure:**
```
backend/
core/
scraper.py — Playwright browser setup, stealth, waiting, HTML cleaning
extractor.py — OpenRouter model cycling, JSON parsing, fallback chain
ai_fetch.py — AI web fetch fallback (existing fetch_with_ai())
industries/
adventure_travel/
fields.py — STANDARD_FIELDS, NGS_FIELDS column maps
prompt.py — build_prompt(), inject_page_content()
schema.py — JSON extraction schema for this vertical
# future verticals added here — never touch core/
config/
client.py — SHEET_ID, tab names, column assignments per client
jobs.py — RQ job functions — imports from core/ and industries/
api.py — Flask routes
app.py — Entry point only — imports and wires everything together
```
**The three layers:**
Layer 1 — `core/` — never changes between industries or clients
- Playwright stealth browser config
- Smart waiting and scroll logic
- Cookie consent handling
- HTML cleaning via BeautifulSoup
- OpenRouter model cycling and JSON parsing
- AI web fetch fallback
Layer 2 — `industries/{vertical}/` — swapped per vertical
- Field maps (column positions in the Sheet)
- Prompt template and context ("you are a travel industry analyst")
- JSON extraction schema (what fields to extract)
- Tab names
Layer 3 — `config/client.py` — swapped per client within a vertical
- Sheet ID
- Column assignments per competitor
- Competitor DB tab structure
**To pivot to a new industry:**
Create a new folder under `industries/` with three files.
Core scraping logic is never touched.
Sheet template changes are driven entirely by the field map.
### playwright-stealth Integration
Replace the manual `add_init_script` block in `core/scraper.py` with
`playwright-stealth` — a comprehensive stealth library applying 20+
evasion techniques automatically.
```
pip install playwright-stealth
```
In `core/scraper.py` — replace the existing `add_init_script` call:
```python
from playwright_stealth import stealth_async
async def render_page(url: str) -> dict:
async with async_playwright() as p:
browser = await p.chromium.launch(...)
context = await browser.new_context(...)
page = await context.new_page()
# Replaces the manual add_init_script block entirely
await stealth_async(page)
# Rest of existing code unchanged
await page.goto(url, ...)
```
`stealth_async` handles: navigator.webdriver spoofing, plugins array,
languages, chrome runtime, canvas fingerprint, WebGL masking, audio
context randomisation, and ~15 additional evasion techniques that the
manual script did not cover.
### Smart Proxy Service — `core/proxy_service.py`
The proxy layer is a standalone service in `core/proxy_service.py`.
It is the ONLY place in the codebase that knows about any proxy provider.
Switching providers or adjusting the switching logic requires changing only this file.
**Two providers — smart auto-switching:**
- **Primary:** Webshare (~$3/month flat, free tier available)
- **Fallback:** Bright Data pay-as-you-go (~$4-8/GB, only used when Webshare is blocked)
At MVP scraping scale (6 competitors × 5 clients = 30 pages/day) total proxy
cost is under $5/month. Bright Data fallback costs pennies at this volume.
**PocketBase collection — `proxy_overrides`**
```
id auto
tenant_id relation → tenants (nullable — global overrides have no tenant)
domain text (e.g. intrepidtravel.com)
provider select [webshare, brightdata]
blocked_count integer
last_blocked date
created autodate
```
**ProxyService class:**
```python
class ProxyService:
"""
Smart proxy provider — uses Webshare by default with country targeting,
auto-escalates to Bright Data for domains that block Webshare.
Country targeting uses competitors.primary_market (ISO code e.g. GB, US, AU)
passed through from the competitor record — no hardcoded country map in code.
Data flow (first request to a domain):
domain + country → proxy_overrides lookup → no override found →
Webshare config with country targeting returned →
scraper attempts request → if blocked →
escalate_to_brightdata(domain) called →
Bright Data config returned → scraper retries →
override stored in PocketBase for this domain
Data flow (subsequent requests to a known blocked domain):
domain + country → proxy_overrides lookup → brightdata override found →
Bright Data config returned immediately — no Webshare attempt
Data flow (specific-IP candidate rotation — before ever escalating to
Bright Data): domain not already known-blocked → the account's own
dedicated Webshare IPs for this country fetched (cached) via
get_webshare_ip_candidates() → core/scraper.py's render_page() tries a
handful of those specific IPs in turn → only once all of them are
exhausted (or none were available) does it fall through to the single
`-rotate` gateway attempt and then Bright Data, as before.
"""
def __init__(self):
# In-memory cache of the account's dedicated Webshare IPs, keyed by
# uppercased country code — {'expires_at': float, 'results': [...]}.
# Same time.time()-based pattern as pocketbase_client.py's token cache.
self._ip_cache = {}
def is_domain_blocked_on_webshare(self, domain: str) -> bool:
"""
Returns True if this domain already has a brightdata override —
lets render_page() skip the specific-IP candidate loop entirely for
a domain already established as Bright-Data-only.
"""
override = proxy_repository.get_by_domain(domain)
return bool(override and override.get('provider') == 'brightdata')
def get_webshare_ip_candidates(self, country: str, limit: int = WEBSHARE_IP_CANDIDATE_LIMIT) -> list:
"""
Returns up to `limit` Playwright proxy configs for specific
dedicated Webshare IPs assigned to this account, filtered to the
target country. Returns [] if none are available (no country,
API failure, or no results) — render_page() treats an empty list
as "skip straight to the existing single-gateway attempt".
Each candidate carries its OWN username/password/port from
Webshare's proxy-list API response — different from
_webshare_playwright()'s single account-level rotating credentials.
"""
if not country:
return []
results = self._fetch_webshare_ip_list(country)
if not results:
return []
chosen = random.sample(results, min(limit, len(results)))
return [
{"server": f"http://{ip['proxy_address']}:{ip['port']}",
"username": ip['username'], "password": ip['password']}
for ip in chosen
]
def _fetch_webshare_ip_list(self, country: str) -> list:
"""
Fetches (and caches) the account's own dedicated Webshare IPs for a
country via Webshare's proxy-list *management* API
(proxy.webshare.io/api/v2/proxy/list/) — a different host and
credential entirely from the proxy gateway (p.webshare.io +
WEBSHARE_USER/PASS). Authenticated with a dashboard API token
(WEBSHARE_API_KEY, "Authorization: Token <key>"), and — per
Webshare's own docs — wants the country code UPPERCASE (their
example: "FR,US"). This is the opposite case convention from
_webshare_playwright()'s gateway username suffix (lowercase) — two
unrelated Webshare systems with inconsistent casing on their end,
not a mistake here. Cached 30 min on success, 5 min on failure (a
short negative TTL bounds a retry storm within one batch run
without permanently caching what might be a transient failure).
"""
cache_key = country.upper()
cached = self._ip_cache.get(cache_key)
if cached and time.time() < cached['expires_at']:
return cached['results']
results = []
try:
url = WEBSHARE_PROXY_LIST_URL
params = {'mode': 'direct', 'country_code__in': cache_key, 'page_size': 100}
for _ in range(3): # defensive pagination cap
response = requests.get(
url, headers={'Authorization': f'Token {WEBSHARE_API_KEY}'},
params=params, timeout=10
)
response.raise_for_status()
data = response.json()
results.extend(r for r in data.get('results', []) if r.get('valid'))
url = data.get('next')
params = None
if not url:
break
self._ip_cache[cache_key] = {
'expires_at': time.time() + WEBSHARE_IP_CACHE_TTL_SECONDS, 'results': results,
}
except (requests.RequestException, ValueError) as e:
logger.warning(f'Webshare proxy list fetch failed for {country}: {str(e)[:200]}')
self._ip_cache[cache_key] = {
'expires_at': time.time() + WEBSHARE_IP_CACHE_NEGATIVE_TTL_SECONDS, 'results': [],
}
return []
return results
def get_proxy_for_domain(self, domain: str, country: str = None) -> dict:
"""
Returns requests proxy config for a domain with optional country targeting.
Routes to Bright Data if domain has been previously blocked on Webshare.
"""
override = proxy_repository.get_by_domain(domain)
if override and override['provider'] == 'brightdata':
return self._brightdata_requests()
return self._webshare_requests(country=country)
def get_playwright_proxy_for_domain(self, domain: str, country: str = None) -> dict:
"""
Returns Playwright proxy config for a domain with optional country targeting.
Routes to Bright Data if domain has been previously blocked on Webshare.
"""
override = proxy_repository.get_by_domain(domain)
if override and override['provider'] == 'brightdata':
return self._brightdata_playwright()
return self._webshare_playwright(country=country)
def escalate_to_brightdata(self, domain: str):
"""
Records that Webshare was blocked on this domain.
Upserts a proxy_override record so future requests skip Webshare entirely.
"""
existing = proxy_repository.get_by_domain(domain)
if existing:
proxy_repository.update(existing['id'], {
'provider': 'brightdata',
'blocked_count': existing['blocked_count'] + 1,
'last_blocked': datetime.now().isoformat()
})
else:
proxy_repository.create({
'domain': domain,
'provider': 'brightdata',
'blocked_count': 1,
'last_blocked': datetime.now().isoformat()
})
def _webshare_requests(self, country: str = None) -> dict:
"""
Webshare requests proxy config with optional country targeting.
Format per Webshare's own rotating-endpoint docs
(help.webshare.io/en/articles/8596715): username-{country}-rotate,
with the country code lowercase — their examples show `-us`, `-gb`,
`-ca`, never uppercase. `country` arrives as an uppercase ISO code
from competitors.primary_market (this schema's own convention —
"GB", "US", "AU"), so this always lowercases it before building the
username rather than assuming the caller already did. An earlier
version of this function (and the real proxy_service.py it
generated) used .upper() here — every proxied scrape failed
Webshare auth silently, which fell through to the AI web-fetch
fallback and then to an outright scrape failure, which in turn
meant analysis_service never reached build_prompt() at all for any
competitor requiring the refresh path. No country = general
rotation across full pool.
"""
suffix = f"-{country.lower()}-rotate" if country else "-rotate"
username = f"{WEBSHARE_USER}{suffix}"
return {
"http": f"http://{username}:{WEBSHARE_PASS}@p.webshare.io:80",
"https": f"http://{username}:{WEBSHARE_PASS}@p.webshare.io:80"
}
def _webshare_playwright(self, country: str = None) -> dict:
"""Webshare Playwright proxy config with optional country targeting — see _webshare_requests() for why the country code is lowercased."""
suffix = f"-{country.lower()}-rotate" if country else "-rotate"
return {
"server": "http://p.webshare.io:80",
"username": f"{WEBSHARE_USER}{suffix}",
"password": WEBSHARE_PASS
}
def _brightdata_requests(self) -> dict:
# Port 33335, not the commonly-documented 22225 — Bright Data's
# currently valid SSL certificate requires 33335; 22225 is tied to
# their deprecated cert (phased out ahead of its 2026 expiry). Using
# 22225 is what produced a real net::ERR_CERT_AUTHORITY_INVALID on
# a live scrape before this fix.
return {
"http": f"http://{BRIGHTDATA_USER}:{BRIGHTDATA_PASS}@brd.superproxy.io:33335",
"https": f"http://{BRIGHTDATA_USER}:{BRIGHTDATA_PASS}@brd.superproxy.io:33335"
}
def _brightdata_playwright(self) -> dict:
return {
"server": "http://brd.superproxy.io:33335",
"username": BRIGHTDATA_USER,
"password": BRIGHTDATA_PASS
}
```
**Block detection and auto-switch in `core/scraper.py`:**
```python
async def render_page(url: str, country: str = None) -> dict:
"""
Renders a page using the smart proxy service with country targeting.
country comes from competitors.primary_market in PocketBase.
Detects blocks and auto-escalates to Bright Data if Webshare fails.
Before the single `-rotate` gateway attempt, tries a handful of the
account's own dedicated Webshare IPs for this country
(get_webshare_ip_candidates()) — gives IP diversity a chance to resolve
a one-off block without paying for a Bright Data request. Skipped
entirely for a domain already known (via proxy_overrides) to block the
whole Webshare account, and falls through gracefully to the existing
gateway/Bright Data flow when no candidates are available (no country
given, Webshare API unreachable, or every candidate still blocked).
Data flow:
url + country → domain extracted →
is_domain_blocked_on_webshare(domain)? → no →
get_webshare_ip_candidates(country) → try each in turn, return on
first success → all exhausted or none available →
proxy_service.get_playwright_proxy_for_domain(domain, country) →
Playwright render → if blocked → escalate_to_brightdata(domain) →
retry with Bright Data (no country targeting at fallback level)
"""
domain = extract_domain(url)
if not proxy_service.is_domain_blocked_on_webshare(domain):
for candidate_proxy in proxy_service.get_webshare_ip_candidates(country):
result = await _render_with_proxy(url, candidate_proxy)
if not _is_blocked(result):
return result
proxy = proxy_service.get_playwright_proxy_for_domain(domain, country)
result = await _render_with_proxy(url, proxy)
if _is_blocked(result):
logger.warning(f'Webshare blocked on {domain} — escalating to Bright Data')
proxy_service.escalate_to_brightdata(domain)
result = await _render_with_proxy(url, proxy_service._brightdata_playwright())
return result
def _is_blocked(result: dict) -> bool:
"""
Detects common block signals from scrape result.
Returns True if the response looks like a bot wall rather than real content.
Signals: low char count, HTTP 403/429, Cloudflare challenge, CAPTCHA text.
"""
if result.get('char_count', 0) < 500:
return True
if result.get('status_code') in [403, 429]:
return True
block_signals = [
'cf-browser-verification', 'captcha',
'access denied', 'blocked', 'robot'
]
return any(s in result.get('text', '').lower() for s in block_signals)
```
**In `jobs.py` — primary_market passed through automatically:**
```python
def run_research_job(job_id: str, data: dict):
"""
Pulls primary_market from each competitor record and passes it
to scrape() for automatic country-targeted proxy routing.
No manual country configuration needed per job.
"""
for i, competitor in enumerate(data['competitors']):
competitor_record = competitor_repository.get_by_id(competitor['id'])
page_data = scrape(
url=competitor['url'],
country=competitor_record.get('primary_market') # e.g. "GB", "US", "AU"
)
# rest of job unchanged
```
**Cost summary:**
```
Webshare free tier — 10 proxies shared, enough for MVP testing
Webshare paid — ~$3/month, handles the vast majority of requests
Bright Data fallback — pay-as-you-go ~$4-8/GB, <$2/month at MVP scale
Total proxy cost MVP — under $5/month
```
### OpenRouter Model Cycling — `core/extractor.py`
Cycles through free tier models, retrying each one up to
`RETRY_ATTEMPTS_PER_MODEL` (2) times with backoff before moving to the
next — ported from the legacy `app.py`'s `extract_with_groq()`. An
earlier version of this section (and the Phase 2 refactor built from
it) moved to the next model on the very first error of any kind,
including a transient rate limit — that meant one rate-limit burst
could burn through the entire free-tier list in a single request with
no retry. Falls back to a paid model if every free model is
exhausted. Never fails silently.
`OPENROUTER_FREE_MODELS` model IDs need the `:free` suffix — that's
OpenRouter's actual naming convention for the free-tier variant of a
model; without it the ID may 404 as "no endpoints found" (this is
exactly what shipped in `.env.example` before — a stale, suffix-less
model list, `google/gemini-flash-1.5,meta-llama/llama-3.1-8b-instruct,
mistralai/mistral-7b-instruct`, that 404s on every real request and
silently burns the paid fallback on every single extraction). Current
working default:
```
OPENROUTER_FREE_MODELS=openai/gpt-oss-120b:free,nvidia/nemotron-3-super-120b-a12b:free,meta-llama/llama-3.3-70b-instruct:free
```
`minimax/minimax-m2.5:free` was in this list until a real test run
against a live job showed it 404ing on every single call: OpenRouter
deprecated the free-tier suffix for this model ("This model is
unavailable for free. The paid version is available now - use this
slug instead: minimax/minimax-m2.5"). Unlike a rate limit, this is not
transient — every extraction call was guaranteed to burn both retry
attempts on a model that can never succeed. Removed rather than left
in to fail predictably; OpenRouter's free-tier lineup shifts over time,
so revisit this list if extractions start failing at every free model
again.
**MVP launch runs paid-first, not free-tier-first.** The same test
run that surfaced the dead `minimax` free-tier model also showed
`openai/gpt-oss-120b:free` eating its full rate-limit backoff (5s +
10s) on nearly every extraction call — combined, a single research job
took 5m47s, which blew past the dashboard's polling timeout (raised
from 5 to 20 minutes in `analysis_store.js` as a separate fix, but the
underlying slowness is still worth avoiding). Free-tier rotation is not
reliable enough to depend on for launch, so
`OPENROUTER_USE_FREE_MODELS_FIRST=false` skips `FREE_MODELS` entirely
and calls `FALLBACK_MODEL` directly — set to `deepseek/deepseek-v4-flash`
rather than `anthropic/claude-haiku-4-5`.
`minimax/minimax-m2.5` (the paid slug) was tried here first, since it
was already the paid fallback other fixes in this session had set up —
it failed 100% of the time (10/10 calls: 2 retry attempts × 5
competitors in one real job) with `_call_openrouter`'s `_parse_json_response`
crashing on `None` content. Root cause, confirmed directly against
OpenRouter's API using the actual production prompt (real JSON schema
+ realistic page-content length, not a synthetic approximation):
`minimax/minimax-m2.5` has *mandatory* reasoning that cannot be
disabled (`reasoning: {enabled: false}` returns `400 Reasoning is
mandatory for this endpoint and cannot be disabled`), and its hidden
chain-of-thought token consumption scales unpredictably with how much
the model has to reason through — real page content demands far more
reasoning than a short, repetitive test string, so it's much more
likely to consume the entire `max_tokens=4096` budget before ever
writing the visible JSON answer, leaving `content: null`. Small/simple
test prompts against this model succeed fine, which is why isolated
smoke-testing didn't catch it — only a prompt shaped like the real
extraction call did. `deepseek/deepseek-v4-flash` was tested against
the identical real prompt (4/4 successful runs, `finish_reason: "stop"`
every time, no `None` content) and is also roughly 3.5x cheaper per
call. If a future model swap is considered, always test against the
real `build_prompt()` output at realistic page-content length — a
short synthetic prompt is not sufficient to catch a reasoning-budget
truncation failure like this one.
`_call_openrouter()` also sends `response_format={'type': 'json_object'}`
on every call — a second, unrelated real failure surfaced after the
`deepseek` switch: it would write a full prose comparative analysis
(markdown headers, tables, "Key Differentiators" sections) instead of
the requested JSON whenever a competitor's page content didn't clearly
match the requested trip, prioritizing a "helpful" explanation of the
mismatch over the format instruction. Not reproducible with a short
synthetic prompt either — only the real, long production prompt
triggered it. `response_format` constrains this at the API level
rather than relying on prompt wording alone, and is a systemic fix for
this whole class of "model deviates into prose" failure, not just the
one reproduced case. Confirmed compatible with every model in
`OPENROUTER_FREE_MODELS`/`OPENROUTER_FALLBACK_MODEL` before shipping —
a model that rejects the parameter would 400 immediately, not the 429s
(rate limiting, unrelated) seen during that compatibility check.
`FREE_MODELS` is left populated and untouched in `.env`/`.env.example`
specifically so re-enabling free-tier cycling later is a one-line
flip (`OPENROUTER_USE_FREE_MODELS_FIRST=true`) — nothing else needs
reconfiguring.
```python
FREE_MODELS = os.getenv('OPENROUTER_FREE_MODELS', '').split(',')
FALLBACK_MODEL = os.getenv('OPENROUTER_FALLBACK_MODEL',
'anthropic/claude-haiku-4-5')
# MVP launch toggle — false skips FREE_MODELS entirely and calls
# FALLBACK_MODEL directly. See the paragraph above for why.
USE_FREE_MODELS_FIRST = os.getenv('OPENROUTER_USE_FREE_MODELS_FIRST', 'true').lower() == 'true'
RETRY_ATTEMPTS_PER_MODEL = 2
def extract_with_openrouter(prompt: str, content: str) -> dict:
"""
Cycles through free OpenRouter models, retrying each one up to
RETRY_ATTEMPTS_PER_MODEL times before moving to the next.
Falls back to the paid fallback model if all free models fail.
Never returns None — raises ExtractorError only after all models exhausted.
Skips straight to FALLBACK_MODEL when USE_FREE_MODELS_FIRST is false.
Data flow:
prompt + content → for each model in FREE_MODELS + [FALLBACK_MODEL]
(or just [FALLBACK_MODEL] when USE_FREE_MODELS_FIRST is false):
up to RETRY_ATTEMPTS_PER_MODEL attempts:
POST openrouter.ai/api/v1/chat/completions →
if success: return parsed JSON →
if rate limit: sleep 5*(attempt+1)s, retry same model →
if credit/quota exhausted (403): move to next model immediately
(quota won't recover mid-request — retrying wastes time) →
if any other error: retry same model up to the attempt limit,
then move to next model →
if all free models fail → try FALLBACK_MODEL (paid) →
if fallback fails → raise ExtractorError, log to Sentry
"""
models_to_try = FREE_MODELS + [FALLBACK_MODEL] if USE_FREE_MODELS_FIRST else [FALLBACK_MODEL]
for model in models_to_try:
for attempt in range(RETRY_ATTEMPTS_PER_MODEL):
try:
response = _call_openrouter(model, prompt, content)
if response:
if USE_FREE_MODELS_FIRST and model == FALLBACK_MODEL and FREE_MODELS:
logger.warning(
f'Free models exhausted — used paid fallback: {model}'
)
sentry_sdk.capture_message(
'OpenRouter paid fallback used',
level='warning'
)
return response
except RateLimitError:
wait = 5 * (attempt + 1)
logger.warning(f'{model} rate limited — waiting {wait}s...')
time.sleep(wait)
except CreditExhaustedError:
logger.warning(f'{model} free tier exhausted — trying next model...')
break
except Exception as e:
logger.warning(f'{model} failed: {str(e)[:200]}')
raise ExtractorError('All OpenRouter models exhausted including paid fallback')
```
Firecrawl is in the MVP stack for `/monitor` only.
`/map` discovery endpoint is Intelligence tier — do not build in MVP.
---
## 11. Battlecard Generation
After each scrape run completes (whether triggered by a manual dashboard
analysis or the nightly watchlist cron — see §7's "Trip watchlist"),
`jobs.py` calls `generate_battlecard()` directly whenever a competitor's
refresh path produced genuinely fresh data (real extraction, not a
cache reuse and not a hash-confirmed-unchanged result).
```python
def generate_battlecard(competitor_id: str, tenant_id: str) -> dict:
"""
Pulls a competitor's tracked products (up to BATTLECARD_PRODUCT_LIMIT
= 50, newest first) from PocketBase, plus their real recent
price_history, and passes a real structured summary to the
OpenRouter extraction model. Saves generated battlecard text and
JSON back to PocketBase. Returns battlecard dict for immediate use.
Data flow:
competitor_id → PocketBase products (latest 50) →
_summarise_products() (top trips, trip count, price range, avg
duration, service levels, target audiences, activities) +
_summarise_recent_price_changes() (real price_history moves in
the last 30 days, sorted by magnitude) →
structured prompt → OpenRouter (via core.extractor) →
battlecard text + JSON → PocketBase battlecards table
"""
```
`BATTLECARD_PRODUCT_LIMIT` was previously 10 — skewed the summary
toward whichever products happened to be scraped most recently instead
of a reasonably complete view of the tracked catalogue. Raised to 50
(adjustable here only).
`{positioning_summary}` and `{recent_changes}` were previously hardcoded
placeholder strings (`'Derived from current product catalogue.'` and
`'See price_history for this competitor.'`) — the model was being told
those inputs existed when they never varied per competitor or per run.
`positioning_summary` is removed outright (it was also circular — the
prompt's own *output* already asks the model to derive `"positioning"`).
`recent_changes` is now a real sentence built from that competitor's
actual `price_history` rows within the last 30 days (every row already
represents a genuine price move — `_save_scrape_result()` only writes
one when the price actually changed — so no magnitude filter is needed,
just the recency window), falling back to "No significant price changes
in the last 30 days." when none qualify.
Prompt template:
```
You are a competitive intelligence analyst for an adventure travel operator.
Based on this competitor data, write a concise battlecard.
Competitor: {name}
Top trips: {top_trips}
Number of tracked trips: {trip_count}
Price range: {price_low} - {price_high} USD
Avg duration: {avg_duration} days
Service levels offered: {service_levels}
Typical target audience: {target_audiences}
Common activities/themes: {activities_summary}
Recent price changes (last 30 days): {recent_changes}
Write — ground every point in the specific data above (real prices,
service levels, activities, audience, recent price moves) rather than
generic statements that could apply to any competitor:
1. One line positioning summary (max 20 words)
2. Three strengths (bullet points, max 14 words each)
3. Three weaknesses (bullet points, max 14 words each)
4. One pricing observation (max 30 words) — reference the actual price
range and/or a real recent price change when one is available
Return ONLY valid JSON:
{
"positioning": "...",
"strengths": ["...", "...", "..."],
"weaknesses": ["...", "...", "..."],
"pricing_observation": "..."
}
```
Word limits were previously 15/8/8/20 — tight enough that the richer
input data above (added in the same pass as removing the placeholder
strings) was mostly getting compressed away into generic-sounding
fragments rather than actually showing up in the output. Loosened once
the input data itself became real; still a scannable card, not a full
narrative (that's what the per-run `comments` field, §7, already is —
the two are intentionally different: `comments` is a one-off comparison
for a specific trip match-up, a battlecard is an evergreen standing
summary of a competitor overall).
`BattlecardCard.vue` previously rendered `positioning` and the
`strengths`/`weaknesses` pills only — `content_json.pricing_observation`
was generated and stored the whole time but never actually displayed
anywhere on the card. Now rendered as its own line below the pills.
`battlecards.generated_at` was `autodate('generated_at', true)` —
onCreate only, never onUpdate (migration 001's `autodate()` helper
defaults `onUpdate` to `false`). Every regeneration since a
battlecard's first creation genuinely updated `content`/`content_json`
underneath while the dashboard's "Updated {generated_at}" timestamp
stayed frozen at the original creation date — a real battlecard
refresh could look untouched for days. Fixed via migration 009
(in-place field mutation — `collection.fields.getByName('generated_at').onUpdate = true`
— not a destructive remove+re-add, which would have wiped every
existing battlecard's real creation timestamp for no benefit).
---
## 12. Semantic Trip Matching
### Client trip population
`client_trips` has no PM-facing form — it's populated automatically
every time a PM submits `/research` (CLAUDE.md §8's TripIntentForm).
The destination/duration/travel_style/product_name they describe IS a
client trip, so `POST /research` upserts it via
`embedding_service.save_client_trip()` before enqueueing the job — a
side effect that never blocks the primary research job if it fails
(§18 resilience rule).
Upsert key: `(tenant_id, trip_name)`, where `trip_name` is the PM's
product name if given, else a `{destination} {travel_style}` fallback.
Re-running analysis for the same product updates the existing row
instead of duplicating it. If `destination` or `duration_days` change
on an update, the stored `embedding` is cleared (set to null) so the
next embedding pass regenerates it — `/internal/embed-products` only
embeds rows missing a vector, so a stale one would otherwise never get
refreshed.
### Embedding generation
After products are stored, embed each new trip using text-embedding-3-small.
Store vector as JSON in the products.embedding field.
```python
def embed_trip(trip: dict) -> list:
"""
Generates a semantic embedding for a trip using OpenRouter.
Combines trip name, destination, duration, and activities into
a single text representation before embedding.
Data flow:
trip dict → formatted text string → OpenRouter embeddings API →
vector list → stored in products.embedding
"""
text = f"""
{trip['trip_name']}
Destination: {trip['destination']}
Duration: {trip['duration_days']} days
Activities: {trip['activities']}
Start: {trip['start_location']}
"""
response = openai_client.embeddings.create(
model="openai/text-embedding-3-small",
input=text
)
return response.data[0].embedding
```
### Similarity matching
```python
def find_comparable_trips(client_trips, competitor_trips, threshold=0.75):
"""
Calculates cosine similarity between all client trips and all
competitor trips. Returns matches above the threshold sorted by
similarity score descending.
Data flow:
client_trips + competitor_trips (each with .embedding) →
pairwise cosine similarity → filter by threshold →
sort by score → list of match dicts
"""
```
Threshold: 0.75. Matches upserted into comparable_matches on
`(tenant_id, client_product, competitor_product)` — a repeat weekly
match updates `similarity_score`/`price_difference`/`duration_difference`/
`last_matched` in place rather than duplicating a row, which is the
whole reason `first_matched`/`last_matched` exist as separate fields.
A PM's `dismissed = true` on a match is left untouched by the upsert,
so dismissing a match survives it matching again in a later run.
`/internal/embed-products/<tenant_id>` embeds both un-embedded
competitor products AND un-embedded client_trips — both sides need a
vector before `/internal/match-comparable/<tenant_id>` can compare
them, and both are read from PocketBase (via their repositories), not
passed in the request body, so the Sunday-night n8n workflow can call
either route unattended per tenant.
---
## 13. Weekly HTML Email Brief
### No PDF — HTML email only
WeasyPrint has been removed from the stack. The Monday brief is a
rich Jinja2-rendered HTML email sent via Resend. Same content, zero
Docker dependency, zero PDF complexity.
Brief email includes:
- Bettersight branded header with tenant name
- Market summary (price changes, new products, competitors monitored)
- Price movements table (competitor, trip, previous, current, delta, percent)
- New products this week (formatted cards)
- Battlecard updates summary
- Link to app.bettersight.io
- Unsubscribe footer
### n8n Workflow — Monday 6am
```
Schedule Trigger (Monday 06:00 AST)
→ HTTP POST /internal/weekly-brief/{tenant_id} for each active tenant
→ Flask renders brief.html Jinja2 template with weekly PocketBase data
→ Resend sends HTML email to tenant admin
→ Logged to digest_logs
```
### Brief service
```python
def send_weekly_brief(tenant_id: str):
"""
Renders the weekly intelligence brief as branded HTML and
sends it via Resend to the tenant admin email.
Data flow:
tenant_id → PocketBase (price_history, products, battlecards
for current week) → brief.html Jinja2 template rendered →
Resend API → digest_logs record created
"""
data = brief_repository.get_weekly_data(tenant_id)
html = render_template('emails/brief.html', **data)
resend.Emails.send({
"from": "Bettersight <brief@bettersight.io>",
"to": data['admin_email'],
"subject": f"Bettersight Weekly Brief — Week of {data['week_of']}",
"html": html
})
brief_repository.log_digest(tenant_id, 'weekly')
```
---
## 15. Google Apps Script
### Role — Sheet-Initiated Pull Integration
The Apps Script is not the primary interface. The dashboard is.
The script's only job is to **pull** completed analysis results from
Flask into the PM's currently open Sheet on demand.
**Architectural rule that governs this section:**
A standalone Apps Script cannot be invoked by an external server.
Google's execution model requires the script to run in response to
a user action inside the Sheet itself. Therefore Flask never pushes
to the Sheet — the Sheet pulls from Flask.
The dashboard's post-analysis affordance is a "Copy rows" button
(tab-separated to clipboard) plus a short instruction reminding the
PM to open their Sheet and click "Pull latest results" in the
Bettersight sidebar when they want the data written directly.
**Three functions only:**
```
pullLatestResults(tabName) — Sheet-initiated fetch + write for the most recent job
getWorkbookTabs() — returns tab list for the sidebar's own tab selector
validateLicence() — confirms PM is authorised (email check only)
```
### Deployment Model — Standalone Script
Deployed as a standalone script, not container-bound to any workbook.
PM installs once via a shareable link from app.bettersight.io/account.
Works in any Google Sheet workbook they open from that point forward.
### Template Import — The PM's One-Time Setup
The PM never moves their existing stage-gate workbook. They import a
single tab from our template into their workbook and continue using
their workbook exactly as before.
**Two-step import performed once:**
1. From app.bettersight.io/account, click [Open template ↗] to open
the Bettersight template Sheet we host on Google Drive
2. In their existing stage-gate workbook: right-click any tab at
the bottom of the page → "Copy to" → "Existing spreadsheet" →
select their workbook
3. The Bettersight Analysis tab is added alongside their existing tabs
**The PM can rename the imported tab to anything.**
The sidebar's tab selector dropdown reads live tab names via
getWorkbookTabs() every time it opens, and detectFieldRows_() maps
data to rows by label — never by tab name or row position.
Rename the tab "Comp Intel", "Peru Research 2026", or leave it as
"Bettersight Analysis" — pullLatestResults() writes correctly either
way as long as the PM selects the intended tab from the dropdown.
**After import:**
- Their existing stage-gate formulas can reference the new tab like any
other tab: ='Bettersight Analysis'!B5 or ='Whatever They Renamed It'!B5
- The standalone Apps Script (installed once against their Google account)
appears in Extensions → Bettersight in every workbook they open,
including this one
- When they click Pull latest results the script uses
SpreadsheetApp.getActiveSpreadsheet() to write into whichever workbook
is currently open — no configuration required per workbook
**What we do not do:**
- We do not ask the PM to migrate their stage-gate workbook into our template
- We do not require them to duplicate their existing workbook
- We do not bind the script to a specific spreadsheet ID
- We do not depend on a specific tab name
- We do not host the PM's stage-gate data — only the imported Bettersight
tab holds any Bettersight data, and that tab lives inside their own
workbook on their own Drive
### Template File Structure
The template Sheet we host on Google Drive contains a single tab
named Bettersight Analysis by default (PM can rename after import).
**Row structure — TRANSPOSED, not column-per-field.** Column A holds
one row per field label (company, link, trip name, pricing fields,
etc. — the exact list and order live in `appscript/Sync.gs`'s
`STANDARD_ROWS`/`NGS_ROWS`, hand-ported from the dashboard's own
`frontend/src/config/resultFields.js`). Row 1 is reserved for
competitor names, starting at column B — one column per competitor in
the most recent run. This matches the dashboard's Copy rows /
Download Excel export exactly, both of which were verified against
the real legacy client reference sheet ("G Compete App V1.xlsx"). An
earlier version of this section (and the `detectColumns()` design it
described) specified the opposite layout — column-per-field headers
in row 1, one row appended per competitor — which was never actually
reconciled against that real reference file; `Sync.gs` was rewritten
to match the transposed layout instead of the reverse.
The template tab does not need to be pre-populated with row labels at
all — a blank tab is enough. `detectFieldRows_()` bootstraps column A
from `STANDARD_ROWS`/`NGS_ROWS` (setting the 'Field' corner label in
A1) the first time it finds no recognisable labels there, so the very
first pull self-describes the sheet. Every subsequent pull REPLACES
the previously-written competitor columns (clears columns B onward)
rather than appending new columns indefinitely — the tab always
reflects the single most recent run, same as the dashboard's results
view.
**Header resilience:**
- If the PM renames a row label, detectFieldRows_() still finds it as
long as it matches (case/punctuation-insensitive) the row's label —
substituting the PM's real company name into the comments row's
`{company}` token first, since that's what's actually written to
the cell
- If the PM reorders rows or deletes optional rows, detectFieldRows_()
adapts — never hardcoded row positions
- If detectFieldRows_() cannot find ANY recognisable row label at all
(a genuinely blank tab), it bootstraps the full row list from
scratch — see above
**Template hosting:**
The template is a single Google Sheets file on Drive at a stable share
URL. The URL is stored as an env var TEMPLATE_SHEET_URL and served via
GET /account/template. Any updates to the template's row list are made
in-place on the source file — no versioning needed since the PM's
copy is a snapshot at their moment of import.
### File Structure
```
appscript/
Code.gs — onOpen(), install entry point
Validation.gs — validateLicence() email-only
Sync.gs — pullLatestResults(), getWorkbookTabs(), detectFieldRows_()
Sidebar.html — tab selector + pull button + last-pull status
```
### Pull Flow
```javascript
/**
* Fetches the most recent completed analysis for the PM from Flask
* and writes it to the selected tab of the currently open workbook.
* Sheet-initiated only — cannot be triggered by any external server.
*
* Writes results in the TRANSPOSED layout (one row per field in
* column A, one column per competitor starting at column B) — matches
* the dashboard's Copy rows / Download Excel export exactly. Row
* detection is label-based, never hardcoded row positions —
* resilient to any sheet restructuring.
*
* Flow:
* PM clicks [Pull latest results] in sidebar →
* validateLicence() confirms authorisation →
* GET Flask /research/history/latest?email={sessionEmail} →
* stored results + tab_type returned →
* detectFieldRows_(sheet, rows) builds { field: rowNumber } map →
* writeResultsToSheet_(results, rowMap, rows, sheet) writes into
* selected tab, one column per competitor →
* sidebar shows "Pulled: Today 9:14am · 5 competitors"
*/
function pullLatestResults(tabName) { ... }
/**
* Returns list of all tab names in the PM's currently open workbook.
* Used by the sidebar (not the dashboard) to populate its own tab selector.
*/
function getWorkbookTabs() {
return SpreadsheetApp.getActiveSpreadsheet()
.getSheets()
.map(s => s.getName());
}
/**
* Detects each field's row number by matching labels in column A
* (rows 2+, row 1 reserved for competitor names). Returns
* { fieldKey: rowNumber }. Bootstraps column A from the row list
* (and sets the 'Field' corner label in A1) if no labels are found at
* all — a blank tab is a valid starting point.
*/
function detectFieldRows_(sheet, rows, companyName) { ... }
```
### Sidebar UI
```
┌─────────────────────────────┐
│ 🔵 Bettersight │
│ Pull results into Sheet │
│ ───────────────────────── │
│ Write to tab: │
│ [Bettersight Analysis ▾] │
│ │
│ [ Pull latest results ] │
│ ───────────────────────── │
│ Pulled: Today 9:14am │
│ 5 competitors · Peru │
└─────────────────────────────┘
```
The full analysis workflow — competitor selection, trip-intent matching,
job status, results view, history, confidence indicators — all live in
the dashboard. The sidebar is a pull button only.
The Apps Script is deployed as a **standalone script**, NOT container-bound
to a specific Google Sheet file. This is the most important architectural
decision for the add-on.
**Why standalone:**
- Works in any Google Sheet the PM opens — including their existing
20-sheet stage gate workbook
- PM installs once via a shareable install link from the Bettersight dashboard
- They import the Bettersight template tab into their existing workbook
- The sidebar runs analysis and writes to whichever tab they select
- Cross-sheet formulas in their stage gate sheet reference the Bettersight tab
natively — no copy-paste ever needed after setup
**What the PM receives:**
1. A template Sheet tab — downloadable from app.bettersight.io/account
as a Google Sheets link. They copy this tab into their existing workbook.
2. A sidebar install link — one click installs the standalone script on
their Google account. Works in any workbook from that point forward.
**Sheet ID binding update:**
Because the script is standalone, the licence validation no longer binds
to a single Sheet ID. Instead it binds to the PM's Google account email
(already implemented via domain + seat validation). Remove the
sheet_id binding layer from the licence validation — it is redundant
and would break the standalone model.
---
### File Structure
```
appscript/
Code.gs — onOpen(), menu setup, sidebar launch, install entry point
Validation.gs — validateLicence()
Research.gs — runResearch(), pollStatus(), writeResultsToSheet()
Competitors.gs — loadCompetitorsFromPocketBase(), refreshCompetitorList()
Settings.gs — saveSettings(), loadSettings(), getTargetSheet()
Battlecards.gs — refreshBattlecards(), writeBattlecardsTab()
ComparableTrips.gs — refreshComparableTrips(), writeComparableTab()
Sidebar.html — sidebar UI
```
---
### Validation Flow (Validation.gs)
```javascript
/**
* Validates the current user's licence against the Bettersight API.
* Standalone script — validates by email only, no sheet_id binding.
*
* Flow:
* 1. Check 6-hour cache — return true immediately if cache valid
* 2. Get user email via Session.getActiveUser().getEmail()
* 3. POST to /validate-email with email only
* 4. On success — cache result and tier for 6 hours, return true
* 5. On failure — show alert with reason, return false
*
* Cache keys: BETTERSIGHT_VALID, BETTERSIGHT_TIER
* Cache duration: 21600 seconds (6 hours)
*/
function validateLicence() { ... }
```
---
### Settings — Tab Selection and Persistence (Settings.gs)
The PM selects which tab in their workbook to write results to.
This setting persists across sessions via PropertiesService.
```javascript
/**
* Returns the target sheet tab for writing analysis results.
* Reads from UserProperties — falls back to tab named
* 'Bettersight Analysis' if no preference is stored.
*
* Flow:
* UserProperties.getProperty('TARGET_TAB') →
* if found: return that tab →
* if not found: find 'Bettersight Analysis' tab →
* if not found: prompt PM to select tab via dropdown
*/
function getTargetSheet() { ... }
/**
* Saves all sidebar settings to UserProperties so they persist
* across sessions. Called automatically after each successful run.
*
* Settings persisted:
* TARGET_TAB — which sheet tab to write to
* TRAVEL_STYLE — last used travel style
* PRODUCT_NAME — last used G Adventures product name
* DESTINATION — last used destination
* DURATION — last used duration
* TAB_TYPE — Standard or NGS
* SELECTED_COMPS — JSON array of last selected competitor IDs
*/
function saveSettings(settings) { ... }
/**
* Loads all persisted settings and returns them to the sidebar
* so the PM's last run is pre-filled on open.
*/
function loadSettings() { ... }
```
---
### Competitor Auto-Population (Competitors.gs)
Competitors are loaded from PocketBase on sidebar open.
The PM never manually types a URL again.
```javascript
/**
* Fetches the PM's competitor list from the Bettersight API.
* Called on sidebar open. Returns array of competitor objects
* for rendering as checkboxes in the sidebar.
*
* Flow:
* validateLicence() → GET /competitors with email header →
* returns [ { id, name, url, primary_market, last_scraped,
* change_detected, status } ] →
* rendered as checkboxes in sidebar with status indicators
*
* Status indicators shown per competitor:
* ✓ Current (updated {time}) — last_scraped < 24hrs, no change
* ⚠ Changed ({time} ago) — change_detected = true
* ○ Stale — last_scraped > 24hrs
*/
function loadCompetitorsFromPocketBase() { ... }
```
---
### Column Auto-Detection (Research.gs)
Column positions are detected by reading header names, not hardcoded numbers.
Makes the sheet resilient to any structural changes the PM makes.
```javascript
/**
* Detects the column index for each field by matching header names
* in row 1 of the target sheet. Returns a field-to-column map.
* Falls back to STANDARD_FIELDS hardcoded positions if headers
* cannot be found — logs a warning when falling back.
*
* Flow:
* Read row 1 of target sheet →
* Build { fieldName: columnIndex } map by matching header text →
* Return map for use by writeResultsToSheet()
*/
function detectColumns(sheet) { ... }
```
---
### Research Flow with Per-Competitor Progress (Research.gs)
```javascript
/**
* Submits a research job and polls for completion.
* Shows per-competitor status in the sidebar in real time.
*
* Flow:
* 1. validateLicence() — exit if invalid
* 2. getTargetSheet() — confirm which tab to write to
* 3. detectColumns(sheet) — map field names to column positions
* 4. loadSettings() — pre-fill job params from last run
* 5. POST to /research with selected competitor IDs and job params
* → receives job_id immediately
* 6. Poll GET /research/status/{job_id} every 3 seconds
* 7. On each poll update sidebar with per-competitor status:
* ✓ Intrepid Travel — Peru Classic $2,190
* ✓ Exodus Travels — Machu Picchu $2,050
* ⟳ Flash Pack — scraping...
* Kimkim — queued
* 8. On complete — writeResultsToSheet() using detected columns
* 9. saveSettings() — persist this run's settings for next time
* 10. Show confidence indicators per competitor in sidebar
* 11. Timeout after 75 attempts (5 minutes)
*/
function runResearch() { ... }
/**
* Writes extracted competitor data to the target sheet.
* Uses column map from detectColumns() — never hardcoded positions.
* Only writes fields that exist in the detected column map.
*
* Flow:
* results array + columnMap + sheet →
* for each competitor result:
* for each field in columnMap:
* sheet.getRange(fieldRow, columnMap[field]).setValue(value)
*/
function writeResultsToSheet(results, columnMap, sheet) { ... }
```
---
### Single Competitor Re-Run (Research.gs)
```javascript
/**
* Triggers a fresh scrape and extraction for a single competitor.
* Used when the PM wants to refresh one competitor without
* re-running the full job. Updates only that competitor's column.
*
* Flow:
* competitorId → POST /research with single competitor →
* poll status → write result to sheet (single column only) →
* update sidebar status for that competitor only
*
* Called from a "Refresh" button shown per competitor
* in the completed run status list in the sidebar.
*/
function refreshSingleCompetitor(competitorId) { ... }
```
---
### Confidence Indicators (Research.gs)
```javascript
/**
* Renders a confidence bar per competitor in the sidebar
* after a run completes. Based on the relevancy score (1-5)
* returned by the AI extraction.
*
* Display format:
* Intrepid Travel ████████░░ 82% — Good match
* Flash Pack ██████░░░░ 61% — Review recommended
* Kimkim ████░░░░░░ 44% — Low confidence ⚠
*
* Low confidence (< 60%) shows a warning prompt:
* "The AI had low confidence extracting Flash Pack data.
* The page may have changed structure. Consider a manual review."
*/
function renderConfidenceIndicators(results) { ... }
```
---
### Analysis History (Research.gs)
```javascript
/**
* Fetches the last 5 analysis runs for the current tenant
* from PocketBase. Displayed in the sidebar as a history panel.
* PM can restore a previous run's data to the sheet for comparison.
*
* Flow:
* GET /research/history?email={email}&limit=5 →
* returns [ { job_id, run_at, competitors, summary } ] →
* rendered as list in sidebar history panel
*
* Each history item shows:
* Mon 9 Jun 9:14am · 5 competitors · Intrepid, Exodus, Flash Pack...
* [Restore to sheet]
*
* Restore writes that run's stored results back to the sheet
* using the same writeResultsToSheet() function.
*/
function loadAnalysisHistory() { ... }
/**
* Restores a previous run's results to the current sheet.
* Fetches stored results from PocketBase by job_id.
* Warns PM that this will overwrite current sheet data.
*/
function restoreHistoryRun(jobId) { ... }
```
---
### Copy to Clipboard (Sidebar.html)
A "Copy rows" button in the sidebar copies selected competitor rows
as tab-separated values — paste-ready into any Google Sheet, Excel,
or Notion table without formatting issues.
```javascript
/**
* Copies the selected competitor result rows as tab-separated
* values to the clipboard. PM can paste directly into any sheet.
* No formatting, no formulas — clean values only.
*
* Called from "Copy selected rows" button in sidebar.
*/
function copyRowsToClipboard(selectedCompetitorIds) { ... }
```
---
### Sidebar UI Sections (Sidebar.html)
```
┌─────────────────────────────────┐
│ 🔵 Bettersight │
│ ───────────────────────────── │
│ ANALYSIS SETTINGS │
│ Travel style [Classic ▾] │
│ Product name [____________] │
│ Destination [____________] │
│ Duration [____________] │
│ Tab type [Standard ● NGS○]│
│ Write to tab [Bettersight ▾]│ ← tab selector
│ ───────────────────────────── │
│ COMPETITORS │
│ ☑ Intrepid Travel ✓ Current │ ← from PocketBase
│ ☑ Exodus Travels ✓ Current │
│ ☑ Flash Pack ⚠ Changed │
│ ☐ Kimkim ○ Stale │
│ ───────────────────────────── │
│ [ Run Analysis ] │
│ [ Copy selected rows ] │
│ ───────────────────────────── │
│ LAST RUN RESULTS │
│ ✓ Intrepid ████████░░ 82% │ ← confidence
│ ✓ Exodus █████████░ 91% │
│ ⚠ Flash Pk ██████░░░░ 61% │ ← low confidence warn
│ [↺ Refresh Flash Pack] │ ← single re-run
│ [ Copy selected rows ] │
│ ───────────────────────────── │
│ HISTORY │
│ Mon 9 Jun · 5 competitors │
│ [Restore] │
│ Mon 2 Jun · 4 competitors │
│ [Restore] │
└─────────────────────────────────┘
```
---
### New Flask Routes Supporting Apps Script Improvements
```
GET /competitors — returns competitor list for sidebar auto-population
includes: id, name, url, primary_market,
last_scraped, change_detected, status
GET /research/history — last 5 runs for tenant (by email header)
GET /research/history/<id> — full results for a specific run by job_id
used by restoreHistoryRun()
```
---
## 15. n8n Workflows
### Daily scrape (6am every day) — targets the trip watchlist, not competitor homepages
```
Schedule Trigger (06:00 AST)
→ For each active tenant:
→ Query PocketBase for that tenant's trip_watchlist items (every
seat's watched trips — no staleness pre-filter; every watched
trip is sent every night)
→ HTTP POST /internal/scrape/{tenant_id} with
competitors: [{ id: competitor_id, url: watched_url }, ...] —
one entry per watched trip (a competitor can appear more than
once if two seats watch two different trips on it)
→ RQ job queued → jobs.py's confirmed_url threading scrapes the
SPECIFIC watched url per entry (analysis_service.needs_refresh()'s
per-url freshness check decides fast-vs-refresh internally,
exactly as a manual dashboard run would)
→ PocketBase updated (products, price_history) for that specific trip
→ price_history diff calculated automatically
→ is_new flag set on new products automatically
```
This replaced an earlier design that queried `competitors` directly
(`change_detected = true || last_scraped > 24hrs`) and re-scraped each
competitor's root `website` every night — a page
`services/url_utils.py`'s `is_generic_homepage()` already flags as
low-signal and non-trip-specific, with no natural bound on nightly
volume. The trip watchlist (below) replaces it with a small, predictable
ceiling and alerts that are always about a trip a PM explicitly chose to
track.
### Trip watchlist — per-seat, tier-capped nightly monitoring
Each **active seat** (not the tenant as a whole) can pin a small number
of specific already-analyzed competitor trips to a personal watchlist —
`trip_watchlist` PocketBase collection (migration 008), one row per
watched trip, `seat_id` + `competitor_id` + `url` + `trip_name`. The cap
is per-seat and tier-based
(`services/watchlist_service.py`'s `WATCHLIST_CAP_PER_SEAT`):
```python
WATCHLIST_CAP_PER_SEAT = {
'analyse': 1, # a fully-seated ($349, 5 seats) tenant tops out
# at 5 real trip scrapes/night — same ceiling as
# scraping every competitor once, but every
# alert is now about a trip a PM chose to track
'discover': 2, # placeholder — Discover isn't built yet (§27)
'intelligence': 3, # placeholder — layered on top of that tier's
'enterprise': 3, # full catalogue auto-discovery, not instead of it
}
```
**Routes** (`api.py`): `GET /watchlist` returns every watched item for
the session's tenant (all seats', with the watching seat's email
denormalized for display — visibility is tenant-wide even though the
cap is per-seat); `POST /watchlist` (`{competitor_id, url, trip_name}`)
resolves the calling seat via `auth_service.get_seat_from_request()` and
enforces the cap, 400 `watchlist_full` with a clear message on breach;
`DELETE /watchlist/<id>` removes an item — any active seat on the tenant
may remove any item, no per-seat ownership lock (matching this app's
existing no-fine-grained-permissions convention).
`auth_service.get_tenant_id_from_request()` used to look up the caller's
active seat purely to validate the JWT, then discard it — every
authenticated route before this feature only ever needed `tenant_id`.
`get_seat_from_request()` now returns `(tenant_id, seat)` instead, with
`get_tenant_id_from_request()` kept as a thin backward-compatible
wrapper around it.
**Frontend**: `ResultsTable.vue` gets a "☆ Watch" / "★ Watching" toggle
per competitor column (not shown on the own-trip column — watching your
own trip isn't meaningful), using the row-derived `link`/`tripName`
values so it works identically for fast-path and refresh-path results.
`ActivityPage.vue`'s new `WatchlistCard.vue` lists every tenant item
(trip name, watched-by email, remove button) plus an "X of Y slots
used" indicator for the current seat (mirrors
`WATCHLIST_CAP_PER_SEAT` client-side for display only — the server
enforces the real cap).
Not built in this pass: no ad-hoc "watch any URL" entry (v1 only lets a
PM watch a trip that already appears in a completed analysis result),
and Discover/Intelligence's actual cap numbers are placeholders, not
load-bearing until those tiers are built.
### Battlecard regeneration (after scrape)
```
PocketBase webhook on scrape_runs status=complete
→ HTTP POST /internal/battlecard/{competitor_id}
→ Runs for each competitor in the completed scrape run
```
### Price change alert
```
PocketBase webhook on price_history insert
→ Code node: check change_percent threshold (>5% triggers alert)
→ Creates alert record in PocketBase alerts table
{ alert_type: "price_change", competitor_id, product_id,
change_amount, change_percent, delivered_dashboard: true }
→ Alert appears in dashboard activity feed immediately
→ n8n daily digest (6pm): collects unread alerts → Resend email summary
```
### New product alert
```
PocketBase webhook on products insert where is_new=true
→ Creates alert record in PocketBase alerts table
{ alert_type: "new_product", competitor_id, product_id,
delivered_dashboard: true }
→ Alert appears in dashboard activity feed immediately
→ Included in daily digest email (6pm) and Monday brief
```
### [Discover tier] External notification integrations
```
When PM connects Slack/Teams/WhatsApp in account settings:
→ webhook_url stored in tenant_notifications table
→ n8n alert workflow checks tenant_notifications on each alert
→ If webhook configured: HTTP POST to Slack/Teams webhook URL
→ If Twilio configured: POST to Twilio WhatsApp API
```
### Monday morning brief (6am)
```
Schedule Trigger (Monday 06:00 AST)
→ HTTP POST /internal/weekly-brief/{tenant_id} per active tenant
→ Flask renders brief.html Jinja2 template with weekly data
→ Resend sends HTML email to tenant admin email
→ Logged to digest_logs
```
### Embedding + comparable matching (Sunday night — Analyse tier, in MVP)
```
Schedule Trigger (Sunday 23:00)
→ For each active tenant:
→ HTTP POST /internal/embed-products/{tenant_id}
→ Embeds any un-embedded competitor products AND client_trips
→ HTTP POST /internal/match-comparable/{tenant_id}
→ Semantic similarity matching, upserted into comparable_matches
→ client_trips itself is populated continuously by every /research
submission (embedding_service.save_client_trip()) — this workflow
only embeds and matches what's already there, it never creates
client_trips records itself.
```
Not to be confused with the Intelligence-tier catalogue discovery
workflow below, which is a different, deferred feature.
### Sunday night catalogue discovery (Intelligence tier — NOT in MVP)
```
DEFERRED — do not build until Intelligence tier development begins.
Requires Firecrawl self-hosted which is not in the current stack.
```
---
## 16. Stripe Integration
### Webhook events handled
```
checkout.session.completed
→ Create tenant in PocketBase
→ Set status=active
→ Generate welcome email with Sheet template download link
customer.subscription.updated
→ Update tier if plan changed
→ Update max_seats based on new tier
customer.subscription.deleted
→ Set tenant status=inactive
→ All seats blocked at next cache expiry (max 6 hours)
```
### Tier → seat mapping
```python
TIER_SEATS = {
"analyse": 5,
"discover": 8,
"intelligence": 10,
"enterprise": 999 # effectively unlimited
}
# Additional seats available on any tier
ADDITIONAL_SEAT_PRICE = 45 # USD per seat per month
# Configured as a Stripe add-on price on each subscription
# Enterprise tier is not in Stripe public product list
# Created manually per client as a custom Stripe price
```
---
## 17. Environment Variables
```bash
# Flask
FLASK_ENV=production
API_SECRET_KEY=
# PocketBase
POCKETBASE_URL=http://pocketbase:8090
POCKETBASE_ADMIN_EMAIL=
POCKETBASE_ADMIN_PASSWORD=
# OpenRouter
OPENROUTER_API_KEY=
# MVP launch default: paid-first — see §10 "OpenRouter Model Cycling"
# for why. Set to "true" to re-enable free-tier cycling; FREE_MODELS
# stays populated below so that's a one-line switch.
OPENROUTER_USE_FREE_MODELS_FIRST=false
OPENROUTER_FREE_MODELS=openai/gpt-oss-120b:free,nvidia/nemotron-3-super-120b-a12b:free,meta-llama/llama-3.3-70b-instruct:free
OPENROUTER_FALLBACK_MODEL=deepseek/deepseek-v4-flash # paid — used directly at launch (USE_FREE_MODELS_FIRST=false), or as fallback once free tier is re-enabled. Not minimax/minimax-m2.5 — see §10, mandatory-reasoning model, 100% failure rate on the real extraction prompt
# Proxy — Primary (Webshare)
WEBSHARE_USER=
WEBSHARE_PASS=
WEBSHARE_API_KEY= # dashboard API token (Authorization: Token <key>) — different credential from WEBSHARE_USER/PASS above (those are proxy-gateway auth); used only to list the account's own dedicated IPs for candidate rotation. Generate from the Webshare dashboard's API/account section.
# Proxy — Fallback (Bright Data — pay-as-you-go, auto-escalated per domain)
BRIGHTDATA_USER=
BRIGHTDATA_PASS=
# Proxy settings
PROXY_ESCALATION_THRESHOLD=2 # blocked_count before treating domain as permanently blocked
# Stripe
STRIPE_SECRET_KEY=
STRIPE_WEBHOOK_SECRET=
STRIPE_PRICE_ANALYSE= # trial_period_days: 14
STRIPE_PRICE_DISCOVER= # trial_period_days: 21
STRIPE_PRICE_INTELLIGENCE= # trial_period_days: 30
# Enterprise: created manually per client — no trial, no env var
# Transactional email
RESEND_API_KEY=
# Error tracking
SENTRY_DSN= # Required in staging and production — never skip
# Hetzner Object Storage (backups)
HETZNER_S3_ACCESS_KEY=
HETZNER_S3_SECRET_KEY=
HETZNER_S3_ENDPOINT=https://fsn1.your-objectstorage.com
HETZNER_S3_BUCKET=bettersight-backups
# Gotify (owner monitoring — never client-facing)
GOTIFY_URL=https://gotify.bettertend.net
GOTIFY_APP_TOKEN=A78_l3r9y.ZzBr-
# Google Sheets (existing from app.py)
GOOGLE_SERVICE_ACCOUNT_JSON=
SHEET_ID=
STANDARD_TAB=
NGS_TAB=
```
---
## 18. Enterprise Software Development Standards
> These standards are absolute. They are never negotiated, never skipped,
> and never deferred to a later phase.
### Languages and Conventions
- Backend: Python only. No mixed languages in the same service.
- Frontend: JavaScript only. No TypeScript. No `.ts` files.
- `snake_case` for Python. `camelCase` for JavaScript.
- No abbreviations unless universally understood (id, url, pdf).
### Separation of Concerns
**Backend layer hierarchy — no layer skips another:**
```
Routes (api.py)
→ validate input, call services, return response
→ NO business logic. NO direct DB calls. NO external API calls.
Services (services/)
→ all business logic and orchestration
→ NO HTTP request/response objects
→ NO direct DB queries or external API calls
→ calls repositories and other services only
Repositories (repositories/)
→ all data access (PocketBase reads/writes, external API calls)
→ NO business logic
→ NO service calls
→ returns plain dicts only
jobs.py
→ imports and calls service functions only
→ NO direct DB access
→ NO business logic
```
### Security
- Every authenticated route checks `X-API-Key` against `API_SECRET_KEY` env var
- Every DB query for non-admin operations is tenant-scoped
(`tenant_id` filter on every PocketBase query)
- All credentials from env vars — hardcoded credentials are a build failure
- Stripe webhook validated via signature before any processing
- No stack traces in production error responses
- Seat validation checks domain and seat count — both every time
### Observability
- Every route logs: method, path, tenant_id (if known), duration, status code
- Every RQ job logs: job_id, tenant_id, start, completion, error if any
- `/health` endpoint always returns `{ status: "ok", version }` — no auth required
- Sentry configured via `SENTRY_DSN` env var in production
- n8n monitors `/health` every 5 minutes — Gotify alert on failure
### Resilience
- RQ job failures are logged to scrape_runs.error_log
- Failed scrapes do not fail the entire batch — continue to next competitor
- Side-effect failures (Gotify, email) never fail a primary operation
- All external API calls (OpenRouter, Bright Data, Firecrawl, Stripe) have
try/except with logged errors and graceful fallback
- RQ retries failed jobs automatically up to 3 times with backoff
### Code Commenting — Mandatory
**All code must be adequately commented. This is not optional.**
Every function, route, service method, and job function must have:
1. A docstring stating what the function does (one sentence)
2. The data flow — what comes in, what happens to it, what goes out
3. Inline comments on every meaningful step
```python
def validate_email(email: str, sheet_id: str) -> dict:
"""
Validates a Google user's access to Polaris via three checks:
domain registration, seat count, and Sheet ID binding.
Data flow:
email + sheet_id →
domain extraction → PocketBase tenant lookup (Layer 1) →
seat count check → seat registration if new (Layer 2) →
sheet_id binding on first use / comparison on subsequent (Layer 3) →
{ valid: bool, tier: str, reason: str }
"""
# Extract domain from email for tenant lookup
domain = email.split("@")[1]
# Layer 1: Check if domain is registered to an active or trial tenant
tenant = tenant_repository.get_by_domain(domain)
if not tenant:
return {"valid": False, "reason": "Domain not registered"}
if tenant["status"] not in VALID_STATUSES:
return {"valid": False, "reason": "Subscription inactive"}
# Layer 2: Check seat availability or register existing seat
seat_result = seat_repository.validate_or_register(tenant, email)
if not seat_result["valid"]:
return seat_result
return {"valid": True, "tier": tenant["tier"]}
```
### Testing
- 80% overall test coverage enforced — cannot merge below this
- 100% coverage on: licence validation, Stripe webhook, seat management, rollback paths
- Every repository has a mock implementation alongside the real one
- Unit tests for all services
- Integration tests for all API routes
- Tests live in `backend/tests/` mirroring the service/repository structure
### Environments and Deployment
- Three environments: development, staging, production
- Nothing reaches production without passing staging
- Production deploys require a tagged version — no unversioned production deploys
- Dokploy handles zero-downtime deploys via rolling restart
### Data and Backup
**Two-layer backup strategy — belt and braces:**
**Layer 1 — Litestream (real-time WAL streaming)**
- Streams every SQLite write to Hetzner Object Storage in real-time
- Recovery point objective: seconds
- Recovery time objective: under 5 minutes
- Runs as a sidecar container alongside PocketBase
**Layer 2 — Daily zip backup (point-in-time snapshots)**
- Daily 2am via crontab on the VPS — survives container restarts
- 7-day retention on Hetzner Object Storage
- Recovery point objective: 24 hours (fallback if Litestream stream is corrupted)
Implemented in `scripts/backup-pocketbase.sh` and
`scripts/bettersight-backup.cron` — see that file for exact install
steps (`cp` to `/usr/local/bin/`, `chmod +x`, `cp` the crontab entry to
`/etc/cron.d/bettersight-backup`). Requires an `rclone` remote named
`hetzner-s3` configured on the VPS with the `HETZNER_S3_*` credentials.
Cron output is redirected to `/var/log/bettersight-backup.log` (a
small addition beyond the original spec below, for debuggability).
```bash
# /etc/cron.d/bettersight-backup
0 2 * * * root /usr/local/bin/backup-pocketbase.sh
# backup-pocketbase.sh
#!/bin/bash
DATE=$(date +%Y%m%d)
docker exec pocketbase /pb/pocketbase backup \
--dir /pb/backups --name backup_$DATE.zip
rclone copy /pb/backups/ \
hetzner-s3:bettersight-backups/ \
--max-age 7d
find /pb/backups -name "*.zip" -mtime +2 -delete
```
- Audit logs (alerts, digest_logs) are append-only — no update or delete permitted
- Multi-step operations (scrape → store → alert) documented with rollback strategy
### Strict Rules — Never Violate
- Every authenticated endpoint validates `X-API-Key` — no exceptions
- Every DB query for non-admin roles includes `tenant_id` filter — no exceptions
- All credentials from env vars — hardcoded values are a build failure
- No layer skips another in the separation of concerns hierarchy
- A side-effect failure never fails a primary operation
- Every multi-step operation has a documented rollback strategy
- Mock implementations ship alongside every real repository
- 80% test coverage enforced by CI — cannot merge below this
- 100% coverage on payment, auth, webhook, and rollback paths
- Nothing reaches production without passing staging
- Production deploys require a tagged version
- Audit logs are append-only — no update or delete ever
- Daily backup with off-server storage — no exceptions
- Sentry configured in staging AND production — silent failures are unacceptable
- `/health` endpoint exists and is monitored — automated alert on failure
- All code and functions are adequately commented — uncommented code is a build failure
- Every Flask error response is structured JSON — never plain text
- Every page has a defined empty state — no blank pages ever
- Rate limiting applied to every public endpoint via Flask-Limiter
- PII (email addresses) never sent to Sentry — scrubbed before send
- Data retention policy enforced by weekly n8n cleanup workflow
- GDPR deletion endpoint exists and tested before launch
- Session expiry handled gracefully — PM returned to exact page after re-login
- In-progress analysis jobs survive session expiry — resume on return
---
## 19. Build Order
Follow this sequence. Do not start a phase until the previous is complete and tested.
```
Phase 1 — Data foundation
1. PocketBase schema (all collections)
2. Repository layer (all repositories with mocks)
3. Service layer (licence, scrape, battlecard, embedding, brief, alert,
trip_finder)
4. Flask API routes (all endpoints including /research/find-urls)
5. RQ job queue setup (jobs.py, worker config)
6. Tests for all of the above (80% floor)
Phase 2 — Refactor app.py into modular structure
7. Create proxy_overrides PocketBase collection
8. Create repositories/proxy_repository.py
9. Create core/proxy_service.py — Webshare primary + Bright Data fallback
10. Create core/scraper.py — Playwright + playwright-stealth + ProxyService
11. Create core/extractor.py — OpenRouter cycling and JSON parsing
12. Create core/ai_fetch.py — AI web fetch fallback + ProxyService
13. Create industries/adventure_travel/fields.py
14. Create industries/adventure_travel/prompt.py
15. Create industries/adventure_travel/schema.py
16. Create config/client.py
17. Create services/trip_finder_service.py — find_competitor_trip_url(),
score_and_rank_urls(), find_all_competitor_urls()
18. Update jobs.py to import from core/ and industries/
19. Verify all existing functionality works identically after refactor
Phase 3 — Dashboard (PRIMARY — build before Apps Script)
20. Clone nuxt-ui-templates/dashboard-vue as frontend base
21. Configure Vue Router:
/login — auth page
/account — subscription, seats, template download
/competitors — add/edit competitor list
/analyse — trip-intent form + confirmation + results (CORE PAGE)
/battlecards — battlecard view
/activity — market movement feed
22. Pinia auth store — tenant_id, email, tier, JWT
23. api_service.js — Axios instance with X-API-Key header
24. LoginPage.vue — email/password + Google OAuth
25. AccountPage.vue — subscription card, seats table, template download
26. CompetitorsPage.vue — competitor list with status badges, add/edit modal
27. AnalysePage.vue — THE CORE PAGE:
a. Trip-intent form (destination, duration, style, competitor checkboxes)
b. [Find & Analyse] button → POST /research/find-urls
c. URL confirmation screen — PM confirms or swaps matches
d. [Confirm & Run] → POST /research → poll status
e. Per-competitor progress display during job
f. Results view — side-by-side comparison table
g. Confidence indicators per competitor
h. [Push to Sheet ▾] button with tab selector dropdown
i. Run history panel — last 5 runs with [Restore] option
28. Stripe integration — checkout, webhook, portal link
29. Resend email templates — welcome, trial reminders, daily digest, weekly brief
30. Freescout deploy — container + support.bettersight.io Pangolin route +
support@bettersight.io MX record + support link in dashboard footer
31. useOnboardingTour.js — Shepherd.js 5-step guided tour
32. tour.css — custom Shepherd styles matching Bettersight design system
33. data-tour anchor attributes on nav items and key components
34. PATCH /account/onboarding route
35. Tour trigger on ActivityPage.vue onMounted
36. Tour replay link on AccountPage.vue
Phase 4 — Apps Script (Sheet-initiated pull — simple)
31. Deploy as standalone Apps Script — not container-bound to any workbook
32. Validation.gs — validateLicence() email-only
33. Sync.gs — pullLatestResults(), getWorkbookTabs(), detectFieldRows_()
34. Sidebar.html — tab selector + [Pull latest results] + last-pull status
35. Sheet template tab — importable into any existing workbook
36. Flask GET /research/history/latest route (reuses history handler)
Phase 5 — Automation
37. n8n daily scrape cron — changed/stale competitors only
38. PocketBase price_history diff logic (change_amount, change_percent)
39. PocketBase is_new flag logic (first_seen detection)
40. Content-hash logic in scrape service — compute_content_hash(),
detect_change(), normalise_html()
41. Add content_hash, content_length, hash_algorithm fields to
scrape_runs collection
42. Verify daily cron sets competitor.change_detected on hash diff
and skips LLM extraction when hash unchanged + cache fresh
43. n8n PocketBase webhook → create price_change alert record in PocketBase
44. n8n PocketBase webhook → create new_product alert record in PocketBase
45. n8n daily digest email (6pm) — collects unread alerts → Resend email
46. n8n battlecard regeneration workflow (fires on scrape_runs complete)
47. n8n Monday brief workflow (6am — Jinja2 HTML → Resend email)
48. Dashboard bell icon + unread count from alerts table
49. Mark as read / dismiss on AlertCard.vue
Phase 6 — Semantic matching
50. embedding_service.py — embed_trip(), find_comparable_trips()
51. n8n embedding and matching workflows (Sunday night)
52. comparable_matches table populated and visible in dashboard
Phase 7 — Ship
53. Flask-Limiter configured on all public routes
54. Sentry initialised in Flask and Vue — staging first, then production
55. GDPR deletion endpoint POST /account/delete tested end to end
56. n8n weekly data cleanup workflow (retention policy)
57. Empty states implemented on all seven dashboard pages (incl. ResetPasswordPage)
58. Session expiry handling — token refresh interceptor + return-to logic
59. In-progress job survival across session expiry
60. Password reset — "Forgot password?" on LoginPage + ResetPasswordPage.vue
61. Timezone captured on first login (no signup form in scope) →
PATCH /account/timezone → stored in tenants.timezone
62. n8n daily digest uses timezone-aware send time per tenant (luxon)
63. Upgrade prompt on AccountPage — next tier card with [Upgrade now →] button
64. POST /billing/upgrade route — Stripe checkout for tier upgrade
65. Multi-currency display in ResultsTable — primary market hero + USD secondary
66. Litestream container added to docker-compose.yml — replicating PocketBase to Hetzner S3
67. Hetzner Object Storage backup configured — rclone cron daily 2am (belt + braces with Litestream)
68. robots.txt check added to core/scraper.py — check_robots_txt() before each scrape
69. OpenRouter paid fallback configured — OPENROUTER_FALLBACK_MODEL env var set
70. Scraping legal clause added to bettersight.io/terms
71. Privacy policy and terms pages live at bettersight.io/privacy and /terms
72. Google OAuth consent screen approved (requires privacy policy URL)
73. End to end test with real competitor data
74. Staging deploy
75. Tag version v1.0.0
76. Production deploy via Dokploy
```
---
## 20. Authentication — PocketBase Native
### Default: Email + Password
Standard PocketBase auth. User registers with email and password.
PocketBase issues JWT on login. Stored in Pinia auth store.
All Flask API calls include `Authorization: Bearer {jwt}` header.
### Option: Google OAuth
PocketBase handles the full OAuth flow natively — no custom auth code.
**Setup in PocketBase admin:**
1. Settings → Auth Providers → Google → Enable
2. Add Google OAuth Client ID and Secret from Google Cloud Console
3. Set redirect URL to `https://app.bettersight.io/auth/callback`
**Frontend flow:**
```javascript
// LoginPage.vue — Google OAuth button
const loginWithGoogle = async () => {
const authData = await pb.collection('users').authWithOAuth2({
provider: 'google'
})
// PocketBase returns verified email — same email used for licence validation
authStore.setUser(authData)
router.push('/account')
}
```
**Why this matters for licence validation:**
Google OAuth email is verified by Google — same email registered in tenant_seats.
Domain matching is perfectly reliable. No manual email entry. No typos.
`Session.getActiveUser().getEmail()` in Apps Script returns the exact same email.
### Login page design
- Email/password form as default (UForm + UInput + UButton)
- "Continue with Google" button below the form (UButton with Google icon)
- Trial messaging above the form — reads `?plan=` query param to show correct length:
```javascript
// LoginPage.vue
const plan = route.query.plan || 'analyse'
const TRIAL_COPY = {
analyse: '14-day free trial — no credit card required',
discover: '21-day free trial — no credit card required',
intelligence: '30-day free trial — no credit card required',
}
const trialCopy = TRIAL_COPY[plan] || TRIAL_COPY.analyse
```
- "Forgot password?" link below the email input field
- Link to privacy policy and terms below
### Password Reset Flow
PocketBase handles password reset natively via email.
The frontend needs two small additions only.
**LoginPage.vue — "Forgot password?" link:**
```javascript
// Triggers PocketBase native password reset email
async function requestPasswordReset() {
await pb.collection('users').requestPasswordReset(email.value)
toast.success('Reset link sent — check your inbox')
}
```
**`/reset-password` route — Vue Router:**
```javascript
// Reads token from URL params — PocketBase reset link format:
// app.bettersight.io/reset-password?token=xxx
async function confirmPasswordReset() {
await pb.collection('users').confirmPasswordReset(
route.query.token,
newPassword.value,
newPasswordConfirm.value
)
router.push('/login?reset=success')
}
```
**Add to Vue Router:**
```javascript
{ path: '/reset-password', component: ResetPasswordPage }
```
**Add `ResetPasswordPage.vue` to pages:**
- New password input + confirm password input
- Submit button calls `confirmPasswordReset()`
- Redirect to login with success toast on completion
- Error state if token is expired or invalid
---
## 21. Professional Onboarding and Brand Touches
### Transactional Emails via Resend
All system emails sent via Resend from `alerts@bettersight.io`.
Never use raw SMTP for user-facing emails.
```python
import resend
resend.api_key = os.getenv('RESEND_API_KEY')
def send_welcome_email(to_email: str, tenant_name: str, template_url: str):
"""
Sends branded welcome email on signup with Sheet template download link.
Data flow: tenant email + name + template URL → Resend API → inbox
"""
resend.Emails.send({
"from": "Bettersight <alerts@bettersight.io>",
"to": to_email,
"subject": "Welcome to Bettersight — you're all set",
"html": render_email_template('welcome', {
"name": tenant_name,
"template_url": template_url
})
})
```
**Email triggers and templates:**
| Trigger | Template | From |
|---|---|---|
| Signup complete | welcome.html | alerts@bettersight.io |
| Trial day 3 | checkin.html | alerts@bettersight.io |
| Trial day 7 | feature_tip.html | alerts@bettersight.io |
| Weekly brief | brief.html | brief@bettersight.io |
| Price alert | alert.html | alerts@bettersight.io |
| New product alert | alert.html | alerts@bettersight.io |
| Stripe receipt | Stripe native | billing@bettersight.io |
All HTML email templates in `backend/templates/emails/`.
Every template includes: Bettersight logo, branded header, consistent
typography, unsubscribe footer, and link to bettersight.io.
### Trial Periods
Trial periods are configured per Stripe price object:
```python
TRIAL_PERIODS = {
"analyse": 14, # 14 days — low price point, one person decides, value visible in first run
"discover": 21, # 21 days — slight committee risk at $499, needs second internal review
"intelligence": 30, # 30 days — $999 needs finance approval, signal pipeline needs time to prove value
"enterprise": 0, # No trial — custom demo and pilot conversation only
}
```
No credit card required to start any trial — configure in Stripe
checkout settings (payment_method_collection: 'if_required').
PocketBase tenant status flow:
```
signup → status = 'trial'
day 12 (Analyse) / day 19 (Discover) / day 28 (Intelligence)
→ n8n fires trial_reminder email (2 days before expiry)
trial expires → Stripe charges card (if provided) → status = 'active'
trial expires → no card provided → status = 'inactive' → access blocked
first Stripe charge succeeds → status = 'active'
```
Apps Script and dashboard allow access for both `trial` and `active` status:
```python
VALID_STATUSES = ['active', 'trial']
```
### Stripe Customer Portal
Self-service billing — clients manage their own subscription, upgrade, cancel.
No support tickets for billing changes.
```python
@app.route('/billing/portal', methods=['POST'])
def billing_portal():
"""
Creates a Stripe customer portal session for self-service billing.
Data flow: tenant_id → PocketBase stripe_customer_id →
Stripe portal session → redirect URL returned to frontend
"""
tenant = tenant_repository.get_by_id(request.json['tenant_id'])
session = stripe.billing_portal.Session.create(
customer=tenant['stripe_customer_id'],
return_url='https://app.bettersight.io/account'
)
return jsonify({'url': session.url})
```
### Onboarding Checklist
Displayed on AccountPage.vue after signup. Tracked in PocketBase tenant record.
Updated automatically as each action is completed. Collapses when all steps done.
```
onboarding_checklist (JSON field on tenants)
{
"add_competitor": false, // set true when first competitor is created
"run_analysis": false, // set true when first job completes
"template_imported": false, // set true from AccountPage confirmation
"sidebar_installed": false, // set true first time validateLicence() succeeds
"sync_from_sheet": false, // set true first time pullLatestResults() succeeds
"tour_completed": false, // set true when guided tour reaches final step
"tour_skipped": false, // set true when PM clicks Skip
"tour_step": 0 // last step reached — for resume on return
}
```
Each step checked off automatically when the action is completed.
Full checklist completion triggers the day-3 check-in email sequence.
---
### Guided Onboarding Tour
A 5-step guided tour fires automatically on first login using Shepherd.js.
It is the primary first-run experience — more active than the checklist,
which is passive.
**Install:**
```bash
npm install shepherd.js
```
**Tour triggers:**
- Fires automatically on first dashboard load when
`tour_completed = false AND tour_skipped = false`
- "Take the tour again" link on AccountPage for PMs who skipped
- Never fires again after `tour_completed = true`
- If PM closes the browser mid-tour, `tour_step` stores their last position
and the tour resumes from that step on next login
**Tour steps:**
```
Step 1 of 5 — Welcome (no anchor element — modal overlay)
┌─────────────────────────────────────────────────┐
│ 👋 Welcome to Bettersight │
│ │
│ Let's get you to your first competitive │
│ analysis in under 5 minutes. │
│ │
│ [Skip tour] [Let's go →] │
└─────────────────────────────────────────────────┘
Step 2 of 5 — Add a competitor (anchors to Competitors sidebar nav item)
┌─────────────────────────────────────────────────┐
│ 🔍 Add your first competitor │
│ │
│ Click Competitors in the sidebar to add the │
│ first company you want to track. │
│ │
│ You'll need their website URL and their │
│ primary operating market (e.g. United Kingdom).│
│ │
│ [← Back] [Go to Competitors →] │
└─────────────────────────────────────────────────┘
→ On [Go to Competitors →]: router.push('/competitors')
→ Tour pauses. Resumes when PM clicks [+ Add Competitor]
Step 3 of 5 — Primary market (anchors to primary_market dropdown in add form)
┌─────────────────────────────────────────────────┐
│ 🌍 Set the primary market │
│ │
│ This tells Bettersight which country to │
│ use when scraping — so you see the right │
│ pricing and currency for that market. │
│ │
│ [← Back] [Next →] │
└─────────────────────────────────────────────────┘
Step 4 of 5 — Run analysis (anchors to Analyse sidebar nav item)
┌─────────────────────────────────────────────────┐
│ ▶ Run your first analysis │
│ │
│ Describe the trip you want to research — │
│ destination, duration, and travel style. │
│ │
│ Bettersight finds the matching competitor │
│ pages automatically. No URLs to copy-paste. │
│ │
│ [← Back] [Go to Analyse →] │
└─────────────────────────────────────────────────┘
→ On [Go to Analyse →]: router.push('/analyse')
Step 5 of 5 — Sync to Sheet (anchors to SyncToSheet component)
┌─────────────────────────────────────────────────┐
│ 📊 Get results into your Sheet │
│ │
│ Once your analysis is complete, copy rows or │
│ open your workbook and click Bettersight → │
│ Pull latest results in the sidebar. │
│ │
│ [← Back] [Done — let's go ✓] │
└─────────────────────────────────────────────────┘
→ On [Done]: set tour_completed = true in PocketBase
→ Tour dismissed permanently
```
**Implementation — `composables/useOnboardingTour.js`:**
```javascript
import Shepherd from 'shepherd.js'
import 'shepherd.js/dist/css/shepherd.css'
import { useRouter } from 'vue-router'
import { useTenantStore } from '@/stores/tenant_store'
/**
* Provides the guided onboarding tour using Shepherd.js.
* Fires on first login. Resumes from last step if interrupted.
* Marks tour_completed in PocketBase on final step.
*
* Data flow:
* onboarding_checklist.tour_completed → false →
* Shepherd.Tour initialised and started →
* PM progresses through 5 steps →
* final step → PATCH /account/onboarding { tour_completed: true } →
* tour never fires again
*/
export function useOnboardingTour() {
const router = useRouter()
const tenantStore = useTenantStore()
const tour = new Shepherd.Tour({
useModalOverlay: true,
defaultStepOptions: {
cancelIcon: { enabled: true },
classes: 'bs-tour-step',
scrollTo: { behavior: 'smooth', block: 'center' },
when: {
cancel: () => markTourSkipped()
}
}
})
tour.addStep({
id: 'welcome',
text: `
<strong>Welcome to Bettersight 👋</strong><br><br>
Let's get you to your first competitive analysis
in under 5 minutes.
`,
buttons: [
{ text: 'Skip tour', action: tour.cancel,
classes: 'bs-btn-ghost' },
{ text: "Let's go →", action: tour.next,
classes: 'bs-btn-primary' }
]
})
tour.addStep({
id: 'add-competitor',
attachTo: { element: '[data-tour="nav-competitors"]',
on: 'right' },
text: `
<strong>Add your first competitor 🔍</strong><br><br>
Click Competitors to add the first company you want to track.
You'll need their website URL and primary operating market.
`,
buttons: [
{ text: '← Back', action: tour.back,
classes: 'bs-btn-ghost' },
{ text: 'Go to Competitors →',
action: () => {
router.push('/competitors')
saveTourStep(2)
tour.next()
},
classes: 'bs-btn-primary' }
]
})
tour.addStep({
id: 'primary-market',
attachTo: { element: '[data-tour="primary-market-field"]',
on: 'bottom' },
text: `
<strong>Set the primary market 🌍</strong><br><br>
This tells Bettersight which country to use when scraping
— so you see the right pricing and currency for that market.
`,
buttons: [
{ text: '← Back', action: tour.back,
classes: 'bs-btn-ghost' },
{ text: 'Next →', action: () => {
saveTourStep(3)
tour.next()
},
classes: 'bs-btn-primary' }
]
})
tour.addStep({
id: 'run-analysis',
attachTo: { element: '[data-tour="nav-analyse"]',
on: 'right' },
text: `
<strong>Run your first analysis ▶</strong><br><br>
Describe the trip you want to research — destination, duration,
and travel style. Bettersight finds matching competitor pages
automatically. No URLs needed.
`,
buttons: [
{ text: '← Back', action: tour.back,
classes: 'bs-btn-ghost' },
{ text: 'Go to Analyse →',
action: () => {
router.push('/analyse')
saveTourStep(4)
tour.next()
},
classes: 'bs-btn-primary' }
]
})
tour.addStep({
id: 'sync-to-sheet',
attachTo: { element: '[data-tour="sync-to-sheet"]',
on: 'top' },
text: `
<strong>Get results into your Sheet 📊</strong><br><br>
Copy rows to the clipboard from here, or open your workbook
and click Bettersight → Pull latest results in the sidebar.
`,
buttons: [
{ text: '← Back', action: tour.back,
classes: 'bs-btn-ghost' },
{ text: 'Done — let\'s go ✓',
action: () => {
markTourCompleted()
tour.complete()
},
classes: 'bs-btn-primary' }
]
})
async function saveTourStep(step) {
await tenantStore.updateOnboarding({ tour_step: step })
}
async function markTourCompleted() {
await tenantStore.updateOnboarding({
tour_completed: true,
tour_step: 5
})
}
async function markTourSkipped() {
await tenantStore.updateOnboarding({ tour_skipped: true })
}
function shouldShowTour(onboarding) {
return !onboarding.tour_completed && !onboarding.tour_skipped
}
function getResumeStep(onboarding) {
return onboarding.tour_step || 0
}
return { tour, shouldShowTour, getResumeStep }
}
```
**Trigger in `ActivityPage.vue` (first page after login):**
```javascript
// ActivityPage.vue — <script setup>
import { useOnboardingTour } from '@/composables/useOnboardingTour'
import { onMounted } from 'vue'
const { tour, shouldShowTour, getResumeStep } = useOnboardingTour()
const tenantStore = useTenantStore()
onMounted(() => {
const onboarding = tenantStore.onboarding_checklist
if (shouldShowTour(onboarding)) {
const resumeStep = getResumeStep(onboarding)
// Start from last reached step if returning mid-tour
tour.start()
if (resumeStep > 0) {
tour.show(resumeStep)
}
}
})
```
**data-tour anchor attributes — add to relevant elements:**
```html
<!-- Sidebar nav items -->
<a class="sb-item" data-tour="nav-competitors" ...>Competitors</a>
<a class="sb-item on" data-tour="nav-analyse" ...>Analyse</a>
<!-- CompetitorForm.vue -->
<USelect data-tour="primary-market-field" ... />
<!-- SyncToSheet.vue -->
<div data-tour="sync-to-sheet" ...>
```
**Custom tour styles — extend Bettersight design system:**
```css
/* assets/tour.css — imported in main.js */
.bs-tour-step .shepherd-content {
border-radius: 14px;
box-shadow: var(--card-hover);
border: 1px solid var(--border-2);
font-family: 'Inter', sans-serif;
max-width: 320px;
}
.bs-tour-step .shepherd-text {
font-size: 13.5px;
line-height: 1.6;
color: var(--t2);
padding: 16px 18px;
}
.bs-tour-step .shepherd-footer {
padding: 12px 18px;
border-top: 1px solid var(--border);
display: flex;
justify-content: space-between;
gap: 8px;
}
.bs-btn-primary {
background: linear-gradient(135deg, #1B8EF8, #0B7AE8);
color: white;
border: none;
border-radius: 8px;
padding: 7px 14px;
font-size: 12.5px;
font-weight: 600;
cursor: pointer;
}
.bs-btn-ghost {
background: transparent;
color: var(--t3);
border: 1px solid var(--border-2);
border-radius: 8px;
padding: 7px 14px;
font-size: 12.5px;
font-weight: 500;
cursor: pointer;
}
/* Progress dots at bottom of tour card */
.shepherd-progress {
display: flex;
gap: 5px;
justify-content: center;
padding-bottom: 12px;
}
.shepherd-progress-dot {
width: 6px;
height: 6px;
border-radius: 50%;
background: var(--border-2);
}
.shepherd-progress-dot.active {
background: var(--blue-vivid);
}
```
**Flask route — update onboarding state:**
```python
@app.route('/account/onboarding', methods=['PATCH'])
def update_onboarding():
"""
Updates individual fields in the tenant onboarding_checklist.
Called by the tour composable and by checklist auto-completion logic.
Data flow:
tenant_id (from JWT) + partial onboarding dict →
PocketBase tenants record →
merge with existing onboarding_checklist JSON →
return updated checklist
"""
tenant_id = get_tenant_from_jwt(request)
updates = request.json # partial dict — only fields being updated
tenant = tenant_repository.get_by_id(tenant_id)
checklist = {**tenant['onboarding_checklist'], **updates}
tenant_repository.update(tenant_id, {
'onboarding_checklist': checklist
})
return jsonify({'onboarding_checklist': checklist})
```
**AccountPage.vue — replay option:**
```html
<!-- In AccountPage.vue onboarding section -->
<p v-if="onboarding.tour_skipped || onboarding.tour_completed"
class="tour-replay-link"
@click="replayTour">
↺ Take the guided tour again
</p>
```
```javascript
function replayTour() {
tenantStore.updateOnboarding({
tour_completed: false,
tour_skipped: false,
tour_step: 0
})
tour.start()
}
```
**Add to package.json:**
```json
"shepherd.js": "^13.x"
```
### Privacy Policy and Terms
Required for Google OAuth and Stripe.
Two static pages hosted at:
- `bettersight.io/privacy`
- `bettersight.io/terms`
Generate via termly.io or equivalent.
Link from login page, email footers, and Freescout support portal.
### Support — Freescout
Self-hosted at `support.bettersight.io`.
Clients email `support@bettersight.io` — becomes a ticket automatically.
Reply from Freescout — client receives normal email back.
No client account or portal required.
Link "Contact support" in dashboard footer and account page.
---
## 22. Competitor CSV Bulk Import
---
### Overview
PMs with many competitors can bulk import via CSV instead of adding
one by one through the modal form. A pre-formatted template is always
available on CompetitorsPage to ensure correct column structure.
**Value:** Eliminates repetitive manual entry on onboarding.
Especially useful for Enterprise clients with 10+ competitors.
**Tier availability:** All tiers.
---
### CSV Template Format
```csv
name,website,catalogue_url,primary_market
Intrepid Travel,https://intrepidtravel.com,https://intrepidtravel.com/adventures,GB
Exodus Travels,https://exodustravels.com,https://exodustravels.com/trips,GB
Flash Pack,https://flashpack.com,https://flashpack.com/adventures,US
Kimkim,https://kimkim.com,https://kimkim.com/guided-trips,US
```
**Column rules:**
- `name` — required, text
- `website` — required, must be a valid URL
- `catalogue_url` — required, must be a valid URL
- `primary_market` — required, ISO 3166-1 alpha-2 country code (GB, US, AU, NZ, CA, DE, FR etc.)
- Max 50 rows per upload
- UTF-8 encoding only
---
### Frontend Flow — CompetitorsPage.vue
```
CompetitorsPage header buttons:
[+ Add Competitor] [↓ Download CSV Template] [↑ Upload CSV]
Step 1 — PM clicks [↓ Download CSV Template]
→ GET /account/competitors/template
→ Downloads bettersight_competitors_template.csv
→ Template has header row + 2 example rows commented out
Step 2 — PM fills template and clicks [↑ Upload CSV]
→ File picker — accepts .csv only
→ Frontend parses CSV immediately using PapaParse
→ Validates each row client-side before sending to API
Step 3 — Preview modal shown before import
┌──────────────────────────────────────────────────────┐
│ Import preview — 4 competitors │
│ ────────────────────────────────────────────────── │
│ ✓ Intrepid Travel intrepidtravel.com GB │
│ ✓ Exodus Travels exodustravels.com GB │
│ ⚠ Flash Pack flashpack.com — │ ← missing market
│ ✓ Kimkim kimkim.com US │
│ ────────────────────────────────────────────────── │
│ 3 valid · 1 error (fix before importing) │
│ │
│ [Cancel] [Import 3 valid rows] │
└──────────────────────────────────────────────────────┘
Step 4 — PM clicks [Import 3 valid rows]
→ POST /account/competitors/bulk with valid rows only
→ Success toast: "3 competitors imported successfully"
→ CompetitorsPage table refreshes
```
---
### Frontend CSV Parsing — PapaParse
```javascript
// components/shared/CsvUpload.vue
import Papa from 'papaparse'
/**
* Parses an uploaded CSV file and validates each row against
* the competitor schema. Returns valid and invalid rows separately
* for display in the preview modal before sending to API.
*
* Data flow:
* File input → PapaParse → raw rows →
* validateRow() per row →
* { valid: [], invalid: [] } returned to parent
*/
function parseCompetitorCsv(file) {
return new Promise((resolve, reject) => {
Papa.parse(file, {
header: true,
skipEmptyLines: true,
complete: (results) => {
const valid = []
const invalid = []
results.data.forEach((row, index) => {
const errors = validateRow(row, index + 2) // +2 for header row
if (errors.length === 0) {
valid.push(row)
} else {
invalid.push({ row, errors })
}
})
resolve({ valid, invalid })
},
error: (err) => reject(err)
})
})
}
function validateRow(row, lineNumber) {
const errors = []
const VALID_MARKETS = [
'GB', 'US', 'AU', 'NZ', 'CA', 'DE', 'FR', 'ES', 'IT',
'NL', 'ZA', 'IN', 'JP', 'SG', 'AE', 'BR', 'MX', 'TT'
]
if (!row.name?.trim()) {
errors.push(`Line ${lineNumber}: name is required`)
}
if (!isValidUrl(row.website)) {
errors.push(`Line ${lineNumber}: website must be a valid URL`)
}
if (!isValidUrl(row.catalogue_url)) {
errors.push(`Line ${lineNumber}: catalogue_url must be a valid URL`)
}
if (!VALID_MARKETS.includes(row.primary_market?.toUpperCase())) {
errors.push(`Line ${lineNumber}: primary_market must be a valid ISO country code`)
}
return errors
}
function isValidUrl(url) {
try {
new URL(url)
return true
} catch {
return false
}
}
```
---
### Flask Routes
```python
@app.route('/account/competitors/template', methods=['GET'])
def download_competitors_template():
"""
Returns a pre-formatted CSV template for bulk competitor import.
Includes header row and two commented example rows.
Data flow:
GET request → generate CSV string →
return as file attachment
"""
csv_content = (
"name,website,catalogue_url,primary_market\n"
"# Example: Intrepid Travel,https://intrepidtravel.com,"
"https://intrepidtravel.com/adventures,GB\n"
"# Example: Flash Pack,https://flashpack.com,"
"https://flashpack.com/adventures,US\n"
)
return Response(
csv_content,
mimetype='text/csv',
headers={
'Content-Disposition':
'attachment; filename=bettersight_competitors_template.csv'
}
)
@app.route('/account/competitors/bulk', methods=['POST'])
@limiter.limit("5 per hour")
def bulk_import_competitors():
"""
Bulk imports competitors from validated CSV rows.
Skips rows where a competitor with the same domain already exists
for this tenant — never creates duplicates.
Data flow:
validated rows array → for each row:
check for duplicate domain in tenant's competitors →
if new: create competitor record in PocketBase →
append to created list
return { created: N, skipped: N, errors: [] }
"""
tenant_id = get_tenant_from_jwt(request)
rows = request.json.get('rows', [])
if len(rows) > 50:
return error_response(
code='too_many_rows',
message='Maximum 50 competitors per upload.',
status=400
)
created = []
skipped = []
errors = []
for row in rows:
try:
domain = extract_domain(row['website'])
existing = competitor_repository.get_by_domain(
tenant_id, domain
)
if existing:
skipped.append(row['name'])
continue
competitor = competitor_repository.create({
'tenant_id': tenant_id,
'name': row['name'].strip(),
'website': row['website'].strip(),
'catalogue_url': row['catalogue_url'].strip(),
'primary_market': row['primary_market'].upper().strip(),
'active': True
})
created.append(row['name'])
except Exception as e:
errors.append({
'name': row.get('name', 'Unknown'),
'error': str(e)
})
return jsonify({
'created': len(created),
'skipped': len(skipped),
'errors': errors
})
```
---
### Add to project structure
```
frontend/src/components/shared/
CsvUpload.vue — file picker + PapaParse + preview modal
```
Add to package.json:
```json
"papaparse": "^5.x"
```
---
### Build order placement
Phase 3 — after CompetitorsPage.vue base is built:
```
After step 26 (CompetitorsPage.vue):
26a. GET /account/competitors/template route
26b. POST /account/competitors/bulk route
26c. CsvUpload.vue — file picker, PapaParse parsing, validation
26d. ImportPreviewModal.vue — valid/invalid row preview before submit
26e. [↓ Download CSV Template] and [↑ Upload CSV] buttons on CompetitorsPage
```
---
---
### Error Handling — JobProgress Failed State
Every scrape job has four possible terminal states. The dashboard must
handle all four explicitly — never show a blank or frozen state.
```
queued → spinner, position in queue shown
running → per-competitor progress with ⟳ spinner
complete → results table rendered
failed → error state with retry option
```
**Failed state UI on AnalysePage.vue:**
```
┌─────────────────────────────────────────────┐
│ ⚠ Analysis failed — Intrepid Travel │
│ │
│ The scraper could not access this page. │
│ This is usually a temporary block. │
│ │
│ [↺ Retry this competitor] [Skip & continue] │
└─────────────────────────────────────────────┘
```
**Failure types and messages:**
```python
FAILURE_MESSAGES = {
'scrape_blocked': "The scraper was blocked by this site. "
"Try again in a few hours or enter the URL manually.",
'scrape_timeout': "The page took too long to load. "
"This may be a temporary issue.",
'extract_failed': "The AI could not extract structured data from this page. "
"The page structure may have changed.",
'url_not_found': "No matching trip page was found for this competitor. "
"Try entering the URL manually.",
'proxy_error': "Proxy connection failed. "
"Retrying with fallback provider.",
}
```
**Flask API error responses — always structured:**
```python
# Every error response uses this format — never plain text
def error_response(code: str, message: str,
status: int = 400) -> tuple:
"""
Returns a structured JSON error response.
Never return plain text errors from any Flask route.
"""
return jsonify({
"error": True,
"code": code,
"message": message
}), status
```
**Vue global error handler — catches unhandled API errors:**
```javascript
// main.js
app.config.errorHandler = (err, instance, info) => {
if (err.response?.status === 401) {
authStore.clearSession()
router.push('/login?reason=session_expired')
return
}
if (err.response?.status === 429) {
toast.error('Too many requests. Please wait a moment.')
return
}
// Log to Sentry in production
if (import.meta.env.PROD) Sentry.captureException(err)
toast.error('Something went wrong. Please try again.')
}
```
---
### Empty States — First Run Experience
Every page must have a defined empty state. Empty states are not errors —
they are onboarding moments. Never show a blank page or an empty table.
**ActivityPage.vue — no events yet:**
```
┌─────────────────────────────────────────────┐
│ │
│ 📡 Nothing to report yet │
│ │
│ Market movements will appear here after │
│ your first overnight scrape (6am daily). │
│ │
│ In the meantime → [Run your first analysis]│
└─────────────────────────────────────────────┘
```
**AnalysePage.vue — no competitors added:**
```
┌─────────────────────────────────────────────┐
│ │
│ 🏁 Add competitors first │
│ │
│ You need at least one competitor to run │
│ an analysis. │
│ │
│ [→ Go to Competitors] │
└─────────────────────────────────────────────┘
```
**BattlecardsPage.vue — no runs yet:**
```
┌─────────────────────────────────────────────┐
│ │
│ 📋 No battlecards yet │
│ │
│ Battlecards are generated automatically │
│ after your first analysis run. │
│ │
│ [→ Run Analysis] │
└─────────────────────────────────────────────┘
```
**CompetitorsPage.vue — no competitors:**
```
┌─────────────────────────────────────────────┐
│ │
│ 🔍 Add your first competitor │
│ │
│ Add competitor websites to start tracking │
│ pricing, new products, and market moves. │
│ │
│ [+ Add Competitor] │
└─────────────────────────────────────────────┘
```
**Rule:** Every empty state includes exactly one clear action button
pointing to the logical next step. No dead ends.
---
### Rate Limiting — Flask-Limiter
Protects the queue from abuse and runaway client scripts.
Applied per IP and per API key.
```python
from flask_limiter import Limiter
from flask_limiter.util import get_remote_address
limiter = Limiter(
app=app,
key_func=get_remote_address,
storage_uri=f"redis://bettersight-redis:6379",
default_limits=["200 per hour", "30 per minute"]
)
# Per-route overrides
@app.route('/research', methods=['POST'])
@limiter.limit("10 per hour") # Max 10 analysis jobs per IP per hour
def research():
"""..."""
@app.route('/research/find-urls', methods=['POST'])
@limiter.limit("20 per hour") # URL matching is lighter than full scrape
def find_urls():
"""..."""
@app.route('/validate-email', methods=['POST'])
@limiter.limit("60 per hour") # Apps Script calls this on open
def validate_email():
"""..."""
```
**Rate limit exceeded response (429):**
```python
@app.errorhandler(429)
def rate_limit_exceeded(e):
return error_response(
code='rate_limit_exceeded',
message='Too many requests. Please wait before trying again.',
status=429
)
```
Add to requirements.txt:
```
Flask-Limiter==3.5.0
```
---
### GDPR and Data Compliance
**Scope:** Bettersight collects and stores:
- PM email addresses (personal data under GDPR)
- Usage logs and scrape history
- Scraped competitor data (public data — not personal)
**Data retention policy:**
```python
DATA_RETENTION = {
'scrape_runs': 90, # days — delete old job records after 90 days
'price_history': 365, # days — keep 1 year of pricing data
'alerts': 90, # days — delete read alerts after 90 days
'audit_logs': 730, # days — keep 2 years (compliance requirement)
'tenant_data': 0, # days — retained until account deletion requested
}
```
**n8n weekly cleanup workflow:**
```
Schedule Trigger (Sunday 03:00)
→ HTTP POST /internal/data-cleanup — one global call, not per-tenant
→ services/retention_service.run_weekly_cleanup():
DELETE scrape_runs older than 90 days
DELETE alerts older than 90 days where read = true (unread alerts
are never auto-deleted regardless of age)
DELETE price_history older than 365 days
→ counts logged (see billing_service's note on why there's no
audit_logs collection to log to)
```
`weekly_data_cleanup_cron.json` in `n8n/workflows/`.
**Data deletion endpoint (GDPR Article 17 — Right to Erasure):**
Implemented as `services/billing_service.delete_tenant_data()`, called
from the thin `POST /account/delete` route.
```python
def delete_tenant_data(tenant_id):
"""
Permanently deletes all tenant data on request.
Cancels Stripe subscription first, then purges PocketBase records.
Sends confirmation email via Resend.
Data flow:
tenant_id → cancel Stripe subscription (side effect, logged not
raised) → delete: tenant_seats, competitors, products,
price_history, alerts, scrape_runs, battlecards,
comparable_matches, client_trips →
delete: tenant record →
send deletion confirmation email (side effect, logged not raised) →
logged
"""
```
`client_trips` (Phase 6) is included even though it predates this
section — it's tenant-scoped product data the same as everything else
on the list.
**"log to audit_logs" note:** no `audit_logs` collection exists
anywhere in this schema — the closest candidates (`alerts`,
`digest_logs`) are either on the delete list itself or hold no PII.
Rather than invent new schema for one log line, the deletion is
recorded via standard Python logging (`logger.info`, visible in
Sentry/server logs). Revisit if a real compliance audit trail is ever
required. This also resolves an apparent tension with §18's "alerts
are append-only" note — that describes normal runtime retention, not
an override of a tenant's explicit erasure request, and alerts
describe competitor products, not the PM, so they carry no PII of
their own.
**Privacy policy must state:**
- What data is collected (email, usage data)
- How long it is retained (per DATA_RETENTION above)
- How to request deletion (email support@bettersight.io)
- That competitor data scraped is from public sources only
- That data is stored on Hetzner servers in EU region
**Google OAuth requirement:**
Google requires a privacy policy URL before approving OAuth consent screen.
Point to `bettersight.io/privacy` in the Google Cloud Console OAuth config.
---
### Session Management — JWT Expiry Handling
PocketBase JWTs expire after 30 minutes by default (configurable).
The dashboard must handle token expiry gracefully without losing state.
**PocketBase token refresh — auto-refresh before expiry:**
```javascript
// api_service.js
// PocketBase SDK handles token refresh automatically when using pb.authStore
// but manual API calls via Axios need an interceptor
axiosInstance.interceptors.response.use(
response => response,
async error => {
if (error.response?.status === 401) {
// Attempt token refresh
try {
await pb.collection('users').authRefresh()
// Retry the original request with new token
error.config.headers['Authorization'] =
`Bearer ${pb.authStore.token}`
return axiosInstance(error.config)
} catch (refreshError) {
// Refresh failed — session truly expired
authStore.clearSession()
// Preserve current route so PM returns after login
const returnTo = router.currentRoute.value.fullPath
router.push(`/login?return=${encodeURIComponent(returnTo)}`)
}
}
return Promise.reject(error)
}
)
```
**Return-to after login:**
```javascript
// LoginPage.vue — after successful login
const returnTo = route.query.return || '/activity'
router.push(returnTo)
// PM is returned exactly where they were before session expired
```
**PocketBase token lifetime configuration:**
```
PocketBase admin → Settings → Auth → Token duration: 7 days
```
7 days is appropriate for a B2B tool where PMs keep tabs open all week.
Reduces friction without meaningfully reducing security.
**In-progress analysis protection:**
If a session expires mid-analysis (job already queued), the job continues
running in the background. When the PM logs back in:
- job_id is stored in localStorage before redirect
- On return, AnalysePage checks localStorage for pending job_id
- Resumes polling the existing job automatically
```javascript
// Before redirect on 401 during analysis
if (analysisStore.activeJobId) {
localStorage.setItem('pending_job_id', analysisStore.activeJobId)
}
// On AnalysePage mount after login
const pendingJobId = localStorage.getItem('pending_job_id')
if (pendingJobId) {
localStorage.removeItem('pending_job_id')
analysisStore.resumePolling(pendingJobId)
}
```
---
### Observability — Sentry
**Required in staging and production. Never optional.**
Sentry catches silent failures — errors that don't reach the PM
but indicate broken functionality.
**Backend setup (Flask):**
```python
import sentry_sdk
from sentry_sdk.integrations.flask import FlaskIntegration
from sentry_sdk.integrations.rq import RqIntegration
sentry_sdk.init(
dsn=os.getenv('SENTRY_DSN'),
integrations=[FlaskIntegration(), RqIntegration()],
traces_sample_rate=0.1, # 10% of requests traced
environment=os.getenv('FLASK_ENV', 'production'),
# Never send PII to Sentry
send_default_pii=False,
before_send=scrub_sensitive_data
)
def scrub_sensitive_data(event, hint):
"""
Removes email addresses, API keys, and tenant data
from Sentry events before sending.
PII must never reach Sentry.
"""
if 'request' in event:
event['request'].pop('headers', None)
return event
```
**Frontend setup (Vue 3):**
```javascript
// main.js
import * as Sentry from '@sentry/vue'
if (import.meta.env.PROD) {
Sentry.init({
app,
dsn: import.meta.env.VITE_SENTRY_DSN,
environment: import.meta.env.VITE_ENV,
tracesSampleRate: 0.1,
// Never send user emails to Sentry
beforeSend: (event) => {
if (event.user) delete event.user.email
return event
}
})
}
```
**Add to requirements.txt:**
```
sentry-sdk[flask]==1.44.0
```
**Add to frontend package.json:**
```
"@sentry/vue": "^7.x"
```
**Add to .env.example:**
```bash
SENTRY_DSN= # Required in staging + production
VITE_SENTRY_DSN= # Frontend Sentry DSN (can be same project)
VITE_ENV=production # staging | production
```
**What Sentry catches that you'd otherwise miss:**
- RQ job failures with full stack traces
- Flask 500 errors with request context
- Vue component crashes
- Failed Resend email sends
- PocketBase write failures
- Stripe webhook processing errors
---
### Flask-Limiter Strict Rules
- Rate limits use Redis as storage backend — consistent across all Flask workers
- Every public endpoint has a rate limit — no unprotected routes
- 429 responses are structured JSON — never plain text
- Rate limit headers included in all responses (X-RateLimit-*)
- Internal routes (/internal/*) exempt from rate limiting — called by n8n only,
protected by X-API-Key header instead
- OPTIONS requests are exempt from rate limiting entirely (`extensions.py`'s
`_exempt_cors_preflight` request_filter) — a browser's CORS preflight is
not a real request and carries no X-API-Key, but Flask-Limiter counts it
against the same bucket as the real request unless explicitly filtered.
Discovered when analysis_store.js's 3s status-poll loop tripped a 429
on the preflight itself within under a minute: each cross-origin fetch
fires OPTIONS before the real GET, doubling counted requests against
the 30/minute default. A failed preflight has no response body, so the
browser reports it as an opaque network error — this is why it showed
up client-side as the generic "Lost connection" fallback rather than a
429 with a real message.
- Polled endpoints need their own `@limiter.limit(..., override_defaults=True)`
rather than the blanket default_limits — `/research/status/<job_id>` is
polled every 3s for up to 20 minutes (analysis_store.js's
`POLL_TIMEOUT_ATTEMPTS`), which alone exceeds the 200/hour default
before a single job even finishes. Set to 3000/hour (50/minute
sustained) — comfortably covers one job's worst case plus several
concurrent jobs/tabs, still bounded well below anything meaningful for
a runaway script against this cheap, read-only lookup. `override_defaults=True`
is required — without it, Flask-Limiter applies the route's own limit
*in addition to* the blanket defaults, not instead of them, so the
200/hour ceiling would still apply underneath a higher per-route limit.
**Price:** $999/month (target $1,499/month with case study)
**Seats:** 10 (additional at $45/seat/month)
**Build phase:** After Analyse tier is live with paying clients
---
### Philosophy
The Intelligence tier shifts Bettersight from reactive to predictive.
Analyse tells you what happened. Intelligence tells you what is about to happen.
All data collected is from public OSINT sources only. No human intelligence,
no social engineering, no non-public information. The edge is aggregation,
pattern recognition, and vertical specificity — not source exclusivity.
Model outputs are probabilistic, not certain. Frame this as a feature:
"When six independent signals all point the same direction, the direction
is almost certainly right — even without access to private financials."
---
### Signal Sources — 15+ per competitor
**Product signals**
```
Job postings
Source: LinkedIn Jobs API, Adzuna API
Signal: Destination-specific roles → expansion intent 3-6 months ahead
Cost: ~$50/month
App store updates
Source: App Store Connect RSS, Google Play API
Signal: Update frequency, feature release notes, rating trajectory
Cost: AppFollow ~$39/month or Apify scraper free tier
```
**People signals**
```
Headcount tracking
Source: Proxycurl LinkedIn API
Signal: Team growth/contraction by department, senior hire patterns
Cost: ~$0.01/contact lookup, ~$30-60/month at scale
Executive social monitoring
Source: Twitter/X API, LinkedIn posts
Signal: Topic and tone shifts in executive communications reveal strategic priorities
Cost: Twitter API Basic ~$100/month
Glassdoor tracking
Source: Glassdoor public scrape via Apify
Signal: Rating trajectory, salary bands, culture signals, review sentiment
Cost: ~$20/month Apify credits
```
**Financial signals**
```
Funding and investment
Source: Crunchbase API, PitchBook (manual quarterly)
Signal: New funding = war chest signal, investor type reveals exit intent
Cost: Crunchbase ~$99/month
UK/AU regulatory filings
Source: Companies House API (UK, free), ASIC (Australia, free)
Signal: Filed accounts reveal revenue trajectory, shareholder loans,
director payments — available for Intrepid (AU), Exodus (UK),
Flash Pack (UK) as private companies
Cost: Free
Revenue proxies for fully private companies
Source: SimilarWeb, TripAdvisor review velocity, app download estimates
Signal: Traffic + review volume + departure frequency × known pricing
= reliable revenue range estimate
Cost: SimilarWeb API ~$99/month
```
**Marketing signals**
```
SEO and PPC tracking
Source: SpyFu API or SimilarWeb
Signal: Keyword bidding changes reveal priority destination investment
Cost: SpyFu ~$99/month
Domain registrations
Source: WHOIS API (WhoisXML)
Signal: New country TLDs and product subdomains = market entry signal
Cost: ~$20/month
```
**Positioning signals — Wayback Machine**
```
Wayback Machine positioning history
Source: Internet Archive Wayback Machine CDX API (free)
Signal: Tracks homepage messaging, pricing page copy, destination focus
changes over months and years — reveals strategic pivots before
any public announcement
Cost: Free
Implementation:
Weekly: Fetch current page snapshot hash via Firecrawl /scrape
Compare: Against stored Wayback snapshots at 30/60/90/180 day intervals
Extract: Key messaging changes using AI diff prompt
Flag: Significant repositioning events (new tagline, new hero destination,
new audience language) as high-priority intelligence signals
CDX API endpoint:
http://web.archive.org/cdx/search/cdx?url={domain}&output=json&limit=10&fl=timestamp,statuscode
```
**Market signals**
```
Review sentiment
Source: Apify Google Reviews, TripAdvisor scraper
Signal: Satisfaction deterioration = window to capture their clients
Cost: ~$30/month Apify credits
News and press monitoring
Source: NewsAPI, GDELT (free)
Signal: Trade publication mentions, press release cadence, tone shifts
Cost: NewsAPI ~$49/month, GDELT free
Conference and event tracking
Source: Industry event websites, speaker list scraping
Signal: Who's presenting where reveals strategic priorities and investment
Cost: Firecrawl /scrape against target conference sites, negligible
```
---
### Network Mapping
Network mapping reveals coordination and strategic alignment invisible
in raw signal data. It answers: who knows whom, and what does that mean?
**What is mapped per competitor:**
```
Leadership network
CEO, CMO, CPO, CFO LinkedIn profiles
→ Their connections to: investors, board members, advisors, ex-colleagues
→ Reveals: advisory relationships, talent pipeline sources, investor access
Board and investor network
Source: Crunchbase investor list, Companies House director filings
→ Cross-reference: shared investors across competitors
→ Signals: coordinated funding rounds, acquisition targets, market consolidation
Alumni network
Former employees tracked via LinkedIn career history
→ Where did Intrepid's last 10 departures go?
→ Signals: talent migration patterns, culture signals, competitor intel
(purely observational — never approached for information)
Partnership signals
Press release mentions of partnerships, integrations, distribution deals
→ Reveals distribution strategy and channel investment
```
**PocketBase collections for network mapping:**
```
competitor_people
id auto
competitor_id relation → competitors
name text
role text
linkedin_url text
seniority select [c_suite, vp, director, manager]
active bool (current employee)
first_seen autodate
last_seen date
competitor_network
id auto
person_id relation → competitor_people
connection_name text
connection_type select [investor, board, advisor, alumni, partner]
connection_org text
signal_weight number (0-1, how significant this connection is)
source text
discovered_at autodate
network_overlaps
id auto
tenant_id relation → tenants
entity_name text (the shared connection)
competitor_a relation → competitors
competitor_b relation → competitors
overlap_type select [investor, board, advisor, alumni]
significance number
notes text
discovered_at autodate
```
**Network mapping weekly workflow (n8n):**
```
Sunday night (after signal harvest)
→ Proxycurl fetch for each tracked executive
→ Extract: current role, connections count, recent activity
→ Compare against stored profiles
→ Flag: role changes, new board appointments, investor announcements
→ Update competitor_people and competitor_network tables
→ Run network_overlaps detection across all competitors for this tenant
```
---
### Signal Scoring Engine — `services/signal_service.py`
```python
SIGNAL_WEIGHTS = {
# Product signals — high predictive value
'job_posting_new_destination': 0.85,
'job_posting_volume_spike': 0.70,
'domain_registration_new': 0.90,
'app_update_frequency_spike': 0.55,
# People signals — medium-high predictive value
'headcount_growth_10pct': 0.75,
'senior_hire_destination': 0.80,
'executive_departure_ceo': 0.70,
'glassdoor_score_drop': 0.60,
# Financial signals
'funding_round_announced': 0.85,
'uk_filing_revenue_growth': 0.80,
'traffic_growth_20pct': 0.65,
# Marketing signals
'ppc_spend_spike_destination': 0.75,
'new_destination_keyword_bid': 0.80,
'seo_ranking_new_destination': 0.70,
# Positioning signals
'wayback_homepage_change': 0.65,
'wayback_pricing_change': 0.85,
'wayback_destination_focus': 0.80,
# Market signals
'review_sentiment_drop': 0.60,
'review_volume_spike': 0.65,
'press_release_new_market': 0.75,
'conference_new_destination': 0.70,
}
TRAJECTORY_THRESHOLDS = {
'strong_expansion': 0.75, # 75%+ weighted signal confidence
'moderate_expansion': 0.55,
'holding': 0.35,
'contracting': 0.20,
}
def calculate_trajectory(competitor_id: str, tenant_id: str) -> dict:
"""
Aggregates all signals for a competitor over the past 30 days.
Weights each signal by type and recency.
Returns a trajectory score, confidence level, and top contributing signals.
Data flow:
competitor_id → PocketBase signal_events (last 30 days) →
weight each signal by SIGNAL_WEIGHTS × recency decay →
sum weighted scores → normalise to 0-1 →
map to trajectory label via TRAJECTORY_THRESHOLDS →
return { score, trajectory, confidence, top_signals, predicted_move }
"""
```
---
### PocketBase — Additional Collections for Intelligence Tier
```
signal_events
id auto
tenant_id relation → tenants
competitor_id relation → competitors
signal_type text (matches SIGNAL_WEIGHTS keys)
signal_value json (raw signal data)
weight number (from SIGNAL_WEIGHTS)
source text (which data source)
detected_at autodate
competitor_trajectories
id auto
tenant_id relation → tenants
competitor_id relation → competitors
score number (0-1)
trajectory select [strong_expansion, moderate_expansion, holding, contracting]
confidence number (0-1)
top_signals json (array of contributing signals)
predicted_move text (AI-generated narrative prediction)
calculated_at autodate
wayback_snapshots
id auto
competitor_id relation → competitors
url text
snapshot_date date
content_hash text
messaging_summary text (AI-extracted key messages)
diff_from_prev text (AI-generated diff narrative)
change_detected bool
captured_at autodate
```
---
### Intelligence Tier Flask Routes
```
Internal (n8n calls):
POST /internal/signal-harvest/{tenant_id} — run full signal harvest
POST /internal/wayback-check/{competitor_id} — fetch and diff Wayback snapshot
POST /internal/network-map/{competitor_id} — update network mapping
POST /internal/trajectory/{tenant_id} — calculate trajectory scores
POST /internal/intelligence-report/{tenant_id} — generate monthly narrative report
Dashboard:
GET /intelligence/trajectory/{tenant_id} — all competitor trajectory scores
GET /intelligence/signals/{competitor_id} — signal event history
GET /intelligence/network/{competitor_id} — network map data
GET /intelligence/wayback/{competitor_id} — positioning history timeline
GET /intelligence/report/{tenant_id} — latest monthly report
```
---
### Intelligence Tier n8n Workflows
```
Weekly signal harvest (Saturday night)
→ For each Intelligence tier tenant:
→ Run signal harvest across all 15 sources per competitor
→ Store signal_events in PocketBase
→ Run trajectory calculation
→ Update competitor_trajectories
Wayback Machine weekly check
→ For each active competitor URL:
→ Fetch current snapshot via Firecrawl /scrape
→ Compare hash against last stored snapshot
→ If changed: fetch Wayback CDX for diff context
→ AI prompt generates messaging change narrative
→ Store wayback_snapshots record
→ If significant change: fire Gotify early warning alert
Network mapping weekly update (Sunday night)
→ Proxycurl fetch for tracked executives
→ Compare against stored profiles
→ Flag role changes, new connections
→ Run network_overlaps detection
Monthly intelligence report (first Monday of month)
→ Pull all signal_events for the period
→ Pull trajectory scores
→ Pull wayback diffs
→ Pull network changes
→ Claude Haiku generates narrative with predicted moves and confidence ratings
→ Resend HTML email to Intelligence tier tenants
→ Stored in PocketBase for dashboard access
```
---
### Running Cost for Intelligence Tier
| Source | Tool | Monthly cost |
|---|---|---|
| Job postings | Adzuna API | ~$50 |
| Headcount | Proxycurl | ~$50 |
| Funding | Crunchbase API | ~$99 |
| SEO/PPC | SpyFu | ~$99 |
| Domains | WhoisXML API | ~$20 |
| Reviews | Apify credits | ~$30 |
| News | NewsAPI | ~$49 |
| App store | AppFollow | ~$39 |
| Social monitoring | Twitter/X API Basic | ~$100 |
| Wayback Machine | CDX API | Free |
| Companies House | UK API | Free |
| GDELT | News API | Free |
| SimilarWeb | Traffic estimates | ~$99 |
| **Total** | | **~$635/month** |
At $999/month per client, break even at 1 client.
At $1,499/month, margin is $864/client/month.
At 5 Intelligence clients: $4,320/month net margin on data costs alone.
---
## 24. Brand and Positioning Reference
**Product name:** Bettersight
**Domain:** bettersight.io
**Dashboard URL:** app.bettersight.io
**Alert email:** alerts@bettersight.io
**Brief email:** brief@bettersight.io
**Support email:** support@bettersight.io
**Primary tagline:** Better sight, better moves.
**Secondary tagline:** Better sight, better decisions.
**Brand positioning:**
Bettersight is the competitive intelligence layer built specifically for adventure
travel — delivering the market clarity your product team needs to price smarter,
move faster, and stay ahead of every competitor, automatically.
**Core brand idea:** You can't lead the market you can't see.
**Key messages:**
- The only competitive intelligence platform that understands adventure travel
- Not just that a page changed — what trip was added, what price moved, what it means
- Turns hours of manual competitor research into instant, structured intelligence
- Your competitors updated their pricing last night. Bettersight knew before morning.
**Target channels:** LinkedIn (primary), cold email, product communities (adventure travel operators), G Adventures referral network
---
## 25. UI Design System
### Philosophy
The dashboard aesthetic is derived from the dark glass UI reference image
but translated to a professional light theme. Cards float significantly
above the page background. The dot grid motif from the dark UI is preserved
as a signature element throughout. The result feels like a premium SaaS
product — not a generic template.
**Do not use a dark theme.** The dashboard is light. The sidebar is the
only dark element. This is intentional and must not change.
---
### Colour Tokens
```css
/* Brand blues — derived from Bettersight B mark gradient */
--blue-vivid: #1B8EF8; /* primary action, links, active states */
--blue-bright: #3B9EFF; /* hover states */
--blue-light: #E8F4FF; /* tint backgrounds, badge fills */
--blue-mid: #B8D9FF; /* button hover tints */
--cyan: #00D4FF; /* accent, logo highlight, dot grid top colour */
--cyan-light: #E0F8FF; /* cyan badge fill */
/* Green accent — from Bettersight tagline card */
--green: #10D98A; /* positive metrics, growth signals */
--green-light: #D4F9ED; /* green badge fill */
--green-dark: #059669; /* green text on light backgrounds */
/* Semantic */
--red: #F04438; /* price drops, alerts, negative signals */
--red-light: #FEE4E2;
--amber: #F79009; /* warnings, changed status */
--amber-light: #FEF0C7;
/* Page and surface */
--page: #EEF1F6; /* cool blue-grey page background */
--surface: #FFFFFF; /* card background */
--surface-2: #F8FAFD; /* tab bars, table headers, hover states */
--border: rgba(28,56,121,.09);
--border-2: rgba(28,56,121,.14);
/* Sidebar */
--sidebar-bg: #0C1A3A; /* only dark element in the UI */
/* Text */
--t1: #0D1526; /* primary headings */
--t2: #2D3A52; /* body text */
--t3: #6B7A99; /* secondary text, metadata */
--t4: #9BA8C0; /* placeholder, timestamps, muted */
```
---
### Typography
**Font:** Inter (Google Fonts)
**Weights used:** 400, 500, 600, 700, 800, 900
```css
/* Scale */
--text-xs: 11px; /* timestamps, labels, tab counts */
--text-sm: 12.5px; /* metadata, secondary text */
--text-base: 13.5px; /* body, table cells, feed titles */
--text-md: 15px; /* topbar title, section headers */
--text-stat: 32px; /* stat card values — font-weight: 900 */
/* Stat values use gradient text for primary metrics */
.blue-num {
background: linear-gradient(135deg, #1B8EF8, #00D4FF);
-webkit-background-clip: text;
-webkit-text-fill-color: transparent;
}
.green-num {
background: linear-gradient(135deg, #10D98A, #00C9A7);
-webkit-background-clip: text;
-webkit-text-fill-color: transparent;
}
```
---
### Card System
Cards are the central design element. They must float visibly above the
page background using a three-layer shadow system.
```css
/* Standard card */
--card-radius: 18px;
--card-shadow:
0 2px 8px rgba(12,26,60,.06),
0 8px 28px rgba(12,26,60,.09),
0 0 0 1px rgba(28,56,121,.06);
/* Card hover state */
--card-hover:
0 4px 16px rgba(12,26,60,.10),
0 16px 48px rgba(12,26,60,.13),
0 0 0 1px rgba(27,142,248,.15);
/* Cards lift 2px on hover */
transform: translateY(-2px);
transition: box-shadow .2s, transform .2s;
/* Stat cards have a 3px colour accent bar at top */
.card::after {
content: '';
position: absolute;
top: 0; left: 0; right: 0;
height: 3px;
border-radius: 18px 18px 0 0;
}
.card.blue::after { background: linear-gradient(90deg, #1B8EF8, #00D4FF); }
.card.green::after { background: linear-gradient(90deg, #10D98A, #00C9A7); }
.card.red::after { background: #F04438; }
.card.amber::after { background: #F79009; }
```
---
### Dot Grid Motif — Signature Element
The dot grid is the single most distinctive visual element — derived
directly from the dark glass UI reference image and the Bettersight B
mark grid construction. It appears in three contexts:
**1. Stat card — bottom right corner (4 rows × 6 cols)**
Dots fade from low opacity top-left to full opacity bottom-right.
Colour matches the card's semantic colour (blue, green, red, amber).
**2. Feed row — left column (3×3 grid)**
Replaces a standard icon. Each feed row has a coloured dot grid
whose colour matches the event type (red = price drop, cyan = new product,
green = price increase, grey = removal).
**3. Table rows — left column (3×3 grid)**
Same pattern as feed rows — semantic colour per row based on price delta.
```svg
<!-- Standard 3×3 feed dot grid — red example -->
<svg width="28" height="28" viewBox="0 0 28 28" fill="none">
<!-- Opacity gradient: top-left dim → bottom-right full -->
<circle cx="4" cy="4" r="2" fill="#F04438" opacity=".20"/>
<circle cx="12" cy="4" r="2" fill="#F04438" opacity=".40"/>
<circle cx="20" cy="4" r="2" fill="#F04438" opacity=".65"/>
<circle cx="4" cy="12" r="2" fill="#F04438" opacity=".35"/>
<circle cx="12" cy="12" r="2" fill="#F04438" opacity=".60"/>
<circle cx="20" cy="12" r="2" fill="#F04438" opacity=".85"/>
<circle cx="4" cy="20" r="2" fill="#F04438" opacity=".55"/>
<circle cx="12" cy="20" r="2" fill="#F04438" opacity=".80"/>
<circle cx="20" cy="20" r="2" fill="#F04438" opacity="1"/>
</svg>
```
**Dot colour mapping:**
```
Red (#F04438) → price drops, alerts, negative signals
Cyan (#00D4FF → #1B8EF8) → new products, neutral intel
Green (#10D98A) → price increases, positive signals
Amber (#F79009) → warnings, changed status
Grey (#9BA8C0) → removals, neutral/inactive
```
**Rule:** Every feed row, table row, alert row, and progress row has
a dot grid in its left column. This is non-negotiable — it is the
visual signature of Bettersight's UI. Never replace with a plain icon.
---
### Page Background
The page background uses a subtle dot grid pattern to reference the
dark UI's connecting grid lines:
```css
body {
background: #EEF1F6;
background-image: radial-gradient(
circle, rgba(27,142,248,.07) 1px, transparent 1px
);
background-size: 28px 28px;
}
```
---
### Topbar
The topbar uses frosted glass — the only glass effect in the UI:
```css
.topbar {
background: rgba(255,255,255,.82);
backdrop-filter: blur(12px);
-webkit-backdrop-filter: blur(12px);
border-bottom: 1px solid rgba(28,56,121,.09);
height: 58px;
position: sticky;
top: 0;
z-index: 40;
}
```
---
### Sidebar
Dark indigo — the only dark element. Never lighten this.
```css
.sidebar {
background: #0C1A3A;
width: 232px;
}
/* Active nav item */
.nav-item.active {
background: rgba(27,142,248,.18);
color: #ffffff;
}
.nav-item.active .icon { color: #00D4FF; }
/* User plan chip */
.user-plan { color: rgba(255,255,255,.38); font-size: 11px; }
```
---
### Badge System
```css
/* All badges use semantic colour pairs */
.badge-red { background: #FEE4E2; color: #C02020; }
.badge-green { background: #D4F9ED; color: #059669; }
.badge-amber { background: #FEF0C7; color: #B45309; }
.badge-blue { background: #E8F4FF; color: #1B8EF8; }
.badge-cyan { background: #E0F8FF; color: #0284C7; }
.badge-gray { background: #F1F4FA; color: #6B7A99; }
/* All badges: 11px, font-weight 700, border-radius 20px */
padding: 2px 8px;
font-size: 11px;
font-weight: 700;
border-radius: 20px;
line-height: 17px;
```
---
### Primary Button
```css
.btn-primary {
background: linear-gradient(135deg, #1B8EF8, #0B7AE8);
color: #ffffff;
box-shadow: 0 2px 8px rgba(27,142,248,.35);
border-radius: 9px;
font-weight: 600;
font-size: 12.5px;
padding: 7px 14px;
}
.btn-primary:hover {
box-shadow: 0 4px 14px rgba(27,142,248,.45);
transform: translateY(-1px);
}
```
---
### Nuxt UI v4 Customisation
These tokens override the Nuxt UI v4 default theme. Set in `app.config.ts`:
```javascript
export default defineAppConfig({
ui: {
primary: 'blue', // maps to --blue-vivid
gray: 'slate',
card: {
rounded: 'rounded-[18px]',
shadow: 'shadow-[0_2px_8px_rgba(12,26,60,.06),0_8px_28px_rgba(12,26,60,.09)]',
},
badge: {
rounded: 'rounded-full',
},
button: {
rounded: 'rounded-[9px]',
}
}
})
```
The dot grid motif is a **custom Vue component** — not part of Nuxt UI:
```
components/
DotGrid.vue — reusable dot grid SVG, accepts: size, color, opacity-range
```
Used in every feed row, table row, alert row, and stat card.
---
### Reference Dashboard
The approved dashboard mockup is at:
`/mnt/user-data/outputs/bettersight-dashboard.html`
All new pages and components must be consistent with this design.
When in doubt, open the reference HTML file before writing component code.
---
## 27. What NOT to Build
Do not build any of the following until explicitly instructed:
- Discover tier ($499) scheduled /map crawl — Analyse uses /map on demand only
- Intelligence tier ($999) features — full OSINT signal pipeline (Section 23)
- Enterprise tier ($1,499) — unlisted, built manually per client
- Scorecard index
- Slack / Teams / WhatsApp notification integrations — Discover tier only
- Gotify push notifications for clients — owner monitoring only, never client-facing
- Zammad or other complex ticketing platforms — migrate from Freescout when 50+ clients
- Docling PDF brochure extraction
- WeasyPrint PDF generation (removed — use Resend HTML email)
- changedetection.io (removed — do not add back)
- Mobile app
- Public marketing site
- Admin dashboard for managing all tenants
- Human intelligence gathering of any kind — OSINT only
- Analysis UI in the Apps Script sidebar — dashboard only
- Custom Sheet functions (=BETTERSIGHT_PRICE() etc) — not needed
- REST API for external integrations — not in scope
- Real-time change detection webhooks. Daily cron with content-hash
comparison is sufficient for adventure travel pricing cycles. Do not
reintroduce Firecrawl Monitor, changedetection.io, or any other
event-driven change detection service. If a client explicitly requests
intra-day change detection, add a second cron slot at 6pm local time
rather than adding a new service.
- Supabase, Postgres for Firecrawl, or the Firecrawl credit accounting
system. Bettersight uses Firecrawl only for /v1/map (unauthenticated,
no credits consumed). Enabling auth/credits would create billing
coupling to Firecrawl Cloud which is not needed.
Note: Firecrawl /map IS in the Analyse tier MVP — used on demand
for trip-intent URL matching only. The scheduled weekly /map crawl
is Discover tier — do not build in MVP.
---
## 28. Version 2 — Custom Extraction Fields
**Rating: 9/10 — build after Analyse tier has paying clients**
### Why This Feature Matters
The current field set is rigid. If a PM renames a column header the
Sheet write breaks silently. More importantly, every adventure travel
operator has unique competitive dimensions that the standard field set
cannot capture — sustainability certifications, altitude gain, camp
quality, vehicle type, toilet facilities along the trail.
Custom extraction fields make the tool shaped around each client's
specific competitive framework rather than a generic template.
Switching cost goes from inconvenient to structural — the PM has built
their competitive analysis system inside Bettersight.
### Tier Placement
- **Discover ($499)** — up to 10 custom fields per tenant
- **Intelligence ($999)** — unlimited custom fields + field templates
### What the PM Does
```
Dashboard → Analyse → Custom Fields → Add Field
Field name: Camp Quality
Output type: select
Description: Rate the quality of accommodation on the trek
Options: budget camping / mid-range lodge / premium glamping / mixed
Prompt hint: Look for mentions of camping style, tent quality, lodge upgrades
Field name: Toilet Facilities
Output type: count
Description: Number of toilet facilities available along the trail
Prompt hint: Count flush toilets, pit latrines, and portable facilities separately
then sum. Mention in itinerary per day counts separately.
```
The PM defines the field. The system handles the extraction rigour.
### Output Type System
Eight output types. Each auto-injects the correct prompt instruction
level for that data type. The PM never writes prompt code.
---
**`text`** — Free text extraction. No special instructions injected.
```
Used for: positioning summary, hotel names, included activities
```
**`number`** — Decimal number. No special instructions injected.
```
Used for: price per kg, weight limits, distance in km
```
**`integer`** — Whole number. No special instructions injected.
```
Used for: group size, age limit, number of days at altitude
```
**`count`** — Summed integer. Auto-injects full counting instruction set.
Modelled on the meals field in app.py — the most carefully engineered
field in the existing prompt. The same rigour applies automatically
to any field the PM marks as count type.
```python
COUNT_FIELD_TEMPLATE = """
{field_name} COUNTING RULES:
- {description}
- Count every individual instance mentioned across the ENTIRE page content,
including itinerary day-by-day descriptions. Do not rely on a summary box
or header figure — read the full itinerary.
- Numbers may appear as digits (3) or words (three) — treat both as integers
and sum them together.
- If the same item appears on multiple itinerary days, count each occurrence
separately. Example: "Day 1: toilet block. Day 4: toilet block." = 2, not 1.
- If a range is given (2-3 facilities), use the lower number.
- If no information is found on the page, return 0, not null.
- Return a single integer only. No text, no description, no units.
"""
```
**`price`** — Currency value. Auto-injects rack rate disambiguation rules.
Modelled on the majorityPrice / priceLow / priceHigh logic in app.py.
The three most common failure modes for price extraction are: returning
a sale price, missing a price buried in a departures table, and
currency-converting when the native currency should be returned.
All three are handled by the auto-injected instruction.
```python
PRICE_FIELD_TEMPLATE = """
{field_name} EXTRACTION RULES:
- {description}
- Extract the standard adult rack rate only.
- IGNORE any price labelled as: sale, promotional, early bird, "was/now",
limited time, discount, or member price.
- Price may appear as "from $X", "per person from $X", or inside a
departures table or pricing grid — extract regardless of format.
- If a departures table shows multiple prices, use the most commonly
listed standard price across visible departures.
- Do NOT currency convert. If the page shows GBP only, return GBP value.
Leave other currency fields null.
- Return as a number only. No currency symbol, no commas, no text.
- If no standard price can be found, return null.
"""
```
**`rating`** — Scored integer with PM-defined rubric.
The current `relevancy` field (1-5) has no rubric — the AI infers
meaning from context, producing inconsistent scores across runs.
Any rating field requires the PM to define the scale so the prompt
generates a consistent scoring rubric.
PM defines:
```
Scale: 1 to 5
Min label: Not comparable — completely different destination/style
Max label: Directly comparable — same destination, duration, target market
```
Auto-generated prompt instruction:
```python
RATING_FIELD_TEMPLATE = """
{field_name}:
- {description}
- Score on a scale of {min} to {max} where:
{min} = {min_label}
{max} = {max_label}
- Use intermediate scores proportionally.
- Return as a single integer only. No explanation.
"""
```
**`select`** — One value from a PM-defined controlled vocabulary.
Prevents AI from inventing values. Keeps data clean for filtering
and sorting in the dashboard.
PM defines options: `budget camping / mid-range lodge / premium glamping / mixed`
Auto-generated prompt instruction:
```python
SELECT_FIELD_TEMPLATE = """
{field_name}:
- {description}
- Return ONLY one of the following values exactly as written:
{options_list}
- Choose the closest match based on the page content.
- Do not invent values, combine values, or return partial matches.
- If genuinely unclear, return the option that best describes the
majority of the experience.
"""
```
**`boolean`** — Yes/No answer. Handles partial mentions and implied values.
```python
BOOLEAN_FIELD_TEMPLATE = """
{field_name}:
- {description}
- Return true if clearly present or confirmed anywhere on the page.
- Return false if clearly absent or explicitly stated as not included.
- Return null if the page contains no relevant information either way.
- Do not infer from absence — only return false if explicitly stated.
"""
```
---
### Fields That Need Special Documentation (Not New Types)
These existing fields have interdependencies or failure modes that
should be surfaced as guidance in the UI when a PM creates similar
custom fields:
**Departure-dependent fields**
`departures` and `seasonality` are coupled — if departures are hidden
behind a "see more" button, seasonality must note this. The UI should
warn: "This field works best alongside a departure count field."
**Multi-part analysis fields**
`comments` asks for four things in one field (differences, strengths,
weaknesses, pricing position) and the AI delivers inconsistently.
For V2 custom fields, any field marked as `text` with multiple
sub-components should be split into separate fields. The UI should
suggest: "Consider creating separate fields for each analysis dimension."
---
### How Custom Fields Flow Through the System
```
PM defines custom field in dashboard
↓
Stored in PocketBase: tenant_custom_fields table
↓
On analysis run:
prompt.py reads tenant_custom_fields
→ generates field-specific instruction block per output type
→ appends to standard prompt JSON schema
→ LLM extracts standard + custom fields together
↓
Results stored in PocketBase:
products table has a custom_fields JSON column
{ "camp_quality": "mid-range lodge", "toilet_facilities": 4 }
↓
Dashboard results table:
standard columns + custom field columns rendered dynamically
↓
Sheet push (Apps Script):
detectColumns() finds custom field headers by name
writeResults() writes custom field values alongside standard fields
If header not found: adds new column automatically at end of sheet
```
### PocketBase Collections
```
tenant_custom_fields
id auto
tenant_id relation → tenants
field_name text (display name, e.g. "Camp Quality")
field_key text (snake_case, e.g. "camp_quality" — used in JSON)
output_type select [text, number, integer, count, price, rating, select, boolean]
description text (PM's plain language description)
prompt_hint text (optional extra guidance for the AI)
options json (for select type: ["budget camping", "mid-range lodge"])
rating_min integer (for rating type)
rating_max integer (for rating type)
rating_min_label text
rating_max_label text
active bool
sort_order integer (controls column order in output)
created autodate
```
### Prompt Builder Extension
```python
def build_custom_field_instructions(custom_fields: list) -> str:
"""
Generates prompt instruction blocks for all active custom fields
for a tenant. Each field gets the appropriate template for its
output type. Appended to the standard prompt before sending to LLM.
Data flow:
tenant_custom_fields list →
for each field: select template by output_type →
format template with field metadata →
join all blocks → return as string for prompt injection
"""
blocks = []
for field in custom_fields:
template = FIELD_TEMPLATES[field['output_type']]
blocks.append(template.format(
field_name=field['field_name'],
description=field['description'],
prompt_hint=field.get('prompt_hint', ''),
options_list=', '.join(field.get('options', [])),
min=field.get('rating_min', 1),
max=field.get('rating_max', 5),
min_label=field.get('rating_min_label', ''),
max_label=field.get('rating_max_label', '')
))
return '\n\n'.join(blocks)
FIELD_TEMPLATES = {
'text': '{field_name}: {description}. {prompt_hint}',
'number': '{field_name}: {description}. Return as decimal number only.',
'integer': '{field_name}: {description}. Return as whole number only.',
'count': COUNT_FIELD_TEMPLATE,
'price': PRICE_FIELD_TEMPLATE,
'rating': RATING_FIELD_TEMPLATE,
'select': SELECT_FIELD_TEMPLATE,
'boolean': BOOLEAN_FIELD_TEMPLATE,
}
```
### JSON Schema Extension
Custom fields are appended to the standard JSON schema returned by the LLM:
```python
def build_json_schema(is_ngs: bool, custom_fields: list) -> dict:
"""
Builds the full JSON extraction schema including standard fields
and any tenant custom fields. Custom fields use their field_key
as the JSON property name.
Data flow:
is_ngs flag → select standard schema (Standard or NGS) →
append custom field keys with output_type annotations →
return complete schema dict for prompt injection
"""
schema = STANDARD_SCHEMA if not is_ngs else NGS_SCHEMA
for field in custom_fields:
schema[field['field_key']] = f"{field['output_type']} — {field['description']}"
return schema
```
### Sheet Sync Handling
`detectColumns()` in Sync.gs handles custom fields automatically
by reading header names from row 1. No code change needed in the
Apps Script for new custom fields.
If a custom field header is not found in the sheet:
```javascript
// detectColumns() — when custom field header not found in sheet
// Appends new column at the end of the existing data range
// PM is notified via sidebar: "Added 1 new column: Camp Quality"
```
### Limits
```
Discover tier: max 10 custom fields per tenant
Intelligence tier: unlimited custom fields
```
Field key names cannot conflict with standard field names:
`tripName, tripCode, serviceLevel, groupSize, duration, meals,
startLocation, endLocation, departures, seasonality, startDays,
targetAudience, hotels, activities, relevancy, majorityPrice,
priceLow, priceHigh, comments, dateUsed, audPrice, cadPrice,
eurPrice, gbpPrice`
---
## 29. Monitoring and Alerting
All alerts delivered via Gotify to the owner's phone via iGotify.
No manual monitoring required — the system alerts when action is needed.
---
### Alert Priority Scale
```
Priority 10 — Critical → immediate action required
Priority 7 — Warning → action needed within the hour
Priority 5 — Advisory → awareness only, monitor
Priority 3 — Info → weekly summaries, no action needed
```
---
### Component 1 — n8n Queue and API Monitor (every 5 mins)
```
Schedule Trigger (every 5 mins)
→ HTTP GET redis-cli LLEN rq:queue:normal → queue depth
→ HTTP GET /health with response time measurement
→ Code node: evaluate all thresholds
→ If any threshold breached → HTTP POST to Gotify
→ Log result to PocketBase monitoring_events table
```
**Thresholds monitored:**
```python
THRESHOLDS = {
# Queue depth
'queue_warning': 5, # jobs waiting
'queue_critical': 10, # jobs waiting
# Job duration (stored on scrape_runs)
'job_slow_mins': 5, # minutes
'job_stuck_mins': 10, # minutes
# API health
'api_response_warn_ms': 2000, # milliseconds
'api_down': None, # any non-200 response
# Worker health
'workers_alive_min': 1, # at least one worker must respond
# Failure rate (rolling 1hr window)
'failure_rate_warn_pct': 10, # percent of jobs failed
'failure_rate_crit_pct': 25, # percent of jobs failed
# Fast path ratio (rolling 24hr window)
'fast_path_warn_pct': 40, # below this = cache not working
}
```
**Alert messages:**
```python
# Scale warning
if queue_depth >= THRESHOLDS['queue_critical']:
gotify.send(
title="🔴 Scale workers now",
message=f"Queue depth: {queue_depth} jobs waiting.\n"
f"Run: docker compose up -d "
f"--scale bettersight-worker=5",
priority=10
)
elif queue_depth >= THRESHOLDS['queue_warning']:
gotify.send(
title="🟡 Queue building",
message=f"Queue depth: {queue_depth} jobs waiting. "
f"Monitor closely.",
priority=5
)
# API down
if api_status != 200:
gotify.send(
title="🔴 Bettersight API is down",
message=f"GET /health returned {api_status}. "
f"Check Dokploy immediately.",
priority=10
)
# API slow
elif api_response_ms > THRESHOLDS['api_response_warn_ms']:
gotify.send(
title="🟡 API responding slowly",
message=f"/health took {api_response_ms}ms. "
f"May indicate memory pressure.",
priority=5
)
# Workers down
if workers_alive == 0:
gotify.send(
title="🔴 All workers down",
message="No RQ workers are responding. "
"Jobs will queue indefinitely. "
"Restart: docker compose restart bettersight-worker",
priority=10
)
```
---
### Component 2 — Job Duration Hook in jobs.py
Every job records its duration on completion. Long jobs trigger alerts.
```python
def run_research_job(job_id: str, data: dict):
"""
Wraps the research job with timing and alerting.
Records duration to scrape_runs on completion.
Fires Gotify alert if job exceeds duration thresholds.
Data flow:
job start → research logic runs →
duration calculated → PocketBase updated →
if slow: Gotify alert fired
"""
start = time.time()
try:
# ... existing job logic unchanged ...
_run_research_core(job_id, data)
status = 'complete'
except Exception as e:
status = 'failed'
logger.error(f'Job {job_id} failed: {str(e)}')
scrape_repository.update(job_id, {
'status': 'failed',
'error_log': str(e)
})
alert_service.send_gotify(
title="🔴 Analysis job failed",
message=f"Job failed for {data.get('tenant_id')}.\n"
f"Error: {str(e)[:120]}",
priority=7
)
return
finally:
duration = round(time.time() - start)
scrape_repository.update(job_id, {
'completed_at': datetime.now().isoformat(),
'duration_seconds': duration,
'status': status
})
# Alert on slow completion
if duration > 600: # 10 minutes = stuck
alert_service.send_gotify(
title="🔴 Job likely stuck",
message=f"Job took {round(duration/60, 1)} mins. "
f"Tenant: {data['tenant_id']}. "
f"Possible proxy or Playwright issue.",
priority=10
)
elif duration > 300: # 5 minutes = slow
alert_service.send_gotify(
title="🟡 Slow job detected",
message=f"Job took {round(duration/60, 1)} mins. "
f"Tenant: {data['tenant_id']}.",
priority=5
)
```
---
### Component 3 — Daily Cron Duration Alert (n8n)
```
After daily scrape cron completes:
→ Calculate total cron duration
→ Query: active tenant count
→ If duration > 2hrs → Gotify warning
→ If duration > 3hrs → Gotify critical (cron overlapping business hours)
```
```python
# In n8n Code node after cron completes
cron_duration_mins = (end_time - start_time) / 60
if cron_duration_mins > 180: # 3 hours
alert_service.send_gotify(
title="🔴 Daily cron overrunning",
message=f"Cron took {round(cron_duration_mins)} mins — "
f"overlapping business hours.\n"
f"Clients: {active_tenant_count}. "
f"Add workers immediately.",
priority=10
)
elif cron_duration_mins > 120: # 2 hours
alert_service.send_gotify(
title="🟡 Daily cron running long",
message=f"Cron took {round(cron_duration_mins)} mins.\n"
f"Clients: {active_tenant_count}. "
f"Consider adding workers soon.",
priority=7
)
```
---
### Component 4 — Weekly Performance Summary (Monday 6am with brief)
Fires after the Monday brief sends. No action required — informational only.
```python
def generate_weekly_performance_summary(tenant_id: str = None) -> dict:
"""
Queries scrape_runs for the past 7 days and calculates
performance metrics. Sent as a Gotify message Monday 6am
alongside the weekly brief.
Metrics:
total_jobs — total research jobs run
fast_path_pct — % that hit PocketBase cache
refresh_path_pct — % that triggered live scrape
avg_duration_secs — average job duration
failure_rate_pct — % of jobs that failed
peak_queue_depth — highest queue depth seen in the week
active_clients — number of active tenants
"""
```
**Sample Gotify message:**
```
📊 Weekly performance — Mon 16 Jun
Jobs run: 54
Fast path: 81% ✓
Avg duration: 11s
Failures: 0 ✓
Peak queue: 2
Active clients: 4
All systems healthy 🟢
```
```
📊 Weekly performance — Mon 16 Jun
Jobs run: 312
Fast path: 44% ⚠ below 40% target
Avg duration: 38s ⚠ slower than usual
Failures: 4 (1.3%)
Peak queue: 8 ⚠ near warning threshold
Active clients: 18
Consider scaling workers to 5.
Run: docker compose up -d --scale bettersight-worker=5
```
---
### Component 5 — Content-Hash Detection Health Check
The daily scrape cron's content-hash comparison must keep running — if
it silently stops writing `content_hash` on `scrape_runs`, change
detection breaks with no user-visible error.
```
n8n — daily 8am check:
→ Query PocketBase: scrape_runs from the last 24hrs where content_hash is empty
→ If any recent scrape_runs row is missing content_hash:
→ Gotify critical alert (hash computation broke in the scrape service)
→ If all recent scrape_runs have content_hash but change_detected
hasn't fired for any competitor in 7 days:
→ Gotify advisory (may mean competitor sites haven't changed — normal)
```
---
### PocketBase — monitoring_events collection
```
monitoring_events
id auto
event_type select [queue_depth, job_duration, api_health,
worker_health, cron_duration, failure_rate,
fast_path_ratio, weekly_summary]
severity select [info, advisory, warning, critical]
value number (the measured value)
threshold number (the threshold that was evaluated)
message text
alert_sent bool
created autodate
```
Stores every monitoring check result — not just alerts. Gives you a
history of system health over time accessible from PocketBase admin.
---
### alert_service.py
All Gotify calls go through a single service function.
Never call the Gotify API directly from jobs or n8n workflows.
```python
def send_gotify(title: str, message: str, priority: int = 5):
"""
Sends a push notification via Gotify to the owner's phone.
Logs the alert to monitoring_events in PocketBase.
Side-effect failure never propagates — alert failure is logged only.
Data flow:
title + message + priority →
HTTP POST gotify.bettertend.net/message →
monitoring_events record created regardless of delivery success
"""
try:
requests.post(
f"{GOTIFY_URL}/message",
headers={"X-Gotify-Key": GOTIFY_APP_TOKEN},
json={
"title": title,
"message": message,
"priority": priority
},
timeout=5
)
except Exception as e:
logger.error(f"Gotify alert failed: {str(e)}")
# Never raise — alert failure must not affect primary operations
```
---
### Scaling Decision Guide — received via Gotify
You never need to check a dashboard to know when to scale.
The alerts tell you what to do and give you the exact command.
| Alert received | Action |
|---|---|
| 🟡 Queue building (depth 5+) | Watch for 30 mins. If persists, scale to 5 workers. |
| 🔴 Scale workers now (depth 10+) | Run scale command immediately from phone. |
| 🟡 Daily cron running long (>2hrs) | Plan to scale workers within a week. |
| 🔴 Daily cron overrunning (>3hrs) | Scale workers today. |
| 🟡 Weekly summary — fast path < 40% | Investigate why cache is missing. Check the daily scrape cron's content-hash detection health. |
| 🟡 Weekly summary — 18+ clients noted | Proactively scale workers before next client onboards. |
**Scale command (copy from alert message):**
```bash
docker compose up -d --scale bettersight-worker=5
```
**Rollback if scaling causes issues:**
```bash
docker compose up -d --scale bettersight-worker=3
```
---
## 30. Web Scraping Legal Policy
### Position
Bettersight scrapes publicly accessible competitor websites to extract
pricing and product data. This data is available to any human visitor
to those sites. The scraping is:
- Limited to one request per competitor per day — human-realistic frequency
- Read-only — no data is ever written to competitor systems
- Used solely for competitive intelligence purposes by paying clients
- Targeting only publicly visible, non-authenticated pages
### Risk Profile
At MVP scale with 5-20 clients, enforcement risk is low. As Bettersight
grows and competitor operators notice the traffic, the risk increases.
The primary mechanism of enforcement would be:
- Competitor sends cease-and-desist to Bettersight directly
- Competitor sends cease-and-desist to the client using Bettersight
- Competitor blocks the IP range (mitigated by proxy rotation)
### Protective Measures — Built Into the Stack
1. **Rate limiting** — one scrape per competitor per day, human-realistic speed
2. **Proxy rotation** — Webshare + Bright Data prevent IP fingerprinting
3. **User-agent rotation** — playwright-stealth prevents browser fingerprinting
4. **robots.txt** — check `robots.txt` before scraping any new competitor URL
**robots.txt check — add to `core/scraper.py`:**
```python
def check_robots_txt(url: str) -> bool:
"""
Checks if the given URL is allowed to be scraped per robots.txt.
Returns True if scraping is permitted, False if disallowed.
Defaults to True if robots.txt cannot be fetched (permissive default).
Data flow:
url → extract root domain → fetch /robots.txt →
parse with urllib.robotparser →
check if Bettersight user-agent is allowed for this path
"""
from urllib.robotparser import RobotFileParser
from urllib.parse import urlparse
root = f"{urlparse(url).scheme}://{urlparse(url).netloc}"
rp = RobotFileParser()
rp.set_url(f"{root}/robots.txt")
try:
rp.read()
return rp.can_fetch('*', url)
except Exception:
return True # permissive default if robots.txt unreachable
```
### Bettersight Terms of Service — Scraping Clause
The following clause must appear in Bettersight's Terms of Service:
---
*"By using Bettersight, you (the client) acknowledge and agree that:*
*(a) Bettersight retrieves publicly available data from third-party websites
on your behalf as instructed by you. You are solely responsible for
ensuring that your use of Bettersight and any data retrieved through
it complies with the terms of service of any third-party website,
applicable law, and any other relevant obligations.*
*(b) Bettersight does not access any password-protected, subscription-gated,
or otherwise non-public content. All data retrieved is publicly accessible
to any internet user without authentication.*
*(c) Bettersight limits its retrieval activity to a frequency consistent with
normal human browsing behaviour and does not engage in denial-of-service
or scraping activity that would unreasonably burden any third-party server.*
*(d) Undo Designs LLC accepts no liability for any claims arising from a
third party in connection with data retrieved from their website on your
behalf. You agree to indemnify and hold harmless Undo Designs LLC from
any such claims."*
---
This clause shifts responsibility to the client, which is the standard
approach used by competitive intelligence platforms including Crayon,
Klue, and Contify.
---
## 31. Monthly Running Cost Projection
At MVP scale the product is profitable from client one.
| Service | Cost | Notes |
|---|---|---|
| Hetzner VPS (CX31) | ~$15/month | 2 vCPU, 8GB RAM — sufficient for 20+ clients |
| Webshare proxies | ~$3/month | Residential rotating, 100 proxies |
| Bright Data | ~$0-5/month | Pay-as-you-go fallback only |
| OpenRouter (free tier) | ~$0/month | Free models handle 90%+ of extractions |
| OpenRouter (paid fallback) | ~$2-5/month | claude-haiku-4-5 at $0.25/1M tokens |
| Resend | $0/month | Free up to 3,000 emails/month |
| Hetzner Object Storage | ~$3/month | Backups + Litestream replication |
| n8n | $0/month | Self-hosted on same VPS |
| Freescout | $0/month | Self-hosted on same VPS |
| Sentry | $0/month | Free tier (5k errors/month) |
| Firecrawl | $0/month | Self-hosted on same VPS |
| **Total fixed** | **~$28-31/month** | |
**Break-even:** 1 client at $349/month
**Gross margin at 5 clients:** ($349 × 5) - $31 = **$1,714/month (~98%)**
**Gross margin at 10 clients:** ($349 × 10) - $31 = **$3,459/month (~99%)**
Stripe fees (2.9% + $0.30 per charge) are the only meaningful variable cost:
- At 10 clients: ~$10.50/month in Stripe fees
- Net margin at 10 clients: ~$3,449/month
**Scaling cost trigger:**
When daily cron duration exceeds 2 hours (roughly 20+ clients):
- Upgrade VPS to CX41 (4 vCPU, 16GB RAM): ~$30/month (+$15)
- Still profitable from client 1
---
## 32. First Client Onboarding Runbook
Before self-serve signup is live you will onboard the first client manually.
This runbook ensures nothing is missed.
### Pre-onboarding checklist
```
□ Client has signed up via bettersight.io (or you create manually)
□ Stripe subscription created — correct tier, trial applied
□ PocketBase tenant record created with correct tier and domain
□ Tenant seats created for all PM email addresses
□ Freescout ticket opened as "Onboarding: {client name}" for tracking
```
### Manual PocketBase setup
```bash
# Create tenant via PocketBase admin UI or API
POST /api/collections/tenants/records
{
"name": "G Adventures",
"email": "pm@gadventures.com",
"domain": "gadventures.com",
"tier": "analyse",
"status": "trial",
"trial_ends_at": "2026-07-09", # 14 days from today
"max_seats": 5,
"timezone": "America/Toronto",
"stripe_customer_id": "cus_xxx",
"stripe_subscription_id": "sub_xxx",
"onboarding_checklist": {
"add_competitor": false,
"run_analysis": false,
"template_imported": false,
"sidebar_installed": false,
"sync_from_sheet": false,
"tour_completed": false,
"tour_skipped": false,
"tour_step": 0
}
}
```
### What to send the client
**Email 1 — Welcome (same day)**
Subject: Welcome to Bettersight — you're all set
```
Hi [Name],
Welcome to Bettersight. You're set up and ready to go.
Here's how to get started in 5 minutes:
1. Sign in at app.bettersight.io using [their email]
2. Add your first competitor (Competitors → + Add Competitor)
3. Run your first analysis (Analyse → describe your trip)
4. Push the results to your Google Sheet
The guided tour will walk you through each step when you first log in.
A few things to know:
- Your 14-day trial starts today
- You have 5 seats — add your team at Account → Seats
- Questions? Reply to this email or visit support.bettersight.io
Better sight, better moves.
Jason
Bettersight
```
**Email 2 — Trial day 3 check-in (automated via n8n)**
Already configured — fires automatically.
**Sheet template**
Send the template download link from app.bettersight.io/account.
They import it into their existing Google Sheet workbook.
**Apps Script install link**
Send the standalone script install link.
Takes 30 seconds to install — works in any workbook.
### During the trial — touch points
```
Day 1: Welcome email (above)
Day 3: Automated check-in email (n8n)
Day 7: Automated feature tip email (n8n)
Day 10: Manual personal email — "How's the first week going?"
Day 12: Automated trial expiry reminder (n8n — 2 days before)
Day 14: Trial ends — Stripe auto-charges if card on file
```
### Post-trial conversion call
If the client hasn't upgraded by day 12, send a personal email:
```
Hi [Name],
Your Bettersight trial ends in 2 days. I wanted to check in —
has the analysis been useful so far?
If you've hit any friction at all, I'm happy to jump on a
15-minute call to make sure you're getting the most out of it
before the trial ends.
Either way, your subscription will continue automatically on
[date] unless you cancel.
Jason
```
### Internal tracking
Keep a simple spreadsheet (or Notion page) with:
```
Client name | Tier | Trial start | Trial end | Converted? | MRR | Notes
```
Update after every client action. This is your first customer success dashboard
before you build anything automated.
Reference in New Issue View Git Blame Copy Permalink