Skip to main content

​
Overview

The presupuestos table stores budget limits set by users for expense categories. Each budget defines a spending limit for a specific category and time period (typically monthly).

​
Table Schema

​
Columns

​
id
UUID
required
Primary key. Auto-generated unique identifier for the budget.
​
monto
NUMERIC(12,2)
required
Budget limit amount in USD. Maximum spending allowed for the category.
​
categoria_id
UUID
required
Foreign key to categorias. Links the budget to an expense category.
​
periodo
DATE
required
Budget period in YYYY-MM-DD format. Typically set to the first day of the month.
​
usuario_id
UUID
required
Foreign key to auth.users. Links the budget to its owner.
​
created_at
TIMESTAMPTZ
Timestamp when the budget was created. Auto-set by database.
​
updated_at
TIMESTAMPTZ
Timestamp when the budget was last modified. Auto-updated.

​
Relationships

  • Many-to-One with categorias: Each budget is linked to one expense category
  • Many-to-One with auth.users: Each budget belongs to one user

​
Row Level Security (RLS)

The table enforces RLS policies: 
CREATE POLICY "Users can view their own budgets"
  ON presupuestos FOR SELECT
  USING (auth.uid() = usuario_id);

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

CREATE POLICY "Users can update their own budgets"
  ON presupuestos FOR UPDATE
  USING (auth.uid() = usuario_id);

CREATE POLICY "Users can delete their own budgets"
  ON presupuestos FOR DELETE
  USING (auth.uid() = usuario_id);

​
Example Queries

​
Fetch All Budgets for Current User

const { data, error } = await supabase
  .from("presupuestos")
  .select("id, categoria_id, monto, periodo, categorias:categoria_id (id, nombre)")
  .eq("usuario_id", user.id);

​
Create a New Budget

const { error } = await supabase
  .from("presupuestos")
  .insert({
    monto: 500.00,
    categoria_id: categoryId,
    periodo: "2024-03-01",
    usuario_id: user.id
  });

​
Update Budget Amount

const { error } = await supabase
  .from("presupuestos")
  .update({ monto: 600.00 })
  .eq("id", budgetId);

​
Calculate Budget Progress

// Fetch budget
const { data: budget } = await supabase
  .from("presupuestos")
  .select("monto, categoria_id, periodo")
  .eq("id", budgetId)
  .single();

// Calculate spending for the period
const [year, month] = budget.periodo.split("-");
const startOfMonth = new Date(year, month - 1, 1);
const endOfMonth = new Date(year, month, 0);

const { data: transactions } = await supabase
  .from("transacciones")
  .select("monto")
  .eq("categoria_id", budget.categoria_id)
  .eq("tipo", "gasto")
  .gte("fecha_transaccion", startOfMonth.toISOString())
  .lte("fecha_transaccion", endOfMonth.toISOString());

const spent = transactions.reduce((sum, tx) => sum + tx.monto, 0);
const percentage = (spent / budget.monto) * 100;
const status = percentage >= 100 ? "danger" : percentage >= 90 ? "warn" : "ok";

​
Budget Status Levels

Available - Spending is within safe limits.
  • Green progress bar
  • No alerts displayed
  • Normal monitoring

​
Validation Rules

  • Amount: Must be greater than 0
  • Category Type: Only expense categories allowed (not income)
  • Period Format: Must be valid YYYY-MM-DD date
  • Duplicate Prevention: One budget per category per period (enforced in UI)

​
Indexes

CREATE INDEX idx_presupuestos_usuario_id ON presupuestos(usuario_id);
CREATE INDEX idx_presupuestos_categoria_id ON presupuestos(categoria_id);
CREATE INDEX idx_presupuestos_periodo ON presupuestos(periodo DESC);
CREATE UNIQUE INDEX idx_presupuestos_unique ON presupuestos(usuario_id, categoria_id, periodo);
The unique index prevents duplicate budgets for the same category and period.

Categories

Expense categories for budgets

Transactions

Spending tracked against budgets

Build docs developers (and LLMs) love

Get started for freeTalk to us