Skip to main content

​
Overview

The @feedback/db package provides the database layer for GTM Feedback, built on Drizzle ORM with PostgreSQL. It includes schema definitions, type-safe queries, and relation mappings for users, requests, feedback, areas, and Salesforce data.

​
Installation

npm install @feedback/db

​
Package Information

​
name
string
@feedback/db
​
version
string
0.0.1
​
dependencies
object
  • drizzle-orm: ^0.44.5
  • pg: ^8.16.3
  • @auth/core: ^0.41.1

​
Exports

The package provides multiple export paths:

​
Main Export

import { db } from '@feedback/db'
​
db
DrizzleDB
Pre-configured Drizzle database client with full schema and relations. Ready to use with type-safe queries.

​
Schema

import {
  users,
  accounts,
  sessions,
  authenticators,
  requests,
  feedback,
  areas,
  sfdcAccounts,
  sfdcOpportunities,
  requestStatusEnum,
  severityEnum,
  feedbackCreationSourceEnum
} from '@feedback/db/schema'
​
users
PgTable
User accounts table with authentication detailsColumns:
  • id (uuid): Primary key
  • name (text): User’s full name
  • email (text): Unique email address
  • image (text, optional): Profile image URL
  • avatar (text, optional): Avatar URL
  • emailVerified (timestamp, optional): Email verification timestamp
  • isAdmin (boolean): Admin flag (default: false)
​
requests
PgTable
Feature requests tableColumns:
  • id (uuid): Primary key
  • title (text): Request title
  • description (text): Detailed description
  • status (requestStatusEnum): ‘open’ | ‘shipped’ | ‘deprioritized’
  • creator (uuid): Foreign key to users
  • createdAt (timestamp): Creation timestamp
  • updatedAt (timestamp): Last update timestamp
  • slug (text): Unique URL-friendly identifier
  • notes (text, optional): Internal notes
  • areaIds (varchar[]): Array of product area IDs
  • relatedLinks (text[]): Array of related URLs
  • linearUrl (text, optional): Linear issue URL
  • followers (uuid[]): Array of user IDs following this request
  • relatedRequestId (uuid, optional): Parent/related request
  • metadata (jsonb, optional): Additional metadata
​
feedback
PgTable
Customer feedback entries linked to requestsColumns:
  • id (uuid): Primary key
  • requestId (uuid): Foreign key to requests
  • accountId (text): Salesforce account ID
  • opportunityId (text, optional): Salesforce opportunity ID
  • severity (severityEnum): ‘low’ | ‘medium’ | ‘high’
  • description (text): Feedback details
  • creator (uuid): Foreign key to users
  • createdAt (timestamp): Creation timestamp
  • externalLinks (text[]): Array of external reference URLs
  • slug (text, optional): Unique identifier within request
  • creationSource (feedbackCreationSourceEnum): ‘manual’ | ‘agent’
  • metadata (jsonb, optional): Additional metadata
​
areas
PgTable
Product areas for categorizing requestsColumns:
  • id (varchar): Primary key (max 36 chars)
  • name (text): Unique area name
  • slug (text): Unique URL-friendly identifier
  • description (text): Area description
  • firehoseChannel (text, optional): Slack channel for notifications
​
sfdcAccounts
PgTable
Salesforce account dataColumns:
  • id (text): Primary key (Salesforce ID)
  • name (text): Account name
  • type (text, optional): Account type
  • regionName (text, optional): Geographic region
  • arr (numeric, optional): Annual recurring revenue
  • isEnterprise (boolean): Enterprise account flag
  • website (text, optional): Company website
  • logo (text, optional): Logo URL
  • link (text, optional): Salesforce record link
  • createdAt (timestamp): Creation timestamp
  • updatedAt (timestamp, optional): Last update timestamp
​
sfdcOpportunities
PgTable
Salesforce opportunity dataColumns:
  • id (text): Primary key (Salesforce ID)
  • accountId (text): Foreign key to sfdcAccounts
  • name (text): Opportunity name
  • arr (numeric, optional): Annual recurring revenue
  • stage (text, optional): Sales stage
  • closeDate (date, optional): Expected close date
  • createdAt (timestamp, optional): Creation timestamp
  • updatedAt (timestamp, optional): Last update timestamp

​
Types

