Files
aevolution/docs/adr/0059-postgresql-bootstrap.md
2026-10-08 12:08:53 -04:00

4.1 KiB

[Reviewed]

ADR-0059: PostgreSQL adapter and migration bootstrap

Status

Accepted for M7.2, 2026-09-28. No region authority or world deployment yet. Extended by ADR-0060 (schema v2 fencing functions and runtime grants). Supersedes ADR-0013's libpqxx recommendation for this scoped adapter.

Context

M7 needs parameterized SQL, bounded I/O and explicit ambiguous failure handling. Kubernetes connection details will be supplied through Secrets, per maintainer. The headless library must remain dependency-free with respect to PostgreSQL.

Decision

Pin PostgreSQL server and libpq to 18.6, built from the same official source: https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2 SHA-256: 555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f. Use direct libpq behind a C++ RAII adapter, optional Linux-only build target. Windows headless targets remain unchanged. No public PGconn/PGresult types. One I/O owner, one outstanding command, no hidden threads or connection pool. Use nonblocking connect/send/receive with a steady-clock deadline. Require a Unix socket or numeric hostaddr plus TLS hostname to avoid blocking DNS inside connect. Remote connections require verify-full. Drop a timed-out/broken connection and return unknown outcome, never retry mutations automatically. Bound parameters and stream rows; expose categories and SQLSTATE, never server messages/credentials.

Migrations run separately under an operator-provided owner login, using psql with ON_ERROR_STOP and transactional SQL. Initial schema is only migration metadata and region lease rows needed by M7.3; no speculative 30-table domain schema. Runtime login is not owner and receives no table mutation rights. Functions and grants follow with fencing in M7.3. No automatic migrations on server startup.

Use a checksum-pinned source preparation script, not a package manager for this single server/client artifact. It performs explicit downloads outside configure. Record system build prerequisites separately; a source pin is not a fully hermetic OS toolchain. vcpkg/Conan remain candidates for future client dependencies; neither would remove the need to provision/test the matching PostgreSQL server here.

Alternatives Considered

libpqxx 8.0.2 provides mature BSD-3-Clause C++ transactions but adds an API/exception layer over libpq without removing the need for bounded asynchronous I/O. Direct libpq has more RAII/result code but one official server/client release. ORM mapping and connection pools add no value to this single-owner bootstrap. Distro unpinned libpq is easier but does not establish a repeatable evaluated pair.

Benefits

Official maintained client, PostgreSQL permissive license, no extra runtime library beyond libpq and its declared TLS/system dependencies. Parameter separation and typed unknown outcomes are straightforward to inspect and test.

Costs

Linux polling adapter; explicit resource wrappers; source build/test time. Native Windows storage support and cross-platform package-manager cost comparison are not claimed. No performance advantage over libpqxx is claimed without measurement.

Risks

Timeout does not prove rollback. Query bounds protect the client, not arbitrary SQL resource use; callers are trusted persistence code and server statement limits remain necessary. Secrets require cluster RBAC/encryption policy, not just YAML. Minor release updates require a new checksum, security review and integration run.

Consequences

Real isolated PostgreSQL tests must cover parameters, rollback, deadlines, clean and repeated migrations, unknown migration versions, constraints and runtime-role denial. Deployment templates contain only Secret references; no credentials, no automatic World0 changes. SHA-256 provider for object manifests remains M7.4 work.

Primary evidence