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

1. 概要

データベース: Cloudflare D1（soshosai-equipment-db）
責務: 備品、マスタ、割当、スキャン、貸出セッション、ラベル、監査、同期情報を保持する
ID形式: 新規エンティティの主キーはUUID v7を基本とする。ただし、既存API互換のためItemHistory.id、SyncLog.id、AuthorityChangeLog.idはINTEGER PRIMARY KEY AUTOINCREMENTを維持する
日時形式: 既存スキーマとの互換性を保ち、SQLiteの日時文字列またはUNIX timestampを用途に応じて使用する
データ保持: 監査ログを含むデータは無期限に保持する。年度更新でも過去の履歴を削除しない
機密情報: 認証・セッション情報は共通認証基盤（login-api、discord-auth-worker、line-auth-worker、unified-auth-worker等）の管轄とし、本データベースでは保持しない

備品データの正本は本データベースです。Google SpreadsheetとのExport / Importは対象外とし、外部の申請システムから取得する必要数や大学環境向けエクスポートは、それぞれ専用の連携処理として扱います。

2. ER図

ItemHistoryは、要件定義書のItemChangeLogに相当する監査履歴です。既存APIとの互換性のためテーブル名はItemHistoryを維持し、変更前後の値、操作元、端末識別子、理由を記録できるようにします。

ER図には監査・同期に必要な列も含めているが、将来列を追加する場合はDDLを正本として更新する。外部キーの作成順序を明確にするため、マイグレーションではLendingSessionをScanLogより先に作成し、EquipmentRequestImportLogはEquipmentRequestsの後に作成する。

Items.updated_atは既存互換のため秒精度のサーバー記録時刻として保持するが、競合判定には使用しない。競合判定の正本はサーバーが更新ごとに進めるItems.revisionであり、occurred_at（現場で発生した時刻）や端末時計と比較しない。

3. テーブル定義

3.1 Locations（場所マスター）

元の場所、現在地、持出先、講義棟前などの場所を管理します。location_typeは場所の種別であり、個別の場所名ではない。取りうる値はROOM（教室）、WAREHOUSE（倉庫）、COLLECTION_POINT（回収・中間集積場所）、DESTINATION（持出先）、OTHER（その他）とする。「講義棟前」はnameに保存し、location_typeはCOLLECTION_POINTとする。参照中の場所は物理削除せず、is_activeを0にして無効化します。講義棟前を中間集積場所としてItems.current_location_idに記録するかどうかは、スパイスとの回収タイミング交渉に応じたD-04のパターンで決定します。

CREATE TABLE Locations (
	id TEXT PRIMARY KEY,
	name TEXT NOT NULL,
	location_type TEXT NOT NULL CHECK (location_type IN ('ROOM', 'WAREHOUSE', 'COLLECTION_POINT', 'DESTINATION', 'OTHER')),
	is_active INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1)),
	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;

CREATE UNIQUE INDEX idx_locations_name_active ON Locations(name) WHERE is_active = 1;
CREATE INDEX idx_locations_active ON Locations(is_active);

is_active=0の論理削除済みレコードは名前を占有しない。したがって、無効化済みの場所と同じ名前を新しい有効レコードとして登録できる。既存レコードを再有効化する場合は、同名の有効レコードがないことを確認する。

3.2 Organizations（参加団体マスター）

参加団体を管理します。団体識別用QRコードの発行・配布は別botの担当であり、本テーブルに配布先DiscordチャンネルやQR発行情報を保持しません。

CREATE TABLE Organizations (
	id TEXT PRIMARY KEY,
	name TEXT NOT NULL,
	external_id TEXT,
	is_active INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1)),
	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;

CREATE INDEX idx_organizations_external_id ON Organizations(external_id);
CREATE INDEX idx_organizations_active ON Organizations(is_active);

3.3 Projects（企画マスター）

参加団体に属する出展単位を管理します。同一団体が複数の企画を持てるため、割当は企画に紐付けます。団体貸出時の照合では、貸出セッションの団体に属する企画の割当をまとめて対象にします。

CREATE TABLE Projects (
	id TEXT PRIMARY KEY,
	organization_id TEXT NOT NULL,
	name TEXT NOT NULL,
	external_id TEXT,
	festival_year INTEGER NOT NULL,
	is_active INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1)),
	created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
	updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
	FOREIGN KEY (organization_id) REFERENCES Organizations(id)
);

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

CREATE UNIQUE INDEX idx_projects_external_year
	ON Projects(external_id, festival_year);
CREATE INDEX idx_projects_organization ON Projects(organization_id);
CREATE INDEX idx_projects_year_active ON Projects(festival_year, is_active);

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

長机、椅子、コーン、バケツ、消火器などの備品種別を管理します。nameは有効な種別間で一意とし、is_active=0の論理削除済みレコードは名前を占有しない。

