D1 to Astro Flow

How data flows from Cloudflare D1 through the Astro SSR site at vinny.vegas

1 — Full Pipeline
graph TD
  A["Scraper
VegasEvent Data"] --> B["Image Downloader"]
  B --> C["R2 Upload
img.vinny.vegas"]
  C --> D["D1 Export
SQL Schema"]
  D --> E["D1 Database
5 Tables"]
  E --> F["Astro SSR
Query Helpers"]
  F --> G["Cloudflare Pages
Deploy"]
  G --> H["Browser
vinny.vegas"]

  classDef scraper fill:#38bdf811,stroke:#38bdf844,stroke-width:1.5px
  classDef r2 fill:#fb923c11,stroke:#fb923c44,stroke-width:1.5px
  classDef d1 fill:#a78bfa11,stroke:#a78bfa44,stroke-width:1.5px
  classDef astro fill:#34d39911,stroke:#34d39944,stroke-width:1.5px
  classDef browser fill:#fb718511,stroke:#fb718544,stroke-width:2px

  class A scraper
  class B,C r2
  class D,E d1
  class F,G astro
  class H browser
Pipeline order matters: Images must be uploaded to R2 before D1 export, so that artist_image_url is populated in the database. All SQL is imported as a single transaction—ALTER TABLE failures abort the entire import.
2 — D1 Schema

5 relational tables with composite keys for deduplication:

events
Main event records
  • composite_key (PK): unique event identifier
  • date, title, description
  • performer, venue, venue_id
  • pricing_tiers (JSON)
  • created_at, updated_at
artists
Enriched artist profiles
  • artist_slug (PK)
  • name, bio, image_url
  • resident_advisor_url
  • spotify_id, top_tracks (JSON)
  • enrichment_status (JSON)
event_artists
Event-artist junction
  • composite_key (FK → events)
  • artist_slug (FK → artists)
  • role (headliner, opener, resident...)
  • PK: (composite_key, artist_slug)
images
Artist images from R2
  • artist_slug (FK)
  • size (main, hd)
  • venue_tag (ebc, xs, liv, etc.)
  • image_url (R2 CDN link)
  • PK: (artist_slug, size, venue_tag)
table_tiers
Table pricing tiers
  • composite_key (FK → events)
  • tier_name, min_spend
  • guest_count, table_count
  • notes (JSON)
3 — Import Methods

Two paths to D1:

Method How It Works Requirements Best For
REST API
Preferred		 Direct POST /query endpoint with Bearer token CLOUDFLARE_API_TOKEN with D1 Edit permission CI/CD pipelines, production deploys
Wrangler
Fallback		 Subprocess: wrangler d1 execute with local config wrangler.jsonc with database_id + database_name Local dev, when API token unavailable

Batching Strategy:

  • SQL split on ; delimiters
  • Sent in batches of 100 statements
  • Each batch: one HTTP request or wrangler call
  • Parallel execution: up to 5 concurrent batches
  • Retries on transient failures (up to 3 attempts)
No ALTER TABLE. All columns must be defined in CREATE TABLE statements. If you need schema changes, drop and recreate the table.
4 — Composite Key Strategy

The composite_key is a content-addressed primary key, not an external EVE ID:

composite_key = hash(venue_id + performer + date)
Example: "e3b0c44298fc1c149afbf4c8996fb924"

Why not use EVE IDs?

  • Venue independence: Same performer at different venues = different composite keys
  • Deduplication: Automatically detect duplicate scrapes (same hash)
  • Stability: EVE IDs are external and sometimes unstable; composite keys are stable across re-scrapes
  • Simplicity: No external ID mappings required

Dedup in Action:

Event 1: LIV Las Vegas + "Carl Cox" + "2026-03-15"
  → composite_key = "abc123def456"

Event 2: XS Nightclub + "Carl Cox" + "2026-03-15"
  → composite_key = "xyz789uvw012" ← different venue

Event 3: LIV Las Vegas + "Carl Cox" + "2026-03-15" (re-scrape)
  → composite_key = "abc123def456" ← same, skip insert
5 — Astro SSR Architecture

Astro 6 SSR with Cloudflare adapter and D1 binding:

graph LR
  A["D1
Database"] --> B["Astro SSR
Route Handler"]
  B --> C["Query Helpers
src/lib/queries.ts"]
  C --> D["Template
.astro file"]
  D --> E["Cloudflare Pages
Edge Runtime"]
  E --> F["Browser
vinny.vegas"]

  classDef db fill:#38bdf811,stroke:#38bdf844,stroke-width:1.5px
  classDef handler fill:#fb923c11,stroke:#fb923c44,stroke-width:1.5px
  classDef page fill:#a78bfa11,stroke:#a78bfa44,stroke-width:1.5px
  classDef browser fill:#fb718511,stroke:#fb718544,stroke-width:2px

  class A db
  class B,C handler
  class D,E page
  class F browser

Key Components:

D1 Binding
Astro.locals.runtime.env.DB — injected by Cloudflare adapter
Query Helpers
Prepared statements in src/lib/queries.ts — parameterized to prevent SQL injection
Route Handlers
src/pages/ — Astro routes, each can query D1 in getStaticProps or API handlers
Deployment
Push to GitLab → Cloudflare Pages → auto-builds and deploys on main branch
6 — Routes & Query Helpers

Astro routes and their corresponding D1 queries:

Route Purpose Query Helper Key Tables
/ Homepage with upcoming events list getUpcomingEvents() events, artists, images
/events All events with filters (date, venue) queryEvents(filters) events, event_artists
/events/[key] Single event detail page getEventByKey(key) events, event_artists, table_tiers
/venues All venues (LIV, XS, EBC, TAO...) getVenues() events (group by venue)
/venues/[id] Single venue with its events getVenueEvents(venueId) events filtered by venue_id
/artists/[slug] Artist bio with appearances getArtistBySlug(slug) artists, event_artists, images
/tables Table pricing browse/filter getTableTiers(filters) table_tiers, events

Query Helper Pattern:

export async function getEventByKey(db, key) {
  const stmt = db.prepare(
    `SELECT * FROM events
     WHERE composite_key = ?1`
  );
  return stmt.bind(key).first();
}
7 — Null Safety & SSR Gotchas
SSR Silent Failure: When template code throws during server-side render, Astro silently renders an empty <main> with HTTP 200. Dev console shows stack trace; production does not. This is the #1 debugging gotcha.

Safe Helpers in src/lib/safe.ts:

  • safeFormatDate(iso) — wraps format(parseISO(...)), returns empty string on error
  • safeParseJsonObject(str) — wraps JSON.parse(str), returns {} on parse error or null input
  • safeInitial(str) — safely accesses str[0], checks length first
JSON.parse("null") gotcha: This is valid JSON that returns JS null. Never assume JSON.parse() returns an object. Always use safeParseJsonObject().

Common SSR Triggers:

  • Calling format(parseISO(null)) on null date from D1
  • Object.keys(JSON.parse("null")) — crashes because JS null has no properties
  • Accessing str[0] on null artist name
  • Template iteration over potentially undefined arrays
XS Nightclub = best canary. XS has the sparsest data (minimal enrichment, no images for many artists). Always test new templates against XS events first.
8 — Troubleshooting

Common Errors:

Error Cause Fix
403 Forbidden
(from pricing API)		 Missing User-Agent header on HTTP request Add header: {"User-Agent": "Mozilla/5.0..."}
no such table D1 import failed silently (check wrangler logs) Re-run import with --force, check SQL syntax
empty <main>
on routes		 SSR template threw exception Check dev logs, use safe.ts helpers, test with XS events
artist_image_url null Images uploaded after D1 export Upload images first, then export D1 (check pipeline order)
composite_key collision Hash function produced same key for different events Verify venue_id + performer + date uniqueness before insert

Debug Checklist:

  • Is D1 database reachable? wrangler d1 execute --command "SELECT COUNT(*) FROM events;"
  • Are all 5 tables created? .schema in D1 CLI
  • Do events have composite_keys? Query SELECT COUNT(composite_key) FROM events;
  • Are images uploaded to R2? Check img.vinny.vegas bucket
  • Is R2_PUBLIC_URL set in wrangler.toml?
  • Check Cloudflare Pages build logs for SSR errors
  • Use XS Nightclub as test venue (sparsest data, catches rendering bugs)
Vinny Scraper — D1 to Astro Flow · Generated 2026-03-06 · docs/D1_DEPLOYMENT.md + docs/SITE_DEVELOPMENT.md