DEV Community: 我妻良樹

How I Implemented 71 Files with Zero Rework Using cc-sdd

我妻良樹 — Fri, 28 Nov 2025 12:20:23 +0000

Key Takeaways

I tried cc-sdd (Claude Code Spec-Driven Development) for the first time.

gotalab / cc-sdd

Spec-driven development (SDD) for your team's workflow. Kiro style commands that enforce structured requirements→design→tasks workflow and steering, transforming how you build with AI. Support Claude Code, Codex, Opencode, Cursor, Github Copilot, Gemini CLI and Windsurf.

cc-sdd: Spec-driven development for your team's workflow

Transform AI coding agents into production-ready spec-driven development

One command. Hours instead of weeks. Requirements → Design → Tasks → Implementation.

👻 Kiro-inspired — Similar Spec-Driven, AI-DLC style as Kiro IDE, so existing Kiro specs remain compatible and portable.

Stop losing 70% of development time to meetings, documentation ceremonies, and scattered context. cc-sdd brings structured AI-DLC (AI-Driven Development Lifecycle) and Spec-Driven Development to Claude Code, Cursor, Gemini CLI, Codex CLI, GitHub Copilot, Qwen Code, OpenCode, and Windsurf.

What you get:

✅ Spec-first guarantees — Approve requirements/design upfront, then AI implements exactly as specified
✅ Parallel execution ready — Tasks decomposed for concurrent implementation with dependency tracking
✅ Team-aligned templates — Customize once, all agents output docs that fit your approval process
✅ Project Memory — AI remembers your architecture, patterns, and standards across sessions
✅ 8 agents, unified…

View on GitHub

The bottom line: I was able to deliver high-quality implementation with virtually no rework. For this user management feature implementation, the changes spanned 71 files (about 30 of which were specification Markdown documents), and ultimately there were almost no major setbacks.

The defining characteristic of cc-sdd is that you follow a predetermined sequence. You progress through requirements → design → task breakdown, with humans deeply involved in refining each phase before implementation. Once the content is finalized, the tool determines the execution order, and you simply follow along with implementation tasks.

What I found particularly valuable was discovering gaps during the initial requirements phase. When generating EARS-format requirements, I had several "oh, I didn't consider this condition" moments.
Additionally, it precisely draws the line on scope—clearly stating what won't be developed this time (e.g., two-factor authentication, user profile image management, etc.) and considering both the current scope and future development items. I found this really helpful.

On the other hand, compared to vibe-coding (the style of trial-and-error development), cc-sdd isn't a try-and-error approach, so it's not ideal for building while testing. It's best suited for tackling large chunks of work, while small iterative changes have a different flavor.

Also, it does take time for review and approval. Each phase requires confirming and approving generated documents. However, with practice, there seem to be many optimization opportunities—potentially reaching a point where you can hand off everything after requirements and design are approved. I want to get more comfortable with this going forward.

Technology Stack

cc-sdd v2.x (kiro commands)
Claude Code - AI pair programming tool
Next.js 15 (App Router) + TypeScript
Prisma ORM + PostgreSQL RLS
NextAuth.js 4.x - Authentication
shadcn/ui + Radix UI - UI components
Zod - Validation

What is cc-sdd?

cc-sdd (Claude Code Spec-Driven Development) enables spec-driven development using AI coding agents. Unlike the traditional "write working code first, then adjust" approach, it follows a requirements → design → task breakdown → implementation sequence.

/kiro:spec-init
→ /kiro:spec-requirements
→ /kiro:spec-design
→ /kiro:spec-tasks
→ /kiro:spec-impl

Key features:

Spec-first guarantee: Requirements and design approved before implementation
Parallel execution support: Track task dependencies while enabling concurrent execution
Team-unified templates: Custom templates shared across all agents
Project memory: Architecture and patterns remembered across sessions

Implementation Flow

Here's how the user management feature development progressed.

Phase 0: Setup

Started with cc-sdd installation.

npx cc-sdd@latest --claude --lang en

Then executed /kiro:steering to set up project information, sharing the tech stack, directory structure, and coding conventions with the AI.

Phase 1: Requirements Definition

/kiro:spec-init user-management
/kiro:spec-requirements user-management

This phase generated EARS (Easy Approach to Requirements Syntax) format requirements. Seven main requirements were defined:

User Registration - Form validation, password complexity, success notification
User List & Search - Pagination, search/filter/sort
Role & Permission Management - ADMIN/OPERATOR/VIEWER, last admin protection
User Edit & Delete - Info updates, soft delete, self-deletion prevention
Session & Security - JWT, 7-day expiry
Multi-tenant Isolation - Prisma RLS, automatic organizationId
UI/UX & Accessibility - Responsive, toast notifications, keyboard nav

The key here was the requirements approval process. While reviewing generated requirements, I caught oversights like "prevent users from deleting themselves" and "prevent demoting the last admin."

Phase 2: Design

/kiro:spec-design user-management

This command generated a 1,015-line detailed design document including:

System flows (Mermaid diagrams)
Component design
Data models (logical & physical)
Error handling strategy
Testing strategy
Security considerations

Thanks to the design doc, the architectural overview was crystal clear before entering implementation.

Phase 3: Task Breakdown

/kiro:spec-tasks user-management

This generated an implementation plan with 6 main sections and 27 subtasks. Particularly useful:

Parallel-executable task identification: Phase 1 tasks 1.1, 1.2, 2.1 can run in parallel
Requirements traceability: Each task clearly maps to requirements

### Recommended Task Execution Order

#### Phase 1: Foundation Setup (Parallelizable)
- Tasks 1.1, 1.2, 2.1 can run in parallel

#### Phase 2: Backend Implementation (Parallelizable)
- Tasks 3.1, 3.2, 3.3, 3.4 can run in parallel (independent API interfaces)

Phase 4: Implementation

/kiro:spec-impl user-management [task-numbers]

During implementation, tasks were executed sequentially following dependencies. Each commit message includes which task was completed and which requirements were addressed.

feat: implement user validation schemas with comprehensive tests

Task 1.1 completed: Zod validation schema with password complexity

Requirements: 1.4, 1.6, 7.8

cc-sdd vs Vibe-Coding Comparison

Aspect	cc-sdd	Vibe-Coding
Best for	Large-scale, planned development	Small-scale, exploratory development
Preparation	Heavy (requirements → design → tasks)	Light
Rework	Minimal	Tends to increase
Review overhead	Required per phase	Ad-hoc
Documentation	Auto-generated	Manual
Learning curve	Moderate	Low
Team adoption	Easy (standardized)	Tends toward individual styles

When to Choose Which

cc-sdd works well for:

New feature development (especially multi-file changes)
Team development (standardized process)
Features requiring future spec reference
Quality-focused production implementations

Vibe-coding works well for:

Prototyping & validation
Small bug fixes
Solo short-term development
Exploratory work with unclear specs

Considerations for Team Adoption

Benefits

Quality standardization: Requirements → design → implementation order enforced
Auto-generated documentation: Requirements, design docs, task lists remain as artifacts
Efficient code reviews: Clear mapping between changes and requirements
Improved onboarding: New members can understand features by reading specs

Considerations

Learning curve: Understanding command systems and workflows required
Increased review overhead: Time needed to confirm/approve outputs per phase
Flexibility trade-off: Spec changes require design/task regeneration

Suggested Adoption Steps

Individual trial: Try one feature personally (like this article)
Team sharing: Share experience with team, gather feedback
Template customization: Adjust for project-specific requirements
Gradual rollout: Apply to new feature development, expand scope gradually

Lessons Learned

Unexpected Pitfalls

Managed DB service constraints can impact testing strategies
Parallel branch development leads to conflict resolution work
Beyond generated tasks, PR review feedback still needs addressing

Useful Insights for the Future

Hands-off after spec approval: Once design and task breakdown complete, implementation is relatively automated
Requirements traceability: Tracking which requirements each change addresses is valuable for auditing and documentation
Parallel task execution: Running independent tasks in parallel improves efficiency

Future Experiments

Template customization (reflecting project-specific patterns)
Multi-person parallel implementation (distributing tasks for concurrent progress)
Leveraging /kiro:validate-impl for implementation verification

Conclusion

cc-sdd shifts how we use AI coding tools from "just have it write something" to "progress consistently from requirements through design to implementation."

For this user management feature spanning 71 files, we maintained quality while minimizing rework. The biggest benefits were discovering gaps during the requirements phase and having generated documents available for future reference.

However, compared to vibe-coding, the preparation and review overhead increases, making it unsuitable for small changes. It's a tool that shines when "progressing large chunks systematically."

For those considering team adoption, I recommend starting with one feature personally. Based on that experience, you can determine if it fits your team's development style.

This article simplifies actual development experience. Project-specific information has been generalized.

Related Technologies: cc-sdd, Claude Code, Next.js 15, TypeScript, Prisma ORM, NextAuth.js, PostgreSQL RLS, Spec-Driven Development

References:

cc-sdd GitHub

Author: 91works Development Team

Fixing SQL Injection Vulnerabilities to Strengthen Security

我妻良樹 — Wed, 26 Nov 2025 08:25:42 +0000

Overview

While implementing full-text search functionality, we discovered an SQL injection vulnerability and fixed it by migrating to Prisma's parameterized queries. This article explains the dangers of $queryRawUnsafe, best practices for secure query implementation, and improvements to error handling.

Tech Stack

Prisma ORM (v5.x)
TypeScript (v5.x)
PostgreSQL (v14+)
Node.js (v20 LTS)
Security tools: OWASP ZAP, Snyk

Background & Challenges

Discovery of the Vulnerability

In the initial implementation of full-text search functionality, we were using $queryRawUnsafe:

// Red flag: Vulnerable code (before)
export async function findManyWithFullTextSearch(params: {
  keywords: string[];
  // ...
}) {
  const searchQuery = keywords.join(' & ');

  // User input is embedded directly in the query!
  const results = await prisma.$queryRawUnsafe(`
    SELECT e.*
    FROM emails e
    WHERE e.search_vector @@ websearch_to_tsquery('simple', '${searchQuery}')
    ORDER BY ts_rank(e.search_vector, websearch_to_tsquery('simple', '${searchQuery}')) DESC
    LIMIT ${take} OFFSET ${skip}
  `);

  return results;
}

Problems Identified

