Neon Integration Playbook

This page is the operational playbook for Neon — the managed Postgres provider that hosts the production database. The data ownership story (which service owns which schema) lives in the Data Model spine; this page covers connection mechanics, branching, migrations, failure modes, and operational hooks that are Neon-specific.

1. Overview

Neon is a serverless Postgres with copy-on-write branching. We use it for three things:

1. Production hosting of the single Postgres instance shared by sirloin (BUN ORM, owns users, characters, media, billing, bi, audits, autoscaling, mcp schemas) and brain (Prisma, owns fennec schema). Schema ownership and the cross-service contract are documented in Data Models — Database Architecture.
2. Per-PR preview environments via copy-on-write branches, wired up by .github/workflows/neon-branching.yml and pushed into Railway preview services as env vars.
3. Local opt-in debugging when developers need prod-like Postgres behavior instead of the default compose rump database.

Default dev uses local Postgres (rump:5432 in docker-compose). Local Neon usage is opt-in by setting the same direct/pooled env vars used in preview and production. Production Neon already lives in the US, and Railway service manifests are pinned to us-east4-eqdc4a to keep the app runtime close to that database path.

Railway private networking does not extend to Neon. Apps connect through Neon direct/pooled connection strings and Neon proxy endpoints, not a *.railway.internal hostname, so there is no Railway-only internal Neon path that avoids Neon network accounting.

2. Architecture

flowchart LR
  subgraph Apps["Production services"]
    sirloin["sirloin (Go, BUN)"]
    brain["brain (NestJS, Prisma)"]
  end

  subgraph Neon["Neon project"]
    pooler["Pooled endpoint<br/>(PgBouncer, transaction mode)"]
    direct["Direct endpoint<br/>(session mode)"]
    primary["Primary branch<br/>(main / production)"]
    pr1["preview/pr-1234-feat-x"]
    pr2["preview/pr-5678-fix-y"]
  end

  sirloin -- "SIRLOIN_DATABASE_POOLED_URL" --> pooler
  sirloin -- "SIRLOIN_DATABASE_URL (migrations)" --> direct
  brain   -- "DATABASE_URL (pooled)" --> pooler
  brain   -- "DIRECT_DATABASE_URL (migrations)" --> direct
  pooler --> primary
  direct --> primary
  primary -. "copy-on-write per PR" .-> pr1
  primary -. "copy-on-write per PR" .-> pr2

Both apps point at the same Postgres branch. sirloin uses the rump database, while brain uses the fennec database with its Prisma client scoped to the fennec schema via ?schema=fennec. The branching workflow rewrites every preview URL to keep this layout (see §4).

3. Per-service connections

Service	Client	Pooled URL?	Env var(s)	Code path
sirloin	BUN over pgx	Yes — runtime uses pooled when set	SIRLOIN_DATABASE_URL (direct), SIRLOIN_DATABASE_POOLED_URL (pooled)	apps/sirloin/internal/pkg/env/variables.go, apps/sirloin/internal/app/config/config.go:564 (runtimeDatabaseURL), apps/sirloin/cmd/app/main.go:253 (storage.NewPostgresWithReadReplica(cfg.DatabaseRuntimeURL, cfg.DatabaseReplicaURL, …))
brain	Prisma Client (Node) over the pg driver adapter	Yes — DATABASE_URL is pooled	DATABASE_URL (pooled, runtime), DIRECT_DATABASE_URL (migrations / introspection)	Runtime: apps/brain/src/modules/application/prisma/services/prisma.service.ts:25 (createPrismaAdapter, PrismaPg on DATABASE_URL). CLI: apps/brain/prisma.config.ts:15 (datasource.url = DIRECT_DATABASE_URL ?? DATABASE_URL)
brisket / strip / round / fennec / chuck / shank / flank	n/a	n/a	n/a	No direct DB access — they speak gRPC/REST to sirloin and brain. chuck (Strapi) has its own Postgres unrelated to Neon (apps/chuck/meat/.env.example).

sirloin’s split is controlled by runtimeDatabaseURL in apps/sirloin/internal/app/config/config.go: when SIRLOIN_DATABASE_POOLED_URL is set, app traffic uses the pooled endpoint while migrations and tools continue to use the unpooled SIRLOIN_DATABASE_URL. The validator enforces a warning when the runtime URL drops sslmode below the direct URL (apps/sirloin/internal/app/config/validator.go:198, insecureDatabaseWarnings).

