Database
Database Migrations

Database Migrations

This document provides a comprehensive overview of all database migrations in the IBW Backend Service, organized chronologically and by feature area.

Migration Overview

The database schema has evolved through 75+ migrations, starting from the initial user table to complex features like quests, conferences, notifications, and enrichment systems.

Migration History

Phase 1: Core User System (Migrations 0001-0012)

0001_init.sql - Initial User Table

Creates the foundational users table with Telegram integration.

Key Fields:

  • id (BIGSERIAL PRIMARY KEY)
  • telegram_id (BIGINT UNIQUE) - Telegram user ID
  • username, first_name, last_name - User identification
  • photo_url - Profile picture
  • is_premium, allows_write_to_pm - Telegram flags
  • created_at, updated_at - Timestamps

0002_add_social_usernames.sql

Adds social media username fields to users table.

Fields Added:

  • twitter_username
  • linkedin_username
  • github_username

0003_create_linkedin_meta.sql

Creates linkedin_meta table for storing LinkedIn profile data.

Structure:

  • id (BIGSERIAL PRIMARY KEY)
  • user_id (BIGINT UNIQUE) - Foreign key to users
  • profile_data (JSONB) - LinkedIn profile information
  • Timestamps

0004_add_profession.sql

Adds professional information fields.

Fields Added:

  • job_title
  • organization

0005_create_connections.sql

Creates user connection system.

Table: connections

  • id (BIGSERIAL PRIMARY KEY)
  • requester_id (BIGINT) - Foreign key to users
  • recipient_id (BIGINT) - Foreign key to users
  • status (TEXT) - 'pending', 'accepted', 'rejected'
  • Unique constraint on (requester_id, recipient_id)

0006_create_twitter_meta.sql

Creates twitter_meta table for Twitter profile data.

Structure:

  • id (BIGSERIAL PRIMARY KEY)
  • user_id (BIGINT UNIQUE) - Foreign key to users
  • profile_data (JSONB) - Twitter profile information

0007_create_twitter_tweets.sql

Creates twitter_tweets table for storing user tweets.

Structure:

  • id (BIGSERIAL PRIMARY KEY)
  • user_id (BIGINT) - Foreign key to users
  • tweet_data (JSONB) - Tweet information
  • created_at - Tweet timestamp

0008_add_username_to_twitter_tweets.sql

Adds username field to twitter_tweets for easier querying.

0009_create_twitter_enrichments.sql

Creates AI-powered Twitter enrichment system.

Table: twitter_enrichments

  • id (BIGSERIAL PRIMARY KEY)
  • user_id (BIGINT UNIQUE) - Foreign key to users
  • topic_expertise (JSONB) - AI-detected expertise topics
  • project_affiliations (JSONB) - Project affiliations
  • crypto_role (VARCHAR) - Primary crypto role
  • key_stances (JSONB) - Key opinions
  • community_signals (JSONB) - Community affiliations
  • activity_profile (JSONB) - Activity type
  • tags (JSONB) - Twitter-specific tags

0010_create_crypto_enrichments.sql

Creates combined crypto enrichment system.

Table: crypto_enrichments

  • id (BIGSERIAL PRIMARY KEY)
  • user_id (BIGINT UNIQUE) - Foreign key to users
  • primary_chains (JSONB) - Top blockchain chains
  • expertise_areas (JSONB) - Key expertise areas
  • networking_pitch (TEXT) - AI-generated networking pitch
  • collab_interests (JSONB) - Collaboration interests
  • profile_type (VARCHAR) - Profile classification
  • sources_used (JSONB) - Data sources used

0011_add_summary_fields.sql

Adds summary and description fields to users.

Fields Added:

  • summary (TEXT)
  • description (TEXT)

0012_add_onboarding_completed.sql

Adds onboarding tracking.

Fields Added:

  • onboarding_completed (BOOLEAN DEFAULT FALSE)

0013_add_role_ecosystem_topics.sql

Adds user categorization fields.

Fields Added:

  • roles (TEXT[]) - Array of user roles
  • ecosystems (TEXT[]) - Array of ecosystems
  • topics (TEXT[]) - Array of topics

Phase 2: Events System (Migrations 0014-0016)

