Vitals

# Use the Kotlin Multiplatform wizard: https://kmp.jetbrains.com/
# Pick: Android + iOS, "Do not share UI" (native UIs).
# Then verify the shared module builds:
./gradlew :shared:assemble

// shared/src/commonMain/kotlin/health/Model.kt
package health

import kotlinx.datetime.Clock
import kotlinx.datetime.Instant
import kotlinx.serialization.Serializable

enum class MetricKind { WEIGHT, STEPS, HEART_RATE, ACTIVE_ENERGY, DISTANCE }

enum class MetricUnit { KG, LB, COUNT, BPM, KCAL, KM, MI }

// @Serializable: the SyncApi round-trips Measurement as JSON, so it (and every embedded
// type) must be serializable. The kotlinx.datetime.Instant fields use kotlinx-datetime's
// built-in serializer -> RFC-3339 strings on the wire (the format the SyncApi Detail pins).
@Serializable
data class Measurement(
    val id: String,            // client-generated UUID, stable across sync
    val kind: MetricKind,
    val value: Double,
    val unit: MetricUnit,
    val recordedAt: Instant,   // when the sample happened
    val updatedAt: Instant,    // when this row last changed (drives merge)
    val version: Long,         // monotonic per-record counter (drives merge)
)

// The single edit rule: new record = version 1; every local edit = version+1, updatedAt = now.
fun Measurement.bumpVersion(now: Instant = Clock.System.now()): Measurement =
    copy(version = version + 1, updatedAt = now)

// shared/src/commonMain/kotlin/health/Units.kt
package health

private const val LB_PER_KG = 2.2046226218

fun Measurement.canonical(): Measurement = when (unit) {
    MetricUnit.LB -> copy(value = value / LB_PER_KG, unit = MetricUnit.KG)
    MetricUnit.MI -> copy(value = value * 1.609344, unit = MetricUnit.KM)
    else -> this
}

fun Measurement.validate(): Result<Measurement> {
    val unitOk = when (kind) {
        MetricKind.WEIGHT -> unit == MetricUnit.KG || unit == MetricUnit.LB
        MetricKind.STEPS -> unit == MetricUnit.COUNT
        MetricKind.HEART_RATE -> unit == MetricUnit.BPM
        MetricKind.ACTIVE_ENERGY -> unit == MetricUnit.KCAL
        MetricKind.DISTANCE -> unit == MetricUnit.KM || unit == MetricUnit.MI
    }
    if (!unitOk) return Result.failure(IllegalArgumentException("invalid unit $unit for $kind"))

    val valueOk = when (kind) {
        MetricKind.WEIGHT -> value in 1.0..635.0          // kg, sane human range
        MetricKind.HEART_RATE -> value in 20.0..250.0      // bpm
        else -> value >= 0.0
    }
    return if (valueOk) Result.success(this)
    else Result.failure(IllegalArgumentException("value $value out of range for $kind"))
}

-- shared/src/commonMain/sqldelight/health/Measurement.sq
CREATE TABLE measurement (
  id          TEXT    NOT NULL PRIMARY KEY,
  kind        TEXT    NOT NULL,
  value_      REAL    NOT NULL,         -- domain/wire/server field is `value`
  unit        TEXT    NOT NULL,
  recorded_at INTEGER NOT NULL,         -- epoch millis
  updated_at  INTEGER NOT NULL,         -- epoch millis, drives merge
  version     INTEGER NOT NULL,         -- monotonic per record
  dirty       INTEGER NOT NULL DEFAULT 1  -- 1 = not yet pushed to server
);

CREATE INDEX measurement_dirty ON measurement(dirty);

-- Local edit: mark dirty so the Syncer pushes it.
upsert:
INSERT OR REPLACE INTO measurement(id, kind, value_, unit, recorded_at, updated_at, version, dirty)
VALUES (?, ?, ?, ?, ?, ?, ?, 1);

-- Merged from server: store WITHOUT re-marking dirty.
upsertClean:
INSERT OR REPLACE INTO measurement(id, kind, value_, unit, recorded_at, updated_at, version, dirty)
VALUES (?, ?, ?, ?, ?, ?, ?, 0);

