Local database (Cloud SQL proxy + migrations)

Connect your laptop to the shared Cloud SQL instance and apply Flyway SQL migrations via ./gradlew :server:dbMigrate (no full server startup, no global flyway install).

Prerequisites

Google Cloud SDK (gcloud)
No global Flyway install required — npm run migrate uses ./gradlew :server:dbMigrate (Flyway 11.x from Gradle). Optional: Flyway CLI with FLYWAY_USE_CLI=1.
cloud-sql-proxy on PATH (install )
IAM: roles/cloudsql.client on project oter-490318
Secret Manager access to read the Postgres password secret

One-time auth:


gcloud auth login
gcloud config set project oter-490318
gcloud auth application-default login

One-time: secret id in `.env`

The password lives in Secret Manager. You need the secret id (resource name), not the password text.

Find it (pick one):


gcloud secrets list --project=oter-490318
 
gcloud run services describe SERVICE \
  --region=us-east1 \
  --project=oter-490318 \
  --format='yaml(spec.template.spec.containers[0].env)'
# → DB_PASSWORD → valueFrom.secretKeyRef.name
 
# GitHub: Repo → Settings → Actions → Variables → GCP_SM_DB_PASSWORD

Add to repo-root .env (never commit real values):


DB_SECRET_ID=your-secret-id-here

Variables (reference)

Variable	Required	Default	Purpose
`DB_SECRET_ID`	Yes*	—	Secret Manager secret id for Postgres password
`GCP_SM_DB_PASSWORD`	Yes*	—	Alias for `DB_SECRET_ID`
`DB_PASSWORD`	Yes*	—	Skip gcloud fetch if set directly
`DB_NAME`	No	`oter_prod`	Database name
`DB_USER`	No	`postgres`	Postgres user
`POSTGRES_HOST`	No	`127.0.0.1`	Proxy listen host
`POSTGRES_PORT`	No	`5432`	Proxy listen port
`GCP_PROJECT_ID`	No	`oter-490318`	For `gcloud secrets`
`CLOUD_SQL_INSTANCE`	Must be unset	—	If set, server uses socket driver, not the proxy

* One of DB_SECRET_ID, GCP_SM_DB_PASSWORD, or DB_PASSWORD is required.


unset CLOUD_SQL_INSTANCE

Migrate (typical workflow)

Terminal 1 — leave running:


npm run proxy

Terminal 2:


npm run migrate

db-migrate.sh loads .env, fetches the password, then runs ./gradlew :server:dbMigrate (Flyway via JDBC, no HTTP server).

Optional Flyway CLI (if installed): FLYWAY_USE_CLI=1 npm run migrate

Manual equivalent:


unset CLOUD_SQL_INSTANCE
export DB_SECRET_ID=your-secret-id
source scripts/db-export-env.sh
npm run migrate

Verify:


psql "postgresql://${DB_USER}:${DB_PASSWORD}@${POSTGRES_HOST}:${POSTGRES_PORT}/${DB_NAME}" -c 'SELECT 1'
 
psql "postgresql://${DB_USER}:${DB_PASSWORD}@${POSTGRES_HOST}:${POSTGRES_PORT}/${DB_NAME}" \
  -c "SELECT version, description, success FROM flyway_schema_history ORDER BY installed_rank DESC LIMIT 10;"

Flyway CLI vs server startup

	`npm run migrate`	`./gradlew :server:run`
Flyway SQL	Yes (`:server:dbMigrate`)	Yes (embedded)
Exposed JDBC sync	No	Yes
Starts HTTP server	No	Yes

npm run migrate is for applying new SQL migrations to an existing database (e.g. shared oter_prod).

Server startup still runs Exposed sync + Flyway on deploy/local run. Some older migrations assume tables created by Exposed sync (e.g. V10__work_tracking.sql references work_sessions). Do not use Flyway CLI alone on a brand-new empty database unless the baseline already matches production.

Run local server against Cloud SQL


source scripts/db-export-env.sh
./gradlew :server:run

Troubleshooting

Symptom	Fix
`flyway: command not found` (manual CLI)	Use `npm run migrate` (Gradle), or `FLYWAY_USE_CLI=1` after installing CLI
`password authentication failed`	Set `DB_SECRET_ID` in `.env`
Nothing on `:5432`	Run `npm run proxy`
`invalid_grant` / `invalid_rapt`	`gcloud auth application-default login`
Flyway missing table	DB may need server once for Exposed sync, or baseline is incomplete
Server uses socket not proxy	`unset CLOUD_SQL_INSTANCE`

Scripts

Script	Purpose
`scripts/db-proxy.sh`	`npm run proxy`
`scripts/db-export-env.sh`	Env + Secret Manager password
`scripts/db-migrate.sh`	`npm run migrate` → `:server:dbMigrate`