D1 Schema

Conventions

• Primary keys: TEXT UUID (crypto.randomUUID()). No auto-increment integers — UUIDs are safer across multiple Workers and future shard scenarios.
• Timestamps: INTEGER Unix epoch seconds. No DATETIME type — SQLite stores it as text; epoch is simpler for arithmetic.
• JSON blobs: TEXT NOT NULL DEFAULT '{}' for variable-structure data (event effects, expedition results, item stats). Query on indexed columns; parse JSON in application code.
• All foreign keys declared but SQLite FK enforcement must be enabled per connection: PRAGMA foreign_keys = ON.
• Migrations: numbered SQL files in migrations/ folder. Applied with wrangler d1 migrations apply aelghar-db --env production.

---

Migration Strategy

migrations/
  0001_initial_accounts.sql
  0002_characters.sql
  0003_skills_inventory.sql
  0004_expeditions_board.sql
  0005_market.sql
  0006_towns.sql
  0007_guilds.sql
  ...

Rules: - Each migration is append-only: CREATE TABLE, CREATE INDEX, ALTER TABLE ... ADD COLUMN. Never DROP or RENAME a production column without a planned migration pair. - Migrations run in numeric order. Never reorder or renumber. - Every migration wrapped in a transaction: BEGIN; ...; COMMIT;. - Test migration on staging D1 before applying to production. - wrangler d1 migrations apply is idempotent — already-applied migrations are skipped.

---

Schema

Accounts

CREATE TABLE account (
  id           TEXT PRIMARY KEY,
  email        TEXT NOT NULL UNIQUE,          -- lowercase normalised
  display_name TEXT NOT NULL,
  password_hash TEXT NOT NULL,                -- base64 PBKDF2-SHA256
  salt         TEXT NOT NULL,                 -- base64 random 16 bytes
  created_at   INTEGER NOT NULL,
  last_login   INTEGER
);
CREATE INDEX idx_account_email ON account(email);

Characters

CREATE TABLE character (
  id              TEXT PRIMARY KEY,
  account_id      TEXT NOT NULL REFERENCES account(id),
  name            TEXT NOT NULL,
  lineage         TEXT NOT NULL,
  village_origin  TEXT NOT NULL,
  archetype       TEXT NOT NULL,
  background      TEXT NOT NULL,
  location_id     TEXT NOT NULL,             -- current town/village id
  coin            INTEGER NOT NULL DEFAULT 0,
  created_at      INTEGER NOT NULL,
  last_active     INTEGER
);
CREATE INDEX idx_character_account ON character(account_id);
CREATE INDEX idx_character_location ON character(location_id);

CREATE TABLE character_flag (
  character_id TEXT NOT NULL REFERENCES character(id),
  flag_key     TEXT NOT NULL,
  flag_value   TEXT NOT NULL DEFAULT 'true',
  set_at       INTEGER NOT NULL,
  PRIMARY KEY (character_id, flag_key)
);

CREATE TABLE character_skill (
  character_id TEXT NOT NULL REFERENCES character(id),
  skill_id     TEXT NOT NULL,
  band         INTEGER NOT NULL DEFAULT 0,   -- 0=Untrained … 6=Master
  xp           INTEGER NOT NULL DEFAULT 0,
  updated_at   INTEGER NOT NULL,
  PRIMARY KEY (character_id, skill_id)
);

Inventory

CREATE TABLE inventory_item (
  id           TEXT PRIMARY KEY,
  character_id TEXT NOT NULL REFERENCES character(id),
  item_id      TEXT NOT NULL,                -- content schema item id
  quantity     INTEGER NOT NULL DEFAULT 1,
  quality      TEXT NOT NULL DEFAULT 'standard',  -- standard | fine | masterwork
  slot_index   INTEGER,                      -- NULL = unequipped stack
  created_at   INTEGER NOT NULL
);
CREATE INDEX idx_inventory_character ON inventory_item(character_id);

Expeditions

CREATE TABLE expedition (
  id              TEXT PRIMARY KEY,
  template_id     TEXT NOT NULL,             -- content schema expedition template id
  status          TEXT NOT NULL DEFAULT 'active',  -- active | resolved | cancelled | failed
  location_id     TEXT NOT NULL,             -- departure location
  destination_id  TEXT NOT NULL,
  started_at      INTEGER NOT NULL,
  resolves_at     INTEGER NOT NULL,          -- Unix timestamp when expedition settles
  seed            INTEGER NOT NULL,          -- deterministic resolver seed
  quality_score   REAL,                      -- 0.0–1.0; NULL until resolved
  result_json     TEXT,                      -- resolver output; NULL until resolved
  created_at      INTEGER NOT NULL
);
CREATE INDEX idx_expedition_status ON expedition(status);
CREATE INDEX idx_expedition_resolves ON expedition(resolves_at);

