IHNYC RC Platform Docs

Database Migrations

3 min read

This directory contains SQL migration files for the D1 database.

Running Migrations

Local Development

# Create local D1 database (if not exists)
wrangler d1 create ihnyc-rc-vote
 
# Run migrations
wrangler d1 execute ihnyc-rc-vote --file=./migrations/001_initial_schema.sql

Production

Migrations should be run manually via Wrangler CLI or Cloudflare Dashboard before deploying the Worker.

Migration Files

  • 001_initial_schema.sql - Initial schema with elections, tokens, ballots, and candidates tables
  • 002_audit_logs.sql - Audit logs table for security and observability
  • 003_invites.sql - Invites table for tracking email invite delivery status
  • 004_allow_null_token_hash.sql - Allow NULL token_hash in invites (for failed emails)
  • 005_add_token_plaintext.sql - Add token_plaintext column for resend functionality
  • 006_notion_integration.sql - Notion database integration fields
  • 007_add_poll_ballot_type.sql - Add poll ballot type support
  • 008_add_results_emails_sent_at.sql - Track when results emails were sent
  • 009_distribution_lists.sql - Distribution lists for bulk email management
  • 010_add_queued_invite_status.sql - Add QUEUED status for invites
  • 011_batch_invites.sql - Batch invite system with magic links
  • 012_add_resend_count.sql - Add resend_count column to track email resend attempts

Production Runbook: 012_add_resend_count

Overview

This migration adds a resend_count column to the invites table to track how many times an email has been resent.

Safety Assessment

  • Safe for production (ADD COLUMN with DEFAULT)
  • No data loss
  • No downtime required
  • Backward compatible (code handles missing column with COALESCE)

Pre-Migration Checklist

  1. Get Time Travel bookmark
wrangler d1 time-travel info ihnyc-rc-vote --remote
  1. Verify column doesn’t exist
wrangler d1 execute ihnyc-rc-vote --remote \
  --command="SELECT COUNT(*) as column_exists FROM pragma_table_info('invites') WHERE name='resend_count';"
  1. Optional: check current invites count
wrangler d1 execute ihnyc-rc-vote --remote \
  --command="SELECT COUNT(*) as total_invites FROM invites;"

Migration Execution

  1. Run pre-flight check
wrangler d1 execute ihnyc-rc-vote --remote \
  --file=./migrations/012_add_resend_count_check.sql
  1. Run migration
wrangler d1 execute ihnyc-rc-vote --remote \
  --file=./migrations/012_add_resend_count.sql

Post-Migration Verification

  1. Verify column exists
wrangler d1 execute ihnyc-rc-vote --remote \
  --command="SELECT COUNT(*) as column_exists FROM pragma_table_info('invites') WHERE name='resend_count';"
  1. Verify default values
wrangler d1 execute ihnyc-rc-vote --remote \
  --command="SELECT COUNT(*) as total, SUM(resend_count) as total_resends FROM invites;"
  1. Test application
  • Log into admin panel
  • Verify invites list loads
  • Verify resend counter displays

Rollback Plan

If needed, restore to the saved Time Travel bookmark:

wrangler d1 time-travel restore ihnyc-rc-vote --remote --bookmark=<BOOKMARK>

Troubleshooting

  • “duplicate column name”: migration already applied; verify column exists and proceed
  • “no such table: invites”: run earlier migrations first (001-011)

Migration Safety

When altering tables with foreign keys, follow the pattern in migrations/011_batch_invites.sql:

  1. Backup dependent tables (tokens, ballots, invites, candidates)
  2. Recreate the main table with new schema
  3. Restore data from backups
  4. Drop backup tables

This prevents data loss during schema changes.

See Also

Graph View

Backlinks