Entity Relationships

This document describes the relationships between all entities in the IBW Backend Service database, with visual flowcharts showing how entities connect.

Core Entity Relationships

User-Centric Relationships

The users table is the central entity, connecting to most other tables in the system.

Event System Relationships

Events connect to conferences, speakers, and attendees.

Quest System Relationships

The quest system has a hierarchical structure with quests, tasks, and user progress.

Conference System Relationships

Conferences connect to events, speakers, attendees, and agenda items.

Notification System Relationships

Notifications connect users to various notification types and jobs.

Enrichment System Relationships

Enrichment tables provide AI-powered insights about users.

Complete Entity Relationship Diagram

Relationship Types

One-to-One (1:1)

• users ↔ linkedin_meta - One user has one LinkedIn meta
• users ↔ twitter_meta - One user has one Twitter meta
• users ↔ twitter_enrichments - One user has one Twitter enrichment
• users ↔ crypto_enrichments - One user has one crypto enrichment
• users ↔ speakers - One user can be one speaker (optional)

One-to-Many (1:N)

• users → connections - One user can have many connections (as requester or recipient)
• users → event_attendees - One user can attend many events
• users → conference_attendees - One user can attend many conferences
• users → user_quest_tasks - One user can complete many quest tasks
• users → user_notifications - One user can receive many notifications
• conferences → events - One conference can have many events
• conferences → conference_attendees - One conference can have many attendees
• conferences → conference_agenda - One conference can have many agenda items
• conferences → quests - One conference can have many quests
• quests → quest_tasks - One quest can have many tasks
• quests → user_quest_tasks - One quest can have many user progress records
• events → event_attendees - One event can have many attendees
• notification_jobs → notification_job_items - One job can have many items

Many-to-Many (M:N)

• users ↔ users (via connections) - Users can connect to many users
• events ↔ speakers (via event_speakers) - Events can have many speakers, speakers can be in many events
• conferences ↔ speakers (via conference_speakers) - Conferences can have many speakers, speakers can be in many conferences
• users ↔ quests (via user_quest_tasks) - Users can participate in many quests, quests can have many users
• users ↔ quests (via quest_registration_requests) - Users can request registration for many quests

Foreign Key Constraints

Cascade Deletes

Most relationships use ON DELETE CASCADE to maintain referential integrity:

• Deleting a user deletes their connections, attendances, quest progress, etc.
• Deleting an event deletes its attendees
• Deleting a quest deletes its tasks and user progress
• Deleting a conference deletes its events, attendees, and agenda

Unique Constraints

• (requester_id, recipient_id) in connections - Prevents duplicate connections
• (event_id, user_id) in event_attendees - One user per event
• (conference_id, user_id) in conference_attendees - One user per conference
• (user_id, quest_task_id) in user_quest_tasks - One completion per task per user
• (user_id, quest_id) in quest_registration_requests - One registration request per quest per user
• (event_id, speaker_id) in event_speakers - One speaker per event (with role)
• (conference_id, speaker_id) in conference_speakers - One speaker per conference (with role)

Indexes for Performance

User-Related Indexes

• users.telegram_id - Unique index for fast user lookup
• connections.requester_id, connections.recipient_id - Fast connection queries
• event_attendees.user_id - Fast user event lookup
• user_quest_tasks.user_id - Fast quest progress lookup

Event-Related Indexes

• events.status, events.date, events.is_featured - Fast event filtering
• events.conference_id - Fast conference event lookup
• event_attendees.event_id - Fast attendee lookup

Quest-Related Indexes

• quests.featured, quests.category - Fast quest filtering
• quest_tasks.quest_id - Fast task lookup
• user_quest_tasks.completed, user_quest_tasks.completed_at - Fast progress queries

Conference-Related Indexes

• conferences.slug - Unique index for URL lookup
• conferences.status, conferences.start_date - Fast filtering
• conference_attendees.conference_id - Fast attendee lookup

Data Flow Patterns

User Registration Flow

1. User created in users table
2. Optional: LinkedIn/Twitter meta created
3. Optional: Enrichments generated
4. User can register for events/conferences
5. User can join quests
6. User receives notifications

Quest Completion Flow

1. User requests quest registration (if approval required)
2. Admin approves/rejects in quest_registration_requests
3. User starts quest, creates user_quest_tasks records
4. User completes tasks, updates user_quest_tasks
5. Admin verifies tasks (if required)
6. Points awarded, progress tracked
7. Quest completion triggers notifications

Event Attendance Flow

1. Event created in events table
2. Users register via event_attendees
3. If capacity reached, users waitlisted
4. Event status updated based on date
5. Attendees can be queried for event

Notification Flow

1. Admin creates notification_job with criteria
2. System finds eligible users
3. Creates notification_job_items for each user
4. Processes items, sends notifications
5. Updates user_notifications table
6. Tracks success/failure in job items