MCP + Supabase: Give Your AI Agent a Persistent Database

The Real Problem: AI Agents Have No Memory

• A client books an appointment through your AI agent on Monday. By Thursday, the agent has no record of it.
• You train your AI on your service menu and pricing. Next session, you repeat the same setup from scratch.
• Your agent analyzes your Google Ads performance and gives you recommendations. A week later, it cannot compare to the previous week because that data is gone.

Why Supabase for Local Business AI Agents

• 500 MB database storage
• 2 active projects
• 50,000 monthly active users (for auth)
• Unlimited API requests
• 7-day log retention

Step 1: Create Your Supabase Project

1. Go to supabase.com and create a free account
2. Click New Project, name it something like datalatte-agent
3. Choose a strong database password (store it in your password manager)
4. Select your region — choose the one closest to your customers
5. Wait ~2 minutes for provisioning

• Project URL: YOUR_SUPABASE_URL (looks like https://abcdefghijklm.supabase.co)
• Anon/Public Key: YOUR_SUPABASE_ANON_KEY (safe to use in client-side code)
• Service Role Key: YOUR_SUPABASE_SERVICE_KEY (keep this private — full database access)

Step 2: Install and Configure the Supabase MCP Server

npm install -g @supabase/mcp-server-supabase

{
  "mcpServers": {
    "supabase": {
      "command": "npx",
      "args": ["-y", "@supabase/mcp-server-supabase"],
      "env": {
        "SUPABASE_URL": "YOUR_SUPABASE_URL",
        "SUPABASE_SERVICE_ROLE_KEY": "YOUR_SUPABASE_SERVICE_KEY"
      }
    }
  }
}

Step 3: Design Your Database Schema

The Four Core Tables

-- Clients table: persistent customer memory
CREATE TABLE clients (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  phone TEXT UNIQUE,
  email TEXT,
  first_name TEXT NOT NULL,
  last_name TEXT,
  total_visits INTEGER DEFAULT 0,
  lifetime_value DECIMAL(10,2) DEFAULT 0,
  loyalty_points INTEGER DEFAULT 0,
  preferred_service TEXT,
  notes TEXT,
  first_seen DATE DEFAULT CURRENT_DATE,
  last_seen DATE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Appointments table: bookings tracking
CREATE TABLE appointments (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  client_id UUID REFERENCES clients(id) ON DELETE SET NULL,
  service TEXT NOT NULL,
  staff_member TEXT,
  scheduled_at TIMESTAMPTZ NOT NULL,
  duration_minutes INTEGER DEFAULT 60,
  status TEXT DEFAULT 'confirmed' CHECK (status IN ('confirmed', 'completed', 'cancelled', 'no_show')),
  price DECIMAL(8,2),
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Reviews table: feedback and sentiment tracking
CREATE TABLE reviews (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  client_id UUID REFERENCES clients(id) ON DELETE SET NULL,
  platform TEXT DEFAULT 'google' CHECK (platform IN ('google', 'yelp', 'facebook', 'direct')),
  rating INTEGER CHECK (rating BETWEEN 1 AND 5),
  content TEXT,
  sentiment_score DECIMAL(3,2), -- -1.0 to 1.0
  responded_at TIMESTAMPTZ,
  response_text TEXT,
  review_date DATE DEFAULT CURRENT_DATE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Marketing metrics table: campaign ROI tracking
CREATE TABLE marketing_metrics (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  week_start DATE NOT NULL,
  campaign_name TEXT NOT NULL,
  platform TEXT CHECK (platform IN ('google_ads', 'meta_ads', 'email', 'sms', 'organic')),
  spend DECIMAL(10,2) DEFAULT 0,
  impressions INTEGER DEFAULT 0,
  clicks INTEGER DEFAULT 0,
  bookings_attributed INTEGER DEFAULT 0,
  revenue_attributed DECIMAL(10,2) DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(week_start, campaign_name)
);

-- Indexes for common query patterns
CREATE INDEX idx_appointments_scheduled ON appointments(scheduled_at);
CREATE INDEX idx_appointments_status ON appointments(status);
CREATE INDEX idx_reviews_rating ON reviews(rating);
CREATE INDEX idx_marketing_metrics_week ON marketing_metrics(week_start);

Step 4: Four AI Agent Workflows Using Supabase MCP

Workflow 1: Client Memory — Greet Returning Customers by Name

"Check if a client with phone [PHONE] exists in Supabase. If they do, tell me their name, total visits, preferred service, and any notes. If they are new, create a record for them."

SELECT first_name, last_name, total_visits, preferred_service, loyalty_points, notes
FROM clients
WHERE phone = '+1-555-0123'
LIMIT 1;

Workflow 2: Appointment Tracking — Write Bookings, Read Today's Schedule

INSERT INTO appointments (client_id, service, staff_member, scheduled_at, duration_minutes, price)
VALUES (
  (SELECT id FROM clients WHERE phone = '+1-555-0123'),
  'Balayage',
  'Maria',
  '2026-06-16 14:00:00+00',
  120,
  180.00
)
RETURNING id;

SELECT c.first_name, c.last_name, a.service, a.scheduled_at, a.staff_member, a.price
FROM appointments a
JOIN clients c ON a.client_id = c.id
WHERE DATE(a.scheduled_at) = CURRENT_DATE
AND a.status = 'confirmed'
ORDER BY a.scheduled_at;

Workflow 3: Review Analytics — Sentiment Trend Tracking

-- Store new review
INSERT INTO reviews (client_id, platform, rating, content, sentiment_score, review_date)
VALUES (
  (SELECT id FROM clients WHERE email = 'sarah@example.com'),
  'google',
  5,
  'Amazing service, Maria was incredible!',
  0.92,
  CURRENT_DATE
);

-- Weekly sentiment trend
SELECT 
  DATE_TRUNC('week', review_date) as week,
  AVG(rating) as avg_rating,
  AVG(sentiment_score) as avg_sentiment,
  COUNT(*) as review_count
FROM reviews
WHERE review_date >= NOW() - INTERVAL '90 days'
GROUP BY week
ORDER BY week;

Workflow 4: Campaign ROI Tracker — Weekly Ad Spend vs Revenue

SELECT 
  campaign_name,
  platform,
  spend,
  bookings_attributed,
  revenue_attributed,
  ROUND((revenue_attributed / NULLIF(spend, 0)), 2) as roas,
  ROUND(((revenue_attributed - spend) / NULLIF(spend, 0)) * 100, 1) as roi_percent
FROM marketing_metrics
WHERE week_start = DATE_TRUNC('week', NOW() - INTERVAL '1 week')
ORDER BY roi_percent DESC;

Step 5: Python Agent With Supabase Client Lookup

import os
from anthropic import Anthropic
from supabase import create_client, Client

# Initialize clients
anthropic = Anthropic(api_key=os.environ["ANTHROPIC_API_KEY"])
supabase: Client = create_client(
    os.environ["YOUR_SUPABASE_URL"],
    os.environ["YOUR_SUPABASE_SERVICE_KEY"]
)

def get_client_context(phone: str) -> str:
    """Look up client history in Supabase before responding."""
    result = supabase.table("clients").select(
        "first_name, total_visits, preferred_service, loyalty_points, notes"
    ).eq("phone", phone).execute()
    
    if result.data:
        client = result.data[0]
        return (
            f"Returning client: {client['first_name']}, "
            f"{client['total_visits']} previous visits, "
            f"preferred service: {client['preferred_service']}, "
            f"loyalty points: {client['loyalty_points']}. "
            f"Notes: {client.get('notes', 'none')}."
        )
    else:
        return "New client — no history on file."

def chat_with_context(phone: str, user_message: str) -> str:
    """Send a message with client context injected into the system prompt."""
    client_context = get_client_context(phone)
    
    system_prompt = f"""You are a helpful assistant for a local hair salon.
    
Current client context: {client_context}

Use this context to personalize your response. Greet returning clients by first name.
If they have many loyalty points, mention that they are close to a reward.
Keep responses warm, professional, and under 100 words."""
    
    response = anthropic.messages.create(
        model="claude-opus-4-5",
        max_tokens=300,
        system=system_prompt,
        messages=[{"role": "user", "content": user_message}]
    )
    
    return response.content[0].text

# Example usage
if __name__ == "__main__":
    phone = "+1-555-0187"
    message = "Hi, I'd like to book an appointment for next week"
    reply = chat_with_context(phone, message)
    print(reply)
    # Output: "Welcome back, Emma! Great to hear from you — 
    # your 12th visit is coming up. You've got 240 loyalty points, 
    # just 60 away from a free treatment! When works best for you next week?"

Step 6: Row-Level Security — Limiting What the AI Can Access

-- Enable RLS
ALTER TABLE clients ENABLE ROW LEVEL SECURITY;
ALTER TABLE appointments ENABLE ROW LEVEL SECURITY;

-- Allow service role (your AI agent backend) full access
CREATE POLICY "service_role_all" ON clients
  FOR ALL TO service_role USING (true);

-- Allow anon role (client-facing chatbot) to only see their own record
CREATE POLICY "clients_own_record" ON clients
  FOR SELECT TO anon
  USING (phone = current_setting('request.jwt.claims', true)::json->>'phone');

Comparison: Supabase Free vs Alternatives

FAQ

Related Articles

• What Is MCP? The Model Context Protocol Explained for Non-Developers
• The Best MCP Servers for Local Business AI Setup
• MCP + Notion: Build a Client CRM for Your Local Business
• Build Your Own MCP Server: Custom Data for Your Local Business AI Agent
• MCP + Google Sheets: AI That Reads and Writes Your Bookings Spreadsheet