PostgreSQL Database Guide

Simple guide to understanding how the database works in this project.

1. Tech Stack

Component	Technology	Why?
Database	PostgresSQL (Supabase)	External SQL database
ORM	GORM v2	Simplifies database operations in Go
Migrations	golang-migrate	Manages database schema changes

2. Connection Setup

File: internal/database/database.go

func New(ctx context.Context) (*DB, error) {
    connStr := os.Getenv("POSTGRES_URL")

    gormDB, err := gorm.Open(postgres.Open(connStr), &gorm.Config{})
    if err != nil {
        return nil, err
    }

    sqlDB, _ := gormDB.DB()
    sqlDB.SetMaxOpenConns(15)  // Max 15 concurrent connections
    sqlDB.SetMaxIdleConns(5)   // Keep 5 idle connections

    return &DB{DB: gormDB}, nil
}

Environment Variable:

POSTGRES_URL=postgresql://user:password@host:5432/database

3. Repository Pattern

All database operations go through repositories. Each model has its own repository.

Structure:

internal/repository/
├── user_repository.go              # User operations
├── organization_repository.go      # Organization operations
├── room_repository.go              # Room operations
└── user_session_repository.go      # Session operations

Basic Template:

type UserRepository struct {
    db *gorm.DB
}

func NewUserRepository(db *gorm.DB) (*UserRepository, error) {
    return &UserRepository{db: db}, nil
}

4. CRUD Operations

CREATE

func (r *UserRepository) Create(ctx context.Context, req *CreateUserRequest) (*User, error) {
    user := &User{
        WorkosId:       req.WorkosId,
        InvitationLink: req.InvitationLink,
    }

    if err := r.db.WithContext(ctx).Create(user).Error; err != nil {
        return nil, fmt.Errorf("failed to create user: %w", err)
    }

    return user, nil  // user.Id is auto-populated
}

Key Points:

Use WithContext(ctx) for tracing
GORM auto-sets created_at and updated_at
Primary key Id is auto-generated

READ

Get by ID:

func (r *UserRepository) GetById(ctx context.Context, id int64) (*User, error) {
    var user User
    err := r.db.WithContext(ctx).First(&user, id).Error

    if errors.Is(err, gorm.ErrRecordNotFound) {
        return nil, fmt.Errorf("user not found")
    }

    return &user, err
}

Get by field:

func (r *UserRepository) GetByWorkosId(ctx context.Context, workosId string) (*User, error) {
    var user User
    err := r.db.WithContext(ctx).
        Where("workos_id = ?", workosId).
        First(&user).Error

    if errors.Is(err, gorm.ErrRecordNotFound) {
        return nil, fmt.Errorf("user not found")
    }

    return &user, err
}

List with pagination:

func (r *UserRepository) List(ctx context.Context, limit, offset int) ([]*User, error) {
    var users []*User
    err := r.db.WithContext(ctx).
        Order("created_at DESC").
        Limit(limit).
        Offset(offset).
        Find(&users).Error

    return users, err
}

UPDATE

func (r *UserRepository) Update(ctx context.Context, id int64, req *UpdateUserRequest) (*User, error) {
    var user User

    // Find user first
    if err := r.db.WithContext(ctx).First(&user, id).Error; err != nil {
        if errors.Is(err, gorm.ErrRecordNotFound) {
            return nil, fmt.Errorf("user not found")
        }
        return nil, err
    }

    // Build update map (only non-empty fields)
    updates := make(map[string]interface{})
    if req.WorkosId != "" {
        updates["workos_id"] = req.WorkosId
    }
    if req.InvitationLink != nil {
        updates["invitation_link"] = req.InvitationLink
    }

    // Apply updates
    if len(updates) > 0 {
        if err := r.db.WithContext(ctx).Model(&user).Updates(updates).Error; err != nil {
            return nil, err
        }
    }

    return r.GetById(ctx, id)
}

Why use a map?

Allows partial updates
Can set nullable fields to NULL
GORM auto-updates updated_at

DELETE

func (r *UserRepository) Delete(ctx context.Context, id int64) error {
    result := r.db.WithContext(ctx).Delete(&User{}, id)

    if result.Error != nil {
        return result.Error
    }

    if result.RowsAffected == 0 {
        return fmt.Errorf("user not found")
    }

    return nil
}

Important: Check RowsAffected to verify the record existed.

5. Domain Models

