Database Schema & Migrations
Relevant source files
The following files were used as context for generating this wiki page:
The database system in Fleet is primarily built on MySQL, serving as the central datastore for application state, configurations, and endpoint metadata. The project employs a structured approach to schema management, utilizing migration scripts to handle updates and automated tools to generate and validate the database structure.
This system ensures that the application can scale across various environments, from local development to production Kubernetes clusters. It manages critical data such as user configurations, application settings, and historical migration status to ensure consistency across deployments.
Architecture and Datastores
Fleet utilizes a dual-layer storage strategy involving a primary relational database and a high-performance cache.
Primary Relational Database (MySQL)
MySQL is the authoritative source of truth for the Fleet server. It stores persistent data including users, teams, policies, and migration logs. In production environments, such as Kubernetes, Fleet supports both managed MySQL services and containerized instances.
Sources: articles/deploy-fleet-on-kubernetes.md:20-22, website/README.md:31-35
Performance Cache (Redis/Valkey)
Fleet uses Redis or Valkey (a Redis-compatible cache) for high-speed data access and session management. This layer is critical for reducing load on the primary MySQL instance during frequent endpoint check-ins.
Sources: articles/deploy-fleet-on-kubernetes.md:24-27
The diagram shows the relationship between the Fleet server and its primary storage components.
Sources: articles/deploy-fleet-on-kubernetes.md:34-45
Migration Workflow
Migrations are handled through the Fleet application logic, typically executing automatically during server startup or deployment.
Deployment Strategy
When deploying changes that affect the database schema, the application supports a "maintenance mode" to prevent data corruption. The workflow involves putting the site into maintenance, applying schema changes, running data fix scripts, and then resuming normal operations.
Sources: website/README.md:31-38
Automated Schema Generation
The dbutils package contains a schema generator that automates the creation of a clean schemadb for testing. This tool performs the following steps:
- Drops and recreates a temporary database (
schemadb). - Initializes a datastore client.
- Executes all pending migrations via the application's internal migration engine.
- Updates internal tables (
app_config_jsonandmigration_status_tables) with fixed timestamps to ensure consistent diffs. - Dumps the resulting schema to a SQL file using
mysqldump.
Sources: tools/dbutils/schema_generator.go:48-77
Migration Components
| Component | Description | Relevant Table/Structure |
|---|---|---|
| App Config | Stores application-wide settings in JSON format. | app_config_json |
| Migration Status | Tracks which migration scripts have been successfully applied. | migration_status_tables |
| Fixed Timestamps | Used during schema generation to prevent non-functional schema diffs. | tstamp / created_at |
Sources: tools/dbutils/schema_generator.go:64-70
External Schema Integration (osquery)
Fleet extends the standard osquery schema with custom documentation and overrides to provide a richer experience for endpoint management.
Merged Schema Generation
The build-static-content.js script merges standard osquery table definitions with Fleet-specific overrides defined in YAML files. This process creates the documentation and metadata used in the Fleet UI and website.
Sources: website/scripts/build-static-content.js:464-472
The flow describes how Fleet-specific overrides are integrated with standard osquery tables to generate the final documentation.
Sources: website/scripts/build-static-content.js:475-485
Schema Override Structure
Custom table documentation is defined in the tables/ folder using a specific YAML template.
| Field | Type | Description |
|---|---|---|
name | String | The unique identifier for the table. |
evented | Boolean | Indicates if the table uses an event-based collection mechanism. |
platforms | Array | List of supported OS (darwin, windows, linux, chrome). |
columns | Array | Defines field names, types, and descriptions. |
Sources: schema/README.md:21-36
Software Library Management
For performance testing and software inventory, Fleet maintains a separate software-specific schema often used in osquery-perf contexts.
Software Table Definition
The software library utilizes a specialized table to track package versions and sources.
Sources: cmd/osquery-perf/software-library/tools/generate-sql/main.go:102-118
| Field | Type | Constraints |
|---|---|---|
id | INTEGER | PRIMARY KEY AUTOINCREMENT |
name | TEXT | NOT NULL |
version | TEXT | NOT NULL |
source | TEXT | NOT NULL (e.g., deb_packages) |
bundle_identifier | TEXT | DEFAULT '' |
Unique Constraints and Indexes
To ensure data integrity and query performance:
- Index:
idx_software_sourceon thesourcecolumn. - Index:
idx_software_nameon thenamecolumn. - Unique Constraint: A unique index on
(name, version, source, bundle_identifier)prevents duplicate software entries.
Sources: cmd/osquery-perf/software-library/tools/generate-sql/main.go:119-125
Conclusion
Fleet's database schema and migration system are designed for high reliability and developer transparency. By centralizing migrations within the application code and providing robust tools for schema generation and validation, the project maintains consistency across complex deployments. The integration of external osquery schemas ensures that Fleet provides comprehensive endpoint visibility while maintaining a structured and manageable relational database core.