SQL Surfaces

Lix exposes logical application data through typed SQL relations:

History reads endpoint differences along a commit’s first-parent chain; lix_commit_ancestry() reads the reachable commit set, and lix_diff compares one relation across two arbitrary commits. lix_registered_schema and its history function provide schema discovery; lix_key_value and its history function provide shared repository metadata. lix_change records repository-wide activity; History documents it together with the history functions.

lix_file represents regular file contents only. Its public columns are id, path, directory_id, name, and content, plus the standard lixcol_* bookkeeping columns. path is an absolute, literal UTF-8 path and content is the file's bytes. lixcol_created_at and lixcol_updated_at are public, read-only timestamps on both lix_file and lix_directory. Path characters such as spaces, %, #, ?, and @ are not URL-encoded. Lix does not represent symbolic links, device nodes, sockets, or other non-regular filesystem entries as lix_file rows. Executable and other permission bits are not part of the file contract.

The engine defines a Lix logical path as an absolute /-separated sequence of literal UTF-8 segments. Empty segments, ., .., / within a segment, NUL, and a trailing slash are invalid; / itself is only the root directory. All other segment text is preserved exactly: the engine does not URL-decode, case-fold, or Unicode-normalize paths. Filesystem adapters diagnose names that the target host cannot represent.

The checkpoint and diff relations are read-only. lix_diff() exposes row_ref, the relation's typed primary-key columns, diff_type, and paired from_<column> / to_<column> relation columns. Pass row_ref in an array to the recovery, undo, redo, and apply functions or as the optional third argument of lix_create_checkpoint(title, comment [, row_refs]). See Checkpoints and Diff commands.

For working changes, use lix_diff(relation). Its actual baseline is exposed as lix_branch.working_base_commit_id; it can differ from the latest marked checkpoint after a fork.

The rule is: lixcol_ prefixes only engine-owned system metadata, while relation-specific payload always uses ordinary names such as diff_type, from_path and to_path. Registered user schemas reject column names beginning with lixcol_ or containing _lixcol_; this keeps system metadata mechanically distinguishable even after from_/to_ side prefixing.

See Conversations and comments for row/commit discussions, reply scope, and deletion behavior.

The executable column contract

The SQL engine is backed by DataFusion. Query information_schema.columns for the executable public contract instead of inferring types from Arrow or JSON Schema names:

Column-resolution errors retain DataFusion's discovery guidance: close misses get a Did you mean ... suggestion, while other misses include the complete Valid fields are ... listing.

SELECT column_name, data_type, is_nullable, column_default,
       lix_insert_policy, description
FROM information_schema.columns
WHERE table_name = 'lix_file'
ORDER BY ordinal_position;

description is what the column means, in prose: a registered schema's own description annotations for its table and columns, and the engine's words for the composed views and the lixcol_* bookkeeping columns. It is NULL where nothing was written. information_schema.lix_surfaces carries the same for each relation, so a tool can present a table and its columns the way the schema author explained them.

Lix reports logical SQL types through data_type, including TEXT, UUID, BYTEA, BIGINT, DOUBLE PRECISION, BOOLEAN, JSONB, TIMESTAMPTZ, and ROW_REF. JSONB, UUID, and row references retain their logical types even though their execution representation is UTF-8. Scalar types other than the opaque ROW_REF are executable as explicit CAST targets in SELECT, INSERT, and UPDATE. Construct row references with lix_row_ref(...) or consume typed references returned by Lix functions. Bound Lix writes use those canonical names; read expressions accept DataFusion's wider cast dialect.

Historical lix_as_of results expose custom non-key columns as nullable because an older commit can predate a required column added later. Current-state relation nullability continues to follow its schema.

History functions are discoverable through information_schema.table_functions, which reports their argument signature and result columns. They do not appear in information_schema.tables or information_schema.columns.

Query information_schema.lix_surfaces to distinguish the complete Lix-owned public SQL contract without relying on naming conventions. surface_class is one of RELATION, TABLE_FUNCTION, COMMAND_SINK, or SCALAR_FUNCTION; relation_kind distinguishes BASE from VIEW. Registered schema relations are bases, while the composed lix_file, lix_directory, lix_branch, and lix_change projections are views. The capability columns report which SQL operations each surface accepts:

SELECT surface_name, surface_class, relation_kind, can_read, can_insert
FROM information_schema.lix_surfaces
ORDER BY surface_name;

The fixed Lix surfaces are classified as follows. Every additional registered schema contributes another RELATION / BASE surface.

