Building a Headless AI-Powered Web Store with Model Context Protocol (MCP), Supabase, and Stripe

here’s a deep, practical build guide you can follow end‑to‑end. it expands the outline into a shippable “headless store for AI agents” using MCP + Supabase + Stripe. I’ll assume Typescript/Node, but you can transpose to Python or Go.

0) architecture at a glance

  • MCP Server (Store Brain): exposes tools the agent can call (product lookup, checkout, order status, stock update). Talks to Supabase + Stripe.

  • AI Agent / Orchestrator: calls MCP tools and handles user dialogue/confirmation.

  • Supabase (DB + Auth + Functions): products, orders, order events; row‑level security (RLS).

  • Stripe (Payments): checkout session + webhooks (paid → update order + stock).

  • Outbox (email/notify): transactional emails (Resend/SendGrid) triggered by DB changes or webhook.

Headless means: no storefront UI required. You can add web UI later without changing server logic.

1) plan the MCP tool surface (your “API for agents”)

Design tools as small, deterministic actions with clear schemas and idempotency keys.

Required tools

  1. listProducts(filter?) → returns safe product projections.

  2. lookupProductById(product_id) → returns full product detail.

  3. createCheckoutSession(product_id, quantity, buyer_email, idempotency_key) → returns Stripe URL + provisional order id.

  4. getOrderStatus(order_id) → returns canonical status.

  5. adjustStock(product_id, delta, reason, idempotency_key) → usually internal; agents rarely call this directly (webhook will).

  6. searchProducts(query, price_max, tags[]) → semantic + faceted search.

Design rules

  • Idempotency required for anything that writes (checkout, stock).

  • Never expose secrets in tool outputs (e.g., Stripe secret keys).

  • Return machine‑readable errors (code, message, retryable boolean).

  • Add metadata.trace_id to every response for observability.

2) database in Supabase

2.1 tables

-- products
create table public.products (
  id uuid primary key default gen_random_uuid(),
  sku text unique not null,
  name text not null,
  description text,
  price_cents integer not null check (price_cents >= 0),
  currency text not null default 'usd',
  stock integer not null default 0 check (stock >= 0),
  tags text[] default '{}',
  is_active boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- orders
create table public.orders (
  id uuid primary key default gen_random_uuid(),
  buyer_email text not null,
  status text not null check (status in ('created','pending_payment','paid','failed','cancelled','refunded','completed')),
  total_cents integer not null check (total_cents >= 0),
  currency text not null default 'usd',
  stripe_checkout_id text,
  stripe_payment_intent_id text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- order_items
create table public.order_items (
  id uuid primary key default gen_random_uuid(),
  order_id uuid not null references public.orders(id) on delete cascade,
  product_id uuid not null references public.products(id),
  quantity integer not null check (quantity > 0),
  unit_price_cents integer not null check (unit_price_cents >= 0),
  created_at timestamptz not null default now()
);

-- order_events (audit trail)
create table public.order_events (
  id uuid primary key default gen_random_uuid(),
  order_id uuid not null references public.orders(id) on delete cascade,
  type text not null, -- 'status_changed','webhook_received','email_sent', etc.
  data jsonb not null default '{}',
  created_at timestamptz not null default now()
);

-- idempotency keys for write ops
create table public.idempotency_keys (
  key text primary key,
  scope text not null, -- 'checkout','stock'
  created_at timestamptz not null default now()
);

Indexes

create index on products using gin (tags);
create index on products (is_active, price_cents);
create index on orders (status, created_at);
create index on order_items (order_id);

Row‑Level Security (RLS)

  • Enable RLS on all tables.

  • Create a service role (used by your MCP server) with elevated access.

  • If you later add a public API, restrict selects to is_active=true columns and hide sensitive fields.

3) Stripe integration (Checkout + Webhooks)

Checkout creation

  • Compute total_cents from DB at request time (never trust client input).

  • Create a provisional orders row with status=pending_payment.

  • Call stripe.checkout.sessions.create(...) with:

    • line_items from your order_items

    • mode: 'payment'

    • success_url / cancel_url

    • metadata: { order_id }

    • payment_intent_data: { metadata: { order_id } }

  • Save stripe_checkout_id.

Webhook flow

  • On checkout.session.completed or payment_intent.succeeded:

    1. Verify signature.

    2. Load order_id from metadata.

    3. Transition order: pending_payment → paid.

    4. Atomic stock decrement: for each item, stock = stock - quantity with check stock >= quantity.

    5. If stock fails, mark order failed and kick off refund (fallback path).

    6. Add order_events audit row.

    7. Fire confirmation email.

Idempotency

  • Use Stripe’s request idempotency AND your idempotency_keys table for your own writes.

4) MCP server (Node/Typescript example structure)

