Skip to main content

🗄️ D1 データベース設計 - World-Class Database Architecture

完璧なデータベース設計
Edge Computing最適化 + Multi-Brand対応 + 高速アクセスを実現するD1データベース

🤔 D1とは？（初心者向け解説）​

D1は「Cloudflare D1」の略で、世界中に分散されたSQLiteベースのエッジデータベースです。

📚 身近な例で理解しよう​

D1を「世界中にある図書館ネットワーク」に例えると：

🏢 東京の図書館    🏢 ニューヨークの図書館    🏢 ロンドンの図書館
     ↓                    ↓                    ↓
📖 同じ本が自動同期   📖 同じ本が自動同期    📖 同じ本が自動同期
     ↓                    ↓                    ↓  
👤 日本のお客様      👤 アメリカのお客様    👤 ヨーロッパのお客様

D1の特徴:

  • エッジ配置: 世界中にデータベースのコピーがある
  • 自動同期: 更新情報が全世界に自動で反映される
  • 超高速: 一番近いデータベースからデータを取得
  • SQLite互換: 使いやすいSQL言語でデータ操作

🔐 重要：個人情報の取り扱い​

⚠️ プライバシー重視設計
D1には個人情報（PII）は一切保存しません。

  • 個人情報: Shopify（全ての個人情報を集約）
  • 認証のみ: Firebase（認証機能のみ）
  • ビジネスデータ: D1（高速エッジDB）
  • 連携: Firebase UID → Shopify Customer IDで安全に連携

🏭 唯一の例外：工場アプリ
工場アプリでのみ、配送情報と注文者情報が必要な場合に限り、
Shopify APIより一時的に取得（保存はしない）

🏗️ データベース全体設計​

🌟 Multi-Brand Database Architecture​

graph TB
    subgraph "Global Edge Network"
        TOKYO[🌸 Tokyo D1<br/>Asia Pacific]
        NYC[🗽 NYC D1<br/>North America]  
        LONDON[🇬🇧 London D1<br/>Europe]
    end
    
    subgraph "Brand Isolation Strategy"
        NEKO_TABLES[🐱 Neko Tables<br/>Prefix: neko_]
        TOKI_TABLES[🎨 Tokinoe Tables<br/>Prefix: toki_]
        DOG_TABLES[🐕 Dog Tables<br/>Prefix: dog_]
        SHARED_TABLES[🔗 Shared Tables<br/>Common Structure]
    end
    
    subgraph "Data Synchronization"
        PRIMARY[Primary Write Node]
        REPLICA1[Read Replica 1]
        REPLICA2[Read Replica 2]
        REPLICA3[Read Replica 3]
    end
    
    TOKYO --> NEKO_TABLES
    TOKYO --> TOKI_TABLES  
    TOKYO --> DOG_TABLES
    TOKYO --> SHARED_TABLES
    
    NYC --> NEKO_TABLES
    NYC --> TOKI_TABLES
    NYC --> DOG_TABLES  
    NYC --> SHARED_TABLES
    
    LONDON --> NEKO_TABLES
    LONDON --> TOKI_TABLES
    LONDON --> DOG_TABLES
    LONDON --> SHARED_TABLES
    
    PRIMARY --> REPLICA1
    PRIMARY --> REPLICA2
    PRIMARY --> REPLICA3
    
    style NEKO_TABLES fill:#e8d5ff
    style TOKI_TABLES fill:#d5ffe8
    style DOG_TABLES fill:#ffe8d5
    style SHARED_TABLES fill:#f0f8ff

📊 テーブル設計（完全版）​

👤 ユーザー管理テーブル（非PII）​

🔰 初心者向け解説
**ユーザー管理（非PII版）**とは「個人情報以外のユーザー関連データ」を管理すること。
個人情報（名前、住所、メール等）は全てShopifyに集約保存し、
D1にはFirebase UIDとShopify Customer IDの紐づけ・設定情報のみを保存します。

-- 🔐 ユーザー参照テーブル（PII一切なし）
CREATE TABLE user_sessions (
  id TEXT PRIMARY KEY,                    -- BFF内部ID
  firebase_uid TEXT UNIQUE NOT NULL,     -- Firebase認証ID（個人情報への参照）
  preferred_language TEXT DEFAULT 'ja',  -- 言語設定
  timezone TEXT DEFAULT 'Asia/Tokyo',    -- タイムゾーン
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  last_login_at DATETIME,               -- 最終ログイン時刻
  status TEXT DEFAULT 'active'          -- active, suspended, deleted
);