0014_create_events.sql

Creates comprehensive events system.

Table: events

  • id (SERIAL PRIMARY KEY)
  • title, subtitle - Event information
  • date, start_time, end_time, timezone - Timing
  • organizer, organizer_logo - Organizer info
  • location_name, location_address, location_lat, location_lng - Location
  • status (VARCHAR) - 'upcoming', 'ongoing', 'past', 'cancelled'
  • format (VARCHAR) - 'in-person', 'online', 'hybrid'
  • capacity, current_attendees, waitlist_count - Capacity management
  • is_featured, is_paid, price, currency - Features
  • speaker_ids (TEXT[]) - Array of speaker IDs
  • sponsors (JSONB) - Sponsor information
  • social_sharing (JSONB) - Social sharing config

Indexes:

  • idx_events_status
  • idx_events_date
  • idx_events_featured

0015_create_event_attendees.sql

Creates event attendance tracking.

Table: event_attendees

  • id (SERIAL PRIMARY KEY)
  • event_id (INTEGER) - Foreign key to events
  • user_id (INTEGER) - Foreign key to users
  • status (VARCHAR) - 'attending', 'waitlisted', 'cancelled'
  • registered_at, updated_at - Timestamps
  • Unique constraint on (event_id, user_id)

Indexes:

  • idx_event_attendees_event_id
  • idx_event_attendees_user_id
  • idx_event_attendees_status

0016_insert_initial_events.sql

Inserts initial event data.

Phase 3: Quest System (Migrations 0017-0027)

0017_create_quests.sql

Creates gamification quest system.

Table: quests

  • id (VARCHAR PRIMARY KEY) - String ID
  • title, subtitle, description - Quest information
  • tag, category - Categorization
  • reward, reward_description, reward_image - Rewards
  • points (INT) - Points awarded
  • deadline (TIMESTAMP) - Quest deadline
  • sponsor, sponsor_logo, sponsor_website - Sponsor info
  • featured (BOOLEAN) - Featured flag
  • background_color, border_color - Styling
  • difficulty, estimated_completion_time - Quest metadata
  • quest_type, raffle_draw_date - Additional features

Table: quest_tasks

  • id (VARCHAR PRIMARY KEY)
  • quest_id (VARCHAR) - Foreign key to quests
  • type (VARCHAR) - Task type
  • description, detail_description - Task description
  • points (INT) - Points for completion
  • action_type, action_url - Action configuration
  • verification_type - Verification method
  • repeatable, repeat_frequency - Repeat configuration
  • start_date, end_date - Task availability
  • is_mandatory - Mandatory flag
  • required_hashtags (JSONB) - Required hashtags
  • suggested_text, tips, example - Guidance
  • unlock_condition (JSONB) - Unlock conditions

Table: user_quest_tasks

  • id (VARCHAR PRIMARY KEY)
  • user_id (BIGINT) - Foreign key to users
  • quest_id (VARCHAR) - Foreign key to quests
  • quest_task_id (VARCHAR) - Foreign key to quest_tasks
  • completed (BOOLEAN) - Completion status
  • completed_at, verified_at - Timestamps
  • points_earned (INT) - Points earned
  • proof (TEXT) - Proof of completion
  • Unique constraint on (user_id, quest_task_id)

Indexes:

  • Multiple indexes for performance optimization

0018_insert_community_quest.sql

Inserts initial community quest data.

0019_add_metadata_to_quest_tasks.sql

Adds metadata field to quest_tasks for flexible task configuration.

Field Added:

  • metadata (JSONB) - Flexible metadata storage

0020_verification_required.sql

Adds verification requirement flag.

Field Added:

  • proof_required (BOOLEAN) - Whether proof is required

0021_approval_rejection_metadata.sql

Adds approval/rejection metadata to user_quest_tasks.

Fields Added:

  • metadata (JSONB) - Approval/rejection metadata

0022_create_user_notifications.sql

Creates user notification system.

Table: user_notifications

  • id (UUID PRIMARY KEY)
  • user_id (BIGINT) - Foreign key to users
  • type (VARCHAR) - Notification type
  • title, message (TEXT) - Notification content
  • is_clicked, is_dismissed (BOOLEAN) - Status flags
  • action_data (JSONB) - Action data
  • Timestamps

