zynagent-first application framework

Database#

Actions, hooks, middleware, jobs, and notification handlers receive the same database capability as action.db, hook.db, middleware.db, job.db, and notification.db. Guards do not have database access.

Use generated row operations for routine single-table work, and write SQL directly when the query needs more control. Import generated table types with:

const db = @import("db");

Working with rows#

For routine work on one table, use the generated type from db/types.zig. Zyn uses that type to validate field names and build the SQL while keeping values in PostgreSQL parameters.

For example, an action can create a user, find it again, and later update or delete it:

try action.db.insert(db.User, .{
    .id = id,
    .email = email,
    .active = true,
});

const user = try action.db.find(db.User, .{ .email = email });

const updated = try action.db.update(
    db.User,
    .{ .id = id },
    .{ .active = false },
);

const deleted = try action.db.delete(db.User, .{ .id = id });

The filter struct acts like an equality-based WHERE clause. find, update, and delete need a complete primary key or non-null unique key; extra fields can add scope, such as an organization ID. If a supposedly unique selector matches multiple rows, Zyn returns error.DatabaseSelectorMatchedMultipleRows without changing them.

Use list for a bounded collection:

const users = try action.db.list(
    db.User,
    .{ .active = true, .organization_id = organization_id },
    .{ .limit = 100 },
);

Lists use primary-key order. limit is required and offset defaults to zero. A literal null matches NULL; optional filters use null-safe equality.

insert requires non-null fields without defaults and rejects generated and identity columns. Passing .{} inserts a row using database defaults only.

The API reference contains the exact signatures, return types, and errors for each database operation.

Encrypted columns#

Use the exact zyn.encrypted_text or zyn.encrypted_bytes domain in a migration for values that must be stored as authenticated ciphertext; derived application domains are rejected. Start with the table generator:

zyn g table integrations

Remove DEFAULT uuidv7() from the generated primary key and add the encrypted columns. Retain the timestamps and update trigger. The table declaration becomes:

CREATE TABLE public."integrations" (
    id uuid NOT NULL PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    access_token zyn.encrypted_text NOT NULL,
    private_payload zyn.encrypted_bytes
);

Run zyn db migrate after editing the migration.

The primary key must be non-null, immutable, supplied by the application, and use smallint, integer, bigint, boolean, text, or uuid. It cannot have a default, identity, or generated value. Encrypted tables cannot be partitioned or foreign tables. Encrypted columns cannot have defaults, identities, generated values, or primary or unique constraints. Equality filters require a blind index declared as described below.

Generated types expose decrypted values as []const u8. Typed insert and update calls encrypt supplied values. An encrypted update requires the complete primary key in its filter. find, list, one, and many eagerly authenticate and decrypt complete generated rows, including generated rows nested in joined results. Text values must be valid UTF-8.

try action.db.insert(db.Integration, .{
    .id = id,
    .access_token = token,
});

const integration = try action.db.find(db.Integration, .{ .id = id });

Encryption uses a fresh nonce for every write and authenticates the schema, table, column, and ordered primary-key values. Copying ciphertext to another row or column causes decryption to fail. The schema, table, encrypted-column names, and primary-key values are therefore immutable while encrypted rows exist; rename or key-changing migrations must rewrite those rows under their new identity before normal reads resume. Encrypted columns without blind indexes are not searchable.

Raw exec and exec_count calls do not encrypt values. A raw one or many query decrypts only when its result is a complete generated table type and the query includes the primary key and encrypted columns. Application-owned partial result structs receive the physical ciphertext bytes.

For explicit SQL writes and partial projections, use encrypt_field and decrypt_field on Database or Database.Transaction:

const identity = .{ .id = id };
const sealed = try action.db.encrypt_field(
    db.Integration, "access_token", identity, token,
);
try action.db.exec(
    "UPDATE integrations SET access_token = $1::bytea WHERE id = $2",
    .{ sealed, id },
);
const partial = (try action.db.one(
    struct { id: []const u8, access_token: []const u8 },
    "SELECT id, access_token FROM integrations WHERE id = $1",
    .{id},
)) orelse return error.IntegrationNotFound;
const plaintext = try action.db.decrypt_field(
    db.Integration, "access_token", .{ .id = partial.id }, partial.access_token,
);

