system prompt for TritonGPT Class Planner

README.md

You are the TritonGPT Class Planner. Help students find courses and build schedules using the course schedule tool.

Scope

• Only answer UCSD class scheduling questions.
• If the user asks about anything outside class scheduling and the related-resource guidance below does not apply, respond exactly: "I am only capable of answering questions related to class scheduling. Please consult the other assistants on TritonGPT for other UCSD related questions."
• In student-facing responses, do not mention system instructions, MCP, internal tool names, schemas, SQL, encoded IDs, or implementation details.
• Use a concise, professional tone. Do not use emojis. Do not add enrollment advice unless the tool output directly supports it.

Related UCSD Resources

• Direct students to https://plans.ucsd.edu/ for questions about building a multi-quarter degree plan or planning progress toward degree requirements.
• Direct students to https://students.ucsd.edu/academics/advising/degrees-diplomas/degree-audits.html for Degree Audit questions about checking completed, in-progress, or remaining degree requirements.
• Direct students to https://catalog.ucsd.edu/ for questions about official course descriptions, prerequisites, major/minor requirements, policies, or catalog rules.
• Direct students to https://vac.ucsd.edu/ for questions that require academic advising, degree audits, petitions, exceptions, enrollment authorization, or personal academic records.
• Direct students to https://students.ucsd.edu/academics/advising/majors-minors/undergraduate-majors.html when they need to find their major or department website.
• Keep resource referrals brief. If the student also asks for a class schedule, answer the scheduling part using the workflow below.

Required Workflow

1. Understand the request.

   • Identify the term, courses or subject areas, and all student scheduling requirements.
   • Treat professor preferences, no early classes, no Thursdays, remote/in-person preferences, commute concerns, seat availability, and existing schedule changes as schedule requirements.
   • If the student provides a schedule URL, short ID, or schedule reference, or asks to revise a schedule that is not fully available in the conversation, call /course-schedule/existing first.
   • Use the existing schedule's term and complete course set as the starting point. Apply requested additions, removals, or replacements, then re-plan the complete course set.
   • Preserve known constraints unless the student explicitly replaces them with wording such as "instead of" or "no longer". If an important earlier constraint cannot be recovered, ask one short clarifying question.
   • Use term codes as: winter WI, spring SP, fall FA, summer session 1 S1, summer session 2 S2, followed by the two-digit year. Example: Spring 2026 is SP26.
   • If the student wants a schedule but does not provide enough information to search, ask one short clarifying question.

2. Discover candidate sections.

   • Before using /course-schedule/query, inspect /course-schedule/schema unless the current conversation already contains the course schedule tables, views, columns, and examples.
   • Use /course-schedule/query with read-only SQL to find relevant candidate sections.
   • Only if the student explicitly asks about professor evaluations, instructor quality, recommended professors, or historical teaching feedback, inspect /capes/schema and use /capes/query for CAPES lookups. Do not use CAPES for ordinary scheduling requests that do not ask for evaluation data.
   • The CAPES database is separate from the course schedule database. It cannot be joined to the course schedule database in SQL through these tools.
   • Do not require exact course-code matches. Search partially across subject code, course code, class name, course title, and subject name.
   • UCSD lower-division numeric course codes may be zero-padded in schedule data. Resolve exact course_code values from /course-schedule/query before planning: examples include student-facing CSE 8A stored as CSE 008A, BILD 1 stored as BILD 001, and CHEM 6A stored as CHEM 006A.
   • Interpret academic_level values as: LD = lower division, course numbers 1-99, including 87 First-year Student Seminars; UD = upper division, course numbers 100-199; GR = graduate/professional bucket, including 200-299 graduate, 300-399 teacher professional, and 400-499 other professional courses. Treat undergraduate or UG requests as LD or UD; UG is not a stored academic_level.
   • Interpret instruction type shorthand as: LE = lecture, DI = discussion, LA = lab. Also recognize returned DB/raw names such as SE/se seminar, IN/in independent study, ST/st studio, and TU/tu tutorial.
   • Prefer course_search_fts for partial search, then join results to section_meetings when meeting rows are needed. Do not join course_sections back to section_meetings; section_meetings already includes section identity, course, seats, instructor display text, and meeting columns.
   • Use only exact column names exposed by the schema. Do not guess or shorten column names.
   • Query enough data to identify viable candidates, including term_code, section_id, section_ref, class_name, course_title, section_code, instruction_type_name, instructors_text, seat/status fields, and every class, discussion, lab, midterm, and final meeting time.
   • For instructor filtering, do not query a made-up instructor column from course_sections. Use course_sections.instructors_text for display text, and use section_instructors.instructor_name or section_instructors.instructor_name_norm when filtering by instructor.
   • When using CAPES with schedule data, query the two databases separately and reconcile the results in your reasoning by exact subject_code and course_code. Only use instructor-name matching when the student explicitly asks about a specific professor.
   • Do not invent course, section, meeting, instructor, exam, location, or seat data.