CREATE TABLE expedition_participant (
  expedition_id TEXT NOT NULL REFERENCES expedition(id),
  character_id  TEXT NOT NULL REFERENCES character(id),
  role          TEXT NOT NULL,               -- combat | support | trade | witness
  joined_at     INTEGER NOT NULL,
  PRIMARY KEY (expedition_id, character_id)
);
CREATE INDEX idx_exp_participant_character ON expedition_participant(character_id);

Board

CREATE TABLE board_item (
  id            TEXT PRIMARY KEY,
  location_id   TEXT NOT NULL,
  template_id   TEXT NOT NULL,               -- content schema board order template id
  guild         TEXT NOT NULL,               -- adventurers_hall | crafters_hall | town_board | caravan_board
  rank          TEXT NOT NULL,               -- F | E | D | C | B | A | S
  status        TEXT NOT NULL DEFAULT 'open',  -- open | accepted | completed | expired | failed
  posted_at     INTEGER NOT NULL,
  expires_at    INTEGER NOT NULL,
  coin_reward   INTEGER NOT NULL,
  data_json     TEXT NOT NULL DEFAULT '{}',  -- template-specific variables resolved at generation time
  generated     INTEGER NOT NULL DEFAULT 1  -- 1=system generated, 0=manually posted
);
CREATE INDEX idx_board_location ON board_item(location_id, status);
CREATE INDEX idx_board_expires ON board_item(expires_at);

CREATE TABLE board_acceptance (
  id            TEXT PRIMARY KEY,
  board_item_id TEXT NOT NULL REFERENCES board_item(id),
  character_id  TEXT NOT NULL REFERENCES character(id),
  expedition_id TEXT REFERENCES expedition(id),
  accepted_at   INTEGER NOT NULL,
  completed_at  INTEGER,
  UNIQUE (board_item_id, character_id)       -- one acceptance per character per order
);
CREATE INDEX idx_acceptance_character ON board_acceptance(character_id);

Market

CREATE TABLE market_order (
  id              TEXT PRIMARY KEY,
  town_id         TEXT NOT NULL,
  character_id    TEXT NOT NULL REFERENCES character(id),
  item_id         TEXT NOT NULL,
  order_type      TEXT NOT NULL,             -- sell | buy
  quantity        INTEGER NOT NULL,
  quantity_filled INTEGER NOT NULL DEFAULT 0,
  price_per_unit  INTEGER NOT NULL,
  fee_paid        INTEGER NOT NULL DEFAULT 0,
  status          TEXT NOT NULL DEFAULT 'active',  -- active | filled | partial | expired | cancelled
  posted_at       INTEGER NOT NULL,
  expires_at      INTEGER NOT NULL
);
CREATE INDEX idx_order_town_item ON market_order(town_id, item_id, status);
CREATE INDEX idx_order_character ON market_order(character_id);
CREATE INDEX idx_order_expires ON market_order(expires_at);

CREATE TABLE market_fill (
  id         TEXT PRIMARY KEY,
  order_id   TEXT NOT NULL REFERENCES market_order(id),
  buyer_id   TEXT NOT NULL REFERENCES character(id),
  seller_id  TEXT NOT NULL REFERENCES character(id),
  quantity   INTEGER NOT NULL,
  price      INTEGER NOT NULL,
  tax_paid   INTEGER NOT NULL DEFAULT 0,
  filled_at  INTEGER NOT NULL
);
CREATE INDEX idx_fill_order ON market_fill(order_id);

CREATE TABLE auction_lot (
  id              TEXT PRIMARY KEY,
  town_id         TEXT NOT NULL,
  character_id    TEXT NOT NULL REFERENCES character(id),
  item_id         TEXT NOT NULL,
  quantity        INTEGER NOT NULL,
  reserve_price   INTEGER NOT NULL,
  current_bid     INTEGER NOT NULL DEFAULT 0,
  current_bidder  TEXT REFERENCES character(id),
  bid_count       INTEGER NOT NULL DEFAULT 0,
  extension_count INTEGER NOT NULL DEFAULT 0,
  status          TEXT NOT NULL DEFAULT 'active',  -- active | sold | unsold | cancelled
  ends_at         INTEGER NOT NULL,
  posted_at       INTEGER NOT NULL
);
CREATE INDEX idx_auction_town ON auction_lot(town_id, status);
CREATE INDEX idx_auction_ends ON auction_lot(ends_at);

