The Simpsons Episode Database - Technical Plan

Overview

A local application that scans TV episodes of The Simpsons, transcribes them, creates searchable embeddings, and provides a web interface for semantic search and video clip creation.

Core Technologies

Backend

• TypeScript - Primary language
• Effect-ts - Functional programming framework for robust error handling and async operations
• Node.js - Runtime environment
• FFmpeg - Video processing and thumbnail extraction
• Whisper AI (OpenAI) - Audio transcription
• OpenAI Embeddings API - Text embeddings for semantic search

Frontend

• React with TypeScript - Modern UI framework
• TanStack Start - Full-stack React framework with type-safe routing
• Tailwind CSS - Utility-first CSS framework
• shadcn/ui - Modern component library

Database

• PostgreSQL - Primary database with vector extension
• pgvector - Vector similarity search for embeddings
• @effect/sql-drizzle-pg - Effect's official Drizzle PostgreSQL integration
• Drizzle ORM - Type-safe schema and query builder

Package Management

• pnpm - Fast, efficient package manager

Tech Stack Architecture

This application follows a TypeScript monorepo structure with clear separation of concerns:

1. Data Layer: PostgreSQL with pgvector extension
2. Repository Layer: Drizzle ORM with Effect integration
3. Service Layer: Effect services for business logic
4. API Layer: HTTP endpoints using Effect's HTTP module
5. Frontend Layer: React with TanStack Start

┌─────────────────┐    ┌─────────────────┐    ┌─────────────────┐
│   Frontend      │    │   Backend API   │    │   Database      │
│ (TanStack Start)│◄──►│   (Effect-ts)   │◄──►│   (PostgreSQL)  │
└─────────────────┘    └─────────────────┘    └─────────────────┘
                              │
                              ▼
                       ┌─────────────────┐
                       │  Video Pipeline │
                       │    (FFmpeg)     │
                       └─────────────────┘

Monorepo Structure

the-simpsons-db/
├── apps/
│   ├── web/                    # Frontend React application
│   │   ├── src/
│   │   ├── package.json
│   │   └── tsconfig.json
│   ├── api/                    # Backend API server
│   │   ├── src/
│   │   ├── package.json
│   │   └── tsconfig.json
│   └── admin/                  # Admin panel application
│       ├── src/
│       ├── package.json
│       └── tsconfig.json
├── packages/
│   ├── database/               # Database schemas and migrations
│   │   ├── src/
│   │   │   ├── schemas/
│   │   │   ├── migrations/
│   │   │   └── index.ts
│   │   ├── package.json
│   │   └── tsconfig.json
│   ├── core/                   # Shared business logic and services
│   │   ├── src/
│   │   │   ├── services/
│   │   │   ├── repositories/
│   │   │   ├── types/
│   │   │   └── index.ts
│   │   ├── package.json
│   │   └── tsconfig.json
│   ├── video-processor/        # Video processing utilities
│   │   ├── src/
│   │   ├── package.json
│   │   └── tsconfig.json
│   ├── ai-services/            # OpenAI integration services
│   │   ├── src/
│   │   ├── package.json
│   │   └── tsconfig.json
│   └── shared/                 # Shared utilities and types
│       ├── src/
│       ├── package.json
│       └── tsconfig.json
├── data/
│   ├── input/                  # Raw video files
│   ├── processed/              # Processed audio, thumbnails
│   └── temp/                   # Temporary processing files
├── package.json                # Root package.json with workspaces
├── pnpm-workspace.yaml         # pnpm workspace configuration
├── tsconfig.json               # Root TypeScript configuration
└── turbo.json                  # Turborepo configuration (optional)

Package Dependencies

• @simpsons-db/database: Database schemas, migrations, and connection utilities
• @simpsons-db/core: Business logic services and repositories
• @simpsons-db/api: API definition that is shared between server and client
• @simpsons-db/video-processor: FFmpeg integration and video processing
• @simpsons-db/ai-services: OpenAI API integration for transcription and embeddings
• @simpsons-db/shared: Common types, utilities, and constants

Workspace Configuration

