# 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: metadb
filename: 00006-metadata-array-support.sqlite.sql
hash: e026f01ea92c1baa1493f4ad5ca7cfe7
sql:
-- Separate table for exploded array metadata values.
-- Each array element gets its own row, enabling efficient $contains queries.
-- The existing embedding_metadata table (with its PRIMARY KEY (id, key))
-- remains untouched and continues to store scalar metadata values.

CREATE TABLE IF NOT EXISTS embedding_metadata_array (
    id INTEGER NOT NULL REFERENCES embeddings(id),
    key TEXT NOT NULL,
    string_value TEXT,
    int_value INTEGER,
    float_value REAL,
    bool_value INTEGER
);

CREATE INDEX IF NOT EXISTS embedding_metadata_array_id_key
    ON embedding_metadata_array (id, key);
CREATE INDEX IF NOT EXISTS embedding_metadata_array_key_string
    ON embedding_metadata_array (key, string_value) WHERE string_value IS NOT NULL;
CREATE INDEX IF NOT EXISTS embedding_metadata_array_key_int
    ON embedding_metadata_array (key, int_value) WHERE int_value IS NOT NULL;
CREATE INDEX IF NOT EXISTS embedding_metadata_array_key_float
    ON embedding_metadata_array (key, float_value) WHERE float_value IS NOT NULL;


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: 2f80102c-5acb-4b03-929e-3240b792c728
name: domain_aUV1tiexS3O4z6XBRlWA
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":"legacy"},"hnsw":{"ef_construction":100,"max_neighbors":16,"ef_search":100,"num_threads":4,"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":{"faq_id":{"string":{"fts_index":{"enabled":false,"config":{}},"string_inverted_index":{"enabled":true,"config":{}}}},"answer":{"string":{"fts_index":{"enabled":false,"config":{}},"string_inverted_index":{"enabled":true,"config":{}}}},"question":{"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":"legacy"},"source_key":"#document","hnsw":{"ef_construction":100,"max_neighbors":16,"ef_search":100,"num_threads":4,"batch_size":100,"sync_threshold":1000,"resize_factor":1.2}}}}},"#document":{"string":{"fts_index":{"enabled":true,"config":{}},"string_inverted_index":{"enabled":false,"config":{}}}}}}


id: c42125ce-142a-4c9a-80b8-dfba602047e0
name: domain_46NUpW6i80wIXsbtyc2C
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":"legacy"},"hnsw":{"ef_construction":100,"max_neighbors":16,"ef_search":100,"num_threads":4,"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","embedding_function":{"type":"legacy"},"source_key":"#document","hnsw":{"ef_construction":100,"max_neighbors":16,"ef_search":100,"num_threads":4,"batch_size":100,"sync_threshold":1000,"resize_factor":1.2}}}}}}}


id: 0efb29b4-6364-4274-82c1-1a524e03eb12
name: domain_8Rlkw3s80ZUgaC2Bb9m4
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":"legacy"},"hnsw":{"ef_construction":100,"max_neighbors":16,"ef_search":100,"num_threads":4,"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":{"answer":{"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":"legacy"},"source_key":"#document","hnsw":{"ef_construction":100,"max_neighbors":16,"ef_search":100,"num_threads":4,"batch_size":100,"sync_threshold":1000,"resize_factor":1.2}}}}},"#document":{"string":{"fts_index":{"enabled":true,"config":{}},"string_inverted_index":{"enabled":false,"config":{}}}},"faq_id":{"string":{"fts_index":{"enabled":false,"config":{}},"string_inverted_index":{"enabled":true,"config":{}}}},"question":{"string":{"fts_index":{"enabled":false,"config":{}},"string_inverted_index":{"enabled":true,"config":{}}}}}}


# embedding_metadata

key: chroma:document
string_value:
Question: What is your refund policy?
Answer: We offer refunds if you cancel 24 hours prior to the ride.


key: chroma:document
string_value:
Question: How early do i need to book my car?
Answer: The earlier the better. However, 12 hours would be recommended. If you are making a last minute booking, it is best to call us to confirm.


key: answer
string_value:
The earlier the better. However, 12 hours would be recommended. If you are making a last minute booking, it is best to call us to confirm.


key: chroma:document
string_value:
Question: How do i know if my reservation is confirmed?
Answer: Once booked you will receive an E-mail or text confirming your booking. You can also call our 24 hour control room and our controllers will be happy to let you know if your booking is confirmed.


key: answer
string_value:
Once booked you will receive an E-mail or text confirming your booking. You can also call our 24 hour control room and our controllers will be happy to let you know if your booking is confirmed.


key: chroma:document
string_value:
Question: What if the flight is delayed or early?
Answer: We will check the flight status on the day and meet you according.


# embeddings_queue

created_at: 2026-05-19 23:54:56
topic: persistent://default/default/2f80102c-5acb-4b03-929e-3240b792c728
id: 7SlmbxYtPE75zqcu7dee
encoding: FLOAT32
metadata:
{"question":"What is your refund policy?","chroma:document":"Question: What is your refund policy?\nAnswer: We offer refunds if you cancel 24 hours prior to the ride.","faq_id":"7SlmbxYtPE75zqcu7dee","answer":"We offer refunds if you cancel 24 hours prior to the ride."}


created_at: 2026-05-19 23:54:56
topic: persistent://default/default/2f80102c-5acb-4b03-929e-3240b792c728
id: JPAXWqd0ybeZ20FIwVE9
encoding: FLOAT32
metadata:
{"faq_id":"JPAXWqd0ybeZ20FIwVE9","chroma:document":"Question: What is your refund policy?\nAnswer: We offer refunds if you cancel 24 hours prior to the ride.","question":"What is your refund policy?","answer":"We offer refunds if you cancel 24 hours prior to the ride."}


