"""Initial schema — captures existing brain database structure.
This migration extracts all table definitions, indexes, triggers and FTS tables
from storage.py into a versioned migration. Future changes should be new
migration files (002_, 003_, ...) rather than editing the tables inline.
Run order: first and only migration until someone creates 002_*.
"""
def upgrade(conn):
"""Apply this migration — create all base tables and triggers."""
# -- Events ----------------------------------------------------------------
conn.execute("""
CREATE TABLE IF NOT EXISTS events (
id TEXT PRIMARY KEY,
timestamp TEXT NOT NULL,
session_id TEXT NOT NULL,
event_type TEXT NOT NULL,
tool TEXT,
tags TEXT,
context_json TEXT,
file_paths TEXT,
entity_id TEXT REFERENCES entities(entity_id),
searchable_text TEXT
)
""")
conn.execute("CREATE INDEX IF NOT EXISTS idx_timestamp ON events(timestamp)")
conn.execute("CREATE INDEX IF NOT EXISTS idx_session ON events(session_id)")
conn.execute("CREATE INDEX IF NOT EXISTS idx_type ON events(event_type)")
conn.execute("CREATE INDEX IF NOT EXISTS idx_events_entity ON events(entity_id)")
# -- Entities --------------------------------------------------------------
conn.execute("""
CREATE TABLE IF NOT EXISTS entities (
entity_id TEXT PRIMARY KEY,
type TEXT NOT NULL,
category TEXT NOT NULL,
pinned INTEGER NOT NULL DEFAULT 0,
strength TEXT DEFAULT 'moderate',
content TEXT NOT NULL,
metadata TEXT DEFAULT '{}',
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
access_count INTEGER NOT NULL DEFAULT 0,
deleted INTEGER NOT NULL DEFAULT 0
)
""")
conn.execute("CREATE INDEX IF NOT EXISTS idx_entities_category ON entities(category)")
conn.execute("CREATE INDEX IF NOT EXISTS idx_entities_pinned ON entities(pinned)")
conn.execute("CREATE INDEX IF NOT EXISTS idx_entities_type ON entities(type)")
conn.execute("CREATE INDEX IF NOT EXISTS idx_entities_strength ON entities(strength)")
# -- Edges -----------------------------------------------------------------
conn.execute("""
CREATE TABLE IF NOT EXISTS edges (
entity1 TEXT NOT NULL,
relation TEXT NOT NULL,
entity2 TEXT NOT NULL,
weight REAL NOT NULL DEFAULT 0.5,
deleted INTEGER NOT NULL DEFAULT 0,
created_at TEXT NOT NULL DEFAULT (datetime('now')),
PRIMARY KEY (entity1, relation, entity2),
FOREIGN KEY (entity1) REFERENCES entities(entity_id),
FOREIGN KEY (entity2) REFERENCES entities(entity_id)
)
""")
conn.execute("CREATE INDEX IF NOT EXISTS idx_edges_e1 ON edges(entity1)")
conn.execute("CREATE INDEX IF NOT EXISTS idx_edges_e2 ON edges(entity2)")
conn.execute("CREATE INDEX IF NOT EXISTS idx_edges_rel ON edges(relation)")
# -- Entity FTS5 -----------------------------------------------------------
_create_entity_fts(conn)
# -- Events FTS5 -----------------------------------------------------------
_create_events_fts(conn)
# -- Entity embeddings (from embeddings.py) --------------------------------
conn.execute("""
CREATE TABLE IF NOT EXISTS entity_embeddings (
entity_id TEXT PRIMARY KEY,
embedding TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT (datetime('now')),
FOREIGN KEY (entity_id) REFERENCES entities(entity_id)
)
""")
# -- Schema versions tracker -----------------------------------------------
conn.execute("""
CREATE TABLE IF NOT EXISTS schema_versions (
version TEXT PRIMARY KEY,
applied_at TEXT NOT NULL DEFAULT (datetime('now'))
)
""")
conn.commit()
def _create_entity_fts(conn):
"""Entity FTS5 with contraction preprocessing (identical to storage.py)."""
# Drop old triggers/tables so we can recreate
conn.execute("DROP TABLE IF EXISTS entity_fts")
for t in ("entity_fts_insert", "entity_fts_update_delete",
"entity_fts_update_undelete", "entity_fts_update_normal",
"entity_fts_delete"):
conn.execute(f"DROP TRIGGER IF EXISTS {t}")
conn.execute("""
CREATE VIRTUAL TABLE entity_fts USING fts5(entity_id, content_pp, category, type)
""")
# Build preprocessing expressions (contraction expansion + lowercase)
# SQLite requires single quotes escaped as double single quotes (''')
contractions = [
("won''t", "will not"), ("can''t", "cannot"), ("don''t", "do not"),
("doesn''t", "does not"), ("isn''t", "is not"), ("aren''t", "are not"),
("wasn''t", "was not"), ("weren''t", "were not"), ("haven''t", "have not"),
("hasn''t", "has not"), ("hadn''t", "had not"), ("wouldn''t", "would not"),
("couldn''t", "could not"), ("shouldn''t", "should not"), ("mustn''t", "must not"),
("i''m", "i am"), ("you''re", "you are"), ("he''s", "he is"), ("she''s", "she is"),
("it''s", "it is"), ("we''re", "we are"), ("they''re", "they are"),
("i''ve", "i have"), ("you''ve", "you have"), ("we''ve", "we have"), ("they''ve", "they have"),
("i''ll", "i will"), ("you''ll", "you will"), ("he''ll", "he will"), ("she''ll", "she will"),
("we''ll", "we will"), ("they''ll", "they will"),
("i''d", "i would"), ("you''d", "you would"), ("he''d", "he would"), ("she''d", "she would"),
("we''d", "we would"), ("they''d", "they would"),
("that''s", "that is"), ("there''s", "there is"), ("who''s", "who is"), ("what''s", "what is"),
("didn''t", "did not"), ("let''s", "let us"), ("here''s", "here is"),
("shan''t", "shall not"), ("need''t", "need not"),
]
pp = "NEW.content"
op = "OLD.content"
for old, new in contractions:
pp = f"REPLACE({pp}, '{old}', '{new}')"
op = f"REPLACE({op}, '{old}', '{new}')"
pp = f"LOWER({pp})"
op = f"LOWER({op})"
conn.execute(f"""
CREATE TRIGGER entity_fts_insert AFTER INSERT ON entities
BEGIN
INSERT OR REPLACE INTO entity_fts(entity_id, content_pp, category, type)
VALUES (NEW.entity_id, {pp}, LOWER(NEW.category), LOWER(NEW.type));
END
""")
conn.execute("""
CREATE TRIGGER entity_fts_update_delete AFTER UPDATE ON entities
WHEN OLD.deleted = 0 AND NEW.deleted = 1
BEGIN
DELETE FROM entity_fts WHERE entity_id = NEW.entity_id;
END
""")
conn.execute(f"""
CREATE TRIGGER entity_fts_update_undelete AFTER UPDATE ON entities
WHEN OLD.deleted = 1 AND NEW.deleted = 0
BEGIN
INSERT INTO entity_fts(entity_id, content_pp, category, type)
VALUES (NEW.entity_id, {pp}, LOWER(NEW.category), LOWER(NEW.type));
END
""")
conn.execute(f"""
CREATE TRIGGER entity_fts_update_normal AFTER UPDATE ON entities
WHEN OLD.deleted = 0 AND NEW.deleted = 0
BEGIN
UPDATE entity_fts SET content_pp = {pp}, category = LOWER(NEW.category), type = LOWER(NEW.type)
WHERE entity_id = NEW.entity_id;
END
""")
conn.execute("""
CREATE TRIGGER entity_fts_delete AFTER DELETE ON entities
BEGIN
DELETE FROM entity_fts WHERE entity_id = OLD.entity_id;
END
""")
# Purge stale rows
conn.execute(
"DELETE FROM entity_fts WHERE entity_id IN (SELECT entity_id FROM entities WHERE deleted = 1)"
)
def _create_events_fts(conn):
"""Events FTS5 with Porter stemming (identical to storage.py)."""
conn.execute("DROP TABLE IF EXISTS events_fts")
for t in ("events_ai", "events_ad", "events_au"):
conn.execute(f"DROP TRIGGER IF EXISTS {t}")
conn.execute("""
CREATE VIRTUAL TABLE events_fts USING fts5(
id UNINDEXED, search_text, entity_id UNINDEXED, tokenize=porter
)
""")
search_expr = (
"new.event_type || ' ' || coalesce(new.tool, '') || ' ' || "
"coalesce(new.tags, '') || ' ' || coalesce(new.context_json, '') || ' ' || "
"coalesce(new.file_paths, '') || ' ' || coalesce(new.searchable_text, '') || ' ' || "
"coalesce(new.entity_id, '')"
)
conn.execute(f"""
CREATE TRIGGER events_ai AFTER INSERT ON events BEGIN
INSERT OR REPLACE INTO events_fts(id, search_text, entity_id)
VALUES (new.id, {search_expr}, new.entity_id);
END
""")
conn.execute("""
CREATE TRIGGER events_ad AFTER DELETE ON events BEGIN
DELETE FROM events_fts WHERE id = old.id;
END
""")
conn.execute(f"""
CREATE TRIGGER events_au AFTER UPDATE ON events BEGIN
INSERT OR REPLACE INTO events_fts(id, search_text, entity_id)
VALUES (new.id, {search_expr}, new.entity_id);
END
""")
# Backfill existing events
select_expr = (
"event_type || ' ' || coalesce(tool, '') || ' ' || "
"coalesce(tags, '') || ' ' || coalesce(context_json, '') || ' ' || "
"coalesce(file_paths, '') || ' ' || coalesce(searchable_text, '') || ' ' || "
"coalesce(entity_id, '')"
)
conn.execute(f"""
INSERT OR IGNORE INTO events_fts(id, search_text, entity_id)
SELECT id, {select_expr}, entity_id FROM events
WHERE id NOT IN (SELECT id FROM events_fts)
""")