selectById:
SELECT * FROM measurement WHERE id = ?;

selectByKind:
SELECT * FROM measurement WHERE kind = ? ORDER BY recorded_at DESC;

selectDirty:
SELECT * FROM measurement WHERE dirty = 1;

clearDirty:
UPDATE measurement SET dirty = 0 WHERE id IN ?;

-- shared/src/commonMain/sqldelight/health/SyncState.sq
CREATE TABLE sync_state (
  id     INTEGER NOT NULL PRIMARY KEY DEFAULT 0 CHECK (id = 0),  -- exactly one row
  cursor INTEGER NOT NULL DEFAULT 0
);
INSERT OR IGNORE INTO sync_state(id, cursor) VALUES (0, 0);      -- seed the single row

selectCursor:
SELECT cursor FROM sync_state WHERE id = 0;

setCursor:
UPDATE sync_state SET cursor = ? WHERE id = 0;

// commonMain — the contract the shared code depends on
expect class SecureStore() {
    fun put(key: String, value: String)
    fun get(key: String): String?
}

// androidMain — backed by EncryptedSharedPreferences (androidx.security:security-crypto)
// iosMain — backed by the Keychain (Security framework via platform.Security.*)

// commonMain
expect class HealthBridge {
    // Returns canonical-unit Measurements since `sinceEpochMillis`.
    suspend fun read(kind: MetricKind, sinceEpochMillis: Long): List<Measurement>
    suspend fun requestPermissions(kinds: Set<MetricKind>): Boolean
}

<!-- iosApp/iosApp/Info.plist -->
<!-- You must also enable the HealthKit capability in the Xcode project target -->
<key>NSHealthShareUsageDescription</key>
<string>We need access to read your health data to sync it.</string>
<key>NSHealthUpdateUsageDescription</key>
<string>We need access to write health data from the app.</string>

<!-- Dependency: androidx.health.connect:connect-client -->
<!-- 1) Declare each typed permission you read. -->
<uses-permission android:name="android.permission.health.READ_STEPS"/>
<uses-permission android:name="android.permission.health.READ_HEART_RATE"/>
<uses-permission android:name="android.permission.health.READ_WEIGHT"/>

<application>
  <!-- 2a) Android 14+ : the system opens this to show your rationale. -->
  <activity-alias
      android:name="ViewPermissionUsageActivity"
      android:exported="true"
      android:targetActivity=".PermissionsRationaleActivity"
      android:permission="android.permission.START_VIEW_PERMISSION_USAGE">
    <intent-filter>
      <action android:name="android.intent.action.VIEW_PERMISSION_USAGE"/>
      <category android:name="android.intent.category.HEALTH_PERMISSIONS"/>
    </intent-filter>
  </activity-alias>

  <!-- 2b) Android 13 and below : same rationale, different action. -->
  <activity android:name=".PermissionsRationaleActivity" android:exported="true">
    <intent-filter>
      <action android:name="androidx.health.ACTION_SHOW_PERMISSIONS_RATIONALE"/>
    </intent-filter>
  </activity>
</application>

// shared/src/commonMain/kotlin/health/SyncApi.kt
package health

import io.ktor.client.HttpClient
import io.ktor.client.call.body
import io.ktor.client.request.*
import io.ktor.http.*
import kotlinx.serialization.Serializable

@Serializable data class PushBody(val records: List<Measurement>)   // typed request envelope
@Serializable data class Pushed(val acceptedIds: List<String>)
@Serializable data class Pulled(val records: List<Measurement>, val nextCursor: Long)

interface SyncApi {
    suspend fun push(records: List<Measurement>): Pushed
    suspend fun pull(cursor: Long): Pulled
}

class KtorSyncApi(
    private val http: HttpClient,
    private val baseUrl: String,
    private val token: () -> String?,   // reads SecureStore.get("sync_token")
) : SyncApi {
    override suspend fun push(records: List<Measurement>): Pushed =
        http.post("$baseUrl/sync/push") {
            bearerAuth(token() ?: error("no sync token"))
            contentType(ContentType.Application.Json)
            setBody(PushBody(records))   // typed envelope -> guarantees the {"records":[...]} wire shape
        }.body()

    override suspend fun pull(cursor: Long): Pulled =
        http.get("$baseUrl/sync/pull") {
            bearerAuth(token() ?: error("no sync token"))
            parameter("cursor", cursor)
        }.body()
}