-- 🏷️ ブランド別ユーザー設定テーブル（PII一切なし）
CREATE TABLE user_brand_settings (
  id TEXT PRIMARY KEY,
  firebase_uid TEXT NOT NULL,
  brand TEXT NOT NULL,                   -- neko, tokinoe, dog
  shopify_customer_id TEXT,              -- ShopifyのカスタマーID（参照のみ）
  marketing_consent BOOLEAN DEFAULT FALSE,
  notification_settings TEXT,           -- JSON: 通知設定
  preferences TEXT,                      -- JSON: ユーザー設定
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (firebase_uid) REFERENCES user_sessions(firebase_uid),
  UNIQUE(firebase_uid, brand)
);

-- 📋 ユーザー行動ログ（匿名化）
CREATE TABLE user_activity_logs (
  id TEXT PRIMARY KEY,
  firebase_uid TEXT NOT NULL,            -- 個人特定はできないが行動追跡用
  brand TEXT NOT NULL,
  activity_type TEXT NOT NULL,          -- view_product, add_to_cart, purchase
  resource_id TEXT,                     -- 商品IDなど
  metadata TEXT,                        -- JSON: 匿名化された詳細情報
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (firebase_uid) REFERENCES user_sessions(firebase_uid)
);

🔐 個人情報の分離設計​

graph TB
    subgraph "🔥 Firebase（認証のみ）"
        FB_AUTH[🔐 Authentication<br/>firebase_uid, 認証情報のみ]
    end
    
    subgraph "🗄️ D1（非PII）"
        D1_SESSION[🔐 User Sessions<br/>firebase_uid, settings]
        D1_BRAND[🏷️ Brand Settings<br/>preferences, consent]
        D1_ACTIVITY[📊 Activity Logs<br/>行動データ]
        D1_PRODUCTS[📦 Products<br/>商品キャッシュ]
        D1_ORDERS[📋 Order Cache<br/>注文キャッシュ]
    end
    
    subgraph "🛒 Shopify（個人情報集約）"
        SH_CUSTOMER[👥 Customer<br/>name, email, phone, address]
        SH_ORDER[📦 Orders<br/>注文・配送・決済]
        SH_PRODUCTS[🎨 Products<br/>商品マスター]
    end
    
    FB_AUTH -->|firebase_uid| D1_SESSION
    D1_SESSION --> D1_BRAND
    D1_SESSION --> D1_ACTIVITY
    D1_BRAND -.->|shopify_customer_id| SH_CUSTOMER
    SH_CUSTOMER --> SH_ORDER
    SH_PRODUCTS --> D1_PRODUCTS
    SH_ORDER --> D1_ORDERS
    
    style FB_AUTH fill:#fce38a
    style D1_SESSION fill:#99ccff
    style D1_BRAND fill:#99ccff
    style D1_ACTIVITY fill:#99ccff
    style D1_PRODUCTS fill:#99ccff
    style D1_ORDERS fill:#99ccff
    style SH_CUSTOMER fill:#ff9999
    style SH_ORDER fill:#ff9999
    style SH_PRODUCTS fill:#95e1d3

🛒 商品・在庫管理テーブル​

🔰 初心者向け解説
商品管理とは「どんな商品があるか、在庫はあるか」を管理すること。
Shopifyの商品情報をキャッシュして、お客様に素早く商品を表示できます。
また、ブランド別に商品を分けて管理します。

