# migrations

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: sysdb
filename: 00010-collection-schema.sqlite.sql
hash: 5c3a5ac4b79df76799b4721827ed5e1d
sql:
-- Stores collection schema as stringified json
ALTER TABLE collections ADD COLUMN schema_str TEXT;


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;


dir: metadb
filename: 00005-max-seq-id-int.sqlite.sql
hash: 0e9de46758761b373ce682925edcc326
sql:
ALTER TABLE max_seq_id ADD COLUMN int_seq_id INTEGER;

-- Convert 8 byte wide big-endian integer as blob to native 64 bit integer.
-- Adapted from https://stackoverflow.com/a/70296198.
UPDATE max_seq_id SET int_seq_id = (
  SELECT (
       (instr('123456789ABCDEF', substr(hex(seq_id), -1 , 1)) <<  0)
     | (instr('123456789ABCDEF', substr(hex(seq_id), -2 , 1)) <<  4)
     | (instr('123456789ABCDEF', substr(hex(seq_id), -3 , 1)) <<  8)
     | (instr('123456789ABCDEF', substr(hex(seq_id), -4 , 1)) << 12)
     | (instr('123456789ABCDEF', substr(hex(seq_id), -5 , 1)) << 16)
     | (instr('123456789ABCDEF', substr(hex(seq_id), -6 , 1)) << 20)
     | (instr('123456789ABCDEF', substr(hex(seq_id), -7 , 1)) << 24)
     | (instr('123456789ABCDEF', substr(hex(seq_id), -8 , 1)) << 28)
     | (instr('123456789ABCDEF', substr(hex(seq_id), -9 , 1)) << 32)
     | (instr('123456789ABCDEF', substr(hex(seq_id), -10, 1)) << 36)
     | (instr('123456789ABCDEF', substr(hex(seq_id), -11, 1)) << 40)
     | (instr('123456789ABCDEF', substr(hex(seq_id), -12, 1)) << 44)
     | (instr('123456789ABCDEF', substr(hex(seq_id), -13, 1)) << 48)
     | (instr('123456789ABCDEF', substr(hex(seq_id), -14, 1)) << 52)
     | (instr('123456789ABCDEF', substr(hex(seq_id), -15, 1)) << 56)
     | (instr('123456789ABCDEF', substr(hex(seq_id), -16, 1)) << 60)
    )
);

ALTER TABLE max_seq_id DROP COLUMN seq_id;
ALTER TABLE max_seq_id RENAME COLUMN int_seq_id TO seq_id;


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
);


# collections

id: 36dc8bc2-e5e8-4b4b-82da-5915b0026ba4
name: db_schema
database_id: 00000000-0000-0000-0000-000000000000
config_json_str: {}
schema_str:
{"defaults":{"string":{"fts_index":{"enabled":false,"config":{}},"string_inverted_index":{"enabled":true,"config":{}}},"float_list":{"vector_index":{"enabled":false,"config":{"space":"l2","hnsw":{"ef_construction":100,"max_neighbors":16,"ef_search":100,"num_threads":16,"batch_size":100,"sync_threshold":1000,"resize_factor":1.2}}}},"sparse_vector":{"sparse_vector_index":{"enabled":false,"config":{"embedding_function":{"type":"unknown"},"bm25":false}}},"int":{"int_inverted_index":{"enabled":true,"config":{}}},"float":{"float_inverted_index":{"enabled":true,"config":{}}},"bool":{"bool_inverted_index":{"enabled":true,"config":{}}}},"keys":{"#document":{"string":{"fts_index":{"enabled":true,"config":{}},"string_inverted_index":{"enabled":false,"config":{}}}},"#embedding":{"float_list":{"vector_index":{"enabled":true,"config":{"space":"l2","source_key":"#document","hnsw":{"ef_construction":100,"max_neighbors":16,"ef_search":100,"num_threads":16,"batch_size":100,"sync_threshold":1000,"resize_factor":1.2}}}}},"table_name":{"string":{"fts_index":{"enabled":false,"config":{}},"string_inverted_index":{"enabled":true,"config":{}}}}}}


