60255
Zn
Zoea-Nova / files
Zoea-Nova / internal / store / schema.sql
1.9 KB
1-- Zoea Nova database schema 2 3CREATE TABLE IF NOT EXISTS schema_version ( 4 version INTEGER PRIMARY KEY 5); 6 7-- Schema v11 → v12 Migration: 8-- Renamed in_use_by to assigned_to (permanent account assignment) 9-- Removed in_use flag (redundant - assigned_to IS NOT NULL means in use) 10-- BREAKING CHANGE: Requires fresh database (make db-reset-accounts) 11INSERT OR REPLACE INTO schema_version (version) VALUES (12); 12 13CREATE TABLE IF NOT EXISTS myses ( 14 id TEXT PRIMARY KEY, 15 name TEXT NOT NULL, 16 provider TEXT NOT NULL, 17 model TEXT NOT NULL, 18 temperature REAL NOT NULL DEFAULT 0.7, 19 state TEXT NOT NULL DEFAULT 'idle', 20 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 21 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP 22); 23 24CREATE TABLE IF NOT EXISTS memories ( 25 id INTEGER PRIMARY KEY AUTOINCREMENT, 26 mysis_id TEXT NOT NULL, 27 role TEXT NOT NULL, 28 source TEXT NOT NULL DEFAULT 'direct', 29 sender_id TEXT, 30 content TEXT NOT NULL, 31 reasoning TEXT, 32 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 33 FOREIGN KEY (mysis_id) REFERENCES myses(id) ON DELETE CASCADE 34); 35 36CREATE INDEX IF NOT EXISTS idx_memories_mysis_id ON memories(mysis_id); 37CREATE INDEX IF NOT EXISTS idx_memories_created_at ON memories(created_at); 38CREATE INDEX IF NOT EXISTS idx_memories_source ON memories(source); 39 40CREATE TABLE IF NOT EXISTS accounts ( 41 username TEXT PRIMARY KEY, 42 password TEXT NOT NULL, 43 assigned_to TEXT, 44 last_used_at DATETIME, 45 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 46 FOREIGN KEY (assigned_to) REFERENCES myses(id) ON DELETE SET NULL 47); 48 49CREATE INDEX IF NOT EXISTS idx_accounts_assigned_to ON accounts(assigned_to); 50 51CREATE TABLE IF NOT EXISTS game_state_snapshots ( 52 id TEXT PRIMARY KEY, 53 username TEXT NOT NULL, 54 tool_name TEXT NOT NULL, 55 content TEXT NOT NULL, 56 game_tick INTEGER, 57 captured_at INTEGER NOT NULL, 58 UNIQUE(username, tool_name) 59); 60 61CREATE INDEX IF NOT EXISTS idx_game_state_username ON game_state_snapshots(username);