box-nestjs-monorepo/REDIS_CACHE_IMPLEMENTATION.md

Redis Cache Migration - Implementation Guide

For: Multi-Category VideoMedia Support
Status: Planning Phase
Updated: December 2025

---

Overview

This guide provides step-by-step implementation details for migrating Redis cache to support:

1. Multi-category videos (categoryIds: string[] instead of categoryId: string)
2. Denormalized tag data in Category/Channel cache
3. Efficient lookup indexes for 58,000+ videos

---

Phase 1: Prisma Schema & Database Migration

Step 1.1: Update Prisma Schema

File: prisma/mongo/schema/video-media.prisma ✅ DONE

// OLD
categoryId    String?    @db.ObjectId      // 分类 ID (local)

// NEW
categoryIds   String[]   @default([]) @db.ObjectId  // 分类 IDs (local)

File: prisma/mongo/schema/category.prisma ✅ DONE

// REMOVE
channelId   String     @db.ObjectId        // 渠道 ID
channel     Channel    @relation(...)

// ADD
tags        Json?                           // Array of { id, name }
tagNames    String[]                        // Array of tag names

File: prisma/mongo/schema/tag.prisma ✅ DONE

// REMOVE
channelId   String     @db.ObjectId      // 渠道 ID
channel     Channel    @relation(...)

// ADD (Optional)
status      Int        @default(1)        // 状态

Step 1.2: Create Database Migration

# Generate migration
pnpm prisma:migrate:create -- --name update_video_multi_category

# Or if using dev
pnpm prisma:generate

Step 1.3: Run Migration

# For production
pnpm prisma:migrate:deploy

# For development
pnpm prisma:generate

---

Phase 2: Update Cache Builders

Step 2.1: Update TagCacheBuilder

File: libs/core/src/cache/tag/tag-cache.builder.ts

import { Injectable } from '@nestjs/common';
import { BaseCacheBuilder } from '@box/common/cache/cache-builder';
import { CacheKeys } from '@box/common/cache/cache-keys';
import { RedisService } from '@box/db/redis/redis.service';
import { MongoPrismaService } from '@box/db/prisma/mongo-prisma.service';

// UPDATED INTERFACE: Remove channelId
export interface TagCachePayload {
  id: string;
  name: string;
  categoryId: string; // Keep: still needed for categorization
  seq: number;
  status?: number; // New: optional status field
}

@Injectable()
export class TagCacheBuilder extends BaseCacheBuilder {
  constructor(redis: RedisService, mongoPrisma: MongoPrismaService) {
    super(redis, mongoPrisma, TagCacheBuilder.name);
  }

  async buildAll(): Promise<void> {
    try {
      // Query: Only get active tags
      const tags = await this.mongoPrisma.tag.findMany({
        where: { status: 1 },
        orderBy: [{ seq: 'asc' }, { name: 'asc' }],
      });

      // Transform: Remove channelId, add status
      const payload: TagCachePayload[] = tags.map((tag) => ({
        id: tag.id,
        name: tag.name,
        categoryId: tag.categoryId, // Changed: only categoryId
        seq: tag.seq,
        status: tag.status, // New
      }));

      // Store in Redis
      await this.redis.setJson(CacheKeys.appTagAll, payload);
      this.logger.log(
        `[TagCacheBuilder] Built ${payload.length} tags (removed channelId dependency)`,
      );
    } catch (error) {
      this.logger.error('[TagCacheBuilder] Build failed:', error);
      throw error;
    }
  }

