Understanding the Database Structure of the Batoi RAD Framework

Back to Help Articles
Gain an in-depth understanding of the system tables that are set up when installing the Batoi RAD Framework. This comprehensive guide details the purpose, fields, and attributes of each table, serving as a robust foundation for your application development journey.

When we embark on the journey of utilizing the Batoi RAD Framework for our application development, one of the first steps involves an understanding of the database structure it sets up. This article aims to provide a comprehensive walkthrough of the 19 key database tables (each table name starts with s_) that get created when we install the Batoi RAD Framework. These tables serve as the backbone for storing application-specific data and ensuring smooth operations.

Common Fields along with attributes for all the tables:

Each table comes pre-configured with certain common fields that provide crucial information:

  • id: Stores a unique bigint(20) identifier.
  • uid: Uses a char(36) to universally identify records.
  • livestatus: An Enum field representing the active status of a record, with values like 0 (inactive), 1 (active), 2 (archive), and 3 (suspended).
  • versioncode: An int(11) that keeps track of record versions.
  • wf_status: Stores workflow state of the record.
  • tenant_id: If the table stores SaaS data, it’s used, else the value stored here is 0.
  • createdby & updatedby: Both bigint(20) fields that identify who created or last updated a record.
  • createstamp & updatestamp: Timestamps indicating when a record was created or last modified.

In-Depth Table Descriptions

Below are detailed explanations of each table, along with their fields and attributes.

Table Name Description Fields along with attributes and explanation
s_api The API user information is stored here.
  • s_entity_id - bigint(20)
  • s_name - varchar(200)
  • s_api_key - varchar(200)
  • s_api_secret_hash - varchar(200)
  • s_tenant_id - varchar(200)
  • s_role_id - bigint(20)
  • updatedby - bigint(20)
  • s_access_ips - bigint(20)
s_config Both systems and application parameters and their values are stored here.
  • s_config_handle - varchar(200)
  • s_config_value - varchar(200)
  • s_config_origin - Enum(‘S’, ‘A’), [S=System, A=Application]
  • s_sort_order - bigint(20)
s_data_field The fields of the application data tables are stored here.
  • s_service_id - bigint(20)
  • s_field_name - varchar(255)
  • s_field_label - text
  • s_sort_order - bigint(20)
  • s_field_group_id - bigint(20)
  • s_help_text - text
  • s_field_type_id - bigint(20)
  • s_field_range - varchar(255)
  • s_field_req - Enum(‘M’, ‘O’), [M=Manual, O=Optional]
  • s_data_enc - Enum(‘Y’, ‘N’), [Y=Yes, N=No]
s_data_field_group The group information, if any, of data fields are stored here.
  • s_service_id - bigint(20)
  • s_group_title - varchar(255)
  • s_description - text
s_data_field_type The master table for type information for data fields
  • s_type - varchar(255)
  • s_definition - json
  • s_sensitivity - Enum(‘Y’, ‘N’), [Y=Yes, N=No]
s_data_method The methods of the microcontroller are stored here.
  • s_service_id - bigint(20)
  • s_method_name - varchar(255)
  • s_description - text
  • s_method_type - Enum(‘S’, ‘C’), [S=Standard, C=Custom]
  • s_custom_query - text
s_data_wf The workflow state information is stored here.
  • s_service_id - bigint(20)
  • s_wfstate_title - varchar(255)
  • s_sort_order - bigint(20)
s_entity The entity information is stored here and used by user and API tables by reference.
  • s_type - Enum(‘U’, ‘A’), [U=User, A=Application]
s_entity_session The user and API sessions are stored here.
  • s_entity_id - bigint(20)
  • s_entity_sub_id - bigint(20)
  • s_session_key - char(64)
  • s_device_type - varchar(255)
  • s_operating_system - varchar(255)
  • s_browser - varchar(255)
  • s_ip - varchar(45)
  • s_otp - varchar(10)
