Database

Uranus uses Cloudflare D1, a serverless SQLite database running at the edge.

Overview

D1 provides:

• SQLite compatibility - Standard SQL syntax
• Edge deployment - Low latency globally
• Automatic replication - Built-in durability
• Zero configuration - Managed by Cloudflare

Schema

Core Tables

agents

CREATE TABLE agents (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)))),
  name TEXT NOT NULL UNIQUE,
  description TEXT,
  status TEXT DEFAULT 'active',
  owner_email TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

agent_settings

CREATE TABLE agent_settings (
  id INTEGER PRIMARY KEY,
  agent_id TEXT NOT NULL UNIQUE REFERENCES agents(id),

  -- LLM Configuration
  llm_provider TEXT DEFAULT 'workers-ai',
  llm_model TEXT DEFAULT '@cf/meta/llama-3.1-8b-instruct',
  llm_temperature REAL DEFAULT 0.7,
  llm_max_tokens INTEGER DEFAULT 2048,
  llm_system_prompt TEXT,

  -- Calendar Settings
  calendar_timezone TEXT DEFAULT 'UTC',
  calendar_working_hours_start TEXT DEFAULT '09:00',
  calendar_working_hours_end TEXT DEFAULT '17:00',
  calendar_default_duration INTEGER DEFAULT 30,

  -- Workflow Settings
  workflow_max_concurrent INTEGER DEFAULT 5,
  workflow_default_timeout INTEGER DEFAULT 300,
  workflow_retry_attempts INTEGER DEFAULT 3,
  workflow_retry_delay INTEGER DEFAULT 60,

  -- Browser Settings
  browser_headless INTEGER DEFAULT 1,
  browser_viewport_width INTEGER DEFAULT 1280,
  browser_viewport_height INTEGER DEFAULT 720,
  browser_user_agent TEXT,
  browser_default_timeout INTEGER DEFAULT 30000,

  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

agent_admins

CREATE TABLE agent_admins (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  agent_id TEXT NOT NULL REFERENCES agents(id),
  email TEXT NOT NULL,
  role TEXT DEFAULT 'admin',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(agent_id, email)
);

Workflow Tables

workflows

CREATE TABLE workflows (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)))),
  agent_id TEXT NOT NULL REFERENCES agents(id),
  name TEXT NOT NULL,
  description TEXT,
  nodes TEXT DEFAULT '[]',      -- JSON array of nodes
  edges TEXT DEFAULT '[]',      -- JSON array of edges
  status TEXT DEFAULT 'draft',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

workflow_executions

CREATE TABLE workflow_executions (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)))),
  workflow_id TEXT NOT NULL REFERENCES workflows(id),
  agent_id TEXT NOT NULL REFERENCES agents(id),
  status TEXT DEFAULT 'queued',
  input TEXT,                   -- JSON input data
  output TEXT,                  -- JSON output data
  error TEXT,
  started_at DATETIME,
  completed_at DATETIME,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Scheduling Tables

schedules

CREATE TABLE schedules (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)))),
  agent_id TEXT NOT NULL REFERENCES agents(id),
  name TEXT NOT NULL,
  type TEXT NOT NULL,           -- 'once', 'delayed', 'cron'
  cron_expression TEXT,
  delay_seconds INTEGER,
  next_run DATETIME,
  last_run DATETIME,
  callback_type TEXT NOT NULL,  -- 'workflow', 'function'
  callback_id TEXT NOT NULL,
  status TEXT DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_schedules_status ON schedules(status);
CREATE INDEX idx_schedules_next_run ON schedules(next_run);

calendar_tasks

CREATE TABLE calendar_tasks (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)))),
  agent_id TEXT NOT NULL REFERENCES agents(id),
  title TEXT NOT NULL,
  description TEXT,
  start_time DATETIME NOT NULL,
  end_time DATETIME NOT NULL,
  recurrence TEXT,              -- 'none', 'daily', 'weekly', 'monthly'
  recurrence_days TEXT,         -- JSON array of day numbers
  workflow_id TEXT REFERENCES workflows(id),
  status TEXT DEFAULT 'pending',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_calendar_start ON calendar_tasks(start_time);

Chat Tables

chat_messages

CREATE TABLE chat_messages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  agent_id TEXT NOT NULL REFERENCES agents(id),
  conversation_id TEXT,
  role TEXT NOT NULL,           -- 'user', 'assistant', 'system', 'tool'
  content TEXT NOT NULL,
  tool_calls TEXT,              -- JSON array
  tool_call_id TEXT,
  metadata TEXT,                -- JSON object
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_messages_created ON chat_messages(created_at);

Contact Tables

contacts

