Supabase schema 設計

Table 方針

domain table:

manaba_courses
manaba_reports
manaba_quizzes
manaba_surveys
manaba_course_news

source table:

manaba_email_sources

成績公開用の domain table は作らない。

実 migration は supabase/migrations/001_initial_schema.sql に置く。

共通命名ルール

ID

manaba 側 ID は以下で統一する。

manaba_course_id
manaba_report_id
manaba_quiz_id
manaba_survey_id
manaba_news_id

成績公開は保存対象外のため manaba_grade_id は作らない。

Title

課題名、アンケート名、ニュースタイトルはすべて title とする。

manaba_reports.title
manaba_quizzes.title
manaba_surveys.title
manaba_course_news.title

course だけは name とする。

Datetime

時刻 field は以下の意味で統一する。

field	意味
`created_at`	DB row 作成時刻
`updated_at`	DB row 更新時刻
`received_at`	Gmail message 受信時刻
`parsed_at`	GAS/parser 処理時刻
`starts_at`	manaba item の受付開始日時
`due_at`	manaba item の受付終了日時
`posted_at`	manaba course news の掲示日時。取れる場合のみ

received_at と parsed_at は manaba_email_sources にだけ置く。 domain table にはメール由来の観測時刻を置かない。

due_at を使い、ends_at や deadline_at は使わない。

URL

domain table の url は対象 item の manaba URL だけを保存する。リマインダ設定 URL は保存しない。

Schema draft

create type manaba_email_source_kind as enum (
  'report_published_email',
  'quiz_published_email',
  'quiz_unsubmitted_email',
  'survey_published_email',
  'course_news_posted_email'
);

create table manaba_courses (
  id uuid primary key default gen_random_uuid(),
  manaba_course_id text unique,
  name text not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table manaba_reports (
  id uuid primary key default gen_random_uuid(),
  course_id uuid not null references manaba_courses(id),
  manaba_report_id text unique,
  title text not null,
  starts_at timestamptz,
  due_at timestamptz,
  url text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table manaba_quizzes (
  id uuid primary key default gen_random_uuid(),
  course_id uuid not null references manaba_courses(id),
  manaba_quiz_id text unique,
  title text not null,
  starts_at timestamptz,
  due_at timestamptz,
  url text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table manaba_surveys (
  id uuid primary key default gen_random_uuid(),
  course_id uuid not null references manaba_courses(id),
  manaba_survey_id text unique,
  title text not null,
  starts_at timestamptz,
  due_at timestamptz,
  url text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table manaba_course_news (
  id uuid primary key default gen_random_uuid(),
  course_id uuid not null references manaba_courses(id),
  manaba_news_id text unique,
  title text not null,
  author_name text,
  has_attachment boolean,
  posted_at timestamptz,
  url text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table manaba_email_sources (
  id uuid primary key default gen_random_uuid(),
  source_kind manaba_email_source_kind not null,
  report_id uuid references manaba_reports(id),
  quiz_id uuid references manaba_quizzes(id),
  survey_id uuid references manaba_surveys(id),
  course_news_id uuid references manaba_course_news(id),
  gmail_message_id text not null unique,
  gmail_thread_id text,
  subject text not null,
  body_hash text not null,
  received_at timestamptz,
  parsed_at timestamptz not null default now(),
  constraint manaba_email_sources_exactly_one_target check (
    num_nonnulls(report_id, quiz_id, survey_id, course_news_id) = 1
  )
);

Fallback unique は nullable timestamp の扱いを明示するため、table constraint ではなく expression index として作る。

create unique index manaba_reports_fallback_unique
on manaba_reports (
  course_id,
  title,
  coalesce(due_at, 'infinity'::timestamptz)
)
where manaba_report_id is null;

create unique index manaba_quizzes_fallback_unique
on manaba_quizzes (
  course_id,
  title,
  coalesce(due_at, 'infinity'::timestamptz)
)
where manaba_quiz_id is null;

create unique index manaba_surveys_fallback_unique
on manaba_surveys (
  course_id,
  title,
  coalesce(due_at, 'infinity'::timestamptz)
)
where manaba_survey_id is null;

create unique index manaba_course_news_fallback_unique
on manaba_course_news (
  course_id,
  title,
  coalesce(posted_at, 'infinity'::timestamptz)
)
where manaba_news_id is null;

updated_at は default だけでは更新されないため、実 migration では trigger で自動更新する。

FilesExpand file tree

supabase-schema.md

Latest commit

History