Database Schema

Core Tables

Table Description
organizations Multi-tenant organizations
users User accounts
roles Role definitions
permissions Permission definitions
user_roles User-role mappings
role_permissions Role-permission mappings
areas Farm areas/zones
ponds Individual ponds
species Aquaculture species
farming_cycles Farming cycles
devices IoT devices
device_telemetry Time-series sensor data
device_alerts Alert records
alert_configs Alert threshold configurations

Inventory Tables

Table Description
inventory_items Inventory item catalog
inventory_batches Batch tracking
inventory_warehouses Warehouse locations
inventory_transactions Stock movements

Supplier Tables

Table Description
suppliers Supplier information

Financial Tables

Table Description
cost_categories Expense/revenue categories
expense_records Individual expense entries
revenue_records Individual revenue entries
recurring_expenses Scheduled recurring expenses

TimescaleDB Hypertable

CREATE TABLE device_telemetry (
  time        TIMESTAMPTZ       NOT NULL,
  device_id   UUID              NOT NULL,
  metric_name VARCHAR(50)       NOT NULL,
  value       DOUBLE PRECISION  NOT NULL,
  unit        VARCHAR(20)
);

SELECT create_hypertable('device_telemetry', 'time');

Entity Relationships

Organization (1) ──< User
Organization (1) ──< Area
Organization (1) ──< Pond
Organization (1) ──< Device
Organization (1) ──< FarmingCycle
Organization (1) ──< Supplier
Organization (1) ──< CostCategory
Organization (1) ──< InventoryItem

Area (1) ──< Pond
Pond (1) ──< FarmingCycle
Pond <──> Device (Many-to-Many)

Species (1) ──< FarmingCycle
FarmingCycle (1) ──< GrowthRecord
FarmingCycle (1) ──< DailyLog
FarmingCycle (1) ──< ExpenseRecord
FarmingCycle (1) ──< RevenueRecord

User (1) ──< UserRole ──< Role

InventoryItem (1) ──< InventoryBatch
InventoryWarehouse (1) ──< InventoryTransaction
Supplier (1) ──< InventoryTransaction

CostCategory (1) ──< ExpenseRecord
CostCategory (1) ──< RevenueRecord
CostCategory (1) ──< RecurringExpense

Last updated: March 2026