Design entity schemas

Table of Contents

1. Overview
2. Schema Creation Best Practices
3. Core Schema Types
4. Entity Definition Management
5. Property Management
6. Schema Operations
7. Best Practices

Overview

The Pikbase platform provides a comprehensive framework for defining and managing data schemas through entity definitions. This guide covers how to work with GSB schema components to create, read, update, and delete data tables and their properties.

Schema Creation Best Practices

Creating Initial Schema

When creating an initial schema with multiple related entity definitions:

1. Create entity definitions without reference types first:

   • Build all your base entity definitions with standard properties (string, number, etc.)
   • Save these entities before adding reference properties

2. Add reference properties in a second pass:

   • After all entity definitions exist, add reference properties
   • GSB automatically manages the bidirectional relationship

Reference Property Management

When adding reference properties between entities:

1. Add reference to only one entity:

   • Only add the reference property to one of the related entity definitions
   • Specify the correct refEntDef_id and refEntPropName
   • GSB automatically adds the corresponding reference property to the other definition

2. Foreign key handling:

   • For single relationships (OneToOne, ManyToOne), GSB automatically adds an _id property
   • For example, adding customer ref property to an Order entity will automatically create customer_id field

3. Bidirectional management:

   • When you delete a reference property, GSB automatically removes:
     • The corresponding reference property in the related entity
     • Any automatically created foreign key fields

Example

// Example: Customer has Orders, Order has Customer

// 1. First create basic entity definitions
await entityDefService.createDataTable(
  'Customer',                    
  'Customer Information',        
  'Stores customer data'         
);

await entityDefService.createDataTable(
  'Order',                    
  'Order Information',        
  'Stores order data'         
);

// 2. Then add the reference property to just one entity
await entityDefService.addColumn(
  'customer-entity-id', // Customer entity
  {
    name: 'orders',
    title: 'Orders',
    description: 'Customer orders',
    definition_id: '924acba8-58c5-4881-940d-472ec01eba5f', // Reference type
    refEntDef_id: 'order-entity-id', // Order entity
    refEntPropName: 'customer', // Name of property in Order entity
    refType: RefType.OneToMany
  }
);

// GSB automatically:
// 1. Adds 'customer' property to Order entity
// 2. Adds 'customer_id' to Order entity for the database relationship

Core Schema Types

Entity Definition (GsbEntityDef)

The GsbEntityDef interface represents a data table in the GSB system:

export interface GsbEntityDef {
  id?: string;                        // Unique identifier
  name?: string;                      // Entity name (must be unique)
  title?: string;                     // Display title
  description?: string;               // Description
  dbTableName?: string;               // Database table name
  publicAccess?: boolean;             // Whether entity is publicly accessible
  activityLogLevel?: ActivityLogLevel; // Level of activity logging
  properties?: GsbProperty[];         // Array of properties (columns)
  isActive?: boolean;                 // Whether entity is active
  isDeleted?: boolean;                // Whether entity is deleted
  createDate?: Date;                  // Creation date (system-managed)
  lastUpdateDate?: Date;              // Last update date (system-managed)
  createdBy_id?: string;              // Creator ID (system-managed)
  lastUpdatedBy_id?: string;          // Last updater ID (system-managed)
  permissions?: GsbPermission[];      // Entity permissions
  workflowTriggers?: GsbWorkflowTrigger[]; // Associated workflow triggers
}

Property (GsbProperty)

The GsbProperty interface represents a column in a data table:

export interface GsbProperty {
  id?: string;                     // Unique identifier
  name?: string;                   // Property name (must be unique within entity)
  title?: string;                  // Display title
  description?: string;            // Description
  definition_id?: string;          // Reference to property definition (data type)
  orderNumber?: number;            // Display order
  isRequired?: boolean;            // Whether property is required
  isSearchable?: boolean;          // Whether property is searchable
  isUnique?: boolean;              // Whether property must have unique values
  isPrimaryKey?: boolean;          // Whether property is a primary key
  isIndexed?: boolean;             // Whether property is indexed
  maxLength?: number;              // Maximum length (for strings)
  defaultValue?: string;           // Default value
  
  // Reference properties
  refEntDef_id?: string;           // Referenced entity definition ID
  refEntPropName?: string;         // Property name in referenced entity
  refType?: RefType;               // Reference type (OneToOne, OneToMany, etc.)
  
