Home

pglens Roadmap

Status: Draft, May 2026. Successor to CHANGELOG.md for forward-looking work. Current version: 2.3.0. Target of this roadmap: 4.0.0.

1. Vision

pglens is the no-code Postgres workstation for engineers.

Engineers should not have to write SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC for the fifteenth time today. pglens covers 80% of daily database work with point-and-click primitives, and exposes a clearly-marked Advanced Mode for the 20% that genuinely needs raw SQL.

We are not competing with DataGrip or DBeaver on breadth of features. We are competing with them on time-to-useful-action and cognitive load. Our north-star user is a backend engineer who knows SQL but doesn't want to type it for routine work.

Positioning vs. the field

Tool	Where they sit	Our differentiation
DataGrip / DBeaver / pgAdmin	SQL-first IDEs, deep but heavy	We are no-code-first
TablePlus / Postico	Lightweight, polished, closed-source	We are open-source, web-based, multi-platform
Supabase Studio	Best-in-class no-code, tied to Supabase	We work against any Postgres
Retool / Forest Admin	No-code but commercial, app-builder shaped	We are a database tool, MIT-licensed, free

2. Strategic Principles

These should guide every feature decision. When in doubt, default to these.

No-code is the default surface. Every user-facing action must have a UI path. Raw SQL is an escape hatch, not the main road.
Advanced Mode is per-tab, not per-app. A user can hold a no-code view of users and a hand-written analytics query open simultaneously.
Every no-code action shows its SQL. A collapsible "Show SQL" affordance on every generated query. This teaches by example and earns credibility with senior engineers.
Postgres-native, not lowest-common-denominator. Lean into Postgres features (JSONB, arrays, EXPLAIN ANALYZE, pg_stat_*, extensions) rather than designing for an abstract SQL dialect.
Local-first, open-source, friction-free install. A single npm install -g pglens or curl | bash must remain the install story.
Ship behind toggles. New surfaces ship behind feature flags until they're polished, so main stays releasable.
The 5K-line vanilla app.js is the bottleneck. No major feature work happens on top of it after Phase 0.

3. Phase 0 — Foundation (target: v3.0.0)

Goal: Replace the frontend stack and harden the backend so every subsequent phase is buildable. This is the gating phase. Trying to build a filter builder, inline editor, or EXPLAIN visualizer on the current vanilla JS will compound debt to a stall.

3.1 Frontend migration

Stack: Vite + React 18 + TypeScript (strict mode).
Component library: shadcn/ui — copy components into the repo (you own the source), styled with Tailwind. Matches the Supabase Studio aesthetic users already expect from a modern DB tool.
Data layer: TanStack Query for all server state (caching, refetch, optimistic updates). This will eventually drive inline editing.
Table grid: TanStack Table + @tanstack/react-virtual for the data viewer. Replaces the hand-rolled grid in app.js and gets you virtualization, column resize, sort, and column visibility for free.
Routing: TanStack Router or React Router 7. Tabs map to routes; deep links to a table/view should work.
Client state: Zustand for small UI state (open tabs, theme, sidebar collapse).
Schema graph: Migrate the visualization to @xyflow/react (React Flow). Powers the editable ERD in Phase 4.
Editor (stub for now): Wire Monaco for the existing POST /api/query runner. Full SQL UX comes in Phase 2.
Validation: Zod schemas for every API response. Runtime safety + free TypeScript types.

3.2 Migration strategy

Don't do a big-bang rewrite. Strangler-fig pattern:

Stand up the new React app under client-next/ served at /v3 behind a feature flag.
Port one screen per week: Landing → Sidebar → Table viewer → Schema viz → Connection dialog → Query runner.
When the new app reaches parity, swap the default route and delete client/.

You ship every week during the migration. No long-lived feature branches.

3.3 Backend hardening (do in parallel)

Secrets: Move connection passwords from plaintext ~/.pglens.* files into the OS keychain via keytar. Keep connection metadata (host, port, schema, name) in a JSON file; secrets get a keychain reference.
Local auth: Generate a per-install token on first start; embed it in the URL pglens prints. Bind the server to 127.0.0.1 (not 0.0.0.0). This prevents accidental LAN exposure if a user changes the bind address.
Identifier escaping: Replace ^[a-zA-Z0-9_]+$ with a proper Postgres identifier escaper (double quote and double internal quotes). Today, perfectly valid mixed-case or Unicode identifiers are rejected.
Structured logging: Replace console.log with pino writing to ~/.pglens/logs/pglens.log with rotation. Add a pglens logs CLI command. Today stdio: 'ignore' makes wild debugging impossible.
Request validation: All API routes get a Zod schema; reject invalid bodies with 400 before they hit the DB.
Error envelope: Standardize error responses to { error: { code, message, hint } } so the frontend can render actionable messages.

