Lakeland Timesheet Implementation Plan

For Claude: REQUIRED SUB-SKILL: Use superpowers:executing-plans to implement this plan task-by-task.

Goal: Build a Slack timesheet app on Val Town for interns to log hours and managers to run reports.

Architecture: Single HTTP val as request router + cron val for weekly reminders. Pure TypeScript business logic tested locally with Vitest; Val Town SQLite for persistence; Slack Block Kit for UI.

Tech Stack: Val Town (HTTP + cron vals), TypeScript, Vitest, Val Town SQLite (@libsql/client), Slack Block Kit, vt CLI for local dev.

TDD approach: All business logic is pure functions — tested locally with Vitest. Platform boundaries (SQLite, Slack API) are injected as dependencies so tests use in-memory fakes.

Development Environment

Val Town CLI (vt):

deno install -grAf jsr:@valtown/vt
vt              # authenticate with API key from val.town/settings/api

Val Town MCP (for Claude Code):

claude mcp add --transport http val-town https://api.val.town/v3/mcp

Local testing: Vitest runs against pure business logic. No Val Town runtime needed for tests.

Task 1: Project Scaffold & Tooling

Files:

Create: package.json
Create: tsconfig.json
Create: vitest.config.ts
Create: .gitignore
Create: src/db/types.ts

Step 1: Initialize npm project

cd /Users/trevormunoz/Code/lakeland-timesheet
npm init -y

Step 2: Install dev dependencies

npm install -D vitest typescript @libsql/client

Note: @libsql/client is both a dev dependency (for types in tests) and the runtime import on Val Town. Val Town provides it at https://esm.town/v/std/sqlite.

Step 3: Create tsconfig.json

{
  "compilerOptions": {
    "target": "ES2022",
    "module": "ES2022",
    "moduleResolution": "bundler",
    "strict": true,
    "esModuleInterop": true,
    "outDir": "dist",
    "rootDir": "src",
    "declaration": true,
    "sourceMap": true,
    "forceConsistentCasingInFileNames": true,
    "skipLibCheck": true
  },
  "include": ["src/**/*"],
  "exclude": ["node_modules", "dist"]
}

Step 4: Create vitest.config.ts

import { defineConfig } from "vitest/config";

export default defineConfig({
  test: {
    include: ["src/**/*.test.ts"],
  },
});

Step 5: Create .gitignore

node_modules/
dist/
.env

Step 6: Create the database abstraction type

This is the key to testability. All database operations go through this interface — Val Town SQLite in production, an in-memory fake in tests.

// src/db/types.ts
export interface DbResult {
  columns: string[];
  rows: unknown[][];
  rowsAffected: number;
  lastInsertRowid: bigint | number;
}

export interface Database {
  execute(query: { sql: string; args?: unknown[] }): Promise<DbResult>;
  batch(queries: Array<string | { sql: string; args?: unknown[] }>): Promise<DbResult[]>;
}

Step 7: Add test script to package.json

Add to scripts: "test": "vitest", "test:run": "vitest run"

Step 8: Commit

git add package.json tsconfig.json vitest.config.ts .gitignore src/db/types.ts
git commit -m "chore: project scaffold with Vitest and database abstraction"

Task 2: Database Schema & Migration

Files:

Create: src/db/schema.ts
Create: src/db/schema.test.ts
Create: src/db/test-helpers.ts

Step 1: Create the test helper — in-memory SQLite fake

// src/db/test-helpers.ts
import { Database as LibSqlDatabase, createClient } from "@libsql/client";
import type { Database, DbResult } from "./types.js";

/**
 * Creates an in-memory SQLite database for testing.
 * Uses @libsql/client with file::memory: URL.
 */
export async function createTestDb(): Promise<Database> {
  const client = createClient({ url: "file::memory:" });
  return {
    async execute(query) {
      const result = await client.execute(query);
      return {
        columns: result.columns,
        rows: result.rows.map((r) => Object.values(r)),
        rowsAffected: result.rowsAffected,
        lastInsertRowid: result.lastInsertRowid ?? 0,
      };
    },
    async batch(queries) {
      const results = await client.batch(
        queries.map((q) => (typeof q === "string" ? q : q))
      );
      return results.map((result) => ({
        columns: result.columns,
        rows: result.rows.map((r) => Object.values(r)),
        rowsAffected: result.rowsAffected,
        lastInsertRowid: result.lastInsertRowid ?? 0,
      }));
    },
  };
}

Step 2: Write the failing test

// src/db/schema.test.ts
import { describe, it, expect, beforeEach } from "vitest";
import { createTestDb } from "./test-helpers.js";
import { migrate } from "./schema.js";
import type { Database } from "./types.js";

describe("schema migration", () => {
  let db: Database;

  beforeEach(async () => {
    db = await createTestDb();
  });

  it("creates all required tables", async () => {
    await migrate(db);

    const result = await db.execute({
      sql: "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name",
    });
    const tables = result.rows.map((r) => r[0]);

    expect(tables).toContain("projects");
    expect(tables).toContain("grants");
    expect(tables).toContain("task_types");
    expect(tables).toContain("time_entries");
    expect(tables).toContain("interns");
    expect(tables).toContain("config");
  });

  it("is idempotent — running twice does not error", async () => {
    await migrate(db);
    await expect(migrate(db)).resolves.not.toThrow();
  });
});

Step 3: Run test to verify it fails

Run: npx vitest run src/db/schema.test.ts Expected: FAIL — migrate is not exported from ./schema.js

Step 4: Implement schema.ts

// src/db/schema.ts
import type { Database } from "./types.js";

const SCHEMA = [
  `CREATE TABLE IF NOT EXISTS projects (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    active INTEGER NOT NULL DEFAULT 1
  )`,
  `CREATE TABLE IF NOT EXISTS grants (
    id INTEGER PRIMARY KEY,
    code TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    active INTEGER NOT NULL DEFAULT 1
  )`,
  `CREATE TABLE IF NOT EXISTS task_types (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    active INTEGER NOT NULL DEFAULT 1
  )`,
  `CREATE TABLE IF NOT EXISTS time_entries (
    id INTEGER PRIMARY KEY,
    slack_user_id TEXT NOT NULL,
    slack_username TEXT NOT NULL,
    date TEXT NOT NULL,
    minutes INTEGER NOT NULL,
    project_id INTEGER REFERENCES projects(id),
    grant_id INTEGER REFERENCES grants(id),
    task_type_id INTEGER NOT NULL REFERENCES task_types(id),
    comment TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    deleted_at TEXT
  )`,
  `CREATE TABLE IF NOT EXISTS interns (
    slack_user_id TEXT PRIMARY KEY,
    slack_username TEXT NOT NULL,
    added_at TEXT NOT NULL DEFAULT (datetime('now'))
  )`,
  `CREATE TABLE IF NOT EXISTS config (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL
  )`,
];

export async function migrate(db: Database): Promise<void> {
  await db.batch(SCHEMA);
}

Step 5: Run test to verify it passes

Run: npx vitest run src/db/schema.test.ts Expected: PASS (2 tests)

Step 6: Commit

git add src/db/
git commit -m "feat(db): schema migration with all tables"

Task 3: Database Queries — Lookup Table CRUD

Files:

Create: src/db/queries.ts
Create: src/db/queries.test.ts

Step 1: Write failing tests for lookup CRUD

// src/db/queries.test.ts
import { describe, it, expect, beforeEach } from "vitest";
import { createTestDb } from "./test-helpers.js";
import { migrate } from "./schema.js";
import {
  addProject,
  addGrant,
  addTaskType,
  getActiveProjects,
  getActiveGrants,
  getActiveTaskTypes,
  deactivateProject,
  deactivateGrant,
  deactivateTaskType,
} from "./queries.js";
import type { Database } from "./types.js";

describe("lookup table CRUD", () => {
  let db: Database;

  beforeEach(async () => {
    db = await createTestDb();
    await migrate(db);
  });

  describe("projects", () => {
    it("adds and retrieves active projects", async () => {
      await addProject(db, "Lakeland Digital Archive");
      await addProject(db, "Community Oral Histories");

      const projects = await getActiveProjects(db);
      expect(projects).toHaveLength(2);
      expect(projects[0].name).toBe("Community Oral Histories");
      expect(projects[1].name).toBe("Lakeland Digital Archive");
    });

    it("deactivates a project without deleting it", async () => {
      await addProject(db, "Old Project");
      await deactivateProject(db, "Old Project");

      const projects = await getActiveProjects(db);
      expect(projects).toHaveLength(0);
    });
  });

  describe("grants", () => {
    it("adds and retrieves active grants", async () => {
      await addGrant(db, "NEH-PW-290261", "NEH Preservation & Access");

      const grants = await getActiveGrants(db);
      expect(grants).toHaveLength(1);
      expect(grants[0].code).toBe("NEH-PW-290261");
      expect(grants[0].name).toBe("NEH Preservation & Access");
    });

    it("deactivates a grant", async () => {
      await addGrant(db, "NEH-PW-290261", "NEH Preservation");
      await deactivateGrant(db, "NEH-PW-290261");

      expect(await getActiveGrants(db)).toHaveLength(0);
    });
  });

  describe("task types", () => {
    it("adds and retrieves active task types", async () => {
      await addTaskType(db, "Transcription");
      await addTaskType(db, "Metadata Editing");

      const types = await getActiveTaskTypes(db);
      expect(types).toHaveLength(2);
    });

    it("deactivates a task type", async () => {
      await addTaskType(db, "Scanning");
      await deactivateTaskType(db, "Scanning");

      expect(await getActiveTaskTypes(db)).toHaveLength(0);
    });
  });
});

