Database Migrations

Overview

Budget Bee uses SQL migration files to set up and evolve the database schema. All migrations are located in packages/core/migrations/ and must be run in a specific order.

Migration Files

The migration system consists of several files:

packages/core/migrations/
├── better-auth-migrations.sql           # Authentication tables
├── init.sql                             # Core schema and policies
├── migration_2026_01_11_feature_flags.sql
├── migration_2026_01_19_colors_on_category.sql
├── migration_2026_02_01_dashboard_views.sql
└── functions.sql                        # Database functions

Migration Order

Migrations must be executed in this exact order:

better-auth-migrations.sql

Creates authentication and authorization tables:

users - User accounts
sessions - User sessions
accounts - OAuth provider accounts
verifications - Email verification tokens
organizations - Multi-tenant organizations
members - Organization members
invitations - Organization invites
jwks - JSON Web Key Sets for JWT validation

init.sql

Creates core application tables and security policies:

categories - Transaction categories
tags - Transaction tags
transactions - Financial transactions
subscriptions - Recurring subscriptions
app_subscriptions - Application billing
Row-level security (RLS) policies
Database roles and permissions

Dated Migration Files

Feature-specific migrations run in chronological order:

migration_2026_01_11_feature_flags.sql - Feature flags system
migration_2026_01_19_colors_on_category.sql - Category color enums
migration_2026_02_01_dashboard_views.sql - Dashboard customization

functions.sql

Creates database functions and stored procedures:

get_transaction_by_category() - Aggregate transactions
get_filtered_transactions() - Dynamic filtering
get_transaction_aggregate() - Statistical functions
delete_category() - Safe category deletion

Running Migrations

Automated Method (Recommended)

Use the provided script to run all migrations in order:

make migrate
# Or directly:
./scripts/run_migrations.sh

This script:

Validates required environment variables
Creates database roles if needed
Runs migrations in the correct order
Reports success or failure for each migration

Manual Method

If the script fails, run migrations manually:

Set Environment Variables

source ./.env

Create Database Roles

./scripts/create_roles.sh

Creates auth_admin and subscription_admin roles.

Run Migrations in Order

psql "postgres://$POSTGRES_USER:$POSTGRES_PASSWORD@$POSTGRES_HOST:$POSTGRES_PORT/$POSTGRES_DATABASE" \
  -f packages/core/migrations/better-auth-migrations.sql

psql "postgres://$POSTGRES_USER:$POSTGRES_PASSWORD@$POSTGRES_HOST:$POSTGRES_PORT/$POSTGRES_DATABASE" \
  -f packages/core/migrations/init.sql

# Run dated migrations
for migration in packages/core/migrations/migration_*.sql; do
  psql "postgres://$POSTGRES_USER:$POSTGRES_PASSWORD@$POSTGRES_HOST:$POSTGRES_PORT/$POSTGRES_DATABASE" \
    -f "$migration"
done

psql "postgres://$POSTGRES_USER:$POSTGRES_PASSWORD@$POSTGRES_HOST:$POSTGRES_PORT/$POSTGRES_DATABASE" \
  -f packages/core/migrations/functions.sql

Migration Script Deep Dive

The run_migrations.sh script (scripts/run_migrations.sh):

#!/usr/bin/env bash

# Validate environment
source ./.env
require_envs POSTGRES_DATABASE POSTGRES_HOST POSTGRES_PORT \
  POSTGRES_USER POSTGRES_PASSWORD \
  POSTGRES_AUTH_ADMIN_USER POSTGRES_AUTH_ADMIN_PASSWORD \
  POSTGRES_SUBSCRIPTION_ADMIN_USER POSTGRES_SUBSCRIPTION_ADMIN_PASSWORD

# Create roles first
./scripts/create_roles.sh

SQL_DIR="$GIT_ROOT/packages/core/migrations"
DB_URL="postgres://$POSTGRES_USER:$POSTGRES_PASSWORD@$POSTGRES_HOST:$POSTGRES_PORT/$POSTGRES_DATABASE"

# Run migration function
run_migration() {
  local migration_file="$1"
  echo "INFO: Running migration from $migration_file"
  psql "$DB_URL" -f "$migration_file"
  if [ $? -ne 0 ]; then
    echo "ERROR: Failed to run migration from $migration_file"
    exit 1
  fi
}

# Execute in order
run_migration "$SQL_DIR/better-auth-migrations.sql"
run_migration "$SQL_DIR/init.sql"

