> ## Documentation Index
> Fetch the complete documentation index at: https://www.halite-app.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Database & Migrations

> Dual-backend portability and Alembic.

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`:

```python theme={"dark"}
from halite.db_dialect import lower_eq

# Case-insensitive username lookup — works on both Postgres and SQLite.
stmt = select(User).where(lower_eq(User.username_lower, username))
```

`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:

```python theme={"dark"}
from sqlalchemy import BigInteger, Integer

# backend/src/halite/audit/models.py
_audit_pk_type = BigInteger().with_variant(Integer(), "sqlite")

class AuditEntry(Base):
    id: Mapped[int] = mapped_column(_audit_pk_type, primary_key=True, autoincrement=True)
```

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:

```text theme={"dark"}
20260519_0001_initial_users_sessions.py
20260519_0002_rbac_tables.py
20260519_0003_audit_log.py
20260525_0004_inventory.py
20260526_0005_command_templates.py
...
20260529_0011_activity_events.py
20260531_0014_widget_config.py
```

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:

```sh theme={"dark"}
alembic upgrade head
```

Create a new migration (from `backend/`):

```sh theme={"dark"}
alembic revision -m "add_widget_table"
```

Alembic creates the file in `backend/alembic/versions/`. Edit the generated `upgrade()` and `downgrade()` functions, then commit the file alongside your model changes.

<Note>
  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.
</Note>

## 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.

## Related pages

* [Testing](/develop/testing) — running the dual-backend test suite