Recover failed Stripe payments with SQL Server context and Gmail dunning

Build an agent workflow that recovers failed Stripe payments by enriching the customer from my SQL Server billing database, sending a tone-matched recovery email through Gmail, and logging every attempt back to SQL Server.

Trigger: a Stripe webhook on the invoice.payment_failed event. The webhook payload includes the invoice, the customer id, the failure reason, the amount due, and the next retry date if Stripe is going to retry automatically.

Steps the agent should run for each event:

1. Call Stripe's Retrieve Customer operation with the customer id from the webhook to get the customer's email, name, and any metadata on the Stripe record.

2. Look up that customer in my SQL Server billing database. Use Find Row against my customers table (keyed on stripe_customer_id) to get account_tier, signup_date, and primary_contact_name. Then use Select Rows against my subscriptions table to get their current plan and MRR, and Select Rows against my failed_payments table to count how many prior failed payment attempts this same customer has had in the last 90 days.

3. Decide tone based on context. First failure in 90 days = gentle, assume-good-faith reminder. Second failure = clearer, slightly firmer with a direct ask. Third or more = firm last-attempt message that mentions the account is at risk of pause or cancellation. Scale the language to plan size and tenure too: a long-tenured enterprise account gets a more deferential tone than a brand-new trial conversion.

4. Draft a recovery email matched to that tone. The email must include the customer's first name, the plan name, the amount that failed, the failure reason in plain English (e.g. "your card was declined" rather than the raw Stripe code), and a clearly labeled link to update their payment method. Use the Stripe customer billing portal link if available, otherwise fall back to a configured fallback URL.

5. Send the email via Gmail's Send a Message operation from my connected Gmail address, with a subject line that matches the tone (e.g. "Quick heads up about your last payment" for first failures vs. "Action needed to keep your account active" for repeat failures).

6. Insert a row into a dunning_attempts table in SQL Server capturing: customer_id, stripe_customer_id, stripe_invoice_id, failure_reason, severity_level (1, 2, or 3 from the tone decision above), email_subject_sent, email_recipient, and sent_at timestamp.

Setup step to include in the workflow: create the dunning_attempts table in SQL Server if it does not exist, with this schema:

CREATE TABLE dunning_attempts (id INT IDENTITY(1,1) PRIMARY KEY, customer_id NVARCHAR(64) NULL, stripe_customer_id NVARCHAR(64) NOT NULL, stripe_invoice_id NVARCHAR(64) NOT NULL, failure_reason NVARCHAR(512) NULL, severity_level INT NOT NULL, email_subject_sent NVARCHAR(256) NOT NULL, email_recipient NVARCHAR(256) NOT NULL, sent_at DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME());

Edge cases to handle:

- If the customer is not found in my SQL Server customers table, still send a recovery email using the Stripe customer record, set severity_level to 1, set customer_id to NULL in the log, and include a note in the log that the internal record was missing.

- If the Stripe customer has no email, skip sending and write a row to dunning_attempts with email_recipient set to "unknown" so retention can see the gap.

- Treat the webhook as idempotent: if a dunning_attempts row already exists for the same stripe_invoice_id, do not send a duplicate email.

Make the table names, the fallback payment URL, the from-name on the email, and the tone copy easy for me to edit at the top of the agent's instructions.