CREATE TABLE auction_bid (
  id         TEXT PRIMARY KEY,
  lot_id     TEXT NOT NULL REFERENCES auction_lot(id),
  bidder_id  TEXT NOT NULL REFERENCES character(id),
  amount     INTEGER NOT NULL,
  bid_at     INTEGER NOT NULL
);
CREATE INDEX idx_bid_lot ON auction_bid(lot_id);

CREATE TABLE warehouse_hold (
  id           TEXT PRIMARY KEY,
  town_id      TEXT NOT NULL,
  character_id TEXT NOT NULL REFERENCES character(id),
  item_id      TEXT NOT NULL,
  quantity     INTEGER NOT NULL,
  quality      TEXT NOT NULL DEFAULT 'standard',
  source       TEXT NOT NULL,                -- filled_order | expedition_return | deposit
  daily_fee    INTEGER NOT NULL DEFAULT 0,
  held_since   INTEGER NOT NULL,
  expires_at   INTEGER NOT NULL
);
CREATE INDEX idx_warehouse_character ON warehouse_hold(character_id);
CREATE INDEX idx_warehouse_expires ON warehouse_hold(expires_at);

CREATE TABLE market_price_index (
  town_id       TEXT NOT NULL,
  item_id       TEXT NOT NULL,
  current_index REAL NOT NULL,
  instant_index REAL NOT NULL,
  scarcity_mult REAL NOT NULL DEFAULT 1.0,
  route_mult    REAL NOT NULL DEFAULT 1.0,
  event_mult    REAL NOT NULL DEFAULT 1.0,
  tax_mult      REAL NOT NULL DEFAULT 1.0,
  liquidity_mult REAL NOT NULL DEFAULT 1.0,
  updated_at    INTEGER NOT NULL,
  PRIMARY KEY (town_id, item_id)
);

CREATE TABLE market_price_history (
  id         TEXT PRIMARY KEY,
  town_id    TEXT NOT NULL,
  item_id    TEXT NOT NULL,
  index_value REAL NOT NULL,
  recorded_at INTEGER NOT NULL
);
CREATE INDEX idx_price_history ON market_price_history(town_id, item_id, recorded_at);

Towns

CREATE TABLE town (
  id             TEXT PRIMARY KEY,
  name           TEXT NOT NULL,
  region         TEXT NOT NULL,
  population     INTEGER NOT NULL DEFAULT 100,
  health_state   TEXT NOT NULL DEFAULT 'stable',  -- failing | strained | stable | growing | prosperous
  controlling_guild_id TEXT,
  last_tick_at   INTEGER,
  created_at     INTEGER NOT NULL
);

CREATE TABLE town_reserve (
  town_id        TEXT NOT NULL REFERENCES town(id),
  class          TEXT NOT NULL,              -- food | water | fuel | medicine | building
  units          REAL NOT NULL DEFAULT 0,
  band           TEXT NOT NULL DEFAULT 'stable',
  updated_at     INTEGER NOT NULL,
  PRIMARY KEY (town_id, class)
);

CREATE TABLE town_daily_ledger (
  id             TEXT PRIMARY KEY,
  town_id        TEXT NOT NULL REFERENCES town(id),
  tick_timestamp INTEGER NOT NULL,
  data_json      TEXT NOT NULL,              -- full tick audit: inbound, consumption, spoilage, bands
  tick_duration_ms INTEGER,
  created_at     INTEGER NOT NULL
);
CREATE INDEX idx_ledger_town_tick ON town_daily_ledger(town_id, tick_timestamp);

CREATE TABLE town_price_index (
  town_id        TEXT NOT NULL REFERENCES town(id),
  category       TEXT NOT NULL,             -- market category key
  index_value    REAL NOT NULL DEFAULT 1.0,
  updated_at     INTEGER NOT NULL,
  PRIMARY KEY (town_id, category)
);

CREATE TABLE town_event (
  id             TEXT PRIMARY KEY,
  town_id        TEXT NOT NULL REFERENCES town(id),
  template_id    TEXT NOT NULL,
  status         TEXT NOT NULL DEFAULT 'active',  -- active | resolved | expired
  tick_started   INTEGER NOT NULL,
  ticks_remaining INTEGER NOT NULL,
  effects_json   TEXT NOT NULL DEFAULT '{}',
  started_at     INTEGER NOT NULL,
  resolved_at    INTEGER
);
CREATE INDEX idx_event_town ON town_event(town_id, status);

Guilds

CREATE TABLE guild (
  id           TEXT PRIMARY KEY,
  name         TEXT NOT NULL UNIQUE,
  tag          TEXT NOT NULL UNIQUE,
  leader_id    TEXT NOT NULL REFERENCES character(id),
  treasury     INTEGER NOT NULL DEFAULT 0,
  founded_at   INTEGER NOT NULL
);

