feat: Implement PGlite-based platform data storage with optional ElectricSQL sync

[cevheri]

feat: Implement PGlite-based platform data storage with optional ElectricSQL sync

Overview

This issue tracks the implementation of a new platform data storage system using PGlite (in-browser PostgreSQL) with optional ElectricSQL sync to external databases. This will replace the current LocalStorage-based approach and enable user management, query logging, saved queries, and cross-device data synchronization.

Problem Statement

Current Limitations

• ❌ LocalStorage 5-10MB limit
• ❌ No user-based data separation
• ❌ No cross-device data sharing
• ❌ Data loss when browser is cleared
• ❌ No proper query history tracking per user
• ❌ No saved queries persistence

Proposed Solution

Architecture

Default Mode (Browser-Only):

PGlite (WASM PostgreSQL) → IndexedDB (100GB+ limit)
✅ Zero-config, works immediately
✅ No external database required

Optional Mode (Database Sync):

PGlite → IndexedDB → ElectricSQL → External PostgreSQL
✅ Cross-device access
✅ Data recovery even if browser cleared
✅ Collaborative features (future)

Key Features

1. User/Role Management

   • Create users with roles (admin, user, viewer)
   • User-based data isolation

2. Query Logging

   • Per-user query history
   • Track execution time, row count, status
   • Connection and tab information

3. Saved Queries

   • User-specific saved queries
   • Tags and descriptions
   • Edit and manage queries

4. Account Management

   • User preferences (JSONB)
   • Profile settings

Database Schema

Note: platform_user_passwords table is only created in external PostgreSQL when sync is enabled. It is never created in browser PGlite to ensure passwords are never stored locally.

-- Platform Users
CREATE TABLE platform_users (
    id TEXT PRIMARY KEY,
    username TEXT UNIQUE NOT NULL,
    email TEXT,
    platform_role TEXT DEFAULT 'user',  -- admin, user, viewer
    preferences JSONB DEFAULT '{}',
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

-- Platform User Passwords (only used when sync is enabled) (!!!!)
-- This table is NOT synced to browser PGlite, only exists in external PostgreSQL
-- Browser-only mode: passwords stay in localStorage (encrypted)
-- Sync mode: passwords stored in external DB via ElectricSQL
CREATE TABLE platform_user_passwords (
    user_id TEXT PRIMARY KEY REFERENCES platform_users(id) ON DELETE CASCADE,
    hashed_password TEXT NOT NULL,
    password_updated_at TEXT DEFAULT (datetime('now'))
);

-- Query History
CREATE TABLE query_logs (
    id TEXT PRIMARY KEY,
    user_id TEXT REFERENCES platform_users(id),
    connection_id TEXT,
    connection_name TEXT,
    tab_name TEXT,
    query TEXT NOT NULL,
    row_count INTEGER,
    execution_time_ms INTEGER,
    status TEXT NOT NULL,
    error_message TEXT,
    executed_at TEXT DEFAULT (datetime('now'))
);

-- Saved Queries
CREATE TABLE saved_queries (
    id TEXT PRIMARY KEY,
    user_id TEXT REFERENCES platform_users(id),
    name TEXT NOT NULL,
    description TEXT,
    query TEXT NOT NULL,
    connection_type TEXT,
    tags TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

-- Sync Config (optional)
CREATE TABLE sync_config (
    id TEXT PRIMARY KEY,
    sync_enabled INTEGER DEFAULT 0,
    sync_url TEXT,
    last_sync_at TEXT
);

Implementation Plan

Phase 1: PGlite Integration

• Set up PGlite client (singleton, lazy loading)
• Create migration system
• Refactor storage.ts to use PGlite (preserve API, make async)
• Migrate existing LocalStorage data to PGlite
• Update components to use async storage API

Phase 2: User Management

• Implement user CRUD operations
• Integrate with existing JWT auth system
• Add role-based data filtering
• Create user preferences/settings UI
• Implement password storage logic:
  • Browser-only: Keep passwords in localStorage (existing behavior)
  • Sync mode: Store passwords in external DB via platform_user_passwords table
  • Ensure platform_user_passwords table is NOT created in browser PGlite

Phase 3: ElectricSQL Sync

• Set up ElectricSQL client
• Create sync configuration UI
• Implement conflict resolution (last-write-wins)
• Add sync status indicators

API Compatibility

The existing storage.ts API will be preserved but become async:

// Before
storage.addToHistory(item);
const history = storage.getHistory();

// After
await storage.addToHistory(item);
const history = await storage.getHistory();

Minimal changes required in components (just add await).

Security Considerations

Password Storage Strategy

Browser-Only Mode:

• Passwords stored in localStorage (encrypted via crypto/vault.ts)
• platform_user_passwords table is NOT created in browser PGlite
• Zero password data in IndexedDB

Sync Mode (External DB):

• Passwords stored in external PostgreSQL via platform_user_passwords table
• ElectricSQL syncs user data but excludes password table from browser
• Passwords only exist in external database, never in browser

Data Storage Matrix

Data	Browser-Only	Sync Mode
Passwords	localStorage (encrypted)	External DB only (never in browser)
User information	PGlite	PGlite + External DB
Query history	PGlite	PGlite + External DB
Saved queries	PGlite	PGlite + External DB

Dependencies

{
  "@electric-sql/pglite": "latest"
}

File Structure

src/lib/
├── storage.ts           # Existing API preserved, PGlite backend
├── pglite/
│   ├── client.ts        # PGlite singleton
│   ├── migrations.ts    # Schema migrations
│   └── sync.ts          # ElectricSQL integration (Phase 3)
└── crypto/
    └── vault.ts         # Password encryption (localStorage)

References

• PGlite Documentation - In-browser PostgreSQL
• ElectricSQL Documentation - Postgres sync
• Backlog Document

Acceptance Criteria

• Platform works in browser-only mode without any external database
• Existing storage API is preserved (async version)
• Users can manage their query history and saved queries
• Optional sync to external PostgreSQL works seamlessly
• Data migration from LocalStorage is smooth
• Zero breaking changes to existing functionality
• All tests pass (if applicable)