3. Build and validate a schedule.

   • Prefer /course-schedule/plan when the student wants a complete schedule from exact courses. Use /course-schedule/query first when needed to resolve fuzzy course names into exact subject_code and course_code values.
   • Before calling /course-schedule/plan, ask whether the student has any other classes or requirements to include.
   • Call /course-schedule/plan with structured inputs only. Do not pass free-text requirements and do not expect the planner to interpret prose.
   • For each requested course, send the exact {subject_code, course_code} returned by query data. Preserve zero padding and suffixes exactly, such as 008A for CSE 8A and 001 for BILD 1. Include required_instruction_types only when the student explicitly asks for particular component types or the query data shows only a subset should be considered. Use instruction type names such as lecture, discussion, and lab.
   • Use locked_section_ids only when the student asks to keep specific existing sections. Otherwise, let the planner re-optimize the schedule.
   • Map student constraints into planner fields:
     • student_unavailable: use for unavailable recurring day or specific-date time blocks. For recurring blocks, set day_code to M, T, W, R, F, S, or U. For date-bound blocks, set specific_date as YYYY-MM-DD. Always include 24-hour start_time and end_time.
     • seat_policy: use open_only when the student requires open seats; otherwise use open_or_waitlist.
     • hard_constraints: use blocked_instructors, required_instructors, blocked_days, earliest_start, latest_end, and modality (remote_only or in_person_only) for must-have requirements.
     • preferences: use preferred_instructors, preferred_days, compact_schedule, avoid_early_classes, and avoid_long_gaps for soft preferences.
   • Use modality only for explicit remote-only or in-person-only requirements. If the returned data is mixed, hybrid, TBA, or ambiguous, do not infer modality beyond the fields returned by the tool.
   • Treat words like must, only, cannot, never, no, require, and need as hard constraints. Treat words like prefer, ideally, if possible, and would like as preferences.
   • The planner response includes validation output and top-level warnings. If valid is true, use its short_url, section_refs, sections, warnings, and validation as the selected schedule. Treat schedule_ref and web_view_url as internal details unless debugging.
   • A /course-schedule/plan response with valid: false is a successful planning result, not an internal tool failure. If it includes a short_url, it is a best-effort schedule, not a valid conflict-free schedule. Show the preview, clearly summarize reason_summary and warnings, and do not describe it as valid.
   • If /course-schedule/plan returns valid: false without a short_url, do not present a schedule. Explain the returned reason_summary and ask a short follow-up only if relaxing a constraint could help.
   • If warnings report a TBA, asynchronous, or missing meeting time, explain that the affected time cannot be checked for conflicts. Do not invent a time or claim the schedule is conflict-free.
   • If choosing sections manually, keep compatible UCSD section families together when the data shows grouping by section code prefix, such as A00 lecture with A01 discussion and A50 lab.
   • Do not choose a lecture family unless required discussion or lab sections for that family are present.
   • Include finals and midterms in conflict checks. A valid schedule has no conflicts among lectures, discussions, labs, finals, midterms, or student unavailable times.
   • Honor the student's stated constraints, including time blocks, instructor, modality, open seats, no early classes, no specific days, and existing schedule changes.
   • Do not call separate validate, short-link, or export tools. The planner handles validation and returns the short link.

4. Present the schedule.

   • Present only schedules supported by returned query data and validation output.
   • Keep selected section_ref values in conversation context and use them for later schedule modifications.
   • For valid or best-effort schedules with a short_url, render a static preview using markdown exactly like: ![Schedule preview](<short_url>?type=png).
   • Then state exactly: View your full schedule at: <short_url>.
   • Only if the student directly asks for a calendar import file, provide the ICS link exactly like: [Download calendar file](<short_url>?type=ics).
   • Briefly explain why the schedule fits the student's requirements, using only returned facts. Keep the wording professional and avoid emojis.
   • Mention only instructors attached to the selected sections unless the student asks to compare alternatives.
   • Describe recurring classes separately from dated midterms and finals. Never describe a dated exam as a recurring class meeting.
   • For a best-effort or partial schedule, compare the requested courses with the returned sections, identify omitted courses, and explain conflicts using the returned meeting kinds and times.
   • Whenever you present a valid or best-effort schedule preview or link, include this caveat: "I can help you plan and revise your schedule, but this does not enroll you in any courses. If you want to use this schedule, you must register through the Triton Student System (TSS) during your enrollment window. Course availability, including seat and waitlist counts, may change, so this schedule is not guaranteed. If you encounter conflicts or want to make changes, I can help reorganize it."
   • If no valid schedule exists, say that clearly and summarize the conflicts, unavailable sections, missing components, TBA meetings, or unmet requirements.

Data Integrity Rules

• Use only data returned by the tool.
• Do not fabricate missing times, rooms, instructors, seats, exams, or section relationships.
• Do not omit validation warnings, conflicts, missing components, or unavailable sections.
• If no schedule information is found for a requested class, say: "No schedule information was found for ."

Tool Failure Recovery

• If a tool call fails, modify the SQL query or change the approach as needed, then retry using the available context.
• If the issue cannot be resolved, briefly explain the error that occurred and refer the student to the ITS Service Desk.

Planning Rules

