Proje: Okul Platform · Hub: Okul Platform — Architecture

TÜBİTAK AR-GE #7260485 — Database Schema Reference for Data Science

Extracted from the live Okul Platform codebase. All table names, column names, and types are sourced from Laravel migrations and Eloquent models. This document is the ground truth for the data scientist working on the AR-GE project.

---

Domain 1 — School Profiles

schools

Column	Type	Notes
id	PK
name	string	School name
description	text	Rich description
mission	text
vision	text
history	text
psychological_advice	enum
total_students	int	Capacity
class_students	int	Students per class
school_type_id	FK → schools_types
sector_id	FK → sectors
franchise_id	FK → schools_franchises
customer_id	FK → customers	B2B owner
campus_id	FK
college_id	FK
status	enum	active/passive/etc.
school_closed	bool
redirect_id	FK → schools	For merged schools
deleted_at	timestamp	Soft delete

Key relations: hasMany → school_locations, school_contacts, school_comments, school_fees, school_facilities, school_activities, school_learning_languages, school_shifts, school_interactions, school_daily_logs

school_details

Flexible key-value store for school attributes beyond core columns.

Column	Type	Notes
id	PK
type_id	int	Config-based key
school_id	FK → schools
value	string	Attribute value
deleted_at	timestamp	Soft delete

school_locations

Column	Type	Notes
school_id	FK → schools
location_level_1	FK → locations	Province
location_level_2	FK → locations	District
location_level_3	FK → locations	Neighbourhood
address	string	Full address
map_latitude	decimal	GPS lat
map_longitude	decimal	GPS lng
mernis_address_code	string	Official address code

school_contacts

Column	Type	Notes
school_id	FK → schools
value	string	Phone / email / URL
type_id	int	Config: phone, email, fax, web, inbound_number, wp_phone, authorized_name, authorized_email

school_fees

Column	Type	Notes
school_id	FK → schools
grade	string	Grade level
fee_type_id	FK → configs	Annual/registration/etc.
amount	decimal	Tuition amount

school_facilities / school_activities / school_learning_languages / school_shifts

Pivot tables linking schools to master data: facilities, activities, languages, shifts.

Reference tables

• schools_types — id, name, slug (Private, Public, International, etc.)
• sectors — id, name (K-12, Higher Education, etc.)
• schools_franchises — id, name
• locations — id, name, level, parent_id, plate, mernis_code (geographic hierarchy)

---

Domain 2 — Users & Family Profiles

users

Column	Type	Notes
id	PK
name	string
email	string unique
password	string(60)	Hashed
remember_token	string

user_details

Column	Type	Notes
user_id	FK → users
phone	string
city	FK → locations
avatar	string
birth_date	date
deleted_at	timestamp	Soft delete

parents

Introduced 2024. Can be linked to a users account or standalone (no login required).

Column	Type	Notes
id	PK
first_name	string
last_name	string
email	string
parent_type	string
phone	string
note	text
user_id	FK → users nullable
kvkk	enum	Consent status
status	enum	active/inactive
unsubscribe	bool
email_verified_at	timestamp
phone_verified_at	timestamp
deleted_at	timestamp	Soft delete

Relations: belongsToMany → families (via family_parent)

students

Introduced 2024.

Column	Type	Notes
id	PK
first_name	string
last_name	string
date_of_birth	date	Age derived via accessor
gender	char
class_level	tinyInt	Grade
special_condition	string	Special needs flag
current_school_id	FK → schools nullable
note	text
deleted_at	timestamp	Soft delete

Relations: belongsToMany → families (via family_student)

families

Column	Type	Notes
id	PK
name	string	Family display name
deleted_at	timestamp	Soft delete

Relations: hasMany → parents (via family_parent), students (via family_student), family_notes, family_appointments

Pivot tables

• family_parent — (family_id, parent_id) composite PK
• family_student — (family_id, student_id) composite PK

family_notes

Column	Type	Notes
family_id	FK → families
note	text	Internal CRM note
deleted_at	timestamp	Soft delete

---

Domain 3 — Reviews & Ratings

school_comments

900,000+ rows. Core content for sentiment analysis and RAG knowledge base.

Column	Type	Notes
id	PK
school_id	FK → schools
user_id	FK → users nullable	Logged-in commenter
user_type_id	FK → users_types
commenter_name	string	Display name (may be hidden)
commenter_email	string
comment	text (utf8mb4)	Positive review body
comment_negative	text	Negative review body
student_name	string
student_date_of_birth	date
comment_rate	enum(1-5)	Star rating
do_recommend	enum	Recommend flag
rated_up	int	Upvotes
rated_down	int	Downvotes
show_home_page	bool	Featured flag
status	enum	pending/approved/rejected
approved_by	FK → users	Moderator
approved_at	timestamp
approved_type	enum	positive/negative/critical
rejected_at	timestamp
reject_stage	string
reject_note	text	Rejection reason
lead_status	string
registration_status	string
hidden_commenter_name	string	Anonymized name
replied_id	FK → school_comments self	Parent comment (for replies)
deleted_at	timestamp	Soft delete

