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
|
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
);
"#,
)
}
|
