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

> Explore the D1 database schema for session index in background agents. Understand the normalized tables and composite indexes for efficient repository-scoped session management and metadata retrieval.

- Repository: [Cole Murray/background-agents](https://github.com/ColeMurray/background-agents)
- Tags: api-reference
- Published: 2026-07-13

---

**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`](https://github.com/ColeMurray/background-agents/blob/main/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:

```sql
-- 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`](https://github.com/ColeMurray/background-agents/blob/main/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:

```typescript
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:

```typescript
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:

```typescript
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`](https://github.com/ColeMurray/background-agents/blob/main/terraform/d1/migrations/0002_create_session_index.sql) | **DDL migration** creating tables and indexes |
| [`packages/control-plane/src/d1/client.ts`](https://github.com/ColeMurray/background-agents/blob/main/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`](https://github.com/ColeMurray/background-agents/blob/main/packages/control-plane/test/integration/d1-session-index.test.ts) | **Integration tests** verifying schema constraints, index usage, and query correctness |
| [`docs/GETTING_STARTED.md`](https://github.com/ColeMurray/background-agents/blob/main/docs/GETTING_STARTED.md) | Deployment documentation covering D1 provisioning and migration execution |

## Summary

- The **D1 database schema** in [`0002_create_session_index.sql`](https://github.com/ColeMurray/background-agents/blob/main/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`](https://github.com/ColeMurray/background-agents/blob/main/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`](https://github.com/ColeMurray/background-agents/blob/main/packages/control-plane/src/d1/client.ts)**, with integration tests located in **[`packages/control-plane/test/integration/d1-session-index.test.ts`](https://github.com/ColeMurray/background-agents/blob/main/packages/control-plane/test/integration/d1-session-index.test.ts)**.