📦 Product Management System

A product management project built with Python, FastAPI, and SQLite. It provides an interactive terminal interface and an HTTP API for managing products.

This is a learning and portfolio project for practicing backend development, database integration, CRUD operations, validation, project organization, and automated testing. It is not presented as a production-ready system.

The current version focuses on backend engineering fundamentals that matter in real maintenance work: shared business rules between API and CLI, explicit schema migrations, predictable REST contracts, test coverage gates, Docker delivery, integer-cent money handling, paginated listing, and optimistic concurrency control for product updates.

🎬 Quick Demo

🖥 Web App — Gôndola

The frontend/ folder ships Gôndola, a React web app for the API: account screens with an interactive 3D scene and a product dashboard built around the yellow shelf price tag.

Highlights

• Sign-up and sign-in screens with a React Three Fiber scene: products drop onto a store shelf with squash-and-stretch physics, react to the pointer and hop when clicked; the camera fits any screen size and rendering pauses off-screen
• Dashboard with animated totals, a catalog-wide price histogram, paginated product list, debounced search and highlighted recent changes
• Create/edit drawer with a live price-tag preview, Brazilian price input (12,90) and a clear recovery path for 409 Conflict version clashes
• Delete confirmation with Undo, toast notifications, empty and error states, and a notice while the free-tier server wakes up
• Command palette (Ctrl/⌘ K) plus / (search) and N (new product) shortcuts
• Accessible overlays (focus trap, Esc, focus return), skip link, visible focus rings and full prefers-reduced-motion support
• Route-level code splitting with long-lived vendor chunks; the 3D engine only loads on the account screens

Stack: React 19, TypeScript, Vite, React Router, React Three Fiber + drei (Three.js), Motion, Lucide icons, self-hosted Bricolage Grotesque and Instrument Sans.

Accounts are local for now. The API has no authentication endpoints yet, so accounts and sessions are stored in the browser (passwords are salted and hashed with SHA-256 before storage). This is a product-demo gate, not a security boundary. All auth calls live in frontend/src/auth/authService.ts, so moving to real endpoints only requires changing that file.

Run the web app

cd frontend
npm install
npm run dev        # http://localhost:5173, proxies /api to http://127.0.0.1:8000
npm run build      # type-check and production build into frontend/dist

Set VITE_API_BASE_URL to point the app at a deployed API (see frontend/.env.example). On Vercel, frontend/vercel.json rewrites every route to index.html so deep links like /painel work on refresh.

🚀 Current Features

• Product creation, listing, lookup, update, and deletion
• REST API built with FastAPI
• Interactive terminal interface in Portuguese
• SQLite persistence
• Exact ID lookup, paginated listing, and partial-name search
• CLI reports for average price and products above a minimum price
• Request validation with Pydantic
• Validated environment configuration and explicit SQLite backend selection
• Repository boundary with an SQLite adapter
• Versioned, explicit SQLite schema migrations
• Integer-cent storage for monetary values, while keeping API/CLI decimal prices
• Optimistic concurrency control with product versions and 409 Conflict
• Production-shaped Docker and Docker Compose delivery with persistent storage
• Automated tests with Pytest, CI quality gates, and minimum coverage enforcement

🛠 Technologies

• Python 3.14
• FastAPI
• SQLite
• Pydantic
• Uvicorn
• Pytest
• HTTPX for API tests
• Docker and Docker Compose for the local production-shaped deployment

The current development baseline uses Python 3.14.3. Use Python 3.14.3 to reproduce the verified local environment.

🌎 Language

The application interface is currently available in Portuguese. Project documentation and technical descriptions are written in English.

📂 Project Structure

Product-Management-System/
├── .github/workflows/      # CI checks
├── assets/                 # Demo assets
├── docs/                   # Database migration and operations notes
├── schemas/                # Pydantic API and error models
├── tests/                  # Automated API, service, CLI, database, and CI tests
├── api.py                  # FastAPI application factory and routes
├── api_errors.py           # HTTP error handlers
├── cli.py                  # Interactive terminal workflows
├── cli_input.py            # Terminal input parsing and validation
├── constants.py            # CLI labels, prompts, and messages
├── config.py               # Validated runtime settings
├── database.py             # SQLite connection and data operations
├── main.py                 # CLI entry point
├── migrations.py           # Explicit SQLite schema migrations
├── persistence.py          # Repository protocol and neutral persistence types
├── product_service.py      # Shared product business rules
├── repositories.py         # Repository composition and SQLite adapter
├── Dockerfile              # Production API image
├── compose.yaml            # Local production-shaped container deployment
├── pyproject.toml          # Ruff, mypy, pytest, and coverage configuration
├── requirements.txt        # Pinned runtime dependencies
├── requirements-dev.txt    # Pinned development and quality dependencies
└── STRUCTURE.md            # Detailed architecture documentation

This is a high-level view. See STRUCTURE.md for the complete module responsibilities, including configuration, migrations, repository composition, Docker delivery, and data flow.

⚙️ Setup

1. Clone and enter the repository

git clone https://github.com/paulo-sempio-neto/Product-Management-System.git
cd Product-Management-System

