カレンダーの繰り返し予約のTBL設計

## はじめに
カレンダーの繰り返し予約のDB設計を考えていきます。[前回](https://qiita.com/koji0705/items/ff7b8f9fed5a29816173)でRRULEを学びました。これで繰り返し予約はバッチリと思いきや、、、あれ？例外にしたイベントの出席者や添付ファイルなどはその例外に紐づく場合があります。データってどんなふうに所持しておくべきかって事がわからなくなったので、整理していきます。

## TBLとシードデータの準備

```sql:ddl
/* ──────────────────────────────────────────────
   Calendar schema with RRULE, attendees, files
   (UNIQUE 演算を式インデックスに移動)
   2025-05-28  Asia/Tokyo (+09:00)
   ──────────────────────────────────────────────*/
BEGIN;

/* 1. 拡張機能 ──────────────────────────────── */
CREATE EXTENSION IF NOT EXISTS "btree_gist";

/* 2. ENUM 定義 ─────────────────────────────── */
CREATE TYPE calendar_visibility AS ENUM ('public','private','confidential');
CREATE TYPE attendee_role        AS ENUM ('owner','required','optional');
CREATE TYPE attendee_status      AS ENUM ('needsAction','accepted','declined');

/* 3. テーブル定義 ──────────────────────────── */
CREATE TABLE users (
  id          SERIAL PRIMARY KEY,
  name        TEXT    NOT NULL,
  email       TEXT    UNIQUE NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE calendars (
  id          BIGSERIAL PRIMARY KEY,
  owner_id    INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,
  color       TEXT,
  visibility  calendar_visibility NOT NULL DEFAULT 'private',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE recurrence_rules (
  id       BIGSERIAL PRIMARY KEY,
  rrule    TEXT        NOT NULL,
  dtstart  TIMESTAMPTZ NOT NULL,
  "until"  TIMESTAMPTZ,
  tzid     TEXT        NOT NULL
);

CREATE TABLE events (
  id            BIGSERIAL PRIMARY KEY,
  calendar_id   BIGINT NOT NULL REFERENCES calendars(id) ON DELETE CASCADE,
  rrule_id      BIGINT REFERENCES recurrence_rules(id) ON DELETE SET NULL,
  title         TEXT NOT NULL,
  description   TEXT,
  time_range    TSTZRANGE NOT NULL,
  status        VARCHAR(16) NOT NULL DEFAULT 'confirmed',
  visibility    calendar_visibility NOT NULL DEFAULT 'private',
  created_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_events_time_range ON events USING GIST (time_range);

CREATE TABLE event_overrides (
  id                  BIGSERIAL PRIMARY KEY,
  parent_event_id     BIGINT NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  original_start      TIMESTAMPTZ NOT NULL,
  override_time_range TSTZRANGE   NOT NULL,
  overridden_fields   JSONB,
  UNIQUE (parent_event_id, original_start)
);

/* ↓ 修正箇所：UNIQUE 制約を外し、代わりにチェックと式インデックスで重複排除 */
CREATE TABLE event_attendees (
  id              BIGSERIAL PRIMARY KEY,
  event_id        BIGINT NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  user_id         INT REFERENCES users(id) ON DELETE CASCADE,
  guest_email     TEXT,
  role            attendee_role   NOT NULL DEFAULT 'required',
  response_status attendee_status NOT NULL DEFAULT 'needsAction',
  reminders       JSONB,
  CHECK ((user_id IS NOT NULL) OR (guest_email IS NOT NULL))
);
-- ここで式インデックスとして重複排除
CREATE UNIQUE INDEX idx_event_attendees_unique
  ON event_attendees (
    event_id,
    (COALESCE(user_id::text, guest_email))
  );

CREATE TABLE event_attachments (
  id         BIGSERIAL PRIMARY KEY,
  event_id   BIGINT NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  file_url   TEXT NOT NULL,
  mime_type  TEXT,
  title      TEXT
);

/* 4. サンプルデータ ───────────────────────── */

INSERT INTO users (name, email)
VALUES
  ('Alice', 'alice@example.com'),
  ('Bob',   'bob@example.com');

INSERT INTO calendars (owner_id, name, color, visibility)
VALUES
  (1, 'Alice Primary', 'blue',  'private'),
  (2, 'Team Shared',   'green', 'public');

-- 1) recurrence_rules を追加（ここで recurrence_rules_id_seq が advance）
INSERT INTO recurrence_rules (rrule, dtstart, "until", tzid)
VALUES (
  'FREQ=WEEKLY;BYDAY=MO,WE,FR',
  '2025-06-02 09:00:00+09',
  '2025-09-30 09:15:00+09',
  'Asia/Tokyo'
);

-- 2) events を追加（直前の INSERT で生成された recurrence_rules の id を取得）
INSERT INTO events (calendar_id, rrule_id, title, description, time_range)
VALUES (
  1,
  currval('recurrence_rules_id_seq'),
  'Morning Stand-up',
  'Daily 15-minute sync',
  tstzrange('2025-06-02 09:00:00+09','2025-06-02 09:15:00+09','[)')
);

-- 3) event_overrides / event_attendees / event_attachments も同様に currval('events_id_seq') を使って
INSERT INTO event_overrides (parent_event_id, original_start, override_time_range, overridden_fields)
VALUES (
  currval('events_id_seq'),
  '2025-06-09 09:00:00+09',
  tstzrange('2025-06-09 10:00:00+09','2025-06-09 10:15:00+09','[)'),
  '{"time_shift_minutes":60}'
);

INSERT INTO event_attendees (event_id, user_id, role, response_status)
VALUES
  (currval('events_id_seq'), 1, 'owner',    'accepted'),
  (currval('events_id_seq'), 2, 'required', 'needsAction');

INSERT INTO event_attachments (event_id, file_url, mime_type, title)
VALUES (
  currval('events_id_seq'),
  'https://example.com/docs/standup-agenda.pdf',
  'application/pdf',
  'Stand-up Agenda'
);

COMMIT;
```

![calendar_er.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/599049/4dc585bb-83ea-4f6a-81b8-e032c45ddf50.png)

## ユースケース1：イベントを作成する
繰り返し予約とか関係なく、単発のイベントを作成します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/599049/4f6da913-576d-4dc1-bda4-edf8a8158831.png)