-- 🏷️ ブランド別商品テーブル
CREATE TABLE products (
  id TEXT PRIMARY KEY,                   -- Shopify Product ID
  brand TEXT NOT NULL,                   -- neko, tokinoe, dog
  title TEXT NOT NULL,                   -- 商品名
  handle TEXT NOT NULL,                  -- URL用のハンドル
  description TEXT,                      -- 商品説明
  product_type TEXT,                     -- 商品カテゴリ
  vendor TEXT,                           -- 販売者
  tags TEXT,                             -- タグ（JSON配列）
  status TEXT DEFAULT 'active',          -- active, archived, draft
  
  -- SEO関連
  seo_title TEXT,
  seo_description TEXT,
  
  -- 価格情報（キャッシュ用）
  price_min DECIMAL(10,2),               -- 最低価格
  price_max DECIMAL(10,2),               -- 最高価格
  
  -- 在庫情報
  inventory_quantity INTEGER DEFAULT 0,   -- 総在庫数
  inventory_management TEXT,              -- Shopify, manual, null
  
  -- Shopify同期情報
  shopify_created_at DATETIME,
  shopify_updated_at DATETIME,
  last_synced_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  
  -- システム情報
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 🎨 商品バリエーション（サイズ・色など）テーブル
CREATE TABLE product_variants (
  id TEXT PRIMARY KEY,                   -- Shopify Variant ID
  product_id TEXT NOT NULL,
  brand TEXT NOT NULL,
  title TEXT NOT NULL,                   -- バリエーション名
  option1 TEXT,                          -- オプション1（サイズなど）
  option2 TEXT,                          -- オプション2（色など）
  option3 TEXT,                          -- オプション3（材質など）
  sku TEXT,                              -- 商品コード
  barcode TEXT,                          -- バーコード
  
  -- 価格情報
  price DECIMAL(10,2) NOT NULL,
  compare_at_price DECIMAL(10,2),        -- 比較価格（定価）
  
  -- 在庫情報
  inventory_quantity INTEGER DEFAULT 0,
  inventory_policy TEXT DEFAULT 'deny',  -- deny, continue
  requires_shipping BOOLEAN DEFAULT TRUE,
  
  -- 物理情報
  weight DECIMAL(8,2),                   -- 重量（グラム）
  weight_unit TEXT DEFAULT 'g',
  
  -- 状態
  available BOOLEAN DEFAULT TRUE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 🖼️ 商品画像テーブル
CREATE TABLE product_images (
  id TEXT PRIMARY KEY,
  product_id TEXT NOT NULL,
  variant_id TEXT,                       -- NULL = 全バリエーション共通
  brand TEXT NOT NULL,
  src TEXT NOT NULL,                     -- 画像URL
  alt TEXT,                              -- Alt テキスト
  position INTEGER DEFAULT 1,           -- 表示順序
  width INTEGER,
  height INTEGER,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES products(id)
);

🛒 注文・カート管理テーブル​

🔰 初心者向け解説
注文管理とは「お客様が何を注文したか」を記録すること。
カートは「買い物かご」、注文は「実際に購入が確定したもの」です。
Shopifyと連携して、注文状況をリアルタイムで管理します。

-- 🛒 カートテーブル
CREATE TABLE carts (
  id TEXT PRIMARY KEY,
  user_id TEXT,                          -- NULL = ゲストユーザー
  brand TEXT NOT NULL,
  shopify_checkout_id TEXT UNIQUE,       -- ShopifyのCheckout ID
  status TEXT DEFAULT 'active',          -- active, abandoned, completed
  
  -- 金額情報
  subtotal_price DECIMAL(10,2) DEFAULT 0,
  tax_price DECIMAL(10,2) DEFAULT 0,
  shipping_price DECIMAL(10,2) DEFAULT 0,
  total_price DECIMAL(10,2) DEFAULT 0,
  currency TEXT DEFAULT 'JPY',
  
  -- 配送情報
  shipping_address_id TEXT,
  billing_address_id TEXT,
  
  -- 状態管理
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME,                   -- カート有効期限
  completed_at DATETIME,                 -- 注文完了日時
  
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (shipping_address_id) REFERENCES user_addresses(id),
  FOREIGN KEY (billing_address_id) REFERENCES user_addresses(id)
);

-- 🛒 カートアイテムテーブル
CREATE TABLE cart_items (
  id TEXT PRIMARY KEY,
  cart_id TEXT NOT NULL,
  product_id TEXT NOT NULL,
  variant_id TEXT NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 1,
  unit_price DECIMAL(10,2) NOT NULL,     -- 単価
  total_price DECIMAL(10,2) NOT NULL,    -- 小計
  
  -- 商品情報のスナップショット（注文時点の情報保持）
  product_title TEXT,
  variant_title TEXT,
  product_image_url TEXT,
  
  -- カスタム情報
  properties TEXT,                        -- JSON: カスタム属性
  
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (cart_id) REFERENCES carts(id),
  FOREIGN KEY (product_id) REFERENCES products(id),
  FOREIGN KEY (variant_id) REFERENCES product_variants(id)
);

-- 📦 注文テーブル（他人分混入事故対策強化版）
CREATE TABLE orders (
  id TEXT PRIMARY KEY,
  user_id TEXT,
  brand TEXT NOT NULL,
  shopify_order_id TEXT UNIQUE NOT NULL, -- ShopifyのOrder ID
  shopify_customer_id TEXT NOT NULL,     -- ShopifyのCustomer ID（事故対策用）
  firebase_uid TEXT NOT NULL,            -- Firebase UID（事故対策用）
  order_number TEXT NOT NULL,            -- 注文番号
  
  -- 🚨 他人分混入事故対策フィールド
  security_status TEXT DEFAULT 'pending', -- pending, verified, failed, quarantined
  verification_passed BOOLEAN DEFAULT FALSE, -- 三点チェック結果
  uid_verification_count INTEGER DEFAULT 0,  -- UID検証済み写真数
  total_photo_count INTEGER DEFAULT 0,   -- 総写真数
  last_verification_at DATETIME,        -- 最終検証日時
  security_hash TEXT,                    -- 注文全体のセキュリティハッシュ
  
  -- 注文状態
  financial_status TEXT,                 -- paid, pending, refunded, etc.
  fulfillment_status TEXT,               -- fulfilled, partial, unfulfilled
  order_status TEXT DEFAULT 'open',      -- open, closed, cancelled
  
  -- 金額情報
  subtotal_price DECIMAL(10,2) NOT NULL,
  tax_price DECIMAL(10,2) DEFAULT 0,
  shipping_price DECIMAL(10,2) DEFAULT 0,
  discount_price DECIMAL(10,2) DEFAULT 0,
  total_price DECIMAL(10,2) NOT NULL,
  currency TEXT DEFAULT 'JPY',
  
  -- 顧客情報（スナップショット）
  customer_email TEXT,
  customer_phone TEXT,
  
  -- 配送情報
  shipping_address TEXT,                 -- JSON: 配送先住所
  billing_address TEXT,                  -- JSON: 請求先住所
  
  -- 日時情報
  processed_at DATETIME,                 -- 処理日時
  cancelled_at DATETIME,                 -- キャンセル日時
  closed_at DATETIME,                    -- 完了日時
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 📦 注文アイテムテーブル
CREATE TABLE order_items (
  id TEXT PRIMARY KEY,
  order_id TEXT NOT NULL,
  shopify_line_item_id TEXT NOT NULL,
  product_id TEXT NOT NULL,
  variant_id TEXT NOT NULL,
  quantity INTEGER NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  total_price DECIMAL(10,2) NOT NULL,
  
  -- 商品情報のスナップショット
  product_title TEXT,
  variant_title TEXT,
  product_image_url TEXT,
  sku TEXT,
  
  -- カスタム情報
  properties TEXT,                        -- JSON: カスタム属性
  fulfillment_status TEXT,               -- fulfilled, partial, unfulfilled
  
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id),
  FOREIGN KEY (variant_id) REFERENCES product_variants(id)
);

📊 システム・キャッシュテーブル​

🔰 初心者向け解説
システムテーブルとは「システムが正常に動くための情報」を保存するテーブル。
キャッシュは「よく使うデータを一時的に保存して高速化」するためのテーブルです。

-- ⚡ APIキャッシュテーブル
CREATE TABLE api_cache (
  key TEXT PRIMARY KEY,                  -- キャッシュキー
  value TEXT NOT NULL,                   -- キャッシュ値（JSON）
  brand TEXT,                            -- ブランド（NULL = 全ブランド共通）
  expires_at DATETIME NOT NULL,          -- 有効期限
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 📊 Shopify同期状態テーブル
CREATE TABLE sync_status (
  id TEXT PRIMARY KEY,
  brand TEXT NOT NULL,
  resource_type TEXT NOT NULL,          -- products, orders, customers
  last_sync_at DATETIME,                -- 最終同期時刻
  last_sync_cursor TEXT,                -- 同期カーソル（ページネーション用）
  status TEXT DEFAULT 'idle',           -- idle, running, error
  error_message TEXT,                   -- エラーメッセージ
  total_synced INTEGER DEFAULT 0,       -- 同期済み件数
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(brand, resource_type)
);

-- 📝 システムログテーブル
CREATE TABLE system_logs (
  id TEXT PRIMARY KEY,
  level TEXT NOT NULL,                   -- info, warn, error, critical
  category TEXT NOT NULL,               -- auth, api, sync, security
  message TEXT NOT NULL,
  details TEXT,                         -- JSON: 詳細情報
  user_id TEXT,                         -- 関連ユーザーID
  brand TEXT,                           -- 関連ブランド
  ip_address TEXT,                      -- IPアドレス
  user_agent TEXT,                      -- ユーザーエージェント
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 🔒 セキュリティイベントテーブル
CREATE TABLE security_events (
  id TEXT PRIMARY KEY,
  event_type TEXT NOT NULL,             -- login_failed, suspicious_activity, etc.
  severity TEXT NOT NULL,               -- low, medium, high, critical
  user_id TEXT,
  ip_address TEXT,
  user_agent TEXT,
  details TEXT,                         -- JSON: イベント詳細
  resolved BOOLEAN DEFAULT FALSE,
  resolved_at DATETIME,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

🚨 他人分混入事故対策テーブル​

🔰 初心者向け解説
他人分混入事故対策とは「他人の写真が間違って配送される事故を防ぐ」システム。
写真1枚1枚にFirebase UIDを埋め込み、3つのチェックポイントで確実に本人確認を行います。

-- 📸 写真記録テーブル（他人分混入事故対策の中核）
CREATE TABLE photo_records (
  id TEXT PRIMARY KEY,
  shopify_order_id TEXT NOT NULL,       -- Shopify注文ID（主キー）
  shopify_line_item_id TEXT NOT NULL,   -- Shopify注文アイテムID
  firebase_uid TEXT NOT NULL,           -- Firebase UID（写真に埋め込み済み）
  shopify_customer_id TEXT NOT NULL,    -- Shopify顧客ID
  
  -- 写真情報
  r2_key TEXT NOT NULL,                 -- Cloudflare R2保存キー
  original_filename TEXT,               -- 元ファイル名
  file_size INTEGER NOT NULL,           -- ファイルサイズ（バイト）
  content_type TEXT DEFAULT 'image/jpeg', -- MIME タイプ
  
  -- セキュリティ情報
  photo_hash TEXT NOT NULL,             -- SHA-256ハッシュ（改ざん検知用）
  uid_embedding_status TEXT DEFAULT 'pending', -- pending, embedded, failed
  uid_verification_status TEXT DEFAULT 'pending', -- pending, verified, failed
  
  -- メタデータ
  exif_data TEXT,                       -- JSON: EXIF情報（UID埋め込み済み）
  upload_metadata TEXT,                 -- JSON: アップロード時メタデータ
  
  -- 処理状況
  processing_stage TEXT DEFAULT 'uploaded', -- uploaded, verified, factory_ready, printed
  last_processed_at DATETIME,           -- 最終処理日時
  
  -- 日時記録
  uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  uid_embedded_at DATETIME,             -- UID埋め込み完了日時
  verified_at DATETIME,                 -- 検証完了日時
  
  FOREIGN KEY (shopify_order_id) REFERENCES orders(shopify_order_id)
);

-- 🔐 三点検証ログテーブル
CREATE TABLE verification_logs (
  id TEXT PRIMARY KEY,
  shopify_order_id TEXT NOT NULL,
  verification_type TEXT NOT NULL,      -- order_creation, payment_confirmation, factory_processing
  
  -- 検証データ
  firebase_uid TEXT NOT NULL,
  shopify_customer_id TEXT NOT NULL,
  shopify_order_id_check TEXT NOT NULL,
  
  -- 検証結果
  verification_result TEXT NOT NULL,    -- success, failure
  verification_details TEXT,            -- JSON: 詳細情報
  failure_reason TEXT,                  -- 失敗理由
  
  -- アクション
  action_taken TEXT,                    -- none, order_cancelled, refund_processed, admin_notified
  
  -- メタデータ
  ip_address TEXT,
  user_agent TEXT,
  processed_by TEXT,                    -- system, admin, factory
  
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (shopify_order_id) REFERENCES orders(shopify_order_id)
);

-- 🏭 工場処理ログテーブル
CREATE TABLE factory_processing_logs (
  id TEXT PRIMARY KEY,
  shopify_order_id TEXT NOT NULL,
  batch_id TEXT,                        -- 工場バッチID
  
  -- 処理状況
  processing_status TEXT DEFAULT 'queued', -- queued, processing, completed, failed
  total_photos INTEGER NOT NULL,        -- 総写真数
  verified_photos INTEGER DEFAULT 0,    -- 検証済み写真数
  failed_photos INTEGER DEFAULT 0,      -- 失敗した写真数
  
  -- 検証結果
  uid_verification_passed BOOLEAN DEFAULT FALSE, -- 全写真のUID検証結果
  hash_verification_passed BOOLEAN DEFAULT FALSE, -- 全写真のハッシュ検証結果
  overall_verification_passed BOOLEAN DEFAULT FALSE, -- 総合検証結果
  
  -- エラー情報
  error_details TEXT,                   -- JSON: エラー詳細
  failed_photo_ids TEXT,                -- JSON: 失敗した写真IDリスト
  
  -- 処理時間
  started_at DATETIME,
  completed_at DATETIME,
  processing_duration INTEGER,          -- 処理時間（秒）
  
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (shopify_order_id) REFERENCES orders(shopify_order_id)
);

-- 🚨 セキュリティインシデントテーブル
CREATE TABLE security_incidents (
  id TEXT PRIMARY KEY,
  incident_type TEXT NOT NULL,          -- uid_mismatch, hash_mismatch, unauthorized_access
  severity TEXT NOT NULL,               -- low, medium, high, critical
  
  -- 関連情報
  shopify_order_id TEXT,
  photo_record_id TEXT,
  firebase_uid TEXT,
  
  -- インシデント詳細
  description TEXT NOT NULL,
  detection_method TEXT,                -- automated, manual, system_check
  evidence TEXT,                        -- JSON: 証拠データ
  
  -- 対応状況
  status TEXT DEFAULT 'open',           -- open, investigating, resolved, false_positive
  assigned_to TEXT,                     -- 担当者
  resolution TEXT,                      -- 解決方法
  
  -- 影響範囲
  affected_users TEXT,                  -- JSON: 影響を受けたユーザーリスト
  impact_assessment TEXT,               -- 影響度評価
  
  -- 日時記録
  detected_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  resolved_at DATETIME,
  
  FOREIGN KEY (shopify_order_id) REFERENCES orders(shopify_order_id),
  FOREIGN KEY (photo_record_id) REFERENCES photo_records(id)
);

🚀 インデックス設計（パフォーマンス最適化）​

📈 高速検索用インデックス​

-- ユーザー関連インデックス
CREATE INDEX idx_users_firebase_uid ON users(firebase_uid);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status);

CREATE INDEX idx_user_brand_profiles_user_brand ON user_brand_profiles(user_id, brand);
CREATE INDEX idx_user_brand_profiles_shopify_customer ON user_brand_profiles(shopify_customer_id);

-- 商品関連インデックス
CREATE INDEX idx_products_brand ON products(brand);
CREATE INDEX idx_products_status ON products(status);
CREATE INDEX idx_products_handle ON products(handle);
CREATE INDEX idx_products_updated_at ON products(updated_at);

CREATE INDEX idx_product_variants_product_id ON product_variants(product_id);
CREATE INDEX idx_product_variants_sku ON product_variants(sku);
CREATE INDEX idx_product_variants_available ON product_variants(available);

-- 注文関連インデックス
CREATE INDEX idx_orders_user_brand ON orders(user_id, brand);
CREATE INDEX idx_orders_shopify_order_id ON orders(shopify_order_id);
CREATE INDEX idx_orders_status ON orders(order_status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

CREATE INDEX idx_carts_user_brand ON carts(user_id, brand);
CREATE INDEX idx_carts_status ON carts(status);
CREATE INDEX idx_carts_expires_at ON carts(expires_at);

-- システム関連インデックス
CREATE INDEX idx_api_cache_key_expires ON api_cache(key, expires_at);
CREATE INDEX idx_api_cache_brand_expires ON api_cache(brand, expires_at);

CREATE INDEX idx_system_logs_level_created ON system_logs(level, created_at);
CREATE INDEX idx_system_logs_category_created ON system_logs(category, created_at);

CREATE INDEX idx_security_events_severity_created ON security_events(severity, created_at);
CREATE INDEX idx_security_events_resolved ON security_events(resolved);

-- 🚨 他人分混入事故対策関連インデックス
CREATE INDEX idx_photo_records_order_id ON photo_records(shopify_order_id);
CREATE INDEX idx_photo_records_firebase_uid ON photo_records(firebase_uid);
CREATE INDEX idx_photo_records_customer_id ON photo_records(shopify_customer_id);
CREATE INDEX idx_photo_records_r2_key ON photo_records(r2_key);
CREATE INDEX idx_photo_records_hash ON photo_records(photo_hash);
CREATE INDEX idx_photo_records_processing_stage ON photo_records(processing_stage);
CREATE INDEX idx_photo_records_uploaded_at ON photo_records(uploaded_at);

CREATE INDEX idx_verification_logs_order_id ON verification_logs(shopify_order_id);
CREATE INDEX idx_verification_logs_type_result ON verification_logs(verification_type, verification_result);
CREATE INDEX idx_verification_logs_firebase_uid ON verification_logs(firebase_uid);
CREATE INDEX idx_verification_logs_created_at ON verification_logs(created_at);

CREATE INDEX idx_factory_logs_order_id ON factory_processing_logs(shopify_order_id);
CREATE INDEX idx_factory_logs_status ON factory_processing_logs(processing_status);
CREATE INDEX idx_factory_logs_batch_id ON factory_processing_logs(batch_id);
CREATE INDEX idx_factory_logs_verification_passed ON factory_processing_logs(overall_verification_passed);

CREATE INDEX idx_security_incidents_order_id ON security_incidents(shopify_order_id);
CREATE INDEX idx_security_incidents_type_severity ON security_incidents(incident_type, severity);
CREATE INDEX idx_security_incidents_status ON security_incidents(status);
CREATE INDEX idx_security_incidents_detected_at ON security_incidents(detected_at);

-- 🔐 事故対策用複合インデックス（高速検索用）
CREATE INDEX idx_orders_security_verification ON orders(shopify_order_id, firebase_uid, shopify_customer_id, verification_passed);
CREATE INDEX idx_photo_records_verification_composite ON photo_records(shopify_order_id, firebase_uid, uid_verification_status);
CREATE INDEX idx_verification_logs_triple_check ON verification_logs(shopify_order_id, firebase_uid, shopify_customer_id, verification_result);

🔄 データ同期戦略​

⚡ Shopify ⇄ D1 同期フロー​

sequenceDiagram
    participant SHOPIFY as 🛒 Shopify
    participant WEBHOOK as 🔔 Webhook
    participant BFF as 🚀 BFF Worker
    participant D1 as 🗄️ D1 Database
    participant CACHE as ⚡ Edge Cache
    
    rect rgb(240, 248, 255)
        Note over SHOPIFY,D1: リアルタイム同期（Webhook）
        SHOPIFY->>WEBHOOK: Product Updated
        WEBHOOK->>BFF: POST /webhooks/products
        BFF->>D1: UPDATE products SET ...
        BFF->>CACHE: INVALIDATE product:*
        BFF-->>WEBHOOK: 200 OK
    end
    
    rect rgb(248, 255, 240)  
        Note over BFF,D1: バッチ同期（定期実行）
        BFF->>BFF: Scheduled Worker (5分毎)
        BFF->>SHOPIFY: GET /products.json
        BFF->>D1: BULK INSERT/UPDATE
        BFF->>CACHE: REFRESH cache
    end
    
    rect rgb(255, 248, 240)
        Note over BFF,CACHE: キャッシュ戦略
        BFF->>CACHE: CHECK cache
        alt Cache Hit
            CACHE-->>BFF: Cached Data
        else Cache Miss
            BFF->>D1: SELECT * FROM products
            D1-->>BFF: Database Data
            BFF->>CACHE: SET cache
        end
    end

🛡️ セキュリティ・プライバシー対策​

🔐 データ保護レベル​

データ種類保存場所保護レベル暗号化アクセス制限
個人情報（PII）Shopify最高Shopify暗号化Shopifyのセキュリティ
認証情報Firebase最高Firebase暗号化Firebase Auth
商品キャッシュD1中なし公開情報
ユーザー設定D1中なしfirebase_uidのみ
行動ログD1中匿名化管理者のみ
🚨 写真記録D1最高SHA-256ハッシュ本人＋管理者のみ
🚨 検証ログD1最高なしシステム＋管理者のみ
🚨 セキュリティインシデントD1最高なし管理者のみ

🔒 アクセス制御​

-- 🔐 Row Level Security (RLS) 設計（アプリケーションレベル実装）
-- D1では直接RLSは使えないが、アプリケーションレベルで厳格に実装

-- ユーザーは自分のデータのみアクセス可能
CREATE VIEW user_secure_view AS
SELECT * FROM users 
WHERE firebase_uid = get_current_firebase_uid();

-- ブランド管理者は自ブランドのデータのみアクセス可能
CREATE VIEW brand_secure_view AS
SELECT * FROM products 
WHERE brand = get_current_brand();

-- 🚨 他人分混入事故対策用セキュリティビュー

-- 写真記録：本人のみアクセス可能
CREATE VIEW photo_records_secure_view AS
SELECT * FROM photo_records 
WHERE firebase_uid = get_current_firebase_uid()
   OR get_current_role() IN ('admin', 'factory_supervisor');

-- 検証ログ：システムおよび管理者のみ
CREATE VIEW verification_logs_secure_view AS
SELECT * FROM verification_logs
WHERE get_current_role() IN ('system', 'admin', 'security_officer');

-- セキュリティインシデント：セキュリティ担当者のみ
CREATE VIEW security_incidents_secure_view AS
SELECT * FROM security_incidents
WHERE get_current_role() IN ('admin', 'security_officer')
   OR assigned_to = get_current_user_id();

-- 工場処理ログ：工場担当者および管理者のみ
CREATE VIEW factory_processing_logs_secure_view AS
SELECT * FROM factory_processing_logs
WHERE get_current_role() IN ('factory_supervisor', 'admin')
   OR get_current_factory_id() IS NOT NULL;

🚨 他人分混入事故対策専用セキュリティ設計​

graph TB
    subgraph "🔐 Triple Security Architecture"
        A[写真アップロード] --> B[Firebase UID埋め込み]
        B --> C[SHA-256ハッシュ生成]
        C --> D[D1記録]
        
        D --> E[注文確定時検証]
        E --> F[三点チェック]
        F --> G[検証ログ記録]
        
        G --> H[工場処理前検証]
        H --> I[全写真UID照合]
        I --> J[最終承認]
    end
    
    subgraph "🚨 Security Monitoring"
        K[リアルタイム監視]
        L[異常検知]
        M[即座停止]
        N[管理者通知]
    end
    
    F -.-> K
    I -.-> L
    L --> M
    M --> N
    
    style B fill:#ffcccb
    style F fill:#ffcccb
    style I fill:#ffcccb
    style M fill:#ff6b6b

セキュリティレベル設計:

  1. 🔐 Level 1: 写真レベルセキュリティ

    • Firebase UID埋め込み（EXIF UserComment）
    • SHA-256ハッシュによる改ざん検知
    • R2署名付きURLによるアクセス制限

  2. 🔐 Level 2: 注文レベルセキュリティ

    • 三点チェック（Order ID / Customer ID / Firebase UID）
    • 検証失敗時の自動停止・返金
    • 全検証ログの完全記録

  3. 🔐 Level 3: システムレベルセキュリティ

    • 工場処理前の全写真一括検証
    • セキュリティインシデント自動検知
    • 管理者への即時通知システム

アクセス権限マトリックス:

役割photo_recordsverification_logsfactory_processing_logssecurity_incidents
顧客自分の写真のみ閲覧❌ アクセス不可❌ アクセス不可❌ アクセス不可
システム全データ操作全ログ記録・参照❌ アクセス不可自動生成のみ
工場担当者処理対象のみ参照❌ アクセス不可担当分のみ操作❌ アクセス不可
管理者全データ参照全ログ参照全データ参照全データ操作
セキュリティ責任者全データ参照全ログ参照全データ参照全データ操作

---

**D1データベース設計完成**: ✅ 実装完了  
**Multi-Brand対応**: ✅ 完全分離設計  
**High-Performance索引**: ✅ 最適化済み  
**セキュリティ対策**: ✅ 多層防御実装

**次のドキュメント**: [API設計](./api-design) | [Shopify統合](./shopify-integration) | [セキュリティ実装](./security-implementation)