Skip to content

database-schema.md

Latest commit

 

History

History
146 lines (121 loc) · 4.53 KB

database-schema.md

File metadata and controls

146 lines (121 loc) · 4.53 KB

Database Schema Documentation

Overview

ServiqAI uses MongoDB as its primary database with Mongoose ODM for data modeling and validation. The current schema is minimal, focusing on storing chatbot configuration settings per user/owner.

Database Configuration

  • Database: MongoDB
  • ODM: Mongoose 9.1.5
  • Connection: Cached connection pattern for Next.js compatibility
  • Environment Variable: MONGODB_URL required

Collections

Settings Collection

Purpose: Stores chatbot configuration and business information for each owner/user.

Schema Definition:

interface ISettings {
    ownerId: string;        // Unique identifier from authentication provider
    businessName?: string;  // Optional business name
    supportEmail?: string;  // Optional support email address
    knowledge?: string;     // Optional knowledge base text
}

Mongoose Schema:

const settingsSchema = new Schema<ISettings>({
    ownerId: {
        type: String,
        required: true,
        unique: true
    },
    businessName: {
        type: String
    },
    supportEmail: {
        type: String
    },
    knowledge: {
        type: String
    }
}, {
    timestamps: true  // Adds createdAt and updatedAt fields
});

Indexes:

  • Unique index on ownerId field (automatically created by Mongoose)
  • Default _id index

Field Details:

ownerId (String, Required, Unique)

  • Description: Unique identifier for the chatbot owner, typically from ScaleKit authentication
  • Validation: Required, must be unique across all documents
  • Usage: Primary key for identifying user's chatbot configuration
  • Example: "user_123456789"

businessName (String, Optional)

  • Description: Name of the business or organization
  • Validation: Optional field, no specific constraints
  • Usage: Used in AI prompts to personalize responses
  • Example: "Acme Corporation"

supportEmail (String, Optional)

  • Description: Email address for customer support contact
  • Validation: Optional field, no specific format validation
  • Usage: Included in business knowledge for AI responses
  • Example: "support@acme.com"

knowledge (String, Optional)

  • Description: Free-form text containing business knowledge, FAQs, policies, etc.
  • Validation: Optional field, no length limits
  • Usage: Primary knowledge base fed to AI model for generating responses
  • Example:
• Refund policy: 7 days return available
• Delivery time: 3–5 working days
• Cash on Delivery available
• Support hours: 9 AM - 6 PM IST

Timestamps

  • createdAt: Automatically set when document is created
  • updatedAt: Automatically updated when document is modified
  • Type: Date

Data Relationships

  • One-to-One: Each ownerId maps to exactly one Settings document
  • Isolation: Data is completely isolated by ownerId - no cross-owner data access

CRUD Operations

Create/Update (Upsert)

  • API Endpoint: POST /api/settings
  • Operation: findOneAndUpdate with upsert: true
  • Behavior: Creates new document if ownerId doesn't exist, updates existing otherwise

Read

  • API Endpoint: POST /api/settings/get
  • Operation: findOne by ownerId
  • Returns: Complete settings object or null if not found

Usage in Application

AI Chat Generation

The settings data is used to construct a knowledge context for the Google Gemini AI model:

const KNOWLEDGE = `
business name- ${setting.businessName || "not provided"}
supportEmail- ${setting.supportEmail || "not provided"}
knowledge- ${setting.knowledge || "not provided"}
`;

Data Validation

  • Owner ID validation on all API endpoints
  • Required ownerId for all operations
  • Graceful handling of missing optional fields

Future Schema Extensions

Potential additions for enhanced functionality:

  • Chat history collection
  • Analytics/metrics collection
  • User preferences
  • Multiple chatbots per owner
  • Custom prompts/templates

Performance Considerations

  • Indexing: ownerId is indexed for fast lookups
  • Caching: Database connections are cached globally
  • Query Patterns: Simple point queries by ownerId
  • Data Size: Knowledge field can contain large text blocks

Backup and Recovery

  • Standard MongoDB backup procedures apply
  • Settings data is critical for chatbot functionality
  • Consider regular exports for disaster recovery

Migration Notes

  • Current schema is version 1.0
  • No migrations implemented yet
  • Schema changes would require careful planning due to production data