💸 SubZap: Hunting “Zombie Subscriptions” with AlloyDB and Gemini AI

#gemini #ai #database #showdev

Have you ever checked your bank statement and found a $12.99 charge for a service you haven’t used in six months? You are not alone. These are “Zombie Subscriptions” — free trials that quietly turned into paid memberships, draining our wallets silently.

In this blog, I will share how I built SubZap, an intelligent AI Agent that connects to your financial data, hunts down these hidden costs, and helps you reclaim your money. This project was built during the Google Cloud Build & Blog Marathon 2025.

💡 The Problem
Modern banking apps show us what we spent, but they don’t tell us why it matters. Finding a recurring subscription buried in 2,000 rows of coffee and grocery transactions is like finding a needle in a haystack.

I wanted to build an agent that doesn’t just “show data” but reasons about it. I wanted to ask:

_“Identify my recurring subscriptions and tell me how much I am wasting.”
_

🛠️ The Solution & Tech Stack
To build a scalable, secure, and intelligent financial agent, I chose the following Google Cloud stack:

Google Cloud Run: To host the application (Streamlit) serverlessly.
AlloyDB for PostgreSQL: A fully managed, PostgreSQL-compatible database to store thousands of transaction records with high performance.
Vertex AI (Gemini 2.0 Flash): The cognitive engine. It serves two roles : - Translator: Converts natural English questions into complex SQL queries.

🚀** Building the “Agentic” Workflow**
The core of SubZap is the Agentic RAG(Retrieval Augmented Generation) pattern. Instead of vector search, I used Text-to-SQL, which is more accurate for structured financial data.

Step 1: Setting up the Data Foundation (AlloyDB)
I needed a database that could handle complex queries and scale easily. I created an AlloyDB Cluster and connected it via VPC Peering to ensure my transaction data remained on a Private IP (crucial for financial security).

I designed a simple but effective schema:

`CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    date DATE,
    description VARCHAR(255),
    amount DECIMAL(10, 2),
    category VARCHAR(100)
);`

Step 2: The “Brain” (Gemini 2.0 Flash)
The magic happens in how the AI interacts with the database. I used Vertex AI’s Gemini 2.0 Flash model because of its incredible speed and reasoning capabilities.

Here is the Python function that powers the Natural Language to SQL conversion:

`
def ask_gemini_to_write_sql(user_question):
    vertexai.init(project=PROJECT_ID, location="us-central1")
    model = GenerativeModel("gemini-2.0-flash-exp") 

    prompt = f"""
    You are a PostgreSQL expert. Convert the user's request into a SQL query.
    Table: 'transactions'
    Columns: id, date (timestamp), description (text), amount (float), category (text)

    Rules: 
    1. Return ONLY the raw SQL code. No markdown.
    2. Case insensitive searches: Use ILIKE.
    3. Look for recurring patterns for subscriptions.

    User Question: {user_question}
    """
    response = model.generate_content(prompt)
    return response.text.strip()
`

Step 3: The Insight (Reasoning)
Getting the data isn’t enough. If the database returns 5 rows of “Gym Membership”, the user needs to know the total impact.

I passed the SQL results back to Gemini with this prompt:

“Act as a financial advisor. Analyze this data. If you see recurring monthly charges, flag them as likely subscriptions and calculate the annual waste.”

📊 The Result
The final application is a Streamlit dashboard where users can interact naturally with their finances.

When I asked, “Identify my recurring subscriptions,” the agent correctly identified a hidden Gym Membership charging me $49/month and flagged it as a high-priority cancellation. It parsed 2,000+ rows in milliseconds, thanks to Alloy DB’s performance.

🧠 Challenges & Learnings

  - **Private Networking:** Connecting Cloud Run to AlloyDB via Private IP was tricky. I learned how VPC Connectors bridge serverless apps to private database clusters.
  - **Prompt Engineering:** Teaching the AI to distinguish between “Coffee” (recurring habit) and “Netflix” (recurring subscription) required refining the system instructions to look for specific keywords and date patterns.

🔮 What’s Next?
I plan to add automatic cancellation emails — where the agent not only finds the subscription but drafts the cancellation email for you.

SubZap proves that with Google Cloud’s composable tools (AlloyDB + Vertex AI), a single developer can build powerful, agentic applications in less than 24 hours.

Code Repository: https://github.com/Aksh2758/subzap-ai-agent.git
Demo Video: https://drive.google.com/file/d/1jLEJ65DeyhCKuSNHp4NokAQVJRI-YX42/view?usp=sharing