/mcp
  server.ts             # MCP transport + tool registration
  tools/
    listProducts.ts
    searchProducts.ts
    lookupProductById.ts
    createCheckoutSession.ts
    getOrderStatus.ts
  services/
    db.ts               # supabase client (service role)
    stripe.ts           # initialized Stripe SDK
    email.ts            # Resend/SendGrid client
    observability.ts    # logger, trace_id helper
  domain/
    pricing.ts          # validates price, currency math
    stock.ts            # lock/decrement logic
    orders.ts           # state transitions, events
  webhooks/
    stripeWebhook.ts    # express handler, signature verify

MCP Tool definition (conceptual)

// listProducts
input: {
  max_results?: number; price_max_cents?: number;
  tags?: string[]; query?: string;
}
output: {
  products: Array<{ id, sku, name, price_cents, currency, stock, tags }>
  trace_id: string
}

createCheckoutSession (core)

input: {
  product_id: string; quantity: number; buyer_email: string;
  idempotency_key: string;
}
output: {
  order_id: string; checkout_url: string; expires_at: string; trace_id: string;
}

Validate all inputs (quantity > 0, product exists, is_active, stock ≥ quantity or “reserve on pay” policy). Decide reservation strategy (see §8).

5) agent workflow (reference script)

System prompt (excerpt)

  • “You are a purchasing agent. Never invent prices. Use only MCP tools. Before creating a checkout, show the user: product name, price, quantity, total, currency, and ask for Y/N. Use createCheckoutSession only after explicit confirmation.”

Happy path

  1. Agent calls listProducts or searchProducts.

  2. Filters by user criteria (e.g., “shirt under $20”).

  3. Presents top 3 options, asks for confirmation.

  4. On “yes”, calls createCheckoutSession with idempotency_key.

  5. Returns the checkout URL.

  6. After payment, agent can poll getOrderStatus(order_id) if needed (optional; your system emails anyway).

Email/Notification

  • Confirmation email: order summary, expected fulfillment window, support contact.

6) example: critical code slices (trimmed)

stock decrement with check (SQL function)

create or replace function public.decrement_stock(p_product uuid, p_qty int)
returns void language plpgsql as $$
begin
  update public.products
     set stock = stock - p_qty,
         updated_at = now()
   where id = p_product and stock >= p_qty;
  if not found then
    raise exception 'INSUFFICIENT_STOCK';
  end if;
end; $$;

orders: safe state transition (pseudo)

async function transitionOrder(orderId: string, from: string, to: string) {
  await db.rpc('transition_order', { order_id: orderId, from_state: from, to_state: to });
}

(You can implement as a SQL function to guarantee atomicity & guard valid transitions.)

7) security & compliance checklist

  • Secrets isolation: MCP server uses Supabase service key & Stripe secret; never return to agent.

  • RLS: Only service role bypasses; any public reader must see limited projections.

  • Idempotency: All writes require idempotency_key.

  • Input validation: zod or valibot schemas per tool.

  • Webhooks: Verify Stripe signatures; treat events as untrusted until checked.

  • PII minimization: Store buyer email only; no card data (Stripe handles PCI).

  • Auditing: order_events for every change.

  • GDPR: Data retention policy; delete/anonymize on request.

8) stock & race conditions (choose a policy)

  1. Decrement on payment (recommended): No reservation; stock decremented on paid. Simple, tiny risk of oversell bursts.

  2. Soft reservation at checkout: Temporarily reserve; expire after N minutes (requires TTL + cron job).

  3. Hard reservation with payment intent: Decrement on checkout create, release on timeout/fail (more complex).

For most small catalogs, (1) is plenty. If items are scarce/high‑demand, implement (2).

9) observability & ops

  • Emit trace_id per request; log inputs (redacted) + outputs + duration.

  • Metrics: orders by status, checkout conversion, stockouts, webhook errors, idempotency collisions, email send failures.

  • Alerts: webhook 4xx/5xx spikes, stock < threshold, payment failures.

  • Add a /health endpoint for uptime checks.

10) testing matrix

  • List/search products (filters, tags, max_results).

  • Checkout:

    • valid product, invalid product, quantity 0, price drift, currency mismatch.

    • idempotency: same key twice → same order.

  • Webhook:

    • valid signature, invalid signature.

    • duplicate event ID (must be idempotent).

    • insufficient stock branch → refund path.

  • RLS: confirm no sensitive columns leak in product list.

  • Email: success + fallback (log only in dev).

