Skip to main content
back to blog

building a warehouse return processing system at alfamart

· cornelius yoga · 14 min read
building a warehouse return processing system at alfamart

during my internship at alfamart supply & distribution, i was assigned to replace a legacy visual basic pdt application used by warehouse operators to process supplier returns. what followed was six months of building a flask backend that had to handle real operational constraints: concurrent barcode scanning, credential re-entry before finalization, offline device sync, and a database migration from oracle to postgresql.

the problem with the legacy system

the existing vb application was tightly coupled to windows ce pdt devices and an oracle database. it had no web interface, no audit trail, and no way to handle the two distinct return workflows the business needed: invoice-backed returns where operators scan items against a purchase invoice, and unilateral returns where operators retrieve existing pending documents by supplier and license plate. the business wanted a web-based replacement that could run on modern android pda devices with barcode scanners, support multilingual operators, and require operators to re-enter their credentials before any return batch was finalized.

i was responsible for the entire backend. a teammate handled the html/css/js templates and the internationalization pipeline. the split was clean: backend api contracts were mine, presentation layer was theirs.

architecture overview

the backend is a flask 3.x application organized into nine blueprints: authentication, two return flow handlers, list-of-value endpoints for supplier and item lookups, barcode scanning, offline sync, and audit logging. the application uses the factory pattern so the app instance is created at runtime, which made it straightforward to configure gunicorn workers correctly.

postgresql is the primary database using psycopg3 with a synchronous connection pool. the pool is initialized in the application factory and reinitialized in gunicorn's post_fork hook. this is not optional: psycopg3's sync pool opens connections in the master process, and if workers inherit those connections after forking, they share broken socket state. the post-fork hook closes and recreates the pool in each worker so every worker has its own clean connections.

the finalization confirmation flow

the most operationally critical feature was the finalization step. when an operator finishes scanning a batch of return items, finalizing it is not a single tap. the operator has to enter their username and password again before the batch is committed. it is worth being precise about what this is and what it is not. this is a confirmation step, not a separation of duties control. the same operator who scanned the batch can confirm it with their own credentials, and nobody with higher authority is required to sign off. it is the credential equivalent of an "are you sure" dialog, chosen over a plain confirmation modal because it forces a deliberate pause on an action that cannot be undone.

sequenceDiagram
    participant Op as Operator
    participant App as Flask Backend
    participant DB as PostgreSQL
    participant Redis

    Op->>App: POST /finalize/request (session token)
    App->>Redis: check rate limit (5/min)
    Redis-->>App: allowed
    App->>DB: validate batch completeness
    DB-->>App: all items scanned
    App-->>Op: 200 OK, awaiting confirmation

    Op->>App: POST /finalize/confirm (re-entered credentials)
    App->>Redis: check rate limit (5/min)
    Redis-->>App: allowed
    App->>DB: verify credentials
    DB-->>App: valid
    App->>DB: BEGIN TRANSACTION
    App->>DB: update batch status = finalized
    App->>DB: write audit log entry
    App->>DB: COMMIT
    DB-->>App: success
    App-->>Op: 200 OK, batch finalized

the finalization endpoint is rate-limited to five attempts per minute using flask-limiter with a redis backend. csrf protection via flask-wtf uses the x-csrftoken header pattern for xhr requests. all scan inputs are sanitized with bleach before being written to the database. every auth event and state change is written to rotating json audit log files using python's rotatingfilehandler.

atomic sequential document numbers

each finalized return batch needs a unique sequential document number, generated daily and reset each day. the naive approach of select max + insert breaks under concurrent requests because two workers can read the same max value simultaneously. i solved this with a dedicated sequence table and postgresql's on conflict do update pattern:

-- sequence table: (date TEXT, last_val INT, PRIMARY KEY(date))
INSERT INTO doc_sequence (date, last_val)
VALUES (%(today)s, 1)
ON CONFLICT (date)
DO UPDATE SET last_val = doc_sequence.last_val + 1
RETURNING last_val;

the sequence increment and the batch header insert happen inside the same transaction, so if the header insert fails, the sequence increment rolls back. no gaps, no duplicates, no race conditions.

idempotent offline sync

pda devices in a warehouse lose connectivity regularly. operators scan items while offline, and the app queues those scans locally. when connectivity returns, the device replays the queue to the backend's /sync/push endpoint. the problem is that a device might replay the same scan twice if the first request timed out before the server responded.

sequenceDiagram
    participant Device
    participant App as Flask /sync/push
    participant DB as PostgreSQL

    Device->>App: POST /sync/push [scan_key, plu, qty, ...]
    loop for each scan
        App->>App: compute SHA-256(session_token + plu + trunc(ts, minute))
        App->>DB: SELECT 1 FROM dedup_log WHERE key = %(key)s
        alt key not seen
            DB-->>App: no row
            App->>DB: INSERT scan + INSERT dedup_log key
            App-->>Device: accepted
        else key already processed
            DB-->>App: row exists
            App-->>Device: skipped (idempotent)
        end
    end

the deduplication key is a sha-256 hash of the session token, the plu code, and the timestamp truncated to the minute. truncating to the minute means a scan replayed within the same minute window is treated as a duplicate. this is intentional: device clocks drift, and we cannot trust sub-minute precision. first-write-wins is the correct resolution strategy here because the operator physically scanned the item once.

oracle to postgresql migration

the legacy hr and master data tables were in oracle. migrating them to postgresql was not just a data dump. oracle uses :name bind parameter syntax; psycopg3 uses %(name)s. every query touching legacy tables had to be refactored. oracle's nvl() becomes coalesce(). oracle's rownum becomes limit. date arithmetic differs. i went through twelve tables and refactored every query systematically, testing each against the postgresql schema before moving on.

one constraint i inherited was the credential handling of the legacy oracle system. the business required behavioral parity during the transition period, so i kept the existing validation logic rather than changing it mid-migration, and documented hardening it as explicit technical debt for the next phase.

containerization and integration testing

the application is containerized with docker and docker-compose, orchestrating the flask/gunicorn service and a redis service. redis uses two separate database indices: one for session storage and one for rate limiting, so a rate limit flush does not affect active sessions.

i wrote five integration test scripts using the requests library, totaling around 1,900 lines. they cover the full workflows: login with locale switching, list-of-value endpoints, draft management, and both finalization flows including the credential confirmation step. these are manual scripts that hit live endpoints with session cookies, not automated unit tests with a ci runner. they were sufficient for validating the api contracts during development, but proper pytest fixtures with a test database are the obvious next step.

current status and what comes next

the backend implementation is complete and the project is currently in pre-qa stage. the frontend, built by a teammate, is integrated and functional. the system is not yet in production.

the things i would address before calling this production-ready: harden credential storage, add pytest-based unit tests for the business logic layer, set up a ci pipeline to run those tests on every push, and write a proper readme since the current one is empty. the security middleware for rate limiting and csrf is active, which is better than the picking system i also worked on during the same internship.

what this internship taught me about building for operations

warehouse software is not forgiving. when an operator scans a barcode and the system returns an error, they do not file a bug report. they call someone, work around it, or stop using the system. the credential confirmation exists because a finalized return batch triggers downstream financial processes and cannot be reversed from the app. the idempotent sync exists because connectivity is unreliable and replaying a scan twice would corrupt inventory counts. these are not edge cases. they are the normal operating conditions.

building for that environment forced me to think about correctness before features. the atomic sequence generation, the deduplication log, the audit trail, the rate limiting on the finalization endpoint: none of these are visible to the user. they are the parts that make the system trustworthy enough to use in a real warehouse. that is the lesson i am taking forward.

back to blog