Skip to content

Latest commit

 

History

History
479 lines (380 loc) · 10 KB

database.mdx

File metadata and controls

479 lines (380 loc) · 10 KB
title Database
description Work with PostgreSQL, Drizzle ORM, and TypeIDs.
icon database

Database

Master the data layer. This guide covers how Quackback uses PostgreSQL with Drizzle ORM, from querying data to creating migrations to understanding TypeIDs.

Overview

Quackback uses:

  • PostgreSQL 14+ as the database (also the job queue and realtime bus)
  • Drizzle ORM for type-safe queries
  • TypeIDs for branded identifiers
  • pgvector for semantic search
  • pg_trgm for text search

Schema Location

Database schema is defined in packages/db/src/schema/:

packages/db/src/schema/
├── index.ts              # Barrel export
├── auth.ts               # Users, sessions, members, OAuth
├── posts.ts              # Posts, comments, votes, tags
├── boards.ts             # Boards, roadmaps, tags
├── statuses.ts           # Post statuses
├── integrations.ts       # Integration configs
├── notifications.ts      # Notifications, subscriptions
├── sentiment.ts          # AI sentiment data
├── merge-suggestions.ts  # AI duplicate detection
├── feedback.ts           # Feedback pipeline (sources, signals)
├── segments.ts           # User segments
├── user-attributes.ts    # Custom user attributes
├── changelog.ts          # Changelog entries
├── api-keys.ts           # API key management
├── webhooks.ts           # Webhook configurations
└── external-links.ts     # External link tracking

Core Tables

Users & Authentication

// users - Portal users
users: {
  id: UserId,
  email: string,
  name: string | null,
  image: string | null,
  createdAt: timestamp,
}

// members - Team members (admins, staff)
members: {
  id: MemberId,
  userId: UserId,
  workspaceId: WorkspaceId,
  role: 'admin' | 'member',
  createdAt: timestamp,
}

Posts & Feedback

// posts - User feedback
posts: {
  id: PostId,
  title: string,
  content: string | null,
  contentJson: jsonb | null,      // Tiptap rich text
  boardId: BoardId,
  statusId: StatusId,
  authorId: UserId | null,
  authorMemberId: MemberId | null,
  voteCount: integer,
  commentCount: integer,
  searchVector: tsvector,          // Full-text search
  createdAt: timestamp,
  updatedAt: timestamp,
  deletedAt: timestamp | null,
}

// votes
votes: {
  id: VoteId,
  postId: PostId,
  userId: UserId,
  createdAt: timestamp,
}

// comments
comments: {
  id: CommentId,
  postId: PostId,
  parentId: CommentId | null,      // Nested comments
  content: string,
  authorId: UserId | null,
  authorMemberId: MemberId | null,
  isPinned: boolean,               // Official response
  createdAt: timestamp,
  deletedAt: timestamp | null,
}

Organization

// boards
boards: {
  id: BoardId,
  name: string,
  slug: string,
  description: string | null,
  access: BoardAccess,  // per-action permissions matrix, see below
  createdAt: timestamp,
  deletedAt: timestamp | null,
}

// BoardAccess (jsonb column)
// Each action has its own tier; per-action segment allowlists apply
// wherever a tier is 'segments'. Moderation is tri-state per board.
type AccessTier = 'anonymous' | 'authenticated' | 'segments' | 'team'
type ModerationRule = 'inherit' | 'on' | 'off'

interface BoardAccess {
  view: AccessTier
  vote: AccessTier
  comment: AccessTier
  submit: AccessTier
  segments: {
    view: SegmentId[]
    vote: SegmentId[]
    comment: SegmentId[]
    submit: SegmentId[]
  }
  moderation: {
    anonPosts: ModerationRule
    signedPosts: ModerationRule
    comments: ModerationRule
  }
}

// statuses
statuses: {
  id: StatusId,
  name: string,
  slug: string,
  color: string,
  showOnRoadmap: boolean,
  order: integer,
}

// tags
tags: {
  id: TagId,
  name: string,
  color: string,
}

// post_tags (junction table)
postTags: {
  postId: PostId,
  tagId: TagId,
}

Database Access

Importing

Always import from the app's db module to ensure proper connection management.
// ✅ Correct
import { db, posts, eq, and, desc } from '@/lib/server/db'

// ❌ Wrong - bypasses tenant context
import { db } from '@quackback/db'

Basic Queries

// Select with conditions
const post = await db.query.posts.findFirst({
  where: eq(posts.id, postId),
})

// Select with relations
const postWithBoard = await db.query.posts.findFirst({
  where: eq(posts.id, postId),
  with: {
    board: true,
    status: true,
    tags: true,
  },
})

// List with filtering
const feedbackPosts = await db.query.posts.findMany({
  where: and(
    eq(posts.boardId, boardId),
    eq(posts.statusId, statusId),
    isNull(posts.deletedAt),
  ),
  orderBy: desc(posts.createdAt),
  limit: 20,
})

Insert

import { createId } from '@quackback/ids'

