Documentation

Documentation regarding various apects could be found at https://docs.google.com/document/d/1arxpuFZeH0-wSDiPeqGITXCv4QyfDtWb/edit?usp=sharing&ouid=117346421118620353373&rtpof=true&sd=true.

Database structure description

Generated automatically from database according to attributes description.

Database schema All standard ASP.Net Core Identity tables (AspNet*) are not shown here for a simplicity reason.

Tables

dbo.AspNetRoleClaims

Contains claims for roles from AspNetRoles

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
Id int Row Id (identity, primary key) Yes
RoleId int Role Id (from AspNetRoles.Id) No AspNetRoles.Id
ClaimType nvarchar(max) Name of a claim No
ClaimValue nvarchar(max) Value of a claim No

dbo.AspNetRoles

Contains roles available in the application

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
Id int RoleId Yes
Name nvarchar(512) Role Name No
NormalizedName nvarchar(512) Name in uppercase No
ConcurrencyStamp nvarchar(max) Concurrency Stamp No

dbo.AspNetUserClaims

Contains claims for users from AspNetUsers

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
Id int Row Id (identity, primary key) Yes
UserId int User Id (from AspNetUsers.Id) No AspNetUsers.Id
ClaimType nvarchar(max) Claim Name No
ClaimValue nvarchar(max) Claim value No

dbo.AspNetUserLogins

Contains external OAuth authentification providers for users

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
LoginProvider nvarchar(256) OAuth Login Provider Yes
ProviderKey nvarchar(256) Provider Key (provider-specific key for the user) Yes
ProviderDisplayName nvarchar(max) OAuth Login Provider Name (to display) No
UserId int User Id from AspNetUsers.Id No AspNetUsers.Id

dbo.AspNetUserRoles

Contains user-group relation

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
UserId int User Id from AspNetUsers.Id Yes AspNetUsers.Id
RoleId int Role Id from AspNetRoles.Id Yes AspNetRoles.Id

dbo.AspNetUsers

Contains registered users of the application (users are shared by all tenants in the same database)

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
Id int UserId Yes
UserName nvarchar(512) Login in the authorisation form No
NormalizedUserName nvarchar(512) UserName in uppercase No
Email nvarchar(512) User E-mail No
NormalizedEmail nvarchar(512) E-mail in uppercase No
EmailConfirmed bit whether the E-mail has been confirmed (using the link from the e-mail) No
PasswordHash nvarchar(max) User Password hash No
SecurityStamp nvarchar(max) Security Stamp No
ConcurrencyStamp nvarchar(max) Concurrency Stamp No
PhoneNumber nvarchar(max) Phone Number No
PhoneNumberConfirmed bit whether the PhoneNumber has been confirmed No
TwoFactorEnabled bit Two-factor authorisation No
LockoutEnd datetimeoffset Once LockoutEnd has a future date then the user is considered locked out whether LockoutEnabled is true or false No
LockoutEnabled bit enable a user lockout for a specific period of time No
AccessFailedCount int how many times the user has entered an incorrect password No

dbo.AspNetUserTokens

Contains user tokens for a specific OAuth provider

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
UserId int UserId from AspNetUsers.Id Yes AspNetUsers.Id
LoginProvider nvarchar(256) Login Provider Yes
Name nvarchar(256) Name of a token Yes
Value nvarchar(max) Token Value No

dbo.Composition

Contains the quantitative composition for a sample (from Sample), identified by SampleId

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
CompositionId int CompositionId - uniqie row identifier, primary key Yes
SampleId int Reference to a Sample (identifier from Sample table) No Sample.SampleId
CompoundIndex int To sort chemical elements in a formula (list) in ascending order, by default 0 No
ElementName varchar(2) Element name as specified in the Periodic Table (ElementInfo table) No ElementInfo.ElementName
ValueAbsolute float Absolute value in chemical formula (element contents) No
ValuePercent float Percent value in chemical formula (element contents), calculated automatically if ValueAbsolute is set No

dbo.ElementInfo

Contains chemical elements from the Periodic Table

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
ElementId int Element Identifier (atomic number), primary key Yes
ElementName varchar(2) Element name as specified in the Periodic Table No

dbo.Handover

