@budgetbuddyde/db

About

A shared database schema library for BudgetBuddy services. This package provides Drizzle ORM schemas, types, and utilities for both the backend and authentication services, ensuring a consistent database structure across all services.

Features

• Drizzle ORM Integration: Modern TypeScript ORM with type-safe queries
• Multi-Schema Support: Separate schemas for backend (budgetbuddy_backend) and auth (budgetbuddy_auth)
• Type Safety: Fully typed database entities with Zod validation schemas
• Schema Generation: Automated schema generation for Better-Auth integration
• Migration Support: Built-in migration tools via Drizzle Kit
• Shared Library: Used across all BudgetBuddy services to avoid duplication

Installation

This is an internal package used within the BudgetBuddy monorepo. It can be referenced in other packages via workspace dependencies:

{
  "dependencies": {
    "@budgetbuddyde/db": "workspace:*"
  }
}

Getting Started

Basic Usage

import { backendSchema, categories, transactions } from '@budgetbuddyde/db/backend';
import { authSchema, user, session } from '@budgetbuddyde/db/auth';
import { drizzle } from 'drizzle-orm/node-postgres';

// Initialize database connection
const db = drizzle(process.env.DATABASE_URL);

// Query backend data
const userCategories = await db.select().from(categories).where(eq(categories.ownerId, userId));

// Query auth data
const userSession = await db.select().from(session).where(eq(session.userId, userId));

Available Exports

Backend Schema (@budgetbuddyde/db/backend)

• Tables: categories, paymentMethods, transactions, recurringPayments, budgets
• Relations: Defined relationships between tables
• Types: TypeScript types for all entities
• Views: Predefined database views
• Schema: backendSchema - PostgreSQL schema object

Auth Schema (@budgetbuddyde/db/auth)

• Tables: user, session, account, verification
• Types: TypeScript types for authentication entities
• Schema: authSchema - PostgreSQL schema object

Database Schemas

Backend Schema (budgetbuddy_backend)

The backend schema contains all business logic tables:

Categories

Manage transaction categories for income and expenses.

{
  id: uuid (PK)
  ownerId: varchar
  name: varchar(40)
  description: text
  createdAt: timestamp
  updatedAt: timestamp
}

Payment Methods

Store user payment methods (bank accounts, credit cards, etc.).

{
  id: uuid (PK)
  ownerId: varchar
  name: varchar(40)
  provider: varchar(32)
  address: varchar(32)
  description: text
  createdAt: timestamp
  updatedAt: timestamp
}

Transactions

Record individual financial transactions.

{
  id: uuid (PK)
  ownerId: varchar
  categoryId: uuid (FK -> categories)
  paymentMethodId: uuid (FK -> paymentMethods)
  processedAt: timestamp
  receiver: varchar(100)
  transferAmount: double
  information: text
  createdAt: timestamp
  updatedAt: timestamp
}

Recurring Payments

Manage scheduled recurring payments.

{
  id: uuid (PK)
  ownerId: varchar
  categoryId: uuid (FK -> categories)
  paymentMethodId: uuid (FK -> paymentMethods)
  executeAt: integer (day of month)
  paused: boolean
  receiver: varchar(100)
  transferAmount: double
  information: text
  createdAt: timestamp
  updatedAt: timestamp
}

Budgets

Define income and expense budgets.

{
  id: uuid (PK)
  ownerId: varchar
  type: enum ('i' | 'e') // income or expense
  name: varchar(32)
  budget: double
  description: text
  createdAt: timestamp
  updatedAt: timestamp
}

Auth Schema (budgetbuddy_auth)

[!TIP] For more information about the schema generation and migration have a look into the documentation.

The auth schema is generated by Better-Auth and contains authentication-related tables:

User

Store user account information.

{
  id: text (PK)
  name: text
  email: text (unique)
  emailVerified: boolean
  image: text
  createdAt: timestamp
  updatedAt: timestamp
}

