Database Schema Reference

Complete reference of all database tables, their fields, relationships, and constraints.

Core Tables

users

The central user table storing all user information.

Column	Type	Constraints	Description
id	BIGSERIAL	PRIMARY KEY	Unique user identifier
telegram_id	BIGINT	UNIQUE, NOT NULL	Telegram user ID
username	TEXT		Telegram username
first_name	TEXT		User's first name
last_name	TEXT		User's last name
photo_url	TEXT		Profile picture URL
language_code	TEXT		Telegram language code
is_premium	BOOLEAN	NOT NULL, DEFAULT FALSE	Telegram premium status
allows_write_to_pm	BOOLEAN	NOT NULL, DEFAULT FALSE	PM permission flag
onboarding_completed	BOOLEAN	NOT NULL, DEFAULT FALSE	Onboarding status
notifications_enabled	BOOLEAN	NOT NULL, DEFAULT FALSE	Notification preference
email	VARCHAR		User email address
verification_code	VARCHAR		Email verification code
email_verified	BOOLEAN	NOT NULL, DEFAULT FALSE	Email verification status
twitter_username	VARCHAR		Twitter handle
linkedin_username	VARCHAR		LinkedIn username
github_username	VARCHAR		GitHub username
job_title	VARCHAR		Professional title
organization	VARCHAR		Organization name
roles	TEXT[]	DEFAULT ''	Array of user roles
ecosystems	TEXT[]	DEFAULT ''	Array of ecosystems
topics	TEXT[]	DEFAULT ''	Array of topics
experience	TEXT		Experience level
location	TEXT		User location
phone_number	VARCHAR		Phone number
country_code	VARCHAR		Country code
pog_image_url	VARCHAR		POG card image URL
kgen_username	VARCHAR		KGen username
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Update timestamp

Indexes:

users_telegram_id_key (UNIQUE)
Various indexes on common query fields

Relationships:

One-to-one: linkedin_meta, twitter_meta, twitter_enrichments, crypto_enrichments
One-to-many: connections (as requester/recipient), event_attendees, conference_attendees, user_quest_tasks, user_notifications, feedback, rate_limits
Optional one-to-one: speakers

connections

User connection requests and status.

Column	Type	Constraints	Description
id	BIGSERIAL	PRIMARY KEY	Connection ID
requester_id	BIGINT	NOT NULL, FK → users(id)	User who sent request
recipient_id	BIGINT	NOT NULL, FK → users(id)	User who received request
status	TEXT	NOT NULL, CHECK	'pending', 'accepted', 'rejected'
message	TEXT		Connection message
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Update timestamp

Constraints:

UNIQUE (requester_id, recipient_id)
CHECK status IN ('pending', 'accepted', 'rejected')

Indexes:

idx_connections_requester_id
idx_connections_recipient_id

events

Event information and details.

Column	Type	Constraints	Description
id	SERIAL	PRIMARY KEY	Event ID
title	VARCHAR(255)	NOT NULL	Event title
slug	VARCHAR(255)	UNIQUE	URL-friendly identifier
conference_id	INTEGER	FK → conferences(id)	Associated conference
conference_slug	VARCHAR		Conference slug
subtitle	VARCHAR(255)		Event subtitle
image	VARCHAR(255)		Event image URL
date	DATE	NOT NULL	Event date
time_description	VARCHAR(255)		Time description
start_time	VARCHAR(50)		Start time
end_time	VARCHAR(50)		End time
timezone	VARCHAR(50)		Timezone
organizer	VARCHAR(255)	NOT NULL	Organizer name
organizer_logo	VARCHAR(255)		Organizer logo URL
city	VARCHAR(255)		Event city
description	TEXT		Event description
tags	TEXT[]		Event tags
calendar_link	VARCHAR(255)		Calendar link
registration_url	VARCHAR(255)		Registration URL
location_name	VARCHAR(255)		Location name
location_address	TEXT		Location address
location_lat	FLOAT		Latitude
location_lng	FLOAT		Longitude
status	VARCHAR(20)	CHECK	'upcoming', 'ongoing', 'past', 'cancelled'
capacity	INTEGER		Event capacity
current_attendees	INTEGER	DEFAULT 0	Current attendee count
waitlist_count	INTEGER	DEFAULT 0	Waitlist count
is_featured	BOOLEAN	DEFAULT FALSE	Featured flag
is_paid	BOOLEAN	DEFAULT FALSE	Paid event flag
price	FLOAT		Event price
currency	VARCHAR(10)		Currency code
ticket_url	VARCHAR(255)		Ticket URL
category	VARCHAR(100)		Event category
format	VARCHAR(20)	CHECK	'in-person', 'online', 'hybrid'
event_url	VARCHAR(255)		Event URL
stream_url	VARCHAR(255)		Stream URL
recording_url	VARCHAR(255)		Recording URL
speaker_ids	TEXT[]		Array of speaker IDs
sponsors	JSONB		Sponsor information
social_sharing	JSONB		Social sharing config
approval_status	VARCHAR(20)		'pending', 'approved', 'rejected'
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Update timestamp

