# Project databases

A Rogue project can have **one optional Cloudflare D1 database**. A database
always belongs to an initialized project and its owner. There are no unattached
account databases. Free owners may have one database across their projects; Pro
owners may have ten. Active administrators are exempt from owner quotas. Pending
and uncertain provisioning reservations count toward capacity.

## Owner and application access

The project owner administers SQL through `rog`, REST or MCP using their Rogue
credentials and the `projects:write` scope (`agent:read` for listing). Project
membership alone does not grant database administration. Rogue holds the
account-scoped Cloudflare management token; customers never receive it.

A running app gets its own explicitly selected `DB` binding. That binding is a
runtime capability: it has no reusable password and cannot create/delete other
D1 databases or administer the Rogue account. Never copy the owner's API key into
the app. Rogue checks project, owner, provider account and database readiness at
binding, deployment and dispatch. Apps cannot supply raw provider database IDs.

**D1 does not provide SQL users, GRANT/REVOKE, or per-table SQL roles.** The native
binding permits SQL, including schema changes, inside its one database. The
separation here is between owner management credentials and app SQL capability;
it is not a read-only/DDL restriction. Implement end-user permissions in the app,
and parameterize user values. An application compromise can affect its own
attached database. Keep backups and compatible migrations.

## Administer with rog

Commands target the **project UUID**. `create` returns the database UUID used for
app bindings and exact deletion confirmation. No command silently chooses remote
SQL. `query` can mutate data: inspect the SQL before invoking it.

```sh
rog db create PROJECT_UUID --yes
rog db list PROJECT_UUID
rog db tables PROJECT_UUID --remote
rog db schema PROJECT_UUID --remote --table users
rog db rows PROJECT_UUID --remote --table users --limit 50 --offset 0
rog db query PROJECT_UUID --remote --sql 'SELECT * FROM users WHERE id = ?' --params '[123]'
rog db shell PROJECT_UUID --remote --yes
rog db seed PROJECT_UUID --remote --file seed.sql --yes
rog db import PROJECT_UUID --remote --file data.sql --yes
rog db migrate PROJECT_UUID --remote --name 0001_initial --file migrations/0001_initial.sql --yes
rog db dump PROJECT_UUID --remote --out backup.sql --yes
rog db dump PROJECT_UUID --remote --out schema.sql --schema-only --yes
```

The remote SQL shell accepts multiline statements and `.tables`, `.schema` and
`.quit`. It saves no history. Query results are JSON; table listing and row
inspection use the same authenticated API. Query responses are capped at 1 MiB;
use filters, pagination or a dump for large datasets. Queries are at most 100,000
characters with at most 100 parameters. Cloudflare's D1 limits still apply.

SQL imports/seeds accept UTF-8 files up to 25 MiB, split on complete SQL statement
boundaries into at most 90 KiB requests. Quoted strings, comments and trigger
bodies are preserved. Each request is a batch; the **whole file is not one
transaction**. Imports stop on the first failure and report confirmed batch and
byte progress. Never automatically retry a mutation after a transport error:
inspect tables before deciding how to resume. Use D1-compatible SQLite SQL,
without explicit BEGIN/COMMIT wrappers. Seed design should make reruns explicit.

Named migrations are at most 90,000 characters, with an immutable SHA-256 ledger
in `_rogue_migrations`. Repeating the same name/checksum returns already applied;
changed SQL under an existing name is refused. The migration and ledger entry are
submitted together. Transaction-control keywords and ledger modifications are
reserved. SQLite `CREATE TRIGGER ... BEGIN ... END` bodies are supported; do not
wrap the migration itself in a transaction. Quoted values and SQL comments do
not count as transaction commands. Keep files small and apply them in filename order.

Dumps use D1's snapshot export and a short-lived signed download. Exports can
briefly pause queries. `rog` polls that same export, downloads without sending
Rogue credentials, refuses redirects, and creates a new private file. Existing
files are never overwritten. Failed downloads remove the partial output. Backups
may contain secrets or personal data: store them privately outside source Git.

## Local D1, with no remote credentials

Install Wrangler in the project, then:

```sh
rog db local init
rog db local migrate
rog db local query --sql 'SELECT name FROM sqlite_schema WHERE type = '\''table'\'''
rog db local import --file seed.sql
rog db local shell
rog db local dump --out local-backup.sql
```

`local init` creates a minimal Wrangler configuration only if none exists. For
an existing app, declare a local binding named `DB` and `migrations_dir` in its
Wrangler config. Local commands use installed Wrangler, force `--local`, and
persist to the project's `.wrangler/state`. The local shell accepts one complete
command per line. They do not create a Rogue or Cloudflare database. Use that
same state path when starting the application, including built Vinext previews:

```sh
npx wrangler dev --config dist/server/wrangler.json --persist-to .wrangler/state --env-file /dev/null
```

See [Cloudflare local D1 development](https://developers.cloudflare.com/d1/best-practices/local-development/).
Local Wrangler migrations use their own ledger; importing/validating locally
does not apply a remote migration. Database data survives code deployment,
rollback and app deletion. Before removing a database, export it and delete all
apps bound to it, then explicitly confirm its UUID:

```sh
rog db delete PROJECT_UUID --remote --confirm DATABASE_UUID --yes
```

This permanently removes SQL data. The project stays. Database deletion must
finish before the project can be deleted or transferred; code cleanup never
silently detaches or deletes its database. After a provisioning timeout, inspect
`rog db list`; an explicit subsequent create reconciles the same reserved
provider identity instead of allocating a second database.

REST: `/api/v1/projects/{id}/databases`, `/api/v1/databases/{id}/query`,
`/migrations`, `/export`, and DELETE `/api/v1/databases/{id}`. MCP/SDK equivalents:
`create_project_database`, `list_project_databases`, `query_project_database`,
`migrate_project_database`, `export_project_database`, `delete_project_database`.