Database Schema

This document details the database schema for the automation engine, including tables, enums, indexes, and Row-Level Security policies.

Migration File: supabase/migrations/20260107164436_automation_engine.sql

Enums

`automation_trigger_type`

Defines when an automation should fire:

sql

CREATE TYPE automation_trigger_type AS ENUM (
    -- Event-based triggers
    'booking.confirmed',      -- When a booking is confirmed
    'booking.declined',       -- When a booking is declined
    'booking.canceled',       -- When a booking is canceled
    'booking.rescheduled',    -- When a booking is rescheduled
    'episode.published',      -- When an episode is published
    'episode.scheduled',      -- When an episode is scheduled
    'episode.draft_created',  -- When an episode draft is created
    'guest.responded',        -- When a guest responds to questions
    'guest.reminder_sent',    -- When a reminder is sent to a guest

    -- Time-based triggers
    'time.before_recording',  -- X time before recording
    'time.after_recording',   -- X time after recording
    'time.before_publish',    -- X time before publish date
    'time.after_publish',     -- X time after publish date
    'time.after_booking'      -- X time after booking confirmed
);

`automation_action_type`

Defines what actions an automation can perform:

sql

CREATE TYPE automation_action_type AS ENUM (
    'send_email',     -- Send email via template
    'send_webhook',   -- HTTP POST to external URL
    'update_field',   -- Update a database field
    'delay'           -- Wait before next action (added 2026-01-09)
);

Note: The delay action pauses workflow execution for a specified duration. When encountered, the executor schedules a continuation job via automation_scheduled_jobs and sets execution status to waiting.

`automation_execution_status`

Tracks the state of an execution:

sql

CREATE TYPE automation_execution_status AS ENUM (
    'pending',      -- Created, waiting to be processed
    'processing',   -- Currently being executed
    'waiting',      -- Paused for delay action (added 2026-01-09)
    'completed',    -- Successfully completed
    'failed',       -- Failed after all retries
    'skipped',      -- Skipped (e.g., condition not met)
    'retrying'      -- Failed, will retry
);

Note: The waiting status indicates the execution is paused during a delay action. A scheduled job will resume execution from resume_from_index when the delay expires.

`time_unit`

Used for time-based trigger delays:

sql

CREATE TYPE time_unit AS ENUM (
    'minutes',
    'hours',
    'days'
);

Tables

`notification_templates`

Stores reusable email templates with magic tag support.

sql

CREATE TABLE notification_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    podcast_id UUID NOT NULL REFERENCES podcasts(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    description TEXT,
    category TEXT DEFAULT 'general',  -- 'booking', 'episode', 'guest', 'general'
    subject TEXT NOT NULL,            -- Email subject (supports magic tags)
    body_html TEXT NOT NULL,          -- HTML body (supports magic tags)
    body_text TEXT,                   -- Plain text fallback (auto-generated if null)
    is_system BOOLEAN DEFAULT FALSE,  -- System templates can't be deleted
    usage_count INT DEFAULT 0,        -- How many automations use this
    last_used_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id)
);

Example Record:

json

{
	"id": "abc123",
	"podcast_id": "podcast-uuid",
	"name": "Booking Confirmation",
	"category": "booking",
	"subject": "Recording Confirmed: {episode_title}",
	"body_html": "<p>Hi {guest_first_name},</p><p>Your recording is confirmed for {recording_date} at {recording_time}.</p>",
	"body_text": "Hi {guest_first_name}, Your recording is confirmed for {recording_date} at {recording_time}."
}

`automation_rules`

Core automation rule definitions.

sql

CREATE TABLE automation_rules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    podcast_id UUID NOT NULL REFERENCES podcasts(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    description TEXT,
    trigger_type automation_trigger_type NOT NULL,
    trigger_config JSONB DEFAULT '{}',    -- Additional trigger configuration
    workflow_data JSONB DEFAULT '{"nodes": [], "connections": []}',
    is_enabled BOOLEAN DEFAULT TRUE,
    is_paused BOOLEAN DEFAULT FALSE,
    -- Execution statistics
    total_executions INT DEFAULT 0,
    successful_executions INT DEFAULT 0,
    failed_executions INT DEFAULT 0,
    last_executed_at TIMESTAMPTZ,
    last_error TEXT,
    -- Rate limiting
    max_executions_per_hour INT DEFAULT 100,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    UNIQUE(podcast_id, name)
);