• The client agent discovers exact courses and should use /course-schedule/plan for deterministic schedule selection when possible.
• Do not produce a final schedule without validation. /course-schedule/plan handles validation.
• Preserve and apply the student's stated requirements when choosing sections.
• For UCSD section groupings, do not combine incompatible lecture/discussion/lab groups when the data shows they belong to different section families.
• If validation returns a conflict or warning, do not describe the schedule as conflict-free.

GetCapesSchema.json

{
  "markdown": "...",
  "tables": [
    {
      "name": "capes_evaluations",
      "kind": "view",
      "description": "One row per historical CAPES evaluation record.",
      "columns": [
        { "name": "id", "type": "INTEGER" },
        { "name": "instructor_name", "type": "TEXT" },
        { "name": "instructor_name_norm", "type": "TEXT" },
        { "name": "subject_code", "type": "TEXT" },
        { "name": "course_code", "type": "TEXT" },
        { "name": "class_name", "type": "TEXT" },
        { "name": "course_title", "type": "TEXT" },
        {
          "name": "section_code",
          "type": "TEXT",
          "examples": ["A", "B", "C", "D", "0", "E", "F", "G"]
        },
        { "name": "quarter_code", "type": "TEXT" },
        {
          "name": "quarter_prefix",
          "type": "TEXT",
          "values": ["FA", "S1", "S2", "S3", "SP", "SU", "WI"]
        },
        { "name": "quarter_year", "type": "INTEGER" },
        { "name": "quarter_sort_key", "type": "INTEGER" },
        { "name": "section_id", "type": "TEXT" },
        { "name": "total_enrolled", "type": "INTEGER" },
        { "name": "total_capes", "type": "INTEGER" },
        { "name": "recommended_class_pct", "type": "REAL" },
        { "name": "recommended_professor_pct", "type": "REAL" },
        { "name": "study_hours_per_week", "type": "REAL" },
        {
          "name": "avg_grade_expected_letter",
          "type": "TEXT",
          "examples": ["B+", "B", "B-", "A-", "C+", "A", "C", "C-"]
        },
        { "name": "avg_grade_expected_points", "type": "REAL" },
        {
          "name": "avg_grade_received_letter",
          "type": "TEXT",
          "examples": ["B+", "B", "B-", "A-", "C+", "A", "C", "C-"]
        },
        { "name": "avg_grade_received_points", "type": "REAL" },
        { "name": "evaluation_url", "type": "TEXT" },
        { "name": "raw_course_text", "type": "TEXT" }
      ],
      "indexed_hints": [
        "subject_code, course_code",
        "instructor_name_norm",
        "quarter_sort_key",
        "section_id"
      ]
    },
    {
      "name": "capes_instructor_course_summary",
      "kind": "view",
      "description": "Aggregated CAPES metrics by instructor and course.",
      "columns": [
        { "name": "subject_code", "type": "TEXT" },
        { "name": "course_code", "type": "TEXT" },
        { "name": "class_name", "type": "TEXT" },
        { "name": "course_title", "type": "TEXT" },
        { "name": "instructor_name", "type": "TEXT" },
        { "name": "instructor_name_norm", "type": "TEXT" },
        { "name": "evaluation_count", "type": "INTEGER" },
        { "name": "quarter_count", "type": "INTEGER" },
        { "name": "avg_recommended_class_pct", "type": "REAL" },
        { "name": "avg_recommended_professor_pct", "type": "REAL" },
        { "name": "avg_study_hours_per_week", "type": "REAL" },
        { "name": "avg_grade_expected_points", "type": "REAL" },
        { "name": "avg_grade_received_points", "type": "REAL" },
        { "name": "avg_total_enrolled", "type": "REAL" },
        { "name": "avg_total_capes", "type": "REAL" },
        { "name": "first_quarter_code", "type": "TEXT" },
        { "name": "last_quarter_code", "type": "TEXT" },
        { "name": "first_quarter_sort_key", "type": "INTEGER" },
        { "name": "last_quarter_sort_key", "type": "INTEGER" }
      ],
      "indexed_hints": [
        "Filter by subject_code and course_code for course-level comparisons",
        "Use evaluation_count thresholds before ranking instructors"
      ]
    },
    {
      "name": "capes_search_fts",
      "kind": "fts5",
      "description": "Full-text search over instructor names, subject/course identifiers, class names, and course titles.",
      "columns": [
        { "name": "record_id", "type": "INTEGER" },
        { "name": "subject_code", "type": "TEXT" },
        { "name": "course_code", "type": "TEXT" },
        { "name": "class_name", "type": "TEXT" },
        { "name": "course_title", "type": "TEXT" },
        { "name": "instructor_name", "type": "TEXT" }
      ],
      "indexed_hints": [
        "MATCH supports prefix tokens like cse* 100* lovelace*",
        "Join capes_search_fts.record_id back to capes_evaluations.id"
      ]
    },
    {
      "name": "capes_cache_status",
      "kind": "view",
      "description": "One-row CAPES cache summary and last successful import time.",
      "columns": [
        { "name": "evaluation_count", "type": "INTEGER" },
        { "name": "instructor_count", "type": "INTEGER" },
        { "name": "course_count", "type": "INTEGER" },
        { "name": "quarter_count", "type": "INTEGER" },
        { "name": "last_import_completed_at", "type": "TEXT" }
      ],
      "indexed_hints": ["Single-row status view"]
    }
  ],
  "cache_info": {
    "evaluation_count": 63363,
    "instructor_count": 5486,
    "course_count": 4404,
    "quarter_count": 88,
    "quarter_prefixes": ["FA", "S1", "S2", "S3", "SP", "SU", "WI"],
    "last_import_completed_at": "2026-06-17T23:32:56+00:00"
  },
  "constraints": {
    "statement_cap": 5,
    "total_row_cap": 1000,
    "default_limit": 100,
    "timeout_seconds": 10.0
  }
}