2. Create the canonical virtual environment

py -3.14 -m venv .venv

The canonical environment directory for this project is .venv.

3. Activate it on Windows PowerShell

.\.venv\Scripts\Activate.ps1

For Windows Command Prompt:

.venv\Scripts\activate.bat

4. Install the pinned dependencies

python -m pip install -r requirements.txt

▶️ Run the CLI

With .venv activated:

python main.py

The terminal menu initializes only new, empty storage. If produtos.db is a legacy database, follow the explicit upgrade procedure in Database evolution before starting the CLI.

🌐 Run the API

With .venv activated:

python -m uvicorn api:app --reload

The API is available at:

http://127.0.0.1:8000

With the development defaults, FastAPI's interactive documentation is available at:

http://127.0.0.1:8000/docs

🗄 Database

The application uses SQLite through a typed repository boundary. Fresh storage is initialized when the CLI starts or during the FastAPI application lifespan. Existing legacy databases require an explicit upgrade; see Database evolution before starting this version.

Local runtime database files are ignored by Git and should not be committed. Automated tests create isolated databases under pytest's temporary directory.

🧪 Run the Tests

With .venv activated:

python -B -m pytest -q -p no:cacheprovider

The test suite uses temporary SQLite databases and covers API behavior, shared product rules, CLI workflows, configuration, repository composition, migrations, and SQLite reliability behavior.

📚 Development Goals

This project is intended to demonstrate and improve:

• Python backend development
• REST API development
• SQLite integration
• CRUD design
• Input and request validation
• Automated testing
• Incremental software maintenance

🔮 Possible Future Improvements

• Expand operational observability
• Expand validation and database integrity rules
• Add authentication and authorization, and connect the web app to it
• Add product categories and inventory information
• Publish the existing container image and automate deployment
• Add production infrastructure such as TLS termination and monitoring

👤 Author

Paulo Sempio Neto

GitHub: https://github.com/paulo-sempio-neto

Engineering Quality

Architecture Overview

flowchart TD
    API[FastAPI routes] --> Schemas[Pydantic schemas]
    API --> Service[product_service.py<br/>product rules]
    CLI[CLI workflows] --> Input[cli_input.py<br/>terminal parsing]
    CLI --> Service
    Service --> Repository[ProductRepository<br/>storage contract]
    Repository --> Adapter[SQLiteProductRepository]
    Adapter --> Database[database.py<br/>SQL and connection lifecycle]
    Database --> SQLite[(SQLite)]

Loading

The API owns HTTP concerns, the CLI owns terminal interaction, the service layer owns product rules, the repository boundary owns storage selection, and the SQLite adapter/database layer owns SQLite operations.

Local Development

Use the existing local setup instructions above to create and activate .venv. Install the development toolchain, including the application dependencies, with:

python -m pip install -r requirements-dev.txt

Run the CLI with python main.py, or start the API with python -m uvicorn api:app --reload. Interactive API documentation is at http://127.0.0.1:8000/docs.

Tests and Coverage

Run the full isolated test suite:

python -B -m pytest

Run it with a terminal coverage report and missing-line information:

python -B -m pytest --cov --cov-report=term-missing

Coverage is used to identify meaningful gaps, not as a standalone percentage target. CI currently enforces a minimum of 75%. Further coverage should focus on complete CLI menu workflows and concurrent product operations. Rollback, configuration, API error handling, and terminal-input validation have dedicated regression tests.

Quality Checks

python -m ruff format --check .
python -m ruff check .
python -m mypy

Run python -m ruff format . to apply formatting. GitHub Actions runs the format, dependency compatibility, lint, strict type-check, smoke test, full pytest-with-coverage, and Docker image build checks on every push and pull request.

Configuration and Operational Behavior

Both API and CLI read validated process environment variables through config.py. Development retains the existing defaults. .env.example documents the variables; the application does not load .env automatically. Set these before starting a process; restart the API after configuration changes.

Variable	Default	Purpose
APP_ENV	development	development, testing, or production
DB_BACKEND	sqlite	Explicit backend selection; only sqlite is implemented
DB_NAME	produtos.db in development	SQLite file path; must be explicit in testing and production
SQLITE_TIMEOUT	5	Lock wait in seconds, greater than zero and at most 60
API_DOCS_ENABLED	true, except in production	Enables /docs, /redoc, and /openapi.json
PORT	8000 in the Docker image	HTTP port used by container platforms such as Render
CORS_ALLOWED_ORIGINS	Vite local origins in development; empty otherwise	Comma-separated browser origins allowed to call the API

Relative database paths resolve from the working directory. Use an absolute path for a stable location in production. The containing directory must already exist. Invalid settings fail startup. In-memory SQLite is not supported by configuration because operations use separate connections. Selecting testing requires a path; it does not itself create or manage test isolation (pytest fixtures do that). Unknown backends, including postgresql, fail configuration validation. DB_NAME accepts a file path, not a database URL or SQLite URI. Existing deployments without DB_BACKEND retain the SQLite default.