Step 2: Run test to verify it fails

Run: npx vitest run src/db/queries.test.ts Expected: FAIL — functions not exported

Step 3: Implement lookup CRUD in queries.ts

// src/db/queries.ts
import type { Database } from "./types.js";

// --- Lookup types ---

export interface Project {
  id: number;
  name: string;
}

export interface Grant {
  id: number;
  code: string;
  name: string;
}

export interface TaskType {
  id: number;
  name: string;
}

// --- Projects ---

export async function addProject(db: Database, name: string): Promise<void> {
  await db.execute({ sql: "INSERT INTO projects (name) VALUES (?)", args: [name] });
}

export async function getActiveProjects(db: Database): Promise<Project[]> {
  const result = await db.execute({
    sql: "SELECT id, name FROM projects WHERE active = 1 ORDER BY name",
  });
  return result.rows.map((r) => ({ id: r[0] as number, name: r[1] as string }));
}

export async function deactivateProject(db: Database, name: string): Promise<void> {
  await db.execute({ sql: "UPDATE projects SET active = 0 WHERE name = ?", args: [name] });
}

// --- Grants ---

export async function addGrant(db: Database, code: string, name: string): Promise<void> {
  await db.execute({ sql: "INSERT INTO grants (code, name) VALUES (?, ?)", args: [code, name] });
}

export async function getActiveGrants(db: Database): Promise<Grant[]> {
  const result = await db.execute({
    sql: "SELECT id, code, name FROM grants WHERE active = 1 ORDER BY code",
  });
  return result.rows.map((r) => ({ id: r[0] as number, code: r[1] as string, name: r[2] as string }));
}

export async function deactivateGrant(db: Database, code: string): Promise<void> {
  await db.execute({ sql: "UPDATE grants SET active = 0 WHERE code = ?", args: [code] });
}

// --- Task Types ---

export async function addTaskType(db: Database, name: string): Promise<void> {
  await db.execute({ sql: "INSERT INTO task_types (name) VALUES (?)", args: [name] });
}

export async function getActiveTaskTypes(db: Database): Promise<TaskType[]> {
  const result = await db.execute({
    sql: "SELECT id, name FROM task_types WHERE active = 1 ORDER BY name",
  });
  return result.rows.map((r) => ({ id: r[0] as number, name: r[1] as string }));
}

export async function deactivateTaskType(db: Database, name: string): Promise<void> {
  await db.execute({ sql: "UPDATE task_types SET active = 0 WHERE name = ?", args: [name] });
}

Step 4: Run test to verify it passes

Run: npx vitest run src/db/queries.test.ts Expected: PASS (6 tests)

Step 5: Commit

git add src/db/queries.ts src/db/queries.test.ts
git commit -m "feat(db): lookup table CRUD for projects, grants, task types"

Task 4: Database Queries — Time Entries

Files:

Modify: src/db/queries.ts
Modify: src/db/queries.test.ts

Step 1: Write failing tests for time entry operations

Add to src/db/queries.test.ts:

import {
  // ... existing imports ...
  createTimeEntry,
  updateTimeEntry,
  softDeleteTimeEntry,
  getRecentEntries,
  getTimeEntryById,
} from "./queries.js";

describe("time entries", () => {
  let db: Database;

  beforeEach(async () => {
    db = await createTestDb();
    await migrate(db);
    await addTaskType(db, "Transcription");
    await addProject(db, "Lakeland");
    await addGrant(db, "NEH-PW-290261", "NEH");
  });

  it("creates a time entry and retrieves it", async () => {
    const id = await createTimeEntry(db, {
      slackUserId: "U123",
      slackUsername: "maria",
      date: "2026-02-26",
      minutes: 60,
      taskTypeId: 1,
      projectId: 1,
      grantId: 1,
      comment: "Worked on interviews",
    });

    const entry = await getTimeEntryById(db, id);
    expect(entry).not.toBeNull();
    expect(entry!.minutes).toBe(60);
    expect(entry!.slackUsername).toBe("maria");
    expect(entry!.comment).toBe("Worked on interviews");
  });

  it("creates entry with optional fields null", async () => {
    const id = await createTimeEntry(db, {
      slackUserId: "U123",
      slackUsername: "maria",
      date: "2026-02-26",
      minutes: 30,
      taskTypeId: 1,
      projectId: null,
      grantId: null,
      comment: null,
    });

    const entry = await getTimeEntryById(db, id);
    expect(entry!.projectId).toBeNull();
    expect(entry!.grantId).toBeNull();
  });

  it("updates an existing entry", async () => {
    const id = await createTimeEntry(db, {
      slackUserId: "U123",
      slackUsername: "maria",
      date: "2026-02-26",
      minutes: 60,
      taskTypeId: 1,
      projectId: null,
      grantId: null,
      comment: null,
    });

    await updateTimeEntry(db, id, { minutes: 90, comment: "Updated" });

    const entry = await getTimeEntryById(db, id);
    expect(entry!.minutes).toBe(90);
    expect(entry!.comment).toBe("Updated");
  });

  it("soft-deletes an entry", async () => {
    const id = await createTimeEntry(db, {
      slackUserId: "U123",
      slackUsername: "maria",
      date: "2026-02-26",
      minutes: 60,
      taskTypeId: 1,
      projectId: null,
      grantId: null,
      comment: null,
    });

    await softDeleteTimeEntry(db, id);

    const recent = await getRecentEntries(db, "U123", 5);
    expect(recent).toHaveLength(0);
  });

  it("retrieves recent entries excluding deleted", async () => {
    for (let i = 0; i < 7; i++) {
      await createTimeEntry(db, {
        slackUserId: "U123",
        slackUsername: "maria",
        date: `2026-02-${20 + i}`,
        minutes: 60,
        taskTypeId: 1,
        projectId: null,
        grantId: null,
        comment: null,
      });
    }

    const recent = await getRecentEntries(db, "U123", 5);
    expect(recent).toHaveLength(5);
    // Most recent first
    expect(recent[0].date).toBe("2026-02-26");
  });
});

Step 2: Run test to verify it fails

Run: npx vitest run src/db/queries.test.ts Expected: FAIL — new functions not exported

Step 3: Add time entry operations to queries.ts

Add to src/db/queries.ts:

// --- Time Entry types ---

export interface TimeEntryInput {
  slackUserId: string;
  slackUsername: string;
  date: string;
  minutes: number;
  taskTypeId: number;
  projectId: number | null;
  grantId: number | null;
  comment: string | null;
}

export interface TimeEntry {
  id: number;
  slackUserId: string;
  slackUsername: string;
  date: string;
  minutes: number;
  taskTypeId: number;
  projectId: number | null;
  grantId: number | null;
  comment: string | null;
  createdAt: string;
}

// --- Time Entries ---

