Spaces:
Running on Zero
Running on Zero
File size: 6,356 Bytes
f7e32a5 59c28b9 f7e32a5 | 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 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 | """Database migrations — schema versioning."""
SCHEMA_VERSION = 1
MIGRATIONS = {
1: """
CREATE TABLE IF NOT EXISTS users (
id TEXT PRIMARY KEY,
username TEXT UNIQUE NOT NULL,
display_name TEXT,
email TEXT,
avatar_url TEXT,
preferences TEXT DEFAULT '{}',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS conversations (
id TEXT PRIMARY KEY,
user_id TEXT,
title TEXT DEFAULT 'New Chat',
folder_id TEXT,
personality_id TEXT DEFAULT 'synapse',
mode TEXT DEFAULT 'agent',
is_pinned INTEGER DEFAULT 0,
is_bookmarked INTEGER DEFAULT 0,
summary TEXT,
metadata TEXT DEFAULT '{}',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS messages (
id TEXT PRIMARY KEY,
conversation_id TEXT NOT NULL,
role TEXT NOT NULL CHECK(role IN ('user','assistant','system','tool')),
content TEXT NOT NULL,
reasoning TEXT,
tool_calls TEXT,
tool_results TEXT,
model TEXT,
tokens_used INTEGER DEFAULT 0,
metadata TEXT DEFAULT '{}',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS folders (
id TEXT PRIMARY KEY,
user_id TEXT,
name TEXT NOT NULL,
parent_id TEXT,
color TEXT DEFAULT '#6366f1',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS memories (
id TEXT PRIMARY KEY,
user_id TEXT,
conversation_id TEXT,
content TEXT NOT NULL,
memory_type TEXT DEFAULT 'long_term',
importance REAL DEFAULT 0.5,
embedding BLOB,
is_pinned INTEGER DEFAULT 0,
tags TEXT DEFAULT '[]',
metadata TEXT DEFAULT '{}',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
accessed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS user_facts (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL,
fact TEXT NOT NULL,
category TEXT DEFAULT 'general',
confidence REAL DEFAULT 1.0,
source TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS personalities (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
system_prompt TEXT NOT NULL,
description TEXT,
mood TEXT DEFAULT 'neutral',
humor_level REAL DEFAULT 0.5,
creativity_level REAL DEFAULT 0.7,
is_default INTEGER DEFAULT 0,
avatar_url TEXT,
metadata TEXT DEFAULT '{}',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS chat_history (
id TEXT PRIMARY KEY,
user_id TEXT,
conversation_id TEXT NOT NULL,
action TEXT NOT NULL,
old_value TEXT,
new_value TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS plugins (
id TEXT PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
description TEXT,
version TEXT DEFAULT '1.0.0',
author TEXT,
enabled INTEGER DEFAULT 1,
config TEXT DEFAULT '{}',
tools TEXT DEFAULT '[]',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS generated_files (
id TEXT PRIMARY KEY,
user_id TEXT,
file_type TEXT NOT NULL,
file_path TEXT NOT NULL,
original_prompt TEXT,
metadata TEXT DEFAULT '{}',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS sessions (
id TEXT PRIMARY KEY,
user_id TEXT,
token TEXT UNIQUE NOT NULL,
ip_address TEXT,
user_agent TEXT,
expires_at TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS settings (
user_id TEXT,
key TEXT NOT NULL,
value TEXT,
PRIMARY KEY (user_id, key),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS migrations (
version INTEGER PRIMARY KEY,
applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_messages_conversation ON messages(conversation_id);
CREATE INDEX IF NOT EXISTS idx_messages_role ON messages(role);
CREATE INDEX IF NOT EXISTS idx_conversations_user ON conversations(user_id);
CREATE INDEX IF NOT EXISTS idx_memories_user ON memories(user_id);
CREATE INDEX IF NOT EXISTS idx_memories_type ON memories(memory_type);
CREATE INDEX IF NOT EXISTS idx_generated_files_user ON generated_files(user_id);
CREATE INDEX IF NOT EXISTS idx_chat_history_conversation ON chat_history(conversation_id);
""",
}
async def run_migrations(db):
await db.execute("""
CREATE TABLE IF NOT EXISTS migrations (
version INTEGER PRIMARY KEY,
applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
""")
await db.commit()
cursor = await db.execute(
"SELECT version FROM migrations ORDER BY version DESC LIMIT 1"
)
row = await cursor.fetchone()
current_version = row[0] if row else 0
for version, sql in sorted(MIGRATIONS.items()):
if version > current_version:
await db.executescript(sql)
await db.execute(
"INSERT INTO migrations (version) VALUES (?)", (version,)
)
await db.commit()
|