P0.3.2 — Step 2 Contract

Behavioral contract for src/domains/tasks/repository.ts — the dumb-CRUD persistence layer for β tasks. No state-machine enforcement. No MCP surface. No singleton db coupling. Consumed later by P0.3.3 writeback enforcement and P0.3.4 MCP tools.

Canonical sources:

docs/3-world/execution/task-pipeline.md — β semantics + tool-surface boundary
docs/colibri-system.md §6.3 — canonical FSM
docs/reference/extractions/beta-task-pipeline-extraction.md — heritage genealogy (not normative)

Upstream chain artifact:

Audit: docs/audits/p0-3-2-task-crud-audit.md (commit bb0e914c)

§1. Module surface

src/domains/tasks/repository.ts exports exactly the following (no more, no less):

Export	Kind	Shape
`Task`	`interface`	The canonical API row shape
`CreateTaskInput`	`interface`	Required + optional fields accepted by `createTask`
`UpdateTaskPatch`	`interface`	Partial-update shape accepted by `updateTask`
`ListTasksFilter`	`interface`	Filter + pagination args for `listTasks`
`createTask`	`function`	`(db, input) => Task`
`getTask`	`function`	`(db, id) => Task \\| null`
`updateTask`	`function`	`(db, id, patch) => Task`
`deleteTask`	`function`	`(db, id) => Task`
`listTasks`	`function`	`(db, filter?) => Task[]`
`TaskNotFoundError`	`class` (extends `Error`)	Thrown by `updateTask` / `deleteTask` when row missing

No default export. No mutable module state. No top-level side effects (no Database handles opened at import). The module may contain module-scoped const strings holding prepared-statement SQL text and a private helper rowToTask(row) — these are internal.

Parameter order convention: (db, …) — db handle first, matching the pattern that makes testing trivial (pass a fresh :memory: db per test).

§2. Type shapes

§2a. `Task`

export interface Task {
  readonly id: string;              // UUID v4
  readonly project_id: string | null;
  readonly title: string;
  readonly description: string | null;
  readonly status: TaskState;       // one of the 8 β literals
  readonly priority: string | null;
  readonly assignee: string | null;
  readonly created_at: string;      // ISO-8601
  readonly updated_at: string;      // ISO-8601
  readonly deleted_at: string | null; // ISO-8601 when soft-deleted, else null
}

TaskState imported from ./state-machine.js (P0.3.1). readonly on every field — Task objects are immutable snapshots at read time.

§2b. `CreateTaskInput`

export interface CreateTaskInput {
  title: string;                    // required
  status?: TaskState;               // default: 'INIT'
  project_id?: string | null;
  description?: string | null;
  priority?: string | null;
  assignee?: string | null;
}

Only title is required. status defaults to 'INIT' (the canonical entry state). id, created_at, updated_at, deleted_at are never accepted from input — the repository owns those fields.

§2c. `UpdateTaskPatch`

export interface UpdateTaskPatch {
  title?: string;
  status?: TaskState;
  project_id?: string | null;
  description?: string | null;
  priority?: string | null;
  assignee?: string | null;
}

All fields optional. An empty patch is legal (useful for “touch” operations that bump updated_at). updated_at is always bumped on any successful update regardless of patch contents. id, created_at, deleted_at are never accepted from the patch.

§2d. `ListTasksFilter`

export interface ListTasksFilter {
  status?: TaskState;
  project_id?: string | null;
  limit?: number;                   // default: 50, max: 500
  offset?: number;                  // default: 0
  include_deleted?: boolean;        // default: false
}

project_id: null filters for tasks with SQL NULL in the column (no project). project_id: undefined / omitted filters nothing. include_deleted: true includes soft-deleted rows (default behaviour excludes them). Results ordered by created_at DESC, id DESC for stable pagination (the id tiebreaker handles bulk inserts within the same ISO-8601 second).

§3. Function contracts

§3a. `createTask(db, input)`

Purpose: insert a new row into tasks. Assigns a fresh UUID v4. Stamps created_at and updated_at with the current ISO-8601 timestamp. Returns the full persisted Task.

