# Ephemera — Relational database on Cloudflare (D1 + CLI)

> Self-executing Markdown. The **Cloudflare binding** of the *relational-database* intent — D1 (SQLite at the
> edge) as a **primary application database**: versioned schema, migrations, shared across Workers, optional
> global read replication. The cloud is the source of truth; this file is intent + ledger + audit.

> **Provides / Requires**: **Provides** `d1-db(DB_NAME, database_id)` — consumer Workers
> (`service.cloudflare.md`, a queue consumer) **Require** it and discover the `database_id` via
> `wrangler d1 info`. **Requires** nothing upstream.

---

## 🤖 Director prompt

You are the Director. Execute this plan:
- Observe-before-act; verify each step before advancing
- Stop at every 🔴 GATE and 💥 for human "go"
- Write realized values + verify results back into Live State
- **Schema changes are migrations** (append-only, numbered) — never hand-edit a shipped migration
- Teardown deletes only the database this plan created
- Use only the commands in this plan

> **Candor:** authored, **not yet dogfooded** — confirm `wrangler d1` subcommands against `--help` live (the
> CLI is the source of truth). `--remote` targets the live DB; omitting it hits a local replica.

```
Legend  🟢 create · 🟡 config · 🔴 GATE (human go) · 💥 destructive (human go) · ⏳ wait · ✔ verify
```

## Intent

A real relational database for an app — users, content, orders — not just a key-value status row. D1 is
SQLite exposed as a managed, edge-replicated database: you bind it by name to one or more Workers, evolve its
schema through **numbered migrations**, and (optionally) serve low-latency reads globally via read
replication. This is D1 as the **app's source of record**, shared across services — distinct from the
`STATE_STORE=d1` status-store role in `task-runner.cloudflare.md`.

**Shared acceptance contract** (every relational-database binding — D1 / AWS RDS / GCP Cloud SQL — must pass):
1. the **schema is applied** — the expected tables exist
2. **write then read** a row back → identical (read-after-write consistency on the primary)
3. **(`SCHEMA_MODE=migrations`)** a v2 migration applies and re-applying it is a **no-op** (idempotent ledger)
4. **(`ACCESS=shared`)** a second Worker binding reads a row written through the first (one DB, many Workers)

## Provisioning Inputs

| # | Question | Options (closed enum) | Default | Sets | Gates |
|---|----------|-----------------------|---------|------|-------|
| 1 | How is schema managed? | `migrations` / `single-file` | `migrations` | `SCHEMA_MODE` | §2 (versioned migrations vs one schema.sql) |
| 2 | Bound to one Worker or shared? | `single-worker` / `shared` | `single-worker` | `ACCESS` | §3 (one binding vs many) + acceptance 4 |
| 3 | Global low-latency reads? | `none` / `read-replication` | `none` | `REPLICATION` | §4 (Sessions API read replicas) |
| 4 | Environment | `dev` / `stg` / `uat` / `prod` | `dev` | `ENV` | every resource name (`*-${ENV}`) |

**Why `migrations` is the default:** a primary database outlives any single deploy; its schema must evolve
without data loss and with an auditable history. `wrangler d1 migrations` gives numbered, append-only,
idempotent migration files (the applied ledger lives in the DB) — the disciplined path. `single-file`
(`execute --file schema.sql`) is fine for a throwaway or a status table, but re-applying it is **not**
inherently idempotent. **`ACCESS=shared`** is the acme pattern: one `database_id` bound (by the same id)
into several Workers — the DB is the hand-off, no shared state file. **`read-replication`** adds global read
replicas (queried via the Sessions API with read-your-writes guarantees) for read-heavy, latency-sensitive apps.

```yaml
# → written into Live State once resolved
resolved_inputs:
  schema_mode: migrations      # migrations | single-file
  access:      single-worker   # single-worker | shared
  replication: none            # none | read-replication
  env:         dev
  resolved_by: <human who confirmed>
  resolved_at: <timestamp>
```

## Tags & provenance (binding asymmetry)

**Cloudflare has no resource-tag API** — the D1 database (`DB_NAME`) takes no tags. Provenance is the
**naming convention** (`${APP}-db-${ENV}`) + every binding Worker's `[vars]`. The database carries no `vars`
of its own; `wrangler d1 info` is the audit read (size, created, region hint).

## 0. Variables