Indexes:

idx_events_status
idx_events_date
idx_events_featured
idx_events_conference_id
idx_events_slug

Relationships:

Many-to-one: conferences (via conference_id)
One-to-many: event_attendees, event_speakers

event_attendees

Event attendance tracking.

Column	Type	Constraints	Description
id	SERIAL	PRIMARY KEY	Attendee record ID
event_id	INTEGER	NOT NULL, FK → events(id)	Event ID
user_id	INTEGER	NOT NULL, FK → users(id)	User ID
status	VARCHAR(20)	CHECK, DEFAULT 'attending'	'attending', 'waitlisted', 'cancelled'
registered_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Registration timestamp
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Update timestamp

Constraints:

UNIQUE (event_id, user_id)
CHECK status IN ('attending', 'waitlisted', 'cancelled')

Indexes:

idx_event_attendees_event_id
idx_event_attendees_user_id
idx_event_attendees_status

conferences

Conference information and configuration.

Column	Type	Constraints	Description
id	SERIAL	PRIMARY KEY	Conference ID
slug	VARCHAR(255)	UNIQUE, NOT NULL	URL-friendly identifier
name	VARCHAR(255)	NOT NULL	Conference name
tagline	VARCHAR(500)		Conference tagline
description	TEXT		Full description
short_description	VARCHAR(1000)		Short description
logo_url	VARCHAR(500)		Logo URL
banner_url	VARCHAR(500)		Banner URL
hero_image_url	VARCHAR(500)		Hero image URL
favicon_url	VARCHAR(500)		Favicon URL
start_date	DATE		Start date
end_date	DATE		End date
timezone	VARCHAR(50)	DEFAULT 'UTC'	Timezone
status	VARCHAR(20)	CHECK, DEFAULT 'draft'	'upcoming', 'ongoing', 'past', 'cancelled', 'draft'
city	VARCHAR(255)		City
country	VARCHAR(255)		Country
venue_name	VARCHAR(255)		Venue name
venue_address	TEXT		Venue address
venue_lat	FLOAT		Venue latitude
venue_lng	FLOAT		Venue longitude
is_virtual	BOOLEAN	DEFAULT FALSE	Virtual conference flag
virtual_platform	VARCHAR(100)		Virtual platform
virtual_url	VARCHAR(500)		Virtual URL
organizer_name	VARCHAR(255)	NOT NULL	Organizer name
organizer_logo	VARCHAR(500)		Organizer logo
organizer_website	VARCHAR(500)		Organizer website
organizer_email	VARCHAR(255)		Organizer email
organizer_phone	VARCHAR(50)		Organizer phone
website_url	VARCHAR(500)		Website URL
twitter_url	VARCHAR(500)		Twitter URL
linkedin_url	VARCHAR(500)		LinkedIn URL
instagram_url	VARCHAR(500)		Instagram URL
facebook_url	VARCHAR(500)		Facebook URL
youtube_url	VARCHAR(500)		YouTube URL
telegram_url	VARCHAR(500)		Telegram URL
discord_url	VARCHAR(500)		Discord URL
registration_url	VARCHAR(500)		Registration URL
ticket_url	VARCHAR(500)		Ticket URL
is_free	BOOLEAN	DEFAULT TRUE	Free conference flag
price_range	VARCHAR(100)		Price range
currency	VARCHAR(10)	DEFAULT 'USD'	Currency
max_attendees	INTEGER		Maximum attendees
current_attendees	INTEGER	DEFAULT 0	Current attendee count
is_featured	BOOLEAN	DEFAULT FALSE	Featured flag
is_public	BOOLEAN	DEFAULT TRUE	Public visibility flag
requires_approval	BOOLEAN	DEFAULT FALSE	Approval required flag
is_active	BOOLEAN	DEFAULT TRUE	Active status
theme_color	VARCHAR(7)		Theme color
secondary_color	VARCHAR(7)		Secondary color
custom_css	TEXT		Custom CSS
custom_js	TEXT		Custom JavaScript
meta_title	VARCHAR(255)		SEO meta title
meta_description	VARCHAR(500)		SEO meta description
meta_keywords	TEXT[]		SEO keywords
og_image	VARCHAR(500)		Open Graph image
google_analytics_id	VARCHAR(50)		Google Analytics ID
facebook_pixel_id	VARCHAR(50)		Facebook Pixel ID
custom_tracking_code	TEXT		Custom tracking code
sponsorship_prospectus_url	VARCHAR(500)		Sponsorship prospectus
sponsor_contact_email	VARCHAR(255)		Sponsor contact email
tags	TEXT[]		Conference tags
categories	TEXT[]		Conference categories
languages	TEXT[]	DEFAULT ARRAY['English']	Supported languages
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Update timestamp
created_by	BIGINT	FK → users(id)	Creator user ID
updated_by	BIGINT	FK → users(id)	Updater user ID

