# Database Infrastructure

Sovrium runs on an embedded SQLite database with **zero configuration** and scales up to PostgreSQL by setting a single environment variable. The entire runtime — migrations, client bundles, the CSS engine, example configs — is bundled into one standalone binary, and a static page-render cache keeps server CPU frugal. This page documents the persistence and distribution layer that makes "run the binary, open the URL" possible.

## Zero-Config SQLite Default

When no `DATABASE_URL` is set, Sovrium resolves the `sqlite` dialect, creates `./.sovrium/database.db`, auto-applies the SQLite migration set on first boot, and serves the full core surface — schema, auth, records CRUD, migrations, and forms. The startup banner prints `Database: SQLite (<absolute path>)`.

This mirrors the platform's frugal-by-default philosophy (twin of `STORAGE_PROVIDER` and the `ECO_*` family): the operator opts **into** PostgreSQL by setting `DATABASE_URL`, never the other way around.

## PostgreSQL When Configured

Set `DATABASE_URL` to a `postgresql://` URL and Sovrium resolves the `postgres` dialect, connects, applies the PostgreSQL migration set, and prints `Database: PostgreSQL`. No SQLite file is created. This path is byte-identical to pre-existing PostgreSQL deployments.

A single source of truth — `parseDatabaseDialectConfig()` — scheme-discriminates the one `DATABASE_URL` variable:

| `DATABASE_URL` value                     | Resolves to                                                           |
| ---------------------------------------- | --------------------------------------------------------------------- |
| `postgres://…` / `postgresql://…`        | PostgreSQL at that URL.                                               |
| _unset / empty_                          | SQLite at `./.sovrium/database.db` (the zero-config default).         |
| `file:./x.db` / `file:/abs.db`           | SQLite at the resolved path.                                          |
| `sqlite:./x.db` / `sqlite:///abs.db`     | SQLite alias for the same on-disk file.                               |
| `:memory:`                               | Ephemeral SQLite (dialect-level only — not a full deployment target). |
| anything else (bare path, `mysql://`, …) | Throws `Unsupported DATABASE_URL scheme: …` at boot (fail-loud).      |

:::callout
**`SQLITE_PATH` was removed** (pre-1.0 breaking change, no compatibility bridge). The SQLite file location is now chosen exclusively through `DATABASE_URL` via the `file:` / `sqlite:` schemes. A bare filesystem path with no scheme (`DATABASE_URL=./database.db`) is **rejected** at boot — prefix it with `file:`.
:::

The selected dialect drives the Drizzle client (`bun:sqlite` or `bun:sql`), the migration set, the Better Auth adapter provider, and the operator-facing `runtime` label from `GET /api/admin/config/version` (`sqlite-aio` on SQLite, `postgres` on PostgreSQL).

### Graceful degradation

SQLite mode supports the core subset. Advanced PostgreSQL-only features — notably pgvector RAG semantic search — return a typed `501 { error: 'requires-postgres' }` rather than crash. Scale up to PostgreSQL to unlock them; no code changes required.

## Embedded Data Directory

All local state lives under a single, relocatable data directory.

| Env var            | Default          | Description                                                                                                |
| ------------------ | ---------------- | ---------------------------------------------------------------------------------------------------------- |
| `DATABASE_URL`     | _unset → SQLite_ | Database engine + location selector.                                                                       |
| `SOVRIUM_DATA_DIR` | `./.sovrium`     | Root for the SQLite DB file, lock files, and local storage. Relocate the whole data dir with one variable. |

The default `./.sovrium/database.db` sits under this data dir; the parent directory is created automatically on first boot.

## Static Page Render Cache

Sovrium renders pages server-side on every request. For a page whose HTML does not depend on per-request state (a marketing, about, or pricing page), that is wasted CPU. The in-memory render cache renders such a page once and serves it from memory on subsequent anonymous requests, skipping `renderToString` entirely — an ecoconception and latency win.

| Env var          | Default | Options       | Description                                                                           |
| ---------------- | ------- | ------------- | ------------------------------------------------------------------------------------- |
| `ECO_PAGE_CACHE` | `on`    | `on` \| `off` | In-memory cache of request-invariant page HTML. Operators opt out; they never opt in. |

How it works:

- **Cacheability is derived, never authored.** A page is cacheable only when its rendered HTML cannot vary by request — it is excluded if it has non-public `access`, a `collection`, a page/component `dataSource`, a `contentDir`, a file `source`, a `markdown` body, `presence`, a data-bound sidebar, or a dynamic `:param` route segment.
- **Safety model.** The cache is consulted only for **anonymous, non-preview** requests; session-dependent filtering is deterministic when no session exists, so one user's view can never leak to another.
- **Invalidation by checksum.** Each entry is keyed by `${appRenderChecksum}:${path}:${language}`. The checksum is a SHA-256 of the render-affecting schema slice (`pages`, `components`, `theme`, `languages`, `analytics`); any schema edit changes every key, so stale entries become unreachable. No TTL, no purge logic.
- **Observability.** Every page response carries `X-Render-Cache: hit | miss | bypass` and a `Cache-Control` header (`public, max-age=300` for cacheable, `private, no-cache` for bypassed). Language variants (`/` vs `/fr/`) are isolated by key.

See [Ecoconception](/en/docs/ecoconception) for the full `ECO_*` env-var contract.

## Standalone Binary Distribution

Sovrium's primary distribution channel is a **standalone binary** (`bun build --compile`) that runs with no Bun or Node.js on the target host. The binary embeds the entire runtime into a single executable:

- CLI command surface (`--version`, `validate`, `build`, `schema`, `init`, `agents list`)
- Migration sets (both dialects)
- Client bundles and island chunks
- Agent templates and example configs
- The native-free themed CSS engine

In compiled mode the binary reads its **embedded** assets, never the repo's on-disk `dist/`/`src/`/`specs/` trees — so it behaves identically when launched from any working directory on a customer machine. Embedded client bundles, island chunks, and the language-switcher script are served over HTTP from a binary-launched server (`GET /assets/output.css`, `GET /assets/client.js`, `GET /assets/islands/*`).

## Related Pages

- [Installation](/en/docs/installation) — getting the binary and running it.
- [Environment Variables](/en/docs/env-vars) — full env-var reference incl. `DATABASE_URL`, `SOVRIUM_DATA_DIR`.
- [Schema Migrations](/en/docs/migrations) — the migration sets each dialect applies.
- [Buckets Overview](/en/docs/buckets-overview) — local vs S3 storage, the `STORAGE_PROVIDER` twin.
- [Ecoconception](/en/docs/ecoconception) — the `ECO_PAGE_CACHE` and broader frugal-by-default contract.
- [Admin Dashboard](/en/docs/admin-dashboard) — `config/version` reports the active runtime.