```bash
export ENV="dev"
export SCHEMA_MODE="migrations" ACCESS="single-worker" REPLICATION="none"
export APP="acme"
export DB_NAME="${APP}-db-${ENV}"                 # e.g. acme-db-dev
export MIG_DIR="$PWD/migrations/${APP}"
export CONFIG="$PWD/${APP}.jsonc"
# NOTE: `command wrangler` bypasses the wrangler shell-fn so the ambient CLOUDFLARE_API_TOKEN is used.
# ALWAYS pass --remote for the live DB; without it you mutate a local replica only.
```

## Dependency frontier

```
database (§1) ─> schema/migrations (§2) ─> binding(s) (§3) ─> (REPLICATION) read replicas (§4) ─> ✔ acceptance
                         ▲ migrations are append-only; the applied ledger lives in the DB
```
Non-negotiable edges: **the database exists before schema**; **a migration is applied before a Worker queries
the table it adds**; **a shared DB's `database_id` is identical across every binding**. Teardown deletes the DB
(migrations files are local artifacts).

## 1. Database  🟢

```bash
command wrangler d1 create "$DB_NAME"        # prints database_id → record it for every binding
```
```bash
command wrangler d1 info "$DB_NAME" 2>&1 | grep -iE "uuid|database_id|name"     # ✔ verify
```
> → Live State: DB_NAME, database_id, status: creating→live.

## 2. Schema  🟡  *(`SCHEMA_MODE=migrations` — versioned, append-only)*

```bash
mkdir -p "$MIG_DIR"
command wrangler d1 migrations create "$DB_NAME" create_core_tables --config "$CONFIG"
# edit the generated migrations/0001_create_core_tables.sql, e.g.:
cat > "$MIG_DIR/0001_create_core_tables.sql" <<'SQL'
CREATE TABLE IF NOT EXISTS users (
  id         TEXT PRIMARY KEY,
  email      TEXT UNIQUE NOT NULL,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
SQL
command wrangler d1 migrations apply "$DB_NAME" --remote --config "$CONFIG"
```
```bash
# ✔ verify schema applied + the migrations ledger
command wrangler d1 execute "$DB_NAME" --remote --command "SELECT name FROM sqlite_master WHERE type='table' AND name='users'"
command wrangler d1 migrations list "$DB_NAME" --remote --config "$CONFIG"
```
> → Live State: migrations applied (0001…). **`single-file` branch:** `wrangler d1 execute "$DB_NAME"
> --remote --file=schema.sql` once — no ledger, re-apply is your responsibility.

## 3. Binding(s)  🟡  *(`ACCESS=single-worker` | `shared`)*

> Each consumer Worker binds the **same `database_id`**. For `ACCESS=shared`, multiple `wrangler.jsonc` files
> carry an identical `d1_databases` entry — the DB is the hand-off between services (no shared state file).

```jsonc
{ "d1_databases": [ { "binding": "DB", "database_name": "acme-db-dev", "database_id": "<from §1>" } ] }
```
```js
// in a consumer Worker:
const row = await env.DB.prepare('SELECT id FROM users WHERE email = ?').bind(email).first();
```
> → Live State: list every Worker bound to this DB (the shared-consumer set).

## 4. Read replication  🟡  *(`REPLICATION=read-replication` — global low-latency reads)*

> Enable read replication for the database (dashboard/API), then query through the **Sessions API** so reads
> get read-your-writes consistency against the nearest replica:

```js
const session = env.DB.withSession('first-primary');     // read-your-writes
const rows = await session.prepare('SELECT * FROM content WHERE published = 1').all();
```
```bash
command wrangler d1 info "$DB_NAME" 2>&1 | grep -i replica     # ✔ verify replication state
```
> → Live State: replication enabled.

## Acceptance verify  ✔  *(the shared contract)*

```bash
# 1 + 2: schema present, write-then-read round-trip
command wrangler d1 execute "$DB_NAME" --remote --command "INSERT OR REPLACE INTO users (id,email) VALUES ('u1','a@example.com')"
command wrangler d1 execute "$DB_NAME" --remote --command "SELECT email FROM users WHERE id='u1'" 2>&1 | grep -q 'a@example.com' && echo "2: round-trip OK"
# 3: re-applying migrations is a no-op
command wrangler d1 migrations apply "$DB_NAME" --remote --config "$CONFIG" 2>&1 | grep -qiE "no migrations|already applied|up to date" && echo "3: idempotent OK"
# NEGATIVE: the UNIQUE constraint actually bites — a duplicate email is rejected, not silently overwritten
command wrangler d1 execute "$DB_NAME" --remote --command "INSERT INTO users (id,email) VALUES ('u2','a@example.com')" 2>&1 | grep -qiE "unique|constraint" && echo "neg: constraint enforced OK"
```
> → write Live State: status: live; fill verify rows. The negative (constraint enforced) proves the schema's
> integrity rules are live, not just declared — a DB that silently accepts a duplicate key is the failure to catch.

