Suspend & unsuspend skills

This page documents the change-request pipeline that lowers (and later restores) a member’s stored approval_level_* field. For the broader context on what those fields mean and how they’re normally derived upwards, see Approval levels.

The whole flow lives in api/src/features/account/change-request/:

• service/index.js — defines the action map
• service/update.skills.service.js — per-action handlers
• model/index.js — the SQL

What it does

A member with sufficient role (typically 8+: instructor and above) raises a row in channel_change_requests. An administrator approves it via PUT /change-request/form/:id. The action text on the request routes to a handler that:

• flips a single logbook row’s status between open and suspended, and
• writes one of the three member-level columns (approval_level_coach / _instructor / _trainer) accordingly.

There are two actions:

Despite the action title, Suspend / Unsuspend Instructor Skill covers all three programmes (coach, instructor, trainer) — the per-skill category determines which member column is written, not the action title.

The flow does not cascade. Suspending a single instructor logbook entry only flips that one row and adjusts the member’s approval level. It does not auto-suspend higher-level entries the member also holds — the level field itself is the gate that prevents them from approving above their authority.

End-to-end flow

The six steps in detail:

1. Load the member’s full skill list. getSkills returns every coach / instructor / trainer logbook row whose status is open, suspended, or not_current.
2. Locate the row being acted on. getCurrentSkill() finds the logbook row whose entry_id matches the CR’s logbook_entry_to_remove. If it can’t find a match it returns 0 — that fallback is dangerous (see Pitfall 2).
3. Pick the column pair. getDBFields() decides which members.* column to write and which channel_skills.* column to read (see Which member column gets written).
4. Compute the new level. getNewApprovalLevel() returns a number. The formula is asymmetric between suspend and unsuspend — see The asymmetric formula.
5. Run the directional UPDATE. updateApprovalLevel() issues a guarded SQL update. The operator (> or <) decides whether the write is allowed.
6. Flip the logbook row. updateLogbookStatus() sets channel_logbook.status for the single row referenced by the CR to either suspended or open.

The asymmetric formula

In code form:

on suspend:
  let level = currentSkill.<approval_level_assigned_* for its category>
  new_level = level > 0 ? level − 1 : 0

on unsuspend:
  remaining = other rows in the same programme that are still 'suspended'
  if remaining is non-empty:
    new_level = min(level among remaining) − 1
  else:
    new_level = max(level among rows currently 'open')

The asymmetry is deliberate: on suspend, the just-suspended row’s own value is the source of truth (level − 1). On unsuspend, the suspended row that’s being re-opened isn’t a useful reference, so the formula looks at the rest of the portfolio instead.

Source: getNewApprovalLevel, unsuspendApprovalLevel, findLowestApprovalLevelSkill, findHighestApprovalLevelSkill in update.skills.service.js.

The directional UPDATE

UPDATE members
   SET <column> = <new_level>
 WHERE currency_instructor != 0
   AND CAST(<column> AS signed) <op> CAST(<new_level> AS signed)
   AND member_id = <member_id>;

Source: updateApprovalLevel in api/src/features/account/change-request/model/index.js.

The two operators:

Which member column gets written

Determined per skill by getDBFields:

The “any category” coach-priority rule is what routes 363675 (Coach Rating Assessor, trainer-category) to approval_level_coach. See Coach Assessor — cross-mapped for the full set.

Known pitfalls

Pitfall 1 — the currency_instructor != 0 clause is hardcoded

It applies even when the column being written is approval_level_trainer or approval_level_coach. So if a member’s instructor currency has lapsed, every UPDATE in this flow becomes a silent no-op for all three level columns — coach and trainer included.

Pitfall 2 — missing currentSkill wipes to zero

If getCurrentSkill() returned 0 (no match), then 0[skill_*_approval_level] is undefined, the suspend formula’s ternary fires its else-branch, and new_level = 0. The UPDATE then runs unconditionally (the guard stored > 0 is true for any positive level). This is the wipe-to-zero failure mode seen in Example 2.

