Cloudflare D1 Deployment

This document covers everything you need to know to push Vinny data to Cloudflare D1 — credentials, configuration, how the import works under the hood, and how to diagnose authentication errors.

---

Overview

flowchart LR
    A["vinny scrape"] --> B["vinny images\ndownload"]
    B --> C["vinny images\nupload-r2"]
    C --> D["vinny export-d1\n--execute"]
    D --> E["Cloudflare D1"]
    E --> F["vinny.vegas\n(Astro SSR)"]

    style A fill:#7c3aed,color:#fff
    style C fill:#d97706,color:#fff
    style E fill:#059669,color:#fff
    style F fill:#059669,color:#fff

Vinny exports events, artists, and image URLs to a Cloudflare D1 SQLite database. The easiest way to trigger this is vinny sync, which does the full pipeline automatically. You can also run individual steps:

# Full pipeline (recommended)
vinny sync

# Just the D1 export step
vinny export-d1 --execute

# After a separate R2 upload, sync R2 URLs to D1
vinny images upload-r2 --sync-d1

---

Prerequisites

1. A Cloudflare account with a D1 database created
2. An API token with Account > D1 > Edit permission
3. Your Cloudflare account ID
4. wrangler.jsonc configured (or env vars set)

---

wrangler.jsonc Configuration

Vinny reads two distinct values from wrangler.jsonc:

Field	Used for
database_name	wrangler d1 execute CLI (fallback only)
database_id	Cloudflare REST API (/query endpoint)

Minimal config:

{
  "name": "my-worker",
  "d1_databases": [
    {
      "binding": "DB",
      "database_name": "vinny-vegas",
      "database_id": "ca47027a-b83a-4e07-9354-a233a8a17bec"
    }
  ]
}

Both fields matter. database_name identifies the DB by name (used by wrangler CLI). database_id is the UUID required by the REST API. Get the UUID from the Cloudflare dashboard → Workers & Pages → D1 → your database.

---

API Token Setup

Required permissions

Your Cloudflare API token must have:

Account > D1 > Edit

Without this, you'll get HTTP 403 with error code 7403: "The given account is not valid or is not authorized to access this service"

Creating a token

1. Go to dash.cloudflare.com/profile/api-tokens
2. Click Create Token
3. Use Create Custom Token
4. Add permission: Account > D1 > Edit
5. Optionally scope to your specific account
6. Copy the token (shown only once)

---

Environment Variables

Store in .env at the project root:

# --- D1 ---
D1_DATABASE=vinny-vegas          # database_name from wrangler.jsonc
                                  # (database_id is read from wrangler.jsonc automatically)

# API token — must have Account > D1 > Edit
CLOUDFLARE_API_TOKEN=...          # preferred name
# D1_API_TOKEN=...               # alias — either works

# Your Cloudflare account ID (visible at dash.cloudflare.com, right sidebar)
CLOUDFLARE_ACCOUNT_ID=...         # preferred name
# D1_ACCOUNT_ID=...              # alias — either works

# --- R2 (for image upload) ---
R2_ACCOUNT_ID=...
R2_ACCESS_KEY_ID=...
R2_SECRET_ACCESS_KEY=...
R2_PUBLIC_URL=https://img.vinny.vegas
R2_BUCKET=vinny-vegas-images

To find your account ID: log in to dash.cloudflare.com, look for the Account ID in the right sidebar on the Workers & Pages overview page. Alternatively:

bunx wrangler whoami

---

How the D1 Import Works

REST API path (preferred)

When api_token + account_id + database_id are all available, Vinny sends SQL directly to the Cloudflare D1 REST API:

POST https://api.cloudflare.com/client/v4/accounts/{account_id}/d1/database/{database_id}/query
Authorization: Bearer {api_token}
Content-Type: application/json

{"sql": "INSERT INTO events ..."}

This is implemented in D1Exporter._execute_sql_via_api() using Python's stdlib urllib.request — no extra dependencies.

Batching: SQL statements are split on ;, PRAGMA statements are stripped (unsupported by the REST API), and statements are sent in batches of 100.

Wrangler subprocess fallback

If any credential is missing, Vinny falls back to:

wrangler d1 execute {database_name} --file {sql_file} [--remote]

This requires wrangler to be installed and logged in (bunx wrangler login).

Why not always use wrangler for file import?