CREATE TABLE ItemTypes (
	id TEXT PRIMARY KEY,
	name TEXT NOT NULL,
	is_active INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1)),
	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;

CREATE UNIQUE INDEX idx_item_types_name_active ON ItemTypes(name) WHERE is_active = 1;
CREATE INDEX idx_item_types_active ON ItemTypes(is_active);

3.5 EquipmentRequests（備品申請数）

申請システムから取得した、企画ごと・備品種別ごとの必要数を保持します。割当案作成時の入力正本であり、現在値の取込日時を記録します。過去の取込値はEquipmentRequestImportLogへ保存し、EquipmentRequests.imported_atの上書きによって履歴が失われないようにします。

CREATE TABLE EquipmentRequests (
	id TEXT PRIMARY KEY,
	project_id TEXT NOT NULL,
	item_type_id TEXT NOT NULL,
	requested_quantity INTEGER NOT NULL CHECK (requested_quantity >= 0 AND typeof(requested_quantity) = 'integer'),
	imported_at TEXT NOT NULL,
	created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
	updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
	FOREIGN KEY (project_id) REFERENCES Projects(id),
	FOREIGN KEY (item_type_id) REFERENCES ItemTypes(id)
);

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

CREATE UNIQUE INDEX idx_equipment_requests_project_type
	ON EquipmentRequests(project_id, item_type_id);

再取込は、対象年度の全件スナップショットであることを明示した場合に限って現在値を更新する。部分取得や通信失敗では既存値と確定済み割当を変更しない。全件スナップショットの各行をimport_batch_id単位でEquipmentRequestImportLogへ記録し、同じproject_idとitem_type_idの現在値を原子的にupsertする。

申請数が減った場合、すでにCONFIRMEDまたはLENTの割当は自動で取り消さず、過剰割当として取込結果に警告を出す。PROPOSEDのうち新しい必要数を超えるものはCANCELLED（REQUEST_QUANTITY_DECREASED）へ変更できるが、確定済み割当の取消は管理者が理由を入力して個別に行う。企画が申請システムから消えた場合や無効化された場合も申請行・取込履歴・確定済み割当を物理削除せず、新規割当案の対象から除外し、既存の確定済み割当は同じ管理者確認を経て扱う。全件スナップショットで欠落した行は必要数0として履歴に記録するが、既存の確定済み割当を自動取消しない。

CREATE TABLE EquipmentRequestImportLog (
	id TEXT PRIMARY KEY,
	import_batch_id TEXT NOT NULL,
	equipment_request_id TEXT NOT NULL,
	project_id TEXT NOT NULL,
	item_type_id TEXT NOT NULL,
	previous_requested_quantity INTEGER,
	requested_quantity INTEGER NOT NULL CHECK (requested_quantity >= 0 AND typeof(requested_quantity) = 'integer'),
	import_status TEXT NOT NULL CHECK (import_status IN ('UPSERTED', 'UNCHANGED', 'REMOVED_FROM_SOURCE', 'PROJECT_INACTIVE', 'REJECTED')),
	imported_at TEXT NOT NULL,
	FOREIGN KEY (equipment_request_id) REFERENCES EquipmentRequests(id),
	FOREIGN KEY (project_id) REFERENCES Projects(id),
	FOREIGN KEY (item_type_id) REFERENCES ItemTypes(id)
);

CREATE INDEX idx_equipment_request_import_log_batch
	ON EquipmentRequestImportLog(import_batch_id);
CREATE INDEX idx_equipment_request_import_log_request
	ON EquipmentRequestImportLog(equipment_request_id, imported_at);

3.6 Items（備品マスター）

個別の備品を管理します。QRコードには変更されないidを使用し、display_idなどの表示属性を直接埋め込みません。Itemsは年度をまたいで再利用する物理備品のマスターであり、年度は持たせない。年度単位の割当・申請・監査範囲はAllocations.project_idからProjects.festival_yearを辿って絞り込む設計とする。

CREATE TABLE Items (
	id TEXT PRIMARY KEY,
	display_id TEXT NOT NULL,
	item_type_id TEXT NOT NULL,
	status TEXT NOT NULL CHECK (status IN ('STOCK', 'ALLOCATED', 'LENDING_SCANNED', 'LENT', 'RETURNED')),
	condition TEXT NOT NULL CHECK (condition IN ('NORMAL', 'NEEDS_REPAIR', 'UNDER_REPAIR', 'BROKEN', 'LOST', 'DISPOSED')),
	original_location_id TEXT,
	current_location_id TEXT,
	destination_id TEXT,
	organization_id TEXT,
	project_id TEXT,
	note TEXT,
	is_active INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1)),
	revision INTEGER NOT NULL DEFAULT 1,
	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 (destination_id) REFERENCES Locations(id),
	FOREIGN KEY (organization_id) REFERENCES Organizations(id),
	FOREIGN KEY (project_id) REFERENCES Projects(id)
);

