internal active Last verified 2026-04-09

TopoloLearn Schema Plan

Schema and migration plan for the TopoloLearn D1 data model, indexes, and state transitions.

Owners
product-platform
Source Repos
PlatformApplications/TopoloLearn, PlatformApplications/TopoloDocs
Systems
TopoloLearn
Tags
schema, d1, learning

What It Is

This page records the initial D1 schema design for TopoloLearn, the migration set, core entity groupings, indexes, state transitions, and the tenant-safe access expectations attached to the model.

Architecture

The schema uses a shared-database, explicit-scope model. Every business record is tied back to a tenant, brand-owned records also carry brand scope, and assessment plus certification state is modeled with first-class relational tables rather than implicit JSON blobs.

Runtime Surfaces

  • D1 migration and seed assets in packages/database
  • Worker repository access in apps/api
  • browser views that consume the resulting domain model

API Reference

The schema supports the current runtime-resolution, overview, assessment review, and certificate issuance routes, and it defines the persistent contract future repository code must maintain.

Migration Files

  • PlatformApplications/TopoloLearn/packages/database/migrations/0001_initial.sql
  • PlatformApplications/TopoloLearn/packages/database/seeds/0001_demo.sql

Core Entity Groups

Control plane:

  • tenants
  • brands
  • brand_domains
  • users
  • tenant_memberships

Learning:

  • programmes
  • modules
  • lessons
  • resources
  • cohorts
  • live_sessions
  • offers
  • enrolments

Assessment:

  • assessment_rubrics
  • artefact_requirements
  • submissions
  • submission_versions
  • reviews
  • evidence_packs

Enterprise:

  • organisations
  • seat_packs
  • seat_assignments

Certification and telemetry:

  • certificate_templates
  • certificates
  • audit_events
  • analytics_events

Key Indexes

  • tenant and brand slugs
  • hostname lookup
  • programme and offer slugs per brand
  • module and lesson ordering
  • cohort status and schedule filtering
  • enrolment uniqueness by cohort and learner
  • submission lookup by tenant, brand, cohort, and learner
  • certificate verification-code uniqueness
  • audit and analytics time-series indexes

Foreign Key Strategy

  • all business tables point back to tenants
  • brand-owned rows point to brands
  • programme and cohort children cascade through the owning hierarchy
  • submission, review, evidence-pack, and certificate state keeps explicit foreign keys instead of JSON-only relationships

Auth and Permissions

Auth does not live in D1, but repository access must always start from authenticated tenant scope. TopoloLearn role assignments and enrolment relationships determine whether the caller can manage a brand, review a submission, or view a learner surface.

Tenant-Safe Access Model

The database is shared, but every query is expected to carry explicit tenant scope. A worker helper should reject any request whose requested tenant scope does not match the authenticated org slug before repository methods run.

Data Ownership

The TopoloLearn schema owns tenants, brands, domains, learning objects, assessment objects, enterprise-seat objects, certificates, audit records, and analytics records. Shared identity remains outside this schema in Topolo Auth.

Deployments

The schema is intended for a shared D1 deployment in v1, with repository and binding boundaries designed so higher-value tenants can later be moved to stronger isolation without rewriting the browser or worker surfaces.

Assessment Lifecycle

Submission status:

  • draft
  • submitted
  • under_review
  • revisions_requested
  • approved

Review outcome:

  • pass
  • resubmit
  • not_yet

Certificate status:

  • pending
  • issued
  • revoked

Seed Coverage

The first seed set should include:

  • one generic demo tenant
  • one flagship B2B Faculty tenant
  • a branded public and member runtime snapshot
  • one programme, one cohort, one offer, one evidence-pack flow, and one certificate template

Failure Modes

  • missing tenant scope in repository code can cause cross-tenant leakage
  • weak foreign key coverage can leave orphaned submissions or certificates after deletes
  • incorrect state transitions can issue certificates before assessment or evidence-pack completion

Debugging

Start from packages/database/migrations/0001_initial.sql, then compare the domain types and worker repository usage to ensure ids, status enums, and ownership columns still line up.

Change Log / Verification

  • Added the initial TopoloLearn schema plan and D1 migration coverage on 2026-04-08.