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 when its first migration is applied. Reading the schema does not create a database. This is not a local SQLite file or an automatic backend for HTML.

Prerequisites and permissions

A Viewer Project role can read the schema and run SELECT. Collaborator and Admin can also run migrations, INSERT, UPDATE, and DELETE. These roles apply to covered Projects and human access. A Session with cross-Project read permission can read schema and run SELECT without membership in the target; writes and migrations fail with CROSS_PROJECT_READ_ONLY. A Team owner without Project membership cannot operate the Database through the human API.

Database CLI operations require an active Project. For covered Projects and human access, Archived returns a frozen-Database error, Done (completed) and Planning return project_not_active, and a deleting Project is treated as not found. To resume, set the Project back to In progress (Projects). The WebUI Database tab also shows Database unavailable (Database browsing is available only for active projects.) for any non-active Project and does not show rows.

You do not need to give the agent D1 credentials.

Create a table and store one result (inside a Session)

chat db is a Session command. It has no flags for Team, Project, Database ID, or Session. schema and query resolve the Project from the current directory; migrate resolves it from the migration file path. A path outside a Project root or an ambiguous path fails.

Replace acme/customer-research with a real Project in the Session's coverage or read-only targets, then read the current schema:

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

The response data contains schema_sql, schema_version, migration_count, and head_migration. Covered Projects also have a read-only db/schema.sql projection of the same schema: an empty database starts with -- aachat-schema-version: root, and a migrated one starts with a sha256:... version.

For a cross-Project read, use the target root under $AA_WS_DIR/aachat/teams/<team>/projects/<target> (AA_WS_DIR is the workspace root). No db/ directory or schema.sql projection is required. Read the schema, then use SELECT with the existing tables and columns; skip the migration and write examples below.

Do not edit db/schema.sql. An edit is not synchronized; a diagnostic is written to db/_errors.md and the file is restored. To change the schema, create db/migrations/<id>.sql. The example below is saved as db/migrations/add_customer.sql after you confirm the database is empty. If a schema already exists, copy the entire current version exactly into the parent header and adapt the SQL to the 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 starts at byte 0. Use a regular UTF-8 file with no BOM or NUL bytes, and do not use a symlink. A migration ID uses lowercase letters, digits, _, and -, starts with a lowercase letter or digit, and is at most 128 characters. File sync stores the SQL source; it does not apply it to the database. Once the file has synced, apply it explicitly, then write and read back rows:

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"]'

Expected results:

• migrate returns data.disposition applied (or reconciled if it was already applied), with the new schema_version and migration_count. The following schema shows the table and index.
• query returns data.result_set.columns and data.result_set.items (an array of rows), plus changes and last_row_id. The first SELECT returns c1, Ada, active; the SELECT after the UPDATE returns c1, Ada, reviewed.

Read each actual JSON response before continuing. Repeating an INSERT with the same primary key does not create a new record. For a disposable practice row, run chat db query --sql 'DELETE FROM customer WHERE id = ?' --params '["c1"]', then SELECT the same key to confirm it is gone.

For long SQL or parameters, use --file <path> or --stdin instead of --sql, and --params-file <path> (a JSON array of strings) instead of --params. query is not read-only: it also runs changing SQL as given.

Operate from outside a Session

A person logged in to aachat, or an external coding agent, can operate the same Database without a Session. These commands do not work inside a Session; use chat db there. Outside a connected repository, pass the Team with --team.

aachat db schema customer-research --team acme
echo '{"sql":"SELECT id, name FROM customer WHERE status = ?","params":["active"]}' \
  | aachat db query customer-research --team acme --stdin
aachat db migrate customer-research ~/aachat/teams/acme/projects/customer-research/db/migrations/add_customer.sql --team acme

• query takes {"sql": "...", "params": [...]} JSON on --stdin.
• The migrate FILE must be the target Project's db/migrations/<id>.sql in the common root (~/aachat/teams/<team>/projects/<project>/, configurable with AACHAT_HOME) or in a connected repository. The digest of the whole source is sent, so a source that has not synced, or was edited after syncing, fails.
• Permissions come from your own Project role (Viewer: schema and SELECT only).
• aachat project database <project> --team <team> only reads the schema catalog; it does not run SQL.

Query and migration SQL rules differ

Both accept at most 100 KiB of SQL. Reserved _aachat_, sqlite_, _cf_, and pragma_ relations, database-qualified object names, and arbitrary SQLite extensions are not allowed.

Queries cannot run DDL or PRAGMA. A SELECT result is limited to 1,000 rows and 1 MiB decoded; exceeding either returns an error, not a partial result. Select only the columns you need, use WHERE, and use a small LIMIT. If you use LIMIT, use a static non-negative integer, not a placeholder or expression. Query UPDATE and DELETE cannot use ORDER BY or LIMIT.

The migration allowlist is: ordinary CREATE TABLE and CREATE INDEX; ALTER TABLE to add, drop, or rename a column or rename a table; single-object DROP TABLE or DROP INDEX; INSERT, UPDATE, and DELETE without RETURNING or OUTPUT; and PRAGMA defer_foreign_keys = ON. That PRAGMA is a migration-only exception. SELECT, arbitrary PRAGMA, views, triggers, and explicit transaction control cannot be used in a migration. Being in the allowlist does not mean every SQLite dialect form is accepted.

View results in the WebUI

Open the Project's Database tab, select a table in the bar at the bottom, and inspect its columns and rows. It is read-only: there is no SQL editor or update action.

• Click a column header to toggle ascending and descending sort.
• Choose 25, 50, or 100 rows per page (default 100) and move with Previous / Next. The rows-per-page selector is hidden on narrow screens.
• The selected table's total row count is shown: on wide screens a range and total such as Rows 1–100 of 1,234, on narrow screens Total 1,234. An empty table shows 0; when the count is not loaded or fails, it shows Total unavailable.
• The table, page, page size, and sort are kept in the URL. With nine or more tables, use Choose table to search and select.
• When data changes, Updates available appears; click it to load the new rows. Refresh reloads the catalog and rows. A schema change reloads the catalog.

Empty strings (Empty), NULL, and filled cells are displayed differently. Click a non-NULL cell to open its full value and use Copy. NULL cells have no detail view.

To specify a table in an agent request, use Copy table reference next to the selected table name in the bottom bar, then paste it into your request. It copies the Database path and exact table name together.

Database: aachat/teams/<team>/projects/<project>/db/
Table: "customers"

The Table line is a JSON string; a table is not a separate file. You can copy a reference to an empty table or when its rows fail to load, as long as a table can be selected. The reference does not include page or sort settings and does not grant access.

Recover without duplicating writes

A failure also prints one JSON object on stdout: {"ok":false,"error":{"code","message",...},"data":...}. Read error.message, error.hint when present, and data. For chat db, data.mutation_state says whether the request was rejected (not accepted) or unknown (outcome unknown). For aachat db, data.change.state is not_applied or unknown. A timeout alone does not prove that a write failed.

Deleting local migration or schema files does not undo applied data. There is no instant rollback or backup mechanism. Project deletion waits for accepted queries and unresolved migrations to finish and for the physical database to be deleted. See also Trust boundary and Projects.