A PTY-backed PostgreSQL console running in the browser using reverse WebSockets, Redis Streams, and xterm.js — built under NAT constraints and production realities.
We needed a real PostgreSQL terminal in the browser.
Not a SQL editor.
Not a query API.
A real psql session with full terminal semantics.
That immediately meant:
- PTY required
- stateful process required
- bidirectional streaming required
And three constraints made the architecture non-obvious:
- agents behind NAT
- xterm.js only supports WebSocket
- we cannot emulate psql
High-level architecture
This system only makes sense if you read it as a dataflow graph, not components.
Browser (xterm.js)
│
│ WebSocket (user input/output)
▼
Control Plane ───────────────────────────────────────────────┐
│ │
│ session management + auth │
│ │
▼ │
Redis Streams (PTY output buffer) │
│ │
│ pub/sub control events │
▼ │
Agent Control Channel (reverse WebSocket) │
│ │
│ writes stdin / reads stdout │
▼ │
PTY process (real psql)
Now the important part:
The agent initiates the entire connection graph.
Everything else is just message routing.
Step 1 — Browser creates a session
Browser
│
│ WebSocket connect
▼
Control Plane
├── creates session_id
├── registers browser handler
└── starts auth timeout
At this point:
- there is no agent
- no database
- no PTY Only a logical session exists.
Step 2 — Control plane triggers the agent
Control Plane
│
│ HTTP POST /terminal?session_id
▼
Agent (behind NAT)
This is intentional.
We do not open inbound connections.
Instead:
we push a signal, not a connection
The signal means:
“open a reverse WebSocket for this session”
Step 3 — Reverse WebSocket is established
Agent
│
│ WebSocket connect (outbound)
▼
Control Plane (Client Handler)
Now we have:
- browser WS → control plane
- agent WS → control plane
But they are still isolated.
The system is now in a half-connected state.
Step 4 — Session stitching
Browser Handler ───────┐
├── session_id → Redis → logical binding
Agent Handler ─────────┘
At this moment:
the control plane stops being a transport and becomes a router
It now forwards:
- browser input → agent
- agent output → browser
But not directly.
Everything goes through buffering and coordination layers.
Step 5 — PTY + psql is spawned
Agent
│
│ forkpty()
▼
PTY master fd
│
├── child → psql process
└── parent → I/O threads
From here on:
the system stops being “web architecture” and becomes “process supervision”
We now manage:
- file descriptors
- blocking reads
- backpressure
- OS signals
Step 6 — The real data pipeline
This is the most important flow in the system.
Browser
│
│ keystroke
▼
Control Plane
│
│ forward event
▼
Agent WS handler
│
│ write(fd)
▼
PTY → psql
│
│ stdout
▼
PTY reader thread
│
│ Redis XADD ←--- decoupling boundary
▼
Redis Streams
│
│ consumer (async)
▼
Control Plane
│
│ WebSocket push
▼
Browser (xterm.js)
The most important design boundary
This line:
PTY reader → Redis XADD → consumer → WebSocket
is the entire stability model.
Without it:
- WebSocket backpressure freezes psql output
- terminal becomes non-deterministic under load
With it:
- PTY is isolated from network behavior
- system becomes resilient to slow clients
Why Redis Streams are not optional
We originally tried:
PTY → WebSocket directly
This failed in production because:
- WebSocket writes block under load
- PTY reader is synchronous
- blocking IO propagates backwards
So the failure mode was:
network slowdown → frozen terminal → stuck psql session
Redis Streams break that chain:
- PTY write becomes O(1)
- network becomes asynchronous
- failure is localized
The real system is actually two loops
This is the part most designs hide.
Loop 1 — Input loop
Browser → Control Plane → Agent → PTY
Loop 2 — Output loop
PTY → Redis → Control Plane → Browser
They are completely independent.
This is why the system survives partial failure.
Why two WebSocket handlers exist
We deliberately split responsibilities:
Browser Handler:
- auth
- session lifecycle
- user events
Agent Handler:
- PTY lifecycle
- process management
- reconnect logic
Reason:
browser failures and agent failures are fundamentally different systems problems
Merging them creates coupled failure modes.
Failure model (what actually breaks)
A. Redis failure
Impact:
- output pipeline breaks
- PTY continues running
Mitigation:
- bounded memory
- retention limits
- monitoring
B. Agent disconnect
Impact:
- WS breaks
- PTY may still run
Mitigation:
- reconnect window
- session reattachment
- delayed teardown
C. Process explosion
Impact:
- memory exhaustion
- DB connection storm
Mitigation:
BoundedSemaphore(max_sessions=10)
This is the simplest and most effective safety boundary in the system.
D. xterm resize storm
Impact:
ioctl(TIOCSWINSZ) × 100/sec
Mitigation:
- 200ms debounce
- agent-side throttling
Scaling reality
Each session is not lightweight.
It includes:
- psql process
- PTY
- two threads
- Redis stream
- WS pair
- DB connection
So scaling is bounded by:
number of real database sessions you can sustain
Not by WebSockets.
Not by Redis.
Not by CPU.
Why HTTP/SSE architecture fails
We evaluated:
HTTP polling
- stateless
- no streaming
- no cancellation
- no session continuity
SSE
- one-directional
- incompatible with terminal interaction model
Conclusion:
terminals are inherently bidirectional state machines → WebSocket is the only fit
What this system really is
If you strip all abstractions:
It is a distributed process supervisor for a PTY running psql
Everything else is just transport.
Final architecture insight
The system is defined by three separations:
Connection separation
Reverse WebSocket removes NAT from the problem space.Process separation
PTY isolates PostgreSQL from the web layer.Flow separation
Redis decouples terminal I/O from network I/O.
Final mental model
If you understand only one thing, understand this:
Browser ↔ Control Plane ↔ Agent ↔ PTY ↔ psql
↑
Redis is the buffer
Everything else is failure handling around this chain.
Final thought
We didn’t build a “web UI for PostgreSQL”.
We built a distributed, fault-tolerant terminal runtime for a stateful OS process.
PostgreSQL just happened to be the process we attached to it.