Database migrations

The voting repository keeps D1 SQL in migrations/. Apply files in numeric order to the database bound as DB.

Apply migrations

Local database:

npx wrangler d1 migrations apply ihnyc-rc-vote --local

Production database:

npx wrangler d1 migrations apply ihnyc-rc-vote --remote

Review the pending list before confirming a remote operation. A Worker deploy does not replace the database migration step.

Sequence

File	Change
001_initial_schema.sql	Elections, token hashes, ballots, candidates, indexes
002_audit_logs.sql	Audit records and lookup indexes
003_invites.sql	Invitation delivery records
004_allow_null_token_hash.sql	Rebuild invites without the token foreign key so failed delivery can retain a record
005_add_token_plaintext.sql	Plaintext invitation token for resend
006_notion_integration.sql	Notion page, status, sync, and results-link fields
007_add_poll_ballot_type.sql	Add POLL to election and ballot constraints
008_add_results_emails_sent_at.sql	Automatic results-email completion timestamp
009_distribution_lists.sql	Reusable lists and member addresses
010_add_queued_invite_status.sql	Add QUEUED invite state
011_batch_invites.sql	Email magic links and elections.invite_mode
012_add_resend_count.sql	Invitation resend counter
013_add_invite_reminder_tracking.sql	Reminder timestamp, counter, and index
014_add_manual_results.sql	Imported result payload and certification fields
015_add_notion_attachments.sql	Serialized Notion attachment metadata
016_add_notion_sync_timestamps.sql	Separate Notion push and pull timestamps

Two additional SQL files are operational checks:

File	Purpose
012_add_resend_count_check.sql	Inspect whether resend_count is already present
014_add_manual_results_check.sql	Inspect manual-result columns before migration 014

They contain read-only checks and are not schema changes.

Table rebuilds

Migrations 004, 007, 010, and 011 recreate tables because SQLite cannot alter an existing check constraint or remove a foreign key in place.

Migration 011 is the broadest rebuild. It:

1. copies tokens, ballots, invites, and candidates into temporary tables;
2. replaces elections with a version containing invite_mode;
3. restores the dependent rows; and
4. recreates election indexes.

Verify dependent-row counts around any rebuild:

SELECT COUNT(*) FROM elections;
SELECT COUNT(*) FROM tokens;
SELECT COUNT(*) FROM ballots;
SELECT COUNT(*) FROM invites;
SELECT COUNT(*) FROM candidates;

Focused verification

After migrations 012–016, inspect the expected columns:

SELECT name, type
FROM pragma_table_info('invites')
WHERE name IN ('resend_count', 'reminder_sent_at', 'reminder_count');

SELECT name, type
FROM pragma_table_info('elections')
WHERE name IN (
  'manual_results_json',
  'manual_results_certified_at',
  'manual_results_certified_by',
  'manual_results_notes',
  'attachments_json',
  'notion_last_push_at',
  'notion_last_pull_at'
);

Migration 016 declares the push and pull columns as TEXT; application code currently writes millisecond numbers. D1’s SQLite storage accepts those values, and the application converts retrieved values with Number(...).

Current constraints

• tests/setup.ts builds a consolidated schema instead of applying this sequence and is missing fields from migrations 014–016.
• The numeric prefixes 012 and 014 are shared by a schema file and a check file. Confirm the exact pending filenames Wrangler reports rather than assuming one file per number.
• Migration 011 relies on temporary backup tables inside the migration; verify that a previous interrupted attempt did not leave those names behind before rerunning manually.

Sources

Verified against ihnyc-rc-vote commit 2451aa1.

• migrations/001_initial_schema.sql through migrations/016_add_notion_sync_timestamps.sql
• migrations/PRODUCTION_RUNBOOK_012.md
• tests/setup.ts
• wrangler.jsonc

Related

• Operations
• Testing
• Notion integration