COTEN（コテンラジオ運営）「世界史データベース」

データ構造とデータパイプライン調査メモ（公開情報ベース）

この文書は、株式会社COTENが公開している記事・採用情報などを元に、世界史データベースの**データ構造（モデル）とデータパイプライン（分析基盤／複合処理）**を技術者向けに整理したものです。
非公開部分は推測しません。断言できるのは出典リンクの範囲です。

1. 全体像：2つの「データの道」

世界史データベースには大きく2系統のデータフローがあります。

プロダクト（本番）系

メインDB：PostgreSQL
ここに「入力アプリ」が書誌（書籍等）を元にデータを登録する
ORM：Prisma（スキーマファイルがSoTとして扱われる）

分析・品質管理（DWH）系

DWH：Snowflake
本番DBのスナップショットをS3に日次エクスポート → Snowflakeにロード
dbtで3層（Raw / DWH / Mart）に整形し、品質管理と可視化を支える

2. プロダクトDBのデータ構造：カテゴリ階層（ltree）中心に見えるもの

2.1 Categoryを「階層ラベル」で表現（PostgreSQL ltree）

COTENは、人物・出来事・場所などのEntityや、Entity同士の関連（Relation）にCategoryを持たせ、
人物なら actor.person、出来事なら event のような「ラベルパス（materialized path）」でカテゴリを割り当てる、と説明しています。

例（記事中の例。実データとは異なると明記あり）
- actor.person.priest
- actor.person.warrior
- actor.person.warrior.samurai

このカテゴリ階層を扱うのに、PostgreSQLの ltree 型（拡張）を使っています。

2.2 「木」ではなくDAGも許容するために categories と category_paths を分離

COTENは、categories テーブル（カテゴリ定義）と、category_paths テーブル（ltreeのpath）を分離して管理しています。
理由は、categories に path を埋め込むと「各ノードが単一パス＝厳密な木構造」しか表現できず、世界史カテゴリでは制約が厳しいためです。
分離により、**1つのカテゴリノードが複数のpathを持つ＝複数親（DAG）**を許容できます。

Prismaモデル例（記事の抜粋）

model Category {
  id String @id @db.Uuid
  // ...
}

model CategoryPath {
  categoryId String               @id @map("category_id") @db.Uuid
  path       Unsupported("ltree") @unique
  // ...
}

2.3 Prismaとltreeのギャップ（Unsupported型＋Raw SQL）

記事時点では Prisma が ltree を直接サポートしていないため、

Prisma schema上は Unsupported("ltree")
クエリは $queryRaw / $executeRaw でSQLを書く
SQLインジェクション対策としてパラメータ化クエリ必須（Unsafe系は使わない）

という運用上の工夫が必要、とされています。

3. “同定・複合（Compound）”のためのデータ構造（Evidence → Record → Composer → Compound Record）

世界史データ特有の問題（表記ゆれ、別名、同名別人、出来事の粒度差など）に対処するため、COTENは「同定・複合（Compound）」を中核プロセスとして位置づけています。

3.1 4段階モデル

Evidence：書籍・論文など資料情報を元に入力
Record：Evidenceを加工・整形して作る
Composer：Record同士を比較して「同一（または統合）マッピング」を作る
Compound Record：Composerに基づき統合された最終レコード

このモデルは、処理責務の分離（入力→整形→マッピング→統合）としても読みやすいです。

3.2 出来事（Event）の同一候補生成：3つのスコア合成

出来事データはWikidata ID付与が難しいため、「既存イベント集合から同一候補を出す」アルゴリズムを設計しています。
記事では以下の3スコアを合成しています：

ユリウス通日（date〜endDate＋バッファ）重複率
地域の同一性（地名一致／人物から推定／MLで補完）
ベクトル空間の類似度（Nameの埋め込み＋Relationから文字列補完）

地域補完のMLはメタモデル方式として

SVM / XGBoost / MLP → Random Forestで最終予測

という構成が記載されています。

3.3 LLMによる一次判定（Leonardo）：多数決＋人が承認

同一候補リストをLLMアプリ「Leonardo」に渡し、

5回判定させて多数決
その結果をデータマネージャがルールに則って確認・承認

というフローが説明されています。

4. 分析基盤（RDS→S3→Snowflake）のデータパイプライン詳細

COTENは、プロダクトDB（PostgreSQL）とは別に Snowflake をDWHとして運用し、

データ拡充状況の共有
時系列比較による品質管理

を行うとしています。

4.1 主要スタック（記事に記載）

RDBMS: PostgreSQL（Amazon RDS）
Data Lake: Amazon S3
DWH: Snowflake
ETL/ELT: dbt / Step Functions / ECS(Fargate) / EventBridge
Document Hosting: S3 / CloudFront
CI/CD: GitHub Actions
IaC: Terraform

4.2 データの流れ（安全性＝「本番に負荷をかけない」）

プロダクトに負荷をかけないために、本番DBのDaily Backup（RDS Automated Snapshot） を起点に分析環境へ同期します。

Step 1: RDS → S3（Parquetで日次ダンプ）

