warehouse SQL

Magazyn z partiami towaru i alokacją FIFO/FEFO na SQL Server append-only, ledger ruchów, spójny cache stanów, pesymistyczna obsługa współbieżności i raporty biznesowe na ~25 tys. zamówień wygenerowanych danych.

Problem biznesowy

Hurtownia spożywcza nie ma „jednego stanu produktu" — ma partie: ta sama mrożonka leży w magazynie w kilku dostawach, każda z innym kosztem zakupu i innym terminem ważności. Wydawać trzeba mądrze: towar psujący się według najbliższego terminu (FEFO), resztę według kolejności przyjęcia (FIFO), inaczej zostajemy z przeterminowanym towarem i stratami. Dopiero śledzenie, z której partii fizycznie zszedł towar, daje prawdziwy koszt sprzedaży (marżę), realny raport strat i ranking dostawców.

Model danych

Kluczowa tabela to BatchAllocations — most między światem sprzedaży (OrderLines) a światem magazynu (StockBatches). Jeden wiersz mówi: „pozycja X zamówienia dostała N sztuk z partii Y po koszcie Z". UnitCostAtAllocation jest zamrożony w momencie alokacji, więc marża liczona po fakcie nie zmienia się, gdy przyjeżdżają nowe, droższe partie. Bez tej tabeli koszt sprzedaży dałoby się tylko szacować średnią.

Drugi filar: StockMovements — append-only ledger wszystkich ruchów (RECEIPT / SALE / ADJUSTMENT). StockBatches.QuantityOnHand to tylko cache odtwarzalny z ledgera.

Algorytm alokacji (skrót)

Zamówienie na 150 szt. mleka, na stanie partie A (100 szt., termin 20.07) i B (80 szt., termin 05.08). Sumy narastające układają podaż i popyt na wspólnej osi sztuk:

podaż  (FEFO):  A = (0, 100]   B = (100, 180]
popyt:          pozycja #1 = (0, 150]
alokacja = przecięcia:  A → 100 szt.,  B → 50 szt.

Podział pozycji na partie wynika z algebry przedziałów — bez pętli i kursorów, całe zamówienie w jednym INSERT ... SELECT. Gdy towaru brakuje, procedura wycofuje całość i rzuca czytelny błąd (Brak towaru dla zamowienia 501: Mleko UHT Classic (brakuje 20 szt.)) — nie ma realizacji częściowych. Przykład krok po kroku: docs/allocation-algorithm.md.

Diagram sekwencji alokacji

sequenceDiagram
    actor Client as Klient
    participant App as Aplikacja
    participant SP as sp_AllocateOrder
    participant Batches as StockBatches
    participant Alloc as BatchAllocations
    participant Ledger as StockMovements
    participant Trg as tr_UpdateQuantityOnHand

    Client->>App: Sklada zamowienie (OrderID)
    App->>SP: EXEC sp_AllocateOrder @OrderID

    Note over SP: BEGIN TRANSACTION

    loop Dla kazdej pozycji zamowienia
        SP->>Batches: SELECT partie z QuantityOnHand > 0<br/>(UPDLOCK, HOLDLOCK)
        Batches-->>SP: Lista dostepnych partii

        Note over SP: Sortowanie:<br/>FEFO (ExpiryDate) jesli IsPerishable<br/>FIFO (ReceivedDate) w przeciwnym razie

        Note over SP: SUM() OVER (...) liczy skumulowana<br/>dostepnosc i wyznacza ile wziac z partii

        alt Wystarczajaco towaru
            loop Dla kazdej uzytej partii
                SP->>Alloc: INSERT alokacja<br/>(QuantityTaken, UnitCostAtAllocation)
                SP->>Ledger: INSERT ruch SALE (-Quantity)
                Ledger->>Trg: AFTER INSERT
                Trg->>Batches: UPDATE QuantityOnHand
            end
        else Za malo towaru
            Note over SP: ROLLBACK TRANSACTION
            SP-->>App: Blad: brak wystarczajacego stanu
            App-->>Client: Zamowienie odrzucone
        end
    end

    Note over SP: COMMIT TRANSACTION
    SP-->>App: Sukces
    App-->>Client: Zamowienie zrealizowane

Loading

Decyzje projektowe