Read replicas

Both services can send selected reads to a Neon read replica. Routing is opt-in per endpoint: every query stays on the primary unless its endpoint is on the allowlist below, and nothing uses the replica while the replica URL is unset. Point the URL at the replica compute’s pooled endpoint.

Service	Env var	Endpoints that read the replica	Mechanism
sirloin	SIRLOIN_DATABASE_REPLICA_URL	SirloinService/ListAllMedia, ListCollections	ReplicaReadsInterceptor (apps/sirloin/internal/app/services/replicareads.go) marks the request context with storage.WithReplica; plain bun SELECTs built on the *bun.DB then go to the replica
brain	DATABASE_REPLICA_URL	GET /generation/success-stats	ReplicaReadsInterceptor runs the handler inside readFromReplica; Prisma model reads (find*, count, aggregate, groupBy) then go to the replica

Transactions, raw SQL and writes always stay on the primary, including inside an allowlisted endpoint. The replica replicates asynchronously, so a read may miss a row written moments earlier. Add an endpoint only when it writes nothing, decides nothing from what it reads, and fills no cache that another path trusts. ListMediaExamples and GET /api/media/examples are excluded: each refills a catalog cache keyed by a version read from the primary, so a stale refill would be served until the cache expires. GET /api/character/vi/catalogue is excluded because Sirloin’s shop sync writes from its response. BillingService/GetCurrentUsage is excluded because it returns billing, dunning and KYC status alongside its usage counts, and ListCharacters because its list query filters NSFW characters by KYC and Ondato verification state.

A client that refetches a list right after its own mutation can see the pre-mutation rows for the length of the replica lag.

Sirloin routes in apps/sirloin/internal/pkg/storage/readreplica.go. A configured replica that cannot be reached fails startup, and one that fails after startup fails the allowlisted reads; there is no fallback to the primary, so unset the URL to recover. ReplicaReadsInterceptor runs last in the gRPC chain, so the auth, rate-limit and fraud interceptors read the primary. Brain routes in withReadReplica (apps/brain/src/modules/application/prisma/services/prisma.service.ts); a replica URL without ?schema= inherits the primary’s schema, and the replica pool is sized from the replica URL’s connection_limit, not BRAIN_DB_POOL_*. Sirloin’s test databases attach a read-only connection as the replica, so a write or row lock routed there fails the test. Preview environments point both replica URLs at their own Neon branch (.github/workflows/neon-branching.yml). To roll back, unset the replica URL and redeploy.

