ideas-generator/docs-archive/IMPLEMENTATION_GUIDE.md

# Ideas Generator 2025 - Implementation Guide
## Complete Step-by-Step Development Plan

### 📋 **Project Overview**
**Goal**: Migrate Ideas Generator from Make.com workflow (OpenAI Assistants API) to local Node.js backend (OpenAI Responses API)

**Current System**: 48 specialized AI assistants via Make.com webhook → OpenAI Assistants API
**Target System**: Local backend with PostgreSQL + OpenAI Responses API + Admin interface

**Timeline**: 5 weeks (25 working days)
**Architecture**: Node.js + Express + PostgreSQL + Redis + OpenAI Responses API

---

## 🏗 **Phase 1: Foundation Setup (Week 1 - Days 1-5)**

### **Day 1: Project Structure & Environment**

#### **1.1 Create Project Structure**
```
ideas-gen-2025/
├── server/                    # Backend application
│   ├── package.json
│   ├── .env.example
│   ├── .env
│   ├── index.js              # Main server file
│   ├── config/
│   │   ├── database.js       # PostgreSQL configuration
│   │   ├── redis.js          # Redis configuration
│   │   └── openai.js         # OpenAI client setup
│   ├── models/
│   │   ├── User.js
│   │   ├── Assistant.js
│   │   ├── Conversation.js
│   │   ├── Message.js
│   │   └── index.js          # Model exports
│   ├── routes/
│   │   ├── auth.js           # Authentication (dev bypass)
│   │   ├── assistants.js     # Assistant CRUD
│   │   ├── conversations.js  # Conversation management
│   │   ├── messages.js       # Message handling
│   │   ├── chat.js           # Main chat endpoint
│   │   └── admin.js          # Admin interface APIs
│   ├── middleware/
│   │   ├── auth.js           # Authentication middleware
│   │   ├── validation.js     # Request validation
│   │   ├── errorHandler.js   # Error handling
│   │   └── logging.js        # Request logging
│   ├── utils/
│   │   ├── assistantManager.js # Dynamic assistant loading
│   │   ├── systemPrompts.js   # TOV integration
│   │   ├── titleGenerator.js  # Auto-title generation
│   │   └── contentFilter.js   # Security filtering
│   └── migrations/
│       └── 001_initial_schema.sql
├── admin/                     # Admin interface (React)
│   ├── package.json
│   ├── src/
│   │   ├── components/
│   │   ├── pages/
│   │   └── services/
│   └── public/
├── frontend/                  # Existing frontend (minimal changes)
│   ├── index.html
│   ├── js/
│   └── css/
└── docs/                     # Implementation documentation
    ├── API.md
    ├── DATABASE.md
    └── DEPLOYMENT.md
```

#### **1.2 Initialize Backend**
```bash
cd server
npm init -y
npm install express cors dotenv
npm install @sequelize/core pg redis openai
npm install joi express-rate-limit helmet morgan
npm install --save-dev nodemon concurrently
```

#### **1.3 Create package.json Scripts**
```json
{
  "scripts": {
    "dev": "nodemon index.js",
    "start": "node index.js",
    "db:migrate": "node migrations/migrate.js",
    "db:seed": "node migrations/seed.js",
    "test": "jest"
  }
}
```

#### **1.4 Environment Configuration**
Create `.env.example` and `.env` files with:
```bash
# Database
DATABASE_URL=postgres://localhost:5432/ideas_gen_dev
DATABASE_HOST=localhost
DATABASE_NAME=ideas_gen_dev
DATABASE_USER=postgres
DATABASE_PASS=password

# Redis
REDIS_URL=redis://localhost:6379

# OpenAI
OPENAI_API_KEY=your_openai_api_key_here
OPENAI_ORG_ID=your_org_id_here

# Server
PORT=3000
NODE_ENV=development

# Development flags
SKIP_AUTH=true
ENABLE_CORS=true
LOG_LEVEL=debug
```

### **Day 2: Database Setup**

#### **2.1 Install & Configure PostgreSQL**
```bash
# macOS
brew install postgresql
brew services start postgresql
createdb ideas_gen_dev

# Ubuntu
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo -u postgres createdb ideas_gen_dev
```

