Open source personal finance application
// Generates app/supabase/seed.sql from a demo-mode data snapshot.
//
// Two-step, local-only flow (the seed user/password are for local dev only):
// 1. Capture a snapshot of demo data from the running app (real demo generator output):
// open the app in demo mode, then read the Pinia stores via devtools and save
// { wallets, categories, trns } to app/scripts/.demo-snapshot.json.
// 2. Run `node app/scripts/gen-seed.mjs` to emit app/supabase/seed.sql.
//
// seed.sql is applied automatically by `supabase db reset` ([db.seed] in config.toml)
// and is idempotent (it deletes the test user's prior rows first), so it can also be
// applied against a running DB without a full reset.
import { readFileSync, writeFileSync } from 'node:fs'
import { dirname, resolve } from 'node:path'
import { fileURLToPath } from 'node:url'
const here = dirname(fileURLToPath(import.meta.url))
const SNAPSHOT = resolve(here, '.demo-snapshot.json')
const OUT = resolve(here, '../supabase/seed.sql')
// Fixed identity so seed.sql is deterministic and domain rows can reference the uid.
const TEST_UID = 'e2e00000-0000-4000-8000-000000000001'
const TEST_EMAIL = '[email protected]'
const TEST_PASSWORD = 'finapp-e2e'
const snap = JSON.parse(readFileSync(SNAPSHOT, 'utf8'))
const wallets = snap.wallets ?? {}
const categories = snap.categories ?? {}
const trns = snap.trns ?? {}
// --- SQL literal helpers ------------------------------------------------------
const q = s => `'${String(s).replace(/'/g, '\'\'')}'`
const txt = v => (v == null || v === '' ? 'null' : q(v))
const num = v => (v == null ? 'null' : String(v))
const int = v => (v == null ? 'null' : String(Math.round(v)))
const bool = v => (v ? 'true' : 'false')
function valuesBlock(table, cols, rows) {
if (!rows.length)
return ''
const colList = cols.map(c => `"${c}"`).join(', ')
const lines = []
// Chunk to keep individual INSERTs reasonable.
for (let i = 0; i < rows.length; i += 500) {
const chunk = rows.slice(i, i + 500)
lines.push(`insert into public.${table} (${colList}) values`)
lines.push(`${chunk.map(r => ` (${r.join(', ')})`).join(',\n')};`)
}
return `${lines.join('\n')}\n`
}
// --- wallets ------------------------------------------------------------------
const walletCols = ['id', 'userId', 'name', 'color', 'currency', 'type', 'order', 'creditLimit', 'desc', 'isArchived', 'isExcludeInTotal', 'isWithdrawal', 'updatedAt']
const walletRows = Object.entries(wallets).map(([id, w]) => [
q(id),
q(TEST_UID),
txt(w.name),
txt(w.color),
txt(w.currency),
txt(w.type),
int(w.order ?? 0),
w.type === 'credit' ? num(w.creditLimit ?? 0) : 'null',
txt(w.desc),
bool(w.isArchived),
bool(w.isExcludeInTotal),
bool(w.isWithdrawal),
int(w.updatedAt ?? Date.now()),
])
// --- categories ---------------------------------------------------------------
// 'adjustment' / 'transfer' are synthetic literals stored in trns.categoryId, NOT real
// category rows (and the demo store carries them without a color), so skip them.
const SYNTHETIC_CATS = new Set(['adjustment', 'transfer'])
const categoryCols = ['id', 'userId', 'name', 'color', 'icon', 'parentId', 'showInLastUsed', 'showInQuickSelector', 'updatedAt']
const categoryRows = Object.entries(categories)
.filter(([id, c]) => !SYNTHETIC_CATS.has(id) && c.color && c.icon && c.name)
.map(([id, c]) => [
q(id),
q(TEST_UID),
txt(c.name),
txt(c.color),
txt(c.icon),
c.parentId && c.parentId !== 0 ? q(String(c.parentId)) : 'null',
bool(c.showInLastUsed),
bool(c.showInQuickSelector),
int(c.updatedAt ?? Date.now()),
])
// --- trns ---------------------------------------------------------------------
const trnCols = ['id', 'userId', 'type', 'date', 'updatedAt', 'categoryId', 'desc', 'amount', 'walletId', 'expenseAmount', 'expenseWalletId', 'incomeAmount', 'incomeWalletId']
const trnRows = Object.entries(trns).map(([key, t]) => {
const isTransfer = t.type === 2
return [
q(`seed_t_${key}`),
q(TEST_UID),
num(t.type),
int(t.date),
int(t.updatedAt ?? Date.now()),
isTransfer ? q('transfer') : txt(t.categoryId),
txt(t.desc),
isTransfer ? 'null' : num(t.amount),
isTransfer ? 'null' : txt(t.walletId),
isTransfer ? num(t.expenseAmount) : 'null',
isTransfer ? txt(t.expenseWalletId) : 'null',
isTransfer ? num(t.incomeAmount) : 'null',
isTransfer ? txt(t.incomeWalletId) : 'null',
]
})
// --- assemble -----------------------------------------------------------------
const sql = `-- LOCAL DEV SEED - generated by app/scripts/gen-seed.mjs from a demo-mode snapshot.
-- Creates a fixed email/password test user (${TEST_EMAIL}) plus the demo dataset for it,
-- so agents / e2e can sign in to real PowerSync mode without Google OAuth.
-- Local only: the password is not a secret. Applied by \`supabase db reset\`.
begin;
-- Idempotent reset of the test user and its data (no-op on a fresh reset).
delete from public.trns where "userId" = ${q(TEST_UID)};
delete from public.wallets where "userId" = ${q(TEST_UID)};
delete from public.categories where "userId" = ${q(TEST_UID)};
delete from public.user_settings where "userId" = ${q(TEST_UID)};
delete from auth.identities where user_id = ${q(TEST_UID)}::uuid;
delete from auth.users where id = ${q(TEST_UID)}::uuid or email = ${q(TEST_EMAIL)};
-- Auth user (email provider) + identity. crypt/gen_salt come from pgcrypto (extensions schema).
-- The token columns must be '' (not NULL): GoTrue scans them as strings and a NULL
-- triggers "Database error querying schema" on sign-in.
insert into auth.users (
instance_id, id, aud, role, email, encrypted_password,
email_confirmed_at, created_at, updated_at, raw_app_meta_data, raw_user_meta_data,
is_sso_user, is_anonymous,
confirmation_token, recovery_token, email_change, email_change_token_new,
email_change_token_current, phone_change, phone_change_token, reauthentication_token
) values (
'00000000-0000-0000-0000-000000000000', ${q(TEST_UID)}::uuid, 'authenticated', 'authenticated',
${q(TEST_EMAIL)}, extensions.crypt(${q(TEST_PASSWORD)}, extensions.gen_salt('bf')),
now(), now(), now(), '{"provider":"email","providers":["email"]}', '{}', false, false,
'', '', '', '', '', '', '', ''
);
insert into auth.identities (
provider_id, user_id, identity_data, provider, last_sign_in_at, created_at, updated_at
) values (
${q(TEST_UID)}, ${q(TEST_UID)}::uuid,
jsonb_build_object('sub', ${q(TEST_UID)}, 'email', ${q(TEST_EMAIL)}),
'email', now(), now(), now()
);
-- user_settings (the on_auth_user_created trigger inserts a default row; pin currency/locale).
insert into public.user_settings (id, "userId", "baseCurrency", locale)
values (${q(TEST_UID)}, ${q(TEST_UID)}, 'USD', 'en')
on conflict (id) do update set "baseCurrency" = excluded."baseCurrency", locale = excluded.locale;
-- Domain data (from demo): ${walletRows.length} wallets, ${categoryRows.length} categories, ${trnRows.length} trns.
${valuesBlock('wallets', walletCols, walletRows)}
${valuesBlock('categories', categoryCols, categoryRows)}
${valuesBlock('trns', trnCols, trnRows)}
commit;
`
writeFileSync(OUT, sql)
console.log(`wrote ${OUT}`)
console.log(`wallets=${walletRows.length} categories=${categoryRows.length} trns=${trnRows.length}`)