# pnpm-workspace.yaml
packages:
  - "apps/*"
  - "packages/*"

// Root package.json
{
  "name": "the-simpsons-db",
  "private": true,
  "workspaces": ["apps/*", "packages/*"],
  "packageManager": "pnpm@8.0.0",
  "scripts": {
    "build": "pnpm -r build",
    "dev": "pnpm -r --parallel dev",
    "test": "pnpm -r test",
    "lint": "pnpm -r lint",
    "type-check": "pnpm -r type-check"
  }
}

Database Schema

Episodes Table

CREATE TABLE episodes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  season_number INTEGER NOT NULL,
  episode_number INTEGER NOT NULL,
  title VARCHAR(255),
  air_date DATE,
  description TEXT,
  imdb_id VARCHAR(20),
  tvdb_id INTEGER,
  file_path VARCHAR(500) NOT NULL,
  file_size BIGINT,
  duration_seconds INTEGER,
  video_codec VARCHAR(50),
  audio_codec VARCHAR(50),
  resolution VARCHAR(20),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(season_number, episode_number)
);

Transcriptions Table

CREATE TABLE transcriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  episode_id UUID REFERENCES episodes(id) ON DELETE CASCADE,
  start_time_seconds DECIMAL(10,3) NOT NULL,
  end_time_seconds DECIMAL(10,3) NOT NULL,
  text TEXT NOT NULL,
  confidence DECIMAL(4,3),
  speaker VARCHAR(100),
  created_at TIMESTAMP DEFAULT NOW(),
  INDEX(episode_id, start_time_seconds)
);

Transcription Embeddings Table

CREATE TABLE transcription_embeddings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  transcription_id UUID REFERENCES transcriptions(id) ON DELETE CASCADE,
  embedding VECTOR(1536) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Vector similarity index
CREATE INDEX ON transcription_embeddings USING ivfflat (embedding vector_cosine_ops);

Thumbnails Table

CREATE TABLE thumbnails (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  episode_id UUID REFERENCES episodes(id) ON DELETE CASCADE,
  timestamp_seconds DECIMAL(10,3) NOT NULL,
  file_path VARCHAR(500) NOT NULL,
  width INTEGER,
  height INTEGER,
  created_at TIMESTAMP DEFAULT NOW(),
  INDEX(episode_id, timestamp_seconds)
);

Episode Metadata Table (from API)

CREATE TABLE episode_metadata (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  season_number INTEGER NOT NULL,
  episode_number INTEGER NOT NULL,
  title VARCHAR(255) NOT NULL,
  air_date DATE,
  description TEXT,
  imdb_id VARCHAR(20),
  tvdb_id INTEGER,
  rating DECIMAL(3,1),
  votes INTEGER,
  writers TEXT[],
  directors TEXT[],
  guest_stars TEXT[],
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(season_number, episode_number)
);

Video Processing Pipeline

1. File Discovery

• Scan input directory for video files (.mp4, .mkv, .avi)
• Extract metadata using FFprobe
• Store file information in database

Frontend Features

1. Search Interface

• Semantic Search: Vector similarity search using embeddings
• Filters: Season, episode, date range, duration
• Results: Episode cards with thumbnails, titles, and relevant excerpts

2. Episode Details

• Full transcription with timestamps
• Thumbnail gallery
• Video player with transcript sync
• Metadata display (cast, crew, air date)

3. Clip Creation

• Select start/end timestamps from transcription
• Preview clip before generation
• Generate MP4 clips using FFmpeg
• Download or share generated clips

4. Admin Panel

• Import Episodes: Trigger metadata import from API
• Scan Videos: Process video files in input directory
• Processing Status: View progress of transcription/embedding jobs
• Storage Management: View disk usage, clean up temporary files

Third-Party API Integration

The Movie Database (TMDB) API

const importEpisodeMetadata = Effect.gen(function* () {
  const seasons = yield* fetchSimpsonsSeasons()

  for (const season of seasons) {
    const episodes = yield* fetchSeasonEpisodes(season.number)
    yield* storeEpisodeMetadata(episodes)
  }
})

Alternative: TVMaze API

