# Capturing form submissions into Cloudflare D1 This is the established pattern for turning a static site's contact / waitlist form into durable, queryable leads: the form POSTs to a **Pages Function**, the Function inserts a row into a **Cloudflare D1** database, and the agent reads and sweeps new rows with `wrangler d1 execute --remote`. It is live on `realagent.pages.dev` today — the worked example below is that deployment, generalized with placeholders. Pair this with `hosting-sites.md` (how the site itself gets deployed) and `api.md` (the master token + reused per-scope token discipline that authenticates every `wrangler` call). --- > **Multi-tenant installs.** The raw `wrangler d1` pattern below (admin holds an account-wide token and queries D1 directly) is for a single-tenant install. On a multi-client install where sub-accounts share one Cloudflare account, a sub-account never holds a Cloudflare token and never runs `wrangler` against D1 directly. It reaches D1 only through the storage broker tools (`storage-d1-list`, `storage-d1-create`, `storage-d1-query`), which scope every operation to the caller's account. See [`../../../../.docs/cloudflare-storage-isolation.md`](../../../../.docs/cloudflare-storage-isolation.md). ## Establishing a store — naming, count, and shape The mechanics below (token scope, `wrangler d1 create`, the `swept` cursor) govern **how** you talk to a store. This section governs **how a store is established** — how many, what it is named, what shape it takes. Skipping it is what let one account grow three tables across two databases all recording the same fact. - **Naming — `-`.** Every D1 database is named `-`, computed with the helper, never hand-built: ```bash DB_NAME="$(bash bin/cf-store-name.sh "" "")" # e.g. acc9f2-acceptances ``` The name then identifies the store's owner and purpose at a glance. On a single-tenant install where the account and the brand coincide, the worked `realagent-leads` example below is the same scheme with the brand standing in for ``; on a multi-tenant install `` is what keeps one client's store from colliding with another's. - **One store per purpose.** A purpose gets exactly **one** database. Before `wrangler d1 create`, list what already exists (`wrangler d1 list`); if a store already serves the purpose, **reuse it** — that is the desired outcome, not a second store. A new capture *kind* within a purpose is a discriminator **column** (`doc_type`, `party`), never a new table and never a new database. A second capture kind spawning a second store is the sprawl signature this rule exists to prevent. - **Canonical schema per purpose.** Each store-purpose has one schema, declared once and reused; a build does not reinvent the shape. The acceptance store's canonical schema is fixed below. Any new capture-purpose store follows the same "canonical schema, declared once" pattern rather than being re-derived per build. **Store-provision breadcrumb.** When a store is created or reused, log the establishment decision so a reuse is visible and a create is attributable to a purpose that genuinely had none: ``` op=storage-provision resource=d1 purpose= account= name= action=create|reuse ``` ## The canonical acceptance store The document-acceptance store — the one the e-sign, works-agreement, and quote-signing watchers read — is a single `acceptances` table with fixed column names. One shape covers every acceptance kind, so a watcher reads one known store instead of several drifted ones: ```sql CREATE TABLE IF NOT EXISTS acceptances ( id INTEGER PRIMARY KEY AUTOINCREMENT, doc_ref TEXT NOT NULL, doc_type TEXT NOT NULL, party TEXT, signer_name TEXT, accepted_at TEXT NOT NULL DEFAULT (datetime('now')), swept INTEGER NOT NULL DEFAULT 0 ); ``` - `doc_ref` — identifies the job or document the acceptance is against. - `doc_type` — the acceptance kind (`works-agreement`, `single-signer-quote`, `dual-signer-quote`); this is the discriminator, so a new kind is a value here, not a new table. - `party` — which signer, for a two-signer document; `NULL` for a single-signer one. This column is what a separate `dual_acceptances` table would otherwise duplicate. - `signer_name` — the signer's identity. The column is named `signer_name`, never a bare `name`. - `accepted_at` — when the document was accepted. - `swept` — the read cursor, exactly as in `leads` below: `0` until the agent has ingested the row, then `1`. Confirm a live store follows canon with the exit-code gate. Read the stored DDL with `--json` (as the sweep `SELECT` below does) and extract the `sql` values with `jq -r`, so the validator receives newline-delimited `CREATE` statements rather than `wrangler`'s bordered table output: ```bash CLOUDFLARE_API_TOKEN="${PAGES_D1}" wrangler d1 execute "" --remote --json --command \ "SELECT sql FROM sqlite_master WHERE sql IS NOT NULL;" \ | jq -r '.[0].results[].sql' | bash bin/cf-schema-check.sh # exit 0 = follows canon; exit 1 = the drift is named on stderr. ``` --- ## The single most common breakage — token scope A Pages-only token **cannot touch D1**. The deploy succeeds, the form renders, and every submission silently 500s at the D1 insert. The token used for D1-backed Pages work must carry **both**: - **Account · Cloudflare Pages · Edit** - **Account · D1 · Edit** Use the stable `-pages-d1` token that includes both permission groups (see `api.md` § Provisioning and reusing a stable per-scope token, and § 0 below) before any `wrangler d1` or D1-backed deploy command. This was observed live in the session that built `realagent.pages.dev`; it is the first thing to check when captures stop arriving. **Even a read-only `SELECT` needs D1 *Edit*.** The `wrangler d1 execute --remote` query path goes through the D1 **query** endpoint, which **rejects a D1-Read token** — a token scoped to D1 *Read* fails the `SELECT`, not just the `INSERT`/`UPDATE`. Use the **D1-Edit**-scoped token (or the both-Pages-Edit-and-D1-Edit token above) for every `wrangler d1 execute`, reads included. A separately-minted "D1 Read" token for the sweep `SELECT`s is wrong guidance: it is rejected at the query endpoint. --- ## 0. Load (or mint once) the stable Pages-+-D1 token Every command below uses `${PAGES_D1}` — the **stable, reused** per-scope token (`-pages-d1`) scoped to **both** Pages Edit and D1 Edit. Provision it the way `api.md` § Provisioning and reusing a stable per-scope token prescribes: **load it from the secrets file; mint it once only if absent** (no expiry), persist it, and reuse it thereafter. It is never written into a project tree or echoed: ```bash set -a; . "${SECRETS_DIR}/cloudflare.env"; set +a # loads master + account id + persisted per-scope tokens : "${CLOUDFLARE_API_TOKEN:?credentials not loaded}"; : "${CLOUDFLARE_ACCOUNT_ID:?account id not loaded}" TOKEN_KEY="CF_PAGES_D1_TOKEN"; SCOPE_NAME="-pages-d1" PAGES_D1=$(grep -m1 "^${TOKEN_KEY}=" "${SECRETS_DIR}/cloudflare.env" 2>/dev/null | cut -d= -f2-) if [ -z "${PAGES_D1}" ]; then # absent → mint once, scoped to BOTH Pages Edit + D1 Edit, no expiry PAGES_D1=$(curl -sS -X POST \ -H "Authorization: Bearer ${CLOUDFLARE_API_TOKEN}" \ -H "Content-Type: application/json" \ "https://api.cloudflare.com/client/v4/accounts/${CLOUDFLARE_ACCOUNT_ID}/tokens" \ --data @- <", "name": "Pages Write" }, { "id": "", "name": "D1 Write" } ] }] } JSON ) ( umask 077; printf '%s=%s\n' "${TOKEN_KEY}" "${PAGES_D1}" >> "${SECRETS_DIR}/cloudflare.env" ) fi ``` Resolve the two permission-group ids once via `GET /accounts/{account_id}/tokens/permission_groups` (see `api.md`). There is **no `unset`** — `${PAGES_D1}` is the reused per-scope token, not a throwaway; the reconcile pass in `api.md` § Reconcile keeps the account from accumulating strays. --- ## 1. Create the database ```bash CLOUDFLARE_API_TOKEN="${PAGES_D1}" wrangler d1 create # worked example: wrangler d1 create realagent-leads ``` `wrangler` prints the database `name` and `database_id`. Copy the `database_id` into `wrangler.toml` (next step). The token value is never printed — only the database identifiers. ## 2. Bind the database in `wrangler.toml` The binding name (`DB` below) is the variable the Pages Function reads off `env`. Keep `wrangler.toml` in the site's project root — it carries no secret, only the database id. ```toml name = "" # worked example: realagent pages_build_output_dir = "dist" # or the framework's output dir [[d1_databases]] binding = "DB" database_name = "" # realagent-leads database_id = "" # from step 1 ``` ## 3. Create the table Apply the schema to the remote database (`--remote` targets the live D1, not a local replica): ```bash CLOUDFLARE_API_TOKEN="${PAGES_D1}" wrangler d1 execute --remote --command \ "CREATE TABLE IF NOT EXISTS leads ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT NOT NULL, message TEXT, created_at TEXT NOT NULL DEFAULT (datetime('now')), swept INTEGER NOT NULL DEFAULT 0 );" ``` The `swept` column is the read-cursor: a row is `swept = 0` until the agent has ingested it, then flipped to `1`. This is what makes "show me new leads" a deterministic query rather than a guess. ## 4. The Pages Function — `functions/api/contact.ts` A file at `functions/api/contact.ts` is served at `POST /api/contact` automatically (Pages Functions route by file path). It reads the `DB` binding and inserts a row. No secret lives in this file — the D1 binding is injected by the platform at runtime. ```ts interface Env { DB: D1Database } export const onRequestPost: PagesFunction = async ({ request, env }) => { const form = await request.formData(); const email = String(form.get("email") ?? "").trim(); if (!email) return new Response("email required", { status: 400 }); await env.DB .prepare("INSERT INTO leads (name, email, message) VALUES (?, ?, ?)") .bind(String(form.get("name") ?? ""), email, String(form.get("message") ?? "")) .run(); return new Response(null, { status: 303, headers: { Location: "/thanks" } }); }; ``` The site's form posts to it: ```html
``` ## 5. Deploy Deploy via `hosting-sites.md`. The binding in `wrangler.toml` is what wires the deployed Function to D1; the deploy token must carry both Pages Edit and D1 Edit (above). ## 6. Read and sweep new submissions Read everything not yet ingested: ```bash CLOUDFLARE_API_TOKEN="${PAGES_D1}" wrangler d1 execute --remote --json --command \ "SELECT id, name, email, message, created_at FROM leads WHERE swept = 0 ORDER BY id;" ``` After ingesting those rows (e.g. writing them into the graph), mark them swept so the next read returns only newer ones: ```bash CLOUDFLARE_API_TOKEN="${PAGES_D1}" wrangler d1 execute --remote --command \ "UPDATE leads SET swept = 1 WHERE swept = 0;" ``` --- ## Outcome contract D1 capture is done when a **test POST to the live form** appears in the unswept set: ```bash curl -sS -X POST -d "email=test@example.com&name=verify" "https://.pages.dev/api/contact" -i | head -1 # then: CLOUDFLARE_API_TOKEN="${PAGES_D1}" wrangler d1 execute --remote --command \ "SELECT email FROM leads WHERE swept = 0;" ``` The test email appearing in that `SELECT` is the proof. A 200/303 on the POST alone is not — the row in D1 is the contract. Surface the operation as verb + target ("inserting a test lead", "reading unswept rows from `realagent-leads`"); the token value never appears.