MCP Quoting System

/** * PostgreSQL Storage Service * * Implementation of IStorageService using PostgreSQL with Prisma ORM. * Supports vector search for semantic similarity matching. */ import { PrismaClient, Prisma } from '@prisma/client'; import { IStorageService, QuoteSearchCriteria, QuoteWithSimilarity, AuditLogEntry } from './interface'; import { HistoricalQuote, RFPEvaluation } from '../types'; export class PostgresStorageService implements IStorageService { private prisma: PrismaClient; private static instance: PostgresStorageService; private constructor() { this.prisma = new PrismaClient({ log: process.env.NODE_ENV === 'development' ? ['query', 'error', 'warn'] : ['error'], }); } public static getInstance(): PostgresStorageService { if (!PostgresStorageService.instance) { PostgresStorageService.instance = new PostgresStorageService(); } return PostgresStorageService.instance; } // ===== QUOTES ===== async getAllQuotes(): Promise<HistoricalQuote[]> { const quotes = await this.prisma.quote.findMany({ orderBy: { quoteDate: 'desc' } }); return quotes.map(this.mapQuoteFromDb); } async getQuoteById(id: string): Promise<HistoricalQuote | null> { const quote = await this.prisma.quote.findUnique({ where: { id } }); return quote ? this.mapQuoteFromDb(quote) : null; } async addQuote(quote: HistoricalQuote): Promise<void> { await this.prisma.quote.create({ data: this.mapQuoteToDb(quote) }); } async updateQuote(id: string, quote: Partial<HistoricalQuote>): Promise<void> { await this.prisma.quote.update({ where: { id }, data: this.mapQuoteToDb(quote as HistoricalQuote) }); } async deleteQuote(id: string): Promise<void> { await this.prisma.quote.delete({ where: { id } }); } async searchQuotes(criteria: QuoteSearchCriteria): Promise<HistoricalQuote[]> { const where: Prisma.QuoteWhereInput = {}; if (criteria.material) { where.material = { contains: criteria.material, mode: 'insensitive' }; } if (criteria.processes && criteria.processes.length > 0) { where.processes = { hasSome: criteria.processes }; } if (criteria.qtyMin !== undefined) { where.qtyMin = { gte: criteria.qtyMin }; } if (criteria.qtyMax !== undefined) { where.qtyMax = { lte: criteria.qtyMax }; } if (criteria.customerName) { where.customerName = { contains: criteria.customerName, mode: 'insensitive' }; } if (criteria.dateFrom || criteria.dateTo) { where.quoteDate = {}; if (criteria.dateFrom) where.quoteDate.gte = criteria.dateFrom; if (criteria.dateTo) where.quoteDate.lte = criteria.dateTo; } if (criteria.approved !== undefined) { where.approved = criteria.approved; } const quotes = await this.prisma.quote.findMany({ where, orderBy: { quoteDate: 'desc' } }); return quotes.map(this.mapQuoteFromDb); } // ===== VECTOR SEARCH ===== async searchQuotesByVector(embedding: number[], limit: number = 5): Promise<QuoteWithSimilarity[]> { // Using pgvector extension for cosine similarity // Raw SQL query because Prisma doesn't fully support vector operations yet const embeddingStr = `[${embedding.join(',')}]`; const result = await this.prisma.$queryRaw<any[]>` SELECT id, quote_date, customer_name, contact_email, material, processes, qty_min, qty_max, tolerances, finish, cost_per_unit, total_cost, lead_days, actual_lead_days, approved, won, notes, raw_rfp, created_at, updated_at, created_by, 1 - (embedding <=> ${embeddingStr}::vector) as similarity FROM quotes WHERE embedding IS NOT NULL ORDER BY embedding <=> ${embeddingStr}::vector LIMIT ${limit} `; return result.map(row => ({ ...this.mapQuoteFromDbRaw(row), similarity: parseFloat(row.similarity) || 0 })); } async updateQuoteEmbedding(id: string, embedding: number[]): Promise<void> { const embeddingStr = `[${embedding.join(',')}]`; await this.prisma.$executeRaw` UPDATE quotes SET embedding = ${embeddingStr}::vector WHERE id = ${id} `; } // ===== EVALUATIONS ===== async getAllEvaluations(): Promise<RFPEvaluation[]> { const evaluations = await this.prisma.evaluation.findMany({ orderBy: { createdAt: 'desc' }, take: 100 // Limit to recent 100 }); return evaluations.map(this.mapEvaluationFromDb); } async getEvaluationById(id: string): Promise<RFPEvaluation | null> { const evaluation = await this.prisma.evaluation.findUnique({ where: { id } }); return evaluation ? this.mapEvaluationFromDb(evaluation) : null; } async getEvaluationByIdempotencyKey(key: string): Promise<RFPEvaluation | null> { const evaluation = await this.prisma.evaluation.findUnique({ where: { idempotencyKey: key } }); return evaluation ? this.mapEvaluationFromDb(evaluation) : null; } async addEvaluation(evaluation: RFPEvaluation): Promise<void> { await this.prisma.evaluation.create({ data: this.mapEvaluationToDb(evaluation) }); } async updateEvaluation(id: string, evaluation: Partial<RFPEvaluation>): Promise<void> { const updateData: any = {}; if (evaluation.status) updateData.status = evaluation.status; if (evaluation.approvedBy) updateData.approvedBy = evaluation.approvedBy; if (evaluation.approvedAt) updateData.approvedAt = evaluation.approvedAt; if (evaluation.sentAt) updateData.sentAt = evaluation.sentAt; if (evaluation.parsedRfp) updateData.parsedRfp = evaluation.parsedRfp; if (evaluation.matches) updateData.matches = evaluation.matches; if (evaluation.estimate) updateData.estimate = evaluation.estimate; if (evaluation.quoteDoc) updateData.quoteDoc = evaluation.quoteDoc; if (evaluation.confidence) updateData.confidence = evaluation.confidence; await this.prisma.evaluation.update({ where: { id }, data: updateData }); } // ===== AUDIT LOGGING ===== async logAudit(entry: AuditLogEntry): Promise<void> { await this.prisma.auditLog.create({ data: { action: entry.action, resourceType: entry.resourceType, resourceId: entry.resourceId, details: entry.details, userId: entry.userId, ipAddress: entry.ipAddress, userAgent: entry.userAgent } }); } // ===== HEALTH CHECK ===== async healthCheck(): Promise<boolean> { try { await this.prisma.$queryRaw`SELECT 1`; return true; } catch (error) { console.error('Database health check failed:', error); return false; } } // ===== CLEANUP ===== async disconnect(): Promise<void> { await this.prisma.$disconnect(); } // ===== MAPPING FUNCTIONS ===== private mapQuoteFromDb(dbQuote: any): HistoricalQuote { return { id: dbQuote.id, quoteDate: dbQuote.quoteDate.toISOString(), customerName: dbQuote.customerName, contactEmail: dbQuote.contactEmail || undefined, normalized: { material: dbQuote.material || undefined, processes: dbQuote.processes || [], qtyRange: [dbQuote.qtyMin || 0, dbQuote.qtyMax || 0], tolerances: dbQuote.tolerances || undefined, finish: dbQuote.finish || undefined }, costPerUnit: parseFloat(dbQuote.costPerUnit.toString()), totalCost: parseFloat(dbQuote.totalCost.toString()), leadDays: dbQuote.leadDays, actualLeadDays: dbQuote.actualLeadDays || undefined, approved: dbQuote.approved, won: dbQuote.won || undefined, notes: dbQuote.notes || undefined, rawRfp: dbQuote.rawRfp || undefined }; } private mapQuoteFromDbRaw(row: any): HistoricalQuote { return { id: row.id, quoteDate: row.quote_date.toISOString(), customerName: row.customer_name, contactEmail: row.contact_email || undefined, normalized: { material: row.material || undefined, processes: row.processes || [], qtyRange: [row.qty_min || 0, row.qty_max || 0], tolerances: row.tolerances || undefined, finish: row.finish || undefined }, costPerUnit: parseFloat(row.cost_per_unit), totalCost: parseFloat(row.total_cost), leadDays: row.lead_days, actualLeadDays: row.actual_lead_days || undefined, approved: row.approved, won: row.won || undefined, notes: row.notes || undefined, rawRfp: row.raw_rfp || undefined }; } private mapQuoteToDb(quote: HistoricalQuote): any { return { id: quote.id, quoteDate: new Date(quote.quoteDate), customerName: quote.customerName, contactEmail: quote.contactEmail, material: quote.normalized.material, processes: quote.normalized.processes || [], qtyMin: quote.normalized.qtyRange?.[0], qtyMax: quote.normalized.qtyRange?.[1], tolerances: quote.normalized.tolerances, finish: quote.normalized.finish, costPerUnit: new Prisma.Decimal(quote.costPerUnit), totalCost: new Prisma.Decimal(quote.totalCost), leadDays: quote.leadDays, actualLeadDays: quote.actualLeadDays, approved: quote.approved, won: quote.won, notes: quote.notes, rawRfp: quote.rawRfp }; } private mapEvaluationFromDb(dbEval: any): RFPEvaluation { return { id: dbEval.id, idempotencyKey: dbEval.idempotencyKey, rawText: dbEval.rawText, qty: dbEval.qty || undefined, contactEmail: dbEval.contactEmail || undefined, customerName: dbEval.customerName || undefined, parsedRfp: dbEval.parsedRfp as any, matches: dbEval.matches as any, estimate: dbEval.estimate as any, quoteDoc: dbEval.quoteDoc as any, confidence: dbEval.confidence as any, status: dbEval.status, approvedBy: dbEval.approvedBy || undefined, approvedAt: dbEval.approvedAt?.toISOString(), sentAt: dbEval.sentAt?.toISOString(), createdAt: dbEval.createdAt.toISOString(), createdBy: dbEval.createdBy || undefined }; } private mapEvaluationToDb(evaluation: RFPEvaluation): any { return { id: evaluation.id, idempotencyKey: evaluation.idempotencyKey, rawText: evaluation.rawText, qty: evaluation.qty, contactEmail: evaluation.contactEmail, customerName: evaluation.customerName, parsedRfp: evaluation.parsedRfp, matches: evaluation.matches, estimate: evaluation.estimate, quoteDoc: evaluation.quoteDoc, confidence: evaluation.confidence, status: evaluation.status || 'draft', approvedBy: evaluation.approvedBy, approvedAt: evaluation.approvedAt ? new Date(evaluation.approvedAt) : undefined, sentAt: evaluation.sentAt ? new Date(evaluation.sentAt) : undefined, createdBy: evaluation.createdBy }; } }