#### **2.2 Database Schema (migrations/001_initial_schema.sql)**
```sql
-- Users table
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255),
    role VARCHAR(50) DEFAULT 'user',
    permissions JSONB DEFAULT '[]'::jsonb,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    last_login TIMESTAMPTZ,
    metadata JSONB DEFAULT '{}'::jsonb
);

-- Assistants table (dynamic configuration)
CREATE TABLE assistants (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    key VARCHAR(100) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    system_prompt TEXT NOT NULL,
    configuration JSONB NOT NULL DEFAULT '{
        "model": "gpt-4o",
        "temperature": 0.7,
        "max_tokens": 1000,
        "tools": []
    }'::jsonb,
    metadata JSONB DEFAULT '{
        "category": "",
        "technique_focus": "",
        "tags": [],
        "client_specific": null
    }'::jsonb,
    initial_message TEXT NOT NULL,
    
    -- Admin fields
    status VARCHAR(20) DEFAULT 'active',
    version INTEGER DEFAULT 1,
    created_by UUID REFERENCES users(id),
    updated_by UUID REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    deleted BOOLEAN DEFAULT false
);

-- Conversations table
CREATE TABLE conversations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id),
    assistant_key VARCHAR(100) NOT NULL,
    title VARCHAR(255),
    last_response_id VARCHAR(255),
    
    -- Configuration snapshots
    assistant_config JSONB NOT NULL,
    tov_config JSONB DEFAULT '{}'::jsonb,
    
    -- Analytics
    message_count INTEGER DEFAULT 0,
    total_tokens INTEGER DEFAULT 0,
    total_cost DECIMAL(10,6) DEFAULT 0,
    
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    deleted BOOLEAN DEFAULT false
);

-- Messages table
CREATE TABLE messages (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    conversation_id UUID NOT NULL REFERENCES conversations(id),
    role VARCHAR(20) NOT NULL CHECK (role IN ('user', 'assistant')),
    content TEXT NOT NULL,
    content_plain TEXT NOT NULL,
    
    -- OpenAI metadata
    response_metadata JSONB DEFAULT '{}'::jsonb,
    token_usage JSONB DEFAULT '{
        "prompt_tokens": 0,
        "completion_tokens": 0,
        "total_tokens": 0
    }'::jsonb,
    
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_assistants_key ON assistants(key);
CREATE INDEX idx_assistants_status ON assistants(status);
CREATE INDEX idx_conversations_user ON conversations(user_id);
CREATE INDEX idx_conversations_assistant ON conversations(assistant_key);
CREATE INDEX idx_messages_conversation ON messages(conversation_id);
CREATE INDEX idx_messages_role ON messages(role);
```

#### **2.3 Sequelize Models Setup**
Create models/index.js with database connection and model definitions.

### **Day 3: OpenAI Responses API Integration**

#### **3.1 OpenAI Client Configuration (config/openai.js)**
```javascript
const OpenAI = require('openai');

const openai = new OpenAI({
  apiKey: process.env.OPENAI_API_KEY,
  organization: process.env.OPENAI_ORG_ID,
});

// Content moderation wrapper
async function moderateContent(content) {
  try {
    const moderation = await openai.moderations.create({
      input: content
    });
    return moderation.results[0];
  } catch (error) {
    console.error('Content moderation failed:', error);
    return { flagged: false }; // Fail open for development
  }
}

// Title generation using Chat Completions
async function generateTitle(userMessage) {
  try {
    const completion = await openai.chat.completions.create({
      model: 'gpt-4o',
      messages: [
        {
          role: 'system',
          content: 'You are a conversation title generator. Create a concise title (2-4 words) that captures the essence of the conversation. No quotes, no prefixes like "Title:".'
        },
        {
          role: 'user',
          content: `Generate a short title for a conversation that starts with: "${userMessage}"`
        }
      ],
      temperature: 0.3,
      max_tokens: 10
    });
    
    return completion.choices[0].message.content.trim();
  } catch (error) {
    console.error('Title generation failed:', error);
    return 'New Conversation';
  }
}

module.exports = { openai, moderateContent, generateTitle };
```

