# migrations

dir: embeddings_queue
filename: 00001-embeddings.sqlite.sql
hash: d3755dfd232be8e8301f4d7fcfb3a486
sql:
CREATE TABLE embeddings_queue (
    seq_id INTEGER PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    operation INTEGER NOT NULL,
    topic TEXT NOT NULL,
    id TEXT NOT NULL,
    vector BLOB,
    encoding TEXT,
    metadata TEXT
);


dir: sysdb
filename: 00001-collections.sqlite.sql
hash: 38352d725ad1c16074fac420b22b4633
sql:
CREATE TABLE collections (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    topic TEXT NOT NULL,
    UNIQUE (name)
);

CREATE TABLE collection_metadata (
    collection_id TEXT REFERENCES collections(id) ON DELETE CASCADE,
    key TEXT NOT NULL,
    str_value TEXT,
    int_value INTEGER,
    float_value REAL,
    PRIMARY KEY (collection_id, key)
);


dir: sysdb
filename: 00002-segments.sqlite.sql
hash: 2913cb6a503055a95f625448037e8912
sql:
CREATE TABLE segments (
    id TEXT PRIMARY KEY,
    type TEXT NOT NULL,
    scope TEXT NOT NULL,
    topic TEXT,
    collection TEXT REFERENCES collection(id)
);

CREATE TABLE segment_metadata (
    segment_id TEXT  REFERENCES segments(id) ON DELETE CASCADE,
    key TEXT NOT NULL,
    str_value TEXT,
    int_value INTEGER,
    float_value REAL,
    PRIMARY KEY (segment_id, key)
);


dir: sysdb
filename: 00004-tenants-databases.sqlite.sql
hash: 048867ce8fcdefe4023c7110e4433591
sql:
CREATE TABLE IF NOT EXISTS tenants (
    id TEXT PRIMARY KEY,
    UNIQUE (id)
);

CREATE TABLE IF NOT EXISTS databases (
    id TEXT PRIMARY KEY, -- unique globally
    name TEXT NOT NULL, -- unique per tenant
    tenant_id TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    UNIQUE (tenant_id, name) -- Ensure that a tenant has only one database with a given name
);

CREATE TABLE IF NOT EXISTS collections_tmp (
    id TEXT PRIMARY KEY, -- unique globally
    name TEXT NOT NULL, -- unique per database
    topic TEXT NOT NULL,
    dimension INTEGER,
    database_id TEXT NOT NULL REFERENCES databases(id) ON DELETE CASCADE,
    UNIQUE (name, database_id)
);

-- Create default tenant and database
INSERT OR REPLACE INTO tenants (id) VALUES ('default_tenant'); -- The default tenant id is 'default_tenant' others are UUIDs
INSERT OR REPLACE INTO databases (id, name, tenant_id) VALUES ('00000000-0000-0000-0000-000000000000', 'default_database', 'default_tenant');

INSERT OR REPLACE INTO collections_tmp (id, name, topic, dimension, database_id)
    SELECT id, name, topic, dimension, '00000000-0000-0000-0000-000000000000' FROM collections;
DROP TABLE collections;
ALTER TABLE collections_tmp RENAME TO collections;


dir: sysdb
filename: 00005-remove-topic.sqlite.sql
hash: b1367c826b8fba5f96f27befdc1d42d2
sql:
-- Remove the topic column from the Collections and Segments tables

ALTER TABLE collections DROP COLUMN topic;
ALTER TABLE segments DROP COLUMN topic;


dir: sysdb
filename: 00006-collection-segment-metadata.sqlite.sql
hash: 4eea7468935bf25d4604a0fed2366116
sql:
-- SQLite does not support adding check with alter table, as a result, adding a check
-- involve creating a new table and copying the data over. It is over kill with adding
-- a boolean type column. The application write to the table needs to ensure the data
-- integrity.
ALTER TABLE collection_metadata ADD COLUMN bool_value INTEGER;
ALTER TABLE segment_metadata ADD COLUMN bool_value INTEGER;


dir: sysdb
filename: 00007-collection-config.sqlite.sql
hash: 1c7e63bba346a42a18b6ab7f1c989bed
sql:
-- Stores collection configuration dictionaries.
ALTER TABLE collections ADD COLUMN config_json_str TEXT;