• Free alternative to TMDB
• Good coverage of TV show data
• No API key required

Development Phases

Phase 1: Core Infrastructure

• Database setup with migrations
• Basic video file scanning
• Simple transcription pipeline

Phase 2: Search & Embeddings

• Embedding generation
• Vector search implementation
• Basic web interface

Phase 3: Advanced Features

• Clip generation
• Thumbnail extraction
• Admin panel

Phase 4: Polish & Optimization

• Performance optimization
• Error handling improvements
• UI/UX enhancements

Environment Configuration

# Database
DATABASE_URL="postgresql://user:pass@localhost:5432/simpsons_db"

# OpenAI
OPENAI_API_KEY="sk-..."

# TMDB (optional)
TMDB_API_KEY="..."

# Paths
INPUT_VIDEO_PATH="/path/to/simpsons/episodes"
PROCESSED_DATA_PATH="/path/to/processed/data"

Effect Services Architecture

Service Layer (Business Logic)

// Episodes domain service
class EpisodesService extends Effect.Service<EpisodesService>()(
  "EpisodesService",
  {
    scoped: Effect.gen(function* () {
      const repository = yield* EpisodesRepository
      const videoProcessor = yield* VideoProcessorService
      const transcription = yield* TranscriptionService

      const scanVideoFiles = (inputPath: string) =>
        Effect.gen(function* () {
          const files = yield* videoProcessor.discoverVideoFiles(inputPath)
          const episodes = yield* Effect.forEach(files, (file) =>
            Effect.gen(function* () {
              const metadata = yield* videoProcessor.extractMetadata(file)
              return yield* repository.create({
                filePath: file.path,
                fileSize: file.size,
                duration: metadata.duration,
                videoCodec: metadata.videoCodec,
                audioCodec: metadata.audioCodec,
                resolution: metadata.resolution
              })
            })
          )
          return episodes
        }).pipe(
          Effect.annotateSpan("scanVideoFiles", { inputPath }),
          Effect.withSpan("EpisodesService.scanVideoFiles")
        )

      const processEpisode = (episodeId: string) =>
        Effect.gen(function* () {
          const episode = yield* repository.findById(episodeId)
          yield* transcription.processEpisode(episode)
          return episode
        }).pipe(
          Effect.annotateSpan("processEpisode", { episodeId }),
          Effect.withSpan("EpisodesService.processEpisode")
        )

      return { scanVideoFiles, processEpisode }
    })
  }
) {}

// Embeddings domain service
class EmbeddingsService extends Effect.Service<EmbeddingsService>()(
  "EmbeddingsService",
  {
    scoped: Effect.gen(function* () {
      const repository = yield* EmbeddingsRepository
      const transcriptionRepo = yield* TranscriptionRepository
      const openai = yield* OpenAIService

      const generateEmbeddings = (episodeId: string) =>
        Effect.gen(function* () {
          const transcriptions = yield* transcriptionRepo.findByEpisodeId(
            episodeId
          )

          const embeddings = yield* Effect.forEach(transcriptions, (segment) =>
            Effect.gen(function* () {
              const embedding = yield* openai.createEmbedding(segment.text)
              return yield* repository.create({
                transcriptionId: segment.id,
                embedding: embedding.data[0].embedding
              })
            })
          )

          return embeddings
        }).pipe(
          Effect.annotateSpan("generateEmbeddings", { episodeId }),
          Effect.withSpan("EmbeddingsService.generateEmbeddings")
        )

      const semanticSearch = (query: string, limit: number = 10) =>
        Effect.gen(function* () {
          const queryEmbedding = yield* openai.createEmbedding(query)
          const results = yield* repository.findSimilar(
            queryEmbedding.data[0].embedding,
            limit
          )
          return results
        }).pipe(
          Effect.annotateSpan("semanticSearch", { query, limit }),
          Effect.withSpan("EmbeddingsService.semanticSearch")
        )

      return { generateEmbeddings, semanticSearch }
    })
  }
) {}