CREATE TABLE contacts (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)))),
  agent_id TEXT NOT NULL REFERENCES agents(id),
  name TEXT NOT NULL,
  email TEXT,
  phone TEXT,
  telegram TEXT,
  whatsapp TEXT,
  twitter TEXT,
  external_id TEXT,
  notes TEXT,
  tags TEXT,                    -- JSON array
  metadata TEXT,                -- JSON object
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_contacts_email ON contacts(email);
CREATE INDEX idx_contacts_phone ON contacts(phone);
CREATE INDEX idx_contacts_telegram ON contacts(telegram);

Data Source Tables

data_sources

CREATE TABLE data_sources (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)))),
  agent_id TEXT NOT NULL REFERENCES agents(id),
  type TEXT NOT NULL,           -- 'file', 'url', 'database', 'api'
  name TEXT NOT NULL,
  url TEXT,
  file_path TEXT,
  config TEXT,                  -- JSON config
  status TEXT DEFAULT 'ready',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

data_chunks

CREATE TABLE data_chunks (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)))),
  source_id TEXT NOT NULL REFERENCES data_sources(id),
  content TEXT NOT NULL,
  embedding_id TEXT,            -- Vectorize ID
  metadata TEXT,                -- JSON metadata
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Migrations

Directory Structure

migrations/
├── 002_multi_agent.sql
├── 003_agent_auth.sql
├── 004_workflow_executions.sql
├── 005_cloudflare_settings.sql
├── 006_browser_actions_types.sql
└── 007_puppeteer_action_types.sql

Applying Migrations

Local Development:

npx wrangler d1 execute agents-dashboard --local --file=./migrations/002_multi_agent.sql

Production:

npx wrangler d1 execute agents-dashboard --file=./migrations/002_multi_agent.sql

Creating New Migrations

1. Create a new file: migrations/008_new_feature.sql
2. Write migration SQL:

-- Add new column
ALTER TABLE agents ADD COLUMN new_field TEXT;

-- Create new table
CREATE TABLE IF NOT EXISTS new_table (
  id TEXT PRIMARY KEY,
  data TEXT
);

-- Create index
CREATE INDEX IF NOT EXISTS idx_new_table_data ON new_table(data);

Query Patterns

Agent-Scoped Queries

All queries filter by agent_id:

const workflows = await db
  .prepare('SELECT * FROM workflows WHERE agent_id = ?')
  .bind(agentId)
  .all()

Parameterized Queries

Always use parameterized queries:

// Good
const result = await db
  .prepare('SELECT * FROM contacts WHERE email = ?')
  .bind(email)
  .first()

// Bad - SQL injection risk
const result = await db
  .prepare(`SELECT * FROM contacts WHERE email = '${email}'`)
  .first()

JSON Columns

Handle JSON columns properly:

// Writing JSON
await db
  .prepare('INSERT INTO workflows (nodes, edges) VALUES (?, ?)')
  .bind(JSON.stringify(nodes), JSON.stringify(edges))
  .run()

// Reading JSON
const workflow = await db
  .prepare('SELECT * FROM workflows WHERE id = ?')
  .bind(id)
  .first()

const nodes = JSON.parse(workflow.nodes || '[]')
const edges = JSON.parse(workflow.edges || '[]')

SQL Interface

The dashboard includes a SQL query editor:

Features

• Monaco editor with SQL syntax highlighting
• Schema browser
• Query execution with timing
• Result display
• Export capabilities

Allowed Operations

Operation	Allowed
SELECT	Yes
INSERT	Yes
UPDATE	Yes
DELETE	Yes
PRAGMA	Yes (read-only)
EXPLAIN	Yes
CREATE	No
DROP	No
ALTER	No

API Usage

# Get schema
curl https://your-domain.com/api/agents/{id}/sql/schema

# Execute query
curl -X POST https://your-domain.com/api/agents/{id}/sql/execute \
  -d '{"query": "SELECT * FROM workflows LIMIT 10"}'

Best Practices

1. Use Indexes

Create indexes for frequently queried columns:

CREATE INDEX idx_schedules_status ON schedules(status);
CREATE INDEX idx_schedules_next_run ON schedules(next_run);

2. Optimize Queries

• Select only needed columns
• Use LIMIT for large results
• Use proper WHERE clauses

3. Handle Nulls

Check for NULL values:

SELECT * FROM contacts
WHERE email IS NOT NULL
  AND email != ''

4. Use Transactions

For multi-statement operations:

await db.batch([
  db.prepare('INSERT INTO ...').bind(...),
  db.prepare('UPDATE ...').bind(...),
])

5. Monitor Performance

Check query performance:

const start = Date.now()
const result = await db.prepare(query).all()
const duration = Date.now() - start
console.log(`Query took ${duration}ms`)