Store Adapters

5.1 Store Interface

A store adapter provides data persistence to the Kalos platform. The ADL engine’s action handlers call store methods to create, read, update, and delete records. The migration system calls store methods to execute DDL and inspect schema state. The auth plugin calls store methods to manage users and sessions.

Store adapters implement a common query interface. The ADL engine generates SQL appropriate to the active store adapter. The domain author does not write SQL, reference database-specific constructs, or configure database-specific behavior — the store adapter encapsulates all platform differences.

Two reference adapters are provided: SQLite and PostgreSQL. Both are loaded as plugins (§2.5) and configured via the store section of the configuration file (§3.5). Only one store adapter SHOULD be active per server instance.

File storage is a separate, optional adapter. The handler.files plugin registers a file store for binary assets (uploads served at /api/v1/files/…) that backs the media library. It is documented alongside its primary consumer in §18.8 (File Store Backing), not here, because the record-store query interface described below does not apply to it.

5.2 SQLite Store

5.2.1 Connection Management

The SQLite store opens a connection pool of configurable size (default: 4) to the database file specified in the configuration. All connections share the same database file. Concurrent reads are supported via WAL mode; concurrent writes are serialized by SQLite’s internal locking.

The store applies the following PRAGMAs on every new connection:

These PRAGMAs are non-negotiable. A conforming implementation MUST apply them. foreign_keys = ON is particularly critical — without it, FK constraints declared in CREATE TABLE statements are silently ignored, and cascade delete/restrict behavior does not execute.

5.2.2 Database File Organization

SQLite deployments use separate database files for different concerns:

Separation isolates concerns: dropping and recreating the application database does not affect user accounts or domain registry state. The seeder’s “clean reset” workflow (rm dbs/*.db && restart) relies on this — it destroys all data but the domain is re-bootstrapped from the YAML file on startup.

When adl.db_path and adl.app_db_path are not explicitly configured, the store creates them in the same directory as store.sqlite.path with default filenames.

5.2.3 Type Mapping

ADL field types map to SQLite column types as follows:

All text-like ADL types map to TEXT in SQLite. Type-specific validation (email format, UUID structure, URL scheme, money precision) is enforced by the ADL runtime layer at the API boundary, not by the database. This is a deliberate design choice: the database stores data faithfully, and the application layer enforces semantics.

decimal and money types are stored as TEXT to avoid floating-point precision loss. The runtime converts between string and numeric representations as needed.

5.2.4 Column Constraints

The DDL generator translates ADL field constraints to SQLite column definitions:

Unique constraints are implemented as separate CREATE UNIQUE INDEX statements rather than inline UNIQUE keywords. This gives the migration system explicit control over index naming and allows independent index management (create, drop, rename) without rebuilding the table.

5.2.5 ALTER TABLE Limitations

SQLite has significant limitations on ALTER TABLE operations compared to other databases:

Operations that SQLite does not support natively are handled by the 4-step column rebuild pattern:

-- Step 1: Add temporary column with new constraints
ALTER TABLE t ADD COLUMN col_new TYPE [NEW CONSTRAINTS];

-- Step 2: Copy data from old column to new column
UPDATE t SET col_new = col;                    -- or COALESCE(col, 'default')

-- Step 3: Drop old column (requires SQLite >= 3.35.0)
ALTER TABLE t DROP COLUMN col;

-- Step 4: Rename temporary column to original name
ALTER TABLE t RENAME COLUMN col_new TO col;

Three migration scenarios trigger the column rebuild:

All four steps execute within a single transaction. If any step fails, the entire sequence is rolled back and the table remains in its original state.

Column position side effect: After a rebuild, the column may appear at a different position in the table (typically at the end). SQLite accesses columns by name, not by position, so this has no functional impact. It is visible in schema inspection tools (PRAGMA table_info).

SQLite version guard: If the SQLite library is older than 3.35.0, the DROP COLUMN step (Step 3) is skipped with a warning. The migration is aborted midway, leaving both the old and temporary columns in the table — a state that requires manual intervention. The migration system logs this as a warning and continues.

5.2.6 Transaction Semantics

All DDL and DML operations within a single migration plan execute in a single BEGIN/COMMIT transaction. If any statement fails, the transaction is rolled back and no schema or data changes are applied.

CRUD operations (create, update, delete) each execute in their own implicit transaction unless the resource has features.transactions: true, in which case the ADL engine wraps the operation explicitly.

SQLite’s WAL mode allows concurrent readers during a write transaction. A single write transaction is active at any time; concurrent write attempts wait up to busy_timeout milliseconds before failing with SQLITE_BUSY.

5.3 PostgreSQL Store

5.3.1 Connection Management

The PostgreSQL store maintains a connection pool of configurable size (default: 4) using libpq. Connections are established at plugin initialization and reused across requests. Connection health is checked before dispatch; dead connections are replaced.

The conninfo string supports all libpq parameters:

host=localhost port=5432 dbname=myapp user=postgres password=secret sslmode=require

Common sslmode values:

Production deployments SHOULD use sslmode=require or stronger.

5.3.2 Database Organization

Unlike SQLite, PostgreSQL stores all tables in a single database. The ADL registry tables (adl_domains, adl_migration_log), auth tables (users, sessions, roles), and domain resource tables all coexist in the same database and schema (public).

The adl.db_path and adl.app_db_path configuration keys are ignored when using PostgreSQL. Table name prefixing (domain name prefix on resource tables) provides namespace separation.

5.3.3 Type Mapping

PostgreSQL’s richer type system allows more precise mapping than SQLite:

PostgreSQL’s native types provide advantages over SQLite’s text-based storage: UUID enables native UUID operations, NUMERIC preserves decimal precision without string conversion, BOOLEAN stores true/false without integer mapping, DATE/TIMESTAMP enable native date arithmetic, and JSONB enables indexed JSON queries.

5.3.4 Native ALTER TABLE

PostgreSQL supports ALTER TABLE operations that SQLite cannot perform in-place:

The migration generator detects the active store adapter and produces adapter-appropriate DDL. For PostgreSQL, many operations that require the 4-step column rebuild on SQLite are emitted as single ALTER TABLE statements.

5.3.5 Schema Inspection

The PostgreSQL schema verifier uses information_schema instead of SQLite PRAGMAs:

5.3.6 Transaction Semantics

PostgreSQL supports full ACID transactions with true concurrent write access. Multiple write transactions can execute simultaneously (with row-level locking), unlike SQLite’s single-writer model.

Migration plans execute within a single transaction, identical to the SQLite behavior. DDL statements in PostgreSQL are transactional — a failed ALTER TABLE within a transaction rolls back cleanly without leaving partial schema state. This is a significant advantage over SQLite, where DDL statements are not always fully transactional.

5.4 Store-Agnostic Guarantees

Regardless of which store adapter is active, the ADL engine guarantees the following behaviors:

1. The same domain YAML produces the same API behavior on both stores. Field types, validation rules, permissions, state machines, computed fields, and query parameters behave identically. The only differences are in storage representation (§5.2.3 vs §5.3.3) and migration strategies (§5.2.5 vs §5.3.4).

2. Schema verification runs on every boot. The store adapter inspects the actual database schema and compares it against the stored domain model. Missing tables or columns block domain activation. Type mismatches and extra columns produce warnings but do not block activation. See §30.7.

3. All DDL executes in transactions. A failed migration plan leaves the database in its pre-migration state. No partial schema changes are possible.

4. Type validation happens at the API boundary, not the database. The runtime validates email format, UUID structure, URL scheme, and other type-specific rules before any database operation. The database stores the validated value as-is. This ensures consistent validation behavior regardless of the underlying database’s type system.

5. Foreign key constraints are enforced. Both adapters ensure FK constraints are active. SQLite requires PRAGMA foreign_keys = ON on every connection. PostgreSQL enforces FK constraints by default. Cascade and restrict behaviors execute at the database level.

End of Chapter 5.

Next: Chapter 6 — Document Structure