Проектирование WarehouseControl

1. Анализ требований

Функциональные требования:

• CRUD для товаров (создание, чтение, обновление, удаление)
• История изменений через триггеры PostgreSQL
• Ролевая модель: admin, manager, viewer
• JWT-аутентификация
• Web UI с авторизацией

Нефункциональные требования:

• Аудит всех изменений
• Разграничение доступа
• RESTful API

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

graph TB
    subgraph Frontend
        UI[React/HTML UI]
    end
    
    subgraph Backend
        API[Go HTTP Server]
        MW[Middleware Layer]
        JWT[JWT Service]
        RBAC[Role Manager]
    end
    
    subgraph Database
        PG[(PostgreSQL)]
        TRG[Triggers]
        HIST[History Table]
    end
    
    UI --> API
    API --> MW
    MW --> JWT
    MW --> RBAC
    API --> PG
    PG --> TRG
    TRG --> HIST

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

-- Таблица пользователей
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL CHECK (role IN ('admin', 'manager', 'viewer')),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Таблица товаров
CREATE TABLE items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(200) NOT NULL,
    sku VARCHAR(50) UNIQUE NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 0,
    price DECIMAL(10,2) NOT NULL,
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Таблица истории изменений
CREATE TABLE items_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    item_id UUID NOT NULL,
    action VARCHAR(10) NOT NULL, -- INSERT, UPDATE, DELETE
    old_data JSONB,
    new_data JSONB,
    changed_by UUID REFERENCES users(id),
    changed_at TIMESTAMP DEFAULT NOW()
);

-- Триггерная функция
CREATE OR REPLACE FUNCTION log_item_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO items_history(item_id, action, new_data, changed_by)
        VALUES (NEW.id, 'INSERT', row_to_json(NEW), NEW.created_by);
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO items_history(item_id, action, old_data, new_data, changed_by)
        VALUES (OLD.id, 'UPDATE', row_to_json(OLD), row_to_json(NEW), NEW.updated_by);
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO items_history(item_id, action, old_data, changed_by)
        VALUES (OLD.id, 'DELETE', row_to_json(OLD), OLD.deleted_by);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER items_audit
    AFTER INSERT OR UPDATE OR DELETE ON items
    FOR EACH ROW EXECUTE FUNCTION log_item_changes();

4. Реализация API на Go

package main

import (
    "context"
    "database/sql"
    "encoding/json"
    "net/http"
    "strings"
    "time"
    
    "github.com/golang-jwt/jwt/v5"
    "github.com/google/uuid"
    _ "github.com/lib/pq"
)

// Модели
type Item struct {
    ID        uuid.UUID  `json:"id"`
    Name      string     `json:"name"`
    SKU       string     `json:"sku"`
    Quantity  int        `json:"quantity"`
    Price     float64    `json:"price"`
    CreatedBy uuid.UUID  `json:"created_by"`
    CreatedAt time.Time  `json:"created_at"`
    UpdatedAt time.Time  `json:"updated_at"`
}

type ItemHistory struct {
    ID        uuid.UUID       `json:"id"`
    ItemID    uuid.UUID       `json:"item_id"`
    Action    string          `json:"action"`
    OldData   json.RawMessage `json:"old_data,omitempty"`
    NewData   json.RawMessage `json:"new_data,omitempty"`
    ChangedBy uuid.UUID       `json:"changed_by"`
    ChangedAt time.Time       `json:"changed_at"`
}

// Middleware для JWT и RBAC
type contextKey string
const userContextKey contextKey = "user"

type UserClaims struct {
    jwt.RegisteredClaims
    UserID   uuid.UUID `json:"user_id"`
    Username string    `json:"username"`
    Role     string    `json:"role"`
}

