YeboLearn Database Models

Complete schema documentation with all fields, types, and relationships.

Core Models

User

The central authentication and identity model.

CREATE TABLE users (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  school_id       UUID REFERENCES schools(id),
  email           VARCHAR(255) NOT NULL,
  password_hash   VARCHAR(255) NOT NULL,
  first_name      VARCHAR(100) NOT NULL,
  last_name       VARCHAR(100) NOT NULL,
  phone           VARCHAR(20),
  role            user_role NOT NULL,
  is_active       BOOLEAN DEFAULT true,
  avatar_url      VARCHAR(500),
  
  -- Security
  token_version           INTEGER DEFAULT 0,
  failed_login_attempts   INTEGER DEFAULT 0,
  account_locked_until    TIMESTAMP,
  password_changed_at     TIMESTAMP,
  last_login              TIMESTAMP,
  
  -- Timestamps
  created_at      TIMESTAMP DEFAULT NOW(),
  updated_at      TIMESTAMP DEFAULT NOW(),
  
  UNIQUE(email, school_id)
);

-- Enum for role
CREATE TYPE user_role AS ENUM (
  'student',
  'teacher', 
  'parent',
  'school_admin',
  'super_admin'
);

School

Multi-tenant school organization.

CREATE TABLE schools (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name            VARCHAR(255) NOT NULL,
  slug            VARCHAR(100) UNIQUE NOT NULL,
  country_code    VARCHAR(3) NOT NULL,
  address         TEXT,
  phone           VARCHAR(20),
  email           VARCHAR(255),
  logo_url        VARCHAR(500),
  
  -- Settings
  settings        JSONB DEFAULT '{}',
  subscription_tier VARCHAR(50) DEFAULT 'free',
  subscription_expires_at TIMESTAMP,
  
  -- Status
  is_active       BOOLEAN DEFAULT true,
  onboarded_at    TIMESTAMP,
  
  created_at      TIMESTAMP DEFAULT NOW(),
  updated_at      TIMESTAMP DEFAULT NOW()
);

Student

Student profile linked to user.

CREATE TABLE students (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id         UUID UNIQUE REFERENCES users(id),
  school_id       UUID NOT NULL REFERENCES schools(id),
  
  -- Personal
  first_name      VARCHAR(100) NOT NULL,
  last_name       VARCHAR(100) NOT NULL,
  date_of_birth   DATE,
  gender          VARCHAR(10),
  nationality     VARCHAR(100),
  
  -- Enrollment
  admission_number VARCHAR(50),
  admission_date  DATE,
  current_class_id UUID REFERENCES classes(id),
  grade_level     VARCHAR(20),
  
  -- Contact
  address         TEXT,
  emergency_contact VARCHAR(20),
  
  -- Status
  status          VARCHAR(20) DEFAULT 'active',
  
  created_at      TIMESTAMP DEFAULT NOW(),
  updated_at      TIMESTAMP DEFAULT NOW(),
  
  UNIQUE(school_id, admission_number)
);

Teacher

Teacher profile with subjects and qualifications.

CREATE TABLE teachers (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id         UUID UNIQUE REFERENCES users(id),
  school_id       UUID NOT NULL REFERENCES schools(id),
  
  first_name      VARCHAR(100) NOT NULL,
  last_name       VARCHAR(100) NOT NULL,
  employee_number VARCHAR(50),
  
  -- Professional
  qualifications  TEXT[],
  specialization  VARCHAR(100),
  years_experience INTEGER,
  
  -- Employment
  hire_date       DATE,
  department      VARCHAR(100),
  
  status          VARCHAR(20) DEFAULT 'active',
  
  created_at      TIMESTAMP DEFAULT NOW(),
  updated_at      TIMESTAMP DEFAULT NOW()
);

-- Teacher-Subject junction
CREATE TABLE teacher_subjects (
  teacher_id      UUID REFERENCES teachers(id),
  subject_id      UUID REFERENCES subjects(id),
  PRIMARY KEY (teacher_id, subject_id)
);

Guardian

Parent/guardian linked to students.

CREATE TABLE guardians (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id         UUID UNIQUE REFERENCES users(id),
  school_id       UUID NOT NULL REFERENCES schools(id),
  
  first_name      VARCHAR(100) NOT NULL,
  last_name       VARCHAR(100) NOT NULL,
  phone           VARCHAR(20),
  email           VARCHAR(255),
  
  relationship    VARCHAR(50),  -- 'father', 'mother', 'guardian'
  occupation      VARCHAR(100),
  address         TEXT,
  
  created_at      TIMESTAMP DEFAULT NOW(),
  updated_at      TIMESTAMP DEFAULT NOW()
);

