P0.3.2 — Step 3 Packet

Execution plan for src/domains/tasks/repository.ts + src/db/migrations/002_tasks.sql + src/db/schema.sql extension + src/__tests__/domains/tasks/repository.test.ts. Gates Step 4 (Implement). Sigma-pre-approved via the dispatch prompt; this packet records the pre-approval and adds packet-level tightening.

Upstream:

§1. File plan

Four files touched: three new, one edited. No package.json delta (all runtime deps already installed — better-sqlite3 from P0.2.2, crypto is Node built-in).

Path Action LOC estimate Role
src/db/migrations/002_tasks.sql new ~30 DDL — create tasks + indexes
src/db/schema.sql edit +~22 human-readable ownership block
src/domains/tasks/repository.ts new ~320 Production module
src/__tests__/domains/tasks/repository.test.ts new ~650 Test suite

Existing directories reused: src/domains/tasks/ (P0.3.1), src/db/migrations/ (P0.2.2), src/__tests__/ (P0.1.2). New directory: src/__tests__/domains/tasks/ (Sigma-specified subfolder).

No README.md in any new directory — module JSDoc and test describe blocks are the documentation.

§2. Design decisions locked in

Carried over from audit + contract. All Sigma-approved at dispatch.

# Issue Decision Rationale
1 Test path src/__tests__/domains/tasks/repository.test.ts Matches Sigma prompt; Jest roots + testMatch accept it.
2 Migration number 002 Sigma-assigned exclusively; 003 reserved for P0.6.2, 004 for P0.7.2.
3 Migration filename 002_tasks.sql (not 002_beta.sql) Match the concept table name; runner only parses numeric prefix.
4 UUID generation crypto.randomUUID() Node built-in, matches src/server.ts correlationId path.
5 FK on project_id None projects table does not exist in Phase 0.
6 Priority shape single TEXT column Defer two-axis split to later scheduler task; narrow schema now.
7 FSM enforcement Not in repository Dumb CRUD layer; enforcement is tool-layer (P0.3.4).
8 Singleton db binding None Repository accepts db parameter; tests use :memory:.
9 Soft-delete discipline getTask / default listTasks hide deleted; updateTask / deleteTask on deleted row throw Explicit in contract §3, §5b.
10 TaskNotFoundError Thrown by updateTask / deleteTask only getTask / listTasks never throw on empty result.
11 limit cap Clamp to 500 Defensive; Phase 0 never needs more.
12 Ordering created_at DESC, id DESC Stable pagination with id tiebreaker.

§3. Production module skeleton — src/domains/tasks/repository.ts

Ordered top-to-bottom:

§3a. Module header (JSDoc, ~25 lines)

  • Purpose line.
  • Canonical references (task-pipeline.md, colibri-system.md §6.3, state-machine.ts).
  • Chain artifact references (audit, contract, packet).
  • Side-effect summary: “pure functions; all I/O is via the caller-provided db handle”.
  • Consumed-by note: P0.3.3 writeback + P0.3.4 MCP tools.

§3b. Imports 

import { randomUUID } from 'node:crypto';
import type { Database } from 'better-sqlite3';
import { TASK_STATES, type TaskState } from './state-machine.js';

type imports per .eslintrc.json consistent-type-imports: error.

§3c. Public types

Task, CreateTaskInput, UpdateTaskPatch, ListTasksFilter — see contract §2.

§3d. TaskNotFoundError

Class as specified in contract §4.

§3e. Constants 

const DEFAULT_LIMIT = 50;
const MAX_LIMIT = 500;

§3f. Private helpers

  • rowToTask(row: unknown): Task — casts a raw SQLite row to the Task interface. Uses as casts because noUncheckedIndexedAccess would otherwise force per-field nullish checks we cannot meaningfully handle (a row returned by SELECT * either has all columns or the schema is broken, which is a programmer error). The cast is centralized here to keep the rest of the module type-clean.
  • nowIso(): string — thin wrapper around new Date().toISOString(). Kept as a helper for readability; inlining would also be fine.

§3g. Prepared-statement memoization

