ZuloOne
English
CabinetAccount
Sign in
Menu

ZuloOne Database Schema

Updated

Overview#

This document provides a detailed database schema for the ZuloOne ERP platform. The database stores:

  • Metadata objects — all metadata definitions (EDT, Dictionaries, Documents, Registers, Services, etc.)
  • Security — users, roles, permissions, sessions, audit logs
  • Development — projects, tests, scripts, packages
  • Runtime business data — actual business records (Customers, Invoices, etc.) are generated dynamically

Important Notes:

  • All metadata tables use MetaId (Guid) as primary key
  • Business data tables (runtime tables) use Id (integer) as primary key
  • No foreign key constraints in database — relationships handled by application logic
  • All data access through system's API/OData interface

Table of Contents#

  1. Core Metadata Tables
  2. Security Tables
  3. Development Tables
  4. Runtime Business Data Tables
  5. Relationships Summary
  6. Indexes Summary
  7. Database Provider Considerations
  8. Notes

Core Metadata Tables#

MetaModel#

Defines business logic models (Core, Tax, CRM, WHS, Custom).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
Name nvarchar(100) NOT NULL, UNIQUE Model name (e.g., "Core", "Tax", "CRM")
ModelType nvarchar(50) NOT NULL "System" or "Client"
BaseModelMetaId uniqueidentifier NULL References MetaModel.MetaId (for model inheritance)
LayerId int NOT NULL References MetaLayer.MetaId (System=1, ISV=2, Customer=3)
IsSystem bit NOT NULL, DEFAULT 0 System model flag (read-only)
IsReadOnly bit NOT NULL, DEFAULT 0 Read-only flag
Description nvarchar(500) NULL Model description
Version nvarchar(50) NULL Model version
Created datetime2 NOT NULL, DEFAULT GETDATE() Creation timestamp
Modified datetime2 NOT NULL, DEFAULT GETDATE() Last modification timestamp

Indexes:

  • IX_MetaModel_Name (Name)
  • IX_MetaModel_LayerId (LayerId)

MetaLayer#

System layers for extension hierarchy (System, ISV, Customer).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
Name nvarchar(100) NOT NULL, UNIQUE Layer name ("System", "ISV", "Customer")
LayerType nvarchar(50) NOT NULL Layer type
Order int NOT NULL Compilation order (0=System, 1=ISV, 2=Customer)
Description nvarchar(500) NULL Layer description

Indexes:

  • IX_MetaLayer_Order (Order)

MetaEDT#

Extended Data Types — reusable data types with parameters.

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
Name nvarchar(100) NOT NULL, UNIQUE EDT name (e.g., "String50", "Decimal152")
EDTType nvarchar(50) NOT NULL Type: "String", "Int", "Decimal", "DateTime", "Date", "Boolean", "Guid", "Binary", "Reference", "Enum"
Length int NULL Length parameter (for String, Int)
Precision int NULL Precision (for Decimal - total digits)
Scale int NULL Scale (for Decimal - decimal places)
IsUnicode bit NULL Unicode flag (for String)
HasTime bit NULL Include time (for DateTime)
HasTimezone bit NULL Include timezone (for DateTime)
DateOnly bit NULL Date only, no time (for Date)
MaxSize bigint NULL Maximum size in bytes (for Binary)
StorageType nvarchar(50) NULL "Database", "FileSystem", "CloudStorage" (for Binary)
AllowedExtensions nvarchar(500) NULL Comma-separated extensions (for Binary)
ReferenceDictionaryMetaId uniqueidentifier NULL References MetaDictionary.MetaId (for Reference EDT)
ReferenceEnumMetaId uniqueidentifier NULL References MetaEnum.MetaId (for Enum EDT)
ModelId uniqueidentifier NOT NULL References MetaModel.MetaId
Description nvarchar(500) NULL EDT description

Indexes:

  • IX_MetaEDT_Name (Name)
  • IX_MetaEDT_ModelId (ModelId)
  • IX_MetaEDT_EDTType (EDTType)

MetaDictionary#

Dictionary definitions (simple lookup tables).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
Name nvarchar(100) NOT NULL, UNIQUE Dictionary name (e.g., "Customers", "Products")
ModelId uniqueidentifier NOT NULL References MetaModel.MetaId
LayerId int NOT NULL References MetaLayer.MetaId
IsExtension bit NOT NULL, DEFAULT 0 Extension flag
BaseDictionaryMetaId uniqueidentifier NULL References MetaDictionary.MetaId (if IsExtension=true)
Description nvarchar(500) NULL Dictionary description
Created datetime2 NOT NULL, DEFAULT GETDATE() Creation timestamp
Modified datetime2 NOT NULL, DEFAULT GETDATE() Last modification timestamp

Indexes:

  • IX_MetaDictionary_Name (Name)
  • IX_MetaDictionary_ModelId (ModelId)
  • IX_MetaDictionary_BaseDictionaryMetaId (BaseDictionaryMetaId)

MetaDictionaryProperty#

