D1 Database Schema for Session Index in Background Agents: Complete Reference

The D1 database schema for session index in Background Agents consists of two normalized tables—sessions and repo_metadata—augmented with composite indexes that optimize SQL queries for repository-scoped session management and metadata retrieval.

The ColeMurray/background-agents repository implements a Cloudflare D1 relational layer to replace the legacy KV-store session storage. The schema defined in terraform/d1/migrations/0002_create_session_index.sql establishes the foundation for persistent session tracking and repository metadata management.

Schema Structure and Tables

The migration creates a relational structure that links agent sessions to GitHub repositories while storing searchable metadata in JSON columns. This design enables complex queries that were impossible with the previous KV-store implementation.

The sessions Table

The sessions table stores one row per agent session with strict repository ownership and lifecycle tracking.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	Unique session identifier
title	TEXT		Optional human-readable session name
repo_owner	TEXT	NOT NULL	GitHub organization or user owner
repo_name	TEXT	NOT NULL	GitHub repository name
model	TEXT	NOT NULL DEFAULT 'claude-haiku-4-5'	LLM model identifier for the session
status	TEXT	NOT NULL DEFAULT 'created'	Lifecycle state (e.g., created, running, completed)
created_at	INTEGER	NOT NULL	Unix timestamp of creation
updated_at	INTEGER	NOT NULL	Unix timestamp of last modification

The repo_metadata Table

The repo_metadata table stores denormalized repository attributes to support batch queries and keyword filtering.

Column	Type	Constraints	Description
repo_owner	TEXT	NOT NULL	Part of composite primary key
repo_name	TEXT	NOT NULL	Part of composite primary key
description	TEXT		Repository description text
aliases	TEXT		JSON array of string aliases
channel_associations	TEXT		JSON array of associated channels
keywords	TEXT		JSON array of searchable keywords
created_at	INTEGER	NOT NULL	Unix timestamp
updated_at	INTEGER	NOT NULL	Unix timestamp

Performance Indexes

Two composite indexes accelerate the query patterns used by the control plane:

• idx_sessions_status_updated: Optimizes queries filtering by status ordered by updated_at DESC, enabling efficient retrieval of recent active sessions.
• idx_sessions_repo: Optimizes queries filtering by repo_owner and repo_name ordered by updated_at DESC, supporting the common use case of listing a repository's session history.

SQL Schema Definition

The complete data definition language resides in the migration file:

-- terraform/d1/migrations/0002_create_session_index.sql

CREATE TABLE IF NOT EXISTS sessions (
    id TEXT PRIMARY KEY,
    title TEXT,
    repo_owner TEXT NOT NULL,
    repo_name TEXT NOT NULL,
    model TEXT NOT NULL DEFAULT 'claude-haiku-4-5',
    status TEXT NOT NULL DEFAULT 'created',
    created_at INTEGER NOT NULL,
    updated_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS repo_metadata (
    repo_owner TEXT NOT NULL,
    repo_name TEXT NOT NULL,
    description TEXT,
    aliases TEXT,
    channel_associations TEXT,
    keywords TEXT,
    created_at INTEGER NOT NULL,
    updated_at INTEGER NOT NULL,
    PRIMARY KEY (repo_owner, repo_name)
);

CREATE INDEX IF NOT EXISTS idx_sessions_status_updated 
    ON sessions(status, updated_at);

CREATE INDEX IF NOT EXISTS idx_sessions_repo 
    ON sessions(repo_owner, repo_name, updated_at);

Querying the Session Index in TypeScript

The control plane interacts with these tables using Cloudflare D1's prepared statement API. The following patterns from packages/control-plane/src/d1/client.ts demonstrate production-grade access patterns.

Listing Sessions by Repository

Retrieve all sessions for a specific repository, ordered by most recent activity:

const listSessionsByRepo = async (
  db: D1Database, 
  owner: string, 
  name: string
) => {
  const stmt = db.prepare(`
    SELECT id, title, status, created_at, updated_at
    FROM sessions
    WHERE repo_owner = ? AND repo_name = ?
    ORDER BY updated_at DESC
  `);
  const { results } = await stmt.all(owner, name);
  return results;
};

Filtering by Status

Query active sessions across all repositories using the status index:

const getRunningSessions = async (db: D1Database) => {
  const stmt = db.prepare(`
    SELECT id, repo_owner, repo_name, created_at
    FROM sessions
    WHERE status = ?
    ORDER BY updated_at DESC
  `);
  const { results } = await stmt.all('running');
  return results;
};

Retrieving Repository Metadata

Fetch and parse the JSON metadata fields for repository context:

const getRepoMetadata = async (
  db: D1Database, 
  owner: string, 
  name: string
) => {
  const stmt = db.prepare(`
    SELECT description, aliases, channel_associations, keywords
    FROM repo_metadata
    WHERE repo_owner = ? AND repo_name = ?
  `);
  const row = await stmt.first(owner, name);
  
  return {
    ...row,
    aliases: JSON.parse(row.aliases ?? '[]'),
    channel_associations: JSON.parse(row.channel_associations ?? '[]'),
    keywords: JSON.parse(row.keywords ?? '[]'),
  };
};

Key Implementation Files

The following source files define and utilize the D1 session index schema:

File Path	Purpose
terraform/d1/migrations/0002_create_session_index.sql	DDL migration creating tables and indexes
packages/control-plane/src/d1/client.ts	TypeScript wrapper implementing db.prepare patterns for CRUD operations
packages/control-plane/test/integration/d1-session-index.test.ts	Integration tests verifying schema constraints, index usage, and query correctness
docs/GETTING_STARTED.md	Deployment documentation covering D1 provisioning and migration execution

Summary

• The D1 database schema in 0002_create_session_index.sql establishes two normalized tables: sessions for agent lifecycle tracking and repo_metadata for searchable repository attributes.
• Composite indexes on sessions optimize the two primary query patterns: status-based filtering and repository-scoped listings.
• JSON columns in repo_metadata store array data (aliases, keywords) requiring client-side parsing in TypeScript.
• The control plane uses parameterized prepared statements via Cloudflare D1's API to prevent SQL injection and ensure type safety.

Frequently Asked Questions

What is the primary key for the sessions table?

The sessions table uses a single-column primary key id TEXT that uniquely identifies each agent session. This UUID or string identifier is generated by the application layer when creating new sessions.

How does the schema handle repository metadata storage?

The repo_metadata table uses a composite primary key of (repo_owner, repo_name) to uniquely identify each repository. Metadata fields like aliases, channel_associations, and keywords are stored as JSON text arrays, which the TypeScript client parses after retrieval to reconstruct native arrays.

Why were these specific indexes created on the sessions table?

The idx_sessions_repo index accelerates queries that list sessions for a specific GitHub repository ordered by update time, while idx_sessions_status_updated supports global status checks (e.g., finding all "running" sessions) without performing full table scans. Both indexes place updated_at last to support ordering operations directly from the index.

Where is the D1 database schema defined in the source code?

The schema is defined in terraform/d1/migrations/0002_create_session_index.sql, which executes during the Terraform apply phase to create the tables and indexes. Runtime queries are implemented in packages/control-plane/src/d1/client.ts, with integration tests located in packages/control-plane/test/integration/d1-session-index.test.ts.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}