export async function createTimeEntry(db: Database, input: TimeEntryInput): Promise<number> {
  const result = await db.execute({
    sql: `INSERT INTO time_entries (slack_user_id, slack_username, date, minutes, task_type_id, project_id, grant_id, comment)
          VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
    args: [input.slackUserId, input.slackUsername, input.date, input.minutes, input.taskTypeId, input.projectId, input.grantId, input.comment],
  });
  return Number(result.lastInsertRowid);
}

export async function getTimeEntryById(db: Database, id: number): Promise<TimeEntry | null> {
  const result = await db.execute({
    sql: `SELECT id, slack_user_id, slack_username, date, minutes, task_type_id, project_id, grant_id, comment, created_at
          FROM time_entries WHERE id = ? AND deleted_at IS NULL`,
    args: [id],
  });
  if (result.rows.length === 0) return null;
  const r = result.rows[0];
  return {
    id: r[0] as number,
    slackUserId: r[1] as string,
    slackUsername: r[2] as string,
    date: r[3] as string,
    minutes: r[4] as number,
    taskTypeId: r[5] as number,
    projectId: r[6] as number | null,
    grantId: r[7] as number | null,
    comment: r[8] as string | null,
    createdAt: r[9] as string,
  };
}

export async function updateTimeEntry(
  db: Database,
  id: number,
  updates: Partial<Pick<TimeEntryInput, "date" | "minutes" | "taskTypeId" | "projectId" | "grantId" | "comment">>
): Promise<void> {
  const setClauses: string[] = [];
  const args: unknown[] = [];

  for (const [key, value] of Object.entries(updates)) {
    const columnMap: Record<string, string> = {
      date: "date",
      minutes: "minutes",
      taskTypeId: "task_type_id",
      projectId: "project_id",
      grantId: "grant_id",
      comment: "comment",
    };
    const col = columnMap[key];
    if (col) {
      setClauses.push(`${col} = ?`);
      args.push(value);
    }
  }

  setClauses.push("updated_at = datetime('now')");
  args.push(id);

  await db.execute({
    sql: `UPDATE time_entries SET ${setClauses.join(", ")} WHERE id = ?`,
    args,
  });
}

export async function softDeleteTimeEntry(db: Database, id: number): Promise<void> {
  await db.execute({
    sql: "UPDATE time_entries SET deleted_at = datetime('now') WHERE id = ?",
    args: [id],
  });
}

export async function getRecentEntries(db: Database, slackUserId: string, limit: number): Promise<TimeEntry[]> {
  const result = await db.execute({
    sql: `SELECT id, slack_user_id, slack_username, date, minutes, task_type_id, project_id, grant_id, comment, created_at
          FROM time_entries WHERE slack_user_id = ? AND deleted_at IS NULL
          ORDER BY date DESC, created_at DESC LIMIT ?`,
    args: [slackUserId, limit],
  });
  return result.rows.map((r) => ({
    id: r[0] as number,
    slackUserId: r[1] as string,
    slackUsername: r[2] as string,
    date: r[3] as string,
    minutes: r[4] as number,
    taskTypeId: r[5] as number,
    projectId: r[6] as number | null,
    grantId: r[7] as number | null,
    comment: r[8] as string | null,
    createdAt: r[9] as string,
  }));
}

Step 4: Run test to verify it passes

Run: npx vitest run src/db/queries.test.ts Expected: PASS (all tests)

Step 5: Commit

git add src/db/queries.ts src/db/queries.test.ts
git commit -m "feat(db): time entry CRUD with soft delete"

Task 5: Database Queries — Intern Management & Config

Files:

Modify: src/db/queries.ts
Modify: src/db/queries.test.ts

Step 1: Write failing tests

Add to src/db/queries.test.ts:

import {
  // ... existing imports ...
  addIntern,
  removeIntern,
  getInterns,
  setConfig,
  getConfig,
} from "./queries.js";

describe("intern management", () => {
  let db: Database;

  beforeEach(async () => {
    db = await createTestDb();
    await migrate(db);
  });

  it("adds and lists interns", async () => {
    await addIntern(db, "U123", "maria");
    await addIntern(db, "U456", "james");

    const interns = await getInterns(db);
    expect(interns).toHaveLength(2);
  });

  it("removes an intern", async () => {
    await addIntern(db, "U123", "maria");
    await removeIntern(db, "U123");

    expect(await getInterns(db)).toHaveLength(0);
  });
});

describe("config", () => {
  let db: Database;

  beforeEach(async () => {
    db = await createTestDb();
    await migrate(db);
  });

  it("sets and gets config values", async () => {
    await setConfig(db, "reminder_day", "friday");
    expect(await getConfig(db, "reminder_day")).toBe("friday");
  });

  it("upserts config on conflict", async () => {
    await setConfig(db, "reminder_day", "friday");
    await setConfig(db, "reminder_day", "thursday");
    expect(await getConfig(db, "reminder_day")).toBe("thursday");
  });

  it("returns null for missing keys", async () => {
    expect(await getConfig(db, "nonexistent")).toBeNull();
  });
});

Step 2: Run to verify failure, implement, run to verify pass

Add to src/db/queries.ts:

// --- Intern types ---

export interface Intern {
  slackUserId: string;
  slackUsername: string;
}

// --- Interns ---

export async function addIntern(db: Database, slackUserId: string, slackUsername: string): Promise<void> {
  await db.execute({
    sql: "INSERT OR REPLACE INTO interns (slack_user_id, slack_username) VALUES (?, ?)",
    args: [slackUserId, slackUsername],
  });
}

export async function removeIntern(db: Database, slackUserId: string): Promise<void> {
  await db.execute({ sql: "DELETE FROM interns WHERE slack_user_id = ?", args: [slackUserId] });
}

export async function getInterns(db: Database): Promise<Intern[]> {
  const result = await db.execute({ sql: "SELECT slack_user_id, slack_username FROM interns ORDER BY slack_username" });
  return result.rows.map((r) => ({ slackUserId: r[0] as string, slackUsername: r[1] as string }));
}

// --- Config ---

export async function setConfig(db: Database, key: string, value: string): Promise<void> {
  await db.execute({
    sql: "INSERT OR REPLACE INTO config (key, value) VALUES (?, ?)",
    args: [key, value],
  });
}

export async function getConfig(db: Database, key: string): Promise<string | null> {
  const result = await db.execute({ sql: "SELECT value FROM config WHERE key = ?", args: [key] });
  return result.rows.length > 0 ? (result.rows[0][0] as string) : null;
}

Step 3: Run tests

Run: npx vitest run src/db/queries.test.ts Expected: PASS (all tests)

Step 4: Commit

git add src/db/queries.ts src/db/queries.test.ts
git commit -m "feat(db): intern management and config key-value store"

Task 6: Duration & Date Formatting

Files:

Create: src/lib/format.ts
Create: src/lib/format.test.ts

Step 1: Write failing tests

// src/lib/format.test.ts
import { describe, it, expect } from "vitest";
import { formatDuration, formatDateRange, getWeekBounds, getLast7Days } from "./format.js";

describe("formatDuration", () => {
  it("formats minutes as hours and minutes", () => {
    expect(formatDuration(60)).toBe("1h");
    expect(formatDuration(90)).toBe("1h 30m");
    expect(formatDuration(15)).toBe("15m");
    expect(formatDuration(480)).toBe("8h");
    expect(formatDuration(0)).toBe("0m");
  });
});

describe("formatDateRange", () => {
  it("formats a date range for display", () => {
    expect(formatDateRange("2026-02-24", "2026-02-28")).toBe("Feb 24 – Feb 28, 2026");
  });

  it("handles cross-month ranges", () => {
    expect(formatDateRange("2026-01-28", "2026-02-03")).toBe("Jan 28 – Feb 3, 2026");
  });
});

describe("getWeekBounds", () => {
  it("returns Monday–Sunday for a given date", () => {
    // Feb 26 2026 is a Thursday
    const { start, end } = getWeekBounds("2026-02-26");
    expect(start).toBe("2026-02-23"); // Monday
    expect(end).toBe("2026-03-01");   // Sunday
  });
});

describe("getLast7Days", () => {
  it("returns last 7 days in descending order", () => {
    const days = getLast7Days("2026-02-26");
    expect(days).toHaveLength(7);
    expect(days[0]).toBe("2026-02-26");
    expect(days[6]).toBe("2026-02-20");
  });
});

Step 2: Run to verify failure, implement, run to verify pass

// src/lib/format.ts

export function formatDuration(minutes: number): string {
  const hours = Math.floor(minutes / 60);
  const mins = minutes % 60;
  if (hours === 0) return `${mins}m`;
  if (mins === 0) return `${hours}h`;
  return `${hours}h ${mins}m`;
}

const MONTHS = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];

export function formatDateRange(start: string, end: string): string {
  const s = new Date(start + "T00:00:00");
  const e = new Date(end + "T00:00:00");
  return `${MONTHS[s.getMonth()]} ${s.getDate()} – ${MONTHS[e.getMonth()]} ${e.getDate()}, ${e.getFullYear()}`;
}

export function getWeekBounds(dateStr: string): { start: string; end: string } {
  const date = new Date(dateStr + "T00:00:00");
  const day = date.getDay(); // 0=Sun, 1=Mon...
  const diffToMonday = day === 0 ? -6 : 1 - day;
  const monday = new Date(date);
  monday.setDate(date.getDate() + diffToMonday);
  const sunday = new Date(monday);
  sunday.setDate(monday.getDate() + 6);
  return {
    start: monday.toISOString().slice(0, 10),
    end: sunday.toISOString().slice(0, 10),
  };
}

export function getLast7Days(fromDate: string): string[] {
  const days: string[] = [];
  const date = new Date(fromDate + "T00:00:00");
  for (let i = 0; i < 7; i++) {
    const d = new Date(date);
    d.setDate(date.getDate() - i);
    days.push(d.toISOString().slice(0, 10));
  }
  return days;
}

Step 3: Run tests

Run: npx vitest run src/lib/format.test.ts Expected: PASS

Step 4: Commit

git add src/lib/
git commit -m "feat(lib): duration and date formatting utilities"

Task 7: Admin Command Parser

Files:

Create: src/handlers/admin.ts
Create: src/handlers/admin.test.ts

Step 1: Write failing tests

// src/handlers/admin.test.ts
import { describe, it, expect } from "vitest";
import { parseAdminCommand } from "./admin.js";

describe("parseAdminCommand", () => {
  it("parses add project", () => {
    expect(parseAdminCommand('add project "Community Oral Histories"'))
      .toEqual({ action: "add", entity: "project", args: ["Community Oral Histories"] });
  });

  it("parses add grant with code and name", () => {
    expect(parseAdminCommand('add grant "NEH-PW-290261" "NEH Preservation & Access"'))
      .toEqual({ action: "add", entity: "grant", args: ["NEH-PW-290261", "NEH Preservation & Access"] });
  });

  it("parses add task", () => {
    expect(parseAdminCommand('add task "Scanning"'))
      .toEqual({ action: "add", entity: "task", args: ["Scanning"] });
  });

  it("parses remove task", () => {
    expect(parseAdminCommand('remove task "Scanning"'))
      .toEqual({ action: "remove", entity: "task", args: ["Scanning"] });
  });

  it("parses list", () => {
    expect(parseAdminCommand("list"))
      .toEqual({ action: "list", entity: null, args: [] });
  });

  it("parses intern add with user mention", () => {
    expect(parseAdminCommand("intern add <@U123|maria>"))
      .toEqual({ action: "add", entity: "intern", args: ["U123", "maria"] });
  });

  it("parses intern remove", () => {
    expect(parseAdminCommand("intern remove <@U123|maria>"))
      .toEqual({ action: "remove", entity: "intern", args: ["U123", "maria"] });
  });

  it("parses reminder config", () => {
    expect(parseAdminCommand("reminder day friday"))
      .toEqual({ action: "reminder", entity: "day", args: ["friday"] });
    expect(parseAdminCommand("reminder off"))
      .toEqual({ action: "reminder", entity: "off", args: [] });
  });

  it("returns error for unrecognized commands", () => {
    expect(parseAdminCommand("do something weird"))
      .toEqual({ action: "error", entity: null, args: ["Unrecognized command. Try: add, remove, list, intern, reminder"] });
  });
});

Step 2: Run to verify failure, implement, run to verify pass

// src/handlers/admin.ts

export interface AdminCommand {
  action: string;
  entity: string | null;
  args: string[];
}

export function parseAdminCommand(text: string): AdminCommand {
  const trimmed = text.trim();

  // "list"
  if (trimmed === "list") {
    return { action: "list", entity: null, args: [] };
  }

  // "reminder day friday" / "reminder off" / "reminder on" / "reminder time 15:00"
  const reminderMatch = trimmed.match(/^reminder\s+(\S+)(?:\s+(.+))?$/);
  if (reminderMatch) {
    const subcommand = reminderMatch[1];
    const value = reminderMatch[2];
    return { action: "reminder", entity: subcommand, args: value ? [value] : [] };
  }

  // "intern add <@U123|maria>" / "intern remove <@U123|maria>"
  const internMatch = trimmed.match(/^intern\s+(add|remove)\s+<@(\w+)\|([^>]+)>/);
  if (internMatch) {
    return { action: internMatch[1], entity: "intern", args: [internMatch[2], internMatch[3]] };
  }

  // "add project "Name"" / "remove task "Name""
  const crudMatch = trimmed.match(/^(add|remove)\s+(project|grant|task)\s+(.+)$/);
  if (crudMatch) {
    const action = crudMatch[1];
    const entity = crudMatch[2];
    const rawArgs = crudMatch[3];

    // Extract quoted strings
    const quoted = [...rawArgs.matchAll(/"([^"]+)"/g)].map((m) => m[1]);
    if (quoted.length > 0) {
      return { action, entity, args: quoted };
    }
    return { action, entity, args: [rawArgs.trim()] };
  }

  return { action: "error", entity: null, args: ["Unrecognized command. Try: add, remove, list, intern, reminder"] };
}

Step 3: Run tests

Run: npx vitest run src/handlers/admin.test.ts Expected: PASS

Step 4: Commit

git add src/handlers/
git commit -m "feat(handlers): admin command parser for timesheet-admin"

Task 8: Report Generation

Files:

Create: src/handlers/report.ts
Create: src/handlers/report.test.ts
Modify: src/db/queries.ts (add report query)

Step 1: Write failing test for report query

Add to src/db/queries.test.ts:

import {
  // ... existing ...
  getReportData,
} from "./queries.js";

describe("report query", () => {
  let db: Database;

  beforeEach(async () => {
    db = await createTestDb();
    await migrate(db);
    await addTaskType(db, "Transcription");
    await addTaskType(db, "Metadata Editing");
    await addGrant(db, "NEH-PW-290261", "NEH");
  });

  it("aggregates hours by user, task, and grant for a date range", async () => {
    await createTimeEntry(db, {
      slackUserId: "U123", slackUsername: "maria", date: "2026-02-24",
      minutes: 120, taskTypeId: 1, projectId: null, grantId: 1, comment: null,
    });
    await createTimeEntry(db, {
      slackUserId: "U123", slackUsername: "maria", date: "2026-02-25",
      minutes: 60, taskTypeId: 2, projectId: null, grantId: null, comment: null,
    });
    await createTimeEntry(db, {
      slackUserId: "U456", slackUsername: "james", date: "2026-02-25",
      minutes: 90, taskTypeId: 1, projectId: null, grantId: 1, comment: null,
    });

    const data = await getReportData(db, "2026-02-24", "2026-02-28", null);
    expect(data).toHaveLength(3);
    expect(data[0].slackUsername).toBe("james");
    expect(data[0].totalMinutes).toBe(90);
  });

  it("filters by user when specified", async () => {
    await createTimeEntry(db, {
      slackUserId: "U123", slackUsername: "maria", date: "2026-02-24",
      minutes: 60, taskTypeId: 1, projectId: null, grantId: null, comment: null,
    });
    await createTimeEntry(db, {
      slackUserId: "U456", slackUsername: "james", date: "2026-02-24",
      minutes: 60, taskTypeId: 1, projectId: null, grantId: null, comment: null,
    });

    const data = await getReportData(db, "2026-02-24", "2026-02-28", "U123");
    expect(data).toHaveLength(1);
    expect(data[0].slackUsername).toBe("maria");
  });
});

Step 2: Add report query to queries.ts

// Add to src/db/queries.ts

export interface ReportRow {
  slackUserId: string;
  slackUsername: string;
  taskTypeName: string;
  grantCode: string | null;
  totalMinutes: number;
}

export async function getReportData(
  db: Database,
  startDate: string,
  endDate: string,
  slackUserId: string | null
): Promise<ReportRow[]> {
  const args: unknown[] = [startDate, endDate];
  let userFilter = "";
  if (slackUserId) {
    userFilter = "AND te.slack_user_id = ?";
    args.push(slackUserId);
  }

  const result = await db.execute({
    sql: `SELECT te.slack_user_id, te.slack_username, tt.name as task_type_name,
                 g.code as grant_code, SUM(te.minutes) as total_minutes
          FROM time_entries te
          JOIN task_types tt ON te.task_type_id = tt.id
          LEFT JOIN grants g ON te.grant_id = g.id
          WHERE te.date >= ? AND te.date <= ? AND te.deleted_at IS NULL ${userFilter}
          GROUP BY te.slack_user_id, te.slack_username, tt.name, g.code
          ORDER BY te.slack_username, tt.name`,
    args,
  });

  return result.rows.map((r) => ({
    slackUserId: r[0] as string,
    slackUsername: r[1] as string,
    taskTypeName: r[2] as string,
    grantCode: r[3] as string | null,
    totalMinutes: r[4] as number,
  }));
}

Step 3: Write failing test for report formatting

// src/handlers/report.test.ts
import { describe, it, expect } from "vitest";
import { formatReport } from "./report.js";
import type { ReportRow } from "../db/queries.js";

describe("formatReport", () => {
  it("formats a multi-user report", () => {
    const data: ReportRow[] = [
      { slackUserId: "U456", slackUsername: "james", taskTypeName: "Transcription", grantCode: "NEH-PW-290261", totalMinutes: 480 },
      { slackUserId: "U123", slackUsername: "maria", taskTypeName: "Metadata Editing", grantCode: null, totalMinutes: 360 },
      { slackUserId: "U123", slackUsername: "maria", taskTypeName: "Transcription", grantCode: "NEH-PW-290261", totalMinutes: 720 },
    ];

    const output = formatReport(data, "2026-02-24", "2026-02-28");

    expect(output).toContain("Feb 24 – Feb 28, 2026");
    expect(output).toContain("james");
    expect(output).toContain("maria");
    expect(output).toContain("8h");    // james total
    expect(output).toContain("18h");   // maria total
    expect(output).toContain("26h");   // grand total
    expect(output).toContain("NEH-PW-290261");
  });

  it("handles empty data", () => {
    const output = formatReport([], "2026-02-24", "2026-02-28");
    expect(output).toContain("No time entries");
  });
});

Step 4: Implement report formatter

// src/handlers/report.ts
import type { ReportRow } from "../db/queries.js";
import { formatDuration, formatDateRange } from "../lib/format.js";

export function formatReport(data: ReportRow[], startDate: string, endDate: string): string {
  const header = `*Timesheet Report: ${formatDateRange(startDate, endDate)}*\n`;

  if (data.length === 0) {
    return header + "\nNo time entries found for this period.";
  }

  // Group by user
  const byUser = new Map<string, { username: string; rows: ReportRow[]; total: number }>();
  for (const row of data) {
    if (!byUser.has(row.slackUserId)) {
      byUser.set(row.slackUserId, { username: row.slackUsername, rows: [], total: 0 });
    }
    const user = byUser.get(row.slackUserId)!;
    user.rows.push(row);
    user.total += row.totalMinutes;
  }

  let grandTotal = 0;
  const lines: string[] = [header];

  for (const [, user] of byUser) {
    grandTotal += user.total;
    lines.push(`*@${user.username}* — ${formatDuration(user.total)}`);
    for (const row of user.rows) {
      const grant = row.grantCode ? `  (${row.grantCode})` : "";
      lines.push(`  ${row.taskTypeName}  ${formatDuration(row.totalMinutes)}${grant}`);
    }
    lines.push("");
  }

  lines.push(`*Total: ${formatDuration(grandTotal)} across ${byUser.size} intern${byUser.size === 1 ? "" : "s"}*`);

  return lines.join("\n");
}

Step 5: Run all tests

Run: npx vitest run Expected: ALL PASS

Step 6: Commit

git add src/db/queries.ts src/db/queries.test.ts src/handlers/report.ts src/handlers/report.test.ts
git commit -m "feat(reports): report data query and text formatter"

Task 9: Report Command Argument Parser

Files:

Modify: src/handlers/report.ts
Modify: src/handlers/report.test.ts

Step 1: Write failing tests for argument parsing

Add to src/handlers/report.test.ts:

import { parseReportArgs } from "./report.js";

describe("parseReportArgs", () => {
  it("defaults to current week, all users", () => {
    const result = parseReportArgs("", "2026-02-26");
    expect(result.userId).toBeNull();
    expect(result.startDate).toBe("2026-02-23"); // Monday
    expect(result.endDate).toBe("2026-03-01");   // Sunday
  });

  it("parses user mention", () => {
    const result = parseReportArgs("<@U123|maria>", "2026-02-26");
    expect(result.userId).toBe("U123");
  });

  it("parses date range", () => {
    const result = parseReportArgs("2026-02-01 2026-02-28", "2026-02-26");
    expect(result.startDate).toBe("2026-02-01");
    expect(result.endDate).toBe("2026-02-28");
  });

  it("parses user + date range", () => {
    const result = parseReportArgs("<@U123|maria> 2026-02-01 2026-02-28", "2026-02-26");
    expect(result.userId).toBe("U123");
    expect(result.startDate).toBe("2026-02-01");
    expect(result.endDate).toBe("2026-02-28");
  });
});

Step 2: Implement parser, run tests, commit

// Add to src/handlers/report.ts
import { getWeekBounds } from "../lib/format.js";

export interface ReportArgs {
  userId: string | null;
  startDate: string;
  endDate: string;
}

export function parseReportArgs(text: string, today: string): ReportArgs {
  const trimmed = text.trim();
  let userId: string | null = null;

  // Extract user mention
  let remaining = trimmed;
  const userMatch = remaining.match(/<@(\w+)\|[^>]+>/);
  if (userMatch) {
    userId = userMatch[1];
    remaining = remaining.replace(userMatch[0], "").trim();
  }

  // Extract dates
  const dateMatch = remaining.match(/(\d{4}-\d{2}-\d{2})\s+(\d{4}-\d{2}-\d{2})/);
  if (dateMatch) {
    return { userId, startDate: dateMatch[1], endDate: dateMatch[2] };
  }

  // Default: current week
  const { start, end } = getWeekBounds(today);
  return { userId, startDate: start, endDate: end };
}

Run: npx vitest run Expected: ALL PASS

git add src/handlers/report.ts src/handlers/report.test.ts
git commit -m "feat(reports): argument parser for /timesheet command"

Task 10: Slack Signature Verification

Files:

Create: src/slack/verify.ts
Create: src/slack/verify.test.ts

Step 1: Write failing tests

// src/slack/verify.test.ts
import { describe, it, expect } from "vitest";
import { verifySlackSignature } from "./verify.js";

const SIGNING_SECRET = "test_secret_12345";

describe("verifySlackSignature", () => {
  it("returns true for valid signature", async () => {
    const timestamp = Math.floor(Date.now() / 1000).toString();
    const body = "token=xyzz0WbapA4vBCDEFaYZlElrm&command=%2Flogtime";

    // Compute expected signature
    const encoder = new TextEncoder();
    const key = await crypto.subtle.importKey(
      "raw", encoder.encode(SIGNING_SECRET), { name: "HMAC", hash: "SHA-256" }, false, ["sign"]
    );
    const sig = await crypto.subtle.sign("HMAC", key, encoder.encode(`v0:${timestamp}:${body}`));
    const hex = Array.from(new Uint8Array(sig)).map(b => b.toString(16).padStart(2, "0")).join("");
    const signature = `v0=${hex}`;

    const result = await verifySlackSignature(body, timestamp, signature, SIGNING_SECRET);
    expect(result).toBe(true);
  });

  it("returns false for invalid signature", async () => {
    const result = await verifySlackSignature("body", "12345", "v0=invalid", SIGNING_SECRET);
    expect(result).toBe(false);
  });

  it("returns false for stale timestamp (>5 min)", async () => {
    const staleTimestamp = (Math.floor(Date.now() / 1000) - 600).toString();
    const result = await verifySlackSignature("body", staleTimestamp, "v0=anything", SIGNING_SECRET);
    expect(result).toBe(false);
  });
});

Step 2: Implement

// src/slack/verify.ts

export async function verifySlackSignature(
  body: string,
  timestamp: string,
  signature: string,
  signingSecret: string
): Promise<boolean> {
  // Reject stale requests (>5 minutes)
  const now = Math.floor(Date.now() / 1000);
  if (Math.abs(now - Number(timestamp)) > 300) {
    return false;
  }

  const encoder = new TextEncoder();
  const key = await crypto.subtle.importKey(
    "raw",
    encoder.encode(signingSecret),
    { name: "HMAC", hash: "SHA-256" },
    false,
    ["sign"]
  );

  const baseString = `v0:${timestamp}:${body}`;
  const sig = await crypto.subtle.sign("HMAC", key, encoder.encode(baseString));
  const hex = Array.from(new Uint8Array(sig))
    .map((b) => b.toString(16).padStart(2, "0"))
    .join("");
  const expected = `v0=${hex}`;

  // Constant-time comparison
  if (expected.length !== signature.length) return false;
  let mismatch = 0;
  for (let i = 0; i < expected.length; i++) {
    mismatch |= expected.charCodeAt(i) ^ signature.charCodeAt(i);
  }
  return mismatch === 0;
}

Step 3: Run tests, commit

Run: npx vitest run Expected: ALL PASS

git add src/slack/
git commit -m "feat(slack): request signature verification with replay protection"

Files:

Create: src/slack/modals.ts
Create: src/slack/modals.test.ts

Step 1: Write failing tests

// src/slack/modals.test.ts
import { describe, it, expect } from "vitest";
import { buildLogtimeModal, buildConfirmationBlocks, buildRecentEntriesBlocks } from "./modals.js";
import type { Project, Grant, TaskType, TimeEntry } from "../db/queries.js";

describe("buildLogtimeModal", () => {
  const taskTypes: TaskType[] = [{ id: 1, name: "Transcription" }, { id: 2, name: "Metadata Editing" }];
  const projects: Project[] = [{ id: 1, name: "Lakeland" }];
  const grants: Grant[] = [{ id: 1, code: "NEH-PW-290261", name: "NEH" }];

  it("builds a valid Block Kit modal with all dropdowns", () => {
    const modal = buildLogtimeModal(taskTypes, projects, grants, "2026-02-26");

    expect(modal.type).toBe("modal");
    expect(modal.title.text).toBe("Log Time");
    expect(modal.submit!.text).toBe("Submit");
    // Should have blocks for: date, time, task, project, grant, comment
    expect(modal.blocks.length).toBeGreaterThanOrEqual(6);
  });

  it("builds modal pre-filled for editing", () => {
    const existing = {
      id: 42,
      date: "2026-02-25",
      minutes: 90,
      taskTypeId: 1,
      projectId: 1,
      grantId: 1,
      comment: "Test",
    };
    const modal = buildLogtimeModal(taskTypes, projects, grants, "2026-02-26", existing);

    // private_metadata should contain the entry ID for update
    expect(modal.private_metadata).toContain("42");
  });
});

describe("buildConfirmationBlocks", () => {
  it("builds confirmation message with edit/delete buttons", () => {
    const blocks = buildConfirmationBlocks(42, "1h", "Transcription", "Feb 26", "NEH-PW-290261");

    // Should contain text and action buttons
    const text = JSON.stringify(blocks);
    expect(text).toContain("Transcription");
    expect(text).toContain("edit");
    expect(text).toContain("delete");
  });
});

describe("buildRecentEntriesBlocks", () => {
  it("builds blocks for recent entries with edit/delete buttons", () => {
    const entries: Array<TimeEntry & { taskTypeName: string; grantCode: string | null }> = [
      {
        id: 1, slackUserId: "U123", slackUsername: "maria", date: "2026-02-26",
        minutes: 60, taskTypeId: 1, projectId: null, grantId: null, comment: null,
        createdAt: "2026-02-26T10:00:00", taskTypeName: "Transcription", grantCode: null,
      },
    ];

    const blocks = buildRecentEntriesBlocks(entries);
    const text = JSON.stringify(blocks);
    expect(text).toContain("Transcription");
    expect(text).toContain("1h");
  });

  it("shows message when no entries", () => {
    const blocks = buildRecentEntriesBlocks([]);
    const text = JSON.stringify(blocks);
    expect(text).toContain("No recent entries");
  });
});

Step 2: Implement modals.ts

This is the largest file — Block Kit JSON is verbose. The implementation builds Slack Block Kit view objects as plain objects (no SDK dependency).

// src/slack/modals.ts
import type { Project, Grant, TaskType, TimeEntry } from "../db/queries.js";
import { formatDuration, getLast7Days } from "../lib/format.js";

interface ModalView {
  type: "modal";
  callback_id: string;
  title: { type: "plain_text"; text: string };
  submit?: { type: "plain_text"; text: string };
  blocks: unknown[];
  private_metadata?: string;
}

interface EditPrefill {
  id: number;
  date: string;
  minutes: number;
  taskTypeId: number;
  projectId: number | null;
  grantId: number | null;
  comment: string | null;
}

export function buildLogtimeModal(
  taskTypes: TaskType[],
  projects: Project[],
  grants: Grant[],
  today: string,
  prefill?: EditPrefill
): ModalView {
  const days = getLast7Days(today);
  const timeOptions = [];
  for (let m = 15; m <= 480; m += 15) {
    timeOptions.push({ text: { type: "plain_text", text: formatDuration(m) }, value: String(m) });
  }

  return {
    type: "modal",
    callback_id: prefill ? "edit_time_entry" : "log_time_entry",
    title: { type: "plain_text", text: "Log Time" },
    submit: { type: "plain_text", text: "Submit" },
    private_metadata: prefill ? JSON.stringify({ entryId: prefill.id }) : undefined,
    blocks: [
      {
        type: "input",
        block_id: "date_block",
        label: { type: "plain_text", text: "Date" },
        element: {
          type: "static_select",
          action_id: "date_select",
          options: days.map((d) => ({ text: { type: "plain_text", text: d }, value: d })),
          ...(prefill?.date ? { initial_option: { text: { type: "plain_text", text: prefill.date }, value: prefill.date } } : {}),
        },
      },
      {
        type: "input",
        block_id: "time_block",
        label: { type: "plain_text", text: "Time Worked" },
        element: {
          type: "static_select",
          action_id: "time_select",
          options: timeOptions,
          ...(prefill?.minutes ? { initial_option: timeOptions.find((o) => o.value === String(prefill.minutes)) } : {}),
        },
      },
      {
        type: "input",
        block_id: "task_block",
        label: { type: "plain_text", text: "Task" },
        element: {
          type: "static_select",
          action_id: "task_select",
          options: taskTypes.map((t) => ({ text: { type: "plain_text", text: t.name }, value: String(t.id) })),
          ...(prefill?.taskTypeId ? { initial_option: { text: { type: "plain_text", text: taskTypes.find((t) => t.id === prefill.taskTypeId)?.name ?? "" }, value: String(prefill.taskTypeId) } } : {}),
        },
      },
      {
        type: "input",
        block_id: "project_block",
        label: { type: "plain_text", text: "Project" },
        optional: true,
        element: {
          type: "static_select",
          action_id: "project_select",
          options: projects.map((p) => ({ text: { type: "plain_text", text: p.name }, value: String(p.id) })),
        },
      },
      {
        type: "input",
        block_id: "grant_block",
        label: { type: "plain_text", text: "Grant" },
        optional: true,
        element: {
          type: "static_select",
          action_id: "grant_select",
          options: grants.map((g) => ({ text: { type: "plain_text", text: `${g.code} — ${g.name}` }, value: String(g.id) })),
        },
      },
      {
        type: "input",
        block_id: "comment_block",
        label: { type: "plain_text", text: "Comment" },
        optional: true,
        element: { type: "plain_text_input", action_id: "comment_input", multiline: true },
      },
    ],
  };
}

export function buildConfirmationBlocks(
  entryId: number,
  duration: string,
  taskName: string,
  dateDisplay: string,
  grantCode: string | null
): unknown[] {
  const grantSuffix = grantCode ? ` (${grantCode})` : "";
  return [
    {
      type: "section",
      text: { type: "mrkdwn", text: `Logged *${duration}* of *${taskName}* on ${dateDisplay}${grantSuffix}` },
    },
    {
      type: "actions",
      elements: [
        { type: "button", text: { type: "plain_text", text: "Edit" }, action_id: "edit_entry", value: String(entryId) },
        { type: "button", text: { type: "plain_text", text: "Delete" }, action_id: "delete_entry", value: String(entryId), style: "danger" },
      ],
    },
  ];
}

export function buildRecentEntriesBlocks(
  entries: Array<TimeEntry & { taskTypeName: string; grantCode: string | null }>
): unknown[] {
  if (entries.length === 0) {
    return [{ type: "section", text: { type: "mrkdwn", text: "No recent entries found." } }];
  }

  const blocks: unknown[] = [
    { type: "section", text: { type: "mrkdwn", text: "*Your recent entries:*" } },
  ];

  for (const entry of entries) {
    const grant = entry.grantCode ? ` (${entry.grantCode})` : "";
    blocks.push({
      type: "section",
      text: { type: "mrkdwn", text: `${entry.date} — ${formatDuration(entry.minutes)} ${entry.taskTypeName}${grant}` },
      accessory: {
        type: "overflow",
        action_id: `entry_overflow_${entry.id}`,
        options: [
          { text: { type: "plain_text", text: "Edit" }, value: `edit:${entry.id}` },
          { text: { type: "plain_text", text: "Delete" }, value: `delete:${entry.id}` },
        ],
      },
    });
  }

  return blocks;
}

Step 3: Run all tests

Run: npx vitest run Expected: ALL PASS

Step 4: Commit

git add src/slack/modals.ts src/slack/modals.test.ts
git commit -m "feat(slack): Block Kit modal builder for time entry and confirmations"

Task 12: Slack API Client

Files:

Create: src/slack/api.ts

This is thin glue — no tests needed for HTTP calls. Tested via integration in Task 14.

Step 1: Implement

// src/slack/api.ts

export interface SlackClient {
  openModal(triggerId: string, view: unknown): Promise<void>;
  postEphemeral(channel: string, userId: string, text: string, blocks?: unknown[]): Promise<void>;
  sendDm(userId: string, text: string): Promise<void>;
}

export function createSlackClient(botToken: string): SlackClient {
  async function slackApi(method: string, body: unknown): Promise<unknown> {
    const response = await fetch(`https://slack.com/api/${method}`, {
      method: "POST",
      headers: {
        Authorization: `Bearer ${botToken}`,
        "Content-Type": "application/json",
      },
      body: JSON.stringify(body),
    });
    const data = await response.json();
    if (!data.ok) {
      throw new Error(`Slack API error (${method}): ${data.error}`);
    }
    return data;
  }

  return {
    async openModal(triggerId, view) {
      await slackApi("views.open", { trigger_id: triggerId, view });
    },
    async postEphemeral(channel, userId, text, blocks) {
      await slackApi("chat.postEphemeral", { channel, user: userId, text, blocks });
    },
    async sendDm(userId, text) {
      // Open DM channel, then send
      const conv = (await slackApi("conversations.open", { users: userId })) as { channel: { id: string } };
      await slackApi("chat.postMessage", { channel: conv.channel.id, text });
    },
  };
}