Field definitions for dictionaries.

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
DictionaryMetaId uniqueidentifier NOT NULL References MetaDictionary.MetaId
FieldName nvarchar(100) NOT NULL Field name (e.g., "Code", "Name", "Phone")
EDTMetaId uniqueidentifier NOT NULL References MetaEDT.MetaId
IsRequired bit NOT NULL, DEFAULT 0 Required field flag
IsUnique bit NOT NULL, DEFAULT 0 Unique constraint flag
DefaultValue nvarchar(500) NULL Default value
DisplayOrder int NOT NULL, DEFAULT 0 Field display order
Description nvarchar(500) NULL Field description

Indexes:

  • IX_MetaDictionaryProperty_DictionaryMetaId (DictionaryMetaId)
  • IX_MetaDictionaryProperty_FieldName (FieldName)
  • IX_MetaDictionaryProperty_EDTMetaId (EDTMetaId)

MetaDocumentType#

Document type definitions (documents with header and lines).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
Name nvarchar(100) NOT NULL, UNIQUE Document type name (e.g., "Invoice", "PurchaseOrder")
ModelId uniqueidentifier NOT NULL References MetaModel.MetaId
LayerId int NOT NULL References MetaLayer.MetaId
IsExtension bit NOT NULL, DEFAULT 0 Extension flag
BaseDocumentTypeMetaId uniqueidentifier NULL References MetaDocumentType.MetaId (if IsExtension=true)
Description nvarchar(500) NULL Document description
Created datetime2 NOT NULL, DEFAULT GETDATE() Creation timestamp
Modified datetime2 NOT NULL, DEFAULT GETDATE() Last modification timestamp

Indexes:

  • IX_MetaDocumentType_Name (Name)
  • IX_MetaDocumentType_ModelId (ModelId)
  • IX_MetaDocumentType_BaseDocumentTypeMetaId (BaseDocumentTypeMetaId)

MetaDocumentHeaderField#

Header field definitions for documents.

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
DocumentTypeMetaId uniqueidentifier NOT NULL References MetaDocumentType.MetaId
FieldName nvarchar(100) NOT NULL Field name (e.g., "Number", "Date", "CustomerId")
EDTMetaId uniqueidentifier NOT NULL References MetaEDT.MetaId
IsRequired bit NOT NULL, DEFAULT 0 Required field flag
DisplayOrder int NOT NULL, DEFAULT 0 Field display order
Description nvarchar(500) NULL Field description

Indexes:

  • IX_MetaDocumentHeaderField_DocumentTypeMetaId (DocumentTypeMetaId)
  • IX_MetaDocumentHeaderField_FieldName (FieldName)
  • IX_MetaDocumentHeaderField_EDTMetaId (EDTMetaId)

MetaDocumentLine#

Document line type definitions (defines line types for documents).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
DocumentTypeMetaId uniqueidentifier NOT NULL References MetaDocumentType.MetaId
Name nvarchar(200) NOT NULL Line type name (e.g., "SalesOrderLine", "InvoiceLine", "InvoiceDiscountLine")
Description nvarchar(500) NULL Line type description
ModelId uniqueidentifier NOT NULL References MetaModel.MetaId
LayerId int NOT NULL References MetaLayer.MetaId
IsExtension bit NOT NULL, DEFAULT 0 Extension flag (true if this line type extends another)
BaseDocumentLineMetaId uniqueidentifier NULL References MetaDocumentLine.MetaId (for extensions)
DisplayOrder int NOT NULL, DEFAULT 0 Display order (for UI)
Created datetime2 NOT NULL, DEFAULT GETDATE() Creation timestamp
Modified datetime2 NOT NULL, DEFAULT GETDATE() Last modification timestamp

Indexes:

  • IX_MetaDocumentLine_DocumentTypeMetaId (DocumentTypeMetaId)
  • IX_MetaDocumentLine_Name (Name)
  • IX_MetaDocumentLine_BaseDocumentLineMetaId (BaseDocumentLineMetaId)

Usage:

  • When document type is created, system automatically generates default line type with name "{DocumentTypeName}Line"
  • One document type can have multiple line types (e.g., InvoiceLine, InvoiceDiscountLine, InvoiceTaxLine)
  • System automatically generates C# class for each line type in metadata
  • Line types can be extended via IsExtension and BaseDocumentLineMetaId

MetaDocumentLineField#

Line field definitions for documents.

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
DocumentLineMetaId uniqueidentifier NOT NULL References MetaDocumentLine.MetaId (NOT DocumentTypeMetaId)
FieldName nvarchar(100) NOT NULL Field name (e.g., "LineNumber", "ProductId", "Quantity", "Price", "Amount")
EDTMetaId uniqueidentifier NOT NULL References MetaEDT.MetaId
IsRequired bit NOT NULL, DEFAULT 0 Required field flag
DisplayOrder int NOT NULL, DEFAULT 0 Field display order
Description nvarchar(500) NULL Field description

Indexes:

  • IX_MetaDocumentLineField_DocumentLineMetaId (DocumentLineMetaId)
  • IX_MetaDocumentLineField_FieldName (FieldName)
  • IX_MetaDocumentLineField_EDTMetaId (EDTMetaId)

