GradeSync

A comprehensive grade synchronization and management system that integrates with assessment platforms (Gradescope, PrairieLearn, iClicker) to automate gradebook updates and facilitate post-semester submissions.

---

📋 Table of Contents

• Overview
• Architecture
• Features
• Quick Start
• Installation
• Configuration
• Usage
• API Documentation
• Deployment
• Development
• Troubleshooting
• Contributing
• License

---

🎯 Overview

GradeSync is a backend microservice designed for educational institutions to:

• Automate grade synchronization from multiple assessment platforms
• Store grades in PostgreSQL for reliable persistence and querying
• Provide REST API endpoints for frontend applications and third-party integrations
• Generate Google Sheets summaries for instructor convenience
• Support post-semester workflows allowing students to submit after term ends

Key Benefits

✅ Automated Updates - No manual CSV downloads or spreadsheet updates
✅ Multi-Platform Support - Gradescope, PrairieLearn, iClicker integration
✅ Database-Backed - Reliable PostgreSQL storage with full audit trails
✅ RESTful API - Easy integration with dashboards, mobile apps, and LMS
✅ Flexible Deployment - Docker containers for easy scaling

---

🏗️ Architecture

System Architecture

┌─────────────┐
│ Gradescope  │──┐
└─────────────┘  │
┌─────────────┐  │
│PrairieLearn │──┤ ① Download CSVs
└─────────────┘  │
┌─────────────┐  │
│  iClicker   │──┘
└─────────────┘
       │
       ↓
┌──────────────────────────────┐
│   Sync Scripts (Cron Jobs)   │
│  - gradescope/gradescope_to  │
│    _spreadsheet.py           │
│  - prairieLearn/pl_to_       │
│    spreadsheet.py            │
└───────────┬──────────────────┘
            │
            │ ② Ingest via Core Module
            ↓
┌─────────────────────────────┐
│    Core Database Module     │
│  - ingest.py (CSV→DB)       │
│  - summary_from_db.py       │
│  - models.py (ORM)          │
└───────────┬─────────────────┘
            │
            │ ③ Store
            ↓
┌─────────────────────────────┐
│   PostgreSQL Database       │
│  - courses                  │
│  - assignments              │
│  - students                 │
│  - submissions              │
└───────────┬─────────────────┘
            │
            ├─────────────┐
            │ ④ Query     │ ⑤ Generate
            ↓             ↓
    ┌──────────┐   ┌────────────┐
    │ REST API │   │   Google   │
    │(FastAPI) │   │   Sheets   │
    └─────┬────┘   │  Summary   │
          │        └────────────┘
          │ ⑥ Serve
          ↓
┌─────────────────────────┐
│  Client Applications    │
│  - Web Dashboard        │
│  - Mobile Apps          │
│  - Canvas/LMS          │
│  - Slack Bots          │
└─────────────────────────┘

Module Structure

GradeSync/
├── 📂 core/                 # Database operations (shared)
│   ├── db.py                # Database connection
│   ├── models.py            # SQLAlchemy ORM models
│   ├── ingest.py            # CSV → Database ingestion
│   └── summary_from_db.py   # Database → Summary generation
│
├── 📂 api/                  # REST API service (FastAPI)
│   ├── app.py               # API endpoints
│   ├── gradescopeClient.py  # Gradescope API client
│   ├── utils.py             # Utility functions
│   └── config_loader.py     # Configuration loader
│
├── 📂 gradescope/           # Gradescope sync scripts
│   ├── gradescope_to_spreadsheet.py  # Main sync script
│   ├── dueDate.py           # Due date extraction
│   └── fill_summary_emails.py        # Email population utility
│
├── 📂 prairieLearn/         # PrairieLearn sync scripts
│   └── pl_to_spreadsheet.py # PL sync script
│
├── 📂 iclicker/             # iClicker sync scripts
│   └── iclicker_to_spreadsheet.py   # iClicker sync script
│
├── 📂 config/               # Centralized configuration
│   └── courses.json         # Course settings
│
└── 📂 scripts/              # Utility scripts
    ├── backfill_grades.py   # Historical data import
    ├── generate_summary_sheets.py
    └── update_categories.py

---

✨ Features

Core Features

• Multi-Platform Integration

  • Gradescope (primary)
  • PrairieLearn
  • iClicker

• Database-First Architecture

  • PostgreSQL for reliable storage
  • Full audit trail with CSV backups
  • Automatic assignment categorization

