Step 1 — Create a free Supabase project
Go to supabase.com → New Project. Choose a name, set a database password, pick the region closest to you. Takes ~2 minutes.
Step 2 — Run this SQL to create the tables
In your Supabase project, go to SQL Editor → New Query, paste and run:
-- Transactions table
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
type TEXT NOT NULL,
transfer_keywords TEXT[],
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE transactions (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
date TEXT, description TEXT, amount NUMERIC,
account_id INTEGER REFERENCES accounts(id),
category_id INTEGER, receipt_path TEXT,
txn_type TEXT DEFAULT 'expense',
quick_entry_id UUID, reconciled BOOLEAN DEFAULT false,
created_at TIMESTAMPTZ DEFAULT now()
);
-- Quick entries (logged from phone)
CREATE TABLE quick_entries (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
amount NUMERIC NOT NULL,
note TEXT,
category_id INTEGER,
transaction_id UUID REFERENCES transactions(id),
matched BOOLEAN DEFAULT false,
created_at TIMESTAMPTZ DEFAULT now()
);
-- Categories table
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL, color TEXT,
keywords TEXT[], budget NUMERIC,
parent_id INTEGER REFERENCES categories(id),
created_at TIMESTAMPTZ DEFAULT now()
);
-- Enable public access (RLS off for personal use)
ALTER TABLE accounts DISABLE ROW LEVEL SECURITY;
-- Migration: add balance tracking columns
-- ALTER TABLE accounts ADD COLUMN IF NOT EXISTS current_balance NUMERIC;
-- ALTER TABLE accounts ADD COLUMN IF NOT EXISTS balance_date TEXT;
ALTER TABLE transactions DISABLE ROW LEVEL SECURITY;
-- Migration: add card member field for authorized user tracking
-- ALTER TABLE transactions ADD COLUMN IF NOT EXISTS card_member TEXT;
-- Migration: split transaction support
-- ALTER TABLE transactions ADD COLUMN IF NOT EXISTS is_split_parent BOOLEAN DEFAULT false;
-- ALTER TABLE transactions ADD COLUMN IF NOT EXISTS split_parent_id UUID REFERENCES transactions(id);
-- Migration: recurring charge tracking
-- ALTER TABLE transactions ADD COLUMN IF NOT EXISTS is_recurring BOOLEAN DEFAULT false;
-- ALTER TABLE transactions ADD COLUMN IF NOT EXISTS recurring_frequency TEXT; -- 'monthly','weekly','yearly'
ALTER TABLE quick_entries DISABLE ROW LEVEL SECURITY;
ALTER TABLE categories DISABLE ROW LEVEL SECURITY;
-- Income forecasts (BvA / expected income tracking)
CREATE TABLE IF NOT EXISTS income_forecasts (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
description TEXT NOT NULL,
expected_amount NUMERIC NOT NULL,
expected_date TEXT,
income_type TEXT DEFAULT 'other',
business_id INTEGER REFERENCES businesses(id),
notes TEXT,
match_keyword TEXT,
status TEXT DEFAULT 'pending',
actual_amount NUMERIC,
matched_transaction_id UUID REFERENCES transactions(id),
reconciled_at TIMESTAMPTZ,
reconcile_notes TEXT,
created_at TIMESTAMPTZ DEFAULT now()
);
ALTER TABLE income_forecasts DISABLE ROW LEVEL SECURITY;
-- Expense reports (replaces user_settings blobs)
-- CREATE TABLE expense_reports (
-- id TEXT NOT NULL,
-- user_id UUID NOT NULL REFERENCES auth.users(id),
-- name TEXT NOT NULL DEFAULT '',
-- notes TEXT,
-- trip_id TEXT,
-- status TEXT DEFAULT 'pending',
-- matched_transaction_id UUID,
-- actual_amount NUMERIC,
-- reconciled_at TIMESTAMPTZ,
-- reconcile_notes TEXT,
-- created_at TIMESTAMPTZ DEFAULT now(),
-- PRIMARY KEY (id)
-- );
-- ALTER TABLE expense_reports ENABLE ROW LEVEL SECURITY;
-- CREATE POLICY "expense_reports_owner" ON expense_reports FOR ALL USING (auth.uid() = user_id);
-- CREATE TABLE expense_report_items (
-- id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
-- user_id UUID NOT NULL REFERENCES auth.users(id),
-- report_id TEXT NOT NULL REFERENCES expense_reports(id) ON DELETE CASCADE,
-- forecast_id TEXT NOT NULL,
-- created_at TIMESTAMPTZ DEFAULT now()
-- );
-- ALTER TABLE expense_report_items ENABLE ROW LEVEL SECURITY;
-- CREATE POLICY "expense_report_items_owner" ON expense_report_items FOR ALL USING (auth.uid() = user_id);
-- INVENTORY TABLES — run in Supabase SQL editor
-- CREATE TABLE inventory_items (
-- id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
-- user_id UUID NOT NULL REFERENCES auth.users(id),
-- business_id TEXT NOT NULL,
-- name TEXT NOT NULL,
-- unit TEXT DEFAULT 'each',
-- notes TEXT,
-- created_at TIMESTAMPTZ DEFAULT now()
-- );
-- ALTER TABLE inventory_items ENABLE ROW LEVEL SECURITY;
-- CREATE POLICY "inv_items_owner" ON inventory_items FOR ALL USING (auth.uid() = user_id);
--
-- CREATE TABLE inventory_lots (
-- id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
-- user_id UUID NOT NULL REFERENCES auth.users(id),
-- inventory_item_id UUID NOT NULL REFERENCES inventory_items(id) ON DELETE CASCADE,
-- transaction_id TEXT,
-- receipt_id TEXT,
-- quantity_purchased NUMERIC NOT NULL,
-- quantity_remaining NUMERIC NOT NULL,
-- unit_cost NUMERIC NOT NULL DEFAULT 0,
-- purchased_at DATE,
-- notes TEXT,
-- created_at TIMESTAMPTZ DEFAULT now()
-- );
-- ALTER TABLE inventory_lots ENABLE ROW LEVEL SECURITY;
-- CREATE POLICY "inv_lots_owner" ON inventory_lots FOR ALL USING (auth.uid() = user_id);
--
-- CREATE TABLE order_inventory_usage (
-- id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
-- user_id UUID NOT NULL REFERENCES auth.users(id),
-- order_id TEXT NOT NULL,
-- inventory_item_id UUID NOT NULL REFERENCES inventory_items(id) ON DELETE CASCADE,
-- inventory_lot_id UUID REFERENCES inventory_lots(id),
-- quantity_used NUMERIC NOT NULL,
-- unit_cost NUMERIC NOT NULL DEFAULT 0,
-- created_at TIMESTAMPTZ DEFAULT now()
-- );
-- ALTER TABLE order_inventory_usage ENABLE ROW LEVEL SECURITY;
-- CREATE POLICY "inv_usage_owner" ON order_inventory_usage FOR ALL USING (auth.uid() = user_id);
-- ORDER UNITS — per-unit production tracking
-- CREATE TABLE order_units (
-- id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
-- user_id UUID NOT NULL REFERENCES auth.users(id),
-- order_id TEXT NOT NULL,
-- unit_number INTEGER NOT NULL,
-- label TEXT,
-- status TEXT DEFAULT 'pending',
-- minutes_spent NUMERIC DEFAULT 0,
-- notes TEXT,
-- completed_at TIMESTAMPTZ,
-- created_at TIMESTAMPTZ DEFAULT now()
-- );
-- ALTER TABLE order_units ENABLE ROW LEVEL SECURITY;
-- CREATE POLICY "order_units_owner" ON order_units FOR ALL USING (auth.uid() = user_id);
--
-- CREATE TABLE order_unit_materials (
-- id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
-- user_id UUID NOT NULL REFERENCES auth.users(id),
-- order_unit_id UUID NOT NULL REFERENCES order_units(id) ON DELETE CASCADE,
-- order_id TEXT NOT NULL,
-- inventory_item_id UUID NOT NULL REFERENCES inventory_items(id) ON DELETE CASCADE,
-- inventory_lot_id UUID REFERENCES inventory_lots(id),
-- quantity_used NUMERIC NOT NULL,
-- unit_cost NUMERIC NOT NULL DEFAULT 0,
-- created_at TIMESTAMPTZ DEFAULT now()
-- );
-- ALTER TABLE order_unit_materials ENABLE ROW LEVEL SECURITY;
-- CREATE POLICY "order_unit_materials_owner" ON order_unit_materials FOR ALL USING (auth.uid() = user_id);
-- Jobs / employment fields on businesses table
-- ALTER TABLE businesses ADD COLUMN IF NOT EXISTS stream_type TEXT DEFAULT 'business';
-- ALTER TABLE businesses ADD COLUMN IF NOT EXISTS employer TEXT;
-- ALTER TABLE businesses ADD COLUMN IF NOT EXISTS employment_start TEXT;
-- ALTER TABLE businesses ADD COLUMN IF NOT EXISTS employment_end TEXT;
-- ALTER TABLE businesses ADD COLUMN IF NOT EXISTS annual_salary NUMERIC;
-- ALTER TABLE businesses ADD COLUMN IF NOT EXISTS hourly_rate NUMERIC;
-- ALTER TABLE businesses ADD COLUMN IF NOT EXISTS hours_per_week NUMERIC;
-- Orders: link transactions to a named order/project
-- ALTER TABLE transactions ADD COLUMN IF NOT EXISTS order_id TEXT;
-- Business income type (active vs passive)
-- ALTER TABLE businesses ADD COLUMN IF NOT EXISTS income_type TEXT DEFAULT 'active';
-- Gmail receipt linking
-- ALTER TABLE transactions ADD COLUMN IF NOT EXISTS receipt_email_id TEXT;
-- Budget system v2: bucket structure + transaction linking
-- ALTER TABLE transactions ADD COLUMN IF NOT EXISTS budget_id UUID REFERENCES budgets(id);
-- ALTER TABLE budgets ADD COLUMN IF NOT EXISTS bucket_type TEXT; -- 'taxes','deductions','k401','living','savings','fun'
-- ALTER TABLE budgets ADD COLUMN IF NOT EXISTS is_top_bucket BOOLEAN DEFAULT false;
-- ALTER TABLE budgets ADD COLUMN IF NOT EXISTS bucket_amount NUMERIC; -- total from BSW (top-level buckets only)
-- User settings (replaces localStorage for all user data)
CREATE TABLE IF NOT EXISTS user_settings (
user_id TEXT NOT NULL,
key TEXT NOT NULL,
value JSONB,
updated_at TIMESTAMPTZ DEFAULT now(),
PRIMARY KEY (user_id, key)
);
ALTER TABLE user_settings ENABLE ROW LEVEL SECURITY;
CREATE POLICY "users manage own settings" ON user_settings
USING (user_id = auth.uid()::text)
WITH CHECK (user_id = auth.uid()::text);
CREATE TABLE IF NOT EXISTS budget_setup (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
user_key TEXT UNIQUE,
data JSONB NOT NULL DEFAULT '{}',
updated_at TIMESTAMPTZ DEFAULT now()
);
ALTER TABLE budget_setup DISABLE ROW LEVEL SECURITY;
-- ALTER TABLE budgets ADD COLUMN IF NOT EXISTS name TEXT;
-- ALTER TABLE budgets ADD COLUMN IF NOT EXISTS sankey_node_id TEXT;
-- Categorization feedback / learning table
CREATE TABLE IF NOT EXISTS cat_feedback (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
description TEXT NOT NULL,
merchant_key TEXT,
original_cat_id INTEGER REFERENCES categories(id),
corrected_cat_id INTEGER NOT NULL REFERENCES categories(id),
created_at TIMESTAMPTZ DEFAULT now()
);
ALTER TABLE cat_feedback DISABLE ROW LEVEL SECURITY;
-- Time tracking entries (cross-device — stored in Supabase, not localStorage)
CREATE TABLE IF NOT EXISTS time_entries (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
stream_id TEXT NOT NULL,
order_id TEXT,
date TEXT NOT NULL,
hours NUMERIC NOT NULL,
description TEXT,
created_at TIMESTAMPTZ DEFAULT now()
);
ALTER TABLE time_entries ADD COLUMN IF NOT EXISTS started_at TIMESTAMPTZ;
ALTER TABLE time_entries ADD COLUMN IF NOT EXISTS ended_at TIMESTAMPTZ;
-- RLS policy: allow the anon key (used by this app) full access
ALTER TABLE time_entries ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "anon_all" ON time_entries;
CREATE POLICY "anon_all" ON time_entries FOR ALL TO anon USING (true) WITH CHECK (true);
-- Subscriptions tracker
CREATE TABLE IF NOT EXISTS subscriptions (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
user_id TEXT NOT NULL,
name TEXT NOT NULL,
amount NUMERIC NOT NULL DEFAULT 0,
frequency TEXT NOT NULL DEFAULT 'monthly',
start_date DATE,
next_renewal DATE,
status TEXT DEFAULT 'active',
origin TEXT DEFAULT 'standalone',
bundle_name TEXT,
bundle_members JSONB,
credit_card_id TEXT,
credit_card_name TEXT,
notes TEXT,
category TEXT,
proj_exp_parent_id TEXT,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
ALTER TABLE subscriptions DISABLE ROW LEVEL SECURITY;
-- Subscription change log
CREATE TABLE IF NOT EXISTS subscription_changelog (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
user_id TEXT NOT NULL,
subscription_id UUID REFERENCES subscriptions(id) ON DELETE CASCADE,
event_type TEXT NOT NULL,
event_date DATE NOT NULL,
amount NUMERIC,
previous_amount NUMERIC,
notes TEXT,
transaction_id TEXT,
created_at TIMESTAMPTZ DEFAULT now()
);
ALTER TABLE subscription_changelog DISABLE ROW LEVEL SECURITY;
-- Mortgages table (required for home value in Net Worth)
CREATE TABLE IF NOT EXISTS mortgages (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL,
name TEXT DEFAULT 'My Mortgage',
address TEXT,
type TEXT DEFAULT 'primary',
rental_income NUMERIC DEFAULT 0,
notes JSONB DEFAULT '{}',
segments JSONB DEFAULT '[]',
appraisals JSONB DEFAULT '[]',
collapsed BOOLEAN DEFAULT false,
created_at TIMESTAMPTZ DEFAULT now()
);
ALTER TABLE mortgages DISABLE ROW LEVEL SECURITY;
-- If mortgages table already exists but is missing the appraisals column:
ALTER TABLE mortgages ADD COLUMN IF NOT EXISTS appraisals JSONB DEFAULT '[]';
-- Storage bucket for PDF receipts
INSERT INTO storage.buckets (id, name, public)
VALUES ('receipts', 'receipts', false)
ON CONFLICT DO NOTHING;
Step 3 — Get your API keys
In your project: Settings → API. Copy the Project URL and the anon public key. Paste them into the Ledger setup screen.
Step 4 — Host Ledger (for phone access)
Upload this HTML file to GitHub Pages (free) or Netlify (free, drag-and-drop). Then bookmark the URL on your phone and tap "Add to Home Screen" in your browser.
Storage limits (free tier)
500 MB database · 1 GB file storage (PDF receipts) · More than enough for personal use.