Phase 2: PostgreSQL-specific features beyond the built-in driver (tabularis#16)

[aesslinger]

Summary

Phase 2 of the postgres-plugin migration (referenced from tabularis PR #577, full plan in docs/planning/03-phase-2-issue-16.md). Where Phase 1 was byte-for-byte parity with the built-in driver, Phase 2 is where the plugin exceeds it — implementing PostgreSQL-specific features the built-in driver never had, tracked upstream as tabularis#16 ("Better PostgreSQL Support": schema handling, specialized data types, sequences, materialized views, etc.).

Prerequisite: Phase 1 complete — done. 1.0.0-beta.3 is out, 82/82 parity, all Phase 1 sign-off items from #577 except cross-platform build verification/24-item smoke test/security audit/frontend regression (per this repo's CLAUDE.md).

Note on scope vs. the original plan: two items from #16's description are already resolved and NOT part of this issue:

• Non-public schema browsing (tabularis#15) — already implemented (get_schemas, schema-qualified queries throughout src/handlers/metadata.rs).
• HStore column editing — tabularis#427 merged upstream 2026-08-02. When we reach extension type support (2.3 below), port that logic into the plugin rather than reimplementing.

Approach

Same TDD discipline as Phase 1: write the test (RED) → implement (GREEN) → verify no regressions. Phase 2 features are plugin-only — they don't exist in the built-in driver, so there's no parity suite to satisfy; each feature gets its own dedicated tests instead.

Before implementing any feature below, check tabularis for in-flight PRs that already address it (gh pr list --repo TabularisDB/tabularis --search "<feature>") — avoid duplicating community work. Known relevant PRs as of this writing:

PR	Feature	Status
#402	Multi-database connections	Open
#222	Composite PK end-to-end	Open (draft)

Features (priority order)

2.1 — Sequence management

List, inspect, create, alter, reset, and drop PostgreSQL sequences (pg_sequences, ALTER SEQUENCE ... RESTART, setval). Currently invisible in Tabularis — users write raw SQL. Sidebar node alongside Tables/Views/Routines.

2.2 — JSONB inline editing

Structured JSONB editing (add/remove keys, nested values, raw/tree toggle) instead of raw-text-only. Plugin side: validate_jsonb, jsonb_patch RPC methods for targeted modification without full-value overwrite. Mostly a frontend UI extension; plugin provides validation + patch primitives.

2.3 — Extension-aware type system

Detect installed extensions (pg_extension) and expose their types: PostGIS (geometry/geography), pgvector (vector(N)), ltree, hstore (port #427's logic here), citext. Needs a get_dynamic_data_types RPC method since the manifest's data_types list is static.

2.4 — Partition table introspection

Show partition hierarchy in the sidebar (parent + child partitions, partition key, bounds) via pg_class.relkind = 'p' / pg_inherits / pg_get_partkeydef.

2.5 — Row-level security policy visibility

Display RLS policies per table (pg_policy): command, roles, USING/WITH CHECK expressions. Security note: only show to connection owner/superuser, since USING expressions can reveal security-rule internals.

2.6 — Publication/subscription visibility

Show logical replication publications/subscriptions (pg_publication, pg_subscription) for monitoring. New "Replication" sidebar section.

2.7 — Advisory lock monitoring

Show currently held advisory locks (pg_locks joined to pg_stat_activity) for lock-contention debugging. Security note: same visibility gating as pg_stat_activity (pg_monitor role).

Full SQL, frontend integration notes, and per-feature test lists for each item are in docs/planning/03-phase-2-issue-16.md — this issue is the tracking summary, that doc is the detailed spec.

Suggested sprint order

Sprint 1: Sequence management       (high demand, straightforward)
Sprint 2: JSONB inline editing      (high demand, more complex — UI extension)
Sprint 3: Extension type system     (high demand for PostGIS/pgvector users)
Sprint 4: Partition introspection   (medium demand)
Sprint 5: RLS policies              (medium demand, straightforward)
Sprint 6: Pub/Sub + advisory locks  (lower priority, quick wins)

Checkpoint: CP-5 (Phase 2 complete — stable release gate)

This is a major release gate — the plugin becomes strictly better than the built-in driver, not just at parity with it.

• All Phase 1 parity tests still GREEN (no regressions)
• New Phase 2 features have dedicated tests (all GREEN)
• Sequence management works end-to-end
• JSONB editing works with nested objects
• At least one extension type (PostGIS or pgvector) handled
• Plugin published as stable (not beta) to the Tabularium registry

At minimum, sequences + JSONB + extensions must land before calling Phase 2 core done and promoting to stable.

Ship points

Each feature ships independently as a beta update as it lands — no need to wait for the full Phase 2 set. Promotion to stable happens once the CP-5 minimum bar (sequences + JSONB + at least one extension type) is met.