Refactor: Migrate project preview to polymorphic file table

[coderabbitai]

Context

This issue tracks the refactoring work to migrate from a string-based preview column in the project table to using a polymorphic file table pattern.

Related PR: #766
Requested by: @fingertips18

Current State

• ✅ File table created with polymorphic pattern (parent_table, parent_id, role)
• ❌ Project table still has preview string column
• ❌ Backend and frontend code still expect preview as a string field
• Migration to drop preview column exists but should be deferred until this refactoring is complete

Goal

Implement a flexible polymorphic file management system where:

• Projects can have multiple files with different roles (preview, attachments, etc.)
• File metadata is centralized in the file table
• Easy to extend to other entities (users, etc.)

---

Implementation Steps

1. Backend Changes (Go)

1.1 Create File Domain Model

// apps/backend/internal/domain/file.go
type File struct {
    ID          uuid.UUID
    ParentTable string
    ParentID    uuid.UUID
    Role        string
    Key         string
    URL         string
    Type        string
    Size        int64
    CreatedAt   time.Time
    UpdatedAt   time.Time
}

1.2 Create File Repository

• FindByParent(parentTable, parentID, role) - Get files for a specific parent and role
• Create(file) - Create new file record
• Delete(id) - Delete file record
• FindByID(id) - Get file by ID

1.3 Update Project Model

Add computed field or association:

type Project struct {
    // ... existing fields
    PreviewFile *File `json:"preview_file,omitempty"`
}

1.4 Update Project Repository

Modify GetByID and List to JOIN with file table:

SELECT p.*, f.* FROM project p
LEFT JOIN file f ON f.parent_table = 'project' 
  AND f.parent_id = p.id 
  AND f.role = 'preview'

1.5 Update API Handlers

• Modify GET /projects responses to include preview_file
• Add file upload endpoints if not already present
• Handle file creation when project is created/updated

2. Frontend Changes (React/TypeScript)

2.1 Update TypeScript Types

// apps/admin/src/types/file.ts
export interface File {
  id: string;
  parentTable: string;
  parentId: string;
  role: string;
  key: string;
  url: string;
  type: string;
  size: number;
  createdAt: string;
  updatedAt: string;
}

// apps/admin/src/types/project.ts
export interface Project {
  // ... existing fields
  preview?: string; // Keep for backward compatibility initially
  previewFile?: File; // New field
}

2.2 Update Project Forms

• apps/admin/src/pages/project/add/_components/form.tsx
• apps/admin/src/pages/project/edit/_components/form.tsx
• Update file upload handlers to create file records with parent_table='project' and role='preview'

2.3 Update Display Components

• apps/admin/src/pages/project/view/index.tsx
• apps/frontend/src/pages/project/card.tsx
• Use previewFile?.url instead of preview where applicable

2.4 Add Fallback Logic

Handle transition period where some projects have string preview, others have previewFile:

const previewUrl = project.previewFile?.url ?? project.preview;

3. Data Migration

3.1 Create Migration Script

-- Migrate existing preview URLs to file table
INSERT INTO file (id, parent_table, parent_id, role, key, url, type, size, created_at, updated_at)
SELECT 
  gen_random_uuid(),
  'project',
  id,
  'preview',
  SUBSTRING(preview FROM '[^/]+$'), -- Extract filename from URL
  preview,
  'image/jpeg', -- Default type, adjust as needed
  0, -- Size unknown, can be updated later
  NOW(),
  NOW()
FROM project
WHERE preview IS NOT NULL AND preview != '';

3.2 Drop Preview Column

Once migration is complete and tested:

-- apps/backend/internal/database/migrations/YYYYMMDDHHMMSS_drop_project_preview.up.sql
ALTER TABLE project DROP COLUMN preview;

4. Testing Checklist

• Backend: Test file repository methods
• Backend: Test project API returns preview_file
• Frontend: Test creating new project with file upload
• Frontend: Test editing project with file replacement
• Frontend: Test viewing project with preview
• Data Migration: Run migration script on staging
• Data Migration: Verify all previews migrated correctly
• E2E: Test full flow from upload to display
• Rollback: Test down migration works

5. Deployment Steps

1. Merge backend changes (API backward compatible)
2. Run data migration script
3. Deploy frontend changes
4. Monitor for issues
5. After stable, run migration to drop preview column
6. Clean up fallback code in frontend

---

Notes

• Keep the migration that drops the preview column but don't apply it until this refactoring is complete
• Consider adding file deletion cleanup (delete from storage when file record is deleted)
• Future: Extend to support multiple files per project (galleries, attachments)
• Future: Add file validation (size limits, type restrictions)

Definition of Done

• File table is being used for project previews
• Old preview column is dropped
• All existing projects have their previews migrated
• Frontend displays previews correctly
• Tests are passing
• Documentation updated

[fingertips18]

@coderabbitai provide the actual code for these:

1.2 Create File Repository
FindByParent(parentTable, parentID, role) - Get files for a specific parent and role
Create(file) - Create new file record
Delete(id) - Delete file record
FindByID(id) - Get file by ID

[coderabbitai]

🧩 Analysis chain

@fingertips18, I'll help you create the File Repository code. First, let me explore the codebase to understand the technology stack and existing patterns.

---

🏁 Script executed:

#!/bin/bash

# Check the repository structure and identify the main programming language
echo "=== Repository Structure ==="
ls -la

echo -e "\n=== Package files to identify tech stack ==="
fd -t f 'package.json|go.mod|requirements.txt|Gemfile|pom.xml|build.gradle|Cargo.toml' --max-depth 2

echo -e "\n=== Look for existing repository files ==="
fd -e go -e ts -e js -e py -e java 'repository|repo' --type f

echo -e "\n=== Look for file model/schema ==="
fd -e go -e ts -e js -e py -e java 'file' --type f | head -20

echo -e "\n=== Check for database/ORM usage ==="
rg -n "gorm|mongoose|sequelize|prisma|sqlalchemy|hibernate" --type-list | head -5
rg -l "gorm|mongoose|sequelize|prisma|sqlalchemy|hibernate" | head -10

Length of output: 1598

---

🏁 Script executed:

#!/bin/bash

# Check Go module and dependencies
echo "=== Go Module ==="
cat apps/backend/go.mod | head -30

echo -e "\n=== Check for GORM specifically ==="
rg -l "gorm" apps/backend/ | head -10

echo -e "\n=== Look at existing repository implementations ==="
fd 'repository.go' apps/backend/internal/repository/

echo -e "\n=== Check one example repository for pattern ==="
fd -e go . apps/backend/internal/repository/v1/ --type f | grep -v mock | head -5 | xargs ls -la

echo -e "\n=== Look for models directory ==="
fd -t d 'model' apps/backend/

echo -e "\n=== Look for file-related models ==="
fd -e go 'file' apps/backend/internal/