Standard SQL value expressions such as CURRENT_TIMESTAMP are supported SQL syntax, not Lix-owned scalar-function surfaces, and are therefore omitted from information_schema.lix_surfaces.

Classify by SQL shape, not merely by whether data is computed dynamically. Unparameterized, table-shaped projections are views. Row producers invoked in the FROM clause with function syntax are table functions. Apply, restore, revert, revert-range, checkpoint creation, undo, and redo are top-level mutating table functions and use the exact SELECT commit_id FROM ... command shape.

JSON-backed columns report data_type = 'JSONB'; row references report ROW_REF, and timezone-aware timestamps report TIMESTAMPTZ. JSONB values return as native JSON in SELECT and RETURNING results. The lix_value_kind extension has been removed from both information_schema.columns and information_schema.table_functions. is_nullable describes values returned by reads; column_default and lix_insert_policy separately describe whether a write may omit a column. A defaulted ID, for example, is non-null when read, may be omitted on insert, and rejects an explicit NULL.

lix_insert_policy describes omission on INSERT:

CONDITIONAL covers deliberate alternative forms: filesystem rows can use a path or their directory/name fields, and typed rows derive identity from their public primary-key columns. These policies describe omission only; is_nullable still describes read values.

Typed schema surfaces

Registering a Schema v1 document with key: "acme_task" produces:

User properties become ordinary typed columns:

SELECT id, title, done
FROM acme_task
WHERE done = false;

Lix bookkeeping columns use the lixcol_* prefix. Relations are scoped to the session's active branch. Open another session to work on another branch.

lixcol_author_id is the lix_account.id of the account that last wrote the row's current state. It is read-only and changes when another account edits the row. Query the account directly, for example JOIN lix_account AS a ON a.id = c.lixcol_author_id for a comment c. This does not require a lix_change scan.

A row written with lixcol_untracked = true has a lixcol_change_id identifying its current-state write. It has no lixcol_commit_id, and its change ID does not identify a retained lix_change record. Do not join untracked rows to lix_change to recover write metadata.

Every public history read calls lix_history with a relation-name text literal and an optional commit-id argument; there are no generated history functions or bare history table aliases. History documents the endpoint columns, position ordering, composite-key lookups, and removals.

Schema discovery and interoperability

lix_registered_schema records persisted schema registrations. Built-in definitions also ship with the engine and are available without registration; an older database may not contain registry rows for built-ins added by a newer engine. Use information_schema.lix_surfaces and information_schema.columns to discover the executable SQL catalog, including those built-ins. For persisted application schema definitions:

SELECT schema_key, value -> 'primary_key' AS primary_key
FROM lix_registered_schema
ORDER BY schema_key;

The registry contains application schemas and built-in schemas with public SQL relations. Internal storage schemas remain embedded in the engine for validation but are not listed in lix_registered_schema. Use information_schema.lix_surfaces to discover public SQL relations; the registry is not an inventory of engine storage structures.

Applications and plugins cannot register the exact Schema v1 key lix or a key beginning with lix_; their base or generated SQL names occupy the namespace reserved for Lix bootstrap schemas. Use an owner-specific prefix such as acme_task.

lix_key_value and lix_history('lix_key_value') are public for shared repository settings and interoperability metadata.

Files

lix_file exposes logical files, including their byte content:

User columns are id, path, directory_id, name, and content.

For text content, bind a text parameter and cast it to BYTEA:

INSERT INTO lix_file (path, content)
VALUES ('/orders.md', CAST($1 AS BYTEA));

SELECT CAST(content AS TEXT)
FROM lix_file
WHERE path = '/orders.md';

For the server wire protocol, the $1 parameter is a plain text value such as { "kind": "text", "value": "# Orders\n" }. Use a Uint8Array parameter when the file contains arbitrary non-UTF-8 bytes, and read content with row.content as Uint8Array.

length(content) is character-oriented even though content is BYTEA. Use the standard OCTET_LENGTH(content) function to verify the stored byte count; for example, aé— has length 3 and octet length 6.

File history records changes between complete composed file states; see History.

Directories

Directories use the same three scopes:

User columns are id, path, parent_id, and name. Directory and file paths share the same canonical syntax: non-root paths do not end with a slash. The typed SQL surface determines whether /data names a directory or a file.

Inserting a file at /a/b/c.txt creates /a and /a/b when needed. Insert directories explicitly only when they should exist before any file.

Directory history follows the same composed-projection semantics as file history; see History.