-- Memory MCP Knowledge Graph Schema for Cloudflare D1
-- Entities table: Projects, People, Companies, Tools, etc.
CREATE TABLE IF NOT EXISTS entities (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
entity_type TEXT NOT NULL,
created_at TEXT DEFAULT (datetime('now')),
updated_at TEXT DEFAULT (datetime('now'))
);
-- Observations: Notes and learnings attached to entities
CREATE TABLE IF NOT EXISTS observations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
entity_id INTEGER NOT NULL,
content TEXT NOT NULL,
created_at TEXT DEFAULT (datetime('now')),
FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE
);
-- Relations: Links between entities
CREATE TABLE IF NOT EXISTS relations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
from_entity_id INTEGER NOT NULL,
to_entity_id INTEGER NOT NULL,
relation_type TEXT NOT NULL,
created_at TEXT DEFAULT (datetime('now')),
FOREIGN KEY (from_entity_id) REFERENCES entities(id) ON DELETE CASCADE,
FOREIGN KEY (to_entity_id) REFERENCES entities(id) ON DELETE CASCADE,
UNIQUE(from_entity_id, to_entity_id, relation_type)
);
-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_entities_name ON entities(name);
CREATE INDEX IF NOT EXISTS idx_entities_type ON entities(entity_type);
CREATE INDEX IF NOT EXISTS idx_observations_entity ON observations(entity_id);
CREATE INDEX IF NOT EXISTS idx_relations_from ON relations(from_entity_id);
CREATE INDEX IF NOT EXISTS idx_relations_to ON relations(to_entity_id);
-- Full-text search on observations
CREATE VIRTUAL TABLE IF NOT EXISTS observations_fts USING fts5(
content,
content=observations,
content_rowid=id
);
-- Triggers to keep FTS in sync
CREATE TRIGGER IF NOT EXISTS observations_ai AFTER INSERT ON observations BEGIN
INSERT INTO observations_fts(rowid, content) VALUES (new.id, new.content);
END;
CREATE TRIGGER IF NOT EXISTS observations_ad AFTER DELETE ON observations BEGIN
INSERT INTO observations_fts(observations_fts, rowid, content) VALUES('delete', old.id, old.content);
END;
CREATE TRIGGER IF NOT EXISTS observations_au AFTER UPDATE ON observations BEGIN
INSERT INTO observations_fts(observations_fts, rowid, content) VALUES('delete', old.id, old.content);
INSERT INTO observations_fts(rowid, content) VALUES (new.id, new.content);
END;
