CachiBot: Skill for Claude Code

CachiBot Full-Stack Entity

Add a new persistent data entity across all layers: database, repository, models, API, frontend types, API client, and Zustand store.

Layer Overview

Database (SQLite)
  └── Repository (async CRUD)
      └── Pydantic Models (request/response schemas)
          └── API Routes (FastAPI endpoints)
              └── Frontend Types (TypeScript interfaces)
                  └── API Client (fetch functions)
                      └── Zustand Store (state management)

Step 1: Database Table

Edit cachibot/storage/database.py — add to the init_db() CREATE TABLE block:

-- Your entities
CREATE TABLE IF NOT EXISTS your_entities (
    id TEXT PRIMARY KEY,
    bot_id TEXT NOT NULL,
    title TEXT NOT NULL,
    description TEXT,
    status TEXT DEFAULT 'active',
    metadata TEXT DEFAULT '{}',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_your_entities_bot ON your_entities(bot_id);
CREATE INDEX IF NOT EXISTS idx_your_entities_status ON your_entities(status);

Conventions:

• Table name: snake_case, plural
• Always include id TEXT PRIMARY KEY
• Bot-scoped entities always have bot_id TEXT NOT NULL with an index
• Timestamps as ISO 8601 TEXT: created_at, updated_at
• JSON fields stored as TEXT with DEFAULT '{}' or DEFAULT '[]'
• Foreign keys with ON DELETE CASCADE or ON DELETE SET NULL

If adding columns to an existing table, add a migration at the end of init_db():

migrations = [
    # ... existing migrations ...
    "ALTER TABLE your_entities ADD COLUMN new_column TEXT",
]

Step 2: Repository

Add to cachibot/storage/repository.py (or create a new repo file):

class YourEntityRepository:
    """CRUD operations for your_entities table."""

    async def get_by_bot(self, bot_id: str) -> list[dict]:
        db = await get_db()
        async with db.execute(
            "SELECT * FROM your_entities WHERE bot_id = ? ORDER BY created_at DESC",
            (bot_id,),
        ) as cursor:
            rows = await cursor.fetchall()
        return [dict(row) for row in rows]

    async def get_by_id(self, entity_id: str) -> dict | None:
        db = await get_db()
        async with db.execute(
            "SELECT * FROM your_entities WHERE id = ?",
            (entity_id,),
        ) as cursor:
            row = await cursor.fetchone()
        return dict(row) if row else None

    async def save(self, entity: dict) -> None:
        db = await get_db()
        await db.execute(
            """INSERT OR REPLACE INTO your_entities
               (id, bot_id, title, description, status, metadata, created_at, updated_at)
               VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
            (
                entity["id"],
                entity["bot_id"],
                entity["title"],
                entity.get("description", ""),
                entity.get("status", "active"),
                entity.get("metadata", "{}"),
                entity["created_at"],
                entity["updated_at"],
            ),
        )
        await db.commit()

    async def update(self, entity_id: str, updates: dict) -> None:
        db = await get_db()
        set_clauses = ", ".join(f"{k} = ?" for k in updates)
        values = list(updates.values()) + [entity_id]
        await db.execute(
            f"UPDATE your_entities SET {set_clauses} WHERE id = ?",
            values,
        )
        await db.commit()

    async def delete(self, entity_id: str) -> None:
        db = await get_db()
        await db.execute("DELETE FROM your_entities WHERE id = ?", (entity_id,))
        await db.commit()