SQLite Dual-Mode Backend Implementation¶
Status: Phase 2 Complete, Phase 3 In Progress¶
This document tracks the implementation of SQLite support for AppControl's portable Windows deployment.
Completed¶
Phase 1: Infrastructure (Complete)¶
1. Cargo.toml Updates¶
- Added feature flags:
postgres(default),sqlite,full - SQLx configured with
anydriver for runtime database selection - Conditional compilation via features
2. Database Type Configuration (config.rs)¶
- Added
DatabaseTypeenum (Postgres, Sqlite) DATABASE_TYPEenvironment variable supportSQLITE_PATHfor SQLite database file location- Automatic URL generation for SQLite mode
3. Database Abstraction Layer (db.rs)¶
DbPooltype alias resolves tosqlx::PgPoolorsqlx::SqlitePoolbased on featurecreate_pool()handles both database types- SQLite-specific configuration: WAL mode, busy timeout, foreign keys
sql::module with dialect-specific helpers:now(),gen_uuid(),json_extract(),array_contains()count_filter(),ilike(),bool_true(),bool_false()add_interval(),sub_interval(),in_clause()UuidArraywrapper type for cross-database array handlingIntArraywrapper type for integer arrays- Helper functions:
delete_by_ids(),update_by_ids(),select_ids_by_ids()
4. AppState Updates (lib.rs)¶
- Changed
db: sqlx::PgPooltodb: crate::db::DbPool
5. Main.rs Updates¶
- Conditional partition creation (PostgreSQL only)
- Conditional partition maintenance task
- Database-specific migration paths (
migrations/postgres/ormigrations/sqlite/) - Database-specific
_migrationstable schema - Separate data retention implementations for each database
6. PgPool References Updated¶
- Replaced all 33 files using
sqlx::PgPoolwithcrate::db::DbPool
Phase 2: SQLite Migrations (Complete)¶
Created all 31 SQLite migrations in migrations/sqlite/:
| Migration | Description |
|---|---|
| V001 | organizations, users |
| V002 | agents, gateways |
| V003 | sites, applications |
| V004 | components, dependencies, site_overrides, component_commands |
| V005 | check_events, state_transitions, action_log, switchover_log, config_versions |
| V006 | workspaces, teams, team_members, permissions, share_links, favorites, views |
| V007 | api_keys, notification_preferences |
| V008 | component_daily_stats (regular table, not materialized view) |
| V009 | SAML/OIDC columns, saml_group_mappings |
| V010 | app_variables, component_groups, component_links, command_input_params |
| V011 | agent IP addresses, workspace_sites, workspace_members, heartbeat timeout |
| V012 | components.host field |
| V013 | Security resilience tables |
| V014 | Command executions tracking |
| V015 | Enrollment tokens, CA storage |
| V016 | FSM state cache, webhook notifications |
| V017 | Token revocation and database rate limiting |
| V018 | Local authentication support |
| V019 | Typed parameters, output streaming |
| V020 | Gateway/agent status management |
| V021 | Discovery, operation estimates, air-gap updates |
| V022 | Platform admin, gateway-site binding, certificate revocation |
| V023 | Enriched discovery |
| V024 | Gateway failover zones |
| V025 | Certificate rotation support |
| V026 | Agent system info |
| V027 | Agent metrics time-series |
| V030 | Binding profiles |
| V031 | Remove component_type constraint |
| V032 | Check event metrics |
| V033 | Snapshot schedules |
| V034 | Application references |
| V035 | Component cluster support |
| V036 | Application suspension |
| V037 | Action log results |
| V038 | Operation locks |
| V039 | Zone to site migration |
| V040 | Gateway version |
| V041 | Binding profile wizard resolved_via |
SQLite Schema Adaptations¶
| PostgreSQL | SQLite |
|---|---|
UUID |
TEXT (36 chars) |
TIMESTAMPTZ |
TEXT (ISO8601) |
JSONB |
TEXT (JSON string) |
BOOLEAN |
INTEGER (0/1) |
gen_random_uuid() |
App-generated UUID |
BIGINT IDENTITY |
INTEGER PRIMARY KEY AUTOINCREMENT |
| Table partitioning | Single table with indexes |
now() |
datetime('now') |
UUID[] |
TEXT (JSON array) |
INTEGER[] |
TEXT (JSON array) |
MATERIALIZED VIEW |
Regular table with refresh |
COMMENT ON |
Not supported (omitted) |
Phase 3: Query Adaptations (Complete)¶
Files Fixed¶
api/profiles.rs- UuidArray for gateway_idsapi/agents.rs- Bulk delete with conditional queriesapi/apps.rs- Referenced app status queriesapi/import_wizard.rs- UuidArray for profile gateway_idscore/diagnostic.rs- Latest check queriescore/heartbeat_batcher.rs- Batch update queriescore/fsm.rs- Component state querieswebsocket/mod.rs- Agent lookup queriesapi/discovery.rs- UuidArray/IntArray for schedule/snapshot arrays, helper functionsapi/history.rs- Helper functions for state transitions, component actions, command executionscore/snapshot_scheduler.rs- UuidArray for agent/report arrays, helper functionscore/resolution.rs- Helper functions for agent resolution queries
Query Patterns Addressed¶
- Array binding (
Vec<Uuid>,Vec<i32>): - Use
UuidArrayandIntArraywrapper types in FromRow structs -
Use conditional helper functions for queries
-
ANY($1) clause:
- PostgreSQL:
WHERE id = ANY($1)with Vec -
SQLite:
WHERE id IN ($1, $2, ...)with individual binds via helper functions -
FILTER clause:
COUNT(*) FILTER (WHERE x) - PostgreSQL: Native FILTER clause
-
SQLite:
SUM(CASE WHEN x THEN 1 ELSE 0 END) -
JSON operators:
details->>'key' - PostgreSQL: Native JSON access
-
SQLite:
json_extract(details, '$.key') -
Array columns in SELECT:
- PostgreSQL: Native UUID[] arrays
- SQLite: JSON TEXT decoded via UuidArray/IntArray Decode impls
Build Status¶
# PostgreSQL build (default)
cargo build --package appcontrol-backend --features postgres # ✓ SUCCESS
cargo test --package appcontrol-backend --features postgres # ✓ 87 tests pass
# SQLite build (complete)
cargo build --package appcontrol-backend --no-default-features --features sqlite # ✓ SUCCESS
Phase 4: UUID/Timestamp Handling (Complete)¶
- All INSERT statements provide UUIDs via
Uuid::new_v4()in Rust - Timestamps stored as ISO8601 strings (RFC3339 format)
- SQLite migrations use TEXT type for both UUID and TIMESTAMPTZ columns
Phase 5: Locking and Concurrency (Complete)¶
- FSM transition queries use conditional compilation:
- PostgreSQL:
FOR UPDATE OF cclause for row-level locking - SQLite: WAL mode provides serializable isolation
- Helper functions:
fetch_component_for_transition(),update_component_state() - SQLite uses
datetime('now')instead ofnow()for timestamps
Phase 6: Partition Handling (Complete)¶
run_data_retention()has separate implementations:- PostgreSQL: DROP PARTITION for check_events
- SQLite: Simple DELETE queries
- SQLite-specific function already existed in main.rs
Phase 7: Materialized View Replacement (Complete)¶
- SQLite migrations use regular tables instead of materialized views
component_daily_statsas regular table with manual refresh
Phase 8: Testing Infrastructure (Complete)¶
- Added
sqlite-buildjob to CI workflow - Builds and tests backend with SQLite feature
- Parallel execution with PostgreSQL tests
Phase 9: PowerShell Deployment Script (Complete)¶
- Updated
deploy-standalone.ps1with SQLite mode (-DbMode sqlite) - SQLite mode is now the default (zero dependencies)
- Simplified start/stop scripts for SQLite (no PostgreSQL management)
- Environment variables:
DATABASE_TYPE=sqlite,SQLITE_PATH=<path>
Implementation Complete¶
All phases are now complete. The backend can be built and run with either database:
# PostgreSQL build (default, full features)
cargo build --package appcontrol-backend --features postgres
# SQLite build (portable, zero dependencies)
cargo build --package appcontrol-backend --no-default-features --features sqlite
Windows Portable Deployment¶
# Download and deploy with SQLite (recommended)
.\deploy-standalone.ps1 -DbMode sqlite
# Or with embedded PostgreSQL
.\deploy-standalone.ps1 -DbMode embedded
Configuration¶
PostgreSQL Mode (Default)¶
SQLite Mode¶
export DATABASE_TYPE=sqlite
export SQLITE_PATH=./appcontrol.db
# Or with explicit URL:
export DATABASE_URL=sqlite:./appcontrol.db
CI/CD Build Process (CRITICAL)¶
The Problem: Cargo Workspace Feature Unification¶
When building from the workspace root, Cargo unifies features across all workspace members.
The e2e-tests crate depends on sqlx with the postgres feature:
# crates/e2e-tests/Cargo.toml
[dependencies]
sqlx = { version = "0.7", features = ["postgres", ...] }
This causes the postgres feature to "contaminate" the SQLite build, even with --no-default-features --features sqlite.
The resulting binary contains both drivers and fails at runtime with:
The Solution: Build from Crate Directory¶
The SQLite backend MUST be built from crates/backend/ directory, NOT from the workspace root.
This bypasses Cargo's workspace feature unification:
# ❌ WRONG - builds from workspace root, gets contaminated
cargo build --release --package appcontrol-backend --no-default-features --features sqlite
# ✅ CORRECT - builds from crate directory, isolated from workspace
cd crates/backend
cargo build --release --no-default-features --features sqlite
Verification¶
After building, verify the binary has the correct driver:
# Should return 0 - no postgres error message
strings appcontrol-backend | grep -c "sqlite mode not supported"
# Should show sqlx-sqlite, NOT sqlx-postgres
cargo tree -p appcontrol-backend --no-default-features --features sqlite | grep sqlx
Release Workflow¶
The .github/workflows/release.yaml implements this correctly:
# Build SQLite from crate directory to avoid workspace feature unification
export CARGO_TARGET_DIR=$PWD/target-sqlite
cd crates/backend
cargo build --release --target ${{ matrix.target }} --no-default-features --features sqlite
cd ../..
DO NOT change this to build from the workspace root - it will break SQLite standalone deployment.
History¶
This issue took multiple releases to diagnose and fix (v1.3.4 → v1.3.9). The root cause was not obvious because: 1. Local builds from crate directory worked fine 2. The error message appeared in the binary but was never triggered during CI testing 3. Cargo's feature unification is silent and happens implicitly
Key Files¶
crates/backend/Cargo.toml- Feature flagscrates/backend/src/config.rs- DatabaseType enumcrates/backend/src/db.rs- Pool abstraction + SQL helpers + UuidArray/IntArraycrates/backend/src/lib.rs- AppState type changecrates/backend/src/main.rs- Conditional logicmigrations/sqlite/V001-V041.sql- Complete SQLite schema.github/workflows/release.yaml- Build process (see CI/CD section above)