```sql:ユースケース1：イベントを作成する

INSERT INTO events (
  calendar_id,
  rrule_id,   -- 繰り返しルールなしなので NULL
  title,
  time_range  -- [開始, 終了)
) VALUES (
  1, 
  NULL,
  '単発イベント',
  tstzrange(
    '2025-05-27 11:00:00+09',
    '2025-05-27 12:00:00+09',
    '[)'
  )
)


 id | calendar_id | rrule_id |    title     | description |                     time_range                      |  status   | visibility |          created_at           |          updated_at           
----+-------------+----------+--------------+-------------+-----------------------------------------------------+-----------+------------+-------------------------------+-------------------------------
  3 |           1 |          | 単発イベント |             | ["2025-05-27 02:00:00+00","2025-05-27 03:00:00+00") | confirmed | private    | 2025-05-28 02:18:52.360079+00 | 2025-05-28 02:18:52.360079+00
(1 row)
```

→ここは簡単ですね。では次です。

## ユースケース2:繰り返しイベントを作成する
平日の毎朝 9:30 〜 9:45 にデイリースクラムのイベントを作成します。

```sql:
BEGIN;

-- 1) 繰り返しルールを作成（平日: 月〜金）
INSERT INTO recurrence_rules (rrule, dtstart, "until", tzid)
VALUES (
  'FREQ=WEEKLY;BYDAY=MO,TU,WE,TH,FR',   -- 月曜〜金曜
  '2025-06-02 09:00:00+09',             -- シリーズ開始日時
  '2025-09-30 09:15:00+09',             -- シリーズ終了日時（NULL にすれば無期限）
  'Asia/Tokyo'
);

-- 2) events に親イベントを挿入
INSERT INTO events (
  calendar_id,
  rrule_id,
  title,
  description,
  time_range
) VALUES (
  1,
  currval('recurrence_rules_id_seq'),   -- 直前で作成したルールID
  'Daily Scrum',
  '平日毎朝の15分デイリースクラム',
  tstzrange(
    '2025-06-02 09:00:00+09',
    '2025-06-02 09:15:00+09',
    '[)'
  )
)
RETURNING id;  -- 作成された親イベントのIDを取得

COMMIT;
```