// shared/src/commonMain/kotlin/health/Syncer.kt
package health

// Deterministic, COMMUTATIVE conflict resolution:
//   higher version wins; equal version -> later updatedAt; full tie -> stable content compare.
// The final compare makes merge(a,b) == merge(b,a) even when version AND updatedAt are equal.
private fun Measurement.tiebreak(): String =
    "$version|${updatedAt.toEpochMilliseconds()}|$value|$unit|$kind|$recordedAt"

internal fun merge(local: Measurement, remote: Measurement): Measurement = when {
    remote.version > local.version -> remote
    local.version > remote.version -> local
    remote.updatedAt > local.updatedAt -> remote
    local.updatedAt > remote.updatedAt -> local
    // Full tie: pick by a stable, order-independent content compare (ties are usually byte-identical anyway).
    remote.tiebreak() >= local.tiebreak() -> remote
    else -> local
}

class Syncer(
    private val store: MeasurementStore,
    private val api: SyncApi,        // built in the previous step (KtorSyncApi over an HttpClient)
) {
    suspend fun sync(): SyncResult {
        val pushed = api.push(store.dirty())          // send local changes
        store.clearDirty(pushed.acceptedIds)

        val incoming = api.pull(store.cursor())        // remote changes since last cursor
        for (remote in incoming.records) {
            val local = store.byId(remote.id)
            val winner = if (local == null) remote else merge(local, remote)
            store.upsertClean(winner)                  // store without re-marking dirty
        }
        store.setCursor(incoming.nextCursor)
        return SyncResult(pushed = pushed.acceptedIds.size, pulled = incoming.records.size)
    }
}

-- 1) Identity first (measurements FK-references it). Minimal on purpose.
CREATE TABLE IF NOT EXISTS users (
  id    UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),  -- gen_random_uuid is built into PG13+
  token TEXT NOT NULL UNIQUE        -- the Bearer token the device stores; maps a request to this user_id
);

-- A deterministic dev user so a fresh local DB runs end-to-end with no auth UI.
INSERT INTO users (id, token)
VALUES ('00000000-0000-0000-0000-000000000001', 'dev-token')
ON CONFLICT DO NOTHING;

-- 2) The synced records.
CREATE TABLE IF NOT EXISTS measurements (
  id           UUID        NOT NULL,
  user_id      UUID        NOT NULL REFERENCES users(id),
  kind         TEXT        NOT NULL,
  value        DOUBLE PRECISION NOT NULL,
  unit         TEXT        NOT NULL,             -- canonical unit only
  recorded_at  TIMESTAMPTZ NOT NULL,
  updated_at   TIMESTAMPTZ NOT NULL,
  version      BIGINT      NOT NULL CHECK (version > 0),
  PRIMARY KEY (user_id, id)
);

-- Range scans for time-series queries and the pull-since-cursor path.
CREATE INDEX IF NOT EXISTS measurements_user_time ON measurements (user_id, recorded_at);
CREATE INDEX IF NOT EXISTS measurements_user_updated ON measurements (user_id, updated_at);