3.4 Quality gates

Tests: Vitest for the frontend, node:test for the backend, Playwright for E2E. Boot Postgres in docker-compose for integration tests.
CI: GitHub Actions matrix on Node 18/20/22, macOS/Linux/Windows. Block PRs on test failure.
Coverage target: 60% for backend critical paths (connection, pagination, sanitization, query history) by end of phase. Don't chase 100%.

3.5 Definition of done for 3.0.0

New React/TS app reaches feature parity with 2.3.0 and is the default.
Old client/ directory is deleted.
Connection secrets are in the keychain on macOS and Windows (fall back to encrypted file on Linux if no Secret Service).
CI green on all three OSes with full test suite.
pglens logs works.

4. Phase 1 — No-Code Editing Core (target: v3.1.0)

Goal: Move pglens from "viewer" to "client." This is the biggest perceived-value jump in the entire roadmap. Every feature here must work without typing SQL.

4.1 Visual filter builder

A bar above every table grid:

Column dropdown (typeahead, grouped by data type)
Operator dropdown (type-aware: =, !=, >, <, IN, LIKE, IS NULL, IS NOT NULL, JSONB @>, array &&)
Value input (date picker for dates, dropdown for FKs, toggle for booleans, JSON editor for JSONB)
AND / OR grouping with parentheses
"+ Add filter" button
"Show SQL" disclosure that reveals the generated WHERE clause

Backend: Extend GET /api/tables/:tableName to accept a structured filter spec (server-side parsed, parameterized). Never accept raw SQL fragments from the filter UI.

4.2 Visual sort builder

Multi-column sort, drag-to-reorder priority. Already partially supported server-side (sortColumn/sortDirection); extend to an array.

4.3 Saved views

A view is a named bundle of (filter + sort + visible columns + column widths + timezone) scoped to a (connection, table) pair.

Stored in ~/.pglens/views.json, synced to disk on change
Sidebar shows views nested under each table
One default "All rows" view always exists
"Save current as view" button in the table toolbar

This is the feature that turns pglens from "I'm browsing a table" into "I have a workflow." Airtable's entire product is this primitive.

4.4 Type-aware inline editing

Double-click a cell to edit. Widget chosen by column type:

Type	Widget
`boolean`	Toggle
`date`, `timestamp`, `timestamptz`	Date picker (timezone-aware)
`json`, `jsonb`	Monaco JSON editor in a popup
FK column	Searchable dropdown showing referenced row preview
`enum` (PG enums)	Dropdown
`text`, `varchar`	Textarea with auto-size
`integer`, `numeric`	Number input with validation
`uuid`	Text input with format validation + "generate" button
Array types	Multi-input with add/remove rows

Editing modes (configurable per connection):

Auto-commit: Edits applied immediately on blur.
Review mode: Edits accumulate as pending; user reviews diff and clicks "Apply changes" to run as one transaction. Default for production-tagged connections.

Backend: New PATCH /api/tables/:tableName/rows accepting { where: <pk>, set: {...} }. Parameterized. Generates UPDATE ... WHERE pk = $1 and returns the updated row.

4.5 Row insert form

Generated from the schema:

NOT NULL fields are required
DEFAULT-having columns show the default ghosted
FK columns become lookup dropdowns
CHECK constraints surface as inline validation (parse pg_constraint.consrc where possible)
"Insert another" / "Insert and view" buttons after submit

POST /api/tables/:tableName/rows.

4.6 FK click-through navigation

Click any foreign-key cell → side panel slides in showing the full referenced row, with two buttons:

"Show all rows in orders that reference this customer" → opens a new tab with that filter pre-applied
"Edit referenced row" → switches the panel to edit mode

This is the killer exploration feature. Engineers stop writing joins to investigate data.

4.7 Aggregations strip

Bottom of every table grid. Per-column dropdowns:

Numeric: count, sum, avg, min, max, stddev
Text/date: count, count distinct, min, max
Boolean: count, count true / false