MetaDocumentStatus#

Document status definitions for workflow (possible statuses for document types).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
DocumentTypeMetaId uniqueidentifier NOT NULL References MetaDocumentType.MetaId
StatusName nvarchar(100) NOT NULL Status name (e.g., "Draft", "Posted", "Cancelled")
StatusValue nvarchar(100) NOT NULL Status value (used in code and database)
DisplayOrder int NOT NULL, DEFAULT 0 Display order in UI dropdowns
Description nvarchar(500) NULL Status description
IsInitial bit NOT NULL, DEFAULT 0 Initial status flag (document starts with this status)
IsFinal bit NOT NULL, DEFAULT 0 Final status flag (document workflow ends here)
WorkflowOrder int NOT NULL, DEFAULT 0 Workflow order (defines status transition sequence)
AllowChange bit NOT NULL, DEFAULT 1 Allow manual status change flag
Icon nvarchar(100) NULL Status icon (for UI display)
Color nvarchar(50) NULL Status color (for UI display)

Indexes:

  • IX_MetaDocumentStatus_DocumentTypeMetaId (DocumentTypeMetaId)
  • IX_MetaDocumentStatus_StatusValue (StatusValue)
  • IX_MetaDocumentStatus_WorkflowOrder (WorkflowOrder)

Examples:

-- Invoice document statuses
DocumentTypeMetaId: GUID_INVOICE
- StatusName: "Draft", StatusValue: "Draft", IsInitial=true, WorkflowOrder=1
- StatusName: "Posted", StatusValue: "Posted", IsFinal=false, WorkflowOrder=2
- StatusName: "Cancelled", StatusValue: "Cancelled", IsFinal=true, WorkflowOrder=3

-- Inventory document statuses
DocumentTypeMetaId: GUID_INVENTORY
- StatusName: "Draft", StatusValue: "Draft", IsInitial=true, WorkflowOrder=1
- StatusName: "Started", StatusValue: "Started", IsFinal=false, WorkflowOrder=2
- StatusName: "Completed", StatusValue: "Completed", IsFinal=false, WorkflowOrder=3
- StatusName: "Closed", StatusValue: "Closed", IsFinal=true, WorkflowOrder=4

-- SalesOrder document statuses
DocumentTypeMetaId: GUID_SALESORDER
- StatusName: "Draft", StatusValue: "Draft", IsInitial=true, WorkflowOrder=1
- StatusName: "Confirmed", StatusValue: "Confirmed", IsFinal=false, WorkflowOrder=2
- StatusName: "Shipped", StatusValue: "Shipped", IsFinal=false, WorkflowOrder=3
- StatusName: "Invoiced", StatusValue: "Invoiced", IsFinal=false, WorkflowOrder=4
- StatusName: "Completed", StatusValue: "Completed", IsFinal=true, WorkflowOrder=5

MetaRegister#

Register type definitions (registers with header and lines).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
Name nvarchar(100) NOT NULL, UNIQUE Register name (e.g., "InventoryBalance", "AccountBalance")
ModelId uniqueidentifier NOT NULL References MetaModel.MetaId
LayerId int NOT NULL References MetaLayer.MetaId
IsExtension bit NOT NULL, DEFAULT 0 Extension flag
BaseRegisterMetaId uniqueidentifier NULL References MetaRegister.MetaId (if IsExtension=true)
RegisterEngineType nvarchar(50) NOT NULL, DEFAULT 'Standard' Engine type: "Standard", "FIFO", "Financial", "FlatHistory", "Custom"
EngineScriptMetaId uniqueidentifier NULL References MetaScript.MetaId (if RegisterEngineType="Custom")
Description nvarchar(500) NULL Register description
Created datetime2 NOT NULL, DEFAULT GETDATE() Creation timestamp
Modified datetime2 NOT NULL, DEFAULT GETDATE() Last modification timestamp

Indexes:

  • IX_MetaRegister_Name (Name)
  • IX_MetaRegister_ModelId (ModelId)
  • IX_MetaRegister_BaseRegisterMetaId (BaseRegisterMetaId)

MetaRegisterHeader#

Header field definitions for registers.

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
RegisterMetaId uniqueidentifier NOT NULL References MetaRegister.MetaId
FieldName nvarchar(100) NOT NULL Field name (e.g., "Period", "Dimension1")
EDTMetaId uniqueidentifier NOT NULL References MetaEDT.MetaId
DisplayOrder int NOT NULL, DEFAULT 0 Field display order
Description nvarchar(500) NULL Field description

Indexes:

  • IX_MetaRegisterHeader_RegisterMetaId (RegisterMetaId)
  • IX_MetaRegisterHeader_FieldName (FieldName)

MetaRegisterField#