• Ledger + cache zamiast samej kolumny stanu. Sama kolumna QuantityOnHand mówi tylko, ile jest teraz — nie da się z niej odtworzyć stanu na dowolny dzień, wykryć rozjazdu po błędzie ani wyjaśnić, skąd wziął się ubytek. Append-only ledger daje audyt i rekonstrukcję historii (raport nr 7), a cache — szybkie odczyty bieżące. Spójność pilnują trigger tr_UpdateQuantityOnHand (cache liczony wyłącznie z ledgera) i raport rozbieżności, który ma być zawsze pusty.
• Niemutowalność ledgera. tr_StockMovementsAppendOnly blokuje UPDATE/DELETE — pomyłki poprawia się wyłącznie wpisem kompensującym, jak w księgowości. Historia nigdy nie kłamie.
• Blokady pesymistyczne (UPDLOCK, HOLDLOCK) zamiast optymistycznych. Wyścig dwóch zamówień o ostatnie sztuki to tu codzienność, a wycofanie alokacji jest drogie (wpisy w dwóch tabelach + cache). Taniej kazać drugiej sesji chwilę poczekać i zobaczyć już pomniejszony stan, niż pozwolić obu „wygrać" i sprzątać po ujemnym stanie. Zakres blokady jest wąski: tylko partie produktów z alokowanego zamówienia.
• Alokacja metodą przedziałów narastających (SUM() OVER) zamiast kursora — całe zamówienie jednym zapytaniem; na wygenerowanych danych alokuje 71 tys. wierszy w ~3 s (patrz sql/02_seed_data.sql, który używa tej samej metody).

Test współbieżności

sqlcmd -d warehouse -i sql/05_concurrency_test.sql     # setup: partia 10 szt., 2 zamówienia po 8
# potem w DWÓCH osobnych sesjach równocześnie: blok SESJA A i blok SESJA B (z komentarzy w pliku)
# na końcu: blok WERYFIKACJA

Co udowadnia: przy dwóch równoległych sp_AllocateOrder na ostatnie sztuki dokładnie jedna sesja wygrywa (8 szt.), druga dostaje błąd 50005 z brakującą ilością, a po wyścigu QuantityOnHand = suma ledgera = 2 — stan się nie rozjeżdża i nie schodzi poniżej zera. Wynik przeprowadzonego testu: sesja B zaalokowała 8 szt., sesja A dostała Brak towaru dla zamowienia 25400: Produkt testowy wspolbieznosci (brakuje 6 szt.).

Performance

Środowisko pomiaru: SQL Server 2019 (Docker), dane z 02_seed_data.sql — ok. 74 tys. ruchów w ledgerze, 3 tys. partii, 75 tys. linii zamówień. Pomiar SET STATISTICS IO/TIME skryptem sql/07_indexes.sql; metryką są odczyty logiczne (czasy przy tej skali to pojedyncze milisekundy).

create index IX_StockMovements_BatchID on StockMovements(BatchID)
create index IX_StockBatches_ProductID_ExpiryDate on StockBatches(ProductID, ExpiryDate)
create index IX_StockBatches_ProductID_ReceivedDate on StockBatches(ProductID, ReceivedDate)
create index IX_BatchAllocations_OrderLineID on BatchAllocations(OrderLineID)

Zapytanie	Przed	Po	Zmiana
Suma ledgera jednej partii (StockMovements)	430 — Clustered Index Scan	134 — Index Seek + Key Lookup	−69%
sp_AllocateOrder: kontrola „czy już zaalokowane" (BatchAllocations)	300 — scan	6 — seek	−98%
Raport rozbieżności cache vs ledger	430	430	bez zmian

Przypadek „było table scan → jest index seek" (suma ledgera jednej partii):

-- PRZED
|--Stream Aggregate(DEFINE:(SUM(StockMovements.Quantity)))
     |--Clustered Index Scan(OBJECT:(StockMovements.PK__StockMov...), WHERE:(BatchID=@BatchID))

-- PO
|--Stream Aggregate(DEFINE:(SUM(StockMovements.Quantity)))
     |--Nested Loops(Inner Join)
          |--Index Seek(OBJECT:(StockMovements.IX_StockMovements_BatchID), SEEK:(BatchID=@BatchID))
          |--Clustered Index Seek(OBJECT:(StockMovements.PK__StockMov...))   -- key lookup

