Records the 2026-06-09 migration of weather-bet’s persistence from local SQLite to Supabase Postgres, and — more importantly — the durable, fleet-wide engineering knowledge it produced: the dedicated-schema isolation convention, the idempotent embedded-SQL bootstrap, the SQLite→Postgres strict-typing gotchas, the Supabase connection rules (session pooler, NEVER :6543), and how to test against real Postgres without a live DB. Future weather / crypto / Polymarket Rust work on the shared Supabase instance will hit these same issues — read this before adding storage.
For Agents
Status (2026-06-09): Code complete + verified (clippy -D warnings clean, fmt clean, 88/88 tests green against real Postgres). NOT yet committed/pushed — awaiting operator OK. A @deploy task is handed off in polymarket_fetch/AGENT_HANDOFF.md. No data migration — fresh start (paper-trader, no production data worth keeping).
What moved:weather_bet persistence SQLite → Supabase Postgres, dedicated schema weather_bet.
Why: an ephemeral-storage footgun (missing WXBET_DB_PATH → container wrote SQLite to ephemeral storage → data loss on every container recreate). Postgres makes that whole bug class impossible (no host file / volume).
weather_bet is a paper-trader (places no real orders) whose entire value is the accruing record of would-be bets and their resolved outcomes. A missing WXBET_DB_PATH env key in the deployed container meant SQLite defaulted to a path on ephemeral container storage rather than a mounted volume. Every container recreate wiped the DB → repeated, silent data loss of exactly the data the project exists to collect.
The footgun this migration kills
A path-configured local file store + containers = a standing trap: one missing env key (or a volume that didn’t mount) silently routes writes to ephemeral storage. Moving to Supabase Postgres removes the host file/volume entirely — there is no path to misconfigure, so this whole class of “where did my data go after redeploy” bug becomes impossible, not merely “fixed this once.”
Mirrors crypto-shortterm’s crypto_shortterm schema (its recorder writes binance_ticks, cl_reports, pm_book_snaps, observations, outcomes into that isolated schema).
This (dedicated-schema) is the newer, cleaner style. The older style is pm_*-prefixed tables sitting in public — see polymarket-fetch-deploy (pm_runs, pm_trades) and supabase-findings-store (public.bsc_finding). New apps should follow the dedicated-schema style, not the public-prefix one.
Benefits: zero name collisions between apps; trivial per-app teardown (DROP SCHEMA weather_bet CASCADE); clear ownership; no risk of one app’s migration clobbering another’s table in public.
Bootstrap — idempotent embedded SQL, NOT sqlx::migrate!
Schema is created by running an embedded, idempotent schema.sql on every connect, not by a migration framework.
// crates/wx-core/src/store/postgres.rsconst SCHEMA_SQL: &str = include_str!("../../schema.sql");pub async fn connect(database_url: &str) -> Result<Self> { let pool = PgPoolOptions::new() .max_connections(2) .acquire_timeout(Duration::from_secs(10)) .connect(database_url).await?; sqlx::raw_sql(SCHEMA_SQL).execute(&pool).await?; // CREATE SCHEMA/TABLE IF NOT EXISTS … Ok(Self { pool })}
schema.sql is all CREATE SCHEMA IF NOT EXISTS weather_bet; + CREATE TABLE IF NOT EXISTS weather_bet.<table> (…). Re-running on every process start is safe (idempotent).
Deliberately NOT sqlx::migrate!
Using sqlx::raw_sql(SCHEMA_SQL) instead of sqlx::migrate! is a chosen design, not an oversight:
Avoids the _sqlx_migrations tracking-table drift that bit polymarket-fetch (where the migration framework + .dockerignore excluding the supabase/ dir caused real deploy pain — see polymarket-fetch-deploy gotcha #17, “.dockerignore killed sqlx::migrate!()”). No tracking table → no drift, no checksum mismatches, no “migration X already applied” surprises.
Pooler-safe and embed-at-compile-time (include_str!) — the SQL ships inside the binary, so there’s no migrations directory for .dockerignore to drop.
For a paper-trading spike where the schema is small and additive, idempotent IF NOT EXISTS is strictly simpler and lower-risk than a migration ledger.
SQLite → Postgres strict-typing gotchas (the expensive part)
sqlx is LENIENT on SQLite but STRICT on Postgres
SQLite has dynamic typing and sqlx lets a lot slide; Postgres + sqlx is strictly typed and will reject mismatches at decode/bind time. Every item below was a real fix during this migration. Anyone porting another SQLite app — or writing new Postgres queries by hand — will hit these.
Placeholders: SQLite ? → Postgres positional $1, $2, …. The highest $N must equal the .bind() count exactly, or it fails at prepare time.
REAL → DOUBLE PRECISION: SQLite REAL maps to Postgres DOUBLE PRECISION for Rust f64 binds. Postgres REAL is f32, not f64 — binding an f64 against a column typed REAL is a type error. All float columns in weather_bet.schema.sql are DOUBLE PRECISION (e.g. lat, lon, mu, sigma, our_prob, vwap, stake_usd, shares, fee_rate, modeled_edge).
All integers → BIGINT ↔ Rust i64: Postgres rejects decoding an INTEGER (int4) column into i64 and vice-versa — the classic error is mismatched types; Rust type i64 is not compatible with SQL type INT4. Fix: make every integer column BIGINT, and widen i32 domain values to i64 at the bind boundary (i64::from(x)), narrowing back on read (usize::try_from(bi) etc.). Do the widen/narrow at the DB edge so domain types stay i32/usize.
COUNT(*) is BIGINT: decode aggregate counts as i64, never i32.
date('now') → CURRENT_DATE: and comparing a TEXT YYYY-MM-DD column to a date needs an explicit cast — target_date::date < CURRENT_DATE (Postgres won’t implicitly compare text to date).
No SELECT-list-alias references in WHERE: SQLite tolerates referencing a SELECT-list alias / correlated subquery alias in WHERE; Postgres does NOT.
"Latest row per group" — rewrite correlated subqueries as INNER JOIN LATERAL
The SQLite pattern of SELECT (… correlated subquery …) AS x … WHERE x IS NOT NULL doesn’t port. Rewrite as an INNER JOIN LATERAL that orders + LIMIT 1 per outer row. The INNER join drops outer rows with no match — which is exactly equivalent to the old WHERE x IS NOT NULL filter. Real example from store/postgres.rs (latest forecast per slug):
SELECT pb.slug, pb.bucket_index, ..., r.winning_index, fc.probs_jsonFROM weather_bet.paper_bets pbINNER JOIN weather_bet.resolutions r ON pb.slug = r.slugINNER JOIN LATERAL ( SELECT probs_json FROM weather_bet.forecasts WHERE slug = pb.slug ORDER BY taken_at DESC LIMIT 1) fc ON true
Two queries use this shape: load_resolved_bets_with_forecast and load_resolved_markets_with_forecast.
Timestamps / JSON kept as TEXT: Timestamps are round-tripped via .to_rfc3339() and JSON via serde_json into TEXT columns (buckets_json, probs_json, taken_at, …). This was a deliberate lowest-risk choice for a spike — it avoids changing the serialization format vs the old SQLite store. Migrating these to native timestamptz / jsonb later is a separate, optional hardening step.
Supabase connection — session pooler, NEVER :6543 (CRITICAL, fleet-wide)
Use the SESSION pooler :5432 (or direct) — NEVER the :6543 transaction pooler
sqlx uses prepared statements, which PgBouncer transaction-mode (:6543) breaks. This is the single most expensive Supabase-with-sqlx footgun and it is fleet-wide — polymarket-fetch-deploy hit it as gotcha #18 (“Transaction pooler :6543 is not sqlx-compatible — must be session”).
Use: the session pooleraws-1-eu-west-1.pooler.supabase.com:5432 (or the direct DSN).
Avoid: the transaction pooler on :6543.
Note the direct DSN (db.<ref>.supabase.co) is IPv6-only and fails from inside Docker’s IPv4 bridge — so on the containerized rig the session pooler is the one that actually works (see polymarket-fetch-deploy gotcha #18 for the Network is unreachable (os error 101) symptom).
DSN source: read from SUPABASE_DB_URL, falling back to DATABASE_URL (wx-bet/src/main.rs; errors with “SUPABASE_DB_URL (or DATABASE_URL) must be set”). Never log the DSN — it carries the password.
Pool: small — max_connections(2), acquire_timeout(10s). A paper-trader needs almost no concurrency; keeping the pool tiny is friendly to the shared pooler.
Testing real Postgres without a live DB — testcontainers behind a testkit feature
Test against REAL Postgres, gated behind a cargo feature so production builds exclude it
The whole point of the migration is correct Postgres behavior, so the tests run against real Postgres via testcontainers-modules — but the dependency is optional = true and gated behind a cargo feature testkit so the production image never pulls it in.
Feature wiring: in wx-core/Cargo.toml, testkit = ["dep:testcontainers-modules"] with testcontainers-modules marked optional = true. wx-bet enables it only for tests: [dev-dependencies] wx-core = { path = "../wx-core", features = ["testkit"] }.
One shared helper (DRY): a single testkit::spawn_pg() returns (ContainerAsync<Postgres>, PgStore) per test. Each test gets its own ephemeral container on a random host port → no shared state, no isolation hazards, no port collisions. Every test (tests/e2e_smoke.rs, tests/loop_once.rs, tests/resolve.rs, and the inline #[cfg(test)] mods) calls the same spawn_pg() — the bootstrap (PgStore::connect → raw_sql(SCHEMA_SQL)) is exercised on every test.
Pin the image tag to avoid a pull:Postgres::default().with_tag("16-alpine") so it uses the locally-cached image rather than pulling on each run.
Production build excludes it:cargo build --release -p wx-bet (no testkit feature) → the image has no testcontainers dependency.
Result:88/88 tests green against real Postgres this way. Verifies the schema bootstrap, the strict typing fixes, the LATERAL rewrites, and the round-tripping end-to-end.
Status & deploy handoff (2026-06-09)
Code: complete + verified — clippy -D warnings clean, fmt clean, 88/88 tests green against real Postgres.
Git:NOT yet committed/pushed — awaiting operator OK.
Data:no migration — fresh start (paper data, nothing to preserve).
@deploy handoff (recorded in polymarket_fetch/AGENT_HANDOFF.md):
Add SUPABASE_DB_URL (session-pooler DSN) to WEATHER_BET_ENV.
Remove WXBET_DB_PATH (the SQLite path key — now meaningless).
Drop the /var/lib/weather-bet volume (no longer any host file to persist).
Redeploy onto the existing polymarket-infra VM (Frankfurt, europe-west3-a) — same geofenced rig the rest of the fleet uses (see weather_bet/DEPLOY.md).
For future weather / crypto / Polymarket Rust work
Reuse checklist when adding Supabase storage to a new fleet app
Dedicated schema (<app>.<table>, fully qualified), never public / pm_*. Mirror this note and crypto-shortterm.
Idempotent embedded schema.sql via include_str! + sqlx::raw_sql(...).execute(&pool) on connect. Avoid sqlx::migrate! unless you actually need versioned, destructive migrations.
Connect via the session pooler :5432, never :6543; sslmode=require; runtime-tokio-rustls; DSN from SUPABASE_DB_URL/DATABASE_URL; never log it; small pool.
Strict typing:$N placeholders, DOUBLE PRECISION for f64, BIGINT↔i64 everywhere (widen/narrow at the edge), CURRENT_DATE with explicit ::date casts, INNER JOIN LATERAL for latest-per-group.
Test against real Postgres via testcontainers behind a testkit/optional feature with a shared spawn_pg() helper; pin the image tag; keep it out of the release build.
crypto-shortterm — sibling Phase-0 Polymarket paper-trader; the crypto_shortterm dedicated-schema precedent
polymarket-fetch-deploy — origin of the session-pooler-vs-:6543 rule (gotcha #18) and the sqlx::migrate! / .dockerignore pain (gotcha #17) this migration deliberately sidesteps