Database Operations - GTM Feedback

Overview

GTM Feedback uses Drizzle ORM with PostgreSQL (recommended: Neon) for all database operations.

Critical: This project uses Drizzle Relational Query Builder (RQB) syntax exclusively. Never use db.select() patterns.

Database Schema

The schema is located in packages/database/src/schema.ts.

Core Tables

users

User accounts with authentication and admin flags

requests

Feature requests with areas, status, and links

feedback

Customer-specific feedback linked to accounts/opportunities

areas

Product areas for categorization

sfdcAccounts

Salesforce account data (ARR, enterprise status)

sfdcOpportunities

Salesforce opportunities with ARR and stages

Schema Example

export const users = pgTable(
  "users",
  {
    id: uuid().defaultRandom().primaryKey().notNull(),
    name: text("name").notNull(),
    email: text("email").unique().notNull(),
    image: text("image"),
    avatar: text("avatar"),
    emailVerified: timestamp("emailVerified", { mode: "date" }),
    isAdmin: boolean("is_admin").default(false).notNull(),
  },
  (table) => [unique("users_email_key").on(table.email)],
);

Relational Query Builder (RQB)

Critical: Always use Drizzle Relational Query Builder (RQB) syntax. Never use db.select() patterns.

Correct Pattern: Using Query Engine

import { db } from "@feedback/db";

// find many with relations
const feedback = await db.query.feedback.findMany({
  with: {
    user: true,
    entries: {
      with: { user: true },
      orderBy: (entries, { desc }) => [desc(entries.createdAt)]
    },
    comments: {
      orderBy: (comments, { desc }) => [desc(comments.createdAt)],
      limit: 10
    }
  },
  where: (feedback, { eq, and, isNotNull }) => and(
    eq(feedback.status, 'open'),
    isNotNull(feedback.creator)
  ),
  orderBy: (feedback, { desc }) => [desc(feedback.updatedAt)]
});

Incorrect Pattern: Never Use This

import { db } from "@feedback/db";
import { eq } from "drizzle-orm";
import { Feedback } from "@feedback/db/schema";

// NEVER use db.select() syntax
const result = await db.select().from(Feedback).where(eq(Feedback.id, id));

Database Relations

Understanding the relationship patterns:

One-to-Many Relationships

Users → Feedback
Requests → Feedback
Accounts → Opportunities

One user creates many feedback items:

const user = await db.query.users.findFirst({
  where: (users, { eq }) => eq(users.id, userId),
  with: {
    feedback: {
      orderBy: (feedback, { desc }) => [desc(feedback.createdAt)],
      limit: 10
    }
  }
});

One request has many feedback items:

const request = await db.query.requests.findFirst({
  where: (requests, { eq }) => eq(requests.id, requestId),
  with: {
    feedback: {
      with: { user: true },
      orderBy: (feedback, { desc }) => [desc(feedback.createdAt)]
    }
  }
});

One account has many opportunities:

const account = await db.query.sfdcAccounts.findFirst({
  where: (accounts, { eq }) => eq(accounts.id, accountId),
  with: {
    opportunities: {
      orderBy: (opps, { desc }) => [desc(opps.arr)]
    }
  }
});

Migrations

Configuration

Drizzle is configured in packages/database/drizzle.config.ts:

import "dotenv/config";
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/schema.ts",
  out: "./drizzle/migrations",
  strict: true,
  verbose: true,
  dbCredentials: {
    url: process.env.DATABASE_URL ?? "",
  },
});

Applying Schema Changes

Push schema changes

For development, use db:push to sync schema without migrations:

pnpm db:push

This is the recommended approach for local development.

Generate migrations (production)

For production deployments, generate migration files:

cd packages/database
pnpm drizzle-kit generate

Apply migrations

Apply generated migrations:

pnpm drizzle-kit migrate

The db:push command is filtered to run in the www workspace: pnpm --filter www db:push

Seeding Data

The seed script populates demo data for local development:

pnpm db:seed

This script:

Creates demo users
Creates product areas
Populates feature requests
Generates sample feedback
Creates embeddings (if AI configured)
Links accounts and opportunities

Seeding is optional but helpful for testing the full application flow.

Query Patterns from CLAUDE.md

Follow these established patterns from the codebase:

Pattern 1: Simple Queries

// fetch single record by ID
const request = await db.query.requests.findFirst({
  where: (requests, { eq }) => eq(requests.id, requestId)
});

// fetch with basic filtering
const openRequests = await db.query.requests.findMany({
  where: (requests, { eq }) => eq(requests.status, 'open'),
  orderBy: (requests, { desc }) => [desc(requests.updatedAt)]
});