Parameters:

db: Database.Database — live better-sqlite3 handle
input: CreateTaskInput — see §2b

Return: Task — the just-inserted row, re-read via SELECT (so any DB defaults are reflected).

Side effects: one INSERT statement. No errors expected on valid input.

Errors:

SQLite CHECK constraint violation if input.status is not one of the 8 β literals (TypeScript prevents this at compile time, but caller may pass a cast value).
Propagates any lower-level better-sqlite3 error (disk full, locked, etc.) unchanged.

Invariants:

Returned id is a valid UUID v4 (36 chars, 4 hyphens, version-4 bit pattern).
created_at === updated_at immediately after creation.
deleted_at === null.
status defaults to 'INIT' if the input omits it.

§3b. `getTask(db, id)`

Purpose: read a single row by primary key.

Parameters:

db: Database.Database
id: string — UUID (format not validated — caller’s responsibility)

Return: Task | null. null if:

No row has that id.
A row has that id but deleted_at IS NOT NULL (soft-deleted rows are invisible to getTask by default).

Side effects: one SELECT statement. No writes.

Errors: none under normal operation. Propagates lower-level DB errors unchanged.

Invariants:

Two calls with the same id between writes return strictly equal snapshots (same updated_at).
A soft-deleted row remains invisible to getTask — there is no “include deleted” bypass at this layer. Callers who need admin visibility use listTasks({ include_deleted: true }) then filter.

§3c. `updateTask(db, id, patch)`

Purpose: apply a partial update to an existing row.

Parameters:

db: Database.Database
id: string
patch: UpdateTaskPatch — see §2c

Return: Task — the row as it stands after the update (re-read via SELECT).

Side effects: one UPDATE statement (even for an empty patch — updated_at is always bumped).

Errors:

TaskNotFoundError if no row has that id, including if the row exists but is soft-deleted. Soft-deleted rows are not updatable via this function (policy — prevents silent revival). Callers who need to “undelete” must first read via listTasks({ include_deleted: true }) and then issue an explicit statement, or call a future reviveTask (out of scope for P0.3.2).
SQLite CHECK on invalid status (same as createTask).

Invariants:

updated_at on the returned Task is strictly later than or equal to the pre-update updated_at (same-millisecond edits can tie).
created_at is unchanged.
id is unchanged.
deleted_at is unchanged (null if the row was live; update of a soft-deleted row throws).
Fields not mentioned in the patch are unchanged.
Fields set to null in the patch become NULL in the DB (not “unchanged”). The distinction between “field omitted” and “field: null” is load-bearing.
No FSM validation. Passing status: 'DONE' on a task currently in 'INIT' will silently persist the jump. The repository is a dumb layer. FSM enforcement is P0.3.4.

§3d. `deleteTask(db, id)`

Purpose: soft-delete an existing row. Sets deleted_at to the current ISO-8601 timestamp; updated_at is also bumped.

Parameters:

db: Database.Database
id: string

Return: Task — the soft-deleted row (deleted_at !== null).

Side effects: one UPDATE statement.

Errors:

TaskNotFoundError if no row has that id, or the row exists but is already soft-deleted (idempotent-reject policy — re-deleting an already-deleted row is a programming error, not a silent no-op).

Invariants:

Returned deleted_at is a valid ISO-8601 string.
created_at, id, title, etc. are unchanged.
After deleteTask, getTask(id) returns null.
After deleteTask, listTasks() excludes this row by default.

§3e. `listTasks(db, filter?)`

Purpose: paginated, filtered list of live (or all, if include_deleted) tasks.

Parameters:

db: Database.Database
filter?: ListTasksFilter — defaults apply per field (see §2d)

Return: Task[] — may be empty.

Side effects: one SELECT statement. No writes.

Errors: none under normal operation. limit > 500 is silently clamped to 500 (defensive — prevents DoS-ish callers from blowing memory).

Invariants:

Results ordered created_at DESC, id DESC (newest first).
length <= filter.limit ?? 50.
Default filter (undefined or {}) excludes soft-deleted rows.
With include_deleted: true, results interleave live and deleted rows sorted by created_at DESC.
Pagination is offset-based (known limitation — cursor-based pagination is a future enhancement, not needed for Phase 0 where task counts are tiny).

§4. Error model

TaskNotFoundError extends Error carries structured fields { taskId, operation }:

export class TaskNotFoundError extends Error {
  override readonly name = 'TaskNotFoundError';
  readonly taskId: string;
  readonly operation: 'update' | 'delete';
  constructor(args: { taskId: string; operation: 'update' | 'delete' }) {
    super(`Task not found: ${args.taskId} (operation: ${args.operation})`);
    this.taskId = args.taskId;
    this.operation = args.operation;
    Object.setPrototypeOf(this, TaskNotFoundError.prototype);
  }
}

The operation field distinguishes “tried to update a missing/deleted task” vs. “tried to delete a missing/already-deleted task” at the tool layer (P0.3.4 maps these to different s17 error envelopes).

getTask and listTasks do not throw on missing data — they return null / []. Only write operations (updateTask, deleteTask) throw TaskNotFoundError.

createTask never throws TaskNotFoundError (nothing to look up).

Lower-level errors (disk full, SQLite integrity, etc.) propagate unchanged as their native better-sqlite3 Error / SqliteError instances. The repository does not wrap or rename them.

§5. Invariants (module-wide)

§5a. Persistence

All writes use better-sqlite3 prepared statements. No string interpolation of user-provided values into SQL text.
Prepared statements are constructed lazily at first use (not at module load) so importing the module does not require an open Database. They are memoized per Database instance via a WeakMap<Database, { create: Statement; get: Statement; … }> to avoid repeated .prepare() calls on the hot path.

§5b. Soft-delete discipline

getTask and default listTasks ignore soft-deleted rows.
updateTask and deleteTask on soft-deleted rows throw TaskNotFoundError.
Only listTasks({ include_deleted: true }) sees soft-deleted rows.

§5c. Time

All timestamps are ISO-8601 UTC strings from new Date().toISOString().
created_at is stamped once at insert and never modified by any other operation.
updated_at is bumped on every updateTask and deleteTask.
deleted_at is set exactly once by deleteTask and never modified elsewhere.
No time source other than new Date() — no injectable clock in P0.3.2. Tests use real wall time.

§5d. Identity

id is generated exactly once per Task by createTask via crypto.randomUUID().
id is never mutated by any operation.
id is the primary key; any attempt to insert a colliding id propagates the SQLite unique-constraint error.

§5e. Status field

Stored as raw TEXT with a SQL CHECK (status IN (…8 values…)) constraint.
Reads cast the TEXT back to TaskState type (safe because the CHECK guarantees the value is one of the 8 literals).
Write operations accept any TaskState — including illegal forward/backward jumps. FSM enforcement is not this layer’s job.

§5f. Purity

No console.log, no console.error, no stderr writes, no stdout writes. Errors throw; callers log.
No process.exit, no setTimeout, no timers.
No HTTP, no fetch, no MCP, no file I/O beyond what better-sqlite3 does internally.
No reading environment variables.
No reading getDb() from src/db/index.ts — the db: Database.Database parameter is the sole DB-handle source.

§6. Migration contract

src/db/migrations/002_tasks.sql creates the tasks table and two indexes in a single migration. The migration runner (P0.2.2 src/db/index.ts) wraps the entire file body in one transaction and bumps user_version from 1 to 2 atomically.

§6a. Table columns (authoritative)

Column	Type	Constraints
`id`	TEXT	PRIMARY KEY
`project_id`	TEXT	— (nullable, no FK)
`title`	TEXT	NOT NULL
`description`	TEXT	— (nullable)
`status`	TEXT	NOT NULL, `CHECK (status IN ('INIT','GATHER','ANALYZE','PLAN','APPLY','VERIFY','DONE','CANCELLED'))`
`priority`	TEXT	— (nullable)
`assignee`	TEXT	— (nullable)
`created_at`	TEXT	NOT NULL
`updated_at`	TEXT	NOT NULL
`deleted_at`	TEXT	— (nullable)