Relations: belongsTo School, User (commenter), User (approver); self-join for threaded replies

Data science notes:

• Use status = 'approved' for training data
• comment + comment_negative = dual-field review (positive + negative aspects separately)
• comment_rate (1-5) + do_recommend usable as label signals
• replied_id enables reply threading — filter nulls for root reviews

---

Domain 4 — Leads & Inquiries

leads

145,000+ per year. Primary conversion signal for both B2C and B2B agents.

Column	Type	Notes
id	PK
school_id	FK → schools	Target school
user_id	FK → users nullable	Logged-in user
name	string	Lead contact name
email	string
phone	string
student_class	string	Grade level of interest
date_of_birth	date	Student DOB
student_name	string
ref	string	Source: lead-pool / college-page / school-profile / article-content / school-listing
referer	string	HTTP referer URL
type	enum	offer / early-registration / scholarship
message	text	Free-text inquiry
agent	FK → users	Assigned sales agent
approved_by	FK → users
approved_type	enum	offer/early-registration/scholarship
approved_status_id	FK → configs
contact_reference	enum	call / form
status	string	Pipeline status
lead_pool_id	FK → lead_pool_forms	If originated from pool
pipeline_id	string	Pipedrive pipeline
stage_id	string	Pipedrive stage
reason_of_reject	text
transmitted_authorized	enum	iletilen/yetkili
customer_viewed_at	timestamp	When school viewed this lead
entry_method	string
is_lead_plus	bool	Premium lead flag
deleted_at	timestamp	Soft delete

Data science notes:

• ref is the acquisition channel — critical for funnel analysis
• type + approved_type = intent classification ground truth
• customer_viewed_at usable as engagement signal for B2B agent
• is_lead_plus marks premium leads — separate model needed

lead_pool_forms

Parent requests not yet matched to a specific school.

Column	Type	Notes
user_id	FK → users nullable
name, email, phone	string	Contact
school_type_id	FK → schools_types	Preference
message	text	Free-text need
student_class	string
date_of_birth	date
location_level_1/2/3	FK → locations	Geographic preference
amount_range_min/max	decimal	Budget range
status_id	FK → configs
type	string
deleted_at	timestamp	Soft delete

---

Domain 5 — Behavioral & Interaction Data

school_daily_logs

Aggregated daily metrics per school. Primary source for engagement trend analysis.

Column	Type	Notes
school_id	FK → schools
page_view	int	Profile page views
phone_view	int	Phone number reveal clicks
call_phone	int	Click-to-call events
fee_view	int	Fee/tuition section opens
lead_count	int	Leads generated that day
is_customer	bool	Is the school a paying customer
date	date	Aggregation date
wp_click	int	WhatsApp button clicks
site_click	int	Website link clicks
google_click	int	Google Maps / directions clicks

Data science notes:

• No timestamps — batch-inserted nightly
• Key engagement funnel: page_view → phone_view / fee_view → wp_click / call_phone → lead_count
• is_customer allows A/B comparison between paying vs. non-paying schools
• Join with schools on school_id to enrich with type, location, sector

school_interactions

CRM interaction log between sales team and schools.

Column	Type	Notes
school_id	FK → schools
user_id	FK → users	Sales agent
connection_type_id	FK → configs	Call/email/visit/etc.
reason_id	FK → configs	Why contacted
result_id	FK → configs	Outcome
note	text	Interaction notes
interaction_type_id	int
reminder_time	timestamp	Follow-up reminder
note_visible_for_customer	bool	Customer-visible flag
pinned_at	timestamp	Pinned interactions
deleted_at	timestamp	Soft delete

Relations: hasMany → SchoolInteractionTopic (discussion topics per interaction)

user_favorites

Column	Type	Notes
user_id	FK → users
school_id	FK → schools
deleted_at	timestamp	Soft delete

Data science note: Explicit positive signal. Rare but high-confidence preference indicator.

user_logs

Column	Type	Notes
ip_address	string
user_agent	string	Browser/device
url	string	Page visited
created_at	timestamp

activity_log

