FEAT[BE]: add GetAllProductUsageByProjectFlockKandangID method and ProductUsageRow struct

fix(BE): remove supplier price in master nonstock

See merge request mbugroup/lti-api!194

.air.toml

@@ -3,13 +3,9 @@ root = "."
 tmp_dir = "tmp"
 
 [build]
-# Build binary utama
-cmd = "go build -o /lti-api/tmp/main ./cmd/api"
-# Lokasi binary hasil build
-bin = "/lti-api/tmp/main"
-# Jalankan binary langsung dengan environment dev
-full_bin = "APP_ENV=dev /lti-api/tmp/main"
-# File yang dipantau oleh Air
+cmd = "go build -buildvcs=false -o ./tmp/main ./cmd/api"
+bin = "tmp/main"
+full_bin = "APP_ENV=dev ./tmp/main"
 include_ext = ["go", "tpl", "tmpl", "html"]
 exclude_dir = ["vendor", "tmp"]
 

.env.example

@@ -1,56 +0,0 @@
-# server configuration
-# Env value : prod || dev
-VERSION=0.0.1
-APP_ENV=dev
-APP_HOST=0.0.0.0
-APP_PORT=8081
-APP_URL=http://localhost:8081
-
-# database configuration
-DB_HOST=postgresdb
-DB_USER=postgres
-DB_PASSWORD=changeme
-DB_NAME=db_lti_erp
-DB_PORT=5432
-DB_PORT_HOST=5542
-
-# JWT
-JWT_SECRET=changeme
-JWT_ACCESS_EXP_MINUTES=30
-JWT_REFRESH_EXP_DAYS=30
-JWT_RESET_PASSWORD_EXP_MINUTES=10
-JWT_VERIFY_EMAIL_EXP_MINUTES=10
-
-# CORS
-CORS_ALLOW_ORIGINS=changeme
-CORS_ALLOW_METHODS=GET,POST,PUT,PATCH,DELETE,OPTIONS
-CORS_ALLOW_HEADERS=Authorization,Content-Type,X-Requested-With
-CORS_EXPOSE_HEADERS=Link,Location
-CORS_ALLOW_CREDENTIALS=true
-CORS_MAX_AGE=600
-
-# Redis
-REDIS_URL=redis://redis:6379/0
-REDIS_PORT_HOST=6381
-
-# SSO Integration
-SSO_ISSUER=http://localhost:8080/api
-# SSO_JWKS_URL=http://localhost:8080/api/.well-known/jwks.json
-SSO_JWKS_URL=http://host.docker.internal:8080/api/.well-known/jwks.json
-SSO_ALLOWED_AUDIENCES=client:lti-api
-SSO_AUTHORIZE_URL=http://localhost:8080/sso/authorize
-SSO_TOKEN_URL=http://localhost:8080/sso/token
-SSO_GETME_URL=http://localhost:8080/api/auth/get-me
-SSO_ACCESS_COOKIE_NAME=sso_access
-SSO_REFRESH_COOKIE_NAME=sso_refresh
-SSO_COOKIE_DOMAIN=
-SSO_COOKIE_SECURE=false
-SSO_COOKIE_SAMESITE=Lax
-SSO_TOKEN_BLACKLIST_PREFIX=sso:blacklist
-SSO_PKCE_TTL_SECONDS=300
-# Security window and payload limits for SSO user sync webhook
-SSO_USER_SYNC_SIGNATURE_DRIFT_SECONDS=120
-SSO_USER_SYNC_NONCE_TTL_SECONDS=600
-SSO_USER_SYNC_MAX_BODY_BYTES=32768
-# Example JSON (single-line) of client configs (each client requires a unique sync_secret)
-SSO_CLIENTS={"LTI":{"public_id":"Lumbung-Telur-Indonesia","redirect_uri":"http://localhost:8081/api/sso/callback","scope":"openid profile","default_return_uri":"http://localhost:3000","allowed_return_origins":["http://localhost:3000"],"sync_secret":"onUyfODIMHOh4TgGLgyWLmsNeVNxFRHqoLJFLPjr"}}

.env.lti-api

@@ -1,58 +0,0 @@
-# .env.lti-api (Development Server with Domain)
-# =============================================
-
-# Server configuration
-VERSION=0.0.1
-APP_ENV=dev
-APP_HOST=0.0.0.0
-APP_PORT=8081
-APP_URL=https://dev-api-lti.mbugroup.id
-
-# Database configuration (pakai PostgreSQL milik SSO)
-DB_HOST=sso-postgres
-DB_USER=postgres
-DB_PASSWORD=postgres
-DB_NAME=db_lti_erp
-DB_PORT=5432
-
-# JWT configuration
-JWT_SECRET=changeme
-JWT_ACCESS_EXP_MINUTES=30
-JWT_REFRESH_EXP_DAYS=30
-JWT_RESET_PASSWORD_EXP_MINUTES=10
-JWT_VERIFY_EMAIL_EXP_MINUTES=10
-
-# Redis (pakai Redis milik SSO)
-REDIS_URL=redis://sso-redis:6379/0
-
-# CORS configuration
-CORS_ALLOW_ORIGINS=https://dev-api-sso.mbugroup.id,https://dev-lti.mbugroup.id,https://dev-api-lti.mbugroup.id,http://localhost:3000
-CORS_ALLOW_METHODS=GET,POST,PUT,PATCH,DELETE,OPTIONS
-CORS_ALLOW_HEADERS=Authorization,Content-Type,X-Requested-With
-CORS_EXPOSE_HEADERS=Link,Location
-CORS_ALLOW_CREDENTIALS=true
-CORS_MAX_AGE=600
-
-# SSO Integration (Gunakan domain backend SSO)
-SSO_ISSUER=https://dev-api-sso.mbugroup.id
-SSO_JWKS_URL=https://dev-api-sso.mbugroup.id/api/.well-known/jwks.json
-SSO_ALLOWED_AUDIENCES=
-SSO_AUTHORIZE_URL=https://dev-api-sso.mbugroup.id/api/sso/authorize
-SSO_TOKEN_URL=https://dev-api-sso.mbugroup.id/api/sso/token
-SSO_GETME_URL=https://dev-api-sso.mbugroup.id/api/auth/get-me
-
-# Cookie & session configuration
-SSO_ACCESS_COOKIE_NAME=sso_access
-SSO_REFRESH_COOKIE_NAME=sso_refresh
-SSO_COOKIE_DOMAIN=.mbugroup.id
-SSO_COOKIE_SECURE=true
-SSO_COOKIE_SAMESITE=Lax
-SSO_PKCE_TTL_SECONDS=300
-
-# SSO webhook / user sync settings
-SSO_USER_SYNC_SIGNATURE_DRIFT_SECONDS=120
-SSO_USER_SYNC_NONCE_TTL_SECONDS=600
-SSO_USER_SYNC_MAX_BODY_BYTES=32768
-
-# Client registration for SSO
-SSO_CLIENTS={"Lumbung-Telur-Indonesia":{"public_id":"Lumbung-Telur-Indonesia","redirect_uri":"https://dev-api-lti.mbugroup.id/api/sso/callback","scope":"openid profile","default_return_uri":"https://dev-lti.mbugroup.id","allowed_return_origins":["https://dev-lti.mbugroup.id","http://localhost:3000"],"sync_secret":"onUyfODIMHOh4TgGLgyWLmsNeVNxFRHqoLJFLPjr"}}

.gitignore

@@ -9,14 +9,16 @@ main
 bin/
 *.exe
 *.out
-
+.air.toml
 Makefile
 docker-compose.local.yml
 docker-compose.yaml
+Dockerfile
 Dockerfile.local
+.gitlab-ci.yml
 # Go build cache
 .gocache/
-vendor/
+vendor
 
 # Logs & reports
 *.log
@@ -27,3 +29,4 @@ coverage/
 .vscode/
 .idea/
 *.swp
+.DS_Store

.gitlab-ci.yml

@@ -0,0 +1,90 @@
+stages:
+  - deploy
+
+deploy-dev:
+  stage: deploy
+  image: alpine:3.20
+  variables:
+    DEPLOY_APP: "LTI-MBUGROUP"
+    # Opsional: kalau pakai submodule, ini bikin clone submodule pakai SSH juga
+    GIT_SUBMODULE_STRATEGY: recursive
+    GIT_DEPTH: "1"
+
+  before_script:
+    - echo "🧰 Installing dependencies..."
+    - apk update && apk add --no-cache openssh git curl bash
+
+    # Setup SSH di runner
+    - mkdir -p ~/.ssh
+    - echo "$SSH_PRIVATE_KEY" | tr -d '\r' > ~/.ssh/id_rsa
+    - chmod 600 ~/.ssh/id_rsa
+    - eval "$(ssh-agent -s)"
+    - ssh-add ~/.ssh/id_rsa
+
+    # Trust host keys (server + gitlab) biar SSH gak nanya interaktif
+    - ssh-keyscan -H "$SERVER_IP" >> ~/.ssh/known_hosts
+    - ssh-keyscan -H gitlab.com >> ~/.ssh/known_hosts
+
+  script:
+    - echo "🚀 Deploying latest code to $SERVER_USER@$SERVER_IP"
+
+    - >
+      if ssh -o StrictHostKeyChecking=no "$SERVER_USER@$SERVER_IP" "
+        set -e
+
+        cd /home/devops/docker/deployment/development/lti-api
+
+        # Pastikan remote origin SSH (antisipasi kalau pernah ke-set HTTPS)
+        git remote set-url origin git@gitlab.com:mbugroup/lti-api.git
+
+        # Pastikan server percaya gitlab.com juga (untuk git fetch via SSH)
+        mkdir -p ~/.ssh
+        ssh-keyscan -H gitlab.com >> ~/.ssh/known_hosts
+
+        # Fetch/reset pakai SSH
+        GIT_SSH_COMMAND='ssh -o StrictHostKeyChecking=no' git fetch origin development
+        git reset --hard origin/development
+
+        docker compose restart dev-api-lti || docker compose up -d dev-api-lti
+      "; then
+        STATUS='success';
+      else
+        STATUS='failed';
+      fi;
+
+      RUN_URL="${CI_PROJECT_URL}/-/pipelines/${CI_PIPELINE_ID}";
+
+      if [ "$STATUS" = "success" ]; then
+        COLOR=3066993;
+        TITLE="✅ Deployment API Succeeded";
+        DESC="Deployment job on branch \`${CI_COMMIT_REF_NAME}\` completed successfully.";
+      else
+        COLOR=15158332;
+        TITLE="❌ Deployment API Failed Gaes";
+        DESC="Deployment job on branch \`${CI_COMMIT_REF_NAME}\` failed.";
+      fi;
+
+      echo "{
+        \"username\": \"CI Bot\",
+        \"embeds\": [{
+          \"title\": \"$TITLE\",
+          \"description\": \"$DESC\",
+          \"color\": $COLOR,
+          \"fields\": [
+            {\"name\": \"Repository\", \"value\": \"${CI_PROJECT_PATH}\", \"inline\": true},
+            {\"name\": \"Actor\", \"value\": \"${GITLAB_USER_LOGIN}\", \"inline\": true},
+            {\"name\": \"Commit\", \"value\": \"${CI_COMMIT_SHA}\", \"inline\": false},
+            {\"name\": \"Pipeline\", \"value\": \"[Open run](${RUN_URL})\", \"inline\": false}
+          ]
+        }]
+      }" > payload.json;
+
+      echo "📡 Sending notification to Discord...";
+      curl -sS -H "Content-Type: application/json" \
+        -d @payload.json "$DISCORD_WEBHOOK_URL";
+
+  only:
+    - development
+
+  environment:
+    name: development

.gitlab-ci.yml.old

@@ -1,78 +0,0 @@
-stages:
-  - build
-  - deploy
-  - cleanup
-
-# ==============================
-# 🏗️ BUILD IMAGE (Overwrite :dev)
-# ==============================
-build_image:
-  stage: build
-  image: docker:latest
-  services:
-    - docker:dind
-  variables:
-    DOCKER_TLS_CERTDIR: "/certs"
-  script:
-    - echo "🔧 Building Docker image for :dev..."
-    - docker login -u gitlab-ci-token -p "$CI_JOB_TOKEN" "$CI_REGISTRY"
-    - docker build -f Dockerfile.local -t registry.gitlab.com/mbugroup/sso-mbugroup/lti-api:dev .
-    - docker push registry.gitlab.com/mbugroup/sso-mbugroup/lti-api:dev
-  only:
-    - development
-
-# ==============================
-# 🚀 DEPLOY TO DEV SERVER
-# ==============================
-deploy_lti:
-  stage: deploy
-  image: alpine:latest
-  before_script:
-    - apk add --no-cache openssh-client bash curl
-    - mkdir -p ~/.ssh
-    - echo "$SSH_PRIVATE_KEY" > ~/.ssh/id_rsa
-    - chmod 600 ~/.ssh/id_rsa
-    - ssh-keyscan -H "$SERVER_IP" >> ~/.ssh/known_hosts
-
-  script:
-    - echo "🚀 Deploy ke ${SERVER_USER}@${SERVER_IP} menggunakan image :dev"
-    - |
-      ssh -o StrictHostKeyChecking=no ${SERVER_USER}@${SERVER_IP} bash -s <<REMOTE
-        set -e
-
-        APP_NAME="lti-api"
-        DOCKER_IMAGE="registry.gitlab.com/mbugroup/sso-mbugroup/lti-api:dev"
-        NETWORK_NAME="lti-network"
-        ENV_PATH="/home/devops/code/api/lti-api/.env.lti-api"
-        PORT=8081
-
-        echo "🔑 Login ke GitLab Registry..."
-        echo "${GITLAB_TOKEN}" | docker login -u "${GITLAB_USER}" --password-stdin registry.gitlab.com
-
-        echo "🛑 Stop & remove old container..."
-        docker stop "\${APP_NAME}" >/dev/null 2>&1 || true
-        docker rm -f "\${APP_NAME}" >/dev/null 2>&1 || true
-
-        echo "🧹 Membersihkan container zombie di port \${PORT}..."
-        OLD_ID=\$(docker ps -aq --filter "publish=\${PORT}")
-        if [ -n "\${OLD_ID}" ]; then
-          echo "⚠️  Container lain masih pakai port \${PORT}, hapus..."
-          docker stop \${OLD_ID} >/dev/null 2>&1 || true
-          docker rm -f \${OLD_ID} >/dev/null 2>&1 || true
-        fi
-
-        echo "🐳 Pull image baru..."
-        docker pull "\${DOCKER_IMAGE}"
-
-        echo "🚀 Run container baru..."
-        docker run -d --name "\${APP_NAME}" --restart always \
-          --env-file "\${ENV_PATH}" \
-          -p \${PORT}:8081 \
-          --network "\${NETWORK_NAME}" \
-          "\${DOCKER_IMAGE}"
-
-        echo "✅ Deployment selesai di port \${PORT}"
-      REMOTE
-
-  only:
-    - development

Dockerfile

@@ -0,0 +1,38 @@
+# =========================
+# Builder stage
+# =========================
+FROM golang:1.23-alpine AS builder
+
+RUN apk add --no-cache git ca-certificates tzdata
+WORKDIR /app
+
+COPY go.mod go.sum ./
+RUN go mod download
+
+COPY . .
+
+# Build API binary
+RUN CGO_ENABLED=0 GOOS=linux GOARCH=amd64 \
+    go build -trimpath -ldflags="-s -w" -o lti-api ./cmd/api
+
+# Build SEED binary (pastikan cmd/seed ada)
+RUN CGO_ENABLED=0 GOOS=linux GOARCH=amd64 \
+    go build -trimpath -ldflags="-s -w" -o lti-seed ./cmd/seed
+
+# =========================
+# Runtime stage
+# =========================
+FROM alpine:3.20
+
+RUN apk add --no-cache ca-certificates tzdata curl bash postgresql-client \
+  && adduser -D -H -u 10001 appuser
+
+WORKDIR /app
+
+COPY --from=builder /app/lti-api /app/lti-api
+COPY --from=builder /app/lti-seed /app/lti-seed
+
+USER appuser
+EXPOSE 8081
+
+CMD ["/app/lti-api"]

Dockerfile.local

@@ -1,20 +0,0 @@
-FROM golang:1.23-alpine
-
-# Install dependensi dasar
-RUN apk add --no-cache git curl bash build-base
-
-# Install Air (pakai repo baru air-verse)
-RUN go install github.com/air-verse/air@v1.52.3
-
-WORKDIR /lti-api
-
-# Cache dependencies
-COPY go.mod go.sum ./
-RUN go mod download
-
-# Copy source code
-COPY . .
-
-EXPOSE 8081
-
-CMD ["air", "-c", ".air.toml"]

Makefile

@@ -1,59 +0,0 @@
-# ===============================
-# LTI-API Makefile (Docker Setup)
-# ===============================
-
-APP_NAME := lti-api
-COMPOSE := docker compose -f docker-compose.yaml
-NETWORK := lti-network
-ENV_FILE := .env.lti-api
-
-include $(ENV_FILE)
-export $(shell sed 's/=.*//' $(ENV_FILE))
-
-MIGRATIONS_DIR := ./migrations
-MIGRATE_IMAGE := migrate/migrate:v4.15.2
-DB_URL := postgres://$(DB_USER):$(DB_PASSWORD)@lti-postgres:5432/$(DB_NAME)?sslmode=disable
-
-# --- Docker ---
-docker-local:
-	@echo "🚀 Starting $(APP_NAME) with local PostgreSQL & Redis..."
-	@$(COMPOSE) up --build -d
-
-docker-down:
-	@$(COMPOSE) down --remove-orphans
-
-docker-nuke:
-	@echo "💣 Removing all containers, images, and volumes..."
-	@$(COMPOSE) down --rmi all --volumes --remove-orphans
-
-# --- Database / Migration ---
-
-wait-db:
-	@echo "⏳ Waiting for database lti-postgres to be ready (inside Docker network)..."
-	@$(COMPOSE) run --rm app sh -c 'until nc -z lti-postgres 5432; do echo "Waiting for DB..."; sleep 2; done; echo "✅ Database is ready!"'
-
-migrate-up: wait-db
-	@echo "⬆️  Running migrations..."
-	@docker run --rm -v $(MIGRATIONS_DIR):/migrations --network $(NETWORK) \
-		$(MIGRATE_IMAGE) -path=/migrations/ -database "$(DB_URL)" up
-
-migrate-down: wait-db
-	@echo "⬇️  Rolling back all migrations..."
-	@docker run --rm -v $(MIGRATIONS_DIR):/migrations --network $(NETWORK) \
-		$(MIGRATE_IMAGE) -path=/migrations/ -database "$(DB_URL)" down -all
-
-seed:
-	@echo "🌱 Running seed script..."
-	@$(COMPOSE) run --rm app go run cmd/seed/main.go
-
-psql:
-	@docker exec -it lti-postgres psql -U $(DB_USER) -d $(DB_NAME)
-
-logs:
-	@$(COMPOSE) logs -f app
-
-restart:
-	@$(COMPOSE) restart
-
-status:
-	@$(COMPOSE) ps

README.md

@@ -110,4 +110,4 @@ IT Development PT Mitra Berlian Unggas Group
 
 ## 📃 License
 
-This project is private. All rights reserved.
+> This project is private. All rights reserved.

credential/.env.db

@@ -0,0 +1,3 @@
+POSTGRES_USER=postgres
+POSTGRES_PASSWORD=Postgres@Secure2025!
+POSTGRES_DB=db_lti_erp

credential/01_init_app_user.sql

@@ -0,0 +1,47 @@
+-- ============================================================
+-- 🧩 INIT SCRIPT: CREATE LIMITED APP USER FOR LTI API
+-- ============================================================
+
+-- Buat user aplikasi jika belum ada
+DO
+$$
+BEGIN
+    IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'app_lti_user') THEN
+        CREATE ROLE app_lti_user WITH LOGIN PASSWORD 'AppLti@Secure2025!' NOINHERIT NOCREATEROLE NOCREATEDB NOSUPERUSER;
+        RAISE NOTICE '✅ Role app_lti_user created successfully.';
+    ELSE
+        RAISE NOTICE 'ℹ️ Role app_lti_user already exists.';
+    END IF;
+END
+$$;
+
+-- Buat database jika belum ada
+DO
+$$
+BEGIN
+    IF NOT EXISTS (SELECT FROM pg_database WHERE datname = 'db_lti_erp') THEN
+        CREATE DATABASE db_lti_erp OWNER app_lti_user;
+        RAISE NOTICE '✅ Database db_lti_erp created and owned by app_lti_user.';
+    ELSE
+        RAISE NOTICE 'ℹ️ Database db_lti_erp already exists.';
+    END IF;
+END
+$$;
+
+\connect db_lti_erp
+
+-- Beri hak CRUD untuk app_lti_user
+GRANT CONNECT ON DATABASE db_lti_erp TO app_lti_user;
+GRANT USAGE ON SCHEMA public TO app_lti_user;
+GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_lti_user;
+GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_lti_user;
+
+-- Set default privileges agar tabel baru juga bisa diakses
+ALTER DEFAULT PRIVILEGES IN SCHEMA public
+GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_lti_user;
+
+ALTER DEFAULT PRIVILEGES IN SCHEMA public
+GRANT USAGE, SELECT ON SEQUENCES TO app_lti_user;
+
+-- Tampilkan hasil
+\du app_lti_user

docker-compose.local.yml

