規約の判定と、蓄積のためのデータモデル

-- 選手は永続。卒業しても行は消さない。所属は enrollments が持つ。
CREATE TABLE players (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  birth_date    date        NOT NULL,
  sex           char(1),
  status        text        NOT NULL DEFAULT 'active'
                CHECK (status IN ('active','graduated','withdrawn','deleted')),
  graduated_on  date,
  created_at    timestamptz NOT NULL DEFAULT now()
);

-- 年度ごとの所属。移籍・カテゴリ変更もここで表現する。
CREATE TABLE enrollments (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  player_id   uuid NOT NULL REFERENCES players(id),
  season      int  NOT NULL,
  category    text NOT NULL,   -- 'U-10' | 'U-11' | ...
  team_id     uuid NOT NULL,
  prefecture  text NOT NULL,
  jersey_no   int,
  UNIQUE (player_id, season, category)
);

CREATE TABLE guardians (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email      citext NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- 両親2アカウント、兄弟の複数子に対応するため多対多。
CREATE TABLE guardian_links (
  guardian_id uuid NOT NULL REFERENCES guardians(id),
  player_id   uuid NOT NULL REFERENCES players(id),
  relation    text,
  PRIMARY KEY (guardian_id, player_id)
);

-- 同意した時点の規約文面を特定できるようにする。
CREATE TABLE policy_documents (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  kind          text NOT NULL CHECK (kind IN ('terms','privacy')),
  version       text NOT NULL,
  body_sha256   text NOT NULL,
  effective_on  date NOT NULL,
  UNIQUE (kind, version)
);

-- 目的は必ず個別に。まとめて1つにしないこと。
CREATE TABLE consent_purposes (
  code            text PRIMARY KEY,
  label           text NOT NULL,
  is_sensitive    boolean NOT NULL DEFAULT false,
  default_granted boolean NOT NULL DEFAULT false
);
INSERT INTO consent_purposes VALUES
  ('growth_record',   '成長の記録',               false, true ),
  ('oral_selfcheck',  '口腔セルフチェック',       false, false),
  ('clinical_screen', '歯科検診の結果',          true,  false),
  ('anon_stats',      '匿名統計としての利用',     false, false),
  ('ad_optin',        '歯科医院の案内の受信',     false, false);

-- 付与も撤回も追記。UPDATE / DELETE は行わない。
-- 「現在の状態」は下のビューで導出する。
CREATE TABLE consents (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  guardian_id  uuid NOT NULL REFERENCES guardians(id),
  player_id    uuid NOT NULL REFERENCES players(id),
  purpose_code text NOT NULL REFERENCES consent_purposes(code),
  policy_id    uuid NOT NULL REFERENCES policy_documents(id),
  action       text NOT NULL CHECK (action IN ('grant','withdraw')),
  acted_at     timestamptz NOT NULL DEFAULT now(),
  ip_hash      text,
  ui_surface   text
);
CREATE INDEX ON consents (player_id, purpose_code, acted_at DESC);

-- 現在の同意状態。過去の任意時点も acted_at で絞れば再現できる。
CREATE VIEW v_consent_now AS
SELECT DISTINCT ON (player_id, purpose_code)
       player_id, purpose_code, action, acted_at
FROM consents
ORDER BY player_id, purpose_code, acted_at DESC;

-- 上書きしない。訂正は新しい行を入れて旧行に superseded_by を張る。
CREATE TABLE measurements (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  player_id     uuid NOT NULL REFERENCES players(id),
  metric        text NOT NULL CHECK (metric IN ('height_cm','weight_kg')),
  value         numeric(5,1) NOT NULL,
  observed_on   date NOT NULL,
  recorded_by   uuid REFERENCES guardians(id),
  superseded_by uuid REFERENCES measurements(id),
  created_at    timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON measurements (player_id, metric, observed_on);

-- 設問は版管理する。文言を変えても過去と比較できるように。
CREATE TABLE oral_check_questions (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  code        text NOT NULL,      -- 'mouth_breathing' 等
  version     int  NOT NULL,
  body        text NOT NULL,
  flag_answer text NOT NULL,      -- サインとして数える回答
  retired_at  timestamptz,
  UNIQUE (code, version)
);

CREATE TABLE oral_check_sessions (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  player_id    uuid NOT NULL REFERENCES players(id),
  answered_by  uuid NOT NULL REFERENCES guardians(id),
  period_ym    date NOT NULL,      -- 月初日で保持
  completed_at timestamptz,
  UNIQUE (player_id, period_ym)
);

-- 「サイン◯件」は保存しない。設問版と結合して都度導出する。
-- 判定ロジックを変えたとき、過去分も新ロジックで見直せる。
CREATE TABLE oral_check_answers (
  session_id  uuid NOT NULL REFERENCES oral_check_sessions(id),
  question_id uuid NOT NULL REFERENCES oral_check_questions(id),
  answer      text NOT NULL CHECK (answer IN ('yes','no','unknown')),
  PRIMARY KEY (session_id, question_id)
);

-- FDI歯式番号（11-48）。観察日つきの追記型。
CREATE TABLE dentition_records (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  player_id   uuid NOT NULL REFERENCES players(id),
  tooth_fdi   int  NOT NULL,
  state       text NOT NULL CHECK (state IN ('primary','erupting','permanent','missing')),
  observed_on date NOT NULL
);
CREATE INDEX ON dentition_records (player_id, observed_on DESC);

-- 交換目安は measurements と結合して導出する（保存しない）。
CREATE TABLE mouthguards (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  player_id     uuid NOT NULL REFERENCES players(id),
  started_on    date NOT NULL,
  ended_on      date,
  end_reason    text,
  height_at_fit numeric(5,1)
);

-- 別スキーマに置き、アプリの通常ロールからは触れないようにする。
CREATE SCHEMA sensitive;
REVOKE ALL ON SCHEMA sensitive FROM app_readwrite;

-- 歯科医による検診結果。取得には個別の明示同意が必須。
CREATE TABLE sensitive.clinical_screenings (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  player_id   uuid NOT NULL,
  screened_on date NOT NULL,
  event_id    uuid,                    -- 大会ブース等
  provider    text NOT NULL,           -- 実施した歯科医院
  findings    bytea NOT NULL,          -- 列暗号化して格納
  consent_id  uuid NOT NULL,           -- 根拠となる同意レコード
  retain_until date NOT NULL
);

-- 口腔内写真。既定は端末内保存。アップロードは同意時のみ。
CREATE TABLE sensitive.media_assets (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  player_id     uuid NOT NULL,
  kind          text NOT NULL CHECK (kind IN ('intraoral','other')),
  storage_key   text NOT NULL,
  captured_on   date NOT NULL,
  consent_id    uuid NOT NULL,
  -- PP第12条の広報利用の対象外であることをスキーマで固定する
  promo_allowed boolean NOT NULL DEFAULT false
                CHECK (promo_allowed = false)
);

-- 要配慮に触れた操作は全件記録する。
CREATE TABLE sensitive.access_logs (
  id         bigserial PRIMARY KEY,
  actor_id   uuid NOT NULL,
  actor_role text NOT NULL,
  player_id  uuid NOT NULL,
  resource   text NOT NULL,
  action     text NOT NULL,
  at         timestamptz NOT NULL DEFAULT now()
);

-- 生成のたびに1バッチ。加工方法とk値を必ず残す。
CREATE TABLE anon_batches (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  generated_on date NOT NULL,
  method      text NOT NULL,
  k_anonymity int  NOT NULL CHECK (k_anonymity >= 10),
  source_note text NOT NULL,
  published_at timestamptz          -- 法定の公表を行った日時
);

-- player_id を持たない。個票へ戻れないことが匿名加工情報の要件。
-- cohort_key は 都道府県 x 学年 x 性別 のように粒度を固定する。
CREATE TABLE anon_facts (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  batch_id   uuid NOT NULL REFERENCES anon_batches(id),
  cohort_key text NOT NULL,
  metric     text NOT NULL,
  value      numeric NOT NULL,
  n          int NOT NULL CHECK (n >= 10)   -- 小さすぎる母数は出さない
);