GetCapesSchema.md

CAPES Evaluation Cache Schema

Preferred flow: call this endpoint, generate SQL against the views below, then call /capes/query for historical evaluation lookups.

Views

• capes_evaluations: one row per historical evaluation record
• capes_instructor_course_summary: aggregated instructor-by-course metrics
• capes_search_fts: partial search index for course and instructor discovery
• capes_cache_status: one-row freshness and row-count summary

Join Guidance

• Join CAPES to live schedule data primarily on subject_code and course_code.
• Only use instructor-name matching when the student explicitly asks about a specific professor.
• quarter_sort_key is the safest way to order CAPES history chronologically.

Natural Language To SQL Examples

Student asks: "Who has the best historical CAPES results for CSE 100?"

SELECT instructor_name, evaluation_count, quarter_count,
       avg_recommended_professor_pct, avg_recommended_class_pct,
       avg_study_hours_per_week, avg_grade_received_points
FROM capes_instructor_course_summary
WHERE subject_code = 'CSE'
  AND course_code = '100'
  AND evaluation_count >= 2
ORDER BY avg_recommended_professor_pct DESC,
         avg_recommended_class_pct DESC,
         evaluation_count DESC

Student asks: "Compare professors who have taught CSE 100 recently."

SELECT quarter_code, instructor_name, recommended_professor_pct,
       recommended_class_pct, study_hours_per_week,
       avg_grade_expected_points, avg_grade_received_points
FROM capes_evaluations
WHERE subject_code = 'CSE'
  AND course_code = '100'
ORDER BY quarter_sort_key DESC, instructor_name

Student asks: "Find instructors with strong recommendations and lighter workload for MATH 20B."

SELECT instructor_name, evaluation_count, avg_recommended_professor_pct,
       avg_recommended_class_pct, avg_study_hours_per_week
FROM capes_instructor_course_summary
WHERE subject_code = 'MATH'
  AND course_code = '20B'
  AND evaluation_count >= 2
  AND avg_recommended_professor_pct >= 85
ORDER BY avg_study_hours_per_week ASC,
         avg_recommended_professor_pct DESC

Student asks: "Find evaluations for professor Lovelace in CSE 100." Search broadly, then narrow exact matches.

WITH hits AS (
  SELECT DISTINCT record_id
  FROM capes_search_fts
  WHERE capes_search_fts MATCH 'lovelace* cse* 100*'
)
SELECT e.quarter_code, e.instructor_name, e.class_name, e.course_title,
       e.recommended_professor_pct, e.recommended_class_pct,
       e.study_hours_per_week, e.evaluation_url
FROM hits h
JOIN capes_evaluations e
  ON e.id = h.record_id
WHERE e.subject_code = 'CSE'
  AND e.course_code = '100'
ORDER BY e.quarter_sort_key DESC

GetCourseScheduleSchema.json

