在庫同期システム データベース構成引き継ぎ書

本ドキュメントは、在庫同期システムが使用している SQLite3 データベースのテーブル設計、初期化方法、運用メンテナンス、および最新のシステム不具合改修内容について引き継ぎ用にまとめたものです。

📂 データベース基本情報

• ファイル配置: main/data/inventory_sync.db
  (※ 過去の改修により、カレントディレクトリに依存せず常にこの絶対パスを参照するようプログラムが最適化されています)
• エンジンタイプ: SQLite3
• エンコーディング: UTF-8
• 主要なバックアップ先:
• 本番サーバー: /var/www/inventory-sync/data/inventory_sync.db

🗄️ テーブル設計 (スキーマ定義)

1. processed_orders (処理済み注文テーブル)

• 用途: 注文データの重複取り込み・在庫の二重減算を防止するため、処理完了した各ストアの注文・商品品目レベルのキーを記録します。
• スキーマ: sql CREATE TABLE processed_orders ( id INTEGER PRIMARY KEY AUTOINCREMENT, order_id TEXT NOT NULL, -- 注文商品の一意なID (Shopifyは "注文ID_品目index", 楽天は "rakuten_注文ID_品目index") store_name TEXT NOT NULL, -- ストア識別名 (artgraph, PHOTOPRI, Qoo, rakuten) order_number TEXT, -- 注文番号 (表示用) processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 処理日時 shipping_date TEXT, -- 出荷予定日 order_type TEXT, -- 注文タイプ (大物印刷 / 小物印刷) order_size TEXT, -- 商品サイズ (A4, A3, 2L等) order_quantity INTEGER, -- 注文数量 order_color TEXT, -- カラー (チェリーウッド, ナチュラル等) UNIQUE(order_id, store_name) -- 重複登録防止のユニーク制約 );

2. inventory_adjustments (在庫調整履歴テーブル)

• 用途: システムが実行した在庫の減算・調整結果の全履歴を記録します。
• スキーマ: sql CREATE TABLE inventory_adjustments ( id INTEGER PRIMARY KEY AUTOINCREMENT, store_name TEXT NOT NULL, -- 調整対象ストア product_id TEXT NOT NULL, -- 商品ID variant_id TEXT, -- バリアントID / SKU old_stock INTEGER, -- 調整前在庫数 new_stock INTEGER, -- 調整後在庫数 adjustment_reason TEXT, -- 調整理由 (例: "部材消費（注文処理による）") adjusted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 調整日時 );

3. execution_logs (システム実行ログテーブル)

• 用途: システムのバッチ処理実行開始、正常完了、異常終了時のステータスを監視用に記録します。
• スキーマ: sql CREATE TABLE execution_logs ( id INTEGER PRIMARY KEY AUTOINCREMENT, execution_type TEXT NOT NULL, -- 実行タイプ ("inventory_sync" 等) status TEXT NOT NULL, -- 実行結果ステータス (started / completed / error) message TEXT, -- 補足メッセージ・エラー詳細 executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

4. material_conditions (部材条件マッピングテーブル)

• 用途: 各ストアの商品タイトルや管理番号、バリアント情報をスプレッドシート（在庫シート）の「どの行・列」と紐付けて在庫調整するかを動的に定義します。
• スキーマ: sql CREATE TABLE material_conditions ( id INTEGER PRIMARY KEY AUTOINCREMENT, material_name TEXT NOT NULL, -- 部材名 (例: "キャンバス木枠", "L字スタンド") store_name TEXT NOT NULL, -- ストア識別名 store_type TEXT NOT NULL, -- ストアタイプ (shopify / rakuten) condition_type TEXT NOT NULL, -- 条件判定タイプ (product_type / product_name_pattern / product_id_and_variant等) condition_value TEXT, -- 条件値 (例: "Canvas.", "L字スタンド") condition_pattern TEXT, -- (予備) 判定パターン sheet_name TEXT NOT NULL, -- スプレッドシートのシート名 sheet_row_start INTEGER, -- 対象セルの開始行 sheet_row_end INTEGER, -- 対象セルの終了行 sheet_col_type TEXT, -- タイプ(名称)列 (A列 等) sheet_col_size TEXT, -- サイズ列 (C列 等) sheet_col_stock TEXT, -- 在庫数列 (G列 等) rakuten_manage_number TEXT, -- 楽天側の管理番号 rakuten_sku_pattern TEXT, -- 楽天側のSKUパターン created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

🔧 直近の不具合改修内容

本番環境で発生していたデータの不整合・バグに対して以下の修正を行いました。

1. パスの絶対パス化によるデータベースの集約

• 問題: カレントディレクトリによってローカル内の別階層に inventory_sync.db が自動生成され、過去データと最新データが分断される。
• 修正: 実行ファイルの場所を基準（BASE_DIR）とし、data/inventory_sync.db へのフルパスで固定接続するよう変更。

2. Shopify重複チェックキーの「商品品目レベル」への統一

• 問題: 1つの注文に複数商品が含まれる場合、注文単位の order_id で重複判定していたため、同一注文内の別商品が再処理時にスキップされるか、二重引き下げになる現象が発生。
• 修正: ユニークキーを商品品目（Line Item）レベルの f"{order['id']}_{item_index}" に完全統一。

3. スプレッドシート在庫減算時の下限値 (0) 制限

• 問題: 在庫数が0の状態で注文が入った際、スプレッドシートが -1 や -2 などのマイナス値を記録し、楽天（0以上の整数のみ対応）との同期で値の乖離が発生。
• 修正: 在庫引き下げ時に max(0, current_stock - qty) とし、負の値にならないよう制限。

🛠️ データベース運用とメンテナンス

1. 基本操作コマンド (SQLite3)

本番サーバーにログイン後、以下のコマンドで手動メンテナンスが可能です。

# データベースへ接続
sqlite3 /var/www/inventory-sync/data/inventory_sync.db

# 接続後の便利なメタコマンド
.tables       -- テーブル一覧
.schema       -- テーブルの作成スキーマ
.header on    -- 出力時にヘッダー（列名）を表示
.mode column  -- 出力を見やすいテーブル形式にする

2. データメンテナンス SQL の例

定期的な古いログ・古い注文の削除

長期間の運用でデータサイズが肥大化するのを防ぐため、30日以上前の実行ログ、および出荷後7日以上経過した重複チェック用データをクリーンアップします。

-- 30日以上前のログをクリーンアップ
DELETE FROM execution_logs WHERE executed_at < datetime('now', '-30 days');

-- 出荷予定日から7日以上経過した重複チェック用注文データの削除
DELETE FROM processed_orders WHERE shipping_date < DATE('now', '-7 days');

⚠️ トラブルシューティング

1. Google Sheets API 認証エラー (invalid_grant: account not found)

本番サーバーのログにおいて、以下のようにスプレッドシート接続処理時に invalid_grant が発生した場合、Google Cloud Console 側で接続用サービスアカウントが無効化されています。

ERROR - 在庫情報取得エラー: ('invalid_grant: Invalid grant: account not found', ...)

• 原因: config/credentials.json 内に記載されている client_email (サービスアカウント) が無効、もしくは削除されています。
• 対策:
  1. GCP管理画面よりサービスアカウントを再作成、または有効なJSONキーを発行します。
  2. 発行したJSONキーファイルで /var/www/inventory-sync/config/credentials.json を上書き差し替えします。
  3. 新しいサービスアカウントのメールアドレスに対し、対象スプレッドシートの「共有」設定でアクセス権限（閲覧・編集）を再度付与します。