id: 19e07535-4e6b-4b2c-b9aa-04f9da873ff5
name: schema_knowledge
database_id: 00000000-0000-0000-0000-000000000000
config_json_str: {}
schema_str:
{"defaults":{"string":{"fts_index":{"enabled":false,"config":{}},"string_inverted_index":{"enabled":true,"config":{}}},"float_list":{"vector_index":{"enabled":false,"config":{"space":"l2","embedding_function":{"type":"known","name":"default","config":{}},"hnsw":{"ef_construction":100,"max_neighbors":16,"ef_search":100,"num_threads":16,"batch_size":100,"sync_threshold":1000,"resize_factor":1.2}}}},"sparse_vector":{"sparse_vector_index":{"enabled":false,"config":{"embedding_function":{"type":"unknown"},"bm25":false}}},"int":{"int_inverted_index":{"enabled":true,"config":{}}},"float":{"float_inverted_index":{"enabled":true,"config":{}}},"bool":{"bool_inverted_index":{"enabled":true,"config":{}}}},"keys":{"table":{"string":{"fts_index":{"enabled":false,"config":{}},"string_inverted_index":{"enabled":true,"config":{}}}},"#embedding":{"float_list":{"vector_index":{"enabled":true,"config":{"space":"l2","embedding_function":{"type":"known","name":"default","config":{}},"source_key":"#document","hnsw":{"ef_construction":100,"max_neighbors":16,"ef_search":100,"num_threads":16,"batch_size":100,"sync_threshold":1000,"resize_factor":1.2}}}}},"#document":{"string":{"fts_index":{"enabled":true,"config":{}},"string_inverted_index":{"enabled":false,"config":{}}}}}}


# embedding_metadata

key: chroma:document
string_value:
Table: employees
Columns:
- id (INTEGER)
- name (VARCHAR(100))
- department (VARCHAR(50))
- salary (DECIMAL(10, 2))
- hire_date (DATE)


key: chroma:document
string_value:
Table: sales
Columns:
- sale_id (INTEGER)
- employee_id (INTEGER)
- amount (DECIMAL(10, 2))
- sale_date (DATE)


key: chroma:document
string_value:
Table: customers
Columns: id (int), name (varchar(100)), company (varchar(100)), region (varchar(50)), join_date (date)


key: chroma:document
string_value:
Table: departments
Columns: id (int), name (varchar(50)), budget (decimal(15,2)), location (varchar(100))


key: chroma:document
string_value:
Table: employees
Columns: id (int), name (varchar(100)), email (varchar(100)), department_id (int), role (varchar(50)), salary (decimal(10,2)), hire_date (date)


key: chroma:document
string_value:
Table: products
Columns: id (int), name (varchar(100)), category (varchar(50)), price (decimal(10,2)), stock_quantity (int)


key: chroma:document
string_value:
Table: sales
Columns: id (int), employee_id (int), customer_id (int), product_id (int), quantity (int), total_amount (decimal(10,2)), sale_date (date)


# embeddings_queue

created_at: 2026-01-07 14:38:31
topic: persistent://default/default/36dc8bc2-e5e8-4b4b-82da-5915b0026ba4
id: a1285c8a-ba66-4965-a595-f2b1c1cc40a5
encoding: FLOAT32
metadata:
{"chroma:document":"Table: employees\nColumns:\n- id (INTEGER)\n- name (VARCHAR(100))\n- department (VARCHAR(50))\n- salary (DECIMAL(10, 2))\n- hire_date (DATE)\n","table_name":"employees"}


created_at: 2026-01-07 14:38:31
topic: persistent://default/default/36dc8bc2-e5e8-4b4b-82da-5915b0026ba4
id: 935171b9-9303-4ec2-ac1f-0ad761e9c49f
encoding: FLOAT32
metadata:
{"chroma:document":"Table: sales\nColumns:\n- sale_id (INTEGER)\n- employee_id (INTEGER)\n- amount (DECIMAL(10, 2))\n- sale_date (DATE)\n","table_name":"sales"}


created_at: 2026-01-07 14:40:12
topic: persistent://default/default/36dc8bc2-e5e8-4b4b-82da-5915b0026ba4
id: 02a9e521-6b01-4bb9-b73a-247a2d27b212
encoding: FLOAT32
metadata:
{"table_name":"employees","chroma:document":"Table: employees\nColumns:\n- id (INTEGER)\n- name (VARCHAR(100))\n- department (VARCHAR(50))\n- salary (DECIMAL(10, 2))\n- hire_date (DATE)\n"}


