Cron-based scheduled prompts with multi-channel delivery and web management UI.
Add the ability to create recurring automated investigations in Sherlog. Users define a name, prompt, cron schedule, and optionally a Slack channel for delivery. Sherlog executes the prompt on schedule and delivers results.
Inspired by: Codex App automations
- Cron-native: Standard 5-field cron expressions under the hood, with a friendly UI on top
- Stable across releases: Persisted in SQLite, survives restarts, no in-memory-only state
- Multi-channel: Create and view automations from Slack or Web; results delivered to configured channel
- Natural language: "Check PostHog stats every weekday at 9am" → Sherlog parses and creates the automation
- Fresh context per run: Each run gets a brand-new OpenCode session — always starts from the raw prompt, no accumulated context that could drift behavior over time
Goal: Automations can be created (via DB insert) and executed on schedule.
File: packages/db/src/schema.ts
Add two new tables:
// --- Types ---
export type AutomationSchedule = {
mode: "cron";
cron: string; // 5-field: minute hour dom month dow
timezone: string; // IANA, e.g. "America/Sao_Paulo"
};
// Target is optional Slack delivery. If absent, results are web-only.
export type AutomationTarget =
| { source: "slack"; channel: string } // Slack channel ID
| { source: "web" };
// --- Tables ---
export const automations = sqliteTable(
"automations",
{
id: text("id").primaryKey(), // ULID
name: text("name").notNull(),
prompt: text("prompt").notNull(),
schedule: text("schedule", { mode: "json" })
.$type<AutomationSchedule>().notNull(),
status: text("status").notNull()
.$type<"enabled" | "disabled">().default("enabled"),
ownerEmail: text("owner_email").notNull(),
target: text("target", { mode: "json" })
.$type<AutomationTarget>().notNull(),
lastRunAt: integer("last_run_at", { mode: "timestamp_ms" }),
nextRunAt: integer("next_run_at", { mode: "timestamp_ms" }),
metadata: text("metadata", { mode: "json" })
.$type<Record<string, unknown>>(),
createdAt: integer("created_at", { mode: "timestamp_ms" }).notNull(),
updatedAt: integer("updated_at", { mode: "timestamp_ms" }).notNull(),
},
(table) => [
index("automations_status_next_run_idx").on(table.status, table.nextRunAt),
index("automations_owner_email_idx").on(table.ownerEmail),
]
);
export const automationRuns = sqliteTable(
"automation_runs",
{
id: text("id").primaryKey(), // ULID
automationId: text("automation_id").notNull()
.references(() => automations.id),
jobId: text("job_id").references(() => jobs.id), // nullable until job created
status: text("status").notNull()
.$type<"running" | "completed" | "failed" | "skipped">().default("running"),
response: text("response"),
errorMessage: text("error_message"),
startedAt: integer("started_at", { mode: "timestamp_ms" }).notNull(),
completedAt: integer("completed_at", { mode: "timestamp_ms" }),
},
(table) => [
index("automation_runs_automation_id_idx").on(table.automationId),
index("automation_runs_started_at_idx").on(table.startedAt),
]
);Design decisions:
scheduleis JSON withmodediscriminator — extensible to{ mode: "interval", minutes: number }later without migrationtargetdefaults to{ source: "web" }if no Slack channel specifiedstatusisenabled/disabled(soft delete, no hard deletes)nextRunAtpre-computed for efficient scheduling queries (composite index with status)- Runs link to automations and optionally to jobs
- No Linear target — doesn't make sense for scheduled automations
Migration:
cd packages/db && bun run db:generate && bun run db:migrateExport new tables and types from packages/db/src/index.ts.
File: packages/worker/src/index.ts
New dependency: croner — zero-dep, Bun-native, timezone-aware cron parser.
cd packages/worker && bun add cronerArchitecture: Separate startAutomationLoop() running alongside existing pollForJobs().
| Concern | Decision |
|---|---|
| Poll interval | 30s (AUTOMATION_POLL_INTERVAL_MS) — cron minimum is 1 minute |
| Missed runs | Skip and advance to next future run (no catch-up) |
| Overlapping runs | Skip if previous run's job is still active |
| Failed runs | Log, move on — next scheduled tick is the retry |
| Thread key | automation:{automationId}:{runId} — fresh session every run |
| Shutdown | Shared shuttingDown flag, stop polling, let in-flight complete |
Critical: Fresh session per run
Each automation run uses a unique threadKey of format automation:{automationId}:{runId}. This means:
- Every run creates a brand new OpenCode session
- The prompt is always executed from scratch with no prior context
- Automations are stable and reproducible — no context drift between runs
- No risk of the AI "remembering" something from a previous run that changes behavior
Core flow:
startAutomationLoop() — every 30s:
1. SELECT * FROM automations WHERE status='enabled' AND next_run_at <= now()
2. For each due automation:
a. Check overlap: any active run (status="running") for this automation?
→ Yes: log "skipped", create run with status="skipped", advance next_run_at
→ No: continue
b. Create run record (status="running", startedAt=now)
c. Create job in jobs table:
- source: target.source (slack or web)
- threadKey: "automation:{automationId}:{runId}" ← unique per run
- messageText: automation.prompt ← always the raw prompt
- metadata: { automationId, automationRunId, channel? }
d. Update run with jobId
e. Compute next_run_at via croner and update automation
f. Update last_run_at
3. On error: log, advance next_run_at anyway (prevent infinite retry)
Overlap detection: Instead of checking active jobs by threadKey (which is now unique per run), check automationRuns for any run with status="running" for the same automationId.
Completion tracking: When a job completes (in the existing event loop), check if its metadata contains automationRunId. If so, update the corresponding automationRun with status, response, and completedAt.
Integration point — add to main() after reconciliation, before the polling loop:
void startAutomationLoop();| File | Action |
|---|---|
packages/db/src/schema.ts |
Add automations + automationRuns tables + types |
packages/db/src/index.ts |
Export new tables and types |
packages/worker/package.json |
Add croner dependency |
packages/worker/src/index.ts |
Add startAutomationLoop(), checkDueAutomations(), triggerAutomation(), advanceNextRun() |
packages/worker/src/index.ts |
Modify completion handler to update automation runs |
Goal: Full CRUD management of automations through the web interface.
File: packages/api/src/routes/api.ts
All endpoints require authentication (existing session middleware).
| Method | Path | Description |
|---|---|---|
GET |
/api/automations |
List automations (filters: mine, status) |
GET |
/api/automations/:id |
Get automation detail + recent runs |
POST |
/api/automations |
Create automation |
PATCH |
/api/automations/:id |
Update automation (name, prompt, schedule, target, status) |
DELETE |
/api/automations/:id |
Disable automation (status: "disabled") |
| Method | Path | Description |
|---|---|---|
POST |
/api/automations/:id/trigger |
Trigger immediate run |
POST |
/api/automations/:id/pause |
Set status: "disabled" |
POST |
/api/automations/:id/resume |
Set status: "enabled", recompute nextRunAt |
| Method | Path | Description |
|---|---|---|
GET |
/api/automations/:id/runs |
List runs for automation (paginated, newest first) |
{
name: string;
prompt: string;
schedule: {
mode: "cron";
cron: string; // "0 9 * * 1-5"
timezone: string; // "America/Sao_Paulo"
};
target?: // Optional — defaults to { source: "web" }
| { source: "slack"; channel: string }
| { source: "web" };
}Validation:
- Use
cronerto validate cron expressions before saving (return 400 if invalid) - Minimum interval: 5 minutes (reject
* * * * *or*/1 * * * *) - Per-user limit: 20 automations max
Framework: React + Tailwind (existing stack). Linear/Notion-inspired minimal design.
Add "Automations" as a new section in the sidebar, below sessions. Clock icon. Count badge for enabled automations.
Sidebar
├── Sessions (existing)
│ └── [session list]
├── ─────────── (divider)
└── Automations (new)
└── [automation list]
packages/web/src/components/automations/
├── AutomationList.tsx # List view with status, schedule, actions
├── AutomationCard.tsx # Individual row in list
├── AutomationModal.tsx # Create/Edit modal
├── AutomationDetail.tsx # Detail view with run history
├── AutomationRunList.tsx # Run history table
├── ScheduleInput.tsx # Daily/Interval toggle + time + day pickers
├── CronPreview.tsx # "Next 3 runs: ..." preview
└── TargetSelector.tsx # Optional Slack channel selector
┌─────────────────────────────────────────────┐
│ Create automation │
│ │
│ Name │
│ ┌───────────────────────────────────────────┐│
│ │ Check PostHog daily stats ││
│ └───────────────────────────────────────────┘│
│ │
│ Prompt │
│ ┌───────────────────────────────────────────┐│
│ │ Check PostHog for conversion funnel ││
│ │ trends and flag any significant changes ││
│ └───────────────────────────────────────────┘│
│ │
│ Schedule [Daily] [Interval]│
│ ┌──────┐ (Mo)(Tu)(We)(Th)(Fr) Sa Su │
│ │09:00 │ │
│ └──────┘ │
│ Next runs: Mon Feb 10 9:00AM, Tue Feb 11... │
│ │
│ Deliver to (optional) │
│ ┌───────────────────────────────────────────┐│
│ │ 🔽 #analytics (Slack) | Web only ││
│ └───────────────────────────────────────────┘│
│ │
│ Timezone │
│ ┌───────────────────────────────────────────┐│
│ │ 🔽 America/Sao_Paulo ││
│ └───────────────────────────────────────────┘│
│ │
│ [Cancel] [Create] │
└─────────────────────────────────────────────┘
Schedule input behavior:
- Daily mode: Time picker (HH:MM) + day-of-week toggles → generates cron like
0 9 * * 1-5 - Interval mode: "Every [ ] [minutes/hours ▾]" → generates cron like
*/30 * * * * - CronPreview shows next 3 runs computed via croner (loaded as dependency in web package too)
- User never sees raw cron syntax
Automation list view:
- Each row: name, human-readable schedule ("Weekdays at 9:00 AM"), status badge, last run (relative), next run
- Row actions: pause/resume toggle, edit, delete (confirm), run now
- Empty state: illustration + "No automations yet. Create one to run scheduled investigations."
Automation detail view:
- Header: name, schedule description, status, edit/delete buttons
- Run history table: timestamp, status badge (success/failed/skipped), response preview, duration
- Click row → expands full response (markdown rendered)
- Link to session thread if available
Automation badge in conversations:
When viewing a session whose threadKey starts with automation:, show:
⚡ Automated · Check PostHog daily stats · Weekdays at 9:00 AM
| File | Action |
|---|---|
packages/api/src/routes/api.ts |
Add automation CRUD + action endpoints |
packages/web/src/App.tsx |
Add automations route/view |
packages/web/src/components/Sidebar.tsx |
Add Automations nav section |
packages/web/src/components/automations/*.tsx |
Create all 8 components |
packages/web/package.json |
Add croner for client-side cron preview |
Goal: Automation results posted to Slack; automations created via natural language.
When an automation job completes and target.source === "slack":
The existing Slack processor handles posting, but needs a tweak: automation runs have no parent thread ts (they're new conversations). The processor should:
- Check if
metadata.tsis empty/missing - If so, post as a new message to
metadata.channel(not a thread reply) - Store the resulting message
tsin the job metadata (for reference)
Each automation run creates a new Slack message. No threading between runs — each is independent.
File: packages/worker/src/processors/slack.ts
Add tools the AI agent can call to create and manage automations. When a user says "check PostHog stats every weekday at 9am", the AI parses the intent and calls the tool.
Implementation: Bash-callable API wrapper (the worker runs on the same machine as the API).
Tool: create_automation
Description: Create a scheduled automation that runs a prompt on a cron schedule.
Parameters:
- name: string
- prompt: string
- cron: string (5-field cron expression)
- timezone: string (IANA, default UTC)
- target_source: "slack" | "web" (default "web")
- target_channel?: string (Slack channel ID, if target_source=slack)
Tool: list_automations
Description: List all active automations with their schedules and status.
Tool: manage_automation
Description: Pause, resume, delete, or trigger an automation.
Parameters:
- id: string
- action: "pause" | "resume" | "delete" | "trigger"
These tools call the REST API via curl to localhost. Add to the OpenCode agent's allowed bash patterns.
Natural language → cron mapping (add to agent system prompt / skill):
Parse natural language schedules into cron expressions:
- "every day at 9am" → "0 9 * * *"
- "weekdays at 9am" → "0 9 * * 1-5"
- "every Monday at 10:30" → "30 10 * * 1"
- "every 30 minutes" → "*/30 * * * *"
- "every hour" → "0 * * * *"
Always confirm the parsed schedule with the user before creating.
If no channel specified, default to web-only delivery.
Example conversation:
User: "Set up a daily check of PostHog funnels, weekdays at 9am SP time, in #analytics"
Sherlog: "I'll create an automation:
Name: PostHog funnel check
Schedule: Weekdays at 9:00 AM (America/Sao_Paulo)
Delivery: #analytics (Slack)
Next run: Mon Feb 10 at 9:00 AM
Shall I go ahead?"
User: "yes"
Sherlog: [calls create_automation] "Done! Automation created."
| File | Action |
|---|---|
packages/worker/src/processors/slack.ts |
Handle automated runs (no parent thread) |
Skill file (e.g., skills/automations/SKILL.md) |
Create automation management skill |
opencode.json |
Add allowed bash patterns for automation API |
Phase 1 (Core)
├── 1a. Schema + migration (automations, automationRuns)
├── 1b. Export types from db package
├── 1c. Add croner dependency to worker
├── 1d. Worker: startAutomationLoop + checkDueAutomations
├── 1e. Worker: triggerAutomation (creates job with unique threadKey per run)
├── 1f. Worker: completion handler updates automation runs
└── 1g. Manual testing via direct DB inserts
Phase 2 (Management)
├── 2a. API: automation CRUD endpoints
├── 2b. API: trigger/pause/resume actions
├── 2c. API: run history endpoint
├── 2d. Web: AutomationList + AutomationCard
├── 2e. Web: AutomationModal + ScheduleInput + CronPreview
├── 2f. Web: AutomationDetail + RunList
├── 2g. Web: Sidebar integration
└── 2h. Web: Automation badge on conversation view
Phase 3 (Channels + AI)
├── 3a. Slack processor: handle new-thread automated posts
├── 3b. AI skill: create/list/manage automation tools
├── 3c. System prompt: natural language → cron guidance
└── 3d. End-to-end testing across all channels
| Edge Case | Handling |
|---|---|
| Server restart | First poll picks up overdue automations, fires once, advances to next |
| Previous run still active | Skip, create run with status="skipped", advance schedule |
| Invalid cron expression | Validate on create/update; if invalid at runtime, disable + log |
| DST transitions | croner handles it; nextRunAt recomputed from cron+timezone |
| Slack channel deleted | Job fails, run logged as "failed", automation stays enabled |
| Per-user limit | 20 automations max, enforced at API |
| Minimum interval | 5 minutes minimum, enforced at API validation |
| Long-running job | Existing 10-minute timeout; next run skips if still active |
| Fresh context guarantee | Unique threadKey per run = new OpenCode session = no drift |
| Package | Where | Purpose |
|---|---|---|
croner |
packages/worker, packages/api, packages/web |
Cron parsing, validation, next-run preview |
No other new dependencies. Everything else uses existing infrastructure.
- Add tables in
schema.ts bun run db:generate→bun run db:migrate- Tables are additive — zero risk to existing data
- Deploy: update Docker images, restart worker + API
- Existing
jobsandsessionstables untouched — automations create new rows that flow through the same pipeline