Location: internal/domain/database/*.go

Example:

package models

type User struct {
    Id             int64     `json:"id" gorm:"primaryKey"`
    WorkosId       string    `json:"workos_id" gorm:"uniqueIndex;not null"`
    InvitationLink *string   `json:"invitation_link,omitempty"`
    LastLoggedOrg  string    `json:"last_logged_org"`
    CreatedAt      time.Time `json:"created_at" gorm:"autoCreateTime"`
    UpdatedAt      time.Time `json:"updated_at" gorm:"autoUpdateTime"`

    // Relationships
    Organizations []Organization `gorm:"many2many:user_organizations"`
}

func (User) TableName() string {
    return "users"
}

Common GORM Tags:

Tag	Purpose
`primaryKey`	Primary key column
`uniqueIndex`	Unique constraint
`not null`	NOT NULL constraint
`default:`	Default value
`autoCreateTime`	Auto-set on create
`autoUpdateTime`	Auto-set on update
`foreignKey:`	Foreign key column
`many2many:`	Junction table name

6. Migrations

Location: internal/database/migrations/*.sql

File Naming

000001_initial_schema.up.sql
000001_initial_schema.down.sql
000002_schema_updates.up.sql
000002_schema_updates.down.sql

Migration Template

-- Migration: Create users table
-- Version: 000001

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    workos_id VARCHAR(255) NOT NULL,
    invitation_link VARCHAR(500),
    last_logged_org VARCHAR(255),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes
CREATE UNIQUE INDEX idx_users_workos_id ON users(workos_id);
CREATE INDEX idx_users_created_at ON users(created_at DESC);

Running Migrations

Migrations run automatically on app startup:

func (d *DB) RunMigrations() error {
    // ... setup code ...

    if err := m.Up(); err != nil && err != migrate.ErrNoChange {
        return err
    }

    return nil
}

Features:

Embedded in binary (no external files needed)
Idempotent (safe to run multiple times)
Version tracked in schema_migrations table

7. Database Schema

For the complete database schema including:

Entity Relationship Diagram (Mermaid)
Detailed table descriptions
Constraints and indexes
Foreign key behaviors
Cardinality explanations
Sample SQL queries

See: DATABASE_SCHEMA.md

Quick Overview

Table	Purpose
`users`	Authenticated users from WorkOS
`organizations`	Organizations from WorkOS
`rooms`	LiveKit meeting rooms
`user_sessions`	User login sessions with refresh tokens
`user_organizations`	Many-to-many: users ↔ organizations
`user_integrations`	Many-to-many: users ↔ integrations
`integrations`	Third-party integrations (Google Calendar, etc.)
`valid_room_combinations`	Whitelist of valid room layouts
`subscription_link`	Pilot program invitation links

8. GORM Query Guide

Basic Querying

GORM provides a fluent API for building queries. Here are common patterns:

Find One Record

// By primary key
var user User
db.First(&user, 1)  // SELECT * FROM users WHERE id = 1;

// With WHERE clause
db.Where("workos_id = ?", "user_123").First(&user)
// SELECT * FROM users WHERE workos_id = 'user_123' LIMIT 1;

// Using struct conditions
db.Where(&User{WorkosId: "user_123"}).First(&user)

Find Multiple Records

// Get all
var users []User
db.Find(&users)  // SELECT * FROM users;

// With conditions
db.Where("created_at > ?", time.Now().AddDate(0, -1, 0)).Find(&users)
// SELECT * FROM users WHERE created_at > '2025-12-14';

// With IN clause
db.Where("id IN ?", []int64{1, 2, 3}).Find(&users)
// SELECT * FROM users WHERE id IN (1,2,3);

// With multiple conditions (AND)
db.Where("workos_id = ? AND last_logged_org = ?", "user_123", "org_456").Find(&users)

Pagination & Ordering

// Order by
db.Order("created_at desc").Find(&users)

// Limit & Offset (pagination)
db.Limit(10).Offset(20).Find(&users)  // Get 10 records starting from record 21

// Combined
db.Where("status = ?", "active").
   Order("created_at desc").
   Limit(10).
   Offset(0).
   Find(&users)

Counting

// Count records
var count int64
db.Model(&User{}).Count(&count)

// Count with WHERE
db.Model(&User{}).Where("status = ?", "active").Count(&count)

Selecting Specific Columns

// Select specific columns
var users []User
db.Select("id", "workos_id", "created_at").Find(&users)
// SELECT id, workos_id, created_at FROM users;

// Select with custom names
type Result struct {
    ID   int64
    Name string
}
var results []Result
db.Table("users").Select("id, workos_id as name").Scan(&results)

Advanced Queries

OR Conditions

// Using OR
db.Where("workos_id = ?", "user_123").
   Or("email = ?", "user@example.com").
   Find(&users)
// SELECT * FROM users WHERE workos_id = 'user_123' OR email = 'user@example.com';

LIKE / ILIKE (Case-insensitive)

// Case-sensitive LIKE
db.Where("name LIKE ?", "%john%").Find(&users)

// Case-insensitive ILIKE (PostgreSQL)
db.Where("name ILIKE ?", "%john%").Find(&rooms)

NOT Conditions

// NOT
db.Not("workos_id = ?", "user_123").Find(&users)
// SELECT * FROM users WHERE NOT workos_id = 'user_123';

// NOT IN
db.Not(map[string]interface{}{"id": []int64{1, 2, 3}}).Find(&users)

Joins

// Simple join
var rooms []Room
db.Joins("JOIN organizations ON rooms.org_id = organizations.id").
   Where("organizations.workos_org_id = ?", "org_123").
   Find(&rooms)

// Left join
db.Joins("LEFT JOIN user_sessions ON users.id = user_sessions.user_id").
   Find(&users)

Group By & Having

// Group by with aggregation
type Result struct {
    OrgID int64
    Count int64
}
var results []Result
db.Table("rooms").
   Select("org_id, count(*) as count").
   Group("org_id").
   Having("count(*) > ?", 5).
   Scan(&results)

Raw SQL

// Raw query
var users []User
db.Raw("SELECT * FROM users WHERE workos_id = ?", "user_123").Scan(&users)

// Execute raw SQL (non-query)
db.Exec("UPDATE users SET last_logged_org = ? WHERE id = ?", "org_456", 1)

Relationships

Preload (Eager Loading)

// Preload single association
var user User
db.Preload("Organizations").First(&user, 1)
// Loads user AND all their organizations

// Preload multiple associations
db.Preload("Organizations").
   Preload("UserSessions").
   First(&user, 1)

// Nested preload
db.Preload("Organizations.Rooms").First(&user, 1)
// Loads user -> organizations -> rooms for each org

// Conditional preload
db.Preload("Organizations", "initialized = ?", true).First(&user, 1)

Associations

// Append associations (many-to-many)
var user User
var org Organization
db.First(&user, 1)
db.First(&org, 1)

// Add organization to user
db.Model(&user).Association("Organizations").Append(&org)

// Remove association
db.Model(&user).Association("Organizations").Delete(&org)

// Replace all associations
db.Model(&user).Association("Organizations").Replace(&org)

// Clear all associations
db.Model(&user).Association("Organizations").Clear()

// Count associations
count := db.Model(&user).Association("Organizations").Count()

Transactions

// Basic transaction
err := db.Transaction(func(tx *gorm.DB) error {
    // Create user
    if err := tx.Create(&user).Error; err != nil {
        return err  // Rollback
    }

    // Create session
    if err := tx.Create(&session).Error; err != nil {
        return err  // Rollback
    }

    return nil  // Commit
})

Checking Existence

// Check if record exists
var exists bool
err := db.Model(&User{}).
    Select("count(*) > 0").
    Where("workos_id = ?", "user_123").
    Find(&exists).Error

// Or using Take (doesn't error if not found)
var user User
err := db.Where("workos_id = ?", "user_123").Take(&user).Error
if err == gorm.ErrRecordNotFound {
    // Doesn't exist
}

Batch Operations

// Create multiple records
users := []User{
    {WorkosId: "user_1"},
    {WorkosId: "user_2"},
    {WorkosId: "user_3"},
}
db.Create(&users)  // Batch insert

// Update multiple records
db.Model(&User{}).
   Where("status = ?", "pending").
   Update("status", "active")

// Delete multiple records
db.Where("created_at < ?", time.Now().AddDate(-1, 0, 0)).
   Delete(&User{})

Common GORM Methods

Method	Purpose	Example
`First()`	Get first matching record	`db.First(&user, 1)`
`Take()`	Get one record (no order)	`db.Take(&user)`
`Last()`	Get last record	`db.Last(&user)`
`Find()`	Get all matching records	`db.Find(&users)`
`Where()`	Add WHERE condition	`db.Where("id = ?", 1)`
`Or()`	Add OR condition	`db.Or("name = ?", "John")`
`Not()`	Add NOT condition	`db.Not("id = ?", 1)`
`Order()`	Add ORDER BY	`db.Order("created_at desc")`
`Limit()`	Add LIMIT	`db.Limit(10)`
`Offset()`	Add OFFSET	`db.Offset(20)`
`Group()`	Add GROUP BY	`db.Group("status")`
`Having()`	Add HAVING	`db.Having("count > ?", 5)`
`Joins()`	Add JOIN	`db.Joins("JOIN orgs ON...")`
`Preload()`	Eager load associations	`db.Preload("Organizations")`
`Select()`	Select specific columns	`db.Select("id", "name")`
`Omit()`	Omit columns	`db.Omit("password")`
`Count()`	Count records	`db.Model(&User{}).Count(&count)`
`Create()`	Insert record	`db.Create(&user)`
`Save()`	Insert or update	`db.Save(&user)`
`Update()`	Update single field	`db.Update("name", "John")`
`Updates()`	Update multiple fields	`db.Updates(map[...])`
`Delete()`	Delete record	`db.Delete(&user, 1)`

Error Handling

// Check for specific errors
err := db.First(&user, id).Error

if err == gorm.ErrRecordNotFound {
    // Record doesn't exist
} else if err != nil {
    // Other database error
}

// Check affected rows
result := db.Delete(&user, id)
if result.RowsAffected == 0 {
    // No rows deleted
}

Tips & Tricks

Chain methods for clean queries

query := db.Model(&User{})

if status != "" {
    query = query.Where("status = ?", status)
}
if search != "" {
    query = query.Where("name ILIKE ?", "%"+search+"%")
}

query.Order("created_at desc").Limit(limit).Find(&users)

Use scopes for reusable queries

func ActiveUsers(db *gorm.DB) *gorm.DB {
    return db.Where("status = ?", "active")
}

// Use it
db.Scopes(ActiveUsers).Find(&users)

Use First() for single records, Find() for multiple

// Single record
db.Where("id = ?", 1).First(&user)  // Errors if not found

// Multiple records (could be empty slice)
db.Where("status = ?", "active").Find(&users)  // Never errors if empty

Always use placeholders to prevent SQL injection

// ✅ Good - parameterized
db.Where("workos_id = ?", userInput).First(&user)

// ❌ Bad - SQL injection risk
db.Where(fmt.Sprintf("workos_id = '%s'", userInput)).First(&user)

9. Common Patterns

Get or Create (Upsert)

func (r *UserRepository) GetOrCreateByWorkosId(ctx context.Context, workosId string) (*User, error) {
    var user User

    err := r.db.WithContext(ctx).
        Where("workos_id = ?", workosId).
        FirstOrCreate(&user, User{WorkosId: workosId}).Error

    return &user, err
}

Use Case: Syncing users from WorkOS - create if doesn't exist.

Eager Loading (Preload)

func (r *RoomRepository) GetByIDWithOrganization(ctx context.Context, id int64) (*Room, error) {
    var room Room
    err := r.db.WithContext(ctx).
        Preload("Organization").  // Load associated org
        First(&room, id).Error

    return &room, err
}

Why? Avoids N+1 query problem.

Joins

func (r *RoomRepository) ListByWorkosOrgId(ctx context.Context, workosOrgID string) ([]*Room, error) {
    var rooms []*Room
    err := r.db.WithContext(ctx).
        Joins("JOIN organizations ON rooms.org_id = organizations.id").
        Where("organizations.workos_org_id = ?", workosOrgID).
        Find(&rooms).Error

    return rooms, err
}

9. Best Practices

✅ DO

Always use WithContext(ctx)
```
r.db.WithContext(ctx).Find(&users)
```

Check for gorm.ErrRecordNotFound

if errors.Is(err, gorm.ErrRecordNotFound) {
    return nil, fmt.Errorf("not found")
}

Use maps for partial updates

updates := map[string]interface{}{"name": "new name"}
db.Model(&user).Updates(updates)

Check RowsAffected on delete

if result.RowsAffected == 0 {
    return fmt.Errorf("not found")
}

Create indexes for foreign keys and WHERE clauses

CREATE INDEX idx_rooms_org_id ON rooms(org_id);

❌ DON'T

Don't use context.Background() in handlers

// ❌ Bad
r.db.WithContext(context.Background())

// ✅ Good
r.db.WithContext(c.Request.Context())

Don't ignore errors

// ❌ Bad
db.Create(&user)

// ✅ Good
if err := db.Create(&user).Error; err != nil {
    return err
}

Don't use structs for zero-value updates

// ❌ Bad - won't update to empty string
db.Updates(&User{Name: ""})

// ✅ Good
db.Updates(map[string]interface{}{"name": ""})

10. Quick Examples

Create a User

user, err := userRepo.Create(ctx, &CreateUserRequest{
    WorkosId: "user_123",
})

Get User's Organizations

orgs, err := userOrgRepo.GetUserOrganizations(ctx, userId)

List Rooms in an Organization

rooms, err := roomRepo.ListByWorkosOrgId(ctx, workosOrgId, limit, offset)

Delete a Session

err := sessionRepo.DeleteBySessionId(ctx, sessionId)

11. Troubleshooting

Connection Failed

# Test connection
psql $POSTGRES_URL -c "SELECT 1"

Migration Failed

-- Check migration status
SELECT version, dirty FROM schema_migrations;

Slow Query

// Enable GORM logging
db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{
    Logger: logger.Default.LogMode(logger.Info),
})

12. Testing

Tests use real PostgreSQL in Docker via testcontainers:

func setupTestDB(t *testing.T) *gorm.DB {
    // Start PostgreSQL container
    container := startPostgresContainer(t)

    // Connect
    db := connectToContainer(t, container)

    // Run migrations
    db.AutoMigrate(&User{}, &Organization{})

    return db
}

Location: internal/repository/*_test.go

13. Summary

Key Points:

PostgreSQL database on Supabase
GORM handles all database operations
Repository pattern for clean separation
Migrations run automatically on startup
Always use context, check errors, use maps for updates

File Structure:

internal/
├── database/              # Connection & migrations
│   ├── database.go
│   ├── migrate.go
│   └── migrations/*.sql
├── domain/database/       # Models (structs)
└── repository/            # Data access (CRUD operations)

That's it! Everything you need to work with the database.

1. Tech Stack​

2. Connection Setup​

3. Repository Pattern​

4. CRUD Operations​

CREATE​

READ​

UPDATE​

DELETE​

5. Domain Models​

6. Migrations​

File Naming​

Migration Template​

Running Migrations​

7. Database Schema​

Quick Overview​

8. GORM Query Guide​

Basic Querying​

Find One Record​

Find Multiple Records​

Pagination & Ordering​

Counting​

Selecting Specific Columns​

Advanced Queries​

OR Conditions​

LIKE / ILIKE (Case-insensitive)​

NOT Conditions​

Joins​

Group By & Having​

Raw SQL​

Relationships​

Preload (Eager Loading)​

Associations​

Transactions​

Checking Existence​

Batch Operations​

Common GORM Methods​

Error Handling​

Tips & Tricks​

9. Common Patterns​

Get or Create (Upsert)​

Eager Loading (Preload)​

Joins​

9. Best Practices​

✅ DO​

❌ DON'T​

10. Quick Examples​

Create a User​

Get User's Organizations​

List Rooms in an Organization​

Delete a Session​

11. Troubleshooting​

Connection Failed​

Migration Failed​

Slow Query​

12. Testing​

13. Summary​

1. Tech Stack

2. Connection Setup

3. Repository Pattern

4. CRUD Operations

CREATE

READ

UPDATE

DELETE

5. Domain Models

6. Migrations

File Naming

Migration Template

Running Migrations

7. Database Schema

Quick Overview

8. GORM Query Guide

Basic Querying

Find One Record

Find Multiple Records

Pagination & Ordering

Counting

Selecting Specific Columns

Advanced Queries

OR Conditions

LIKE / ILIKE (Case-insensitive)

NOT Conditions

Joins

Group By & Having

Raw SQL

Relationships

Preload (Eager Loading)

Associations

Transactions

Checking Existence

Batch Operations

Common GORM Methods

Error Handling

Tips & Tricks

9. Common Patterns

Get or Create (Upsert)

Eager Loading (Preload)

Joins

9. Best Practices

✅ DO

❌ DON'T

10. Quick Examples

Create a User

Get User's Organizations

List Rooms in an Organization

Delete a Session

11. Troubleshooting

Connection Failed

Migration Failed

Slow Query

12. Testing

13. Summary