Pitfall 3 — suspending the lowest-level skill drags the whole level down

currentSkill.level − 1 is computed in isolation, with no reference to the member’s other skills. So suspending the lowest skill in someone’s portfolio always pulls the level field down to (lowest − 1), even when dozens of higher skills remain open. See Example 4.

Pitfall 4 — trainer-category rows with zero assigned-trainer value

The column-mapping rule prefers a positive coach column over the category, but trainer columns aren’t given that priority. Result: rows like 806792 (Train/Qualify High Wind Skills: lvl_trainer = 0 but lvl_instr = 2) suspend to approval_level_trainer = 0 regardless of their instructor-side value. See the anomaly summary.

Possible mitigations

1. Data: stop offering parent / prerequisite / anomaly rows as suspendable, or correct their approval_level_assigned_* to the value implied by their name. The full anomaly list is in the anomaly summary.
2. Code: change getNewApprovalLevel to skip the UPDATE when the suspended skill’s level is 0, and/or recompute the level from the remaining open skills rather than currentSkill.level − 1. (Out of scope for this doc.)

Worked examples

Example 1 — standard instructor suspend

Starting state:

• members.approval_level_instructor = 7, currency_instructor = 1
• Skill 162 (Teach/Spot Head Down, lvl_instr = 7) is open in the member’s logbook

Admin approves Suspend Instructor Skill against skill 162:

1. new_level = 7 − 1 = 6
2. Guard 7 > 6 ✓ → UPDATE runs
3. approval_level_instructor = 6; logbook row → suspended

Now the admin approves a second suspension against skill 161 (also lvl_instr = 7):

1. new_level = 7 − 1 = 6
2. Guard 6 > 6 ✗ → UPDATE skipped
3. approval_level_instructor stays at 6; logbook row → suspended

The level only drops once per tier. Subsequent same-tier suspensions flag the logbook entry without re-lowering the field.

Example 2 — the data-anomaly trap

Same starting state as Example 1. Admin approves Suspend Instructor Skill against skill 155 (Level 4 Flight Skills, lvl_instr = 0):

1. new_level = 0 (the formula’s else-branch fires because the assigned column is 0)
2. Guard 7 > 0 ✓ → UPDATE runs
3. approval_level_instructor = 0

The member is now gated to 0 authority despite still holding ten other open level-7 skills. This was the failure mode hit by member 70603 (Jonah Gleeson) in April 2026 — the change-request UI offered skill 155 as suspendable even though it’s not really a leaf teaching skill.

Example 3 — unsuspend with the asymmetric formula

Starting state:

• members.approval_level_instructor = 0
• Skill 162 (lvl_instr = 7) — suspended
• Skill 161 (lvl_instr = 7) — suspended
• Skill 152 (lvl_instr = 7) — open

Admin approves Unsuspend Instructor Skill against skill 162:

1. Other suspended skills remain (161). min(suspended) = 7. So new_level = 7 − 1 = 6.
2. Guard 0 < 6 ✓ → UPDATE runs
3. approval_level_instructor = 6; logbook row → open

Then the admin processes the second unsuspend against skill 161:

1. No suspended skills remain. new_level = max(open) = 7.
2. Guard 6 < 7 ✓ → UPDATE runs
3. approval_level_instructor = 7; logbook row → open

This restoration path is the reason an asymmetric formula exists — suspend can use the simpler “the skill being acted on, minus one” because something else is the source of truth on unsuspend.

Example 4 — Level-4 instructor, Level-1 skill suspended

This is the inverse of the “you’d expect higher skills to protect you” intuition. They don’t.

Starting state — a senior Instructor Level 4 with a full portfolio:

• members.approval_level_instructor = 7, currency_instructor = 1
• Their instructor-category logbook is fully populated:

Admin approves Suspend Instructor Skill against skill 361 (Completed FITP, lvl_instr = 1) — the single Instructor Level 1 leaf skill the member holds:

1. currentSkill[skill_instructor_approval_level] = 1

2. new_level = 1 − 1 = 0

3. UPDATE runs:

   UPDATE members
      SET approval_level_instructor = 0
    WHERE currency_instructor != 0
      AND CAST(approval_level_instructor AS signed) > CAST(0 AS signed)
      AND member_id = <id>;

   Guard 7 > 0 ✓ → write.

4. approval_level_instructor = 0; row 361 → suspended.

The member is now gated to 0 authority despite holding 20+ open skills up to level 7. Same end-state as the data-anomaly trap in Example 2, but here the data isn’t broken — the formula does this on purpose any time the suspended skill happens to sit below the member’s current stored level.

Recovery: admin processes Unsuspend Instructor Skill against skill 361:

1. No other suspended skills remain in the instructor programme.
2. new_level = max(level among open) = 7.
3. Guard 0 < 7 ✓ → write.
4. approval_level_instructor = 7; row 361 → open.

So the unsuspend formula does recover correctly via findHighestApprovalLevelSkill. The asymmetry is only on the way down.

Reference: skill catalogue

Source of truth: rows in channel_skills joined to channel_skills_categories via channel_skills_category_posts. Only status = 'open' rows are considered approveable.

In the tables below:

• lvl_instr / coach / trainer — the per-skill values stored on channel_skills.approval_level_assigned_*. These are the minimum approver tier required to sign that skill, and the input the suspend formula reads.
• Kind: a row’s role in the hierarchy.
  • Leaf — a real teaching/training skill that can be approved directly.
  • Parent — a grouping row; other skills point at it via skill_parent. Not directly approveable.
  • Prereq — a checkbox skill (age, experience, FITP completion).
  • Anomaly — looks like a leaf but has approval_level_assigned_* = 0. Suspending one of these is what triggered the member 70603 incident.

Coach (cat_parent_id = 38)

Note: every coach-category row has approval_level_assigned_coach = 0. Coach approval authority itself does not come from coach-category skills — it comes from the two coach-assessor rows in the trainer category (363675 / 386861, listed below). Suspending any coach row through this flow therefore writes approval_level_coach = 0 on that member, which is consistent with the rating being revoked.

Instructor / Controller (cat_parent_id = 39)

Instructor Level 1 (cat_id = 44)

Airflow Controller (cat_id = 62)

These are gated by role_id, not by approval_level_instructor. Suspending any of them through this flow will wipe approval_level_instructor to 0 (see Example 2).

Instructor Level 2 (cat_id = 65)

Instructor Level 3 (cat_id = 64)

Note the asymmetric scale: Static-Level-3 children carry lvl_instr = 3 while Dynamic-Level-3 children carry lvl_instr = 6. The numeric value is an opaque approver-tier, not a 1-to-1 match with the IBA programme level on the category name.

Instructor Level 4 (cat_id = 63)

Additional Skills — instructor (cat_id = 83, cat_id = 118)

Trainer (cat_parent_id = 40)

Trainer Level 1 (cat_id = 45)

Trainer Level 2 (cat_id = 68)

Trainer Level 3 (cat_id = 67)

Trainer Level 4 (cat_id = 66)

Coach Assessor — cross-mapped (cat_id = 116, cat_id = 117)

These are categorised under trainer (parent_id = 40), but their operative value is on approval_level_assigned_coach. The suspend code’s getDBFields prioritises any positive coach column over the category, so suspending either of these touches approval_level_coach on the member, not _trainer.

Anomaly summary

Rows that look approveable through the change-request UI but trigger one of the pitfalls above. Fixing these — either at the data layer or in getNewApprovalLevel — is the practical mitigation for the member 70603 class of incident.

See also

• Approval levels — what the approval_level_* fields mean and how they’re normally derived upwards.
• Skill levels — what skills make up each programme level.
• Logbook lifecycle — the state-machine that the status column on each logbook row moves through.