chore: establish Strategy Lab source baseline (development, not release)

1 files changed, 146 insertions, 0 deletions

diff --git a/server/src/db.rs b/server/src/db.rs
new file mode 100644
index 0000000..d8b09dd
--- /dev/null
+++ b/server/src/db.rs
@@ -0,0 +1,146 @@
+use rusqlite::Connection;
+
+pub fn init_db(conn: &Connection) -> rusqlite::Result<()> {
+    conn.execute_batch(
+        r#"
+        PRAGMA journal_mode=WAL;
+        PRAGMA foreign_keys=ON;
+        CREATE TABLE IF NOT EXISTS users (
+            id TEXT PRIMARY KEY,
+            email TEXT NOT NULL UNIQUE,
+            password_hash TEXT NOT NULL,
+            name TEXT NOT NULL,
+            role TEXT NOT NULL CHECK(role IN ('admin','member')),
+            active INTEGER NOT NULL DEFAULT 1,
+            ai_enabled INTEGER NOT NULL DEFAULT 0,
+            daily_run_limit INTEGER NOT NULL DEFAULT 10,
+            created_at TEXT NOT NULL
+        );
+        CREATE TABLE IF NOT EXISTS sessions (
+            id TEXT PRIMARY KEY,           -- sha256(token)
+            user_id TEXT NOT NULL REFERENCES users(id),
+            created_at TEXT NOT NULL,
+            expires_at TEXT NOT NULL,
+            origin_note TEXT
+        );
+        CREATE TABLE IF NOT EXISTS login_failures (
+            email TEXT NOT NULL,
+            failed_at TEXT NOT NULL
+        );
+        CREATE TABLE IF NOT EXISTS invitations (
+            id TEXT PRIMARY KEY,
+            email TEXT,
+            token_hash TEXT NOT NULL UNIQUE,
+            role TEXT NOT NULL DEFAULT 'member',
+            expires_at TEXT NOT NULL,
+            used_by TEXT,
+            created_at TEXT NOT NULL
+        );
+        CREATE TABLE IF NOT EXISTS password_resets (
+            id TEXT PRIMARY KEY,
+            user_id TEXT NOT NULL REFERENCES users(id),
+            token_hash TEXT NOT NULL UNIQUE,
+            expires_at TEXT NOT NULL,
+            used INTEGER NOT NULL DEFAULT 0
+        );
+        CREATE TABLE IF NOT EXISTS audit (
+            seq INTEGER PRIMARY KEY AUTOINCREMENT,
+            ts TEXT NOT NULL,
+            actor_id TEXT,
+            action TEXT NOT NULL,
+            target TEXT,
+            status TEXT NOT NULL,
+            details TEXT
+        );
+        CREATE TABLE IF NOT EXISTS projects (
+            id TEXT PRIMARY KEY,
+            user_id TEXT NOT NULL REFERENCES users(id),
+            name TEXT NOT NULL,
+            description TEXT NOT NULL DEFAULT '',
+            draft_code TEXT NOT NULL DEFAULT '',
+            draft_generation INTEGER NOT NULL DEFAULT 0,
+            created_at TEXT NOT NULL,
+            updated_at TEXT NOT NULL
+        );
+        CREATE TABLE IF NOT EXISTS project_versions (
+            id TEXT PRIMARY KEY,
+            project_id TEXT NOT NULL REFERENCES projects(id),
+            code TEXT NOT NULL,
+            hash TEXT NOT NULL,
+            message TEXT NOT NULL,
+            source TEXT NOT NULL CHECK(source IN ('manual','run','ai','restore')),
+            created_at TEXT NOT NULL
+        );
+        CREATE TABLE IF NOT EXISTS datasets (
+            id TEXT PRIMARY KEY,
+            user_id TEXT NOT NULL REFERENCES users(id),
+            name TEXT NOT NULL,
+            request TEXT NOT NULL,
+            status TEXT NOT NULL CHECK(status IN ('pending','running','ready','failed')),
+            error TEXT,
+            manifest_hash TEXT,
+            manifest TEXT,
+            cache_hit INTEGER,
+            created_at TEXT NOT NULL,
+            updated_at TEXT NOT NULL
+        );
+        CREATE TABLE IF NOT EXISTS data_objects (
+            hash TEXT PRIMARY KEY,
+            path TEXT NOT NULL UNIQUE,
+            size INTEGER NOT NULL,
+            fetched_at TEXT NOT NULL
+        );
+        CREATE TABLE IF NOT EXISTS runs (
+            id TEXT PRIMARY KEY,
+            user_id TEXT NOT NULL REFERENCES users(id),
+            project_id TEXT NOT NULL REFERENCES projects(id),
+            version_id TEXT NOT NULL REFERENCES project_versions(id),
+            dataset_id TEXT NOT NULL REFERENCES datasets(id),
+            status TEXT NOT NULL CHECK(status IN ('queued','running','succeeded','failed','cancelled')),
+            config TEXT NOT NULL,
+            manifest_hash TEXT,
+            container_id TEXT,
+            result TEXT,
+            error TEXT,
+            created_at TEXT NOT NULL,
+            started_at TEXT,
+            finished_at TEXT
+        );
+        CREATE INDEX IF NOT EXISTS runs_project ON runs(project_id, created_at DESC);
+        CREATE INDEX IF NOT EXISTS runs_user_daily ON runs(user_id, created_at DESC);
+        CREATE TABLE IF NOT EXISTS ai_requests (
+            id TEXT PRIMARY KEY,
+            user_id TEXT NOT NULL REFERENCES users(id),
+            project_id TEXT NOT NULL REFERENCES projects(id),
+            instruction TEXT NOT NULL,
+            status TEXT NOT NULL CHECK(status IN ('pending','succeeded','failed')),
+            model TEXT,
+            explanation TEXT,
+            proposed_code TEXT,
+            diff TEXT,
+            base_generation INTEGER,
+            base_code_hash TEXT,
+            version_id TEXT,
+            usage TEXT,
+            error TEXT,
+            created_at TEXT NOT NULL
+        );
+        CREATE TABLE IF NOT EXISTS fetch_cache (
+            key TEXT PRIMARY KEY,
+            manifest_hash TEXT NOT NULL,
+            manifest TEXT NOT NULL,
+            object_count INTEGER NOT NULL,
+            fetched_at TEXT NOT NULL
+        );
+        CREATE TABLE IF NOT EXISTS ai_usage (
+            id TEXT PRIMARY KEY,
+            user_id TEXT NOT NULL,
+            request_id TEXT,
+            ts TEXT NOT NULL,
+            kind TEXT NOT NULL,
+            input_tokens INTEGER DEFAULT 0,
+            output_tokens INTEGER DEFAULT 0
+        );
+        "#,
+    )
+}