const [newPost] = await db.insert(posts).values({
  id: createId('post'),
  title: input.title,
  content: input.content,
  boardId: input.boardId,
  statusId: defaultStatusId,
  authorId: user.id,
}).returning()

Update

await db.update(posts)
  .set({
    statusId: newStatusId,
    updatedAt: new Date(),
  })
  .where(eq(posts.id, postId))

Delete (Soft Delete)

// Soft delete - preserve data
await db.update(posts)
  .set({ deletedAt: new Date() })
  .where(eq(posts.id, postId))

// Hard delete - remove permanently
await db.delete(votes)
  .where(eq(votes.postId, postId))

TypeIDs

All entities use branded TypeIDs:

import { createId, type PostId } from '@quackback/ids'

// Generate new ID
const id: PostId = createId('post')
// => "post_01h455vb4pex5vsknk084sn02q"

// Column definition in schema
import { typeIdWithDefault, typeIdColumn } from '@quackback/ids/drizzle'

export const posts = pgTable('posts', {
  // Primary key with auto-generated TypeID
  id: typeIdWithDefault('post')('id').primaryKey(),
  // Foreign key column
  boardId: typeIdColumn('board')('board_id').references(() => boards.id),
})

Benefits:

  • Type safety (can't mix PostId with BoardId)
  • Self-describing (prefix shows entity type)
  • Sortable (timestamp-based UUIDs)

Migrations

Write a Migration

Migrations are hand-written SQL. A schema change is always two edits that land together:

  1. A new file in packages/db/drizzle/, numbered with the next free four-digit number, plus a matching entry appended to packages/db/drizzle/meta/_journal.json (tag is the filename without .sql)
  2. The matching change to the TypeScript schema in packages/db/src/schema/
Don't run `bun run db:generate` (`drizzle-kit generate`). Its snapshot history is stale, so it produces wrong SQL. Write the SQL by hand.

Check that the SQL and the TypeScript schema agree:

bun run db:check-drift

CI runs the same check.

Run Migrations

bun run db:migrate

Migrations run automatically on deployment.

Migration Best Practices

  1. One change per migration - Easier to debug
  2. Replay-safe - A migration must be safe to run again (IF EXISTS, IF NOT EXISTS, guarded updates)
  3. Non-breaking changes - Add columns as nullable first
  4. Data migrations - Handle existing data carefully

Full-Text Search

Posts support full-text search via tsvector:

// Schema includes search vector
export const posts = pgTable('posts', {
  // ...
  searchVector: tsvector('search_vector'),
})

// Search query
const results = await db.query.posts.findMany({
  where: sql`search_vector @@ plainto_tsquery(${searchTerm})`,
})

The search vector is updated automatically via database trigger.

Vector Search (pgvector)

Semantic search using embeddings:

// Store embedding
await db.update(postEmbeddings)
  .set({ embedding: vector })
  .where(eq(postEmbeddings.postId, postId))

// Find similar posts
const similar = await db.execute(sql`
  SELECT id, title, 1 - (embedding <=> ${queryVector}) as similarity
  FROM post_embeddings
  ORDER BY embedding <=> ${queryVector}
  LIMIT 5
`)

Transactions

For atomic operations:

await db.transaction(async (tx) => {
  // All operations use `tx` instead of `db`
  await tx.insert(posts).values(postData)
  await tx.insert(postTags).values(tagData)

  // If any fails, all are rolled back
})

Relations

Define relations in schema:

export const postsRelations = relations(posts, ({ one, many }) => ({
  board: one(boards, {
    fields: [posts.boardId],
    references: [boards.id],
  }),
  status: one(statuses, {
    fields: [posts.statusId],
    references: [statuses.id],
  }),
  comments: many(comments),
  votes: many(votes),
  tags: many(postTags),
}))

Query with relations:

const post = await db.query.posts.findFirst({
  where: eq(posts.id, postId),
  with: {
    board: true,
    status: true,
    comments: {
      where: isNull(comments.deletedAt),
      orderBy: desc(comments.createdAt),
    },
  },
})

Drizzle Studio

Visual database browser:

bun run db:studio

Features:

  • Browse tables
  • Edit data
  • Run queries
  • View relations

Seeding

Demo data for development:

bun run db:seed

Seed file: packages/db/src/seed.ts

Creates:

  • Demo workspace
  • Sample boards and statuses
  • Example posts and comments
  • Demo user accounts

Best Practices

Query Optimization

// ✅ Select only needed columns
const titles = await db
  .select({ id: posts.id, title: posts.title })
  .from(posts)

// ❌ Avoid selecting everything
const allData = await db.select().from(posts)

Indexes

Add indexes for frequently queried columns:

export const postsStatusIdx = index('posts_status_idx')
  .on(posts.statusId)

export const postsCreatedIdx = index('posts_created_idx')
  .on(posts.createdAt)

Soft Deletes

Always check for deletedAt:

const activePosts = await db.query.posts.findMany({
  where: isNull(posts.deletedAt),
})

Next Steps