import type {
  User,
  NewUser,
  Request,
  NewRequest,
  Feedback,
  NewFeedback,
  FeedbackWithRequest,
  FeedbackWithAccount,
  FeedbackWithRequestAndAccount,
  FeedbackWithUser,
  FeedbackWithUserAndAccount,
  SfdcAccount,
  SfdcOpportunity,
  ProductArea,
  NewProductArea,
  SeverityGroup,
  Severity,
  RequestStatus
} from '@feedback/db/types'
​
User
type
Inferred select type from users table
​
NewUser
type
Inferred insert type for creating users
​
Request
type
Inferred select type from requests table
​
NewRequest
type
Inferred insert type for creating requests
​
Feedback
type
Inferred select type from feedback table
​
NewFeedback
type
Inferred insert type for creating feedback
​
Severity
type
Union type: ‘low’ | ‘medium’ | ‘high’
​
RequestStatus
type
Union type: ‘open’ | ‘shipped’ | ‘deprioritized’

​
Relations

import {
  usersRelations,
  requestsRelations,
  feedbackRelations,
  areasRelations,
  sfdcAccountsRelations,
  sfdcOpportunitiesRelations
} from '@feedback/db/relations'
Relation definitions for Drizzle’s relational queries. These enable type-safe nested queries with .with() syntax.

​
Database Client Usage

​
Basic Queries

import { db, requests, users } from '@feedback/db'
import { eq, desc } from 'drizzle-orm'

// Select all open requests
const openRequests = await db
  .select()
  .from(requests)
  .where(eq(requests.status, 'open'))
  .orderBy(desc(requests.updatedAt))

// Select user by email
const user = await db
  .select()
  .from(users)
  .where(eq(users.email, '[email protected]'))
  .limit(1)

​
Relational Queries

import { db, users, requests, feedback } from '@feedback/db'
import { eq } from 'drizzle-orm'

// Query request with nested feedback and user data
const requestWithFeedback = await db.query.requests.findFirst({
  where: eq(requests.id, requestId),
  with: {
    feedback: {
      with: {
        creator: true
      }
    },
    creator: true
  }
})

// Query user with all their requests
const userWithRequests = await db.query.users.findFirst({
  where: eq(users.email, '[email protected]'),
  with: {
    requests: {
      orderBy: desc(requests.updatedAt),
      limit: 10
    }
  }
})

​
Query Patterns

​
Filtering by Arrays

import { db, requests } from '@feedback/db'
import { sql } from 'drizzle-orm'

// Find requests in specific areas
const analyticsRequests = await db
  .select()
  .from(requests)
  .where(
    sql`${requests.areaIds} && ARRAY['analytics']::varchar[]`
  )

// Find requests with specific follower
const userFollowedRequests = await db
  .select()
  .from(requests)
  .where(
    sql`${userId} = ANY(${requests.followers})`
  )

​
Aggregations

import { db, feedback, requests } from '@feedback/db'
import { eq, sql } from 'drizzle-orm'

// Count feedback by severity for a request
const severityCounts = await db
  .select({
    severity: feedback.severity,
    count: sql<number>`count(*)`
  })
  .from(feedback)
  .where(eq(feedback.requestId, requestId))
  .groupBy(feedback.severity)

​
Joins with Salesforce Data

import { db, feedback, sfdcAccounts, sfdcOpportunities } from '@feedback/db'
import { eq } from 'drizzle-orm'

// Get feedback with account and opportunity details
const enrichedFeedback = await db
  .select({
    feedback,
    account: sfdcAccounts,
    opportunity: sfdcOpportunities
  })
  .from(feedback)
  .innerJoin(sfdcAccounts, eq(feedback.accountId, sfdcAccounts.id))
  .leftJoin(
    sfdcOpportunities,
    eq(feedback.opportunityId, sfdcOpportunities.id)
  )
  .where(eq(feedback.requestId, requestId))

​
Database Scripts

​
db:generate
script
Generate migration files from schema changes
npm run db:generate
​
db:migrate
script
Run pending migrations
npm run db:migrate
​
db:push
script
Push schema changes directly (development only)
npm run db:push
​
db:studio
script
Launch Drizzle Studio for database exploration
npm run db:studio

​
Environment Variables

​
DATABASE_URL
string
required
PostgreSQL connection string
DATABASE_URL="postgresql://user:password@host:5432/database"

Build docs developers (and LLMs) love

Get started for freeTalk to us