-- Guardian-Student junction
CREATE TABLE student_guardians (
  student_id      UUID REFERENCES students(id),
  guardian_id     UUID REFERENCES guardians(id),
  is_primary      BOOLEAN DEFAULT false,
  PRIMARY KEY (student_id, guardian_id)
);

---

Academic Models

Class

School class/section.

CREATE TABLE classes (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  school_id       UUID NOT NULL REFERENCES schools(id),
  
  name            VARCHAR(100) NOT NULL,
  grade_level     VARCHAR(20),
  section         VARCHAR(10),
  academic_year   VARCHAR(20),
  
  class_teacher_id UUID REFERENCES teachers(id),
  capacity        INTEGER,
  
  is_active       BOOLEAN DEFAULT true,
  
  created_at      TIMESTAMP DEFAULT NOW(),
  updated_at      TIMESTAMP DEFAULT NOW()
);

Subject

Curriculum subjects.

CREATE TABLE subjects (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  school_id       UUID NOT NULL REFERENCES schools(id),
  
  name            VARCHAR(100) NOT NULL,
  code            VARCHAR(20),
  description     TEXT,
  
  grade_levels    VARCHAR(20)[],  -- Which grades study this
  is_core         BOOLEAN DEFAULT true,
  credit_hours    INTEGER,
  
  is_active       BOOLEAN DEFAULT true,
  
  created_at      TIMESTAMP DEFAULT NOW(),
  updated_at      TIMESTAMP DEFAULT NOW()
);

Grade

Academic performance records.

CREATE TABLE grades (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  school_id       UUID NOT NULL REFERENCES schools(id),
  student_id      UUID NOT NULL REFERENCES students(id),
  subject_id      UUID NOT NULL REFERENCES subjects(id),
  class_id        UUID REFERENCES classes(id),
  teacher_id      UUID REFERENCES teachers(id),
  
  -- Assessment details
  assessment_type VARCHAR(50) NOT NULL,  -- 'quiz', 'test', 'exam', 'assignment'
  assessment_name VARCHAR(200),
  score           DECIMAL(5,2) NOT NULL,
  max_score       DECIMAL(5,2) NOT NULL,
  percentage      DECIMAL(5,2) GENERATED ALWAYS AS (score / max_score * 100) STORED,
  
  -- Term/period
  term            VARCHAR(20),
  academic_year   VARCHAR(20),
  date_recorded   DATE DEFAULT CURRENT_DATE,
  
  -- Optional
  remarks         TEXT,
  
  created_at      TIMESTAMP DEFAULT NOW(),
  updated_at      TIMESTAMP DEFAULT NOW()
);

Attendance

Daily attendance records.

CREATE TABLE attendance (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  school_id       UUID NOT NULL REFERENCES schools(id),
  student_id      UUID NOT NULL REFERENCES students(id),
  class_id        UUID REFERENCES classes(id),
  
  date            DATE NOT NULL,
  status          attendance_status NOT NULL,
  
  time_in         TIME,
  time_out        TIME,
  
  marked_by       UUID REFERENCES users(id),
  notes           TEXT,
  
  created_at      TIMESTAMP DEFAULT NOW(),
  
  UNIQUE(student_id, date)
);

CREATE TYPE attendance_status AS ENUM (
  'present',
  'absent',
  'late',
  'excused'
);

---

Financial Models

FeeStructure

Fee definitions per school/term.

CREATE TABLE fee_structures (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  school_id       UUID NOT NULL REFERENCES schools(id),
  
  name            VARCHAR(100) NOT NULL,
  amount          DECIMAL(12,2) NOT NULL,
  currency        VARCHAR(3) DEFAULT 'USD',
  
  fee_type        VARCHAR(50),  -- 'tuition', 'transport', 'meals'
  grade_level     VARCHAR(20),
  term            VARCHAR(20),
  academic_year   VARCHAR(20),
  
  is_mandatory    BOOLEAN DEFAULT true,
  due_date        DATE,
  
  created_at      TIMESTAMP DEFAULT NOW(),
  updated_at      TIMESTAMP DEFAULT NOW()
);

Invoice

Student fee invoices.

