Press n or j to go to the next uncovered block, b, p or k for the previous block.
| 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 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 | 1x 1x 60x 60x 60x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x | /**
* SQLite schema for the libSQL backend.
*
* This is a translation of `supabase/migrations/`, not a replay of it. Those
* four migrations describe how the Postgres schema arrived at its current
* shape; a new backend only needs the shape itself, so this is one consolidated
* migration. Future changes append a new entry rather than editing this one.
*
* Statements are listed individually rather than as one blob because the
* migration runner submits them through `client.batch(..., 'write')`, which is
* transactional. Splitting a blob on `;` would break the trigger bodies.
*
* Type mapping from Postgres:
*
* | Postgres | SQLite | Handled by |
* | ------------------------- | ----------------- | ----------------------------- |
* | `UUID` + `uuid_generate_v4` | `TEXT` | ids generated in TS |
* | `JSONB` | `TEXT` | `toJsonColumn` / `fromJson` |
* | `TIMESTAMPTZ` | `TEXT` (ISO-8601) | `nowIso` and the triggers |
* | `BIGINT` | `INTEGER` | SQLite INTEGER is 64-bit |
* | `FLOAT` | `REAL` | direct |
* | `BOOLEAN` | `INTEGER` (0/1) | `toBoolColumn` / `fromBool` |
* | `TEXT[]` / `FLOAT[]` | `TEXT` (JSON) | `toJsonColumn` / `fromJson` |
* | `ENUM` | `TEXT` + `CHECK` | the CHECK constraints below |
*
* Row-level security has no SQLite equivalent and is intentionally dropped: the
* Postgres policies all grant unrestricted access to the service role, which is
* the only role the API ever connects as.
*/
export interface LibsqlMigration {
/** Sort key and primary key in `schema_migrations`. */
version: string;
statements: string[];
}
/** Written by the updated_at triggers; matches `Date.prototype.toISOString`. */
const NOW_ISO = "strftime('%Y-%m-%dT%H:%M:%fZ', 'now')";
/** Postgres sets `updated_at` on every UPDATE via `update_updated_at_column`. */
const updatedAtTrigger = (table: string): string => `
CREATE TRIGGER IF NOT EXISTS ${table}_updated_at
AFTER UPDATE ON ${table}
FOR EACH ROW
BEGIN
UPDATE ${table} SET updated_at = ${NOW_ISO} WHERE rowid = NEW.rowid;
END`;
const initialSchema: LibsqlMigration = {
version: '0001_initial_schema',
statements: [
// ---------------------------------------------------------------- agents
`CREATE TABLE IF NOT EXISTS agents (
id TEXT PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
description TEXT NOT NULL,
metadata TEXT NOT NULL DEFAULT '{}',
created_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
updated_at TEXT NOT NULL DEFAULT (${NOW_ISO})
)`,
updatedAtTrigger('agents'),
// --------------------------------------------------------- ai_providers
`CREATE TABLE IF NOT EXISTS ai_providers (
id TEXT PRIMARY KEY,
ai_provider TEXT NOT NULL,
name TEXT NOT NULL,
api_key TEXT,
custom_fields TEXT NOT NULL DEFAULT '{}',
created_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
updated_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
UNIQUE (ai_provider, name)
)`,
`CREATE INDEX IF NOT EXISTS idx_ai_providers_ai_provider ON ai_providers(ai_provider)`,
`CREATE INDEX IF NOT EXISTS idx_ai_providers_name ON ai_providers(name)`,
updatedAtTrigger('ai_providers'),
// --------------------------------------------------------------- models
`CREATE TABLE IF NOT EXISTS models (
id TEXT PRIMARY KEY,
ai_provider_id TEXT NOT NULL,
model_name TEXT NOT NULL,
model_type TEXT NOT NULL DEFAULT 'text' CHECK (model_type IN ('text', 'embed')),
embedding_dimensions INTEGER,
created_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
updated_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
FOREIGN KEY (ai_provider_id) REFERENCES ai_providers(id) ON DELETE CASCADE,
UNIQUE (ai_provider_id, model_name),
CONSTRAINT embedding_dimensions_only_for_embed CHECK (
(model_type = 'embed' AND embedding_dimensions IS NOT NULL) OR
(model_type = 'text' AND embedding_dimensions IS NULL)
)
)`,
`CREATE INDEX IF NOT EXISTS idx_models_ai_provider_id ON models(ai_provider_id)`,
`CREATE INDEX IF NOT EXISTS idx_models_model_name ON models(model_name)`,
updatedAtTrigger('models'),
// --------------------------------------------------------------- skills
`CREATE TABLE IF NOT EXISTS skills (
id TEXT PRIMARY KEY,
agent_id TEXT NOT NULL,
name TEXT NOT NULL,
description TEXT NOT NULL,
metadata TEXT NOT NULL DEFAULT '{}',
optimize INTEGER NOT NULL DEFAULT 1 CHECK (optimize IN (0, 1)),
configuration_count INTEGER NOT NULL DEFAULT 3,
clustering_interval INTEGER NOT NULL DEFAULT 15,
reflection_min_requests_per_arm INTEGER NOT NULL DEFAULT 2,
exploration_temperature REAL NOT NULL DEFAULT 1.0,
total_requests INTEGER NOT NULL DEFAULT 0,
allowed_template_variables TEXT NOT NULL DEFAULT '[]',
last_clustering_at TEXT,
last_clustering_log_start_time INTEGER,
evaluations_regenerated_at TEXT,
evaluation_lock_acquired_at TEXT,
created_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
updated_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE,
UNIQUE (agent_id, name)
)`,
`CREATE INDEX IF NOT EXISTS idx_skills_agent_id ON skills(agent_id)`,
`CREATE INDEX IF NOT EXISTS idx_skills_name ON skills(name)`,
`CREATE INDEX IF NOT EXISTS idx_skills_evaluation_lock ON skills(evaluation_lock_acquired_at)
WHERE evaluation_lock_acquired_at IS NOT NULL`,
`CREATE INDEX IF NOT EXISTS idx_skills_last_clustering ON skills(last_clustering_at)
WHERE last_clustering_at IS NOT NULL`,
updatedAtTrigger('skills'),
// ------------------------------------------- skill_optimization_clusters
`CREATE TABLE IF NOT EXISTS skill_optimization_clusters (
id TEXT PRIMARY KEY,
agent_id TEXT NOT NULL,
skill_id TEXT NOT NULL,
name TEXT NOT NULL,
total_steps INTEGER NOT NULL DEFAULT 0,
observability_total_requests INTEGER NOT NULL DEFAULT 0,
centroid TEXT NOT NULL,
embedding_model_id TEXT,
reflection_lock_acquired_at TEXT,
created_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
updated_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE,
FOREIGN KEY (skill_id) REFERENCES skills(id) ON DELETE CASCADE,
FOREIGN KEY (embedding_model_id) REFERENCES models(id) ON DELETE CASCADE
)`,
`CREATE INDEX IF NOT EXISTS idx_skill_optimization_clusters_name ON skill_optimization_clusters(name)`,
`CREATE INDEX IF NOT EXISTS idx_skill_optimization_clusters_agent_id ON skill_optimization_clusters(agent_id)`,
`CREATE INDEX IF NOT EXISTS idx_skill_optimization_clusters_skill_id ON skill_optimization_clusters(skill_id)`,
`CREATE INDEX IF NOT EXISTS idx_skill_optimization_clusters_embedding_model_id ON skill_optimization_clusters(embedding_model_id)`,
`CREATE INDEX IF NOT EXISTS idx_clusters_reflection_lock ON skill_optimization_clusters(reflection_lock_acquired_at)
WHERE reflection_lock_acquired_at IS NOT NULL`,
updatedAtTrigger('skill_optimization_clusters'),
// ---------------------------------------------------------------- tools
`CREATE TABLE IF NOT EXISTS tools (
id TEXT PRIMARY KEY,
agent_id TEXT NOT NULL,
hash TEXT NOT NULL,
type TEXT NOT NULL,
name TEXT NOT NULL,
raw_data TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
updated_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE,
UNIQUE (agent_id, hash)
)`,
`CREATE INDEX IF NOT EXISTS idx_tools_agent_id ON tools(agent_id)`,
`CREATE INDEX IF NOT EXISTS idx_tools_hash ON tools(hash)`,
`CREATE INDEX IF NOT EXISTS idx_tools_type ON tools(type)`,
`CREATE INDEX IF NOT EXISTS idx_tools_name ON tools(name)`,
updatedAtTrigger('tools'),
// ----------------------------------------------------------------- logs
`CREATE TABLE IF NOT EXISTS logs (
id TEXT PRIMARY KEY,
agent_id TEXT NOT NULL,
skill_id TEXT NOT NULL,
cluster_id TEXT,
method TEXT NOT NULL CHECK (method IN ('GET', 'POST', 'PUT', 'DELETE', 'PATCH')),
endpoint TEXT NOT NULL,
function_name TEXT NOT NULL,
status INTEGER NOT NULL,
start_time INTEGER NOT NULL,
first_token_time INTEGER,
end_time INTEGER NOT NULL,
duration INTEGER NOT NULL,
base_sa_config TEXT NOT NULL,
ai_provider TEXT NOT NULL,
model TEXT NOT NULL,
ai_provider_request_log TEXT NOT NULL,
hook_logs TEXT NOT NULL,
metadata TEXT NOT NULL,
embedding TEXT DEFAULT NULL,
cache_status TEXT NOT NULL CHECK (
cache_status IN ('HIT', 'SEMANTIC_HIT', 'MISS', 'SEMANTIC_MISS', 'REFRESH', 'DISABLED')
),
trace_id TEXT,
parent_span_id TEXT,
span_id TEXT,
span_name TEXT,
app_id TEXT,
external_user_id TEXT,
external_user_human_name TEXT,
user_metadata TEXT,
FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE,
FOREIGN KEY (skill_id) REFERENCES skills(id) ON DELETE CASCADE,
FOREIGN KEY (cluster_id) REFERENCES skill_optimization_clusters(id) ON DELETE SET NULL
)`,
`CREATE INDEX IF NOT EXISTS idx_logs_agent_id ON logs(agent_id)`,
`CREATE INDEX IF NOT EXISTS idx_logs_skill_id ON logs(skill_id)`,
`CREATE INDEX IF NOT EXISTS idx_logs_start_time ON logs(start_time)`,
`CREATE INDEX IF NOT EXISTS idx_logs_end_time ON logs(end_time)`,
`CREATE INDEX IF NOT EXISTS idx_logs_app_id ON logs(app_id)`,
`CREATE INDEX IF NOT EXISTS idx_logs_span_id ON logs(span_id)`,
`CREATE INDEX IF NOT EXISTS idx_logs_parent_span_id ON logs(parent_span_id)`,
`CREATE INDEX IF NOT EXISTS idx_logs_status ON logs(status)`,
`CREATE INDEX IF NOT EXISTS idx_logs_method ON logs(method)`,
`CREATE INDEX IF NOT EXISTS idx_logs_cache_status ON logs(cache_status)`,
// ------------------------------------------------------------ feedbacks
`CREATE TABLE IF NOT EXISTS feedbacks (
id TEXT PRIMARY KEY,
log_id TEXT NOT NULL,
score REAL CHECK (score >= 0 AND score <= 1),
feedback TEXT,
created_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
FOREIGN KEY (log_id) REFERENCES logs(id) ON DELETE CASCADE
)`,
`CREATE INDEX IF NOT EXISTS idx_feedbacks_log_id ON feedbacks(log_id)`,
// ---------------------------------------------------- improved_responses
`CREATE TABLE IF NOT EXISTS improved_responses (
id TEXT PRIMARY KEY,
agent_id TEXT NOT NULL,
skill_id TEXT NOT NULL,
log_id TEXT NOT NULL,
original_response_body TEXT NOT NULL,
improved_response_body TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
updated_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE,
FOREIGN KEY (skill_id) REFERENCES skills(id) ON DELETE CASCADE,
FOREIGN KEY (log_id) REFERENCES logs(id) ON DELETE CASCADE
)`,
`CREATE INDEX IF NOT EXISTS idx_improved_responses_agent_id ON improved_responses(agent_id)`,
`CREATE INDEX IF NOT EXISTS idx_improved_responses_skill_id ON improved_responses(skill_id)`,
`CREATE INDEX IF NOT EXISTS idx_improved_responses_log_id ON improved_responses(log_id)`,
updatedAtTrigger('improved_responses'),
// ---------------------------------------------------------------- cache
`CREATE TABLE IF NOT EXISTS cache (
key TEXT PRIMARY KEY,
value TEXT NOT NULL,
expires_at TEXT NOT NULL
)`,
`CREATE INDEX IF NOT EXISTS idx_cache_expires_at ON cache(expires_at)`,
// --------------------------------------------------------- skill_models
`CREATE TABLE IF NOT EXISTS skill_models (
skill_id TEXT NOT NULL,
model_id TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
PRIMARY KEY (skill_id, model_id),
FOREIGN KEY (skill_id) REFERENCES skills(id) ON DELETE CASCADE,
FOREIGN KEY (model_id) REFERENCES models(id) ON DELETE CASCADE
)`,
`CREATE INDEX IF NOT EXISTS idx_skill_models_skill_id ON skill_models(skill_id)`,
`CREATE INDEX IF NOT EXISTS idx_skill_models_model_id ON skill_models(model_id)`,
// ----------------------------------------------- skill_optimization_arms
`CREATE TABLE IF NOT EXISTS skill_optimization_arms (
id TEXT PRIMARY KEY,
agent_id TEXT NOT NULL,
skill_id TEXT NOT NULL,
cluster_id TEXT NOT NULL,
name TEXT NOT NULL,
params TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
updated_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE,
FOREIGN KEY (skill_id) REFERENCES skills(id) ON DELETE CASCADE,
FOREIGN KEY (cluster_id) REFERENCES skill_optimization_clusters(id) ON DELETE CASCADE
)`,
`CREATE INDEX IF NOT EXISTS idx_skill_optimization_arms_name ON skill_optimization_arms(name)`,
`CREATE INDEX IF NOT EXISTS idx_skill_optimization_arms_agent_id ON skill_optimization_arms(agent_id)`,
`CREATE INDEX IF NOT EXISTS idx_skill_optimization_arms_skill_id ON skill_optimization_arms(skill_id)`,
`CREATE INDEX IF NOT EXISTS idx_skill_optimization_arms_cluster_id ON skill_optimization_arms(cluster_id)`,
updatedAtTrigger('skill_optimization_arms'),
// ---------------------------------------- skill_optimization_evaluations
`CREATE TABLE IF NOT EXISTS skill_optimization_evaluations (
id TEXT PRIMARY KEY,
agent_id TEXT NOT NULL,
skill_id TEXT NOT NULL,
evaluation_method TEXT NOT NULL,
params TEXT NOT NULL DEFAULT '{}',
weight REAL NOT NULL DEFAULT 1.0,
model_id TEXT,
created_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
updated_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE,
FOREIGN KEY (skill_id) REFERENCES skills(id) ON DELETE CASCADE,
FOREIGN KEY (model_id) REFERENCES models(id) ON DELETE CASCADE,
UNIQUE (agent_id, skill_id, evaluation_method),
CHECK (weight > 0)
)`,
`CREATE INDEX IF NOT EXISTS idx_skill_optimization_evaluations_agent_id ON skill_optimization_evaluations(agent_id)`,
`CREATE INDEX IF NOT EXISTS idx_skill_optimization_evaluations_evaluation_method ON skill_optimization_evaluations(evaluation_method)`,
`CREATE INDEX IF NOT EXISTS idx_skill_optimization_evaluations_model_id ON skill_optimization_evaluations(model_id)`,
updatedAtTrigger('skill_optimization_evaluations'),
// ----------------------------------------- skill_optimization_arm_stats
`CREATE TABLE IF NOT EXISTS skill_optimization_arm_stats (
arm_id TEXT NOT NULL,
evaluation_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
skill_id TEXT NOT NULL,
cluster_id TEXT NOT NULL,
n INTEGER NOT NULL DEFAULT 0,
mean REAL NOT NULL DEFAULT 0,
n2 REAL NOT NULL DEFAULT 0,
total_reward REAL NOT NULL DEFAULT 0,
created_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
updated_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
PRIMARY KEY (arm_id, evaluation_id),
FOREIGN KEY (arm_id) REFERENCES skill_optimization_arms(id) ON DELETE CASCADE,
FOREIGN KEY (evaluation_id) REFERENCES skill_optimization_evaluations(id) ON DELETE CASCADE,
FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE,
FOREIGN KEY (skill_id) REFERENCES skills(id) ON DELETE CASCADE,
FOREIGN KEY (cluster_id) REFERENCES skill_optimization_clusters(id) ON DELETE CASCADE
)`,
`CREATE INDEX IF NOT EXISTS idx_skill_optimization_arm_stats_arm_id ON skill_optimization_arm_stats(arm_id)`,
`CREATE INDEX IF NOT EXISTS idx_skill_optimization_arm_stats_evaluation_id ON skill_optimization_arm_stats(evaluation_id)`,
`CREATE INDEX IF NOT EXISTS idx_skill_optimization_arm_stats_skill_id ON skill_optimization_arm_stats(skill_id)`,
`CREATE INDEX IF NOT EXISTS idx_skill_optimization_arm_stats_cluster_id ON skill_optimization_arm_stats(cluster_id)`,
updatedAtTrigger('skill_optimization_arm_stats'),
// ------------------------------- skill_optimization_evaluation_runs
`CREATE TABLE IF NOT EXISTS skill_optimization_evaluation_runs (
id TEXT PRIMARY KEY,
agent_id TEXT NOT NULL,
skill_id TEXT NOT NULL,
cluster_id TEXT,
log_id TEXT NOT NULL,
results TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE,
FOREIGN KEY (skill_id) REFERENCES skills(id) ON DELETE CASCADE,
FOREIGN KEY (cluster_id) REFERENCES skill_optimization_clusters(id) ON DELETE SET NULL,
FOREIGN KEY (log_id) REFERENCES logs(id) ON DELETE CASCADE
)`,
`CREATE INDEX IF NOT EXISTS idx_skill_optimization_evaluation_runs_agent_id ON skill_optimization_evaluation_runs(agent_id)`,
`CREATE INDEX IF NOT EXISTS idx_skill_optimization_evaluation_runs_skill_id ON skill_optimization_evaluation_runs(skill_id)`,
`CREATE INDEX IF NOT EXISTS idx_skill_optimization_evaluation_runs_log_id ON skill_optimization_evaluation_runs(log_id)`,
`CREATE INDEX IF NOT EXISTS idx_skill_optimization_evaluation_runs_created_at ON skill_optimization_evaluation_runs(created_at)`,
// --------------------------------------------------------- skill_events
`CREATE TABLE IF NOT EXISTS skill_events (
id TEXT PRIMARY KEY,
agent_id TEXT NOT NULL,
skill_id TEXT NOT NULL,
cluster_id TEXT,
event_type TEXT NOT NULL,
metadata TEXT DEFAULT '{}',
created_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE,
FOREIGN KEY (skill_id) REFERENCES skills(id) ON DELETE CASCADE,
FOREIGN KEY (cluster_id) REFERENCES skill_optimization_clusters(id) ON DELETE CASCADE
)`,
`CREATE INDEX IF NOT EXISTS idx_skill_events_skill_id ON skill_events(skill_id)`,
`CREATE INDEX IF NOT EXISTS idx_skill_events_cluster_id ON skill_events(cluster_id) WHERE cluster_id IS NOT NULL`,
`CREATE INDEX IF NOT EXISTS idx_skill_events_type ON skill_events(event_type)`,
`CREATE INDEX IF NOT EXISTS idx_skill_events_created_at ON skill_events(created_at DESC)`,
`CREATE INDEX IF NOT EXISTS idx_skill_events_skill_type_time ON skill_events(skill_id, event_type, created_at DESC)`,
`CREATE INDEX IF NOT EXISTS idx_skill_events_cluster_time ON skill_events(cluster_id, created_at DESC) WHERE cluster_id IS NOT NULL`,
// ------------------------------------------------------ system_settings
// Postgres enforces the singleton with a unique index on the constant
// expression `(true)`. SQLite cannot index a constant, so a pinned column
// carries the same guarantee.
`CREATE TABLE IF NOT EXISTS system_settings (
id TEXT PRIMARY KEY,
singleton INTEGER NOT NULL DEFAULT 1 UNIQUE CHECK (singleton = 1),
system_prompt_reflection_model_id TEXT REFERENCES models(id) ON DELETE RESTRICT,
evaluation_generation_model_id TEXT REFERENCES models(id) ON DELETE RESTRICT,
embedding_model_id TEXT REFERENCES models(id) ON DELETE RESTRICT,
judge_model_id TEXT REFERENCES models(id) ON DELETE RESTRICT,
developer_mode INTEGER NOT NULL DEFAULT 0 CHECK (developer_mode IN (0, 1)),
created_at TEXT NOT NULL DEFAULT (${NOW_ISO}),
updated_at TEXT NOT NULL DEFAULT (${NOW_ISO})
)`,
updatedAtTrigger('system_settings'),
// ------------------------------------------------- model type integrity
// Postgres enforces these through `validate_system_settings_model_types`,
// `validate_evaluation_model_type` and
// `prevent_model_type_change_if_referenced`. Without them the libSQL
// backend would accept configurations the Postgres one rejects.
`CREATE TRIGGER IF NOT EXISTS system_settings_validate_model_types_insert
BEFORE INSERT ON system_settings
FOR EACH ROW
BEGIN
SELECT CASE WHEN NEW.system_prompt_reflection_model_id IS NOT NULL
AND (SELECT model_type FROM models WHERE id = NEW.system_prompt_reflection_model_id) IS NOT 'text'
THEN RAISE(ABORT, 'system_prompt_reflection_model_id must reference a text model') END;
SELECT CASE WHEN NEW.evaluation_generation_model_id IS NOT NULL
AND (SELECT model_type FROM models WHERE id = NEW.evaluation_generation_model_id) IS NOT 'text'
THEN RAISE(ABORT, 'evaluation_generation_model_id must reference a text model') END;
SELECT CASE WHEN NEW.judge_model_id IS NOT NULL
AND (SELECT model_type FROM models WHERE id = NEW.judge_model_id) IS NOT 'text'
THEN RAISE(ABORT, 'judge_model_id must reference a text model') END;
SELECT CASE WHEN NEW.embedding_model_id IS NOT NULL
AND (SELECT model_type FROM models WHERE id = NEW.embedding_model_id) IS NOT 'embed'
THEN RAISE(ABORT, 'embedding_model_id must reference an embed model') END;
END`,
`CREATE TRIGGER IF NOT EXISTS system_settings_validate_model_types_update
BEFORE UPDATE ON system_settings
FOR EACH ROW
BEGIN
SELECT CASE WHEN NEW.system_prompt_reflection_model_id IS NOT NULL
AND (SELECT model_type FROM models WHERE id = NEW.system_prompt_reflection_model_id) IS NOT 'text'
THEN RAISE(ABORT, 'system_prompt_reflection_model_id must reference a text model') END;
SELECT CASE WHEN NEW.evaluation_generation_model_id IS NOT NULL
AND (SELECT model_type FROM models WHERE id = NEW.evaluation_generation_model_id) IS NOT 'text'
THEN RAISE(ABORT, 'evaluation_generation_model_id must reference a text model') END;
SELECT CASE WHEN NEW.judge_model_id IS NOT NULL
AND (SELECT model_type FROM models WHERE id = NEW.judge_model_id) IS NOT 'text'
THEN RAISE(ABORT, 'judge_model_id must reference a text model') END;
SELECT CASE WHEN NEW.embedding_model_id IS NOT NULL
AND (SELECT model_type FROM models WHERE id = NEW.embedding_model_id) IS NOT 'embed'
THEN RAISE(ABORT, 'embedding_model_id must reference an embed model') END;
END`,
`CREATE TRIGGER IF NOT EXISTS evaluations_validate_model_type_insert
BEFORE INSERT ON skill_optimization_evaluations
FOR EACH ROW
WHEN NEW.model_id IS NOT NULL
BEGIN
SELECT CASE WHEN (SELECT model_type FROM models WHERE id = NEW.model_id) IS NOT 'text'
THEN RAISE(ABORT, 'evaluation model_id must reference a text model') END;
END`,
`CREATE TRIGGER IF NOT EXISTS evaluations_validate_model_type_update
BEFORE UPDATE ON skill_optimization_evaluations
FOR EACH ROW
WHEN NEW.model_id IS NOT NULL
BEGIN
SELECT CASE WHEN (SELECT model_type FROM models WHERE id = NEW.model_id) IS NOT 'text'
THEN RAISE(ABORT, 'evaluation model_id must reference a text model') END;
END`,
`CREATE TRIGGER IF NOT EXISTS models_prevent_type_change_if_referenced
BEFORE UPDATE OF model_type ON models
FOR EACH ROW
WHEN NEW.model_type IS NOT OLD.model_type
BEGIN
SELECT CASE WHEN EXISTS (
SELECT 1 FROM system_settings
WHERE system_prompt_reflection_model_id = NEW.id
OR evaluation_generation_model_id = NEW.id
OR judge_model_id = NEW.id
OR embedding_model_id = NEW.id
) OR EXISTS (
SELECT 1 FROM skill_optimization_evaluations WHERE model_id = NEW.id
) OR EXISTS (
SELECT 1 FROM skill_optimization_clusters WHERE embedding_model_id = NEW.id
) THEN RAISE(ABORT, 'cannot change model_type while the model is referenced') END;
END`,
// ------------------------------------------------------- reporting views
// Postgres reaches into the `results` array with
// `CROSS JOIN LATERAL jsonb_array_elements`; `json_each` is the SQLite
// equivalent. The Postgres version uses `LEFT JOIN ... AND e.weight IS NOT
// NULL`, which is an inner join in effect, so this joins directly.
`CREATE VIEW IF NOT EXISTS logs_with_eval_scores AS
SELECT
l.*,
(
SELECT SUM(CAST(json_extract(result.value, '$.score') AS REAL) * e.weight)
/ NULLIF(SUM(e.weight), 0)
FROM skill_optimization_evaluation_runs er,
json_each(er.results) AS result
JOIN skill_optimization_evaluations e
ON e.id = json_extract(result.value, '$.evaluation_id')
WHERE er.log_id = l.id
AND json_extract(result.value, '$.score') IS NOT NULL
) AS avg_eval_score,
(
SELECT COUNT(*)
FROM skill_optimization_evaluation_runs er
WHERE er.log_id = l.id
) AS eval_run_count
FROM logs l`,
`CREATE VIEW IF NOT EXISTS evaluation_runs_with_scores AS
SELECT
er.id,
er.agent_id,
er.skill_id,
er.cluster_id,
er.log_id,
er.created_at,
(
SELECT SUM(CAST(json_extract(result.value, '$.score') AS REAL) * e.weight)
/ NULLIF(SUM(e.weight), 0)
FROM json_each(er.results) AS result
JOIN skill_optimization_evaluations e
ON e.id = json_extract(result.value, '$.evaluation_id')
) AS avg_score,
(
SELECT json_group_object(
e.evaluation_method,
CAST(json_extract(result.value, '$.score') AS REAL)
)
FROM json_each(er.results) AS result
JOIN skill_optimization_evaluations e
ON e.id = json_extract(result.value, '$.evaluation_id')
) AS scores_by_evaluation
FROM skill_optimization_evaluation_runs er`,
],
};
/**
* Mirrors `supabase/migrations/20260826000000_feedbacks_updated_at.sql`.
*
* `Feedback` is `.strict()` and requires `updated_at`, and
* `FeedbackCreateParams` generates one on every create, but the table never had
* the column on either backend. Appended rather than folded into the initial
* schema so the two backends' migration histories stay in step.
*/
const feedbacksUpdatedAt: LibsqlMigration = {
version: '0002_feedbacks_updated_at',
statements: [
`ALTER TABLE feedbacks
ADD COLUMN updated_at TEXT NOT NULL DEFAULT (${NOW_ISO})`,
updatedAtTrigger('feedbacks'),
],
};
/**
* The Postgres initial migration seeds the singleton settings row, and
* `getSystemSettings` returns `settings[0]` assuming it is there. The initial
* libSQL migration created the table but not the row.
*
* The id is fixed rather than generated: the row is a singleton whose id is
* never referenced by anything, and a constant keeps the migration
* deterministic (SQLite has no uuid function).
*/
const defaultSystemSettings: LibsqlMigration = {
version: '0003_default_system_settings',
statements: [
`INSERT OR IGNORE INTO system_settings (id, singleton)
VALUES ('00000000-0000-4000-8000-000000000001', 1)`,
],
};
export const libsqlMigrations: LibsqlMigration[] = [
initialSchema,
feedbacksUpdatedAt,
defaultSystemSettings,
];
|