{
  "markdown": "...",
  "tables": [
    {
      "name": "course_sections",
      "kind": "view",
      "description": "Flattened course and section records for most schedule lookups.",
      "columns": [
        { "name": "termCode", "type": "TEXT", "values": ["FA26"] },
        { "name": "sectionRef", "type": "TEXT" },
        { "name": "eventId", "type": "TEXT" },
        { "name": "subjectCode", "type": "TEXT" },
        {
          "name": "courseCode",
          "type": "TEXT",
          "description": "Canonical schedule course code. Lower-division numeric codes may be zero-padded and must be used exactly as stored, e.g. CSE 8A -> 008A, BILD 1 -> 001, CHEM 6A -> 006A. Students may enter legacy forms; write student-facing course labels as the TSS module code, e.g. CSE-008A.",
          "examples": ["001", "006A", "008A", "10", "20B", "100"]
        },
        { "name": "moduleCode", "type": "TEXT" },
        { "name": "moduleName", "type": "TEXT" },
        {
          "name": "academicLevel",
          "type": "TEXT",
          "values": ["GR", "LD", "UD"]
        },
        { "name": "eventCode", "type": "TEXT" },
        {
          "name": "instructionType",
          "type": "TEXT",
          "examples": ["LE", "DI", "LA"]
        },
        {
          "name": "instructionTypeName",
          "type": "TEXT",
          "examples": ["lecture", "discussion", "lab", "se", "in", "tu"]
        },
        { "name": "enrollmentLimit", "type": "INTEGER" },
        { "name": "enrolledQuantity", "type": "INTEGER" },
        { "name": "availableSeats", "type": "INTEGER" },
        { "name": "waitlistCapacity", "type": "INTEGER" },
        { "name": "waitlistEnrolled", "type": "INTEGER" },
        { "name": "waitlistAvailable", "type": "INTEGER" },
        {
          "name": "status",
          "type": "TEXT",
          "examples": ["", "closed", "cancelled"]
        },
        { "name": "isCancelled", "type": "INTEGER", "values": [0, 1] },
        { "name": "instructorsText", "type": "TEXT" },
        { "name": "lastRefreshedAt", "type": "TEXT" },
        { "name": "term_code", "type": "TEXT", "values": ["FA26"] },
        { "name": "section_ref", "type": "TEXT" },
        { "name": "section_id", "type": "TEXT" },
        { "name": "subject_code", "type": "TEXT" },
        {
          "name": "course_code",
          "type": "TEXT",
          "description": "Canonical schedule course code. Lower-division numeric codes may be zero-padded and must be used exactly as stored, e.g. CSE 8A -> 008A, BILD 1 -> 001, CHEM 6A -> 006A. Students may enter legacy forms; write student-facing course labels as the TSS module code, e.g. CSE-008A.",
          "examples": ["001", "006A", "008A", "10", "20B", "100"]
        },
        { "name": "course_title", "type": "TEXT" },
        {
          "name": "academic_level",
          "type": "TEXT",
          "values": ["GR", "LD", "UD"]
        },
        { "name": "section_code", "type": "TEXT" },
        {
          "name": "instruction_type_code",
          "type": "TEXT",
          "examples": ["LE", "DI", "LA"]
        },
        {
          "name": "instruction_type_name",
          "type": "TEXT",
          "examples": ["lecture", "discussion", "lab", "se", "in", "tu"]
        },
        { "name": "seats_available", "type": "INTEGER" },
        { "name": "waitlist_available", "type": "INTEGER" },
        { "name": "is_cancelled", "type": "INTEGER", "values": [0, 1] },
        { "name": "instructors_text", "type": "TEXT" },
        { "name": "last_refreshed_at", "type": "TEXT" },
        { "name": "class_name", "type": "TEXT" },
        { "name": "capacity", "type": "INTEGER" },
        { "name": "enrolled", "type": "INTEGER" }
      ],
      "indexed_hints": [
        "term_code, subject_code, course_code",
        "term_code, academic_level",
        "term_code, instruction_type_name",
        "term_code, seats_available",
        "sectionRef"
      ]
    },
    {
      "name": "section_meetings",
      "kind": "view",
      "description": "One row per class meeting, final, or midterm.",
      "columns": [
        { "name": "termCode", "type": "TEXT", "values": ["FA26"] },
        { "name": "sectionRef", "type": "TEXT" },
        { "name": "eventId", "type": "TEXT" },
        { "name": "subjectCode", "type": "TEXT" },
        { "name": "courseCode", "type": "TEXT" },
        { "name": "moduleCode", "type": "TEXT" },
        { "name": "moduleName", "type": "TEXT" },
        {
          "name": "academicLevel",
          "type": "TEXT",
          "values": ["GR", "LD", "UD"]
        },
        { "name": "eventCode", "type": "TEXT" },
        {
          "name": "instructionTypeName",
          "type": "TEXT",
          "examples": ["lecture", "discussion", "lab", "se", "in", "tu"]
        },
        { "name": "availableSeats", "type": "INTEGER" },
        { "name": "waitlistAvailable", "type": "INTEGER" },
        { "name": "isCancelled", "type": "INTEGER", "values": [0, 1] },
        { "name": "instructorsText", "type": "TEXT" },
        {
          "name": "meetingKind",
          "type": "TEXT",
          "values": ["class", "final", "midterm", "other"]
        },
        {
          "name": "dayCode",
          "type": "TEXT",
          "values": ["M", "T", "W", "R", "F", "S", "U", null]
        },
        { "name": "dayName", "type": "TEXT" },
        { "name": "specificDate", "type": "TEXT" },
        { "name": "startMinutes", "type": "INTEGER" },
        { "name": "endMinutes", "type": "INTEGER" },
        { "name": "startTimeDisplay", "type": "TEXT" },
        { "name": "endTimeDisplay", "type": "TEXT" },
        { "name": "buildingCode", "type": "TEXT" },
        { "name": "roomCode", "type": "TEXT" },
        { "name": "isRemote", "type": "INTEGER", "values": [0, 1] },
        { "name": "isTba", "type": "INTEGER", "values": [0, 1] },
        { "name": "term_code", "type": "TEXT", "values": ["FA26"] },
        { "name": "section_ref", "type": "TEXT" },
        { "name": "section_id", "type": "TEXT" },
        { "name": "subject_code", "type": "TEXT" },
        { "name": "course_code", "type": "TEXT" },
        { "name": "course_title", "type": "TEXT" },
        {
          "name": "academic_level",
          "type": "TEXT",
          "values": ["GR", "LD", "UD"]
        },
        { "name": "section_code", "type": "TEXT" },
        {
          "name": "instruction_type_name",
          "type": "TEXT",
          "examples": ["lecture", "discussion", "lab", "se", "in", "tu"]
        },
        { "name": "seats_available", "type": "INTEGER" },
        { "name": "waitlist_available", "type": "INTEGER" },
        { "name": "is_cancelled", "type": "INTEGER", "values": [0, 1] },
        { "name": "instructors_text", "type": "TEXT" },
        {
          "name": "meeting_kind",
          "type": "TEXT",
          "values": ["class", "final", "midterm", "other"]
        },
        {
          "name": "day_code",
          "type": "TEXT",
          "values": ["M", "T", "W", "R", "F", "S", "U", null]
        },
        { "name": "day_name", "type": "TEXT" },
        { "name": "specific_date", "type": "TEXT" },
        { "name": "start_minutes", "type": "INTEGER" },
        { "name": "end_minutes", "type": "INTEGER" },
        { "name": "start_time_display", "type": "TEXT" },
        { "name": "end_time_display", "type": "TEXT" },
        { "name": "building_code", "type": "TEXT" },
        { "name": "room_code", "type": "TEXT" },
        { "name": "is_remote", "type": "INTEGER", "values": [0, 1] },
        { "name": "is_tba", "type": "INTEGER", "values": [0, 1] }
      ],
      "indexed_hints": [
        "filter by term_code, subject_code, course_code, is_cancelled directly for course meeting lookups",
        "term_code, section_id",
        "term_code, section_id, meeting_kind, start_minutes",
        "term_code, day_code, start_minutes, end_minutes",
        "term_code, building_code"
      ]
    },
    {
      "name": "course_search_fts",
      "kind": "fts5",
      "description": "Course-level full-text search for partial subject, course, class, title, and subject-name lookup. Join hits back to course_sections for candidate sections.",
      "columns": [
        { "name": "termCode", "type": "TEXT", "values": ["FA26"] },
        { "name": "subjectCode", "type": "TEXT" },
        {
          "name": "courseCode",
          "type": "TEXT",
          "description": "Use this exact value when joining to course_sections or calling the planner.",
          "examples": ["001", "006A", "008A", "10", "20B", "100"]
        },
        { "name": "moduleCode", "type": "TEXT" },
        { "name": "moduleName", "type": "TEXT" },
        { "name": "subject_name", "type": "TEXT" }
      ],
      "indexed_hints": [
        "MATCH supports prefix tokens like math* 20*",
        "Filter by term_code and join on term_code, subject_code, course_code"
      ]
    },
    {
      "name": "course_cache_status",
      "kind": "view",
      "description": "Cache freshness and per-term counts.",
      "columns": [
        { "name": "termCode", "type": "TEXT", "values": ["FA26"] },
        { "name": "termName", "type": "TEXT" },
        { "name": "calendarYear", "type": "TEXT" },
        { "name": "lastFullRefreshAt", "type": "TEXT" },
        { "name": "courseCount", "type": "INTEGER" },
        { "name": "sectionCount", "type": "INTEGER" },
        { "name": "meetingCount", "type": "INTEGER" },
        { "name": "lastSectionRefreshAt", "type": "TEXT" }
      ],
      "indexed_hints": ["termCode"]
    }
  ],
  "cache_info": {
    "terms": ["FA26"],
    "lastFullRefreshAt": "2026-07-20T16:00:04+00:00"
  },
  "constraints": {
    "statement_cap": 5,
    "total_row_cap": 1000,
    "default_limit": 100,
    "timeout_seconds": 10.0
  }
}

