# Task Phase 2A Schema Design

## Why Phase 2A still exists

`agentteam-task` already owns the top-level task tables:

- `task_boards`
- `tasks`

and task numbering no longer lives in channel-scope metadata.

That means the first ownership transfer already happened. The remaining leakage is subtler:

- `task_boards.channel_id` is still both the external channel reference and the board primary key
- `task_boards.workspace_id` still has a hard FK into core workspaces
- `tasks.{created,claimed,completed}_by_member_id` still have hard FKs into core members
- read paths still join core `members/users/agents/channels` tables directly to render task payloads

This prevents a future “move task to its own database” cutover, because task persistence still assumes core tables are locally joinable.

## Target model

Phase 2A redesigns the task-owned schema around task-native IDs plus opaque core references.

### Task boards

Target shape:

- `task_boards.id`
- `task_boards.workspace_ref`
- `task_boards.channel_ref`
- `task_boards.created_at`
- `task_boards.updated_at`
- unique key on `(workspace_ref, channel_ref)`

Notes:

- `workspace_ref` and `channel_ref` stay as external references, not foreign keys
- board identity becomes task-owned (`task_boards.id`), so internal task records no longer key off core `channel_id`

### Task counters

Target shape:

- `task_counters.board_id`
- `task_counters.next_task_number`
- `task_counters.updated_at`

Notes:

- number allocation remains task-owned and transactional
- separating the counter row from board metadata makes the future outbox/migration path clearer

### Tasks

Target shape:

- `tasks.id`
- `tasks.board_id`
- `tasks.task_number`
- `tasks.title`
- `tasks.status`
- `tasks.created_by_ref`
- `tasks.claimed_by_ref`
- `tasks.completed_by_ref`
- `tasks.created_at`
- `tasks.claimed_at`
- `tasks.completed_at`
- `tasks.updated_at`

Reference fields are opaque strings from core’s point of view:

- member refs stay stable as protocol IDs
- no FK from task tables back into `members`
- no FK from task tables back into channel scopes

Optional denormalized caches can be added later if task-native list views need fewer identity lookups:

- `created_by_name_cache`
- `claimed_by_name_cache`

## Access model after redesign

Task service should stop discovering authorization from task-table foreign keys.
Instead:

1. core resolves access and injects `ServiceContext`
2. task service treats `workspace_ref`, `channel_ref`, and member refs as opaque references
3. compat handlers in core continue to join/resolve display names when needed

That keeps task persistence task-owned while preserving the canonical Human
Task, workspace-scoped Agent Runtime Task, and `task:*` event surfaces.

## Concrete code hotspots affected in Phase 2B

- `internal/store/taskboard/service.go`
  - `ensureTaskBoard`
  - `allocateTaskNumbers`
  - `insertTask`
- `internal/store/taskboard/user_tasks.go`
  - task select joins that currently pull from `members/users/agents`
  - delete/update lookups that key directly by `channel_id`
- `internal/store/taskboard/agent_tasks.go`
  - board resolution and task lookups keyed by `channel_id`
- `internal/tidb/migrations/*`
  - add new board/counter/reference columns and backfill data

## Phase 2B migration outline

1. Add task-native board IDs and counter table.
2. Backfill `task_boards.id`, `task_counters`, and `tasks.board_id`.
3. Replace member/channel FKs with opaque ref columns.
4. Switch reads/writes to the new shape.
5. Drop legacy FK-coupled columns once parity is verified.