trigger_config Schema (for time-based triggers):

json

{
	"delay_value": 24,
	"delay_unit": "hours"
}

workflow_data Schema:

json

{
	"nodes": [
		{
			"id": "trigger_abc123",
			"type": "trigger",
			"position": { "x": 250, "y": 100 },
			"data": {
				"trigger_type": "booking.confirmed",
				"label": "Booking Confirmed"
			}
		},
		{
			"id": "action_def456",
			"type": "action",
			"position": { "x": 250, "y": 250 },
			"data": {
				"action_type": "send_email",
				"config": {
					"template_id": "template-uuid",
					"to": "guest"
				},
				"label": "Send Confirmation Email"
			}
		}
	],
	"connections": [
		{
			"id": "conn_xyz789",
			"sourceId": "trigger_abc123",
			"sourcePort": "output",
			"targetId": "action_def456",
			"targetPort": "input"
		}
	]
}

`automation_actions`

Individual actions within a workflow (denormalized from workflow_data for efficient execution queries).

sql

CREATE TABLE automation_actions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    rule_id UUID NOT NULL REFERENCES automation_rules(id) ON DELETE CASCADE,
    podcast_id UUID NOT NULL REFERENCES podcasts(id) ON DELETE CASCADE,
    node_id TEXT NOT NULL,                         -- ID in workflow_data
    action_type automation_action_type NOT NULL,
    action_config JSONB NOT NULL DEFAULT '{}',
    execution_order INT DEFAULT 0,
    condition JSONB,                               -- Optional condition for this action
    parent_node_id TEXT,                           -- For true/false branches from conditions
    branch_type TEXT,                              -- 'true' | 'false' | null
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(rule_id, node_id)
);

action_config Examples:

Email action:

json

{
	"template_id": "template-uuid",
	"to": "guest", // "guest", "host", or email address
	"to_override": null, // Override recipient
	"cc_emails": "[email protected]"
}

Webhook action:

json

{
	"url": "https://api.example.com/webhook",
	"method": "POST",
	"headers": {
		"Authorization": "Bearer token123"
	},
	"payload_template": "{\"guest\": \"{guest_full_name}\", \"episode\": \"{episode_title}\"}"
}

Field update action:

json

{
	"table": "episodes",
	"field": "status",
	"value": "confirmed",
	"record_id_source": "episode_id"
}

Delay action:

json

{
	"delay_value": 24,
	"delay_unit": "hours" // "minutes", "hours", or "days"
}

`automation_scheduled_jobs`

Queue of scheduled automation jobs for time-based triggers. The scheduler worker queries this table for due jobs.

sql

CREATE TABLE automation_scheduled_jobs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    rule_id UUID NOT NULL REFERENCES automation_rules(id) ON DELETE CASCADE,
    podcast_id UUID NOT NULL REFERENCES podcasts(id) ON DELETE CASCADE,
    episode_id UUID REFERENCES episodes(id) ON DELETE CASCADE,
    booking_id UUID REFERENCES bookings(id) ON DELETE CASCADE,
    guest_id UUID REFERENCES episode_guests(id) ON DELETE CASCADE,
    scheduled_for TIMESTAMPTZ NOT NULL,
    timezone TEXT NOT NULL DEFAULT 'UTC',          -- Original timezone for reference
    idempotency_key TEXT NOT NULL UNIQUE,          -- Prevents duplicate scheduling
    status automation_execution_status DEFAULT 'pending',
    attempts INT DEFAULT 0,
    max_attempts INT DEFAULT 3,
    last_attempt_at TIMESTAMPTZ,
    next_retry_at TIMESTAMPTZ,
    error_message TEXT,
    error_details JSONB,
    context_snapshot JSONB DEFAULT '{}',           -- Snapshot of magic tag data at scheduling time
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    completed_at TIMESTAMPTZ,
    executed_at TIMESTAMPTZ                        -- When job was actually executed (added 2026-01-09)
);

context_snapshot for delay continuations: When scheduling a continuation job for a delay action, the snapshot includes resume_from_index to indicate which action to resume from.

`automation_executions`

Detailed execution history and logs for all automation runs.

sql

