bindbox-game/.planning/research/STACK.md

Technology Stack

Project: Bindbox Game — Profit/Loss Analytics Functions Researched: 2026-03-21 Scope: Service-layer multi-dimensional financial aggregation in an existing Go 1.24 / GORM 1.25 / MySQL project

---

Existing Stack (Confirmed from Codebase)

The following are already in use and must not be replaced or duplicated.

Layer	Technology	Version	Notes
Language	Go	1.24.0	toolchain go1.24.2
ORM	gorm.io/gorm	1.25.9	with gorm.io/gen v0.3.26
Database	MySQL	8.x (inferred)	read/write split via gorm.io/plugin/dbresolver
DB driver	github.com/go-sql-driver/mysql	1.7.1
Logger	go.uber.org/zap (wrapped)	1.26.0	project custom logger.CustomLogger interface
Test DB	gorm.io/driver/sqlite	1.4.3	in-memory SQLite via NewSQLiteRepoForTest()
Test assertions	github.com/stretchr/testify	1.11.1
SQL mock	github.com/DATA-DOG/go-sqlmock	1.5.2

No new runtime dependencies are required for this milestone.

---

Recommended Patterns for Analytics Functions

1. Query Execution: db.Raw() + Named Scan Struct for Complex Aggregations

Confidence: HIGH (verified from existing codebase usage in dashboard_activity.go, dashboard_spending.go)

The project already uses two GORM query styles:

Style A — GORM builder with .Select() + .Scan() (for joins + GROUP BY with multiple aggregated columns):

type revenueRow struct {
    DimensionID  int64
    TotalRevenue float64
    TotalCost    int64
}
var rows []revenueRow

db.Table(model.TableNameOrders).
    Select(`
        orders.user_id as dimension_id,
        SUM(...) as total_revenue,
        SUM(...) as total_cost
    `).
    Joins("LEFT JOIN ...").
    Where("orders.status = ?", 2).
    Group("orders.user_id").
    Scan(&rows)

Use this style when:

• Grouping by a single dimension (user_id, activity_id)
• The aggregation fits in one SQL pass
• The query does not require correlated subqueries that GORM cannot model

Style B — db.Raw() + .Scan() (for queries with inline derived tables / CTEs):

db.Raw(`
    SELECT user_id, SUM(revenue) as total_revenue
    FROM (
        SELECT user_id, actual_amount + discount_amount as revenue
        FROM orders WHERE status = 2
    ) t
    WHERE user_id IN (?)
    GROUP BY user_id
`, userIDs).Scan(&rows)

Use this style when:

• The query has two or more levels of subqueries
• GORM's builder would produce ambiguous deleted_at injection (known GORM pitfall, already documented in dashboard_activity.go comments)
• Conditional aggregation across multiple joins is complex enough to be unmaintainable in builder form

Recommendation: Use Style A (builder) as the default. Drop to Style B only when builder clarity degrades — which happens when the query has more than 2 subquery levels.

---

2. Service Constructor Pattern

Confidence: HIGH (established project pattern)

All services follow this exact signature:

package finance

import (
    "bindbox-game/internal/pkg/logger"
    "bindbox-game/internal/repository/mysql"
    "bindbox-game/internal/repository/mysql/dao"
)

type Service interface {
    QueryUserProfitLoss(ctx context.Context, params UserProfitLossParams) (*ProfitLossResult, error)
    QueryActivityProfitLoss(ctx context.Context, params ActivityProfitLossParams) (*ProfitLossResult, error)
}

type service struct {
    logger  logger.CustomLogger
    readDB  *dao.Query   // analytics always use read replica
    repo    mysql.Repo   // for direct *gorm.DB access when needed
}

func New(l logger.CustomLogger, db mysql.Repo) Service {
    return &service{
        logger: l,
        readDB: dao.Use(db.GetDbR()),
        repo:   db,
    }
}

Always use db.GetDbR() for analytics (read replica). Never use GetDbW() in the new finance service. The writeDB field should not exist in this service.

---

3. Multi-Dimensional Aggregation: Fan-Out + In-Memory Merge

Confidence: HIGH (established by DashboardPlayerSpendingLeaderboard and DashboardActivityProfitLoss)

The codebase consistently uses this pattern for analytics requiring data from multiple tables:

1. Fetch the primary dimension IDs in one query (user IDs or activity IDs)
2. Execute N parallel scan queries — one per data source (orders, inventory, draw_logs, etc.)
3. Accumulate results into a map[int64]*ResultItem
4. Apply in-memory business logic (e.g. ComputeProfit, ClassifyOrderSpending)
5. Return the merged result

// Step 1: collect IDs
dimensionIDs := []int64{...}

// Step 2: fan out — each scan targets one logical data source
var revRows []revRow
db.Table(...).Select(...).Where("user_id IN ?", dimensionIDs).Group("user_id").Scan(&revRows)