Computed server-side against the current filter. Replaces 90% of one-off SELECT COUNT(*) queries.

4.8 Per-table data export

CSV, JSON, SQL INSERT script. Respects the current filter and visible columns. Streams to the browser for large tables.

4.9 Per-table data import

CSV import wizard:

Upload CSV
Map CSV columns → table columns (auto-guess by header name)
Choose insert mode: INSERT, INSERT ... ON CONFLICT DO NOTHING, INSERT ... ON CONFLICT ... DO UPDATE
Dry run shows N rows to insert, N conflicts
Execute in a transaction

4.10 Definition of done for 3.1.0

A user can filter, sort, save a view, edit rows, insert rows, follow FKs, and export — without typing SQL.
Every action has a working "Show SQL" disclosure.
Editing is fully transactional with rollback on error.

5. Phase 2 — Advanced Mode (target: v3.2.0)

Goal: Make pglens credible to senior engineers who do want SQL, without compromising the no-code surface.

5.1 Per-tab Advanced toggle

A small switch in each tab header: [ No-code | Advanced ]. Flipping to Advanced replaces the grid with a Monaco SQL editor pre-populated with the SQL that no-code mode was about to run. State is preserved if the user flips back.

5.2 Monaco-based SQL editor

Postgres syntax highlighting (monaco-sql-languages or prismjs for tokenization)
Schema-aware autocomplete (table names, column names) sourced from the existing schema introspection
Multi-cursor, find/replace, multi-line
Format-on-save via pg-formatter running server-side
Parameterized query support: :foo placeholders rendered as a form below the editor

5.3 Transaction mode

A [ Auto-commit | Transaction ] toggle. In transaction mode:

BEGIN runs implicitly on first query
A persistent connection is held for the tab
A visible "T" badge in the tab indicates an open transaction
Toolbar shows Commit and Rollback buttons
Closing the tab triggers a confirmation modal

Backend: Reuse a pg client (not the pool) per Advanced tab, parked in a server-side session map keyed by the per-install token + tab id, with idle timeout.

5.4 Query result enhancements

Multiple result tabs from multi-statement queries
Result grid uses the same TanStack Table component as the no-code viewer (inherits filtering, sorting, export)
Query timing breakdown: parse, plan, execute (from EXPLAIN ANALYZE when toggled)

5.5 Improved query history & saved queries

History scoped per connection
Saved queries support {{parameters}} like Postman
Folders / tags for saved queries
Export/import saved queries as JSON

5.6 Definition of done for 3.2.0

A senior engineer can do their entire daily workflow in Advanced Mode without missing DataGrip.
Switching between no-code and Advanced on the same tab works seamlessly.

6. Phase 3 — Postgres-Native Operations (target: v3.3.0 + v3.4.0)

Goal: Be the tool engineers open when something is slow or broken. These features are visible to everyone — they're "ops," not "advanced."

6.1 Live activity dashboard (v3.3.0)

A new sidebar section: Operations.

Active connections — live pg_stat_activity view with state, wait event, query (truncated), idle time, age. Click a row → "Terminate" / "Cancel."
Locks & blocking — derived from pg_locks + pg_blocking_pids(). Show blocker → blocked chain visually.
Replication status — from pg_stat_replication. Lag in bytes and seconds.
Database size & table sizes — top 20 tables by size, with index sizes broken out.
Connection count — current / max, with a warning at 80%.

Refresh every 5 seconds while the panel is open.

6.2 Slow query view (v3.3.0)

Requires pg_stat_statements extension. If not enabled, show a one-click "Enable pg_stat_statements" with the DDL preview.

Top queries by total_exec_time, mean_exec_time, calls
Click a query → drilldown showing call count, mean / stddev / p95 / max time, total IO, with EXPLAIN integration
"Reset stats" button

6.3 EXPLAIN plan visualizer (v3.4.0)

This is one of pglens's biggest moats. The reference for execution is pev2.

Tree visualization of plan nodes with costs, rows, actual vs. estimated
Heatmap on slow nodes (red = problem)
Tooltips explaining each node type in plain English
One-click toggle between EXPLAIN and EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
Available from: (a) Advanced Mode toolbar, (b) any no-code query's "Show SQL" panel, (c) slow query drilldown

6.4 Index assistant (v3.4.0)

Watches the queries no-code mode generates (plus pg_stat_statements) and surfaces suggestions:

"This filter scanned 8M rows. An index on (status, created_at) would reduce it to ~10K. Create index?"
Unused indexes from pg_stat_user_indexes where idx_scan = 0
Duplicate indexes (same column list, different names)
Bloat warnings from pgstattuple if installed

Every suggestion shows the DDL, never executes it without confirmation.

6.5 Definition of done for Phase 3

A user with a slow app opens pglens, finds the slow query, reads the plan, applies an index, and confirms the improvement — all in pglens.

7. Phase 4 — Smart Features & AI (target: v3.5.0 + v3.6.0)

Goal: Features that make pglens stand out vs. older tools. Schema diff and AI assistance are the two with the biggest "wow."

7.1 Schema diff & migration generator (v3.5.0)

Connect to two databases (or one DB + a baseline .sql file) and produce an idempotent migration.

Side-by-side schema viewer with diffs highlighted (added/removed/changed columns, indexes, constraints, types)
Generate SQL migration script (forward and backward)
Show migration preview with destructive operations flagged red
Optional: produce migration in Flyway / Alembic / Knex / Prisma format

This is what people currently pay Liquibase-adjacent tools for. Shipping it in an MIT open-source tool is a strong story.

7.2 Visual ERD editor (v3.5.0)

Today you have a schema viewer. Make it editable:

Drag a column from one table to another to create a FK
Right-click a table to add a column
Edit column types, names, defaults, constraints in a panel
Generate the DDL diff and review before applying
Auto-layout button (force-directed)

7.3 JSONB explorer (v3.5.0)

JSONB is one of Postgres's killer features and most tools handle it badly.

Schema inference: sample N rows of a JSONB column, infer the keys and their types
Path builder UI: click a key to add column->>'key' to the filter or projection
JSON tree view in cell content popup with expand/collapse
Generated @> / ? / #>> queries

7.4 Extensions panel (v3.5.0)

A page listing common Postgres extensions (pg_trgm, pgvector, postgis, pg_stat_statements, pgcrypto, uuid-ossp, citext, hstore). Each shows:

Installed status
Version available vs. installed
One-click install (CREATE EXTENSION IF NOT EXISTS ...) with permission check

7.5 Chart panel (v3.5.0)

Click "Chart" on any query result or no-code view:

Auto-suggest 2–3 chart types based on column types
Time series (date + numeric), bar (categorical + numeric), scatter (numeric + numeric)
Chart library: Recharts or Visx
Save chart as part of a view

7.6 AI mode — schema-aware NL→SQL (v3.6.0)

BYOK: user provides their own Anthropic / OpenAI API key, stored in the keychain
Local model option via Ollama for users who can't send data to a cloud
Prompt grounded on: schema, sample rows (3–5 per table), recent query history, current view's filter
UI: a chat box in the sidebar. "Show me orders from last week that weren't shipped." → SQL → result → option to "Save as view"
The generated SQL is always visible and editable before running
All AI-generated queries are read-only unless the user explicitly enables write access

7.7 Query explainer (v3.6.0)

Paste a slow query → get a plain-English breakdown:

"This query is doing a sequential scan on orders (12M rows) because there's no index on customer_id. The join with users is using a hash join, which is fine here. Consider..."
Built on the EXPLAIN visualizer plus an LLM call with the plan as context

7.8 Definition of done for Phase 4

A user can diff two databases and generate a migration.
A user can ask "show me X" in plain English and get a working SQL query.
ERD is editable, not just viewable.

8. Phase 5 — Team Mode & Production Polish (target: v4.0.0)

Goal: Open the path to (a) charging for a Pro/Team tier, (b) being adopted inside companies, not just individual developers.

8.1 Team mode (self-hosted)

A new deployment mode: pglens server --team runs pglens as a multi-user web service, not a localhost daemon.

User accounts (email + password, or SSO via OIDC)
Shared connections — admin defines connection metadata, users authenticate against the DB with their own credentials (or a shared service account)
Shared views, saved queries, charts
RBAC: per-user read/write toggle per connection
Audit log — every executed statement, who ran it, when, against which connection. This is non-negotiable for enterprise.

8.2 Connectivity primitives

SSH tunnel (via ssh2) — host, port, user, key file, optional jumphost
IAM auth for AWS RDS, GCP Cloud SQL, Azure Database — short-lived tokens fetched per-connection
Cloud connection presets — paste an RDS endpoint, pglens recognizes it and prompts for IAM
Read replica awareness — mark a connection as read-only, block writes in the UI

