Skip to content

Budget365 API Database Structure

Overview

The Budget365 API uses a comprehensive database schema designed for multi-tenant personal finance and budget management. The structure follows best practices for data normalization, referential integrity, and scalability.

Database Features

  • Multi-tenant architecture with proper data isolation
  • Soft deletes using GORM's deleted_at timestamps
  • Audit trails for critical operations (budget changes, savings goal contributions)
  • Flexible wallet system supporting various account types
  • Comprehensive transaction tracking with support for transfers
  • User authentication with both traditional and OAuth (Google) support

Core Tables

1. Tenants

Purpose: Organizations or groups using the application (multi-tenancy support) 

type Tenant struct {
    ID        uint           `gorm:"primarykey"`
    Name      string         `gorm:"not null"`
    CreatedAt time.Time
    UpdatedAt time.Time
    DeletedAt gorm.DeletedAt `gorm:"index"`
}

Key Features: - Root entity for multi-tenant data isolation - All financial data is scoped to a tenant - Private tenants created automatically for individual users

2. Users

Purpose: User accounts with support for email/password and OAuth authentication 

type User struct {
    ID        uint           `gorm:"primarykey"`
    FirstName string         `gorm:"not null"`
    LastName  string         `gorm:"not null"`
    Email     string         `gorm:"uniqueIndex;not null"`
    Password  *string        `gorm:"null"` // Nullable for OAuth users
    GoogleID  *string        `gorm:"null"` // For Google OAuth
    CreatedAt time.Time
    UpdatedAt time.Time
    DeletedAt gorm.DeletedAt `gorm:"index"`
}

Key Features: - Unique email constraint - Optional password (for OAuth-only users) - Support for Google OAuth integration - Name split into first/last for better data structure

3. Tenant Members

Purpose: Many-to-many relationship between users and tenants 

type TenantMember struct {
    ID       uint `gorm:"primarykey"`
    TenantID uint `gorm:"not null;index;uniqueIndex:idx_tenant_user"`
    UserID   uint `gorm:"not null;index;uniqueIndex:idx_tenant_user"`
    // Standard timestamps
}

Key Features: - Enables users to belong to multiple organizations - Composite unique index prevents duplicate memberships - Foundation for role-based access control (future extension)

Financial Tables

4. Categories

Purpose: Income and expense categorization 

type Category struct {
    ID          uint         `gorm:"primarykey"`
    TenantID    uint         `gorm:"not null;index"`
    Name        string       `gorm:"not null"`
    Description *string
    Type        CategoryType `gorm:"not null"` // 'income' or 'expense'
    Icon        *string
    Color       *string      // HEX color code
    // Standard timestamps
}

Enums: - CategoryType: income, expense

5. Wallets (Master Table)

Purpose: Central wallet management for all account types 

type Wallet struct {
    ID       uint       `gorm:"primarykey"`
    TenantID uint       `gorm:"not null;index"`
    UserID   uint       `gorm:"not null;index"`
    Name     string     `gorm:"not null"`
    Type     WalletType `gorm:"not null"`
    Balance  float64    `gorm:"not null;default:0.00;type:decimal(15,2)"`
    // Standard timestamps
}

Enums: - WalletType: Cash, Bank, Mobile_Money

6. Bank Accounts

Purpose: Bank-specific wallet details 

type BankAccount struct {
    ID          uint   `gorm:"primarykey"`
    WalletID    uint   `gorm:"not null;index"`
    BankName    string `gorm:"not null"`
    AccountType string `gorm:"not null"`
    // Standard timestamps
}

7. Mobile Money Accounts

Purpose: Mobile money specific wallet details 

type MobileMoneyAccount struct {
    ID       uint           `gorm:"primarykey"`
    WalletID uint           `gorm:"not null;index"`
    Provider MobileProvider `gorm:"not null"`
    // Standard timestamps
}

Enums: - MobileProvider: AirtelMoney, Mpamba

8. Budgets

Purpose: Budget planning and tracking 

type Budget struct {
    ID             uint      `gorm:"primarykey"`
    TenantID       uint      `gorm:"not null;index"`
    CategoryID     uint      `gorm:"not null;index"`
    Name           string    `gorm:"not null"`
    Description    *string
    ExpectedAmount float64   `gorm:"not null;type:decimal(15,2)"`
    ActualAmount   *float64  `gorm:"default:0.00;type:decimal(15,2)"`
    StartAt        time.Time `gorm:"not null"`
    ExpectedEndsAt time.Time `gorm:"not null"`
    ActualEndsAt   *time.Time
    // Standard timestamps
}

9. Budget Update Reasons (Audit Trail)

Purpose: Track all budget modifications with reasons 

type BudgetUpdateReason struct {
    ID          uint      `gorm:"primarykey"`
    TenantID    uint      `gorm:"not null;index"`
    BudgetID    uint      `gorm:"not null;index"`
    Reason      string    `gorm:"not null"`
    OldValues   string    `gorm:"not null;type:jsonb"`
    NewValues   string    `gorm:"not null;type:jsonb"`
    DateUpdated time.Time `gorm:"not null;default:CURRENT_TIMESTAMP"`
    // Standard timestamps
}

10. Savings Goals

Purpose: Long-term savings planning 

