Reference

Connection Pooling

Spectral’s API + workers acquire database connections through Supavisor transaction mode (port 6543) using psycopg 3 async pools. Tenancy is enforced via SET LOCAL inside an explicit transaction — never at pool checkout. Decision lineage in ADR-041.

Endpoint

Supavisor transaction mode is the primary endpoint for API and worker request-scope work. It multiplexes to 200 clients on Pro Micro vs 60 raw max_connections.

The LISTEN/NOTIFY listener process (per event substrate) owns a dedicated direct-to-Postgres connection on port 5432. LISTEN is incompatible with transaction-mode pooling — Supavisor releases the session at commit, which breaks LISTEN.

Supavisor session mode is not used.

Driver

psycopg 3 async with psycopg_pool.AsyncConnectionPool. No SQLAlchemy.

prepare_threshold=None is the single honored switch for transaction-mode compatibility. autocommit=False on the configure callback forces every query through an explicit transaction — making the SET LOCAL correctness rule (below) structural rather than disciplinary.

Tenancy via `SET LOCAL` inside explicit transactions

The correctness-critical pattern.

Pool-checkout hooks fire at logical-connection-checkout time, but Supavisor transaction mode assigns a fresh physical backend per transaction — anything SET at checkout may not be on the backend where the query runs. SET LOCAL inside BEGIN…COMMIT is Postgres-guaranteed to stay on the same backend and clear at commit/rollback.

The session-var contract lives in spectral.core.db.session_vars:

SESSION_VAR_ACCOUNT_ID: Final[str]   = "app.account_id"
SESSION_VAR_WORKSPACE_ID: Final[str] = "app.workspace_id"
SESSION_VAR_USER_ID: Final[str]      = "app.user_id"

These constants are referenced by every RLS policy in every schema. Contract tests pin the exact strings.

Per-context pools

Three pools, one per context role:

spectral_core_app
spectral_worlds_app
spectral_platform_app

search_path is set at role level via ALTER ROLE spectral_<context>_app SET search_path = <schema>, public. Reaching for another context’s tables from the wrong role fails at the database layer. SET ROLE at checkout is not used — role-at-login is enforced by Postgres, visible in pg_stat_activity, and simplifies audit identity attribution.

The structural separation enforces two invariants. Schema isolation: a query against another context’s table fails at the Postgres role layer, not at code review. Tenancy isolation: because pool checkout assigns a fresh backend per transaction in Supavisor txn-mode, tenancy session vars must be set with SET LOCAL inside an explicit transaction (above) — and the request_scope context manager (below) is the sole sanctioned acquisition path.

Per-process pool sizing (alpha defaults)

Surface	Per context per process
API (FastAPI)	`min_size=2, max_size=5, timeout=10s`
Workers	`min_size=2, max_size=10, timeout=30s`
Listener	1 dedicated direct-to-Postgres connection (not in any pool)

Single-instance API + single-instance worker budget: 3 × 5 + 3 × 10 = 45 client-side sockets on the Supavisor transaction-mode endpoint.

Scaling trigger: instance count where (3 × 5 × API instances) + (3 × 10 × worker instances) > 160 (80% of the 200-client Pro Micro cap) → scale Supabase compute tier.

Idle and reconnect

max_idle=240s (sits under Supavisor’s 5-minute idle reaper)
configure callback sets autocommit=False
check callback runs SELECT 1 for pre-ping
Pool creation is lifespan-scoped in FastAPI (never module-level)
reconnect_timeout=None — the client retries with explicit backoff in app code

The `request_scope` context manager

The sole sanctioned path to acquire a connection for request- or job-scoped work; lands as spectral_api.db.request_scope and spectral_workers.db.request_scope per ADR-041 D8 with the API and workers epics:

async with db.request_scope(account_id, workspace_id) as conn:
    # BEGIN opened; SET LOCAL app.account_id = $1; SET LOCAL app.workspace_id = $2
    # conn is psycopg AsyncConnection; pool + context selected by caller
    ...
    # COMMIT (or ROLLBACK on exception) — session vars cleared automatically

A FastAPI dependency wraps it per request; the worker equivalent wraps it per job. No code outside these wrappers may acquire a raw pool connection.