• RESTful API

  • Get grades for individual assignments
  • Fetch all assignments for a course
  • Query student performance
  • PrairieLearn integration endpoints

• Google Sheets Integration

  • Auto-generate summary sheets
  • Formula-free (DB-backed) for reliability
  • Clickable assignment index

• Automation Ready

  • Docker containerization
  • Cron job compatible
  • Rate limiting and retry logic

---

🚀 Quick Start

Prerequisites

• Python 3.8+
• PostgreSQL 13+
• Docker (optional, for containerized deployment)
• Google Cloud Service Account (for Sheets access)
• Gradescope account with TA/Instructor access

5-Minute Setup

1. Clone the repository

   git clone https://github.com/yourusername/GradeSync.git
   cd GradeSync

2. Install dependencies

   pip install -r requirements_db.txt

3. Set up PostgreSQL

   # macOS (using Homebrew)
   brew install postgresql@15
   brew services start postgresql@15
   createdb gradesync

4. Configure environment

   cp .env.example .env
   # Edit .env with your credentials

5. Initialize database

   python -c "from core.db import init_db; init_db(); print('Database initialized!')"

6. Run your first sync

   cd gradescope
   python gradescope_to_spreadsheet.py

---

📦 Installation

Standard Installation

# 1. Create virtual environment
python3 -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate

# 2. Install dependencies
pip install -r requirements_db.txt

# 3. Install PostgreSQL
# macOS:
brew install postgresql@15

# Ubuntu/Debian:
sudo apt-get install postgresql-13

# 4. Create database
createdb gradesync

# 5. Initialize schema
python -c "from core.db import init_db; init_db()"

Docker Installation

# 1. Build API service
cd api
docker-compose build

# 2. Start services
docker-compose up -d

# 3. Access API
curl http://localhost:8000/

---

⚙️ Configuration

Environment Variables

Create a .env file in the project root:

# Database
DATABASE_URL=postgresql://postgres:password@localhost:5432/gradesync
USE_DB_AS_PRIMARY=true
KEEP_CSV_FILES=false  # Set to 'true' to keep CSV files after importing to DB

# Gradescope credentials
GRADESCOPE_EMAIL=your-email@example.com
GRADESCOPE_PASSWORD=your-password

# Google Sheets (Service Account JSON)
SERVICE_ACCOUNT_CREDENTIALS={"type":"service_account","project_id":"your-project",...}

# PrairieLearn (optional)
PL_API_TOKEN=your-prairielearn-token

Course Configuration

Edit config/courses.json:

{
  "gradescope_course_id": "1098053",
  "spreadsheet_id": "YOUR_GOOGLE_SHEETS_ID",
  "scopes": ["https://www.googleapis.com/auth/spreadsheets"],
  
  "course_name": "The Beauty and Joy of Computing",
  "department": "COMPSCI",
  "course_number": "10",
  "semester": "Fall",
  "year": "2025",
  "instructor": "",
  
  "categories": [
    {
      "name": "Labs",
      "patterns": ["lab", "laboratory"]
    },
    {
      "name": "Projects",
      "patterns": ["project", "proj"]
    },
    {
      "name": "Discussions",
      "patterns": ["discussion", "disc"]
    },
    {
      "name": "Quizzes",
      "patterns": ["quiz", "lecture"]
    }
  ]
}

Google Cloud Setup

1. Create a project in Google Cloud Console
2. Enable Google Sheets API
3. Create a Service Account
4. Download JSON key
5. Share your Google Sheet with the service account email
6. Add JSON contents to SERVICE_ACCOUNT_CREDENTIALS in .env

---

📖 Usage

Running Gradescope Sync

# One-time sync
cd gradescope
python gradescope_to_spreadsheet.py

# Expected output:
# - Downloads CSVs from Gradescope
# - Saves to gradescope/data/gradescope_csvs/<course_id>/
# - Ingests into PostgreSQL
# - Updates Google Sheets Summary

Running API Server

# Development mode
cd api
uvicorn app:app --reload --port 8000

# Production mode with Docker
docker-compose up -d api

# Test API
curl http://localhost:8000/
curl http://localhost:8000/getAssignmentJSON?class_id=YOUR_COURSE_ID

Backfilling Historical Data

# From existing CSVs
python scripts/backfill_grades.py --config config/courses.json

# Re-download from Gradescope
python scripts/backfill_grades.py --config config/courses.json --re-download

Scheduled Automation

Add to crontab for automated syncing:

# Sync every hour
0 * * * * cd /path/to/GradeSync/gradescope && /usr/bin/python3 gradescope_to_spreadsheet.py

# Sync daily at 2 AM
0 2 * * * cd /path/to/GradeSync/gradescope && /usr/bin/python3 gradescope_to_spreadsheet.py

---

🔌 API Documentation

Base URL

http://localhost:8000

Endpoints

Health Check

GET /

Returns API status.

Get Assignment Grades

GET /getGrades?class_id={course_id}&assignment_id={assignment_id}

Returns grades for a specific assignment in JSON format.

Parameters:

• class_id: Gradescope course ID
• assignment_id: Gradescope assignment ID

Response:

[
  {
    "Name": "Student Name",
    "Email": "student@example.com",
    "Total Score": "95.0",
    "Max Points": "100.0",
    "Status": "Graded"
  }
]

Get All Assignments

GET /getAssignmentJSON?class_id={course_id}

Returns structured list of all assignments.

Response:

{
  "labs": {
    "1": {
      "title": "Lab 1: Introduction",
      "assignment_id": "123456"
    }
  },
  "projects": { ... },
  "discussions": { ... }
}

Get All Grades

GET /fetchAllGrades?class_id={course_id}

Returns grades for all assignments and all students.

Get Assignment ID

GET /getGradeScopeAssignmentID/{category}/{number}?lab_type={type}

Returns assignment ID for a specific category and number.

Parameters:

• category: labs, projects, discussions, lecture_quizzes
• number: Assignment number
• lab_type (for labs only): 1 for conceptual, 0 for code

Example Usage

Python:

import requests

response = requests.get(
    "http://localhost:8000/getGrades",
    params={"class_id": "123456", "assignment_id": "789"}
)
grades = response.json()

JavaScript:

fetch('http://localhost:8000/getAssignmentJSON?class_id=123456')
  .then(response => response.json())
  .then(data => console.log(data));

cURL:

curl "http://localhost:8000/getGrades?class_id=123456&assignment_id=789"

---

🚢 Deployment

Docker Deployment

# 1. Build images
docker-compose build

# 2. Start all services
docker-compose up -d

# 3. Check logs
docker-compose logs -f

# 4. Stop services
docker-compose down

Production Considerations

• Use environment variables for secrets (never commit .env)
• Set up PostgreSQL backups (pg_dump)
• Configure SSL/TLS for API endpoints
• Implement rate limiting and authentication
• Use monitoring (Prometheus, Grafana)
• Set up log aggregation (ELK stack)

---

🛠️ Development

Project Structure

• core/ - Database operations (shared by API and sync scripts)
• api/ - FastAPI REST service
• gradescope/ - Gradescope synchronization scripts
• config/ - Centralized configuration
• scripts/ - Utility and maintenance scripts

Running Tests

# Database ingestion tests
pytest core/test_ingest.py -v

# API tests
pytest api/test_app.py -v

# All tests
pytest -v

Code Quality

# Linting
flake8 core/ api/ gradescope/

# Type checking
mypy core/ api/

# Formatting
black core/ api/ gradescope/

Database Migrations

# Create migration
alembic revision --autogenerate -m "Add new column"

# Apply migration
alembic upgrade head

# Rollback
alembic downgrade -1

---

🐛 Troubleshooting

Common Issues

"relation does not exist"

Solution: Initialize the database schema

python -c "from core.db import init_db; init_db()"

"could not connect to server"

Solution: Start PostgreSQL

brew services start postgresql@15  # macOS
sudo service postgresql start       # Linux

Import errors

Solution: Ensure you're in the project root and dependencies are installed

pip install -r requirements_db.txt
export PYTHONPATH="${PYTHONPATH}:$(pwd)"

403 Forbidden (Google Sheets)

Solution: Share your Google Sheet with the service account email

Rate limiting errors

Solution: The system has built-in exponential backoff. Check logs for details.

Getting Help

• Check the Issues page
• Review logs: docker-compose logs -f
• Database debugging: psql gradesync

---

🤝 Contributing

We welcome contributions! Please see CONTRIBUTING.md for guidelines.

Development Workflow

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

---

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

---

🙏 Acknowledgments

• UC Berkeley CS10 - Original use case and testing
• Gradescope - Assessment platform integration
• PrairieLearn - Online learning platform
• Contributors and maintainers

---

📞 Contact

For questions or support, please open an issue or contact the maintainers.

---

Built with ❤️ for educators and students