# SQLite Export

The SQLite exporter serialises an entire project's imported master data into one SQLite database file. The database is a self-describing, queryable artifact that scales to data sets the JSON exporter would inflate beyond comfortable distribution sizes.

## Kind

The export-kind identifier is `sqlite`.

## Configuration

A SQLite export is configured under `exports:` in the project configuration file:

```yaml
exports:
  - kind: sqlite
    out: data/masterdata.db
```

- `kind` selects this exporter.
- `out` is the file system path the exporter writes to. Relative paths resolve against the project root. The exporter creates any missing parent directories. If the file already exists it is overwritten.

No `options` keys are recognised at this stage; unknown keys are silently ignored to leave room for future extensions.

## Database Shape

The exporter writes one SQLite database per run. The database contains:

- One table per master, named with the master's **flat camelCased identifier** (the same name the per-target MasterData accessor and the JSON exporter's top-level key use).
- One metadata table, `_masterbelt_meta`, that records the export's format version and the producing tool's identity.

A nested master `master User { master Friendships { ... } }` appears as a single top-level table `userFriendships` — no nested table layout is used.

Every table is created with `STRICT` so SQLite enforces declared column affinities at insert time. Records are inserted in importer-supplied order.

## Table Layout

For each master the exporter emits a `CREATE TABLE` statement whose columns mirror the master's record fields:

- Column names are the master's surface field names as written in Masterbelt source. Names are emitted verbatim with no case transformation.
- A `ref<T>` field expands to the target master's primary-key columns under the surrounding field's name joined with `_` (`field_pk1`, `field_pk2`, ...) — the same expansion the JSON exporter applies.
- A primary key declared by `primary` on the master is materialised as a SQL `PRIMARY KEY` clause. Composite keys appear as `PRIMARY KEY (col1, col2, ...)` in the order declared on the master.
- The implicit primary-key index aside, the only secondary indexes are those inferred from a master's [`indexed scope`](#secondary-indexes-from-indexed-scopes) declarations.

### Column Types

Masterbelt scalar types map to SQLite column affinities as follows:

| Masterbelt value | SQLite type |
| --- | --- |
| `bool` | `INTEGER` (stored as `0` / `1`) |
| `int`, `int8`, `int16`, `int32`, `int64`, `uint8`, `uint16`, `uint32`, `uint64` | `INTEGER` |
| `string` | `TEXT` |

Phase 1 does not infer `NOT NULL` constraints. Every column is created without `NOT NULL` and accepts `NULL` so a value that cannot be represented (out-of-range integer, future nullable value) can be stored as `NULL` while the rest of the row still inserts. A future revision will tie `NOT NULL` emission to a richer schema-side nullability model once one exists; until then, loaders must not rely on the database to reject `NULL` values.

Composite-shaped fields (`list<T>`, `map<K, V>`, nested products that did not flatten through `ref<T>`) do not have a Phase 1 SQLite representation. The exporter omits these fields from the master's table entirely and reports `masterbelt.exporter.sqlite.value_unsupported` once per affected field. The master's remaining primitive columns are still emitted and populated so the rest of the database remains usable.

An integer value whose magnitude does not fit in SQLite's 8-byte signed `INTEGER` range produces the same `masterbelt.exporter.sqlite.value_unsupported` diagnostic for the offending row and is stored as `NULL` so the surrounding rows still load.

### Secondary Indexes from Indexed Scopes

A master's [`indexed scope`](schema.md#indexed-scopes) declarations drive secondary-index generation. The exporter inlines each indexed scope's relation plan (its `where` predicates and order-by stages, with chained scopes expanded in place) and infers `CREATE INDEX` statements from it. The inference is purely structural: it reads the lowered query plan, never executes it, and only the SQLite backend consults it — other targets ignore `indexed` without a diagnostic.

#### Inferable Plan Shapes

These plan fragments contribute index columns:

- Equality predicates (`eq`), including `bool` equality.
- Range predicates (`lt`, `le`, `gt`, `ge`).
- `between` and `in` predicates.
- `orderBy` / `thenBy` orderings, including descending order (emitted as `DESC` in the index).
- `not(p)` contributes the field(s) of its inner predicate.
- `or(a, b)` over distinct fields contributes those fields; a more complex `or` falls into partial success (below).