A module-private WeakMap<Database, { create, get, getForUpdate, update, delete, list, listFiltered, … }> caches compiled statements per db handle. Built lazily on first call for each db. Avoids re-compilation cost on hot paths (the repository is called per MCP request; future load profile benefits from cached statements).

Fallback path: for listTasks with varying filter shapes, statements are built per distinct shape key (${has_status}_${has_project_id}_${include_deleted}_${project_id_is_null}) — at most 2³×2 = 16 distinct shapes, pre-compiled on first use of each shape.

§3h. Public functions

Implementation order of the five exports (§3.1 through §3.5 in the module), each backed by one or more prepared statements:

  1. createTask(db, input) — INSERT then SELECT (one prepared each, bundled in a db.transaction).
  2. getTask(db, id) — single SELECT.
  3. updateTask(db, id, patch) — dynamic SQL: only fields present in patch go into the SET clause. Because the field set is tiny (6 optional fields) and the type system already enumerates them, we pre-build 64 statements lazily by bitmask of present fields. Each statement has a fixed WHERE clause excluding soft-deleted rows. On .run() with .changes === 0, throw TaskNotFoundError.
  4. deleteTask(db, id) — UPDATE SET deleted_at = ?, updated_at = ? WHERE id = ? AND deleted_at IS NULL. On .changes === 0, throw TaskNotFoundError.
  5. listTasks(db, filter) — builds one of the 16 shape-keyed statements; orders by created_at DESC, id DESC; applies LIMIT / OFFSET.

§3i. Type helpers consideration

noUncheckedIndexedAccess: true in tsconfig means any row['title'] returns string | undefined. Better-sqlite3 types a .get() result as unknown. The rowToTask helper uses a targeted cast once per read path rather than scattering ?? across the module. This matches P0.2.2 src/db/index.ts which also uses targeted casts for pragma results.

§4. Migration SQL — src/db/migrations/002_tasks.sql

Exact body (non-empty — better-sqlite3.exec('') guard is not hit):

-- 002_tasks — β Task Pipeline CRUD table (P0.3.2).
--
-- Earned by the β concept. The canonical 8 states live in
-- src/domains/tasks/state-machine.ts (TASK_STATES). The CHECK constraint
-- below must stay in sync with that tuple; if TASK_STATES ever changes,
-- a new migration (003_tasks_states.sql or similar) must drop and re-create
-- the CHECK — SQLite does not support ALTER TABLE CHECK modification.
--
-- Soft-delete: deleted_at IS NULL means "live"; a non-null ISO-8601 string
-- means "soft-deleted at that time". The repository layer hides soft-deleted
-- rows from getTask() and from listTasks() default queries.
--
-- project_id is intentionally NOT a FOREIGN KEY — no `projects` table
-- exists in Phase 0. When the projects domain lands (future phase), a
-- separate migration may add the constraint.
--
-- Canonical reference: docs/contracts/p0-3-2-task-crud-contract.md §6.

CREATE TABLE tasks (
  id          TEXT PRIMARY KEY,
  project_id  TEXT,
  title       TEXT NOT NULL,
  description TEXT,
  status      TEXT NOT NULL CHECK (status IN (
                'INIT','GATHER','ANALYZE','PLAN','APPLY','VERIFY','DONE','CANCELLED'
              )),
  priority    TEXT,
  assignee    TEXT,
  created_at  TEXT NOT NULL,
  updated_at  TEXT NOT NULL,
  deleted_at  TEXT
);

CREATE INDEX idx_tasks_project_status ON tasks(project_id, status, deleted_at);
CREATE INDEX idx_tasks_deleted ON tasks(deleted_at);

No IF NOT EXISTS — the migration runner guarantees one-time application via user_version. Duplicate-apply is a bug in the runner, not something this DDL should mask.

§5. schema.sql additions

After the existing comment header (which ends at line 24), append the tasks ownership block (keeping the file comment-only — it is never executed):


