Database Schema

Database Schema

This page documents LinkUp's database structure, relationships, and data models.

Overview

LinkUp uses PostgreSQL with SQLAlchemy async ORM. The schema is designed for simplicity while maintaining audit trails and meeting history.

Entity-Relationship Diagram

┌─────────────────────┐
│      Meeting        │
│─────────────────────│
│ id (PK)             │
│ code (UNIQUE)       │
│ title               │
│ created_at          │
│ is_active           │
│ max_participants    │
│ settings (JSON)     │
└─────────────────────┘
          │
          │ 1:N
          ▼
┌─────────────────────┐       ┌─────────────────────┐
│    Participant      │       │    MeetingLog       │
│─────────────────────│       │─────────────────────│
│ id (PK)             │◄──┐   │ id (PK)             │
│ meeting_id (FK)     │   │   │ meeting_id (FK)     │
│ client_id           │   │   │ participant_id (FK) │
│ display_name        │   │   │ event_type          │
│ audio_enabled       │   └───│ event_data (JSON)   │
│ video_enabled       │       │ timestamp           │
│ screen_sharing      │       └─────────────────────┘
│ is_host             │
│ is_active           │
│ ip_address          │
│ joined_at           │
│ left_at             │
└─────────────────────┘

Tables

Meeting Table

Stores information about meeting rooms.

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY	Unique meeting identifier
code	VARCHAR(10)	UNIQUE, NOT NULL	10-character meeting code
title	VARCHAR(255)	NULL	Optional meeting title
created_at	TIMESTAMP	NOT NULL	When meeting was created
is_active	BOOLEAN	DEFAULT true	Whether meeting is active
max_participants	INTEGER	DEFAULT 10	Maximum allowed participants
settings	JSON	NULL	Additional meeting settings

Indexes:

• Primary key on id
• Unique index on code
• Index on is_active for filtering active meetings

Sample Data:

INSERT INTO meeting (id, code, title, created_at, is_active, max_participants)
VALUES (
  '550e8400-e29b-41d4-a716-446655440000',
  'abc123xyz9',
  'Team Standup',
  '2025-11-09 12:00:00',
  true,
  10
);

---

Participant Table

Stores information about users who join meetings.

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY	Unique participant identifier
meeting_id	UUID	FOREIGN KEY → meeting(id)	Meeting this participant joined
client_id	VARCHAR(255)	NOT NULL	WebSocket client identifier
display_name	VARCHAR(100)	NOT NULL	User's display name
audio_enabled	BOOLEAN	DEFAULT true	Microphone on/off
video_enabled	BOOLEAN	DEFAULT true	Camera on/off
screen_sharing	BOOLEAN	DEFAULT false	Currently sharing screen
is_host	BOOLEAN	DEFAULT false	First participant becomes host
is_active	BOOLEAN	DEFAULT true	Whether still in meeting
ip_address	VARCHAR(45)	NULL	Participant's IP address
joined_at	TIMESTAMP	NOT NULL	When they joined
left_at	TIMESTAMP	NULL	When they left (NULL if still active)

Indexes:

• Primary key on id
• Foreign key index on meeting_id
• Index on client_id for fast lookups
• Composite index on (meeting_id, is_active) for active participant queries

Sample Data:

INSERT INTO participant (
  id, meeting_id, client_id, display_name, 
  is_host, joined_at, is_active
)
VALUES (
  '660e8400-e29b-41d4-a716-446655440001',
  '550e8400-e29b-41d4-a716-446655440000',
  'client-abc123',
  'John Doe',
  true,
  '2025-11-09 12:00:00',
  true
);

---

MeetingLog Table

Audit trail of all events during meetings.

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY	Unique log entry identifier
meeting_id	UUID	FOREIGN KEY → meeting(id)	Meeting where event occurred
participant_id	UUID	FOREIGN KEY → participant(id), NULL	Participant who triggered event
event_type	VARCHAR(50)	NOT NULL	Type of event
event_data	JSON	NULL	Additional event details
timestamp	TIMESTAMP	NOT NULL	When event occurred

Event Types:

• join - Participant joined meeting
• leave - Participant left meeting
• audio_toggle - Microphone toggled
• video_toggle - Camera toggled
• screen_share_start - Screen sharing started
• screen_share_stop - Screen sharing stopped
• chat_message - Chat message sent (optional logging)

Indexes:

• Primary key on id
• Foreign key index on meeting_id
• Foreign key index on participant_id
• Index on timestamp for chronological queries
• Composite index on (meeting_id, timestamp) for meeting timelines

Sample Data:

INSERT INTO meeting_log (
  id, meeting_id, participant_id, 
  event_type, event_data, timestamp
)
VALUES (
  '770e8400-e29b-41d4-a716-446655440002',
  '550e8400-e29b-41d4-a716-446655440000',
  '660e8400-e29b-41d4-a716-446655440001',
  'join',
  '{"display_name": "John Doe", "is_host": true}',
  '2025-11-09 12:00:00'
);

---

