Writing a Custom Adapter

Only needed on platforms where the official adapters don't fit - Convex, Firebase, DynamoDB, proprietary AI-builder clouds, or another store without a first-party adapter.

Decide if you even need one

• Demos / tests / a single long-lived process: @chatpack/adapter-memory.
• Any Postgres you can reach with a connection string (Neon, RDS, Railway, Fly, Replit, Vercel Postgres): @chatpack/adapter-drizzle. Do not write a custom adapter for these.
• Prisma ORM 7 with PostgreSQL: use the first-party @chatpack/adapter-prisma adapter. Copy its models and migration, generate the client in your application, and pass that server-side client to prismaAdapter(client).
• Supabase/PostgREST: use the first-party @chatpack/adapter-supabase adapter. It is server-only, accepts an already-created privileged Supabase client, and ships with its own RPC-backed migration.
• MySQL 8 with a server-side transaction-capable mysql2 connection or pool: use the first-party @chatpack/adapter-mysql adapter and its MySQL migration. Its verified target is MySQL 8 plus Drizzle's mysql2 driver. It does not claim MariaDB, PlanetScale, Aurora, HTTP/serverless drivers, or edge-runtime support.
• A non-SQL store (Convex, Firestore, DynamoDB): write a custom adapter - continue below.

Supabase setup must stay on the server. Use a service-role or server-secret key only in trusted server code, disable session persistence, and never expose the key or bundle the adapter into browser code. Apply packages/adapter-supabase/supabase/migrations/0001_chatpack.sql; it enables RLS on every Chatpack table and creates no public policies. Chatpack core still owns application permission checks.

The contract

Implement the StorageAdapter interface exported from @chatpack/core and pass it as chatpack({ storage: yourAdapter() }). Twenty-one required async methods:

Exact TypeScript signatures and per-method TSDoc ship in the package's .d.ts (@chatpack/core, storage.ts module).

The five group methods are required, not optional (unlike searchMessages): a membership row either exists or it doesn't, so the semantics are identical on every backend and there's nothing for a platform to be unable to express. An adapter written before groups won't typecheck until it implements them - that's deliberate, because silently missing membership writes look like data loss.

The two mention methods are required for the same reason, and an adapter written before them won't typecheck either - silently discarding a mention looks exactly like a notification that never fired. Forwarding adds no methods at all: it is addMessage plus three more nullable columns on the message.

Two things to get right, both covered by invariants 19 and 20: setMessageMentions is a replace, not an add (insert the given ids, delete every other mention row on that message, in one transaction - and an empty array means delete them all), and listMentionsByMessageIds sorts by createdAt with userId as a tiebreak, because a set written in one call shares a timestamp and would otherwise come back in whatever order the planner chose.

Optional capabilities

Four features sit outside the required contract, so an adapter can skip them and still be complete. Each reports 501 rather than degrading silently.

searchMessages is an optional storage capability. Memory and Drizzle provide it. Existing custom adapters may omit it; chat.api.searchMessages and GET /search/messages then return SEARCH_UNSUPPORTED with HTTP status 501. Adapters that provide search must return non-tombstone participant-scoped results. Use tokenizeSearch, getSearchTerms, countSearchTokens, and scoreSearchTerms from @chatpack/core for the canonical contract: Unicode NFKC normalization, case-insensitive matching, punctuation as a separator, all unique query terms required, no stemming or prefix matching, and occurrence-sum relevance. Cursors remain adapter-defined and opaque. Core applies canRead after participant-scoped results; non-participant dynamic access is not supported by this initial design.

Invites and join requests are the second optional capability, and unlike search they are a whole namespace rather than a method: StorageAdapter gains one optional invites property holding nine methods.

invites?: {
  createInvite, getInvite, listInvites, deleteInvite, consumeInvite,
  createJoinRequest, getJoinRequest, listJoinRequests, resolveJoinRequest,
}

It is all-or-nothing on purpose. Nine independently optional methods would have 2⁹ states, nearly all broken - an adapter implementing six of them would typecheck and then fail at runtime on the seventh. Core checks for the namespace, once, and all eight invite routes return 501 INVITES_UNSUPPORTED when it is absent. So implement every method or omit the whole property; don't ship a partial one.

Why optional at all, when the five group methods above are required: a token row is just as backend-neutral as a participant row, so it passes the semantics test groups passed. It fails a different one - the required contract went fourteen → nineteen → twenty-one methods over a few releases, and taking it to thirty for a feature a 1:1-only app will never call turns "write an adapter" from an afternoon into a project. Groups reuse the participants table every DM already writes; invites need two genuinely new tables.

Public channels are the third capability, and they split across both sides of the line - which is the part worth understanding before you implement them.

The visibility and joinPolicy fields on Conversation are required: every adapter stores and returns them, and core always passes both, fully resolved, to createGroupConversation and updateConversation. Two columns with defaults are not an afternoon's work, and making them optional would mean core could never trust that a conversation it just wrote reads back the way it wrote it.

The directory is the optional part - a namespace with exactly one method:

channels?: {
  listPublicConversations,  // { limit, cursor } -> { conversations, nextCursor }
}

Return every type: "group" conversation with visibility: "public", in the same order and with the same cursor contract as listConversations. Filter on both fields: a hand-edited row must not be able to put a DM in a public directory. Core narrows each row to a thin ChannelPreview itself, so return full conversations.

Omitting the namespace does more than disable GET /channels: core also refuses to set a non-default visibility or joinPolicy (501 CHANNELS_UNSUPPORTED). That gate is the point. Without it an adapter that stored the columns but had no directory would happily accept visibility: "public", return a conversation that claims to be a channel, and leave the developer debugging an empty directory. Explicitly sending the defaults still works, so existing clients are unaffected.

