Database Schema

Overview

Jefftube uses PostgreSQL 16 with Drizzle ORM for type-safe database operations. The schema is defined in server/src/db/schema.ts and consists of five main tables:

videos - Video metadata and statistics
users - User accounts (IP-based)
comments - User comments on videos
commentLikes - Likes/dislikes on comments
videoLikes - Likes/dislikes on videos

Entity Relationship Diagram

Table Schemas

videos

Stores video metadata and aggregated statistics.

Schema
Indexes
TypeScript Type
Relations

Defined in schema.ts:4-16:

Column	Type	Constraints	Description
`id`	`varchar(50)`	PRIMARY KEY	Filename without extension (e.g., “EFTA01683563”)
`title`	`varchar(255)`	nullable	Video title
`filename`	`varchar(255)`	NOT NULL, UNIQUE	Original filename with extension
`length`	`integer`	NOT NULL	Video duration in seconds
`hasThumbnail`	`boolean`	NOT NULL, DEFAULT false	Whether a thumbnail exists
`views`	`integer`	NOT NULL, DEFAULT 0	Total view count
`likes`	`integer`	NOT NULL, DEFAULT 0	Net likes (likes - dislikes)
`is_shorts`	`boolean`	NOT NULL, DEFAULT false	Whether video appears in Shorts feed
`playlist`	`varchar(100)`	nullable	Playlist identifier (e.g., “elevator-cam”)

CREATE INDEX videos_views_idx ON videos(views);

Purpose: Optimize sorting by popularity (most viewed videos)

From schema.ts:110-111:

export type Video = typeof videos.$inferSelect;
export type NewVideo = typeof videos.$inferInsert;

Example usage:

const video: Video = {
  id: "EFTA01683563",
  title: "Elevator Camera Footage",
  filename: "EFTA01683563.mp4",
  length: 3545, // 59:05 in seconds
  hasThumbnail: true,
  views: 15234,
  likes: 42,
  is_shorts: false,
  playlist: "elevator-cam"
};

From schema.ts:58-61:

export const videosRelations = relations(videos, ({ many }) => ({
  comments: many(comments),
  likes: many(videoLikes),
}));

A video can have:

Many comments
Many likes/dislikes

users

Stores user accounts created anonymously via IP address hashing.

Schema
Privacy
TypeScript Type
Relations

Defined in schema.ts:18-23:

Column	Type	Constraints	Description
`id`	`uuid`	PRIMARY KEY, DEFAULT random	Unique user identifier
`ipHash`	`varchar(64)`	NOT NULL, UNIQUE	SHA-256 hash of IP address
`username`	`varchar(50)`	NOT NULL	Display name (user-chosen)
`createdAt`	`timestamp`	NOT NULL, DEFAULT now()	Account creation time

export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

Example:

const user: User = {
  id: "550e8400-e29b-41d4-a716-446655440000",
  ipHash: "2c26b46b68ffc68ff99b453c1d30413413422d706...",
  username: "Anonymous_1234",
  createdAt: new Date("2024-01-15T10:30:00Z")
};

From schema.ts:63-67:

export const usersRelations = relations(users, ({ many }) => ({
  comments: many(comments),
  commentLikes: many(commentLikes),
  videoLikes: many(videoLikes),
}));

A user can:

Post many comments
Like/dislike many comments
Like/dislike many videos

comments

Stores comments and replies in a self-referential tree structure.

Schema
Indexes
TypeScript Type
Relations

Defined in schema.ts:25-35:

Column	Type	Constraints	Description
`id`	`uuid`	PRIMARY KEY, DEFAULT random	Unique comment identifier
`videoId`	`varchar(50)`	NOT NULL, FK → videos.id	Video being commented on
`userId`	`uuid`	NOT NULL, FK → users.id	Author of the comment
`parentId`	`uuid`	nullable, FK → comments.id	Parent comment (null for top-level)
`content`	`text`	NOT NULL	Comment text
`createdAt`	`timestamp`	NOT NULL, DEFAULT now()	When comment was posted

Cascade Behavior:

If a video is deleted → all its comments are deleted
If a user is deleted → all their comments are deleted

CREATE INDEX comments_video_parent_created 
  ON comments(video_id, parent_id, created_at);

CREATE INDEX comments_video_user_created 
  ON comments(video_id, user_id, created_at);

Purpose:

First index: Efficiently fetch comments for a video, grouped by thread
Second index: Quickly find all comments by a user on a video

export type Comment = typeof comments.$inferSelect;
export type NewComment = typeof comments.$inferInsert;

Example:

// Top-level comment
const topComment: Comment = {
  id: "...",
  videoId: "EFTA01683563",
  userId: "...",
  parentId: null,  // null = top-level
  content: "Great video!",
  createdAt: new Date()
};

// Reply to comment
const reply: Comment = {
  id: "...",
  videoId: "EFTA01683563",
  userId: "...",
  parentId: topComment.id,  // references parent
  content: "I agree!",
  createdAt: new Date()
};

From schema.ts:69-85:

export const commentsRelations = relations(comments, ({ one, many }) => ({
  video: one(videos, {
    fields: [comments.videoId],
    references: [videos.id],
  }),
  user: one(users, {
    fields: [comments.userId],
    references: [users.id],
  }),
  parent: one(comments, {
    fields: [comments.parentId],
    references: [comments.id],
    relationName: "replies",
  }),
  replies: many(comments, { relationName: "replies" }),
  likes: many(commentLikes),
}));

A comment:

Belongs to one video
Is authored by one user
May have one parent comment
Can have many replies
Can have many likes/dislikes

commentLikes

Tracks user likes/dislikes on comments.

Schema
Indexes
TypeScript Type
Relations

Defined in schema.ts:37-45:

Column	Type	Constraints	Description
`id`	`uuid`	PRIMARY KEY, DEFAULT random	Unique like identifier
`commentId`	`uuid`	NOT NULL, FK → comments.id	Comment being liked
`userId`	`uuid`	NOT NULL, FK → users.id	User who liked
`isLike`	`boolean`	NOT NULL	true = like, false = dislike
`createdAt`	`timestamp`	NOT NULL, DEFAULT now()	When action was performed

Cascade Behavior:

If comment is deleted → all its likes are deleted
If user is deleted → all their likes are deleted

CREATE UNIQUE INDEX comment_user_unique 
  ON comment_likes(comment_id, user_id);

Purpose: Ensures one user can only have one like/dislike per comment. Attempting to insert a duplicate will fail.

To change from like to dislike (or vice versa), update the existing row rather than inserting a new one.

export type CommentLike = typeof commentLikes.$inferSelect;
export type NewCommentLike = typeof commentLikes.$inferInsert;

Example:

const like: CommentLike = {
  id: "...",
  commentId: "...",
  userId: "...",
  isLike: true,  // true = thumbs up
  createdAt: new Date()
};

const dislike: CommentLike = {
  id: "...",
  commentId: "...",
  userId: "...",
  isLike: false,  // false = thumbs down
  createdAt: new Date()
};

From schema.ts:87-96:

export const commentLikesRelations = relations(commentLikes, ({ one }) => ({
  comment: one(comments, {
    fields: [commentLikes.commentId],
    references: [comments.id],
  }),
  user: one(users, {
    fields: [commentLikes.userId],
    references: [users.id],
  }),
}));

videoLikes

Tracks user likes/dislikes on videos.

Schema
Indexes
TypeScript Type
Relations

Defined in schema.ts:47-55:

Column	Type	Constraints	Description
`id`	`uuid`	PRIMARY KEY, DEFAULT random	Unique like identifier
`videoId`	`varchar(50)`	NOT NULL, FK → videos.id	Video being liked
`userId`	`uuid`	NOT NULL, FK → users.id	User who liked
`isLike`	`boolean`	NOT NULL	true = like, false = dislike
`createdAt`	`timestamp`	NOT NULL, DEFAULT now()	When action was performed

Cascade Behavior:

If video is deleted → all its likes are deleted
If user is deleted → all their likes are deleted

CREATE UNIQUE INDEX video_user_unique 
  ON video_likes(video_id, user_id);

Purpose: Ensures one user can only have one like/dislike per video.

export type VideoLike = typeof videoLikes.$inferSelect;
export type NewVideoLike = typeof videoLikes.$inferInsert;

Example:

const like: VideoLike = {
  id: "...",
  videoId: "EFTA01683563",
  userId: "...",
  isLike: true,
  createdAt: new Date()
};

From schema.ts:98-107:

export const videoLikesRelations = relations(videoLikes, ({ one }) => ({
  video: one(videos, {
    fields: [videoLikes.videoId],
    references: [videos.id],
  }),
  user: one(users, {
    fields: [videoLikes.userId],
    references: [users.id],
  }),
}));

Database Operations

Connection

The database connection is configured in drizzle.config.ts:

export default defineConfig({
  schema: "./src/db/schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL || 
         "postgres://jtube:jtube@localhost:5432/jtube",
  },
});

Migrations

Generate migration

After modifying schema.ts, generate a migration file:

bun run db:generate

This creates a SQL file in the drizzle/ directory.

Apply migration

Run the migration against your database:

bun run db:migrate

Or push directly (development)

For rapid development, push schema changes directly:

bun run db:push

db:push is for development only. Use db:generate and db:migrate in production.

Seeding

The seed script (src/db/seed.ts) populates the database with video data.

Run Seed
What it does
Example Data Flow

bun run db:seed

From seed.ts:44-90:

Clears existing videos: await db.delete(videos)
Reads video data from src/data.json
Filters out:
- Videos with null titles
- NSFW videos (based on src/video-state.json)
Transforms data:
- Extracts ID from filename (removes extension)
- Converts time strings (“59:22”) to seconds
- Applies metadata overrides from video-state.json
Inserts in batches of 100 to avoid timeout issues

Input (from data.json):

{
  "title": "Elevator Camera",
  "filename": "EFTA01683563.mp4",
  "length": "59:22",
  "hasThumbnail": true
}

Transform:

{
  id: "EFTA01683563",           // filename without .mp4
  title: "Elevator Camera",
  filename: "EFTA01683563.mp4",
  length: 3562,                  // 59:22 = 3562 seconds
  hasThumbnail: true,
  views: 0,
  likes: 0,
  is_shorts: false,
  playlist: null
}

Insert:

await db.insert(videos).values([...]);

Common Queries

import { db } from "./db";
import { videos } from "./db/schema";
import { desc } from "drizzle-orm";

const popularVideos = await db
  .select()
  .from(videos)
  .orderBy(desc(videos.views));

Database Tools

Drizzle Studio

Launch a web-based database GUI:

bun run db:studio

This opens https://local.drizzle.studio where you can:

Browse tables and data
Run queries
Edit records
View relationships

PostgreSQL Docker Container

Start
Stop
Reset
Connect via psql

bun run db:start

Starts PostgreSQL in a Docker container with persistent volume.

bun run db:stop

Stops the container but preserves data.

bun run db:stop
docker volume rm server_postgres_data
bun run db:start
bun run db:push

Completely resets the database (deletes all data).

docker exec -it jtube-postgres psql -U jtube -d jtube

Opens an interactive PostgreSQL shell.

Performance Considerations

Indexes

The schema includes strategic indexes for common queries:

Index	Purpose	Query Pattern
`videos_views_idx`	Sort by popularity	`ORDER BY views DESC`
`comments_video_parent_created`	Fetch comment threads	`WHERE video_id = ? AND parent_id IS NULL`
`comments_video_user_created`	User’s comments on video	`WHERE video_id = ? AND user_id = ?`
`comment_user_unique`	Prevent duplicate likes	`WHERE comment_id = ? AND user_id = ?`
`video_user_unique`	Prevent duplicate likes	`WHERE video_id = ? AND user_id = ?`

Query Optimization Tips

Use selective columns

Instead of:

const videos = await db.select().from(videos);

Use:

const videos = await db
  .select({
    id: videos.id,
    title: videos.title,
    views: videos.views,
  })
  .from(videos);

Paginate large result sets

const pageSize = 20;
const page = 1;

const paginatedVideos = await db
  .select()
  .from(videos)
  .orderBy(desc(videos.views))
  .limit(pageSize)
  .offset((page - 1) * pageSize);

Use joins for related data

Instead of making separate queries, use joins:

const videosWithCommentCount = await db
  .select({
    video: videos,
    commentCount: count(comments.id),
  })
  .from(videos)
  .leftJoin(comments, eq(videos.id, comments.videoId))
  .groupBy(videos.id);

Batch inserts

The seed script demonstrates efficient batch inserts:

const batchSize = 100;
for (let i = 0; i < data.length; i += batchSize) {
  const batch = data.slice(i, i + batchSize);
  await db.insert(videos).values(batch);
}

Schema Modifications

When you need to modify the schema:

Edit schema.ts

Make your changes to server/src/db/schema.ts.Example: Add a description field to videos:

export const videos = pgTable("videos", {
  // ... existing fields
  description: text("description"),
});

Generate migration

bun run db:generate

This creates a migration file in drizzle/ that adds the column.

Review migration SQL

Check the generated SQL file to ensure it does what you expect.

Apply migration

bun run db:migrate

Or for development:

bun run db:push

Update TypeScript types

Drizzle automatically infers types from the schema. Your IDE should now recognize the new field.

Backup & Restore

Backup

# Backup to SQL file
docker exec jtube-postgres pg_dump -U jtube jtube > backup.sql

# Backup with compression
docker exec jtube-postgres pg_dump -U jtube jtube | gzip > backup.sql.gz

Restore

# Restore from SQL file
docker exec -i jtube-postgres psql -U jtube -d jtube < backup.sql

# Restore from compressed backup
gunzip -c backup.sql.gz | docker exec -i jtube-postgres psql -U jtube -d jtube

Get Started

Core Features

Development

Overview

Entity Relationship Diagram

Table Schemas

videos

users

comments

commentLikes

videoLikes

Database Operations

Connection

Migrations

Seeding

Common Queries

Database Tools

Drizzle Studio

PostgreSQL Docker Container

Performance Considerations

Indexes

Query Optimization Tips

Schema Modifications

Backup & Restore

Backup

Restore

Next Steps

Setup Guide

Architecture

Build docs developers (and LLMs) love

Get Started

Core Features

Development

​Overview

​Entity Relationship Diagram

​Table Schemas

​videos

​users

​comments

​commentLikes

​videoLikes

​Database Operations

​Connection

​Migrations

​Seeding

​Common Queries

​Database Tools

​Drizzle Studio

​PostgreSQL Docker Container

​Performance Considerations

​Indexes

​Query Optimization Tips

​Schema Modifications

​Backup & Restore

​Backup

​Restore

​Next Steps

Setup Guide

Architecture

Build docs developers (and LLMs) love

Overview

Entity Relationship Diagram

Table Schemas

videos

users

comments

commentLikes

videoLikes

Database Operations

Connection

Migrations

Seeding

Common Queries

Database Tools

Drizzle Studio

PostgreSQL Docker Container

Performance Considerations

Indexes

Query Optimization Tips

Schema Modifications

Backup & Restore

Backup

Restore

Next Steps