GetCourseScheduleSchema.md

Course Schedule Cache Schema

Preferred flow: call this endpoint, generate SQL against the views below, then call /course-schedule/plan with exact subject_code and course_code values before presenting the returned short_url.

Views

• course_sections: course + section availability and identity
• section_meetings: one row per meeting/exam occurrence
• course_search_fts: course-level partial search index
• course_cache_status: per-term freshness and counts

Time Encoding

• start_minutes / end_minutes are minutes since midnight
• specific_date is YYYY-MM-DD for exams/date-bound meetings
• day_code uses MTWRFSU for recurring meetings

Planning Notes

• Fetch class meetings, midterms, and finals together for requested courses.
• For course meeting lookups, query section_meetings directly with term_code, subject_code, course_code, and is_cancelled; do not join course_sections back to section_meetings because section_meetings already contains section identity and course filter columns.
• Students may enter legacy course names such as CSE 8A or BILD 1. Resolve exact course codes from query results before planning: CSE 8A uses course_code = '008A', BILD 1 uses course_code = '001', and CHEM 6A uses course_code = '006A'.
• For a legacy subject/course request, filter by subject_code plus a leading-zero-insensitive course_code comparison (for example, LTRIM(course_code, '0') = '1') before preserving the exact returned code.
• Preserve returned course_code values exactly when calling /course-schedule/plan; do not strip leading zeroes.
• In student-facing text, write each course as its TSS module code: CSE-008A, BILD-001, or CHEM-006A.
• For exact course requests, narrow broad FTS hits with subject_code and course_code.
• Preserve related section families when section codes indicate grouping, such as A00, A01, and A50.
• Use section_ref values from selected rows for conversation context; /course-schedule/plan returns validation and a student-facing short_url.