@@ -1,75 +0,0 @@
-services:
-  postgresdb:
-    image: postgres:alpine
-    restart: always
-    ports:
-      - "${DB_PORT_HOST:-5542}:5432"
-    environment:
-      POSTGRES_USER: ${DB_USER:-postgres}
-      POSTGRES_PASSWORD: ${DB_PASSWORD:-postgres}
-      POSTGRES_DB: ${DB_NAME:-db_lti_erp}
-    volumes:
-      - dbdata:/var/lib/postgresql/data
-      - ./internal/database/init:/docker-entrypoint-initdb.d
-    networks: [go-network]
-    healthcheck:
-      test:
-        [
-          "CMD-SHELL",
-          "pg_isready -U ${DB_USER:-postgres} -d ${DB_NAME:-db_lti_erp}",
-        ]
-      interval: 10s
-      timeout: 5s
-      retries: 5
-  redis:
-    image: redis:7-alpine
-    restart: unless-stopped
-    ports:
-      - "${REDIS_PORT_HOST:-6381}:6379"
-    healthcheck:
-      test: ["CMD-SHELL", "redis-cli ping | grep PONG"]
-      interval: 5s
-      timeout: 3s
-      retries: 10
-    networks: [go-network]
-
-  app:
-    build:
-      context: .
-      dockerfile: Dockerfile.local
-    image: cosmtrek/air:v1.52.3
-    working_dir: /lti-api
-    volumes:
-      - .:/lti-api
-    command: air -c .air.toml
-    env_file:
-      - .env
-    environment:
-      DB_HOST: postgresdb
-      DB_PORT: 5432
-      DB_USER: ${DB_USER:-postgres}
-      DB_PASSWORD: ${DB_PASSWORD:-postgres}
-      DB_NAME: ${DB_NAME:-db_lti_erp}
-      REDIS_URL: ${REDIS_URL:-redis://redis:6379/0}
-    ports:
-      - "${APP_PORT:-8081}:8081"
-    depends_on:
-      postgresdb:
-        condition: service_healthy
-    networks: [go-network]
-    healthcheck:
-      test: ["CMD-SHELL", "wget -qO- http://localhost:8081/healthz || exit 1"]
-      interval: 10s
-      timeout: 3s
-      retries: 10
-      start_period: 10s
-
-volumes:
-  dbdata:
-  go-mod-cache:
-  go-build-cache:
-
-networks:
-  go-network:
-    name: lti-api_go-network
-    driver: bridge

docker-compose.yaml

@@ -1,77 +0,0 @@
-version: "3.9"
-
-services:
-  dev-lti-api:
-    container_name: dev-lti-api
-    build:
-      context: .
-      dockerfile: Dockerfile.local
-    image: dev-lti-api:latest
-    working_dir: /lti-api
-    command: air -c .air.toml
-    ports:
-      - "8081:8081"
-    env_file:
-      - .env.lti-api
-    environment:
-      # override agar koneksi ke container internal
-      DB_HOST: dev-lti-postgres
-      DB_PORT: 5432
-      REDIS_URL: redis://dev-lti-redis:6379/0
-    volumes:
-      - .:/lti-api
-      - ./internal/config/jwtRS256.key:/run/keys/jwtRS256.key
-      - ./internal/config/jwtRS256.key.pub:/run/keys/jwtRS256.key.pub
-    depends_on:
-      - dev-lti-postgres
-      - dev-lti-redis
-    networks:
-      - lti-network
-    healthcheck:
-      test: ["CMD-SHELL", "wget -qO- http://localhost:8081/healthz || exit 1"]
-      interval: 10s
-      timeout: 3s
-      retries: 10
-      start_period: 10s
-
-  dev-lti-postgres:
-    image: postgres:15-alpine
-    container_name: dev-lti-postgres
-    restart: always
-    environment:
-      POSTGRES_USER: ${DB_USER:-postgres}
-      POSTGRES_PASSWORD: ${DB_PASSWORD:-postgres}
-      POSTGRES_DB: ${DB_NAME:-db_lti_erp}
-    ports:
-      - "5433:5432"
-    volumes:
-      - dev-lti-postgres-data:/var/lib/postgresql/data
-    networks:
-      - lti-network
-    healthcheck:
-      test: ["CMD-SHELL", "pg_isready -U ${DB_USER:-postgres} -d ${DB_NAME:-db_lti_erp}"]
-      interval: 10s
-      timeout: 5s
-      retries: 5
-      start_period: 5s
-
-  dev-lti-redis:
-    image: redis:7-alpine
-    container_name: dev-lti-redis
-    restart: always
-    ports:
-      - "6380:6379"
-    networks:
-      - lti-network
-    healthcheck:
-      test: ["CMD", "redis-cli", "ping"]
-      interval: 10s
-      timeout: 3s
-      retries: 10
-
-networks:
-  lti-network:
-    driver: bridge
-
-volumes:
-  dev-lti-postgres-data:

go.mod

@@ -4,16 +4,23 @@ go 1.23
 
 require (
 	github.com/MicahParks/keyfunc/v2 v2.1.0
+	github.com/aws/aws-sdk-go-v2 v1.40.0
+	github.com/aws/aws-sdk-go-v2/config v1.32.2
+	github.com/aws/aws-sdk-go-v2/credentials v1.19.2
+	github.com/aws/aws-sdk-go-v2/service/s3 v1.92.1
 	github.com/bytedance/sonic v1.12.1
 	github.com/glebarez/sqlite v1.11.0
 	github.com/go-playground/validator/v10 v10.27.0
 	github.com/gofiber/contrib/jwt v1.0.10
 	github.com/gofiber/fiber/v2 v2.52.5
 	github.com/golang-jwt/jwt/v5 v5.2.1
+	github.com/google/uuid v1.6.0
 	github.com/jackc/pgconn v1.14.1
+	github.com/jackc/pgx/v5 v5.5.5
 	github.com/redis/go-redis/v9 v9.14.0
 	github.com/sirupsen/logrus v1.9.3
 	github.com/spf13/viper v1.19.0
+	github.com/xuri/excelize/v2 v2.9.0
 	golang.org/x/crypto v0.33.0
 	gorm.io/driver/postgres v1.5.9
 	gorm.io/gorm v1.25.11
@@ -21,6 +28,21 @@ require (
 
 require (
 	github.com/andybalholm/brotli v1.1.0 // indirect
+	github.com/aws/aws-sdk-go-v2/aws/protocol/eventstream v1.7.3 // indirect
+	github.com/aws/aws-sdk-go-v2/feature/ec2/imds v1.18.14 // indirect
+	github.com/aws/aws-sdk-go-v2/internal/configsources v1.4.14 // indirect
+	github.com/aws/aws-sdk-go-v2/internal/endpoints/v2 v2.7.14 // indirect
+	github.com/aws/aws-sdk-go-v2/internal/ini v1.8.4 // indirect
+	github.com/aws/aws-sdk-go-v2/internal/v4a v1.4.14 // indirect
+	github.com/aws/aws-sdk-go-v2/service/internal/accept-encoding v1.13.3 // indirect
+	github.com/aws/aws-sdk-go-v2/service/internal/checksum v1.9.5 // indirect
+	github.com/aws/aws-sdk-go-v2/service/internal/presigned-url v1.13.14 // indirect
+	github.com/aws/aws-sdk-go-v2/service/internal/s3shared v1.19.14 // indirect
+	github.com/aws/aws-sdk-go-v2/service/signin v1.0.2 // indirect
+	github.com/aws/aws-sdk-go-v2/service/sso v1.30.5 // indirect
+	github.com/aws/aws-sdk-go-v2/service/ssooidc v1.35.10 // indirect
+	github.com/aws/aws-sdk-go-v2/service/sts v1.41.2 // indirect
+	github.com/aws/smithy-go v1.23.2 // indirect
 	github.com/bytedance/sonic/loader v0.2.0 // indirect
 	github.com/cespare/xxhash/v2 v2.3.0 // indirect
 	github.com/cloudwego/base64x v0.1.4 // indirect
@@ -33,14 +55,12 @@ require (
 	github.com/go-playground/locales v0.14.1 // indirect
 	github.com/go-playground/universal-translator v0.18.1 // indirect
 	github.com/google/go-cmp v0.6.0 // indirect
-	github.com/google/uuid v1.6.0 // indirect
 	github.com/hashicorp/hcl v1.0.0 // indirect
 	github.com/jackc/chunkreader/v2 v2.0.1 // indirect
 	github.com/jackc/pgio v1.0.0 // indirect
 	github.com/jackc/pgpassfile v1.0.0 // indirect
 	github.com/jackc/pgproto3/v2 v2.3.2 // indirect
 	github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a // indirect
-	github.com/jackc/pgx/v5 v5.5.5 // indirect
 	github.com/jackc/puddle/v2 v2.2.1 // indirect
 	github.com/jinzhu/inflection v1.0.0 // indirect
 	github.com/jinzhu/now v1.1.5 // indirect
@@ -52,9 +72,12 @@ require (
 	github.com/mattn/go-isatty v0.0.20 // indirect
 	github.com/mattn/go-runewidth v0.0.16 // indirect
 	github.com/mitchellh/mapstructure v1.5.0 // indirect
+	github.com/mohae/deepcopy v0.0.0-20170929034955-c48cc78d4826 // indirect
 	github.com/pelletier/go-toml/v2 v2.2.2 // indirect
 	github.com/philhofer/fwd v1.1.2 // indirect
 	github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec // indirect
+	github.com/richardlehane/mscfb v1.0.4 // indirect
+	github.com/richardlehane/msoleps v1.0.4 // indirect
 	github.com/rivo/uniseg v0.4.7 // indirect
 	github.com/rogpeppe/go-internal v1.11.0 // indirect
 	github.com/sagikazarmark/locafero v0.4.0 // indirect
@@ -63,12 +86,15 @@ require (
 	github.com/spf13/afero v1.11.0 // indirect
 	github.com/spf13/cast v1.6.0 // indirect
 	github.com/spf13/pflag v1.0.5 // indirect
+	github.com/stretchr/testify v1.11.1 // indirect
 	github.com/subosito/gotenv v1.6.0 // indirect
 	github.com/tinylib/msgp v1.1.8 // indirect
 	github.com/twitchyliquid64/golang-asm v0.15.1 // indirect
 	github.com/valyala/bytebufferpool v1.0.0 // indirect
 	github.com/valyala/fasthttp v1.55.0 // indirect
 	github.com/valyala/tcplisten v1.0.0 // indirect
+	github.com/xuri/efp v0.0.0-20240408161823-9ad904a10d6d // indirect
+	github.com/xuri/nfp v0.0.0-20240318013403-ab9948c2c4a7 // indirect
 	go.uber.org/atomic v1.9.0 // indirect
 	go.uber.org/multierr v1.9.0 // indirect
 	golang.org/x/arch v0.0.0-20210923205945-b76863e36670 // indirect

go.sum

@@ -2,6 +2,44 @@ github.com/MicahParks/keyfunc/v2 v2.1.0 h1:6ZXKb9Rp6qp1bDbJefnG7cTH8yMN1IC/4nf+G
 github.com/MicahParks/keyfunc/v2 v2.1.0/go.mod h1:rW42fi+xgLJ2FRRXAfNx9ZA8WpD4OeE/yHVMteCkw9k=
 github.com/andybalholm/brotli v1.1.0 h1:eLKJA0d02Lf0mVpIDgYnqXcUn0GqVmEFny3VuID1U3M=
 github.com/andybalholm/brotli v1.1.0/go.mod h1:sms7XGricyQI9K10gOSf56VKKWS4oLer58Q+mhRPtnY=
+github.com/aws/aws-sdk-go-v2 v1.40.0 h1:/WMUA0kjhZExjOQN2z3oLALDREea1A7TobfuiBrKlwc=
+github.com/aws/aws-sdk-go-v2 v1.40.0/go.mod h1:c9pm7VwuW0UPxAEYGyTmyurVcNrbF6Rt/wixFqDhcjE=
+github.com/aws/aws-sdk-go-v2/aws/protocol/eventstream v1.7.3 h1:DHctwEM8P8iTXFxC/QK0MRjwEpWQeM9yzidCRjldUz0=
+github.com/aws/aws-sdk-go-v2/aws/protocol/eventstream v1.7.3/go.mod h1:xdCzcZEtnSTKVDOmUZs4l/j3pSV6rpo1WXl5ugNsL8Y=
+github.com/aws/aws-sdk-go-v2/config v1.32.2 h1:4liUsdEpUUPZs5WVapsJLx5NPmQhQdez7nYFcovrytk=
+github.com/aws/aws-sdk-go-v2/config v1.32.2/go.mod h1:l0hs06IFz1eCT+jTacU/qZtC33nvcnLADAPL/XyrkZI=
+github.com/aws/aws-sdk-go-v2/credentials v1.19.2 h1:qZry8VUyTK4VIo5aEdUcBjPZHL2v4FyQ3QEOaWcFLu4=
+github.com/aws/aws-sdk-go-v2/credentials v1.19.2/go.mod h1:YUqm5a1/kBnoK+/NY5WEiMocZihKSo15/tJdmdXnM5g=
+github.com/aws/aws-sdk-go-v2/feature/ec2/imds v1.18.14 h1:WZVR5DbDgxzA0BJeudId89Kmgy6DIU4ORpxwsVHz0qA=
+github.com/aws/aws-sdk-go-v2/feature/ec2/imds v1.18.14/go.mod h1:Dadl9QO0kHgbrH1GRqGiZdYtW5w+IXXaBNCHTIaheM4=
+github.com/aws/aws-sdk-go-v2/internal/configsources v1.4.14 h1:PZHqQACxYb8mYgms4RZbhZG0a7dPW06xOjmaH0EJC/I=
+github.com/aws/aws-sdk-go-v2/internal/configsources v1.4.14/go.mod h1:VymhrMJUWs69D8u0/lZ7jSB6WgaG/NqHi3gX0aYf6U0=
+github.com/aws/aws-sdk-go-v2/internal/endpoints/v2 v2.7.14 h1:bOS19y6zlJwagBfHxs0ESzr1XCOU2KXJCWcq3E2vfjY=
+github.com/aws/aws-sdk-go-v2/internal/endpoints/v2 v2.7.14/go.mod h1:1ipeGBMAxZ0xcTm6y6paC2C/J6f6OO7LBODV9afuAyM=
+github.com/aws/aws-sdk-go-v2/internal/ini v1.8.4 h1:WKuaxf++XKWlHWu9ECbMlha8WOEGm0OUEZqm4K/Gcfk=
+github.com/aws/aws-sdk-go-v2/internal/ini v1.8.4/go.mod h1:ZWy7j6v1vWGmPReu0iSGvRiise4YI5SkR3OHKTZ6Wuc=
+github.com/aws/aws-sdk-go-v2/internal/v4a v1.4.14 h1:ITi7qiDSv/mSGDSWNpZ4k4Ve0DQR6Ug2SJQ8zEHoDXg=
+github.com/aws/aws-sdk-go-v2/internal/v4a v1.4.14/go.mod h1:k1xtME53H1b6YpZt74YmwlONMWf4ecM+lut1WQLAF/U=
+github.com/aws/aws-sdk-go-v2/service/internal/accept-encoding v1.13.3 h1:x2Ibm/Af8Fi+BH+Hsn9TXGdT+hKbDd5XOTZxTMxDk7o=
+github.com/aws/aws-sdk-go-v2/service/internal/accept-encoding v1.13.3/go.mod h1:IW1jwyrQgMdhisceG8fQLmQIydcT/jWY21rFhzgaKwo=
+github.com/aws/aws-sdk-go-v2/service/internal/checksum v1.9.5 h1:Hjkh7kE6D81PgrHlE/m9gx+4TyyeLHuY8xJs7yXN5C4=
+github.com/aws/aws-sdk-go-v2/service/internal/checksum v1.9.5/go.mod h1:nPRXgyCfAurhyaTMoBMwRBYBhaHI4lNPAnJmjM0Tslc=
+github.com/aws/aws-sdk-go-v2/service/internal/presigned-url v1.13.14 h1:FIouAnCE46kyYqyhs0XEBDFFSREtdnr8HQuLPQPLCrY=
+github.com/aws/aws-sdk-go-v2/service/internal/presigned-url v1.13.14/go.mod h1:UTwDc5COa5+guonQU8qBikJo1ZJ4ln2r1MkF7Dqag1E=
+github.com/aws/aws-sdk-go-v2/service/internal/s3shared v1.19.14 h1:FzQE21lNtUor0Fb7QNgnEyiRCBlolLTX/Z1j65S7teM=
+github.com/aws/aws-sdk-go-v2/service/internal/s3shared v1.19.14/go.mod h1:s1ydyWG9pm3ZwmmYN21HKyG9WzAZhYVW85wMHs5FV6w=
+github.com/aws/aws-sdk-go-v2/service/s3 v1.92.1 h1:OgQy/+0+Kc3khtqiEOk23xQAglXi3Tj0y5doOxbi5tg=
+github.com/aws/aws-sdk-go-v2/service/s3 v1.92.1/go.mod h1:wYNqY3L02Z3IgRYxOBPH9I1zD9Cjh9hI5QOy/eOjQvw=
+github.com/aws/aws-sdk-go-v2/service/signin v1.0.2 h1:MxMBdKTYBjPQChlJhi4qlEueqB1p1KcbTEa7tD5aqPs=
+github.com/aws/aws-sdk-go-v2/service/signin v1.0.2/go.mod h1:iS6EPmNeqCsGo+xQmXv0jIMjyYtQfnwg36zl2FwEouk=
+github.com/aws/aws-sdk-go-v2/service/sso v1.30.5 h1:ksUT5KtgpZd3SAiFJNJ0AFEJVva3gjBmN7eXUZjzUwQ=
+github.com/aws/aws-sdk-go-v2/service/sso v1.30.5/go.mod h1:av+ArJpoYf3pgyrj6tcehSFW+y9/QvAY8kMooR9bZCw=
+github.com/aws/aws-sdk-go-v2/service/ssooidc v1.35.10 h1:GtsxyiF3Nd3JahRBJbxLCCdYW9ltGQYrFWg8XdkGDd8=
+github.com/aws/aws-sdk-go-v2/service/ssooidc v1.35.10/go.mod h1:/j67Z5XBVDx8nZVp9EuFM9/BS5dvBznbqILGuu73hug=
+github.com/aws/aws-sdk-go-v2/service/sts v1.41.2 h1:a5UTtD4mHBU3t0o6aHQZFJTNKVfxFWfPX7J0Lr7G+uY=
+github.com/aws/aws-sdk-go-v2/service/sts v1.41.2/go.mod h1:6TxbXoDSgBQ225Qd8Q+MbxUxUh6TtNKwbRt/EPS9xso=
+github.com/aws/smithy-go v1.23.2 h1:Crv0eatJUQhaManss33hS5r40CG3ZFH+21XSkqMrIUM=
+github.com/aws/smithy-go v1.23.2/go.mod h1:LEj2LM3rBRQJxPZTB4KuzZkaZYnZPnvgIhb4pu07mx0=
 github.com/bsm/ginkgo/v2 v2.12.0 h1:Ny8MWAHyOepLGlLKYmXG4IEkioBysk6GpaRTLC8zwWs=
 github.com/bsm/ginkgo/v2 v2.12.0/go.mod h1:SwYbGRRDovPVboqFv0tPTcG1sN61LM1Z4ARdbAV9g4c=
 github.com/bsm/gomega v1.27.10 h1:yeMWxP2pV2fG3FgAODIY8EiRE3dy0aeFYt4l7wh6yKA=
@@ -144,6 +182,8 @@ github.com/mattn/go-runewidth v0.0.16 h1:E5ScNMtiwvlvB5paMFdw9p4kSQzbXFikJ5SQO6T
 github.com/mattn/go-runewidth v0.0.16/go.mod h1:Jdepj2loyihRzMpdS35Xk/zdY8IAYHsh153qUoGf23w=
 github.com/mitchellh/mapstructure v1.5.0 h1:jeMsZIYE/09sWLaz43PL7Gy6RuMjD2eJVyuac5Z2hdY=
 github.com/mitchellh/mapstructure v1.5.0/go.mod h1:bFUtVrKA4DC2yAKiSyO/QUcy7e+RRV2QTWOzhPopBRo=
+github.com/mohae/deepcopy v0.0.0-20170929034955-c48cc78d4826 h1:RWengNIwukTxcDr9M+97sNutRR1RKhG96O6jWumTTnw=
+github.com/mohae/deepcopy v0.0.0-20170929034955-c48cc78d4826/go.mod h1:TaXosZuwdSHYgviHp1DAtfrULt5eUgsSMsZf+YrPgl8=
 github.com/pelletier/go-toml/v2 v2.2.2 h1:aYUidT7k73Pcl9nb2gScu7NSrKCSHIDE89b3+6Wq+LM=
 github.com/pelletier/go-toml/v2 v2.2.2/go.mod h1:1t835xjRzz80PqgE6HHgN2JOsmgYu/h4qDAS4n929Rs=
 github.com/philhofer/fwd v1.1.2 h1:bnDivRJ1EWPjUIRXV5KfORO897HTbpFAQddBdE8t7Gw=
@@ -157,6 +197,11 @@ github.com/redis/go-redis/v9 v9.14.0/go.mod h1:huWgSWd8mW6+m0VPhJjSSQ+d6Nh1VICQ6
 github.com/remyoudompheng/bigfft v0.0.0-20200410134404-eec4a21b6bb0/go.mod h1:qqbHyh8v60DhA7CoWK5oRCqLrMHRGoxYCSS9EjAz6Eo=
 github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec h1:W09IVJc94icq4NjY3clb7Lk8O1qJ8BdBEF8z0ibU0rE=
 github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec/go.mod h1:qqbHyh8v60DhA7CoWK5oRCqLrMHRGoxYCSS9EjAz6Eo=
+github.com/richardlehane/mscfb v1.0.4 h1:WULscsljNPConisD5hR0+OyZjwK46Pfyr6mPu5ZawpM=
+github.com/richardlehane/mscfb v1.0.4/go.mod h1:YzVpcZg9czvAuhk9T+a3avCpcFPMUWm7gK3DypaEsUk=
+github.com/richardlehane/msoleps v1.0.1/go.mod h1:BWev5JBpU9Ko2WAgmZEuiz4/u3ZYTKbjLycmwiWUfWg=
+github.com/richardlehane/msoleps v1.0.4 h1:WuESlvhX3gH2IHcd8UqyCuFY5yiq/GR/yqaSM/9/g00=
+github.com/richardlehane/msoleps v1.0.4/go.mod h1:BWev5JBpU9Ko2WAgmZEuiz4/u3ZYTKbjLycmwiWUfWg=
 github.com/rivo/uniseg v0.2.0/go.mod h1:J6wj4VEh+S6ZtnVlnTBMWIodfgj8LQOQFoIToxlJtxc=
 github.com/rivo/uniseg v0.4.7 h1:WUdvkW8uEhrYfLC4ZzdpI2ztxP1I582+49Oc5Mq64VQ=
 github.com/rivo/uniseg v0.4.7/go.mod h1:FN3SvrM+Zdj16jyLfmOkMNblXMcoc8DfTHruCPUcx88=
@@ -200,8 +245,9 @@ github.com/stretchr/testify v1.7.1/go.mod h1:6Fq8oRcR53rry900zMqJjRRixrwX3KX962/
 github.com/stretchr/testify v1.8.0/go.mod h1:yNjHg4UonilssWZ8iaSj1OCr/vHnekPRkoO+kdMU+MU=
 github.com/stretchr/testify v1.8.1/go.mod h1:w2LPCIKwWwSfY2zedu0+kehJoqGctiVI29o6fzry7u4=
 github.com/stretchr/testify v1.8.4/go.mod h1:sz/lmYIOXD/1dqDmKjjqLyZ2RngseejIcXlSw2iwfAo=
-github.com/stretchr/testify v1.9.0 h1:HtqpIVDClZ4nwg75+f6Lvsy/wHu+3BoSGCbBAcpTsTg=
 github.com/stretchr/testify v1.9.0/go.mod h1:r2ic/lqez/lEtzL7wO/rwa5dbSLXVDPFyf8C91i36aY=
+github.com/stretchr/testify v1.11.1 h1:7s2iGBzp5EwR7/aIZr8ao5+dra3wiQyKjjFuvgVKu7U=
+github.com/stretchr/testify v1.11.1/go.mod h1:wZwfW3scLgRK+23gO65QZefKpKQRnfz6sD981Nm4B6U=
 github.com/subosito/gotenv v1.6.0 h1:9NlTDc1FTs4qu0DDq7AEtTPNw6SVm7uBMsUCUjABIf8=
 github.com/subosito/gotenv v1.6.0/go.mod h1:Dk4QP5c2W3ibzajGcXpNraDfq2IrhjMIvMSWPKKo0FU=
 github.com/tinylib/msgp v1.1.8 h1:FCXC1xanKO4I8plpHGH2P7koL/RzZs12l/+r7vakfm0=
@@ -214,6 +260,12 @@ github.com/valyala/fasthttp v1.55.0 h1:Zkefzgt6a7+bVKHnu/YaYSOPfNYNisSVBo/unVCf8
 github.com/valyala/fasthttp v1.55.0/go.mod h1:NkY9JtkrpPKmgwV3HTaS2HWaJss9RSIsRVfcxxoHiOM=
 github.com/valyala/tcplisten v1.0.0 h1:rBHj/Xf+E1tRGZyWIWwJDiRY0zc1Js+CV5DqwacVSA8=
 github.com/valyala/tcplisten v1.0.0/go.mod h1:T0xQ8SeCZGxckz9qRXTfG43PvQ/mcWh7FwZEA7Ioqkc=
+github.com/xuri/efp v0.0.0-20240408161823-9ad904a10d6d h1:llb0neMWDQe87IzJLS4Ci7psK/lVsjIS2otl+1WyRyY=
+github.com/xuri/efp v0.0.0-20240408161823-9ad904a10d6d/go.mod h1:ybY/Jr0T0GTCnYjKqmdwxyxn2BQf2RcQIIvex5QldPI=
+github.com/xuri/excelize/v2 v2.9.0 h1:1tgOaEq92IOEumR1/JfYS/eR0KHOCsRv/rYXXh6YJQE=
+github.com/xuri/excelize/v2 v2.9.0/go.mod h1:uqey4QBZ9gdMeWApPLdhm9x+9o2lq4iVmjiLfBS5hdE=
+github.com/xuri/nfp v0.0.0-20240318013403-ab9948c2c4a7 h1:hPVCafDV85blFTabnqKgNhDCkJX25eik94Si9cTER4A=
+github.com/xuri/nfp v0.0.0-20240318013403-ab9948c2c4a7/go.mod h1:WwHg+CVyzlv/TX9xqBFXEZAuxOPxn2k1GNHwG41IIUQ=
 github.com/yuin/goldmark v1.4.13/go.mod h1:6yULJ656Px+3vBD8DxQVa3kxgyrAnzto9xy5taEt/CY=
 github.com/zenazn/goji v0.9.0/go.mod h1:7S9M489iMyHBNxwZnk9/EHS098H4/F6TATF2mIxtB1Q=
 go.uber.org/atomic v1.3.2/go.mod h1:gD2HeocX3+yG+ygLZcrzQJaqmWj9AIm7n08wl/qW/PE=
@@ -240,6 +292,8 @@ golang.org/x/crypto v0.33.0 h1:IOBPskki6Lysi0lo9qQvbxiQ+FvsCC/YWOecCHAixus=
 golang.org/x/crypto v0.33.0/go.mod h1:bVdXmD7IV/4GdElGPozy6U7lWdRXA4qyRVGJV57uQ5M=
 golang.org/x/exp v0.0.0-20230905200255-921286631fa9 h1:GoHiUyI/Tp2nVkLI2mCxVkOjsbSXD66ic0XW0js0R9g=
 golang.org/x/exp v0.0.0-20230905200255-921286631fa9/go.mod h1:S2oDrQGGwySpoQPVqRShND87VCbxmc6bL1Yd2oYrm6k=
+golang.org/x/image v0.18.0 h1:jGzIakQa/ZXI1I0Fxvaa9W7yP25TqT6cHIHn+6CqvSQ=
+golang.org/x/image v0.18.0/go.mod h1:4yyo5vMFQjVjUcVk4jEQcU9MGy/rulF5WvUILseCM2E=
 golang.org/x/mod v0.6.0-dev.0.20220419223038-86c51ed26bb4/go.mod h1:jJ57K6gSWd91VN4djpZkiMVwK6gcyfeH4XE8wZrZaV4=
 golang.org/x/mod v0.7.0/go.mod h1:iBbtSCu2XBx23ZKBPSOrRkjjQPZFPuis4dIYUhu/chs=
 golang.org/x/net v0.0.0-20190311183353-d8887717615a/go.mod h1:t9HGtf8HONx5eT2rtn7q6eTqICYqUVnKs3thJo3Qplg=

internal/capabilities/capabilities.go

@@ -1,44 +0,0 @@
-package capabilities
-
-import (
-	"strings"
-
-	recordings "gitlab.com/mbugroup/lti-api.git/internal/modules/production/recordings"
-)
-
-// FromPermissions returns a filtered map of capabilities that the frontend can use
-// to toggle features. Only permissions recognized by the application are exposed.
-func FromPermissions(perms []string) map[string]bool {
-	if len(perms) == 0 {
-		return nil
-	}
-
-	out := make(map[string]bool)
-	for _, perm := range perms {
-		if key, ok := normalizeAndAllow(perm); ok {
-			out[key] = true
-		}
-	}
-	if len(out) == 0 {
-		return nil
-	}
-	return out
-}
-
-func normalizeAndAllow(perm string) (string, bool) {
-	perm = strings.ToLower(strings.TrimSpace(perm))
-	if perm == "" {
-		return "", false
-	}
-	if _, ok := allowed[perm]; !ok {
-		return "", false
-	}
-	return perm, true
-}
-
-var allowed = map[string]struct{}{
-	recordings.PermissionRecordingRead:   {},
-	recordings.PermissionRecordingCreate: {},
-	recordings.PermissionRecordingUpdate: {},
-	recordings.PermissionRecordingDelete: {},
-}

internal/common/repository/common.approval.repository..go

@@ -13,6 +13,7 @@ type ApprovalRepository interface {
 	FindByTarget(ctx context.Context, workflow string, approvableID uint, modifier func(*gorm.DB) *gorm.DB) ([]entity.Approval, error)
 	LatestByTarget(ctx context.Context, workflow string, approvableID uint, modifier func(*gorm.DB) *gorm.DB) (*entity.Approval, error)
 	LatestByTargets(ctx context.Context, workflow string, approvableIDs []uint, modifier func(*gorm.DB) *gorm.DB) (map[uint]entity.Approval, error)
+	DeleteByTarget(ctx context.Context, workflow string, approvableID uint) error
 }
 
 type approvalRepositoryImpl struct {
@@ -83,8 +84,9 @@ func (r *approvalRepositoryImpl) LatestByTargets(
 	result := make(map[uint]entity.Approval, len(approvableIDs))
 
 	q := r.DB().WithContext(ctx).
+		Select("DISTINCT ON (approvable_id) *").
 		Where("approvable_type = ? AND approvable_id IN ?", workflow, approvableIDs).
-		Order("action_at DESC")
+		Order("approvable_id, action_at DESC")
 
 	if modifier != nil {
 		q = modifier(q)
@@ -104,3 +106,13 @@ func (r *approvalRepositoryImpl) LatestByTargets(
 
 	return result, nil
 }
+
+func (r *approvalRepositoryImpl) DeleteByTarget(
+	ctx context.Context,
+	workflow string,
+	approvableID uint,
+) error {
+	return r.DB().WithContext(ctx).
+		Where("approvable_type = ? AND approvable_id = ?", workflow, approvableID).
+		Delete(&entity.Approval{}).Error
+}

internal/common/repository/common.base.repository.go

@@ -187,10 +187,11 @@ func (r *BaseRepositoryImpl[T]) PatchOne(
 	updates map[string]any,
 	modifier func(*gorm.DB) *gorm.DB,
 ) error {
-	q := r.db.WithContext(ctx).Model(new(T)).Where("id = ?", id)
+	q := r.db.WithContext(ctx)
 	if modifier != nil {
 		q = modifier(q)
 	}
+	q = q.Model(new(T)).Where("id = ?", id)
 
 	result := q.Updates(updates)
 	if result.Error != nil {

internal/common/repository/common.document.repository.go

@@ -0,0 +1,62 @@
+package repository
+
+import (
+	"context"
+
+	entity "gitlab.com/mbugroup/lti-api.git/internal/entities"
+	"gorm.io/gorm"
+)
+
+type DocumentRepository interface {
+	BaseRepository[entity.Document]
+	ListByTarget(ctx context.Context, documentableType string, documentableID uint64, modifier func(*gorm.DB) *gorm.DB) ([]entity.Document, error)
+	DeleteByTarget(ctx context.Context, documentableType string, documentableID uint64, modifier func(*gorm.DB) *gorm.DB) error
+}
+
+type documentRepositoryImpl struct {
+	*BaseRepositoryImpl[entity.Document]
+}
+
+func NewDocumentRepository(db *gorm.DB) DocumentRepository {
+	return &documentRepositoryImpl{
+		BaseRepositoryImpl: NewBaseRepository[entity.Document](db),
+	}
+}
+
+func (r *documentRepositoryImpl) ListByTarget(
+	ctx context.Context,
+	documentableType string,
+	documentableID uint64,
+	modifier func(*gorm.DB) *gorm.DB,
+) ([]entity.Document, error) {
+	var documents []entity.Document
+
+	q := r.DB().WithContext(ctx).
+		Where("documentable_type = ? AND documentable_id = ?", documentableType, documentableID)
+
+	if modifier != nil {
+		q = modifier(q)
+	}
+
+	if err := q.Order("created_at ASC").Find(&documents).Error; err != nil {
+		return nil, err
+	}
+
+	return documents, nil
+}
+
+func (r *documentRepositoryImpl) DeleteByTarget(
+	ctx context.Context,
+	documentableType string,
+	documentableID uint64,
+	modifier func(*gorm.DB) *gorm.DB,
+) error {
+	q := r.DB().WithContext(ctx).
+		Where("documentable_type = ? AND documentable_id = ?", documentableType, documentableID)
+
+	if modifier != nil {
+		q = modifier(q)
+	}
+
+	return q.Delete(&entity.Document{}).Error
+}

internal/common/repository/common.exists.repository.go

@@ -2,6 +2,7 @@ package repository
 
 import (
 	"context"
+	"errors"
 	"fmt"
 
 	"gorm.io/gorm"
@@ -9,45 +10,59 @@ import (
 
 // Exists reports whether a record with the given ID exists for type T.
 func Exists[T any](ctx context.Context, db *gorm.DB, id uint) (bool, error) {
-	var count int64
-	if err := db.WithContext(ctx).
+	var marker int
+	err := db.WithContext(ctx).
 		Model(new(T)).
+		Select("1").
 		Where("id = ?", id).
-		Count(&count).Error; err != nil {
+		Limit(1).
+		Take(&marker).Error
+	if errors.Is(err, gorm.ErrRecordNotFound) {
+		return false, nil
+	}
+	if err != nil {
 		return false, err
 	}
-	return count > 0, nil
+	return true, nil
 }
 
 func ExistsByName[T any](ctx context.Context, db *gorm.DB, name string, excludeID *uint) (bool, error) {
-	var count int64
 	q := db.WithContext(ctx).
 		Model(new(T)).
+		Select("1").
 		Where("name = ?", name).
 		Where("deleted_at IS NULL")
 	if excludeID != nil {
 		q = q.Where("id <> ?", *excludeID)
 	}
-	if err := q.Count(&count).Error; err != nil {
+	var marker int
+	if err := q.Limit(1).Take(&marker).Error; err != nil {
+		if errors.Is(err, gorm.ErrRecordNotFound) {
+			return false, nil
+		}
 		return false, err
 	}
-	return count > 0, nil
+	return true, nil
 }
 
 func ExistsByField[T any](ctx context.Context, db *gorm.DB, field string, value any, excludeID *uint) (bool, error) {
 	if field == "" {
 		return false, fmt.Errorf("field is required")
 	}
-	var count int64
 	q := db.WithContext(ctx).
 		Model(new(T)).
+		Select("1").
 		Where(fmt.Sprintf("%s = ?", field), value).
 		Where("deleted_at IS NULL")
 	if excludeID != nil {
 		q = q.Where("id <> ?", *excludeID)
 	}
-	if err := q.Count(&count).Error; err != nil {
+	var marker int
+	if err := q.Limit(1).Take(&marker).Error; err != nil {
+		if errors.Is(err, gorm.ErrRecordNotFound) {
+			return false, nil
+		}
 		return false, err
 	}
-	return count > 0, nil
+	return true, nil
 }

internal/common/repository/common.stock_allocation.repository.go

@@ -0,0 +1,76 @@
+package repository
+
+import (
+	"context"
+	"time"
+
+	entity "gitlab.com/mbugroup/lti-api.git/internal/entities"
+	"gorm.io/gorm"
+)
+
+type StockAllocationRepository interface {
+	BaseRepository[entity.StockAllocation]
+	FindActiveByUsable(ctx context.Context, usableType string, usableID uint, modifier func(*gorm.DB) *gorm.DB) ([]entity.StockAllocation, error)
+	ReleaseByUsable(ctx context.Context, usableType string, usableID uint, note *string, modifier func(*gorm.DB) *gorm.DB) error
+}
+
+type StockAllocationRepositoryImpl struct {
+	*BaseRepositoryImpl[entity.StockAllocation]
+}
+
+func NewStockAllocationRepository(db *gorm.DB) StockAllocationRepository {
+	return &StockAllocationRepositoryImpl{
+		BaseRepositoryImpl: NewBaseRepository[entity.StockAllocation](db),
+	}
+}
+
+func (r *StockAllocationRepositoryImpl) FindActiveByUsable(
+	ctx context.Context,
+	usableType string,
+	usableID uint,
+	modifier func(*gorm.DB) *gorm.DB,
+) ([]entity.StockAllocation, error) {
+	var allocations []entity.StockAllocation
+
+	q := r.DB().WithContext(ctx).
+		Where("usable_type = ? AND usable_id = ? AND status = ?", usableType, usableID, entity.StockAllocationStatusActive)
+
+	if modifier != nil {
+		q = modifier(q)
+	}
+
+	if err := q.Order("created_at ASC").Find(&allocations).Error; err != nil {
+		return nil, err
+	}
+
+	return allocations, nil
+}
+
+func (r *StockAllocationRepositoryImpl) ReleaseByUsable(
+	ctx context.Context,
+	usableType string,
+	usableID uint,
+	note *string,
+	modifier func(*gorm.DB) *gorm.DB,
+) error {
+	now := time.Now()
+
+	updates := map[string]any{
+		"status":      entity.StockAllocationStatusReleased,
+		"released_at": now,
+	}
+	if note != nil {
+		updates["note"] = *note
+	}
+
+	baseDB := r.DB()
+	if modifier != nil {
+		baseDB = modifier(baseDB)
+	}
+
+	q := baseDB.WithContext(ctx).
+		Model(&entity.StockAllocation{}).
+		Where("usable_type = ? AND usable_id = ? AND status = ?", usableType, usableID, entity.StockAllocationStatusActive)
+
+	return q.Updates(updates).Error
+}

internal/common/service/common.closing.service.go

@@ -0,0 +1,120 @@
+package service
+
+import (
+	"context"
+	"errors"
+	"fmt"
+
+	productWarehouseRepo "gitlab.com/mbugroup/lti-api.git/internal/modules/inventory/product-warehouses/repositories"
+	warehouseRepo "gitlab.com/mbugroup/lti-api.git/internal/modules/master/warehouses/repositories"
+	projectFlockKandangRepo "gitlab.com/mbugroup/lti-api.git/internal/modules/production/project_flocks/repositories"
+
+	"github.com/gofiber/fiber/v2"
+	"gorm.io/gorm"
+)
+
+// Dipakai untuk semua module yang butuh cek:
+// "PW ini → warehouse → kandang → project_flock_kandang sudah closing atau belum"
+func EnsureProjectFlockNotClosedForProductWarehouses(
+	ctx context.Context,
+	db *gorm.DB,
+	productWarehouseIDs []uint,
+) error {
+	if len(productWarehouseIDs) == 0 {
+		return nil
+	}
+
+	pwRepo := productWarehouseRepo.NewProductWarehouseRepository(db)
+	wRepo := warehouseRepo.NewWarehouseRepository(db)
+	pfkRepo := projectFlockKandangRepo.NewProjectFlockKandangRepository(db)
+
+	seenPW := make(map[uint]struct{})
+	seenKandang := make(map[uint]struct{})
+
+	for _, pwID := range productWarehouseIDs {
+		if pwID == 0 {
+			continue
+		}
+		if _, ok := seenPW[pwID]; ok {
+			continue
+		}
+		seenPW[pwID] = struct{}{}
+
+		pw, err := pwRepo.GetByID(ctx, pwID, nil)
+		if err != nil {
+			if errors.Is(err, gorm.ErrRecordNotFound) {
+				return fiber.NewError(fiber.StatusBadRequest,
+					fmt.Sprintf("Product warehouse %d tidak ditemukan", pwID))
+			}
+			return fiber.NewError(fiber.StatusInternalServerError, "Failed to validate product warehouse")
+		}
+
+		wh, err := wRepo.GetByID(ctx, uint(pw.WarehouseId), nil)
+		if err != nil {
+			if errors.Is(err, gorm.ErrRecordNotFound) {
+				return fiber.NewError(fiber.StatusBadRequest,
+					fmt.Sprintf("Warehouse %d tidak ditemukan", pw.WarehouseId))
+			}
+			return fiber.NewError(fiber.StatusInternalServerError, "Failed to validate warehouse")
+		}
+
+		// Warehouse tanpa kandang → bukan kandang produksi → skip
+		if wh.KandangId == nil || *wh.KandangId == 0 {
+			continue
+		}
+
+		kandangID := uint(*wh.KandangId)
+		if _, ok := seenKandang[kandangID]; ok {
+			continue
+		}
+		seenKandang[kandangID] = struct{}{}
+
+		pfk, err := pfkRepo.GetActiveByKandangID(ctx, kandangID)
+		if err != nil {
+			if errors.Is(err, gorm.ErrRecordNotFound) {
+				// nggak ada project aktif untuk kandang ini → aman
+				continue
+			}
+			return fiber.NewError(fiber.StatusInternalServerError, "Failed to validate project flock")
+		}
+		// INTI RULE: kalau aktif tapi sudah punya ClosedAt → anggap "project sudah closing"
+		if pfk != nil && pfk.ClosedAt != nil {
+			return fiber.NewError(fiber.StatusBadRequest, "Project sudah closing")
+		}
+	}
+
+	return nil
+}
+
+func EnsureProjectFlockNotClosedByProjectFlockKandangID(
+	ctx context.Context,
+	db *gorm.DB,
+	pfkIDs []uint,
+) error {
+	pfkRepo := projectFlockKandangRepo.NewProjectFlockKandangRepository(db)
+
+	seen := make(map[uint]struct{})
+	for _, id := range pfkIDs {
+		if id == 0 {
+			continue
+		}
+		if _, ok := seen[id]; ok {
+			continue
+		}
+		seen[id] = struct{}{}
+
+		pfk, err := pfkRepo.GetByID(ctx, id)
+		if err != nil {
+			if errors.Is(err, gorm.ErrRecordNotFound) {
+				return fiber.NewError(fiber.StatusBadRequest,
+					fmt.Sprintf("Project flock kandang %d tidak ditemukan", id))
+			}
+			return fiber.NewError(fiber.StatusInternalServerError, "Failed to validate project flock")
+		}
+
+		if pfk.ClosedAt != nil {
+			return fiber.NewError(fiber.StatusBadRequest, "Project sudah closing")
+		}
+	}
+	return nil
+}

internal/common/service/common.document.service.go

@@ -0,0 +1,474 @@
+package service
+
+import (
+	"context"
+	"errors"
+	"fmt"
+	"mime"
+	"mime/multipart"
+	"net/url"
+	"path/filepath"
+	"strings"
+	"time"
+
+	commonRepo "gitlab.com/mbugroup/lti-api.git/internal/common/repository"
+	"gitlab.com/mbugroup/lti-api.git/internal/config"
+	entity "gitlab.com/mbugroup/lti-api.git/internal/entities"
+	"gitlab.com/mbugroup/lti-api.git/internal/utils"
+
+	"github.com/google/uuid"
+)
+
+const (
+	defaultDocumentPathLimit = 50
+	defaultDocumentKeyPrefix = "docs"
+	maxDocumentNameLength    = 50
+)
+
+type DocumentService interface {
+	UploadDocuments(ctx context.Context, req DocumentUploadRequest) ([]DocumentUploadResult, error)
+	ListByTarget(ctx context.Context, documentableType string, documentableID uint64) ([]entity.Document, error)
+	DeleteDocuments(ctx context.Context, ids []uint, removeFromStorage bool) error
+	DeleteByTarget(ctx context.Context, documentableType string, documentableID uint64, removeFromStorage bool) error
+	PublicURL(document entity.Document) string
+	PresignURL(ctx context.Context, document entity.Document, expires time.Duration) (string, error)
+}
+
+type DocumentUploadRequest struct {
+	DocumentableType string
+	DocumentableID   uint64
+	CreatedBy        *uint
+	Files            []DocumentFile
+}
+
+type DocumentFile struct {
+	File  *multipart.FileHeader
+	Type  string
+	Index *int
+}
+
+type DocumentUploadResult struct {
+	Document entity.Document
+	URL      string
+	Index    *int
+}
+
+type DocumentServiceOption func(*documentService)
+
+type documentService struct {
+	repo          commonRepo.DocumentRepository
+	storage       DocumentStorage
+	keyPrefix     string
+	maxPathLength int
+}
+
+func NewDocumentService(repo commonRepo.DocumentRepository, storage DocumentStorage, opts ...DocumentServiceOption) DocumentService {
+	svc := &documentService{
+		repo:          repo,
+		storage:       storage,
+		keyPrefix:     defaultDocumentKeyPrefix,
+		maxPathLength: defaultDocumentPathLimit,
+	}
+
+	for _, opt := range opts {
+		opt(svc)
+	}
+
+	return svc
+}
+
+func NewDocumentServiceFromConfig(ctx context.Context, repo commonRepo.DocumentRepository) (DocumentService, error) {
+	if repo == nil {
+		return nil, errors.New("document repository is required")
+	}
+	if strings.TrimSpace(config.S3Bucket) == "" {
+		return nil, errors.New("S3_BUCKET is not configured")
+	}
+
+	storage, err := NewS3DocumentStorage(ctx, S3DocumentStorageConfig{
+		Region:         config.S3Region,
+		Bucket:         config.S3Bucket,
+		AccessKey:      config.S3AccessKey,
+		SecretKey:      config.S3SecretKey,
+		Endpoint:       config.S3Endpoint,
+		BaseURL:        config.S3PublicBaseURL,
+		ForcePathStyle: config.S3ForcePathStyle,
+	})
+	if err != nil {
+		return nil, err
+	}
+
+	prefix := config.S3DocumentKeyPrefix
+	if prefix == "" {
+		prefix = defaultDocumentKeyPrefix
+	}
+
+	return NewDocumentService(
+		repo,
+		storage,
+		WithDocumentKeyPrefix(prefix),
+		WithDocumentPathLimit(defaultDocumentPathLimit),
+	), nil
+}
+
+func WithDocumentKeyPrefix(prefix string) DocumentServiceOption {
+	return func(svc *documentService) {
+		prefix = strings.Trim(prefix, "/")
+		if prefix == "" {
+			prefix = defaultDocumentKeyPrefix
+		}
+		svc.keyPrefix = prefix
+	}
+}
+
+func WithDocumentPathLimit(limit int) DocumentServiceOption {
+	return func(svc *documentService) {
+		if limit > 0 {
+			svc.maxPathLength = limit
+		}
+	}
+}
+
+func (s *documentService) UploadDocuments(ctx context.Context, req DocumentUploadRequest) ([]DocumentUploadResult, error) {
+	if s.repo == nil {
+		return nil, errors.New("document repository not configured")
+	}
+	if s.storage == nil {
+		return nil, errors.New("document storage not configured")
+	}
+
+	documentableType := strings.ToUpper(strings.TrimSpace(req.DocumentableType))
+	if documentableType == "" {
+		return nil, errors.New("documentable type is required")
+	}
+	if req.DocumentableID == 0 {
+		return nil, errors.New("documentable id is required")
+	}
+	if len(req.Files) == 0 {
+		return nil, errors.New("no files to upload")
+	}
+
+	var createdBy *uint
+	if req.CreatedBy != nil && *req.CreatedBy != 0 {
+		idCopy := *req.CreatedBy
+		createdBy = &idCopy
+	}
+
+	results := make([]DocumentUploadResult, 0, len(req.Files))
+	createdDocs := make([]entity.Document, 0, len(req.Files))
+
+	for _, file := range req.Files {
+		if file.File == nil {
+			return nil, errors.New("file header is required")
+		}
+
+		originalName := sanitizeDocumentName(file.File.Filename)
+		contentType := detectContentType(file.File, originalName)
+		ext := detectExtension(file.File.Filename, contentType)
+		key, err := s.generateObjectKey(ext)
+		if err != nil {
+			s.rollbackDocuments(ctx, createdDocs)
+			return nil, err
+		}
+
+		reader, err := file.File.Open()
+		if err != nil {
+			s.rollbackDocuments(ctx, createdDocs)
+			return nil, err
+		}
+		uploadRes, err := s.storage.Upload(ctx, key, reader, file.File.Size, contentType)
+		_ = reader.Close()
+		if err != nil {
+			s.rollbackDocuments(ctx, createdDocs)
+			return nil, err
+		}
+
+		docType := resolveDocumentType(file.Type, documentableType)
+		doc := entity.Document{
+			DocumentableType: documentableType,
+			DocumentableId:   req.DocumentableID,
+			Type:             docType,
+			Path:             uploadRes.Key,
+			Name:             originalName,
+			Ext:              strings.TrimPrefix(ext, "."),
+			Size:             float64(file.File.Size),
+			CreatedBy:        createdBy,
+		}
+
+		if err := s.repo.CreateOne(ctx, &doc, nil); err != nil {
+			_ = s.storage.Delete(ctx, uploadRes.Key)
+			s.rollbackDocuments(ctx, createdDocs)
+			return nil, err
+		}
+
+		createdDocs = append(createdDocs, doc)
+		results = append(results, DocumentUploadResult{
+			Document: doc,
+			URL:      uploadRes.URL,
+			Index:    cloneIndex(file.Index),
+		})
+	}
+
+	return results, nil
+}
+
+func (s *documentService) ListByTarget(ctx context.Context, documentableType string, documentableID uint64) ([]entity.Document, error) {
+	if s.repo == nil {
+		return nil, errors.New("document repository not configured")
+	}
+
+	documentableType = strings.ToUpper(strings.TrimSpace(documentableType))
+	if documentableType == "" {
+		return nil, errors.New("documentable type is required")
+	}
+	if documentableID == 0 {
+		return nil, errors.New("documentable id is required")
+	}
+
+	return s.repo.ListByTarget(ctx, documentableType, documentableID, nil)
+}
+
+func (s *documentService) DeleteDocuments(ctx context.Context, ids []uint, removeFromStorage bool) error {
+	if s.repo == nil {
+		return errors.New("document repository not configured")
+	}
+	if len(ids) == 0 {
+		return nil
+	}
+
+	docs, err := s.repo.GetByIDs(ctx, ids, nil)
+	if err != nil {
+		return err
+	}
+
+	for _, doc := range docs {
+		if err := s.repo.DeleteOne(ctx, doc.Id); err != nil {
+			return err
+		}
+		if removeFromStorage && s.storage != nil {
+			if err := s.storage.Delete(ctx, doc.Path); err != nil {
+				utils.Log.WithError(err).Warnf("failed to delete document object %s", doc.Path)
+			}
+		}
+	}
+
+	return nil
+}
+
+func (s *documentService) DeleteByTarget(ctx context.Context, documentableType string, documentableID uint64, removeFromStorage bool) error {
+	if s.repo == nil {
+		return errors.New("document repository not configured")
+	}
+
+	documentableType = strings.ToUpper(strings.TrimSpace(documentableType))
+	if documentableType == "" || documentableID == 0 {
+		return errors.New("documentable type and id are required")
+	}
+
+	var docs []entity.Document
+	if removeFromStorage && s.storage != nil {
+		var err error
+		docs, err = s.repo.ListByTarget(ctx, documentableType, documentableID, nil)
+		if err != nil {
+			return err
+		}
+	}
+
+	if err := s.repo.DeleteByTarget(ctx, documentableType, documentableID, nil); err != nil {
+		return err
+	}
+
+	if removeFromStorage && len(docs) > 0 {
+		for _, doc := range docs {
+			if err := s.storage.Delete(ctx, doc.Path); err != nil {
+				utils.Log.WithError(err).Warnf("failed to delete document object %s", doc.Path)
+			}
+		}
+	}
+
+	return nil
+}
+
+func (s *documentService) PublicURL(document entity.Document) string {
+	if s.storage == nil || strings.TrimSpace(document.Path) == "" {
+		return ""
+	}
+	return s.storage.URL(document.Path)
+}
+
+func (s *documentService) PresignURL(ctx context.Context, document entity.Document, expires time.Duration) (string, error) {
+	if s.storage == nil {
+		return "", errors.New("document storage not configured")
+	}
+	if strings.TrimSpace(document.Path) == "" {
+		return "", errors.New("document path is required")
+	}
+	return s.storage.PresignURL(ctx, document.Path, expires)
+}
+
+// ResolveDocumentURL normalizes a stored path or URL into a presigned URL.
+func ResolveDocumentURL(
+	ctx context.Context,
+	svc DocumentService,
+	rawPath string,
+	expires time.Duration,
+) (string, error) {
+	if svc == nil {
+		return "", nil
+	}
+
+	rawPath = strings.TrimSpace(rawPath)
+	if rawPath == "" {
+		return "", nil
+	}
+
+	key := rawPath
+	lower := strings.ToLower(rawPath)
+	if strings.HasPrefix(lower, "http://") || strings.HasPrefix(lower, "https://") {
+		key = extractS3KeyFromURL(rawPath)
+		if key == "" {
+			return "", nil
+		}
+	}
+
+	return svc.PresignURL(ctx, entity.Document{Path: key}, expires)
+}
+
+func extractS3KeyFromURL(raw string) string {
+	parsed, err := url.Parse(strings.TrimSpace(raw))
+	if err != nil {
+		return ""
+	}
+	path := strings.TrimPrefix(parsed.Path, "/")
+	if path == "" {
+		return ""
+	}
+
+	host := strings.ToLower(strings.TrimSpace(parsed.Host))
+	if strings.HasPrefix(host, "s3.") || strings.HasPrefix(host, "s3-") {
+		parts := strings.SplitN(path, "/", 2)
+		if len(parts) == 2 {
+			return parts[1]
+		}
+		return ""
+	}
+
+	return path
+}
+
+func (s *documentService) generateObjectKey(ext string) (string, error) {
+	normalizedExt := strings.TrimSpace(ext)
+	if normalizedExt != "" && !strings.HasPrefix(normalizedExt, ".") {
+		normalizedExt = "." + normalizedExt
+	}
+
+	u := uuid.New().String()
+	key := fmt.Sprintf("%s/%s%s", strings.Trim(s.keyPrefix, "/"), u, normalizedExt)
+	if s.keyPrefix == "" {
+		key = fmt.Sprintf("%s%s", u, normalizedExt)
+	}
+
+	if len(key) > s.maxPathLength {
+		key = fmt.Sprintf("%s%s", u, normalizedExt)
+	}
+
+	if len(key) > s.maxPathLength {
+		return "", fmt.Errorf("object key exceeds maximum length (%d)", s.maxPathLength)
+	}
+
+	return key, nil
+}
+
+func (s *documentService) rollbackDocuments(ctx context.Context, docs []entity.Document) {
+	if len(docs) == 0 {
+		return
+	}
+
+	for i := len(docs) - 1; i >= 0; i-- {
+		doc := docs[i]
+		if s.repo != nil && doc.Id != 0 {
+			if err := s.repo.DeleteOne(ctx, doc.Id); err != nil {
+				utils.Log.WithError(err).Warnf("failed to rollback document #%d", doc.Id)
+			}
+		}
+		if s.storage != nil && strings.TrimSpace(doc.Path) != "" {
+			if err := s.storage.Delete(ctx, doc.Path); err != nil {
+				utils.Log.WithError(err).Warnf("failed to rollback document object %s", doc.Path)
+			}
+		}
+	}
+}
+
+func sanitizeDocumentName(name string) string {
+	name = filepath.Base(strings.TrimSpace(name))
+	if name == "." || name == "" {
+		name = "document"
+	}
+	name = strings.Map(func(r rune) rune {
+		if r < 32 {
+			return -1
+		}
+		switch r {
+		case '\\', '/', ':', '*', '?', '"', '<', '>', '|':
+			return '-'
+		default:
+			return r
+		}
+	}, name)
+
+	if len(name) > maxDocumentNameLength {
+		runes := []rune(name)
+		if len(runes) > maxDocumentNameLength {
+			name = string(runes[:maxDocumentNameLength])
+		}
+	}
+	return name
+}
+
+func detectExtension(filename, contentType string) string {
+	ext := strings.ToLower(strings.TrimSpace(filepath.Ext(filename)))
+	if ext == "" && contentType != "" {
+		if exts, _ := mime.ExtensionsByType(contentType); len(exts) > 0 {
+			ext = exts[0]
+		}
+	}
+	if ext == "" {
+		return ".bin"
+	}
+	if !strings.HasPrefix(ext, ".") {
+		ext = "." + ext
+	}
+	return ext
+}
+
+func detectContentType(file *multipart.FileHeader, filename string) string {
+	if file == nil {
+		return "application/octet-stream"
+	}
+	contentType := strings.TrimSpace(file.Header.Get("Content-Type"))
+	if contentType != "" {
+		return contentType
+	}
+	if ext := filepath.Ext(filename); ext != "" {
+		if guess := mime.TypeByExtension(ext); guess != "" {
+			return guess
+		}
+	}
+	return "application/octet-stream"
+}
+
+func resolveDocumentType(fileType, fallback string) string {
+	value := strings.ToUpper(strings.TrimSpace(fileType))
+	if value == "" {
+		return fallback
+	}
+	return value
+}
+
+func cloneIndex(index *int) *int {
+	if index == nil {
+		return nil
+	}
+	value := *index
+	return &value
+}

internal/common/service/common.document.service_test.go

@@ -0,0 +1,101 @@
+package service
+
+import (
+	"bytes"
+	"context"
+	"mime/multipart"
+	"net/http/httptest"
+	"strings"
+	"testing"
+
+	commonRepo "gitlab.com/mbugroup/lti-api.git/internal/common/repository"
+	"gitlab.com/mbugroup/lti-api.git/internal/config"
+	"gitlab.com/mbugroup/lti-api.git/internal/database"
+	entity "gitlab.com/mbugroup/lti-api.git/internal/entities"
+	"gorm.io/gorm"
+)
+
+func TestDocumentServiceUpload(t *testing.T) {
+	if strings.TrimSpace(config.S3Bucket) == "" {
+		t.Fatal("S3 bucket is not configured; set S3_* env vars to run this test")
+	}
+
+	ctx := context.Background()
+	db := setupDocumentTestDB(t)
+	repo := commonRepo.NewDocumentRepository(db)
+
+	svc, err := NewDocumentServiceFromConfig(ctx, repo)
+	if err != nil {
+		t.Fatalf("failed to create document service from config: %v", err)
+	}
+
+	file := newTestFileHeader(t, "integration-proof.txt", "text/plain", []byte("document integration test"))
+	userID := uint(100)
+
+	results, err := svc.UploadDocuments(ctx, DocumentUploadRequest{
+		DocumentableType: "INVENTORY_TRANSFER",
+		DocumentableID:   99,
+		CreatedBy:        &userID,
+		Files: []DocumentFile{
+			{File: file, Type: "integration"},
+		},
+	})
+	if err != nil {
+		t.Fatalf("upload to S3 failed: %v", err)
+	}
+	if len(results) != 1 {
+		t.Fatalf("expected 1 uploaded document, got %d", len(results))
+	}
+
+	doc := results[0].Document
+	if doc.Path == "" {
+		t.Fatalf("expected non-empty storage path")
+	}
+	if results[0].URL == "" {
+		t.Fatalf("expected public URL for uploaded document")
+	}
+
+	t.Logf("uploaded document #%d to %s (path=%s)", doc.Id, results[0].URL, doc.Path)
+}
+
+func setupDocumentTestDB(t *testing.T) *gorm.DB {
+	t.Helper()
+	if strings.TrimSpace(config.DBHost) == "" || strings.TrimSpace(config.DBName) == "" {
+		t.Fatal("database configuration missing; ensure DB_HOST and DB_NAME are set")
+	}
+	db := database.Connect(config.DBHost, config.DBName)
+	if db == nil {
+		t.Fatal("failed to create database connection")
+	}
+	if err := db.AutoMigrate(&entity.Document{}); err != nil {
+		t.Fatalf("failed to migrate document table: %v", err)
+	}
+	return db
+}
+
+func newTestFileHeader(t *testing.T, filename, contentType string, data []byte) *multipart.FileHeader {
+	t.Helper()
+
+	body := &bytes.Buffer{}
+	writer := multipart.NewWriter(body)
+	part, err := writer.CreateFormFile("documents", filename)
+	if err != nil {
+		t.Fatalf("failed to create form file: %v", err)
+	}
+	if _, err := part.Write(data); err != nil {
+		t.Fatalf("failed to write file data: %v", err)
+	}
+	if err := writer.Close(); err != nil {
+		t.Fatalf("failed to close writer: %v", err)
+	}
+
+	req := httptest.NewRequest("POST", "http://example.com/upload", body)
+	req.Header.Set("Content-Type", writer.FormDataContentType())
+
+	_, fileHeader, err := req.FormFile("documents")
+	if err != nil {
+		t.Fatalf("failed to parse form file: %v", err)
+	}
+	fileHeader.Header.Set("Content-Type", contentType)
+	return fileHeader
+}

internal/common/service/common.document.storage.go

@@ -0,0 +1,185 @@
+package service
+
+import (
+	"context"
+	"errors"
+	"fmt"
+	"io"
+	"strings"
+	"time"
+
+	"github.com/aws/aws-sdk-go-v2/aws"
+	awsconfig "github.com/aws/aws-sdk-go-v2/config"
+	"github.com/aws/aws-sdk-go-v2/credentials"
+	"github.com/aws/aws-sdk-go-v2/service/s3"
+)
+
+type DocumentStorage interface {
+	Upload(ctx context.Context, key string, body io.Reader, size int64, contentType string) (DocumentStorageUploadResult, error)
+	Delete(ctx context.Context, key string) error
+	URL(key string) string
+	PresignURL(ctx context.Context, key string, expires time.Duration) (string, error)
+}
+
+type DocumentStorageUploadResult struct {
+	Key  string
+	URL  string
+	ETag string
+}
+
+type S3DocumentStorageConfig struct {
+	Region         string
+	Bucket         string
+	AccessKey      string
+	SecretKey      string
+	Endpoint       string
+	BaseURL        string
+	ForcePathStyle bool
+}
+
+type s3DocumentStorage struct {
+	client        *s3.Client
+	presignClient *s3.PresignClient
+	bucket        string
+	base          string
+}
+
+func NewS3DocumentStorage(ctx context.Context, cfg S3DocumentStorageConfig) (DocumentStorage, error) {
+	bucket := strings.TrimSpace(cfg.Bucket)
+	if bucket == "" {
+		return nil, errors.New("s3 bucket is required")
+	}
+	region := strings.TrimSpace(cfg.Region)
+	if region == "" {
+		region = "us-east-1"
+	}
+
+	options := []func(*awsconfig.LoadOptions) error{
+		awsconfig.WithRegion(region),
+	}
+
+	endpoint := strings.TrimSpace(cfg.Endpoint)
+	if endpoint != "" {
+		resolver := aws.EndpointResolverWithOptionsFunc(func(service, region string, _ ...interface{}) (aws.Endpoint, error) {
+			if service == s3.ServiceID {
+				return aws.Endpoint{
+					URL:               endpoint,
+					SigningRegion:     region,
+					HostnameImmutable: true,
+				}, nil
+			}
+			return aws.Endpoint{}, &aws.EndpointNotFoundError{}
+		})
+		options = append(options, awsconfig.WithEndpointResolverWithOptions(resolver))
+	}
+
+	accessKey := strings.TrimSpace(cfg.AccessKey)
+	secretKey := strings.TrimSpace(cfg.SecretKey)
+	if accessKey != "" && secretKey != "" {
+		options = append(options, awsconfig.WithCredentialsProvider(
+			credentials.NewStaticCredentialsProvider(accessKey, secretKey, ""),
+		))
+	}
+
+	awsCfg, err := awsconfig.LoadDefaultConfig(ctx, options...)
+	if err != nil {
+		return nil, err
+	}
+
+	client := s3.NewFromConfig(awsCfg, func(o *s3.Options) {
+		o.UsePathStyle = cfg.ForcePathStyle
+	})
+	presignClient := s3.NewPresignClient(client)
+
+	baseURL := strings.TrimSuffix(strings.TrimSpace(cfg.BaseURL), "/")
+	if baseURL == "" {
+		if endpoint != "" {
+			baseURL = fmt.Sprintf("%s/%s", strings.TrimSuffix(endpoint, "/"), bucket)
+		} else {
+			baseURL = fmt.Sprintf("https://%s.s3.%s.amazonaws.com", bucket, region)
+		}
+	}
+
+	return &s3DocumentStorage{
+		client:        client,
+		presignClient: presignClient,
+		bucket:        bucket,
+		base:          baseURL,
+	}, nil
+}
+
+func (s *s3DocumentStorage) Upload(ctx context.Context, key string, body io.Reader, size int64, contentType string) (DocumentStorageUploadResult, error) {
+	if strings.TrimSpace(key) == "" {
+		return DocumentStorageUploadResult{}, errors.New("storage key is required")
+	}
+	if size < 0 {
+		size = 0
+	}
+	input := &s3.PutObjectInput{
+		Bucket: aws.String(s.bucket),
+		Key:    aws.String(key),
+		Body:   body,
+	}
+	input.ContentLength = aws.Int64(size)
+	if ct := strings.TrimSpace(contentType); ct != "" {
+		input.ContentType = aws.String(ct)
+	}
+
+	out, err := s.client.PutObject(ctx, input)
+	if err != nil {
+		return DocumentStorageUploadResult{}, err
+	}
+
+	var etag string
+	if out.ETag != nil {
+		etag = strings.Trim(*out.ETag, "\"")
+	}
+
+	return DocumentStorageUploadResult{
+		Key:  key,
+		URL:  s.URL(key),
+		ETag: etag,
+	}, nil
+}
+
+func (s *s3DocumentStorage) Delete(ctx context.Context, key string) error {
+	if strings.TrimSpace(key) == "" {
+		return nil
+	}
+	_, err := s.client.DeleteObject(ctx, &s3.DeleteObjectInput{
+		Bucket: aws.String(s.bucket),
+		Key:    aws.String(key),
+	})
+	return err
+}
+
+func (s *s3DocumentStorage) URL(key string) string {
+	key = strings.TrimPrefix(strings.TrimSpace(key), "/")
+	if key == "" {
+		return s.base
+	}
+	if s.base == "" {
+		return key
+	}
+	return fmt.Sprintf("%s/%s", s.base, key)
+}
+
+func (s *s3DocumentStorage) PresignURL(ctx context.Context, key string, expires time.Duration) (string, error) {
+	key = strings.TrimPrefix(strings.TrimSpace(key), "/")
+	if key == "" {
+		return "", errors.New("storage key is required")
+	}
+	if expires <= 0 {
+		expires = 15 * time.Minute
+	}
+
+	out, err := s.presignClient.PresignGetObject(ctx, &s3.GetObjectInput{
+		Bucket: aws.String(s.bucket),
+		Key:    aws.String(key),
+	}, s3.WithPresignExpires(expires))
+	if err != nil {
+		return "", err
+	}
+
+	return out.URL, nil
+}

internal/common/service/common.fifo.service.go

@@ -0,0 +1,851 @@
+package service
+
+import (
+	"context"
+	"errors"
+	"fmt"
+	"math"
+	"sort"
+	"strings"
+	"time"
+
+	"github.com/sirupsen/logrus"
+	commonRepo "gitlab.com/mbugroup/lti-api.git/internal/common/repository"
+	"gitlab.com/mbugroup/lti-api.git/internal/entities"
+	productWarehouseRepo "gitlab.com/mbugroup/lti-api.git/internal/modules/inventory/product-warehouses/repositories"
+	"gitlab.com/mbugroup/lti-api.git/internal/utils/fifo"
+	"gorm.io/gorm"
+	"gorm.io/gorm/clause"
+)
+
+type FifoService interface {
+	RegisterStockable(cfg fifo.StockableConfig) error
+	RegisterUsable(cfg fifo.UsableConfig) error
+
+	Replenish(ctx context.Context, req StockReplenishRequest) (*StockReplenishResult, error)
+	Consume(ctx context.Context, req StockConsumeRequest) (*StockConsumeResult, error)
+	ReleaseUsage(ctx context.Context, req StockReleaseRequest) error
+}
+
+type fifoService struct {
+	db                     *gorm.DB
+	logger                 *logrus.Logger
+	allocations            commonRepo.StockAllocationRepository
+	productWarehouseRepo   productWarehouseRepo.ProductWarehouseRepository
+	defaultOrderBy         []string
+	pendingBatchPerUsable  int
+	maxLotsPerStockable    int
+	defaultAllocationNotes string
+}
+
+func NewFifoService(
+	db *gorm.DB,
+	allocations commonRepo.StockAllocationRepository,
+	productWarehouseRepo productWarehouseRepo.ProductWarehouseRepository,
+	logger *logrus.Logger,
+) FifoService {
+	if logger == nil {
+		logger = logrus.StandardLogger()
+	}
+	return &fifoService{
+		db:                    db,
+		logger:                logger,
+		allocations:           allocations,
+		productWarehouseRepo:  productWarehouseRepo,
+		defaultOrderBy:        []string{"created_at ASC", "id ASC"},
+		pendingBatchPerUsable: 25,
+		maxLotsPerStockable:   50,
+	}
+}
+
+func (s *fifoService) withTransaction(
+	ctx context.Context,
+	tx *gorm.DB,
+	fn func(*gorm.DB) error,
+) error {
+	if tx != nil {
+		return fn(tx.WithContext(ctx))
+	}
+	return s.db.WithContext(ctx).Transaction(func(inner *gorm.DB) error {
+		return fn(inner)
+	})
+}
+
+func (s *fifoService) txOrDB(tx, db *gorm.DB) *gorm.DB {
+	if tx != nil {
+		return tx
+	}
+	return db
+}
+
+func (s *fifoService) RegisterStockable(cfg fifo.StockableConfig) error {
+	return fifo.RegisterStockable(cfg)
+}
+
+func (s *fifoService) RegisterUsable(cfg fifo.UsableConfig) error {
+	return fifo.RegisterUsable(cfg)
+}
+
+type StockReplenishRequest struct {
+	StockableKey       fifo.StockableKey
+	StockableID        uint
+	ProductWarehouseID uint
+	Quantity           float64
+	Note               *string
+	Tx                 *gorm.DB
+}
+
+type PendingResolution struct {
+	UsableKey fifo.UsableKey
+	UsableID  uint
+	Quantity  float64
+}
+
+type StockReplenishResult struct {
+	AddedQuantity    float64
+	PendingResolved  []PendingResolution
+	RemainingPending float64
+}
+
+type StockConsumeRequest struct {
+	UsableKey          fifo.UsableKey
+	UsableID           uint
+	ProductWarehouseID uint
+	Quantity           float64
+	AllowPending       bool
+	Note               *string
+	Tx                 *gorm.DB
+}
+
+type AllocationDetail struct {
+	StockableKey fifo.StockableKey
+	StockableID  uint
+	Quantity     float64
+}
+
+type StockConsumeResult struct {
+	RequestedQuantity float64
+	UsageQuantity     float64
+	PendingQuantity   float64
+	AddedAllocations  []AllocationDetail
+	ReleasedQuantity  float64
+}
+
+type StockReleaseRequest struct {
+	UsableKey fifo.UsableKey
+	UsableID  uint
+	Reason    *string
+	Tx        *gorm.DB
+}
+
+func (s *fifoService) Replenish(ctx context.Context, req StockReplenishRequest) (*StockReplenishResult, error) {
+	if req.StockableID == 0 || strings.TrimSpace(req.StockableKey.String()) == "" {
+		return nil, errors.New("stockable key and id are required")
+	}
+	if req.ProductWarehouseID == 0 {
+		return nil, errors.New("product warehouse id is required")
+	}
+	if req.Quantity <= 0 {
+		return nil, errors.New("quantity must be greater than zero")
+	}
+
+	cfg, ok := fifo.Stockable(req.StockableKey)
+	if !ok {
+		return nil, fmt.Errorf("stockable %q is not registered", req.StockableKey)
+	}
+
+	result := &StockReplenishResult{
+		AddedQuantity: req.Quantity,
+	}
+
+	err := s.withTransaction(ctx, req.Tx, func(tx *gorm.DB) error {
+		if err := s.incrementStockableQty(ctx, tx, cfg, req.StockableID, req.Quantity); err != nil {
+			return err
+		}
+
+		if err := s.productWarehouseRepo.AdjustQuantities(ctx, map[uint]float64{
+			req.ProductWarehouseID: req.Quantity,
+		}, func(db *gorm.DB) *gorm.DB {
+			return s.txOrDB(tx, db)
+		}); err != nil {
+			return err
+		}
+
+		resolved, err := s.resolvePendingForWarehouse(ctx, tx, req.ProductWarehouseID)
+		if err != nil {
+			return err
+		}
+		result.PendingResolved = resolved
+		return nil
+	})
+	if err != nil {
+		return nil, err
+	}
+
+	return result, nil
+}
+
+func (s *fifoService) Consume(ctx context.Context, req StockConsumeRequest) (*StockConsumeResult, error) {
+	if req.UsableID == 0 || strings.TrimSpace(req.UsableKey.String()) == "" {
+		return nil, errors.New("usable key and id are required")
+	}
+	if req.Quantity < 0 {
+		return nil, errors.New("quantity must be zero or greater")
+	}
+	cfg, ok := fifo.Usable(req.UsableKey)
+	if !ok {
+		return nil, fmt.Errorf("usable %q is not registered", req.UsableKey)
+	}
+
+	result := &StockConsumeResult{
+		RequestedQuantity: req.Quantity,
+	}
+
+	err := s.withTransaction(ctx, req.Tx, func(tx *gorm.DB) error {
+		ctxRow, err := s.loadUsableContext(ctx, tx, cfg, req.UsableID)
+		if err != nil {
+			return err
+		}
+
+		productWarehouseID := ctxRow.ProductWarehouseID
+		if productWarehouseID == 0 {
+			return fmt.Errorf("usable %q (id: %d) has no product warehouse reference", req.UsableKey, req.UsableID)
+		}
+		if req.ProductWarehouseID != 0 && req.ProductWarehouseID != productWarehouseID {
+			return fmt.Errorf("usable %q (id: %d) references product warehouse %d but %d was provided", req.UsableKey, req.UsableID, productWarehouseID, req.ProductWarehouseID)
+		}
+
+		currentUsage := ctxRow.UsageQty
+		currentPending := ctxRow.PendingQty
+		currentTotal := currentUsage + currentPending
+		delta := req.Quantity - currentTotal
+		var (
+			usageDelta     float64
+			pendingDelta   float64
+			addedAlloc     []AllocationDetail
+			releasedAmount float64
+		)
+
+		switch {
+		case delta > 0:
+
+			var excludedStockables []fifo.StockableKey
+			if cfg.ExcludedStockables != nil {
+				excludedStockables = cfg.ExcludedStockables
+			}
+
+			allocationRes, err := s.allocateFromStock(ctx, tx, productWarehouseID, req.UsableKey, req.UsableID, delta, excludedStockables)
+			if err != nil {
+				return err
+			}
+			if allocationRes.pending > 0 && !req.AllowPending {
+				return fmt.Errorf("insufficient stock: requested %.3f, allocated %.3f", req.Quantity, currentUsage+allocationRes.allocated)
+			}
+
+			usageDelta += allocationRes.allocated
+			pendingDelta += allocationRes.pending
+			addedAlloc = allocationRes.allocations
+
+			if allocationRes.allocated > 0 {
+				if err := s.productWarehouseRepo.AdjustQuantities(ctx, map[uint]float64{
+					productWarehouseID: -allocationRes.allocated,
+				}, func(db *gorm.DB) *gorm.DB {
+					return s.txOrDB(tx, db)
+				}); err != nil {
+					return err
+				}
+			}
+		case delta < 0:
+			reductionTarget := -delta
+
+			if currentPending > 0 {
+				pendingReduction := math.Min(currentPending, reductionTarget)
+				if pendingReduction > 0 {
+					pendingDelta -= pendingReduction
+					reductionTarget -= pendingReduction
+				}
+			}
+
+			if reductionTarget > 0 {
+				released, err := s.releaseUsagePortion(ctx, tx, req.UsableKey, req.UsableID, reductionTarget)
+				if err != nil {
+					return err
+				}
+				if released+1e-6 < reductionTarget {
+					return fmt.Errorf("unable to release %.3f from usable %d, only %.3f available", reductionTarget, req.UsableID, released)
+				}
+				usageDelta -= released
+				releasedAmount = released
+			}
+		default:
+			// no change
+		}
+
+		if err := s.applyUsableDeltas(ctx, tx, cfg, req.UsableID, usageDelta, pendingDelta); err != nil {
+			return err
+		}
+
+		result.AddedAllocations = addedAlloc
+		result.ReleasedQuantity = releasedAmount
+		result.UsageQuantity = currentUsage + usageDelta
+		result.PendingQuantity = currentPending + pendingDelta
+		return nil
+	})
+	if err != nil {
+		return nil, err
+	}
+
+	return result, nil
+}
+
+func (s *fifoService) ReleaseUsage(ctx context.Context, req StockReleaseRequest) error {
+	if req.UsableID == 0 || strings.TrimSpace(req.UsableKey.String()) == "" {
+		return errors.New("usable key and id are required")
+	}
+	return s.withTransaction(ctx, req.Tx, func(tx *gorm.DB) error {
+		cfg, ok := fifo.Usable(req.UsableKey)
+		if !ok {
+			return fmt.Errorf("usable %q is not registered", req.UsableKey)
+		}
+
+		ctxRow, err := s.loadUsableContext(ctx, tx, cfg, req.UsableID)
+		if err != nil {
+			return err
+		}
+		var usageDelta, pendingDelta float64
+		if ctxRow.UsageQty > 0 {
+			if _, err := s.releaseUsagePortion(ctx, tx, req.UsableKey, req.UsableID, ctxRow.UsageQty); err != nil {
+				return err
+			}
+			usageDelta -= ctxRow.UsageQty
+		}
+		if ctxRow.PendingQty > 0 {
+			pendingDelta -= ctxRow.PendingQty
+		}
+
+		if err := s.applyUsableDeltas(ctx, tx, cfg, req.UsableID, usageDelta, pendingDelta); err != nil {
+			return err
+		}
+
+		return s.allocations.ReleaseByUsable(ctx, req.UsableKey.String(), req.UsableID, req.Reason, func(db *gorm.DB) *gorm.DB {
+			return s.txOrDB(tx, db)
+		})
+	})
+}
+
+// --- helpers ---
+
+type usableContextRow struct {
+	ProductWarehouseID uint
+	UsageQty           float64
+	PendingQty         float64
+}
+
+func (s *fifoService) loadUsableContext(ctx context.Context, tx *gorm.DB, cfg fifo.UsableConfig, id uint) (*usableContextRow, error) {
+	var row usableContextRow
+
+	query := tx.Table(cfg.Table).
+		Select(fmt.Sprintf("%s AS product_warehouse_id, COALESCE(%s,0) AS usage_qty, COALESCE(%s,0) AS pending_qty", cfg.Columns.ProductWarehouseID, cfg.Columns.UsageQuantity, cfg.Columns.PendingQuantity)).
+		Where(fmt.Sprintf("%s = ?", cfg.Columns.ID), id).
+		Clauses(clause.Locking{Strength: "UPDATE"})
+
+	if cfg.Scope != nil {
+		query = cfg.Scope(query)
+	}
+
+	if err := query.Take(&row).Error; err != nil {
+		if errors.Is(err, gorm.ErrRecordNotFound) {
+			return nil, fmt.Errorf("usable record %d not found", id)
+		}
+		return nil, err
+	}
+
+	return &row, nil
+}
+
+func (s *fifoService) incrementStockableQty(ctx context.Context, tx *gorm.DB, cfg fifo.StockableConfig, id uint, qty float64) error {
+	column := cfg.Columns.TotalQuantity
+
+	query := tx.Table(cfg.Table).
+		Where(fmt.Sprintf("%s = ?", cfg.Columns.ID), id)
+	if cfg.Scope != nil {
+		query = cfg.Scope(query)
+	}
+
+	updates := map[string]any{
+		column: gorm.Expr(fmt.Sprintf("COALESCE(%s,0) + ?", column), qty),
+	}
+	if cfg.Columns.TotalUsedQuantity != "" {
+		updates[cfg.Columns.TotalUsedQuantity] = gorm.Expr(fmt.Sprintf("COALESCE(%s,0)", cfg.Columns.TotalUsedQuantity))
+	}
+
+	return query.Updates(updates).Error
+}
+
+func (s *fifoService) incrementStockableUsage(ctx context.Context, tx *gorm.DB, cfg fifo.StockableConfig, id uint, qty float64) error {
+	if qty == 0 {
+		return nil
+	}
+	column := cfg.Columns.TotalUsedQuantity
+	query := tx.Table(cfg.Table).
+		Where(fmt.Sprintf("%s = ?", cfg.Columns.ID), id)
+	if cfg.Scope != nil {
+		query = cfg.Scope(query)
+	}
+
+	return query.Update(column, gorm.Expr(fmt.Sprintf("COALESCE(%s,0) + ?", column), qty)).Error
+}
+
+type allocationOutcome struct {
+	allocated   float64
+	pending     float64
+	allocations []AllocationDetail
+}
+
+type stockLot struct {
+	StockableKey fifo.StockableKey
+	RecordID     uint
+	AvailableQty float64
+	CreatedAt    time.Time
+}
+
+func (s *fifoService) allocateFromStock(
+	ctx context.Context,
+	tx *gorm.DB,
+	productWarehouseID uint,
+	usableKey fifo.UsableKey,
+	usableID uint,
+	requestQty float64,
+	excludedStockables []fifo.StockableKey,
+) (*allocationOutcome, error) {
+	lots, err := s.fetchStockLots(ctx, tx, productWarehouseID, excludedStockables)
+	if err != nil {
+		return nil, err
+	}
+	if len(lots) == 0 {
+		return &allocationOutcome{pending: requestQty}, nil
+	}
+
+	var (
+		remaining           = requestQty
+		applied             float64
+		allocations         []*entities.StockAllocation
+		allocationSummaries []AllocationDetail
+		usageAdjustments    = make(map[fifo.StockableKey]map[uint]float64)
+	)
+
+	for _, lot := range lots {
+		if remaining <= 0 {
+			break
+		}
+		if lot.AvailableQty <= 0 {
+			continue
+		}
+
+		portion := lot.AvailableQty
+		if portion > remaining {
+			portion = remaining
+		}
+
+		applied += portion
+		remaining -= portion
+
+		allocationSummaries = append(allocationSummaries, AllocationDetail{
+			StockableKey: lot.StockableKey,
+			StockableID:  lot.RecordID,
+			Quantity:     portion,
+		})
+
+		allocations = append(allocations, &entities.StockAllocation{
+			ProductWarehouseId: productWarehouseID,
+			StockableType:      lot.StockableKey.String(),
+			StockableId:        lot.RecordID,
+			UsableType:         usableKey.String(),
+			UsableId:           usableID,
+			Qty:                portion,
+			Status:             entities.StockAllocationStatusActive,
+		})
+
+		if _, ok := usageAdjustments[lot.StockableKey]; !ok {
+			usageAdjustments[lot.StockableKey] = make(map[uint]float64)
+		}
+		usageAdjustments[lot.StockableKey][lot.RecordID] += portion
+	}
+
+	if len(allocations) > 0 {
+		if err := s.allocations.CreateMany(ctx, allocations, func(db *gorm.DB) *gorm.DB {
+			return s.txOrDB(tx, db)
+		}); err != nil {
+			return nil, err
+		}
+
+		for key, deltas := range usageAdjustments {
+			cfg, ok := fifo.Stockable(key)
+			if !ok {
+				continue
+			}
+			for id, qty := range deltas {
+				if err := s.incrementStockableUsage(ctx, tx, cfg, id, qty); err != nil {
+					return nil, err
+				}
+			}
+		}
+	}
+
+	return &allocationOutcome{
+		allocated:   applied,
+		pending:     remaining,
+		allocations: allocationSummaries,
+	}, nil
+}
+
+func (s *fifoService) fetchStockLots(ctx context.Context, tx *gorm.DB, productWarehouseID uint, excludedStockables []fifo.StockableKey) ([]stockLot, error) {
+	configs := fifo.Stockables()
+	if len(configs) == 0 {
+		return nil, nil
+	}
+
+	// Create exclusion set for faster lookup
+	excludedSet := make(map[fifo.StockableKey]bool)
+	for _, key := range excludedStockables {
+		excludedSet[key] = true
+	}
+
+	var lots []stockLot
+	for key, cfg := range configs {
+		// Skip excluded stockables
+		if excludedSet[key] {
+			continue
+		}
+
+		usesNumericTime := cfg.Columns.CreatedAt == cfg.Columns.ID
+
+		var selectStmt string
+		if usesNumericTime {
+
+			selectStmt = fmt.Sprintf(
+				"%s AS id, %s AS available_qty, '1970-01-01 00:00:00 UTC'::timestamp AS created_at",
+				cfg.Columns.ID,
+				fmt.Sprintf("%s - COALESCE(%s,0)", cfg.Columns.TotalQuantity, cfg.Columns.TotalUsedQuantity),
+			)
+		} else {
+			selectStmt = fmt.Sprintf(
+				"%s AS id, %s AS available_qty, %s AS created_at",
+				cfg.Columns.ID,
+				fmt.Sprintf("%s - COALESCE(%s,0)", cfg.Columns.TotalQuantity, cfg.Columns.TotalUsedQuantity),
+				cfg.Columns.CreatedAt,
+			)
+		}
+
+		var rows []struct {
+			ID           uint
+			AvailableQty float64
+			CreatedAt    time.Time
+		}
+
+		query := tx.Table(cfg.Table).
+			Select(selectStmt).
+			Where(fmt.Sprintf("%s = ?", cfg.Columns.ProductWarehouseID), productWarehouseID).
+			Where(fmt.Sprintf("%s > %s", cfg.Columns.TotalQuantity, cfg.Columns.TotalUsedQuantity))
+
+		if cfg.Scope != nil {
+			query = cfg.Scope(query)
+		}
+
+		for _, order := range s.orderClauses(cfg.OrderBy) {
+			query = query.Order(order)
+		}
+		query = query.Limit(s.maxLotsPerStockable)
+
+		if err := query.Find(&rows).Error; err != nil {
+			return nil, err
+		}
+
+		for _, row := range rows {
+			if row.AvailableQty <= 0 {
+				continue
+			}
+			lots = append(lots, stockLot{
+				StockableKey: key,
+				RecordID:     row.ID,
+				AvailableQty: row.AvailableQty,
+				CreatedAt:    row.CreatedAt,
+			})
+		}
+	}
+
+	if len(lots) == 0 {
+		return nil, nil
+	}
+
+	sort.SliceStable(lots, func(i, j int) bool {
+		if lots[i].CreatedAt.Equal(lots[j].CreatedAt) {
+			return lots[i].RecordID < lots[j].RecordID
+		}
+		return lots[i].CreatedAt.Before(lots[j].CreatedAt)
+	})
+
+	return lots, nil
+}
+
+func (s *fifoService) applyUsableDeltas(ctx context.Context, tx *gorm.DB, cfg fifo.UsableConfig, id uint, usageDelta, pendingDelta float64) error {
+	if usageDelta == 0 && pendingDelta == 0 {
+		return nil
+	}
+
+	updates := map[string]any{}
+	if usageDelta != 0 {
+		updates[cfg.Columns.UsageQuantity] = gorm.Expr(fmt.Sprintf("COALESCE(%s,0) + ?", cfg.Columns.UsageQuantity), usageDelta)
+	}
+	if pendingDelta != 0 {
+		updates[cfg.Columns.PendingQuantity] = gorm.Expr(fmt.Sprintf("COALESCE(%s,0) + ?", cfg.Columns.PendingQuantity), pendingDelta)
+	}
+
+	query := tx.Table(cfg.Table).Where(fmt.Sprintf("%s = ?", cfg.Columns.ID), id)
+	if cfg.Scope != nil {
+		query = cfg.Scope(query)
+	}
+
+	return query.Updates(updates).Error
+}
+
+type pendingCandidate struct {
+	UsableKey fifo.UsableKey
+	Config    fifo.UsableConfig
+	UsableID  uint
+	Pending   float64
+	CreatedAt time.Time
+}
+
+func (s *fifoService) resolvePendingForWarehouse(ctx context.Context, tx *gorm.DB, productWarehouseID uint) ([]PendingResolution, error) {
+	candidates, err := s.fetchPendingCandidates(ctx, tx, productWarehouseID)
+	if err != nil {
+		return nil, err
+	}
+	if len(candidates) == 0 {
+		return nil, nil
+	}
+
+	var resolutions []PendingResolution
+
+	for _, candidate := range candidates {
+		if candidate.Pending <= 0 {
+			continue
+		}
+
+		// Get excluded stockables from candidate usable config
+		var excludedStockables []fifo.StockableKey
+		if candidate.Config.ExcludedStockables != nil {
+			excludedStockables = candidate.Config.ExcludedStockables
+		}
+
+		outcome, err := s.allocateFromStock(ctx, tx, productWarehouseID, candidate.UsableKey, candidate.UsableID, candidate.Pending, excludedStockables)
+		if err != nil {
+			return nil, err
+		}
+		if outcome.allocated <= 0 {
+			break
+		}
+
+		if err := s.applyUsableDeltas(ctx, tx, candidate.Config, candidate.UsableID, outcome.allocated, -outcome.allocated); err != nil {
+			return nil, err
+		}
+
+		if err := s.productWarehouseRepo.AdjustQuantities(ctx, map[uint]float64{
+			productWarehouseID: -outcome.allocated,
+		}, func(db *gorm.DB) *gorm.DB {
+			return s.txOrDB(tx, db)
+		}); err != nil {
+			return nil, err
+		}
+
+		resolutions = append(resolutions, PendingResolution{
+			UsableKey: candidate.UsableKey,
+			UsableID:  candidate.UsableID,
+			Quantity:  outcome.allocated,
+		})
+
+		if outcome.pending > 0 {
+			// No more stock available for this warehouse at the moment.
+			break
+		}
+	}
+
+	return resolutions, nil
+}
+
+func (s *fifoService) releaseUsagePortion(
+	ctx context.Context,
+	tx *gorm.DB,
+	usableKey fifo.UsableKey,
+	usableID uint,
+	target float64,
+) (float64, error) {
+	if target <= 0 {
+		return 0, nil
+	}
+
+	allocations, err := s.allocations.FindActiveByUsable(ctx, usableKey.String(), usableID, func(db *gorm.DB) *gorm.DB {
+		target := s.txOrDB(tx, db)
+		return target.Clauses(clause.Locking{Strength: "UPDATE"})
+	})
+	if err != nil {
+		return 0, err
+	}
+	if len(allocations) == 0 {
+		return 0, nil
+	}
+
+	var (
+		remaining            = target
+		totalReleased        float64
+		warehouseAdjustments = make(map[uint]float64)
+		stockableAdjustments = make(map[fifo.StockableKey]map[uint]float64)
+	)
+
+	now := time.Now()
+
+	for i := len(allocations) - 1; i >= 0 && remaining > 0; i-- {
+		allocation := allocations[i]
+		releaseAmt := allocation.Qty
+		if releaseAmt > remaining {
+			releaseAmt = remaining
+		}
+
+		remaining -= releaseAmt
+		totalReleased += releaseAmt
+		warehouseAdjustments[allocation.ProductWarehouseId] += releaseAmt
+
+		key := fifo.StockableKey(allocation.StockableType)
+		if _, ok := stockableAdjustments[key]; !ok {
+			stockableAdjustments[key] = make(map[uint]float64)
+		}
+		stockableAdjustments[key][allocation.StockableId] += releaseAmt
+
+		if releaseAmt == allocation.Qty {
+			if err := s.allocations.PatchOne(ctx, allocation.Id, map[string]any{
+				"status":      entities.StockAllocationStatusReleased,
+				"released_at": now,
+			}, func(db *gorm.DB) *gorm.DB {
+				return s.txOrDB(tx, db)
+			}); err != nil {
+				return 0, err
+			}
+		} else {
+			if err := s.allocations.PatchOne(ctx, allocation.Id, map[string]any{
+				"qty": allocation.Qty - releaseAmt,
+			}, func(db *gorm.DB) *gorm.DB {
+				return s.txOrDB(tx, db)
+			}); err != nil {
+				return 0, err
+			}
+		}
+	}
+
+	if totalReleased == 0 {
+		return 0, nil
+	}
+
+	for key, deltas := range stockableAdjustments {
+		cfg, ok := fifo.Stockable(key)
+		if !ok {
+			continue
+		}
+		for id, qty := range deltas {
+			if err := s.incrementStockableUsage(ctx, tx, cfg, id, -qty); err != nil {
+				return 0, err
+			}
+		}
+	}
+
+	if len(warehouseAdjustments) > 0 {
+		if err := s.productWarehouseRepo.AdjustQuantities(ctx, warehouseAdjustments, func(db *gorm.DB) *gorm.DB {
+			return s.txOrDB(tx, db)
+		}); err != nil {
+			return 0, err
+		}
+
+		for warehouseID := range warehouseAdjustments {
+			if _, err := s.resolvePendingForWarehouse(ctx, tx, warehouseID); err != nil {
+				return 0, err
+			}
+		}
+	}
+
+	return totalReleased, nil
+}
+
+func (s *fifoService) fetchPendingCandidates(ctx context.Context, tx *gorm.DB, productWarehouseID uint) ([]pendingCandidate, error) {
+	configs := fifo.Usables()
+	if len(configs) == 0 {
+		return nil, nil
+	}
+
+	var candidates []pendingCandidate
+
+	for key, cfg := range configs {
+		selectStmt := fmt.Sprintf(
+			"%s AS id, %s AS pending_qty, %s AS created_at",
+			cfg.Columns.ID,
+			cfg.Columns.PendingQuantity,
+			cfg.Columns.CreatedAt,
+		)
+
+		var rows []struct {
+			ID        uint
+			Pending   float64
+			CreatedAt time.Time
+		}
+
+		query := tx.Table(cfg.Table).
+			Select(selectStmt).
+			Where(fmt.Sprintf("%s = ?", cfg.Columns.ProductWarehouseID), productWarehouseID).
+			Where(fmt.Sprintf("%s > 0", cfg.Columns.PendingQuantity)).
+			Limit(s.pendingBatchPerUsable)
+
+		if cfg.Scope != nil {
+			query = cfg.Scope(query)
+		}
+
+		for _, order := range s.orderClauses(cfg.OrderBy) {
+			query = query.Order(order)
+		}
+
+		if err := query.Find(&rows).Error; err != nil {
+			return nil, err
+		}
+
+		for _, row := range rows {
+			if row.Pending <= 0 {
+				continue
+			}
+			candidates = append(candidates, pendingCandidate{
+				UsableKey: key,
+				Config:    cfg,
+				UsableID:  row.ID,
+				Pending:   row.Pending,
+				CreatedAt: row.CreatedAt,
+			})
+		}
+	}
+
+	if len(candidates) == 0 {
+		return nil, nil
+	}
+
+	sort.SliceStable(candidates, func(i, j int) bool {
+		if candidates[i].CreatedAt.Equal(candidates[j].CreatedAt) {
+			return candidates[i].UsableID < candidates[j].UsableID
+		}
+		return candidates[i].CreatedAt.Before(candidates[j].CreatedAt)
+	})
+
+	return candidates, nil
+}
+
+func (s *fifoService) orderClauses(custom []string) []string {
+	if len(custom) > 0 {
+		return custom
+	}
+	return s.defaultOrderBy
+}

internal/common/validation/validation.go

@@ -3,6 +3,8 @@ package validation
 import (
 	"errors"
 	"fmt"
+	"reflect"
+	"strings"
 
 	"github.com/go-playground/validator/v10"
 )
@@ -21,34 +23,41 @@ var customMessages = map[string]string{
 	"alphanum": "Field %s must contain only alphanumeric characters",
 	"oneof":    "Invalid value for field %s",
 	"password": "Field %s must be at least 8 characters, contain uppercase, lowercase, number, and special character",
+	"gt":       "Invalid %s, must be greater than %s",
 }
 
-func CustomErrorMessages(err error) map[string]string {
+func CustomErrorMessages(err error) (string, map[string]string) {
 	var validationErrors validator.ValidationErrors
 	if errors.As(err, &validationErrors) {
 		return generateErrorMessages(validationErrors)
 	}
-	return nil
+	return "", nil
 }
 
-func generateErrorMessages(validationErrors validator.ValidationErrors) map[string]string {
+func generateErrorMessages(validationErrors validator.ValidationErrors) (string, map[string]string) {
 	errorsMap := make(map[string]string)
-	for _, err := range validationErrors {
+	var firstMessage string
+	for i, err := range validationErrors {
 		fieldName := err.StructNamespace()
 		tag := err.Tag()
 
 		customMessage := customMessages[tag]
+		var msg string
 		if customMessage != "" {
-			errorsMap[fieldName] = formatErrorMessage(customMessage, err, tag)
+			msg = formatErrorMessage(customMessage, err, tag)
 		} else {
-			errorsMap[fieldName] = defaultErrorMessage(err)
+			msg = defaultErrorMessage(err)
+		}
+		errorsMap[fieldName] = msg
+		if i == 0 {
+			firstMessage = msg
 		}
 	}
-	return errorsMap
+	return firstMessage, errorsMap
 }
 
 func formatErrorMessage(customMessage string, err validator.FieldError, tag string) string {
-	if tag == "min" || tag == "max" || tag == "len" {
+	if tag == "min" || tag == "max" || tag == "len" || tag == "gt" {
 		return fmt.Sprintf(customMessage, err.Field(), err.Param())
 	}
 	return fmt.Sprintf(customMessage, err.Field())
@@ -61,6 +70,16 @@ func defaultErrorMessage(err validator.FieldError) string {
 func Validator() *validator.Validate {
 	validate := validator.New()
 
+	validate.RegisterTagNameFunc(func(fld reflect.StructField) string {
+		if jsonTag := getTagName(fld, "json"); jsonTag != "" {
+			return jsonTag
+		}
+		if queryTag := getTagName(fld, "query"); queryTag != "" {
+			return queryTag
+		}
+		return fld.Name
+	})
+
 	if err := validate.RegisterValidation("password", Password); err != nil {
 		return nil
 	}
@@ -72,3 +91,16 @@ func Validator() *validator.Validate {
 	}
 	return validate
 }
+
+func getTagName(fld reflect.StructField, tag string) string {
+	value, ok := fld.Tag.Lookup(tag)
+	if !ok || value == "-" {
+		return ""
+	}
+
+	name := strings.Split(value, ",")[0]
+	if name == "" || name == "-" {
+		return ""
+	}
+	return name
+}

internal/config/config.go

@@ -54,6 +54,7 @@ var (
 	SSOAuthorizeURL         string
 	SSOTokenURL             string
 	SSOGetMeURL             string
+	SSOPortalURL            string
 	SSOClients              map[string]SSOClientConfig
 	SSOAccessCookieName     string
 	SSORefreshCookieName    string
@@ -65,6 +66,14 @@ var (
 	SSOUserSyncDrift        time.Duration
 	SSOUserSyncNonceTTL     time.Duration
 	SSOUserSyncMaxBodyBytes int
+	S3Endpoint              string
+	S3Region                string
+	S3Bucket                string
+	S3AccessKey             string
+	S3SecretKey             string
+	S3ForcePathStyle        bool
+	S3PublicBaseURL         string
+	S3DocumentKeyPrefix     string
 )
 
 func init() {
@@ -106,6 +115,16 @@ func init() {
 	// Redis
 	RedisURL = viper.GetString("REDIS_URL")
 
+	// Object storage
+	S3Endpoint = strings.TrimSpace(viper.GetString("S3_ENDPOINT"))
+	S3Region = strings.TrimSpace(viper.GetString("S3_REGION"))
+	S3Bucket = strings.TrimSpace(viper.GetString("S3_BUCKET"))
+	S3AccessKey = strings.TrimSpace(viper.GetString("S3_ACCESS_KEY"))
+	S3SecretKey = strings.TrimSpace(viper.GetString("S3_SECRET_KEY"))
+	S3ForcePathStyle = viper.GetBool("S3_FORCE_PATH_STYLE")
+	S3PublicBaseURL = strings.TrimSuffix(strings.TrimSpace(viper.GetString("S3_PUBLIC_BASE_URL")), "/")
+	S3DocumentKeyPrefix = defaultString(strings.Trim(strings.TrimSpace(viper.GetString("S3_DOCUMENT_PREFIX")), "/"), "docs")
+
 	// SSO integration
 	SSOIssuer = viper.GetString("SSO_ISSUER")
 	SSOJWKSURL = viper.GetString("SSO_JWKS_URL")
@@ -113,6 +132,7 @@ func init() {
 	SSOAuthorizeURL = viper.GetString("SSO_AUTHORIZE_URL")
 	SSOTokenURL = viper.GetString("SSO_TOKEN_URL")
 	SSOGetMeURL = viper.GetString("SSO_GETME_URL")
+	SSOPortalURL = strings.TrimSpace(viper.GetString("SSO_PORTAL_URL"))
 	SSOAccessCookieName = defaultString(viper.GetString("SSO_ACCESS_COOKIE_NAME"), "sso_access")
 	SSORefreshCookieName = defaultString(viper.GetString("SSO_REFRESH_COOKIE_NAME"), "sso_refresh")
 	SSOCookieDomain = viper.GetString("SSO_COOKIE_DOMAIN")

internal/config/fiber.go

@@ -13,6 +13,7 @@ func FiberConfig() fiber.Config {
 		CaseSensitive: true,
 		ServerHeader:  "Fiber",
 		AppName:       "Fiber API",
+		BodyLimit:    8 * 1024 * 1024,
 		ErrorHandler:  utils.ErrorHandler,
 		JSONEncoder:   sonic.Marshal,
 		JSONDecoder:   sonic.Unmarshal,

internal/database/migrations/20250925040409_create_master_tables.up.sql

@@ -2,39 +2,39 @@
 CREATE TABLE users (
     id BIGSERIAL PRIMARY KEY,
     id_user BIGINT NOT NULL,
-    name VARCHAR NOT NULL,
-    email VARCHAR NOT NULL,
+    name VARCHAR(50) NOT NULL,
+    email VARCHAR(50) NOT NULL,
     created_at TIMESTAMPTZ DEFAULT NOW (),
     updated_at TIMESTAMPTZ DEFAULT NOW (),
     deleted_at TIMESTAMPTZ
 );
 
-CREATE UNIQUE INDEX users_id_user_unique ON users (id_user) WHERE deleted_at IS NULL;
+CREATE UNIQUE INDEX users_id_user_unique ON users (id_user)
+WHERE
+    deleted_at IS NULL;
 
-CREATE UNIQUE INDEX users_email_unique ON users (email) WHERE deleted_at IS NULL;
+CREATE UNIQUE INDEX users_email_unique ON users (email)
+WHERE
+    deleted_at IS NULL;
 
 -- FLAGS
 CREATE TABLE flags (
     id BIGSERIAL PRIMARY KEY,
-    name VARCHAR NOT NULL,
+    name VARCHAR(50) NOT NULL,
     flagable_id BIGINT NOT NULL,
     flagable_type VARCHAR(50) NOT NULL,
     created_at TIMESTAMPTZ DEFAULT NOW (),
     updated_at TIMESTAMPTZ DEFAULT NOW ()
 );
 
-CREATE UNIQUE INDEX flags_unique_flagable ON flags (
-    name,
-    flagable_id,
-    flagable_type
-);
+CREATE UNIQUE INDEX flags_unique_flagable ON flags (name, flagable_id, flagable_type);
 
 CREATE INDEX flags_flagable_lookup ON flags (flagable_type, flagable_id);
 
 -- PRODUCT CATEGORIES
 CREATE TABLE product_categories (
     id BIGSERIAL PRIMARY KEY,
-    name VARCHAR NOT NULL,
+    name VARCHAR(50) NOT NULL,
     code VARCHAR(10) NOT NULL,
     created_at TIMESTAMPTZ DEFAULT NOW (),
     updated_at TIMESTAMPTZ DEFAULT NOW (),
@@ -53,7 +53,7 @@ WHERE
 -- UOM
 CREATE TABLE uoms (
     id BIGSERIAL PRIMARY KEY,
-    name VARCHAR NOT NULL,
+    name VARCHAR(50) NOT NULL,
     created_at TIMESTAMPTZ DEFAULT NOW (),
     updated_at TIMESTAMPTZ DEFAULT NOW (),
     deleted_at TIMESTAMPTZ,
@@ -67,9 +67,9 @@ WHERE
 -- BANKS
 CREATE TABLE banks (
     id BIGSERIAL PRIMARY KEY,
-    name VARCHAR NOT NULL,
+    name VARCHAR(50) NOT NULL,
     alias VARCHAR(5) NOT NULL,
-    owner VARCHAR,
+    owner VARCHAR(50),
     account_number VARCHAR(50) NOT NULL,
     created_at TIMESTAMPTZ DEFAULT NOW (),
     updated_at TIMESTAMPTZ DEFAULT NOW (),
@@ -84,7 +84,7 @@ WHERE
 -- AREAS
 CREATE TABLE areas (
     id BIGSERIAL PRIMARY KEY,
-    name VARCHAR NOT NULL,
+    name VARCHAR(50) NOT NULL,
     created_at TIMESTAMPTZ DEFAULT NOW (),
     updated_at TIMESTAMPTZ DEFAULT NOW (),
     deleted_at TIMESTAMPTZ,
@@ -98,7 +98,7 @@ WHERE
 -- LOCATIONS
 CREATE TABLE locations (
     id BIGSERIAL PRIMARY KEY,
-    name VARCHAR NOT NULL,
+    name VARCHAR(50) NOT NULL,
     address TEXT NOT NULL,
     area_id BIGINT NOT NULL REFERENCES areas (id) ON DELETE RESTRICT ON UPDATE CASCADE,
     created_at TIMESTAMPTZ DEFAULT NOW (),
@@ -114,7 +114,7 @@ WHERE
 -- KANDANG
 CREATE TABLE kandangs (
     id BIGSERIAL PRIMARY KEY,
-    name VARCHAR NOT NULL,
+    name VARCHAR(50) NOT NULL,
     location_id BIGINT NOT NULL REFERENCES locations (id) ON DELETE RESTRICT ON UPDATE CASCADE,
     pic_id BIGINT REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE,
     created_at TIMESTAMPTZ DEFAULT NOW (),
@@ -130,7 +130,7 @@ WHERE
 -- WAREHOUSES
 CREATE TABLE warehouses (
     id BIGSERIAL PRIMARY KEY,
-    name VARCHAR NOT NULL,
+    name VARCHAR(50) NOT NULL,
     type VARCHAR(50) NOT NULL,
     area_id BIGINT NOT NULL REFERENCES areas (id) ON DELETE RESTRICT ON UPDATE CASCADE,
     location_id BIGINT REFERENCES locations (id) ON DELETE SET NULL ON UPDATE CASCADE,
@@ -148,12 +148,12 @@ WHERE
 -- CUSTOMERS
 CREATE TABLE customers (
     id BIGSERIAL PRIMARY KEY,
-    name VARCHAR NOT NULL,
+    name VARCHAR(50) NOT NULL,
     pic_id BIGINT REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE,
     type VARCHAR(50) NOT NULL,
     address TEXT NOT NULL,
     phone VARCHAR(20) NOT NULL,
-    email VARCHAR NOT NULL,
+    email VARCHAR(50) NOT NULL,
     account_number VARCHAR(50) NOT NULL,
     balance NUMERIC(15, 3) DEFAULT 0,
     created_at TIMESTAMPTZ DEFAULT NOW (),
@@ -169,7 +169,7 @@ WHERE
 -- NONSTOCK
 CREATE TABLE nonstocks (
     id BIGSERIAL PRIMARY KEY,
-    name VARCHAR NOT NULL,
+    name VARCHAR(50) NOT NULL,
     uom_id BIGINT NOT NULL REFERENCES uoms (id) ON DELETE RESTRICT ON UPDATE CASCADE,
     created_at TIMESTAMPTZ DEFAULT NOW (),
     updated_at TIMESTAMPTZ DEFAULT NOW (),
@@ -184,7 +184,7 @@ WHERE
 -- FCR
 CREATE TABLE fcrs (
     id BIGSERIAL PRIMARY KEY,
-    name VARCHAR NOT NULL,
+    name VARCHAR(50) NOT NULL,
     created_at TIMESTAMPTZ DEFAULT NOW (),
     updated_at TIMESTAMPTZ DEFAULT NOW (),
     deleted_at TIMESTAMPTZ,
@@ -209,14 +209,14 @@ CREATE TABLE fcr_standards (
 -- SUPPLIERS
 CREATE TABLE suppliers (
     id BIGSERIAL PRIMARY KEY,
-    name VARCHAR NOT NULL,
+    name VARCHAR(50) NOT NULL,
     alias VARCHAR(5) NOT NULL,
-    pic VARCHAR NOT NULL,
+    pic VARCHAR(50) NOT NULL,
     type VARCHAR(50) NOT NULL,
     category VARCHAR(20) NOT NULL,
-    hatchery VARCHAR,
+    hatchery VARCHAR(50),
     phone VARCHAR(20) NOT NULL,
-    email VARCHAR NOT NULL,
+    email VARCHAR(50) NOT NULL,
     address TEXT NOT NULL,
     npwp VARCHAR(50),
     account_number VARCHAR(50),
@@ -242,8 +242,8 @@ CREATE TABLE nonstock_suppliers (
 -- PRODUCTS
 CREATE TABLE products (
     id BIGSERIAL PRIMARY KEY,
-    name VARCHAR NOT NULL,
-    brand VARCHAR NOT NULL,
+    name VARCHAR(50) NOT NULL,
+    brand VARCHAR(50) NOT NULL,
     sku VARCHAR(100),
     uom_id BIGINT NOT NULL REFERENCES uoms (id) ON DELETE RESTRICT ON UPDATE CASCADE,
     product_category_id BIGINT NOT NULL REFERENCES product_categories (id) ON DELETE RESTRICT ON UPDATE CASCADE,

internal/database/migrations/20251018120649_create_project_chick_ins_table.up.sql

@@ -1,36 +0,0 @@
-CREATE TABLE IF NOT EXISTS project_chickins (
-    id BIGSERIAL PRIMARY KEY,
-    project_flock_kandang_id BIGINT NOT NULL,
-    chick_in_date DATE NOT NULL,
-    quantity NUMERIC(15, 3) NOT NULL,
-    note TEXT,
-    created_by BIGINT NOT NULL,
-    created_at TIMESTAMPTZ DEFAULT now(),
-    updated_at TIMESTAMPTZ DEFAULT now(),
-    deleted_at TIMESTAMPTZ
-);
-
--- FOREIGN KEYS (dijalankan setelah semua tabel parent ada)
-DO $$
-BEGIN
-    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'project_flock_kandangs') THEN
-        ALTER TABLE project_chickins
-            ADD CONSTRAINT fk_project_flock_kandang_id
-            FOREIGN KEY (project_flock_kandang_id)
-            REFERENCES project_flock_kandangs(id)
-            ON DELETE RESTRICT ON UPDATE CASCADE;
-    END IF;
-
-    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'users') THEN
-        ALTER TABLE project_chickins
-            ADD CONSTRAINT fk_created_by
-            FOREIGN KEY (created_by)
-            REFERENCES users(id)
-            ON DELETE RESTRICT ON UPDATE CASCADE;
-    END IF;
-END $$;
-
--- INDEXES
-CREATE INDEX IF NOT EXISTS idx_project_chickins_project_flock_kandang_id ON project_chickins (project_flock_kandang_id);
-
-CREATE INDEX IF NOT EXISTS idx_project_chickins_created_by ON project_chickins (created_by);

internal/database/migrations/20251020022357_create_project_flock_populations_table.up.sql

@@ -1,36 +0,0 @@
-CREATE TABLE IF NOT EXISTS project_flock_populations (
-    id BIGSERIAL PRIMARY KEY,
-    project_flock_kandang_id BIGINT NOT NULL,
-    initial_quantity NUMERIC(15, 3) NOT NULL,
-    current_quantity NUMERIC(15, 3) NOT NULL,
-    reserved_quantity NUMERIC(15, 3),
-    created_by BIGINT NOT NULL,
-    created_at TIMESTAMPTZ DEFAULT now(),
-    updated_at TIMESTAMPTZ DEFAULT now(),
-    deleted_at TIMESTAMPTZ
-);
-
--- FOREIGN KEYS (dijalankan setelah semua tabel parent ada)
-DO $$
-BEGIN
-    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'project_flock_kandangs') THEN
-        ALTER TABLE project_flock_populations
-            ADD CONSTRAINT fk_project_flock_kandang_id
-            FOREIGN KEY (project_flock_kandang_id)
-            REFERENCES project_flock_kandangs(id)
-            ON DELETE RESTRICT ON UPDATE CASCADE;
-    END IF;
-
-    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'users') THEN
-        ALTER TABLE project_flock_populations
-            ADD CONSTRAINT fk_created_by
-            FOREIGN KEY (created_by)
-            REFERENCES users(id)
-            ON DELETE RESTRICT ON UPDATE CASCADE;
-    END IF;
-END $$;
-
--- INDEXES
-CREATE INDEX IF NOT EXISTS idx_project_flock_populations_project_flock_kandang_id ON project_flock_populations (project_flock_kandang_id);
-
-CREATE INDEX IF NOT EXISTS idx_project_flock_populations_created_by ON project_flock_populations (created_by);

internal/database/migrations/20251022024829_create_project_chickin_details.up.sql

@@ -9,7 +9,7 @@ CREATE TABLE IF NOT EXISTS project_chickin_details (
     deleted_at TIMESTAMPTZ
 );
 
--- FOREIGN KEYS (dijalankan setelah semua tabel parent ada)
+
 DO $$
 BEGIN
     IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'project_chickins') THEN

internal/database/migrations/20251024092758_deleted-project_flock_id_in_kandangs.up.sql

@@ -1,22 +1,30 @@
-
 ALTER TABLE kandangs
 DROP CONSTRAINT IF EXISTS kandangs_project_flock_id_fkey;
 
-ALTER TABLE kandangs
-    DROP COLUMN IF EXISTS project_flock_id;
+ALTER TABLE kandangs DROP COLUMN IF EXISTS project_flock_id;
 
+-- Only alter if tables exist
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'project_chickins') THEN
+        ALTER TABLE project_chickins
+          DROP CONSTRAINT IF EXISTS fk_project_flock_kandang_id;
         ALTER TABLE project_chickins
-  DROP CONSTRAINT fk_project_flock_kandang_id,
           ADD CONSTRAINT fk_project_flock_kandang_id
                FOREIGN KEY (project_flock_kandang_id)
                REFERENCES project_flock_kandangs(id)
                ON UPDATE CASCADE
                ON DELETE CASCADE;
+    END IF;
 
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'project_flock_populations') THEN
+        ALTER TABLE project_flock_populations
+          DROP CONSTRAINT IF EXISTS fk_project_flock_kandang_id;
         ALTER TABLE project_flock_populations
-  DROP CONSTRAINT fk_project_flock_kandang_id,
           ADD CONSTRAINT fk_project_flock_kandang_id
                FOREIGN KEY (project_flock_kandang_id)
                REFERENCES project_flock_kandangs(id)
                ON UPDATE CASCADE
                ON DELETE CASCADE;
+    END IF;
+END $$;

internal/database/migrations/20251029074825_create_laying_transfers_table.down.sql

@@ -0,0 +1 @@
+DROP TABLE IF EXISTS laying_transfers CASCADE;

internal/database/migrations/20251029074825_create_laying_transfers_table.up.sql

@@ -0,0 +1,52 @@
+CREATE TABLE IF NOT EXISTS laying_transfers (
+    id BIGSERIAL PRIMARY KEY,
+    transfer_number VARCHAR(50) UNIQUE NOT NULL,
+    from_project_flock_id BIGINT NOT NULL,
+    to_project_flock_id BIGINT NOT NULL,
+    transfer_date DATE NOT NULL,
+    pending_usage_qty NUMERIC(15, 3),
+    usage_qty NUMERIC(15, 3),
+    notes TEXT,
+    created_at TIMESTAMPTZ DEFAULT now(),
+    updated_at TIMESTAMPTZ DEFAULT now(),
+    deleted_at TIMESTAMPTZ,
+    created_by BIGINT NOT NULL
+);
+
+-- FOREIGN KEYS (dijalankan setelah semua tabel parent ada)
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'project_flocks') THEN
+        ALTER TABLE laying_transfers
+            ADD CONSTRAINT fk_laying_from_project_flock
+            FOREIGN KEY (from_project_flock_id)
+            REFERENCES project_flocks(id)
+            ON DELETE RESTRICT ON UPDATE CASCADE;
+        ALTER TABLE laying_transfers
+            ADD CONSTRAINT fk_laying_to_project_flock
+            FOREIGN KEY (to_project_flock_id)
+            REFERENCES project_flocks(id)
+            ON DELETE RESTRICT ON UPDATE CASCADE;
+    END IF;
+
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'users') THEN
+        ALTER TABLE laying_transfers
+            ADD CONSTRAINT fk_laying_created_by
+            FOREIGN KEY (created_by)
+            REFERENCES users(id)
+            ON DELETE RESTRICT ON UPDATE CASCADE;
+    END IF;
+END $$;
+
+-- INDEXES
+CREATE UNIQUE INDEX IF NOT EXISTS idx_laying_transfers_transfer_number ON laying_transfers (transfer_number)
+WHERE
+    deleted_at IS NULL;
+
+CREATE INDEX IF NOT EXISTS idx_laying_transfers_from_project_flock_id ON laying_transfers (from_project_flock_id);
+
+CREATE INDEX IF NOT EXISTS idx_laying_transfers_to_project_flock_id ON laying_transfers (to_project_flock_id);
+
+CREATE INDEX IF NOT EXISTS idx_laying_transfers_created_by ON laying_transfers (created_by);
+
+CREATE INDEX IF NOT EXISTS idx_laying_transfers_deleted_at ON laying_transfers (deleted_at);

internal/database/migrations/20251030134527_recreate_project_chikins_table.up.sql

@@ -0,0 +1,58 @@
+-- ============================================
+-- MIGRATION: project_chickins
+-- ============================================
+
+-- STEP 1: Hapus tabel jika sudah ada
+DROP TABLE IF EXISTS project_chickins;
+
+-- STEP 2: Buat tabel project_chickins
+CREATE TABLE IF NOT EXISTS project_chickins (
+    id BIGSERIAL PRIMARY KEY,
+    project_flock_kandang_id BIGINT NOT NULL,
+    product_warehouse_id BIGINT NOT NULL,
+    chick_in_date DATE NOT NULL,
+    usage_qty NUMERIC(15, 3) NOT NULL,
+    pending_usage_qty NUMERIC(15, 3) DEFAULT 0,
+    notes TEXT,
+    created_by BIGINT NOT NULL,
+    created_at TIMESTAMPTZ DEFAULT now(),
+    updated_at TIMESTAMPTZ DEFAULT now(),
+    deleted_at TIMESTAMPTZ
+);
+
+-- STEP 3: FOREIGN KEYS
+BEGIN;
+
+-- Relasi ke project_flock_kandangs
+ALTER TABLE project_chickins
+ADD CONSTRAINT fk_project_chickins_kandang FOREIGN KEY (project_flock_kandang_id) REFERENCES project_flock_kandangs (id) ON DELETE RESTRICT ON UPDATE CASCADE;
+
+-- Relasi ke product_warehouses
+ALTER TABLE project_chickins
+ADD CONSTRAINT fk_project_chickins_warehouse FOREIGN KEY (product_warehouse_id) REFERENCES product_warehouses (id) ON DELETE CASCADE ON UPDATE CASCADE;
+
+-- Relasi ke users
+ALTER TABLE project_chickins
+ADD CONSTRAINT fk_project_chickins_created_by FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE RESTRICT ON UPDATE CASCADE;
+
+COMMIT;
+
+-- STEP 4: INDEXES
+CREATE INDEX IF NOT EXISTS idx_chickins_kandang_id ON project_chickins (project_flock_kandang_id)
+WHERE
+    deleted_at IS NULL;
+
+CREATE INDEX IF NOT EXISTS idx_chickins_warehouse_id ON project_chickins (product_warehouse_id)
+WHERE
+    deleted_at IS NULL;
+
+CREATE INDEX IF NOT EXISTS idx_chickins_created_by ON project_chickins (created_by);
+
+-- Composite index for common queries
+CREATE INDEX IF NOT EXISTS idx_chickins_kandang_deleted ON project_chickins (
+    project_flock_kandang_id,
+    deleted_at
+);
+
+-- Index for soft delete queries
+CREATE INDEX IF NOT EXISTS idx_chickins_deleted_at ON project_chickins (deleted_at);

internal/database/migrations/20251030134542_recreate_project_flock_populations.up.sql

@@ -0,0 +1,62 @@
+-- ============================================
+-- MIGRATION: project_flock_populations
+-- ============================================
+
+-- STEP 1: Hapus tabel jika sudah ada
+DROP TABLE IF EXISTS project_flock_populations;
+
+-- STEP 2: Buat tabel project_flock_populations
+CREATE TABLE IF NOT EXISTS project_flock_populations (
+    id BIGSERIAL PRIMARY KEY,
+    project_chickin_id BIGINT NOT NULL,
+    product_warehouse_id BIGINT NOT NULL,
+    total_qty NUMERIC(15, 3) NOT NULL,
+    total_used_qty NUMERIC(15, 3) DEFAULT 0,
+    notes TEXT,
+    created_by BIGINT NOT NULL,
+    created_at TIMESTAMPTZ DEFAULT now(),
+    updated_at TIMESTAMPTZ DEFAULT now(),
+    deleted_at TIMESTAMPTZ
+);
+
+-- STEP 3: FOREIGN KEYS
+BEGIN;
+
+-- Relasi ke project_chickins
+ALTER TABLE project_flock_populations
+ADD CONSTRAINT fk_project_flock_populations_chickin FOREIGN KEY (project_chickin_id) REFERENCES project_chickins (id) ON DELETE RESTRICT ON UPDATE CASCADE;
+
+-- Relasi ke product_warehouses
+ALTER TABLE project_flock_populations
+ADD CONSTRAINT fk_project_flock_populations_warehouse FOREIGN KEY (product_warehouse_id) REFERENCES product_warehouses (id) ON DELETE RESTRICT ON UPDATE CASCADE;
+
+-- Relasi ke users
+ALTER TABLE project_flock_populations
+ADD CONSTRAINT fk_project_flock_populations_created_by FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE RESTRICT ON UPDATE CASCADE;
+
+COMMIT;
+
+-- STEP 4: INDEXES
+CREATE INDEX IF NOT EXISTS idx_populations_chickin_id ON project_flock_populations (project_chickin_id)
+WHERE
+    deleted_at IS NULL;
+
+CREATE INDEX IF NOT EXISTS idx_populations_warehouse_id ON project_flock_populations (product_warehouse_id)
+WHERE
+    deleted_at IS NULL;
+
+CREATE INDEX IF NOT EXISTS idx_populations_created_by ON project_flock_populations (created_by);
+
+-- Composite index for common queries
+CREATE INDEX IF NOT EXISTS idx_populations_chickin_deleted ON project_flock_populations (
+    project_chickin_id,
+    deleted_at
+);
+
+-- Index for soft delete queries
+CREATE INDEX IF NOT EXISTS idx_populations_deleted_at ON project_flock_populations (deleted_at);
+
+-- Unique constraint: one population per chickin
+CREATE UNIQUE INDEX IF NOT EXISTS idx_populations_chickin_unique ON project_flock_populations (project_chickin_id)
+WHERE
+    deleted_at IS NULL;

internal/database/migrations/20251103054536_add_laying_transfer_sources_and_targets_tables.down.sql

@@ -0,0 +1,5 @@
+-- Rollback laying_transfer_sources dan laying_transfer_targets tables
+
+DROP TABLE IF EXISTS laying_transfer_targets CASCADE;
+
+DROP TABLE IF EXISTS laying_transfer_sources CASCADE;

internal/database/migrations/20251103054536_add_laying_transfer_sources_and_targets_tables.up.sql

@@ -0,0 +1,93 @@
+-- Create laying_transfer_sources dan laying_transfer_targets tables
+
+-- 1. Create laying_transfer_sources table (detail sumber - kandang asal growing)
+CREATE TABLE laying_transfer_sources (
+    id BIGSERIAL PRIMARY KEY,
+    laying_transfer_id BIGINT NOT NULL,
+    source_project_flock_kandang_id BIGINT NOT NULL,
+    product_warehouse_id BIGINT,
+    qty NUMERIC(15, 3) NOT NULL,
+    note TEXT,
+    created_at TIMESTAMPTZ DEFAULT NOW(),
+    updated_at TIMESTAMPTZ DEFAULT NOW(),
+    deleted_at TIMESTAMPTZ
+);
+
+-- Add foreign keys untuk laying_transfer_sources
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'laying_transfers') THEN
+        ALTER TABLE laying_transfer_sources
+            ADD CONSTRAINT fk_laying_transfer_sources_laying_transfer_id 
+            FOREIGN KEY (laying_transfer_id) REFERENCES laying_transfers(id) ON DELETE CASCADE;
+    END IF;
+
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'project_flock_kandangs') THEN
+        ALTER TABLE laying_transfer_sources
+            ADD CONSTRAINT fk_laying_transfer_sources_project_flock_kandang_id 
+            FOREIGN KEY (source_project_flock_kandang_id) REFERENCES project_flock_kandangs(id) ON DELETE RESTRICT;
+    END IF;
+
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'product_warehouses') THEN
+        ALTER TABLE laying_transfer_sources
+            ADD CONSTRAINT fk_laying_transfer_sources_product_warehouse_id 
+            FOREIGN KEY (product_warehouse_id) REFERENCES product_warehouses(id) ON DELETE SET NULL;
+    END IF;
+END $$;
+
+-- 2. Create laying_transfer_targets table (detail tujuan - kandang laying)
+CREATE TABLE laying_transfer_targets (
+    id BIGSERIAL PRIMARY KEY,
+    laying_transfer_id BIGINT NOT NULL,
+    target_project_flock_kandang_id BIGINT NOT NULL,
+    qty NUMERIC(15, 3) NOT NULL,
+    product_warehouse_id BIGINT,
+    note TEXT,
+    created_at TIMESTAMPTZ DEFAULT NOW(),
+    updated_at TIMESTAMPTZ DEFAULT NOW(),
+    deleted_at TIMESTAMPTZ
+);
+
+-- Add foreign keys untuk laying_transfer_targets
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'laying_transfers') THEN
+        ALTER TABLE laying_transfer_targets
+            ADD CONSTRAINT fk_laying_transfer_targets_laying_transfer_id 
+            FOREIGN KEY (laying_transfer_id) REFERENCES laying_transfers(id) ON DELETE CASCADE;
+    END IF;
+
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'project_flock_kandangs') THEN
+        ALTER TABLE laying_transfer_targets
+            ADD CONSTRAINT fk_laying_transfer_targets_project_flock_kandang_id 
+            FOREIGN KEY (target_project_flock_kandang_id) REFERENCES project_flock_kandangs(id) ON DELETE RESTRICT;
+    END IF;
+
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'product_warehouses') THEN
+        ALTER TABLE laying_transfer_targets
+            ADD CONSTRAINT fk_laying_transfer_targets_product_warehouse_id 
+            FOREIGN KEY (product_warehouse_id) REFERENCES product_warehouses(id) ON DELETE SET NULL;
+    END IF;
+END $$;
+
+-- 3. Create indexes untuk laying_transfer_sources
+CREATE INDEX idx_laying_transfer_sources_laying_transfer_id ON laying_transfer_sources (laying_transfer_id);
+
+CREATE INDEX idx_laying_transfer_sources_source_kandang_id ON laying_transfer_sources (
+    source_project_flock_kandang_id
+);
+
+CREATE INDEX idx_laying_transfer_sources_product_warehouse_id ON laying_transfer_sources (product_warehouse_id);
+
+CREATE INDEX idx_laying_transfer_sources_deleted_at ON laying_transfer_sources (deleted_at);
+
+-- 4. Create indexes untuk laying_transfer_targets
+CREATE INDEX idx_laying_transfer_targets_laying_transfer_id ON laying_transfer_targets (laying_transfer_id);
+
+CREATE INDEX idx_laying_transfer_targets_target_kandang_id ON laying_transfer_targets (
+    target_project_flock_kandang_id
+);
+
+CREATE INDEX idx_laying_transfer_targets_product_warehouse_id ON laying_transfer_targets (product_warehouse_id);
+
+CREATE INDEX idx_laying_transfer_targets_deleted_at ON laying_transfer_targets (deleted_at);

internal/database/migrations/20251104084540_purchase-items.down.sql

@@ -0,0 +1 @@
+DROP TABLE IF EXISTS purchase_items;

internal/database/migrations/20251104084540_purchase-items.up.sql

@@ -0,0 +1,54 @@
+CREATE TABLE IF NOT EXISTS purchase_items (
+    id BIGSERIAL PRIMARY KEY,
+    purchase_id BIGINT NOT NULL,
+    product_id BIGINT NOT NULL,
+    warehouse_id BIGINT NOT NULL,
+    product_warehouse_id BIGINT,
+    received_date TIMESTAMPTZ,
+    travel_number VARCHAR,
+    travel_number_docs VARCHAR,
+    vehicle_number VARCHAR,
+    sub_qty NUMERIC(15, 3) NOT NULL,
+    total_qty NUMERIC(15, 3) NOT NULL DEFAULT 0,
+    total_used NUMERIC(15, 3) NOT NULL DEFAULT 0,
+    price NUMERIC(15, 3) NOT NULL DEFAULT 0,
+    total_price NUMERIC(15, 3) NOT NULL DEFAULT 0,
+    CONSTRAINT uq_purchase_items_purchase_product_warehouse
+        UNIQUE (purchase_id, product_id, warehouse_id)
+);
+
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'products') THEN
+        EXECUTE
+        'ALTER TABLE purchase_items
+         ADD CONSTRAINT fk_purchase_items_product
+         FOREIGN KEY (product_id)
+         REFERENCES products(id)
+         ON DELETE RESTRICT ON UPDATE CASCADE';
+    END IF;
+
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'warehouses') THEN
+        EXECUTE
+        'ALTER TABLE purchase_items
+         ADD CONSTRAINT fk_purchase_items_warehouse
+         FOREIGN KEY (warehouse_id)
+         REFERENCES warehouses(id)
+         ON DELETE RESTRICT ON UPDATE CASCADE';
+    END IF;
+
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'product_warehouses') THEN
+        EXECUTE
+        'ALTER TABLE purchase_items
+         ADD CONSTRAINT fk_purchase_items_product_warehouse
+         FOREIGN KEY (product_warehouse_id)
+         REFERENCES product_warehouses(id)
+         ON DELETE SET NULL ON UPDATE CASCADE';
+    END IF;
+
+END $$;
+
+CREATE INDEX IF NOT EXISTS idx_purchase_items_product_id ON purchase_items (product_id);
+CREATE INDEX IF NOT EXISTS idx_purchase_items_warehouse_id ON purchase_items (warehouse_id);
+CREATE INDEX IF NOT EXISTS idx_purchase_items_product_warehouse_id ON purchase_items (product_warehouse_id);
+CREATE INDEX IF NOT EXISTS idx_purchase_items_purchase_id ON purchase_items (purchase_id);

internal/database/migrations/20251104084555_purchases.down.sql

@@ -0,0 +1,14 @@
+DO $$
+BEGIN
+    IF EXISTS (
+        SELECT 1
+        FROM pg_constraint
+        WHERE conname = 'fk_purchase_items_purchase'
+            AND conrelid = 'purchase_items'::regclass
+    ) THEN
+        ALTER TABLE purchase_items
+            DROP CONSTRAINT fk_purchase_items_purchase;
+    END IF;
+END $$;
+
+DROP TABLE IF EXISTS purchases;

internal/database/migrations/20251104084555_purchases.up.sql

@@ -0,0 +1,58 @@
+CREATE TABLE IF NOT EXISTS purchases (
+    id BIGSERIAL PRIMARY KEY,
+    pr_number VARCHAR NOT NULL,
+    po_number VARCHAR NULL,
+    po_date TIMESTAMPTZ NULL,
+    supplier_id BIGINT NOT NULL,
+    credit_term INT NOT NULL,
+    due_date TIMESTAMPTZ,
+    grand_total NUMERIC(15, 3) NOT NULL,
+    notes TEXT,
+    created_at TIMESTAMPTZ NOT NULL,
+    updated_at TIMESTAMPTZ NOT NULL,
+    deleted_at TIMESTAMPTZ,
+    created_by BIGINT NOT NULL,
+    CONSTRAINT uq_purchases_pr_number UNIQUE (pr_number),
+    CONSTRAINT uq_purchases_po_number UNIQUE (po_number)
+);
+
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'suppliers') THEN
+        EXECUTE
+        'ALTER TABLE purchases
+         ADD CONSTRAINT fk_purchases_supplier
+         FOREIGN KEY (supplier_id)
+         REFERENCES suppliers(id)
+         ON DELETE RESTRICT ON UPDATE CASCADE';
+    END IF;
+
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'users') THEN
+        EXECUTE
+        'ALTER TABLE purchases
+         ADD CONSTRAINT fk_purchases_created_by
+         FOREIGN KEY (created_by)
+         REFERENCES users(id)
+         ON DELETE RESTRICT ON UPDATE CASCADE';
+    END IF;
+
+    IF EXISTS (
+        SELECT 1 FROM pg_tables WHERE tablename = 'purchase_items'
+    ) AND NOT EXISTS (
+        SELECT 1
+        FROM pg_constraint
+        WHERE conname = 'fk_purchase_items_purchase'
+    ) THEN
+        EXECUTE
+        'ALTER TABLE purchase_items
+         ADD CONSTRAINT fk_purchase_items_purchase
+         FOREIGN KEY (purchase_id)
+         REFERENCES purchases(id)
+         ON DELETE CASCADE ON UPDATE CASCADE';
+    END IF;
+END $$;
+
+CREATE INDEX IF NOT EXISTS idx_purchases_supplier_id ON purchases (supplier_id);
+CREATE INDEX IF NOT EXISTS idx_purchases_created_by ON purchases (created_by);
+CREATE INDEX IF NOT EXISTS idx_purchases_po_date ON purchases (po_date);
+CREATE INDEX IF NOT EXISTS idx_purchases_deleted_at ON purchases (deleted_at);

internal/database/migrations/20251107013921_create_marketings.down.sql

@@ -0,0 +1,5 @@
+DROP TABLE IF EXISTS marketing_delivery_products CASCADE;
+
+DROP TABLE IF EXISTS marketing_products CASCADE;
+
+DROP TABLE IF EXISTS marketings CASCADE;

internal/database/migrations/20251107013921_create_marketings.up.sql

@@ -0,0 +1,44 @@
+CREATE TABLE marketings (
+    id BIGSERIAL PRIMARY KEY,
+    so_number VARCHAR(255) UNIQUE NOT NULL,
+    customer_id BIGINT NOT NULL,
+    so_docs VARCHAR(20),
+    so_date DATE NOT NULL,
+    sales_person_id BIGINT NOT NULL,
+    notes TEXT,
+    created_by BIGINT NOT NULL,
+    created_at TIMESTAMPTZ DEFAULT NOW(),
+    updated_at TIMESTAMPTZ DEFAULT NOW(),
+    deleted_at TIMESTAMPTZ
+);
+
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'customers') THEN
+        ALTER TABLE marketings
+            ADD CONSTRAINT fk_marketings_customer_id 
+            FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT;
+    END IF;
+
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'users') THEN
+        ALTER TABLE marketings
+            ADD CONSTRAINT fk_marketings_sales_person_id 
+            FOREIGN KEY (sales_person_id) REFERENCES users(id) ON DELETE RESTRICT;
+    END IF;
+
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'users') THEN
+        ALTER TABLE marketings
+            ADD CONSTRAINT fk_marketings_created_by 
+            FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT;
+    END IF;
+END $$;
+
+CREATE INDEX idx_marketings_customer_id ON marketings (customer_id);
+
+CREATE INDEX idx_marketings_sales_person_id ON marketings (sales_person_id);
+
+CREATE INDEX idx_marketings_created_by ON marketings (created_by);
+
+CREATE INDEX idx_marketings_so_date ON marketings (so_date);
+
+CREATE INDEX idx_marketings_deleted_at ON marketings (deleted_at);

internal/database/migrations/20251107015122_create_marketing_products.down.sql

@@ -0,0 +1 @@
+DROP TABLE IF EXISTS marketing_products CASCADE;

internal/database/migrations/20251107015122_create_marketing_products.up.sql

@@ -0,0 +1,34 @@
+CREATE TABLE marketing_products (
+    id BIGSERIAL PRIMARY KEY,
+    marketing_id BIGINT NOT NULL,
+    product_warehouse_id BIGINT NOT NULL,
+    qty NUMERIC(15, 3) NOT NULL,
+    unit_price NUMERIC(15, 3) NOT NULL,
+    avg_weight NUMERIC(15, 3) NOT NULL,
+    total_weight NUMERIC(15, 3) NOT NULL,
+    total_price NUMERIC(15, 3) NOT NULL,
+    created_at TIMESTAMPTZ DEFAULT NOW(),
+    updated_at TIMESTAMPTZ DEFAULT NOW(),
+    deleted_at TIMESTAMPTZ
+);
+
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'marketings') THEN
+        ALTER TABLE marketing_products
+            ADD CONSTRAINT fk_marketing_products_marketing_id 
+            FOREIGN KEY (marketing_id) REFERENCES marketings(id) ON DELETE CASCADE;
+    END IF;
+
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'product_warehouses') THEN
+        ALTER TABLE marketing_products
+            ADD CONSTRAINT fk_marketing_products_product_warehouse_id 
+            FOREIGN KEY (product_warehouse_id) REFERENCES product_warehouses(id) ON DELETE RESTRICT;
+    END IF;
+END $$;
+
+CREATE INDEX idx_marketing_products_marketing_id ON marketing_products (marketing_id);
+
+CREATE INDEX idx_marketing_products_product_warehouse_id ON marketing_products (product_warehouse_id);
+
+CREATE INDEX idx_marketing_products_deleted_at ON marketing_products (deleted_at);

internal/database/migrations/20251107015128_create_marketing_product_deliveries.down.sql

@@ -0,0 +1,2 @@
+
+DROP TABLE IF EXISTS marketing_delivery_products CASCADE;

internal/database/migrations/20251107015128_create_marketing_product_deliveries.up.sql

@@ -0,0 +1,29 @@
+CREATE TABLE marketing_delivery_products (
+    id BIGSERIAL PRIMARY KEY,
+    marketing_product_id BIGINT UNIQUE NOT NULL,
+    qty NUMERIC(15, 3) NOT NULL,
+    unit_price NUMERIC(15, 3) NOT NULL,
+    total_weight NUMERIC(15, 3) NOT NULL,
+    avg_weight NUMERIC(15, 3) NOT NULL,
+    total_price NUMERIC(15, 3) NOT NULL,
+    delivery_date DATE,
+    vehicle_number VARCHAR(50),
+    created_at TIMESTAMPTZ DEFAULT NOW(),
+    updated_at TIMESTAMPTZ DEFAULT NOW(),
+    deleted_at TIMESTAMPTZ
+);
+
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'marketing_products') THEN
+        ALTER TABLE marketing_delivery_products
+            ADD CONSTRAINT fk_marketing_delivery_products_marketing_product_id 
+            FOREIGN KEY (marketing_product_id) REFERENCES marketing_products(id) ON DELETE CASCADE;
+    END IF;
+END $$;
+
+CREATE INDEX idx_marketing_delivery_products_marketing_product_id ON marketing_delivery_products (marketing_product_id);
+
+CREATE INDEX idx_marketing_delivery_products_delivery_date ON marketing_delivery_products (delivery_date);
+
+CREATE INDEX idx_marketing_delivery_products_deleted_at ON marketing_delivery_products (deleted_at);

internal/database/migrations/20251110070219_create_stock_allocations_table.down.sql

@@ -0,0 +1,7 @@
+DROP INDEX IF EXISTS stock_allocations_released_at_idx;
+DROP INDEX IF EXISTS stock_allocations_status_idx;
+DROP INDEX IF EXISTS stock_allocations_usage_lookup;
+DROP INDEX IF EXISTS stock_allocations_lookup;
+DROP INDEX IF EXISTS stock_allocations_product_warehouse_id_idx;
+
+DROP TABLE IF EXISTS stock_allocations;

internal/database/migrations/20251110070219_create_stock_allocations_table.up.sql

@@ -0,0 +1,30 @@
+CREATE TABLE IF NOT EXISTS stock_allocations (
+    id BIGSERIAL PRIMARY KEY,
+    product_warehouse_id BIGINT NOT NULL REFERENCES product_warehouses(id),
+    stockable_type VARCHAR(100) NOT NULL,
+    stockable_id BIGINT NOT NULL,
+    usable_type VARCHAR(100) NOT NULL,
+    usable_id BIGINT NOT NULL,
+    qty NUMERIC(15,3) NOT NULL,
+    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
+    note TEXT NULL,
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    released_at TIMESTAMPTZ NULL,
+    deleted_at TIMESTAMPTZ NULL
+);
+
+CREATE INDEX IF NOT EXISTS stock_allocations_product_warehouse_id_idx
+    ON stock_allocations (product_warehouse_id);
+
+CREATE INDEX IF NOT EXISTS stock_allocations_lookup
+    ON stock_allocations (stockable_type, stockable_id);
+
+CREATE INDEX IF NOT EXISTS stock_allocations_usage_lookup
+    ON stock_allocations (usable_type, usable_id);
+
+CREATE INDEX IF NOT EXISTS stock_allocations_status_idx
+    ON stock_allocations (status);
+
+CREATE INDEX IF NOT EXISTS stock_allocations_released_at_idx
+    ON stock_allocations (released_at);

internal/database/migrations/20251114084320_update_kandang_capacity.down.sql

@@ -0,0 +1,2 @@
+ALTER TABLE kandangs
+    DROP COLUMN IF EXISTS capacity;

internal/database/migrations/20251114084320_update_kandang_capacity.up.sql

@@ -0,0 +1,2 @@
+ALTER TABLE kandangs
+    ADD COLUMN capacity NUMERIC(15,3) NOT NULL;

internal/database/migrations/20251117034511_create_expenses_table.down.sql

@@ -0,0 +1,2 @@
+DROP SEQUENCE IF EXISTS expenses_ref_seq;
+DROP TABLE IF EXISTS expenses;

internal/database/migrations/20251117034511_create_expenses_table.up.sql

@@ -0,0 +1,50 @@
+CREATE TABLE expenses (
+    id BIGSERIAL PRIMARY KEY,
+    reference_number VARCHAR(50) UNIQUE NOT NULL,
+    supplier_id BIGINT NOT NULL,
+    category VARCHAR(50) NOT NULL CHECK (
+        category IN ('BOP', 'NON-BOP')
+    ),
+    po_number VARCHAR(50) NULL,
+    document_path JSON,
+    realization_document_path JSON,
+    expense_date DATE NOT NULL,
+    realization_date DATE,
+    grand_total NUMERIC(15, 3) DEFAULT 0,
+    note TEXT,
+    created_by BIGINT,
+    created_at TIMESTAMPTZ DEFAULT now(),
+    updated_at TIMESTAMPTZ DEFAULT now(),
+    deleted_at TIMESTAMPTZ
+);
+
+CREATE SEQUENCE expenses_ref_seq INCREMENT BY 1 START WITH 1;
+
+-- Tambahkan Foreign Key ke suppliers
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'suppliers') THEN
+        ALTER TABLE expenses
+            ADD CONSTRAINT fk_expenses_supplier_id 
+            FOREIGN KEY (supplier_id) REFERENCES suppliers(id);
+
+END IF;
+
+END $$;
+
+-- Tambahkan Foreign Key ke users (created_by)
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'users') THEN
+        ALTER TABLE expenses
+            ADD CONSTRAINT fk_expenses_created_by
+            FOREIGN KEY (created_by) REFERENCES users(id);
+    END IF;
+END $$;
+
+-- Index
+CREATE INDEX idx_expenses_supplier_id ON expenses (supplier_id);
+
+CREATE INDEX idx_expenses_expense_date ON expenses (expense_date);
+
+CREATE INDEX idx_expenses_deleted_at ON expenses (deleted_at);

internal/database/migrations/20251117034529_create_expense_nonstocks_table.down.sql

@@ -0,0 +1,3 @@
+DROP TABLE IF EXISTS expense_nonstocks;
+
+DROP SEQUENCE expenses_ref_seq;

internal/database/migrations/20251117034529_create_expense_nonstocks_table.up.sql

@@ -0,0 +1,56 @@
+CREATE TABLE expense_nonstocks (
+    id BIGSERIAL PRIMARY KEY,
+    expense_id BIGINT NOT NULL,
+    project_flock_kandang_id BIGINT NULL,
+    kandang_id BIGINT NULL,
+    nonstock_id BIGINT,
+    qty NUMERIC(15, 3) NOT NULL,
+    unit_price NUMERIC(15, 3) NOT NULL,
+    total_price NUMERIC(15, 3) NOT NULL,
+    note TEXT NULL
+);
+
+-- Tambahkan Foreign Key ke expenses
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'expenses') THEN
+        ALTER TABLE expense_nonstocks
+            ADD CONSTRAINT fk_expense_nonstocks_expense_id 
+            FOREIGN KEY (expense_id) REFERENCES expenses(id);
+    END IF;
+END $$;
+
+-- Tambahkan Foreign Key ke project_flock_kandangs
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'project_flock_kandangs') THEN
+        ALTER TABLE expense_nonstocks
+            ADD CONSTRAINT fk_expense_nonstocks_kandang_id 
+            FOREIGN KEY (project_flock_kandang_id) REFERENCES project_flock_kandangs(id);
+    END IF;
+END $$;
+
+-- Tambahkan Foreign key ke kandang_id
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'kandangs') THEN
+        ALTER TABLE expense_nonstocks
+            ADD CONSTRAINT fk_expense_nonstocks_kandang_id_2 
+            FOREIGN KEY (kandang_id) REFERENCES kandangs(id);
+    END IF;
+END $$;
+
+-- Tambahkan Foreign Key ke nonstocks
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'nonstocks') THEN
+        ALTER TABLE expense_nonstocks
+            ADD CONSTRAINT fk_expense_nonstocks_nonstock_id 
+            FOREIGN KEY (nonstock_id) REFERENCES nonstocks(id);
+    END IF;
+END $$;
+
+-- Index
+CREATE INDEX idx_expense_nonstocks_expense_id ON expense_nonstocks (expense_id);
+
+CREATE INDEX idx_expense_nonstocks_nonstock_id ON expense_nonstocks (nonstock_id);

internal/database/migrations/20251117034538_create_expense_realizations_table.down.sql

@@ -0,0 +1 @@
+DROP TABLE IF EXISTS expense_realizations;

internal/database/migrations/20251117034538_create_expense_realizations_table.up.sql

@@ -0,0 +1,35 @@
+CREATE TABLE expense_realizations (
+    id BIGSERIAL PRIMARY KEY,
+    expense_nonstock_id BIGINT UNIQUE,
+    realization_qty NUMERIC(15, 3) NOT NULL,
+    realization_unit_price NUMERIC(15, 3) NOT NULL,
+    realization_total_price NUMERIC(15, 3) NOT NULL,
+    realization_date DATE NOT NULL,
+    note TEXT,
+    created_by BIGINT
+);
+
+-- Tambahkan Foreign Key ke expense_nonstocks
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'expense_nonstocks') THEN
+        ALTER TABLE expense_realizations
+            ADD CONSTRAINT fk_expense_realizations_nonstock_id 
+            FOREIGN KEY (expense_nonstock_id) REFERENCES expense_nonstocks(id);
+    END IF;
+END $$;
+
+-- Tambahkan Foreign Key ke users (created_by)
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'users') THEN
+        ALTER TABLE expense_realizations
+            ADD CONSTRAINT fk_expense_realizations_created_by
+            FOREIGN KEY (created_by) REFERENCES users(id);
+    END IF;
+END $$;
+
+-- Index
+CREATE INDEX idx_expense_realizations_nonstock_id ON expense_realizations (expense_nonstock_id);
+
+CREATE INDEX idx_expense_realizations_date ON expense_realizations (realization_date);

internal/database/migrations/20251117092514_adjustment_projectflock_period.down.sql

@@ -0,0 +1,16 @@
+BEGIN;
+
+ALTER TABLE project_flock_kandangs
+    DROP COLUMN IF EXISTS period;
+
+ALTER TABLE project_flocks
+    ADD COLUMN IF NOT EXISTS period INT NOT NULL DEFAULT 0;
+
+CREATE UNIQUE INDEX IF NOT EXISTS project_flocks_base_period_unique
+    ON project_flocks (
+        LOWER(TRIM(regexp_replace(flock_name, '\\s+\\d+(\\s+\\d+)*$', '', 'g'))),
+        period
+    )
+    WHERE deleted_at IS NULL;
+
+COMMIT;

internal/database/migrations/20251117092514_adjustment_projectflock_period.up.sql

@@ -0,0 +1,29 @@
+BEGIN;
+
+ALTER TABLE project_flock_kandangs
+    ADD COLUMN IF NOT EXISTS period INT;
+
+UPDATE project_flock_kandangs pfk
+SET period = pf.period
+FROM project_flocks pf
+WHERE pfk.project_flock_id = pf.id
+  AND (pfk.period IS NULL OR pfk.period = 0)
+  AND pf.period IS NOT NULL;
+
+ALTER TABLE project_flock_kandangs
+    ALTER COLUMN period SET DEFAULT 0;
+
+UPDATE project_flock_kandangs
+SET period = 0
+WHERE period IS NULL;
+
+ALTER TABLE project_flock_kandangs
+    ALTER COLUMN period SET NOT NULL;
+
+-- Drop period from project_flocks as the source of truth
+DROP INDEX IF EXISTS project_flocks_base_period_unique;
+
+ALTER TABLE project_flocks
+    DROP COLUMN IF EXISTS period;
+
+COMMIT;

internal/database/migrations/20251118044026_update_marketing_product_and_marketing_delivery.down.sql

@@ -0,0 +1,11 @@
+-- Add back timestamp columns to marketing_products table
+ALTER TABLE marketing_products
+ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ DEFAULT NOW(),
+ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ DEFAULT NOW(),
+ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
+
+-- Add back timestamp columns to marketing_delivery_products table
+ALTER TABLE marketing_delivery_products
+ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ DEFAULT NOW(),
+ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ DEFAULT NOW(),
+ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;

internal/database/migrations/20251118044026_update_marketing_product_and_marketing_delivery.up.sql

@@ -0,0 +1,27 @@
+-- Drop timestamp columns from marketing_products table if it exists
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'marketing_products' AND column_name = 'created_at') THEN
+        ALTER TABLE marketing_products DROP COLUMN created_at;
+    END IF;
+    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'marketing_products' AND column_name = 'updated_at') THEN
+        ALTER TABLE marketing_products DROP COLUMN updated_at;
+    END IF;
+    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'marketing_products' AND column_name = 'deleted_at') THEN
+        ALTER TABLE marketing_products DROP COLUMN deleted_at;
+    END IF;
+END $$;
+
+-- Drop timestamp columns from marketing_delivery_products table if it exists
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'marketing_delivery_products' AND column_name = 'created_at') THEN
+        ALTER TABLE marketing_delivery_products DROP COLUMN created_at;
+    END IF;
+    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'marketing_delivery_products' AND column_name = 'updated_at') THEN
+        ALTER TABLE marketing_delivery_products DROP COLUMN updated_at;
+    END IF;
+    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'marketing_delivery_products' AND column_name = 'deleted_at') THEN
+        ALTER TABLE marketing_delivery_products DROP COLUMN deleted_at;
+    END IF;
+END $$;

internal/database/migrations/20251125055613_update_expneses_expense_nostock_and_expense_ralization.up.sql

@@ -0,0 +1,44 @@
+-- ============================
+-- EXPENSES
+-- ============================
+ALTER TABLE expenses DROP COLUMN IF EXISTS grand_total;
+
+ALTER TABLE expenses RENAME COLUMN note TO notes;
+
+ALTER TABLE expenses RENAME COLUMN expense_date TO transaction_date;
+
+-- ============================
+-- EXPENSE_REALIZATIONS
+-- ============================
+ALTER TABLE expense_realizations
+RENAME COLUMN realization_qty TO qty;
+
+ALTER TABLE expense_realizations
+RENAME COLUMN realization_unit_price TO price;
+
+ALTER TABLE expense_realizations RENAME COLUMN note TO notes;
+
+ALTER TABLE expense_realizations
+DROP COLUMN IF EXISTS realization_total_price;
+
+ALTER TABLE expense_realizations
+DROP COLUMN IF EXISTS realization_date;
+
+ALTER TABLE expense_realizations DROP COLUMN IF EXISTS created_by;
+
+ALTER TABLE expense_realizations
+ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ DEFAULT NOW();
+
+-- ============================
+-- EXPENSE_NONSTOCKS
+-- ============================
+ALTER TABLE expense_nonstocks RENAME COLUMN note TO notes;
+
+ALTER TABLE expense_nonstocks DROP COLUMN IF EXISTS total_price;
+
+ALTER TABLE expense_nonstocks RENAME COLUMN unit_price TO price;
+
+ALTER TABLE expense_nonstocks DROP COLUMN IF EXISTS created_by;
+
+ALTER TABLE expense_nonstocks
+ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ DEFAULT NOW();

internal/database/migrations/20251127070744_create_project_budgets.down.sql

@@ -0,0 +1,2 @@
+DROP Table IF EXISTS project_budgets;
+

internal/database/migrations/20251127070744_create_project_budgets.up.sql

@@ -0,0 +1,31 @@
+CREATE TABLE project_budgets (
+    id BIGSERIAL PRIMARY KEY,
+    project_flock_id BIGINT NOT NULL,
+    nonstock_id BIGINT NOT NULL,
+    qty NUMERIC(15, 3) NOT NULL,
+    price NUMERIC(15, 3) NOT NULL,
+    created_at TIMESTAMPTZ DEFAULT now()
+);
+
+-- Tambahkan Foreign Key ke project_flocks
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'project_flocks') THEN
+        ALTER TABLE project_budgets
+            ADD CONSTRAINT fk_project_budgets_project_flock_id 
+            FOREIGN KEY (project_flock_id) REFERENCES project_flocks(id);
+    END IF;
+END $$;
+-- Tambahkan Foreign Key ke nonstocks
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'nonstocks') THEN
+        ALTER TABLE project_budgets
+            ADD CONSTRAINT fk_project_budgets_nonstock_id 
+            FOREIGN KEY (nonstock_id) REFERENCES nonstocks(id);
+    END IF;
+END $$;
+-- Index
+CREATE INDEX idx_project_budgets_project_flock_id ON project_budgets (project_flock_id);
+
+CREATE INDEX idx_project_budgets_nonstock_id ON project_budgets (nonstock_id);

