Backend CI/CD & Environment Isolation

Status: Phases A/B done, Phase C shipped 2026-07-09, Phases D–F designed but not built

This is the design of record for bringing the Supabase backend under the same governed, automated, quality-gated CI/CD the frontend already has. Phases A (foundational hygiene) and B (provision a separate non-prod project) are done — Nefoxx now runs on two independently managed Supabase projects, verified at parity (see Environment Strategy for the full architecture and promotion runbook). Phase C (.github/workflows/backend-ci.yml, the PR-time validation gate) shipped 2026-07-09. Phases D–F (auto-deploy to non-prod, manual production promotion, governance/monitoring/DR) remain designed here, not yet built — picked up when the need arises. See dev-tracker NFX-027 for live status.

Context

Nefoxx now runs on two Supabase Cloud projects — Nefoxx-Prod (unchanged, the original project) and Nefoxx-Dev (provisioned 2026-07-08, Free tier, serves both devv.nefoxx.com and uatt.nefoxx.com). The shared-backend risk this initiative originally set out to close — devv/uatt writing to the live production database — is resolved. Full topology, verified parity status, and the promotion runbook: Environment Strategy.

What remains: PRs touching supabase/ are now validated by backend-ci.yml (migration replay + pgTAP, Edge Function tests/type-check — Phase C). Backend deploys themselves (migrations, Edge Functions) are still manual supabase db push / functions deploy from a developer machine, with no CI-driven promotion — that gap is Phases D and E below. Project config-as-code is partway there: supabase/config.toml now enumerates every Type-B function's verify_jwt setting (32 of 67), used by supabase start (backend-ci, E2E, local dev alike) and by supabase functions deploy's deploy-all mode.

Goal: finish giving the backend the same GitHub Actions-driven, quality-gated, manual-promotion pipeline the frontend has (ci.yml/deploy.yml/deploy-uat.yml/deploy-prod.yml), and keep version-controlling what still isn't (cron job schedules and storage bucket definitions exist only as live database state today on both projects — see NFX-033).

Decisions of record

• Cutover direction (done): the original project stayed Production, untouched; a new, empty Nefoxx-Dev project was provisioned and the schema replayed into it via a verified baseline migration. Zero downtime, zero risk to live users/data/storage.
• Topology: two persistent projects (live). Nefoxx-Dev (serves both devv and uatt frontends) + Nefoxx-Prod (unchanged). Actual added cost: $0/mo — Nefoxx-Dev is Free tier, not the originally budgeted ~$10/mo Pro-tier project (see the Cloud-vs-self-host table below for the full picture). This came with a trade-off the original cost estimate didn't model: Free-tier projects auto-pause after 7 days of inactivity — a design consideration for Phase D, not a blocker (see that section).
• Mechanism: fully self-managed GitHub Actions + Supabase CLI — confirmed viable, not just planned. Per-PR isolation uses an ephemeral local Supabase stack inside the CI runner (supabase start in Docker → migrations applied from scratch → tests → torn down), the same disposable-container pattern as the SonarQube scan job. This mechanism was unproven when originally designed — a migration-history crisis (NFX-030: the old YYYYMMDD_NNN_ naming convention collided on Supabase's version parser, and production's own tracking table was independently desynced) meant supabase start/db reset could not actually replay from scratch until that was fixed. It's fixed now — proven via a clean local db reset and a byte-identical db diff --linked against live prod.
  • Supabase Branching was considered and dropped: it can only be driven by the native GitHub integration, which contradicts the self-managed premise. Remains a future opt-in only if a hosted, browser-reachable per-PR preview URL is ever specifically wanted — see the Scaling Considerations table below (item 3) for the concrete revisit trigger.

Cloud vs. self-host verdict

Recommendation: stay on Supabase Cloud; run two projects. Do not self-host.