  // New: Build tags by category for tag list key
  async buildByCategory(categoryId: string): Promise<void> {
    try {
      const tags = await this.mongoPrisma.tag.findMany({
        where: { status: 1, categoryId },
        orderBy: [{ seq: 'asc' }, { name: 'asc' }],
      });

      const key = CacheKeys.appTagByCategoryKey(categoryId);

      if (tags.length === 0) {
        await this.redis.del(key);
        this.logger.debug(
          `[TagCacheBuilder] Cleared tags for category ${categoryId}`,
        );
        return;
      }

      const payload: TagCachePayload[] = tags.map((tag) => ({
        id: tag.id,
        name: tag.name,
        categoryId: tag.categoryId,
        seq: tag.seq,
        status: tag.status,
      }));

      // Store as Redis list (RPUSH each tag as JSON)
      await this.redis.del(key);
      for (const tag of payload) {
        await this.redis.rpush(key, JSON.stringify(tag));
      }
      await this.redis.expire(key, 604800); // 7 days TTL

      this.logger.debug(
        `[TagCacheBuilder] Built ${payload.length} tags for category ${categoryId}`,
      );
    } catch (error) {
      this.logger.error(
        `[TagCacheBuilder] Failed to build tags for category ${categoryId}:`,
        error,
      );
      throw error;
    }
  }
}

---

Step 2.2: Update CategoryCacheBuilder

File: libs/core/src/cache/category/category-cache.builder.ts

import { Injectable, Logger } from '@nestjs/common';
import { BaseCacheBuilder } from '@box/common/cache/cache-builder';
import { CacheKeys } from '@box/common/cache/cache-keys';
import { RedisService } from '@box/db/redis/redis.service';
import { MongoPrismaService } from '@box/db/prisma/mongo-prisma.service';

// UPDATED INTERFACE
export interface CategoryCachePayload {
  id: string;
  name: string;
  subtitle?: string | null;
  seq: number;
  status: number;
  // Changed: tags now as JSON objects {id, name}
  tags?: Array<{ id: string; name: string }>;
  // New: tagNames for search optimization
  tagNames?: string[];
}

@Injectable()
export class CategoryCacheBuilder extends BaseCacheBuilder {
  private readonly logger = new Logger(CategoryCacheBuilder.name);

  constructor(redis: RedisService, mongoPrisma: MongoPrismaService) {
    super(redis, mongoPrisma, CategoryCacheBuilder.name);
  }

  async buildAll(): Promise<void> {
    try {
      // Step 1: Fetch all active categories
      const categories = await this.mongoPrisma.category.findMany({
        where: { status: 1 },
        orderBy: [{ seq: 'asc' }, { name: 'asc' }],
      });

      this.logger.log(
        `[CategoryCacheBuilder] Found ${categories.length} active categories`,
      );

      // Step 2: Fetch all active tags
      const categoryIds = categories.map((c) => c.id);
      const tags = categoryIds.length
        ? await this.mongoPrisma.tag.findMany({
            where: { status: 1, categoryId: { in: categoryIds } },
            orderBy: [{ seq: 'asc' }, { name: 'asc' }],
          })
        : [];

      this.logger.log(
        `[CategoryCacheBuilder] Found ${tags.length} active tags`,
      );

      // Step 3: Build tag lookup maps
      const tagsByCategory = new Map<
        string,
        Array<{ id: string; name: string }>
      >();
      const tagNamesByCategory = new Map<string, string[]>();

      for (const tag of tags) {
        // Map 1: Tag objects {id, name}
        const tagList = tagsByCategory.get(tag.categoryId) || [];
        tagList.push({ id: tag.id, name: tag.name });
        tagsByCategory.set(tag.categoryId, tagList);

        // Map 2: Tag names only (for search)
        const nameList = tagNamesByCategory.get(tag.categoryId) || [];
        nameList.push(tag.name);
        tagNamesByCategory.set(tag.categoryId, nameList);
      }

      // Step 4: Build category payloads
      const payloads: CategoryCachePayload[] = categories.map((category) => ({
        id: category.id,
        name: category.name,
        subtitle: category.subtitle ?? null,
        seq: category.seq,
        status: category.status,
        tags: tagsByCategory.get(category.id), // JSON objects
        tagNames: tagNamesByCategory.get(category.id), // Names only
      }));

      // Step 5: Store in Redis
      const key = CacheKeys.appCategoryAll;
      await this.redis.setJson(key, payloads);
      await this.redis.expire(key, 604800); // 7 days TTL

      this.logger.log(
        `[CategoryCacheBuilder] Built ${payloads.length} categories with tag denormalization`,
      );

      // Step 6: Also store individual category records
      for (const payload of payloads) {
        const categoryKey = CacheKeys.appCategoryById(payload.id);
        await this.redis.setJson(categoryKey, payload);
        await this.redis.expire(categoryKey, 604800);
      }

      this.logger.log(
        `[CategoryCacheBuilder] Stored ${payloads.length} individual category records`,
      );

      return;
    } catch (error) {
      this.logger.error('[CategoryCacheBuilder] Build failed:', error);
      throw error;
    }
  }