internal/database/migrations/20251128081118_add_closing_project_flock_kandangs.down.sql

@@ -0,0 +1,3 @@
+DROP INDEX IF EXISTS idx_project_flock_kandangs_closed_at;
+ALTER TABLE project_flock_kandangs
+    DROP COLUMN IF EXISTS closed_at;

internal/database/migrations/20251128081118_add_closing_project_flock_kandangs.up.sql

@@ -0,0 +1,5 @@
+ALTER TABLE project_flock_kandangs
+    ADD COLUMN IF NOT EXISTS closed_at TIMESTAMPTZ;
+
+CREATE INDEX IF NOT EXISTS idx_project_flock_kandangs_closed_at
+    ON project_flock_kandangs (closed_at);

internal/database/migrations/20251201140316_add_is_visible_to_products.down.sql

@@ -0,0 +1,2 @@
+ALTER TABLE products
+DROP COLUMN IF EXISTS is_visible;

internal/database/migrations/20251201140316_add_is_visible_to_products.up.sql

@@ -0,0 +1,2 @@
+ALTER TABLE products
+ADD COLUMN IF NOT EXISTS is_visible BOOLEAN NOT NULL DEFAULT TRUE;

internal/database/migrations/20251202103838_create_document_table.down.sql

@@ -0,0 +1,2 @@
+DROP INDEX IF EXISTS documents_documentable_polymorphic;
+DROP TABLE IF EXISTS documents;

