Skip to main content
Halite runs on both PostgreSQL (via asyncpg) and SQLite (via aiosqlite). SQLite is the default for local development and homelab deployments; Postgres is recommended for production.

​
Portability rules

Because the app must work on both backends, you must follow a few constraints when writing queries or models:
  • Do not use Postgres-only operators or types. No ILIKE, JSONB operators, arrays, or other PG-only features.
  • Route portability concerns through db_dialect.py. Any helper that needs to behave differently on each backend belongs there.
The main helper today is lower_eq:
lower_eq compares against a pre-lowercased column (e.g. username_lower). The column stores only lowercase values; lower_eq lowercases the input at query time. This avoids LOWER() in the WHERE clause, which can bypass indexes on some backends.

​
Dialect variant pattern

SQLite only autocrements INTEGER PRIMARY KEY, not BIGINT. For tables that expect large row counts (like the audit log), use SQLAlchemy’s .with_variant() to select the right type per backend:
This gives you a real BIGINT autoincrement on Postgres and an INTEGER autoincrement on SQLite. The same pattern applies to JSON columns. The activity_events table stores each raw Salt event in a column typed JSON().with_variant(JSONB(), "postgresql") — JSONB on Postgres, generic JSON on SQLite — so the model stays portable without any PG-only JSON operators in queries.

​
Alembic migrations

All schema changes go through Alembic. Migrations live in backend/alembic/versions/ and use a dated filename convention:
The prefix is YYYYMMDD_NNNN_description — the date the migration was written, a four-digit sequence number, and a short snake-case description.

​
Common commands

Apply all pending migrations:
Create a new migration (from backend/):
Alembic creates the file in backend/alembic/versions/. Edit the generated upgrade() and downgrade() functions, then commit the file alongside your model changes.
When writing a migration that touches column types, check whether you need the .with_variant() pattern to stay portable. Test your migration against both backends before merging — the CI suite runs every DB test against both SQLite and Postgres.

​
Engine setup

db.py initialises the async SQLAlchemy engine. SQLite gets two extra connect args:
  • check_same_thread=False — required for async use.
  • timeout=15 — aiosqlite busy-timeout (15 s), which prevents write-lock races between the fleet and inventory schedulers during cold start.
  • Testing — running the dual-backend test suite