8.3 Plugin API

A small JS plugin API for community extensions:

Custom cell renderers (e.g., render image/png columns as thumbnails, GeoJSON columns on a map)
Custom export formats
Per-table action buttons
Plugins distributed as npm packages, loaded from a config file

Mirrors the Claude Code plugin pattern: low-ceremony, source-available.

8.4 Polish

Responsive layout for tablet (full mobile is not a priority)
Keyboard-shortcut overlay (? to show)
Onboarding tour for first-time users
Telemetry (opt-in, anonymous) for feature usage so future prioritization is data-driven
i18n scaffolding (no translations shipped, just the infrastructure)

8.5 Definition of done for 4.0.0

A small team can self-host pglens, share connections, and audit usage.
The pglens binary supports both start (single-user, current behavior) and server (team mode).

9. Phase 6 — Multi-Engine (target: v4.x, when Postgres is won)

Do not start this until Phase 4 ships. Multi-engine before the Postgres experience is best-in-class will dilute the wedge and double the maintenance surface.

Order of engines (by demand, by similarity to Postgres):

SQLite — easiest, no server, huge audience
MySQL / MariaDB — biggest market, most-requested
DuckDB — fast-growing analytics audience, similar dialect
ClickHouse — analytics, growing
MS SQL / Oracle — enterprise, only if commercial demand justifies it

Architecture: Introduce a dialect-abstraction layer in src/db/dialects/. Each dialect implements:

Introspection (tables, columns, indexes, FKs)
Pagination strategy
Quoting rules
Type mapping
Live ops queries (engine-specific equivalents of pg_stat_*)

Frontend features that are Postgres-specific (EXPLAIN visualizer style, JSONB explorer) gracefully degrade or hide for other engines.

10. Version Summary

Version	Phase
3.0.0	Foundation
3.1.0	No-code editing
3.2.0	Advanced mode
3.3.0	Live ops dashboard
3.4.0	EXPLAIN + index assistant
3.5.0	Schema diff + ERD + JSONB + charts
3.6.0	AI mode
4.0.0	Team mode + connectivity

Multi-engine (Phase 6) is open-ended and engine-dependent.

11. Risks & Decisions to Make Now

Risks

Frontend migration is the gating dependency. If Phase 0 slips, everything slips. Use the strangler-fig pattern, ship weekly, don't let it become a six-month rewrite.
Raw SQL endpoint + plaintext secrets is a CVE-shaped hole. Fix it in Phase 0, before any marketing push that increases install count.
Scope creep on AI mode. Resist the urge to make pglens an "AI database tool." AI is a feature, not the product. Schema-aware NL→SQL is in scope; agentic workflows are not.
Solo bus factor. Aggressively merge community PRs once Phase 1 ships and the project gets attention. Pin a "good first issue" label list.

Decisions to make before starting Phase 0

Component library: shadcn/ui vs. Radix + custom vs. Mantine. Recommendation: shadcn/ui.
State manager: TanStack Query + Zustand vs. Redux Toolkit. Recommendation: the former.
License going forward: Stay MIT, or move new features to a dual license (MIT + commercial for team mode)? Recommendation: keep MIT through 3.x; consider BSL or dual-license at 4.0 if team mode ships and you want a commercial tier.
Telemetry: Opt-in only, anonymous, self-hostable endpoint. Decide the data model in Phase 0 so it's available from 3.0.0.
Brand: Domain pglens.org already exists. Worth investing in a proper landing page in Phase 0 timed with the 3.0.0 launch.

Decisions deferrable to later

Pricing model for team mode (decide during Phase 5)
Whether to ship a desktop wrapper again (Electron was removed in Feb 2026; reconsider if user demand returns)
Cloud-hosted pglens — not on the roadmap, but worth revisiting at 4.x

12. Out of Scope (explicitly)

So future PRs and feature requests have something to point at:

Building dashboards / apps on top of the database. That's Retool / Forest Admin / Internal. pglens is a database tool.
Database administration features that require superuser (creating databases, managing users, replication setup). pgAdmin owns this niche; we don't try.
NoSQL databases (Mongo, DynamoDB, Redis). Different mental model, different tool.
GUI for database design from scratch (à la draw.io for databases). The ERD editor in Phase 4 is "edit what's in your DB," not "design a DB before it exists."
Mobile-first UI. Tablet is fine; phone is not the use case.

Last updated: May 2026.