Cloudflare D1 Database
Relevant source files
The following files were used as context for generating this wiki page:
Cloudflare D1 serves as the primary relational data store for the Lou Gehrig Fan Club (LGFC) project. It handles diverse datasets including website content blocks, FAQs, membership-related data, and photo metadata. The database is integrated into the Next.js application via Cloudflare Pages Functions, providing a serverless SQL storage solution that supports both public site content and authenticated administrative workflows.
Sources: context.md:15, functions/api/d1-test.ts:31-72
Database Architecture and Schema
The D1 implementation utilizes a structured schema managed through SQL migrations. The system is designed to handle content management (CMS), user submissions, and media metadata.
Core Data Tables
The following table summarizes the primary entities defined in the initial schema:
| Table Name | Purpose | Primary Key |
|---|---|---|
join_requests | Stores mailing list and join form submissions. | id (INTEGER) |
library_entries | Stores fan stories and memories for the Gehrig library. | id (INTEGER) |
photos | Catalog of archive and memorabilia assets. | id (INTEGER) |
content_blocks | Current state of editable CMS blocks (draft/published). | key (TEXT) |
content_revisions | Historical versions of content blocks for auditing. | id (INTEGER) |
Sources: scripts/phase1_d1_init.sh:25-60, migrations/0011_cms_content_blocks.sql:5-32
CMS Logic and Versioning
The CMS module utilizes a draft-to-publish workflow. The content_blocks table tracks the status (draft or published) and manages versioning through an integer counter. When a block is updated, the published_body_md and published_at fields allow the system to serve stable content while new changes remain in a draft state.
The diagram shows the lifecycle of a content block from initial draft to publication and historical archiving.
Sources: migrations/0011_cms_content_blocks.sql:5-23, src/lib/cmsContent.ts:40-100
Integration and Data Flow
The database interacts with the application through three primary channels: Edge API functions, administrative scripts, and external synchronization tasks.
External Media Synchronization
A specialized shell script, b2_d1_incremental_sync.sh, manages the idempotent synchronization of objects from Backblaze B2 to the D1 photos table. This process uses a delta-based comparison to ensure only new files are inserted, preventing duplicates while maintaining a reference to external media.
This sequence illustrates the process of syncing external storage metadata into the D1 relational database.
Sources: scripts/b2_d1_incremental_sync.sh:137-250, scripts/B2_D1_SYNC_README.md:85-95
Serverless API Access
API routes defined in Cloudflare Pages Functions (located in /functions/api/) use the env.DB binding to interact with D1. These functions handle tasks such as retrieving published content for page rendering and performing health checks.
Key Helper Functions:
getPublishedBlocksByPage: Retrieves only blocks wherestatus = 'published'.getDraftBlocksByPage: Fetches all blocks for a page, used primarily in admin previews.requireTables: A diagnostic utility used in tests to ensure all mandatory tables exist.
Sources: src/lib/cmsContent.ts:34-92, functions/api/d1-test.ts:10-25
Administration and Diagnostics
The project includes diagnostic tools to monitor database health and connectivity. The /api/d1-test endpoint validates the D1 binding and confirms that all required migrations have been applied.
Health Check Diagnostics
The diagnostic system checks for the presence of core functional tables:
join_requestsjoin_email_loglogin_attemptsmembersphotos
Sources: functions/api/d1-test.ts:10-18
Migration Management
Migrations are applied using wrangler d1 migrations apply. Initial setup scripts like phase1_d1_init.sh automate the creation of the schema and verify table existence using sqlite_master lookups.
Sources: scripts/phase1_d1_init.sh:65-72, scripts/phase1_d1_apply.sh:55-60
Summary
Cloudflare D1 provides the LGFC project with a resilient, serverless SQL backend. By combining a structured migration-based schema with idempotent synchronization scripts and versioned CMS content, the system ensures data integrity for both user-generated content and administrative site management. Its integration with Cloudflare Pages Functions allows for high-performance data access at the edge.