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

1. 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)
   

2. Use scopes for reusable queries

   func ActiveUsers(db *gorm.DB) *gorm.DB {
       return db.Where("status = ?", "active")
   }
   
   // Use it
   db.Scopes(ActiveUsers).Find(&users)
   

3. 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
   

4. 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

1. Always use WithContext(ctx)

   r.db.WithContext(ctx).Find(&users)
   

2. Check for gorm.ErrRecordNotFound

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

3. Use maps for partial updates

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

4. Check RowsAffected on delete

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

5. Create indexes for foreign keys and WHERE clauses

   CREATE INDEX idx_rooms_org_id ON rooms(org_id);
   

---

❌ DON'T

1. Don't use context.Background() in handlers

   // ❌ Bad
   r.db.WithContext(context.Background())
   
   // ✅ Good
   r.db.WithContext(c.Request.Context())
   

2. Don't ignore errors

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

3. 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.