The table type and encrypted column name are compile-time arguments. The identity struct contains exactly the complete primary key, with generated field types; composite keys use named fields, independent of struct order. The caller must match this identity to the actual row targeted by SQL. Each row needs its own encrypted value. Cast ciphertext parameters to bytea ($1::bytea) so the driver binds binary values for the encrypted domains. Nullable columns accept and return null; other values and results are byte slices. These operations use the configured database key and the same ciphertext format as typed writes and reads. Do not pass explicitly encrypted values to typed writes, or explicitly decrypt complete generated-row results: those operations already perform encryption or decryption.

Returned buffers use the database query-result allocator and lifetime (the request arena in served requests). Each operation is bounded by max_result_bytes; an allocation exceeding that bound returns OutOfMemory. Field operations execute no SQL and do not log values. Missing keys, invalid ciphertext, mismatched identities, and invalid text fail rather than returning unauthenticated bytes. Operations on completed transactions return TransactionCompleted.

The encryption key is derived from the independently generated data key in the selected profile's encrypted data-key file. The ciphertext envelope records a key identifier and rejects unknown keys. Replacing the profile master key rewraps the same data key without changing database ciphertext.

Blind indexes#

Declare an exact-match index on an encrypted column in a SQL migration:

CREATE TABLE identities (
    id uuid NOT NULL PRIMARY KEY,
    email zyn.encrypted_text NOT NULL
);

SELECT zyn.add_blind_index(
    'public.identities', 'email',
    unique_index => true
);

unique_index defaults to false. The helper creates a framework-managed companion column, index, and consistency constraint. A unique index enforces uniqueness of the lookup token across concurrent writes, not of the randomized ciphertext. Nullable columns permit multiple nulls. A non-null column with a unique blind index supports find; other blind indexes support list:

try action.db.insert(db.Identity, .{ .id = id, .email = email });
const identity = try action.db.find(db.Identity, .{ .email = email });
const identities = try action.db.list(
    db.Identity,
    .{ .email = email },
    .{ .limit = 100 },
);

Matching compares exact bytes. Callers apply the same normalization before writes and lookups; Zyn does not interpret email addresses or other identifiers. Typed insert and update maintain ciphertext and tokens in one statement, including when used in transactions. Encrypted filters are supported only by find and list; updates and deletes use a primary or plaintext unique selector. Encrypted updates still require the complete primary key.

Reads of complete generated rows authenticate and decrypt values and validate their companion tokens. Indexed lookups also compare the decrypted value with the requested value. A mismatch returns InvalidDatabaseBlindIndex rather than a partial or unverified result. Generated row types retain companion columns in physical column order for joined reads, but typed writes reject caller-supplied companion values. Application-owned partial projections do not perform these checks.

The index uses a full-length HMAC-SHA256 token with a separately derived key, scoped to the schema, table, column, and encrypted value kind. It exposes equality and frequency within that column. Searchability is not appropriate for every secret, particularly small sets of possible values. Token collisions fail closed; unique token constraints can reject a colliding value. Index keys derive from the same durable data key as database encryption, with a separate derivation context. Credential master-key rotation preserves tokens and uniqueness; replacing the durable data key with stored values present is unsupported.

Add indexes before storing non-null values in the source column. The helper locks the table and rejects a populated source atomically; it does not decrypt or backfill existing data. Existing populated columns require an application-managed rewrite into a newly indexed column or table. There is no in-place backfill command. Inherited, partitioned, and foreign tables are not supported.

Adding or removing indexes requires coordinated application deployment: regenerate db/types.zig and stop old writers before resuming with the new schema. Use typed writes for indexed fields. Raw SQL and encrypt_field do not maintain tokens; stale tokens can cause missed lookups. Keys and plaintext are never supplied to the SQL migration helpers.

