joelhooks D1 Patterns
D1 stores encrypted records. Content is ciphertext; indexes work on unencrypted metadata.
Schema
-- Core records table (encrypted content)
CREATE TABLE records (
id TEXT PRIMARY KEY, -- TID (timestamp-based ID)
did TEXT NOT NULL, -- Agent DID
collection TEXT NOT NULL, -- Lexicon type (agent.memory.note)
rkey TEXT NOT NULL, -- Record key within collection
ciphertext BLOB NOT NULL, -- Encrypted content
encrypted_dek BLOB, -- DEK encrypted for agent (NULL if public)
nonce BLOB NOT NULL, -- AES-GCM nonce
public INTEGER DEFAULT 0, -- 1 if plaintext
created_at TEXT NOT NULL,
updated_at TEXT,
UNIQUE(did, collection, rkey)
);
CREATE INDEX idx_records_did ON records(did);
CREATE INDEX idx_records_collection ON records(collection);
CREATE INDEX idx_records_did_collection ON records(did, collection);
CREATE INDEX idx_records_created ON records(created_at);
-- Shared records (re-encrypted DEKs for recipients)
CREATE TABLE shared_records (
id INTEGER PRIMARY KEY AUTOINCREMENT,
record_id TEXT NOT NULL,
recipient_did TEXT NOT NULL,
encrypted_dek BLOB NOT NULL, -- DEK encrypted for recipient
shared_at TEXT NOT NULL,
FOREIGN KEY (record_id) REFERENCES records(id),
UNIQUE(record_id, recipient_did)
);
CREATE INDEX idx_shared_recipient ON shared_records(recipient_did);
-- Agent registry (for multi-agent coordination)
CREATE TABLE agents (
did TEXT PRIMARY KEY,
public_key BLOB NOT NULL, -- X25519 public key
signing_key BLOB NOT NULL, -- Ed25519 public key
metadata TEXT, -- JSON metadata
created_at TEXT NOT NULL,
updated_at TEXT
);
-- Observability events
CREATE TABLE events (
id TEXT PRIMARY KEY, -- ULID
agent_did TEXT NOT NULL,
event_type TEXT NOT NULL,
outcome TEXT NOT NULL,
timestamp TEXT NOT NULL,
duration_ms INTEGER,
session_id TEXT,
trace_id TEXT,
span_id TEXT,
context TEXT, -- JSON
reasoning TEXT, -- JSON (decision traces)
error TEXT, -- JSON (error context)
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (agent_did) REFERENCES agents(did)
);
CREATE INDEX idx_events_agent ON events(agent_did);
CREATE INDEX idx_events_type ON events(event_type);
CREATE INDEX idx_events_timestamp ON events(timestamp);
CREATE INDEX idx_events_session ON events(session_id);
TID Generation
Timestamp-based IDs (compatible with AT Protocol):
function generateTid(): string {
const now = Date.now()
const timestamp = now.toString(36).padStart(10, '0')
const random = crypto.getRandomValues(new Uint8Array(4))
const suffix = Array.from(random).map(b => b.toString(36)).join('').slice(0, 4)
return `${timestamp}${suffix}`
}
CRUD Operations
Create Record
async function createRecord(
db: D1Database,
did: string,
collection: string,
encrypted: EncryptedRecord
): Promise<string> {
const rkey = generateTid()
const id = `${did}/${collection}/${rkey}`
await db.prepare(`
INSERT INTO records (id, did, collection, rkey, ciphertext, encrypted_dek, nonce, public, created_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
`).bind(
id,
did,
collection,
rkey,
encrypted.ciphertext,
encrypted.encryptedDek,
encrypted.nonce,
encrypted.public ? 1 : 0,
new Date().toISOString()
).run()
return id
}
Get Record
async function getRecord(
db: D1Database,
id: string
): Promise<EncryptedRecord | null> {
const row = await db.prepare(
'SELECT * FROM records WHERE id = ?'
).bind(id).first()
if (!row) return null
return {
id: row.id as string,
collection: row.collection as string,
ciphertext: row.ciphertext as Uint8Array,
encryptedDek: row.encrypted_dek as Uint8Array,
nonce: row.nonce as Uint8Array,
public: row.public === 1,
createdAt: row.created_at as string
}
}
List Records
async function listRecords(
db: D1Database,
did: string,
collection?: string,
options: { limit?: number; cursor?: string } = {}
): Promise<{ records: EncryptedRecord[]; cursor?: string }> {
const limit = options.limit || 50
let query = 'SELECT * FROM records WHERE did = ?'
const params: unknown[] = [did]
if (collection) {
query += ' AND collection = ?'
params.push(collection)
}
if (options.cursor) {
query += ' AND id > ?'
params.push(options.cursor)
}
query += ' ORDER BY id LIMIT ?'
params.push(limit + 1)
const rows = await db.prepare(query).bind(...params).all()
const hasMore = rows.results.length > limit
const records = rows.results.slice(0, limit).map(rowToRecord)
return {
records,
cursor: hasMore ? records[records.length - 1].id : undefined
}
}
Update Record
async function updateRecord(
db: D1Database,
id: string,
encrypted: Partial<EncryptedRecord>
): Promise<void> {
const sets: string[] = []
const params: unknown[] = []
if (encrypted.ciphertext) {
sets.push('ciphertext = ?')
params.push(encrypted.ciphertext)
}
if (encrypted.encryptedDek) {
sets.push('encrypted_dek = ?')
params.push(encrypted.encryptedDek)
}
if (encrypted.nonce) {
sets.push('nonce = ?')
params.push(encrypted.nonce)
}
if (encrypted.public !== undefined) {
sets.push('public = ?')
params.push(encrypted.public ? 1 : 0)
}
sets.push('updated_at = ?')
params.push(new Date().toISOString())
params.push(id)
await db.prepare(
`UPDATE records SET ${sets.join(', ')} WHERE id = ?`
).bind(...params).run()
}
Delete Record
async function deleteRecord(db: D1Database, id: string): Promise<void> {
await db.batch([
db.prepare('DELETE FROM shared_records WHERE record_id = ?').bind(id),
db.prepare('DELETE FROM records WHERE id = ?').bind(id)
])
}
Batch Operations
D1 supports batching for efficiency:
async function batchInsert(
db: D1Database,
records: Array<{ did: string; collection: string; encrypted: EncryptedRecord }>
): Promise<string[]> {
const statements = records.map(r => {
const rkey = generateTid()
const id = `${r.did}/${r.collection}/${rkey}`
return {
id,
stmt: db.prepare(`
INSERT INTO records (id, did, collection, rkey, ciphertext, encrypted_dek, nonce, public, created_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
`).bind(
id, r.did, r.collection, rkey,
r.encrypted.ciphertext, r.encrypted.encryptedDek, r.encrypted.nonce,
r.encrypted.public ? 1 : 0, new Date().toISOString()
)
}
})
await db.batch(statements.map(s => s.stmt))
return statements.map(s => s.id)
}
Wrangler Configuration
[[d1_databases]]
binding = "DB"
database_name = "agent-records"
database_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
Local Development
# Create local database
wrangler d1 create agent-records --local
# Apply schema
wrangler d1 execute agent-records --local --file=schema.sql
# Query
wrangler d1 execute agent-records --local --command="SELECT COUNT(*) FROM records"