---
phase: 02-data-layer
plan: 01
type: execute
wave: 1
depends_on: []
files_modified:
  - package.json
  - src/db/config.ts
  - src/db/schema.ts
  - src/db/index.ts
  - drizzle.config.ts
autonomous: true
---

<objective>
Set up SQLite database with Drizzle ORM and define the task hierarchy schema.

Purpose: Establish the persistence layer that all later phases (Agent Lifecycle, Task Dispatch, Coordination) depend on.
Output: Working database connection factory and schema for initiative → phase → plan → task hierarchy.
</objective>

<execution_context>
@~/.claude/get-shit-done/workflows/execute-plan.md
@~/.claude/get-shit-done/templates/summary.md
</execution_context>

<context>
@.planning/PROJECT.md
@.planning/ROADMAP.md
@.planning/STATE.md
@src/events/types.ts
@src/events/bus.ts
</context>

<tasks>

<task type="auto">
  <name>Task 1: Install Drizzle ORM and SQLite dependencies</name>
  <files>package.json</files>
  <action>
    Install required dependencies:
    - drizzle-orm: The ORM for type-safe database operations
    - better-sqlite3: Synchronous SQLite driver for Node.js (preferred over async for local-first apps)
    - @types/better-sqlite3: TypeScript types for better-sqlite3
    - drizzle-kit: CLI tools for migrations (dev dependency)

    Use pnpm if lockfile exists, otherwise npm.
  </action>
  <verify>npm ls drizzle-orm better-sqlite3 drizzle-kit shows installed versions</verify>
  <done>All Drizzle and SQLite dependencies installed</done>
</task>

<task type="auto">
  <name>Task 2: Create database connection factory</name>
  <files>src/db/config.ts, src/db/index.ts, drizzle.config.ts</files>
  <action>
    Create database connection module following hexagonal patterns:

    1. src/db/config.ts:
       - Export function getDbPath() that returns ~/.cw/data/cw.db
       - Ensure parent directories exist (use mkdir -p equivalent)
       - Support optional path override via CW_DB_PATH env var for testing

    2. src/db/index.ts:
       - Import better-sqlite3 and drizzle
       - Export function createDatabase(path?: string): DrizzleDatabase
       - Default to getDbPath() if no path provided
       - Enable WAL mode for better concurrent read performance

    3. drizzle.config.ts:
       - Configure drizzle-kit for migrations
       - Point to schema file and db path
       - Use "better-sqlite" driver

    Pattern: Factory function, not singleton - allows multiple instances for testing.
  </action>
  <verify>
    Create minimal test: import { createDatabase } from './src/db/index.js'; const db = createDatabase(':memory:');
    Should not throw.
  </verify>
  <done>Database connection factory works with in-memory and file-based SQLite</done>
</task>

<task type="auto">
  <name>Task 3: Define task hierarchy schema</name>
  <files>src/db/schema.ts</files>
  <action>
    Define the four-level task hierarchy schema using Drizzle:

    1. initiatives table:
       - id: text (primary key, nanoid)
       - name: text (not null)
       - description: text (nullable)
       - status: text ('active' | 'completed' | 'archived')
       - createdAt: integer (Unix timestamp)
       - updatedAt: integer (Unix timestamp)

    2. phases table:
       - id: text (primary key)
       - initiativeId: text (foreign key -> initiatives.id, cascade delete)
       - number: integer (for ordering, e.g., 1, 2, 3)
       - name: text (not null)
       - description: text (nullable)
       - status: text ('pending' | 'in_progress' | 'completed')
       - createdAt: integer
       - updatedAt: integer

    3. plans table:
       - id: text (primary key)
       - phaseId: text (foreign key -> phases.id, cascade delete)
       - number: integer (for ordering)
       - name: text (not null)
       - description: text (nullable)
       - status: text ('pending' | 'in_progress' | 'completed')
       - wave: integer (for parallel execution grouping)
       - createdAt: integer
       - updatedAt: integer

    4. tasks table:
       - id: text (primary key)
       - planId: text (foreign key -> plans.id, cascade delete)
       - name: text (not null)
       - description: text (nullable)
       - type: text ('auto' | 'checkpoint:human-verify' | 'checkpoint:decision' | 'checkpoint:human-action')
       - status: text ('pending' | 'in_progress' | 'completed' | 'blocked')
       - order: integer (for sequencing within plan)
       - createdAt: integer
       - updatedAt: integer

    Export:
    - Table definitions (initiatives, phases, plans, tasks)
    - Inferred types (Initiative, Phase, Plan, Task, NewInitiative, etc.)
    - Relations for Drizzle relational queries

    Use Unix timestamps (integers) not Date objects for SQLite compatibility.
  </action>
  <verify>npm run build succeeds with no TypeScript errors in schema.ts</verify>
  <done>Schema defines all 4 tables with proper foreign key relationships and exported types</done>
</task>

</tasks>

<verification>
Before declaring plan complete:
- [ ] `npm run build` succeeds without errors
- [ ] Database can be created in memory: `createDatabase(':memory:')`
- [ ] Schema types are exported and usable
- [ ] drizzle.config.ts is valid (drizzle-kit validates it)
</verification>

<success_criteria>
- All dependencies installed (drizzle-orm, better-sqlite3, drizzle-kit)
- Database connection factory creates working SQLite connections
- Schema defines initiative → phase → plan → task hierarchy
- Foreign keys cascade deletes correctly
- Types are properly exported for use in repository layer
</success_criteria>

<output>
After completion, create `.planning/phases/02-data-layer/02-01-SUMMARY.md`
</output>
