Database Migrations
Relevant source files
The following files were used as context for generating this wiki page:
The database migration system in this project manages the schema and initial data for Cloudflare D1, a SQLite-compatible serverless database. The system handles the creation of tables for core site features including user join requests, library entries, photo catalogs, and CMS content blocks. Migrations are executed using the wrangler CLI and are organized into a sequential series of SQL files located in the migrations/ directory.
Sources: scripts/phase1_d1_init.sh:5-10, context.md:15-18
Migration Workflow and Execution
The project employs a structured "Phase 1" approach for initializing and applying the database schema. This process involves creating a migration placeholder via npx wrangler d1 migrations create, overwriting that file with a predefined schema, and then applying it to the remote database instance.
Initial Schema Application Flow
The application of the initial schema follows a specific sequence to ensure the database environment is correctly provisioned.
The diagram shows the automated steps for provisioning the lgfc_lite database schema.
Sources: scripts/phase1_d1_init.sh:12-58, scripts/phase1_d1_apply.sh:10-53
Core Data Schema
The migration files define several critical tables that support the application's functionality. These range from user-submitted forms to content management systems.
Primary Application Tables
The initial schema establishes the foundation for user interactions and media management.
| Table Name | Primary Purpose | Key Fields |
|---|---|---|
join_requests | Mailing list and join form submissions | id, name, email, message, created_at |
library_entries | Fan library submissions (stories/content) | id, name, email, title, content, is_approved |
photos | Photo catalog for archive and memorabilia | id, photo_id, url, is_memorabilia, description |
Sources: scripts/phase1_d1_init.sh:33-58, scripts/phase1_d1_apply.sh:25-50
Content Management System (CMS) Schema
Phase 2 of the migration strategy introduces the CMS Data Layer foundation, enabling DB-backed editable content blocks.
This ER diagram illustrates the relationship between live content states and their historical revisions.
Sources: migrations/0008_page_content.sql:4-24, migrations/0011_cms_content_blocks.sql:5-30
Idempotent Data Seeding
The system includes idempotent seeding scripts that populate the database with initial live copy blocks if they are missing. This ensures the site has baseline content upon deployment without overwriting manual edits made in production.
Seeded Content Structure
The 0009_page_content_seed.sql file provides initial content for various routes:
- Routes:
/,/about,/charities,/contact,/privacy,/terms,/photos,/memorabilia. - Sections:
title,lead_html,body_html,hero_title,join_banner_html.
Sources: migrations/0009_page_content_seed.sql:4-44
Email Content Management
Specific migrations like 0023_welcome_email_content.sql manage email-specific copy, storing the "top-half" of the welcome email in D1 for administrative editing.
| Field | Type | Description |
|---|---|---|
title | TEXT | Description of the email block (Default: 'Welcome Email') |
body_md | TEXT | The Markdown content of the email |
status | TEXT | Visibility status (posted/hidden) |
updated_at | TEXT | Timestamp of last modification |
Sources: migrations/0023_welcome_email_content.sql:4-10
Schema Constraints and Optimization
Migrations enforce data integrity through SQLite features:
- Foreign Keys: Enabled via
PRAGMA foreign_keys = ON;. - Indices: Unique indices are used on
page_content (slug, section, status)andcontent_blocks (page, status, updated_at)to optimize content retrieval. - Views: A lightweight view
v_page_content_liveprovides easy access to current live content for debugging.
Sources: scripts/phase1_d1_init.sh:30, migrations/0008_page_content.sql:18-32, migrations/0011_cms_content_blocks.sql:22-35
Database migrations serve as the authoritative record of the lgfc_lite database evolution, transitioning the project from a static build to a dynamic, database-driven application supporting CMS capabilities and user engagement.