Specly
All implementations

React Router + Drizzle + SQLite

detailed

Full-stack TypeScript implementation using React Router framework mode, Drizzle ORM against SQLite (Turso-ready), better-auth for sessions, and Server-Sent Events for realtime. Deploys as a single process — Fly.io or Railway.

For spec: Slack-like Messaging App

Source
Frontend
React Router (framework mode)
Backend
Bun + React Router
Database
SQLite / Turso
ORM
Drizzle
Auth
better-auth
Hosting
Fly.io
9 tables8 routes5 modules5 packages7 env vars3 features uncovered

Setup

1. `bun install`
2. `bunx drizzle-kit migrate`
3. `bunx @better-auth/cli migrate`
4. `bun run dev`

Packages

react-router^7— Framework + routing
drizzle-orm^0.34— ORM
drizzle-kit^0.27— Migrations
better-auth^1.0— Auth sessions
better-sqlite3^11— SQLite driver

Environment

DATABASE_URLrequired= ./db/app.db
BETTER_AUTH_SECRETrequired

32+ char secret

BETTER_AUTH_URLrequired= http://localhost:5173

Feature implementations

File Storage

file-storage

Presigned S3 uploads; attachments rows reference messages.

Tables

attachments5 fields
  • idtext · PK
  • message_idtext · → messages
  • s3_keytext
  • mimetext
  • size_bytesinteger

Routes

POST/api/uploads/presignauth

app/routes/api.uploads.presign.tsx

200 { url: string; key: string }

Env vars

S3_BUCKET *S3_ACCESS_KEY_ID *S3_SECRET_ACCESS_KEY *

Messaging

messaging

Channels and DMs share a messages table. Realtime via per-channel SSE streams backed by an in-process emitter.

Tables

messages7 fields
  • idtext · PK
  • channel_idtext · nullable · → channels
  • recipient_idtext · nullable · → user
  • sender_idtext · → user
  • contenttext
  • parent_idtext · nullable · → messages — thread root
  • created_atinteger
Indexes: (channel_id, created_at) · (recipient_id, sender_id, created_at)

Exactly one of channel_id / recipient_id must be set — enforced in the action.

channels4 fields
  • idtext · PK
  • nametext
  • is_privateinteger · default 0
  • created_atinteger
channel_members3 fields
  • channel_idtext · → channels
  • user_idtext · → user
  • joined_atinteger
Indexes: UNIQUE (channel_id, user_id)

Routes

GET/channels/:channelIdauth

app/routes/channels.$channelId.tsx

Params: channelId:string (path)

200 { messages: Message[]; channel: Channel }

POST/api/messagesauth

app/routes/api.messages.tsx

Params: channelId?:string (body), recipientId?:string (body), content:string (body)

201 { message: Message }

GET/api/messages/streamauth

Per-channel SSE stream of new messages

app/routes/api.messages.stream.tsx

Params: channelId:string (query)

Modules

  • app/lib/realtime/emitter.ts — In-process EventEmitter for fan-out
  • app/lib/messaging/service.ts — createMessage / listMessages

Notifications

notifications

Notifications table + SSE stream; falls back to email via Resend for offline users.

Tables

notifications6 fields
  • idtext · PK
  • user_idtext · → user
  • kindtext — e.g. "mention", "dm"
  • payloadtext — JSON blob
  • read_atinteger · nullable
  • created_atinteger
Indexes: (user_id, read_at)

Routes

GET/api/notifications/streamauth

SSE; emits notification objects for the current user

app/routes/api.notifications.stream.tsx

Env vars

RESEND_API_KEY

Roles & Permissions

roles-permissions

Workspace-scoped roles (owner, admin, member) enforced via a canPerform() helper.

Tables

workspace_members3 fields
  • workspace_idtext
  • user_idtext · → user
  • roletext — "owner" | "admin" | "member"
Indexes: UNIQUE (workspace_id, user_id)

Modules

  • app/lib/rbac/can.ts — canPerform(user, action, resource) helper

Search

search

SQLite FTS5 virtual table over messages.content rebuilt incrementally via triggers.

Tables

messages_fts

Virtual FTS5 table shadowing messages(id, content)

Backfill with `bun run db:fts`; kept in sync by AFTER INSERT/UPDATE/DELETE triggers.

Routes

GET/searchauth

app/routes/search.tsx

Params: q:string (query)

User System & Auth

user-system

better-auth with email/password; session cookie middleware per loader.

Tables

user5 fields

Managed by better-auth — do not hand-edit.

  • idtext · PK
  • emailtext · unique
  • nametext · nullable
  • emailVerifiedinteger · default 0
  • createdAtinteger
Indexes: UNIQUE (email)
session4 fields
  • idtext · PK
  • userIdtext · → user
  • tokentext · unique
  • expiresAtinteger
Indexes: UNIQUE (token) · (userId)

Routes

GET/auth/login

app/routes/auth.login.tsx

ANY/api/auth/*

better-auth mounted handler

app/routes/api.auth.$.tsx

Modules

  • app/lib/auth/index.ts — betterAuth() instance
  • app/lib/auth/session.ts — getSession / requireSession helpers

Uncovered features

The spec includes features this implementation doesn't describe. Contributions welcome.

Audit LoggingRate Limiting & Abuse PreventionOnboarding & Activation