CREATE TABLE automation_executions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    rule_id UUID NOT NULL REFERENCES automation_rules(id) ON DELETE CASCADE,
    podcast_id UUID NOT NULL REFERENCES podcasts(id) ON DELETE CASCADE,
    scheduled_job_id UUID REFERENCES automation_scheduled_jobs(id) ON DELETE SET NULL,
    episode_id UUID REFERENCES episodes(id) ON DELETE SET NULL,
    booking_id UUID REFERENCES bookings(id) ON DELETE SET NULL,
    guest_id UUID REFERENCES episode_guests(id) ON DELETE SET NULL,
    trigger_type automation_trigger_type NOT NULL,
    trigger_event_data JSONB,             -- Event payload that triggered this
    status automation_execution_status DEFAULT 'pending',
    started_at TIMESTAMPTZ DEFAULT NOW(),
    completed_at TIMESTAMPTZ,
    duration_ms INT,                      -- Execution duration
    action_results JSONB DEFAULT '[]',    -- Results from each action
    error_message TEXT,
    error_details JSONB,
    context_data JSONB,                   -- Magic tag data used (for debugging)
    resume_from_index INT DEFAULT 0,      -- Action index to resume from (for delays)
    idempotency_key TEXT,                 -- Prevents duplicate processing
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Columns added 2026-01-09:

resume_from_index: When a workflow encounters a delay action, this stores which action to resume from when the delay expires.
idempotency_key: Prevents duplicate execution when the same event is processed multiple times (e.g., queue retries).

action_results Example:

json

[
	{
		"action_type": "send_email",
		"status": "completed",
		"result": {
			"message_id": "resend-msg-123",
			"to": "[email protected]"
		}
	},
	{
		"action_type": "send_webhook",
		"status": "completed",
		"result": {
			"status_code": 200,
			"response_time_ms": 245
		}
	}
]

`automation_rate_limits`

Rate limiting tracking to prevent abuse. Tracks execution counts per time window.

sql

CREATE TABLE automation_rate_limits (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    podcast_id UUID NOT NULL REFERENCES podcasts(id) ON DELETE CASCADE,
    rule_id UUID REFERENCES automation_rules(id) ON DELETE CASCADE,
    window_start TIMESTAMPTZ NOT NULL,
    window_duration_minutes INT NOT NULL DEFAULT 60,
    -- Counters
    execution_count INT DEFAULT 0,
    email_count INT DEFAULT 0,
    webhook_count INT DEFAULT 0,
    -- Limits (denormalized for quick checks)
    max_executions INT DEFAULT 100,
    max_emails INT DEFAULT 50,
    max_webhooks INT DEFAULT 200,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(podcast_id, rule_id, window_start)
);

Indexes

sql

-- Templates
CREATE INDEX idx_notification_templates_podcast ON notification_templates(podcast_id);
CREATE INDEX idx_notification_templates_category ON notification_templates(podcast_id, category);

-- Rules
CREATE INDEX idx_automation_rules_podcast ON automation_rules(podcast_id);
CREATE INDEX idx_automation_rules_enabled ON automation_rules(podcast_id, is_enabled) WHERE is_enabled = TRUE;
CREATE INDEX idx_automation_rules_trigger ON automation_rules(trigger_type) WHERE is_enabled = TRUE;
CREATE INDEX idx_automation_rules_time_based ON automation_rules(podcast_id)
  WHERE is_enabled = TRUE
  AND trigger_type IN ('time.before_recording', 'time.after_recording', 'time.before_publish', 'time.after_publish', 'time.after_booking');

-- Actions
CREATE INDEX idx_automation_actions_rule ON automation_actions(rule_id);
CREATE INDEX idx_automation_actions_type ON automation_actions(action_type);
CREATE INDEX idx_automation_actions_order ON automation_actions(rule_id, execution_order);

-- Scheduled Jobs
CREATE INDEX idx_scheduled_jobs_due ON automation_scheduled_jobs(scheduled_for, status)
  WHERE status = 'pending';
CREATE INDEX idx_scheduled_jobs_podcast ON automation_scheduled_jobs(podcast_id);
CREATE INDEX idx_scheduled_jobs_rule ON automation_scheduled_jobs(rule_id);
CREATE INDEX idx_scheduled_jobs_status ON automation_scheduled_jobs(status);
CREATE INDEX idx_scheduled_jobs_retry ON automation_scheduled_jobs(next_retry_at, status)
  WHERE status = 'retrying';