Indexes:

  • Multiple indexes for efficient querying

0023_add_quest_registration_approval.sql

Adds quest registration approval system.

Fields Added to quests:

  • registration_approval_required (BOOLEAN)
  • approval_form_fields (JSONB)

Table: quest_registration_requests

  • id (UUID PRIMARY KEY)
  • user_id (BIGINT) - Foreign key to users
  • quest_id (VARCHAR) - Foreign key to quests
  • status (VARCHAR) - 'pending', 'approved', 'rejected'
  • form_data (JSONB) - Form submission data
  • admin_notes (TEXT) - Admin notes
  • processed_by (BIGINT) - Foreign key to users (admin)
  • processed_at (TIMESTAMP) - Processing timestamp
  • Unique constraint on (user_id, quest_id)

0024_add_points_disabled_to_quests.sql

Adds ability to disable points for quests.

Field Added:

  • points_disabled (BOOLEAN) - Disable points

0025_add_reward_to_quest_registration.sql

Adds reward field to quest registration requests.

Field Added:

  • reward (JSONB) - Reward assigned to user

0026_add_copies_to_quests.sql

Adds copies/templates field to quests.

Field Added:

  • copies (JSONB) - Reward copies/template

Phase 4: Conference System (Migrations 0027-0039)

0027_add_slug_to_events.sql

Adds slug field to events for URL-friendly identifiers.

Field Added:

  • slug (VARCHAR UNIQUE)

0028_add_conference_slug_to_events.sql

Adds conference slug field to events.

Field Added:

  • conference_slug (VARCHAR)

0029_create_conferences.sql

Creates comprehensive conference system.

Table: conferences

  • id (SERIAL PRIMARY KEY)
  • slug (VARCHAR UNIQUE) - URL-friendly identifier
  • name, tagline, description, short_description - Conference info
  • logo_url, banner_url, hero_image_url, favicon_url - Media
  • start_date, end_date, timezone - Dates
  • status (VARCHAR) - 'upcoming', 'ongoing', 'past', 'cancelled', 'draft'
  • city, country, venue_name, venue_address, venue_lat, venue_lng - Location
  • is_virtual, virtual_platform, virtual_url - Virtual conference
  • organizer_name, organizer_logo, organizer_website, organizer_email, organizer_phone - Organizer
  • Social media URLs (twitter, linkedin, instagram, facebook, youtube, telegram, discord)
  • registration_url, ticket_url, is_free, price_range, currency - Registration
  • max_attendees, current_attendees, is_featured, is_public, requires_approval - Settings
  • theme_color, secondary_color, custom_css, custom_js - Branding
  • SEO fields (meta_title, meta_description, meta_keywords, og_image)
  • Analytics fields (google_analytics_id, facebook_pixel_id, custom_tracking_code)
  • tags, categories, languages (TEXT[]) - Categorization
  • created_by, updated_by (BIGINT) - Foreign keys to users

Indexes:

  • Multiple indexes for performance

Foreign Key:

  • Adds conference_id to events table

0030_add_email_verification.sql

Adds email verification system to users.

Fields Added:

  • email (VARCHAR)
  • verification_code (VARCHAR)
  • email_verified (BOOLEAN DEFAULT FALSE)

0031_add_message_to_quest_registrations.sql

Adds message field to quest registration requests.

Field Added:

  • message (TEXT) - Message to participant

0031_add_notifications_enabled.sql

Adds notifications enabled flag to users.

Field Added:

  • notifications_enabled (BOOLEAN DEFAULT FALSE)

0032_add_event_approval_status.sql

Adds approval workflow to events.

Field Added:

  • approval_status (VARCHAR) - 'pending', 'approved', 'rejected'

0033_add_conference_mapping_to_quests.sql

Adds conference mapping to quests.

Fields Added:

  • conference_id (INTEGER) - Foreign key to conferences
  • conference_slug (VARCHAR)

0034_create_speakers_table.sql

Creates comprehensive speaker system.

