Database & Living Ledger Architecture

info

The schema continues to evolve as pedagogical patterns are tested and refined. This document reflects the current working implementation.

The Living Ledger Philosophy

The Research to Story (RTS) application is architected around a central principle: "everything is recorded."

It functions as a system where a persistent database acts as a living ledger for a student's entire inquiry journey. This database-centric approach ensures that every step—from initial topic ideation to final synthesis and beyond—is captured and made available for subsequent computational operations.

Generative AI (currently Google Gemini via the @google/genai SDK) is integrated as a constrained text processing system that performs specific, bounded operations. Its outputs depend entirely on the structured data it receives from the database ledger for a given user and session.

Technical Infrastructure

Development Environment

The system uses Supabase (open source) which can run entirely locally using the Supabase CLI (or hosted). Locally, the CLI handles all Docker orchestration and spins up a full development stack including:

PostgreSQL database
Authentication service
Storage buckets
Studio dashboard (GUI for database management)

The CLI supports migration-based development:

Make changes in Supabase Studio or via SQL
Capture changes as migration files
Version control migrations in Git
Apply migrations to production environments

System Architecture

Three main components work together:

1. Frontend UI (Next.js & React)

User interface guiding students through sequential RTS Movements
Dashboards for instructors and administrators
Built with TypeScript, shadcn/ui components, Tailwind CSS

2. Backend API Handlers (Next.js API Routes)

Server-side functions managing application logic
Handle authentication and database operations
Process all interactions with Gemini models
Implement Row Level Security policies

3. Database & Auth (Supabase)

PostgreSQL database serving as the persistent ledger
Authentication system with role-based access
Row Level Security (RLS) for data access control
Real-time subscriptions for live updates

Core Database Architecture

I. Authentication & User Management

profiles — Extended user metadata beyond Supabase auth

Links to Supabase auth.users via user ID
Stores role (student, instructor, admin) with 'student' as default
Tracks first name and last name
Establishes baseline for all RLS policies

Why separate profiles?

Supabase auth.users is managed by Supabase
Custom profile data belongs in your schema
Enables role-based access control
Supports course enrollment logic

II. Course Infrastructure (The "Walled Garden")

RTS implements a course-centric "walled garden" where all student work exists within course boundaries.

courses — The organizational container

Created and owned by instructors (via instructor_id)
Stores course title, course code, and semester
Can be archived when complete (via is_archived flag)

course_enrollments — Links students to courses

Many-to-many relationship (students can join multiple courses)
Tracks enrollment date
Scopes all student work to course context

Why this architecture matters:

Instructors control who can access their courses
Students only see work within their enrolled courses
Clear data boundaries for assessment
Enables course-specific pedagogical approaches
Privacy through isolation

Row Level Security Example:

-- Students can only see courses they're enrolled in
CREATE POLICY "Students can view enrolled courses"
ON courses FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM course_enrollments
    WHERE course_enrollments.course_id = courses.id
    AND course_enrollments.student_id = auth.uid()
  )
);

III. Movement Architecture

movements — Reference table defining the movement structure

Integer primary key (id) rather than UUID — these are fixed structural definitions
Stores title, description, and display order
Supports hierarchical structure via parent_movement_id
Can be toggled active/inactive via is_active flag
Defines the pedagogical sequence students move through

This table acts as configuration rather than user data — it defines what movements exist so the UI and handlers can reference them consistently.

IV. Movement 1: Core Reflection System

Movement 1 serves as the foundational testing architecture for the entire RTS framework. The patterns established here—4-round Socratic questioning with AI synthesis—provide the template for scaling to additional movements.

rts_sessions — Research inquiry journeys

Unique session per research topic (primary key: session_id)
Links to user and course via user_id and course_id
Stores research topic text (topic_text)
Tracks current movement number, start and completion timestamps
Records model used for the session
Foundation for all reflection work

followup_questions_log — Complete record of AI-generated questions

Every computationally generated question with full provenance
Categorized according to pedagogical framework (5 categories in Movement 1)
Includes movement number to support multi-movement architecture
Stores model used and generation timestamp
Enables research into question generation patterns

Fields captured:

{
  id: uuid,
  session_id: uuid,       // Links to rts_sessions.session_id
  user_id: uuid,          // Student who owns this
  movement_number: integer,  // Which movement (1-6)
  round_number: integer,     // Which reflection round (1-4)
  category: text,            // Pedagogical category
  question_text: text,       // The actual question
  model_used: text,          // AI model that generated it
  created_at: timestamp
}

