MCP Quoting System

# Railway PostgreSQL Setup Guide **Quick setup for MCP Quoting System with Railway** --- ## 🚀 Step-by-Step Setup ### Step 1: Create PostgreSQL Database 1. **Log into Railway** - Go to https://railway.app - Sign in with your account 2. **Create New Project** - Click "New Project" - Name it: "quoting-system" (or your preference) 3. **Add PostgreSQL Database** - Click "+ New" - Select "Database" - Choose "PostgreSQL" - Railway will provision it (takes ~30 seconds) ### Step 2: Install pgvector Extension 1. **Open PostgreSQL Settings** - Click on your PostgreSQL service - Go to "Connect" tab - Copy the PSQL command (looks like: `psql postgresql://...`) 2. **Connect via Command Line** Open PowerShell or Command Prompt: ```bash # Paste the connection command Railway gave you psql postgresql://postgres:PASSWORD@containers-us-west-xxx.railway.app:PORT/railway ``` 3. **Enable pgvector** ```sql CREATE EXTENSION vector; -- Verify installation SELECT extname, extversion FROM pg_extension WHERE extname = 'vector'; -- You should see: vector | 0.5.1 (or similar) -- Exit \q ``` ### Step 3: Create Your Database While connected: ```sql -- Create the quoting database CREATE DATABASE quoting_db; -- Connect to it \c quoting_db -- Enable pgvector in this database CREATE EXTENSION vector; -- Verify \dt -- Exit \q ``` ### Step 4: Get Connection String 1. **In Railway Dashboard:** - Click on PostgreSQL service - Go to "Variables" tab - You'll see several connection variables 2. **Copy the DATABASE_URL** - Look for: `DATABASE_URL` - It looks like: ``` postgresql://postgres:PASSWORD@containers-us-west-xxx.railway.app:PORT/railway ``` 3. **Modify for Your Database** Change `railway` at the end to `quoting_db`: ``` postgresql://postgres:PASSWORD@containers-us-west-xxx.railway.app:PORT/quoting_db ``` ### Step 5: Configure Your Project 1. **Edit .env file** Open: `C:\Users\Rich\OneDrive\Projects\Quoting\.env` Update: ```env DATABASE_URL="postgresql://postgres:PASSWORD@containers-us-west-xxx.railway.app:PORT/quoting_db" ``` 2. **Save the file** ### Step 6: Initialize Database Schema ```powershell # Navigate to project cd C:\Users\Rich\OneDrive\Projects\Quoting # Install dependencies (if needed) npm install # Generate Prisma Client npm run prisma:generate # Push schema to Railway database npm run db:setup # Migrate existing quotes npm run db:migrate ``` ### Step 7: Verify Everything Works ```powershell # Start Prisma Studio (visual database browser) npm run prisma:studio # Opens at http://localhost:5555 # You should see your Quote and Evaluation tables with data # Start development server npm run dev # Test health endpoint # Open browser: http://localhost:3789/health ``` Expected response: ```json { "status": "healthy", "database": "connected", "vector_search": "enabled" } ``` --- ## 🎯 Railway Features You'll Love ### 1. Environment Variables In Railway dashboard: - Go to your PostgreSQL service - Click "Variables" tab - All connection details are here - Can add custom variables ### 2. Metrics & Monitoring - See database usage - Query performance - Storage usage - Connection count ### 3. Automatic Backups - Railway backs up your database automatically - Can restore to any point ### 4. Easy Scaling - Upgrade plan as you grow - Increase storage/memory - Add read replicas --- ## 💰 Railway Pricing **Hobby Plan (What you have):** - $5 free credit per month - PostgreSQL usage: ~$5-10/month - 1GB storage included - Good for development and small production **Pro Plan ($20/month if you need it later):** - $20 included usage - More resources - Priority support --- ## 🌐 Optional: Deploy to Railway If you want to deploy your Node.js app to Railway too: ### Quick Deploy: 1. **In Railway dashboard:** - Click "+ New" - Select "GitHub Repo" - Connect your repo 2. **Configure Build:** - Build command: `npm run build` - Start command: `npm start` 3. **Add Environment Variables:** - `DATABASE_URL` → Link to your PostgreSQL service - `OPENAI_API_KEY` → Your OpenAI key - `PORT` → Will auto-set 4. **Deploy:** - Push to GitHub - Railway auto-deploys - Get public URL --- ## 🔗 Railway + Vercel Strategy **Option A: Everything on Railway** ``` User → Railway (Node.js + PostgreSQL) ``` - Simpler - Single platform - Good for APIs **Option B: Split Architecture (Recommended)** ``` User → Vercel (Frontend/API routes) ↓ Railway (PostgreSQL only) ``` - Vercel: Fast edge hosting for frontend - Railway: Database backend - Best performance **Option C: Hybrid** ``` User → Vercel (Next.js frontend + API routes) ↓ Railway (Node.js MCP server + PostgreSQL) ``` - Vercel: User interface - Railway: Heavy processing + database - Most flexible --- ## 📝 Connection String Format Railway gives you several formats: **Internal URL (if app is on Railway too):** ``` postgresql://postgres:pass@postgres.railway.internal:5432/quoting_db ``` **External URL (for local dev):** ``` postgresql://postgres:pass@containers-us-west-xxx.railway.app:PORT/quoting_db ``` **With SSL (recommended for production):** ``` postgresql://postgres:pass@containers-us-west-xxx.railway.app:PORT/quoting_db?sslmode=require ``` Use the external URL in your local `.env` file. --- ## 🐛 Troubleshooting ### "role postgres does not exist" Railway's PostgreSQL might use different user. Check Variables tab for actual username. ### "database railway does not exist" You need to create `quoting_db`: ```sql psql [CONNECTION_STRING] CREATE DATABASE quoting_db; \c quoting_db CREATE EXTENSION vector; ``` ### "connection timeout" - Check Railway service is running (green dot) - Verify connection string is correct - Check firewall/VPN settings ### "extension vector not found" Run in psql: ```sql CREATE EXTENSION vector; ``` ### "too many connections" Railway hobby plan has connection limits. Use connection pooling: ```env DATABASE_URL="postgresql://...?connection_limit=5" ``` --- ## ✅ Success Checklist - [ ] Railway PostgreSQL service created - [ ] pgvector extension installed - [ ] quoting_db database created - [ ] Connection string copied to .env - [ ] `npm run db:setup` completed successfully - [ ] `npm run db:migrate` migrated quotes - [ ] Prisma Studio shows data - [ ] `npm run dev` starts without errors - [ ] Health check returns "connected" --- ## 🚀 Next Steps After Setup 1. **Test the system:** ```bash npm run dev quick-test.bat ``` 2. **Generate embeddings (optional):** - Add OPENAI_API_KEY to .env - Embeddings will generate on next migration 3. **Deploy to Railway (optional):** - Connect GitHub repo - Auto-deploy on push 4. **Add Vercel frontend (optional):** - We can build a Next.js UI - Connect to Railway database --- ## 📊 Railway Dashboard Overview **What to monitor:** - **Deployments:** See build/deploy status - **Metrics:** CPU, memory, network usage - **Logs:** Real-time application logs - **Variables:** Environment configuration - **Settings:** Domain, scaling, backups --- ## 💡 Pro Tips 1. **Use Connection Pooling:** ```env DATABASE_URL="postgresql://...?connection_limit=5&pool_timeout=30" ``` 2. **Enable SSL in Production:** ```env DATABASE_URL="postgresql://...?sslmode=require" ``` 3. **Set Up Staging:** - Create second PostgreSQL service - Use for testing before production 4. **Monitor Usage:** - Watch credit usage in Railway - Set up alerts - Optimize queries --- ## 🔗 Useful Links - **Railway Dashboard:** https://railway.app/dashboard - **Railway Docs:** https://docs.railway.app/ - **pgvector Docs:** https://github.com/pgvector/pgvector - **Prisma Railway Guide:** https://www.prisma.io/docs/guides/deployment/railway --- **Ready to start? Let's get Railway configured!**