Player Messaging

Architecture

Pure D1 + existing focus-poll. No Durable Objects. No persistent connections. Piggybacks on the notification poll already fired on visibilitychange.

Phase 1 scope: direct messages between characters, inbox, sent folder, soft-delete. No attachments, no threading, no group messages.

---

D1 Schema

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

---

KV Projection

On message send, Worker writes:

inbox_meta:{character_id}  →  { unread_count: N, last_at: epoch }

No TTL — overwritten on each send or read action. Stale risk is negligible: worst case shows unread count one cycle behind.

Focus-poll already hits /api/notifications?since= — Worker reads inbox_meta:{character_id} from KV and includes unread_messages in the response. One extra KV get per poll. No D1 hit per poll.

---

API Routes

Method	Route	Description
POST	/api/messages	Send message
GET	/api/messages/inbox	Inbox list (paginated, 20/page, sorted sent_at DESC)
GET	/api/messages/sent	Sent list (paginated, 20/page)
GET	/api/messages/:id	Full message body; marks read_at if recipient
POST	/api/messages/:id/read	Explicit read mark (idempotent)
DELETE	/api/messages/:id	Soft-delete (sets deleted_by_sender or deleted_by_recipient)

Send Request Body

{
  "recipient_id": "char_abc123",
  "subject": "Trade inquiry",
  "body": "Would you sell 10 Amber Ore?"
}

Validation on send: - recipient_id must exist in D1 and not be soft-blocked - subject max 120 chars - body max 2000 chars - Sender must have character; cannot message self

Inbox Query

SELECT id, sender_id, subject, sent_at, read_at
FROM player_message
WHERE recipient_id = ?
  AND deleted_by_recipient = 0
ORDER BY sent_at DESC
LIMIT 20 OFFSET ?;

Read Mark + KV Update

On GET /api/messages/:id (recipient reading):

1. Set read_at = unixepoch() in D1
2. Decrement unread_count in inbox_meta:{character_id} KV; clamp to 0

Soft Delete

Caller is sender → set deleted_by_sender = 1. Message still visible to recipient.
Caller is recipient → set deleted_by_recipient = 1. Message still visible to sender.
Both deleted → message invisible to both; retained in D1 for 30 days then prunable.

---

Rate Limiting

WAF rule: POST /api/messages → 10 req/IP/min, block 5 min.

Worker-side anti-spam: on send, count messages sender posted in last hour:

SELECT COUNT(*) FROM player_message
WHERE sender_id = ? AND sent_at > unixepoch() - 3600;

Reject with 429 if count ≥ 30. One extra D1 read per send — acceptable.

---

Client Routes

/messages              Inbox (default tab)
/messages/sent         Sent folder
/messages/compose      Compose form
/messages/:id          Read view

Compose Form

Fields: recipient (search by display name → GET /api/characters/search?q=), subject, body textarea with 2000-char counter.

Character search endpoint:

GET /api/characters/search?q={query}   →  [{ id, display_name, rank }]

Returns max 10 results. Reads D1 character table WHERE display_name LIKE ? LIMIT 10. No KV needed — low-frequency call.

Notification Integration

Existing poll response gains one field:

{
  "resolved_expeditions": [...],
  "unread_messages": 3
}

Unread badge renders on Messages nav icon. Clears when player opens /messages.

---

Cost Profile

Operation	Cost
Send message	1 D1 write + 1 KV write + 1 D1 read (spam check)
Focus-poll (tab focus)	1 KV read (already paid)
Open inbox	1 D1 read (20 rows)
Read message	1 D1 read + 1 D1 write + 1 KV write

No DOs. No persistent connections. Fits D1 free tier for Phase 1 player counts.

---

Phase 2 Extensions (deferred)

• Block list (recipient blocks sender; block stored in character_block table)
• Report / moderation flag on message
• Guild broadcast messages (write one row per member in player_message; or separate guild_broadcast table)
• Message retention policy (prune rows older than 90 days via scheduled cron)