SalesTracker — CRUD с аналитикой продаж

Обзор задачи

Необходимо реализовать сервис для учёта финансовых транзакций с возможностью получения аналитических агрегатов. Система включает REST API, работу с SQL для аналитики и минимальный веб-интерфейс.

Архитектура решения

┌─────────────┐     ┌──────────────┐     ┌─────────────┐
│   Frontend  │────▶│   Go API     │────▶│ PostgreSQL  │
│  (HTML/JS)  │◀────│  (Chi/Gin)   │◀────│             │
└─────────────┘     └──────────────┘     └─────────────┘

1. Модель данных

CREATE TABLE transactions (
    id          BIGSERIAL PRIMARY KEY,
    amount      NUMERIC(15,2) NOT NULL CHECK (amount > 0),
    description TEXT NOT NULL DEFAULT '',
    category    VARCHAR(100) NOT NULL DEFAULT 'other',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_transactions_created_at ON transactions(created_at);
CREATE INDEX idx_transactions_category ON transactions(category);

2. REST API

CRUD эндпоинты

// POST /items — создание транзакции
func CreateTransaction(w http.ResponseWriter, r *http.Request) {
    var req struct {
        Amount      float64 `json:"amount"`
        Description string  `json:"description"`
        Category    string  `json:"category"`
    }
    if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
        http.Error(w, "invalid request body", http.StatusBadRequest)
        return
    }
    if req.Amount <= 0 {
        http.Error(w, "amount must be positive", http.StatusBadRequest)
        return
    }
    // валидация категории
    validCategories := map[string]bool{"food":true, "transport":true, "entertainment":true, "other":true}
    if !validCategories[req.Category] {
        http.Error(w, "invalid category", http.StatusBadRequest)
        return
    }
    // сохранение в БД
    var id int64
    err := db.QueryRow(
        `INSERT INTO transactions (amount, description, category) 
         VALUES ($1, $2, $3) RETURNING id`,
        req.Amount, req.Description, req.Category,
    ).Scan(&id)
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }
    w.WriteHeader(http.StatusCreated)
    json.NewEncoder(w).Encode(map[string]int64{"id": id})
}

// GET /items — список с пагинацией и фильтрацией
func ListTransactions(w http.ResponseWriter, r *http.Request) {
    page, _ := strconv.Atoi(r.URL.Query().Get("page"))
    if page < 1 { page = 1 }
    limit, _ := strconv.Atoi(r.URL.Query().Get("limit"))
    if limit < 1 || limit > 100 { limit = 20 }
    offset := (page - 1) * limit

    category := r.URL.Query().Get("category")
    dateFrom := r.URL.Query().Get("date_from")
    dateTo := r.URL.Query().Get("date_to")

    query := `SELECT id, amount, description, category, created_at, updated_at 
              FROM transactions WHERE 1=1`
    args := []interface{}{}
    argIdx := 1

    if category != "" {
        query += fmt.Sprintf(" AND category = $%d", argIdx)
        args = append(args, category)
        argIdx++
    }
    if dateFrom != "" {
        query += fmt.Sprintf(" AND created_at >= $%d", argIdx)
        args = append(args, dateFrom)
        argIdx++
    }
    if dateTo != "" {
        query += fmt.Sprintf(" AND created_at <= $%d", argIdx)
        args = append(args, dateTo)
        argIdx++
    }

    query += ` ORDER BY created_at DESC`
    query += fmt.Sprintf(" LIMIT $%d OFFSET $%d", argIdx, argIdx+1)
    args = append(args, limit, offset)

    rows, err := db.Query(query, args...)
    // обработка rows...
}

