---
title: "Project Database — store and query structured results"
description: "Create a schema, store and read rows, browse tables, and recover from limits, migration conflicts, or an unknown write result."
---

# 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](/en/docs/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](/en/docs/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:
```sh
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`.
```sql
-- 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:
```sh
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.

| Operation | Allowed input | Important limits |
|---|---|---|
| `chat db query` | Exactly one SELECT, INSERT, UPDATE, or DELETE; no RETURNING or OUTPUT | Positional `?` only; `--params` is a JSON array of strings, at most 100, with exactly one value per placeholder |
| `chat db migrate` | 1–100 statements from the migration allowlist below | No parameter bindings; current parent version required |

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.

| Symptom | Next step |
|---|---|
| Project cannot be resolved or access is denied | Check Project-root cwd, Session coverage, active status, and the agent's role. Moving a file does not grant permission. |
| Migration source is rejected or missing | Read `db/_errors.md`; fix the path, header, encoding, or SQL, and allow sync to accept the file before applying. |
| Parent version is stale | Read current schema and prepare a revised migration against that version. Only one child can advance a parent; there is no force option. Keep accepted migration sources unchanged. |
| Migration is reconciling | Follow the returned read/recovery action. The server reconciles its operation and provider ledger; schema reads and the next migration wait until it resolves. SELECT/DML may continue under normal admission rules. |
| `OPERATION_OUTCOME_UNKNOWN` after DML | Read the known primary key and current values before deciding whether another write is needed. Never blindly repeat INSERT, UPDATE, or DELETE. |
| Query is busy | Another live query holds the Project lease. Follow the retry guidance; do not submit concurrent writes to work around it. |
| Result too large | Narrow columns/rows and use bounded pages rather than assuming truncated data is complete. |

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](/en/docs/trust-boundary) and [Projects](/en/docs/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.