School AI System with Role-Based Access Control

A comprehensive educational database system with AI-powered query capabilities, built on EdgeDB with a Flask web interface and role-based access control.

Overview

This school AI system combines:

• EdgeDB Database: Stores student records, grades, teacher notes, and educational documents
• Flask Web Interface: Modern web application with role-based dashboards
• AI Query System: Intelligent analysis and educational insights using OpenAI integration
• Role-Based Access Control: Secure data access based on user roles (superintendent, principal, teacher, student, parent)
• Retrieval-Augmented Generation: Real-time database queries combined with AI responses

Features

Core Educational Features

• Student Management: Complete student profiles with academic records
• Class Management: Course enrollment and scheduling
• Grade Tracking: Comprehensive grade recording and analysis
• Teacher Notes: Progress tracking and behavioral observations
• Document Management: Educational content storage with metadata

AI-Powered Features

• Student Performance Analysis: AI-generated insights on academic performance
• Personalized Study Recommendations: Tailored learning strategies
• Educational Query Answering: Natural language questions about students and classes
• Real-Time Data Integration: AI responses based on actual database content
• Role-Based Data Filtering: AI respects user permissions and access levels

Web Interface Features

• Modern UI: Bootstrap-based responsive design
• Role-Based Dashboards: Customized views for different user types
• AI Assistant: Integrated chat interface for educational queries
• Session Management: Secure user authentication and authorization
• Real-Time Updates: Live data from EdgeDB database

Project Structure

school_ai_new/
├── dbschema/                 # EdgeDB schema and migrations
│   └── migrations/          # Database migrations
├── static/                  # Web assets
│   ├── css/style.css       # Custom styling
│   └── js/app.js           # Client-side JavaScript
├── templates/               # HTML templates
│   ├── base.html           # Base template
│   ├── login.html          # Login page
│   ├── dashboard.html      # Role-based dashboard
│   ├── ai_query.html       # AI assistant interface
│   ├── students.html       # Student listing
│   ├── student_detail.html # Individual student view
│   ├── notes.html          # Teacher notes
│   ├── 403.html            # Forbidden error page
│   └── 404.html            # Not found error page
├── ai_data_client.py       # EdgeDB data client for AI queries
├── web_app.py              # Main Flask application
├── complete_seed.edgeql    # Database seed data
├── requirements.txt        # Python dependencies
├── web_requirements.txt    # Web app dependencies
└── README.md               # This file

Setup Instructions

1. Prerequisites

• EdgeDB installed and running
• Python 3.8+ with pip
• OpenAI API key for AI functionality

2. Database Setup

# Verify your database is running
edgedb query -I school_ai_new "select count(Student);"

3. Python Environment Setup

# Install web application dependencies
pip install -r web_requirements.txt

# Install general dependencies
pip install -r requirements.txt

4. AI Integration Setup

To enable AI functionality:

# Set your OpenAI API key
export OPENAI_API_KEY="your_openai_api_key_here"

The API key can also be configured directly in web_app.py for development purposes.

5. Start the Application

# Run the Flask web application
python web_app.py

The application will be available at http://localhost:5002

Usage

Web Interface

1. Login: Use one of the demo accounts:

   • Superintendent: superintendent@district.edu / admin123
   • Principal: principal@school.edu / principal123
   • Teacher: teacher@school.edu / teacher123
   • Student: emily@student.edu / student123

2. Dashboard: Each role sees a customized dashboard with relevant information

3. AI Assistant: Click "AI Assistant" to ask questions about school data

AI Query Examples

For Students:

• "What are my current grades?"
• "What classes am I enrolled in?"
• "How am I doing in math?"

For Teachers:

• "What students are in my classes?"
• "What are the recent grades for my students?"
• "Which students need extra help?"

For Administrators:

• "What are the school statistics?"
• "How are students performing across all classes?"
• "What is the grade distribution?"

Role-Based Access Control

The system enforces strict access controls:

• Students: Can only access their own academic data
• Teachers: Can access their students' data and class information
• Principals: Can access school-wide data and teacher performance
• Superintendents: Can access all data across the district

Technical Architecture

Database Layer

• EdgeDB: Graph-relational database with advanced querying capabilities
• Schema: Comprehensive educational data model with relationships
• Migrations: Version-controlled database schema evolution

Application Layer

• Flask: Web framework for handling HTTP requests and sessions
• Werkzeug: Password hashing and security utilities
• Jinja2: Template engine for dynamic HTML generation

AI Integration Layer

• OpenAI API: GPT-4o-mini for intelligent query responses
• Retrieval-Augmented Generation: Combines real database data with AI analysis
• Context-Aware Responses: AI understands user roles and data permissions

Data Flow

1. User submits query through web interface
2. System authenticates user and determines role
3. AI client queries EdgeDB for relevant data based on user permissions
4. Real database data is sent to OpenAI as context
5. AI generates response using actual school data
6. Response is returned to user through web interface

Configuration

The system is configured through environment variables and direct configuration in web_app.py:

# OpenAI Configuration
OPENAI_API_KEY = "your_api_key_here"

# EdgeDB Configuration
EDGEDB_INSTANCE = 'school_ai_new'
EDGEDB_HOST = 'localhost'
EDGEDB_PORT = '10703'

Current Database Contents

Your database includes:

• 1 School: Lincoln High School
• 7 Users: Superintendent, Principal, Teachers, Parents
• 6 Students: Complete academic profiles
• 4 Classes: Algebra, English, Biology, Chemistry
• 8 Grades: Fall 2024 academic records
• 8 Schedules: Class timetables
• 8 Documents: Teacher notes and progress reports

Security Features

• Password Hashing: Secure password storage using Werkzeug
• Session Management: Secure user sessions with proper timeout
• Role-Based Access: Data access strictly controlled by user roles
• SQL Injection Prevention: Parameterized queries through EdgeDB
• Input Validation: All user inputs are validated and sanitized

Development

Adding New Features

1. Update the database schema in dbschema/
2. Add new routes in web_app.py
3. Create corresponding templates in templates/
4. Update the AI client in ai_data_client.py for new data types

Testing

• Test different user roles and permissions
• Verify AI responses use real database data
• Ensure proper error handling and validation

Contributing

This is a demonstration system. To contribute:

1. Fork the repository
2. Add new AI features or improve existing ones
3. Enhance the database schema
4. Improve the web interface
5. Add comprehensive tests
6. Submit pull requests

License

This project is for educational and demonstration purposes.

---

Ready to revolutionize education with AI-powered insights and secure, role-based data access.