Collaborative SQL Notebook (Real-Time)
Collaborative SQL Notebook: a Jupyter-like Real-time Workspace
Data work has always been collaborative in spirit but solitary in practice. A data scientist explores a dataset alone, writing queries and interpreting results in isolation. When collaboration is needed, it happens through screen-sharing, file-sharing, or—worst of all—copying queries into Slack and pasting results back. These workarounds are clumsy, lossy, and slow. They reduce collaboration to observation rather than participation, and they discard the context that makes analysis reproducible.
The Collaborative SQL Notebook reimagines what collaborative data work could be. It is a Jupyter-like environment where multiple users write SQL and Python cells together in real time—not taking turns, not observing, but genuinely editing the same notebook simultaneously. Changes synchronize via CRDTs (Conflict-free Replicated Data Types), queries execute against a shared PostgreSQL instance, and results are visible to every collaborator instantly. Chat, version history, and experimental branching round out the experience, making the notebook a true collaborative workspace rather than a shared document.
The result is pair-programming for data teams: a shared space where ideas develop in real time, queries evolve through collective insight, and the friction of remote collaboration disappears.
What It Does
The notebook presents a familiar interface—cells containing code, executed in sequence, with outputs displayed beneath. But where a traditional notebook is a single-user document, this one is a shared, live workspace.
Real-time collaborative editing: Multiple users edit the same notebook simultaneously. When one person types, others see the change immediately. When someone adds a cell, it appears for everyone. When a query executes, all collaborators see the results. There is no "save and share"—the document is continuously synchronized.
SQL and Python cells: The notebook supports both SQL and Python cells. SQL cells execute against a shared PostgreSQL instance, with results rendered as tables or charts. Python cells run client-side via Pyodide, enabling data manipulation, visualization, and analysis without server round-trips.
Shared execution context: All collaborators share the same database connection and the same Python runtime state. A table created by one user is visible to all. A variable defined in one Python cell is accessible in another—regardless of who wrote it.
Chat and comments: A side panel provides chat for discussion, and individual cells can carry comments for asynchronous feedback. Conversation and computation live in the same space, eliminating the context-switching that plagues distributed teams.
Version history: Every change is tracked, with the ability to view the notebook at any point in its history. This is not just undo—it is a complete audit trail of how the analysis developed.
Branching: Users can "branch" a notebook, creating an independent copy for experimentation. Branches can be merged back, or they can remain separate explorations. This mirrors version control for code, applied to the exploratory work of data analysis.
Architecture and Data Flow
Real-Time Sync with Yjs CRDTs
The foundation of real-time collaboration is a conflict-free replicated data type (CRDT) implementation, specifically Yjs. CRDTs solve the fundamental problem of collaborative editing: how do you merge concurrent changes from multiple users without conflicts?
The traditional answer is operational transformation (OT), which requires a central server to sequence operations. CRDTs take a different approach: they define data structures where operations commute—the order in which changes are applied does not affect the final state. This allows changes to be applied locally and synchronized asynchronously, with convergence guaranteed.
Yjs is a mature, high-performance CRDT library that provides shared data types: Y.Text for text, Y.Array for lists, Y.Map for key-value structures. The notebook's document model maps naturally to these types:
- The notebook itself is a Y.Array of cells
- Each cell's source code is a Y.Text
- Cell metadata (type, execution state) is a Y.Map
- Outputs are stored as Y.Map entries keyed by cell ID
When a user types in a cell, the change is applied to the local Y.Text. Yjs computes a minimal delta and broadcasts it to other clients, who apply it to their own copies. Because the underlying data structure is a CRDT, concurrent edits—even edits to the same cell—converge without conflict.
The synchronization protocol runs over WebSockets. FastAPI maintains WebSocket connections for each collaborator, relaying Yjs updates between them. The server also persists the document state, using Yjs's binary encoding for efficient storage and retrieval.
Query Execution Against Shared PostgreSQL
Queries execute against a shared PostgreSQL instance, ensuring that all collaborators see the same data. When a user runs a SQL cell, the query is sent to the server, executed via SQLAlchemy, and the results are broadcast to all collaborators.
This shared execution model has important implications:
Consistency: Everyone sees the same results because everyone queries the same database. There is no risk of collaborators working with different data snapshots.
Isolation: Notebook sessions can be scoped to specific schemas or databases, preventing cross-session interference. Within a session, however, all users share the same database state—a table created by one user is visible to all.
Concurrency: Multiple queries can execute simultaneously, but write operations may conflict. The notebook should provide mechanisms for coordinating writes—transactions, locks, or advisory locks—to prevent race conditions.
Safety: Because multiple users share a database, queries that modify data must be carefully managed. Read-only mode for exploratory sessions, or role-based permissions for write access, prevent accidental damage.
The execution flow is:
- User submits a SQL cell
- The cell's execution state is updated to "running" and broadcast to all collaborators
- The query is sent to the server via WebSocket
- The server executes the query via SQLAlchemy
- Results are broadcast to all collaborators
- The cell's execution state is updated to "complete" with the results
This flow ensures that everyone sees the same execution state and the same results, creating a shared experience even though only one user initiated the query.
Client-Side Python with Pyodide
Python cells execute client-side via Pyodide, a Python runtime compiled to WebAssembly. This brings the scientific Python stack—NumPy, Pandas, Matplotlib—into the browser, enabling data manipulation and visualization without server round-trips.
Pyodide's role in the collaborative notebook is nuanced. Because Python executes in each user's browser, the Python state is not inherently shared. A variable defined by one user exists only in their browser, not in others'. This is a fundamental difference from SQL cells, where execution is centralized.
Several approaches address this:
Shared Python state via CRDTs: Variables could be synchronized through Yjs, with each user's Python runtime receiving updates. This is complex—Python objects must be serialized, transferred, and deserialized—but it enables true shared Python state.
Server-side Python execution: Python cells could execute on the server, with results broadcast to all collaborators. This provides shared state at the cost of server resources and latency.
Hybrid approach: Simple Python cells run client-side for speed; cells that need shared state run server-side. Users choose the execution mode based on their needs.
The choice depends on the use case. For teaching and exploration, client-side Python with independent state may be sufficient—each student runs their own analysis. For collaborative data science, shared state is essential. The notebook should support both models.
Jupyter Kernel Protocol
For compatibility with the broader Jupyter ecosystem, the notebook can implement the Jupyter kernel protocol. This protocol defines how frontends communicate with kernels—the processes that execute code and return results. It supports:
- Execute requests: Run code and return outputs
- Completion requests: Provide autocomplete suggestions
- Inspection requests: Return documentation for objects
- Kernel info: Report language, version, and capabilities
Implementing the kernel protocol allows the notebook to use existing Jupyter kernels—IPython, IRkernel, and others—broadening the languages and tools available. It also allows the notebook to integrate with Jupyter's ecosystem of extensions and tools.
Use Cases and Applications
Pair-Programming for Data Teams
The most immediate use case is pair-programming—two or more developers working together on the same analysis. Unlike screen-sharing, where one person drives and others watch, collaborative editing allows everyone to contribute simultaneously. One person writes a query while another examines results; one explores a hypothesis while another checks the data. The collaboration is genuine, not observational.
For remote teams, this is transformative. The friction of distributed work—the delays, the miscommunications, the lost context—dissolves when everyone is in the same document, seeing the same results, in real time.
Data Science Interviews
Technical interviews for data roles often involve live coding, but the logistics are awkward. Screen-sharing is one-directional; the interviewer cannot easily contribute. Collaborative notebooks solve this: interviewer and candidate share a notebook, both can write and execute code, and the conversation flows naturally between discussion and experimentation.
The version history provides a record of the interview for later review, and the branching feature allows candidates to explore alternatives without losing their primary approach.
Teaching and Workshops
In educational settings, collaborative notebooks enable a new kind of instruction. An instructor can write a query, and students see it appear in real time. Students can experiment in their own branches, then merge their work back. The chat panel provides a space for questions, and the version history shows how understanding developed over the session.
For workshops, this is particularly valuable. Participants work through exercises together, with the instructor able to see progress, offer guidance, and share solutions—all within the same document.
Collaborative Data Exploration
Beyond structured collaboration, the notebook supports ad hoc exploration. A team investigating a data anomaly can work together, each pursuing different hypotheses, sharing findings through cells and chat. The shared database ensures everyone is looking at the same data, and the shared notebook ensures everyone sees the same results.
Reproducible Analysis
The version history and branching features support reproducibility. An analysis can be traced through its development, with each change recorded. Branches show alternative approaches that were explored and abandoned. The notebook becomes not just a record of the final analysis, but a record of how it was developed.
Modern Technology Stack
Yjs (CRDT)
Yjs provides the CRDT foundation for real-time collaboration. It is mature, performant, and widely used in collaborative applications. Its binary encoding minimizes network traffic, and its awareness protocol tracks user presence—who is online, where their cursor is, what they are selecting.
FastAPI + WebSockets
FastAPI handles the server side: WebSocket connections for real-time sync, HTTP endpoints for query execution and notebook management, and authentication for access control. Its async architecture handles many concurrent connections efficiently, essential for collaborative applications.
PostgreSQL
PostgreSQL provides the shared database against which queries execute. Its robustness, feature set, and ecosystem make it the natural choice for data analysis. Row-level security, schemas, and roles provide mechanisms for managing access and isolation.
SQLAlchemy
SQLAlchemy provides the ORM and query execution layer. It handles connection pooling, query construction, and result serialization, abstracting the details of database interaction from the notebook's core logic.
Pyodide
Pyodide brings Python to the browser, enabling client-side execution of Python cells. It includes NumPy, Pandas, and other scientific libraries, making it suitable for data manipulation and analysis. Its integration with JavaScript allows Python code to interact with the notebook's UI and data.
Jupyter Kernel Protocol
The Jupyter kernel protocol provides compatibility with the broader Jupyter ecosystem. Implementing it allows the notebook to use existing kernels and tools, extending its capabilities without reinventing infrastructure.
User Benefits
Pair-Programming for Data Teams
The primary benefit is genuine collaboration. Data teams can work together in real time, sharing not just a screen but a workspace. Ideas develop through collective effort, with each person contributing simultaneously. The friction of remote collaboration—the delays, the turn-taking, the lost context—disappears.
Replaces Screen-Sharing
Screen-sharing is a one-way medium. One person drives; others watch. Collaborative notebooks make everyone a driver. This is more engaging, more productive, and more respectful of each participant's expertise.
Ideal for Remote Teams
For distributed teams, the notebook provides a shared space that approximates co-location. Everyone sees the same document, the same results, the same conversation. The physical distance matters less when the digital workspace is truly shared.
Supports Interviews and Teaching
The notebook's collaborative features make it ideal for interviews and teaching. Interviewers can contribute to the candidate's work, not just observe. Instructors can guide students in real time, not just lecture. The experience is interactive and engaging.
Version History and Branching
The version history and branching features bring version control to exploratory analysis. Analyses can be traced, alternatives explored, and findings reproduced. This is valuable for auditing, for learning from past work, and for building on previous analyses.
Conclusion
The Collaborative SQL Notebook represents a genuine advance in how data teams work together. By combining Yjs CRDTs for real-time sync, FastAPI and WebSockets for communication, PostgreSQL for shared data, Pyodide for client-side Python, and the Jupyter kernel protocol for ecosystem compatibility, it creates a workspace where collaboration is native rather than bolted on.
The technology stack is mature and well-understood. CRDTs have been refined over years of research and production use. WebSockets are standard. PostgreSQL and SQLAlchemy are staples of data infrastructure. Pyodide brings Python to the browser with impressive fidelity.
For remote data science teams, the notebook offers a way to work together that feels natural—not a compromise forced by distance, but a genuine improvement over co-located work. For interviews and teaching, it provides an interactive medium that screen-sharing cannot match. And for analysis itself, the version history and branching features bring reproducibility and experimentation to exploratory work.
In a world where data teams are increasingly distributed, tools that make collaboration seamless become essential. The Collaborative SQL Notebook is such a tool—a shared workspace for shared thinking, where data work becomes a collective endeavor.