Index of every page on this site: llms.txt
All skills
Run it yourself

Stand up one student's capstone build through the Student Build Pipeline end to end — intake interview, decompose, gate, repair, scope agents, persist the draft, PUBLISH, materialize tasks, render docs, commit to their repo — and then prove with queries that the student can actually see it. Invoke when Ali says "set up <student>'s build", "their Projects page is empty", "my tasks have no dates", "the Command Center is missing", when a cohort is about to start builds, or before any class where students are expected to open a plan. Also invoke to audit a whole cohort for builds that generated but never published. Code lives in backend/src/services/sbp/.

  • 4 files
  • 158.6 KB
  • Updated 3 days ago
  • GitHub
Use this Skill: https://skilld.dev/gh/colaberryintern/colaberryenterprise_ai_leadershipaccelerator/build-student-project

Nothing lands on disk. Nothing to clean up.

Fork this Skill

Edit a local copy. It keeps the author and licence.

referencesverification-queries.md

≈3.9k tokens on demand. Your agent reads this file only when SKILL.md points to it.

Verification queries — the evidence, not the vibe

Every command here answers a question with a fact. Run them against the real database, not against the poll response — the poll returns the latest plan, not the published one (getPlan() with no version is ORDER BY version DESC LIMIT 1), so it will happily report a draft on a project that also has a published v1.

Getting a shell

ssh root@95.216.199.47
docker exec accelerator-db psql -U accelerator -d accelerator_prod -c "SELECT 1"

The backend container is accelerator-backend; its port 3001 is not published on the host, so health checks go through docker exec and the image has no wget — use node -e "fetch(...)". The DB container is accelerator-db, user accelerator, database accelerator_prod (defaults from docker-compose.production.yml:4-12; read the real values out of /opt/colaberry-accelerator/.env if they were overridden).

Read-only queries are safe at any hour. Anything that writes waits for after hours.

If you reach for a read-only guard, use the right one. SET TRANSACTION READ ONLY is a no-op through node-postgres: the driver sends each query as its own implicit transaction, so it guards the statement carrying it and nothing after, while looking perfectly correct in the source. Proven on 2026-08-13 by attempting a write after issuing it (allowed), then again after switching to the session form (rejected):

SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;

psql -c is a single session per invocation, so for one-shot queries the guard buys little; it matters for any script holding a connection.

Prefer the script over hand-written SQL for a cohort. PR #1461 adds backend/src/scripts/auditStudentBuilds.js — one row per active enrollment, a READY / NOT_READY verdict naming the first stage that did not complete, --cohort / --only / --with-project / --json. Its readiness definition is deliberately the same as Q1's, including undated_tasks = 0.

docker exec accelerator-backend node /app/dist/scripts/auditStudentBuilds.js --cohort <id>

Q1 — THE query. Is this student genuinely ready?

One row, one boolean. ready = true means: the plan is published, STORY-000 exists, there is at least one task, every task is dated, and the project is the one the enrollment points at.

SELECT
  p.id                                             AS project_id,
  e.id                                             AS enrollment_id,
  bp.version                                            AS plan_version,
  COALESCE(bp.status = 'published', false)              AS plan_published,
  COUNT(st.id)                                          AS tasks,
  COALESCE(BOOL_OR(st.story_id = 'STORY-000'), false)   AS has_story_000,
  COUNT(st.id) FILTER (WHERE st.due_on IS NULL)         AS undated_tasks,
  COALESCE(e.active_project_id = p.id, false)           AS is_active_project,
  (
        COALESCE(bp.status = 'published', false)
    AND COALESCE(BOOL_OR(st.story_id = 'STORY-000'), false)
    AND COUNT(st.id) > 0
    AND COUNT(st.id) FILTER (WHERE st.due_on IS NULL) = 0
    AND COALESCE(e.active_project_id = p.id, false)
  )                                                     AS ready
FROM projects p
JOIN enrollments e   ON e.id = p.enrollment_id
LEFT JOIN build_plans bp
       ON bp.project_id = p.id AND bp.status = 'published'