CREATE TABLE guild_member (
  guild_id     TEXT NOT NULL REFERENCES guild(id),
  character_id TEXT NOT NULL REFERENCES character(id),
  role         TEXT NOT NULL DEFAULT 'initiate',  -- leader | officer | member | initiate
  joined_at    INTEGER NOT NULL,
  PRIMARY KEY (guild_id, character_id)
);
CREATE INDEX idx_guild_member_character ON guild_member(character_id);

CREATE TABLE guild_storage_item (
  id           TEXT PRIMARY KEY,
  guild_id     TEXT NOT NULL REFERENCES guild(id),
  town_id      TEXT NOT NULL,
  item_id      TEXT NOT NULL,
  quantity     INTEGER NOT NULL,
  quality      TEXT NOT NULL DEFAULT 'standard',
  deposited_by TEXT NOT NULL REFERENCES character(id),
  deposited_at INTEGER NOT NULL
);
CREATE INDEX idx_guild_storage ON guild_storage_item(guild_id, town_id);

CREATE TABLE guild_treasury_ledger (
  id           TEXT PRIMARY KEY,
  guild_id     TEXT NOT NULL REFERENCES guild(id),
  actor_id     TEXT NOT NULL REFERENCES character(id),
  amount       INTEGER NOT NULL,             -- positive = credit, negative = debit
  reason       TEXT NOT NULL,
  recorded_at  INTEGER NOT NULL
);
CREATE INDEX idx_treasury_guild ON guild_treasury_ledger(guild_id, recorded_at);

CREATE TABLE town_influence_ledger (
  id              TEXT PRIMARY KEY,
  guild_id        TEXT NOT NULL REFERENCES guild(id),
  town_id         TEXT NOT NULL REFERENCES town(id),
  character_id    TEXT NOT NULL REFERENCES character(id),
  raw_influence   REAL NOT NULL,
  effective_influence REAL NOT NULL,
  source          TEXT NOT NULL,            -- contract | delivery | project | event | window_victory
  recorded_at     INTEGER NOT NULL
);
CREATE INDEX idx_influence_guild_town ON town_influence_ledger(guild_id, town_id, recorded_at);

CREATE TABLE departure_slot (
  id               TEXT PRIMARY KEY,
  guild_id         TEXT NOT NULL REFERENCES guild(id),
  posted_by        TEXT NOT NULL REFERENCES character(id),
  destination_id   TEXT NOT NULL,
  template_id      TEXT NOT NULL,
  min_composition  TEXT NOT NULL DEFAULT '{}',  -- JSON: { combat: 2, support: 1 }
  invite_status    TEXT NOT NULL DEFAULT 'guild',  -- guild | open
  notes            TEXT,
  status           TEXT NOT NULL DEFAULT 'open',  -- open | departed | cancelled
  departs_at_tick  INTEGER NOT NULL,
  created_at       INTEGER NOT NULL
);
CREATE INDEX idx_departure_guild ON departure_slot(guild_id, status);

CREATE TABLE departure_commitment (
  slot_id      TEXT NOT NULL REFERENCES departure_slot(id),
  character_id TEXT NOT NULL REFERENCES character(id),
  role         TEXT NOT NULL,
  committed_at INTEGER NOT NULL,
  PRIMARY KEY (slot_id, character_id)
);

Codex

CREATE TABLE codex_entry (
  character_id TEXT NOT NULL REFERENCES character(id),
  entry_id     TEXT NOT NULL,               -- content schema id (creature, item, zone, etc.)
  entry_type   TEXT NOT NULL,               -- creature | item | zone | recipe | lore
  tier         INTEGER NOT NULL DEFAULT 1,  -- 1=basic, 2=detailed, 3=rare lore
  discovered_at INTEGER NOT NULL,
  PRIMARY KEY (character_id, entry_id)
);
CREATE INDEX idx_codex_character ON codex_entry(character_id);

---

Indexes Summary

Critical query patterns that need coverage:

Query	Index
Account lookup by email	idx_account_email
All characters for account	idx_character_account
Board items at location	idx_board_location
Active expeditions for character	idx_exp_participant_character + expedition status
Market orders by town + item	idx_order_town_item
Warehouse holds for character	idx_warehouse_character
Town influence by guild + town	idx_influence_guild_town
Price history for sparkline	idx_price_history
Active events for town	idx_event_town
Expired board orders (cleanup)	idx_board_expires
Messages by recipient	idx_message_recipient
Stat events by type + time	idx_stat_event_type_time
Idempotency key lookup	idx_idempotency_key

