Refactor: Use UUID for Primary Keys Across All Tables

[diyor28]

Description

Refactor the database schema to use UUIDs as primary keys instead of auto-incremented integers across all tables. This change enhances flexibility at the application level, improves data uniqueness across distributed systems, and allows for safer merging of records between environments.

Rationale

• Uniqueness & Global Scope: UUIDs are globally unique, preventing conflicts when syncing data across environments.
• Security: UUIDs make it harder for malicious users to guess entity counts or infer business metrics.
• Decoupling from Database Logic: UUIDs allow application-level key generation, reducing dependency on database-specific auto-increment behavior.
• Scalability & Distribution: Easier replication and sharding in distributed systems.

[twinguy]

@diyor28 Below is a proposed implementation plan. This is based on an analysis of the codebase by Cursor.ai using Claude 3.7 (Latest model). Happy to discuss approach and specifics.

UUID v7 Implementation Plan

Overview

This document outlines the plan to migrate the database primary key strategy from sequential integers (SERIAL8) to UUID v7. UUID v7 is a time-ordered UUID format that provides several advantages over sequential integers, including:

1. Distributed ID generation: No need for a central authority to generate IDs
2. Security: Harder to guess or enumerate IDs
3. Scalability: Better for distributed systems and horizontal scaling
4. Collision resistance: Virtually no chance of ID collisions

Current Implementation

The current implementation uses PostgreSQL's SERIAL8 (BIGSERIAL) data type for most tables, which creates auto-incrementing 64-bit integer primary keys. Some tables already use UUIDs, but they use the default gen_random_uuid() function which generates UUID v4 (random).

The codebase already uses the github.com/google/uuid package for UUID v4 generation in some places, particularly in the modules/core/domain/entities/group implementation.

Implementation Approach

Instead of using a PostgreSQL function to generate UUID v7 values, we will use Go code to generate the UUIDs. This approach has several advantages:

1. Consistency: The same UUID generation logic will be used across all parts of the application
2. Testability: Easier to test UUID generation in Go code
3. Flexibility: Can be used in any context, not just database operations
4. Simplicity: No need to create and maintain a database function

The github.com/google/uuid package already supports UUID v7 generation with the NewV7() function, which we will use throughout the codebase.

Implementation Steps

1. Create UUID Utility Package

Create a new utility package in pkg/uuidutil to provide consistent UUID v7 generation across the codebase:

// pkg/uuidutil/uuid.go
package uuidutil

import (
	"github.com/google/uuid"
)

// NewV7 generates a new UUID v7 (time-ordered)
func NewV7() uuid.UUID {
	id, err := uuid.NewV7()
	if err != nil {
		// Fall back to v4 if v7 generation fails for any reason
		id, _ = uuid.NewRandom()
	}
	return id
}

// NewV7String generates a new UUID v7 and returns it as a string
func NewV7String() string {
	return NewV7().String()
}

// Parse parses a UUID string into a UUID object
func Parse(s string) (uuid.UUID, error) {
	return uuid.Parse(s)
}

// MustParse parses a UUID string into a UUID object, panicking on error
func MustParse(s string) uuid.UUID {
	return uuid.MustParse(s)
}

2. Go Code Changes

2.1 Model Changes

Update all model structs in all modules to use UUID strings instead of uint for ID fields:

// modules/core/infrastructure/persistence/models/models.go
type Upload struct {
    ID        string    // Changed from uint to string (UUID)
    Hash      string
    Path      string
    Name      string
    Size      int
    Mimetype  string
    Type      string
    CreatedAt time.Time
    UpdatedAt time.Time
}

// Similar changes for all other model structs

2.2 Domain Entity Changes

Update all domain entity interfaces and implementations across all modules to use UUID strings instead of uint:

// modules/core/domain/entities/upload/upload.go
type Upload interface {
    ID() string  // Changed from uint to string (UUID)
    Type() UploadType
    Hash() string
    Path() string
    Name() string
    Size() Size
    IsImage() bool
    PreviewURL() string
    URL() *url.URL
    Mimetype() *mimetype.MIME
    CreatedAt() time.Time
    UpdatedAt() time.Time
}

// Update the implementation struct
type upload struct {
    id        string  // Changed from uint to string (UUID)
    hash      string
    path      string
    name      string
    size      Size
    _type     UploadType
    mimetype  *mimetype.MIME
    createdAt time.Time
    updatedAt time.Time
}