LEFT JOIN student_tasks st
       ON st.project_id = p.id
WHERE p.id = :project_id
GROUP BY p.id, e.id, bp.version, bp.status;

Note every join is on project_id. Never join student_tasks on story_id alone — STORY-001 exists in every plan in the database (H-6).

Q1b — the same question for a whole cohort, which is how you find the ones nobody reported

SELECT e.id                                          AS enrollment_id,
       p.id                                          AS project_id,
       COALESCE(bp.status, '(no published plan)')    AS plan_status,
       COUNT(st.id)                                        AS tasks,
       COALESCE(BOOL_OR(st.story_id = 'STORY-000'), false) AS story_000,
       COUNT(st.id) FILTER (WHERE st.due_on IS NULL)       AS undated,
       COALESCE(e.active_project_id = p.id, false)         AS active
FROM enrollments e
JOIN cohorts c            ON c.id = e.cohort_id
LEFT JOIN projects p      ON p.enrollment_id = e.id
LEFT JOIN build_plans bp  ON bp.project_id = p.id AND bp.status = 'published'
LEFT JOIN student_tasks st ON st.project_id = p.id
WHERE c.id = :cohort_id
GROUP BY e.id, p.id, bp.status
ORDER BY plan_status, tasks;

Sort puts the broken ones at the top. (no published plan) with a non-zero task count means those tasks came from somewhere other than publish — almost certainly a localStorage import.


Q2 — the draft sweep (H-1). Run this before every class, still.

Plans that were generated and never published. Each row is a student who will open the portal to nothing. PR #1462 does not retire this query. Auto-publish cannot throw, so its failures land here silently, and it only fires on new generations — the 11 students already sitting at drafted on 2026-08-13 will stay there until someone publishes them.

SELECT bp.project_id,
       bp.version,
       bp.gate_ok,
       bp.created_at,
       p.enrollment_id,
       (SELECT COUNT(*) FROM student_tasks st WHERE st.project_id = bp.project_id) AS tasks
FROM build_plans bp
JOIN projects p ON p.id = bp.project_id
WHERE bp.status = 'draft'
  AND NOT EXISTS (
        SELECT 1 FROM build_plans x
         WHERE x.project_id = bp.project_id AND x.status = 'published')
ORDER BY bp.created_at DESC;

tasks = 0 on one of these confirms it: generated, gate-clean, never materialized.

Q3 — intake status, when there is no plan row at all

SELECT project_id, enrollment_id, status, correlation_id, created_at, updated_at,
       length(idea) AS idea_chars,
       jsonb_array_length(COALESCE(answers, '[]'::jsonb)) AS answers
FROM build_intake
WHERE project_id = :project_id;

generating for more than ~10 minutes is a lost job (a restart drops the in-memory queue; the intake is deliberately replayable, so re-POST the build). failed means generation itself threw — find the reason by correlation id, Q7.

Q4 — does the cohort have a start date? (H-4)

SELECT c.id, c.name, c.start_date
FROM enrollments e JOIN cohorts c ON c.id = e.cohort_id
WHERE e.id = :enrollment_id;

start_date IS NULL ⇒ scheduleFor() returns null ⇒ every task materializes with due_on = NULL and the whole demo-prep list (PREP-1..PREP-6) is absent. Fix the cohort, then republish; materialize is idempotent and will backfill the dates.

Q5 — what actually got materialized

SELECT l.cluster, l.title, l.position,
       COUNT(t.id) AS tasks,
       MIN(t.due_on) AS first_due,
       MAX(t.due_on) AS last_due
FROM student_task_lists l
LEFT JOIN student_tasks t ON t.task_list_id = l.id
WHERE l.project_id = :project_id
GROUP BY l.id, l.cluster, l.title, l.position
ORDER BY l.position;

Healthy shape: clusters r0, r1, … rN, prep; titles Release N · <name> and Demo prep · the dedicated week. Cluster values that are UUIDs are localStorage release ids — that project has been written by the browser import path, not by publish.

