Beacon Platform Docs

D1 Migration Catalog

3 min read

Catalog of all D1 schema migrations applied to the Beacon platform database. Run in the listed order; each migration is safe to re-run where noted.

Run Order

schema.sql (baseline)
add_progress_tracking.sql
add_ai_quota_tracking.sql
enhance_ai_quota_tracking.sql / enhance_ai_quota_tracking_safe.sql
add_quota_bypass_setting.sql
add_parallel_digest_pipeline_settings.sql
add_ai_two_stage_pipeline_fields.sql
add_summary_original.sql
add_weekly_health_snapshots.sql
add_weekly_concept_graph_public.sql
add_weekly_progress_tracking.sql
add_community_strain.sql / add_community_strain_safe.sql
add_raw_export_retention.sql
add_chat_source_dimension.sql
add_media_analysis_tables.sql
add_media_content_hash.sql
add_video_frame_media_analysis.sql
migrate_null_to_ihouse_main.sql
fix_daily_digests_weekly_summaries_pk.sql
add_performance_indexes.sql

Migrations

add_progress_tracking.sql

Adds days_processed and days_total to exports for tracking batch progress.

add_ai_quota_tracking.sql

Creates ai_quota_usage and ai_usage_log tables. Daily call counters and per-call logs for text AI costs.

enhance_ai_quota_tracking.sql / enhance_ai_quota_tracking_safe.sql

Adds neuron tracking and weekly model call counters to ai_quota_usage. Use the _safe variant on existing databases to avoid column-already-exists errors.

add_quota_bypass_setting.sql

Adds the quota_bypass_enabled setting key to quota_settings for temporarily disabling quota enforcement.

add_parallel_digest_pipeline_settings.sql

Adds daily_pipeline_mode, daily_concurrency, and daily_start_stagger_ms settings to quota_settings to control the parallel digest pipeline.

add_ai_two_stage_pipeline_fields.sql

Adds analysis_model_used and narrative_model_used columns to daily_digests and weekly_summaries_public to track which model handled each stage.

add_summary_original.sql

Adds summary_original to daily_digests and weekly_summaries_public for storing the raw pre-edit summary text.

add_weekly_health_snapshots.sql

Creates weekly_health_snapshots_public with composite PK (community_id, source_id, week_start).

add_weekly_concept_graph_public.sql

Creates weekly_concept_graph_public for storing concept graph node/edge data with pillar assignments. Composite PK includes source_id from the start (not affected by the PK fix migration).

add_weekly_progress_tracking.sql

Adds weeks_total and weeks_processed to exports for tracking weekly summary generation progress per export.

add_community_strain.sql / add_community_strain_safe.sql

Adds Community Strain Index (CSI) columns to daily_digests and weekly_summaries_public: strain_score, strain_level, strain_signals_json, strain_confidence.

add_raw_export_retention.sql

Adds R2 retention columns to exports: raw_delete_after_processing, raw_deleted_at, raw_cleanup_error, raw_retention_hours.

add_chat_source_dimension.sql

Creates the chat_sources table and adds source_id to exports, message_hashes, daily_digests, and weekly_summaries_public. Prerequisite for the PK fix migration.

add_media_analysis_tables.sql

Creates export_media and media_analysis tables for attachment tracking and AI-generated media metadata.

add_media_content_hash.sql

Adds content_hash to export_media for deduplication — avoids re-analyzing the same file across exports.

add_video_frame_media_analysis.sql

Adds media_ai_quota_usage table and video-frame-specific columns (video_frame_extractions, video_frame_stage1_calls, video_frame_stage2_calls) to quota tracking.

migrate_null_to_ihouse_main.sql

One-time data migration: backfills source_id = 'ihouse-main-whatsapp-group' for rows that had NULL before multi-source support.

fix_daily_digests_weekly_summaries_pk.sql (2026-04-25)

Critical. Recreates daily_digests and weekly_summaries_public with source_id in the primary key. Prior to this migration both tables had source_id in a secondary unique index only; INSERT OR REPLACE would resolve on the old PK and silently delete rows for a different source sharing the same day or week. All data is preserved via rename-create-copy-drop. Run after add_chat_source_dimension.sql.

See also: data-model.

add_performance_indexes.sql (2026-04-25)

Adds two missing composite indexes:

  • idx_export_media_export_community ON export_media(export_id, community_id) — eliminates full-table scans in the /pulse/history.json media-count aggregation query.
  • idx_exports_status_community ON exports(status, community_id) — speeds up the cron self-healing query that filters stuck exports by status.

Both use CREATE INDEX IF NOT EXISTS and are safe to re-run.

Notes

  • SQLite (D1) does not support ALTER PRIMARY KEY. Migrations that change PKs use the rename-create-copy-drop pattern.
  • Use the _safe variant of a migration when re-running on a database that already has some of the columns.
  • Shell scripts (run_*.sh) in the migrations folder automate applying individual migrations via wrangler d1 execute.
  • After applying add_performance_indexes.sql, verify with wrangler d1 execute beacon-pulse-db --command "EXPLAIN QUERY PLAN SELECT ..." that the new indexes are used.

Graph View

Backlinks