🔌 API Reference

Overview

The Express server runs on http://localhost:3000 and provides REST endpoints for curriculum calendar data.

# Start the server
cd E:\Lucas\Personal-Productivity
npm start

Base URL: http://localhost:3000/api

GET /api/health

Check server and database status.

Request

GET /api/health

Response

{
  "status": "ok",
  "database": "connected",
  "timestamp": "2025-11-28T10:30:00.000Z"
}

Error Response

{
  "status": "error",
  "database": "disconnected",
  "error": "Connection refused"
}

GET /api/weeks

Get all 24 school weeks.

Request

GET /api/weeks

Response

[
  {
    "id": 1,
    "week_number": 1,
    "start_date": "2025-08-18",
    "end_date": "2025-08-22",
    "theme": "Back to School",
    "goals": "Establish routines, review basics"
  },
  {
    "id": 2,
    "week_number": 2,
    "start_date": "2025-08-25",
    "end_date": "2025-08-29",
    "theme": "Building Foundations",
    "goals": "..."
  }
  // ... 24 weeks total
]

GET /api/weeks/:weekNumber/assignments

Get all assignments for a specific week.

Request

GET /api/weeks/1/assignments

Response

[
  {
    "id": 1,
    "week_id": 1,
    "subject_id": 1,
    "day_of_week": 1,
    "title": "Place Value Review",
    "description": "Review place value concepts...",
    "duration_minutes": 30,
    "assignment_type": "lesson",
    "sequence_order": 1,
    "resource_id": 1,
    "subject": {
      "id": 1,
      "name": "Math",
      "color": "#3b82f6",
      "emoji": "🔢"
    },
    "resource": {
      "id": 1,
      "name": "Saxon Math 5/4",
      "type": "textbook"
    }
  },
  // ... more assignments
]

Query Parameters

GET /api/weeks/1/assignments?day=1
GET /api/weeks/1/assignments?subject=1

GET /api/subjects

Get all subjects with colors and emojis.

Request

GET /api/subjects

Response

[
  { "id": 1, "name": "Math", "color": "#3b82f6", "emoji": "🔢" },
  { "id": 2, "name": "Writing", "color": "#eab308", "emoji": "✍️" },
  { "id": 3, "name": "Reading", "color": "#22c55e", "emoji": "📖" },
  { "id": 4, "name": "Science", "color": "#a855f7", "emoji": "🔬" },
  { "id": 5, "name": "Spelling", "color": "#f97316", "emoji": "📝" },
  { "id": 6, "name": "History", "color": "#ec4899", "emoji": "🏛️" },
  { "id": 7, "name": "Art", "color": "#14b8a6", "emoji": "🎨" },
  { "id": 8, "name": "Music", "color": "#6366f1", "emoji": "🎵" },
  { "id": 9, "name": "PE", "color": "#64748b", "emoji": "⚽" }
]

GET /api/assignments/today

Get today's assignments based on current date.

Request

GET /api/assignments/today

Response

Same format as week assignments, filtered to current day.

Logic

// Calculates current week and day based on:
// - School year start date
// - Current date
// - Day of week (1=Mon, 5=Fri)

Curriculum Audit & Repair

GET /api/curriculum/audit

Scans all active (non-graded, non-cancelled) assignments for a student and flags alignment issues.

Query parameters:

• student_id - UUID (defaults to Lucas)

Response:

{
  "total_issues": 23,
  "total_assignments_scanned": 588,
  "issues": {
    "weekend_dates": [{ "student_assignment_id", "title", "current_due_date", "suggested_due_date", ... }],
    "out_of_range": [{ "student_assignment_id", "title", "due_date", "assigned_week", "actual_week_number", ... }],
    "null_due_dates": [{ "student_assignment_id", "title", "week_number", ... }],
    "sequence_breaks": [{ "resource", "earlier_title", "later_title", ... }]
  },
  "school_weeks": [{ "id", "week_number", "start_date", "end_date" }]
}

POST /api/curriculum/repair

Applies date fixes to misaligned assignments. Updates due_date, week_id, and day_of_week in a single transaction.

Request body:

{
  "fixes": [
    { "student_assignment_id": "uuid", "new_due_date": "2026-02-11" }
  ]
}

Validation: Rejects weekend dates and dates outside school weeks. Rolls back all changes if entire batch fails.

Response:

{
  "message": "Successfully repaired 5 assignments",
  "repaired": 5,
  "failed": 0,
  "results": [{ "student_assignment_id", "new_due_date", "new_week_number", "new_day_of_week" }]
}

Vocabulary Review

GET /api/vocab-review/:assignmentId

Returns vocabulary terms for a review assignment. Resolves the unit key from the assignment's title/resource and fetches from vocab_sets table.

Query parameters:

• student_id - UUID (optional, includes student assignment status)

Response:

{
  "assignment": { "id", "title", "subject", "resource" },
  "unit_key": "ckhg-unit-1",
  "terms": [
    { "id", "term": "river", "definition": "A large natural stream...", "example_sentence": "The Nile is...", "display_order": 1 }
  ],
  "student_assignment": { "id", "status", "due_date" }
}

POST /api/vocab-review/:assignmentId/complete

Auto-grades the vocabulary review and creates a submission. Calculates grade from fill-in-the-blank score.

Request body:

{
  "student_id": "uuid",
  "student_assignment_id": "uuid",
  "score": 18,
  "total": 18,
  "attempts": 1,
  "results": [{ "term": "river", "answer": "river", "correct": true }]
}

Response:

{
  "message": "Vocabulary review completed and graded",
  "grade": "A",
  "score": 18,
  "total": 18,
  "feedback": "Vocabulary Review completed. 18/18 correct (100%). Perfect on first try!"
}

Program Links (D1 Shooter)

Resource library backing dashboards/d1-shooter.html. Rows live in the program_links table and are owned by a student, so the same endpoints serve any student id.

Table: program_links

ensureProgramLinksTable() runs at server start. It creates the table and, only when no rows exist for Lucas, seeds the 13 D1 Shooter Blueprint Month 1 links.

GET /api/students/:studentId/program-links

Returns every link for the student, ordered by category then label.

{
  "links": [
    { "id", "student_id", "category": "Drill Video", "label": "Kneeling Demo",
      "url": "https://...", "local_path": null, "phase_tag": "Month 1",
      "notes": null, "created_at", "updated_at" }
  ],
  "categories": ["Plan", "Breakdown", "Drill Video", "Tracker", "Course", "Call", "Other"]
}

POST /api/students/:studentId/program-links

Creates a link. Returns 201 with the created row.

Request body:

{
  "category": "Tracker",
  "label": "Free Throw Tracker",
  "url": "https://drive.google.com/file/d/.../view",
  "local_path": null,
  "phase_tag": "Month 1",
  "notes": null
}

Validation (400 on failure): label is required, category must be one of the seven values, and at least one of url or local_path must be present.

PUT /api/program-links/:id

Partial update. Fields omitted from the body keep their stored value; passing an explicit null clears a nullable field. Returns 404 if the id does not exist, 400 if no updatable field was supplied.

{ "label": "Free Throw Tracker v2" }

DELETE /api/program-links/:id

Deletes the link. Returns { "success": true, "id": "..." }, or 404 if the id does not exist.

D1 Shooter Program Drills (Workout Card)

The prescription behind the Today card on dashboards/d1-shooter.html. Two tables: program_plans holds one record per student per month (plan name and coach notes), and program_drills holds one row per drill.

Table: program_drills

ensureProgramDrillTables() creates both tables at startup, seeds Month 1 for Lucas when nothing is stored, then runs backfillD1Month1Demos(), which fills demo links onto rows that predate the URLs being transcribed. The backfill only writes where the field is still null, so it never overwrites a link edited on the card.

Embedding

Nothing about embedding is stored. parseYouTube() in the page reduces a stored link to a kind and an id, and youTubeEmbedUrl() rebuilds it against the privacy enhanced domain:

video:    https://www.youtube-nocookie.com/embed/<id>?rel=0&controls=1&modestbranding=1&playsinline=1&autoplay=1
playlist: https://www.youtube-nocookie.com/embed/videoseries?list=<id>&rel=0&...

The library reuses these same builders through linkEmbed(url), which tries YouTube first, then a Google Drive file id, then falls back to no embed. Nothing about embedding is duplicated between the Today card and the link library.

Recognised inputs: /watch?v=, youtu.be/, /shorts/, /embed/, /live/, /v/, /playlist?list=, and a bare id. A watch link that also carries a list plays the single chosen video rather than the playlist.

GET /api/students/:studentId/program-drills?month=Month%201

Returns the plan record, the drills ordered by sort_order, every month stored for the student, and the allowed log_field values. Defaults to Month 1 when no month is given.

{
  "plan": { "plan_month": "Month 1", "plan_name": "...", "coach_notes": "..." },
  "drills": [ { "id", "drill_group", "sort_order", "name", "target", "cue",
                "demo_url", "breakdowns": [{ "label", "url" }], "log_field", "is_active" } ],
  "months": ["Month 1"],
  "log_fields": ["ball_raises_reps", ...]
}

PUT /api/program-drills/:id

Partial update. Fields omitted from the body keep their stored value. Returns 400 on an unknown drill_group or log_field, an empty name, or a breakdowns value that is not an array; 404 if the id does not exist.

PUT /api/students/:studentId/program-drills/reorder

Body { "order": [id, id, ...] }. Rewrites sort_order to the position in that array, inside a transaction, scoped to the student so one student's list cannot renumber another's.

PUT /api/students/:studentId/program-plans/:month

Upserts the plan name and coach notes for a month.

D1 Shooter Settings

A small key and value store for the program level settings the print sheets need, in d1_settings, keyed on (student_id, setting_key) so another child can run the program with different targets.