## Live State

```yaml
status:        not-created      # published template - run it to realize state
last_action:   authored — D1 primary app DB (schema-mode/access/replication knobs + migrations lifecycle)
last_verified: —
resolved_inputs: { schema_mode: migrations, access: single-worker, replication: none, env: dev }
migrations_applied: []        # append each numbered migration as it applies
```

| key          | value (filled on apply) |
|--------------|-------------------------|
| DB_NAME      | `${APP}-db-${ENV}` |
| database_id  | `—` |
| bound Workers| `—` (the shared-consumer set when `ACCESS=shared`) |
| migrations   | `—` (0001, …) |

| ✔ check                   | expected                                   | observed | result |
|---------------------------|--------------------------------------------|----------|--------|
| schema applied            | expected tables exist                       | —        | — |
| write→read round-trip     | row read back identical                     | —        | — |
| migrations idempotent     | re-apply is a no-op                         | —        | — |
| constraint enforced (neg) | duplicate-key insert rejected, not silent   | —        | — |
| shared read (access=shared)| second Worker reads first Worker's write    | —        | — |

## Update (idempotent reconcile)

- Schema change → `wrangler d1 migrations create … && wrangler d1 migrations apply --remote` (append-only,
  numbered; never edit a shipped migration).
- Add a consumer → add the identical `d1_databases` entry to its `wrangler.jsonc` (same `database_id`).
- Enable replication → §4, then switch hot read paths to the Sessions API.
- Backup before risky changes → `wrangler d1 export "$DB_NAME" --remote --output=dump.sql`.

## Teardown (observe-first, resumable)  💥

> 💥 Human go. Deleting the database is **irreversible for the data** (Time Travel allows ~30-day restore while
> the DB exists, not after delete). Export first if the data matters. Observe, then delete.

```bash
command wrangler d1 export "$DB_NAME" --remote --output="/tmp/${DB_NAME}-final.sql" 2>/dev/null || true   # optional safety net
command wrangler d1 delete "$DB_NAME"
```
```bash
command wrangler d1 info "$DB_NAME" 2>&1 | grep -qi "not found\|does not exist" && echo "database gone"   # ✔
```
> → Live State: status: gone; clear realized ids + migrations.

---

## Portability ledger — same intent, three bindings

| | AWS (`database.aws.md`, RDS/Aurora) | Cloudflare (`database.cloudflare.md`, D1) | GCP (`database.gcp.md`, Cloud SQL) |
|---|---|---|---|
| Provisioning | instance/cluster + subnet group + SG (minutes) | `wrangler d1 create` — seconds | instance + network (minutes) |
| Engine | Postgres/MySQL | SQLite | Postgres/MySQL |
| Wiring to compute | VPC + SG + IAM/secret | **binding by name** — no network | private IP / proxy + IAM |
| Migrations | external tool (Flyway/Liquibase) | `wrangler d1 migrations` (built-in ledger) | external tool |
| Global reads | read replicas (provisioned) | **read replication** (Sessions API) | read replicas (provisioned) |
| Point-in-time | automated backups + PITR | **Time Travel** (~30 days) | PITR |
| Cost shape | hourly instance | per-row-read/write + storage | hourly instance |
| Tags | resource tags | **none** — naming + `[vars]` | labels |

## Deliberately not included

- **D1 as a status store** — that's `task-runner.cloudflare.md` (`STATE_STORE=d1`); this plan is D1 as the
  primary app DB (full schema + migrations + sharing + replication).
- **Cross-database joins / sharding** — D1 has per-database size limits; sharding strategy is an app concern.
- **ORM/query-builder choice** — Drizzle/Kysely over the `DB` binding is application code, not infra.
- **Automated migration CI** — running `migrations apply` in a pipeline is a delivery concern; the plan covers
  the authoring + manual apply path.