Indexes:

idx_conferences_slug (UNIQUE)
idx_conferences_status
idx_conferences_start_date
idx_conferences_featured
idx_conferences_public
idx_conferences_city
idx_conferences_organizer

Relationships:

One-to-many: events, conference_attendees, conference_speakers, conference_agenda, quests

conference_attendees

Conference attendance tracking.

Column	Type	Constraints	Description
id	SERIAL	PRIMARY KEY	Attendee record ID
user_id	BIGINT	NOT NULL, FK → users(id)	User ID
conference_id	INTEGER	NOT NULL, FK → conferences(id)	Conference ID
registration_date	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Registration date
status	VARCHAR(20)	CHECK, DEFAULT 'registered'	'registered', 'attended', 'cancelled'
email	VARCHAR		Attendee email
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Update timestamp

Constraints:

UNIQUE (user_id, conference_id)
CHECK status IN ('registered', 'attended', 'cancelled')

Indexes:

idx_conference_attendees_user_id
idx_conference_attendees_conference_id
idx_conference_attendees_status
idx_conference_attendees_registration_date

conference_agenda

Conference agenda/schedule items.

Column	Type	Constraints	Description
id	SERIAL	PRIMARY KEY	Agenda item ID
conference_id	INTEGER	FK → conferences(id)	Conference ID
day_date	DATE	NOT NULL	Agenda day
stage_name	VARCHAR(255)	NOT NULL	Stage/room name
time_slot	VARCHAR(100)	NOT NULL	Time slot
event_name	VARCHAR(500)	NOT NULL	Event/session name
speakers_data	TEXT		Semicolon-separated speaker info
speaker_image_url	TEXT		Semicolon-separated speaker image URLs
org_image_url	TEXT		Semicolon-separated org image URLs
display_order	INTEGER	NOT NULL, DEFAULT 0	Display order
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Update timestamp

Indexes:

idx_conference_agenda_conference_id
idx_conference_agenda_day_date
idx_conference_agenda_stage_name
idx_conference_agenda_conference_day_stage
idx_conference_agenda_display_order

speakers

Speaker information and profiles.

