NutsNews Backend PostgreSQL Primary And Supabase Hot Standby

NutsNews Backend PostgreSQL Primary And Supabase Hot Standby

Architecture status: backend PostgreSQL is the production read/write primary. The existing production Supabase project remains the protected hot-standby target. Do not create a new Supabase project or nutsnews-standby database for this standby plan. Worker-uplift target ownership and coexistence are summarized in Architecture.

Simple Summary

NutsNews reads and writes through the private backend PostgreSQL database. The existing production Supabase database is kept as the locked backup target and must not receive app or worker writes before an approved failover.

Intermediate Summary

ramideltoro/nutsnews-backend provisions PostgreSQL on backend.nutsnews.com through the protected Ansible pipeline. PostgreSQL and Adminer are bound to 127.0.0.1 only, so operators must use an SSH tunnel. Backend PostgreSQL remains the production primary for app and worker reads/writes. The existing production Supabase project is the standby target for protected backend-primary recovery work. App, worker, and normal release workflows must not write to Supabase through standby credentials before an approved failover path passes lag, parity, schema, sequence, writer-pause, and split-brain gates.

Expert Summary

Backend issue #13 established the single-writer PostgreSQL path; later cutover work moved production reads/writes to backend PostgreSQL. Issue ramideltoro/nutsnews#223 keeps Supabase as a standby/recovery target, not an active writer. Sync-back and active-active writes are not supported; any Supabase promotion remains a controlled forward-recovery procedure to avoid split-brain.

Control Flow

flowchart TD
    A[Protected Backend Apply] --> B[Install PostgreSQL on backend]
    B --> C[Bind PostgreSQL to 127.0.0.1:5432]
    B --> D[Bind Adminer to 127.0.0.1:8082]
    E[NutsNews app and worker] --> F[Backend compatibility APIs]
    F --> G[Backend PostgreSQL primary]
    H[Existing production Supabase] -. protected hot standby target .-> G
    I[Standby manifest validator] --> H
    J[Failover gates] -. lag parity schema sequence writer-pause split-brain .-> H

Operating Model

Backend PostgreSQL is the only production read/write primary. The existing production Supabase project is retained as the hot-standby target and must stay isolated from normal app/worker write paths until an approved failover changes that policy.

The first restore path is logical dump/restore from the staging Supabase project into:

nutsnews_restore_rehearsal

The backend production database name reserved by the backend baseline is:

nutsnews_failover

The earlier primary-shadow database used for production-shadow migration gates is:

nutsnews_primary_shadow

Supabase Hot Standby Target Readiness

Related issue: https://github.com/ramideltoro/nutsnews/issues/496 Related manifest issue: https://github.com/ramideltoro/nutsnews/issues/497 Related reconciliation issue: https://github.com/ramideltoro/nutsnews/issues/498 Related relay issue: https://github.com/ramideltoro/nutsnews/issues/499 Related original app PR: https://github.com/ramideltoro/nutsnews/pull/507 Related correction app PR: https://github.com/ramideltoro/nutsnews/pull/509 Related probe runbook: NutsNews Supabase Standby Restricted Probe

Simple Summary

NutsNews is using the production Supabase database it already has as the locked backup place. Backend PostgreSQL stays in charge of normal reading and writing. A protected GitHub button checks the Supabase backup keys and database doorway without showing the keys.

Intermediate Summary

Issue #496 now adopts the existing production Supabase project/database as the hot-standby target for backend PostgreSQL primary. The app-repo Supabase Standby Credential Readiness workflow remains manual-only, requires the typed confirmation verify-supabase-standby-readiness, and enters the protected supabase-standby GitHub Environment before reading any standby values. The readiness check validates that the standby project ref matches NUTSNEWS_PRODUCTION_SUPABASE_PROJECT_REF, validates the production Supabase URL, direct Postgres URL, service-role key, and anon key aliases, then pipes the protected direct database URL to the restricted backend forced-command SSH probe. The backend owns the fixed read-only psql query and returns only READY on success. Normal production app and worker workflows still cannot read the standby service-role key or DB URL through these protected aliases, so Supabase writes remain unavailable until a later approved relay or failover path exists.

Expert Summary

