Data Model & Database Schema

Part of: Pickatale Master Build Wiki | Version: v1.7 | Last Updated: 2026-04-18

8. Data Model

Source: Code audit 2026-04-18 | Status: Confirmed (UNVERIFIED fields noted)

Universal ID Rule

learner_id (UUID VARCHAR 36) is the single universal child identifier across ALL services. Integer IDs must never be used for cross-service references.

8.1 Learner (Learner Profile DB)

Field	Type	Required	Status
learner_id	UUID VARCHAR(36)	✅	Confirmed
reading_level	DECIMAL(4,2)	✅	Confirmed
reading_level_history	JSON [{level, date}]	—	Confirmed
wcpm_history	JSON [{wcpm, date}]	—	Confirmed
cefr_level	VARCHAR(10)	—	Confirmed
interest_tags	JSON string[]	—	Confirmed
curriculum_territory	VARCHAR	—	Confirmed
age	INT	—	Confirmed
language	VARCHAR(10)	—	Confirmed
gdpr_consent	BOOLEAN	✅	Confirmed

8.2 Telemetry Event

Field	Type	Required	Status
event_id	UUID	✅	Confirmed
learner_id	UUID	✅	Confirmed
session_id	UUID	✅	Confirmed
event_type	ENUM	✅	Confirmed
book_id	VARCHAR	—	Confirmed
page_number	INT	—	Confirmed
word	VARCHAR	—	Confirmed
time_on_page_ms	INT	—	Confirmed
client_ts	BIGINT (Unix ms)	✅	Confirmed
created_at	TIMESTAMP	✅	Confirmed

8.3 Learner Bot Memory

Field	Type	Required	Status
id	INT	✅	Confirmed
learner_id	UUID	✅	Confirmed
memory_type	VARCHAR	✅	Confirmed
value	TEXT/JSON	✅	Confirmed
confidence	DECIMAL(3,2)	—	Confirmed
created_at	TIMESTAMP	✅	Confirmed

memory_type values: assessment_result · curriculum_state · reading_pattern · engagement_signal · vocabularyGaps

8.4 Vocabulary Gaps

Field	Type	Required	Status
id	INT	✅	Confirmed
learner_id	UUID	✅	Confirmed
word	VARCHAR (lowercase)	✅	Confirmed
tap_count	INT	✅	Confirmed
last_tapped	TIMESTAMP	—	Confirmed

8.5 Curriculum Objective (CM DB)

Field	Type	Required	Status
id	INT	✅	Confirmed
territory	VARCHAR	✅	Confirmed
year_level	INT	✅	Confirmed
subject	VARCHAR	✅	Confirmed
title	VARCHAR	✅	Confirmed
description	TEXT	—	Confirmed
difficulty	INT	—	Confirmed
vocabulary	JSON string[]	—	Confirmed
story_potential_score	DECIMAL(3,2)	—	Confirmed

8.6 Teacher / Class / Student

Field	Type	Required	Status
teacher_id	INT	✅	Confirmed
teacher_email	VARCHAR	✅	Confirmed
school_name	VARCHAR	—	Confirmed
class_id	INT	✅	Confirmed
class_name	VARCHAR	✅	Confirmed
year_level	INT	✅	Confirmed
student_id	INT	✅	Confirmed
learner_service_id	UUID	—	Confirmed
student_username	VARCHAR	✅	Confirmed
pin	VARCHAR(4)	✅	Confirmed — shown once only
student_name	VARCHAR	✅	Confirmed

26. Database Tables (Complete List)