Column	Type	Constraints	Description
id	SERIAL	PRIMARY KEY	Speaker ID
first_name	VARCHAR(100)	NOT NULL	First name
last_name	VARCHAR(100)	NOT NULL	Last name
email	VARCHAR(255)	UNIQUE	Email address
phone	VARCHAR(20)		Phone number
bio	TEXT		Biography
title	VARCHAR(200)		Professional title
organization	VARCHAR(200)		Organization
photo_url	VARCHAR(500)		Photo URL
website	VARCHAR(500)		Website URL
linkedin_url	VARCHAR(500)		LinkedIn URL
twitter_url	VARCHAR(500)		Twitter URL
github_url	VARCHAR(500)		GitHub URL
instagram_url	VARCHAR(500)		Instagram URL
facebook_url	VARCHAR(500)		Facebook URL
youtube_url	VARCHAR(500)		YouTube URL
personal_website	VARCHAR(500)		Personal website
location	VARCHAR(200)		Location
country	VARCHAR(100)		Country
city	VARCHAR(100)		City
languages	TEXT[]		Languages spoken
expertise	TEXT[]		Areas of expertise
topics	TEXT[]		Speaking topics
experience	TEXT		Experience description
education	TEXT		Education background
certifications	TEXT[]		Certifications
availability	TEXT		Availability
travel_willing	BOOLEAN	DEFAULT FALSE	Willing to travel
virtual_willing	BOOLEAN	DEFAULT TRUE	Willing for virtual
fee_range	VARCHAR(100)		Fee range
currency	VARCHAR(3)	DEFAULT 'USD'	Currency
is_verified	BOOLEAN	DEFAULT FALSE	Verification status
is_active	BOOLEAN	DEFAULT TRUE	Active status
is_featured	BOOLEAN	DEFAULT FALSE	Featured flag
rating	FLOAT	DEFAULT 0	Average rating
rating_count	INTEGER	DEFAULT 0	Rating count
total_talks	INTEGER	DEFAULT 0	Total talks given
total_events	INTEGER	DEFAULT 0	Total events
last_talk_date	TIMESTAMPTZ		Last talk date
user_id	BIGINT	FK → users(id)	Associated user ID
slug	VARCHAR(255)		URL-friendly identifier
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Update timestamp

Indexes:

idx_speakers_email (UNIQUE)
idx_speakers_is_active
idx_speakers_is_featured
idx_speakers_is_verified
idx_speakers_country
idx_speakers_city
GIN indexes on expertise, topics, languages

Relationships:

Optional many-to-one: users (via user_id)
Many-to-many: events (via event_speakers), conferences (via conference_speakers)

event_speakers

Junction table for event-speaker relationships.

Column	Type	Constraints	Description
id	SERIAL	PRIMARY KEY	Relationship ID
event_id	BIGINT	NOT NULL, FK → events(id)	Event ID
speaker_id	BIGINT	NOT NULL, FK → speakers(id)	Speaker ID
role	VARCHAR(100)		Speaker role (keynote, panelist, etc.)
order	INTEGER	DEFAULT 0	Display order
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Update timestamp

Constraints:

UNIQUE (event_id, speaker_id)

Indexes:

idx_event_speakers_event_id
idx_event_speakers_speaker_id
idx_event_speakers_role

conference_speakers

Junction table for conference-speaker relationships.

Column	Type	Constraints	Description
id	SERIAL	PRIMARY KEY	Relationship ID
conference_id	BIGINT	NOT NULL, FK → conferences(id)	Conference ID
speaker_id	BIGINT	NOT NULL, FK → speakers(id)	Speaker ID
role	VARCHAR(100)		Speaker role
order	INTEGER	DEFAULT 0	Display order
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Update timestamp

Constraints:

UNIQUE (conference_id, speaker_id)

Indexes:

idx_conference_speakers_conference_id
idx_conference_speakers_speaker_id
idx_conference_speakers_role

Quest System Tables

quests

Quest/gamification system.

