Session SQL Database Management

Why This is Copilot-Exclusive

Copilot CLI includes a built-in SQLite database per session accessible via a native sql tool. It comes with pre-built todos and todo_deps tables and supports creating any custom tables you need. This structured data layer enables proper task tracking, batch processing, and state management — all queryable with SQL. Claude Code has no database; state management is limited to reading/writing files or keeping everything in the conversation context.

When to Use

• Tracking progress on multi-step tasks with dependencies
• Batch-processing items (issues, files, test cases) with status tracking
• Storing intermediate results from analysis or exploration
• Managing key-value state across a complex workflow
• Building reports from structured data (test results, PR reviews, audit findings)

Workflow

1. Pre-Built Todo Tracking

The todos table is ready to use immediately:

-- Create todos for your task
INSERT INTO todos (id, title, description, status) VALUES
  ('setup-db', 'Set up database', 'Initialize Prisma schema with User and Post models', 'pending'),
  ('seed-data', 'Create seed data', 'Add development seed script with 10 users', 'pending'),
  ('api-routes', 'Build API routes', 'REST endpoints for User CRUD operations', 'pending');

-- Define dependencies
INSERT INTO todo_deps (todo_id, depends_on) VALUES
  ('seed-data', 'setup-db'),
  ('api-routes', 'setup-db');

2. Track Progress as You Work

-- Start working on a todo
UPDATE todos SET status = 'in_progress', updated_at = datetime('now') WHERE id = 'setup-db';

-- Complete it
UPDATE todos SET status = 'done', updated_at = datetime('now') WHERE id = 'setup-db';

-- Check what's ready to start next
SELECT t.id, t.title FROM todos t
WHERE t.status = 'pending'
AND NOT EXISTS (
  SELECT 1 FROM todo_deps td
  JOIN todos dep ON td.depends_on = dep.id
  WHERE td.todo_id = t.id AND dep.status != 'done'
);

3. Custom Tables for Any Purpose

Test Case Tracking

CREATE TABLE test_cases (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  file_path TEXT,
  status TEXT DEFAULT 'not_written',
  notes TEXT
);

INSERT INTO test_cases (id, name, file_path) VALUES
  ('tc-login', 'User login flow', 'tests/auth/login.test.ts'),
  ('tc-register', 'User registration', 'tests/auth/register.test.ts'),
  ('tc-logout', 'User logout', 'tests/auth/logout.test.ts');

-- Track TDD progress
UPDATE test_cases SET status = 'written' WHERE id = 'tc-login';
SELECT * FROM test_cases WHERE status = 'not_written';

Batch Processing

CREATE TABLE files_to_process (
  path TEXT PRIMARY KEY,
  action TEXT,
  status TEXT DEFAULT 'pending',
  result TEXT
);

-- Load files to process
INSERT INTO files_to_process (path, action) VALUES
  ('src/utils/string.ts', 'add-jsdoc'),
  ('src/utils/array.ts', 'add-jsdoc'),
  ('src/utils/date.ts', 'add-jsdoc');

-- Process one at a time
SELECT path, action FROM files_to_process WHERE status = 'pending' LIMIT 1;
UPDATE files_to_process SET status = 'done', result = 'Added 5 JSDoc comments' WHERE path = 'src/utils/string.ts';

Key-Value State

CREATE TABLE session_state (key TEXT PRIMARY KEY, value TEXT);

INSERT OR REPLACE INTO session_state (key, value) VALUES
  ('current_phase', 'testing'),
  ('target_branch', 'feature/auth'),
  ('last_test_run', '2024-01-15T10:30:00Z');

SELECT value FROM session_state WHERE key = 'current_phase';

Examples

Code Review Tracker

CREATE TABLE review_items (
  id TEXT PRIMARY KEY,
  file_path TEXT,
  line_number INTEGER,
  severity TEXT,  -- 'critical', 'warning', 'suggestion'
  comment TEXT,
  status TEXT DEFAULT 'pending'
);

-- Copilot populates this during code review
INSERT INTO review_items (id, file_path, line_number, severity, comment) VALUES
  ('r1', 'src/auth.ts', 42, 'critical', 'SQL injection vulnerability in query'),
  ('r2', 'src/api.ts', 15, 'warning', 'Missing error handling for null case'),
  ('r3', 'src/utils.ts', 88, 'suggestion', 'Could use optional chaining here');

-- Work through issues by severity
SELECT * FROM review_items WHERE severity = 'critical' AND status = 'pending';
UPDATE review_items SET status = 'fixed' WHERE id = 'r1';

-- Progress dashboard
SELECT severity, status, COUNT(*) as count
FROM review_items
GROUP BY severity, status;

Migration Tracking

CREATE TABLE migration_files (
  source_path TEXT PRIMARY KEY,
  target_path TEXT,
  migration_type TEXT,
  status TEXT DEFAULT 'pending',
  errors TEXT
);

-- Track a JS → TS migration
INSERT INTO migration_files (source_path, target_path, migration_type) VALUES
  ('src/users.js', 'src/users.ts', 'js-to-ts'),
  ('src/orders.js', 'src/orders.ts', 'js-to-ts');

-- Summary
SELECT status, COUNT(*) FROM migration_files GROUP BY status;

Tips

• Use the pre-built tables: todos and todo_deps are ready immediately — no setup needed for basic task tracking.
• Descriptive IDs: Use kebab-case IDs like setup-db instead of t1 — they're self-documenting in query results.
• Query for dashboards: GROUP BY status gives instant progress summaries.
• Cross-reference tables: Join your custom tables with todos for rich tracking (e.g., link test cases to feature todos).
• The database resets per session: Data doesn't persist across sessions. For persistent data, write results to files before ending your session.
• SQL is your reporting engine: Generate summaries, find outliers, and track patterns that would be impossible to manage in plain text.