RDSのAutomated Snapshot作成をトリガに EventBridge 経由で Step Functions 起動
Step Functions が RDSのS3エクスポート機能 を実行し、S3に Parquet形式 で保存

多重実行防止
RDSのスナップショットは「日次バックアップ以外」にも作成され得る（例：バージョンアップ等）。
そのため、同日のデータが重複取り込みされないように、S3のキー空間を日付で区切り、既に当日Keyがある場合はスキップする、という実装が紹介されています（Lambda不要でStep Functionsのサービス統合で完結）。

クロスアカウント＋暗号化
RDSはプロダクトアカウント、S3は分析基盤アカウントにあり、Cross-Accountエクスポートで暗号化できるようKMSアクセス制御を行うとされています。

Step 2: S3 → Snowflake（dbtで Raw → DWH → Mart）

S3格納をトリガに EventBridge → Step Functions
Step Functionsが ECS(Fargate) 上で dbt コンテナ起動
dbtがS3の生データをSnowflakeのRaw/Lake層にロード
その後 dbtモデルに従い Raw(Lake) → DWH → Mart の順に変換

4.3 Snowflake上の3層アーキテクチャ（Raw / DWH / Mart）

Raw Layer：S3から取り込んだ生データ。カラム名や型変更を吸収するWrapper Viewも提供。再加工の元データとして保持（主にエンジニア向け）
DWH Layer：共通指標・ビジネスロジックを定義する“セマンティック層”的な位置づけ（非エンジニアでSQLを書く人はここから触る想定）
DataMart Layer：ダッシュボード／レポート用に特化したデータセット

5. スキーマ追従の自動化：Prisma Schema をSoTにして dbt Models を生成

分析基盤の運用負荷を減らすために、

プロダクト開発：Prisma schema から DDL生成
分析基盤：同じ Prisma schema から dbt Models を自動生成

という仕組みを構築していると説明されています。

実装としては、Prisma schema からASTを取るために @loancrate/prisma-schema-parser（非公式パーサ）を利用し、ASTからdbtモデルを生成するスクリプトを作成。
GitHub Actionsで Prisma schema 変更時に生成を走らせる、という形です。

型変換（例：geometry / ltree などDWH側でサポートが薄い型）は変換関数で吸収する例も示されています（Unsupported型をStringに落とすなど）。

6. Compound（複合）処理とSnowflakeの役割の変化

Compoundの実装は流動的で、Snowflake上でCompound Recordを生成していた構成から、
バックエンドサービス側でCompound Record生成を担う構成へ変更する方針が紹介されています。

6.1 旧：ComposerをバックエンドDB→日次スナップショット→SnowflakeでCompound Record生成

ComposerはバックエンドDBに保存
Composerを日次でスナップショットしてSnowflakeに保存
Snowflake上で dbt を使い、ComposerをもとにCompound Recordを日次生成（増分更新したい等の理由）

6.2 新：バックエンドでCompound Record生成（検証サイクル短縮＋責務分離）

背景として、

Composer作成ロジックが頻繁に改善され、検証頻度が高い
Snowflake側で生成だとデータ転送・表示に時間がかかり、アーキテクチャも複雑
バックアップ上書き問題など運用リスク

が挙げられています。

方針変更後は、

Composer更新時に関連するCompound Recordをバックエンドで作る（結果がすぐ見える）
Snowflakeは外部公開用などに寄せて責務を明確化

という整理です。

7. 図：パイプライン俯瞰（Mermaid）

7.1 分析基盤（RDS→S3→Snowflake→Mart）

flowchart LR
  subgraph Product["プロダクト系"]
    RDS[(PostgreSQL on RDS)]
    APP[入力アプリ / Backend]
  end

  subgraph Analytics["分析・品質管理系"]
    S3[(S3 Data Lake<br/>Parquet)]
    SF[(Snowflake)]
    DBT[dbt on ECS(Fargate)]
    MART[(Mart / Dashboards)]
  end

  RDS -->|Automated Snapshot| EB[EventBridge]
  EB --> SFN1[Step Functions: Export]
  SFN1 -->|RDS S3 Export| S3
  S3 --> EB2[EventBridge]
  EB2 --> SFN2[Step Functions: Run dbt]
  SFN2 --> DBT
  DBT --> SF
  SF --> MART

7.2 Compound（概念モデル）

flowchart LR
  Evidence --> Record --> Composer --> CompoundRecord
  Composer -->|判定支援| LLM[Leonardo (LLM)]
  LLM -->|多数決結果| DM[Data Manager 承認]
  DM --> Composer

8. 参考リンク（一次情報）

COTENのデータ分析基盤とその運用を支える技術（2025-10-31, Zenn）
https://zenn.dev/coten/articles/be8a8cb22947f0
ltreeを使って実現する階層データ構造（2025-09-16, Zenn）
https://zenn.dev/coten/articles/97ef16f24b7d44
歴史データを同定・複合するには？（2025-06-27, Zenn）
https://zenn.dev/coten/articles/dbb49948e46214
豊臣秀吉を結合する：歴史データのCompound実装（2025-09-18, Zenn）
https://zenn.dev/coten/articles/ed420affbaa4d9

coten_history_db_data_structure_pipeline.md