The Custom Fields functionality allows users to define custom fields, organize them into sections, assign options for fields that require predefined choices, and store values for these fields associated with various entities in the application.

​
Entities and Attributes

​
1. CustomFieldSection

Purpose: Groups custom fields into sections for better organization and presentation within the application. Table Name: custom_field_sections Attributes:
AttributeTypeConstraintsDescription
idBIGINT UNSIGNEDPrimary KeyUnique identifier for the custom field section.
tenant_idBIGINT UNSIGNEDForeign Key, Nullable, IndexedReferences tenants.id if multitenancy is enabled.
codeVARCHAR(255)Not NullUnique code for the custom field section.
nameVARCHAR(255)Not NullHuman-readable name of the custom field section.
typeVARCHAR(255)Not NullType of the section (e.g., 'section', 'fieldset', 'headless').
entity_typeVARCHAR(255)Not NullThe entity (model) type associated with this section (e.g., 'Customer', 'Order').
sort_orderBIGINT UNSIGNEDNullableOrder for displaying sections in the user interface.
descriptionVARCHAR(255)NullableOptional description providing more details about the section.
activeBOOLEANDefault TRUEIndicates if the section is active and should be displayed.
system_definedBOOLEANDefault FALSEIndicates if the section is system-defined and may have restrictions on changes.
created_atTIMESTAMPDefault CURRENT_TIMESTAMPRecord creation timestamp.
updated_atTIMESTAMPDefault CURRENT_TIMESTAMP ON UPDATERecord update timestamp.
Constraints:
  • Primary Key: id
  • Unique Constraint: (entity_type, code, tenant_id) (if tenant_id is present)
  • Foreign Key: tenant_id references tenants.id (if multitenancy is enabled)
  • Indexes: tenant_id (for query optimization)

​
2. CustomField

Purpose: Defines custom fields that can be associated with different entity types in the application. Table Name: custom_fields Attributes:
AttributeTypeConstraintsDescription
idBIGINT UNSIGNEDPrimary KeyUnique identifier for the custom field.
custom_field_section_idBIGINT UNSIGNEDForeign Key, NullableReferences custom_field_sections.id. Groups the custom field under a section.
widthVARCHAR(255)NullableDefines the display width of the field in the UI (e.g., 'full', 'half').
tenant_idBIGINT UNSIGNEDForeign Key, Nullable, IndexedReferences tenants.id if multitenancy is enabled.
codeVARCHAR(255)Not NullUnique code for the custom field.
nameVARCHAR(255)Not NullHuman-readable name of the custom field.
typeVARCHAR(255)Not NullData type of the custom field (e.g., 'string', 'integer', 'boolean', 'date', 'select').
lookup_typeVARCHAR(255)NullableType for lookup fields or relational associations.
entity_typeVARCHAR(255)Not NullThe entity (model) type associated with this custom field (e.g., 'Customer', 'Order').
sort_orderBIGINT UNSIGNEDNullableOrder for displaying fields in the user interface.
validation_rulesJSONNullableJSON-formatted validation rules (e.g., {"required": true, "max": 255}).
activeBOOLEANDefault TRUEIndicates if the custom field is active and should be displayed.
system_definedBOOLEANDefault FALSEIndicates if the field is system-defined and may have restrictions on changes.
created_atTIMESTAMPDefault CURRENT_TIMESTAMPRecord creation timestamp.
updated_atTIMESTAMPDefault CURRENT_TIMESTAMP ON UPDATERecord update timestamp.
Constraints:
  • Primary Key: id
  • Unique Constraint: (code, entity_type, tenant_id) (if tenant_id is present)
  • Foreign Keys:
    • tenant_id references tenants.id (if multitenancy is enabled)
    • custom_field_section_id references custom_field_sections.id
  • Indexes: tenant_id, custom_field_section_id (for query optimization)

​
3. CustomFieldOption

Purpose: Stores predefined options for custom fields that require selectable choices. Table Name: custom_field_options Attributes:
AttributeTypeConstraintsDescription
idBIGINT UNSIGNEDPrimary KeyUnique identifier for the custom field option.
tenant_idBIGINT UNSIGNEDForeign Key, Nullable, IndexedReferences tenants.id if multitenancy is enabled.
custom_field_idBIGINT UNSIGNEDForeign KeyReferences custom_fields.id.
nameVARCHAR(255)NullableOption name or value.
sort_orderBIGINT UNSIGNEDNullableOrder for displaying options in the user interface.
created_atTIMESTAMPDefault CURRENT_TIMESTAMPRecord creation timestamp.
updated_atTIMESTAMPDefault CURRENT_TIMESTAMP ON UPDATERecord update timestamp.
Constraints:
  • Primary Key: id
  • Unique Constraint: (custom_field_id, name, tenant_id) (if tenant_id is present)
  • Foreign Keys:
    • tenant_id references tenants.id (if multitenancy is enabled)
    • custom_field_id references custom_fields.id (with CASCADE ON DELETE)
  • Indexes: tenant_id, custom_field_id (for query optimization)

​
4. CustomFieldValue