-- ---------------------------------------------------------------------------
-- β Task Pipeline — tasks table (earned by P0.3.2, migration 002_tasks.sql).
-- ---------------------------------------------------------------------------
--
--   CREATE TABLE tasks (
--     id          TEXT PRIMARY KEY,     -- UUID v4
--     project_id  TEXT,                 -- no FK (no projects table in Phase 0)
--     title       TEXT NOT NULL,
--     description TEXT,
--     status      TEXT NOT NULL         -- one of TASK_STATES (state-machine.ts)
--                  CHECK (status IN ('INIT','GATHER','ANALYZE','PLAN','APPLY',
--                                    'VERIFY','DONE','CANCELLED')),
--     priority    TEXT,                 -- single-column; may split later
--     assignee    TEXT,
--     created_at  TEXT NOT NULL,        -- ISO-8601
--     updated_at  TEXT NOT NULL,        -- ISO-8601
--     deleted_at  TEXT                  -- NULL = live; ISO-8601 = soft-deleted
--   );
--   CREATE INDEX idx_tasks_project_status ON tasks(project_id, status, deleted_at);
--   CREATE INDEX idx_tasks_deleted ON tasks(deleted_at);
--
-- Owned by β. Repository module: src/domains/tasks/repository.ts.
-- State-machine enforcement lives in the tool layer (P0.3.4), not here.

§6. Test matrix — src/__tests__/domains/tasks/repository.test.ts

Minimum 30 tests (Sigma spec says ≥25; we go above to cover the listTasks filter combinatorics). All tests use a helper makeTestDb() that returns a fresh :memory: Database with 002_tasks.sql applied:

function makeTestDb(): Database.Database {
  const db = new Database(':memory:');
  db.pragma('journal_mode = WAL');
  db.pragma('foreign_keys = ON');
  db.exec(readFileSync(MIGRATION_PATH, 'utf-8'));
  return db;
}

MIGRATION_PATH resolved once at module scope via fileURLToPath + relative path. No Windows WAL cleanup needed (:memory: has no disk footprint).

§6a. Describe-block structure

  • describe('createTask') — 6 tests
    • inserts with minimal input (title only), returns task with UUID id
    • default status is ‘INIT’ when not provided
    • preserves all optional fields when provided
    • created_at equals updated_at at creation
    • deleted_at is null at creation
    • generates distinct UUIDs for two back-to-back creates
  • describe('getTask') — 4 tests
    • returns live task by id
    • returns null for unknown id
    • returns null for soft-deleted task
    • returns all fields correctly after create
  • describe('updateTask') — 8 tests
    • applies title-only patch
    • applies multi-field patch
    • empty patch bumps updated_at only
    • nullable fields can be set to null
    • updated_at strictly advances (use monotonic time tick or epsilon check)
    • created_at and id unchanged
    • throws TaskNotFoundError for unknown id (with operation: 'update')
    • throws TaskNotFoundError for soft-deleted id (with operation: 'update')
  • describe('deleteTask') — 5 tests
    • soft-deletes a live task (deleted_at becomes non-null)
    • updated_at bumped on delete
    • throws TaskNotFoundError for unknown id (with operation: 'delete')
    • throws TaskNotFoundError for already-deleted id (with operation: 'delete')
    • getTask returns null after delete
  • describe('listTasks') — 9 tests
    • returns empty array for empty table
    • returns all live tasks (ordered created_at DESC, id DESC)
    • filters by status
    • filters by project_id (non-null)
    • filters by project_id: null (NULL-valued rows)
    • excludes soft-deleted by default
    • includes soft-deleted when include_deleted: true
    • pagination (limit + offset + stable ordering across calls)
    • clamps limit over 500 to 500
  • describe('Task roundtrip') — 3 tests
    • create → get → update → get → delete → get (full CRUD cycle verifies each step’s state)
    • Create 100 tasks, list with pagination (5 pages × 20), verify every unique
    • Stress: create + update + delete interleaved across two project_ids, verify listTasks with project_id filter returns correct subset
  • describe('TaskNotFoundError shape') — 2 tests
    • carries taskId + operation
    • restores prototype chain (instanceof TaskNotFoundError works)

Total: 37 tests. Comfortably above the 25 floor. Target 100% branch coverage on repository.ts.

§6b. updated_at monotonicity hazard

