Claude Code一時ファイルからSkillsへ：ビジネスロジック抽出の実践ガイド

# tmpディレクトリを作成
mkdir -p application/tools/tmp

# .gitignoreでtmpの中身を除外（任意）
echo "application/tools/tmp/*.py" >> .gitignore
# または、観察目的で意図的にコミットする場合は除外しない

## Temporary Scripts Directory

**IMPORTANT**: Always save temporary Python scripts to `application/tools/tmp/` directory.

When generating temporary scripts:
1. Place them in `application/tools/tmp/`
2. Use descriptive filenames
3. Include docstrings

Example:
```python
# application/tools/tmp/validate_data_integrity.py
"""Temporary script to validate data integrity."""
```

application/tools/
├── src/
│   └── supabase_client/          # 自作Client（ビジネスロジック）
│       ├── models.py              # データモデル定義
│       ├── operations/            # テーブル操作クラス
│       │   ├── posts.py
│       │   ├── categories.py
│       │   ├── hashtags.py
│       │   └── ...
│       └── cli.py                 # CLIコマンド実装
├── tmp/                           # tmpスクリプト（ここ重要！）
│   ├── merge_duplicate_categories.py
│   ├── validate_orphaned_posts.py
│   ├── check_hashtags.py
│   ├── remove_hashtags_from_posts.py
│   └── ...
└── pyproject.toml                 # 依存関係管理

# application/tools/tmp/check_hashtags.py
import subprocess
import json

def get_all_post_uuids():
    """Get all post UUIDs from the database."""
    result = subprocess.run(
        ["uv", "run", "db", "posts", "list", "--limit", "1000"],  # ← CLIコマンドを呼び出す
        capture_output=True,
        text=True,
        check=True,
    )
    # Extract UUIDs from output
    # ...

def get_post_details(uuid):
    """Get post details by UUID."""
    result = subprocess.run(
        ["uv", "run", "db", "posts", "get", uuid],  # ← CLIコマンドを呼び出す
        capture_output=True,
        text=True,
        check=True,
    )
    return json.loads(result.stdout)

# 投稿一覧取得
uv run db posts list [--limit N] [--blog-url URL] [--pattern N] [--category NAME] [--hashtag NAME]

# 投稿詳細取得
uv run db posts get <UUID>

# 投稿作成
uv run db posts create --blog-url <URL> --pattern <1-3> --content <TEXT>

# 投稿更新
uv run db posts update <UUID> [--content TEXT] [--pattern N]

# 投稿削除
uv run db posts delete <UUID>

# ブログURL一覧
uv run db posts urls

# 統計情報
uv run db posts stats

# カテゴリ一覧
uv run db categories list

# カテゴリ作成
uv run db categories create <NAME>

# 統計情報
uv run db categories stats

# 重複検出
uv run db categories find-duplicates

# カテゴリマージ
uv run db categories merge --to <ID> --from <IDs> [--dry-run]

# ハッシュタグ一覧
uv run db hashtags list

# ハッシュタグ作成
uv run db hashtags create <NAME>

# 統計情報
uv run db hashtags stats

# 重複検出
uv run db hashtags find-duplicates

# ハッシュタグマージ
uv run db hashtags merge --to <ID> --from <IDs> [--dry-run]

# 予約投稿一覧
uv run db scheduled-posts list [--status <STATUS>] [--today] [--limit N]

# 予約投稿詳細
uv run db scheduled-posts get <UUID>

# 予約投稿作成
uv run db scheduled-posts create --text <TEXT> --scheduled-date <ISO8601> [--source-post-uuid <UUID>]

# 予約投稿更新
uv run db scheduled-posts update <UUID> [--status STATUS] [--scheduled-date DATE]

# 予約投稿削除
uv run db scheduled-posts delete <UUID>

# 孤立レコード検出
uv run db validate orphaned

# application/tools/tmp/check_hashtags.py
import subprocess
import json
import re

def get_all_post_uuids():
    result = subprocess.run(
        ["uv", "run", "db", "posts", "list", "--limit", "1000"],
        capture_output=True,
        text=True,
        check=True,
    )
    # UUIDを正規表現で抽出
    uuids = []
    for line in result.stdout.split('\n'):
        uuid_match = re.search(r'([0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12})', line)
        if uuid_match:
            uuids.append(uuid_match.group(1))
    return uuids

def has_hashtag(text):
    return bool(re.search(r'#\w+', text))

# メイン処理: 全投稿をチェックして分類
for uuid in get_all_post_uuids():
    post = get_post_details(uuid)
    if has_hashtag(post['content_text']):
        with_hashtags.append(post)

# application/tools/tmp/remove_hashtags_from_posts.py
import re
import subprocess
import sys

def remove_hashtags(text):
    # 日本語対応の正規表現でハッシュタグを削除
    cleaned = re.sub(r'#[\w\u3040-\u309F\u30A0-\u30FF\u4E00-\u9FFF]+', '', text)
    # Clean up multiple spaces
    cleaned = re.sub(r'\s+', ' ', cleaned)
    # Remove trailing/leading whitespace
    cleaned = cleaned.strip()
    return cleaned

# DRY RUNモードで安全確認
execute = '--execute' in sys.argv

for post in posts_with_hashtags:
    new_content = remove_hashtags(post['content_text'])
    if execute:
        subprocess.run([
            "uv", "run", "db", "posts", "update",
            post['uuid'], "--content", new_content
        ])
    else:
        print(f"DRY RUN: Would update {post['uuid']}")
        print(f"  OLD: {post['content_text'][:100]}...")
        print(f"  NEW: {new_content[:100]}...")