CREATE INDEX idx_scheduled_jobs_episode ON automation_scheduled_jobs(episode_id)
  WHERE episode_id IS NOT NULL;
CREATE INDEX idx_scheduled_jobs_booking ON automation_scheduled_jobs(booking_id)
  WHERE booking_id IS NOT NULL;

-- Executions
CREATE INDEX idx_automation_executions_rule ON automation_executions(rule_id);
CREATE INDEX idx_automation_executions_podcast ON automation_executions(podcast_id);
CREATE INDEX idx_automation_executions_status ON automation_executions(status);
CREATE INDEX idx_automation_executions_time ON automation_executions(created_at DESC);
CREATE INDEX idx_automation_executions_podcast_time ON automation_executions(podcast_id, created_at DESC);
CREATE INDEX idx_automation_executions_job ON automation_executions(scheduled_job_id)
  WHERE scheduled_job_id IS NOT NULL;

-- Rate Limits
CREATE INDEX idx_rate_limits_window ON automation_rate_limits(podcast_id, window_start);
CREATE INDEX idx_rate_limits_rule ON automation_rate_limits(rule_id, window_start)
  WHERE rule_id IS NOT NULL;

Row-Level Security Policies

All tables have RLS enabled with policies based on podcast membership using get_podcast_role() and has_podcast_role() helper functions.

Templates Policies

sql

-- Team members can view templates
CREATE POLICY "Team can view notification templates"
ON notification_templates FOR SELECT
USING (get_podcast_role(podcast_id) IS NOT NULL);

-- Staff (admin/owner) can create templates
CREATE POLICY "Staff can create notification templates"
ON notification_templates FOR INSERT
WITH CHECK (has_podcast_role(podcast_id, 'admin'));

-- Staff can update templates
CREATE POLICY "Staff can update notification templates"
ON notification_templates FOR UPDATE
USING (has_podcast_role(podcast_id, 'admin'));

-- Staff can delete templates (except system templates)
CREATE POLICY "Staff can delete notification templates"
ON notification_templates FOR DELETE
USING (has_podcast_role(podcast_id, 'admin') AND is_system = FALSE);

Rules Policies

sql

-- Team members can view automation rules
CREATE POLICY "Team can view automation rules"
ON automation_rules FOR SELECT
USING (get_podcast_role(podcast_id) IS NOT NULL);

-- Staff can create/update/delete automation rules
CREATE POLICY "Staff can create automation rules"
ON automation_rules FOR INSERT
WITH CHECK (has_podcast_role(podcast_id, 'admin'));

CREATE POLICY "Staff can update automation rules"
ON automation_rules FOR UPDATE
USING (has_podcast_role(podcast_id, 'admin'));

CREATE POLICY "Staff can delete automation rules"
ON automation_rules FOR DELETE
USING (has_podcast_role(podcast_id, 'admin'));

Actions Policies

sql

-- Team members can view automation actions
CREATE POLICY "Team can view automation actions"
ON automation_actions FOR SELECT
USING (get_podcast_role(podcast_id) IS NOT NULL);

-- Staff can manage automation actions
CREATE POLICY "Staff can create automation actions"
ON automation_actions FOR INSERT
WITH CHECK (has_podcast_role(podcast_id, 'admin'));

CREATE POLICY "Staff can update automation actions"
ON automation_actions FOR UPDATE
USING (has_podcast_role(podcast_id, 'admin'));

CREATE POLICY "Staff can delete automation actions"
ON automation_actions FOR DELETE
USING (has_podcast_role(podcast_id, 'admin'));

Scheduled Jobs Policies

sql

-- Team members can view scheduled jobs
CREATE POLICY "Team can view scheduled jobs"
ON automation_scheduled_jobs FOR SELECT
USING (get_podcast_role(podcast_id) IS NOT NULL);

-- Staff can manage scheduled jobs
CREATE POLICY "Staff can create scheduled jobs"
ON automation_scheduled_jobs FOR INSERT
WITH CHECK (has_podcast_role(podcast_id, 'admin'));

CREATE POLICY "Staff can update scheduled jobs"
ON automation_scheduled_jobs FOR UPDATE
USING (has_podcast_role(podcast_id, 'admin'));

CREATE POLICY "Staff can delete scheduled jobs"
ON automation_scheduled_jobs FOR DELETE
USING (has_podcast_role(podcast_id, 'admin'));