created_at: 2026-01-07 14:40:12
topic: persistent://default/default/36dc8bc2-e5e8-4b4b-82da-5915b0026ba4
id: e732f880-8642-42af-8e5c-4d9a728aa317
encoding: FLOAT32
metadata:
{"table_name":"sales","chroma:document":"Table: sales\nColumns:\n- sale_id (INTEGER)\n- employee_id (INTEGER)\n- amount (DECIMAL(10, 2))\n- sale_date (DATE)\n"}


created_at: 2026-01-08 07:52:44
topic: persistent://default/default/19e07535-4e6b-4b2c-b9aa-04f9da873ff5
id: customers
encoding: FLOAT32
metadata:
{"table":"customers","chroma:document":"Table: customers\nColumns: id (int), name (varchar(100)), company (varchar(100)), region (varchar(50)), join_date (date)"}


created_at: 2026-01-08 07:52:45
topic: persistent://default/default/19e07535-4e6b-4b2c-b9aa-04f9da873ff5
id: departments
encoding: FLOAT32
metadata:
{"table":"departments","chroma:document":"Table: departments\nColumns: id (int), name (varchar(50)), budget (decimal(15,2)), location (varchar(100))"}


created_at: 2026-01-08 07:52:45
topic: persistent://default/default/19e07535-4e6b-4b2c-b9aa-04f9da873ff5
id: employees
encoding: FLOAT32
metadata:
{"table":"employees","chroma:document":"Table: employees\nColumns: id (int), name (varchar(100)), email (varchar(100)), department_id (int), role (varchar(50)), salary (decimal(10,2)), hire_date (date)"}


created_at: 2026-01-08 07:52:45
topic: persistent://default/default/19e07535-4e6b-4b2c-b9aa-04f9da873ff5
id: products
encoding: FLOAT32
metadata:
{"chroma:document":"Table: products\nColumns: id (int), name (varchar(100)), category (varchar(50)), price (decimal(10,2)), stock_quantity (int)","table":"products"}


created_at: 2026-01-08 07:52:45
topic: persistent://default/default/19e07535-4e6b-4b2c-b9aa-04f9da873ff5
id: sales
encoding: FLOAT32
metadata:
{"table":"sales","chroma:document":"Table: sales\nColumns: id (int), employee_id (int), customer_id (int), product_id (int), quantity (int), total_amount (decimal(10,2)), sale_date (date)"}


created_at: 2026-01-09 06:41:31
topic: persistent://default/default/19e07535-4e6b-4b2c-b9aa-04f9da873ff5
id: products
encoding: FLOAT32
metadata:
{"table":"products","chroma:document":"Table: products\nColumns: id (int), name (varchar(100)), category (varchar(50)), price (decimal(10,2)), stock_quantity (int)"}


created_at: 2026-01-09 06:41:31
topic: persistent://default/default/19e07535-4e6b-4b2c-b9aa-04f9da873ff5
id: sales
encoding: FLOAT32
metadata:
{"chroma:document":"Table: sales\nColumns: id (int), employee_id (int), customer_id (int), product_id (int), quantity (int), total_amount (decimal(10,2)), sale_date (date)","table":"sales"}


created_at: 2026-01-09 06:42:25
topic: persistent://default/default/19e07535-4e6b-4b2c-b9aa-04f9da873ff5
id: customers
encoding: FLOAT32
metadata:
{"chroma:document":"Table: customers\nColumns: id (int), name (varchar(100)), company (varchar(100)), region (varchar(50)), join_date (date)","table":"customers"}


created_at: 2026-01-09 06:42:26
topic: persistent://default/default/19e07535-4e6b-4b2c-b9aa-04f9da873ff5
id: employees
encoding: FLOAT32
metadata:
{"chroma:document":"Table: employees\nColumns: id (int), name (varchar(100)), email (varchar(100)), department_id (int), role (varchar(50)), salary (decimal(10,2)), hire_date (date)","table":"employees"}


created_at: 2026-01-09 06:47:19
topic: persistent://default/default/19e07535-4e6b-4b2c-b9aa-04f9da873ff5
id: departments
encoding: FLOAT32
metadata:
{"chroma:document":"Table: departments\nColumns: id (int), name (varchar(50)), budget (decimal(15,2)), location (varchar(100))","table":"departments"}