Contains handovers for tracking physical objects/samples, but potentially could be extended for any type/logic (extension for ObjectInfo)

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
HandoverId int HandoverId==ObjectId (in this table information from ObjectInfo is extended) Yes ObjectInfo.ObjectId
SampleObjectId int Reference to a Sample (from Sample table == ObjectId from ObjectInfo) that should be physically transferred to other person No ObjectInfo.ObjectId
DestinationUserId int Target user (from AspNetUser) who will receive the sample No AspNetUsers.Id
DestinationConfirmed datetime Time stamp as a receivement confirmation (NULL - not confirmed) No
DestinationComments varchar(128) Comments from a target/destination user No
Json varchar(max) JSON containing additional data regarding the handover No
Amount float Amount of a sample (in case of powder) / NULL in case of items (materials library wafer) No
MeasurementUnit varchar(32) Measurement unit for amount (if specified) / NULL in case of items (materials library wafer) No

dbo.ObjectInfo

Contains objects of all types

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
ObjectId int Unique Object Identifier (assigned automatically, identity), primary key Yes
TenantId int Tenant Identifier (assigned automatically based on URL address) from Tenant table No Tenant.TenantId
_created datetime Date and time of the record creation No
_createdBy int User who created the object (from AspNetUsers table) No AspNetUsers.Id
_updated datetime Date and time of the record creation/modification No
_updatedBy int User who created/modified the object (from AspNetUsers table) No AspNetUsers.Id
TypeId int Object Type (identifier from TypeInfo table) No TypeInfo.TypeId
RubricId int Identifier of the RubricId from RubricInfo (specifies the location of the object within a project tree) No RubricInfo.RubricId
SortCode int To sort objects in a list (project) in ascending order, by default 0 No
AccessControl int Object Availability: 0 - Public (visible for anonymous users), 10 - Partner=Restricted=External=Affiliated - available to external partners ("Partner" claim must be set to "1", so that user sees Partner items and not Protected ones) (less restrictive than "Protected"); for affiliated partner users (who should have access to a limited data), new access level since September 2025, 20 - Protected (visible for users with at least the "User" group), 30 - ProtectedNDA (visible for users with at least the "User" group and the "NDA" claim set to "1"; more restrictive than "Protected"), 40 - Private (visible for user-creator and administrators). No
IsPublished bit Reserved (not used so far): 1 - published; 0 - draft (do not display in lists). No
ExternalId int Auxiliary Id from external information system to support coherence and consistency (if needed) No
ObjectName varchar(512) Unique (within type) object name, must be specified No
ObjectNameUrl varchar(256) Unique (within tenant) object URL (used in web application to refer to object page) No
ObjectFilePath varchar(256) relative path (in future could be an absolute link to an external cloud storage) to the object document stored on the disk (reference to the data file uploaded by the user) No
ObjectFileHash varchar(128) SHA-256 hash (hex) for document in ObjectFilePath (must be unique within a tenant) No
ObjectDescription varchar(1024) Optional Object description (showed in project list) No

dbo.ObjectLinkObject

Contains directed edges (ObjectId -> LinkedObjectId) to form the oriented object graph. The semantic of the edge could be specified in LinkTypeObjectId which characterises the edge.

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
ObjectLinkObjectId int Unique ObjectLinkObject Identifier (assigned automatically, identity), primary key; to link objects to other objects, thus creating a graph Yes
ObjectId int ObjectId (reference to a parent object) from ObjectInfo No ObjectInfo.ObjectId
LinkedObjectId int Linked ObjectId (reference to a child object) from ObjectInfo No ObjectInfo.ObjectId
SortCode int To sort objects in a list of references in ascending order, by default 0 No
_created datetime Date and time of the record creation No
_createdBy int User who created the object (from AspNetUsers table) No AspNetUsers.Id
_updated datetime Date and time of the record creation/modification No
_updatedBy int User who created/modified the object (from AspNetUsers table) No AspNetUsers.Id
LinkTypeObjectId int Object to characterise a link (thus providing semantics for an oriented graph edge) No ObjectInfo.ObjectId

dbo.ObjectLinkRubric

Contains additional rubrics (in addition to ObjectInfo.RubricId) where the object should be visible, allowing multiple locations for an object in the project tree.

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
ObjectLinkRubricId int Unique ObjectLinkRubric Identifier (assigned automatically, identity), primary key; to link objects to other objects, thus creating a graph Yes
RubricId int Reference to a rubric (from RubricInfo) that should show a specified object (thus object is shown in several rubrics) No RubricInfo.RubricId
ObjectId int Reference to an object (from ObjectsInfo) that should be displayed in the rubric No ObjectInfo.ObjectId
SortCode int The sort code for an object link; to sort objects in a list (rubric) of objects/references in ascending order, by default 0 No
_created datetime Date and time of the record creation No
_createdBy int User who created the object (from AspNetUsers table) No AspNetUsers.Id
_updated datetime Date and time of the record creation/modification No
_updatedBy int User who created/modified the object (from AspNetUsers table) No AspNetUsers.Id

