DEPRECATED: Services migrating to Z2 (800145). See reference_z2-service-catalog for current endpoints. Only Zuko's portfolio recruiter front-end stays on the VPS edge.

jimmy-vps Hinata Collector — Design Notes

project: hinata-infrastructuretype: design-notesstatus: deprecating (800145)owner: jimmy-neutroncreated: 2026-05-24 Historical design notes for the VPS collector. jimmy-vps-hinata-collector.sh deleted 2026-06-11 (dead-script law).

A single multi-tenant API service hosted on jimmy-vps. One FastAPI process, one Postgres database, one Tailscale endpoint, one shared x-hinata-key. Each Studio app gets a route prefix and a Postgres schema — never its own service.

Why one service, not many

On 1 GB RAM:

TopologyMemorysystemd unitsAuth keysFailure surface

1 service multi-tenant~50 MB111 process to monitor N services per-app~50 MB × NNNN processes, N restart loops, N logs

The collector pattern collapses per-app Tailscale ACLs, per-app deploy scripts, per-app backups to one config.

Which Studio tabs eventually live here

AppSchema(s)Route prefixWhy it needs server-side

MusicMasterymusicmastery.events``/musicmastery/*Event stream, adaptive engine cohort baselines, cross-device sync Bulmabulma.transactions, bulma.accounts, bulma.balances``/bulma/*TrueLayer / Monzo OAuth refresh tokens must NOT live in browser Zepilezepile.listings, zepile.scrape_runs``/zepile/*Rightmove / Zoopla scrapers can't run in-browser (CORS) AllMightallmight.events``/allmight/*Event stream, time-of-day + streak analytics, cross-device Zoro (maybe)zoro.sets, zoro.sessions``/zoro/*Migration target from Excel/OneDrive when querying beats spreadsheet UX

How to add a new tenant

1. Schema file — write [app]-schema-bigquery.sql alongside this script (BigQuery dialect = source of truth).

2. Add to APP_SCHEMAS array in the install script: APP_SCHEMAS=(musicmastery bulma)

3. Inline the Postgres DDL in the script's section 3 (a new heredoc block).

4. Add a router file in section 4: app/routers/[app].py

5. Mount the router in app/main.py: app.include_router(bulma.router, prefix="/bulma", tags=["bulma"])

6. Re-run the install script. Everything is idempotent.

Schema dialect

Source of truth: [app]-schema-bigquery.sql (BigQuery Standard SQL / GoogleSQL). Michael's daily-driver dialect at VMO2 is BigQuery. Migration to a real BigQuery dataset (or DuckDB, Snowflake, Trino) is a bq load from pg_dump --inserts — no schema rewrite.

BigQueryPostgres runtime

INT64``BIGSERIAL (auto-id) STRING``TEXTBOOL``BOOLEANFLOAT64``DOUBLE PRECISIONTIMESTAMP``TIMESTAMPTZCURRENT_TIMESTAMP()``CURRENT_TIMESTAMPPARTITION BY DATE(ts)Skipped — small row count CLUSTER BY mode, question_keyReplaced with B-tree indexes

Query dialect — portable subset

All SELECTs in any router are written in the subset that runs unchanged in both Postgres and BigQuery.

Don't useUse insteadWhy

COUNT(*) FILTER (WHERE …)``SUM(CASE WHEN … THEN 1 ELSE 0 END)FILTER is Postgres-only … ::floatnothing (AVG/STDDEV already return float-ish in both):: cast is Postgres-only COUNTIF(…)``SUM(CASE WHEN … THEN 1 ELSE 0 END)COUNTIF is BigQuery-only GENERATE_SERIES(…)Hardcode or app-sidePostgres-only

Auth

Single API key in x-hinata-key header shared across all apps. Same value lives in:

• ${SERVICE_HOME}/.env on the VPS (mode 0600, owned by service user)

• Vaultwarden (CT103, 192.168.1.250) — credential store (Itachi = source of truth)

• Hinata Studio's MusicMastery (and future Bulma/…) Settings panel

Exposure

Service binds 0.0.0.0:8080 — but the only interface Tailscale advertises in our tailnet is tailscale0. The GCP firewall on 34.13.8.155 does not open port 8080 to the public internet. Reachable only over the tailnet via jimmy-vps.[tailnet].ts.net:8080.

Operational notes

* **Logs:** `sudo journalctl -u hinata-collector -f`

* **DB shell:** `sudo -u postgres psql -d hinata`

* **List schemas:** `\dn` inside psql

* **List tables in a schema:** `\dt musicmastery.*`

* **Re-run with new API key:** rotate `$HINATA_API_KEY` in env, re-run the script

* **Backup snapshot now:** `sudo -u postgres pg_dump hinata > hinata_$(date +%Y%m%d).sql`

* **Restore on new host:** `psql -d hinata < hinata_YYYYMMDD.sql`

◆ hinata · projects/hinata-infrastructure/install-scripts/jimmy-vps-hinata-collector.html · phase-19 conversion