  // New: Build flat tag names for search optimization
  async buildTagNamesFlat(categoryId: string): Promise<void> {
    try {
      const tags = await this.mongoPrisma.tag.findMany({
        where: { status: 1, categoryId },
        select: { name: true },
      });

      const key = `app:category:tagnames:flat:${categoryId}`;
      const flatNames = tags.map((t) => t.name.toLowerCase()).join(' ');

      if (flatNames) {
        await this.redis.set(key, flatNames);
        await this.redis.expire(key, 86400); // 24 hours
      } else {
        await this.redis.del(key);
      }

      this.logger.debug(
        `[CategoryCacheBuilder] Built flat tag names for category ${categoryId}`,
      );
    } catch (error) {
      this.logger.error(
        `[CategoryCacheBuilder] Failed to build tag names flat for ${categoryId}:`,
        error,
      );
      throw error;
    }
  }
}

---

Step 2.3: Update ChannelCacheBuilder

File: libs/core/src/cache/channel/channel-cache.builder.ts

import { Injectable, Logger } from '@nestjs/common';
import { BaseCacheBuilder } from '@box/common/cache/cache-builder';
import { CacheKeys } from '@box/common/cache/cache-keys';
import { RedisService } from '@box/db/redis/redis.service';
import { MongoPrismaService } from '@box/db/prisma/mongo-prisma.service';

// UPDATED INTERFACE
export interface ChannelCachePayload {
  id: string;
  name: string;
  landingUrl: string;
  videoCdn?: string;
  coverCdn?: string;
  clientName?: string;
  clientNotice?: string;
  remark?: string;
  // New: Denormalized categories and tags
  categories?: Array<{ id: string; name: string }>;
  tags?: Array<{ id: string; name: string }>;
  tagNames?: string[];
}

@Injectable()
export class ChannelCacheBuilder extends BaseCacheBuilder {
  private readonly logger = new Logger(ChannelCacheBuilder.name);

  constructor(redis: RedisService, mongoPrisma: MongoPrismaService) {
    super(redis, mongoPrisma, ChannelCacheBuilder.name);
  }

  async buildAll(): Promise<void> {
    try {
      // Step 1: Fetch all channels
      const channels = await this.mongoPrisma.channel.findMany({
        orderBy: [{ name: 'asc' }],
      });

      // Step 2: Fetch all categories and tags
      const categories = await this.mongoPrisma.category.findMany({
        where: { status: 1 },
      });

      const tags = await this.mongoPrisma.tag.findMany({
        where: { status: 1 },
      });

      // Step 3: Build payloads
      const payloads: ChannelCachePayload[] = channels.map((channel) => ({
        id: channel.id,
        name: channel.name,
        landingUrl: channel.landingUrl,
        videoCdn: channel.videoCdn ?? undefined,
        coverCdn: channel.coverCdn ?? undefined,
        clientName: channel.clientName ?? undefined,
        clientNotice: channel.clientNotice ?? undefined,
        remark: channel.remark ?? undefined,
        // New: Include categories and tags
        categories: categories.map((c) => ({ id: c.id, name: c.name })),
        tags: tags.map((t) => ({ id: t.id, name: t.name })),
        tagNames: [...new Set(tags.map((t) => t.name))], // Unique tag names
      }));

      // Step 4: Store in Redis
      const key = CacheKeys.appChannelAll;
      await this.redis.setJson(key, payloads);
      await this.redis.expire(key, 604800); // 7 days

      this.logger.log(
        `[ChannelCacheBuilder] Built ${payloads.length} channels with denormalized categories and tags`,
      );

      // Step 5: Store individual channel records
      for (const payload of payloads) {
        const channelKey = CacheKeys.appChannelById(payload.id);
        await this.redis.setJson(channelKey, payload);
        await this.redis.expire(channelKey, 604800);
      }

      return;
    } catch (error) {
      this.logger.error('[ChannelCacheBuilder] Build failed:', error);
      throw error;
    }
  }
}

