query-orchestration/internal/database/README.md

# Package database

## Overview

Package database provides the data access layer for the query orchestration platform. It manages PostgreSQL database connections, schema migrations, and generated query functions using SQLC. The package implements a repository pattern with type-safe database operations and comprehensive transaction support.

## Architecture

### Service Layer Architecture

The database access follows a clean, layered architecture pattern:

1. **Repository Layer** (`repository/`)
   - SQLC-generated code providing type-safe database queries
   - `Queries` struct contains all database operations
   - Generated from SQL files in `queries/` directory
   - Accessed throughout codebase via `ConfigProvider.GetDBQueries()`

2. **Service Layer** (`internal/*/service.go`)
   - Each domain has its own service containing business logic
   - Services use the repository layer for all database access
   - Examples: `client/service.go`, `document/service.go`, `query/service.go`

3. **Configuration Layer** (`internal/serviceconfig/database/`)
   - Manages database connections and connection pooling
   - Provides transaction support via `ExecuteDBTransaction()`
   - Handles migration execution on startup

4. **Database Access Pattern**
   ```
   API Controllers → Service Layer (business logic)
         ↓
   ConfigProvider.GetDBQueries() 
         ↓
   Repository Layer (SQLC-generated)
         ↓
   PostgreSQL Database
   ```

### Architecture Principles

- **No Direct SQL in Business Logic**: All SQL queries are defined in `.sql` files
- **Type Safety**: SQLC generates Go types from SQL schemas
- **Clean Separation**: HTTP handlers → Services → Repository → Database
- **Transaction Support**: Coordinated through ConfigProvider interface
- **Code Generation**: Repository code is generated, not hand-written
- **Single Source of Truth**: Database schema defined in migrations

### Core Components

#### Database Schema
The database schema supports document processing and query orchestration with the following key entities:
- **Clients**: Customer configurations with associated document sets
- **Documents**: Uploaded files with processing state tracking
- **Queries**: Data extraction and processing definitions with versioning
- **Collectors**: Query collection specifications per client
- **Results**: Processed query outputs linked to documents
- **Text Extractions**: OCR results from document processing
- **Batch Uploads**: Batch document upload tracking with progress monitoring

#### Migration System
- Uses `golang-migrate` for schema version management
- Migrations stored in `migrations/` directory with up/down SQL files
- Automatic migration execution on startup
- Support for extensions, tables, views, and functions

#### Query Generation
- SQLC generates type-safe Go code from SQL queries
- Query definitions in `queries/` directory
- Generated code in `repository/` subdirectory
- Automatic struct mapping and null handling

### Key Files

