システムデータベース設計 (`soshosai-system-db`)

1. 概要

データベース: Cloudflare D1 (soshosai-system-db)
ID形式: 原則 UUIDv7（時系列ソート可能）
日時形式: SQLiteのDATETIME関数（DATETIME('now', 'localtime')）
更新トリガー: created_at/updated_atは自動更新

2. ER図

3. テーブル定義

3.1 Staffs（スタッフマスタ）

学祭委員の情報を管理する。

CREATE TABLE Staffs (
    DiscordId  TEXT    PRIMARY KEY NOT NULL,         -- Discord User ID
    StaffName  TEXT    NOT NULL,                     -- Discord表示名
    StudentId  TEXT    UNIQUE,                       -- 学籍番号 (s1234567形式)
    RealName   TEXT,                                 -- 本名
    GlobalName TEXT,                                 -- Discord Global Name
    NfcIdm     TEXT,                                 -- FeliCa IDm (16進数文字列)
    Authority  INTEGER NOT NULL DEFAULT 5,           -- permission bits (BASE_STAFF_PERMISSIONS)
    CreatedAt  TEXT    NOT NULL DEFAULT (DATETIME('now', 'localtime')),
    UpdatedAt  TEXT    NOT NULL DEFAULT (DATETIME('now', 'localtime')),
    DeletedAt  TEXT                                  -- ソフトデリート用
);

CREATE UNIQUE INDEX idx_staffs_student_id ON Staffs(StudentId);
CREATE INDEX idx_staffs_nfc_idm ON Staffs(NfcIdm);

CREATE TRIGGER trigger_staffs_updated_at AFTER UPDATE ON Staffs
FOR EACH ROW
WHEN OLD.UpdatedAt IS NEW.UpdatedAt
BEGIN
    UPDATE Staffs SET UpdatedAt = DATETIME('now', 'localtime') WHERE DiscordId = NEW.DiscordId;
END;

権限ビット (Authority):

値	内訳	説明
`5`	VIEW(1) \| ANALYTICS(4)	一般スタッフ
`15`	上記 \| EDIT(2) \| STAFF_VIEW(8)	部門管理者
`61`	上記 \| STAFF_MANAGE(16) \| SYSTEM_MANAGE(32)	システム管理者
`63`	全ビット (ALL_PERMISSIONS)	最高管理者

3.2 Departments（部署マスタ）

部署とDiscordロールの紐付けを管理する。

CREATE TABLE Departments (
    department_id INTEGER PRIMARY KEY AUTOINCREMENT,
    department_name TEXT NOT NULL UNIQUE,           -- 部署名
    role_id TEXT,                                   -- Discord Role ID
    authority TEXT NOT NULL DEFAULT 'staff'         -- staff, dept_admin, system_admin, super_admin
);

3.3 StaffDepartments（所属中間テーブル）

スタッフと部署の多対多の関係を管理する。

CREATE TABLE StaffDepartments (
    discord_id TEXT NOT NULL,
    department_id INTEGER NOT NULL,
    PRIMARY KEY (discord_id, department_id),
    FOREIGN KEY (discord_id) REFERENCES Staffs(discord_id) ON DELETE CASCADE,
    FOREIGN KEY (department_id) REFERENCES Departments(department_id) ON DELETE CASCADE
);

CREATE INDEX idx_staff_departments_discord_id ON StaffDepartments(discord_id);
CREATE INDEX idx_staff_departments_department_id ON StaffDepartments(department_id);

3.4 BannedUsers（BANリスト）

アカウント停止情報を管理する。スタッフ・ゲスト共通。

CREATE TABLE BannedUsers (
    ban_id TEXT PRIMARY KEY,                        -- UUIDv7
    user_id TEXT NOT NULL,                          -- Discord ID または Guest ID
    type TEXT NOT NULL,                             -- 'staff', 'guest', 'global'
    banned_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
    banned_by TEXT NOT NULL,                        -- 実行者のDiscord ID
    reason TEXT,
    expires_at TEXT                                 -- NULL = 永久BAN
);

CREATE INDEX idx_banned_users_user_id ON BannedUsers(user_id);
CREATE INDEX idx_banned_users_type ON BannedUsers(type);

タイプ (type):

値	説明
`staff`	スタッフのBAN
`guest`	ゲスト（来場者）のBAN
`global`	全サービスからのBAN

3.5 Groups（グループ）

来場者のグループ情報を管理する。

CREATE TABLE Groups (
    group_id TEXT PRIMARY KEY,                      -- 'groups-' + UUIDv7
    representative_guest_id TEXT,                   -- Guests.guest_id への参照
    age_range TEXT,
    gender TEXT,
    member_count INTEGER NOT NULL,
    entrance TEXT,
    entrance_time TEXT,
    is_reserved INTEGER NOT NULL DEFAULT 0,         -- 0: 予約なし, 1: 予約あり
    reservation_time TEXT,
    created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
    updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
    deleted_at TEXT,
    FOREIGN KEY (representative_guest_id) REFERENCES Guests(guest_id)
);

