Appearance
データベース・スキーマ仕様書
1. 概要
本プロジェクトのデータベースは、エッジネイティブおよびオフライン対応の観点から Cloudflare D1 (SQLite) を採用しています。 型安全なクエリ発行と移行管理には Prisma ORM を使用しています。
2. データベース・アーキテクチャとER関係
本システムはマルチテナント構造を採用しており、Facility (施設) および Organization (組織) を起点とする厳格なデータ分離が行われます。
3. 主要エンティティ定義
3.1 組織・施設 (Organization & Facility)
Organization
法人や自治体などの統括組織。
| カラム名 | 型 | 制約 | 説明 |
|---|---|---|---|
id | String | PK (UUID) | 組織識別ID |
name | String | Not Null | 組織名称 |
code | String | Unique, Not Null | 法人コード（制限英数3文字、大文字、I/L/O/0/1を除外） |
created_at | DateTime | Default (now) | 作成日時 |
updated_at | DateTime | 更新日時 |
Facility
各放課後児童クラブ（施設）単位の物理的な単位。
| カラム名 | 型 | 制約 | 説明 |
|---|---|---|---|
id | Int | PK (AutoInc) | 施設ID |
organization_id | String | FK (Organization) | 紐付く組織ID |
name | String | Not Null | 施設名称 |
code | String | Nullable, Unique | 施設コード（設定済みの場合はシステム全体で一意な制限英数2文字、大文字、I/L/O/0/1を除外） |
capacity | Int | Nullable | 定員 |
phone_number | String | Nullable | 連絡先電話番号 |
use_nfc | Boolean | Default (true) | Kiosk での NFC 打刻の使用有無 |
allow_manual_nfc | Boolean | Default (true) | NFC の手動登録を許可するか |
lunch_threshold | String | Default ("12:20") | 給食・昼食の判定境界時刻 |
dinner_threshold | String | Default ("19:00") | 夕食の判定境界時刻 |
3.2 ユーザー・アカウント (User & Guardian)
User
施設職員（管理者、一般職員、パートタイマーなど）。
| カラム名 | 型 | 制約 | 説明 |
|---|---|---|---|
id | Int | PK (AutoInc) | ユーザーID |
user_uid | String | Unique | API用公開UID |
facility_id | Int | FK (Facility) | 所属施設ID |
name | String | Not Null | 職員氏名 |
role | String | Not Null | 'admin' (施設長), 'staff' (一般職員), 'assistant' (補助員) |
login_id | String | Not Null, 法人単位で複合Unique | S + 法人コード3 + 施設コード2 + 制限英数4の職員ログインID（10文字）。DB制約はorganization_id + login_idで、旧形式の互換IDも存在し得る |
staff_id | String | Unique, Nullable | 原則としてlogin_idと同一の職員公開ID |
password_hash | String | Not Null | ハッシュ化されたパスワード。初期パスワードは8文字かつ大文字・小文字・数字・記号を各1文字以上含む |
nfc_uid | String | Unique, Nullable | 職員打刻用NFC UID |
kiosk_pin | String | Nullable | Kiosk打刻用の簡易PINコード |
hourly_rate | Int | Default (0) | 時給（労働コスト算出用） |
Guardian
保護者。
| カラム名 | 型 | 制約 | 説明 |
|---|---|---|---|
id | Int | PK (AutoInc) | 保護者ID |
guardian_uid | String | Unique | API用公開UID |
login_id | String | Not Null, 法人単位で複合Unique | P + 法人コード3 + 施設コード2 + 制限英数4の保護者ログインID（10文字）。DB制約はorganization_id + login_id |
guardian_id | String | Unique, Nullable | 原則としてlogin_idと同一の保護者公開ID |
password_hash | String | Not Null | ハッシュ化されたパスワード |
name | String | Not Null | 保護者氏名 |
bank_customer_number | String | Nullable | 口座振替用顧客番号 |
emergency_contact | String | Nullable | 緊急連絡先電話番号 |
3.3 児童 (Child)
Child
施設を利用する児童。
| カラム名 | 型 | 制約 | 説明 |
|---|---|---|---|
id | Int | PK (AutoInc) | 児童ID |
child_id | String | Unique, Nullable | C + 法人コード3 + 施設コード2 + 制限英数4の児童公開ID（10文字） |
facility_id | Int | FK (Facility) | 所属施設ID |
guardian_id | Int | FK (Guardian) | 紐付く保護者ID |
first_name | String | Not Null | 児童（名） |
last_name | String | Not Null | 児童（姓） |
birth_date | DateTime | Nullable | 生年月日 |
is_long_term_contract | Boolean | Default (false) | 常時利用契約（月額）かどうか |
is_exemption | Boolean | Default (false) | 減免措置対象かどうか |
birth_order | String | Nullable | 多子減免用世帯内順位 ("FIRST", "SECOND", "THIRD_PLUS") |
support_unit | String | Default ("STANDARD") | 支援区分 ("STANDARD", "SUPPORT_1", "SUPPORT_2") |
nfc_uid | String | Unique, Nullable | 児童登下校打刻用NFC UID |
status | String | Default ("active") | 在籍状態 ('active', 'inactive', 'withdrawn') |
3.4 運用・記録 (Attendance & Billing)
AttendanceRecord
児童の入退室（登下校・保護者お迎え）の実績レコード。
| カラム名 | 型 | 制約 | 説明 |
|---|---|---|---|
id | Int | PK (AutoInc) | レコードID |
child_id | Int | FK (Child) | 児童ID |
facility_id | Int | FK (Facility) | 施設ID |
target_date | DateTime | Not Null | 対象日付 |
entry_time | DateTime | Nullable | 登室時刻 |
exit_time | DateTime | Nullable | 退室時刻 |
lunch_used | Boolean | Default (false) | 昼食・給食提供フラグ |
dinner_used | Boolean | Default (false) | 夕食提供フラグ |
status | String | Default ("present") | 出欠ステータス ('present', 'late', 'absent', etc.) |
Billing
各児童の月ごとの請求情報。
| カラム名 | 型 | 制約 | 説明 |
|---|---|---|---|
id | Int | PK (AutoInc) | 請求ID |
facility_id | Int | FK (Facility) | 施設ID |
child_id | Int | FK (Child) | 児童ID |
target_month | String | Not Null | 対象月 ("YYYY-MM") |
total_amount | Int | Not Null | 合計請求額 |
base_amount | Int | Default (0) | 基本保育料 |
extension_fee | Int | Default (0) | 延長保育料 |
snack_fee | Int | Default (0) | おやつ代 |
meal_fee | Int | Default (0) | 食事代 |
discount_amount | Int | Default (0) | 多子減免等による割引額 |
status | String | Default ("draft") | 請求状況 ('draft', 'invoiced', 'paid', 'overdue') |
3.5 監査・ログ (AuditLog)
施設運営データ（特に手動打刻修正や請求変更）の透明性を担保するための変更履歴。
| カラム名 | 型 | 制約 | 説明 |
|---|---|---|---|
id | Int | PK (AutoInc) | ログID |
user_id | Int | FK (User) | 操作実行した職員ID |
facility_id | Int | FK (Facility) | 施設ID |
action | String | Not Null | アクション内容 (例: 'export_csv', 'manual_edit_time') |
target_table | String | Nullable | 対象テーブル名 |
target_id | Int | Nullable | 対象レコードID |
old_value | String | Nullable | 変更前値 (JSON形式) |
new_value | String | Nullable | 変更後値 (JSON形式) |
created_at | DateTime | Default (now) | 記録日時 |
previous_hash | String | Nullable | 一つ前のログ of current_hash（改ざん検知チェーン用） |
current_hash | String | Nullable | 本レコードの完全性ハッシュ値 |
3.6 補助金関連 (Subsidy)
自治体別の補助金計算で用いられる計算用単価のマスタデータ。
SubsidyRate
| カラム名 | 型 | 制約 | 説明 |
|---|---|---|---|
id | Int | PK (AutoInc) | レコードID |
key | String | Unique, Not Null | 単価設定キー（例: 'kimitsu.base.lt20'） |
name | String | Not Null | 設定名（例: '君津市 基本額 lt20'） |
amount | Float | Not Null | 金額または加算単価 |
description | String | Nullable | 設定に関する説明 |
created_at | DateTime | Default (now) | 作成日時 |
updated_at | DateTime | 更新日時 |
4. セキュリティ & データ完全性保証
本プロジェクトのデータベース設計では、児童・保護者の個人情報を安全に保護し、かつ手動時間修正などの履歴改ざんを防止するために以下の設計手法を採用しています。
4.1 PBKDF2によるパスワードハッシュ化
パスワードの保護には、辞書攻撃やGPUを用いた高速ブルートフォース攻撃から防衛するため、高い計算コストを持つ PBKDF2 (Password-Based Key Derivation Function 2) を採用しています。
- 仕様:
- ハッシュアルゴリズム: SHA-512
- ソルトサイズ: 16バイト (暗号論的疑似乱数)
- クライアント側ストレッチ: PBKDF2-HMAC-SHA512、250,000回。ソルトはログインIDの小文字化値。
- API側の現行実装: PBKDF2-HMAC-SHA512、1,000回。ランダム16バイトソルト、64バイト出力。
- 出力サイズ: 64バイト (512ビット)
- 形式:
storedHashは$v2$[Rounds]$[Salt_Hex]$[Hash_Hex]のフォーマットで保存されます。- 例:
$v2$1000$a1f9e2...$9f8b7a... - 600,000回以上というセキュリティ方針は目標値であり、現行API実装は未達である。本番リリース前にAPI側の反復回数を引き上げて性能検証するか、方針を正式に改訂して承認する必要がある。
- デモシード最適化:
- 大規模なデモデータ投入時（4,500件以上）のハッシュ化によるタイムアウトを防ぐため、シードスクリプトは動的計算を行わず
$demo$loginIdのダミー形式を挿入します。API 認証レイヤーがこのプレフィックスを検知し、初回ログイン成功時に正しい$v2$ハッシュへ自動で再ハッシュ・保存します。
- 大規模なデモデータ投入時（4,500件以上）のハッシュ化によるタイムアウトを防ぐため、シードスクリプトは動的計算を行わず
- オンザフライ・リハッシュ (NeedsRehash):
- ログイン時、レガシーな bcrypt や旧 v1 フォーマット、または上記の
$demo$プレフィックスを検知した場合、ログイン成功と同時に最新フォーマットで自動的にリハッシュ（上書き保存）されます。
- ログイン時、レガシーな bcrypt や旧 v1 フォーマット、または上記の
4.2 AuditLog における暗号化ハッシュチェーンによる改ざん検知
特に施設運営において「手動による打刻時間の不正修正」や「請求金額の書き換え」といった改ざん行為は、行政の補助金監査時に重大な指摘事項となります。これらを完全に防ぐため、AuditLog テーブルには暗号的な完全性チェーン（ブロックチェーンと同様の構造）を実装しています。
ハッシュチェーンの仕組み
- 新しい監査ログを記録する際、直近に記録されたログレコードの
current_hashを取得し、それをprevious_hashとして使用します。（最初のレコードはgenesisを使用） - 今回のログに関連する情報（操作した職員ID、アクション、対象テーブル、対象レコードID、変更前の値、変更後の値）および
previous_hashを結合した文字列を作成します。content = previous_hash|userId|action|targetTable|targetId|oldValueStr|newValueStr
- この文字列の SHA-256 暗号ハッシュ値を算出し、
current_hashとしてログレコードに記録します。
改ざんの検出
もしデータベース管理者が直接 SQLite ファイルを操作して Log #2 のデータを書き換えた場合、Log #2 のデータから再計算したハッシュ値は B ではなくなり、さらに後続の Log #3 が保持する previous_hash: B と不整合が発生します。これにより、監査プログラムを実行した際にデータの書き換えがミリ秒単位で即座に検出されます。
5. Prisma ORM によるマイグレーションワークフロー
スキーマの変更およびデータベースへの適用は、Prisma を用いて厳格に管理されます。
5.1 開発環境でのスキーマ適用
スキーマファイル (prisma/schema.prisma) を編集した後、以下のコマンドを実行してマイグレーションファイルの作成とローカルデータベース (SQLite) への適用を行います。
bash
cd app/api
npx prisma migrate dev --name <migration_name>5.2 Cloudflare D1 へのマイグレーション適用 (本番)
本番環境 (D1) へのマイグレーション適用は、Wrangler CLI を用いて行います。
bash
cd app/api
# ローカルで作成された SQL マイグレーションファイルを適用
npx wrangler d1 migrations apply houkago_link_d1 --remote