Phase 2.3: Extension-aware type system

[aesslinger]

Part of the Phase 2 set of PostgreSQL-specific features beyond the built-in driver.

Problem

PostgreSQL's extension ecosystem adds types the manifest's static data_types list can't represent — PostGIS geometries, pgvector embeddings, ltree paths, hstore key-value pairs, citext case-insensitive text. Right now the plugin has no way to detect which extensions are installed on a given database, so columns using these types fall through to a generic/unrecognized handling path in the UI.

Proposed approach

Detect installed extensions via pg_extension:

SELECT extname, extversion FROM pg_extension WHERE extname IN (
    'postgis', 'vector', 'ltree', 'hstore', 'citext'
);

For each detected extension, add its types to the runtime type list. Initial extensions to support:

• PostGIS — geometry, geography, raster types
• pgvector — vector(N) type for embeddings
• ltree — label tree type
• hstore — key-value store (legacy, still common) — port tabularis#427's logic here rather than reimplementing (check whether that PR has merged before starting)
• citext — case-insensitive text

Gotcha: static manifest types

.tabularium's data_types list is static — it can't vary per-connection. Dynamic type discovery needs either:

• A new RPC method, get_dynamic_data_types(params), returning additional types based on what's actually installed on that connection, or
• The plugin returning a comprehensive superset up front and letting the UI filter down to what's usable.

Pick whichever the frontend's type-picker architecture makes cheaper — check with the tabularis maintainers if the manifest-loading contract isn't clear from the current frontend code.

Acceptance criteria

• At least PostGIS and pgvector types are detected and correctly represented in the type picker
• Extension detection doesn't error or slow down initialize when no extensions are installed
• hstore support reuses/ports tabularis#427's logic rather than reimplementing it
• No regressions in existing Phase 1 parity tests

Tests

• test_detect_postgis_extension (requires PG with PostGIS — optional CI extension)
• test_vector_type_handling (requires pgvector)
• test_ltree_insert_and_query

These may need #[ignore] in CI unless the extensions are installed in the test container — consider a separate "extended type" test profile rather than skipping coverage entirely.

References

• Full spec: docs/planning/03-phase-2-issue-16.md (2.3)
• Priority: Sprint 3 — high demand for PostGIS/pgvector users
• Required for the Phase 2 stable-release gate (CP-5) — at least one extension type must be handled before promoting to stable