s_external_notification The external notifications (like email and SMS logs) are stored here.
  • s_user_id - bigint(20)
  • s_message - text
  • s_event_details - json
  • s_destination - varchar(255)
  • s_sent_status - tinyint(1)
  • s_send_timestamp - timestamp
  • s_notification_type - Enum(‘email’, ‘sms’, ‘wa’), [email=Email, sms=Message, wa=WhatsApp]
s_ms The information of microservices are stored here.
  • s_name - varchar(255)
  • s_description - text
  • s_type - Enum(‘STA’, ‘DYN’, ‘ID’, ‘UID’), [STA=Static, DYN=Dynamic, ID=Primary Key, UID=Universal ID]
  • s_is_saas - Enum(‘Y’, ‘N’), [Y=Yes, N=No]
  • s_access_scope - Enum(‘public’, ‘private’)
  • s_access_role_ids - varchar(255)
  • s_default_route_id - bigint(20)
  • s_tpl_name - varchar(255)
s_mscontroller Information about microservice controllers are stored here.
  • s_ms_id - bigint(20)
  • s_name - varchar(12)
  • s_description - text
  • s_type - Enum(‘BL’, ‘DM’), [BL=Business Logic, DM=Data Method]
s_msroute Information of microservice routes are stored here.
  • s_ms_id - varchar(255)
  • s_name - text
  • s_description - Enum(‘STA’, ‘DYN’, ‘ID’, ‘UID’), [STA=Static, DYN=Dynamic, ID=Primary Key, UID=Universal ID]
  • s_degree - Enum(‘Y’, ‘N’), [Y=Yes, N=No]
  • s_entity_scope - Enum(‘public’, ‘private’)
  • s_service_definition - varchar(255)
  • s_access_role_ids - varchar(255)
s_notification The in-app notification logs are stored here.
  • s_user_id - bigint(20)
  • s_message - text
  • s_event_details - json
  • s_further_link - varchar(255)
  • s_is_read - tinyint(1)
s_queue The job queues are stored here.
  • s_queue_title - varchar(255)
  • s_queue_script_name - varchar(255)
  • s_execution_frequency - Enum(‘1 min’, ‘5 min’, ‘15 min’, ‘30 min’, ‘1 h’, ‘2h’, ‘4h’, ‘6h’, ‘8h’, ‘12h’, ‘1d’, ‘1w’, ‘2w’, ‘1m’, ‘2m’, ‘3m’, ‘4m’, ‘6m’, ‘1y’)
  • s_last_executed - timestamp
  • s_next_execution - timestamp
  • s_queue_status - Enum(‘SUCCESS’, ‘FAILURE’)
  • s_error_message - text
s_role The information of user roles are stored here.
  • s_role_name - varchar(200)
  • s_saas - Enum(‘Y’, ‘N’), [Y=Yes, N=No]
  • s_default_route_id - bigint(20)
s_sso_provider The information of allowed SSO providers is stored here.
  • s_provider_name - varchar(200)
  • s_sso_configuration - json
s_tenant Information of SaaS tenants are stored here.
  • s_name - varchar(200)
s_user Information of users is stored here.
  • s_entity_id - bigint(20)
  • s_login_mode - Enum(‘GL’, ‘TW’, ‘SE’), [GL=Google, TW=Twitter, SE=Self]
  • s_username - varchar(20)
  • s_password - varchar(200)
  • s_fullname - varchar(200)
  • s_email - varchar(200)
  • s_mobile - varchar(20)
  • s_enablemfa - Enum(‘Y’, ‘N’), [Y=Yes, N=No]
  • s_tenant_id - bigint(20)
  • s_role_id - bigint(20)
  • s_access_ips - text
  • s_agreement_signed - Enum(‘Y’, ‘N’), [Y=Yes, N=No]

These tables are critical for both built-in and custom applications using the Batoi RAD Framework. Whether you're looking to build a scalable e-commerce platform, a high-utility app, or anything in between, having a solid grasp of these tables will serve as a strong foundation for your development journey.

Conclusion

Understanding the Batoi RAD Framework’s database architecture is key to effectively leveraging its capabilities for robust, scalable, and secure application development. As we continue to work on projects, the knowledge of these tables and their functions becomes invaluable for efficient development and debugging.

Back to Help Articles