AkiraSaaS — Database Overview

How your PostgreSQL tables connect and what happens when data flows through the system.

Database: bizhub  ·  API: /api/products

Entity relationships

Core — businesses, users, products POS — sales, purchases, expenses Web — orders, M-Pesa 
erDiagram
    businesses ||--o{ users : has
    businesses ||--o{ categories : has
    businesses ||--o{ products : has
    businesses ||--o{ customers : has
    businesses ||--o{ suppliers : has
    businesses ||--o{ sales : has
    businesses ||--o{ purchases : has
    businesses ||--o{ expenses : has
    businesses ||--o{ business_subscriptions : has
    subscription_plans ||--o{ business_subscriptions : offers
    categories ||--o{ products : groups
    customers ||--o{ sales : places
    users ||--o{ sales : records
    users ||--o{ expenses : creates
    users ||--o{ orders : places
    sales ||--|{ sale_items : contains
    products ||--o{ sale_items : sold_in
    purchases ||--|{ purchase_items : contains
    products ||--o{ purchase_items : received_in
    suppliers ||--o{ purchases : supplies
    expense_categories ||--o{ expenses : categorizes
    products ||--o{ inventory_transactions : tracks
    orders ||--|{ order_items : contains
    products ||--o{ order_items : ordered_as
    orders ||--o| mpesa_transactions : paid_via

What happens when…

In-store sale (Flutter POS)

  1. businesses — identifies which shop
  2. users — cashier records the sale
  3. customers — optional buyer
  4. sales — invoice header (total, payment method)
  5. sale_items — each samosa line links to products
  6. inventory_transactions — stock reduced (optional)

Online order (web client)

  1. users — customer logs in
  2. products — menu items added to cart
  3. orders — checkout creates an order + phone number
  4. order_items — snapshot of product, price, quantity
  5. mpesa_transactions — STK push initiated
  6. orders.payment_status — updated to paid on M-Pesa callback

Stock purchase

  1. suppliers — who you buy from
  2. purchases — purchase header
  3. purchase_items — products and quantities received
  4. products.stock_quantity — increased
  5. inventory_transactions — audit entry

Expense tracking

  1. expense_categories — e.g. Gas, Transport
  2. users — who recorded it
  3. expenses — amount linked to business + category

Tables (live row counts)

businesses

0 rows

Top-level tenant. Every shop / samosa business lives here.

Referenced by ←
users.business_id
categories.business_id
products.business_id
customers.business_id
+7 more

subscription_plans

0 rows

Available SaaS plans (pricing tiers).

Referenced by ←
business_subscriptions.plan_id

business_subscriptions

0 rows

Links a business to its active plan.

Points to →
business_id → businesses
plan_id → subscription_plans

users

0 rows

Staff accounts scoped to a business (owner, cashier, etc.).

Points to →
business_id → businesses
Referenced by ←
sales.user_id
expenses.created_by
inventory_transactions.created_by
orders.user_id

categories

0 rows

Product groupings per business (e.g. Samosas, Drinks).

Points to →
business_id → businesses
Referenced by ←
products.category_id

global_categories

0 rows

Platform-wide product categories shared by all businesses.

Referenced by ←
global_products.global_category_id

global_products

0 rows

Shared catalog items (Coca Cola, Rice, etc.) managed by the platform.

Points to →
global_category_id → global_categories
Referenced by ←
products.global_product_id

products

0 rows

Business inventory — may link to global_products or be fully custom.

Points to →
business_id → businesses
category_id → categories
global_product_id → global_products
Referenced by ←
sale_items.product_id
purchase_items.product_id
inventory_transactions.product_id
order_items.product_id

customers

0 rows

People who buy from the business (in-store or online).

Points to →
business_id → businesses
Referenced by ←
sales.customer_id

suppliers

0 rows

Vendors you purchase stock from.

Points to →
business_id → businesses
Referenced by ←
purchases.supplier_id

sales

0 rows

A completed or pending in-store sale (invoice header).

Points to →
business_id → businesses
customer_id → customers
user_id → users
Referenced by ←
sale_items.sale_id

sale_items

0 rows

Line items inside a sale — links products to quantities sold.

Points to →
sale_id → sales
product_id → products

purchases

0 rows

Stock bought from a supplier.

Points to →
business_id → businesses
supplier_id → suppliers
Referenced by ←
purchase_items.purchase_id

purchase_items

0 rows

Line items inside a purchase.

Points to →
purchase_id → purchases
product_id → products

expense_categories

0 rows

Buckets for expenses (rent, transport, gas…).

Points to →
business_id → businesses
Referenced by ←
expenses.category_id

expenses

0 rows

Money spent, recorded by a user under a category.

Points to →
business_id → businesses
category_id → expense_categories
created_by → users

inventory_transactions

0 rows

Stock in/out audit trail per product.

Points to →
business_id → businesses
product_id → products
created_by → users

orders

0 rows

Online customer orders (web client checkout).

Points to →
user_id → users
Referenced by ←
order_items.order_id
mpesa_transactions.order_id

order_items

0 rows

Products in an online order.

Points to →
order_id → orders
product_id → products

mpesa_transactions

0 rows

M-Pesa STK push records tied to online orders.

Points to →
order_id → orders
→ Test the JSON API