Skip to main content

Database Architecture

Database design and data storage strategy.

Database Strategy​

Bellamy Book uses a polyglot persistence approach:

  • PostgreSQL: Relational data (users, posts, comments, reactions, friendships, follows, hashtags, blogs, tickets, etc.)
  • MongoDB: Document store (stories, notifications, chat messages)
  • Redis: Cache, session storage, rate limiting, real-time state
  • Neo4j: Social graph (friend suggestions, recommendations) — used by GraphWorker and ScoringWorker
  • Elasticsearch/OpenSearch: Full-text search (posts, blogs) — synced by ElasticsearchSyncWorker

PostgreSQL Schema​

Core Tables​

Users​

CREATE TABLE users (
    id UUID PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    profile_picture_url VARCHAR(500),
    cover_photo_url VARCHAR(500),
    bio TEXT,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

Posts​

CREATE TABLE posts (
    id UUID PRIMARY KEY,
    author_id UUID NOT NULL REFERENCES users(id),
    content TEXT NOT NULL,
    privacy VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    INDEX idx_author_created (author_id, created_at DESC)
);

Comments​

CREATE TABLE comments (
    id UUID PRIMARY KEY,
    post_id UUID NOT NULL REFERENCES posts(id),
    author_id UUID NOT NULL REFERENCES users(id),
    content TEXT NOT NULL,
    parent_comment_id UUID REFERENCES comments(id),
    created_at TIMESTAMP NOT NULL,
    INDEX idx_post_created (post_id, created_at DESC)
);

Reactions​

CREATE TABLE reactions (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES users(id),
    target_id UUID NOT NULL,
    target_type VARCHAR(20) NOT NULL,
    type VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    UNIQUE(user_id, target_id, target_type),
    INDEX idx_target (target_id, target_type)
);

MongoDB Collections​

Stories​

{
  _id: ObjectId,
  userId: String,
  mediaUrl: String,
  type: String, // "image" or "video"
  createdAt: Date,
  expiresAt: Date,
  viewers: [{
    userId: String,
    viewedAt: Date
  }]
}

Notifications​

{
  _id: ObjectId,
  userId: String,
  type: String, // "friend_request", "post_reaction", etc.
  title: String,
  message: String,
  data: Object,
  read: Boolean,
  createdAt: Date
}

Messages​

{
  _id: ObjectId,
  conversationId: String,
  senderId: String,
  content: String,
  type: String, // "text", "image", "video"
  read: Boolean,
  createdAt: Date
}

Redis Usage​

Cache Keys​

user:{userId}                    # User profile cache
post:{postId}                    # Post cache
feed:{userId}:{page}             # User feed cache
session:{sessionId}              # Session data
rate_limit:{userId}:{endpoint}   # Rate limiting

Cache Strategy​

  • User profiles: 30 minutes TTL
  • Posts: 10 minutes TTL
  • Feeds: 5 minutes TTL
  • Sessions: 24 hours TTL

Data Relationships​

User Relationships​

User 1───N FriendRequest
User 1───N Post
User 1───N Comment
User 1───N Reaction

Post Relationships​

Post 1───N Comment
Post 1───N Reaction
Post 1───N Media

Indexing Strategy​

PostgreSQL Indexes​

-- User lookups
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);

-- Post queries
CREATE INDEX idx_posts_author_created ON posts(author_id, created_at DESC);
CREATE INDEX idx_posts_privacy_created ON posts(privacy, created_at DESC);

-- Comment queries
CREATE INDEX idx_comments_post_created ON comments(post_id, created_at DESC);
CREATE INDEX idx_comments_parent ON comments(parent_comment_id);

-- Reaction queries
CREATE INDEX idx_reactions_target ON reactions(target_id, target_type);
CREATE INDEX idx_reactions_user ON reactions(user_id);

MongoDB Indexes​

// Stories
db.stories.createIndex({ userId: 1, createdAt: -1 });
db.stories.createIndex({ expiresAt: 1 }, { expireAfterSeconds: 0 });

// Notifications
db.notifications.createIndex({ userId: 1, createdAt: -1 });
db.notifications.createIndex({ userId: 1, read: 1 });

// Messages
db.messages.createIndex({ conversationId: 1, createdAt: -1 });
db.messages.createIndex({ senderId: 1, createdAt: -1 });

Data Migration​

Entity Framework Migrations​

# Create migration
dotnet ef migrations add MigrationName --project DatabaseApplication

# Apply migration
dotnet ef database update --project DatabaseApplication

Backup Strategy​

  • PostgreSQL: Daily pg_dump backups
  • MongoDB: Daily mongodump backups
  • Redis: RDB snapshots + AOF persistence

Next Steps​