Admin from a spreadsheet (P19)
Tabula is an admin panel that turns a business's spreadsheets into a shared workspace. The sample data is a wholesaler's orders and products in an Excel file and its customers in a CSV file saved by a Danish version of Excel.
Reading the files
Tabula reads the files in your browser and sends nothing to the server until you create the workspace. A CSV file is read as UTF-8 when every byte is valid UTF-8, and as Windows-1252 otherwise. The separator is the character that splits the first 200 lines into the same number of fields: a comma, a semicolon, a tab or a vertical bar. An Excel file is a zip archive of XML files, and Tabula reads it without a library.
Tabula gives each column the first type that fits at least 90 % of its values. The types are yes or no, date, code, number, amount, email, phone number, a fixed list of values and text. A number with a leading zero is kept as a code. The decimal separator comes from values that can only be read one way. The order of day and month comes from dates where the first part is above 12. Values that do not fit the column's type are listed with their row numbers.
A key is a column where every value is filled in and unique. A column in another sheet links to a key when its name matches the sheet or the key. At least 90 % of its values must also appear in the key. Rows that link to a missing row are listed.
Roles on the server
Anna owns the workspace, Ben is an editor and Carla is a viewer, and each has a personal link. Each role has rules for the rows it can see, the columns hidden from it, and the rows and columns it can edit. The server applies the rules to every response and every change. A hidden column is left out of every response, log entry and live update that the role receives.
Changes and the log
Each change is sent with the value it replaces, and the server applies it only if that value is still in the cell. If someone else changed the cell first, the server rejects the change and returns the current value and who wrote it. Each change is added to a log, and each log entry contains a SHA-256 hash of the entry before it. The owner's browser checks these hashes, so an entry edited directly in the database shows as broken from that entry on. You can undo any entry with a new entry, and view the workspace as it was before any entry.
A million rows
In the browser, a Web Worker stores each sheet column by column. Numbers and dates are stored in typed arrays, and text is stored as references to a list of unique values. A test runs 1,000 random queries through this engine and through the design system's query engine and gets the same rows in the same order. The page with 1,000,000 rows measures eight queries in your browser.
Previous: Connections (P22). Next: Invoices read by AI (P20).