gitfor/platform

-- Code search: one row per indexed default-branch file, mirrored into an FTS5 trigram index by rowid.
CREATE TABLE code_files (
  id INTEGER PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  path TEXT NOT NULL,
  blob_sha TEXT NOT NULL,
  UNIQUE (repo_id, path)
);

CREATE VIRTUAL TABLE code_fts USING fts5(path, content, tokenize = 'trigram');

CREATE TRIGGER code_files_delete AFTER DELETE ON code_files BEGIN
  DELETE FROM code_fts WHERE rowid = old.id;
END;

CREATE TABLE code_index_state (
  repo_id TEXT PRIMARY KEY REFERENCES repos(id) ON DELETE CASCADE,
  commit_sha TEXT NOT NULL,
  tree_sha TEXT NOT NULL,
  file_count INTEGER NOT NULL DEFAULT 0,
  truncated INTEGER NOT NULL DEFAULT 0,
  updated_at INTEGER NOT NULL
);