created_at: 2026-05-19 23:54:56
topic: persistent://default/default/2f80102c-5acb-4b03-929e-3240b792c728
id: Ld6lullUzJ6jmprihX48
encoding: FLOAT32
metadata:
{"question":"How early do i need to book my car?","faq_id":"Ld6lullUzJ6jmprihX48","answer":"The earlier the better. However, 12 hours would be recommended. If you are making a last minute booking, it is best to call us to confirm.","chroma:document":"Question: How early do i need to book my car?\nAnswer: The earlier the better. However, 12 hours would be recommended. If you are making a last minute booking, it is best to call us to confirm."}


created_at: 2026-05-19 23:54:56
topic: persistent://default/default/2f80102c-5acb-4b03-929e-3240b792c728
id: aLPSzvwGJTiegxJeEG5a
encoding: FLOAT32
metadata:
{"answer":"We offer refunds if you cancel 24 hours prior to the ride.","faq_id":"aLPSzvwGJTiegxJeEG5a","question":"What is your refund policy?","chroma:document":"Question: What is your refund policy?\nAnswer: We offer refunds if you cancel 24 hours prior to the ride."}


created_at: 2026-05-19 23:54:56
topic: persistent://default/default/2f80102c-5acb-4b03-929e-3240b792c728
id: b0H3KlCxgWzQJdTjSM9o
encoding: FLOAT32
metadata:
{"faq_id":"b0H3KlCxgWzQJdTjSM9o","chroma:document":"Question: How do i know if my reservation is confirmed?\nAnswer: Once booked you will receive an E-mail or text confirming your booking. You can also call our 24 hour control room and our controllers will be happy to let you know if your booking is confirmed.","answer":"Once booked you will receive an E-mail or text confirming your booking. You can also call our 24 hour control room and our controllers will be happy to let you know if your booking is confirmed.","question":"How do i know if my reservation is confirmed?"}


created_at: 2026-05-19 23:54:56
topic: persistent://default/default/2f80102c-5acb-4b03-929e-3240b792c728
id: ePrqXQfGe5AkfXCouc13
encoding: FLOAT32
metadata:
{"faq_id":"ePrqXQfGe5AkfXCouc13","chroma:document":"Question: What if the flight is delayed or early?\nAnswer: We will check the flight status on the day and meet you according.","question":"What if the flight is delayed or early?","answer":"We will check the flight status on the day and meet you according."}


created_at: 2026-05-19 23:54:56
topic: persistent://default/default/2f80102c-5acb-4b03-929e-3240b792c728
id: uY9byf3fuQMGhGwknCHR
encoding: FLOAT32
metadata:
{"faq_id":"uY9byf3fuQMGhGwknCHR","answer":"We offer refunds if you cancel 24 hours prior to the ride.","question":"What is your refund policy?","chroma:document":"Question: What is your refund policy?\nAnswer: We offer refunds if you cancel 24 hours prior to the ride."}


created_at: 2026-05-20 03:02:29
topic: persistent://default/default/0efb29b4-6364-4274-82c1-1a524e03eb12
id: LcTQoCNpzgIEzzBq5X9Q
encoding: FLOAT32
metadata:
{"chroma:document":"Question: What are your business hours?\nAnswer: We are open Monday to Friday, 9 AM to 5 PM EST.","faq_id":"LcTQoCNpzgIEzzBq5X9Q","answer":"We are open Monday to Friday, 9 AM to 5 PM EST.","question":"What are your business hours?"}


created_at: 2026-05-20 03:02:29
topic: persistent://default/default/0efb29b4-6364-4274-82c1-1a524e03eb12
id: ZrsmX35QHgQbHIj1c7Lj
encoding: FLOAT32
metadata:
{"question":"How can I contact support?","faq_id":"ZrsmX35QHgQbHIj1c7Lj","chroma:document":"Question: How can I contact support?\nAnswer: You can reach us at support@example.com.","answer":"You can reach us at support@example.com."}


created_at: 2026-05-20 03:02:29
topic: persistent://default/default/0efb29b4-6364-4274-82c1-1a524e03eb12
id: cLvhEsoJX19xuz1RWZZo
encoding: FLOAT32
metadata:
{"faq_id":"cLvhEsoJX19xuz1RWZZo","answer":"Yes, we offer a 30-day money-back guarantee.","chroma:document":"Question: Do you offer refunds?\nAnswer: Yes, we offer a 30-day money-back guarantee.","question":"Do you offer refunds?"}


created_at: 2026-05-20 03:02:42
topic: persistent://default/default/0efb29b4-6364-4274-82c1-1a524e03eb12
id: 444c6f59-ebbf-4bda-957f-adab70c6a399
encoding: FLOAT32
metadata:
{"chroma:document":"hello","answer":"Hello! How can I assist you today?","question":"hello","faq_id":"444c6f59-ebbf-4bda-957f-adab70c6a399"}


created_at: 2026-05-20 03:02:53
topic: persistent://default/default/0efb29b4-6364-4274-82c1-1a524e03eb12
id: 00fd187a-fde9-4b3e-9334-435456066175
encoding: FLOAT32
metadata:
{"question":"support","answer":"You can reach us at support@example.com for assistance.","faq_id":"00fd187a-fde9-4b3e-9334-435456066175","chroma:document":"support"}


created_at: 2026-05-20 03:03:00
topic: persistent://default/default/0efb29b4-6364-4274-82c1-1a524e03eb12
id: ee675ef9-310f-4f05-b3b7-380bc840abff
encoding: FLOAT32
metadata:
{"answer":"I'm sorry, but I don't have that information. Please consider contacting support for assistance.","chroma:document":"airport","faq_id":"ee675ef9-310f-4f05-b3b7-380bc840abff","question":"airport"}