SQL Injection Risk
- User input was not properly escaped
- Malicious input could manipulate the database
Application crashes from tsquery syntax errors
- Searches containing special characters (', &, |, etc.) caused errors
- Insufficient error handling
Flagged in security review
- Use of $queryRawUnsafe is not recommended
- Cannot benefit from Prisma's type safety

Concrete Attack Example

If a malicious user executed a search like this:

// Attack example
const maliciousInput = "'; DROP TABLE emails; --";
// Generated SQL:
// WHERE e.search_vector @@ websearch_to_tsquery('simple', ''; DROP TABLE emails; --')

Fortunately, PostgreSQL permission settings prevented actual damage, but a fundamental fix was needed.

Solution

1. Migration to Prisma.sql Parameterized Queries

We changed from $queryRawUnsafe to parameterized queries using Prisma.sql:

// Green flag: Secure code (after)
import { Prisma } from '@prisma/client';

export async function findManyWithFullTextSearch(params: {
  keywords: string[];
  accountId: string;
  category?: string;
  skip?: number;
  take?: number;
}) {
  const { keywords, accountId, category, skip = 0, take = 50 } = params;

  // Join keywords with AND (Prisma automatically escapes)
  const searchQuery = keywords.join(' & ');

  try {
    const results = await prisma.$queryRaw`
      SELECT e.*
      FROM emails e
      WHERE e.search_vector @@ websearch_to_tsquery('simple', ${searchQuery})
        AND e."accountId" = ${accountId}
        ${category ? Prisma.sql`AND EXISTS (
          SELECT 1 FROM "EmailClassificationResult" ecr
          WHERE ecr."emailId" = e.id AND ecr.category = ${category}
          LIMIT 1
        )` : Prisma.empty}
      ORDER BY ts_rank(e.search_vector, websearch_to_tsquery('simple', ${searchQuery})) DESC
      LIMIT ${take} OFFSET ${skip}
    `;

    return results;
  } catch (error) {
    // Error handling (described later)
    console.error('Full-text search error:', error);
    return [];
  }
}

Key Points:

Use template literal syntax (Prisma.sql`...`)
Parameters are embedded with ${}, Prisma auto-escapes them
Use Prisma.empty for conditional branching

2. Handling tsquery Syntax Errors

Properly catch PostgreSQL tsquery syntax errors (error code 42601):

export async function findManyWithFullTextSearch(params: SearchParams) {
  try {
    const results = await prisma.$queryRaw`
      SELECT e.*
      FROM emails e
      WHERE e.search_vector @@ websearch_to_tsquery('simple', ${searchQuery})
      -- ...
    `;

    return results as Email[];
  } catch (error) {
    // Return empty array for PostgreSQL syntax errors
    if (error instanceof Error && 'code' in error && error.code === '42601') {
      console.warn('Invalid tsquery syntax, returning empty results:', searchQuery);
      return [];
    }

    // Re-throw other errors
    throw error;
  }
}

Error Handling Policy:

Syntax errors: Return empty array to keep application running
Other errors: Re-throw for upstream handling
Log output enables problem tracking

3. Character Limit for bodyText

Limit body text to 10,000 characters to avoid exceeding tsvector size limit (1MB):

// Migration
CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS trigger AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('simple', COALESCE(NEW.subject, '')), 'A') ||
    setweight(to_tsvector('simple', COALESCE(NEW."fromName", '')), 'B') ||
    setweight(to_tsvector('simple', COALESCE(NEW."fromAddress", '')), 'C') ||
    setweight(to_tsvector('simple',
      LEFT(COALESCE(NEW."bodyText", ''), 10000)), 'D');  -- 10,000 character limit
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

4. Securing the Backfill Script

The existing data update process was also secured using CTEs (Common Table Expressions):

async function backfillSearchVector() {
  const BATCH_SIZE = 1000;
  const totalCount = await prisma.email.count();
  let processedCount = 0;

  console.log(`Starting backfill for ${totalCount} emails...`);

  for (let offset = 0; offset < totalCount; offset += BATCH_SIZE) {
    try {
      // Safe bulk update using CTE
      const result = await prisma.$executeRaw`
        WITH batch AS (
          SELECT id, subject, "fromName", "fromAddress",
                 LEFT("bodyText", 10000) as truncated_body
          FROM emails
          ORDER BY id
          LIMIT ${BATCH_SIZE} OFFSET ${offset}
        )
        UPDATE emails
        SET search_vector =
          setweight(to_tsvector('simple', COALESCE(batch.subject, '')), 'A') ||
          setweight(to_tsvector('simple', COALESCE(batch."fromName", '')), 'B') ||
          setweight(to_tsvector('simple', COALESCE(batch."fromAddress", '')), 'C') ||
          setweight(to_tsvector('simple', COALESCE(batch.truncated_body, '')), 'D')
        FROM batch
        WHERE emails.id = batch.id
      `;

      processedCount += result;
      console.log(`Progress: ${processedCount}/${totalCount} (${Math.round(processedCount/totalCount * 100)}%)`);
    } catch (error) {
      console.error(`Error processing batch at offset ${offset}:`, error);
      // Continue processing even if an error occurs
    }
  }

  console.log(`Backfill completed: ${processedCount} emails processed`);
}

Technical Details

How Prisma.sql Ensures Safety

Prisma.sql internally performs the following:

Parameter Escaping

   // User input
   const input = "'; DROP TABLE emails; --";

   // Processed by Prisma
   // -> Safely bound as a parameter
   // -> SQL injection is impossible

Type Safety Guarantee

   // TypeScript type checking is effective
   const accountId: string = "acc_123";
   const take: number = 50;

   // Compilation error if types don't match
   // Example: The following causes a compilation error
   const invalidTake: string = "50";
   await prisma.$queryRaw`
     SELECT * FROM emails
     LIMIT ${invalidTake}  // Error: Type 'string' is not assignable to type 'number'
   `;

Use of PreparedStatements
- Parameter binding at the database level
- Separation of SQL parsing and execution
- Performance improvement through execution plan caching

$queryRawUnsafe vs Prisma.sql

Item	$queryRawUnsafe	Prisma.sql
Security	Bad: Vulnerability risk	Good: Safe
Type Safety	Bad: None	Good: Yes
Performance	Equivalent	Equivalent to slightly faster*
PreparedStatement	Bad: None	Good: Yes
Use Case	Not recommended	Recommended

*Performance improves with repeated executions due to PreparedStatement caching

Performance Benchmark

Benchmark results from actual environment:

// Benchmark code
import { performance } from 'perf_hooks';

async function benchmark() {
  const iterations = 1000;
  const keywords = ['typescript', 'react', 'nextjs'];

  // $queryRawUnsafe (not recommended)
  const unsafeStart = performance.now();
  for (let i = 0; i < iterations; i++) {
    await prisma.$queryRawUnsafe(
      `SELECT * FROM emails WHERE subject LIKE '%${keywords[0]}%' LIMIT 10`
    );
  }
  const unsafeTime = performance.now() - unsafeStart;

  // Prisma.sql (recommended)
  const safeStart = performance.now();
  for (let i = 0; i < iterations; i++) {
    await prisma.$queryRaw`
      SELECT * FROM emails WHERE subject LIKE ${`%${keywords[0]}%`} LIMIT 10
    `;
  }
  const safeTime = performance.now() - safeStart;

  console.log('Results:');
  console.log(`$queryRawUnsafe: ${unsafeTime.toFixed(2)}ms`);
  console.log(`Prisma.sql: ${safeTime.toFixed(2)}ms`);
  console.log(`Performance improvement: ${((unsafeTime - safeTime) / unsafeTime * 100).toFixed(2)}%`);
}

Results (measured on a table with 100,000 records):

$queryRawUnsafe: 3245.67ms
Prisma.sql: 2987.34ms
Performance improvement: 7.96%

Approximately 8% performance improvement was confirmed due to PreparedStatement caching.

Conditional Branching Implementation Patterns

Prisma.empty is useful for building dynamic queries with Prisma.sql:

// Pattern 1: Simple conditional branching
const query = Prisma.sql`
  SELECT * FROM emails
  WHERE accountId = ${accId}
  ${category ? Prisma.sql`AND category = ${category}` : Prisma.empty}
`;

// Pattern 2: Complex conditional branching
const conditions = [];
if (category) {
  conditions.push(Prisma.sql`category = ${category}`);
}
if (minAge) {
  conditions.push(Prisma.sql`age >= ${minAge}`);
}

const query = Prisma.sql`
  SELECT * FROM users
  WHERE ${Prisma.join(conditions, ' AND ')}
`;

Operational Considerations

Implementing Audit Logs

Implementing audit logs is crucial for tracking security incidents:

import winston from 'winston';

// Logger configuration for audit logs
const auditLogger = winston.createLogger({
  level: 'info',
  format: winston.format.combine(
    winston.format.timestamp(),
    winston.format.json()
  ),
  transports: [
    new winston.transports.File({ filename: 'audit.log' }),
    // In production, send to log services like CloudWatch
  ]
});

// Recording security events
export async function logSecurityEvent(event: {
  type: 'SQL_INJECTION_ATTEMPT' | 'INVALID_QUERY' | 'UNAUTHORIZED_ACCESS';
  userId?: string;
  query?: string;
  ip?: string;
  details?: any;
}) {
  auditLogger.warn('Security Event', {
    ...event,
    timestamp: new Date().toISOString(),
    environment: process.env.NODE_ENV
  });

  // Send alert for critical events
  if (event.type === 'SQL_INJECTION_ATTEMPT') {
    await sendSecurityAlert(event);
  }
}

// Usage example
export async function findManyWithFullTextSearch(params: SearchParams) {
  const { keywords, accountId } = params;

  // Detection of suspicious patterns
  const suspiciousPatterns = [
    /(\-\-|\/\*|\*\/|xp_|sp_|exec|execute|drop|create|alter|insert|update|delete)/i,
    /'.*or.*'='|".*or.*"="/i
  ];

  if (suspiciousPatterns.some(pattern => pattern.test(keywords.join(' ')))) {
    await logSecurityEvent({
      type: 'SQL_INJECTION_ATTEMPT',
      userId: accountId,
      query: keywords.join(' '),
      ip: req?.ip
    });
  }

  // Continue with normal processing...
}

CSP Header Configuration

Provide an additional layer of protection with Content Security Policy (CSP) headers:

// For Next.js (next.config.js)
module.exports = {
  async headers() {
    return [
      {
        source: '/:path*',
        headers: [
          {
            key: 'Content-Security-Policy',
            value: [
              "default-src 'self'",
              "script-src 'self' 'unsafe-inline' 'unsafe-eval'",
              "style-src 'self' 'unsafe-inline'",
              "img-src 'self' data: https:",
              "font-src 'self' data:",
              "connect-src 'self'",
              "frame-ancestors 'none'",
              "base-uri 'self'",
              "form-action 'self'"
            ].join('; ')
          },
          {
            key: 'X-Frame-Options',
            value: 'DENY'
          },
          {
            key: 'X-Content-Type-Options',
            value: 'nosniff'
          },
          {
            key: 'X-XSS-Protection',
            value: '1; mode=block'
          }
        ]
      }
    ];
  }
};

Automating Security Scans

Integrate security checks into the CI/CD pipeline:

# .github/workflows/security.yml
name: Security Scan

on:
  pull_request:
  push:
    branches: [main]

jobs:
  security:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3

      - name: Run Snyk Security Scan
        uses: snyk/actions/node@master
        env:
          SNYK_TOKEN: ${{ secrets.SNYK_TOKEN }}
        with:
          args: --severity-threshold=high

      - name: Run SQLMap Test (staging only)
        if: github.ref == 'refs/heads/staging'
        run: |
          # Automated testing using SQLMap
          sqlmap -u "https://staging.example.com/api/search?q=test" \
                 --batch --level=2 --risk=2 \
                 --output-dir=./sqlmap-results

      - name: Check for hardcoded credentials
        run: |
          npx secretlint "**/*"

Testing Strategy

Implementing Unit Tests

Add security-related unit tests:

// __tests__/security.test.ts
import { describe, it, expect, vi, beforeEach } from 'vitest';
import { findManyWithFullTextSearch } from '../search';
import { prisma } from '../prisma';

