discord-manager: Skill for Claude Code

Database Design

Everything here documents the repository as it is on main. If main disagrees with this file, main wins: follow it and flag the drift.

ALWAYS load the "kysely" skill before anything else. These principles govern every table and every migration in app/db/migrations/.

The doctrine: 100% append-only, event-sourced, zero exceptions

The application schema is insert-only. INSERT is the only write the application ever performs. No UPDATE, no DELETE, no TRUNCATE, no ON CONFLICT ... DO UPDATE. This holds for every application table with zero exceptions — including anything that feels "infrastructural". A design that seems to require mutating a row is wrong: model the change as a new event row.

What this buys:

• A complete audit trail with no extra machinery — the schema is the audit trail
• Time travel: any past state is reconstructed by filtering events by createdAt
• No lost-update bugs, no races between readers and writers of the same row
• No sync risk between stored state and the events that produced it

The only table outside the rule is Kysely's own migration bookkeeping table, which mutates itself internally. Never design tables there and never write to it directly.

Two kinds of tables

Every application table is one of two kinds.

Identity tables hold what is immutable by design: id, createdAt, ownership foreign keys that can never change, and the external identifier the row exists to pin. Nothing else. When it is unclear whether an attribute is truly immutable for the row's entire life, it is not — it belongs in an event table.

Event tables hold everything that happens to an entity after (and including) its birth. Each row is a fact that occurred at createdAt and is never modified.

messages                      -- identity: which message exists
  id                  text PK
  channel_id          text not null (FK)
  author_member_id    text not null (FK)
  discord_message_id  text not null unique
  discord_created_at  text not null
  created_at          text not null

message_revisions             -- event: what the message says
  id          text PK
  message_id  text not null (FK)
  content     text not null
  created_at  text not null   -- latest revision wins

message_deletions             -- event: the message was deleted
  id          text PK
  message_id  text not null (FK)
  created_at  text not null