Database Architecture

Overview

Your Finance App uses PostgreSQL as its primary database, managed through Prisma ORM. The schema is designed for multi-tenant operation with soft deletes, hierarchical data, and optimized indexing.

Technology Stack

PostgreSQL

Production-grade relational database

Prisma

Type-safe ORM with migrations

Neon

Serverless PostgreSQL provider

Schema Configuration

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}

The directUrl is used for migrations in serverless environments like Neon, bypassing connection pooling.

Data Models

User Model

The central entity representing application users with support for multiple authentication providers:

model User {
  id       String @id @default(uuid())
  
  // Identity
  email    String  @unique
  password String?  // Optional for OAuth users
  
  firstName String @map("first_name")
  lastName  String @map("last_name")
  avatarUrl String?
  phone     String?

  // Social Authentication
  authProvider   AuthProvider @default(LOCAL) @map("auth_provider")
  authProviderId String?      @map("auth_provider_id")

  // Preferences
  currency       String @default("ARS")
  fiscalStartDay Int    @default(1)
  timezone       String @default("America/Argentina/Buenos_Aires")
  language       String @default("es")

  // Security
  role          Role      @default(USER)
  isActive      Boolean   @default(true)
  lastLogin     DateTime?
  
  resetToken       String?   @unique
  resetTokenExpiry DateTime?
  emailVerified    DateTime?

  // SaaS Business Model
  subscription     SubscriptionTier @default(FREE)
  externalCustomerId String?  // Stripe/MercadoPago

  // Timestamps
  createdAt DateTime  @default(now()) @map("created_at")
  updatedAt DateTime  @updatedAt @map("updated_at")
  deletedAt DateTime? @map("deleted_at")

  // Relationships
  transactions Transaction[]
  categories   Category[]
  accounts     Account[]
  budgets      Budget[]

  @@index([email])
  @@index([authProviderId])
  @@map("users")
}

User Model Features

Multi-provider Auth: Supports LOCAL, GOOGLE, MICROSOFT, and APPLE authentication
Soft Deletes: deletedAt field for data retention
Localization: User-specific timezone, language, and currency settings
Password Recovery: Token-based reset mechanism with expiry
SaaS Ready: Subscription tier and external payment provider integration

Transaction Model

Records all financial transactions (income, expense, and transfers):

model Transaction {
  id          String    @id @default(uuid())
  userId      String    @map("user_id")
  type        String    // 'income' | 'expense' | 'transfer'
  amount      Decimal   @db.Decimal(15, 2)
  currency    String    @default("ARS")
  description String?   @db.Text
  date        DateTime  @default(now())
  categoryId  String?   @map("category_id")
  accountId   String?   @map("account_id")
  
  createdAt   DateTime  @default(now()) @map("created_at")
  updatedAt   DateTime  @updatedAt @map("updated_at")
  deletedAt   DateTime? @map("deleted_at")
  
  // Relationships
  user     User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  category Category? @relation(fields: [categoryId], references: [id], onDelete: SetNull)
  account  Account?  @relation(fields: [accountId], references: [id])
  
  // Performance Indexes
  @@index([userId])
  @@index([date])
  @@index([categoryId])
  @@index([type])
  @@map("transactions")
}

Transactions use Decimal(15, 2) for precise monetary calculations, avoiding floating-point errors.

Key Features:

Cascade Delete: Deleting a user removes all their transactions
Soft Delete Pattern: deletedAt allows transaction recovery
Optimized Queries: Indexes on userId, date, categoryId, and type
Flexible Categories: onDelete: SetNull preserves transactions if category is deleted

Category Model

Hierarchical category system supporting parent-child relationships:

model Category {
  id      String  @id @default(uuid())
  userId  String  @map("user_id")
  name    String
  type    String  // 'income' | 'expense' | 'both'
  isFixed Boolean @default(false)
  color   String?
  icon    String?
  
  // Hierarchy (Self-referential)
  parentId  String?   @map("parent_id")
  parent    Category? @relation("CategoryHierarchy", fields: [parentId], references: [id])
  children  Category[] @relation("CategoryHierarchy")

  createdAt DateTime  @default(now()) @map("created_at")
  updatedAt DateTime  @updatedAt @map("updated_at")
  deletedAt DateTime? @map("deleted_at")
  
  user         User          @relation(fields: [userId], references: [id], onDelete: Cascade)
  transactions Transaction[]
  budgets      Budget[]
  
  @@unique([userId, name, type, parentId])
  @@index([userId])
  @@map("categories")
}

Hierarchical Design:

Serviceios (Parent)
├── Internet (Child)
├── Streaming (Child)
└── Otros (Child)

Alimentación (Parent)
├── Supermercado (Child)
├── Restaurantes (Child)
└── Otros (Child)

The @@unique([userId, name, type, parentId]) constraint allows “Otros” subcategory under different parents.

Account Model

Multi-purpose account system for wallets, savings, investments, and credit cards:

model Account {
  id           String      @id @default(uuid())
  name         String
  type         AccountType @default(WALLET)
  currency     String      @default("ARS")
  balance      Decimal     @default(0) @db.Decimal(15, 2)
  color        String?
  icon         String      @default("wallet")
  
  // Optional fields for SAVINGS type
  targetAmount Decimal?    @db.Decimal(10, 2)
  targetDate   DateTime?
  
  isDefault    Boolean     @default(false)
  userId       String
  user         User        @relation(fields: [userId], references: [id], onDelete: Cascade)

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  transactions Transaction[]

  @@map("accounts")
}

enum AccountType {
  WALLET        // Cash, Bank, Digital Wallet
  SAVINGS       // Goals, Savings
  INVESTMENT    // Investments
  CREDIT_CARD   // Credit Cards
}

Account Types:

WALLET

Daily cash flow - bank accounts, cash, digital wallets

SAVINGS

Accumulation goals with optional target amount and date

INVESTMENT

Investment portfolios and assets

CREDIT_CARD

Credit cards for future expense tracking

Budget Model

Monthly budget allocation per category:

model Budget {
  id         String  @id @default(uuid())
  amount     Decimal @db.Decimal(10, 2)
  month      Int
  year       Int

  userId     String
  user       User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  categoryId String
  category   Category @relation(fields: [categoryId], references: [id], onDelete: Cascade)

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@unique([userId, categoryId, month, year])
  @@map("budgets")
}

The unique constraint prevents duplicate budgets for the same category in a given month.

Enumerations

enum Role {
  USER
  ADMIN
}

enum SubscriptionTier {
  FREE
  PRO
}

enum CategoryType {
  INCOME
  EXPENSE
  BOTH
}

enum TransactionType {
  INCOME
  EXPENSE
  TRANSFER
}

enum AuthProvider {
  LOCAL
  GOOGLE
  MICROSOFT
  APPLE
}

Relationships

One-to-Many Relationships

Self-Referential Relationship

Indexing Strategy

Indexes are strategically placed for common query patterns:

User Indexes

email: Fast login lookups
authProviderId: Quick OAuth authentication

Transaction Indexes

userId: User-specific transaction queries
date: Date range filtering for reports
categoryId: Category-based filtering
type: Income vs expense queries

Category Indexes

userId: User-specific category lists

Soft Delete Pattern

All major entities implement soft deletes via deletedAt:

// Service implementation
async softDelete(id: string, userId: string) {
  return this.prisma.transaction.update({
    where: { id, userId },
    data: { deletedAt: new Date() },
  });
}

// Query filtering
async findAll(userId: string) {
  return this.prisma.transaction.findMany({
    where: {
      userId,
      deletedAt: null,  // Exclude soft-deleted records
    },
  });
}

Soft deletes enable data recovery and maintain referential integrity for analytics.

Migration Strategy

Prisma migrations are version-controlled and applied via CLI:

# Create a new migration
prisma migrate dev --name add_soft_deletes

# Apply migrations in production
prisma migrate deploy

# Reset database (development only)
prisma migrate reset

Always review generated migrations before applying to production. Some schema changes may require data backfilling.

Performance Considerations

Connection Pooling

Prisma uses connection pooling to manage database connections efficiently

Query Optimization

Strategic indexes on foreign keys and frequently queried fields

Decimal Precision

Using Decimal type prevents floating-point arithmetic errors

Cascade Deletes

Automatic cleanup of related records when parent is deleted

Next Steps

Authentication

Learn how user authentication works with JWT and OAuth

API Reference

Explore available API endpoints

Get Started

Architecture

Core Features

Advanced

Overview

Technology Stack

PostgreSQL

Prisma

Neon

Schema Configuration

Data Models

User Model

Transaction Model

Category Model

Account Model

WALLET

SAVINGS

INVESTMENT

CREDIT_CARD

Budget Model

Enumerations

Relationships

One-to-Many Relationships

Self-Referential Relationship

Indexing Strategy

Soft Delete Pattern

Migration Strategy

Performance Considerations

Connection Pooling

Query Optimization

Decimal Precision

Cascade Deletes

Next Steps

Authentication

API Reference

Build docs developers (and LLMs) love

Get Started

Architecture

Core Features

Advanced

​Overview

​Technology Stack

PostgreSQL

Prisma

Neon

​Schema Configuration

​Data Models

​User Model

​Transaction Model

​Category Model

​Account Model

WALLET

SAVINGS

INVESTMENT

CREDIT_CARD

​Budget Model

​Enumerations

​Relationships

​One-to-Many Relationships

​Self-Referential Relationship

​Indexing Strategy

​Soft Delete Pattern

​Migration Strategy

​Performance Considerations

Connection Pooling

Query Optimization

Decimal Precision

Cascade Deletes

​Next Steps

Authentication

API Reference

Build docs developers (and LLMs) love

Overview

Technology Stack

Schema Configuration

Data Models

User Model

Transaction Model

Category Model

Account Model

Budget Model

Enumerations

Relationships

One-to-Many Relationships

Self-Referential Relationship

Indexing Strategy

Soft Delete Pattern

Migration Strategy

Performance Considerations

Next Steps