---

Step 2.4: Update VideoDetail Cache Builder

File: Update VideoMedia cache builder (in cache-sync service)

// In video cache builder (update existing)

async buildVideoDetail(videoId: string): Promise<void> {
  const video = await this.mongoPrisma.videoMedia.findUnique({
    where: { id: videoId },
  });

  if (!video) {
    await this.redis.del(tsCacheKeys.video.detail(videoId));
    return;
  }

  // CHANGED: categoryId → categoryIds
  const payload = {
    ...video,
    categoryIds: video.categoryIds,  // Changed: now an array
    // Keep the rest as-is
  };

  const key = tsCacheKeys.video.detail(videoId);
  await this.redis.setJson(key, payload);
  await this.redis.expire(key, 86400); // 24 hours TTL
}

---

Phase 3: Create New Index Builders

Step 3.1: Create VideoCategoryIndexBuilder

File: libs/core/src/cache/video/video-category-index.builder.ts (NEW)

import { Injectable, Logger } from '@nestjs/common';
import { BaseCacheBuilder } from '@box/common/cache/cache-builder';
import { RedisService } from '@box/db/redis/redis.service';
import { MongoPrismaService } from '@box/db/prisma/mongo-prisma.service';

/**
 * Builds category-to-video index for multi-category support.
 * Creates ZSET entries for each category with video IDs as members
 * and addedTime as score for sorting.
 *
 * Key: app:video:category:index:{categoryId}:videos
 * Type: ZSET (video ID → score=addedTime)
 * TTL: 2 hours
 */
@Injectable()
export class VideoCategoryIndexBuilder extends BaseCacheBuilder {
  private readonly logger = new Logger(VideoCategoryIndexBuilder.name);

  constructor(redis: RedisService, mongoPrisma: MongoPrismaService) {
    super(redis, mongoPrisma, VideoCategoryIndexBuilder.name);
  }

  async buildAll(): Promise<void> {
    try {
      this.logger.log('[VideoCategoryIndexBuilder] Starting build...');

      // Step 1: Fetch all videos with categoryIds and addedTime
      const videos = await this.mongoPrisma.videoMedia.findMany({
        select: {
          id: true,
          categoryIds: true,
          addedTime: true,
        },
        where: {
          listStatus: 1, // Only published videos
          categoryIds: { not: { equals: [] } }, // Only videos with categories
        },
      });

      this.logger.log(
        `[VideoCategoryIndexBuilder] Found ${videos.length} videos with categories`,
      );

      // Step 2: Group videos by category
      const categoryIndex = new Map<
        string,
        Array<{ videoId: string; score: number }>
      >();

      for (const video of videos) {
        const score = video.addedTime?.getTime() || Date.now();

        for (const categoryId of video.categoryIds) {
          if (!categoryIndex.has(categoryId)) {
            categoryIndex.set(categoryId, []);
          }
          categoryIndex.get(categoryId)!.push({
            videoId: video.id,
            score,
          });
        }
      }

      this.logger.log(
        `[VideoCategoryIndexBuilder] Built index for ${categoryIndex.size} categories`,
      );

      // Step 3: Store in Redis
      let totalStored = 0;

      for (const [categoryId, videoList] of categoryIndex.entries()) {
        const key = `app:video:category:index:${categoryId}:videos`;

        // Clear old key
        await this.redis.del(key);

        // Add all videos to ZSET
        const pairs: Array<number | string> = [];
        for (const item of videoList) {
          pairs.push(item.score, item.videoId);
        }

        if (pairs.length > 0) {
          await this.redis.zadd(key, ...pairs);
          await this.redis.expire(key, 7200); // 2 hours TTL
          totalStored += videoList.length;
        }
      }

      this.logger.log(
        `[VideoCategoryIndexBuilder] Stored ${totalStored} video-category associations`,
      );
    } catch (error) {
      this.logger.error('[VideoCategoryIndexBuilder] Build failed:', error);
      throw error;
    }
  }