Confirmed: a repo-wide grep (apps/brain/src/**) finds no LISTEN, NOTIFY, or session-level advisory-lock usage. DIRECT_DATABASE_URL is referenced only in apps/brain/prisma.config.ts:15 (the Prisma CLI datasource URL, falling back to DATABASE_URL) and apps/brain/create_migration.sh, which means migrations only. Prisma 7 moved the datasource URL out of schema.prisma; the runtime client never reads prisma.config.ts and instead opens its own node-pg pool through PrismaPg in apps/brain/src/modules/application/prisma/services/prisma.service.ts:25.

Local Neon opt-in

The checked-in .env.example keeps local compose on rump. To point local services at Neon, override only the database variables you need. Brain consumes DATABASE_URL and DIRECT_DATABASE_URL; when the values live in the root compose .env, use the BRAIN_* inputs that docker-compose.yml maps into those canonical Brain variables.

The helper path is:

neonctl auth

# First run only, if your local branch does not exist yet.
# Creates local/<git user.email> from staging by default:
NEON_PROJECT_ID='<project-id>' NEON_CREATE_BRANCH=1 make neon-local-env

# Later runs reuse the same branch:
NEON_PROJECT_ID='<project-id>' make neon-local-env

make dev-up-neon-d

make neon-local-env uses the authenticated neonctl CLI session to fetch direct and pooled URLs for the configured project/branch, then writes an ignored .env.neon.local file with mode 0600. If NEON_BRANCH is omitted, the helper derives a stable branch name from git config user.email, such as local/dev-example.com; override it with NEON_BRANCH='<branch-name-or-id>' when needed. It rewrites the same Neon branch into Beef’s two local databases: rump for sirloin, and fennec?schema=fennec for brain.

neonctl refuses to build a connection string when a branch holds more than one database, and a branch forked from staging holds three (neondb, fennec, rump). The helper therefore picks the source database itself, preferring rump and then fennec. The pick only decides which database the fetched URL names, since both service URLs are rewritten afterwards and the branch role owns all three. Override it with ARGS="--database-name <db>" when needed. Roles are not auto-detected: if a branch has more than one, pass ARGS="--role-name <role>". New branches fork from staging by default; pass NEON_PARENT_BRANCH or ARGS="--parent-branch <branch>" to override. Pass NEON_BRANCH_EXPIRES_AT to set an expiry. The helper does not accept or forward Neon API keys; run neonctl auth locally instead. By default it leaves SIRLOIN_DATABASE_POOLED_URL empty because sirloin migrations, startup session setup, and advisory-lock flows need the direct URL. Pass ARGS=--include-sirloin-pooler only when you are explicitly testing sirloin’s pooled runtime path.

Manual URL fallback:

NEON_DIRECT_URL='postgresql://<role>:<password>@<direct-host>/<db>?sslmode=require' \
NEON_POOLED_URL='postgresql://<role>:<password>@<pooled-host>/<db>?sslmode=require' \
make neon-local-env

# sirloin: direct URL is required for startup migrations and advisory locks.
SIRLOIN_DATABASE_URL=postgresql://<role>:<password>@<direct-host>/rump?sslmode=require
# Optional. Set only when you are explicitly testing pooled runtime queries.
SIRLOIN_DATABASE_POOLED_URL=postgresql://<role>:<password>@<pooled-host>/rump?sslmode=require

# brain: runtime may use the pooler, migrations must use the direct URL.
# Host-run Brain / Railway / apps/brain env:
DATABASE_URL=postgresql://<role>:<password>@<pooled-host>/fennec?sslmode=require&schema=fennec
DIRECT_DATABASE_URL=postgresql://<role>:<password>@<direct-host>/fennec?sslmode=require&schema=fennec

# Root compose .env equivalent:
BRAIN_DATABASE_URL=postgresql://<role>:<password>@<pooled-host>/fennec?sslmode=require&schema=fennec
BRAIN_DIRECT_DATABASE_URL=postgresql://<role>:<password>@<direct-host>/fennec?sslmode=require&schema=fennec

docker-compose.yml passes pre-set values through to sirloin and brain. make dev-up-neon-d starts compose without the local rump service, with .env.neon.local layered over the normal .env file, so other local secrets can stay where they already live. Override NEON_COMPOSE_SERVICES if you only need a smaller subset such as brain sirloin redis round. apps/brain/create_migration.sh rewrites only the compose host rump to localhost:8800; Neon hosts are left untouched and local rump is not started for that path.

4. Branching

.github/workflows/neon-branching.yml runs on pull_request opened|reopened|synchronize|closed, for every base branch.

• Setup job sanitizes the PR branch name (slashes → dashes).
• Create job (create_neon_branch_and_update_railway):
  1. Validates secrets.NEON_API_KEY, vars.NEON_PROJECT_ID, secrets.RAILWAY_API_TOKEN, vars.RAILWAY_PROJECT_ID.
  2. Calls neondatabase/create-branch-action@v6 with branch_name: preview/pr-${PR_NUMBER}-${SANITIZED_BRANCH} and a 14-day TTL (expires_at derived from date -u --date '+14 days').
  3. Reads db_url (direct) and db_url_pooled from the action outputs and rewrites them with Python (urllib.parse) into three flavours:
     • /tmp/rump_db_url — direct, points at the rump database.
     • /tmp/rump_db_pooled_url — pooled, points at rump.
     • /tmp/fennec_db_url — direct + ?schema=fennec for brain.
  4. Installs the Railway CLI, waits for the matching preview environment beef-pr-${PR_NUMBER} to exist, then sets:
     • beef-sirloin → SIRLOIN_DATABASE_URL, SIRLOIN_DATABASE_POOLED_URL, SIRLOIN_DATABASE_REPLICA_URL (the branch’s own pooled URL)
     • beef-brain → DATABASE_URL, DIRECT_DATABASE_URL, DATABASE_REPLICA_URL (the branch’s own fennec URL)
  5. Deletes BRAIN_OTEL_URL and SIRLOIN_AXIOM_TOKEN in the preview env so PR traffic never lands in the staging Axiom dataset.
  6. Verifies every var was actually written and does not still point at a *.railway.internal host — fails the job otherwise.
  7. Publishes the smoke configuration and keeps one preview-ready PR comment. The comment carries a hidden <!-- beef-preview-ready --> marker; later pushes update that comment instead of posting another.
• Downstream smoke (.github/workflows/playwright-smoke.yml): when the create job fails, is cancelled, or times out on a PR head, Wait for Preview fails and Run Smoke Tests is skipped rather than failing too. After the create job is fixed or re-run to green, re-run the smoke run’s failed jobs, which also re-runs the skipped smoke job.
• Cleanup job (delete_neon_branch, if: github.event.action == 'closed'): invokes neondatabase/delete-branch-action@v3 to drop the branch when the PR is merged or closed. The 14-day TTL is the safety net for PRs that bypass close events.

Branch naming: preview/pr-<NUMBER>-<SANITIZED_HEAD_BRANCH> — keep this prefix; the cleanup action and the schema-diff action both depend on it.

5. Connection pooling

Neon ships its own PgBouncer-based pooler. We use it from both services in transaction pooling mode (the default for Neon’s -pooler host).

Quirks to remember:

• No session-scoped state — SET, LISTEN/NOTIFY, prepared statements outside the transaction, and session-level advisory locks (pg_advisory_lock) do not survive across queries. If you need any of these, point the call at the direct URL (SIRLOIN_DATABASE_URL / DIRECT_DATABASE_URL).
• Prisma: pgbouncer=true is implied by Neon’s pooler hostname. Migrations and prisma db pull must use the direct URL, which is exactly how apps/brain/prisma.config.ts is wired: the CLI datasource is DIRECT_DATABASE_URL ?? DATABASE_URL, while the runtime PrismaPg adapter in prisma.service.ts pools over DATABASE_URL.
• BUN / pgx: server-side prepared statements are off by default with the Neon pooler. apps/sirloin/internal/pkg/storage/postgres_config.go configures the pgx pool; review it before turning on any prepared-statement caching.
• Migration runner: sirloin’s Go runner takes its own advisory lock (postgresLockPoolConfig in apps/sirloin/internal/pkg/storage/) — point it at the direct URL, never the pooled URL.

6. Migrations

Service	Tool	Source of truth	CI invocation
brain	Prisma migrate	apps/brain/prisma/migrations/ (timestamped, e.g. 20241231153339_init_fennec)	pnpm exec prisma migrate deploy from apps/brain/startup.sh:9 (runs at container start). New migrations are authored locally via apps/brain/create_migration.sh (prisma migrate dev) against canonical DIRECT_DATABASE_URL, using root BRAIN_DIRECT_DATABASE_URL only as the compose input.
sirloin	Custom Go runner	apps/sirloin/internal/app/migrate/migrate.go + SQL in apps/sirloin/internal/app/migrate/schema/*.sql	make verify-migrations (numbering check) gates CI; runner executes against SIRLOIN_DATABASE_URL (direct).

For preview branches, the workflow does not run migrations itself — the deployed Railway service runs them on container start (startup.sh for brain). Because the branch is a copy-on-write of the production primary, all existing migrations are already applied; only the new ones in the PR run.

Confirmed: sirloin runs migrate.Migrate(cfg.DatabaseURL) at process boot in apps/sirloin/cmd/app/main.go:154. The Go binary executes migrations against the direct URL (SIRLOIN_DATABASE_URL) on every container start, identical to brain’s prisma migrate deploy flow — no manual deploy step.

7. Failure modes

Mode	Detection	Mitigation
Cold-start latency on suspended branches — Neon suspends idle compute. First query after suspension can take 0.5–3s.	First-request P99 spike on a freshly-deployed preview; connection refused/timeouts immediately after deploy.	Send a warmup query in app boot. For previews, accept the cost — these are short-lived.
Connection-pool exhaustion at the pooler	FATAL: sorry, too many clients already or pgx acquire timeouts	Reduce per-pod pool size (we share one pooler across services); shed long-running transactions; check for leaked txns in BUN/Prisma.
Migration deadlock during prisma migrate deploy against pooled URL	Migration hangs; advisory-lock timeout	Always run migrations against DIRECT_DATABASE_URL / SIRLOIN_DATABASE_URL. Already enforced in code paths above — regressions usually mean someone set DIRECT_DATABASE_URL to the pooled endpoint (or left it unset so prisma.config.ts fell back to a pooled DATABASE_URL).
Branch quota hit — Neon project hits max-branch limit	create-branch-action fails the workflow	The 14-day TTL plus the on-close delete keep us under quota. Manually purge stale preview/pr-* branches via neon branches list if hit. TODO(@law): exact branch quota on the current plan.
Pooled URL leaks into migrations env	prepared statement "sN" already exists errors during prisma migrate deploy	Keep canonical DIRECT_DATABASE_URL on the direct Neon endpoint. In root compose .env, set BRAIN_DIRECT_DATABASE_URL so compose passes that direct URL through to Brain.
sslmode=disable in production URL	sirloin validator emits warning at boot (validator.go:198)	Production Neon URLs must include sslmode=require. The dev .env uses sslmode=disable against the local rump container — that is expected and limited to dev.

8. Secrets

Per-environment DATABASE_URL shapes:

Env	Where stored	Format
dev	.env.example (root)	Defaults point at compose rump: SIRLOIN_DATABASE_URL=postgresql://sirloin:sirloin@rump:5432/rump?sslmode=disable, root BRAIN_DATABASE_URL=postgresql://sirloin:sirloin@rump:5432/fennec?sslmode=disable&schema=fennec maps to Brain’s DATABASE_URL, with optional direct/pooled overrides left empty.
preview	Railway env vars on beef-pr-<N> services	Written by neon-branching.yml from the create-branch action outputs. Never logged.
staging / production	Railway env vars on beef-* services in their respective environments	TODO(@law): canonical hostname pattern (e.g. ep-<id>-pooler.<region>.neon.tech) and whether we use a single Neon project across staging+prod or one per environment.

Rotation path: rotate the Neon role password in the Neon console → update the four Railway vars (SIRLOIN_DATABASE_URL, SIRLOIN_DATABASE_POOLED_URL, DATABASE_URL, DIRECT_DATABASE_URL) on each environment, plus SIRLOIN_DATABASE_REPLICA_URL and DATABASE_REPLICA_URL where a read replica is set (a stale replica password fails the allowlisted reads) → redeploy. The preview workflow re-derives every URL on each PR sync, so it self-heals after a rotation.

Never log DATABASE_URL. The branching workflow is explicit about this (comment near db_url_with_pooler in the Neon action docs).

9. Backups & PITR

• TODO(@law): production backup schedule and RPO / RTO targets.
• TODO(@law): Neon point-in-time recovery window on the current plan (Neon offers PITR up to N days depending on tier).
• TODO(@law): retention policy for StripAuditLog, FraudEvent, Generation rows — see Data Models.

PII surface that lives on Neon: brain.fennec.User.email/firstName/lastName, sirloin.users.*, sirloin.subs_all.email, plus anything keyed off Chargebee customer IDs. Treat any Neon export as a PII export.

10. Cost model

Neon bills on three axes — TODO(@law) all numbers against the current plan:

• Compute hours per branch (autosuspend keeps idle cost near zero; preview branches dominate this if PRs sit open).
• Storage for the primary (copy-on-write means branches add only their diff).
• Data transfer out of the region.

Cost levers we already pull: 14-day branch TTL, on-close cleanup, autosuspend on the production branch (Neon default).

11. Dashboards

• Neon console — TODO(@law): paste link to the Beef project dashboard.
• Branch list / quota — TODO(@law): link.
• Slow query insights — TODO(@law): whether Neon’s built-in insights are enabled or whether we rely on Axiom traces (SIRLOIN_AXIOM_TOKEN).

12. Runbook hooks

• Operations — Railway — covers the Railway side of the preview-environment wiring.
• TODO(@law): once the Neon-specific runbook lands under docs/src/content/docs/operations/runbooks/, link it here (suggested: neon-branch-purge.md, neon-pool-exhaustion.md).
• For schema-ownership questions, use Data Model as the spine.