dbo.PropertyBigString

Contains large strings (BLOBs) for object description (aka extended properties)

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
PropertyBigStringId int Unique Big String Property Identifier (assigned automatically, identity), primary key Yes
ObjectId int Reference to an object, that owns the property (ObjectId from ObjectInfo) No ObjectInfo.ObjectId
SortCode int To sort properties in a list of properties in ascending order, by default 0 No
_created datetime Date and time of the record creation No
_createdBy int User who created the property (from AspNetUsers table) No AspNetUsers.Id
_updated datetime Date and time of the record creation/modification No
_updatedBy int User who created/modified the property (from AspNetUsers table) No AspNetUsers.Id
Row int Row number (1, 2, 3...) for a table properties (NULL - no table, scalar property). In templates -1 - table property; NULL - scalar No
Value varchar(max) Property value (-1 for templates as a fake value) No
PropertyName varchar(256) Name of the property. Attention: triplet (ObjectId, PropertyName, Row) must be unique, ensured by unique index. No
Comment varchar(256) Comment for a property (could be a measurement unit in a template) No
SourceObjectId int ObjectId of a source object, that created this property (or data from which was saved to this property) No ObjectInfo.ObjectId

dbo.PropertyFloat

Contains real numbers for object description (aka extended properties)

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
PropertyFloatId int Unique Float Property Identifier (assigned automatically, identity), primary key Yes
ObjectId int Reference to an object, that owns the property (ObjectId from ObjectInfo) No ObjectInfo.ObjectId
SortCode int To sort properties in a list of properties in ascending order, by default 0 No
_created datetime Date and time of the record creation No
_createdBy int User who created the property (from AspNetUsers table) No AspNetUsers.Id
_updated datetime Date and time of the record creation/modification No
_updatedBy int User who created/modified the property (from AspNetUsers table) No AspNetUsers.Id
Row int Row number (1, 2, 3...) for a table properties (NULL - no table, scalar property). In templates -1 - table property; NULL - scalar No
Value float Property value (-1 for templates as a fake value) No
ValueEpsilon float Measurement accuracy No
PropertyName varchar(256) Name of the property. Attention: triplet (ObjectId, PropertyName, Row) must be unique, ensured by unique index. No
Comment varchar(256) Comment for a property (could be a measurement unit in a template) No
SourceObjectId int ObjectId of a source object, that created this property (or data from which was saved to this property) No ObjectInfo.ObjectId

dbo.PropertyInt

Contains integer numbers for object description (aka extended properties)

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
PropertyIntId int Unique Integer Property Identifier (assigned automatically, identity), primary key Yes
ObjectId int Reference to an object, that owns the property (ObjectId from ObjectInfo) No ObjectInfo.ObjectId
SortCode int To sort properties in a list of properties in ascending order, by default 0 No
_created datetime Date and time of the record creation No
_createdBy int User who created the property (from AspNetUsers table) No AspNetUsers.Id
_updated datetime Date and time of the record creation/modification No
_updatedBy int User who created/modified the property (from AspNetUsers table) No AspNetUsers.Id
Row int Row number (1, 2, 3...) for a table properties (NULL - no table, scalar property). In templates -1 - table property; NULL - scalar No
Value bigint Property value (-1 for templates as a fake value) No
PropertyName varchar(256) Name of the property. Attention: triplet (ObjectId, PropertyName, Row) must be unique, ensured by unique index. No
Comment varchar(256) Comment for a property (could be a measurement unit in a template) No
SourceObjectId int ObjectId of a source object, that created this property (or data from which was saved to this property) No ObjectInfo.ObjectId

dbo.PropertyString

