trellispark DAS-RSS is implemented using MS SQL Server as a hybrid Relational/Document database. The database sits behind the trellispark Core API so that all of the solution components have direct access to the stored records.
Using an interface API for trellispark enables horizontal database scaling and advanced deployment scenarios.
Using a MS SQL Server database makes it easy to share data for Dashboards, Reports, AI/ML, or utilize any workflow technology
The latest version of the database and interface API is included in each Release of trellispark which can be downloaded from the GreatIdeaz Portal.
Most of the database objects are open for inspection, including all objects that are created by Code Generation.
The core database objects that are required to operate trellispark are all prefixed with GI and are encrypted. This is because we do not recommend modifying any of this code unless you have spoken to us first and fully understand the consequences of your proposed changes.
Link to the coding standards used by trellispark for database objects.
Definition of key terms – Record, Instance, Concept
- A Record is defined a specific Instance of a Concept (or Entity)
- A Concept defines a class of things we store information about
- Example: Vehicle is a Concept
- Concepts will have one or more data elements (attributes. Columns)
- Example: Vehicles have: Make, Model, Year, Seating Capacity, …
- An Instance is a specific single example of a Concept
- Example: My vehicle has: Make=Toyota, Model=RAV4, …
- In relational databases we store Records in Tables with data elements in Columns
- Every Concept gets its own Table and Stored Procedures
- Relational database design enforces relationships between concepts
trellispark’s hybrid approach
- Hybrid approach combining document and relational database principles
- Records are stored as “documents” using a data agnostic XML or JSON format
- Allows for both traditional “simple” and “complex” database field types
- Reduces maintenance of tables, columns and relationships
- Only need one Instances table that includes metadata describing the Record
- Unique Record Identifier
- Type of data being stored
- Key relationships between Records (Parent-Child Hierarchy)
- The record data (XML/JSON)
trellispark uses a CQRS approach to creating, maintaining and querying data.
- An architecture pattern that separates read and update operations for a data store
- Improves performance, scalability and security
- In Data Agnostic Services and trellispark
- Command is simply “SaveInstance” or “DeleteInstance”
- May trigger updates to read only lookup tables (minimum set for optimal performance)
- Query is optimized search over the indexed lookup tables
- Access to a specific record is simply “ReadInstance”
- Command is simply “SaveInstance” or “DeleteInstance”
- The Command side gives the benefits of a document database
- The Query side fully utilizes the relational properties of the database
Core Tables
trellispark uses the following core tables to maintain your records:
- Instances – contains the latest version of all active record
- InstanceHistory – contains all versions of all active records
- InstanceAccess – contains all of the row-level security access rights that controls which users/groups can see which records.
Records are added to the Instances table using T-SQL Stored Procedures GI_SaveInstance and retrieved using GI_ReadInstance. These procedures are performing many functions including security and versioning.
As records are added to the Instances table, they are inserted into a record hierarchy based on a simple parent-child relationship.
The XML Manager API can be used to extract all of the records in a hierarchy irrespective of type into a structured XML document. This document can be further enriched with other records from the database, or other data sources. This technology is used to cache complete Form Definitions to optimize page rendering at runtime. trellispark also uses the XML Manager in combination with XSLT to create complex documents.
The Instances table can also be used to “lock” a complete record hierarchy when it is necessary to perform a complex workflow operation.
Deleted Tables
When you delete a record in trellispark it is not immediately removed from the database. Deleted records are moved to a corresponding set of tables:
- DeleteRequestValidation – used to track which records are encompassed by a delete request and manage the request’s validation. This link outlines the record deletion and restoration process.
- DeletedInstances – all of the record instances which have been deleted grouped by request
- DeletedInstanceHistory – the history of all deleted records
- DeletedInstanceAccess – the access rights of all delete records
This allows you to quickly restore any deleted records if a mistake is made.
When you delete a record, all of the descendent (child) records are also removed at the same time as a set. If your user doesn’t have permission to delete any record in the set, then the whole delete operation is failed and no records are removed.
This virtual deletion approach maintains an audit trail which may be required for sensitive data sets.
Security Tables
trellispark maintains a list of active user sessions in a table called ActiveSessions. Any attempt to access records in the DAS-RSS is first checked to ensure that the request has a valid SessionGUID/UserGUID combination, and that the session has not timed out.
The user’s access rights are stored in the following tables:
- UserApps – which apps the user has access to
- UserGroups – which groups the user belongs to
- UserPortals – which portals the user belongs to – typically zero or one
- UserRoles – which roles the user has
The user’s groups determine which records the user can see when combined with the InstanceAccess table.
The user’s apps provide access to user experience forms that act as entry points to access a set of records. Roles provide a fine granularity of control over what the user can do with those records.
Querying Data
The records in the Instances table store their data as XML fragments. To make it easy for developers to access and query that information trellispark implements a simple Code Generation technology.
The trellispark code generator uses the user experience Form Definitions to create the following database objects for all forms that are used to define Concepts. Where a Concept defines a type of record that needs to be stored and displayed.
- A View that shreds the XML fragment into a set of columns that would be familiar to any relational database developer
- A Get T-SQL Stored Procedure that can be used to read a set of records of the specified concept that are children of a specified parent.
For concepts that will be frequently queried, the UX Creator provides an option to create some additional database objects used to optimize search performance.
- A read-only lookup table that contains a subset of the XML elements of records of the specified concept.
- A pair of T-SQL Stored Procedures that are called by a trigger on the Instances table to automatically refresh the concept’s lookup table as records are added, updated, removed from the Instances table.
The minimal overhead of maintaining these lookup tables is overshadowed by the performance gain of having fully optimized lookup tables.
Incoming Bulk Data Updates
trellispark implements a bulk data update process to apply consistent standards to the ingestion and validation of incoming bulk data.
The trellispark code generator uses the UX Creator Functionality Form Definitions to determine how to import bulk data into the DAS-RSS record storage.
Depending upon the Form Definition, some custom code may need to be added to the code generated T-SQL Stored Procedures.
Event Logging
The following tables are used to log events within the DAS-RSS:
- EventLog – global event log used mainly for error handling
- InstanceEvents/DeletedInstanceEvents – tracks events against a specific record instance used, mainly for tracking user interactions with the instance
- InstanceMilestones/DeletedInstanceMilestones – tracks milestones for a specific record instance, used mainly for tracking life-cycle state changes
- WorkspaceEntry – tracks when users enter a workspace
XML Manager
To optimize solution performance, it is useful to persist complete XML documents for some concepts. The table DeepXML is used to store an exploded and enriched XML document for a record instance.
This mechanism is used to cache complete Form Definitions to optimize the performance of the Blazor dynamic page builder components resident in the Core API.
Sustainability Considerations
- Add or remove fields – it’s just XML!
- Add or remove whole Concepts
- Restructure how Concepts inter-relate
- Reuse existing Concepts in multiple places
- Add new functionality to further extend CRUD+
- Change underlying database storage technology if more effective and efficient solutions become available
- The efficiency of the underlying architecture as means you need less infrastructure to host your record storage
