Tables

Overview

The BudgetBuddyDE schema contains the following tables:

payment_method: Payment methods and accounts
category: Categories for transactions
transaction: Individual financial transactions
recurring_payment: Recurring payments
budget: Budgets/spending goals
budget_category: Link between budgets and categories

payment_method

Stores payment methods such as bank accounts, credit cards, or digital wallets.

Fields

Field	Type	Constraints	Description
`payment_method_id`	UUID	PRIMARY KEY, DEFAULT RANDOM	Unique ID of the payment method
`owner_id`	VARCHAR	NOT NULL	Owner's ID
`name`	VARCHAR(40)	NOT NULL	Name of the payment method
`provider`	VARCHAR(32)	NOT NULL	Provider (e.g., "Sparkasse", "Visa")
`address`	VARCHAR(32)	NOT NULL	Account number/address
`description`	TEXT	NULLABLE	Optional description
`created_at`	TIMESTAMP WITH TIMEZONE	NOT NULL, DEFAULT NOW	Creation timestamp
`updated_at`	TIMESTAMP WITH TIMEZONE	NOT NULL, DEFAULT NOW	Last update timestamp

Relationships

Has Many: transaction (via payment_method_id)
Has Many: recurring_payment (via payment_method_id)

Example

{
  payment_method_id: "550e8400-e29b-41d4-a716-446655440000",
  owner_id: "user_123",
  name: "Checking Account",
  provider: "Sparkasse",
  address: "DE89370400440532013000",
  description: "Main account for daily expenses",
  created_at: "2024-01-15T10:30:00Z",
  updated_at: "2024-01-15T10:30:00Z"
}

Field	Type	Constraints	Description
`category_id`	UUID	PRIMARY KEY, DEFAULT RANDOM	Unique ID of the category
`owner_id`	VARCHAR	NOT NULL	Owner's ID
`name`	VARCHAR(40)	NOT NULL	Name of the category
`description`	TEXT	NULLABLE	Optional description
`created_at`	TIMESTAMP WITH TIMEZONE	NOT NULL, DEFAULT NOW	Creation timestamp
`updated_at`	TIMESTAMP WITH TIMEZONE	NOT NULL, DEFAULT NOW	Last update timestamp

transaction

Central table for all financial transactions (income and expenses).

Fields

Field	Type	Constraints	Description
`transaction_id`	UUID	PRIMARY KEY, DEFAULT RANDOM	Unique ID of the transaction
`owner_id`	VARCHAR	NOT NULL	Owner's ID
`category_id`	UUID	NOT NULL, FK → category(category_id) ON DELETE CASCADE	Transaction category
`payment_method_id`	UUID	NOT NULL, FK → payment_method(payment_method_id) ON DELETE CASCADE	Payment method used
`processed_at`	TIMESTAMP	NOT NULL	Transaction timestamp
`receiver`	VARCHAR(100)	NOT NULL	Receiver/sender
`transfer_amount`	DOUBLE PRECISION	NOT NULL	Amount (positive = income, negative = expense)
`information`	TEXT	NULLABLE	Additional information
`created_at`	TIMESTAMP WITH TIMEZONE	NOT NULL, DEFAULT NOW	Creation timestamp
`updated_at`	TIMESTAMP WITH TIMEZONE	NOT NULL, DEFAULT NOW	Last update timestamp

Relationships

Belongs To: category (via category_id)
Belongs To: payment_method (via payment_method_id)

Specifics

Positive amounts: Income/revenue
Negative amounts: Expenses
processed_at is the actual transaction time, not the booking time

Example

{
  transaction_id: "770e8400-e29b-41d4-a716-446655440002",
  owner_id: "user_123",
  category_id: "660e8400-e29b-41d4-a716-446655440001",
  payment_method_id: "550e8400-e29b-41d4-a716-446655440000",
  processed_at: "2024-01-20T14:30:00Z",
  receiver: "REWE Market",
  transfer_amount: -45.67,
  information: "Weekly shopping",
  created_at: "2024-01-20T15:00:00Z",
  updated_at: "2024-01-20T15:00:00Z"
}

recurring_payment

Recurring payments that are automatically processed on a specific day of the month.

Fields

