Design: データモデル

要件は requirements.md 参照。本ドキュメントは テーブル定義・ER図・インデックス・マイグレーション・集計クエリ など、PostgreSQL + Prisma による具体的な実装設計を定義する。

---

技術スタック

• DB: PostgreSQL 15+
• ORM: Prisma 5+
• マネージドDB: Neon(Branching機能を活用)
• 画像保存: PostgreSQL の bytea 型でBLOBとしてDB内に保持

詳細は steering/tech.md の「データレイヤー」を参照。

---

ER図

erDiagram
    User ||--o{ Vote : "投票する"
    User ||--o{ Purchase : "購入する"
    Candidate ||--o{ Vote : "得票"
    Candidate ||--o{ Purchase : "購入対象"
    Candidate ||--o{ CandidateImage : "保持"
    CreditPackage ||--o{ Purchase : "スナップショット元"
    Purchase ||--o{ Vote : "由来 (有料投票のみ)"

    User {
        uuid id PK
        text name
        text email
        AuthProvider provider
        text providerUserId
        timestamptz createdAt
        timestamptz lastLoginAt
        timestamptz deletedAt "匿名化日時 (nullable)"
    }

    Candidate {
        uuid id PK
        text name
        text region
        int age
        int height
        text occupation
        text[] hobbies
        text[] specialties
        text motto
        text dream
        text message
        int displayOrder
        timestamptz createdAt
        timestamptz updatedAt
    }

    CandidateImage {
        uuid id PK
        uuid candidateId FK
        bytea data "画像本体"
        text mime "image/jpeg等"
        int order "0=メイン画像"
        timestamptz createdAt
        timestamptz updatedAt
    }

    Vote {
        uuid id PK
        uuid userId FK
        uuid candidateId FK
        VoteType voteType
        uuid purchaseId FK "有料投票のみ (nullable)"
        VoteStatus status "active or invalidated"
        timestamptz votedAt
        timestamptz createdAt
    }

    CreditPackage {
        uuid id PK
        text name
        int credits
        int priceJpy
        boolean isPopular
        int displayOrder
        boolean isActive
        timestamptz createdAt
        timestamptz updatedAt
    }

    Purchase {
        uuid id PK
        uuid userId FK
        uuid candidateId FK
        uuid creditPackageId FK "ref only"
        text packageNameSnapshot
        int packageCreditsSnapshot
        int packagePriceJpySnapshot
        text paymentProvider
        text paymentIntentId UK
        PaymentStatus status
        timestamptz createdAt
        timestamptz completedAt "成功時のみ"
    }

    VotingPeriod {
        uuid id PK
        timestamptz startsAt
        timestamptz endsAt
        boolean isActive "現開催かどうか"
        timestamptz createdAt
        timestamptz updatedAt
    }

VotingPeriod は他エンティティと外部キー関係を持たない独立テーブル。現開催の取得は isActive = true で1件絞り込む。

---

Prisma スキーマ

prisma/schema.prisma として配置する想定。

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// =========================================
// Enums
// =========================================

enum AuthProvider {
  FACEBOOK
  INSTAGRAM
}

enum VoteType {
  FREE
  PAID
}

enum VoteStatus {
  ACTIVE
  INVALIDATED
}

enum PaymentStatus {
  PROCESSING
  SUCCEEDED
  FAILED
  REFUNDED
}

// =========================================
// User(ユーザー)
// =========================================

model User {
  id              String       @id @default(uuid()) @db.Uuid
  name            String       @db.Text
  email           String       @db.Text
  provider        AuthProvider
  providerUserId  String       @db.Text
  createdAt       DateTime     @default(now()) @db.Timestamptz()
  lastLoginAt     DateTime     @default(now()) @db.Timestamptz()
  deletedAt       DateTime?    @db.Timestamptz()  // 匿名化日時

  votes           Vote[]
  purchases       Purchase[]

  @@unique([provider, providerUserId])
  @@index([email])
  @@map("users")
}

// =========================================
// Candidate(候補者)
// =========================================

model Candidate {
  id            String     @id @default(uuid()) @db.Uuid
  name          String     @db.Text
  region        String     @db.Text
  age           Int
  height        Int        // cm
  occupation    String     @db.Text
  hobbies       String[]   @db.Text
  specialties   String[]   @db.Text
  motto         String     @db.Text
  dream         String     @db.Text
  message       String     @db.Text
  displayOrder  Int        @default(0)
  createdAt     DateTime   @default(now()) @db.Timestamptz()
  updatedAt     DateTime   @updatedAt @db.Timestamptz()

  images        CandidateImage[]
  votes         Vote[]
  purchases     Purchase[]

  @@index([displayOrder])
  @@map("candidates")
}

// =========================================
// CandidateImage(候補者画像)
// =========================================

model CandidateImage {
  id            String     @id @default(uuid()) @db.Uuid
  candidateId   String     @db.Uuid
  data          Bytes                              // bytea: 画像本体
  mime          String     @db.Text                // "image/jpeg" 等
  order         Int        @default(0)             // 0=メイン画像
  createdAt     DateTime   @default(now()) @db.Timestamptz()
  updatedAt     DateTime   @updatedAt @db.Timestamptz()

  candidate     Candidate  @relation(fields: [candidateId], references: [id], onDelete: Cascade)

  @@index([candidateId, order])
  @@map("candidate_images")
}

// =========================================
// Vote(投票)
// =========================================

model Vote {
  id           String     @id @default(uuid()) @db.Uuid
  userId       String     @db.Uuid
  candidateId  String     @db.Uuid
  voteType     VoteType
  purchaseId   String?    @db.Uuid          // 有料投票のみ
  status       VoteStatus @default(ACTIVE)
  votedAt      DateTime   @default(now()) @db.Timestamptz()
  createdAt    DateTime   @default(now()) @db.Timestamptz()

  user         User       @relation(fields: [userId], references: [id], onDelete: Restrict)
  candidate    Candidate  @relation(fields: [candidateId], references: [id], onDelete: Restrict)
  purchase     Purchase?  @relation(fields: [purchaseId], references: [id], onDelete: Restrict)

  @@index([candidateId, status])
  @@index([userId, voteType, votedAt])
  @@index([candidateId, userId, status])
  @@index([purchaseId])
  @@map("votes")
}

// =========================================
// CreditPackage(投票券パッケージ)
// =========================================

model CreditPackage {
  id            String     @id @default(uuid()) @db.Uuid
  name          String     @db.Text
  credits       Int
  priceJpy      Int
  isPopular     Boolean    @default(false)
  displayOrder  Int        @default(0)
  isActive      Boolean    @default(true)
  createdAt     DateTime   @default(now()) @db.Timestamptz()
  updatedAt     DateTime   @updatedAt @db.Timestamptz()

  purchases     Purchase[]

  @@index([isActive, displayOrder])
  @@map("credit_packages")
}

// =========================================
// Purchase(購入)
// =========================================

model Purchase {
  id                       String        @id @default(uuid()) @db.Uuid
  userId                   String        @db.Uuid
  candidateId              String        @db.Uuid
  creditPackageId          String        @db.Uuid

  packageNameSnapshot      String        @db.Text
  packageCreditsSnapshot   Int
  packagePriceJpySnapshot  Int

  paymentProvider          String        @db.Text
  paymentIntentId          String        @db.Text
  status                   PaymentStatus @default(PROCESSING)

  createdAt                DateTime      @default(now()) @db.Timestamptz()
  completedAt              DateTime?     @db.Timestamptz()

  user                     User          @relation(fields: [userId], references: [id], onDelete: Restrict)
  candidate                Candidate     @relation(fields: [candidateId], references: [id], onDelete: Restrict)
  creditPackage            CreditPackage @relation(fields: [creditPackageId], references: [id], onDelete: Restrict)
  votes                    Vote[]

  @@unique([paymentIntentId])
  @@index([userId])
  @@index([status])
  @@map("purchases")
}

// =========================================
// VotingPeriod(投票期間)
// =========================================

model VotingPeriod {
  id          String   @id @default(uuid()) @db.Uuid
  startsAt    DateTime @db.Timestamptz()
  endsAt      DateTime @db.Timestamptz()
  isActive    Boolean  @default(false)
  createdAt   DateTime @default(now()) @db.Timestamptz()
  updatedAt   DateTime @updatedAt @db.Timestamptz()

  @@index([isActive])
  @@map("voting_periods")
}

アプリ層で endsAt > startsAt および「isActive = true のレコードは同時に1件以下」を担保する。DB層で部分一意インデックス(CREATE UNIQUE INDEX voting_periods_active_uniq ON voting_periods (is_active) WHERE is_active = true;)を併用してもよい。

---

設計のポイント

1. UUID を主キーに採用

全テーブルで UUID v4 を採用。クライアント側でも生成可能、URL露出に強い、シャーディング耐性が高い。

2. 画像本体は別テーブルに分離

Candidate 本体には画像を持たず、CandidateImage を1対多で関連付ける。

理由:

• SELECT * FROM candidates で画像本体まで取得されるのを防ぐ
• 候補者一覧画面の集計クエリで画像が混じらない
• 候補者ごとに複数画像を持たせる仕様にマッチ
• Prisma の select で必ず明示しないと取得されないので、ミスでもパフォーマンス劣化しない

3. bytea 型でBLOB保存

PostgreSQL の bytea 型(Prismaでは Bytes)を使用。

• 行サイズの上限(8KB)を超える画像は PostgreSQL の TOAST 機能で自動的に外部領域に格納される
• 数MB 程度の画像でも問題なく動作
• Neon の Branching では BLOB データもブランチ間でコピーオンライト方式で管理されるため、容量効率が良い

4. Vote を「無効化」する方式

ステータス列方式を採用(status: ACTIVE | INVALIDATED)。

• 物理削除より監査可能性が高い
• 集計時は WHERE status = 'ACTIVE' で除外

5. パッケージスナップショット

Purchase は creditPackageId を参考として保持しつつ、購入時点の名称・票数・価格を別カラム(*Snapshot)にコピーする。価格改定・パッケージ廃止に影響されない。

6. 決済重複防止

paymentIntentId に UNIQUE 制約を付与。Stripe Webhook の冪等性を DB 層で保証。

7. ON DELETE 戦略

• Vote / Purchase の外部キー: onDelete: Restrict(履歴の意図しない消失を防ぐ)
• CandidateImage: onDelete: Cascade(候補者削除時に画像も連鎖削除)

---

画像配信エンドポイント

配信エンドポイントの設計

候補者画像は /api/images/[id] 経由で配信する。

// app/api/images/[id]/route.ts
import { prisma } from '@/lib/prisma';
import { NextRequest } from 'next/server';

export async function GET(
  req: NextRequest,
  { params }: { params: { id: string } }
) {
  const image = await prisma.candidateImage.findUnique({
    where: { id: params.id },
    select: { data: true, mime: true, updatedAt: true },
  });

  if (!image) {
    return new Response('Not Found', { status: 404 });
  }

  return new Response(image.data, {
    headers: {
      'Content-Type': image.mime,
      'Cache-Control': 'public, max-age=31536000, immutable',
      'ETag': `"${params.id}-${image.updatedAt.getTime()}"`,
    },
  });
}

キャッシュ戦略

層	役割
ブラウザキャッシュ	Cache-Control: immutable で1年間保持
Vercel CDN(Edge)	同じく1年間エッジでキャッシュ、初回以外はオリジンに到達しない
Next.js Image Cache	next/image 経由の最適化済み画像をキャッシュ
DB クエリ	unstable_cache 等での個別キャッシュは不要(配信層でカバー)

画像更新時のキャッシュ無効化

Cache-Control: immutable でキャッシュした画像はそのままでは破棄されないので、画像更新時はURLを変えてキャッシュバストする:

// 画像URLに updatedAt を含める
<Image
  src={`/api/images/${image.id}?v=${image.updatedAt.getTime()}`}
  alt={candidate.name}
  width={400}
  height={533}
/>

?v= を含めるとURLが変わるため、新しいキャッシュエントリが作成される。古い画像のキャッシュは TTL 経過まで残るが、URLが参照されないので実害なし。

next/image との連携

next/image は /api/images/[id] を画像ソースとして扱える。同じドメインなので next.config.ts の images.remotePatterns 設定は不要。

<Image
  src={`/api/images/${imageId}?v=${updatedAt}`}
  width={400}
  height={533}
  alt={candidateName}
  priority={index < 4}
  sizes="(max-width: 640px) 100vw, (max-width: 1024px) 50vw, 25vw"
/>

これにより:

• 自動リサイズ(複数解像度生成)
• WebP / AVIF への自動変換
• レスポンシブ画像(srcset)
• Lazy loading
• すべて通常のCDN配信と同じく動作する

配信時の認証

公開エンドポイントとして認証なしで配信する。画像URLはUUIDで推測困難なため、URLが流出しない限り無断アクセスはほぼ不可能。

ただし、管理画面からのアップロード・削除は認証必須(後述)。

---

1日1票の制約をどう保証するか

無料投票は「同一ユーザーが1日1票」が要件だが、Prisma の @@unique だけでは表現しきれない(日付の境界が動的なため)。

採用する戦略: アプリ層 + DB層の二重防御

アプリ層(Server Action)

const todayStart = startOfDay(new Date());
const existingVote = await prisma.vote.findFirst({
  where: {
    userId,
    voteType: 'FREE',
    status: 'ACTIVE',
    votedAt: { gte: todayStart },
  },
});
if (existingVote) {
  throw new AlreadyVotedError();
}
await prisma.vote.create({ /* ... */ });

DB層(PostgreSQL の部分一意インデックス)

-- migrations/xxxx_add_daily_free_vote_constraint/migration.sql
CREATE UNIQUE INDEX votes_user_free_daily_unique
ON votes (user_id, (DATE(voted_at AT TIME ZONE 'Asia/Tokyo')))
WHERE vote_type = 'FREE' AND status = 'ACTIVE';

Prisma は部分一意インデックスを直接サポートしないため、生SQLのマイグレーションで追加する。

---

インデックス戦略

インデックス	目的
votes (candidate_id, status)	候補者の得票数集計
votes (user_id, vote_type, voted_at)	当日無料投票チェック
votes (candidate_id, user_id, status)	応援者ランキング
votes (purchase_id)	払戻時の一括無効化
users (provider, provider_user_id) UNIQUE	OAuth ログイン時のユーザー特定
purchases (payment_intent_id) UNIQUE	Stripe Webhook 冪等性
candidates (display_order)	候補者一覧の表示順
candidate_images (candidate_id, order)	候補者ごとの画像取得

---

主要な集計クエリ

候補者の現在得票数(全候補者)

候補者の表示用情報と得票数集計を1クエリで取得する。画像本体は select せず、画像IDだけ取得する点に注意。

const candidates = await prisma.candidate.findMany({
  orderBy: { displayOrder: 'asc' },  // home画面はID/displayOrder順
  select: {
    id: true,
    name: true,
    region: true,
    age: true,
    height: true,
    occupation: true,
    hobbies: true,
    specialties: true,
    motto: true,
    dream: true,
    message: true,
    displayOrder: true,
    images: {
      select: {
        id: true,
        order: true,
        updatedAt: true,
      },
      orderBy: { order: 'asc' },
    },
    _count: {
      select: {
        votes: { where: { status: 'ACTIVE' } },
      },
    },
  },
});

得票数は _count.votes で取得できる。画像本体は配信エンドポイント経由で取得するため、ここでは ID と updatedAt(キャッシュバスト用)だけあれば十分。

候補者ごとの応援者ランキング(上位5名)

const topVoters = await prisma.vote.groupBy({
  by: ['userId'],
  where: {
    candidateId,
    status: 'ACTIVE',
  },
  _count: { _all: true },
  orderBy: { _count: { userId: 'desc' } },
  take: 5,
});

const userIds = topVoters.map(v => v.userId);
const users = await prisma.user.findMany({
  where: { id: { in: userIds } },
  select: { id: true, name: true },
});

当日無料投票したか

import { startOfDay } from 'date-fns';
import { utcToZonedTime } from 'date-fns-tz';

const tokyoNow = utcToZonedTime(new Date(), 'Asia/Tokyo');
const todayStart = startOfDay(tokyoNow);

const exists = await prisma.vote.findFirst({
  where: {
    userId,
    voteType: 'FREE',
    status: 'ACTIVE',
    votedAt: { gte: todayStart },
  },
});

購入と Vote の同時生成(トランザクション)

await prisma.$transaction(async (tx) => {
  const purchase = await tx.purchase.create({
    data: {
      userId,
      candidateId,
      creditPackageId: pkg.id,
      packageNameSnapshot: pkg.name,
      packageCreditsSnapshot: pkg.credits,
      packagePriceJpySnapshot: pkg.priceJpy,
      paymentProvider: 'stripe',
      paymentIntentId,
      status: 'SUCCEEDED',
      completedAt: new Date(),
    },
  });

  await tx.vote.createMany({
    data: Array.from({ length: pkg.credits }).map(() => ({
      userId,
      candidateId,
      voteType: 'PAID' as const,
      purchaseId: purchase.id,
      status: 'ACTIVE' as const,
    })),
  });

  return purchase;
});

払戻時の Vote 一括無効化

await prisma.$transaction(async (tx) => {
  await tx.purchase.update({
    where: { id: purchaseId },
    data: { status: 'REFUNDED' },
  });
  await tx.vote.updateMany({
    where: { purchaseId },
    data: { status: 'INVALIDATED' },
  });
});

画像のアップロード(管理画面から)

async function uploadCandidateImage(
  candidateId: string,
  file: File,
  order: number,
) {
  const buffer = Buffer.from(await file.arrayBuffer());
  
  return prisma.candidateImage.create({
    data: {
      candidateId,
      data: buffer,
      mime: file.type,
      order,
    },
  });
}

画像の差し替え

async function replaceCandidateImage(
  imageId: string,
  newFile: File,
) {
  const buffer = Buffer.from(await newFile.arrayBuffer());
  
  return prisma.candidateImage.update({
    where: { id: imageId },
    data: {
      data: buffer,
      mime: newFile.type,
      // updatedAt が自動更新されるためURLのキャッシュバストが効く
    },
  });
}

---

マイグレーション戦略

初期セットアップ

# Prisma スキーマからマイグレーション生成
npx prisma migrate dev --name init

# 部分一意インデックスを追加
npx prisma migrate dev --name add_daily_free_vote_constraint --create-only
# → migration.sql を編集して上記の CREATE UNIQUE INDEX を追加
npx prisma migrate dev

Production への適用

npx prisma migrate deploy

CI/CD パイプラインで自動実行する。

---

Seed データ

prisma/seed.ts で初期データを投入する。

CreditPackage(必須・本番でも使用)

await prisma.creditPackage.createMany({
  data: [
    { name: '10票パッケージ',  credits:  10, priceJpy:  1000, isPopular: false, displayOrder: 1 },
    { name: '35票パッケージ',  credits:  35, priceJpy:  3000, isPopular: true,  displayOrder: 2 },
    { name: '60票パッケージ',  credits:  60, priceJpy:  5000, isPopular: false, displayOrder: 3 },
    { name: '150票パッケージ', credits: 150, priceJpy: 10000, isPopular: false, displayOrder: 4 },
  ],
});

VotingPeriod(初期開催の登録)

開発・初期構築用のシード。現開催の値を初期投入する例。

await prisma.votingPeriod.create({
  data: {
    startsAt: new Date('2026-04-01T00:00:00+09:00'),
    endsAt:   new Date('2026-04-30T23:59:59+09:00'),
    isActive: true,
  },
});

Candidate + CandidateImage(初期一括投入)

運営側から提供されたCSV/JSONとjpg画像ファイル群を読み込んで投入するスクリプト:

// scripts/seed-candidates.ts
import fs from 'fs/promises';
import path from 'path';
import { parse } from 'csv-parse/sync';
import { prisma } from '@/lib/prisma';

interface CandidateRow {
  name: string;
  region: string;
  age: number;
  height: number;
  occupation: string;
  hobbies: string;       // カンマ区切り
  specialties: string;   // カンマ区切り
  motto: string;
  dream: string;
  message: string;
  displayOrder: number;
  imageFiles: string;    // セミコロン区切りのファイル名
}

async function seedCandidates() {
  const csvText = await fs.readFile('./seed-data/candidates.csv', 'utf-8');
  const rows: CandidateRow[] = parse(csvText, { columns: true });

  for (const row of rows) {
    const candidate = await prisma.candidate.create({
      data: {
        name: row.name,
        region: row.region,
        age: Number(row.age),
        height: Number(row.height),
        occupation: row.occupation,
        hobbies: row.hobbies.split(',').map(s => s.trim()),
        specialties: row.specialties.split(',').map(s => s.trim()),
        motto: row.motto,
        dream: row.dream,
        message: row.message,
        displayOrder: Number(row.displayOrder),
      },
    });

    const imageFiles = row.imageFiles.split(';').map(s => s.trim());
    for (let i = 0; i < imageFiles.length; i++) {
      const filePath = path.join('./seed-data/images', imageFiles[i]);
      const buffer = await fs.readFile(filePath);
      const ext = path.extname(filePath).toLowerCase();
      const mime = ext === '.png' ? 'image/png' : 'image/jpeg';
      
      await prisma.candidateImage.create({
        data: {
          candidateId: candidate.id,
          data: buffer,
          mime,
          order: i,  // 0 がメイン画像
        },
      });
    }
  }
}

package.json に登録:

{
  "scripts": {
    "seed": "tsx prisma/seed.ts",
    "seed:candidates": "tsx scripts/seed-candidates.ts"
  }
}

配置するファイル構成例

seed-data/
├── candidates.csv
└── images/
    ├── alisa-1.jpg
    ├── alisa-2.jpg
    ├── alisa-3.jpg
    ├── ...

---

パフォーマンス考慮事項

候補者一覧の得票集計

規模	戦略
〜10万票	都度 GROUP BY で集計、インデックスで十分
10万〜100万票	unstable_cache でキャッシュ(30秒程度の revalidate)
100万票超	Materialized View または candidate_vote_count キャッシュテーブルを別途用意

具体的なキャッシュ設定は steering/tech.md の「キャッシュポリシー(全画面共通)」を参照。

画像取得のN+1対策

候補者一覧画面で「N人 × M枚の画像取得」をしないために:

• 一覧画面では各候補者の メイン画像のID だけを取得(order = 0 の1件)
• ブラウザが <Image src="/api/images/[id]"> で個別配信エンドポイントにアクセス
• CDNでキャッシュされるので、2回目以降のアクセスは DB に到達しない

詳細画面では複数画像のIDを取得(画像本体は同様にエンドポイント経由)。

DB帯域

候補者画像のサイズ感:

• 候補者10名 × 各5枚 = 約50枚
• 1枚あたり 200KB〜2MB
• 合計 25MB 程度
• Neon の無料枠(0.5GB)で十分余裕

応援者ランキング

Vote.groupBy({ candidateId × userId }) は多対多の集計でやや重いが、(candidate_id, user_id, status) の複合インデックスで対応可能。上位5件のみ取得するため、結果セットは小さい。unstable_cache で60秒程度キャッシュする想定。

---

テスト戦略

対象	テスト
Prismaクエリ(集計・順位計算)	Vitest + テスト用DB(Docker Compose / Neon dev branch)
トランザクション境界(購入→投票一括生成)	統合テスト、ロールバック確認
部分一意インデックス(1日1票)	同時並行リクエストでのDB制約発火を確認
払戻時のVote無効化	E2E(Playwright + Stripe テストモード)
画像配信エンドポイント	レスポンス内容・キャッシュヘッダーの検証
画像アップロード	不正ファイル拒否、サイズ上限の検証

---

関連ドキュメント

• requirements.md — エンティティ・関係・整合性ルール
• ../voting-flow/design.md — Voting Context との接続
• ../credit-purchase/design.md — Purchase 作成のフック
• ../social-login/design.md — User 作成のフック
• ../admin/design.md — 候補者・画像の管理画面
• ../../steering/tech.md — DB/ORM 技術選定、セキュリティポリシー、画像配信