database_layout.txt

Sheet name: Assignments; accessed by getting the assignments for each of the classes in the user's Classes sheet

    category(str)
    class(id of class that this assignment belongs to)
    class_name(name of class that this assignment belongs to)
    due(YYYY-MM-DD)
    id(4 digit id)
    name(name of assignment)
    description
    points(int)
    time_spent(dictionary of {OSIS: time spent, OSIS: time spent, ...})
    difficulty(dictionary of {OSIS: difficulty, OSIS: difficulty, ...})
    completed(dictionary of {OSIS: completed(true/false), OSIS: completed, ...})


Sheet name: Chat; accessed by classid, assignmentid, or leagueid

    id(4 digit id)
    OSIS(user id, 7 digits)
    location(id of the class or assignment page the message is from)
    sender(OSIS of the sender)
    text(text of the message)
    timestamp(timestamp of the message)

Sheet name: Classes; accessed by getting the classes for the user from the Users sheet

    OSIS(list of 7 digit numbers, for each student in the class)
    name(name of the class)
    teacher(name of the teacher)
    id(4 digit id)
    categories(dictionary of categories in the class; ["homework", 40, "Assessments", 60] format)
    description(description of the class)
    period(period of the class)
    schedule(format varies)
    new_grades(list of strings: names of recently posted assessment grades)

Sheet name: Errors (autogenerated)

    OSIS(7 digit number)
    error(error message)