One dependency to know: an "approval" channel files its joiners into the ADR 0019 queue, so it needs the invites namespace too. "open" channels need only channels.

Moderation is the fourth optional capability. Add StorageAdapter.moderation when the adapter supports durable blocks, conversation mutes, reports, and bans. Block and mute writes must be idempotent. Report evidence is captured by core and must remain immutable. Expired and revoked bans stay in storage for audit history. Adapters without this namespace remain valid and return MODERATION_UNSUPPORTED when moderation actions are requested.

createBan has the same concurrency rule as consumeInvite: decide in one statement whether the user already has an active ban and return that ban instead of inserting a second. In Postgres that is an INSERT ... SELECT ... WHERE NOT EXISTS (an active ban for this user). A read-then-insert lets two moderators acting at the same moment leave two active rows - and revoking the one they can see leaves the other still enforcing, which looks like a ban that can't be lifted. "Active" means revokedAt IS NULL and (expiresAt IS NULL or expiresAt > now). revokeBan should keep the original revoker when it is called twice.

The entities you store are described in Conversations & Messages. Users are opaque string ids - do not add foreign keys into your users table and do not validate user existence in the adapter.

You store replyToMessageId verbatim (core has already validated that it names a message in the same conversation) and you never see the API-only decorations replyTo / reactions - core builds those per request from getMessagesByIds and listReactionsByMessageIds, one batched call per page.

The invariants

These are database-agnostic - satisfy every one of them however your platform does atomicity. Everything else is implementation detail.

