Apply your knowledge to build something amazing!
What you'll do: Open the project and familiarize yourself with the interface
DO NOT DELETE the existing files in the template:
ONLY EDIT the necessary files.
A real-time collaborative platform for study group management that handles scheduling conflicts, tracks attendance, and optimizes resource allocation. You'll research and architect database solutions for complex collaboration challenges, analyzing how platforms like Discord, Slack, and Notion solve similar problems.
Before starting, ensure you can:
Don't worry if you're not 100% confident - this project will strengthen these skills through research and experimentation!
Study groups require complex relationship modeling:
Students (Many) ←→ (Many) Groups
Groups (Many) ←→ (Many) Sessions
Students (Many) ←→ (Many) Sessions
Sessions (Many) ←→ (Many) Resources
-- Junction table for many-to-many relationships
CREATE TABLE student_groups (
student_id INTEGER REFERENCES students(student_id),
group_id INTEGER REFERENCES groups(group_id),
joined_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
role TEXT CHECK(role IN ('owner', 'moderator', 'member')),
PRIMARY KEY (student_id, group_id)
);
-- Temporal overlap detection
SELECT s1.group_name, s2.group_name, s1.session_time
FROM sessions s1
JOIN sessions s2 ON s1.room_id = s2.room_id
AND s1.session_date = s2.session_date
AND s1.session_id != s2.session_id
WHERE (s1.start_time, s1.end_time) OVERLAPS
(s2.start_time, s2.end_time);
-- Complex aggregation with window functions
SELECT
group_id,
group_name,
COUNT(DISTINCT student_id) as member_count,
AVG(attendance_rate) OVER (PARTITION BY subject) as subject_avg_attendance,
RANK() OVER (ORDER BY attendance_rate DESC) as attendance_rank
FROM group_statistics
WHERE active = true
GROUP BY group_id, group_name, subject, attendance_rate;
Design a schema that handles:
Write queries to detect:
Research and benchmark:
⭐⭐⭐⭐☆ (4/5 stars) - Advanced architectural thinking required
Group Management
Scheduling System
Real-time Collaboration
Resource Management
Your system must:
Start by researching how existing platforms handle collaboration:
-- Discovery Challenge: Message Ordering in Distributed Systems
-- Research Question: How do you ensure message order when 100+ users
-- are simultaneously chatting across different servers?
-- Option 1: Database-generated timestamps
-- TODO: Research the clock skew problem
-- What happens when server clocks aren't synchronized?
-- Option 2: Vector clocks
-- TODO: Implement Lamport timestamps
-- How do distributed systems maintain causality?
-- Option 3: Hybrid logical clocks
-- TODO: Research HLC implementation
-- Can you combine physical and logical time?
-- Your task: Implement and benchmark each approach
CREATE TABLE message_ordering_experiment (
-- Design your schema based on research findings
-- Document why you chose specific approaches
);
Research Prompts:
Discovery Tasks:
// Real-time Presence System Research
// Challenge: Track 1000+ users without overwhelming the database
// Research areas:
// 1. Should presence live in PostgreSQL or Redis?
// 2. What's the optimal heartbeat frequency?
// 3. How to handle "zombie" connections?
// 4. When to use database vs in-memory storage?
// TODO: Build prototypes for each approach
// TODO: Measure database load under different strategies
// TODO: Document trade-offs and scaling limits
Investigate complex scheduling challenges:
-- Discovery Challenge: Timezone-Aware Recurring Events
-- Research problem: A weekly study group with members in
-- New York, London, and Tokyo during DST transitions
-- Research questions to explore:
-- 1. Store in UTC or user's local time?
-- 2. How do calendar apps handle DST transitions?
-- 3. What about recurring events crossing DST boundaries?
-- TODO: Research temporal database patterns
-- - Bitemporal data modeling
-- - Event sourcing for schedule changes
-- - Interval trees for overlap detection
-- Experiment with different approaches:
CREATE TABLE scheduling_approaches (
approach_name TEXT,
-- Your experimental schema here
performance_metrics JSONB,
trade_offs TEXT
);
-- Benchmark: Find common availability for 200 users
-- Constraint: Query must complete in < 500ms
Performance Investigation Tasks:
Research version control and collaborative editing:
-- Discovery Challenge: Google Docs-Style Collaboration
-- How do you handle 50 users editing the same document?
-- Research Operational Transforms (OT) vs CRDTs
-- TODO: Implement a simple OT algorithm
-- TODO: Build a CRDT-based solution
-- TODO: Compare consistency guarantees
CREATE TABLE collaboration_experiments (
-- Design tables for:
-- 1. Operation storage (inserts, deletes, formatting)
-- 2. Version history with efficient reconstruction
-- 3. Conflict resolution metadata
-- 4. User cursor positions and selections
);
-- Performance challenge: Reconstruct any version in < 100ms
-- Storage challenge: Store 10,000 edits efficiently
Research Areas:
Build scalable analytics for learning insights:
-- Discovery Challenge: Real-time Analytics at Scale
-- Track every user action for behavioral analysis
-- Research time-series databases
-- TODO: Compare PostgreSQL vs InfluxDB vs Timescale
-- TODO: Design partitioning strategy for events
-- TODO: Implement roll-up aggregations
-- Analytics queries to optimize:
-- 1. "Most active study groups this week"
-- 2. "Correlation between attendance and performance"
-- 3. "Optimal session times by subject"
-- 4. "Resource utilization heat maps"
CREATE TABLE analytics_architecture (
-- Design for 10M+ events per day
-- Consider: partitioning, indexing, aggregation
);
Prepare for production scale:
-- Performance Challenge: 10,000 Concurrent Users
-- TODO: Load test with realistic usage patterns
-- TODO: Identify and resolve bottlenecks
-- TODO: Implement caching strategies
-- TODO: Design for graceful degradation
-- Research areas:
-- 1. Connection pooling strategies
-- 2. Read replica configurations
-- 3. Cache invalidation patterns
-- 4. Circuit breaker implementations
Once your core system works, consider these enhancements:
Before submitting, verify:
GitHub Repository with:
Live Demo including:
Research Portfolio containing:
| Criteria | Weight | Focus |
|---|---|---|
| Research Depth | 30% | Thorough investigation of patterns |
| Performance | 25% | Measurable optimizations |
| Architecture | 20% | Scalable design decisions |
| Innovation | 15% | Creative problem-solving |
| Documentation | 10% | Clear technical writing |
Ready to research? Start with Phase 1 and discover how real-world platforms handle collaborative challenges. Remember: the goal isn't just to build features - it's to understand WHY certain architectural patterns exist and WHEN to apply them!
🚀 Begin by researching three collaboration platforms and documenting their database patterns. The insights you gain will guide your entire implementation!