Table: speakers

  • id (SERIAL PRIMARY KEY)
  • first_name, last_name, email (UNIQUE) - Basic info
  • phone, bio, title, organization - Professional info
  • photo_url, website - Media
  • Social media URLs (linkedin, twitter, github, instagram, facebook, youtube, personal_website)
  • location, country, city - Location
  • languages, expertise, topics (TEXT[]) - Categorization
  • experience, education, certifications (TEXT[]) - Background
  • availability, travel_willing, virtual_willing - Availability
  • fee_range, currency - Pricing
  • is_verified, is_active, is_featured - Status flags
  • rating, rating_count, total_talks, total_events - Statistics
  • last_talk_date - Last talk timestamp

Table: event_speakers (Junction Table)

  • id (SERIAL PRIMARY KEY)
  • event_id (BIGINT) - Foreign key to events
  • speaker_id (BIGINT) - Foreign key to speakers
  • role (VARCHAR) - 'keynote', 'panelist', 'moderator', etc.
  • order (INTEGER) - Display order
  • Unique constraint on (event_id, speaker_id)

Table: conference_speakers (Junction Table)

  • id (SERIAL PRIMARY KEY)
  • conference_id (BIGINT) - Foreign key to conferences
  • speaker_id (BIGINT) - Foreign key to speakers
  • role (VARCHAR) - Speaker role
  • order (INTEGER) - Display order
  • Unique constraint on (conference_id, speaker_id)

Indexes:

  • Multiple indexes including GIN indexes for array fields

0035_add_event_search_indexes.sql

Adds search indexes for events.

0035_make_speaker_email_optional.sql

Makes speaker email optional.

Change:

  • email field becomes nullable

0036_speaker_user_mapping.sql

Adds user mapping to speakers.

Field Added:

  • user_id (BIGINT) - Foreign key to users

0037_add_experience_location_to_users.sql

Adds experience and location to users.

Fields Added:

  • experience (TEXT)
  • location (TEXT)

0038_create_conference_attendees.sql

Creates conference attendance tracking.

Table: conference_attendees

  • id (SERIAL PRIMARY KEY)
  • user_id (BIGINT) - Foreign key to users
  • conference_id (INTEGER) - Foreign key to conferences
  • registration_date (TIMESTAMP) - Registration timestamp
  • status (VARCHAR) - 'registered', 'attended', 'cancelled'
  • email (VARCHAR) - Attendee email
  • Unique constraint on (user_id, conference_id)

Indexes:

  • Multiple indexes for performance

0039_add_active_to_conferences.sql

Adds active flag to conferences.

Field Added:

  • is_active (BOOLEAN DEFAULT TRUE)

Phase 5: Additional Features (Migrations 0040-0075)

0040-0075: Various Feature Additions

Key Additions:

  • Quest data inserts (0040, 0045, 0052, 0057, 0066, 0071, 0073, 0074)
  • Phone number to users (0049)
  • POG image URL to users (0050)
  • KGen username to users (0051)
  • KGen quest flag to quests (0056)
  • Rate limits table (0043)
  • Connection messages (0044)
  • Quest active flag (0046)
  • Notification jobs system (0053, 0054, 0069, 0070)
  • Conference agenda (0059, 0060, 0061, 0062)
  • Speaker slugs (0063)
  • Speaker socials (0064, 0065)
  • Quest notification templates (0067, 0068)
  • Poll results indexes (0072)
  • Feedback table (0075)

Migration Statistics

  • Total Migrations: 75+
  • Core Tables: 20+
  • Junction Tables: 5+
  • Enrichment Tables: 3
  • Indexes: 100+

Migration Best Practices

  1. Always test migrations in development first
  2. Backup database before running migrations in production
  3. Review foreign key constraints to understand dependencies
  4. Check indexes for performance optimization
  5. Verify data integrity after migrations
  6. Document breaking changes in migration comments

Running Migrations

# Run all pending migrations
make migrate-up
 
# Rollback last migration
make migrate-down
 
# Check migration status
migrate -path migrations -database "$DATABASE_URL" version

Migration Dependencies

Migrations should be run in order as they have dependencies:

  • User table must exist before any user-related tables
  • Events must exist before event_attendees
  • Quests must exist before quest_tasks
  • Conferences must exist before conference-related tables
Quick Start GuideEntity Relationships