The standby credential inventory is stored as supabase-standby Environment secrets in ramideltoro/nutsnews: NUTSNEWS_STANDBY_SUPABASE_PROJECT_REF, NUTSNEWS_STANDBY_SUPABASE_URL, NUTSNEWS_STANDBY_SUPABASE_DB_URL, NUTSNEWS_STANDBY_SUPABASE_SERVICE_ROLE_KEY, and NUTSNEWS_STANDBY_SUPABASE_ANON_KEY. These are protected aliases for the existing production Supabase project values, not credentials for a new project. The local validator enforces existing-production-supabase, requires NUTSNEWS_PRODUCTION_SUPABASE_PROJECT_REF, rejects malformed project refs, rejects a standby ref that does not match the production Supabase ref, rejects non-HTTPS project URLs, rejects pooler DB URLs, rejects DB URLs missing sslmode=require, rejects missing DB credentials, and rejects identical service-role/anon values. The direct-connectivity step runs on GitHub-hosted ubuntu-latest, prepares the dedicated probe SSH key with 0600 permissions, requires strict known-host checking, clears forwarding, requests no TTY, and pipes the protected direct DB URL to the existing backend. GitHub does not execute SQL or invoke psql; the backend restricted forced command owns the fixed read-only query. The workflow prints only safe metadata and the success/failure outcome; raw URLs, database users, passwords, API keys, PostgreSQL errors, and row data are not printed. App-repo regression coverage also scans workflows so no workflow other than supabase-standby-readiness.yml reads standby write credentials before a protected failover path is implemented. The readiness workflow is not a failover approval; lag <= 30 seconds, parity, schema, sequence, writer-pause, and split-brain gates remain required before any Supabase promotion.

Standby Schema And Replication Manifest

Issue ramideltoro/nutsnews#497 adds the app-repo standby manifest at supabase/standby_manifest.json, with validator scripts/supabase_standby_manifest.mjs, tests in tests/supabase-standby-manifest.test.mjs, and CI coverage through supabase-standby-manifest-regression.yml plus database-migration-gate.yml. The manifest is source-controlled metadata only; it does not connect to Supabase and does not grant write access.

Current manifest contract:

Field	Value
Migration head	20260717113000
Replicated base tables	15
Excluded views/materialized views	7
Sequence-backed tables	6
Schema fingerprint	9f8cffa694100f438abaddbf6e00db13ff969572e8c16fd4b8ee4afa5087088f

Replicated tables are the current public base tables from the Supabase migration contract. Views and materialized views are excluded from row replication and must be checked through schema and derived-query parity instead of copied as rows. Every replicated table must have a primary key or explicit replica identity; a missing identity fails the validator before merge. Sequence safety is explicit: before a Supabase promotion, each target sequence must be advanced beyond both the source last_value and the target table max(id), and promotion fails closed if any sequence is missing, stale, or unvalidated.

Destructive Supabase retirement work is blocked until this manifest exists and passes. A schema fingerprint mismatch blocks standby promotion. The manifest also restates the standing safety policy: backend PostgreSQL remains primary, the target is the existing production Supabase project, no new Supabase project or nutsnews-standby database is created, and app/worker Supabase writes remain withheld until approved failover.

Standby Reconciliation And Bootstrap

Issue ramideltoro/nutsnews#498 is backend-owned in ramideltoro/nutsnews-backend through .github/workflows/backend-supabase-standby-reconciliation.yml. The workflow uses the backend production-backend Environment, runs on GitHub-hosted ubuntu-latest, SSHes to the existing backend host, and performs the backend-primary-to-existing-production-Supabase comparison from that host. This keeps backend PostgreSQL credentials out of the app repo while still letting the backend reach the Supabase direct database endpoint.

Workflow modes:

Mode	Confirmation	Behavior
report	report-existing-production-supabase-standby	Compares schema fingerprint, migration contract fingerprint, table row counts, table row checksums, and sequence safety with safe metadata only.
apply-backfill	backfill-existing-production-supabase-from-backend-primary	Copies backend-primary rows into the existing production Supabase standby target with upsert semantics, advances target sequences above source and target max ids, then reruns the same report checks.

The report artifact is backend-supabase-standby-reconciliation. It may include workflow run URL, status, table counts, checksum digests, schema/catalog digests, sequence last/next values, and backfill row counts. It must not include database URLs, passwords, tokens, service-role keys, PostgreSQL error text, or raw row data.

This reconciliation workflow does not create a new Supabase project, create a nutsnews-standby database, expose Supabase as the production app/worker provider, or approve failover. Continuous backend-to-Supabase sync is owned by ramideltoro/nutsnews#499; lag monitoring and failover approval remain later #223 child gates.

Standby Continuous Sync Relay

Issue ramideltoro/nutsnews#499 adds the backend-owned continuous sync path in ramideltoro/nutsnews-backend through the protected .github/workflows/protected-backend-ansible-apply.yml workflow, the backend_baseline Ansible role’s standby_sync_relay.yml task file, and the nutsnews-supabase-sync-relay.timer systemd unit.

The relay is deliberately backend-local:

• backend PostgreSQL remains private on loopback and remains the source of truth;
• the locked nutsnews-standby-relay OS user runs a snapshot-apply relay on the backend host;
• each run reads the backend source tables over loopback, upserts changed rows, deletes target-only rows, advances sequences, and writes outbound to the existing production Supabase direct PostgreSQL endpoint;
• no Supabase-initiated inbound connection to backend PostgreSQL is required;
• the Supabase pooler, a new Supabase project, and a new nutsnews-standby database are not used.

The protected apply workflow supports:

Mode	Required setting	Behavior
check	NUTSNEWS_BACKEND_SUPABASE_SYNC_RELAY_ENABLED=true	Runs Ansible check mode against the backend host and reports the planned relay install/update.
apply	NUTSNEWS_BACKEND_SUPABASE_SYNC_RELAY_ENABLED=true plus confirm_apply=backend.nutsnews.com	Installs the relay script, reconciliation helper, root-owned credential file, contract, hardened service, and timer.

After install, .github/workflows/backend-supabase-sync-relay-smoke.yml proves live catch-up using only synthetic rows in public.staging_fixture_runs and public.staging_fixture_users. Insert, update, and delete must all catch up through the backend relay before #499 is closed.

Required protected backend values:

Protected value	Scope	Purpose
NUTSNEWS_BACKEND_SSH_PRIVATE_KEY	production-backend Environment secret	SSH key used by the protected backend Ansible workflow.
NUTSNEWS_BACKEND_KNOWN_HOSTS	production-backend Environment secret	Trusted backend host key used for strict SSH verification.
NUTSNEWS_BACKEND_POSTGRES_MIGRATION_VALIDATION_PASSWORD	production-backend Environment secret	Password for the PostgreSQL role used by the relay to read backend source state over loopback.
NUTSNEWS_PRODUCTION_SUPABASE_DB_URL	production-backend Environment secret	Existing production Supabase direct DB URL used as the standby target.
NUTSNEWS_BACKEND_HOST	production-backend Environment variable	Fixed backend address.
NUTSNEWS_BACKEND_SUPABASE_SYNC_RELAY_ENABLED	production-backend Environment variable	Must be true to install and keep the relay enabled.

Fail-closed rules:

• table identity must be safe on both source and target;
• source and target schema and migration-contract fingerprints must match before apply;
• target URL must be the direct Supabase PostgreSQL host on port 5432 with TLS, not the Supavisor pooler;
• the apply path upserts source rows, deletes target-only rows, and validates parity after each sync run;
• target sequences are advanced above source and target observed values;
• reports and summaries contain safe metadata only, never connection strings, passwords, tokens, PostgreSQL errors, or raw row values.

Operators must run and review check before apply. Disabling or removing the relay requires a follow-up reviewed backend change; do not remove standby sync resources as incidental cleanup.

flowchart TD
    A[Operator starts Supabase Standby Credential Readiness] --> B[Typed confirmation preflight]
    B --> C[Enter protected supabase-standby Environment]
    C --> D[Validate existing production Supabase aliases]
    D --> E[Pipe direct Postgres URL to restricted backend probe]
    E --> F[Backend runs fixed read-only query]
    F --> G[Step summary with safe metadata only]
    R[Existing backend forced-command SSH probe] --> E
    H[Normal app and worker workflows] -. cannot read standby secrets .-> C
    I[Backend PostgreSQL primary] --> J[Normal app and worker reads/writes]
    K[Failover gate] -. requires lag parity schema sequence writer-pause split-brain checks .-> D

Required setup:

Protected value	Scope	Purpose
NUTSNEWS_STANDBY_SUPABASE_PROJECT_REF	supabase-standby Environment secret	Existing production Supabase project ref. Must match NUTSNEWS_PRODUCTION_SUPABASE_PROJECT_REF.
NUTSNEWS_STANDBY_SUPABASE_URL	supabase-standby Environment secret	Existing production Supabase HTTPS API URL used as the standby target.
NUTSNEWS_STANDBY_SUPABASE_DB_URL	supabase-standby Environment secret	Existing production Supabase direct Postgres URL. The app workflow pipes it to the restricted backend probe over SSH stdin; GitHub does not run psql.
NUTSNEWS_STANDBY_SUPABASE_SERVICE_ROLE_KEY	supabase-standby Environment secret	Existing production Supabase elevated API credential for later protected relay/failover work only.
NUTSNEWS_STANDBY_SUPABASE_ANON_KEY	supabase-standby Environment secret	Existing production Supabase low-privilege API credential retained in the protected environment until failover approval.
NUTSNEWS_STANDBY_PROBE_SSH_PRIVATE_KEY	supabase-standby Environment secret	Dedicated private key for the restricted backend probe identity.
NUTSNEWS_STANDBY_PROBE_KNOWN_HOSTS	supabase-standby Environment secret	Independently verified backend host key for strict SSH host checking.
NUTSNEWS_STANDBY_PROBE_HOST	Repository/environment variable	Fixed backend probe address.
NUTSNEWS_STANDBY_PROBE_USER	Repository/environment variable	Fixed restricted probe username.