  // UI control properties
  formModes?: number;              // Form modes where property is visible
  listScreens?: ScreenType;        // List screens where property is visible
  
  // Additional properties
  enum_id?: string;                // Enum ID (for enum properties)
  isMultiLingual?: boolean;        // Whether property supports multiple languages
  isEncrypted?: boolean;           // Whether property value is encrypted
  regex?: string;                  // Validation regex pattern
  
  // System properties
  isDefault?: boolean;             // Whether it's a default property
  type?: string;                   // Property type name
}

Property Definition (GsbPropertyDef)

The GsbPropertyDef interface represents a data type definition:

export interface GsbPropertyDef {
  id: string;                      // Unique identifier
  dataType: DataType;              // Data type enum value
  title: string;                   // Display title
  name: string;                    // Type name
  description?: string;            // Description
  maxLength?: number;              // Maximum length
  scale?: number;                  // Scale (for decimal numbers)
  regex?: string;                  // Default validation regex
  usage?: number;                  // Usage counter
  createDate?: Date;               // Creation date
  lastUpdateDate?: Date;           // Last update date
  defaultControlComponent?: {      // Default UI component
    title: string;
    id: string;
  };
}

Entity Definition Management

Creating an Entity Definition

To create a new data table, use the EntityDefService:

import { EntityDefService } from '@gsb-core/core';

const entityDefService = EntityDefService.getInstance();

// Create a basic data table
const tableId = await entityDefService.createDataTable(
  'Customer',                    // Table name
  'Customer Information',        // Display title
  'Stores customer data'         // Description
);

// Create a more complex entity definition
const entityDef: GsbEntityDef = {
  name: 'Product',
  title: 'Product Catalog',
  description: 'Product information and inventory data',
  properties: [
    // Default properties will be added automatically
    // Add custom properties
    {
      name: 'price',
      title: 'Price',
      description: 'Product price',
      definition_id: '35efcf9c-fff0-44d4-8972-73a9a32b93fa', // Number type
      isRequired: true,
      isSearchable: false,
      orderNumber: 10
    },
    {
      name: 'category',
      title: 'Category',
      description: 'Product category',
      definition_id: 'c6c34bf3-f51b-4e69-a689-b09847be74b9', // String type
      isSearchable: true,
      orderNumber: 11
    }
  ]
};

const entityId = await entityDefService.createEntityDef(entityDef);

Default Properties

1. id - Primary key (UUID), Required
2. title - Display title, better to define automated form builders use this field
3. createdBy - User who created the record (If a property with this name is defined GSB will atuomatically set its value)
4. lastUpdatedBy - User who last updated the record (If a property with this name is defined GSB will atuomatically set its value)
5. createDate - Creation timestamp (If a property with this name is defined GSB will atuomatically set its value)
6. lastUpdateDate - Last update timestamp (If a property with this name is defined GSB will atuomatically set its value)

Retrieving Entity Definitions

// Get by ID
const entityDef = await entityDefService.getEntityDefById('entity-id');

// Get by name
const customerTable = await entityDefService.getDataTableByName('Customer');

// Get all tables with pagination
const { entityDefs, totalCount } = await entityDefService.getEntityDefs(1, 10);

// Search for tables
const { entityDefs, totalCount } = await entityDefService.searchEntityDefs('customer', 1, 10);

// Get all tables
const allTables = await entityDefService.getAllDataTables();

Updating Entity Definitions

// Update an entity definition
const entityDef = await entityDefService.getEntityDefById('entity-id');
if (entityDef) {
  entityDef.title = 'Updated Title';
  entityDef.description = 'Updated description';
  
  const success = await entityDefService.updateEntityDef(entityDef);
}

Deleting Entity Definitions

// Soft delete (sets isDeleted flag)
const success = await entityDefService.deleteEntityDef('entity-id');

// Permanent delete (removes table and data)
const success = await entityDefService.permanentlyDeleteDataTable('entity-id');

Property Management

Adding Properties

// Add a simple string property
await entityDefService.addColumn(
  'entity-id',
  {
    name: 'address',
    title: 'Address',
    description: 'Customer address',
    definition_id: 'c6c34bf3-f51b-4e69-a689-b09847be74b9', // String type
    isSearchable: true
  }
);

// Add a reference property
await entityDefService.addColumn(
  'entity-id',
  {
    name: 'category',
    title: 'Category',
    description: 'Product category',
    definition_id: '924acba8-58c5-4881-940d-472ec01eba5f', // Reference type
    refEntDef_id: 'category-entity-id',
    refEntPropName: 'products',
    refType: RefType.OneToMany
  }
);

