Gauntlet-Incept MCP
by Birdsmith
- database
- init
-- Create schema for the CCC database
CREATE SCHEMA IF NOT EXISTS ccc;
-- Create tables for QTI content
CREATE TABLE IF NOT EXISTS ccc.questions (
id SERIAL PRIMARY KEY,
qti_id VARCHAR(255) NOT NULL UNIQUE,
content TEXT NOT NULL,
subject VARCHAR(50) NOT NULL,
grade VARCHAR(10) NOT NULL,
standard VARCHAR(50) NOT NULL,
lesson VARCHAR(255) NOT NULL,
difficulty INTEGER NOT NULL,
source VARCHAR(50),
is_example BOOLEAN DEFAULT FALSE,
expected_result VARCHAR(10),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS ccc.articles (
id SERIAL PRIMARY KEY,
qti_id VARCHAR(255) NOT NULL UNIQUE,
content TEXT NOT NULL,
subject VARCHAR(50) NOT NULL,
grade VARCHAR(10) NOT NULL,
standard VARCHAR(50) NOT NULL,
lesson VARCHAR(255) NOT NULL,
source VARCHAR(50),
is_example BOOLEAN DEFAULT FALSE,
expected_result VARCHAR(10),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS ccc.courses (
id SERIAL PRIMARY KEY,
qti_id VARCHAR(255) NOT NULL UNIQUE,
title VARCHAR(255) NOT NULL,
description TEXT,
subject VARCHAR(50) NOT NULL,
grade VARCHAR(10) NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS ccc.lessons (
id SERIAL PRIMARY KEY,
qti_id VARCHAR(255) NOT NULL UNIQUE,
course_id INTEGER REFERENCES ccc.courses(id) ON DELETE CASCADE,
title VARCHAR(255) NOT NULL,
description TEXT,
standard VARCHAR(50) NOT NULL,
sequence_number INTEGER NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS ccc.lesson_articles (
id SERIAL PRIMARY KEY,
lesson_id INTEGER REFERENCES ccc.lessons(id) ON DELETE CASCADE,
article_id INTEGER REFERENCES ccc.articles(id) ON DELETE CASCADE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS ccc.lesson_questions (
id SERIAL PRIMARY KEY,
lesson_id INTEGER REFERENCES ccc.lessons(id) ON DELETE CASCADE,
question_id INTEGER REFERENCES ccc.questions(id) ON DELETE CASCADE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_questions_subject_grade ON ccc.questions(subject, grade);
CREATE INDEX IF NOT EXISTS idx_questions_standard ON ccc.questions(standard);
CREATE INDEX IF NOT EXISTS idx_questions_lesson ON ccc.questions(lesson);
CREATE INDEX IF NOT EXISTS idx_questions_difficulty ON ccc.questions(difficulty);
CREATE INDEX IF NOT EXISTS idx_questions_is_example ON ccc.questions(is_example);
CREATE INDEX IF NOT EXISTS idx_articles_subject_grade ON ccc.articles(subject, grade);
CREATE INDEX IF NOT EXISTS idx_articles_standard ON ccc.articles(standard);
CREATE INDEX IF NOT EXISTS idx_articles_lesson ON ccc.articles(lesson);
CREATE INDEX IF NOT EXISTS idx_articles_is_example ON ccc.articles(is_example);
CREATE INDEX IF NOT EXISTS idx_courses_subject_grade ON ccc.courses(subject, grade);
-- Create function to update timestamp
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Create triggers to update timestamp
CREATE TRIGGER update_questions_timestamp
BEFORE UPDATE ON ccc.questions
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();
CREATE TRIGGER update_articles_timestamp
BEFORE UPDATE ON ccc.articles
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();
CREATE TRIGGER update_courses_timestamp
BEFORE UPDATE ON ccc.courses
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();
CREATE TRIGGER update_lessons_timestamp
BEFORE UPDATE ON ccc.lessons
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();