describe('SQL Injection Prevention', () => {
  beforeEach(() => {
    vi.clearAllMocks();
  });

  it('should safely handle SQL injection attempts', async () => {
    const maliciousInputs = [
      "'; DROP TABLE emails; --",
      "1' OR '1'='1",
      "admin'--",
      "' OR 1=1--",
      "'; DELETE FROM emails WHERE '1'='1"
    ];

    for (const input of maliciousInputs) {
      // Verify that Prisma.sql performs escape processing
      const spy = vi.spyOn(prisma, '$queryRaw');

      await findManyWithFullTextSearch({
        keywords: [input],
        accountId: 'test_account'
      });

      // Verify parameters are bound
      expect(spy).toHaveBeenCalled();
      const call = spy.mock.calls[0];

      // Verify it's a Prisma.Sql object
      expect(call[0]).toHaveProperty('sql');
      expect(call[0]).toHaveProperty('values');

      // Verify SQL injection is neutralized
      expect(call[0].sql).not.toContain('DROP TABLE');
      expect(call[0].sql).not.toContain('DELETE FROM');
    }
  });

  it('should handle special characters in search queries', async () => {
    const specialCharInputs = [
      "test's",
      'test"quote',
      'test & test',
      'test | test',
      'test\\backslash'
    ];

    for (const input of specialCharInputs) {
      // Verify processing without errors
      await expect(
        findManyWithFullTextSearch({
          keywords: [input],
          accountId: 'test_account'
        })
      ).resolves.not.toThrow();
    }
  });

  it('should validate input types', async () => {
    // Type checking test
    const invalidParams = {
      keywords: ['test'],
      accountId: 'test_account',
      take: 'not-a-number', // Type error
      skip: '0' // Type error
    };

    // Simulate TypeScript type error
    // @ts-expect-error - Intentional type error
    const result = await findManyWithFullTextSearch(invalidParams);

    // Verify no runtime error occurs
    expect(result).toBeDefined();
  });
});

describe('Error Handling', () => {
  it('should return empty array on tsquery syntax error', async () => {
    // Simulate PostgreSQL syntax error
    vi.spyOn(prisma, '$queryRaw').mockRejectedValueOnce(
      Object.assign(new Error('syntax error in tsquery'), {
        code: '42601'
      })
    );

    const result = await findManyWithFullTextSearch({
      keywords: ['invalid::syntax'],
      accountId: 'test_account'
    });

    expect(result).toEqual([]);
  });

  it('should log security events', async () => {
    const logSpy = vi.spyOn(console, 'warn');

    await findManyWithFullTextSearch({
      keywords: ["'; DROP TABLE emails; --"],
      accountId: 'test_account'
    });

    // Verify security event is logged
    expect(logSpy).toHaveBeenCalledWith(
      expect.stringContaining('Security Event')
    );
  });
});

Implementing Integration Tests

// __tests__/integration/search.test.ts
import { describe, it, expect, beforeAll, afterAll } from 'vitest';
import { createTestDatabase, destroyTestDatabase } from '../test-utils';

describe('Full-text Search Integration', () => {
  let testDb: any;

  beforeAll(async () => {
    testDb = await createTestDatabase();

    // Insert test data
    await testDb.email.createMany({
      data: [
        { subject: 'TypeScript tutorial', bodyText: 'Learn TypeScript...' },
        { subject: 'React hooks guide', bodyText: 'Understanding hooks...' },
        { subject: 'Next.js best practices', bodyText: 'Server components...' }
      ]
    });
  });

  afterAll(async () => {
    await destroyTestDatabase(testDb);
  });

  it('should perform secure full-text search', async () => {
    const results = await findManyWithFullTextSearch({
      keywords: ['TypeScript'],
      accountId: 'test_account'
    });

    expect(results).toHaveLength(1);
    expect(results[0].subject).toContain('TypeScript');
  });

  it('should handle concurrent searches safely', async () => {
    // Concurrent processing test
    const promises = Array.from({ length: 100 }, (_, i) =>
      findManyWithFullTextSearch({
        keywords: [`test${i}`],
        accountId: 'test_account'
      })
    );

    await expect(Promise.all(promises)).resolves.not.toThrow();
  });
});

Troubleshooting

Common Problems and Solutions

1. "ERROR: syntax error in tsquery" occurs

Symptom: Error occurs with search queries containing special characters

Cause: Use of characters reserved in PostgreSQL's tsquery syntax

Solution:

// Implement a function to escape special characters
function escapeSpecialChars(query: string): string {
  // Escape tsquery special characters
  return query
    .replace(/[&|!()':*]/g, ' ')  // Replace special chars with spaces
    .replace(/\s+/g, ' ')          // Consolidate consecutive spaces
    .trim();
}

// Usage example
const safeQuery = escapeSpecialChars(userInput);
const searchQuery = keywords.map(escapeSpecialChars).join(' & ');

2. Performance degradation

Symptom: Searches are slow with large amounts of data

Cause: Index is not being used, or inappropriate queries

Solution:

-- Check indexes
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'emails';

-- Check execution plan
EXPLAIN ANALYZE
SELECT * FROM emails
WHERE search_vector @@ websearch_to_tsquery('simple', 'test');

-- Rebuild index if necessary
REINDEX INDEX idx_emails_search_vector;

3. Memory usage increases

Symptom: Memory usage increases after long runtime

Cause: Prisma connection pool leak

Solution:

// Monitor connection pool
setInterval(async () => {
  const metrics = await prisma.$metrics.json();
  console.log('Connection pool metrics:', {
    activeConnections: metrics.counters.find(
      m => m.key === 'prisma_pool_connections_open'
    )?.value,
    idleConnections: metrics.counters.find(
      m => m.key === 'prisma_pool_connections_idle'
    )?.value
  });

  // Warn if threshold exceeded
  if (metrics.counters[0].value > 50) {
    console.warn('High connection count detected');
  }
}, 60000);

4. Connection errors after deployment

Symptom: "Can't reach database server" error

Solution:

// Implement retry logic
async function executeWithRetry<T>(
  fn: () => Promise<T>,
  maxRetries = 3,
  delay = 1000
): Promise<T> {
  for (let i = 0; i < maxRetries; i++) {
    try {
      return await fn();
    } catch (error) {
      if (i === maxRetries - 1) throw error;

      console.log(`Retry attempt ${i + 1}/${maxRetries}`);
      await new Promise(resolve => setTimeout(resolve, delay * (i + 1)));
    }
  }
  throw new Error('Max retries exceeded');
}

// Usage example
const results = await executeWithRetry(
  () => findManyWithFullTextSearch(params)
);

Lessons Learned

Unexpected Pitfalls

Error handling is still necessary with Prisma.sql
- PostgreSQL syntax errors can still occur even with parameterization
- Be careful with search queries containing special characters
Importance of CTEs
- Improved safety during batch updates
- Optimization by query planner
- Improved readability
Designing character limits
- Consider tsvector's 1MB limit
- Trade-offs with search performance

Knowledge for Future Use

Secure Query Implementation Checklist

   [ ] Use Prisma.sql
   [ ] Parameterize all user input
   [ ] Implement error handling
   [ ] Enable problem tracking with log output
   [ ] Conduct security review

Error Handling Best Practices

   try {
     // Database operation
   } catch (error) {
     // 1. Determine error type
     // 2. Select appropriate handling (retry/empty result/re-throw)
     // 3. Log output
     // 4. User feedback
   }

Incremental Migration Strategy

   Step 1: Identify vulnerabilities
   Step 2: Change to secure implementation
   Step 3: Secure existing data
   Step 4: Testing and review
   Step 5: Deployment and monitoring

Discovering Better Approaches

Before (string concatenation):

const query = `
  SELECT * FROM emails
  WHERE accountId = '${accountId}'
  ${category ? `AND category = '${category}'` : ''}
`;
await prisma.$queryRawUnsafe(query);

After (parameterization):

const query = Prisma.sql`
  SELECT * FROM emails
  WHERE accountId = ${accountId}
  ${category ? Prisma.sql`AND category = ${category}` : Prisma.empty}
`;
await prisma.$queryRaw(query);

Conclusion

SQL injection vulnerability is a classic but still serious security risk today. The key points in this fix were:

Leveraging Prisma's parameterized queries: Achieving both type safety and security
Appropriate error handling: Not compromising user experience
Incremental migration: Minimizing risk

In particular, even when using an ORM, extra care is needed when writing Raw SQL. Prisma's $queryRawUnsafe is convenient, but we recommend using Prisma.sql whenever possible, understanding the security risks involved.

We encourage readers to check their existing code for $queryRawUnsafe or SQL construction using string concatenation. If found, we recommend fixing them sooner rather than later.

The code presented in this article is a simplified version of actual production code. There are additional considerations for error handling and security checks in real implementations.

Related Technologies: Prisma ORM, PostgreSQL, TypeScript, SQL Injection Prevention, Security, Error Handling, Parameterized Queries

Author: 91works Development Team

How We Achieved Up to 71% Faster Email Search with PostgreSQL Full-Text Search

我妻良樹 — Tue, 25 Nov 2025 03:57:27 +0000

Overview

By migrating our email system's search functionality from traditional LIKE queries to PostgreSQL's full-text search, we achieved up to 71% performance improvement. This article details the implementation approach using GIN indexes and tsvector for 260,000 existing records, along with a non-blocking migration strategy.

Tech Stack

PostgreSQL (Full-Text Search)
Prisma ORM
TypeScript
tsvector / GIN Index
websearch_to_tsquery()

Background & Challenges

Existing Problems

Initially, the email search feature was implemented using simple LIKE queries:

// Traditional LIKE search (simplified)
const emails = await prisma.email.findMany({
  where: {
    OR: [
      { subject: { contains: keyword } },
      { fromName: { contains: keyword } },
      { bodyText: { contains: keyword } }
    ]
  }
});

However, as data volume exceeded 260,000 records, the following issues became apparent:

Performance degradation: Some multi-keyword searches took over 1.4 seconds
Scalability limits: Search time increased proportionally with data volume
Lack of search flexibility: Unable to implement weighting or relevance scoring

Specific Numbers

Java search: 1,458ms
Engineer search: 968ms
Project search: 655ms

These search times needed improvement.

Implementation Details

1. Database Schema Design

First, we added a search_vector column to the emails table. The key point is applying weights to subject, sender name, and body:

-- Add search_vector column
ALTER TABLE emails ADD COLUMN search_vector tsvector;

-- Create weighted trigger function
CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS trigger AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('simple', COALESCE(NEW.subject, '')), 'A') ||
    setweight(to_tsvector('simple', COALESCE(NEW."fromName", '')), 'B') ||
    setweight(to_tsvector('simple', COALESCE(NEW."fromAddress", '')), 'C') ||
    setweight(to_tsvector('simple',
      LEFT(COALESCE(NEW."bodyText", ''), 10000)), 'D');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER emails_search_vector_update
  BEFORE INSERT OR UPDATE ON emails
  FOR EACH ROW
  EXECUTE FUNCTION update_search_vector();

Weight Intent:

A (Subject): Most important information source
B (Sender Name): Second most important
C (Email Address): Supplementary information
D (Body): Overall context

2. Creating GIN Index

We used the CONCURRENTLY option to avoid table locks:

// GIN index creation script (simplified)
async function createIndex() {
  await prisma.$executeRawUnsafe(`
    CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_emails_search_vector
    ON emails USING GIN(search_vector)
  `);
}

Reasons for Using CONCURRENTLY:

Doesn't block existing traffic
Safe to run in production environments
Allows reads and writes during index creation

3. Backfilling Existing Data

We updated search_vector for 260,000 existing records using batch processing with error handling and retry logic:

const BATCH_SIZE = 1000;
const MAX_RETRIES = 3;
const RETRY_DELAY = 5000; // 5 seconds

async function backfillSearchVector() {
  const totalCount = await prisma.email.count();
  console.log(`Total emails to process: ${totalCount}`);

  let successCount = 0;
  let failureCount = 0;

  for (let offset = 0; offset < totalCount; offset += BATCH_SIZE) {
    let retryCount = 0;
    let success = false;

    while (retryCount < MAX_RETRIES && !success) {
      try {
        const startTime = Date.now();

        // Efficient update using CTE (Common Table Expression)
        await prisma.$executeRaw`
          WITH batch AS (
            SELECT id, subject, "fromName", "fromAddress", "bodyText"
            FROM emails
            ORDER BY id
            LIMIT ${BATCH_SIZE} OFFSET ${offset}
          )
          UPDATE emails
          SET search_vector =
            setweight(to_tsvector('simple', COALESCE(batch.subject, '')), 'A') ||
            setweight(to_tsvector('simple', COALESCE(batch."fromName", '')), 'B') ||
            setweight(to_tsvector('simple', COALESCE(batch."fromAddress", '')), 'C') ||
            setweight(to_tsvector('simple',
              LEFT(COALESCE(batch."bodyText", ''), 10000)), 'D')
          FROM batch
          WHERE emails.id = batch.id
        `;

        const elapsed = Date.now() - startTime;
        successCount += BATCH_SIZE;
        console.log(`✓ Processed ${offset + BATCH_SIZE}/${totalCount} (${elapsed}ms)`);
        success = true;

      } catch (error) {
        retryCount++;
        console.error(`✗ Batch failed (attempt ${retryCount}/${MAX_RETRIES}):`, error);

        if (retryCount < MAX_RETRIES) {
          console.log(`Retrying in ${RETRY_DELAY}ms...`);
          await new Promise(resolve => setTimeout(resolve, RETRY_DELAY));
        } else {
          failureCount += BATCH_SIZE;
          console.error(`✗ Batch permanently failed after ${MAX_RETRIES} attempts. Skipping...`);
        }
      }
    }
  }

  console.log(`\nBackfill complete:`);
  console.log(`- Success: ${successCount} records`);
  console.log(`- Failure: ${failureCount} records`);

  if (failureCount > 0) {
    throw new Error(`Backfill completed with ${failureCount} failures`);
  }
}

Processing Results:

Completed 260,000 records in approximately 53 minutes
Average 12 seconds per batch
Up to 3 retries on errors
Failed batches logged and skipped

4. Search Query Implementation

We implemented a safe search function using websearch_to_tsquery() with error handling:

export async function findManyWithFullTextSearch(params: {
  keywords: string[];
  accountId: string;
  category?: string;
  skip?: number;
  take?: number;
}): Promise<Email[]> {
  const { keywords, accountId, category, skip = 0, take = 50 } = params;

  // Input validation: Limit keyword count (DoS protection)
  if (keywords.length > 10) {
    throw new Error('Too many keywords. Maximum 10 keywords allowed.');
  }

  // Input validation: Limit keyword length
  const invalidKeywords = keywords.filter(k => k.length > 100);
  if (invalidKeywords.length > 0) {
    throw new Error('Keyword too long. Maximum 100 characters per keyword.');
  }

  // Convert keywords to tsquery format (AND conjunction)
  // SQL injection protection is automatic with Prisma.sql parameterization
  const searchQuery = keywords.join(' & ');

  try {
    const results = await prisma.$queryRaw<Email[]>`
      SELECT e.*
      FROM emails e
      WHERE e.search_vector @@ websearch_to_tsquery('simple', ${searchQuery})
        AND e."accountId" = ${accountId}
        ${category ? Prisma.sql`AND EXISTS (
          SELECT 1 FROM "EmailClassificationResult" ecr
          WHERE ecr."emailId" = e.id AND ecr.category = ${category}
          LIMIT 1
        )` : Prisma.empty}
      ORDER BY ts_rank(e.search_vector, websearch_to_tsquery('simple', ${searchQuery})) DESC
      LIMIT ${take} OFFSET ${skip}
    `;

    return results;

  } catch (error) {
    // Return empty array for PostgreSQL syntax error (error code 42601)
    if (error instanceof Error && 'code' in error && error.code === '42601') {
      console.warn('Invalid tsquery syntax, returning empty results:', searchQuery);
      return [];
    }

    // Log and rethrow other errors
    console.error('Full-text search error:', error);
    throw error;
  }
}

Key Points:

Security Measures:
- SQL injection prevention through Prisma.sql parameterization
- DoS attack prevention with keyword count and length limits
Error Handling:
- Returns empty array for tsquery syntax errors to continue application
- Properly logs and rethrows other errors
Query Optimization:
- @@ operator: Matching between tsvector and tsquery
- websearch_to_tsquery(): Supports natural search syntax (e.g., "Java AND Python")
- ts_rank(): Sorting by relevance score
- WHERE clause order optimization: search_vector → accountId → category

Performance Improvement Results

Search Time Comparison

Search Keyword	Before	After	Improvement
Java	1,458ms	596ms	59% faster
Engineer	968ms	279ms	71% faster
Project	655ms	203ms	69% faster

Why Such Significant Speed Improvements?

Index Scan Efficiency
- LIKE search: O(N) - Full table scan
- GIN index: O(log N) - Index scan only
Query Planner Optimization
- PostgreSQL query planner efficiently uses GIN index
- WHERE clause order optimized (high selectivity conditions first)
Weighted Relevance Scoring
- Prioritizes subject matches, improving user experience
- Natural ordering through ts_rank()

Lessons Learned

Unexpected Pitfalls

Importance of Character Limits
- tsvector has a 1MB limit
- Ensured safety by limiting bodyText to 10,000 characters
GIN Index Disk Usage
- Approximately 1.2GB index size for 260,000 records
- About 30-40% of original table size
- Consider tradeoff between storage cost and performance
Choosing 'simple' Configuration and Japanese Search Challenges
- For mixed Japanese-English text, 'simple' is more appropriate than language-specific settings ('english', 'japanese')
- Stemming processing is often unnecessary
- Comparison with Japanese Morphological Analysis:
  - MeCab + pg_bigm: Higher accuracy but complex setup
  - 'simple' setting: Easy setup but imperfect word boundary recognition
  - This project chose 'simple' prioritizing development speed
CONCURRENTLY Pitfalls
- Cannot be used within transactions
- Index creation took longer than expected (about 45 minutes for 260,000 records)
- Caution needed for re-execution on errors (partially created indexes may remain)

Knowledge for Future Use

Gradual Migration Strategy

   Step 1: Schema change (add search_vector column)
   Step 2: Trigger setup (new data auto-updates)
   Step 3: GIN index creation (CONCURRENTLY)
   Step 4: Backfill existing data
   Step 5: Application code switch

Performance Tuning Best Practices
- Verify execution plan with EXPLAIN ANALYZE
- Arrange WHERE clause in order of selectivity
- Add LIMIT 1 to EXISTS clause for early termination
Batch Processing Optimization
- Efficient bulk updates using CTE
- Ensure transparency through progress logging
- Error handling and retry logic

Better Approaches Discovered

Before (Multiple OR Conditions):

where: {
  OR: [
    { subject: { contains: 'Java' } },
    { fromName: { contains: 'Java' } },
    { bodyText: { contains: 'Java' } }
  ]
}

After (Full-Text Search):

where: {
  search_vector: {
    search: 'Java',
    mode: 'websearch'
  }
}

The implementation became simpler, faster, and more flexible.

Operational Considerations

Monitoring Setup

// Monitor index usage statistics
const indexStats = await prisma.$queryRaw`
  SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan as index_scans,
    idx_tup_read as tuples_read,
    idx_tup_fetch as tuples_fetched,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size
  FROM pg_stat_user_indexes
  WHERE indexname = 'idx_emails_search_vector'
`;

Alert Thresholds

Search response time: Warning at 500ms+, Alert at 1 second+
Index scan count: Warning below 80% of expected traffic
GIN index size: Warning when exceeding 30% of disk capacity

Log Output Design

// Structured log example
logger.info('full_text_search', {
  keywords: keywords.join(','),
  resultCount: results.length,
  duration: Date.now() - startTime,
  accountId,
  category
});

Test Strategy

Unit Tests

describe('findManyWithFullTextSearch', () => {
  it('should return emails matching single keyword', async () => {
    const results = await findManyWithFullTextSearch({
      keywords: ['Java'],
      accountId: 'acc_123'
    });

    expect(results).toHaveLength(10);
    expect(results[0].subject).toContain('Java');
  });

  it('should handle invalid tsquery syntax gracefully', async () => {
    const results = await findManyWithFullTextSearch({
      keywords: ['test & | invalid'],
      accountId: 'acc_123'
    });

    expect(results).toEqual([]); // Returns empty array instead of error
  });

  it('should reject too many keywords', async () => {
    await expect(
      findManyWithFullTextSearch({
        keywords: new Array(11).fill('test'),
        accountId: 'acc_123'
      })
    ).rejects.toThrow('Too many keywords');
  });
});

Performance Tests

describe('Full-text search performance', () => {
  it('should complete search within 500ms', async () => {
    const startTime = Date.now();

    await findManyWithFullTextSearch({
      keywords: ['engineer'],
      accountId: 'acc_123'
    });

    const duration = Date.now() - startTime;
    expect(duration).toBeLessThan(500);
  });
});

Troubleshooting

Common Problems and Solutions

Search returns 0 results
- Cause: search_vector might be null
- Check: SELECT COUNT(*) FROM emails WHERE search_vector IS NULL
- Solution: Re-run backfill script
Search is slow
- Cause: GIN index not being used
- Check: Verify execution plan with EXPLAIN ANALYZE
- Solution: Update index statistics with VACUUM ANALYZE emails
tsvector size limit error
- Cause: bodyText exceeds 10,000 characters
- Check: SELECT id, LENGTH("bodyText") FROM emails WHERE LENGTH("bodyText") > 10000
- Solution: Verify and apply character limit in trigger function

Debugging Methods

-- Check search_vector for specific email
SELECT
  id,
  subject,
  search_vector,
  ts_rank(search_vector, websearch_to_tsquery('simple', 'Java')) as rank
FROM emails
WHERE id = 'email_123';

-- Verify index is being used
EXPLAIN ANALYZE
SELECT * FROM emails
WHERE search_vector @@ websearch_to_tsquery('simple', 'Java')
LIMIT 10;

Conclusion

PostgreSQL's full-text search feature is a powerful tool that can achieve fast search even on large-scale data when properly implemented. The following points were key to success in this implementation:

Non-blocking migration: Minimized impact on production environment
Weighted search: Relevance scoring focused on user experience
Gradual migration: Risk-distributed implementation strategy

Particularly, the gradual migration using the CONCURRENTLY option and batch processing was very effective in achieving large-scale data migration without service interruption.

If your data volume is growing, we recommend considering full-text search implementation early. When you feel the limits of LIKE search, that's the time to migrate.

The code presented in this article is a simplified version of actual production code. Real implementations require additional considerations for error handling and security checks.

Related Technologies: PostgreSQL, Prisma ORM, TypeScript, Full-Text Search, GIN Index, tsvector, Performance Optimization, Database Design

Author: 91works Development Team

Improving AI Email Classification Accuracy Through Prompt Engineering

我妻良樹 — Fri, 21 Nov 2025 04:20:37 +0000

Improving AI Email Classification Accuracy Through Prompt Engineering

Overview

We resolved email misclassification issues in our email classification system, where project emails (PROJECT) and talent emails (TALENT) were being incorrectly categorized. This article describes how we improved the problem of personnel emails containing "project desired" being misclassified as projects, using Few-shot learning and clearer judgment criteria.

Tech Stack

OpenAI GPT-4 Turbo (gpt-4-1106-preview)
Claude 3 Opus (for comparison testing)
TypeScript (v5.x)
Prompt Engineering
Few-shot Learning
Natural Language Processing

Background & Challenges

Real Misclassification Examples

Our email classification AI was making incorrect judgments in cases like these:

Misclassification Case 1: Personnel information classified as PROJECT

Subject: [Personnel Information] Introduction of Mr./Ms. ○○
Body:
We would like to introduce the following candidate from Mr./Ms. ○○.

[Basic Information]
Name: Taro Yamada
Age: 35 years old
Skills: Java, Spring Boot
Desired rate: 600,000 yen/month
Project desired: Remote-friendly projects preferred

→ Misclassified as PROJECT based solely on "project desired" keyword

Misclassification Case 2: Unable to understand context

Subject: Re: Introduction of Engineer
Body:
We would like to introduce the following engineer.
Currently seeking new projects.

[Career]
- 5 years of development experience at major SI company
- Experience in machine learning projects with Python

→ Misclassified as PROJECT due to "seeking projects"

Root Causes

Keyword-based Judgment
- Classification based solely on the word "project"
- Not understanding context or subject
Ambiguous Judgment Criteria
- Unclear "who is providing what"
- Ambiguous subject of "introduction"
Insufficient Few-shot Examples
- Only covering typical cases
- Lacking learning from ambiguous patterns

Solution

1. Clarifying Classification Criteria

We added the perspective of "who is providing what" to the prompt:

// Improved prompt (simplified version)
const CLASSIFICATION_PROMPT = `
You are an email classification AI. Please classify emails into the following categories.

## Classification Criteria

**Most Important Point: Who is providing what?**

### PROJECT (Project Information)
- **Provider**: Client companies, sales representatives
- **Content**: Development projects, job postings, work requests
- **Definitive Keywords**:
  - "Project details", "Project information", "Job posting"
  - "Development member recruitment", "Candidates available"
- **Judgment Method**:
  - Email sender is providing the project
  - Recruiting engineers/personnel

### TALENT (Personnel Information)
- **Provider**: Staffing companies, sales representatives, agents
- **Content**: Engineer introductions, skill sheets
- **Definitive Keywords**:
  - "Personnel information", "Talent information", "Engineer information attached"
  - "Engineer introduction", "Skill sheet of Mr./Ms. ○○"
- **Judgment Method**:
  - Email sender is introducing personnel
  - Contains personal information such as age/gender
  - Even if "project desired" appears, if it's written as the person's preference, classify as TALENT

## Judgment for Ambiguous Cases

### Context Analysis for "Project Desired"
- "Mr./Ms. ○○ desires projects" → TALENT (person's preference)
- "Those who desire the following project" → PROJECT (recruitment condition)

### Confirming Subject of "Introduction"
- "Introducing engineer" → TALENT (introducing personnel)
- "Introducing project" → PROJECT (introducing project)

### Handling Age/Gender Information
- Personal age/gender listed → Almost certainly TALENT
- "Age unspecified" as recruitment condition → PROJECT
`;

2. Adding Few-shot Examples

We added Few-shot examples based on actual misclassification cases:

const FEW_SHOT_EXAMPLES = [
  // Existing examples 1, 2...

  // Example 3: TALENT email containing "project desired"
  {
    input: {
      subject: "[Personnel Information] Introduction of Taro Yamada",
      fromName: "Suzuki (ABC Corporation)",
      bodyText: `
        Thank you for your continued support.
        We are sending the following personnel information.

        [Basic Information]
        Name: Taro Yamada
        Age: 35 years old
        Gender: Male

        [Skills]
        - Java, Spring Boot 5 years
        - AWS, Docker 3 years

        [Desired Conditions]
        Rate: 600,000 yen/month
        Project desired: Remote-friendly projects
        Location: Within Tokyo

        We appreciate your consideration.
      `
    },
    output: {
      category: "TALENT",
      reasoning: "Staffing company introducing engineer (Taro Yamada). Contains personal information such as age and gender. 'Project desired' is listed as the person's preference, so classified as TALENT."
    }
  },

  // Example 4: TALENT email with "Sending engineer information"
  {
    input: {
      subject: "Sending Engineer Information",
      fromName: "Sato (XYZ Staffing Services)",
      bodyText: `
        Thank you for your work.
        Sending the following engineer information.

        [Profile]
        Name: Hanako Sato
        Age: 28 years old
        Nearest Station: Shinjuku Station

        [Experience]
        - React, TypeScript 3 years
        - Next.js, Tailwind CSS 2 years

        Currently available for immediate start.
        Please let us know if you're interested.
      `
    },
    output: {
      category: "TALENT",
      reasoning: "Staffing service company introducing engineer (Hanako Sato). Contains personal information (age, nearest station). Sender is in position of providing personnel, so TALENT."
    }
  },

  // Example 5: Confusing PROJECT email
  {
    input: {
      subject: "[Project Information] Web Application Development Member Recruitment",
      fromName: "Tanaka (Client Corporation)",
      bodyText: `
        Thank you for your support.
        We are recruiting development members for the following project.

        [Project Details]
        Content: E-commerce site renewal
        Period: January - June 2025 (6 months)
        Rate: 600,000-800,000 yen/month
        Location: Shibuya, Tokyo (remote available)

        [Required Skills]
        - React, TypeScript 3+ years
        - Team development experience

        [Desired Candidates]
        Immediately available
        Full-time 5 days/week available

        If you have interested engineers,
        we would appreciate introductions.
      `
    },
    output: {
      category: "PROJECT",
      reasoning: "Client company providing development project. 'Recruitment' and 'desired candidates' indicate seeking engineers. Sender is in position of providing project, so PROJECT."
    }
  }
];

3. Strengthening Judgment Logic

Added automatic classification based on age/gender information:

export async function classifyEmail(email: {
  subject: string;
  fromName: string;
  fromAddress: string;
  bodyText: string;
}): Promise<ClassificationResult> {
  // Preprocessing: Check obvious patterns
  const bodyLower = email.bodyText.toLowerCase();

  // Personal information patterns (age/gender) almost certainly indicate TALENT
  const personalInfoPatterns = [
    /年齢[：:]\s*\d{2}歳/,
    /性別[：:]\s*(男性|女性)/,
    /生年月日[：:]/,
    /氏名[：:]\s*[\p{Script=Han}\p{Script=Hiragana}\p{Script=Katakana}]+\s*様/u
  ];

  const hasPersonalInfo = personalInfoPatterns.some(pattern =>
    pattern.test(email.bodyText)
  );

  // Execute AI classification
  const aiResult = await callAIClassificationAPI({
    prompt: CLASSIFICATION_PROMPT,
    fewShotExamples: FEW_SHOT_EXAMPLES,
    email: email,
    hint: hasPersonalInfo ? 'Likely TALENT due to personal information' : undefined
  });

  return aiResult;
}

Test Results

Accuracy Before Improvement

Testing with 100 actual misclassified emails:

Accuracy: 72% (72 correct, 28 misclassified)
TALENT email misclassifications: 18 (personnel classified as projects)
PROJECT email misclassifications: 10 (projects classified as personnel)

Accuracy After Improvement

Retesting with the same 100 emails:

Accuracy: 98% (98 correct, 2 misclassified)
TALENT email misclassifications: 1 (extremely ambiguous content)
PROJECT email misclassifications: 1 (composite email containing both elements)

Large-scale Validation (1,000 emails)

Validation results with 1,000 production data emails:

Category	Before	After	Improvement
PROJECT	68% (272/400)	97% (388/400)	+29%
TALENT	75% (450/600)	98% (588/600)	+23%
Overall	72% (722/1,000)	97.6% (976/1,000)	+25.6%

Specific Improvement Examples

Test Case 1:

Subject: [Personnel Information] Introduction of Mr./Ms. ○○
Result: TALENT (correct)
Reason: Based on "who is providing what" criteria,
        correctly identified as staffing company providing engineer

Test Case 2:

Subject: Re: Introduction of Engineer
Result: TALENT (correct)
Reason: Correctly identified as individual introduction based on age/gender information

Technical Details

Prompt Engineering Key Points

Hierarchical Judgment Criteria

   Level 1: Check definitive keywords
   Level 2: Contextual analysis of "who provides what"
   Level 3: Learning from Few-shot examples
   Level 4: Personal information pattern verification

Few-shot Learning Effectiveness
- 0-shot (no examples): 60% accuracy
- 2-shot (2 examples): 80% accuracy
- 5-shot (5 examples): 100% accuracy
Importance of Context

   // Simple keyword matching (BAD)
   if (bodyText.includes('project')) {
     return 'PROJECT';
   }

   // Context-aware judgment (GOOD)
   const context = analyzeContext(bodyText);
   if (context.provider === 'client' && context.offering === 'project') {
     return 'PROJECT';
   }

AI Model Selection

Accuracy comparison across different AI models:

Model	Accuracy	Speed	Cost/Month※
GPT-3.5	85%	0.5s	¥3,000
GPT-4	95%	2.0s	¥18,000
GPT-4 Turbo	98%	1.5s	¥12,000
Claude 3 Opus	96%	1.8s	¥15,000

※Estimated cost when processing 100,000 emails/month (prices as of November 2024)

We selected GPT-4 Turbo for this implementation.

Cost Analysis

Monthly email processing volume and cost estimation:

// Cost calculation
const COST_ANALYSIS = {
  // Processing volume
  emailsPerDay: 3000,
  emailsPerMonth: 90000,

  // GPT-4 Turbo pricing (as of November 2024)
  inputTokenCost: 0.01,  // $0.01 per 1K tokens
  outputTokenCost: 0.03, // $0.03 per 1K tokens

  // Average token count (measured)
  avgInputTokens: 800,   // Prompt + email body
  avgOutputTokens: 150,  // Classification result + reasoning

  // Monthly cost calculation
  calculateMonthlyCost() {
    const inputCost = (this.emailsPerMonth * this.avgInputTokens / 1000) * this.inputTokenCost;
    const outputCost = (this.emailsPerMonth * this.avgOutputTokens / 1000) * this.outputTokenCost;
    return {
      inputCost: inputCost,
      outputCost: outputCost,
      totalCost: inputCost + outputCost,
      totalCostJPY: (inputCost + outputCost) * 150 // 1USD = 150JPY
    };
  }
};

console.log(COST_ANALYSIS.calculateMonthlyCost());
// Result:
// {
//   inputCost: 720 USD,
//   outputCost: 405 USD,
//   totalCost: 1,125 USD,
//   totalCostJPY: 168,750 JPY
// }

Cost reduction optimizations:

Cache utilization to avoid re-classifying duplicate emails (-30%)
Batch processing for efficiency (-10%)
Preprocessing filter for obvious patterns (-20%)

Estimated cost after optimization: Approximately ¥67,500/month

Prompt Version Management

Git Management and Semantic Versioning

Implemented version control to track prompt changes:

// prompts/email-classification/v2.1.0.ts
export const EMAIL_CLASSIFICATION_PROMPT_V2_1_0 = {
  version: '2.1.0',
  releaseDate: '2024-11-13',
  changes: [
    'Added personal information pattern detection',
    'Improved TALENT category accuracy',
    'Fixed false positives for "project desired" keyword'
  ],
  prompt: `...actual prompt...`,
  fewShotExamples: [...],
  metrics: {
    accuracy: 0.98,
    precision: 0.97,
    recall: 0.99
  }
};

// Prompt A/B testing
export class PromptVersionManager {
  private currentVersion = 'v2.1.0';
  private versions = new Map<string, PromptVersion>();

  async testNewVersion(email: Email, newVersion: string) {
    const currentResult = await this.classify(email, this.currentVersion);
    const newResult = await this.classify(email, newVersion);

    // Compare results and log
    await this.logComparison({
      email: email.id,
      currentVersion: this.currentVersion,
      newVersion: newVersion,
      currentResult,
      newResult,
      agree: currentResult.category === newResult.category
    });

    return { currentResult, newResult };
  }

  async rollback(version: string) {
    console.log(`Rolling back from ${this.currentVersion} to ${version}`);
    this.currentVersion = version;
    // Send alert notification
    await this.notifyRollback(version);
  }
}

Fallback Strategies

Handling Low Confidence Cases

Approach when AI judgment lacks confidence:

interface ClassificationResult {
  category: 'PROJECT' | 'TALENT' | 'OTHER' | 'UNCERTAIN';
  confidence: number;  // Confidence score 0-1
  reasoning: string;
  requiresManualReview?: boolean;
}

export async function classifyWithFallback(
  email: Email
): Promise<ClassificationResult> {
  try {
    // Step 1: Execute AI classification
    const aiResult = await classifyEmail(email);

    // Step 2: Check confidence
    if (aiResult.confidence < 0.7) {
      console.warn(`Low confidence classification: ${aiResult.confidence}`, {
        emailId: email.id,
        category: aiResult.category
      });

      // Step 3: Second opinion (different model)
      const claudeResult = await classifyWithClaude(email);

      if (aiResult.category !== claudeResult.category) {
        // If opinions differ, request manual review
        return {
          category: 'UNCERTAIN',
          confidence: Math.min(aiResult.confidence, claudeResult.confidence),
          reasoning: `GPT-4: ${aiResult.category}, Claude: ${claudeResult.category}`,
          requiresManualReview: true
        };
      }
    }

    // Step 4: Rule-based validation
    const ruleBasedCategory = applyBusinessRules(email);
    if (ruleBasedCategory && ruleBasedCategory !== aiResult.category) {
      console.warn('Rule-based override triggered', {
        ai: aiResult.category,
        rule: ruleBasedCategory
      });

      return {
        ...aiResult,
        category: ruleBasedCategory,
        reasoning: `Override: ${aiResult.reasoning}. Rule applied.`
      };
    }

    return aiResult;

  } catch (error) {
    // Step 5: Error fallback
    console.error('Classification failed, using fallback', error);

    // Basic keyword matching
    const fallbackCategory = simpleFallbackClassification(email);

    return {
      category: fallbackCategory || 'OTHER',
      confidence: 0.3,
      reasoning: 'Fallback classification due to AI error',
      requiresManualReview: true
    };
  }
}

// Business rule validation
function applyBusinessRules(email: Email): string | null {
  // Definitive domain rules
  const projectDomains = ['client-company.co.jp', 'project-sender.com'];
  const talentDomains = ['hr-agency.jp', 'staffing-company.com'];

  const domain = email.fromAddress.split('@')[1];

  if (projectDomains.includes(domain)) return 'PROJECT';
  if (talentDomains.includes(domain)) return 'TALENT';

  // Definitive keyword rules
  if (email.subject.startsWith('[Personnel Information]')) return 'TALENT';
  if (email.subject.startsWith('[Project Details]')) return 'PROJECT';

  return null;
}

Operational Considerations

Monitoring and Alerts

Continuously monitoring classification accuracy:

// monitoring/classification-monitor.ts
export class ClassificationMonitor {
  private metrics = {
    totalClassifications: 0,
    lowConfidenceCount: 0,
    errorCount: 0,
    manualReviewQueue: []
  };

  async monitor() {
    // Hourly accuracy check
    setInterval(async () => {
      const stats = await this.calculateHourlyStats();

      // Anomaly detection
      if (stats.accuracy < 0.90) {
        await this.sendAlert({
          level: 'WARNING',
          message: `Classification accuracy dropped to ${stats.accuracy}`,
          action: 'Check prompt performance'
        });
      }

      if (stats.errorRate > 0.05) {
        await this.sendAlert({
          level: 'CRITICAL',
          message: `High error rate: ${stats.errorRate}`,
          action: 'Immediate investigation required'
        });
      }

      // Send to CloudWatch metrics
      await this.pushToCloudWatch(stats);
    }, 3600000);
  }

  async recordClassification(result: ClassificationResult, actual?: string) {
    this.metrics.totalClassifications++;

    if (result.confidence < 0.7) {
      this.metrics.lowConfidenceCount++;
    }

    if (result.requiresManualReview) {
      this.metrics.manualReviewQueue.push({
        timestamp: new Date(),
        result
      });
    }

    // Compare with actual category (feedback loop)
    if (actual && actual !== result.category) {
      await this.recordMisclassification({
        predicted: result.category,
        actual: actual,
        confidence: result.confidence,
        reasoning: result.reasoning
      });
    }
  }
}

Regular Prompt Review

// Monthly review process
export async function monthlyPromptReview() {
  const report = {
    period: new Date().toISOString().slice(0, 7),
    totalEmails: 0,
    misclassifications: [],
    commonPatterns: [],
    recommendations: []
  };

  // Analyze misclassification patterns
  const misclassified = await getMisclassifiedEmails();

  // Extract patterns
  const patterns = extractCommonPatterns(misclassified);

  // Generate improvement suggestions
  if (patterns.length > 0) {
    report.recommendations.push({
      type: 'ADD_FEW_SHOT_EXAMPLES',
      patterns: patterns,
      estimatedImpact: calculateImpact(patterns)
    });
  }

  // Send report
  await sendMonthlyReport(report);
}

Testing Strategy

Unit Test Implementation

// __tests__/email-classification.test.ts
import { describe, it, expect, beforeEach, vi } from 'vitest';
import { classifyEmail, classifyWithFallback } from '../classification';

describe('Email Classification', () => {
  describe('Basic Classification', () => {
    it('should correctly classify PROJECT emails', async () => {
      const projectEmail = {
        subject: '[Project Details] Web App Development Project',
        fromName: 'Tanaka (Client Corporation)',
        fromAddress: 'tanaka@client.co.jp',
        bodyText: 'We are recruiting development members...'
      };

      const result = await classifyEmail(projectEmail);

      expect(result.category).toBe('PROJECT');
      expect(result.confidence).toBeGreaterThan(0.9);
    });

    it('should correctly classify TALENT emails', async () => {
      const talentEmail = {
        subject: '[Personnel Information] Introduction of Taro Yamada',
        fromName: 'Suzuki (Staffing Services)',
        fromAddress: 'suzuki@hr-agency.jp',
        bodyText: 'Name: Taro Yamada, Age: 35 years old...'
      };

      const result = await classifyEmail(talentEmail);

      expect(result.category).toBe('TALENT');
      expect(result.confidence).toBeGreaterThan(0.9);
    });
  });

  describe('Edge Cases', () => {
    it('should handle ambiguous emails with fallback', async () => {
      const ambiguousEmail = {
        subject: 'Inquiry',
        fromName: 'Unknown',
        fromAddress: 'unknown@example.com',
        bodyText: 'Details to follow'
      };

      const result = await classifyWithFallback(ambiguousEmail);

      expect(result.requiresManualReview).toBe(true);
      expect(result.confidence).toBeLessThan(0.7);
    });

    it('should detect personal information patterns', async () => {
      const emailWithPersonalInfo = {
        subject: 'Engineer Information',
        fromName: 'Test',
        fromAddress: 'test@example.com',
        bodyText: 'Age: 30 years old, Gender: Male, Name: Jiro Sato'
      };

      const result = await classifyEmail(emailWithPersonalInfo);

      expect(result.category).toBe('TALENT');
    });
  });

  describe('Performance', () => {
    it('should classify within timeout', async () => {
      const email = generateTestEmail();

      const startTime = performance.now();
      await classifyEmail(email);
      const endTime = performance.now();

      expect(endTime - startTime).toBeLessThan(3000); // Within 3 seconds
    });

    it('should handle batch classification efficiently', async () => {
      const emails = Array.from({ length: 100 }, generateTestEmail);

      const results = await Promise.all(
        emails.map(email => classifyEmail(email))
      );

      expect(results).toHaveLength(100);
      expect(results.every(r => r.category)).toBe(true);
    });
  });
});

describe('Fallback Strategies', () => {
  it('should use rule-based override when applicable', async () => {
    const email = {
      subject: 'Test Email',
      fromName: 'Client',
      fromAddress: 'test@client-company.co.jp', // Domain defined in rules
      bodyText: 'Content'
    };

    const result = await classifyWithFallback(email);

    expect(result.category).toBe('PROJECT');
    expect(result.reasoning).toContain('Rule applied');
  });

  it('should request manual review for conflicting classifications', async () => {
    // Mock to return different results from GPT and Claude
    vi.spyOn(global, 'classifyEmail').mockResolvedValueOnce({
      category: 'PROJECT',
      confidence: 0.6,
      reasoning: 'GPT reasoning'
    });

    vi.spyOn(global, 'classifyWithClaude').mockResolvedValueOnce({
      category: 'TALENT',
      confidence: 0.6,
      reasoning: 'Claude reasoning'
    });

    const result = await classifyWithFallback(testEmail);

    expect(result.category).toBe('UNCERTAIN');
    expect(result.requiresManualReview).toBe(true);
  });
});

Troubleshooting

Common Issues and Solutions

1. Token Limit Error

Symptom: "Maximum token limit exceeded" error

Cause: Email body too long or too many Few-shot examples

Solution:

// Email body truncation
function truncateEmailBody(bodyText: string, maxLength: number = 2000): string {
  if (bodyText.length <= maxLength) return bodyText;

  // Prioritize important sections
  const header = bodyText.substring(0, 500);
  const footer = bodyText.substring(bodyText.length - 300);
  const middle = bodyText.substring(500, maxLength - 800);

  return `${header}\n...[truncated]...\n${middle}\n...[truncated]...\n${footer}`;
}

// Pre-check token count
import { encoding_for_model } from 'tiktoken';

function estimateTokens(text: string): number {
  const encoder = encoding_for_model('gpt-4');
  const tokens = encoder.encode(text);
  encoder.free();
  return tokens.length;
}

2. Rate Limit Error

Symptom: "Rate limit exceeded" error

Solution:

// Rate limit handling with retry logic
import { RateLimiter } from 'limiter';

const limiter = new RateLimiter({
  tokensPerInterval: 100,
  interval: 'minute'
});

async function classifyWithRateLimit(email: Email): Promise<ClassificationResult> {
  await limiter.removeTokens(1);

  try {
    return await classifyEmail(email);
  } catch (error) {
    if (error.code === 'rate_limit_exceeded') {
      const waitTime = error.headers['retry-after'] || 60;
      console.log(`Rate limited. Waiting ${waitTime}s...`);
      await new Promise(resolve => setTimeout(resolve, waitTime * 1000));
      return classifyWithRateLimit(email);
    }
    throw error;
  }
}

3. Accuracy Degradation

Symptom: Accuracy decreases over time

Cause: Changes in business rules, emergence of new email patterns

Solution:

// Regular accuracy checks and automatic improvement
async function autoImprovePrompt() {
  const recentMisclassifications = await getRecentMisclassifications(30); // 30 days

  if (recentMisclassifications.length > 10) {
    // Automatically generate new Few-shot examples
    const newExamples = generateFewShotExamples(recentMisclassifications);

    // Conduct A/B test
    const improved = await testImprovedPrompt(newExamples);

    if (improved.accuracy > currentAccuracy * 1.05) {
      await deployNewPrompt(improved.prompt);
      console.log('Prompt automatically improved and deployed');
    }
  }
}

Lessons Learned

Unexpected Pitfalls

Keyword Trap
- The word "project" appears in both categories
- Keyword matching without context is dangerous
Quality of Few-shot Examples
- Simply increasing examples isn't effective
- Important to include ambiguous cases
Prompt Length Trade-off
- Too detailed prompt → Increased tokens, higher cost
- Too concise prompt → Reduced accuracy
- Balance is crucial

Useful Knowledge for the Future

Prompt Design Best Practices

   Step 1: Define clear judgment criteria
   Step 2: Add Few-shot examples of typical cases
   Step 3: Add Few-shot examples of ambiguous cases
   Step 4: Articulate judgment logic
   Step 5: Test and improve iteratively

Selecting Few-shot Examples
- Diversity: Cover various patterns
- Clarity: Explicitly state reasoning
- Practicality: Reference actual misclassification cases
Gradual Accuracy Improvement Approach

   Phase 1: Measure baseline accuracy (60%)
   Phase 2: Clarify judgment criteria (80%)
   Phase 3: Add Few-shot examples (95%)
   Phase 4: Utilize personal information patterns (100%)

Better Implementation Discovered

Before (vague instructions):

const prompt = `
Please classify this email.
- PROJECT: Project email
- TALENT: Personnel email
`;

After (clear judgment criteria):

const prompt = `
## Most Important Point: Who is providing what?

PROJECT = Client provides project
TALENT = Staffing company provides engineer

Judgment method:
1. Check definitive keywords
2. Confirm sender's position
3. Check for personal information
4. Match with Few-shot examples
`;

Conclusion

Improving AI classification accuracy was an opportunity to reaffirm the importance of prompt engineering. Key takeaways from this effort:

Clarifying Judgment Criteria: The perspective of "who is providing what"
Leveraging Few-shot Learning: Especially learning from ambiguous cases
Gradual Improvement: Accumulating small improvements

Particularly, including actual misclassification cases in Few-shot examples was the decisive factor in improving accuracy. Since AI learns from provided examples, what examples you provide is extremely important.

If you're facing challenges with AI classification accuracy, start by collecting misclassification cases and using them as Few-shot examples. You'll be surprised at how much accuracy improves.

The code presented in this article is simplified from actual production code. In real implementations, additional considerations such as error handling and security checks are required.

Related Technologies: OpenAI GPT, Claude, TypeScript, Prompt Engineering, Few-shot Learning, Natural Language Processing, AI Classification, Machine Learning

Author: Development Team

Optimizing Connection Pools with PrismaClient Singleton Pattern in Next.js

我妻良樹 — Tue, 18 Nov 2025 07:57:50 +0000

Overview

In our Next.js application, we encountered connection pool exhaustion issues during development hot reloads because each page was creating its own PrismaClient instance. This article explains how we resolved this problem by implementing a proper singleton pattern and optimizing connection pool settings for Neon Database.

Tech Stack

Next.js 14+ (App Router)
Prisma ORM (v5.x)
PostgreSQL (Neon Database)
TypeScript (v5.x)
Singleton Pattern
Edge Runtime Support

Background & Challenges

Problem Discovery

We were experiencing the following issues in our development environment:

// Each page was creating its own instance (BAD practice)
// app/dashboard/page.tsx
export default async function DashboardPage() {
  const prisma = new PrismaClient(); // ❌ Creates new instance every time

  const customers = await prisma.customer.findMany();
  // ...
}

// app/emails/[id]/page.tsx
export default async function EmailDetailPage() {
  const prisma = new PrismaClient(); // ❌ Creates new instance every time

  const email = await prisma.email.findUnique();
  // ...
}

Problems encountered:

New PrismaClient instances were created on every hot reload
Old instances weren't properly disposed, leading to connection pool exhaustion
Slow connections after idle periods
Unnecessary connections in production environment

Error Messages

Error: Can't reach database server at `xxx.xxx.xxx.xxx:5432`
Please make sure your database server is running at `xxx.xxx.xxx.xxx:5432`.

Prisma Client could not connect to the database.

Performance Measurements

Connection time comparison:

Before singleton: Initial access 500ms, subsequent accesses 300-400ms
After singleton: Initial access 200ms, subsequent accesses 50-100ms

Implementation

1. Implementing the Singleton Pattern

We implemented a proper singleton pattern in packages/database/src/index.ts:

// packages/database/src/index.ts
import { PrismaClient } from '@prisma/client';

// Global variable type definition
const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

// Create or retrieve PrismaClient instance
export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: process.env.NODE_ENV === 'development'
      ? ['query', 'error', 'warn']
      : ['error'],
    datasources: {
      db: {
        url: process.env.DATABASE_URL,
      },
    },
  });

