Spaces:
Paused
Paused
File size: 5,750 Bytes
d94d354 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 |
-- OpenManus Database Schema for Cloudflare D1
-- Users table to store user information
CREATE TABLE IF NOT EXISTS users (
id TEXT PRIMARY KEY,
mobile_number TEXT UNIQUE NOT NULL,
full_name TEXT NOT NULL,
password_hash TEXT NOT NULL,
avatar_url TEXT,
preferences TEXT, -- JSON string for user preferences
is_active BOOLEAN DEFAULT TRUE,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- Sessions table to store user sessions
CREATE TABLE IF NOT EXISTS sessions (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL,
title TEXT,
metadata TEXT, -- JSON string for session metadata
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
expires_at DATETIME,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- Conversations table to store chat messages
CREATE TABLE IF NOT EXISTS conversations (
id TEXT PRIMARY KEY,
session_id TEXT NOT NULL,
role TEXT NOT NULL, -- 'user', 'assistant', 'system'
content TEXT NOT NULL,
metadata TEXT, -- JSON string for message metadata (files, tools used, etc.)
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);
-- Files table to store uploaded file information
CREATE TABLE IF NOT EXISTS files (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL,
session_id TEXT,
filename TEXT NOT NULL,
content_type TEXT,
size INTEGER,
r2_key TEXT NOT NULL, -- Key in R2 storage
bucket TEXT NOT NULL, -- Which R2 bucket
metadata TEXT, -- JSON string for file metadata
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE SET NULL
);
-- Agents table to store agent configurations
CREATE TABLE IF NOT EXISTS agents (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL,
name TEXT NOT NULL,
description TEXT,
system_prompt TEXT,
model TEXT,
tools TEXT, -- JSON array of enabled tools
config TEXT, -- JSON configuration object
is_active BOOLEAN DEFAULT TRUE,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- Agent sessions table for durable object session tracking
CREATE TABLE IF NOT EXISTS agent_sessions (
id TEXT PRIMARY KEY,
agent_id TEXT NOT NULL,
user_id TEXT NOT NULL,
session_id TEXT NOT NULL,
durable_object_id TEXT, -- ID of the corresponding durable object
status TEXT DEFAULT 'active', -- 'active', 'paused', 'completed', 'error'
metadata TEXT, -- JSON string for session state
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);
-- Usage tracking table for monitoring and analytics
CREATE TABLE IF NOT EXISTS usage_logs (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL,
session_id TEXT,
agent_id TEXT,
action TEXT NOT NULL, -- 'chat', 'upload', 'tool_use', etc.
resource_type TEXT, -- 'd1', 'r2', 'kv', 'durable_object'
resource_id TEXT,
tokens_used INTEGER DEFAULT 0,
duration_ms INTEGER DEFAULT 0,
cost_cents INTEGER DEFAULT 0,
metadata TEXT, -- JSON string for additional details
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE SET NULL,
FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE SET NULL
);
-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_users_mobile_number ON users(mobile_number);
CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_sessions_created_at ON sessions(created_at);
CREATE INDEX IF NOT EXISTS idx_conversations_session_id ON conversations(session_id);
CREATE INDEX IF NOT EXISTS idx_conversations_created_at ON conversations(created_at);
CREATE INDEX IF NOT EXISTS idx_files_user_id ON files(user_id);
CREATE INDEX IF NOT EXISTS idx_files_session_id ON files(session_id);
CREATE INDEX IF NOT EXISTS idx_files_created_at ON files(created_at);
CREATE INDEX IF NOT EXISTS idx_agents_user_id ON agents(user_id);
CREATE INDEX IF NOT EXISTS idx_agent_sessions_user_id ON agent_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_agent_sessions_session_id ON agent_sessions(session_id);
CREATE INDEX IF NOT EXISTS idx_usage_logs_user_id ON usage_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_usage_logs_created_at ON usage_logs(created_at);
-- Insert a default system user for system-level operations
INSERT OR IGNORE INTO users (id, mobile_number, full_name, password_hash)
VALUES ('system', '0000000000', 'OpenManus System', 'system_hash');
-- Insert a default agent configuration
INSERT OR IGNORE INTO agents (id, user_id, name, description, system_prompt, model, tools)
VALUES (
'default-agent',
'system',
'OpenManus Assistant',
'Default OpenManus AI assistant with full capabilities',
'You are OpenManus, an intelligent AI assistant with access to various tools and services. You help users with a wide range of tasks including file management, data analysis, web browsing, and more. Always be helpful, accurate, and concise in your responses.',
'gpt-4-turbo-preview',
'["file_operations", "web_search", "data_analysis", "browser_use", "python_execute"]'
);
|