Field	Type	Constraints	Description
`recurring_payment_id`	UUID	PRIMARY KEY, DEFAULT RANDOM	Unique ID of the recurring payment
`owner_id`	VARCHAR	NOT NULL	Owner's ID
`category_id`	UUID	NOT NULL, FK → category(category_id) ON DELETE CASCADE	Payment category
`payment_method_id`	UUID	NOT NULL, FK → payment_method(payment_method_id) ON DELETE CASCADE	Payment method used
`execute_at`	INTEGER	NOT NULL	Day of month (1-31) for execution
`paused`	BOOLEAN	NOT NULL, DEFAULT false	Is the payment paused?
`receiver`	VARCHAR(100)	NOT NULL	Receiver/sender
`transfer_amount`	DOUBLE PRECISION	NOT NULL	Amount
`information`	TEXT	NULLABLE	Additional information
`created_at`	TIMESTAMP WITH TIMEZONE	NOT NULL, DEFAULT NOW	Creation timestamp
`updated_at`	TIMESTAMP WITH TIMEZONE	NOT NULL, DEFAULT NOW	Last update timestamp

Relationships

Belongs To: category (via category_id)
Belongs To: payment_method (via payment_method_id)

Specifics

execute_at: For days > month length, executed at end of month (e.g., 31st is executed on Feb 28/29)
paused: Paused payments are skipped by the job

Example

{
  recurring_payment_id: "880e8400-e29b-41d4-a716-446655440003",
  owner_id: "user_123",
  category_id: "660e8400-e29b-41d4-a716-446655440001",
  payment_method_id: "550e8400-e29b-41d4-a716-446655440000",
  execute_at: 1,
  paused: false,
  receiver: "Landlord GmbH",
  transfer_amount: -850.00,
  information: "Monthly rent apartment",
  created_at: "2024-01-01T10:00:00Z",
  updated_at: "2024-01-01T10:00:00Z"
}

budget

Budgets/spending goals for specific categories.

Fields

Field	Type	Constraints	Description
`budget_id`	UUID	PRIMARY KEY, DEFAULT RANDOM	Unique ID of the budget
`owner_id`	VARCHAR	NOT NULL	Owner's ID
`type`	ENUM('i', 'e')	NOT NULL	Budget type: 'i' = Income, 'e' = Expense
`name`	VARCHAR(32)	NOT NULL	Name of the budget
`budget`	DOUBLE PRECISION	NOT NULL	Budget amount
`description`	TEXT	NULLABLE	Optional description
`created_at`	TIMESTAMP WITH TIMEZONE	NOT NULL, DEFAULT NOW	Creation timestamp
`updated_at`	TIMESTAMP WITH TIMEZONE	NOT NULL, DEFAULT NOW	Last update timestamp

Relationships

Has Many: budget_category (via budget_id)

Enum: budget_type

'i': Income (income budget)
'e': Expense (expense budget)

Example

{
  budget_id: "990e8400-e29b-41d4-a716-446655440004",
  owner_id: "user_123",
  type: "e",
  name: "Groceries Budget",
  budget: 400.00,
  description: "Monthly budget for groceries",
  created_at: "2024-01-01T10:00:00Z",
  updated_at: "2024-01-01T10:00:00Z"
}

budget_category

Junction table between budgets and categories (many-to-many).

Fields

Field	Type	Constraints	Description
`budget_id`	UUID	PRIMARY KEY (composite), FK → budget(budget_id) ON DELETE CASCADE	Budget ID
`category_id`	UUID	PRIMARY KEY (composite), FK → category(category_id) ON DELETE CASCADE	Category ID

Relationships

Belongs To: budget (via budget_id)
Belongs To: category (via category_id)

Specifics

Composite Primary Key: Combination of budget_id and category_id
Allows multiple categories per budget
Cascade delete: When deleting a budget or category, the links are automatically removed

Example

{
  budget_id: "990e8400-e29b-41d4-a716-446655440004",
  category_id: "660e8400-e29b-41d4-a716-446655440001"
}

Migrations

Database migrations are managed with Drizzle Kit:

# Create migration
npm run db:generate
 
# Execute migration
npm run db:migrate
 
# Push schema (Development)
npm run db:push

Schema Export

The complete schema is exported from the following files:

src/db/schema/tables.ts - Table definitions
src/db/schema/relations.ts - Relationships between tables
src/db/schema/views.ts - View definitions
src/db/schema/schema.ts - Schema configuration

Tables

On this page