Skip to content
Laravel

Database Overview

Introduction

Let's delve into the database structure of the OTAS (Oktamam Admission System), which is built on OctoberCMS. This database comprises numerous tables, some integral to OctoberCMS and others specific to OTAS. In this section, we will exclusively focus on tables pertinent to OTAS. The database utilizes MySQL as its server.

OTAS boasts a variety of features, each with its own unique database structure. We will methodically explore the database architecture of each feature before proceeding to the next.

Note: For clarity and brevity in this document, we will reference OTAS database tables without the prefix spotlayerteam_institutionsprograms_. The tables will be referred to by their singular names, simplifying readability and understanding.

API Clients

The otas_api_clients table is designed to store API keys that authorize access to the APIs provided by OTAS, including options to set an expiration date and limit access based on the type of API key. The table includes the following columns:

  • id: An auto-incremental integer serving as the primary key.
  • active: A boolean flag indicating whether the API Key is activated (1) or disabled (0).
  • type: A string column specifying the type of the API key, with available types including:
    • otas-plugin: For API keys used within the OTAS plugin component.
    • saide: [deprecated] Previously used for integration with the Saide system, now deprecated.
    • license-key: For generating a license key for integration with the S-apply system, granting access to it.
    • agent-key: For creating an agent key to enable the agent plugin component.
  • name: The name of the system for which the API key is generated.
  • api_key: The actual API key value, consisting of 60 random characters.
  • expires_at: The expiration date of the API key.
  • created_at: The timestamp marking the creation date of the API key.
  • updated_at: The timestamp of the last update made to the API key.
  • agent_id: A nullable column for agent-key type, containing the ID of the agent for whom the API key was generated.
  • source: A nullable column for types otas-plugin or agent-key, where each application created by the plugin running with that API key will include this source value in the application's source field.

Leads

The otas_leads table is designed to capture leads generated by the OTAS plugin, offering a pathway to convert these leads into student profiles. Initially, the table collects basic information about the potential student. To convert a lead into a full student profile, additional details are required. Key columns in this table include:

  • id: Primary key, auto-incrementing.
  • sid: [deprecated] Identifier in the Saide system.
  • status: Status of the lead (e.g., pending, rejected, verified).
  • birthdate, passport_no, phone, gender, marital, nationality, address_aboard, address_inside, country, father_name, mother_name, first_name, last_name, phone_code, passport_issue_date, passport_expiry_date: These fields store personal information, some of which are mandatory for conversion to a student profile.
  • name, email: Required fields for the lead’s contact information.
  • created_at, updated_at: Track the creation and last update times.
  • rejection_reason: Reason for lead rejection, if applicable.
  • history: Records any changes made to the lead.
  • assignee: The user assigned to the lead.
  • extra_data: Holds additional data in JSON format.
  • program_id, school_id: Indicates the program and school the lead is interested in.
  • form: The form through which the lead was captured.
  • aff_code: Affiliate code, if any.
  • country_code, surname: Additional contact information required for conversion to a student profile.

This table structure supports efficient lead management and the seamless transition from lead to student within the OTAS system.