Step 2: Commit

git add src/slack/api.ts
git commit -m "feat(slack): API client for modals, ephemeral messages, and DMs"

Task 13: HTTP Router (main.ts)

Files:

Create: src/main.ts

This wires everything together. It's the Val Town HTTP val entry point. Tested manually against Slack (Task 15).

Step 1: Implement the router

// src/main.ts
import { verifySlackSignature } from "./slack/verify.js";
import { createSlackClient, type SlackClient } from "./slack/api.js";
import { buildLogtimeModal, buildConfirmationBlocks, buildRecentEntriesBlocks } from "./slack/modals.js";
import { parseAdminCommand } from "./handlers/admin.js";
import { formatReport, parseReportArgs } from "./handlers/report.js";
import { migrate } from "./db/schema.js";
import * as queries from "./db/queries.js";
import { formatDuration } from "./lib/format.js";
import type { Database } from "./db/types.js";

// Val Town provides these — injected at runtime
declare const Deno: { env: { get(key: string): string | undefined } };

let db: Database;
let slack: SlackClient;
let migrated = false;

async function getDb(): Promise<Database> {
  if (!db) {
    // Dynamic import for Val Town runtime
    const { sqlite } = await import("https://esm.town/v/std/sqlite");
    db = sqlite;
  }
  if (!migrated) {
    await migrate(db);
    migrated = true;
  }
  return db;
}

