Forem: BrewHubPHL

Beyond Chatbots: How I use Gemini 2.5 and Supabase to run a fully automated retention team

BrewHubPHL — Wed, 29 Apr 2026 00:23:22 +0000

When most developers think about integrating AI into their apps, the default move is to build a chatbot. But for my coffee shop app, BrewHub PHL, I didn't want users talking to an AI. I wanted the AI doing the heavy lifting in the background.

Customer retention is notoriously hard for local businesses. Figuring out who hasn't visited in a while, drafting a personalized message, and issuing a custom discount code usually takes hours of manual marketing work.

I decided to fully automate this using Gemini 2.5 Flash, Supabase, and a simple weekly cron job. Here is how I built a headless AI retention agent that automatically wins back lapsed customers while I sleep.

The Architecture
The pipeline runs every Monday at 10 AM and consists of four steps:

The Data Layer: A Supabase RPC finds eligible lapsed customers.

The Privacy Layer: The script strips all Personally Identifiable Information (PII) before it touches the LLM.

The Brains: The Gemini API generates hyper-personalized SMS messages and forces the output into strict JSON.

The Execution: The system generates physical POS vouchers and sends the SMS via Twilio.

Step 1: Finding Eligible Customers
I didn't want to spam one-off visitors. To find the right targets, I wrote a Postgres RPC in Supabase called get_lapsed_customers_eligible_for_retention.

It filters the database for users who:

Have ordered at least 3 times (loyal customers).

Haven't ordered in the last 14 days.

Haven't received a marketing voucher in the last 90 days (the cooldown period).

SQL
-- The Supabase RPC handles the heavy data filtering instantly
SELECT id, full_name, phone, favorite_drink, days_since_last_visit
FROM get_lapsed_customers_eligible_for_retention(
p_min_orders := 3,
p_lapsed_days := 14,
p_cooldown_days := 90,
p_batch_limit := 10
);
Step 2: Privacy by Design
Sending raw customer data to an LLM is a terrible idea. Before the data leaves my server, the script maps the Supabase response to a strictly anonymous payload.

Names and phone numbers are dropped. Gemini only sees the customer_id, their favorite_drink, and days_since_last_visit.

Step 3: Prompting for Structured JSON with Gemini 2.5
This is where the magic happens. I don't just want Gemini to write a message; I need it to return an array of objects that my code can iterate over to send SMS messages.

Using the official @google/generative-ai SDK, I pass responseMimeType: "application/json" to guarantee the output won't break my script.

JavaScript
import { GoogleGenerativeAI } from "@google/generative-ai";

const genAI = new GoogleGenerativeAI(process.env.GEMINI_API_KEY);
const model = genAI.getGenerativeModel({
model: "gemini-2.5-flash",
generationConfig: {
responseMimeType: "application/json",
}
});

const prompt = `
You are the BrewHub PHL Retention Agent. I will provide a list of anonymous customer profiles.
For each customer, write a short, friendly, and highly personalized SMS text message under 160 characters.
Acknowledge that we haven't seen them in a while, mention their favorite drink by name, and offer them a $5 voucher to come back.

Return ONLY a JSON array with this exact structure:
[
{ "customer_id": "uuid-here", "sms_message": "Hey! It's been a while..." }
]

Customer Data:
${JSON.stringify(anonymousCustomerList)}
`;

const response = await model.generateContent(prompt);
const aiDecisions = JSON.parse(response.text());
Because Gemini 2.5 Flash is incredibly fast, this entire batch generation takes just a few seconds.

Step 4: Fulfillment and SMS
Once Gemini hands back the clean JSON array of messages, my cron job loops through the results.

For each customer_id, it:

Generates a unique secure voucher code (e.g., 5OFF-A3F9C1).

Inserts that active voucher into my Supabase vouchers table.

Appends the code to Gemini's personalized message: "Show this code to one of our baristas: 5OFF-A3F9C1".

Dispatches the final message via Twilio.

The Result
By moving AI out of the chat window and into a scheduled backend worker, the system feels like magic. Customers get a highly personalized text referencing their actual favorite order, complete with a working POS discount code, and I don't have to lift a finger.

The Gemini API's strict JSON output makes it incredibly reliable for server-to-server data pipelines, proving that the real power of modern LLMs is as a background reasoning engine.

If you want to see the Supabase + Next.js architecture in action (or just want to order some coffee in Philadelphia), you can check out the live web app at brewhubphl.com.

