arnaudne
4e38b5a791
Full Next.js App Router application with: - AI-graded lesson checkpoints (BKT/Elo mastery tracking) - Auth.js v5: credentials, Google, GitHub, generic OIDC - Anonymous-session-first with migrate-on-signin - Admin panel: users, blueprints, reports, site settings - Password reset + email verification (nodemailer/SMTP) - Site config: require_auth + signups_enabled flags - server-only guards on all DB/generation/verification modules - PostgreSQL 16 + pgvector, Redis cache, Drizzle ORM - 271 unit tests (Vitest), golden-eval harness, Playwright e2e stubs Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
140 lines
6.9 KiB
SQL
140 lines
6.9 KiB
SQL
CREATE TYPE "public"."blueprint_status" AS ENUM('draft', 'verifying', 'published', 'flagged');--> statement-breakpoint
|
|
CREATE TYPE "public"."checkpoint_kind" AS ENUM('predict', 'explain', 'solve');--> statement-breakpoint
|
|
CREATE TYPE "public"."grade_verdict" AS ENUM('mastered', 'partial', 'misconception', 'uncertain');--> statement-breakpoint
|
|
CREATE TYPE "public"."job_status" AS ENUM('pending', 'running', 'done', 'failed');--> statement-breakpoint
|
|
CREATE TYPE "public"."verify_status" AS ENUM('pending', 'pass', 'fail', 'uncertain');--> statement-breakpoint
|
|
CREATE TYPE "public"."verify_verdict" AS ENUM('pass', 'fail', 'uncertain');--> statement-breakpoint
|
|
CREATE TABLE "blueprint" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"intent_key" text NOT NULL,
|
|
"embedding" vector(1536),
|
|
"title" text NOT NULL,
|
|
"model_version" text NOT NULL,
|
|
"content_version" integer DEFAULT 1 NOT NULL,
|
|
"status" "blueprint_status" DEFAULT 'draft' NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
CONSTRAINT "blueprint_intent_key_unique" UNIQUE("intent_key")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "checkpoint" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"segment_id" uuid NOT NULL,
|
|
"prompt" text NOT NULL,
|
|
"kind" "checkpoint_kind" NOT NULL,
|
|
"reference_answer" text NOT NULL,
|
|
"rubric_json" jsonb NOT NULL,
|
|
"verify_status" "verify_status" DEFAULT 'pending' NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "concept" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"blueprint_id" uuid NOT NULL,
|
|
"ord" integer NOT NULL,
|
|
"name" text NOT NULL,
|
|
"retrieval_ctx_ref" text
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "grade" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"response_id" uuid NOT NULL,
|
|
"verdict" "grade_verdict" NOT NULL,
|
|
"misconception_tag" text,
|
|
"confidence" real NOT NULL,
|
|
"payload_json" jsonb NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "job" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"type" text NOT NULL,
|
|
"idempotency_key" text NOT NULL,
|
|
"status" "job_status" DEFAULT 'pending' NOT NULL,
|
|
"payload_json" jsonb NOT NULL,
|
|
"attempts" integer DEFAULT 0 NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
CONSTRAINT "job_idempotency_key_unique" UNIQUE("idempotency_key")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "journey_instance" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"user_id" uuid NOT NULL,
|
|
"blueprint_id" uuid NOT NULL,
|
|
"position" integer DEFAULT 0 NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "lesson" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"blueprint_id" uuid NOT NULL,
|
|
"ord" integer NOT NULL,
|
|
"est_minutes" integer DEFAULT 10 NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "mastery" (
|
|
"user_id" uuid NOT NULL,
|
|
"concept_id" uuid NOT NULL,
|
|
"score" real DEFAULT 0.5 NOT NULL,
|
|
"last_seen" timestamp DEFAULT now() NOT NULL,
|
|
"next_review" timestamp DEFAULT now() NOT NULL,
|
|
CONSTRAINT "mastery_user_id_concept_id_pk" PRIMARY KEY("user_id","concept_id")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "misconception" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"concept_id" uuid NOT NULL,
|
|
"tag" text NOT NULL,
|
|
"signature" text NOT NULL,
|
|
"verify_status" "verify_status" DEFAULT 'pending' NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "response" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"user_id" uuid NOT NULL,
|
|
"checkpoint_id" uuid NOT NULL,
|
|
"text" text NOT NULL,
|
|
"ts" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "segment" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"lesson_id" uuid NOT NULL,
|
|
"ord" integer NOT NULL,
|
|
"body_json" jsonb NOT NULL,
|
|
"source_chunk_ids" uuid[] DEFAULT '{}' NOT NULL,
|
|
"verify_status" "verify_status" DEFAULT 'pending' NOT NULL,
|
|
"content_version" integer DEFAULT 1 NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "source_chunk" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"doc_ref" text NOT NULL,
|
|
"embedding" vector(1536),
|
|
"text" text NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "user" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "verify_report" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"target_id" uuid NOT NULL,
|
|
"target_type" text NOT NULL,
|
|
"verdict" "verify_verdict" NOT NULL,
|
|
"confidence" real NOT NULL,
|
|
"payload_json" jsonb NOT NULL,
|
|
"model_version" text NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
ALTER TABLE "checkpoint" ADD CONSTRAINT "checkpoint_segment_id_segment_id_fk" FOREIGN KEY ("segment_id") REFERENCES "public"."segment"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "concept" ADD CONSTRAINT "concept_blueprint_id_blueprint_id_fk" FOREIGN KEY ("blueprint_id") REFERENCES "public"."blueprint"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "grade" ADD CONSTRAINT "grade_response_id_response_id_fk" FOREIGN KEY ("response_id") REFERENCES "public"."response"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "journey_instance" ADD CONSTRAINT "journey_instance_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "journey_instance" ADD CONSTRAINT "journey_instance_blueprint_id_blueprint_id_fk" FOREIGN KEY ("blueprint_id") REFERENCES "public"."blueprint"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "lesson" ADD CONSTRAINT "lesson_blueprint_id_blueprint_id_fk" FOREIGN KEY ("blueprint_id") REFERENCES "public"."blueprint"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "mastery" ADD CONSTRAINT "mastery_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "mastery" ADD CONSTRAINT "mastery_concept_id_concept_id_fk" FOREIGN KEY ("concept_id") REFERENCES "public"."concept"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "misconception" ADD CONSTRAINT "misconception_concept_id_concept_id_fk" FOREIGN KEY ("concept_id") REFERENCES "public"."concept"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "response" ADD CONSTRAINT "response_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "response" ADD CONSTRAINT "response_checkpoint_id_checkpoint_id_fk" FOREIGN KEY ("checkpoint_id") REFERENCES "public"."checkpoint"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "segment" ADD CONSTRAINT "segment_lesson_id_lesson_id_fk" FOREIGN KEY ("lesson_id") REFERENCES "public"."lesson"("id") ON DELETE no action ON UPDATE no action; |