Database Schema & Initialization

Relevant source files

The following files were used as context for generating this wiki page:

• src/services/central-alerts/v1/db/init.sql
• README.md
• test/utils/d1-mock.ts
• test/services/central-alerts/v1/database.test.ts
• test/lib/adapters/node/database.test.ts
• test/lib/adapters/node/cache.test.ts

The FOSSBilling API utilizes a multi-layered storage architecture designed to support both Cloudflare Workers (using D1 and KV) and local Node.js environments (using SQLite). The primary database focus is on the Central Alerts service, which stores and manages system-wide notifications for FOSSBilling instances. Initialization involves setting up these relational tables and configuring key-value namespaces for caching and authentication.

Sources: README.md:1-10, src/services/central-alerts/v1/db/init.sql:1-5

Core Storage Components

The application relies on three primary storage bindings, each serving a specific role in the system architecture.

Binding Name	Type	Purpose
DB_CENTRAL_ALERTS	D1 (SQL)	Primary relational storage for alert messages and metadata.
CACHE_KV	KV	Stores cached GitHub API responses to avoid rate limits.
AUTH_KV	KV	Stores the UPDATE_TOKEN for secure version update operations.

Sources: README.md:46-51

Data Flow for Initialization

The initialization process typically involves running SQL scripts via local development tools or CI/CD pipelines to ensure the target environment (D1 or SQLite) has the required schema.

Central Alerts Schema

The central_alerts table is the primary relational entity. It stores notification content, targeting information (version ranges), and UI configuration for buttons.

Table: central_alerts

Field	Type	Constraints	Description
id	TEXT	PRIMARY KEY	Unique identifier for the alert.
title	TEXT	NOT NULL	Display title of the notification.
message	TEXT	NOT NULL	Body content of the notification.
type	TEXT	CHECK (success, info, warning, danger)	Severity level of the alert.
dismissible	BOOLEAN	NOT NULL DEFAULT false	Whether the user can hide the alert.
min_fossbilling_version	TEXT	NOT NULL	Lower bound of targeted version range.
max_fossbilling_version	TEXT	NOT NULL	Upper bound of targeted version range.
include_preview_branch	BOOLEAN	NOT NULL DEFAULT false	Targeted at preview branches.
buttons	JSON	DEFAULT '[]'	Actionable links embedded in the alert.
datetime	TEXT	NOT NULL	ISO timestamp for the alert.
created_at	DATETIME	DEFAULT CURRENT_TIMESTAMP	Record creation timestamp.
updated_at	DATETIME	DEFAULT CURRENT_TIMESTAMP	Record last update timestamp.

Sources: src/services/central-alerts/v1/db/init.sql:6-20

Performance Optimization

To ensure efficient querying of alerts by the API, several indices are established on fields commonly used in filtering and sorting.

• Type Index: idx_central_alerts_type for filtering by severity.
• Version Range Index: idx_central_alerts_version_range for filtering based on the instance's current version.
• Chronological Index: idx_central_alerts_datetime for sorting alerts by time.

Sources: src/services/central-alerts/v1/db/init.sql:22-24

Platform Adapters & Mocking

The project implements a platform-agnostic layer to support different environments. This is achieved through adapters that translate generic database interfaces into specific implementations.

Node.js SQLite Adapter

For local development and Node-based testing, the SQLiteAdapter wraps node:sqlite's DatabaseSync. It handles statement preparation, parameter binding, and result set management.

D1 Mock Implementation

For integration testing, a MockD1Database is used to simulate the behavior of Cloudflare's D1. It implements semantic version comparison logic within the mock to test the alert range filtering.

Initialization Logic

The initialization process is primarily manual for local development via npm scripts, but the system also uses automated seeding for testing environments.

SQL Initialization Example

The following SQL defines the table structure and a critical security alert as initial data.

CREATE TABLE IF NOT EXISTS central_alerts (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    -- ... other fields
    buttons JSON DEFAULT '[]',
    datetime TEXT NOT NULL
);

INSERT OR IGNORE INTO central_alerts (id, title, message, type, min_fossbilling_version, max_fossbilling_version, datetime, buttons) 
VALUES ('1', 'This version of FOSSBilling is insecure', '...', 'danger', '0.0.0', '0.5.2', '2023-06-30T21:43:03+00:00', '[...]');

Sources: src/services/central-alerts/v1/db/init.sql:6-30

Cache Initialization

While the database stores persistent alerts, the KV store handles transient data. The CACHE_KV namespace is initialized with an expiration TTL for statistics and GitHub release data.

• Statistics Cache: fossbilling-stats-data (TTL: 86,400 seconds).
• Release Cache: gh-fossbilling-releases (TTL: Dynamic).

Sources: src/services/stats/v1/index.ts:17-21, test/lib/adapters/node/cache.test.ts:44-50

Summary

The Database Schema & Initialization module provides the backbone for the Central Alerts service and caching mechanisms. By combining structured relational data in D1 with flexible KV storage and platform-agnostic adapters, the system ensures consistent behavior across Cloudflare Workers and local Node.js environments. Proper index management and SQL-based initialization scripts maintain data integrity and query performance.