`skip` and `take` are ignored for inference. Collation and null ordering are out of scope. The exporter generates neither expression indexes nor unique indexes, and never emits a partial (`WHERE`) index — a literal or parameter predicate produces an ordinary index.

#### Column Ordering

When a single scope mixes equality, range, and order-by usage, the composite index orders columns **equality columns → range column → order-by columns**. Multiple equality predicates order by record field declaration order. Multiple `where` calls merge as a conjunction before inference. A chained scope (`genderedAdult(g) => self.adult().gendered(g)`) inlines its stages, so `gender == $g and age >= 20 order by name` yields the composite index `(gender, age, name)`, and a bare `age >= 20` yields `(age)`.

#### Generation, Naming, and Deduplication

- One scope may yield more than one index.
- An inferred index identical to the master's primary key is not generated.
- Identical indexes inferred from multiple scopes are deduplicated.
- An index is named `idx_<table>_<scope>`. When one scope yields several indexes, the second and later carry a numeric suffix starting at `2`: `idx_<table>_<scope>`, `idx_<table>_<scope>_2`, `idx_<table>_<scope>_3`. If the resulting name still collides with an already-emitted index name, a `_<n>` disambiguating suffix is appended.
- The DDL is `CREATE INDEX <name> ON <table> (<columns>);` with no `IF NOT EXISTS` — the export artifact is created fresh on every run.

#### Diagnostics

- Generating an index emits `masterbelt.scope.index_generated` (info) with the index name.
- An `indexed scope` that cannot be turned into an index — or only partially — emits `masterbelt.scope.index_inference_failed` (warning); any inferable part is still generated. A scope parameter that is never used in an indexable predicate or ordering is not itself an error.

### Metadata Table

The metadata table is created as:

```sql
CREATE TABLE _masterbelt_meta (
  key   TEXT PRIMARY KEY,
  value TEXT
) STRICT;
```

The exporter populates the following keys on every run:

| Key | Value |
| --- | --- |
| `format` | The literal string `masterbelt.sqlite`. |
| `format_version` | The SQLite export format version. Phase 1 emits `1`. |
| `masterbelt_version` | The producing tool's release identifier, or `dev` for unstamped builds. |
| `created_at` | The export's wall-clock time as an RFC 3339 timestamp in UTC. |

`format_version` is reserved for breaking changes to the SQLite export layout itself. Additive schema changes (new metadata keys, additional indexes, additional columns) do not bump the version; consumers must ignore keys they do not recognise.

## Determinism

Two runs against the same input and the same producer version produce byte-identical row contents:

- Tables are created in master-declaration order.
- Records are inserted in importer-supplied order (already deterministic for every importer defined in [import-csv.md](import-csv.md) and friends).
- Composite primary keys are written column-by-column in the order declared on the master.

The `created_at` metadata entry intentionally records the run's wall-clock time and is therefore not byte-deterministic across runs. Tools that need a byte-deterministic database can post-process the metadata table; the rest of the database remains stable across identical inputs.

## Loader Contract

The SQLite export is the input format for the SQL-storage code generation modes documented in:

- [../codegen/golang.md](../codegen/golang.md#master-data)
- [../codegen/typescript.md](../codegen/typescript.md#master-data)
- [../codegen/csharp.md](../codegen/csharp.md#master-data)

A generated runtime does not validate the database's full schema at startup. Missing tables or columns surface as ordinary SQL errors when the affected query runs. Unknown extra tables, columns, indexes, or metadata keys are silently tolerated so additive schema evolution does not break older runtimes.

## Diagnostics

The exporter emits the following diagnostic codes:

- `masterbelt.exporter.sqlite.open_failed` — opening or creating the output database failed.
- `masterbelt.exporter.sqlite.exec_failed` — executing a `CREATE TABLE` or `INSERT` statement failed.
- `masterbelt.exporter.sqlite.value_unsupported` — a Masterbelt value shape cannot be represented as a SQLite column value (composite fields, oversized integers, ...). The offending row is still inserted with the affected column left `NULL`.

[Secondary-index inference](#secondary-indexes-from-indexed-scopes) additionally emits `masterbelt.scope.index_generated` (info) and `masterbelt.scope.index_inference_failed` (warning).