A "Worksheets for Everything" vision

[Sean Colsen]

Hey team,

In preparation for our design session meeting tomorrow, I want to explain a bit more about a vision of mine. I alluded to this vision during our last session so I want to flesh it out a bit.

To start: Ghislaine and I had a 1:1 today where we talked about some of this, and she mentioned another product which has a concept of a “worksheet”. I’ve been searching for a word like this! To me, “worksheet” perfectly captures what I’m after, so I’ll use that word in this email.

In my vision, users would do most of their work through worksheets. Worksheets would replace our table page, exploration pages, column display options, and table metadata — and would lay a powerful foundation for building more elaborate visualizations and interfaces.

Here’s how worksheets would work:

• For the data model, each worksheet would have:

  • A query object having:
    • A “language” string, e.g. “sql”
    • A “definition” field e.g. select * from authors;.
  • A display object having:
    • A “type” string, e.g. “sheet”, “pie_chart”, “calendar”
    • A “settings” object containing arbitrary settings for the specific display type.
  • A parameters array for parameterized worksheets (which I’ll gloss over for the sake of brevity)

• Navigation

  • Mathesar would have “New Worksheet Page” where users can build a worksheet from scratch. In some ways this would be similar to our current Data Explorer. However, the entire worksheet state would be serialized into the URL as the user modifies the worksheet definition.

  • Users would be able to save worksheets privately to their user account and also share them with other Mathesar users within their workspace. Saved worksheets would get a name, description, and a dedicated route.

  • Mathesar would still have a Schema Page which shows a list of tables. Instead of pointing to a “Table Page”, each table in the list would link to a “New Worksheet Page” with a query that selects all columns and a display of type “sheet”. This would feel a lot like our current Table Page but would be a lot more powerful.

• Worksheets would be able to modify data in the following ways (ordered from least consequential to most):

  • Temporary data changes: Worksheets would have the ability to store and modify temporary data like the state of the user’s selection. This would only persist in the browser tab — nowhere else.

  • Worksheet definition changes: Worksheets could modify their own definitions. For example, on a “sheet” type worksheet, a user could interact with a “Filters” UI to apply a filter (much like on our Table Page). That action would alter the query definition within the worksheet, forcing Mathesar to re-serialize the entire worksheet definition and store it in URL — even for previously saved worksheets. This way if a worksheet has unsaved changes to its definition, the user could still (say) send the URL to a colleague or bookmark it. As another example, if a user resizes a column within a sheet, re-orders columns, or adjusts the formatting for a number, those changes would be persisted in the display settings of the worksheet.

  • DML changes: Worksheets would have the ability to modify cells, add records, and delete records. Mathesar would be smart enough to statically analyze the SQL and construct the appropriate mechanisms to update data in most cases. I see this as perhaps one of the most challenging aspects of my worksheets vision. “Connecting the dots” between a cell in the query output and a cell in the database is either tricky, really tricky, or impossible. But in the cases where it matters, I do think it’s possible. And if we can just get part of the way there we’d be able to offer something tremendously powerful. I would imagine building (or finding) a low-level library for this. That work is something I’d be very interested personally in doing!

  • DDL changes: Worksheets would also be able to modify the database schema. This would lead to some tricky UX questions like “do you want to rename the column in the underlying table or alias the column within the worksheet definition?” — but I think those questions are solvable. Also: some DDL changes wouldn’t fit well within the worksheet interface, so I would imagine Mathesar having a separate “Structure Editor” page that prioritizes DDL changes and alleviates some of the UX design pressure on the worksheet, especially at first.

• The query editor

  • An SQL editor would be straightforward to implement, and I would be inclined to prioritize that.
  • Within the worksheets framework I’ve outlined, we could also support alternate query editors, each with their own UI. This would allow us to have a query builder akin the the UI within our current Data Explorer. As an aside: my personal opinion is that I don’t think we’ll ever be able to build a good form-based query editor, so I don’t think it’s worth trying. But if we do choose to continue down this path, it would still fit within my broader worksheets vision.

• The “sheet” display type

  • We’d start with this as the only display type.
  • We’d design it to be a combination of our Table Page and Exploration Page, offering as much functionality from both pages as possible.
  • There would certainly be some UX challenges here. I don’t mean to hand-waive any of this away. We’d need to answer a lot of smaller questions. But I think it’s doable. I have lots of ideas here, but this email is more to lay out my vision for the broader scaffolding than to get into these finer points.

• Other cool things

  • I also imagine having the ability to set (and modify) a “default worksheet” for each table in a workspace. This would effectively replace table and column display options.
  • Worksheet display types would allow us to build new functionality in a self-contained manner that gives users power through modularity. This could eventually become a path to Mathesar “extensions” or similar. For example, an administrator of a Mathesar-based issue tracker could bundle up a couple Svelte components into a custom Mathesar extension which adds an “Issue list” worksheet display type. Then a user could select this display type for a list of issues and see something a lot like GitHub shows.
  • Parameterized worksheets could be configured and selected in order to customize the interface of linked records on the Record Page. They would also be useful for building reports that people want to re-run with various parameters at different times.

​