function getSlack(): SlackClient {
  if (!slack) {
    const token = Deno.env.get("SLACK_BOT_TOKEN");
    if (!token) throw new Error("SLACK_BOT_TOKEN not set");
    slack = createSlackClient(token);
  }
  return slack;
}

export default async function handler(req: Request): Promise<Response> {
  if (req.method !== "POST") {
    return new Response("Method not allowed", { status: 405 });
  }

  const body = await req.text();
  const timestamp = req.headers.get("x-slack-request-timestamp") ?? "";
  const signature = req.headers.get("x-slack-signature") ?? "";
  const signingSecret = Deno.env.get("SLACK_SIGNING_SECRET") ?? "";

  const valid = await verifySlackSignature(body, timestamp, signature, signingSecret);
  if (!valid) {
    return new Response("Invalid signature", { status: 401 });
  }

  // Slack sends either URL-encoded (slash commands) or JSON (interactions)
  const contentType = req.headers.get("content-type") ?? "";

  if (contentType.includes("application/x-www-form-urlencoded")) {
    const params = new URLSearchParams(body);
    const payload = params.get("payload");

    if (payload) {
      // Interaction payload (modal submission, button click)
      return handleInteraction(JSON.parse(payload));
    }

    // Slash command
    const command = params.get("command");
    const text = params.get("text") ?? "";
    const triggerId = params.get("trigger_id") ?? "";
    const userId = params.get("user_id") ?? "";
    const userName = params.get("user_name") ?? "";
    const channelId = params.get("channel_id") ?? "";

    switch (command) {
      case "/logtime":
        return handleLogtime(text, triggerId, userId, userName, channelId);
      case "/timesheet":
        return handleTimesheet(text, userId, channelId);
      case "/timesheet-admin":
        return handleAdmin(text, userId, channelId);
      default:
        return new Response("Unknown command", { status: 200 });
    }
  }

  return new Response("", { status: 200 });
}

