D1 Databases & Data Models
Relevant source files
The following files were used as context for generating this wiki page:
Introduction
FusionOps utilizes Cloudflare D1, a serverless relational database based on SQLite, to manage structured data across its distributed architecture. The system is partitioned into three distinct database instances to ensure separation of concerns between core application logic, event tracking, and asynchronous lead handling. This modularity allows for specialized scaling and maintenance of data related to campaigns, tracking pixels, and external webhooks.
The D1 ecosystem serves as the persistence layer for three primary backend workers: the API Worker, the Pixel Worker, and the Callback Worker. While Neon Postgres is optionally used for specific dashboard settings and site configurations, D1 remains the primary engine for high-frequency operations like campaign CRUD and real-time event logging.
Sources: CLAUDE.md:73-80, INFRASTRUCTURE_SETUP.md:38-46, PRODUCTION_STATUS.md:34-40
Database Architecture Overview
The project distributes its data models across three primary D1 instances. Each database is bound to a specific worker environment using the DB binding name in wrangler.toml.
The diagram shows the relationship between backend workers and their respective D1 database instances.
Instance Inventory
| Database Name | Purpose | Binding | Migration File |
|---|---|---|---|
fusionops-main | Core application data, campaigns, and templates | DB | apps/api-worker/migrations/0001_init.sql |
fusionops-pixel | High-volume tracking of user interactions | DB | apps/pixel-worker/migrations/0001_pixel_events.sql |
fusionops-callback | Storage for external lead and webhook data | DB | apps/worker/migrations/0001_init.sql |
Sources: INFRASTRUCTURE_SETUP.md:65-71, scripts/setup-infrastructure.js:33-40, PRODUCTION_STATUS.md:41-75
Data Models: Main Database
The fusionops-main database contains the schema for orchestrating landing page operations. It includes tables for settings, site metadata, and template definitions.
Sites and Campaigns
The sites table stores configuration for landing pages, using a JSONB-like approach in SQLite to handle flexible site data.
| Field | Type | Constraints | Description |
|---|---|---|---|
id | TEXT | PRIMARY KEY | Unique identifier for the site/campaign |
data | TEXT | NOT NULL | Stringified JSON containing site configuration |
created_at | DATETIME | DEFAULT CURRENT_TIMESTAMP | Record creation time |
updated_at | DATETIME | DEFAULT CURRENT_TIMESTAMP | Last modification time |
Sources: apps/api-worker/migrations/0001_init.sql:10-15, INFRASTRUCTURE_SETUP.md:144-150
Key Tables Summary
| Table Name | Description | Source File |
|---|---|---|
settings | Global key-value store for application configuration | apps/api-worker/migrations/0001_init.sql:2 |
templates | Library of available landing page templates | PRODUCTION_STATUS.md:55 |
ops_accounts | Management of advertising or registrar accounts | PRODUCTION_STATUS.md:58 |
risk_scores | Scores generated by FBIS agents for security audits | HERMES_FBIS_AUDIT.txt:140 |
Data Models: Pixel Tracking
The fusionops-pixel database is optimized for event logging. It handles the data generated by the t.{domain}/e endpoint.
Pixel Events Schema
The entity-relationship diagram for the pixel tracking events storage.
| Field | Type | Description |
|---|---|---|
campaign_id | TEXT | Links the event to a specific site in the Main DB |
event_type | TEXT | The type of interaction (page view, conversion, etc.) |
data | TEXT | Flexible JSON storage for custom event parameters |
Sources: apps/pixel-worker/migrations/0001_pixel_events.sql:1-10, PRODUCTION_STATUS.md:69
Data Models: Callback and Webhooks
The fusionops-callback database serves the worker app, primarily managing incoming lead data and account-level integrations.
Accounts and Leads
CREATE TABLE IF NOT EXISTS accounts (
id TEXT PRIMARY KEY,
type TEXT NOT NULL,
data TEXT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
Sources: apps/worker/migrations/0001_init.sql:1-7
The accounts table in this instance is specifically used for mapping external identifiers to internal leads and processing callbacks from providers like Voluum.
Sources: CLAUDE.md:79-80, scripts/setup-infrastructure.js:290-293
Database Lifecycle Management
Infrastructure scripts automate the creation and migration of these databases to ensure consistency across environments.
Migration Flow
The setup process involves a sequential execution of SQL files against specific D1 names.
The sequence diagram illustrating the automated database initialization and migration process.
Sources: scripts/setup-infrastructure.js:145-185, INFRASTRUCTURE_SETUP.md:73-83
Staging vs. Production
The project supports parallel environments by appending -staging to database names and using distinct database_id values in wrangler.toml configurations.
Sources: scripts/create-new-databases.js:108-115, scripts/setup-infrastructure.js:213-228
Conclusion
The D1 data architecture of FusionOps is designed for scalability and high availability within the Cloudflare Edge network. By separating core metadata (Main), tracking telemetry (Pixel), and integration callbacks (Callback), the system minimizes contention and provides a robust foundation for PPC campaign management and autonomous FBIS agent auditing.