Tier 3 Pre-Migration Audit — RealOutcomes (T3-0)¶
Status: Audit complete. Awaiting Ryan review before Tier 3 execution (T3-1 planning).
Date: 2026-08-28
Scope: Planning only (T3-0). No migration work executed.
Product: realoutcomes.app — nonprofit watchdog / outcomes intelligence
Repo: Last-1-Enterprises/RealOutcomes
Muster item: migrate-realoutcomes-railway-supabase-to-r940
Parent sequencing: TIER_RESEQUENCING_2026-08-28.md
Executive summary¶
Tier 3 is RealOutcomes full-stack sovereignty: move Railway compute, hosted Supabase (cpocwrygxlqhzvmlrpjv), Edge Functions, pg_cron, Storage, and the monthly-irs-sync Railway cron off vendor hosting onto R940.
This is the portfolio's first post–Tier 2 full-stack migration (compute + database + background jobs). It is more complex than Tier 2 last1.id in Supabase platform surface (Edge Functions, pg_cron, Storage, large IRS bulk dataset) but lower user blast radius (pre-user / minimal auth users).
Recommended calendar: 5–8 working days compressed (not an 8-week Tier 2 calendar). Pre-work (Railway env audit, dump rehearsal, Edge Function strategy) is the gating work.
Top pre-flight risks:
- ~1.3 GB Postgres dominated by
irs_organizations(~2M rows) — dump/restore time and disk on R940 DB VM. - Eight Supabase Edge Functions + five pg_cron jobs — must replatform to self-hosted Supabase stack on R940 or port triggers to Express/systemd (explicit T3-1 decision).
- Stripe live webhooks + D-live secret rotation — cutover window coordination with
admin.last1.enterprisesgateway unchanged until Tier 6. - Marketing/app monolith split — apex is Railway monolith today; Tier 3 should decide Pages extraction timing (parallel vs post-cutover).
Pre-user confirmation: Yes. Supabase auth.users = 3, public.users = 3, user_profiles = 1. Production data is overwhelmingly public IRS / nonprofit registry bulk imports, not customer clinical records.
1. Current architecture¶
1.1 Railway deployment¶
| Item | Finding |
|---|---|
| Project | Same Railway org as other Last 1 products (disciplined-beauty pattern; exact service IDs — T3-1 Railway GraphQL audit required) |
| Web service | Dockerfile Node 20 alpine · npm run build → node dist/index.cjs · port 5000 |
| Config | railway.toml: builder = DOCKERFILE, restartPolicyType = ON_FAILURE, max retries 3 |
| Domain | Apex realoutcomes.app (+ www) on Cloudflare → Railway (yi6abdjh.up.railway.app per consolidation plan, 2026-08-20) |
| Last push | 2026-08-10 (main) |
| Memory / replicas / cost | Unknown (dashboard not read in T3-0) |
Second Railway service — monthly-irs-sync (not a separate product):
| Item | Value |
|---|---|
| Config | railway.cron.toml |
| Schedule | 0 3 1 * * (1st of month, 03:00 UTC) |
| Command | node dist/cron/monthly-irs.cjs |
| Script | scripts/monthly-irs-sync.ts (BMF + Auto-Revocation List imports) |
| Restart | NEVER |
Fold into RealOutcomes R940 VM as systemd timer or retain Railway cron until T3-7 (rollback net).
1.2 Supabase project¶
| Field | Value |
|---|---|
| Project ref | cpocwrygxlqhzvmlrpjv |
| Name | Real Outcomes |
| Region | us-east-1 |
| Postgres | 17.6.x (matches R940 last1.id / lastvet Postgres 17 target) |
| Status | ACTIVE_HEALTHY (2026-08-28 MCP) |
| Database size | ~1297 MB total |
| Public tables | 40 (all sampled with RLS enabled) |
| Storage | Bucket uploads (50 MiB file limit in supabase/config.toml) |
| Auth | Hosted Supabase Auth (email/OAuth providers per dashboard — not audited in T3-0) |
Largest tables (public schema):
| Table | Est. rows | Size | Notes |
|---|---|---|---|
irs_organizations |
~1,987,840 | 1224 MB | IRS Exempt Orgs directory (public watchdog core) |
fwa_flags |
~61,967 | 22 MB | Fraud/waste/abuse flags |
org_findings |
~31,217 | 13 MB | Investigation hub |
org_data_provenance |
~31,476 | 13 MB | Provenance tracking |
org_status_changes |
~30,572 | 10 MB | Status delta engine |
state_charity_registrations |
~1,758 | 568 kB | State registry data |
| (remaining 34 tables) | mostly small / empty | User features, crawl queue, Stripe subs |
Migration inventory (repo):
| Source | Count |
|---|---|
Legacy migrations/*.sql |
9 |
supabase/migrations/*.sql |
17 |
| Total SQL migration files | 26 |
pg_cron jobs (hosted, 5 active):
| Job | Schedule | Purpose |
|---|---|---|
| 18 | Sun 03:00 UTC | Queue weekly recrawl jobs (100 orgs/week) |
| 19 | Daily 04:00 UTC | Fail stuck crawl jobs |
| 20 | Sun 05:00 UTC | Log orgs needing score refresh |
| 21 | 1st of month 02:00 UTC | Log orgs needing IRS status check |
| 22 | Sun 06:00 UTC | Purge old sent alert_queue rows |
Supabase Edge Functions (8):
| Function | verify_jwt |
Role |
|---|---|---|
run-crawl |
false | Execute crawl jobs |
compute-score |
false | Score calculation |
scheduled-recrawl |
false | Scheduled recrawl orchestration |
run-validations |
false | Data validation passes |
process-status-changes |
false | Status change processing |
process-score-runs |
false | Score run queue |
send-alert-digests |
false | Email alert digests |
All internal/cron-style (verify_jwt = false in supabase/config.toml).
1.3 Repo and tech stack¶
| Field | Value |
|---|---|
| Repo | Last-1-Enterprises/RealOutcomes (private) |
| Branch | main |
| Pattern | Express 5 + Vite 7 React SPA monolith (same shape as last1.app / pre-L2 last1.id) |
| Runtime | Node 20 (Dockerfile; package.json dev on tsx) |
| ORM / DB | Drizzle + pg pool; Supabase JS client for Auth/Storage |
| UI | React 18, Wouter, Radix, Tailwind, TanStack Query, Recharts |
| Languages (GitHub) | ~1.77 MB TypeScript, PL/pgSQL migrations |
Key server modules: server/routes.ts (large API surface), server/stripeService.ts, server/firecrawl.ts, server/scoring/, IRS import jobs under server/jobs/, admin gateway auth in server/auth.ts.
Marketing/app: Monolith at apex today. Consolidation plan scopes marketing → Cloudflare Pages during this tier (not a separate pre-tier migration).
1.4 Data classification¶
| Data class | Present? | Notes |
|---|---|---|
| LastVet veteran ePHI | No | Out of Phase 1 clinical scope |
| 42 CFR Part 2 records | No | Not a behavioral-health record store |
| Clinical PHI | No | Nonprofit / IRS / grant transparency product |
| Real user PII | Minimal | 3 auth users; emails in users / profiles; watchlists (1 watchlist row) |
| Public registry bulk data | Yes (dominant) | ~2M IRS org rows; political/FEC enrichment; FWA flags |
| Payment PII | Stripe-handled | Subscriptions table empty in snapshot; Stripe is PCI boundary |
| Operator/admin access | Yes | Domain-gated admin (last1.enterprises, last1.org, realoutcomes.org) + admin gateway from last1-admin |
Phase 1 LastVet note: RealOutcomes is not the mental-health coordination wedge. No VA API dependency.
1.5 User count¶
| Metric | Count (2026-08-28 Supabase) |
|---|---|
auth.users |
3 |
public.users |
3 |
public.user_profiles |
1 |
stripe_subscriptions |
0 |
watchlists |
1 |
Verdict: Pre-user / founder-era. Stripe price IDs configured; no active subscription rows in DB snapshot. Safe for full-stack rehearsal with low customer blast radius.
2. Dependencies¶
2.1 Upstream (RealOutcomes consumes)¶
| Service | Mechanism | Post–Tier 2 status |
|---|---|---|
| Supabase Auth | @supabase/supabase-js; Bearer token on API routes |
Hosted cpocwrygxlqhzvmlrpjv until migration |
| Postgres | DATABASE_URL / Drizzle |
Hosted Supabase pooler |
| Supabase Storage | Uploads bucket | Hosted |
| Supabase Edge Functions | Invoked from app + pg_cron side effects | Hosted Deno runtime |
| Stripe | STRIPE_* keys, webhooks |
External SaaS (stays external; keys rotate) |
| SendGrid | Transactional email | External (hello@realoutcomes.app) |
| OpenAI | Scoring narratives / analysis | External API |
| Firecrawl | Website crawl enrichment | External API |
| FEC API | Political connection enrichment | Open FEC (optional key) |
| last1.id / auth.last1.id | Not primary auth | Optional LAST1_CERT_REGISTRY_URL badge only (defaults false) |
RealOutcomes does not use auth.last1.id as its IdP today. Auth is native Supabase Auth on its own project.
2.2 Downstream (others consume RealOutcomes)¶
| Consumer | Dependency |
|---|---|
| last1-admin | POST /api/proxy/realoutcomes with shared ADMIN_GATEWAY_SECRET; admin UI /admin/realoutcomes/* |
| Public web | realoutcomes.app end users (minimal today) |
| Stripe | Webhook endpoint on Railway origin (must update on cutover) |
No other Last 1 product reads RealOutcomes Postgres directly.
Admin coupling (Tier 6 note): last1-admin remains on hosted Supabase for its own auth until Tier 6. RealOutcomes cutover only requires REALOUTCOMES_API_BASE + gateway secret alignment on admin's Railway env (unchanged until admin Tier 6).
2.3 External SaaS summary¶
| Vendor | Secrets in BWS/Railway | Rotation in migration? |
|---|---|---|
| Stripe | API keys, webhook secret, 4+ price IDs | Yes (D-live) |
| SendGrid | API key | Yes (D-live) |
| OpenAI | API key | Yes (GG invalid — verify live) |
| Firecrawl | API key | Yes (D-live) |
| FEC | Optional API key | Low risk |
| Supabase hosted | URL, anon, service role, DB password | Replace with R940 credentials at cutover |
3. Complexity vs Tier 2 (last1.id)¶
| Dimension | Tier 2 (last1.id) | Tier 3 (RealOutcomes) |
|---|---|---|
| Scope | App + Postgres + GoTrue + Kong + DNS + marketing split | App + Postgres + Edge Functions + pg_cron + Storage + IRS cron |
| Auth model | Public IdP (auth.last1.id) |
Product-local Supabase Auth (3 users) |
| Ingress | Kong SPA gap (lesson learned) | Single monolith port 5000 (simpler) |
| Database size | Identity DB (moderate) | ~1.3 GB, 2M-row bulk table |
| Migrations | 71+ hosted history | 26 files in repo + hosted state |
| RLS | ~34 policies (sampled) | 40/40 public tables RLS enabled |
| Background work | Email/OIDC jobs | 5 pg_cron + 8 Edge Functions + monthly IRS Railway cron |
| Payments | None on IdP | Stripe subscriptions (configured, 0 rows) |
| User blast radius | Founders + OIDC partners | Pre-user |
| Downstream deps | Many products on issuer | last1-admin proxy only |
| Marketing split | Tier 2 L2-6/L2-7 done | Still monolith — must plan in Tier 3 |
Similar pattern: Railway Docker monolith + Supabase Postgres + Vite client.
Harder than Tier 2: Edge Function replatforming, large bulk DB migration, pg_cron portability, Stripe webhook cutover, Firecrawl/OpenAI operational secrets.
Easier than Tier 2: No public IdP / Kong / partner OIDC matrix; minimal users; no clinical compliance trip-wires.
Tech debt / gotchas¶
- Dual migration paths — legacy
migrations/andsupabase/migrations/both exist; production state must be reconciled from hostedschema_migrations, not repo count alone. ADMIN_GATEWAY_SECRETalias chain — shared with last1-admin; do not rotate independently during parallel soak without coordinating both sides.- Edge Functions all
verify_jwt = false— rely on network/service-role invocation; R940 design must preserve same trust boundary (no public anonymous triggers). - IRS bulk imports — monthly cron + large tables; restore must preserve indexes (
trgmon org search per recent migrations). - Node 20 vs 22 — Dockerfile uses Node 20; last1.id uses 22. Align in T3-1 (22 OK unless native deps block).
- No
railway.tomlmemory — size R940 VM after Railway metrics export in T3-1.
4. GitGuardian D-live keys (RealOutcomes repo)¶
Sub-group D3 — six incidents in Last-1-Enterprises/RealOutcomes (.env paths, commits 9d610baa39ac, a44c03a2ddb7, 7cab01a19c2c).
| GG ID | Detector | GG validity | Service | T3 rotation plan |
|---|---|---|---|---|
| 29169495 | Stripe Webhook Secret | no_checker | Stripe webhook HMAC | Rotate in Stripe Dashboard; update BWS REALOUTCOMES_R940_* before cutover |
| 29169496 | OpenAI API Key | invalid | OpenAI | Verify dead in OpenAI dashboard; issue new key if any usage |
| 29169497 | JSON Web Token | no_checker | Supabase JWT | Not "defer T6" — this project migrates in Tier 3; new JWTs from R940 Supabase/GoTrue at cutover |
| 29169498 | PostgreSQL Credentials | valid | Hosted Postgres | Replaced by R940 DB credentials; revoke hosted pooler password post-teardown |
| 29169499 | SendGrid Key | valid | SendGrid | Rotate in SendGrid; store in BWS |
| 29169500 | Firecrawl API Key | valid | Firecrawl | Rotate in Firecrawl dashboard |
Not in RealOutcomes repo (last1.app cross-repo leaks): Stripe 29615733, Claude 29615732, etc. — handle under Tier 5 bundle, not this tier.
BWS setup (T3-1): adopt REALOUTCOMES_R940_PROD_* prefix per BITWARDEN_NAMING_CONVENTION.md; sync from Railway GraphQL read-only; rotate D-live keys into new prefix before T3-5 cutover; never infer secrets from last1-admin or last1.id projects.
5. Risk assessment¶
| Risk | Severity | Notes |
|---|---|---|
| Large DB restore failure / long downtime | High | 1.3 GB + indexes; rehearse pg_dump/pg_restore in T3-1 |
| Edge Function parity gap | High | Crawl/score/alert pipeline stops if functions not replatformed |
| pg_cron + Edge Function coupling | Medium | Cron job 20 logs "scoring done by Edge Function" — both must work |
| Stripe webhook miss during cutover | Medium | Update endpoint URL; run test checkout in T3-4 |
| Gateway secret drift vs last1-admin | Medium | Admin proxy breaks; keep Railway admin on old secret until admin Tier 6 |
| IRS monthly import failure | Medium | BMF/ARL scripts need R940 timer + same env as web |
| Wrong pre-user assumption | Low | Confirmed 3 auth users; re-check before T3-5 |
| Clinical / Part 2 / VA data leak | None expected | Product scope is nonprofit transparency |
Pre-user status¶
Confirmed: 3 Supabase auth users, no active Stripe subscription rows, production traffic is bulk public IRS data + internal operator use.
Blast radius¶
| Audience | Impact if migration fails |
|---|---|
| Paying customers | Minimal today (pre-user) |
| Ryan / operators | Admin RealOutcomes dashboard via last1-admin proxy |
| Public site | Directory/search on realoutcomes.app |
| Other Last 1 products | None direct (admin proxy only) |
Rollback complexity¶
| Phase | Rollback |
|---|---|
| Through T3-4 parallel soak | Railway + hosted Supabase remain live; R940 is shadow |
| Post T3-5 DNS cutover | Repoint DNS to Railway; keep hosted Supabase until T3-7 teardown decision |
| Post T3-7 hosted Supabase delete | Irreversible — require Gate B dump + restore drill first (Tier 2 pattern) |
Keep Railway prod frozen snapshot until T3-7 sign-off (same discipline as last1.id rollback net).
6. Recommended plan structure¶
Use a compressed phase model (Tier 2 / n8n hybrid), not 8 weeks:
| Phase | Duration | Deliverables |
|---|---|---|
| T3-0 | 1 session | This audit (done) |
| T3-1 | ~1 day | REALOUTCOMES_MIGRATION_PLAN.md + Railway env audit + BWS REALOUTCOMES_R940_* + VMIDs + Edge Function strategy decision |
| T3-2 | ~1 day | R940 DB VM + Postgres 17 + Gate B dump/restore rehearsal |
| T3-3 | ~1 day | R940 app VM + Docker deploy + parallel soak (Railway still prod) |
| T3-4 | ~1 day | Edge Functions + pg_cron + IRS cron on R940; Ryan browser smoke + Stripe test webhook |
| T3-5 | ~2 h | DNS cutover realoutcomes.app → R940 tunnel |
| T3-6 | 48–72 h | Soak (consolidation plan: customer-data tier) |
| T3-7 | ~1 day | Railway teardown + hosted Supabase delete + runbook + muster close + GG D-live close |
Total: ~5–8 working days after T3-1 planning sign-off.
Real data migration considerations (must enumerate in T3-1)¶
| Supabase capability | Migration approach (decide in T3-1) |
|---|---|
| Postgres 17 | Dedicated R940 DB VM (sovereign-db-realoutcomes or similar); ZFS dataset |
| Auth (GoTrue) | Self-host on app VM or co-locate with last1.id pattern — 3 users can be re-invited |
| RLS (40 tables) | Migrate policies via pg_dump --schema-only + verification queries |
| pg_cron (5 jobs) | Enable pg_cron on R940 Postgres; replay cron.job definitions |
| Edge Functions (8) | Option A: Supabase self-hosted stack on R940 · Option B: port to Express + systemd timers |
Storage (uploads) |
rclone/Supabase CLI bucket sync to MinIO or local Supabase Storage |
| Realtime | Audit usage (not prominent in repo); disable until proven needed |
| Supabase CLI migrations | Checked-in supabase/migrations/ become source of truth on R940 |
Pre-work before T3-1 execution (Ryan / Cursor)¶
| Item | Owner | Blocker? |
|---|---|---|
| Approve this T3-0 audit | Ryan | Yes |
| Railway GraphQL env audit (all vars + service IDs + memory) | Cursor | Strongly recommended |
| Choose Edge Function strategy (self-hosted vs port) | Ryan | Yes |
BWS project + REALOUTCOMES_R940_* naming |
Ryan | Yes |
| VMID allocation (app + db) on pve-r940 | Ryan | Yes |
| Marketing Pages split: parallel vs deferred | Ryan | Recommended |
Gate B dump rehearsal from cpocwrygxlqhzvmlrpjv |
Cursor | Before T3-5 |
| D-live rotations (Stripe, SendGrid, Firecrawl, OpenAI) | Ryan + Cursor | Before T3-5 |
Update last1-admin REALOUTCOMES_API_BASE only at cutover |
Tier 6 admin | Not during Tier 3 unless testing staging hostname |
7. T3-0 verification matrix (for execution)¶
Execute before DNS cutover when Ryan authorizes T3-1+:
| Check | Method | Pass criteria |
|---|---|---|
| Public directory | Browser/curl | /api/directory returns org data from R940 |
| Auth login | Browser | Supabase auth → app shell |
| Admin gateway | last1-admin proxy (Railway admin OK for test) | /api/admin/stats via gateway returns 200 |
| Stripe webhook | Stripe CLI or test event | 200 on R940 origin |
| IRS cron | Manual npm run import:monthly-irs on R940 |
irs_sync_logs updated |
| pg_cron | SELECT * FROM cron.job_run_details |
Jobs fire on schedule |
| Edge crawl/score | Trigger test org recrawl | crawl_jobs / score_runs progress |
| Rollback | DNS back to Railway | <15 min recovery |
8. Investigation limits (T3-0 honesty box)¶
- No local clone of RealOutcomes; repo facts via GitHub API + Supabase MCP.
- Railway live env vars / service IDs / memory not read (T3-1 action).
- Stripe dashboard (live vs test mode) not verified.
- Edge Function invocation graph (which cron paths call which function) not traced line-by-line.
- Cloudflare DNS record types for apex not re-verified live.
- No VM provisioning, dump, or deploy performed in T3-0.
9. Recommended Ryan decisions (post-audit)¶
- Authorize T3-1 — migration plan + Edge Function strategy + VM sizing.
- Confirm pre-user posture is acceptable for first full-stack cutover.
- Confirm D-live rotation window (Stripe/SendGrid/Firecrawl) as part of BWS setup, not afterthought.
- Marketing split timing — parallel Pages project vs post-cutover follow-up.
- Soak duration — default 48–72 h per consolidation plan.
10. Related documents¶
- TIER_RESEQUENCING_2026-08-28.md
- TIER_3_ADMIN_PAUSED_STATUS.md
- LAST1_ID_MIGRATION_PLAN.md / runbook (Tier 2 pattern)
- N8N_MIGRATION_RUNBOOK.md (compute-only precedent)
- GITGUARDIAN_CATEGORY_D_REFINEMENT_2026-08-27.md (D3 RealOutcomes)
- Consolidation plan § RealOutcomes (
LAST1_INFRASTRUCTURE_CONSOLIDATION_PLAN.mdonmain) - Muster:
migrate-realoutcomes-railway-supabase-to-r940
T3-0 verdict: RealOutcomes is ready for planning sign-off as Tier 3 first full-stack migration. Scope is bounded by pre-user status, own Supabase project, and clean admin coupling. Edge Functions + pg_cron are the highest engineering unknowns and must be resolved in T3-1 before provisioning.