Q6 — completion vs verification (H-8)

SELECT story_id, title, status, verified_at, verified_by,
       verification_json ->> 'state'  AS verify_state,
       verification_json ->> 'reason' AS verify_reason
FROM student_tasks
WHERE project_id = :project_id
  AND (status = 'complete' OR verified_at IS NOT NULL OR verification_json IS NOT NULL)
ORDER BY position;

status='complete' with verified_at IS NULL earns no points. That combination is legitimate only for rows that predate the verification columns; anything new means something wrote complete down a path that is not markTaskVerifiedComplete.

Since PR #1463 the two columns answer different questions and both are worth reading. verified_at is written once and never moves — "was it confirmed". verification_json is the live verdict, rewritten on every repo sync — "what is left", which is the question a student sitting at 3 of 4 criteria is actually asking. A row with verification_json IS NOT NULL and verified_at IS NULL is the healthy, expected state of work in progress, not a defect; verify_reason says which half is missing (criteria still outstanding, or no pushed commit naming the story). All-NULL verification_json across an entire cohort means the loop has never had a repo to read — see H-10.

Q7 — the logs, by correlation id

Every SBP log line is JSON with a correlation_id you can get from Q3 or from the POST /builds response.

docker logs accelerator-backend --since 60m 2>&1 | grep '"service":"sbp-orchestrator"'
docker logs accelerator-backend --since 60m 2>&1 | grep '<correlation-id>'

Events worth knowing by name:

Event Means
sbp_build_started intake persisted, job queued
sbp_build_repairing gate found violations, repair attempt N running
sbp_build_generated plan written as draft — on its own this is not delivery
sbp_autopublished PR #1462: the plan promoted itself; carries status, version, files, has_repo
sbp_autopublish_failed auto-publish refused. Carries error_class and a recovery hint. The build rests at drafted and nothing else will retry it
sbp_autopublish_disabled SBP_AUTO_PUBLISH=off — publish is manual again
sbp_build_failed generation threw; error_class says which boundary
sbp_build_queue_failed the job never ran
sbp_schedule_skipped no cohort start_date — tasks will be undated
sbp_schedule_built dates computed; carries buildWeeks, capacity, demoDay
sbp_agents_scope_failed / _unusable / _rejected_placeholder scoping fell back; plan keeps original owners
sbp_build_published / sbp_build_published_no_repo publish completed
sbp_active_project_set / _noop / _failed the visibility pointer
sbp_repo_write_committed / _noop / _failed the GitHub write
project_import_skipped_published the H-6 guard refusing a stale tab
task_status_client_complete_refused H-8 guard firing (expect a lot of these)
sbp_schema_incomplete DDL did not land — read the missing array

Q9 — re-grade a gate_failed label instead of believing it

build_intake.status is written at generation time and never revisited. A plan generated before the advisory/blocking split can carry gate_failed on violations that do not block — one student sat stranded three days on requirement_unfalsifiable, which is not in BLOCKING_RULES. Count the blocking rules yourself:

SELECT bp.project_id, bp.version, i.status AS labelled,
       ARRAY(SELECT DISTINCT v->>'rule'
               FROM jsonb_array_elements(bp.gate_violations) v
              WHERE v->>'rule' IN (
                'must_uncovered','dangling_requirement','dangling_release',
                'dangling_blocked_by','malformed_requirement','malformed_story',
                'r0_missing','r0_not_ungated','invented_vendor')) AS blocking_rules,
       ARRAY(SELECT DISTINCT v->>'rule'
               FROM jsonb_array_elements(bp.gate_violations) v) AS all_rules
FROM build_plans bp
JOIN build_intake i ON i.project_id = bp.project_id
WHERE i.status = 'gate_failed'
ORDER BY bp.created_at;

Empty blocking_rules ⇒ the plan is publishable and the label is stale; publish it. Keep that list in sync with BLOCKING_RULES in planGate.ts:79 — PR #1461's audit has a test that parses the constant out of the source to stop exactly this drift. Nothing sweeps for stale labels, so run this whenever a gate_failed looks wrong.