-- 3) Append-only audit: every accepted change, newest cursor = max(seq).
CREATE TABLE IF NOT EXISTS sync_log (
  seq        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    UUID        NOT NULL,
  record_id  UUID        NOT NULL,
  version    BIGINT      NOT NULL,
  applied_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS sync_log_user_seq ON sync_log (user_id, seq);

# docker-compose.yml
services:
  db:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: dev
      POSTGRES_DB: vitals
    ports: ["5432:5432"]
    volumes: ["pgdata:/var/lib/postgresql/data"]
volumes: { pgdata: {} }

docker compose up -d
export DATABASE_URL="postgres://postgres:dev@localhost:5432/vitals?sslmode=disable"
psql "$DATABASE_URL" -f db/schema.sql

// POST /sync/push — upsert each record under the version guard, log accepted ids, return them.
func (s *Server) push(w http.ResponseWriter, r *http.Request) {
	userID := userFrom(r.Context()) // from auth middleware; never trust the body
	var req struct {
		Records []Measurement `json:"records"`
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		http.Error(w, "bad request", http.StatusBadRequest)
		return
	}

	tx, err := s.pool.Begin(r.Context())
	if err != nil { http.Error(w, err.Error(), 500); return }
	defer tx.Rollback(r.Context()) // no-op after Commit

	accepted := []string{}
	for _, m := range req.Records {
		var id string
		err := tx.QueryRow(r.Context(),
			`INSERT INTO measurements
			   (id, user_id, kind, value, unit, recorded_at, updated_at, version)
			 VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
			 ON CONFLICT (user_id, id) DO UPDATE
			   SET value = EXCLUDED.value,
			       unit = EXCLUDED.unit,
			       recorded_at = EXCLUDED.recorded_at,
			       updated_at = EXCLUDED.updated_at,
			       version = EXCLUDED.version
			 WHERE measurements.version < EXCLUDED.version
			 RETURNING id`,
			m.ID, userID, m.Kind, m.Value, m.Unit, m.RecordedAt, m.UpdatedAt, m.Version,
		).Scan(&id)
		switch {
		case err == pgx.ErrNoRows: // stale write: version guard rejected it, no-op
			continue
		case err != nil:
			http.Error(w, err.Error(), 500)
			return
		}
		if _, err := tx.Exec(r.Context(),
			`INSERT INTO sync_log (user_id, record_id, version) VALUES ($1, $2, $3)`,
			userID, m.ID, m.Version); err != nil {
			http.Error(w, err.Error(), 500)
			return
		}
		accepted = append(accepted, id)
	}
	if err := tx.Commit(r.Context()); err != nil { http.Error(w, err.Error(), 500); return }
	writeJSON(w, map[string]any{"acceptedIds": accepted})
}

-- GET /sync/pull?cursor=N — sync_log carries no payload, so take each record's latest
-- seq, keep those past the cursor, then JOIN back to measurements for the row body.
WITH latest AS (
  SELECT record_id, max(seq) AS seq
  FROM sync_log
  WHERE user_id = $1
  GROUP BY record_id
  HAVING max(seq) > $2            -- $2 = the client's cursor N
)
SELECT m.id, m.kind, m.value, m.unit, m.recorded_at, m.updated_at, m.version
FROM measurements m
JOIN latest l ON l.record_id = m.id
WHERE m.user_id = $1
ORDER BY l.seq;                   -- nextCursor = max(l.seq) over this set, or N if empty

@Service
class SyncService(private val jdbc: JdbcTemplate) {

    @Transactional
    fun push(userId: UUID, records: List<Measurement>): List<String> {
        val accepted = mutableListOf<String>()
        for (m in records) {
            // RETURNING id yields a row only when the version guard passes; queryForList stays empty otherwise.
            val returned = jdbc.queryForList(
                """INSERT INTO measurements
                     (id, user_id, kind, value, unit, recorded_at, updated_at, version)
                   VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                   ON CONFLICT (user_id, id) DO UPDATE
                     SET value = EXCLUDED.value,
                         unit = EXCLUDED.unit,
                         recorded_at = EXCLUDED.recorded_at,
                         updated_at = EXCLUDED.updated_at,
                         version = EXCLUDED.version
                   WHERE measurements.version < EXCLUDED.version
                   RETURNING id""",
                String::class.java,
                m.id, userId, m.kind, m.value, m.unit, m.recordedAt, m.updatedAt, m.version,
            )
            if (returned.isEmpty()) continue // stale write rejected by the guard — no-op
            jdbc.update(
                "INSERT INTO sync_log (user_id, record_id, version) VALUES (?, ?, ?)",
                userId, m.id, m.version,
            )
            accepted += returned.first()
        }
        return accepted
    }
}

-- GET /sync/pull?cursor=N — byte-identical to the Go path; only the ? placeholders differ from $1/$2.
-- sync_log has no payload, so take each record's latest seq, keep those past the cursor, JOIN to measurements.
WITH latest AS (
  SELECT record_id, max(seq) AS seq
  FROM sync_log
  WHERE user_id = ?
  GROUP BY record_id
  HAVING max(seq) > ?              -- the client's cursor N
)
SELECT m.id, m.kind, m.value, m.unit, m.recorded_at, m.updated_at, m.version
FROM measurements m
JOIN latest l ON l.record_id = m.id
WHERE m.user_id = ?
ORDER BY l.seq;                    -- nextCursor = max(l.seq) over this set, or N if empty

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, dsnFromEnv()) // DATABASE_URL locally; /cloudsql socket on Cloud Run
	if err != nil { log.Fatal(err) }
	if err := pool.Ping(ctx); err != nil { log.Fatalf("db unreachable: %v", err) } // fail fast
	defer pool.Close()

	srv := &Server{pool: pool}
	mux := http.NewServeMux()
	mux.Handle("POST /sync/push", auth(pool, http.HandlerFunc(srv.push)))
	mux.Handle("GET /sync/pull", auth(pool, http.HandlerFunc(srv.pull)))

	port := os.Getenv("PORT")
	if port == "" { port = "8080" } // Cloud Run injects PORT
	httpSrv := &http.Server{Addr: ":" + port, Handler: mux}

	// Graceful shutdown: SIGINT/SIGTERM -> stop accepting, drain in-flight syncs, close the pool.
	stopCtx, stop := signal.NotifyContext(ctx, os.Interrupt, syscall.SIGTERM)
	defer stop()
	go func() {
		if err := httpSrv.ListenAndServe(); err != nil && err != http.ErrServerClosed {
			log.Fatal(err)
		}
	}()
	<-stopCtx.Done()
	shutCtx, cancel := context.WithTimeout(ctx, 10*time.Second)
	defer cancel()
	_ = httpSrv.Shutdown(shutCtx)
}