Executions Policies

sql

-- Team members can view execution history
CREATE POLICY "Team can view automation executions"
ON automation_executions FOR SELECT
USING (get_podcast_role(podcast_id) IS NOT NULL);

-- Workers use service role key which bypasses RLS for inserts/updates

Rate Limits Policies

sql

-- Team members can view rate limits
CREATE POLICY "Team can view rate limits"
ON automation_rate_limits FOR SELECT
USING (get_podcast_role(podcast_id) IS NOT NULL);

-- System manages rate limits (workers use service role)

Trigger Functions

Auto-update `updated_at`

All automation tables have triggers to auto-update the updated_at timestamp:

sql

CREATE TRIGGER trigger_notification_templates_updated_at
  BEFORE UPDATE ON notification_templates
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trigger_automation_rules_updated_at
  BEFORE UPDATE ON automation_rules
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trigger_automation_actions_updated_at
  BEFORE UPDATE ON automation_actions
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trigger_scheduled_jobs_updated_at
  BEFORE UPDATE ON automation_scheduled_jobs
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trigger_rate_limits_updated_at
  BEFORE UPDATE ON automation_rate_limits
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

Update Rule Statistics

Automatically updates rule execution statistics after each execution completes:

sql

CREATE OR REPLACE FUNCTION update_automation_rule_stats()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.status IN ('completed', 'failed') AND (OLD.status IS NULL OR OLD.status NOT IN ('completed', 'failed')) THEN
    UPDATE automation_rules
    SET
      total_executions = total_executions + 1,
      successful_executions = successful_executions + CASE WHEN NEW.status = 'completed' THEN 1 ELSE 0 END,
      failed_executions = failed_executions + CASE WHEN NEW.status = 'failed' THEN 1 ELSE 0 END,
      last_executed_at = NEW.completed_at,
      last_error = CASE WHEN NEW.status = 'failed' THEN NEW.error_message ELSE last_error END,
      updated_at = NOW()
    WHERE id = NEW.rule_id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER trigger_update_rule_stats
  AFTER INSERT OR UPDATE ON automation_executions
  FOR EACH ROW EXECUTE FUNCTION update_automation_rule_stats();

Update Template Usage Count

Tracks template usage when used in email actions:

sql

CREATE OR REPLACE FUNCTION update_template_usage()
RETURNS TRIGGER AS $$
DECLARE
  template_uuid UUID;
BEGIN
  IF NEW.action_type = 'send_email' AND NEW.action_config ? 'template_id' THEN
    template_uuid := (NEW.action_config->>'template_id')::UUID;
    UPDATE notification_templates
    SET usage_count = usage_count + 1, last_used_at = NOW(), updated_at = NOW()
    WHERE id = template_uuid;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER trigger_update_template_usage
  AFTER INSERT ON automation_actions
  FOR EACH ROW EXECUTE FUNCTION update_template_usage();

Helper Functions

Idempotency Check

sql

-- Check if a scheduled job already exists (prevents duplicates)
CREATE OR REPLACE FUNCTION automation_job_exists(p_idempotency_key TEXT)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM automation_scheduled_jobs
    WHERE idempotency_key = p_idempotency_key
    AND status NOT IN ('failed', 'skipped')
  )
$$ LANGUAGE sql SECURITY DEFINER STABLE;

Get Due Jobs

sql

-- Returns due scheduled jobs with row locking for safe concurrent processing
CREATE OR REPLACE FUNCTION get_due_automation_jobs(p_limit INT DEFAULT 100)
RETURNS SETOF automation_scheduled_jobs AS $$
  SELECT * FROM automation_scheduled_jobs
  WHERE status = 'pending'
  AND scheduled_for <= NOW()
  ORDER BY scheduled_for ASC
  LIMIT p_limit
  FOR UPDATE SKIP LOCKED
$$ LANGUAGE sql SECURITY DEFINER;

-- Returns jobs ready for retry
CREATE OR REPLACE FUNCTION get_retry_automation_jobs(p_limit INT DEFAULT 50)
RETURNS SETOF automation_scheduled_jobs AS $$
  SELECT * FROM automation_scheduled_jobs
  WHERE status = 'retrying'
  AND next_retry_at <= NOW()
  AND attempts < max_attempts
  ORDER BY next_retry_at ASC
  LIMIT p_limit
  FOR UPDATE SKIP LOCKED