Source: Code audit + spec | Status: Confirmed (tables marked MUST BUILD don't exist yet)

Account Center DB (`account_center`)

Table	Status	Key Fields
`users`	✅ Exists	id, uuid, email, password_hash, role, state, email_verified, dpa_accepted_at, locked_until, failed_attempts, created_at
`sessions`	✅ Exists	id, user_id, token_hash, expires_at, invalidated_at, ip, user_agent
`password_reset_tokens`	✅ Exists	id, user_id, token_hash, expires_at, used_at
`schools`	✅ Exists	id, name, country, admin_user_id, state, auto_approve_parent_claims
`invites`	MUST BUILD	id, token_hash, inviter_id, target_email, target_role, school_id, expires_at, used_at
`email_log`	MUST BUILD	id, user_id, template, sent_at, status, sendgrid_message_id
`audit_log`	MUST BUILD	id, actor_id, action, target_type, target_id, metadata_json, ip, created_at, impersonated
`impersonation_sessions`	MUST BUILD	id, admin_id, target_id, started_at, ended_at

Billing DB (within account_center or separate)

Table	Status	Key Fields
`subscriptions`	MUST BUILD	id, user_id, school_id, state, tier, trial_ends_at, current_period_start, current_period_end, grace_ends_at, cancelled_at, stripe_subscription_id, stripe_customer_id
`entitlement_grants`	MUST BUILD	id, user_id, granted_by, access_level, expires_at, reason
`payments`	MUST BUILD	id, subscription_id, amount, currency, status, stripe_payment_id, created_at

Teacher Portal DB (`teacher_portal`)

Table	Status	Key Fields
`teachers`	✅ Exists	id, user_id (FK account_center.users), school_id
`classes`	✅ Exists	id, class_name, year_level, teacher_id, school_id, state, curriculum_territory
`students`	✅ Exists	id, uuid (learner_id), name, username, pin_hash, class_id, teacher_id, school_id, state, parent_id, placement_test_completed, placement_inferred, failed_attempts, locked, entitlement
`assignments`	MUST BUILD	id, class_id, book_id, assigned_by, due_date, state
`student_assignments`	MUST BUILD	id, student_id, assignment_id, state
`teacher_notifications`	MUST BUILD	id, teacher_id, type, child_name, message, action_url, read, created_at
`parent_claims`	MUST BUILD	id, parent_id, student_id, state, created_at

38. Full Database Schema (DDL)

Source: (D) + code audit | Status: Confirmed (tables verified in code) or MUST BUILD (specified, not yet built)

38.1 Account Center DB (`account_center`)

CREATE TABLE users (
  id               INT PRIMARY KEY AUTO_INCREMENT,
  uuid             VARCHAR(36) NOT NULL UNIQUE DEFAULT (UUID()),
  name             VARCHAR(255) NOT NULL,
  email            VARCHAR(255) NOT NULL UNIQUE,
  password_hash    VARCHAR(255) NOT NULL,
  role             ENUM('teacher','parent','school_admin','platform_admin') NOT NULL,
  state            ENUM('pending_verification','active','suspended','archived') NOT NULL DEFAULT 'pending_verification',
  email_verified   BOOLEAN NOT NULL DEFAULT FALSE,
  school_id        INT NULL,
  failed_attempts  INT NOT NULL DEFAULT 0,
  locked_until     DATETIME NULL,
  created_at       DATETIME NOT NULL DEFAULT NOW(),
  updated_at       DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  INDEX(email), INDEX(school_id), INDEX(state)
);

CREATE TABLE schools (
  id               INT PRIMARY KEY AUTO_INCREMENT,
  name             VARCHAR(255) NOT NULL,
  country          VARCHAR(100) NOT NULL DEFAULT '',
  admin_user_id    INT NULL,
  state            ENUM('pending','active','suspended','archived') NOT NULL DEFAULT 'pending',
  auto_approve_parent_claims BOOLEAN NOT NULL DEFAULT FALSE,
  created_at       DATETIME NOT NULL DEFAULT NOW(),
  updated_at       DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW()
);

CREATE TABLE sessions (
  id               INT PRIMARY KEY AUTO_INCREMENT,
  user_id          INT NULL,                          -- NULL for child sessions (use student_id)
  student_id       INT NULL,                          -- child sessions only
  role             ENUM('teacher','parent','school_admin','platform_admin','child') NOT NULL,
  token_hash       VARCHAR(64) NOT NULL UNIQUE,       -- sha256(session_uuid)
  expires_at       DATETIME NOT NULL,
  invalidated_at   DATETIME NULL,
  ip               VARCHAR(45),
  user_agent       VARCHAR(500),
  created_at       DATETIME NOT NULL DEFAULT NOW(),
  INDEX(token_hash), INDEX(user_id), INDEX(student_id)
);

CREATE TABLE email_verification_tokens (
  id               INT PRIMARY KEY AUTO_INCREMENT,
  user_id          INT NOT NULL,
  token_hash       VARCHAR(64) NOT NULL UNIQUE,
  expires_at       DATETIME NOT NULL,
  used_at          DATETIME NULL,
  created_at       DATETIME NOT NULL DEFAULT NOW()
);

CREATE TABLE password_reset_tokens (
  id               INT PRIMARY KEY AUTO_INCREMENT,
  user_id          INT NOT NULL,
  token_hash       VARCHAR(64) NOT NULL UNIQUE,
  expires_at       DATETIME NOT NULL,
  used_at          DATETIME NULL,
  created_at       DATETIME NOT NULL DEFAULT NOW()
);

CREATE TABLE invites (
  id               INT PRIMARY KEY AUTO_INCREMENT,
  inviter_id       INT NOT NULL,
  target_email     VARCHAR(255) NOT NULL,
  target_role      ENUM('teacher','school_admin') NOT NULL,
  school_id        INT NULL,
  token_hash       VARCHAR(64) NOT NULL UNIQUE,
  state            ENUM('pending','accepted','expired','failed') NOT NULL DEFAULT 'pending',
  resend_count     INT NOT NULL DEFAULT 0,
  expires_at       DATETIME NOT NULL,
  used_at          DATETIME NULL,
  created_at       DATETIME NOT NULL DEFAULT NOW()
);

CREATE TABLE audit_log (
  id               BIGINT PRIMARY KEY AUTO_INCREMENT,
  action           VARCHAR(100) NOT NULL,
  actor_id         INT NULL,                          -- user_id or student_id
  actor_role       VARCHAR(50) NULL,
  target_type      VARCHAR(50) NULL,
  target_id        VARCHAR(100) NULL,
  metadata         JSON NULL,
  ip               VARCHAR(45) NULL,
  created_at       DATETIME NOT NULL DEFAULT NOW(),
  INDEX(actor_id), INDEX(action), INDEX(created_at)
);

38.2 Teacher Portal DB (`teacher_portal`)

CREATE TABLE classes (
  id                     INT PRIMARY KEY AUTO_INCREMENT,
  class_name             VARCHAR(255) NOT NULL,
  year_level             INT NOT NULL,
  teacher_id             INT NOT NULL,
  school_id              INT NULL,
  curriculum_territory   VARCHAR(100) NULL,
  state                  ENUM('active','archived') NOT NULL DEFAULT 'active',
  archived_at            DATETIME NULL,
  created_at             DATETIME NOT NULL DEFAULT NOW(),
  updated_at             DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  INDEX(teacher_id), INDEX(school_id)
);

CREATE TABLE students (
  id                        INT PRIMARY KEY AUTO_INCREMENT,
  uuid                      VARCHAR(36) NOT NULL UNIQUE DEFAULT (UUID()),  -- learner_id across all services
  name                      VARCHAR(255) NOT NULL,
  username                  VARCHAR(50) NOT NULL UNIQUE,
  pin_hash                  VARCHAR(255) NOT NULL,                         -- bcrypt cost 10
  class_id                  INT NULL,
  teacher_id                INT NULL,
  school_id                 INT NULL,
  year_level                INT NULL,
  language                  VARCHAR(10) NOT NULL DEFAULT 'en',
  state                     ENUM('created','active','inactive','archived') NOT NULL DEFAULT 'created',
  placement_test_completed  BOOLEAN NOT NULL DEFAULT FALSE,
  failed_attempts           INT NOT NULL DEFAULT 0,
  locked                    BOOLEAN NOT NULL DEFAULT FALSE,
  parent_count              INT NOT NULL DEFAULT 0,
  entitlement_tier          ENUM('free','trial','teacher_paid','enterprise','gifted') NOT NULL DEFAULT 'free',  -- cached from teacher_licenses; see billing page
  miles                     DECIMAL(10,2) NOT NULL DEFAULT 0,
  tokens                    INT NOT NULL DEFAULT 0,
  created_at                DATETIME NOT NULL DEFAULT NOW(),
  updated_at                DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  INDEX(username), INDEX(class_id), INDEX(teacher_id), INDEX(uuid)
);

CREATE TABLE students_parents (
  id           INT PRIMARY KEY AUTO_INCREMENT,
  student_id   INT NOT NULL,
  parent_id    INT NOT NULL,               -- users.id in Account Center DB
  linked_at    DATETIME NOT NULL DEFAULT NOW(),
  UNIQUE(student_id, parent_id)
);

CREATE TABLE parent_claims (
  id           INT PRIMARY KEY AUTO_INCREMENT,
  parent_id    INT NOT NULL,
  student_id   INT NOT NULL,
  state        ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  approved_at  DATETIME NULL,
  rejected_at  DATETIME NULL,
  created_at   DATETIME NOT NULL DEFAULT NOW(),
  UNIQUE(parent_id, student_id)
);

CREATE TABLE pin_reveal_tokens (
  id           INT PRIMARY KEY AUTO_INCREMENT,
  student_id   INT NOT NULL,
  token        VARCHAR(36) NOT NULL UNIQUE,   -- UUID, single-use
  pin_plaintext VARCHAR(4) NOT NULL,
  expires_at   DATETIME NOT NULL,             -- now() + 10 minutes
  created_at   DATETIME NOT NULL DEFAULT NOW()
);

CREATE TABLE teacher_notifications (
  id           INT PRIMARY KEY AUTO_INCREMENT,
  teacher_id   INT NOT NULL,
  type         VARCHAR(100) NOT NULL,         -- 'parent_claim_request','child_locked_pin','low_score','inactive'
  payload      JSON NOT NULL,
  read_at      DATETIME NULL,
  created_at   DATETIME NOT NULL DEFAULT NOW(),
  INDEX(teacher_id, read_at)
);

-- Membership table: school → users (teachers/admins at school)
CREATE TABLE memberships (
  id           INT PRIMARY KEY AUTO_INCREMENT,
  school_id    INT NOT NULL,
  user_id      INT NOT NULL,                  -- Account Center users.id
  role         ENUM('teacher','school_admin') NOT NULL,
  state        ENUM('active','inactive') NOT NULL DEFAULT 'active',
  joined_at    DATETIME NOT NULL DEFAULT NOW(),
  UNIQUE(school_id, user_id)
);

38.3 Reader App DB (`reader_app`)

CREATE TABLE reading_sessions (
  id              INT PRIMARY KEY AUTO_INCREMENT,
  session_id      VARCHAR(36) NOT NULL UNIQUE,      -- UUID, client-generated
  learner_id      VARCHAR(36) NOT NULL,              -- students.uuid
  book_id         VARCHAR(100) NOT NULL,
  state           ENUM('open','completed','abandoned') NOT NULL DEFAULT 'open',
  pages_read      INT NOT NULL DEFAULT 0,
  total_pages     INT NULL,
  words_read      INT NOT NULL DEFAULT 0,
  started_at      DATETIME NOT NULL DEFAULT NOW(),
  ended_at        DATETIME NULL,
  last_event_at   DATETIME NULL,
  fk_level        DECIMAL(4,2) NULL,                -- level at time of reading
  created_at      DATETIME NOT NULL DEFAULT NOW(),
  INDEX(learner_id), INDEX(book_id), INDEX(state)
);

-- quiz_attempts: comprehension quiz results
CREATE TABLE quiz_attempts (
  id                    INT PRIMARY KEY AUTO_INCREMENT,
  learner_id            VARCHAR(36) NOT NULL,
  book_id               VARCHAR(100) NOT NULL,
  session_id            VARCHAR(36) NOT NULL,
  score_pct             DECIMAL(5,2) NOT NULL,
  questions_total       INT NOT NULL,
  questions_correct     INT NOT NULL,
  fk_level              DECIMAL(4,2) NULL,
  level_recommendation  ENUM('drop','hold','raise') NULL,
  created_at            DATETIME NOT NULL DEFAULT NOW(),
  INDEX(learner_id)
);

-- placement_results: initial placement test scores
CREATE TABLE placement_results (
  id           INT PRIMARY KEY AUTO_INCREMENT,
  learner_id   VARCHAR(36) NOT NULL UNIQUE,
  fk_level     DECIMAL(4,2) NOT NULL,
  raw_scores   JSON NULL,
  completed_at DATETIME NOT NULL DEFAULT NOW()
);

38.4 Telemetry DB (`telemetry`)

CREATE TABLE events (
  id              BIGINT PRIMARY KEY AUTO_INCREMENT,
  event_id        VARCHAR(36) NOT NULL UNIQUE,        -- UUID, client-generated (dedup key)
  learner_id      VARCHAR(36) NOT NULL,
  session_id      VARCHAR(36) NOT NULL,
  event_type      ENUM('book_opened','page_turned','word_tapped','session_ended',
                       'quiz_completed','placement_completed','speedread_started',
                       'speedread_ended','book_abandoned') NOT NULL,
  book_id         VARCHAR(100) NULL,
  page_number     INT NULL,
  word            VARCHAR(255) NULL,
  time_on_page_ms INT NULL,
  payload         JSON NULL,                           -- catch-all for future fields
  client_ts       BIGINT NOT NULL,                     -- Unix milliseconds
  created_at      DATETIME NOT NULL DEFAULT NOW(),
  INDEX(learner_id), INDEX(session_id), INDEX(event_type), INDEX(created_at)
  -- Retention: rows with created_at < now()-90d deleted by cron at 03:00 UTC
);

CREATE TABLE sessions_summary (
  id                    INT PRIMARY KEY AUTO_INCREMENT,
  session_id            VARCHAR(36) NOT NULL UNIQUE,
  learner_id            VARCHAR(36) NOT NULL,
  book_id               VARCHAR(100) NOT NULL,
  pages_total           INT NOT NULL,
  pages_read            INT NOT NULL,
  avg_time_per_page_ms  INT NOT NULL,
  slow_pages            JSON NOT NULL,                -- int[]
  word_tap_count        INT NOT NULL DEFAULT 0,
  created_at            DATETIME NOT NULL DEFAULT NOW()
);

38.5 Learner Bot DB (`learner_bot`)

CREATE TABLE learner_memories (
  id           INT PRIMARY KEY AUTO_INCREMENT,
  learner_id   VARCHAR(36) NOT NULL,
  memory_type  VARCHAR(100) NOT NULL,
  value        JSON NOT NULL,
  confidence   DECIMAL(3,2) NOT NULL DEFAULT 1.00,
  created_at   DATETIME NOT NULL DEFAULT NOW(),
  updated_at   DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  INDEX(learner_id, memory_type)
);

CREATE TABLE vocabulary_gaps (
  id           INT PRIMARY KEY AUTO_INCREMENT,
  learner_id   VARCHAR(36) NOT NULL,
  word         VARCHAR(255) NOT NULL,
  tap_count    INT NOT NULL DEFAULT 1,
  last_tapped  DATETIME NOT NULL DEFAULT NOW(),
  UNIQUE(learner_id, word)
);

CREATE TABLE assessment_results (
  id                   INT PRIMARY KEY AUTO_INCREMENT,
  learner_id           VARCHAR(36) NOT NULL,
  book_id              VARCHAR(100) NOT NULL,
  score_pct            DECIMAL(5,2) NOT NULL,
  fk_level             DECIMAL(4,2) NULL,
  level_recommendation ENUM('drop','hold','raise') NULL,
  created_at           DATETIME NOT NULL DEFAULT NOW(),
  INDEX(learner_id)
);

CREATE TABLE nightly_reports (
  id           INT PRIMARY KEY AUTO_INCREMENT,
  learner_id   VARCHAR(36) NOT NULL,
  report_type  ENUM('teacher','parent') NOT NULL,
  content_json JSON NOT NULL,
  generated_at DATETIME NOT NULL DEFAULT NOW(),
  INDEX(learner_id, report_type)
);

CREATE TABLE curriculum_state (
  id           INT PRIMARY KEY AUTO_INCREMENT,
  learner_id   VARCHAR(36) NOT NULL UNIQUE,
  territory    VARCHAR(100) NOT NULL,
  year_level   INT NOT NULL,
  mastered     JSON NOT NULL DEFAULT '[]',            -- objective IDs []
  in_progress  JSON NOT NULL DEFAULT '[]',
  updated_at   DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW()
);

38.6 Billing Tables (Account Center DB — continued)

-- Already in Section 29. Repeated here for completeness.

CREATE TABLE invoices (
  id                  INT PRIMARY KEY AUTO_INCREMENT,
  subscription_id     INT NOT NULL,
  stripe_invoice_id   VARCHAR(255) NULL,
  amount_cents        INT NOT NULL,
  currency            CHAR(3) NOT NULL DEFAULT 'USD',
  state               ENUM('draft','open','paid','void','uncollectible') NOT NULL,
  due_date            DATETIME NULL,
  paid_at             DATETIME NULL,
  period_start        DATETIME NULL,
  period_end          DATETIME NULL,
  invoice_pdf_url     VARCHAR(500) NULL,
  created_at          DATETIME NOT NULL DEFAULT NOW(),
  INDEX(subscription_id)
);

CREATE TABLE email_log (
  id           INT PRIMARY KEY AUTO_INCREMENT,
  user_id      INT NULL,
  template     VARCHAR(100) NOT NULL,
  recipient    VARCHAR(255) NOT NULL,
  status       ENUM('sent','failed','bounced') NOT NULL,
  sendgrid_id  VARCHAR(255) NULL,
  created_at   DATETIME NOT NULL DEFAULT NOW(),
  INDEX(user_id)
);

36. Source of Truth

Source: (D) | Status: Confirmed as spec. These rules are non-negotiable. Claude Code must not guess where data lives.**

Every service has one authoritative owner per data type. Services may cache, but must write to the owner.

36.1 Identity

Owner: Account Center (account.readingtester.com, port 3126)

Tables: users, sessions, password_reset_tokens, email_verification_tokens, schools, invites, audit_log

Who reads it:

Teacher Portal: validates uc_session via GET /api/auth/session
Reader App: validates uc_session via GET /api/auth/session
Parent Portal: validates uc_session
All services: call Account Center for session validation — they do NOT maintain their own session stores

Rule: No service may create or store a user identity record except Account Center. Students (child accounts) are an exception — they live in Teacher Portal students table with a UUID (learner_id) that is the cross-service identity.

36.2 Entitlement

Owner: Account Center billing tables (subscriptions, entitlement_grants)

Resolved by: GET /api/billing/entitlement?user_id=N (Account Center)

Who calls it:

Teacher Portal: on every protected page load
Reader App: on book open (to enforce 50-book free limit and feature gates)
Learner Bot: before running nightly cycle (skip if tier=free)
All other services: call Account Center — they do NOT maintain entitlement logic locally

Rule: Every feature-gated endpoint must call checkEntitlement() server-side. Client-side hints are cosmetic only. On Account Center timeout → fail-open to free tier (never block reading).

36.3 Child Ownership (Class Membership)

Owner: Teacher Portal (students table, classes table)

Fields that define ownership:

students.class_id       → current class
students.teacher_id     → current teacher (denormalized from class)
students.school_id      → current school
students.parent_count   → number of linked parents
students_parents        → parent↔child links

Who reads it:

Teacher Portal: owns all writes
Parent Portal: reads via GET /api/v1/parent/children (Teacher Portal API)
Learner Bot: reads via GET /api/v1/bot/:learner_id/status — gets class_id, teacher_id context
Account Center: never reads student ownership (no cross-DB joins)

Rule: Ownership is always resolved from Teacher Portal. No other service may move or reassign a child.

36.4 Reading Progress

Owner: Reader App server (reader_app DB, reading_sessions table)

Key fields:

reading_sessions.learner_id     → who read
reading_sessions.book_id        → what they read
reading_sessions.state          → open / completed / abandoned
reading_sessions.words_read     → for miles calculation
reading_sessions.pages_read
students.miles                  → accumulated miles (denormalized for speed)
students.tokens                 → accumulated tokens

Who reads it:

Learner Bot: GET /api/internal/learner/:id (Reader App internal endpoint, X-Internal-Key) — gets books_read, total_miles, reading_level
Teacher Portal: fetches via Learner Bot status endpoint (not direct DB)
Parent Portal: fetches via Learner Bot status endpoint

Rule: Miles and tokens are the canonical count in students.miles and students.tokens in Reader App DB. The Learner Bot's cached value is a copy for reporting — Reader App is the source.

36.5 Telemetry vs LRS

Telemetry Service (port 3110) = raw behavioral events for bot processing

events table: page_turned, word_tapped, session_ended, quiz_completed
Retention: 90 days (cron at 03:00 UTC deletes older rows)
Consumer: Learner Bot (aggregates on session_end via telemetry API)
Purpose: real-time behavioral signals for gap detection

LRS (port 3111) = permanent xAPI compliance record

Stores xAPI statements (actor, verb, object, result)
Retention: indefinite (GDPR deletion on request)
Consumer: compliance/analytics, not Learner Bot
Purpose: SOC2/ISO27001 audit trail, interoperability with LMS

Rule: Telemetry feeds the bot. LRS is the compliance record. Do NOT use LRS for bot logic — it is too slow and structured differently. Do NOT use Telemetry for compliance — it has no xAPI structure and expires.

36.6 Curriculum Objectives

Owner: Curriculum Mapper (cm.readingtester.com)

API: GET /api/v1/objectives?territory=England&year_level=3&subject=English

Who reads it:

Learner Bot Orchestrator: to select target objectives for nightly cycle
CM dashboard: reads and writes its own DB

Rule: No service hardcodes curriculum content. Always call CM REST API. CM is the only service that writes to curriculum_objectives table.

36.7 Learner Profile (Bot Memory)

Owner: Learner Bot (learner-bot, port 3120)

Tables: learner_memories, vocabulary_gaps, assessment_results, curriculum_state, nightly_reports

Who reads it:

Teacher Portal: GET /api/v1/bot/:learner_id/status (X-Internal-Key)
Parent Portal: GET /api/v1/bot/:learner_id/digest (X-Internal-Key)
Adaptive Engine: does not read bot memory directly — receives leveling request with target FK

Who writes it:

Telemetry (session end): fires vocab-taps + session-summary to bot endpoints
Reader App quiz result: POST /bot/:learner_id/quiz-result
Bot nightly cycle: writes new memories, updates curriculum_state, generates reports
Fluency Assessment: POST /api/v1/bot/:learner_id/fluency-result (UNVERIFIED — not yet wired)

Rule: Teacher Portal and Parent Portal MUST read learner data via Learner Bot API — never via direct DB access. Learner Bot is the single aggregator.

36.8 Adaptive / Leveled Text

Owner: Adaptive Content Engine (adapt.readingtester.com, port 3119)

Cache table: leveled_text_cache (book_id + page_number + rounded_level → leveled_text)

Rule: Reader App calls Adaptive Engine for every page render at child's FK level. Adaptive Engine owns the cache. Reader App does not cache leveled text locally.

Data Model & Database Schema

8. Data Model

Universal ID Rule

8.1 Learner (Learner Profile DB)

8.2 Telemetry Event

8.3 Learner Bot Memory

8.4 Vocabulary Gaps

8.5 Curriculum Objective (CM DB)

8.6 Teacher / Class / Student

26. Database Tables (Complete List)

Account Center DB (account_center)

Billing DB (within account_center or separate)

Teacher Portal DB (teacher_portal)

38. Full Database Schema (DDL)

38.1 Account Center DB (account_center)

38.2 Teacher Portal DB (teacher_portal)

38.3 Reader App DB (reader_app)

38.4 Telemetry DB (telemetry)

38.5 Learner Bot DB (learner_bot)

38.6 Billing Tables (Account Center DB — continued)

36. Source of Truth

36.1 Identity

36.2 Entitlement

36.3 Child Ownership (Class Membership)

36.4 Reading Progress

36.5 Telemetry vs LRS

36.6 Curriculum Objectives

36.7 Learner Profile (Bot Memory)

36.8 Adaptive / Leveled Text

Account Center DB (`account_center`)

Teacher Portal DB (`teacher_portal`)

38.1 Account Center DB (`account_center`)

38.2 Teacher Portal DB (`teacher_portal`)

38.3 Reader App DB (`reader_app`)

38.4 Telemetry DB (`telemetry`)

38.5 Learner Bot DB (`learner_bot`)