§6b. Indexes

idx_tasks_project_status on (project_id, status, deleted_at) — serves listTasks({ project_id, status }) with soft-delete exclusion on the same index.
idx_tasks_deleted on (deleted_at) — serves unfiltered listTasks() default path (soft-delete exclusion always applies).

No unique constraints beyond the PRIMARY KEY.

§6c. Post-migration `user_version`

After 002_tasks.sql applies, PRAGMA user_version returns 2. 001_init.sql bumped it to 1; this migration bumps to 2.

§7. `schema.sql` contract

src/db/schema.sql is extended with a tasks ownership block, commented only. It is still a shipped-asset reference, not executed SQL. The block documents:

The owning concept (β Task Pipeline).
The owning task (P0.3.2).
The migration file (002_tasks.sql).
A CREATE-TABLE hint (so a human reading schema.sql can see the shape without opening the migration).

The file’s top-level header contract (from P0.2.2) is preserved: α owns the file, concept migrations earn the tables.

§8. Coverage posture

Target: 100% branch coverage on repository.ts. All branches:

createTask: default-status fallback (input.status ?? 'INIT') → 2 branches.
getTask: row found vs not found vs soft-deleted → 3 branches (returns null for two of them).
updateTask: row missing, row soft-deleted (throws), partial patch (every field optional), empty patch → ~8 branches.
deleteTask: row missing, row already soft-deleted, live row → 3 branches.
listTasks: every optional filter field absent / present, include_deleted true/false, limit clamping → ~10 branches.

Estimated total: ~26 branches. Test count target: 30+ (each branch exercised plus CRUD roundtrip + soft-delete visibility + pagination edge cases).

Coverage enforcement level: verification-phase check (Jest coverage text report + npm test exit code). Not a CI-level gate in P0.3.2 (no coverage threshold is configured in jest.config.ts; that’s a later tightening task).

§9. Non-goals

No reviveTask / un-delete.
No bulkCreate / bulkUpdate.
No countTasks (the length of listTasks suffices for Phase 0).
No dependency tracking (deferred to P0.3.4 tool-layer).
No Eisenhower two-axis priority columns.
No MCP tool surface.
No FSM enforcement.
No caller-provided id in createTask input.
No injectable clock (real new Date() only).
No streaming / async iterator for listTasks.

§10. Exit condition for Step 2

Every exported symbol listed (§1).
Every type shape fully specified (§2).
Every function: purpose, params, return, side effects, errors, invariants (§3).
Error model defined (§4).
Module-wide invariants catalogued (§5).
Migration and schema.sql contracts locked (§6, §7).
Coverage target declared (§8).
Non-goals enumerated (§9).

Next step: Step 3 Packet translates this contract into a concrete file plan, SQL bodies, test matrix, and risk register.

P0.3.2 — Step 2 Contract

§1. Module surface

§2. Type shapes

§2a. Task

§2b. CreateTaskInput

§2c. UpdateTaskPatch

§2d. ListTasksFilter

§3. Function contracts

§3a. createTask(db, input)

§3b. getTask(db, id)

§3c. updateTask(db, id, patch)

§3d. deleteTask(db, id)

§3e. listTasks(db, filter?)

§4. Error model

§5. Invariants (module-wide)

§5a. Persistence

§5b. Soft-delete discipline

§5c. Time

§5d. Identity

§5e. Status field

§5f. Purity

§6. Migration contract

§6a. Table columns (authoritative)

§6b. Indexes

§6c. Post-migration user_version

§7. schema.sql contract

§8. Coverage posture

§9. Non-goals

§10. Exit condition for Step 2

§2a. `Task`

§2b. `CreateTaskInput`

§2c. `UpdateTaskPatch`

§2d. `ListTasksFilter`

§3a. `createTask(db, input)`

§3b. `getTask(db, id)`

§3c. `updateTask(db, id, patch)`

§3d. `deleteTask(db, id)`

§3e. `listTasks(db, filter?)`

§6c. Post-migration `user_version`

§7. `schema.sql` contract