-- Migration: Schema Audit Fixes
-- Fixes: Non-functional triggers (S-H2), missing RLS policies (S-H3), remaining indexes
-- Date: 2026-02-13
-- ============================================================================
-- S-H3 FIX: Missing service_role RLS policies
-- Without these, direct service_role access fails on these tables
-- ============================================================================
-- workflow_approval_requests — needs service_role bypass
DO $$ BEGIN
IF NOT EXISTS (
SELECT 1 FROM pg_policies WHERE tablename = 'workflow_approval_requests' AND policyname = 'approval_service_role'
) THEN
CREATE POLICY approval_service_role ON workflow_approval_requests FOR ALL
USING (true) WITH CHECK (true);
END IF;
END $$;
-- workflow_versions — needs service_role bypass
DO $$ BEGIN
IF NOT EXISTS (
SELECT 1 FROM pg_policies WHERE tablename = 'workflow_versions' AND policyname = 'versions_service_role'
) THEN
CREATE POLICY versions_service_role ON workflow_versions FOR ALL
USING (true) WITH CHECK (true);
END IF;
END $$;
-- ============================================================================
-- S-H2 FIX: Schedule/condition triggers read from platform_secrets
-- The old functions used current_setting('app.fly_internal_secret') which returns empty
-- ============================================================================
CREATE OR REPLACE FUNCTION process_schedule_triggers() RETURNS void AS $$
DECLARE
v_trigger RECORD;
v_idempotency_key TEXT;
v_minute INTEGER;
v_hour INTEGER;
v_fly_url TEXT := 'https://whale-agent.fly.dev';
v_fly_secret TEXT;
BEGIN
-- S-H2 FIX: Read from platform_secrets instead of current_setting
SELECT value INTO v_fly_secret FROM platform_secrets WHERE key = 'fly_internal_secret';
IF v_fly_secret IS NULL OR v_fly_secret = '' THEN
RAISE NOTICE 'fly_internal_secret not found in platform_secrets — skipping schedule triggers';
RETURN;
END IF;
v_minute := EXTRACT(MINUTE FROM now())::INTEGER;
v_hour := EXTRACT(HOUR FROM now())::INTEGER;
FOR v_trigger IN
SELECT ut.*, w.id as wf_id, w.store_id as wf_store_id
FROM user_triggers ut
LEFT JOIN workflows w ON w.id = ut.workflow_id
WHERE ut.trigger_type = 'schedule'
AND ut.is_active = true
AND (ut.workflow_id IS NOT NULL OR ut.tool_id IS NOT NULL)
LOOP
IF v_trigger.trigger_config ? 'interval_minutes' THEN
IF v_minute % (v_trigger.trigger_config->>'interval_minutes')::INTEGER != 0 THEN
CONTINUE;
END IF;
END IF;
v_idempotency_key := v_trigger.id::TEXT || '_' || to_char(now(), 'YYYY-MM-DD-HH24-MI');
IF v_trigger.workflow_id IS NOT NULL THEN
PERFORM net.http_post(
url := v_fly_url || '/workflows/start',
headers := jsonb_build_object(
'Content-Type', 'application/json',
'Authorization', 'Bearer ' || v_fly_secret
),
body := jsonb_build_object(
'workflow_id', v_trigger.workflow_id,
'store_id', v_trigger.wf_store_id,
'trigger_type', 'schedule',
'trigger_payload', jsonb_build_object('trigger_id', v_trigger.id, 'fired_at', now()),
'idempotency_key', v_idempotency_key
)
);
ELSE
INSERT INTO trigger_queue (trigger_id, store_id, payload, idempotency_key)
VALUES (v_trigger.id, v_trigger.store_id, jsonb_build_object('fired_at', now()), v_idempotency_key)
ON CONFLICT DO NOTHING;
END IF;
INSERT INTO audit_logs (action, severity, store_id, resource_type, resource_id, source, details)
VALUES ('trigger.schedule.fired', 'info', v_trigger.store_id, 'user_trigger', v_trigger.id::TEXT,
'pg_cron', jsonb_build_object('interval_minutes', v_trigger.trigger_config->>'interval_minutes'));
END LOOP;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Also update condition triggers to read from platform_secrets
-- (process_condition_triggers was already replaced in security_fixes migration,
-- but that version still uses current_setting. Update it here.)
CREATE OR REPLACE FUNCTION process_condition_triggers() RETURNS void AS $$
DECLARE
v_trigger RECORD;
v_result BOOLEAN;
v_idempotency_key TEXT;
v_fly_url TEXT := 'https://whale-agent.fly.dev';
v_fly_secret TEXT;
v_condition_sql TEXT;
BEGIN
-- S-H2 FIX: Read from platform_secrets instead of current_setting
SELECT value INTO v_fly_secret FROM platform_secrets WHERE key = 'fly_internal_secret';
IF v_fly_secret IS NULL OR v_fly_secret = '' THEN
RAISE NOTICE 'fly_internal_secret not found in platform_secrets — skipping condition triggers';
RETURN;
END IF;
FOR v_trigger IN
SELECT ut.*, w.id as wf_id, w.store_id as wf_store_id
FROM user_triggers ut
LEFT JOIN workflows w ON w.id = ut.workflow_id
WHERE ut.trigger_type = 'condition'
AND ut.is_active = true
AND (ut.workflow_id IS NOT NULL OR ut.tool_id IS NOT NULL)
AND (ut.last_checked_at IS NULL OR ut.last_checked_at < now() - ((ut.trigger_config->>'check_interval_seconds')::INTEGER || ' seconds')::interval)
LOOP
UPDATE user_triggers SET last_checked_at = now() WHERE id = v_trigger.id;
v_condition_sql := v_trigger.trigger_config->>'condition_sql';
IF v_condition_sql IS NULL OR v_condition_sql = '' THEN
CONTINUE;
END IF;
-- P3 FIX: Block non-SELECT statements
IF v_condition_sql ~* '^\s*(INSERT|UPDATE|DELETE|DROP|ALTER|TRUNCATE|CREATE|GRANT|REVOKE|COPY|EXECUTE|CALL|DO|SET|RESET|VACUUM|CLUSTER|REINDEX|COMMENT|SECURITY|REASSIGN|DISCARD)' THEN
INSERT INTO audit_logs (action, severity, store_id, resource_type, resource_id, error_message, source)
VALUES ('trigger.condition.blocked', 'error', v_trigger.store_id, 'user_trigger', v_trigger.id::TEXT,
'Blocked non-SELECT condition SQL: ' || left(v_condition_sql, 100), 'pg_cron');
CONTINUE;
END IF;
IF v_condition_sql ~* '(;\s*(INSERT|UPDATE|DELETE|DROP|ALTER|CREATE))|(--)|(\/\*)|(\bunion\b.*\bselect\b)' THEN
INSERT INTO audit_logs (action, severity, store_id, resource_type, resource_id, error_message, source)
VALUES ('trigger.condition.blocked', 'error', v_trigger.store_id, 'user_trigger', v_trigger.id::TEXT,
'Blocked suspicious condition SQL: ' || left(v_condition_sql, 100), 'pg_cron');
CONTINUE;
END IF;
BEGIN
SET LOCAL transaction_read_only = on;
EXECUTE 'SELECT (' || v_condition_sql || ')::BOOLEAN' INTO v_result;
EXCEPTION WHEN OTHERS THEN
INSERT INTO audit_logs (action, severity, store_id, resource_type, resource_id, error_message, source)
VALUES ('trigger.condition.error', 'error', v_trigger.store_id, 'user_trigger', v_trigger.id::TEXT,
'Condition SQL failed: ' || SQLERRM, 'pg_cron');
CONTINUE;
END;
IF NOT COALESCE(v_result, false) THEN
CONTINUE;
END IF;
v_idempotency_key := v_trigger.id::TEXT || '_' || to_char(now(), 'YYYY-MM-DD-HH24');
IF v_trigger.workflow_id IS NOT NULL THEN
PERFORM net.http_post(
url := v_fly_url || '/workflows/start',
headers := jsonb_build_object(
'Content-Type', 'application/json',
'Authorization', 'Bearer ' || v_fly_secret
),
body := jsonb_build_object(
'workflow_id', v_trigger.workflow_id,
'store_id', v_trigger.wf_store_id,
'trigger_type', 'condition',
'trigger_payload', jsonb_build_object(
'trigger_id', v_trigger.id,
'condition_sql', v_condition_sql,
'result', v_result
),
'idempotency_key', v_idempotency_key
)
);
END IF;
INSERT INTO audit_logs (action, severity, store_id, resource_type, resource_id, source, details)
VALUES ('trigger.condition.fired', 'info', v_trigger.store_id, 'user_trigger', v_trigger.id::TEXT,
'pg_cron', jsonb_build_object('condition_sql', left(v_condition_sql, 200)));
END LOOP;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- ============================================================================
-- Remaining indexes from original migrations that were never applied
-- ============================================================================
CREATE INDEX IF NOT EXISTS idx_workflows_trigger
ON workflows(trigger_type, is_active)
WHERE is_active = true;
CREATE INDEX IF NOT EXISTS idx_workflow_steps_workflow
ON workflow_steps(workflow_id);
CREATE INDEX IF NOT EXISTS idx_workflow_steps_entry
ON workflow_steps(workflow_id, is_entry_point)
WHERE is_entry_point = true;
CREATE INDEX IF NOT EXISTS idx_webhook_active
ON webhook_endpoints(is_active)
WHERE is_active = true;
CREATE INDEX IF NOT EXISTS idx_webhook_workflow
ON webhook_endpoints(workflow_id);
CREATE INDEX IF NOT EXISTS idx_approval_store
ON workflow_approval_requests(store_id);
CREATE INDEX IF NOT EXISTS idx_approval_run
ON workflow_approval_requests(run_id);
CREATE INDEX IF NOT EXISTS idx_approval_pending
ON workflow_approval_requests(status, expires_at)
WHERE status = 'pending';
CREATE INDEX IF NOT EXISTS idx_approval_step_run
ON workflow_approval_requests(step_run_id);
-- ============================================================================
-- M8 FIX: Change version FK to SET NULL for safe version deletion
-- ============================================================================
-- Drop and recreate FKs with SET NULL
ALTER TABLE workflow_runs DROP CONSTRAINT IF EXISTS workflow_runs_version_id_fkey;
ALTER TABLE workflow_runs ADD CONSTRAINT workflow_runs_version_id_fkey
FOREIGN KEY (version_id) REFERENCES workflow_versions(id) ON DELETE SET NULL;
ALTER TABLE workflows DROP CONSTRAINT IF EXISTS workflows_published_version_id_fkey;
ALTER TABLE workflows ADD CONSTRAINT workflows_published_version_id_fkey
FOREIGN KEY (published_version_id) REFERENCES workflow_versions(id) ON DELETE SET NULL;