11) minimal MCP tool examples (Typescript, pseudo)

// tools/createCheckoutSession.ts
import { z } from 'zod';
export const input = z.object({
  product_id: z.string().uuid(),
  quantity: z.number().int().positive().max(50),
  buyer_email: z.string().email(),
  idempotency_key: z.string().min(16).max(64),
});
export async function run(args, ctx) {
  const trace_id = ctx.traceId();
  await ctx.db.begin();

  // prevent duplicate work
  const existing = await ctx.db.from('idempotency_keys').select().eq('key', args.idempotency_key).single();
  if (!existing) await ctx.db.insert('idempotency_keys', { key: args.idempotency_key, scope: 'checkout' });

  const product = await ctx.db.from('products').select('*').eq('id', args.product_id).eq('is_active', true).single();
  if (!product) return ctx.error('NOT_FOUND', 'product not found', { trace_id });

  const total = product.price_cents * args.quantity;

  const order = await ctx.db.insert('orders', {
    buyer_email: args.buyer_email,
    status: 'pending_payment',
    total_cents: total,
    currency: product.currency
  }).single();

  await ctx.db.insert('order_items', {
    order_id: order.id, product_id: product.id, quantity: args.quantity, unit_price_cents: product.price_cents
  });

  const session = await ctx.stripe.checkout.sessions.create({
    mode: 'payment',
    line_items: [{ price_data: { currency: product.currency, product_data: { name: product.name }, unit_amount: product.price_cents }, quantity: args.quantity }],
    success_url: `${ctx.cfg.publicBaseUrl}/success?order_id=${order.id}`,
    cancel_url: `${ctx.cfg.publicBaseUrl}/cancel?order_id=${order.id}`,
    metadata: { order_id: order.id },
    payment_intent_data: { metadata: { order_id: order.id } }
  }, { idempotencyKey: args.idempotency_key });

  await ctx.db.update('orders', order.id, { stripe_checkout_id: session.id });
  await ctx.db.insert('order_events', { order_id: order.id, type: 'status_changed', data: { to: 'pending_payment' } });

  await ctx.db.commit();
  return { order_id: order.id, checkout_url: session.url, expires_at: session.expires_at ?? null, trace_id };
}

// webhooks/stripeWebhook.ts (essentials)
const sig = req.headers['stripe-signature'];
const event = stripe.webhooks.constructEvent(req.rawBody, sig, process.env.STRIPE_WEBHOOK_SECRET);
switch (event.type) {
  case 'checkout.session.completed':
  case 'payment_intent.succeeded': {
    const orderId = event.data.object.metadata?.order_id;
    // fetch items, decrement stock atomically, set status=paid, add event, send email
  }
}

12) demo script (end‑to‑end)

  1. Agent: listProducts({ price_max_cents: 2000, tags: ['apparel'] })

  2. Agent (to user): “I found a ‘Logo Tee’ at $18. Want 1?”

  3. User: “Yes.”

  4. Agent: createCheckoutSession({ product_id, quantity:1, buyer_email:'you@ex.com', idempotency_key: 'user-...-ts' })

  5. Agent returns: “Here’s your secure payment link: …”

  6. User pays. Stripe webhook fires → order paid → stock decremented → confirmation email sent (with order id and summary).

  7. Agent (optional): getOrderStatus(order_id) → “Your order is paid and processing.”

13) optional upgrades

  • Vector search for products (pgvector / Supabase AI) backing searchProducts.

  • Bundles & coupons: add promotions table and apply rules in pricing.ts.

  • Multi‑item carts: extend createCheckoutSession to accept an array of {product_id, qty}.

  • Multi‑tenant: add tenant_id to all tables + RLS policies.

  • Rate limiting: per tool, per agent, via token bucket.

  • Trust/Safety: blocklists, maximum order total, anomaly flags.

quick implementation checklist

  • Supabase project + tables + indexes + RLS.

  • Stripe keys + webhook endpoint + secret.

  • MCP server with 5 tools, zod validation, idempotency.

  • Observability: request logs, trace_id, metrics.

  • Confirmation email via Resend/SendGrid.

  • Test matrix passing (including webhook idempotency).

  • Written runbook (rotate keys, replay webhooks, refunds).

MCP, Model Context Protocol, Supabase, StripeFrancesca Tabor