async function handleLogtime(
  text: string, triggerId: string, userId: string, userName: string, channelId: string
): Promise<Response> {
  const database = await getDb();

  if (text.trim() === "list") {
    const entries = await queries.getRecentEntries(database, userId, 5);
    // Enrich with task type names and grant codes
    const enriched = [];
    for (const entry of entries) {
      const taskTypes = await queries.getActiveTaskTypes(database);
      const grants = await queries.getActiveGrants(database);
      const taskType = taskTypes.find((t) => t.id === entry.taskTypeId);
      const grant = entry.grantId ? grants.find((g) => g.id === entry.grantId) : null;
      enriched.push({ ...entry, taskTypeName: taskType?.name ?? "Unknown", grantCode: grant?.code ?? null });
    }

    const blocks = buildRecentEntriesBlocks(enriched);
    await getSlack().postEphemeral(channelId, userId, "Your recent entries:", blocks);
    return new Response("", { status: 200 });
  }

  // Open the modal
  const taskTypes = await queries.getActiveTaskTypes(database);
  const projects = await queries.getActiveProjects(database);
  const grants = await queries.getActiveGrants(database);
  const today = new Date().toISOString().slice(0, 10);

  const modal = buildLogtimeModal(taskTypes, projects, grants, today);
  await getSlack().openModal(triggerId, modal);

  return new Response("", { status: 200 });
}

