Source code
Revision control
Copy as Markdown
Other Tools
/*
This Source Code Form is subject to the terms of the Mozilla Public
* License, v. 2.0. If a copy of the MPL was not distributed with this
// Every time the schema or the underlying data changes, you must bump up the
// schema version.
// Remember to:
// 1. Bump up the version number
// 2. Add a migration function to migrate the data to the new schema.
// 3. Update #createDatabaseEntities and #checkDatabaseHealth
// 4. Add a test to check that the migration works correctly.
// Note: migrations should be reasonably re-entry-friendly. If the user
// downgrades, the schema version is decreased, and upon a subsequent upgrade,
// the migration step is reapplied.
// This ensures that any necessary conversions are performed, even for entries
// added after the downgrade.
// In practice, schema changes should be additive, allowing newer versions to
// operate on older schemas, albeit with potentially reduced functionality.
export const ESCAPE_CHAR = "/";
export const CONVERSATION_TABLE = `
CREATE TABLE conversation (
conv_id TEXT PRIMARY KEY,
title TEXT,
description TEXT,
page_url TEXT,
page_meta_jsonb BLOB,
created_date INTEGER NOT NULL,
updated_date INTEGER NOT NULL,
status INTEGER NOT NULL DEFAULT 0,
active_branch_tip_message_id TEXT -- no foreign here, as we insert messages later.
) WITHOUT ROWID;
`;
export const CONVERSATION_UPDATED_DATE_INDEX = `
CREATE INDEX conversation_updated_date_idx ON conversation(updated_date);
`;
export const MESSAGE_TABLE = `
CREATE TABLE message (
message_id TEXT PRIMARY KEY,
conv_id TEXT NOT NULL REFERENCES conversation(conv_id) ON DELETE CASCADE,
created_date INTEGER NOT NULL,
parent_message_id TEXT REFERENCES message(message_id) ON DELETE CASCADE,
revision_root_message_id TEXT REFERENCES message(message_id) ON DELETE CASCADE,
ordinal INTEGER NOT NULL CHECK(ordinal >= 0),
is_active_branch INTEGER NOT NULL,
role INTEGER NOT NULL,
model_id TEXT,
params_jsonb BLOB,
content_jsonb BLOB,
usage_jsonb BLOB,
page_url TEXT,
turn_index INTEGER,
insights_enabled BOOLEAN,
insights_flag_source INTEGER,
insights_applied_jsonb BLOB,
web_search_queries_jsonb BLOB
) WITHOUT ROWID;
`;
export const MESSAGE_ORDINAL_INDEX = `
CREATE INDEX message_ordinal_idx ON message(ordinal);
`;
// Maybe add hashed url column to optimize message_url_idx
export const MESSAGE_URL_INDEX = `
CREATE INDEX message_url_idx ON message(page_url);
`;
export const MESSAGE_CREATED_DATE_INDEX = `
CREATE INDEX message_created_date_idx ON message(created_date);
`;
export const MESSAGE_CONV_ID_INDEX = `
CREATE INDEX IF NOT EXISTS message_conv_id_idx ON message(conv_id);
`;
export const CONVERSATION_INSERT = `
INSERT INTO conversation (
conv_id, title, description, page_url, page_meta_jsonb,
created_date, updated_date, status, active_branch_tip_message_id
) VALUES (
:conv_id, :title, :description, :page_url, jsonb(:page_meta),
:created_date, :updated_date, :status, :active_branch_tip_message_id
)
ON CONFLICT(conv_id) DO UPDATE
SET title = :title,
updated_date = :updated_date,
status = :status,
active_branch_tip_message_id = :active_branch_tip_message_id;
`;
export const MESSAGE_INSERT = `
INSERT INTO message (
message_id, conv_id, created_date, parent_message_id,
revision_root_message_id, ordinal, is_active_branch, role,
model_id, params_jsonb, content_jsonb, usage_jsonb, page_url, turn_index,
insights_enabled, insights_flag_source, insights_applied_jsonb,
web_search_queries_jsonb
) VALUES (
:message_id, :conv_id, :created_date, :parent_message_id,
:revision_root_message_id, :ordinal, :is_active_branch, :role,
:model_id, jsonb(:params), jsonb(:content), jsonb(:usage), :page_url, :turn_index,
:insights_enabled, :insights_flag_source, jsonb(:insights_applied_jsonb),
jsonb(:web_search_queries_jsonb)
)
ON CONFLICT(message_id) DO UPDATE SET
is_active_branch = :is_active_branch;
`;
export const CONVERSATIONS_MOST_RECENT = `
SELECT conv_id, title
FROM conversation
ORDER BY updated_date DESC
LIMIT :limit;
`;
export const CONVERSATIONS_OLDEST = `
SELECT conv_id, title
FROM conversation
ORDER BY updated_date ASC
LIMIT :limit;
`;
export const CONVERSATION_BY_ID = `
SELECT conv_id, title, description, page_url,
json(page_meta_jsonb) AS page_meta, created_date, updated_date,
status, active_branch_tip_message_id
FROM conversation WHERE conv_id = :conv_id;
`;
export const CONVERSATIONS_BY_DATE = `
SELECT conv_id, title, description, page_url,
json(page_meta_jsonb) AS page_meta, created_date, updated_date,
status, active_branch_tip_message_id
FROM conversation
WHERE updated_date >= :start_date AND updated_date <= :end_date
ORDER BY updated_date DESC;
`;
export const CONVERSATIONS_BY_URL = `
SELECT c.conv_id, c.title, c.description, c.page_url,
json(c.page_meta_jsonb) AS page_meta, c.created_date, c.updated_date,
c.status, c.active_branch_tip_message_id
FROM conversation c
WHERE EXISTS (
SELECT 1
FROM message m
WHERE m.conv_id = c.conv_id
AND m.page_url = :page_url
)
ORDER BY c.updated_date DESC;
`;
/**
* Get all messages for multiple conversations
*
* @param {number} amount - The number of conversation IDs to get messages for
*/
export function getConversationMessagesSql(amount) {
return `
SELECT
message_id, created_date, parent_message_id, revision_root_message_id,
ordinal, is_active_branch, role, model_id, conv_id,
json(params_jsonb) AS params, json(usage_jsonb) AS usage,
page_url, turn_index, insights_enabled, insights_flag_source,
json(insights_applied_jsonb) AS insights_applied,
json(web_search_queries_jsonb) AS web_search_queries,
json(content_jsonb) AS content
FROM message
WHERE conv_id IN(${new Array(amount).fill("?").join(",")})
ORDER BY ordinal ASC;
`;
}
export const CONVERSATIONS_CONTENT_SEARCH = `
SELECT c.conv_id, c.title, c.description, c.page_url,
json(c.page_meta_jsonb) AS page_meta, c.created_date, c.updated_date,
c.status, c.active_branch_tip_message_id
FROM conversation c
JOIN message m ON m.conv_id = c.conv_id
WHERE json_type(m.content_jsonb, :path) IS NOT NULL;
`;
export const CONVERSATIONS_CONTENT_SEARCH_BY_ROLE = `
SELECT c.conv_id, c.title, c.description, c.page_url,
json(c.page_meta_jsonb) AS page_meta, c.created_date, c.updated_date,
c.status, c.active_branch_tip_message_id
FROM conversation c
JOIN message m ON m.conv_id = c.conv_id
WHERE m.role = :role
AND json_type(m.content_jsonb, :path) IS NOT NULL;
`;
export const CONVERSATIONS_HISTORY_SEARCH = `
SELECT c.conv_id, c.title, c.description, c.page_url,
json(c.page_meta_jsonb) AS page_meta, c.created_date, c.updated_date,
c.status, c.active_branch_tip_message_id
FROM conversation c
JOIN message m ON m.conv_id = c.conv_id
WHERE m.role = 0
AND (
CAST(json_extract(m.content_jsonb, :path) AS TEXT) LIKE :pattern ESCAPE '/'
OR
c.title LIKE :pattern ESCAPE '/'
);
`;
export const MESSAGES_BY_DATE = `
SELECT
message_id, created_date, parent_message_id, revision_root_message_id,
ordinal, is_active_branch, role, model_id, conv_id,
json(params_jsonb) AS params, json(usage_jsonb) AS usage,
page_url, turn_index, insights_enabled, insights_flag_source,
json(insights_applied_jsonb) AS insights_applied,
json(web_search_queries_jsonb) AS web_search_queries,
json(content_jsonb) AS content
FROM message
WHERE created_date >= :start_date AND created_date <= :end_date
ORDER BY created_date DESC
LIMIT :limit OFFSET :offset;
`;
export const MESSAGES_BY_DATE_AND_ROLE = `
SELECT
message_id, created_date, parent_message_id, revision_root_message_id,
ordinal, is_active_branch, role, model_id, conv_id,
json(params_jsonb) AS params, json(usage_jsonb) AS usage,
page_url, turn_index, insights_enabled, insights_flag_source,
json(insights_applied_jsonb) AS insights_applied,
json(web_search_queries_jsonb) AS web_search_queries,
json(content_jsonb) AS content
FROM message
WHERE role = :role
AND created_date >= :start_date AND created_date <= :end_date
ORDER BY created_date DESC
LIMIT :limit OFFSET :offset;
`;
export const DELETE_CONVERSATION_BY_ID = `
DELETE FROM conversation WHERE conv_id = :conv_id;
`;
export const CONVERSATION_HISTORY = `
SELECT c.conv_id, c.title, c.created_date, c.updated_date, (
SELECT group_concat(t.page_url)
FROM (
SELECT
m.page_url
FROM message m
WHERE m.conv_id = c.conv_id
AND m.page_url IS NOT NULL
GROUP BY m.page_url
ORDER BY MAX(m.created_date) ASC
) AS t
) AS urls
FROM conversation c
WHERE EXISTS (
SELECT 1
FROM message AS m
WHERE m.conv_id = c.conv_id
)
ORDER BY c.updated_date {sort}
LIMIT :limit OFFSET :offset;
`;