GET /api/students/:studentId/d1-settings

Returns stored values layered over the defaults, so a caller never has to handle a missing key.

{ "settings": { "program_start": "2026-08-17", "ft_target": "100", "smc_target": "700" },
  "defaults": { ... } }

PUT /api/students/:studentId/d1-settings

Upserts one or more settings in a transaction and returns the full merged set. Unknown keys are rejected with 400 rather than silently stored, and program_start must be formatted YYYY-MM-DD.

D1 Shooter Coach Reminders

Coaching feedback that arrives between calls, shown in the Current Reminders block of the Today card. One row per reminder in coach_reminders.

ensureCoachRemindersTable() runs at startup, after ensureProgramDrillTables() so its seed can resolve drill associations by name rather than carrying ids.

GET /api/students/:studentId/coach-reminders

Active reminders ordered by sort_order. Pass ?include_inactive=true for the full history including retired rows.

{
  "reminders": [ { "id", "drill_id", "audience": "Player", "reminder_text": "...",
                   "source_date": "2026-08-20", "is_active": true,
                   "deactivated_at": null, "sort_order": 1 } ],
  "audiences": ["Player", "Parent"]
}

The page fetches this in parallel with the drills, so the Today card renders once with both.

POST /api/students/:studentId/coach-reminders

Creates a reminder at the end of the list. reminder_text is required, audience defaults to Player, and source_date must be formatted YYYY-MM-DD when present. Returns 201.

PUT /api/coach-reminders/:id

Partial update. Fields omitted keep their stored value. Passing is_active also maintains deactivated_at, stamping it on retirement and clearing it on reactivation.

PUT /api/coach-reminders/:id/deactivate

Retires a reminder and stamps deactivated_at. 404 if the id does not exist.

PUT /api/students/:studentId/coach-reminders/reorder

Body { "order": [id, id, ...] }. Rewrites sort_order in a transaction, scoped to the student.

D1 Shooter Daily Log

Training log backing the Daily Log section of dashboards/d1-shooter.html. One row per student per date, in the d1_daily_log table.

Table: d1_daily_log, unique on (student_id, log_date)

Every numeric column is nullable. A null means the drill was not recorded, which is distinct from a logged zero: nulls stay out of week totals and are never eligible for a personal best.

Total makes

total_makes is computed on every read by d1ComputeTotalMakes() in server.js and is never stored. The same rule is mirrored client side in computeTotalMakes() so the entry form can show a live figure before saving. Changing the rule means changing both.

// Lateral Hop Mikan depends on the date. MIKAN_SCORE_START is 2026-08-20.
if (workout_done) {
  total += usesMikanScore(log_date) ? mikan_makes : 20;   // Lateral Hop Mikan
  total += 25 + 25 + 25;                                  // Hop Touch, Five Spot, Forward Hop
}
total += around_horn_makes
       + x_out_makes
       + round(twos_points / 2)                // two points per make
       + ft_student_makes;                     // kneeling_makes deliberately excluded

GET /api/students/:studentId/d1-daily-log

Returns entries ordered by date, each carrying a computed total_makes. Optional start and end query parameters narrow to a date range and must be formatted YYYY-MM-DD, otherwise 400. With no range every entry is returned, which is what the page requests so it can derive personal bests, the running Shot Make Club total, and the season record from the full history.

{ "entries": [ { "id", "student_id", "log_date": "2026-08-17", ..., "total_makes": 140 } ] }

log_date is cast to text with TO_CHAR rather than returned as a DATE, so a day never shifts during JSON serialization across a timezone boundary.

POST /api/students/:studentId/d1-daily-log

Upserts one date on the (student_id, log_date) unique constraint, so saving a day that already exists overwrites it rather than inserting a duplicate. log_date is required and must be formatted YYYY-MM-DD. Blank numeric fields are stored as null. Returns the saved row with total_makes.

DELETE /api/students/:studentId/d1-daily-log/:date

Deletes that date for that student. Returns { "success": true, "id": "..." }, 404 if nothing was logged for the date, or 400 if the date is malformed.

Error Handling

All endpoints return consistent error responses:

{
  "error": "Error message here",
  "status": 404,
  "timestamp": "2025-11-28T10:30:00.000Z"
}

Status Codes

Frontend Usage

// Fetch weeks
const response = await fetch('http://localhost:3000/api/weeks');
const weeks = await response.json();

// Fetch assignments for week 1
const response = await fetch('http://localhost:3000/api/weeks/1/assignments');
const assignments = await response.json();

// Fetch with day filter
const monday = await fetch('http://localhost:3000/api/weeks/1/assignments?day=1');
const mondayWork = await monday.json();

Database Connection

// .env.local configuration
DATABASE_URL=postgresql://postgres:password@localhost:5432/andrews_family_lms

// Or individual settings
DB_HOST=localhost
DB_PORT=5432
DB_NAME=andrews_family_lms
DB_USER=postgres
DB_PASSWORD=your_password