DAO Layer — Data Persistence

Overview

DAO (Data Access Object) is the lowest application layer. It encapsulates all PostgreSQL interactions via GORM ORM. Every model is a Go struct that maps to a database table. Queries are methods on the DAO struct that return domain types.

Key principle: Services and handlers never execute raw SQL; they call DAO methods.

Why GORM?

Aspect	Benefit
Type Safety	Struct = schema. Compiler catches column name typos. Go types = no casting.
Auto-Migration	Define schema once in code. Run once per version. DB state always in sync.
Hooks	BeforeSave, AfterFind callbacks enable auto-timestamping, encryption, validation.
Associations	HasMany, BelongsTo, ManyToMany reduce boilerplate.
Transactions	BeginTx, Rollback, Commit with automatic panic recovery.
Query Building	Where, Select, Limit, Order chainable. No string concatenation.

Core Models

Knowledgebase

type Knowledgebase struct {
    ID            string    `gorm:"primaryKey" json:"id"`
    TenantID      string    `gorm:"index" json:"tenant_id"`
    Name          string    `json:"name"`
    Description   string    `json:"description"`
    DocumentCount int       `json:"document_count"`
    IsActive      bool      `json:"is_active"`
    CreatedAt     time.Time `json:"created_at"`
    UpdatedAt     time.Time `json:"updated_at"`
    Documents     []Document  `gorm:"foreignKey:KnowledgebaseID"`
}

Document

type Document struct {
    ID              string    `gorm:"primaryKey" json:"id"`
    KnowledgebaseID string    `gorm:"index" json:"kb_id"`
    TenantID        string    `gorm:"index" json:"tenant_id"`
    Name            string    `json:"name"`
    FilePath        string    `json:"file_path"`
    FileSize        int64     `json:"file_size"`
    Pages           int       `json:"pages"`
    Status          string    `json:"status"`
    CreatedAt       time.Time `json:"created_at"`
    UpdatedAt       time.Time `json:"updated_at"`
    Chunks          []Chunk   `gorm:"foreignKey:DocumentID"`
}

Chunk

type Chunk struct {
    ID           string      `gorm:"primaryKey" json:"id"`
    DocumentID   string      `gorm:"index" json:"doc_id"`
    TenantID     string      `gorm:"index" json:"tenant_id"`
    Content      string      `json:"content"`
    TokenCount   int         `json:"token_count"`
    Embedding    pgvector.Vector  `gorm:"type:vector(1536)" json:"embedding"`
    PageNum      int         `json:"page_num"`
    Score        float32     `json:"score"`
    CreatedAt    time.Time   `json:"created_at"`
}

Chat (Conversation)

type Chat struct {
    ID                string    `gorm:"primaryKey" json:"id"`
    KnowledgebaseID   string    `gorm:"index" json:"kb_id"`
    TenantID          string    `gorm:"index" json:"tenant_id"`
    UserID            string    `gorm:"index" json:"user_id"`
    Title             string    `json:"title"`
    Messages          []Message `gorm:"foreignKey:ChatID;constraint:OnDelete:CASCADE"`
    TokensUsed        int       `json:"tokens_used"`
    CreatedAt         time.Time `json:"created_at"`
    UpdatedAt         time.Time `json:"updated_at"`
}

Message

type Message struct {
    ID        string    `gorm:"primaryKey" json:"id"`
    ChatID    string    `gorm:"index" json:"chat_id"`
    Role      string    `json:"role"`
    Content   string    `json:"content"`
    Tokens    int       `json:"tokens"`
    CreatedAt time.Time `json:"created_at"`
}

DAO Query Methods

Knowledgebase Queries

func (d *DAO) GetKnowledgebase(ctx context.Context, id string) (*Knowledgebase, error) {
    var kb Knowledgebase
    err := d.db.WithContext(ctx).First(&kb, "id = ?", id).Error
    return &kb, err
}

func (d *DAO) ListKnowledgebases(ctx context.Context, tenantID string) ([]Knowledgebase, error) {
    var kbs []Knowledgebase
    err := d.db.WithContext(ctx).Where("tenant_id = ?", tenantID).Find(&kbs).Error
    return kbs, err
}

func (d *DAO) CreateKnowledgebase(ctx context.Context, kb *Knowledgebase) error {
    return d.db.WithContext(ctx).Create(kb).Error
}

Document Queries

func (d *DAO) GetDocument(ctx context.Context, id string) (*Document, error) {
    var doc Document
    err := d.db.WithContext(ctx).Preload("Chunks").First(&doc, "id = ?", id).Error
    return &doc, err
}

func (d *DAO) ListDocuments(ctx context.Context, kbID string) ([]Document, error) {
    var docs []Document
    err := d.db.WithContext(ctx).Where("knowledgebase_id = ?", kbID).Find(&docs).Error
    return docs, err
}

Chunk Queries (Vector Search)

func (d *DAO) SearchChunks(ctx context.Context, embedding pgvector.Vector, topK int) ([]Chunk, error) {
    var chunks []Chunk
    err := d.db.WithContext(ctx).
        Select("*, embedding <-> ? as distance", embedding).
        Order("distance ASC").
        Limit(topK).
        Find(&chunks).Error
    return chunks, err
}

Chat & Message Queries

func (d *DAO) GetChat(ctx context.Context, chatID string) (*Chat, error) {
    var chat Chat
    err := d.db.WithContext(ctx).Preload("Messages", func(db *gorm.DB) *gorm.DB {
        return db.Order("created_at ASC")
    }).First(&chat, "id = ?", chatID).Error
    return &chat, err
}

