From cw
Use when investigating Heritage ↔ EdgeEx section drift (Heritage references sections EdgeEx has never seen) — provides schema reference, drift-detection SQL, and investigation playbook. SQL-only; user runs queries.
How this skill is triggered — by the user, by Claude, or both
Slash command
/cw:heritage-edgeex-section-driftThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Heritage (Edgenuity LMS) and EdgeEx share section state via Caliper events through Jetstream. EdgeEx is the source of truth — sections are created only via `cwng-section-api` HTTP endpoints. Heritage mirrors them by subscribing to outbound Caliper events. When Heritage references a section that EdgeEx has never seen, that's "section drift" — Heritage created or flagged a section locally without...
Heritage (Edgenuity LMS) and EdgeEx share section state via Caliper events through Jetstream. EdgeEx is the source of truth — sections are created only via cwng-section-api HTTP endpoints. Heritage mirrors them by subscribing to outbound Caliper events. When Heritage references a section that EdgeEx has never seen, that's "section drift" — Heritage created or flagged a section locally without going through the EdgeEx API.
This skill helps you compose Snowflake queries to detect and investigate drift, and guides the investigation workflow. It does NOT execute queries — the user runs them in their Snowflake UI and returns results.
SectionException: ... section is null errors in edgeex-enrollment-api, a section 404 from edgeex-section-api for an ID that Heritage is sending events for, or wants to sweep for orphaned Heritage-flagged-EdgeEx sections that don't exist in EdgeEx.section_get MCP tool first), the issue is about enrollment or grading rather than section existence, or you need today's data (Snowflake mirror lags by hours).EdgeEx (source of truth) Heritage (mirror)
───────────────────────── ─────────────────
POST /section (create)
│
▼
DynamoDB persists row
│
▼
Caliper event → Jetstream ──────────▶ Heritage subscribes,
(Section CREATED/MODIFIED/ mirrors to EDGE_LMS.COURSE
ARCHIVED/ACTIVATED) with IS_EDGE_EX = TRUE
There is no reverse path. Heritage cannot create a section in EdgeEx. If Heritage has a section that EdgeEx doesn't, Heritage produced it locally — the question is which Heritage code path did it.
Rely on these when ruling out hypotheses:
@Delete route exists. Sections can only be ARCHIVED.GET /section/{id} means the row genuinely never existed in DynamoDB.Combined: a 404 on section_get means EdgeEx has never had this section.
WELD_NORTH_PROD.EDGE_LMS.COURSEHeritage stores courses, sections, and enrollments in one table distinguished by TYPE:
| TYPE | CLASS_TYPE | What it is |
|---|---|---|
| 1 | 0 | Course template / "course build" |
| 2 | 0 | Section (mirrors to EdgeEx) |
| 4 | 0 | Enrollment |
Critical: always filter TYPE = 2 AND CLASS_TYPE = 0 for sections. Without this filter, enrollments and course templates appear as false "orphans."
Key columns for section rows:
| Column | Notes |
|---|---|
ID | UUID, stored UPPERCASE. Always UPPER() both sides of any join. |
NAME | Section display name. |
PARENT_COURSE_KEY | UUID of the parent course template. |
IS_EDGE_EX | Heritage marks this row as belonging to EdgeEx. |
SHOULD_EVENT_EDGE_EX | Heritage should publish events to EdgeEx for this row. |
SHOULD_EVENT | Heritage should publish events generally. |
SCHOOL_ID | Integer FK to the school. |
REALM_ID | Integer FK to the realm/district. |
CREATE_DATE | When Heritage created this row. |
CREATE_USER_ID | Integer ID of the Heritage user/process that created it. |
MODIFIED_DATE | Last modification timestamp. |
INACTIVE_DATE | Non-null means Heritage considers it inactive. |
Flag nuance: IS_EDGE_EX and SHOULD_EVENT_EDGE_EX can disagree. A row with IS_EDGE_EX = TRUE and SHOULD_EVENT_EDGE_EX = FALSE is real — some genuinely orphaned sections have this combination. Filter on IS_EDGE_EX for the full cohort; tighten with SHOULD_EVENT_EDGE_EX only when you care specifically about "currently emitting events."
WELD_NORTH_PROD.LANDING_ZONE.OTK_CWNG_SECTIONA DynamoDB JSON dump. The entire DynamoDB item is in a single data VARIANT column.
| Column | Type | Purpose |
|---|---|---|
data | VARIANT | Full DynamoDB JSON item |
Extract the section ID:
UPPER(PARSE_JSON(data):Item.section_id.S::string)
Important:
ACTIVE and ARCHIVED sections.AND CREATE_DATE < DATEADD('day', -1, CURRENT_TIMESTAMP()).Does Heritage know about this UUID?
SELECT TYPE, CLASS_TYPE, NAME, IS_EDGE_EX, SHOULD_EVENT_EDGE_EX,
PARENT_COURSE_KEY, CREATE_DATE, CREATE_USER_ID, INACTIVE_DATE
FROM WELD_NORTH_PROD.EDGE_LMS.COURSE
WHERE UPPER(ID) = '<UUID-IN-UPPERCASE>';
WITH heritage_edgeex_sections AS (
SELECT
UPPER(ID) AS section_id,
UPPER(PARENT_COURSE_KEY) AS course_id,
NAME AS section_name,
SCHOOL_ID,
REALM_ID,
CREATE_DATE,
CREATE_USER_ID,
MODIFIED_DATE,
IS_EDGE_EX,
SHOULD_EVENT_EDGE_EX,
SHOULD_EVENT
FROM WELD_NORTH_PROD.EDGE_LMS.COURSE
WHERE TYPE = 2
AND CLASS_TYPE = 0
AND IS_EDGE_EX = TRUE
AND INACTIVE_DATE IS NULL
AND CREATE_DATE >= '<START-DATE>' -- e.g. '2020-01-01'
AND CREATE_DATE < DATEADD('day', -1, CURRENT_TIMESTAMP()) -- avoid mirror lag
),
edgeex_sections AS (
SELECT UPPER(PARSE_JSON(data):Item.section_id.S::string) AS section_id
FROM WELD_NORTH_PROD.LANDING_ZONE.OTK_CWNG_SECTION
)
SELECT
h.*,
DATEDIFF('day', h.CREATE_DATE, CURRENT_TIMESTAMP()) AS days_since_create
FROM heritage_edgeex_sections h
LEFT JOIN edgeex_sections e ON e.section_id = h.section_id
WHERE e.section_id IS NULL
ORDER BY h.CREATE_DATE DESC;
Adjust CREATE_DATE >= '<START-DATE>' based on investigation scope. For a full historical sweep, use '2020-01-01'.
WITH heritage_edgeex_sections AS (
-- same CTE as above
),
edgeex_sections AS (
SELECT UPPER(PARSE_JSON(data):Item.section_id.S::string) AS section_id
FROM WELD_NORTH_PROD.LANDING_ZONE.OTK_CWNG_SECTION
)
SELECT
COUNT(*) AS heritage_total,
COUNT(e.section_id) AS matched_in_edgeex,
COUNT(*) - COUNT(e.section_id) AS orphans
FROM heritage_edgeex_sections h
LEFT JOIN edgeex_sections e ON e.section_id = h.section_id;
Find which Heritage user/process is creating the drift:
-- Use the same CTEs as the orphan detection query, then:
SELECT
h.CREATE_USER_ID,
DATE_TRUNC('month', h.CREATE_DATE) AS create_month,
COUNT(*) AS orphans
FROM heritage_edgeex_sections h
LEFT JOIN edgeex_sections e ON e.section_id = h.section_id
WHERE e.section_id IS NULL
GROUP BY 1, 2
ORDER BY orphans DESC
LIMIT 50;
When you have a sectionId from a failing enrollment event:
Call section_get with the section ID.
status: ARCHIVED → EdgeEx archived it. Heritage may not know — different issue from this playbook.status: ACTIVE → EdgeEx has it. The failing event is a different problem.Search logs for the section creation event:
app-logs-courseware-prod-cluster-*namespace.keyword = edgeex-prod, container_name.keyword = edgeex-section-apimatch_phrase: "Created section: ...sectionId=<UUID>"Zero hits confirms EdgeEx never persisted this section.
Ask the user to run the single-section Heritage lookup query with the UUID in uppercase.
Key fields to note from the result:
TYPE (should be 2) and CLASS_TYPE (should be 0)IS_EDGE_EX and SHOULD_EVENT_EDGE_EXCREATE_USER_ID — who/what created itCREATE_DATE — whenINACTIVE_DATE — null means Heritage still considers it activeNAME — check for patterns like - reporting copy (known PUP bug)CREATE_USER_ID, and create date. They own debugging which code path produced it.If this looks like more than a one-off, ask the user to run:
CREATE_USER_ID or month?A cluster of orphans from the same CREATE_USER_ID in the same time window points to the specific Heritage code path that's leaking.
| Pattern | Description | Status |
|---|---|---|
| PUP "reporting copy" | Heritage's Power User Portal copies sections with - reporting copy in the name and sends them as enrollment events to EdgeEx even though they're Heritage-only constructs. | Fixed by Heritage as of May 2026. Watch for similar patterns with new name suffixes. |
| Mistake | Symptom | Fix |
|---|---|---|
Forgot TYPE = 2 filter on EDGE_LMS.COURSE | Enrollments (TYPE=4) and course templates (TYPE=1) appear as orphans | Always filter TYPE = 2 AND CLASS_TYPE = 0 |
| Case mismatch on UUID join | 0 matches / everything looks orphaned | UPPER() both sides of the join |
| Included very recent Heritage rows | False orphans from mirror lag | Add CREATE_DATE < DATEADD('day', -1, CURRENT_TIMESTAMP()) |
Filtered on SHOULD_EVENT_EDGE_EX instead of IS_EDGE_EX | Misses orphans that have already been flagged to stop eventing | Use IS_EDGE_EX for the broader cohort |
| Expected EdgeEx Snowflake mirror to have today's sections | Missing rows | Mirror lags by hours. Use the MCP section_get tool for real-time lookups. |
CREATE_USER_ID, create date range, and whether SHOULD_EVENT_EDGE_EX is still true.gather-edgeex-enrollment-info — Fetches enrollment, section, customization, and gradebook data via EdgeEx MCP tools. Use when you have a section and need to understand enrollments against it.search-opensearch-logs — IL-specific OpenSearch query guidance. Step 2 of the investigation playbook uses this to confirm EdgeEx never created the section.npx claudepluginhub imaginelearning/dp-claude-plugin --plugin cwCreates, edits, and optimizes skills for Claude Code, including drafting, evaluating with test prompts, iterating on performance, and improving skill descriptions for better triggering accuracy.