Dimension	Supabase Cloud (2 projects)	Self-hosted (VPS/EC2)
Monthly $	~$40 total (current ~$40 prod + $0 dev, Free tier — cheaper than the original ~$50 estimate)	Server ~$40–80 for adequate RAM (full stack needs ~8GB) + your time
Backups / PITR	Managed daily backups; PITR add-on available	You build and test backups + PITR yourself
Ops overhead	Near-zero (patching, upgrades, HA handled)	You are the DBA: patching, security, upgrades, HA
Reliability	Managed SLA, read replicas available	Single box unless you build HA
Scalability	Vertical slider + read replicas, one click	Manual re-provisioning, migration downtime
Security	Managed, SOC2, network isolation	Entirely your responsibility
Solo-founder fit	Strong — money buys back operational risk	Poor — cost is dominated by time + failure risk

The Free-tier dev project makes isolation cheaper than planned, not more expensive. Revisit trigger: reconsider self-hosting only once the platform has scaled with established revenue, the Cloud bill has grown materially significant (into the thousands/month), and a dedicated platform/DevOps owner exists. (This is the same revisit-trigger pattern the Scaling Considerations section below applies to every deliberately-deferred item — this table is where that pattern originates in this document.)

Target architecture

Full architecture diagram and verified parity status: Environment Strategy §1 — not duplicated here, to avoid two architecture diagrams for the same topology silently drifting out of sync (exactly the class of bug NFX-032 found in this initiative's own cron-parity claims). This document owns the CI/CD mechanism diagram instead — see below.

• Frontend VITE_SUPABASE_URL/VITE_SUPABASE_ANON_KEY are per-environment (done): dev+uat builds → Nefoxx-Dev; prod build → Nefoxx-Prod, injected via GitHub Environment variables in deploy.yml. Full mechanism: Environment Strategy §3.
• Secrets: Edge Functions themselves are fully deployed to both projects (67/67, matched verify_jwt). Actual secret values are partial — Cloudflare/Zoom pending (NFX-035, user-owned), Shoonya deliberately not replicated (confirmed stale/unused even on prod — NFX-034).

End-to-end flow

CI is built and live (Phase C). NONPROD and PROD are the Phase D/E design below, not yet built.

Completeness model — the three deployable artifact classes

There are only three classes of deployable backend artifact — triggers, DB functions, and RPCs are not separate deploy steps, they are SQL objects inside migrations:

1. Database objects (tables, indexes, RLS, triggers, DB functions, RPCs, views, enums, grants, the pg_cron/pg_net extensions) → all SQL inside supabase/migrations/*.sql → shipped by supabase db push. Exception, confirmed today: pg_cron scheduled jobs (the data rows in cron.job) and storage bucket definitions are not migration-tracked on either project — they exist only as live database state, correctly configured and at parity, but outside version control. See NFX-033.
2. Edge Functions (67 today) → supabase functions deploy (all in one command).
3. Project config (auth, JWT, per-function verify_jwt, storage buckets) → supabase/config.toml — exists, and as of Phase C enumerates all 32 Type-B functions (was previously a 3-entry, E2E-scoped subset; one of those 3 was also wrong — trade-planner-view-shared is actually Type A on production, corrected during Phase C) → applied by the CLI.

Anti-drift guarantee, corrected placement: supabase db diff (shadow-DB + migra) is not run as part of the PR gate (Phase C) — traced through what it actually computes, it answers "has the linked project drifted from what migrations reproduce right now," a project-state question unrelated to whether this PR's migration is valid, and it would show drift on every PR that adds a migration (since the target project hasn't received it yet). The real anti-drift guarantee lives in Phase D/E's post-deploy validation instead: run db diff --linked immediately after db push, when a non-empty result is an actionable signal about that specific deploy. A non-zero supabase start exit code in Phase C is what actually proves a PR's migration applies cleanly.

Senior-architect review — gaps to respect

A critical review surfaced real oversights the naive "replay migrations + deploy functions" framing misses. Each is folded into the phases below; status updated as of Phase C shipping (2026-07-09):

• A — Prod may already be drifted from its own migrations. ✅ Done, more thoroughly than planned.NFX-030 found prod's tracking was desynced (6/212 migrations recorded as applied, not just drifted) — required a full baseline squash, not an incremental catch-up migration. db diff --linked now returns "No schema changes found."
• B — Dashboard-only objects don't replay. Extensions: ✅ done (11/11 identical, in the baseline). Cron jobs + storage buckets: confirmed still not migration-tracked — see the Completeness model exception above and NFX-033. supabase_realtime publication membership: not verified either way yet — still open.
• C — Cron→Edge-Function calls with hard-coded prod URLs (safety). ✅ Resolved as a decision, not as originally recommended. All 26 cron jobs were replicated to Nefoxx-Dev active (not disabled, as originally recommended) with every net.http_post URL correctly rewritten per-project — the safety property (no cross-environment calls) holds; the "disable non-essential cron in dev" recommendation was deliberately overridden in favor of full parity.
• D — Storage buckets. Buckets are correctly provisioned and at parity (5/5, verified). The "belongs in migrations/config.toml" premise is not actually satisfied — same gap as cron jobs, see B above and NFX-033 (scope broadened 2026-07-09 to cover this).
• E — Auth/OAuth is per-project. Still open. Google OAuth for Nefoxx-Dev: NFX-031 (user-owned). Broader auth config parity (Site URL, redirect URLs, JWT expiry): NFX-036 (user-owned) — not verifiable via CLI/SQL, needs a manual Dashboard comparison.
• F — config.toml must enumerate per-function verify_jwt. ✅ Done (Phase C) — all 32 Type-B functions enumerated, sourced from production's own functions list -o json output, not assumed from naming convention.
• G — Destructive-migration rollback is not free (DR). Unchanged, Phase F territory. PITR is the only real recovery for destructive changes; take a snapshot/confirm backup immediately before a prod db push.
• H — Deploy ordering. Unchanged, still correct: migrations before functions (an EF usually depends on a new table/RPC) — reflected in Phase D/E below.
• I — Path-filter backend CI (cost). ✅ Done, not just recommended — backend-ci.yml is path-filtered to supabase/** on both pull_request and push triggers.
• J — The CI access token is account-wide. ✅ Improved, not just accepted. Supabase supports project-scoped Personal Access Tokens ("Scoped PAT," distinct from the account-wide PAT this repo used manually so far). Phase D/E now specify separate scoped tokens per environment (SUPABASE_DEV_ACCESS_TOKEN, SUPABASE_PROD_ACCESS_TOKEN) instead of one account-wide token — this directly shrinks the blast radius rather than only compensating for it with a manual gate.
• K — The tracked-secrets exposure (NFX-013) is moderate, not catastrophic. Untracking: done. Anon key rotation: not confirmed done — worth a quick check.

Phased rollout

Phase A — Foundational hygiene + prove the baseline — done

Prod-drift verification, dashboard-only-object audit (extensions done; cron/buckets still open, see gap B/D), NFX-013 resolved (untracked; anon-key rotation still to confirm), supabase/config.toml created and — as of Phase C — fully enumerates Type-B verify_jwt. Backfilling tests for untested EFs (NFX-005, 26 functions) remains open and is still a real prerequisite before Phase D can safely auto-deploy EF changes.

Phase B — Provision the non-prod (Dev) project — done

Nefoxx-Dev created (Free tier, ap-south-1, matching prod), schema replayed via the verified baseline migration, storage buckets + all 67 Edge Functions + all 26 cron jobs replicated at parity (cron URLs correctly rewritten per-project). Two decisions superseded the original plan, deliberately:

• Data replication: the plan originally called for copying real auth.users/profiles as a one-time PII snapshot. Superseded — synthetic seed users (supabase/seed.sql, 3 *.local test accounts) were used instead, after concluding real PII had no functional testing benefit and added exposure risk in a second, lower-scrutiny environment.
• Cron in dev: the plan originally recommended disabling non-essential/market-data cron jobs in dev to reduce external API load. Superseded — full parity was chosen instead; all 26 jobs run active on Nefoxx-Dev.

Frontend per-environment wiring (dev/uat → Nefoxx-Dev, prod → Nefoxx-Prod) is live in deploy.yml.

Phase C — Backend CI gate on PRs (non-deploying), isolated per-run — shipped 2026-07-09

.github/workflows/backend-ci.yml, path-filtered to supabase/** on both pull_request and push. Two independent, parallel jobs (no needs: between them — neither deno test/deno check nor pgTAP needs the other's runtime):

• db-migrations-pgtap: checkout → migration-immutability check (no edits to already-merged migration files, via the GitHub API, same idiom deploy-uat.yml/deploy-prod.yml use for check-runs) → SHA-pinned supabase/setup-cli@v3.0.0 (no version: input — auto-detects from package-lock.json, keeping CI's CLI identical to local npm ci) → supabase start -x studio,analytics,vector,imgproxy (a fresh runner's empty Docker volume means this single command is "replay all migrations from scratch," and also runs seed.sql once automatically — a non-zero exit already proves the PR's migration is valid; no separate db reset call, since seed.sql isn't idempotent and a second run would fail on a duplicate-key violation) → npm run test:db (pgTAP) → supabase stop.
• edge-functions: checkout → SHA-pinned denoland/setup-deno@v2.0.5 → npm run test:ef (now correctly permissioned — the script was missing --allow-env --allow-net --allow-read and had apparently never completed a run as originally written) → deno check across all .ts files. Both steps run with continue-on-error: true (visibility-first rollout, matching this repo's own established pattern for the Sonar baseline gate and E2E regression): the job starts red on two pre-existing, unrelated bugs — NFX-004 (a type error that blocks deno test's whole-directory type-check entirely, so it runs zero tests until fixed) and NFX-037 (an isolated test-fixture mismatch, root-caused to the documented archetype-scoring formula genuinely producing a different result than one test's title assumed — 1804 other assertions pass cleanly once NFX-004's type-check blocker is bypassed with --no-check). Promote to a required check once both are fixed.

No cross-wiring into ci.yml — a PR touching only src/ never triggers backend-ci.yml, and it doesn't gate ci.yml's deploy-dev job (frontend-only, never touches Supabase). Once NFX-026 lands (branch protection), both workflows' jobs become independent required-status-checks — GitHub correctly treats a path-filtered-out (skipped) required check as "not applicable," not "failing."

Phase D — Auto-deploy to non-prod on develop — designed, not built

A new job inside backend-ci.yml (same path-filter, so it belongs alongside the jobs it gates on): deploy-backend-nonprod, needs: [db-migrations-pgtap, edge-functions], fires only on push to develop, environment: dev (reusing the frontend's existing GitHub Environment). Steps: supabase link --project-ref rchglrpywmxkcbmxcepl → supabase db push → supabase functions deploy (deploy-all, reading the now-complete config.toml) → post-deploy validation, four concrete checks (not a vague "health call"):

1. supabase db diff --linked returns empty (the anti-drift guarantee, correctly placed here per the Completeness model note above).
2. One RPC call (e.g. get_nifty50_market_mood) returns 200 with the expected shape.
3. One Type A and one Type B Edge Function each return their documented success shape for a trivial, read-only invocation.
4. verify_jwt parity check — re-run functions list -o json immediately after deploy and assert every function's verify_jwt matches config.toml's declared exceptions. This is the automated version of the exact manual check that caught the trade-planner-view-shared bug during Phase C — codifying it here catches that class of drift on every deploy going forward. Any of the four failing fails the job loudly; no automatic rollback (matches this repo's existing frontend-deploy philosophy).

Secrets — scoped, not account-wide (gap J, above): SUPABASE_DEV_ACCESS_TOKEN (Nefoxx-Dev-scoped) in the dev GitHub Environment; SUPABASE_DEV_DB_PASSWORD alongside it. Neither exists yet — creating them (including confirming Supabase's project-scoping UI, Dashboard → Account → Access Tokens) is part of actually building this phase.

Idempotency: if this job fails between db push succeeding and functions deploy completing, the environment is safely left in a re-runnable state — db push skips already-applied migrations, functions deploy unconditionally redeploys regardless of prior state. Document "re-run the job" as the recovery procedure.

Migration-ordering policy for concurrent PRs: unlike most application code, two independently-valid migrations can combine into a broken sequence or collide outright, in a way a normal 3-way merge doesn't catch. Process policy (not new automation, proportionate to today's team size): any supabase/migrations/** PR must be rebased onto the latest develop and re-pass backend-ci.yml immediately before merging. Revisit trigger for actual automation (merge queue, or a bot that re-runs backend-ci on rebase): once more than one person is regularly opening migration PRs concurrently.

Free-tier auto-pause risk: a paused Nefoxx-Dev fails this job's link/db push steps outright. Interim workaround: a manual Dashboard visit to unpause. A proper keep-warm mechanism is still undesigned (candidate: piggyback on the nightly E2E regression job, NFX-029).

Phase E — Manual production promotion — designed, not built

deploy-backend-prod.yml, workflow_dispatch only, environment: production (scoped SUPABASE_PROD_ACCESS_TOKEN/SUPABASE_PROD_DB_PASSWORD), mirrors deploy-prod.yml's exact shape: verify-ci job using the gh api check-runs pattern — now checking for two job names (db-migrations-pgtap and edge-functions), not one — → pre-deploy safety snapshot (gap G) → migrations then functions (gap H) → the same four-part post-deploy validation defined under Phase D, run against prod. Never auto-fires on push. Prod only ever receives migrations already proven on non-prod (Phase D).

Phase F — Governance, monitoring, DR — designed, not built

• Branch protection on main/develop including the backend checks (extends NFX-026).
• Rollback/DR runbook: non-destructive → compensating forward migration; destructive/data-losing → PITR restore is the only real recovery (decide whether prod carries the add-on; always take the pre-deploy snapshot); EF rollback = git revert + redeploy; full-project loss → restore a managed backup into a new project.
• Monitoring — named priority, not generic. This codebase has a documented historical incident class: CLAUDE.md's own Composite RPC Pattern section records a real capture of 6 parallel admin-dashboard RPCs climbing 6.9s → 14.9s → 21.3s from connection-pool contention. Connection-pool exhaustion / RPC-latency alerting should be the first alert configured on prod, not an item in an unordered "enable alerts" list.
• Secrets rotation — proactive, not just reactive. Beyond "rotate on any suspicion": a lightweight quarterly rotation cadence for both scoped access tokens and both DB passwords, as a calendar reminder — no new tooling required.
• Least-privilege: the scoped tokens from Phase D/E (gap J) are the concrete mitigation here now, not just "treat as high-value."

Scaling considerations (deferred)

What this plan intentionally excludes today, with a concrete trigger for when it stops being premature. Building all of these now would itself be a production-grade anti-pattern — capacity spent on problems the platform doesn't have yet, at the cost of problems it does (Phase D/E don't exist yet; that's the actual priority). The discipline is naming the gap and its trigger, not closing every gap immediately — the same posture the Cloud-vs-self-host verdict above already takes.

#	Gap	Why not now	Concrete revisit trigger
1	Migration replay time will grow as more migrations land post-baseline.	One baseline, effectively zero post-baseline migrations today.	Post-baseline migration count exceeds ~100, or db-migrations-pgtap's supabase start step exceeds ~5 minutes. Mitigation: periodic re-baselining, proactively this time (same mechanism as NFX-030's fix).
2	functions deploy with no args redeploys all 67 every time, even for a one-function change.	Deno bundling is fast at 67 functions; deploy-all is simpler and already proven (this initiative's own manual parity-fix deploy).	Deploy frequency + function count grow enough that deploy-all wall-clock time becomes real friction. Mitigation: git-diff-based targeting to deploy only changed functions.
3	No hosted, reviewable per-PR preview environment (Supabase Branching, evaluated and dropped above).	Solo-developer repo; ephemeral CI-only isolation is sufficient.	Team grows beyond one active backend contributor, or QA specifically needs a browsable per-PR preview.
4	seed.sql will need to grow as pgTAP coverage grows from zero.	Zero pgTAP tests exist yet; 3 seed users already exceed current needs.	seed.sql exceeds a few hundred lines, or tests need fixtures spanning multiple domains. Mitigation: split into supabase/seeds/*.sql by domain.
5	No DORA-style deployment metrics (frequency, change-failure rate, MTTR).	GitHub Actions run history is genuinely sufficient for one person.	Team or deploy frequency grows enough that retrospectives need trend data the UI doesn't surface.
6	No automated rollback — a failed validation fails loudly, a human runs the runbook.	Matches this repo's existing frontend-deploy philosophy; a human is always the one deciding anyway.	A dedicated on-call/ops rotation exists, making "someone is always available" no longer safe to assume.
7	CI resource ceiling — GitHub-hosted runners cap at 7GB RAM / 2 cores.	Well within ceiling today.	pgTAP/EF test volume grows enough to approach it (job timeouts/OOM in logs). Mitigation: larger hosted runners, then self-hosted.
8	Nefoxx-Dev Free tier will eventually need Pro as CI-driven load grows.	Well within Free-tier limits today.	Approaching Supabase's current Free-tier invocation/DB-size caps — budget ~$10-25/mo at that point.
9	No compliance/data-residency posture beyond "both projects are in ap-south-1" (already correct).	Out of scope for a CI/CD plan; not triggered by anything above.	Real trading execution or broker-account integrations go live (the already-stale, confirmed-unused shoonya-nse-indices/NFX-034 is the closest this platform has come).

Two items deliberately excluded from this table as not real gaps: canary/traffic-split Edge Function deploys (Supabase doesn't support this at the platform level — an accepted constraint of the Cloud-vs-self-host verdict, not something to design around) and transactional linking of db push + functions deploy (both are independently idempotent/safe-to-retry, which is the correct production-grade property here — true distributed-transaction semantics would be over-engineering for what re-running a job already solves).

Files

Shipped (Phase A/B/C): supabase/config.toml (expanded); package.json (test:ef fixed); .github/workflows/backend-ci.yml (new); this document; documentations/portal/docs/dev-tracker/tracker.md (NFX-027/033 updated, NFX-037 added); .gitignore/.env.example/frontend deploy.yml env injection (all done under NFX-013/NFX-027, prior to Phase C).

Future (Phase D/E/F, not created now):

• .github/workflows/backend-ci.yml — add the deploy-backend-nonprod job (Phase D; lives inside this file, not a sibling workflow, so it shares the path-filter).
• .github/workflows/deploy-backend-prod.yml — manual prod promotion + two-job verify-ci (Phase E).
• New GitHub secrets: SUPABASE_DEV_ACCESS_TOKEN/SUPABASE_DEV_DB_PASSWORD (dev Environment), SUPABASE_PROD_ACCESS_TOKEN/SUPABASE_PROD_DB_PASSWORD (production Environment) — none exist yet.

Related tracker items

NFX-027 (this initiative) · NFX-005 (untested EFs — Phase D prerequisite) · NFX-013 (tracked secrets — Phase A, untracking done) · NFX-026 (branch protection — Phase F) · NFX-030 (migration-history fix that unblocked Phase C's core mechanism) · NFX-031 (Google OAuth for Nefoxx-Dev, gap E) · NFX-032 (parity re-audit + remediation — corrected this plan's own prior cron claim) · NFX-033 (cron + storage-bucket IaC gap, gaps B/D) · NFX-034 (stale Shoonya secrets, not replicated to dev) · NFX-035 (Cloudflare/Zoom secrets on Nefoxx-Dev, user-owned) · NFX-036 (auth config parity, gap E, user-owned) · NFX-004 and NFX-037 (current edge-functions job blockers, Phase C) · NFX-029 (E2E — shares the ephemeral-stack pattern, deliberately not the same schema-loading mechanism; see the Completeness model note above for why).