func authMiddleware(jwtSecret []byte) func(http.Handler) http.Handler {
    return func(next http.Handler) http.Handler {
        return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
            authHeader := r.Header.Get("Authorization")
            if authHeader == "" {
                http.Error(w, "missing authorization header", http.StatusUnauthorized)
                return
            }

            tokenString := strings.TrimPrefix(authHeader, "Bearer ")
            claims := &UserClaims{}
            
            token, err := jwt.ParseWithClaims(tokenString, claims, func(token *jwt.Token) (interface{}, error) {
                return jwtSecret, nil
            })

            if err != nil || !token.Valid {
                http.Error(w, "invalid token", http.StatusUnauthorized)
                return
            }

            ctx := context.WithValue(r.Context(), userContextKey, claims)
            next.ServeHTTP(w, r.WithContext(ctx))
        })
    }
}

func requireRole(roles ...string) func(http.Handler) http.Handler {
    return func(next http.Handler) http.Handler {
        return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
            claims := r.Context().Value(userContextKey).(*UserClaims)
            
            for _, role := range roles {
                if claims.Role == role {
                    next.ServeHTTP(w, r)
                    return
                }
            }
            
            http.Error(w, "forbidden", http.StatusForbidden)
        })
    }
}

// CRUD Handlers
func createItem(db *sql.DB) http.HandlerFunc {
    return func(w http.ResponseWriter, r *http.Request) {
        claims := r.Context().Value(userContextKey).(*UserClaims)
        
        var item Item
        if err := json.NewDecoder(r.Body).Decode(&item); err != nil {
            http.Error(w, err.Error(), http.StatusBadRequest)
            return
        }
        
        item.ID = uuid.New()
        item.CreatedBy = claims.UserID
        item.CreatedAt = time.Now()
        item.UpdatedAt = time.Now()
        
        _, err := db.ExecContext(r.Context(),
            `INSERT INTO items (id, name, sku, quantity, price, created_by, created_at, updated_at)
             VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`,
            item.ID, item.Name, item.SKU, item.Quantity, item.Price,
            item.CreatedBy, item.CreatedAt, item.UpdatedAt)
        
        if err != nil {
            http.Error(w, err.Error(), http.StatusInternalServerError)
            return
        }
        
        w.Header().Set("Content-Type", "application/json")
        w.WriteHeader(http.StatusCreated)
        json.NewEncoder(w).Encode(item)
    }
}

func getItems(db *sql.DB) http.HandlerFunc {
    return func(w http.ResponseWriter, r *http.Request) {
        rows, err := db.QueryContext(r.Context(),
            `SELECT id, name, sku, quantity, price, created_by, created_at, updated_at 
             FROM items ORDER BY created_at DESC`)
        if err != nil {
            http.Error(w, err.Error(), http.StatusInternalServerError)
            return
        }
        defer rows.Close()
        
        var items []Item
        for rows.Next() {
            var item Item
            if err := rows.Scan(&item.ID, &item.Name, &item.SKU, &item.Quantity,
                &item.Price, &item.CreatedBy, &item.CreatedAt, &item.UpdatedAt); err != nil {
                http.Error(w, err.Error(), http.StatusInternalServerError)
                return
            }
            items = append(items, item)
        }
        
        w.Header().Set("Content-Type", "application/json")
        json.NewEncoder(w).Encode(items)
    }
}

func updateItem(db *sql.DB) http.HandlerFunc {
    return func(w http.ResponseWriter, r *http.Request) {
        claims := r.Context().Value(userContextKey).(*UserClaims)
        itemID := r.PathValue("id")
        
        var item Item
        if err := json.NewDecoder(r.Body).Decode(&item); err != nil {
            http.Error(w, err.Error(), http.StatusBadRequest)
            return
        }
        
        item.UpdatedAt = time.Now()
        
        // В триггере используем updated_by из контекста
        _, err := db.ExecContext(r.Context(),
            `UPDATE items SET name=$1, sku=$2, quantity=$3, price=$4, updated_at=$5
             WHERE id=$6`,
            item.Name, item.SKU, item.Quantity, item.Price, item.UpdatedAt, itemID)
        
        if err != nil {
            http.Error(w, err.Error(), http.StatusInternalServerError)
            return
        }
        
        w.WriteHeader(http.StatusOK)
        json.NewEncoder(w).Encode(map[string]string{"status": "updated"})
    }
}