Purpose: Stores the values assigned to entities for each custom field. Table Name: custom_field_values Attributes:
AttributeTypeConstraintsDescription
idBIGINT UNSIGNEDPrimary KeyUnique identifier for the custom field value.
tenant_idBIGINT UNSIGNEDForeign Key, Nullable, IndexedReferences tenants.id if multitenancy is enabled.
entity_typeVARCHAR(255)Not NullThe entity (model) type associated with this custom field value (e.g., 'Customer', 'Order').
entity_idBIGINT UNSIGNEDNot NullThe ID of the entity instance.
custom_field_idBIGINT UNSIGNEDForeign KeyReferences custom_fields.id.
string_valueVARCHAR(255)NullableValue when the custom field type is string.
text_valueTEXTNullableValue when the custom field type is text.
boolean_valueBOOLEANNullableValue when the custom field type is boolean.
integer_valueINTEGERNullableValue when the custom field type is integer.
float_valueDOUBLENullableValue when the custom field type is float.
date_valueDATENullableValue when the custom field type is date.
datetime_valueDATETIMENullableValue when the custom field type is datetime.
json_valueJSONNullableValue when the custom field type is JSON or for storing array of values.
created_atTIMESTAMPDefault CURRENT_TIMESTAMPRecord creation timestamp.
updated_atTIMESTAMPDefault CURRENT_TIMESTAMP ON UPDATERecord update timestamp.
Constraints:
  • Primary Key: id
  • Unique Constraint: (entity_type, entity_id, custom_field_id, tenant_id) (if tenant_id is present)
  • Foreign Keys:
    • tenant_id references tenants.id (if multitenancy is enabled)
    • custom_field_id references custom_fields.id (with CASCADE ON DELETE)
  • Indexes:
    • tenant_id, entity_type, entity_id, custom_field_id (for query optimization)
  • Polymorphic Association: Uses entity_type and entity_id to associate with any entity in the application.

​
Relationships and Associations

  • CustomFieldSection (1) --- (M) CustomField
    • Type: One-to-Many
    • Foreign Key in CustomField: custom_field_section_id references custom_field_sections.id
    • On Delete: Application-defined (nullable foreign key allows for fields without sections)
  • CustomField (M) --- (1) CustomFieldSection
    • Type: Many-to-One
    • A custom field may belong to a custom field section.
  • CustomField (1) --- (M) CustomFieldOption
    • Type: One-to-Many
    • Foreign Key in CustomFieldOption: custom_field_id references custom_fields.id
    • On Delete: Cascade (deleting a custom field deletes its options)
  • CustomFieldValue (M) --- (1) CustomField
    • Type: Many-to-One
    • Foreign Key in CustomFieldValue: custom_field_id references custom_fields.id
    • On Delete: Cascade (deleting a custom field deletes its values)
  • CustomFieldValue (M) --- (1) Entity (Polymorphic)
    • Type: Polymorphic Many-to-One
    • Polymorphic Fields: entity_type and entity_id
    • Associates custom field values with any entity in the application.

​
Constraints and Indexes

​
Unique Constraints

  • CustomFieldSection
    • Unique Index: (entity_type, code, tenant_id) (if tenant_id is present)
  • CustomField
    • Unique Index: (code, entity_type, tenant_id) (if tenant_id is present)
  • CustomFieldOption
    • Unique Index: (custom_field_id, name, tenant_id) (if tenant_id is present)
  • CustomFieldValue
    • Unique Index: (entity_type, entity_id, custom_field_id, tenant_id) (if tenant_id is present)

​
Foreign Keys and Indexes

  • Foreign Keys
    • tenant_id
      • References tenants.id in all tables where multitenancy is enabled.
    • custom_field_section_id
      • References custom_field_sections.id in custom_fields.
    • custom_field_id
      • References custom_fields.id in custom_field_options and custom_field_values.
  • Indexes
    • tenant_id
      • Indexed in all tables for query optimization in a multitenant environment.
    • Other Foreign Keys
      • Indexed to speed up joins and lookups.

​
Multitenancy Support

  • Tenant Isolation
    • Data is scoped per tenant by including tenant_id in each table.
    • Unique constraints include tenant_id to prevent conflicts across tenants.
    • All queries should filter by tenant_id to ensure data isolation.
  • Foreign Key Constraints
    • tenant_id references tenants.id (assuming a tenants table exists).
  • Conditional Inclusion
    • The tenant_id column is added only if multitenancy is enabled (Utils::isTenantEnabled()).
    • This allows flexibility in deploying the application in both multitenant and single-tenant environments.
  • Indexes
    • Indexes on tenant_id optimize tenant-specific queries.

​
Example Usage

​
Scenario: Storing Additional Customer Information

  1. Define a Custom Field Section
    • CustomFieldSection with:
      • code: 'contact_details'
      • name: 'Contact Details'
      • type: 'additional'
      • entity_type: 'Customer'
      • sort_order: 1
      • description: 'Additional contact information for the customer.'
  2. Define Custom Fields within the Section
    • CustomField for 'alternate_email':
      • custom_field_section_id: Reference to 'contact_details' section
      • code: 'alternate_email'
      • name: 'Alternate Email'
      • type: 'string'
      • entity_type: 'Customer'
      • validation_rules: {"email": true}
    • CustomField for 'preferred_contact_time':
      • custom_field_section_id: Reference to 'contact_details' section
      • code: 'preferred_contact_time'
      • name: 'Preferred Contact Time'
      • type: 'select'
      • entity_type: 'Customer'
      • Define options in CustomFieldOption:
        • Option 1:
          • custom_field_id: Reference to 'preferred_contact_time'
          • name: 'Morning'
          • sort_order: 1
        • Option 2:
          • custom_field_id: Reference to 'preferred_contact_time'
          • name: 'Afternoon'
          • sort_order: 2
        • Option 3:
          • custom_field_id: Reference to 'preferred_contact_time'
          • name: 'Evening'
          • sort_order: 3
  3. Store Values for a Customer Entity
    • CustomFieldValue for 'alternate_email':
      • entity_type: 'Customer'
      • entity_id: 1001 (ID of the customer)
      • custom_field_id: Reference to 'alternate_email'
      • string_value: 'customer.alt@example.com'
    • CustomFieldValue for 'preferred_contact_time':
      • entity_type: 'Customer'
      • entity_id: 1001
      • custom_field_id: Reference to 'preferred_contact_time'
      • string_value: 'Afternoon'