internal/database/migrations/20251202103838_create_document_table.up.sql

@@ -0,0 +1,14 @@
+CREATE TABLE documents (
+    id BIGSERIAL PRIMARY KEY,
+    documentable_type VARCHAR(50) NOT NULL,
+    documentable_id BIGINT NOT NULL,
+    type VARCHAR(50) NOT NULL,
+    path VARCHAR(50) NOT NULL,
+    name VARCHAR(50) NOT NULL,
+    ext VARCHAR(50) NOT NULL,
+    size NUMERIC(15, 3) NOT NULL,
+    created_by BIGINT REFERENCES users (id) ON DELETE RESTRICT ON UPDATE CASCADE,
+    created_at TIMESTAMPTZ DEFAULT now()
+);
+
+CREATE INDEX documents_documentable_polymorphic ON documents (documentable_type, documentable_id);

internal/database/migrations/20251203083339_add_project_flock_kandang_to_product_warehouses.down.sql

@@ -0,0 +1,35 @@
+BEGIN;
+
+-- Drop new indexes and FK
+DROP INDEX IF EXISTS idx_product_warehouses_project_flock_kandang_id;
+DROP INDEX IF EXISTS idx_product_warehouses_unique;
+
+ALTER TABLE product_warehouses
+    DROP CONSTRAINT IF EXISTS fk_product_warehouses_project_flock_kandang_id,
+    ALTER COLUMN project_flock_kandang_id DROP NOT NULL,
+    DROP COLUMN IF EXISTS project_flock_kandang_id;
+
+-- Revert qty to integer quantity
+ALTER TABLE product_warehouses
+    RENAME COLUMN qty TO quantity;
+
+ALTER TABLE product_warehouses
+    ALTER COLUMN quantity TYPE INTEGER USING quantity::integer,
+    ALTER COLUMN quantity SET DEFAULT 0,
+    ALTER COLUMN quantity SET NOT NULL;
+
+-- Restore audit/soft-delete columns
+ALTER TABLE product_warehouses
+    ADD COLUMN IF NOT EXISTS created_by BIGINT REFERENCES users (id),
+    ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ DEFAULT NOW(),
+    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ DEFAULT NOW(),
+    ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
+
+-- Recreate prior indexes
+CREATE INDEX IF NOT EXISTS idx_product_warehouses_deleted_at ON product_warehouses (deleted_at);
+
+CREATE UNIQUE INDEX IF NOT EXISTS idx_product_warehouses_unique
+    ON product_warehouses (product_id, warehouse_id)
+    WHERE deleted_at IS NULL;
+
+COMMIT;