var costRows []costRow
db.Table(...).Select(...).Where("user_id IN ?", dimensionIDs).Group("user_id").Scan(&costRows)

// Step 3: merge into map
resultMap := make(map[int64]*ProfitLossResult)
for _, r := range revRows {
    resultMap[r.UserID].Revenue = r.Total
}
for _, c := range costRows {
    resultMap[c.UserID].Cost = c.Total
}

// Step 4: apply finance functions
for _, item := range resultMap {
    item.Profit, item.ProfitRate = financesvc.ComputeProfit(item.Revenue, item.Cost)
}

Why this approach over a single mega-JOIN:

• Avoids Cartesian products when joining tables with 1-to-many relationships (draw_logs × inventory × orders)
• Individual queries are independently cacheable in future
• Easier to test each data segment in isolation
• Avoids MySQL's GROUP BY optimizer struggling with multi-table fan-out

---

4. Optional Parameter Pattern with Struct

Confidence: HIGH (aligns with project idiom and Go best practices for analytics functions)

The new functions must accept all-optional parameters (no asset type = all types, no IDs = all records, no time range = all time). Use a plain struct — not variadic options or functional options — consistent with how the project already expresses request inputs:

// AssetType constants — defined in finance package
type AssetType int

const (
    AssetTypeAll      AssetType = 0 // zero value = "all types"
    AssetTypePoints   AssetType = 1
    AssetTypeCoupon   AssetType = 2
    AssetTypeItemCard AssetType = 3
    AssetTypeProduct  AssetType = 4
    AssetTypeFragment AssetType = 5
)

type UserProfitLossParams struct {
    AssetTypes []AssetType // empty = all types
    UserIDs    []int64     // empty = all users
    StartTime  *time.Time  // nil = no lower bound
    EndTime    *time.Time  // nil = no upper bound
}

type ActivityProfitLossParams struct {
    AssetTypes  []AssetType // empty = all types
    ActivityIDs []int64     // empty = all activities
    StartTime   *time.Time
    EndTime     *time.Time
}

Do NOT use time.Time zero values as sentinels — pointer semantics make optionality explicit and avoid the zero-time edge case in GORM queries.

---

5. Result Type Design

Confidence: HIGH (matches the finance domain model already in place)

// ProfitLossBreakdown is one asset-type slice within the result.
type ProfitLossBreakdown struct {
    AssetType AssetType `json:"asset_type"`
    Revenue   int64     `json:"revenue"`    // platform income (fen)
    Cost      int64     `json:"cost"`       // prize cost (fen)
    Profit    int64     `json:"profit"`     // revenue - cost (fen)
}

// ProfitLossResult is returned by both dimension functions.
type ProfitLossResult struct {
    TotalRevenue int64                 `json:"total_revenue"`
    TotalCost    int64                 `json:"total_cost"`
    TotalProfit  int64                 `json:"total_profit"`
    ProfitRate   float64               `json:"profit_rate"`
    Breakdown    []ProfitLossBreakdown `json:"breakdown"`
}

Keep all monetary values as int64 fen (1/100 RMB), consistent with the entire codebase. Never use float64 for monetary storage — only for profit rate display.

---

6. Existing Finance Utilities (Reuse, Do Not Reimplement)

Confidence: HIGH (verified in internal/service/finance/profit_metrics.go)

These functions are already tested and must be reused in the new service:

Function	Purpose
ClassifyOrderSpending(sourceType, orderNo, actualAmount, discountAmount, remark, gamePassValue)	Classifies order as game-pass or paid-coupon and returns SpendingBreakdown
IsGamePassOrder(sourceType, orderNo, actualAmount, remark)	Boolean test for game pass order
ComputeGamePassValue(drawCount, activityPrice)	Calculates game pass monetary value
ComputePrizeCostWithMultiplier(baseCost, multiplierX1000)	Applies item card multiplier to base cost
ComputeProfit(spending, prizeCost)	Returns (profit int64, profitRate float64)
NormalizeMultiplierX1000(multiplierX1000)	Clamps multiplier to minimum 1000

The new aggregation functions will call these at the per-row level when processing scan results in Go, not inside SQL expressions where possible.

---

7. SQL Aggregation Best Practices for This Codebase

Confidence: HIGH (derived from existing queries and MySQL behavior)

Use CAST(... AS SIGNED) for SUM over expressions involving division: MySQL returns DECIMAL for SUM(x / y) even when inputs are BIGINT. This causes GORM scan failures into int64. The existing code already uses CAST(SUM(...) AS SIGNED).

Use COALESCE(NULLIF(col, 0), fallback1, fallback2, 0) for value resolution: The price priority chain for inventory items is established in the codebase:

COALESCE(NULLIF(user_inventory.value_cents, 0),
         activity_reward_settings.price_snapshot_cents,
         products.price,
         0)

