備品データベース設計 (soshosai-equipment-db)

1. 概要

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

---

2. ER図

---

3. テーブル定義

3.1 Locations（場所マスター）

元の場所や移動先を管理する。

CREATE TABLE Locations (
    id TEXT PRIMARY KEY,              -- UUID v7
    name TEXT NOT NULL,               -- 場所名 (例: "M1", "講義棟", "体育館")
    created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
    updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
);

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

---

3.2 Organizations（使用団体マスター）

備品を使用する団体を管理する。

CREATE TABLE Organizations (
    id TEXT PRIMARY KEY,              -- UUID v7
    name TEXT NOT NULL,               -- 団体名
    created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
    updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
);

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

---

3.3 ItemTypes（備品種別マスター）

備品の種類（長机、椅子など）を管理する。

CREATE TABLE ItemTypes (
    id TEXT PRIMARY KEY,              -- UUID v7
    name TEXT NOT NULL,               -- 種別名 (例: "長机", "椅子", "プロジェクター")
    created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
    updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
);

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

---

3.4 Items（備品マスター）

個別の備品情報を管理する。

CREATE TABLE Items (
    id TEXT PRIMARY KEY,                    -- UUID v7 (Internal System ID)
    display_id TEXT NOT NULL,               -- 可読ID (例: "DESK-001") - 編集可能
    item_type_id TEXT NOT NULL,             -- ItemTypes.id (備品種別)
    status TEXT NOT NULL,                   -- STOCK, LENT, RETURNED
    condition TEXT NOT NULL,                -- NORMAL, NEEDS_REPAIR, UNDER_REPAIR, BROKEN, LOST, DISPOSED
    original_location_id TEXT,              -- Locations.id (元の場所)
    current_location_id TEXT,               -- Locations.id (現在地)
    organization_id TEXT,                   -- Organizations.id (使用団体) - NULL可
    note TEXT,                              -- 備考
    is_active INTEGER NOT NULL DEFAULT 1,   -- 有効フラグ (1: 有効, 0: 無効)
    created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
    updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
    FOREIGN KEY (item_type_id) REFERENCES ItemTypes(id),
    FOREIGN KEY (original_location_id) REFERENCES Locations(id),
    FOREIGN KEY (current_location_id) REFERENCES Locations(id),
    FOREIGN KEY (organization_id) REFERENCES Organizations(id)
);

-- インデックス（検索・ページネーション高速化）
CREATE UNIQUE INDEX idx_items_display_id ON Items(display_id);
CREATE INDEX idx_items_search_composite ON Items(item_type_id, status, condition);
CREATE INDEX idx_items_organization ON Items(organization_id);
CREATE INDEX idx_items_current_location ON Items(current_location_id);
CREATE INDEX idx_items_original_location ON Items(original_location_id);
CREATE INDEX idx_items_is_active ON Items(is_active);

-- 更新トリガー
CREATE TRIGGER trigger_items_updated_at AFTER UPDATE ON Items
FOR EACH ROW
WHEN OLD.updated_at IS NEW.updated_at
BEGIN
    UPDATE Items SET updated_at = DATETIME('now', 'localtime') WHERE id = NEW.id;
END;

ステータス（status）の定義:

値	説明
STOCK	在庫（利用可能）
LENT	貸出中
RETURNED	返却済

コンディション（condition）の定義:

値	説明
NORMAL	正常
NEEDS_REPAIR	要修理
UNDER_REPAIR	修理中
BROKEN	破損
LOST	紛失
DISPOSED	廃棄

---

3.5 ItemHistory（操作ログ）

備品の操作履歴を記録する。

CREATE TABLE ItemHistory (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    item_id TEXT NOT NULL,                  -- Items.id
    actor_id TEXT,                          -- Discord User ID（操作者）
    condition_type TEXT,                    -- NORMAL, NEEDS_REPAIR, UNDER_REPAIR, BROKEN, LOST, DISPOSED, RESET
    action_type TEXT,                       -- LEND, RETURN, MOVE, RESET
    created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
    FOREIGN KEY (item_id) REFERENCES Items(id)
);

CREATE INDEX idx_item_history_item_id ON ItemHistory(item_id);
CREATE INDEX idx_item_history_created_at ON ItemHistory(created_at);
CREATE INDEX idx_item_history_actor_id ON ItemHistory(actor_id);

アクションタイプ（action_type）の定義:

値	説明
LEND	貸出
RETURN	返却
MOVE	移動
RESET	リセット（年度更新時）

---

3.6 SyncLog（同期ログ）

Google Spreadsheetとの同期処理の実行結果を記録する。

CREATE TABLE SyncLog (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    actor_id TEXT,                          -- Discord User ID（実行者）
    direction TEXT,                         -- 'D1_TO_SHEET' or 'SHEET_TO_D1'
    status TEXT,                            -- 'SUCCESS' or 'FAILED'
    timestamp INTEGER,                      -- UNIX timestamp
    created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
);

CREATE INDEX idx_sync_log_timestamp ON SyncLog(timestamp);

---

3.7 AuthorityChangeLog（権限変更ログ）

特殊操作での権限変更履歴を記録する。

CREATE TABLE AuthorityChangeLog (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    target_discord_id TEXT NOT NULL,        -- 対象者のDiscord User ID
    target_student_id TEXT,                 -- 対象者の学籍番号
    target_name TEXT,                       -- 対象者の氏名
    old_authority TEXT NOT NULL,            -- 変更前の権限
    new_authority TEXT NOT NULL,            -- 変更後の権限
    actor_id TEXT NOT NULL,                 -- 実行者のDiscord User ID
    reason TEXT,                            -- 変更理由
    created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
);

CREATE INDEX idx_authority_change_target ON AuthorityChangeLog(target_discord_id);
CREATE INDEX idx_authority_change_actor ON AuthorityChangeLog(actor_id);
CREATE INDEX idx_authority_change_date ON AuthorityChangeLog(created_at);

---

4. System DBとの連携

備品管理システムは soshosai-system-db の Staffs テーブルを参照する。

参照するカラム:

• discord_id: Discord User ID（主キー）
• student_id: 学籍番号（NFCログイン時の検索キー）
• nfc_idm: FeliCa IDm（16進数文字列）

詳細はシステムDB設計を参照