// PUT /items/{id} — обновление
func UpdateTransaction(w http.ResponseWriter, r *http.Request) {
    id, _ := strconv.ParseInt(chi.URLParam(r, "id"), 10, 64)
    var req struct {
        Amount      *float64 `json:"amount"`
        Description *string  `json:"description"`
        Category    *string  `json:"category"`
    }
    if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
        http.Error(w, "invalid request", http.StatusBadRequest)
        return
    }
    // динамическое построение SET
    setClauses := []string{}
    args := []interface{}{}
    argIdx := 1

    if req.Amount != nil {
        if *req.Amount <= 0 {
            http.Error(w, "amount must be positive", http.StatusBadRequest)
            return
        }
        setClauses = append(setClauses, fmt.Sprintf("amount = $%d", argIdx))
        args = append(args, *req.Amount)
        argIdx++
    }
    // аналогично для description и category
    setClauses = append(setClauses, "updated_at = NOW()")

    query := fmt.Sprintf(`UPDATE transactions SET %s WHERE id = $%d`,
        strings.Join(setClauses, ", "), argIdx)
    args = append(args, id)

    _, err := db.Exec(query, args...)
    // обработка ошибок...
}

// DELETE /items/{id} — удаление
func DeleteTransaction(w http.ResponseWriter, r *http.Request) {
    id, _ := strconv.ParseInt(chi.URLParam(r, "id"), 10, 64)
    result, err := db.Exec("DELETE FROM transactions WHERE id = $1", id)
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }
    rowsAffected, _ := result.RowsAffected()
    if rowsAffected == 0 {
        http.Error(w, "not found", http.StatusNotFound)
        return
    }
    w.WriteHeader(http.StatusNoContent)
}

3. Аналитические запросы

// GET /analytics?date_from=...&date_to=...
func GetAnalytics(w http.ResponseWriter, r *http.Request) {
    dateFrom := r.URL.Query().Get("date_from")
    dateTo := r.URL.Query().Get("date_to")

    query := `
    WITH filtered AS (
        SELECT amount FROM transactions 
        WHERE created_at >= $1 AND created_at <= $2
    ),
    ordered AS (
        SELECT amount, 
               ROW_NUMBER() OVER (ORDER BY amount) as row_num,
               COUNT(*) OVER() as total_count
        FROM filtered
    )
    SELECT 
        COALESCE(SUM(amount), 0) as total_sum,
        COALESCE(AVG(amount), 0) as avg_amount,
        COUNT(*) as transaction_count,
        AVG(CASE 
            WHEN row_num IN ((total_count+1)/2, (total_count+2)/2) 
            THEN amount 
        END) as median,
        percentile_cont(0.90) WITHIN GROUP (ORDER BY amount) as percentile_90
    FROM ordered
    GROUP BY true
    `

    var result struct {
        TotalSum        float64 `json:"total_sum"`
        AvgAmount       float64 `json:"avg_amount"`
        TransactionCount int64  `json:"transaction_count"`
        Median          float64 `json:"median"`
        Percentile90    float64 `json:"percentile_90"`
    }

    err := db.QueryRow(query, dateFrom, dateTo).Scan(
        &result.TotalSum,
        &result.AvgAmount,
        &result.TransactionCount,
        &result.Median,
        &result.Percentile90,
    )
    // обработка ошибок...
    json.NewEncoder(w).Encode(result)
}

4. Экспорт CSV

// GET /export?date_from=...&date_to=...
func ExportCSV(w http.ResponseWriter, r *http.Request) {
    dateFrom := r.URL.Query().Get("date_from")
    dateTo := r.URL.Query().Get("date_to")

    w.Header().Set("Content-Type", "text/csv")
    w.Header().Set("Content-Disposition", "attachment; filename=transactions.csv")

    writer := csv.NewWriter(w)
    writer.Write([]string{"ID", "Amount", "Description", "Category", "Created At"})

    rows, _ := db.Query(
        `SELECT id, amount, description, category, created_at 
         FROM transactions WHERE created_at >= $1 AND created_at <= $2
         ORDER BY created_at`,
        dateFrom, dateTo,
    )
    defer rows.Close()

    for rows.Next() {
        var id int64
        var amount float64
        var desc, cat string
        var createdAt time.Time
        rows.Scan(&id, &amount, &desc, &cat, &createdAt)
        writer.Write([]string{
            strconv.FormatInt(id, 10),
            strconv.FormatFloat(amount, 'f', 2, 64),
            desc,
            cat,
            createdAt.Format(time.RFC3339),
        })
    }
    writer.Flush()
}

