Skip to content
Vinxi/Kernel THE BOOK OF RECORD

ADR-0038: The transactional schema is extension-free

ADR-0038: The transactional schema is extension-free

Status

Accepted

Date

2026-07-05

Context

The Postgres migrations hard-required two extensions — postgis and pgcrypto — created in migrations/postgres/000001_init_extensions.up.sql. That single file made the schema un-runnable on any PostgreSQL that does not ship those extensions: the dev stack and CI were pinned to the postgis/postgis image, and the schema could not be hosted on an extension-less engine.

Two directions made that lock-in a real constraint rather than a convenience:

  • One-binary / embedded deployment. The platform direction is a kernel that operates its own stores end to end — postgresql_embedded on the server, and (at the small-scale / browser tier) PGlite or DuckDB — none of which can be assumed to carry PostGIS/pgcrypto. See ADR-0007 (lean profile first) and ADR-0004 (sqlx, portable SQL over an ORM).
  • A browser target. Running the kernel in WASM with a WASM relational store (PGlite / DuckDB-wasm) is only possible if the schema needs no native C-extension.

Auditing actual usage showed the dependency was almost vestigial: no geometry/geography columns or ST_* calls exist in any migration or in Rust SQL — geometry already lives as GeoJSON inside jsonb traits, validated Rust-side, and spatial ingest already runs through DuckDB. The only pgcrypto-adjacent usage is gen_random_uuid() defaults, which is a core PostgreSQL built-in since PG 13 (we target 17), independent of the extension.

Decision

The transactional schema is extension-free. It uses only a portable relational subset that runs on any vanilla PostgreSQL, and by extension on postgresql_embedded, PGlite, and (through its own adapter) DuckDB.

Concretely:

  • No create extension. postgis and pgcrypto are removed from 000001. UUID defaults keep gen_random_uuid() (core PG 13+, not pgcrypto).
  • Spatial belongs to the analytical engine, not the store. Geometry at rest is GeoJSON (later WKB) in plain columns; spatial operators run in the analytical plane (DuckDB over Parquet / duckdb-wasm), never as PostGIS in the transactional path.
  • Crypto and identifiers are application-side or core built-ins — no pgcrypto digest()/crypt()/pgp_*; encryption stays in Rust (chacha20poly1305), uuids stay gen_random_uuid() / uuid-v5.
  • Time series stay plain — append tables with a time index, no extension-backed types.
  • JSON usage stays portable — plain jsonb + core GIN (jsonb_ops); no extension-only operator classes (btree_gin, pg_trgm, …).

Enforcement is mechanical, not aspirational: a filesystem guard test (migrations_are_extension_free in vinxi-adapter-postgres) fails on any create extension / geometry( / geography( / ::geometry / ::geography / tsvector token, and the dev + CI Postgres images are vanilla postgres:17 — a green suite on an image that has no PostGIS is the permanent proof.

Consequences

  • Unblocks the embedded and browser DB tiers. The same schema now backs real Postgres (scale), postgresql_embedded (single-binary server), and PGlite / DuckDB-wasm (browser / low-scale) behind the existing StatePort adapter seam. This is the prerequisite for the one-binary platform direction.
  • Spatial responsibility moves to the analytical plane. Spatial queries are answered by DuckDB (native or wasm) over materialized Parquet, not by the transactional store. At the server/scale tier this introduces a materialization/staleness concern (the existing materialization schema and derivation evaluator are the hooks); the embedded/browser tiers collapse it by using one engine for operational + analytical + spatial.
  • A one-time migration-checksum break. Editing the applied 000001 invalidates sqlx’s stored checksum on existing long-lived databases; those are re-created (./dev reset per worktree). CI is unaffected (fresh DB per run). This was a sanctioned, deliberate one-time exception to the “never edit an applied migration” rule; the rule stands afterwards.
  • Regression is guarded. The guard test plus vanilla-image CI prevent the extension surface from creeping back.
  • Supersedes nothing (the extension requirement was never an ADR); it records a now-binding constraint referenced by the one-binary platform direction.