You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
This document specifies the requirements and technical implementation details for converting the local Recharge Storefront API MCP Server into a publicly hosted, multi-tenant service. The public server will enable AI assistants to manage Recharge subscriptions through the Model Context Protocol (MCP) without requiring users to run local infrastructure.
1.2 Infrastructure Choice
Component
Technology
Purpose
Serverless Functions
Netlify Functions
MCP gateway, tool execution, management APIs
Database
Supabase PostgreSQL
Credential storage, session cache, usage logs
Authentication
Custom API Keys
Secure access to MCP endpoints
CDN/Edge
Netlify Edge
Global distribution, low latency
1.3 Goals
Accessibility: Enable any MCP-compatible AI client to access Recharge APIs without local setup
Multi-tenancy: Support multiple Shopify stores and users simultaneously
Security: Secure credential storage and transmission with enterprise-grade protection
Scalability: Handle high request volumes with automatic scaling via Netlify
Reliability: 99.9% uptime with proper error handling and recovery
Shopify store configuration with encrypted Recharge tokens
api_keys
MCP API key management with scopes and rate limits
session_tokens
Cached Recharge session tokens per customer
usage_logs
API usage tracking and analytics
rate_limits
Per-key rate limit tracking
3.3 Request Flow
1. MCP Client sends HTTPS POST to Netlify Function with API key
2. Netlify Function validates API key against Supabase
3. Check rate limits in Supabase
4. Retrieve store credentials from Supabase (decrypt admin token)
5. Check/create Recharge session token in cache
6. Execute tool against Recharge Storefront API
7. Format and return MCP response
8. Log usage to Supabase
The database uses Supabase PostgreSQL with Row Level Security (RLS) enabled on all tables.
4.2 Migration Files
Migration 001: Create Stores Table
/* # Create stores table 1. New Tables - `stores` - `id` (uuid, primary key) - `user_id` (uuid, references auth.users) - `shopify_domain` (text, unique per user) - `store_name` (text) - `recharge_admin_token_encrypted` (text, encrypted) - `recharge_api_url` (text, optional custom URL) - `is_active` (boolean) - `created_at` (timestamptz) - `updated_at` (timestamptz) 2. Security - Enable RLS on `stores` table - Users can only access their own stores*/CREATETABLEIF NOT EXISTS stores (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id uuid NOT NULLREFERENCESauth.users(id) ON DELETE CASCADE,
shopify_domain textNOT NULL,
store_name text,
recharge_admin_token_encrypted textNOT NULL,
recharge_api_url text DEFAULT 'https://api.rechargeapps.com',
is_active boolean DEFAULT true,
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now(),
CONSTRAINT unique_user_domain UNIQUE (user_id, shopify_domain),
CONSTRAINT valid_shopify_domain CHECK (shopify_domain ~ '^[a-z0-9-]+\.myshopify\.com$')
);
CREATEINDEXIF NOT EXISTS idx_stores_user_id ON stores(user_id);
CREATEINDEXIF NOT EXISTS idx_stores_shopify_domain ON stores(shopify_domain);
CREATEINDEXIF NOT EXISTS idx_stores_is_active ON stores(is_active) WHERE is_active = true;
ALTERTABLE stores ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view own stores"ON stores FOR SELECT
TO authenticated
USING (auth.uid() = user_id);
CREATE POLICY "Users can insert own stores"ON stores FOR INSERT
TO authenticated
WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update own stores"ON stores FOR UPDATE
TO authenticated
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can delete own stores"ON stores FOR DELETE
TO authenticated
USING (auth.uid() = user_id);
-- Service role can access all stores (for Netlify Functions)
CREATE POLICY "Service role can access all stores"ON stores FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
-- Trigger to update updated_at timestampCREATE OR REPLACEFUNCTIONupdate_updated_at_column()
RETURNS TRIGGER AS $$
BEGINNEW.updated_at= now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATETRIGGERupdate_stores_updated_at
BEFORE UPDATEON stores
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
Migration 002: Create API Keys Table
/* # Create api_keys table 1. New Tables - `api_keys` - `id` (uuid, primary key) - `user_id` (uuid, references auth.users) - `store_id` (uuid, references stores) - `key_hash` (text, SHA-256 hash of API key) - `key_prefix` (text, first 8 chars for identification) - `name` (text, user-friendly name) - `scopes` (text[], allowed operations) - `rate_limit_tier` (text, rate limit tier) - `is_active` (boolean) - `last_used_at` (timestamptz) - `expires_at` (timestamptz, optional) - `created_at` (timestamptz) 2. Security - Enable RLS - Users can only manage their own API keys*/CREATETABLEIF NOT EXISTS api_keys (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id uuid NOT NULLREFERENCESauth.users(id) ON DELETE CASCADE,
store_id uuid NOT NULLREFERENCES stores(id) ON DELETE CASCADE,
key_hash textNOT NULL UNIQUE,
key_prefix textNOT NULL,
name textNOT NULL,
scopes text[] NOT NULL DEFAULT ARRAY['read'],
rate_limit_tier textNOT NULL DEFAULT 'standard',
is_active boolean DEFAULT true,
last_used_at timestamptz,
expires_at timestamptz,
created_at timestamptz DEFAULT now(),
CONSTRAINT valid_rate_limit_tier CHECK (rate_limit_tier IN ('free', 'standard', 'professional', 'enterprise')),
CONSTRAINT valid_scopes CHECK (scopes <@ ARRAY['read', 'write', 'admin']::text[])
);
CREATEINDEXIF NOT EXISTS idx_api_keys_key_hash ON api_keys(key_hash);
CREATEINDEXIF NOT EXISTS idx_api_keys_store_id ON api_keys(store_id);
CREATEINDEXIF NOT EXISTS idx_api_keys_user_id ON api_keys(user_id);
CREATEINDEXIF NOT EXISTS idx_api_keys_is_active ON api_keys(is_active) WHERE is_active = true;
ALTERTABLE api_keys ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view own api_keys"ON api_keys FOR SELECT
TO authenticated
USING (auth.uid() = user_id);
CREATE POLICY "Users can insert own api_keys"ON api_keys FOR INSERT
TO authenticated
WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update own api_keys"ON api_keys FOR UPDATE
TO authenticated
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can delete own api_keys"ON api_keys FOR DELETE
TO authenticated
USING (auth.uid() = user_id);
-- Service role can access all api_keys (for Netlify Functions)
CREATE POLICY "Service role can access all api_keys"ON api_keys FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
Migration 003: Create Session Tokens Table
/* # Create session_tokens table 1. New Tables - `session_tokens` - `id` (uuid, primary key) - `store_id` (uuid, references stores) - `customer_id` (text, Recharge customer ID) - `customer_email` (text, optional email for lookup) - `session_token_encrypted` (text, encrypted token) - `created_at` (timestamptz) - `last_used_at` (timestamptz) - `expires_at` (timestamptz) 2. Security - Enable RLS - Service role only access (Netlify Functions)*/CREATETABLEIF NOT EXISTS session_tokens (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
store_id uuid NOT NULLREFERENCES stores(id) ON DELETE CASCADE,
customer_id textNOT NULL,
customer_email text,
session_token_encrypted textNOT NULL,
created_at timestamptz DEFAULT now(),
last_used_at timestamptz DEFAULT now(),
expires_at timestamptz DEFAULT (now() + interval '4 hours'),
CONSTRAINT unique_store_customer UNIQUE (store_id, customer_id)
);
CREATEINDEXIF NOT EXISTS idx_session_tokens_store_customer ON session_tokens(store_id, customer_id);
CREATEINDEXIF NOT EXISTS idx_session_tokens_expires_at ON session_tokens(expires_at);
CREATEINDEXIF NOT EXISTS idx_session_tokens_customer_email ON session_tokens(store_id, customer_email) WHERE customer_email IS NOT NULL;
ALTERTABLE session_tokens ENABLE ROW LEVEL SECURITY;
-- Only service role can access session tokens (Netlify Functions)
CREATE POLICY "Service role can manage session_tokens"ON session_tokens FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
Migration 004: Create Usage Logs Table
/* # Create usage_logs table 1. New Tables - `usage_logs` - `id` (uuid, primary key) - `api_key_id` (uuid, references api_keys) - `store_id` (uuid, references stores) - `user_id` (uuid, references auth.users) - `tool_name` (text) - `customer_id` (text, optional) - `status` (text, success/error) - `error_message` (text, optional) - `execution_time_ms` (integer) - `request_metadata` (jsonb) - `created_at` (timestamptz) 2. Security - Enable RLS - Users can view their own usage logs*/CREATETABLEIF NOT EXISTS usage_logs (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
api_key_id uuid REFERENCES api_keys(id) ON DELETESETNULL,
store_id uuid REFERENCES stores(id) ON DELETESETNULL,
user_id uuid REFERENCESauth.users(id) ON DELETESETNULL,
tool_name textNOT NULL,
customer_id text,
status textNOT NULL DEFAULT 'success',
error_message text,
execution_time_ms integer,
request_metadata jsonb DEFAULT '{}',
created_at timestamptz DEFAULT now(),
CONSTRAINT valid_status CHECK (status IN ('success', 'error', 'rate_limited'))
);
CREATEINDEXIF NOT EXISTS idx_usage_logs_api_key_id ON usage_logs(api_key_id);
CREATEINDEXIF NOT EXISTS idx_usage_logs_store_id ON usage_logs(store_id);
CREATEINDEXIF NOT EXISTS idx_usage_logs_user_id ON usage_logs(user_id);
CREATEINDEXIF NOT EXISTS idx_usage_logs_created_at ON usage_logs(created_at DESC);
CREATEINDEXIF NOT EXISTS idx_usage_logs_tool_name ON usage_logs(tool_name);
CREATEINDEXIF NOT EXISTS idx_usage_logs_status ON usage_logs(status);
-- Partition by month for better performance (optional)-- CREATE INDEX IF NOT EXISTS idx_usage_logs_created_at_month ON usage_logs(date_trunc('month', created_at));ALTERTABLE usage_logs ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view own usage_logs"ON usage_logs FOR SELECT
TO authenticated
USING (auth.uid() = user_id);
-- Service role can insert and select usage logs
CREATE POLICY "Service role can manage usage_logs"ON usage_logs FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
Migration 005: Create Rate Limits Table
/* # Create rate_limits table 1. New Tables - `rate_limits` - `id` (uuid, primary key) - `api_key_id` (uuid, references api_keys) - `window_start` (timestamptz) - `request_count` (integer) 2. Security - Service role only*/CREATETABLEIF NOT EXISTS rate_limits (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
api_key_id uuid NOT NULLREFERENCES api_keys(id) ON DELETE CASCADE,
window_start timestamptzNOT NULL,
request_count integerNOT NULL DEFAULT 1,
CONSTRAINT unique_api_key_window UNIQUE (api_key_id, window_start)
);
CREATEINDEXIF NOT EXISTS idx_rate_limits_api_key_window ON rate_limits(api_key_id, window_start);
CREATEINDEXIF NOT EXISTS idx_rate_limits_window_start ON rate_limits(window_start);
ALTERTABLE rate_limits ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Service role can manage rate_limits"ON rate_limits FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
Migration 006: Create Database Functions
/* # Create database functions 1. Functions - encrypt_token / decrypt_token (using pgcrypto) - check_rate_limit - cleanup_expired_sessions - increment_rate_limit*/-- Enable pgcrypto extension
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- Function to check and increment rate limit-- Returns true if within limit, false if exceededCREATE OR REPLACEFUNCTIONcheck_and_increment_rate_limit(
p_api_key_id uuid,
p_tier text,
p_window_minutes integer DEFAULT 1
)
RETURNS jsonb AS $$
DECLARE
v_limit integer;
v_current_count integer;
v_window_start timestamptz;
v_within_limit boolean;
BEGIN-- Define limits per tier (requests per minute)
v_limit := CASE p_tier
WHEN 'free' THEN 10
WHEN 'standard' THEN 60
WHEN 'professional' THEN 300
WHEN 'enterprise' THEN 1000
ELSE 10
END;
-- Calculate window start (truncate to minute)
v_window_start := date_trunc('minute', now());
-- Get or create rate limit recordINSERT INTO rate_limits (api_key_id, window_start, request_count)
VALUES (p_api_key_id, v_window_start, 1)
ON CONFLICT (api_key_id, window_start)
DO UPDATESET request_count =rate_limits.request_count+1
RETURNING request_count INTO v_current_count;
v_within_limit := v_current_count <= v_limit;
RETURN jsonb_build_object(
'within_limit', v_within_limit,
'current_count', v_current_count,
'limit', v_limit,
'window_start', v_window_start,
'reset_at', v_window_start + (p_window_minutes ||' minutes')::interval
);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Function to cleanup expired sessionsCREATE OR REPLACEFUNCTIONcleanup_expired_sessions()
RETURNS integerAS $$
DECLARE
v_deleted integer;
BEGIN
WITH deleted AS (
DELETEFROM session_tokens
WHERE expires_at < now()
RETURNING id
)
SELECTCOUNT(*) INTO v_deleted FROM deleted;
RETURN v_deleted;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Function to cleanup old rate limit recordsCREATE OR REPLACEFUNCTIONcleanup_old_rate_limits()
RETURNS integerAS $$
DECLARE
v_deleted integer;
BEGIN
WITH deleted AS (
DELETEFROM rate_limits
WHERE window_start < now() - interval '1 hour'
RETURNING id
)
SELECTCOUNT(*) INTO v_deleted FROM deleted;
RETURN v_deleted;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Function to get session token (with expiry check)CREATE OR REPLACEFUNCTIONget_valid_session_token(
p_store_id uuid,
p_customer_id text
)
RETURNS TABLE(
session_token_encrypted text,
customer_email text,
is_valid boolean
) AS $$
BEGIN
RETURN QUERY
SELECTst.session_token_encrypted,
st.customer_email,
(st.expires_at> now()) as is_valid
FROM session_tokens st
WHEREst.store_id= p_store_id
ANDst.customer_id= p_customer_id;
-- Update last_used_at if foundUPDATE session_tokens
SET last_used_at = now()
WHERE store_id = p_store_id
AND customer_id = p_customer_id
AND expires_at > now();
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Function to upsert session tokenCREATE OR REPLACEFUNCTIONupsert_session_token(
p_store_id uuid,
p_customer_id text,
p_customer_email text,
p_session_token_encrypted text,
p_expires_hours integer DEFAULT 4
)
RETURNS void AS $$
BEGININSERT INTO session_tokens (
store_id,
customer_id,
customer_email,
session_token_encrypted,
expires_at
)
VALUES (
p_store_id,
p_customer_id,
p_customer_email,
p_session_token_encrypted,
now() + (p_expires_hours ||' hours')::interval
)
ON CONFLICT (store_id, customer_id)
DO UPDATESET
customer_email =EXCLUDED.customer_email,
session_token_encrypted =EXCLUDED.session_token_encrypted,
last_used_at = now(),
expires_at = now() + (p_expires_hours ||' hours')::interval;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Function to validate API key and return detailsCREATE OR REPLACEFUNCTIONvalidate_api_key(p_key_hash text)
RETURNS jsonb AS $$
DECLARE
v_result jsonb;
BEGINSELECT jsonb_build_object(
'is_valid', true,
'key_id', ak.id,
'user_id', ak.user_id,
'store_id', ak.store_id,
'scopes', ak.scopes,
'rate_limit_tier', ak.rate_limit_tier,
'shopify_domain', s.shopify_domain,
'recharge_admin_token_encrypted', s.recharge_admin_token_encrypted,
'recharge_api_url', s.recharge_api_url
)
INTO v_result
FROM api_keys ak
JOIN stores s ONs.id=ak.store_idWHEREak.key_hash= p_key_hash
ANDak.is_active= true
ANDs.is_active= true
AND (ak.expires_at IS NULLORak.expires_at> now());
IF v_result IS NULL THEN
RETURN jsonb_build_object('is_valid', false, 'error', 'Invalid or expired API key');
END IF;
-- Update last_used_atUPDATE api_keys
SET last_used_at = now()
WHERE key_hash = p_key_hash;
RETURN v_result;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
// src/lib/encryption.tsimport{createCipheriv,createDecipheriv,randomBytes,scryptSync}from'crypto';constALGORITHM='aes-256-gcm';constKEY_LENGTH=32;constIV_LENGTH=16;constSALT_LENGTH=16;constTAG_LENGTH=16;exportfunctionencryptToken(token: string): string{constencryptionKey=process.env.ENCRYPTION_KEY!;constsalt=randomBytes(SALT_LENGTH);constkey=scryptSync(encryptionKey,salt,KEY_LENGTH);constiv=randomBytes(IV_LENGTH);constcipher=createCipheriv(ALGORITHM,key,iv);constencrypted=Buffer.concat([cipher.update(token,'utf8'),cipher.final()]);consttag=cipher.getAuthTag();// Format: salt (16) + iv (16) + tag (16) + encryptedconstresult=Buffer.concat([salt,iv,tag,encrypted]);returnresult.toString('base64');}exportfunctiondecryptToken(encryptedToken: string): string{constencryptionKey=process.env.ENCRYPTION_KEY!;constdata=Buffer.from(encryptedToken,'base64');constsalt=data.subarray(0,SALT_LENGTH);constiv=data.subarray(SALT_LENGTH,SALT_LENGTH+IV_LENGTH);consttag=data.subarray(SALT_LENGTH+IV_LENGTH,SALT_LENGTH+IV_LENGTH+TAG_LENGTH);constencrypted=data.subarray(SALT_LENGTH+IV_LENGTH+TAG_LENGTH);constkey=scryptSync(encryptionKey,salt,KEY_LENGTH);constdecipher=createDecipheriv(ALGORITHM,key,iv);decipher.setAuthTag(tag);constdecrypted=Buffer.concat([decipher.update(encrypted),decipher.final()]);returndecrypted.toString('utf8');}
6.6 Security Headers
// Applied to all responsesconstsecurityHeaders={'Strict-Transport-Security': 'max-age=31536000; includeSubDomains','X-Content-Type-Options': 'nosniff','X-Frame-Options': 'DENY','X-XSS-Protection': '1; mode=block','Content-Security-Policy': "default-src 'none'",'Cache-Control': 'no-store, no-cache, must-revalidate','Referrer-Policy': 'strict-origin-when-cross-origin'};
7. Tool Definitions
7.1 Tool Input Schema (Public Server)
For the public server, authentication parameters are removed from individual tool schemas since authentication is handled at the gateway level via API key:
1. Tool call received with customer_id or customer_email
2. Check Supabase for cached session token (with expiry check)
3. If cached and not expired:
a. Decrypt token
b. Update last_used_at
c. Return decrypted token
4. If not cached or expired:
a. Look up customer via Admin API (if email provided)
b. Create session via Admin API
c. Encrypt and store in Supabase
d. Return new token
5. On 401/403 from Recharge:
a. Clear cached session from Supabase
b. Retry with new session (up to 2 retries)
-- Requests per tool (last 24 hours)SELECT tool_name, COUNT(*) as count
FROM usage_logs
WHERE created_at > now() - interval '24 hours'GROUP BY tool_name
ORDER BY count DESC;
-- Error rate per storeSELECT
store_id,
COUNT(*) FILTER (WHERE status ='error') as errors,
COUNT(*) as total,
ROUND(COUNT(*) FILTER (WHERE status ='error')::numeric/COUNT(*) *100, 2) as error_rate
FROM usage_logs
WHERE created_at > now() - interval '1 hour'GROUP BY store_id
HAVINGCOUNT(*) >10ORDER BY error_rate DESC;
-- Average response time per toolSELECT
tool_name,
AVG(execution_time_ms) as avg_ms,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY execution_time_ms) as p95_ms
FROM usage_logs
WHERE created_at > now() - interval '1 hour'GROUP BY tool_name;
-- Top users by request countSELECT
user_id,
COUNT(*) as requests,
COUNT(*) FILTER (WHERE status ='error') as errors
FROM usage_logs
WHERE created_at > now() - interval '24 hours'GROUP BY user_id
ORDER BY requests DESCLIMIT10;
13. Deployment Guide
13.1 Prerequisites
Netlify account (Pro plan recommended for scheduled functions)
Integration Tests: API key validation, session cache, rate limiting
End-to-End Tests: Full MCP request/response cycle
Load Tests: Performance under high concurrency
14.2 Test Commands
# Run all tests
npm test# Run specific test suite
npm run test:unit
npm run test:integration
npm run test:e2e
# Load testing with k6
k6 run tests/load/mcp-gateway.js
15. Migration from Local Server
15.1 Breaking Changes
Authentication: Requires API key instead of environment variables
Transport: HTTP POST instead of stdio
Removed Parameters: store_url, admin_token, session_token removed from tool schemas
All Recharge tokens encrypted at rest (AES-256-GCM)
API keys hashed with SHA-256
RLS enabled on all Supabase tables
HTTPS only (enforced by Netlify)
Rate limiting per API key
Usage logging for audit trail
Input validation with Zod schemas
Security headers on all responses
Document Version History
Version
Date
Changes
1.0.0
2024-12-24
Initial PRD with Supabase Edge Functions
1.1.0
2024-12-24
Updated to Netlify Functions + Supabase Database
This document provides the complete specification for building the Public Recharge Storefront API MCP Server using Netlify Functions for compute and Supabase for database. All 46 tools from the local server are preserved with full feature parity.