internal/database/migrations/20251203083339_add_project_flock_kandang_to_product_warehouses.up.sql

@@ -0,0 +1,41 @@
+BEGIN;
+
+-- Drop indexes that depend on deleted_at or old uniqueness
+DROP INDEX IF EXISTS idx_product_warehouses_deleted_at;
+DROP INDEX IF EXISTS idx_product_warehouses_unique;
+
+-- Add new relation and adjust quantity column
+ALTER TABLE product_warehouses
+    ADD COLUMN IF NOT EXISTS project_flock_kandang_id BIGINT;
+
+ALTER TABLE product_warehouses
+    RENAME COLUMN quantity TO qty;
+
+-- Enforce numeric quantity with precision and default
+ALTER TABLE product_warehouses
+    ALTER COLUMN qty TYPE NUMERIC(15, 3) USING qty::numeric(15, 3),
+    ALTER COLUMN qty SET DEFAULT 0,
+    ALTER COLUMN qty SET NOT NULL;
+
+-- Remove audit/soft-delete columns no longer used
+ALTER TABLE product_warehouses
+    DROP COLUMN IF EXISTS created_by,
+    DROP COLUMN IF EXISTS created_at,
+    DROP COLUMN IF EXISTS updated_at,
+    DROP COLUMN IF EXISTS deleted_at;
+
+-- Enforce FK and not-null for project_flock_kandang_id
+ALTER TABLE product_warehouses
+    ADD CONSTRAINT fk_product_warehouses_project_flock_kandang_id
+        FOREIGN KEY (project_flock_kandang_id)
+        REFERENCES project_flock_kandangs (id)
+        ON DELETE RESTRICT ON UPDATE CASCADE;
+
+-- New indexes
+CREATE INDEX IF NOT EXISTS idx_product_warehouses_project_flock_kandang_id
+    ON product_warehouses (project_flock_kandang_id);
+
+CREATE UNIQUE INDEX IF NOT EXISTS idx_product_warehouses_unique
+    ON product_warehouses (product_id, warehouse_id, project_flock_kandang_id);
+
+COMMIT;

