Managed PostgreSQL extensions: finish observed-version reporting and lifecycle E2E #74

Objective

Let the managed PostgreSQL driver provide a closed, versioned set of compiled extensions, starting with pgvector, without weakening the managed-service boundary into arbitrary images or runtime package installation.

This should be a general Onebox capability with PostgreSQL as the first implementation, not a Monk-specific exception.

Why

Today services: {postgres: 18} fixes the service image to postgres:18. Driver settings become PostgreSQL -c parameters; there is no extension, variant, image, package, or initialization-hook contract. Extensions that need server binaries therefore force the database into a user-owned daemon workload.

The repository already documents this boundary in e2e/apps/immich.yml: its vector-enabled PostgreSQL image cannot use the managed driver. Monk has the same requirement for its proposed hybrid PostgreSQL full-text + pgvector research store, while its current custom PostgreSQL 18 image also carries WAL-G.

Onebox should be able to own a vector-capable PostgreSQL service completely: image provenance, compatibility, lifecycle, credentials, persistence, health, planning, and recovery evidence.

Contract direction

Add a driver-owned capability surface rather than overloading settings. The exact authored shape needs design, but it should express an allowlisted extension and a pinned version, for example:

services:
  postgres:
    version: 18
    features:
      extensions:
        vector: PINNED_VERSION

The driver resolves that declaration to a known digest-pinned image. It must not accept an arbitrary repository, Dockerfile, package name, shell command, or unbounded extension identifier. If Onebox cannot own a combination, validation refuses it and directs the user to a daemon workload.

Application migrations remain responsible for enabling an available extension in the intended database:

CREATE EXTENSION IF NOT EXISTS vector;

Onebox provides and verifies the compiled extension; it does not silently mutate every database.

Requirements

Driver and image ownership

• Define a closed per-driver extension catalogue, starting with postgres / vector.
• Bind PostgreSQL major versions to explicitly supported extension versions.
• Resolve each supported combination to a signed or otherwise provenance-verifiable, digest-pinned image.
• Build or resolve images before production; never install packages with ob exec, container startup scripts, or a production build.
• Include the resolved extension identity and image digest in plan, canonical state, and drift comparison.
• Reject unknown extensions, unsupported combinations, downgrades, and removal when safe continuity cannot be proven.

Runtime semantics

• Preserve the existing managed-service ownership model: separate Compose project, durable volume, target-generated credential, health gate, and injected connection parts.
• Verify the declared extension is present in pg_available_extensions before the service is considered ready.
• Report available and enabled extension versions through status/doctor without exposing credentials.
• Keep database-level CREATE EXTENSION and extension-schema migrations under application ownership.

Lifecycle and adoption

• Plan extension installation, upgrade, downgrade, and removal as explicit stateful changes.
• Preflight the existing cluster for enabled extension versions and incompatible objects.
• Support moving an existing same-major PostgreSQL service to an extension-capable managed image without replacing or silently renaming its data volume.
• Document dump/restore when in-place adoption is not safe.
• Exercise rollback before declaring an extension combination supported.

Protection dependency

Managed PostgreSQL currently has no executable backup or restore contract. #63 tracks that existing subsystem. This issue must integrate with it rather than invent a second backup path.

• Do not describe extension-capable PostgreSQL as production-managed until the selected service has current backup evidence and a passing isolated restore proof.
• Backup and restore must preserve extension metadata and use an image containing the same compatible binaries.
• A stateful extension change must participate in the migration-backup gate.

First implementation: pgvector

Use pgvector as the first extension because it exercises the whole contract:

• compiled server binaries tied to PostgreSQL major version;
• database-level enablement through CREATE EXTENSION vector;
• extension-version upgrades;
• vector column, exact search, and HNSW/IVFFlat index persistence;
• backup and restore into an extension-capable image;
• measurable image, WAL, storage, and upgrade effects.

The implementation should not assume vector-only retrieval or make pgvector an application framework. It only supplies a trustworthy PostgreSQL capability.

Non-goals

• Arbitrary custom images inside services.
• Runtime apt, source compilation, or mutable package installation.
• Automatically enabling extensions in every database.
• Treating a migration success as backup or restore proof.
• Adding a Monk-specific driver or hidden exception.
• Claiming general support for PostGIS, TimescaleDB, VectorChord, or other extensions before each has its own compatibility and lifecycle contract.

Delivery slices

1. Contract: choose and document the driver-owned feature shape, compatibility model, plan representation, and refusal behavior.
2. Artifact: publish and resolve the first PostgreSQL 18 + pinned pgvector image with provenance and digest binding.
3. Lifecycle: add availability health, status/doctor facts, drift detection, and upgrade/removal preflights.
4. Protection: connect the image and extension identity to Protection layer: 4,865 lines in the tree, no command reaches it, no spec on main, no tracking issue #63 backup, restore, and migration gates.
5. Adoption: prove a plain PostgreSQL 18 data volume can move to the vector-capable image, enable pgvector by migration, restart, back up, restore, and roll back safely.
6. Product proof: migrate one real consumer only after those gates pass. Monk is a suitable first consumer because it already owns PostgreSQL 18, WAL-G, SQLx migrations, and a proposed pgvector workload.

Verification

• Schema and loader tests accept the supported declaration and reject unknown or incompatible extensions.
• Generated service runtime uses the expected digest-pinned image and preserves existing credential/volume semantics.
• A PostgreSQL 18 service reports the pinned pgvector version in pg_available_extensions.
• An application migration enables vector, writes vectors, performs exact distance search, creates an index, and survives restart.
• Plan and drift output change when the extension or its resolved image changes.
• Unsafe removal/downgrade is refused with an actionable code.
• Existing plain PostgreSQL managed services remain byte-for-byte unchanged when no extension is declared.
• Backup and isolated restore preserve extension version, schema, data, and indexes once Protection layer: 4,865 lines in the tree, no command reaches it, no spec on main, no tracking issue #63 is executable.
• Documentation states the managed-service versus daemon boundary and never implies that arbitrary PostgreSQL add-ons are supported.

Done when

A project can declaratively request PostgreSQL 18 with a pinned pgvector capability; Onebox validates and plans a known image, runs it with normal managed-service semantics, proves extension availability, observes lifecycle drift, safely handles adoption and upgrades, and has current backup plus isolated restore evidence through #63 before production use.