hackathon/sql/schema.sql

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;

CREATE TABLE users (
  id TEXT PRIMARY KEY NOT NULL, -- supplied from google - not auto increment
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  picture TEXT NOT NULL,

  admin BOOLEAN NOT NULL,
  created_at DATETIME NOT NULL
);

CREATE TABLE user_info (
  user_id TEXT PRIMARY KEY NOT NULL REFERENCES users (id),
  grade_level INTEGER NOT NULL,
  skill_level INTEGER NOT NULL
);

CREATE TABLE tokens (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  token TEXT NOT NULL UNIQUE,
  user_id TEXT NOT NULL REFERENCES users (id),
  created_at DATETIME NOT NULL
);

CREATE INDEX user_tokens ON tokens(user_id);

CREATE TABLE question_attempt (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  user_id TEXT NOT NULL REFERENCES users (id),
  question_id TEXT NOT NULL,
  question_part INTEGER NOT NULL,
  correct BOOLEAN NOT NULL,
  points_awarded INTEGER NOT NULL,
  answer TEXT NOT NULL,
  code TEXT NOT NULL,
  submitted_at DATETIME NOT NULL
);