Skip to main content

​
Overview

EduMate uses PostgreSQL with SQLAlchemy ORM for data persistence. The application has two primary models: User and Assessment.
All models are defined in backend/models.py and schemas in backend/schemas.py

​
Database Configuration

​
Connection Setup

Location: backend/database.py:1-24 
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base

SQLALCHEMY_DATABASE_URL = "postgresql://edumate_user:edumate_pass@localhost:5432/edumate"

# Create the engine that talks to Postgres
engine = create_engine(SQLALCHEMY_DATABASE_URL)

# Create a session factory
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# Base class for our database models
Base = declarative_base()
Update the database credentials before deploying to production!

​
Models

​
User Model

Location: backend/models.py:5-11 Stores user authentication and profile information. 
class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String)
    email = Column(String, unique=True, index=True)
    password_hash = Column(String)

​
Table Structure

​
id
Integer
required
Primary key with auto-increment.Constraints:
  • Primary key
  • Indexed
​
name
String
required
User’s full name.
​
email
String
required
User’s email address.Constraints:
  • Unique
  • Indexed
Used for authentication and password reset
​
password_hash
String
required
Bcrypt hashed password.
Never store plain text passwords! Uses bcrypt with SHA256 pre-hashing for passwords > 72 bytes.

​
Assessment Model

Location: backend/models.py:13-21 Stores generated MCQ assessments and their metadata. 
class Assessment(Base):
    __tablename__ = "assessments"
    
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"))
    chapter_name = Column(String)
    bloom_factors = Column(JSONB)  # Stores {remember: 5, apply: 2, etc.}
    content_json = Column(JSONB)   # Stores the massive output from Gemini
    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)

​
Table Structure

​
id
Integer
required
Primary key with auto-increment.Constraints:
  • Primary key
  • Indexed
​
user_id
Integer
required
Foreign key reference to the User who created this assessment.Constraints:
  • Foreign key → users.id
Relationship:
  • Many-to-one with User model
​
chapter_name
String
required
Name/topic of the assessment chapter.Example:
"Introduction to Data Structures"
​
bloom_factors
JSONB
required
JSON object storing Bloom’s Taxonomy distribution.Structure:
{
  "remember": 5,
  "understand": 3,
  "apply": 4,
  "analyze": 3,
  "evaluate": 2,
  "create": 3
}
Uses PostgreSQL’s JSONB type for efficient querying and indexing
​
content_json
JSONB
required
Complete MCQ assessment generated by Gemini AI.Structure:
{
  "mcqs": [
    {
      "question_no": "1",
      "bloom_level": "remember",
      "question": "What is...",
      "answer_options": ["A", "B", "C", "D"],
      "correct_answer": "A",
      "explaination": "..."
    }
  ]
}
​
created_at
DateTime
required
Timestamp of assessment creation.Constraints:
  • Timezone-aware
  • Auto-generated (server default)
  • Not nullable

​
Schemas (Pydantic Models)

Location: backend/schemas.py

​
User Schemas

class UserCreate(BaseModel):
    name: str
    email: str
    password: str

class UserResponse(BaseModel):
    id: int
    name: str
    email: str
    
    class Config:
        from_attributes = True

​
Authentication Schemas

class Token(BaseModel):
    access_token: str
    token_type: str

class ForgotPasswordRequest(BaseModel):
    email: str

class ResetPasswordRequest(BaseModel):
    token: str
    new_password: str

​
Assessment Schemas

class AssessmentHistoryItem(BaseModel):
    id: int
    chapter_name: str
    questions: int
    created_at: datetime
    status: str = "completed"
    
    class Config:
        from_attributes = True

class AssessmentDetail(BaseModel):
    id: int
    chapter_name: str
    content: Any
    
    class Config:
        from_attributes = True

​
Database Initialization

Location: backend/server.py:26 Tables are automatically created on application startup: 
from .database import engine
from . import models

# Create Postgres tables if they don't exist
models.Base.metadata.create_all(bind=engine)
Run this once to create all tables. SQLAlchemy will not modify existing tables.

​
Relationships

​
Database Session Management

Location: backend/database.py:19-24 
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()
Used as a FastAPI dependency: 
@app.post("/api/signup")
def create_user(user: schemas.UserCreate, db: Session = Depends(get_db)):
    # Database operations
    db.add(new_user)
    db.commit()
    db.refresh(new_user)

​
Manual Database Setup

If you need to create the database manually: 
# Connect to PostgreSQL
psql -U postgres

# Create database and user
CREATE DATABASE edumate;
CREATE USER edumate_user WITH PASSWORD 'edumate_pass';
GRANT ALL PRIVILEGES ON DATABASE edumate TO edumate_user;
Change the default credentials in production!

Build docs developers (and LLMs) love

Get started for freeTalk to us