  // Single category rebuild
  async buildCategory(categoryId: string): Promise<void> {
    try {
      const videos = await this.mongoPrisma.videoMedia.findMany({
        select: { id: true, categoryIds: true, addedTime: true },
        where: {
          listStatus: 1,
          categoryIds: { has: categoryId },
        },
      });

      const key = `app:video:category:index:${categoryId}:videos`;
      await this.redis.del(key);

      if (videos.length === 0) {
        this.logger.debug(
          `[VideoCategoryIndexBuilder] No videos for category ${categoryId}`,
        );
        return;
      }

      const pairs: Array<number | string> = [];
      for (const video of videos) {
        const score = video.addedTime?.getTime() || Date.now();
        pairs.push(score, video.id);
      }

      await this.redis.zadd(key, ...pairs);
      await this.redis.expire(key, 7200);

      this.logger.debug(
        `[VideoCategoryIndexBuilder] Built ${videos.length} videos for category ${categoryId}`,
      );
    } catch (error) {
      this.logger.error(
        `[VideoCategoryIndexBuilder] Failed for category ${categoryId}:`,
        error,
      );
      throw error;
    }
  }
}

---

Step 3.2: Create VideoCategoriesLookupBuilder

File: libs/core/src/cache/video/video-categories-lookup.builder.ts (NEW)

import { Injectable, Logger } from '@nestjs/common';
import { BaseCacheBuilder } from '@box/common/cache/cache-builder';
import { RedisService } from '@box/db/redis/redis.service';
import { MongoPrismaService } from '@box/db/prisma/mongo-prisma.service';

/**
 * Builds video-to-categories lookup for quick reference.
 * Creates SET entries for each video with category IDs as members.
 *
 * Key: app:video:categories:{videoId}
 * Type: SET (category IDs)
 * TTL: 24 hours
 */
@Injectable()
export class VideoCategoriesLookupBuilder extends BaseCacheBuilder {
  private readonly logger = new Logger(VideoCategoriesLookupBuilder.name);

  constructor(redis: RedisService, mongoPrisma: MongoPrismaService) {
    super(redis, mongoPrisma, VideoCategoriesLookupBuilder.name);
  }

  async buildAll(): Promise<void> {
    try {
      this.logger.log('[VideoCategoriesLookupBuilder] Starting build...');

      // Fetch all videos with categoryIds
      const videos = await this.mongoPrisma.videoMedia.findMany({
        select: { id: true, categoryIds: true },
        where: { categoryIds: { not: { equals: [] } } },
      });

      this.logger.log(
        `[VideoCategoriesLookupBuilder] Building lookup for ${videos.length} videos`,
      );

      let stored = 0;
      for (const video of videos) {
        if (video.categoryIds.length > 0) {
          const key = `app:video:categories:${video.id}`;
          await this.redis.del(key);
          await this.redis.sadd(key, ...video.categoryIds);
          await this.redis.expire(key, 86400); // 24 hours
          stored++;
        }
      }

      this.logger.log(
        `[VideoCategoriesLookupBuilder] Built lookup for ${stored} videos`,
      );
    } catch (error) {
      this.logger.error('[VideoCategoriesLookupBuilder] Build failed:', error);
      throw error;
    }
  }

  // Single video rebuild
  async buildVideo(videoId: string): Promise<void> {
    try {
      const video = await this.mongoPrisma.videoMedia.findUnique({
        select: { id: true, categoryIds: true },
        where: { id: videoId },
      });

      if (!video) {
        await this.redis.del(`app:video:categories:${videoId}`);
        return;
      }

      const key = `app:video:categories:${videoId}`;
      await this.redis.del(key);

      if (video.categoryIds.length > 0) {
        await this.redis.sadd(key, ...video.categoryIds);
        await this.redis.expire(key, 86400);
      }

      this.logger.debug(
        `[VideoCategoriesLookupBuilder] Built categories for video ${videoId}`,
      );
    } catch (error) {
      this.logger.error(
        `[VideoCategoriesLookupBuilder] Failed for video ${videoId}:`,
        error,
      );
      throw error;
    }
  }
}

