Prisma 7 + NestJS 11 + PostgreSQL 18 + pgvector: ORM과 벡터 검색의 실전 통합 전략

// src/common/prisma/prisma.service.ts
import { PrismaPg } from '@prisma/adapter-pg';
import { PrismaClient } from '../../generated/prisma/client.js';

@Injectable()
export class PrismaService
  extends PrismaClient
  implements OnModuleInit, OnModuleDestroy
{
  constructor() {
    super({
      // Prisma 7: Node.js pg 드라이버를 직접 사용
      adapter: new PrismaPg({
        connectionString: process.env.DATABASE_URL,
      }),
    });
  }

  async onModuleInit() {
    await this.$connect();
  }

  async onModuleDestroy() {
    await this.$disconnect();
  }
}

// schema.prisma
generator client {
  provider     = "prisma-client"
  output       = "../src/generated/prisma"
  moduleFormat = "cjs"
}

datasource db {
  provider = "postgresql"
}

// package.json
{
  "scripts": {
    "build": "prisma generate && nest build"
  }
}

// package.json — Jest 설정
{
  "jest": {
    "moduleNameMapper": {
      "^(\\.{1,2}/.*)\\.js$": "$1"
    }
  }
}

#!/usr/bin/env bash
# scripts/start_dev.sh
set -e

bash scripts/ensure_db.sh
bash scripts/ensure_redis.sh

cd backend
# deploy, not dev — pgvector 인덱스 드리프트 회피
npx prisma migrate deploy

pnpm run start:dev

-- 20260312100000_add_pgvector_embedding/migration.sql
CREATE EXTENSION IF NOT EXISTS vector;

ALTER TABLE "session_memories" ADD COLUMN "embedding" vector(512);

-- HNSW 인덱스 — 코사인 유사도 검색용
CREATE INDEX "session_memories_embedding_idx"
  ON "session_memories" USING hnsw ("embedding" vector_cosine_ops);

-- PostgreSQL 18 — UUIDv7 네이티브 지원
-- 기존: gen_random_uuid() (v4, 랜덤)
-- 변경: uuidv7() (v7, 타임스탬프 정렬)
ALTER TABLE chat_messages
  ALTER COLUMN id SET DEFAULT uuidv7();

// src/common/prisma/prisma.module.ts
@Global()
@Module({
  providers: [PrismaService],
  exports: [PrismaService],
})
export class PrismaModule {}

// src/app.module.ts — 19개 모듈 등록
@Module({
  imports: [
    SentryModule.forRoot(),
    ConfigModule.forRoot({ isGlobal: true }),
    PrismaModule,       // @Global — 전체 앱에서 PrismaService 주입 가능
    RedisModule,        // @Global — 전체 앱에서 RedisService 주입 가능
    RateLimitModule,
    EncryptionModule,
    StorageModule,
    AiClientModule,
    AuthModule,
    StoriesModule,
    SessionsModule,
    ChatModule,
    CharactersModule,
    // ... 나머지 피처 모듈들
  ],
})
export class AppModule implements NestModule {
  configure(consumer: MiddlewareConsumer) {
    consumer.apply(IpRateLimitMiddleware).forRoutes('*');
  }
}

// src/gems/gems.service.ts — 인터랙티브 트랜잭션
async consume(userId: string, reason: GemTransactionReason) {
  const cost = GEM_COST[reason];

  return this.prisma.$transaction(async (tx) => {
    // 원자적 잔액 차감 — race condition 방지를 위해 raw SQL 사용
    const result = await tx.$queryRawUnsafe<{ balance: number }[]>(
      'UPDATE gem_wallets SET balance = balance - $1, updated_at = NOW() WHERE user_id = $2 AND balance >= $1 RETURNING balance',
      cost,
      userId,
    );

    if (result.length === 0) {
      throw new HttpException('젬이 부족합니다', HttpStatus.PAYMENT_REQUIRED);
    }

    // 거래 기록 생성
    await tx.gemTransaction.create({
      data: {
        walletId: wallet.id,
        type: GemTransactionType.CONSUME,
        amount: -cost,
        balanceAfter: result[0].balance,
        reason,
      },
    });

    return { balance: result[0].balance };
  });
}

// src/ai-pipeline/services/memory.service.ts — 배열 트랜잭션
const [longTermMemory] = await this.prisma.$transaction([
  this.prisma.sessionMemory.create({
    data: { sessionId, type: 'long_term', summary: encryptedSummary, /* ... */ },
  }),
  this.prisma.sessionMemory.deleteMany({
    where: { id: { in: oldestIds } },
  }),
]);

