# Access application data

:::status{of="dal"}
:::

This guide owns **data access**, not GizDB SQL or server administration. Start here to
connect an application to GizDB, PostgreSQL, SQLite or ODBC, query typed records and carry
committed changes to GizWeb. [Model your data](model.md) defines records, validation and
policies. [Use GizDB](data.md) covers the native engine, SQL, indexes, search and protocols.

The database is the source of truth. A `Source` reports which operations it can execute;
not every adapter supports snapshots, CDC, schema changes or server-specific SQL.

## Choose the right data layer

The [web guide](web.md) tutorial uses level 1. Each level below it gives more control and needs more code. Use
the highest level that does the job, and go down for one query, not for the whole app.

| Level | Use it for | You write |
| --- | --- | --- |
| 1. Model (`Data`) | An app that owns its data and wants the table, forms, checks, JSON and tools from one type | A record type, then `Data.find`, `Data.one`, `Data.insert`, `Data.write` and `Data.follow` |
| 2. Typed query (DAL) | Joins, aggregates, keyset pages, expected revisions, transactions | `Query.Prepared`, `Query.page`, `Query.require` ([typed queries](#open-a-database-and-query-it-with-types)); or a Shape's own query, `Model.select` |
| 3. Connector (`Source`) | The same typed query on another engine | `Access.native` (GizDB), `sqlite` (`gizdb:ingest/sqlitesource`), `Source.postgres`, `Source.odbc` |
| 4. SQL text | A script, an admin write, a statement that no builder covers | `Query.statements(text)`, `Query.sql(text, …)`, `Data.statements(text, params)` |
| 5. Driver | One embedded SQLite file and no layer at all | `Sqlite.open(path)` and `db.query(sql, params)` |

Level 1 keeps its records in the GizDB database that the app's own process serves: a folder, `data/db` for a
`Pages.app`, with no separate server to start. With DATABASE_URL set to a gizdb or PostgreSQL server, the same records live
there: the app reads and writes them by SQL, its pages follow their changes through a Relay, and it serves on one
thread. A SQLite file cannot hold an app (it has no schema for the app's people), so `Pages.app` stops with a message
that says so. Levels 2 to 5 reach the other engines.

At level 1, the records of a Shape are read with a filter whose values are bound.

```typescript gizlang
import * as Model from "gizdb:model";
import * as Data from "gizdb:data";

/** A saved note. */
@Model.Table("notes")
export type Note = { @Model.Key id: string; title: string; body: string; };

/** The notes with the title TITLE. */
export function titled(db: Data.Db, title: string): Vec<Note> {
    const shape = Model.shape<Note>();
    return Data.find(db, shape, "title = $1", [title], Model.rowReader<Note>(shape))!;
}
```

At level 5 you map each column yourself. SQLite gives every column as optional text, and you bind the
values (`$1`), never put them into the SQL text:

```typescript gizlang
import {Option} from "gizlang:core";
import * as Sqlite from "gizlang:sqlite";

function main(): void {
    const db = Sqlite.open("notes.db")!;
    db.exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, title TEXT NOT NULL, body TEXT NOT NULL)")!;
    const rows = db.query("SELECT id, title FROM notes WHERE title = $1", [Option.some("Milk")])!.rows;
    print(`${rows.length} notes`);
}
```


## Open a database and query it with types

`Access.local` opens an embedded database in a folder: the data actor runs in your process. One
engine opens a folder at a time: a second opener, in this or another process, fails until the
first closes. The threads of one process (`Task.thread`) share the engine: each thread calls
`Access.connect(local.endpoint, local.identity, local.secret)` for its own Connection. Other
processes reach the data through a gizdb server, with `Source.postgres`.

This program makes a table, queries it with a typed query, updates a row with an expected
revision and rolls a transaction back.

```typescript gizlang
import {System} from "gizlang:system";
import {Result} from "gizlang:core";
import * as Access from "gizdb:access";
import * as Typed from "gizdb:typed";
import * as Query from "gizdb:query";
import {Value, Type, DbError, parse} from "gizdb";

type Task = { id: i64; title: string; revision: i64; };

/** The parameters of the query, and the record of a result row. */
function params(done: bool): Vec<Value> throws DbError { return [Value.boolean(done)]; }
function task(row: Vec<Value>): Task throws DbError {
    return Task(try Typed.integerCodec().decode(row[0]), try Typed.textCodec().decode(row[1]), try Typed.integerCodec().decode(row[2]));
}

function main(sys: System): void {
    const dir = sys.temporaryDir()!;
    const db = Access.local(sys, dir, "guide", 67108864)!;
    const c = db.connection;
    c.write(parse("CREATE TABLE tasks(id BIGINT PRIMARY KEY, title TEXT NOT NULL, done BOOLEAN NOT NULL, revision BIGINT NOT NULL)")!, [])!;
    const rows = [[Value.int(1), Value.text("Write the guide"), Value.boolean(false), Value.int(0)],
                  [Value.int(2), Value.text("Test the guide"), Value.boolean(false), Value.int(0)]];
    c.write([Query.insert("tasks", ["id", "title", "done", "revision"], rows)!], [])!;

    // A typed query: columns with codecs, one parameter, decoded records.
    const id = Query.field("tasks", "id", Typed.integerCodec());
    const title = Query.field("tasks", "title", Typed.textCodec());
    const revision = Query.field("tasks", "revision", Typed.integerCodec());
    const done = Query.field("tasks", "done", Typed.booleanCodec());
    let tree = Query.where(Query.table("tasks"), Query.equalTo(done, Query.parameter(0, Typed.booleanCodec())));
    tree = Query.projection(tree, "id", id); tree = Query.projection(tree, "title", title); tree = Query.projection(tree, "revision", revision);
    tree = Query.orderBy(tree, id, false, false);
    const outputs = [Typed.Slot("id", Type.int(), false), Typed.Slot("title", Type.text(), false), Typed.Slot("revision", Type.int(), false)];
    const open = Query.Prepared(tree, [Typed.Slot("done", Type.boolean(), false)], outputs,
        (input: bool): Result<Vec<Value>, DbError> => { return params(input); },
        (row: Vec<Value>): Result<Task, DbError> => { return task(row); }, "");
    const source = Access.native(c, "guide");
    for (const task of open.fetch(source, false)!) { print(string(task.id) + " " + task.title + " r" + string(task.revision)); }

    // An update that expects the revision it read: exactly one row, or a conflict.
    const finish = Query.require(parse("UPDATE tasks SET done = true, revision = revision + 1 WHERE id = $1 AND revision = $2")![0], 1)!;
    c.write([finish], [Value.int(1), Value.int(0)])!;
    try {
        void try c.write([finish], [Value.int(1), Value.int(0)]);
        print("stale write accepted");
    } catch (error) {
        print("stale write: " + error.message);
    }

    // A transaction has one owner. Its writes are visible to others only after commit.
    const tx = c.begin()!;
    tx.write([finish], [Value.int(2), Value.int(0)])!;
    tx.rollback()!;
    print("open after rollback: " + string(open.fetch(source, false)!.length));

    db.close()!;
    dir.close();
}
```

```text output
1 Write the guide r0
2 Test the guide r0
stale write: required 1 affected rows, got 0
open after rollback: 1
```

Rules:

- Write with an expected revision. Read the revision, then write with `Query.require(statement, 1)`.
  A concurrent write makes the count 0, and the whole write rolls back with a conflict. Read
  again and retry.
- Use `Connection.begin` for more than one write that must succeed together. `commit`,
  `rollback`, `savepoint`, `rollbackTo` and `release` act on that one transaction. An idle
  transaction expires after its lease.
- Page with keys, not offsets: `Query.page(source, query, input, keyFields, after, size)`.
- Load relations in one query for each relation: `Query.include(source, parents, relation)`.
- Raw SQL with typed results is the escape hatch: `Query.sql(text, parameters, outputs, encode, decode)`.
  `Query.statements(text)` parses a script of several statements (DDL, admin writes).
- Write a Shape's records with the SQL text of `Model.insertRowsSql(shape, n)` (or `Store.insert(db, shape, record)` for one record; db/store.giz also has `migrate`, `records`, `record` and `one`) and read them with
  `Model.select(shape, filter, parameters, encode, of)` (key order). The same statement and query
  run on each Source: `Access.native(connection, id)` (GizDB), `sqlite(database, id)` (db/ingest/sqlitesource.giz, so a program
  without SQLite does not link it),
  `Source.postgres(connection, id)` and `Source.odbc(connection, id, semantics)` (SQLite and MySQL
  drivers are tested). `Source.semantics(dialect)` gives the
  collation, time zone and NULL typing of `postgres`, `sqlite` and `mysql`.
- A watch needs a change log. GizDB gives one (`source.watch`). A SQLite or PostgreSQL client
  source refuses the watch as a missing capability. A watched query has no `ORDER BY`.


## Open an application Store

The low-level `gizdb` tutorial above embeds an engine directly. `gizdb:embedded` opens a
folder and serves it from the calling process when no server owns it. `gizdb:store` is a
client: it **cannot** start an unserved folder. It also accepts `.db` (SQLite), `:memory:`
and `postgres://USER:PASSWORD@HOST:PORT/DATABASE` locations, sharing one SQL-oriented API.

```typescript gizlang title="app-data.giz"
import {System} from "gizlang:system";
import {Option} from "gizlang:core";
import * as Embedded from "gizdb:embedded";

export function main(sys: System): void {
    let db = Embedded.open(sys, "app-data")!;
    db.exec("CREATE TABLE IF NOT EXISTS notes (id INT PRIMARY KEY, body TEXT)")!;
    db.query("INSERT INTO notes (id, body) VALUES ($1, $2) ON CONFLICT (id) DO NOTHING",
        [Option.some("1"), Option.some("hello")])!;
    const result = db.query("SELECT body FROM notes WHERE id = $1", [Option.some("1")])!;
    for (const row of result.rows) { print(row[0] ?? "NULL"); }
    db.close();
}
```

Run with `giz app-data.giz`; it prints `hello`, including on later runs. `query` binds the
`$1`-style parameters and returns text-or-NULL rows plus an `affected` count for mutations;
`exec` runs SQL without parameters or returned rows. `select` also returns column names;
`transaction`, `tables`, `columns` and `close` are in `db/store.giz`.

For a worker, import `gizdb:store` and call `Store.open(sys, "app-data")` *after* an owning
server or embedded process is running. For an external PostgreSQL-protocol server, use
`Store.open(sys, "postgres://...")`; the URL optionally takes `&pool=N`. The Gizlang PostgreSQL client supports certificate-checked TLS via `sslmode=require`,
`verify-ca` or `verify-full`. See [PostgreSQL TLS](#postgresql-managed-postgresql-and-tls)
and [server configuration](gizdb-server.md#enable-tls).

Use [typed queries](#run-typed-queries-from-gizlang) with `gizdb:typed` codecs and
`gizdb:model` for record contracts and schema migrations. `follow<T>` does not make a
primary key automatically; define the key and constraints needed by the application.


## Run typed queries from Gizlang

:::status{of="dal"}
:::

`gizdb:query` connects Gizlang record types to tables. `gizdb:typed` holds the value
contracts (`Slot`, the codecs). It does not import the SQL engine, so a web model or an
application that only checks and converts values does not link the database:

- `follow<T>(engine, conn, table)` (`gizdb:sql/session`, next to `connection`) makes or changes
  the table so that its columns agree with the record type `T`. It returns the statements that it ran.
- `Query.sql<P, R>(text, parameters, outputs, encode, decode)` makes a typed query. `P` is the
  parameter type, and `R` is the type of one result row.
- The codecs of `gizdb:typed` (`integerCodec`, `textCodec`, `booleanCodec`, `floatCodec`, `decimalCodec`,
  `optionalCodec` and others) convert between Gizlang values and database values.

Make `typed.giz`:

```typescript gizlang title="typed.giz"
import {System} from "gizlang:system";
import {Result} from "gizlang:core";
import {Value, Type} from "gizdb:value";
import {DbError} from "gizdb:error";
import {open} from "gizdb:core/engine";
import {connection, follow} from "gizdb:sql/session";
import * as Typed from "gizdb:typed";
import * as Query from "gizdb:query";

/** The record type is the table definition. */
type Task = { id: i64; title: string; done: bool; };

/** The parameters of the query, and the record of a result row. */
function params(from: i64): Vec<Value> throws DbError { return [Value.int(from)]; }
function task(row: Vec<Value>): Task throws DbError {
    return {
        id: Typed.integerCodec().decode(row[0])!,
        title: Typed.textCodec().decode(row[1])!,
        done: Typed.booleanCodec().decode(row[2])!,
    };
}

export function main(sys: System): void {
    sys.files()!.makeDirectories("typed-data")!;
    let e = open(sys.directory("typed-data")!)!;
    let c = connection("postgres");

    // follow<T> creates or changes the table so that it agrees with the record type.
    for (const sql of follow<Task>(e, c, "task")!) { print(sql); }
    c.run(e, "INSERT INTO task VALUES (1, 'Plan', true), (2, 'Build', false), (3, 'Ship', false)", [], 0, 0u)!;

    // A typed query: an i64 parameter in, Task records out.
    const open = Query.sql<i64, Task>(
        "SELECT id, title, done FROM task WHERE done = false AND id >= $1 ORDER BY id",
        [Typed.Slot("from", Type.int(), false)],
        [Typed.Slot("id", Type.int(), false), Typed.Slot("title", Type.text(), false), Typed.Slot("done", Type.boolean(), false)],
        (from: i64): Result<Vec<Value>, DbError> => { return params(from); },
        (row: Vec<Value>): Result<Task, DbError> => { return task(row); })!;

    const params = open.values(1)!;
    const rows = c.query(e, open.tree, params)!;
    for (const row of rows.rows) {
        const task = open.decode(row)!;
        print(string(task.id) + " " + task.title + " done=" + string(task.done));
    }
    e.close()!;
}
```

Run it one time in a new folder:

```sh
giz typed.giz
```

```text output
CREATE TABLE "task" ("id" BIGINT, "title" TEXT, "done" BOOLEAN)
2 Build done=false
3 Ship done=false
```

- `Query.sql` accepts exactly one SELECT. It parses the text one time. `open.values(input)`
  checks the parameters against their slots before the query runs.
- `c.query(e, tree, params)` runs the parsed query and returns the rows.
- To rename a field later, write `/** @was title */` above the new field name. `follow`
  then renames the column, and keeps its data.
- The table that `follow` makes has no primary key. Thus a second run of this program inserts
  the rows again.

## Connect external databases

Choose `gizdb:store` to give app code one database API. Choose `gizdb:source`
when a typed query must run on a different provider, with an explicit capability contract.
A `Source` does not turn every external database into the `DATABASE_URL` store of GizWeb.

| Provider | Connection | App Store | Typed Source | Native change log in Source |
| --- | --- | --- | --- | --- |
| Embedded GizDB | `gizdb:embedded` or `gizdb:access` | Yes | `Access.native` | Yes, native watches |
| GizDB server | PostgreSQL protocol | Yes | `Source.postgres` | No, when reached through `Source.postgres` |
| PostgreSQL | `gizlang:postgres` | Yes | `Source.postgres` | No |
| SQLite | `gizlang:sqlite`, `.db`, `:memory:` | Yes | `gizdb:ingest/sqlitesource` | No |
| MySQL / MariaDB | `gizlang:odbc` | Not via `DATABASE_URL` | `Source.odbc` | No |
| Other ODBC providers | Installed ODBC driver | Not via `DATABASE_URL` | `Source.odbc` if capabilities match | No |
| JSON / HTTP JSON | `Source.document`, `Source.httpJson` | No | Read-only | No |
| CSV / Parquet files | `Source.files` | No | Read-only | No |

`Source.postgres` uses the PostgreSQL wire protocol for GizDB or a PostgreSQL server;
it is **not** the same as GizDB's `Access.native` watch-enabled source. ODBC support
means a generic driver boundary, not certified SQL Server or Oracle behavior.

### PostgreSQL, managed PostgreSQL and TLS

Set the URL outside your program. `Store.open` uses `gizlang:postgres` and supports
connection pooling. The URL parser expects an explicit port and does not accept general
URL percent-escaping; do not assume it supports every libpq URI extension.

```sh
export DATABASE_URL='postgres://app:secret@db.example.com:5432/app?sslmode=verify-full&sslrootcert=/etc/my-ca.pem'
```

```typescript gizlang title="external-postgres.giz"
import {System} from "gizlang:system";
import {Option} from "gizlang:core";
import * as Store from "gizdb:store";

export function main(sys: System): void {
    let db = Store.open(sys, sys.environment("DATABASE_URL")!)!;
    const result = db.query("SELECT name FROM users WHERE id = $1", [Option.some("1")])!;
    for (const row of result.rows) { print(row[0] ?? "NULL"); }
    db.close();
}
```

The Store URL also accepts `&pool=N` after its first query parameter. Never embed passwords in
source files or logs. `gizlang:postgres` **does support TLS** in the current SDK:
`sslmode=require`, `verify-ca` and `verify-full` negotiate TLS and check the server
certificate and host name. Supply `sslrootcert=FILE` when your certificate chain needs
an explicit authority. This is stricter than libpq's usual `sslmode=require` behavior.
`sslmode=disable`, `allow` and `prefer` do not request TLS in this implementation.
The parsing and option round trips are tested in `tests/sdk/postgres/url.test.giz`.
Test network trust and authentication against the actual target before deployment.

For a typed Source instead of a `Store.Database`, use this inside a function with a
`System sys` parameter:

```typescript gizlang
import * as Pg from "gizlang:postgres";
import * as Source from "gizdb:source";
import {System} from "gizlang:system";

function customers(sys: System): void {
    const connection = Pg.connect(sys, Pg.optionsOf(sys.environment("DATABASE_URL")!));
    const source = Source.postgres(connection, "customers");
    // Run a Query.Prepared on source. Close connection when the work is done.
    connection.close();
}
```

See [Open a database and query it with types](#open-a-database-and-query-it-with-types)
for the complete typed-query execution path.

### SQLite, including an in-memory database

For a standalone SQLite file, use `Sqlite.open("app.db")` or
`Store.open(sys, "app.db")`; `Store.open(sys, ":memory:")` is also supported.
Use `Sqlite.Database.transaction` or `Store.Database.transaction` to keep related
writes atomic. `Store.query` binds `$1` parameters and returns optional text cells.
SQLite uses SQLite's own types and concurrency semantics, not GizDB's.

For `Source` queries, open `gizlang:sqlite`, then adapt it with
`sqlite(database, "table")` from `gizdb:ingest/sqlitesource`.
The separate adapter keeps applications without SQLite from linking its native library.

**GizWeb `Pages.app` rejects a `.db`-file `DATABASE_URL`** because its people and
access records require schemas unsupported by that app path. Direct DAL or standalone
Store usage is a different case and can use SQLite.

### MySQL, MariaDB and other ODBC drivers

`gizlang:odbc` uses the system ODBC driver manager (`libodbc.so.2` on Linux) plus an
installed database-specific driver. A MySQL/MariaDB example:

```typescript gizlang
import {System} from "gizlang:system";
import * as Odbc from "gizlang:odbc";
import * as Source from "gizdb:source";

export function main(sys: System): void {
    const dsn = sys.environment("ODBC_DSN")!;
    const connection = Odbc.open(sys, dsn)!;
    const source = Source.odbc(connection, "items", Source.semantics("mysql")!)!;
    print(source.identity);
    connection.close();
}
```

Set `ODBC_DSN` to an ODBC connection string using the installed driver, server,
credentials and database. Do not assume a generic ODBC driver preserves every PostgreSQL
or GizDB type. `tests/db/data-access/engines/run.giz` includes SQLite ODBC and MariaDB
ODBC integration paths when their drivers and test server are available. Other engines,
such as SQL Server, require their own driver and compatibility testing. `db/ora/client.giz`
provides a separate Oracle TNS client, not a general `DATABASE_URL=oracle://` adapter.

### Documents, files and HTTP data

`Source.document(identity, slots, json)` exposes a JSON array as typed rows.
`Source.httpJson(sys, host, port, request, identity, slots, bodyBytes)` reads a JSON
response and applies a bounded body limit (at most 16 MiB); it does not follow redirects.
`Source.files` reads supported file formats through a directory, pattern, format and
resource budget. These adapters do not provide SQL transactions or native change logs.
File and document readers mainly push down projections; they do not claim full SQL
join, filter or aggregate pushdown. The executor or caller must handle those operations.

## Use typed queries and transactions

Choose `Data.find`, `Data.one`, `Data.insert` and `Data.write` for record-oriented CRUD.
`Model.shape<T>()` is the single declaration of columns, form checks and row decoding.
`Data.install` creates required tables. For many related writes, use `Data.within`:
its callback runs under one transaction, and a rejection rolls the transaction back.

For custom SQL use `Query.sql<P, R>` with declared parameter and result slots;
`Query.Prepared.fetch(source, input)` runs the same typed query on compatible Sources.
Use `Query.page` for keyset pagination, `Query.include` for relationship loading,
and `Query.require(statement, 1)` with a revision predicate for optimistic concurrency.
A zero-row expected-revision write is a conflict, not a successful overwrite.
One transaction belongs to one owner/source. Closing a reader, a transaction and a
connection releases its resources; an idle Access transaction can expire.

For direct Store operations, `query(sql, params)` binds values and returns rows;
`exec(sql)` runs SQL without bound parameters or a row result, and `select` includes
column names. `Store.Database.transaction` keeps writes on one connection.
Never concatenate untrusted values or identifiers into SQL text.

## Scope data access to the caller

`Data.Db` without a scope is a trusted application connection. At an untrusted page,
API or agent boundary, use `Data.as(db, space, principal, now)` to obtain a
`Data.Scoped` context. `Data.rows`, `Data.create`, `Data.update`, `Data.patch` and
`Data.remove` enforce the `gizdb:gate` and `gizdb:policy` rules. Model annotations
for row and field access are in [Model your data](model.md#judge-rows-and-fields-with-the-model).
A direct `Store.query` or `Source` SQL call does not automatically enforce that scope.
Database roles and RLS are a separate defense when the provider supports them.

## Live changes from database to browser

**Live is supported, but it depends on how writes reach the database.**
A live view is not created by `DATABASE_URL` alone. A page must follow records and
serve its subscription route; see [GizWeb live updates](web.md#live-updates).

1. Register `Data.follow(sys, db, shape, filter, keys)` **before** reading the rows. The
   returned position covers changes committed between registration and the first render.
2. Put `Web.live(out, topic, position)` attributes on the HTML element being followed.
3. Register `Web.subscriptions` and answer with `Web.subscribed`. Recheck the caller's
   authorization before computing patches.
4. Render changed keys with a keyed `Html.Patch`. A lost watch or a `whole` invalidation
   requires re-reading the page or result set, not replaying an old patch.
5. Call `Data.unfollow`, or `Watch.close`, when the subscriber leaves.

The browser kernel maintains a page subscription channel at `/_s` (WebSocket when used);
server-side `Web.subscribed` also exposes a long-poll subscription route. Keyed patches
update only changed DOM elements and keep unrelated inputs and focus. See the working
[comments application](https://github.com/gizai/gizlang/tree/main/web/comments):
`main.giz` installs the subscription route, `store.giz` opens follows, and
`view.ghtml` writes keyed patches.

### GizDB native watches

`Access.observe`/`Data.follow` track committed GizDB changes. `Data.watch` exposes a
`Watch.wait` and `Watch.close` wrapper; a lost subscription is followed again and the
caller must re-read the selection. A watched select does not include `ORDER BY`.
The internal web contract caps active watches per database and expires idle
watch leases; the documented defaults are 64 watches and 60 seconds without a poll.
A filtered native follow delivers keys when rows enter or leave its selection,
as tested by `tests/web/data/records/run.giz`. Re-read changed keys to establish
current membership and visibility; a lost watch requires a complete refresh.

The GizDB SQL function `changes(...)` is a **separate** WAL-based committed change feed:
read [GizDB live SQL](data.md#live-queries) for offsets and `GZ001` resync semantics.

### Live from external PostgreSQL or SQLite

`Data.connect(store, feed, name)` uses a `Relay` when its Store does not expose the
native engine. Construct one with `Relay.relay(sys, store, keep)`, as
`web/pages.giz` does. Relay creates `feed` and `feed_history` tables and writes
change notices **in the same transaction** as the DAL data mutation. This works for
writes performed through the DAL and for code which publishes the corresponding notice.
An independent SQL client writing directly to PostgreSQL or SQLite does **not**
automatically populate that feed. For ordered changes from an external CDC consumer,
use `Relay.ingest(source, position, notices)` **after the source's
commit**. The `source` name must identify a single ordered change stream (include
its partition when applicable); `position` is a positive monotonically increasing
i64 offset. `notices` is a `Vec<Relay.Notice>`; one source commit may
change many rows, so publish all of its notices at the same offset. An empty
vector advances the CDC checkpoint for an irrelevant source commit without
emitting a false row-change event. The Relay
stores only the highest accepted offset per source in `feed_external` and appends
all notices **atomically in the Relay store**. A replay,
duplicate or older position returns `false`; a new event returns `true`.

```typescript gizlang
import * as Relay from "gizdb:relay";

// Call after the external transaction commits.
function publish(feed: Relay.Relay): bool {
    return feed.ingest("orders/public/partition-0", 101,
        [Relay.Notice("orders", "42", false)]);
}
```

A consumer must translate its provider's native LSN or binlog coordinate to an
ordered positive offset. Use one offset per committed batch, not one per row. A table-wide event with `key=""` invalidates the whole
selection, not a fake empty-key row. `Data.changed` waits across Relay instances;
when using nonblocking `waitMs=0`, a different process may see the feed head up to
25 ms later. Use a bounded wait or poll again. The offset/notice atomicity does
**not** make the external source commit atomic with the Relay's commit. Configure
CDC retention and replay from the consumer's committed source offset, and recover
with a full resnapshot when a source log or Relay cursor has expired.

This is the **CDC receiving boundary**, not a built-in PostgreSQL logical decoder,
MySQL binlog reader or ODBC change-capture driver. Those provider-specific consumers
must still be supplied; `Source.postgres` and `Source.odbc` declare `changeLog=false`.

Relay cursors can be trimmed when retention is exceeded; on loss, resnapshot and
resubscribe. Do not treat a notification as a current authorized row image: query the
record again in the caller's scope. The live delivery contract is not an exactly-once
external event-processing system.

## Source contracts and multi-database limits

`Source` reports its own `Capabilities`: schema, projection, filter, aggregation, join,
window, ordering, limit, snapshots, change log, mutation, transaction, cancel and
compute. `Source.admit` rejects requests a provider cannot execute; `Source.describe`
returns a capability/schema version contract. `Source.pinSchema` checks a discovered
schema against the expected typed slots.

### Bounded typed reads across independent databases

`Query.Prepared.fetchAcross(sources, params, maxRows)` runs **one already typed query**
on each compatible Source and concatenates its decoded result rows in the supplied
source order. `Source.federated(sources, false, false)` checks source identities and
SQL semantics before the first query is issued. Every result must fit the explicit
combined `maxRows` budget (1 to 1,000,000); exceeding the budget is an error, not
silent truncation. The underlying `fetchBounded` path closes the cursor even when
an oversized result or a decoding error aborts the read.

```typescript gizlang
import * as Query from "gizdb:query";
import {Source} from "gizdb:source";

function across<P, R>(query: Query.Prepared<P, R>, primary: Source, archive: Source, parameters: P): Vec<R> {
    return query.fetchAcross([primary, archive], parameters, 10000);
}
```

This is read-only fan-out with common output contracts, **not** distributed JOIN,
global ORDER BY, a federated aggregate, globally consistent snapshots, atomic
writes or a distributed commit coordinator. Filtering and ordering apply only
within each source; results from different sources are simply concatenated.
Use explicit provenance and a caller-owned merge algorithm where cross-source
identity, deduplication or ordering matters. The source adapters still enforce
their own authentication and row policies; DAL scope is not automatically copied.

A PostgreSQL or ODBC `Source` currently advertises `snapshot=false` and
`changeLog=false` in the generic text adapter even if the underlying server supports
snapshots or native CDC. `Source.federated` checks each source's identity, collation,
timezone, dialect and NULL semantics. More than one source with `atomic=true` is
rejected without a distributed coordinator; `live=true` requires change logs from
every source. This contract does not by itself implement a global distributed SQL
planner, cross-database ACID or automatic cross-engine CDC.

### Exact feature boundary

| Workload | Current mechanism | Boundary |
| --- | --- | --- |
| Typed read on one database | `Prepared.fetch` / `fetchBounded` | Provider's own transaction and policies |
| Typed read across matching sources | `Prepared.fetchAcross` | Bounded sequential fan-out, **no** shared snapshot, distributed SQL JOIN or global sorting |
| Multi-table writes within one database | `Source.transaction` / `Data.within` | One transactional owner |
| Atomic writes across independent databases | Not supplied | `Source.federated(..., true, ...)` rejects multiple sources; a verified distributed coordinator is required |
| Live from DAL-controlled writes | `Data.follow` / `Relay` | Changes must be published transactionally |
| Live from externally committed changes | `Relay.ingest` | Needs an upstream source-specific CDC reader and retained offsets; `Source.postgres` / ODBC do not automatically supply one |
| ML without SQL | `gizai:ml/*` | Native CPU estimator algorithms; GPU/NPU tensor programming is a separate [Compute](compute.md) capability |

Do not rely on a best-effort dual-write or a sequential SQL fan-out as if either
provided cross-database ACID. For global transactions, select a single database
as transactional owner or build and validate a distributed coordinator separately.

## Migrations and operational boundaries

The [model migration guide](model.md#change-the-model) explains `Schema.changes`,
`Store.migrate` and `Migrate.apply` with bounded backfill and explicit contract steps.
Do not assume every external provider accepts the same migration DDL. The
`gizdb migrate` CLI has its own supported adapters; [server migration](gizdb-server.md#migrate-and-check-data)
is not a guarantee of a generic reverse ETL connector for every `Source`.
For ML training and inference entirely in code, with no database, see
[Machine learning](ml.md). For backups, recovery, roles, TLS and quotas of a GizDB server, use the
[server operations guide](gizdb-server.md). Provider-specific backups of external
PostgreSQL, SQLite and MySQL remain the owner's responsibility.

## Troubleshooting

| Symptom | Check | Correct response |
| --- | --- | --- |
| `Store.open` cannot reach a folder | Is another process serving that GizDB folder? | Use `gizdb:embedded` to start it or `gizdb serve` on a dedicated service. |
| PostgreSQL TLS negotiation fails | URL's `sslmode`, host and trusted CA | Set `sslrootcert` and use a certificate for the actual host. |
| MySQL/ODBC does not connect | System driver manager, installed driver, DSN and credentials | Test `Odbc.open` directly before the typed Source. |
| `Source` rejects a query | Provider capability, result slots and SQL semantics | Simplify pushdown or run the operation in an engine supporting it. |
| Typed decoding fails | `Typed.Slot` and real column type/nullability | Correct schema or codec; avoid untyped coercion. |
| A revision write affects zero rows | Stale revision or missing row | Re-read, reauthorize and retry as a new transaction if appropriate. |
| Changes are not reaching the page | Follow registration, `/_s` route, patch keys and Relay | Distinguish DAL-published writes from external direct SQL writes. |
| Live cursor expired | Change retention or watch lease | Re-read the result, resubscribe and use the new cursor. |
| Two independent DBs cannot commit atomically | Multiple Source identities | Use one transactional owner or design an explicit distributed coordinator. |

## Reference

| Module | Responsibility |
| --- | --- |
| `gizdb:data` | Model CRUD, scopes, transactions, live follows and change delivery |
| `gizdb:model` | Record Shape, fields, validation and DDL (see [Model guide](model.md)) |
| `gizdb:store` | Client API for served GizDB folders, PostgreSQL URLs and SQLite files |
| `gizdb:embedded` | Serve a local GizDB folder if unserved; otherwise connect as client |
| `gizdb:access` | Native GizDB actor connection, transaction and watch |
| `gizdb:typed` | Parameter and result slots, codecs, typed contracts |
| `gizdb:query` | Prepared queries, bounded multi-source fan-out, SQL AST, keyset pages, relations, revision guards |
| `gizdb:source` | Native, PostgreSQL, ODBC, file, JSON and HTTP adapters and capabilities |
| `gizdb:ingest/sqlitesource` | SQLite-to-Source adapter |
| `gizdb:relay` | Transactional notice feed and ordered external CDC intake (`Relay.ingest`) |
| `gizlang:postgres` | PostgreSQL wire client, prepared statements, pooling, TLS |
| `gizlang:sqlite` | Embedded SQLite database, queries and transactions |
| `gizlang:odbc` | Generic ODBC connection through the system driver manager |
| `gizweb` | HTTP, actions, forms, page rendering and browser live subscriptions |

For complete application code see [GizWeb](web.md), and for concrete integration
checks see `tests/db/data-access/engines/run.giz`, `tests/db/relay/run.giz` and
`tests/web/data/records/run.giz`.

---

## Data-access levels and portability example

This section records the verified examples and measurements for this topic. The source and test paths in this document remain authoritative.

## 1. Architectural Overview of the 5 DAL Levels

Gizlang's Data Access Layer provides five progressive levels of control. Developers write at the highest level that fulfills their requirements and drop down to lower levels for specific queries without rewriting the application architecture:

```
┌────────────────────────────────────────────────────────┐
│ Level 1: Model (Data)                                  │
│ High-level CRUD, auto-derived table, live web bindings │
└───────────────────────────┬────────────────────────────┘
                            │
┌───────────────────────────▼────────────────────────────┐
│ Level 2: Typed Queries (DAL)                           │
│ Keyset pagination, prepared joins, transaction control │
└───────────────────────────┬────────────────────────────┘
                            │
┌───────────────────────────▼────────────────────────────┐
│ Level 3: Connector (Source)                            │
│ Unified query interface across GizDB, PG, SQLite, ODBC │
└───────────────────────────┬────────────────────────────┘
                            │
┌───────────────────────────▼────────────────────────────┐
│ Level 4: Parametric SQL Statements                     │
│ Raw SQL execution with strict parameter binding ($1)   │
└───────────────────────────┬────────────────────────────┘
                            │
┌───────────────────────────▼────────────────────────────┐
│ Level 5: Direct Drivers                                │
│ Low-level embedded SQLite C-API file handles           │
└────────────────────────────────────────────────────────┘
```

---

## 2. Level 1: Model Operations (`gizdb:data`)

Used for primary application data operations where the record type defines the table structure:

```typescript gizlang
import * as Model from "gizdb:model";
import * as Data from "gizdb:data";

@Model.Table("notes")
export type Note = {
    @Model.Key id: string;
    title: string;
    body: string;
};

export function findNotesByTitle(db: Data.Db, title: string): Vec<Note> {
    const shape = Model.shape<Note>();
    return Data.find(db, shape, "title = $1", [title], Model.rowReader<Note>(shape))!;
}
```

---

## 3. Level 2: Typed Queries & Keyset Pagination

When queries require joins across multiple tables, aggregation, or high-throughput keyset pagination:

`Query.page(source, query, input, keyFields, after, size)` runs a Prepared query one page at a time. It orders by
`keyFields` and continues after the key values `after`, so it never scans skipped rows the way `OFFSET` does. The
`Page` it returns holds at most `size` rows and the key of its last row as `next` (none on the last page). The keys may
be columns the result does not show. See typed queries for a complete program.

---

## 4. Level 3: Multi-Engine Connectors (`Source`)

The same typed queries execute interchangeably across different storage backends:
- `Access.native`: Embedded or networked GizDB engine.
- `Source.postgres`: Remote PostgreSQL wire protocol database.
- `Source.sqlite`: Local SQLite file database.
- `Source.odbc`: Legacy enterprise databases via ODBC bridge.

---

## 5. Levels 4 & 5: Raw SQL & Embedded Drivers

For specialized analytical queries, administrative DDL, or embedded single-file tooling:
- **Level 4 (SQL Text)**: Safe parametric queries using `$1`, `$2` parameter substitution (`Data.statements(sql, params)`).
- **Level 5 (Direct Driver)**: Zero-abstraction C-level SQLite handle bindings (`Sqlite.open(path)`).