dir: sysdb
filename: 00008-maintenance-log.sqlite.sql
hash: 0a0e7e93111a01789addf64961c6127c
sql:
-- Records when database maintenance operations are performed.
-- At time of creation, this table is only used to record vacuum operations.
CREATE TABLE maintenance_log (
  id INT PRIMARY KEY,
  timestamp INT NOT NULL,
  operation TEXT NOT NULL
);


dir: sysdb
filename: 00009-segment-collection-not-null.sqlite.sql
hash: 054355aef9e63702bf54ea29e61563f1
sql:
-- This makes segments.collection non-nullable.
CREATE TABLE segments_temp (
    id TEXT PRIMARY KEY,
    type TEXT NOT NULL,
    scope TEXT NOT NULL,
    collection TEXT REFERENCES collection(id) NOT NULL
);

INSERT INTO segments_temp SELECT * FROM segments;
DROP TABLE segments;
ALTER TABLE segments_temp RENAME TO segments;


dir: metadb
filename: 00001-embedding-metadata.sqlite.sql
hash: 2b4cf52c4bb2676e21d6860a4409f856
sql:
CREATE TABLE embeddings (
    id INTEGER PRIMARY KEY,
    segment_id TEXT NOT NULL,
    embedding_id TEXT NOT NULL,
    seq_id BLOB NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (segment_id, embedding_id)
);

CREATE TABLE embedding_metadata (
    id INTEGER REFERENCES embeddings(id),
    key TEXT NOT NULL,
    string_value TEXT,
    int_value INTEGER,
    float_value REAL,
    PRIMARY KEY (id, key)
);

CREATE TABLE max_seq_id (
    segment_id TEXT PRIMARY KEY,
    seq_id BLOB NOT NULL
);

CREATE VIRTUAL TABLE embedding_fulltext USING fts5(id, string_value);


dir: metadb
filename: 00002-embedding-metadata.sqlite.sql
hash: 12a570f7121b3a8ce750a2a7c36da20f
sql:
-- SQLite does not support adding check with alter table, as a result, adding a check
-- involve creating a new table and copying the data over. It is over kill with adding
-- a boolean type column. The application write to the table needs to ensure the data
-- integrity.
ALTER TABLE embedding_metadata ADD COLUMN bool_value INTEGER


dir: metadb
filename: 00003-full-text-tokenize.sqlite.sql
hash: f97ad6334aeaa8f419f01110b648b97a
sql:
CREATE VIRTUAL TABLE embedding_fulltext_search USING fts5(string_value, tokenize='trigram');
INSERT INTO embedding_fulltext_search (rowid, string_value) SELECT rowid, string_value FROM embedding_metadata;
DROP TABLE embedding_fulltext;


dir: metadb
filename: 00004-metadata-indices.sqlite.sql
hash: fb36603a45ee2cd0254cef3ef86585e8
sql:
CREATE INDEX IF NOT EXISTS embedding_metadata_int_value ON embedding_metadata (key, int_value) WHERE int_value IS NOT NULL;
CREATE INDEX IF NOT EXISTS embedding_metadata_float_value ON embedding_metadata (key, float_value) WHERE float_value IS NOT NULL;
CREATE INDEX IF NOT EXISTS embedding_metadata_string_value ON embedding_metadata (key, string_value) WHERE string_value IS NOT NULL;


# embeddings_queue