internal/database/migrations/20251203103853_update_stock_logs_schema.down.sql

@@ -0,0 +1,44 @@
+BEGIN;
+
+-- Drop new indexes
+DROP INDEX IF EXISTS stock_logs_loggable_type_loggable_id_idx;
+DROP INDEX IF EXISTS stock_logs_product_warehouse_id_idx;
+DROP INDEX IF EXISTS stock_logs_created_by_idx;
+DROP INDEX IF EXISTS stock_logs_created_at_idx;
+
+-- Restore obsolete columns
+ALTER TABLE stock_logs
+    ADD COLUMN IF NOT EXISTS transaction_type VARCHAR(20) DEFAULT '' NOT NULL,
+    ADD COLUMN IF NOT EXISTS quantity NUMERIC(15, 3) DEFAULT 0 NOT NULL,
+    ADD COLUMN IF NOT EXISTS before_quantity NUMERIC(15, 3) DEFAULT 0 NOT NULL,
+    ADD COLUMN IF NOT EXISTS after_quantity NUMERIC(15, 3) DEFAULT 0 NOT NULL,
+    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ DEFAULT NOW(),
+    ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
+
+-- Rename columns back
+ALTER TABLE stock_logs
+    RENAME COLUMN loggable_type TO log_type;
+
+ALTER TABLE stock_logs
+    RENAME COLUMN loggable_id TO log_id;
+
+ALTER TABLE stock_logs
+    RENAME COLUMN notes TO note;
+
+-- Drop new columns
+ALTER TABLE stock_logs
+    DROP COLUMN IF EXISTS increase,
+    DROP COLUMN IF EXISTS decrease;
+
+-- Restore indexes for old structure
+CREATE INDEX IF NOT EXISTS stock_logs_product_warehouse_id_idx ON stock_logs (product_warehouse_id);
+
+CREATE INDEX IF NOT EXISTS stock_logs_log_type_log_id_idx ON stock_logs (log_type, log_id);
+
+CREATE INDEX IF NOT EXISTS stock_logs_created_by_idx ON stock_logs (created_by);
+
+CREATE INDEX IF NOT EXISTS stock_logs_created_at_idx ON stock_logs (created_at);
+
+CREATE INDEX IF NOT EXISTS stock_logs_deleted_at_idx ON stock_logs (deleted_at);
+
+COMMIT;

