DB 設計 — mikyun_cloud
// DATABASE — 最終更新: 2026-05-14 / maguro Docker MariaDB (compose-db-1) に統合済
運用中 Nextcloud と同じ MariaDB スタックに統合 Docker 内部のみ 外部からの直接接続なし
1. DB 基本情報 (2026-05-14)
| 項目 | 内容 |
|---|---|
| DB名 | mikyun_cloud |
| エンジン | MariaDB 11 |
| 配置 | maguro Docker compose-db-1 (Nextcloud と同居) |
| ネットワーク | Docker 内部ネット (compose-default) のみ。 ホスト側 3306 露出なし |
| ユーザー | mikyun (権限: mikyun_cloud DB のみ) |
| 接続元 | mikyun-portal コンテナ から DSN db:3306 で接続 |
| バックアップ | maguro /srv/storage/SM-storage/backup/mikyun_cloud/*.sql.gz (14日保持、 daily 02:30) |
歴史: 旧 R640 ホスト MariaDB (127.0.0.1:3306) → Phase A (2026-05-13) で maguro ホスト MariaDB に統合 → Phase 2 (2026-05-14) で maguro Docker MariaDB へ最終統合。 ホスト直 MariaDB は両機とも
disable 済。 SQLite 時代 (mikyun.db: customers/devices/wg_peers) は完全廃止。
2. テーブル一覧
| 種別 | 名前 | 用途 |
|---|---|---|
| TABLE | contracts | 契約本体 |
| TABLE | customer_devices | 顧客端末 / WireGuard ピア |
| TABLE | admin_logs | 管理操作ログ |
| TABLE | contract_notes | 顧客メモ / 通話記録 / 警告 |
| TABLE | point_transactions | ポイント取引履歴 (2026-04-10 追加) |
| TABLE | traffic_daily | 日次トラフィック集計 |
| VIEW | v_parent_conflicts | 親端末重複監査 |
3. contracts (契約テーブル)
契約本体。 顧客ID、 サブネット、 容量、 上限、 状態、 課金情報を持つ。
| カラム | 型 | 説明 |
|---|---|---|
id | bigint PK auto | 内部ID |
contract_number | int unsigned UNI | 連番 (表示用) |
contract_id | varchar(32) UNI | 契約ID (例: 0001, M010) 永久不変 |
vpn_subnet | varchar(32) | 割当 VPN サブネット (主に 10.200.0.0/16 = wg-clients) |
next_child_ip | varchar(32) | 次割当IP候補 (現状は DB スキャン採番のため未使用) |
customer_name | varchar(128) | 顧客名 |
customer_email | varchar(255) MUL | メール |
customer_phone | varchar(64) | 電話 |
plan_name | varchar(64) | プラン名 (例: standard) |
storage_quota_gb | int DEFAULT 200 | 契約総容量 (GB) |
max_devices | int DEFAULT 3 | 最大端末数 |
max_files | int DEFAULT 10000 | 最大ファイル数 |
bandwidth_profile | varchar(64) | 帯域プロファイル (例: standard) |
contract_status | varchar(16) MUL | active / paused / terminated / deleted |
identity_passphrase | text | 顧客ポータル ログイン用 (改善TODO: Argon2) |
nc_password | varchar(255) | Nextcloud 親アカウントPW (改善TODO: Fernet) |
nc_quota_gb | int DEFAULT 200 | NC割当容量 (GB) |
opt_video_call | tinyint DEFAULT 0 | ビデオ通話オプション (現在は常に0、 後述) |
guest_video_enabled | enum(‘yes’,’no’) DEFAULT ‘no’ | 互換性のため残置 (常に no、 2026-04-05 ロールバック) |
point_balance | int DEFAULT 0 | ポイント残高 (1pt=1円) |
monthly_amount | int DEFAULT 1980 | 月額料金 (自動計算) |
next_payment_at | datetime NULL | 次回支払日 |
billing_warning_at | datetime NULL | 残高不足警告日 |
billing_suspended_at | datetime NULL | アクセス停止日 |
delivery_method | varchar(32) | 連絡方法 (Telegram / Email / SMS 等) |
created_at / updated_at | datetime | 作成/更新 |
4. customer_devices (端末テーブル)
契約配下の端末管理テーブル。 WireGuard 鍵・IP・状態・統計を持つ中核テーブル。
| カラム | 型 | 説明 |
|---|---|---|
id | bigint PK auto | レコードID |
contract_id | varchar(32) MUL | 所属契約ID |
device_id | varchar(64) UNI | 端末識別ID (例: 0001-DEV-001) |
wg_iface | varchar(32) | WGインターフェース名 (wg-clients / wg-customers) |
wg_ip | varchar(64) UNI | 割当WG IP (10.200.0.x 等) |
public_key | text UNI | クライアント公開鍵 |
private_key | text | クライアント秘密鍵 (DB保存、 教訓: 平文格納の歴史的経緯あり) |
preshared_key | text | WG事前共有鍵 |
device_role | varchar(16) | parent / child |
is_parent | tinyint DEFAULT 0 | 親端末フラグ |
status | varchar(16) MUL | issued / active / paused / revoked / expired / deleted |
device_name | varchar(128) | 端末名 |
platform | varchar(64) | iPhone / Android / Windows 等 |
app_version | varchar(64) | アプリバージョン |
issued_at | datetime | QR発行日時 |
qr_expires_at | datetime | QR失効 (+24h) |
first_handshake_at / last_handshake_at | datetime MUL | WG接続日時 (cron収集) |
activated_at | datetime | 有効化日時 |
parent_changed_at / parent_changed_by / parent_change_reason | — | 親変更履歴 |
paused_at / pause_reason | — | 一時停止記録 |
rx_bytes_total / tx_bytes_total | bigint | 累計受信/送信 |
rx_bytes_24h / tx_bytes_24h | bigint | 24h 受信/送信 |
rx_bytes_month / tx_bytes_month | bigint | 月次 受信/送信 |
nc_used_bytes | bigint DEFAULT 0 | NC使用量 (1h cron で collect_file_count.py 集計) |
nc_used_updated_at | datetime | 使用量最終更新 |
file_count / file_count_alert | int / tinyint | ファイル数 + max_files 超過警告 |
storage_quota_gb | int DEFAULT 0 | 端末個別割当容量 |
nc_user / nc_password / nc_quota_gb / nc_enabled | — | Nextcloud アカウント情報 |
deleted_at | datetime NULL | 論理削除日時 |
created_at / updated_at | datetime | 作成/更新 |
5. admin_logs (管理操作ログ)
| カラム | 型 | 説明 |
|---|---|---|
id | bigint PK auto | 内部ID |
action_type | varchar(64) | 操作種別 |
target_type | varchar(64) | 対象種別 (contract / device) |
target_id | varchar(64) | 対象ID |
message | text | 操作内容テキスト |
created_at | datetime auto | 記録日時 |
6. point_transactions (ポイント取引履歴 2026-04-10〜)
| カラム | 型 | 説明 |
|---|---|---|
id | bigint PK auto | ID |
contract_id | varchar(32) | 契約ID |
type | enum(‘charge’,’monthly_deduct’,’refund’,’manual_adjust’) | 取引種別 |
amount | int | 金額 (正=加算、 負=消費) |
balance_after | int | 取引後残高 |
stripe_payment_intent_id | varchar(128) NULL | Stripe PI ID |
note | text NULL | 備考 |
created_at | datetime | 作成日時 |
7. traffic_daily (日次集計)
| カラム | 型 | 説明 |
|---|---|---|
day | date | 集計日 |
contract_id | varchar(32) | 契約ID |
device_id | varchar(64) | 端末ID |
rx_bytes / tx_bytes | bigint | 受信/送信 |
集計元: wg_traffic_collector.py (旧 SSH経由 → 2026-04-10 ローカル wg show wg-clients dump に修正済)
8. v_parent_conflicts (VIEW)
1契約に親端末が複数存在する異常検出ビュー。 cron で定期確認 + Telegram 通知。
SELECT contract_id, COUNT(*) AS parent_count FROM customer_devices WHERE is_parent = 1 AND deleted_at IS NULL GROUP BY contract_id HAVING COUNT(*) > 1
バックアップ / リストア
| 項目 | 内容 |
|---|---|
| 保存先 | maguro /srv/storage/SM-storage/backup/mikyun_cloud/ |
| 頻度 | daily 02:30 (systemd timer) |
| 保持期間 | 14日 |
| 形式 | YYYYMMDD_HHMM.sql.gz (gzip mysqldump) |
| 監視 | cert-watch / db-backup-watch で失敗 + サイズ異常を Telegram 通知 |
# dump docker compose -f /opt/nextcloud/compose/compose.yml exec db \ mysqldump --single-transaction -u root -p mikyun_cloud | gzip > backup.sql.gz # restore zcat backup.sql.gz | docker compose -f /opt/nextcloud/compose/compose.yml exec -T db \ mysql -u root -p mikyun_cloud
歴史的経緯: 旧 SQLite (
mikyun.db: customers/devices/wg_peers) と MySQL が並存していた時代があり、 wg_peers テーブルに WG秘密鍵が平文保存されていた。 現在の customer_devices.private_key も同じ形式で残るが、 これは「再発行コスト > 漏洩リスク」で意図的選択。 DB アクセスは Docker 内部ネット限定、 ホスト経由でしか root にならない設計で防御。