```sql:実行結果
calendar=# select e.id,calendar_id,rrule_id,title,time_range,rr.* from events as e inner join recurrence_rules as rr on e.rrule_id = rr.id where e.id = 4;
 id | calendar_id | rrule_id |    title    |                     time_range                      | id |              rrule               |        dtstart         |         until          |    tzid    
----+-------------+----------+-------------+-----------------------------------------------------+----+----------------------------------+------------------------+------------------------+------------
  4 |           1 |        2 | Daily Scrum | ["2025-06-02 00:00:00+00","2025-06-02 00:15:00+00") |  2 | FREQ=WEEKLY;BYDAY=MO,TU,WE,TH,FR | 2025-06-02 00:00:00+00 | 2025-09-30 00:15:00+00 | Asia/Tokyo
(1 row)
```
→できましたね！さぁ、ここから例外を作っていきます。難しくなっていきますよ！！

## ユースケース3:繰り返し予約の中で1つイベントを対象外にするイベント
繰り返しイベントの中で1つだけ例外を持ちたい。実務の中だとよく発生するケースですね。
RRULEでは`EXDATE`で表現しています。ただ、GoogleCalendarだとRRULEの箇所に直接記述して保持するのでなく、EXDATEは使っていないようです。例外のものとしてTBLに格納しているようです。

https://developers.google.com/workspace/calendar/api/guides/recurringevents?hl=ja

```sql:
calendar=# BEGIN;

-- 例：2025-06-04 09:00 の回だけキャンセル（平日スクラムの親ID = currval）
INSERT INTO event_overrides (
  parent_event_id,
  original_start,
  override_time_range,
  overridden_fields
) VALUES (
  currval('events_id_seq'),                -- または既存の events.id
  '2025-06-04 09:00:00+09',                -- 元の開始時刻
  tstzrange(
    '2025-06-04 09:00:00+09',
    '2025-06-04 09:15:00+09',
    '[)'
  ),
  '{"cancelled": true}'::jsonb             -- JSON で「キャンセル」をマーク
);

COMMIT;
BEGIN
INSERT 0 1
COMMIT
```

```sql:実行結果
calendar=# select * from event_overrides where id = 2;
 id | parent_event_id |     original_start     |                 override_time_range                 |  overridden_fields  
----+-----------------+------------------------+-----------------------------------------------------+---------------------
  2 |               3 | 2025-06-04 00:00:00+00 | ["2025-06-04 00:00:00+00","2025-06-04 00:15:00+00") | {"cancelled": true}
(1 row)
```

----

## 出席者や添付ファイルの紐付きを特定のイベントのみにする
上の例では、イベントの内容に閉じていました。ここからはイベントに紐づくリソースの出席者や添付ファイルと連携させていきます。

### 単発のイベントに出席者を紐づける

```sql:
-- ① 単発イベントを作成して id を取得
INSERT INTO events (
  calendar_id,
  rrule_id,     -- 単発なので NULL
  title,
  description,
  time_range
) VALUES (
  1,
  NULL,
  'Project Kickoff',
  '新規プロジェクトキックオフミーティング',
  tstzrange(
    '2025-06-10 10:00:00+09',
    '2025-06-10 11:30:00+09',
    '[)'
  )
)
RETURNING id;  -- この時点で events_id_seq が advance

-- ② currval() で直前に払い出された events.id を使って出席者を追加
INSERT INTO event_attendees (
  event_id,
  user_id,
  role,
  response_status
) VALUES
  (currval('events_id_seq'), 1, 'owner',    'accepted'),
  (currval('events_id_seq'), 2, 'required', 'needsAction');
```

```sql:実行結果
calendar=# select e.id,e.title, ea.* from event_attendees as ea inner join events as e on e.id = ea.event_id  where event_id =5;
 id |      title      | id | event_id | user_id | guest_email |   role   | response_status | reminders 
----+-----------------+----+----------+---------+-------------+----------+-----------------+-----------
  5 | Project Kickoff |  5 |        5 |       1 |             | owner    | accepted        | 
  5 | Project Kickoff |  6 |        5 |       2 |             | required | needsAction     | 
(2 rows)
```
→はい、ここまでは簡単ですね。次にいきましょう。

### 繰り返しイベントに出席者を紐づける
RRULEを定義の上、作っていきます。