Column	Type	Constraints	Description
id	VARCHAR(255)	PRIMARY KEY	Quest ID
title	VARCHAR(255)	NOT NULL	Quest title
subtitle	VARCHAR(255)		Quest subtitle
description	TEXT		Quest description
tag	VARCHAR(50)		Quest tag
category	VARCHAR(50)		Quest category
conference_id	INTEGER	FK → conferences(id)	Associated conference
conference_slug	VARCHAR		Conference slug
reward	VARCHAR(255)		Reward description
reward_description	TEXT		Detailed reward description
reward_image	VARCHAR(255)		Reward image URL
points	INT	NOT NULL	Points awarded
points_disabled	BOOLEAN		Points disabled flag
deadline	TIMESTAMP		Quest deadline
sponsor	VARCHAR(255)		Sponsor name
sponsor_logo	VARCHAR(255)		Sponsor logo URL
sponsor_website	VARCHAR(255)		Sponsor website
featured	BOOLEAN	DEFAULT FALSE	Featured flag
is_active	BOOLEAN		Active status
background_color	VARCHAR(50)		Background color
border_color	VARCHAR(50)		Border color
difficulty	VARCHAR(50)		Difficulty level
estimated_completion_time	VARCHAR(50)		Estimated time
quest_type	VARCHAR(50)		Quest type
raffle_draw_date	TIMESTAMP		Raffle draw date
registration_approval_required	BOOLEAN	DEFAULT FALSE	Approval required
approval_form_fields	JSONB		Approval form fields
copies	JSONB		Reward copies/template
kgen_quest	BOOLEAN		KGen quest flag
created_at	TIMESTAMP	NOT NULL, DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMP	NOT NULL, DEFAULT NOW()	Update timestamp

Indexes:

idx_quests_featured
idx_quests_category

Relationships:

Many-to-one: conferences (via conference_id)
One-to-many: quest_tasks, user_quest_tasks, quest_registration_requests, quest_notification_templates

quest_tasks

Tasks within quests.

Column	Type	Constraints	Description
id	VARCHAR(255)	PRIMARY KEY	Task ID
quest_id	VARCHAR(255)	NOT NULL, FK → quests(id)	Quest ID
type	VARCHAR(50)	NOT NULL	Task type
description	VARCHAR(255)	NOT NULL	Task description
detail_description	TEXT		Detailed description
points	INT	NOT NULL	Points for completion
action_type	VARCHAR(50)		Action type
action_url	TEXT		Action URL
verification_type	VARCHAR(50)		Verification method
icon	VARCHAR(50)		Icon identifier
repeatable	BOOLEAN	DEFAULT FALSE	Repeatable flag
repeat_frequency	VARCHAR(50)		Repeat frequency
start_date	TIMESTAMP		Start date
end_date	TIMESTAMP		End date
is_mandatory	BOOLEAN	DEFAULT FALSE	Mandatory flag
time_estimate	VARCHAR(50)		Time estimate
platform	VARCHAR(50)		Platform
required_hashtags	JSONB		Required hashtags
suggested_text	TEXT		Suggested text
tips	TEXT		Tips
example	TEXT		Example
unlock_condition	JSONB		Unlock conditions
metadata	JSONB		Flexible metadata (quiz, poll, etc.)
proof_required	BOOLEAN		Proof required flag
created_at	TIMESTAMP	NOT NULL, DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMP	NOT NULL, DEFAULT NOW()	Update timestamp

Indexes:

idx_quest_tasks_quest_id

Relationships:

Many-to-one: quests (via quest_id)
One-to-many: user_quest_tasks

user_quest_tasks

User progress on quest tasks.

Column	Type	Constraints	Description
id	VARCHAR(255)	PRIMARY KEY	Progress record ID
user_id	BIGINT	NOT NULL, FK → users(id)	User ID
quest_id	VARCHAR(255)	NOT NULL, FK → quests(id)	Quest ID
quest_task_id	VARCHAR(255)	NOT NULL, FK → quest_tasks(id)	Task ID
completed	BOOLEAN	DEFAULT FALSE	Completion status
completed_at	TIMESTAMP		Completion timestamp
verified_at	TIMESTAMP		Verification timestamp
points_earned	INT	DEFAULT 0	Points earned
proof	TEXT		Proof of completion
metadata	JSONB		Approval/rejection metadata
created_at	TIMESTAMP	NOT NULL, DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMP	NOT NULL, DEFAULT NOW()	Update timestamp

Constraints:

UNIQUE (user_id, quest_task_id)

Indexes:

idx_user_quest_tasks_user_id
idx_user_quest_tasks_quest_id
idx_user_quest_tasks_completed
idx_user_quest_tasks_completed_at

quest_registration_requests