Field definitions for registers (both header and line fields).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
RegisterMetaId uniqueidentifier NOT NULL References MetaRegister.MetaId
FieldName nvarchar(100) NOT NULL Field name (e.g., "Period", "Dimension1", "Resource", "Quantity", "Amount")
FieldType nvarchar(50) NOT NULL "Header" or "Line" - indicates if field belongs to header or line
EDTMetaId uniqueidentifier NOT NULL References MetaEDT.MetaId
DisplayOrder int NOT NULL, DEFAULT 0 Field display order
Description nvarchar(500) NULL Field description

Indexes:

  • IX_MetaRegisterField_RegisterMetaId (RegisterMetaId)
  • IX_MetaRegisterField_FieldName (FieldName)
  • IX_MetaRegisterField_FieldType (FieldType)

MetaEnum#

Enumeration definitions (simple key-value lists that compile to C# enums).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
Name nvarchar(100) NOT NULL, UNIQUE Enum name (e.g., "EnumNoYes", "EnumStatus")
ModelId uniqueidentifier NOT NULL References MetaModel.MetaId
LayerId int NOT NULL References MetaLayer.MetaId
IsExtension bit NOT NULL, DEFAULT 0 Extension flag
BaseEnumMetaId uniqueidentifier NULL References MetaEnum.MetaId (if IsExtension=true)
Description nvarchar(500) NULL Enum description

Indexes:

  • IX_MetaEnum_Name (Name)
  • IX_MetaEnum_ModelId (ModelId)
  • IX_MetaEnum_BaseEnumMetaId (BaseEnumMetaId)

MetaEnumValue#

Enum value definitions (text-value pairs).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
EnumMetaId uniqueidentifier NOT NULL References MetaEnum.MetaId
Name nvarchar(100) NOT NULL Value name (e.g., "No", "Yes")
Value int NOT NULL Numeric value (e.g., 0, 1)
DisplayOrder int NOT NULL, DEFAULT 0 Display order
Description nvarchar(500) NULL Value description

Indexes:

  • IX_MetaEnumValue_EnumMetaId (EnumMetaId)
  • IX_MetaEnumValue_Value (Value)

MetaService#

Service class definitions (helper classes for business logic, no visual UI).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
Name nvarchar(100) NOT NULL, UNIQUE Service name (e.g., "LocationHelper")
Description nvarchar(500) NULL Service description
ModelId uniqueidentifier NOT NULL References MetaModel.MetaId
LayerId int NOT NULL References MetaLayer.MetaId
Namespace nvarchar(200) NULL C# namespace (e.g., "ZuloOne.Services")
Created datetime2 NOT NULL, DEFAULT GETDATE() Creation timestamp
Modified datetime2 NOT NULL, DEFAULT GETDATE() Last modification timestamp
Version nvarchar(50) NULL Service version

Indexes:

  • IX_MetaService_Name (Name)
  • IX_MetaService_ModelId (ModelId)

MetaDataEntity#

Public OData resource: a projection of dictionary/document tables exposed at /odata/{PublicCollectionName}.

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
Name nvarchar(200) NOT NULL Data entity name
ModelId uniqueidentifier NOT NULL References MetaModel.MetaId
LayerId int NOT NULL Layer
IsExtension bit NOT NULL, DEFAULT 0 Extension flag
BaseDataEntityMetaId uniqueidentifier NULL References MetaDataEntity.MetaId (if IsExtension=true); stored as BaseMetaId
SourceTableName nvarchar(200) NULL Legacy; filled from the primary data source
ScriptMetaId uniqueidentifier NULL Event-handler script
ODataPath nvarchar(200) NULL Legacy path hint
PublicCollectionName nvarchar(200) NULL OData entity-set name; empty = Name
IsActive bit NOT NULL False → 404
IsReadOnly bit NOT NULL, DEFAULT 0 POST/PATCH/DELETE → 405
RequireExplicitRoles bit NOT NULL, DEFAULT 0 Empty matrix otherwise public
Description nvarchar(500) NULL Description
CreatedDateTime datetime2 NOT NULL Creation timestamp
ModifiedDateTime datetime2 NOT NULL Last modification timestamp

Child tables: MetaDataEntityDataSources (alias, object type/id, primary, join), MetaDataEntityFields (public name, data type, source alias/column, key/computed).

Indexes:

  • IX_MetaDataEntity_Name (Name)
  • IX_MetaDataEntity_ModelId (ModelId)
  • IX_MetaDataEntity_BaseDataEntityMetaId (BaseDataEntityMetaId)

MetaScript#

Script storage table (stores all C# code: classes, scripts, processing logic).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
Name nvarchar(200) NOT NULL Script name
ScriptType nvarchar(50) NOT NULL Type: "EventHandler", "ExtensionClass", "ServiceClass", "DataEntityScript", "JobScript", "RegisterEngineScript", "UnitTest", "IntegrationTest"
ObjectType nvarchar(50) NULL Object type: "Dictionary", "Document", "Register", "Service", "DataEntity", "Extension", "Job"
ObjectName nvarchar(100) NULL Object name (e.g., "Customer", "Invoice", "LocationHelper")
ObjectMetaId uniqueidentifier NULL References metadata object MetaId (MetaDictionary.MetaId, MetaDocumentType.MetaId, etc.)
EventName nvarchar(100) NULL Event name (NULL for EventHandler - all events in one class)
BaseClassName nvarchar(200) NULL Base class name (e.g., "DictionaryEventHandler", "InvoicePosting")
Code nvarchar(max) NOT NULL Full C# code
ModelId uniqueidentifier NOT NULL References MetaModel.MetaId
Author nvarchar(100) NULL Script author
Created datetime2 NOT NULL, DEFAULT GETDATE() Creation timestamp
Modified datetime2 NOT NULL, DEFAULT GETDATE() Last modification timestamp
Version nvarchar(50) NULL Script version
IsActive bit NOT NULL, DEFAULT 1 Active flag

Indexes:

  • IX_MetaScript_ScriptType (ScriptType)
  • IX_MetaScript_ObjectType (ObjectType)
  • IX_MetaScript_ObjectMetaId (ObjectMetaId)
  • IX_MetaScript_ObjectName (ObjectName)
  • IX_MetaScript_ModelId (ModelId)

Security Tables#

MetaUser#

User accounts (system users).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
UserName nvarchar(100) NOT NULL, UNIQUE Username for login
Email nvarchar(200) NULL, UNIQUE User email
PasswordHash nvarchar(500) NOT NULL Password hash (bcrypt, PBKDF2, etc.)
IsActive bit NOT NULL, DEFAULT 1 Active account flag
FirstName nvarchar(100) NULL User first name
LastName nvarchar(100) NULL User last name
LastLogin datetime2 NULL Last login timestamp
Created datetime2 NOT NULL, DEFAULT GETDATE() Creation timestamp
Modified datetime2 NOT NULL, DEFAULT GETDATE() Last modification timestamp

Indexes:

  • IX_MetaUser_UserName (UserName)
  • IX_MetaUser_Email (Email)

MetaRole#

Security roles (groups of permissions).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
Name nvarchar(100) NOT NULL, UNIQUE Role name (e.g., "Administrator", "Developer", "User")
Description nvarchar(500) NULL Role description
IsSystem bit NOT NULL, DEFAULT 0 System role flag (cannot be deleted)
Created datetime2 NOT NULL, DEFAULT GETDATE() Creation timestamp
Modified datetime2 NOT NULL, DEFAULT GETDATE() Last modification timestamp

Indexes:

  • IX_MetaRole_Name (Name)

MetaUserRole#

Many-to-many relationship between users and roles.

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
UserMetaId uniqueidentifier NOT NULL References MetaUser.MetaId
RoleMetaId uniqueidentifier NOT NULL References MetaRole.MetaId
Assigned datetime2 NOT NULL, DEFAULT GETDATE() Assignment timestamp

Indexes:

  • IX_MetaUserRole_UserMetaId (UserMetaId)
  • IX_MetaUserRole_RoleMetaId (RoleMetaId)
  • IX_MetaUserRole_UserRole (UserMetaId, RoleMetaId) UNIQUE

MetaPermission#

Permission definitions (types of permissions).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
Name nvarchar(100) NOT NULL, UNIQUE Permission name (e.g., "MetadataRead", "DataWrite")
PermissionType nvarchar(50) NOT NULL Type: "MetadataRead", "MetadataWrite", "DataRead", "DataWrite", "Execute", "Compile"
Description nvarchar(500) NULL Permission description

Indexes:

  • IX_MetaPermission_Name (Name)
  • IX_MetaPermission_PermissionType (PermissionType)

MetaObjectPermission#

Permissions on metadata objects (Dictionary, Document, Register, Service, etc.).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
RoleMetaId uniqueidentifier NOT NULL References MetaRole.MetaId
ObjectType nvarchar(50) NULL Object type: "Dictionary", "Document", "Register", "Service", "DataEntity", "Enum", NULL (all types)
ObjectMetaId uniqueidentifier NULL References metadata object MetaId (specific object, NULL for all objects)
PermissionType nvarchar(50) NOT NULL Type: "Read", "Write", "Create", "Delete", "Execute", "Compile"
IsGranted bit NOT NULL true=grant permission, false=deny permission

Indexes:

  • IX_MetaObjectPermission_RoleMetaId (RoleMetaId)
  • IX_MetaObjectPermission_ObjectType (ObjectType)
  • IX_MetaObjectPermission_ObjectMetaId (ObjectMetaId)
  • IX_MetaObjectPermission_RoleObject (RoleMetaId, ObjectType, ObjectMetaId)

MetaDataPermission#

Record-level data permissions (permissions on business data records).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
UserMetaId uniqueidentifier NULL References MetaUser.MetaId (user-specific permission, NULL if role-based)
RoleMetaId uniqueidentifier NULL References MetaRole.MetaId (role-based permission, NULL if user-specific)
TableName nvarchar(100) NOT NULL Business table name (e.g., "Customers", "Invoices")
RecordId int NULL Specific record Id (NULL for all records in table)
PermissionType nvarchar(50) NOT NULL Type: "Read", "Write", "Create", "Delete", "Post"
Condition nvarchar(max) NULL C# expression for dynamic filtering (e.g., "Invoice.Status == 'Draft' OR Invoice.CreatedBy == CurrentUser.MetaId")

Indexes:

  • IX_MetaDataPermission_UserMetaId (UserMetaId)
  • IX_MetaDataPermission_RoleMetaId (RoleMetaId)
  • IX_MetaDataPermission_TableName (TableName)
  • IX_MetaDataPermission_RecordId (RecordId)

Constraints:

  • At least one of UserMetaId or RoleMetaId must be NOT NULL

MetaSession#

User sessions (active user sessions).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
UserMetaId uniqueidentifier NOT NULL References MetaUser.MetaId
SessionToken nvarchar(500) NOT NULL, UNIQUE Session token (JWT or custom token)
IpAddress nvarchar(50) NULL Client IP address
UserAgent nvarchar(500) NULL Client user agent
Created datetime2 NOT NULL, DEFAULT GETDATE() Session creation timestamp
Expires datetime2 NOT NULL Session expiration timestamp
LastActivity datetime2 NOT NULL, DEFAULT GETDATE() Last activity timestamp
IsActive bit NOT NULL, DEFAULT 1 Active session flag

Indexes:

  • IX_MetaSession_UserMetaId (UserMetaId)
  • IX_MetaSession_SessionToken (SessionToken)
  • IX_MetaSession_IsActive (IsActive, Expires)

MetaAuditLog#

Audit trail (log of all user actions).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
UserMetaId uniqueidentifier NULL References MetaUser.MetaId (NULL for system actions)
Action nvarchar(50) NOT NULL Action type: "Create", "Update", "Delete", "Read", "Execute", "Login", "Logout", "PermissionGrant", "PermissionDeny"
ObjectType nvarchar(50) NULL Object type (e.g., "Dictionary", "Document", "User")
ObjectMetaId uniqueidentifier NULL References metadata object MetaId
ObjectName nvarchar(200) NULL Object name (for display)
TableName nvarchar(100) NULL Business table name (for data operations)
RecordId int NULL Business record Id (for data operations)
Changes nvarchar(max) NULL JSON with old/new values
IpAddress nvarchar(50) NULL Client IP address
Timestamp datetime2 NOT NULL, DEFAULT GETDATE() Action timestamp

Indexes:

  • IX_MetaAuditLog_UserMetaId (UserMetaId)
  • IX_MetaAuditLog_Action (Action)
  • IX_MetaAuditLog_ObjectType (ObjectType)
  • IX_MetaAuditLog_ObjectMetaId (ObjectMetaId)
  • IX_MetaAuditLog_Timestamp (Timestamp)
  • IX_MetaAuditLog_TableRecord (TableName, RecordId)

Development Tables#

MetaProject#

Development projects (isolated workspaces for developers).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
Name nvarchar(100) NOT NULL, UNIQUE Project name
Description nvarchar(500) NULL Project description
Version nvarchar(50) NULL Project version
Author nvarchar(100) NULL Project author
Created datetime2 NOT NULL, DEFAULT GETDATE() Creation timestamp
Modified datetime2 NOT NULL, DEFAULT GETDATE() Last modification timestamp

Indexes:

  • IX_MetaProject_Name (Name)

MetaProjectObject#

Many-to-many relationship between projects and metadata objects.

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
ProjectMetaId uniqueidentifier NOT NULL References MetaProject.MetaId
ObjectType nvarchar(50) NOT NULL Object type: "Dictionary", "Document", "Register", "Service", "DataEntity", "Script", "Enum"
ObjectMetaId uniqueidentifier NOT NULL References metadata object MetaId (MetaDictionary.MetaId, MetaDocumentType.MetaId, etc.)

Indexes:

  • IX_MetaProjectObject_ProjectMetaId (ProjectMetaId)
  • IX_MetaProjectObject_ObjectMetaId (ObjectMetaId)
  • IX_MetaProjectObject_ProjectObject (ProjectMetaId, ObjectType, ObjectMetaId) UNIQUE

Tests (Stored in MetaScript)#

Note: Tests are NOT stored in a separate table. They are stored in MetaScript table with:

  • ScriptType = "UnitTest" or ScriptType = "IntegrationTest"
  • ObjectType = "Test"
  • ObjectName = "TestName" (e.g., "CustomerCreationTest")
  • ObjectMetaId — references metadata object being tested (Dictionary, Document, Register, etc.)
  • Code — full C# test code (uses test framework like NUnit, xUnit, etc.)

Tests are managed through dedicated Test Editor in Web UI, but stored as scripts in MetaScript table.

Example:

-- Test script in MetaScript table
ScriptType: "UnitTest"
ObjectType: "Test"
ObjectName: "CustomerCreationTest"
ObjectMetaId: GUID_CUSTOMER_DICTIONARY (references MetaDictionary.MetaId)
Code: "[Test] public void TestCustomerCreation() { ... }"

MetaPackage#

Installed package registry (tracks package versions, dependencies).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
Name nvarchar(200) NOT NULL Package name
Version nvarchar(50) NOT NULL Package version
Description nvarchar(1000) NULL Package description
Author nvarchar(100) NULL Package author
Installed datetime2 NOT NULL, DEFAULT GETDATE() Installation timestamp
Dependencies nvarchar(max) NULL JSON array of dependency packages

Indexes:

  • IX_MetaPackage_NameVersion (Name, Version) UNIQUE

MetaScriptHistory#

Script version history and audit trail.

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
ScriptMetaId uniqueidentifier NOT NULL References MetaScript.MetaId
Version nvarchar(50) NOT NULL Script version
Code nvarchar(max) NOT NULL Script code snapshot
Author nvarchar(100) NULL Author who created this version
Created datetime2 NOT NULL, DEFAULT GETDATE() Version creation timestamp
Comment nvarchar(1000) NULL Version comment

Indexes:

  • IX_MetaScriptHistory_ScriptMetaId (ScriptMetaId)
  • IX_MetaScriptHistory_ScriptVersion (ScriptMetaId, Version)

MetaJob#

Job definitions for Scheduler (references MetaScript).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
Name nvarchar(200) NOT NULL Job name
Description nvarchar(500) NULL Job description
ScriptMetaId uniqueidentifier NOT NULL References MetaScript.MetaId (job code)
Schedule nvarchar(200) NOT NULL CRON expression or interval
IsActive bit NOT NULL, DEFAULT 1 Active job flag
LastRun datetime2 NULL Last execution timestamp
NextRun datetime2 NULL Next scheduled execution timestamp
Created datetime2 NOT NULL, DEFAULT GETDATE() Creation timestamp
Modified datetime2 NOT NULL, DEFAULT GETDATE() Last modification timestamp

Indexes:

  • IX_MetaJob_ScriptMetaId (ScriptMetaId)
  • IX_MetaJob_IsActive (IsActive)
  • IX_MetaJob_NextRun (NextRun)

MetaMetadataVersion#

Metadata version history (snapshots of metadata objects for rollback capability).

Column Type Constraints Description
MetaId uniqueidentifier PK, NOT NULL, DEFAULT NEWID() Primary key (Guid)
ObjectType nvarchar(50) NOT NULL Object type: "Dictionary", "Document", "Register", "Service", "DataEntity", "Enum", "EDT"
ObjectMetaId uniqueidentifier NOT NULL References metadata object MetaId
Version int NOT NULL Version number (incremental, auto-increment per object)
Snapshot nvarchar(max) NOT NULL JSON snapshot of full metadata object state at this version
Created datetime2 NOT NULL, DEFAULT GETDATE() Snapshot creation timestamp
CreatedBy uniqueidentifier NULL References MetaUser.MetaId (user who made the change)
Description nvarchar(500) NULL Version description/comment
IsWorkingVersion bit NOT NULL, DEFAULT 0 Known good version flag (marks versions that work correctly)
ChangeSummary nvarchar(1000) NULL Summary of what changed in this version

Indexes:

  • IX_MetaMetadataVersion_ObjectMetaId (ObjectMetaId)
  • IX_MetaMetadataVersion_ObjectVersion (ObjectType, ObjectMetaId, Version)
  • IX_MetaMetadataVersion_IsWorkingVersion (IsWorkingVersion)

Usage:

  • System automatically creates version snapshot before any metadata change
  • Snapshots stored as JSON (complete metadata object state)
  • Enables rollback to previous versions
  • IsWorkingVersion flag allows marking known good versions (e.g., after testing)
  • Rollback operation creates new version (preserves history)

Example:

-- Version history for Customer dictionary
ObjectType: Dictionary
ObjectMetaId: GUID_CUSTOMER
- Version: 1, Created: 2025-01-01, IsWorkingVersion: true, Snapshot: {Name: "Customer", Fields: [...]}
- Version: 2, Created: 2025-01-10, IsWorkingVersion: true, Snapshot: {Name: "Customer", Fields: [...]} -- Added Email field
- Version: 3, Created: 2025-01-15, IsWorkingVersion: false, Snapshot: {Name: "Customer", Fields: [...]} -- Bug introduced
- Version: 4, Created: 2025-01-15, IsWorkingVersion: true, Snapshot: {Name: "Customer", Fields: [...]} -- Rolled back to Version 2

Runtime Business Data Tables#

Note: Runtime business data tables are generated dynamically based on metadata. They use Id (integer) as primary key, not MetaId.

Example: Customers Dictionary#

Generated from MetaDictionary named "Customers" with fields from MetaDictionaryProperty.

Column Type Constraints Description
Id int PK, IDENTITY(1,1) Primary key (integer)
Code nvarchar(50) NOT NULL, UNIQUE Customer code
Name nvarchar(200) NOT NULL Customer name
Phone nvarchar(20) NULL Phone number
AddressId int NULL References Addresses.Id (if Reference EDT field exists)
Created datetime2 NOT NULL, DEFAULT GETDATE() Creation timestamp
Modified datetime2 NOT NULL, DEFAULT GETDATE() Last modification timestamp
CreatedBy int NULL User Id who created record (optional)
ModifiedBy int NULL User Id who last modified record (optional)

Example: Invoices Document#

Generated from MetaDocumentType named "Invoices" with header fields from MetaDocumentHeaderField and line fields from MetaDocumentLineField.

Header Table: Invoices

Column Type Constraints Description
Id int PK, IDENTITY(1,1) Primary key (integer)
Number nvarchar(50) NOT NULL, UNIQUE Invoice number
Date date NOT NULL Invoice date
CustomerId int NULL References Customers.Id
TotalAmount decimal(18,2) NULL Total amount
Status nvarchar(50) NULL Invoice status
Created datetime2 NOT NULL, DEFAULT GETDATE() Creation timestamp
Modified datetime2 NOT NULL, DEFAULT GETDATE() Last modification timestamp

Lines Table: InvoiceLines

Column Type Constraints Description
Id int PK, IDENTITY(1,1) Primary key (integer)
InvoiceId int NOT NULL References Invoices.Id (no FK constraint)
LineNumber int NOT NULL Line number
ProductId int NULL References Products.Id
Quantity decimal(18,3) NULL Quantity
Price decimal(18,2) NULL Unit price
Amount decimal(18,2) NULL Line amount
Created datetime2 NOT NULL, DEFAULT GETDATE() Creation timestamp

Indexes:

  • IX_InvoiceLines_InvoiceId (InvoiceId)
  • IX_InvoiceLines_ProductId (ProductId)

Relationships Summary#

Metadata Object Relationships#

MetaModel (1) → (N) MetaDictionary, MetaDocumentType, MetaRegister, MetaService, MetaDataEntity, MetaEnum, MetaEDT, MetaScript
MetaLayer (1) → (N) MetaModel
MetaDictionary (1) → (N) MetaDictionaryProperty
MetaDocumentType (1) → (N) MetaDocumentHeaderField, MetaDocumentLine, MetaDocumentStatus
MetaDocumentLine (1) → (N) MetaDocumentLineField
MetaRegister (1) → (N) MetaRegisterHeader, MetaRegisterField
MetaEnum (1) → (N) MetaEnumValue
MetaEDT (1) → (N) MetaDictionaryProperty, MetaDocumentHeaderField, MetaDocumentLineField, MetaRegisterHeader, MetaRegisterField
MetaDictionary (1) → (N) MetaDictionary (BaseDictionaryMetaId) [Extension chain]
MetaDocumentType (1) → (N) MetaDocumentType (BaseDocumentTypeMetaId) [Extension chain]
MetaRegister (1) → (N) MetaRegister (BaseRegisterMetaId) [Extension chain]
MetaDataEntity (1) → (N) MetaDataEntity (BaseDataEntityMetaId) [Extension chain]
MetaEnum (1) → (N) MetaEnum (BaseEnumMetaId) [Extension chain]

Security Relationships#

MetaUser (1) → (N) MetaUserRole
MetaRole (1) → (N) MetaUserRole
MetaRole (1) → (N) MetaObjectPermission
MetaUser (1) → (N) MetaSession
MetaUser (1) → (N) MetaAuditLog
MetaUser (1) → (N) MetaDataPermission (user-specific)
MetaRole (1) → (N) MetaDataPermission (role-based)

Development Relationships#

MetaProject (1) → (N) MetaProjectObject
MetaScript (1) → (N) MetaScriptHistory (script version history)
MetaScript (1) → (1) MetaJob

Indexes Summary#

All tables have indexes on:

  • Primary key (MetaId or Id)
  • Foreign key columns (for join performance)
  • Frequently queried columns (Name, UserName, etc.)
  • Composite indexes for common query patterns

Database Provider Considerations#

The schema is designed to work with multiple database providers:

SQL Server#

  • Uses uniqueidentifier for Guid columns
  • Uses nvarchar for string columns
  • Uses datetime2 for timestamp columns
  • Uses IDENTITY(1,1) for auto-increment integer columns

PostgreSQL#

  • Uses uuid for Guid columns
  • Uses varchar or text for string columns
  • Uses timestamp for timestamp columns
  • Uses SERIAL or GENERATED ALWAYS AS IDENTITY for auto-increment columns

MySQL#

  • Uses char(36) or binary(16) for Guid columns
  • Uses varchar or text for string columns
  • Uses datetime for timestamp columns
  • Uses AUTO_INCREMENT for auto-increment columns

Entity Framework Core migrations handle provider-specific differences automatically.

Notes#

  1. No Foreign Key Constraints: All relationships are enforced at application level, not in database
  2. MetaId vs Id: Metadata tables use MetaId (Guid), business data tables use Id (integer)
  3. Dynamic Tables: Business data tables (Customers, Invoices, etc.) are generated dynamically based on metadata
  4. Audit Trail: All changes are logged in MetaAuditLog for compliance
  5. Versioning: Scripts have version history in MetaScriptHistory
  6. Soft Deletes: Consider adding IsDeleted flags if soft delete is needed (not shown in schema above)
Was this page helpful?