D1 Database

The relational source of truth. SQLite at the edge.

What is D1?

Cloudflare D1 is a serverless SQLite database that runs at the edge, integrated with Workers.

For AI World Sim, D1 stores:

• 👤 All NPC records (state, personality, relationships)
• 📍 World state (clock, weather, locations)
• 💾 Memory (short-term, mid-term)
• 📖 Diary entries
• 💰 Wallets and transactions
• 💡 Suggestions

Vectorize handles semantic memory. Everything else is D1.

---

Schema Overview

---

Tables

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);

---

Access Patterns

Read: World Snapshot (Player 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();

Write: Tick End (Atomic)

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

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

---

Size Estimates (v1)

Table	Rows (approx)	Size
npc	30	<10KB
npc_state	30	<10KB
npc_short_term_memory	~1500/day	~500KB/day
npc_daily_digest	30/day	~50KB/day
npc_diary	30/day	~100KB/day
Total v1	—	~700KB/day

After a year of v1 operation: ~250MB. Well within D1 limits.