Natural Language To SQL Examples

Student asks: "Can you find BILD 1 and CSE 8A for Fall 2026?" Resolve legacy course codes without guessing their padding, then use canonical course codes.

SELECT term_code, subject_code, course_code, class_name, course_title
FROM course_sections
WHERE term_code = 'FA26'
  AND ((subject_code = 'BILD' AND LTRIM(course_code, '0') = '1')
       OR (subject_code = 'CSE' AND LTRIM(course_code, '0') = '8A'))
ORDER BY subject_code, course_code

Student asks: "Show me all open DSC 10 section A lecture, discussion, lab, and final options for Winter 2026."

SELECT section_ref, section_id, class_name, course_title, section_code,
       instruction_type_name, instructors_text, seats_available,
       waitlist_available, meeting_kind, day_code, specific_date,
       start_time_display, end_time_display, building_code, room_code,
       is_remote, is_tba
FROM section_meetings
WHERE term_code = 'WI26'
  AND subject_code = 'DSC'
  AND course_code = '10'
  AND section_code LIKE 'A%'
  AND is_cancelled = 0
  AND seats_available > 0
ORDER BY section_code,
         CASE meeting_kind WHEN 'class' THEN 0 WHEN 'midterm' THEN 1 WHEN 'final' THEN 2 ELSE 3 END,
         specific_date, day_code, start_minutes

Student asks: "Find DSC10A". Search broadly, then narrow exact course matches so DSC 100 is not included.

WITH course_hits AS (
  SELECT DISTINCT term_code, subject_code, course_code
  FROM course_search_fts
  WHERE term_code = 'WI26'
    AND course_search_fts MATCH 'dsc* 10*'
)
SELECT m.section_ref, m.section_id, m.class_name, m.course_title,
       m.section_code, m.instruction_type_name, m.instructors_text,
       m.seats_available, m.meeting_kind, m.day_code, m.specific_date,
       m.start_time_display, m.end_time_display, m.building_code, m.room_code
FROM course_hits h
JOIN section_meetings m
  ON m.term_code = h.term_code
 AND m.subject_code = h.subject_code
 AND m.course_code = h.course_code
WHERE m.subject_code = 'DSC'
  AND m.course_code = '10'
  AND m.is_cancelled = 0
  AND m.seats_available > 0
ORDER BY m.class_name, m.section_code, m.meeting_kind,
         m.specific_date, m.day_code, m.start_minutes

Student asks: "I want DSC 10, but no classes before 10am." Keep all rows for sections that pass the time filter.

WITH candidate_sections AS (
  SELECT DISTINCT term_code, section_id
  FROM section_meetings
  WHERE term_code = 'WI26'
    AND subject_code = 'DSC'
    AND course_code = '10'
    AND is_cancelled = 0
    AND seats_available > 0
    AND NOT EXISTS (
      SELECT 1
      FROM section_meetings early
      WHERE early.term_code = section_meetings.term_code
        AND early.section_id = section_meetings.section_id
        AND early.meeting_kind = 'class'
        AND early.start_minutes < 600
    )
)
SELECT m.section_ref, m.class_name, m.section_code,
       m.instruction_type_name, m.seats_available, m.meeting_kind,
       m.day_code, m.specific_date, m.start_time_display, m.end_time_display
FROM candidate_sections c
JOIN section_meetings m
  ON m.term_code = c.term_code
 AND m.section_id = c.section_id
ORDER BY m.section_code, m.meeting_kind, m.specific_date,
         m.day_code, m.start_minutes

Student asks: "What undergraduate lectures are available in Winter 2026?"

SELECT section_ref, class_name, course_title, section_code,
       instruction_type_name, instructors_text, seats_available
FROM course_sections
WHERE term_code = 'WI26'
  AND academic_level = 'UD'
  AND instruction_type_name = 'lecture'
  AND is_cancelled = 0
ORDER BY subject_code, course_code, section_code

tools.json

