Day 1 : Social Media Data Modeling

# Clone or create project directory
mkdir twitter-data-modeling && cd twitter-data-modeling

# Verify Node.js installation (v16+)
node --version

# Verify PostgreSQL installation
psql --version

# Start PostgreSQL service
sudo systemctl start postgresql  # Linux
brew services start postgresql   # macOS

# Create database and user
sudo -u postgres psql
CREATE DATABASE twitter_db;
CREATE USER twitter_user WITH PASSWORD 'twitter_pass';
GRANT ALL PRIVILEGES ON DATABASE twitter_db TO twitter_user;
q

CREATE TYPE privacy_level AS ENUM ('public', 'protected', 'private');

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    display_name VARCHAR(100),
    bio TEXT,
    privacy privacy_level DEFAULT 'public',
    follower_count INTEGER DEFAULT 0,
    following_count INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE tweets (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    content TEXT NOT NULL CHECK (LENGTH(content) <= 280),
    reply_to_tweet_id BIGINT REFERENCES tweets(id),
    original_tweet_id BIGINT REFERENCES tweets(id),
    hashtags TEXT[],
    like_count INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Primary relationship table
CREATE TABLE user_follows (
    follower_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    following_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    PRIMARY KEY (follower_id, following_id)
);

-- Reverse lookup optimization
CREATE TABLE user_followers (
    user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    follower_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    PRIMARY KEY (user_id, follower_id)
);

-- Timeline generation (most critical)
CREATE INDEX idx_tweets_user_created ON tweets(user_id, created_at DESC);

-- Relationship queries
CREATE INDEX idx_follows_follower ON user_follows(follower_id);
CREATE INDEX idx_followers_user ON user_followers(user_id);

-- Search optimization
CREATE INDEX idx_tweets_hashtags ON tweets USING GIN(hashtags);
CREATE INDEX idx_users_username ON users(username);

EXPLAIN ANALYZE SELECT * FROM tweets WHERE user_id = 1 ORDER BY created_at DESC LIMIT 20;

cd backend
npm init -y
npm install express pg cors helmet typescript ts-node-dev @types/express @types/pg

// Key method: Atomic follow operation
static async follow(followerId: number, followingId: number) {
  await query('BEGIN');
  try {
    // Insert into both tables atomically
    await query('INSERT INTO user_follows VALUES ($1, $2)', [followerId, followingId]);
    await query('INSERT INTO user_followers VALUES ($1, $2)', [followingId, followerId]);
    
    // Update counters
    await query('UPDATE users SET following_count = following_count + 1 WHERE id = $1', [followerId]);
    await query('UPDATE users SET follower_count = follower_count + 1 WHERE id = $1', [followingId]);
    
    await query('COMMIT');
  } catch (error) {
    await query('ROLLBACK');
    throw error;
  }
}

npm run dev

# Create user
curl -X POST http://localhost:3001/api/users 
  -H "Content-Type: application/json" 
  -d '{"username":"alice","email":"alice@test.com"}'

# Expected: User created with ID, <50ms response time

cd frontend
npm install react react-dom @types/react @types/react-dom typescript webpack

npm start

const testTimelineQuery = async (userId: number) => {
  const start = performance.now();
  
  const result = await query(`
    SELECT t.*, u.username 
    FROM tweets t 
    INNER JOIN users u ON t.user_id = u.id 
    INNER JOIN user_follows uf ON t.user_id = uf.following_id 
    WHERE uf.follower_id = $1 
    ORDER BY t.created_at DESC 
    LIMIT 20
  `, [userId]);
  
  const duration = performance.now() - start;
  console.log(`Timeline query: ${duration.toFixed(2)}ms`);
  return duration;
};

# Test atomic follow operation
time curl -X POST http://localhost:3001/api/users/2/follow 
  -H "Content-Type: application/json" 
  -d '{"followerId":1}'

# Start PostgreSQL
sudo systemctl start postgresql

# Backend
cd backend && npm run build && npm run dev

# Frontend  
cd frontend && npm start

# Run tests
npm test

# Build and start all services
docker-compose up --build

# Run performance tests
docker exec -it backend npm run test:performance

# Check database status
docker exec -it postgres psql -U twitter_user -d twitter_db -c "dt"