- **migrations.go**: Database migration management and execution
- **queries/*.sql**: SQLC query definitions for each entity
- **repository/db.go**: Database connection interface and transaction support
- **repository/*.sql.go**: Generated query functions from SQLC
- **repository/models.go**: Generated data models and type definitions
- **database.md**: Schema documentation with Mermaid diagram
- **diagram.mmd**: Visual database schema representation

## Database Schema Design

### Tables

#### Core Tables
- **clients**: Customer configurations and metadata
- **documents**: Document upload tracking and processing status
- **documentCleans**: Cleaned document content storage
- **documentTextExtractions**: OCR text extraction results
- **queries**: Query definitions with JSON configurations
- **queryVersions**: Query version history and dependency tracking
- **results**: Query execution results per document
- **collectors**: Client-specific query collections
- **collectorVersions**: Collector version history
- **batch_uploads**: Batch document upload tracking with progress and failure metrics

### Views

#### Query Views
- **fullActiveQueries**: Complete active query information with dependencies
- **queryActiveVersions**: Current active version for each query
- **queryActiveDependencies**: Recursive dependency resolution
- **activeQueryNameTypeMap**: Mapping of query names to types

#### Collector Views
- **fullActiveCollectorVersions**: Active collector configurations
- **collectorActiveVersions**: Current version for each collector
- **collectorQueryDependencyTree**: Full dependency graph for collectors

#### Document Views
- **currentTextEntries**: Latest text extraction for each document
- **latestDocumentTextResults**: Most recent results per document

### Custom Functions
- **uuid_generate_v7()**: Generates time-ordered UUIDs for better indexing
- **json_matches_schema()**: Validates JSON against schema definitions

## Usage

### Database Connection

```go
// Create database configuration
dbConfig := &serviceconfig.DBConfig{
    DBHost:     "localhost",
    DBPort:     5432,
    DBName:     "querydb",
    DBUser:     "dbuser",
    DBPassword: "dbpass",
}

// Initialize connection pool
err := dbConfig.SetDBPoolConfig()
if err != nil {
    return err
}

// Get repository queries instance
queries := dbConfig.GetDBQueries()
```

### Transaction Management

```go
// Execute operations in a transaction
err := dbConfig.ExecuteDBTransaction(ctx, func(ctx context.Context, qtx *repository.Queries) error {
    // All operations here run in a single transaction
    client, err := qtx.CreateClient(ctx, repository.CreateClientParams{
        ID:   clientID,
        Name: clientName,
    })
    if err != nil {
        return err // Transaction will rollback
    }
    
    // Additional operations...
    return nil // Transaction will commit
})
```

### Query Examples

```go
// Get a document by ID
document, err := queries.GetDocument(ctx, documentID)

// List documents for a client
documents, err := queries.ListDocumentsByClient(ctx, repository.ListDocumentsByClientParams{
    ClientID: clientID,
    Limit:    50,
    Offset:   0,
})

// Create a query result
result, err := queries.CreateResult(ctx, repository.CreateResultParams{
    TextEntryID: textEntryID,
    QueryID:     queryID,
    Result:      resultJSON,
})
```

## Migration Management

### Creating Migrations

```bash
# Create a new migration
migrate create -ext sql -dir internal/database/migrations -seq add_new_feature

# This creates:
# - 000000XX_add_new_feature.up.sql
# - 000000XX_add_new_feature.down.sql
```

### Migration Conventions
- Use sequential numbering with zero padding
- Provide complete rollback logic in down migrations
- Test both up and down migrations
- Include data migrations where necessary

### Migration Execution
Migrations run automatically on application startup via:
```go
migrator := database.NewMigrator(dbURL)
err := migrator.Up()
```

## SQLC Integration

### Configuration
SQLC is configured via `sqlc.yml` in the project root with:
- PostgreSQL engine with pgx/v5 driver
- Queries sourced from `internal/database/queries/`
- Schema from `internal/database/migrations/`
- Generated code output to `internal/database/repository/`
- Custom rules enforcing query performance and safety
- UUID type mapping to `github.com/google/uuid`

### Query Definition Format

```sql
-- name: GetDocument :one
SELECT * FROM documents
WHERE id = $1;

-- name: ListDocumentsByClient :many
SELECT * FROM documents
WHERE clientId = $1
ORDER BY created DESC
LIMIT $2 OFFSET $3;

-- name: UpdateDocumentStatus :exec
UPDATE documents
SET status = $2, updated = CURRENT_TIMESTAMP
WHERE id = $1;
```

### Generated Code Features
SQLC generates:
- Type-safe query functions with parameter structs
- Struct definitions matching table schemas
- Null handling with pointers for nullable fields
- UUID type support via google/uuid package
- SQL comments included in generated code
- Enum validation methods
- Empty slice initialization for array returns

## Testing

### Test Database Setup
Tests use testcontainers to spin up PostgreSQL instances:
```go
container := test.NewDatabaseContainer(t)
dbConfig := container.DBConfig(t)
```

### Repository Testing
- Each repository file has corresponding test coverage
- Tests validate CRUD operations
- Transaction rollback testing
- Concurrent access validation

## Performance Considerations

### Connection Pooling
- Uses pgx connection pooling
- Configurable pool size and timeouts
- Health check pings with timeout
- Automatic connection recovery

### Query Optimization
- Views provide pre-computed joins
- Recursive CTEs for dependency resolution
- Proper use of LIMIT/OFFSET for pagination
- JSON indexing for configuration queries

## Data Types

### Custom Types
- **UUID**: Used for all primary keys
- **JSONB**: Query configurations and results
- **Timestamptz**: All timestamp fields timezone-aware
- **Text**: Unbounded string storage
- **Custom Enums**: Via CHECK constraints

### Null Handling
- Explicit NULL/NOT NULL constraints
- Go sql.Null* types for nullable fields
- Default values where appropriate

## Security

### Access Control
- Database credentials via environment variables
- Connection string sanitization
- Parameterized queries prevent SQL injection
- No dynamic SQL construction

### Data Protection
- Sensitive data fields identified
- Audit fields (created, updated) on all tables
- Logical deletion support where needed