CREATE TRIGGER trigger_items_updated_at AFTER UPDATE ON Items
FOR EACH ROW
WHEN NEW.revision = OLD.revision
BEGIN
	UPDATE Items
	SET updated_at = DATETIME('now', 'localtime'), revision = OLD.revision + 1
	WHERE id = NEW.id;
END;

-- revision はオフライン同期の競合判定の正本（#754）。アプリケーションからの
-- 直接書き込みを拒否し、上記トリガー自身による +1 のみを許可する。
-- +1 と同時に updated_at も直接指定された場合は拒否する（#793レビュー指摘）。
CREATE TRIGGER trigger_items_guard_revision
BEFORE UPDATE ON Items
FOR EACH ROW
WHEN (NEW.revision != OLD.revision AND NEW.revision != OLD.revision + 1)
	OR (
		NEW.revision = OLD.revision + 1
		AND NEW.updated_at IS NOT OLD.updated_at
		AND NEW.updated_at IS NOT DATETIME('now', 'localtime')
	)
BEGIN
	SELECT RAISE(ABORT, 'Items.revision is managed by trigger_items_updated_at and must not be set directly');
END;

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_project ON Items(project_id);
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_destination ON Items(destination_id);
CREATE INDEX idx_items_is_active ON Items(is_active);

statusは貸出状態、conditionは備品状態です。既存APIの列名を維持しつつ、要件上は次の状態を扱います。revisionはサーバーが備品更新ごとに単調増加させる競合判定用の値であり、端末の時計や秒精度のupdated_atに依存しません。

列	値の例	説明
`status`	`STOCK`	割当前または在庫
`status`	`ALLOCATED`	企画へ割当済み
`status`	`LENDING_SCANNED`	団体貸出セッション中に担当者がFZ-N1でスキャン済み
`status`	`LENT`	貸出中
`status`	`RETURNED`	返却済み
`condition`	`NORMAL`	正常
`condition`	`NEEDS_REPAIR`	要修理
`condition`	`UNDER_REPAIR`	修理中
`condition`	`BROKEN`	破損
`condition`	`LOST`	紛失
`condition`	`DISPOSED`	廃棄

condition_tag_idは条件タグを別テーブルで管理する設計へ移行する場合の論理属性です。現行の詳細仕様では、既存のcondition列に事前定義タグの値を保持します。

current_location_idは実際に備品が存在する場所を表し、destination_id（持出先として予定された場所）とは区別します。返却成立の時点と中間集積場所を現在地として記録するかどうかは別の運用判断であり、D-02〜D-05のパターンに従います。

貸出状態の正式な遷移は、STOCK → ALLOCATED（割当確定）、ALLOCATED → LENDING_SCANNED（通常貸出セッションで一致したスキャン）、LENDING_SCANNED → LENT（セッション終了）、LENT → RETURNED（返却成立）、RETURNED → STOCK（足拭き・教室収容完了）とする。指定外スキャンでは状態を変更しない。ALLOCATED → LENTの直接遷移は、EQUIPMENT_BULK_OPERATIONを持つ学祭委員が理由付きの例外処理を行う場合だけ許可する。

3.7 Allocations（備品割当）

備品と企画、持出先の対応付けを管理します。割当案の作成、手動修正、確定の履歴はItemHistoryにも記録します。

CREATE TABLE Allocations (
	id TEXT PRIMARY KEY,
	item_id TEXT NOT NULL,
	project_id TEXT NOT NULL,
	destination_id TEXT,
	status TEXT NOT NULL CHECK (status IN ('PROPOSED', 'CONFIRMED', 'CANCELLED')),
	assigned_at TEXT NOT NULL,
	assigned_by TEXT,
	cancelled_at TEXT,
	cancelled_by TEXT,
	cancel_reason TEXT,
	created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
	updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
	FOREIGN KEY (item_id) REFERENCES Items(id),
	FOREIGN KEY (project_id) REFERENCES Projects(id),
	FOREIGN KEY (destination_id) REFERENCES Locations(id)
);

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

CREATE UNIQUE INDEX idx_allocations_active_item
	ON Allocations(item_id)
	WHERE status IN ('PROPOSED', 'CONFIRMED');
CREATE INDEX idx_allocations_project ON Allocations(project_id);
CREATE INDEX idx_allocations_destination ON Allocations(destination_id);

Allocations.statusはPROPOSED、CONFIRMED、CANCELLEDのいずれかとする。idx_allocations_active_itemは同一備品に同時に存在できる有効割当（PROPOSEDまたはCONFIRMED）を1件に制限し、CANCELLEDは制約対象外とする。割当案は、講義棟出展で使用する備品を除外し、必要数を満たす範囲で同一企画の備品が同一教室に集約されるよう作成します。集約できない場合は、複数教室への分散と不足数を記録・表示します。

