Status/Hook Board (Kanban-style)

[Entropy-rgb]

Problem Statement

No way to track data items, query results, or insights over time. Users cannot create "watch lists" or monitor specific data points. There is no kanban-style board for managing data-driven tasks.

Proposed Solution

Implement a Kanban-style board where users can pin query results, create status items, and set up hooks for data changes.

Acceptance Criteria

• Create customizable boards (e.g., "Sales Watchlist", "Data Quality")
• Add cards from query results (pin result to board)
• Card statuses: "Watching", "Alert", "Resolved", "Archived"
• Hook cards to re-run queries periodically
• Status change notifications
• Board sharing with team members
• Drag-and-drop card organization
• Card notes and comments
• Board templates

Technical Approach

Backend Changes

1. Board models (backend/app/pgdatabase/models.py):

class Board(Base):
    __tablename__ = "boards"
    id = Column(UUID, primary_key=True)
    user_id = Column(UUID, ForeignKey("users.id"))
    workspace_id = Column(UUID, ForeignKey("workspaces.id"))
    name = Column(String, nullable=False)
    description = Column(Text)
    is_shared = Column(Boolean, default=False)
    created_at = Column(DateTime, default=func.now())
    updated_at = Column(DateTime, onupdate=func.now())

class BoardColumn(Base):
    __tablename__ = "board_columns"
    id = Column(UUID, primary_key=True)
    board_id = Column(UUID, ForeignKey("boards.id"))
    name = Column(String, nullable=False)
    position = Column(Integer, default=0)
    color = Column(String, default="#cccccc")

class BoardCard(Base):
    __tablename__ = "board_cards"
    id = Column(UUID, primary_key=True)
    board_id = Column(UUID, ForeignKey("boards.id"))
    column_id = Column(UUID, ForeignKey("board_columns.id"))
    query_id = Column(UUID, ForeignKey("query_history.id"))
    title = Column(String, nullable=False)
    description = Column(Text)
    status = Column(String, default="watching")  # watching, alert, resolved, archived
    hook_enabled = Column(Boolean, default=False)
    hook_interval = Column(Integer)  # seconds
    last_checked = Column(DateTime)
    position = Column(Integer, default=0)
    metadata = Column(JSON)
    created_at = Column(DateTime, default=func.now())

class BoardCardComment(Base):
    __tablename__ = "board_card_comments"
    id = Column(UUID, primary_key=True)
    card_id = Column(UUID, ForeignKey("board_cards.id"))
    user_id = Column(UUID, ForeignKey("users.id"))
    content = Column(Text, nullable=False)
    created_at = Column(DateTime, default=func.now())

2. Board service (backend/app/services/boards.py):

class BoardService:
    def __init__(self, db, llm):
        self.db = db
        self.llm = llm
    
    async def create_board(self, user_id: str, name: str, template: str = None) -> Board:
        """Create a new board, optionally from template."""
        board = Board(user_id=user_id, name=name)
        self.db.add(board)
        
        # Add default columns
        default_columns = ["Watching", "Alert", "Resolved", "Archived"]
        for i, col_name in enumerate(default_columns):
            column = BoardColumn(board_id=board.id, name=col_name, position=i)
            self.db.add(column)
        
        await self.db.commit()
        return board
    
    async def add_card_from_query(self, board_id: str, column_id: str, 
                                   query_id: str, title: str) -> BoardCard:
        """Add a card from a query result."""
        card = BoardCard(
            board_id=board_id,
            column_id=column_id,
            query_id=query_id,
            title=title
        )
        self.db.add(card)
        await self.db.commit()
        return card
    
    async def check_hooks(self):
        """Check all cards with hooks and update if needed."""
        cards = self.db.query(BoardCard).filter(
            BoardCard.hook_enabled == True,
            BoardCard.last_checked < datetime.utcnow() - timedelta(seconds=BoardCard.hook_interval)
        ).all()
        
        for card in cards:
            await self._check_card(card)
    
    async def _check_card(self, card: BoardCard):
        """Re-run query for a card and check for changes."""
        query = self.db.query(QueryHistory).get(card.query_id)
        if not query:
            return
        
        # Execute query and compare with previous result
        new_result = await self._execute_query(query.sql)
        old_result = card.metadata.get("last_result")
        
        if old_result and self._has_significant_change(old_result, new_result):
            card.status = "alert"
            card.metadata["change_detected"] = True
            card.metadata["last_result"] = new_result
        
        card.last_checked = datetime.utcnow()
        await self.db.commit()

3. API routes (backend/app/routes/boards.py):

• GET /api/boards - List boards
• POST /api/boards - Create board
• PATCH /api/boards/{id} - Update board
• DELETE /api/boards/{id} - Delete board
• GET /api/boards/{id}/columns - List columns
• POST /api/boards/{id}/columns - Add column
• POST /api/boards/{id}/cards - Add card
• PATCH /api/boards/{id}/cards/{card_id} - Update card
• DELETE /api/boards/{id}/cards/{card_id} - Delete card
• POST /api/boards/{id}/cards/{card_id}/move - Move card to column
• POST /api/boards/{id}/cards/{card_id}/comments - Add comment

Frontend Changes

1. Board page (frontend/src/routes/boards/+page.svelte):

<script>
    import { dndzone } from 'svelte-dnd-action';
    import BoardColumn from '$lib/components/BoardColumn.svelte';
    
    export let board;
    let columns = board.columns;
    
    function handleDndConsider(columnId, e) {
        const colIndex = columns.findIndex(c => c.id === columnId);
        columns[colIndex].cards = e.detail.items;
    }
    
    function handleDndFinalize(columnId, e) {
        const colIndex = columns.findIndex(c => c.id === columnId);
        columns[colIndex].cards = e.detail.items;
        saveCardPositions(columnId, columns[colIndex].cards);
    }
</script>

<div class="board">
    <header>
        <h1>{board.name}</h1>
        <button on:click={addCard}>Add Card</button>
    </header>
    
    <div class="columns">
        {#each columns as column (column.id)}
            <BoardColumn 
                {column}
                on:consider={(e) => handleDndConsider(column.id, e)}
                on:finalize={(e) => handleDndFinalize(column.id, e)}
            />
        {/each}
    </div>
</div>

2. Card component (frontend/src/lib/components/BoardCard.svelte):

• Card with title, status badge
• Click to expand with details
• Comments section

3. Pin to board (frontend/src/lib/components/AnswerCard.svelte):

• "Pin to Board" button
• Board/column selector

Key Files

• backend/app/pgdatabase/models.py - Board models
• backend/app/services/boards.py - Board service (new)
• backend/app/routes/boards.py - API routes (new)
• frontend/src/routes/boards/+page.svelte - Board page
• frontend/src/lib/components/BoardColumn.svelte - Column component
• frontend/src/lib/components/BoardCard.svelte - Card component

Related Issues

• Part of Feature Roadmap (Feature Roadmap: Next Iteration of BoloDB #71)
• Related to Dashboard Builder (Dashboard Builder (Pin Results) #234) for pinned results
• Related to Anomaly Alerts (Anomaly Alerts #231) for status change notifications