# Run dated migrations
for migration_file in $(ls "$SQL_DIR"/migration_*.sql 2>/dev/null | sort); do
  run_migration "$migration_file"
done

run_migration "$SQL_DIR/functions.sql"

Database Roles

Budget Bee creates several PostgreSQL roles with specific permissions:

Application Roles

Role	Purpose	Permissions
`anon`	Unauthenticated users	No table access (NOLOGIN)
`authenticated`	Logged-in users	Full CRUD via RLS policies
`auth_admin`	Better Auth service	Manages auth tables, bypasses RLS
`subscription_admin`	Billing service	Manages app_subscriptions, bypasses RLS

Creating Roles

The create_roles.sh script creates admin roles:

CREATE ROLE auth_admin WITH LOGIN PASSWORD 'password' BYPASSRLS;
CREATE ROLE subscription_admin WITH LOGIN PASSWORD 'password' BYPASSRLS;

These roles bypass row-level security. Use strong passwords and restrict network access.

Migration Details

Better Auth Migration

Creates the authentication system:

-- Users table
create table "users" (
  "id" text not null primary key,
  "name" text not null,
  "email" text not null unique,
  "email_verified" boolean not null,
  "image" text,
  "created_at" timestamptz default CURRENT_TIMESTAMP not null,
  "updated_at" timestamptz default CURRENT_TIMESTAMP not null
);

-- Organizations for multi-tenancy
create table "organizations" (
  "id" text not null primary key,
  "name" text not null,
  "slug" text not null unique,
  "logo" text,
  "created_at" timestamptz not null,
  "metadata" text
);

-- Organization members
create table "members" (
  "id" text not null primary key,
  "organization_id" text not null references "organizations" ("id") on delete cascade,
  "user_id" text not null references "users" ("id") on delete cascade,
  "role" text not null,
  "created_at" timestamptz not null
);

Init Migration

Creates core business tables with row-level security:

-- Categories
create table categories (
  id uuid primary key default gen_random_uuid(),
  name varchar(255) not null,
  description varchar(1000),
  color varchar(6),
  user_id text references users (id),
  organization_id text references organizations (id)
);

-- Transactions
create table transactions (
  id uuid primary key default gen_random_uuid(),
  amount numeric(10, 2) default 0,
  currency varchar(3) default 'usd',
  user_id text references users (id) on delete cascade,
  organization_id text references organizations (id) on delete cascade,
  category_id uuid references categories (id) on delete set null,
  name varchar(255),
  description varchar(1000),
  status varchar(8) default 'paid',
  transaction_date timestamp default current_timestamp,
  created_at timestamp default current_timestamp,
  updated_at timestamp default current_timestamp
);

Row-Level Security

Init migration enables RLS to ensure users only see their own data:

alter table transactions enable row level security;

-- Users can only see their own transactions or their organization's
CREATE POLICY limit_transactions_select ON transactions FOR SELECT
  TO authenticated USING (
    (organization_id IS NULL AND user_id = uid())
    OR (organization_id = org_id() AND check_ac_current('transaction', 'list'))
  );

-- Users can only create transactions for themselves or their organization
CREATE POLICY limit_transactions_insert ON transactions FOR INSERT
  TO authenticated WITH CHECK (
    (organization_id IS NULL AND user_id = uid())
    OR (organization_id = org_id() AND check_ac_current('transaction', 'create'))
  );

Helper functions uid() and org_id() extract user and organization IDs from JWT tokens.

Feature Flags Migration

Adds a feature flag system:

CREATE TYPE feature_flag_scope AS ENUM('global', 'account', 'organization');

CREATE TABLE feature_flags (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  key TEXT NOT NULL,
  value BOOLEAN DEFAULT FALSE,
  scope feature_flag_scope NOT NULL,
  scope_id TEXT,
  description TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE (key, scope, scope_id)
);

Category Colors Migration

Adds enum-based colors to categories:

create type builtin_colors as enum(
  'gray', 'brown', 'orange', 'yellow', 'green',
  'blue', 'purple', 'pink', 'red'
);

alter table categories
drop column color;

alter table categories
add column color builtin_colors default get_random_builtin_color();

Dashboard Views Migration

Enables customizable dashboards:

CREATE TABLE dashboard_views (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL DEFAULT 'My Dashboard',
  user_id TEXT REFERENCES users(id) ON DELETE CASCADE,
  organization_id TEXT REFERENCES organizations(id) ON DELETE CASCADE,
  widgets JSONB NOT NULL DEFAULT '[]'::jsonb,
  is_default BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT current_timestamp,
  updated_at TIMESTAMP DEFAULT current_timestamp
);

Functions Migration

Creates stored procedures for data aggregation:

CREATE OR REPLACE FUNCTION get_filtered_transactions(filters jsonb)
RETURNS SETOF transactions AS $$
DECLARE
  query_sql text;
  where_clauses text[] := '{}';
BEGIN
  -- Dynamic query building with whitelisted fields
  -- Supports filters for amount, category, status, dates
  -- Returns filtered transaction set
END;
$$ LANGUAGE plpgsql;

Verifying Migrations

Check Tables

\dt

Should show all tables including users, transactions, categories, etc.

Verify Roles

\du

Should list anon, authenticated, auth_admin, subscription_admin.

Test RLS Policies

SELECT * FROM pg_policies WHERE tablename = 'transactions';

Check Functions

\df

Should list all custom functions.

Rolling Back Migrations

Budget Bee does not include automated rollback migrations. Always backup before migrating.

To rollback:

Restore from backup:
```
pg_restore -d budgetbee backup.sql
```

Manual rollback (carefully reverse each migration):

DROP TABLE IF EXISTS dashboard_views CASCADE;
-- Continue in reverse order...

Common Issues

Role Already Exists

ERROR: role "auth_admin" already exists

This is normal on re-runs. The script handles this gracefully:

DO $$
BEGIN
  CREATE ROLE auth_admin WITH LOGIN PASSWORD 'password' BYPASSRLS;
EXCEPTION WHEN duplicate_object THEN
  RAISE NOTICE 'Role already exists, skipping.';
END
$$;

Missing Environment Variables

ERROR: POSTGRES_PASSWORD is not set

Source your .env file:

source ./.env
./scripts/run_migrations.sh

Connection Refused

Ensure PostgreSQL is running:

docker ps | grep bu-postgres
docker exec bu-postgres pg_isready

Best Practices

Always Backup

Create a backup before running migrations in production

Test First

Run migrations in a staging environment first

Read the SQL

Review migration files before executing

Monitor Logs

Watch migration output for errors

Next Steps

PostgreSQL Config

Configure PostgreSQL settings

PostgREST Setup

Configure the REST API

Redis Setup

Optional caching layer

Deployment

Infrastructure

Overview

Migration Files

Migration Order

Running Migrations

Automated Method (Recommended)

Manual Method

Migration Script Deep Dive

Database Roles

Application Roles

Creating Roles

Migration Details

Better Auth Migration

Init Migration

Row-Level Security

Feature Flags Migration

Category Colors Migration

Dashboard Views Migration

Functions Migration

Verifying Migrations

Rolling Back Migrations

Common Issues

Role Already Exists

Missing Environment Variables

Connection Refused

Best Practices

Always Backup

Test First

Read the SQL

Monitor Logs

Next Steps

PostgreSQL Config

PostgREST Setup

Redis Setup

Build docs developers (and LLMs) love

Deployment

Infrastructure

​Overview

​Migration Files

​Migration Order

​Running Migrations

​Automated Method (Recommended)

​Manual Method

​Migration Script Deep Dive

​Database Roles

​Application Roles

​Creating Roles

​Migration Details

​Better Auth Migration

​Init Migration

​Row-Level Security

​Feature Flags Migration

​Category Colors Migration

​Dashboard Views Migration

​Functions Migration

​Verifying Migrations

​Rolling Back Migrations

​Common Issues

​Role Already Exists

​Missing Environment Variables

​Connection Refused

​Best Practices

Always Backup

Test First

Read the SQL

Monitor Logs

​Next Steps

PostgreSQL Config

PostgREST Setup

Redis Setup

Build docs developers (and LLMs) love

Overview

Migration Files

Migration Order

Running Migrations

Automated Method (Recommended)

Manual Method

Migration Script Deep Dive

Database Roles

Application Roles

Creating Roles

Migration Details

Better Auth Migration

Init Migration

Row-Level Security

Feature Flags Migration

Category Colors Migration

Dashboard Views Migration

Functions Migration

Verifying Migrations

Rolling Back Migrations

Common Issues

Role Already Exists

Missing Environment Variables

Connection Refused

Best Practices

Next Steps