type ctxKey struct{}

// auth resolves the Bearer token to a user_id and puts it in the context; userFrom() reads it.
func auth(pool *pgxpool.Pool, next http.Handler) http.Handler {
	return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
		token, ok := strings.CutPrefix(r.Header.Get("Authorization"), "Bearer ")
		if !ok || token == "" { http.Error(w, "unauthorized", http.StatusUnauthorized); return }
		var userID string
		err := pool.QueryRow(r.Context(), `SELECT id FROM users WHERE token = $1`, token).Scan(&userID)
		if err != nil { http.Error(w, "unauthorized", http.StatusUnauthorized); return } // unknown token
		ctx := context.WithValue(r.Context(), ctxKey{}, userID)
		next.ServeHTTP(w, r.WithContext(ctx))
	})
}

func userFrom(ctx context.Context) string { return ctx.Value(ctxKey{}).(string) }

@SpringBootApplication
class VitalsApplication

fun main(args: Array<String>) { runApplication<VitalsApplication>(*args) }

// Bearer token -> user_id, mirroring the Go auth middleware. Registered as a servlet Filter.
@Component
class BearerAuthFilter(private val jdbc: JdbcTemplate) : OncePerRequestFilter() {
    override fun doFilterInternal(req: HttpServletRequest, res: HttpServletResponse, chain: FilterChain) {
        val token = req.getHeader("Authorization")?.removePrefix("Bearer ")?.takeIf { it.isNotBlank() }
        val userId = token?.let {
            jdbc.queryForList("SELECT id FROM users WHERE token = ?", String::class.java, it).firstOrNull()
        }
        if (userId == null) { res.sendError(HttpServletResponse.SC_UNAUTHORIZED); return } // missing/unknown token
        req.setAttribute("userId", userId) // the controller reads this; never a body field
        chain.doFilter(req, res)
    }
}