Quest registration approval requests.

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY	Request ID
user_id	BIGINT	NOT NULL, FK → users(id)	User ID
quest_id	VARCHAR(255)	NOT NULL, FK → quests(id)	Quest ID
status	VARCHAR(20)	DEFAULT 'pending'	'pending', 'approved', 'rejected'
form_data	JSONB		Form submission data
admin_notes	TEXT		Admin notes
message	TEXT		Message to participant
reward	JSONB		Reward assigned to user
processed_by	BIGINT	FK → users(id)	Admin who processed
processed_at	TIMESTAMP		Processing timestamp
created_at	TIMESTAMP	DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMP	DEFAULT NOW()	Update timestamp

Constraints:

UNIQUE (user_id, quest_id)

Indexes:

idx_quest_registration_requests_user_id
idx_quest_registration_requests_quest_id
idx_quest_registration_requests_status
idx_quest_registration_requests_created_at

quest_notification_templates

Quest reminder notification templates.

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY	Template ID
quest_id	VARCHAR(255)	NOT NULL, FK → quests(id)	Quest ID
template_name	VARCHAR(100)	NOT NULL, DEFAULT 'default'	Template name
is_default	BOOLEAN	DEFAULT FALSE	Default template flag
status_payloads	JSONB	NOT NULL	Notification payloads per stage
created_by	BIGINT	FK → users(id)	Creator user ID
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Update timestamp
updated_by	BIGINT	FK → users(id)	Updater user ID

Constraints:

UNIQUE (quest_id, template_name)

Indexes:

idx_quest_notification_templates_quest_id
idx_quest_notification_templates_default

Notification System Tables

user_notifications

User notifications.

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY	Notification ID
user_id	BIGINT	NOT NULL, FK → users(id)	User ID
type	VARCHAR(50)	NOT NULL	Notification type
title	VARCHAR(255)	NOT NULL	Notification title
message	TEXT	NOT NULL	Notification message
is_clicked	BOOLEAN	DEFAULT FALSE	Clicked status
is_dismissed	BOOLEAN	DEFAULT FALSE	Dismissed status
action_data	JSONB		Action data
created_at	TIMESTAMP	DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMP	DEFAULT NOW()	Update timestamp

Indexes:

idx_user_notifications_user_id
idx_user_notifications_type
idx_user_notifications_created_at
idx_user_notifications_is_clicked
idx_user_notifications_is_dismissed

notification_jobs

Bulk notification job tracking.

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY	Job ID
created_by	BIGINT	FK → users(id)	Creator user ID
selection_criteria	JSONB	NOT NULL	User selection criteria
notification_payload	JSONB	NOT NULL	Notification payload
status	VARCHAR(20)	NOT NULL, DEFAULT 'pending', CHECK	'pending', 'processing', 'completed', 'failed'
total_users	INTEGER	NOT NULL, DEFAULT 0	Total eligible users
attempted_count	INTEGER	NOT NULL, DEFAULT 0	Attempted count
success_count	INTEGER	NOT NULL, DEFAULT 0	Success count
failure_count	INTEGER	NOT NULL, DEFAULT 0	Failure count
error_message	TEXT		Error message
scheduled_at	TIMESTAMPTZ		Scheduled time
started_at	TIMESTAMPTZ		Start timestamp
completed_at	TIMESTAMPTZ		Completion timestamp
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Update timestamp

Indexes:

idx_notification_jobs_status
idx_notification_jobs_created_at
idx_notification_jobs_created_by

Relationships:

One-to-many: notification_job_items

notification_job_items

Individual notification attempts within a job.

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY	Item ID
job_id	UUID	NOT NULL, FK → notification_jobs(id)	Job ID
user_id	BIGINT	NOT NULL, FK → users(id)	User ID
telegram_id	BIGINT	NOT NULL	Telegram ID
status	VARCHAR(20)	NOT NULL, DEFAULT 'pending', CHECK	'pending', 'processing', 'success', 'failed'
error_message	TEXT		Error message
sent_at	TIMESTAMPTZ		Sent timestamp
template_variables	JSONB		Template variables used
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Update timestamp

Constraints:

UNIQUE (job_id, user_id)

Indexes:

idx_notification_job_items_job_id
idx_notification_job_items_user_id
idx_notification_job_items_status

Enrichment Tables

linkedin_meta

LinkedIn profile metadata.

Column	Type	Constraints	Description
id	BIGSERIAL	PRIMARY KEY	Record ID
user_id	BIGINT	NOT NULL, UNIQUE, FK → users(id)	User ID
profile_data	JSONB	NOT NULL, DEFAULT ''	LinkedIn profile data
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Update timestamp

