Automation Architecture

This document describes the system architecture, component interactions, and data flow for the show.fm Automation Engine.

System Components

1. Frontend Layer (SvelteKit)

The frontend provides three main interfaces:

Interface	Route	Purpose
Automations Dashboard	/p/[slug]/automations	List, create, toggle, delete rules
Workflow Builder	/p/[slug]/automations/[id]	Visual step-based editor
Template Editor	/p/[slug]/templates	Create/edit email templates
Execution History	/p/[slug]/automations/history	View past executions

2. API Layer (Hono)

All API routes are mounted at /api/automations/* in the main Hono application:

// src/api/index.ts
.route('/automations', automationsRoutes)

The API layer handles:

• CRUD operations for templates, rules, and actions
• Authorization via get_podcast_role() RLS functions
• Request validation with Zod schemas
• Response formatting

3. Database Layer (Supabase/PostgreSQL)

Five core tables with RLS policies:

4. Worker Layer (Cloudflare Workers)

Two workers handle automation execution:

Scheduler Worker (automation-scheduler)

• Trigger: Cron every minute (* * * * *)
• Responsibility: Find due jobs, emit to queue
• Handles: Time-based triggers (before/after recording, publish, booking)

Executor Worker (automation-executor)

• Trigger: Queue consumer (automation-executions queue)
• Responsibility: Execute actions, record results
• Handles: Email sending, webhook calls, field updates
• Retries: 5 attempts with exponential backoff
• Dead Letter: Failed messages go to automation-dlq

Data Flow Diagrams

Event-Based Trigger Flow

When a booking is confirmed, episode is published, or guest responds:

Database-Backed Queue Pattern

The producer (events.ts) does NOT send messages directly to the queue. Instead, it writes execution records to the database. The Scheduler Worker polls for pending executions and sends queue messages to the Executor Worker.

Time-Based Trigger Flow

For triggers like "24 hours before recording":

Workflow Builder Data Flow

The builder is step-based (click to append; layout is derived; no drag-and-drop or manual connection drawing). When a user builds and saves an automation:

Saving is explicit (the Save/Create button); the top bar shows "Unsaved changes" / "All changes saved" from workflowStore.isDirty.

Component Architecture

State Management (Svelte 5 Runes)

Two module-level runes stores:

// workflow.svelte.ts - main state
// SvelteMap: in-place .set()/.delete()/.clear() are reactive (no $state wrap)
const nodes = new SvelteMap<string, WorkflowNode>();
const connections = new SvelteMap<string, WorkflowConnection>();
let selectedNodeId = $state<string | null>(null);
let isDirty = $state(false);

// panels.svelte.ts - side-panel collapse state (persisted)

Component Hierarchy

WorkflowBuilder
├── WorkflowTopBar (name, save status, enable toggle, Test/Save)
├── NodeLibrary (click a step type to append it)
├── FlowCanvas (auto-laid-out vertical flow, zoom, branch forks)
│   ├── FlowNodeCard (per step: icon, When/Then/If badge, summary, ⋮ menu)
│   └── AddStepMenu (dashed tail buttons + insert points)
└── NodeConfigPanel
    └── *Config Component (based on node type)
        ├── TriggerConfig
        ├── ActionEmailConfig
        ├── ActionWebhookConfig
        ├── ActionFieldUpdateConfig
        ├── ConditionConfig
        └── DelayConfig

See Workflow Builder for the full builder architecture (flow-model.ts render model, condition branch columns, unconnected-steps handling).

Security Architecture

Row-Level Security (RLS)

All automation tables use RLS policies based on podcast membership:

-- Example policy for automation_rules
CREATE POLICY "Users can manage automation rules for their podcasts"
ON automation_rules
FOR ALL
USING (
    get_podcast_role(podcast_id) IN ('owner', 'admin', 'editor')
);

The get_podcast_role(podcast_id) function checks the user's role in podcast_members.

API Authorization

All API endpoints verify the user has appropriate access:

// src/api/routes/automations/rules.ts
const role = await c.var.supabase.rpc('get_podcast_role', { p_podcast_id: podcast_id });
if (!role || !['owner', 'admin', 'editor'].includes(role)) {
	return c.json({ error: 'Unauthorized' }, 403);
}

Field Update Restrictions

The field update executor maintains an allowlist of safe fields:

// workers/automation-executor/src/executors/field-update.ts
const ALLOWED_FIELDS: Record<string, string[]> = {
	episodes: ['status', 'notes', 'internal_notes'],
	bookings: ['status', 'notes', 'internal_notes'],
	episode_guests: ['status', 'notes']
};

Reliability Architecture

Queue-Based Execution

Using Cloudflare Queues ensures:

• At-least-once delivery: Messages are retried until acknowledged
• Automatic retries: 5 attempts with exponential backoff
• Dead letter queue: Failed messages preserved for investigation
• Batch processing: Up to 25 messages per invocation

Idempotency

Executions use idempotency keys to prevent duplicates:

// Format: rule_id:trigger_type:podcast_id:episode_id:booking_id:guest_id:date
const key = `${ruleId}:${triggerType}:${podcastId}:${episodeId}:${bookingId}:${guestId}:${date}`;

The database has a unique constraint on idempotency_key in automation_executions.

Error Handling

Performance Considerations

Database Indexes

Key indexes for query performance:

-- Fast lookup of enabled rules by trigger type
CREATE INDEX idx_automation_rules_trigger
ON automation_rules(podcast_id, trigger_type, is_enabled);

-- Fast lookup of due scheduled jobs
CREATE INDEX idx_scheduled_jobs_due
ON automation_scheduled_jobs(scheduled_for, status);

-- Fast lookup of executions by status
CREATE INDEX idx_executions_status
ON automation_executions(podcast_id, status, created_at);

Hyperdrive Connection Pooling

Both workers use Cloudflare Hyperdrive for:

• Connection pooling (reduces connection overhead)
• Edge caching of query results
• Automatic failover

# wrangler.toml
[[hyperdrive]]
binding = "HYPERDRIVE"
id = "a81d477ff9264805989f5a72f0354ee8"

Batch Processing

The executor processes messages in batches:

[[queues.consumers]]
queue = "automation-executions"
max_batch_size = 25
max_batch_timeout = 30