init.sql

CREATE TABLE users (
  user_id     BIGINT PRIMARY KEY,
  created_at  TIMESTAMPTZ DEFAULT now(),
  aliases     JSONB,
  username    TEXT,
  language_code TEXT,
  is_bot      BOOLEAN
);

CREATE TABLE sessions (
  session_id  UUID PRIMARY KEY,
  user_id     BIGINT REFERENCES users(user_id),
  started_at  TIMESTAMPTZ DEFAULT now(),
  ended_at    TIMESTAMPTZ,
  is_closed   BOOLEAN     DEFAULT FALSE,
  pending_revision_msg TEXT DEFAULT NULL,
  knowledge_profile    TEXT,
  knowledge_updated_at TIMESTAMPTZ
);

CREATE TABLE messages (
  id          BIGSERIAL PRIMARY KEY,
  session_id  UUID REFERENCES sessions(session_id),
  user_id     BIGINT REFERENCES users(user_id),
  role        TEXT,          -- 'user' | 'assistant'
  content     TEXT,
  created_at  TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE revisions (
  id             BIGSERIAL PRIMARY KEY,
  session_id     UUID REFERENCES sessions(session_id),
  user_id        BIGINT REFERENCES users(user_id),
  revision_time  TIMESTAMPTZ,
  reminded       BOOLEAN      DEFAULT FALSE,
  manual         BOOLEAN      DEFAULT FALSE,
  sent_at        TIMESTAMPTZ,
  CONSTRAINT uix_revision_session_user UNIQUE (session_id, user_id)
);

CREATE TABLE issue_logs (
  id         BIGSERIAL PRIMARY KEY,
  user_id    BIGINT REFERENCES users(user_id),
  context    TEXT,
  details    TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE interleaving_practices (
  id                BIGSERIAL PRIMARY KEY,
  user_id           BIGINT REFERENCES users(user_id),
  created_at        TIMESTAMPTZ DEFAULT now(),
  profile_snapshot  TEXT,
  tasks             TEXT NOT NULL
);