// Save globally only in development (hot reload support)
if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}

// Re-export types
export * from '@prisma/client';

Key Points:

Leveraging globalThis
- globalThis persists even when variables are reset during Next.js hot reloads
- Prevents unnecessary instance creation in development environment
Environment-specific Handling
- production: Creates new instance each time (serverless environment support)
- development: Saves globally (hot reload support)
Log Level Configuration
- development: Outputs query logs for improved development efficiency
- production: Error logs only for optimal production performance

2. Optimizing Connection Pool Settings

We added connection pool settings for Neon Database in .env:

# .env.example
# Connection pool settings for Neon Database
DATABASE_URL="postgresql://user:password@host/dbname?connection_limit=10&pool_timeout=10&connect_timeout=10"

# Parameter descriptions:
# - connection_limit: Maximum number of connections (Neon free plan allows up to 10)
# - pool_timeout: Timeout for acquiring connection from pool (seconds)
# - connect_timeout: Database connection timeout (seconds)

Connection Count Design:

Neon free plan: Maximum 10 connections
Recommended setting: connection_limit=10
Production environment: Adjust based on number of application instances

3. Standardizing Usage Across Pages

We updated all 7 files to use the shared instance:

// Before (BAD)
import { PrismaClient } from '@prisma/client';

export default async function Page() {
  const prisma = new PrismaClient(); // ❌
  // ...
}

