Skip to main content

​
Overview

The categorias table stores user-defined categories for organizing income and expense transactions. Each category has a type (income or expense) and can be used across multiple transactions.

​
Table Schema

​
Columns

​
id
UUID
required
Primary key. Auto-generated unique identifier for the category.
​
nombre
VARCHAR
required
Category name. User-defined label for the category.
​
tipo
VARCHAR
required
Category type. Either "gasto" (expense) or "ingreso" (income).
​
usuario_id
UUID
required
Foreign key to auth.users. Links the category to its owner.
​
created_at
TIMESTAMPTZ
Timestamp when the category was created. Auto-set by database.
​
updated_at
TIMESTAMPTZ
Timestamp when the category was last modified. Auto-updated.

​
Relationships

  • One-to-Many with transacciones: A category can be used in many transactions
  • One-to-Many with presupuestos: An expense category can have multiple budgets
  • Many-to-One with auth.users: Each category belongs to one user

​
Row Level Security (RLS)

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

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

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

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

​
Example Queries

​
Fetch All Categories

const { data, error } = await supabase
  .from("categorias")
  .select("id, nombre, tipo");

​
Fetch Categories by Type

const { data: expenseCategories } = await supabase
  .from("categorias")
  .select("id, nombre")
  .eq("tipo", "gasto")
  .order("nombre", { ascending: true });

const { data: incomeCategories } = await supabase
  .from("categorias")
  .select("id, nombre")
  .eq("tipo", "ingreso")
  .order("nombre", { ascending: true });

​
Create a New Category

const { error } = await supabase
  .from("categorias")
  .insert({
    nombre: "Groceries",
    tipo: "gasto",
    usuario_id: user.id
  });

​
Calculate Category Usage

const { data: categories } = await supabase
  .from("categorias")
  .select("id, nombre, tipo");

const { data: transactions } = await supabase
  .from("transacciones")
  .select("categoria_id, monto");

const categoryStats = categories.map(category => {
  const categoryTxs = transactions.filter(tx => tx.categoria_id === category.id);
  const total = categoryTxs.reduce((sum, tx) => sum + tx.monto, 0);
  
  return {
    ...category,
    transactionCount: categoryTxs.length,
    totalAmount: total
  };
});

​
Category Types

Categories for tracking spending.Common examples:
  • Food & Dining
  • Transportation
  • Housing
  • Utilities
  • Entertainment
  • Healthcare
Features:
  • Can have budgets assigned
  • Displayed with red indicators
  • Used in expense breakdowns

​
Validation Rules

  • Name Required: Cannot be empty or null
  • Type Required: Must be either "gasto" or "ingreso"
  • Type Immutable: Cannot change category type after creation (to maintain data integrity)
  • Deletion Constraint: Cannot delete categories with associated transactions

​
Default Category

The system uses "Sin categoría" (Uncategorized) as a fallback when a category is not specified or has been deleted. This is handled at the application level, not in the database.

​
Indexes

CREATE INDEX idx_categorias_usuario_id ON categorias(usuario_id);
CREATE INDEX idx_categorias_tipo ON categorias(tipo);
CREATE INDEX idx_categorias_nombre ON categorias(nombre);

Transactions

Transactions using these categories

Budgets

Budget limits by expense category

Build docs developers (and LLMs) love

Get started for freeTalk to us