# Server, database roles, and operation

> The account and relay service, the PostgreSQL role invariant, the configuration a development server needs, and what to verify beyond HTTP health.

Handbook / Contribute · Checked against the source on 2026-10-05 · https://www.hbcode.ai/docs/server-operations

## The server has account and relay responsibilities

The Actix application initializes shared services at startup: database pools, account authentication, connection management, and configured provider and push integrations. The DeviceConnectionManager is shared across HTTP workers in the process. Its live sockets, connection generations, and pending RPC records are process state, and the relay session store behind resume tokens is in memory too, with a 24-hour TTL and a 5-minute sweep. Every RPC and every heartbeat frame renews a session record. Redis is required for shared sign-in state, rate limiting, and credit reservations for paid requests, and the process exits at startup without it. It does not make a live socket registry portable between server processes.

Both devices and their pending request must reach that connection manager, so production runs one relay-serving process per region with explicit connection draining. The device routing chapter explains why a round-robin replica would break routing.

## PostgreSQL roles are a startup invariant

**The server exits at startup unless its two database logins are separate and confined by row-level security**

Inside PostgreSQL the migration role owns the tables, and 27 of them must have row-level security enabled and forced. The server connects through two logins, the system login hbcode and the tenant login authenticated. At startup it checks that neither login is a superuser, bypasses row-level security, inherits roles, or owns tables, that neither is the other or a member of it or of the table owner, and that the tenant login reads no rows from 16 tenant tables without a user ID. Any failure stops the process before it listens. Each server process opens its own system pool of 8 and tenant pool of 16 connections, shared by its HTTP workers.

- **Runtime logins** (DATABASE_SYSTEM_URL · DATABASE_USER_URL): assert_runtime_boundary checks the principals these URLs connect as, not the URLs. Before connecting, create_dual_pools also rejects two identical URLs.
- **Tenant context** (app.current_user_id): Set with set_config for one transaction, so a pooled connection forgets it when the transaction ends. Row-level security policies written for each login decide what it may touch.
- **Pools** (DB_POOL_SYSTEM_MAX · DB_POOL_USER_MAX): Opened once per server process and shared by all its HTTP workers. Each extra process, including the second color during a blue-green cutover, opens its own.

create_dual_pools opens both pools and runs assert_runtime_boundary on the principals they actually connected as, so a URL that names the wrong role fails the same checks.

| Default | System pool | Tenant pool |
| --- | --- | --- |
| Maximum connections | 8 | 16 |
| Minimum connections | 2 | 4 |
| application_name | hbcode-system | hbcode-user |
| Statement timeout | 30 seconds | 8 seconds |
| Idle-in-transaction timeout | 60 seconds | 10 seconds |
| Lock timeout | 5 seconds | 2 seconds |

Both pools use a 2-second acquire timeout, a 60-second idle timeout, and a maximum connection lifetime of 1,800 seconds. Every connection sets the time zone to UTC. A failed connect is tried up to five times, and the wait grows by one second with each attempt. Each server process opens its own pools, shared by its HTTP workers. More processes, including the second color of a blue-green cutover, multiply the connections and need a database capacity check first.

## Configure a local server

1. Copy server/.env.example to server/.env and configure a local database, Redis URL, JWT and refresh-token encryption settings, and Auth0 values.
2. Create separate migration and runtime principals against a local development database.
3. Apply server/scripts/run-migrations.sh with the required migration environment. server/README.md describes role provisioning and the preservation policy.

Read and run from the repository root:

```
cp server/.env.example server/.env
# Configure the local services and database roles first.
pnpm server:check
pnpm dev:server
```

The migration runner consumes infrastructure/ansible/playbooks/plantocode/migration-execution-plan.yml, the same ordered plan deployment uses. The migration role must be a superuser that is none of postgres, hbcode, or authenticated and is absent from DATABASE_RUNTIME_ROLES. Files marked execution once are sha256-locked in plantocode_internal.migration_execution_ledger, and the runner stops when a runtime role can reach that ledger. A checksum mismatch, a once file without a single transaction, or an unplanned .sql file stops the run. PRESERVE_EXISTING_DATABASE_IDENTITIES=true is accepted only for the expand phase. Do not replace the runner with sqlx migrate run.