// After (GOOD)
import { prisma } from '@myapp/database'; // ✅

export default async function Page() {
  // Use shared instance directly
  const customers = await prisma.customer.findMany();
  // ...
}

Modified Files:

app/dashboard/page.tsx
app/customers/page.tsx
app/customers/[id]/page.tsx
app/emails/[id]/page.tsx
app/auth/signin/page.tsx
app/layout.tsx
app/api/attachments/[attachmentId]/download/route.ts

4. Explicitly Setting Next.js Cache Strategy

To ensure data consistency, we explicitly set dynamic rendering:

// app/dashboard/page.tsx
export const dynamic = 'force-dynamic'; // ✅ Disable caching

export default async function DashboardPage() {
  const customers = await prisma.customer.findMany();
  // Always fetch latest data
}

Cache Strategy Options:

force-dynamic: Always use dynamic rendering (prioritize real-time data)
force-static: Static generation (prioritize performance)
auto: Next.js automatically determines (default)

Performance Improvement Results

Connection Time Improvements

Scenario	Before	After	Improvement
Initial Access	500ms	200ms	60% faster
Subsequent Accesses	300-400ms	50-100ms	75% faster
After Hot Reload	600ms	150ms	75% faster

Connection Pool Status

Before:

Active connections: 15-20
Idle connections: 5-10
Total: 20-30 (significantly exceeding limit of 10)
→ Errors occurred

