Decision: Collapse Supabase Weather/Alert Data Into Yaya Engine's Own Postgres

Rada — the weather-and-alert side of the Atram system — used to be split across two Postgres databases: yaya-engine's own, and a separate Supabase instance that rada-weather, the rada-message-system "Composer," and alert-manager read and wrote through PostgREST. The two had to be kept in sync by hand, and Supabase's users table had no fsp_id column, forcing downstream consumers to infer FSP membership from country plus whether a yaya_user_id mirror existed — a heuristic that breaks the moment a country gets a second FSP. The decision behind Rada v2 is to end that split entirely: move geography, forecast, and alert-dispatch data into yaya-engine's own Postgres and make yaya-engine the single system of record, described in more depth in Rada v2 / Supabase Collapse.

Context

A cutover of this shape carries two distinct risks that have to be managed separately: whether the new schema is correct on the actual production database engine, and whether the operational step of moving live data across actually happened, as opposed to merely having code that could do it. Yaya-engine's test suite runs on SQLite in-memory, while production runs Postgres on Neon, so schema correctness on Postgres specifically had never been directly proven before this cutover — SQLite passing tests does not guarantee migrations, indexes, and destructive down() methods behave identically on Postgres [@pg-verification].

Decision

Rada v2's schema — countries, seasons, events, event_fsp_assignments, town_forecasts, town_observations, forecast_configs, dispatch_modes, and their audit tables — is added directly to yaya-engine's own Postgres migrations, rather than staying in Supabase or being introduced as yet another mirrored table. A dedicated verification pass on 2026-07-08 stood up a real standalone PostgreSQL 18 cluster (production runs PG 16 on Neon, but the migrations use no version-specific DDL, so the gap doesn't affect the result) and ran the full migration set against it [@pg-verification]. migrate:fresh --force applied all 113 migrations — Rada v2's and everything pre-existing — with zero errors, and a repeat run was equally clean, confirming idempotency [@pg-verification]. A leading-column FK-index audit against pg_constraint/pg_index confirmed all ten Rada v2 foreign-key constraints are covered by a proper index, and migrate:rollback --force tore all 113 migrations back down with zero errors, including FK teardown ordering [@pg-verification]. The same pass exercised PruneForecastStoreCommand's retention logic with a seeded "data-loss sentinel" row — simultaneously the oldest and the highest-id row in its group — and confirmed the prune correctly kept it rather than deleting it on age alone [@pg-verification]. The verdict recorded from that pass: no critical or major bugs in the Rada v2 migration set [@pg-verification].

Cutover is deliberately gradual rather than a single flag flip. While external Rada services still read Supabase directly, every outbound call through App\Http\Integrations\Supabase\SupabaseConnector logs a structured rada.supabase_call event — endpoint, method, and calling class — as a tripwire meant to catch any call site the cutover missed [@tripwire]. The tripwire treats zero events per five minutes as the healthy post-cutover steady state, more than five a minute as a code path that slipped through cutover and needs investigation within a day, and more than fifty a minute as cause to page on-call [@tripwire]. This is the mechanism that turns "we believe the cutover is complete" into something checkable in production logs rather than an assumption resting on the migration having merged.

Status

Approved and in progress. The schema side is verified safe on Postgres as of the 2026-07-08 pass [@pg-verification], and the tripwire instrumentation is live to catch residual Supabase calls during the gradual transition [@tripwire]. The cutover is not a single event — it proceeds table by table and consumer by consumer, with the Supabase read path staying available until the tripwire shows nothing is calling it.

Consequences

The verification pass surfaced a secondary finding worth acting on but explicitly out of scope for Rada v2 itself: roughly 21 pre-existing (non-Rada-v2) foreign-key columns across the schema — on users, user_action_steps, requested_payouts, generated_links, and others — have no covering index, a gap the pass flagged rather than fixed, since fixing it belonged to a separate hygiene migration [@pg-verification].

The more consequential lesson came from a related geography cutover, not from Rada v2's own schema pass. Three sub-issues covering the towns/states migration (#317, #352, #355) were closed as done once the migration, the rada:backfill-geography command, and the read API had merged and deployed. The one-time operational step of actually running that backfill against production never happened, and prod's towns and states tables sat at zero rows for roughly two weeks — migration landed 2026-06-25, discovery came 2026-07-08 — with nothing alerting on the empty tables and the legacy Supabase paths quietly continuing to serve reads in the meantime, so the gap was invisible from outside [@p22-incident]. The root cause was procedural, not technical: issue closure tracked code shipped, not effect achieved, and a backfill command that exists but was never invoked is indistinguishable, from the tracker's point of view, from one that ran [@p22-incident].

The prevention adopted going forward applies directly to any future step of this same cutover: a data-migration or cutover issue may only be closed after a production row-count check is pasted into the issue itself, and any epic step whose deliverable is a runnable command gets two separate checkboxes — "command merged" and "command executed against prod, output attached" — rather than one [@p22-incident]. That rule is the operational discipline this decision now carries: schema correctness on Postgres and the tripwire prove the code path is safe, but neither one proves a given cutover step actually happened in production, and only a pasted row-count check does. See Supabase Integration and the Rada v2 Cutover for how the tripwire and this incident sit together in the current architecture, and Running the Cross-Repo Harness for how the live contract between yaya-engine and rada-weather gets exercised end to end.