// Video processing service
class VideoProcessorService extends Effect.Service<VideoProcessorService>()(
  "VideoProcessorService",
  {
    scoped: Effect.gen(function* () {
      const ffmpeg = yield* FFmpegService

      const extractThumbnails = (videoPath: string, outputDir: string) =>
        Effect.gen(function* () {
          const duration = yield* ffmpeg.getDuration(videoPath)
          const intervals = Array.from(
            { length: Math.floor(duration / 30) },
            (_, i) => i * 30
          )

          const thumbnails = yield* Effect.forEach(intervals, (timestamp) =>
            ffmpeg.extractFrame(
              videoPath,
              timestamp,
              `${outputDir}/thumb_${timestamp}.jpg`
            )
          )

          return thumbnails
        }).pipe(
          Effect.annotateSpan("extractThumbnails", { videoPath }),
          Effect.withSpan("VideoProcessorService.extractThumbnails")
        )

      const createClip = (
        videoPath: string,
        startTime: number,
        endTime: number,
        outputPath: string
      ) =>
        ffmpeg.createClip(videoPath, startTime, endTime, outputPath).pipe(
          Effect.annotateSpan("createClip", {
            videoPath,
            startTime,
            endTime
          }),
          Effect.withSpan("VideoProcessorService.createClip")
        )

      return { extractThumbnails, createClip }
    })
  }
) {}

Repository Layer (Data Access)

// Episodes repository interface
abstract class EpisodesRepository extends Effect.Service<EpisodesRepository>()(
  "EpisodesRepository",
  {
    accessors: true
  }
) {
  abstract create: (
    data: CreateEpisodeData
  ) => Effect.Effect<Episode, DatabaseError>
  abstract findById: (
    id: string
  ) => Effect.Effect<Episode, DatabaseError | NotFoundError>
  abstract findBySeasonAndEpisode: (
    season: number,
    episode: number
  ) => Effect.Effect<Episode, DatabaseError | NotFoundError>
  abstract findAll: (
    filters?: EpisodeFilters
  ) => Effect.Effect<Episode[], DatabaseError>
  abstract update: (
    id: string,
    data: UpdateEpisodeData
  ) => Effect.Effect<Episode, DatabaseError | NotFoundError>
  abstract delete: (
    id: string
  ) => Effect.Effect<void, DatabaseError | NotFoundError>
}

// Drizzle schema definitions
import {
  pgTable,
  uuid,
  varchar,
  integer,
  decimal,
  timestamp,
  vector,
  text,
  bigint
} from "drizzle-orm/pg-core"

const episodes = pgTable("episodes", {
  id: uuid("id").primaryKey().defaultRandom(),
  seasonNumber: integer("season_number").notNull(),
  episodeNumber: integer("episode_number").notNull(),
  title: varchar("title", { length: 255 }),
  airDate: timestamp("air_date", { mode: "date" }),
  description: text("description"),
  imdbId: varchar("imdb_id", { length: 20 }),
  tvdbId: integer("tvdb_id"),
  filePath: varchar("file_path", { length: 500 }).notNull(),
  fileSize: bigint("file_size", { mode: "number" }),
  durationSeconds: integer("duration_seconds"),
  videoCodec: varchar("video_codec", { length: 50 }),
  audioCodec: varchar("audio_codec", { length: 50 }),
  resolution: varchar("resolution", { length: 20 }),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow()
})

const transcriptions = pgTable("transcriptions", {
  id: uuid("id").primaryKey().defaultRandom(),
  episodeId: uuid("episode_id").references(() => episodes.id, {
    onDelete: "cascade"
  }),
  startTimeSeconds: decimal("start_time_seconds", {
    precision: 10,
    scale: 3
  }).notNull(),
  endTimeSeconds: decimal("end_time_seconds", {
    precision: 10,
    scale: 3
  }).notNull(),
  text: text("text").notNull(),
  confidence: decimal("confidence", { precision: 4, scale: 3 }),
  speaker: varchar("speaker", { length: 100 }),
  createdAt: timestamp("created_at").defaultNow()
})