// schema.prisma — pgvector 컬럼 선언
model SessionMemory {
  id                 String   @id @default(uuid())
  sessionId          String   @map("session_id")
  summary            String   @db.Text
  embedding          Unsupported("vector(512)")?
  searchVector       Unsupported("tsvector")? @map("search_vector")
  // ...

  @@map("session_memories")
}

// src/ai-pipeline/services/memory.service.ts — 벡터 임베딩 저장
const embedding = await this.generateEmbedding(parsed.summary);
if (embedding) {
  const vectorStr = `[${embedding.join(',')}]`; // "[0.123, -0.456, ...]"
  await this.prisma.$executeRawUnsafe(
    `UPDATE session_memories SET embedding = $1::vector WHERE id = $2`,
    vectorStr,
    memory.id,
  );
}

// src/ai-pipeline/services/memory-retrieval.service.ts — 시맨틱 검색
private async getVectorResults(
  sessionIds: string[],
  vectorStr: string,
  limit: number,
  characterName?: string,
): Promise<MemoryResult[]> {
  // 코사인 유사도 * 시간 감쇠 (반감기 1일)
  const scoreSql =
    `(1 - (embedding <=> $2::vector)) * ` +
    `EXP(-0.693 * EXTRACT(EPOCH FROM (NOW() - created_at)) / 86400.0)`;

  const rows: MemoryResult[] = await this.prisma.$queryRawUnsafe(
    `SELECT id, summary,
            involved_characters AS "involvedCharacters",
            key_details AS "keyDetails",
            created_at AS "createdAt"
     FROM session_memories
     WHERE session_id = ANY($1::text[])
     ORDER BY ${scoreSql} DESC
     LIMIT $3`,
    sessionIds,
    vectorStr,
    limit,
  );
  return this.decryptMemoryResults(rows);
}

// RRF 병합 — 두 랭킹 리스트를 하나로
static mergeByRRF(
  vectorResults: MemoryResult[],
  textResults: MemoryResult[],
  limit: number,
  k = 60, // RRF 상수
): MemoryResult[] {
  const scoreMap = new Map<string, { score: number; item: MemoryResult }>();

  for (let i = 0; i < vectorResults.length; i++) {
    scoreMap.set(vectorResults[i].id, {
      score: 1 / (k + i),
      item: vectorResults[i],
    });
  }

  for (let i = 0; i < textResults.length; i++) {
    const existing = scoreMap.get(textResults[i].id);
    if (existing) {
      existing.score += 1 / (k + i); // 양쪽에 등장하면 점수 합산
    } else {
      scoreMap.set(textResults[i].id, {
        score: 1 / (k + i),
        item: textResults[i],
      });
    }
  }

  return Array.from(scoreMap.values())
    .sort((a, b) => b.score - a.score)
    .slice(0, limit)
    .map((e) => e.item);
}

model Character {
  persona       Json?  // { personality, speaking_style, speech_examples[] }
  identity      Json?  // { backstory, core_traits[], motivation, flaw, archetype }
  emotionConfig Json?  @map("emotion_config")
  safety        Json?  // { never_do[], always_do[], escalation_response }
}

model PlaySession {
  stateJson     Json?  @map("state_json") // 이벤트 진행 상태, 플래그 등
}

model ChatMessage {
  affinitySnapshot Json? @map("affinity_snapshot") // 되감기용 호감도 스냅샷
}

enum CharacterRole {
  MAIN_LOVE
  SUB_LOVE
  RIVAL
  FRIEND
  ANTAGONIST
  NARRATOR
  SUPPORT
}

enum GemTransactionReason {
  PURCHASE
  SUBSCRIPTION_MONTHLY
  REWIND
  ENERGY_CHARGE
  AFFINITY_BOOST
  STORY_UNLOCK
  // ... 14개 사유
}

model StoryCharacter {
  storyId         String @map("story_id")
  characterId     String @map("character_id")
  initialAffinity Int    @default(15) @map("initial_affinity")

  @@map("story_characters")
}

model StoryCharacter {
  // 이 스토리에서의 역할
  role               CharacterRole
  initialAffinity    Int           @default(15)
  storyContext       Json?         // { secret, arc, relationships[] }
  affinityThresholds Json?         // { cold, wary, neutral, warm, intimate }

  @@unique([storyId, characterId])
}