Skip to content

Change Brief: Switch to Local Postgres

Identity

  • Change ID: 2026-07-20-switch-to-local-postgres
  • Source Linear change issue: ANU-5
  • Priority: High
  • Owner: Product Main coordination; CMS implementation
  • Status: Planning in progress

User outcome

Anuva development runs the complete CMS application and its database locally through Docker Desktop. A developer can start the CMS web application, PostgreSQL with pgvector, and pgAdmin from one Docker Compose definition, while retaining the application data that currently lives in Supabase Postgres.

Current behavior

  • anuvax-cms runs directly on the host with pnpm dev and is not containerized.
  • Payload, Mastra storage, and the RAG vector store share DATABASE_URI.
  • The active development environment points DATABASE_URI at hosted Supabase Postgres, although .env.example already illustrates a localhost URI.
  • Payload uses @payloadcms/db-postgres; Mastra uses PostgresStore; RAG uses PgVector. No Supabase Auth, Storage, Realtime, Edge Functions, or client SDK dependency has been identified.
  • Supabase-pooler detection currently disables PAYLOAD_DB_PUSH; local direct Postgres will not require that pooler-specific guard.

Target behavior

The CMS repository owns a development-focused Docker Compose stack with three services:

  1. postgres: PostgreSQL 17 with pgvector, a persistent named data volume, a health check, and host access on 127.0.0.1:5432.
  2. pgadmin: pgAdmin with persistent configuration, a preconfigured connection to the postgres service, and host access on 127.0.0.1:5050.
  3. web: the Node 22 CMS development runtime, built from the repository, with source hot reload and host access on 127.0.0.1:3000.

Inside Compose, the web application uses postgres:5432; host-run diagnostics and database tools use 127.0.0.1:5432. External AI, email, billing, S3, and render-worker services remain configurable dependencies rather than being added to this Compose stack.

As of planning on 2026-07-21, the recommended baselines are PostgreSQL 17.10, pgvector 0.8.2, and pgAdmin 9.16. Implementation must use explicit image tags, record resolved image digests, and verify the running versions. PostgreSQL 17.10 is preferred over the hosted Supabase 17.6-derived build because it is the current PostgreSQL 17 maintenance release; this remains a same-major logical dump/restore migration.

Known constraints

  • Docker Desktop is the local container runtime.
  • The complete CMS web application, database, and pgAdmin must run in Docker.
  • PostgreSQL remains on major version 17 for the migration.
  • The existing Supabase database is the source of application schema and data.
  • Supabase CLI must perform the remote logical export.
  • Database dumps, credentials, personal data, and pgAdmin state must never be committed or included in documentation or command receipts.
  • The development stack must work on Windows-hosted Docker Desktop and preserve the ports expected by the Anuva development configuration.

Non-goals

  • Production hosting or deployment architecture.
  • Self-hosting the complete Supabase platform.
  • Migrating Supabase Auth, Storage, Realtime, Edge Functions, or managed roles.
  • Containerizing the Python server, Unity project, external AI providers, email, billing, or S3-compatible storage.
  • Upgrading Payload, Mastra, Next.js, Node, or PostgreSQL to a new major version.
  • Deleting, modifying, or disabling the Supabase source database.
  • Automatically deleting Docker volumes or local backup artifacts.

Product acceptance criteria

  • [ ] docker compose config validates with no secrets embedded in tracked files.
  • [ ] One documented Compose command builds and starts web, postgres, and pgadmin, and all three report healthy or ready.
  • [ ] The CMS is reachable on http://127.0.0.1:3000, PostgreSQL on 127.0.0.1:5432, and pgAdmin on http://127.0.0.1:5050.
  • [ ] Runtime evidence records PostgreSQL 17's selected maintenance version, pgvector 0.8.2, pgAdmin 9.16, and the resolved container image digests.
  • [ ] vector is installed and the existing anuvax_rag_chunks_v1 vector data can be queried through the application path.
  • [ ] Supabase application schema and data are exported with the Supabase CLI to ignored local artifacts, checksummed, and restored without importing Supabase-managed services or secrets.
  • [ ] Table counts, key application records, foreign keys, sequences, migration state, Payload access, Mastra storage, and RAG vector behavior pass the documented parity checks.
  • [ ] The containerized CMS can execute the relevant workers and the normal lint, typecheck, integration, and selected end-to-end verification.
  • [ ] Restarting the stack preserves Postgres and pgAdmin state; recovery and explicit reset commands are documented and guarded.
  • [ ] Switching back to the untouched Supabase connection remains a documented rollback until local acceptance is complete.

