A10 立替管理表スプシ 21列再設計 + 双方向書込 + 三段ガード dedup v1.1 rev3
変更概要（v1.0 → v1.1 rev3）
| 項目 | v1.0 | v1.1 rev3 |
|---|---|---|
| スプシ列数 | 23 列（A〜W） | 21 列（A〜U） |
| 書込み方向 | アップロード起点のみ | 双方向（アップロード + Freee MCP 起点） |
| 重複防止 | SHA256 + OCR 3項（二段） | SHA256 + OCR 3項 + Freee deal_id（三段） |
| 証憑有無 | なし | B 列: ◎/○/⚫/△/❌ の 5 状態 |
| 要確認 | B 列: ○ のみ | D 列: テキスト判定（9 種 / 区切り） |
| E 列「Freee 転記済」 | スタッフ手動 ✓（廃止） | reconciler 自動同期（rev3）: details[].account_item_id 有無で ✓ / 空欄を自動判定 |
| LaunchAgent | 2 件（pipeline 夜次 / poller 5 分） | 4 件（+ reconcile_weekly 月曜 / sort_weekly 月曜） |
| 段階導入 | — | Phase 1/2/3 ロードマップ |
新 21 列構造（A〜U）
左ブロック: 人間視点（5 列）
| 列 | ヘッダー | 旧列 | データ源 | 備考 |
|---|---|---|---|---|
| A | 発生日 | 旧 C | OCR | ソート主軸（A 列を発生日に移動） |
| B | 証憑有無 新 | — | reconciler 計算 | ◎/○/⚫/△/❌ |
| C | 状態/信頼 | 旧 S+R 統合 | パイプライン | 「ok / 0.95」形式 |
| D | 要確認 改善 | 旧 B 拡張 | 自動判定 | 何を確認かをテキストで（複数 / 区切り） |
| E | Freee 転記済 | 旧 O | reconciler 自動同期 rev3 | deal の details[].account_item_id 有無を判定。✓=仕訳登録済み、空欄=未完。スタッフ手動 ✓ は廃止。 |
中ブロック: 取引内容（12 列）
| 列 | ヘッダー | 旧列 | データ源 | 備考 |
|---|---|---|---|---|
| F | 支払区分 | 旧 G | UI | 会社支払/代表立替 |
| G | 証憑種別 | 旧 H 拡張 | UI | 電子証憑/紙証憑/クレカ明細/通信費（月次）/公租公課（5 大分類） |
| H | 証憑形式 新 | — | renamer 自動付与 | 電子領/電子請/電子その他 / 紙領/紙請/紙その他 / クレカ明細 / 通信費（月次） / 公租公課（9 細分） |
| I | 金額(税込) | 旧 D | OCR + MCP 補完 | 外貨は reconciler が JPY 換算値を埋める |
| J | 外貨（合計） | 旧 F | OCR | 外貨時のみ "$11.51 USD" 形式 |
| K | 勘定科目（ID） | 旧 L+M 統合 | OCR+マスタ照合 | 「交際費 (#232703272)」形式 |
| L | 品目メモ | 旧 J | OCR | |
| M | 店舗・発行者名 | 旧 I | OCR | |
| N | 備考 | 旧 K | UI/クレカ理由 | |
| O | 取引先 ID | 旧 V | reconciler | |
| P | Freee 連携 | 旧 P+Q+W 統合 | reconciler | 「deal_id (score 1.0)」形式 |
| Q | Drive 証憑 | 旧 T+U 統合 | パイプライン | =HYPERLINK 関数でファイル名→Drive URL |
右ブロック: システムメタ・税務（4 列）
| 列 | ヘッダー | 旧列 | データ源 | 備考 |
|---|---|---|---|---|
| R | 税区分 | 旧 N | マスタ照合 | |
| S | 消費税額 | 旧 E | OCR | |
| T | 消費税額（外貨）新 | — | OCR | 外貨時のみ |
| U | 処理日時 | 旧 A | システム | A 列から U 列に移動 |
証憑形式 H 列: 9 細分（renamer 自動付与）
| G 証憑種別（UI 選択） | → 自動付与される H 証憑形式 | 判定条件 |
|---|---|---|
| 電子証憑 | 電子領 | OCR テキストに「領収書/Receipt/領収印」を検出 |
| 電子請 | OCR テキストに「請求書/Invoice/INV」を検出 | |
| クレカ明細 | OCR テキストにクレカキーワード（VISA/MasterCard/JCB/AMEX 等）を検出 | |
| 電子その他 | 上記いずれも検出不能の場合 | |
| 紙証憑 | 紙領 | OCR テキストに「領収書/Receipt/領収印」を検出 |
| 紙請 | OCR テキストに「請求書/Invoice/INV」を検出 | |
| 紙その他 | 上記いずれも検出不能の場合 | |
| クレカ明細 | クレカ明細 | 固定（UI 選択と同値） |
| 通信費（月次） | 通信費（月次） | 固定（UI 選択と同値） |
| 公租公課 | 公租公課 | 固定（UI 選択と同値） |
旧データ移行時: 旧 H 列（電子領/電子請/紙領/紙請/クレカ明細/通信費（月次）/公租公課）をそのまま新 H 列に引き継ぎ、 新 G 列（5 大分類）は旧 H 値から逆引きして自動設定する。
証憑有無 B 列: 5 状態
| 表示 | 意味 | 判定条件 |
|---|---|---|
| ◎ | 完全証憑 | Drive ファイルあり かつ Freee deal の receipts[] が非空 |
| ○ | Drive のみ | Drive ファイルあり（Freee receipts なし） |
| ⚫ | Freee のみ | Drive ファイルなし、Freee receipts あり |
| △ | クレカ明細 | 証憑種別 = クレカ明細（実物証憑の代替） |
| ❌ | なし | Drive もなし / Freee receipts もなし |
Freee receipts チェックは reconciler が /api/1/deals/{deal_id} を GET して確認。全行再計算は --update-coverage フラグで実行。
要確認 D 列: 9 種の判定
| フラグ | 発火条件 |
|---|---|
| 金額不一致 | OCR 金額と Freee 金額が ±100 円超 |
| 日付差大 | OCR 日付と Freee 日付が ±7 日超 |
| OCR 信頼度低 | confidence < 0.7 |
| Freee 未連携 | reconciler 後も deal_id 未確定 |
| Freee 未処理 rev3 | Freee deal あり（P 列 deal_id 確定）+ E 列「Freee 転記済」が空欄（＝Freee UI で仕訳登録がまだ）。CEO が D 列フィルタで「やるべき仕訳作業」一覧が見える。 |
| Drive リンク切れ | Q 列 ID で files.get が 404（週次チェック） |
| クレカ明細・追記要 | 証憑種別=クレカ明細 + 備考（N 列）が空欄 |
| 公租公課要照合 | 公租公課で対応 Freee deal 未確定 |
| 外貨換算未補完 | 外貨ケースで I 列が空欄かつ Freee 連携あり |
複数フラグが同時発火した場合は「金額不一致 / OCR 信頼度低」のように " / " で連結して D 列に入る。
双方向書込み + 三段ガード dedup
アップロード起点フロー
ファイル受信 → SHA256 計算
├─ dedup 段 1（SHA256） ヒット → SKIP（既存行に Drive 情報追記のみ）
├─ OCR 実行 → dedup 段 2（date+amount+store）
│ ├─ 既存行と (日付±3日 + 金額一致) → 既存行に P 列追記、新規行追加せず
│ └─ マッチなし → 新規行追加（証憑有無=○）
Freee MCP 起点フロー（reconciler --create-missing）
全 Freee deals 取得 → 各 deal について:
├─ dedup 段 3（freee_deal_id） ヒット → SKIP
├─ スプシ O 列に同 deal_id → SKIP
├─ (日付±3日 + 金額一致) の既存行あり → 既存行に O 列追記（マージ）
└─ マッチなし → 新規行追加（証憑有無=⚫ or ❌）
三段ガード dedup_index
1
段 1: SHA256 — ファイルバイトハッシュ完全一致。同一ファイルの再投入を確実にブロック。
2
段 2: OCR 3項 — (日付 ± 3日) + (金額一致) + (店舗名部分一致) でファイルの中身が同じかチェック。スキャン再送等をブロック。
3
段 3: Freee deal_id ★新 — deal_id が既に dedup_index に登録されていれば SKIP。Freee 起点での同 deal 二重行作成をブロック。
dedup_index.json スキーマ（v1.1）
{
"schema_version": "1.1",
"entries": [
{
"sha256": "abc123...", // 段 1
"amount_total": 1100, // 段 2
"date": "2026-05-21", // 段 2
"store_name": "テスト店舗", // 段 2
"store_name_normalized": "...",
"freee_deal_id": "12345678", // 段 3 ★新
"drive_file_id": "1TDn5-...",
"sheet_row": 9,
"processed_at": "2026-05-28T09:00:00+09:00",
"freee_attached": false,
"source": "upload" | "freee" | "merged" // ★新
}
]
}
段階導入ロードマップ
| Phase | 期間 | 内容 | コマンド例 |
|---|---|---|---|
| Phase 1 | 2026/5 分のみ | 双方向書込の動作実証（5 月分約 XX 件） |
python3 freee_reconciler_v3.py --backfill-period 2026-05-01 2026-05-31 --create-missing --update-coverage
|
| Phase 2 | Phase 1 OK 後 | 今期バックフィル（2025/7/1〜2026/4/30）全件スプシ反映 |
python3 freee_reconciler_v3.py --backfill-period 2025-07-01 2026-04-30 --create-missing
|
| Phase 3 | 2026/7/1〜 | 来期完全運用（週次 LaunchAgent で自動化） | LaunchAgent: reconcile_weekly（月曜 09:00） |
Phase 1 実験の確認シナリオ
| # | シナリオ | 期待する B 列 |
|---|---|---|
| 1 | アップロード → OCR → スプシ追加 → reconciler で Freee 紐付け | ◎ |
| 2 | Freee 自動連携 → reconciler で空スプシ行作成 → 後日アップロードでマージ | ◎ |
| 3 | クレカ明細のみ | △ |
| 4 | Drive 証憑だけで Freee 未連携（現金等） | ○ |
| 5 | Freee も Drive もなし（理論上 ❌ が出たらエラー検知） | ❌ |
バックフィル進捗の確認方法
バックフィル中は 5 分おきに Discord #finance-report に進捗投稿:
バックフィル進捗: 327/800 件処理 (40.9%)
内訳: Freee 起点 新規 280 / 既存マージ 47 / SKIP (重複) 0
整合性チェック
python3 freee_reconciler_v3.py \
--backfill-period 2026-05-01 2026-05-31 \
--verify-integrity
出力: Freee deals 件数 / スプシ行数 / dedup_index エントリ数 の三者比較。不一致時は Discord 通知。
マイグレーション手順（23列 → 21列）
# 1. dry-run（バックアップ + プレビュー）
python3 migrate_to_21col.py dry-run
# 2. CEO に dry-run 結果を提示・承認取得
# 3. execute（承認後）
python3 migrate_to_21col.py execute
# → 確認プロンプトで "yes" を入力
# 4. B 列（証憑有無）を補完
python3 freee_reconciler_v3.py --days 90 --update-coverage
# 5. A 列（発生日）でソート
python3 -c "from sheet_writer_v3 import sort_by_date_desc_v3; sort_by_date_desc_v3()"
# ロールバック（問題が起きた場合）
python3 migrate_to_21col.py rollback
バックアップ: _backups/<timestamp>_pre_21col_migration.json に自動保存。
LaunchAgent 4 件（稼働・追加）
| Label | 実行タイミング | 内容 | 状態 |
|---|---|---|---|
com.connect8.upan.run_pipeline |
毎日 23:59 | 夜次パイプライン（全件 OCR + スプシ追記） | 稼働中 |
com.connect8.upan.poller |
5 分おき | INBOX 監視（スタッフ投入ファイルを即時処理） | 稼働中 |
com.connect8.upan.reconcile_weekly |
月曜 09:00 | 週次 reconcile（--create-missing --update-coverage） | 追加（load 要） |
com.connect8.upan.sheet_sort_weekly |
月曜 09:30 | A 列（発生日）降順ソート（reconcile の 30 分後） | 追加（load 要） |
新規 LaunchAgent のインストール
# reconcile_weekly
cp ~/claude/2_Areas/A10_corporate_finance/phase_b_reimbursement/com.connect8.upan.reconcile_weekly.plist \
~/Library/LaunchAgents/
launchctl load ~/Library/LaunchAgents/com.connect8.upan.reconcile_weekly.plist
# sheet_sort_weekly
cp ~/claude/2_Areas/A10_corporate_finance/phase_b_reimbursement/com.connect8.upan.sheet_sort_weekly.plist \
~/Library/LaunchAgents/
launchctl load ~/Library/LaunchAgents/com.connect8.upan.sheet_sort_weekly.plist
# 稼働確認
launchctl list | grep upan
テストスイート
ファイル: test_sheet_redesign.py
cd ~/claude/2_Areas/A10_corporate_finance/phase_b_reimbursement
python3 -m pytest test_sheet_redesign.py -v
79+ passed in 0.05s
テストカバレッジ
- 4 シナリオ（A: Freee 先 → アップ後追 / B: アップ先 → reconciler 後追 / C: Freee のみ / D: Drive のみ）
- 三段ガード dedup（SHA256 / OCR 3項 / Freee deal_id）
- 要確認列の自動判定 8 パターン
- 証憑有無列の 5 状態（◎/○/⚫/△/❌）
- migrate_to_21col の列変換ロジック（12 ケース）
- reconcile_one 照合スコア（4 ケース）
関連ファイル一覧
| ファイル | 役割 | v3 対応 |
|---|---|---|
sheet_writer_v3.py | 21 列スプシ書込み（アップロード + Freee 起点） | 新規 |
freee_reconciler_v3.py | 照合 + --create-missing + --backfill-period + --verify-integrity | 新規 |
dedup_index_manager_v3.py | 三段 dedup（段 3: freee_deal_id 追加） | 新規 |
migrate_to_21col.py | 23 列 → 21 列マイグレーション（dry-run/execute/rollback） | 新規 |
test_sheet_redesign.py | 79+ 件テストスイート（H 列証憑形式 9 パターン + G/H 分割ロジック追加） | 新規 |
sheet_writer.py | 旧 23 列版（互換維持・非推奨） | 旧版 |
freee_reconciler.py | 旧 23 列照合（互換維持・非推奨） | 旧版 |
dedup_index_manager.py | 旧 二段 dedup（互換維持・非推奨） | 旧版 |
厳守ルール
- Freee 書込なし: MCP は GET のみ。
freee_api_post/put/patch/deleteは import 禁止。 - 金額丸め厳禁: 1 円単位で忠実に記録。
- マイグレ前バックアップ必須: dry-run 結果を CEO に提示してから execute。
- 個人情報マスク継続: メアド・口座番号・カード番号はマスク。
- supportsAllDrives=True: Drive API 呼び出し時に必須。
- 既存テスト全 PASS 維持: 新規実装後も既存テストがすべて通ること。