---

Step 3.3: Create CategoryVideoCountBuilder

File: libs/core/src/cache/category/category-video-count.builder.ts (NEW)

import { Injectable, Logger } from '@nestjs/common';
import { BaseCacheBuilder } from '@box/common/cache/cache-builder';
import { RedisService } from '@box/db/redis/redis.service';
import { MongoPrismaService } from '@box/db/prisma/mongo-prisma.service';

/**
 * Builds video count per category for UI statistics.
 *
 * Key: app:category:video:count:{categoryId}
 * Type: STRING (integer count)
 * TTL: 1 hour (refreshes frequently)
 */
@Injectable()
export class CategoryVideoCountBuilder extends BaseCacheBuilder {
  private readonly logger = new Logger(CategoryVideoCountBuilder.name);

  constructor(redis: RedisService, mongoPrisma: MongoPrismaService) {
    super(redis, mongoPrisma, CategoryVideoCountBuilder.name);
  }

  async buildAll(): Promise<void> {
    try {
      this.logger.log('[CategoryVideoCountBuilder] Starting build...');

      // Fetch all categories
      const categories = await this.mongoPrisma.category.findMany({
        select: { id: true },
        where: { status: 1 },
      });

      let updated = 0;
      for (const category of categories) {
        const count = await this.mongoPrisma.videoMedia.count({
          where: {
            listStatus: 1,
            categoryIds: { has: category.id },
          },
        });

        const key = `app:category:video:count:${category.id}`;
        if (count > 0) {
          await this.redis.set(key, count.toString());
          await this.redis.expire(key, 3600); // 1 hour TTL
        } else {
          await this.redis.del(key);
        }
        updated++;
      }

      this.logger.log(
        `[CategoryVideoCountBuilder] Updated ${updated} category counts`,
      );
    } catch (error) {
      this.logger.error('[CategoryVideoCountBuilder] Build failed:', error);
      throw error;
    }
  }

  // Single category rebuild
  async buildCategory(categoryId: string): Promise<void> {
    try {
      const count = await this.mongoPrisma.videoMedia.count({
        where: {
          listStatus: 1,
          categoryIds: { has: categoryId },
        },
      });

      const key = `app:category:video:count:${categoryId}`;
      if (count > 0) {
        await this.redis.set(key, count.toString());
        await this.redis.expire(key, 3600); // 1 hour
      } else {
        await this.redis.del(key);
      }

      this.logger.debug(
        `[CategoryVideoCountBuilder] Updated count for category ${categoryId}: ${count}`,
      );
    } catch (error) {
      this.logger.error(
        `[CategoryVideoCountBuilder] Failed for category ${categoryId}:`,
        error,
      );
      throw error;
    }
  }
}

---

Phase 4: Update Cache Sync Service

File: apps/box-mgnt-api/src/cache-sync/cache-sync.service.ts

Add these imports and calls:

import { VideoCategoryIndexBuilder } from '@box/core/cache/video/video-category-index.builder';
import { VideoCategoriesLookupBuilder } from '@box/core/cache/video/video-categories-lookup.builder';
import { CategoryVideoCountBuilder } from '@box/core/cache/category/category-video-count.builder';

@Injectable()
export class CacheSyncService {
  constructor(
    private readonly categoryBuilder: CategoryCacheBuilder,
    private readonly tagBuilder: TagCacheBuilder,
    private readonly channelBuilder: ChannelCacheBuilder,
    private readonly videoCategoryIndexBuilder: VideoCategoryIndexBuilder, // NEW
    private readonly videoCategoriesLookupBuilder: VideoCategoriesLookupBuilder, // NEW
    private readonly categoryVideoCountBuilder: CategoryVideoCountBuilder, // NEW
    // ... other builders
  ) {}

