TimeBack Analytics Documentation

Overview

This document provides a comprehensive catalog of all analytical views available in the TimeBack platform's AWS QuickSight environment. Each view is designed to support specific analytical use cases while maintaining consistency, performance, and data governance standards across the analytics environment.

Data Sources:

Staging: analytics_staging schema
Production: analytics_production schema

Available Views

The following views are available in the TimeBack QuickSight environment, as part of the above data source and schema. Each view is documented with its purpose, available fields, and recommended join keys for cross-view analysis. Under the subtabs of this section, you will find the available views. You can link them together as needed in your own data sets.

Academic Sessions

Purpose

Calendar structure of the school year: one row per OneRoster academic session, which is a school year, term, semester, grading period, or assessment window. Use it to answer "what are the grading periods this school year", "when does the term end", "which term is active on a given date", and to give any date-bounded question its temporal anchors (filter another dataset's dates against a session's start_date/end_date). Sessions form a hierarchy through parent_id: a grading period's parent is its term, a term's parent is its school year; the type column says which level a row is. A 'term' row is NOT necessarily a teaching window: administrative and app-created rows span a full calendar year, so gate any date-in-session question on span_kind = 'instructional'. Without that gate an out-of-term date such as mid-July is answered only by container rows and summer reads as term instruction. This is a platform-wide reference view with no org scoping (like the subjects dataset): sessions are not stored with an organization on the row, so every session is visible to every consumer. Data coverage: real sessions land here only for organizations whose Beyond roster has been imported (the per-org data-import sync, #2280); a single seeded platform-default school year ("SY 2025-2026") exists as a fallback for orgs without their own calendar. Auto-provisioned placeholder calendars from Beyond are excluded at import.

Fields

Field Name	Data Type	Description
id	String	Genesis: copied (from Beyond org sync, API writers, or the platform-default seed). Identifier of the academic session, a UUID from one_roster_academic_session.id. This is the grain: one row per session. It is the join target for academic_session_id / grading_period_id columns on other datasets, and is also the value another row's parent_id points at. Never null. Example: 'd4e5f6a7-8b9c-4d0e-1f2a-3b4c5d6e7f80'.
title	String	Genesis: copied (from Beyond org sync, API writers, or the platform-default seed). Human-readable name of the session, from one_roster_academic_session.title. For a school year this is typically a year label (e.g. 'SY 2025-2026'); for a term or grading period it is the period name (e.g. 'Fall 2025', 'Quarter 1'). Never null. Example: 'Quarter 1'.
type	String	Genesis: copied (from Beyond org sync, API writers, or the platform-default seed). Kind of session, from one_roster_academic_session.type. Allowable values: 'schoolYear' (the top-level calendar, parent_id is null), 'semester', 'term', 'gradingPeriod', and 'ext ' (a vendor-extension window for external tests such as MAP/NWEA). Filter on this to select a level of the hierarchy (e.g. type = 'gradingPeriod' for grading periods). Never null. Example: 'gradingPeriod'.
start_date	Date	Genesis: copied (from Beyond org sync, API writers, or the platform-default seed). First calendar day the session covers, from one_roster_academic_session.start_date, a date in 'YYYY-MM-DD' format (no time component). Use it with end_date to test whether a date falls inside the session or to order sessions on a timeline. Never null; a source that omitted a date is stored as '1970-01-01', so treat that epoch value as "unknown", not a real start. Example: '2025-08-18'.
end_date	Date	Genesis: copied (from Beyond org sync, API writers, or the platform-default seed). Last calendar day the session covers, from one_roster_academic_session.end_date, a date in 'YYYY-MM-DD' format (no time component). Use it with start_date to bound the session; "when does the term end" reads this column. Never null; a source that omitted a date is stored as '1970-01-01' (treat as "unknown"). Example: '2025-10-17'.
span_days	Integer	Genesis: computed (end_date minus start_date) from copied inputs (session dates). Length of the session in days, computed as end_date - start_date. Never null; 0 when a session starts and ends on the same day. Use it to tell a real instructional window from a full-calendar container: campus terms run about 334 days (Aug-1 to Jun-30) and the platform default about 296 (Aug-13 to Jun-05), while administrative and app-created containers run 364 (Aug-1 to Jul-31, or Jan-1 to Dec-31). Prefer the pre-classified span_kind unless you need the raw number. Example: 334.
span_kind	String	Genesis: computed (span_days classified at the full-calendar-day boundary) from copied inputs (session dates). Whether the row is a real instructional window or a full-calendar container span, derived from span_days at a 360-day boundary. Allowable values are 'instructional' (shorter than 360 days — a real teaching window) and 'full_calendar_span' (360 days or longer — an administrative or app-created container that is NOT a teaching window). Never null. Gate date-in-session questions on 'instructional': without it an out-of-term date such as mid-July is answered only by container rows, so summer reads as term instruction. Derived from the row's own dates, not a curated marker, so a genuine year-long programme is classified as a container. Example: 'instructional'.
school_year	String	Genesis: copied (from Beyond org sync, API writers, or the platform-default seed). School year the session belongs to, from one_roster_academic_session.school_year, a four-digit year label stored as text (NOT a foreign key). It is a scalar tag for grouping sessions by year, so "grading periods for this school year" filters on this column. Never null. Example: '2026'.
parent_id	String	Genesis: copied (from Beyond org sync, API writers, or the platform-default seed). Identifier of the parent session in the calendar hierarchy, a UUID from one_roster_academic_session.parent_id that self-references this dataset's id. A grading period points at its term, a term at its school year. Null for top-level sessions (typically school years) that have no parent. Self-join id = parent_id to resolve the parent, or read parent_title for its name. Example: 'a1b2c3d4-5e6f-4a7b-8c9d-0e1f2a3b4c5d'.
parent_title	String	Genesis: copied (from the parent academic session record). Title of the parent session, resolved by joining parent_id back to this dataset (one_roster_academic_session.title of the parent row). Saves a self-join when you only need the parent's name (e.g. showing a grading period alongside its term). Null when parent_id is null (top-level sessions) or the parent row is absent. Example: 'Fall 2025'.

Join Keys

id: Primary key; join target for any dataset carrying an academic_session_id (e.g. Assessment Results) or a grading_period_id
parent_id: Self-join to this dataset's id to walk up the hierarchy (grading period -> term -> school year)

Aggregated Session Attempts

Purpose

Provides session-level aggregated metrics for student attempts on learning resources. This dataset summarizes student performance within each session by counting total questions answered, questions answered with full marks, the average score fraction, and average attempt duration. It enables analysis of session-level engagement patterns and performance trends across students and time periods. One row per session+student. Because one attempt can resolve to more than one session, an attempt linked to several sessions is counted in each of those sessions' rows, so summing these counts across sessions can exceed the number of distinct attempts — treat the counts as per-session figures, not as a basis for a student total. It has no subject, grade, or learning-app dimension; it summarizes question attempts per session and student only.

Fields

Field Name	Data Type	Description
session_id	String	Genesis: captured (session id minted at capture). Identifier of the learning session being summarized. An opaque identifier string, not a uuid: production values are a mix of 'https://timeback.com/sessions/ ' URLs and bare UUID strings, so never cast it to uuid. Never null; together with student_id it forms the grain, one row per session+student. Join with the Sessions dataset on session_id. Example: 'https://timeback.com/sessions/2026-07-23T11-02-18-750Z-ufduv99q0t8'.
student_id	String	Genesis: copied (minted by the OneRoster writers). Identifier of the student whose attempts in the session are summarized, a UUID. Never null. Join with the Students dataset on student_id. Example: 'a1b2c3d4-5e6f-4a7b-8c9d-0e1f2a3b4c5d'.
date	Date	Genesis: computed (day of the session's earliest captured attempt start). Calendar date of the session's first attempt, derived from the earliest attempt start time. Null only when no attempt in the session has a start timestamp. Example: '2026-03-14'.
total_questions_answered	Integer	Genesis: computed (count of scored first attempts) from judged inputs (learning-app grading). Count of questions the student answered in the session where a score fraction could be computed (score given divided by a positive max score). Excludes attempts with no score and attempts whose max score is zero, matching the score_fraction null rule on the attempts dataset. Never null; zero when no scorable question was answered, and always greater than or equal to total_questions_full_marks. Example: 12.
total_questions_full_marks	Integer	Genesis: computed (count of full-marks first attempts) from judged inputs (learning-app grading). Count of questions the student answered with full marks in the session (the score given equals the max score) among scorable attempts only. Excludes max-score-zero attempts even when score given equals max score, since those have no score fraction. Never null; zero when none reached full marks, and never exceeds total_questions_answered. Partial-credit answers are not counted here — use avg_score_fraction for partial performance. Example: 9.
avg_score_fraction	Decimal	Genesis: computed (average of scoreGiven / maxScore) from judged inputs (learning-app grading). Average score fraction across the session's scorable question attempts, each attempt's score given divided by its max score, rounded to four decimals and ranging from 0 to 1. Captures partial-credit performance that the full-marks count hides. Uses the same scorable set as total_questions_answered (score_fraction is not null). Null when the session has no scorable question attempt. Example: 0.8125.
avg_duration_sec	Decimal	Genesis: computed (average attempt duration from captured attempt events). Average attempt duration in whole seconds across the session's question attempts, rounded; uses client-provided duration when present, otherwise computed from start and end times. Null when no attempt has a usable duration. Example: 38.
org_ids	String	Genesis: computed (caller-scope intersection of the student's rostered orgs). Array of UUIDs of the organizations this row is visible under: the student's organizations (from one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the student's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Used for filtering rather than as a strict join key. Example: ['2b1a0c9d-8e7f-4a6b-b5c4-d3e2f1a09b8c'].

Join Keys

session_id: Join with Sessions dataset to get session details and timing information
student_id: Join with Students dataset to get student details
org_ids: Array of organization IDs the user belongs to; used for org-scoped filtering

App Operational Health

Purpose

Per-app operational health over a rolling 7-day window, one row per LTI application with in-window activity (registered apps with no activity in the window produce no row): student-minutes, distinct students, sessions, XP awarded, the observed sourcing pattern (native sessions vs Beyond facts vs mixed), and the share of sessions that earned zero XP. Rank by student_minutes to surface the apps that move the needle without writing a bespoke aggregation — but classify the app names first: rows are not all student-facing learning apps (dashboards, assessment surfaces, and internal LTI applications register here too and their time is attributed the same way), and no classification column is served, so an unclassified ranking can be led by a non-learning surface. Say which rows you treated as learning apps. Built on the daily engagement rollup (student_daily_engagement_rollup) plus caliper_session and tb_xp_ledger; no live join of the event views is required for the engagement metrics.

When to use: use this for at-a-glance per-app operational ranking and health (minutes in, XP out, students, sessions, sourcing, zero-XP rate) over the last 7 days. For arbitrary date windows or per-day detail, aggregate student_daily_engagement instead. Do NOT re-derive Beyond-vs-ours XP drift here — that lives in the Beyond reconciliation instrument (S3), not in this view.

Units and keying: student_minutes is whole minutes from round(sum(clamped active_seconds) / 60), where each rollup row is clamped to [0, 86400] before summing (trap 19). xp_awarded is the signed ledger/facts sum already merged in the rollup. zero_xp_session_rate is a fraction in [0, 1]. Sessions exclude revoked insights_session rows. Activity with no matching lti_application row is excluded.

Fields

Field Name	Data Type	Description
learning_app_id	String	Genesis: captured (app id from LTI application registration by staff/API writers). Identifier of the learning application this operational row is for, a UUID string matching learning_apps.id (from the rollup learning_app_id / caliper_session.client_id / lti_application.id). Never null; one row per app with in-window activity. Join with the Learning Apps dataset on id. Example: '7c9e6679-7425-40de-944b-e07fc1f90ae7'.
app_name	String	Genesis: captured (from LTI application registration by staff/API writers). Display name of the application, from lti_application.name. Never null for rows that appear in this view (the view inner-joins lti_application). The name is the only signal for telling a student-facing learning app from a dashboard, assessment surface, or internal application, since no classification column is served — classify it before ranking or aggregating by app. Example: 'Zearn'.
student_minutes	Integer	Genesis: computed (7-day clamped active-seconds sum) from captured native sessions or copied Beyond facts. Total active learning time for this app over the last 7 days, in whole minutes, computed as round(sum(GREATEST(0, LEAST(active_seconds, 86400))) / 60.0) from student_daily_engagement_rollup. Each rollup row is clamped to [0, 86400] before summing so known negatives and overflows on the rollup cannot dominate ranking (trap 19). Never null; 0 when the app appears only from sessions with no rollup engagement. Sort by this column to rank needle-movers. Example: 5400.
students	Integer	Genesis: computed (7-day distinct-student count) from captured native sessions or copied Beyond facts. Count of distinct students with engagement rollup activity on this app over the last 7 days (native sessions and/or Beyond facts). Never null; 0 when the app appears only from sessions with no rollup rows in scope. Example: 128.
sessions	Integer	Genesis: computed (7-day count of non-revoked sessions) from captured Caliper session events. Count of non-revoked caliper_session rows for this app started in the last 7 days (revoked insights_session rows excluded). Never null; 0 when the app has engagement rollup activity but no sessions in the window. Example: 412.
xp_awarded	Decimal	Genesis: computed (7-day rollup xp sum) from captured native XP ledger or copied Beyond facts. Total experience points awarded for this app over the last 7 days, summed from student_daily_engagement_rollup.xp (native ledger XP plus Beyond-facts XP where native is absent). A signed decimal: usually positive, can be 0, and may be negative where the ledger records a correction. Never null; 0 when no XP was recorded. Example: 15250.5.
sourcing	String	Genesis: computed (classification of rollup source legs present in the window). Observed sourcing pattern for this app's rollup rows in the window: 'native' when only source='sessions' rows contribute, 'beyond_facts' when only source='facts' rows contribute, and 'mixed' when both are present. Never null; defaults to 'native' when the app appears only from sessions with no rollup rows. This is operational provenance, not the XP design registry. Example: 'mixed'.
zero_xp_session_rate	Decimal	Genesis: computed (share of 7-day sessions with zero linked XP) from captured session and ledger events. Share of this app's in-window sessions that earned zero XP, as a fraction in [0, 1] rounded to 4 decimal places. A session counts as zero-XP when the sum of tb_xp_ledger values linked via caliper_event.session_id is 0 or absent. Never null; 0 when the app has no sessions in the window. Example: 0.2500.
org_ids	String	Genesis: computed (contributing student roster orgs intersected with caller scope) from copied OneRoster roles. Array of UUIDs of the organizations this app-health row is visible under: the union of contributing students' organizations (from one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the contributors' full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Used for filtering rather than as a strict join key. Example: ['2b1a0c9d-8e7f-4a6b-b5c4-d3e2f1a09b8c'].

Join Keys

learning_app_id: Join with the Learning Apps dataset
org_ids: Array of organization IDs for org-scoped filtering

Assessment Results

Purpose

Academic test scores recorded in the gradebook, one row per gradebook assessment result. This is where imported and internally-produced test outcomes land: externally-provided assessments (MAP, STAAR, other provider results imported via Beyond) and internal AI-graded quizzes and test-outs. Use it for "what were this student's test scores" and score-trend questions. This is the dataset for gradebook OneRoster assessmentResults (results tied to an assessment line item); it is NOT plain class-lineItem results (per-class gradebook entries), which are not covered here. For practice/telemetry accuracy on third-party apps that emit no gradebook data (e.g. Math Academy, Edia) use the Attempts dataset instead; the two sources have no shared key and must never be summed together. To tell external-provider tests (STAAR/MAP) apart from internal quizzes/test-outs, use provider (trust axis) and assessment_type (family axis); trust assessment_type structurally where provider_source='captured' and treat inferred families as best-effort. Do not use external_identifier for that distinction.

Fields

Field Name	Data Type	Description
id	String	Genesis: captured (result id minted at gradebook write). Identifier of this assessment result, a UUID from gradebook_assessment_result.id. This is the grain: one row per result. It is also the target of another row's parent_id, so a rubric or question-level result chain is walkable by self-joining parent_id to id. Never null. Example: 'a1b2c3d4-5e6f-4a7b-8c9d-0e1f2a3b4c5d'.
student_id	String	Genesis: copied (minted by the OneRoster writers). Identifier of the student the result belongs to, a UUID from gradebook_assessment_result.student_id (OneRosterUser.id). Never null. Join with the Students dataset on student_id. Example: 'b2c3d4e5-6f7a-4b8c-9d0e-1f2a3b4c5d6e'.
assessment_line_item_id	String	Genesis: copied (from Beyond gradebook) or captured (API and quiz-engine writers, per provider). Identifier of the assessment line item this result scores, a UUID from gradebook_assessment_result.assessment_line_item_id. An assessment line item is the assessment definition (e.g. a specific STAAR or MAP test, or an internal quiz). Never null. Join to the assessment line item on its id; assessment_title carries its title. Example: 'c3d4e5f6-7a8b-4c9d-0e1f-2a3b4c5d6e7f'.
assessment_title	String	Genesis: copied (from Beyond gradebook import) or captured (platform assessment writers). Human-readable title of the assessment (the line item this result is for), from gradebook_assessment_line_item.title. For externally-imported assessments this carries the provider's assessment name (e.g. a STAAR or MAP test name); for internal assessments it is the quiz or test-out title. Null only when the referenced line item is absent (the join misses). Example: 'MAP Growth: Reading (Fall)'.
score	Decimal	Genesis: copied (from Beyond gradebook), captured (API writers), or judged (quiz engine, per provider). Numeric score the student achieved, from gradebook_assessment_result.score, a decimal with two-place precision. Null when the result carries only a non-numeric text_score (e.g. Pass/Fail or a letter grade) or was not scored numerically. Range is assessment-defined and not fixed here (it is not necessarily 0-100). Example: 87.50.
text_score	String	Genesis: copied (from Beyond gradebook), captured (API writers), or judged (quiz engine, per provider). Non-numeric score value, from gradebook_assessment_result.text_score: a letter grade or verbal mark (e.g. 'A+', 'Pass'), or a free-form graded response (Beyond ingests essay excerpts and transcripts here). Null when a numeric score was recorded instead. When both score and text_score are present, they describe the same result in different forms. Example: 'Pass'.
score_percentile	Decimal	Genesis: copied (from Beyond gradebook), captured (API writers), or judged (quiz engine, per provider). Percentile rank of the score, from gradebook_assessment_result.score_percentile, a decimal from 0 to 100 (a value of 90.00 means the score is at or above the 90th percentile). Null when the source did not provide a percentile, which is common for internal assessments. Example: 92.50.
score_status	String	Genesis: copied (from Beyond import) or captured (quiz engine, mastery upsert, API writers). Grading status of the result, from gradebook_assessment_result.score_status. Allowable values: 'exempt', 'fully graded', 'incomplete', 'in progress', 'late', 'missing', 'not submitted', 'ext ', 'partially graded', 'submitted', 'withdrawal'. A finalized numeric or text score is normally present once 'fully graded'. Never null. Example: 'fully graded'.
provider	String	Genesis: captured (at write time) or computed (heuristic backfill, per provider_source). Provenance of the result, from gradebook_assessment_result.provider. Values: 'beyond' (replicated from Beyond gradebook import, including MAP and STAAR), 'api' (external OneRoster writer), 'quiz_engine' (platform-judged quiz or test-out), 'unknown' (heuristic backfill could not classify). This is the trust axis for per-provider filtering. Never null. Example: 'beyond'.
assessment_type	String	Genesis: captured (at write time) or computed (title and reportUrl heuristics, per provider_source). Assessment family, from gradebook_assessment_result.assessment_type. Values: 'map', 'staar', 'quiz', 'test_out'; null when undetermined. Structural replacement for assessment_title regex filters (e.g. assessment_type='staar' instead of ILIKE '%STAAR%'). Authoritative where provider_source='captured'; best-effort (title/reportUrl derived) where provider_source='inferred'. Example: 'staar'.
provider_source	String	Genesis: computed (platform provenance bookkeeping at write or backfill). Whether provider/assessment_type were captured at write time or inferred from heuristics, from gradebook_assessment_result.provider_source. Values: 'captured' (writer knew the family structurally) or 'inferred' (Beyond import or historical backfill derived family from title/reportUrl). Trust assessment_type structurally only when provider_source='captured'. Never null. Example: 'inferred'.
score_date	Date	Genesis: copied (from Beyond gradebook) or captured (API and quiz-engine writers, per provider). Date the score was assigned, from gradebook_assessment_result.score_date, a calendar date in 'YYYY-MM-DD' format (no time component). Use it to place a result on a timeline or filter to a school year or a rolling window (e.g. the last 12 months). Never null. Example: '2026-05-14'.
academic_session_id	String	Genesis: copied (from Beyond gradebook) or captured (API and quiz-engine writers, per provider). Identifier of the OneRoster academic session (a term or school year) the result falls in, a UUID from gradebook_assessment_result.academic_session_id. Null when the source did not associate the result with a session. Join with the academic session to resolve the term or year. Example: 'd4e5f6a7-8b9c-4d0e-1f2a-3b4c5d6e7f80'.
parent_id	String	Genesis: copied (from Beyond gradebook) or captured (API and quiz-engine writers, per provider). Identifier of the parent assessment result, a UUID from gradebook_assessment_result.parent_id, used to model rubric or question-level result chains (a child result rolls up to its parent). Null for top-level results with no parent. Self-join to this dataset's id to walk the chain. Example: 'e5f6a7b8-9c0d-4e1f-2a3b-4c5d6e7f8091'.
external_identifier	String	Genesis: copied (Beyond-side record id preserved for reverse mapping) or captured (minted here for rows with no Beyond origin). Original Beyond-side identifier of this result, read from gradebook_assessment_result.metadata ->> 'originalIdentifier'. TimeBack preserves it for reverse ID mapping: when a record originates from or is written through the Beyond convergence layer (Beyond-compat writes, the proxy async relay, or the content-import pipeline), the record's Beyond-side id is stored here so TimeBack can substitute it back into Beyond-compat responses and route writes back to Beyond. Non-null means the row carries a preserved Beyond-side id (it was created through or is tracked by the Beyond convergence/import path); null means no Beyond-side id was stored. It is an opaque string with no fixed format (frequently a UUID; in at least one documented case it is the row's own generated UUID). Do NOT use it to distinguish external-provider tests (STAAR/MAP) from internal AI-graded quizzes or test-outs: it is present on both. Use provider and assessment_type for that distinction instead. Example: 'a1b2c3d4-5e6f-4a7b-8c9d-0e1f2a3b4c5d'.
org_ids	String	Genesis: computed (role-membership intersection with caller scope) from copied inputs (OneRoster roles). Array of UUIDs of the organizations this row is visible under: the student's organizations (from one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the student's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Used for filtering rather than as a strict join key. Example: ['2b1a0c9d-8e7f-4a6b-b5c4-d3e2f1a09b8c'].

Join Keys

student_id: Join with the Students dataset to get student demographic and profile information
assessment_line_item_id: Join with the assessment line item to get the assessment's definition
academic_session_id: Join with the academic session (term or school year) the result falls in
parent_id: Self-join to this dataset's id to walk rubric or question-level result chains
org_ids: Array of organization IDs the student belongs to; used for org-scoped filtering

Attempts

Purpose

Provides detailed information about student attempts on learning resources, including timing, duration, correctness data, and session association. This dataset combines engagement metrics (for lessons and curriculum items) with performance results (for questions), enabling comprehensive analysis of both time-on-task and answer accuracy within the context of learning sessions. Only first attempts are included (repeat attempts are excluded). The grain is one row per (first attempt, session) pair, NOT one row per attempt: an attempt can resolve to more than one session, so an attempt linked to several sessions appears once per session. Counting rows therefore over-counts attempts and summing duration_sec or score_fraction double-counts — use COUNT(DISTINCT (student_id, resource_id)) for an attempt count, or aggregate after narrowing to one session. This dataset has no subject, grade, or learning-app column of its own: a student's placed grade lives in Placement Results, and subject is not available directly here.

Fields

Field Name	Data Type	Description
student_id	String	Genesis: copied (minted by the OneRoster writers). Identifier of the student who made the attempt, a UUID from caliper_attempt.assignee_id. Never null. Join with the Students dataset on student_id. Example: 'a1b2c3d4-5e6f-4a7b-8c9d-0e1f2a3b4c5d'.
resource_id	String	Genesis: captured (content id from the Caliper content graph). Identifier of the learning resource the attempt was on, a UUID from caliper_attempt.assignable_id. Never null. The resource may be a lesson, test, question, or other assignable content, so join the matching content dataset (Questions, Tests, Lessons, ...) by type; this dataset has no subject or grade column. Example: '7f3a2b1c-9d8e-4f6a-b5c4-d3e2f1a09b8c'.
session_id	String	Genesis: computed (attempt-to-session resolution through related Caliper events). Identifier of the learning session the attempt belongs to, resolved through the related Caliper events. An opaque identifier string, not a uuid: production values are a mix of 'https://timeback.com/sessions/ ' URLs and bare UUID strings, so never cast it to uuid. Null when no session could be associated with the attempt. One attempt can resolve to several sessions, in which case it appears once per session here — this column is part of the grain, so de-duplicate before counting attempts. Join with the Sessions dataset on session_id. Example: 'https://timeback.com/sessions/2026-07-23T11-02-18-750Z-ufduv99q0t8'.
date	Date	Genesis: computed (calendar date of the captured attempt start). Calendar date on which the attempt started, derived as date(start_time). The source timestamp carries no time zone, so no conversion happens and this is the UTC calendar date, not a local school day. Null only when the attempt has no start timestamp. Useful for day-level grouping of activity. Example: '2026-03-14'.
start_time	DateTime	Genesis: captured (from Caliper attempt events). Timestamp when the attempt started (when the student opened the resource). Null only when the source did not record a start; pairs with end_time to bound the attempt. Example: '2026-03-14T09:15:30Z'.
end_time	DateTime	Genesis: captured (from Caliper attempt events). Timestamp when the attempt ended (when the student finished or it closed). Null when the attempt has no recorded end, for example it is still open or was abandoned. Example: '2026-03-14T09:23:10Z'.
duration_sec	Decimal	Genesis: computed (client duration or end minus start, from captured attempt events). Attempt duration in whole seconds: the client-provided duration when present, otherwise the difference between end_time and start_time. Null when neither a duration nor both timestamps are available. Example: 460.
is_full_marks	Boolean	Genesis: computed (scoreGiven = maxScore) from judged inputs (learning-app grading). Whether the attempt earned full marks: true when the score given equals the max score, false otherwise. This is a strict full-marks flag, not partial correctness — a partially correct multi-point answer (0 < score < max) is false; read score_fraction for partial credit. Derived from caliper_score as scoreGiven = maxScore. Null for attempts with no score, such as lesson or curriculum-item engagement rather than a graded question. Example: true.
score_fraction	Decimal	Genesis: computed (scoreGiven / maxScore) from judged inputs (learning-app grading). Fraction of the maximum score the attempt earned, the score given divided by the max score, rounded to four decimals and ranging from 0 to 1. Captures the partial credit that the binary is_full_marks flag hides. Derived from caliper_score as scoreGiven / maxScore. Null for attempts with no score, or when the max score is zero. Example: 0.6667.
org_ids	String	Genesis: computed (caller-scope intersection of the student's rostered orgs). Array of UUIDs of the organizations this row is visible under: the student's organizations (from one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the student's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Used for filtering rather than as a strict join key. Example: ['2b1a0c9d-8e7f-4a6b-b5c4-d3e2f1a09b8c'].

Join Keys

student_id: Join with Students dataset to get student details
resource_id: Join with Questions, Tests, Lessons, or other content datasets to get resource details (type-specific joining may be needed)
session_id: Join with Sessions dataset to get session details and timing information
org_ids: Array of organization IDs the user belongs to; used for org-scoped filtering

Case Courses

Purpose

Provides CASE (Competency and Academic Standards Exchange) framework course data. Each record represents a course defined in the CASE framework, sourced from cf_item rows classified as Course type, enabling analysis of CASE-backed curriculum structure and subject alignment.

Grain caveat: this is intended as one row per CASE course, but the subject link is inlined rather than bridged, so a course item mapped to more than one CASE subject yields one row per subject. De-duplicate on id before counting courses.

Fields

Field Name	Data Type	Description
id	String	Genesis: copied (from CASE hierarchy imports). Identifier of the CASE course, from cf_item.identifier (the CASE item identifier, a UUID) for cf_item rows of Course type. Never null. Intended as the grain, but not unique in practice: the inlined cf_item_subject join is many-to-many, so a course mapped to several CASE subjects repeats here once per subject — de-duplicate before counting. Join with curriculum-related datasets on this id. Example: 'd4e5f6a7-8b9c-4d0e-1f2a-3b4c5d6e7f80'.
title	String	Genesis: copied (from CASE hierarchy imports). Full statement of the CASE course, from cf_item.full_statement: the human-readable course description recorded in the CASE framework. Never null. Example: 'Grade 8 Mathematics'.
case_subject_id	String	Genesis: copied (from CASE hierarchy imports). Identifier of the associated CASE subject, from cf_item_subject.subject_id resolved via a left join on the course item. Null when the course has no subject mapping. Join with the Case Subjects dataset on case_subject_id. Example: 'e5f6a7b8-9c0d-4e1f-2a3b-4c5d6e7f8091'.

Join Keys

id: Primary key for joining with curriculum-related datasets
case_subject_id: Join with Case Subjects dataset to get subject details

Case Subjects

Purpose

Reference (dimension) dataset of academic subjects defined in the CASE (Competency and Academic Standards Exchange) framework: one row per subject with its title, hierarchy code, and optional description. Use it to label and resolve a subject id to its name for placement, curriculum-alignment, and standards-based features. This is the canonical list of subjects, not a record of which subject a student takes: for a student's subject enrollment use Course Enrollments (its subject_ids array) or the curriculum item a mastery record points to; this dataset has no student, grade, or date columns.

Fields

Field Name	Data Type	Description
id	String	Genesis: copied (from CASE hierarchy imports). Identifier of the CASE subject, a UUID from cf_subject.identifier. Never null; it is the primary key of this reference dataset, one row per subject. Other datasets reference it as their CASE subject id (e.g. Course Enrollments.subject_ids). Example: '9c8b7a6d-5e4f-4a3b-8c2d-1e0f9a8b7c6d'.
title	String	Genesis: copied (from CASE hierarchy imports). Human-readable subject name, from cf_subject.title, suitable for display and grouping. Never null. Example: 'Mathematics' (or 'Reading', 'Science').
hierarchy_code	String	Genesis: copied (from CASE hierarchy imports). Publisher-designated code marking the subject's position within the CASE subject hierarchy, from cf_subject.hierarchy_code. A free-form publisher code rather than a fixed vocabulary. Never null. Example: a publisher code such as '1' or 'MATH'.
description	String	Genesis: copied (from CASE hierarchy imports). Optional human-readable description of the subject, from cf_subject.description. Null when the publisher supplied no description. Example: 'Mathematics, including number sense, algebra, geometry, and data analysis.'

Join Keys

id: Primary key for joining with placement results, Course Enrollments (subject_ids), and other CASE-framework datasets

Cheating Insights

Purpose

Provides detailed cheating insight events for learning sessions, capturing individual confirmed cheating violations with their type, timing, duration, and the per-instance description shown in the Vault. Only includes external-facing cheating insight types where is_visible is true. Insight types that require review (per insights_config) are only included if they have a TruePositive review result. Each record represents a single cheating event (e.g., Cheating: Help From Another Person, Cheating: Unauthorized App Use, Cheating: Unauthorized Device Use) identified during a session.

When to use: this view is per-event grain (one row per individual cheating incident), so use it for reading a violation's type, timing, and description. For totals, rankings, or per-student or per-session sums of cheating time, use sessions_cheating instead: it is the per-session aggregate with overlapping events already merged and is far cheaper to aggregate.

Fields

Field Name	Data Type	Description
session_id	String	Genesis: captured (session id minted at capture). Identifier of the session this cheating event occurred in, from insights_session.caliper_session_id. An opaque identifier string, not a uuid: production values are a mix of 'https://timeback.com/sessions/ ' URLs and bare UUID strings, so never cast it to uuid. Never null. Joins to sessions.id. Cheating insights are NOT restricted to proctored sessions — they fire on ordinary learning sessions too, so never gate them on is_proctored. Example: 'https://timeback.com/sessions/2026-07-23T11-02-18-750Z-ufduv99q0t8'.
insight_type	String	Genesis: judged (Vision cheating detection; review-gated types human-confirmed). Slug identifying the kind of cheating event, from insights_type.slug (insight types in the Cheating category with visibility = 'external'). Never null. Allowable values are CheatingHelpFromAnotherPerson, CheatingUnauthorizedAppUse, CheatingUnauthorizedDeviceUse. New cheating types may be added without code changes. Example: 'CheatingUnauthorizedAppUse'.
started_at_time	DateTime	Genesis: judged (span boundaries set by Vision detection, with merge gaps). Timestamp the cheating event started, in UTC ISO-8601 format, from insights_insight.started_at_time. Never null. Always ≤ ended_at_time. Use start_time (identical values). Example: '2026-06-17T09:55:00.000Z'.
start_time	DateTime	Genesis: judged (span boundaries set by Vision detection, with merge gaps). Timestamp the cheating event started, in UTC ISO-8601 format, from insights_insight.started_at_time. Never null. Always ≤ end_time. Prefer this column (aligned with the sessions dataset); started_at_time carries identical values. Example: '2026-06-17T09:55:00.000Z'.
ended_at_time	DateTime	Genesis: judged (span boundaries set by Vision detection, with merge gaps). Timestamp the cheating event ended, in UTC ISO-8601 format, from insights_insight.ended_at_time. Never null. Always ≥ started_at_time. Use end_time (identical values). Example: '2026-06-17T09:55:15.000Z'.
end_time	DateTime	Genesis: judged (span boundaries set by Vision detection, with merge gaps). Timestamp the cheating event ended, in UTC ISO-8601 format, from insights_insight.ended_at_time. Never null. Always ≥ start_time. Prefer this column (aligned with the sessions dataset); ended_at_time carries identical values. Example: '2026-06-17T09:55:15.000Z'.
duration_sec	Decimal	Genesis: computed (whole seconds between the insight timestamps) from judged inputs (Vision cheating detection). Duration of the cheating event in whole seconds, computed as round(extract(epoch from (ended_at_time - started_at_time))). Never null. Example: 15.
description	String	Genesis: copied (from the insight reason text, the Vault-facing description). Per-instance contextual description of the cheating event, sourced from insights_insight.reason — the same human-readable text shown in the Vault for this insight. Never null. Example: 'Background app usage detected'.
org_ids	String	Genesis: computed (roster org membership intersected with caller scope) from copied OneRoster roles. Array of UUIDs of the organizations this row is visible under: the session's student's organizations (from public.one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the student's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Each element joins to an organization id. Example: ['1a2b3c4d-1111-2222-3333-444455556666'].

Join Keys

session_id: Join with Sessions dataset to get session details, timing, and student information
org_ids: Array of organization IDs the user belongs to; used for org-scoped filtering

Class Enrollments

Purpose

Links students to the classes they are enrolled in (the real OneRoster class enrollments), one row per enrollment, with the enrollment's effective dates so consumers can reconstruct class history over time. Only student-role enrollments are included. Subject and grade are exposed as arrays because the class can carry more than one of each. Use begin_date/end_date to answer point-in-time questions such as "which classes was the student enrolled in on date D".

Fields

Field Name	Data Type	Description
enrollment_id	String	Genesis: copied (minted by the OneRoster writers). Unique identifier of the class enrollment, a UUID from one_roster_enrollment.id. Primary key of this dataset; never null. One row exists per student class enrollment. Example: 'b3f1c2a4-7d8e-4f10-9a2b-6c5d4e3f2a1b'.
student_id	String	Genesis: copied (minted by the OneRoster writers). UUID of the enrolled student, from one_roster_enrollment.user_id. Never null; only student-role enrollments are included. Joins to students.id and to student_schools.student_id. Example: '7c9e6679-7425-40de-944b-e07fc1f90ae7'.
class_id	String	Genesis: copied (minted by the OneRoster writers). UUID of the class the student is enrolled in, from one_roster_enrollment.class_id. Never null. Joins to classes.id. Example: '3f2504e0-4f89-41d3-9a0c-0305e82c3301'.
class_title	String	Genesis: copied (from the linked OneRoster class record). Human-readable title of the class, from one_roster_class.title. Never null. Example: 'Grade 4 Mathematics, Section A'.
course_id	String	Genesis: copied (minted by the OneRoster writers). UUID of the course this class is an offering of, from one_roster_class.course_id. Never null (the source column is NOT NULL with a foreign key). This is a OneRoster course id, a different key space from the Courses dataset, which is the Caliper content graph: joining the two returns no rows and raises no error. Example: 'a1b2c3d4-5e6f-4a7b-8c9d-0e1f2a3b4c5d'.
course_title	String	Genesis: copied (from the linked OneRoster course record). Human-readable title of the course the class belongs to, from one_roster_course.title. Effectively never null: course_id is NOT NULL with a foreign key, so this is null only in the pathological case of a missing course row behind the left join. Example: 'Mathematics, Grade 4'.
school_id	String	Genesis: copied (minted by the OneRoster writers). UUID of the school (organization) the class is held at, from one_roster_class.school_id. Never null (the source column is NOT NULL with a foreign key). Joins to schools.id. Example: '1a2b3c4d-1111-2222-3333-444455556666'.
school_name	String	Genesis: copied (from the linked OneRoster organization record). Human-readable name of the school the class is held at, from one_roster_organization.name. Effectively never null: school_id is NOT NULL with a foreign key, so this is null only in the pathological case of a missing organization row behind the left join. Example: 'Lincoln Elementary School'.
is_primary	Boolean	Genesis: copied (from Beyond data-import and OneRoster API writers). Whether this is the student's primary enrollment in the class, from one_roster_enrollment.primary. True for the primary enrollment, false otherwise. Never null. Example: true.
begin_date	Date	Genesis: copied (from Beyond data-import and OneRoster API writers). Calendar date the enrollment became effective, in YYYY-MM-DD form, from one_roster_enrollment.begin_date. Null when no start date was recorded. Use with end_date for point-in-time membership. Example: '2026-08-15'.
end_date	Date	Genesis: copied (from Beyond data-import and OneRoster API writers). Calendar date the enrollment ended, in YYYY-MM-DD form, from one_roster_enrollment.end_date. Null means the enrollment is ongoing (no end recorded). Example: '2027-06-12'.
status	String	Genesis: copied (from Beyond data-import and OneRoster API writers). Lifecycle status of the enrollment, from one_roster_enrollment.status. Allowable values are 'active', 'inactive', and 'tobedeleted'. Never null. Example: 'active'.
subject_ids	String	Genesis: computed (distinct aggregation of class-subject links) from copied inputs (OneRoster rostering). Array of CASE subject UUIDs the class covers, aggregated distinctly from one_roster_class_subject. Exposed as an array because a class can carry more than one subject. Never null, but may be an empty array when the class has no subjects. Each element joins to case_subjects.id. Example: ['c1d2e3f4-1111-2222-3333-444455556666'].
grade_ids	String	Genesis: computed (distinct aggregation of class-grade links) from copied inputs (OneRoster rostering). Array of grade UUIDs the class targets, aggregated distinctly from one_roster_class_grade.placement_grade_id. Exposed as an array because a class can span more than one grade. Never null, but may be an empty array when the class has no grades. Each element joins to grades.id. Example: ['d4e5f6a7-1111-2222-3333-444455556666'].
org_ids	String	Genesis: computed (intersection of the class school with caller org scope). Array of organization UUIDs this enrollment row is visible under: the enrolled class's school organization id intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organization intersects it are returned; internal/unrestricted callers (the '*' sentinel) see the class's school id. The array is never null and in practice always holds exactly that one school id, since one_roster_class.school_id is NOT NULL; it is the empty array {} only when nothing is in scope. Each element joins to an organization id. Example: ['2b1a0c9d-8e7f-4a6b-b5c4-d3e2f1a09b8c'].

Join Keys

student_id: Join with Students dataset to get student details
class_id: Join with Classes dataset to get class details
course_id: OneRoster course id; stay in the OneRoster chain (Classes, Class Enrollments, Course Enrollments). Do NOT join it to the Courses dataset, which is the Caliper content graph and a disjoint id space — that join returns no rows and raises no error
school_id: Join with Schools dataset to get school details
subject_ids: Array of CASE subject ids (join with Case Subjects via id)
grade_ids: Array of grade ids (join with Grades via id)
org_ids: Array of organization IDs this enrollment is visible under; used for org-scoped filtering

Classes

Purpose

Lists the classes (course sections) on the platform, one row per class, so consumers can resolve a class to its course, school, subject(s) and grade(s) without joining the raw roster tables. A class is a single offering of a course at a school; its subjects and grades are exposed as arrays because a class can carry more than one of each.

Fields

Field Name	Data Type	Description
id	String	Genesis: copied (minted by the OneRoster writers). Unique identifier of the class (course section), a UUID and the primary key of this dataset. Never null. Join class-scoped datasets such as Class Enrollments on class_id. Example: 'c1d2e3f4-a5b6-4c7d-8e9f-0a1b2c3d4e5f'.
title	String	Genesis: copied (from Beyond data-import and OneRoster API writers). Human-readable title of the class as shown to users, from one_roster_class.title. Never null (the source column is NOT NULL); not guaranteed unique across schools or courses. Example: 'Algebra I - Period 3'.
class_code	String	Genesis: copied (from Beyond data-import and OneRoster API writers). External or school-assigned code for the class, carried over from the source roster. Null when no code was provided; useful for reconciling against a school's own systems. Example: 'MATH-101-P3'.
class_type	String	Genesis: copied (from Beyond data-import and OneRoster API writers). Scheduling type of the class. Allowable values are 'homeroom' and 'scheduled'; null when the source did not classify the class. Example: 'scheduled'.
course_id	String	Genesis: copied (minted by the OneRoster writers). Identifier of the course this class is a section of, a UUID from one_roster_class.course_id. Never null (the source column is NOT NULL with a foreign key). This is a OneRoster course id, a different key space from the Courses dataset, which is the Caliper content graph: joining the two returns no rows and raises no error. Example: '7f3a2b1c-9d8e-4f6a-b5c4-d3e2f1a09b8c'.
course_title	String	Genesis: copied (from the linked OneRoster course record). Human-readable title of the course this class belongs to, from one_roster_course.title. Effectively never null: course_id is NOT NULL with a foreign key, so this is null only in the pathological case of a missing course row behind the left join. Example: 'Algebra I'.
school_id	String	Genesis: copied (minted by the OneRoster writers). Identifier of the school (organization) that offers the class, a UUID from one_roster_class.school_id. Never null (the source column is NOT NULL with a foreign key). Join with the Schools dataset on id. Example: '2b1a0c9d-8e7f-4a6b-b5c4-d3e2f1a09b8c'.
school_name	String	Genesis: copied (from the linked OneRoster organization record). Human-readable name of the school that offers the class, from one_roster_organization.name. Effectively never null: school_id is NOT NULL with a foreign key, so this is null only in the pathological case of a missing organization row behind the left join. Example: 'Lincoln Elementary School'.
status	String	Genesis: copied (from Beyond data-import and OneRoster API writers). Lifecycle status of the class from OneRoster. Allowable values are 'active', 'inactive', and 'tobedeleted'; treat 'tobedeleted' as soft-deleted. Never null (defaults to 'active'). Example: 'active'.
subject_ids	String	Genesis: computed (distinct aggregation of class-subject links) from copied inputs (OneRoster rostering). Array of CASE subject identifiers (UUIDs) the class covers, aggregated and de-duplicated with nulls removed. Never null but can be an empty array when no subject is mapped. Join each element with the Case Subjects dataset on id. Example: '{9c8b7a6d-5e4f-4a3b-8c2d-1e0f9a8b7c6d}'.
grade_ids	String	Genesis: computed (distinct aggregation of class-grade links) from copied inputs (OneRoster rostering). Array of grade identifiers (UUIDs) the class targets, aggregated and de-duplicated from the class's placement grades with nulls removed. Never null but can be an empty array when no grade is set. Join each element with the Grades dataset on id. Example: '{3d2c1b0a-9f8e-4d7c-b6a5-948372615f0e}'.
org_ids	String	Genesis: computed (intersection of the class school with caller org scope). Array of organization UUIDs this class row is visible under: the class's school organization id intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organization intersects it are returned; internal/unrestricted callers (the '*' sentinel) see the class's school id. The array is never null and in practice always holds exactly that one school id, since one_roster_class.school_id is NOT NULL; it is the empty array {} only when nothing is in scope. Each element joins to an organization id. Example: ['2b1a0c9d-8e7f-4a6b-b5c4-d3e2f1a09b8c'].

Join Keys

id: Primary key for joining class-scoped datasets (e.g. Class Enrollments via class_id)
course_id: OneRoster course id; stay in the OneRoster chain (Classes, Class Enrollments, Course Enrollments). Do NOT join it to the Courses dataset, which is the Caliper content graph and a disjoint id space — that join returns no rows and raises no error
school_id: Join with Schools dataset to get school details
subject_ids: Array of CASE subject ids (join with Case Subjects via id) the class covers
grade_ids: Array of grade ids (join with Grades via id) the class targets
org_ids: Array of organization IDs the class is visible under; used for org-scoped filtering

Course Enrollments

Purpose

Links students to the courses they are taking, rolled up from class enrollments to the course level (enrollment -> class -> course), one row per student+course. Consumers usually care which courses a student is taking rather than the individual class sections, so the section detail is collapsed. begin_date is the earliest underlying begin and end_date is the latest known end, with a null end_date meaning the course is still ongoing. This dataset exposes only school and subject arrays and has no grade dimension, so there is no grade_ids column here; for grade level use Placement Results (per subject) or the grade arrays on Classes and Class Enrollments. It likewise has no application/learning-app dimension; application activity is captured per session in Students XP and Attempts. For org-scoped filtering, use school_ids (the in-scope schools of the underlying class enrollments); a dedicated org_ids column is omitted on this view because it rolls up across multiple schools per student+course.

Fields

Field Name	Data Type	Description
student_id	String	Genesis: copied (minted by the OneRoster writers). Identifier of the student taking the course, a UUID from one_roster_enrollment.user_id (student-role enrollments only). Never null; together with course_id it forms the grain of this dataset, one row per student+course. Join with the Students dataset on student_id. Example: 'a1b2c3d4-5e6f-4a7b-8c9d-0e1f2a3b4c5d'.
course_id	String	Genesis: copied (minted by the OneRoster writers). Identifier of the course the student is taking, a UUID derived from one_roster_class.course_id rolled up to the course level. Never null (the source column is NOT NULL with a foreign key). This is a OneRoster course id, a different key space from the Courses dataset, which is the Caliper content graph: joining the two returns no rows and raises no error. Example: '7f3a2b1c-9d8e-4f6a-b5c4-d3e2f1a09b8c'.
course_title	String	Genesis: copied (from the linked OneRoster course record). Human-readable title of the course, from one_roster_course.title. Effectively never null: course_id is NOT NULL with a foreign key, so this is null only in the pathological case of a missing course row behind the left join. Example: 'Algebra I'.
school_ids	String	Genesis: computed (distinct schools across rolled-up class enrollments) from copied inputs (OneRoster rostering). Array of school identifiers (UUIDs) at which the student takes any class for this course, aggregated and de-duplicated across the rolled-up class enrollments with nulls removed. Never null and never empty in practice, since one_roster_class.school_id is NOT NULL, so every rolled-up class contributes a school. Join each element with the Schools dataset on id. Example: '{2b1a0c9d-8e7f-4a6b-b5c4-d3e2f1a09b8c}'.
subject_ids	String	Genesis: computed (distinct aggregation of course-subject links) from copied inputs (OneRoster rostering). Array of CASE subject identifiers (UUIDs) covered by the course, aggregated and de-duplicated across the rolled-up classes with nulls removed. Never null but can be an empty array when the course has no subject mapping. Join each element with the Case Subjects dataset on id. Example: '{9c8b7a6d-5e4f-4a3b-8c2d-1e0f9a8b7c6d}'.
begin_date	Date	Genesis: computed (minimum begin date across class enrollments) from copied inputs (OneRoster enrollments). Earliest begin date across the student's underlying class enrollments for this course, i.e. when the student first started the course, as a calendar date. Null when none of the underlying enrollments carry a begin date. Example: '2025-08-25'.
end_date	Date	Genesis: computed (maximum-or-null end date across class enrollments) from copied inputs (OneRoster enrollments). Latest end date across the student's underlying class enrollments for this course, as a calendar date. Null means the course is still ongoing: it is set to null whenever any underlying enrollment has no end date, otherwise it is the maximum end date. Example: '2026-06-12'.
has_primary	Boolean	Genesis: computed (OR of primary flags across class enrollments) from copied inputs (OneRoster enrollments). Whether at least one of the student's underlying class enrollments for this course is marked primary (true) or none is (false). Never null: one_roster_enrollment.primary is NOT NULL, so the OR across enrollments always resolves. Indicates the student's main placement in the course. Example: true.
class_count	Integer	Genesis: computed (count of distinct enrolled classes) from copied inputs (OneRoster enrollments). Number of distinct classes (course sections) the student is enrolled in for this course, counted across the rolled-up enrollments. Never null and always at least 1. Example: 2.

Join Keys

student_id: Join with Students dataset to get student details
course_id: OneRoster course id; stay in the OneRoster chain (Classes, Class Enrollments, Course Enrollments). Do NOT join it to the Courses dataset, which is the Caliper content graph and a disjoint id space — that join returns no rows and raises no error
school_ids: Array of school ids the student takes this course at (join with Schools via id)
subject_ids: Array of CASE subject ids for the course (join with Case Subjects via id)

Courses

Purpose

Provides information about curriculum courses and their relationship to subjects. This dataset enables analysis of course-level organization, subject alignment, and curriculum structure within the educational hierarchy.

Grain caveat: this is intended as one row per course, but the parent link is inlined rather than bridged, so a course carrying more than one 'isPartOf' parent yields one row per parent. De-duplicate on id before counting courses.

Fields

Field Name	Data Type	Description
id	String	Genesis: captured (from learning-app-published Caliper resources). Identifier of the curriculum course, a UUID v4 (or a persistent URL) from caliper_assignable_digital_resource.id (media_type 'curriculum/course'). Never null. Intended as the grain, but not unique in practice: the inlined 'isPartOf' join emits one row per parent, so a course with several parents repeats here — de-duplicate before counting. Units, lessons and tests carry no course id, so join them through the bridge datasets (Unit Courses, Test Parents, Resource Relations) rather than on this id. Example: 'a1b2c3d4-5e6f-4a7b-8c9d-0e1f2a3b4c5d'.
subject_id	String	Genesis: captured (from Caliper isPartOf relationships). Identifier of the course's 'isPartOf' parent in the Caliper content graph (caliper_entity_relationship.destination_id). Named subject_id, but NOT guaranteed to be a subject: the relationship is not filtered by the destination's media type, so a course that is part of a level, topic, or program surfaces that parent's id here and will not be found in the Subjects dataset. Check the parent's type via Resource Relations before relying on it, and expect an inner join to Subjects to silently drop such rows. Distinct from the CASE subject ids carried by placement_results.subject_id and the subject_ids arrays on the enrollment views — those are a different taxonomy and do not overlap this one. Null when the course has no parent recorded. Example: '7f3a2b1c-9d8e-4f6a-b5c4-d3e2f1a09b8c'.
title	String	Genesis: captured (from learning-app-published Caliper resource names). Human-readable title of the course, taken from the Caliper entity's name, falling back to its description, then to the literal 'N/A' when neither is recorded. Never null: the value is 'N/A' exactly when both name and description are missing. Example: 'Algebra I' (or 'N/A').

Join Keys

id: Caliper content-graph course id. Units, lessons and tests do NOT carry a course id, so there is nothing to join on directly — reach them through the bridge datasets (Unit Courses, Test Parents, Resource Relations). This id space is also disjoint from the OneRoster course_id on Classes/Class Enrollments/Course Enrollments
subject_id: the course's 'isPartOf' parent, which is not guaranteed to be a subject — see the column comment before joining Subjects

Curriculum Items

Purpose

Provides curriculum framework item data including courses, sections, topics, levels, and standards. Each record represents a curriculum item with its classification and descriptive information, enabling analysis of curriculum structure and linking mastery data to specific learning outcomes.

Fields

Field Name	Data Type	Description
curriculum_item_id	String	Genesis: copied (from CASE hierarchy imports). Unique identifier of the curriculum item, a UUID from cf_item.identifier (the CASE framework item GUID). Primary key of this dataset; never null. Joins to students_mastery.curriculum_item_id to link mastery awards to the framework item, and to unit_courses/question_tests style bridges where curriculum structure is referenced. Example: '3f2a1b4c-5d6e-4f70-8a9b-0c1d2e3f4a5b'.
curriculum_item_title	String	Genesis: copied (from CASE hierarchy imports). Full statement text of the curriculum item, from cf_item.full_statement — the human-readable description the CASE framework author gave the node (a course name, section name, topic, level, or the standard's full text). Never null. Example: 'Add and subtract within 20 to solve word problems.'.
curriculum_item_code	String	Genesis: copied (from CASE hierarchy imports). Human-readable code for the curriculum item, from cf_item.human_coding_scheme (e.g., a standard code like 'CCSS.MATH.1.OA.1'). This is the item's short label/code — there is no separate "label" or "code" column. Null when the CASE source assigned no coding scheme to the node (common for structural nodes like sections). Example: 'CCSS.MATH.1.OA.1'.
curriculum_item_type	String	Genesis: copied (from CASE hierarchy imports). Type of the curriculum item, from the CASE item type register (cf_item_type.type_code): values such as 'course', 'section', 'topic', 'level', or 'standard', as defined by the imported framework. This is the item's classification — there is no separate "classification" or "category" column. Never null: items without a resolvable type are excluded by the view's inner join. Example: 'standard'.

Join Keys

curriculum_item_id: Join with Students Mastery dataset to link student mastery to curriculum items

Grades

Purpose

Reference (dimension) dataset of grade levels, aligned with CEDS (Common Education Data Standards) education levels: one row per grade level with its code, human-readable label, and sort order. Use it to label and order grades and to resolve a grade id to its name. This is the canonical list of grade levels, not a student's assigned grade, and this dataset has no student, subject, or date columns. "What grade is this student in" has two different answers on this wire and you must decide which the question means before answering: the ROSTER grade the student is enrolled at (Students, via its grades and roster_grade_identifiers columns, which normalize against this reference), or the PLACED grade the platform assessed them into per subject (Placement Results joined to this dataset on grade_id). They routinely disagree — a student can be rostered in Grade 5 and placed at Grade 3 in one subject and Grade 7 in another — so never substitute one for the other, and state which one a figure used.

Fields

Field Name	Data Type	Description
id	String	Genesis: captured (platform-seeded CEDS-aligned reference data). Identifier of the grade level, a UUID from placement_grade.id. Never null; it is the primary key of this reference dataset, one row per grade level. Other datasets reference it as their grade id (e.g. Placement Results). Example: '6d5c4b3a-2e1f-4a0b-9c8d-7e6f5a4b3c2d'.
grade_identifier	String	Genesis: captured (platform-seeded CEDS-aligned reference data). CEDS education-level code for the grade, from placement_grade.grade_primary_identifier. Drawn from the CEDS vocabulary such as 'PK' (pre-kindergarten), 'KG' (kindergarten), '01' through '12', and 'PS' (postsecondary). Never null. Example: 'KG'.
label	String	Genesis: captured (platform-seeded CEDS-aligned reference data). Human-readable label for the grade level, from placement_grade.label, suitable for display. Never null. Example: 'Kindergarten' (or 'Grade 1', 'Grade 12').
order_number	Integer	Genesis: captured (platform-seeded CEDS-aligned reference data). Integer ordinal that orders grade levels from earliest to latest, from placement_grade.order_number; unique across grades, so sorting by it yields correct grade precedence (e.g. Kindergarten before Grade 1). Never null. Example: 1.

Join Keys

id: Primary key for joining with Placement Results (placed grade) and other grade-referencing datasets
grade_identifier: the CEDS code the roster path resolves against, matching Students.roster_grade_identifiers

Guides

Purpose

Provides core guide (educator) identification and contact information. Contains only users who hold a 'teacher' or 'principal' role — the same educator relationship types surfaced as guides in the Student Guides dataset. This is the foundational dataset for resolving an educator's details (e.g. the guide referenced by Student Guides), the educator-side counterpart to the Students dataset.

Fields

Field Name	Data Type	Description
id	String	Genesis: copied (minted by the OneRoster writers). Unique identifier of the guide (educator), a UUID from one_roster_user.id. Primary key, never null. Join target for guide_id on the student_guides dataset. Example: '7c9e6679-7425-40de-944b-e07fc1f90ae7'.
name	String	Genesis: copied (from SIS sync and Beyond rostering). Full display name of the guide, built as given_name then family_name from one_roster_user. Never null, though it may carry only one part when the other name field is empty. Example: 'Maria Gonzalez'.
email	String	Genesis: copied (from SIS sync and Beyond rostering). Email address of the guide, from one_roster_user.email (citext, unique). Never null. Example: 'maria.gonzalez@school.example.edu'.
org_ids	String	Genesis: computed (roster org memberships intersected with the caller allowed-org scope). Array of UUIDs of the organizations this row is visible under: the guide's organizations (from one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no guides, and when it is set only guides whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the guide's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Each element joins to an organization id. Example: ['1a2b3c4d-1111-2222-3333-444455556666'].

Join Keys

id: Primary key for resolving a guide's details (e.g. join from Student Guides via guide_id)
org_ids: Array of organization IDs the user belongs to; used for org-scoped filtering

Learning App Subjects

Purpose

Provides the relationship between learning applications and their associated subjects. This dataset enables subject-level analysis of learning app usage, time tracking by subject, and filtering dashboards by subject within learning app contexts.

Fields

Field Name	Data Type	Description
learning_app_id	String	Genesis: captured (app id minted at LTI application registration). Identifier of the learning application, a UUID from lti_application.id; joins to learning_apps.id. Never null. One row exists per (app, subject) pair, so the same app id repeats across all of the app's subjects. Example: '9e0f1a2b-3c4d-4e5f-8a6b-7c8d9e0f1a2b'.
subject_title	String	Genesis: captured (from the registered app subjects list). Subject name associated with the learning application: one element of the comma-separated lti_application.subjects list, whitespace-trimmed. Free text as registered, not canonicalized — match case-insensitively against subjects.title for curriculum lookups. Never null. Apps whose subjects list is NULL or empty produce no rows here at all (string_to_array of an empty string yields an empty array), so absence from this view means "no registered subjects". An empty-string value can only appear when the registered list itself contains an empty element (for example a trailing comma); treat empty strings as registration noise and filter them out. Example: 'Math'.

Join Keys

learning_app_id: Foreign key for joining with learning_apps.id
subject_title: Can be matched with subjects.title for curriculum subject lookups

Learning Apps

Purpose

The registry of LTI applications on the TimeBack platform, one row per registered application, used to resolve a learning_app_id to a name. Despite the dataset name it is NOT restricted to student-facing learning apps: dashboards, assessment surfaces, and internal LTI applications are registered here too, and their time flows into student_daily_engagement as app-attributed learning time. The registry serves no classification column, so this dataset cannot tell you which rows are real learning apps — classify the returned names yourself for the question at hand and disclose having done so. Ranking apps by time or XP without that step surfaces artifacts: a naive per-app ranking has been led by an assessment surface.

Fields

Field Name	Data Type	Description
id	String	Genesis: captured (app id minted at LTI application registration). Unique identifier of the application, as a UUID, from lti_application.id. Primary key, never null. This is the join target for any learning_app_id across the analytics datasets (e.g. sessions.learning_app_id, student_journals.learning_app_id). Example: '7c9e6679-7425-40de-944b-e07fc1f90ae7'.
name	String	Genesis: captured (from LTI application registration). Display name of the application, from lti_application.name. Never null and unique across applications. This dataset is not restricted to learning apps: dashboards, assessment surfaces, and internal applications register in lti_application too (dashboards under the ordinary learning-app registration type), so a row here may represent any LTI application. The name is the only signal available for telling them apart, so classify it explicitly before treating a row as a learning app. Example: 'Alpha Reading'.

Join Keys

id: Primary key for joining with any dataset that references learning applications (events, sessions, usage data, etc.)

Lessons

Purpose

Provides information about curriculum lessons available in the platform. This dataset enables analysis of lesson-level engagement, content organization, and curriculum coverage.

Fields

Field Name	Data Type	Description
id	String	Genesis: captured (from learning-app-published Caliper resources). Unique identifier of the lesson, from the learning-app-published Caliper resource (caliper_assignable_digital_resource rows with media_type 'curriculum/lesson'). Primary key; never null. Other datasets reference it as their lesson id (attempts, curriculum bridges). Example: '7c8d9e0f-1a2b-4c3d-8e9f-0a1b2c3d4e5f'.
title	String	Genesis: captured (from learning-app-published Caliper resource names). Human-readable lesson title: the Caliper entity's name, falling back to its description, then to the literal 'N/A' when the publishing app supplied neither. Never null — the fallback guarantees a value; 'N/A' means the app published the resource without a name. Example: 'Fractions on a Number Line'.

Join Keys

id: Primary key. Attempts references it via resource_id (which also holds tests, questions, and other assignable content, so filter by type). Sessions carries no lesson or resource column at all, and Questions carries no lesson id — reach a lesson's place in the hierarchy through Resource Relations instead

Organizations

Purpose

Lists every organization on the platform (all OneRoster org types) as a countable, joinable dimension with typed kind, hierarchy position, curated population scope, and per-org student counts. Each row is one organization. Scope roster-denominated metrics with population_scope: keep production for headline customer numbers, add internal for dogfood questions, and exclude legacy and test. Supersedes the deprecated students_enrollments view for cross-type org listing with joinable ids and counts; for school-only convenience use schools, and for student-to-org membership use student_schools.

Fields

Field Name	Data Type	Description
id	String	Genesis: copied (minted by the OneRoster writers). Unique identifier of the organization, a UUID equal to one_roster_organization.id. Primary key, never null. Join target for org-scoped datasets and for parent_id, root_id, and path self-joins. Example: '1a2b3c4d-1111-2222-3333-444455556666'.
name	String	Genesis: copied (from the SIS, Beyond, and OneRoster rostering writers). Human-readable name of the organization, from one_roster_organization.name. Never null. Example: 'Springfield Unified District'.
identifier	String	Genesis: copied (SIS-issued external identifier). External (SIS-issued) identifier for the organization, from one_roster_organization.identifier. Never null (the source column is NOT NULL), though it can be an empty or placeholder string when the writer had nothing to record, so test for emptiness rather than for null. Example: 'ORG-00042'.
type	String	Genesis: copied (from the SIS, Beyond, and OneRoster rostering writers). Typed organization kind from the OneRoster OrgType enum (one_roster_organization.type). Allowable values are department, school, district, local, state, and national. Never null. Example: 'district'.
status	String	Genesis: copied (from the SIS, Beyond, and OneRoster rostering writers). Lifecycle status of the organization, from one_roster_organization.status. Allowable values are active, inactive, and tobedeleted. Never null. Example: 'active'.
population_scope	String	Genesis: judged (curated per organization by platform operators; defaults to production). Curated analytics population classification, from one_roster_organization.population_scope. Allowable values are production (real external customers), internal (staff or dogfood accounts), legacy (retired population kept for history), and test (synthetic, QA, or sandbox). Never null; organizations default to production until an operator curates them, so production also covers never-reviewed rows. Set at creation or by operator backfill, never derived from names or emails. Per-org grain with no inheritance: an organization under a legacy or test ancestor keeps its own value, so filter on this column directly rather than on a root or parent. Exclude test and legacy from customer-facing roster denominators. Example: 'production'.
parent_id	String	Genesis: copied (from the SIS, Beyond, and OneRoster rostering writers). UUID of the immediate parent organization, from one_roster_organization.parent_id. Null when the organization is a hierarchy root. Joins to organizations.id. Example: '9f8e7d6c-1111-2222-3333-444455556666'.
parent_name	String	Genesis: copied (from the SIS, Beyond, and OneRoster rostering writers). Human-readable name of the immediate parent organization, from the parent one_roster_organization.name. Null when parent_id is null. Example: 'Springfield Unified District'.
parent_type	String	Genesis: copied (from the SIS, Beyond, and OneRoster rostering writers). OrgType of the immediate parent organization, from the parent one_roster_organization.type. Null when parent_id is null. Makes the parent-child kind relationship explicit without a self-join. Example: 'district'.
depth	Integer	Genesis: computed (recursive parent-walk over parent_id links). Distance from the hierarchy root along parent_id links. Zero at a root organization. Never null. Example: 1 for a school under a district.
path	String	Genesis: computed (recursive parent-walk over parent_id links). Ancestor organization UUIDs ordered root to self, inclusive of this organization. Never null. Use ' ' = ANY(path) to select an organization and all its descendants for rollups. Example: ['9f8e7d6c-1111-2222-3333-444455556666', '1a2b3c4d-1111-2222-3333-444455556666'].
root_id	String	Genesis: computed (recursive parent-walk over parent_id links). UUID of the top-of-tree organization for this row, equal to path[1]. Never null. Group by root_id for top-level rollups. Example: '9f8e7d6c-1111-2222-3333-444455556666'.
root_name	String	Genesis: computed (root resolved by recursive parent-walk) from copied organization names. Human-readable name of the root organization, from the root one_roster_organization.name. Falls back to this organization's own name when the root row has no name. Never null. Example: 'Springfield Unified District'.
student_count	Integer	Genesis: computed (distinct students ever attached via roles or class enrollments). Number of distinct students EVER associated with this organization by student role or class enrollment, with no status or date filter — it counts anyone ever attached. Prefer current_student_count for current org size. Never null; 0 when none. Per-org grain only: summing across a subtree may double-count a student attached at multiple levels. Example: 327.
current_student_count	Integer	Genesis: computed (distinct students with open-ended role or enrollment attachments). Number of distinct students CURRENTLY attached to this organization, counting only open-ended attachments (role or enrollment with end_date IS NULL; status not applied), matching the analytics student_schools open-membership probe — not roster status=active and not calendar-open future end dates. Always less than or equal to student_count. Never null; 0 when none. Use this for current org size. Example: 311.
org_ids	String	Genesis: computed (own organization id intersected with the caller allowed-org scope). Array of organization UUIDs this row is visible under: the organization's own id intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organization intersects it are returned; internal/unrestricted callers (the '*' sentinel) see the organization's own id. The array is never null and, for this single-organization row, contains exactly the organization's id when in scope. Example: ['1a2b3c4d-1111-2222-3333-444455556666'].

Join Keys

id: Primary key for joining org-scoped datasets and for parent_id, root_id, and path self-joins
parent_id: Immediate parent organization, if any
root_id: Top-of-tree organization for grouping rollups
org_ids: Array of organization IDs this row is visible under; used for org-scoped filtering

Placement Results

Purpose

Provides placement test results showing each student's placement status and assigned grade level per subject, one row per student+subject. Each record enables analysis of placement progress, grade distribution, and assessment completion rates. grade_id is null on every NotStarted row and populated on every InProgress and Completed row; on InProgress rows it is the current interim placement and may still change, so treat it as final only once the placement reaches Completed.

Fields

Field Name	Data Type	Description
student_id	String	Genesis: copied (minted by the OneRoster writers). Identifier of the student the placement is for, a UUID from placement_student_subject_placement.student_id. Never null; together with subject_id it forms the grain, one row per student+subject. Join with the Students dataset on student_id. Example: 'a1b2c3d4-5e6f-4a7b-8c9d-0e1f2a3b4c5d'.
subject_id	String	Genesis: copied (from CASE framework imports). Identifier of the CASE subject the student was placed in, a UUID. Never null. Join with the Case Subjects dataset on id. Example: '9c8b7a6d-5e4f-4a3b-8c2d-1e0f9a8b7c6d'.
grade_id	String	Genesis: captured (from the completing API caller) or copied (from Beyond placement sync). Identifier of the grade level the student is currently placed at for the subject, a UUID. Null on every NotStarted row and populated on every InProgress and Completed row. On InProgress rows this is the current interim placement and may still change; treat it as final only once placement_status is 'Completed'. Join with the Grades dataset on id. Example: '3d2c1b0a-9f8e-4d7c-b6a5-948372615f0e'.
placement_status	String	Genesis: captured (from the platform placement flow). Status of the placement process for this student and subject. Allowable values are 'NotStarted', 'InProgress', and 'Completed'; grade_id is null on NotStarted rows, interim on InProgress rows, and final on Completed rows. Never null (defaults to 'NotStarted'). Example: 'Completed'.
last_updated	DateTime	Genesis: captured (modification timestamp recorded by the placement writers). Timestamp of the most recent change to this placement record, from date_last_modified. Use it to find the latest placement state or detect recent activity. Null only when the source never set a modification time. Example: '2026-02-09T14:30:00Z'.
org_ids	String	Genesis: computed (role-membership intersection with caller scope) from copied inputs (OneRoster roles). Array of UUIDs of the organizations this row is visible under: the student's organizations (from one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the student's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Used for filtering rather than as a strict join key. Example: ['2b1a0c9d-8e7f-4a6b-b5c4-d3e2f1a09b8c'].

Join Keys

student_id: Join with Students dataset to get student demographic and profile information
subject_id: Join with Case Subjects dataset to get subject details
grade_id: Join with Grades dataset to get grade level details (nullable)
org_ids: Array of organization IDs the user belongs to; used for org-scoped filtering

Proctoring Insights

Purpose

Provides detailed proctoring insight events for learning sessions, capturing individual confirmed proctoring violations with their type, timing, duration, and the per-instance description shown in the Vault. Only includes external-facing proctoring insight types from proctored sessions where is_visible is true. Insight types that require review (per insights_config) are only included if they have a TruePositive review result. Each record represents a single proctoring event (e.g., Help From Another Person, Unauthorized App Use, Unauthorized Device Use) identified during a session.

When to use: this view is per-event grain (one row per individual proctoring incident), so use it for reading a violation's type, timing, and description. For totals, rankings, or per-student or per-session sums of proctoring time, use sessions_proctoring instead: it is the per-session aggregate with overlapping events already merged and is far cheaper to aggregate.

Fields

Field Name	Data Type	Description
session_id	String	Genesis: captured (session id minted at capture). Identifier of the session this proctoring event occurred in, from insights_session.caliper_session_id. Rows exist only for proctored sessions (is_proctored = TRUE). An opaque identifier string, not a uuid: production values are a mix of 'https://timeback.com/sessions/ ' URLs and bare UUID strings, so never cast it to uuid. Never null. Joins to sessions.id. Example: 'https://timeback.com/sessions/2026-07-23T11-02-18-750Z-ufduv99q0t8'.
insight_type	String	Genesis: judged (Vision proctoring detection; review-gated types human-confirmed). Slug identifying the kind of proctoring event, from insights_type.slug (insight types in the Proctoring category with visibility = 'external'). Never null. Allowable values are ProctoringHelpFromAnotherPerson, ProctoringUnauthorizedAppUse, ProctoringUnauthorizedDeviceUse. New proctoring types may be added without code changes. Example: 'ProctoringUnauthorizedAppUse'.
started_at_time	DateTime	Genesis: judged (span boundaries set by Vision detection, with merge gaps). Timestamp the proctoring event started, in UTC ISO-8601 format, from insights_insight.started_at_time. Never null. Always ≤ ended_at_time. Use start_time (identical values). Example: '2026-06-17T09:55:00.000Z'.
start_time	DateTime	Genesis: judged (span boundaries set by Vision detection, with merge gaps). Timestamp the proctoring event started, in UTC ISO-8601 format, from insights_insight.started_at_time. Never null. Always ≤ end_time. Prefer this column (aligned with the sessions dataset); started_at_time carries identical values. Example: '2026-06-17T09:55:00.000Z'.
ended_at_time	DateTime	Genesis: judged (span boundaries set by Vision detection, with merge gaps). Timestamp the proctoring event ended, in UTC ISO-8601 format, from insights_insight.ended_at_time. Never null. Always ≥ started_at_time. Use end_time (identical values). Example: '2026-06-17T09:55:15.000Z'.
end_time	DateTime	Genesis: judged (span boundaries set by Vision detection, with merge gaps). Timestamp the proctoring event ended, in UTC ISO-8601 format, from insights_insight.ended_at_time. Never null. Always ≥ start_time. Prefer this column (aligned with the sessions dataset); ended_at_time carries identical values. Example: '2026-06-17T09:55:15.000Z'.
duration_sec	Decimal	Genesis: computed (whole seconds between the insight timestamps) from judged inputs (Vision proctoring detection). Duration of the proctoring event in whole seconds, computed as round(extract(epoch from (ended_at_time - started_at_time))). Never null. Example: 15.
description	String	Genesis: copied (from the insight reason text, the Vault-facing description). Per-instance contextual description of the proctoring event, sourced from insights_insight.reason — the same human-readable text shown in the Vault for this insight. Never null. Example: 'Background app usage detected'.
org_ids	String	Genesis: computed (roster org membership intersected with caller scope) from copied OneRoster roles. Array of UUIDs of the organizations this row is visible under: the session's student's organizations (from public.one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the student's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Each element joins to an organization id. Example: ['1a2b3c4d-1111-2222-3333-444455556666'].

Join Keys

session_id: Join with Sessions dataset to get session details, timing, and student information
org_ids: Array of organization IDs the user belongs to; used for org-scoped filtering

Question Tests

Purpose

Bridge dataset linking assessment questions to the tests they belong to, one row per (question, test) pair, sourced from the Caliper 'isPartOf' relationship. The same question can be reused across more than one test, so this is a true many-to-many relationship and a question may appear in several rows; query this dataset instead of expecting a single test on the Questions dataset. Only relationships whose endpoints are real question and test entities are included, so every test_id resolves to a row in the Tests dataset (dangling identifiers are excluded).

Fields

Field Name	Data Type	Description
question_id	String	Genesis: captured (from Caliper isPartOf relationships). Identifier of the question, a UUID (or a persistent URL) from caliper_assessment_item.id. Never null; with test_id it forms the grain of this dataset, one row per question+test pair. Join with the Questions dataset on question_id. Example: '30083978-fd4d-4c24-a276-9a9779d355a6'.
test_id	String	Genesis: captured (from Caliper isPartOf relationships). Identifier of a test the question is part of, from caliper_assessment.id, resolved through the question's 'isPartOf' relationship. Never null and always resolvable to the Tests dataset. A question reused across multiple tests yields one row per test. Join with the Tests dataset on test_id. Example: 'https://app.athena.learnwith.ai/lesson/009062fa-7bdb-4edd-a4e0-32d01cb770ea/mastery-practice'.

Join Keys

question_id: Join with the Questions dataset to get question details
test_id: Join with the Tests dataset to get test details

Questions

Purpose

One row per assessment question (Caliper assessment item), with the question's title and type. Use it to identify a question. A question can belong to more than one test, so its test is not a column here; join the Question Tests dataset on id to resolve a question's test(s), and from there the Test Parents dataset to place it in the curriculum hierarchy. This dataset is identity only: it carries no per-student answer, score, time-spent, redirect, or worked-example data, and no difficulty, subject, or standard-alignment fields (subject and standard come from the CASE framework via the curriculum item). Note there is no question-response or per-question-performance dataset on this wire at all; the closest served surface is Attempts, whose resource_id can hold a question id.

Fields

Field Name	Data Type	Description
id	String	Genesis: captured (from learning-app-published Caliper resources). Identifier of the assessment question, a UUID v4 (or a persistent URL) from caliper_assessment_item.id. Never null; it is the grain of this dataset, one row per question. Join Question Tests on this id to reach its test(s), or Attempts on resource_id for per-student attempts; no response or per-question-results dataset is served. Example: 'a1b2c3d4-5e6f-4a7b-8c9d-0e1f2a3b4c5d'.
title	String	Genesis: captured (from learning-app-published Caliper resource names). Human-readable title of the question, taken from the Caliper entity's name, falling back to its description, then to the literal 'N/A' when neither is recorded. Never null: the value is 'N/A' exactly when both name and description are missing. Example: 'Solve for x: 2x + 3 = 11' (or 'N/A').
type	String	Genesis: captured (from learning-app-published Caliper resources). The question's content type, copied from caliper_assessment_item.media_type. It is a free-form string rather than a fixed enumeration; in this dataset it labels the question's delivery format, with observed values such as 'quiz' and 'guiding' rather than strict IANA media types. Null when the source item records no media type. Example: 'quiz'.

Join Keys

id: Primary key. Join Question Tests to resolve a question's test(s), and Attempts on resource_id for per-student attempt data. There is no responses or per-question-performance dataset to join

Recommendations

Purpose

Provides consumer-visible coaching recommendation data for students, including generation metadata, delivery status, and student feedback. Enables analysis of recommendation volume, delivery rates, feedback sentiment distribution, and source effectiveness across organizations. Two filters apply at the view boundary, and both narrow any denominator computed here: internal (staff-only) recommendations are excluded, and so are recommendations whose lifecycle status is not 'active', which drops inactive and soft-deleted ('tobedeleted') rows. So volume, delivery-rate, and feedback-rate figures from this view are over active student-visible recommendations only, not over everything ever generated — say so when reporting them.

Fields

Field Name	Data Type	Description
id	String	Genesis: captured (recommendation id minted at creation). Unique identifier of the recommendation, as a UUID, from insights_recommendation.id. Primary key, never null. Example: 'd4c3b2a1-6f5e-8b7a-0d9c-2f1e4b3a6c5d'.
student_id	String	Genesis: captured (set by the recommendation writer at creation). UUID of the student the recommendation is for, from insights_recommendation.user_id. Never null. Joins to students.id. Example: 'b3f1c2a4-7d8e-4f10-9a2b-6c5d4e3f2a1b'.
type	String	Genesis: captured (set by the recommendation writer at creation). The kind of recommendation, from insights_recommendation.type. Free-form string, not a closed enum; new types may appear without code changes. Never null. Observed value: 'daily-coaching'. Example: 'daily-coaching'.
source	String	Genesis: captured (writer-declared origin recorded at creation). The system or process that produced the recommendation, from insights_recommendation.source. Free-form string, not a closed enum. Never null. Observed values: 'vault-agent', 'manual', 'app-generated'. Example: 'vault-agent'.
source_reference_id	String	Genesis: captured (writer-supplied reference recorded at creation). Identifier of the originating record in the source system, e.g. the agent execution id, from insights_recommendation.source_reference_id. Free-form string. Null when the source records no reference. Example: 'exec_01H8XK4P2Q'.
title	String	Genesis: judged (coaching authored by the vault agent, staff, or apps, per source). Optional short title for the recommendation, from insights_recommendation.title, set by the source system. Null when the source provides no title. Example: 'Try a shorter session tomorrow'.
content	String	Genesis: judged (coaching authored by the vault agent, staff, or apps, per source). The recommendation message text in plain markdown, from insights_recommendation.content. Never null. Example: 'Great focus today! Tomorrow, try starting with math while your energy is high.'
date	Date	Genesis: computed (calendar date of generated_at). Calendar date on which the recommendation was generated, in YYYY-MM-DD form, derived as date(generated_at). Used for daily aggregation. Never null. Example: '2026-06-17'.
generated_at	DateTime	Genesis: captured (generation timestamp recorded by the writer). Timestamp the source produced the recommendation, in UTC ISO-8601 format, from insights_recommendation.generated_at. Never null. Example: '2026-06-17T06:00:00.000Z'.
delivered_at	DateTime	Genesis: captured (delivery event when the student first saw it). Timestamp the student first saw the recommendation, in UTC ISO-8601 format, from insights_recommendation.delivered_at. Null until the recommendation has been delivered to and seen by the student. Example: '2026-06-17T07:12:30.000Z'.
feedback_sentiment	String	Genesis: captured (student-submitted feedback). The student's feedback sentiment on the recommendation, from insights_recommendation.feedback_sentiment. Enum. Allowable values: 'POSITIVE' and 'NEGATIVE'. Null when the student has not given feedback. Example: 'POSITIVE'.
feedback_text	String	Genesis: captured (student-submitted feedback). Optional free-text feedback the student left on the recommendation, from insights_recommendation.feedback_text. Null when no feedback text was provided. Example: 'This helped me plan my morning.'
feedback_at	DateTime	Genesis: captured (student-submitted feedback timestamp). Timestamp the student submitted feedback, in UTC ISO-8601 format, from insights_recommendation.feedback_at. Null when no feedback has been submitted. Example: '2026-06-17T08:00:00.000Z'.
org_ids	String	Genesis: computed (roster org membership intersected with caller scope) from copied OneRoster roles. Array of UUIDs of the organizations this row is visible under: the student's organizations (from public.one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the student's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Each element joins to an organization id. Example: ['1a2b3c4d-1111-2222-3333-444455556666'].

Join Keys

id: Primary key for the recommendation
student_id: Join with Students dataset to get student details
org_ids: Array of organization IDs the student belongs to; used for org-scoped filtering

Resource Relations

Purpose

Directional relationships between Caliper entities, one row per edge, for navigating curriculum structure and content organization. This is an unfiltered projection of the whole relationship table: it is NOT restricted to the curriculum hierarchy or to 'isPartOf' edges, and neither endpoint is restricted by media type. So edges between non-curriculum entities are served here too, and a join to a specific resource dataset (Courses, Units, Lessons, Tests, Questions) will silently miss on any edge whose endpoint is not of that kind. Filter on relationship_type, and expect to check what each endpoint actually is.

Fields

Field Name	Data Type	Description
source_resource_id	String	Genesis: captured (from learning-app-published Caliper resource relationships). Identifier of the source resource in the relationship, from caliper_entity_relationship.source_id: the resource the edge points from (e.g., a unit in a unit->course 'isPartOf' edge). Never null. Join with any resource dataset (Courses, Units, Lessons, Tests, Questions) on its id. Example: 'f6a7b8c9-0d1e-4f2a-3b4c-5d6e7f809112'.
destination_resource_id	String	Genesis: captured (from learning-app-published Caliper resource relationships). Identifier of the destination resource in the relationship, from caliper_entity_relationship.destination_id: the resource the edge points to (e.g., the course in a unit->course 'isPartOf' edge). Never null. Join with any resource dataset on its id to get the related/parent resource. Example: '0a1b2c3d-4e5f-4a6b-7c8d-9e0f1a2b3c4d'.
relationship_type	String	Genesis: captured (from learning-app-published Caliper resource relationships). Type of the directional relationship, from caliper_entity_relationship.relationship_type; e.g. 'isPartOf' indicates the source resource is contained within the destination. Never null. Example: 'isPartOf'.
item_order	Integer	Genesis: captured (from learning-app-published Caliper resource relationships). Ordering/sequence position of the source resource within its relationship to the destination, taken from the relationship's order field. Null when no explicit order is recorded for the edge. Example: 2.

Join Keys

source_resource_id: Join a resource dataset (Courses, Units, Lessons, Tests, Questions) on its id — but only edges whose source is that kind will match
destination_resource_id: Same, for the related/parent resource

School Usage Stats

Purpose

Per-school usage rollup, one row per school, giving the student count, all-time learning-app time, and all-time waste time. Do NOT use this view for cross-school comparison or for any waste total: total_waste_time_sec sums UNMERGED insight durations, so overlapping events double-count and the figure overstates real waste — unlike every other integrity aggregate on this wire. For waste totals and rankings use sessions_waste (merged per session) or student_daily_engagement; this view also times out at platform scope. Two further reasons the numbers are not comparable across rows: the sums are all-time and unbounded in date, and a student attached to more than one school contributes their entire history to each, so totals across schools can exceed platform totals. student_count is ever-associated with no status or date filter; prefer schools.current_student_count for current school size. Waste time uses the same visibility and review rules as the Waste Insights dataset.

Fields

Field Name	Data Type	Description
school_id	String	Genesis: copied (minted by the OneRoster writers). UUID of the school this usage rollup is for, from one_roster_organization.id. Never null; one row per school. Joins to schools.id (use schools.parent_id for district rollups). Example: '1a2b3c4d-1111-2222-3333-444455556666'.
school_name	String	Genesis: copied (from SIS sync and Beyond-compatible rostering writers). Human-readable name of the school, from one_roster_organization.name. Never null. Example: 'Lincoln Elementary School'.
student_count	Integer	Genesis: computed (distinct students over copied roster roles and enrollments). Number of distinct students EVER associated with the school, counted across both organization role and class enrollment with no status, end-date, or date filter, so departed students are still counted. Never null; 0 when no students are attached. A student attached to more than one school is counted in each school's row. For current school size use schools.current_student_count instead. Example: 327.
total_app_time_sec	Decimal	Genesis: computed (sum of wall-clock durations of captured Caliper sessions). All-time learning-app session time in whole seconds, summed across the school's students, computed from caliper_session as sum(round(extract(epoch from (ended_at_time - started_at_time)))) over sessions with both timestamps set, excluding sessions whose insights record has been revoked. The sum is unbounded in date and a student attached to two schools contributes their whole history to both, so this is not comparable across rows. Never null; 0 when there are no completed sessions. Example: 1284560.
total_waste_time_sec	Decimal	Genesis: computed (sum of unmerged waste durations) from judged inputs (Vision waste insights). All-time waste time in whole seconds, summed across the school's students as a plain sum of individual insight durations with NO interval merging, using the same visibility and review rules as the Waste Insights dataset (external, visible, and review-confirmed insights only). Because simultaneous waste events are counted once each rather than merged, this OVERSTATES real waste time and is the only integrity aggregate on this wire that behaves that way: it will not reconcile with sessions_waste.waste_duration_sec or student_daily_engagement.waste_seconds, which merge overlapping intervals. Treat it as an upper bound, never as the school's waste total, and prefer the merged sources for any figure that leaves this view. Never null; 0 when no waste insights qualify. Example: 84230.
org_ids	String	Genesis: computed (caller-scope intersection of the school's own org id). Array of organization UUIDs this school-usage row is visible under: the school's own organization id intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organization intersects it are returned; internal/unrestricted callers (the '*' sentinel) see the school's own id. The array is never null and, for this single-organization row, contains exactly the school's organization id. Each element joins to an organization id. Example: ['1a2b3c4d-1111-2222-3333-444455556666'].

Join Keys

school_id: Join with Schools dataset to get school details (and parent_id for district rollups)
org_ids: Array of organization IDs the school is visible under; used for org-scoped filtering

Schools

Purpose

Lists the schools (educational institutions) on the platform so consumers can look up a school by its human-readable name without joining the operational roster tables. Each row is one school, with the parent organization (e.g. the district it belongs to) resolved to a readable name and ever-associated plus current student counts. Scope roster-denominated metrics with population_scope: keep production for headline customer numbers, add internal for dogfood questions, and exclude legacy and test.

Fields

Field Name	Data Type	Description
id	String	Genesis: copied (minted by the OneRoster writers). Unique identifier of the school, a UUID equal to the underlying one_roster_organization.id. Primary key, never null. Join target for school-scoped datasets such as student_schools.school_id and school_usage_stats.school_id. Example: '1a2b3c4d-1111-2222-3333-444455556666'.
name	String	Genesis: copied (from the SIS, Beyond, and OneRoster rostering writers). Human-readable name of the school, from one_roster_organization.name. Never null. Example: 'Lincoln Elementary School'.
identifier	String	Genesis: copied (SIS-issued external identifier). External (SIS-issued) identifier for the school, from one_roster_organization.identifier. Never null (the source column is NOT NULL), though it can be an empty or placeholder string when the writer had nothing to record, so test for emptiness rather than for null. Example: 'SCH-00042'.
parent_id	String	Genesis: copied (from the SIS, Beyond, and OneRoster rostering writers). UUID of the parent organization the school belongs to (typically its district), from one_roster_organization.parent_id. Null when the school has no parent. Joins to the organization hierarchy; a parent that is itself a school joins to schools.id. Example: '9f8e7d6c-1111-2222-3333-444455556666'.
parent_name	String	Genesis: copied (from the SIS, Beyond, and OneRoster rostering writers). Human-readable name of the parent organization, from the parent one_roster_organization.name. Null when the school has no parent (parent_id is null). Example: 'Springfield Unified District'.
status	String	Genesis: copied (from the SIS, Beyond, and OneRoster rostering writers). Lifecycle status of the school, from one_roster_organization.status. Allowable values are 'active', 'inactive', and 'tobedeleted'. Never null. Example: 'active'.
population_scope	String	Genesis: judged (curated per organization by platform operators; defaults to production). Curated analytics population classification of the school, from one_roster_organization.population_scope. Allowable values are production (real external customers), internal (staff or dogfood accounts), legacy (retired population kept for history), and test (synthetic, QA, or sandbox). Never null; schools default to production until an operator curates them, so production also covers never-reviewed rows. Set at creation or by operator backfill, never derived from names or emails. Per-org grain with no inheritance: a school under a legacy or test parent keeps its own value, so filter on this column directly rather than on parent_name. Exclude test and legacy from customer-facing roster denominators. Example: 'production'.
student_count	Integer	Genesis: computed (distinct students ever attached via roles or class enrollments). Number of distinct students EVER associated with the school by student role or class enrollment, with no status or date filter — it counts anyone ever attached. Prefer current_student_count for current school size. Never null; 0 when none. Example: 327.
current_student_count	Integer	Genesis: computed (distinct students with open-ended role or enrollment attachments). Number of distinct students CURRENTLY attached to the school, counting only open-ended attachments (role or enrollment with end_date IS NULL; status not applied), matching the analytics student_schools open-membership probe — not roster status=active and not calendar-open future end dates. Always less than or equal to student_count. Never null; 0 when none. Use this for current school size. Example: 311.
org_ids	String	Genesis: computed (own organization id intersected with the caller allowed-org scope). Array of organization UUIDs this school row is visible under: the school's own organization id intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organization intersects it are returned; internal/unrestricted callers (the '*' sentinel) see the school's own id. The array is never null and, for this single-organization row, contains exactly the school's organization id. Each element joins to an organization id. Example: ['1a2b3c4d-1111-2222-3333-444455556666'].

Join Keys

id: Primary key for joining school-scoped datasets (e.g. Student Schools via school_id)
parent_id: Parent organization the school belongs to, if any (e.g. its district)
org_ids: Array of organization IDs this school is visible under; used for org-scoped filtering

Sessions

Purpose

Provides detailed information about learning sessions, including timing, duration, proctoring data, and the attributed academic subject. This dataset enables analysis of student engagement patterns, time-on-task, and session-based learning activities across different applications.

Fields

Field Name	Data Type	Description
id	String	Genesis: captured (session id minted at capture). Unique identifier of the learning session, as recorded by Caliper. Sourced from caliper_session.id. Primary key, never null. Joins to session_id on the waste_insights, proctoring_insights, cheating_insights, and student_journals datasets. This id is an opaque identifier string with two shapes live in production — 'https://timeback.com/sessions/ ' URLs and bare UUID strings — so it is neither a timestamp nor reliably a uuid: never sort or range-filter it as a date, and never cast it to uuid (the URL-shaped rows make the cast fail). To filter or order sessions by time, use date or start_time instead. Example: 'https://timeback.com/sessions/2026-08-07T20-29-55-926Z-0cj8lwr80lmr'.
student_id	String	Genesis: captured (from Caliper session events). UUID of the student who owned this session, from caliper_session.user_id (the Person who initiated the session). Joins to students.id. Null when the underlying Caliper session has no associated person. Example: 'b3f1c2a4-7d8e-4f10-9a2b-6c5d4e3f2a1b'.
learning_app_id	String	Genesis: captured (from Caliper session events). Identifier of the application that hosted this session, from caliper_session.client_id (the hosting SoftwareApplication). Joins to learning_apps.id. Note this value derives from the Caliper client identifier, a different key space from student_journals.learning_app_id (which comes from insights_session.application_id). Null when the session records no hosting application. Example: '7c9e6679-7425-40de-944b-e07fc1f90ae7'.
date	Date	Genesis: computed (calendar date of start_time) from captured Caliper session events. Calendar date on which the session started, in YYYY-MM-DD form, derived as date(start_time). Used for daily grouping. Null only when start_time is null. Example: '2026-06-17'.
start_time	DateTime	Genesis: captured (from Caliper session events). Timestamp the session started, in UTC ISO-8601 format. For native sessions (Caliper SessionEvent with action 'Started'), this is that event's eventTime. For auto-attached sessions (created when activity events tagged with urn:tag arrive without a parent session — see #2044), this is the eventTime of the first activity event in the cluster. Null when the underlying Caliper session records no start (caliper_session.started_at_time is nullable) — in that case date and duration_sec are null too. When set, always ≤ end_time. Example: '2026-06-17T09:42:18.451Z'.
end_time	DateTime	Genesis: captured (from Caliper session events). Timestamp the session ended, in UTC ISO-8601 format. For native sessions this is the eventTime of the Caliper SessionEvent that ended or logged out the session; for auto-attached sessions (see #2044) it is extended to the eventTime of the latest activity event in the cluster, within a 1-hour inactivity window. Null while the session is still running or was never properly closed — in that case duration_sec is also null. When set, always ≥ start_time. Example: '2026-06-17T10:15:02.122Z'.
duration_sec	Decimal	Genesis: computed (whole seconds between session start and end) from captured Caliper session events. Wall-clock length of the session in whole seconds, computed as round(extract(epoch from (end_time - start_time))). Null whenever either endpoint is null: the session is still running, was never properly closed, or records no start at all. Named duration_sec on this dataset — the shared name across sessions, attempts, and the integrity insight datasets. Example: 1964.
webcam_enabled	Boolean	Genesis: captured (from the proctoring session upsert). Whether the webcam was enabled for this session. Sourced from insights_session.webcam_enabled and coalesced to FALSE when the session has no insights_session row, so it is never null. Proctoring is not a precondition: webcam-judged insight types also fire on ordinary learning sessions, so this can be true with is_proctored false. Because the coalesce collapses two cases, false means either the webcam was off or the session produced no insights record — use it to establish observation coverage, never as evidence the webcam was refused. Example: true.
is_proctored	Boolean	Genesis: captured (from the proctoring session upsert). Whether this session was run under proctoring. Sourced from insights_session.is_proctored and coalesced to FALSE when the session has no insights_session row, so it is never null. Example: false.
subject	String	Genesis: judged (vision-end subject resolution: app shortcut or journal classification). Academic subject attributed to this learning session at vision-end. Sourced from insights_session.subject. Null means not yet decided (the session has not been through a vision-end upsert). The explicit value 'Unknown' means the platform looked and could not confidently name a subject. Values: Reading, Language, Vocabulary, Social Studies, Writing, Science, FastMath, Math, Unknown. In V1 only vision-processed sessions receive a non-null subject. Example: 'Math'.
subject_source	String	Genesis: judged (records which resolution path produced subject). How subject was decided. Sourced from insights_session.subject_source. Values: 'app' (single-subject LTI application shortcut), 'journal' (vision journal classification on the upsert), 'none' (fallback Unknown). Null when subject is null. Example: 'journal'.
org_ids	String	Genesis: computed (roster org membership intersected with caller scope) from copied OneRoster roles. Array of UUIDs of the organizations this row is visible under: the session's student's organizations (from public.one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the student's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Each element joins to an organization id. Example: ['1a2b3c4d-1111-2222-3333-444455556666'].

Join Keys

id: Primary key for joining with event-level or session-related datasets
student_id: Join with Students dataset to get student details
learning_app_id: Join with Learning Apps dataset to get application details
org_ids: Array of organization IDs the user belongs to; used for org-scoped filtering

Sessions Cheating

Purpose

Provides aggregated cheating violation time data for learning sessions, capturing total confirmed cheating event duration per session. Only includes external-facing cheating insight types where is_visible is true. Insight types that require review (per insights_config) are only included if they have a TruePositive review result. Overlapping cheating events are merged to avoid double-counting. This dataset enables analysis of cheating violation severity across sessions.

When to use: this is the per-session aggregate (one row per session, overlapping events already merged), so use it for any ranking, total, or per-student rollup of confirmed cheating time. Canonical per-student ranking: join the Sessions dataset on sessions.id = sessions_cheating.session_id to get student_id, then GROUP BY student_id and SUM(cheating_duration_sec); filter the period on the Sessions dataset's date or start_time. Do not use cheating_insights for totals: it is per-event grain and much heavier to aggregate, and is meant only for reading an individual incident's type, timing, or description.

Fields

Field Name	Data Type	Description
session_id	String	Genesis: captured (session id minted at capture). Caliper session id (caliper_session_id of the insights_session) this cheating total is aggregated for; one row per session, proctored or not — cheating insights are not restricted to proctored sessions, so never gate them on is_proctored. An opaque identifier string, not a uuid: production values are a mix of 'https://timeback.com/sessions/ ' URLs and bare UUID strings, so never cast it to uuid. Joins to the Sessions dataset on its id to get session timing and the student. Never null. Example: 'https://timeback.com/sessions/2026-07-23T11-02-18-750Z-ufduv99q0t8'.
cheating_duration_sec	Decimal	Genesis: computed (per-session merge of overlapping insight intervals) from judged inputs (Vision cheating insights). Total confirmed cheating-violation time for the session in whole seconds, the rounded sum of all qualifying cheating insight durations with overlapping events merged so simultaneous violations are not double-counted. Counts only external, visible cheating insights (and review-gated ones only when confirmed TruePositive). A session with no qualifying cheating time produces no row, and the value is never null; however, because the sum is rounded to whole seconds, a session whose total qualifying time is under half a second emits 0 (rare). Example: 184.
org_ids	String	Genesis: computed (roster org membership intersected with caller scope) from copied OneRoster roles. Array of UUIDs of the organizations this row is visible under: the session's student's organizations (from one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the student's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Used for filtering rather than as a strict join key. Example: ['1a2b3c4d-1111-2222-3333-444455556666'].

Join Keys

session_id: Join with Sessions dataset using the id field to get session details, timing, and student information
org_ids: Array of organization IDs the user belongs to; used for org-scoped filtering

Sessions Proctoring

Purpose

Provides aggregated proctoring violation time data for learning sessions, capturing total confirmed proctoring event duration per session. Only includes external-facing proctoring insight types from proctored sessions where is_visible is true. Insight types that require review (per insights_config) are only included if they have a TruePositive review result. Overlapping proctoring events are merged to avoid double-counting. This dataset enables analysis of proctoring violation severity across sessions.

When to use: this is the per-session aggregate (one row per session, overlapping events already merged), so use it for any ranking, total, or per-student rollup of confirmed proctoring time. Canonical per-student ranking: join the Sessions dataset on sessions.id = sessions_proctoring.session_id to get student_id, then GROUP BY student_id and SUM(proctoring_duration_sec); filter the period on the Sessions dataset's date or start_time. Platform-wide totals may currently exceed the query timeout, so prefer per-student or short-window questions. Do not use proctoring_insights for totals: it is per-event grain and much heavier to aggregate, and is meant only for reading an individual incident's type, timing, or description.

Fields

Field Name	Data Type	Description
session_id	String	Genesis: captured (session id minted at capture). Caliper session id (caliper_session_id of the insights_session) this proctoring total is aggregated for; one row per proctored session. An opaque identifier string, not a uuid: production values are a mix of 'https://timeback.com/sessions/ ' URLs and bare UUID strings, so never cast it to uuid. Joins to the Sessions dataset on its id to get session timing and the student. Never null. Example: 'https://timeback.com/sessions/2026-07-23T11-02-18-750Z-ufduv99q0t8'.
proctoring_duration_sec	Decimal	Genesis: computed (per-session merge of overlapping insight intervals) from judged inputs (Vision proctoring insights). Total confirmed proctoring-violation time for the session in whole seconds, the rounded sum of all qualifying proctoring insight durations with overlapping events merged so simultaneous violations are not double-counted. Counts only external, visible proctoring insights on proctored sessions (and review-gated ones only when confirmed TruePositive). A session with no qualifying proctoring time produces no row, and the value is never null; however, because the sum is rounded to whole seconds, a session whose total qualifying time is under half a second emits 0 (rare). Example: 95.
org_ids	String	Genesis: computed (roster org membership intersected with caller scope) from copied OneRoster roles. Array of UUIDs of the organizations this row is visible under: the session's student's organizations (from one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the student's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Used for filtering rather than as a strict join key. Example: ['1a2b3c4d-1111-2222-3333-444455556666'].

Join Keys

session_id: Join with Sessions dataset using the id field to get session details, timing, and student information
org_ids: Array of organization IDs the user belongs to; used for org-scoped filtering

Sessions Waste

Purpose

Provides aggregated waste time data for learning sessions, capturing non-productive or off-task time identified by the insights system. Membership is decided by the type's is_waste flag and external visibility, NOT by category, so do not assume a fixed category set. Only includes insights where is_visible is true. Insight types that require review (per insights_config) are only included if they have a TruePositive review result. This dataset enables analysis of student focus, distraction patterns, and effective learning time across sessions.

When to use: this is the per-session aggregate (one row per session, overlapping events already merged), so use it for any ranking, total, or per-student rollup of wasted learning time. Canonical per-student ranking: join the Sessions dataset on sessions.id = sessions_waste.session_id to get student_id, then GROUP BY student_id and SUM(waste_duration_sec); filter the period on the Sessions dataset's date or start_time. Do not use waste_insights for totals: it is per-event grain and much heavier to aggregate, and is meant only for reading an individual incident's type, timing, or description.

Fields

Field Name	Data Type	Description
session_id	String	Genesis: captured (session id minted at capture). Caliper session id (caliper_session_id of the insights_session) this waste total is aggregated for; one row per session. An opaque identifier string, not a uuid: production values are a mix of 'https://timeback.com/sessions/ ' URLs and bare UUID strings, so never cast it to uuid. Joins to the Sessions dataset on its id to get session timing and the student. Never null. Example: 'https://timeback.com/sessions/2026-07-23T11-02-18-750Z-ufduv99q0t8'.
waste_duration_sec	Decimal	Genesis: computed (per-session merge of overlapping insight intervals) from judged inputs (Vision waste insights). Total non-productive (off-task) time for the session in whole seconds, computed by merging the qualifying waste insight intervals with Postgres range aggregation and then summing the merged spans — so two simultaneous waste events count once, not twice. It is therefore NOT the sum of the per-event waste_insights durations: summing those instead overstates this figure whenever events overlap. Counts only external, visible insight types flagged as waste (membership is decided by the is_waste flag and visibility, not by category, and review-gated types only when confirmed TruePositive). A session with no qualifying waste time produces no row, and the value is never null; however, because the sum is rounded to whole seconds, a session whose total qualifying time is under half a second emits 0 (rare). Example: 240.
org_ids	String	Genesis: computed (roster org membership intersected with caller scope) from copied OneRoster roles. Array of UUIDs of the organizations this row is visible under: the session's student's organizations (from one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the student's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Used for filtering rather than as a strict join key. Example: ['1a2b3c4d-1111-2222-3333-444455556666'].

Join Keys

session_id: Join with Sessions dataset using the id field to get session details, timing, and student information
org_ids: Array of organization IDs the user belongs to; used for org-scoped filtering

Purpose

Each student's current parental-consent status, one row per student, derived from the tb_consent_record ledger. tb_consent_record is an append-only audit trail of consent transitions; this view collapses it to the single latest non-deleted transition per student (highest occurred_at_time where date_deleted is null), so it answers "what is this student's consent status right now" without exposing the full history. Use it for "is this student's parental consent signed / granted", "which students are still pending or awaiting a guardian", and "when did this student's consent last change". "Signed" means consent_status = 'granted'. A student with no consent record at all does not appear (there is no row to project). This is a status snapshot, not a history: for the full transition trail an engineer must query tb_consent_record directly.

Fields

Field Name	Data Type	Description
student_id	String	Genesis: captured (in the platform consent ledger by staff and API writers). Identifier of the student the consent status is for, a UUID from tb_consent_record.student_id (a OneRoster user id, stored as char(36)). This is the grain: exactly one row per student. Never null. Join with the Students dataset on student_id to resolve the student's name, email, and grade. Example: 'b3f1c2a4-7d8e-4f10-9a2b-6c5d4e3f2a1b'.
consent_status	String	Genesis: captured (by staff and API consent writers) or judged (DocuSign webhook transitions). The student's current parental-consent status, the consent_status enum value from the latest non-deleted tb_consent_record row. Allowable values: 'granted' (consent signed and in force), 'pending' (requested but not yet granted; when docusign_status = 'sent' the envelope has been sent to the guardian and is awaiting their signature), 'denied' (guardian refused), 'expired' (a previously granted consent lapsed), 'voided' (invalidated, e.g. a superseded envelope), 'withdrawn' (guardian revoked a prior consent). Never null. "Signed" for product purposes means this equals 'granted'. Example: 'granted'.
status_changed_at	DateTime	Genesis: captured (transition timestamp recorded in the consent ledger). Timestamp of the latest consent transition, from tb_consent_record.occurred_at_time (UTC, no timezone offset stored). This is when the current consent_status was reached, so it doubles as "consent last changed at". Use it to order or window consent changes (e.g. granted in the last 30 days). Never null. Example: '2026-05-14T09:42:18.451Z'.
guardian_id	String	Genesis: captured (in the platform consent ledger by staff and API writers). Identifier of the guardian tied to the current consent record, a UUID from tb_consent_record.guardian_id (a OneRoster user id, char(36)). Null when the latest transition recorded no guardian (e.g. an in-person or awaiting-guardian record captured before a guardian was linked). A single guardian signing is sufficient to grant a student's consent. Join with student_guardians on guardian_id to resolve the guardian's name and email while the family relationship is open. Example: 'c4e5f6a7-8b9c-4d0e-1f2a-3b4c5d6e7f80'.
consent_method	String	Genesis: captured (in the consent ledger metadata at write time). How the current consent was collected, read from tb_consent_record.metadata ->> 'consentMethod'. Allowable values: 'email' (a DocuSign consent envelope was emailed to the guardian), 'in_person' (captured in person by staff), 'awaiting_guardian' (the student has no linked guardian yet, so consent cannot be requested). Null when the latest ledger row carried no consentMethod in its metadata; the application treats a missing consentMethod as in-person (ConsentMetadataUtil.getConsentMethod defaults to 'in_person'), so to match the app's in-person population filter on (consent_method IS NULL OR consent_method = 'in_person') rather than consent_method = 'in_person' alone. Example: 'email'.
docusign_status	String	Genesis: copied (from DocuSign at envelope creation). DocuSign envelope status captured at envelope creation, read from tb_consent_record.metadata ->> 'docusignStatus'. It is written in exactly one place: the PENDING record created when a DocuSign consent envelope is generated, where it holds the creation-time envelope status ('sent' when dispatched to the guardian, 'created' for a draft). It is NOT updated by later transitions: terminal rows (granted, denied, voided, withdrawn, expired) and in-person / awaiting-guardian records carry no docusignStatus, so this column is NULL on them. Because this view shows only the latest row per student, docusign_status is populated only while the student is still 'pending' on an emailed envelope and is NULL once consent is resolved; a filter like docusign_status = 'completed' therefore matches nothing (use consent_status = 'granted' to find signed consents). A 'sent' value alongside consent_status = 'pending' means the envelope was sent to the guardian and is awaiting their signature. Example: 'sent'.
org_ids	String	Genesis: computed (roster org memberships intersected with the caller allowed-org scope). Array of UUIDs of the organizations this row is visible under: the student's organizations (from public.one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set to a specific set only rows whose organizations intersect it are returned and the array is that intersection (a subset of the student's full membership, never exceeding the caller's scope). For an internal/unrestricted caller (scope '*') the array is the student's full organization membership; it is the empty array {} only when the student has no one_roster_role rows at all. The array itself is never null. Each element joins to an organization id (one_roster organizations). Used for filtering rather than as a strict join key. Example: ['1a2b3c4d-1111-2222-3333-444455556666'].

Join Keys

student_id: Join with the Students dataset to get student demographic and profile information (name, email, grades)
guardian_id: Raw OneRoster user id of the guardian; nullable. Join with student_guardians on guardian_id to resolve name and email while the relationship is open
org_ids: Array of organization IDs the student belongs to; used for org-scoped filtering, not as a strict join key

Student Daily Engagement

Purpose

Pre-aggregated daily engagement per student, learning app, and day: active time, off-task/cheating/proctoring time, XP earned, question counts, and mastered units, at one row per (student_id, learning_app_id, date). Rows merge native TimeBack session activity (source='sessions') with Beyond imported daily facts (source='facts'); when both exist for a cell, native values win. Always read the source column before interpreting a measure: active_seconds, waste_seconds, xp, questions_answered and questions_correct are computed here on native rows but COPIED from Beyond's EduBridge feed on facts rows, where the native formulas in their column comments do not apply, they will not reconcile with the sessions/sessions_waste/students_xp datasets, and the usual invariants between them are not enforced. The copied leg carries the majority of all-time hours, so this is the normal case, not an edge case. Materialized offline (not recomputed live), so per-school dashboards read an indexed scan instead of joining event views at scale.

When to use: use this for any per-student / per-school / per-day / per-app engagement rollup (time-on-task, waste, XP, integrity time, question counts, mastered units) and for coarser aggregations over it via GROUP BY. Do NOT assemble the same numbers live from the sessions, sessions_waste, sessions_cheating, sessions_proctoring, students_xp, or aggregated_session_attempts event views — that is the slow path this dataset replaces.

Units and keying: all *_seconds columns are whole seconds (divide by 60 for minutes). Session-derived metrics key on session owner, hosting app, and start day; XP keys on ledger user, application, and entry day; Beyond facts key on user_id, mapped learning_app_id, and fact date. Cheating/proctoring are native-only (0 for source='facts'). Activity with no student or no learning app is excluded.

Fields

Field Name	Data Type	Description
student_id	String	Genesis: copied (minted by the OneRoster writers). Identifier of the student this daily rollup row is for, a UUID from student_daily_engagement_rollup.student_id (the session owner for session-derived metrics, the ledger user for XP). Part of the (student_id, learning_app_id, date) grain, so never null. Join with the Students dataset on student_id. Example: 'a1b2c3d4-5e6f-4a7b-8c9d-0e1f2a3b4c5d'.
learning_app_id	String	Genesis: captured (application id minted at LTI app registration). Identifier of the learning application this row aggregates activity for, a UUID from student_daily_engagement_rollup.learning_app_id (caliper_session.client_id for session-derived metrics, tb_xp_ledger.application_id for XP; both resolve to a learning app id). Part of the grain, so never null. Join with the Learning Apps dataset on id. Example: '7c9e6679-7425-40de-944b-e07fc1f90ae7'.
date	Date	Genesis: computed (UTC day of the activity) or copied (Beyond fact date, per the source column). Calendar date the activity occurred on, in 'YYYY-MM-DD' form. For active/waste/cheating/proctoring time and question counts it is the session's start day (date of caliper_session.started_at_time); for XP it is the ledger entry day (date of tb_xp_ledger.date_created). Part of the grain, so never null. Example: '2026-06-17'.
active_seconds	Integer	Genesis: computed (daily sum of captured session durations) or copied (Beyond EduBridge facts, per the source column). Total active learning time for this student, app, and day, in whole seconds (divide by 60 for minutes). Check the source column before interpreting it, because the two legs are produced differently. On source='sessions' rows it is summed from each session's wall-clock duration round(extract(epoch from ended_at_time - started_at_time)) and reconciles with SUM(sessions.duration_sec) over the day. On source='facts' rows it is copied from the Beyond EduBridge daily facts feed, touches no session at all, will NOT reconcile with the sessions dataset, and is computed by Beyond in a way this platform does not restate. Never null; 0 when the day's row exists only because of other metrics (e.g. XP) with no closed session. Example: 5400.
waste_seconds	Integer	Genesis: computed (daily merge of judged Vision waste insights) or copied (Beyond facts, per the source column). Total off-task (waste) time for this student, app, and day, in whole seconds (divide by 60 for minutes). Check the source column before interpreting it. On source='sessions' rows it sums the per-session merged waste intervals (overlaps merged before summing, so simultaneous events are not double-counted) and matches SUM(sessions_waste.waste_duration_sec) over the day. On source='facts' rows it is copied from the Beyond EduBridge daily facts feed, has no sessions_waste rows behind it, and carries no guarantee of relating to active_seconds — copied rows can report more waste than active time. Never null; 0 when no qualifying waste was recorded. Example: 240.
cheating_seconds	Integer	Genesis: computed (daily merge of confirmed intervals) from judged inputs (Vision cheating insights). Total confirmed cheating-violation time for this student, app, and day, in whole seconds (divide by 60 for minutes). Sums the per-session merged cheating intervals (overlaps merged before summing), matching SUM(sessions_cheating.cheating_duration_sec) over the day. Native-only: on source='facts' rows it is hard-coded 0, which means NOT MEASURED rather than none found, so check source before reading a zero as a clean day. Never null; on native rows 0 means no confirmed cheating time was recorded. Example: 184.
proctoring_seconds	Integer	Genesis: computed (daily merge of confirmed intervals) from judged inputs (Vision proctoring insights). Total confirmed proctoring-violation time for this student, app, and day, in whole seconds (divide by 60 for minutes), from proctored sessions only. Sums the per-session merged proctoring intervals (overlaps merged before summing), matching SUM(sessions_proctoring.proctoring_duration_sec) over the day. Native-only: on source='facts' rows it is hard-coded 0, which means NOT MEASURED rather than none found, so check source before reading a zero as a clean day. Never null; on native rows 0 means no confirmed proctoring time was recorded. Example: 95.
xp	Decimal	Genesis: computed (daily sum of XP ledger entries) or copied (Beyond facts, per the source column). Total experience points earned for this student, app, and day. Check the source column before interpreting it: on source='sessions' rows it is SUM(tb_xp_ledger.value) over ledger entries created that day, while on source='facts' rows it is copied from the Beyond EduBridge daily facts feed and never reads this platform's ledger, so it will not reconcile with students_xp. A signed decimal: usually positive, can be 0, and may be negative where the ledger records a correction. Includes manual adjustments; the origin-level breakdown lives on students_xp. Never null (0 when no XP was earned that day). Example: 150.
questions_answered	Integer	Genesis: computed (daily count of scored first attempts) or copied (Beyond facts, per the source column). Count of questions the student answered on this app and day (first-attempt questions that produced a score, correct or not), attributed to the start day of the session the attempt belongs to on native rows. Check the source column: on source='sessions' rows this is computed here and is guaranteed to be at least questions_correct, because both count the same first-attempt set. On source='facts' rows it is copied from Beyond and that relationship is NOT guaranteed — copied rows can report more correct answers than answered ones, so test the invariant rather than assuming it. Never null; 0 when no scored question was answered. Example: 12.
questions_correct	Integer	Genesis: computed (daily count of full-marks first attempts) or copied (Beyond facts, per the source column). Count of questions the student answered correctly on this app and day (score given equals max score), first attempts only on native rows. Check the source column: on source='sessions' rows this never exceeds questions_answered, because both count the same first-attempt set. On source='facts' rows it is copied from Beyond with no such guarantee and can exceed questions_answered, so any accuracy ratio built across both legs needs the copied rows checked or excluded. Never null; 0 when none were correct. Example: 9.
mastered_units	Integer	Genesis: copied (from Beyond EduBridge daily facts; native rows carry 0). Count of units mastered on this app and day from Beyond imported facts (source='facts'). Native session rows carry 0. Never null. Example: 2.
source	String	Genesis: computed (provenance label assigned by the rollup refresh job). Provenance of this row: 'sessions' for native TimeBack activity (sessions, insights, XP ledger, attempts); 'facts' for Beyond imported daily aggregates. When both sources had data for the same cell, native values were kept and source is 'sessions'. Never null. Example: 'sessions'.
org_ids	String	Genesis: computed (caller-scope intersection of the student's rostered orgs). Array of UUIDs of the organizations this row is visible under: the student's organizations (from one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the student's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Used for filtering rather than as a strict join key. Example: ['2b1a0c9d-8e7f-4a6b-b5c4-d3e2f1a09b8c'].

Join Keys

student_id: Join with the Students dataset
learning_app_id: Join with the Learning Apps dataset
org_ids: Array of organization IDs for org-scoped filtering

Student Guardians

Purpose

Links students to their open guardians (parents and legal guardians) so consumers can answer "who are student X's guardians right now" directly without reconstructing it from the raw agent table. A guardian relationship is stored in one_roster_user_agent with the student as the source and the guardian as the agent; this view serves only open family relationships that can still sign in: relationship_type 'parent' or 'guardian', relationship status 'active', end_date null, and guardian user status 'active' with enabled_user true (matching parent-access eligibility). Ended, inactive, or disabled guardian users are excluded by construction, so a student with no row here has no reachable open guardian rather than no guardian ever. Educator relationships (teacher, principal) and other link types are excluded. A student with more than one open guardian appears once per row.

Fields

Field Name	Data Type	Description
student_id	String	Genesis: copied (from SIS relationship sync and API writers). UUID of the student the guardian is linked to, from one_roster_user_agent.source_user_id. Never null. Joins to students.id. Example: '7c9e6679-7425-40de-944b-e07fc1f90ae7'.
student_name	String	Genesis: copied (from SIS sync and Beyond rostering). Full display name of the student, built as given_name then family_name from one_roster_user. Never null, though it may carry only one part when the other name field is empty. Example: 'Jordan Lee'.
guardian_id	String	Genesis: copied (from SIS relationship sync and API writers). UUID of the guardian linked to the student, from one_roster_user_agent.agent_user_id. Never null. Do NOT join it to the Students dataset: a guardian holds no student role, so it is absent there and the join returns nothing — guardian_name and guardian_email are projected on this row precisely so no guardian lookup is needed. No analytics dataset exposes guardians as a directory of their own (Students is student-role only, Guides is teacher/principal only). Example: 'b3f1c2a4-7d8e-4f10-9a2b-6c5d4e3f2a1b'.
guardian_name	String	Genesis: copied (from SIS sync and Beyond rostering). Full display name of the guardian, built as given_name then family_name from one_roster_user. Never null, though it may carry only one part when the other name field is empty. Example: 'Maria Gonzalez'.
guardian_email	String	Genesis: copied (from SIS sync and Beyond rostering). Email address of the guardian, from one_roster_user.email (citext, unique, never null). Example: 'parent@example.com'.
relationship_type	String	Genesis: copied (from SIS relationship sync and API writers). Type of family relationship, from one_roster_user_agent.relationship_type. Allowable values in this dataset are 'parent' and 'guardian'; educator and other link types are excluded. Never null. Example: 'parent'.
status	String	Genesis: copied (from SIS relationship sync and API writers). Lifecycle status of the guardian relationship, from one_roster_user_agent.status. Only 'active' rows are served; ended relationships are excluded by the open-only filter. Never null on served rows. Example: 'active'.
begin_date	Date	Genesis: copied (from SIS relationship sync and API writers). Calendar date the guardian relationship became effective, in YYYY-MM-DD form, from one_roster_user_agent.begin_date. Null when no start date was recorded (open start). Example: '2026-08-15'.
org_ids	String	Genesis: computed (roster org memberships intersected with the caller allowed-org scope). Array of UUIDs of the organizations this row is visible under: the student's organizations (from one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the student's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Each element joins to an organization id. Example: ['1a2b3c4d-1111-2222-3333-444455556666'].

Join Keys

student_id: Join with Students dataset to get student details
guardian_id: Guardian OneRoster user id; guardian_name and guardian_email are projected on this row
org_ids: Array of organization IDs the student belongs to; used for org-scoped filtering

Student Guides

Purpose

Links students to their guides (educators) so consumers can answer "who is student X's guide" directly without reconstructing it from the raw agent table. A guide relationship is stored in one_roster_user_agent with the student as the source and the educator as the agent; this view keeps only the educator relationship types ('teacher' and 'principal') and excludes family relationships (parent, guardian) and peer/administrative links. A student with more than one guide (or the same guide over more than one period) appears once per row.

Full history is preserved: a guide relationship that ends is end-dated (end_date set, status inactive) rather than deleted. A distinct new relationship is a new row; re-establishing the same student/guide/type reopens that relationship's period (a partial unique index keeps at most one open period per relationship). Use begin_date/end_date for point-in-time questions such as "who was student X's guide on date D" (begin_date null = open start, end_date null = ongoing). assigned_date (row creation) remains as the timestamp the row was first recorded. org_ids reflects the student's current organizations (ended role memberships are excluded from org scoping).

Fields

Field Name	Data Type	Description
student_id	String	Genesis: copied (from SIS relationship sync and API writers). UUID of the student the guide is assigned to, from one_roster_user_agent.source_user_id. Never null. Joins to students.id. Example: '7c9e6679-7425-40de-944b-e07fc1f90ae7'.
student_name	String	Genesis: copied (from SIS sync and Beyond rostering). Full display name of the student, built as given_name then family_name from one_roster_user. Never null, though it may carry only one part when the other name field is empty. Example: 'Jordan Lee'.
guide_id	String	Genesis: copied (from SIS relationship sync and API writers). UUID of the guide (educator) assigned to the student, from one_roster_user_agent.agent_user_id. Never null. Joins to guides.id. Example: 'b3f1c2a4-7d8e-4f10-9a2b-6c5d4e3f2a1b'.
guide_name	String	Genesis: copied (from SIS sync and Beyond rostering). Full display name of the guide, built as given_name then family_name from one_roster_user. Never null, though it may carry only one part when the other name field is empty. Example: 'Maria Gonzalez'.
relationship_type	String	Genesis: copied (from SIS relationship sync and API writers). Type of guide relationship, from one_roster_user_agent.relationship_type. Allowable values in this dataset are 'teacher' and 'principal'; family relationships (parent, guardian) and other link types are excluded. Never null. Example: 'teacher'.
status	String	Genesis: copied (from SIS relationship sync and API writers). Lifecycle status of the guide relationship, from one_roster_user_agent.status. Allowable values are 'active', 'inactive', and 'tobedeleted'. A relationship that has ended is 'inactive' with end_date set. Never null. Example: 'active'.
begin_date	Date	Genesis: copied (from SIS relationship sync and API writers). Calendar date the guide relationship became effective, in YYYY-MM-DD form, from one_roster_user_agent.begin_date. Null when no start date was recorded (open start). Use with end_date for point-in-time membership. Example: '2026-08-15'.
end_date	Date	Genesis: copied (from SIS relationship sync and API writers). Calendar date the guide relationship ended, in YYYY-MM-DD form, from one_roster_user_agent.end_date. Null means the relationship is ongoing (no end recorded). Example: '2027-06-12'.
assigned_date	DateTime	Genesis: computed (write timestamp stamped when the relationship row was recorded). Timestamp the relationship row was created, in UTC ISO-8601 format, from one_roster_user_agent.date_created. The timestamp the row was first recorded; use begin_date for the effective start of the relationship. Never null. Example: '2026-08-15T13:20:05.000Z'.
last_modified_date	DateTime	Genesis: computed (write timestamp stamped when the relationship row was last updated). Timestamp the relationship row was last modified, in UTC ISO-8601 format, from one_roster_user_agent.date_last_modified. Never null; equals assigned_date when the row has never been updated. Example: '2026-09-01T08:05:42.000Z'.
org_ids	String	Genesis: computed (roster org memberships intersected with the caller allowed-org scope). Array of UUIDs of the organizations this row is visible under: the student's organizations (from one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the student's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Each element joins to an organization id. Example: ['1a2b3c4d-1111-2222-3333-444455556666'].

Join Keys

student_id: Join with Students dataset to get student details
guide_id: Join with Guides dataset (via id) to resolve the guide's details
org_ids: Array of organization IDs the student belongs to; used for org-scoped filtering

Student Journals

Purpose

Provides AI-narrated journal entries captured during learning sessions for externally visible journal types. Each entry is a 2-4 sentence narration of what the student appeared to be doing during a ~10-second recording window, written by TimeBack Vision's LLM chronicler from the combined screen, webcam, and audio chunk; per-chunk entries are merged and deduplicated before emission, so one row can cover a longer span. Entries are NOT student-authored: this dataset holds no student reflection, self-report, or qualitative feedback. It is the qualitative evidence layer under the quantitative rollups — use it to explain behavior the aggregates only measure. Every query must carry a single-student filter (student_id = '...'); the unbounded aggregate exceeds the statement budget, so volume and date extent are not derivable here.

Fields

Field Name	Data Type	Description
id	String	Genesis: judged (id minted with the AI session narration entry). Unique identifier of the journal entry, as a UUID, from insights_insight.id (a Journal-slug insight). Primary key, never null. Example: 'a1b2c3d4-5e6f-7a8b-9c0d-1e2f3a4b5c6d'.
student_id	String	Genesis: captured (from the session capture upsert). UUID of the student whose session was narrated, from insights_session.user_id. The student is the subject of the entry, not its author. Never null. Joins to students.id. Every query must filter on this column. Example: 'b3f1c2a4-7d8e-4f10-9a2b-6c5d4e3f2a1b'.
session_id	String	Genesis: captured (session id minted at capture). Identifier of the session in which the journal was captured, from insights_session.caliper_session_id. An opaque identifier string, not a uuid: production values are a mix of 'https://timeback.com/sessions/ ' URLs and bare UUID strings, so never cast it to uuid and never sort or range-filter it as a date. Never null. Joins to sessions.id. Example: 'https://timeback.com/sessions/2026-07-23T11-02-18-750Z-ufduv99q0t8'.
learning_app_id	String	Genesis: captured (from the session capture upsert). UUID of the application in which the journal was created, from insights_session.application_id. Never null. Joins to learning_apps.id. Note this derives from insights_session.application_id (an lti_application FK), a different key space from sessions.learning_app_id (which comes from caliper_session.client_id). Example: '7c9e6679-7425-40de-944b-e07fc1f90ae7'.
journal_text	String	Genesis: judged (AI session narration). The narration text, from insights_insight.reason verbatim. Written by TimeBack Vision's LLM chronicler describing the observed scene (student behavior, screen activity, environmental context such as background voices); the prompt forbids generic summaries and the chronicler is instructed to state what the evidence shows rather than editorialize. Not written by the student, so never read it as self-reported intent or feeling. Plain text, never null. Example: 'The student is working through a long-division worksheet on screen, pausing to re-watch a short video segment. A second voice is briefly audible off-camera.'
date	Date	Genesis: computed (calendar date of the narrated chunk window start). Calendar date on which the journal entry was created, in YYYY-MM-DD form, derived as date(timestamp). Used for daily grouping. Never null. Example: '2026-06-17'.
timestamp	DateTime	Genesis: captured (window start of the narrated recording chunk). Timestamp the journal entry was recorded, in UTC ISO-8601 format, from insights_insight.started_at_time. Never null. Example: '2026-06-17T09:42:18.451Z'.
org_ids	String	Genesis: computed (roster org membership intersected with caller scope) from copied OneRoster roles. Array of UUIDs of the organizations this row is visible under: the student's organizations (from public.one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the student's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Each element joins to an organization id. Example: ['1a2b3c4d-1111-2222-3333-444455556666'].

Join Keys

id: Primary key for the journal entry
student_id: Join with Students dataset to get student details
session_id: Join with Sessions dataset using the id field to get session details and timing
learning_app_id: Join with Learning Apps dataset to get application details
org_ids: Array of organization IDs the user belongs to; used for org-scoped filtering

Student Schools

Purpose

Links students to the schools they belong to, so consumers can answer "which students are attached to school X" directly without reconstructing membership from the raw roster tables. A student attaches to a school in two ways, and both are covered here: through their organization role (primary or secondary) and through enrollment in a class held at the school. The link_kind column makes the attachment type explicit so the two can be told apart, and begin_date/end_date expose when the attachment was active so consumers can reconstruct a student's school history over time. A student attached to one school in more than one way (or over more than one period) appears once per distinct attachment.

Fields

Field Name	Data Type	Description
student_id	String	Genesis: copied (from the roster role and enrollment writers). UUID of the student attached to the school, from one_roster_role.user_id or one_roster_enrollment.user_id. Never null. Joins to students.id. Example: '7c9e6679-7425-40de-944b-e07fc1f90ae7'.
school_id	String	Genesis: copied (minted by the OneRoster writers). UUID of the school the student is attached to, from one_roster_organization.id (organizations of type 'school'). Never null. Joins to schools.id. Example: '1a2b3c4d-1111-2222-3333-444455556666'.
school_name	String	Genesis: copied (from the SIS, Beyond, and OneRoster rostering writers). Human-readable name of the school the student is attached to, from one_roster_organization.name. Never null. Example: 'Lincoln Elementary School'.
link_kind	String	Genesis: computed (discriminator naming which roster record produced the link). How the student attaches to the school. Allowable values are 'primary_role' (primary organization role), 'secondary_role' (secondary organization role), and 'enrollment' (enrolled in a class held at the school). Never null. Example: 'enrollment'.
begin_date	Date	Genesis: copied (from the role or enrollment writer; SIS role begin dates are sync days). Calendar date the attachment became effective, in YYYY-MM-DD form, from the role's or enrollment's begin_date. Null when no start date was recorded. Use with end_date for point-in-time membership. Example: '2026-08-15'.
end_date	Date	Genesis: copied (from the role or enrollment writer). Calendar date the attachment ended, in YYYY-MM-DD form, from the role's or enrollment's end_date. Null means the attachment is ongoing (no end recorded). Example: '2027-06-12'.
org_ids	String	Genesis: computed (roster org memberships intersected with the caller allowed-org scope). Array of UUIDs of the organizations this row is visible under: the student's organizations (from one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the student's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Each element joins to an organization id. Example: ['1a2b3c4d-1111-2222-3333-444455556666'].

Join Keys

student_id: Join with Students dataset to get student details
school_id: Join with Schools dataset to get school details
org_ids: Array of organization IDs the student belongs to; used for org-scoped filtering

Student Start Dates

Purpose

One row per student giving the three canonical "start date" anchors in one place, so a consumer does not have to reconstruct them from roles, enrollments, and assessment results. admission_date comes from the student's primary school role (falling back to any school role); earliest_enrollment_date is the earliest class-enrollment begin date; earliest_map_date is a proxy, not a verified MAP date: it is the earliest active gradebook assessment result carrying a report URL, which is not the same thing as a completed MAP sitting (see the column comment). Each anchor is independent and any of them can be null when the underlying record does not exist. This dataset carries only these date anchors and the org-scoping array; it has no subject, grade, school, or score columns.

Fields

Field Name	Data Type	Description
student_id	String	Genesis: copied (minted by the OneRoster writers). Identifier of the student, a UUID restricted to users holding the 'student' role. Never null; it is the grain of this dataset, one row per student. Join with the Students dataset on student_id. Example: 'a1b2c3d4-5e6f-4a7b-8c9d-0e1f2a3b4c5d'.
admission_date	Date	Genesis: copied (school-role begin date, a sync-day proxy for admission). Admission date from the student's primary school role, falling back to any school role, as a calendar date. Currently backfilled from the role's begin date as a transitional proxy until authoritative admission data is ingested, so it may equal earliest_enrollment_date. Null when the student has no school role carrying an admission date. Example: '2025-08-25'.
earliest_enrollment_date	Date	Genesis: computed (minimum begin date of copied OneRoster enrollments). Earliest class-enrollment begin date across the student's student-role enrollments, as a calendar date: when the student first started any class. Null when none of the student's enrollments record a begin date. Example: '2025-09-02'.
earliest_map_date	Date	Genesis: computed (earliest score date of copied gradebook results carrying a report URL). Earliest score date, as a calendar date, across the student's active gradebook assessment results that carry a report URL in their metadata. Intended as "when the student first sat a MAP test", but the query tests neither MAP-ness nor completion: it filters only on status = 'active' and a non-null metadata reportUrl, so a STAAR result or any other report-bearing row satisfies it. Treat the MAP reading as a product-level assumption, not a guarantee this column enforces; if you need MAP specifically, filter assessment_results on assessment_type = 'map' instead. Null when the student has no active report-bearing result. Example: '2025-09-15'.
org_ids	String	Genesis: computed (caller-scope intersection of the student's rostered orgs). Array of UUIDs of the organizations this row is visible under: the student's organizations (from one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the student's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Used for filtering rather than as a strict join key. Example: ['2b1a0c9d-8e7f-4a6b-b5c4-d3e2f1a09b8c'].

Join Keys

student_id: Join with the Students dataset to get student details
org_ids: Array of organization IDs the student belongs to; used for org-scoped filtering

Students

Purpose

Provides core student identification and contact information. Contains only users who hold a 'student' role (educators, guides, and family members are excluded — see the Guides dataset for educators). This is the foundational dataset for joining student-related data across all other datasets in the analytics environment. Includes the student's grade level(s) so consumers can filter students by grade (e.g. all 8th-grade students in an org) without joining other datasets. Also serves roster_grade_identifiers (the raw grades normalized to CEDS grade_identifier codes) and roster_grade_unmapped_codes (raw codes that did not normalize), so consumers can compare roster grade to placed grade without hand-rolled normalization (see the Grades dataset and dictionary trap 2).

Fields

| Field Name | Data Type | Description | | :------------------------------ | :-------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --- | --- | --- | ---------------------------------------------------------------------------------------------------------------------------------------------- | | id | String | Genesis: copied (minted by the OneRoster writers). Unique identifier of the student, as a UUID. Primary key, copied from one_roster_user.id; never null. This is the value other datasets reference as student_id (sessions, recommendations, student_journals, waste/proctoring/cheating insights via their session). Example: 'b3f1c2a4-7d8e-4f10-9a2b-6c5d4e3f2a1b'. | | name | String | Genesis: copied (from SIS sync and Beyond rostering). The student's full display name, formed by concatenating the given and family names from their SIS-issued OneRoster user record as given_name | | ' ' | | family_name (single space separator). Both source columns are required, so this is never null. Not guaranteed unique. Example: 'Ada Lovelace'. | | email | String | Genesis: copied (from SIS sync and Beyond rostering). The student's email address, taken verbatim from the OneRoster user record. Case-insensitive (stored as citext) and unique across users; never null. Example: 'ada.lovelace@school.edu'. | | grades | String | Genesis: copied (from SIS sync and Beyond rostering). Array of grade-level codes assigned to the student, from the OneRoster user record (one_roster_user.grades, split on commas, whitespace-trimmed, blank entries dropped). Values are raw grade identifiers as supplied by the rostering source, not canonicalized: un-padded forms such as '8' or 'K' and CEDS-style codes such as '08' or 'KG' both occur in real data, so filter with both variants (e.g. grade-8 students: grades && ARRAY['8','08']). Never null: the empty array {} when the user record has no grade set; usually contains exactly one element. Updated whenever the student's OneRoster user record is imported or upserted. This is the roster-declared grade, not the per-subject placed grade — for the latter use Placement Results. Example: ['8']. | | roster_grade_identifiers | String | Genesis: computed (roster grades normalized against the CEDS reference) from copied roster grades. Array of canonical CEDS grade codes that the student's raw roster grades (see grades column) normalize to. Each element is a grades.grade_identifier value ('PK','KG','01'..'12'), resolved case-insensitively against the CEDS reference by matching the raw roster code to a grade's primary or alternative identifier (e.g. roster '8' or '08' -> '08'; 'K' or 'KG' -> 'KG'; 'PK' or 'PreK' -> 'PK'). This is the served normalization: join unnest(roster_grade_identifiers) to grades.grade_identifier (or the Grades dataset) to compare roster grade against placed grade without a hand-rolled CASE expression (trap 2). Never null: the empty array {} when the student has no roster grade or none of them normalize (see roster_grade_unmapped_codes). Example: ['08']. | | roster_grade_unmapped_codes | String | Genesis: computed (roster grades that failed CEDS normalization) from copied roster grades. Array of the student's non-empty raw roster grade codes that did NOT resolve to any CEDS grade (unrecognized spellings). Use it to keep non-normalizable students visible in a denominator instead of silently dropping them. Never null: the empty array {} when every roster grade normalized (the common case). Example: ['ZZ']. | | org_ids | String | Genesis: computed (roster org memberships intersected with the caller allowed-org scope). Array of UUIDs of the organizations this row is visible under: the student's organizations (from public.one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the student's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Each element joins to an organization id (one_roster organizations). Example: ['1a2b3c4d-1111-2222-3333-444455556666','5e6f7a8b-7777-8888-9999-aaaabbbbcccc']. |

Join Keys

id: Primary key for joining with any student-related dataset (performance, events, enrollments, etc.)
org_ids: Array of organization IDs the user belongs to; used for org-scoped filtering

Students Enrollments

Purpose

DEPRECATED and known-defective — do not build anything new on this view; prefer the Student Schools dataset, which links students to schools with an explicit link_kind (primary role, secondary role, or enrollment) and a joinable school_id.

The defect: despite the dataset name and the student_id column name, this view applies NO role filter. It projects every one_roster_role row, so teachers, principals, and any other role are served here alongside students, under a column called student_id. Anyone counting students or joining student_id to the Students dataset gets a wrong answer — the count is inflated by staff, and the join silently drops the staff rows rather than reporting them. Filter by joining Students (student-role only) if you must read this view at all. The filter is deliberately not being added because the view is on its way out and changing which rows it returns would break existing consumers in a second, less visible way; the fix is migrating consumers to Student Schools, after which this view can be removed.

Beyond that: it reports role-based membership across every organization type (school, district, department, and so on), one row per user-organization role, keyed only by the organization's name and not by a joinable id; it is not a class enrollment. It carries no school_id, subject, grade, or class column. For school linkage use Student Schools; for the courses or subjects a student is enrolled in use Course Enrollments.

Fields

Field Name	Data Type	Description
student_id	String	Genesis: copied (from the roster role writers). Identifier of the user holding the role, a UUID from one_roster_role.user_id. Misnamed: this is NOT restricted to students. The view applies no role filter, so teachers, principals, and other roles appear here under this column — never treat it as a student list or count students from it. Never null; with organization_name it forms the grain, one row per user-organization role. Join with the Students dataset on student_id to restrict to actual students (that join also silently discards the staff rows, so it is not a way to detect them). Example: 'a1b2c3d4-5e6f-4a7b-8c9d-0e1f2a3b4c5d'.
organization_name	String	Genesis: copied (from the SIS, Beyond, and OneRoster rostering writers). Display name of an organization the user holds a role in (any organization type, not specifically a school), from one_roster_organization.name. This is a name string, not a joinable id, so organizations that share a name are indistinguishable here; use the Student Schools dataset when a school id is needed. Never null. Example: 'Alpha High School'.
org_ids	String	Genesis: computed (roster org memberships intersected with the caller allowed-org scope). Array of UUIDs of the organizations this row is visible under: the student's organizations (from one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the student's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Used for filtering rather than as a strict join key. Example: ['2b1a0c9d-8e7f-4a6b-b5c4-d3e2f1a09b8c'].

Join Keys

student_id: Join with the Students dataset to get student details
org_ids: Array of organization IDs the student belongs to; used for org-scoped filtering

Students Mastery

Purpose

Mastery achievements earned by students, one row per mastery credential: a student's measured mastery of a single CASE curriculum item (a learning standard or objective). Restricted to standard- and curriculum-mastery credentials. The view holds both in-progress and completed credentials; filter result_status = 'Completed' for earned mastery. Use it for "how well has this student mastered this item" and learning-gap analysis. Mastery is keyed by curriculum item, not by subject or grade: there is no subject or grade column here. To reach the subject, join curriculum_item_id to the curriculum item and on to Case Subjects; for a student's grade level use Placement Results (per subject) or the Grades reference dataset. This dataset is mastery-credential level and carries no per-attempt or per-session detail (use Attempts and Students XP for that).

Fields

Field Name	Data Type	Description
student_id	String	Genesis: copied (minted by the OneRoster writers). Identifier of the student who earned the mastery credential, a UUID from clr_achievement_credential.credential_subject_id (OneRosterUser.id). Never null; with curriculum_item_id it identifies the mastery record. Join with the Students dataset on student_id. Example: 'a1b2c3d4-5e6f-4a7b-8c9d-0e1f2a3b4c5d'.
mastery_percentage	Decimal	Genesis: computed (stored fraction times 100) from judged (learning-app Caliper grades) or computed inputs (platform mastery calculation). Mastery level as a percentage from 0 to 100, computed as the credential's result_value (a 0-to-1 progress fraction toward the mastery achievement) multiplied by 100, rounded to two decimals. Null when the credential carries no result_value. Example: 87.50.
curriculum_item_id	String	Genesis: copied (from CASE hierarchy imports). Identifier of the CASE curriculum item (CFItem: a learning standard or objective) this mastery is measured against, from clr_achievement.cf_item_id. This is a curriculum-item id, not a subject or grade. Null when the underlying achievement is not aligned to a CASE item. Join to the curriculum item on its identifier, then to Case Subjects for the subject. Example: '3f2e1d0c-9b8a-4c7d-8e6f-5a4b3c2d1e0f'.
mastery_timestamp	DateTime	Genesis: copied (original award timestamp preserved on the credential). Timestamp the mastery credential was originally awarded, from clr_achievement_credential.awarded_date, in UTC. Use it to place mastery on a timeline (e.g. highest item mastered as of a date). Null when the original award date was not preserved on the credential. Example: '2026-04-18T14:05:22.000Z'.
result_status	String	Genesis: copied (from clr_achievement_credential.result_status). Lifecycle state of the mastery credential from clr_achievement_credential.result_status. Observed values for mastery credentials include Completed and InProgress (prod 2026-07-22: 15,438 Completed vs 17,166 InProgress StandardMastery rows). Filter WHERE result_status = 'Completed' for earned credentials; InProgress rows may still carry an awarded_date or mastery_percentage. Never null on active credentials. Example: 'Completed'.
learning_app_id	String	Genesis: captured (from LTI application curriculum-scope registration). Identifier of the learning application whose curriculum contains the mastered item, resolved via the item's CASE document to lti_application_curriculum_scope.lti_application_id. Null when the item's document maps to no application. Join with the Learning Apps dataset on learning_app_id. Example: '9c8b7a6d-5e4f-4a3b-8c2d-1e0f9a8b7c6d'.
org_ids	String	Genesis: computed (role-membership intersection with caller scope) from copied inputs (OneRoster roles). Array of UUIDs of the organizations this row is visible under: the student's organizations (from one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the student's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Used for filtering rather than as a strict join key. Example: ['2b1a0c9d-8e7f-4a6b-b5c4-d3e2f1a09b8c'].

Join Keys

student_id: Join with the Students dataset to get student demographic and profile information
curriculum_item_id: Join with the curriculum item (CASE CFItem); from there reach Case Subjects for the subject
result_status: Filter Completed vs InProgress mastery credentials
learning_app_id: Join with the Learning Apps dataset to get application details
org_ids: Array of organization IDs the student belongs to; used for org-scoped filtering

Students Xp

Purpose

Experience points (XP) recorded on this platform's own XP ledger, for analysis of engagement, progression, and gamification. Each row is the XP total for a student within one learning app, session, day, origin, and subject, linked back to its originating Caliper event. Scope limits worth knowing before you quote a number: this is the platform ledger, not a canonical or complete record of XP a student has earned — it has no published parity contract with the predecessor platform, some apps' XP is credited there and not here, and a row exists only where the ledger entry could be tied to a Caliper event. Disclose which surface a figure came from rather than presenting these totals as the student's XP.

origin distinguishes how the XP was produced: COMPUTED (platform engine), APP_REPORTED (verbatim GradeEvent from an app), or MANUAL (administrative adjustment). Manual entries are administrative escape-valve adjustments attributed to a real app — never confuse them with computed or app-reported XP. Filter origin = 'MANUAL' (or exclude it) when mining manual awards; student_daily_engagement.xp includes manual XP in its total, while the origin-level breakdown lives here.

When to use: to compute XP earned in sessions flagged for integrity issues, join this view's session_id to the per-session aggregates sessions_cheating, sessions_waste, or sessions_proctoring on their session_id (all share the Caliper session-id key space). Example: XP earned in cheating-flagged sessions is a join of students_xp to sessions_cheating on session_id, GROUP BY student_id and SUM(total_value), filtering the period on students_xp.date.

Fields

Field Name	Data Type	Description
student_id	String	Genesis: copied (minted by the OneRoster writers). Identifier of the student who earned the XP, a UUID from tb_xp_ledger.user_id. Never null. Join with the Students dataset on student_id. Example: 'a1b2c3d4-5e6f-4a7b-8c9d-0e1f2a3b4c5d'.
learning_app_id	String	Genesis: captured (application id minted at LTI app registration). Identifier of the learning application in which the XP was earned, a UUID from tb_xp_ledger.application_id. Never null (every ledger entry is attributed to an application). Join with the Learning Apps dataset on id. Example: '5a4b3c2d-1e0f-4a9b-8c7d-6e5f4a3b2c1d'.
session_id	String	Genesis: captured (session id minted at capture). Identifier of the learning session the XP was earned in, taken from the originating Caliper event. An opaque identifier string, not a uuid: production values are a mix of 'https://timeback.com/sessions/ ' URLs and bare UUID strings, so never cast it to uuid. Null when the source event has no session. Join with the Sessions dataset on session_id. Example: 'https://timeback.com/sessions/2026-07-23T11-02-18-750Z-ufduv99q0t8'.
date	Date	Genesis: computed (day of the XP ledger entry's creation timestamp). Calendar date on which the XP was earned, derived from the ledger entry's creation timestamp. Null only when that timestamp is missing. Rows are aggregated per student, app, session, date, origin, and subject. Example: '2026-03-14'.
total_value	Decimal	Genesis: computed (platform XP engine) or captured (app-reported grade events and manual adjustments, per the origin column). Total experience points earned for this student, app, session, date, origin, and subject, summed across the underlying ledger entries. Never null; typically positive, can be zero, and may be negative where the ledger records a correction. Example: 150.
origin	String	Genesis: computed (provenance label assigned by the XP ledger writer). How the XP was produced, from tb_xp_ledger.origin. Allowable values: 'COMPUTED' (platform XP engine), 'APP_REPORTED' (verbatim GradeEvent from an app), 'MANUAL' (administrative adjustment). Never null. Filter origin = 'MANUAL' to mine manual awards by app/subject; exclude MANUAL when measuring computed/app-reported engagement. Example: 'MANUAL'.
subject_id	String	Genesis: copied (CASE subject identifier from CASE imports). Identifier of the CASE subject attributed on the ledger entry, a UUID from tb_xp_ledger.subject_id referencing cf_subject.identifier. Populated for manual adjustments that carry a subject; typically null for computed and app-reported rows (subject is derivable via curriculum item when needed). Join with the Subjects dataset on identifier. Example: '7f6e5d4c-3b2a-4190-8e7d-6c5b4a392817'.
org_ids	String	Genesis: computed (caller-scope intersection of the student's rostered orgs). Array of UUIDs of the organizations this row is visible under: the student's organizations (from one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the student's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Used for filtering rather than as a strict join key. Example: ['2b1a0c9d-8e7f-4a6b-b5c4-d3e2f1a09b8c'].

Join Keys

student_id: Join with Students dataset to get student demographic and profile information
learning_app_id: Join with Learning Apps dataset to get application details and metadata
session_id: Join with Sessions dataset to get session context, timing, and related activities
subject_id: Join with Subjects (cf_subject) for the subject attributed on manual adjustments; null for most computed/app-reported rows
org_ids: Array of organization IDs the user belongs to; used for org-scoped filtering

Subject Tracks

Purpose

Which course a student is placed into for a given subject and grade: one row per subject-track slot mapping a (subject, grade) pair to the course that teaches it. Use it for "list the subject tracks and their subjects", "which course covers Grade 4 Reading", and "which subject track is this course on" (join on course_id). The subject is a fixed set of track subjects modeled by Beyond directly on the row (an enum), NOT the CASE-framework subject taxonomy nor the MAP-test subjects; these three subject vocabularies are independent and must not be equated by string match, so join to the Subjects/CASE datasets only when you explicitly want that other taxonomy, never to "resolve" this subject. A track can be the global default (is_global = true, org_sourced_id null, applies to every organization) or a per-organization override (is_global = false, org_sourced_id set, that org's course for the slot). Org scoping: global-default rows carry no organization and are visible to every caller; per-organization override rows are fail-closed and visible only to callers whose org scope includes org_sourced_id (internal/unrestricted callers see all), so a restricted caller sees the global defaults plus only its own overrides. See the org_ids column. Data coverage: imported tenant-wide from Beyond (#2287) under a single tenant; there is effectively one real tenant today. Deletions on the Beyond side are not propagated, so a slot may persist after Beyond removes it.

Fields

Field Name	Data Type	Description
id	String	Genesis: copied (from the Beyond tenant-data import). Identifier of the subject-track slot, a UUID from beyond_subject_track.id. This is the grain: one row per (subject, grade, organization-or-global) slot. Never null. Example: 'c3d4e5f6-7a8b-4c9d-0e1f-2a3b4c5d6e7f'.
subject	String	Genesis: copied (Beyond track-subject enum, via the tenant-data import). Track subject, from beyond_subject_track.subject. Allowable values: 'Reading', 'Language', 'Vocabulary', 'Social Studies', 'Writing', 'Science', 'FastMath', 'Math', 'None', 'Other'. This is Beyond's own track-subject enum stored on the row, distinct from the CASE-framework subjects (Subjects dataset) and from MAP-test subjects; do not equate by string even when the text agrees. Never null. Example: 'Reading'.
grade	String	Genesis: copied (from the Beyond tenant-data import). Grade level the track applies to, from beyond_subject_track.grade, stored as a text code. Allowable values: '-1' = Pre-K, '0' = Kindergarten, '1' through '12' = Grades 1-12, '13' = AP. Compare as text, not as a number (the codes are strings). Never null. Example: '4'.
course_id	String	Genesis: copied (from the Beyond tenant-data import). Identifier of the course this (subject, grade) slot maps to, a UUID from beyond_subject_track.course_id. Never null (every track points at a course). This is a OneRoster course id resolved against one_roster_course, a different key space from the Courses dataset, which is the Caliper content graph: joining the two returns no rows and raises no error. Use course_title, carried inline, instead of a join. Example: 'e5f6a7b8-9c0d-4e1f-2a3b-4c5d6e7f8091'.
course_title	String	Genesis: copied (from the linked OneRoster course record). Title of the mapped course, from one_roster_course.title (the track row itself has no name, so its display name is the course's). Use this for "list the subject tracks" without joining any course dataset. Never null: the course join is an inner join, which also means a track whose OneRoster course row is missing is dropped from this view entirely rather than surfacing a null here, so the row count can be lower than the number of slots Beyond holds. Example: 'Grade 4 Reading'.
org_sourced_id	String	Genesis: copied (from the Beyond tenant-data import). Identifier of the organization this track overrides for, a UUID from beyond_subject_track.org_sourced_id. Null means this is the global default track that applies to every organization; non-null means a per-organization override. Join to the Schools/organizations datasets to resolve the org; org_name carries its name inline. See is_global for the boolean form and org_ids for visibility scoping. Example: '1a2b3c4d-1111-2222-3333-444455556666'.
org_name	String	Genesis: copied (from the linked OneRoster organization record). Name of the overriding organization, resolved by joining org_sourced_id to one_roster_organization.name. Null when org_sourced_id is null (a global default) or the organization row is absent. Only ever populated for override rows the caller is allowed to see (per org scoping), so a restricted caller never sees another org's name here. Example: 'Lincoln Elementary School'.
is_global	Boolean	Genesis: computed (org_sourced_id null test) from copied inputs (Beyond tenant-data import). True when this is the global default track (org_sourced_id is null), applying to every organization; false when it is a per-organization override. Derived as (org_sourced_id IS NULL). Filter is_global = true for the platform defaults. Never null. Example: true.
tenant_id	String	Genesis: copied (Beyond tenant id, stamped by the tenant-data import). Beyond tenant the track belongs to, a UUID from beyond_subject_track.tenant_id. Rows are stamped with the importing tenant; there is effectively one real tenant today, so this is mostly a provenance/audit column rather than a filter. Null when no tenant was stamped. Example: 'f6a7b8c9-0d1e-4f2a-3b4c-5d6e7f809112'.
org_ids	String	Genesis: computed (intersection of the override org with caller org scope). Array of UUIDs of the organizations this row is visible under, driving org-scoped row filtering. For a per-organization override this is org_sourced_id intersected with the caller's allowed scope; for a global-default row (org_sourced_id null) it is the empty array {} because the row is not org-restricted and is visible to everyone. Override rows are fail-closed: when the app.allowed_org_ids session setting is unset they are hidden, and when it is set only rows whose org intersects it are returned; global-default rows are always returned regardless. The array is never null (empty {} for a global row, or when nothing is in scope). Used for filtering rather than as a join key. Example: ['2b1a0c9d-8e7f-4a6b-b5c4-d3e2f1a09b8c'].

Join Keys

id: Primary key for the subject-track slot
course_id: OneRoster course id for the course that teaches this (subject, grade) slot; course_title is denormalized here so no join is needed. Do NOT join it to the Courses dataset, which is the Caliper content graph and a disjoint id space — that join returns no rows and raises no error
org_sourced_id: Organization the override applies to (null for global defaults); join to the Schools/organizations datasets
org_ids: Organizations this row is visible under (empty for global defaults); used for org-scoped filtering, not as a join key

Subjects

Purpose

Provides information about curriculum subjects available in the platform. This dataset enables analysis of subject-level organization, content categorization, and high-level curriculum structure.

This is the Caliper content-graph subject taxonomy. It is NOT the CASE subject taxonomy served by Case Subjects, and the two id spaces do not overlap — placement_results.subject_id and the subject_ids arrays on the enrollment views are CASE ids and will not be found here.

Fields

Field Name	Data Type	Description
id	String	Genesis: captured (from learning-app-published Caliper resources). Unique identifier of the subject, from the learning-app-published Caliper resource (caliper_assignable_digital_resource rows with media_type 'curriculum/subject'). Primary key; never null. Only Courses.subject_id references it, and that column is an unfiltered content-graph parent rather than a guaranteed subject, so an inner join on it can silently drop courses. Distinct from CASE subject ids (Case Subjects), which live in a separate key space. Example: '5a6b7c8d-9e0f-4a1b-8c2d-3e4f5a6b7c8d'.
title	String	Genesis: captured (from learning-app-published Caliper resource names). Human-readable subject title: the Caliper entity's name, falling back to its description, then to the literal 'N/A' when the publishing app supplied neither. Never null — the fallback guarantees a value; 'N/A' means the app published the resource without a name. The view deduplicates, so each (id, title) pair appears once. Example: 'Mathematics'.

Join Keys

id: Primary key. Only Courses references it, via subject_id, and even there the value is an unfiltered 'isPartOf' parent that may not be a subject at all (see that column's comment). Units and Lessons carry no subject column — traverse Resource Relations for their place in the hierarchy

Test Parents

Purpose

Bridge dataset linking tests to their parent curriculum resources, one row per (test, parent) pair, sourced from the Caliper 'isPartOf' relationship. A test's parent is not a single fixed level: it can be a topic, section, course, lesson, level, unit, or other curriculum resource, and a test can have more than one parent, so this is a true many-to-many relationship. The parent_type column tells you which kind of resource each parent is, so you join the matching dataset. Parents are restricted to assignable digital resources, but no media-type filter is applied: a parent that records no media type is still included and surfaces here with a null parent_type, so filter on parent_type rather than assuming every row names a level. Note: the same logical parent may currently appear under more than one identifier form in the source data, so a test may show duplicate-looking parents; identifier canonicalisation is tracked upstream.

Fields

Field Name	Data Type	Description
test_id	String	Genesis: captured (from the learning-app-published Caliper content graph). Identifier of the test, from caliper_assessment.id. Never null; with parent_id it forms the grain of this dataset, one row per test+parent pair. Join with the Tests dataset on test_id. Example: 'https://app.athena.learnwith.ai/lesson/009062fa-7bdb-4edd-a4e0-32d01cb770ea/mastery-practice'.
parent_id	String	Genesis: captured (from the Caliper isPartOf content-graph relationship). Identifier of a parent curriculum resource the test belongs to, a UUID (or a persistent URL) from caliper_assignable_digital_resource.id, resolved through the test's 'isPartOf' relationship. Never null. Use parent_type to pick the dataset to join (e.g. Courses when parent_type is 'curriculum/course', Lessons when 'curriculum/lesson'). Example: '88405b15-2d9f-11f1-82c5-0e986e48a3f9'.
parent_type	String	Genesis: captured (media type from the learning-app-published Caliper content graph). The parent resource's content type, copied from caliper_assignable_digital_resource.media_type. A free-form string rather than a fixed enumeration; observed values include 'curriculum/topic', 'curriculum/section', 'curriculum/course', 'curriculum/level', 'curriculum/lesson', and 'curriculum/unit'. Tells you which dataset to join parent_id against. Null when the source resource records no media type. Example: 'curriculum/topic'.

Join Keys

test_id: Join with the Tests dataset to get test details
parent_id: Join with the dataset matching parent_type (Courses, Units, Lessons, etc.) to get the parent's details

Tests

Purpose

Provides information about assessments, one row per test. This dataset enables analysis of test-level performance, assessment organization, and curriculum alignment. A test's place in the curriculum is not a single lesson: its parent can be a topic, section, course, lesson, level, or unit, and a test can have more than one parent, so the parent is not a column here; join the Test Parents dataset on id to resolve a test's parent(s) and their type.

Fields

Field Name	Data Type	Description
id	String	Genesis: captured (from learning-app-published Caliper resources). Identifier of the test (Caliper assessment), a UUID (or a persistent URL) from caliper_assessment.id. Never null; it is the grain of this dataset, one row per test. Join Question Tests, Test Parents, or Attempts (on resource_id) with this id. Not joinable to Assessment Results, which keys on the gradebook's assessment_line_item_id instead. Example: 'https://app.athena.learnwith.ai/lesson/009062fa-7bdb-4edd-a4e0-32d01cb770ea/mastery-practice'.
title	String	Genesis: captured (from learning-app-published Caliper resource names). Human-readable title of the test, taken from the Caliper entity's name, falling back to its description, then to the literal 'N/A' when neither is recorded. Never null: the value is 'N/A' exactly when both name and description are missing. Example: 'Mastery Practice' (or 'N/A').

Join Keys

id: Primary key. Join Question Tests for its questions, Test Parents for its place in the hierarchy, and Attempts on resource_id for per-student attempts. Assessment Results is NOT joinable on this id — that dataset is the gradebook chain and keys on assessment_line_item_id, a different key space

Unit Courses

Purpose

Bridge dataset linking curriculum units to the courses they belong to, one row per (unit, course) pair, sourced from the Caliper 'isPartOf' relationship. A unit can belong to more than one course, so this is a true many-to-many relationship and a unit may appear in several rows; query this dataset instead of expecting a single course on the Units dataset. Only relationships whose endpoints are real curriculum entities are included (a unit with no recorded course parent has no row here).

Fields

Field Name	Data Type	Description
unit_id	String	Genesis: captured (from Caliper isPartOf relationships). Identifier of the unit, a UUID from caliper_assignable_digital_resource.id (media_type 'curriculum/unit'). Never null; with course_id it forms the grain of this dataset, one row per unit+course pair. Join with the Units dataset on unit_id. Example: '1f977081-014b-4bc7-99eb-cf49b526f418'.
course_id	String	Genesis: captured (from Caliper isPartOf relationships). Identifier of a course the unit belongs to, a UUID from caliper_assignable_digital_resource.id (media_type 'curriculum/course'), resolved through the unit's 'isPartOf' relationship. Never null. A unit with multiple course parents yields one row per course. Join with the Courses dataset on course_id. Example: 'dd66c2a7-d200-11ec-829e-12bc5a81f3ad'.

Join Keys

unit_id: Join with the Units dataset to get unit details
course_id: Join with the Courses dataset to get course details

Units

Purpose

Provides information about curriculum units, one row per unit. This dataset enables analysis of unit-level organization and curriculum structure within the educational hierarchy. A unit can belong to more than one course, so the parent course is not a column here; join the Unit Courses dataset on id to resolve a unit's course(s).

Fields

Field Name	Data Type	Description
id	String	Genesis: captured (from learning-app-published Caliper resources). Identifier of the unit, a UUID from caliper_assignable_digital_resource.id (media_type 'curriculum/unit'). Never null; it is the grain of this dataset, one row per unit. Join with unit-level datasets (lessons, tests, Unit Courses) on this id. Example: '1f977081-014b-4bc7-99eb-cf49b526f418'.
title	String	Genesis: captured (from learning-app-published Caliper resource names). Human-readable title of the unit, taken from the Caliper entity's name, falling back to its description, then to the literal 'N/A' when neither is recorded. Never null: the value is 'N/A' exactly when both name and description are missing. Example: 'Unit 1: Foundations' (or 'N/A').

Join Keys

id: Primary key for joining with unit-related datasets (lessons, tests, questions, Unit Courses, etc.)

Waste Insights

Purpose

Provides detailed waste time insights for learning sessions, capturing individual waste events with their duration, type, timing, and the per-instance description shown in the Vault. Membership is decided by the type's is_waste flag and external visibility, NOT by category: any waste-flagged, externally visible type reaches this view whatever category it sits in, so do not assume a fixed category set. Only includes insights where is_visible is true. Insight types that require review (per insights_config) are only included if they have a TruePositive review result. Each record represents a single insight/distraction event identified during a session, enabling granular analysis of waste patterns, distraction types, and their impact on learning time.

When to use: this view is per-event grain (one row per individual waste incident), so use it for reading a distraction event's type, timing, and description. For totals, rankings, or per-student or per-session sums of wasted time, use sessions_waste instead: it is the per-session aggregate with overlapping events already merged and is far cheaper to aggregate.

Fields

Field Name	Data Type	Description
waste_duration_sec	Decimal	Genesis: computed (whole seconds between the insight timestamps) from judged inputs (per slug: Vision detection, this platform's own detectors, or the desktop client's idle timer). Duration of the waste/distraction event in whole seconds, computed as round(extract(epoch from (ended_at_time - started_at_time))). Never null (the insight's start and end timestamps are required). Use duration_sec (identical values). Example: 42.
duration_sec	Decimal	Genesis: computed (whole seconds between the insight timestamps) from judged inputs (per slug: Vision detection, this platform's own detectors, or the desktop client's idle timer). Duration of the waste/distraction event in whole seconds, computed as round(extract(epoch from (ended_at_time - started_at_time))). Never null (the insight's start and end timestamps are required). Prefer this column; waste_duration_sec carries identical values. Example: 42.
waste_type	String	Genesis: judged (per slug: Vision detection, this platform's own detectors, or the desktop client's idle timer; review-gated types human-confirmed). Slug identifying the kind of waste/distraction event, from insights_type.slug — every type included here has is_waste = TRUE. Never null. Use insight_type (identical values). Observed slugs include Engagement (AwayFromSeat, Idling, EyesOffScreen, Socializing, NonLearningContent, InappropriateExternalHelp, SupplementalLearning, IgnoringHelp, SkipAssessment, AbandonedAssessment, TopicShopping), Proctoring (ProctoringHelpFromAnotherPerson, ProctoringUnauthorizedAppUse, ProctoringUnauthorizedDeviceUse), and Cheating (CheatingHelpFromAnotherPerson, CheatingUnauthorizedAppUse, CheatingUnauthorizedDeviceUse). Treat that list as open, not closed: the view filters on is_waste and visibility with no category predicate, so a waste-flagged type in any category reaches this column, and new types and categories appear without code changes — derive the live slug set at read time. Producers differ by slug and are not all vision: Idling is the desktop client's idle-timeout heuristic (default 3 minutes, per-app overridable) whose timestamps the backend trusts, while TopicShopping, IgnoringHelp, SkipAssessment and AbandonedAssessment come from this platform's own detectors; the remainder are Vision-judged. Example: 'NonLearningContent'.
insight_type	String	Genesis: judged (per slug: Vision detection, this platform's own detectors, or the desktop client's idle timer; review-gated types human-confirmed). Slug identifying the kind of waste/distraction event, from insights_type.slug — every type included here has is_waste = TRUE. Never null. Prefer this column; waste_type carries identical values. Observed slugs include Engagement (AwayFromSeat, Idling, EyesOffScreen, Socializing, NonLearningContent, InappropriateExternalHelp, SupplementalLearning, IgnoringHelp, SkipAssessment, AbandonedAssessment, TopicShopping), Proctoring (ProctoringHelpFromAnotherPerson, ProctoringUnauthorizedAppUse, ProctoringUnauthorizedDeviceUse), and Cheating (CheatingHelpFromAnotherPerson, CheatingUnauthorizedAppUse, CheatingUnauthorizedDeviceUse). Treat that list as open, not closed: the view filters on is_waste and visibility with no category predicate, so a waste-flagged type in any category reaches this column, and new types and categories appear without code changes — derive the live slug set at read time. Producers differ by slug and are not all vision: Idling is the desktop client's idle-timeout heuristic (default 3 minutes, per-app overridable) whose timestamps the backend trusts, while TopicShopping, IgnoringHelp, SkipAssessment and AbandonedAssessment come from this platform's own detectors; the remainder are Vision-judged. Example: 'NonLearningContent'.
session_id	String	Genesis: captured (session id minted at capture). Identifier of the session this waste event occurred in, from insights_session.caliper_session_id. An opaque identifier string, not a uuid: production values are a mix of 'https://timeback.com/sessions/ ' URLs and bare UUID strings, so never cast it to uuid. Never null. Joins to sessions.id. Example: 'https://timeback.com/sessions/2026-07-23T11-02-18-750Z-ufduv99q0t8'.
started_at_time	DateTime	Genesis: judged (span boundaries from Vision, from platform detectors, or client-reported for the idle timer, per slug, with merge gaps). Timestamp the waste event started, in UTC ISO-8601 format, from insights_insight.started_at_time. Never null. Always ≤ ended_at_time. Use start_time (identical values). Example: '2026-06-17T09:50:12.000Z'.
start_time	DateTime	Genesis: judged (span boundaries from Vision, from platform detectors, or client-reported for the idle timer, per slug, with merge gaps). Timestamp the waste event started, in UTC ISO-8601 format, from insights_insight.started_at_time. Never null. Always ≤ end_time. Prefer this column (aligned with the sessions dataset); started_at_time carries identical values. Example: '2026-06-17T09:50:12.000Z'.
ended_at_time	DateTime	Genesis: judged (span boundaries from Vision, from platform detectors, or client-reported for the idle timer, per slug, with merge gaps). Timestamp the waste event ended, in UTC ISO-8601 format, from insights_insight.ended_at_time. Never null. Always ≥ started_at_time. Use end_time (identical values). Example: '2026-06-17T09:50:54.000Z'.
end_time	DateTime	Genesis: judged (span boundaries from Vision, from platform detectors, or client-reported for the idle timer, per slug, with merge gaps). Timestamp the waste event ended, in UTC ISO-8601 format, from insights_insight.ended_at_time. Never null. Always ≥ start_time. Prefer this column (aligned with the sessions dataset); ended_at_time carries identical values. Example: '2026-06-17T09:50:54.000Z'.
description	String	Genesis: copied (from the insight reason text, the Vault-facing description). Per-instance contextual description of the waste event, sourced from insights_insight.reason — the same human-readable text shown in the Vault for this insight. Never null. Example, for a TopicShopping event: 'You started "Lesson A" but switched to "Lesson B" before finishing — that time was wasted.'
org_ids	String	Genesis: computed (roster org membership intersected with caller scope) from copied OneRoster roles. Array of UUIDs of the organizations this row is visible under: the session's student's organizations (from public.one_roster_role) intersected with the caller's allowed scope. Drives org-scoped row filtering: the view is fail-closed, so when the app.allowed_org_ids session setting is unset the view returns no rows, and when it is set only rows whose organizations intersect it are returned. The emitted array is that intersection, so it never exceeds the caller's scope and may be a subset of the student's full membership (and may be empty for an internal/unrestricted caller). The array itself is never null: it is the empty array {} when nothing is in scope. Each element joins to an organization id. Example: ['1a2b3c4d-1111-2222-3333-444455556666'].

Join Keys

session_id: Join with Sessions dataset to get session details, timing, and student information
org_ids: Array of organization IDs the user belongs to; used for org-scoped filtering

QuickSight Setup

MCP Tools