Data Model & RPC Contracts
Supabase schemas, RLS boundary, pgvector retrieval, bitemporal mastery và RPC submit_attempt_v3 của EduGap.
EduGap dùng Supabase PostgreSQL làm source of truth cho auth-linked user state, concept graph, adaptive decisions, mastery, attempts, chat history và RAG chunks.
Schema ownership
| Schema/table group | Purpose |
|---|---|
auth.* | Supabase Auth identities and sessions |
app.users, app.user_roles, app.roles | Application profile and RBAC role mapping |
app.courses, app.concepts, app.concept_relations | Course structure and prerequisite graph |
app.questions, app.question_concepts, app.hints | Adaptive question bank and multi-skill mapping |
app.student_concept_mastery | Current Elo/BKT/mastery state per student-concept |
app.student_mastery_bitemporal | Historical mastery intervals for retroactive reads |
audit.adaptive_decisions, audit.adaptive_rewards | Bandit decision/reward traces |
app.quiz_attempts | Submitted answers and grading outcomes |
app.chat_sessions, app.chat_messages | Tutor conversations |
public.slide_embeddings | RAG chunks and pgvector embeddings |
Key RPCs and views
| Name | Type | Runtime caller |
|---|---|---|
app.submit_attempt_v3 | PostgreSQL function | FastAPI adaptive submit |
public.match_slides | PostgreSQL function | RAGService vector search |
app.active_student_mastery | View | Backend mastery reads |
app.patch_student_mastery_retroactive | PostgreSQL function | Bitemporal mastery updates |
submit_attempt_v3
submit_attempt_v3 is the atomic submit boundary. The API computes the answer score and reward, then passes the payload into PostgreSQL so database state changes land together.
Inputs:
| Parameter | Meaning |
|---|---|
p_decision_id | Prior recommendation trace that must be consumed exactly once |
p_student_id, p_course_id, p_concept_id, p_question_id | Ownership and target dimensions |
p_student_answer | Submitted answer payload for audit storage |
p_actual_score | Server-graded score |
p_hint_count, p_used_ai_help | Discount signals from server logs |
p_context | LinUCB context vector |
p_reward | ZPD reward |
p_response_time_ms | Time-to-answer signal |
Outputs include the updated student Elo, question Elo, BKT probability, mastery state, weakness flag, correctness and stability days.
Replay protection
The RPC consumes audit.adaptive_decisions.consumed_at. If the same decision_id is submitted twice, the route returns conflict behavior instead of applying a second mastery update. The API also pre-checks ownership and selected question mismatch before calling the RPC.
Bitemporal mastery
Current UI reads should use active mastery. Historical or retroactive updates use bitemporal rows so a concept's state can be queried "as of" a target time without overwriting audit history.
This matters for:
- reviewing progression after delayed graph propagation;
- recalculating mastery after question calibration;
- preserving auditability for demos, experiments and mentor review.
RLS and secret boundary
- Browser clients may use Supabase publishable keys under RLS.
- FastAPI app/audit schema writes use
SUPABASE_SECRET_KEY. - RPC execute privileges should not expose
submit_attempt_v3to public browser contexts. - Live mode rejects fake tokens and raw UUID bearer tokens.
RAG vector data
RAGService calls public.match_slides with:
query_embedding
match_threshold
match_count
filter_document_regex?The function returns document name, slide number, content, image URL and similarity. Backend then applies dedupe, fallback and neighboring-slide expansion before building context for the tutor.
Migration references
| Concern | Migration family |
|---|---|
| Base schema | db/supabase/migrations/20260611_initial_schema.sql |
| Submit RPC | db/supabase/migrations/*submit_attempt_v3*.sql |
| RAG metadata filtering | db/supabase/migrations/20260617_add_metadata_filter_to_match_slides.sql |
| RPC security/correctness | db/supabase/migrations/20260621_security_and_correctness_fixes.sql |
| Async/concurrency | db/supabase/migrations/20260621_concurrency_and_async_outbox.sql |
| Bitemporal mastery | db/supabase/migrations/20260624_bitemporal_mastery.sql |
| Onboarding diagnostic mastery seed | db/supabase/migrations/20260630185421_onboarding_diagnostic_mastery_seed.sql |