93 lines
3.5 KiB
SQL
93 lines
3.5 KiB
SQL
CREATE TABLE IF NOT EXISTS schoolhouse_course (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
d2l_course_id TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
code TEXT,
|
|
semester TEXT,
|
|
instructor_name TEXT,
|
|
active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
first_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
last_synced_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS schoolhouse_assignment (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
course_id BIGINT NOT NULL REFERENCES schoolhouse_course(id) ON DELETE CASCADE,
|
|
d2l_assignment_key TEXT,
|
|
title TEXT NOT NULL,
|
|
category TEXT,
|
|
due_at TIMESTAMPTZ,
|
|
status TEXT NOT NULL DEFAULT 'assigned',
|
|
grade_text TEXT,
|
|
grade_numeric DOUBLE PRECISION,
|
|
professor_comments TEXT,
|
|
source TEXT NOT NULL DEFAULT 'd2l',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS schoolhouse_submission (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
assignment_id BIGINT NOT NULL REFERENCES schoolhouse_assignment(id) ON DELETE CASCADE,
|
|
version_label TEXT NOT NULL DEFAULT 'v1',
|
|
original_filename TEXT NOT NULL,
|
|
mime_type TEXT NOT NULL,
|
|
checksum_sha256 TEXT,
|
|
submitted_at TIMESTAMPTZ,
|
|
suggested_metadata_json JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
confirmed_metadata_json JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
paperless_document_id BIGINT,
|
|
paperless_title TEXT,
|
|
paperless_note_last_hash TEXT,
|
|
paperless_note_appended_at TIMESTAMPTZ,
|
|
status TEXT NOT NULL DEFAULT 'draft',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS schoolhouse_recording (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
course_id BIGINT NOT NULL REFERENCES schoolhouse_course(id) ON DELETE CASCADE,
|
|
assignment_id BIGINT REFERENCES schoolhouse_assignment(id) ON DELETE SET NULL,
|
|
session_date DATE NOT NULL,
|
|
original_filename TEXT NOT NULL,
|
|
mp3_filename TEXT,
|
|
duration_seconds INTEGER,
|
|
transcription_status TEXT NOT NULL DEFAULT 'queued',
|
|
summary_status TEXT NOT NULL DEFAULT 'queued',
|
|
transcript_paperless_document_id BIGINT,
|
|
summary_paperless_document_id BIGINT,
|
|
recording_paperless_document_id BIGINT,
|
|
notes TEXT,
|
|
recording_metadata_json JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS schoolhouse_d2l_sync_run (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
completed_at TIMESTAMPTZ,
|
|
status TEXT NOT NULL DEFAULT 'running',
|
|
raw_snapshot_path TEXT,
|
|
changes_summary_json JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
error_text TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS schoolhouse_assignment_mapping (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
course_id BIGINT NOT NULL REFERENCES schoolhouse_course(id) ON DELETE CASCADE,
|
|
d2l_grade_item_name TEXT NOT NULL,
|
|
normalized_key TEXT NOT NULL,
|
|
assignment_id BIGINT NOT NULL REFERENCES schoolhouse_assignment(id) ON DELETE CASCADE,
|
|
confidence DOUBLE PRECISION,
|
|
mapping_source TEXT NOT NULL DEFAULT 'auto',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE (course_id, d2l_grade_item_name)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_schoolhouse_assignment_course_id ON schoolhouse_assignment(course_id);
|
|
CREATE INDEX IF NOT EXISTS idx_schoolhouse_submission_assignment_id ON schoolhouse_submission(assignment_id);
|
|
CREATE INDEX IF NOT EXISTS idx_schoolhouse_recording_course_id ON schoolhouse_recording(course_id);
|
|
CREATE INDEX IF NOT EXISTS idx_schoolhouse_recording_assignment_id ON schoolhouse_recording(assignment_id);
|