Session

Manage user sessions.

{
  id: text (PK)
  expiresAt: timestamp
  token: text (unique)
  userId: text (FK -> user)
  ipAddress: text
  userAgent: text
  createdAt: timestamp
  updatedAt: timestamp
}

Account

OAuth provider accounts linked to users.

{
  id: text (PK)
  accountId: text
  providerId: text
  userId: text (FK -> user)
  accessToken: text
  refreshToken: text
  idToken: text
  accessTokenExpiresAt: timestamp
  refreshTokenExpiresAt: timestamp
  scope: text
  password: text
  createdAt: timestamp
  updatedAt: timestamp
}

Migration Workflows

Prerequisites

[!WARNING] Make sure that the DATABASE_URL environment variable is set and points to the target database where the schema should be applied. If the environment variable is not set, the migration process will fail with an error.

export DATABASE_URL="postgresql://user:password@localhost:5432/budgetbuddy"

Generating Schemas

The database schema is defined using Drizzle ORM. To modify the schema:

1. Edit the table definitions in src/backend/tables.ts or src/auth/tables.ts
2. Update relationships in src/backend/relations.ts if needed
3. Generate migration files

Auth Schema Generation

The database schema required for the Better-Auth auth service can be generated using the @better-auth/cli:

npm run ba:schema-generate

[!WARNING] Nach dem Erstellen/Anpassen des Datenbankschemas für den Auth-Service können manuelle Änderungen notwendig sein, um nahtlose Integration mit dem Backend-Schema zu gewährleisten. Es ist wichtig, die generierten Schemas sorgfältig zu überprüfen und sicherzustellen, dass sie mit den Anforderungen des Backend-Schemas kompatibel sind.

Creating Migrations

Generate migration SQL files from your schema definitions:

npm run db:generate

This command will:

1. Run ba:schema-generate to generate auth schemas
2. Format the generated schema files with Prettier
3. Generate migration SQL files using Drizzle Kit

The migration files will be created in the drizzle/ directory of the services that use this package.

Applying Migrations

Apply the generated migrations to your database:

npm run db:migrate

This will execute all pending migrations in the correct order.

Database Studio

Drizzle Kit provides a web-based database browser:

npm run db:studio

This opens a local web interface where you can browse and edit your database tables.

Development

Build

npm run build

Compiles TypeScript to JavaScript in the lib/ folder.

Tests

# Run tests once
npm test

# Run tests in watch mode
npm run test:watch

Code Quality

# Biome Check (Linting + Formatting)
npm run check

# Auto-fix issues
npm run check:write

# Linting only
npm run lint

# Formatting only
npm run format

Usage in Services

Backend Service

import { drizzle } from 'drizzle-orm/node-postgres';
import { categories, transactions } from '@budgetbuddyde/db/backend';
import { eq } from 'drizzle-orm';

const db = drizzle(process.env.DATABASE_URL);

// Fetch user categories
const userCategories = await db
  .select()
  .from(categories)
  .where(eq(categories.ownerId, userId));

Auth Service

import { drizzle } from 'drizzle-orm/node-postgres';
import { user, session } from '@budgetbuddyde/db/auth';
import { eq } from 'drizzle-orm';

const db = drizzle(process.env.DATABASE_URL);

// Fetch user by email
const foundUser = await db
  .select()
  .from(user)
  .where(eq(user.email, email))
  .limit(1);

Important Notes

[!IMPORTANT] This package is a shared library used across all BudgetBuddy services (backend, auth-service) to ensure a consistent database schema and to avoid code duplication. The schema is defined using Drizzle ORM and can be generated and applied using the @better-auth/cli and Drizzle Kit.

[!WARNING] When making schema changes, ensure that:

1. All dependent services are updated accordingly
2. Migrations are tested in a development environment first
3. The auth and backend schemas remain compatible
4. Foreign key relationships are maintained

Credits

• Drizzle ORM
• Better Auth
• Zod