type SavingsGoal struct {
    ID           uint      `gorm:"primarykey"`
    TenantID     uint      `gorm:"not null;index"`
    Title        string    `gorm:"not null"`
    Description  *string
    TargetAmount float64   `gorm:"not null;type:decimal(15,2)"`
    StartAt      time.Time `gorm:"not null"`
    EndsAt       time.Time `gorm:"not null"`
    IsCompleted  bool      `gorm:"not null;default:false"`
    IsClosed     bool      `gorm:"not null;default:false"`
    // Standard timestamps
}

11. Savings Goal Contributions

Purpose: Track contributions to savings goals 

type SavingsGoalContribution struct {
    ID            uint    `gorm:"primarykey"`
    TenantID      uint    `gorm:"not null;index"`
    SavingsGoalID uint    `gorm:"not null;index"`
    Amount        float64 `gorm:"not null;type:decimal(15,2)"`
    // Standard timestamps
}

12. Savings Goal Contribution Update Reasons (Audit Trail)

Purpose: Track modifications to savings contributions 

type SavingsGoalContributionUpdateReason struct {
    ID             uint      `gorm:"primarykey"`
    TenantID       uint      `gorm:"not null;index"`
    ContributionID uint      `gorm:"not null;index"`
    Reason         string    `gorm:"not null"`
    Amount         float64   `gorm:"not null;type:decimal(15,2)"`
    OldValues      string    `gorm:"not null;type:jsonb"`
    NewValues      string    `gorm:"not null;type:jsonb"`
    DateUpdated    time.Time `gorm:"not null;default:CURRENT_TIMESTAMP"`
    // Standard timestamps
}

13. Transactions

Purpose: Record all financial transactions 

type Transaction struct {
    ID          uint            `gorm:"primarykey"`
    TenantID    uint            `gorm:"not null;index"`
    CategoryID  *uint           `gorm:"index"` // Optional for transfers
    WalletID    uint            `gorm:"not null;index"`
    ToWalletID  *uint           `gorm:"index"` // Required only for transfers
    Type        TransactionType `gorm:"not null"`
    Amount      float64         `gorm:"not null;type:decimal(15,2)"`
    Description *string
    Date        time.Time       `gorm:"not null;default:CURRENT_TIMESTAMP"`
    Reference   *string
    Notes       *string
    // Standard timestamps
}

Enums: - TransactionType: income, expense, transfer

Business Rules: - For transfer transactions: ToWalletID is required, CategoryID is optional - For income/expense transactions: ToWalletID must be null

System Tables

14. Reset Passwords

Purpose: Secure password reset functionality 

type ResetPassword struct {
    ID         uint      `gorm:"primarykey"`
    UserID     uint      `gorm:"not null;index"`
    Token      string    `gorm:"not null"`
    IsVerified bool      `gorm:"not null;default:false"`
    ExpiresAt  time.Time `gorm:"not null"`
    // Standard timestamps
}

15. Notifications

Purpose: System and user notifications 

type Notification struct {
    ID          uint    `gorm:"primarykey"`
    TenantID    uint    `gorm:"not null;index"`
    UserID      *uint   `gorm:"index"` // Optional for tenant-wide notifications
    Title       string  `gorm:"not null"`
    Type        string  `gorm:"not null"`
    Description *string
    IsRead      bool    `gorm:"not null;default:false"`
    // Standard timestamps
}

Database Enums

CategoryType

  • income: Revenue or money coming in
  • expense: Costs or money going out

WalletType

  • Cash: Physical cash
  • Bank: Bank accounts
  • Mobile_Money: Mobile money services

MobileProvider

  • AirtelMoney: Airtel Money service
  • Mpamba: Mpamba mobile money service

TransactionType

  • income: Money coming into a wallet
  • expense: Money going out of a wallet
  • transfer: Money moving between wallets

Key Relationships

One-to-Many Relationships

  • Tenant → Categories, Wallets, Budgets, SavingsGoals, Transactions, Notifications
  • User → Wallets, ResetPasswords
  • Category → Budgets, Transactions
  • Wallet → Transactions (as source), BankAccount, MobileMoneyAccount
  • Budget → BudgetUpdateReasons
  • SavingsGoal → SavingsGoalContributions
  • SavingsGoalContribution → SavingsGoalContributionUpdateReasons

Many-to-Many Relationships

  • User ↔ Tenant (through TenantMember)

Optional Relationships

  • Transaction → Category (nullable for transfers)
  • Transaction → ToWallet (nullable for non-transfers)
  • Notification → User (nullable for tenant-wide notifications)

Data Integrity Features

Constraints

  • Unique email addresses for users
  • Unique tenant-user combinations in TenantMember
  • Proper foreign key constraints with cascading deletes
  • Check constraints for transaction business rules

Soft Deletes

All models implement soft deletes using GORM's DeletedAt field, allowing data recovery and maintaining referential integrity.

Audit Trails

Critical operations (budget updates, savings goal contribution changes) maintain full audit trails with: - Reason for change - Old and new values (stored as JSONB) - Timestamp of modification - User context through tenant relationship

Indexing Strategy

  • Primary keys on all ID fields
  • Foreign key indexes for optimal join performance
  • Composite unique indexes for business rules (e.g., tenant-user combination)
  • Indexes on frequently queried fields (tenant_id, user_id, category_id, etc.)

Multi-Tenancy Implementation

  • All financial data is scoped to tenant_id
  • Users can belong to multiple tenants
  • Private tenants created automatically for individual user accounts
  • Data isolation enforced at the application and database level

This structure provides a robust foundation for a comprehensive personal finance application with enterprise-grade features like multi-tenancy, audit trails, and flexible account management.