Uwagi (uczciwie):

• Raport rozbieżności agreguje cały ledger, więc IX_StockMovements_BatchID nic tu nie daje — indeks nie zawiera Quantity, a optymalizator słusznie zostaje przy skanie. Odczyty per partia (raporty punktowe, rekonstrukcja stanu jednego produktu) korzystają z seeka.
• Indeksy StockBatches(ProductID, ...) przy 3 tys. partii (21 stron) nie zmieniają planu — optymalizator woli skan tak małej tabeli. Są założone pod wzrost danych i pod sortowanie FEFO/FIFO w sp_AllocateOrder.
• Każde uruchomienie 07_indexes.sql dodaje dwa małe zamówienia TEST-PERF-* (ledger jest append-only, więc skrypt celowo po sobie nie sprząta).

Świadome ograniczenia zakresu

• Brak cen sprzedaży — marża liczona jest po stronie kosztu (rzeczywisty vs naiwny). Dodałbym UnitPrice w OrderLines (cena zamrożona w momencie zamówienia) i raport pełnej marży.
• Alokacja nie rozróżnia magazynów — partie wybierane są globalnie. Dodałbym parametr @WarehouseID w sp_AllocateOrder i partycję okna podaży po magazynie.
• Brak rezerwacji — alokacja od razu ściąga stan. Dodałbym status alokacji (RESERVED/PICKED/SHIPPED) i zwalnianie rezerwacji po timeout.
• Brak zwrotów i anulowania po alokacji — dodałbym sp_CancelAllocation wstawiającą kompensujące ruchy RETURN do ledgera (bez kasowania historii).
• Brak transferów między magazynami — dodałbym parę ruchów TRANSFER_OUT/TRANSFER_IN w jednej transakcji, na tych samych zasadach co SALE.
• Brak użytkowników i audytu „kto" — dodałbym kolumny CreatedBy zasilane SUSER_SNAME() i role bazodanowe (operator vs raporty).

Jak uruchomić

docker compose up -d          # SQL Server 2019 na localhost:1433 (sa / TwojeHaslo123!)

# sqlcmd lokalnie lub z kontenera: docker exec -i warehouse-sql /opt/mssql-tools18/bin/sqlcmd -C ...
sqlcmd -C -S localhost -U sa -P 'TwojeHaslo123!' -i sql/01_schema.sql                # tworzy baze warehouse
sqlcmd -C -S localhost -U sa -P 'TwojeHaslo123!' -d warehouse -i sql/02_seed_data.sql
sqlcmd -C -S localhost -U sa -P 'TwojeHaslo123!' -d warehouse -i sql/03_procedures.sql
sqlcmd -C -S localhost -U sa -P 'TwojeHaslo123!' -d warehouse -i sql/04_triggers.sql
sqlcmd -C -S localhost -U sa -P 'TwojeHaslo123!' -d warehouse -i sql/05_concurrency_test.sql   # + 2 sesje recznie
sqlcmd -C -S localhost -U sa -P 'TwojeHaslo123!' -d warehouse -i sql/06_views_reports.sql
sqlcmd -C -S localhost -U sa -P 'TwojeHaslo123!' -d warehouse -i sql/07_indexes.sql

Skrypty są idempotentne: 01 zrzuca i odtwarza schemat, 02 czyści i generuje dane od zera (parametry @ProductCount, @OrderCount itd. na górze pliku), 07 powtarza eksperyment indeksowy. Kolejność 01→07 jest istotna.

Struktura repo

├── README.md
├── docker-compose.yml
├── docs/
│   ├── erd.png                     # generowany z graphviz
│   └── allocation-algorithm.md     # algorytm alokacji krok po kroku
├── sql/                            # skrypty uruchomieniowe 01-07 (kolejnosc istotna)
└── warehouse/                      # projekt Microsoft.Build.Sql (zrodlo prawdy schematu)
    └── dbo/{Tables,Functions,StoredProcedures,Triggers,Views}

Źródłem prawdy dla schematu jest projekt warehouse/ (walidacja: cd warehouse && dotnet build); pliki sql/01/03/04 oraz widok w 06 są z niego wygenerowane — zmiany schematu rób w projekcie i przenieś do skryptów.