Multi-Tenancy & Institutional Hierarchy (Districts, Schools, Cohorts, Enrollments)

Implement multi-tenant educational schema (Districts, Schools, Cohorts, Enrollments)

1. Problem Statement

The curriculum is designed for implementation across public school CTE pathways and Advisory periods. Currently, users exist in a flat user table with simple roles (user, student, teacher, admin, superadmin). There is no construct for:

• School districts or charter networks.
• Individual school campuses.
• Class cohorts / advisory sections (e.g., "Period 2 - 9th Grade Advisory").
• Student enrollment associations and teacher cohort assignments.

Educators cannot view aggregated telemetry or grade student practicums without exposing all students across all schools.

2. Target Schema Additions

// Proposed structure within @autonomy/db
export const districts = sqliteTable("districts", {
  id: text("id").primaryKey(), // uuidv7
  name: text("name").notNull(),
  domainWhitelist: text("domain_whitelist"), // e.g. "lausd.net,district.k12.ca.us"
  createdAt: integer("created_at", { mode: "timestamp_ms" }).notNull(),
});

export const schools = sqliteTable("schools", {
  id: text("id").primaryKey(),
  districtId: text("district_id").notNull().references(() => districts.id, { onDelete: "cascade" }),
  name: text("name").notNull(),
  slug: text("slug").notNull(),
  createdAt: integer("created_at", { mode: "timestamp_ms" }).notNull(),
});

export const cohorts = sqliteTable("cohorts", {
  id: text("id").primaryKey(),
  schoolId: text("school_id").notNull().references(() => schools.id, { onDelete: "cascade" }),
  name: text("name").notNull(), // e.g. "Advisory Cohort 2026-A"
  academicYear: text("academic_year").notNull(), // e.g. "2026-2027"
  cteTrack: text("cte_track", { enum: ["general_advisory", "computer_science", "healthcare", "trades"] }).default("general_advisory"),
  leadTeacherId: text("lead_teacher_id").notNull().references(() => user.id, { onDelete: "restrict" }),
  createdAt: integer("created_at", { mode: "timestamp_ms" }).notNull(),
});

export const cohortEnrollments = sqliteTable("cohort_enrollments", {
  id: text("id").primaryKey(),
  cohortId: text("cohort_id").notNull().references(() => cohorts.id, { onDelete: "cascade" }),
  userId: text("user_id").notNull().references(() => user.id, { onDelete: "cascade" }),
  role: text("role", { enum: ["student", "co_teacher", "mentor"] }).default("student").notNull(),
  status: text("status", { enum: ["active", "withdrawn", "completed"] }).default("active").notNull(),
  enrolledAt: integer("enrolled_at", { mode: "timestamp_ms" }).notNull(),
}, (table) => [
  uniqueIndex("cohort_user_idx").on(table.cohortId, table.userId),
]);

3. Acceptance Criteria

• Tables for districts, schools, cohorts, and cohort_enrollments are defined in Drizzle with relations and foreign keys.
• Users can belong to multiple cohorts with distinct roles (e.g., student in one cohort, peer mentor in another).- [ ]
• Indexes exist on foreign keys to support sub-10ms roster queries.

Start by locating the schema entry point for @autonomy/db and reviewing the existing user table and migration setup. Define the four proposed tables with relations, foreign keys, and foreign-key indexes, then verify that users can hold different roles across cohorts and that roster queries meet the stated acceptance criteria.