Relationships

One-to-Many: Meeting → Participants

• One meeting can have multiple participants
• Cascade delete: When meeting is deleted, all participants are deleted
• Foreign key: participant.meeting_id → meeting.id

# SQLAlchemy relationship
class Meeting(Base):
    participants = relationship(
        "Participant",
        back_populates="meeting",
        cascade="all, delete-orphan"
    )

class Participant(Base):
    meeting = relationship("Meeting", back_populates="participants")

One-to-Many: Meeting → MeetingLogs

• One meeting can have many log entries
• Cascade delete: When meeting is deleted, all logs are deleted
• Foreign key: meeting_log.meeting_id → meeting.id

class Meeting(Base):
    logs = relationship(
        "MeetingLog",
        back_populates="meeting",
        cascade="all, delete-orphan"
    )

class MeetingLog(Base):
    meeting = relationship("Meeting", back_populates="logs")

One-to-Many: Participant → MeetingLogs

• One participant can have many log entries
• Nullable: Logs can exist without participant (system events)
• Foreign key: meeting_log.participant_id → participant.id

class Participant(Base):
    logs = relationship(
        "MeetingLog",
        back_populates="participant"
    )

class MeetingLog(Base):
    participant = relationship("Participant", back_populates="logs")

---

Common Queries

Get Active Meetings

SELECT * FROM meeting
WHERE is_active = true
ORDER BY created_at DESC;

Get Meeting with Participant Count

SELECT 
    m.*,
    COUNT(p.id) as participant_count
FROM meeting m
LEFT JOIN participant p ON m.id = p.meeting_id AND p.is_active = true
WHERE m.code = 'abc123xyz9'
GROUP BY m.id;

Get Active Participants in Meeting

SELECT * FROM participant
WHERE meeting_id = '550e8400-e29b-41d4-a716-446655440000'
  AND is_active = true
ORDER BY joined_at ASC;

Get Meeting Timeline (Audit Trail)

SELECT 
    ml.timestamp,
    ml.event_type,
    p.display_name,
    ml.event_data
FROM meeting_log ml
LEFT JOIN participant p ON ml.participant_id = p.id
WHERE ml.meeting_id = '550e8400-e29b-41d4-a716-446655440000'
ORDER BY ml.timestamp ASC;

Find Meetings by Participant

SELECT DISTINCT m.*
FROM meeting m
JOIN participant p ON m.id = p.meeting_id
WHERE p.display_name ILIKE '%John%'
ORDER BY m.created_at DESC;

---

Database Migrations

LinkUp uses Alembic for database migrations.

Creating a Migration

# After modifying models.py
cd backend
alembic revision --autogenerate -m "Description of changes"

Applying Migrations

# Upgrade to latest
alembic upgrade head

# Upgrade one version
alembic upgrade +1

# Downgrade one version
alembic downgrade -1

# Downgrade to base
alembic downgrade base

Migration History

# View migration history
alembic history

# Show current version
alembic current

---

Data Retention

Current Policy

• Active meetings: Kept indefinitely while active
• Inactive meetings: Kept indefinitely (for audit)
• Participants: Kept with meeting data
• Logs: Kept with meeting data
• Chat messages: In-memory only, cleared when meeting ends

Recommended Production Policy

Consider implementing:

• Auto-archive meetings after 30 days of inactivity
• Delete meetings older than 90 days
• Anonymize participant data after 1 year
• Purge logs older than 1 year

Example cleanup query:

-- Delete inactive meetings older than 90 days
DELETE FROM meeting
WHERE is_active = false
  AND created_at < NOW() - INTERVAL '90 days';

---

Performance Optimization

Recommended Indexes

Already implemented:

• Primary keys (automatic)
• Foreign keys (automatic)
• Unique constraints (meeting.code)
• is_active indexes

Consider adding for large deployments:

-- For finding meetings by creation date range
CREATE INDEX idx_meeting_created_at ON meeting(created_at DESC);

-- For participant queries
CREATE INDEX idx_participant_composite ON participant(meeting_id, is_active, joined_at);

-- For log queries
CREATE INDEX idx_log_composite ON meeting_log(meeting_id, timestamp DESC);

Query Optimization Tips

1. Always filter by is_active when querying current state
2. Use LEFT JOIN for optional relationships
3. Limit results with LIMIT clause for pagination
4. Use indexes for WHERE, JOIN, and ORDER BY clauses
5. Batch inserts for logs to reduce round-trips

---

Backup and Recovery

Backup Strategy

# Full database backup
pg_dump -U username linkup > backup_$(date +%Y%m%d_%H%M%S).sql

# Schema only
pg_dump -U username --schema-only linkup > schema.sql

# Data only
pg_dump -U username --data-only linkup > data.sql

Restore Database

# Restore full backup
psql -U username linkup < backup_20251109_120000.sql

# Restore to new database
createdb linkup_restore
psql -U username linkup_restore < backup_20251109_120000.sql

---

Next: Learn about WebRTC Implementation for the real-time video architecture.