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

74 lines
4.1 KiB
Markdown

[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
- [Official releases](https://www.postgresql.org/ftp/source/)
- [PostgreSQL license](https://www.postgresql.org/about/licence/)
- [Connection polling and DNS restrictions](https://www.postgresql.org/docs/18/libpq-connect.html)
- [Async parameterized queries](https://www.postgresql.org/docs/18/libpq-async.html)
- [libpqxx releases and license](https://pqxx.org/libpqxx/)