Data Model

Overview

The BudgetBuddyDE data model is based on PostgreSQL and managed with Drizzle ORM. It consists of multiple tables for storing transactions, categories, payment methods, budgets, and recurring payments, as well as views for data aggregation and analysis.

Schema

All tables and views are located in the PostgreSQL schema backend.

Components

Technology

• Database: PostgreSQL
• ORM: Drizzle ORM
• Migrations: Drizzle Kit
• Schema Definition: TypeScript

Entity-Relationship Overview

The data model follows a relational design with the following main entities:

• User-related Data: All tables contain an ownerId field for multi-tenancy
• Transactions: Central entity for all financial flows
• Categories: Classification of transactions
• Payment Methods: Accounts and payment instruments
• Budgets: Spending goals with linked categories
• Recurring Payments: Automated, periodic transactions

Data Integrity

The schema implements various mechanisms to ensure data integrity:

• Foreign Keys with cascade delete for referential integrity
• NOT NULL Constraints for required fields
• Primary Keys (UUID) for unique identification
• Timestamps (createdAt, updatedAt) for audit trails
• Enum Types for controlled value lists (e.g., budget types)

Best Practices

UUIDs as Primary Keys

All tables use UUIDs as primary keys, generated with .defaultRandom(). This provides:

• Distributed system compatibility
• No collisions during parallel inserts
• Security through unpredictability

Soft Deletes

The current schema uses hard deletes with onDelete: 'cascade'. Deleted records are permanently removed, along with all dependent entries.

Timestamps

All tables contain:

• createdAt: Automatically set on creation
• updatedAt: Automatically updated on each change (via $onUpdate)