| Purpose | Settings and when they are required |
| --- | --- |
| Core services | DATABASE_SYSTEM_URL, DATABASE_USER_URL, REDIS_URL. Core database and shared sign-in services. |
| Account login | AUTH0_DOMAIN, AUTH0_API_AUDIENCE, AUTH0_ALLOWED_CLIENT_IDS. The configured account login trust boundary. |
| Tokens | JWT_SECRET, REFRESH_TOKEN_ENCRYPTION_KEY. Issuing app credentials and storing refresh material. |
| Providers | Provider API keys. Only the corresponding configured provider routes. |
| Integrations | Stripe, APNs, FCM, mail and other integration settings. Their respective account or notification features. |
| Extra databases | METRICS_DATABASE_URL, ACCOUNT_CONTROL_DATABASE_URL. Optional in development. Production refuses to start without both, and each must target its own PostgreSQL database. |
| Service health | SERVICE_HEALTH_REGION, SERVICE_HEALTH_RELEASE. Required when METRICS_DATABASE_URL is set. The region is eu or us. The release is the SHA-256 of the deployed binary as 64 hex characters. |
| Previews | WORKSPACE_PREVIEW_HMAC_SECRET. Optional in development, required in production. At least 32 characters. |
| Deployment | PLANTOCODE_DEPLOYMENT_TOKEN. The protected /health/deployment endpoint and its action /health/deployment/drain-device-links. Without the token both answer 503. A wrong token gets 401. |

## Verify behavior beyond HTTP health

GET /health checks basic server health. Protected /health/deployment supports deployment inspection. Then verify an account login, two device registrations, one read-only desktop RPC, and one controlled outbox operation. A healthy HTTP listener alone does not test database role separation, a native callback, provider configuration, or relay routing.

A useful relay trace follows the request ID, source client, target desktop, method, and connection generation. The forwarding code extracts W3C trace context and injects the child context into the next hop. For ambiguous delivery, inspect the desktop’s remote_rpc_idempotency claim and its chat_operation_ledger rows before inferring whether work ran.

**A release finishes on the US canary before it starts in the EU, so the two regions run different releases for a while**

Two host lanes on a time axis that is not to scale. The lane interserver-us runs five steps in order: the database backup, api-expand, api-binary-deploy, the soak, and api-migrate. After that it serves the new release. During all of this the lane hetzner-primary serves the previous release. An operator reviews the canary result. Only then does hetzner-primary run the same five steps in the same order.

- **Backup** (/usr/local/bin/backup_postgresql.sh): Runs first on each host. Its verified output is the only way back after a forward migration.
- **Expand** (site-app.yml --tags api-expand): Applies the backward-compatible part of the migration plan. The binary that still runs keeps working on the expanded schema.
- **Binary and soak** (site-app.yml --tags api-binary-deploy): Activates the new binary with the blue-green services. The operator checks health and logs and waits for the soak to end.
- **Migrate** (site-app.yml --tags api-migrate): Commits both release colors and applies the contract part of the plan. There is no generic rollback action after this step.
- **One host per run** (--limit interserver-us · --limit hetzner-primary): Each action that changes production targets exactly one inventory host. The EU sequence starts only after the canary result is reviewed.

Production has two hosts in the Ansible inventory: interserver-us in the US and hetzner-primary in the EU. Each runs the server as two services, plantocode-blue on port 8080 and plantocode-green on port 8081. Every action that changes production runs through site-app.yml with one explicit --limit host. infrastructure/ansible/operations.md is the runbook. Its inventory and secrets are operator configuration.

| Step on one host | Action and result |
| --- | --- |
| Backup | backup_postgresql.sh in /usr/local/bin. A verified database backup that the operator keeps. |
| Expand | --tags api-expand. The backward-compatible phase of the migration plan is applied and attested. |
| Binary | --tags api-binary-deploy. The new binary is active. The operator checks health and logs and completes the soak. |
| Migrate | --tags api-migrate. Both release colors are committed and the contract phase is applied. |

The runbook forbids three shortcuts. Never copy a release SQL file to a server and run psql by hand. Never run an application or database change without one explicit --limit. FLUSHALL on Redis is not a routine recovery action and needs explicit operator approval.

- [Why one relay process per region](https://www.hbcode.ai/docs/device-routing.md): Live sockets and pending requests are process state, so a drain comes before a process stops.