PerspectPerspectDocs
Go to Admin
View as Markdown

Database and Migrations

Perspect sites can be backed by a Cloudflare D1 database and an R2 bucket, provisioned from the organization's resource pool and attached through the security-gated release flow. This guide covers the full lifecycle: create → bind → migrate → query.

D1 concepts

  • Org pool — databases belong to your organization, not a specific site. database_create adds one to the pool.
  • Binding — a binding attaches a database to a specific site's Worker as env.<binding_name>. One database can bind to one site at a time.
  • Release gate — binding, migrating, and deleting databases require Security Auditor review plus Release Manager deployment. This is intentional: these operations change deployed Worker configuration or live D1 data.
  • database_query — the only direct agent action against a live D1; it is read-only. All writes go through migration files and the release gate.

1. Provision a database

database_create

No arguments. The platform auto-names the database and adds it to your org pool. The response includes the new database_id:

{
  "id": "db_abc123",
  "status": "unbound",
  "created_at": "2026-04-01T10:00:00Z"
}

List all databases (including attachment state) at any time:

database_list

Returns each database's id, name, status, the site it's bound to (if any), the binding name, and the last applied migration version.

2. Bind the database to your site

Binding wires the database into your site's Worker as an environment binding, which means it triggers a deploy. That deploy goes through the release gate.

First, prepare the binding via the tool:

{
  "tool": "database_bind",
  "database_id": "db_abc123",
  "binding_name": "DB"
}

binding_name becomes the identifier your Worker code uses: env.DB. Must use only letters, digits, and underscores and must start with a letter or underscore. The default is "DB".

Then hand off to the release gate:

{
  "tool": "release_record_security_review",
  "deploy_intent_type": "database_bind",
  "deploy_intent_details": { "database_id": "db_abc123" },
  "max_severity": "none",
  "summary": "Binding existing empty D1 database to site Worker. No schema or data changes.",
  "blocking_findings": [],
  "deploy_requested": true
}

Then deploy:

{
  "tool": "release_deploy",
  "deploy_intent_type": "database_bind",
  "deploy_intent_details": { "database_id": "db_abc123" },
  "deploy_requested": true
}

After deploy completes, env.DB is accessible in your Worker code.

3. Write migration files

Migrations are SQL files in the migrations/ directory of your app source. The platform applies them in filename order and tracks applied migrations in a d1_migrations table inside your D1.

Name files numerically: 0001_create_users.sql, 0002_add_index.sql, and so on.

Write migration files with app_write_file (after app_init):

{
  "tool": "app_write_file",
  "path": "migrations/0001_create_users.sql",
  "content": "CREATE TABLE users (\n  id INTEGER PRIMARY KEY AUTOINCREMENT,\n  email TEXT UNIQUE NOT NULL,\n  created_at TEXT DEFAULT (datetime('now'))\n);"
}

D1 SQL constraints to follow:

  • Do NOT use BEGIN/COMMIT/ROLLBACK — each migration file is auto-wrapped in a transaction.
  • Do NOT use PRAGMA statements.
  • Do NOT use ATTACH/DETACH/SAVEPOINT.
  • Do NOT use the _cf_ prefix for table or index names (reserved by D1).

4. Check migration status

Before deploying, see which migrations are pending:

{
  "tool": "database_migrate_status",
  "database_id": "db_abc123"
}

Returns which files in migrations/ have been applied and which are pending. Requires app_init first (reads migration files from the sandbox).

You can optionally pass migrations_dir if your migrations live outside the default migrations/ directory:

{
  "tool": "database_migrate_status",
  "database_id": "db_abc123",
  "migrations_dir": "db/migrations"
}

5. Deploy migrations through the release gate

Migrations are deployed the same way as source code — through Security Auditor review and Release Manager deploy — but with deploy_intent_type: "database_migrate".

{
  "tool": "release_record_security_review",
  "deploy_intent_type": "database_migrate",
  "deploy_intent_details": { "database_id": "db_abc123" },
  "max_severity": "low",
  "summary": "Creates users table. Additive schema change, no destructive operations.",
  "blocking_findings": [],
  "deploy_requested": true
}

Then deploy:

{
  "tool": "release_deploy",
  "deploy_intent_type": "database_migrate",
  "deploy_intent_details": { "database_id": "db_abc123" },
  "deploy_requested": true
}

Destructive migrations and risk acceptance

If a migration drops tables or deletes data, the security review must include a risk_acceptance block:

{
  "tool": "release_record_security_review",
  "deploy_intent_type": "database_migrate",
  "deploy_intent_details": { "database_id": "db_abc123" },
  "max_severity": "high",
  "summary": "Drops the legacy_sessions table. User confirmed backup exists.",
  "blocking_findings": [
    {
      "severity": "high",
      "location": "migrations/0005_drop_legacy_sessions.sql",
      "issue": "DROP TABLE is irreversible without a restore",
      "remediation": "User confirmed a restorable backup exists"
    }
  ],
  "risk_acceptance": {
    "backup_confirmed": true,
    "destructive_action_confirmed": true,
    "confirmation": "I understand this permanently changes live D1 data and I have a restorable backup.",
    "note": "User confirmed backup in Cloudflare dashboard before proceeding"
  },
  "deploy_requested": true
}

backup_confirmed and destructive_action_confirmed must both be true, and the confirmation string must match exactly. These are only valid for database_migrate and database_delete intents.

6. Query data

After migrations are applied, use database_query for read-only inspection:

{
  "tool": "database_query",
  "database_id": "db_abc123",
  "sql": "SELECT id, email, created_at FROM users ORDER BY created_at DESC LIMIT 10"
}

Parameterized queries use ? placeholders:

{
  "tool": "database_query",
  "database_id": "db_abc123",
  "sql": "SELECT * FROM users WHERE email = ?",
  "params": ["hello@example.com"]
}

params accepts strings, numbers, and booleans. Only a single SELECT (or WITH ... SELECT) statement is allowed per call. Multi-statement or write queries are rejected.

R2 buckets

The same org-pool model applies to R2 buckets. Buckets are auto-named.

bucket_create    # provision a new bucket
bucket_list      # list all org buckets with attachment status

A site's Worker accesses its bucket as env.BUCKET. Only one R2 bucket can be bound to a site at a time. Attach, detach, and delete operations must be done manually through the admin console — they are not available as agent actions.

What agents can't do

  • Unbind a database or bucket — manual admin action only.
  • Delete a database or bucket — manual admin action only.
  • Execute write SQL directly — all writes go through migration files and the release gate.
  • Bypass the release gate for binding or migration changes — these are deployment-level changes.

Flow summary

database_create
→ database_bind (prepare binding)
→ release_record_security_review (deploy_intent_type: "database_bind")
→ release_deploy (deploy_intent_type: "database_bind")
→ app_init
→ app_write_file (migrations/0001_*.sql)
→ database_migrate_status (verify pending)
→ release_record_security_review (deploy_intent_type: "database_migrate")
→ release_deploy (deploy_intent_type: "database_migrate")
→ database_query (verify data)

Where to go next