  async rebuildAll(): Promise<void> {
    try {
      this.logger.log('[CacheSyncService] Starting full cache rebuild...');

      // Existing builders
      await this.channelBuilder.buildAll();
      await this.categoryBuilder.buildAll();
      await this.tagBuilder.buildAll();

      // NEW: Index builders
      await this.videoCategoryIndexBuilder.buildAll(); // NEW
      await this.videoCategoriesLookupBuilder.buildAll(); // NEW
      await this.categoryVideoCountBuilder.buildAll(); // NEW

      this.logger.log('[CacheSyncService] Full cache rebuild completed');
    } catch (error) {
      this.logger.error('[CacheSyncService] Rebuild failed:', error);
      throw error;
    }
  }

  // Add method for periodic index refresh (call every 2 hours)
  async refreshIndexes(): Promise<void> {
    try {
      this.logger.log('[CacheSyncService] Refreshing video indexes...');

      await this.videoCategoryIndexBuilder.buildAll();
      await this.videoCategoriesLookupBuilder.buildAll();
      await this.categoryVideoCountBuilder.buildAll();

      this.logger.log('[CacheSyncService] Index refresh completed');
    } catch (error) {
      this.logger.error('[CacheSyncService] Index refresh failed:', error);
      // Don't throw - just log. Indexes are rebuilt from DB when stale.
    }
  }
}

---

Phase 5: Update Invalidation Logic

File: apps/box-mgnt-api/src/cache-sync/cache-sync.service.ts

Add invalidation methods:

// When a video's categoryIds change
async invalidateVideoChanges(videoId: string, oldCategoryIds: string[], newCategoryIds: string[]): Promise<void> {
  try {
    // Invalidate video detail
    await this.redis.del(`app:video:detail:${videoId}`);

    // Invalidate video categories lookup
    await this.redis.del(`app:video:categories:${videoId}`);

    // Invalidate category indexes for old categories
    for (const catId of oldCategoryIds) {
      await this.redis.del(`app:video:category:index:${catId}:videos`);
      await this.redis.del(`app:category:video:count:${catId}`);
    }

    // Invalidate category indexes for new categories
    for (const catId of newCategoryIds) {
      await this.redis.del(`app:video:category:index:${catId}:videos`);
      await this.redis.del(`app:category:video:count:${catId}`);
    }

    this.logger.debug(`Invalidated caches for video ${videoId}`);
  } catch (error) {
    this.logger.error(`Failed to invalidate video ${videoId}:`, error);
  }
}

// When a category is updated
async invalidateCategoryChanges(categoryId: string): Promise<void> {
  try {
    await this.redis.del(`app:category:all`);
    await this.redis.del(`app:category:by-id:${categoryId}`);
    await this.redis.del(`app:category:with-tags:${categoryId}`);
    await this.redis.del(`app:category:video:count:${categoryId}`);
    await this.redis.del(`app:category:tagnames:flat:${categoryId}`);

    this.logger.debug(`Invalidated caches for category ${categoryId}`);
  } catch (error) {
    this.logger.error(`Failed to invalidate category ${categoryId}:`, error);
  }
}

// When a tag is updated
async invalidateTagChanges(tagId: string, categoryId: string): Promise<void> {
  try {
    await this.redis.del(`app:tag:all`);
    await this.redis.del(`app:tag:list:${categoryId}`);
    await this.redis.del(`app:category:tagnames:flat:${categoryId}`);
    await this.redis.del(`app:category:all`);

    this.logger.debug(`Invalidated caches for tag ${tagId}`);
  } catch (error) {
    this.logger.error(`Failed to invalidate tag ${tagId}:`, error);
  }
}

---

Phase 6: Testing Checklist

• Unit tests for each builder
• Integration tests with 58K+ videos
• Redis memory usage validation
• Cache hit rate testing
• Invalidation logic testing
• Performance benchmarks
• Load testing with concurrent operations

---

Deployment Checklist

• Backup existing Redis data
• Run Prisma migrations on staging
• Deploy new cache builders
• Run full cache rebuild
• Validate all keys exist in Redis
• Monitor Redis memory and CPU
• Monitor application logs for errors
• Verify cache hit rates improve
• Plan rollback if issues detected

---

Next Steps: Start with Phase 1 (Schema updates) and work through sequentially.