Supabase Configuration

Overview

Sistema Financiero uses Supabase as its backend database and authentication provider. Supabase is an open-source Firebase alternative built on PostgreSQL.

Why Supabase?

Free tier with 500MB database storage
Built-in authentication and Row Level Security (RLS)
Real-time subscriptions
RESTful API auto-generated from your schema
No server management required

Creating a Supabase Project

Go to supabase.com and create a free account:

Click Start your project
Sign in with GitHub, Google, or email
Verify your email address (if using email signup)

The free tier includes 500MB database, 1GB file storage, and 2GB bandwidth - perfect for personal finance tracking.

Create New Project

From the Supabase dashboard:

Click New Project
Select your organization (or create one)
Fill in project details:
- Name: sistema-financiero (or your preferred name)
- Database Password: Generate a strong password (save this securely!)
- Region: Choose closest to your users (e.g., us-east-1, eu-central-1)
- Pricing Plan: Free (sufficient for personal use)
Click Create new project

Save your database password in a password manager. You’ll need it for direct database connections (though not for the Supabase client).

Project creation takes 1-2 minutes.

Get API Credentials

Once your project is ready:

Go to Settings (gear icon) → API
Copy these two values:
- Project URL (e.g., https://abcdefgh.supabase.co)
- anon public key (starts with eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...)

The anon key is safe to expose in your frontend code. It’s read-only and respects Row Level Security policies.

Keep these values for the Environment Variables setup step.

Database Schema Setup

Sistema Financiero requires one table: transacciones (transactions).

Open SQL Editor

In your Supabase dashboard:

Click SQL Editor in the left sidebar
Click New query

Create Transactions Table

Copy and paste this SQL schema into the editor:

SQL Schema

-- ============================================
-- Sistema Financiero - Database Schema
-- ============================================

-- Table: transacciones (Stores all income and expenses)
CREATE TABLE transacciones (
  -- Primary Key
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

  -- Core Transaction Fields
  fecha TIMESTAMP NOT NULL DEFAULT NOW(),
  tipo TEXT CHECK (tipo IN ('ingreso', 'gasto')) NOT NULL,
  monto NUMERIC(10, 2) NOT NULL CHECK (monto > 0),
  categoria TEXT NOT NULL,

  -- Optional Details
  concepto TEXT DEFAULT 'Transacción manual',
  descripcion TEXT,
  metodo_pago TEXT CHECK (metodo_pago IN ('Efectivo', 'Tarjeta', 'Transferencia')),
  registrado_por TEXT,
  foto_url TEXT,

  -- Metadata
  usuario_id UUID REFERENCES auth.users(id),
  created_at TIMESTAMP DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX idx_transacciones_fecha ON transacciones(fecha DESC);
CREATE INDEX idx_transacciones_tipo ON transacciones(tipo);
CREATE INDEX idx_transacciones_usuario ON transacciones(usuario_id);

-- Enable Row Level Security (RLS)
ALTER TABLE transacciones ENABLE ROW LEVEL SECURITY;

-- Policy: Users can only see their own transactions
CREATE POLICY "Users can view own transactions"
  ON transacciones FOR SELECT
  USING (auth.uid() = usuario_id);

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

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

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

Click Run (or press Ctrl+Enter) to execute the SQL.

You should see “Success. No rows returned” - this is expected for schema creation.

Verify Table Creation

Confirm the table was created:

Go to Table Editor in the left sidebar
You should see the transacciones table listed
Click on it to view the column structure

The table should have these columns:

id (uuid)
fecha (timestamp)
tipo (text)
monto (numeric)
categoria (text)
concepto (text)
descripcion (text)
metodo_pago (text)
registrado_por (text)
foto_url (text)
usuario_id (uuid)
created_at (timestamp)

Schema Explanation

Column Definitions

Column	Type	Constraints	Description
`id`	UUID	PRIMARY KEY	Unique identifier (auto-generated)
`fecha`	TIMESTAMP	NOT NULL	Transaction date (defaults to now)
`tipo`	TEXT	CHECK, NOT NULL	Transaction type: `'ingreso'` or `'gasto'`
`monto`	NUMERIC(10,2)	CHECK > 0, NOT NULL	Amount in currency (e.g., 1500.50)
`categoria`	TEXT	NOT NULL	Category (e.g., ‘Alimentación’, ‘Salario’)
`concepto`	TEXT	DEFAULT	Brief description (e.g., ‘Gasolina’)
`descripcion`	TEXT	NULLABLE	Detailed notes
`metodo_pago`	TEXT	CHECK	Payment method: `'Efectivo'`, `'Tarjeta'`, `'Transferencia'`
`registrado_por`	TEXT	NULLABLE	How it was recorded (e.g., ‘AI’, ‘Manual’)
`foto_url`	TEXT	NULLABLE	URL to receipt/invoice image
`usuario_id`	UUID	FOREIGN KEY	References `auth.users(id)`
`created_at`	TIMESTAMP	DEFAULT	Record creation timestamp

Valid Categories (Hardcoded in App)

Categories are not stored in a separate table - they’re hardcoded in the application code for simplicity.

Expense Categories (tipo = 'gasto'):

Alimentación (Food)
Transporte (Transportation)
Vivienda (Housing)
Salud (Health)
Entretenimiento (Entertainment)
Educación (Education)
Otros Gastos (Other Expenses)

Income Categories (tipo = 'ingreso'):

Salario (Salary)
Ventas (Sales)
Servicios (Services)
Inversiones (Investments)
Otros Ingresos (Other Income)

Indexes

Three indexes optimize query performance:

CREATE INDEX idx_transacciones_fecha ON transacciones(fecha DESC);
-- Speeds up: "Get transactions ordered by date"

CREATE INDEX idx_transacciones_tipo ON transacciones(tipo);
-- Speeds up: "Get all expenses" or "Get all income"

CREATE INDEX idx_transacciones_usuario ON transacciones(usuario_id);
-- Speeds up: "Get transactions for a specific user"

Indexes are automatically used by PostgreSQL’s query planner. You don’t need to reference them in your code.

Row Level Security (RLS)

What is RLS?

Row Level Security ensures users can only access their own transactions. Even if someone gets your anon API key, they can’t read other users’ data.

RLS Policies

Four policies are created:

CREATE POLICY "Users can view own transactions"
  ON transacciones FOR SELECT
  USING (auth.uid() = usuario_id);
-- Users can only SELECT rows where usuario_id matches their auth.uid()

Never disable RLS unless you’re building a public dashboard. Without RLS, any user can see all transactions in your database.

Storage Setup (Optional)

If you want to enable receipt/invoice uploads:

Create Storage Bucket

In Supabase dashboard:

Go to Storage in the left sidebar
Click New bucket
Bucket name: facturas
Public bucket: ✅ (check this box)
Click Create bucket

Configure Bucket Policies

Set storage policies:

SQL

-- Allow authenticated users to upload files
CREATE POLICY "Users can upload own invoices"
  ON storage.objects FOR INSERT
  WITH CHECK (
    bucket_id = 'facturas' AND
    auth.role() = 'authenticated'
  );

-- Allow public read access to invoices
CREATE POLICY "Public can view invoices"
  ON storage.objects FOR SELECT
  USING (bucket_id = 'facturas');

Run this SQL in the SQL Editor to enable file uploads.

Testing the Setup

Insert Test Data

Verify your schema works by inserting a test transaction:

SQL

INSERT INTO transacciones (
  fecha,
  tipo,
  monto,
  categoria,
  concepto,
  metodo_pago
) VALUES (
  NOW(),
  'gasto',
  150.00,
  'Alimentación',
  'Supermercado',
  'Tarjeta'
);

This will only work if RLS is temporarily disabled OR you insert with a valid usuario_id. For testing, you can temporarily disable RLS:

ALTER TABLE transacciones DISABLE ROW LEVEL SECURITY;

Remember to re-enable it after testing!

Query Test Data

Retrieve the test transaction:

SQL

SELECT * FROM transacciones ORDER BY fecha DESC LIMIT 10;

You should see your test transaction with all fields populated.

Clean Up Test Data

Delete the test transaction:

SQL

DELETE FROM transacciones WHERE concepto = 'Supermercado';

Real transactions will be created through the application once you complete the setup.

Troubleshooting

Error: uuid_generate_v4() does not exist

Problem: Missing UUID extension.Solution: Enable the extension:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Error: relation 'auth.users' does not exist

Problem: Auth schema not initialized (very rare).Solution: This should auto-exist in Supabase. Try refreshing the page or contact Supabase support.

Can't see inserted data in Table Editor

Problem: Row Level Security is blocking access.Solution: Either:

Insert with a valid usuario_id from auth.users
Temporarily disable RLS for testing (remember to re-enable!)
Use the Service Role Key (found in API settings) which bypasses RLS

Slow queries after inserting 10,000+ rows

Problem: Missing indexes or need to vacuum.Solution:

-- Rebuild statistics
ANALYZE transacciones;

-- If still slow, check if indexes exist
SELECT indexname FROM pg_indexes WHERE tablename = 'transacciones';

Next Steps

Database configured! Now set up your environment variables:

Environment Variables

Configure .env.local with your Supabase credentials

Introduction

Setup

Overview

Creating a Supabase Project

Database Schema Setup

Schema Explanation

Column Definitions

Valid Categories (Hardcoded in App)

Indexes

Row Level Security (RLS)

What is RLS?

RLS Policies

Storage Setup (Optional)

Testing the Setup

Troubleshooting

Next Steps

Environment Variables

Build docs developers (and LLMs) love

Introduction

Setup

​Overview

​Creating a Supabase Project

​Database Schema Setup

​Schema Explanation

​Column Definitions

​Valid Categories (Hardcoded in App)

​Indexes

​Row Level Security (RLS)

​What is RLS?

​RLS Policies

​Storage Setup (Optional)

​Testing the Setup

​Troubleshooting

​Next Steps

Environment Variables

Build docs developers (and LLMs) love

Overview

Creating a Supabase Project

Database Schema Setup

Schema Explanation

Column Definitions

Valid Categories (Hardcoded in App)

Indexes

Row Level Security (RLS)

What is RLS?

RLS Policies

Storage Setup (Optional)

Testing the Setup

Troubleshooting

Next Steps