created_at: 2025-01-14 16:03:42
topic: persistent://default/default/ac139c2e-6fbf-4cb7-9f15-5f3cb251309a
id: 84810d02-2349-471f-b69d-9c239e03e769
encoding: FLOAT32
metadata:
{"excerpt_keywords": "scouting, youth development, outdoor activities, leadership skills, community service, merit badges, camping, adventure, teamwork, character building", "_node_content": "{\"id_\": \"84810d02-2349-471f-b69d-9c239e03e769\", \"embedding\": null, \"metadata\": {\"excerpt_keywords\": \"scouting, youth development, outdoor activities, leadership skills, community service, merit badges, camping, adventure, teamwork, character building\"}, \"excluded_embed_metadata_keys\": [], \"excluded_llm_metadata_keys\": [], \"relationships\": {\"1\": {\"node_id\": \"8a2cdbe9-ff5b-4a7a-9087-22e91e1f31e9\", \"node_type\": \"4\", \"metadata\": {}, \"hash\": \"9aef565c88e7d006ebf3f29fb07b94100004f49b866c5fc0885ec7fd5d723379\", \"class_name\": \"RelatedNodeInfo\"}}, \"metadata_template\": \"{key}: {value}\", \"metadata_separator\": \"\\n\", \"text\": \"\", \"mimetype\": \"text/plain\", \"start_char_idx\": 0, \"end_char_idx\": 20, \"metadata_seperator\": \"\\n\", \"text_template\": \"[Excerpt from document]\\n{metadata_str}\\nExcerpt:\\n-----\\n{content}\\n-----\\n\", \"class_name\": \"TextNode\"}", "_node_type": "TextNode", "document_id": "8a2cdbe9-ff5b-4a7a-9087-22e91e1f31e9", "doc_id": "8a2cdbe9-ff5b-4a7a-9087-22e91e1f31e9", "ref_doc_id": "8a2cdbe9-ff5b-4a7a-9087-22e91e1f31e9", "chroma:document": "https://scouting.org"}


created_at: 2025-01-14 16:03:42
topic: persistent://default/default/ac139c2e-6fbf-4cb7-9f15-5f3cb251309a
id: 681be574-8fb3-4489-b0f0-fccdb335177f
encoding: FLOAT32
metadata:
{"excerpt_keywords": "Scouting, youth development, outdoor activities, leadership skills, community service, adventure, teamwork, merit badges, camping, scouting programs", "_node_content": "{\"id_\": \"681be574-8fb3-4489-b0f0-fccdb335177f\", \"embedding\": null, \"metadata\": {\"excerpt_keywords\": \"Scouting, youth development, outdoor activities, leadership skills, community service, adventure, teamwork, merit badges, camping, scouting programs\"}, \"excluded_embed_metadata_keys\": [], \"excluded_llm_metadata_keys\": [], \"relationships\": {\"1\": {\"node_id\": \"75fdff21-6e3b-4e28-a281-4605763e046d\", \"node_type\": \"4\", \"metadata\": {}, \"hash\": \"a4ffd2348829073a8fe5d6ccb839463a307c8ce3c552cf94bf476c7f404d6494\", \"class_name\": \"RelatedNodeInfo\"}}, \"metadata_template\": \"{key}: {value}\", \"metadata_separator\": \"\\n\", \"text\": \"\", \"mimetype\": \"text/plain\", \"start_char_idx\": 0, \"end_char_idx\": 33, \"metadata_seperator\": \"\\n\", \"text_template\": \"[Excerpt from document]\\n{metadata_str}\\nExcerpt:\\n-----\\n{content}\\n-----\\n\", \"class_name\": \"TextNode\"}", "_node_type": "TextNode", "document_id": "75fdff21-6e3b-4e28-a281-4605763e046d", "doc_id": "75fdff21-6e3b-4e28-a281-4605763e046d", "ref_doc_id": "75fdff21-6e3b-4e28-a281-4605763e046d", "chroma:document": "https://blog.scoutingmagazine.org"}


# collections

id: ac139c2e-6fbf-4cb7-9f15-5f3cb251309a
name: scout_data
database_id: 00000000-0000-0000-0000-000000000000
config_json_str:
{"hnsw_configuration": {"space": "l2", "ef_construction": 100, "ef_search": 10, "num_threads": 8, "M": 16, "resize_factor": 1.2, "batch_size": 100, "sync_threshold": 1000, "_type": "HNSWConfigurationInternal"}, "_type": "CollectionConfigurationInternal"}


# embedding_metadata

key: excerpt_keywords
string_value:
scouting, youth development, outdoor activities, leadership skills, community service, adventure, teamwork, character building, merit badges, camping