Canonical contracts

Runtime and network

Consumer Database endpoint Purpose
Compose web service postgres:5432 Payload, Mastra, workers, and RAG
Host Anuva CLI and approved host tools 127.0.0.1:5432 Health checks and controlled diagnostics
Compose pgadmin service postgres:5432 Local database administration

The Compose service names and container ports are stable. Host ports may be made configurable, but their checked-in defaults remain 3000, 5432, and 5050 to match the existing development harness.

Configuration and secrets

  • Tracked example files document required variable names and safe placeholders.
  • An ignored local environment file supplies database and pgAdmin credentials.
  • The web container receives DATABASE_URI with the Compose service hostname; passwords are not encoded in the Compose file, image, or documentation.
  • External services reached through a host-local endpoint use an explicit Docker Desktop host address rather than assuming container localhost.

Persistence and destructive operations

  • Postgres and pgAdmin use named volumes.
  • Normal down and restart operations preserve volumes.
  • Any down --volumes, volume removal, database reset, or restore-over-existing operation requires an explicit user approval after the exact target is shown.
  • Export and restore tooling refuses an ambiguous source or target and never mutates the Supabase source.

Compatibility and migration

  1. Inventory the source PostgreSQL version, extensions, schemas, roles needed by application objects, Payload migration state, table counts, and vector index.
  2. Use the Supabase CLI to produce separate schema and data dumps. The default schema export and --data-only --use-copy export exclude Supabase-managed schemas; a role dump is created only if inventory proves an application-owned role is required.
  3. Store dumps outside tracked source, calculate checksums, and record only redacted metadata and evidence.
  4. Initialize the local cluster, enable pgvector, and restore schema before data. Resolve Supabase-specific ownership, extension schema, or grants explicitly; do not weaken permissions globally to make a restore pass.
  5. Restore and advance sequences, run ANALYZE, verify constraints and migration state, and run parity checks before changing the active CMS environment.
  6. Point the containerized CMS at local Postgres and execute application-level verification. Keep the source Supabase database unchanged throughout.

Failure, cancellation, and recovery behavior

  • A dump failure leaves the source untouched and produces no cutover.
  • A restore failure leaves the source untouched; capture the failing object and restore log before considering a clean local retry.
  • A clean retry may remove only the verified ANU-5 local database volume and only after explicit approval. It must not use broad Docker cleanup commands.
  • If parity or application checks fail, revert DATABASE_URI to Supabase and retain the local dump, checksum, and failure evidence for diagnosis.
  • A partial migration is never treated as accepted merely because the CMS boots.

Security and privacy

  • Treat exports as sensitive production-like data even if the source is a development project.
  • Ignore dump directories, restore logs containing row data, and local credential files in Git; verify with git status before every commit.
  • Bind database and pgAdmin ports to loopback by default.
  • Use strong machine-local credentials and do not reuse Supabase credentials.
  • Redact connection strings, access tokens, emails, and row contents from docs, Linear, PRs, logs, screenshots, and completion evidence.

Documentation impact

anuvax-cms must update its bootstrap/runtime documentation, environment and service map, scripts and commands, system architecture, testing guidance, and change-specific implementation plan/log/report. Product Main will keep this change folder and final cross-repository completion evidence synchronized.

Version references

Approval record

  • Plan reviewed by: Girish
  • Approved at: 2026-07-21 05:35 UTC
  • Approval notes: Approved as written, including PostgreSQL 17.10, the two-issue CMS split, and keeping S3, Python, Unity, and external providers outside the Compose stack. Implementation issue creation remains gated on the merged planning PR and reviewed CLI dry run.