Contains strings (up to 4096 characters) for object description (aka extended properties)

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
PropertyStringId int Unique String Property Identifier (assigned automatically, identity), primary key Yes
ObjectId int Reference to an object, that owns the property (ObjectId from ObjectInfo) No ObjectInfo.ObjectId
SortCode int To sort properties in a list of properties in ascending order, by default 0 No
_created datetime Date and time of the record creation No
_createdBy int User who created the property (from AspNetUsers table) No AspNetUsers.Id
_updated datetime Date and time of the record creation/modification No
_updatedBy int User who created/modified the property (from AspNetUsers table) No AspNetUsers.Id
Row int Row number (1, 2, 3...) for a table properties (NULL - no table, scalar property). In templates -1 - table property; NULL - scalar No
Value varchar(4096) Property value (-1 for templates as a fake value) No
PropertyName varchar(256) Name of the property. Attention: triplet (ObjectId, PropertyName, Row) must be unique, ensured by unique index. No
Comment varchar(256) Comment for a property (could be a measurement unit in a template) No
SourceObjectId int ObjectId of a source object, that created this property (or data from which was saved to this property) No ObjectInfo.ObjectId

dbo.Reference

Contains description for literature references (extension for ObjectInfo)

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
ReferenceId int ReferenceId==ObjectId (in this table information from ObjectInfo is extended) Yes ObjectInfo.ObjectId
Authors varchar(512) All authors in a comma-separated list No
Title varchar(1024) Title of manuscript No
Journal varchar(256) Journal Title No
Year int publication year No
Volume varchar(32) volume descriptor No
Number varchar(32) issue number No
StartPage varchar(32) Start page of a publication No
EndPage varchar(32) End page of a publication No
DOI varchar(256) DOI (Digital Object Identifier) No
URL varchar(256) Publication URL (Uniform Resource Locator) No
BibTeX varchar(4096) Description in a BibTeX format No

dbo.RubricInfo

Contains tree structures for the application (project tree, organisational structure)

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
RubricId int Unique Rubric Identifier, primary key Yes
TenantId int Tenant Identifier (assigned automatically based on URL address) from Tenant table No Tenant.TenantId
_created datetime Date and time of the record creation No
_createdBy int User who created the object (from AspNetUsers table) No AspNetUsers.Id
_updated datetime Date and time of the record creation/modification No
_updatedBy int User who created/modified the object (from AspNetUsers table) No AspNetUsers.Id
TypeId int Rubric Type (identifier from TypeInfo table) No TypeInfo.TypeId
ParentId int RubricId of a parent rubric (or NULL in case of top-level rubric) No RubricInfo.RubricId
Level int Level of rubric nesting (0 - root rubric; 1 - next level, etc...) No
LeafFlag int Set of flags. From the least significant bit: 0x1 - presence of objects (in ObjectInfo): 0 - no objects; 1 - there are objects; 0x2 - presence of objects (in ObjectLinkRubric): 0 - no objects; 1 - links exist; 0x4 - Is Rubric End / Leaf (child free): 0 - no children (leaf); 1 - there are children (not leaf). Calculated automatically on the server (see dbo.ProcRubric_NormaliseTree) No
Flags int Reserved (not used so far) No
SortCode int To sort rubrics within a parent (project) in ascending order, by default 0 No
AccessControl int Rubric Availability: 0 - Public (visible for anonymous users), 1 - Protected (visible for users with at least the "User" group), 2 - ProtectedNDA (visible for users with at least the "User" group and assigned "NDA" claim), 3 - Private (visible for user-creator and administrators). No
IsPublished bit Reserved (not used so far): 1 - published; 0 - draft (do not display in lists). No
RubricName varchar(256) Name of the Rubric, must be specified No
RubricNameUrl varchar(256) Unique (within tenant) rubric URL (used in web application to refer to rubric page) No
RubricPath varchar(256) '}'-separated rubric path from the root (calculated automatically on the server, see dbo.ProcRubric_NormaliseTree) No

dbo.RubricInfoAdds

Contains text description for a rubric from RubricInfo

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
RubricId int Rubric Identifier from RubricInfo table Yes RubricInfo.RubricId
RubricText varchar(max) Rubric Description (HTML) to shown in the app No

dbo.Sample