internal/database/migrations/20251203103853_update_stock_logs_schema.up.sql

@@ -0,0 +1,50 @@
+BEGIN;
+
+-- Drop old indexes tied to removed columns
+DROP INDEX IF EXISTS stock_logs_log_type_log_id_idx;
+DROP INDEX IF EXISTS stock_logs_deleted_at_idx;
+
+-- Rename columns to new naming
+ALTER TABLE stock_logs
+    RENAME COLUMN log_type TO loggable_type;
+
+ALTER TABLE stock_logs
+    RENAME COLUMN log_id TO loggable_id;
+
+ALTER TABLE stock_logs
+    RENAME COLUMN note TO notes;
+
+-- Add new increase/decrease columns
+ALTER TABLE stock_logs
+    ADD COLUMN IF NOT EXISTS increase NUMERIC(15, 3) DEFAULT 0,
+    ADD COLUMN IF NOT EXISTS decrease NUMERIC(15, 3) DEFAULT 0;
+
+-- Adjust column definitions
+ALTER TABLE stock_logs
+    ALTER COLUMN loggable_type TYPE VARCHAR(50),
+    ALTER COLUMN loggable_type SET NOT NULL,
+    ALTER COLUMN loggable_id SET NOT NULL,
+    ALTER COLUMN increase SET DEFAULT 0,
+    ALTER COLUMN increase SET NOT NULL,
+    ALTER COLUMN decrease SET DEFAULT 0,
+    ALTER COLUMN decrease SET NOT NULL;
+
+-- Remove obsolete columns
+ALTER TABLE stock_logs
+    DROP COLUMN IF EXISTS transaction_type,
+    DROP COLUMN IF EXISTS quantity,
+    DROP COLUMN IF EXISTS before_quantity,
+    DROP COLUMN IF EXISTS after_quantity,
+    DROP COLUMN IF EXISTS updated_at,
+    DROP COLUMN IF EXISTS deleted_at;
+
+-- Recreate indexes for new structure
+CREATE INDEX IF NOT EXISTS stock_logs_product_warehouse_id_idx ON stock_logs (product_warehouse_id);
+
+CREATE INDEX IF NOT EXISTS stock_logs_loggable_type_loggable_id_idx ON stock_logs (loggable_type, loggable_id);
+
+CREATE INDEX IF NOT EXISTS stock_logs_created_by_idx ON stock_logs (created_by);
+
+CREATE INDEX IF NOT EXISTS stock_logs_created_at_idx ON stock_logs (created_at);
+
+COMMIT;