wrangler d1 execute --file uses the /import endpoint internally. This multi-step protocol (init → upload → ingest → poll) requires OAuth-style auth that can fail silently or with confusing errors depending on token type.

The REST API /query endpoint accepts a standard Bearer token and is far more reliable for programmatic use. Use the REST API path whenever possible.

---

Data Model

Vinny's core Python type is VegasEvent (src/models/__init__.py). Understanding two fields is essential before working with the D1 schema:

• composite_key (computed property) — the stable D1 primary key; derived from event_date, performer, and venue. Never stored on the object.
• external_id — the raw scraper-assigned ID (e.g. EVE111500020260531). Stored as a non-PK column in D1; changes between scrapes and must not be used as a join key.

See DATA_MODEL.md for the complete field reference, EventData TypedDict, and type-safety guarantees enforced at pre-commit time.

---

Event Primary Key: composite_key

This is the single most important thing to understand about D1 imports.

Every event row uses composite_key as its event_id primary key — not the external scraper ID (like EVE111500020260531).

composite_key = "{event_date}-{performer_slug}-{venue_slug}"
# e.g.  2026-05-02-calvin-harris-encore-beach-club

Why this matters

The scraper may assign a new external event_id each time it runs (e.g. each scrape of an existing event can produce a different EVE... string). If event_id tracked the external ID, a re-scrape would insert a duplicate row with the same composite_key and trigger:

UNIQUE constraint failed: events.composite_key

Then a seemingly-safe fix of switching the conflict target to composite_key introduces a FK failure: the old event_id stays in the row, but child inserts (images, table_tiers, event_artists) use the new event_id → FK violation.

The correct fix: always derive primary_event_id = event.composite_key in the D1 exporter so event_id == composite_key for every row. Then ON CONFLICT(event_id) fires correctly on re-scrape with no FK issues.

This is now enforced in src/export/d1.py.

Schema is the source of truth

No ALTER TABLE migrations

All columns are defined in CREATE TABLE statements in d1.py. Never use ALTER TABLE — wrangler CLI sends the whole SQL as one transaction, so ALTER TABLE on existing columns fails the entire import. If you need to add a column, add it to the CREATE TABLE statement directly.

As of March 2026, CREATE TABLE statements in d1.py define all columns. There are no ALTER TABLE migrations — all columns (including category, section, capacity_min, capacity_max on table_tiers) are part of the initial schema. If you need to add a column, add it to the CREATE TABLE statement directly.

One-time migration (March 2026)

When EBC was added alongside legacy XS/LIV data, some rows had event_id = EVE... while new rows had event_id = composite_key. A migration was run to normalize:

PRAGMA foreign_keys = OFF;
UPDATE event_artists SET event_id = (SELECT composite_key FROM events WHERE events.event_id = event_artists.event_id)
  WHERE event_id IN (SELECT event_id FROM events WHERE event_id != composite_key);
UPDATE table_tiers SET event_id = (SELECT composite_key FROM events WHERE events.event_id = table_tiers.event_id)
  WHERE event_id IN (SELECT event_id FROM events WHERE event_id != composite_key);
UPDATE images SET event_id = (SELECT composite_key FROM events WHERE events.event_id = images.event_id)
  WHERE event_id IN (SELECT event_id FROM events WHERE event_id != composite_key);
UPDATE events SET event_id = composite_key WHERE event_id != composite_key;
PRAGMA foreign_keys = ON;
PRAGMA optimize;

This updated ~6,400 rows. All new scrapes and exports will produce the correct key automatically — this migration only needs to be run once on pre-existing data.

---

D1 Schema

Vinny manages four tables:

-- Core events
CREATE TABLE IF NOT EXISTS events (
  id TEXT PRIMARY KEY,
  title TEXT, date TEXT, time TEXT,
  venue TEXT, venue_id TEXT,
  description TEXT, age_requirement TEXT,
  ticket_url TEXT, table_pricing_json TEXT,
  run_id TEXT, created_at TEXT, updated_at TEXT
);

-- Deduplicated artist profiles
CREATE TABLE IF NOT EXISTS artists (
  id TEXT PRIMARY KEY,
  name TEXT, slug TEXT,
  bio TEXT, image_url TEXT,
  spotify_url TEXT, apple_music_url TEXT,
  youtube_url TEXT, soundcloud_url TEXT,
  updated_at TEXT
);