reflection_rounds — Student responses to AI-generated questions

Links student writing to specific AI-generated questions via answered_question_id
Captures complete AI interaction metadata
Course-scoped via course_id for proper data boundaries

Comprehensive AI metadata:

{
  prompt_token_count: integer,    // Tokens in the prompt
  response_token_count: integer,  // Tokens in AI response
  thinking_token_count: integer,  // Tokens in AI reasoning
  total_token_count: integer,     // Sum of all tokens
  thought_summary: text,          // AI's reasoning process artifact
  model_used: text,               // Exact model string
  interpretive_summary: text      // AI's summary of the round
}

Why this metadata matters:

Builds AI literacy through observability
Enables cost tracking and analysis
Supports research into AI interaction patterns
Model tracking aims for accuracy, with minimal fallback defaults

movement_synthesis — AI-generated text reorganizations

Computational reorganization of student reflections after completing 4 rounds
Markdown-formatted synthesis (not JSON-constrained)
Complete metadata about computational costs and processing
Course-scoped via course_id
Includes per-synthesis token counts (prompt, response, thinking, total)

Output format:

# Your Inquiry Journey: [Topic Name]

[Mentor-style letter synthesizing all 4 rounds...]

## Patterns I'm Noticing
- Pattern 1...
- Pattern 2...

## Questions to Carry Forward
- Question 1...
- Question 2...

session_token_analytics — Cumulative token tracking

Dedicated table for tracking computational costs across rounds
Records per-round token counts and running cumulative totals
Links to session, user, movement number, and round number
Enables analysis of how computational cost evolves through a student's journey
Supports cost forecasting and resource allocation

{
  id: uuid,
  session_id: uuid,          // Links to rts_sessions.session_id
  user_id: uuid,
  movement_number: integer,
  round_number: integer,
  round_tokens: integer,      // Tokens used in this round
  cumulative_tokens: integer, // Running total across all rounds
  created_at: timestamp
}

V. Deep Dive Architecture

Deep Dives extend Movement 1 by allowing students to explore completed topics through focused categorical lenses. Currently implemented across 6 categories, each following identical technical patterns.

deep_dive_followup_questions_log — Questions for deep dive explorations

Similar to Movement 1 questions but category-focused
Includes category field (e.g., "Spark of Inquiry")
Stores subcategory for fine-grained pedagogical structure (e.g., "The Origin Scene", "The Emotional Core")
Links to original session being explored

deep_dive_reflection_rounds — Deep dive student responses

Same metadata structure as Movement 1 reflections (all token counts, thought summary, model used)
Includes category_selected to identify which deep dive lens is active
Links to deep dive questions via answered_question_id, not movement questions
Course-scoped via course_id
Enables analysis of how students revisit topics through different lenses

deep_dive_synthesis — Deep dive final synthesis

Category-specific synthesis of deep dive journey
Includes category_selected to scope the synthesis
Same observability features as movement synthesis (all token metadata)
Course-scoped via course_id
Allows multiple deep dives per original session

Six Deep Dive Categories:

Spark of Inquiry
Inquiry as Story
Stakes and Significance
Puzzles and Unknowns
Listeners and Lens
Production Lab

VI. Parallel Scaffolds (In Development)

reflection_journal_entries — Metacognitive process tracking

Three-prompt structure at each movement
Captures noticing, tensions, and forward momentum
Accumulates across movements
Independent of topic-specific work

bbme_prompts — Black Box Micro-Engagement tasks

Small production exercises embedded after movements
Tool exploration and technical resilience building
Audio production skill development
Instructor-manageable with active/inactive toggling

bbme_submissions — Student BBME work

Four-part reflection structure
Tracks tools used, friction encountered, help sought
Documents relational dimension of production

bbme_ai_synthesis — AI analysis of BBME reflections

Computational synthesis of technical learning
Same observability patterns as other AI operations

Additional supporting tables: bbme_course_availability, bbme_prompt_links, bbme_source_citations

AI Integration & Observability

How AI Interacts with the Database

Question Generation Flow:

Student writes reflection (or starts new session)
    → API handler fetches student context from DB
    → First round: topic text only
    → Subsequent rounds: builds reflection trail from previous rounds
    → Sends to Gemini API with structured JSON schema
    → Receives structured JSON response
    → Saves questions to followup_questions_log (with subcategory for deep dives)
    → Returns questions + metadata to UI