func deleteItem(db *sql.DB) http.HandlerFunc {
    return func(w http.ResponseWriter, r *http.Request) {
        itemID := r.PathValue("id")
        
        _, err := db.ExecContext(r.Context(), "DELETE FROM items WHERE id=$1", itemID)
        if err != nil {
            http.Error(w, err.Error(), http.StatusInternalServerError)
            return
        }
        
        w.WriteHeader(http.StatusNoContent)
    }
}

func getItemHistory(db *sql.DB) http.HandlerFunc {
    return func(w http.ResponseWriter, r *http.Request) {
        itemID := r.PathValue("id")
        
        rows, err := db.QueryContext(r.Context(),
            `SELECT id, item_id, action, old_data, new_data, changed_by, changed_at
             FROM items_history WHERE item_id=$1 ORDER BY changed_at DESC`,
            itemID)
        if err != nil {
            http.Error(w, err.Error(), http.StatusInternalServerError)
            return
        }
        defer rows.Close()
        
        var history []ItemHistory
        for rows.Next() {
            var h ItemHistory
            if err := rows.Scan(&h.ID, &h.ItemID, &h.Action, &h.OldData,
                &h.NewData, &h.ChangedBy, &h.ChangedAt); err != nil {
                http.Error(w, err.Error(), http.StatusInternalServerError)
                return
            }
            history = append(history, h)
        }
        
        w.Header().Set("Content-Type", "application/json")
        json.NewEncoder(w).Encode(history)
    }
}

5. Web UI (HTML + JavaScript)