```sql:
BEGIN;

-- 1) 繰り返しルールを作成（平日スクラム）
INSERT INTO recurrence_rules (rrule, dtstart, "until", tzid)
VALUES (
  'FREQ=WEEKLY;BYDAY=MO,TU,WE,TH,FR',
  '2025-06-02 09:30:00+09',
  NULL,
  'Asia/Tokyo'
);

-- 2) events に親イベントを挿入
INSERT INTO events (
  calendar_id,
  rrule_id,
  title,
  description,
  time_range
) VALUES (
  1,
  currval('recurrence_rules_id_seq'),
  'Daily Scrum',
  '平日毎朝の15分デイリースクラム',
  tstzrange(
    '2025-06-02 09:30:00+09',
    '2025-06-02 09:45:00+09',
    '[)'
  )
)
RETURNING id;

-- 3) 直前に作成された親イベント（series）の ID を使って出席者を追加
INSERT INTO event_attendees (
  event_id,
  user_id,
  role,
  response_status
) VALUES
  (currval('events_id_seq'), 1, 'owner',    'accepted'),    -- Alice を主催者に
  (currval('events_id_seq'), 2, 'required', 'needsAction'), -- Bob を必須参加者に
  (currval('events_id_seq'), 3, 'optional', 'needsAction'); -- Carol をオプション参加者に

COMMIT;
```

```sql:実行結果の確認
calendar=# select e.id,e.rrule_id, e.title, ea.*,rr.* from event_attendees as ea inner join events as e on e.id = ea.event_id left join recurrence_rules as rr on rr.id = e.rrule_id  where event_id =7;
 id | rrule_id |    title    | id | event_id | user_id | guest_email |   role   | response_status | reminders | id |              rrule               |        dtstart         | until |    tzid    
----+----------+-------------+----+----------+---------+-------------+----------+-----------------+-----------+----+----------------------------------+------------------------+-------+------------
  7 |        4 | Daily Scrum | 10 |        7 |       1 |             | owner    | accepted        |           |  4 | FREQ=WEEKLY;BYDAY=MO,TU,WE,TH,FR | 2025-06-02 00:30:00+00 |       | Asia/Tokyo
  7 |        4 | Daily Scrum | 11 |        7 |       2 |             | required | needsAction     |           |  4 | FREQ=WEEKLY;BYDAY=MO,TU,WE,TH,FR | 2025-06-02 00:30:00+00 |       | Asia/Tokyo
  7 |        4 | Daily Scrum | 12 |        7 |       3 |             | optional | needsAction     |           |  4 | FREQ=WEEKLY;BYDAY=MO,TU,WE,TH,FR | 2025-06-02 00:30:00+00 |       | Asia/Tokyo
(3 rows)
```

### 繰り返し予約の中で1つの予定のみ、参加者を減らす。
さぁ、だんだん難易度が上がっていきます。ここから本腰入れてやっていきましょう。
繰り返しイベントの参加者は現在3人。そこから特定のイベントのみ参加者を2人にします。
これはそもそもどう表現するべきなのかもよくわからないですよね。。。

```sql:
-- 例：親イベント ID = 7、2025-06-05 09:30 の回から Bob(user_id=2) を外す
INSERT INTO event_overrides (
  parent_event_id,       -- シリーズ親の events.id
  original_start,        -- 除外・変更対象の回の開始時刻
  override_time_range,   -- 対象回の本来の時間帯
  overridden_fields      -- 差分情報を JSONB で保持
) VALUES (
  7,
  '2025-06-05 09:30:00+09',
  tstzrange(
    '2025-06-05 09:30:00+09',
    '2025-06-05 09:45:00+09',
    '[)'
  ),
  '{
     "removed_attendees": [2]
   }'::jsonb
);
```
→`event_overrides`です。json内でデータを持つので、表現はしやすいですね。
ただ、jsonはSQLで取得がしにくいのでおすすめはしないです。
学習しているうちに気がついたのですが、これは<strong>マスターシリーズ＋差分オーバーライド（JSON差分）方式</strong>という方式らしいです。Googleカレンダーもこの方式を採用しているのかなと思ったのですが、どうやら違うらしいです。Googleカレンダーは<strong>マスターシリーズ＋子イベント方式</strong>方式を採用しているらしいです。なるほど、、、確かにこれは実運用には耐えられないですもんね。。。
今回はこれで終わりとしますが、次にマスターシリーズ＋子イベント方式を学んでいきます。