#### **3.2 Assistant Manager (utils/assistantManager.js)**
```javascript
const { Assistant } = require('../models');
const NodeCache = require('node-cache');

class AssistantManager {
  constructor() {
    // Cache for 5 minutes
    this.cache = new NodeCache({ stdTTL: 300 });
  }
  
  async getAssistant(key) {
    // Check cache first
    const cached = this.cache.get(key);
    if (cached) return cached;
    
    // Load from database
    const assistant = await Assistant.findOne({
      where: { key, status: 'active', deleted: false }
    });
    
    if (!assistant) {
      throw new Error(`Assistant '${key}' not found or inactive`);
    }
    
    const config = {
      key: assistant.key,
      name: assistant.name,
      system_prompt: assistant.system_prompt,
      model: assistant.configuration.model,
      temperature: assistant.configuration.temperature,
      max_tokens: assistant.configuration.max_tokens,
      initial_message: assistant.initial_message,
      category: assistant.metadata.category
    };
    
    // Cache the result
    this.cache.set(key, config);
    return config;
  }
  
  invalidateCache(key) {
    this.cache.del(key);
  }
  
  async getAllActiveAssistants() {
    return await Assistant.findAll({
      where: { status: 'active', deleted: false },
      attributes: ['key', 'name', 'metadata', 'initial_message'],
      order: [['name', 'ASC']]
    });
  }
}

module.exports = new AssistantManager();
```

### **Day 4: Core API Endpoints**

#### **4.1 Main Chat Endpoint (routes/chat.js)**
```javascript
const express = require('express');
const router = express.Router();
const { v4: uuidv4 } = require('uuid');
const { openai, moderateContent, generateTitle } = require('../config/openai');
const assistantManager = require('../utils/assistantManager');
const { buildSystemPrompt } = require('../utils/systemPrompts');
const { Conversation, Message } = require('../models');

router.post('/', async (req, res) => {
  try {
    const { user_id = 'dev@local.dev' } = req.auth || {}; // Dev user
    const { ConversationID, AssistantKey, TOV_Key, Message: userMessage } = req.body;
    
    // Validate required fields
    if (!AssistantKey || !TOV_Key || !userMessage) {
      return res.status(400).json({ error: 'Missing required fields' });
    }
    
    // Content moderation
    const moderation = await moderateContent(userMessage);
    if (moderation.flagged) {
      return res.status(400).json({ error: 'Content flagged by moderation' });
    }
    
    // Get assistant configuration
    const assistantConfig = await assistantManager.getAssistant(AssistantKey);
    
    let conversation;
    let isNewConversation = !ConversationID;
    let previousResponseId = null;
    
    if (isNewConversation) {
      // Create new conversation
      conversation = await Conversation.create({
        id: uuidv4(),
        user_id,
        assistant_key: AssistantKey,
        assistant_config: assistantConfig,
        tov_config: { key: TOV_Key }
      });
    } else {
      // Get existing conversation
      conversation = await Conversation.findOne({
        where: { id: ConversationID, user_id }
      });
      
      if (!conversation) {
        return res.status(404).json({ error: 'Conversation not found' });
      }
      
      previousResponseId = conversation.last_response_id;
    }
    
    // Build system prompt with TOV
    const systemPrompt = buildSystemPrompt(assistantConfig, TOV_Key);
    
    // Call OpenAI Responses API
    const response = await openai.responses.create({
      model: assistantConfig.model,
      input: userMessage,
      system: systemPrompt,
      temperature: assistantConfig.temperature,
      max_tokens: assistantConfig.max_tokens,
      store: true, // Enable conversation memory
      previous_response_id: previousResponseId
    });
    
    // Store user message
    await Message.create({
      conversation_id: conversation.id,
      role: 'user',
      content: userMessage,
      content_plain: userMessage
    });
    
    // Extract assistant response
    const assistantMessage = response.choices[0].message.content;
    
    // Store assistant message
    await Message.create({
      conversation_id: conversation.id,
      role: 'assistant',
      content: assistantMessage,
      content_plain: assistantMessage,
      response_metadata: { response_id: response.id },
      token_usage: response.usage || {}
    });
    
    // Update conversation
    await conversation.update({
      last_response_id: response.id,
      message_count: conversation.message_count + 2, // user + assistant
      total_tokens: (conversation.total_tokens || 0) + (response.usage?.total_tokens || 0),
      updated_at: new Date()
    });
    
    // Generate title for new conversations
    if (isNewConversation) {
      const title = await generateTitle(userMessage);
      await conversation.update({ title });
      
      return res.json({
        conversation_id: conversation.id,
        conversation_title: title,
        message: assistantMessage
      });
    }
    
    res.json({
      conversation_id: conversation.id,
      message: assistantMessage
    });
    
  } catch (error) {
    console.error('Chat error:', error);
    res.status(500).json({ error: 'Internal server error' });
  }
});

module.exports = router;
```

### **Day 5: Frontend Integration**

