MCP Quoting System

# PostgreSQL Installation Guide for Windows **Quick Start for Phase 1 - Day 3** --- ## 📋 Pre-Installation Checklist Before we begin, make sure you have: - [ ] Administrator access to your Windows computer - [ ] At least 500MB free disk space - [ ] A password you'll remember (for postgres user) - [ ] 15-20 minutes of time --- ## 🚀 Step-by-Step Installation ### Step 1: Download PostgreSQL 1. **Open your browser** and go to: ``` https://www.postgresql.org/download/windows/ ``` 2. **Click** on "Download the installer" 3. **Choose version:** - Recommended: **PostgreSQL 16.x** (latest stable) - Also good: PostgreSQL 15.x - Download the **Windows x86-64** version 4. **Save the file** (postgresql-16.x-windows-x64.exe) - File size: ~300-400 MB - Download time: 2-10 minutes depending on connection ### Step 2: Run the Installer 1. **Double-click** the downloaded .exe file 2. **Allow** Windows UAC prompt (Administrator access) 3. **Setup Wizard - Welcome Screen** - Click "Next" 4. **Installation Directory** - Default: `C:\Program Files\PostgreSQL\16` - ✅ Keep the default - Click "Next" 5. **Select Components** - ✅ PostgreSQL Server (required) - ✅ pgAdmin 4 (visual database tool) - ✅ Stack Builder (for extensions) - ✅ Command Line Tools (required for our scripts) - Click "Next" 6. **Data Directory** - Default: `C:\Program Files\PostgreSQL\16\data` - ✅ Keep the default - Click "Next" 7. **🔑 PASSWORD - VERY IMPORTANT** - Enter a password for the `postgres` superuser - **Write it down!** You'll need this many times - Suggestion: Use something memorable like `postgres123` (for development only) - Re-enter to confirm - Click "Next" 8. **Port Number** - Default: `5432` - ✅ Keep the default (unless you know it conflicts) - Click "Next" 9. **Locale** - Default: `[Default locale]` - ✅ Keep the default - Click "Next" 10. **Pre-Installation Summary** - Review your choices - Click "Next" to begin installation 11. **Installing...** - This takes 3-5 minutes - Progress bar will show installation steps - ☕ Good time for coffee! 12. **Completing Installation** - ✅ CHECK the box: "Launch Stack Builder at exit" - Click "Finish" --- ### Step 3: Install pgvector Extension **Stack Builder will launch automatically:** 1. **Welcome Screen** - Select: PostgreSQL 16 (localhost:5432) - Click "Next" 2. **Categories** - Expand: "Spatial Extensions" - Look for: "pgvector" or "vector extension" - ⚠️ If not available, we'll install manually (see below) 3. **If pgvector is available:** - Check the box - Click "Next" - Follow prompts to install 4. **If pgvector is NOT available (most common):** - Close Stack Builder - We'll install manually in Step 4 --- ### Step 4: Install pgvector Manually (Most Users) **Option A: Using Pre-built Binaries (Easiest)** 1. **Download pgvector:** - Go to: https://github.com/pgvector/pgvector/releases - Download: `pgvector-v0.5.1-windows-x64.zip` (or latest version) 2. **Extract files:** - Right-click → Extract All - You'll see files like: - `vector.dll` - `vector.control` - `vector--0.5.1.sql` 3. **Copy files to PostgreSQL:** Copy `vector.dll` to: ``` C:\Program Files\PostgreSQL\16\lib\ ``` Copy `vector.control` and `vector--*.sql` to: ``` C:\Program Files\PostgreSQL\16\share\extension\ ``` 4. **Restart PostgreSQL:** - Open Windows Services (Win+R, type `services.msc`) - Find "postgresql-x64-16" - Right-click → Restart **Option B: Build from Source (Advanced)** Only if you have Visual Studio and make experience: ```bash git clone https://github.com/pgvector/pgvector.git cd pgvector make make install ``` --- ### Step 5: Verify Installation 1. **Open Command Prompt or PowerShell** 2. **Test PostgreSQL:** ```cmd psql --version ``` Expected output: `psql (PostgreSQL) 16.x` 3. **Connect to PostgreSQL:** ```cmd psql -U postgres ``` - Enter your password when prompted - You should see: `postgres=#` 4. **Test pgvector:** ```sql CREATE EXTENSION vector; ``` Expected: `CREATE EXTENSION` 5. **Verify:** ```sql SELECT * FROM pg_extension WHERE extname = 'vector'; ``` You should see a row with 'vector' 6. **Exit:** ```sql \q ``` --- ## 🎯 Create the Quoting Database Now let's create the database for our project: 1. **Open psql:** ```cmd psql -U postgres ``` 2. **Create database:** ```sql CREATE DATABASE quoting_db; ``` Expected: `CREATE DATABASE` 3. **Connect to it:** ```sql \c quoting_db ``` Expected: `You are now connected to database "quoting_db"` 4. **Enable pgvector:** ```sql CREATE EXTENSION vector; ``` Expected: `CREATE EXTENSION` 5. **Verify:** ```sql SELECT extname, extversion FROM pg_extension WHERE extname = 'vector'; ``` Should show: `vector | 0.5.1` (or current version) 6. **Exit:** ```sql \q ``` --- ## ⚙️ Configure Your Project 1. **Navigate to project folder:** ```cmd cd C:\Users\Rich\OneDrive\Projects\Quoting ``` 2. **Edit .env file:** - Open `.env` in your text editor - Find the line with `DATABASE_URL` - Update it with your password: ```env DATABASE_URL="postgresql://postgres:YOUR_PASSWORD@localhost:5432/quoting_db?schema=public" ``` Replace `YOUR_PASSWORD` with the password you set during installation 3. **Save the file** --- ## 🚀 Run the Setup Now let's set up the database schema and migrate data: 1. **Install dependencies (if not already done):** ```cmd npm install ``` 2. **Run automated setup:** ```cmd setup-phase1.bat ``` OR manually: ```cmd npm run db:setup npm run db:migrate ``` 3. **Expected output:** ``` ✓ Database connected ✓ Prisma Client generated ✓ Database synchronized with schema ✓ Found 5 historical quotes ✓ Migrated 5 quotes ``` --- ## ✅ Verify Everything Works 1. **Start Prisma Studio (visual database browser):** ```cmd npm run prisma:studio ``` Opens at: http://localhost:5555 2. **View your data:** - Click on "Quote" table - You should see your migrated historical quotes - Click on "Evaluation" table to see evaluations 3. **Start the server:** ```cmd npm run dev ``` Should show: ``` ✓ OpenAI Embedding Service initialized ✓ Database connected 🚀 Server running on http://localhost:3789 ``` 4. **Test the API:** Open browser: http://localhost:3789/health Should return: ```json { "status": "healthy", "database": "connected", "vector_search": "enabled" } ``` --- ## 🎉 Success! If you got here, you now have: - ✅ PostgreSQL 16 installed and running - ✅ pgvector extension installed - ✅ quoting_db database created - ✅ All tables created (quotes, evaluations, users, audit_logs, pdf_uploads) - ✅ Historical quotes migrated from JSON - ✅ Embeddings generated (if OpenAI key configured) - ✅ Server running with database connection --- ## 🐛 Troubleshooting ### "psql is not recognized" **Problem:** PostgreSQL not in PATH **Solution:** 1. Add to PATH manually: - Search Windows for "Environment Variables" - Edit "Path" variable - Add: `C:\Program Files\PostgreSQL\16\bin` - Restart Command Prompt ### "password authentication failed" **Problem:** Wrong password in DATABASE_URL **Solution:** 1. Edit `.env` file 2. Update DATABASE_URL with correct password 3. Or reset postgres password: ```cmd psql -U postgres ALTER USER postgres PASSWORD 'newpassword'; ``` ### "extension vector does not exist" **Problem:** pgvector not installed correctly **Solution:** 1. Download pgvector binaries 2. Copy files to PostgreSQL directories (see Step 4) 3. Restart PostgreSQL service 4. Try CREATE EXTENSION again ### "could not connect to server" **Problem:** PostgreSQL service not running **Solution:** 1. Open Services (Win+R → `services.msc`) 2. Find "postgresql-x64-16" 3. Right-click → Start 4. Set startup type to "Automatic" ### "relation does not exist" **Problem:** Database schema not created **Solution:** ```cmd npm run db:setup ``` --- ## 🎓 What You Just Learned You now know how to: - ✅ Install PostgreSQL on Windows - ✅ Install and configure pgvector - ✅ Create databases and extensions - ✅ Use psql command line - ✅ Configure connection strings - ✅ Run database migrations - ✅ Use Prisma Studio to browse data --- ## 📚 Useful Commands ### PostgreSQL Commands: ```cmd psql -U postgres # Connect as postgres user psql -U postgres -d quoting_db # Connect to specific database ``` ### Inside psql: ```sql \l # List all databases \c quoting_db # Connect to database \dt # List all tables \d quotes # Describe quotes table \q # Quit ``` ### Windows Services: ```cmd net start postgresql-x64-16 # Start PostgreSQL net stop postgresql-x64-16 # Stop PostgreSQL services.msc # Open Services GUI ``` ### Project Commands: ```cmd npm run db:setup # Setup database npm run db:migrate # Migrate data npm run prisma:studio # Visual database browser npm run dev # Start server ``` --- ## 🚀 Next Steps Now that PostgreSQL is installed and working: 1. **Test the system:** - Run `npm run dev` - Test with: `quick-test.bat` - Check web interface: http://localhost:3789 2. **Continue Phase 1:** - Move to Day 3-4: Data Access Layer integration - See PHASE-1-PLAN.md for details 3. **Explore Prisma Studio:** - `npm run prisma:studio` - Browse your data visually - Try adding/editing records --- ## 📞 Need Help? - **Documentation:** See DATABASE-SETUP.md for more details - **PostgreSQL Docs:** https://www.postgresql.org/docs/ - **Prisma Docs:** https://www.prisma.io/docs - **pgvector Docs:** https://github.com/pgvector/pgvector --- **🎉 Congratulations! PostgreSQL is ready for Phase 1!** *Installation guide created: November 13, 2024*