Pattern 2: Eager Loading Relations

// load related data in a single query
const feedback = await db.query.feedback.findFirst({
  where: (feedback, { eq }) => eq(feedback.id, feedbackId),
  with: {
    user: true,  // load creator
    request: {   // load request with its creator
      with: { user: true }
    },
    comments: {  // load comments with pagination
      with: { user: true },
      orderBy: (comments, { desc }) => [desc(comments.createdAt)],
      limit: 10
    }
  }
});

Pattern 3: Complex Filtering

// combine multiple conditions
const results = await db.query.feedback.findMany({
  where: (feedback, { eq, and, or, gte, inArray }) => and(
    eq(feedback.severity, 'high'),
    or(
      inArray(feedback.accountId, accountIds),
      gte(feedback.createdAt, thirtyDaysAgo)
    )
  ),
  with: { user: true, request: true }
});

Pattern 4: Ordering and Limiting

// sort and paginate results
const recentFeedback = await db.query.feedback.findMany({
  where: (feedback, { eq }) => eq(feedback.requestId, requestId),
  with: { user: true },
  orderBy: (feedback, { desc }) => [desc(feedback.createdAt)],
  limit: 20,
  offset: page * 20
});

Common Operations

Insert new records

import { db } from "@feedback/db";
import { feedback } from "@feedback/db/schema";

const [newFeedback] = await db.insert(feedback)
  .values({
    requestId,
    accountId,
    severity: 'high',
    description: 'Customer needs this feature',
    creator: userId,
  })
  .returning();

Update existing records

import { db } from "@feedback/db";
import { requests } from "@feedback/db/schema";
import { eq } from "drizzle-orm";

await db.update(requests)
  .set({ 
    status: 'shipped',
    updatedAt: new Date().toISOString()
  })
  .where(eq(requests.id, requestId));

Delete records

import { db } from "@feedback/db";
import { feedback } from "@feedback/db/schema";
import { eq } from "drizzle-orm";

await db.delete(feedback)
  .where(eq(feedback.id, feedbackId));

Count records

import { db } from "@feedback/db";
import { requests } from "@feedback/db/schema";
import { eq, count } from "drizzle-orm";

const [result] = await db
  .select({ count: count() })
  .from(requests)
  .where(eq(requests.status, 'open'));

Best Practices

Use RQB Syntax

Always use db.query.tableName patterns, never db.select()

Eager Load Relations

Use with clause to avoid N+1 queries

Type Safety

Let TypeScript infer types from Drizzle queries

Indexes

Use indexes for frequently queried fields (defined in schema)

Troubleshooting

Connection errors

Verify your DATABASE_URL environment variable:

echo $DATABASE_URL

Test the connection:

psql $DATABASE_URL

Migration conflicts

If migrations are out of sync:

# Reset local database (dev only!)
pnpm db:push
pnpm db:seed

Type errors with queries

Ensure you’re using the callback syntax for where and orderBy:

// correct
where: (table, { eq }) => eq(table.id, id)

// incorrect
where: eq(table.id, id)

Get Started

Core Concepts

Deployment

Features

Integrations

Development

​Overview

​Database Schema

​Core Tables

users

requests

feedback

areas

sfdcAccounts

sfdcOpportunities

​Schema Example

​Relational Query Builder (RQB)

​Correct Pattern: Using Query Engine

​Incorrect Pattern: Never Use This

​Database Relations

​One-to-Many Relationships

​Migrations

​Configuration

​Applying Schema Changes

​Seeding Data

​Query Patterns from CLAUDE.md

​Pattern 1: Simple Queries

​Pattern 2: Eager Loading Relations

​Pattern 3: Complex Filtering

​Pattern 4: Ordering and Limiting

​Common Operations

​Best Practices

Use RQB Syntax

Eager Load Relations

Type Safety

Indexes

​Troubleshooting

​Next Steps

Workflows

Contributing

Build docs developers (and LLMs) love

Overview

Database Schema

Core Tables

Schema Example

Relational Query Builder (RQB)

Correct Pattern: Using Query Engine

Incorrect Pattern: Never Use This

Database Relations

One-to-Many Relationships

Migrations

Configuration

Applying Schema Changes

Seeding Data

Query Patterns from CLAUDE.md

Pattern 1: Simple Queries

Pattern 2: Eager Loading Relations

Pattern 3: Complex Filtering

Pattern 4: Ordering and Limiting

Common Operations

Best Practices

Troubleshooting

Next Steps