const transcriptionEmbeddings = pgTable("transcription_embeddings", {
  id: uuid("id").primaryKey().defaultRandom(),
  transcriptionId: uuid("transcription_id").references(
    () => transcriptions.id,
    { onDelete: "cascade" }
  ),
  embedding: vector("embedding", { dimensions: 1536 }).notNull(),
  createdAt: timestamp("created_at").defaultNow()
})

// @effect/sql-drizzle-pg implementation
import { DrizzlePgClient } from "@effect/sql-drizzle-pg"
import { eq, and } from "drizzle-orm"

class DrizzleEpisodesRepository extends EpisodesRepository {
  static readonly Live = Effect.gen(function* () {
    const db = yield* DrizzlePgClient

    const create = (data: CreateEpisodeData) =>
      db
        .insert(episodes)
        .values(data)
        .returning()
        .pipe(
          Effect.map((rows) => rows[0] as Episode),
          Effect.mapError((error) => new DatabaseError({ cause: error }))
        )

    const findById = (id: string) =>
      db
        .select()
        .from(episodes)
        .where(eq(episodes.id, id))
        .pipe(
          Effect.map((rows) => rows[0] as Episode | undefined),
          Effect.flatMap((episode) =>
            episode
              ? Effect.succeed(episode)
              : Effect.fail(new NotFoundError({ id }))
          ),
          Effect.mapError((error) => new DatabaseError({ cause: error }))
        )

    const findAll = (filters?: EpisodeFilters) =>
      Effect.gen(function* () {
        let query = db.select().from(episodes)
        if (filters) {
          query = query.where(buildDrizzleWhereClause(filters))
        }
        return yield* query.orderBy(
          episodes.seasonNumber,
          episodes.episodeNumber
        )
      }).pipe(
        Effect.map((rows) => rows as Episode[]),
        Effect.mapError((error) => new DatabaseError({ cause: error }))
      )

    const findBySeasonAndEpisode = (season: number, episode: number) =>
      db
        .select()
        .from(episodes)
        .where(
          and(
            eq(episodes.seasonNumber, season),
            eq(episodes.episodeNumber, episode)
          )
        )
        .pipe(
          Effect.map((rows) => rows[0] as Episode | undefined),
          Effect.flatMap((ep) =>
            ep
              ? Effect.succeed(ep)
              : Effect.fail(new NotFoundError({ season, episode }))
          ),
          Effect.mapError((error) => new DatabaseError({ cause: error }))
        )

    return EpisodesRepository.of({
      create,
      findById,
      findAll,
      findBySeasonAndEpisode
    })
  }).pipe(Effect.scoped)
}

// Transcription Embeddings repository
abstract class TranscriptionEmbeddingsRepository extends Effect.Service<TranscriptionEmbeddingsRepository>()(
  "TranscriptionEmbeddingsRepository",
  {
    accessors: true
  }
) {
  abstract create: (
    data: CreateTranscriptionEmbeddingData
  ) => Effect.Effect<TranscriptionEmbedding, DatabaseError>
  abstract findSimilar: (
    vector: number[],
    limit: number
  ) => Effect.Effect<SimilarityResult[], DatabaseError>
  abstract findByTranscriptionId: (
    transcriptionId: string
  ) => Effect.Effect<TranscriptionEmbedding[], DatabaseError>
}