Operational notes:

• Do not create a new Supabase project or nutsnews-standby database for this plan. The intended standby target is the existing production Supabase project.
• Direct database readiness uses the db.<project-ref>.supabase.co:5432/postgres?sslmode=require form, not Supavisor pooler URLs.
• Backend PostgreSQL remains the primary read/write database. This workflow does not change provider mode and does not expose Supabase write credentials to production app or worker jobs.
• The workflow is a credential readiness gate only. It does not migrate schema, copy data, install a sync relay, reconcile existing Supabase state, promote Supabase, or approve failover.
• The standby manifest is a schema and safety gate. It defines the intended replication surface but still does not approve failover.
• The backend reconciliation workflow is the #498 bootstrap gate. It can copy backend-primary rows to the existing production Supabase standby target only through the protected production-backend Environment and typed confirmation.
• The backend relay workflow is the #499 continuous sync gate. It can install or roll back the backend-local trigger-ledger relay only through the protected production-backend Environment and typed confirmation.
• Before failover, operators still need lag <= 30 seconds, parity checks, schema fingerprint checks, sequence safety checks, writer-pause evidence, and split-brain checks.
• If the workflow fails on missing secrets, populate the protected supabase-standby Environment with aliases for the existing production Supabase values and rerun it. Do not paste values into issues, PRs, docs, or chat.

Risks and mitigations:

Risk	Mitigation
Standby credentials accidentally become app or worker runtime inputs before failover	The app-repo regression scans workflow secret usage and allows standby secret reads only in supabase-standby-readiness.yml for this phase.
A pooler URL passes as direct database readiness	The validator requires the direct db.<project-ref>.supabase.co host and TLS.
A new standby project is accidentally introduced	The validator requires the standby ref to match NUTSNEWS_PRODUCTION_SUPABASE_PROJECT_REF and rejects mismatches.
Readiness is mistaken for failover approval	Workflow and docs state that lag, parity, schema, sequence, writer-pause, and split-brain gates still have to pass before failover.
Readiness output leaks credentials	The workflow summary records only safe metadata and the restricted probe success result.

Rollback:

• Revert PR #509 if the owner decides to restore a separate-project standby policy, then update issue #223/#496 policy comments before rerunning the readiness workflow.
• Leave the supabase-standby Environment and secrets in place unless the owner explicitly approves removal; issue #223 says Supabase standby credentials and sync resources must not be removed by cleanup work without a separate decision.
• If a standby credential is wrong or over-scoped, rotate it in Supabase, update the protected GitHub Environment secret, and rerun the readiness workflow.

Worker Compatibility API

Backend issue ramideltoro/nutsnews-backend#242 owns the backend worker database compatibility route required by ramideltoro/nutsnews-worker#27.

The public route shape is:

https://backend.nutsnews.com/api/worker/db/*

The route is disabled unless the protected backend apply receives:

NUTSNEWS_BACKEND_WORKER_API_ENABLED=true

Requests authenticate with:

Authorization: Bearer <NUTSNEWS_BACKEND_API_TOKEN>

Shadow mode must stay least-privilege and read-only: the backend API connects to nutsnews_primary_shadow as nutsnews_worker_api, accepts only bounded allow-listed worker operations, and rejects write operations before touching PostgreSQL. The role has BYPASSRLS so restored Supabase RLS does not hide shadow rows from the server-side compatibility API, but its grants are limited to the Worker read objects used by the route: articles, article_ai_reviews, article_summaries, feed_health, public_feed_snapshot, rss_feeds, and runtime_feature_flags. nutsnews_readonly remains the generic operator-inspection role, not the Worker API runtime role.

Backend-primary writes require all of these conditions:

• worker provider mode is backend_postgres_primary;
• backend protected apply has NUTSNEWS_BACKEND_WORKER_API_WRITES_ENABLED=true;
• the protected backend baseline has granted nutsnews_app the same bounded Worker read objects plus focused Worker write tables, sequence usage, and refresh_public_feed_snapshot() execution;
• parity, smoke, rollback, and cutover evidence has been linked from the backend migration runbooks and issues.

