Project Database — store and query structured results

Use Project Database for structured results that agents and people need to reuse: customer records, research observations, or inventories. Use Shared Documents for the explanation and decisions around those rows. Each ordinary Project has its own server-managed Cloudflare D1 database, created lazily by its first migration. Reading the schema does not create a database. This is not a local SQLite file or an automatic backend for HTML.

Prerequisites and permissions

Ask an agent in a running Session that covers the active Project to operate the database. The Session's Project role controls access: Viewer can read schema and SELECT; Collaborator and Admin can also apply migrations and INSERT, UPDATE, or DELETE. Archived, completed, and deleting Projects do not accept new database operations.

chat db is a Session command. There are no Team, Project, Database ID, or Session selector flags: schema and query resolve the Project from the current directory, while migrate resolves it from the canonical migration file path. Run the examples from the Project root. The outer aachat project database command provides the catalog, not arbitrary SQL execution. Agents do not need direct D1 credentials.

Create a table and save a result

Replace acme/customer-research with an existing Project covered by the Session. Start by reading its current schema:

cd aachat/projects/acme/customer-research
chat db schema

The read-only db/schema.sql projection starts with -- aachat-schema-version: root for an empty database, or a sha256:... version after migration. Do not edit that file. For this example, confirm the database is empty and create db/migrations/add_customer.sql with the following exact content. If it already has a schema, copy its exact current version into the parent header and adapt the SQL to existing tables; do not reuse root.

-- aachat-parent: root
CREATE TABLE customer (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  status TEXT NOT NULL
);
CREATE INDEX customer_status ON customer(status);

The parent header must start at byte 0. Save UTF-8 without a BOM or NUL, using a regular file rather than a symlink. Migration IDs use lowercase letters, digits, _, or -, start with a letter or digit, and have at most 128 characters. File sync saves the migration source; it does not apply SQL. Apply it explicitly, then insert and inspect one row:

chat db migrate db/migrations/add_customer.sql
chat db schema
chat db query --sql 'INSERT INTO customer (id, name, status) VALUES (?, ?, ?)' --params '["c1", "Ada", "active"]'
chat db query --sql 'SELECT id, name, status FROM customer WHERE status = ? ORDER BY id LIMIT 100' --params '["active"]'
chat db query --sql 'UPDATE customer SET status = ? WHERE id = ?' --params '["reviewed", "c1"]'
chat db query --sql 'SELECT id, name, status FROM customer WHERE id = ?' --params '["c1"]'

After a successful migration, the schema has a new version and the table and index appear. The first SELECT returns c1, Ada, active; after the UPDATE, the final SELECT returns c1, Ada, reviewed. Read the actual JSON response at each step before continuing. Repeating the INSERT with the same primary key is not a fresh record. For a deliberately disposable example row, remove it with chat db query --sql 'DELETE FROM customer WHERE id = ?' --params '["c1"]', then SELECT by that key to confirm absence.

Query and migration have different SQL rules

Both accept at most 100 KiB of SQL. Neither permits reserved _aachat_, sqlite_, _cf_, or pragma_ relations, qualified database object names, or arbitrary SQLite extensions.

Query cannot run DDL or PRAGMA. SELECT returns at most 1,000 rows and 1 MiB of decoded result data; an oversized result is an error, not a partial page. Choose explicit columns, a WHERE clause, and a small LIMIT. A supplied LIMIT must be a static nonnegative integer, not a placeholder or expression. UPDATE and DELETE cannot use ORDER BY or LIMIT in this query interface.

The migration allowlist is ordinary CREATE TABLE and CREATE INDEX; ALTER TABLE to add, drop, or rename a column or rename a table; DROP TABLE or DROP INDEX for a single object; INSERT, UPDATE, and DELETE without RETURNING or OUTPUT; and PRAGMA defer_foreign_keys = ON. This PRAGMA is a migration-only exception. SELECT, arbitrary PRAGMA, views, triggers, and explicit transaction-control scripts are not migration operations. The allowlist does not make every SQLite dialect feature valid.

Browse results in the WebUI

Open the Project's Database tab, select a table, and inspect its columns and rows. The browser is read-only: it has no SQL editor or mutation controls. Click a column heading to cycle sorting, and use pages of 25, 50, or 100 rows (default 100). Table, page, page size, and sort are retained in the URL.

A data change displays Updates available; refresh to load new rows. Schema changes refresh the catalog. Empty strings, NULL, and BLOB values are distinct. If a page exceeds 1 MiB, try 25 rows; if that still fails, ask the agent to SELECT fewer columns or smaller values.

Recover without duplicating a write

Read the response's error, reason, and next action. A timeout does not prove that a write failed.

Deleting a local migration or schema file does not undo applied data. Project deletion waits for accepted queries and unresolved migrations, and for physical database deletion. It is not an instant rollback or backup mechanism. See Trust Boundary and Projects.

Empty states and full cell values

In the WebUI Database browser, No database yet means the first migration has not initialized the database: ask an Agent to create and apply it using the steps above. No tables yet means there are no user tables; create a table through a migration and refresh the browser. Reading the schema alone does not initialize a database.

Click a non-NULL cell to open its full value and use Copy if needed. Empty string is distinguishable from NULL and can open the detail; a NULL cell has no detail button. This is a read-only inspection surface, not an editor. After an Agent changes rows or schema, use Refresh/Updates available to load the current state.