$$ LANGUAGE sql SECURITY DEFINER;

Rate Limiting Functions

sql

-- Check rate limit (returns TRUE if within limits)
CREATE OR REPLACE FUNCTION check_automation_rate_limit(
  p_podcast_id UUID,
  p_rule_id UUID DEFAULT NULL,
  p_action_type TEXT DEFAULT 'execution'
) RETURNS BOOLEAN AS $$
  -- Returns TRUE if execution count is within limits for the current hour window
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Increment rate limit counter
CREATE OR REPLACE FUNCTION increment_automation_rate_limit(
  p_podcast_id UUID,
  p_rule_id UUID DEFAULT NULL,
  p_action_type TEXT DEFAULT 'execution'
) RETURNS VOID AS $$
  -- Increments the appropriate counter (execution_count, email_count, or webhook_count)
$$ LANGUAGE plpgsql SECURITY DEFINER;

Get Active Rules

sql

-- Get active automation rules for a trigger type
CREATE OR REPLACE FUNCTION get_active_automation_rules(
  p_podcast_id UUID,
  p_trigger_type automation_trigger_type
)
RETURNS SETOF automation_rules AS $$
  SELECT * FROM automation_rules
  WHERE podcast_id = p_podcast_id
  AND trigger_type = p_trigger_type
  AND is_enabled = TRUE
  AND is_paused = FALSE
$$ LANGUAGE sql SECURITY DEFINER STABLE;

Seed Default Templates

sql

-- Creates default system notification templates for a podcast
CREATE OR REPLACE FUNCTION seed_default_notification_templates(p_podcast_id UUID)
RETURNS VOID AS $$
  -- Creates: Guest Booking Confirmed, Recording Reminder - 24 Hours, Episode Published - Guest Notification
$$ LANGUAGE plpgsql SECURITY DEFINER;

Migration Notes

Rolling Back

To remove the automation engine:

sql

-- Drop tables (in order due to FK constraints)
DROP TABLE IF EXISTS automation_rate_limits;
DROP TABLE IF EXISTS automation_executions;
DROP TABLE IF EXISTS automation_scheduled_jobs;
DROP TABLE IF EXISTS automation_actions;
DROP TABLE IF EXISTS automation_rules;
DROP TABLE IF EXISTS notification_templates;

-- Drop enums
DROP TYPE IF EXISTS time_unit;
DROP TYPE IF EXISTS automation_execution_status;
DROP TYPE IF EXISTS automation_action_type;
DROP TYPE IF EXISTS automation_trigger_type;

Adding to Existing Database

The migration is designed to be additive and won't affect existing tables. Run via:

bash

supabase db push
# or
supabase migration up

Database Schema ​

Enums ​

automation_trigger_type ​

automation_action_type ​

automation_execution_status ​

time_unit ​

Tables ​

notification_templates ​

automation_rules ​

automation_actions ​

automation_scheduled_jobs ​

automation_executions ​

automation_rate_limits ​

Indexes ​

Row-Level Security Policies ​

Templates Policies ​

Rules Policies ​

Actions Policies ​

Scheduled Jobs Policies ​

Executions Policies ​

Rate Limits Policies ​

Trigger Functions ​

Auto-update updated_at ​

Update Rule Statistics ​

Update Template Usage Count ​

Helper Functions ​

Idempotency Check ​

Get Due Jobs ​

Rate Limiting Functions ​

Get Active Rules ​

Seed Default Templates ​

Migration Notes ​

Rolling Back ​

Adding to Existing Database ​

Database Schema

Enums

`automation_trigger_type`

`automation_action_type`

`automation_execution_status`

`time_unit`

Tables

`notification_templates`

`automation_rules`

`automation_actions`

`automation_scheduled_jobs`

`automation_executions`

`automation_rate_limits`

Indexes

Row-Level Security Policies

Templates Policies

Rules Policies

Actions Policies

Scheduled Jobs Policies

Executions Policies

Rate Limits Policies

Trigger Functions

Auto-update `updated_at`

Update Rule Statistics

Update Template Usage Count

Helper Functions

Idempotency Check

Get Due Jobs

Rate Limiting Functions

Get Active Rules

Seed Default Templates

Migration Notes

Rolling Back

Adding to Existing Database