SQLite Store & Persistence

Relevant source files

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

• internal/store/store.go
• internal/store/backup.go
• internal/store/migrations.go
• internal/store/nodes.go
• internal/store/recovery_test.go
• AGENTS.md

The SQLite Store serves as the primary persistence layer for the HX-ProxyGroup control plane. It manages the desired state of the system, including subscriptions, proxy nodes, listeners, and traffic statistics. The system uses a single SQLite database file with Write-Ahead Logging (WAL) enabled to ensure high performance and reliability during concurrent access.

Persistence logic resides primarily in the internal/store package. The store implements an atomic migration system to manage schema updates and an online backup mechanism to safeguard data without interrupting service.

Sources: AGENTS.md:31-35, internal/store/store.go:1-25

Architecture and Initialization

The Store struct encapsulates the database connection and operational locks. Upon opening the database, the system configures the SQLite environment for optimal reliability and performance.

Initialization Workflow

1. Directory Creation: The store creates the parent directory if it does not exist.
2. Connection Management: It limits the connection pool to exactly one connection to prevent concurrent write contention in the embedded environment.
3. PRAGMA Configuration: The store executes several PRAGMA statements to enable foreign keys, set busy timeouts, and enable WAL mode.
4. Permissions: The store secures the database file by setting its permissions to 0o600 (read/write for the owner only).

Sources: internal/store/store.go:34-85

Database Configuration Parameters

Parameter	Value	Description
busy_timeout	5000ms	The time to wait for a locked table before returning an error.
foreign_keys	ON	Enforces referential integrity between tables.
synchronous	NORMAL	Balances data safety and write performance.
temp_store	MEMORY	Stores temporary tables and indices in RAM.
journal_mode	WAL	Enables Write-Ahead Logging for concurrent reads/writes.

Sources: internal/store/store.go:19-75

Schema and Migrations

The store manages its schema through a sequential list of versioned migrations. Each migration includes SQL statements and metadata to track applied changes in the schema_migrations table.

The diagram shows the iterative migration process performed during store initialization.
Sources: internal/store/migrations.go:511-546

Key Data Entities

The schema defines several critical tables for the proxy control plane:

• nodes: Stores proxy server details, fingerprints, and lifecycle states (candidate, healthy, retired, etc.).
• subscriptions: Manages remote or inline node sources and their refresh schedules.
• listeners: Configures inbound proxy services (HTTP, SOCKS, Mixed) and their port bindings.
• traffic_totals / traffic_buckets: Maintains connection and byte counts for resources.

Sources: internal/store/migrations.go:20-300

Online Backup Mechanism

The store implements a non-blocking backup system using the SQLite Online Backup API. This allows the system to create a consistent snapshot of the active database while it remains in use.

Backup Process Flow

1. Target Verification: Ensures the destination path exists and is not the active database file.
2. Atomic Temp File: Creates a temporary file to store the backup during the copy phase.
3. Paging: Copies the database pages in batches (256 pages per step) to the temporary file.
4. Integrity Check: Executes PRAGMA integrity_check and verifies the schema version on the backup file before finalizing.
5. Atomic Publish: Uses os.Link and os.Rename to move the verified backup to the final destination.

Sources: internal/store/backup.go:17-108

This sequence illustrates the lifecycle of a backup operation from temporary file creation to final publication.
Sources: internal/store/backup.go:43-100

Lifecycle Management for Nodes

The store handles the transition of proxy nodes between different states based on subscription refreshes.

Automated Node Retirement

When a subscription refresh occurs, the store performs a "diff" operation within a transaction:

1. Upsert: It updates existing nodes or inserts new ones based on their unique fingerprint.
2. Orphan Identification: It identifies nodes that were previously associated with the subscription but are missing from the latest fetch.
3. Retirement: It sets the lifecycle_state to retired and updates the retired_at timestamp for any node no longer reachable via any active subscription snapshot.

Sources: internal/store/nodes.go:94-110, internal/store/nodes.go:210-226

Reliability and Data Safety

• WAL Mode Recovery: The system relies on SQLite's WAL mode to recover committed data even if the process is killed during a transaction. The recovery_test.go verifies that committed metadata persists after a simulated crash.
• Foreign Key Enforcement: Migrations that require rebuilding tables (e.g., changing primary keys or constraints) use a specialized applyMigrationWithoutForeignKeys path. This path disables enforcement, performs the rebuild, and executes PRAGMA foreign_key_check before re-enabling enforcement to ensure no referential violations occurred.
• Metadata Storage: A dedicated system_metadata table stores key-value pairs for system-wide settings, ensuring configuration state is versioned alongside the schema.

Sources: internal/store/recovery_test.go:15-45, internal/store/migrations.go:565-618, internal/store/store.go:138-161

Summary

The SQLite Store & Persistence module provides a robust, ACID-compliant foundation for the HX-ProxyGroup. By utilizing SQLite's WAL mode and a structured migration system, it ensures that the proxy control plane maintains a consistent desired state across restarts and schema upgrades, while the online backup system enables disaster recovery without service interruption.