CORS_ALLOWED_ORIGINS is intended for browser-based frontends. Development defaults allow the local Vite server at http://localhost:5173 and http://127.0.0.1:5173. Production must list explicit origins, such as the Vercel application URL; wildcard origins are rejected in production.

Example PowerShell configuration for a separate local database:

$env:APP_ENV = "development"
$env:DB_BACKEND = "sqlite"
$env:DB_NAME = "local-products.db"
$env:SQLITE_TIMEOUT = "5"
python -m uvicorn api:app --reload

GET /live is a liveness probe: it confirms that the HTTP application can serve requests without accessing SQLite. GET /health is a readiness probe: it checks the current schema revision and read access to the products table, returning 200 {"status":"ok"} or a sanitized 503. It neither creates a missing database nor verifies write access. Startup initializes only empty storage. Existing CRUD paths, success payloads, and error status codes remain.

GET /products supports paginated listing and an optional name filter:

GET /products?page=1&limit=20&name=ar

The response is an object with items, page, limit, and total. The legacy GET /products/search/?name=ar route remains available and continues returning a plain product list for compatibility.

Product responses include a version field. PUT /products/{id} requires the client's known version; if the stored version has changed, the API returns 409 Conflict instead of silently overwriting another update. Successful updates increment the product version.

Error responses retain detail and add error.code, for example:

{"detail":"Produto não encontrado","error":{"code":"not_found"}}

Validation failures retain a detail list with loc, msg, and type, but omit submitted input and internal ctx values. Boolean, non-finite, non-positive, too-large, and more-than-two-decimal-place prices are rejected with 422. Numeric price strings remain accepted for compatibility. Internal failures return generic messages; server logs record failure categories without request bodies or database paths. HTTP error headers such as Allow are preserved. Error schemas and route groups are included in OpenAPI.

Security and Reliability Scope

SQL parameters are bound rather than interpolated. Connections use explicit transactions, rollback on exceptions, and close after each operation. Unique-name violations are distinguished from other integrity failures. Updates use optimistic locking through the product version, and update/delete checks detect a product removed between the initial lookup and the write. Explicit, transactional schema upgrades are described in the migration runbook. Startup never upgrades legacy data.

The application remains unauthenticated: callers can access all CRUD operations. Do not expose it to untrusted clients yet. Product listing is paginated, but request-body limits and rate limiting are not implemented. Those abuse protections need a separate compatibility and deployment design; disabling API docs is not access control. SQLite still serializes writers, and multi-step service operations are not a single transaction. These are explicit limits of the current portfolio baseline.

Container Deployment

The production image runs the API as an unprivileged user with APP_ENV=production, docs disabled, SQLite stored at /data/products.db, and PORT=8000 by default. Container platforms such as Render can override PORT at runtime while the application continues to bind to 0.0.0.0. The accompanying Compose file mounts that directory as the named product_data volume, so rebuilding or recreating the container does not discard product data.

Build and start the local production-shaped deployment:

docker compose up --build -d
docker compose ps

The Compose port is bound to 127.0.0.1:8000; check it with:

Invoke-WebRequest http://127.0.0.1:8000/live
Invoke-WebRequest http://127.0.0.1:8000/health

Use docker compose logs -f api for application logs and docker compose down to stop the service without deleting data. docker compose down -v also removes the persistent SQLite volume and is therefore destructive. Before copying a database backup, stop the API and all other writers cleanly. See the migration and backup runbook for commands.

Run only one API container against a SQLite volume. SQLite permits concurrent readers but serializes writers, and a shared local volume is not a multi-replica database. Put a TLS-terminating reverse proxy in front of the service when it must be reachable beyond the host, and do not treat disabled documentation as access control. The CI workflow builds the container image after its existing quality and test gates; image publication and platform-specific deployment credentials remain intentionally out of scope.

Database Evolution

Business rules consume ProductRepository from persistence.py; repositories.py selects SQLite by default. Services also accept an explicit repository= for testing and future adapters. SQLite remains the only configured runtime backend.

get_repository(settings=...) binds a repository to a validated configuration snapshot, including its file path and timeout. create_app(settings=..., repository=...) supports isolated application composition and test injection; the existing uvicorn api:app entry point remains unchanged. Shared repository and API tests use a backend-parametrized fixture, currently containing only SQLite. SQLite schema, migration, and file-behavior tests remain backend-specific.

Revision 3 stores product prices as integer cents and adds a positive integer version for optimistic concurrency control. The API and CLI continue to expose price in reais. It retains case-sensitive uniqueness, product IDs, and the autoincrement sequence. Older schemas using price REAL are upgraded only when existing prices are exactly representable with two decimal places; values that would require rounding are refused for manual repair. Products migrated from older schemas start at version = 1.

With all application writers stopped, inspect an existing file before upgrading:

python -m migrations status --database produtos.db

After backing up and reviewing the result, the explicit upgrade command is:

python -m migrations upgrade --database produtos.db

Both commands require an existing file to avoid creating a database from a mistyped path. Unknown/customized schemas and invalid legacy rows are refused. Consult Database evolution for backup/restore, migration design, modeling decisions, and the PostgreSQL adapter compatibility checklist.