Common Property Types

GSB provides several pre-defined property types:

Type	Definition ID	Description
ID	5c0aa76f-9c32-4e7e-a4bc-b56e93877883	Unique identifier
String	c6c34bf3-f51b-4e69-a689-b09847be74b9	Text string
Number	35efcf9c-fff0-44d4-8972-73a9a32b93fa	Numeric value
Boolean	7868afdf-2709-45be-87e3-87de8d35f30f	True/false value
DateTime	12e647e0-ebd2-4ec2-a4e3-82c1dfe07da2	Date and time
Reference	924acba8-58c5-4881-940d-472ec01eba5f	Entity reference
Enum	7bf08f4f-7de0-469e-bbfb-f4c43762f4d7	Enumerated value
RichText	e07f578e-2705-49c1-b97f-3ca5963c67c0	Rich text content
Email	df7ce94b-d59c-4b67-8519-aa4c98ab477c	Email address
Password	7291fbc2-a7cf-4713-a876-0cff085cc035	Password field

Removing Properties

// Remove a property by name
await entityDefService.removeColumn('entity-id', 'propertyName');

// Remove a property by ID
await entityDefService.removeColumn('entity-id', 'property-id');

Schema Operations

Checking Name Uniqueness

Before creating a new entity or property, check if the name is already used:

// Check entity name uniqueness
const { entityDefs } = await entityDefService.checkNameUniqueness('Customer');
const isNameUnique = entityDefs.length === 0;

// Check reference property name uniqueness
const { isValid, validationMessage } = await entityDefService.checkRefPropNameUniqueness(
  'products',
  'category-entity-id'
);

Working with References

GSB supports different types of entity relationships:

enum RefType {
  OneToOne = 1,
  OneToMany = 2,
  ManyToOne = 3,
  ManyToMany = 4
}

When creating a reference property:

1. Set definition_id to the Reference type ID
2. Set refEntDef_id to the referenced entity's ID
3. Set refEntPropName to create a back-reference property in the referenced entity
4. Set refType to define the relationship type

Example:

// Create a one-to-many relationship from Category to Product
await entityDefService.addColumn(
  'product-entity-id',
  {
    name: 'category',
    title: 'Category',
    definition_id: '924acba8-58c5-4881-940d-472ec01eba5f', // Reference type
    refEntDef_id: 'category-entity-id',
    refEntPropName: 'products', // Creates a 'products' property in Category entity
    refType: RefType.ManyToOne
  }
);

Best Practices

Entity Definition Naming

1. Use PascalCase for entity names: Customer, ProductCategory, OrderItem
2. Use singular nouns: Product instead of Products
3. Be descriptive but concise: CustomerAddress instead of CustAddr or CustomerAddressInformation
4. Avoid special characters: Use only letters, numbers, and underscores
5. Start with a letter: Entity names must start with a letter

Property Naming

1. Use camelCase for property names: firstName, orderDate, productCategory
2. Be descriptive: customerAddress instead of custAddr
3. Use consistent naming patterns: createDate/updateDate instead of mixing createDate/modifiedOn
4. Prefix boolean properties with 'is' or 'has': isActive, hasAttachments

Schema Design

1. Normalize appropriately: Break down complex entities into related tables
2. Use references instead of duplicating data: Link to a Customer entity instead of duplicating customer fields
3. Add appropriate indexes: Mark frequently searched fields as isIndexed: true
4. Set searchable fields: Mark fields that should be included in search as isSearchable: true
5. Define required fields: Mark mandatory fields as isRequired: true
6. Set appropriate field lengths: Define maxLength for string fields

Performance Considerations

1. Cache entity definitions: GSB automatically caches entity definitions
2. Limit the number of properties: Too many columns can impact performance
3. Use appropriate data types: Use the most specific type for each property
4. Index wisely: Only index fields used in filters and sorts
5. Use reference relationships appropriately: Choose the right relationship type

Security Best Practices

1. Set appropriate permissions: Define who can view and modify each entity
2. Mark sensitive fields as encrypted: Use isEncrypted: true for sensitive data
3. Use publicAccess flag carefully: Only set publicAccess: true when necessary
4. Implement field-level security: Control which users can see specific fields
5. Audit important changes: Set appropriate activityLogLevel