This guide explains how Sector Agencies submit data through the WSQ SA Workspace (form.html) and documents the system architecture, data model, and business rules for the SFw 1.0 → 2.0 transition exercise managed by NJSDO.
Platform v2.1 · Supabase backend · 34 Sector Agencies · 57 SFw Sectors · Last updated: 2026-05-21 (data quality patches + mandatory EC review)
Open the SA Workspace (form.html). Log in with the credentials provided by NJSDO. Enter your username (e.g. edb) and password (e.g. edb1234). Do not use an email address — your login is your agency short name only.
After logging in, your assigned sector(s) are displayed on the dashboard as sector chips. Select the sector you want to work on. The system pre-loads all reference data — courses, skills, and K&A statements — from the NJSDO database for your sector. Agencies with only one sector will see a single chip displayed; they do not need to switch sectors.
Work through the seven sections of your sector workspace. You do not need to complete them in one session — save your draft at any time and return to continue.
| Tab | Section | Your Input |
|---|---|---|
| 1 | Existing Modular Courses (SOA) | Whitelist decision (YES / NO) for each SOA course in your sector |
| 2 | Existing Full Qualifications (FQ) | Whitelist decision (YES / NO) for each FQ in your sector |
| 3 | Entry Criteria | Two sub-sections: [Optional] Define new entry criteria — propose new SFw 2.0 entry criteria for modular courses. Select a SFw 2.0 skill via typeahead, choose Proficiency Level and K&A statements, specify Training Provider(s), and optionally name a course title. A reference panel automatically surfaces any courses with retired/unmapped SFw 1.0 skills that will cease upon expiry, filtered to the selected skill. [Mandatory] Review previous entry criteria — applies only to the 7 SAs that had SFw 1.0 entry criteria on file (AIC/MOH, MHA/SPF-CPS, MIPD/PPD, NParks, NAC, NEA). NJSDO has pre-loaded each SA's SFw 1.0 criteria alongside the equivalent SFw 2.0 K&A statements. The SA must review each skill and select which SFw 2.0 K&A statements they wish to adopt as new entry criteria. Exact-match K&A items are pre-selected as a starting point. A coverage badge shows how many statements are selected — a warning is shown if below 40%, but the SA can still save and submit. |
| 4 | K&A — Sectoral Skills (SSS) | Opt in to optional SSS bundles (default bundle is pre-assigned — no action needed otherwise) |
| 5 | K&A — Cross-Sectoral Skills (CSS) | For each CSS skill with whitelisted courses, optionally assign each course to a K&A bundle (B1/B2/B3). Bundle assignment is optional — SAs may leave it unassigned if no tight control over bundles is needed. |
| 6 | Propose New Modules | Propose new Modular courses not yet in TPGateway |
| 7 | Propose New FQs | Propose new Full Qualifications with component SOA courses |
Click Save Draft at any time to preserve your progress. Drafts are stored in the NJSDO Supabase database and accessible only to your SA. You can save and return as many times as needed before the submission deadline.
When all sections are complete, click Submit to NJSDO. Your submission is locked in the database. If you need to make changes after submitting, contact NJSDO Data Management & Operations — an admin can re-open your submission for editing. You can download a copy of all your decisions at any time using the 📥 Export Excel button in the navigation bar.
WSQ Data Extractor v2.0 is a Progressive Web App (PWA) backed by Supabase (PostgreSQL). All reference data is pre-loaded by NJSDO. SAs interact through the browser — no Excel files are uploaded.
| Layer | Technology | Purpose |
|---|---|---|
| Frontend | HTML / CSS / Vanilla JS | form.html — SA workspace; guide.html — this guide |
| Auth | Supabase Auth | 34 SA accounts; email/password login; JWT tokens |
| Database | Supabase (PostgreSQL 15) | Reference tables + submission tables with Row Level Security |
| API | PostgREST (via Supabase) | Max Rows setting = 10,000 (Dashboard → Settings → API). Default is 1,000 — must be increased to avoid silent row truncation for large sectors. |
| Offline | Service Worker (sw.js) | Cache v3; CDN-first for SheetJS; fallback for app shell |
| Export | SheetJS (xlsx.full.min.js) | Client-side Excel generation — available on demand via Export Excel button |
Reference tables (NJSDO-managed, read-only to SAs):
| Table | Content | Source | Rows |
|---|---|---|---|
sector_agencies | 34 SA records with login credentials. sa_id is INTEGER (not UUID). supabase_user_id is UUID (auth.users.id). | Seeded via schema SQL | 34 |
ref_sectors | 57 sectors (40 main + 17 sub-sectors), each linked to an SA | Seeded via schema SQL | 57 |
ref_base_skill | Exact replica of base_skill sheet — all 17 columns stored as-is (no conversions). All SFw 1.0/2.0 K&A statements across CSS, SSS, Interim, and pilot rows. CSS = data_source = 'Cross-sector skill'; SSS = data_source = 'Sector-specific skill'. Join key: ref_courses_soa.skill_code = sfw_v1_code. | base_skill sheet | 275,878 |
ref_skill_crosswalk | SFw 1.0 → 2.0 skill code mappings (direct / remapped) | base_11k_6k sheet | 11,907 |
ref_courses_soa | SOA courses from Consolidated Masterlist. whitelist_status = 'Whitelisted' marks pre-decided courses. | Consolidated Masterlist | 10,745 |
ref_courses_fq | FQ courses from Consolidated Masterlist. whitelist_status = 'Whitelisted' marks pre-decided FQs. | Consolidated Masterlist | 2,410 |
ref_bundles_sss | SSS default and optional K&A bundles | w_sss_bundles sheet | — |
ref_bundles_css | CSS K&A bundles (bundle1/2/3 per parent skill) | w_css_bundles sheet | — |
ref_ec_soa_prev | SFw 1.0 SOA entry criteria previously on file per SA — reference baseline for the Mandatory Review exercise. One row per SA × SFw 1.0 skill. Stores original TP names, course titles, and other criteria. RLS-filtered: each SA sees only their own rows. | base_entry_criteria_soa sheet · migrate_ec_soa_prev_20260521.py | 42 (7 SAs) |
Submission tables (SA-writable, RLS-protected — each SA sees only their own data):
| Table | SA Input | Key Columns |
|---|---|---|
submissions | Master record — status (draft/submitted), version, timestamps | sa_id INTEGER, sector_name, status, version |
sub_course_soa | Whitelist decision (YES/NO) per SOA course; per-course CSS bundle assignment | whitelist_decision, selected_css_bundle |
sub_course_fq | Whitelist decision (YES/NO) per FQ; per-course CSS bundle assignment | whitelist_decision, selected_css_bundle (added 2026-05-19) |
sub_ka_css | Bundle selection (1/2/3) per CSS K&A item | — |
sub_ka_sss | Optional SSS bundle opt-in flags | — |
sub_entry_criteria | Entry criteria selection / custom proposals (optional new criteria) | — |
sub_ec_soa_review | Mandatory review — SA's selected SFw 2.0 K&A statements adopted from previous SFw 1.0 entry criteria. One row per (submission_id, ec_prev_id). UNIQUE constraint prevents duplicates. selected_ak_ids is JSONB array of sfw_v2_ak_id strings. Populated by Save Draft. | submission_id, ec_prev_id FK → ref_ec_soa_prev, selected_ak_ids JSONB, remarks |
sub_new_mod | New modular course proposals | — |
sub_new_fq | New Full Qualification proposals | — |
submission_audit_log | System-generated audit trail (not SA-editable) | sa_id INTEGER, action, timestamp |
sub_course_fq.selected_css_bundle added via database/add_fq_css_bundle_20260519.sql. submission_audit_log.sa_id is INTEGER (not UUID). ref_entry_criteria and ref_fq_components were dropped on 2026-05-15 and are no longer used.cease_to_exist BOOLEAN DEFAULT FALSE added to ref_courses_soa and ref_courses_fq via database/patch_cease_to_exist_20260520.sql. 20 SOA rows + 16 FQ rows flagged TRUE — WPH-sector courses whose SFw 1.0 skill codes have no SFw 2.0 mapping. Join key for the Entry Criteria reference panel: ref_base_skill.sfw_v1_code = ref_courses_soa.skill_code (different column names across tables).sector_agencies, ref_courses_soa, ref_courses_fq via database/patch_sector_agency_norm_20260521.sql — EDB/EntSG → EDB / EntSG, MOM-R1 → MOM R1-> BCA/EDB/EntSG/MHA/SCDF/WSHC. username fields (login identifiers) were intentionally left unchanged. ref_ec_soa_prev and sub_ec_soa_review added for the Mandatory Review exercise. Effective dates backfilled for 533 SOA + 213 FQ rows from Consolidated Masterlist — 11 SOA + 22 FQ rows remain NULL (absent from Masterlist; flag to NJSDO for manual input).
All submission tables are protected by RLS. The helper function auth_sa_id() resolves the logged-in user's UUID to their sa_id. SAs can only read and write their own submission records. ref_ec_soa_prev is also RLS-filtered — each SA only sees their own prior entry criteria rows. SAs with no rows in ref_ec_soa_prev (i.e. not among the 7 with prior criteria) will see an empty Mandatory Review tab — this is correct behaviour. CSS reference data is readable by all authenticated users since CSS skills appear across multiple sectors.
| Rule | Detail |
|---|---|
| One sector → one SA | Each sector is owned by exactly one Sector Agency |
| One SA → multiple sectors | An SA may own multiple sectors (e.g. EDB owns 6 sectors) |
| Sub-sectors | 17 sub-sectors (prefixed with " – ") are treated as independent workspaces |
| CSS skills | Cross-sectoral — eligible across all sectors; each SA selects bundles independently; NJSDO reconciles in Phase 3 |
| SSS skills | Sector-specific — default bundle pre-assigned; SA action only for optional bundles |
| Rule | Detail |
|---|---|
| Previously submitted (NJSDO) | Courses with whitelist_status = 'Whitelisted' in ref_courses_soa or ref_courses_fq are already whitelisted based on inputs provided in the earlier whitelisting exercise — SA does not need to take action on these in the Whitelisting tabs. They appear as read-only. |
| SA-decided | SA explicitly marks a course as whitelist_decision = 'YES' in sub_course_soa or sub_course_fq via the Whitelisting tabs. |
| 1 Modular Course → 1 SOA | Each module issues exactly one Statement of Attainment |
| 1 FQ → ≥5 SOA courses | Each Full Qualification comprises at least five Modular component courses |
| cease_to_exist | NJSDO-set boolean flag (ref_courses_soa.cease_to_exist, ref_courses_fq.cease_to_exist) — marks course-skill pairs where the SFw 1.0 skill has no SFw 2.0 equivalent and the course will cease upon expiry. Set via SQL patch (2026-05-20). Surfaced in two places in form.html: (1) ⚠️ Retiring badge on courses in the Whitelisting Exercise tables; (2) Amber reference panel inside each Entry Criteria proposal card — automatically queries all courses (cross-sector) with cease_to_exist = TRUE that map to the SA's selected SFw 2.0 skill, using the join ref_base_skill.sfw_v1_code = ref_courses_soa.skill_code. SAs do not set this flag — read-only to all SAs. |
| Resubmission | SA cannot self-unlock a submitted form. Admin unlocks via admin.html. Locked banner in form.html reads "Contact NJSDO admin to unlock." |
The K&A Bundling Exercise tab enables SAs to perform two related exercises:
(a) K&A Scoping: Reviewing which K&A statements belong to each CSS skill bundle. All whitelisted courses — both previously submitted (NJSDO-determined) and SA-decided — contribute their skill codes to the K&A Bundling Exercise view immediately. If BCA has 120 previously submitted Whitelisted courses mapping to 50 distinct CSS skills, all 50 appear in the K&A Bundling Exercise tab from the moment the SA logs in — before any additional whitelisting action is taken.
(b) Bundle Assignment (Optional): For each CSS skill block, the SA can see all courses using that skill and optionally assign each course to a K&A bundle (Bundle 1, Bundle 2, or Bundle 3). This tagging is optional — SAs that do not need tight bundle control can leave all courses unassigned.
| Element | Behaviour |
|---|---|
| Skill trigger | All whitelisted courses (previously submitted + SA-decided, both SOA and FQ) that map to a CSS v1_code cause that skill to appear in the tab |
| Course list per skill | Collapsible list showing every whitelisted course using that skill. SOA (modular) and FQ courses shown together; FQ courses are visually distinguished with an FQ badge. |
| Course detail shown | Course Ref, Course Title, TP Name, SFw Sector, WSQ Category, About This Course (truncated ~80 chars with tooltip), What You'll Learn (truncated ~80 chars with tooltip) |
| Bundle 1 | Always active. Exact-match K&A statements are auto-ticked. SA can add more K&A to Bundle 1. |
| Bundle 2 | Dormant by default. SA activates by clicking "Add Bundle 2" — becomes active once Bundle 1 has ≥1 Knowledge + ≥1 Ability statement selected. The 40% coverage threshold does not gate Bundle 2 activation. |
| Bundle 3 | Dormant by default. SA activates by clicking "Add Bundle 3" — becomes active once Bundle 2 is non-empty. |
| Per-course bundle dropdown | Each course in the skill block has a dropdown (Bundle 1 / Bundle 2 / Bundle 3 / None). Selecting a bundle tags that course to that bundle. One course can only be tagged to one bundle. |
| Bundle assignment for previously submitted courses | Previously submitted Whitelisted courses can also be assigned to a bundle (e.g. for highly regulated courses). This creates a sub_course_soa row with whitelist_decision = 'YES' and selected_css_bundle set. |
| FQ course storage | FQ bundle assignment stored in sub_course_fq.selected_css_bundle (column added 2026-05-19) |
| Dimension | CSS (Cross-Sectoral Skills) | SSS (Sector-Specific Skills) |
|---|---|---|
| Eligibility | Appears across multiple sectors; SA-independent selection | Sector-specific only |
| Bundle options | Up to 3 bundles (B1/B2/B3) | 1 default + up to 2 optional bundles |
| SA action | K&A scoping + optional per-course bundle assignment | Default pre-assigned; opt-in to optional bundles only |
| Reconciliation | NJSDO reconciles cross-SA selections in Phase 3 | No cross-SA conflict; sector-isolated |
| match_type | Meaning | SA Treatment |
|---|---|---|
exact_match | Skill exists in both SFw 1.0 and 2.0 | Auto-included in B1 — SA can remove or move to another bundle |
sfw_v2_only | New skill in SFw 2.0 with no 1.0 equivalent | Available for manual selection at SA discretion |
sfw_v1_only | Legacy SFw 1.0 skill with no 2.0 equivalent | Not available for selection (excluded from output) |
| Rule | Threshold | Enforcement |
|---|---|---|
| K&A coverage per bundle | ≥ 40% of all K&A statements in the skill | Warning only — SA is notified but can still save and submit |
| Minimum Knowledge statements | ≥ 1 Knowledge (K-type) statement per bundle | Warning only — same as above |
| Minimum Ability statements | ≥ 1 Ability (A-type) statement per bundle | Warning only — same as above |
| Status | Behaviour |
|---|---|
draft | SA can save, overwrite, and resume at any time. |
submitted | Locked. Version counter increments. SA cannot self-unlock — NJSDO admin must re-open via admin.html. |
Extracts a row for every (bundle_code, sfw_v2_ak_id) pairing from the CSS and SSS tabs of an uploaded Excel template.
CSS: sector=1, v1c=2, v2c=7, akid=12, stmt=13, ktype=14, mtype=15, bc1=23, bc2=24, bc3=25, b1=27, b2=28, b3=29 SSS: sector=1, v1c=2, v2c=7, akid=12, stmt=13, ktype=14, mtype=15, bcD=22, bc1=23, bc2=24, bD=26, b1=27, b2=28
v2_code are skipped.bundle_N column contains a truthy value (case-insensitive "yes" or any non-empty non-zero value).sfw_v2_ak_id is (NONE) are classified as sfw_v1_only and excluded from Output 1.exact_match — row has both a v1_code and a v2_codesfw_v2_only — row has a v2_code but no v1_codesfw_v1_only — ak_id is (NONE) (legacy, excluded from output)A single K&A statement selected for multiple bundles produces one output row per bundle. For CSS: up to 3 bundles (bc1/bc2/bc3). For SSS: up to 3 bundles (bcD/bc1/bc2).
Bundle codes are read from the labelled code columns adjacent to each bundle flag column. If no code is found, the bundle index (e.g., bundle_1) is used as a fallback.
Extracts one row per course from the MOD and FQ tabs of an uploaded Excel template.
MOD: status=0, sector=1, tp=2, ref=3, title=4, v1c=5, v1t=6, effFrom=7, effTo=8, cat=9, wlStatus=10, sssB=18, cssB=19, v2css=20 FQ: status=0, ref=1, title=2, skillTitle=3, skillCode=4, valFrom=5, effFrom=6, valTo=7, funding=8, tp=9, nature=12, wlStatus=14, sssB=22, cssB=23, v2css=24
status field is passed through to allow downstream filtering.Excel serial dates are converted to YYYY-MM-DD strings using: new Date(Math.round((serial - 25569) * 86400000)). If a cell is already a string, it is passed through as-is.
sss_bundle — the SSS bundle code the course is mapped tocss_bundle — the CSS bundle code(s) the course is mapped tosfw_v2_css — the SFw 2.0 CSS identifier (computed column in the template)course_type — MOD or FQ depending on source tabExtracts one row per proposed new course/FQ from the New Mod and New FQ tabs of an uploaded Excel template.
New Mod / New FQ: rowId=0, v2t=1, bcode=2, tp=3, ctitle=4, other=5, err=6
v2_title are skipped.errors column surfaces any validation flags written by the template's formula engine.source_tab is set to New Mod or New FQ to distinguish origin.These are the rules enforced by the Excel template's formula engine and honoured in downstream validation. The SA Workspace surfaces these as warnings.
| Rule | Threshold | Applies To |
|---|---|---|
| K&A coverage per bundle | ≥ 40% of all K&A statements for the skill | Each bundle · Warning only — does not block save or submit |
| Minimum Knowledge statements | ≥ 1 Knowledge (K-type) statement per bundle | Each bundle · Warning only |
| Minimum Ability statements | ≥ 1 Ability (A-type) statement per bundle | Each bundle · Warning only |
| CSS bundle limit | Up to 3 bundles (B1, B2, B3) | Cross-Sectoral Skills tab |
| SSS bundle structure | 1 default bundle + up to 2 optional bundles | Sector-Specific Skills tab |
| Bundle 2 activation prerequisite | Bundle 1 must have ≥ 1 Knowledge + ≥ 1 Ability statement selected. 40% coverage is NOT required. | K&A Bundling Exercise tab |
| Bundle 3 activation prerequisite | Bundle 2 must be non-empty before Bundle 3 can be activated | K&A Bundling Exercise tab |
errors column.
| Step | Who | What |
|---|---|---|
| A | NJSDO | Apply supabase_schema_v2.sql — creates all tables, RLS, seeds 34 SAs and 57 sectors |
| B1 | NJSDO | Run migrate_ref_base_skill_20260519.py — loads ref_base_skill (275,878 rows) from base_skill sheet. Full truncate-and-reload; all 17 columns stored as TEXT. Re-run to refresh. |
| B2 | NJSDO | Run migrate_composite_pk_20260515.py — loads ref_courses_soa (10,745 rows) and ref_courses_fq (2,410 rows) from the Consolidated Masterlist. Script reads only base_* sheets. |
| B3 | NJSDO | Run SQL patch database/patch_sector_agency_norm_20260521.sql — corrects 2 SA name normalization errors in sector_agencies, ref_courses_soa, ref_courses_fq. username (login key) is intentionally preserved. |
| B4 | NJSDO | Run backfill_effective_dates_masterlist_20260521.py — fills effective_from/effective_to for NULL rows in ref_courses_soa and ref_courses_fq using the Consolidated Masterlist. Only updates WHERE effective_from IS NULL; safe to re-run. Post-backfill: 11 SOA + 22 FQ rows remain NULL (absent from Masterlist — flag to NJSDO for manual input). |
| B5 | NJSDO | Apply database/create_ref_ec_soa_prev_20260521.sql, then run migrate_ec_soa_prev_20260521.py — populates ref_ec_soa_prev (42 rows, 7 SAs) from the base_entry_criteria_soa sheet. Includes SA name normalization. Must be run before SAs log in for the Mandatory Review exercise. |
| B6 | NJSDO | Apply database/create_sub_ec_soa_review_20260521.sql — creates the sub_ec_soa_review submission table with full RLS (per-SA read/write of own rows only). |
| C | NJSDO | Run seed_auth_accounts.py — creates 34 Supabase Auth accounts, links to sector_agencies |
| D | NJSDO | Supabase Dashboard → Settings → API → set Max Rows = 10,000 (required — default 1,000 silently truncates large sectors) |
| E | NJSDO | Distribute credentials to SAs via secure channel |
| F | SA | Log in to form.html, work through 7-tab sector workspace, save drafts |
| G | SA | Submit — system locks submission. SA downloads timestamped Excel export via Export Excel button if needed for internal audit. |
| H | NJSDO | Review submissions via Admin panel (Phase 2); reconcile CSS K&A bundle conflicts across SAs |
| I | NJSDO | Export consolidated dataset → S3 (Parquet, partitioned by year/month) → Redshift/Athena → dashboards and policy APIs |
| Data | How Loaded | Why |
|---|---|---|
CSS K&A statements (ref_base_skill) | Batched in chunks of 100 skill codes via .in('sfw_v1_code', chunk) with .eq('data_source', 'Cross-sector skill'). Each batch has .limit(5000). Fields: sfw_v1_code, sfw_v2_code, sfw_v2_ak_id, ka_statement, ka_classification, sfw_v2_proficiency_level, sfw_v1_proficiency_level, is_match_knowledge_ability, sfw_sector. | PostgREST URL length limit (~8KB) prevents passing 300+ codes in one request. Large sectors (e.g. BCA Built Environment) have 300+ CSS skill codes generating 1,300+ rows per 100-code batch — requires Max Rows = 10,000. |
| SOA and FQ courses | Single query filtered by sector_name | Sector-scoped; manageable row counts |
| Draft data | Loaded from sub_course_soa, sub_course_fq, sub_ec_soa_review, and other sub_ tables on login | Restores all SA decisions. FQ draft includes selected_css_bundle. Mandatory Review draft restores selected K&A and remarks per ec_prev_id. |
Mandatory Review data (ref_ec_soa_prev) | Loaded at login via ref_ec_soa_prev (RLS-filtered). K&A options loaded from ref_base_skill filtered to matching sfw_v1_code values, excluding is_match_knowledge_ability = 'sfw_v1_only'. Exact-match items pre-selected by default. | Only 7 SAs have data — all others see empty Mandatory Review tab (correct behaviour) |
SAs can download an Excel file at any time using the 📥 Export Excel button in the navigation bar. The export is generated client-side by SheetJS and contains all entered decisions across all 7 sections. File naming convention: WSQ_Export_<SA_shortname>_<sector>_YYYYMMDD_HHmmss.xlsx
created_at / updated_at timestampssubmission_audit_log — immutable audit trailkgriflirhjiwqzyyzwhd · Region: ap-southeast-1 (Singapore)wsq-extractor-v3 — bump to v4 in sw.js to force cache refresh after updatesNJSDO (National Jobs and Skills Data Office) is managing the SFw 1.0 → SFw 2.0 skills framework transition for 34 Sector Agencies across 57 WSQ sectors. This platform replaces the offline Excel submission process with a browser-based, database-driven data collection and validation workspace. All SA inputs feed directly into NJSDO's downstream data infrastructure (S3 / Redshift / Athena) for policy-grade consumption.
| Phase | Deliverable | Owner | Status |
|---|---|---|---|
| Phase A | Database schema, reference data migration, SA auth seeding | NJSDO Data Ops | ✅ Complete |
| Phase B | SA Workspace (form.html) — online submission portal with 7-tab exercise | NJSDO Data Ops | ✅ Complete |
| Phase C | Admin panel (admin.html) — submission tracking and unlock management | NJSDO Data Ops | ✅ Complete |
| Phase D | Downstream export — consolidated Parquet → S3 → Redshift → policy APIs | NJSDO Data Ops | 🔲 Pending |
| # | Requirement | Implementation |
|---|---|---|
| A1 | Schema design with full RLS for 34 SA accounts | supabase_schema_v2.sql — 8 reference tables, 8 submission tables, auth_sa_id() helper, RLS policies |
| A2 | Load SFw 2.0 base skill K&A data (275,878 rows) | migrate_ref_base_skill_20260519.py — full truncate-and-reload from base_skill sheet; all 17 columns stored as TEXT |
| A3 | Load Consolidated Masterlist (SOA + FQ courses) | migrate_composite_pk_20260515.py — ref_courses_soa (10,745 rows), ref_courses_fq (2,410 rows); composite PK (course_ref, skill_code) |
| A4 | Backfill effective dates across all SOA/FQ rows | Phase 1: 18-part SOA patch + 1-part FQ patch (2026-05-19) from TP Gateway source data. Phase 2: backfill_effective_dates_masterlist_20260521.py filled additional 533 SOA + 213 FQ rows from Consolidated Masterlist — 11 SOA + 22 FQ remain NULL (genuine source gaps; flag to NJSDO). |
| A5 | Flag courses with retired/unmapped SFw 1.0 skills | patch_cease_to_exist_20260520.sql — cease_to_exist BOOLEAN added to both ref tables; 20 SOA rows + 16 FQ rows flagged TRUE (WPH-sector courses) |
| A6 | Correct SA name normalization errors in DB | patch_sector_agency_norm_20260521.sql — fixed EDB/EntSG → EDB / EntSG and MOM-R1 → MOM R1-> BCA/EDB/EntSG/MHA/SCDF/WSHC across 3 tables. Login usernames (edbentsg, momr1) intentionally unchanged. |
| A7 | Load mandatory SOA entry criteria baseline for Mandatory Review | create_ref_ec_soa_prev_20260521.sql + migrate_ec_soa_prev_20260521.py — 42 rows for 7 SAs; all 42 SFw 1.0 codes matched in ref_base_skill |
| A8 | Create Mandatory Review submission table | create_sub_ec_soa_review_20260521.sql — full RLS, UNIQUE constraint on (submission_id, ec_prev_id), selected_ak_ids JSONB |
| A9 | Seed 34 SA auth accounts and sector ownership | seed_auth_accounts.py; sector_agencies table with supabase_user_id UUID link |
| # | Requirement | Implementation |
|---|---|---|
| B1 | Secure login with SA-scoped session | Supabase Auth; JWT tokens; RLS enforces data isolation at DB level |
| B2 | Multi-sector navigation for SAs owning >1 sector | Sector chips in nav bar; switching loads fresh reference data per sector |
| B3 | SOA Whitelisting Exercise — YES/NO per course with remarks | Tab 1; paginated table with search/filter; previously submitted Whitelisted courses shown read-only; ⚠️ Retiring badge for cease_to_exist courses |
| B4 | FQ Whitelisting Exercise — YES/NO per FQ with remarks | Tab 2; same pattern as SOA; FQ-specific fields (funding category, nature of training) |
| B5 | Entry Criteria — optional new proposals and mandatory SFw 1.0 → 2.0 review | Tab 3; two sub-tabs: (1) Optional — skill typeahead search, PL selection, K&A checkbox scoping, TP/course fields, retiring-courses reference panel cross-sector; (2) Mandatory — pre-loaded from ref_ec_soa_prev (7 SAs only); one card per SFw 1.0 skill showing original TP/course/criteria, collapsible source panel, K&A checkboxes grouped by PL with exact-match items pre-selected, coverage badge (warning only), remarks textarea. Saved to sub_ec_soa_review. SAs not in the 7 see empty tab — correct behaviour. |
| B6 | K&A Bundling Exercise — CSS skills across whitelisted courses | Tab: CSS bundles (B1/B2/B3) with exact-match auto-selection; per-course bundle dropdown; dormant bundle activation with prerequisite validation |
| B7 | SSS K&A — optional bundle opt-in | Default bundle pre-assigned; SA opts into optional bundles only |
| B8 | Save draft and resume across sessions | All decisions persisted to Supabase in real-time via Save Draft; draft restored on login |
| B9 | Submit and lock with version control | Submit button locks form; version counter incremented; admin required to re-open |
| B10 | Excel export of all decisions | Client-side SheetJS export with all 7 tabs; timestamped filename |
| B11 | Performance — large sector support (>1,000 courses) | Supabase Max Rows = 10,000; skill typeahead uses prefix search (not leading wildcard) to avoid timeout on 275,878-row table; CSS K&A batched in 100-code chunks |
| # | Requirement | Implementation |
|---|---|---|
| C1 | Submission completion tracker across all 34 SAs × all sectors | Admin tab — shows submission status (Draft / Submitted), completion %, last updated, per SA per sector |
| C2 | Unlock submitted SA forms for re-editing | Admin re-open button resets submissions.status to draft; SA can then save and resubmit |
| C3 | Admin-only access | Separate login required; not accessible via SA credentials |
| # | Requirement | Notes |
|---|---|---|
| D1 | Consolidated export of all SA submissions from Supabase | Query sub_course_soa, sub_course_fq, sub_ka_css, sub_entry_criteria, sub_ec_soa_review, sub_new_mod, sub_new_fq — joined to reference tables including ref_ec_soa_prev for Mandatory Review data |
| D2 | Output format: Parquet (SNAPPY), partitioned by year/month | Target: AWS S3 bucket under NJSDO data lake namespace |
| D3 | Redshift / Athena integration for policy consumption | Expose as queryable tables for NJSDO dashboards and downstream APIs |
| D4 | CSS bundle conflict reconciliation report | Identify where multiple SAs have assigned different bundles to the same CSS K&A statement; requires cross-SA view (NJSDO only) |
| D5 | Data quality validation before export | Completeness checks (all mandatory fields filled), consistency checks (whitelist YES ↔ bundle assignments), audit log reconciliation |
| Decision | Rationale |
|---|---|
| No Excel upload — browser-native DB writes | Eliminates file versioning confusion; all data is authoritative at submission time; eliminates re-keying errors at NJSDO end |
Composite PK (course_ref, skill_code) on ref tables | One course maps to multiple SFw 1.0 skills; a single course_ref key would not support row-level precision for whitelist decisions and cease_to_exist flagging |
| CSS K&A loaded per sector (not globally) | Global load of 275,878 rows is impractical; scoped to the sector's whitelisted skill codes; batched in 100-code chunks due to PostgREST URL limits |
| RLS at DB level (not just app level) | Ensures even direct API calls cannot access another SA's data; auth_sa_id() resolves UUID → sa_id for row-level filtering |
| Retire panel cross-sector (no SA filter) | A retiring course may be in a different SA's sector — the SA proposing new entry criteria must see all affected courses system-wide to make an informed decision |
sfw_v1_code (ref_base_skill) ≠ skill_code (ref_courses_*) | Same concept, different column names across tables — join must use explicit alias; enforced in all queries |
username vs short_name in sector_agencies | username is the stable alphanumeric login key (e.g. edbentsg, momr1) — never changed, even when the SA's display name is corrected. short_name must exactly match the Masterlist value (e.g. EDB / EntSG) — used as the join key to ref_courses_soa.sector_agency. These are two separate fields and must not be conflated. |
| base_* sheets only for migration scripts | All Python migration scripts read only base_*-prefixed sheets from wsq_master_template. No non-base sheets may be read — even as lookup or validation sources. This ensures data lineage is traceable and scripts are deterministic. |
| Mandatory Review only for 7 SAs | Only AIC/MOH, MHA/SPF-CPS, MIPD/PPD, NParks, NAC, NEA have rows in ref_ec_soa_prev. All other SAs see an empty Mandatory Review tab — this is intended, not a bug. RLS enforces the filtering automatically at the DB level. |