// Update the constructor functions
func New(
    hash, path, name string,
    size int,
    mimetype *mimetype.MIME,
    _type UploadType,
) Upload {
    return NewWithID(
        uuidutil.NewV7String(), // Generate UUID v7 here
        hash, path, name,
        size,
        mimetype,
        _type,
        time.Now(),
        time.Now(),
    )
}

func NewWithID(
    id string,  // Changed from uint to string (UUID)
    hash, path, name string,
    size int,
    mimetype *mimetype.MIME,
    _type UploadType,
    createdAt, updatedAt time.Time,
) Upload {
    // ...
}

2.3 Repository Changes

Update all repository interfaces and implementations to use UUID strings:

// modules/core/domain/entities/upload/upload_repository.go
type FindParams struct {
    ID       string  // Changed from uint to string (UUID)
    Hash     string
    Limit    int
    Offset   int
    SortBy   SortBy
    Search   string
    Type     UploadType
    Mimetype *mimetype.MIME
}

type Repository interface {
    Count(ctx context.Context) (int64, error)
    GetAll(ctx context.Context) ([]Upload, error)
    GetPaginated(ctx context.Context, params *FindParams) ([]Upload, error)
    GetByID(ctx context.Context, id string) (Upload, error)  // Changed from uint to string (UUID)
    GetByHash(ctx context.Context, hash string) (Upload, error)
    Create(ctx context.Context, data Upload) (Upload, error)
    Update(ctx context.Context, data Upload) error
    Delete(ctx context.Context, id string) error  // Changed from uint to string (UUID)
}

Update repository implementations:

// modules/core/infrastructure/persistence/upload_repository.go
func (g *GormUploadRepository) GetByID(ctx context.Context, id string) (upload.Upload, error) {
    // Update query to use UUID comparison
    query := selectUploadQuery + " WHERE uuid = $1"
    uploads, err := g.queryUploads(ctx, query, id)
    // ...
}

func (g *GormUploadRepository) Create(ctx context.Context, data upload.Upload) (upload.Upload, error) {
    pool, err := composables.UseTx(ctx)
    if err != nil {
        return nil, err
    }
    
    // No need to generate UUID here as it's already generated in the domain entity constructor
    _, err = pool.Exec(ctx, insertUploadQuery,
        data.ID(),       // Use the UUID generated in the domain entity
        data.Hash(),
        data.Path(),
        data.Name(),
        data.Size().Bytes(),
        data.Type().String(),
        data.Mimetype().String(),
        data.CreatedAt(),
        data.UpdatedAt(),
    )
    
    if err != nil {
        return nil, err
    }
    
    return data, nil
}

func (g *GormUploadRepository) Delete(ctx context.Context, id string) error {
    // Update query to use UUID
    pool, err := composables.UseTx(ctx)
    if err != nil {
        return err
    }
    _, err = pool.Exec(ctx, deleteUploadQuery, id)
    return err
}

// Similar changes for other repository methods

2.4 SQL Query Changes

Update all SQL queries to handle UUID values:

// modules/core/infrastructure/persistence/upload_repository.go
const (
    selectUploadQuery = `SELECT uuid, hash, path, name, size, type, mimetype, created_at, updated_at FROM uploads`

    countUploadsQuery = `SELECT COUNT(*) FROM uploads`

    insertUploadQuery = `INSERT INTO uploads (uuid, hash, path, name, size, type, mimetype, created_at, updated_at)
                         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)`

    updatedUploadQuery = `UPDATE uploads
                          SET hash = $1,
                              path = $2,
                              name = $3,
                              size = $4,
                              type = $5,
                              mimetype = $6,
                              updated_at = $7
                          WHERE uuid = $8`

    deleteUploadQuery = `DELETE FROM uploads WHERE uuid = $1`
)

2.5 Mapper Changes

Update all mapper functions across the codebase to handle UUID strings instead of uint:

// modules/core/infrastructure/persistence/core_mappers.go
func ToDomainUser(dbUser *models.User, dbUpload *models.Upload, roles []role.Role, groupIDs []uuid.UUID) (user.User, error) {
    var avatar upload.Upload
    if dbUpload != nil {
        avatar = ToDomainUpload(dbUpload)
    }

    email, err := internet.NewEmail(dbUser.Email)
    if err != nil {
        return nil, err
    }

    options := []user.Option{
        user.WithID(dbUser.ID), // ID is now a string
        // Other options...
    }
    // ...
}