-- Many-to-many event ↔ artist
CREATE TABLE IF NOT EXISTS event_artists (
  event_id TEXT, artist_id TEXT,
  is_headliner INTEGER DEFAULT 0,
  PRIMARY KEY (event_id, artist_id)
);

-- One row per image × size variant
CREATE TABLE IF NOT EXISTS images (
  id TEXT PRIMARY KEY,
  event_id TEXT, artist_id TEXT,
  size TEXT, url TEXT, r2_url TEXT,
  local_path TEXT,
  updated_at TEXT
);

-- VIP table pricing tiers
CREATE TABLE IF NOT EXISTS table_tiers (
  id TEXT PRIMARY KEY,
  event_id TEXT, section TEXT,
  min_spend REAL, deposit REAL,
  capacity_min INT, capacity_max INT,
  available INTEGER,
  updated_at TEXT
);

All upserts use INSERT OR REPLACE (events) or INSERT INTO ... ON CONFLICT DO UPDATE (artists — COALESCE-based to never overwrite enriched fields with NULL).

---

R2 → D1 URL Flow

Pipeline order: images before D1

Images must be uploaded to R2 before the D1 export so that artist_image_url is populated. If you run vinny export-d1 before vinny images upload-r2, image columns will be NULL. vinny sync handles this ordering automatically.

After images are uploaded to Cloudflare R2, their public URLs need to be written back to the D1 images table. Vinny generates SQL like:

UPDATE images
SET r2_url = 'https://img.vinny.vegas/artists/dom-dolla_main.jpg',
    updated_at = '2026-03-01T20:10:04'
WHERE url = 'https://venueeventartist.com/...'
   OR (artist_id = 'dom-dolla' AND size = 'main');

This happens automatically in step 3 of vinny sync. To do it manually:

vinny images upload-r2 --sync-d1

---

Troubleshooting

HTTP 403 — error code 7403

"The given account is not valid or is not authorized to access this service"

Cause: API token lacks D1 permission.

Fix: 1. Edit your API token at dash.cloudflare.com/profile/api-tokens 2. Add Account > D1 > Edit 3. Save

HTTP 403 — "account not found"

Your account_id is wrong. Verify it:

bunx wrangler whoami
# Look for: "Account ID: fffb8723..."

Compare against CLOUDFLARE_ACCOUNT_ID in your .env.

"D1 API error" in import

Check the error messages returned in the response body. Common causes: - SQL syntax error (check d1_import.sql manually) - Table doesn't exist yet (run schema migrations first) - Statement too large (Vinny already batches at 100 statements)

UNIQUE constraint failed: events.composite_key

This happens when the same event already exists in D1 with a different event_id (e.g. an old EVE... ID) but the same composite_key.

Root cause: Some legacy rows had event_id != composite_key. When the exporter inserted a new row with event_id = composite_key, the ON CONFLICT(event_id) clause didn't fire (different values), but the composite_key UNIQUE index blocked the insert.

Fix: Run the one-time migration above to normalize all rows so event_id = composite_key. Future imports will be conflict-free since primary_event_id = event.composite_key is now always used.

FOREIGN KEY constraint failed (during import)

If you changed the conflict target to ON CONFLICT(composite_key) as a fix for the above, you'll see FK failures: the old event_id is preserved in events, but child rows (images, table_tiers, event_artists) are inserted with the new event_id.

Fix: Revert to ON CONFLICT(event_id) in d1.py and ensure primary_event_id = event.composite_key.

Wrangler fallback fails

If the REST API path isn't triggered and wrangler fails, ensure: 1. wrangler is installed: bunx wrangler --version 2. You're authenticated: bunx wrangler whoami 3. The database name in wrangler.jsonc matches your actual D1 database

Testing locally

Use wrangler's local D1 mode (no internet needed):

vinny export-d1 --execute --local
# or
vinny sync --local

This writes to a local SQLite file wrangler manages in .wrangler/state/.

---

Quick Setup Checklist

• D1 database created in Cloudflare dashboard
• wrangler.jsonc has both database_name and database_id
• API token created with Account > D1 > Edit permission
• CLOUDFLARE_API_TOKEN set in .env
• CLOUDFLARE_ACCOUNT_ID set in .env
• R2 bucket created and R2_* vars set in .env (for image sync)
• Test: vinny export-d1 --execute (or vinny sync --run latest)

---

Last updated: 2026-03-04