---

Player Messages

CREATE TABLE player_message (
  id                   TEXT PRIMARY KEY,
  sender_id            TEXT NOT NULL REFERENCES character(id),
  recipient_id         TEXT NOT NULL REFERENCES character(id),
  subject              TEXT NOT NULL,                -- max 120 chars
  body                 TEXT NOT NULL,                -- plain text, max 2000 chars
  sent_at              INTEGER NOT NULL,
  read_at              INTEGER,
  deleted_by_sender    INTEGER NOT NULL DEFAULT 0,
  deleted_by_recipient INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX idx_message_recipient ON player_message(recipient_id, sent_at DESC);
CREATE INDEX idx_message_sender    ON player_message(sender_id, sent_at DESC);

Character Stat Events (Leaderboard Ledger)

Append-only. Only server-side settlement code writes here.

CREATE TABLE character_stat_event (
  id           TEXT PRIMARY KEY,
  character_id TEXT NOT NULL REFERENCES character(id),
  stat_type    TEXT NOT NULL,   -- expeditions_completed | expeditions_failed | coin_earned | coin_spent
                                -- | items_crafted | items_gathered | town_influence
                                -- | board_orders_filled | elite_kills | boss_kills
  delta        INTEGER NOT NULL,
  occurred_at  INTEGER NOT NULL
);
CREATE INDEX idx_stat_event_type_time ON character_stat_event(stat_type, occurred_at DESC);
CREATE INDEX idx_stat_event_char      ON character_stat_event(character_id, stat_type);

Item Audit Log

Append-only. Required by security baseline — every item/coin creation, destruction, and transfer writes a row. Enables exploit tracing and rollback.

CREATE TABLE item_audit_log (
  id             TEXT PRIMARY KEY,
  event_type     TEXT NOT NULL,               -- create | destroy | transfer | coin_credit | coin_debit
  character_id   TEXT NOT NULL REFERENCES character(id),
  item_id        TEXT,                        -- NULL for coin events
  quantity       INTEGER NOT NULL,
  coin_delta     INTEGER,                     -- populated for coin_credit / coin_debit
  source_ref     TEXT NOT NULL,               -- expedition_id | order_id | admin | settlement
  related_id     TEXT,                        -- counterparty character_id for transfers
  occurred_at    INTEGER NOT NULL,
  notes          TEXT
);
CREATE INDEX idx_audit_character ON item_audit_log(character_id, occurred_at DESC);
CREATE INDEX idx_audit_source    ON item_audit_log(source_ref, occurred_at DESC);

Retention: rows older than 90 days are prunable via scheduled cron. Admin incident tooling reads this table directly.

Idempotency Keys

Every mutating Worker command carries a client-generated UUID. The Worker stores settled keys here and rejects duplicates.

CREATE TABLE idempotency_key (
  key          TEXT PRIMARY KEY,              -- client-generated UUID (crypto.randomUUID())
  endpoint     TEXT NOT NULL,                -- e.g. "board.accept", "market.sell", "expedition.cancel"
  character_id TEXT NOT NULL REFERENCES character(id),
  response_json TEXT NOT NULL,               -- serialised response returned to client on first settlement
  settled_at   INTEGER NOT NULL,
  expires_at   INTEGER NOT NULL              -- Unix epoch; pruned by cron after TTL
);
CREATE INDEX idx_idempotency_key ON idempotency_key(character_id, settled_at DESC);
CREATE INDEX idx_idempotency_expires ON idempotency_key(expires_at);

TTL: 24 hours. Client may safely retry any mutating request with the same key within 24 hours and receive the original response without double-execution.

Worker middleware pattern:

1. Extract Idempotency-Key header from request
2. Check D1: SELECT response_json FROM idempotency_key WHERE key = ?
3. If found and not expired → return stored response_json immediately, skip business logic
4. Execute business logic
5. In same D1 transaction as the mutation: INSERT INTO idempotency_key ...

Client generates key with crypto.randomUUID() before sending. Key must be fresh per logical action — not reused across different actions.

---

KV Projections (not D1)

High-read data that the settlement tick writes and the client reads — stored in KV, not queried from D1 per request:

Key pattern	Contents	TTL
town_snapshot:{town_id}	Reserve bands, health, active events, service states	5 min
board_snapshot:{location_id}	Active board items rendered for client	5 min
market_snapshot:{town_id}:{category}	Top 20 sell orders + price index per category	2 min
character_session:{character_id}	Character location, coin, active expedition ids	1 min
price_index:{town_id}:{item_id}	Current and 7-day price history array	5 min