class DrizzleTranscriptionEmbeddingsRepository extends TranscriptionEmbeddingsRepository {
  static readonly Live = Effect.gen(function* () {
    const db = yield* DrizzlePgClient

    const create = (data: CreateTranscriptionEmbeddingData) =>
      db
        .insert(transcriptionEmbeddings)
        .values(data)
        .returning()
        .pipe(
          Effect.map((rows) => rows[0] as TranscriptionEmbedding),
          Effect.mapError((error) => new DatabaseError({ cause: error }))
        )

    const findSimilar = (vector: number[], limit: number) =>
      db
        .execute(
          sql`
        SELECT e.*, t.text, t.start_time_seconds, t.end_time_seconds, 
               ep.title, ep.season_number, ep.episode_number,
               1 - (e.embedding <=> ${vector}::vector) as similarity
        FROM ${transcriptionEmbeddings} e
        JOIN ${transcriptions} t ON e.transcription_id = t.id
        JOIN ${episodes} ep ON t.episode_id = ep.id
        ORDER BY e.embedding <=> ${vector}::vector
        LIMIT ${limit}
      `
        )
        .pipe(
          Effect.map((result) => result.rows as SimilarityResult[]),
          Effect.mapError((error) => new DatabaseError({ cause: error }))
        )

    const findByTranscriptionId = (transcriptionId: string) =>
      db
        .select()
        .from(transcriptionEmbeddings)
        .where(eq(transcriptionEmbeddings.transcriptionId, transcriptionId))
        .pipe(
          Effect.map((rows) => rows as TranscriptionEmbedding[]),
          Effect.mapError((error) => new DatabaseError({ cause: error }))
        )

    return TranscriptionEmbeddingsRepository.of({
      create,
      findSimilar,
      findByTranscriptionId
    })
  }).pipe(Effect.scoped)
}

// Transcription repository
abstract class TranscriptionRepository extends Effect.Service<TranscriptionRepository>()(
  "TranscriptionRepository",
  {
    accessors: true
  }
) {
  abstract create: (
    data: CreateTranscriptionData
  ) => Effect.Effect<Transcription, DatabaseError>
  abstract findByEpisodeId: (
    episodeId: string
  ) => Effect.Effect<Transcription[], DatabaseError>
  abstract findByTimeRange: (
    episodeId: string,
    startTime: number,
    endTime: number
  ) => Effect.Effect<Transcription[], DatabaseError>
}

External Service Interfaces

// OpenAI service
abstract class OpenAIService extends Effect.Service<OpenAIService>()(
  "OpenAIService",
  {
    accessors: true
  }
) {
  abstract createEmbedding: (
    text: string
  ) => Effect.Effect<EmbeddingResponse, OpenAIError>
  abstract transcribeAudio: (
    audioPath: string
  ) => Effect.Effect<TranscriptionResponse, OpenAIError>
}

// FFmpeg service
abstract class FFmpegService extends Effect.Service<FFmpegService>()(
  "FFmpegService",
  {
    accessors: true
  }
) {
  abstract extractAudio: (
    videoPath: string,
    audioPath: string
  ) => Effect.Effect<void, FFmpegError>
  abstract extractFrame: (
    videoPath: string,
    timestamp: number,
    outputPath: string
  ) => Effect.Effect<string, FFmpegError>
  abstract createClip: (
    videoPath: string,
    startTime: number,
    endTime: number,
    outputPath: string
  ) => Effect.Effect<string, FFmpegError>
  abstract getDuration: (
    videoPath: string
  ) => Effect.Effect<number, FFmpegError>
}

Testing with Mock Repositories

// Mock repository for testing
class MockEpisodesRepository extends EpisodesRepository {
  static readonly Test = Effect.gen(function* () {
    const episodes = new Map<string, Episode>()

    const create = (data: CreateEpisodeData) =>
      Effect.sync(() => {
        const episode = {
          id: crypto.randomUUID(),
          ...data,
          createdAt: new Date()
        }
        episodes.set(episode.id, episode)
        return episode
      })

    const findById = (id: string) =>
      Effect.sync(() => {
        const episode = episodes.get(id)
        if (!episode) return Effect.fail(new NotFoundError({ id }))
        return Effect.succeed(episode)
      }).pipe(Effect.flatten)

    return EpisodesRepository.of({ create, findById })
  })
}

// Test example
const testEpisodesService = Effect.gen(function* () {
  const service = yield* EpisodesService
  const episode = yield* service.scanVideoFiles("/test/path")
  expect(episode).toBeDefined()
}).pipe(
  Effect.provide(MockEpisodesRepository.Test),
  Effect.provide(EpisodesService.Default)
)

Performance Considerations

• Batch Processing: Process multiple episodes in parallel
• Chunked Embeddings: Split long transcriptions into searchable chunks
• Caching: Cache frequently accessed embeddings and metadata
• Indexing: Proper database indexes for fast queries
• Storage: Separate hot (thumbnails) and cold (full videos) storage