internal/database/migrations/20251203145514_adjustment_recording_without_grading_eggs.down.sql

@@ -0,0 +1,33 @@
+BEGIN;
+
+-- Remove grading details from recording_eggs
+ALTER TABLE recording_eggs
+    DROP CONSTRAINT IF EXISTS chk_recording_eggs_qty;
+
+ALTER TABLE recording_eggs
+    DROP COLUMN IF EXISTS weight;
+
+ALTER TABLE recording_eggs
+    ADD CONSTRAINT chk_recording_eggs_qty CHECK (qty >= 0);
+
+-- Restore grading_eggs table for rollback scenarios
+CREATE TABLE grading_eggs (
+    id                BIGSERIAL PRIMARY KEY,
+    recording_egg_id  BIGINT NOT NULL,
+    qty               NUMERIC(15,3) NOT NULL,
+    grade             VARCHAR,
+    created_by        BIGINT,
+    created_at        TIMESTAMPTZ DEFAULT NOW(),
+    updated_at        TIMESTAMPTZ DEFAULT NOW(),
+
+    CONSTRAINT fk_grading_eggs_recording_egg
+        FOREIGN KEY (recording_egg_id) REFERENCES recording_eggs(id) ON DELETE CASCADE,
+    CONSTRAINT fk_grading_eggs_created_by
+        FOREIGN KEY (created_by) REFERENCES users(id),
+    CONSTRAINT chk_grading_eggs_qty CHECK (qty >= 0)
+);
+
+CREATE INDEX idx_grading_eggs_recording_egg
+    ON grading_eggs (recording_egg_id);
+
+COMMIT;

internal/database/migrations/20251203145514_adjustment_recording_without_grading_eggs.up.sql

@@ -0,0 +1,18 @@
+BEGIN;
+
+-- Remove separate grading table and move grading details into recording_eggs
+DROP INDEX IF EXISTS idx_grading_eggs_recording_egg;
+DROP TABLE IF EXISTS grading_eggs;
+
+ALTER TABLE recording_eggs
+    ADD COLUMN IF NOT EXISTS weight NUMERIC(10,3);
+
+ALTER TABLE recording_eggs
+    DROP CONSTRAINT IF EXISTS chk_recording_eggs_qty;
+
+ALTER TABLE recording_eggs
+    ADD CONSTRAINT chk_recording_eggs_qty CHECK (
+        qty >= 0 AND (weight IS NULL OR weight >= 0)
+    );
+
+COMMIT;

internal/database/migrations/20251204193903_adjustment_purchase_expedition.down.sql

@@ -0,0 +1,38 @@
+DO $$
+BEGIN
+    IF EXISTS (
+        SELECT 1 FROM pg_constraint WHERE conname = 'fk_purchase_items_expense_nonstock'
+    ) THEN
+        ALTER TABLE purchase_items
+            DROP CONSTRAINT fk_purchase_items_expense_nonstock;
+    END IF;
+    IF EXISTS (
+        SELECT 1 FROM pg_constraint WHERE conname = 'fk_purchase_items_project_flock_kandang'
+    ) THEN
+        ALTER TABLE purchase_items
+            DROP CONSTRAINT fk_purchase_items_project_flock_kandang;
+    END IF;
+END $$;
+
+DROP INDEX IF EXISTS idx_purchase_items_expense_nonstock_id;
+DROP INDEX IF EXISTS idx_purchase_items_project_flock_kandang_id;
+
+ALTER TABLE purchase_items
+    DROP COLUMN IF EXISTS expense_nonstock_id,
+    DROP COLUMN IF EXISTS project_flock_kandang_id,
+    ALTER COLUMN vehicle_number DROP NOT NULL,
+    ALTER COLUMN vehicle_number TYPE VARCHAR USING vehicle_number;
+
+ALTER TABLE purchases
+    ALTER COLUMN pr_number TYPE VARCHAR USING pr_number,
+    ALTER COLUMN po_number TYPE VARCHAR USING po_number,
+    ALTER COLUMN created_at DROP DEFAULT,
+    ALTER COLUMN updated_at DROP DEFAULT;
+
+ALTER TABLE purchases
+    ADD COLUMN credit_term INT NOT NULL DEFAULT 0,
+    ADD COLUMN grand_total NUMERIC(15, 3) NOT NULL DEFAULT 0;
+
+ALTER TABLE purchases
+    ALTER COLUMN credit_term DROP DEFAULT,
+    ALTER COLUMN grand_total DROP DEFAULT;

internal/database/migrations/20251204193903_adjustment_purchase_expedition.up.sql

@@ -0,0 +1,57 @@
+-- Adjust purchases table to new purchasing schema
+ALTER TABLE purchases
+    ALTER COLUMN pr_number TYPE VARCHAR(50) USING LEFT(pr_number, 50),
+    ALTER COLUMN po_number TYPE VARCHAR(50) USING LEFT(po_number, 50),
+    ALTER COLUMN created_at SET DEFAULT now(),
+    ALTER COLUMN updated_at SET DEFAULT now();
+
+ALTER TABLE purchases
+    DROP COLUMN IF EXISTS credit_term,
+    DROP COLUMN IF EXISTS grand_total;
+
+-- Bring purchase_items in line with new requirements
+ALTER TABLE purchase_items
+    ADD COLUMN IF NOT EXISTS expense_nonstock_id BIGINT,
+    ADD COLUMN IF NOT EXISTS project_flock_kandang_id BIGINT;
+
+UPDATE purchase_items
+SET vehicle_number = ''
+WHERE vehicle_number IS NULL;
+
+ALTER TABLE purchase_items
+    ALTER COLUMN vehicle_number TYPE VARCHAR(10) USING LEFT(vehicle_number, 10),
+    ALTER COLUMN vehicle_number SET NOT NULL;
+
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'expense_nonstocks') THEN
+        IF NOT EXISTS (
+            SELECT 1 FROM pg_constraint WHERE conname = 'fk_purchase_items_expense_nonstock'
+        ) THEN
+            EXECUTE
+            'ALTER TABLE purchase_items
+             ADD CONSTRAINT fk_purchase_items_expense_nonstock
+             FOREIGN KEY (expense_nonstock_id)
+             REFERENCES expense_nonstocks(id)
+             ON DELETE SET NULL ON UPDATE CASCADE';
+        END IF;
+    END IF;
+
+    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'project_flock_kandangs') THEN
+        IF NOT EXISTS (
+            SELECT 1 FROM pg_constraint WHERE conname = 'fk_purchase_items_project_flock_kandang'
+        ) THEN
+            EXECUTE
+            'ALTER TABLE purchase_items
+             ADD CONSTRAINT fk_purchase_items_project_flock_kandang
+             FOREIGN KEY (project_flock_kandang_id)
+             REFERENCES project_flock_kandangs(id)
+             ON DELETE SET NULL ON UPDATE CASCADE';
+        END IF;
+    END IF;
+END $$;
+
+CREATE INDEX IF NOT EXISTS idx_purchase_items_expense_nonstock_id
+    ON purchase_items (expense_nonstock_id);
+CREATE INDEX IF NOT EXISTS idx_purchase_items_project_flock_kandang_id
+    ON purchase_items (project_flock_kandang_id);

internal/database/migrations/20251210044651_create_so_number_sequence.down.sql

@@ -0,0 +1,3 @@
+-- Drop function and sequence for sales order numbers
+DROP SEQUENCE IF EXISTS so_number_seq;
+DROP FUNCTION IF EXISTS generate_so_number();

internal/database/migrations/20251210044651_create_so_number_sequence.up.sql

@@ -0,0 +1,12 @@
+-- Create sequence for sales order numbers
+CREATE SEQUENCE so_number_seq START WITH 1 INCREMENT BY 1;
+
+CREATE OR REPLACE FUNCTION generate_so_number()
+RETURNS VARCHAR AS $$
+DECLARE
+    next_val INTEGER;
+BEGIN
+    next_val := nextval('so_number_seq');
+    RETURN 'SO-' || LPAD(next_val::TEXT, 5, '0');
+END;
+$$ LANGUAGE plpgsql;

internal/database/migrations/20251210125335_add_column_credit_term_purchase_table.down.sql

@@ -0,0 +1,2 @@
+ALTER TABLE purchases
+    DROP COLUMN IF EXISTS credit_term;

internal/database/migrations/20251210125335_add_column_credit_term_purchase_table.up.sql

@@ -0,0 +1,5 @@
+ALTER TABLE purchases
+    ADD COLUMN IF NOT EXISTS credit_term INT NOT NULL DEFAULT 0;
+
+ALTER TABLE purchases
+    ALTER COLUMN credit_term DROP DEFAULT;

internal/database/migrations/20251224033033_create_payment_table.down.sql

@@ -0,0 +1,3 @@
+DROP INDEX IF EXISTS idx_payments_bank_id;
+DROP INDEX IF EXISTS payments_party_polymorphic;
+DROP TABLE IF EXISTS payments;

internal/database/migrations/20251224033033_create_payment_table.up.sql

@@ -0,0 +1,22 @@
+CREATE TABLE IF NOT EXISTS payments (
+    id BIGSERIAL PRIMARY KEY,
+    payment_code VARCHAR(50) NOT NULL,
+    reference_number VARCHAR(100) NULL,
+    transaction_type VARCHAR(50),
+    party_type VARCHAR(50) NOT NULL,
+    party_id BIGINT NOT NULL,
+    payment_date TIMESTAMPTZ NOT NULL,
+    payment_method VARCHAR(20) NOT NULL,
+    bank_id BIGINT NULL REFERENCES banks(id) ON DELETE RESTRICT ON UPDATE CASCADE,
+    direction VARCHAR(5) NOT NULL,
+    nominal NUMERIC(15, 3) NOT NULL,
+    notes TEXT NOT NULL,
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    deleted_at TIMESTAMPTZ NULL,
+    created_by BIGINT REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE
+);
+
+-- Indexes
+CREATE INDEX payments_party_polymorphic ON payments (party_type, party_id);
+CREATE INDEX idx_payments_bank_id ON payments (bank_id);

internal/database/migrations/20251224043019_add_soft_delete_fk_triggers.down.sql

@@ -0,0 +1,18 @@
+DO $$
+DECLARE
+    r record;
+    trigger_name text;
+BEGIN
+    FOR r IN
+        SELECT table_schema, table_name
+        FROM information_schema.columns
+        WHERE column_name = 'deleted_at'
+          AND table_schema = 'public'
+        GROUP BY table_schema, table_name
+    LOOP
+        trigger_name := format('trg_soft_delete_fk_%s', r.table_name);
+        EXECUTE format('DROP TRIGGER IF EXISTS %I ON %I.%I', trigger_name, r.table_schema, r.table_name);
+    END LOOP;
+END $$;
+
+DROP FUNCTION IF EXISTS soft_delete_handle_fk();

internal/database/migrations/20251224043019_add_soft_delete_fk_triggers.up.sql

@@ -0,0 +1,126 @@
+CREATE OR REPLACE FUNCTION soft_delete_handle_fk() RETURNS TRIGGER AS $$
+DECLARE
+    fk record;
+    child_column text;
+    parent_column text;
+    parent_value text;
+    child_has_deleted_at boolean;
+    ref_exists boolean;
+    sql text;
+BEGIN
+    IF OLD.deleted_at IS NULL AND NEW.deleted_at IS NOT NULL THEN
+        FOR fk IN
+            SELECT conrelid::regclass AS child_table,
+                   conkey AS child_cols,
+                   confkey AS parent_cols,
+                   confdeltype
+            FROM pg_constraint
+            WHERE contype = 'f'
+              AND confrelid = TG_RELID
+        LOOP
+            IF array_length(fk.child_cols, 1) IS DISTINCT FROM 1
+                OR array_length(fk.parent_cols, 1) IS DISTINCT FROM 1 THEN
+                RAISE NOTICE 'soft_delete_handle_fk skipped composite fk on %', fk.child_table;
+                CONTINUE;
+            END IF;
+
+            SELECT attname INTO child_column
+            FROM pg_attribute
+            WHERE attrelid = fk.child_table
+              AND attnum = fk.child_cols[1]
+              AND NOT attisdropped;
+
+            SELECT attname INTO parent_column
+            FROM pg_attribute
+            WHERE attrelid = TG_RELID
+              AND attnum = fk.parent_cols[1]
+              AND NOT attisdropped;
+
+            EXECUTE format('SELECT ($1).%I', parent_column)
+                INTO parent_value
+                USING OLD;
+
+            SELECT EXISTS (
+                SELECT 1
+                FROM pg_attribute
+                WHERE attrelid = fk.child_table
+                  AND attname = 'deleted_at'
+                  AND NOT attisdropped
+            ) INTO child_has_deleted_at;
+
+            IF fk.confdeltype IN ('r', 'a') THEN
+                sql := format(
+                    'SELECT EXISTS (SELECT 1 FROM %s WHERE %I = $1 %s)',
+                    fk.child_table,
+                    child_column,
+                    CASE WHEN child_has_deleted_at THEN 'AND deleted_at IS NULL' ELSE '' END
+                );
+                EXECUTE sql INTO ref_exists USING parent_value;
+                IF ref_exists THEN
+                    RAISE EXCEPTION 'Cannot soft delete %, still referenced by %',
+                        TG_TABLE_NAME, fk.child_table;
+                END IF;
+            ELSIF fk.confdeltype = 'n' THEN
+                sql := format(
+                    'UPDATE %s SET %I = NULL WHERE %I = $1 %s',
+                    fk.child_table,
+                    child_column,
+                    child_column,
+                    CASE WHEN child_has_deleted_at THEN 'AND deleted_at IS NULL' ELSE '' END
+                );
+                EXECUTE sql USING parent_value;
+            ELSIF fk.confdeltype = 'c' THEN
+                IF child_has_deleted_at THEN
+                    sql := format(
+                        'UPDATE %s SET deleted_at = NOW() WHERE %I = $1 AND deleted_at IS NULL',
+                        fk.child_table,
+                        child_column
+                    );
+                    EXECUTE sql USING parent_value;
+                ELSE
+                    sql := format(
+                        'DELETE FROM %s WHERE %I = $1',
+                        fk.child_table,
+                        child_column
+                    );
+                    EXECUTE sql USING parent_value;
+                END IF;
+            ELSIF fk.confdeltype = 'd' THEN
+                sql := format(
+                    'UPDATE %s SET %I = DEFAULT WHERE %I = $1 %s',
+                    fk.child_table,
+                    child_column,
+                    child_column,
+                    CASE WHEN child_has_deleted_at THEN 'AND deleted_at IS NULL' ELSE '' END
+                );
+                EXECUTE sql USING parent_value;
+            END IF;
+        END LOOP;
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+DO $$
+DECLARE
+    r record;
+    trigger_name text;
+BEGIN
+    FOR r IN
+        SELECT table_schema, table_name
+        FROM information_schema.columns
+        WHERE column_name = 'deleted_at'
+          AND table_schema = 'public'
+        GROUP BY table_schema, table_name
+    LOOP
+        trigger_name := format('trg_soft_delete_fk_%s', r.table_name);
+        EXECUTE format('DROP TRIGGER IF EXISTS %I ON %I.%I', trigger_name, r.table_schema, r.table_name);
+        EXECUTE format(
+            'CREATE TRIGGER %I BEFORE UPDATE OF deleted_at ON %I.%I FOR EACH ROW EXECUTE FUNCTION soft_delete_handle_fk()',
+            trigger_name,
+            r.table_schema,
+            r.table_name
+        );
+    END LOOP;
+END $$;

internal/database/migrations/20251224130000_create_payment_code_sequence.down.sql

@@ -0,0 +1 @@
+DROP SEQUENCE IF EXISTS payments_code_seq;

internal/database/migrations/20251224130000_create_payment_code_sequence.up.sql

@@ -0,0 +1 @@
+CREATE SEQUENCE IF NOT EXISTS payments_code_seq START WITH 1 INCREMENT BY 1;

internal/database/migrations/20251226031727_alter_table_expense_delete_document.down.sql

@@ -0,0 +1,3 @@
+-- Rollback: restore document columns to expenses table
+ALTER TABLE expenses ADD COLUMN IF NOT EXISTS document_path JSON;
+ALTER TABLE expenses ADD COLUMN IF NOT EXISTS realization_document_path JSON;

internal/database/migrations/20251226031727_alter_table_expense_delete_document.up.sql

@@ -0,0 +1,3 @@
+-- Delete document columns from expenses table since we now use Document service with polymorphic relations
+ALTER TABLE expenses DROP COLUMN IF EXISTS document_path;
+ALTER TABLE expenses DROP COLUMN IF EXISTS realization_document_path;

internal/database/migrations/20251226054852_add_fifo_fields_to_marketing_delivery_products.down.sql

@@ -0,0 +1,28 @@
+-- ============================================
+-- Rollback: Remove FIFO fields and restore qty column
+-- ============================================
+
+-- STEP 1: Drop indexes
+DROP INDEX IF EXISTS idx_marketing_delivery_products_fifo_lookup;
+DROP INDEX IF EXISTS idx_marketing_delivery_products_pending_qty;
+DROP INDEX IF EXISTS idx_marketing_delivery_products_usage_qty;
+DROP INDEX IF EXISTS idx_marketing_delivery_products_created_at;
+
+-- STEP 2: Drop constraints
+ALTER TABLE marketing_delivery_products
+DROP CONSTRAINT IF EXISTS chk_marketing_delivery_products_fifo_nonneg;
+
+-- STEP 3: Restore qty column from usage_qty data
+ALTER TABLE marketing_delivery_products
+ADD COLUMN IF NOT EXISTS qty NUMERIC(15, 3) DEFAULT 0 NOT NULL;
+
+-- Migrate data back from usage_qty to qty
+UPDATE marketing_delivery_products
+SET qty = usage_qty
+WHERE qty = 0;
+
+-- STEP 4: Drop FIFO columns
+ALTER TABLE marketing_delivery_products
+DROP COLUMN IF EXISTS usage_qty,
+DROP COLUMN IF EXISTS pending_qty,
+DROP COLUMN IF EXISTS created_at;