Structured loading: Universal data access and interaction

Features of Structured Loading

Here are some of structured loading’s high-level features:

• Editable Lists: If you can edit something in a form, then why not also in a list? We continue to work towards universal data entry.
• Aggregated Data: Users can configure grouping and aggregation calculations to produce statistics and draw charts.
• Naming System: We provide a standard “id to name” mapping system based on our modelling, usable on forms, lists, and even supporting external copy-paste.
• Column and Filter Discovery: The UI automatically shows all columns available for a list as well as their filter options.
• Virtual Columns: These provide data that isn’t available as a single database column, but result from a subquery.
• Data Relations: For entities that point to other entities, such as an Invoice pointing to a Customer, data from that transitive entity can be added to the same listing. This is recursive, to any depth.
• Equation Columns: The user can build equations combining the results of other columns. This works on aggregated data and supports sorting and filtering.
• Type Safety: Our data format is highly expressive and we ensure it’s valid. From simple constraints on numeric fields, to typing and validation of JSON blobs on the database.
• Security: Not all users are allowed to see all entities, nor all fields in those entities.

Basic Operations with a Store

The core piece of data modelling is called a store. We define these for each of our entity types in the system. For example, lab requests have a store, projects have a store, experiments have a store, and so on. Each store maps onto a database table, and at a minimum describes each column in the table.

For example, the store for the lab request will define fields such as below:

LabRequest:
	table: lab_request
	fields:
		name:
			column: name
			value_type: text
			name: Name
		lab:
			column: lab_id
			value_type: id
			id_source: entity/lab
			name: Lab
		dueDate:
			column: due_date
			value_type: date
			name: Due Date

From this bit of information, we can build most of the features of the system. For example, you can see how, if the user is in the Lab Request page, we can present a list of columns from this table. Since we have the database table and columns, we can generate the SQL needed to load this data. Given the high-level value_type, we can also present a form field to the user, and then store it to the table if they enter it. We also know which types of filters we can support on these fields, and present a selection to the user.

In the simplest case, the stores express columns that are present in the database model directly to the user. However, there are also cases where the user should see only a derived version of the underlying database model. For example, users can “watch” lab requests, meaning they get notices about them. This is stored in a separate table and has a filtered join condition. A user may want to see who is watching a lab request in the list of lab requests, but more importantly, filter to lab requests they themselves are watching. Some of these columns were difficult to model directly, so we introduced custom loaders. The store entry for this might look like below:

   watchers:
       type: ExtraColumn
       name: Watchers
       loader: LabRequestWatchers
       value_type: ids

Where LabRequestWatchers is a Python class that implements the particulars of the query. However, there is a tradeoff here. By not making the full underlying database model accessible, in the future you may need to build even more ExtraColumns to satisfy user needs. We’re finding the more we expose, the easier it is to scale the application. It doesn’t have to be direct exposure, instead, we try to build generic semantic concepts. For example, we’ve recently added ORM style backlinks, where you can join across stores via indirect field references.

Naming and linking entities

The naming system is a central part of our application. It’s a mechanism to give all entities names when the user sees them. Beyond that, it’s used as the basis for data relationships, allowing navigation between entities, and drilling down for information.

The example has a field called lab with a value type of id. This is a typical foreign key on the database. The lab request table’s lab_id contains the primary key of another table, lab in this case. When a user sees this field, they don’t want to see the id, they want to see the name of the lab. This applies both in the static view in a list and while editing in a form.

The id_source for a field tells us what entity type the id refers to. In the example, the entity/lab source tells us that the lab field refers to the lab entity.

In brief, the id-source system provides this functionality:

• Display Name: In a list, a user wants to see the name of the entity, rather than its id.
• Selection: While editing a field, we can use this to show a list of names to select from, as well as provide search functionality. We also offer a filtering mechanism for this selection.
• Search: In a list, the general text search can search over the names of these linked entities.
• Data Relations: This builds the relations between tables, allowing a user to look into linked entities.

The id-source system existed prior to structured loading, but the two have grown to be sibling systems, with tightly interwoven functionality. The structured loading system replaced much of what us used to write manually for the id-source system. Now, naming is automatic as long as we follow a few rules about “essential fields” in the stores.

The id-source system has solved a lot of challenges in our application.

Backing forms and Stores as their own System

Our system already had generic forms. We incorporated this system into structured loading. User defined fields were available in lists, just the same as all the hard-coded store fields. Then, we added the ability to show lists inside forms, as well as pull out columns from lists and put them as individual fields in some entry systems.

The ability to edit a value in a list was initially driven by the system backing our forms. But at some point we realized the stores had all the information we needed. So we rewrote several of the middle pieces of the software, so the form system was backed by the stores. The boundaries between the systems blurred, allowing us to remove a lot of redundant code.

We eventually took the logical step of treating stores as a separate concept from the list system. They now form the basis to structured loading and the form system. The push to universal systems continues to expose new features and reduces the surface area of our code.

An Evolving System

The structured loading system is an actively developed part of our architecture. We continue to find new ways to push its boundaries and help our customers to get more value out of their data. We’ve created a system where a small bit of data modelling exposes a rich set of data management and exploration tools.

As a unique system, new programmers need to familiarize themselves with it when they join the team. Once learned, it saves us countless hours of development. Instead of worrying about basic data management and access APIs, we can focus on the higher-level business functions of the code.

It’s obviously a complex system with a long history. I will follow up with articles about specific aspects and sub-systems, as well as non-functional aspects that I find interesting. There’s so much more to uncover, such as query generation and the optimizations that go well beyond what the database itself can do.