BrewHub PHL: Low Latency, High Caffeine (Notion MCP Operations Ledger)

BrewHubPHL — Thu, 05 Mar 2026 04:08:14 +0000

This is a submission for the Notion MCP Challenge

## What I Built

<BrewHubPHL.com isn't just a software project; it's the digital infrastructure for a physical neighborhood coffee and parcel logistics shop. Managing a physical retail space requires strict security, fast transactions, and immutable audit logs. But reading database tables isn't a great experience for human store managers.

To bridge the gap between our high-speed PostgreSQL database and our human management team, I built the Hub-to-Notion Operations Ledger.

Whenever a barista completes an order on the POS, or a manager performs a high-security action (like an IRS-compliant payroll override), our database automatically and securely syncs that canonical data directly into a Notion Workspace. This turns Notion into a "Live Manager's Logbook" that updates in real-time, without any manual data entry.

## Show us the code

<The magic happens through a highly secure, zero-trust architecture between Supabase, Netlify Functions, and Notion.

Instead of trusting the payload from the database trigger, our Netlify function acts as a secure bridge that re-fetches the canonical data. We also built-in Graceful Degradation: if the Notion API ever goes down, the database catches the exception and returns NEW, ensuring we never stop selling coffee just because a sync failed.

Here is a snippet of our Double-Guarded Postgres Trigger for Auto-Syncing completed orders:
-- 20260304_schema86b_orders_notion_sync.sql
CREATE OR REPLACE FUNCTION public.fn_trg_orders_notion_sync()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER SET search_path = public, extensions
AS $$
BEGIN
-- Defense-in-depth guard: Only fire on transition to 'completed'
IF (NEW.status = 'completed') AND (OLD.status IS DISTINCT FROM 'completed') THEN
BEGIN
-- Fire webhook to Netlify with signed headers and ONLY the record_id
PERFORM net.http_post(
url := coalesce(current_setting('app.settings.notion_sync_webhook_url', true), 'https://brewhubphl.com/.netlify/functions/notion-sync'),
headers := jsonb_build_object(
'Content-Type', 'application/json',
'X-BrewHub-Action', 'true',
'x-brewhub-secret', current_setting('app.settings.internal_sync_secret', true)
),
body := jsonb_build_object('table', 'orders', 'record_id', NEW.id, 'type', 'UPDATE')
);
EXCEPTION WHEN undefined_function THEN
RAISE WARNING 'pg_net missing, skipping Notion sync';
WHEN OTHERS THEN
RAISE WARNING 'Notion sync trigger failed, but allowing order to complete: %', SQLERRM;
END;
END IF;
RETURN NEW;
END;
$$;

CREATE TRIGGER trg_orders_notion_sync
AFTER UPDATE ON public.orders
FOR EACH ROW
-- Double guard: Postgres level check before invoking function
WHEN (NEW.status = 'completed' AND OLD.status IS DISTINCT FROM 'completed')
EXECUTE FUNCTION public.fn_trg_orders_notion_sync();

## How I Used Notion MCP

<While the Postgres triggers and Netlify functions populate the data, Notion MCP (Model Context Protocol) acts as our "AI Shift Manager."

By adding the Notion MCP server to our .vscode/mcp.json configuration, our AI concierge ("Elise", powered by Claude) has direct read/write access to the Shop Command Center in Notion.

This unlocks an incredible Human-in-the-Loop workflow:

The Daily Pulse: I can ask the AI to "Check the Ledger." The AI uses MCP to read the Sales Ledger database in Notion, summarizes the total revenue and completed orders for the day, and cross-references it with our physical parcel logistics.

Security Audits: The AI scans the Audit Trail page in Notion for any new Manager Overrides (synced via our zero-trust webhooks). If it sees an unusual comp or payroll adjustment, it flags it for my review.

Drafting Briefings: Instead of just outputting text, the AI uses MCP to draft a neat "Morning Briefing" page directly inside my Notion workspace for the opening manager to read the next day.

I built an Infinite AI Debate Arena using the GitHub Copilot CLI 🥊

BrewHubPHL — Wed, 11 Feb 2026 19:29:41 +0000

This is a submission for the GitHub Copilot CLI Challenge

💡 The Idea
What happens if you lock two AI personalities in a room and force them to argue about "Is a hotdog a sandwich?" forever?