func ToDomainUpload(dbUpload *models.Upload) upload.Upload {
    // Changed from parsing uint to using string directly
    return upload.NewWithID(
        dbUpload.ID,
        dbUpload.Hash,
        dbUpload.Path,
        dbUpload.Name,
        dbUpload.Size,
        mimetype.Detect([]byte{}),
        upload.UploadType(dbUpload.Type),
        dbUpload.CreatedAt,
        dbUpload.UpdatedAt,
    )
}

// modules/core/interfaces/graph/mappers/user_mapper.go
func UserToGraphModel(u user.User) *model.User {
    return &model.User{
        ID:         u.ID(), // Changed from int64(u.ID()) to u.ID()
        Email:      u.Email().Value(),
        FirstName:  u.FirstName(),
        LastName:   u.LastName(),
        UILanguage: string(u.UILanguage()),
        CreatedAt:  u.CreatedAt(),
        UpdatedAt:  u.UpdatedAt(),
    }
}

// modules/core/presentation/mappers/mappers.go
func UserToViewModel(entity user.User) *viewmodels.User {
    var avatar *viewmodels.Upload
    if entity.Avatar() != nil {
        avatar = UploadToViewModel(entity.Avatar())
    }
    return &viewmodels.User{
        ID:         entity.ID(), // Changed from strconv.FormatUint to direct string
        FirstName:  entity.FirstName(),
        LastName:   entity.LastName(),
        MiddleName: entity.MiddleName(),
        Email:      entity.Email().Value(),
        Avatar:     avatar,
        UILanguage: string(entity.UILanguage()),
        LastAction: entity.LastAction().Format(time.RFC3339),
        CreatedAt:  entity.CreatedAt().Format(time.RFC3339),
        UpdatedAt:  entity.UpdatedAt().Format(time.RFC3339),
        Roles:      mapping.MapViewModels(entity.Roles(), RoleToViewModel),
        AvatarID:   entity.AvatarID(), // Changed from strconv.Itoa to direct string
    }
}

func UploadToViewModel(entity upload.Upload) *viewmodels.Upload {
    return &viewmodels.Upload{
        ID:        entity.ID(), // Changed from strconv.FormatUint to direct string
        Hash:      entity.Hash(),
        URL:       entity.PreviewURL(),
        Mimetype:  entity.Mimetype().String(),
        Size:      entity.Size().String(),
        CreatedAt: entity.CreatedAt().Format(time.RFC3339),
        UpdatedAt: entity.UpdatedAt().Format(time.RFC3339),
    }
}

2.6 Templ File Changes

Update all .templ files where IDs are used to handle UUID strings:

// modules/core/presentation/templates/pages/users/edit.templ
// Example of form input changes to handle UUID

// Before
<input type="hidden" name="ID" value={ strconv.FormatUint(uint64(props.User.ID), 10) }/>

// After
<input type="hidden" name="ID" value={ props.User.ID }/>

// Before (in JavaScript)
const userId = parseInt(document.getElementById("user-id").value);

// After (in JavaScript)
const userId = document.getElementById("user-id").value;

// modules/core/presentation/templates/pages/roles/permissions.templ
// Example of link href changes

// Before
<a href={fmt.Sprintf("/users/%d", user.ID)}>

// After
<a href={fmt.Sprintf("/users/%s", user.ID)}>

3. Service Layer Changes

Update all service methods that handle entity IDs to use string instead of uint:

// Update service method signatures
func (s *uploadService) GetByID(ctx context.Context, id string) (upload.Upload, error) {
    // ...
}

func (s *uploadService) Delete(ctx context.Context, id string) error {
    // ...
}

4. API/Handler Changes

Update all API handlers and request/response DTOs to use string IDs:

// Update DTO structs
type UploadDTO struct {
    ID        string    `json:"id"`
    Hash      string    `json:"hash"`
    Path      string    `json:"path"`
    Name      string    `json:"name"`
    Size      int       `json:"size"`
    Mimetype  string    `json:"mimetype"`
    Type      string    `json:"type"`
    CreatedAt time.Time `json:"created_at"`
    UpdatedAt time.Time `json:"updated_at"`
}