# Cloud SQL via the JDBC SocketFactory on Cloud Run; a plain jdbc:postgresql://localhost/vitals locally.
spring.datasource.url=${JDBC_DATABASE_URL:jdbc:postgresql://localhost:5432/vitals}
spring.datasource.username=${DB_USER:postgres}
spring.datasource.password=${DB_PASSWORD:dev}
# Fail fast on a bad URL at boot, not on the first request (the Go Ping equivalent).
spring.datasource.hikari.initialization-fail-timeout=1
# Drain in-flight syncs on SIGTERM (Cloud Run scale-down) before exiting.
server.shutdown=graceful
spring.lifecycle.timeout-per-shutdown-phase=10s

# 1) Server up against the local Docker Postgres (schema already applied earlier).
export DATABASE_URL="postgres://postgres:dev@localhost:5432/vitals?sslmode=disable"
#   Go:     go run ./cmd/server      # listens on :8080
#   Spring: ./gradlew bootRun        # listens on :8080

# 2) Two clients, two terminals. baseUrl differs per platform:
#   Android emulator -> http://10.0.2.2:8080     iOS simulator -> http://localhost:8080
./gradlew :androidApp:installDebug      # client 1: Android emulator
xcrun simctl boot "iPhone 15"           # client 2: iOS simulator (needs macOS + Xcode)

# 3) On EACH client: turn networking off, edit the SAME weight to a different value, turn it back on, sync.
#    Verify directly in Postgres that one version won:
psql "$DATABASE_URL" -c "SELECT id, value, version FROM measurements ORDER BY version DESC LIMIT 1;"

// In each app's composition root (androidApp / iosApp), before first sync:
// baseUrl is the ONE value that differs per client.
val baseUrl = "http://10.0.2.2:8080"      // Android emulator; iOS simulator uses "http://localhost:8080"

val secure = SecureStore(/* Android: context */)
if (secure.get("sync_token") == null) {
    secure.put("sync_token", "dev-token")  // matches the users row seeded in db/schema.sql
}

val api = KtorSyncApi(http = httpClient, baseUrl = baseUrl, token = { secure.get("sync_token") })
val syncer = Syncer(store = measurementStore, api = api)
// syncer.sync() now pushes dirty rows and pulls merged ones against the real server.

struct MeasurementList: View {
    let store: MeasurementStore   // the shared KMP type, exposed to Swift
    let syncer: Syncer

    var body: some View {
        List(measurements, id: \.id) { m in
            HStack {
                VStack(alignment: .leading) {
                    Text(m.kind.name)
                    Text(formatInstant(m.recordedAt)).font(.caption)
                }
                Spacer()
                Text(formatForDisplay(m))   // canonical -> user unit at the edge
            }
        }
        .refreshable { try? await syncer.sync() }  // suspend fun -> Swift async
    }
}

@Composable
fun MeasurementRow(m: Measurement, prefs: UnitPrefs) {
    Row(Modifier.fillMaxWidth().padding(16.dp), Arrangement.SpaceBetween) {
        Column {
            Text(m.kind.name, style = MaterialTheme.typography.titleMedium)
            Text(formatInstant(m.recordedAt), style = MaterialTheme.typography.bodySmall)
        }
        Text(formatForDisplay(m, prefs))  // e.g. canonical 90.7 kg -> "200 lb" if user prefers lb
    }
}

Future<SyncResult> sync(int cursor) async {
  // Push local dirty rows, then pull everything changed since the cursor.
  await http.post(
    Uri.parse('$baseUrl/sync/push'),
    headers: {'Authorization': 'Bearer $token', 'Content-Type': 'application/json'},
    body: jsonEncode({'records': dirtyRecords.map((m) => m.toJson()).toList()}),
  );
  final res = await http.get(
    Uri.parse('$baseUrl/sync/pull?cursor=$cursor'),
    headers: {'Authorization': 'Bearer $token'},
  );
  final data = jsonDecode(res.body) as Map<String, dynamic>;
  final records = (data['records'] as List).map((j) => Measurement.fromJson(j)).toList();
  return SyncResult(records: records, nextCursor: data['nextCursor'] as int);
}

gcloud sql instances create vitals-pg \
  --database-version=POSTGRES_16 --tier=db-f1-micro --region=europe-west1
gcloud sql databases create vitals --instance=vitals-pg
gcloud sql users create app_user --instance=vitals-pg --password='CHANGE_ME'
CONNECTION_NAME=$(gcloud sql instances describe vitals-pg --format='value(connectionName)')

# Go path: dsnFromEnv() reads the WHOLE DATABASE_URL — a libpq DSN whose host is the mounted socket
# (NOT a TCP postgres://host:5432 URL). gcloud --set-env-vars does NOT interpolate other env vars, and
# pgx/libpq does NOT expand $VAR inside a DSN, so the password must already be in the string. Store the
# complete DSN (real password included) in Secret Manager and inject it whole as DATABASE_URL via
# --set-secrets, so the container sees a ready-to-use DSN — no $VAR survives into the connection string.
printf 'postgres://app_user:THE_REAL_PASSWORD@/vitals?host=/cloudsql/%s' "$CONNECTION_NAME" \
  | gcloud secrets create vitals-database-url --data-file=-
gcloud run deploy vitals-sync \
  --source . --region europe-west1 --allow-unauthenticated \
  --add-cloudsql-instances "$CONNECTION_NAME" \
  --set-env-vars "INSTANCE_CONNECTION_NAME=$CONNECTION_NAME" \
  --set-secrets "DATABASE_URL=vitals-database-url:latest"

# Spring path instead: Spring reads username/password as SEPARATE datasource properties, not inside the URL,
# so DATABASE_URL does not apply. The URL (JDBC SocketFactory) and DB_USER are plain env vars; only the
# password is secret. No un-interpolated $VAR appears in any value.
#   --set-env-vars "INSTANCE_CONNECTION_NAME=$CONNECTION_NAME,JDBC_DATABASE_URL=jdbc:postgresql:///vitals?cloudSqlInstance=$CONNECTION_NAME&socketFactory=com.google.cloud.sql.postgres.SocketFactory,DB_USER=app_user"
#   --set-secrets "DB_PASSWORD=vitals-db-password:latest"

-- One row of aggregates for the user's current week, in canonical units.
SELECT
  avg(value) FILTER (WHERE kind = 'WEIGHT')                    AS avg_weight_kg,
  sum(value) FILTER (WHERE kind = 'STEPS')                     AS total_steps,
  avg(value) FILTER (WHERE kind = 'HEART_RATE')                AS avg_heart_rate_bpm
FROM measurements
WHERE user_id = $1
  AND recorded_at >= date_trunc('week', now());

// shared/src/commonMain/kotlin/health/Parsed.kt
package health

import kotlinx.datetime.Instant

// What /parse returns, before the user confirms — one per extracted signal.
data class ParsedEntry(
    val kind: MetricKind,
    val value: Double,
    val unit: MetricUnit,
    val recordedAt: Instant,
    val label: String? = null,     // e.g. "dizzy" for a SYMPTOM; null for plain metrics
)

// Reuse the existing canonical()/validate(); never re-implement the unit math here.
fun ParsedEntry.toMeasurement(id: String, now: Instant): Result<Measurement> =
    Measurement(
        id = id, kind = kind, value = value, unit = unit,
        recordedAt = recordedAt, updatedAt = now, version = 1, label = label,
    ).canonical().validate()

-- shared/src/commonMain/sqldelight/health/migrations/1.sqm
-- SQLDelight runs numbered .sqm files in order; this one takes the schema from v1 to v2.
-- A nullable ADD COLUMN is backward-compatible: existing rows get NULL, no rewrite, no data loss.
ALTER TABLE measurement ADD COLUMN label TEXT;

// build.gradle.kts (shared) — tell SQLDelight a fresh install is at schema 2 and migrations exist.
sqldelight {
    databases {
        create("VitalsDb") {
            packageName.set("health")
            schemaVersion = 2            // was 1; migrations/1.sqm upgrades a v1 device to this
            // verifyMigrations.set(true) // optional: fail the build if a migration doesn't reach schemaVersion
        }
    }
}

-- Add `label` to the table so a FRESH install has it, and to upsert/upsertClean so saved entries carry it.
-- Existing v1 devices reach the same shape via migrations/1.sqm above.
-- (the table definition now ends with) ... version INTEGER NOT NULL, dirty INTEGER NOT NULL DEFAULT 1, label TEXT
upsert:
INSERT OR REPLACE INTO measurement(id, kind, value_, unit, recorded_at, updated_at, version, dirty, label)
VALUES (?, ?, ?, ?, ?, ?, ?, 1, ?);

upsertClean:
INSERT OR REPLACE INTO measurement(id, kind, value_, unit, recorded_at, updated_at, version, dirty, label)
VALUES (?, ?, ?, ?, ?, ?, ?, 0, ?);

// Add Label to the server's Measurement struct so the wire field is decoded and persisted.
type Measurement struct {
    ID         string    `json:"id"`
    Kind       string    `json:"kind"`
    Value      float64   `json:"value"`
    Unit       string    `json:"unit"`
    RecordedAt time.Time `json:"recordedAt"`
    UpdatedAt  time.Time `json:"updatedAt"`
    Version    int64     `json:"version"`
    Label      *string   `json:"label"` // nullable; non-null only for SYMPTOM entries
}

// In the push handler: 9-column INSERT carrying label, with label = EXCLUDED.label in DO UPDATE.
err := tx.QueryRow(r.Context(),
    `INSERT INTO measurements
       (id, user_id, kind, value, unit, recorded_at, updated_at, version, label)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
     ON CONFLICT (user_id, id) DO UPDATE
       SET value      = EXCLUDED.value,
           unit       = EXCLUDED.unit,
           recorded_at = EXCLUDED.recorded_at,
           updated_at = EXCLUDED.updated_at,
           version    = EXCLUDED.version,
           label      = EXCLUDED.label
     WHERE measurements.version < EXCLUDED.version
     RETURNING id`,
    m.ID, userID, m.Kind, m.Value, m.Unit, m.RecordedAt, m.UpdatedAt, m.Version, m.Label,
).Scan(&id)

// Add label to the server's Measurement data class.
data class Measurement(
    val id: String,
    val kind: String,
    val value: Double,
    val unit: String,
    val recordedAt: java.time.OffsetDateTime,
    val updatedAt: java.time.OffsetDateTime,
    val version: Long,
    val label: String?,  // nullable; non-null only for SYMPTOM entries
)

// In SyncService.push: 9-column INSERT carrying label, with label = EXCLUDED.label in DO UPDATE.
val returned = jdbc.queryForList(
    """INSERT INTO measurements
         (id, user_id, kind, value, unit, recorded_at, updated_at, version, label)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
       ON CONFLICT (user_id, id) DO UPDATE
         SET value       = EXCLUDED.value,
             unit        = EXCLUDED.unit,
             recorded_at = EXCLUDED.recorded_at,
             updated_at  = EXCLUDED.updated_at,
             version     = EXCLUDED.version,
             label       = EXCLUDED.label
       WHERE measurements.version < EXCLUDED.version
       RETURNING id""",
    String::class.java,
    m.id, userId, m.kind, m.value, m.unit, m.recordedAt, m.updatedAt, m.version, m.label,
)

POST /parse  { "phrase": "ran 5k this morning, felt dizzy after, weight 72.3kg" }

responseSchema (conceptual) — one object per extracted signal:
{ "type": "array", "items": {
    "type": "object",
    "properties": {
      "kind":  { "type": "string", "enum": ["WEIGHT","STEPS","HEART_RATE","ACTIVE_ENERGY","DISTANCE","SYMPTOM"] },
      "value": { "type": "number" },
      "unit":  { "type": "string", "enum": ["KG","LB","COUNT","BPM","KCAL","KM","MI"] },
      "recordedAt": { "type": "string" },
      "label": { "type": "string" }
    },
    "required": ["kind","value","unit","recordedAt"] } }

POST /parse  { "phrase": "ran 5k this morning, felt dizzy after, weight 72.3kg" }

responseSchema (conceptual) — one object per extracted signal:
{ "type": "array", "items": {
    "type": "object",
    "properties": {
      "kind":  { "type": "string", "enum": ["WEIGHT","STEPS","HEART_RATE","ACTIVE_ENERGY","DISTANCE","SYMPTOM"] },
      "value": { "type": "number" },
      "unit":  { "type": "string", "enum": ["KG","LB","COUNT","BPM","KCAL","KM","MI"] },
      "recordedAt": { "type": "string" },
      "label": { "type": "string" }
    },
    "required": ["kind","value","unit","recordedAt"] } }