func (d *DAO) AppendMessage(ctx context.Context, message *Message) error {
    return d.db.WithContext(ctx).Create(message).Error
}

Indexes for Performance

Table	Index	Reason
chunks	tenant_id, kb_id, document_id	Fast filtering by tenant → KB → doc
chunks	pgvector (embedding)	Similarity search via <-> operator
documents	tenant_id, kb_id	List docs within KB
chat	tenant_id, user_id, kb_id	List conversations per user
messages	chat_id, created_at	Retrieve chat history in order

GORM Hooks

Models can implement hooks for automatic behavior:

func (d *Document) BeforeSave(tx *gorm.DB) error {
    if d.ID == "" {
        d.ID = uuid.New().String()
    }
    if d.Status == "" {
        d.Status = "pending"
    }
    return nil
}

func (c *Chunk) AfterFind(tx *gorm.DB) error {
    c.Score = 0  // default score before ranking
    return nil
}

Transactions & Constraints

Foreign Keys

Models declare relationships to enforce referential integrity:

type Document struct {
    ID              string    `gorm:"primaryKey"`
    KnowledgebaseID string    `gorm:"constraint:OnDelete:CASCADE"`
}

Migrations

AutoMigrate creates schema on startup:

func (d *DAO) AutoMigrate() error {
    return d.db.AutoMigrate(
        &Knowledgebase{},
        &Document{},
        &Chunk{},
        &Chat{},
        &Message{},
        &User{},
    )
}

Common DAO Methods

CRUD Operations

Create, Read (Get), Update, Delete. Standard methods for every model.

List & Filter

Query with where conditions, pagination, sorting.

Vector Search

Similarity search via pgvector <-> operator. Top-K results.

Transactions

Multi-step operations with rollback on error.

Batch Operations

Insert many rows efficiently. Bulk delete by criteria.

Aggregations

Count, sum, group-by, distinct. Metrics and statistics.

Design Principles

No Business Logic in DAO

DAO is dumb. It does only what SQL can do: fetch, filter, sort. Business logic (like "deduplicate results") belongs in services.

Context Propagation

Every query method accepts context.Context for cancellation and timeouts:

func (d *DAO) GetDocument(ctx context.Context, id string) (*Document, error) {
    var doc Document
    err := d.db.WithContext(ctx).First(&doc, "id = ?", id).Error
    return &doc, err
}

Preloading Associations

Use Preload to avoid N+1 queries:

// Bad: N+1 queries
chats, _ := d.ListChats(ctx, userID)
for _, chat := range chats {
    messages, _ := d.ListMessages(ctx, chat.ID)  // 1 query per chat
}

// Good: 2 queries total
var chats []Chat
d.db.WithContext(ctx).Preload("Messages").Find(&chats, "user_id = ?", userID)

Query Optimization Techniques

N+1 Query Problem

Problem: Fetching a list of documents, then fetching chunks for each document, results in 1 + N queries.

// WRONG: N+1 queries
docs := d.ListDocuments(ctx, kbID)
for _, doc := range docs {
    chunks := d.ListChunks(ctx, doc.ID)  // one query per doc
    // ...
}

// CORRECT: 2 queries total
var docs []Document
d.db.WithContext(ctx).
    Preload("Chunks").  // JOIN in SQL
    Find(&docs, "kb_id = ?", kbID)

Query Projection

Fetch only needed columns to reduce network bandwidth:

var results []struct {
    ID      string
    Content string
    Score   float32
}
d.db.Select("id", "content", "score").Find(&results, "kb_id = ?", kbID)

Batch Operations

Insert multiple rows in a single query:

chunks := []Chunk{ /* ... */ }
d.db.CreateInBatches(chunks, 100)  // insert 100 at a time

Advanced GORM Features

Soft Delete

Mark rows as deleted without removing them (enables recovery, audit trail):

type Document struct {
    ID        string
    Name      string
    DeletedAt gorm.DeletedAt  // NULL = not deleted
}

d.db.Where("id = ?", docID).Delete(&Document{})
// Sets DeletedAt = now()

// Auto-filter soft-deleted rows in queries
d.db.Find(&docs)  // only non-deleted rows
d.db.Unscoped().Find(&docs)  // include deleted

Sharding Preparation

Design models for future sharding by tenant or KB:

type Chunk struct {
    TenantID      string  // shard key
    KnowledgebaseID string  // partition key
    ID            string  // composite primary key
}

// Queries always include shard key
d.db.Where("tenant_id = ? AND kb_id = ?", tenantID, kbID).Find(&chunks)

Callback Hooks

Automatically update fields on create/update:

func (d *Document) BeforeSave(tx *gorm.DB) error {
    d.UpdatedAt = time.Now()
    return nil
}

func (c *Chunk) AfterCreate(tx *gorm.DB) error {
    return tx.Model(&c).Association("Document").Load()  // eager load
}

DAO Layer: Data Access Objects

📚 What is this & why does it exist?

Overview

Why GORM?

Core Models

Knowledgebase

Document

Chunk

Chat (Conversation)

Message

DAO Query Methods

Knowledgebase Queries

Document Queries

Chunk Queries (Vector Search)

Chat & Message Queries

Indexes for Performance

GORM Hooks

Transactions & Constraints

Foreign Keys

Migrations

Common DAO Methods

CRUD Operations

List & Filter

Vector Search

Transactions

Batch Operations

Aggregations

Design Principles

No Business Logic in DAO

Context Propagation

Preloading Associations

Query Optimization Techniques

N+1 Query Problem

Query Projection

Batch Operations

Advanced GORM Features

Soft Delete

Sharding Preparation

Callback Hooks

Further Reading