P0.3.2 — Step 1 Audit

Inventory of the worktree against the task spec for P0.3.2 β Task CRUD (second task in the β Task Pipeline group). Scope: the repository surface being added (src/domains/tasks/repository.ts + migration 002_tasks.sql + tests), the adjacent code it must consume (P0.3.1 state-machine, P0.2.2 DB init), the canonical β column set, and the MCP-surface boundary (what this task does NOT do).

Baseline: worktree E:/AMS/.worktrees/claude/p0-3-2-task-crud/ at commit 6c26bb58 (P0.7.1 ζ trail merged, all Wave C complete).

§1. Surface being added

Target: src/domains/tasks/repository.ts — a new dumb-CRUD repository module. Companion test: src/__tests__/domains/tasks/repository.test.ts (Sigma-approved deviation from spec’s tests/domains/tasks/repository.test.ts because jest.config.ts line 15 pins roots: ['<rootDir>/src']; all prior Phase 0 tests use src/__tests__/**).

Migration: src/db/migrations/002_tasks.sql — the first earned table under α’s empty schema slot. Number 002 is Sigma-locked (003_skills.sql is reserved for P0.6.2, 004_thought_records.sql for P0.7.2, both running in parallel waves).

Sibling edit: src/db/schema.sql — currently a comment-only asset (per P0.2.2); extended with a tasks ownership block for human-readable documentation. Stays in sync with the migration body.

Worktree scan confirms greenfield for the repository module:

  • ls src/domains/tasks/repository.ts → does not exist
  • ls src/db/migrations/002_tasks.sql → does not exist
  • src/db/schema.sql — exists, header-only (no SQL body)
  • src/domains/tasks/state-machine.ts — exists (P0.3.1, 197 LOC)

No other module references tasks as a SQL table yet (grep -rn "FROM tasks\|INTO tasks\|UPDATE tasks" src/ returns zero hits), so the repository is the sole owner of the table surface introduced here.

§2. Upstream — what this task consumes

§2a. P0.3.1 β state-machine (src/domains/tasks/state-machine.ts)

Provides the canonical 8-state tuple TASK_STATES and the TaskState union type. The repository imports TaskState from this module for its column typing and uses TASK_STATES as the source of the SQL CHECK constraint values.

Relevant exports (from state-machine.ts lines 43-58):

export const TASK_STATES = [
  'INIT', 'GATHER', 'ANALYZE', 'PLAN', 'APPLY', 'VERIFY', 'DONE', 'CANCELLED',
] as const;
export type TaskState = (typeof TASK_STATES)[number];

The repository does NOT import transition, canTransition, or InvalidTransitionError. The Sigma spec is explicit: “Status transitions are enforced by the state-machine module (P0.3.1), not by the repository. updateTask may change status without validation (the repository is a dumb CRUD layer); state-machine enforcement lives in the tool layer (P0.3.4).” The repository is a persistence boundary; FSM enforcement is a tool-layer concern scheduled for P0.3.4.

§2b. P0.2.2 α SQLite init (src/db/index.ts)

Provides the Database.Database type (from better-sqlite3), the migration runner, and the singleton getDb(). The repository module takes the db handle as a function argument rather than importing getDb() directly — this matches the pattern in state-machine.ts (pure inputs, no module state) and lets tests use an in-memory database without singleton interaction. The migration runner (lines 254-279) is already wired to discover 002_tasks.sql by its numeric prefix and apply it inside a single transaction bumping user_version from 1 to 2.

Pragmas already set by initDb before any migration runs (lines 244-245):

  • journal_mode = WAL
  • foreign_keys = ON

This means the tasks table migration runs inside a FK-enforcing transaction. Per the Sigma spec deviation #4, project_id is a plain TEXT column with no FK to a projects table (that table does not exist in Phase 0 and would cause the migration to fail under FK enforcement if declared).

§2c. Empty migration slot (src/db/migrations/001_init.sql)

Confirms that migration number 002 is the next available slot. The P0.2.2 schema.sql ownership map (lines 7-11) explicitly reserves:

P0.3  β Task Pipeline     → migrations/002_beta.sql     (tasks, task_dependencies)

Sigma-approved deviation: filename is 002_tasks.sql, not 002_beta.sql. This is a presentation choice (match the concept table name rather than the Greek letter); the migration runner only reads the numeric prefix, so the filename body is cosmetic.

§2d. Canonical β doc (docs/3-world/execution/task-pipeline.md)

Provides state semantics (lines 24-33), writeback contract (lines 60-82), and — critically — the tool-surface boundary (lines 84-90):

task_create, task_list, task_get, task_update, task_cancel, task_next_actions, task_link, task_unlink.

No task_delete — terminal states are immutable except through the decision-trail record. A task can be CANCELLED but not erased.

Reconciliation with this task’s deleteTask(id): the task spec mandates a deleteTask(id) repository function that soft-deletes (sets deleted_at). The canonical β doc’s rule is about the MCP tool surface — there is no task_delete tool in the 8-tool β surface. A soft-delete repository function exposed only to the tool layer is consistent: the tool layer chooses whether to surface it, and per canon, it does NOT. P0.3.4 implements only the 8 enumerated tools. Soft-delete is still useful inside the repository for test fixtures, admin ops, and future internal cleanup paths. No contradiction.

§2e. β heritage extraction (docs/reference/extractions/beta-task-pipeline-extraction.md)

Marked HERITAGE at the head (“Phase 0 Colibri β is an 8-state FSM… There is no src/gsd/ directory and no donor RETRY edge in Phase 0”). Read as genealogy only. Relevant to the repository: the AMS donor used a kanban vocabulary (backlog | todo | in_progress | blocked | review | done | cancelled) — Phase 0 β uses the 8-state canonical FSM. The repository’s status column stores only canonical TaskState values; any kanban-lifecycle mapping is ν Integration territory (not this task).

§3. Column set — baseline reconciliation

The Sigma-specified baseline columns (deviation #4) align with the canonical β semantics table and the common CRUD columns used across all Phase 0 earned tables. Full column list with types and nullability:

Column Type Null? Default Source
id TEXT NOT NULL (PK) Sigma baseline, UUID v4 via crypto.randomUUID()
project_id TEXT NULL — Sigma baseline (no FK — no projects table in Phase 0)
title TEXT NOT NULL — Sigma baseline
description TEXT NULL — Sigma baseline
status TEXT NOT NULL — Sigma baseline, CHECK (status IN (…8 TASK_STATES…))
priority TEXT NULL — Sigma baseline
assignee TEXT NULL — Sigma baseline
created_at TEXT NOT NULL — Sigma baseline, ISO-8601
updated_at TEXT NOT NULL — Sigma baseline, ISO-8601
deleted_at TEXT NULL — Sigma baseline (soft-delete)

Indexes (both from Sigma baseline):

  • idx_tasks_project_status on (project_id, status, deleted_at) — supports listTasks({ project_id, status }) filter with soft-delete exclusion.
  • idx_tasks_deleted on (deleted_at) — supports unfiltered listTasks() fast path (soft-delete exclusion is always applied).

The canonical β doc §”Priority” (task-pipeline.md lines 42-44) describes a two-axis priority (urgency + importance, each 0-3) — a more refined scheme than the Sigma-baseline single-TEXT priority column. Sigma-approved deviation #4 explicitly says “extend if canonical β doc requires”; the question is whether to split priority into urgency + importance columns now or keep the single TEXT column.

Decision (locked in the packet, not here): keep the single-TEXT priority column for P0.3.2. The two-axis scheme is a scheduler concern, not a persistence-shape concern; storing it as a TEXT value like "urgent_important" or a small JSON object is compatible with both the current spec and a future migration that splits it into numeric columns. Deferring keeps the P0.3.2 schema narrow and aligns with the CHECK (status IN …) pattern — plain strings, no JSON. If the scheduler (P0.3.3 / P0.3.4) surfaces a real need, a 003_tasks_priority.sql migration can add urgency INTEGER + importance INTEGER without breaking existing rows (default NULL, backfill via ν Integrations if ever needed).

No other columns from the canonical doc demand first-class storage in P0.3.2. attempts / retry_count is a VERIFY-stage bookkeeping field; the state-machine contract (line 87-90) explicitly says “bookkeeping is the caller’s job”. Not a Phase 0 P0.3.2 column.

§4. Function surface — baseline vs canonical

Sigma-baseline functions (from the task prompt acceptance criteria):

Fn Signature (informal) Returns
createTask (input: CreateInput, db: Database) → Task new Task with fresh UUID
getTask (id: string, db: Database) → Task \| null null if not found or soft-deleted
updateTask (id: string, patch: UpdatePatch, db: Database) → Task updated Task
deleteTask (id: string, db: Database) → Task Task with deleted_at set
listTasks (filter: ListFilter, db: Database) → Task[] filtered + paginated list

All 5 use better-sqlite3 prepared statements (Sigma deviation #4 acceptance bullet). No string interpolation in WHERE clauses. Prepared statements are module-level constants built once at import time, matching the better-sqlite3 best practice for hot-path queries.

TaskRow (DB shape) vs Task (API shape): SQLite has no native boolean or null-typed distinction in TEXT columns. deleted_at is either a real ISO-8601 timestamp or SQL NULL. The API shape exposes it as string | null. The repository module converts between the two at the boundary.

§5. Test surface — prior-art patterns

Prior test files this module should mirror:

  • src/__tests__/task-state-machine.test.ts (P0.3.1, 182 tests) — pattern: describe-block per export, table-driven via it.each, explicit edge enumeration. Repository tests should use the same describe structure per exported function.
  • src/__tests__/db-init.test.ts (P0.2.2, 25 tests) — pattern: makeTempDbPath() returns a unique os.tmpdir() path per test; afterEach closes the singleton and rmsyncs directories with a swallow on Windows file locks. Repository tests use in-memory DB (:memory: passed to new Database) rather than tmpdir files because the repository takes a db handle as an argument — no singleton interaction needed, no disk I/O, and Windows file-lock hazard (P0.2.2 afterEach swallow comment) is avoided entirely.

In-memory DB construction path for repository tests:

import Database from 'better-sqlite3';
import { readFileSync } from 'node:fs';
import { fileURLToPath } from 'node:url';
import { dirname, join } from 'node:path';

function makeTestDb(): Database.Database {
  const db = new Database(':memory:');
  db.pragma('journal_mode = WAL');
  db.pragma('foreign_keys = ON');
  const migrationPath = join(
    dirname(fileURLToPath(import.meta.url)),
    '../../../db/migrations/002_tasks.sql',
  );
  db.exec(readFileSync(migrationPath, 'utf-8'));
  return db;
}

This pattern skips the P0.2.2 migration runner (which requires a real file path and singleton) and applies the 002_tasks.sql body directly — simpler, hermetic, faster.

Acceptance criteria mandates ≥25 tests (Sigma packet). Expected distribution:

Method Happy Edge Error
createTask 2 2 1
getTask 2 2 —
updateTask 2 3 1
deleteTask 1 2 —
listTasks 2 4 —
Cross-op roundtrip 2 — —

Total: 26 tests minimum. Actual count likely higher once the packet finalizes the matrix.

§6. Non-goals (explicitly excluded)

From the Sigma prompt, items NOT in this task:

  1. No MCP tool registration. task_create / task_list / task_get / task_update / task_cancel / task_next_actions / task_link / task_unlink are all P0.3.4. Do not edit src/server.ts.
  2. No state-machine enforcement. updateTask accepts any TaskState for status. The tool layer (P0.3.4) wraps updateTask with canTransition / transition calls.
  3. No FK to a projects table. project_id is a plain TEXT column. No projects migration in this task.
  4. No task_dependencies table. The schema.sql ownership map mentions (tasks, task_dependencies) for migration 002, but dependencies are P0.3.4 (tool-level) — kept out of this migration to keep the scope narrow.
  5. No two-axis priority. Single-TEXT priority column (see §3).
  6. No writeback enforcement. That’s P0.3.3.
  7. No direct getDb() singleton binding. The repository accepts a db: Database parameter for testability.

§7. Deviations from the Sigma baseline

None at the audit stage beyond those already locked in the Sigma prompt. The packet (Step 3) confirms all six deviations before implement.

# Deviation Source
1 Test path src/__tests__/domains/tasks/repository.test.ts (not tests/domains/tasks/…) Sigma prompt / jest.config.ts roots
2 Migration 002_tasks.sql (not 002_beta.sql) Sigma prompt (presentation only)
3 UUID v4 via crypto.randomUUID() Sigma prompt
4 Tasks-table baseline columns (see §3) Sigma prompt
5 No FSM enforcement in updateTask Sigma prompt
6 schema.sql extended with tasks ownership block Sigma prompt

All six are Sigma-approved at dispatch time; the packet rewrites them as locked design decisions.

§8. Risks and open questions

Risk Mitigation
Migration FK on non-existent projects table Use plain TEXT column, no REFERENCES. Already locked.
Parallel Wave D migration collision (003/004) Not our problem — Sigma assigned 002 exclusively. Other waves own 003_skills.sql / 004_thought_records.sql.
better-sqlite3.exec rejects empty body (P0.2.2 bug #4) Not applicable — 002_tasks.sql has a non-empty body.
Windows WAL file locks in tests Avoided by using :memory: DB in tests.
Cross-worktree file leak (Wave C P0.7.1 feedback) Step 1 pre-clean passed (git status clean). No uncommitted leaks from parallel waves.
PRAGMA foreign_keys = ON with no REFERENCES declared Safe — foreign_keys=ON only enforces declared FKs; it doesn’t require any.
noUncheckedIndexedAccess tripping prepared-statement all() / get() Type-cast return rows via a private asTask(row) helper.

No blockers for Step 2 (Contract).

§9. Pre-clean verification

Per the Sigma prompt “Step 1 pre-clean rule (MANDATORY — Wave C feedback)”:

$ git status
On branch feature/p0-3-2-task-crud
Your branch is up to date with 'origin/main'.
nothing to commit, working tree clean

No cross-worktree leaks. No src/server.ts / src/startup.ts modifications visible. Safe to proceed with Step 2.

§10. Exit condition for Step 1

  • Baseline commit identified (6c26bb58).
  • Greenfield surface confirmed (repository.ts, 002_tasks.sql both absent).
  • Upstream dependencies inventoried (P0.3.1 state-machine, P0.2.2 DB init).
  • Canonical column set reconciled against β spec (§3).
  • MCP-surface boundary documented (§6).
  • deleteTask ↔ “no task_delete tool” reconciliation established (§2d).
  • Test-pattern prior art identified (§5).
  • Risks catalogued (§8).
  • Pre-clean clean (§9).

Next step: Step 2 Contract formalizes the exported surface, error modes, and invariants.

Back to top

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

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