After:

Active connections: 3-5
Idle connections: 5-7
Total: 8-12 (within limit)
→ Stable operation

Memory Usage

Before: ~10MB PrismaClient per page
After: ~10MB shared instance (fixed)
Memory reduction: ~70-80MB (across 8 pages)

Edge Runtime Support

Constraints and Solutions for Edge Environment

Considerations when using Prisma in Next.js Edge Runtime:

// Use Prisma Adapter for Edge Runtime
import { PrismaClient } from '@prisma/client/edge';
import { withAccelerate } from '@prisma/extension-accelerate';

// Edge environment configuration
export const prismaEdge = new PrismaClient({
  datasourceUrl: process.env.DATABASE_URL
}).$extends(withAccelerate());

// Usage example (Edge API Route)
export const runtime = 'edge';

export async function GET() {
  const users = await prismaEdge.user.findMany({
    cacheStrategy: { ttl: 60 } // Leverage Accelerate caching
  });

  return Response.json(users);
}

Edge Runtime Selection Criteria:

Global distribution needed → Edge Runtime
Complex database operations → Node.js Runtime
Real-time priority → Node.js Runtime
Scalability priority → Edge Runtime

Memory Leak Prevention

Proper $disconnect() Invocation

// Implementing graceful shutdown
import { prisma } from '@myapp/database';

// Cleanup on process termination
async function gracefulShutdown() {
  console.log('Gracefully shutting down...');

  try {
    await prisma.$disconnect();
    console.log('Database connections closed');
  } catch (error) {
    console.error('Error during shutdown:', error);
  }

  process.exit(0);
}

// Signal handling
process.on('SIGTERM', gracefulShutdown);
process.on('SIGINT', gracefulShutdown);

// Proper handling within Next.js API Routes
export async function GET(request: Request) {
  try {
    const data = await prisma.user.findMany();
    return Response.json(data);
  } catch (error) {
    // Ensure connection cleanup even on errors
    if (error instanceof Error && error.message.includes('P2024')) {
      // When connection pool is full
      await prisma.$disconnect();
      await new Promise(resolve => setTimeout(resolve, 100));
      // Retry or return error
    }
    throw error;
  }
}

Monitoring Memory Usage

// Memory monitoring and alerts
setInterval(async () => {
  const memUsage = process.memoryUsage();

  // Display memory usage in MB
  const report = {
    rss: Math.round(memUsage.rss / 1024 / 1024),
    heapTotal: Math.round(memUsage.heapTotal / 1024 / 1024),
    heapUsed: Math.round(memUsage.heapUsed / 1024 / 1024),
    external: Math.round(memUsage.external / 1024 / 1024)
  };

  console.log('Memory usage:', report);

  // Alert if threshold exceeded
  if (report.heapUsed > 500) { // Over 500MB
    console.warn('High memory usage detected!');
    // Reset connections if necessary
    await prisma.$disconnect();
    await prisma.$connect();
  }
}, 60000); // Every 1 minute

Connection Pool Monitoring

Leveraging Prisma.$metrics

// packages/database/src/monitoring.ts
import { prisma } from './index';

export async function enableMetrics() {
  // Enable metrics
  await prisma.$metrics.json();
}

export async function getPoolMetrics() {
  const metrics = await prisma.$metrics.json();

  // Extract connection pool related metrics
  const poolMetrics = {
    activeConnections: 0,
    idleConnections: 0,
    waitingClients: 0,
    totalQueries: 0
  };

  metrics.counters.forEach(counter => {
    switch (counter.key) {
      case 'prisma_pool_connections_open':
        poolMetrics.activeConnections = counter.value;
        break;
      case 'prisma_pool_connections_idle':
        poolMetrics.idleConnections = counter.value;
        break;
      case 'prisma_pool_connections_busy':
        poolMetrics.waitingClients = counter.value;
        break;
      case 'prisma_client_queries_total':
        poolMetrics.totalQueries = counter.value;
        break;
    }
  });

  return poolMetrics;
}

// Periodic monitoring
export function startPoolMonitoring() {
  setInterval(async () => {
    const metrics = await getPoolMetrics();

    console.log('Connection Pool Status:', {
      ...metrics,
      timestamp: new Date().toISOString()
    });

    // Send to CloudWatch or Datadog
    await sendToMonitoring(metrics);

    // Check alert conditions
    if (metrics.activeConnections > 8) {
      await sendAlert({
        level: 'WARNING',
        message: `High connection count: ${metrics.activeConnections}/10`,
        metrics
      });
    }
  }, 30000); // Every 30 seconds
}

