---
phase: 05-task-dispatch
plan: 01
subsystem: database
tags: [sqlite, drizzle, messages, repository, agents, threading]

# Dependency graph
requires:
  - phase: 04-agent-lifecycle
    provides: agents table for sender/recipient foreign keys
provides:
  - messages table with sender/recipient pattern
  - MessageRepository port interface
  - DrizzleMessageRepository adapter with full CRUD
  - Message threading via parentMessageId
affects: [05-task-dispatch, 06-coordination, 07-file-system-ui]

# Tech tracking
tech-stack:
  added: []
  patterns:
    - "Message participant pattern: senderType/senderId, recipientType/recipientId"
    - "Message threading via self-referential parentMessageId"
    - "Repository port/adapter following AgentRepository pattern"

key-files:
  created:
    - src/db/repositories/message-repository.ts
    - src/db/repositories/drizzle/message.ts
    - src/db/repositories/drizzle/message.test.ts
  modified:
    - src/db/schema.ts
    - src/db/repositories/index.ts
    - src/db/repositories/drizzle/index.ts
    - src/db/repositories/drizzle/test-helpers.ts

key-decisions:
  - "Sender/recipient pattern with type enum and nullable ID for agent-user communication"
  - "requiresResponse boolean for distinguishing questions from notifications"
  - "Self-referential parentMessageId for response threading"

# Metrics
duration: 4min
completed: 2026-01-30
---

# Phase 5 Plan 01: Message Schema & Repository Summary

**Messages table with sender/recipient pattern for agent-user communication, full CRUD repository with threading support**

## Performance

- **Duration:** 4 min
- **Started:** 2026-01-30T19:32:02Z
- **Completed:** 2026-01-30T19:35:38Z
- **Tasks:** 2
- **Files modified:** 7

## Accomplishments

- Added messages table to schema with sender/recipient pattern (agent or user)
- Implemented MessageRepository port interface with comprehensive query methods
- Built DrizzleMessageRepository adapter with full CRUD operations
- Added message threading support via parentMessageId for response linking
- Created 23 tests covering all operations and edge cases

## Task Commits

Each task was committed atomically:

1. **Task 1: Add messages table to schema** - `f873a32` (feat)
2. **Task 2: Create MessageRepository port and adapter** - `19dc75c` (feat)

## Files Created/Modified

- `src/db/schema.ts` - Added messages table with relations
- `src/db/repositories/message-repository.ts` - Port interface with query methods
- `src/db/repositories/drizzle/message.ts` - Drizzle adapter implementation
- `src/db/repositories/drizzle/message.test.ts` - 23 comprehensive tests
- `src/db/repositories/index.ts` - Re-export MessageRepository types
- `src/db/repositories/drizzle/index.ts` - Re-export DrizzleMessageRepository
- `src/db/repositories/drizzle/test-helpers.ts` - Added messages table to test schema

## Decisions Made

1. **Sender/recipient participant pattern**: Used senderType/senderId and recipientType/recipientId pairs to support both agent-to-user and user-to-agent messages
2. **requiresResponse flag**: Boolean distinguishes questions (needs reply) from notifications (informational)
3. **Self-referential threading**: parentMessageId links responses to original questions for conversation threading
4. **SET NULL on delete**: Foreign keys use SET NULL so messages persist even if sender/recipient agent is deleted

## Deviations from Plan

None - plan executed exactly as written.

## Issues Encountered

None

## User Setup Required

None - no external service configuration required.

## Next Phase Readiness

- Messages table ready for agent question persistence
- Repository supports all query patterns for pending messages and responses
- Ready for 05-02 (Task tRPC & CLI) and 05-05 (Message & Dispatch CLI)

---
*Phase: 05-task-dispatch*
*Completed: 2026-01-30*