Q8 — schema post-condition (H-7)

Never trust that ensureSbpSchema ran. It swallows every statement failure.

SELECT table_name, column_name
FROM information_schema.columns
WHERE table_schema = 'public'
  AND (table_name, column_name) IN (
      ('build_intake','answers'),
      ('student_tasks','due_on'),
      ('student_tasks','due_baseline_on'),
      ('student_tasks','verified_at'),
      ('student_tasks','verified_by'),
      ('student_tasks','verification_json'))
ORDER BY 1, 2;

Six rows expected — the sixth, student_tasks.verification_json, arrived with PR #1463 and is in REQUIRED_COLUMNS. Fewer means an ALTER was skipped and the code is writing values Postgres is silently dropping; specifically, a missing verification_json means the verification loop writes a verdict Sequelize discards without a word, so every story renders "not started" while the run logs success. Also check the tables and indexes:

SELECT tablename FROM pg_tables
 WHERE schemaname='public' AND tablename IN ('build_intake','build_plans');
SELECT indexname FROM pg_indexes
 WHERE schemaname='public'
   AND indexname IN ('build_intake_unique_project','build_plans_unique_project_version');

And the boot log:

docker logs accelerator-backend 2>&1 | grep -E "sbp schema stmt skipped|sbp_schema_incomplete|SchemaInvariantViolation|SBP schema ensured"

HTTP verification

All SBP endpoints are participant-scoped: the enrollment comes from the JWT and never from the body, and a project that is not yours answers 404, not 403, so it cannot be probed. Mint a participant JWT the way this program's other deploy verifications do.

# The flag. All five endpoints answer 404 without it.
docker exec accelerator-backend printenv | grep -E 'SBP_PIPELINE_ENABLED|PROJECT_API_ENABLED|SBP_AGENT_SCOPING|SBP_PROVISION_CONCURRENCY'

# Poll a build
curl -s -H "Authorization: Bearer $JWT" \
  https://enterprise.colaberry.ai/api/portal/sbp/builds/$PROJECT_ID | jq '.status, .plan.version, .gate.ok'

# Publish it
curl -s -X POST -H "Authorization: Bearer $JWT" -H 'Content-Type: application/json' \
  -d '{}' \
  https://enterprise.colaberry.ai/api/portal/sbp/builds/$PROJECT_ID/publish | jq

# What the student's portal will actually render
curl -s -H "Authorization: Bearer $JWT" \
  https://enterprise.colaberry.ai/api/portal/projects/active \
  | jq '{id, lists: [.lists[] | {cluster, title, n: (.tasks|length)}]}'

GET /api/portal/projects/active returns 200 {"project": null} — not a 404 — when there is no active project. A null there with a published plan in the database is makeActiveProject having failed; check for sbp_active_project_failed.

Publish response decoder:

Field Healthy Meaning if not
status published, or awaiting_repo awaiting_repo is fine and expected with no GITHUB_TOKEN / no repo — tasks still materialized
planVersion the version you reviewed a different number means you published a plan nobody looked at
commitSha a sha, or null when awaiting_repo null with status: published is impossible
filesWritten ~16-19 0 with a repo means the write no-opped

Publish failure codes: 404 no plan to publish · 409 blocking gate violations (the message names them) · 409 hash mismatch when expected_sha256 was supplied and the row changed · 503 queue full.

Source: SKILL.md on GitHub

skilld matched fixed text patterns in SKILL.md and file names. Patterns miss obfuscated code.

skilld run checks every file with the same patterns. It asks for approval before it loads a Skill with a behavior marked Needs approval.

No third-party reports yet.

Signed by skilld at 7f168b8. This ties the file your Agent reads to that commit on GitHub. It does not review the instructions.

Last checked against GitHub yesterday.

Activeupdated 3 days ago

README badge

README badge for colaberryintern/colaberryenterprise_ai_leadershipaccelerator/build-student-project