When NUTSNEWS_BACKEND_WORKER_API_WRITES_ENABLED=true, the Worker compatibility API intentionally connects as nutsnews_app instead of nutsnews_worker_api. That app role must bypass restored Supabase RLS and be able to run the complete Worker path: read feeds, dedupe/review state, existing summaries, feed health, and edge snapshot rows; insert or update accepted articles, reviews, summaries, health, AI usage, and worker run rows; update article publish status; and refresh public_feed_snapshot. A write-enabled smoke that only checks empty batch writes is insufficient; include at least one Worker read probe before declaring the backend-primary Worker path healthy.

Historical shadow-validation note: before backend-primary cutover, NUTSNEWS_BACKEND_WORKER_API_WRITES_ENABLED=false kept the Worker compatibility API read-only. Current production keeps backend PostgreSQL primary; any rollback from backend-primary mode or any Supabase standby write enablement still needs a separate protected approval.

App Compatibility Boundary

The web app now has issue ramideltoro/nutsnews#255 as the app-side provider-mode tracker. App runtime safety recognizes three modes:

Mode	Production write owner	Expected use
supabase_primary	Supabase	Default and explicit rollback mode.
backend_postgres_shadow	Supabase	App can prove backend API configuration while reads and writes still use Supabase.
backend_postgres_primary	Backend PostgreSQL compatibility API	Future cutover mode only, gated by explicit confirmation and backend app API parity.

backend_postgres_primary must require NUTSNEWS_BACKEND_POSTGRES_PRIMARY_CONFIRMATION=enable-backend-postgres-primary and must fail closed if app code attempts direct Supabase primary access. Non-production can exercise backend-primary runtime safety with a mock or non-production backend API endpoint and without writing to Supabase.

The app compatibility API is separate from the worker operation allowlist but is served by the same loopback backend database compatibility service. Backend issue ramideltoro/nutsnews-backend#247 commits the first app route:

https://backend.nutsnews.com/api/app/db/*

That route has app-specific allow-listed operations for public feed snapshots, article detail and sitemap reads, search, runtime feature flags, readiness schema-contract replacement, bounded admin dashboard read snapshots, quota usage writes, article engagement writes, and runtime feature flag writes. It is enabled by the protected backend apply only when the loopback compatibility service is enabled; backend-primary writes remain disabled unless the protected apply explicitly sets the write guardrail on. No browser bundle may receive backend API tokens or service-role credentials.

As of 2026-07-19, app-route provisioning and non-production smoke evidence is available:

Gate	Evidence	Result
Backend app route PR	ramideltoro/nutsnews-backend#248, merge commit 7cdbdd79815009a3e1cfee6ab75820c78df1e902	/api/app/db/* added beside /api/worker/db/*
Protected check	protected-backend-ansible-apply run 29693574619	passed in check mode
Protected apply	protected-backend-ansible-apply run 29693776534	passed in apply mode with deployment safety preflight and postcheck
Backend app-route smoke	python3 scripts/backend_app_db_api_smoke.py against https://backend.nutsnews.com/api/app/db	passed: smoke 200, snapshot rows 5, shadow write 409, primary guarded write 403
App helper shadow smoke	callBackendDatabaseOperation from ramideltoro/nutsnews against https://backend.nutsnews.com/api/app/db	passed: provider backend_postgres, writes disabled, snapshot rows 3, no Supabase writes
Backend contract evidence	ramideltoro/nutsnews-backend#249, merge commit e4704050e48704a9127c7d2c6366e05ba42d64b1	machine-readable contract now records provisioned app route and smoke evidence
Cutover dry-run refresh	backend-production-cutover run 29694281117	passed with status=dry_run_ready and mutation_performed=false; remaining blockers are writer pause, provider-switch owner approval, final go/no-go, and rollback owner coverage

Worker Writer-Pause Guard

The worker now has a deployable production writer-pause guard for the cutover window, but the guard is not enabled by default. The normal deployed value is:

NUTSNEWS_PRODUCTION_WRITES_PAUSED=false

When a separate cutover approval says to pause worker writes, set:

NUTSNEWS_PRODUCTION_WRITES_PAUSED=true

and redeploy through the worker pipeline. Manual refresh requests then return HTTP 423 before rate limits, Redis locks, database clients, feed fetching, AI review, or Supabase/backend writes run. Scheduled refreshes exit before run locks or database writes. Public snapshot and status endpoints stay available for read-side cutover validation.

As of 2026-07-19, the worker pause capability has implementation and deployment evidence:

Gate	Evidence	Result
Worker writer-pause PR	ramideltoro/nutsnews-worker#32, merge commit 5dfb14db5f4f119b1e3840e116306835f1a7fe83	added NUTSNEWS_PRODUCTION_WRITES_PAUSED guard for manual and scheduled refresh paths
Worker PR checks	PR #32 head 09fa930455fa9c963aaf3aa3fad2114643c1f5f6	passed worker pipeline, TypeScript, offline E2E, shadow target validation, workflow lint, secrets scan, dependency review, OSV, AI safety evals, and CodeQL
Worker main deploy	Worker Pipeline run 29694794130	passed CI and deployed worker shards/controller to Cloudflare with the pause flag defaulting to false
Worker smoke coverage	npm run test:db-provider-modes in ramideltoro/nutsnews-worker	passed backend-primary without Supabase bindings, explicit Supabase rollback, manual 423 pause response, and scheduled pause no-op coverage

flowchart TD
    A[Manual refresh request] --> B{NUTSNEWS_PRODUCTION_WRITES_PAUSED}
    C[Cron scheduled handler] --> B
    B -->|true| D[Skip before locks and database writes]
    B -->|false| E[Existing provider-mode refresh flow]
    E --> F[Supabase primary rollback]
    E --> G[Backend shadow or future primary]
    H[Public snapshot and status endpoints] --> I[Read-side validation remains available]

App Writer-Pause Guard

The app now has the same deployable production writer-pause control for app and admin write paths. The normal deployed value is:

NUTSNEWS_PRODUCTION_WRITES_PAUSED=false

When a separate cutover approval says to pause app writes, set:

NUTSNEWS_PRODUCTION_WRITES_PAUSED=true

in the app runtime environment and redeploy through the normal app release process. The pause is enforced in the central runtime safety layer, so app/admin data mutations, isolated quota and article-engagement writes, contact/external side effects, and telemetry delivery fail closed with production_writes_paused. Public reads, /readyz, and /api/runtime-config remain available. /readyz exposes both a JSON productionWritesPaused boolean and X-NutsNews-Production-Writes-Paused; runtime public config exposes only the same boolean and no backend tokens, service-role keys, project refs, or database URLs beyond the existing public Supabase URL allowlist.

As of 2026-07-19, the app pause capability has implementation, merge, and main-branch evidence:

Gate	Evidence	Result
App writer-pause PR	ramideltoro/nutsnews#261, head commit 48e8df7586cf80c167e849fab9144c8028a9e19a, merge commit 936062eee2ed097817a81f881920faa9808c2fac	adds NUTSNEWS_PRODUCTION_WRITES_PAUSED to app runtime safety, readiness, public config, and API contract allowlists
App PR checks	PR #261 check suite	passed Web CI, container image build/smoke, API compatibility, Vercel preview, public reader smoke, visual regression, accessibility, Lighthouse, CodeQL, Snyk, OSV, dependency review, and secret scan
App main checks	merge commit 936062eee2ed097817a81f881920faa9808c2fac	passed Web CI, container image build/smoke, immutable image publish, API compatibility, public reader smoke, visual regression, accessibility, Lighthouse, CodeQL, Snyk, OSV, Gitleaks, SEO, cache, homepage budget, staging-release regression, and staging-candidate request
Local app smoke	npm run test:runtime-safety, node scripts/api_contract_compatibility_regression.mjs, npm run test:routes, npm run test:components, npx tsc --noEmit, npm run lint, and CI-style fixture npm run build	passed; bare local build without runtime env failed closed with existing runtime_environment_invalid
Cutover dry-run refresh	backend-production-cutover run 29695707354	passed with status=dry_run_ready, mutation_performed=false, and blockers=[]; remaining external cutover items are coordinated maintenance window approval, actual app/worker writer-pause execution evidence, provider-switch owner approval, final go/no-go approval, and rollback owner coverage

flowchart TD
    A[App/admin write or external side effect] --> B[Runtime safety policy]
    B --> C{NUTSNEWS_PRODUCTION_WRITES_PAUSED}
    C -->|true| D[Fail closed: production_writes_paused]
    C -->|false| E[Existing production behavior]
    F[Public reads and readiness] --> G[Remain available]
    G --> H[/readyz and runtime config expose boolean only]
    E --> I[Supabase primary rollback remains default]

flowchart TD
    A[App runtime policy] --> B{NUTSNEWS_DATABASE_PROVIDER_MODE}
    B -->|supabase_primary| C[Supabase PostgREST remains primary]
    B -->|backend_postgres_shadow| D[Supabase primary plus backend API config smoke]
    B -->|backend_postgres_primary| E[Backend app DB compatibility API]
    E --> F[Backend PostgreSQL primary]
    C -. explicit rollback .-> A
    D -. rollback .-> C
    E -. rollback window .-> C