// Update handler methods
func (h *uploadHandler) GetByID(w http.ResponseWriter, r *http.Request) {
    id := chi.URLParam(r, "id")
    // No need to parse as uint anymore
    upload, err := h.service.GetByID(r.Context(), id)
    // ...
}

5. GraphQL Schema Changes

Update all GraphQL schemas to use ID scalar for primary keys:

type Upload {
  id: ID!
  hash: String!
  path: String!
  name: String!
  size: Int!
  mimetype: String!
  type: String!
  createdAt: DateTime!
  updatedAt: DateTime!
}

Modules to Modify

Based on the codebase analysis, the following modules contain entities that need to be updated:

Core Module

• modules/core/domain/entities/upload
• modules/core/domain/entities/session
• modules/core/domain/entities/tab
• modules/core/domain/entities/telegramsession
• modules/core/domain/entities/currency
• modules/core/domain/entities/passport
• modules/core/domain/entities/permission
• modules/core/domain/entities/authlog
• modules/core/domain/entities/costcomponent

Warehouse Module

• modules/warehouse/domain/entities/inventory
• modules/warehouse/domain/entities/unit

CRM Module

• modules/crm/domain/entities/message-template

Finance Module

• modules/finance/domain/entities/counterparty
• modules/finance/domain/entities/transaction

HRM Module

• modules/hrm/domain/entities/position

BiChat Module

• modules/bichat/domain/entities/prompt
• modules/bichat/domain/entities/dialogue
• modules/bichat/domain/entities/embedding
• modules/bichat/domain/entities/llm

Files to Modify

New Files to Create

• pkg/uuidutil/uuid.go - Utility package for UUID v7 generation

Domain Entity Files

• All files in modules/*/domain/entities/*/ directories that define entity interfaces and implementations

Repository Files

• All repository interface files: modules/*/domain/entities/*/repository.go
• All repository implementation files: modules/*/infrastructure/persistence/*_repository.go

Service Files

• All service implementation files that handle entity IDs

API/Handler Files

• All handler files that process entity IDs from requests
• All DTO files that include entity IDs

GraphQL Files

• All GraphQL schema files that define types with ID fields

Mapper Files

• Core mappers: modules/core/infrastructure/persistence/core_mappers.go
• Domain-to-Graph mappers: modules/*/interfaces/graph/mappers/*.go
• Domain-to-View mappers: modules/*/presentation/mappers/*.go
• All other mappers in the different modules:
  • modules/warehouse/infrastructure/persistence/mappers/*.go
  • modules/finance/infrastructure/persistence/mappers/*.go
  • modules/hrm/infrastructure/persistence/mappers/*.go
  • modules/crm/infrastructure/persistence/mappers/*.go
  • modules/bichat/infrastructure/persistence/mappers/*.go

Template Files

• All .templ files that use entity IDs in forms, links, or JavaScript code:
  • modules/*/presentation/templates/pages/**/*.templ
  • components/**/*.templ

Implementation Considerations

Performance

• UUID v7 is designed to be time-ordered, which helps maintain index performance similar to sequential IDs
• Consider adding appropriate indexes on UUID columns for foreign key relationships

Compatibility

• Ensure all client applications can handle UUID strings instead of numeric IDs
• Update any client-side validation to accept UUID format
• Update JavaScript code in .templ files to handle UUID strings instead of numeric IDs

Rollout Strategy

1. Implement the pkg/uuidutil package first
2. Update domain entities to use UUID v7 generation
3. Implement changes in a development environment
4. Create a comprehensive test suite to verify functionality

Testing

Create comprehensive tests to verify:

1. UUID v7 generation works correctly
2. Database operations (CRUD) work with UUID primary keys
3. Foreign key relationships work correctly
4. API endpoints correctly handle UUID values
5. Template rendering works correctly with UUID strings
6. Client-side JavaScript handles UUID strings correctly

Conclusion

Migrating to UUID v7 primary keys will improve the system's scalability and security. The time-ordered nature of UUID v7 provides performance benefits over random UUIDs while maintaining the advantages of distributed ID generation.

By using Go code for UUID generation instead of a database function, we ensure consistency across the application and simplify the implementation. The migration will require changes to domain models, repositories, services, and API layers, but the benefits of using UUID v7 will outweigh the implementation effort in the long term.

[diyor28]

@twinguy LGTM, except for domain entities I'd rather use uuid.UUID instead of string. Also don't see the need for UUID v7. It takes up more space while not providing any real benefits for us