Ghalalytics

• 

  Ghalalytics — warehouse management SaaS for East Africa. Offline-first, multi-tenant, powered by Amazon Aurora PostgreSQL and Vercel.

• 

  Free, Growth, and Scale pricing tiers — built for small distributors to enterprise warehouse operations across Africa.

• 

  Secure sign-in with email/password (scrypt) or Google OAuth 2.0. Session via httpOnly cookies — no JWT stored client-side.

• 

  Organisation registration — every new account creates an isolated multi-tenant workspace with its own warehouses and users.

• 

  Live dashboard — Total SKUs, Received Today, Dispatched Today, Low Stock Alerts, Pending Sync. Data from Amazon Aurora PostgreSQL.

• 

  Real-time inventory across all warehouses. Stock updated automatically by PostgreSQL trigger trg_inventory_update — no app-level writes.

• 

  Receive Stock — records incoming goods offline-first. Transactions queued to IndexedDB and synced to Aurora automatically on reconnect.

• 

  Dispatch Stock — validates available stock before dispatch. Deducted via DB trigger. Offline dispatches queued and synced on reconnect.

• 

  Analytics with Recharts — monthly movement, top products, portfolio value. Supports 7d/30d/90d ranges with CSV export.

• 

  Sync Log — offline transactions queued in IndexedDB, sync status, last sync time. Background sync fires automatically on reconnect.

• 

  Offline mode — OfflineBanner shows queued count. App works fully without internet.

• 

  Purchase Orders — create, track, and receive supplier orders. Status workflow: draft → sent → partial → received → cancelled.

Inspiration

Distributors in East Africa managing stock across several locations — building materials, FMCG, pharmaceutical — still run operations on Excel spreadsheets, WhatsApp messages, and paper ledgers. The result: no one knows the actual stock count, low-stock surprises happen daily, and any dispute about what was received or dispatched is unresolvable because there is no audit trail.

Track: Monetizable B2B App — Ghalalytics targets distributors, wholesalers, and warehouse operators as paying business customers, with a tiered subscription model (Free / Growth / Scale).

The problem is solvable with software, but most existing warehouse management systems are desktop-only, expensive, and require constant internet connectivity. Power cuts and poor mobile data coverage are daily realities in the markets this targets.

What it does

Ghalalytics is a multi-tenant warehouse management SaaS. Each business registers an account, creating an isolated organisation with its own users, warehouses, products, suppliers, and stock records.

Core workflow:

1. The owner creates warehouse locations and adds products to the catalogue.
2. Staff log into the dashboard and record stock movements — receive, dispatch, or mutation — each generating a sequenced reference number (RCV-0001, DSP-0042).
3. Every movement triggers a PostgreSQL trigger (trg_inventory_update) that atomically upserts the inventory table. Application code never writes to inventory directly.
4. Dispatch validates available stock before creating any record. If quantity requested exceeds available stock, the API returns 422 Insufficient stock with a per-product breakdown.
5. When the device is offline, the transaction is saved to IndexedDB. When connectivity returns, useOfflineSync replays all pending entries in order, then revalidates all SWR caches automatically.
6. Managers create purchase orders, track partial deliveries, and generate monthly movement reports showing portfolio value, top products by volume, and stock health.
7. The owner invites team members with a role (admin / manager / operator / viewer), and each role has enforced API-level guards.

Authentication supports both email + password (scrypt via Node.js crypto, no external auth library) and Google OAuth 2.0 with CSRF state protection. Sessions are stored as 64-character hex tokens in an Aurora sessions table, set as httpOnly; Secure; SameSite=Lax cookies with 30-day TTL.

Billing is handled through a Gumroad webhook (/api/webhooks/gumroad). On a successful sale or subscription_renewed event (verified with HMAC-SHA256), the organisation's plan is upgraded from free to growth or scale, unlocking higher user limits and additional features.

How we built it

• Frontend: Next.js 16 App Router, React 19, Tailwind CSS v4, SWR for client-side state and caching.
• Backend: 26 Next.js Route Handlers, all parameterised SQL — no ORM, no string interpolation.
• Database: Amazon Aurora PostgreSQL, connected via pg Pool with IAM authentication using @aws-sdk/rds-signer and @vercel/functions/oidc. No passwords stored anywhere.
• Schema: 13 tables, 4 sequences, multiple indexes, and a PostgreSQL trigger function — all in scripts/001-schema.sql, applied via POST /api/setup.
• Offline: lib/offlineQueue.ts manages an IndexedDB store. lib/useOfflineSync.ts is a React hook that listens for online events and calls flushQueue().
• Service Worker: public/sw.js caches the app shell (all dashboard routes) so the UI loads with zero network. API routes are always network-only.
• Auth: Custom scrypt implementation using Node.js crypto.scrypt. Google OAuth 2.0 callback handles both existing-user login and new-org creation.
• Deployment: Vercel with the Amazon Aurora PostgreSQL integration, which automatically provides OIDC credentials — no hardcoded AWS keys anywhere in the codebase.
• Design: The interface uses a token-based design system (CSS custom properties) with a consistent component library. Every screen is built around the core workflow — receive, dispatch, mutate — so a warehouse operator can complete any task in under 60 seconds without training.

Challenges we faced

1. IAM authentication with a connection pool. The pg Pool requires a password function, not a static string. Aurora IAM tokens expire every 15 minutes. The solution was to pass an async () => signer.getAuthToken() function as the pool password, so a fresh token is fetched before each new connection.

2. Offline queue ordering. IndexedDB has no built-in ordering by insertion time. Each queued entry stores a client-generated UUID combined with a queuedAt ISO timestamp. The flush loop sorts by queuedAt before replaying to preserve the correct stock movement sequence.

3. Dispatch quantity enforcement. Simply checking inventory.quantity >= requested is not enough under concurrent requests. The solution is to perform the stock check and the INSERT inside the same BEGIN...COMMIT transaction, with the inventory trigger enforcing the CHECK (quantity >= 0) constraint as a safety net.

4. Multi-tenant isolation without RLS. Aurora PostgreSQL does not have Supabase-style Row Level Security in the connection pool model. Every single query in the codebase includes an explicit AND org_id = $n parameter, enforced by getAuthUser() returning the session user's org_id which is then passed to every query.

What we learned

• Aurora IAM authentication is production-ready and genuinely eliminates a whole category of credential management problems. The OIDC integration with Vercel means there are zero long-lived AWS credentials anywhere.
• PostgreSQL triggers are the right tool for derived tables like inventory. Putting the update logic in the database rather than the application layer means it is correct regardless of which code path creates a transaction line.
• Offline-first architecture is not about fancy sync protocols — it is about a simple, ordered queue with reliable replay semantics.

Built With

• amazonaurorapostgresql
• amazonwebservices
• aws-sdk/rds-signer
• googleoauth2.0
• indexeddb
• next.js
• node.js
• pg(node-postgres)
• postgresql
• react
• serviceworker
• swr
• tailwindcss
• typescript
• vercel
• vercel/functions