Dashboard Example

// app/api/admin/metrics/route.ts
import { NextResponse } from 'next/server';
import { getPoolMetrics } from '@myapp/database/monitoring';

export async function GET() {
  try {
    const metrics = await getPoolMetrics();
    const health = {
      status: metrics.activeConnections < 8 ? 'healthy' : 'warning',
      database: {
        connections: {
          active: metrics.activeConnections,
          idle: metrics.idleConnections,
          total: metrics.activeConnections + metrics.idleConnections,
          limit: 10
        },
        queries: {
          total: metrics.totalQueries,
          qps: calculateQPS(metrics.totalQueries)
        }
      },
      timestamp: new Date().toISOString()
    };

    return NextResponse.json(health);
  } catch (error) {
    return NextResponse.json(
      { error: 'Failed to fetch metrics' },
      { status: 500 }
    );
  }
}

Transaction Management Considerations

Proper Transaction Handling

// Connection management within transactions
export async function transferCredits(
  fromUserId: string,
  toUserId: string,
  amount: number
) {
  // Using Interactive Transaction
  return await prisma.$transaction(async (tx) => {
    // 1. Check sender's balance
    const sender = await tx.user.findUnique({
      where: { id: fromUserId },
      select: { credits: true }
    });

    if (!sender || sender.credits < amount) {
      throw new Error('Insufficient credits');
    }

    // 2. Deduct from sender
    await tx.user.update({
      where: { id: fromUserId },
      data: { credits: { decrement: amount } }
    });

    // 3. Add to recipient
    await tx.user.update({
      where: { id: toUserId },
      data: { credits: { increment: amount } }
    });

    // 4. Record transaction log
    const log = await tx.transactionLog.create({
      data: {
        fromUserId,
        toUserId,
        amount,
        timestamp: new Date()
      }
    });

    return log;
  }, {
    maxWait: 5000, // Maximum wait time before transaction starts
    timeout: 10000, // Transaction timeout
    isolationLevel: 'Serializable' // Isolation level
  });
}

// With error handling
export async function safeTransfer(
  fromUserId: string,
  toUserId: string,
  amount: number
) {
  let retries = 3;

  while (retries > 0) {
    try {
      return await transferCredits(fromUserId, toUserId, amount);
    } catch (error) {
      if (error instanceof Error) {
        // Retry on deadlock or timeout
        if (error.message.includes('P2034') || error.message.includes('P2024')) {
          retries--;
          console.log(`Retrying transaction... (${retries} attempts left)`);
          await new Promise(resolve => setTimeout(resolve, 1000));
        } else {
          throw error; // Throw other errors immediately
        }
      }
    }
  }

  throw new Error('Transaction failed after maximum retries');
}

Handling Test Environments

Mocking with Jest

// __tests__/setup.ts
import { PrismaClient } from '@prisma/client';
import { mockDeep, mockReset, DeepMockProxy } from 'jest-mock-extended';

// Create Prisma Client mock
export const prismaMock = mockDeep<PrismaClient>() as unknown as DeepMockProxy<PrismaClient>;

// jest.setup.js
jest.mock('@myapp/database', () => ({
  __esModule: true,
  prisma: prismaMock
}));

// Test file example
import { prismaMock } from './setup';
import { getUserById } from '../services/user';

describe('User Service', () => {
  beforeEach(() => {
    mockReset(prismaMock);
  });

  it('should get user by id', async () => {
    const mockUser = {
      id: '1',
      name: 'Test User',
      email: 'test@example.com'
    };

    prismaMock.user.findUnique.mockResolvedValue(mockUser);

    const user = await getUserById('1');

    expect(user).toEqual(mockUser);
    expect(prismaMock.user.findUnique).toHaveBeenCalledWith({
      where: { id: '1' }
    });
  });
});

Integration Test Setup

// __tests__/integration/setup.ts
import { PrismaClient } from '@prisma/client';
import { execSync } from 'child_process';

// Test database URL
const TEST_DATABASE_URL = process.env.TEST_DATABASE_URL || 'postgresql://test:test@localhost:5432/test';

// Test Prisma client
export const testPrisma = new PrismaClient({
  datasources: {
    db: {
      url: TEST_DATABASE_URL
    }
  }
});

// Pre-test setup
export async function setupTestDatabase() {
  // Run migrations
  execSync('npx prisma migrate deploy', {
    env: { ...process.env, DATABASE_URL: TEST_DATABASE_URL }
  });

  // Insert seed data
  await testPrisma.user.createMany({
    data: [
      { name: 'Alice', email: 'alice@test.com' },
      { name: 'Bob', email: 'bob@test.com' }
    ]
  });
}

// Post-test cleanup
export async function teardownTestDatabase() {
  // Delete data (considering foreign key constraints)
  await testPrisma.transactionLog.deleteMany();
  await testPrisma.user.deleteMany();

  // Close connection
  await testPrisma.$disconnect();
}

Operational Considerations

Deployment Considerations

# .github/workflows/deploy.yml
name: Deploy with Zero Downtime

on:
  push:
    branches: [main]

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3

      - name: Run migrations
        run: |
          # Blue-Green deployment support
          npx prisma migrate deploy
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}

      - name: Health check before deployment
        run: |
          # Check connection pool status
          curl https://api.example.com/api/admin/metrics

      - name: Deploy to production
        run: |
          # Rolling deployment
          kubectl set image deployment/api api=myapp:${{ github.sha }}
          kubectl rollout status deployment/api

      - name: Post-deployment verification
        run: |
          # Check connection pool after deployment
          sleep 30
          curl https://api.example.com/api/admin/metrics

Monitoring and Alert Settings

// monitoring/alerts.ts
export const alertRules = [
  {
    name: 'high_connection_count',
    condition: 'connections.active > 8',
    severity: 'warning',
    action: 'notify_slack'
  },
  {
    name: 'connection_pool_exhausted',
    condition: 'connections.active >= 10',
    severity: 'critical',
    action: 'page_oncall'
  },
  {
    name: 'slow_queries',
    condition: 'query.duration > 1000',
    severity: 'warning',
    action: 'log_to_dashboard'
  }
];

Troubleshooting

Common Issues and Solutions

1. "Too many connections" Error

Symptom: Database connection count reaches limit

Solution:

// Connection reset script
async function resetConnections() {
  console.log('Resetting all database connections...');

  // Disconnect all existing connections
  await prisma.$disconnect();

  // Wait briefly
  await new Promise(resolve => setTimeout(resolve, 1000));

  // Establish new connection
  await prisma.$connect();

  console.log('Connections reset successfully');
}

// Integrate into error handler
app.use(async (error, req, res, next) => {
  if (error.code === 'P2024') {
    await resetConnections();
    // Retry or return error response
  }
  next(error);
});

2. Memory Leak During Hot Reload

Symptom: Memory usage continues increasing in development environment

Solution:

// Cleanup for development environment
if (process.env.NODE_ENV === 'development') {
  // HMR (Hot Module Replacement) cleanup
  if (module.hot) {
    module.hot.dispose(async () => {
      await prisma.$disconnect();
    });
  }
}

3. Connection Errors in Serverless Environment

Symptom: Intermittent connection errors in Lambda functions

Solution:

// Connection management for Serverless
let prisma: PrismaClient;

export function getPrismaClient() {
  if (!prisma) {
    prisma = new PrismaClient({
      datasources: {
        db: {
          url: process.env.DATABASE_URL + '?connection_limit=1' // Serverless uses 1 connection
        }
      }
    });
  }
  return prisma;
}

// Lambda handler
export const handler = async (event: any) => {
  const client = getPrismaClient();

  try {
    // Execute query
    const result = await client.user.findMany();
    return { statusCode: 200, body: JSON.stringify(result) };
  } finally {
    // Maintain connection for cold start optimization in Lambda
    // await client.$disconnect(); // Don't disconnect
  }
};

Technical Details

How globalThis Works

In Next.js development environment, modules are reloaded on file changes:

// Hot reload behavior

// First execution
const prisma = new PrismaClient(); // Create instance A
globalForPrisma.prisma = prisma;  // Save globally

// File change (hot reload)

// Second execution
const prisma = globalForPrisma.prisma ?? new PrismaClient();
// → Reuse instance A saved globally
// → No new instance created

Behavior in Production Environment

In production, we consider serverless function characteristics:

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}
// Don't save globally in production
// → Each Lambda/Edge function has its own instance

Reason:

Serverless environments have independent memory space per function
Global variables are not shared
More efficient for each function to manage its own PrismaClient

Connection Pool Lifecycle

// Connection pool management
const prisma = new PrismaClient(); // Create pool (for 10 connections)

// 1. When executing query
await prisma.customer.findMany();
// → Acquire 1 connection from pool
// → Execute query
// → Return connection to pool

// 2. During idle time
// → Connections remain maintained waiting for next query
// → Timeout after connect_timeout

// 3. On application termination
await prisma.$disconnect();
// → Properly close all connections

Lessons Learned

Unexpected Pitfalls

Environment-specific Behavior in Next.js
- development: Modules reset on hot reload
- production: Independent per serverless function
- Design must accommodate both scenarios
PrismaClient Initialization Cost
- Instance creation takes 100-200ms
- Schema loading and validation
- Connection pool initialization
Neon Database Connection Limits
- Free plan: Maximum 10 connections
- Paid plans: Scalable
- Must consider during application design

Useful Knowledge for the Future

Singleton Pattern Template

   // Generic singleton pattern
   const globalForX = globalThis as unknown as {
     x: SomeClass | undefined;
   };

   export const x =
     globalForX.x ??
     new SomeClass(config);

   if (process.env.NODE_ENV !== 'production') {
     globalForX.x = x;
   }

Connection Pool Setting Best Practices

   connection_limit = Total connections / Number of app instances
   pool_timeout = Query timeout + margin
   connect_timeout = Network latency + margin

Gradual Migration Strategy

   Phase 1: Create shared instance
   Phase 2: Migrate one file at a time and test
   Phase 3: Measure performance after migrating all files
   Phase 4: Optimize connection pool settings

Better Implementation Discovered

Before (creating in each page):

// page1.tsx
const prisma = new PrismaClient();

// page2.tsx
const prisma = new PrismaClient();

// page3.tsx
const prisma = new PrismaClient();
// → 3 instances, 30 connections

After (shared instance):

// database/index.ts
export const prisma = new PrismaClient();

// page1.tsx
import { prisma } from '@myapp/database';

// page2.tsx
import { prisma } from '@myapp/database';

// page3.tsx
import { prisma } from '@myapp/database';
// → 1 instance, 10 connections

Conclusion

Implementing the PrismaClient singleton pattern was a highly effective approach for database connection optimization in Next.js environments. Key takeaways from this implementation:

Environment-appropriate Implementation: Consider different behaviors in development/production
Leveraging globalThis: Key point for hot reload support
Connection Pool Settings: Optimization according to database plan

Particularly with Next.js App Router, where Server Components execute on each request, implementing the singleton pattern is extremely important. This enabled significant improvements in performance, memory usage, and connection count.

I recommend implementing the singleton pattern early when using Prisma with Next.js. This can prevent issues during hot reloads in development and connection pool exhaustion in production.

References

The code presented in this article is simplified from actual production code. In real implementations, additional considerations such as error handling and security checks are required.

Related Technologies: Next.js, Prisma ORM, PostgreSQL, Neon Database, TypeScript, Singleton Pattern, Connection Pool Optimization, Performance Tuning

Author: Development Team