Length of output: 2243

---

🏁 Script executed:

#!/bin/bash

# Look at an example repository to understand the pattern
echo "=== Analytics Repository Implementation ==="
cat apps/backend/internal/repository/v1/analytics.go

echo -e "\n=== Education Repository Implementation ==="
cat apps/backend/internal/repository/v1/education.go | head -100

echo -e "\n=== Look for domain models ==="
fd -e go . apps/backend/internal/domain/ --type f

echo -e "\n=== Check if there's a file model in domain ==="
fd 'file' apps/backend/internal/domain/

Length of output: 5877

---

🏁 Script executed:

#!/bin/bash

# Check for database API interface
echo "=== Database API Interface ==="
cat apps/backend/internal/database/database.go 2>/dev/null || echo "Not found at expected location"

# Look for database package
echo -e "\n=== Find database package ==="
fd 'database' apps/backend/internal/ --type d

# Look inside database directory
echo -e "\n=== Database files ==="
fd -e go . apps/backend/internal/database/ --type f

# Check one more repository for complete pattern
echo -e "\n=== Image Repository (likely similar to file) ==="
cat apps/backend/internal/repository/v1/image.go

# Check domain models to understand structure
echo -e "\n=== Image Domain Model ==="
cat apps/backend/internal/domain/image.go

Length of output: 9945

---

🏁 Script executed:

#!/bin/bash

# Check for utils package with GenerateKey function
echo "=== Utils package ==="
fd 'utils' apps/backend/internal/ --type d

# Look at utils to understand ID generation
echo -e "\n=== Utils functions ==="
cat apps/backend/internal/utils/key.go 2>/dev/null || cat apps/backend/internal/utils/utils.go 2>/dev/null || echo "Utils file not found in expected location"

# Look for any existing files table or file-related queries
echo -e "\n=== Search for files table references ==="
rg -n "files" apps/backend/ --type go -C 2 | head -50

# Check the issue to understand the context better
echo -e "\n=== Check repository structure ==="
ls -la apps/backend/internal/repository/v1/ | grep -v mock

Length of output: 4120

---

🏁 Script executed:

#!/bin/bash

# List utils files
echo "=== Utils files ==="
ls -la apps/backend/internal/utils/