1. One conversation per pairKey, even under concurrency - and that uniqueness must NOT apply to groups. Core computes pairKey (sorted user ids joined with ":") and hands it to you. Two concurrent getOrCreateDirectConversation calls with the same pairKey must converge on a single conversation (created: true for at most one caller). A JS-side "select, then insert if missing" is NOT enough - enforce uniqueness in the database (unique index + insert-on-conflict, transaction, or your platform's equivalent). Groups store pairKey: null and are never find-or-create, so the constraint has to exclude nulls: in Postgres that means a partial unique index (WHERE pair_key IS NOT NULL). Watch the corollary - ON CONFLICT only matches a partial index when the insert repeats the predicate, so the DM insert needs ON CONFLICT (pair_key) WHERE pair_key IS NOT NULL DO NOTHING.
2. seq is strictly increasing per conversation and assigned atomically with the message insert. Never reused, never decreasing; gaps are allowed. Concurrent sends to the same conversation must serialize. Do NOT compute MAX(seq) + 1 in application code - that races.
3. Adapters generate ids. getOrCreateDirectConversation and addMessage create the id themselves (any unique string; the official adapters use prefixed random ids like msg_<uuid>). Ids must be unique and stable.
4. Adapters never enforce permissions. Core validates participants and permission hooks before calling you. Return whatever is asked for. Corollary for hosted platforms: the adapter must run server-side with full DB privileges (e.g. the Supabase service-role key), and the Chatpack tables must NOT be readable or writable by browser/anon clients - otherwise users can read each other's messages around Chatpack entirely.
5. Cursors are opaque strings that the adapter defines. Core (and the HTTP layer) round-trip your nextCursor back into input.cursor verbatim, without inspecting it. Pick any encoding that survives a URL query parameter. Return nextCursor: null when there are no more results. Reference encodings: listMessages - the seq of the last message on the page; listConversations - "<lastActivityMs>:<conversationId>" (keyset) or simply the last conversation id.
6. Ordering. listMessages: newest-first (descending seq). listMessagesAfterSeq: oldest-first (ascending seq), only seq > afterSeq, capped at limit. listConversations: most-recently-active first (latest message activity, falling back to conversation creation time), with a stable tiebreak so pagination never skips or repeats. participants must come back in a stable order across reads - any order you like (the official adapters use join order, tie-broken by userId), but the same one every time. SQL stores give no row order without an explicit ORDER BY, and clients diff participant lists positionally: an unordered read makes an N-member group look like it changed membership on every poll.
7. Return real Date instances, never ISO strings. The interface types say Date and core does not coerce. Database drivers and HTTP clients (PostgREST, Convex, Firestore) often hand back strings or numbers - convert at the adapter boundary, both directions. JSON serialization is the HTTP handler's job, never yours.
8. Soft delete is an update, not a removal. Core calls updateMessage({ body: "", deletedAt }); the row must remain (clients render a tombstone) and must keep its seq. Never hard-delete. updateMessage patches only the fields that are defined on the input and throws on unknown ids; updateLastRead overwrites lastReadMessageId unconditionally (core has already validated the message).
9. metadata is an arbitrary JSON object. Store and return it losslessly (default {}).
10. countUnread is exact and batched. For each requested conversation, count messages with seq strictly greater than the seq of the viewer's lastReadMessageId (null read-state = 0, i.e. everything) AND senderId !== userId (a viewer's own messages are never unread). Soft-deleted messages keep their seq and DO count; all roles count. Return { [conversationId]: count }; ids the viewer doesn't participate in may be omitted or 0 (core treats missing as 0). One call covers a whole page of conversations - do it in one query, not one per id. Note lastReadMessageId is a message id, not a seq: resolve it at query time (SQL: LEFT JOIN the message row + COALESCE(read.seq, 0)).
11. Optional search is participant-scoped. If you implement searchMessages, filter by the supplied userId, exclude tombstones, rank deterministically, and return an opaque cursor. Use the core search helpers to preserve the canonical case-insensitive token and relevance semantics. Core applies canRead after retrieving participant-scoped candidates; non-participant dynamic access is not supported by this initial design.
12. Reaction writes are idempotent and return the full set. addReaction with the same (messageId, userId, emoji) triple twice must leave exactly one reaction - not two, not an error - so enforce uniqueness in the database (unique index + insert-on-conflict-do-nothing), not in JS. removeReaction for something that was never there is a silent no-op. Both return every reaction on the message afterwards, so core can publish a complete snapshot without a second round trip - returning only the changed row would blank out the other participant's reactions in every client. Neither may touch the conversation's lastSeq or activity timestamp: a reaction is not a message, so it must not reorder the conversation list or move the Last-Event-ID baseline. Only addMessage bumps those.
13. Batched lookups are batched and tolerant. getMessagesByIds and listReactionsByMessageIds each take a whole page's ids and do one query, not one per id. Unknown ids are simply absent from the result - never null entries, never a throw - and an empty input array returns [] without touching the database. Sort listReactionsByMessageIds ascending by createdAt so core's aggregated userIds come out earliest-first.
14. Group creation is atomic and never find-or-create. createGroupConversation always inserts a new conversation (generate the id, pairKey: null, type: "group") plus one participant row per member, in a single transaction: a half-created group with no participants is unrecoverable, because nobody can read it to fix it. Core hands you a de-duplicated userIds that excludes the creator; write the creator as role: "admin" and everyone else as "member", and don't attempt uniqueness on membership.
15. Membership writes are idempotent, and never clobber a role. addParticipants inserts only ids that are absent (unique index on (conversationId, userId) + on-conflict-do-nothing, not do-update): a replayed request must not reset an admin back to member. removeParticipant for a non-participant is a silent no-op, and it never touches their messages - history outlives membership. setParticipantRole throws when the target is not a participant (core already checked, so that's a corruption signal, not a user error). None of these may bump lastSeq or the activity timestamp - a membership change is not a message, exactly like a reaction (invariant 12).
16. Adapters never enforce group policy. Core owns the participant cap, the name length, and the last-admin rule, and validates before calling you. Store what you're given. An adapter that re-checks will drift from core and start rejecting valid writes after a core upgrade.
17. If you implement invites, consumption is atomic and lookups are honest. consumeInvite must check usability and increment uses in one statement - a SELECT, a JS comparison, then an UPDATE is the same race as a non-atomic seq, except here it hands a one-use link to two strangers. Zero rows affected means "not usable any more"; return null. Meanwhile getInvite must return expired and exhausted invites too rather than filtering them out, because that is how core tells 410 INVITE_EXPIRED ("ask for a new link") from 404 INVITE_NOT_FOUND ("no such link"). Two more: createJoinRequest upserts on (conversationId, userId) and must reset status to pending while clearing resolvedAt/resolvedBy, so a previously denied user can ask again - the opposite of addParticipants, where a do-update would demote an admin. And store the code core hands you verbatim: don't generate, hash, trim, or case-fold it. Core mints it from crypto.getRandomValues because unguessability is a security property, not a storage detail - if adapters minted tokens one could reach for Math.random() and no test would fail.
18. visibility and joinPolicy round-trip, and you coerce on read. Both are required fields on every conversation, always supplied by core on create and update, and must read back exactly as written - so write all three fields in updateConversation, not just the one that looks like it changed. On the way out, narrow anything outside the two unions to "private" / "approval": a text column holds whatever a hand-run migration put there, and failing closed keeps a private group private. If you implement the optional channels namespace, listPublicConversations must filter on type: "group" and visibility: "public", in listConversations order.

Reference schema (Postgres)

If your store speaks SQL, use the official schema as-is; if not, translate the shapes and satisfy the invariants instead. Also available programmatically: import { migrationSql, chatpackSchema } from "@chatpack/adapter-drizzle" (migrationSql is plain idempotent DDL - no Drizzle required to execute it). For an existing database, run backfillMessageSearchTokens once after applying the migration. New and edited messages are maintained automatically by the official Drizzle adapter.

CREATE TABLE IF NOT EXISTS "chatpack_conversations" (
  "id" text PRIMARY KEY,
  "type" text NOT NULL DEFAULT 'direct',
  "pair_key" text,                          -- NULL for groups
  "name" text,                              -- groups only
  "visibility" text NOT NULL DEFAULT 'private',
  "join_policy" text NOT NULL DEFAULT 'approval',
  "created_at" timestamptz NOT NULL,
  "metadata" jsonb NOT NULL DEFAULT '{}',
  "last_seq" integer NOT NULL DEFAULT 0,
  "last_activity_at" timestamptz NOT NULL
);
-- Upgrade path (the CREATE TABLE above no-ops on an existing table):
ALTER TABLE "chatpack_conversations"
  ADD COLUMN IF NOT EXISTS "type" text NOT NULL DEFAULT 'direct';
ALTER TABLE "chatpack_conversations"
  ADD COLUMN IF NOT EXISTS "name" text;
ALTER TABLE "chatpack_conversations"
  ALTER COLUMN "pair_key" DROP NOT NULL;
-- Channels. The defaults are what make this a safe upgrade: every row written
-- before the migration reads back private and approval-gated.
ALTER TABLE "chatpack_conversations"
  ADD COLUMN IF NOT EXISTS "visibility" text NOT NULL DEFAULT 'private';
ALTER TABLE "chatpack_conversations"
  ADD COLUMN IF NOT EXISTS "join_policy" text NOT NULL DEFAULT 'approval';
-- The pair-key index becomes PARTIAL so unlimited null-keyed groups coexist
-- with one-per-pair DMs. A partial index can't replace a total one under the
-- same name (CREATE ... IF NOT EXISTS would silently keep the old one), hence
-- the drop-and-rename.
DROP INDEX IF EXISTS "chatpack_conversations_pair_key_idx";
CREATE UNIQUE INDEX IF NOT EXISTS "chatpack_conversations_pair_key_unique_idx"
  ON "chatpack_conversations" ("pair_key") WHERE "pair_key" IS NOT NULL;
CREATE INDEX IF NOT EXISTS "chatpack_conversations_activity_idx"
  ON "chatpack_conversations" ("last_activity_at", "id");
-- The channel directory sorts by the same keyset as listConversations, so it
-- takes the same index shape - but PARTIAL, so browsing never pays to sort
-- every DM in the table.
CREATE INDEX IF NOT EXISTS "chatpack_conversations_public_idx"
  ON "chatpack_conversations" ("last_activity_at", "id") WHERE "visibility" = 'public';

CREATE TABLE IF NOT EXISTS "chatpack_conversation_participants" (
  "conversation_id" text NOT NULL REFERENCES "chatpack_conversations"("id") ON DELETE CASCADE,
  "user_id" text NOT NULL,
  "role" text NOT NULL DEFAULT 'member',
  "joined_at" timestamptz NOT NULL,
  "last_read_message_id" text
);
ALTER TABLE "chatpack_conversation_participants"
  ADD COLUMN IF NOT EXISTS "role" text NOT NULL DEFAULT 'member';
-- Both participants of a DM are admins; the column default is 'member', so
-- pre-existing rows need a backfill. Idempotent: a second run matches nothing.
UPDATE "chatpack_conversation_participants" AS p
  SET "role" = 'admin'
  WHERE "role" = 'member'
    AND EXISTS (
      SELECT 1 FROM "chatpack_conversations" AS c
      WHERE c."id" = p."conversation_id" AND c."type" = 'direct'
    );
CREATE UNIQUE INDEX IF NOT EXISTS "chatpack_participants_conv_user_idx"
  ON "chatpack_conversation_participants" ("conversation_id", "user_id");
CREATE INDEX IF NOT EXISTS "chatpack_participants_user_idx"
  ON "chatpack_conversation_participants" ("user_id");

CREATE TABLE IF NOT EXISTS "chatpack_messages" (
  "id" text PRIMARY KEY,
  "conversation_id" text NOT NULL REFERENCES "chatpack_conversations"("id") ON DELETE CASCADE,
  "sender_id" text NOT NULL,
  "body" text NOT NULL,
  "role" text NOT NULL DEFAULT 'user',
  "seq" bigint NOT NULL,
  "created_at" timestamptz NOT NULL,
  "edited_at" timestamptz,
  "deleted_at" timestamptz,
  "reply_to_message_id" text,
  "metadata" jsonb NOT NULL DEFAULT '{}'
);
-- Upgrade path: the CREATE TABLE above is a no-op on an existing table, so
-- the reply column needs its own idempotent statement.
ALTER TABLE "chatpack_messages"
  ADD COLUMN IF NOT EXISTS "reply_to_message_id" text;
CREATE UNIQUE INDEX IF NOT EXISTS "chatpack_messages_conv_seq_idx"
  ON "chatpack_messages" ("conversation_id", "seq");

CREATE TABLE IF NOT EXISTS "chatpack_message_search_tokens" (
  "message_id" text NOT NULL REFERENCES "chatpack_messages"("id") ON DELETE CASCADE,
  "token" text NOT NULL,
  "occurrences" integer NOT NULL,
  PRIMARY KEY ("message_id", "token")
);
CREATE INDEX IF NOT EXISTS "chatpack_message_search_tokens_token_idx"
  ON "chatpack_message_search_tokens" ("token", "message_id");

CREATE TABLE IF NOT EXISTS "chatpack_message_reactions" (
  "message_id" text NOT NULL REFERENCES "chatpack_messages"("id") ON DELETE CASCADE,
  "user_id" text NOT NULL,
  "emoji" text NOT NULL,
  "created_at" timestamptz NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS "chatpack_reactions_msg_user_emoji_idx"
  ON "chatpack_message_reactions" ("message_id", "user_id", "emoji");
CREATE INDEX IF NOT EXISTS "chatpack_reactions_message_idx"
  ON "chatpack_message_reactions" ("message_id", "created_at");

-- The two invite tables, only needed if you implement the optional `invites`
-- namespace. Pure additions: no column changes, no index swaps, so this part of
-- the migration is safe to run before deploying new code.
CREATE TABLE IF NOT EXISTS "chatpack_conversation_invites" (
  "code" text PRIMARY KEY,                  -- the secret IS the identity
  "conversation_id" text NOT NULL REFERENCES "chatpack_conversations"("id") ON DELETE CASCADE,
  "created_by" text NOT NULL,
  "created_at" timestamptz NOT NULL,
  "expires_at" timestamptz,                 -- NULL never expires
  "max_uses" integer,                       -- NULL is unlimited
  "uses" integer NOT NULL DEFAULT 0,
  "requires_approval" boolean NOT NULL DEFAULT false,
  "metadata" jsonb NOT NULL DEFAULT '{}'
);
CREATE INDEX IF NOT EXISTS "chatpack_invites_conversation_idx"
  ON "chatpack_conversation_invites" ("conversation_id", "created_at");

CREATE TABLE IF NOT EXISTS "chatpack_join_requests" (
  "id" text PRIMARY KEY,
  "conversation_id" text NOT NULL REFERENCES "chatpack_conversations"("id") ON DELETE CASCADE,
  "user_id" text NOT NULL,
  "status" text NOT NULL DEFAULT 'pending', -- pending | approved | denied
  "message" text,
  "invite_code" text,                       -- which link produced it, if any
  "created_at" timestamptz NOT NULL,
  "resolved_at" timestamptz,
  "resolved_by" text,
  "metadata" jsonb NOT NULL DEFAULT '{}'
);
-- One row per user per group: this index is also the ON CONFLICT arbiter that
-- makes a re-ask replace the old decision instead of accumulating rows.
CREATE UNIQUE INDEX IF NOT EXISTS "chatpack_join_requests_conv_user_idx"
  ON "chatpack_join_requests" ("conversation_id", "user_id");
CREATE INDEX IF NOT EXISTS "chatpack_join_requests_status_idx"
  ON "chatpack_join_requests" ("conversation_id", "status", "created_at");

requires_approval and status are worth getting right: a text column where the boolean belongs reads back truthy for 'false' and silently inverts the feature.

The two statements with real concurrency requirements (invariant 17):

-- consumeInvite: check and increment together. Zero rows -> return null.
UPDATE chatpack_conversation_invites
   SET uses = uses + 1
 WHERE code = $1
   AND (max_uses IS NULL OR uses < max_uses)
   AND (expires_at IS NULL OR expires_at > now())
RETURNING *;

-- createJoinRequest: DO UPDATE here, unlike addParticipants. A denied user
-- asking again must get a fresh pending row, not their old rejection.
INSERT INTO chatpack_join_requests (id, conversation_id, user_id, status, message,
                                    invite_code, created_at, metadata)
VALUES ($1, $2, $3, 'pending', $4, $5, now(), $6)
ON CONFLICT (conversation_id, user_id) DO UPDATE
   SET status = 'pending', message = EXCLUDED.message,
       invite_code = EXCLUDED.invite_code, created_at = EXCLUDED.created_at,
       resolved_at = NULL, resolved_by = NULL
RETURNING *;

reply_to_message_id deliberately has no foreign key: a reply must outlive its parent, and messages are only ever soft-deleted anyway.

Existing Postgres deployments must re-run the migration before upgrading - every statement is idempotent, so re-running the whole script is safe and preserves data and seq counters.

How the reference implementation satisfies the hard invariants:

• Atomic seq - one statement, row-locked by Postgres:

  UPDATE chatpack_conversations
  SET last_seq = last_seq + 1, last_activity_at = $now
  WHERE id = $conversationId
  RETURNING last_seq;

  then insert the message with the returned value. The unique index on (conversation_id, seq) backstops the invariant. On Supabase/PostgREST you cannot express this UPDATE through the client - wrap it in a SQL function and call it via RPC.

• Idempotent pair creation - INSERT ... ON CONFLICT (pair_key) WHERE pair_key IS NOT NULL DO NOTHING, then re-select by pair_key; zero inserted rows means a concurrent call won and both converge on the same row. The WHERE clause is load-bearing, not decorative: the index is partial, and Postgres only matches an ON CONFLICT target to a partial index when the predicate is repeated. Omit it and every DM insert fails with "there is no unique or exclusion constraint matching the ON CONFLICT specification".

• Group creation takes the opposite path: a plain INSERT with no conflict target (a group is always new) for the conversation plus the participant rows, both inside one db.transaction.

• Idempotent member adds - the same shape as reactions, keyed on the membership pair:

  INSERT INTO chatpack_conversation_participants
    (conversation_id, user_id, role, joined_at)
  VALUES ($conversationId, $userId, 'member', $now)
  ON CONFLICT (conversation_id, user_id) DO NOTHING;

  DO NOTHING, never DO UPDATE: a replayed add must not reset an existing admin to member (invariant 15). And as with reactions, note what's absent - no UPDATE chatpack_conversations.

• Batched unread counts - one GROUP BY query per page; a LEFT JOIN resolves last_read_message_id to its seq (invariant 10). The unique index on (conversation_id, seq) makes each count an index range scan:

  SELECT m.conversation_id, count(*)
  FROM chatpack_messages m
  JOIN chatpack_conversation_participants p
    ON p.conversation_id = m.conversation_id AND p.user_id = $userId
  LEFT JOIN chatpack_messages r ON r.id = p.last_read_message_id
  WHERE m.conversation_id = ANY($conversationIds)
    AND m.sender_id <> $userId
    AND m.seq > COALESCE(r.seq, 0)
  GROUP BY m.conversation_id;

  Conversations absent from the result have zero unread.

• Idempotent reacting - the same shape as pair creation, one statement, then re-select the message's whole set:

  INSERT INTO chatpack_message_reactions (message_id, user_id, emoji, created_at)
  VALUES ($messageId, $userId, $emoji, $now)
  ON CONFLICT (message_id, user_id, emoji) DO NOTHING;

  Note what's absent: no UPDATE chatpack_conversations. Reacting must not advance last_seq or last_activity_at (invariant 12).

Non-SQL platforms (Convex, Firestore, ...)

Ignore the SQL; keep the five-collection shape and satisfy the invariants with your platform's transaction primitive. Example - on Convex, each adapter method becomes a Convex query/mutation (DB access only exists inside Convex functions) and the StorageAdapter object calls them through the Convex client; addMessage is a single mutation (read counter → increment → insert), which is automatically a serializable transaction, satisfying invariant 2 with no extra machinery. Enforce invariant 1 with an index/lookup on pairKey inside one mutation (skipping the check when pairKey is null, since groups are never deduplicated), invariant 12 with a lookup on the (messageId, userId, emoji) triple inside the reaction mutation, and invariant 14 by writing the group and its participant rows in a single mutation.

Skeleton

import type { StorageAdapter } from "@chatpack/core";

export function myAdapter(client: MyDbClient): StorageAdapter {
  return {
    async getOrCreateDirectConversation({ pairKey, userIds, metadata }) {
      // 1. Attempt atomic create (unique on pairKey; generate the id here).
      // 2. On conflict, fetch the existing conversation by pairKey.
      // return { conversation, created };
    },
    async createGroupConversation({ creatorId, userIds, name, metadata, visibility, joinPolicy }) {
      // ALWAYS a new conversation: generate the id, type "group",
      // pairKey null. One transaction: the conversation row + the creator as
      // "admin" + each of userIds as "member" (already de-duplicated and
      // creator-free). Never find-or-create.
      // visibility/joinPolicy arrive already defaulted ("private"/"approval") -
      // store them as given; they are required columns, not part of a namespace.
      // return conversation;
    },
    async getConversation(conversationId) {
      // Fetch conversation + ALL participant rows, or return null.
      // Coerce visibility/joinPolicy on the way out: anything outside the two
      // unions (a legacy NULL, a hand-edited row) reads back as "private" /
      // "approval" (invariant 18).
    },
    async listConversations({ userId, limit, cursor }) {
      // Conversations where userId is a participant, most-recently-active
      // first; keyset-paginate; you define the cursor encoding.
      // return { conversations, nextCursor }; // nextCursor: string | null
    },
    async updateConversation({ conversationId, name, visibility, joinPolicy }) {
      // Core sends all three fields, already merged with the current row - so
      // write all three, don't try to guess which one "really" changed.
      // name is a string or null; throw if the id is unknown.
      // return updatedConversation; // with participants
    },
    async addParticipants({ conversationId, userIds }) {
      // Insert-on-conflict-DO-NOTHING per id as role "member" - a replayed
      // add must not demote an existing admin (invariant 15).
      // return updatedConversation;
    },
    async removeParticipant({ conversationId, userId }) {
      // Delete that one membership row; absent = silent no-op. Leave their
      // messages alone.
      // return updatedConversation;
    },
    async setParticipantRole({ conversationId, userId, role }) {
      // Set that participant's role; throw if they are not a participant.
      // return updatedConversation;
    },
    async addMessage({ conversationId, senderId, body, role, replyToMessageId, metadata }) {
      // Atomically bump the conversation's seq counter and insert the
      // message with that seq (single transaction / RPC / mutation).
      // Store replyToMessageId verbatim - core already validated it.
      // return message; // with adapter-generated id and real Date fields
    },
    async getMessage(messageId) {
      // Fetch by id, or return null.
    },
    async getMessagesByIds(messageIds) {
      // One batched fetch. Unknown ids are simply absent (no nulls, no throw);
      // [] in -> [] out without hitting the database. Order is irrelevant.
      // return messages;
    },
    async listMessages({ conversationId, limit, cursor }) {
      // Descending seq; cursor = e.g. String(lastMessage.seq).
      // return { messages, nextCursor };
    },
    async listMessagesAfterSeq({ conversationId, afterSeq, limit }) {
      // Ascending seq, seq > afterSeq, up to limit. Powers gap-fill.
      // return messages;
    },
    async updateMessage({ messageId, body, editedAt, deletedAt }) {
      // Patch ONLY the defined fields; throw if messageId is unknown.
      // return updatedMessage;
    },
    async updateLastRead({ conversationId, userId, messageId }) {
      // Set that participant's lastReadMessageId.
    },
    async countUnread({ userId, conversationIds }) {
      // For each conversation: count messages with seq > the seq of this
      // user's lastReadMessageId (null -> 0) AND senderId !== userId.
      // Tombstones count. One batched query, not one per id (invariant 10).
      // return { [conversationId]: count };
    },
    async searchMessages({ userId, query, limit, cursor }) {
      // Use getSearchTerms/countSearchTokens/scoreSearchTerms from @chatpack/core.
      // Search only conversations where userId participates. Exclude tombstones,
      // rank by score, createdAt, then id, and return an opaque cursor.
      // return { messages, nextCursor };
    },
    async addReaction({ messageId, userId, emoji }) {
      // Insert-on-conflict-do-nothing on the (messageId, userId, emoji)
      // triple, then return ALL reactions on the message (invariant 12).
      // Do NOT touch the conversation's seq counter or activity timestamp.
      // return reactions;
    },
    async removeReaction({ messageId, userId, emoji }) {
      // Delete that one triple (absent = no-op), return the remaining set.
      // return reactions;
    },
    async listReactionsByMessageIds(messageIds) {
      // One batched query for a whole page, ascending createdAt so core's
      // aggregated userIds come out earliest-first. [] in -> [] out.
      // return reactions;
    },
    // Optional capability. Omit the WHOLE namespace or implement all nine -
    // core checks for `invites`, not for individual methods.
    invites: {
      async createInvite(input) {
        // Store input.code VERBATIM as the primary key - core generated it.
        // uses starts at 0. return invite;
      },
      async getInvite(code) {
        // Return expired/exhausted invites too, or null. Core needs both to
        // tell 404 from 410 (invariant 17).
      },
      async listInvites(conversationId) {
        // That group's invites, newest-first, spent ones included.
      },
      async deleteInvite({ conversationId, code }) {
        // Scoped by conversationId AND idempotent: zero rows is success.
      },
      async consumeInvite(code) {
        // ONE statement: check usability and increment uses together.
        // Zero rows affected -> return null. return invite;
      },
      async createJoinRequest(input) {
        // UPSERT on (conversationId, userId): status "pending", and clear
        // resolvedAt/resolvedBy so a re-ask doesn't look decided.
      },
      async getJoinRequest({ conversationId, userId }) {
        // That pair's request whatever its status, or null.
      },
      async listJoinRequests({ conversationId, status, limit }) {
        // Newest-first, filtered by status when given.
      },
      async resolveJoinRequest(input) {
        // Write the decision; core already checked it exists and is pending,
        // and adds the participant itself. Throw if the row is gone.
      },
    },
    // Optional capability, and independent of `invites` - either can be present
    // without the other. Omitting it also makes core refuse to SET
    // visibility/joinPolicy to anything but the defaults (501).
    channels: {
      async listPublicConversations({ limit, cursor }) {
        // Filter on type "group" AND visibility "public" - no userId, the
        // directory is the same for everyone. Same order and cursor encoding as
        // listConversations. Return FULL conversation rows; core narrows them
        // to previews itself.
        // return { conversations, nextCursor };
      },
    },
  };
}

Pitfalls

Each of these has bitten a real integration:

• Checking pair existence in JS before inserting (races; use DB uniqueness).
• MAX(seq) + 1 computed in application code (races; use an atomic counter).
• Returning ISO strings where the contract says Date.
• Hard-deleting messages, or dropping seq/row on delete (must tombstone).
• listMessages returned oldest-first (must be newest-first), or listMessagesAfterSeq returned newest-first (must be oldest-first).
• Stubbing nextCursor as always-null - pagination silently breaks once history exceeds one page.
• Exposing chatpack_* tables to browser/anon database clients (bypasses Chatpack's permission layer; adapter must be server-only with privileged credentials).
• Adding a foreign key to the app's users table (Chatpack never owns users).
• Enforcing "is sender a participant?" in the adapter (core already did).
• Counting the viewer's own messages as unread in countUnread (they never are), or issuing one count query per conversation (N+1; batch the page).
• Checking "did this user already react?" in JS before inserting (races; use a unique index + on-conflict-do-nothing), or throwing on a duplicate react or a missing un-react instead of treating both as no-ops.
• Returning only the changed reaction from addReaction / removeReaction instead of the message's whole set - core publishes that set as a snapshot, so a delta silently blanks other users' reactions in every client.
• Bumping the conversation's activity timestamp or lastSeq on a reaction (reorders the conversation list and poisons Last-Event-ID gap-fill).
• Adding a foreign key from reply_to_message_id to messages (a reply must outlive its parent), or validating the parent in the adapter (core did).
• Returning null entries or throwing for unknown ids in getMessagesByIds / listReactionsByMessageIds (unknown ids are simply absent), or hitting the database at all when the input array is empty.
• Leaving the pair-key unique index total after adding groups, so the second null-keyed group collides - or making it partial but forgetting to repeat the predicate in ON CONFLICT, which breaks every DM with "no unique or exclusion constraint matching the ON CONFLICT specification" (invariant 1).
• Making createGroupConversation find-or-create, or inserting the conversation and its participants in two separate round trips (a crash between them leaves a group nobody can read, let alone repair).
• ON CONFLICT ... DO UPDATE in addParticipants, which demotes an admin every time a client retries the request.
• Throwing on a duplicate add or an absent removal instead of treating both as no-ops - or hard-deleting a departed member's messages (history outlives membership).
• Re-checking core's group policy (participant cap, name length, last-admin) in the adapter, or defaulting a DM's participants to member (both DM participants are admin).
• Bumping the conversation's activity timestamp or lastSeq on a membership change (same trap as reactions: it reorders the list and poisons gap-fill).
• Returning participants in whatever order the database felt like - with no ORDER BY, an N-member group looks like it changed membership on every poll (invariant 6).
• SELECT an invite, check uses < maxUses in JS, then UPDATE - the same race as a non-atomic seq, except here it hands a one-use link to two strangers.
• Filtering expired invites out of getInvite, which turns every 410 ("that link expired, ask for a new one") into a 404 ("no such link").
• Implementing six of the nine invite methods: core checks for the namespace, not the methods, so a partial one typechecks and then crashes instead of reporting a clean 501.
• Ignoring conversationId in deleteInvite, letting one group's admin revoke another group's links.
• ON CONFLICT ... DO NOTHING in createJoinRequest - the opposite of addParticipants. A user denied last week must be able to ask again, so the upsert has to reset status and clear the resolution.
• Generating the invite code in the adapter, or hashing/normalizing the one core passed in - the stored code must be byte-identical to the one in the URL.
• Treating visibility / joinPolicy as part of the channels namespace and skipping the columns. They ride the required contract; the namespace only covers the directory query. Drop the columns and every channel reads back private, which is the exact failure the 501 exists to prevent.
• Writing only the field that looks changed in updateConversation. Core sends all three already resolved against the current row, so a visibility-only request arrives carrying the existing name - a COALESCE-style "skip the ones I think are unchanged" write is both unnecessary and a way to lose a name.
• listPublicConversations filtering on visibility but not type, so a hand-edited DM row surfaces in the directory with both participants' ids reachable to whoever joins.
• Stripping fields down to a preview shape inside the adapter - core computes participantCount, alreadyParticipant, and requestPending from the full rows, and gets alreadyParticipant wrong if the participants are missing.
• A total index on visibility where a partial one (WHERE visibility = 'public') is what the directory query needs - it indexes every private conversation in the database to serve a query that never reads them.
• Trusting the stored string on read. A legacy row (added the column with no default) or a hand-edited one must narrow to "private" / "approval", not flow into core as an out-of-union value that then ships to clients.

Verify your adapter

Run these checks before calling it done. All of them go through the normal Chatpack API (chat.api.* or the HTTP routes) - if any fails, the bug is in the adapter.

1. Round trip. Create a conversation (A→B), send a message, restart the process (or hit a fresh serverless isolate), list messages - the message is still there with the same id, seq: 1, and createdAt as a valid timestamp.
2. Pair idempotency, both directions. getOrCreateConversation(A→B) then (B→A) - same conversation id both times, and only one row/document exists.
3. Pair idempotency, concurrent. Fire 5+ parallel getOrCreateConversation calls for the same new pair - exactly one conversation exists afterwards.
4. Seq under concurrent sends. Fire 10 parallel sendMessage calls into one conversation - afterwards all 10 seq values are distinct and strictly increasing; no duplicates, no failures.
5. Pagination walk. Send 25 messages, list with limit: 10, follow nextCursor until null - you get exactly 25 unique messages, newest-first, no repeats, no gaps. Same walk for listConversations.
6. Gap-fill. After sending messages 1..N, listMessagesAfterSeq with afterSeq: 3 returns 4..N oldest-first.
7. Edit + soft delete. Edit a message (body changes, editedAt set); delete it (body "", deletedAt set, still present in listMessages with its original seq); editing the deleted message now fails with MESSAGE_DELETED.
8. Read-state. markRead then re-fetch the conversation - that participant's lastReadMessageId is updated and survives a restart. Bonus: markRead with an OLDER message afterwards is a no-op (core enforces monotonicity; your adapter just stores what it's given).
9. Unread counts. B sends 3 messages; getConversation as A shows unreadCount: 3, as B shows 0 (own messages never count). A marks the 2nd read - A now sees 1. Delete the 3rd as B - A still sees 1 (tombstones count).
10. Types at the boundary. createdAt instanceof Date is true on returned conversations and messages (catches ISO-string leakage).
11. Search. Query with different casing; verify participant scoping, relevance order, time tie-breaking, cursor pagination, and tombstone exclusion.
12. Quote-reply round trip. Send a parent, then a reply with replyToMessageId - the reply comes back with that pointer and a replyTo whose excerpt is the parent's body. Edit the parent: the excerpt on a refetched reply changes too (proves it's hydrated, not denormalized). Delete the parent: the reply survives with replyTo.deleted: true and an empty excerpt. A pointer at a message in another conversation is MESSAGE_NOT_FOUND.
13. Reaction idempotency. addReaction the same emoji twice as the same user - exactly one row exists and the returned set shows count: 1. Fire 5 identical addReaction calls in parallel - still one row. Have the other participant add the same emoji - count: 2 with both ids, earliest-first. removeReaction twice - the second is a silent no-op.
14. Reactions do not disturb messages. Note the conversation's list position and unreadCount, add and remove a reaction, re-list: identical ordering, identical unread counts, and the next message still gets the seq it would have had.
15. Groups are not deduplicated. createGroupConversation twice with the same members - two distinct ids, both readable, pairKey: null on both. Then getOrCreateConversation(A→B) still converges on one DM, proving the pair-key constraint survived. This is the check that catches a total-vs-partial index mistake.
16. Group membership. Create a group A(admin)+B, add C - three participants, C a member. Promote C to admin, then add C again: still admin, still one row. Remove B twice - the second is a silent no-op, B's messages are still in listMessages, and B now gets FORBIDDEN_READ on the conversation.
17. Group unread + list. With A, B, C in a group, B and C each send one message: A sees unreadCount: 2, B sees 1 (never their own). The group appears in all three users' listConversations, ordered by activity next to their DMs, and type / name / role survive a restart. Read the conversation twice: participants comes back in the same order both times.
18. Privilege check (hosted DBs). With the browser/anon credentials, a direct read of chatpack_messages, chatpack_message_reactions, chatpack_conversation_participants, or chatpack_conversation_invites returns nothing/denied. The invites table matters as much as the rest: a leaked table is a leaked set of live links. chatpack_conversations too - the channel directory is deliberately public through the API, but the table also holds every private group's name.
19. Invites, if you implemented them. Mint a maxUses: 1 invite and fire 5 parallel accepts - exactly one succeeds, uses is 1, and the group gained exactly one member. Accept an already-spent link as the user it admitted: they still get the conversation back, and uses does not move. Backdate an invite's expires_at and accept: 410 INVITE_EXPIRED, uses still unchanged. Deny a join request, have the same user ask again: one row, back to pending, resolution cleared. Finally, run the same suite against an adapter with the invites property removed - every invite route answers 501 INVITES_UNSUPPORTED and every other route keeps working.
20. Channels round trip. Create a group with visibility: "public", joinPolicy: "open", restart the process, getConversation - both fields survived (this is the check that catches columns you forgot to add). listPublicConversations returns it and not a private group or a DM. Now updateConversation with only visibility: "private": the name is unchanged and the channel leaves the directory. Flip it back with only joinPolicy: "approval": still public, policy changed.
21. Channel joins. Fire 8 parallel joinConversation calls at an "open" channel as the same new user - exactly one participant row, role member, and the rest come back ALREADY_PARTICIPANT or the same success. On an "approval" channel, join twice: one pending join request with the first message kept. As a non-member, getConversation and listMessages on a public channel still throw FORBIDDEN_READ - discoverable is not readable.
22. The 501 gate. Remove the channels property and rerun: GET /channels and POST /conversations/:id/join answer 501 CHANNELS_UNSUPPORTED, so does creating a group with visibility: "public" - but passing the defaults explicitly ("private" / "approval") still succeeds, and every non-channel route keeps working.

Real-time plugins and custom adapters

typing(), presence(), receipts() publish ephemeral events on the SSE stream - never stored, never replayed - so custom storage adapters need zero changes to support them.

If you also implement a custom Transport, note that TransportEvent has four members - ChatEvent | ReactionEvent | ConversationEvent | EphemeralEvent - so !isEphemeralEvent(e) no longer means "a message". Branch on isMessageEvent(e) wherever the message snapshot matters, and use isReactionEvent(e) / isConversationEvent(e) for the other two. See Real-time with SSE.