key: _node_content
string_value:
{"id_": "deb94d26-f917-47a0-bf26-97f876c38617", "embedding": null, "metadata": {"excerpt_keywords": "scouting, youth development, outdoor activities, leadership skills, community service, adventure, teamwork, character building, merit badges, camping"}, "excluded_embed_metadata_keys": [], "excluded_llm_metadata_keys": [], "relationships": {"1": {"node_id": "14da916a-399b-4876-b68e-f8a23c2a7461", "node_type": "4", "metadata": {}, "hash": "9aef565c88e7d006ebf3f29fb07b94100004f49b866c5fc0885ec7fd5d723379", "class_name": "RelatedNodeInfo"}}, "metadata_template": "{key}: {value}", "metadata_separator": "\n", "text": "", "mimetype": "text/plain", "start_char_idx": 0, "end_char_idx": 20, "metadata_seperator": "\n", "text_template": "[Excerpt from document]\n{metadata_str}\nExcerpt:\n-----\n{content}\n-----\n", "class_name": "TextNode"}


key: excerpt_keywords
string_value:
Scouting, youth development, outdoor activities, leadership skills, community service, adventure, teamwork, merit badges, camping, scouting programs


key: _node_content
string_value:
{"id_": "92c9ae96-6336-4c1b-9906-b2a41dbd0e53", "embedding": null, "metadata": {"excerpt_keywords": "Scouting, youth development, outdoor activities, leadership skills, community service, adventure, teamwork, merit badges, camping, scouting programs"}, "excluded_embed_metadata_keys": [], "excluded_llm_metadata_keys": [], "relationships": {"1": {"node_id": "859558bc-19b6-4a0b-b7b1-a84f86f0ff31", "node_type": "4", "metadata": {}, "hash": "a4ffd2348829073a8fe5d6ccb839463a307c8ce3c552cf94bf476c7f404d6494", "class_name": "RelatedNodeInfo"}}, "metadata_template": "{key}: {value}", "metadata_separator": "\n", "text": "", "mimetype": "text/plain", "start_char_idx": 0, "end_char_idx": 33, "metadata_seperator": "\n", "text_template": "[Excerpt from document]\n{metadata_str}\nExcerpt:\n-----\n{content}\n-----\n", "class_name": "TextNode"}


key: excerpt_keywords
string_value:
scouting, youth development, outdoor activities, leadership skills, community service, merit badges, camping, adventure, teamwork, character building


key: _node_content
string_value:
{"id_": "f81a4d63-82b3-49fe-bf4f-373c6f5dce5f", "embedding": null, "metadata": {"excerpt_keywords": "scouting, youth development, outdoor activities, leadership skills, community service, merit badges, camping, adventure, teamwork, character building"}, "excluded_embed_metadata_keys": [], "excluded_llm_metadata_keys": [], "relationships": {"1": {"node_id": "1d0a5875-c852-4b0f-96ee-133a16d4862e", "node_type": "4", "metadata": {}, "hash": "9aef565c88e7d006ebf3f29fb07b94100004f49b866c5fc0885ec7fd5d723379", "class_name": "RelatedNodeInfo"}}, "metadata_template": "{key}: {value}", "metadata_separator": "\n", "text": "", "mimetype": "text/plain", "start_char_idx": 0, "end_char_idx": 20, "metadata_seperator": "\n", "text_template": "[Excerpt from document]\n{metadata_str}\nExcerpt:\n-----\n{content}\n-----\n", "class_name": "TextNode"}


key: _node_content
string_value:
{"id_": "96edde36-b29b-463e-9801-fa24e9d25167", "embedding": null, "metadata": {"excerpt_keywords": "Scouting, youth development, outdoor activities, leadership skills, community service, adventure, teamwork, merit badges, camping, scouting programs"}, "excluded_embed_metadata_keys": [], "excluded_llm_metadata_keys": [], "relationships": {"1": {"node_id": "62c34689-18f3-4cf5-9541-a5dede9d2315", "node_type": "4", "metadata": {}, "hash": "a4ffd2348829073a8fe5d6ccb839463a307c8ce3c552cf94bf476c7f404d6494", "class_name": "RelatedNodeInfo"}}, "metadata_template": "{key}: {value}", "metadata_separator": "\n", "text": "", "mimetype": "text/plain", "start_char_idx": 0, "end_char_idx": 33, "metadata_seperator": "\n", "text_template": "[Excerpt from document]\n{metadata_str}\nExcerpt:\n-----\n{content}\n-----\n", "class_name": "TextNode"}