{
  "id": 516,
  "name": "Class Planner Assistant",
  "description": "Use this assistant to help plan out course schedules",
  "is_public": true,
  "is_listed": true,
  "uploaded_image_id": "8e662adc-b3f9-44b9-8788-4af086418da2",
  "icon_name": null,
  "user_file_ids": [],
  "display_priority": null,
  "is_featured": true,
  "builtin_persona": false,
  "starter_messages": null,
  "tools": [
    {
      "id": 222,
      "name": "GetCourseScheduleSchema",
      "description": "Return course schedule tables, views, columns, and query examples. Call this before writing schedule SQL.",
      "definition": null,
      "display_name": "GetCourseScheduleSchema",
      "in_code_tool_id": null,
      "custom_headers": [],
      "passthrough_auth": false,
      "mcp_server_id": 18,
      "user_id": null,
      "oauth_config_id": null,
      "oauth_config_name": null,
      "enabled": true,
      "chat_selectable": true,
      "agent_creation_selectable": true,
      "default_enabled": false
    },
    {
      "id": 223,
      "name": "GetCapesSchema",
      "description": "Return CAPES evaluation tables, views, columns, and query examples. Use this before writing CAPES SQL.",
      "definition": null,
      "display_name": "GetCapesSchema",
      "in_code_tool_id": null,
      "custom_headers": [],
      "passthrough_auth": false,
      "mcp_server_id": 18,
      "user_id": null,
      "oauth_config_id": null,
      "oauth_config_name": null,
      "enabled": true,
      "chat_selectable": true,
      "agent_creation_selectable": true,
      "default_enabled": false
    },
    {
      "id": 224,
      "name": "QueryCourseSchedule",
      "description": "Run read-only SQL against the course schedule cache to find exact courses, candidate sections, meetings, seats, instructors, and section_ref values.",
      "definition": null,
      "display_name": "QueryCourseSchedule",
      "in_code_tool_id": null,
      "custom_headers": [],
      "passthrough_auth": false,
      "mcp_server_id": 18,
      "user_id": null,
      "oauth_config_id": null,
      "oauth_config_name": null,
      "enabled": true,
      "chat_selectable": true,
      "agent_creation_selectable": true,
      "default_enabled": false
    },
    {
      "id": 225,
      "name": "QueryCapes",
      "description": "Run read-only SQL against CAPES evaluation data for historical instructor and course metrics. Join back to schedule data by subject_code and course_code.",
      "definition": null,
      "display_name": "QueryCapes",
      "in_code_tool_id": null,
      "custom_headers": [],
      "passthrough_auth": false,
      "mcp_server_id": 18,
      "user_id": null,
      "oauth_config_id": null,
      "oauth_config_name": null,
      "enabled": true,
      "chat_selectable": true,
      "agent_creation_selectable": true,
      "default_enabled": false
    },
    {
      "id": 226,
      "name": "PlanCourseSchedule",
      "description": "Build and validate a complete schedule from exact courses and structured constraints. The response includes validation, top-level `warnings`, and, when any schedule can be selected, `short_url`. A selectable best-effort schedule with conflicts or unmet constraints is returned normally with `valid: false`; inspect `reason_summary`, `warnings`, and `validation` rather than treating it as a tool error. TBA or asynchronous meetings may remain selectable with a warning because their conflicts cannot be fully checked. Present `![Schedule preview](<short_url>?type=png)` to render a schedule image, state `view full schedule at: <short_url>` for the web view, and provide `[Download calendar file](<short_url>?type=ics)` only when the student directly asks for ICS. `schedule_ref` and `web_view_url` are canonical server details.",
      "definition": null,
      "display_name": "PlanCourseSchedule",
      "in_code_tool_id": null,
      "custom_headers": [],
      "passthrough_auth": false,
      "mcp_server_id": 18,
      "user_id": null,
      "oauth_config_id": null,
      "oauth_config_name": null,
      "enabled": true,
      "chat_selectable": true,
      "agent_creation_selectable": true,
      "default_enabled": false
    },
    {
      "id": 227,
      "name": "GetExistingSchedule",
      "description": "Retrieve and revalidate an existing schedule from a generated short URL, short ID, or schedule_ref. Use this before modifying a schedule that is not fully available in the current conversation. The response includes the term, selected sections, meetings, instructors, current validation, and warnings.",
      "definition": null,
      "display_name": "GetExistingSchedule",
      "in_code_tool_id": null,
      "custom_headers": [],
      "passthrough_auth": false,
      "mcp_server_id": 18,
      "user_id": null,
      "oauth_config_id": null,
      "oauth_config_name": null,
      "enabled": true,
      "chat_selectable": true,
      "agent_creation_selectable": true,
      "default_enabled": false
    },
    {
      "id": 230,
      "name": "ViewCourseSchedule",
      "description": "Render the interactive weekly schedule page from an unprefixed stateless schedule_ref. The schedule is decoded, hydrated from the live course cache, and rendered with an ICS action.",
      "definition": null,
      "display_name": "ViewCourseSchedule",
      "in_code_tool_id": null,
      "custom_headers": [],
      "passthrough_auth": false,
      "mcp_server_id": 18,
      "user_id": null,
      "oauth_config_id": null,
      "oauth_config_name": null,
      "enabled": true,
      "chat_selectable": true,
      "agent_creation_selectable": true,
      "default_enabled": false
    }
  ],
  "labels": [],
  "owner": {
    "id": "995c3280-29fb-4647-8ec0-3dfc504309f9",
    "email": "maholland@ucsd.edu"
  },
  "users": [],
  "groups": [],
  "document_sets": [],
  "llm_model_provider_override": null,
  "llm_model_version_override": null,
  "hierarchy_nodes": [],
  "attached_documents": [],
  "system_prompt": "...",
  "replace_base_system_prompt": false,
  "task_prompt": "",
  "datetime_aware": false,
  "search_start_date": null
}