Database Schema
Primentra uses 30 SQL Server tables, organized into seven groups.
Core Data — the EAV (Entity-Attribute-Value) model
| Table | Purpose | Key columns |
|---|---|---|
Models | Top-level data domains | Id, Name, IsActive, SortOrder |
Entities | Entity types within a model | Id, ModelId, Name, TableName, SharedAcrossModels |
Attributes | Column definitions per entity | Id, EntityId, DisplayName, DataType, DomainEntityId |
EntityRows | One record per master data row | Id, EntityId, Code, Name, CreatedBy, ModifiedBy |
EntityValues | One value per attribute per row | EntityRowId, AttributeId, TextValue / IntValue / DecimalValue / DateTimeValue / DomainValue |
Auth / RBAC
| Table | Purpose | Key columns |
|---|---|---|
Users | User accounts | Id, Email, DisplayName, PasswordHash, IsActive, UserType |
Roles | Permission roles | Id, Name, IsAdmin, CanApprove |
RoleMembers | User ↔ role membership | RoleId, UserId |
Permissions | Per-role CRUD access | RoleId, Scope, TargetId, CanCreate, CanRead, CanUpdate, CanDelete, IsModerator, Level (attribute scope) |
ApiKeys | API key credentials for external REST access | Id, UserId, KeyHash, KeyPrefix, Label, ExpiresAt, CallCount, LastUsedAt |
Features
| Table | Purpose | Key columns |
|---|---|---|
IntegrationViews | SQL view definitions for external systems | Id, Name, EntityId, ViewType (hierarchy/flat) |
AuditLog | Business event history (append-only) | EntityId, Action, UserId, RecordCount, CreatedAt |
ErrorLog | Server-side technical errors (append-only) | Timestamp, Level, Method, Path, StatusCode |
UserSettings | Per-user key-value preferences | UserId, SettingKey, SettingValue |
UserFavorites | Per-user entity favorites | UserId, EntityId |
UserHiddenEntities | Per-user hidden sidebar entities | UserId, EntityId |
AppSettings | Application-wide configuration | SettingKey, SettingValue |
EntityDerivedColumns | Derived column definitions (relational path traversal) | Id, EntityId, DisplayName, AttributePath, SortOrder |
Staging & Scheduler
| Table | Purpose | Key columns |
|---|---|---|
StagingImport | Temporary staging for bulk imports | SessionId, EntityName, RowData, Status |
StagingConfigurations | Staging pipeline definitions | Id, EntityId, SourceType, ConnectionString, IsActive |
StagingFieldRules | Per-field mapping rules for staging pipelines | Id, StagingConfigId, SourceField, TargetAttributeId, TransformType |
StagingBatches | Execution history for staging runs | Id, StagingConfigId, Status, RowsProcessed, StartedAt, CompletedAt |
StagingErrorDetails | Per-row error details for failed staging rows | Id, BatchId, RowIndex, ErrorMessage, RowData |
ErrorCodes | Lookup table for standardized staging error codes | Code, Severity, Description |
ImportDomainFKStaging | Temporary staging for domain FK resolution during model import | Id, SessionId, EntityName, AttributeName, DomainEntityName |
SchedulerConfigurations | Scheduled job definitions | Id, Name, CronExpression, IsActive, StagingConfigId |
SchedulerLog | Execution log for scheduled jobs | Id, SchedulerConfigId, Status, StartedAt, CompletedAt, Message |
System
| Table | Purpose | Key columns |
|---|---|---|
SystemConfig | License and trial state | ConfigKey, ConfigValue (DATETIME2) |
SchemaVersion | Currently deployed schema version | Version, UpdatedAt |
Approval Workflow
| Table | Purpose | Key columns |
|---|---|---|
ApprovalRequests | Approval workflow requests | Id, EntityId, SubmittedByUserId, Status, SubmitterNote, Version |
ApprovalRows | Individual row snapshots within a request | Id, ApprovalRequestId, EntityRowId, Operation, RowStatus, SnapshotData |
ApprovalReviews | Reviewer decisions on approval requests | Id, ApprovalRequestId, ReviewedByUserId, Decision, Comment |
EntityApprovers | Per-entity approver assignments | EntityId, UserId, ReceivesEmail |
All tables (except append-only logs) include CreatedAt and ModifiedAt columns with automatic GETUTCDATE() defaults. Every write to the database goes through stored procedures — the API never sends raw SQL.
Foreign key cascade behavior
When records are deleted through the application, stored procedures handle cleanup in the correct order. The following cascade actions are also defined at the database level as a safety net:
| Relationship | On delete |
|---|---|
Entity deleted → its IntegrationViews | Removed automatically (CASCADE) |
Entity deleted → its AuditLog entries | EntityId set to NULL — entries are preserved |
Entity deleted → its Attributes | Removed automatically (CASCADE) |
Attribute deleted → its EntityValues | Removed automatically (CASCADE) |
Row deleted → its EntityValues | Removed automatically (CASCADE) |
Role deleted → its Permissions and RoleMembers | Removed automatically (CASCADE) |
Entity deleted → its EntityApprovers | Removed automatically (CASCADE) |
Entity deleted → its EntityDerivedColumns | Removed automatically (CASCADE) |
Entity deleted → its UserHiddenEntities | Removed automatically (CASCADE) |
User deleted → their UserHiddenEntities | Removed automatically (CASCADE) |
Approval request deleted → its ApprovalRows and ApprovalReviews | Removed automatically (CASCADE) |
Some relationships cannot use cascades because SQL Server rejects configurations where two paths converge on the same table. EntityRows, Entities, and Models do not have cascade deletes for this reason — the stored procedures handle them in the correct dependency order.
Do not delete directly from the database
It is technically possible to delete rows directly in SQL Server Management Studio, but this is strongly discouraged in production:
- No audit entry is written. Primentra's audit log only records actions that go through the application. A direct
DELETE FROM Entitiesleaves no trace of who deleted what or when. - Permissions become orphaned. The
Permissionstable references models and entities by ID without a foreign key onTargetId. Deleting an entity directly leaves behind permission records pointing to a non-existent entity. These orphaned rows accumulate silently. - Audit log entries lose context. The
ON DELETE SET NULLonAuditLog.EntityIdkeeps the entries but clears the entity reference, making historical records harder to attribute.
If you must remove data at the database level (for example, during emergency maintenance), use the stored procedures usp_Entity_Delete and usp_Model_Delete directly. They clean up in the correct order, handle permissions, and write an audit entry — all in a single atomic transaction.