For Developers

Endpoints

The Career Map Routes, Under /api/career-Map/

The Data Files the Build Reads

1. data/sessions.json: the manifest from the Drive tree (npm run scan): levels, modules, sessions with code, title, url, placement (week, day, slot), aliases, assets by audience. Committed.
2. data/handins.json: per BootCamp session, from the live Word kits (npm run kits: the Take-Home Card for the pieces, the Evaluation Sheet for the criteria; the kit scripts as fallback): outputTitle, output (lead, items), submission, criteria, grading, gradingNote, source, updated, fromDocx.
3. data/worksheets.json: per BootCamp session, from the live Word kits (npm run kits; the kit scripts as fallback): the sheets as blocks (heading, sub, label, note, p with parts, blank, ticks, form, grid, list, tree), each field { id, kind: text | output | blank | check | select, label, options }; served as /worksheets/<session id>.json. The receipt (FS5's Director Log) is added to every definition by lib/worksheets.js addReceipt(). Answers are { field id: text | true } on the worksheets table; a hand-in is a submissions row with worksheet_id; the evaluator's marks are submissions.evaluation_json, { sheets: [{ id, title, met, note }], lines: [{ text, met }] }.
4. data/plan-hours.json: per BootCamp session, from the kit scripts (npm run plan): the workbook block's taught, sglLab, overtime and total hours. The build turns the placements and these hours into /plan-base.json, the base calendar: every session's pieces (taught, and self-guided from SGL/Lab and Overtime) in the three blocks of a day. The trainers' plan (/studio/plan/) is one plans row laid over it; a cohort calendar (/calendar/) lays the result onto dates from a cohorts row. Never written back to the kits, the Eight-Week Matrix or the Google calendar.
5. data/levels.json: the three levels, hand-written. data/career-map.json: the Career Map's words, stages, sittings per level, tracks, blocks, the CV questionnaire.
6. data/drive-ids.json: the Drive file id for every file in the manifest, so session pages link to Drive.
7. hub.config.json: the site name, addresses, the publish policy, the AI cards, the Safety and Welfare people and numbers, the kit script folders.

The Database

Generated from 14 migrations: 001-hub.sql, 002-roles.sql, 003-my-hub.sql, 004-handins.sql, 005-career-map.sql, 006-verified.sql, 007-guided.sql, 008-worksheets.sql, 009-plans.sql, 010-evaluation.sql, 011-track.sql, 012-cohorts.sql, 013-google-calendar.sql, 014-walks.sql. A fresh database takes the whole file; an existing one takes the migrations it has not had, in order.

-- The hub's D1 schema, GENERATED by tools/schema.js from functions/migrations/ (001-hub.sql, 002-roles.sql, 003-my-hub.sql, 004-handins.sql, 005-career-map.sql, 006-verified.sql, 007-guided.sql, 008-worksheets.sql, 009-plans.sql, 010-evaluation.sql, 011-track.sql, 012-cohorts.sql, 013-google-calendar.sql, 014-walks.sql).
-- Do not edit: change a migration, or add the next numbered one, then run `npm run schema`.
-- Paste this whole file into a fresh database (the D1 console takes one statement per Execute, or
--   wrangler d1 execute learninglions-hub --remote --file functions/schema.sql).
-- An existing database gets only the migrations it has not had yet, in order. Column comments and
-- the reasons are in the migration files.

CREATE TABLE IF NOT EXISTS submissions (
  id       INTEGER PRIMARY KEY AUTOINCREMENT,
  created  TEXT NOT NULL,
  name     TEXT NOT NULL,
  lion_id  TEXT,                -- the learner's email when signed in, else what they typed
  level    TEXT,
  session  TEXT NOT NULL,       -- a session id from data/sessions.json
  link     TEXT,
  note     TEXT
, assigned_to TEXT, assigned_at TEXT, status TEXT NOT NULL DEFAULT 'new', evaluated_by TEXT, evaluated_at TEXT, feedback TEXT, items TEXT, verified INTEGER NOT NULL DEFAULT 0, stars INTEGER, worksheet_id INTEGER, evaluation_json TEXT);
CREATE INDEX IF NOT EXISTS submissions_session ON submissions (session, created);
CREATE INDEX IF NOT EXISTS submissions_assigned ON submissions (assigned_to, status);

CREATE TABLE IF NOT EXISTS users (
  email      TEXT PRIMARY KEY,
  name       TEXT,
  picture    TEXT,
  first_seen TEXT NOT NULL,
  last_seen  TEXT NOT NULL,
  logins     INTEGER NOT NULL DEFAULT 0,
  views      INTEGER NOT NULL DEFAULT 0
, level TEXT, cohort TEXT, start_date TEXT, track TEXT);

CREATE TABLE IF NOT EXISTS logins (
  id     INTEGER PRIMARY KEY AUTOINCREMENT,
  email  TEXT NOT NULL,
  at     TEXT NOT NULL,
  agent  TEXT
);
CREATE INDEX IF NOT EXISTS logins_email ON logins (email, at);

CREATE TABLE IF NOT EXISTS roles (
  email TEXT NOT NULL,
  role  TEXT NOT NULL,
  given_by TEXT,
  given_at TEXT,
  PRIMARY KEY (email, role)
);

CREATE TABLE IF NOT EXISTS cm_map (
  email             TEXT PRIMARY KEY,                  -- level and cohort live on users, shared with My Hub
  who_json          TEXT NOT NULL DEFAULT '{}',        -- traits self-check, strength, watch-out, EI goals, the sentence
  plan_json         TEXT NOT NULL DEFAULT '{}',        -- strongest track, direction, three steps with cost and date, backup
  plan_history_json TEXT NOT NULL DEFAULT '[]',        -- earlier plans, newest first
  audit_json        TEXT NOT NULL DEFAULT '{}',        -- what a search shows, the three fixes
  intro_json        TEXT NOT NULL DEFAULT '{}',        -- the two-line introduction request
  reminders_on      INTEGER NOT NULL DEFAULT 1,
  updated_at        TEXT
);

CREATE TABLE IF NOT EXISTS cm_evidence (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  email      TEXT NOT NULL,
  on_date    TEXT NOT NULL DEFAULT '',
  what       TEXT NOT NULL,
  link       TEXT NOT NULL DEFAULT '',
  block      TEXT NOT NULL DEFAULT '',                 -- the session or place it came from (DO3, X1.7, a client, a job)
  created_at TEXT
);
CREATE INDEX IF NOT EXISTS cm_evidence_email ON cm_evidence (email, on_date);

CREATE TABLE IF NOT EXISTS cm_entries (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  email       TEXT NOT NULL,
  level       TEXT NOT NULL DEFAULT '',
  track       TEXT NOT NULL,
  made        TEXT NOT NULL DEFAULT '',
  easy_hard   TEXT NOT NULL DEFAULT '',
  pull        TEXT NOT NULL DEFAULT '',
  direction   TEXT NOT NULL DEFAULT '',                -- Pursue | Stay curious | Not my path
  action      TEXT NOT NULL DEFAULT '',
  action_date TEXT NOT NULL DEFAULT '',
  created_at  TEXT,
  updated_at  TEXT
);
CREATE INDEX IF NOT EXISTS cm_entries_email ON cm_entries (email, id);

CREATE TABLE IF NOT EXISTS cm_actions (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  email      TEXT NOT NULL,
  text       TEXT NOT NULL,
  due        TEXT NOT NULL DEFAULT '',
  earning    INTEGER NOT NULL DEFAULT 0,
  done_at    TEXT,
  created_at TEXT
);
CREATE INDEX IF NOT EXISTS cm_actions_email ON cm_actions (email, done_at, due);

CREATE TABLE IF NOT EXISTS cm_leads (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  email      TEXT NOT NULL,
  col        TEXT NOT NULL DEFAULT 'local',            -- local | national | online | ai | any
  lead       TEXT NOT NULL,
  offer      TEXT NOT NULL DEFAULT '',
  route      TEXT NOT NULL DEFAULT '',
  fit        TEXT NOT NULL DEFAULT '',                 -- the honest fit note: what it pays, what it requires
  link       TEXT NOT NULL DEFAULT '',
  created_at TEXT,
  updated_at TEXT
);
CREATE INDEX IF NOT EXISTS cm_leads_email ON cm_leads (email, col, id);

CREATE TABLE IF NOT EXISTS cm_outreach (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  email      TEXT NOT NULL,
  lead_id    INTEGER,
  kind       TEXT NOT NULL DEFAULT 'intro',            -- intro | application | follow-up
  to_name    TEXT NOT NULL DEFAULT '',                 -- first name only
  channel    TEXT NOT NULL DEFAULT '',
  message    TEXT NOT NULL DEFAULT '',
  sent_on    TEXT NOT NULL DEFAULT '',
  reply      TEXT NOT NULL DEFAULT 'none',             -- none | yes | no | silence
  reply_note TEXT NOT NULL DEFAULT '',
  next_on    TEXT NOT NULL DEFAULT '',
  created_at TEXT
);
CREATE INDEX IF NOT EXISTS cm_outreach_email ON cm_outreach (email, sent_on);

CREATE TABLE IF NOT EXISTS cm_checkins (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  email       TEXT NOT NULL,
  on_date     TEXT NOT NULL,
  status      TEXT NOT NULL,                           -- job-ft | job-pt | business | freelance | study | looking | other
  employer    TEXT NOT NULL DEFAULT '',
  role        TEXT NOT NULL DEFAULT '',
  town        TEXT NOT NULL DEFAULT '',
  hours       TEXT NOT NULL DEFAULT '',
  gigs_3m     INTEGER NOT NULL DEFAULT 0,
  platforms   TEXT NOT NULL DEFAULT '',
  income_band TEXT NOT NULL DEFAULT '',                -- a band, never a figure; optional
  how_found   TEXT NOT NULL DEFAULT '',
  note        TEXT NOT NULL DEFAULT '',
  consent     INTEGER NOT NULL DEFAULT 0,              -- Learning Lions may use this, without the name, in its reports
  created_at  TEXT
);
CREATE INDEX IF NOT EXISTS cm_checkins_email ON cm_checkins (email, on_date);

CREATE TABLE IF NOT EXISTS cm_profiles (
  email          TEXT PRIMARY KEY,
  cv_text        TEXT,
  cv_filename    TEXT,
  cv_uploaded_at TEXT,
  answers_json   TEXT NOT NULL DEFAULT '{}',
  prefs_json     TEXT NOT NULL DEFAULT '{}',
  analysis_json  TEXT,
  improved_cv    TEXT,
  previous_cv    TEXT,                                 -- for Undo
  game_plan      TEXT,
  last_change    TEXT,
  cv_version     INTEGER NOT NULL DEFAULT 1,
  updated_at     TEXT
);

CREATE TABLE IF NOT EXISTS cm_versions (
  id                INTEGER PRIMARY KEY AUTOINCREMENT,
  email             TEXT NOT NULL,
  name              TEXT NOT NULL,
  brief             TEXT NOT NULL DEFAULT '',
  posting           TEXT NOT NULL DEFAULT '',
  markdown          TEXT,
  previous_markdown TEXT,
  last_change       TEXT,
  version           INTEGER NOT NULL DEFAULT 1,
  created_at        TEXT,
  updated_at        TEXT
);
CREATE INDEX IF NOT EXISTS cm_versions_email ON cm_versions (email, id);

CREATE TABLE IF NOT EXISTS worksheets (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  email         TEXT NOT NULL,                     -- the signed-in learner
  session       TEXT NOT NULL,                     -- a session id from data/sessions.json
  version       TEXT,                              -- the kit version the answers were typed against
  answers_json  TEXT NOT NULL DEFAULT '{}',        -- { field id: text | true }
  status        TEXT NOT NULL DEFAULT 'draft',     -- draft | submitted
  submission_id INTEGER,                           -- the submissions row of the latest hand-in
  created_at    TEXT NOT NULL,
  updated_at    TEXT NOT NULL,
  submitted_at  TEXT
);
CREATE UNIQUE INDEX worksheets_owner ON worksheets (email, session);

CREATE TABLE IF NOT EXISTS plans (
  team          TEXT PRIMARY KEY,         -- 'bootcamp': the BootCamp trainer team
  plan_json     TEXT NOT NULL,            -- { v, capacity: { am, pm }, pools: { "week-day-slot": [session ids] }, tray: [ids], hours: { id: hours } }
  previous_json TEXT,                     -- the plan before the last save, for one undo
  updated_at    TEXT NOT NULL,
  updated_by    TEXT NOT NULL             -- the email of whoever saved last
);

CREATE TABLE IF NOT EXISTS cohorts (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  name          TEXT NOT NULL,                      -- "Cohort 1, Lodwar"
  level         TEXT NOT NULL DEFAULT 'bootcamp',
  start_date    TEXT NOT NULL,                      -- the Monday the cohort starts, YYYY-MM-DD
  holidays_json TEXT NOT NULL DEFAULT '[]',         -- [{ "date": "2026-10-20", "name": "Mashujaa Day" }]
  active        INTEGER NOT NULL DEFAULT 0,         -- 1 on the one cohort /calendar/ shows by default
  created_at    TEXT NOT NULL,
  created_by    TEXT NOT NULL,
  updated_at    TEXT NOT NULL
, gcal_id TEXT, gcal_name TEXT, gcal_synced_at TEXT);

CREATE TABLE IF NOT EXISTS walks (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  email       TEXT NOT NULL,                      -- the signed-in person
  session     TEXT NOT NULL,                      -- a session id from data/sessions.json
  kind        TEXT NOT NULL,                      -- take | teach
  slide       INTEGER NOT NULL DEFAULT 1,         -- the stage reached, 1-based (the slides, then the sheets and the hand-in)
  count       INTEGER,                            -- how many stages the walk had when last saved
  ticks_json  TEXT NOT NULL DEFAULT '[]',         -- take: the step ids ticked done ("<sheet id>:<row>"); teach: the slide numbers practised
  notes_json  TEXT NOT NULL DEFAULT '{}',         -- teach: { slide number: the trainer's note to self }
  status      TEXT NOT NULL DEFAULT 'started',    -- started | done
  created_at  TEXT NOT NULL,
  updated_at  TEXT NOT NULL
);
CREATE UNIQUE INDEX walks_owner ON walks (email, session, kind);

Running It Locally

1. git clone, then npm run build (the prompt linter, the schema check, the build, the output check) and npm run serve on port 8787. The local server stands in for sign-in and the database with one preview admin and a few made-up rows, so every page can be looked at.
2. The output check refuses a Cloudflare preview address, the kits' all-capitals marker for an unsettled detail, filler Latin text, and a clock time on a learner page; the banned list is bannedInOutput in hub.config.json.
3. The repo is github.com/Learning-Lions-Arizona/learninglions-hub; the README has the Cloudflare setup, the variables and the house rules.