The current production cutover blockers are Supabase no-new-write watermark evidence after the live pause, provider switch owner approval, rollback owner coverage, final production approval, and protected DB gate refresh approval. Worker and app writer-pause execution evidence now exists, but this is not a database provider switch. Do not set the production app to backend_postgres_primary until the backend cutover issues link refreshed parity evidence, watermark evidence, rollback coverage, and the protected production cutover approval. Rollback remains explicit: set the app provider mode back to supabase_primary, remove or ignore backend API credentials, and keep Supabase as the production primary.

Historical 2026-07-19 Live Pause Execution Evidence

This section records the 2026-07-19 state before the later backend-primary policy. At that time, the production app and worker pause controls had been executed but the database provider had not been switched.

Gate	Evidence	Result
App production release	ramideltoro/nutsnews run 29704129436, source commit 936062eee2ed097817a81f881920faa9808c2fac	promoted with PRODUCTION_WRITES_PAUSED=true, staged smoke pass, production aliases verified
App live readiness	https://nutsnews.com/readyz checked on 2026-07-19	productionWritesPaused=true, X-NutsNews-Production-Writes-Paused: true, databaseProviderMode=supabase_primary
App runtime config	https://nutsnews.com/api/runtime-config checked on 2026-07-19	exposes the pause boolean and no backend API token
Worker production deploy	ramideltoro/nutsnews-worker run 29703213882, merge commit 352442e4be796114a64abb0cf135d387163bc072	deployed all 25 worker shards with NUTSNEWS_PRODUCTION_WRITES_PAUSED=true
Backend provider shadow dry-run	ramideltoro/nutsnews-backend run 29705168086	dry_run_ready, no mutation
Backend provider primary guard	ramideltoro/nutsnews-backend run 29705127819	failed closed with production_switch_requires_protected_cutover_workflow, no mutation
Backend rollback/final catch-up guard	ramideltoro/nutsnews-backend run 29705128499	failed closed with requires_live_writer_pause_evidence, no mutation

The protected DB evidence refreshes are waiting for the production-backend environment before any job steps execute:

• cutover dry-run: 29705124753;
• production replication health: 29705125374;
• production shadow parity: 29705126018;
• primary-shadow backup proof status: 29705126517.

Remaining before any protected production cutover mutation:

• coordinated maintenance window approval;
• Supabase no-new-write watermark evidence after the live pause timestamp;
• app and worker provider-switch owner approval;
• final go/no-go owner approval;
• rollback owner coverage through the rollback window;
• production-backend approval for refreshed DB gate evidence and the eventual protected cutover run.

flowchart TD
    A[Production app release] --> B[App writes paused]
    C[Worker pipeline] --> D[25 worker shards paused]
    B --> E[Provider still supabase_primary]
    D --> E
    E --> F[Supabase remains production writer]
    F --> G{Cutover approvals complete?}
    G -->|no| H[No provider switch or final sync]
    G -->|yes| I[Protected backend cutover workflow]
    H --> J[Collect no-new-write watermarks and owner approvals]

Access Boundary

PostgreSQL:

127.0.0.1:5432

Adminer:

127.0.0.1:8082

SSH tunnel:

ssh -i ~/.ssh/servercheap_65_75_201_18 \
  -L 8082:127.0.0.1:8082 \
  rami@65.75.201.18

Then open:

http://127.0.0.1:8082/

No public 5432, 8082, or PHP-FPM port is approved.

Protected Workflows

Provision:

.github/workflows/protected-backend-ansible-apply.yml

Restore drill:

.github/workflows/backend-postgres-failover-drill.yml

Primary-shadow migration gates:

.github/workflows/backend-postgres-primary-shadow-restore.yml
.github/workflows/backend-postgres-logical-replication.yml
.github/workflows/backend-postgres-replication-health.yml
.github/workflows/backend-postgres-parity-validation.yml
.github/workflows/backend-postgres-backup-restore-proof.yml

Restore drill modes:

Mode	Effect
status	Read current backend PostgreSQL readiness only.
dry-run	Inspect readiness and explain the fixed restore path without mutation.
restore-staging	Restore staging Supabase public schema/data into the backend rehearsal database.

restore-staging requires:

confirm_restore=restore-staging-to-backend-postgres

Each restore drops and recreates nutsnews_restore_rehearsal. After schema and data replay, the restore runner reapplies rehearsal database grants for nutsnews_readonly, nutsnews_migration_validation, and nutsnews_app_rehearsal so parity, smoke, and benchmark validation can connect with protected migration credentials. The validation role is allowed to bypass restored RLS only for aggregate-only migration checks; production app access remains blocked until a separate cutover approval.