Contains sample information (essentially a chemical system, i.e. qualitative description of composition (extension for ObjectInfo)

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
SampleId int SampleId==ObjectId (in this table information from ObjectInfo is extended) Yes ObjectInfo.ObjectId
ElemNumber int Number of chemical elements (number of elements in the chemical system) No
Elements varchar(256) List of chemical elements (in ascending order by name) separated by "-" (to search using "-El-" expression). No

dbo.Tenant

Contains list of tenants in the database (tenants share common users and types)

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
TenantId int Unique tenant identifier (TenantId must be unique within an application instance that supports multiple databases, i.e. TenantId is unique across all instance databases) Yes
_date datetime Date and time of the record creation/modification No
Language varchar(32) User Interface Language No
TenantUrl varchar(32) Tenant URL (unique across instances) No
TenantName varchar(128) Short name of a tenant (displayed in the top left corner of the UI) No
AccessControl int Default access control value (see ObjectsInfo.AccessControl description) in UI for new objects No
SettingsJson varchar(max) JSON with settings for the tenant (see documentation) No

dbo.TypeInfo

Contains types of objects that could be present in the application, regarding their configuration (allowed file type extensions, validation and data extraction schema, etc.)

Columns description

Name Type Description Is Primary Key Foreign Key (table.column)
TypeId int Unique Type Identifier, primary key Yes
IsHierarchical bit 1 - Hierarchical types (such as project tree) are stored in the RubricInfo table; 0 - List types (such as chemical objects, measurement results) are stored in the ObjectInfo table. No
TypeIdForRubric int TypeId for the hierarchical classifier where the object should be displayed (in most cases - id of Rubric Type) No
TypeName varchar(64) Unique Type Name No
TableName varchar(64) Table Name to store data (one of: RubricInfo; ObjectInfo, Sample, Composition, Reference, Handover) No
UrlPrefix varchar(64) URL prefix for type (reserved, not used so far) No
TypeComment varchar(256) Comment for type, describing it's purpose No
ValidationSchema varchar(256) Schema to validate type objects data (type:ClassName or https://WebServiceURL) No
DataSchema varchar(256) Schema to extract data from objects' data documents (type:ClassName or https://WebServiceURL) No
SettingsJson varchar(8000) JSON with settings for the type (see documentation) No
FileRequired bit 1 - impossible to create object without a file in ObjectInfo.ObjectFilePath; 0 - optional file No
_date datetime Date and time of the record creation/modification No

Views

dbo.vComposition

View that combines Composition with Sample and ObjectInfo tables to get all quantitative compositions

dbo.vHandover

View that combines Handover with ObjectInfo table to get complete handovers data

dbo.vObjectInfo

View that combines ObjectInfo with types and users tables to get extended object data

dbo.vObjectLinkObject

View that combines ObjectLinkObject with ObjectInfo table two times (for parent - ObjectId; and for Child - LinkedObjectId) to get information about interconnected objects (except edge type characterisation in LinkTypeObjectId)

dbo.vReference

View that combines Reference with ObjectInfo table to get complete literature reference data

dbo.vSample

View that combines Sample with ObjectInfo table to get complete sample data

dbo.vsearchComposition

View for search on composition

vro.vroAspNetRoleClaims

Read-only view for AspNetRoleClaims table

vro.vroAspNetRoles

Read-only view for AspNetRoles table

vro.vroAspNetUserClaims

Read-only view for AspNetUserClaims table

vro.vroAspNetUserLogins

Read-only view for AspNetUserLogins table

vro.vroAspNetUserRoles

Read-only view for AspNetUserRoles table

vro.vroAspNetUsers

Read-only view for AspNetUsers table

vro.vroAspNetUserTokens

Read-only view for AspNetUserTokens table

vro.vroComposition

Read-only view for Composition table

vro.vroElementInfo

Read-only view for ElementInfo table

vro.vroHandover

Read-only view for Handover table

vro.vroObjectInfo

Read-only view for ObjectInfo table

vro.vroObjectLinkObject

Read-only view for ObjectLinkObject table

vro.vroObjectLinkRubric

Read-only view for ObjectLinkRubric table

vro.vroPropertyBigString

Read-only view for PropertyBigString table

vro.vroPropertyFloat

Read-only view for PropertyFloat table

vro.vroPropertyInt

Read-only view for PropertyInt table

vro.vroPropertyString

Read-only view for PropertyString table

vro.vroReference

Read-only view for Reference table

vro.vroRubricInfo

Read-only view for RubricInfo table

vro.vroRubricInfoAdds

Read-only view for RubricInfoAdds table

vro.vroSample

Read-only view for Sample table

vro.vroTenant

Read-only view for Tenant table

vro.vroTypeInfo

Read-only view for TypeInfo table

vro.vrovComposition

Read-only view for vComposition view

vro.vrovHandover

Read-only view for vHandover view

vro.vrovObjectLinkObject

Read-only view for vObjectLinkObject view

vro.vrovReference

Read-only view for vReference view

vro.vrovSample

Read-only view for vSample view