Database Schema & Migrations
Relevant source files
The following files were used as context for generating this wiki page:
e-zer0 uses Cloudflare D1, a serverless SQL database based on SQLite, to store application state, user credentials, agent configurations, and audit logs. The system manages data integrity through structured SQL migrations and provides an abstraction layer for AI agents to interact with email accounts securely.
Sources: README.md, wrangler.toml:13-17
Database Architecture
The database architecture centers on the Cloudflare D1 binding named DB. It stores core entities including administrative users, connected email accounts, and Model Context Protocol (MCP) client configurations.
Entity Relationship Overview
The following diagram illustrates the relationships between the primary tables in the e-zer0 database:
This diagram shows how administrative users manage settings and sessions, while MCP clients (agents) are granted specific access to email and calendar accounts.
Sources: src/routes/mcp-auth.test.ts:40-60, src/routes/agents.test.ts:10-35, src/types.ts
Core Data Models
The system defines several key data structures that correspond to the database schema.
User and Session Management
The admin_users table stores the credentials for the application administrator. Passwords are secured using a salt and a hash.
| Field | Type | Description |
|---|---|---|
id | Integer | Primary key |
username | String | Unique administrator username |
password_hash | String | PBKDF2 hashed password |
salt | String | Unique salt for password hashing |
Sources: src/routes/setup.tsx:135-141, src/routes/mcp-auth.test.ts:61-62
MCP Clients (Agents)
MCP clients represent AI agents authorized to access the system. They are identified by a client_id and can be configured with specific grant types and redirect URIs.
| Field | Type | Description |
|---|---|---|
id | Integer | Internal primary key |
name | String | Display name of the agent |
client_id | String | Public identifier starting with ez_ |
secret_hash | String | Hash of the client secret (if confidential) |
is_active | Boolean | Active status of the agent |
last_used_at | DateTime | Timestamp of the last tool execution |
Sources: src/routes/mcp-auth.test.ts:7-18, src/lib/crypto.test.ts:145-150
Migration System
Migrations are stored as SQL files in the migrations/ directory. They are applied sequentially to define and update the database schema.
Execution Commands
You apply migrations using the Wrangler CLI for both local and remote environments:
- Local Migration:
npm run db:migrate:localapplies changes to the local SQLite state. - Remote Migration:
npm run db:migrate:remoteapplies changes to the production D1 instance. - Reset Local DB:
npm run db:reset:local:unixwipes the local state and reapplies all migrations.
Sources: package.json:8-13, wrangler.toml:17
Migration Flow
When deploying via GitHub Actions, the system ensures the D1 database exists before applying migrations.
The flow ensures that the database infrastructure is provisioned and updated before the application code is deployed.
Sources: scripts/setup-cf.js:100-115, README.md
Access Control and Permissions
Permissions are managed through join tables that link MCP clients to specific email or calendar accounts.
Permission Tables
mcp_client_accounts: Maps agents to permitted email mailboxes.mcp_client_calendar_accounts: Maps agents to read-only calendar access.filter_write_intents: Tracks uncertain filter creation attempts to prevent duplicate POST requests during network failures.
Sources: src/routes/agents.test.ts:110-120, src/lib/email/label-rules.test.ts:160-180
Transactional Safety
The application uses D1's batch() API to ensure atomic updates when modifying agent permissions. If any part of a permission update fails (e.g., updating the name or changing account links), the entire transaction rolls back to preserve the previous state.
Sources: src/routes/mcp-auth.test.ts:80-100, src/routes/agents.test.ts:125-135
Configuration and Secrets
Application-wide settings and sensitive credentials (like OAuth Client IDs) are stored in the app_settings table.
- Encryption: Sensitive settings are encrypted at rest using AES-256-GCM.
- Keys: The
ENCRYPTION_KEYandJWT_SECRETare managed as Cloudflare Worker Secrets and are not stored directly in the database. - Provisioning: The
scripts/setup-cf.jsscript handles the initial generation and pushing of these secrets to the Cloudflare environment.
Sources: scripts/setup-cf.js:155-185, README.md, src/lib/crypto.test.ts:85-100
Summary
The e-zer0 database schema provides a robust foundation for secure AI-to-email communication. By utilizing Cloudflare D1 and a structured migration path, the project ensures that administrative settings, agent permissions, and audit logs are managed with high integrity and transactional safety. The use of join tables for account grants allows for fine-grained, revocable access control for every AI agent connected to the system.