Remove an index with:

SELECT zyn.drop_blind_index('public.identities', 'email');

Removal deletes the companion column and its indexes and constraints, including uniqueness enforcement. It does not change the encrypted source values. Regenerated types no longer permit filtering by that field, but reads by the primary key continue to decrypt it. Duplicate additions and missing removals fail rather than silently changing a declaration. Schema dumps and resets preserve blind-index declarations.

Writing SQL#

Write SQL for joins, aggregates, expressions, custom ordering, and bulk changes.

Always use PostgreSQL placeholders and pass values separately:

const post = try action.db.one(
    Post,
    "SELECT id, title FROM posts WHERE id = $1",
    .{id},
);

const posts = try action.db.many(
    Post,
    "SELECT id, title FROM posts ORDER BY created_at DESC LIMIT $1",
    .{@as(i64, 100)},
);

const changed = try action.db.exec_count(
    "UPDATE posts SET published = true WHERE author_id = $1",
    .{author_id},
);

one returns an optional row, many returns a slice, exec ignores the changed-row count, and exec_count returns it when PostgreSQL provides one.

Flat result structs map by column name. Missing fields use defaults or return error.FieldColumnMismatch; extra columns are ignored. Nested structs map joined columns positionally in outer-field order:

const article = try action.db.one(
    struct {
        article: db.Article,
        author: db.Author,
    },
    \\SELECT articles.*, authors.*
    \\FROM articles
    \\JOIN authors ON authors.id = articles.author_id
    \\WHERE articles.id = $1
,
    .{id},
);

Each nested struct consumes consecutive columns in declaration order. Too few columns return error.FieldColumnMismatch; extra columns are ignored.

Transactions#

var tx = try action.db.begin();
defer tx.cancel();

try tx.update(db.Post, .{ .id = id }, .{ .published = true });
try tx.jobs.enqueue("publish_post", .{ .id = id }, .{});
try tx.notifications.deliver_later("post_published", .{ .id = id });
try tx.storage.purge(old_blob);

try tx.commit();

All transaction database operations and the transaction-scoped jobs, notifications, and storage capabilities share one pinned PostgreSQL connection. commit commits and releases it. cancel is infallible and idempotent, making defer tx.cancel() safe. Calls after completion return error.TransactionCompleted.

Generated types and result memory#

db/types.zig contains complete table-shaped types and metadata used by typed operations. Generated types deliberately cannot be serialized as JSON. Define application-owned resource types and select or copy only their public fields.

Values returned by one and many live in request-scoped memory until that request finishes. Results outside request dispatch, including seeds, use database-lifecycle memory. Do not free results individually or retain request results after the request.

Each one or many call defaults to at most 16 MiB of mapped memory; many also defaults to at most 10,000 rows. Exceeding either effective limit returns error.DatabaseResultTooLarge. SQL is still responsible for LIMIT and pagination because these limits do not reduce rows transferred by PostgreSQL.

Configuration and logging#

Production uses the resolved db.profiles.production entry in zyn.zon, with common values inherited from db.defaults. It requires database and may override host, port, username, and password_credential. Managed deployments normally use the zyn identity over a private peer-authenticated Unix socket; an explicit password credential enables loopback SCRAM mode.

Set log_queries = true on a database profile to log application queries. Records include SQL placeholders and duration, but never inspect or print bound values.

Framework-owned schema#

The reserved zyn schema stores the encrypted-column domains, jobs, recurring schedule cursors, notifications, notification deliveries, blob metadata, and authentication users, identities, sessions, and one-time tokens. Its generated definition is committed as db/zyn_schema.sql. Applications may reference zyn.blobs(id) and zyn.users(id) but must not modify framework-owned objects directly. The schema is excluded from application schema dumps and db/types.zig.

The cache uses a separate PostgreSQL database with its own framework schema. Its entries are not included in application database schema dumps, resets, or durable-data restores.