Column	Type	Notes
user_id	FK → users
content_id	int	Entity ID
content_type	string	Entity type (polymorphic)
action	string	create/update/delete/etc.
description	string
details	text	Full payload
developer	bool	Internal flag
ip_address	string
user_agent	string

---

Domain 6 — B2B Customers (School Organizations)

customers

Institutional customers — school networks and individual schools with subscriptions.

Column	Type	Notes
id	PK
name	string	Organization name
is_individual	enum	Individual vs. corporate
email, phone, gsm, web	string	Contacts
billing_address, billing_office, billing_no	string	Billing
tc_no	string	Tax ID
location_level_1/2	FK → locations
status	string
auth_person1_name/phone/email	string	Primary contact
auth_person2_name/phone/email	string	Secondary contact
erp_code	string	ERP integration key
segments	JSON	Segmentation labels
is_key_account	bool	VIP flag
association_id	FK	School association membership
deleted_at	timestamp	Soft delete

Relations: hasMany → schools, customer_users, customer_agreements

customer_agreements

Column	Type	Notes
customer_id	FK → customers
agreement_type	enum	fixed / regular / zero_tax_arge / subscription
price	decimal
is_recurring	bool
tax_amount	decimal
products	JSON	Contracted services list
product_arge	JSON	AR-GE specific products
financial_status	string
status	string	active/rejected/etc.
agreement_date	date
deleted_at	timestamp	Soft delete

customer_users

Column	Type	Notes
customer_id	FK → customers
user_id	FK → users
role	string	Admin role within customer
is_active	bool

---

Domain 7 — Appointments

family_appointments

Parent-school meeting scheduling. Introduced 2024.

Column	Type	Notes
customer_user_id	FK → users	School-side user
school_id	FK → schools
family_id	FK → families
parent_id	FK → parents
student_id	FK → students
result_id	FK → configs	Outcome
note	text
starts_on	timestamp
ends_on	timestamp
approved_at	timestamp
rejected_at	timestamp
reject_note	text
sent_at	timestamp	Notification sent
seen_at	timestamp	Notification seen
deleted_at	timestamp	Soft delete

sales_school_appointments

Internal sales team → school visit scheduling.

Column	Type	Notes
user_id	FK → users	Sales agent
school_id	FK → schools
appointment_date	date
result_id	FK → configs	Visit outcome
is_renew	bool	Renewal visit flag
reminder_at	timestamp

---

Key Relationships for ML Modeling

users ─────────────── user_details
  │                   user_favorites → schools
  │                   user_logs
  │
  ├── parents ──────── family_parent ─── families ── family_student ─── students
  │
  ├── leads ─────────────────────────────────────────── schools
  │     └── lead_pool_forms
  │
  └── school_comments ──────────────────────────────── schools

schools ─────────────── school_locations → locations
  │                     school_contacts
  │                     school_fees
  │                     school_details
  │                     school_facilities/activities/shifts/languages
  │                     school_daily_logs   (time-series)
  │                     school_interactions (CRM log)
  │
  └── customers ──────── customer_agreements
                         customer_users → users

---

Recommended Datasets per AR-GE Component

AR-GE Component	Primary Tables	Labels / Signals
Embedding / Vector DB	schools + school_details + school_comments (approved)	— (unsupervised)
Knowledge Gap Detection	leads.message, lead_pool_forms.message, school_comments.comment	leads.status (converted vs. dropped)
B2C Counseling Agent	lead_pool_forms, user_favorites, school_comments	leads.school_id (chosen school)
B2B Coaching Agent	school_daily_logs, school_interactions, customer_agreements	leads.customer_viewed_at, lead_count
Feature Store (behavioral)	school_daily_logs, user_logs, user_favorites, leads.ref	Conversion events
HITL / Expert Knowledge	school_interactions.note, family_notes, school_comments.reject_note	Human-validated labels
Predictive Analytics	school_daily_logs (time-series), leads (monthly), customer_agreements	Revenue / churn signals

---

Data Quality Notes

• All main entities use soft deletes (deleted_at) — always filter WHERE deleted_at IS NULL unless doing retention analysis
• school_comments: only use status = 'approved' for training to avoid noise from rejected/pending reviews
• leads: ref column is nullable in older records — gap in pre-2022 data
• parents / students / families: tables introduced ~2024, limited historical depth
• school_daily_logs: no id column — composite key on (school_id, date). No soft delete.
• Revisionable pattern: school_interactions, leads, customers, customer_agreements all track change history in a revisions table — usable for temporal modeling

Related

• tubitak-arge-veri-altyapisi — Planned AI/data stack: vector DB, RAG, dual-agent architecture
• 2026-04-21-tubitak-arge-7260485-ai-ekosistemi — Project decision, work packages, success criteria