# Check all utils files
echo -e "\n=== Key generation function ==="
cat apps/backend/internal/utils/*.go | grep -A 10 "GenerateKey"

# Look at the full project repository to understand the pattern better
echo -e "\n=== Project Repository (comprehensive example) ==="
cat apps/backend/internal/repository/v1/project.go

Length of output: 17205

---

@fingertips18, I'll provide you with the complete File Repository implementation based on the patterns in your codebase. This includes both the domain model and the repository.

1. File Domain Model

First, create the file domain model at apps/backend/internal/domain/file.go:

package domain

import (
	"errors"
	"fmt"
	"time"
)

type FileRole string

const (
	FileRoleImage    FileRole = "image"
	FileRoleDocument FileRole = "document"
	FileRoleVideo    FileRole = "video"
	FileRoleOther    FileRole = "other"
)

type File struct {
	ID          string    `json:"id"`
	ParentTable string    `json:"parentTable"`
	ParentID    string    `json:"parentId"`
	Role        FileRole  `json:"role"`
	FileName    string    `json:"fileName"`
	FileURL     string    `json:"fileUrl"`
	FileType    string    `json:"fileType"`
	FileSize    int64     `json:"fileSize"`
	CreatedAt   time.Time `json:"createdAt"`
	UpdatedAt   time.Time `json:"updatedAt"`
}

func (f File) ValidatePayload() error {
	if f.ParentTable == "" {
		return errors.New("parentTable missing")
	}
	if f.ParentID == "" {
		return errors.New("parentId missing")
	}
	if f.Role == "" {
		return errors.New("role missing")
	}
	if f.Role != FileRoleImage && f.Role != FileRoleDocument && f.Role != FileRoleVideo && f.Role != FileRoleOther {
		return fmt.Errorf("invalid role: must be one of %s, %s, %s, %s", FileRoleImage, FileRoleDocument, FileRoleVideo, FileRoleOther)
	}
	if f.FileName == "" {
		return errors.New("fileName missing")
	}
	if f.FileURL == "" {
		return errors.New("fileUrl missing")
	}
	if f.FileType == "" {
		return errors.New("fileType missing")
	}
	if f.FileSize <= 0 {
		return errors.New("fileSize must be greater than 0")
	}
	return nil
}

func (f File) ValidateResponse() error {
	if f.ID == "" {
		return errors.New("id missing")
	}
	if err := f.ValidatePayload(); err != nil {
		return err
	}
	if f.CreatedAt.IsZero() {
		return errors.New("createdAt missing")
	}
	if f.UpdatedAt.IsZero() {
		return errors.New("updatedAt missing")
	}
	return nil
}

2. File Repository

Create the repository at apps/backend/internal/repository/v1/file.go:

package v1

import (
	"context"
	"errors"
	"fmt"
	"time"

	"github.com/fingertips18/fingertips18.github.io/backend/internal/database"
	"github.com/fingertips18/fingertips18.github.io/backend/internal/domain"
	"github.com/fingertips18/fingertips18.github.io/backend/internal/utils"
	"github.com/jackc/pgx/v5"
)

type FileRepository interface {
	FindByParent(ctx context.Context, parentTable, parentID string, role domain.FileRole) ([]domain.File, error)
	Create(ctx context.Context, file *domain.File) (string, error)
	Delete(ctx context.Context, id string) error
	FindByID(ctx context.Context, id string) (*domain.File, error)
}

type FileRepositoryConfig struct {
	DatabaseAPI database.DatabaseAPI
	FileTable   string

	timeProvider domain.TimeProvider
}

type fileRepository struct {
	fileTable    string
	databaseAPI  database.DatabaseAPI
	timeProvider domain.TimeProvider
}

// NewFileRepository creates and returns a configured FileRepository.
//
// It accepts a FileRepositoryConfig and constructs an internal
// fileRepository backed by cfg.FileTable and cfg.DatabaseAPI.
// If cfg.timeProvider is nil, the repository defaults to using time.Now
// as the time provider. The returned value implements the
// FileRepository interface and is never nil.
func NewFileRepository(cfg FileRepositoryConfig) FileRepository {
	timeProvider := cfg.timeProvider
	if timeProvider == nil {
		timeProvider = time.Now
	}

	return &fileRepository{
		fileTable:    cfg.FileTable,
		databaseAPI:  cfg.DatabaseAPI,
		timeProvider: timeProvider,
	}
}

// FindByParent retrieves all files for a specific parent entity and role.
// It queries the database for files matching the provided parentTable, parentID, and role.
// The results are ordered by created_at in descending order.
//
// Parameters:
//   - ctx: The context for controlling cancellation and deadlines.
//   - parentTable: The name of the parent table (e.g., "projects", "education").
//   - parentID: The unique identifier of the parent record.
//   - role: The file role to filter by (e.g., FileRoleImage, FileRoleDocument).
//
// Returns:
//   - []domain.File: A slice of files matching the criteria (may be empty).
//   - error: An error if the query fails, scanning fails, or row iteration encounters an issue.
func (r *fileRepository) FindByParent(ctx context.Context, parentTable, parentID string, role domain.FileRole) ([]domain.File, error) {
	if parentTable == "" {
		return nil, errors.New("failed to find files: parentTable missing")
	}
	if parentID == "" {
		return nil, errors.New("failed to find files: parentID missing")
	}
	if role == "" {
		return nil, errors.New("failed to find files: role missing")
	}

	query := fmt.Sprintf(
		`SELECT id, parent_table, parent_id, role, file_name, file_url, file_type, file_size, created_at, updated_at
		FROM %s
		WHERE parent_table = $1 AND parent_id = $2 AND role = $3
		ORDER BY created_at DESC`,
		r.fileTable,
	)

	rows, err := r.databaseAPI.Query(ctx, query, parentTable, parentID, role)
	if err != nil {
		return nil, fmt.Errorf("failed to query files by parent: %w", err)
	}
	defer rows.Close()

	var files []domain.File
	for rows.Next() {
		var file domain.File
		err := rows.Scan(
			&file.ID,
			&file.ParentTable,
			&file.ParentID,
			&file.Role,
			&file.FileName,
			&file.FileURL,
			&file.FileType,
			&file.FileSize,
			&file.CreatedAt,
			&file.UpdatedAt,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan file: %w", err)
		}

		files = append(files, file)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("row iteration error: %w", err)
	}

	return files, nil
}

// Create creates a new file record in the repository and returns its generated ID.
// It validates the provided File payload, generates a unique ID, sets CreatedAt and
// UpdatedAt timestamps from the repository's time provider, and inserts the record
// into the configured file table.
//
// Parameters:
//   - ctx: The context for controlling cancellation and deadlines.
//   - file: The file record to create. Must not be nil and must pass validation.
//
// Returns:
//   - string: The newly created file's ID.
//   - error: An error if validation fails, database insertion fails, or the returned ID is empty.
//
// The provided file object's CreatedAt and UpdatedAt fields are updated when the method succeeds.
func (r *fileRepository) Create(ctx context.Context, file *domain.File) (string, error) {
	if file == nil {
		return "", errors.New("failed to validate file: payload is nil")
	}

	if err := file.ValidatePayload(); err != nil {
		return "", fmt.Errorf("failed to validate file: %w", err)
	}

	id := utils.GenerateKey()
	now := r.timeProvider()

	file.CreatedAt = now
	file.UpdatedAt = now

	query := fmt.Sprintf(
		`INSERT INTO %s
		(id, parent_table, parent_id, role, file_name, file_url, file_type, file_size, created_at, updated_at)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
		RETURNING id`,
		r.fileTable,
	)

	var returnedID string
	err := r.databaseAPI.QueryRow(
		ctx,
		query,
		id,
		file.ParentTable,
		file.ParentID,
		file.Role,
		file.FileName,
		file.FileURL,
		file.FileType,
		file.FileSize,
		file.CreatedAt,
		file.UpdatedAt,
	).Scan(&returnedID)

	if err != nil {
		return "", fmt.Errorf("failed to create file: %w", err)
	}

	if returnedID == "" {
		return "", errors.New("invalid file returned: ID missing")
	}

	return returnedID, nil
}

// Delete removes a file from the database by its ID.
// It returns an error if the deletion fails or if no file with the given ID exists.
//
// Parameters:
//   - ctx: The context for controlling cancellation and deadlines.
//   - id: The unique identifier of the file to delete.
//
// Returns:
//   - error: An error if the operation fails or if no file is found with the specified ID (pgx.ErrNoRows).
func (r *fileRepository) Delete(ctx context.Context, id string) error {
	if id == "" {
		return errors.New("failed to delete file: ID missing")
	}

	query := fmt.Sprintf("DELETE FROM %s WHERE id=$1", r.fileTable)

	cmdTag, err := r.databaseAPI.Exec(ctx, query, id)
	if err != nil {
		return fmt.Errorf("failed to delete file: %w", err)
	}

	if cmdTag.RowsAffected() == 0 {
		return pgx.ErrNoRows
	}

	return nil
}

// FindByID retrieves a file by its unique identifier from the repository's database.
// The ctx is used for cancellation and deadlines. The id must be non-empty; if it is
// empty, FindByID returns an error indicating a missing ID. On success, it returns a
// pointer to a validated domain.File populated from the matching database row.
// If no row matches the provided id, the returned error will wrap pgx.ErrNoRows.
// Any other query/scan or validation failures are returned wrapped to provide context.
//
// Parameters:
//   - ctx: The context for controlling cancellation and deadlines.
//   - id: The unique identifier of the file to retrieve.
//
// Returns:
//   - *domain.File: A pointer to the file record if found.
//   - error: An error if the file is not found, the query fails, or validation fails.
func (r *fileRepository) FindByID(ctx context.Context, id string) (*domain.File, error) {
	if id == "" {
		return nil, errors.New("failed to get file: ID missing")
	}

	var file domain.File

	query := fmt.Sprintf(
		`SELECT id, parent_table, parent_id, role, file_name, file_url, file_type, file_size, created_at, updated_at
		FROM %s
		WHERE id = $1`,
		r.fileTable,
	)

	err := r.databaseAPI.QueryRow(ctx, query, id).Scan(
		&file.ID,
		&file.ParentTable,
		&file.ParentID,
		&file.Role,
		&file.FileName,
		&file.FileURL,
		&file.FileType,
		&file.FileSize,
		&file.CreatedAt,
		&file.UpdatedAt,
	)

	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, fmt.Errorf("failed to get file: %w", err)
		}
		return nil, fmt.Errorf("failed to scan file: %w", err)
	}

	if err := file.ValidateResponse(); err != nil {
		return nil, fmt.Errorf("invalid file returned: %w", err)
	}

	return &file, nil
}

Database Schema

You'll also need to create the files table in your PostgreSQL database:

CREATE TABLE files (
    id VARCHAR(36) PRIMARY KEY,
    parent_table VARCHAR(255) NOT NULL,
    parent_id VARCHAR(36) NOT NULL,
    role VARCHAR(50) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_url TEXT NOT NULL,
    file_type VARCHAR(100) NOT NULL,
    file_size BIGINT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    INDEX idx_parent_lookup (parent_table, parent_id, role)
);

Usage Example

// Initialize the repository
fileRepo := v1.NewFileRepository(v1.FileRepositoryConfig{
    DatabaseAPI: databaseAPI,
    FileTable:   "files",
})

// Create a new file
file := &domain.File{
    ParentTable: "projects",
    ParentID:    "some-project-id",
    Role:        domain.FileRoleImage,
    FileName:    "project-screenshot.png",
    FileURL:     "https://example.com/files/screenshot.png",
    FileType:    "image/png",
    FileSize:    1024000,
}
fileID, err := fileRepo.Create(ctx, file)

// Find files by parent
files, err := fileRepo.FindByParent(ctx, "projects", "some-project-id", domain.FileRoleImage)

// Find file by ID
file, err := fileRepo.FindByID(ctx, fileID)

// Delete file
err = fileRepo.Delete(ctx, fileID)

This implementation follows all the patterns from your existing codebase, including error handling, validation, time providers, and the database API abstraction. Let me know if you need any adjustments!

[fingertips18]

@coderabbitai so I already added the file table, refer to the database migration but I intend to drop it since some of the fields are wrong.

[coderabbitai]

🧩 Analysis chain

@fingertips18, let me check your existing database migration to see the current file table structure.

---

🏁 Script executed:

#!/bin/bash

# Find database migration files
echo "=== Database Migration Files ==="
fd -e sql -e go 'migration' apps/backend/

echo -e "\n=== Look for migration directory ==="
fd -t d 'migration' apps/backend/

echo -e "\n=== Search for file table creation in migrations ==="
rg -n "CREATE TABLE.*files" apps/backend/ --type sql -A 20

echo -e "\n=== Alternative: Search in Go migration files ==="
rg -n "CREATE TABLE.*files|files.*table" apps/backend/ -C 10 | head -100

echo -e "\n=== List all migration files ==="
find apps/backend/ -type f \( -name "*migration*" -o -name "*migrate*" \) -not -path "*/node_modules/*" | head -20

Length of output: 325

---

🏁 Script executed:

#!/bin/bash

# List all files in migrations directory
echo "=== Migration Files ==="
ls -la apps/backend/internal/database/migrations/

echo -e "\n=== Read migration files content ==="
fd -e sql -e go . apps/backend/internal/database/migrations/ --type f --exec echo "=== File: {} ===" \; --exec cat {} \;

echo -e "\n=== Check database migrations documentation ==="
cat apps/backend/docs/database-migrations.md

echo -e "\n=== Search for 'file' in all migration files ==="
rg -n "file" apps/backend/internal/database/migrations/ -i -C 5

Length of output: 17484

---

@fingertips18, I can see your existing file table migration. You're right—it has several fields that don't align with the repository code I provided. Here's what needs to be fixed:

Issues with Current Schema

1. Extra unnecessary fields: key, content_disposition, polling_jwt, polling_url, custom_id, fields
2. Redundant URL fields: Both file_url and url (duplicated)
3. Missing important field: file_size
4. ID type mismatch: Your codebase uses UUID (which is correct), but my code used string

Solution: New Migration + Updated Code

1. Create a new migration to fix the table

./scripts/create-migration.sh fix_file_table_schema

Migration Up (fix_file_table_schema.up.sql):

-- Drop the old table
DROP TABLE IF EXISTS file;

-- Recreate with correct schema
CREATE TABLE file (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    parent_table TEXT NOT NULL,
    parent_id UUID NOT NULL,
    role TEXT NOT NULL,
    file_name TEXT NOT NULL,
    file_url TEXT NOT NULL,
    file_type TEXT NOT NULL,
    file_size BIGINT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

-- Index for fast lookup
CREATE INDEX idx_file_parent_role ON file(parent_table, parent_id, role);

Migration Down (fix_file_table_schema.down.sql):

-- Revert to original schema (if needed)
DROP TABLE IF EXISTS file;

CREATE TABLE file (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    parent_table TEXT NOT NULL,
    parent_id UUID NOT NULL,
    role TEXT NOT NULL,
    key TEXT NOT NULL,
    file_name TEXT NOT NULL,
    file_type TEXT NOT NULL,
    file_url TEXT NOT NULL,
    content_disposition TEXT,
    polling_jwt TEXT,
    polling_url TEXT,
    custom_id TEXT,
    url TEXT NOT NULL,
    fields JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_file_parent_role ON file(parent_table, parent_id, role);

2. Updated Domain Model (UUID-based)

apps/backend/internal/domain/file.go:

package domain

import (
	"errors"
	"fmt"
	"time"

	"github.com/google/uuid"
)

type FileRole string

const (
	FileRoleImage    FileRole = "image"
	FileRoleDocument FileRole = "document"
	FileRoleVideo    FileRole = "video"
	FileRoleOther    FileRole = "other"
)

type File struct {
	ID          uuid.UUID `json:"id"`
	ParentTable string    `json:"parentTable"`
	ParentID    uuid.UUID `json:"parentId"`
	Role        FileRole  `json:"role"`
	FileName    string    `json:"fileName"`
	FileURL     string    `json:"fileUrl"`
	FileType    string    `json:"fileType"`
	FileSize    int64     `json:"fileSize"`
	CreatedAt   time.Time `json:"createdAt"`
	UpdatedAt   time.Time `json:"updatedAt"`
}

func (f File) ValidatePayload() error {
	if f.ParentTable == "" {
		return errors.New("parentTable missing")
	}
	if f.ParentID == uuid.Nil {
		return errors.New("parentId missing")
	}
	if f.Role == "" {
		return errors.New("role missing")
	}
	if f.Role != FileRoleImage && f.Role != FileRoleDocument && f.Role != FileRoleVideo && f.Role != FileRoleOther {
		return fmt.Errorf("invalid role: must be one of %s, %s, %s, %s", FileRoleImage, FileRoleDocument, FileRoleVideo, FileRoleOther)
	}
	if f.FileName == "" {
		return errors.New("fileName missing")
	}
	if f.FileURL == "" {
		return errors.New("fileUrl missing")
	}
	if f.FileType == "" {
		return errors.New("fileType missing")
	}
	if f.FileSize <= 0 {
		return errors.New("fileSize must be greater than 0")
	}
	return nil
}

func (f File) ValidateResponse() error {
	if f.ID == uuid.Nil {
		return errors.New("id missing")
	}
	if err := f.ValidatePayload(); err != nil {
		return err
	}
	if f.CreatedAt.IsZero() {
		return errors.New("createdAt missing")
	}
	if f.UpdatedAt.IsZero() {
		return errors.New("updatedAt missing")
	}
	return nil
}

3. Updated Repository (UUID-based)

apps/backend/internal/repository/v1/file.go:

package v1

import (
	"context"
	"errors"
	"fmt"
	"time"

	"github.com/fingertips18/fingertips18.github.io/backend/internal/database"
	"github.com/fingertips18/fingertips18.github.io/backend/internal/domain"
	"github.com/google/uuid"
	"github.com/jackc/pgx/v5"
)

type FileRepository interface {
	FindByParent(ctx context.Context, parentTable string, parentID uuid.UUID, role domain.FileRole) ([]domain.File, error)
	Create(ctx context.Context, file *domain.File) (uuid.UUID, error)
	Delete(ctx context.Context, id uuid.UUID) error
	FindByID(ctx context.Context, id uuid.UUID) (*domain.File, error)
}

type FileRepositoryConfig struct {
	DatabaseAPI database.DatabaseAPI
	FileTable   string

	timeProvider domain.TimeProvider
}

type fileRepository struct {
	fileTable    string
	databaseAPI  database.DatabaseAPI
	timeProvider domain.TimeProvider
}

func NewFileRepository(cfg FileRepositoryConfig) FileRepository {
	timeProvider := cfg.timeProvider
	if timeProvider == nil {
		timeProvider = time.Now
	}

	return &fileRepository{
		fileTable:    cfg.FileTable,
		databaseAPI:  cfg.DatabaseAPI,
		timeProvider: timeProvider,
	}
}

func (r *fileRepository) FindByParent(ctx context.Context, parentTable string, parentID uuid.UUID, role domain.FileRole) ([]domain.File, error) {
	if parentTable == "" {
		return nil, errors.New("failed to find files: parentTable missing")
	}
	if parentID == uuid.Nil {
		return nil, errors.New("failed to find files: parentID missing")
	}
	if role == "" {
		return nil, errors.New("failed to find files: role missing")
	}

	query := fmt.Sprintf(
		`SELECT id, parent_table, parent_id, role, file_name, file_url, file_type, file_size, created_at, updated_at
		FROM %s
		WHERE parent_table = $1 AND parent_id = $2 AND role = $3
		ORDER BY created_at DESC`,
		r.fileTable,
	)

	rows, err := r.databaseAPI.Query(ctx, query, parentTable, parentID, role)
	if err != nil {
		return nil, fmt.Errorf("failed to query files by parent: %w", err)
	}
	defer rows.Close()

	var files []domain.File
	for rows.Next() {
		var file domain.File
		err := rows.Scan(
			&file.ID,
			&file.ParentTable,
			&file.ParentID,
			&file.Role,
			&file.FileName,
			&file.FileURL,
			&file.FileType,
			&file.FileSize,
			&file.CreatedAt,
			&file.UpdatedAt,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan file: %w", err)
		}

		files = append(files, file)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("row iteration error: %w", err)
	}

	return files, nil
}

func (r *fileRepository) Create(ctx context.Context, file *domain.File) (uuid.UUID, error) {
	if file == nil {
		return uuid.Nil, errors.New("failed to validate file: payload is nil")
	}

	if err := file.ValidatePayload(); err != nil {
		return uuid.Nil, fmt.Errorf("failed to validate file: %w", err)
	}

	id := uuid.New()
	now := r.timeProvider()

	file.ID = id
	file.CreatedAt = now
	file.UpdatedAt = now

	query := fmt.Sprintf(
		`INSERT INTO %s
		(id, parent_table, parent_id, role, file_name, file_url, file_type, file_size, created_at, updated_at)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
		RETURNING id`,
		r.fileTable,
	)

	var returnedID uuid.UUID
	err := r.databaseAPI.QueryRow(
		ctx,
		query,
		id,
		file.ParentTable,
		file.ParentID,
		file.Role,
		file.FileName,
		file.FileURL,
		file.FileType,
		file.FileSize,
		file.CreatedAt,
		file.UpdatedAt,
	).Scan(&returnedID)

	if err != nil {
		return uuid.Nil, fmt.Errorf("failed to create file: %w", err)
	}

	if returnedID == uuid.Nil {
		return uuid.Nil, errors.New("invalid file returned: ID missing")
	}

	return returnedID, nil
}

func (r *fileRepository) Delete(ctx context.Context, id uuid.UUID) error {
	if id == uuid.Nil {
		return errors.New("failed to delete file: ID missing")
	}

	query := fmt.Sprintf("DELETE FROM %s WHERE id=$1", r.fileTable)

	cmdTag, err := r.databaseAPI.Exec(ctx, query, id)
	if err != nil {
		return fmt.Errorf("failed to delete file: %w", err)
	}

	if cmdTag.RowsAffected() == 0 {
		return pgx.ErrNoRows
	}

	return nil
}

func (r *fileRepository) FindByID(ctx context.Context, id uuid.UUID) (*domain.File, error) {
	if id == uuid.Nil {
		return nil, errors.New("failed to get file: ID missing")
	}

	var file domain.File

	query := fmt.Sprintf(
		`SELECT id, parent_table, parent_id, role, file_name, file_url, file_type, file_size, created_at, updated_at
		FROM %s
		WHERE id = $1`,
		r.fileTable,
	)

	err := r.databaseAPI.QueryRow(ctx, query, id).Scan(
		&file.ID,
		&file.ParentTable,
		&file.ParentID,
		&file.Role,
		&file.FileName,
		&file.FileURL,
		&file.FileType,
		&file.FileSize,
		&file.CreatedAt,
		&file.UpdatedAt,
	)

	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, fmt.Errorf("failed to get file: %w", err)
		}
		return nil, fmt.Errorf("failed to scan file: %w", err)
	}

	if err := file.ValidateResponse(); err != nil {
		return nil, fmt.Errorf("invalid file returned: %w", err)
	}

	return &file, nil
}

4. Run the migration

cd apps/backend
./scripts/migrate.sh up

The key changes from my previous code:

• ✅ Uses uuid.UUID instead of string for IDs (matches your codebase pattern)
• ✅ Removed unnecessary fields (key, polling_jwt, etc.)
• ✅ Added missing file_size field
• ✅ Removed duplicate URL field
• ✅ Validates UUID with uuid.Nil instead of empty string checks

This now properly aligns with your existing database patterns!

[fingertips18]

@coderabbitai this ./scripts/create-migration.sh fix_file_table_schema script does not exists.

[coderabbitai]

🧩 Analysis chain

🏁 Script executed:

#!/bin/bash

# Check what scripts exist in the scripts directory
echo "=== Scripts Directory ==="
ls -la apps/backend/scripts/ 2>/dev/null || ls -la scripts/ 2>/dev/null || echo "Scripts directory not found at expected locations"

# Find all shell scripts
echo -e "\n=== All Shell Scripts ==="
fd -e sh . apps/backend/ --type f | head -20

# Look at the migrate.sh script to understand the pattern
echo -e "\n=== Migrate Script Content ==="
cat apps/backend/scripts/migrate.sh 2>/dev/null || cat scripts/migrate.sh 2>/dev/null || echo "migrate.sh not found"

# Check how migrations are named/created
echo -e "\n=== Existing Migrations Pattern ==="
ls -la apps/backend/internal/database/migrations/ | head -20

# Check if golang-migrate is available and how it's used
echo -e "\n=== Check for migrate binary or installation ==="
which migrate || echo "migrate CLI not in PATH"

# Look for any documentation about creating migrations
echo -e "\n=== Search for migration creation docs ==="
rg -n "migrate create|creating migration" apps/backend/ --type md -C 3

Length of output: 2840

---

@fingertips18, you're absolutely right—my apologies for the confusion! The create-migration.sh script doesn't exist yet. Let me provide you with two options:

Option 1: Manual Migration Creation (Quick Solution)

Create the migration files manually following your existing naming pattern:

cd apps/backend/internal/database/migrations/

# Create the up migration
cat > 20250117060100_fix_file_table_schema.up.sql << 'EOF'
-- Drop the old table
DROP TABLE IF EXISTS file;

-- Recreate with correct schema
CREATE TABLE file (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    parent_table TEXT NOT NULL,
    parent_id UUID NOT NULL,
    role TEXT NOT NULL,
    file_name TEXT NOT NULL,
    file_url TEXT NOT NULL,
    file_type TEXT NOT NULL,
    file_size BIGINT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

-- Index for fast lookup
CREATE INDEX idx_file_parent_role ON file(parent_table, parent_id, role);
EOF

# Create the down migration
cat > 20250117060100_fix_file_table_schema.down.sql << 'EOF'
-- Revert to original schema
DROP TABLE IF EXISTS file;

CREATE TABLE file (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    parent_table TEXT NOT NULL,
    parent_id UUID NOT NULL,
    role TEXT NOT NULL,
    key TEXT NOT NULL,
    file_name TEXT NOT NULL,
    file_type TEXT NOT NULL,
    file_url TEXT NOT NULL,
    content_disposition TEXT,
    polling_jwt TEXT,
    polling_url TEXT,
    custom_id TEXT,
    url TEXT NOT NULL,
    fields JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_file_parent_role ON file(parent_table, parent_id, role);
EOF

Option 2: Create the create-migration.sh Script (Recommended)

Based on the learning from our previous interaction, you prefer having a helper script. Here's one that follows the same pattern as your migrate.sh:

apps/backend/scripts/create-migration.sh:

#!/usr/bin/env bash
set -eo pipefail
cd "$(dirname "$0")/.." || exit 1

MIGRATION_NAME=$1

if [ -z "$MIGRATION_NAME" ]; then
  echo "Error: Migration name required"
  echo "Usage: ./scripts/create-migration.sh <migration_name>"
  echo "Example: ./scripts/create-migration.sh fix_file_table_schema"
  exit 1
fi

TIMESTAMP=$(date -u +%Y%m%d%H%M%S)
MIGRATION_DIR="internal/database/migrations"
UP_FILE="${MIGRATION_DIR}/${TIMESTAMP}_${MIGRATION_NAME}.up.sql"
DOWN_FILE="${MIGRATION_DIR}/${TIMESTAMP}_${MIGRATION_NAME}.down.sql"

# Create the up migration file
cat > "$UP_FILE" << EOF
-- Migration: ${MIGRATION_NAME}
-- Created: $(date -u)

EOF

# Create the down migration file
cat > "$DOWN_FILE" << EOF
-- Rollback: ${MIGRATION_NAME}
-- Created: $(date -u)

EOF

echo "✅ Created migration files:"
echo "   $UP_FILE"
echo "   $DOWN_FILE"

Make it executable:

chmod +x apps/backend/scripts/create-migration.sh

Then use it:

./apps/backend/scripts/create-migration.sh fix_file_table_schema

This will create the two migration files with the correct timestamp, and you can then edit them to add the SQL statements I provided earlier.

Which approach would you prefer? I can also help you create the script if you'd like! 🐰

---

🧠 Learnings used

Learnt from: fingertips18
Repo: fingertips18/fingertips18.github.io PR: 604
File: backend/internal/database/migrations/20251026044752_skill_table.up.sql:2-2
Timestamp: 2025-10-26T05:18:55.981Z
Learning: In the fingertips18/fingertips18.github.io repository, gen_random_uuid() is available in the PostgreSQL database without requiring explicit pgcrypto extension setup. Existing migrations (project_table.up.sql, education_table.up.sql) have been using gen_random_uuid() successfully since August 2025.

---

Learnt from: fingertips18
Repo: fingertips18/fingertips18.github.io PR: 564
File: backend/internal/database/migrations/20250831062237_project_table.up.sql:8-9
Timestamp: 2025-10-11T05:43:59.925Z
Learning: User prefers to have a shell script (`create-migration.sh`) to generate migration files using the `migrate create` command, following the same pattern as their existing `migrate.sh` script for running migrations.

[fingertips18]

@coderabbitai now let's proceed with the handler, where is it?

[coderabbitai]

🧩 Analysis chain

@fingertips18, let me explore your codebase to find where handlers are located and understand the pattern used.

---

🏁 Script executed:

#!/bin/bash

# Find handler directories
echo "=== Handler Directories ==="
fd -t d 'handler' apps/backend/internal/

# List handler files
echo -e "\n=== Handler Files ==="
fd -e go . apps/backend/internal/handler/ --type f 2>/dev/null | head -20

# Look at an example handler (image handler since it's similar to file)
echo -e "\n=== Image Handler Implementation ==="
cat apps/backend/internal/handler/v1/image.go 2>/dev/null || echo "Image handler not found"

# Look at project handler for another example
echo -e "\n=== Project Handler Implementation ==="
cat apps/backend/internal/handler/v1/project.go 2>/dev/null | head -150

# Check handler structure
echo -e "\n=== All Handlers in v1 ==="
ls -la apps/backend/internal/handler/v1/ 2>/dev/null || echo "Handler v1 directory not found"

Length of output: 11176

---

@fingertips18, I found the handler structure! Handlers are located in apps/backend/internal/handler/v1/. Based on your existing patterns (project, education, skill handlers), here's the complete File Handler implementation:

1. File Handler

apps/backend/internal/handler/v1/file.go:

package v1

import (
	"bytes"
	"encoding/json"
	"errors"
	"net/http"
	"strings"

	"github.com/fingertips18/fingertips18.github.io/backend/internal/database"
	"github.com/fingertips18/fingertips18.github.io/backend/internal/domain"
	v1 "github.com/fingertips18/fingertips18.github.io/backend/internal/repository/v1"
	"github.com/fingertips18/fingertips18.github.io/backend/internal/utils"
	"github.com/google/uuid"
	"github.com/jackc/pgx/v5"
)

type FileHandler interface {
	http.Handler
	Create(w http.ResponseWriter, r *http.Request)
	Get(w http.ResponseWriter, r *http.Request, id string)
	Delete(w http.ResponseWriter, r *http.Request, id string)
	ListByParent(w http.ResponseWriter, r *http.Request)
}

type FileServiceConfig struct {
	DatabaseAPI database.DatabaseAPI

	fileRepo v1.FileRepository
}

type fileServiceHandler struct {
	fileRepo v1.FileRepository
}

// NewFileServiceHandler creates and returns a new instance of FileHandler.
// It accepts a FileServiceConfig, which may include a custom file repository.
// If no repository is provided in the config, it initializes a default FileRepository
// using the provided DatabaseAPI and a default table name.
// Returns a FileHandler implementation.
func NewFileServiceHandler(cfg FileServiceConfig) FileHandler {
	fileRepo := cfg.fileRepo
	if fileRepo == nil {
		fileRepo = v1.NewFileRepository(
			v1.FileRepositoryConfig{
				DatabaseAPI: cfg.DatabaseAPI,
				FileTable:   "file",
			},
		)
	}

	return &fileServiceHandler{
		fileRepo: fileRepo,
	}
}

// ServeHTTP handles HTTP requests for file-related endpoints.
//
// It supports the following routes:
//   - GET    /files?parentTable=...&parentId=...&role=...  : List files by parent
//   - POST   /file                                         : Create a new file record
//   - GET    /file/{id}                                    : Retrieve a file by its ID
//   - DELETE /file/{id}                                    : Delete a file by its ID
//
// For unsupported methods or unknown routes, it responds with appropriate HTTP error codes.
func (h *fileServiceHandler) ServeHTTP(w http.ResponseWriter, r *http.Request) {
	path := strings.TrimSuffix(r.URL.Path, "/")

	switch {
	// GET /files?parentTable=...&parentId=...&role=...
	case path == "/files":
		if r.Method != http.MethodGet {
			http.Error(w, "Method not allowed", http.StatusMethodNotAllowed)
			return
		}
		h.ListByParent(w, r)
		return

	// POST /file
	case path == "/file":
		if r.Method != http.MethodPost {
			http.Error(w, "Method not allowed", http.StatusMethodNotAllowed)
			return
		}
		h.Create(w, r)
		return

	// GET / DELETE /file/{id}
	case strings.HasPrefix(path, "/file/"):
		id := strings.TrimPrefix(path, "/file/")

		if r.Method != http.MethodGet && r.Method != http.MethodDelete {
			http.Error(w, "Method not allowed", http.StatusMethodNotAllowed)
			return
		}

		if id == "" {
			http.Error(w, "File ID is required", http.StatusBadRequest)
			return
		}

		switch r.Method {
		case http.MethodGet:
			h.Get(w, r, id)
		case http.MethodDelete:
			h.Delete(w, r, id)
		default:
			http.Error(w, "Method not allowed", http.StatusMethodNotAllowed)
		}
		return

	// Unknown route
	default:
		http.NotFound(w, r)
		return
	}
}

// Create handles HTTP POST requests to create a new file record.
// It expects a JSON payload in the request body representing file metadata.
// On success, it responds with a JSON object containing the new file's ID and a status message.
// If the request method is not POST, the JSON is invalid, or file creation fails, it responds with an appropriate HTTP error.
//
// `@Security` ApiKeyAuth
// `@Summary` Create a file record
// `@Description` Creates a new file metadata record from the provided JSON payload. Returns the created file ID.
// `@Tags` file
// `@Accept` json
// `@Produce` json
// `@Param` file body CreateFileRequest true "File payload"
// `@Success` 201 {object} IDResponse "File ID"
// `@Failure` 400 {object} ErrorResponse
// `@Failure` 500 {object} ErrorResponse
// `@Router` /file [post]
func (h *fileServiceHandler) Create(w http.ResponseWriter, r *http.Request) {
	defer r.Body.Close()

	var req CreateFileRequest
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		http.Error(w, "Invalid JSON in request body", http.StatusBadRequest)
		return
	}

	// Parse parent ID
	parentID, err := uuid.Parse(req.ParentID)
	if err != nil {
		http.Error(w, "Invalid parent ID format", http.StatusBadRequest)
		return
	}

	file := &domain.File{
		ParentTable: req.ParentTable,
		ParentID:    parentID,
		Role:        domain.FileRole(req.Role),
		FileName:    req.FileName,
		FileURL:     req.FileURL,
		FileType:    req.FileType,
		FileSize:    req.FileSize,
	}

	id, err := h.fileRepo.Create(r.Context(), file)
	if err != nil {
		msg := err.Error()
		if len(msg) > 0 {
			msg = strings.ToUpper(msg[:1]) + msg[1:]
		}
		http.Error(w, msg, http.StatusInternalServerError)
		return
	}

	resp := IDResponse{
		ID:      id.String(),
		Message: "File created successfully",
	}

	var buf bytes.Buffer
	if err := json.NewEncoder(&buf).Encode(resp); err != nil {
		http.Error(w, "Failed to write response: "+err.Error(), http.StatusInternalServerError)
		return
	}

	w.Header().Set("Content-Type", "application/json")
	w.WriteHeader(http.StatusCreated)
	w.Write(buf.Bytes())
}

// Get handles HTTP GET requests to retrieve a file by its ID.
// It expects the file ID as a URL path parameter.
// On success, it responds with a JSON representation of the file.
// If the ID is invalid, the file is not found, or retrieval fails, it responds with an appropriate HTTP error.
//
// `@Security` ApiKeyAuth
// `@Summary` Get a file by ID
// `@Description` Retrieves a file record by its unique identifier.
// `@Tags` file
// `@Produce` json
// `@Param` id path string true "File ID"
// `@Success` 200 {object} FileResponse "File details"
// `@Failure` 400 {object} ErrorResponse
// `@Failure` 404 {object} ErrorResponse
// `@Failure` 500 {object} ErrorResponse
// `@Router` /file/{id} [get]
func (h *fileServiceHandler) Get(w http.ResponseWriter, r *http.Request, id string) {
	fileID, err := uuid.Parse(id)
	if err != nil {
		http.Error(w, "Invalid file ID format", http.StatusBadRequest)
		return
	}

	file, err := h.fileRepo.FindByID(r.Context(), fileID)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			http.Error(w, "File not found", http.StatusNotFound)
			return
		}
		http.Error(w, "Failed to retrieve file: "+err.Error(), http.StatusInternalServerError)
		return
	}

	resp := FileResponse{
		ID:          file.ID.String(),
		ParentTable: file.ParentTable,
		ParentID:    file.ParentID.String(),
		Role:        string(file.Role),
		FileName:    file.FileName,
		FileURL:     file.FileURL,
		FileType:    file.FileType,
		FileSize:    file.FileSize,
		CreatedAt:   file.CreatedAt.Format(utils.ISO8601),
		UpdatedAt:   file.UpdatedAt.Format(utils.ISO8601),
	}

	var buf bytes.Buffer
	if err := json.NewEncoder(&buf).Encode(resp); err != nil {
		http.Error(w, "Failed to write response: "+err.Error(), http.StatusInternalServerError)
		return
	}

	w.Header().Set("Content-Type", "application/json")
	w.WriteHeader(http.StatusOK)
	w.Write(buf.Bytes())
}

// Delete handles HTTP DELETE requests to remove a file by its ID.
// It expects the file ID as a URL path parameter.
// On success, it responds with a confirmation message.
// If the ID is invalid, the file is not found, or deletion fails, it responds with an appropriate HTTP error.
//
// `@Security` ApiKeyAuth
// `@Summary` Delete a file by ID
// `@Description` Deletes a file record by its unique identifier.
// `@Tags` file
// `@Produce` json
// `@Param` id path string true "File ID"
// `@Success` 200 {object} MessageResponse "Deletion confirmation"
// `@Failure` 400 {object} ErrorResponse
// `@Failure` 404 {object} ErrorResponse
// `@Failure` 500 {object} ErrorResponse
// `@Router` /file/{id} [delete]
func (h *fileServiceHandler) Delete(w http.ResponseWriter, r *http.Request, id string) {
	fileID, err := uuid.Parse(id)
	if err != nil {
		http.Error(w, "Invalid file ID format", http.StatusBadRequest)
		return
	}

	err = h.fileRepo.Delete(r.Context(), fileID)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			http.Error(w, "File not found", http.StatusNotFound)
			return
		}
		http.Error(w, "Failed to delete file: "+err.Error(), http.StatusInternalServerError)
		return
	}

	resp := MessageResponse{
		Message: "File deleted successfully",
	}

	var buf bytes.Buffer
	if err := json.NewEncoder(&buf).Encode(resp); err != nil {
		http.Error(w, "Failed to write response: "+err.Error(), http.StatusInternalServerError)
		return
	}

	w.Header().Set("Content-Type", "application/json")
	w.WriteHeader(http.StatusOK)
	w.Write(buf.Bytes())
}

// ListByParent handles HTTP GET requests to retrieve files by parent entity and role.
// It expects query parameters: parentTable, parentId, and role.
// On success, it responds with a JSON array of files matching the criteria.
// If required parameters are missing or invalid, it responds with an appropriate HTTP error.
//
// `@Security` ApiKeyAuth
// `@Summary` List files by parent
// `@Description` Retrieves all files for a specific parent entity and role.
// `@Tags` file
// `@Produce` json
// `@Param` parentTable query string true "Parent table name"
// `@Param` parentId query string true "Parent ID"
// `@Param` role query string true "File role (image, document, video, other)"
// `@Success` 200 {array} FileResponse "List of files"
// `@Failure` 400 {object} ErrorResponse
// `@Failure` 500 {object} ErrorResponse
// `@Router` /files [get]
func (h *fileServiceHandler) ListByParent(w http.ResponseWriter, r *http.Request) {
	query := r.URL.Query()
	parentTable := query.Get("parentTable")
	parentIDStr := query.Get("parentId")
	roleStr := query.Get("role")

	if parentTable == "" {
		http.Error(w, "parentTable query parameter is required", http.StatusBadRequest)
		return
	}
	if parentIDStr == "" {
		http.Error(w, "parentId query parameter is required", http.StatusBadRequest)
		return
	}
	if roleStr == "" {
		http.Error(w, "role query parameter is required", http.StatusBadRequest)
		return
	}

	parentID, err := uuid.Parse(parentIDStr)
	if err != nil {
		http.Error(w, "Invalid parentId format", http.StatusBadRequest)
		return
	}

	files, err := h.fileRepo.FindByParent(r.Context(), parentTable, parentID, domain.FileRole(roleStr))
	if err != nil {
		http.Error(w, "Failed to retrieve files: "+err.Error(), http.StatusInternalServerError)
		return
	}

	var fileResponses []FileResponse
	for _, file := range files {
		fileResponses = append(fileResponses, FileResponse{
			ID:          file.ID.String(),
			ParentTable: file.ParentTable,
			ParentID:    file.ParentID.String(),
			Role:        string(file.Role),
			FileName:    file.FileName,
			FileURL:     file.FileURL,
			FileType:    file.FileType,
			FileSize:    file.FileSize,
			CreatedAt:   file.CreatedAt.Format(utils.ISO8601),
			UpdatedAt:   file.UpdatedAt.Format(utils.ISO8601),
		})
	}

	var buf bytes.Buffer
	if err := json.NewEncoder(&buf).Encode(fileResponses); err != nil {
		http.Error(w, "Failed to write response: "+err.Error(), http.StatusInternalServerError)
		return
	}

	w.Header().Set("Content-Type", "application/json")
	w.WriteHeader(http.StatusOK)
	w.Write(buf.Bytes())
}

2. DTOs to Add to dto.go

Add these DTO definitions to apps/backend/internal/handler/v1/dto.go:

// File DTOs

type CreateFileRequest struct {
	ParentTable string `json:"parentTable"`
	ParentID    string `json:"parentId"`
	Role        string `json:"role"`
	FileName    string `json:"fileName"`
	FileURL     string `json:"fileUrl"`
	FileType    string `json:"fileType"`
	FileSize    int64  `json:"fileSize"`
}

type FileResponse struct {
	ID          string `json:"id"`
	ParentTable string `json:"parentTable"`
	ParentID    string `json:"parentId"`
	Role        string `json:"role"`
	FileName    string `json:"fileName"`
	FileURL     string `json:"fileUrl"`
	FileType    string `json:"fileType"`
	FileSize    int64  `json:"fileSize"`
	CreatedAt   string `json:"createdAt"`
	UpdatedAt   string `json:"updatedAt"`
}

3. Usage Example

// In your main.go or router setup
fileHandler := v1.NewFileServiceHandler(v1.FileServiceConfig{
    DatabaseAPI: databaseAPI,
})

// Register routes
http.Handle("/file", fileHandler)
http.Handle("/file/", fileHandler)
http.Handle("/files", fileHandler)

The handler follows the same patterns as your existing handlers and provides full CRUD operations for file metadata management! 🚀

---

🧠 Learnings used

Learnt from: fingertips18
Repo: fingertips18/fingertips18.github.io PR: 564
File: backend/internal/database/migrations/20250831062237_project_table.up.sql:8-9
Timestamp: 2025-10-11T05:43:59.925Z
Learning: User prefers to have a shell script (`create-migration.sh`) to generate migration files using the `migrate create` command, following the same pattern as their existing `migrate.sh` script for running migrations.