#### **5.1 Update Frontend API calls (js/script.js modifications)**
```javascript
// Update variables for local development
const isDevelopment = true;
const make_url = isDevelopment ? 
  "http://localhost:3000/api" : 
  "https://hook.us1.make.celonis.com/htn0fepeoai19d1unx6fqm5qd5ptk5px";

// Update gcp_fetch for local backend
async function gcp_fetch(url, options = {}) {
    console.log("Fetching:", url);
    
    const defaultOptions = {
        method: "GET",
        headers: {
            "Content-type": "application/json",
        }
    };
    
    return await fetch(url, { ...defaultOptions, ...options });
}

// Update sendMessage function for Responses API
const sendMessage = async () => {
    if (!assistant_key) {
        alert("Please Select an Assistant");
        return false;
    }
    
    if (!tov_key) {
        alert("Please Select a Tone of Voice");
        return false;
    }
    
    if (sending_message) return false;
    sending_message = true;
    
    const message = maskUKBankDetails(document.getElementById("message-input")?.value);
    document.getElementById("message-input").value = "";
    
    // Add user message to UI
    Array.from(document.getElementsByClassName("chat-message-appear"))?.forEach(el => 
        el.classList.remove("chat-message-appear")
    );
    document.getElementById("chat").innerHTML += 
        chat_message_user_new.replaceAll("{CONTENT}", md_converter?.makeHtml(message));
    
    // Scroll to bottom
    document.getElementById("chat").scrollTop = document.getElementById("chat").scrollHeight;
    
    // Show loading
    setTimeout(() => {
        document.getElementById("chat").innerHTML += chat_message_assistant_loading_dots;
        document.getElementById("chat").scrollTop = document.getElementById("chat").scrollHeight;
    }, 1000);
    
    try {
        const response = await fetch(make_url + '/chat', {
            method: 'POST',
            headers: {
                'Content-Type': 'application/json'
            },
            body: JSON.stringify({
                ConversationID: conversation_id || undefined,
                AssistantKey: assistant_key,
                TOV_Key: tov_key,
                Message: message
            })
        });
        
        const data = await response.json();
        
        if (response.ok) {
            // Update conversation ID
            if (data.conversation_id) {
                conversation_id = data.conversation_id;
            }
            
            // Update conversation title for new conversations
            if (data.conversation_title) {
                // Add to conversations list or update title
                if (conversations.findIndex(c => c.id === data.conversation_id) === -1) {
                    conversations = [{
                        id: data.conversation_id,
                        title: data.conversation_title,
                        assistant_key: assistant_key,
                        tov_key: tov_key
                    }].concat(conversations);
                    
                    // Update conversations list in UI
                    updateConversationsList();
                }
            }
            
            // Remove loading and add response
            Array.from(document.getElementsByClassName("chat-message-loading-dots"))?.forEach(el => el.remove());
            document.getElementById("chat").innerHTML += 
                chat_message_assistant_new.replaceAll("{CONTENT}", data.message);
            
        } else {
            console.error('API Error:', data.error);
            alert(data.error || 'An error occurred');
        }
        
    } catch (error) {
        console.error('Network error:', error);
        alert('Network error occurred');
    } finally {
        sending_message = false;
        document.getElementById("chat").scrollTop = document.getElementById("chat").scrollHeight;
    }
};
```

---

## 🎯 **Implementation Checkpoints**

### **Week 1 Success Criteria:**
- [ ] Project structure created and dependencies installed
- [ ] PostgreSQL database running with schema
- [ ] OpenAI Responses API integration working
- [ ] Basic chat endpoint functional
- [ ] Frontend making API calls to local backend
- [ ] Can create new conversations and continue existing ones
- [ ] Assistant configurations loading dynamically

### **Next Steps for Week 2:**
- Import all 48 assistant configurations from CSV
- Create admin interface for assistant management
- Implement remaining API endpoints (conversations, messages, assistants)
- Add comprehensive error handling and logging

---

## 📝 **Quick Reference Commands**

### **Development Startup:**
```bash
# Start PostgreSQL
brew services start postgresql  # macOS
sudo systemctl start postgresql # Ubuntu

# Start Redis (optional for Week 1)
redis-server

# Start backend
cd server
npm run dev

# Start frontend (separate terminal)
cd frontend
python -m http.server 8080  # or use Live Server
```

### **Database Commands:**
```bash
# Connect to database
psql ideas_gen_dev

# Run migration
npm run db:migrate

# Check tables
\dt
```

### **Git Commands:**
```bash
# Commit progress
git add .
git commit -m "Phase 1 Day X: [Description]"
git push origin ideas-gen-2025
```

This implementation guide provides everything needed to start Week 1 development. Each day builds incrementally toward a functional local backend system.