async function handleTimesheet(text: string, userId: string, channelId: string): Promise<Response> {
  const database = await getDb();
  const today = new Date().toISOString().slice(0, 10);
  const args = parseReportArgs(text, today);

  const data = await queries.getReportData(database, args.startDate, args.endDate, args.userId);
  const report = formatReport(data, args.startDate, args.endDate);

  await getSlack().postEphemeral(channelId, userId, report);
  return new Response("", { status: 200 });
}

async function handleAdmin(text: string, userId: string, channelId: string): Promise<Response> {
  const database = await getDb();
  const cmd = parseAdminCommand(text);
  let response = "";

  switch (cmd.action) {
    case "add":
      if (cmd.entity === "project") {
        await queries.addProject(database, cmd.args[0]);
        response = `Added project: ${cmd.args[0]}`;
      } else if (cmd.entity === "grant") {
        await queries.addGrant(database, cmd.args[0], cmd.args[1]);
        response = `Added grant: ${cmd.args[0]} (${cmd.args[1]})`;
      } else if (cmd.entity === "task") {
        await queries.addTaskType(database, cmd.args[0]);
        response = `Added task type: ${cmd.args[0]}`;
      } else if (cmd.entity === "intern") {
        await queries.addIntern(database, cmd.args[0], cmd.args[1]);
        response = `Registered intern: @${cmd.args[1]}`;
      }
      break;
    case "remove":
      if (cmd.entity === "project") await queries.deactivateProject(database, cmd.args[0]);
      else if (cmd.entity === "grant") await queries.deactivateGrant(database, cmd.args[0]);
      else if (cmd.entity === "task") await queries.deactivateTaskType(database, cmd.args[0]);
      else if (cmd.entity === "intern") await queries.removeIntern(database, cmd.args[0]);
      response = `Removed ${cmd.entity}: ${cmd.args[0]}`;
      break;
    case "list": {
      const projects = await queries.getActiveProjects(database);
      const grants = await queries.getActiveGrants(database);
      const taskTypes = await queries.getActiveTaskTypes(database);
      const interns = await queries.getInterns(database);
      response = [
        `*Projects:* ${projects.map((p) => p.name).join(", ") || "none"}`,
        `*Grants:* ${grants.map((g) => `${g.code} (${g.name})`).join(", ") || "none"}`,
        `*Task Types:* ${taskTypes.map((t) => t.name).join(", ") || "none"}`,
        `*Interns:* ${interns.map((i) => `@${i.slackUsername}`).join(", ") || "none"}`,
      ].join("\n");
      break;
    }
    case "reminder":
      if (cmd.entity === "on" || cmd.entity === "off") {
        await queries.setConfig(database, "reminder_enabled", cmd.entity === "on" ? "true" : "false");
        response = `Reminders ${cmd.entity}`;
      } else if (cmd.entity === "day" || cmd.entity === "time") {
        await queries.setConfig(database, `reminder_${cmd.entity}`, cmd.args[0]);
        response = `Reminder ${cmd.entity} set to: ${cmd.args[0]}`;
      }
      break;
    case "error":
      response = cmd.args[0];
      break;
  }

  await getSlack().postEphemeral(channelId, userId, response);
  return new Response("", { status: 200 });
}

async function handleInteraction(payload: Record<string, unknown>): Promise<Response> {
  const type = payload.type as string;
  const database = await getDb();

  if (type === "view_submission") {
    const view = payload.view as Record<string, unknown>;
    const callbackId = view.callback_id as string;
    const values = view.state as { values: Record<string, Record<string, { selected_option?: { value: string }; value?: string }>> };
    const user = payload.user as { id: string; username: string };

    const date = values.values.date_block.date_select.selected_option!.value;
    const minutes = Number(values.values.time_block.time_select.selected_option!.value);
    const taskTypeId = Number(values.values.task_block.task_select.selected_option!.value);
    const projectId = values.values.project_block.project_select.selected_option?.value
      ? Number(values.values.project_block.project_select.selected_option.value) : null;
    const grantId = values.values.grant_block.grant_select.selected_option?.value
      ? Number(values.values.grant_block.grant_select.selected_option.value) : null;
    const comment = values.values.comment_block.comment_input.value || null;

    if (callbackId === "edit_time_entry") {
      const meta = JSON.parse(view.private_metadata as string);
      await queries.updateTimeEntry(database, meta.entryId, { date, minutes, taskTypeId, projectId, grantId, comment });
    } else {
      await queries.createTimeEntry(database, {
        slackUserId: user.id, slackUsername: user.username,
        date, minutes, taskTypeId, projectId, grantId, comment,
      });
    }

    return new Response(JSON.stringify({ response_action: "clear" }), {
      headers: { "Content-Type": "application/json" },
    });
  }

  if (type === "block_actions") {
    const actions = payload.actions as Array<{ action_id: string; value: string }>;
    const action = actions[0];
    const user = payload.user as { id: string; username: string };
    const channel = (payload.channel as { id: string })?.id;

    if (action.action_id === "delete_entry") {
      await queries.softDeleteTimeEntry(database, Number(action.value));
      if (channel) {
        await getSlack().postEphemeral(channel, user.id, "Entry deleted.");
      }
    } else if (action.action_id === "edit_entry") {
      const entry = await queries.getTimeEntryById(database, Number(action.value));
      if (entry) {
        const taskTypes = await queries.getActiveTaskTypes(database);
        const projects = await queries.getActiveProjects(database);
        const grants = await queries.getActiveGrants(database);
        const today = new Date().toISOString().slice(0, 10);
        const triggerId = (payload as Record<string, string>).trigger_id;
        const modal = buildLogtimeModal(taskTypes, projects, grants, today, {
          id: entry.id, date: entry.date, minutes: entry.minutes,
          taskTypeId: entry.taskTypeId, projectId: entry.projectId, grantId: entry.grantId, comment: entry.comment,
        });
        await getSlack().openModal(triggerId, modal);
      }
    }
  }

  return new Response("", { status: 200 });
}

