Phase 6: Preset Data, First-Run Detection, and DB Safety - Context

Gathered: 2026-04-20 Status: Ready for planning

## Phase Boundary

The data layer is safe and ready — duplicate budget/category writes are impossible at the DB level, and the app correctly identifies first-run users. This phase delivers: DB uniqueness constraints, a setup_completed column with backfill migration, a useFirstRunState hook, and a preset budget item library (~15-20 items with i18n).

## Implementation Decisions

Preset Library Content

Default amounts in EUR (matches existing profiles.currency default)
4 income, 4 bill, 5 variable_expense, 2 debt, 2 saving, 2 investment items (~19 total)
i18n key format: presets.{category_type}.{slug} (e.g., presets.bill.rent)
Round number amounts (e.g., rent=1000, groceries=400) — easier to adjust, less presumptuous

First-Run Detection Logic

First-run triggers when user has zero categories OR zero template items (either missing = not set up)
New dedicated hook: src/hooks/useFirstRunState.ts (follows useCategories/useTemplate pattern)
Backfill via Supabase migration SQL: UPDATE profiles SET setup_completed = true WHERE id IN (SELECT DISTINCT user_id FROM categories)
Hook derives state from existing hooks (useCategories count + useTemplate items count) — no extra network call

DB Migration & Constraint Strategy

Cleanup step before adding unique constraints (deduplicate existing data safely)
Separate migration files: 006_uniqueness_constraints.sql and 007_setup_completed.sql
setup_completed column defaults to false — new signups start as not-setup; existing users backfilled to true
Unique constraint on budgets: (user_id, start_date) only — end_date always derived

Claude's Discretion

Exact preset item names and amounts (within the balanced category distribution)
German translation text for preset i18n keys
Order of migration operations within each file
Error handling approach for constraint violations in application code

<code_context>

Existing Code Insights

Reusable Assets

src/hooks/useCategories.ts — existing hook pattern to follow for useFirstRunState
src/hooks/useTemplate.ts — template data access, can derive "has template items" from this
src/lib/types.ts — type definitions for categories, templates, budgets
supabase/migrations/001-005 — existing migration chain to extend

Established Patterns

Hooks use React Query with Supabase client
Category types enum: income, bill, variable_expense, debt, saving, investment
Template items reference categories via category_id
Profiles table auto-created via trigger on auth.users insert

Integration Points

profiles table needs new setup_completed boolean column
budgets table needs unique constraint on (user_id, start_date)
categories table needs unique constraint on (user_id, name)
New src/data/presets.ts file for preset library
New src/hooks/useFirstRunState.ts for first-run detection
i18n files need preset translation keys (en + de)

</code_context>

## Specific Ideas

Currency column in profiles is already currency text not null default 'EUR' — confirmed in 001_profiles.sql
The handle_new_user() trigger creates profiles on signup — setup_completed will be false by default for new users
Templates table already has unique(user_id) — one template per user is enforced

## Deferred Ideas

None — discussion stayed within phase scope.

3.5 KiB Raw Permalink Blame History