Always use this chain when resolving item cost — do not use products.price alone as it may be stale.

Use GREATEST(COALESCE(multiplier, 1000), 1000) for multiplier safety: Prevents zero or negative multipliers from producing incorrect cost calculations.

Avoid GORM auto-injecting deleted_at in subqueries: When writing raw subqueries inside .Joins(), explicitly add deleted_at IS NULL conditions. GORM does NOT auto-inject soft-delete conditions inside string literals passed to .Joins(). This is a known bug documented in the existing code comments.

Time range filtering — use explicit column prefix:

if params.StartTime != nil {
    db = db.Where("orders.created_at >= ?", *params.StartTime)
}
if params.EndTime != nil {
    db = db.Where("orders.created_at <= ?", *params.EndTime)
}

Always prefix column names with table names in multi-join queries to prevent ambiguous column errors.

---

8. Testing Pattern

Confidence: HIGH (established pattern in profit_metrics_test.go and testrepo_sqlite.go)

Unit tests for pure finance logic (no DB): test all functions in profit_metrics.go and the new calculation logic directly. These should cover boundary cases (zero revenue, zero cost, all-optional params, single asset type).

Integration tests for scan functions: use NewSQLiteRepoForTest() to create an in-memory SQLite DB. Note the limitations:

• SQLite does not support CAST(... AS SIGNED) — use CAST(... AS INTEGER) in test-only helper SQL, or restructure the scan to accept float64 and convert in Go
• SQLite does not support LIKE 'GP%' the same way in some edge cases — keep game-pass detection in Go-layer logic where possible, not in SQL CASE expressions during testing
• The GREATEST() MySQL function is not available in SQLite — abstract multiplier logic into Go helpers

Recommended test structure for the new service:

internal/service/finance/
├── profit_metrics.go          (existing — pure business logic, no DB)
├── profit_metrics_test.go     (existing — pure unit tests)
├── service.go                 (NEW — Service interface + constructor)
├── params.go                  (NEW — param structs, AssetType constants, result types)
├── query_user.go              (NEW — UserProfitLoss scan logic)
├── query_activity.go          (NEW — ActivityProfitLoss scan logic)
└── service_test.go            (NEW — integration tests using SQLiteRepoForTest)

Keep each query file under 300 lines. If query_user.go grows beyond that, split by data source (e.g. query_user_revenue.go, query_user_cost.go).

---

What NOT to Do

Anti-Pattern	Why	What to Do Instead
Single mega-JOIN across orders + inventory + draw_logs + products	Produces Cartesian products; MySQL optimizer struggles; query becomes unmaintainable	Fan-out into separate .Scan() calls per data source, merge in Go
float64 for monetary storage in result structs	Precision loss at large values; inconsistent with codebase	Use int64 (fen); only use float64 for display-only fields like profit_rate
Using GORM GEN query builder for complex aggregations	GEN is designed for CRUD; .Select() + .Group() via GEN is awkward for multi-table GROUP BY with conditional SUM	Use db.GetDbR().Table(...).Select(raw).Joins(...).Scan() directly
Returning raw *gorm.DB from the service layer	Leaks ORM dependency upward; breaks testability	Return typed result structs
Putting business logic (e.g. game-pass classification) inside SQL CASE expressions	Hard to test; differs between MySQL and SQLite; duplicates logic from finance package	Compute classification in Go after scanning raw amounts
Accepting time.Time{} zero value to mean "no filter"	Zero time is a valid timestamp; causes subtle bugs	Use *time.Time; nil means "no filter"
Writing analytics queries to the write DB	Unnecessary load on master; read replica exists exactly for this purpose	Always use repo.GetDbR() in analytics service
Reusing the existing dashboard handler logic directly	Dashboard logic is tightly coupled to HTTP handler, specific response shape, and pagination	Implement fresh service-layer functions with clean params/result types

---

Dependency Additions

None required. All necessary libraries are already in go.mod.

---

Sources

• Codebase analysis: internal/service/finance/profit_metrics.go (existing finance utilities)
• Codebase analysis: internal/api/admin/dashboard_activity.go (activity-dimension aggregation pattern)
• Codebase analysis: internal/api/admin/dashboard_spending.go (user-dimension aggregation pattern)
• Codebase analysis: internal/repository/mysql/mysql.go (Repo interface, DbR/DbW split)
• Codebase analysis: internal/repository/mysql/testrepo_sqlite.go (test DB pattern)
• Codebase analysis: internal/service/user/user.go (Service interface + constructor pattern)
• GORM v1.25 docs: soft-delete not injected into raw JOIN strings — HIGH confidence (matches existing code comments)
• MySQL docs: SUM() returns DECIMAL when expression involves division — HIGH confidence (matches CAST(... AS SIGNED) usage in codebase)

---

Stack analysis: 2026-03-21