Step 2: Commit

git add src/main.ts
git commit -m "feat: HTTP router wiring all handlers together"

Task 14: Weekly Reminder Cron

Files:

Create: src/cron.ts
Create: src/handlers/reminder.ts
Create: src/handlers/reminder.test.ts

Step 1: Write failing test for reminder logic

// src/handlers/reminder.test.ts
import { describe, it, expect, beforeEach } from "vitest";
import { createTestDb } from "../db/test-helpers.js";
import { migrate } from "../db/schema.js";
import { addIntern, addTaskType, createTimeEntry } from "../db/queries.js";
import { getInternsWithoutEntries } from "./reminder.js";
import type { Database } from "../db/types.js";

describe("getInternsWithoutEntries", () => {
  let db: Database;

  beforeEach(async () => {
    db = await createTestDb();
    await migrate(db);
    await addTaskType(db, "Transcription");
  });

  it("returns interns who have not logged time in the date range", async () => {
    await addIntern(db, "U123", "maria");
    await addIntern(db, "U456", "james");

    // Only maria logs time
    await createTimeEntry(db, {
      slackUserId: "U123", slackUsername: "maria", date: "2026-02-24",
      minutes: 60, taskTypeId: 1, projectId: null, grantId: null, comment: null,
    });

    const missing = await getInternsWithoutEntries(db, "2026-02-23", "2026-03-01");
    expect(missing).toHaveLength(1);
    expect(missing[0].slackUserId).toBe("U456");
  });

  it("returns all interns when nobody logged time", async () => {
    await addIntern(db, "U123", "maria");
    await addIntern(db, "U456", "james");

    const missing = await getInternsWithoutEntries(db, "2026-02-23", "2026-03-01");
    expect(missing).toHaveLength(2);
  });

  it("returns empty when all interns logged time", async () => {
    await addIntern(db, "U123", "maria");
    await createTimeEntry(db, {
      slackUserId: "U123", slackUsername: "maria", date: "2026-02-24",
      minutes: 60, taskTypeId: 1, projectId: null, grantId: null, comment: null,
    });

    const missing = await getInternsWithoutEntries(db, "2026-02-23", "2026-03-01");
    expect(missing).toHaveLength(0);
  });
});

Step 2: Implement reminder logic

// src/handlers/reminder.ts
import type { Database } from "../db/types.js";
import type { Intern } from "../db/queries.js";

export async function getInternsWithoutEntries(
  db: Database,
  startDate: string,
  endDate: string
): Promise<Intern[]> {
  const result = await db.execute({
    sql: `SELECT i.slack_user_id, i.slack_username
          FROM interns i
          WHERE i.slack_user_id NOT IN (
            SELECT DISTINCT te.slack_user_id FROM time_entries te
            WHERE te.date >= ? AND te.date <= ? AND te.deleted_at IS NULL
          )
          ORDER BY i.slack_username`,
    args: [startDate, endDate],
  });
  return result.rows.map((r) => ({ slackUserId: r[0] as string, slackUsername: r[1] as string }));
}

Step 3: Implement cron.ts (Val Town cron val)

// src/cron.ts
import { getConfig, getInterns } from "./db/queries.js";
import { getInternsWithoutEntries } from "./handlers/reminder.js";
import { getWeekBounds } from "./lib/format.js";
import { createSlackClient } from "./slack/api.js";
import { migrate } from "./db/schema.js";
import type { Database } from "./db/types.js";

declare const Deno: { env: { get(key: string): string | undefined } };

export default async function reminder() {
  // Dynamic import for Val Town runtime
  const { sqlite } = await import("https://esm.town/v/std/sqlite");
  const db: Database = sqlite;
  await migrate(db);

  const enabled = await getConfig(db, "reminder_enabled");
  if (enabled === "false") return;

  const token = Deno.env.get("SLACK_BOT_TOKEN");
  if (!token) return;
  const slack = createSlackClient(token);

  const today = new Date().toISOString().slice(0, 10);
  const { start, end } = getWeekBounds(today);
  const missing = await getInternsWithoutEntries(db, start, end);

  for (const intern of missing) {
    await slack.sendDm(
      intern.slackUserId,
      `Hey! You haven't logged any time this week. Use /logtime to log your hours before end of day.`
    );
  }
}

Step 4: Run tests, commit

Run: npx vitest run Expected: ALL PASS

git add src/handlers/reminder.ts src/handlers/reminder.test.ts src/cron.ts
git commit -m "feat(cron): weekly reminder for interns without time entries"

Task 15: Val Town Deployment & Slack App Setup

This task is manual — no TDD. It's configuration and integration testing.

Step 1: Install Val Town CLI

deno install -grAf jsr:@valtown/vt
vt  # authenticate

Step 2: Create Val Town project

cd /Users/trevormunoz/Code/lakeland-timesheet
vt create lakeland-timesheet
# Or if project already exists:
# vt clone <your-username>/lakeland-timesheet

Step 3: Push code to Val Town

vt push

Step 4: Create Slack App at api.slack.com/apps

Create new app → "From scratch"
Name: "Lakeland Timesheet"
Select your workspace

Step 5: Configure OAuth scopes

Bot Token Scopes: commands, chat:write, users:read, im:write

Step 6: Register slash commands

All three point to your Val Town HTTP URL:

/logtime — "Log time worked"
/timesheet — "Run a timesheet report"
/timesheet-admin — "Manage timesheet configuration"

Step 7: Enable interactivity

Request URL: same Val Town HTTP URL

Step 8: Install to workspace

Copy the Bot User OAuth Token (xoxb-...)

Step 9: Set Val Town environment variables

In Val Town project settings:

SLACK_BOT_TOKEN = xoxb-...
SLACK_SIGNING_SECRET = (from Slack app Basic Information page)

Step 10: Set up cron val

In Val Town, set cron.ts to run on schedule: 0 20 * * 5 (Friday 8pm UTC = 3pm ET)

Step 11: Seed initial data

In Slack:

/timesheet-admin add task "Transcription"
/timesheet-admin add task "Metadata Editing"
/timesheet-admin add task "Oral History"
/timesheet-admin add project "Lakeland Digital Archive"
/timesheet-admin add grant "NEH-PW-290261" "NEH Preservation & Access"
/timesheet-admin reminder on

Step 12: Smoke test

/logtime → verify modal opens with all dropdowns populated
Submit an entry → verify confirmation with Edit/Delete buttons
/logtime list → verify recent entries appear
Edit an entry → verify modal pre-fills
Delete an entry → verify it disappears from list
/timesheet → verify report shows your entry
/timesheet-admin list → verify all config displays

Step 13: Commit any deployment adjustments

git add -A && git commit -m "chore: deployment configuration adjustments"

Summary

Task	What	Tests
1	Project scaffold, Vitest, DB abstraction	—
2	Schema migration	2 tests
3	Lookup table CRUD	6 tests
4	Time entry CRUD	5 tests
5	Intern management + config	5 tests
6	Duration/date formatting	~8 tests
7	Admin command parser	9 tests
8	Report query + formatter	4 tests
9	Report argument parser	4 tests
10	Slack signature verification	3 tests
11	Block Kit modal builder	4 tests
12	Slack API client (thin glue)	—
13	HTTP router (main.ts)	—
14	Weekly reminder cron	3 tests
15	Deployment + Slack app setup	Manual smoke test

Total: ~53 automated tests, 15 tasks, ~15 commits

trevormunoz

lakeland-timesheet

Lakeland Timesheet Implementation Plan

Development Environment

Task 1: Project Scaffold & Tooling

Task 2: Database Schema & Migration

Task 3: Database Queries — Lookup Table CRUD

Task 4: Database Queries — Time Entries

Task 5: Database Queries — Intern Management & Config

Task 6: Duration & Date Formatting

Task 7: Admin Command Parser

Task 8: Report Generation

Task 9: Report Command Argument Parser

Task 10: Slack Signature Verification

Task 11: Block Kit Modal Builder

Task 12: Slack API Client

Task 13: HTTP Router (main.ts)

Task 14: Weekly Reminder Cron

Task 15: Val Town Deployment & Slack App Setup

Summary