5. UI (минимальный HTML/JS)

<!DOCTYPE html>
<html>
<head>
    <title>SalesTracker</title>
    <style>
        body { font-family: Arial; margin: 20px; }
        table { border-collapse: collapse; width: 100%; }
        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
        th { background-color: #f2f2f2; }
        .analytics { margin: 20px 0; padding: 15px; background: #f9f9f9; }
        input, select { margin: 5px; padding: 5px; }
    </style>
</head>
<body>
    <h1>SalesTracker</h1>
    
    <div id="add-form">
        <h2>Добавить транзакцию</h2>
        <input type="number" id="amount" placeholder="Сумма" step="0.01">
        <input type="text" id="description" placeholder="Описание">
        <select id="category">
            <option value="food">Еда</option>
            <option value="transport">Транспорт</option>
            <option value="entertainment">Развлечения</option>
            <option value="other">Другое</option>
        </select>
        <button onclick="addTransaction()">Добавить</button>
    </div>

    <div class="analytics">
        <h2>Аналитика</h2>
        <input type="date" id="date-from">
        <input type="date" id="date-to">
        <button onclick="loadAnalytics()">Показать</button>
        <div id="analytics-results"></div>
    </div>

    <div>
        <h2>Транзакции</h2>
        <button onclick="exportCSV()">Экспорт CSV</button>
        <table id="transactions-table">
            <thead>
                <tr>
                    <th>ID</th><th>Сумма</th><th>Описание</th>
                    <th>Категория</th><th>Дата</th><th>Действия</th>
                </tr>
            </thead>
            <tbody id="transactions-body"></tbody>
        </table>
    </div>

    <script>
        async function loadTransactions() {
            const res = await fetch('/items');
            const data = await res.json();
            // отрисовка таблицы...
        }

        async function addTransaction() {
            await fetch('/items', {
                method: 'POST',
                headers: {'Content-Type': 'application/json'},
                body: JSON.stringify({
                    amount: document.getElementById('amount').value,
                    description: document.getElementById('description').value,
                    category: document.getElementById('category').value
                })
            });
            loadTransactions();
        }

        async function loadAnalytics() {
            const from = document.getElementById('date-from').value;
            const to = document.getElementById('date-to').value;
            const res = await fetch(`/analytics?date_from=${from}&date_to=${to}`);
            const data = await res.json();
            document.getElementById('analytics-results').innerHTML = `
                <p>Сумма: ${data.total_sum}</p>
                <p>Среднее: ${data.avg_amount}</p>
                <p>Количество: ${data.transaction_count}</p>
                <p>Медиана: ${data.median}</p>
                <p>90-й перцентиль: ${data.percentile_90}</p>
            `;
        }

        function exportCSV() {
            const from = document.getElementById('date-from').value;
            const to = document.getElementById('date-to').value;
            window.location.href = `/export?date_from=${from}&date_to=${to}`;
        }

        loadTransactions();
    </script>
</body>
</html>

6. Обработка ошибок и edge cases

• Пустой результат аналитики: возвращаем нулевые значения, а не ошибку
• Некорректные даты: валидация формата, возврат 400
• Большие объёмы данных: пагинация (limit/offset), индексы на created_at
• Конкурентные обновления: используем оптимистичные блокировки или версионирование
• SQL-инъекции: параметризованные запросы (показано выше)
• Медиана для чётного количества: среднее двух центральных значений

7. Масштабирование

• Кэширование аналитики: Redis с TTL для частых периодов
• Асинхронный экспорт: для больших объёмов — фоновая задача + уведомление
• Шардирование: по диапазонам дат (created_at)
• Read replicas: аналитические запросы на реплики

Ключевые моменты для интервью

1. SQL для перцентилей: использование оконных функций и percentile_cont
2. Динамические UPDATE: безопасное построение запросов с параметрами
3. Валидация: на уровне API и БД (CHECK constraints)
4. Производительность: индексы, пагинация, EXPLAIN ANALYZE для сложных запросов
5. Обработка ошибок: единый формат ответов, логирование