1# =============================================================================
2# AI Software Factory — PgBouncer (transaction mode) — TASK-0135 pooling.
3#
4# Why a pooler (and not per-app Prisma pools):
5# Prisma 5.22 has NO client-side pool (PrismaClientOptions = {datasources,
6# datasourceUrl, errorFormat, log, transactionOptions} — connectionLimit /
7# poolTimeout are Prisma 6). Connection pooling is therefore owned HERE,
8# in front of Postgres, in **transaction mode** (Prisma's required mode:
9# the client opens a connection, runs one transaction, and may be handed a
10# different backend next time).
11#
12# Auth:
13# `auth_query` runs `SELECT usename, passwd FROM pg_catalog.pg_shadow
14# WHERE usename = $1` as the `factory` role (which has the privilege to read
15# pg_shadow's own scram verifier) to verify each incoming app user. So the
16# apps keep connecting with REAL roles (api/worker/realtime/web) — pgbouncer
17# proves each one to the primary before pooling it. The app roles + the
18# read-only `reader` role are created by the primary's first-boot SQL
19# (postgres/configmap.yaml `zz-app-roles.sql`) and granted the `factory`
20# role's privileges, so they can use the same `factory` database.
21#
22# How the apps connect (per-app bounded pools, one role each):
23# api: postgresql:
24# worker: postgresql:
25# realtime: postgresql:
26# web: postgresql:
27# (DATABASE_REPLICA_URL, the db.read() path:
28# postgresql:
29# In dev there is no pooler — DATABASE_URL points at Postgres directly as
30# `factory` and pool sizing is meaningless (one Prisma client per process).
31#
32# Pool sizing (per-app pool_size, transaction mode — the deployment.md
33# §pooling table):
34# api: 20 (the 1k-rps load path — busiest writer/reader)
35# worker: 20 (BullMQ steady state; FILES_WORKER_CONCURRENCY ≤ 8)
36# realtime: 10 (Yjs ops are Redis-first; PG is the flush/commit path)
37# web: 5 (server-side rendering reads only)
38# reader: 15 (the read-replica pool — db.read() tree/listing/search)
39# Max total ≈ 70 server connections, comfortably under the PG16 default
40# max_connections=100 with headroom for superuser + maintenance.
41#
42# Staging check (the spec's "connection counts stay under the documented pool
43# limit under the api-load suite") — query the pooler's stats during api-load:
44# psql "postgres://factory:factory@factory-pgbouncer.factory.svc:6432/pgbouncer" \
45# -c "SHOW POOLS;" -- current_pool / max_client_conn per db+user
46# -c "SHOW STATS;" -- total_xact_count, avg_query_time
47# current_pool per user must stay ≤ that app's pool_size (above).
48# =============================================================================
49apiVersion: v1
50kind: ConfigMap
51metadata:
52 name: factory-pgbouncer
53 labels:
54 app.kubernetes.io/name: pgbouncer
55 app.kubernetes.io/part-of: ai-software-factory
56data:
57 pgbouncer.ini: |
58 [databases]
59 # Per-app pools (dbname;user → pool_size). Each app connects with its own
60 # role (created + granted by the primary's first-boot SQL). All pools hit
61 # the SAME `factory` database on the primary — separate roles = separate
62 # bounded pools, so one app's burst cannot starve another.
63 factory;api = host=factory-db.factory.svc port=5432 dbname=factory pool_size=20
64 factory;worker = host=factory-db.factory.svc port=5432 dbname=factory pool_size=20
65 factory;realtime = host=factory-db.factory.svc port=5432 dbname=factory pool_size=10
66 factory;web = host=factory-db.factory.svc port=5432 dbname=factory pool_size=5
67 # Read-replica pool (DATABASE_REPLICA_URL → db.read()). The `reader` role
68 # is read-only (see zz-app-roles.sql); the replica is a hot standby that
69 # rejects writes at the DB. A stray write fails there, not at the pooler.
70 factory;reader = host=factory-db-replica.factory.svc port=5432 dbname=factory pool_size=15
71 # pgbouncer's own stats database (SHOW POOLS / SHOW STATS).
72 pgbouncer = host=factory-db.factory.svc port=5432 dbname=postgres pool_size=2
73
74 [pgbouncer]
75 # transaction mode — the ONLY mode compatible with Prisma (the client
76 # opens a connection, runs one transaction, and is handed a possibly
77 # different backend next time).
78 pool_mode = transaction
79 # Authentication: a static userlist.txt of scram-sha-256 verifiers, one
80 # line per role pgbouncer accepts (api/worker/realtime/web/reader/
81 # factory). The verifiers are generated ONCE against the factory-db
82 # secret's password and stored in a `factory-pgbouncer-auth` secret
83 # (mounted as /etc/pgbouncer/userlist.txt). Format:
84 # "api" "SCRAM-SHA-256$<iter>:<salt>$<StoredKey>:<ServerKey>"
85 # ... (one per role, same password as the factory-db secret)
86 # Generate with: psql -c "SELECT 'SCRAM-SHA-256$' || ... " (the pg
87 # crypto) or the `passfile` pgbouncer ships. See the header's staging note.
88 auth_type = scram-sha-256
89 auth_file = /etc/pgbouncer/userlist.txt
90 listen_addr = 0.0.0.0
91 listen_port = 6432
92 max_client_conn = 250
93 max_db_connections = 100
94 default_pool_size = 20
95 server_lifetime_sec = 3600
96 server_idle_timeout = 300
97 log_connections = 1
98 log_disconnections = 1
99 stats_period = 30
100 admin_users = factory
101 ignore_startup_parameters = search_path,extra_float_digits
102 application_name_add_host = 1
103
104# STAGING-VERIFICATION NOTE: the pooler's auth wiring (auth_query + the app
105# roles' scram verifiers + the edoburu ini env-expansion) must be confirmed
106# against the real factory-db secret at first staging apply — there is no
107# cluster in this repo to test it. The app-role creation (zz-app-roles.sql,
108# postgres/configmap.yaml) + the DATABASE_URL users in the deployment.md
109# §pooling table are the contract this manifest implements.
110
111---
112apiVersion: apps/v1
113kind: Deployment
114metadata:
115 name: factory-pgbouncer
116 labels:
117 app.kubernetes.io/name: pgbouncer
118 app.kubernetes.io/part-of: ai-software-factory
119spec:
120 replicas: 2
121 selector:
122 matchLabels:
123 app.kubernetes.io/name: pgbouncer
124 template:
125 metadata:
126 labels:
127 app.kubernetes.io/name: pgbouncer
128 spec:
129 containers:
130 - name: pgbouncer
131 image: edoburu/pgbouncer:1.23.1
132 ports:
133 - name: pgbouncer
134 containerPort: 6432
135 volumeMounts:
136 - name: conf
137 mountPath: /etc/pgbouncer/pgbouncer.ini
138 subPath: pgbouncer.ini
139 readOnly: true
140 # The scram verifiers (factory-pgbouncer-auth secret, generated
141 # from the factory-db secret's password — see the ini header).
142 - name: auth
143 mountPath: /etc/pgbouncer/userlist.txt
144 subPath: userlist.txt
145 readOnly: true
146 readinessProbe:
147 tcpSocket: { port: 6432 }
148 initialDelaySeconds: 3
149 periodSeconds: 5
150 resources:
151 requests: { cpu: 100m, memory: 128Mi }
152 limits: { cpu: 500m, memory: 512Mi }
153 volumes:
154 - name: conf
155 configMap: { name: factory-pgbouncer }
156 - name: auth
157 secret: { secretName: factory-pgbouncer-auth }
158
159---
160apiVersion: v1
161kind: Service
162metadata:
163 name: factory-pgbouncer
164 labels:
165 app.kubernetes.io/name: pgbouncer
166 app.kubernetes.io/part-of: ai-software-factory
167spec:
168 selector:
169 app.kubernetes.io/name: pgbouncer
170 ports:
171 - name: pgbouncer
172 port: 6432
173 targetPort: 6432
174