new Date().toISOString() has millisecond precision. Two successive calls within the same millisecond produce the same string. The “strictly advances” invariant must be asserted with >= old, not > old. Tests use an explicit await new Promise(r => setTimeout(r, 2)) before the second write when strict monotonicity is needed.

§6c. foreign_keys = ON with no FK declared

Safe — PRAGMA only enforces declared FKs; our table has none. Verified empirically in P0.2.2 tests (db-init.test.ts creates no tables with FKs and never trips).

§7. Commit plan

Five commits on feature/p0-3-2-task-crud, in canonical 5-step order:

  1. audit(p0-3-2-task-crud): inventory β state-machine + DB + CRUD heritage — already committed at bb0e914c.
  2. contract(p0-3-2-task-crud): behavioral contract — already committed at 507f5a6b.
  3. packet(p0-3-2-task-crud): execution plan — this commit.
  4. feat(p0-3-2-task-crud): β task repository with CRUD + UUID v4 + soft-delete — the four implementation files in a single commit (per P0.2.2 pattern: single feat commit once all four compile-and-pass together).
  5. verify(p0-3-2-task-crud): test evidence — verification doc with coverage numbers.

§8. Risk register

Risk Likelihood Impact Mitigation
SQL CHECK constraint drifts from TASK_STATES Low Medium Single source of truth enforced by a test that reads both TASK_STATES and the migration file’s CHECK clause and asserts they agree.
noUncheckedIndexedAccess type friction on row access Medium Low rowToTask helper concentrates casts in one place.
updated_at test flakiness from ms precision Medium Low Use >= old checks + explicit 2ms sleep where strict advance is needed.
Prepared-statement memoization WeakMap bug Low Medium Covered by every test — any cache miss / wrong SQL surfaces immediately.
Dynamic-SQL injection via patch keys — High Impossible by construction — the dynamic SET builder only iterates over a hardcoded whitelist of column names; patch values are bound as parameters. A test specifically asserts the whitelist is exhaustive.
Parallel Wave D migration collision (003/004) — — Not our concern; Sigma pre-assigned 002.
Cross-worktree file leak (Wave C feedback) Low Low Already verified clean at Step 1. No further parallel waves running against this worktree.
:memory: DB doesn’t exercise disk-path migration runner Low Low P0.2.2 already tests the full on-disk migration runner; P0.3.2 tests the SQL correctness only.
Sigma baseline vs two-axis priority tension Low Low Keep single TEXT column; log the deferral in contract §9 non-goals.

No unresolved risks. Safe to proceed to Step 4.

§9. Acceptance criteria map

From the Sigma prompt, each bullet mapped to the code artefact that satisfies it:

Acceptance bullet Satisfied by
createTask(input): inserts, returns with UUID v4 id §3h(1) + test createTask > inserts with minimal input, returns task with UUID id
getTask(id): returns task or null §3h(2) + tests in describe('getTask')
updateTask(id, patch): partial update; returns updated task §3h(3) + tests in describe('updateTask')
deleteTask(id): soft delete (sets deleted_at) §3h(4) + tests in describe('deleteTask')
listTasks({ status?, project_id?, limit?, offset? }): filtered + paginated §3h(5) + tests in describe('listTasks')
All operations use prepared statements §3g + implicit across all tests (any raw-SQL path would be caught by type errors or test failures)
Test: CRUD roundtrip with all fields describe('Task roundtrip') §6a

All seven acceptance bullets have an explicit home.

§10. Exit condition for Step 3

  • File plan complete (§1).
  • Design decisions locked (§2).
  • Production module structure laid out (§3).
  • Migration SQL body drafted (§4).
  • schema.sql additions drafted (§5).
  • Test matrix covers ≥25 tests with target 100% branch coverage (§6).
  • Commit plan declared (§7).
  • Risks enumerated with mitigations (§8).
  • Every Sigma acceptance bullet mapped to an artefact (§9).

Next step: Step 4 Implement. Single feat commit containing all four files once npm test && npm run lint && npm run build all pass.

Back to top

Colibri — documentation-first MCP runtime. Apache 2.0 + Commons Clause.

This site uses Just the Docs, a documentation theme for Jekyll.