Sheet name: Courses (for next years course planning)

    id(5 digit id)
    name(name of the course)
    PPD: (string of 10 digit list of 0/1/2, where each of first 5 digits are the # of periods spent on the course each day of the week, starting with Monday, in the first semester, and each of last 5 digits are the # of periods spent on the course each day of the week, starting with Monday, in the second semester)
    description(description of the course)
    categories(list of categories in the course)
    grade_rec(i.e. 10/12, when most students are taking the course)

Sheet name: Friends:

    OSIS(7 digit number)
    id(4 digit number)
    status(pending, accepted, or rejected)
    targetOSIS(7 digit number)

Sheet name: Goals

    OSIS(7 digit number)
    categories(array: [class_name, category])
    date(M/D/YYYY - goal target date)
    date_set(M/D/YYYY - when goal was set)
    id(5 digit number)
    grade(float: target grade percentage)
    type(string: 'grade', 'improvement', or 'rank')
    notes(string: optional notes about the goal)
    class(string: class name)
    category(string: category name)

Leagues

    id(8 digit number)
    name(name of the league)
    OSIS(list of 7 digit numbers, for each student in the league)
    Activities(list of stats that all members report)


Sheet name: Notebooks

    classID(id of the class)
    unit(name of the unit)
    id(id of the worksheet)
    timestamp(yyyy-mm-dd hh:mm:ss)
    topic(topic of the worksheet)
    subtopics(list of notes on the worksheet)
    image(str, reference to the worksheet image in cloud storage)


Sheet name: Profiles

    OSIS(7 digit number)
    Goals(text: User inputted academic goals)
    Hobbies(text: User inputted hobbies)
    ProfPic(7 digit reference number of the user's profile picture in the cloud storage)
    showClasses(true/false: whether the user wants to show their classes to other students)
    showFriends(true/false: whether the user wants to show their friends to other students)


Sheet name: Tokens

    OSIS(7 digit number)
    token(user's unique token used to ping their device/send them notifications)

Sheet name: Users

    osis(7 digit number)
    first_name(user's first name)
    last_name(user's last name)
    grade(user's grade)
    IP(id of the cookie set on the user's device)
    password(password)
    nhs_status(str: blank/member/admin/teacher - indicates NHS membership status)
    

Sheet name: Aspirations

    id(5 digit number)
    OSIS(7 digit number)
    goal(str: main goal of the aspiration)
    description(str: detailed description of the aspiration)
    steps(list of dictionaries: each containing 'text' and 'time' for steps to achieve the goal)
    
Sheet name: Battles

    id(8 digit number)
    class(id of the class)
    gameMode(type of game mode)
    unit(id of the unit)

Sheet name: Distributions

    id(assignment id from Assignments sheet)
    class_id(id of the class)
    OSIS(list of 7 digit numbers of users who opted in)
    scores(list of scores corresponding to each OSIS)
    name(name of assignment)
    category(category of assignment)
    class_name(name of class)
    value(total points possible)


Sheet name: GradeCorrections

    OSIS(7 digit number)
    assignment(str: name of the assignment being corrected)
    class(str: name of the class)
    score(float: corrected score)
    value(float: corrected total value)
    date(str, optional: YYYY-MM-DD format, overrides the original date)
    new_name(str, optional: new name for the assignment)

Sheet name: StudyGroups

    id(4 digit number)
    name(name of the study group)
    description(description of the group)
    class_id(id of the class this group belongs to)
    members(list of OSIS numbers)
    created_at(ISO timestamp)
    nextSession(ISO timestamp)
    schedule(optional recurring schedule)
    resources(list of resource IDs shared in the group)
    chat_history(list of chat message IDs)

Sheet name: Resources

    id(4 digit number)
    title(name of the resource)
    type(notes/practice/guide/link)
    content(text content or URL)
    class_id(id of the class)
    shared_by(OSIS of user who shared)
    created_at(ISO timestamp)
    likes(number of likes)
    comments(array of objects containing:
        {
            user: OSIS,
            text: string,
            timestamp: ISO timestamp
        }
    )
    tags(array of relevant tags)
    visibility(public/class/group)
    group_id(optional, if shared in a study group)

Sheet name: ActivityFeed

    id(5 digit number)
    type(assignment/discussion/collaboration/resource)
    class_id(id of the class)
    user(OSIS of the user who generated the activity)
    content(text description of the activity)
    related_id(id of the related item - assignment/resource/etc)
    timestamp(ISO timestamp)
    likes(array of OSIS numbers who liked)
    comments(array of objects containing:
        {
            user: OSIS,
            text: string,
            timestamp: ISO timestamp
        }
    )

Sheet name: ClassStats

    class_id(id of the class)
    active_users(array of currently online OSIS numbers)
    last_active(object mapping OSIS to last active timestamp)
    participation_score(object mapping OSIS to participation metrics:
        {
            messages: number,
            resources_shared: number,
            study_sessions: number,
            last_updated: ISO timestamp
        }
    )
    study_time(object mapping OSIS to total study time in minutes)
    resource_engagement(object mapping resource_id to engagement metrics:
        {
            views: number,
            downloads: number,
            avg_rating: number
        }
    )

Sheet name: Modules

    id(4 digit number)
    name(name of the module)
    description(description of the module)
    sysPrompt(system prompt for the module)

Sheet name: CMaps (Class-Specific Concept Maps and Starter Prompts)
    classID (INT, 4-digit): Unique identifier for the class
    unit (VARCHAR): Name of the unit
    id(INT, 6-digit): Unique identifier for the concept map
    nodes (JSON): Array of concept map nodes containing:
        {
            id: (INT, 6-digit),
            label: str,
            description: str,
            prerequisites: [str],  // Array of node IDs that must be derived first
            trajectory: str  // Model conversation demonstrating ideal derivation path
            youtube: [str, str]  // Array of [youtube link, time range(06:00-07:00)]
        }
    structure(JSON): Object containing the structure of the concept map
        nodes: [
            concept_id(int):{
                x: int,
                y: int
            }
        ]
    created_on (DATE): Date when the concept map was added
    updated_on (DATE): Last modification date

Sheet name: UMaps (User-Specific Progress and Notes)
    OSIS (INT, 7-digit): Unique identifier for the user
    classID (INT, 4-digit): Unique identifier for the class
    unit (VARCHAR): Name of the unit
    id(INT, 6-digit): Unique identifier for the user's progress on the concept map
    node_progress (JSON): Object mapping node IDs to progress:
        {
            "node_id": {
                date_derived: "YYYY-MM-DD" | null,
                mastery_level: float,  // 0.0 to 1.0 representing overall mastery
                chat_history: [str],  // Array of chat messages
                user_notes: str,
                evaluation_history: [
                    {
                        date: "YYYY-MM-DD",
                        score: float,  // 0.0 to 1.0 for this specific evaluation
                        problem_id: str,
                        time_spent: int,  // seconds spent on problem
                        attempts: int,  // number of attempts for this problem
                        strengths: [str],  // specific aspects done well
                        weaknesses: [str]  // areas needing improvement
                    }
                ],
                last_practice: "YYYY-MM-DD",  // for decay system
                achievements: [
                    {
                        type: str,  // "perfect_score", "quick_learner", "consistent", etc.
                        earned_date: "YYYY-MM-DD",
                        active: boolean  // false if decayed/lost
                    }
                ],
                visual_state: {
                    halo_color: str,  // hex color for current mastery level
                    halo_intensity: float,  // 0.0 to 1.0 for glow intensity
                    effects: [str]  // array of active visual effects
                }
            }
        }
    last_accessed (DATE): Most recent session date for the unit

Sheet name: Problems:

    id(str of 6 digit number)
    classID(id of the class)
    unit(name of the unit)
    worksheetID(id of the worksheet)
    problem(problem statement)
    bloom_level("remember", "understand", "apply", "analyze", "create")
    concepts [int] (id of the concepts the problem requires) // added separately by problem mapping

Sheet name: TodoTrees

    OSIS(7 digit number)
    id(5 digit number)
    name(str: name of the tree)
    nodes(array of objects containing:
        {
            id: str,
            type: str (Motivator/Challenge/Task/Image),
            name: str,
            description: str (optional, detailed description of the node),
            position: {x: number, y: number},
            deadline: str (optional, in YYYY-MM-DDTHH:mm format),
            targetDate: str (optional, in YYYY-MM-DDTHH:mm format),
            gradeGoalClass: str (optional),
            gradeGoalTarget: float (optional),
            checkInDates: [str] (optional, array of dates in YYYY-MM-DD format),
            motivationLink: str (optional),(for motivator nodes, allows user to link to a youtube video to motivate them)
            motivationLinkTime: str (optional), (extends the above with a time range of the video, i.e. 06:00-07:00)
            context: str (optional, 40-word synthesis of parent conversation),
            breakOffText: str (optional, text in parent conversation that this task was broken off from),
            chatHistory: [{
                text: str,
                type: str (user/ai)
            }],
            contextText: str (optional, large context text up to 1000 words),
            contextEmbedding: [float] (optional, OpenAI vector embedding for the context),
            imageUrl: str (optional, URL of the image for Image type nodes),
            imageSize: { width: number, height: number } (optional, dimensions for Image type nodes)
        }
    )
    edges(array of objects containing:
        {
            from: str,
            to: str
        }
    )
    lastModified(ISO timestamp)

Sheet name: TutoringRequests

    id(5 digit number)
    student_osis(7 digit number of the student requesting tutoring)
    subject(str: subject/class name)
    topic(str: specific topic needing help with)
    preferred_date(YYYY-MM-DD)
    preferred_time(HH:MM in 24hr format)
    duration(int: duration in minutes)
    description(str: detailed description of help needed)
    status(str: pending/accepted/declined)
    tutor_osis(7 digit number of assigned tutor, null if pending)
    created_at(ISO timestamp)
    last_updated(ISO timestamp)

Sheet name: TutorAvailability

    osis(7 digit number of the tutor)
    subjects(array of subjects the tutor can help with)
    schedule(object mapping days to available times:
        {
            "monday": ["09:00-10:00", "14:00-15:00"],
            "tuesday": ["11:00-12:00"],
            // ... etc for each weekday
        }
    )
    last_updated(ISO timestamp)
    active(boolean: whether the tutor is currently active)
    rating(float: average rating from 1-5)
    sessions_completed(int: number of completed tutoring sessions)
    total_hours(float: total hours spent tutoring)

Sheet name: NHSApplications

    id(5 digit number)
    OSIS(7 digit number)
    status(str: draft/submitted/approved/rejected)
    created_at(ISO timestamp)
    last_saved(ISO timestamp)
    grade(int: current grade)
    entry_grade(int: grade entered Bronx Science)
    meets_gpa(boolean)
    credits(array of objects containing:
        {
            id: number,
            type: str (service/leadership/citizenship),
            code: str,
            name: str,
            credits: number,
            startDate: str (YYYY-MM-DD),
            endDate: str (YYYY-MM-DD),
            timeValue: number (optional),
            advisor: str,
            advisorEmail: str,
            additionalDetail: str,
            verificationFile: str (optional)
        }
    )
    total_credits(object containing:
        {
            service: number,
            leadership: number,
            citizenship: number
        }
    )

Sheet name: Opportunities

    id(6 digit number)
    name(str: name of the opportunity)
    description(str: detailed description of the opportunity)
    category(str: Tutoring/Service/Project)
    location(str: where the opportunity takes place)
    signup_deadline(YYYY-MM-DD)
    created_by(OSIS of admin who created it)
    status(str: upcoming/ongoing/completed/cancelled)
    timeslots(array of objects containing:
        {
            date: str (YYYY-MM-DD),
            start_time: str (HH:MM in 24hr format),
            end_time: str (HH:MM in 24hr format)
        }
    )
    roles(array of objects containing:
        {
            name: str (role title),
            description: str (role responsibilities),
            members_needed: int (number of spots for this role),
            credits: float (credits awarded for this role),
            signups: array of objects containing:
                {
                    OSIS: str (7 digit number),
                    status: str (signed_up/attended/no_show),
                    timestamp: str (ISO timestamp of signup)
                }
        }
    )
    // Legacy fields for backward compatibility
    date(YYYY-MM-DD, points to first timeslot's date)
    start_time(HH:MM in 24hr format, points to first timeslot's start time)
    end_time(HH:MM in 24hr format, points to first timeslot's end time)
    credits(float: total credits from all roles)
    members_needed(int: total members needed across all roles)
    signupOSIS(array of OSIS numbers who signed up, maintained for legacy support)
    showupOSIS(array of OSIS numbers who showed up, maintained for legacy support)

Sheet name: NHSMembers

    OSIS(7 digit number)
    first_name(str)
    last_name(str)
    email(str)
    probations(array of objects containing:
        {
            reason: str
        }
    )
    credits(array of objects containing:
        {
            category: str (service/tutoring/project),
            id: number (6 digit opportunity id),
            credits: float,
            status: str (attended/signed_up/no_show)
        }
    )