feat(services): support driver-owned PostgreSQL extensions

[vishr]

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.

[vishr]

Two observations on dependency and one on the authored shape.

Slice 2 is the same commitment as #63's PostgreSQL work

"Publish and resolve the first PostgreSQL 18 + pinned pgvector image with provenance and digest binding" is the same obligation as task 7.2 of the complete-oss-production-baseline spec that #63 tracks: poll upstream, build twice for reproducibility, emit SBOM, sign or transparency-publish provenance, atomically publish a qualified mapping, hold a publication SLO.

That spec is not on main — it survives at tag v2026.8.0:

git show v2026.8.0:openspec/changes/complete-oss-production-baseline/tasks.md

So both issues independently require Onebox to become a container image publisher, for two different images. Worth deciding once whether that is a business this project wants, and if so building one pipeline that serves both, rather than arriving at it twice.

Slices 4 and 6 are gated on a decision that has not been made

Slice 4 connects to #63's backup, restore, and migration gates, and states the requirement not to invent a second backup path. #63 is currently a question, not a subsystem: 4,865 lines that no command reaches, with restore, remove, and freeze all still open. Slice 6 depends on slice 4.

Slices 1 and 3 — the contract shape, availability health, status/doctor facts, drift detection — do not depend on #63 and could proceed. Slices 2, 4 and 6 cannot be finished until #63 is answered.

The authored shape pins a version the driver owns

features:
  extensions:
    vector: PINNED_VERSION

This asks each project to name a pgvector version, while the issue's premise is that the driver owns image, provenance, and compatibility. It is the same duplication #75 argues against for Redis flags: requiring every application to understand internals the driver already owns.

A shape closer to that premise:

features:
  extensions: [vector]

The driver's catalogue resolves the extension version for the declared PostgreSQL major, and the resolved identity still appears in plan, canonical state, and drift comparison — so reproducibility comes from the plan artifact rather than from every author tracking upstream releases. If an explicit pin is wanted for auditability, make it optional and refuse unknown values, rather than required.

Sequencing against #75

They touch internal/app/services.go in different places — #75 changes command construction, this changes image resolution — so no conflict. #75 is small, blocked on nothing, and fixes a defect affecting all ten managed drivers today, so it is the cheaper one to land first.

Not in dispute

The non-goals are the strongest part of this issue and worth preserving verbatim: no arbitrary images, no runtime package installation, no automatic CREATE EXTENSION, no Monk-specific exception, and no claimed support for PostGIS or TimescaleDB before each has its own contract. e2e/apps/immich.yml is real evidence of the gap this closes.