Relationships:

One-to-one: users

twitter_meta

Twitter profile metadata.

Column	Type	Constraints	Description
id	BIGSERIAL	PRIMARY KEY	Record ID
user_id	BIGINT	NOT NULL, UNIQUE, FK → users(id)	User ID
profile_data	JSONB	NOT NULL, DEFAULT ''	Twitter profile data
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Update timestamp

Relationships:

One-to-one: users

twitter_tweets

User tweets.

Column	Type	Constraints	Description
id	BIGSERIAL	PRIMARY KEY	Tweet ID
user_id	BIGINT	NOT NULL, FK → users(id)	User ID
username	VARCHAR		Twitter username
tweet_data	JSONB		Tweet data
created_at	TIMESTAMP		Tweet timestamp

Relationships:

Many-to-one: users

twitter_enrichments

AI-generated Twitter enrichments.

Column	Type	Constraints	Description
id	BIGSERIAL	PRIMARY KEY	Enrichment ID
user_id	BIGINT	NOT NULL, UNIQUE, FK → users(id)	User ID
topic_expertise	JSONB	NOT NULL, DEFAULT ''	Topic expertise
project_affiliations	JSONB	NOT NULL, DEFAULT ''	Project affiliations
crypto_role	VARCHAR(255)		Crypto role
key_stances	JSONB	NOT NULL, DEFAULT ''	Key stances
community_signals	JSONB	NOT NULL, DEFAULT ''	Community signals
activity_profile	JSONB	NOT NULL, DEFAULT ''	Activity profile
tags	JSONB	NOT NULL, DEFAULT ''	Tags
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Update timestamp

Relationships:

One-to-one: users

crypto_enrichments

Combined crypto enrichments from multiple sources.

Column	Type	Constraints	Description
id	BIGSERIAL	PRIMARY KEY	Enrichment ID
user_id	BIGINT	NOT NULL, UNIQUE, FK → users(id)	User ID
primary_chains	JSONB	NOT NULL, DEFAULT ''	Primary chains
expertise_areas	JSONB	NOT NULL, DEFAULT ''	Expertise areas
networking_pitch	TEXT		Networking pitch
collab_interests	JSONB	NOT NULL, DEFAULT ''	Collaboration interests
profile_type	VARCHAR(255)		Profile type
sources_used	JSONB	NOT NULL, DEFAULT ''	Sources used
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Update timestamp

Relationships:

One-to-one: users

Utility Tables

rate_limits

API rate limiting tracking.

Column	Type	Constraints	Description
id	BIGSERIAL	PRIMARY KEY	Record ID
user_id	BIGINT	NOT NULL, FK → users(id)	User ID
endpoint	VARCHAR(255)	NOT NULL	Endpoint path
request_count	INTEGER	NOT NULL, DEFAULT 1	Request count
window_start	TIMESTAMPTZ	NOT NULL	Rate limit window start
created_at	TIMESTAMPTZ	DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	DEFAULT NOW()	Update timestamp

Indexes:

idx_rate_limits_user_endpoint_window
idx_rate_limits_window_start

feedback

User feedback submissions.

Column	Type	Constraints	Description
id	SERIAL	PRIMARY KEY	Feedback ID
user_id	BIGINT	NOT NULL, FK → users(id)	User ID
feedback	TEXT	NOT NULL	Feedback text
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Creation timestamp

Indexes:

idx_feedback_user_id
idx_feedback_created_at

Summary Statistics

Total Tables: 25+
Core Tables: 5 (users, events, conferences, quests, speakers)
Junction Tables: 5 (connections, event_attendees, conference_attendees, event_speakers, conference_speakers)
Enrichment Tables: 4 (linkedin_meta, twitter_meta, twitter_enrichments, crypto_enrichments)
Quest System Tables: 4 (quests, quest_tasks, user_quest_tasks, quest_registration_requests)
Notification Tables: 3 (user_notifications, notification_jobs, notification_job_items)
Utility Tables: 2 (rate_limits, feedback)
Total Indexes: 100+
Foreign Key Constraints: 30+

Entity Relationships IBW Admin Dashboard - Overview