Database Schema

Overview

BudgetView uses PostgreSQL via Supabase as its primary database. The schema is designed around four core tables that handle user wallets, transactions, categories, and budgets. All tables enforce Row Level Security (RLS) to ensure users can only access their own data.

Database Technology

PostgreSQL 15+

Industry-leading open-source relational database with ACID compliance, advanced indexing, and JSON support.

Supabase enhancements:

Automatic RESTful API generation
Built-in authentication integration
Real-time change notifications (Postgres LISTEN/NOTIFY)
Connection pooling via PgBouncer

Core Tables

1. `billeteras` (Wallets)

Stores user wallet accounts (e.g., Cash, Bank, Savings).

CREATE TABLE billeteras (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  nombre VARCHAR(13) NOT NULL,
  usuario_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_billeteras_usuario ON billeteras(usuario_id);

Columns:

Column	Type	Constraints	Description
`id`	UUID	PRIMARY KEY	Unique wallet identifier
`nombre`	VARCHAR(13)	NOT NULL	Wallet display name (max 13 chars)
`usuario_id`	UUID	FOREIGN KEY	Links to authenticated user
`created_at`	TIMESTAMPTZ	DEFAULT now()	Record creation timestamp
`updated_at`	TIMESTAMPTZ	DEFAULT now()	Last modification timestamp

Usage example:

// Fetching user wallets
const { data: walletRows, error } = await supabase
  .from("billeteras")
  .select("id, nombre")
  .order("nombre", { ascending: true })

The 13-character limit ensures wallet names display properly on mobile devices without truncation.

2. `transacciones` (Transactions)

Records all income and expense transactions linked to wallets and categories.

CREATE TABLE transacciones (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  monto NUMERIC(12, 2) NOT NULL CHECK (monto > 0),
  tipo VARCHAR(10) NOT NULL CHECK (tipo IN ('ingreso', 'gasto')),
  descripcion TEXT,
  fecha_transaccion TIMESTAMPTZ NOT NULL DEFAULT now(),
  categoria_id UUID REFERENCES categorias(id) ON DELETE SET NULL,
  billetera_id UUID NOT NULL REFERENCES billeteras(id) ON DELETE CASCADE,
  usuario_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_transacciones_usuario ON transacciones(usuario_id);
CREATE INDEX idx_transacciones_billetera ON transacciones(billetera_id);
CREATE INDEX idx_transacciones_categoria ON transacciones(categoria_id);
CREATE INDEX idx_transacciones_fecha ON transacciones(fecha_transaccion DESC);

Columns:

Column	Type	Constraints	Description
`id`	UUID	PRIMARY KEY	Transaction identifier
`monto`	NUMERIC(12,2)	NOT NULL, > 0	Amount in USD (always positive)
`tipo`	VARCHAR(10)	CHECK constraint	Either ‘ingreso’ or ‘gasto’
`descripcion`	TEXT	NULLABLE	Optional transaction note (max 280 chars in UI)
`fecha_transaccion`	TIMESTAMPTZ	NOT NULL	Transaction date/time
`categoria_id`	UUID	FOREIGN KEY	Links to transaction category
`billetera_id`	UUID	FOREIGN KEY	Wallet where transaction occurred
`usuario_id`	UUID	FOREIGN KEY	Owner of the transaction

Type constraints:

Ingreso (Income)
Gasto (Expense)

Represents money coming into a wallet:

Salary
Refunds
Gifts
Investment returns

Query example:

// Fetching transactions with category details
const { data, error } = await supabase
  .from("transacciones")
  .select("id, monto, tipo, descripcion, fecha_transaccion, categoria_id, categorias(id, nombre, tipo)")
  .eq("billetera_id", walletId)
  .order("fecha_transaccion", { ascending: false })

All monetary amounts are stored in USD. The application converts to VES (Venezuelan Bolívar) on the client side using BCV exchange rates.

3. `categorias` (Categories)

Defines transaction categories (e.g., Food, Transport, Salary).

CREATE TABLE categorias (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  nombre VARCHAR(13) NOT NULL,
  tipo VARCHAR(10) NOT NULL CHECK (tipo IN ('ingreso', 'gasto')),
  usuario_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_categorias_usuario ON categorias(usuario_id);
CREATE INDEX idx_categorias_tipo ON categorias(tipo);

Columns:

Column	Type	Constraints	Description
`id`	UUID	PRIMARY KEY	Category identifier
`nombre`	VARCHAR(13)	NOT NULL	Category name (max 13 chars)
`tipo`	VARCHAR(10)	CHECK constraint	Either ‘ingreso’ or ‘gasto’
`usuario_id`	UUID	FOREIGN KEY	Owner of the category

Category filtering:

// Get expense categories only
const { data: expenseCategories } = await supabase
  .from("categorias")
  .select("id, nombre, tipo")
  .eq("tipo", "gasto")
  .order("nombre")

4. `presupuestos` (Budgets)

Tracks spending limits per category and time period.

CREATE TABLE presupuestos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  monto NUMERIC(12, 2) NOT NULL CHECK (monto > 0),
  periodo VARCHAR(7) NOT NULL, -- Format: YYYY-MM
  categoria_id UUID NOT NULL REFERENCES categorias(id) ON DELETE CASCADE,
  usuario_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(categoria_id, periodo, usuario_id)
);

CREATE INDEX idx_presupuestos_usuario ON presupuestos(usuario_id);
CREATE INDEX idx_presupuestos_categoria ON presupuestos(categoria_id);
CREATE INDEX idx_presupuestos_periodo ON presupuestos(periodo);

Columns:

Column	Type	Constraints	Description
`id`	UUID	PRIMARY KEY	Budget identifier
`monto`	NUMERIC(12,2)	NOT NULL, > 0	Budget limit in USD
`periodo`	VARCHAR(7)	NOT NULL	ISO period (e.g., “2024-01”)
`categoria_id`	UUID	FOREIGN KEY	Category being budgeted
`usuario_id`	UUID	FOREIGN KEY	Owner of the budget

Unique constraint: The UNIQUE(categoria_id, periodo, usuario_id) constraint ensures users can only have one budget per category per month.

// Creating a monthly budget
const { error } = await supabase
  .from("presupuestos")
  .insert({
    monto: 500.00,
    periodo: "2024-03",
    categoria_id: categoryId,
    usuario_id: user.id
  })

Entity Relationship Diagram

Row Level Security (RLS)

All tables enforce RLS policies. Direct database access without authentication will return zero rows.

Policy Structure

Each table has policies that enforce user ownership:

-- Enable RLS on all tables
ALTER TABLE billeteras ENABLE ROW LEVEL SECURITY;
ALTER TABLE transacciones ENABLE ROW LEVEL SECURITY;
ALTER TABLE categorias ENABLE ROW LEVEL SECURITY;
ALTER TABLE presupuestos ENABLE ROW LEVEL SECURITY;

-- Example policy: Users can only see their own wallets
CREATE POLICY "Users can view own wallets"
  ON billeteras FOR SELECT
  USING (auth.uid() = usuario_id);

CREATE POLICY "Users can insert own wallets"
  ON billeteras FOR INSERT
  WITH CHECK (auth.uid() = usuario_id);

CREATE POLICY "Users can update own wallets"
  ON billeteras FOR UPDATE
  USING (auth.uid() = usuario_id);

CREATE POLICY "Users can delete own wallets"
  ON billeteras FOR DELETE
  USING (auth.uid() = usuario_id);

Similar policies exist for transacciones, categorias, and presupuestos tables.

Policy enforcement:

User authenticates

Supabase Auth issues JWT with auth.uid() claim

Client sends query

JWT token included in Authorization header

PostgreSQL validates

RLS policies check if auth.uid() = usuario_id

Return filtered results

Only matching rows are returned to client

Database Indexes

Optimized indexes for common query patterns:

Table	Index	Purpose
`billeteras`	`idx_billeteras_usuario`	Fast user wallet lookups
`transacciones`	`idx_transacciones_usuario`	User transaction filtering
`transacciones`	`idx_transacciones_billetera`	Wallet transaction history
`transacciones`	`idx_transacciones_categoria`	Category-based reports
`transacciones`	`idx_transacciones_fecha`	Date range queries (DESC)
`categorias`	`idx_categorias_usuario`	User category lists
`categorias`	`idx_categorias_tipo`	Income/expense filtering
`presupuestos`	`idx_presupuestos_periodo`	Monthly budget retrieval

Data Types

Numeric Precision

NUMERIC(12, 2)  -- Max: 9,999,999,999.99 USD

Financial amounts use NUMERIC type (not FLOAT) to avoid floating-point precision errors in calculations.

Timestamps

All timestamps use TIMESTAMPTZ (timestamp with timezone) to properly handle users across different timezones:

// JavaScript Date objects are automatically converted
const isoDate = new Date().toISOString()  // "2024-03-07T14:30:00.000Z"

const { error } = await supabase
  .from("transacciones")
  .insert({ fecha_transaccion: isoDate })

Common Query Patterns

Calculate Wallet Balance

// Aggregate transactions to compute wallet balance
const { data: transactions } = await supabase
  .from("transacciones")
  .select("monto, tipo")
  .eq("billetera_id", walletId)

const balance = transactions.reduce((sum, tx) => {
  return tx.tipo === "ingreso" 
    ? sum + tx.monto 
    : sum - tx.monto
}, 0)

Budget vs. Actual Spending

// Compare budget limit to actual spending
const periodo = "2024-03"

const { data: budget } = await supabase
  .from("presupuestos")
  .select("monto")
  .eq("categoria_id", categoryId)
  .eq("periodo", periodo)
  .single()

const { data: transactions } = await supabase
  .from("transacciones")
  .select("monto")
  .eq("categoria_id", categoryId)
  .eq("tipo", "gasto")
  .gte("fecha_transaccion", `${periodo}-01`)
  .lt("fecha_transaccion", `${periodo}-31`)

const spent = transactions.reduce((sum, tx) => sum + tx.monto, 0)
const remaining = budget.monto - spent

Data Migrations

Supabase provides migration tools for schema changes:

# Create new migration
supabase migration new add_wallet_description

# Edit SQL file
echo "ALTER TABLE billeteras ADD COLUMN descripcion TEXT;" > migrations/..._add_wallet_description.sql

# Apply migration
supabase db push

Database Backups

Supabase Pro plan includes automatic daily backups with 7-day retention.

Manual backup options:

# Using pg_dump
pg_dump -h db.your-project.supabase.co -U postgres -d postgres > backup.sql

# Using Supabase CLI
supabase db dump -f backup.sql

Performance Considerations

Connection Pooling

PgBouncer handles up to 10,000 concurrent connections

Query Optimization

Indexes reduce query times from seconds to milliseconds

Prepared Statements

Supabase client uses parameterized queries (SQL injection protection)

JSON Support

PostgreSQL JSONB columns for flexible metadata storage

For authentication details, see the Authentication page.

Get Started

Core Features

User Guide

Technical

Overview

Database Technology

PostgreSQL 15+

Core Tables

1. `billeteras` (Wallets)

2. `transacciones` (Transactions)

3. `categorias` (Categories)

4. `presupuestos` (Budgets)

Entity Relationship Diagram

Row Level Security (RLS)

Policy Structure

Database Indexes

Data Types

Numeric Precision

Timestamps

Common Query Patterns

Calculate Wallet Balance

Budget vs. Actual Spending

Data Migrations

Database Backups

Performance Considerations

Connection Pooling

Query Optimization

Prepared Statements

JSON Support

Build docs developers (and LLMs) love

Get Started

Core Features

User Guide

Technical

​Overview

​Database Technology

PostgreSQL 15+

​Core Tables

​1. billeteras (Wallets)

​2. transacciones (Transactions)

​3. categorias (Categories)

​4. presupuestos (Budgets)

​Entity Relationship Diagram

​Row Level Security (RLS)

​Policy Structure

​Database Indexes

​Data Types

​Numeric Precision

​Timestamps

​Common Query Patterns

​Calculate Wallet Balance

​Budget vs. Actual Spending

​Data Migrations

​Database Backups

​Performance Considerations

Connection Pooling

Query Optimization

Prepared Statements

JSON Support

Build docs developers (and LLMs) love

Overview

Database Technology

Core Tables

1. `billeteras` (Wallets)

2. `transacciones` (Transactions)

3. `categorias` (Categories)

4. `presupuestos` (Budgets)

Entity Relationship Diagram

Row Level Security (RLS)

Policy Structure

Database Indexes

Data Types

Numeric Precision

Timestamps

Common Query Patterns

Calculate Wallet Balance

Budget vs. Actual Spending

Data Migrations

Database Backups

Performance Considerations