heritage-edgeex-section-drift

Heritage ↔ EdgeEx Section Drift Investigation

Overview

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.

When to use / not to use

• Use when: the user is investigating 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.
• Skip when: the section exists in EdgeEx (use 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).

How sections flow between the two systems

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.

EdgeEx section-api invariants

Rely on these when ruling out hypotheses:

• No hard delete. No @Delete route exists. Sections can only be ARCHIVED.
• GET returns archived sections. A 404 from GET /section/{id} means the row genuinely never existed in DynamoDB.
• No DynamoDB TTL. Rows persist indefinitely.

Combined: a 404 on section_get means EdgeEx has never had this section.

Snowflake tables

Heritage — WELD_NORTH_PROD.EDGE_LMS.COURSE

Heritage 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."

EdgeEx mirror — WELD_NORTH_PROD.LANDING_ZONE.OTK_CWNG_SECTION

A 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:

• Includes both ACTIVE and ARCHIVED sections.
• Mirror lags by hours. Sections created today may not be there yet. Exclude recent Heritage rows: AND CREATE_DATE < DATEADD('day', -1, CURRENT_TIMESTAMP()).

Query templates

Single-section Heritage lookup

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>';

Orphan detection (Heritage has it, EdgeEx doesn't)

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'.

Orphan count (cheap sanity check)

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;

Bucket orphans by code path

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;

Investigation playbook

When you have a sectionId from a failing enrollment event:

Step 1 — Check EdgeEx (MCP tool, no Snowflake needed)

Call section_get with the section ID.

• 404 → EdgeEx has never had it. Continue to step 2.
• Returns with status: ARCHIVED → EdgeEx archived it. Heritage may not know — different issue from this playbook.
• Returns with status: ACTIVE → EdgeEx has it. The failing event is a different problem.

Step 2 — Confirm EdgeEx never created it (OpenSearch)

Search logs for the section creation event:

• Index: app-logs-courseware-prod-cluster-*
• Filters: namespace.keyword = edgeex-prod, container_name.keyword = edgeex-section-api
• Query: match_phrase: "Created section: ...sectionId=<UUID>"
• Window: 90+ days

Zero hits confirms EdgeEx never persisted this section.

Step 3 — Confirm Heritage has it (Snowflake)

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_EX
• CREATE_USER_ID — who/what created it
• CREATE_DATE — when
• INACTIVE_DATE — null means Heritage still considers it active
• NAME — check for patterns like - reporting copy (known PUP bug)

Step 4 — Decide where the bug lives

• Heritage has it, EdgeEx doesn't → Heritage created the section locally without going through the EdgeEx API. Hand off to the Heritage team with: section ID, name, CREATE_USER_ID, and create date. They own debugging which code path produced it.
• Heritage doesn't have it either → Very unusual. Both sides are missing it. Check if the Caliper event payload itself is malformed or referencing a stale ID.

Step 5 — Sweep for siblings (optional, if systemic)

If this looks like more than a one-off, ask the user to run:

1. The orphan count query — how many total?
2. The bucket by code path query — do they share a 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.

Known failure modes

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.

Common mistakes

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.

Workflow when drafting queries for a user

1. Confirm the investigation scope: single section ID, or a sweep? What date range?
2. Draft the appropriate SQL from the templates above.
3. Show the SQL and ask the user to run it in Snowflake.
4. Parse results to determine next steps per the investigation playbook.
5. If handing off to the Heritage team, compile: section ID(s), name(s), CREATE_USER_ID, create date range, and whether SHOULD_EVENT_EDGE_EX is still true.

Related Skills

• 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.