年度更新では、過年度のPROPOSEDおよびCONFIRMEDを物理削除せず、CANCELLEDへ変更し、cancelled_at、cancelled_by、cancel_reasonにYEAR_RESETを記録する。これにより監査履歴を保持したまま、次年度の新しい割当を部分ユニークインデックスの制約なく作成できる。すでに貸出中の備品も割当レコード自体は削除せず、年度更新処理のItemHistoryへ状態リセットを記録する。

3.8 ItemHistory（備品変更監査ログ / 要件上のItemChangeLog）

備品の全項目変更を、変更前後の値と操作情報付きで記録します。既存APIが利用するItemHistoryという名前を維持します。

CREATE TABLE ItemHistory (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	item_id TEXT NOT NULL,
	field_name TEXT,
	before_value TEXT,
	after_value TEXT,
	actor_id TEXT,
	condition_type TEXT,
	action_type TEXT,
	source TEXT NOT NULL,
	device_id TEXT,
	reason TEXT,
	operation_id TEXT,
	occurred_at TEXT NOT NULL,
	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_occurred_at ON ItemHistory(occurred_at);
CREATE INDEX idx_item_history_actor_id ON ItemHistory(actor_id);
CREATE INDEX idx_item_history_source ON ItemHistory(source);

ItemHistory.sourceにはSho-Room、FZ-N1、API、lending-session、placement-confirmation、return-operationなど、操作経路を記録します。ScanLog.sourceは後述のDDLで定める列挙値に限定します。一括操作では操作単位の記録に加え、対象となった各備品の変更を追跡できるようにします。occurred_atは端末または現場で操作が発生した日時、created_atはサーバーが受信・記録した日時です。通常の更新・削除や年度更新でこのログを削除してはいけません。FZ-N1からの操作はdevice_idで端末を識別します。

3.9 ScanLog（スキャン履歴）

個別スキャン、団体操作、貸出時スキャン、持出後の二重確認スキャン、返却時の逐次・全数スキャン、貼付後確認など、備品QRの読み取りを記録します。一致したスキャンだけでなく、指定外備品のスキャンも保存します。ただし、返却パターンAのD-03では2日目夜のスキャン自体を行わないため、該当するScanLogも作成しません。

CREATE TABLE ScanLog (
	id TEXT PRIMARY KEY,
	item_id TEXT,
	scanning_organization_id TEXT,
	allocated_project_id TEXT,
	lending_session_id TEXT,
	actor_id TEXT,
	scanned_by_role TEXT NOT NULL CHECK (scanned_by_role IN ('STAFF', 'ORG_REPRESENTATIVE')),
	match_result TEXT NOT NULL CHECK (match_result IN ('MATCHED', 'NOT_IN_MEMBER_LIST', 'ALREADY_SCANNED', 'ALLOCATION_NOT_CONFIRMED', 'ITEM_NOT_FOUND', 'PLACEMENT_CONFIRMED', 'PLACEMENT_MISMATCH', 'RETURN_RECORDED', 'SCAN_RECORDED', 'SYNC_CONFLICT', 'LABEL_MATCHED', 'LABEL_MISSING', 'LABEL_DUPLICATE', 'LABEL_WRONG_LOCATION')),
	source TEXT NOT NULL CHECK (source IN ('individual-scan', 'lending-session', 'placement-confirmation', 'return-operation', 'label-attachment')),
	device_id TEXT,
	occurred_at TEXT NOT NULL,
	created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
	FOREIGN KEY (item_id) REFERENCES Items(id),
	FOREIGN KEY (scanning_organization_id) REFERENCES Organizations(id),
	FOREIGN KEY (allocated_project_id) REFERENCES Projects(id),
	FOREIGN KEY (lending_session_id) REFERENCES LendingSession(id)
);

CREATE INDEX idx_scan_log_item ON ScanLog(item_id);
CREATE INDEX idx_scan_log_organization ON ScanLog(scanning_organization_id);
CREATE INDEX idx_scan_log_session ON ScanLog(lending_session_id);
CREATE INDEX idx_scan_log_created_at ON ScanLog(created_at);
CREATE INDEX idx_scan_log_occurred_at ON ScanLog(occurred_at);
CREATE INDEX idx_scan_log_match_result ON ScanLog(match_result);

貸出時スキャンでは、貸出セッションから解決した対象団体、備品、割当先企画、貸出セッション、認証上の操作元、実際のスキャン担当、日時、端末識別子、一致結果を保存します。actor_idはログイン済みFZ-N1のJWTのsub（貸出セッションを開始した学祭委員）であり、scanned_by_roleが実際の操作担当を示す。したがって、参加団体担当者が端末を操作した場合も、actor_idに参加団体のIDを入れず、scanned_by_role=ORG_REPRESENTATIVEとする。

持出後の二重確認スキャンでは、学祭委員（備品PJ）がFZ-N1を持って各テント等を巡回し、学祭委員またはその場に居合わせた参加団体の担当者が対象備品を一つずつ読み取ります。これをsource=placement-confirmationとして貸出時スキャンと区別し、match_result=PLACEMENT_CONFIRMEDまたはPLACEMENT_MISMATCH、配置完了の確認結果、対象備品、端末識別子を追跡できるようにします。実際のスキャン担当は運用で決め、参加団体担当者に別の認証情報は要求しません。指定外備品のスキャンも保存します。QRに対応する備品が存在しない場合はitem_idをNULLとしてITEM_NOT_FOUNDを記録できます。

match_resultは、通常の割当一致をMATCHED、対象団体の割当外をNOT_IN_MEMBER_LIST、同一操作の再読取をALREADY_SCANNED、確定前割当をALLOCATION_NOT_CONFIRMED、QRに対応する備品なしをITEM_NOT_FOUND、配置確認の成功・不一致をPLACEMENT_CONFIRMED・PLACEMENT_MISMATCH、返却記録をRETURN_RECORDED、照会だけのスキャンをSCAN_RECORDED、同期競合をSYNC_CONFLICT、貼付後確認の一致・不足・重複・場所違いをLABEL_MATCHED・LABEL_MISSING・LABEL_DUPLICATE・LABEL_WRONG_LOCATIONで表す。

返却時のスキャンはD-02〜D-05で確定したパターンに従います。パターンBでは講義棟前到着時と教室収容後の全数スキャンをsource=return-operationとして保存します。パターンAでは2日目夜のスキャン・記録を行わず、講義棟前到着後の逐次スキャンを保存します。

3.10 LabelPrintLog（ラベル発行履歴）

ラベルの発行、再発行、印刷結果を記録します。

CREATE TABLE LabelPrintLog (
	id TEXT PRIMARY KEY,
	item_id TEXT NOT NULL,
	print_sequence INTEGER NOT NULL,
	printed_by TEXT NOT NULL,
	reason TEXT,
	print_status TEXT NOT NULL CHECK (print_status IN ('PENDING', 'PRINTING', 'SUCCEEDED', 'FAILED', 'CANCELLED')),
	bridge_error_code TEXT,
	printed_at TEXT NOT NULL,
	FOREIGN KEY (item_id) REFERENCES Items(id)
);

CREATE INDEX idx_label_print_log_item ON LabelPrintLog(item_id);
CREATE INDEX idx_label_print_log_printed_at ON LabelPrintLog(printed_at);
CREATE UNIQUE INDEX idx_label_print_log_item_sequence
	ON LabelPrintLog(item_id, print_sequence);

print_sequenceは備品ごとの連番とし、初回を1として発行処理を原子的に採番する。印刷失敗でも採番済みの番号は再利用せず、同一備品・同一番号の重複をUNIQUE制約で防ぐ。同一備品の再発行では理由を必須とし、発行番号の重複や印刷失敗を管理者が確認できるようにします。

3.11 LabelAttachmentCheckLog（貼付後確認）

教室・場所単位でラベルを確認した結果を記録します。要件定義の主要エンティティには含まれない補助ログですが、重複、不足、別教室への混入を追跡するために保持します。

CREATE TABLE LabelAttachmentCheckLog (
	id TEXT PRIMARY KEY,
	item_id TEXT NOT NULL,
	location_id TEXT,
	result TEXT NOT NULL CHECK (result IN ('MATCHED', 'MISSING', 'DUPLICATE', 'WRONG_LOCATION')),
	checked_by TEXT NOT NULL,
	device_id TEXT,
	checked_at TEXT NOT NULL,
	FOREIGN KEY (item_id) REFERENCES Items(id),
	FOREIGN KEY (location_id) REFERENCES Locations(id)
);

CREATE INDEX idx_label_attachment_item ON LabelAttachmentCheckLog(item_id);
CREATE INDEX idx_label_attachment_location ON LabelAttachmentCheckLog(location_id);

3.12 OfflineOperation（オフライン操作）

FZ-N1が通信できない間に保存した操作を管理します。

CREATE TABLE OfflineOperation (
	operation_id TEXT PRIMARY KEY,
	device_id TEXT NOT NULL,
	payload TEXT NOT NULL,
	occurred_at TEXT NOT NULL,
	base_revision INTEGER NOT NULL,
	synced_at TEXT,
	conflict_status TEXT NOT NULL DEFAULT 'PENDING' CHECK (conflict_status IN ('PENDING', 'APPLIED', 'CONFLICT', 'REJECTED')),
	conflict_detail TEXT
);

CREATE INDEX idx_offline_operation_device ON OfflineOperation(device_id);
CREATE INDEX idx_offline_operation_status ON OfflineOperation(conflict_status);

操作IDに一意制約を設け、同じ操作を複数回適用しません。payloadにはsource、scanned_by_role、lending_session_id、organization_id、allocated_project_id、match_resultなどの監査情報を含める。競合で適用しなかった操作も削除せず、競合履歴としてSho-Roomから確認できるようにします。base_revisionはオフライン開始時に端末が保持した備品の版番号であり、端末時計による競合判定は行わない。

3.13 LoginNotificationLog（Discordログイン通知）

FZ-N1のNFCログイン成功後に作成するDiscordプライベートスレッドの作成・投稿・削除状況を記録します。

CREATE TABLE LoginNotificationLog (
	login_id TEXT PRIMARY KEY,
	discord_user_id TEXT NOT NULL,
	thread_id TEXT,
	thread_created_at TEXT,
	message_sent_at TEXT,
	delete_at TEXT,
	notification_status TEXT NOT NULL CHECK (notification_status IN ('PENDING', 'SENT', 'FAILED')),
	deletion_status TEXT NOT NULL CHECK (deletion_status IN ('PENDING', 'DELETED', 'FAILED', 'NOT_REQUIRED')),
	retry_count INTEGER NOT NULL DEFAULT 0,
	last_error_code TEXT,
	created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
);

CREATE INDEX idx_login_notification_delete_at
	ON LoginNotificationLog(delete_at, deletion_status);
CREATE INDEX idx_login_notification_discord_user
	ON LoginNotificationLog(discord_user_id);

メッセージ送信成功時のdelete_atは送信日時の15分後、投稿失敗時はスレッド作成日時の15分後とします。その時刻にログイン通知スレッドを削除し、削除失敗は再試行回数とエラーコードを記録します。通知本文やスレッド名に学籍番号、カード情報、JWTを保存しません。

3.14 LendingSession（団体貸出セッション）

FZ-N1のログイン済み状態下で行う、団体単位の貸出セッションを管理します。これは認証セッションやCookieセッションではなく、学祭委員立会いのもとで団体担当者へFZ-N1を手渡してから返却されるまでの業務記録です。開始時に当年度の確定済み割当リストを端末へ事前ダウンロードし、セッション中のスキャンはオフラインでもローカル照合します。各団体の搬出後に行う持出後の二重確認スキャンは、貸出セッション終了後の別の現場操作としてScanLogおよび必要なItemHistoryに記録し、貸出セッションを再開しません。

CREATE TABLE LendingSession (
	id TEXT PRIMARY KEY,
	organization_id TEXT NOT NULL,
	device_id TEXT NOT NULL,
	started_by TEXT NOT NULL,
	started_at TEXT NOT NULL,
	expires_at TEXT NOT NULL,
	ended_at TEXT,
	ended_by TEXT,
	end_reason TEXT CHECK (end_reason IN ('DEVICE_RETURNED', 'STAFF_TERMINATED', 'SESSION_TIMEOUT', 'YEAR_RESET')),
	created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
	FOREIGN KEY (organization_id) REFERENCES Organizations(id)
);

CREATE INDEX idx_lending_session_organization
	ON LendingSession(organization_id);
CREATE INDEX idx_lending_session_started_at
	ON LendingSession(started_at);
CREATE INDEX idx_lending_session_active
	ON LendingSession(ended_at);
CREATE UNIQUE INDEX idx_lending_session_active_device
	ON LendingSession(device_id)
	WHERE ended_at IS NULL;
CREATE UNIQUE INDEX idx_lending_session_active_organization
	ON LendingSession(organization_id)
	WHERE ended_at IS NULL;

started_byとended_byには学祭委員のDiscord User IDを記録します。expires_atは開始時刻から設定値（初期値120分）で算出し、期限を超えた未終了セッションはSESSION_TIMEOUTで自動終了します。担当者から端末が返却された場合のend_reasonはDEVICE_RETURNED、学祭委員が明示的に終了した場合はSTAFF_TERMINATEDとします。年度更新で進行中のセッションを終了扱いにする場合はYEAR_RESETを記録します。部分ユニークインデックスと開始APIの事前チェックにより、同じ端末または同じ団体の未終了セッションを複数作成しません。団体QRコードの発行・配布情報や参加団体の認証情報は保存しません。

3.15 AuthorityChangeLog（権限変更監査ログ）

Discordロールから計算された権限の変更を監査します。権限そのものの正本はDiscordロールであり、本テーブルを直接編集して権限を変更しません。

CREATE TABLE AuthorityChangeLog (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	target_discord_id TEXT NOT NULL,
	target_student_id TEXT,
	target_name TEXT,
	old_authority INTEGER NOT NULL,
	new_authority INTEGER NOT NULL,
	actor_id TEXT NOT NULL DEFAULT 'SYSTEM',
	reason TEXT NOT NULL DEFAULT 'AUTHORITY_RECALCULATED_ON_LOGIN',
	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);

対象者のDiscord ID、学籍番号、氏名、変更前後の権限を履歴として保持し、既存実装との互換性のためtarget_student_id、target_nameを含む定義を維持します。本システムはDiscord上でロールを変更した実際の利用者や理由を取得できないため、ログインまたは権限再計算時に直近のnew_authorityと現在のStaffs.Authorityが異なることを検出した場合に、actor_id=SYSTEM、reason=AUTHORITY_RECALCULATED_ON_LOGINとして自動記録する。実際のDiscord操作担当者の監査はDiscord側の監査ログで行う。

3.16 SyncLog（外部エクスポート・同期実行ログ）

既存API互換のためSyncLogを残します。Google Spreadsheetとの同期ログとしては扱わず、FZ-N1の同期や大学環境向け全データエクスポートの実行結果を記録します。

CREATE TABLE SyncLog (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	actor_id TEXT,
	direction TEXT NOT NULL CHECK (direction IN ('INBOUND', 'OUTBOUND')),
	status TEXT NOT NULL CHECK (status IN ('PENDING', 'RUNNING', 'SUCCEEDED', 'PARTIAL', 'FAILED')),
	operation_id TEXT,
	timestamp INTEGER NOT NULL,
	created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
);

CREATE INDEX idx_sync_log_timestamp ON SyncLog(timestamp);
CREATE INDEX idx_sync_log_status ON SyncLog(status);

directionはFZ-N1からの同期や申請値の取込をINBOUND、大学環境へのエクスポートをOUTBOUNDとする。statusはPENDING、RUNNING、SUCCEEDED、PARTIAL、FAILEDのいずれかとする。

4. データ整合性・保持ルール

備品、割当、変更履歴、スキャン履歴のIDは衝突しない値とする
備品を論理削除しても、過去の割当、変更履歴、スキャン履歴を参照できるようにする
場所、団体、企画、備品種別は、使用中の参照がある場合に物理削除しない
備品の割当は企画単位で管理し、同一団体の別企画と混同しない
Itemsは年度を持たない再利用資産とし、年度による絞り込みはAllocationsからProjects.festival_yearを辿って行う
アプリケーションはItems.revision/Items.updated_atをUPDATE文で直接指定しない。両者はtrigger_items_updated_atが更新ごとに一括管理する正本であり、直接の書き込みはtrigger_items_guard_revisionがDB側で拒否する（#754）。ただしrevision = revision + 1をupdated_atに触れず単独で書いた場合はDB側で検知できず、updated_atが古いまま据え置かれる既知の残存ギャップがある（revisionの単調増加という楽観ロック本来の目的は保たれるため許容。#793レビューで指摘）
DATETIME('now', 'localtime')は本DB上ではUTCを返す（#760）。 Cloudflare D1のタイムゾーンはUTC固定のため、localtime修飾子は実質no-opで、created_at/updated_at等はJSTではなくZサフィックス無しのUTC文字列になる。名前に反してJSTへ変換されるわけではない点に注意し、画面表示でJSTが必要な場合はクライアント側またはAPI層で変換する。記法自体はpackages/shared/migrationsの既存migrationと揃えるため変更しない
Locations/Organizations/ItemTypes/Projects/EquipmentRequests/Allocationsのupdated_at更新トリガーは、WHEN OLD.updated_at IS NEW.updated_atという条件のため、アプリケーションがupdated_atを直接指定すると素通しする（過去日時・未来日時いずれも通る）。トリガー内での強制上書きへ統一するかどうかは#800で判断する。それまではアプリケーションがupdated_atを直接指定しない運用で担保する
Sho-Roomの認証・セッション状態は共通認証基盤（login-api等）が管理し、本データベースに認証用セッションテーブルを設けない
LendingSessionは対象団体、開始・終了日時、タイムアウト日時、開始・終了操作元、端末識別子を保持し、認証セッションやCookieセッションとして利用しない。同じ端末または同じ団体の未終了セッションを部分ユニークインデックスで1件に制限する
オフライン操作IDに一意制約を設け、同一操作を複数回適用しない
競合で適用されなかったオフライン操作も競合履歴として保持する
オフライン同期の競合判定は端末が保持したbase_revisionとサーバー側Items.revisionで行い、occurred_atや端末時計を優先順位の根拠にしない
すべての備品変更で、変更前後の値、実行者または貸出セッション、日時、操作元、端末識別子、理由を記録する
指定外を含むすべての貸出時スキャンをScanLogへ記録する
ScanLogには認証上の操作元actor_idとは別に、実際のスキャン担当をscanned_by_role（STAFF / ORG_REPRESENTATIVE）として記録する
ScanLog.sourceはindividual-scan、lending-session、placement-confirmation、return-operation、label-attachmentのいずれかとし、二重確認スキャンはplacement-confirmationで同期する
監査ログ、通知ログ、ラベル発行・貼付確認ログは通常操作や年度更新で更新・削除しない
年度更新ではAllocationsの有効割当をCANCELLEDへ変更し、削除せずにYEAR_RESETの理由と取消操作情報を保持する
Discordログイン通知スレッドはメッセージ送信から15分後に削除し、削除結果と再試行情報を保持する
バックアップとリストアはCloudflareの機能を利用する。大学環境cron向けエクスポートはバックアップの代替とはしない

4.1 返却運用と配置確認の記録ルール（D-02〜D-05）

テント等のレンタル業者（スパイス）との回収タイミング交渉結果により、返却運用は次の2パターンのいずれかに確定します。D-02〜D-05は2026/09/11まで回答を待ち、期限までに回答がない場合はパターンBを実装・当日運用の既定とする。参加団体による返却時のセルフスキャンは行いません。

項目	パターンA：スパイスが回収を遅らせてくれる場合	パターンB：スパイスが例年通り回収する場合
D-02（返却成立条件）	片付けの日（2026/10/12）に講義棟前へ返却された時点を返却成立とする	昨年通り、講義棟前に備品が返却された時点を返却成立とする。ただし、この運用が学生課等から承認されるかは未確定
D-03（2日目夜の記録）	記録しない。2日目夜はスキャン・記録を行わない	講義棟前到着時に全数スキャンを行う。到着時点で備品への責任が備品課に移るため
D-04（中間集積場所を現在地として記録するか）	記録しない。講義棟前を`Items.current_location_id`に設定しない	混乱防止のため、講義棟前を`Items.current_location_id`に設定する
D-05（足拭き・教室収容）	備品課・学祭委員が講義棟前へ到着次第、逐次スキャン・足拭き・教室収容を行う。可能であれば、備品を持ってきた各団体自身に足拭き・教室搬入まで行ってもらう運用を志向する	備品課・学祭委員がテント等の片付け後に足拭きを行って教室へ収容し、その後に全数スキャンを行う

返却成立の時点とItems.current_location_idの更新は別に扱います。パターンAではD-03の夜間記録を作成せず、D-04の講義棟前も現在地として記録しません。パターンBではD-03の講義棟前到着時スキャンとD-05の教室収容後スキャンをScanLogへ保存し、各時点の実際の場所をItems.current_location_idへ反映します。D-02〜D-05の最終パターンが確定したら、関連する実装・テスト・運用手順へ反映します。

5. System DBとの連携

備品管理システムはsoshosai-system-dbのStaffsを参照します。学祭委員名簿の登録・管理は本システムの対象外で、外部システムから供給される前提です。

参照する主な情報は次のとおりです。以下はsoshosai-system-dbの物理列名です。

DiscordId: Discord User ID（主キー）
StudentId: NFCから読み取った学籍番号との照合に使用する学籍番号
NfcIdm: FeliCa IDm
Authority: Discordロールから計算された権限ビット

JWTペイロードではDiscord User IDをsub、権限ビットをpermissionsとして表現します。APIのstudent_idなどのフィールド名やJWTのフィールド名は、StaffsのDB列名（StudentId、Authorityなど）とは区別します。既存認証基盤がFeliCaのIDmを保持する場合も、その登録・再登録は認証基盤の責務として扱い、備品DBからスタッフ名簿を直接管理しません。

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

1. 概要​

2. ER図​

3. テーブル定義​

3.1 Locations（場所マスター）​

3.2 Organizations（参加団体マスター）​

3.3 Projects（企画マスター）​

3.4 ItemTypes（備品種別マスター）​

3.5 EquipmentRequests（備品申請数）​

3.6 Items（備品マスター）​

3.7 Allocations（備品割当）​

3.8 ItemHistory（備品変更監査ログ / 要件上のItemChangeLog）​

3.9 ScanLog（スキャン履歴）​

3.10 LabelPrintLog（ラベル発行履歴）​

3.11 LabelAttachmentCheckLog（貼付後確認）​

3.12 OfflineOperation（オフライン操作）​

3.13 LoginNotificationLog（Discordログイン通知）​

3.14 LendingSession（団体貸出セッション）​

3.15 AuthorityChangeLog（権限変更監査ログ）​

3.16 SyncLog（外部エクスポート・同期実行ログ）​

4. データ整合性・保持ルール​

4.1 返却運用と配置確認の記録ルール（D-02〜D-05）​

5. System DBとの連携​