Primary Shadow Evidence

As of 2026-07-18, the backend primary-shadow gates have protected workflow evidence for database-only readiness. Supabase remains the only production writer.

Gate	Evidence	Result
Shadow restore	backend-postgres-primary-shadow-restore run 29660688547	nutsnews_primary_shadow, snapshot logical-29660688547-4d7ad3dc2062-e933ae07dd27, RPO/RTO 3s
Logical replication	backend-postgres-logical-replication run 29660754171	publication table count 11, slot count 1, subscription count 1, copy_data=false
Replication health	backend-postgres-replication-health run 29660804755	healthy, blockers [], max lag 24s
Object and behavior parity	backend-postgres-parity-validation run 29660835941	object parity 18/18 pass, behavior parity 12/12 pass
Backup/restore proof	backend-postgres-backup-restore-proof run 29660905225	isolated restore target nutsnews_primary_shadow_backup_restore_proof, RPO/RTO 7s
Monitoring fail-closed simulation	backend-postgres-replication-health run 29660962794	expected failure with simulated replication blockers

The parity validator uses exact aggregate equality for stable objects. For explicitly marked append-style live tables, it records the source count at validator start and passes only after the target reaches that aggregate watermark without exceeding the current source count.

Secret Names

The protected production-backend Environment owns these names:

NUTSNEWS_BACKEND_POSTGRES_APP_PASSWORD
NUTSNEWS_BACKEND_POSTGRES_READONLY_PASSWORD
NUTSNEWS_BACKEND_POSTGRES_MIGRATION_RESTORE_PASSWORD
NUTSNEWS_BACKEND_POSTGRES_MIGRATION_VALIDATION_PASSWORD
NUTSNEWS_BACKEND_POSTGRES_MIGRATION_REPLICATION_PASSWORD
NUTSNEWS_BACKEND_POSTGRES_MIGRATION_APP_REHEARSAL_PASSWORD
NUTSNEWS_BACKEND_POSTGRES_WORKER_API_PASSWORD
NUTSNEWS_BACKEND_API_TOKEN
SUPABASE_ACCESS_TOKEN
NUTSNEWS_STAGING_SUPABASE_PROJECT_REF

Secret values must never appear in logs, screenshots, issues, PRs, docs, or workflow summaries.

Observability

PostgreSQL readiness is visible in:

• /var/lib/nutsnews/postgres/status.json;
• the loopback ops dashboard;
• Grafana metrics:
  • nutsnews_backend_postgres_failover_ready;
  • nutsnews_backend_postgres_restore_drill_healthy;
  • nutsnews_backend_postgres_replication_lag_configured;
• backend health report check postgres_restore_readiness.

For the production shadow, replication health is configured by backend-postgres-replication-health.yml and writes /var/lib/nutsnews/postgres/replication-health.json plus /var/lib/nutsnews/metrics/backend-postgres-replication-health.prom. The ops dashboard exposes:

• postgres.primary_shadow_database;
• postgres.replication.dashboard_status;
• postgres.replication.validation_status;
• postgres.replication.subscription_count;
• postgres.replication.slot_count;
• postgres.replication.max_lag_seconds.

RPO And RTO

Current tested RPO is the age of the latest approved logical dump. Current RTO target is 4 hours for controlled restore and app failover after drills pass.

Future RPO target is 15 minutes after PITR/WAL or reviewed continuous replication is implemented and tested.

Production Cutover Boundary

Production cutover is not enabled by issue #13. Before production traffic can write to backend PostgreSQL, NutsNews needs:

• approved production Supabase dump or reviewed replication catch-up;
• paused writers;
• PostgREST-compatible API layer or app-owned database API change for the web app, with issue ramideltoro/nutsnews#255 and the backend app API blocker closed or explicitly waived in the production cutover record;
• worker API shadow parity evidence for feeds, articles, summaries, reviews, run logging, quota logging, feed health, and public feed snapshot refresh;
• app/worker environment switch plan;
• smoke tests;
• rollback window;
• separate approval for production use.

Failback

Sync-back to Supabase is not supported yet. Bidirectional writes remain forbidden because conflict handling and split-brain prevention are not proven.

The safest failback path is forward recovery: pause writers, compare evidence, choose one authoritative database, then restore or migrate once through a reviewed production procedure.

Rollback

Before production cutover, rollback is simple: keep Supabase as writer and disable NUTSNEWS_BACKEND_POSTGRES_ENABLED before a protected apply if the local database should be removed from active management.

After a future production cutover, rollback must follow the approved cutover runbook and only happen inside the documented rollback window unless reverse replication has been separately proven.