D1 数据库

关系型数据的唯一真相源。边缘上的 SQLite。

什么是 D1？

Cloudflare D1 是一个无服务器 SQLite 数据库，运行在边缘，与 Workers 深度集成。

在 AI World Sim 中，D1 存储：

• 👤 所有 NPC 记录（状态、性格、关系）
• 📍 世界状态（时钟、天气、地点）
• 💾 记忆（短期、中期）
• 📖 日记条目
• 💰 钱包和交易记录
• 💡 玩家建议

语义记忆由 Vectorize 处理，其余所有数据都在 D1。

---

表结构概览

---

各表定义

npc

CREATE TABLE npc (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  age INTEGER NOT NULL,
  gender TEXT NOT NULL,
  profession TEXT NOT NULL,
  home_location_id TEXT NOT NULL,
  family_json TEXT NOT NULL,
  backstory TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  alive INTEGER NOT NULL DEFAULT 1,
  death_day INTEGER,
  FOREIGN KEY (home_location_id) REFERENCES location(id)
);

npc_personality

CREATE TABLE npc_personality (
  npc_id TEXT PRIMARY KEY,
  openness REAL NOT NULL,
  conscientiousness REAL NOT NULL,
  extraversion REAL NOT NULL,
  agreeableness REAL NOT NULL,
  neuroticism REAL NOT NULL,
  romantic REAL NOT NULL,
  aggressive REAL NOT NULL,
  religious REAL NOT NULL,
  alcohol_tolerance REAL NOT NULL,
  risk_taking REAL NOT NULL,
  parental REAL NOT NULL,
  gossipy REAL NOT NULL,
  version INTEGER NOT NULL DEFAULT 1,
  FOREIGN KEY (npc_id) REFERENCES npc(id)
);

npc_state

CREATE TABLE npc_state (
  npc_id TEXT PRIMARY KEY,
  current_location_id TEXT NOT NULL,
  activity TEXT NOT NULL,
  hunger REAL NOT NULL,
  energy REAL NOT NULL,
  social REAL NOT NULL,
  hygiene REAL NOT NULL,
  fun REAL NOT NULL,
  health REAL NOT NULL,
  emotion_label TEXT NOT NULL,
  emotion_intensity REAL NOT NULL,
  mood TEXT NOT NULL,
  updated_tick INTEGER NOT NULL
);

npc_short_term_memory

CREATE TABLE npc_short_term_memory (
  id TEXT PRIMARY KEY,
  npc_id TEXT NOT NULL,
  world_day INTEGER NOT NULL,
  world_hour INTEGER NOT NULL,
  type TEXT NOT NULL,
  content TEXT NOT NULL,
  importance REAL NOT NULL,
  decay_at_day INTEGER NOT NULL,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (npc_id) REFERENCES npc(id)
);

CREATE INDEX idx_stm_npc_day ON npc_short_term_memory(npc_id, world_day);
CREATE INDEX idx_stm_decay ON npc_short_term_memory(decay_at_day);

npc_daily_digest

CREATE TABLE npc_daily_digest (
  id TEXT PRIMARY KEY,
  npc_id TEXT NOT NULL,
  day INTEGER NOT NULL,
  summary TEXT NOT NULL,
  key_events_json TEXT NOT NULL,
  emotional_tone TEXT NOT NULL,
  relationship_changes_json TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (npc_id) REFERENCES npc(id),
  UNIQUE(npc_id, day)
);

npc_diary

CREATE TABLE npc_diary (
  id TEXT PRIMARY KEY,
  npc_id TEXT NOT NULL,
  day INTEGER NOT NULL,
  hour INTEGER NOT NULL DEFAULT 22,
  text TEXT NOT NULL,
  mood TEXT NOT NULL,
  prompt_hash TEXT NOT NULL,
  generated_at INTEGER NOT NULL,
  generation_cost INTEGER NOT NULL,
  FOREIGN KEY (npc_id) REFERENCES npc(id),
  UNIQUE(npc_id, day)
);

CREATE INDEX idx_diary_npc_day ON npc_diary(npc_id, day DESC);

npc_relationships

CREATE TABLE npc_relationships (
  npc_a_id TEXT NOT NULL,
  npc_b_id TEXT NOT NULL,
  affinity REAL NOT NULL,
  familiarity REAL NOT NULL,
  last_interaction_day INTEGER,
  interaction_count INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (npc_a_id, npc_b_id)
);

location

CREATE TABLE location (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  type TEXT NOT NULL,
  x INTEGER NOT NULL,
  y INTEGER NOT NULL,
  open_hour INTEGER NOT NULL,
  close_hour INTEGER NOT NULL,
  open_days_json TEXT NOT NULL,
  metadata_json TEXT NOT NULL DEFAULT '{}'
);

world_state

CREATE TABLE world_state (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  updated_at INTEGER NOT NULL
);

wallet

CREATE TABLE wallet (
  npc_id TEXT PRIMARY KEY,
  balance INTEGER NOT NULL,
  last_payday_day INTEGER NOT NULL,
  daily_spend_avg REAL NOT NULL
);

transaction

CREATE TABLE transaction (
  id TEXT PRIMARY KEY,
  npc_id TEXT NOT NULL,
  day INTEGER NOT NULL,
  hour INTEGER NOT NULL,
  amount INTEGER NOT NULL,
  type TEXT NOT NULL,
  counterparty_id TEXT,
  location_id TEXT,
  description TEXT
);

CREATE INDEX idx_tx_npc_day ON transaction(npc_id, day);

suggestion

CREATE TABLE suggestion (
  id TEXT PRIMARY KEY,
  npc_id TEXT NOT NULL,
  text TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending',
  submitted_at INTEGER NOT NULL,
  submitted_day INTEGER NOT NULL,
  delivered_at INTEGER,
  ip_hash TEXT,
  FOREIGN KEY (npc_id) REFERENCES npc(id)
);

CREATE INDEX idx_suggestion_status ON suggestion(status, submitted_day);

---

访问模式

读取：世界快照（玩家 API）

const snapshot = await env.DB.prepare(`
  SELECT
    n.id, n.name, n.age, n.profession,
    s.current_location_id, s.activity, s.emotion_label,
    l.x, l.y
  FROM npc n
  JOIN npc_state s ON n.id = s.npc_id
  JOIN location l ON s.current_location_id = l.id
  WHERE n.alive = 1
`).all();

写入：Tick 结束（原子操作）

await env.DB.batch([
  env.DB.prepare(`UPDATE npc_state SET hunger = ?, energy = ?, ... WHERE npc_id = ?`).bind(...),
  env.DB.prepare(`INSERT INTO npc_short_term_memory (...) VALUES (...)`).bind(...),
]);

---

迁移

migrations/
├── 0001_initial.sql
├── 0002_add_mood.sql
└── 0003_add_decay.sql

wrangler d1 migrations apply ai-world-sim --local
wrangler d1 migrations apply ai-world-sim --remote

---

容量估算（v1）

表	行数（约）	大小
npc	30	<10KB
npc_state	30	<10KB
npc_short_term_memory	~1500/天	~500KB/天
npc_daily_digest	30/天	~50KB/天
npc_diary	30/天	~100KB/天
v1 总计	—	~700KB/天

v1 运行一年后：约 250MB，远在 D1 限制之内。