CREATE TABLE invoices (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  school_id       UUID NOT NULL REFERENCES schools(id),
  student_id      UUID NOT NULL REFERENCES students(id),
  
  invoice_number  VARCHAR(50) UNIQUE NOT NULL,
  total_amount    DECIMAL(12,2) NOT NULL,
  currency        VARCHAR(3) DEFAULT 'USD',
  
  status          VARCHAR(20) DEFAULT 'pending',  -- 'pending', 'partial', 'paid', 'overdue'
  due_date        DATE,
  
  term            VARCHAR(20),
  academic_year   VARCHAR(20),
  
  created_at      TIMESTAMP DEFAULT NOW(),
  updated_at      TIMESTAMP DEFAULT NOW()
);

CREATE TABLE invoice_items (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  invoice_id      UUID NOT NULL REFERENCES invoices(id),
  fee_structure_id UUID REFERENCES fee_structures(id),
  
  description     VARCHAR(200),
  amount          DECIMAL(12,2) NOT NULL
);

Payment

Payment records.

CREATE TABLE payments (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  school_id       UUID NOT NULL REFERENCES schools(id),
  invoice_id      UUID NOT NULL REFERENCES invoices(id),
  student_id      UUID NOT NULL REFERENCES students(id),
  
  amount          DECIMAL(12,2) NOT NULL,
  currency        VARCHAR(3) DEFAULT 'USD',
  
  payment_method  VARCHAR(50),  -- 'cash', 'bank_transfer', 'mobile_money'
  reference       VARCHAR(100),
  
  status          VARCHAR(20) DEFAULT 'completed',
  payment_date    TIMESTAMP DEFAULT NOW(),
  
  received_by     UUID REFERENCES users(id),
  notes           TEXT,
  
  created_at      TIMESTAMP DEFAULT NOW()
);

---

Communication Models

Message

In-app messaging.

CREATE TABLE messages (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  school_id       UUID NOT NULL REFERENCES schools(id),
  
  sender_id       UUID NOT NULL REFERENCES users(id),
  recipient_id    UUID NOT NULL REFERENCES users(id),
  
  subject         VARCHAR(255),
  body            TEXT NOT NULL,
  
  is_read         BOOLEAN DEFAULT false,
  read_at         TIMESTAMP,
  
  parent_id       UUID REFERENCES messages(id),  -- For threads
  
  created_at      TIMESTAMP DEFAULT NOW()
);

Notification

Push/in-app notifications.

CREATE TABLE notifications (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id         UUID NOT NULL REFERENCES users(id),
  
  type            VARCHAR(50) NOT NULL,
  title           VARCHAR(255) NOT NULL,
  body            TEXT NOT NULL,
  
  data            JSONB DEFAULT '{}',
  action_url      VARCHAR(500),
  
  is_read         BOOLEAN DEFAULT false,
  read_at         TIMESTAMP,
  
  created_at      TIMESTAMP DEFAULT NOW()
);

---

Security Models

RefreshToken

CREATE TABLE refresh_tokens (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id         UUID NOT NULL REFERENCES users(id),
  school_id       UUID REFERENCES schools(id),
  
  token           VARCHAR(500) UNIQUE NOT NULL,
  expires_at      TIMESTAMP NOT NULL,
  
  created_at      TIMESTAMP DEFAULT NOW()
);

AuditLog

CREATE TABLE audit_logs (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  school_id       UUID REFERENCES schools(id),
  user_id         UUID REFERENCES users(id),
  
  action          VARCHAR(100) NOT NULL,
  resource_type   VARCHAR(100),
  resource_id     VARCHAR(100),
  
  old_values      JSONB,
  new_values      JSONB,
  
  ip_address      VARCHAR(45),
  user_agent      TEXT,
  
  created_at      TIMESTAMP DEFAULT NOW()
);

---

Relationships Diagram

School 1──────* User
  │             │
  │             ├── Student 1──* Grade
  │             │     │
  │             │     └──* StudentGuardian *──1 Guardian
  │             │
  │             ├── Teacher *──* Subject
  │             │
  │             └── Parent/Guardian
  │
  ├──────* Class *──1 Teacher (class_teacher)
  │         │
  │         └──* Student (current_class)
  │
  ├──────* Subject
  │
  ├──────* FeeStructure
  │         │
  │         └──* InvoiceItem *──1 Invoice *──1 Student
  │                                   │
  │                                   └──* Payment
  │
  └──────* Attendance *──1 Student