StockPilot - Backend API (Golang)

A Golang REST API powering the StockPilot inventory management application. Handles authentication, product and category management, bulk operations, and multi-tenant data isolation for every request.

Built from scratch with Gorilla Mux for routing, GORM for database access, and JWT for stateless authentication. Deployed on Render's free tier and connected to a PostgreSQL database on Supabase.

Most MVPs at this price point use a single shared table with a user_id column and trust the frontend to filter correctly. One misconfigured query leaks data between users. A single malformed payload crashes the entire server.

The StockPilot backend was built with the opposite philosophy: strict server-side data isolation, hard limits on every input, and zero trust in whatever the client sends.

Language: Go

Router: Gorilla Mux

ORM: GORM

Database: PostgreSQL (Supabase)

Auth: JWT with bcrypt password hashing

Deployment: Render (512 MB free tier)

Keep-alive: In-process background goroutine that pings the database every 10 minutes

The backend is a single binary with no external service dependencies beyond PostgreSQL. Every protected route runs through authentication middleware before reaching a handler.

The API exposes 15 route definitions across 10 unique URL paths, split into public and protected groups.

GET /api/health — static JSON status used by the uptime monitor

POST /api/auth/register — creates a new user with a bcrypt-hashed password

POST /api/auth/login — validates credentials and returns a signed JWT

GET /api/categories — list all categories for the authenticated user

POST /api/categories — create a new category

GET /api/categories/{id} — fetch a single category

DELETE /api/categories/{id} — delete a category

GET /api/products — list all products for the authenticated user

POST /api/products — create a new product

GET /api/products/{id} — fetch a single product scoped to the user

PATCH /api/products/{id} — update a product

DELETE /api/products/{id} — delete a product

POST /api/products/bulk/create — insert multiple products in one request

PATCH /api/products/bulk/update — update multiple products in one request

DELETE /api/products/bulk/delete — delete multiple products by ID

All protected routes live under a Gorilla Mux subrouter that applies the authentication middleware at the group level. Individual handlers never re-check auth — the middleware guarantees a valid user ID is in the request context.

JWT-based, stateless authentication.

Passwords are hashed with bcrypt at default cost before storage. Plaintext passwords never touch the database.

On login, the server signs a JWT with an HS256 signature using a secret read from an environment variable. The application refuses to start if the secret is missing.

The token payload includes the user ID and an iat / exp pair. Expiry is configurable via the JWT_TIME environment variable, currently set to 1440 minutes (24 hours).

The token is verified on every protected request. The middleware extracts the user ID from the claims and injects it into the request context.

Every protected handler enforces isolation at the query level, not the presentation layer.

Instead of filtering products in the frontend, every read and write is scoped with a WHERE user_id = ? clause bound to the user ID extracted from the JWT. This means:

A user cannot read another user's products by guessing an ID

A user cannot delete another user's data

A user cannot insert a product into another user's account by modifying the request body

The isolation is not a filter applied after the fact. It is part of the query itself, which means a malicious request with a forged ID will simply return zero rows or a 404, never another user's data.

A custom middleware enforces a 5 MB limit on every request body. It works in two stages:

If the request includes a Content-Length header larger than 5 MB, the middleware returns 413 Request Entity Too Large immediately, before the body is read into memory.

If the request uses chunked encoding (no Content-Length), the middleware wraps the body with http.MaxBytesReader, which stops the read at the limit and prevents memory exhaustion.

This was added after an early version of the API was tested with a 200 MB JSON payload. Under the original 128 MB limit, the server attempted to buffer the payload and was terminated by the platform with an out-of-memory error. The limit was lowered to 5 MB and the pre-check was added so that the same attack is now rejected instantly.

Every write endpoint validates input before persisting:

Negative quantities are rejected with a 400

Negative prices are rejected with a 400

Malformed JSON bodies are rejected with a 400

Validation runs on individual write handlers and on each item inside bulk requests, so a single bad entry in a bulk operation cannot corrupt the database.

Cross-origin requests are handled with the rs/cors middleware. Only the specific origins used by the frontend are allowed (localhost during development, the production Vercel domain). Only the HTTP methods and headers used by the API are exposed.

The JWT middleware runs before every protected handler. It reads the Authorization header, validates the signature and expiry, extracts the user ID from the claims, and injects it into the request context. If any step fails, the request is rejected with a 401 before the handler runs.

The API connects to Supabase through its direct PostgreSQL port (5432) rather than the transaction pooler (6543).

This decision was made after encountering a SQLSTATE 42P05 error — "prepared statement already exists" — caused by PgBouncer reusing connections across GORM-prepared statements. Switching to the direct connection eliminated the collision entirely.

The DSN is read from a single DATABASE_URL environment variable, keeping local and production configurations identical. During development, the same code connects to localhost:5432 with SSL disabled; in production it connects to Supabase with SSL required.

The API runs a background goroutine that pings the database every 10 minutes. This serves two purposes:

It keeps the Render free-tier container from spinning down due to inactivity.

It keeps the Supabase free-tier database from being paused after prolonged inactivity.

The goroutine uses FirstOrCreate before every UPDATE, so the single-row keep_alive table is self-healing — if the row is ever deleted, the next tick recreates it automatically. The goroutine starts with an immediate ping on boot, then ticks on a 10-minute interval.

A few of the harder issues encountered and resolved during development:

IPv6 unreachable on Render. The free tier does not support outbound IPv6. The initial database connection resolved to an IPv6 address and failed. Resolved by moving from the Supabase pooler (which prefers IPv6) to the direct connection on port 5432.

Malformed DSN. An early version of the database connection used postgres.New(postgres.Config{}) without passing the DSN, which caused GORM to attempt a Unix socket connection at /tmp/.s.PGSQL.5432. Replaced with postgres.Open(dsn).

Out-of-memory crash. A 200 MB JSON payload crashed the backend under the original 128 MB limit. The limit was reduced to 5 MB and a Content-Length pre-check was added so that oversized requests are rejected before the body is read.

Prepared statement collision. PgBouncer on the Supabase transaction pooler caused SQLSTATE 42P05 errors under load. Fixed by moving to the direct PostgreSQL port.

Hosting: Render, free tier, 512 MB container

Database: Supabase PostgreSQL

Environment variables: DATABASE_URL, JWT_SECRET, JWT_TIME, PORT — all read at startup, none committed to the repository

Monthly cost: $0.

This backend demonstrates the ability to:

Design and build a complete REST API from scratch in Go

Implement stateless JWT authentication with proper password hashing

Enforce strict multi-tenant data isolation at the query level

Harden a public API against oversized payloads and malformed input

Diagnose and resolve platform-specific issues (IPv6, connection pooling, OOM)

Deploy and operate a production-grade backend on free infrastructure

It is a live, public demonstration of my abilities as a backend developer.