Overview
The KAIU Natural Living database uses PostgreSQL with Prisma ORM. The schema includes models for users, products, orders, AI knowledge base, and WhatsApp orchestration.Database Configuration
User Management
User Model
Central user model supporting customers, admins, warehouse staff, and support agents.id- UUID primary keyemail- Unique identifier for loginpassword- Bcrypt hashed passwordrole- User permission level (see Role enum)bsuid- WhatsApp Business-Scoped User ID for integration
Role Enum
Defines access levels throughout the system.CUSTOMER- Browse products, create orders, track shipmentsADMIN- Full dashboard access, manage all resourcesWAREHOUSE- Inventory management, order fulfillment, shippingSUPPORT- Handle customer inquiries, manage AI knowledge base
Product & Inventory
Product Model
Core product information with stock control and logistics data.sku- Unique product identifierslug- SEO-friendly URL pathprice- Stored in smallest currency unit (centavos)stockReserved- Prevents overselling during checkout process- Logistics fields (
weight,width,height,length) required for shipping quotes
Orders & Logistics
Order Model
Complete order lifecycle from creation to delivery.readableId- Human-friendly order number shown to customersexternalId- Links to external systems (payment gateway, logistics)- Addresses stored as JSON for immutability (prevents data loss if customer updates address)
customerId(national ID) required by most Colombian logistics providers
OrderStatus Enum
PaymentMethod Enum
OrderItem Model
Line items with price snapshots.Auxiliary Models
Address Model
Saved customer addresses for faster checkout.AI & Knowledge Base
KnowledgeBase Model
RAG (Retrieval-Augmented Generation) knowledge base with vector embeddings.- Stores product information, FAQs, and support content
embeddingfield uses PostgreSQLpgvectorextension (1536 dimensions for OpenAI embeddings)metadatatracks source and reference information- Powers AI chatbot responses with semantic search
WhatsApp Orchestrator
WhatsAppSession Model
Manages conversation state and AI handover.isBotActive- Controls AI auto-response (false when escalated to human)sessionContext- Stores conversation state, cart data, etc.handoverTrigger- Logs reason for AI → human handoverexpiresAt- Tracks WhatsApp messaging window limits
Database Setup
Initial Migration
Generate Prisma Client
Enable pgvector Extension
Run in your PostgreSQL database:Prisma Studio
Browse and edit data visually:http://localhost:5555
Next Steps
- Learn about Database Migrations
- Explore Database Seeding
- Review API Reference