Data model
Tables in D1, how they relate, and the ID format.
The better-auth tables (user, session, account, verification) already exist in packages/db/src/schema/auth.ts. v1 adds the tables below. The OAuth tables for MCP sign-in (jwks, oauth_client, oauth_access_token, oauth_refresh_token, oauth_consent, oauth_resource, oauth_client_resource, oauth_client_assertion) are in packages/db/src/schema/oauth.ts; better-auth owns them.
Notes
- IDs. notifier IDs are
<prefix>_<26 lowercase base32 chars>, for examplemsg_01jd8x5k2m9q7r4t6v8w0y3z5a. The characters are a ULID, so IDs sort by creation time and work as pagination cursors. User IDs come from better-auth. The prefixes are defined inID_PREFIXESinpackages/contract/src/common.ts. - Project names are unique across the deployment, because they appear in publish URLs. Open
- Time is stored as Unix milliseconds in integer columns, like the existing auth tables, and exposed as ISO-8601 strings in the API.
- JSON columns (
tags_json,actions_json,conversation_json,sender_json,scope_json,grants_json) are validated with the contract's zod schemas on write and on read. D1 has no JSON column type. - Integrity. Enum-like columns (
role,kind,platform) andmessage.priorityhave SQLCHECKconstraints, andaccess_token.hashmust be 64 lowercase hex characters, so bad rows fail in the database too. - Project → Feature → Notification.
project.personal_user_idmarks a user's private General project; a unique index allows one per user. Afeaturebelongs to one project, and itsslug(lowercase[a-z0-9][a-z0-9-]{0,62}, checked in SQL too) is theconversation.idof its messages, so a feature's thread is conversation keyc:<slug>. Messages without a conversation form the project-level thread, keyp:<project id>. Deleting a project deletes its features. - Every conversation is a feature. Every publish path (REST, app API, MCP) finds or creates the project's feature for a message's
conversation.id: an id that is a valid slug once lowercased is the slug, any other id is slugified (org/repobecomesorg-repo, with-2,-3… when that slug belongs to a feature with another name). The message is stored withconversation = { id: <slug>, title: <feature name> }and moves the feature'slast_message_at. Migration20261120222058_feature_backfillgave existing conversations a feature row when their id was already a valid slug; other ids keep working as inbox threads without a feature. - Project ids and keys. Project ids are
prj_…everywhere, including inside invite grants and token scopes, and the project-level thread key isp:<project id>. Rows written before the current model were rewritten by a migration. - Read state is one row per user and message in
message_read. Unread counts come from messages in the user's projects with no row for that user. - Indexes.
message (project_id, id desc)for history,message (expires_at)for cleanup,device (user_id),project_member (user_id). - Deleting. Removing a project cascades to members, subscriptions, messages, reads and responses. Attachment files in R2 are deleted by the retention job, not inside the request.