key: _node_content
string_value:
{"id_": "c20c896c-2236-41ca-a512-69c2e47c6620", "embedding": null, "metadata": {"excerpt_keywords": "scouting, youth development, outdoor activities, leadership skills, community service, merit badges, camping, adventure, teamwork, character building"}, "excluded_embed_metadata_keys": [], "excluded_llm_metadata_keys": [], "relationships": {"1": {"node_id": "bcf57717-0d5e-4081-b7cd-673d357474f9", "node_type": "4", "metadata": {}, "hash": "9aef565c88e7d006ebf3f29fb07b94100004f49b866c5fc0885ec7fd5d723379", "class_name": "RelatedNodeInfo"}}, "metadata_template": "{key}: {value}", "metadata_separator": "\n", "text": "", "mimetype": "text/plain", "start_char_idx": 0, "end_char_idx": 20, "metadata_seperator": "\n", "text_template": "[Excerpt from document]\n{metadata_str}\nExcerpt:\n-----\n{content}\n-----\n", "class_name": "TextNode"}


key: _node_content
string_value:
{"id_": "8335c957-10db-487e-ab2a-b775b3762fdc", "embedding": null, "metadata": {"excerpt_keywords": "Scouting, youth development, outdoor activities, leadership skills, community service, adventure, teamwork, merit badges, camping, scouting programs"}, "excluded_embed_metadata_keys": [], "excluded_llm_metadata_keys": [], "relationships": {"1": {"node_id": "df08a41d-3d04-412c-bb74-e333d2686229", "node_type": "4", "metadata": {}, "hash": "a4ffd2348829073a8fe5d6ccb839463a307c8ce3c552cf94bf476c7f404d6494", "class_name": "RelatedNodeInfo"}}, "metadata_template": "{key}: {value}", "metadata_separator": "\n", "text": "", "mimetype": "text/plain", "start_char_idx": 0, "end_char_idx": 33, "metadata_seperator": "\n", "text_template": "[Excerpt from document]\n{metadata_str}\nExcerpt:\n-----\n{content}\n-----\n", "class_name": "TextNode"}


key: _node_content
string_value:
{"id_": "84810d02-2349-471f-b69d-9c239e03e769", "embedding": null, "metadata": {"excerpt_keywords": "scouting, youth development, outdoor activities, leadership skills, community service, merit badges, camping, adventure, teamwork, character building"}, "excluded_embed_metadata_keys": [], "excluded_llm_metadata_keys": [], "relationships": {"1": {"node_id": "8a2cdbe9-ff5b-4a7a-9087-22e91e1f31e9", "node_type": "4", "metadata": {}, "hash": "9aef565c88e7d006ebf3f29fb07b94100004f49b866c5fc0885ec7fd5d723379", "class_name": "RelatedNodeInfo"}}, "metadata_template": "{key}: {value}", "metadata_separator": "\n", "text": "", "mimetype": "text/plain", "start_char_idx": 0, "end_char_idx": 20, "metadata_seperator": "\n", "text_template": "[Excerpt from document]\n{metadata_str}\nExcerpt:\n-----\n{content}\n-----\n", "class_name": "TextNode"}


key: _node_content
string_value:
{"id_": "681be574-8fb3-4489-b0f0-fccdb335177f", "embedding": null, "metadata": {"excerpt_keywords": "Scouting, youth development, outdoor activities, leadership skills, community service, adventure, teamwork, merit badges, camping, scouting programs"}, "excluded_embed_metadata_keys": [], "excluded_llm_metadata_keys": [], "relationships": {"1": {"node_id": "75fdff21-6e3b-4e28-a281-4605763e046d", "node_type": "4", "metadata": {}, "hash": "a4ffd2348829073a8fe5d6ccb839463a307c8ce3c552cf94bf476c7f404d6494", "class_name": "RelatedNodeInfo"}}, "metadata_template": "{key}: {value}", "metadata_separator": "\n", "text": "", "mimetype": "text/plain", "start_char_idx": 0, "end_char_idx": 33, "metadata_seperator": "\n", "text_template": "[Excerpt from document]\n{metadata_str}\nExcerpt:\n-----\n{content}\n-----\n", "class_name": "TextNode"}


