Pre-Conditions

• supabase-mcp-verification MUST have completed successfully.
• The MCP execute_sql tool MUST be available and authenticated.
• The pg_net extension MUST be enabled. If not, HALT and instruct the operator to run CREATE EXTENSION IF NOT EXISTS pg_net;.
• The target table MUST already exist. HALT if the table is missing.
• The destination endpoint URL MUST be provided by the operator.

Execution Phases

Phase 1 — Define the Trigger Function

1. Create a PL/pgSQL function that serializes the row payload using row_to_json(NEW) for INSERT/UPDATE or row_to_json(OLD) for DELETE.
2. The function MUST call net.http_post() from pg_net with:
   • The destination endpoint URL.
   • Content-Type: application/json header.
   • The serialized JSON payload as the request body.
3. The function MUST return the trigger row (NEW for INSERT/UPDATE, OLD for DELETE).
4. Execute the CREATE OR REPLACE FUNCTION statement via MCP execute_sql.

Example — trigger function (use NEW/RETURN NEW for INSERT/UPDATE; substitute OLD/RETURN OLD for DELETE):

CREATE OR REPLACE FUNCTION notify_webhook_on_change()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  PERFORM net.http_post(
    url     := 'https://your-project.supabase.co/functions/v1/your-handler',
    headers := '{"Content-Type": "application/json"}'::jsonb,
    body    := row_to_json(NEW)::text  -- replace NEW with OLD for DELETE triggers
  );
  RETURN NEW;  -- replace with RETURN OLD for DELETE triggers
END;
$$;

Phase 2 — Create the Trigger

1. Create the trigger using CREATE TRIGGER bound to the target table.
2. MUST specify the exact event(s): AFTER INSERT, AFTER UPDATE, or AFTER DELETE.
3. For UPDATE triggers, MUST specify OF <column_list> to watch only relevant columns. NEVER use bare AFTER UPDATE without a column list.
4. Set the trigger as FOR EACH ROW.
5. Execute the CREATE TRIGGER statement via MCP execute_sql.

Example — AFTER UPDATE OF specific columns:

CREATE TRIGGER trg_orders_webhook
AFTER UPDATE OF status, amount
ON public.orders
FOR EACH ROW
EXECUTE FUNCTION notify_webhook_on_change();

Example — AFTER INSERT:

CREATE TRIGGER trg_orders_insert_webhook
AFTER INSERT
ON public.orders
FOR EACH ROW
EXECUTE FUNCTION notify_webhook_on_change();

Example — AFTER DELETE:

CREATE TRIGGER trg_orders_delete_webhook
AFTER DELETE
ON public.orders
FOR EACH ROW
EXECUTE FUNCTION notify_webhook_on_delete();

Phase 3 — Validate Deployment

1. Query information_schema.triggers to confirm the trigger exists on the target table.
2. Query pg_proc to confirm the trigger function exists.
3. HALT if either validation query returns zero rows.

Verification Report

The agent MUST produce a report containing: