paperclip/docs/deploy/database.md at master

mirror of https://github.com/paperclipai/paperclip.git synced 2026-10-02 02:07:25 +08:00

Files

T

Jannes StubbemannandClaude Fable 5.1 023e640a7e fix(db): reap idle pool connections, name the pool, and end it on shutdown (#12956 )

## Thinking Path

> - Paperclip is the open source app people use to manage AI agents for
work.
> - The server keeps one postgres.js pool (`packages/db/src/client.ts`,
`createDb`) for every query it runs. #10795 made the pool tunable from
the environment, but the defaults stayed at the driver defaults: an idle
connection never closes, the pool reports itself as `postgres.js`, and
no code path ever calls `sql.end()`.
> - On a hosted Paperclip deployment the server entered a restart loop
(a bundled plugin failure that #12953 describes made every run fail, and
the pool saturated). Each generation opened its ten connections, died,
and left the backends open on the PostgreSQL side until TCP keepalive
reaped them hours later. After about 20 generations the backends
exceeded `max_connections`, and every later boot died on its first
bootstrap query with `sorry, too many clients already`, before
`server.listen()`. The loop could not heal itself. #9555 describes the
same shape on a launchd-supervised self-hosted install.
> - Three properties of the pool combine to make this possible: idle
connections are never reaped, the pool is never ended on any exit path,
and an operator cannot even find the leaked backends in
`pg_stat_activity` because they carry the generic driver name.
> - This pull request gives the pool a 60 second idle timeout and the
`paperclip` application name by default, exposes `max_lifetime` and
`application_name` through the same `DATABASE_*` environment contract
that #10795 introduced, and ends the pool on the orderly SIGINT/SIGTERM
path and on the fail-loud startup path.
> - The benefit is that a restarting or crash-looping server releases
its backends instead of accumulating them, and an operator can see and
count Paperclip's connections.

## Linked Issues or Issue Description

- Refs #9555 — database connection pool leak causes an infinite restart
loop under load. This PR closes the "pool never ends, idle connections
never close" part of that report.
- Refs #12953 — hosted outage report. The pool exhaustion is the second
half of that incident; the first half (a stuck sandbox provider plugin)
has its own PR.
- Related prior PRs: #9597 and #8780 both propose hard-coded
`idle_timeout` / `max_lifetime` values in `createDb`. Both predate
#10795 (merged), which made these options environment-driven; this PR
builds on the merged shape and adds the shutdown `end()` that neither
covers. #4006 and #7481 are closed earlier attempts in the same area.

## What Changed

- `packages/db/src/client.ts`
- New `resolveDatabaseClientOptions()` applies Paperclip defaults on top
of the environment: `idleTimeoutSeconds` defaults to 60
(`DEFAULT_DATABASE_IDLE_TIMEOUT_SECONDS`) and `applicationName` to
`paperclip` (`DEFAULT_DATABASE_APPLICATION_NAME`). `createDb` uses it
for both the environment path and explicit options.
- `DATABASE_IDLE_TIMEOUT_SECONDS` now accepts `0` to restore the driver
default (keep idle connections open). Negative or non-integer values
still throw.
- New environment variables: `DATABASE_MAX_LIFETIME_SECONDS` (positive
integer, maps to `max_lifetime`) and `DATABASE_APPLICATION_NAME`
(non-empty string, maps to `connection.application_name`).
  - `postgresJsOptions()` maps the two new options.
- `server/src/shutdown.ts`
- `finalizeServerShutdown` gains two optional ordered steps:
`closeHttpListener` runs first, before the application services stop;
`closeDatabase` runs after the application services and before the
embedded PostgreSQL stop. A failure in either is logged and does not
stop the teardown. Final order: listener → application services →
database pool → embedded PostgreSQL → instrumentation → Sentry.
- New `closeHttpListenerForShutdown()`: stops accepting requests, closes
idle keep-alive sockets, waits up to 5 s for open connections, then
closes whatever is left. Requests still in flight are drained while
every service is available, and none can reach a route after
`sql.end()`, on the signal path and the programmatic path alike (the
programmatic path's later `server.close` finds the listener closed and
skips).
- `server/src/app.ts`: the app shutdown hook (`shutdownAppServices`) now
stops the plugin job scheduler, whose tick queries the database, so a
programmatic `shutdown()` leaves no timer running against the ended
pool.
- `server/src/index.ts`
- `startServer()` is now a thin wrapper around the boot sequence. When
the boot sequence throws after the pool exists, the wrapper ends the
pool (and the separate migration pool, when configured) before it
rethrows. This covers the `process.exit(1)` path in the main module and
the CLI `paperclip run` path alike.
- The orderly shutdown passes the same `closeDatabaseClients` to
`finalizeServerShutdown`.
- `endDatabaseClient` tolerates a client without `$client` (test
doubles) and uses a 5 second end timeout.
- Docs: `docs/deploy/database.md` gets a "Connection Pool Settings"
table with every `DATABASE_*` pool variable, its default and its effect;
`doc/DATABASE.md` lists the two new variables.
- Tests
- `packages/db/src/client-options.test.ts`: parsing of the new
variables, `0` for the idle timeout, rejection of malformed values,
driver option mapping, and the `resolveDatabaseClientOptions` defaults.
- `packages/db/src/client.test.ts` (embedded PostgreSQL):
`createDb(url)` reports `application_name = paperclip` for its own
backend, and a pool with `idleTimeoutSeconds: 1` has zero backends in
`pg_stat_activity` after the timeout.
- `server/src/shutdown.test.ts`: the listener closes before the
application services, and the database close runs between the
application services and the embedded PostgreSQL stop; a failing
database close is logged while the teardown still finishes;
`closeHttpListenerForShutdown` closes idle sockets and resolves on
close, force-closes after the grace period, and is a no-op when the
listener was never bound.

## Verification

- `pnpm --filter @paperclipai/db typecheck` — passes (`check:migrations`
+ `tsc --noEmit`).
- `cd server && pnpm typecheck` — passes.
- `cd packages/db && pnpm exec vitest run src/client-options.test.ts
src/client.test.ts src/client-teardown-registry.test.ts` — 9 + 18 + 3
tests pass (the `client.test.ts` cases need embedded PostgreSQL; the new
one waits up to 10 s for the idle reap and passed in about 3 s).
- `cd server && pnpm exec vitest run src/shutdown.test.ts
src/__tests__/server-startup-feedback-export.test.ts
src/__tests__/bootstrap-claim-routes.test.ts` — 34 + 11 tests pass. The
startup-feedback suite exercises `startServer()` with a mocked
`createDb`, which is why `endDatabaseClient` tolerates a client without
`$client`.
- Manual check for a reviewer: start the server against any PostgreSQL,
then run `SELECT application_name, state, count(*) FROM pg_stat_activity
GROUP BY 1, 2;`. Paperclip's backends now show `paperclip`. Leave the
server idle for more than 60 s and the idle backends disappear. Send
SIGTERM and the backends close before the process exits.

## Risks

- Behavior change with no environment set: idle pooled connections now
close after 60 s. The next query after an idle period pays a reconnect
(single-digit milliseconds on a local socket). postgres.js reconnects
transparently. Set `DATABASE_IDLE_TIMEOUT_SECONDS=0` to keep the
previous behavior.
- `application_name` changes from `postgres.js` to `paperclip`. Anything
that filtered `pg_stat_activity` on the old name would need an update;
nothing in this repo does.
- The HTTP listener now closes at the start of the final teardown (after
the heartbeat run drain, which still needs the API for running agents).
The pool close runs after the application services. A late query from a
timer that survived the service shutdown would fail with a driver
"connection ended" error instead of running; the known database-backed
timer (the plugin job scheduler) is now stopped in the service shutdown.
- The listener drain adds at most 5 s to a shutdown while long-lived
connections (for example WebSocket clients) are open; after that they
are closed forcibly.
- `startServer()` is split into a wrapper and the boot sequence. The
exported signature and return type are unchanged.
- No migration, no schema change.

## Model Used

- Claude Fable 5.1 (`claude-fable-5-1`) via Claude Code, extended
thinking, tool use (file edits, shell, test runs). The change was
produced with the model and reviewed by the submitting human.

## Checklist

- [x] I have included a thinking path that traces from project context
to this change
- [x] I have specified the model used (with version and capability
details)
- [x] I have checked ROADMAP.md and confirmed this PR does not duplicate
planned core work
- [x] I have searched GitHub for duplicate or related PRs and linked
them above
- [x] I have either (a) linked existing issues with `Fixes: #` / `Closes
#` / `Refs #` OR (b) described the issue in-PR following the relevant
issue template
- [x] I have not referenced internal/instance-local Paperclip issues or
links (only public GitHub `#NNN` / `github.com/paperclipai/paperclip`
URLs)
- [x] My branch name describes the change (e.g. `docs/...`, `fix/...`)
and contains no internal Paperclip ticket id or instance-derived details
- [x] I have run tests locally and they pass
- [x] I have added or updated tests where applicable
- [x] I have updated relevant documentation to reflect my changes
- [x] I have considered and documented any risks above
- [x] All Paperclip CI gates are green
- [x] Greptile is 5/5 with no open P2s, recommendations, or follow-ups
- [x] I will address all Greptile and reviewer comments before
requesting merge

🤖 Generated with [Claude Code](https://claude.com/claude-code)

https://claude.ai/code/session_014t3bi2beVNVVHAxK36dmXm

---------

Co-authored-by: Claude Fable 5.1 <noreply@anthropic.com>

2026-09-08 08:56:39 -07:00

3.6 KiB

Raw Permalink Blame History

title, summary

title	summary
Database	Embedded PGlite vs Docker Postgres vs hosted

Paperclip uses PostgreSQL via Drizzle ORM. There are three ways to run the database.

1. Embedded PostgreSQL (Default)

Zero config. If you don't set DATABASE_URL, the server starts an embedded PostgreSQL instance automatically.

pnpm dev

On first start, the server:

Creates ~/.paperclip/instances/default/db/ for storage
Ensures the paperclip database exists
Runs migrations automatically
Starts serving requests

Data persists across restarts. To reset: rm -rf ~/.paperclip/instances/default/db.

The Docker quickstart also uses embedded PostgreSQL by default.

2. Local PostgreSQL (Docker)

For a full PostgreSQL server locally:

docker compose up -d

This starts PostgreSQL 17 on localhost:5432. Set the connection string:

cp .env.example .env
# DATABASE_URL=postgres://paperclip:paperclip@localhost:5432/paperclip

Push the schema:

DATABASE_URL=postgres://paperclip:paperclip@localhost:5432/paperclip \
  npx drizzle-kit push

3. Hosted PostgreSQL (Supabase)

For production, use a hosted provider like Supabase.

Create a project at database.new
Copy the connection string from Project Settings > Database
Set DATABASE_URL in your .env

Use the direct connection (port 5432) for migrations and the pooled connection (port 6543) for the application.

If using connection pooling (transaction mode), disable prepared statements via the environment — no source edits needed:

DATABASE_PREPARED_STATEMENTS=false

Related optional client tuning: DATABASE_POOL_MAX, DATABASE_IDLE_TIMEOUT_SECONDS, DATABASE_CONNECT_TIMEOUT_SECONDS, DATABASE_MAX_LIFETIME_SECONDS, DATABASE_APPLICATION_NAME. Driver defaults apply when unset, except that idle pooled connections close after 60 seconds (DATABASE_IDLE_TIMEOUT_SECONDS=0 keeps them open) and the pool reports application_name=paperclip. See Connection pool settings.

Connection Pool Settings

The server opens one postgres.js pool for its own queries (and a second one when DATABASE_MIGRATION_URL points at a different connection). Every setting is optional:

Variable	Default	Effect
`DATABASE_POOL_MAX`	`10` (driver)	Maximum pooled connections.
`DATABASE_IDLE_TIMEOUT_SECONDS`	`60`	Close a pooled connection after this much idle time. `0` keeps idle connections open forever (the driver default).
`DATABASE_CONNECT_TIMEOUT_SECONDS`	`30` (driver)	Give up on a connection attempt after this long.
`DATABASE_MAX_LIFETIME_SECONDS`	30–60 min, randomized (driver)	Recycle a pooled connection once it is this old.
`DATABASE_APPLICATION_NAME`	`paperclip`	Value of `application_name` in `pg_stat_activity`, so you can find Paperclip's backends: `SELECT * FROM pg_stat_activity WHERE application_name = 'paperclip';`
`DATABASE_PREPARED_STATEMENTS`	`true` (driver)	Set `false` behind a transaction-mode pooler (see above).

The server ends its pools during shutdown (SIGINT/SIGTERM) and when startup fails after the pool was opened, so a restarting server does not leave idle backends behind. Size max_connections on the PostgreSQL side for at least DATABASE_POOL_MAX per server process plus your other clients.

Switching Between Modes

`DATABASE_URL`	Mode
Not set	Embedded PostgreSQL
`postgres://...localhost...`	Local Docker PostgreSQL
`postgres://...supabase.com...`	Hosted Supabase

The Drizzle schema (packages/db/src/schema/) is the same regardless of mode.

3.6 KiB Raw Permalink Blame History Unescape Escape