CREATE TRIGGER trigger_groups_updated_at AFTER UPDATE ON Groups
FOR EACH ROW
WHEN OLD.updated_at IS NEW.updated_at
BEGIN
    UPDATE Groups SET updated_at = DATETIME('now', 'localtime') WHERE group_id = NEW.group_id;
END;

3.6 Guests（来場者）

個々の来場者情報を管理する。

CREATE TABLE Guests (
    guest_id TEXT PRIMARY KEY,                      -- 'guest-' + UUIDv7
    guest_line_id TEXT,                             -- LINE User ID
    age_range TEXT,
    gender TEXT,
    group_id TEXT NOT NULL,
    entrance TEXT,
    entrance_time TEXT,
    exit_time TEXT,
    nick_name TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
    updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
    deleted_at TEXT,
    FOREIGN KEY (group_id) REFERENCES Groups(group_id)
);

CREATE INDEX idx_guests_group_id ON Guests(group_id);
CREATE INDEX idx_guests_line_id ON Guests(guest_line_id);

CREATE TRIGGER trigger_guests_updated_at AFTER UPDATE ON Guests
FOR EACH ROW
WHEN OLD.updated_at IS NEW.updated_at
BEGIN
    UPDATE Guests SET updated_at = DATETIME('now', 'localtime') WHERE guest_id = NEW.guest_id;
END;

3.7 Locations（場所マスタ）

イベント開催場所を管理する。

CREATE TABLE Locations (
    location_id INTEGER PRIMARY KEY AUTOINCREMENT,
    location_name TEXT NOT NULL UNIQUE,
    capacity INTEGER,
    description TEXT
);

3.8 Events（イベント）

学祭中のイベント情報を管理する。

CREATE TABLE Events (
    event_id TEXT PRIMARY KEY,                      -- 'event-' + UUIDv7
    event_name TEXT NOT NULL,
    event_start_date TEXT NOT NULL,
    event_end_date TEXT NOT NULL,
    location_id INTEGER,
    max_attendees INTEGER,
    description TEXT,
    type TEXT,
    created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
    updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
    deleted_at TEXT,
    FOREIGN KEY (location_id) REFERENCES Locations(location_id)
);

CREATE INDEX idx_events_location_id ON Events(location_id);
CREATE INDEX idx_events_start_date ON Events(event_start_date);

CREATE TRIGGER trigger_events_updated_at AFTER UPDATE ON Events
FOR EACH ROW
WHEN OLD.updated_at IS NEW.updated_at
BEGIN
    UPDATE Events SET updated_at = DATETIME('now', 'localtime') WHERE event_id = NEW.event_id;
END;

3.9 EventAttendees（イベント参加者）

イベントと来場者の関連を管理する中間テーブル。

CREATE TABLE EventAttendees (
    event_id TEXT NOT NULL,
    guest_id TEXT NOT NULL,
    attendee_id TEXT NOT NULL,                      -- イベント毎の参加者ID
    created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
    updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
    deleted_at TEXT,
    PRIMARY KEY (event_id, guest_id),
    FOREIGN KEY (event_id) REFERENCES Events(event_id) ON DELETE CASCADE,
    FOREIGN KEY (guest_id) REFERENCES Guests(guest_id) ON DELETE CASCADE
);

CREATE TRIGGER trigger_event_attendees_updated_at AFTER UPDATE ON EventAttendees
FOR EACH ROW
WHEN OLD.updated_at IS NEW.updated_at
BEGIN
    UPDATE EventAttendees SET updated_at = DATETIME('now', 'localtime')
    WHERE event_id = NEW.event_id AND guest_id = NEW.guest_id;
END;

4. 権限とDiscordロールの連携

ログイン時、DiscordからユーザーのロールIDを取得
Departmentsテーブルで該当ロールの権限を確認
StaffDepartmentsを更新（所属部署の同期）
複数ロールがある場合、最高権限をStaffs.authorityに設定

1. 概要​

2. ER図​

3. テーブル定義​

3.1 Staffs（スタッフマスタ）​

3.2 Departments（部署マスタ）​

3.3 StaffDepartments（所属中間テーブル）​

3.4 BannedUsers（BANリスト）​

3.5 Groups（グループ）​

3.6 Guests（来場者）​

3.7 Locations（場所マスタ）​

3.8 Events（イベント）​

3.9 EventAttendees（イベント参加者）​

4. 権限とDiscordロールの連携​