Synthesis Generation Flow:

Student completes 4 rounds
    → API handler fetches ALL rounds from DB
    → Builds synthesis prompt with student's complete journey
    → Sends to Gemini API (markdown mode, not JSON)
    → Receives markdown synthesis
    → Saves to movement_synthesis or deep_dive_synthesis with metadata
    → Returns synthesis to UI

AI Observability Captured

Every AI operation logs:

Token Usage:

Prompt tokens (input computational cost)
Response tokens (output computational cost)
Thinking tokens (reasoning computational cost)
Total tokens (complete operation cost)

Model Traceability:

Exact model string (e.g., gemini-3-pro-preview)
Dynamic model override support for testing via model_override parameter
Supports model comparison research

Thought Summaries:

Extracted from multi-part model responses when available
Intermediate computational artifacts, not transcripts of reasoning
Fallback descriptions generated when thinking tokens are used but thought text is unavailable
Surfaced pedagogically as "The Imitation of a Thought Process"

Complete Audit Trail:

Every question generated
Every reflection saved
Every synthesis created
Queryable for research purposes

Row Level Security (RLS)

RTS implements comprehensive RLS policies to enforce data boundaries:

Student policies:

-- Students can only see their own sessions
CREATE POLICY "Users can view own sessions"
ON rts_sessions FOR SELECT
USING (auth.uid() = user_id);

-- Students can only see sessions in enrolled courses
CREATE POLICY "Users can view course sessions"
ON rts_sessions FOR SELECT
USING (
  course_id IN (
    SELECT course_id FROM course_enrollments
    WHERE student_id = auth.uid()
  )
);

Instructor policies:

-- Instructors can see all student work in their courses
CREATE POLICY "Instructors can view course data"
ON reflection_rounds FOR SELECT
USING (
  course_id IN (
    SELECT id FROM courses
    WHERE instructor_id = auth.uid()
  )
);

Admin policies:

-- Admins can see everything
CREATE POLICY "Admins have full access"
ON ALL TABLES
USING (
  (SELECT role FROM profiles WHERE id = auth.uid()) = 'admin'
);

Why This Architecture Matters

1. Pedagogical Observability

Every computational operation is visible. Students build AI literacy by seeing:

Token counts (computational costs)
Model names (which AI processed their work)
Thought summary artifacts (intermediate computational outputs)
Complete interaction history
AI Literacy Lens annotations in exports (critical reading guidance)

2. Research Infrastructure

The living ledger enables investigation of:

How students interact with AI-scaffolded inquiry
Whether AI observability affects confidence
How different models perform with same prompts
Patterns in question generation and synthesis
Cumulative computational cost trends via session_token_analytics

3. Assessment Capability

Instructors can:

Track student inquiry development
See complete research journeys
Export student work with AI literacy annotations
Identify struggling students early

4. Auditability

Every computational operation is traceable:

No "black box" AI interactions
Student work always contextual
Research questions answerable empirically
Pedagogical effectiveness measurable

Current Technology Stack

Database: PostgreSQL via Supabase
ORM: Direct SQL via Supabase client
AI Integration: Google Gemini (currently gemini-3-pro-preview) via @google/genai SDK
AI Configuration: Structured JSON output for questions, markdown for synthesis, thinking tokens enabled
Authentication: Supabase Auth with JWT tokens
Development: Supabase CLI for local database
Deployment: Supabase hosted (production)

The ledger does more than just store data—it creates the infrastructure for investigating whether constrained computational text processing can function as a useful thinking tool without becoming a “thinking partner.”

By recording everything, we can study what actually happens when students interact with computationally generated prompts and text reorganizations, rather than making assumptions about effectiveness or pedagogical value.

The Living Ledger Philosophy​

Technical Infrastructure​

Development Environment​

System Architecture​

Core Database Architecture​

I. Authentication & User Management​

II. Course Infrastructure (The "Walled Garden")​

III. Movement Architecture​

IV. Movement 1: Core Reflection System​

V. Deep Dive Architecture​

VI. Parallel Scaffolds (In Development)​

AI Integration & Observability​

How AI Interacts with the Database​

AI Observability Captured​

Row Level Security (RLS)​

Why This Architecture Matters​

1. Pedagogical Observability​

2. Research Infrastructure​

3. Assessment Capability​

4. Auditability​

Current Technology Stack​