# tmp/内のスクリプトを名前でカウント
ls application/tools/tmp/*.py | xargs -n1 basename | sort | uniq -c | sort -nr

5 validate_orphaned_posts.py
4 bulk_update_scheduled_posts.py
3 merge_duplicate_categories.py
2 analyze_hashtag_stats.py
1 export_posts_csv.py

# tmp/validate_orphaned_posts.py の処理フロー
1. uv run db posts list（全投稿取得）
2. カテゴリ未設定の投稿をフィルタ
3. 結果をリスト表示

# 新しいコマンドとして実装
uv run db validate orphaned

# 出力例
Orphaned Scheduled Posts: 0
Unused Categories: 2
  ID: 106, Name: 本番環境構築
  ID: 66, Name: フロントエンド

Unused Hashtags: 1
  ID: 34, Name: #TEST

Total issues found: 3

# application/tools/src/supabase_client/operations/hashtags.py
"""Hashtags table operations"""

from supabase import Client
from ..models import Hashtag

class HashtagOperations:
    def __init__(self, client: Client):
        self.client = client
        self.table = "hashtags"

    def list(self, limit: int = 100, offset: int = 0) -> list[Hashtag]:
        result = (
            self.client.table(self.table)
            .select("*")
            .order("hashtag_name")
            .range(offset, offset + limit - 1)
            .execute()
        )
        return [Hashtag(**item) for item in result.data]

    def count_posts_by_hashtag(self) -> list[dict]:
        """
        Count posts for each hashtag

        Returns:
            [
                {"hashtag_id": 1, "hashtag_name": "#AI", "post_count": 15},
                ...
            ]
        """
        result = (
            self.client.table("post_hashtags")
            .select("hashtag_id, hashtags(hashtag_name)")
            .execute()
        )

        # Count posts per hashtag
        hashtag_counts: dict[int, dict] = {}
        for item in result.data:
            tag_id = item["hashtag_id"]
            tag_name = item["hashtags"]["hashtag_name"] if item.get("hashtags") else None
            if tag_id not in hashtag_counts:
                hashtag_counts[tag_id] = {
                    "hashtag_id": tag_id,
                    "hashtag_name": tag_name,
                    "post_count": 0,
                }
            hashtag_counts[tag_id]["post_count"] += 1

        # Sort by post count descending
        return sorted(
            hashtag_counts.values(),
            key=lambda x: x["post_count"],
            reverse=True,
        )

# application/tools/src/supabase_client/cli.py
import click
from .client import SupabaseClient
from .operations import HashtagOperations

@cli.group()
def hashtags():
    """Hashtag operations"""
    pass

@hashtags.command("list")
@click.option("--limit", type=int, default=10)
def hashtags_list(limit: int):
    client = SupabaseClient()
    ops = HashtagOperations(client.get_client())
    results = ops.list(limit=limit)

    for hashtag in results:
        click.echo(f"ID: {hashtag.hashtag_id}, Name: {hashtag.hashtag_name}")

@hashtags.command("stats")
def hashtags_stats():
    """Show hashtag usage statistics"""
    client = SupabaseClient()
    ops = HashtagOperations(client.get_client())
    stats = ops.count_posts_by_hashtag()

    click.echo("Hashtag Usage Statistics:")
    for item in stats:
        click.echo(f"  {item['hashtag_name']}: {item['post_count']} posts")

# 頻繁に生成されるスクリプト
ls application/tools/tmp/*.py | xargs -n1 basename | sort | uniq -c | sort -nr

# tmp観察ログ: 2024-11-15

## 今週の傾向
- validate_orphaned_posts.py: 5回生成（先週3回）
- merge_duplicate_categories.py: 3回生成（新規）
- bulk_update_scheduled_posts.py: 4回生成（継続）

## 発見
- 孤立投稿の検証は定期作業（毎日1回）
- カテゴリ重複チェックの需要が増加
- 一括更新のロジックが固まってきた

## ビジネスルール（tmpから抽出）
- 重複判定: 類似度90%以上
- マージ優先順位: 投稿数 > 作成日時 > UUID
- 孤立投稿: カテゴリ未設定 or ハッシュタグ0個

## アクション
- [ ] db validate orphaned コマンド追加
- [ ] db categories find-duplicates コマンド追加
- [ ] db categories merge --dry-run 実装

# uvのインストール
curl -LsSf https://astral.sh/uv/install.sh | sh

# プロジェクト初期化と依存関係追加
uv init
uv add click pandas requests

# tmpスクリプトを実行
uv run python application/tools/tmp/script.py

# TypeScript環境のセットアップ
npm init -y
npm install -D typescript ts-node @types/node
npx tsc --init

# tmpスクリプト用の設定
# tsconfig.json
{
  "compilerOptions": {
    "target": "ES2022",
    "module": "commonjs",
    "outDir": "./dist",
    "rootDir": "./",
    "strict": true,
    "esModuleInterop": true
  },
  "include": ["application/tools/tmp/**/*"]
}

# tmpスクリプトを実行
npx ts-node application/tools/tmp/script.ts

## Development Language Preferences

**Primary Language: Python 3.12+**
- Use `uv run python tmp/script.py` for execution
- Leverage type hints for better code generation

**Avoid**: Shell scripts for complex logic (use Python subprocess instead)

# ❌ 型ヒントなし（Claude Codeが推測する必要がある）
def get_posts(limit):
    return client.posts.list(limit=limit)

# ✅ 型ヒント付き（Claude Codeが正確に理解）
def get_posts(limit: int) -> list[Post]:
    return client.posts.list(limit=limit)