Peeking inside GitHub Copilot’s session database

When you work with GitHub Copilot AI, the .copilot folder under your user profile stores information about your chat sessions. I blogged earlier about the plan.md file in the same folder.

This post is about the SQLite database named session.db inside this same session folder. Now, the structure of this database is an internal implementation detail, and will surely change. But, it's good know what's happening inside the database. For instance, any plan you create gets stored in this database as entries in the todos table.

Here is the SQL structure of the database. At present, it has three tables only:

CREATE TABLE inbox_entries (
   id TEXT PRIMARY KEY,
   recipient_session_id TEXT NOT NULL,
   sender_id TEXT NOT NULL,
   sender_name TEXT NOT NULL,
   sender_type TEXT NOT NULL,
   interaction_id TEXT NOT NULL,
   sequence INTEGER NOT NULL DEFAULT 0,
   summary TEXT NOT NULL,
   content TEXT NOT NULL,
   unread INTEGER NOT NULL DEFAULT 1,
   sent_at INTEGER NOT NULL,
   read_at INTEGER,
   notified_at INTEGER
)
CREATE TABLE todo_deps (
   todo_id TEXT NOT NULL,
   depends_on TEXT NOT NULL,
   PRIMARY KEY (todo_id, depends_on),
   FOREIGN KEY (todo_id) REFERENCES todos(id),
   FOREIGN KEY (depends_on) REFERENCES todos(id)
)
CREATE TABLE todos (
   id TEXT PRIMARY KEY,
   title TEXT NOT NULL,
   description TEXT,
   status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'in_progress', 'done', 'blocked')),
   created_at TEXT DEFAULT (datetime('now')),
   updated_at TEXT DEFAULT (datetime('now'))
)

From here, we can see that the todos table is the main one. It's easy to write C# code to access this database with the Microsoft.Data.Sqlite NuGet package.

Happy hacking!