For the GitHub Copilot CLI Challenge, I didn't want to just build a utility tool. I wanted to build something chaotic. Enter the AI Debate Arena.

It’s a terminal-based "fighting game" where:

Captain Capslock (An angry Boomer) fights Lil' Zoomer (A Gen-Z teen).

They argue in an infinite loop.

Sentiment Analysis determines who is "winning" (getting angrier).

🎥 The Demo
[https://youtu.be/-RBdUKZY9zA]

🛠️ How it Works
The project uses Python to orchestrate the chaos, but the "brains" are entirely powered by the GitHub Copilot CLI.

The "Persona Injection" I used the gh copilot explain command to generate the dialogue. By injecting a specific persona into the prompt, we can force Copilot to break character.

Python

The Secret Sauce

prompt = f"{persona} Your opponent said: '{last_response}'. Reply in one short, funny sentence."
cmd = ["gh", "copilot", "explain", "-p", prompt]

The Loop The script creates a feedback loop:

Fighter A generates a response using Copilot.

TextBlob analyzes the sentiment (Politeness = Weakness, Anger = Power).

Fighter B takes that response and generates a counter-argument.

Rich renders the ASCII faces and health bars in real-time.

🎨 The Tech Stack
GitHub CLI (gh): The AI engine.

Rich: For the beautiful terminal UI and layouts.

TextBlob: For the "Rage Meter" logic.

Python: To glue it all together.

🏆 The Outcome
Sometimes they argue about politics, sometimes about cereal. The CLI handles the roleplay surprisingly well. My favorite line so far?

"Milk-first people stay taking Ls fr fr, that's giving unhinged villain energy." — Lil' Zoomer

🔗 The Code
Check out the repository here to run it yourself! [https://github.com/BrewHubPHL/ai-debate.git]

How I Built an AI Barista using Square, Supabase, and ElevenLabs

BrewHubPHL — Fri, 06 Feb 2026 12:28:28 +0000

How I Built an AI Barista using Square, Supabase, and ElevenLabs

I run a tech-forward coffee hub in Philadelphia called BrewHubPHL. When we opened, I didn't just want a screen flashing "Order Ready"—I wanted the shop to speak.

Here is how I used Supabase Edge Functions to glue Square POS and ElevenLabs together, creating an automated announcer for our orders.

The Stack

Database & Auth: Supabase
Payments: Square (POS and Webhooks)
Voice AI: ElevenLabs (Turbo v2 model)
Compute: Netlify Functions

The Workflow

Square detects a payment (payment.updated).
Supabase receives the webhook and routes it.
ElevenLabs generates the audio file ("Order for John is ready!").
The frontend plays the audio automatically.

Step 1: Catching the Square Webhook

First, we need to know when an order is actually paid. We set up a serverless function to listen for Square's payment.updated event.


javascript
// square-webhook.js
exports.handler = async (event) => {
  const body = JSON.parse(event.body);

  if (body.type === 'payment.updated' && body.data.object.payment.status === 'COMPLETED') {
    const orderId = body.data.object.payment.reference_id;

    // Update Supabase
    await supabase.from('orders').update({ status: 'paid' }).eq('id', orderId);

    // Trigger the Announcer
    await triggerVoiceAnnouncement(orderId);
  }
};

## Step 2: Generating the Voice
This is where the magic happens. We don't want a robotic "text-to-speech" voice; we want personality. I used the ElevenLabs Turbo v2 model because it has low latency (essential for real-time retail).

We send the text to their API and get back an audio buffer.

// text-to-speech.js
const response = await fetch(`https://api.elevenlabs.io/v1/text-to-speech/${VOICE_ID}`, {
    method: 'POST',
    headers: {
        'xi-api-key': process.env.ELEVENLABS_API_KEY
    },
    body: JSON.stringify({
        text: "Order ready for specific_customer!",
        model_id: 'eleven_turbo_v2',
        voice_settings: { stability: 0.5, similarity_boost: 0.75 }
    })
});

Why build this?
It’s not just a gimmick. In a busy shop, customers tune out shouting baristas. A distinct, consistent AI voice cuts through the noise. Plus, by integrating it directly with Square and Supabase, we have zero manual work—the barista just taps "Charge," and the code does the rest.

For the developers, I've open-sourced the sync logic on GitHub: https://gist.github.com/BrewHubPHL/53937283c5eaa7cafedb9555e851c509