<!DOCTYPE html>
<html>
<head>
    <title>WarehouseControl</title>
    <style>
        body { font-family: Arial; margin: 20px; }
        .container { max-width: 1200px; margin: 0 auto; }
        .hidden { display: none; }
        table { width: 100%; border-collapse: collapse; }
        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
        th { background-color: #f2f2f2; }
        .btn { padding: 8px 16px; margin: 4px; cursor: pointer; }
        .btn-primary { background: #007bff; color: white; border: none; }
        .btn-danger { background: #dc3545; color: white; border: none; }
        .btn-success { background: #28a745; color: white; border: none; }
        .form-group { margin: 10px 0; }
        .form-group label { display: inline-block; width: 100px; }
        .form-group input { padding: 5px; width: 200px; }
        .error { color: red; }
        .success { color: green; }
    </style>
</head>
<body>
    <div class="container">
        <!-- Login Form -->
        <div id="loginForm">
            <h2>Вход в систему</h2>
            <div class="form-group">
                <label>Логин:</label>
                <input type="text" id="username" required>
            </div>
            <div class="form-group">
                <label>Пароль:</label>
                <input type="password" id="password" required>
            </div>
            <button class="btn btn-primary" onclick="login()">Войти</button>
            <div id="loginError" class="error"></div>
        </div>

        <!-- Main Content -->
        <div id="mainContent" class="hidden">
            <div style="display: flex; justify-content: space-between;">
                <h2>Управление складом</h2>
                <div>
                    <span id="userInfo"></span>
                    <button class="btn btn-danger" onclick="logout()">Выйти</button>
                </div>
            </div>

            <!-- Add/Edit Form -->
            <div id="itemForm" class="hidden">
                <h3 id="formTitle">Добавить товар</h3>
                <div class="form-group">
                    <label>Название:</label>
                    <input type="text" id="itemName" required>
                </div>
                <div class="form-group">
                    <label>SKU:</label>
                    <input type="text" id="itemSku" required>
                </div>
                <div class="form-group">
                    <label>Количество:</label>
                    <input type="number" id="itemQuantity" required>
                </div>
                <div class="form-group">
                    <label>Цена:</label>
                    <input type="number" step="0.01" id="itemPrice" required>
                </div>
                <button class="btn btn-success" onclick="saveItem()">Сохранить</button>
                <button class="btn" onclick="cancelEdit()">Отмена</button>
            </div>

            <!-- Items Table -->
            <div>
                <button class="btn btn-primary" onclick="showAddForm()">Добавить товар</button>
                <table id="itemsTable">
                    <thead>
                        <tr>
                            <th>Название</th>
                            <th>SKU</th>
                            <th>Количество</th>
                            <th>Цена</th>
                            <th>Действия</th>
                        </tr>
                    </thead>
                    <tbody id="itemsBody"></tbody>
                </table>
            </div>

            <!-- History -->
            <div id="historySection" class="hidden">
                <h3>История изменений</h3>
                <table>
                    <thead>
                        <tr>
                            <th>Дата</th>
                            <th>Действие</th>
                            <th>Пользователь</th>
                            <th>Старые данные</th>
                            <th>Новые данные</th>
                        </tr>
                    </thead>
                    <tbody id="historyBody"></tbody>
                </table>
                <button class="btn" onclick="hideHistory()">Закрыть</button>
            </div>
        </div>
    </div>

    <script>
        let token = localStorage.getItem('token');
        let currentUser = null;
        let editingId = null;

        // API calls
        async function apiCall(url, method = 'GET', body = null) {
            const headers = {
                'Content-Type': 'application/json',
                'Authorization': `Bearer ${token}`
            };
            
            const options = { method, headers };
            if (body) options.body = JSON.stringify(body);
            
            const response = await fetch(url, options);
            if (response.status === 401) {
                logout();
                throw new Error('Unauthorized');
            }
            return response;
        }

        // Auth functions
        async function login() {
            const username = document.getElementById('username').value;
            const password = document.getElementById('password').value;
            
            try {
                const response = await fetch('/api/login', {
                    method: 'POST',
                    headers: { 'Content-Type': 'application/json' },
                    body: JSON.stringify({ username, password })
                });
                
                if (!response.ok) throw new Error('Invalid credentials');
                
                const data = await response.json();
                token = data.token;
                currentUser = data.user;
                localStorage.setItem('token', token);
                
                document.getElementById('loginForm').classList.add('hidden');
                document.getElementById('mainContent').classList.remove('hidden');
                document.getElementById('userInfo').textContent = 
                    `${currentUser.username} (${currentUser.role})`;
                
                loadItems();
            } catch (error) {
                document.getElementById('loginError').textContent = error.message;
            }
        }

        function logout() {
            token = null;
            localStorage.removeItem('token');
            document.getElementById('loginForm').classList.remove('hidden');
            document.getElementById('mainContent').classList.add('hidden');
        }

        // CRUD operations
        async function loadItems() {
            try {
                const response = await apiCall('/api/items');
                const items = await response.json();
                
                const tbody = document.getElementById('itemsBody');
                tbody.innerHTML = items.map(item => `
                    <tr>
                        <td>${item.name}</td>
                        <td>${item.sku}</td>
                        <td>${item.quantity}</td>
                        <td>${item.price}</td>
                        <td>
                            ${currentUser.role !== 'viewer' ? `
                                <button class="btn btn-primary" onclick="editItem('${item.id}')">Редактировать</button>
                                ${currentUser.role === 'admin' ? `
                                    <button class="btn btn-danger" onclick="deleteItem('${item.id}')">Удалить</button>
                                ` : ''}
                            ` : ''}
                            <button class="btn" onclick="showHistory('${item.id}')">История</button>
                        </td>
                    </tr>
                `).join('');
            } catch (error) {
                console.error('Error loading items:', error);
            }
        }

        function showAddForm() {
            editingId = null;
            document.getElementById('formTitle').textContent = 'Добавить товар';
            document.getElementById('itemForm').classList.remove('hidden');
            document.getElementById('itemName').value = '';
            document.getElementById('itemSku').value = '';
            document.getElementById('itemQuantity').value = '';
            document.getElementById('itemPrice').value = '';
        }

        function editItem(id) {
            editingId = id;
            document.getElementById('formTitle').textContent = 'Редактировать товар';
            document.getElementById('itemForm').classList.remove('hidden');
            // Load item data and fill form
        }

        async function saveItem() {
            const item = {
                name: document.getElementById('itemName').value,
                sku: document.getElementById('itemSku').value,
                quantity: parseInt(document.getElementById('itemQuantity').value),
                price: parseFloat(document.getElementById('itemPrice').value)
            };
            
            try {
                if (editingId) {
                    await apiCall(`/api/items/${editingId}`, 'PUT', item);
                } else {
                    await apiCall('/api/items', 'POST', item);
                }
                
                document.getElementById('itemForm').classList.add('hidden');
                loadItems();
            } catch (error) {
                console.error('Error saving item:', error);
            }
        }

        async function deleteItem(id) {
            if (!confirm('Удалить товар?')) return;
            
            try {
                await apiCall(`/api/items/${id}`, 'DELETE');
                loadItems();
            } catch (error) {
                console.error('Error deleting item:', error);
            }
        }

        function cancelEdit() {
            document.getElementById('itemForm').classList.add('hidden');
            editingId = null;
        }

        async function showHistory(itemId) {
            try {
                const response = await apiCall(`/api/items/${itemId}/history`);
                const history = await response.json();
                
                const tbody = document.getElementById('historyBody');
                tbody.innerHTML = history.map(h => `
                    <tr>
                        <td>${new Date(h.changed_at).toLocaleString()}</td>
                        <td>${h.action}</td>
                        <td>${h.changed_by}</td>
                        <td>${h.old_data ? JSON.stringify(h.old_data) : '-'}</td>
                        <td>${h.new_data ? JSON.stringify(h.new_data) : '-'}</td>
                    </tr>
                `).join('');
                
                document.getElementById('historySection').classList.remove('hidden');
            } catch (error) {
                console.error('Error loading history:', error);
            }
        }

        function hideHistory() {
            document.getElementById('historySection').classList.add('hidden');
        }

        // Check if already logged in
        if (token) {
            // Validate token and load data
            login();
        }
    </script>
</body>
</html>

6. Маршрутизация и запуск

func main() {
    // Подключение к БД
    db, err := sql.Open("postgres", "postgres://user:password@localhost/warehouse?sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    jwtSecret := []byte("your-secret-key")
    mux := http.NewServeMux()

    // Публичные endpoints
    mux.HandleFunc("POST /api/login", loginHandler(db, jwtSecret))
    mux.HandleFunc("POST /api/register", registerHandler(db, jwtSecret))

    // Защищенные endpoints
    protected := authMiddleware(jwtSecret)(mux)

    // CRUD для товаров
    mux.Handle("GET /api/items", requireRole("admin", "manager", "viewer")(getItems(db)))
    mux.Handle("POST /api/items", requireRole("admin", "manager")(createItem(db)))
    mux.Handle("PUT /api/items/{id}", requireRole("admin", "manager")(updateItem(db)))
    mux.Handle("DELETE /api/items/{id}", requireRole("admin")(deleteItem(db)))
    
    // История
    mux.Handle("GET /api/items/{id}/history", requireRole("admin", "manager", "viewer")(getItemHistory(db)))

    // Статика
    mux.Handle("GET /", http.FileServer(http.Dir("./static")))

    log.Println("Server starting on :8080")
    log.Fatal(http.ListenAndServe(":8080", protected))
}

7. Ключевые моменты реализации

Безопасность:

• JWT токены с ролями в claims
• Middleware для проверки аутентификации
• Ролевой доступ на уровне middleware
• Хеширование паролей (bcrypt)

Аудит:

• Триггеры PostgreSQL для автоматической записи истории
• JSONB для хранения старых/новых данных
• Привязка изменений к пользователю

UI:

• Адаптивный интерфейс
• Ролевая видимость кнопок
• История изменений для каждого товара

Edge cases:

• Истечение JWT токена
• Конкуренция при обновлении
• Валидация входных данных
• Обработка дубликатов SKU

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

graph LR
    subgraph "Production"
        LB[Load Balancer]
        API1[API Instance 1]
        API2[API Instance 2]
        PG[(PostgreSQL)]
        CACHE[(Redis Cache)]
    end
    
    LB --> API1
    LB --> API2
    API1 --> PG
    API2 --> PG
    API1 --> CACHE
    API2 --> CACHE

Для production рекомендуется:

• Добавить кэширование (Redis)
• Репликация PostgreSQL
• Rate limiting
• Swagger/OpenAPI документация
• Graceful shutdown
• Structured logging