Lakeland Timesheet — Slack App Design

A Slack app for interns working on Lakeland Digital Archive projects to log their hours in 15-minute increments, with optional project/grant/task categorization. Managers and interns can run reports of hours worked over configurable time periods.

Platform: Val Town (HTTP val + cron val) Storage: Val Town built-in SQLite (Turso) Team size: 4–10 interns across multiple projects/grants

CREATE TABLE projects (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  active INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE grants (
  id INTEGER PRIMARY KEY,
  code TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  active INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE task_types (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  active INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE 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 interns (
  slack_user_id TEXT PRIMARY KEY,
  slack_username TEXT NOT NULL,
  added_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE config (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL
);

• slack_user_id is the stable identity key; slack_username is cached for display and refreshed on each interaction.
• minutes stored as integer (multiples of 15). The 15-minute increment constraint is enforced in the modal UI, not the database.
• project_id and grant_id are nullable — an intern may log general work. task_type_id is required.
• Soft deletes via deleted_at on time entries and active flag on lookup tables — preserves audit trail for grant reporting.
• interns table defines who receives weekly reminders and scopes the default report population.
• config table stores reminder settings (day, time, enabled).

1. Intern types /logtime.
2. Slack POSTs to the Val Town HTTP endpoint with a trigger_id.
3. App calls views.open with a Block Kit modal:
   • Date — dropdown of last 7 days, defaults to today
   • Time — dropdown of 15-minute increments from 15m to 8h
   • Task — required dropdown (Transcription, Metadata Editing, Oral History, etc.)
   • Project — optional dropdown
   • Grant — optional dropdown
   • Comment — optional free-form text input
4. Intern submits. Slack POSTs view_submission to the same endpoint.
5. App saves to SQLite, responds with ephemeral confirmation: "Logged 1h of Transcription on Feb 26 (NEH-PW-290261) [Edit] [Delete]"

Shows the intern's last 5 entries with Edit and Delete buttons on each.

• Edit button reopens the modal pre-filled with the entry's values. Submission updates the row.
• Delete button shows a confirmation dialog, then soft-deletes (deleted_at set).

/timesheet                          → This week, all registered interns
/timesheet @intern                  → This week, specific intern
/timesheet 2026-02-01 2026-02-28   → Custom date range, all interns
/timesheet @intern 2026-02-01 2026-02-28 → Custom range, specific intern

Output as ephemeral message:

Timesheet Report: Feb 24 – Feb 28, 2026

@Maria — 20h
  Transcription      12h   (NEH-PW-290261)
  Metadata Editing    6h   (Mellon-41500)
  Oral History        2h

@James — 15h 30m
  Transcription       8h   (NEH-PW-290261)
  Metadata Editing    7h 30m

Total: 35h 30m across 2 interns

Default period is the current week (Monday–Sunday). Report groups by intern, then by task type, with grant shown where applicable.

/timesheet-admin add project "Community Oral Histories"
/timesheet-admin add grant "NEH-PW-290261" "NEH Preservation & Access"
/timesheet-admin add task "Scanning"
/timesheet-admin remove task "Scanning"
/timesheet-admin list

/timesheet-admin intern add @maria
/timesheet-admin intern remove @james

/timesheet-admin reminder day friday
/timesheet-admin reminder time 15:00
/timesheet-admin reminder off
/timesheet-admin reminder on

Text-based rather than modal — config changes are infrequent and a simple syntax is faster.

A separate Val Town cron val runs at the configured day/time (default: Friday 3:00 PM ET).

1. Query registered interns with no time_entries for the current week.
2. DM each via Slack: "You haven't logged any time this week. Use /logtime to log your hours."

Configurable via /timesheet-admin reminder commands. Can be toggled on/off.

Slack Platform
    |
    |  POST (slash commands, interactions)
    v
Main HTTP Val (request router)
    |
    ├── Verify Slack signature (HMAC-SHA256)
    ├── Parse payload type
    └── Route to handler:
        ├── command=/logtime      → open modal / list entries
        ├── command=/timesheet    → generate report
        ├── command=/timesheet-admin → CRUD lookup tables
        └── type=view_submission  → save/update time entry
        └── type=block_actions    → edit/delete button clicks

SQLite (Val Town built-in)

Cron Val (weekly reminder)
    └── Queries same SQLite, sends DMs via Slack API

lakeland-timesheet/
├── main.ts                  # HTTP handler + router
├── cron.ts                  # Weekly reminder cron val
├── slack/
│   ├── verify.ts            # Request signature verification
│   ├── modals.ts            # Block Kit modal definitions
│   └── api.ts               # Slack Web API calls
├── handlers/
│   ├── logtime.ts           # Open modal, list entries
│   ├── submission.ts        # Modal submission → save/update entry
│   ├── report.ts            # Report generation + formatting
│   └── admin.ts             # Admin CRUD for lookup tables + config
├── db/
│   ├── schema.ts            # CREATE TABLE statements + migration
│   └── queries.ts           # Parameterized SQL queries
└── lib/
    └── format.ts            # Duration formatting, date helpers

OAuth Scopes (Bot Token):

Slash Commands — all three point to the same Val Town HTTP endpoint URL. The router distinguishes by the command field.

Interactivity Request URL — same endpoint. Receives view_submission and block_actions payloads.

Environment Variables (Val Town Secrets):

• SLACK_BOT_TOKEN — xoxb-... from OAuth install
• SLACK_SIGNING_SECRET — for request signature verification

• 3-second rule: Slack requires acknowledgement within 3 seconds. /logtime calls views.open and returns 200 immediately. /timesheet may need to acknowledge first and post the report as a follow-up via response_url if generation takes too long.
• Val Town cron is UTC-based. The reminder schedule must account for ET (UTC-5 or UTC-4 depending on DST).
• 10MB SQLite on free tier. Thousands of timesheet entries fit easily — this is not a constraint at this scale.

• Inline slash command parsing (/logtime 1h transcription) for power users
• CSV export (/timesheet export 2026-02) for grant reporting
• App Home tab with persistent view of recent entries and weekly totals